public function init(&$controller)
{
$this->controller = $controller;
$this->url = $this->controller->getRequest()->getRequestUri();
$this->url = str_replace("/outputType/" . $this->controller->getSanParam('outputType'), "", $this->url);
$this->url = str_replace("/edit/" . $this->controller->getSanParam('edit'), "", $this->url);
$this->url = str_replace("/delete/" . $this->controller->getSanParam('delete'), "", $this->url);
$output = '';
$checked = array();
// for json table
if ($this->controller->getSanParam('outputType')) {
return $this->json();
}
// saving
if ($this->controller->getRequest()->isPost()) {
// Update db
MultiAssignList::save($this->table, $this->parent_table, $this->option_table, $this->controller->getSanParam("{$this->parent_table}_id"), $this->controller->getSanParam("{$this->option_table}_id"));
}
//
// deleting
// editing
if ($edit_id = $this->controller->getSanParam('edit')) {
$assignedArray = MultiAssignList::getAssigned($this->table, $this->parent_table, $this->option_table, $edit_id);
foreach ($assignedArray as $row) {
$checked[] = $row["{$this->option_table}_id"];
}
}
$output .= '
<div id="jsonTableHolder"></div>
<script type="text/javascript">
var multiColumnDefs = [
{key:"' . key($this->parent_field) . '", label: "' . current($this->parent_field) . '", sortable:true, resizeable:true},
{key:"' . key($this->option_field) . '", label: "' . current($this->option_field) . '", sortable:true, resizeable:true},
{key:"edit", label: "Edit", sortable:true, resizeable:true}
];
var action = "' . $this->url . '/outputType/json";
makeJSONDataTable("jsonTableHolder", null, action, multiColumnDefs);
</script>
<a name="edit"></a>
<div class="hrGrey"></div>
';
// drop-down
$attributes['onchange'] = "document.location = '{$this->url}/edit/' + this.value";
$output .= '<div class="label">' . current($this->parent_field) . '</div>';
$output .= DropDown::generateHtml($this->parent_table, key($this->parent_field), $this->controller->getSanParam('edit'), false, false, false, false, $attributes);
$output .= '<br><br>';
//$options = OptionList::suggestionList($this->option_table, $this->option_field, false, false);
//print_r($options);
$output .= Checkboxes::generateHtml($this->option_table, key($this->option_field), $view, $checked);
return $output;
}
public function participantsByCategoryAction()
{
require_once 'views/helpers/Location.php';
$criteria = array();
//find the first date in the database
$db = Zend_Db_Table_Abstract::getDefaultAdapter();
$sql = "SELECT MIN(training_start_date) as \"start\" FROM training WHERE is_deleted = 0 ";
$rowArray = $db->fetchAll($sql);
$start_default = $rowArray[0]['start'];
$parts = explode('-', $start_default);
$criteria['start-year'] = $parts[0];
$criteria['start-month'] = $parts[1];
$criteria['start-day'] = $parts[2];
$criteria['end-year'] = date('Y');
$criteria['end-month'] = date('m');
$criteria['end-day'] = date('d');
$criteria['cat'] = $this->getSanParam('cat');
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->getSanParam('end-year')) {
$criteria['end-year'] = $this->getSanParam('end-year');
}
if ($this->getSanParam('end-month')) {
$criteria['end-month'] = $this->getSanParam('end-month');
}
if ($this->getSanParam('end-day')) {
$criteria['end-day'] = $this->getSanParam('end-day');
}
//locations
$locations = Location::getAll();
$this->view->assign('locations', $locations);
list($criteria, $location_tier, $location_id) = $this->getLocationCriteriaValues($criteria);
$criteria['training_organizer_option_id'] = $this->getSanParam('training_organizer_option_id');
//Q1 query UNION
//Q2 query UNION
//Q3 query UNION
//Q4 query
//make sure the date doesn't go back too far
//if ( $criteria['start-year'] < 2000 ) {
// $criteria['start-year'] = 2000;
//}
$qDate = $criteria['start-year'] . '-' . $criteria['start-month'] . '-' . $criteria['start-day'];
$endDate = $criteria['end-year'] . '-' . $criteria['end-month'] . '-' . $criteria['end-day'];
$selectFields = array();
if ($criteria['training_organizer_option_id']) {
$selectFields[] = 'training_organizer_phrase';
}
if ($selectFields) {
$selectFields = ', ' . implode(',', $selectFields);
} else {
$selectFields = '';
}
$rowArray = array();
if ($this->getSanParam('go')) {
$db = Zend_Db_Table_Abstract::getDefaultAdapter();
switch ($criteria['cat']) {
case 'level':
$sql = 'SELECT count(DISTINCT ptt.id) as "cnt", count(DISTINCT ptt.person_id) as "person_cnt", tlo.training_level_phrase as "cat" ' . $selectFields . ' FROM ' . ' person_to_training as ptt JOIN training as t ON ptt.training_id = t.id ';
$sql .= ' JOIN person as p ON ptt.person_id = p.id ';
$sql .= ' LEFT JOIN training_level_option as tlo ON t.training_level_option_id = tlo.id ';
break;
case 'qualification':
$sql = 'SELECT count(DISTINCT ptt.id) as "cnt", count(DISTINCT ptt.person_id) as "person_cnt", pqo.qualification_phrase as "cat" ' . $selectFields . ' FROM ' . ' person_to_training as ptt JOIN training as t ON ptt.training_id = t.id ';
$sql .= ' JOIN person as p ON ptt.person_id = p.id ';
//$sql .= ' LEFT JOIN person_qualification_option as pqo ON p.primary_qualification_option_id = pqo.id ';
// primary qualifications only
$sql .= '
LEFT JOIN person_qualification_option as pqo ON (
(p.primary_qualification_option_id = pqo.id AND pqo.parent_id IS NULL)
OR
pqo.id = (SELECT parent_id FROM person_qualification_option WHERE id = p.primary_qualification_option_id LIMIT 1)
)';
break;
case 'pepfar':
$sql = 'SELECT count(DISTINCT ptt.id) as "cnt", count(DISTINCT ptt.person_id) as "person_cnt", pfr.pepfar_category_phrase as "cat" ' . $selectFields . ' FROM ' . ' person_to_training as ptt JOIN training as t ON ptt.training_id = t.id ';
$sql .= ' JOIN person as p ON ptt.person_id = p.id ';
$sql .= ' LEFT JOIN training_to_training_pepfar_categories_option as tpfr ON t.id = tpfr.training_id ';
$sql .= ' LEFT JOIN training_pepfar_categories_option as pfr ON tpfr.training_pepfar_categories_option_id = pfr.id ';
break;
}
$num_locs = $this->setting('num_location_tiers');
list($field_name, $location_sub_query) = Location::subquery($num_locs, $location_tier, $location_id, true);
if ($criteria['district_id'] or !empty($criteria['province_id']) or !empty($criteria['region_c_id']) || $criteria['region_d_id'] || $criteria['region_e_id'] || $criteria['region_f_id'] || $criteria['region_g_id'] || $criteria['region_h_id'] || $criteria['region_i_id']) {
$sql .= ' JOIN facility as f ON p.facility_id = f.id JOIN (' . $location_sub_query . ') as l ON f.location_id = l.id ';
}
if ($criteria['training_organizer_option_id']) {
$sql .= ' JOIN training_organizer_option as torg ON torg.id = t.training_organizer_option_id ';
}
$sql .= ' WHERE training_start_date >= \'' . $qDate . '\' AND training_start_date <= \'' . $endDate . '\' ';
// restricted access?? only show trainings we have the ACL to view
require_once 'views/helpers/TrainingViewHelper.php';
$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);
$sql .= " 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
$sql .= $site_orgs ? " AND training_organizer_option_id in ({$site_orgs}) " : "";
if ($locWhere = $this->getLocationCriteriaWhereClause($criteria)) {
$sql .= ' AND ' . $locWhere;
}
if ($criteria['training_organizer_option_id'] && is_array($criteria['training_organizer_option_id'])) {
$sql .= ' AND t.training_organizer_option_id IN (' . implode(',', $criteria['training_organizer_option_id']) . ')';
}
$sql .= ' GROUP BY cat ';
if ($criteria['training_organizer_option_id'] && is_array($criteria['training_organizer_option_id'])) {
$sql .= ', t.training_organizer_option_id ';
}
$sql .= ' ORDER BY cat ASC ';
$rowArray = $db->fetchAll($sql);
//add a total row
$total = 0;
foreach ($rowArray as $row) {
$total += $row['cnt'];
}
if ($this->_getParam('outputType')) {
$this->sendData($this->reportHeaders(false, $rowArray));
}
}
$this->view->assign('count', isset($total) ? $total : 0);
$this->viewAssignEscaped('results', $rowArray);
$this->view->assign('criteria', $criteria);
//organizers
// 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 = " 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) {
$orgWhere .= $orgWhere ? " AND id in ({$site_orgs}) " : " id in ({$site_orgs}) ";
}
$this->view->assign('organizers_checkboxes', Checkboxes::generateHtml('training_organizer_option', 'training_organizer_phrase', $this->view, array(), $orgWhere));
}