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