<?php
/**
 * standbench_model.php - Model file for standbench search. Model for "Show me the Benchmarks and Learning Targets from"
 * 							  search in big picture. Does not use units so no need to add the unit query and use course query is fine.
 *
 * @author $Author: dtong $
 * @version $Id: standbench_model.php,v 1.19 2011/02/11 09:09:24 dtong Exp $
 * @copyright Copyright (c) 2009, Tiller Software Co., Ltd.
*/

class Standbench_model extends CI_Model {

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

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

	function get_query($course_query, $standards , $benchmarks, $grades , $sort=FALSE ) {
		$standard_ids = '';
		$benchmark_ids = '';
		$and_where = ' where';

		$standard_sql='';
		if ( $standards ) {
			 $standard_ids  = osa_getarrayvalue($standards);
			 if ( $standard_ids != '') {
			 	  $standard_sql = $and_where." s.id = $standard_ids " ;
			 	  $and_where = ' and ';
			 }
		}

		$benchmark_sql='';
		if ( $benchmarks ) {
			 $benchmark_ids  = osa_getarrayvalue($benchmarks);
			 if ( $benchmark_ids != '') {
			 	  $benchmark_sql = $and_where." b.id = $benchmark_ids " ;
			 	  $and_where = ' and ';
			 }
		}

		$grade_table       = $this->db->dbprefix('grade');
		$strand_table      = $this->db->dbprefix('strand');
		$standard_table    = $this->db->dbprefix('standard');
		$benchmark_table   = $this->db->dbprefix('benchmark');
		$target_text_table = $this->db->dbprefix('target_text');
		$grade_course_table = $this->db->dbprefix(CMS_GRADE_TABLE);

		$sql = '';
 		$field = "g.id gradeid, g.name grade_name, " .
 				 "c.id courseid, c.name course_name, " .
 		 		 "s.id standardid, s.data standard, " .
 				 "b.id benchmarkid, b.data benchmark, ".
 				 "t.id targetid, t.data target " ;
 				// "cg.courseid, cg.gradeid ";

		$where = "$and_where t.type=".CMS_LEARNING_TARGETS_TYPE;
		if (($where_grade=$this->get_where_in('cg.gradeid',$grades)) != FALSE)
			$where .= $where_grade;

		$sql = "select $field " .
			   "from $strand_table d " .
	    	   "join $standard_table s on s.strandid=d.id  " .
	    	   "join $benchmark_table b on b.standardid=s.id  " .
	    	   "join $target_text_table t on t.benchmarkid=b.id  " .
				"join ($course_query) c on t.courseid=c.id " .
		      "left join $grade_course_table cg on cg.courseid=c.id " .
		 	   "join $grade_table g on g.id=cg.gradeid " .
	    	   "$standard_sql  " .
	    	   "$benchmark_sql  " .
	    	   "$where " .
				"and d.enabled=" . CMS_DB_BOOLEAN_TRUE . " " .
				"and s.enabled=" . CMS_DB_BOOLEAN_TRUE . " " .
				"and b.enabled=" . CMS_DB_BOOLEAN_TRUE . " " ;
		$sql .= ' group by g.id, t.data';
		$sort_def = "g.sort,c.id,s.data,b.data,t.data";
		 if ( $sort ) {
		 	 $sql  .= " order by $sort, $sort_def";
		 } else {
		 	  $sql  .= " order by $sort_def";
		 }

		return $sql;
	}

	function get_query_old($course_query, $standards , $benchmarks, $grades , $sort=FALSE ) {
		$standard_ids = '';
		$benchmark_ids = '';
		$and_where = ' where';

		$standard_sql='';
		if ( $standards ) {
			 $standard_ids  = osa_getarrayvalue($standards);
			 if ( $standard_ids != '') {
			 	  $standard_sql = $and_where." s.id = $standard_ids " ;
			 	  $and_where = ' and ';
			 }
		}

		$benchmark_sql='';
		if ( $benchmarks ) {
			 $benchmark_ids  = osa_getarrayvalue($benchmarks);
			 if ( $benchmark_ids != '') {
			 	  $benchmark_sql = $and_where." b.id = $benchmark_ids " ;
			 	  $and_where = ' and ';
			 }
		}

		$grade_table       = $this->db->dbprefix('grade');
		$strand_table      = $this->db->dbprefix('strand');
		$standard_table    = $this->db->dbprefix('standard');
		$benchmark_table   = $this->db->dbprefix('benchmark');
		$target_text_table = $this->db->dbprefix('target_text');

		$grade_course_table = $this->db->dbprefix(CMS_GRADE_TABLE);


		$sql = '';
 		$field = "g.id gradeid, g.name grade_name, " .
 		 		 "s.id standardid, s.data standard, " .
 				 "b.id benchmarkid, b.data benchmark, ".
 				 "t.id targetid, t.data target " ;
 				// "cg.courseid, cg.gradeid ";

		$where = "$and_where t.type=".CMS_LEARNING_TARGETS_TYPE;
		if (($where_grade=$this->get_where_in('cg.gradeid',$grades)) != FALSE)
			$where .= $where_grade;

		$sql = "select $field " .
			   "from $strand_table d " .
	    	   "left join $standard_table s on s.strandid=d.id  " .
	    	   "left join $benchmark_table b on b.standardid=s.id  " .
	    	   "left join $target_text_table t on t.benchmarkid=b.id  " .
		 	   "join ($course_query) c on t.courseid=c.id " .
		      "left join $grade_course_table cg on cg.courseid=c.id " .
		 	   "left join $grade_table g on g.id=cg.gradeid " .
	    	   "$standard_sql  " .
	    	   "$benchmark_sql  " .
	    	   "$where " ;
		$sql .= ' group by g.id, t.data';
		$sort_def = "g.sort,c.id,s.data,b.data,t.data";
		 if ( $sort ) {
		 	 $sql  .= " order by $sort, $sort_def";
		 } else {
		 	  $sql  .= " order by $sort_def";
		 }

		return $sql;
	}

	private function  get_where_in($field, $grades ,$and=TRUE) {
		$where_grade = '';
		$comms = '';
		$result = '';
		if ( $grades == FALSE || $grades == '' || $grades == ' ')
			 return FALSE;
		if ( !is_array($grades))
			 return FALSE;

		foreach ($grades as $grade) {
			$where_grade .= $comms . $grade;
			$comms = ',';
		}
		$where_and = '';
		if ($and)
			$where_and = 'and';

		if ($where_grade != '')
			$result = " $where_and $field in ($where_grade)";

		return $result;
	}
	// get select the standard
    function  get_standards($table , $id) {
    	//$strand_table = $this->db->dbprefix($table);

	    $this->db->select('s.id, s.data');
		$this->db->from('strand d');
		$this->db->join('standard s','s.strandid=d.id','left');
	    $this->db->where('d.departmentid',$id);
	    $this->db->where('d.enabled',CMS_DB_BOOLEAN_TRUE);
	    $this->db->where('s.enabled',CMS_DB_BOOLEAN_TRUE);
	    $this->db->order_by('s.data');
		$resobj = $this->db->get();
		$rowobj = $resobj->result();
	    return $rowobj = $resobj->result_array();  //return all value from table course_sis(All file)
    }

	// get select the benchmark
    function  get_benchmarks($table , $id) {
        $this->db->select('id, data');
		$this->db->from('benchmark');
	    $this->db->where('standardid',$id);
	    $this->db->order_by('data');
		$resobj = $this->db->get();
		$rowobj = $resobj->result();
	    return $rowobj = $resobj->result_array();  //return all value from table course_sis(All file)
    }

	// get title name
    function  get_titlename($table, $ids,$fieldname='data',$isArray=FALSE) {

	    $this->db->select('id, data');
		$this->db->from($table);	    	//'standard'
	    $this->db->where_in('id',$ids);
	    $res = $this->db->get();

		if ( $res->num_rows() <= 0 )
    		return FALSE;
    	if ($isArray==FALSE) {
    		$rows = $res->result();
    		return $rows[0]->$fieldname;
    	} else {
    		$rowobj = $res->result();
    		return $rowobj = $resobj->result_array();
    	}

    }
	// set title name
    function  set_titlename($datas,$isLang=TRUE,$prelang='map',$isNewLine=TRUE) {
		$pagetitle = '';
		$newline   = '';
		$getdata   = array();

		//example : $key is '_standard' , '_benchmark' from data array
    	foreach ($datas as $key => $value) {
    		if (count($getdata)==0) {
    			$getdata[$key] = $value;
    		} else if ($value) {
    		    $table = str_replace("_","",$key);  //not perfect
    			$getdata[$key]  = $this->get_titlename($table, $value);
    			$title = $getdata[$key];
    			if ($isLang && $title)
    				$pagetitle     .= $newline . lang($prelang.$key) .": " . $title  ;
    			else
    				$pagetitle     .= $newline . $title;

				if ($isNewLine)
    				$newline 	    = '<br />';
    		}
    	}

    	if ($pagetitle=='')
    		$pagetitle = $getdata['_pagetitle'];

    	$getdata['_pagetitle'] = $pagetitle;

    	return $getdata;
    }

    function onClick($data) {
    	if (!isset($data))
    		return '';

    	$url         = '"'. base_url() . $data['_url']. '"' ;
		$target_div  = '"' . $data['_div'] . '"';
		$formname 	 = '"' . $data['_formname'] . '"' ;

		$onClick 	 = "onChange='ajax_select(this, $url, $target_div, $formname );'";

    	return $onClick;
    }

}
?>