$query = "\n\tSELECT p.person_id,\n\t\tcoalesce(p.preferred_first_name, p.first_name) as first_name,\n\t\tcoalesce(p.preferred_last_name, p.last_name) as last_name,\n\t\tcoalesce(pc.nickname, p.nickname) as nickname,\n\t\tpc.position_title,\n\t\tpc.person_company_relation,\n\t\tdate_part('month', p.birth_date) as birth_date_month,\n\t\tdate_part('day', p.birth_date) as birth_date_day,\n\t\tdate_part('epoch', p.birth_date) as birth_date_epoch,\n\t\tpc.hire_date,\n\t\tc.company_name,\n\t\tc.company_id,\n\t\tpi.person_image_id,\n\t\tpc.manager_person_id,\n\t\tcoalesce(mgrp.preferred_first_name, mgrp.first_name) as mgr_first_name,\n\t\tcoalesce(mgrp.preferred_last_name, mgrp.last_name) as mgr_last_name,\n\t\tac.account_collection_id,\n\t\tac.account_collection_name,\n\t\ta.login,\n\t\tnumreports.tally as num_reports,\n\t\tofc.display_label,\n\t\tofc.physical_address_id,\n\t\tofc.building,\n\t\tofc.floor,\n\t\tofc.section,\n\t\tofc.seat_number\n\tFROM v_corp_family_account a\n\t\tINNER JOIN person p using (person_id)\n\t\tINNER JOIN company c using (company_id)\n\t\tINNER JOIN person_company pc using (person_id,company_id)\n\t\tLEFT JOIN ( select ac.*, account_id\n\t\t\t FROM account_collection ac\n\t\t\t\tINNER JOIN account_collection_account\n\t\t\t\tUSING (account_collection_id)\n\t\t\t WHERE account_collection_type = 'department'\n\t\t) ac USING (account_id)\n\t\tLEFT JOIN (\n\t\t select pi.*, piu.person_image_usage\n\t\t from person_image pi\n\t\t\t inner join person_image_usage piu\n\t\t\t\ton pi.person_image_id = piu.person_image_id\n\t\t\t\tand piu.person_image_usage = 'corpdirectory'\n\t\t ) pi USING (person_id)\n\t\tLEFT JOIN (\n\t\t select manager_person_id as person_id, count(*) as tally\n\t\t\t from person_company\n\t\t\t where person_company_status = 'enabled'\n\t\t\t group by manager_person_id\n\t\t) numreports USING (person_id)\n\t\tLEFT JOIN (\n\t\t select pl.person_id,\n\t\t\tpa.physical_address_id,\n\t\t\tpa.display_label,\n\t\t\tpl.building,\n\t\t\tpl.floor,\n\t\t\tpl.section,\n\t\t\tpl.seat_number\n\t\t from person_location pl\n\t\t\tinner join physical_address pa\n\t\t\t USING (physical_address_id)\n\t\t where pl.person_location_type = 'office'\n\t\t order by site_rank\n\t\t) ofc USING (person_id)\n\t\tLEFT JOIN person mgrp\n\t\t on pc.manager_person_id = mgrp.person_id\n\tWHERE p.person_id = \$1 \n\tAND (pc.hire_date is null or pc.hire_date <= now())\n\tAND a.account_role = 'primary'\n\tORDER BY ac.account_collection_name\n;\n";
$result = pg_query_params($dbconn, $query, array($personid)) or die('Query failed: ' . pg_last_error());
$row = pg_fetch_array($result, null, PGSQL_ASSOC) or die("no person");
if ($row['login'] == $_SERVER['REMOTE_USER'] || check_admin($dbconn, $_SERVER['REMOTE_USER'])) {
$canedit = 1;
} else {
$canedit = 0;
}
$name = $row['first_name'] . " " . $row['last_name'];
$title = $row['position_title'];
$teamc = " (" . $row['company_name'] . ")";
if (isset($row['mgr_last_name'])) {
$manager = $row['mgr_first_name'] . " " . $row['mgr_last_name'];
}
echo build_header($name);
echo browsingMenu($dbconn, null);
if (isset($_GET['random']) && $_GET['random'] == 'yes') {
echo "<div id=random> <a href=\"./?index=random\"> Another Random Person </a> </div>\n";
}
echo "<div class=directorypic>" . img($row['person_id'], $row['person_image_id'], 'contact') . "</div>";
echo "<div class=\"description\">";
echo "<h1> {$name} </h1>";
if (isset($row['nickname']) && strtolower($row['nickname']) != strtolower($row['first_name'])) {
echo "AKA ", $row['nickname'], " </br>\n";
}
echo "<table id=\"contact\">\n";
if (isset($row['num_reports']) && $row['num_reports'] > 0) {
$title = "{$title} (" . hierlink('reports', $row['person_id'], "team") . ")";
}
if (isset($title)) {
echo build_tr("Title", $title);
?>
<a href="<?php
echo $qs;
?>
"> NEXT </a> <?php
$qs = build_url(build_qs(null, 'offset', $numpages));
?>
// <a href="<?php
echo $qs;
?>
"> LAST </a> <?php
}
echo "</div>\n";
}
} else {
echo browsingMenu($dbconn, $index);
echo "<h3> Browse by Functional Team </h3>\n";
echo "<div class=teamlist><ul>\n";
while ($row = pg_fetch_array($result, null, PGSQL_ASSOC)) {
echo "<li>" . hierlink('team', $row['account_collection_id'], $row['account_collection_name']) . "</li>\n";
}
echo "</ul>\n";
echo "</div>\n";
}
echo build_footer();
// Free resultset
pg_free_result($result);
// Closing connection
pg_close($dbconn);
?>
</div>
<?php
include "personlib.php";
$dbconn = dbauth::connect('directory', null, $_SERVER['REMOTE_USER']) or die("Could not connect: " . pg_last_error());
echo build_header("Events");
echo browsingMenu($dbconn, null, 'locations');
$address = isset($_GET['physical_address_id']) ? $_GET['physical_address_id'] : null;
$addrsubq = "";
if ($address) {
$addrsubq = "AND officemap.physical_address_id = \$1";
}
$query = "\n\tWITH perlimit AS (\n\t\tSELECT\tperson_id, account_collection_name as restrict\n\t\tFROM\tv_corp_family_account\n\t\t\t\tINNER JOIN account_collection_account USING (account_id)\n\t\t\t\tINNER JOIN account_collection USING (account_collection_id)\n\t\tWHERE\taccount_collection_type = 'system'\n\t\tAND\t\taccount_collection_name IN\n\t\t\t\t('noeventsbirthday', 'noeventsanniversary')\n\t), birthdaycompanyok AS (\n\t\tSELECT\ts.site_code, company_id\n\t\tFROM\tsite s\n\t\t\t\tINNER JOIN property p on s.colo_company_id = p.company_id\n\t\tWHERE\tp.property_name = 'ShowBirthday'\n\t\tAND\t\tp.property_type = 'PhoneDirectoryAttributes'\n\t), officemap AS (\n\t select pa.physical_address_id,\n\t\t\t\tpl.person_id,\n\t\t\t\tpa.display_label,\n\t\t\t\tpa.company_id,\n\t\t\t\tpl.building,\n\t\t\t\tpl.floor,\n\t\t\t\tpl.section,\n\t\t\t\tpl.seat_number\n\t from person_location pl\n\t\t\t\tinner join physical_address pa\n\t\t\t\t\tUSING (physical_address_id)\n\t where pl.person_location_type = 'office'\n\t order by site_rank\n\t), anniversary AS (\n\t\tselect\tp.person_id,\n\t\t\tcoalesce(p.preferred_first_name, p.first_name) as first_name,\n\t\t\tcoalesce(p.preferred_last_name, p.last_name) as last_name,\n\t\t\tdate_part('epoch', pc.hire_date) as whence,\n\t\t\tpc.hire_date as whence_human,\n\t\t\t'Hire Date'::text as event,\n CASE WHEN pc.hire_date IS NOT NULL THEN\n\t\t\tround(extract('epoch' FROM (select\n\t\t\t\t\tdate_trunc('year',now()) - date_trunc('year',pc.hire_date)\n\t\t\t))/86400/365) ELSE NULL END AS duration\n\t \tfrom\tperson_company pc\n\t\tjoin person p using (person_id)\n\t\twhere pc.hire_date is not null and pc.hire_date <= now()\n\t\tAND person_id NOT IN (\n\t\t\tSELECT person_id\n\t\t\tFROM perlimit\n\t\t\tWHERE restrict = 'noeventsanniversary'\n\t\t)\n\t), birthdays AS (\n\t\tselect p.person_Id,\n\t\t\tcoalesce(p.preferred_first_name, p.first_name) as first_name,\n\t\t\tcoalesce(p.preferred_last_name, p.last_name) as last_name,\n\t\t\tdate_part('epoch', p.birth_date) as whence,\n\t\t\tp.birth_date as whence_human,\n\t\t\t'Birthday'::text as event,\n\t\t\tNULL::numeric as duration\n\t\tFROM\tperson p\n\t\t\tINNER JOIN officemap USING (person_id)\n\t\t\tINNER JOIN birthdaycompanyok USING (company_id)\n\t\tWHERE\tp.birth_date is not null\n\t\tAND person_id NOT IN (\n\t\t\tSELECT person_id\n\t\t\tFROM perlimit\n\t\t\tWHERE restrict = 'noeventsbirthday'\n\t\t)\n\t), events AS (\n\t\tSELECT * FROM anniversary UNION SELECT * FROM birthdays\n\t) SELECT events.*, officemap.display_label as office_location\n\t\tFROM events\n\t\tINNER JOIN (\n\t\t\tSELECT * from person_company\n\t\t\tWHERE hire_date is null or hire_date <= now()\n\t\t) pc USING (person_id)\n\t\tINNER JOIN v_corp_family_account vcfa USING (person_id, company_id)\n\t\tLEFT JOIN officemap USING (person_id)\n\tWHERE \n\tpc.person_company_relation = 'employee'\n\tAND vcfa.is_enabled = 'Y'\n {$addrsubq}\n\tORDER BY date_part('month', whence_human),\n\t\tdate_part('day', whence_human),\n\t\tlast_name,\n\t\tfirst_name,\n\t\tevent\n";
if ($address) {
$params = array($address);
} else {
$params = array();
}
$result = pg_query_params($dbconn, $query, $params) or die('Query failed: ' . pg_last_error());
echo "<table class=events>\n";
$last_month = "";
while ($row = pg_fetch_array($result, null, PGSQL_ASSOC)) {
$name = $row['first_name'] . " " . $row['last_name'];
$name = personlink($row['person_id'], $name);
$mon = date("F", $row['whence']);
$event = $row['event'];
if ($mon != $last_month) {
$last_month = $mon;
echo "<tr class=month> <td colspan=5> {$mon} </td> </tr>\n";
}
if ($event != 'Birthday') {
$printable = date("F j, Y", $row['whence']);
