PHP Location::subquery方法代码示例

 /**
  * Selects training locations along with facilities
  *
  * @param unknown_type $num_tiers
  * @return unknown
  */
 public static function selectAllLocations($num_tiers = 4)
 {
     $db = Zend_Db_Table_Abstract::getDefaultAdapter();
     list($field_name, $location_sub_query) = Location::subquery($num_tiers, false, false);
     $sql = 'SELECT training_location.id, training_location.training_location_name, ' . implode(',', $field_name) . '
           FROM training_location LEFT JOIN (' . $location_sub_query . ') as l ON training_location.location_id = l.id 
           WHERE training_location.is_deleted = 0 ORDER BY province_name' . ($num_tiers > 2 ? ', district_name' : '') . ', training_location_name';
     return $db->fetchAll($sql);
 }

 public function searchAction()
 {
     if (!$this->hasACL('edit_employee')) {
         $this->doNoAccessError();
     }
     $criteria = $this->getAllParams();
     if ($criteria['go']) {
         // process search
         $where = array();
         list($a, $location_tier, $location_id) = $this->getLocationCriteriaValues($criteria);
         list($locationFlds, $locationsubquery) = Location::subquery($this->setting('num_location_tiers'), $location_tier, $location_id, true);
         $sql = "SELECT DISTINCT\r\n\t\t\t\t\temployee.*, " . implode(',', $locationFlds) . "\r\n\t\t\t\t\t,CONCAT(supervisor.first_name, CONCAT(' ', supervisor.last_name)) as supervisor,\r\n\t\t\t\t\tqual.qualification_phrase as staff_cadre,\r\n\t\t\t\t\tsite.facility_name,\r\n\t\t\t\t\tcategory.category_phrase as staff_category\r\n\t\t\t\t\tFROM employee LEFT JOIN ({$locationsubquery}) as l ON l.id = employee.location_id\r\n\t\t\t\t\tLEFT JOIN employee supervisor ON supervisor.id = employee.supervisor_id\r\n\t\t\t\t\tLEFT JOIN facility site ON site.id = employee.site_id\r\n\t\t\t\t\tLEFT JOIN employee_qualification_option qual ON qual.id = employee.employee_qualification_option_id\r\n\t\t\t\t\tLEFT JOIN employee_category_option category on category.id = employee.employee_category_option_id\r\n\t\t\t\t\tLEFT JOIN partner ON partner.id = employee.partner_id\r\n\t\t\t\t\t";
         #if ($criteria['partner_id']) $sql    .= ' INNER JOIN partner_to_subpartner subp ON partner.id = ' . $criteria['partner_id'];
         // restricted access?? only show partners by organizers that we have the ACL to view
         #$org_allowed_ids = allowed_org_access_full_list($this); // doesnt have acl 'training_organizer_option_all'
         #if($org_allowed_ids)
         #	$where[] = " partner.organizer_option_id in ($org_allowed_ids) ";
         $locationWhere = $this->getLocationCriteriaWhereClause($criteria, '', '');
         if ($locationWhere) {
             $where[] = $locationWhere;
         }
         if ($criteria['first_name']) {
             $where[] = "employee.first_name   = '{$criteria['first_name']}'";
         }
         if ($criteria['last_name']) {
             $where[] = "employee.last_name    = '{$criteria['last_name']}'";
         }
         if ($criteria['partner_id']) {
             $where[] = 'employee.partner_id   = ' . $criteria['partner_id'];
         }
         //todo
         if ($criteria['facilityInput']) {
             $where[] = 'employee.site_id      = ' . $criteria['facilityInput'];
         }
         if ($criteria['employee_qualification_option_id']) {
             $where[] = 'employee.employee_qualification_option_id    = ' . $criteria['employee_qualification_option_id'];
         }
         if ($criteria['category_option_id']) {
             $where[] = 'employee.staff_category_id = ' . $criteria['category_option_id'];
         }
         if (count($where)) {
             $sql .= ' WHERE ' . implode(' AND ', $where);
         }
         $db = $this->dbfunc();
         $rows = $db->fetchAll($sql);
         $this->viewAssignEscaped('results', $rows);
         $this->viewAssignEscaped('count', count($rows));
     }
     // assign form drop downs
     $helper = new Helper();
     $this->view->assign('status', $status);
     $this->viewAssignEscaped('criteria', $criteria);
     $this->viewAssignEscaped('locations', Location::getAll());
     $this->view->assign('partners', DropDown::generateHtml('partner', 'partner', $criteria['partner_id'], false, $this->view->viewonly, false));
     $this->view->assign('subpartners', DropDown::generateHtml('partner', 'partner', $criteria['partner_id'], false, $this->view->viewonly, false, false, array('name' => 'subpartner_id'), true));
     $this->view->assign('cadres', DropDown::generateHtml('employee_qualification_option', 'qualification_phrase', $criteria['employee_qualification_option_id'], false, $this->view->viewonly, false));
     $this->viewAssignEscaped('sites', $helper->getFacilities());
     $this->view->assign('categories', DropDown::generateHtml('employee_category_option', 'category_phrase', $criteria['employee_category_option_id'], false, $this->view->viewonly, false));
 }

//.........这里部分代码省略.........
         }
         if ($criteria['showOrganizer']) {
             $sql .= ', torg.training_organizer_phrase ';
         }
         if ($criteria['showLevel']) {
             $sql .= ', tlev.training_level_phrase ';
         }
         if ($criteria['showType']) {
             $sql .= ', fto.facility_type_phrase ';
         }
         if ($criteria['showSponsor']) {
             $sql .= ', fso.facility_sponsor_phrase ';
         }
         if ($criteria['showPepfar']) {
             if ($criteria['doCount']) {
                 $sql .= ', tpep.pepfar_category_phrase ';
             } else {
                 $sql .= ', GROUP_CONCAT(DISTINCT tpep.pepfar_category_phrase) as "pepfar_category_phrase" ';
             }
         }
         if ($criteria['showTopic']) {
             if ($criteria['doCount']) {
                 $sql .= ', ttopic.training_topic_phrase ';
             } else {
                 $sql .= ', GROUP_CONCAT(DISTINCT ttopic.training_topic_phrase ORDER BY training_topic_phrase) AS "training_topic_phrase" ';
             }
         }
         if ($criteria['showTot']) {
             //$sql .= ', pt.is_tot ';
             $sql .= ", IF(pt.is_tot,'" . t('Yes') . "','" . t('No') . "') AS is_tot";
         }
         //JOIN with the participants to get facility
         $num_locs = $this->setting('num_location_tiers');
         list($field_name, $location_sub_query) = Location::subquery($num_locs, $location_tier, $location_id, true);
         if ($criteria['doCount']) {
             $sql .= ' FROM (SELECT training.*, fac.person_id as "person_id", fac.facility_id as "facility_id", fac.type_option_id, fac.sponsor_option_id, fac.facility_name as "facility_name" , tto.training_title_phrase AS training_title,training_location.training_location_name, l.' . implode(', l.', $field_name) . '       FROM training  ' . '         JOIN training_title_option tto ON (`training`.training_title_option_id = tto.id)' . '         JOIN training_location ON training.training_location_id = training_location.id ' . '         JOIN (SELECT person_id, facility_name, facility_id, location_id, type_option_id, sponsor_option_id,training_id FROM person JOIN person_to_training ON person_to_training.person_id = person.id ' . '         JOIN facility as f ON person.facility_id = f.id) as fac ON training.id = fac.training_id JOIN (' . $location_sub_query . ') as l ON fac.location_id = l.id WHERE training.is_deleted=0) as pt ';
         } else {
             $sql .= ' FROM (SELECT training.*, fac.facility_id as "facility_id", fac.type_option_id, fac.sponsor_option_id ,fac.facility_name as "facility_name" , tto.training_title_phrase AS training_title,training_location.training_location_name, l.' . implode(', l.', $field_name) . '       FROM training  ' . '         JOIN training_title_option tto ON (`training`.training_title_option_id = tto.id) ' . '         JOIN training_location ON training.training_location_id = training_location.id ' . '         JOIN (SELECT DISTINCT facility_name, facility_id, location_id, training_id, type_option_id, sponsor_option_id FROM person JOIN person_to_training ON person_to_training.person_id = person.id ' . '         JOIN facility as f ON person.facility_id = f.id) as fac ON training.id = fac.training_id JOIN (' . $location_sub_query . ') as l ON fac.location_id = l.id  WHERE training.is_deleted=0) as pt ';
         }
         if ($criteria['showOrganizer']) {
             $sql .= '	JOIN training_organizer_option as torg ON torg.id = pt.training_organizer_option_id ';
         }
         if ($criteria['showLevel']) {
             $sql .= '	JOIN training_level_option as tlev ON tlev.id = pt.training_level_option_id ';
         }
         if ($criteria['showType']) {
             $sql .= '	JOIN facility_type_option as fto ON fto.id = pt.type_option_id ';
         }
         /*if ($criteria ['showCadre']) {
         			$sql .= ' Inner JOIN cadres as cad';
         		}*/
         if ($criteria['showSponsor']) {
             $sql .= '	JOIN facility_sponsor_option as fso ON fso.id = pt.sponsor_option_id ';
         }
         if ($criteria['showPepfar']) {
             $sql .= '	LEFT JOIN (SELECT training_id, ttpco.training_pepfar_categories_option_id, pepfar_category_phrase FROM training_to_training_pepfar_categories_option as ttpco JOIN training_pepfar_categories_option as tpco ON ttpco.training_pepfar_categories_option_id = tpco.id) as tpep ON tpep.training_id = pt.id ';
         }
         if ($criteria['showTopic']) {
             //$sql .= '	LEFT JOIN training_topic_option as ttopic ON ttopic.id = ttopic.training_topic_option_id ';
             $sql .= '	LEFT JOIN (SELECT training_id, ttto.training_topic_option_id, training_topic_phrase FROM training_to_training_topic_option as ttto JOIN training_topic_option as tto ON ttto.training_topic_option_id = tto.id) as ttopic ON ttopic.training_id = pt.id ';
         }
         $where = array(' pt.is_deleted=0 ');
         if ($criteria['training_title_option_id'] or $criteria['training_title_option_id'] === '0') {
             $where[] = ' pt.training_title_option_id = ' . $criteria['training_title_option_id'];
         }
         if ($criteria['training_title_id'] or $criteria['training_title_id'] === '0') {

 public function searchAction()
 {
     if (!$this->hasACL('edit_employee')) {
         $this->doNoAccessError();
     }
     $criteria = $this->getAllParams();
     if ($criteria['go']) {
         // process search
         $where = array();
         list($a, $location_tier, $location_id) = $this->getLocationCriteriaValues($criteria);
         list($locationFlds, $locationsubquery) = Location::subquery($this->setting('num_location_tiers'), $location_tier, $location_id);
         $sql = "SELECT DISTINCT\r\n\t\t\t\t\tpartner.id,partner.partner,partner.location_id," . implode(',', $locationFlds) . "\r\n\t\t\t\t\t,GROUP_CONCAT(funderopt.funder_phrase) as funder\r\n\t\t\t\t\t,GROUP_CONCAT(funders.funder_end_date) as funding_end_date\r\n\t\t\t\t\t,GROUP_CONCAT(subp.partner) as subpartners\r\n\t\t\t\t\tFROM partner LEFT JOIN ({$locationsubquery}) as l  ON l.id = partner.location_id\r\n\t\t\t\t\tLEFT JOIN partner_to_funder funders         ON partner.id = funders.partner_id\r\n\t\t\t\t\tLEFT JOIN partner_funder_option funderopt   ON funders.partner_funder_option_id = funderopt.id\r\n\t\t\t\t\tLEFT JOIN partner_to_subpartner subpartners ON subpartners.partner_id = partner.id\r\n\t\t\t\t\tLEFT JOIN partner subp                      ON subp.id = subpartners.subpartner_id ";
         // restricted access?? only show partners by organizers that we have the ACL to view
         #$org_allowed_ids = allowed_org_access_full_list($this); // doesnt have acl 'training_organizer_option_all'
         #if($org_allowed_ids)
         #	$where[] = " partner.organizer_option_id in ($org_allowed_ids) ";
         #// restricted access?? only show organizers that belong to this site if its a multi org site
         #$site_orgs = allowed_organizer_in_this_site($this); // for sites to host multiple training organizers on one domain
         #if ($site_orgs)
         #	$where[] = " partner.organizer_option_id in ($site_orgs) ";
         $locationWhere = $this->getLocationCriteriaWhereClause($criteria, '', '');
         if ($locationWhere) {
             $where[] = $locationWhere;
         }
         if ($criteria['subpartner_id']) {
             $where[] = 'subpartners.subpartner_id = ' . $criteria['subpartner_id'];
         }
         if ($criteria['partner_id']) {
             $where[] = 'partner.id = ' . $criteria['partner_id'];
         }
         if ($criteria['start_date']) {
             $where[] = 'funder_end_date >= \'' . $this->_date_to_sql($criteria['start_date']) . ' 00:00:00\'';
         }
         if ($criteria['end_date']) {
             $where[] = 'funder_end_date <= \'' . $this->_date_to_sql($criteria['end_date']) . ' 23:59:59\'';
         }
         if (count($where)) {
             $sql .= ' WHERE ' . implode(' AND ', $where);
         }
         $sql .= ' GROUP BY partner.id ';
         $db = $this->dbfunc();
         $rows = $db->fetchAll($sql);
         $this->viewAssignEscaped('results', $rows);
         $this->view->assign('count', count($rows));
     }
     // assign form drop downs
     $this->view->assign('status', $status);
     $this->viewAssignEscaped('criteria', $criteria);
     $this->viewAssignEscaped('locations', Location::getAll());
     $this->view->assign('partners', DropDown::generateHtml('partner', 'partner', $criteria['partner_id'], false, $this->view->viewonly, false));
     $this->view->assign('subpartners', DropDown::generateHtml('partner', 'partner', $criteria['subpartner_id'], false, $this->view->viewonly, false, true, array('name' => 'subpartner_id'), true));
 }

 public function searchAction()
 {
     require_once 'models/table/OptionList.php';
     //facilities list
     $criteria = array();
     list($criteria, $location_tier, $location_id) = $this->getLocationCriteriaValues($criteria);
     $criteria['facility_name'] = $this->getSanParam('facility_name');
     $criteria['type_id'] = $this->getSanParam('type_id');
     $criteria['sponsor_id'] = $this->getSanParam('sponsor_id');
     $criteria['outputType'] = $this->getSanParam('outputType');
     $criteria['go'] = $this->getSanParam('go');
     if ($criteria['go']) {
         $db = Zend_Db_Table_Abstract::getDefaultAdapter();
         $num_locs = $this->setting('num_location_tiers');
         list($field_name, $location_sub_query) = Location::subquery($num_locs, $location_tier, $location_id, true);
         $sql = 'SELECT facility_sponsor_option.facility_sponsor_phrase, facility.location_id,
             facility_type_option.facility_type_phrase,
             facility.facility_name,
             facility.id , ' . implode(',', $field_name) . '
           FROM facility LEFT JOIN (' . $location_sub_query . ') as l ON facility.location_id = l.id
           LEFT OUTER JOIN facility_sponsor_option ON facility.sponsor_option_id = facility_sponsor_option.id
           LEFT OUTER JOIN facility_type_option ON facility.type_option_id = facility_type_option.id ';
         $where = array();
         $where[] = ' facility.is_deleted = 0 ';
         $locationWhere = $this->getLocationCriteriaWhereClause($criteria, '', '');
         if ($locationWhere) {
             $where[] = $locationWhere;
         }
         if ($criteria['type_id'] or $criteria['type_id'] === '0') {
             $where[] = ' type_option_id = "' . $criteria['type_id'] . '"';
         }
         if ($criteria['sponsor_id'] or $criteria['sponsor_id'] === '0') {
             $where[] = ' sponsor_option_id = ' . $criteria['sponsor_id'] . ' ';
         }
         if ($criteria['facility_name']) {
             $where[] = " facility_name = '" . mysql_escape_string($criteria['facility_name']) . "'";
         }
         if ($where) {
             $sql .= ' WHERE ' . implode(' AND ', $where);
         }
         $sql .= " ORDER BY " . " facility_name ASC ";
         $rowArray = $db->fetchAll($sql);
         if ($criteria['outputType']) {
             $this->sendData($rowArray);
         }
         $this->viewAssignEscaped('results', $rowArray);
         $this->view->assign('count', count($rowArray));
     }
     $this->view->assign('criteria', $criteria);
     //facility name
     $nameArray = OptionList::suggestionListValues('facility', 'facility_name', false, false, false);
     $this->viewAssignEscaped('facility_names', $nameArray);
     //locations
     $this->viewAssignEscaped('locations', Location::getAll());
     //facility types
     $typesArray = OptionList::suggestionList('facility_type_option', 'facility_type_phrase', false, false);
     $this->viewAssignEscaped('facility_types', $typesArray);
     //sponsor types
     $sponsorsArray = OptionList::suggestionList('facility_sponsor_option', 'facility_sponsor_phrase', false, false);
     $this->viewAssignEscaped('facility_sponsors', $sponsorsArray);
 }

 public function facilitiesNewFacilitiesAction()
 {
     require_once 'views/helpers/Location.php';
     $db = Zend_Db_Table_Abstract::getDefaultAdapter();
     $criteria = array();
     list($criteria, $location_tier, $location_id) = $this->getLocationCriteriaValues($criteria);
     $num_locs = $this->setting('num_location_tiers');
     list($field_name, $location_sub_query) = Location::subquery($num_locs, $location_tier, $location_id);
     $rows = $db->fetchAll("\r\n\t\t\tselect loc.*,facility.*,types.facility_type_phrase,sponsors.facility_sponsor_phrase,facility.id as id\r\n\t\t\tfrom facility\r\n\t\t\tleft join ({$location_sub_query})    as loc   on loc.id = location_id\r\n\t\t\tleft join facility_type_option     as types on types.id = type_option_id\r\n\t\t\tleft join facility_sponsor_option  as sponsors on sponsors.id = sponsor_option_id\r\n\t\t\twhere facility.approved is null and facility.is_deleted = 0\r\n\t\t\torder by facility_name");
     $go = $this->getSanParam('go');
     if ($go) {
         require_once 'FacilityController.php';
         $c = new FacilityController($this->getRequest(), $this->getResponse());
         $c->searchAction();
     }
     // fill form dropdowns
     $this->viewAssignEscaped('primary_results', $rows);
     // facility name
     $nameArray = OptionList::suggestionListValues('facility', 'facility_name', false, false, false);
     $this->viewAssignEscaped('facility_names', $nameArray);
     // locations
     $this->viewAssignEscaped('locations', Location::getAll());
     // facility types
     $typesArray = OptionList::suggestionList('facility_type_option', 'facility_type_phrase', false, false);
     $this->viewAssignEscaped('facility_types', $typesArray);
     // sponsor types
     $sponsorsArray = OptionList::suggestionList('facility_sponsor_option', 'facility_sponsor_phrase', false, false);
     $this->viewAssignEscaped('facility_sponsors', $sponsorsArray);
 }

    public function doMySearch()
    {
        $helper = new Helper();
        require_once 'models/table/Person.php';
        if (!$this->hasACL('view_people') and !$this->hasACL('edit_people')) {
            $this->doNoAccessError();
        }
        $criteria = array();
        list($criteria, $location_tier, $location_id) = $this->getLocationCriteriaValues($criteria);
        $criteria['facility_name'] = $this->getSanParam('facility_name');
        $criteria['facilityInput'] = $this->getSanParam('facilityInput');
        $criteria['first_name'] = $this->getSanParam('first_name');
        $criteria['last_name'] = $this->getSanParam('last_name');
        $criteria['training_title_option_id'] = $this->getSanParam('training_title_option_id');
        $criteria['persal'] = $this->getSanParam('persal');
        $criteria['person_type'] = $this->getSanParam('person_type');
        $criteria['is_complete'] = $this->getSanParam('is_complete');
        if (!$criteria['person_type']) {
            $criteria['person_type'] = 'is_participant';
        }
        // $criteria['type_id'] = $this->getSanParam('trainer_type_id');
        $criteria['qualification_id'] = $this->getSanParam('qualification_id');
        $criteria['outputType'] = $this->getSanParam('outputType');
        // $criteria['language_id'] = $this->getSanParam('trainer_language_id');
        $criteria['go'] = $this->getSanParam('go');
        if ($criteria['go']) {
            $db = Zend_Db_Table_Abstract::getDefaultAdapter();
            $num_locs = $this->setting('num_location_tiers');
            list($field_name, $location_sub_query) = Location::subquery($num_locs, $location_tier, $location_id, true);
            if ($criteria['person_type'] == 'is_everyone') {
                // left join instead of inner for everyone
                $sql = '
				SELECT DISTINCT p.id, p.last_name, p.middle_name, p.first_name, p.gender, p.birthdate, q.qualification_phrase, ' . implode(',', $field_name) . '
				,q.parent_id, (SELECT COUNT(`comp`.`id`) FROM `comp` WHERE `comp`.`person` = `p`.`id` AND `comp`.`active` = \'Y\') `cmp`,p.comments as "persal",IFNULL(cmpr.res,10) `res` FROM person as p
				LEFT JOIN person_qualification_option as q ON p.primary_qualification_option_id = q.id
				LEFT JOIN facility as f ON p.facility_id = f.id
				LEFT JOIN compres as cmpr ON cmpr.person = p.id AND cmpr.active=\'Y\'
				LEFT JOIN (' . $location_sub_query . ') as l ON f.location_id = l.id  ';
            } else {
                # removing from query, filtering happens on front-end
                #if($criteria ['is_complete']=='is_complete'){
                #	$sql = 'select DISTINCT p.id, p.last_name, p.middle_name, p.first_name, p.gender, p.birthdate, q.qualification_phrase, '.implode(',',$field_name).',q.parent_id, (SELECT COUNT(`comp`.`id`) FROM `comp` WHERE `comp`.`person` = `p`.`id` AND `comp`.`active` = \'Y\') `cmp`,p.comments as "persal", cmpr.res from person as p, person_qualification_option as q, facility as f, ('.$location_sub_query.') as l, compres as cmpr';
                #} else {
                $sql = 'select DISTINCT p.id, p.last_name, p.middle_name, p.first_name, p.gender, p.birthdate, q.qualification_phrase, ' . implode(',', $field_name) . ',q.parent_id, (SELECT COUNT(`comp`.`id`) FROM `comp` WHERE `comp`.`person` = `p`.`id` AND `comp`.`active` = \'Y\') `cmp`,p.comments as "persal", IFNULL((SELECT `res` FROM `compres` WHERE `compres`.`person` = `p`.`id` AND `compres`.`active` = \'Y\'),10) `res` from person as p, person_qualification_option as q, facility as f, (' . $location_sub_query . ') as l';
                #}
            }
            if ($criteria['person_type'] == 'is_participant') {
                $sql .= ', person_to_training as ptt ';
            }
            if ($criteria['person_type'] == 'is_trainer') {
                $sql .= ', trainer as trn';
                if ($criteria['training_title_option_id']) {
                    $sql .= ', training_to_trainer as ttt ';
                }
            }
            $where = array('p.is_deleted = 0');
            /* 'f.location_id = l.id'); */
            // bugfix - is shrinkings results
            if ($criteria['person_type'] != 'is_everyone') {
                // was left joined (see above)
                $where[] = 'p.primary_qualification_option_id = q.id and p.facility_id = f.id and f.location_id = l.id ';
            }
            $locationWhere = $this->getLocationCriteriaWhereClause($criteria, '', 'pt');
            if ($locationWhere) {
                $where[] = $locationWhere;
            }
            # removing from query, filtering happens on front-end
            #if($criteria ['is_complete']=='is_complete'){
            #	$where []= 'cmpr.person = p.id';
            #}
            if ($criteria['person_type'] == 'is_participant') {
                $where[] = 'p.id = ptt.person_id ';
            }
            if ($criteria['person_type'] == 'is_trainer') {
                $where[] = 'p.id = trn.person_id ';
            }
            if ($criteria['person_type'] == 'is_unattached_person') {
                $where[] = 'p.id NOT IN (SELECT person_id FROM trainer) AND  p.id NOT IN (SELECT person_id FROM person_to_training)  ';
            }
            if ($criteria['facilityInput']) {
                $where[] = ' p.facility_id = "' . $criteria['facilityInput'] . '"';
            }
            if ($criteria['training_title_option_id'] or $criteria['training_title_option_id'] === '0') {
                $sql .= ', training as tr  ';
                if ($criteria['person_type'] == 'is_participant') {
                    $where[] = ' p.id = ptt.person_id AND ptt.training_id = tr.id AND tr.training_title_option_id = ' . $criteria['training_title_option_id'] . ' ';
                }
                if ($criteria['person_type'] == 'is_trainer') {
                    $where[] = ' p.id = trn.person_id AND trn.person_id = ttt.trainer_id AND ttt.training_id = tr.id AND tr.training_title_option_id = ' . $criteria['training_title_option_id'] . ' ';
                }
            }
            if ($criteria['qualification_id'] or $criteria['qualification_id'] === '0') {
                $where[] = '(primary_qualification_option_id = ' . $criteria['qualification_id'] . ' OR primary_qualification_option_id IN (SELECT id FROM person_qualification_option WHERE parent_id = ' . $criteria['qualification_id'] . ')) ';
            }
            if ($criteria['first_name']) {
                $where[] = 'p.first_name LIKE "' . $criteria['first_name'] . '%"';
            }
            if ($criteria['last_name']) {
                $where[] = 'p.last_name LIKE "' . $criteria['last_name'] . '%"';
            }
//.........这里部分代码省略.........

 public function partnersAction()
 {
     require_once 'models/table/Helper.php';
     require_once 'views/helpers/FormHelper.php';
     require_once 'views/helpers/DropDown.php';
     require_once 'views/helpers/Location.php';
     require_once 'views/helpers/CheckBoxes.php';
     require_once 'views/helpers/TrainingViewHelper.php';
     $criteria = $this->getAllParams();
     if ($criteria['go']) {
         $where = array();
         $criteria['last_selected_rgn'] = regionFiltersGetLastID('', $criteria);
         list($a, $location_tier, $location_id) = $this->getLocationCriteriaValues($criteria);
         list($locationFlds, $locationsubquery) = Location::subquery($this->setting('num_location_tiers'), $location_tier, $location_id);
         $sql = "SELECT\r\n\t\t\t\t\tpartner.*,\r\n\t\t\t\t\tpartner.id,partner.partner,partner.location_id," . implode(',', $locationFlds) . "\r\n\t\t\t\t\t,GROUP_CONCAT(distinct facility.facility_name) as facilities\r\n\t\t\t\t\t,CASE WHEN annual_cost REGEXP '[^!0-9,\\.][0-9\\.,]+' THEN SUBSTRING(annual_cost, 2) ELSE annual_cost END AS 'annual_cost_to_compare'\r\n\t\t\t\t\t,COUNT(e.id) AS pcnt\r\n\t\t\t\t\tFROM partner LEFT JOIN ({$locationsubquery}) as l ON l.id = partner.location_id\r\n\t\t\t\t\tLEFT JOIN partner_to_funder funders ON partner.id = funders.partner_id\r\n\t\t\t\t\tLEFT JOIN partner_funder_option funderopt ON funders.partner_funder_option_id = funderopt.id\r\n\t\t\t\t\tLEFT JOIN partner_to_subpartner subpartners ON subpartners.partner_id = partner.id\r\n\t\t\t\t\tLEFT JOIN employee e on e.partner_id = partner.id\r\n\t\t\t\t\tLEFT JOIN facility ON e.site_id = facility.id\r\n\t\t\t\t\t";
         if ($criteria['facility_type_option_id']) {
             $sql .= " LEFT JOIN facility_type_option fto ON fto.id = facility.type_option_id ";
         }
         // restricted access?? only show partners by organizers that we have the ACL to view
         #$org_allowed_ids = allowed_org_access_full_list($this); // doesnt have acl 'training_organizer_option_all'
         #if ($org_allowed_ids)                             $where[] = "partner.organizer_option_id in ($org_allowed_ids)";
         // restricted access?? only show organizers that belong to this site if its a multi org site
         $site_orgs = allowed_organizer_in_this_site($this);
         // for sites to host multiple training organizers on one domain
         if ($site_orgs) {
             $where[] = "partner.organizer_option_id in ({$site_orgs})";
         }
         // criteria
         if ($criteria['partner_id']) {
             $where[] = 'partner.id = ' . $criteria['partner_id'];
         }
         if ($criteria['last_selected_rgn']) {
             $where[] = 'province_name is not null';
         }
         // bugfix - location subquery is not working like a inner join or where, not sure why
         if ($criteria['facilityInput']) {
             $where[] = 'facility.id = ' . $criteria['facilityInput'];
         }
         if ($criteria['facility_type_option_id']) {
             $where[] = 'facility.type_option_id = ' . $criteria['facility_type_option_id'];
         }
         if ($criteria['employee_qualification_option_id']) {
             $where[] = 'employee_qualification_option_id = ' . $criteria['employee_qualification_option_id'];
         }
         if ($criteria['employee_category_option_id']) {
             $where[] = 'employee_category_option_id = ' . $criteria['employee_category_option_id'];
         }
         if ($criteria['hours_min']) {
             $where[] = 'e.funded_hours_per_week >=' . $criteria['hours_min'];
         }
         if ($criteria['hours_max']) {
             $where[] = 'e.funded_hours_per_week <=' . $criteria['hours_min'];
         }
         if ($criteria['cost_min']) {
             $where[] = 'e.annual_cost_to_compare >=' . $criteria['cost_min'];
         }
         if ($criteria['cost_max']) {
             $where[] = 'e.annual_cost_to_compare <=' . $criteria['cost_max'];
         }
         if ($criteria['employee_role_option_id']) {
             $where[] = 'funder_end_date >= \'' . $this->_date_to_sql($criteria['start_date']) . ' 00:00:00\'';
         }
         if ($criteria['partner_importance_option_id']) {
             $where[] = 'partner_importance_option_id = ' . $criteria['partner_importance_option_id'];
         }
         if ($criteria['start_date']) {
             $where[] = 'funder_end_date >= \'' . $this->_date_to_sql($criteria['start_date']) . ' 00:00:00\'';
         }
         if ($criteria['end_date']) {
             $where[] = 'funder_end_date <= \'' . $this->_date_to_sql($criteria['end_date']) . ' 23:59:59\'';
         }
         if ($criteria['employee_transition_option_id']) {
             $where[] = 'employee_transition_option_id = ' . $criteria['employee_transition_option_id'];
         }
         if ($criteria['transition_confirmed']) {
             $where[] = 'transition_confirmed = 1';
         }
         if (count($where)) {
             $sql .= ' WHERE ' . implode(' AND ', $where);
         }
         $sql .= ' GROUP BY partner.id ';
         $db = $this->dbfunc();
         $rowArray = $db->fetchAll($sql);
         $this->viewAssignEscaped('results', $rowArray);
         $this->view->assign('count', count($rowArray));
         if ($criteria['outputType']) {
             $this->sendData($this->reportHeaders(false, $rowArray));
         }
     }
     // assign form drop downs
     $this->view->assign('status', $status);
     $this->view->assign('criteria', $criteria);
     $this->view->assign('pageTitle', t('Reports'));
     $this->viewAssignEscaped('locations', Location::getAll());
     $this->view->assign('partners', DropDown::generateHtml('partner', 'partner', $criteria['partner_id'], false, $this->view->viewonly, false));
     //table, col, selected_value
     $this->view->assign('subpartners', DropDown::generateHtml('partner', 'partner', $criteria['subpartner_id'], false, $this->view->viewonly, false, true, array('name' => 'subpartner_id'), true));
     $this->view->assign('importance', DropDown::generateHtml('partner_importance_option', 'importance_phrase', $criteria['partner_importance_option_id'], false, $this->view->viewonly, false));
     $this->view->assign('transitions', DropDown::generateHtml('employee_transition_option', 'transition_phrase', $criteria['employee_transition_option_id'], false, $this->view->viewonly, false));
     $this->view->assign('incomingPartners', DropDown::generateHtml('partner', 'partner', $criteria['incoming_partner'], false, $this->view->viewonly, false, true, array('name' => 'incoming_partner'), true));
//.........这里部分代码省略.........