function createDB()
{
global $dbPath, $dbHandle;
global $BL;
if (!file_exists($dbPath)) {
/*
// create a SQLite2 database file and return a database handle (Object Oriented)
try {
$dbHandle = new SQLiteDatabase($dbPath, 0666);
} catch( Exception $exception ) {
die($exception->getMessage());
}
*/
// open database file and return a database handle
$dbHandle = sqlite_open($dbPath, 0666, $sqliteError) or die($sqliteError);
// create page view database table //number INTEGER($chars_number),
$cmd = "CREATE TABLE list (\n\t\t\t\t\tid INTEGER PRIMARY KEY NOT NULL,\n\t\t\t\t\tname CHAR(" . $BL['name']['size'] . "),\n\t\t\t\t\tnotes CHAR(" . $BL['notes']['size'] . "),\n\t\t\t\t\tenddate CHAR(" . $BL['enddate']['size'] . "),\n\t\t\t\t\tperson CHAR(" . $BL['person']['size'] . ")\n\t\t\t\t\t)";
dbExec($cmd);
$cmd = "CREATE TABLE user (\n\t\t\t\t\tid INTEGER PRIMARY KEY NOT NULL,\n\t\t\t\t\tusername CHAR(" . $BL['name']['size'] . "),\n\t\t\t\t\tpassword CHAR(" . $BL['name']['size'] . "),\n\t\t\t\t\tcreationdate CHAR(" . $BL['enddate']['size'] . ")\n\t\t\t\t\t)";
dbExec($cmd);
//?? $pageVisit = sqlite_escape_string($_SERVER['PHP_SELF']);
$n = 10;
insertRecords($n);
return "Database created in '{$dbPath}' with {$n} dummy records";
} else {
return '';
}
}
function updatePhonebook($pidm = null)
{
ini_set('memory_limit', '256M');
$pidm = (int) $pidm;
if ($pidm) {
$spbpers_pidm = "AND spbpers_pidm = {$pidm}";
$spriden_pidm = "AND spriden_pidm = {$pidm}";
$pid_pidm = "AND pid = {$pidm}";
$demog_pidm = "AND d.pidm = {$pidm}";
}
// truncate the build table to prep
PSU::db('phonebook')->execute('TRUNCATE TABLE phonebook_build');
PSU::db('phonebook')->execute('OPTIMIZE TABLE phonebook_build');
// get everybody
$interval = 1000;
$sql = "SELECT COUNT(*) \n\t\t FROM psu_identity.person_identifiers,\n\t\t spbpers\n\t\t WHERE pid = spbpers_pidm \n\t\t AND login_name IS NOT NULL {$spbpers_pidm}";
if ($tot = PSU::db('psc1')->GetOne($sql)) {
for ($i = 0; $i * $interval <= $tot; $i++) {
set_time_limit(600);
$insert = array();
$sql = "SELECT pid AS pidm, \n\t\t\t\t wp_id, \n\t\t\t\t psu_id, \n\t\t\t\t first_name, \n\t\t\t\t middle_name, \n\t\t\t\t last_name, \n\t\t\t\t name_suffix AS spbpers_name_suffix, \n\t\t\t\t login_name AS username, \n\t\t\t\t login_name AS email, \n\t\t\t\t\t\t\t\t\t\t\t spbpers_confid_ind, \n\t\t\t\t\t\t\t\t\t\t\t has_idcard \n\t\t\t\t\t FROM (SELECT k.pid, \n\t\t\t\t\t w.wp_id, \n\t\t\t\t\t psu_id, \n\t\t\t\t\t first_name, \n\t\t\t\t\t middle_name, \n\t\t\t\t\t last_name, \n\t\t\t\t\t name_suffix, \n\t\t\t\t\t login_name, \n\t\t\t\t\t spbpers_confid_ind, \n\t\t\t\t\t decode(spbcard_id, NULL, 0, 1) as has_idcard, \n\t\t\t\t\t ROWNUM AS rnum\n\t\t\t\t\t\t FROM psu_identity.person_identifiers k\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tLEFT OUTER JOIN spbpers\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tON spbpers_pidm = k.pid\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tLEFT OUTER JOIN psu.spbcard\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tON spbcard_pidm = k.pid\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tLEFT OUTER JOIN psu_identity.person_ext_cache w\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tON w.pid = k.pid\n\t\t\t\t\t\t WHERE login_name IS NOT NULL \n\t\t\t\t\t\t AND k.pid not in(53166, 46242)\n\t\t\t\t\t {$pid_pidm}\n\t\t\t\t\t ) \n\t\t\t\t\t WHERE rnum BETWEEN " . $i * $interval . " AND " . ($i + 1) * $interval . "\n\t\t\t\t\t\t AND pid NOT IN(53166, 46242)";
$rset = PSU::db('psc1')->Execute($sql);
foreach ($rset as $person) {
$insert[$person['pidm']] = formatRecord($person);
}
insertRecords($insert, FALSE);
}
}
set_time_limit(600);
$insert = array();
// get all student account actives
$sql = "SELECT aa.pidm, \n\t\t\t\t\t\t\t\t w.wp_id,\n\t\t\t\t\t\t\t\t\tspriden_id AS psu_id, \n\t\t\t\t\t\t\t\t\tspriden_first_name AS first_name, \n\t\t\t\t\t\t\t\t\tspriden_mi AS middle_name, \n\t\t\t\t\t\t\t\t\tspriden_last_name AS last_name, \n\t\t\t\t\t\t\t\t\tspbpers_name_suffix, \n\t\t\t\t\t\t\t\t\tstu.vm_phone_number AS phone_number, \n\t\t\t\t\t\t\t\t\tgobtpac.gobtpac_external_user AS username, \n\t\t\t\t\t\t\t\t\tgobtpac.gobtpac_external_user AS email, \n\t\t\t\t\t\t\t\t\tstu.major, \n\t\t\t\t\t\t\t\t\tca_address1 AS msc, \n\t\t\t\t\t\t\t\t\tdecode(spbcard_id, NULL, 0, 1) as has_idcard \n\t\t\t\t\t\tFROM v_student_account_active aa\n\t\t\t\t\t\t\t\t\tINNER JOIN spriden\n\t\t\t\t\t\t\t\t\t\tON spriden_pidm = aa.pidm\n\t\t\t\t\t\t\t\t\t\tAND spriden_change_ind IS NULL\n\t\t\t\t\t\t\t\t\tLEFT OUTER JOIN spbpers\n\t\t\t\t\t\t\t\t\t\tON spbpers_pidm = aa.pidm\n\t\t\t\t\t\t\t\t\t\tAND (spbpers_confid_ind IS NULL\n\t\t\t\t\t\t\t\t\t\t\t\t\tOR\n\t\t\t\t\t\t\t\t\t\t\t\t\tspbpers_confid_ind = 'N'\n\t\t\t\t\t\t\t\t\t\t\t\t)\n\t\t\t\t\t\t\t\t\tLEFT OUTER JOIN datamart.ps_as_student_demographics stu\n\t\t\t\t\t\t\t\t\t\tON stu.pidm = aa.pidm\n\t\t\t\t\t\t\t\t\tLEFT OUTER JOIN gobtpac\n\t\t\t\t\t\t\t\t\t\tON gobtpac_pidm = aa.pidm\n\t\t\t\t\t\t\t\t\tLEFT OUTER JOIN spbcard\n\t\t\t\t\t\t\t\t\t\tON spbcard_pidm = aa.pidm\n\t\t\t\t\t\t\t\t LEFT OUTER JOIN psu_identity.person_ext_cache w\n\t\t\t\t\t\t\t\t\t ON w.pid = aa.pidm\n\t\t\t\t\t WHERE 1 = 1\n\t\t\t\t\t\t\t\t {$spriden_pidm}\n\t\t\t\t\t\tORDER BY spriden_last_name,\n\t\t\t\t\t\t\t\t\tspriden_first_name,\n\t\t\t\t\t\t\t\t\tspriden_mi,\n\t\t\t\t\t\t\t\t\taa.pidm";
foreach (PSU::db('psc1')->GetAll($sql) as $person) {
$person['emp'] = false;
$person['stu'] = true;
$person['stu_account'] = true;
$insert[$person['pidm']] = formatRecord($person, $insert[$person['pidm']]);
}
// get all public employees
$sql = "SELECT d.pidm, \n\t\t\t\t\t w.wp_id,\n\t\t\t\t\t w.login_name as username,\n\t\t d.id as psu_id, \n\t\t d.first_name, \n\t\t d.middle_name, \n\t\t d.last_name, \n\t\t spbpers_name_suffix, \n\t\t d.title, \n\t\t d.department, \n\t\t d.office_phone, \n\t\t d.email_address AS email, \n\t\t d.msc, \n\t\t decode(spbcard_id, NULL, 0, 1) as has_idcard\n\t\t\t\t\t\t\tFROM datamart.ps_as_employee_demog d\n\t\t\t\t\t\t\t\t\t LEFT OUTER JOIN spbpers\n\t\t\t\t\t\t\t\t\t\tON spbpers_pidm = d.pidm\n\t\t\t\t\t\t\t\t\t LEFT OUTER JOIN spbcard\n\t\t\t\t\t\t\t\t\t\tON spbcard_pidm = d.pidm\n\t\t\t\t\t\t\t\t\t LEFT OUTER JOIN psu_identity.person_identifiers w\n\t\t\t\t\t\t\t\t\t\tON w.pid = d.pidm\n\t\t\t\t\t\t WHERE EXISTS(SELECT 1 \n\t\t FROM v_employee e\n\t\t WHERE e.pidm = d.pidm\n\t\t AND e.pidm not in(53166, 46242)\n\t\t )\n\t\t\t\t\t\t\t\t\t {$demog_pidm}\n\t\t ORDER BY last_name,\n\t\t first_name,\n\t\t middle_name,\n\t\t pidm";
$rset = PSU::db('psc1')->Execute($sql);
foreach ($rset as $person) {
$person['emp'] = true;
$insert[$person['pidm']] = formatRecord($person, $insert[$person['pidm']]);
}
$max = 50;
$loopmax = ceil(count($insert) / $max);
for ($i = 0; $i <= $loopmax; $i++) {
insertRecords(array_slice($insert, $i * $max, $max), TRUE);
}
$sql = "INSERT INTO phonebook \n\t\t SELECT * FROM phonebook_build ON DUPLICATE KEY UPDATE \n\t\t\t\t\t\t\twpid = VALUES( wpid ),\n\t\t \tpsu_id = VALUES( psu_id ), \n\t\t \tlastup = VALUES( lastup ), \n\t\t \tpublic = VALUES( public ), \n\t\t \tusername = VALUES( username ), \n\t\t \temail = VALUES( email ), \n\t\t \tmsc = VALUES( msc ), \n\t\t \tname_last = VALUES( name_last ), \n\t\t \tname_last_formatted = VALUES( name_last_formatted ), \n\t\t \tname_last_metaphone = VALUES( name_last_metaphone ), \n\t\t \tname_first = VALUES( name_first ), \n\t\t \tname_first_formatted = VALUES( name_first_formatted ), \n\t\t \tname_first_metaphone = VALUES( name_first_metaphone ), \n\t\t \tname_middle_formatted = VALUES( name_middle_formatted ), \n\t\t \tname_full = VALUES( name_full ), \n\t\t \tphone_of = VALUES( phone_of ), \n\t\t \tphone_vm = VALUES( phone_vm ), \n\t\t \temp = VALUES( emp ), \n\t\t \tstu = VALUES( stu ), \n\t\t \tstu_account = VALUES( stu_account ), \n\t\t \tdept = VALUES( dept ), \n\t\t \ttitle = VALUES( title ), \n\t\t \tmajor = VALUES( major ), \n\t\t \thint = phonebook.hint, \n\t\t \thas_idcard = VALUES( has_idcard )";
PSU::db('phonebook')->execute($sql);
// remove old records
PSU::db('phonebook')->execute('DELETE FROM phonebook WHERE DATE_SUB( CURDATE(), INTERVAL 1 DAY ) > lastup');
return count($insert);
}
foreach ($walletApis as $api) {
$type = $api['type'];
$keyID = $api['keyID'];
$vCode = $api['vCode'];
$charID = $api['charID'];
$pheal = Util::getPheal($keyID, $vCode, true);
$arr = array('characterID' => $charID, 'rowCount' => 1000);
if ($type == 'char') {
$q = $pheal->charScope->WalletJournal($arr);
} elseif ($type == 'corp') {
$q = $pheal->corpScope->WalletJournal($arr);
} else {
continue;
}
if (count($q->transactions)) {
insertRecords($charID, $q->transactions);
}
}
applyBalances();
function applyBalances()
{
global $walletCharacterID, $baseAddr, $mdb, $adFreeMonthCost, $redis;
// First, set any new records to paymentApplied = 0
$mdb->set("payments", ['paymentApplied' => ['$ne' => 1]], ['paymentApplied' => 0], true);
// And then iterate through un-applied payments
$toBeApplied = $mdb->find("payments", ['paymentApplied' => 0]);
if ($toBeApplied == null) {
$toBeApplied = [];
}
foreach ($toBeApplied as $row) {
if ($row['ownerID2'] != $walletCharacterID) {
</div>
</div>
<ul>
<li><a href="view.php">view</a></li>
<li><a href="edit.php" class='selected'>edit</a></li>
</ul>
</div>
<div class="hline"></div>
<div class="admin-content">
<?php
if ($name) {
// nav
echo "<div class='admin-nav'>\n";
echo "<H2>Edit list definition: <strong>{$name}</strong> <a href='editrecord.php?name={$name}'>(values)</a></H2>\n";
insertRecords($valueLabels, $name, $init, 'value');
insertRecords($assetLabels, $name, $init, 'asset');
echo "</div>\n";
// content
echo "<div class='admin-content-edit'>\n";
if ($selectedRecordName) {
echo "<h2>Edit the following properties for '{$selectedRecordName}' and save</h2>\n";
echo "<form method='post' action='" . $_SERVER['PHP_SELF'] . "'>\n";
// update label
$index = 1;
foreach ($recordStructure as $field) {
echo "<h3>" . $field . "</h3>\n";
echo "<input type='text' name='value" . $index++ . "' value='" . $selectedRecord[$index] . "' width=40></input><br/>\n";
}
echo "<input type='hidden' name='name' value='{$name}' />\n";
echo "<input type='hidden' name='save' value='{$init}' />\n";
echo "<input type='hidden' name='type' value='{$type}' />\n";
