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