Example #1
0
<?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':
Example #2
0
 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'});
                 }
         }
     }
 }