/** * @param \alphayax\utils\cli\model\OptionList $optionList */ protected function displayOptions(OptionList $optionList) { echo 'Options' . PHP_EOL; $options = $optionList->getAll(); $longPad = $optionList->getLongPad(); $shortPad = $optionList->getShortPad(); $emptyShortPad = str_repeat(' ', $shortPad); $emptyLongPad = str_repeat(' ', $longPad); foreach ($options as $option) { $valueFlag = $option->hasValue() ? ' <value>' : ''; /// Short opt $shortOpt = $emptyShortPad; if ($option->hasShortOpt()) { $shortOpt = '-' . $option->getShortOpt() . $valueFlag; $shortOpt = str_pad($shortOpt, $shortPad, ' '); } /// Long opt $longOpt = $emptyLongPad; if ($option->hasLongOpt()) { $longOpt = '--' . $option->getLongOpt() . $valueFlag; $longOpt = str_pad($longOpt, $longPad, ' '); } $description = $option->getDescription(); if ($option->isRequired()) { $description = '[REQUIRED] ' . $description; } echo "\t{$shortOpt}\t{$longOpt}\t{$description}" . PHP_EOL; } echo PHP_EOL; }
/** * Returns ALL people, even if they are not a trainer. Adds flag if they are not. */ protected static function suggestionQuery($match = false, $limit = 100, $field = 'last_name', $fieldsSelect = array('p.last_name', 'p.first_name', 'p.id'), $fieldAdditional = false) { require_once 'models/table/OptionList.php'; $topicTable = new OptionList(array('name' => 'trainer')); $select = $topicTable->select()->from(array('p' => 'person'), $fieldsSelect)->setIntegrityCheck(false); $select->joinLeft(array('t' => 'trainer'), "p.id = t.person_id", array('is_trainer' => '!(ISNULL(t.type_option_id))')); //look for char start if ($match) { $select->where("{$field} LIKE ? ", $match . '%'); if ($fieldAdditional) { $select->orWhere("{$fieldAdditional} LIKE ? ", $match . '%'); } } $select->where('p.is_deleted = 0'); $select->order("{$field} ASC"); // foreach($fieldsSelect as $otherfield) { $select->order("last_name ASC"); $select->order("first_name ASC"); $select->order("middle_name ASC"); // } if ($limit) { $select->limit($limit, 0); } $rows = $topicTable->fetchAll($select); return $rows; }
protected static function suggestionQuery($match = false, $limit = 100) { require_once 'models/table/OptionList.php'; $courseTable = new OptionList(array('name' => 'training_title_option')); $select = $courseTable->select()->from('training_title_option', array('id', 'training_title_phrase')); /* //join with trainings, so we don't return any course names that aren't used $select->setIntegrityCheck(false); $select->join(array('tt' => 'training_title_option'), "course.training_title_option_id = tt.id",array('course_name' => 'training_title_phrase')); $select->join(array('t' => 'training'), "course.id = t.course_id", array('course_id')); $select->distinct(); */ //look for char start if ($match) { $select->where('training_title_phrase LIKE ? ', $match . '%'); } $select->where('is_deleted = 0'); // $select->group('course.training_title_option_id'); $select->order('training_title_phrase ASC'); if ($limit) { $select->limit($limit, 0); } $rows = $courseTable->fetchAll($select); return $rows; }
public static function generateHtml($table, $column, &$view, $checked = array(), $where = '') { $html = '<div class="checkboxHelper" ><table cellspacing="0">'; $rows = OptionList::suggestionList($table, $column, false, false, false, $where); if (!$rows) { return '<input type="text" readonly="readonly" disabled="disabled" /> '; } foreach ($rows as $r) { $isChecked = isset($_GET["{$table}_id"]) && in_array($r['id'], $_GET["{$table}_id"]) || !empty($checked) && in_array($r['id'], $checked) ? ' checked' : ''; $html .= ' <tr class="' . $isChecked . '"> <td valign="top"><input type="checkbox" value="' . $r['id'] . '" name="' . $table . '_id[]" id="' . $table . $r['id'] . '"' . $isChecked . '></td> <td valign="top"><label for="' . $table . $r['id'] . '"> ' . $r[$column] . '</label></td> </tr>' . "\n"; } $html .= '</table></div>'; return $html; }
public function editAction() { if (!$this->hasACL('edit_employee')) { $this->doNoAccessError(); } $db = $this->dbfunc(); $status = ValidationContainer::instance(); $params = $this->getAllParams(); $id = $params['id']; #// restricted access?? only show partners by organizers that we have the ACL to view #$org_allowed_ids = allowed_org_access_full_list($this); #if ($org_allowed_ids && $this->view->mode != 'add') { // doesnt have acl 'training_organizer_option_all' # $validID = $db->fetchCol("SELECT partner.id FROM partner WHERE partner.id = $id AND partner.organizer_option_id in ($org_allowed_ids)"); // check for both # if(empty($validID)) # $this->doNoAccessError (); #} if ($this->getRequest()->isPost()) { //validate then save $params['location_id'] = regionFiltersGetLastID('', $params); $params['dob'] = $this->_date_to_sql($params['dob']); $params['agreement_end_date'] = $this->_date_to_sql($params['agreement_end_date']); $params['transition_date'] = $this->_date_to_sql($params['transition_date']); $params['transition_complete_date'] = $this->_date_to_sql($params['transition_complete_date']); $params['site_id'] = $params['facilityInput']; $params['option_nationality_id'] = $params['lookup_nationalities_id']; $params['facility_type_option_id'] = $params['employee_site_type_option_id']; $status->checkRequired($this, 'first_name', t('Frist Name')); $status->checkRequired($this, 'last_name', t('Last Name')); $status->checkRequired($this, 'last_name', t('Name')); $status->checkRequired($this, 'dob', t('Name')); if ($this->setting('display_employee_nationality')) { $status->checkRequired($this, 'lookup_nationalities_id', t('Employee Nationality')); } $status->checkRequired($this, 'employee_qualification_option_id', t('Staff Cadre')); if ($this->setting('display_employee_salary')) { $status->checkRequired($this, 'salary', t('Salary')); } if ($this->setting('display_employee_benefits')) { $status->checkRequired($this, 'benefits', t('Benefits')); } if ($this->setting('display_employee_additional_expenses')) { $status->checkRequired($this, 'additional_expenses', t('Additional Expenses')); } if ($this->setting('display_employee_stipend')) { $status->checkRequired($this, 'stipend', t('Stipend')); } if ($this->setting('display_employee_partner')) { $status->checkRequired($this, 'partner_id', t('Partner')); } if ($this->setting('display_employee_sub_partner')) { $status->checkRequired($this, 'subpartner_id', t('Sub Partner')); } if ($this->setting('display_employee_intended_transition')) { $status->checkRequired($this, 'employee_transition_option_id', t('Intended Transition')); } if ($this->setting('display_employee_base') && !$params['employee_base_option_id'] || !$this->setting('display_employee_base')) { // either one is OK, javascript disables regions if base is on & has a value choice $status->checkRequired($this, 'province_id', t('Region A (Province)')); } if ($this->setting('display_employee_base')) { $status->checkRequired($this, 'employee_base_option_id', t('Employee Based at')); } if (!$status->hasError()) { $id = $this->_findOrCreateSaveGeneric('employee', $params); if (!$id) { $status->setStatusMessage(t('That person could not be saved.')); } else { # converted to optionlist, link table not needed TODO. marking for removal. #MultiOptionList::updateOptions ( 'employee_to_role', 'employee_role_option', 'employee_id', $id, 'employee_role_option_id', $params['employee_role_option_id'] ); $status->setStatusMessage(t('The person was saved.')); $this->_redirect("employee/edit/id/{$id}"); } } else { $status->setStatusMessage(t('That person could not be saved.')); } } if ($id && !$status->hasError()) { // read data from db $sql = 'SELECT * FROM employee WHERE employee.id = ' . $id; $row = $db->fetchRow($sql); if ($row) { $params = $row; } else { $status->setStatusMessage(t('Error finding that record in the database.')); } $region_ids = Location::getCityInfo($params['location_id'], $this->setting('num_location_tiers')); $region_ids = Location::regionsToHash($region_ids); $params = array_merge($params, $region_ids); #$params['roles'] = $db->fetchCol("SELECT employee_role_option_id FROM employee_to_role WHERE employee_id = $id"); } // assign form drop downs $params['dob'] = formhelperdate($params['dob']); $params['agreement_end_date'] = formhelperdate($params['agreement_end_date']); $params['transition_date'] = formhelperdate($params['transition_date']); $params['transition_complete_date'] = formhelperdate($params['transition_complete_date']); $params['courses'] = $this->getCourses($id); $params['lookup_nationalities_id'] = $params['option_nationality_id']; $params['employee_site_type_option_id'] = $params['facility_type_option_id']; $this->viewAssignEscaped('employee', $params); $validCHWids = $db->fetchCol("select id from employee_qualification_option qual\r\n\t\t\t\t\t\t\t\t\t\tinner join (select id as success from employee_qualification_option where qualification_phrase in ('Community Based Worker','Community Health Worker','NC02 -Community health workers')) parentIDs\r\n\t\t\t\t\t\t\t\t\t\ton (parentIDs.success = qual.id)"); $this->view->assign('validCHWids', $validCHWids); $this->view->assign('expandCHWFields', !(array_search($params['employee_qualification_option_id'], $validCHWids) === false)); // i.e $validCHWids.contains($employee[qualification]) $this->view->assign('status', $status); $this->view->assign('pageTitle', $this->view->mode == 'add' ? t('Add Employee') : t('Edit Employee')); $this->viewAssignEscaped('locations', Location::getAll()); $titlesArray = OptionList::suggestionList('person_title_option', 'title_phrase', false, 9999); $this->view->assign('titles', DropDown::render('title_option_id', $this->translation['Title'], $titlesArray, 'title_phrase', 'id', $params['title_option_id'])); $this->view->assign('partners', DropDown::generateHtml('partner', 'partner', $params['partner_id'], false, $this->view->viewonly, false)); $this->view->assign('subpartners', DropDown::generateHtml('partner', 'partner', $params['subpartner_id'], false, $this->view->viewonly, false, false, array('name' => 'subpartner_id'), true)); $this->view->assign('bases', DropDown::generateHtml('employee_base_option', 'base_phrase', $params['employee_base_option_id'])); $this->view->assign('site_types', DropDown::generateHtml('employee_site_type_option', 'site_type_phrase', $params['facility_type_option_id'])); $this->view->assign('cadres', DropDown::generateHtml('employee_qualification_option', 'qualification_phrase', $params['employee_qualification_option_id'])); $this->view->assign('categories', DropDown::generateHtml('employee_category_option', 'category_phrase', $params['employee_category_option_id'], false, $this->view->viewonly, false)); $this->view->assign('fulltime', DropDown::generateHtml('employee_fulltime_option', 'fulltime_phrase', $params['employee_fulltime_option_id'], false, $this->view->viewonly, false)); $this->view->assign('roles', DropDown::generateHtml('employee_role_option', 'role_phrase', $params['employee_role_option_id'], false, $this->view->viewonly, false)); #$this->view->assign ( 'roles', CheckBoxes::generateHtml ( 'employee_role_option', 'role_phrase', $this->view, $params['roles'] ) ); $this->view->assign('transitions', DropDown::generateHtml('employee_transition_option', 'transition_phrase', $params['employee_transition_option_id'], false, $this->view->viewonly, false)); $this->view->assign('transitions_complete', DropDown::generateHtml('employee_transition_option', 'transition_phrase', $params['employee_transition_complete_option_id'], false, $this->view->viewonly, false, false, array('name' => 'employee_transition_complete_option_id'), true)); $helper = new Helper(); $this->viewAssignEscaped('facilities', $helper->getFacilities()); $this->view->assign('relationships', DropDown::generateHtml('employee_relationship_option', 'relationship_phrase', $params['employee_relationship_option_id'], false, $this->view->viewonly, false)); $this->view->assign('referrals', DropDown::generateHtml('employee_referral_option', 'referral_phrase', $params['employee_referral_option_id'], false, $this->view->viewonly, false)); $this->view->assign('provided', DropDown::generateHtml('employee_training_provided_option', 'training_provided_phrase', $params['employee_training_provided_option_id'], false, $this->view->viewonly, false)); $employees = OptionList::suggestionList('employee', array('first_name', 'CONCAT(first_name, CONCAT(" ", last_name)) as name'), false, 99999); $this->view->assign('supervisors', DropDown::render('supervisor_id', $this->translation['Supervisor'], $employees, 'name', 'id', $params['supervisor_id'])); $this->view->assign('nationality', DropDown::generateHtml('lookup_nationalities', 'nationality', $params['lookup_nationalities_id'], false, $this->view->viewonly, false)); $this->view->assign('race', DropDown::generateHtml('person_race_option', 'race_phrase', $params['race_option_id'], false, $this->view->viewonly, false)); }
public function settingsAction($path) { require_once 'models/table/System.php'; $sysTable = new System(); @date_default_timezone_set("GMT"); $settingsWriter = new XMLWriter(); // Output directly to the user $settingsWriter->openURI($path . 'Settings.xml'); $settingsWriter->startDocument('1.0'); $settingsWriter->setIndent(4); $settingsWriter->startElement('system'); $settingsWriter->writeAttribute('version', '1.0'); $settingsWriter->writeAttribute('password', 'mango'); $sysTable->select('*'); $row = $sysTable->fetchRow(); foreach ($row->toArray() as $k => $v) { $settingsWriter->writeAttribute($k, $v); } $option_tables = array('translation'); foreach ($option_tables as $opt) { $settingsWriter->startElement(str_replace('_option', '', $opt)); $optTable = new OptionList(array('name' => $opt)); $optTable->select('*'); foreach ($optTable->fetchAll() as $row) { $settingsWriter->startElement('add'); foreach ($row->toArray() as $k => $v) { if ($k == 'id') { $settingsWriter->writeAttribute('key', $v); } else { if (strpos($k, '_phrase')) { $settingsWriter->writeAttribute('value', $v); } else { $settingsWriter->writeAttribute($k, $v); } } } $settingsWriter->endElement(); } $settingsWriter->endElement(); } // End Item $settingsWriter->endElement(); }
public function trainingsAction() { try { require_once 'models/table/Training.php'; $table = new Training(); $select = $table->select()->from('training', array('*'))->setIntegrityCheck(false)->joinLeft(array('t' => 'training_title_option'), "training_title_option_id = t.id", array('training_title' => 'training_title_phrase'))->joinLeft(array('tl' => 'training_location'), "training.training_location_id = tl.id", array('training_location_name', 'location_id' => 'tl.location_id'))->joinLeft(array('tg' => 'training_got_curriculum_option'), "training.training_got_curriculum_option_id = tg.id", 'training_got_curriculum_phrase')->joinLeft(array('tlvl' => 'training_level_option'), "training.training_level_option_id = tlvl.id", 'training_level_phrase'); // todo inner joins were Causing trainings w/out level / title to not get dl'ed, might not print right w/out title, should replace nulls with 'unknown' or something $ids = $this->getSanParam('ids'); if ($ids) { $select = $select->where("training.id in ({$ids})"); $ids = explode(',', $ids); $this->view->assign('ids', $ids); } // fetch $rowRay = $table->fetchAll($select)->toArray(); //sort by id $sorted = array(); foreach ($rowRay as $row) { $sorted[$row['id']] = $row; } $sorted = $table->_fill_related($sorted, 'training_got_curriculum_option', 'training_got_curriculum_option_id', 'training_got_curriculum_phrase'); $locations = Location::getAll(); foreach ($sorted as $id => $row) { $city_info = Location::getCityInfo($row['location_id'], $this->setting('num_location_tiers'), $locations); if (count($city_info)) { $sorted[$id]['city_name'] = $locations[$city_info['city_name']]['location_name']; $sorted[$id]['province_name'] = $locations[$city_info[1]]['name']; if ($this->setting('display_region_b')) { $sorted[$id]['district_name'] = $locations[$city_info[2]]['name']; } if ($this->setting('display_region_c')) { $sorted[$id]['region_c_name'] = $locations[$city_info[3]]['name']; } if ($this->setting('display_region_d')) { $sorted[$id]['region_d_name'] = $locations[$city_info[4]]['name']; } if ($this->setting('display_region_e')) { $sorted[$id]['region_e_name'] = $locations[$city_info[5]]['name']; } if ($this->setting('display_region_f')) { $sorted[$id]['region_f_name'] = $locations[$city_info[6]]['name']; } if ($this->setting('display_region_g')) { $sorted[$id]['region_g_name'] = $locations[$city_info[7]]['name']; } if ($this->setting('display_region_h')) { $sorted[$id]['region_h_name'] = $locations[$city_info[8]]['name']; } if ($this->setting('display_region_i')) { $sorted[$id]['region_i_name'] = $locations[$city_info[9]]['name']; } } unset($sorted[$id]['location_id']); } #todo refresher option $sorted = $table->_fill_related($sorted, 'training_custom_1_option', 'training_custom_1_option_id', 'custom1_phrase'); $sorted = $table->_fill_related($sorted, 'training_custom_2_option', 'training_custom_2_option_id', 'custom2_phrase'); $sorted = $table->_fill_related($sorted, 'training_organizer_option', 'training_organizer_option_id', 'training_organizer_phrase'); $sorted = $table->_fill_related($sorted, 'training_level_option', 'training_level_option_id', 'training_level_phrase'); $sorted = $table->_fill_related($sorted, 'training_method_option', 'training_method_option_id', 'training_method_phrase'); $sorted = $table->_fill_related($sorted, 'trainer_language_option', 'training_primary_language_option_id', 'language_phrase'); $sorted = $table->_fill_related($sorted, 'trainer_language_option', 'training_secondary_language_option_id', 'language_phrase'); $sorted = $table->_fill_intersection_related($sorted, 'training_funding_option', 'training_to_training_funding_option', 'training_funding_option_id', 'funding_phrase'); $sorted = $table->_fill_intersection_related($sorted, 'training_pepfar_categories_option', 'training_to_training_pepfar_categories_option', 'training_pepfar_categories_option_id', 'pepfar_category_phrase'); $sorted = $table->_fill_intersection_related($sorted, 'training_topic_option', 'training_to_training_topic_option', 'training_topic_option_id', 'training_topic_phrase'); if ($this->setting('multi_opt_refresher_course')) { $sorted = $table->_fill_intersection_related($sorted, 'training_refresher_option', 'training_to_training_refresher_option', 'training_refresher_option_id', 'refresher_phrase_option'); } //fill participants require_once 'models/table/Person.php'; $personTable = new Person(); $select = $personTable->select()->from('person', array('id', 'first_name', 'middle_name', 'last_name'))->setIntegrityCheck(false)->join(array('pt' => 'person_to_training'), "pt.person_id = person.id", array('training_id')); $rows = $table->fetchAll($select); foreach ($rows as $row) { $tid = $row->training_id; if ($ids && array_search($tid, $ids) === false) { continue; } // dont print this training, the user has filtered by the url param ('ids') $ra = $row->toArray(); unset($ra['training_id']); $sorted[$tid]['participants'][] = $ra; } //fill participants require_once 'models/table/OptionList.php'; $qualsTable = new OptionList(array('name' => 'training_to_person_qualification_option')); $select = $qualsTable->select()->from('training_to_person_qualification_option', array('training_id', 'person_count_na', 'person_count_male', 'person_count_female'))->setIntegrityCheck(false)->join(array('tq' => 'person_qualification_option'), "tq.id = training_to_person_qualification_option.person_qualification_option_id", array('qualification_phrase')); $rows = $qualsTable->fetchAll($select); foreach ($rows as $row) { $tid = $row->training_id; if ($ids && array_search($tid, $ids) === false) { continue; } // dont print this training, the user has filtered by the url param ('ids') $ra = $row->toArray(); $ra['person_count_na'] = $ra['person_count_na'] . '(na)'; $ra['person_count_male'] = $ra['person_count_male'] . '(male)'; $ra['person_count_female'] = $ra['person_count_female'] . '(female)'; unset($ra['training_id']); $sorted[$tid]['unknown participants'][] = $ra; } //fill trainers require_once 'models/table/TrainingToTrainer.php'; $personTable = new TrainingToTrainer(); $select = $personTable->select()->from('training_to_trainer', array('training_id'))->setIntegrityCheck(false)->join(array('p' => 'person'), "training_to_trainer.trainer_id = p.id", array('id', 'first_name', 'middle_name', 'last_name')); $rows = $table->fetchAll($select); foreach ($rows as $row) { $tid = $row->training_id; if ($ids && array_search($tid, $ids) === false) { continue; } // dont print this training, the user has filtered by the url param ('ids') $ra = $row->toArray(); unset($ra['training_id']); $sorted[$tid]['trainers'][] = $ra; } $this->view->assign('data', $sorted); if ($this->getSanParam('outputType') == 'csv') { $this->sendData($this->reportHeaders(false, $sorted)); } } catch (Exception $e) { echo $e->getMessage(); error_log($e->getMessage()); } }
public function listAction() { if (!$this->isLoggedIn()) { $this->doNoAccessError(); } if (!$this->hasACL('add_edit_users')) { $this->doNoAccessError(); } require_once 'models/table/OptionList.php'; $rowArray = OptionList::suggestionList('user', array('id', 'first_name', 'last_name', 'email', 'username', 'is_blocked'), false, 1000); $rtn = array(); foreach ($rowArray as $key => $val) { if ($val['id'] != 0) { $rtn[] = $val; } } $this->sendData($rtn); }
public function ssProfAction() { if (!$this->hasACL('view_people') and !$this->hasACL('edit_people')) { $this->doNoAccessError(); } $criteria = array(); list($criteria, $location_tier, $location_id) = $this->getLocationCriteriaValues($criteria); $criteria['facilityInput'] = $this->getSanParam('facilityInput'); $criteria['training_title_option_id'] = $this->getSanParam('training_title_option_id'); $criteria['qualification_id'] = $this->getSanParam('qualification_id'); $criteria['ques'] = $this->getSanParam('ques'); $criteria['go'] = $this->getSanParam('go'); $criteria['all'] = $this->getSanParam('all'); if ($criteria['go']) { # var_dump ($_GET); # exit; if ($criteria['all']) { $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); $sql = 'select DISTINCT cmp.person, cmp.question, cmp.option from person as p, person_qualification_option as q, facility as f, (' . $location_sub_query . ') as l, comp as cmp, compres as cmpr'; if ($criteria['training_title_option_id']) { $sql .= ', person_to_training as ptt '; $sql .= ', training as tr '; } $where = array('p.is_deleted = 0'); $where[] = 'cmpr.person = p.id'; $where[] = 'cmp.person = p.id'; $where[] = ' p.primary_qualification_option_id = q.id and p.facility_id = f.id and f.location_id = l.id '; if ($criteria['facilityInput']) { $where[] = ' p.facility_id = "' . $criteria['facilityInput'] . '"'; } if ($criteria['training_title_option_id']) { $where[] = ' p.id = ptt.person_id AND ptt.training_id = tr.id AND tr.training_title_option_id = ' . $criteria['training_title_option_id'] . ' '; } $where[] = ' primary_qualification_option_id IN (SELECT id FROM person_qualification_option WHERE parent_id IN (6, 7, 8, 9) ) '; $where[] = 'cmpr.active = \'Y\''; $where[] = 'cmpr.res = 1'; $where[] = 'cmp.active = \'Y\''; $sql .= ' WHERE ' . implode(' AND ', $where); die(__LINE__ . " - " . $sql); $rowArray = $db->fetchAll($sql); $qss = array(); $nmss = array(); $qss = split(",", "0,1,2,3,4,5,6,7"); $nmss = split("~", "1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,200~01,02,03,04,05,06,07,08,09~31,32,33,34,35,36,37,38~41,42,43,44,45~51,52,53,54,55,56,57,58,59,510,511,512,513,514,515,516,517,518~61,62,63,64,65,66,67~71,72,73,74,75,76,77,78,79,710,711~21,22,23"); $ct; $ct = 0; $rssA = array(); $rssB = array(); $rssC = array(); $rssD = array(); $rssE = array(); $ctt; foreach ($qss as $kys => $vls) { $rssA[$ct] = 0; $rssB[$ct] = 0; $rssC[$ct] = 0; $rssD[$ct] = 0; $rssE[$ct] = 0; $ctt = 0; $wss = split(",", $nmss[$vls]); foreach ($wss as $kyss => $vlss) { foreach ($rowArray as $kss => $vss) { if ($vlss . " " == $vss['question'] . " ") { if ($vss['option'] == "A") { $rssA[$ct] = $rssA[$ct] + 1; } else { if ($vss['option'] == "B") { $rssB[$ct] = $rssB[$ct] + 1; } else { if ($vss['option'] == "C") { $rssC[$ct] = $rssC[$ct] + 1; } else { if ($vss['option'] == "D") { $rssD[$ct] = $rssD[$ct] + 1; } else { if ($vss['option'] == "E") { $rssE[$ct] = $rssE[$ct] + 1; } } } } } $ctt = $ctt + 1; } } } if ($ctt > 0) { $rssA[$ct] = number_format($rssA[$ct] / $ctt * 100, 2); $rssB[$ct] = number_format($rssB[$ct] / $ctt * 100, 2); $rssC[$ct] = number_format($rssC[$ct] / $ctt * 100, 2); $rssD[$ct] = number_format($rssD[$ct] / $ctt * 100, 2); $rssE[$ct] = number_format($rssE[$ct] / $ctt * 100, 2); } $ct = $ct + 1; } $this->viewAssignEscaped('results', $rowArray); $this->viewAssignEscaped('rssA', $rssA); $this->viewAssignEscaped('rssB', $rssB); $this->viewAssignEscaped('rssC', $rssC); $this->viewAssignEscaped('rssD', $rssD); $this->viewAssignEscaped('rssE', $rssE); } else { $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); $sql = 'select DISTINCT cmp.person, cmp.question, cmp.option from person as p, person_qualification_option as q, facility as f, (' . $location_sub_query . ') as l, comp as cmp, compres as cmpr'; if ($criteria['training_title_option_id']) { $sql .= ', person_to_training as ptt '; $sql .= ', training as tr '; } $where = array('p.is_deleted = 0'); $whr = array(); $where[] = 'cmpr.person = p.id'; $where[] = 'cmp.person = p.id'; $where[] = ' p.primary_qualification_option_id = q.id and p.facility_id = f.id and f.location_id = l.id '; if ($criteria['facilityInput']) { $where[] = ' p.facility_id = "' . $criteria['facilityInput'] . '"'; } if ($criteria['training_title_option_id']) { $where[] = ' p.id = ptt.person_id AND ptt.training_id = tr.id AND tr.training_title_option_id = ' . $criteria['training_title_option_id'] . ' '; } $where[] = ' primary_qualification_option_id IN (SELECT id FROM person_qualification_option WHERE parent_id = ' . $criteria['qualification_id'] . ') '; $where[] = 'cmpr.active = \'Y\''; $where[] = 'cmpr.res = 1'; $where[] = 'cmp.active = \'Y\''; $qry = "SELECT id FROM competencies_questions WHERE competencyid IN (" . implode(",", $_GET['competencyselect']) . ")"; $questionresult = $db->fetchAll($qry); $_q = array(); foreach ($questionresult as $qres) { $_q[] = $qres['id']; } $whr[] = 'cmp.question IN (' . implode(",", $_q) . ')'; # if($criteria ['qualification_id']=="6"){ # $whr []= 'cmp.question IN ('."'".str_replace(",","','",$this->getSanParam ( 'listcq' ))."'".')'; # } # if($criteria ['qualification_id']=="7"){ # $qs=split(",",$this->getSanParam ( 'ques' )); # $nms=split("~",$this->getSanParam ( 'listdq' )); # foreach ( $qs as $kys => $vls ) { # $whr []= 'cmp.question IN ('."'".str_replace(",","','",$nms[$vls])."'".')'; # } # } # if($criteria ['qualification_id']=="8"){ # $qs=split(",",$this->getSanParam ( 'ques' )); # $nms=split("~",$this->getSanParam ( 'listnq' )); # foreach ( $qs as $kys => $vls ) { # $whr []= 'cmp.question IN ('."'".str_replace(",","','",$nms[$vls])."'".')'; # } # } # if($criteria ['qualification_id']=="9"){ # $whr []= 'cmp.question IN ('."'".str_replace(",","','",$this->getSanParam ( 'listpq' ))."'".')'; # } if (!empty($where)) { $sql .= ' WHERE ' . implode(' AND ', $where); } if (!empty($whr)) { $sql .= ' AND (' . implode(' OR ', $whr) . ')'; } //todo check everything same here! $rowArray = $db->fetchAll($sql); $qss = array(); $nmss = array(); if ($criteria['qualification_id'] == "6") { $qss = split(",", $this->getSanParam('ques')); $nmss = split("~", $this->getSanParam('listcq')); } if ($criteria['qualification_id'] == "7") { $qss = split(",", $this->getSanParam('ques')); $nmss = split("~", $this->getSanParam('listdq')); } if ($criteria['qualification_id'] == "8") { $qss = split(",", $this->getSanParam('ques')); $nmss = split("~", $this->getSanParam('listnq')); } if ($criteria['qualification_id'] == "9") { $qss = split(",", $this->getSanParam('ques')); $nmss = split("~", $this->getSanParam('listpq')); } $ct; $ct = 0; $rssA = array(); $rssB = array(); $rssC = array(); $rssD = array(); $rssE = array(); $ctt; foreach ($qss as $kys => $vls) { $rssA[$ct] = 0; $rssB[$ct] = 0; $rssC[$ct] = 0; $rssD[$ct] = 0; $rssE[$ct] = 0; $ctt = 0; $wss = split(",", $nmss[$vls]); foreach ($wss as $kyss => $vlss) { foreach ($rowArray as $kss => $vss) { if ($vlss . " " == $vss['question'] . " ") { if ($vss['option'] == "A") { $rssA[$ct] = $rssA[$ct] + 1; } else { if ($vss['option'] == "B") { $rssB[$ct] = $rssB[$ct] + 1; } else { if ($vss['option'] == "C") { $rssC[$ct] = $rssC[$ct] + 1; } else { if ($vss['option'] == "D") { $rssD[$ct] = $rssD[$ct] + 1; } else { if ($vss['option'] == "E") { $rssE[$ct] = $rssE[$ct] + 1; } } } } } $ctt = $ctt + 1; } } } if ($ctt > 0) { $rssA[$ct] = number_format($rssA[$ct] / $ctt * 100, 2); $rssB[$ct] = number_format($rssB[$ct] / $ctt * 100, 2); $rssC[$ct] = number_format($rssC[$ct] / $ctt * 100, 2); $rssD[$ct] = number_format($rssD[$ct] / $ctt * 100, 2); $rssE[$ct] = number_format($rssE[$ct] / $ctt * 100, 2); } $ct = $ct + 1; } $this->viewAssignEscaped('results', $rowArray); $this->viewAssignEscaped('rssA', $rssA); $this->viewAssignEscaped('rssB', $rssB); $this->viewAssignEscaped('rssC', $rssC); $this->viewAssignEscaped('rssD', $rssD); $this->viewAssignEscaped('rssE', $rssE); } } $this->view->assign('criteria', $criteria); $this->viewAssignEscaped('locations', Location::getAll()); require_once 'models/table/TrainingTitleOption.php'; $titleArray = TrainingTitleOption::suggestionList(false, 10000); $this->viewAssignEscaped('courses', $titleArray); $qualificationsArray = OptionList::suggestionListHierarchical('person_qualification_option', 'qualification_phrase', false, false); $this->viewAssignEscaped('qualifications', $qualificationsArray); $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); $helper = new Helper(); $this->viewAssignEscaped("competencies", $helper->getCompetencies()); $this->viewAssignEscaped("compqualification", $helper->getQualificationCompetencies()); }
public function organizerListAction() { require_once 'models/table/OptionList.php'; $rowArray = OptionList::suggestionList('training_organizer_option', 'training_organizer_phrase', $this->_getParam('query')); $this->sendData($rowArray); }
public function facilityReport() { require_once 'models/table/TrainingLocation.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 = $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'); } if ($this->view->mode == 'search') { $sql = "SELECT MAX(training_start_date) as \"start\" FROM training "; $rowArray = $db->fetchAll($sql); $end_default = $rowArray[0]['start']; $parts = explode('-', $end_default); $criteria['end-year'] = $parts[0]; $criteria['end-month'] = $parts[1]; $criteria['end-day'] = $parts[2]; } else { $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_title_option_id'] = $this->getSanParam('training_title_option_id'); $criteria['training_title_id'] = $this->getSanParam('training_title_id'); $criteria['training_location_id'] = $this->getSanParam('training_location_id'); $criteria['training_organizer_id'] = $this->getSanParam('training_organizer_id'); $criteria['training_pepfar_id'] = $this->getSanParam('training_pepfar_id'); $criteria['training_topic_id'] = $this->getSanParam('training_topic_id'); $criteria['training_level_id'] = $this->getSanParam('training_level_id'); $criteria['facility_type_id'] = $this->getSanParam('facility_type_id'); $criteria['facility_sponsor_id'] = $this->getSanParam('facility_sponsor_id'); $criteria['facilityInput'] = $this->getSanParam('facilityInput'); $criteria['is_tot'] = $this->getSanParam('is_tot'); $criteria['go'] = $this->getSanParam('go'); $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 ['showCadre'] = ($this->getSanParam ( 'showCadre' ) or ($criteria ['doCount'] and ($criteria ['cadre'] or $criteria ['cadre'] === '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['showLocation'] = ($this->getSanParam('showLocation') or $criteria['doCount'] and $criteria['training_location_id']); $criteria['showOrganizer'] = ($this->getSanParam('showOrganizer') or $criteria['doCount'] and ($criteria['training_organizer_id'] or $criteria['training_organizer_id'] === '0')); $criteria['showPepfar'] = ($this->getSanParam('showPepfar') or $criteria['doCount'] and ($criteria['training_pepfar_id'] or $criteria['training_pepfar_id'] === '0')); $criteria['showTopic'] = ($this->getSanParam('showTopic') or $criteria['doCount'] and ($criteria['training_topic_id'] or $criteria['training_topic_id'] === '0')); $criteria['showLevel'] = ($this->getSanParam('showLevel') or $criteria['doCount'] and $criteria['training_level_id']); $criteria['showType'] = ($this->getSanParam('showType') or $criteria['doCount'] and ($criteria['facility_type_id'] or $criteria['facility_type_id'] === '0')); $criteria['showSponsor'] = ($this->getSanParam('showSponsor') or $criteria['doCount'] and $criteria['facility_sponsor_id']); $criteria['showFacility'] = true; //($this->getSanParam('showFacility') OR ($criteria['doCount'] and $criteria['facility_name'])); $criteria['showTot'] = ($this->getSanParam('showTot') or ($criteria['doCount'] and $criteria['is_tot'] !== '' or $criteria['is_tot'] === '0')); if ($criteria['go']) { $sql = 'SELECT '; if ($criteria['doCount']) { $sql .= ' COUNT(pt.person_id) as "cnt", pt.facility_name '; } else { $sql .= ' DISTINCT pt.id as "id", pt.facility_name, pt.training_start_date '; } if ($criteria['showFacility']) { $sql .= ', pt.facility_name '; } if ($criteria['showTrainingTitle'] or $this->view->mode == 'search') { $sql .= ', pt.training_title '; } if ($criteria['showDistrict']) { $sql .= ', pt.district_name, pt.district_id '; } if ($criteria['showCadre']) { $sql .= ', cad.id, cad.cadrename '; } if ($criteria['showProvince']) { $sql .= ', pt.province_name, pt.province_id '; } if ($criteria['showRegionC']) { $sql .= ', pt.region_c_name, pt.region_c_id '; } if ($criteria['showLocation']) { $sql .= ', pt.training_location_name '; } 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') { $where[] = ' pt.training_title_option_id = ' . $criteria['training_title_id']; } if ($criteria['facilityInput']) { $where[] = ' pt.facility_id = \'' . $criteria['facilityInput'] . '\''; } if ($criteria['training_location_id']) { $where[] = ' pt.training_location_id = \'' . $criteria['training_location_id'] . '\''; } if ($criteria['training_organizer_id'] or $criteria['training_organizer_id'] === '0') { $where[] = ' pt.training_organizer_option_id = \'' . $criteria['training_organizer_id'] . '\''; } if ($criteria['training_topic_id'] or $criteria['training_topic_id'] === '0') { $where[] = ' ttopic.training_topic_option_id = \'' . $criteria['training_topic_id'] . '\''; } if ($criteria['facility_type_id'] or $criteria['facility_type_id'] === '0') { $where[] = ' pt.type_option_id = \'' . $criteria['facility_type_id'] . '\''; } if ($criteria['facility_sponsor_id'] or $criteria['facility_sponsor_id'] === '0') { $where[] = ' pt.sponsor_option_id = \'' . $criteria['facility_sponsor_id'] . '\''; } if ($criteria['training_level_id']) { $where[] = ' pt.training_level_option_id = \'' . $criteria['training_level_id'] . '\''; } if ($criteria['training_pepfar_id'] or $criteria['training_pepfar_id'] === '0') { $where[] = ' tpep.training_pepfar_categories_option_id = \'' . $criteria['training_pepfar_id'] . '\''; } if (intval($criteria['end-year']) and $criteria['start-year']) { $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 . '\' '; } if ($criteria['is_tot'] or $criteria['is_tot'] === '0') { $where[] = ' pt.is_tot = ' . $criteria['is_tot']; } if ($where) { $sql .= ' WHERE ' . implode(' AND ', $where); } if ($criteria['doCount']) { $groupBy = array(); if ($criteria['showFacility']) { $groupBy[] = ' pt.facility_id'; } if ($criteria['showTrainingTitle']) { $groupBy[] = ' pt.training_title_option_id'; } if ($criteria['showProvince']) { $groupBy[] = ' pt.province_id'; } if ($criteria['showDistrict']) { $groupBy[] = ' pt.district_id'; } if ($criteria['showCadre']) { $groupBy[] = ' pt.id'; } if ($criteria['showRegionC']) { $groupBy[] = ' pt.region_c_id'; } if ($criteria['showLocation']) { $groupBy[] = ' pt.training_location_id'; } if ($criteria['showOrganizer']) { $groupBy[] = ' pt.training_organizer_option_id'; } if ($criteria['showTopic']) { $groupBy[] = ' ttopic.training_topic_option_id'; } if ($criteria['showLevel']) { $groupBy[] = ' pt.training_level_option_id'; } if ($criteria['showPepfar']) { $groupBy[] = ' tpep.training_pepfar_categories_option_id'; } if ($criteria['showType']) { $groupBy[] = ' pt.type_option_id'; } if ($criteria['showSponsor']) { $groupBy[] = ' pt.sponsor_option_id'; } if ($criteria['showTot']) { $groupBy[] = ' pt.is_tot'; } if ($groupBy) { $groupBy = ' GROUP BY ' . implode(', ', $groupBy); } $sql .= $groupBy; } else { if ($criteria['showPepfar'] || $criteria['showTopic']) { $sql .= ' GROUP BY pt.id'; } } $rowArray = $db->fetchAll($sql . ' ORDER BY facility_name ASC '); if ($criteria['doCount']) { $count = 0; foreach ($rowArray as $row) { $count += $row['cnt']; } } else { $count = count($rowArray); } if ($this->_getParam('outputType')) { $this->sendData($this->reportHeaders(false, $rowArray)); } } else { $count = 0; $rowArray = array(); } $criteria['go'] = $this->getSanParam('go'); //not sure why we are getting multiple PEPFARS foreach ($rowArray as $key => $row) { if (isset($row['pepfar_category_phrase'])) { $rowArray[$key]['pepfar_category_phrase'] = implode(',', array_unique(explode(',', $row['pepfar_category_phrase']))); } } $this->viewAssignEscaped('results', $rowArray); $this->view->assign('count', $count); $this->view->assign('criteria', $criteria); //facilities list $fArray = OptionList::suggestionList('facility', array('facility_name', 'id'), false, 9999); $facilitiesArray = array(); foreach ($fArray as $key => $val) { if ($val['id'] != 0) { $facilitiesArray[] = $val; } } $this->viewAssignEscaped('facilities', $facilitiesArray); //locations $locations = Location::getAll(); $this->viewAssignEscaped('locations', $locations); //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); //course //$courseArray = Course::suggestionList(false,10000); //$this->viewAssignEscaped('courses',$courseArray); //location // location drop-down $tlocations = TrainingLocation::selectAllLocations($this->setting('num_location_tiers')); $this->viewAssignEscaped('tlocations', $tlocations); //organizers $organizersArray = OptionList::suggestionList('training_organizer_option', 'training_organizer_phrase', false, false, false); $this->viewAssignEscaped('organizers', $organizersArray); //topics $topicsArray = OptionList::suggestionList('training_topic_option', 'training_topic_phrase', false, false, false); $this->viewAssignEscaped('topics', $topicsArray); //levels $levelArray = OptionList::suggestionList('training_level_option', 'training_level_phrase', false, false); $this->viewAssignEscaped('levels', $levelArray); //pepfar $organizersArray = OptionList::suggestionList('training_pepfar_categories_option', 'pepfar_category_phrase', false, false, false); $this->viewAssignEscaped('pepfars', $organizersArray); //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); }
/** * Returns distinct values instead of id/value */ public static function suggestionListValues($table, $cols, $match = false, $limit = 100, $removeUnknown = true, $where = false) { $topicTable = new OptionList(array('name' => $table)); if (is_string($cols)) { $cols = array($cols); } $select = $topicTable->select()->distinct()->from($table, $cols); //look for char start if ($match) { $select->where($cols[0] . ' LIKE ? ', $match . '%'); } $select->where('is_deleted = 0'); if ($where) { $select->where($where); } $select->order($cols[0] . ' ASC'); if ($limit) { $select->limit($limit, 0); } $rows = $topicTable->fetchAll($select); $rowArray = $rows->toArray(); // unset 'unknown' if ($removeUnknown) { foreach ($rowArray as $key => $row) { if ($row[$cols[0]] == 'unknown') { unset($rowArray[$key]); } } } return $rowArray; }
public function custom2ListAction() { require_once 'models/table/OptionList.php'; $rowArray = OptionList::suggestionList('person_custom_2_option', 'custom2_phrase', $this->getSanParam('query')); $this->sendData($rowArray); }
protected static function suggestionQuery($match = false, $limit = 100) { require_once 'models/table/OptionList.php'; $topicTable = new OptionList(array('name' => 'location_city')); $select = $topicTable->select()->from('location_city', array('city_name', 'p.province_name', 'd.district_name', 'id', 'parent_district_id', 'parent_province_id'))->setIntegrityCheck(false); $select->joinLeft(array('d' => 'location_district'), 'location_city.parent_district_id = d.id', 'd.district_name'); $select->joinLeft(array('p' => 'location_province'), 'location_city.parent_province_id = p.id', 'p.province_name'); //look for char start if ($match) { $select->where('city_name LIKE ? ', $match . '%'); } $select->where('location_city.is_deleted = 0'); $select->order('city_name ASC'); if ($limit) { $select->limit($limit, 0); } $rows = $topicTable->fetchAll($select); return $rows; }
public static function suggestionQuery($match = false, $limit = 100, $field = 'last_name', $fieldsSelect = array('p.last_name', 'p.first_name', 'p.birthdate'), $fieldAdditional = false, $fieldAndWhere = false) { require_once 'models/table/OptionList.php'; $topicTable = new OptionList(array('name' => 'person')); $select = $topicTable->select()->distinct()->from(array('p' => 'person'), $fieldsSelect); if (count($fieldsSelect) > 1) { //if there's only one field, then assume we just want distinct names and nothing else $select->setIntegrityCheck(false)->join(array('f' => 'facility'), "p.facility_id = f.id", array('facility_name')); } if (array_search('q.qualification_phrase', $fieldsSelect)) { $select->setIntegrityCheck(false)->join(array('q' => 'person_qualification_option'), 'p.primary_qualification_option_id = q.id'); } $select->where(' p.is_deleted = 0'); //look for char start if ($match) { $select->where("{$field} LIKE ? ", $match . '%'); if ($fieldAdditional) { $select->orWhere("{$fieldAdditional} LIKE ? ", $match . '%'); } } if ($fieldAndWhere) { foreach ($fieldAndWhere as $fieldname => $matchstring) { $select->where("{$fieldname} LIKE ? ", $matchstring . '%'); } } //$select->where('trainer.is_deleted = 0 AND trainer.is_active = 1'); $select->order("{$field} ASC"); // foreach($fieldsSelect as $otherfield) { $select->order("last_name ASC"); $select->order("first_name ASC"); $select->order("middle_name ASC"); // } if ($limit) { $select->limit($limit, 0); } $rows = $topicTable->fetchAll($select); return $rows; }
public function assignTrainingAction() { $id = $this->getSanParam('id'); $this->view->assign('id', $id); require_once 'models/table/Training.php'; require_once 'models/table/OptionList.php'; $training = new Training(); $rows = $training->find($id); $row = $rows->current(); $this->view->assign('training', $row); $this->view->assign('training_name', $training->getCourseName($id)); $evaluations = OptionList::suggestionList('evaluation', array('id', 'title')); $this->view->assign('evaluations', $evaluations); //find currently selected $evalTable = new OptionList(array('name' => 'evaluation_to_training')); $select = $evalTable->select()->from('evaluation_to_training', array('evaluation_id'))->where('training_id = ' . $id); $row = $evalTable->fetchRow($select); if ($row) { $this->view->assign('evaluation_id', $row->evaluation_id); } $request = $this->getRequest(); if ($request->isPost()) { $status = ValidationContainer::instance(); $evaluation_id = $this->getSanParam('evaluation_id'); $status->setStatusMessage(t('The evaluation has been assigned.')); $eval_id = $this->getSanParam('evaluation_id'); require_once 'models/table/MultiOptionList.php'; MultiOptionList::updateOptions('evaluation_to_training', 'evaluation', 'training_id', $id, 'evaluation_id', array($eval_id => $eval_id)); $status->setRedirect('/training/edit/id/' . $id); $this->sendData($status); } }
public function viewAction() { require_once 'models/table/OptionList.php'; if ($id = $this->getSanParam('id')) { if ($this->hasACL('edit_people')) { //redirect to edit mode $this->_redirect(str_replace('view', 'edit', 'http://' . $_SERVER['SERVER_NAME'] . $_SERVER['REQUEST_URI'])); } $facility = new Facility(); $facilityRow = $facility->fetchRow('id = ' . $id); $facilityArray = $facilityRow->toArray(); } else { $facilityArray = array(); $facilityArray['id'] = null; } //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); //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); //sponsors $sponsorsArray = OptionList::suggestionList('facility_sponsors', 'facility_sponsor_phrase_id', false, false, true, 'id = ' . $id); $stable = new ITechTable(array('name' => 'facility_sponsors')); $select = $stable->select()->where('facility_id = ' . $id); $rows = $stable->fetchAll($select); if ($rows) { $this->viewAssignEscaped('sponsor_data', $rows->toArray()); } list($cname, $prov, $dist, $regc) = Location::getCityInfo($facilityRow->location_id, $this->setting('num_location_tiers')); $facilityArray['facility_city'] = $cname; $facilityArray['region_c_id'] = $regc; $facilityArray['district_id'] = $dist; $facilityArray['province_id'] = $prov; $this->viewAssignEscaped('facility', $facilityArray); }
/** * Data dump helper * * @param unknown_type $sorted_data * @param unknown_type $option_table * @param unknown_type $id_col * @param unknown_type $value_col * @param unknown_type $remove_id * @return unknown */ public function _fill_lookup($sorted_data, $option_table, $id_col, $value_col, $remove_id = true) { require_once 'OptionList.php'; $topicTable = new OptionList(array('name' => $option_table)); $select = $topicTable->select()->from($option_table, array('id', $value_col)); $rows = $this->fetchAll($select); $topic = array(); foreach ($rows as $r) { $topic[$r->id] = $r->{$value_col}; } foreach ($sorted_data as $id => $datum) { if (isset($datum[$id_col]) && isset($topic[$datum[$id_col]])) { $sorted_data[$id][$value_col] = $topic[$datum[$id_col]]; } if ($remove_id) { unset($sorted_data[$id][$id_col]); } } return $sorted_data; }
/** * 取得文章列表:用于商品关联文章 * @param object $filters 过滤条件 */ function get_article_list($filter) { /* 创建数据容器对象 */ $ol = new OptionList(); /* 取得过滤条件 */ $where = ' WHERE a.cat_id = c.cat_id AND c.cat_type = 1 '; $where .= isset($filter->title) ? " AND a.title LIKE '%" . mysql_like_quote($filter->title) . "%'" : ''; /* 取得数据 */ $sql = 'SELECT a.article_id, a.title ' . 'FROM ' . $GLOBALS['ecs']->table('article') . ' AS a, ' . $GLOBALS['ecs']->table('article_cat') . ' AS c ' . $where; $res = $GLOBALS['db']->query($sql); while ($row = $GLOBALS['db']->fetchRow($res)) { $ol->add_option($row['article_id'], $row['title']); } /* 生成列表 */ $ol->build_select(); }
/** * 取得文章列表:用于商品关联文章 * * @param object $filters * 过滤条件 */ function get_article_list($filter) { /* 创建数据容器对象 */ $ol = new OptionList(); /* 取得过滤条件 */ $where = ' WHERE a.cat_id = c.cat_id AND c.cat_type = 1 '; $where .= isset($filter->title) ? " AND a.title LIKE '%" . mysql_like_quote($filter->title) . "%'" : ''; /* 取得数据 */ $sql = 'SELECT a.article_id, a.title ' . 'FROM ' . M()->pre . 'article ' . ' AS a, ' . M()->pre . 'article_cat ' . ' AS c ' . $where; $res = M()->query($sql); foreach ($res as $key => $row) { $ol->add_option($row['article_id'], $row['title']); } /* 生成列表 */ $ol->build_select(); }
public static function qualificationsDropDown($name, $selectedVal) { $o = array(); $o[] = '<select id="' . $name . '" name="' . $name . '">'; $o[] = '<option value="">--' . t('choose') . '--</option>'; $lastParent = null; require_once 'models/table/OptionList.php'; $qualificationsArray = OptionList::suggestionListHierarchical('person_qualification_option', 'qualification_phrase', false, false, array('0 AS is_default', 'child.is_default')); foreach ($qualificationsArray as $vals) { if (!$vals['id']) { $lastParent = $vals['parent_phrase']; $o[] = '<option value="' . $vals['parent_id'] . '" ' . ($selectedVal == $vals['parent_id'] ? 'selected="selected"' : '') . '>' . htmlspecialchars($vals['parent_phrase']) . '</option>'; } else { $o[] = '<option value="' . $vals['id'] . '" ' . ($selectedVal == $vals['id'] ? 'selected="selected"' : '') . '> ' . htmlspecialchars($vals['qualification_phrase']) . '</option>'; } } $o[] = '</select>'; return implode(PHP_EOL, $o); }
public function skillsmartOccupationalCatsAction() { $parent = $this->getSanParam('parent'); if ($parent or $this->getSanParam('redirect')) { $editTable = new EditTableController($this); $editTable->table = 'occupational_categories'; $editTable->fields = array('category_phrase' => 'Category'); $editTable->label = 'Occupational Category'; $editTable->dependencies = array('occupational_category_id' => 'person'); $editTable->where = 'parent_id = ' . $parent; $editTable->insertExtra = array('parent_id' => $parent); $editTable->allowDefault = true; $editTable->execute(); } $parentArray = OptionList::suggestionList('occupational_categories', 'category_phrase', false, false, true, 'parent_id IS NULL'); $this->viewAssignEscaped('parents', $parentArray); $this->view->assign('parent', $parent); }