public function doMySearch()
{
$helper = new Helper();
require_once 'models/table/Person.php';
if (!$this->hasACL('view_people') and !$this->hasACL('edit_people')) {
$this->doNoAccessError();
}
$criteria = array();
list($criteria, $location_tier, $location_id) = $this->getLocationCriteriaValues($criteria);
$criteria['facility_name'] = $this->getSanParam('facility_name');
$criteria['facilityInput'] = $this->getSanParam('facilityInput');
$criteria['first_name'] = $this->getSanParam('first_name');
$criteria['last_name'] = $this->getSanParam('last_name');
$criteria['training_title_option_id'] = $this->getSanParam('training_title_option_id');
$criteria['persal'] = $this->getSanParam('persal');
$criteria['person_type'] = $this->getSanParam('person_type');
$criteria['is_complete'] = $this->getSanParam('is_complete');
if (!$criteria['person_type']) {
$criteria['person_type'] = 'is_participant';
}
// $criteria['type_id'] = $this->getSanParam('trainer_type_id');
$criteria['qualification_id'] = $this->getSanParam('qualification_id');
$criteria['outputType'] = $this->getSanParam('outputType');
// $criteria['language_id'] = $this->getSanParam('trainer_language_id');
$criteria['go'] = $this->getSanParam('go');
if ($criteria['go']) {
$db = Zend_Db_Table_Abstract::getDefaultAdapter();
$num_locs = $this->setting('num_location_tiers');
list($field_name, $location_sub_query) = Location::subquery($num_locs, $location_tier, $location_id, true);
if ($criteria['person_type'] == 'is_everyone') {
// left join instead of inner for everyone
$sql = '
SELECT DISTINCT p.id, p.last_name, p.middle_name, p.first_name, p.gender, p.birthdate, q.qualification_phrase, ' . implode(',', $field_name) . '
,q.parent_id, (SELECT COUNT(`comp`.`id`) FROM `comp` WHERE `comp`.`person` = `p`.`id` AND `comp`.`active` = \'Y\') `cmp`,p.comments as "persal",IFNULL(cmpr.res,10) `res` FROM person as p
LEFT JOIN person_qualification_option as q ON p.primary_qualification_option_id = q.id
LEFT JOIN facility as f ON p.facility_id = f.id
LEFT JOIN compres as cmpr ON cmpr.person = p.id AND cmpr.active=\'Y\'
LEFT JOIN (' . $location_sub_query . ') as l ON f.location_id = l.id ';
} else {
# removing from query, filtering happens on front-end
#if($criteria ['is_complete']=='is_complete'){
# $sql = 'select DISTINCT p.id, p.last_name, p.middle_name, p.first_name, p.gender, p.birthdate, q.qualification_phrase, '.implode(',',$field_name).',q.parent_id, (SELECT COUNT(`comp`.`id`) FROM `comp` WHERE `comp`.`person` = `p`.`id` AND `comp`.`active` = \'Y\') `cmp`,p.comments as "persal", cmpr.res from person as p, person_qualification_option as q, facility as f, ('.$location_sub_query.') as l, compres as cmpr';
#} else {
$sql = 'select DISTINCT p.id, p.last_name, p.middle_name, p.first_name, p.gender, p.birthdate, q.qualification_phrase, ' . implode(',', $field_name) . ',q.parent_id, (SELECT COUNT(`comp`.`id`) FROM `comp` WHERE `comp`.`person` = `p`.`id` AND `comp`.`active` = \'Y\') `cmp`,p.comments as "persal", IFNULL((SELECT `res` FROM `compres` WHERE `compres`.`person` = `p`.`id` AND `compres`.`active` = \'Y\'),10) `res` from person as p, person_qualification_option as q, facility as f, (' . $location_sub_query . ') as l';
#}
}
if ($criteria['person_type'] == 'is_participant') {
$sql .= ', person_to_training as ptt ';
}
if ($criteria['person_type'] == 'is_trainer') {
$sql .= ', trainer as trn';
if ($criteria['training_title_option_id']) {
$sql .= ', training_to_trainer as ttt ';
}
}
$where = array('p.is_deleted = 0');
/* 'f.location_id = l.id'); */
// bugfix - is shrinkings results
if ($criteria['person_type'] != 'is_everyone') {
// was left joined (see above)
$where[] = 'p.primary_qualification_option_id = q.id and p.facility_id = f.id and f.location_id = l.id ';
}
$locationWhere = $this->getLocationCriteriaWhereClause($criteria, '', 'pt');
if ($locationWhere) {
$where[] = $locationWhere;
}
# removing from query, filtering happens on front-end
#if($criteria ['is_complete']=='is_complete'){
# $where []= 'cmpr.person = p.id';
#}
if ($criteria['person_type'] == 'is_participant') {
$where[] = 'p.id = ptt.person_id ';
}
if ($criteria['person_type'] == 'is_trainer') {
$where[] = 'p.id = trn.person_id ';
}
if ($criteria['person_type'] == 'is_unattached_person') {
$where[] = 'p.id NOT IN (SELECT person_id FROM trainer) AND p.id NOT IN (SELECT person_id FROM person_to_training) ';
}
if ($criteria['facilityInput']) {
$where[] = ' p.facility_id = "' . $criteria['facilityInput'] . '"';
}
if ($criteria['training_title_option_id'] or $criteria['training_title_option_id'] === '0') {
$sql .= ', training as tr ';
if ($criteria['person_type'] == 'is_participant') {
$where[] = ' p.id = ptt.person_id AND ptt.training_id = tr.id AND tr.training_title_option_id = ' . $criteria['training_title_option_id'] . ' ';
}
if ($criteria['person_type'] == 'is_trainer') {
$where[] = ' p.id = trn.person_id AND trn.person_id = ttt.trainer_id AND ttt.training_id = tr.id AND tr.training_title_option_id = ' . $criteria['training_title_option_id'] . ' ';
}
}
if ($criteria['qualification_id'] or $criteria['qualification_id'] === '0') {
$where[] = '(primary_qualification_option_id = ' . $criteria['qualification_id'] . ' OR primary_qualification_option_id IN (SELECT id FROM person_qualification_option WHERE parent_id = ' . $criteria['qualification_id'] . ')) ';
}
if ($criteria['first_name']) {
$where[] = 'p.first_name LIKE "' . $criteria['first_name'] . '%"';
}
if ($criteria['last_name']) {
$where[] = 'p.last_name LIKE "' . $criteria['last_name'] . '%"';
}
# removing from query, filtering happens on front-end
#if($criteria ['is_complete']=='is_complete'){
# $where []= 'cmpr.active = \'Y\'';
#}
#if($criteria ['is_complete']=='is_complete'){
# $where []= 'cmpr.res = 0';
#}
if ($where) {
$sql .= ' WHERE ' . implode(' AND ', $where);
}
$sql .= " ORDER BY " . " `p`.`last_name` ASC, " . " `p`.`first_name` ASC";
#echo "Search query = " . $sql . "<br>";
$rowArray = $db->fetchAll($sql);
foreach ($rowArray as $key => $row) {
if (strpos($rowArray[$key]['qualification_phrase'], "_Ot") == 1) {
$rowArray[$key]['qualification_phrase'] = substr($rowArray[$key]['qualification_phrase'], 2);
}
if (strpos(strrev($rowArray[$key]['qualification_phrase']), "_") == 1) {
$rowArray[$key]['qualification_phrase'] = strrev(substr(strrev($rowArray[$key]['qualification_phrase']), 2));
}
}
$_arr = array();
foreach ($rowArray as $key => $row) {
$comps = $helper->getPersonCompetencies($row['id'], true);
$row['comps'] = $comps;
$_arr[$key] = $row;
}
$rowArray = $_arr;
foreach ($rowArray as $key => $row) {
$splt = split("§", $rowArray[$key]['persal']);
if (strlen($splt[1]) > 0) {
$rowArray[$key]['persal'] = $splt[1];
if (strlen($criteria['persal']) > 0) {
if (strcasecmp($splt[1], $criteria['persal']) == 0) {
} else {
unset($rowArray[$key]);
}
}
} else {
$rowArray[$key]['persal'] = '';
}
}
if ($criteria['outputType']) {
foreach ($rowArray as $key => $row) {
unset($rowArray[$key]['cmp']);
unset($rowArray[$key]['parent_id']);
}
$this->sendData($rowArray);
}
foreach ($rowArray as $key => $row) {
if (isset($row['gender']) and $row['gender']) {
$rowArray[$key]['gender'] = t(trim($row['gender']));
}
}
$this->viewAssignEscaped('results', $rowArray);
$this->view->assign('count', count($rowArray));
}
$this->view->assign('criteria', $criteria);
//locations
$this->viewAssignEscaped('locations', Location::getAll());
//training titles
require_once 'models/table/TrainingTitleOption.php';
$titleArray = TrainingTitleOption::suggestionList(false, 10000);
$this->viewAssignEscaped('courses', $titleArray);
//types
$qualificationsArray = OptionList::suggestionListHierarchical('person_qualification_option', 'qualification_phrase', false, false);
$this->viewAssignEscaped('qualifications', $qualificationsArray);
//facilities list
$rowArray = OptionList::suggestionList('facility', array('facility_name', 'id'), false, 9999);
$facilitiesArray = array();
foreach ($rowArray as $key => $val) {
if ($val['id'] != 0) {
$facilitiesArray[] = $val;
}
}
$this->viewAssignEscaped('facilities', $facilitiesArray);
}
public function peopleNewPeopleAction()
{
$db = Zend_Db_Table_Abstract::getDefaultAdapter();
$rows = $db->fetchAll('select
person.*,qualification_phrase,facility.facility_name,location.location_name,user.username as created_by from person
left join person_qualification_option on person_qualification_option.id = primary_qualification_option_id
left join facility on facility_id = facility.id
left join location on facility.location_id = location.id
left join user on user.id=person.created_by
where person.approved is null and person.is_deleted = 0');
$this->viewAssignEscaped('primary_results', $rows);
$go = $this->getSanParam('go');
if ($go) {
require_once 'PersonController.php';
$c = new PersonController($this->getRequest(), $this->getResponse());
$c->searchAction();
$this->viewAssignEscaped('primary_results', $rows);
}
// fill form dropdowns
$this->viewAssignEscaped('locations', Location::getAll());
//training titles
require_once 'models/table/TrainingTitleOption.php';
$titleArray = TrainingTitleOption::suggestionList(false, 10000);
$this->viewAssignEscaped('courses', $titleArray);
//types
$qualificationsArray = OptionList::suggestionListHierarchical('person_qualification_option', 'qualification_phrase', false, false);
$this->viewAssignEscaped('qualifications', $qualificationsArray);
//facilities list
$rowArray = OptionList::suggestionList('facility', array('facility_name', 'id'), false, 9999);
$facilitiesArray = array();
foreach ($rowArray as $key => $val) {
if ($val['id'] != 0) {
$facilitiesArray[] = $val;
}
}
$this->viewAssignEscaped('facilities', $facilitiesArray);
}
public function ssProfAction()
{
if (!$this->hasACL('view_people') and !$this->hasACL('edit_people')) {
$this->doNoAccessError();
}
$criteria = array();
list($criteria, $location_tier, $location_id) = $this->getLocationCriteriaValues($criteria);
$criteria['facilityInput'] = $this->getSanParam('facilityInput');
$criteria['training_title_option_id'] = $this->getSanParam('training_title_option_id');
$criteria['qualification_id'] = $this->getSanParam('qualification_id');
$criteria['ques'] = $this->getSanParam('ques');
$criteria['go'] = $this->getSanParam('go');
$criteria['all'] = $this->getSanParam('all');
if ($criteria['go']) {
# var_dump ($_GET);
# exit;
if ($criteria['all']) {
$db = Zend_Db_Table_Abstract::getDefaultAdapter();
$num_locs = $this->setting('num_location_tiers');
list($field_name, $location_sub_query) = Location::subquery($num_locs, $location_tier, $location_id);
$sql = 'select DISTINCT cmp.person, cmp.question, cmp.option from person as p, person_qualification_option as q, facility as f, (' . $location_sub_query . ') as l, comp as cmp, compres as cmpr';
if ($criteria['training_title_option_id']) {
$sql .= ', person_to_training as ptt ';
$sql .= ', training as tr ';
}
$where = array('p.is_deleted = 0');
$where[] = 'cmpr.person = p.id';
$where[] = 'cmp.person = p.id';
$where[] = ' p.primary_qualification_option_id = q.id and p.facility_id = f.id and f.location_id = l.id ';
if ($criteria['facilityInput']) {
$where[] = ' p.facility_id = "' . $criteria['facilityInput'] . '"';
}
if ($criteria['training_title_option_id']) {
$where[] = ' p.id = ptt.person_id AND ptt.training_id = tr.id AND tr.training_title_option_id = ' . $criteria['training_title_option_id'] . ' ';
}
$where[] = ' primary_qualification_option_id IN (SELECT id FROM person_qualification_option WHERE parent_id IN (6, 7, 8, 9) ) ';
$where[] = 'cmpr.active = \'Y\'';
$where[] = 'cmpr.res = 1';
$where[] = 'cmp.active = \'Y\'';
$sql .= ' WHERE ' . implode(' AND ', $where);
die(__LINE__ . " - " . $sql);
$rowArray = $db->fetchAll($sql);
$qss = array();
$nmss = array();
$qss = split(",", "0,1,2,3,4,5,6,7");
$nmss = split("~", "1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,200~01,02,03,04,05,06,07,08,09~31,32,33,34,35,36,37,38~41,42,43,44,45~51,52,53,54,55,56,57,58,59,510,511,512,513,514,515,516,517,518~61,62,63,64,65,66,67~71,72,73,74,75,76,77,78,79,710,711~21,22,23");
$ct;
$ct = 0;
$rssA = array();
$rssB = array();
$rssC = array();
$rssD = array();
$rssE = array();
$ctt;
foreach ($qss as $kys => $vls) {
$rssA[$ct] = 0;
$rssB[$ct] = 0;
$rssC[$ct] = 0;
$rssD[$ct] = 0;
$rssE[$ct] = 0;
$ctt = 0;
$wss = split(",", $nmss[$vls]);
foreach ($wss as $kyss => $vlss) {
foreach ($rowArray as $kss => $vss) {
if ($vlss . " " == $vss['question'] . " ") {
if ($vss['option'] == "A") {
$rssA[$ct] = $rssA[$ct] + 1;
} else {
if ($vss['option'] == "B") {
$rssB[$ct] = $rssB[$ct] + 1;
} else {
if ($vss['option'] == "C") {
$rssC[$ct] = $rssC[$ct] + 1;
} else {
if ($vss['option'] == "D") {
$rssD[$ct] = $rssD[$ct] + 1;
} else {
if ($vss['option'] == "E") {
$rssE[$ct] = $rssE[$ct] + 1;
}
}
}
}
}
$ctt = $ctt + 1;
}
}
}
if ($ctt > 0) {
$rssA[$ct] = number_format($rssA[$ct] / $ctt * 100, 2);
$rssB[$ct] = number_format($rssB[$ct] / $ctt * 100, 2);
$rssC[$ct] = number_format($rssC[$ct] / $ctt * 100, 2);
$rssD[$ct] = number_format($rssD[$ct] / $ctt * 100, 2);
$rssE[$ct] = number_format($rssE[$ct] / $ctt * 100, 2);
}
$ct = $ct + 1;
}
$this->viewAssignEscaped('results', $rowArray);
$this->viewAssignEscaped('rssA', $rssA);
$this->viewAssignEscaped('rssB', $rssB);
$this->viewAssignEscaped('rssC', $rssC);
$this->viewAssignEscaped('rssD', $rssD);
$this->viewAssignEscaped('rssE', $rssE);
} else {
$db = Zend_Db_Table_Abstract::getDefaultAdapter();
$num_locs = $this->setting('num_location_tiers');
list($field_name, $location_sub_query) = Location::subquery($num_locs, $location_tier, $location_id);
$sql = 'select DISTINCT cmp.person, cmp.question, cmp.option from person as p, person_qualification_option as q, facility as f, (' . $location_sub_query . ') as l, comp as cmp, compres as cmpr';
if ($criteria['training_title_option_id']) {
$sql .= ', person_to_training as ptt ';
$sql .= ', training as tr ';
}
$where = array('p.is_deleted = 0');
$whr = array();
$where[] = 'cmpr.person = p.id';
$where[] = 'cmp.person = p.id';
$where[] = ' p.primary_qualification_option_id = q.id and p.facility_id = f.id and f.location_id = l.id ';
if ($criteria['facilityInput']) {
$where[] = ' p.facility_id = "' . $criteria['facilityInput'] . '"';
}
if ($criteria['training_title_option_id']) {
$where[] = ' p.id = ptt.person_id AND ptt.training_id = tr.id AND tr.training_title_option_id = ' . $criteria['training_title_option_id'] . ' ';
}
$where[] = ' primary_qualification_option_id IN (SELECT id FROM person_qualification_option WHERE parent_id = ' . $criteria['qualification_id'] . ') ';
$where[] = 'cmpr.active = \'Y\'';
$where[] = 'cmpr.res = 1';
$where[] = 'cmp.active = \'Y\'';
$qry = "SELECT id FROM competencies_questions WHERE competencyid IN (" . implode(",", $_GET['competencyselect']) . ")";
$questionresult = $db->fetchAll($qry);
$_q = array();
foreach ($questionresult as $qres) {
$_q[] = $qres['id'];
}
$whr[] = 'cmp.question IN (' . implode(",", $_q) . ')';
# if($criteria ['qualification_id']=="6"){
# $whr []= 'cmp.question IN ('."'".str_replace(",","','",$this->getSanParam ( 'listcq' ))."'".')';
# }
# if($criteria ['qualification_id']=="7"){
# $qs=split(",",$this->getSanParam ( 'ques' ));
# $nms=split("~",$this->getSanParam ( 'listdq' ));
# foreach ( $qs as $kys => $vls ) {
# $whr []= 'cmp.question IN ('."'".str_replace(",","','",$nms[$vls])."'".')';
# }
# }
# if($criteria ['qualification_id']=="8"){
# $qs=split(",",$this->getSanParam ( 'ques' ));
# $nms=split("~",$this->getSanParam ( 'listnq' ));
# foreach ( $qs as $kys => $vls ) {
# $whr []= 'cmp.question IN ('."'".str_replace(",","','",$nms[$vls])."'".')';
# }
# }
# if($criteria ['qualification_id']=="9"){
# $whr []= 'cmp.question IN ('."'".str_replace(",","','",$this->getSanParam ( 'listpq' ))."'".')';
# }
if (!empty($where)) {
$sql .= ' WHERE ' . implode(' AND ', $where);
}
if (!empty($whr)) {
$sql .= ' AND (' . implode(' OR ', $whr) . ')';
}
//todo check everything same here!
$rowArray = $db->fetchAll($sql);
$qss = array();
$nmss = array();
if ($criteria['qualification_id'] == "6") {
$qss = split(",", $this->getSanParam('ques'));
$nmss = split("~", $this->getSanParam('listcq'));
}
if ($criteria['qualification_id'] == "7") {
$qss = split(",", $this->getSanParam('ques'));
$nmss = split("~", $this->getSanParam('listdq'));
}
if ($criteria['qualification_id'] == "8") {
$qss = split(",", $this->getSanParam('ques'));
$nmss = split("~", $this->getSanParam('listnq'));
}
if ($criteria['qualification_id'] == "9") {
$qss = split(",", $this->getSanParam('ques'));
$nmss = split("~", $this->getSanParam('listpq'));
}
$ct;
$ct = 0;
$rssA = array();
$rssB = array();
$rssC = array();
$rssD = array();
$rssE = array();
$ctt;
foreach ($qss as $kys => $vls) {
$rssA[$ct] = 0;
$rssB[$ct] = 0;
$rssC[$ct] = 0;
$rssD[$ct] = 0;
$rssE[$ct] = 0;
$ctt = 0;
$wss = split(",", $nmss[$vls]);
foreach ($wss as $kyss => $vlss) {
foreach ($rowArray as $kss => $vss) {
if ($vlss . " " == $vss['question'] . " ") {
if ($vss['option'] == "A") {
$rssA[$ct] = $rssA[$ct] + 1;
} else {
if ($vss['option'] == "B") {
$rssB[$ct] = $rssB[$ct] + 1;
} else {
if ($vss['option'] == "C") {
$rssC[$ct] = $rssC[$ct] + 1;
} else {
if ($vss['option'] == "D") {
$rssD[$ct] = $rssD[$ct] + 1;
} else {
if ($vss['option'] == "E") {
$rssE[$ct] = $rssE[$ct] + 1;
}
}
}
}
}
$ctt = $ctt + 1;
}
}
}
if ($ctt > 0) {
$rssA[$ct] = number_format($rssA[$ct] / $ctt * 100, 2);
$rssB[$ct] = number_format($rssB[$ct] / $ctt * 100, 2);
$rssC[$ct] = number_format($rssC[$ct] / $ctt * 100, 2);
$rssD[$ct] = number_format($rssD[$ct] / $ctt * 100, 2);
$rssE[$ct] = number_format($rssE[$ct] / $ctt * 100, 2);
}
$ct = $ct + 1;
}
$this->viewAssignEscaped('results', $rowArray);
$this->viewAssignEscaped('rssA', $rssA);
$this->viewAssignEscaped('rssB', $rssB);
$this->viewAssignEscaped('rssC', $rssC);
$this->viewAssignEscaped('rssD', $rssD);
$this->viewAssignEscaped('rssE', $rssE);
}
}
$this->view->assign('criteria', $criteria);
$this->viewAssignEscaped('locations', Location::getAll());
require_once 'models/table/TrainingTitleOption.php';
$titleArray = TrainingTitleOption::suggestionList(false, 10000);
$this->viewAssignEscaped('courses', $titleArray);
$qualificationsArray = OptionList::suggestionListHierarchical('person_qualification_option', 'qualification_phrase', false, false);
$this->viewAssignEscaped('qualifications', $qualificationsArray);
$rowArray = OptionList::suggestionList('facility', array('facility_name', 'id'), false, 9999);
$facilitiesArray = array();
foreach ($rowArray as $key => $val) {
if ($val['id'] != 0) {
$facilitiesArray[] = $val;
}
}
$this->viewAssignEscaped('facilities', $facilitiesArray);
$helper = new Helper();
$this->viewAssignEscaped("competencies", $helper->getCompetencies());
$this->viewAssignEscaped("compqualification", $helper->getQualificationCompetencies());
}
