/**
* @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);
}
