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 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 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 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 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); }
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); }
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 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); }
public function custom2ListAction() { require_once 'models/table/OptionList.php'; $rowArray = OptionList::suggestionList('person_custom_2_option', 'custom2_phrase', $this->getSanParam('query')); $this->sendData($rowArray); }
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()); }