/** * Get topics with qualification recommendations */ public static function getTopics() { $tableObj = new TrainingRecommend(); $select = $tableObj->select()->from(array('tr' => $tableObj->_name), array('training_topic_option_id'))->setIntegrityCheck(false)->join(array('tt' => 'training_topic_option'), "tt.id = tr.training_topic_option_id", array('id', 'training_topic_phrase'))->group('training_topic_option_id')->order("training_topic_phrase"); return $tableObj->fetchAll($select); }
public function listByTrainingRecommendPersonAction() { // recommended classes based on person id require_once 'models/table/TrainingRecommend.php'; $trainingRecObj = new TrainingRecommend(); $rowArray = $trainingRecObj->getRecommendedClassesforPerson($this->_getParam('id')); // person id $this->sendData($rowArray); }
public function doAddEditView() { try { //validate $status = ValidationContainer::instance(); require_once 'models/table/OptionList.php'; require_once 'models/table/MultiOptionList.php'; require_once 'models/table/Location.php'; $request = $this->getRequest(); $validateOnly = $request->isXmlHttpRequest(); $person_id = $this->getSanParam('id'); $personObj = new Person(); $personrow = $personObj->findOrCreate($person_id); $personArray = $personrow->toArray(); $helper = new Helper(); $ssl = $helper->getSkillSmartLookups(); $this->view->assign('skillsmart', $ssl); // nationality dropdown data $this->view->assign('lookupnationalities', $helper->getNationalities()); $train = $helper->getPersonTraining($person_id); $this->view->assign('persontraining', $train); //locations $locations = Location::getAll(); $this->viewAssignEscaped('locations', $locations); if ($validateOnly) { $this->setNoRenderer(); } // Figure out reason code "Other" for checking posted data, // and for enabling/disabling reason_other field $other_reason_option_id = -1; $db = Zend_Db_Table_Abstract::getDefaultAdapter(); $sql = "SELECT * FROM person_attend_reason_option where LCASE(attend_reason_phrase) LIKE '%other%'"; $rowArray = $db->fetchAll($sql); if ($rowArray) { $other_reason_option_id = $rowArray[0]['id']; } $this->viewAssignEscaped('other_reason_option_id', $other_reason_option_id); if ($request->isPost()) { $errortext = ""; if ($dupe_id = $this->getSanParam('dupe_id')) { if ($this->getSanParam('maketrainer')) { // require user to add trainer info $status->setRedirect('/person/edit/id/' . $dupe_id . '/trainingredirect/' . $this->getSanParam('trainingredirect') . '/maketrainer/1'); } else { if ($this->_getParam('trainingredirect')) { $status->setStatusMessage(t('The person was saved. Refreshing history...')); $_SESSION['status'] = t('The person was saved.'); $this->trainingRedirect($dupe_id); } else { $status->setRedirect('/person/edit/id/' . $dupe_id); } } return; } $status->checkRequired($this, 'first_name', $this->tr('First Name')); $status->checkRequired($this, 'last_name', $this->tr('Last Name')); $status->checkRequired($this, 'primary_qualification_option_id', t('Professional qualification')); if ($this->setting('display_gender') != '0') { $status->checkRequired($this, 'gender', t('Gender')); } if ($this->setting('display_mod_skillsmart')) { //$status->checkRequired ( $this, 'occupational_category_id', t ( 'Occupational category' ) ); if ($this->getSanParam('govemp_option_id')) { //$status->checkRequired ( $this, 'govemp_option_id', t ( 'Government Employee' ) ); //$status->checkRequired ( $this, 'occupational_category_id', t ( 'Occupational category' ) ); $status->checkRequired($this, 'persal_number', t('Persal Number')); } } else { $status->checkRequired($this, 'primary_qualification_option_id', t('Professional qualification')); } $birthParam = @$this->getSanParam('birth-year') . '-' . @$this->getSanParam('birth-month') . '-' . @$this->getSanParam('birth-day'); if ($birthParam !== '--' and $birthParam !== '0000-00-00') { $status->isValidDate($this, 'birth-day', t('Birthdate'), $birthParam); } //trainer only if ($this->getSanParam('is_trainer') || $this->getSanParam('active_trainer_option_id') || $this->getSanParam('trainer_type_option_id')) { $status->checkRequired($this, 'trainer_type_option_id', t('Trainer') . ' ' . t('type')); // 10/02/2011 Sean Smith: Removed at client request. //if ($this->setting ( 'display_trainer_affiliations' )) { // $status->checkRequired ( $this, 'trainer_affiliation_option_id', t ( 'Trainer').' '.t('affiliation' ) ); //} //at least one skill $status->checkRequired($this, 'trainer_skill_id', t('Trainer') . ' ' . t('skill')); } // Check for manual reason for attending entry (if pulldown reason is 'other') if ($this->setting('display_attend_reason')) { //if ($status->checkRequired ( $this, 'attend_reason_option_id', t ( 'Reason For Attending' ))) { $reason_id = $this->getSanParam('attend_reason_option_id'); $other_reason = $this->getSanParam('attend_reason_other'); if (($reason_id || $reason_id == 0) && $other_reason_option_id >= 0) { if ($reason_id == $other_reason_option_id) { if ($other_reason == "") { $status->addError('attend_reason_other', t('Enter a reason, or select a different reason above.')); $errortext .= "Enter a reason, or select a different reason above.<br>"; error_log("Enter a reason, or select a different reason above."); } } else { if ($other_reason != "") { $status->addError('attend_reason_other', t('You can not type in a reason with the reason selected above.')); $errortext .= "You can not type in a reason with the reason selected above.<br>"; error_log("You can not type in a reason with the reason selected above."); } } } //} } //check facility if ($status->checkRequired($this, 'facilityInput', t('Facility'))) { $facility_id = $this->getSanParam('facilityInput'); if (is_array($facility_id)) { $fac_arr = array(); foreach ($facility_id as $fac_id) { if ($strrpos = strrpos($fac_id, '_')) { $fac_arr[] = substr($fac_id, $strrpos + 1); } } $personrow->multi_facility_ids = json_encode($fac_arr); $facility_id = current($facility_id); } else { $personrow->multi_facility_ids = ''; } if ($strrpos = strrpos($facility_id, '_')) { $facility_id = array_pop(explode('_', $facility_id)); } //find by name if ($facility_id) { $facilityByName = new Facility(); $row = $facilityByName->fetchRow('id = ' . $facility_id); //$row = $facilityByName->fetchRow($facilityByName->select()->where('facility_name = ?', $this->getSanParam('facilityInput'))); } if (@$row->id) { $personrow->facility_id = $row->id; } else { $status->addError('facilityInput', t('That facility name could not be found.')); $errortext .= "That facility name could not be found.<br>"; error_log("That facility name could not be found."); } } //get home city name $city_id = false; $criteria = $this->_getAllParams(); require_once 'views/helpers/Location.php'; $home_city_parent_id = regionFiltersGetLastID('home', $criteria); if ($criteria['home_city'] && !$criteria['is_new_home_city']) { $city_id = Location::verifyHierarchy($criteria['home_city'], $home_city_parent_id, $this->setting('num_location_tiers')); if ($city_id === false) { $status->addError('home_city', t("That city does not appear to be located in the chosen region. If you want to create a new city, check the new city box.")); $errortext .= "That city does not appear to be located in the chosen region. If you want to create a new city, check the new city box.<br>"; error_log("That city does not appear to be located in the chosen region. If you want to create a new city, check the new city box."); } } if ($status->hasError()) { foreach ($status->messages as $k => $v) { $errortext .= $v . "<br>"; } $status->setStatusMessage(t('The person could not be saved. <br>' . $errortext)); } else { $personrow = self::fillFromArray($personrow, $this->_getAllParams()); if ($city_id === false && $this->getSanParam('is_new_home_city')) { $city_id = Location::insertIfNotFound($criteria['home_city'], $home_city_parent_id, $this->setting('num_location_tiers')); if ($city_id === false) { $status->addError('home_city', t('Could not save that city.')); } } if ($city_id) { $personrow->home_location_id = $city_id; } else { $home_location_id = Location::verifyHierarchy($criteria['home_city'], $home_city_parent_id, $this->setting('num_location_tiers')); if ($home_location_id) { $personrow->home_location_id = $home_location_id; } } //these are transitionary database fields, will go away soon // $personrow->home_district_id = null; // $personrow->home_province_id = null; if (!$personrow->home_city) { $personrow->home_city = ''; } // bugfix, field cannot be null. if ($this->getSanParam('active')) { $personrow->active = 'active'; } else { $personrow->active = 'inactive'; } $personrow->birthdate = @$this->getSanParam('birth-year') . '-' . @$this->getSanParam('birth-month') . '-' . @$this->getSanParam('birth-day'); //lookup custom 1 and 2 if ($this->getSanParam('custom1Input')) { $id = OptionList::insertIfNotFound('person_custom_1_option', 'custom1_phrase', $this->getSanParam('custom1Input')); $personrow->person_custom_1_option_id = $id; } else { $personrow->person_custom_1_option_id = null; } if ($this->getSanParam('custom2Input')) { $id = OptionList::insertIfNotFound('person_custom_2_option', 'custom2_phrase', $this->getSanParam('custom2Input')); $personrow->person_custom_2_option_id = $id; } else { $personrow->person_custom_2_option_id = null; } if ($this->setting('display_mod_skillsmart')) { $personrow->govemp_option_id = $this->getSanParam('govemp_option_id'); $personrow->occupational_category_id = $this->getSanParam('occupational_category_id'); $personrow->persal_number = $this->getSanParam('persal_number'); $personrow->bodies_id = $this->getSanParam('professionalbodies_id'); $personrow->race_option_id = $this->getSanParam('race_option_id'); $personrow->disability_option_id = $this->getSanParam('disability_option_id'); $personrow->professional_reg_number = $this->getSanParam('professional_reg_number'); $personrow->nationality_id = $this->getSanParam('nationality_id'); $personrow->nurse_training_id = $this->getSanParam('nurse_training_id'); $personrow->care_start_year = $this->getSanParam('care_start_year'); $personrow->timespent_rank_pregnant = $this->getSanParam('timespent_rank_pregnant'); $personrow->timespent_rank_adults = $this->getSanParam('timespent_rank_adults'); $personrow->timespent_rank_children = $this->getSanParam('timespent_rank_children'); $personrow->timespent_rank_pregnant_pct = $this->getSanParam('timespent_rank_pregnant_pct'); $personrow->timespent_rank_adults_pct = $this->getSanParam('timespent_rank_adults_pct'); $personrow->timespent_rank_children_pct = $this->getSanParam('timespent_rank_children_pct'); $personrow->supervised_id = $this->getSanParam('supervised_id'); $personrow->supervision_frequency_id = $this->getSanParam('supervision_frequency_id'); $personrow->supervisors_profession = $this->getSanParam('supervisors_profession'); $personrow->facilitydepartment_id = $this->getSanParam('facilitydepartment_id'); $training_recieved_arr = array(); $training_recieved_data = ''; $training_recieved_results = array(); foreach ($ssl['training'] as $trainingnode) { $training_recieved_arr[$trainingnode['id']] = $trainingnode['label']; } // build and insert training recieved vars in json foreach ($_POST as $postvar => $postval) { if (strstr($postvar, 'trainingrecieved_') && (isset($_POST[$postvar]) && $_POST[$postvar] != '')) { $recv_str = ''; $recv_num = explode('_', $postvar); if (isset($training_recieved_arr[intval($recv_num[1])])) { $recv_str = $training_recieved_arr[intval($recv_num[1])]; $training_recieved_results[$recv_str][$recv_num[2]] = $postval; } } } if (!empty($training_recieved_results)) { $training_recieved_data = json_encode($training_recieved_results); } $personrow->training_recieved_data = $training_recieved_data; //$personrow->comments_skillsmart = $this->getSanParam('govemp_option_id');//0 /* $personrow->comments_skillsmart = $personrow->comments_skillsmart."§".$this->getSanParam('custom1Input');//1 $personrow->comments_skillsmart = $personrow->comments_skillsmart."§".$this->getSanParam('race_option_id');//2 $personrow->comments_skillsmart = $personrow->comments_skillsmart."§".$this->getSanParam('disability_option_id');//3 $personrow->comments_skillsmart = $personrow->comments_skillsmart."§".$this->getSanParam('disability');//4 $personrow->comments_skillsmart = $personrow->comments_skillsmart."§".$this->getSanParam('custom2Input');//5 $personrow->comments_skillsmart = $personrow->comments_skillsmart."§".$this->getSanParam('rank1_option_id');//6 $personrow->comments_skillsmart = $personrow->comments_skillsmart."§".$this->getSanParam('rank2_option_id');//7 $personrow->comments_skillsmart = $personrow->comments_skillsmart."§".$this->getSanParam('rank3_option_id');//8 $personrow->comments_skillsmart = $personrow->comments_skillsmart."§".$this->getSanParam('percent1');//9 $personrow->comments_skillsmart = $personrow->comments_skillsmart."§".$this->getSanParam('percent2');//10 $personrow->comments_skillsmart = $personrow->comments_skillsmart."§".$this->getSanParam('percent3');//11 $personrow->comments_skillsmart = $personrow->comments_skillsmart."§".$this->getSanParam('supervised_option_id');//12 $personrow->comments_skillsmart = $personrow->comments_skillsmart."§".$this->getSanParam('sfreq_option_id');//13 $personrow->comments_skillsmart = $personrow->comments_skillsmart."§".$this->getSanParam('specifyfreqinput');//14 $personrow->comments_skillsmart = $personrow->comments_skillsmart."§".$this->getSanParam('svoccu');//15 $personrow->comments_skillsmart = $personrow->comments_skillsmart."§".$this->getSanParam('texttrainother');//16 $personrow->comments_skillsmart = $personrow->comments_skillsmart."§".$this->getSanParam('checktrainother');//17 $personrow->comments_skillsmart = $personrow->comments_skillsmart."§".$this->getSanParam('selecttrainother');//18 $personrow->comments_skillsmart = $personrow->comments_skillsmart."§".$this->getSanParam('texttrainotherother');//19 $personrow->comments_skillsmart = $personrow->comments_skillsmart."§".$this->getSanParam('specifyoccupationinput');//20 $personrow->comments_skillsmart = $personrow->comments_skillsmart."§".$this->getSanParam('trainer_type_option_id1');//21 $personrow->comments_skillsmart = $personrow->comments_skillsmart."§".$this->getSanParam('texttrainother0');//22 $personrow->comments_skillsmart = $personrow->comments_skillsmart."§".$this->getSanParam('checktrainother0');//23 $personrow->comments_skillsmart = $personrow->comments_skillsmart."§".$this->getSanParam('selecttrainother0');//24 $personrow->comments_skillsmart = $personrow->comments_skillsmart."§".$this->getSanParam('texttrainotherother0');//25 $personrow->comments_skillsmart = $personrow->comments_skillsmart."§".$this->getSanParam('texttrainother1');//26 $personrow->comments_skillsmart = $personrow->comments_skillsmart."§".$this->getSanParam('checktrainother1');//27 $personrow->comments_skillsmart = $personrow->comments_skillsmart."§".$this->getSanParam('selecttrainother1');//28 $personrow->comments_skillsmart = $personrow->comments_skillsmart."§".$this->getSanParam('texttrainotherother1');//29 $personrow->comments_skillsmart = $personrow->comments_skillsmart."§".$this->getSanParam('texttrainother2');//30 $personrow->comments_skillsmart = $personrow->comments_skillsmart."§".$this->getSanParam('checktrainother2');//31 $personrow->comments_skillsmart = $personrow->comments_skillsmart."§".$this->getSanParam('selecttrainother2');//32 $personrow->comments_skillsmart = $personrow->comments_skillsmart."§".$this->getSanParam('texttrainotherother2');//33 $personrow->comments_skillsmart = $personrow->comments_skillsmart."§".$this->getSanParam('texttrainother3');//34 $personrow->comments_skillsmart = $personrow->comments_skillsmart."§".$this->getSanParam('checktrainother3');//35 $personrow->comments_skillsmart = $personrow->comments_skillsmart."§".$this->getSanParam('selecttrainother3');//36 $personrow->comments_skillsmart = $personrow->comments_skillsmart."§".$this->getSanParam('texttrainotherother3');//37 $personrow->comments_skillsmart = $personrow->comments_skillsmart."§".$this->getSanParam('texttrainother4');//38 $personrow->comments_skillsmart = $personrow->comments_skillsmart."§".$this->getSanParam('checktrainother4');//39 $personrow->comments_skillsmart = $personrow->comments_skillsmart."§".$this->getSanParam('selecttrainother4');//40 $personrow->comments_skillsmart = $personrow->comments_skillsmart."§".$this->getSanParam('texttrainotherother4');//41 $personrow->comments_skillsmart = $personrow->comments_skillsmart."§".$this->getSanParam('texttrainother5');//42 $personrow->comments_skillsmart = $personrow->comments_skillsmart."§".$this->getSanParam('checktrainother5');//43 $personrow->comments_skillsmart = $personrow->comments_skillsmart."§".$this->getSanParam('selecttrainother5');//44 $personrow->comments_skillsmart = $personrow->comments_skillsmart."§".$this->getSanParam('texttrainotherother5');//45 $personrow->comments_skillsmart = $personrow->comments_skillsmart."§".$this->getSanParam('texttrainother6');//46 $personrow->comments_skillsmart = $personrow->comments_skillsmart."§".$this->getSanParam('checktrainother6');//47 $personrow->comments_skillsmart = $personrow->comments_skillsmart."§".$this->getSanParam('selecttrainother6');//48 $personrow->comments_skillsmart = $personrow->comments_skillsmart."§".$this->getSanParam('texttrainotherother6');//49 $personrow->comments_skillsmart = $personrow->comments_skillsmart."§".$this->getSanParam('texttrainother7');//50 $personrow->comments_skillsmart = $personrow->comments_skillsmart."§".$this->getSanParam('checktrainother7');//51 $personrow->comments_skillsmart = $personrow->comments_skillsmart."§".$this->getSanParam('selecttrainother7');//52 $personrow->comments_skillsmart = $personrow->comments_skillsmart."§".$this->getSanParam('texttrainotherother7');//53 $personrow->comments_skillsmart = $personrow->comments_skillsmart."§".$this->getSanParam('texttrainother8');//54 $personrow->comments_skillsmart = $personrow->comments_skillsmart."§".$this->getSanParam('checktrainother8');//55 $personrow->comments_skillsmart = $personrow->comments_skillsmart."§".$this->getSanParam('selecttrainother8');//56 $personrow->comments_skillsmart = $personrow->comments_skillsmart."§".$this->getSanParam('texttrainotherother8');//57 $personrow->comments_skillsmart = $personrow->comments_skillsmart."§".$this->getSanParam('bodies_id');//58 $personrow->comments_skillsmart = $personrow->comments_skillsmart."§".$this->getSanParam('nationality_id');//59 */ if ($person_id) { $trainsaved = array(); $train = $this->getSanParam('train'); foreach ($train as $key => $t) { if (isset($t['check'])) { if ($t['originalid'] != 0) { // UPDATING EXISTING // moving this - cant really use $person_id before $personrow->save on adding a new user... probably should happen later or only on edits... im not sure $trainid = $t['originalid']; $query = "UPDATE link_person_training SET\n\t\t\t\t\t\t\t\t\tpersonid = '" . $person_id . "',\n\t\t\t\t\t\t\t\t\tyear = '" . addslashes($t['year']) . "',\n\t\t\t\t\t\t\t\t\tinstitution = '" . addslashes($t['text']) . "',\n\t\t\t\t\t\t\t\t\tothername = '' WHERE id = " . $trainid; error_log($query); $db->query($query); } else { // ADDING NEW $query = "INSERT INTO link_person_training SET\n\t\t\t\t\t\t\t\t\t\t\tpersonid = '" . $person_id . "',\n\t\t\t\t\t\t\t\t\t\t\ttrainingid = '" . addslashes($key) . "',\n\t\t\t\t\t\t\t\t\t\t\tyear = '" . addslashes($t['year']) . "',\n\t\t\t\t\t\t\t\t\t\t\tinstitution = '" . addslashes($t['text']) . "',\n\t\t\t\t\t\t\t\t\t\t\tothername = ''"; error_log($query); $db->query($query); $trainid = $db->lastInsertId(); } $trainsaved[] = $trainid; } } if (count($trainsaved) > 0) { // REMOVE ALL NON-SAVED TRAINING IDS FOR PERSON $query = "DELETE FROM link_person_training WHERE personid = " . $person_id . " AND id NOT IN (" . implode(",", $trainsaved) . ")"; error_log($query); $db->query($query); } else { // REMOVE ALL TRAINING IDS FOR PERSON $query = "DELETE FROM link_person_training WHERE personid = " . $person_id; error_log($query); $db->query($query); } } } if ($personrow->save()) { $status->setStatusMessage(t('The person was saved. Refreshing change history...')); $_SESSION['status'] = t('The person was saved.'); $person_id = $personrow->id; if ($this->setting('display_mod_skillsmart')) { if (strlen($this->getSanParam('Facilities')) > 0) { $db->query('UPDATE `facs` SET `Active`=\'N\' WHERE `person`=' . $person_id . ' AND `Active`=\'Y\';'); $Facs = split('\\$', $this->getSanParam('Facilities')); foreach ($Facs as $kys => $vls) { $Locs = split("~", $vls); $Fac = $Locs[0]; if ($strrpos = strrpos($Fac, '_')) { $Fac = substr($Fac, $strrpos + 1); } $db->query('INSERT INTO `facs` ( `person`, `facility`, `facstring`, `active`) VALUES (' . $person_id . ', ' . $Fac . ', \'' . $vls . '\', \'Y\');'); } } $co = 0; for ($co = 1; $co <= 20; $co++) { $db->query('UPDATE `trans` SET `Active`=\'N\' WHERE `person`=' . $person_id . ' AND `id`=' . $co . ' AND `Active`=\'Y\';'); if ($this->getSanParam('checktrain' . $co) == 'on') { $db->query('INSERT INTO `trans` ( `person`, `id`, `chk`, `yr`, `transstring`, `active`) VALUES (' . $person_id . ', ' . $co . ', \'' . $this->getSanParam('checktrain' . $co) . '\', \'' . $this->getSanParam('selecttrain' . $co) . '\', \'' . $this->getSanParam('texttrain' . $co) . '\', \'Y\');'); } } } //get trainer information $trainerTable = new Trainer(); $trainerRow = $trainerTable->fetchRow('person_id = ' . $person_id); if (!$trainerRow and ($this->getSanParam('active_trainer_option_id') or $this->getSanParam('trainer_type_option_id'))) { // add trainer $trainerRow = $trainerTable->createRow(); $trainerRow->person_id = $personrow->id; } if ($trainerRow) { //trainer info $trainerRow->is_active = 1; //deprecated //($this->getSanParam ( 'is_trainer' ) ? 1 : 0); $trainerRow->active_trainer_option_id = $this->getSanParam('active_trainer_option_id'); $trainerRow->type_option_id = $this->getSanParam('trainer_type_option_id'); $trainerRow->affiliation_option_id = $this->setting('display_trainer_affiliations') ? $this->getSanParam('trainer_affiliation_option_id') ? $this->getSanParam('trainer_affiliation_option_id') : 0 : 0; if (!$trainerRow->save()) { $status->setStatusMessage(t('The') . ' ' . t('trainer') . ' ' . t('information could not be saved.')); } else { MultiOptionList::updateOptions('trainer_to_trainer_skill_option', 'trainer_skill_option', 'trainer_id', $person_id, 'trainer_skill_option_id', $this->getSanParam('trainer_skill_id')); $language_ids = $this->getSanParam('trainer_language_id'); $planguage_id = $this->getSanParam('primary_language_id'); $primary_settings = array(); $found = false; if ($language_ids) { foreach ($language_ids as $lid) { if ($lid == $planguage_id) { $primary_settings[] = 1; $found = true; } else { $primary_settings[] = 0; } } } if (!$found) { $primary_settings[] = 1; $language_ids[] = $planguage_id; } MultiOptionList::updateOptions('trainer_to_trainer_language_option', 'trainer_language_option', 'trainer_id', $person_id, 'trainer_language_option_id', $language_ids, 'is_primary', $primary_settings); } } TrainingRecommend::saveRecommendedforPerson($person_id, $this->getSanParam('training_recommend')); if ($this->_getParam('redirectUrl')) { $status->redirect = $this->_getParam('redirectUrl'); } else { if ($this->_getParam('trainingredirect')) { // redirect back to training session $this->trainingRedirect($person_id); } else { //if it's an add then redirect to the view page if ($this->setting('display_mod_skillsmart')) { //$status->setRedirect ( '/person/view/id/' . $person_id ); $qs = OptionList::suggestionListHierarchical('person_qualification_option', 'qualification_phrase', false, false, array('0 AS is_default', 'child.is_default')); $parent_id = $this->getSanParam('primary_qualification_option_id'); foreach ($qs as $k => $v) { if ($v['id'] == $parent_id) { $parent_id = $v['parent_id']; break; } } $comps = $helper->getPersonCompetenciesDetailed($person_id); // Disabled redirect to competency page as per Robert's request, 11/19/2012, CDL // if (count($comps) > 0){ // $status->setRedirect ( '/person/editcompetency/id/' . $person_id ); // } else { $status->setRedirect('/person/edit/id/' . $person_id); // } /* if($parent_id=="6") { $status->setRedirect ( '/person/addcomc/id/' . $person_id ); } if($parent_id=="7") { $status->setRedirect ( '/person/addcomd/id/' . $person_id ); } if($parent_id=="8") { $status->setRedirect ( '/person/addcomn/id/' . $person_id ); } if($parent_id=="9") { $status->setRedirect ( '/person/addcomp/id/' . $person_id ); } */ } else { $status->setRedirect('/person/edit/id/' . $person_id); } } } } else { $status->setStatusMessage(t('ERROR: The person could not be saved. ' . __LINE__)); } } if ($validateOnly) { $this->sendData($status); } else { $this->view->assign('status', $status); } } if ($this->setting('display_mod_skillsmart')) { if ($person_id) { $rows = $db->fetchAll('SELECT `facstring` FROM `facs` INNER JOIN `facility` ON `facs`.`facility` = `facility`.`id` WHERE `facility`.`is_deleted`=0 AND `facs`.`person`=' . $person_id . ' AND `facs`.`Active`=\'Y\' ORDER BY `facs`.`sno` ASC;'); $Fcs = ""; foreach ($rows as $rw) { $Fcs = $Fcs . $rw['facstring'] . '$'; } $Fcs = trim($Fcs, '$'); $this->view->assign('Fcs', $Fcs); $rows = $db->fetchAll('SELECT `id`, `chk`, `yr`, `transstring` FROM `trans` WHERE `person`=' . $person_id . ' AND `Active`=\'Y\' ORDER BY sno ASC;'); $Trs = array(); $c*k = 0; for ($c*k = 1; $c*k <= 20; $c*k++) { $Trs[$c*k] = NULL; } foreach ($rows as $rw) { $Trs[$rw['id']] = $rw; } $this->view->assign('Trs', $Trs); } } //view it $facilityObj = new Facility(); $facilityrow = $facilityObj->findOrCreate($personrow->facility_id); $personArray['facility'] = $facilityrow->toArray(); //facility location $region_ids = Location::getCityInfo($facilityrow->location_id, $this->setting('num_location_tiers')); $region_ids = Location::regionsToHash($region_ids, 'person_facility'); $personArray = array_merge($personArray, $region_ids); //audit history $creatorObj = new User(); $updaterObj = new User(); $creatorrow = $creatorObj->findOrCreate($personrow->created_by); $personArray['creator'] = addslashes($creatorrow->first_name . ' ' . $creatorrow->last_name); $updaterrow = $updaterObj->findOrCreate($personrow->modified_by); $personArray['updater'] = addslashes($updaterrow->first_name . ' ' . $updaterrow->last_name); $personArray['birthdate-year'] = ''; $personArray['birthdate-month'] = ''; $personArray['birthdate-day'] = ''; //split birthdate fields if ($person_id and $personrow->birthdate) { $parts = explode(' ', $personrow->birthdate); $parts = explode('-', $parts[0]); $personArray['birthdate-year'] = $parts[0]; $personArray['birthdate-month'] = $parts[1]; $personArray['birthdate-day'] = $parts[2]; } //custom fields if ($person_id) { $personArray['custom1'] = ITechTable::getCustomValue('person_custom_1_option', 'custom1_phrase', $personArray['person_custom_1_option_id']); $personArray['custom2'] = ITechTable::getCustomValue('person_custom_2_option', 'custom2_phrase', $personArray['person_custom_2_option_id']); } //qualifications $qualificationsArray = OptionList::suggestionListHierarchical('person_qualification_option', 'qualification_phrase', false, false, array('0 AS is_default', 'child.is_default')); $personQualificationId = $personArray['primary_qualification_option_id']; // get parent qualification id, if user has sub qualification selected $personArray['primary_qualification_option_id_parent'] = $personQualificationId; foreach ($qualificationsArray as $k => $qualArray) { if ($qualArray['parent_phrase'] == 'unknown') { unset($qualificationsArray[$k]); //remove unknown as an option } if ($qualArray['id'] == $personQualificationId) { $personArray['primary_qualification_option_id_parent'] = $qualArray['parent_id']; } } $this->viewAssignEscaped('qualifications', $qualificationsArray); // occupational categories $occupationalsArray = OptionList::suggestionListHierarchical('occupational_categories', 'category_phrase', false, false, array('0 AS is_default', 'child.is_default')); $personQualificationId = $personArray['primary_qualification_option_id']; // get parent occupational category id, if user has sub qualification selected $personArray['occupational_category_id_parent'] = $personQualificationId; foreach ($occupationalsArray as $k => $catArray) { if ($catArray['category_phrase'] == 'unknown') { unset($qualificationsArray[$k]); //remove unknown as an option } if ($catArray['id'] == $personQualificationId) { $personArray['occupational_category_id'] = $catArray['parent_id']; } } $this->viewAssignEscaped('occupationalcats', $occupationalsArray); // get recommended trainings class topics $training_recommend = TrainingRecommend::getRecommendedTrainingTopics($personArray['primary_qualification_option_id_parent']); $this->viewAssignEscaped('training_recommend', $training_recommend); // get saved recommended trainings class titles $training_recommend_saved = TrainingRecommend::getRecommendedforPerson($person_id); $this->viewAssignEscaped('training_recommend_saved', $training_recommend_saved); //$classes = TrainingRecommend::getRecommendedClassesforPerson ( $person_id ); //responsibilities if ($this->setting('display_mod_skillsmart')) { $responsibilitiesArray = OptionList::suggestionList('person_responsibility_option', 'responsibility_phrase', false, false); $this->viewAssignEscaped('responsibilities', $responsibilitiesArray); } $primaryResponsibilitiesArray = OptionList::suggestionList('person_primary_responsibility_option', 'responsibility_phrase', false, false); $this->viewAssignEscaped('primaryResponsibilities', $primaryResponsibilitiesArray); $secondaryResponsibilitiesArray = OptionList::suggestionList('person_secondary_responsibility_option', 'responsibility_phrase', false, false); $this->viewAssignEscaped('secondaryResponsibilities', $secondaryResponsibilitiesArray); $educationlevelsArray = OptionList::suggestionList('person_education_level_option', 'education_level_phrase', false, false); $this->viewAssignEscaped('educationlevels', $educationlevelsArray); $attendreasonsArray = OptionList::suggestionList('person_attend_reason_option', 'attend_reason_phrase', false, false); $this->viewAssignEscaped('attendreasons', $attendreasonsArray); $activeTrainerArray = OptionList::suggestionList('person_active_trainer_option', 'active_trainer_phrase', false, false); $this->viewAssignEscaped('active_trainer', $activeTrainerArray); $titlesArray = OptionList::suggestionList('person_title_option', 'title_phrase', false, false); $this->viewAssignEscaped('titles', $titlesArray); $suffixesArray = OptionList::suggestionList('person_suffix_option', 'suffix_phrase', false, false); $this->viewAssignEscaped('suffixes', $suffixesArray); //training types //attach Trainer attributes $trainerTable = new Trainer(); $trainerrow = $trainerTable->findOrCreate($person_id); $personArray['trainer_is_active'] = $this->getSanParam('maketrainer') ? 1 : $trainerrow->is_active; $personArray['active_trainer_option_id'] = $trainerrow->active_trainer_option_id; $personArray['trainer_type_option_id'] = $trainerrow->type_option_id; $personArray['trainer_affiliation_option_id'] = $trainerrow->affiliation_option_id; $titlesArray = OptionList::suggestionList('person_title_option', 'title_phrase', false, false); $this->viewAssignEscaped('titles', $titlesArray); $trainerTypesArray = OptionList::suggestionList('trainer_type_option', 'trainer_type_phrase', false, false); $this->viewAssignEscaped('trainer_types', $trainerTypesArray); $trainerAffiliationArray = OptionList::suggestionList('trainer_affiliation_option', 'trainer_affiliation_phrase', false, false); $this->viewAssignEscaped('trainer_affiliations', $trainerAffiliationArray); $trainerSkillsArray = MultiOptionList::choicesList('trainer_to_trainer_skill_option', 'trainer_id', $person_id, 'trainer_skill_option', 'trainer_skill_phrase'); $this->viewAssignEscaped('trainer_skills', $trainerSkillsArray); $trainerLanguagesArray = MultiOptionList::choicesList('trainer_to_trainer_language_option', 'trainer_id', $person_id, 'trainer_language_option', 'language_phrase', 'is_primary'); $this->viewAssignEscaped('trainer_languages', $trainerLanguagesArray); foreach ($trainerLanguagesArray as $lang) { if ($lang['is_primary']) { $personArray['primary_language_id'] = $lang['id']; } } //has training history $hasTrainerHistory = false; if ($trainerrow->person_id) { $hasTrainerHistory = true; //we could also look up any history now, but we'll be lazy } $this->view->assign('hasTrainerHistory', $hasTrainerHistory); //facility types $typesArray = OptionList::suggestionList('facility_type_option', 'facility_type_phrase', false, false); $this->viewAssignEscaped('facility_types', $typesArray); //get home city name if ($personrow->home_location_id) { $region_ids = Location::getCityInfo($personrow->home_location_id, $this->setting('num_location_tiers')); $personArray['home_city'] = $region_ids[0]; $region_ids = Location::regionsToHash($region_ids, 'home'); $personArray = array_merge($personArray, $region_ids); } //sponsor types $sponsorsArray = OptionList::suggestionList('facility_sponsor_option', 'facility_sponsor_phrase', false, false); $this->viewAssignEscaped('facility_sponsors', $sponsorsArray); $this->viewAssignEscaped('person', $personArray); //facilities list //$rowArray = OptionList::suggestionList('facility',array('facility_name','id'),false,9999); $rowArray = Facility::selectAllFacilities($this->setting('num_location_tiers')); $this->viewAssignEscaped('facilities', $rowArray); if ($this->hasACL('edit_facility')) { $this->view->assign('insertFacilityLink', '<a href="#" id="show">' . str_replace(' ', ' ', t('Insert new facility')) . '</a>'); } //see if it is referenced anywhere $this->view->assign('okToDelete', !$person_id or !Person::isReferenced($person_id)); // create reference for GET paramaters if ($this->_getParam('trainingredirect')) { $this->view->assign('trainingredirect', $this->_getParam('trainingredirect')); } if ($this->_getParam('maketrainer')) { $this->view->assign('maketrainer', $this->_getParam('maketrainer')); } if ($this->_getParam('days')) { $this->view->assign('days', $this->_getParam('days')); } } catch (Exception $e) { print $e->getMessage(); } }
public function listByRecommendAction() { require_once 'models/table/TrainingRecommend.php'; $rowArray = TrainingRecommend::getRecommendedAdmin(); foreach ($rowArray as $key => $row) { $rowArray[$key]['edit'] = '<a href="' . Settings::$COUNTRY_BASE_URL . '/admin/training-recommend/edit/' . $row['person_qualification_option_id'] . '#edit">edit</a> ' . '<a href="' . Settings::$COUNTRY_BASE_URL . '/admin/training-recommend/delete/' . $row['person_qualification_option_id'] . '" onclick="return confirm(\'Are you sure you wish to remove these recommendations?\')">delete</a>'; } $this->sendData($rowArray); }
public function needsReport() { require_once 'models/table/TrainingRecommend.php'; require_once 'models/table/TrainingTitleOption.php'; $criteria = array(); //find the first date in the database $db = Zend_Db_Table_Abstract::getDefaultAdapter(); $sql = "SELECT MIN(training_start_date) as \"start\" FROM training WHERE is_deleted = 0"; $rowArray = $db->fetchAll($sql); $start_default = '0000-00-00'; if ($rowArray and $rowArray[0]['start']) { $start_default = $rowArray[0]['start']; } $parts = explode('-', $start_default); $criteria['start-year'] = $parts[0]; $criteria['start-month'] = $parts[1]; $criteria['start-day'] = $parts[2]; if ($this->getSanParam('start-year')) { $criteria['start-year'] = $this->getSanParam('start-year'); } if ($this->getSanParam('start-month')) { $criteria['start-month'] = $this->getSanParam('start-month'); } if ($this->getSanParam('start-day')) { $criteria['start-day'] = $this->getSanParam('start-day'); } $criteria['end-year'] = date('Y'); $criteria['end-month'] = date('m'); $criteria['end-day'] = date('d'); if ($this->getSanParam('end-year')) { $criteria['end-year'] = $this->getSanParam('end-year'); } if ($this->getSanParam('end-month')) { $criteria['end-month'] = $this->getSanParam('end-month'); } if ($this->getSanParam('end-day')) { $criteria['end-day'] = $this->getSanParam('end-day'); } list($criteria, $location_tier, $location_id) = $this->getLocationCriteriaValues($criteria); $criteria['training_gender'] = $this->getSanParam('training_gender'); $criteria['training_active'] = $this->getSanParam('training_active'); $criteria['concatNames'] = $this->getSanParam('concatNames'); $criteria['training_title_option_id'] = $this->getSanParam('training_title_option_id'); $criteria['training_title_id'] = $this->getSanParam('training_title_id'); $criteria['course_recommend_id'] = $this->getSanParam('course_recommend_id'); $criteria['training_pepfar_id'] = $this->getSanParam('training_pepfar_id'); $criteria['training_topic_id'] = $this->getSanParam('training_topic_id'); $criteria['training_topic_recommend_id'] = $this->getSanParam('training_topic_recommend_id'); $criteria['qualification_id'] = $this->getSanParam('qualification_id'); $criteria['qualification_secondary_id'] = $this->getSanParam('qualification_secondary_id'); $criteria['upcoming_id'] = $this->getSanParam('upcoming_id'); $criteria['facilityInput'] = $this->getSanParam('facilityInput'); $criteria['first_name'] = $this->getSanParam('first_name'); $criteria['last_name'] = $this->getSanParam('last_name'); $criteria['doCount'] = $this->view->mode == 'count'; $criteria['showProvince'] = ($this->getSanParam('showProvince') or $criteria['doCount'] and ($criteria['province_id'] or $criteria['province_id'] === '0')); $criteria['showDistrict'] = ($this->getSanParam('showDistrict') or $criteria['doCount'] and ($criteria['district_id'] or $criteria['district_id'] === '0')); $criteria['showRegionC'] = ($this->getSanParam('showRegionC') or $criteria['doCount'] and ($criteria['region_c_id'] or $criteria['region_c_id'] === '0')); $criteria['showTrainingTitle'] = ($this->getSanParam('showTrainingTitle') or $criteria['doCount'] and ($criteria['training_title_option_id'] or $criteria['training_title_option_id'] === '0' or $criteria['training_title_id'])); $criteria['showPepfar'] = ($this->getSanParam('showPepfar') or $criteria['doCount'] and ($criteria['training_title_option_id'] or $criteria['training_pepfar_id'] === '0' or $criteria['training_title_id'])); $criteria['showQualPrim'] = ($this->getSanParam('showQualPrim') or $criteria['doCount'] and ($criteria['qualification_id'] or $criteria['qualification_id'] === '0')); $criteria['showQualSecond'] = ($this->getSanParam('showQualSecond') or $criteria['doCount'] and ($criteria['qualification_secondary_id'] or $criteria['qualification_secondary_id'] === '0')); $criteria['showTopic'] = ($this->getSanParam('showTopic') or $criteria['doCount'] and ($criteria['training_topic_id'] or $criteria['training_topic_id'] === '0')); $criteria['showTopicRecommend'] = ($this->getSanParam('showTopicRecommend') or $criteria['doCount'] and ($criteria['training_topic_recommend_id'] or $criteria['training_topic_recommend_id'] === '0')); $criteria['showCourseRecommend'] = ($this->getSanParam('showCourseRecommend') or $criteria['doCount'] and ($criteria['course_recommend_id'] or $criteria['course_recommend_id'] === '0')); $criteria['showFacility'] = ($this->getSanParam('showFacility') or $criteria['doCount'] and $criteria['facility_name']); $criteria['showGender'] = ($this->getSanParam('showGender') or $criteria['doCount'] and $criteria['training_gender']); $criteria['showActive'] = ($this->getSanParam('showActive') or $criteria['doCount'] and $criteria['training_active']); $criteria['showEmail'] = $this->getSanParam('showEmail'); $criteria['showPhone'] = $this->getSanParam('showPhone'); $criteria['showClasses'] = $this->getSanParam('showPhone'); $criteria['showUpcoming'] = $this->getSanParam('showUpcoming'); $criteria['showFirstName'] = $this->getSanParam('showFirstName'); $criteria['showLastName'] = $this->getSanParam('showLastName'); $criteria['go'] = $this->getSanParam('go'); if ($criteria['go']) { $num_locs = $this->setting('num_location_tiers'); list($field_name, $location_sub_query) = Location::subquery($num_locs, $location_tier, $location_id, true); $sql = 'SELECT '; if ($criteria['doCount']) { $sql .= ' COUNT(pt.person_id) as "cnt" '; } else { if ($criteria['concatNames']) { $sql .= ' DISTINCT pt.person_id as "id", CONCAT(first_name, ' . "' '" . ',last_name, ' . "' '" . ', IFNULL(suffix_phrase, ' . "' '" . ')) as "name", IFNULL(suffix_phrase, ' . "' '" . ') as suffix_phrase, pt.training_start_date '; } else { $sql .= ' DISTINCT pt.person_id as "id", IFNULL(suffix_phrase, ' . "' '" . ') as suffix_phrase, last_name, first_name, middle_name, pt.training_start_date '; } } if ($criteria['showPhone']) { $sql .= ", CASE WHEN (pt.phone_work IS NULL OR pt.phone_work = '') THEN NULL ELSE pt.phone_work END as \"phone_work\", CASE WHEN (pt.phone_home IS NULL OR pt.phone_home = '') THEN NULL ELSE pt.phone_home END as \"phone_home\", CASE WHEN (pt.phone_mobile IS NULL OR pt.phone_mobile = '') THEN NULL ELSE pt.phone_mobile END as \"phone_mobile\" "; } if ($criteria['showEmail']) { $sql .= ', pt.email '; } if ($criteria['showGender']) { $sql .= ', pt.gender '; } if ($criteria['showActive']) { $sql .= ', pt.active '; } if ($criteria['showTrainingTitle']) { $sql .= ', pt.training_title, pt.training_title_option_id '; // already GROUP_CONCAT'ed in main SQL } if ($criteria['showDistrict']) { $sql .= ', pt.district_name '; } if ($criteria['showProvince']) { $sql .= ', pt.province_name '; } if ($criteria['showRegionC']) { $sql .= ', pt.region_c_name '; } if ($criteria['showPepfar']) { $sql .= ', tpep.pepfar_category_phrase '; } if ($criteria['showTopic']) { $sql .= ', ttopic.training_topic_phrase '; } if ($criteria['showFacility']) { $sql .= ', pt.facility_name '; } if ($criteria['showQualPrim']) { $sql .= ', pq.qualification_phrase '; } if ($criteria['showQualSecond']) { $sql .= ', pqs.qualification_phrase AS qualification_secondary_phrase'; } // NOT USED! (recommended topics are, though) if (!$criteria['doCount'] and $criteria['showUpcoming']) { //$sql .= ', precommend.training_title_phrase AS recommended'; $sql .= ", GROUP_CONCAT(DISTINCT CONCAT(recommend.training_title_phrase ) ORDER BY training_title_phrase SEPARATOR ', ') AS upcoming "; } if ($criteria['showTopicRecommend']) { //$sql .= ', ptopic.training_topic_phrase AS recommended '; $sql .= ", GROUP_CONCAT(DISTINCT CONCAT(ptopic.training_topic_phrase) ORDER BY training_topic_phrase SEPARATOR ', ') AS recommended "; // same training location? -- AND t.training_location_id = pt.training_location_id } // select everyone, not just participants $sql .= ' FROM ( SELECT training.*, person.facility_id as "facility_id", person.id as "person_id", person.last_name, IFNULL(suffix_phrase, ' . "' '" . ') as suffix_phrase, person.first_name, person.middle_name, person.phone_work, person.phone_home, person.phone_mobile, person.email, CASE WHEN person.active = \'deceased\' THEN \'inactive\' ELSE person.active END as "active", CASE WHEN person.gender IS NULL THEN \'na\' WHEN person.gender = \'\' THEN \'na\' ELSE person.gender END as "gender", primary_qualification_option_id,facility.facility_name, l.' . implode(', l.', $field_name) . ', GROUP_CONCAT(DISTINCT CONCAT(training_title_phrase) ORDER BY training_title_phrase SEPARATOR \', \') AS training_title FROM person LEFT JOIN person_to_training ON person.id = person_to_training.person_id LEFT JOIN training ON training.id = person_to_training.training_id LEFT JOIN facility ON person.facility_id = facility.id LEFT JOIN (' . $location_sub_query . ') as l ON facility.location_id = l.id LEFT JOIN training_title_option tto ON `training`.training_title_option_id = tto.id LEFT JOIN person_suffix_option suffix ON person.suffix_option_id = suffix.id GROUP BY person.id ) as pt '; if ($criteria['showPepfar']) { $sql .= ' 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 (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 '; } // Recommended classes if (!$criteria['doCount'] and ($criteria['showUpcoming'] or $criteria['upcoming_id'])) { // not tested $sql .= ($criteria['upcoming_id'] ? "INNER" : "LEFT") . " JOIN (SELECT training_title_phrase, person_id\r\n\t\t\t\tFROM person_to_training_topic_option as ptto\r\n\t\t\t\tJOIN training_to_training_topic_option ttt ON (ttt.training_topic_option_id = ptto.training_topic_option_id)\r\n\t\t\t\tJOIN training t ON (t.id = ttt.training_id)\r\n\t\t\t\tJOIN training_title_option tt ON (tt.id = t.training_title_option_id)\r\n\t\t\t\tWHERE\r\n\t\t\t\tt.is_deleted = 0 AND tt.training_title_phrase != 'unknown' AND t.training_start_date > NOW()\r\n\t\t\t\t" . ($criteria['upcoming_id'] ? ' AND t.training_title_option_id = ' . $criteria['upcoming_id'] : '') . "\r\n\t\t\t\t) AS recommend ON recommend.person_id = pt.person_id "; //$sql .= ' JOIN person_to_training_topic_option ptto ON ptto.person_id = pt.person_id'; } if ($criteria['showTopicRecommend'] or $criteria['training_topic_recommend_id'] or $criteria['training_topic_id'] or $criteria['training_topic_id'] === '0') { $sql .= ' INNER JOIN ( SELECT person_id, topicid.id, training_topic_phrase FROM person_to_training_topic_option ptto INNER JOIN training_topic_option topicid ON (topicid.id = ptto.training_topic_option_id) ) AS ptopic ON ptopic.person_id = pt.person_id '; } if ($criteria['showQualPrim'] || $criteria['showQualSecond'] || $criteria['qualification_id'] || $criteria['qualification_secondary_id']) { // primary qualifications $sql .= ' LEFT JOIN person_qualification_option as pq ON ( (pt.primary_qualification_option_id = pq.id AND pq.parent_id IS NULL) OR pq.id = (SELECT parent_id FROM person_qualification_option WHERE id = pt.primary_qualification_option_id LIMIT 1) )'; // secondary qualifications $sql .= ' LEFT JOIN person_qualification_option as pqs ON ( pt.primary_qualification_option_id = pqs.id AND pqs.parent_id IS NOT NULL )'; } $where = ''; // legacy if ($criteria['training_title_option_id'] or $criteria['training_title_option_id'] === '0') { if (strlen($where)) { $where .= ' AND '; } $where .= ' pt.training_title_option_id = ' . $criteria['training_title_option_id']; } if ($criteria['training_title_id'] or $criteria['training_title_id'] === '0') { if (strlen($where)) { $where .= ' AND '; } $where .= ' pt.training_title_option_id = ' . $criteria['training_title_id']; } if ($criteria['training_topic_id'] or $criteria['training_topic_id'] === '0') { if (strlen($where)) { $where .= ' AND '; } $where .= ' ttopic.training_topic_option_id = \'' . $criteria['training_topic_id'] . '\''; } if ($criteria['training_pepfar_id'] or $criteria['training_pepfar_id'] === '0') { if (strlen($where)) { $where .= ' AND '; } $where .= ' tpep.training_pepfar_categories_option_id = \'' . $criteria['training_pepfar_id'] . '\''; } if ($criteria['facilityInput']) { if (strlen($where)) { $where .= ' AND '; } $where .= ' pt.facility_id = \'' . $criteria['facilityInput'] . '\''; } if ($criteria['training_gender']) { if (strlen($where)) { $where .= ' AND '; } $where .= ' pt.gender = \'' . $criteria['training_gender'] . '\''; } if ($criteria['training_active']) { if (strlen($where)) { $where .= ' AND '; } $where .= ' pt.active = \'' . $criteria['training_active'] . '\''; } if ($criteria['qualification_id']) { if (strlen($where)) { $where .= ' AND '; } $where .= ' (pq.id = ' . $criteria['qualification_id'] . ' OR pqs.parent_id = ' . $criteria['qualification_id'] . ') '; } if ($criteria['qualification_secondary_id']) { if (strlen($where)) { $where .= ' AND '; } $where .= ' pqs.id = ' . $criteria['qualification_secondary_id']; } if ($criteria['training_topic_recommend_id']) { if (strlen($where)) { $where .= ' AND '; } $where .= ' ptopic.id = ' . $criteria['training_topic_recommend_id']; } if ($criteria['first_name']) { if (strlen($where)) { $where .= ' AND '; } $where .= " first_name = '" . mysql_escape_string($criteria['first_name']) . "'"; } if ($criteria['last_name']) { if (strlen($where)) { $where .= ' AND '; } $where .= " last_name = '" . mysql_escape_string($criteria['last_name']) . "'"; } if (intval($criteria['end-year']) and $criteria['start-year']) { if (strlen($where)) { $where .= ' AND '; } $startDate = $criteria['start-year'] . '-' . $criteria['start-month'] . '-' . $criteria['start-day']; $endDate = $criteria['end-year'] . '-' . $criteria['end-month'] . '-' . $criteria['end-day']; $where .= ' training_start_date >= \'' . $startDate . '\' AND training_start_date <= \'' . $endDate . '\' '; } // restricted access?? only show trainings we have the ACL to view require_once 'views/helpers/TrainingViewHelper.php'; $org_allowed_ids = allowed_organizer_access($this); if ($org_allowed_ids) { // doesnt have acl 'training_organizer_option_all' $org_allowed_ids = implode(',', $org_allowed_ids); $where .= " AND training_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 $where .= $site_orgs ? " AND training_organizer_option_id in ({$site_orgs}) " : ""; if ($where) { $sql .= ' WHERE ' . $where; } if ($criteria['doCount']) { $groupBy = ''; if ($criteria['showTrainingTitle']) { if (strlen($groupBy)) { $groupBy .= ' , '; } $groupBy .= ' pt.training_title_option_id'; } if ($criteria['showGender']) { if (strlen($groupBy)) { $groupBy .= ' , '; } $groupBy .= ' pt.gender'; } if ($criteria['showActive']) { if (strlen($groupBy)) { $groupBy .= ' , '; } $groupBy .= ' pt.active'; } if ($criteria['showProvince']) { if (strlen($groupBy)) { $groupBy .= ' , '; } $groupBy .= ' pt.province_id'; } if ($criteria['showDistrict']) { if (strlen($groupBy)) { $groupBy .= ' , '; } $groupBy .= ' pt.district_id'; } if (isset($criteria['showLocation']) and $criteria['showLocation']) { if (strlen($groupBy)) { $groupBy .= ' , '; } $groupBy .= ' pt.training_location_id'; } if ($criteria['showTopic']) { if (strlen($groupBy)) { $groupBy .= ' , '; } $groupBy .= ' ttopic.training_topic_option_id'; } if ($criteria['showTopicRecommend']) { if (strlen($groupBy)) { $groupBy .= ' , '; } $groupBy .= ' ptopic.id'; } if ($criteria['showQualPrim'] and !$criteria['showQualSecond']) { if (strlen($groupBy)) { $groupBy .= ' , '; } $groupBy .= ' pq.id'; //added ToddW 090827 } else { if ($criteria['showQualPrim'] || $criteria['showQualSecond']) { if (strlen($groupBy)) { $groupBy .= ' , '; } $groupBy .= ' pt.primary_qualification_option_id'; } } /* if ( $criteria['showQualPrim']) { if ( strlen($groupBy) ) $groupBy .= ' , '; //$groupBy .= ' pq.id '; } if ( $criteria['showQualSecond']) { if ( strlen($groupBy) ) $groupBy .= ' , '; //$groupBy .= ' pqs.id '; } */ if ($criteria['showPepfar']) { if (strlen($groupBy)) { $groupBy .= ' , '; } $groupBy .= ' tpep.training_pepfar_categories_option_id'; } if ($criteria['showFacility']) { if (strlen($groupBy)) { $groupBy .= ' , '; } $groupBy .= ' pt.facility_id'; } if ($groupBy != '') { $groupBy = ' GROUP BY ' . $groupBy; } $sql .= $groupBy; } else { //if ( $criteria['showTopicRecommend'] || $criteria['showCourseRecommend']) { $sql .= ' GROUP BY pt.person_id, pt.id'; //added ToddW 090827 -- always group by person //} } //echo $sql; exit; $rowArray = $db->fetchAll($sql); if ($criteria['doCount']) { $count = 0; foreach ($rowArray as $row) { $count += $row['cnt']; } } else { $count = count($rowArray); //cheezy //get the count of people, now group by topic and run the query again //so we get a line for each topic if ($criteria['showTopicRecommend']) { $sql .= ',ptopic.training_topic_phrase'; $rowArray = $db->fetchAll($sql); } } if ($this->_getParam('outputType')) { $this->sendData($this->reportHeaders(false, $rowArray)); } } else { $count = 0; $rowArray = array(); } $criteria['go'] = $this->getSanParam('go'); $this->viewAssignEscaped('results', $rowArray); if ($rowArray) { $first = reset($rowArray); if (isset($first['phone_work'])) { foreach ($rowArray as $key => $val) { $phones = array(); if ($val['phone_work']) { $phones[] = str_replace(' ', ' ', trim($val['phone_work'])) . ' (w)'; } if ($val['phone_home']) { $phones[] = str_replace(' ', ' ', trim($val['phone_home'])) . ' (h)'; } if ($val['phone_mobile']) { $phones[] = str_replace(' ', ' ', trim($val['phone_mobile'])) . ' (m)'; } $rowArray[$key]['phone'] = implode(', ', $phones); } $this->view->assign('results', $rowArray); } } $this->view->assign('count', $count); $this->view->assign('criteria', $criteria); //province /* $provinceArray = OptionList::suggestionList ( 'location_province', 'province_name', false, false, false ); $this->viewAssignEscaped ( 'provinces', $provinceArray ); //district $districtArray = OptionList::suggestionList ( 'location_district', array ('district_name', 'parent_province_id' ), false, false, false ); $this->viewAssignEscaped ( 'districts', $districtArray ); */ $locations = Location::getAll(); $this->viewAssignEscaped('locations', $locations); //course $courseArray = TrainingTitleOption::suggestionList(false, 10000); $this->viewAssignEscaped('courses', $courseArray); //topics $topicsArray = OptionList::suggestionList('training_topic_option', 'training_topic_phrase', false, false, false); $this->viewAssignEscaped('topics', $topicsArray); //qualifications (primary) $qualsArray = OptionList::suggestionList('person_qualification_option', 'qualification_phrase', false, false, false, 'parent_id IS NULL'); $this->viewAssignEscaped('qualifications_primary', $qualsArray); //qualifications (secondary) $qualsArray = OptionList::suggestionList('person_qualification_option', 'qualification_phrase', false, false, false, 'parent_id IS NOT NULL'); $this->viewAssignEscaped('qualifications_secondary', $qualsArray); //pepfar $organizersArray = OptionList::suggestionList('training_pepfar_categories_option', 'pepfar_category_phrase', false, false, false); $this->viewAssignEscaped('pepfars', $organizersArray); //upcoming classes if (!$criteria['doCount']) { $upcomingArray = TrainingRecommend::getUpcomingTrainingTitles(); $this->viewAssignEscaped('upcoming', $upcomingArray); } //recommended require_once 'models/table/TrainingRecommend.php'; $topicsRecommend = TrainingRecommend::getTopics(); $this->viewAssignEscaped('topicsRecommend', @$topicsRecommend->ToArray()); //facilities list $rowArray = OptionList::suggestionList('facility', array('facility_name', 'id'), false, 9999); $facilitiesArray = array(); foreach ($rowArray as $key => $val) { if ($val['id'] != 0) { $facilitiesArray[] = $val; } } $this->viewAssignEscaped('facilities', $facilitiesArray); }