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