<?php
/**
 * menu_model.php - Model for menu tree.
 *
 * @author $Author: dtong $
 * @version $Id: menu_model.php,v 1.33 2011/03/02 06:55:52 dtong Exp $
 * @copyright Copyright (c) 2011, Tiller Software Co., Ltd.
*/

class Menu_model extends CI_Model{

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

	  //Used by my courses tree
	  function mycourses($userid) {
        $this->db->select('c.*, uc.iseditor');
        $this->db->from('user_course uc');
        $this->db->join('course_sis c', 'uc.courseid=c.id', 'left');
        $this->db->where('uc.userid',$userid);
        $this->db->where('c.enabled', CMS_DB_BOOLEAN_TRUE);
        $this->db->order_by('c.name', 'asc');
        $resobj = $this->db->get();
   	  return $resobj->result_array();
	  }
	//Used by department menu tree
	function getalldepartment() {
      	$this->db->select('*');
      	$this->db->from('department');
      	$this->db->where('enabled', CMS_DB_BOOLEAN_TRUE);
      	$this->db->order_by('name', 'asc');
       	$resobj = $this->db->get();
      	$rowobj = $resobj->result();
      	return $rowobj = $resobj->result_array(); // return value from table department (id,shortname)
	}

	function getdepartment_bycategory($categoryid) {
		if ( !is_numeric($categoryid) ) {
			return FALSE;
		}
		//Get the predefined departments if we have them
		$this->db->select('d.*');
		$this->db->from('department d');
		$this->db->join('category_department cd', 'd.id=cd.departmentid');
		$this->db->join('category c', 'cd.categoryid=c.id');
		$this->db->where('c.id', $categoryid);
		$this->db->where('d.enabled', CMS_DB_BOOLEAN_TRUE);
		$this->db->order_by('d.name');
		$resobj = $this->db->get();

		if ( $resobj->num_rows() <= 0 ) {
			//No defined departments so just get departments with valid courses (according to grade levels)
			$course_tab = $this->db->dbprefix('course_sis');
			$dept_tab = $this->db->dbprefix('department');
			$category_tab = $this->db->dbprefix('category');
			$grade_tab = $this->db->dbprefix('grade');
			$bool_true = CMS_DB_BOOLEAN_TRUE;
			if ( $this->config->item('cms_category_not_show_empty_departments') ) {
				//Show departments with courses only
				$sql = "SELECT d.* FROM $dept_tab d
							join $course_tab c on d.id=c.departmentid
							join $category_tab cat on cat.id=$categoryid
							join $grade_tab g1 on c.mingradeid=g1.id
							join $grade_tab g2 on c.maxgradeid=g2.id
							join $grade_tab g3 on cat.mingradeid=g3.id
							join $grade_tab g4 on cat.maxgradeid=g4.id
							where d.enabled=$bool_true and c.enabled=$bool_true and
									((g1.sort>=g3.sort and g1.sort<=g4.sort) or (g2.sort>=g3.sort and g2.sort<=g4.sort))
							group by d.id order by d.name";
			}
			else {
				//Show all departments regardless empty courses or not
				$sql = "SELECT d.* FROM $dept_tab d
							where d.enabled=$bool_true
							order by d.name";
			}
			$res = $this->db->query($sql);
			if ( $res->num_rows() <= 0 ) {
				return FALSE;
			}
			return $res->result_array();
		}

      $rowobj = $resobj->result();
      return $resobj->result_array();
	}

	function getallcategory() {
		//$res = $this->common_db_model->select('category');
		$query = $this->db->get_where('category', array('enabled'=>CMS_DB_BOOLEAN_TRUE));
		$res = $query->result();
		if ( $res ) {
			osa_array_natsort($res, array('sort','name'));
		}
		return $res;
	}

	//Get all the courses by department id
	function  getcourse($id) {
		if ( !is_numeric($id) ) {
			return FALSE;
		}
		$this->db->select('*');
		$this->db->from('course_sis');
		$this->db->where('departmentid',$id);
		$this->db->where('enabled', CMS_DB_BOOLEAN_TRUE);
		$this->db->order_by('name', 'asc');
	   $resobj = $this->db->get();
     	return $resobj->result_array();  //return all value from table course_sis(All file)
	}

	//Gets the course belonging to a category->department.
	//The grade levels in the category table affects what courses get displayed
	function getcourse_usecategory($departmentid, $categoryid) {
		if ( !is_numeric($departmentid) || !is_numeric($categoryid) ) {
			return FALSE;
		}
		$course_tab = $this->db->dbprefix('course_sis');
		$cat_dept_tab = $this->db->dbprefix('category_department');
		$category_tab = $this->db->dbprefix('category');
		$grade_tab = $this->db->dbprefix('grade');
		$sql = "select c.* from $course_tab c
				  join $category_tab cat on cat.id=$categoryid
				  join $grade_tab g1 on c.mingradeid=g1.id
				  join $grade_tab g2 on c.maxgradeid=g2.id
				  join $grade_tab g3 on cat.mingradeid=g3.id
				  join $grade_tab g4 on cat.maxgradeid=g4.id
				  where (c.departmentid=$departmentid and c.enabled=" . CMS_DB_BOOLEAN_TRUE . ") and
				  		  ((g1.sort>=g3.sort and g1.sort<=g4.sort) or (g2.sort>=g3.sort and g2.sort<=g4.sort))
				  order by name asc";
		//echo $sql;
		$res = $this->db->query($sql);
		if ( $res->num_rows() <= 0 )
			return FALSE;
		return $res->result_array();
	}

	// get all the grades
    function getgrade($gradeid=FALSE) {
	    	$table_grade = $this->db->dbprefix('grade');
	    	$select = 'select *';
	    	$from = ' from '.$table_grade;
			$where_in = '';
			if (is_array($gradeid)){
				$gradeid = implode(",",$gradeid);
				$where_in = ' where id in('.$gradeid.')';
			}
			$orderby = ' order by sort';
			$resobj = $this->db->query($select.$from.$where_in.$orderby);

			$rowobj = $resobj->result();
	     	return $rowobj = $resobj->result_array();  //return all value from table course_sis(All file)

	  }

	 //Used by grade menu tree
	 function  get_coursesbygrade($gradeid) {
			//This is for displaying courses selected by editor
			//$display_grade_table = 'display_grade';
			//This display all courses in that grade
			$display_grade_table = CMS_GRADE_TABLE;
	 		$this->db->select('c.*');
			$this->db->from('course_sis c');
			$this->db->join("$display_grade_table dg", "dg.courseid=c.id",'left');
			$this->db->where("dg.gradeid",$gradeid);
			$this->db->where('c.enabled', CMS_DB_BOOLEAN_TRUE);
			$this->db->order_by('c.name', 'asc');
			$resobj = $this->db->get();
		   $rowobj = $resobj->result();
	      return $rowobj=$resobj->result_array();
	 }

	function get_one_course($courseid) {
		$this->db->select('c.*,d.name as dept_name,d.shortname as dept_shortname');
		$this->db->from('course_sis c');
		$this->db->join('department d', 'c.departmentid=d.id', 'left');
		$this->db->where('c.id',$courseid);
		$this->db->where('c.enabled', CMS_DB_BOOLEAN_TRUE);
		$resobj = $this->db->get();
	   if ( $resobj->num_rows() <= 0 )
			return FALSE;
		$rows = $resobj->result_array();
		return $rows[0];
	}

	// get all the schools
    function  getschools() {
	    $this->db->select('*');
		$this->db->from('school');
		$this->db->order_by('id','asc');
		$resobj = $this->db->get();
		$rowobj = $resobj->result();
	    return $rowobj = $resobj->result_array();  //return all value from table course_sis(All file)
    }

	// get all the schools
    function  getstandards() {
	    $this->db->select('*');
		$this->db->from('standard');
		$this->db->order_by('id','asc');
		$resobj = $this->db->get();
		$rowobj = $resobj->result();
	    return $rowobj = $resobj->result_array();  //return all value from table course_sis(All file)
    }

// get all the schools
    function  getbenchmarks() {
	    $this->db->select('*');
		$this->db->from('benchmark');
		$this->db->order_by('id','asc');
		$resobj = $this->db->get();
		$rowobj = $resobj->result();
	    return $rowobj = $resobj->result_array();  //return all value from table course_sis(All file)
    }
	// get all the grade_type
    function  get_grade_type($type) {
    	$grade_type = $this->db->dbprefix('grade_type');
    	$school_type = $this->db->dbprefix('school_type');
	   $this->db->select('g.*,c.shortname');
		$this->db->from($grade_type.' g');
		$this->db->join($school_type.' c', 'c.id = g.schoolid', 'left');
		$this->db->where('g.type',$type);
		$this->db->group_by('g.schoolid');
		$this->db->order_by('g.sort','asc');
		$resobj = $this->db->get();
		$rowobj = $resobj->result();
	    return $rowobj = $resobj->result_array();  //return all value from table course_sis(All file)
    }
	function get_courses($gradeid) {
		$table_course = $this->db->dbprefix('course_sis');
    	$select = 'select id courseid,name course';
    	$from = ' from '.$table_course;
    	$where = ' where enabled='.CMS_ADMIN_ENABLE;
		if ($gradeid != FALSE){
			$gradeid = implode(",",$gradeid);
			$where .= ' and (mingradeid in('.$gradeid.') or  maxgradeid in('.$gradeid.'))';
		}

		$orderby = ' order by name';
		$rowobj = $this->db->query($select.$from.$where.$orderby);

		if ( $rowobj->num_rows() <= 0 )
			return FALSE;
		return $rowobj->result();
	}
	//Used by department menu tree pyp
	function get_departmentbygrade($gradeid){
		$table_department = $this->db->dbprefix('department d');
		$table_course = $this->db->dbprefix('course_sis c');
    	$select = 'select d.id,d.name';
    	$from = ' from '.$table_department ;
    	$join = ' left join '.$table_course .' on c.departmentid=d.id ' ;
    	$where = ' where d.enabled='.CMS_ADMIN_ENABLE;
		if ($gradeid != FALSE){
			$gradeid = implode(",",$gradeid);
			$where .= ' and (c.mingradeid in('.$gradeid.') or  c.maxgradeid in('.$gradeid.'))';
		}

		$groupby = ' group by d.id';
		$orderby = ' order by d.name';
		$rowobj = $this->db->query($select.$from.$join.$where.$groupby.$orderby);

     	if ( $rowobj->num_rows() <= 0 )
			return FALSE;
		return $rowobj->result_array();

	}
	// get selection_category
    function  get_sel_cat($type, $split=false, $valueindex=0) {
    	$table_selection_category = $this->db->dbprefix('selection_category');
	   $this->db->select('id , text');
		$this->db->from($table_selection_category);
		$this->db->where('type',$type);
		$this->db->order_by('text');
		$resobj = $this->db->get();

		$result = array();
		if ( $resobj->num_rows() <= 0 )
			return $result;
		foreach ($resobj->result() as $value){
			if ($split != false) {
				//$data = split($split, $value->text);
				$data = explode($split, $value->text);
				if (is_array($data) && array_key_exists($valueindex, $data)) {
					$tmp = trim($data[$valueindex]);
					$result[$tmp] = $tmp;
				}
			}
			else
				$result[$value->id] = $value->text;
		}
	   return $result;
    }
    // get selection_category
    function  get_sel_cat_like($text,$split=false, $valueindex=0) {
    	$table_selection_category = $this->db->dbprefix('selection_category');
		$sql = " select id ,text " ;
		$sql .= " from ".$table_selection_category;
		$sql .= " where text like '%{$text}%'";
		$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;
    }
}
?>