public function addAction()
{
$person_id = $this->_getParam('id');
$request = $this->getRequest();
if ($request->isPost()) {
//validate
$status = ValidationContainer::instance();
$status->checkRequired($this, 'title', $this->tr('Title'));
$training_start_date = @$this->getSanParam('start-year') . '-' . @$this->getSanParam('start-month') . '-' . @$this->getSanParam('start-day');
if ($training_start_date !== '--' and $training_start_date !== '0000-00-00') {
$status->isValidDate($this, 'start-day', t('Training') . ' ' . t('start'), $training_start_date);
}
if ($status->hasError()) {
$status->setStatusMessage(t('The person could not be saved.'));
} else {
$ecourseObj = new ExternalCourse();
$ecourseRow = $ecourseObj->createRow();
$ecourseRow->person_id = $person_id;
$ecourseRow->title = $this->getSanParam('title');
$ecourseRow->training_funder = $this->getSanParam('training_funder');
$ecourseRow->training_location = $this->getSanParam('training_location');
$ecourseRow->training_start_date = $training_start_date;
$ecourseRow->training_length_value = $this->getSanParam('training_length_value');
if ($id = $ecourseRow->save()) {
$status->setStatusMessage('The new course was created.');
$this->_redirect('person/edit/id/' . $person_id);
} else {
$status->setStatusMessage(t('The external course could not be saved.'));
}
}
}
$person = new Person();
$personRow = $person->fetchRow('id = ' . $person_id);
$this->view->assign('person', $personRow->toArray());
}
public function preDispatch()
{
$return = parent::preDispatch();
if (!$this->isLoggedIn()) {
$this->doNoAccessError();
}
if (!$this->hasACL('edit_country_options')) {
$this->doNoAccessError();
}
$this->status = ValidationContainer::instance();
return $return;
}
/**
* Putting this here since we can't get the Zend function to work correctly
* $path is from the base path beginning with the action, such as 'user/login'
*/
protected function _redirect($url, array $options = array())
{
$msg = ValidationContainer::instance()->status;
if ($msg) {
$_SESSION['status'] = $msg;
}
if (strstr($url, 'http://') !== false) {
header('Location: ' . $url);
} else {
header('Location: ' . Settings::$COUNTRY_BASE_URL . '/' . $url);
}
exit;
}
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 deleteAction()
{
if (!$this->hasACL('edit_course')) {
$this->doNoAccessError();
}
$status = ValidationContainer::instance();
$id = $this->getSanParam('id');
if ($id) {
$tp = new TrainingPartner();
$rows = $tp->find($id);
$row = $rows->current();
if ($row) {
$tp->delete('id = ' . $row->id);
}
$status->setStatusMessage(t('That partner was deleted.'));
} else {
if (!$id) {
$status->setStatusMessage(t('That partner could not be found.'));
} else {
$status->setStatusMessage(t('That partner is in use and could not be deleted.'));
}
}
//validate
$this->view->assign('status', $status);
}
public function __construct()
{
self::$instance = $this;
}
public function loginAction()
{
require_once 'Zend/Auth/Adapter/DbTable.php';
$request = $this->getRequest();
$validateOnly = $request->isXmlHttpRequest();
$userObj = new User();
$userRow = $userObj->createRow();
if ($validateOnly) {
$this->setNoRenderer();
}
$status = ValidationContainer::instance();
if ($request->isPost()) {
// if a user's already logged in, send them to their account home page
$auth = Zend_Auth::getInstance();
if ($auth->hasIdentity()) {
# $this->_redirect ( 'select/select' );
}
$request = $this->getRequest();
// determine the page the user was originally trying to request
$redirect = $this->_getParam('redirect');
//if (strlen($redirect) == 0)
// $redirect = $request->getServer('REQUEST_URI');
if (strlen($redirect) == 0) {
if ($this->hasACL('pre_service')) {
# $redirect = 'select/select';
}
}
// initialize errors
$status = ValidationContainer::instance();
// process login if request method is post
if ($request->isPost()) {
// fetch login details from form and validate them
$username = $this->getSanParam('username');
$password = $this->_getParam('password');
if (!$status->checkRequired($this, 'username', t('Login')) or !$this->_getParam('send_email') and !$status->checkRequired($this, 'password', t('Password'))) {
$status->setStatusMessage(t('The system could not log you in.'));
}
if (!$status->hasError()) {
// setup the authentication adapter
$db = Zend_Db_Table_Abstract::getDefaultAdapter();
$adapter = new Zend_Auth_Adapter_DbTable($db, 'user', 'username', 'password', 'md5(?)');
$adapter->setIdentity($username);
$adapter->setCredential($password);
// try and authenticate the user
$result = $auth->authenticate($adapter);
if ($result->isValid()) {
$user = new User();
$userRow = $user->find($adapter->getResultRowObject()->id)->current();
if ($user->hasPS($userRow->id)) {
$redirect = $redirect ? $redirect : "select/select";
}
if ($userRow->is_blocked) {
$status->setStatusMessage(t('That user account has been disabled.'));
$auth->clearIdentity();
} else {
// create identity data and write it to session
$identity = $user->createAuthIdentity($userRow);
$auth->getStorage()->write($identity);
// record login attempt
$user->recordLogin($userRow);
// send user to page they originally request
$this->_redirect($redirect);
}
} else {
$auth->clearIdentity();
switch ($result->getCode()) {
case Zend_Auth_Result::FAILURE_IDENTITY_NOT_FOUND:
$status->setStatusMessage(t('That username or password is invalid.'));
break;
case Zend_Auth_Result::FAILURE_CREDENTIAL_INVALID:
$status->setStatusMessage(t('That username or password is invalid.'));
break;
default:
throw new exception('login failure');
break;
}
}
}
}
}
if ($validateOnly) {
$this->sendData($status);
} else {
$this->view->assign('status', $status);
}
}
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 // - removed 5/1/13, they dont want this, its used by site-rollup (datashare), and user-restrict by org.
#$org_allowed_ids = allowed_org_access_full_list($this); // doesnt have acl 'training_organizer_option_all'
#$site_orgs = allowed_organizer_in_this_site($this); // for sites to host multiple training organizers on one domain
#$siteOrgsClause = $site_orgs ? " AND partner.organizer_option_id IN ($site_orgs)" : "";
#if ($org_allowed_ids && $this->view->mode != 'add') {
# $validID = $db->fetchCol("SELECT partner.id FROM partner WHERE partner.id = $id AND partner.organizer_option_id in ($org_allowed_ids) $siteOrgsClause");
# if(empty($validID))
# $this->doNoAccessError ();
#
#}
if ($this->getRequest()->isPost()) {
//validate then save
$status->checkRequired($this, 'partner', t('Partner'));
if ($this->setting('display_partner_type')) {
$status->checkRequired($this, 'partner_type_option_id', t('Type of Partner'));
}
$status->checkRequired($this, 'address1', t('Address 1'));
$status->checkRequired($this, 'address2', t('Address 2'));
$status->checkRequired($this, 'province_id', t('Region A (Province)'));
$status->checkRequired($this, 'phone', t('Phone'));
$status->checkRequired($this, 'fax', t('Fax'));
if ($this->setting('display_employee_funder')) {
$status->checkRequired($this, 'partner_funder_option_id[]', t('Funder'));
}
#$status->checkRequired ( $this, 'funding_end_date[]', t ( 'Funding End Date' ) );
#if ($this->setting('display_employee_intended_transition'))
# $status->checkRequired ( $this, 'employee_transition_option_id', t ( 'Intended Transition' ) );
if ($this->setting('display_employee_agreement_end_date')) {
$status->checkRequired($this, 'agreement_end_date', t('Agreement End Date'));
}
if ($this->setting('display_employee_importance')) {
$status->checkRequired($this, 'partner_importance_option_id', t('Importance'));
}
#$status->checkRequired ( $this, 'comments', t ( 'Partner Comments' ) );
#$status->checkRequired ( $this, 'subpartner_id[]', t ( 'Sub Partner' ) );
$params['funding_end_date'] = $this->_array_me($params['funding_end_date']);
foreach ($params['funding_end_date'] as $i => $value) {
$params['funding_end_date'][$i] = $this->_date_to_sql($value);
}
$params['transition_confirmed'] = $params['transition_confirmed'] == 'on' ? 1 : 0;
$params['agreement_end_date'] = $this->_date_to_sql($params['agreement_end_date']);
$params['subpartner_id'] = $this->_array_me($params['subpartner_id']);
foreach ($params['subpartner_id'] as $i => $value) {
// strip empty values (it breaks MultiOptionList apparently)
if (empty($value)) {
unset($params['subpartner_id'][$i]);
}
}
//location save stuff
$params['location_id'] = regionFiltersGetLastID(null, $params);
// formprefix, criteria
if ($params['city']) {
$params['location_id'] = Location::insertIfNotFound($params['city'], $params['location_id'], $this->setting('num_location_tiers'));
}
if (!$status->hasError()) {
$id = $this->_findOrCreateSaveGeneric('partner', $params);
if (!$id) {
$status->setStatusMessage(t('That partner could not be saved.'));
} else {
MultiOptionList::updateOptions('partner_to_funder', 'partner_funder_option', 'partner_id', $id, 'partner_funder_option_id', $params['partner_funder_option_id'], 'funder_end_date', $params['funding_end_date']);
$db->query("DELETE FROM partner_to_subpartner WHERE partner_id = {$id}");
// updateOptions is not clearing the old options, I dont know why... todo
MultiOptionList::updateOptions('partner_to_subpartner', 'partner', 'partner_id', $id, 'subpartner_id', $params['subpartner_id']);
$status->setStatusMessage(t('The partner was saved.'));
$this->_redirect("partner/edit/id/{$id}");
}
}
}
if ($id) {
// read data from db
#// 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'
#$orgWhere = ($org_allowed_ids) ? " AND 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
#$allowedWhereClause .= $site_orgs ? " AND partner.organizer_option_id in ($site_orgs) " : "";
// continue reading data
$sql = 'SELECT * FROM partner WHERE id = ' . $id . space . $orgWhere;
$row = $db->fetchRow($sql);
if (!$row) {
$status->setStatusMessage(t('Error finding that record in the database.'));
} else {
$params = $row;
// reassign form data
$region_ids = Location::getCityInfo($params['location_id'], $this->setting('num_location_tiers'));
$params['city'] = $region_ids[0];
$region_ids = Location::regionsToHash($region_ids);
$params = array_merge($params, $region_ids);
//get linked table data from option tables
$sql = "SELECT partner_funder_option_id,funder_end_date FROM partner_to_funder WHERE partner_id = {$id}";
$params['funder'] = $db->fetchAll($sql);
$sql = "SELECT subpartner_id FROM partner_to_subpartner WHERE partner_id = {$id}";
$params['subpartners'] = $db->fetchCol($sql);
}
}
// make sure form data is valid for display
if (empty($params['funder'])) {
$params['funder'] = array(array());
}
if (empty($params['subpartners'])) {
$params['subpartners'] = array(' ');
}
// assign form drop downs
$this->view->assign('status', $status);
$this->view->assign('pageTitle', $this->view->mode == 'add' ? t('Add Partner') : t('View Partner'));
$this->viewAssignEscaped('partner', $params);
$this->viewAssignEscaped('locations', Location::getAll());
$this->view->assign('partners', DropDown::generateHtml('partner', 'partner', $params['partner_type_option_id'], false, $this->view->viewonly, false));
//table, col, selected_value
$this->view->assign('subpartners', DropDown::generateHtml('partner', 'partner', 0, false, $this->view->viewonly, false, true, array('name' => 'subpartner_id[]'), true));
$this->view->assign('types', DropDown::generateHtml('partner_type_option', 'type_phrase', $params['partner_type_option_id'], false, $this->view->viewonly, false));
$this->view->assign('importance', DropDown::generateHtml('partner_importance_option', 'importance_phrase', $params['partner_importance_option_id'], false, $this->view->viewonly, false));
$this->view->assign('transitions', DropDown::generateHtml('employee_transition_option', 'transition_phrase', $params['employee_transition_option_id'], false, $this->view->viewonly, false));
$this->view->assign('incomingPartners', DropDown::generateHtml('partner', 'partner', $params['incoming_partner'], false, $this->view->viewonly, false, true, array('name' => 'incoming_partner'), true));
$this->view->assign('organizers', DropDown::generateHtml('training_organizer_option', 'training_organizer_phrase', $params['organizer_option_id'], false, $this->view->viewonly, false, true, array('name' => 'organizer_option_id'), true));
$helper = new Helper();
$this->viewAssignEscaped('facilities', $helper->getFacilities());
}
public function execute()
{
$params = $this->_getAllParams();
if (isset($params['merge']) && $this->allowMerge) {
$this->merge();
return;
}
if (isset($params['default']) && $this->allowDefault) {
$this->setDefault();
}
if (isset($params['redirect']) and $params['redirect']) {
// editTable is part of a "wizard" (redirect to the next step)
header("Location: {$params['redirect']}");
exit;
} elseif (isset($params['saveonly'])) {
$status = ValidationContainer::instance();
$status->setStatusMessage('Your settings have been updated.');
}
require_once 'models/table/EditTable.php';
$editTable = new EditTable(array('name' => $this->table));
$request = $this->controller->getRequest();
$validateOnly = $request->isXmlHttpRequest();
// Delete, insert, or update?
if ($validateOnly) {
//$id = $params['id'];
$id = isset($_POST['id']) && is_numeric($_POST['id']) ? $_POST['id'] : null;
// Get field to update
foreach ($this->fields as $key => $devnull) {
if (isset($params[$key])) {
$fieldEdit = $key;
$fieldValue = $params[$key];
break;
}
}
if ($id == 0 && isset($params['undelete'])) {
// undelete record
try {
$row = $editTable->undelete($fieldEdit, $fieldValue);
$sendRay['insert'] = $row->id;
$sendRay['undelete'] = $row->{$fieldEdit};
$this->sendData($sendRay);
} catch (Zend_Exception $e) {
$this->sendData(array("insert" => 0, 'error' => $e->getMessage()));
}
} elseif ($id == 0) {
// user added new record
try {
if (!$this->insertExtra) {
$insert = $editTable->insertUnique($fieldEdit, $fieldValue);
} else {
$data = array($fieldEdit => $fieldValue);
$insert = $editTable->insert(array_merge($data, $this->insertExtra));
}
$sendRay['insert'] = "{$insert}";
if ($insert == -1) {
$sendRay['error'] = 'A record already exists with this value.';
}
if ($insert == -2) {
$sendRay['error'] = '"%s" already exists, but was deleted. Would you like to undelete?';
}
$this->sendData($sendRay);
} catch (Zend_Exception $e) {
$this->sendData(array("insert" => 0, 'error' => $e->getMessage()));
}
} elseif ($id > 0) {
// update or delete
if (isset($params['delete'])) {
try {
$delete = $editTable->delete("id={$id}", true);
//force the delete, changed 06/16/08 Todd W
$this->sendData(array("delete" => $delete));
} catch (Zend_Exception $e) {
$this->sendData(array("delete" => 0, 'error' => $e->getMessage()));
}
} elseif (isset($fieldEdit)) {
// update
try {
$update = $editTable->update(array($fieldEdit => $fieldValue), "id={$id}");
$this->sendData(array("update" => $id));
} catch (Zend_Exception $e) {
if (strpos($e->getMessage(), 'Duplicate entry') !== false) {
$this->sendData(array("update" => 0, 'error' => t('A record already exists with this value.')));
} else {
$this->sendData(array("update" => 0, 'error' => $e->getMessage()));
}
}
}
}
} else {
// view
$selectFields = array_keys($this->fields);
if ($this->allowDefault) {
$selectFields[] = 'is_default';
}
require_once 'views/helpers/EditTableHelper.php';
$rowRay = $editTable->getRowsSingle($this->table, $selectFields, $this->where);
foreach ($rowRay as $key => $row) {
foreach ($selectFields as $field) {
if ($field != 'id') {
$rowRay[$key][$field] = htmlspecialchars($row[$field]);
}
}
}
// Modify rows
if ($this->rowHook) {
$func_name = $this->rowHook;
eval('$rowRay = ' . $func_name . "(unserialize('" . serialize($rowRay) . "'));");
}
$noDelete = array();
// look up dependencies
if (!empty($this->dependencies)) {
foreach ($this->dependencies as $colDependent => $tableDependent) {
if (is_numeric($colDependent)) {
$colDependent = $this->table . '_id';
}
if (is_array($tableDependent)) {
// in case multiple tables use the same field name
$colDependent = key($tableDependent);
$tableDependent = current($tableDependent);
}
$ray = $editTable->getDependencies($this->table, $tableDependent, $colDependent);
$noDelete += array_merge($noDelete, $ray);
}
$noDelete = array_unique($noDelete);
}
// disable delete on all rows
if ($this->noDelete) {
foreach ($rowRay as $key => $row) {
$noDelete[$row['id']] = $row['id'];
}
}
// merge checkbox
if ($this->allowMerge) {
foreach ($rowRay as $key => $row) {
$rowRay[$key]['merge'] = '
<input type="checkbox" name="merge[]" value="' . $row['id'] . '" id="merge' . $row['id'] . '">';
}
$this->customColDef['merge'] = 'editor:false';
$this->fields['merge'] = 'Merge?';
}
// default radio
if ($this->allowDefault) {
foreach ($rowRay as $key => $row) {
$isChecked = $row['is_default'] ? ' checked="checked"' : '';
$rowRay[$key]['default'] = '
<input type="radio" name="default" value="' . $row['id'] . '" id="merge' . $row['id'] . '"' . $isChecked . '>';
}
$this->customColDef['default'] = 'editor:false';
$this->fields['default'] = 'Default?';
}
$html = '';
if ($this->allowMerge) {
$mergehtml = '
<input type="hidden" name="table_option" value="' . $this->table . '">
<input type="hidden" name="table_dependent" value="' . implode(',', $this->dependencies) . '">
<input type="submit" name="mergesubmit" value="Merge Selected" class="submitArrow">';
$html .= $mergehtml;
}
if ($html) {
$html .= '<div class="clear"></div><br>';
}
$html .= EditTableHelper::generateHtml($this->label, $rowRay, $this->fields, $this->customColDef, $noDelete, $this->noEdit);
// merge form
if ($this->allowMerge) {
$html .= $mergehtml;
}
$this->controller->view->assign($this->viewVar, $html);
}
}
public function deleteLocationAction()
{
if (!$this->hasACL('edit_course')) {
$this->doNoAccessError();
}
require_once 'models/table/TrainingLocation.php';
$status = ValidationContainer::instance();
$id = $this->getSanParam('id');
if ($id and !TrainingLocation::isReferenced($id)) {
$loc = new TrainingLocation();
$rows = $loc->find($id);
$row = $rows->current();
if ($row) {
$loc->delete('id = ' . $row->id);
}
$status->setStatusMessage(t('That location was deleted.'));
} else {
if (!$id) {
$status->setStatusMessage(t('That location could not be found.'));
} else {
$status->setStatusMessage(t('That location is in use and could not be deleted.'));
}
}
//validate
$this->view->assign('status', $status);
}
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 scoresImportAction()
{
require_once 'models/table/Person.php';
require_once 'models/table/PersonToTraining.php';
//labels
$id = $this->getSanParam('training');
$db = Zend_Db_Table_Abstract::getDefaultAdapter();
$status = ValidationContainer::instance();
$trainingObj = new Training();
$this->viewAssignEscaped('courseName', $trainingObj->getCourseName($id));
$this->view->assign('training_id', $id);
//CSV import -- post
if (@$_FILES['import']['tmp_name']) {
$filename = $_FILES['import']['tmp_name'];
if ($filename) {
// we need a table to compare names to
$table = new ITechTable(array('name' => 'score'));
$persons = new ITechTable(array('name' => 'person'));
$sql = 'select distinct person_to_training.id as pid,person.first_name,person.last_name from person_to_training
left join person on person.id = person_id
where person_to_training.training_id = ' . $id;
$ppl = $db->fetchAll($sql);
while ($row = $this->_csv_get_row($filename)) {
if (is_array($row)) {
if (isset($row[0]) && isset($row[4]) && !empty($row[0]) && !empty($row[4])) {
// find person
$row[0] = trim($row[0]);
$row[1] = trim($row[1]);
$pid = null;
foreach ($ppl as $v) {
if ($v['first_name'] == $row[0] && $v['last_name'] == $row[1]) {
$pid = $v['pid'];
break;
}
}
if ($pid) {
$new_row = $table->createRow();
$new_row->person_to_training_id = $pid;
$new_row->training_date = $row[2];
$new_row->score_label = $row[3];
$new_row->score_value = $row[4];
$new_row->save();
} else {
// err
if (!isset($notfound)) {
$notfound = array();
}
if ($row[0] != t('First Name')) {
$notfound[] = $row[0] . ' ' . $row[1] . '<br>';
}
}
}
}
}
}
$_POST['redirect'] = null;
if ($notfound) {
$status->setStatusMessage(t('The following users could not be found while importing, perhaps they were not adding to the training:<br>'));
foreach ($notfound as $v) {
$status->setStatusMessage($v);
}
}
// done
}
// score view (edit table)
require_once 'views/helpers/EditTableHelper.php';
$label = 'Score';
$fields = array('name' => t('Name'), 'score_label' => t('Label'), 'score_value' => t('Score'));
$rowRay = $db->fetchAll("select score.*,CONCAT(person.first_name, CONCAT(' ', person.last_name)) as name from person_to_training\r\n\t\t\t\t\t\tinner join score on score.person_to_training_id = person_to_training.id\r\n\t\t\t\t\t\tleft join person on person.id = person_id\r\n\t\t\t\t\t\twhere person_to_training.training_id = {$id}\r\n\t\t\t\t\t\t");
$this->view->assign('editTable', EditTableHelper::generateHtml($label, $rowRay, $fields, array(), array(), true));
}
public function employeeSettingsAction()
{
require_once 'models/table/System.php';
$sysTable = new System();
// For "Labels"
// same logic as other Settings pages - except the employee_header setting below
require_once 'models/table/Translation.php';
$labelNames = array('label_partner' => 'Partner', 'label_sub_partner' => 'Sub Partner', 'label_type' => 'Type of Partner', 'label_funder' => 'Funder', 'label_full_time' => 'Full Time', 'label_base' => 'Employee Based at', 'label_funded_hours_per_week' => 'Funded hours per week', 'label_cadre' => 'Staff Cadre', 'label_staff_category' => 'Staff Category', 'label_annual_cost' => 'Annual Cost', 'label_primary_role' => 'Primary Role', 'label_importance' => 'Importance', 'label_intended_transition' => 'Intended Transition', 'label_incoming_partner' => 'Incoming partner', 'label_relationship' => 'Relationship', 'label_referral_mechanism' => 'Referral Mechanism', 'label_chw_supervisor' => 'CHW Supervisor', 'label_trainings_provided' => 'Trainings provided', 'label_courses_completed' => 'Courses Completed', 'label_other_id' => 'Other ID', 'label_disability' => 'Disability', 'label_disability_comments' => 'Disability Comments', 'label_nationality' => 'Employee Nationality', 'label_race' => 'Race', 'label_registration_number' => 'Registration Number', 'label_salary' => 'Salary', 'label_benefits' => 'Benefits', 'label_additional_expenses' => 'Additional Expenses', 'label_stipend' => 'Stipend');
$checkboxFields = array('check_partner' => 'display_employee_partner', 'check_sub_partner' => 'display_employee_sub_partner', 'check_type' => 'display_partner_type', 'check_funder' => 'display_employee_funder', 'check_full_time' => 'display_employee_full_time', 'check_base' => 'display_employee_base', 'check_site_type' => 'display_employee_site_type', 'check_funded_hours_per_week' => 'display_employee_funded_hours_per_week', 'check_staff_category' => 'display_employee_staff_category', 'check_annual_cost' => 'display_employee_annual_cost', 'check_primary_role' => 'display_employee_primary_role', 'check_importance' => 'display_employee_importance', 'check_contract_end_date' => 'display_employee_contract_end_date', 'check_agreement_end_date' => 'display_employee_agreement_end_date', 'check_intended_transition' => 'display_employee_intended_transition', 'check_transition_confirmed' => 'display_employee_transition_confirmed', 'check_transition_complete' => 'display_employee_complete_transition', 'check_transition_complete_date' => 'display_employee_actual_transition_date', 'check_incoming_partner' => 'display_employee_incoming_partner', 'check_relationship' => 'display_employee_relationship', 'check_referral_mechanism' => 'display_employee_referral_mechanism', 'check_chw_supervisor' => 'display_employee_chw_supervisor', 'check_trainings_provided' => 'display_employee_trainings_provided', 'check_courses_completed' => 'display_employee_courses_completed', 'check_site_name' => 'display_employee_site_name', 'check_employee_header' => 'display_employee_employee_header', 'check_other_id' => 'display_employee_other_id', 'check_disability' => 'display_employee_disability', 'check_nationality' => 'display_employee_nationality', 'check_race' => 'display_employee_race', 'check_registration_number' => 'display_employee_registration_number', 'check_salary' => 'display_employee_salary', 'check_benefits' => 'display_employee_benefits', 'check_additional_expenses' => 'display_employee_additional_expenses', 'check_stipend' => 'display_employee_stipend');
if ($this->getRequest()->isPost()) {
// Update db
$updateData = array();
// update translation labels
$tranTable = new Translation();
foreach ($labelNames as $input_key => $db_key) {
if ($this->_getParam($input_key)) {
try {
$tranTable->update(array('phrase' => $this->_getParam($input_key)), "key_phrase = '{$db_key}'");
$this->viewAssignEscaped($input_key, $this->_getParam($input_key));
} catch (Zend_Exception $e) {
error_log($e);
}
}
}
// update _system (checkboxes)
foreach ($checkboxFields as $input_key => $db_field) {
$value = $this->_getParam($input_key) == NULL ? 0 : 1;
$updateData[$db_field] = $value;
$this->view->assign($input_key, $value);
}
$updateData['employee_header'] = $this->_getParam('employee_header');
$this->view->assign('employee_header', $this->_getParam('employee_header') ? $this->_getParam('employee_header') : '');
$sysTable->update($updateData, '');
} else {
// view
// checkboxes
$sysRows = $sysTable->fetchRow($sysTable->select()->limit(1));
$this->view->assign('employee_header', isset($sysRows->employee_header) ? $sysRows->employee_header : '');
foreach ($checkboxFields as $input_key => $field_key) {
if (isset($sysRows->{$field_key})) {
$this->view->assign($input_key, $sysRows->{$field_key});
}
}
// labels
$t = Translation::getAll();
foreach ($labelNames as $input_key => $db_key) {
$this->viewAssignEscaped($input_key, $t[$db_key]);
}
}
// redirect to next page
if ($this->_getParam('redirect')) {
header("Location: " . $this->_getParam('redirect'));
exit;
} else {
if ($this->_getParam('saveonly')) {
$status = ValidationContainer::instance();
$status->setStatusMessage(t('Your settings have been updated.'));
}
}
}
/**
* Import a person
*/
public function importAction()
{
$this->view->assign('pageTitle', t('Import a person'));
require_once 'models/table/TrainingToTrainer.php';
// template redirect
if ($this->getSanParam('download')) {
return $this->importTrainingTemplateAction();
}
if (!$this->hasACL('import_person')) {
$this->doNoAccessError();
}
//CSV STUFF
$filename = $_FILES['upload']['tmp_name'];
if ($filename) {
$personObj = new Person();
$errs = array();
while ($row = $this->_csv_get_row($filename)) {
$values = array();
if (!is_array($row)) {
continue;
}
// sanity?
if (!isset($cols)) {
// set headers (field names)
$cols = $row;
// first row is headers (field names)
continue;
}
$countValidFields = 0;
if (!empty($row)) {
// add
foreach ($row as $i => $v) {
// proccess each column
if (empty($v) && $v !== '0') {
continue;
}
if ($v == 'n/a') {
// has to be able to process values from a data export
$v = NULL;
}
$countValidFields++;
$delimiter = strpos($v, ',');
// is this field a comma seperated list too (or array)?
if ($delimiter && $v[$delimiter - 1] != '\\') {
// handle arrays as field values(Export), and comma seperated values(import manual entry), and strings or int
$values[$cols[$i]] = explode(',', $this->sanitize($v));
} else {
$values[$cols[$i]] = $this->sanitize($v);
}
}
}
// done now all fields are named and in $values[my_field]
if ($countValidFields) {
//validate
if (isset($values['uuid'])) {
unset($values['uuid']);
}
if (isset($values['id'])) {
unset($values['id']);
}
if (isset($values['is_deleted'])) {
unset($values['is_deleted']);
}
if (isset($values['created_by'])) {
unset($values['created_by']);
}
if (isset($values['modified_by'])) {
unset($values['modified_by']);
}
if (isset($values['timestamp_created'])) {
unset($values['timestamp_created']);
}
if (isset($values['timestamp_updated'])) {
unset($values['timestamp_updated']);
}
if (!$this->hasACL('approve_trainings')) {
unset($values['approved']);
}
#if ( $values['sponsor_option_id'] ) {
# $sponsors = $this->_array_me($values['sponsor_option_id']); // could be an array, we dont want one
# $values['sponsor_option_id'] = $sponsors[0];
#}
//locations
$num_location_tiers = $this->setting('num_location_tiers');
$bSuccess = true;
$location_id = null;
if ($values['facility_name']) {
if (!$values['facility_id']) {
if (is_array($values['facility_name'])) {
$values['facility_id'] = $values['facility_name'][0];
} else {
if (is_numeric($values['facility_name'])) {
$values['facility_id'] = $values['facility_name'];
}
}
//else
//$errs[] = t ('Not able to set facility location for: ').$values['first_name'].space.$values['last_name'];
}
}
if (!$bSuccess) {
$errs[] = t('Error locating/creating region or city:') . ' ' . $row[$r] . ' ' . t('Facility') . ': ' . $values['facility_name'];
continue;
// couldnt save location
}
//dupecheck
$dupe = new Person();
$select = $dupe->select()->where('facility_id = "' . $values['facility_id'] . '" and first_name = "' . $values['first_name'] . '" and last_name = "' . $values['last_name'] . '"');
if ($dupe->fetchRow($select)) {
$errs[] = t('A person with this name already exists in the database, the user was not added.') . space . t('Name') . ': ' . $values['first_name'] . space . $values['last_name'];
$bSuccess = false;
}
if (!$bSuccess) {
continue;
}
//field mapping (Export vs import)
if (isset($values["qualification_phrase"])) {
$values["primary_qualification_option_id"] = $values["qualification_phrase"];
}
if (isset($values["primary_qualification_phrase"])) {
$values["primary_qualification_option_id"] = $values["primary_qualification_phrase"];
}
if (isset($values["primary_responsibility_phrase"])) {
$values["primary_responsibility_option_id"] = $values["primary_responsibility_phrase"];
}
if (isset($values["secondary_responsibility_phrase"])) {
$values["secondary_responsibility_option_id"] = $values["secondary_responsibility_phrase"];
}
if (isset($values["highest_edu_level_phrase"])) {
$values["highest_edu_level_option_id"] = $values["highest_edu_level_phrase"];
}
if (isset($values["attend_reason_phrase"])) {
$values["attend_reason_option_id"] = $values["attend_reason_phrase"];
}
if (isset($values["custom_1"])) {
$values["person_custom_1_option_id"] = $values["custom_1"];
}
if (isset($values["custom_2"])) {
$values["person_custom_2_option_id"] = $values["custom_2"];
}
//save
try {
//$values['title_option_id'] = $this->_importHelperFindOrCreate('person_title_option', 'title_phrase', $values['title_option_id']); //title_option_id multiAssign (insert via helper)
//$values['suffix_option_id'] = $this->_importHelperFindOrCreate('person_suffix_option', 'suffix_phrase', $values['suffix_option_id']);
$values['primary_qualification_option_id'] = $this->_importHelperFindOrCreate('person_qualification_option', 'qualification_phrase', $values['primary_qualification_option_id']);
$values['primary_responsibility_option_id'] = $this->_importHelperFindOrCreate('person_responsibility_option', 'responsibility_phrase', $values['primary_responsibility_option_id']);
$values['secondary_responsibility_option_id'] = $this->_importHelperFindOrCreate('person_secondary_responsibility_option', 'responsibility_phrase', $values['secondary_responsibility_option_id']);
$values['attend_reason_option_id'] = $this->_importHelperFindOrCreate('person_attend_reason_option', 'attend_reason_phrase', $values['attend_reason_option_id']);
$values['person_custom_1_option_id'] = $this->_importHelperFindOrCreate('person_custom_1_option', 'custom1_phrase', $values['person_custom_1_option_id']);
$values['person_custom_2_option_id'] = $this->_importHelperFindOrCreate('person_custom_2_option', 'custom2_phrase', $values['person_custom_2_option_id']);
$values['highest_level_option_id'] = $this->_importHelperFindOrCreate('person_education_level_option', 'education_level_phrase', $values['highest_level_option_id']);
//$values['courses'] = $this->_importHelperFindOrCreate('facility_type_option', 'facility_type_phrase', null, $values['courses']);
$personrow = $personObj->createRow();
$personrow = ITechController::fillFromArray($personrow, $values);
$row_id = $personrow->save();
} catch (Exception $e) {
$errored = 1;
$errs[] = nl2br($e->getMessage()) . ' ' . t('ERROR: The person could not be saved.');
}
if (!$row_id) {
$errored = 1;
$errs[] = t('That person could not be saved.') . space . t("Name") . ": " . $values['first_name'] . space . $values['last_name'];
}
//sucess - done
}
//loop
}
// done processing rows
$_POST['redirect'] = null;
if (empty($errored) && empty($errs)) {
$stat = t('Your changes have been saved.');
} else {
$stat = t('Error importing data. Some data rows have not been imported.');
}
foreach ($errs as $errmsg) {
$stat .= '<br>' . 'Error: ' . htmlspecialchars($errmsg, ENT_QUOTES);
}
$status = ValidationContainer::instance();
$status->setStatusMessage($stat);
$this->view->assign('status', $status);
}
// done with import
}
public function newEval()
{
$criteria = $this->getAllParams();
$db = $this->dbfunc();
$status = ValidationContainer::instance();
//find the first date in the database
$db = Zend_Db_Table_Abstract::getDefaultAdapter();
$sql = "SELECT MIN(timestamp_created) as \"start\" FROM evaluation_response WHERE is_deleted = 0";
$rowArray = $db->fetchAll($sql);
$start_default = $rowArray[0]['start'];
$parts = explode(' ', $start_default);
$parts = explode('-', $parts[0]);
$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(timestamp_created) as \"start\" FROM evaluation_response ";
$rowArray = $db->fetchAll($sql);
$end_default = $rowArray[0]['start'];
$parts = explode(' ', $start_default);
$parts = explode('-', $parts[0]);
$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 ($criteria['evaluation_id'] || $criteria['training_id']) {
$sql = " SELECT\r\n\t\t\t\t\t\ttl.training_location_name,\r\n\t\t\t\t\t\tevaluation.title,\r\n\t\t\t\t\t\teqr.id,\r\n\t\t\t\t\t\tevaluation_response_id,\r\n\t\t\t\t\t\tevaluation_question_id," . "evaluation_to_training_id,\r\n\t\t\t\t\t\ttrainer_person_id,\r\n\t\t\t\t\t\tevaluation.id as evaluation_id,\r\n\t\t\t\t\t\ttraining_id,\r\n\t\t\t\t\t\ttraining_title_phrase,\r\n\t\t\t\t\t\ttitle,\r\n\t\t\t\t\t\tquestion_text,\r\n\t\t\t\t\t\tquestion_type,\r\n\t\t\t\t\t\tweight,\r\n\t\t\t\t\t\tCONCAT(p.first_name, CONCAT(' ', p.last_name)) as person_full_name,\r\n\t\t\t\t\t\tCONCAT(t.first_name, CONCAT(' ', t.last_name)) as trainer_full_name,\r\n\t\t\t\t\t\tGROUP_CONCAT(IFNULL(value_text, value_int)) as score_array,\r\n\t\t\t\t\t\tGROUP_CONCAT(value_int) as scores_int\r\n\t\t\t\t\t FROM\r\n\t\t\t\t\t\tevaluation_question_response eqr\r\n\t\t\t\t\t\tLEFT JOIN evaluation_response er ON eqr.evaluation_response_id = er.id\r\n\t\t\t\t\t\tINNER JOIN evaluation_to_training ett ON ett.id = er.evaluation_to_training_id AND ett.training_id IS NOT NULL\r\n\t\t\t\t\t\tINNER JOIN training ON training.id = ett.training_id AND training.is_deleted = 0\r\n\t\t\t\t\t\tLEFT JOIN training_location tl ON tl.id = training.training_location_id\r\n\t\t\t\t\t\tLEFT JOIN training_title_option tto ON training.training_title_option_id = tto.id\r\n\t\t\t\t\t\tLEFT JOIN evaluation ON evaluation.id = ett.evaluation_id\r\n\t\t\t\t\t\tLEFT JOIN evaluation_question eq ON eq.id = eqr.evaluation_question_id\r\n\t\t\t\t\t\t" . "\r\n\t\t\t\t\t\tLEFT JOIN person p ON p.id = er.person_id\r\n\t\t\t\t\t\tLEFT JOIN person t ON t.id = er.trainer_person_id\r\n\t\t\t\t\t\t";
$where = array();
$where[] = 'evaluation.is_deleted = 0 AND er.is_deleted = 0 AND eq.is_deleted = 0 AND eqr.is_deleted = 0';
if ($criteria['evaluation_id']) {
$where[] = 'ett.evaluation_id = ' . $criteria['evaluation_id'];
}
if ($criteria['training_id']) {
$where[] = 'training_id = ' . $criteria['training_id'];
}
if ($criteria['start-year'] && !$training_id) {
// bugfix: !training_id todo: these do not play well with evaluations by training_id probably because i just created the responses, and its saerching > today
$startDate = $criteria['start-year'] . '-' . $criteria['start-month'] . '-' . $criteria['start-day'];
$where[] .= ' er.timestamp_created >= \'' . $startDate . '\' ';
}
if ($criteria['end-year'] && !$training_id) {
$endDate = $criteria['end-year'] . '-' . $criteria['end-month'] . '-' . $criteria['end-day'];
$where[] = ' er.timestamp_created <= \'' . $endDate . ' 23:59\' ';
}
$sql .= ' WHERE ' . implode(' AND ', $where);
$sql .= " GROUP BY eq.id,evaluation.id,training_id,person_full_name, trainer_full_name";
$sql .= " ORDER BY ett.training_id, ett.evaluation_id, er.timestamp_created, weight";
$rows = $db->fetchAll($sql);
if ($rows) {
// pivot rows to columns, based on the # of times a participant has a linked evaluation
$maxVisits = 0;
foreach ($rows as $i => $row) {
// count # of visits, keep the max # visits
$rows[$i]['parsed_scores'] = explode(',', $row['score_array']);
// explode list of scores
$cnt = count($rows[$i]['parsed_scores']);
if ($cnt > $maxVisits) {
$maxVisits = $cnt;
}
}
foreach ($rows as $i => $row) {
$rows[$i]['question_number'] = $row['weight'] + 1;
for ($k = 0; $k < $maxVisits; $k++) {
// pivot rows to columns
$rows[$i]['response' . ($k + 1)] = isset($row['parsed_scores'][$k]) ? $row['parsed_scores'][$k] : '';
// do it here so we can export to excel
}
$avgsArray = explode(',', $row['scores_int']);
// averages, value_int column only
$rows[$i]['average'] = isset($avgsArray[0]) && trim($avgsArray[0]) !== '' ? number_format(array_sum($avgsArray) / count($avgsArray), 2) : '-';
// if it seems to not be empty we can do some calculations
// cleanup - in case of export
unset($rows[$i]['score_array']);
unset($rows[$i]['scores_int']);
unset($rows[$i]['parsed_scores']);
unset($rows[$i]['id']);
unset($rows[$i]['evaluation_response_id']);
unset($rows[$i]['evaluation_question_id']);
unset($rows[$i]['answer']);
unset($rows[$i]['evaluation_to_training_id']);
unset($rows[$i]['trainer_person_id']);
unset($rows[$i]['evaluation_id']);
unset($rows[$i]['weight']);
}
$this->viewAssignEscaped('numColumns', $maxVisits);
$this->viewAssignEscaped('results', $rows);
if ($this->_getParam('outputType')) {
$this->sendData($this->reportHeaders(false, $rows));
}
} else {
$status->setStatusMessage('Error running report. There might be no data.');
}
}
$this->viewAssignEscaped('pageTitle', t('Evaluation Report'));
$this->viewAssignEscaped('evaluations', OptionList::suggestionList('evaluation', 'title', false, false, false));
$this->view->assign('criteria', $criteria);
}
/**
* Import a training location
*/
public function importLocationAction()
{
$this->view->assign('pageTitle', t('Import a training location'));
require_once 'models/table/Location.php';
require_once 'models/table/TrainingLocation.php';
// template redirect
if ($this->getSanParam('download')) {
return $this->importLocationTemplateAction();
}
if (!$this->hasACL('import_training_location')) {
$this->doNoAccessError();
}
//CSV STUFF
$filename = $_FILES['upload']['tmp_name'];
if ($filename) {
$trainingLocationObj = new TrainingLocation();
$errs = array();
while ($row = $this->_csv_get_row($filename)) {
$values = array();
if (!is_array($row)) {
continue;
}
// sanity?
if (!isset($cols)) {
// set headers (field names)
$cols = $row;
// first row is headers (field names)
continue;
}
$countValidFields = 0;
if (!empty($row)) {
// add
foreach ($row as $i => $v) {
// proccess each column
if (empty($v) && $v !== '0') {
continue;
}
if ($v == 'n/a') {
// has to be able to process values from a data export
$v = NULL;
}
$countValidFields++;
$delimiter = strpos($v, ',');
// is this field a comma seperated list too (or array)?
if ($delimiter && $v[$delimiter - 1] != '\\') {
// handle arrays as field values(Export), and comma seperated values(import manual entry), and strings or int
$values[$cols[$i]] = explode(',', $this->sanitize($v));
} else {
$values[$cols[$i]] = $this->sanitize($v);
}
}
}
// done now all fields are named and in $values['my_field']
if ($countValidFields) {
//validate
if (isset($values['uuid'])) {
unset($values['uuid']);
}
if (isset($values['id'])) {
unset($values['id']);
}
if (isset($values['is_deleted'])) {
unset($values['is_deleted']);
}
if (isset($values['created_by'])) {
unset($values['created_by']);
}
if (isset($values['modified_by'])) {
unset($values['modified_by']);
}
if (isset($values['timestamp_created'])) {
unset($values['timestamp_created']);
}
if (isset($values['timestamp_updated'])) {
unset($values['timestamp_updated']);
}
//required
if (empty($values['training_location_name'])) {
$errs[] = t('Error adding training location, training location name cannot be empty.');
}
//locations
$num_location_tiers = $this->setting('num_location_tiers');
$bSuccess = true;
$location_id = null;
if ($values['location_id']) {
$location_id = $values['location_id'];
}
$tier = 1;
if (!$location_id) {
for ($i = 0; $i <= $num_location_tiers; $i++) {
// insert/find locations
$r = 1 + $i;
// first location field in csv row // could use this too: $values[t('Region A (Province)')]
if (empty($row[$r]) || $bSuccess == false) {
continue;
}
$location_id = Location::insertIfNotFound($row[$r], $location_id, $tier);
if (!$location_id) {
$bSuccess = false;
break;
}
$tier++;
}
}
if (!$bSuccess || !$location_id) {
$errs[] = t('Error locating/creating region or city:') . ' ' . $row[$r] . ' ' . t('Training Location') . ': ' . $values['training_location_name'];
continue;
// couldnt save location
}
$values['location_id'] = $location_id;
//dupecheck
$dupe = new TrainingLocation();
$select = $dupe->select()->where('location_id =' . $location_id . ' and training_location_name = "' . $values['training_location_name'] . '"');
if ($dupe->fetchRow($select)) {
$errs[] = t('The training location could not be saved. A training location with this name already exists in that location.') . ' ' . t('training location') . ': ' . $values['training_location_name'];
$bSuccess = false;
}
if (!$bSuccess) {
continue;
}
//save
try {
$tableObj = $trainingLocationObj->createRow();
$tableObj->training_location_name = $values['training_location_name'];
$tableObj->location_id = $location_id;
$row_id = $tableObj->save();
} catch (Exception $e) {
$errored = 1;
$errs[] = nl2br($e->getMessage()) . ' ' . t('ERROR: The training location could not be saved.');
}
if (!$row_id) {
$errored = 1;
}
//sucess - done
}
//loop
}
// done processing rows
$_POST['redirect'] = null;
$status = ValidationContainer::instance();
if (empty($errored) && empty($errs)) {
$stat = t('Your changes have been saved.');
} else {
$stat = t('Error importing data. Some data may have been imported and some may not have.');
}
foreach ($errs as $errmsg) {
$stat .= '<br>' . 'Error: ' . htmlspecialchars($errmsg, ENT_QUOTES);
}
$status->setStatusMessage($stat);
$this->view->assign('status', $status);
}
// done with import
}
