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