/* Was $teamc at the end, which includes the company */
echo build_tr("Functional Team", hierlink('team', $row['account_collection_id'], $row['account_collection_name']));
} else {
echo build_tr("Functional Team", 'NONE');
}
$email = get_email($dbconn, $row['person_id']);
if (isset($email) || isset($row['login'])) {
if ($email == null) {
$email = $row['login'] . "@" . get_default_domain($dbconn);
}
} else {
$email = "<b> NONE </b>";
}
echo build_tr("Email", $email);
if (isset($manager)) {
echo build_tr("Manager", personlink($row['manager_person_id'], $manager));
}
if (isset($row['hire_date'])) {
$hd = $row['hire_date'];
$hd = preg_replace("/\\s.*\$/", "", $row['hire_date']);
echo build_tr("Hire Date", $hd);
}
/*
* Not legal in some countries
if(isset($row['birth_date_epoch'])) {
echo build_tr("Birthday", date("F j", $row['birth_date_epoch']));
}
*/
echo build_tr("Relation", $row['person_company_relation']);
if (isset($row['display_label'])) {
$a = '<a href="./?index=byoffice&physical_address_id=' . $row['physical_address_id'] . '">' . $row['display_label'] . '</a>';
echo "<td> </td>";
}
echo "<td>" . personlink($row['person_id'], $name);
if (isset($row['num_reports']) && $row['num_reports'] > 0) {
echo "<br>(" . hierlink('reports', $row['person_id'], "team") . ")";
}
echo "</td>";
if ($row['person_company_relation'] != 'employee' && !$row['position_title']) {
$row['position_title'] = 'CONSULTANT';
}
echo "<td> " . $row['position_title'] . "</td>\n";
echo "<td> " . $row['company_name'] . "</td>\n";
# Show Manager Links
if (isset($row['manager_person_id'])) {
$mgrname = $row['mgr_first_name'] . " " . $row['mgr_last_name'];
echo "<td>" . personlink($row['manager_person_id'], $mgrname);
echo "<br>(" . hierlink('reports', $row['manager_person_id'], "team") . ")";
echo "</td>\n";
} else {
echo "<td></td>";
}
echo "<td>" . hierlink('team', $row['account_collection_id'], $row['account_collection_name']) . "</td>\n";
$loc = "";
if ($row['physical_address_id']) {
$loc = '<a href="./?index=byoffice&physical_address_id=' . $row['physical_address_id'] . '">' . $row['office_location'] . '</a>';
}
echo "<td> {$loc} </td>\n";
echo "\t</tr>\n";
}
echo "</table>\n";
if ($showarrow) {
$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']);
$duration = $row['duration'] ? $row['duration'] . " year" . ($row['duration'] > 1 ? 's' : '') : '';
} else {
$printable = date("F j", $row['whence']);
$duration = '';
}
$office = $row['office_location'] ? $row['office_location'] : '';
echo "<tr>\n\t\t<td> {$name} </td>\n\t\t<td> {$printable} </td>\n\t\t<td> {$event} </td>\n\t\t<td> {$duration} </td>\n\t\t<td> {$office} </td>\n\t\t</tr>\n";
