<?php include "personlib.php"; $dbconn = dbauth::connect('directory', null, $_SERVER['REMOTE_USER']) or die("Could not connect: " . pg_last_error()); $index = isset($_GET['index']) ? $_GET['index'] : 'random'; $select_fields = "\n\t\tdistinct 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\tpc.position_title,\n\t\tc.company_name,\n\t\tc.company_id,\n\t\tpi.person_image_id,\n\t\tpc.manager_person_id,\n\t\tpc.person_company_relation,\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\tu.account_collection_id,\n\t\tu.account_collection_name,\n\t\tnumreports.tally as num_reports,\n\t\tofc.display_label as office_location,\n\t\tofc.physical_address_id\n"; $query_tables = "\n\t FROM person p\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\n\t\t\tusing (person_id)\n\t \tinner join company c\n\t\t\tusing (company_id)\n\t\tinner join v_corp_family_account a\n\t\t\ton ( p.person_id = a.person_id\n\t\t\tand pc.company_id = a.company_id\n\t\t\tand a.account_role = 'primary'\n\t\t\tand a.is_enabled = 'Y' )\n\t\tleft join (\n\t\t\tselect ac.*, account_id\n\t\t\tFROM account_collection ac\n\t\t\t\tINNER JOIN account_collection_account a\n\t\t\t\t\tUSING (account_collection_id)\n\t\t\tWHERE account_collection_type = 'department'\n\t\t) u USING (account_id)\n\t\tleft join (\n\t\t\tselect\tpi.*, piu.person_image_usage\n\t\t\t from\tperson_image pi\n\t\t\t\t\tinner join person_image_usage piu\n\t\t\t\t\t\ton pi.person_image_id = piu.person_image_id\n\t\t\t\t\t\tand piu.person_image_usage = 'corpdirectory'\n\t\t) pi\n\t\t\ton pi.person_id = p.person_id\n\t\tleft join person mgrp\n\t\t\ton pc.manager_person_id = mgrp.person_id\n\t\tleft join ( -- this probably needs to be smarter\n\t\t\tselect 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\t) numreports on numreports.person_id = p.person_id \n\t\tleft join (\n\t\t\tselect pl.person_id, pa.physical_address_id,\n\t\t\t\tpa.display_label\n\t\t\t from\tperson_location pl\n\t\t\t \tinner join physical_address pa\n\t\t\t\t\ton pl.physical_address_id = \n\t\t\t\t\t\tpa.physical_address_id\n\t\t\twhere\tpl.person_location_type = 'office'\n\t\t\torder by site_rank\n\t\t\t) ofc on ofc.person_id = p.person_id\n"; $query_firstpart = "\n\tSELECT {$select_fields}\n\t\t{$query_tables}"; $orderby = "\n\torder by\n\t\tperson_company_relation desc,\n\t\tcoalesce(p.preferred_last_name, p.last_name),\n\t\tcoalesce(p.preferred_first_name, p.first_name),\n\t\tp.person_id\n"; $limit = ""; $address = isset($_GET['physical_address_id']) ? $_GET['physical_address_id'] : null; $showarrow = 0; $style = 'peoplelist'; switch ($index) { case 'random': $query = "\n\t\t\tselect p.person_id {$query_tables} order by random() limit 1\n\t\t"; $result = pg_query($query) or die('Query failed: ' . pg_last_error()); $row = pg_fetch_array($result, null, PGSQL_ASSOC); if ($row && $row['person_id']) { Header('Location: ' . personlinkurl($row['person_id'], 'random=yes')); } pg_free_result($result); pg_close($dbconn); exit(0); break; case 'reports': $who = $_GET['person_id']; $query = "\n\t\t {$query_firstpart}\n\t\t where pc.manager_person_id = \$1\n\t\t and pc.person_company_status = 'enabled'\n\t\t {$orderby}\n\t\t"; $result = pg_query_params($query, array($who)) or die('Query failed: ' . pg_last_error()); break; case 'team':
public static function connect($app, $instance = null, $login = null, $flags = null) { $record = dbauth::find_and_parse_auth($app, $instance); if (!isset($record)) { return null; } $dbspecs = $record->{'database'}; if (!isset($dbspecs)) { return null; } foreach ($dbspecs as $dbspec) { switch ($dbspec->{'DBType'}) { case 'postgresql': $connstr = ""; if (isset($dbspec->{'DBHost'})) { $connstr .= " host=" . $dbspec->{'DBHost'}; } if (isset($dbspec->{'DBPort'})) { $connstr .= " port=" . $dbspec->{'DBPort'}; } if (isset($dbspec->{'DBName'})) { $connstr .= " dbname=" . $dbspec->{'DBName'}; } if (isset($dbspec->{'Service'})) { $connstr .= " service=" . $dbspec->{'Service'}; } if (isset($dbspec->{'SSLMode'})) { $connstr .= " sslmode=" . $dbspec->{'sslmode'}; } if (isset($dbspec->{'Options'})) { $connstr .= " sslmode=" . $dbspec->{'Options'}; } if (isset($dbspec->{'Username'})) { $connstr .= " user="******" password="******"unset dbtype"); // XXX -- set an error and return null } else { die("Unknown database " . $dbspec->{'DBType'}); } } } }