<?php
/**
 * FILE_NAME_HERE - FILE_DESCRIPTION_HERE
 *
 * @author $Author: dtong $
 * @version $Id: unit_select_model.php,v 1.23 2011/04/15 07:45:53 dtong Exp $
 * @copyright Copyright (c) 2009, Tiller Software Co., Ltd.
*/

class Unit_select_model extends CI_Model {
	private $use_gropyby = TRUE;

	function __construct() {
		parent::__construct();
		$this->load->model('unit/unit_model');
	}

	/*
	 * Get query onclick chart on the input parameters.
	 *
	 * @param query $course_query
	 * 	The query string.
	 * @param int $selectfield
	 * 	The selectfield number.
	 * @param int $type
	 * 	The type number.
	 * @param array $params
	 * 	The params array
	 *
	 * @return string
	 * 	Returns string.
	 */
	function get_click_query($course_query, $selectfield, $type, $params) {
		return $this->graph_data($course_query, $selectfield, $type, TRUE, FALSE, $params);
	}

	/*
	 * Get query on the input parameters.
	 *
	 * @param query $course_query
	 * 	The query string.
	 * @param int $selectfield
	 * 	The selectfield number.
	 * @param int $type
	 * 	The type number.
	 * @param array|FALSE $params
	 * 	The params array or FALSE
	 *
	 * @return string
	 * 	Returns string.
	 */
	function get_list_query($course_query, $selectfield, $type, $params=FALSE) {
		$this->use_gropyby = FALSE;
		return $this->graph_data($course_query, $selectfield, $type, TRUE,TRUE, $params);
	}

	//Simple version without template selection support
	function graph_data($course_query, $selectfield, $resource_type, $onclick=FALSE,$all_selections=FALSE, $params=FALSE) {
		if ( isset($params) && $params != FALSE && array_key_exists('select_index',$params) && $params['select_index'] != FALSE ){
			return $this->get_data_has_select_index($course_query, $selectfield, $resource_type, $onclick, $all_selections, $params);
		}
		else{
			$unitcache_table = osa_unit_cachetable();
			if ( $unitcache_table == FALSE )
				return FALSE;
			$unitcache_table = $this->db->dbprefix($unitcache_table);

			$resource_table = $this->db->dbprefix('resource');
			$select_table = $this->db->dbprefix('resource_select');
			$unit_table = $this->db->dbprefix('unit');
			$where = " r.type =" . $resource_type;
			if (is_array($resource_type)){
				$resource_type = implode(',',$resource_type);
				$where  = " r.type in(" . $resource_type . ")";
			}
			if (isset($params['template']) && is_array($params['template'])){
				$where .= ' and ' .$this->where_template($params['template']);
			}

			$groupby = '';
			if ( $this->use_gropyby ) {
				$groupby = 'group by r.unitid,s.choice';
			}
			$sql = "select r.unitid,u.title unit_title,r.type res_type,s.choice selectvalue,r.text1 res_description,r.courseid,
						c.name coursename,'$selectfield' select_field,u.template,r.id resourceid
						from $unitcache_table uc
 				   	join $unit_table u on uc.id=u.id
 				   	join $resource_table r on r.unitid=uc.compare_unif_id
 				   	join $select_table s on r.id = s.resourceid
						join ($course_query) c on uc.courseid=c.id
						where $where and s.type = $selectfield
						$groupby
						order by coursename,uc.courseid,unit_title,unitid,s.choice,r.id";
			return $sql;
		}
	}
	/*
	 * Get get_data_has_select_index on the input parameters. This is the big one that can handle template selections.
	 * Function graph_data(above does the same thing but without template selection support.
	 *
	 * @param query $course_query
	 * 	The query string.
	 * @param int $selectfield
	 * 	The selectfield number.
	 * @param int $resource_type
	 * 	The resource_type number.
	 * @param boolean $onclick
	 * 	The onclick  TRUE or FALSE.
	 * @param boolean $all_selections
	 * 	The all_selections if onclick chart = FALSE else TRUE.
	 * @param array $params
	 * 	Inside $params:
	 * 	_select_template_msg - The massage error.
	 * 	_select_index - The selection type from template config.
	 * 	_template - The template names and it is an array, can be one or more of 'ubd', 'pyp', 'myp'
	 *
	 * @return object|string
	 * 	Returns a database result - array of objects or query string.
	 */
	function get_data_has_select_index($course_query, $selectfield, $resource_type, $onclick=FALSE,$all_selections=FALSE, $params=FALSE) {
		$status = $this->unit_model->STATUS_CURRENT;
		$course_table = $this->db->dbprefix('course_sis');
		$unit_table = $this->db->dbprefix('unit');
		$resource_table = $this->db->dbprefix('resource');
		$select_table = $this->db->dbprefix('resource_select');
		if ($params === FALSE)
			return FALSE;
		$template = FALSE;
		if (array_key_exists('template', $params))
			$template = $params['template'];

		$select_index = FALSE;
		if (array_key_exists('select_index', $params))
			$select_index = $params['select_index'];

		if ($template === FALSE && empty($template) )
			return FALSE;

		if (is_array($template)) {
			$temp = implode('',$template);
			if ($temp == '') {
				echo osa_ajaxmsg($params['select_template_msg']);
				exit;
			}
		}
		if (is_array($select_index) )
			$select_index = $select_index;
		else
			$select_index = $selectfield;

		if (is_array($resource_type))
			$resource_type = implode(',',$resource_type);

		$unitcache_table = osa_unit_cachetable();
		if ( $unitcache_table == FALSE )
			return FALSE;
		$unitcache_table = $this->db->dbprefix($unitcache_table);

		$from = " from $unitcache_table uc
					join $unit_table u on uc.id = u.id
					join $resource_table r on uc.compare_unif_id=r.unitid
					join $select_table s on r.id=s.resourceid
			 		join ($course_query) c on uc.courseid=c.id" ;

		//This prpares the sql for the search when user clicks on the pie chart.
		if ( $onclick ) {
			$fields_graph = " r.unitid,u.title unit_title,u.template,
								   r.type res_type ,s.choice selectvalue,r.text1 res_description,r.courseid,
								   c.name coursename,'$selectfield' select_field, r.id resourceid";
			$sqlunion = '';
			$comma = '';
			$order_by = '';
			$groupby = '';
			if ( $this->use_gropyby ) {
				$groupby = 'group by r.unitid,s.choice';
			}
			foreach ($template as $key) {
				//List all selections belonging to the selected category
				if ( $all_selections ) {
					$sql_graph = "select $fields_graph $from
									 where s.type=%res_sel_type and u.template='%unit_template' and r.type in ($resource_type)
									 $groupby ";
					$order_by  = " order by coursename,unit_title,unitid ";
				}
				else {
					//This is the one for each pie slice
					$sql_graph = "select $fields_graph $from
								 	  where s.type=%res_sel_type and u.template='%unit_template'
								 	  and r.type in ($resource_type) and s.choice=%s ";
					$order_by  = " order by coursename,unit_title";
				}

				if (!strpos('unit_template', $sql_graph) ) {
					$sel_type = '';
					if (is_array($select_index) ) {
						if (array_key_exists($selectfield, $select_index) && array_key_exists($key, $select_index[$selectfield]) ) {
							$sel_type = $select_index[$selectfield][$key];
							$replace = str_replace('%unit_template', $key, $sql_graph);
							$replace = str_replace('%res_sel_type', $sel_type, $replace);
							$sqlunion .= $comma . $replace;
							$comma = ' union ';
						}
					}
					else {
						$sel_type = $selectfield;
						$replace = str_replace('%unit_template', $key, $sql_graph);
						$replace = str_replace('%res_sel_type', $sel_type, $replace);
						$sqlunion .= $comma . $replace;
						$comma = ' union ';
					}
				}
			}
			if ($sqlunion != '')
				$sql_graph = 'select * from ('.$sqlunion.') g '.$order_by;
				$sql_graph = $this->no_data($sql_graph);
			return $sql_graph;
		}

		$fields = "'%unit_template' temp, s.choice selectvalue, count(*) total, r.type";
		$where = "r.type in ($resource_type) group by s.choice";
		$sql = "select $fields $from  where s.type=%res_sel_type and u.template='%unit_template' and $where";
		$sqlunion = '';
		$comma = '';
		foreach ($template as $key) {
			if (is_array($select_index) ) {
				if (array_key_exists($selectfield, $select_index) && array_key_exists($key, $select_index[$selectfield]) ) {
					$sel_type = $select_index[$selectfield][$key];

					$replace = str_replace('%unit_template', $key, $sql);
					$replace = str_replace('%res_sel_type', $sel_type, $replace);
					$sqlunion .= $comma . $replace;
					$comma = ' union ';
				}
			}
			else {
				$sel_type = $selectfield;

				$replace = str_replace('%unit_template', $key, $sql);
				$replace = str_replace('%res_sel_type', $sel_type, $replace);
				$sqlunion .= $comma . $replace;
				$comma = ' union ';
			}
		}
		if ($sqlunion != '')
			$sql = 'select selectvalue, sum(total) total, type from ('.$sqlunion.') g group by selectvalue';
			$sql = $this->no_data($sql);
		$res = $this->db->query($sql);
		if ( $res->num_rows() <= 0 )
			return FALSE;
		return $res->result();
	}

	function run_query($sql) {
		$res = $this->db->query($sql);
		if ( $res->num_rows() <= 0 )
			return FALSE;
		return $res->result();
	}


	/*
	 * Get Query Trans-disciplinary Skills on the input parameters.
	 *
	 * @param query $course_query
	 * 	The query string.
	 * @param string $choice
	 * 	The query string.
	 *
	 * @return string
	 * 	Returns string.
	 */
	function get_tds_query($course_query, $choice) {
		$unitcache_table = osa_unit_cachetable();
		if ( $unitcache_table == FALSE )
			return FALSE;
		$unitcache_table = $this->db->dbprefix($unitcache_table);

		$status = $this->unit_model->STATUS_CURRENT;
		$unit_select_table = $this->db->dbprefix('unit_selection');
		$selection_category_table = $this->db->dbprefix('selection_category');
		$unit_table = $this->db->dbprefix('unit');

		$sql = "select c.name coursename,u.id unitid,u.title unit_title,u.startdate,u.enddate,u.description,sc.text
					from $unitcache_table uc
					join $unit_table u on uc.id = u.id
					join $unit_select_table us on uc.compare_unif_id=us.unitid
					join $selection_category_table sc on sc.id=us.catid
				 	join ($course_query) c on uc.courseid=c.id
					where sc.text like '{$choice}%'
					order by coursename,unit_title";

		return $sql;
	}

 // get selection_category
    function  get_category($params,$split=false, $valueindex=0) {
    	$table_selection_category = $this->db->dbprefix('selection_category');
    	$where = '';
		if(isset($params['type']) && $params['type'] != FALSE)
			$where = " where type =". $params['type'];
		elseif (isset($params['text']) && $params['text'] != FALSE)
			$where = " where text like "."'{$params['text']}%'";

		$sql = " select id ,text " ;
		$sql .= " from ".$table_selection_category;
		$sql .= $where;
		$sql .= " order by text";
		$rowobj = $this->db->query($sql);

		if ( $rowobj->num_rows() <= 0 )
			return FALSE;
		$result = array();
    	foreach ($rowobj->result() as $value){
			if ($split != false) {
				$data = explode($split, $value->text);
				if (is_array($data) && array_key_exists($valueindex, $data)) {
					$tmp = trim($data[$valueindex]);
					$result[$value->id] = $tmp;
				}
			}
			else
				$result[$value->id] = $value->text;
		}

		 return $result;
    }

	/*
	 * Get Data understandings on the input parameters.
	 *
	 * @param query $course_query
	 * 	The query string.
	 * @param array $params
	 * 	Inside $params:
	 * 	_restype - The resource type that is being processed.
	 *
	  * @return object
	 * 	Returns a database result - array of objects.
	 */
	function get_understandings($course_query, $res_type) {
		$where = '';
		if (isset($res_type) && is_array($res_type)){
			$res_type = implode(',',$res_type);
			$where = ' and r.type in('.$res_type.')';
		}
		$unitcache_table = osa_unit_cachetable();
		if ( $unitcache_table == FALSE )
			return FALSE;
		$unitcache_table = $this->db->dbprefix($unitcache_table);

		$status = $this->unit_model->STATUS_CURRENT;
		$resource_table = $this->db->dbprefix('resource');
		$unit_table = $this->db->dbprefix('unit');

		$sql = " select c.name coursename,u.id unitid,u.title unit_title,u.description,r.text1 text
						from $unitcache_table uc
						join $unit_table u on uc.id = u.id
						join $resource_table r on uc.compare_unif_id = r.unitid
				 		join ($course_query) c on uc.courseid=c.id
						where u.status= $status $where
						group by u.id,r.text1
						order by coursename,unit_title";

		$res = $this->db->query($sql);
		if ( $res->num_rows() <= 0 )
			return FALSE;
		return $res->result();
	}

	//Get Query formative_assessment
	/*
	 * Get query formative_assessment on the input parameters.
	 *
	 * @param query $course_query
	 * 	The query string.
	 * @param array $params
	 * 	Inside $params:
	 * 	_type - The resource type that is being processed.
	 * 	_template - The template names and it is an array, can be one or more of 'ubd', 'pyp', 'myp'
	 *
	  * @return string
	 * 	Returns string.
	 */
	function get_formative_assessment_query($course_query, $params) {
		$status = $this->unit_model->STATUS_CURRENT;
		$unit_table = $this->db->dbprefix('unit');
		$resource_table = $this->db->dbprefix('resource');
		$resource_select_table = $this->db->dbprefix('resource_select');
		$where = '';
		$and  = '';
		if (isset($params['type']) && is_array($params['type'])){
			$res_type = implode(',',$params['type']);
			$where = 'r.type in('.$res_type.')';
			$and = ' and ';
		}

		if (isset($params['template']) && is_array($params['template'])){
			$where .= $and .$this->where_template($params['template']);
		}
		$unitcache_table = osa_unit_cachetable();
		if ( $unitcache_table == FALSE )
			return FALSE;
		$unitcache_table = $this->db->dbprefix($unitcache_table);

		$sql = " select c.name coursename,u.id unitid,u.title unit_title,u.description,
						u.template,r.text1 text
						from $unitcache_table uc
						join $unit_table u on uc.id = u.id
						join $resource_table r on uc.compare_unif_id = r.unitid
						join $resource_select_table rs on r.id = rs.resourceid
				 		join ($course_query) c on uc.courseid=c.id
						where $where
						group by u.id,r.text1
						order by coursename,unit_title";

		return $sql;
	}
	private function no_data($sql){
		$sql = str_replace('%unit_template', 0, $sql);
		$sql = str_replace('%res_sel_type', 0, $sql);
		return $sql;
	}
	private function where_template($template=FALSE) {
		$where = '';
		if ($template === FALSE)
			return $where;
		$sep ='';
		$_template = '';
		if ($template !== FALSE){
			foreach ($template as $value){
				if ($value != FALSE){
					$_template .= $sep."'".$value."'";
					$sep = ',';
				}
			}
			$where = "  u.template in(".$_template.")";
		}
		return $where;
	}
}
?>