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