<?php
/**
 * FILE_NAME_HERE - FILE_DESCRIPTION_HERE
 *
 * @author $Author: dtong $
 * @version $Id: itstd_model.php,v 1.30 2011/05/09 11:59:59 dtong Exp $
 * @copyright Copyright (c) 2009, Tiller Software Co., Ltd.
*/

class Itstd_model extends CI_Model {

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

	/*
	 * This is the learning targets section on the unit page
	 */
	function get_targets($unitid, $type) {
		if ( !is_numeric($unitid) || !is_numeric($type) ) {
			return FALSE;
		}

		$this->db->select('b.data,t.id,t.benchmarkid,t.type,t.select1,t.select2');
		$this->db->from('unit_target as u');
	    $this->db->join('target_text as t',"t.benchmarkid=u.benchmarkid and t.type=u.target_type", 'left');
	    $this->db->join('benchmark as b',"b.id=t.benchmarkid and b.type=t.type", 'left');
	   // $this->db->where('u.unitid', $unitid);
		$this->db->where('t.type', $type);
		$res = $this->db->get();
		if ( $res->num_rows() <= 0 )
    		return FALSE;
    	$rows = $res->result();

    	return $rows;
	}


	/*
	 * Gets strands, standards, benchmarks selected for a particular unit. This involves 5 tables
	 * This is the ITstd select popup
	 */
	function get_strand_tree($unitid, $type) {
		if ( !is_numeric($unitid) || !is_numeric($type) ) {
			return FALSE;
		}
		//$this->db->join('unit_target as u', "u.benchmarkid=t.benchmarkid and u.targetid=t.id and u.target_type=t.type", 'left');
		$this->db->select('b.id as benchmarkid, t.id as targetid, u.select1, u.select2');
		$this->db->from('unit_target as u');
		$this->db->join('target_text as t', 'u.benchmarkid=t.benchmarkid and u.targetid=t.id and u.target_type=t.type', 'left');
		$this->db->join('benchmark as b', 't.benchmarkid=b.id', 'left');
		$this->db->join('standard as s', 'b.standardid=s.id', 'left');
		$this->db->join('strand as d', 's.strandid=d.id', 'left');
		$this->db->where('u.unitid', $unitid);
		$this->db->where('d.type', $type);
		$res = $this->db->get();

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

	/*
	 * Gets strands, standards, benchmarks selected for a particular unit. This involves 5 tables
	 * This is the ITstd select popup
	 */
	/*
	function get_strand_tree_render($unitid, $type) {
		if ( !is_numeric($unitid) || !is_numeric($type) ) {
			return FALSE;
		}

		$this->db->select('d.id as strandid, d.data as strand, d.schoolid, s.id as standardid, s.data as standard, b.id as benchmarkid, b.data as benchmark,u.targetid,u.unitid,u.select1,u.select2');
		$this->db->from('unit_target as u');
		$this->db->join('target_text as t', 't.id=u.targetid and t.benchmarkid=u.benchmarkid and t.type=u.target_type', 'left');
		$this->db->join('benchmark as b', 'b.id=t.benchmarkid', 'left');
		$this->db->join('standard as s', 's.id=b.standardid', 'left');
		$this->db->join('strand as d', 'd.id=s.strandid', 'left');
		$this->db->where('u.unitid', $unitid);
		$this->db->where('d.type', $type);
		$this->db->order_by('schoolid', 'asc');
		//$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;
	} */

	//Copied and modified from d*** sort_model
	function get_strand_tree_render($unitid, $type) {
		if ( !osa_is_int_one($unitid) || !osa_is_int_one($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');

		$fields = 't.id, t.type, d.id strandid, d.data strand, d.schoolid,' .
					 's.id standardid, s.data standard, b.id benchmarkid, b.data benchmark,' .
					 'u.targetid, u.unitid, u.select1, u.select2,' .
					 'd.importid strand_importid,s.importid standard_importid,b.importid benchmark_importid ';
		$table = "$table_unit_target u";
		$joins = "join $table_target_text as t on u.unitid={$unitid} and u.target_type={$type} and t.id=u.targetid " .
					"join $table_benchmark as b on b.id=t.benchmarkid " .
					"join $table_standard as s on s.id=b.standardid " .
					"join $table_strand as d on d.id=s.strandid";
		$query = "select $fields from $table $joins";

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

		if ( $res->num_rows() <= 0 ) {
	    	return FALSE;
		}
	   $rows = $res->result();
		$sortarray = array('schoolid', 'strand', 'strandid', 'standard', 'standardid', 'benchmark');
	   if ( $this->config->item('cms_lt_importid_support') ) {
	   	$sortarray = array('schoolid', 'strand_importid', 'strand', 'standard_importid', 'standard', 'benchmark_importid', 'benchmark');
	    	foreach ($rows as $record) {
	    		if ( !empty($record->strand_importid) ) {
					$record->strand = "$record->strand_importid $record->strand";
	    		}
	    		if ( !empty($record->standard_importid) ) {
					$record->standard = "$record->standard_importid $record->standard";
	    		}
	    		if ( !empty($record->benchmark_importid) ) {
					$record->benchmark = "$record->benchmark_importid $record->benchmark";
	    		}
	    	}
	   }
		if ( !osa_array_natsort($rows, $sortarray) ) {
			osa_log(__METHOD__, 'Failed to run natsort.', $sortarray, $rows);
			return NULL;
		}
		return $rows;
	}

	function update($unitid, $type, $select1, $select2) {

		$selectedids = $this->get_targets_byunit($unitid,$type);

		$updateids = array();
		$insertids1 = array();
		$insertids2 = array();

		$deleteids1 = array();
		$deleteids2 = array();

		$updateids1 = array();
		$updateids2 = array();

		if ($selectedids) {
	    	foreach ($selectedids as $selection) {
	    		if ($selection->select1)
	    		     $deleteids1[$selection->benchmarkid]= $selection;
	    		if ($selection->select2)
	    		     $deleteids2[$selection->benchmarkid]= $selection;
				if ( array_key_exists($selection->targetid, $select1)  )
					 $updateids1[$selection->benchmarkid]= $selection;
	    		if ( array_key_exists($selection->targetid, $select2)  )
					 $updateids2[$selection->benchmarkid]= $selection;
	    	}
		}

	    foreach ($select1 as $targetid =>$benchid) {
	    	if ( array_key_exists($benchid, $deleteids1) ) {
	    	  	unset($deleteids1[$benchid]);
	    	} else if ( array_key_exists($benchid, $updateids1) ) {
	    	  	//unset($deleteids1[$benchid]);
	    	} else {
	   	 		$insertids1[$benchid] = $targetid;
	   		}
	    }

		foreach ($select2 as $targetid =>$benchid) {
	    	if ( array_key_exists($benchid, $deleteids2) ) {
	    	  	unset($deleteids2[$benchid]);
	    	} else if ( array_key_exists($benchid, $updateids2) ) {

	    	} else {
	   	 		$insertids2[$benchid] = $targetid;
	   		}
	    }

	    $table = $this->db->dbprefix('unit_target');

		$values_arr  = array();

		foreach ($insertids1 as $benchid => $targetid) {
			$values_arr[$targetid] = "($unitid,$targetid,$benchid,$type,1,0)"; ;
   		}

		foreach ($insertids2 as $benchid => $targetid) {
			if (array_key_exists ($targetid, $values_arr ))
				$values_arr[$targetid] = "($unitid,$targetid,$benchid,$type,1,1)";
			else
				$values_arr[$targetid] = "($unitid,$targetid,$benchid,$type,0,1)"; ;
   		}

   		$sep=''; 	$values = '';
   		foreach ($values_arr as $targetid => $item) {
   			$values .= $sep . $item;
   			$sep = ',';
   		}

   		$sql_insert='';
		if ( $values != '' ) {
    		$sql_insert .= "insert into $table (unitid,targetid,benchmarkid,target_type,select1,select2) values $values ";
   		}

   		$sep=''; 			$updids1 = '';
		foreach ($updateids1 as $benchid => $unit_target) {
			if  ($unit_target->select1!=1) {
	   			 $updids1 .= $sep .$unit_target->targetid;
	   			 $sep = ',';
			}
   		}
		$sep=''; 			$updids2 = '';
		foreach ($updateids2 as $benchid => $unit_target) {
			if  ($unit_target->select2!=1) {
   			     $updids2 .= $sep .$unit_target->targetid;
   			     $sep = ',';
			}
   		}

		$sql_update1 ='';
   		$sql_update2 ='';

   	//fix by tha
   	//update only unitid for keep version
   	//add where unitid = $unitid
		if ( $updids1 != '' ) {
			$sql_update1 = "update $table set select1=1 where unitid = $unitid and targetid in ($updids1) ";
			//$sql_update1 = "update $table set select1=1 where targetid in ($updids1) ";
   		}

		if ( $updids2 != '' ) {
			$sql_update2 = "update $table set select2=1 where unitid = $unitid and targetid in ($updids2) ";
			//$sql_update2 = "update $table set select2=1 where targetid in ($updids2) ";
   		}

   		$sep=''; 			$delids1 = '';
   		foreach ($deleteids1 as $benchid => $unit_target) {
   			//$unit_target->select1 = 0;
   			$delids1 .= $sep .$unit_target->targetid;
   			$sep = ',';
   		}
		$sep=''; 			$delids2 = '';
		foreach ($deleteids2 as $benchid => $unit_target) {
			//$unit_target->select2 = 0;
   			$delids2 .= $sep .$unit_target->targetid;
   			$sep = ',';
   		}
   		$sql_remove1 ='';
   		$sql_remove2 ='';
		if ( $delids1 != '' ) {
			$sql_remove1 = "update $table set select1=0 where unitid = $unitid and targetid in ($delids1) ";
			//$sql_remove1 = "update $table set select1=0 where targetid in ($delids1) ";
    		//$sql_update = "delete from $table where unitid = $unitid and targetid in ($delids1)";
   		}

		if ( $delids2 != '' ) {
			$sql_remove2 = "update $table set select2=0 where unitid = $unitid and targetid in ($delids2) ";
			//$sql_remove2 = "update $table set select2=0 where targetid in ($delids2) ";
    		// $sql_update = "delete from $table where unitid = $unitid and targetid in ($delids2)";
   		}

   	$sql_delete_zero = "delete from $table where unitid = $unitid and select1=0 and select2=0 ";
		//$sql_delete_zero = "delete from $table where select1=0 and select2=0 ";

    	$this->db->trans_start();
		if ( $sql_update1 != '' ) {
    			$this->db->query($sql_update1);
    	}
		if ( $sql_update2 != '' ) {
    			$this->db->query($sql_update2);
    	}

		if ( $sql_remove1 != '' ) {
    			$this->db->query($sql_remove1);
    	}
		if ( $sql_remove2 != '' ) {
    			$this->db->query($sql_remove2);
    	}
		if ( $sql_delete_zero != '' ) {
    			$this->db->query($sql_delete_zero);
    	}
		if ( $sql_insert != '' ) {
    			$this->db->query($sql_insert);
    	}
    	$this->db->trans_complete();
   		return $this->db->trans_status();
	}

	function delete($unitid,$targetid) {
		if ( !is_numeric($unitid) || !is_numeric($targetid))
	    	return FALSE;
	    $this->db->where('unitid', $unitid);
		$this->db->where('targetid', $targetid);
		return $this->db->delete('unit_target');
	}

	function get_targets_byunit($unitid,$type) {
		if ( !is_numeric($type) )
	    	return FALSE;
	   	$this->db->select('u.benchmarkid,u.targetid,u.select1,u.select2');
		$this->db->from('unit_target as u');
	    $this->db->join('target_text as t',"t.benchmarkid=u.benchmarkid and t.type=u.target_type", 'left');
	    $this->db->join('benchmark as b',"b.id=t.benchmarkid and b.type=t.type", 'left');

	    $this->db->where('u.unitid', $unitid);
		$this->db->where('t.type', $type);

		$res = $this->db->get();
		if ( $res->num_rows() <= 0 )
    		return FALSE;

    	$rows = $res->result();
    	return $rows;
	}

/*
	 * Get Reverse unit Linking
	 * For the it standard popup
	 */
 	function get_reverse_link_bycourse($unitid, $courseid, $type, $oriids) {
 		$result = array();

		if (!is_numeric($unitid) || !is_numeric($courseid) || !is_numeric($type))
	    	return $result;

	 //   if (!isset($rowids) || !is_array($rowids) || !isset($oriids) || !is_array($oriids))
	    if (!isset($oriids) || !is_array($oriids))
	    	return $result;
//xxx
	 	$unit_target = $this->get_unit_targets_bytype($type, $oriids);
	 	$unitids = array();

	 	if (!isset($unit_target) || !is_array($unit_target) )
	 		return $result;

 		foreach ( $unit_target as $rows ) {
 			 if (!in_array($rows->unitid , $unitids))
	 			 $unitids[]=$rows->unitid;
	 	}

	 	$units_title = $this->get_title_byarray($unitids);

 	    if ( $units_title == FALSE ) {
	   		return $result;
	 	}

 		foreach ( $unit_target as $row ) {
			$data = $row;
			if (array_key_exists($row->unitid,$units_title))  {
				$data->title = $units_title[$row->unitid];
				$result[$row->benchmarkid][] = $data;
			}
	 	}
    	return $result;
	}

	/*
	 * Get a target from target_text by id
	 */
	function get_target_byid($targetid) {
		if ( !is_numeric($targetid) )
	    	return FALSE;
		$this->db->where('id', $targetid);
		$res = $this->db->get('target_text');
		if ( $res->num_rows() <= 0 )
    		return FALSE;
    	$rows = $res->result();
    	return $rows[0];
	}

	/*
	 * Delete a target_text record by id
	 */
	function delete_targettext($targetid) {
		if ( !is_numeric($targetid) )
	    	return FALSE;
		$this->db->trans_start();
			$this->db->where('targetid', $targetid);
			$this->db->delete('unit_target');
			$this->db->where('id', $targetid);
			$this->db->delete('target_text');
		$this->db->trans_complete();
    	return $this->db->trans_status();
	}

	/*
	 * Update/Add target_text records from the LT edit popup page
	 */
	function update_targets($courseid, $benchmarkid, $type, $curlt, $ids, $newlt) {
		if (!is_numeric($courseid) || !is_numeric($benchmarkid) || !is_array($curlt) ||
			 !is_array($newlt) || !is_array($ids) || !is_numeric($type))
			return FALSE;
		//Lets check to make sure all the ids belong to the same course
		if ( count($ids) >0 ) {
			$this->db->where_in('id', $ids);
			$this->db->where_not_in('courseid', array($courseid));
			$res = $this->db->get('target_text');
			if ($res->num_rows() > 0)
				return FALSE;
		}

		$table = $this->db->dbprefix('target_text');
		$sqladd = '';
		$sqlupdate = '';
		if ( count($curlt) > 0 ) {
			//Build multiple s in one single sql statement
			$idstr = osa_getarrayvalue($ids);
			$sqlupdate = "update $table set data=case id ";
			//update test set data = case id when 1 then '1A' when 2 then '2A' else data end where id in (1,2);
			foreach ($curlt as $lt) {
				$sqlupdate .= " when {$lt['id']} then '{$lt['data']}'";
			}
			$sqlupdate .= " else data end,";
			$sqlupdate .= " select1=case id ";
			foreach ($curlt as $lt) {
				$value=$lt['select'];
				if (is_null($value))
					$value='NULL';
				$sqlupdate .= " when {$lt['id']} then {$value}";
			}
			$sqlupdate .= " else select1 end where id in ({$idstr}) and benchmarkid=$benchmarkid";
		}
		if ( count($newlt) > 0 ) {
			//Build multiple inserts in one single SQL statement
			$sqladd = "insert into $table (benchmarkid,courseid,type,select1,data) values ";
			$sep='';
			foreach ($newlt as $lt) {
				$value=$lt['select'];
				if (is_null($value))
					$value='NULL';
				$sqladd .= "{$sep}($benchmarkid,$courseid,$type,{$value},'{$lt['data']}')";
				$sep=',';
			}
		}

		$this->db->trans_start();
			if ($sqlupdate != '')
				$this->db->query($sqlupdate);
			if ($sqladd != '')
				$this->db->query($sqladd);
		$this->db->trans_complete();
    	return $this->db->trans_status();
	}

	/*
    * Get all the uploads for a resource type in a unit
    * Group the uploads by resourceid in a 2-d array
    */
   function get_uploads_targetids($type) {
   	if (!is_numeric($type) )
   		return FALSE;

   		$table_upload = 'upload';
   		$table_resource = 'resource';
   		$table_resource_link = 'resource_link';
   		$table_resource_select = 'resource_select';
   		$intype = CMS_LIB_TYPE . ',' . CMS_TEC_TYPE;

   		$sub_sql = 'select resourceid, sum(type) libict ' .
						  "from $table_resource_select " .
						  "where type in ($intype) " .
						  'group by resourceid ';

   		$select = 'select l.resourceid as selid, l.pid1 as targetid, ' .
   					 //'r.id as resid, r.type as restype,r.ispublic,r.url1,r.text1 as text1,'.
   					 'r.id as resid, r.type as restype,r.ispublic,r.text1,'.
   					 'u.id as uploadid,u.size,u.filename, '.
   					 's.libict';
   		$from = "from $table_upload u ";
   		$join = "right join $table_resource r on r.id=u.resourceid " .
   				  "right join $table_resource_link l on l.resourceid=r.id ".
   				  "left join ($sub_sql) s on s.resourceid=r.id ";
   		$where  = "where r.type = $type ";
   		$orderby = 'order by l.pid1, r.id, u.filename asc';

   		$sql = "$select $from $join $where $orderby";
   		//echo $sql;
   		$res = $this->db->query($sql);
//	$this->db->query($sql_update2);
/*
   		$this->db->select('l.resourceid as selid, l.pid1 as targetid, r.id as resid, r.type as restype,"$TYPE5" as text1,r.ispublic,r.url1,u.id as uploadid,u.size,u.filename');
   		$this->db->from('upload as u');
   		$this->db->join('resource as r', 'r.id=u.resourceid', 'right');
   		$this->db->join('resource_link as l', 'l.resourceid=r.id', 'right');
   		$this->db->where('r.type', $type);
	   	$this->db->order_by('l.pid1, r.id, u.filename', 'asc');
	   	*/
	   	//$res = $this->db->get();
	   	if ( $res->num_rows() <= 0 )
	   		return FALSE;
	   	$rows = $res->result();
	   	$retarray = array();

	   	$last_resid ='';
	   	//Build the data structure in a 2-d array
	   	foreach ($rows as $row) {
	   		if (!array_key_exists($row->targetid, $retarray))    {
	   			//$row->targetid is resource_link.pid1, don't pid2 is used anywhere
	   			//resource_link.pid1 keeps track what resource is assigned to a ICT benchmarkid
	   			$retarray[$row->targetid] = array();
	   		}

	   		$retarray[$row->targetid][] = $row;
	   	}
	   	return $retarray;
   }

   //Used by it standand view
   function get_department($deptid)
		{
      	$this->db->select('name');
      	$this->db->from('department');
      	$this->db->where('id', $deptid);
      	$resobj = $this->db->get();
		if ( $resobj->num_rows() <= 0 )
			return '';
		$rows = $resobj->result();
		return $rows[0]->name;

	}


    /*
	 * Get schools_by brade
	 * For the it standard popup
	 */
 	 function get_schools_bygrade($courseid,$ismingrade) {
		if (!is_numeric($courseid))
	    	return FALSE;
		$grade_table = $this->db->dbprefix('grade');
		$school_table = $this->db->dbprefix('school');

	   $this->db->select('n.id as unitid,n.title,c.mingradeid,c.maxgradeid,c.id as courseid, s.id as schoolid');
		$this->db->from('unit as n');
		$this->db->join('course_sis as c',"c.id=n.courseid", 'left');
		if ($ismingrade || $ismingrade==1) {
			$this->db->join($grade_table.' as g', "g.id=c.mingradeid", 'left');
		} else {
			$this->db->join($grade_table.' as g', "g.id=c.maxgradeid", 'left');
		}
		$this->db->join($school_table.' as s', "s.id=g.schoolid", 'left');
		$this->db->where('c.id', $courseid);

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

 	function merge_schools($schools_min, $schools_max) {
		$schools_all =  array();

		if ( is_array($schools_min) ) {
			foreach ($schools_min as $data) {
				if (!in_array($data->schoolid,$schools_all))
					$schools_all[] = $data->schoolid;
			}
		}

		if ( is_array($schools_max) ) {
			foreach ($schools_max as $data) {
				if (!in_array($data->schoolid,$schools_all))
					$schools_all[] = $data->schoolid;
			}
		}

		return  $schools_all;
 	}
	/*
	 * Gets standards, benchmarks, target text for a course
	 * This is the standards and benchmarks section on unit page
	 */
	function get_selections_byschool($type, $schools_all) {
		if ( !is_numeric($type) || !is_array($schools_all) )
			return FALSE;

		$this->db->select('d.id strandid, d.data strand, d.schoolid, s.id standardid, s.data standard,
								 b.id benchmarkid, b.data benchmark,t.id ,b.data, t.select1, t.select2, d.departmentid,
								 d.importid strand_importid,s.importid standard_importid,b.importid benchmark_importid');
		$this->db->from('strand as d');
		$this->db->join('standard as s', "s.strandid=d.id and s.type=d.type", 'right');
		$this->db->join('benchmark as b', "b.standardid=s.id and b.type=s.type", 'right');
		$this->db->join('target_text as t', "t.benchmarkid=b.id and t.type=b.type", 'left');
		//$this->db->join('unit_target as u', "u.benchmarkid=t.benchmarkid and u.target_type=t.type", 'left');

		$this->db->where('d.type', $type);
		//where flag enabled=1
		$this->db->where('d.enabled', CMS_ADMIN_ENABLE);
		$this->db->where('s.enabled', CMS_ADMIN_ENABLE);
		$this->db->where('b.enabled', CMS_ADMIN_ENABLE);
		$this->db->where_in('d.schoolid', $schools_all);
		if ( $this->config->item('cms_lt_importid_support') ) {
			//$this->db->order_by('d.schoolid,d.departmentid,d.importid_int,s.importid_int,b.importid_int', 'asc');
			$sort_array = array('schoolid', 'departmentid', 'strand_importid', 'standard_importid', 'benchmark_importid');
		}
		else {
			//$this->db->order_by('d.schoolid,d.departmentid,d.id,s.id,b.id', 'asc');
			$sort_array = array('schoolid', 'departmentid', 'strand', 'strandid', 'standard', 'standardid', 'benchmark', 'benchmarkid');
		}
		$res = $this->db->get();
		if ( $res->num_rows() <= 0 )
    		return FALSE;
    	$rows = $res->result();
    	if ( count($rows) > 1 ) {
    		osa_array_natsort($rows, $sort_array);
    	}
    	if ( $this->config->item('cms_lt_importid_support') ) {
    		foreach ($rows as $record) {
    			if ( !empty($record->strand_importid) ) {
    				$record->strand = "{$record->strand_importid} {$record->strand}";
    			}
    			if ( !empty($record->standard_importid) ) {
    				$record->standard = "{$record->standard_importid} {$record->standard}";
    			}
    			if ( !empty($record->benchmark_importid) ) {
    				$record->benchmark = "{$record->benchmark_importid} {$record->benchmark}";
    			}
    		}
    	}
    	return $rows;
	}


	/*
	 * Get all the targets belonging to a benchmark
	 * For the LT edit popup
	 */
 	function get_targets_byschools($type, $schools_all) {
 		if ( !is_numeric($type) || !is_array($schools_all) )
			return FALSE;
		//var_dump($schools_all);

		$this->db->select('b.data,t.id,t.benchmarkid,t.type,t.select1,t.select2');
		$this->db->from('strand as d');
		$this->db->join('standard as s', "s.strandid=d.id and s.type=d.type", 'right');
		$this->db->join('benchmark as b', "b.standardid=s.id and b.type=s.type", 'right');
		$this->db->join('target_text as t', "t.benchmarkid=b.id and t.type=b.type", 'left');
	//	$this->db->join('unit_target as u', "u.benchmarkid=t.benchmarkid and u.targetid=t.id and u.target_type=t.type", 'left');
	    $this->db->where('t.type', $type);
	    //where flag enabled=1
	    $this->db->where('d.enabled', CMS_ADMIN_ENABLE);
		$this->db->where('s.enabled', CMS_ADMIN_ENABLE);
		$this->db->where('b.enabled', CMS_ADMIN_ENABLE);
	    $this->db->where_in('d.schoolid', $schools_all);
	    $this->db->order_by('d.schoolid,d.id,s.id,b.id', 'asc');

		$res = $this->db->get();
		if ( $res->num_rows() <= 0 )
    		return FALSE;
    	$rows = $res->result();
       // var_dump($rows);
    	return $rows;
	}
	//xxxx
	function get_unit_targets_bytype($type, $benchmarkids) {
		if ( !is_numeric($type) )
	    	return FALSE;
		//$this->db->select('*');

		$table_unit_target = $this->db->dbprefix('unit_target');

		$lctlib = 'concat(unitid,"_",IFNULL(select1,0)+(IFNULL(select2, 0)*2)) code, IFNULL(select1,0)+(IFNULL(select2, 0)*2) ictlib';
		//$this->db->select('unitid, targetid, benchmarkid, target_type, select1, select2, ' . $lctlib);
		//$this->db->from('unit_target');
		//$this->db->where('target_type', $type);
		$select = 'select unitid, targetid, benchmarkid, target_type, select1, select2, '.$lctlib;
		$from = "from $table_unit_target";
		$where = "where target_type=$type";
		if (isset($benchmarkids) && is_array($benchmarkids))
			$where .= ' and benchmarkid in ('.implode(',',$benchmarkids).')';
			//$this->db->where_in('benchmarkid', $benchmarkids);

		///$this->db->order_by('benchmarkid', 'asc');
		$orderby = 'order by benchmarkid asc';
		$sql = "$select $from $where $orderby";
		$res = $this->db->query($sql);
		//$res = $this->db->get();
		if ( $res->num_rows() <= 0 )
    		return FALSE;
    	$rows = $res->result();

		//echo '<pre>';		var_dump($rows); echo '</pre>';
    	return $rows;
	}

	/*
	 * Get unit title by array of unit id
	 */
 	function get_title_byarray($unitids, $status=CMS_UNIT_STATUS_PUBLISH) {
		if (!is_numeric($status))
	    	return FALSE;

	    $this->db->select('id as unitid, courseid, currentunitid, title, type, status');
		$this->db->from('unit as n');
     	$this->db->where('n.status',$status);
     	if (isset($unitids) && is_array($unitids))
     		$this->db->where_in('id', $unitids);

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

    	$result = array();
    	foreach ($rows as $row){
			$result[$row->unitid] = $row->title;
    	}

    	return $result;
 	}

	// get all the schools
    function  get_all_schools($key='id', $toArray=FALSE,$temp=FALSE) {
    	$school_table = $this->db->dbprefix('school');
    	if ($temp != FALSE)
    		$school_table = $this->db->dbprefix($temp['_school']);

	   $this->db->select('*');
		$this->db->from($school_table);
		$this->db->order_by('id','asc');
		$resobj = $this->db->get();

		if ( $resobj->num_rows() < 1 )
     	 	return FALSE;

		$result = $resobj->result();

 		if ($key != '' && $toArray)
 			return $this->toArray($result, $key);
 		else
 			return $resobj->result_array();
 		//	return $result;

	      //return all value from table course_sis(All file)
    }

	function toArray($rows, $key='id') {
    	if (!is_array($rows))
    		return FALSE;

    	$data = array();
		foreach ($rows as $row) {
			$data[$row->$key] = $row;
		}
		return $data;
    }

 	/*
    * Get all the uploads for a resource type in a unit
    * Group the uploads by resourceid in a 2-d array
    */
   function get_resource_select($resourceid) {
   	if (!is_numeric($type) )
   		return FALSE;

   		$this->db->select('s.resourceid, s.type, s.choice ');
   		$this->db->from('resource_select as s');
   		$this->db->order_by('s.s.resourceid');
	   	//$this->db->order_by('l.pid1, r.id, u.filename', 'asc');
	   	$res = $this->db->get();
	   	if ( $res->num_rows() <= 0 )
	   		return FALSE;
	   	$rows = $res->result();
	   	// var_dump($rows);
	   	return $rows;
   }

	/*
    * Get all the uploads for a resource type in a unit
    * Group the uploads by resourceid in a 2-d array
   */
   function update_target_select($update, $type) {
   	//if (!is_numeric($type) )
   	//	return FALSE;
	   	if (!is_array($update) )
				 return FALSE;

			// 1,T
			// 2,L
			// 3,TL
			$table = $this->db->dbprefix('target_text');
			$sql = '';
			$sqls = '';
			//echo $table;

			//$this->db->trans_start();
	   	foreach($update as $select => $ids) {
				//echo $id . ':' .$value . '|';
				//$this->itstd_model->update_target_select($id, $value);
			//	$sql = "update $table set select3=$select where id in ($ids) and type = $type ";
				//$sqls  .= $sql;
   		//	$res = $this->db->query($sql);
			}
			//
	    	//$this->db->trans_complete();
	    	//$this->db->trans_status();
	    	//return $sqls;
   		//return $this->db->trans_status();

   		/*
   		$this->db->select('s.resourceid, s.type, s.choice ');
   		$this->db->from('resource_select as s');
   		$this->db->order_by('s.s.resourceid');
	   	//$this->db->order_by('l.pid1, r.id, u.filename', 'asc');
	   	$res = $this->db->get();
	   	if ( $res->num_rows() <= 0 )
	   		return FALSE;
	   	$rows = $res->result();
	   	 var_dump($rows);
	   	 */
	   	//return $rows;
   }

   function get_notin_select($type, $ids, $notin) {
   	if (!is_numeric($type) )
   		return FALSE;

   	if ($notin == '' || $ids == '')
   		return FALSE;

   		$table_target_text = $this->db->dbprefix('target_text');

   		$sub_sql = 'select id, select1, select2, '.
   					  'concat(id,"_",IFNULL(select1,0)+(IFNULL(select2, 0)*2)) code, IFNULL(select1,0)+(IFNULL(select2, 0)*2) ictlib '.
						  "from $table_target_text " .
						  "where type = $type and id in ($ids) " .
						  'order by id ';

   		$select = 'select code, id, select1, select2' ;
   		$from = "from ( $sub_sql ) s";
   		$where  = "where not s.code in ($notin)";

   		$sql = "$select $from $where";
   		//echo $sql;
   		$res = $this->db->query($sql);

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

	   	return $rows;
   }


   function update_select($type, $id, $value) {
   	if (!is_numeric($type) || !is_numeric($id))
   		return FALSE;

   	if ($value == '')
   		return FALSE;

   		$table_target_text = $this->db->dbprefix('target_text');

   		$update = "update $table_target_text " ;
   		$set = "set $value ";
   		$where  = "where id = $id";

   		$sql = "$update $set $where";
	   	// echo $sql;
   	 	$this->db->trans_start();
    		$this->db->query($sql);
    		$this->db->trans_complete();
   		return $this->db->trans_status();
   }
	function get_grade_bycourse($courseid, $temp=FALSE) {
		if ( !is_numeric($courseid) )
			return FALSE;
		$grade_table = $this->db->dbprefix('grade');
		$course_table = $this->db->dbprefix('course_sis');

		$sub_grade = '';
		if ($temp != FALSE) {
			$grade_table = $this->db->dbprefix($temp['_grade']);

			$sub_grade = ' ( select g.id, g.sort '.
							 ' from '.$grade_table.' gt  '.
							 ' left join grade g on g.sort = gt.sort '.
							 ' where gt.type='.$temp['_typeid'].' ) ';
		}
		else
			$sub_grade = $grade_table;

		$select = 'select cs.id, cs.mingradeid, cs.maxgradeid, cs.departmentid, cs.modifydate, cs.enabled, ' .
				 'cs.autogen, cs.createdate, cs.modifydate, cs.sis_course_number, cs.sis_course_dbid, cs.name, ' .
				 'cs.url_key, cs.description, cs.shortname, ' .
		 		 'gmin.sort gmin, gmax.sort gmax  ';
		$from = " from {$course_table} cs ";
		$join = " left join {$sub_grade} gmin on cs.mingradeid=gmin.id ".
				  " left join {$sub_grade} gmax on cs.maxgradeid=gmax.id ";
		$where = " where cs.id = {$courseid} ";

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

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

		if ( $res->num_rows() != 1 )
			return FALSE;
		$rows = $res->result();
		return $rows[0];
 	}


	function get_grade_type($template, $min_grade, $max_grade) {
		if (!is_numeric($min_grade) || !is_numeric($max_grade))
	    	return FALSE;

	   $grade_table = $this->db->dbprefix('grade');
		$type = FALSE;
	   if ($template != FALSE){
	   	$grade_table = $this->db->dbprefix($template['_grade']);
   		$type = $this->db->dbprefix($template['_typeid']);
	   }

	   $select = 'select g.schoolid';
	   $from = " from {$grade_table} g";
	   $where = "where g.sort between {$min_grade}  and {$max_grade} " ;
	   if ($type != FALSE)
	   	$where .= " and g.type = {$type} " ;
		$groupby = " group by g.schoolid ";

	   $sql = "$select $from  $where $groupby ";
   	$res = $this->db->query($sql);

		if ( $res->num_rows() <= 0 )
    		return FALSE;
    	$rows = 	$res->result();
  	 	if ( is_array($rows) ) {
			foreach ($rows as $data) {
					$schools_all[] = $data->schoolid;
			}
		}
    	return $schools_all;
  	 }

}
?>