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);
}
$header["time"] = "when";
$header["type"] = "act";
$header["ip"] = "from IP";
$header["uid"] = "who";
$header["text"] = "what";
if (!$_GET["sortby"]) {
$_GET["sortby"] = "time";
}
if (!$_GET["sortbyDir"]) {
$_GET["sortbyDir"] = "DESC";
}
if (logTitle()) {
$q = queryForTPL();
while ($rec = $dbClass->next_record($q)) {
drawTableHeader();
formatRecord();
$t->printTableRow($rec);
}
if ($t) {
$t->close();
}
} else {
print x("h3", "log-file is empty");
}
endPage();
// ==========================================================================
/// @fn void ( void )
///
/// ...
///
/// @global
