public function assignEvaluationAction()
{
$id = $this->getSanParam('id');
$this->view->assign('id', $id);
$evaluation = new Evaluation();
$rows = $evaluation->find($id);
$row = $rows->current();
$this->view->assign('evaluation', $row);
// restricted access?? only show trainings we have the ACL to view
require_once 'views/helpers/TrainingViewHelper.php';
$orgWhere = '';
$org_allowed_ids = allowed_organizer_access($this);
if ($org_allowed_ids) {
// doesnt have acl 'training_organizer_option_all'
$org_allowed_ids = implode(',', $org_allowed_ids);
$orgWhere = " training_organizer_option_id in ({$org_allowed_ids}) ";
}
// restricted access?? only show organizers that belong to this site if its a multi org site
$site_orgs = allowed_organizer_in_this_site($this);
// for sites to host multiple training organizers on one domain
$allowedWhereClause .= $site_orgs ? " AND training_organizer_option_id in ({$site_orgs}) " : "";
require_once 'models/table/Training.php';
$tableObj = new Training();
$trainings = $tableObj->getTrainings($orgWhere);
$assigned = $evaluation->fetchAssignments($id);
foreach ($trainings as $k => $r) {
$trainings[$k]['input_checkbox'] = '<input type="checkbox" name="training_ids[]" value="' . $r['training_id'] . '" ' . (array_search($r['training_id'], $assigned) !== false ? ' checked="checked" ' : '') . ' >';
}
$this->view->assign('trainings', $trainings);
$request = $this->getRequest();
if ($request->isPost()) {
$status = ValidationContainer::instance();
$training_ids = $this->getSanParam('training_ids');
$adjusted = array();
foreach ($training_ids as $tr) {
$adjusted[$tr] = $tr;
}
require_once 'models/table/MultiOptionList.php';
MultiOptionList::updateOptions('evaluation_to_training', 'training', 'evaluation_id', $id, 'training_id', $adjusted);
$_SESSION['status'] = t('The') . ' ' . t('Trainings') . ' ' . t('have been assigned.');
$status->setStatusMessage(t('The') . ' ' . t('Trainings') . ' ' . t('have been assigned.'));
$status->setRedirect('/evaluation/browse');
$this->sendData($status);
}
}
public function indexAction()
{
if ($this->hasACL('edit_employee') && $this->setting('module_employee_enabled')) {
if ($this->hasACL('in-service') == false && $this->hasACL('pre-service') == false) {
$this->_redirect('employee');
exit;
}
}
if (strstr($_SERVER['REQUEST_URI'], 'index/index') === false) {
if ($this->hasACL('in_service')) {
$this->_redirect('index/index');
exit;
} elseif ($this->hasACL('pre_service')) {
$this->_redirect('dash/dash');
exit;
}
$this->_redirect('index/index');
exit;
}
$db = Zend_Db_Table_Abstract::getDefaultAdapter();
$sql = "SELECT q2.qualification_phrase, COUNT(q2.qualification_phrase) CNT FROM person p INNER JOIN person_qualification_option q1 ON p.primary_qualification_option_id = q1.id INNER JOIN person_qualification_option q2 ON q1.parent_id = q2.id WHERE p.is_deleted = 0 GROUP BY q2.qualification_phrase;";
$rowyArray1 = $db->fetchAll($sql);
$sql = "SELECT q3.qualification_phrase, 0 CNT FROM person_qualification_option q3 WHERE q3.id NOT IN (SELECT q2.id CNT FROM person p INNER JOIN person_qualification_option q1 ON p.primary_qualification_option_id = q1.id INNER JOIN person_qualification_option q2 ON q1.parent_id = q2.id WHERE p.is_deleted = 0) AND q3.parent_id IS NULL;";
$rowyArray2 = $db->fetchAll($sql);
$rowyArray = array_merge($rowyArray1, $rowyArray2);
$this->viewAssignEscaped('rowy', $rowyArray);
$sql = "SELECT p.comments FROM person p INNER JOIN person_qualification_option q1 ON p.primary_qualification_option_id = q1.id INNER JOIN person_qualification_option q2 ON q1.parent_id = q2.id WHERE p.is_deleted = 0 AND q2.id = 8";
$rowsArray = $db->fetchAll($sql);
$NIMART = 0;
foreach ($rowsArray as $key => $row) {
$NIMARTsplit = split("§", $rowsArray[$key]['comments']);
if (strlen($NIMARTsplit[21]) > 0) {
if ($NIMARTsplit[21] = "Nurse Initiating ART") {
$NIMART = $NIMART + 1;
}
}
}
$this->viewAssignEscaped('NIMART', $NIMART);
// retrieve list of incomplete courses created by user
if ($this->hasACL('edit_course')) {
require_once 'models/table/Training.php';
require_once 'models/Session.php';
require_once 'views/helpers/EditTableHelper.php';
require_once 'views/helpers/TrainingViewHelper.php';
$uid = Session::getCurrentUserId();
// Find incomplete training and future trainings
$trainingFields = array('training_title' => t('Course Name'), 'training_start_date' => t('Start Date'), 'training_location_name' => t('Training Center'), 'creator' => t('Created By'));
foreach (array_keys($trainingFields) as $key) {
$colCustom[$key] = 'sortable:true';
}
$colStatic = array_keys($trainingFields);
// all
$editLinkInfo['disabled'] = 1;
$linkInfo = array('linkFields' => array_keys($trainingFields), 'linkId' => 'id', 'linkUrl' => Settings::$COUNTRY_BASE_URL . '/training/edit/id/%id%');
// restricted access?? only show trainings we have the ACL to view // add this to every query
$org_allowed_ids = allowed_org_access_full_list($this);
// doesnt have acl 'training_organizer_option_all'
$allowedWhereClause = $org_allowed_ids ? " AND training_organizer_option_id in ({$org_allowed_ids}) " : "";
// restricted access?? only show organizers that belong to this site if its a multi org site
$site_orgs = allowed_organizer_in_this_site($this);
// for sites to host multiple training organizers on one domain
$allowedWhereClause .= $site_orgs ? " AND training_organizer_option_id in ({$site_orgs}) " : "";
// Incomplete
$tableObj = new Training();
$rowsPast = $tableObj->getIncompleteTraining($uid, 'training_start_date < NOW() ' . $allowedWhereClause)->toArray();
if ($rowsPast) {
$html = EditTableHelper::generateHtmlTraining('TrainingPast', $rowsPast, $trainingFields, $colStatic, $linkInfo, $editLinkInfo, $colCustom);
$this->view->assign('tableTrainingPast', $html);
}
// Future
$tableObj = new Training();
$rowsFuture = $tableObj->getIncompleteTraining($uid, 'training_start_date >= NOW()' . $allowedWhereClause, '')->toArray();
if ($rowsFuture) {
$html = EditTableHelper::generateHtmlTraining('TrainingFuture', $rowsFuture, $trainingFields, $colStatic, $linkInfo, $editLinkInfo, $colCustom);
$this->view->assign('tableTrainingFuture', $html);
}
// Unapproved
if ($this->setting('module_approvals_enabled')) {
$tableObj = new Training();
$unapproved = $tableObj->getUnapprovedTraining("1" . $allowedWhereClause);
// everything
if ($unapproved) {
$linkInfoUnapprov = $linkInfo;
if (!$this->hasACL('approve_trainings')) {
$linkInfoUnapprov['linkFields'] = array('training_title');
}
$trainingFieldsUnapprov = $trainingFields;
$trainingFieldsUnapprov['message'] = t('Message');
$colStatic['message'] = 'message';
$html = EditTableHelper::generateHtmlTraining('unapproved', $unapproved, $trainingFieldsUnapprov, $colStatic, $linkInfoUnapprov, $editLinkInfo, $colCustom);
$this->view->assign('tableUnapproved', $html);
}
}
//YTD, start at April 1
if ($ytdStart = $this->setting('fiscal_year_start')) {
$ytdStart = date('Y-n-j', strtotime($ytdStart));
$this->view->assign('ytdStart', $ytdStart);
//get total unique participants
$db = Zend_Db_Table_Abstract::getDefaultAdapter();
$sql = "SELECT COUNT(DISTINCT person_id) as \"unique_p\" from person_to_training left join training on (training.id = training_id and training.is_deleted = 0) where training_start_date > " . $ytdStart . $allowedWhereClause;
$rowArray = $db->fetchRow($sql);
$this->view->assign('unique_participants', $rowArray['unique_p']);
} else {
$ytdStart = date('Y') - (date('n') < 4 ? 1 : 0) . '-04-01';
$this->view->assign('ytdStart', $ytdStart);
//get total unique participants
$db = Zend_Db_Table_Abstract::getDefaultAdapter();
$sql = "SELECT COUNT(DISTINCT person_id) as \"unique_p\" from person_to_training left join training on (training.id = training_id and training.is_deleted = 0) where 1 " . $allowedWhereClause;
$rowArray = $db->fetchRow($sql);
$this->view->assign('unique_participants', $rowArray['unique_p']);
}
$allowedOrgJoin = $allowedWhereClause ? ' LEFT JOIN training ON training.id = training_id WHERE training.is_deleted = 0 ' . $allowedWhereClause : '';
// only show trainings we're allowed to see by access level
//get participants total and by YTD
$db = Zend_Db_Table_Abstract::getDefaultAdapter();
$sql = "SELECT COUNT(person_id) as \"attendees\" from person_to_training" . $allowedOrgJoin;
$rowArray = $db->fetchRow($sql);
$this->view->assign('attendees', $rowArray['attendees']);
$sql = "SELECT COUNT(person_id) as \"attendees\" FROM training, person_to_training as pt WHERE pt.training_id = training.id AND training_start_date >= '{$ytdStart}'" . $allowedWhereClause;
$rowArray = $db->fetchRow($sql);
$this->view->assign('attendees_ytd', $rowArray['attendees']);
//get total unique trainers
if ($allowedOrgJoin) {
$sql = "SELECT COUNT(DISTINCT trainer_id) as \"unique_t\" FROM training_to_trainer" . $allowedOrgJoin;
// trainers in viewable trainings
} else {
$sql = "SELECT COUNT(person_id) as \"unique_t\" from trainer";
}
$rowArray = $db->fetchRow($sql);
$this->view->assign('unique_trainers', $rowArray['unique_t']);
//get total trainers and by YTD
$sql = "SELECT COUNT(trainer_id) as \"trainers\" FROM training_to_trainer" . $allowedOrgJoin;
$rowArray = $db->fetchRow($sql);
$this->view->assign('trainers', $rowArray['trainers']);
$sql = "SELECT COUNT(tt.trainer_id) as \"trainers\" FROM training, training_to_trainer as tt WHERE tt.training_id = training.id AND training_start_date >= '{$ytdStart}'" . $allowedWhereClause;
$rowArray = $db->fetchRow($sql);
$this->view->assign('trainers_ytd', $rowArray['trainers']);
//get trainings
// total and YTD
$sql = "SELECT COUNT(id) as \"trainings\", MIN(training_start_date) as \"min_date\",MAX(training_start_date) as \"max_date\" from training WHERE is_deleted = 0" . $allowedWhereClause;
$rowArray = $db->fetchRow($sql);
$this->view->assign('trainings', $rowArray['trainings']);
$this->view->assign('min_date', $rowArray['min_date']);
$this->view->assign('max_date', $rowArray['max_date']);
$sql = "SELECT COUNT(id) as \"trainings\" from training WHERE training_start_date >= '{$ytdStart}' AND is_deleted = 0" . $allowedWhereClause;
$rowArray = $db->fetchRow($sql);
$this->view->assign('trainings_ytd', $rowArray['trainings']);
}
/****************************************************************************************************************/
/* Attached Files */
require_once 'views/helpers/FileUpload.php';
$PARENT_COMPONENT = 'home';
FileUpload::displayFiles($this, $PARENT_COMPONENT, 1, $this->hasACL('admin_files'));
// File upload form
if ($this->hasACL('admin_files')) {
$this->view->assign('filesForm', FileUpload::displayUploadForm($PARENT_COMPONENT, 1, FileUpload::$FILETYPES));
}
/****************************************************************************************************************/
}
public function partnersAction()
{
require_once 'models/table/Helper.php';
require_once 'views/helpers/FormHelper.php';
require_once 'views/helpers/DropDown.php';
require_once 'views/helpers/Location.php';
require_once 'views/helpers/CheckBoxes.php';
require_once 'views/helpers/TrainingViewHelper.php';
$criteria = $this->getAllParams();
if ($criteria['go']) {
$where = array();
$criteria['last_selected_rgn'] = regionFiltersGetLastID('', $criteria);
list($a, $location_tier, $location_id) = $this->getLocationCriteriaValues($criteria);
list($locationFlds, $locationsubquery) = Location::subquery($this->setting('num_location_tiers'), $location_tier, $location_id);
$sql = "SELECT\r\n\t\t\t\t\tpartner.*,\r\n\t\t\t\t\tpartner.id,partner.partner,partner.location_id," . implode(',', $locationFlds) . "\r\n\t\t\t\t\t,GROUP_CONCAT(distinct facility.facility_name) as facilities\r\n\t\t\t\t\t,CASE WHEN annual_cost REGEXP '[^!0-9,\\.][0-9\\.,]+' THEN SUBSTRING(annual_cost, 2) ELSE annual_cost END AS 'annual_cost_to_compare'\r\n\t\t\t\t\t,COUNT(e.id) AS pcnt\r\n\t\t\t\t\tFROM partner LEFT JOIN ({$locationsubquery}) as l ON l.id = partner.location_id\r\n\t\t\t\t\tLEFT JOIN partner_to_funder funders ON partner.id = funders.partner_id\r\n\t\t\t\t\tLEFT JOIN partner_funder_option funderopt ON funders.partner_funder_option_id = funderopt.id\r\n\t\t\t\t\tLEFT JOIN partner_to_subpartner subpartners ON subpartners.partner_id = partner.id\r\n\t\t\t\t\tLEFT JOIN employee e on e.partner_id = partner.id\r\n\t\t\t\t\tLEFT JOIN facility ON e.site_id = facility.id\r\n\t\t\t\t\t";
if ($criteria['facility_type_option_id']) {
$sql .= " LEFT JOIN facility_type_option fto ON fto.id = facility.type_option_id ";
}
// restricted access?? only show partners by organizers that we have the ACL to view
#$org_allowed_ids = allowed_org_access_full_list($this); // doesnt have acl 'training_organizer_option_all'
#if ($org_allowed_ids) $where[] = "partner.organizer_option_id in ($org_allowed_ids)";
// restricted access?? only show organizers that belong to this site if its a multi org site
$site_orgs = allowed_organizer_in_this_site($this);
// for sites to host multiple training organizers on one domain
if ($site_orgs) {
$where[] = "partner.organizer_option_id in ({$site_orgs})";
}
// criteria
if ($criteria['partner_id']) {
$where[] = 'partner.id = ' . $criteria['partner_id'];
}
if ($criteria['last_selected_rgn']) {
$where[] = 'province_name is not null';
}
// bugfix - location subquery is not working like a inner join or where, not sure why
if ($criteria['facilityInput']) {
$where[] = 'facility.id = ' . $criteria['facilityInput'];
}
if ($criteria['facility_type_option_id']) {
$where[] = 'facility.type_option_id = ' . $criteria['facility_type_option_id'];
}
if ($criteria['employee_qualification_option_id']) {
$where[] = 'employee_qualification_option_id = ' . $criteria['employee_qualification_option_id'];
}
if ($criteria['employee_category_option_id']) {
$where[] = 'employee_category_option_id = ' . $criteria['employee_category_option_id'];
}
if ($criteria['hours_min']) {
$where[] = 'e.funded_hours_per_week >=' . $criteria['hours_min'];
}
if ($criteria['hours_max']) {
$where[] = 'e.funded_hours_per_week <=' . $criteria['hours_min'];
}
if ($criteria['cost_min']) {
$where[] = 'e.annual_cost_to_compare >=' . $criteria['cost_min'];
}
if ($criteria['cost_max']) {
$where[] = 'e.annual_cost_to_compare <=' . $criteria['cost_max'];
}
if ($criteria['employee_role_option_id']) {
$where[] = 'funder_end_date >= \'' . $this->_date_to_sql($criteria['start_date']) . ' 00:00:00\'';
}
if ($criteria['partner_importance_option_id']) {
$where[] = 'partner_importance_option_id = ' . $criteria['partner_importance_option_id'];
}
if ($criteria['start_date']) {
$where[] = 'funder_end_date >= \'' . $this->_date_to_sql($criteria['start_date']) . ' 00:00:00\'';
}
if ($criteria['end_date']) {
$where[] = 'funder_end_date <= \'' . $this->_date_to_sql($criteria['end_date']) . ' 23:59:59\'';
}
if ($criteria['employee_transition_option_id']) {
$where[] = 'employee_transition_option_id = ' . $criteria['employee_transition_option_id'];
}
if ($criteria['transition_confirmed']) {
$where[] = 'transition_confirmed = 1';
}
if (count($where)) {
$sql .= ' WHERE ' . implode(' AND ', $where);
}
$sql .= ' GROUP BY partner.id ';
$db = $this->dbfunc();
$rowArray = $db->fetchAll($sql);
$this->viewAssignEscaped('results', $rowArray);
$this->view->assign('count', count($rowArray));
if ($criteria['outputType']) {
$this->sendData($this->reportHeaders(false, $rowArray));
}
}
// assign form drop downs
$this->view->assign('status', $status);
$this->view->assign('criteria', $criteria);
$this->view->assign('pageTitle', t('Reports'));
$this->viewAssignEscaped('locations', Location::getAll());
$this->view->assign('partners', DropDown::generateHtml('partner', 'partner', $criteria['partner_id'], false, $this->view->viewonly, false));
//table, col, selected_value
$this->view->assign('subpartners', DropDown::generateHtml('partner', 'partner', $criteria['subpartner_id'], false, $this->view->viewonly, false, true, array('name' => 'subpartner_id'), true));
$this->view->assign('importance', DropDown::generateHtml('partner_importance_option', 'importance_phrase', $criteria['partner_importance_option_id'], false, $this->view->viewonly, false));
$this->view->assign('transitions', DropDown::generateHtml('employee_transition_option', 'transition_phrase', $criteria['employee_transition_option_id'], false, $this->view->viewonly, false));
$this->view->assign('incomingPartners', DropDown::generateHtml('partner', 'partner', $criteria['incoming_partner'], false, $this->view->viewonly, false, true, array('name' => 'incoming_partner'), true));
$helper = new Helper();
$this->viewAssignEscaped('facilities', $helper->getFacilities());
$this->view->assign('facilitytypes', DropDown::generateHtml('facility_type_option', 'facility_type_phrase', $criteria['facility_type_option_id'], false, $this->view->viewonly, false));
$this->view->assign('cadres', DropDown::qualificationsDropDown('employee_qualification_option_id', $criteria['employee_qualification_option_id']));
$this->view->assign('categories', DropDown::generateHtml('employee_category_option', 'category_phrase', $criteria['employee_category_option_id'], false, $this->view->viewonly, false));
$this->view->assign('roles', DropDown::generateHtml('employee_role_option', 'role_phrase', $criteria['employee_role_option_id'], false, $this->view->viewonly, false));
$this->view->assign('transitions', DropDown::generateHtml('employee_transition_option', 'transition_phrase', $criteria['employee_transition_option_id'], false, $this->view->viewonly, false));
}
