<?php
/**
 * compare_model.php - Model compare data
 *
 * @author $Author: samarth
 * @version $Id: compare_model.php
 * @copyright Copyright (c) 2010, Tiller Software Co., Ltd.
*/
class Compare_model extends CI_Model {

	function __construct() {
		parent::__construct();
    }

    /*
	 * Get resource model on the input parameters.
	 *
	 * @param int $unitid
	 * 	The unitid number.
	 * @param int $type
	 * 	The type number.
	 *
	 * @return array
	 * 	Returns a 2D array .
	 */
	function get_resources($unitid, $type) {
		if ( !is_numeric($unitid) || !is_numeric($type))
   		return FALSE;
		$table_resource = $this->db->dbprefix('resource');
		$this->db->select('r.id resid , r.type, r.text1, r.id');
		$this->db->from($table_resource . ' r');
		$this->db->where('r.type', $type);
		$this->db->where('r.unitid', $unitid);

		$res = $this->db->get();
	   if ( $res->num_rows() <= 0 )
	   		return FALSE;
	   $rows = $res->result();
	   $retarray = array();
	   foreach ($rows as $row) {
		   	if (!array_key_exists($row->resid, $retarray)) {
		   		$retarray[$row->resid] = array();
		   	}
		   	$retarray[$row->resid][] = $row;
	   }
	   return $retarray;
	}

	/*
	 * Get upload model on the input parameters.
	 *
	 * @param int $unitid
	 * 	The unitid number.
	 * @param int $type
	 * 	The type number.
	 *
	 * @return array
	 * 	Returns a 2D array .
	 */
	function get_upload($unitid, $type) {
		if ( !is_numeric($unitid) || !is_numeric($type))
   		return FALSE;
		$table_resource = $this->db->dbprefix('resource');
		$table_upload = $this->db->dbprefix('upload');

   	$select = ' r.id as resid, r.courseid, r.unitid, r.type, u.id, '.
					 ' u.size length, u.filename, concat(u.size,"_",u.filename) filelength ';
		$from = " $table_resource r";
		$join = " left join $table_upload u on r.id=u.resourceid ";
		$where = " r.type=$type and r.unitid=$unitid ";

		$query   = "select $select from $from $join where $where ";
		$res = $this->db->query( $query );
	   if ( $res->num_rows() <= 0 )
	   		return FALSE;
	   $rows = $res->result();
	   $retarray = array();
	   foreach ($rows as $row) {
		   	if (!array_key_exists($row->resid, $retarray)) {
		   		$retarray[$row->resid] = array();
		   	}
		   	$retarray[$row->resid][] = $row;
	   }
	   return $retarray;
	}

	/*
	 * Get resource_url model on the input parameters.
	 *
	 * @param int $unitid
	 * 	The unitid number.
	 * @param int $type
	 * 	The type number.
	 *
	 * @return array
	 * 	Returns a 2D array .
	 */
	function get_resource_url($unitid, $type) {
		if ( !is_numeric($unitid) || !is_numeric($type))
   		return FALSE;
		$table_resource = $this->db->dbprefix('resource');
		$table_resource_url = $this->db->dbprefix('resource_url');

   	$select = 'r.id as resid, r.courseid, r.unitid, r.type, '.
					 'u.id, u.url ';
		$from = "$table_resource r";
		$join = "left join $table_resource_url u on r.id=u.resourceid ";
		$where = "r.type=$type and r.unitid=$unitid ";

		$query   = "select $select from $from $join where $where ";

		$res = $this->db->query( $query );
	   if ( $res->num_rows() <= 0 )
	   		return FALSE;
	   $rows = $res->result();
	   $retarray = array();
	   foreach ($rows as $row) {
		   	if (!array_key_exists($row->resid, $retarray)) {
		   		$retarray[$row->resid] = array();
		   	}
		   	$retarray[$row->resid][] = $row;
	   }
	   return $retarray;
	}

	/*
	 * Get resource_select model on the input parameters.
	 *
	 * @param int $unitid
	 * 	The unitid number.
	 * @param int|array $type
	 * 	The type number or the type array.
	 * @param int $resid
	 * 	The resid number.
	 *
	 * @return array
	 * 	Returns a 3D array .
	 */
	function get_resource_select($unitid, $type, $resid=FALSE) {
		if ( !is_numeric($unitid) )
   		return FALSE;

   	if (is_array($type) ) {
   		if (count($type) == 0 )
   			return FALSE;
   	}
	   elseif (!is_numeric($type) )
   		return FALSE;

   	$table_resource = $this->db->dbprefix('resource');
		$table_resource_select = $this->db->dbprefix('resource_select');

   	$this->db->select('r.id as resid, r.type as restype, r.text1, s.type, s.choice');
   	$this->db->from($table_resource . ' r');
   	$this->db->join($table_resource_select. ' s', 'r.id=s.resourceid');
   	$this->db->where('r.unitid', $unitid);

   	if ($resid !== FALSE && is_numeric($resid))
   		$this->db->where('r.id', $resid);

   	if ( is_array($type) )
			$this->db->where_in('r.type', $type);
		else
			$this->db->where('r.type', $type);

   	$this->db->order_by('r.id,s.type', 'asc');
   	$res = $this->db->get();
   	if ( $res->num_rows() <= 0 )
			return FALSE;
   	$rows = $res->result();
   	$res = array();
   	$retarray = array();
   	//Build the data structure to be used in the dropdown function for each select in each resource
   	//The index is resource id and select type joined by '_'
   	foreach ($rows as $row) {
   		$index = $row->resid . '_' . $row->type;
   		$res[$row->resid][$index][] = $row;
   	}
   	return $res;
	}

	/*
	 * Get unit_selection model on the input parameters.
	 *
	 * @param int $unitid
	 * 	The unitid number.
	 * @param int $type
	 * 	The type number.
	 *
	 * @return array
	 * 	Returns a object .
	 */
    function get_unit_selection($unitid, $type) {
    	if ( !is_numeric($unitid) || !is_numeric($type))
    		return FALSE;
    	$table_unit_selection = $this->db->dbprefix('unit_selection');

    	$this->db->select('*');
    	$this->db->from($table_unit_selection);
    	$this->db->where('unitid', $unitid);
    	$this->db->where('type', $type);
    	$res = $this->db->get();
    	if ($res->num_rows() <= 0)
    		return FALSE;
    	return $res->result();
    }

	/*
	 * Get strand_bench model on the input parameters.
	 *
	 * @param int $unitid
	 * 	The unitid number.
	 * @param int $type
	 * 	The type number.
	 *
	 * @return array
	 * 	Returns a object .
	 */
	function get_strand_bench($unitid, $type) {
		if ( !is_numeric($unitid) || !is_numeric($type) ) {
			return FALSE;
		}
		$table_unit_target = $this->db->dbprefix('unit_target');
		$table_target_text = $this->db->dbprefix('target_text');
		$table_benchmark = $this->db->dbprefix('benchmark');
		$table_standard = $this->db->dbprefix('standard');
		$table_strand = $this->db->dbprefix('strand');

		$this->db->select('b.*,s.data as standard,str.id as strandid,str.data as strand');
		$this->db->from($table_unit_target . ' u');
		$this->db->join($table_target_text . ' t', 'u.targetid=t.id', 'left');
		$this->db->join($table_benchmark . ' b', 't.benchmarkid=b.id', 'left');
		$this->db->join($table_standard . ' s', 'b.standardid=s.id', 'left');
		$this->db->join($table_strand . ' str', 's.strandid=str.id', 'left');
		$this->db->where('u.unitid', $unitid);
		$this->db->where('t.type', $type);
		$this->db->group_by('s.id,b.id');
		$this->db->order_by('str.id,s.strandid,s.id,b.id', 'asc');
		$res = $this->db->get();
		if ( $res->num_rows() <= 0 )
    		return FALSE;
    	$rows = $res->result();
    	return $rows;
	}


	/*
	 * Get learning_targets model on the input parameters.
	 *
	 * @param int $unitid
	 * 	The unitid number.
	 * @param int $type
	 * 	The type number .
	 * @param array  $wherein
	 * 	The wherein array.
	 *
	 * @return array
	 * 	Returns a object .
	 */
	function get_learning_targets($unitid, $type, $wherein=FALSE) {
		if ( !is_numeric($unitid) || !is_numeric($type) ) {
			return FALSE;
		}
		$table_unit_target = $this->db->dbprefix('unit_target');
		$table_target_text = $this->db->dbprefix('target_text');
		$table_res_sort    = $this->db->dbprefix('res_sort');
		$table_course_sis = $this->db->dbprefix('course_sis');
		$table_department = $this->db->dbprefix('department');

		$select  = '';
		$from    = '';
		$join    = '';
		$where   = '';
		$orderby = '';
		$select .= "d.id departmentid,c.name coursename,case when r.sortkey is null then ";
		$select .= "ifnull( (select max(sortkey)+1 from $table_res_sort where type=".$type." and fk1=$unitid group by fk1, type ), 0 ) ";
		$select .= "else r.sortkey end as 'sortmaxid', ";
		$select .= 'r.sortkey realsortkey, ';
		$select .= 't.id, t.benchmarkid, t.courseid, t.select1, t.data, case when t.type ='.$type.' then '.$type.' else t.type end as "type", ';
		$select .= 'u.unitid ';
		$from   = "$table_target_text as t";
		$join   .= "right join $table_unit_target as u on t.id=u.targetid ";
		$join   .= "left  join $table_res_sort as r on r.fk3=t.id and r.fk1=u.unitid";
		$join   .= " left join $table_course_sis as c on c.id=t.courseid  ";
		$join   .= " left join $table_department as d on d.id=c.departmentid  ";
		$where  .= "u.unitid=$unitid ";
		$where  .= "and t.type= ". $type . " ";


		if ($wherein != FALSE && is_array($wherein)) {
			$in = implode(',', $wherein);
			$where  .= "and t.id in ($in) ";
		}
		$orderby.= 'sortmaxid asc, r.sortkey asc ';
		$query   = "select $select from $from $join where $where order by $orderby";
		$res = $this->db->query( $query );

		if ( $res->num_rows() <= 0 )
    		return FALSE;
    	$rows = $res->result();
    	return $rows;
	}

	/*
	 * Get itstandards model on the input parameters.
	 *
	 * @param int $unitid
	 * 	The unitid number.
	 * @param int $type
	 * 	The type number .
	 * @param array  $wherein
	 * 	The wherein array.
	 *
	 * @return array
	 * 	Returns a object .
	 */
	function get_itstandards($unitid, $type, $wherein=FALSE) {
		if ( !is_numeric($unitid) || !is_numeric($type) )
			return FALSE;
		$select  = '';
		$from    = '';
		$join    = '';
		$where   = '';
		$orderby = '';

		$table_unit_target = $this->db->dbprefix('unit_target');
		$table_target_text = $this->db->dbprefix('target_text');
		$table_benchmark   = $this->db->dbprefix('benchmark');
		$table_standard    = $this->db->dbprefix('standard');
		$table_strand      = $this->db->dbprefix('strand');
		$table_res_sort    = $this->db->dbprefix('res_sort');

   	$select .= "case when r.sortkey is null then ";
		$select .= "ifnull( (select max(sortkey)+1 from $table_res_sort where type=".$type." and fk1=$unitid group by fk1, type ), 0 ) ";
		$select .= "else r.sortkey end as 'sortmaxid', ";
		$select .= 'r.sortkey realsortkey, ';
		$select .= 't.id, case when t.type ='.$type.' then '.$type.' else t.type end as "type", ';
		$select .= 'd.id strandid, d.data strand, d.schoolid, ';
		$select .= 's.id standardid, s.data standard, ';
		$select .= 'b.id benchmarkid, b.data benchmark, ';
		$select .= 'u.targetid, u.unitid, u.select1, u.select2 ';

		$from   = "$table_unit_target as u ";

		$join   .= "left join $table_res_sort    as r on r.fk3=u.targetid and r.fk1=u.unitid ";
		$join   .= "left join $table_target_text as t on t.id=u.targetid and t.benchmarkid=u.benchmarkid and t.type=u.target_type ";
		$join   .= "left join $table_benchmark   as b on b.id=t.benchmarkid and b.type=t.type ";
		$join   .= "left join $table_standard    as s on s.id=b.standardid and s.type=b.type ";
		$join   .= "left join $table_strand      as d on d.id=s.strandid and d.type=s.type ";

		$where  .= "u.unitid=$unitid ";
		$where  .= "and d.type=". $type . " ";

		if ($wherein != FALSE && is_array($wherein) && count($wherein) > 0) {
			$in = implode(',', $wherein);
			$where  .= "and t.id in ($in) ";
		}

		$orderby.= 's.strandid, sortmaxid asc, r.sortkey asc ';

		$query   = "select $select from $from $join where $where order by $orderby";

		$res = $this->db->query( $query );

		if ( $res->num_rows() <= 0 )
    		return FALSE;
    	$rows = $res->result();
    	return $rows;
	}

	/*
	 * Get outcomes_skills model on the input parameters.
	 *
	 * @param int $unitid
	 * 	The unitid number.
	 * @param int $type
	 * 	The type number .
	 *
	 * @return array
	 * 	Returns a object .
	 */
	function get_outcomes_skills($unitid, $type) {
		if ( !is_numeric($unitid) || !is_numeric($type) )
			return FALSE;
		$select  = '';
		$from    = '';
		$join    = '';
		$where   = '';
		$orderby = '';

		$table_unit_target = $this->db->dbprefix('unit_target');
		$table_standard    = $this->db->dbprefix('standard');
		$table_strand      = $this->db->dbprefix('strand');
		$table_benchmark   = $this->db->dbprefix('benchmark');
		$table_target_text = $this->db->dbprefix('target_text');
		$table_res_sort    = $this->db->dbprefix('res_sort');
		$table_course_sis = $this->db->dbprefix('course_sis');

		$select .= "case when c.name  is null then concat(s.data,' - ',d.data,'') else c.name end as coursename, ";
		$select .= 't.id, t.type, ';
		$select .= 'b.data outcomes, b.id outcomesid, ';
		$select .= 'case when b.courseid is null then concat(s.id,d.id)  else b.courseid end as courseid, ';
		$select .= 't.data skills, t.id skillsid, t.id , u.unitid ';

		$from   = "$table_strand as s ";
	   $join   .= "left join $table_standard    as d on d.strandid=s.id     and d.type= $type ";
		$join   .= "left join $table_benchmark   as b on b.standardid=d.id   and b.type= $type ";
		$join   .= "left join $table_target_text as t on t.benchmarkid=b.id  and t.type= $type ";
		$join   .= "left join $table_unit_target as u on u.targetid=t.id     and u.target_type= $type and  u.benchmarkid=t.benchmarkid and u.unitid=$unitid ";
		$join   .= "left join $table_course_sis as c on c.id=b.courseid  ";
		$where  .= " s.type=". $type . " ";
		$where  .= "and u.unitid = $unitid ";
		$orderby.= 'coursename,c.id,b.data, b.id,  t.data, t.id  ';
		$query   = "select $select from $from $join where $where order by $orderby ";
		$res = $this->db->query( $query );
		if ( $res->num_rows() <= 0 )
    		 return FALSE;
    	$rows = $res->result();
    	if ( $res->num_rows() == 1 && $rows[0]->id == null)
    	 	 return FALSE;
    	return $rows;
	}

}