<?php
/**
 * user_model - Model for add/update/delete user
 *
 * @author $Author: dtong $
 * @version $Id: admin_course_model.php,v 1.20 2011/05/09 07:48:52 dtong Exp $
 * @copyright Copyright (c) 2009, Tiller Software Co., Ltd.
*/

class admin_course_model extends CI_Model {
	function __construct() {
		parent::__construct();
    }

	 function get_allcourse($course_enabled,$filter=FALSE) {
	 	$user_table = $this->db->dbprefix('user');
	 	$uc_table = $this->db->dbprefix('user_course');
   	$course_table = $this->db->dbprefix('course_sis');
   	$dept_table = $this->db->dbprefix('department');
   	$grade_table = $this->db->dbprefix('grade');

		$sql  = 'select c.id ,c.sis_course_number,c.sis_course_dbid,c.name,c.autogen,c.enabled,gmin.name mingrade, ' .
				  		'gmax.name maxgrade,d.name department,d.enabled department_enabled,' .
						'u.firstname,u.lastname,u.middlename,u.displayname,u.id userid,uc.iseditor ' .
	    		  "from $course_table c " .
	    		  "left join $dept_table d on c.departmentid=d.id " .
	    		  "left join $grade_table gmin on c.mingradeid=gmin.id " .
	    		  "left join $grade_table gmax on c.maxgradeid=gmax.id " .
				  "left join $uc_table uc on uc.courseid=c.id " .
				  "left join $user_table u on u.id=uc.userid and u.enabled=" . CMS_DB_BOOLEAN_TRUE . ' ';
	    //$where = " where c.id <>''";
	    $where_cond = '';
		 $and = '';
		 if ($course_enabled!=CMS_ADMIN_ALL) {
	    	if ( empty($course_enabled) ) {
	    		$where_cond .= '(c.enabled=' . CMS_DB_BOOLEAN_FALSE . ' or d.enabled=' . CMS_DB_BOOLEAN_FALSE . ')';
	    	}
	    	else {
				$where_cond .= 'c.enabled=' . CMS_DB_BOOLEAN_TRUE . ' and d.enabled=' . CMS_DB_BOOLEAN_TRUE;
	    	}
	    	$and = 'and';
	    }
	    if ($filter!=FALSE) {
	    	$where_cond .= " $and c.name like '%$filter%'";
	    }
	    //$order_by = ' order by c.name';
		if ( $where_cond ) {
			$sql .= " where $where_cond ";
	    }
		$sql .= 'order by uc.iseditor desc,u.firstname,u.lastname';
	   $res = $this->db->query($sql);

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

		return $res->result();
	}

	function get_course($courseid, $course_enabled=1, $coursename=FALSE) {
		if ( !is_numeric($courseid) )
			return FALSE;

		$this->db->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,cs.tree_sort,gmin.sort gmin,gmax.sort gmax ');
	    $this->db->from('course_sis cs');
	    $this->db->join('grade gmin', 'cs.mingradeid=gmin.id', 'left');
	    $this->db->join('grade gmax', 'cs.maxgradeid=gmax.id', 'left');
	    $this->db->where('cs.id',$courseid);
		$res = $this->db->get();
		if ( $res->num_rows() != 1 )
			return FALSE;
		return $res->result();
	}

	//function get_user_course($courseid, $iseditor=FALSE) {
	function get_user_course($courseid) {
		if ( !osa_is_int($courseid) ) {
			return FALSE;
		}
		$this->db->select('u.id,u.displayname,u.firstname,u.lastname,uc.iseditor');
	   $this->db->from('user_course uc');
	   $this->db->join('user u', 'uc.userid=u.id', 'left');
	   $this->db->where('uc.courseid',$courseid);
	   /*
	   if ( $iseditor ) {
	   	$this->db->where('uc.iseditor', CMS_DB_BOOLEAN_TRUE);
	   }
	   else {
	   	$this->db->where('uc.iseditor', CMS_DB_BOOLEAN_FALSE);
	   }*/
	   //$this->db->where('u.enabled', CMS_DB_BOOLEAN_TRUE);
	   $this->db->order_by('u.displayname,u.firstname,u.lastname');
		$res = $this->db->get();
		if ( $res->num_rows()<= 0 ) {
			return FALSE;
		}
		return $res->result();
	}

	function get_course_grade($courseid) {
		if ( !is_numeric($courseid) )
			return FALSE;
		$this->db->select('cg.courseid,cg.gradeid id,cs.name cousername,g.name name');
	    $this->db->from('course_grade cg');
	    $this->db->join('course_sis cs', 'cg.courseid=cs.id', 'left');
	    $this->db->join('grade g', 'cg.gradeid=g.id', 'left');
	    $this->db->where('cg.courseid',$courseid);
	     $this->db->order_by('cg.courseid');
		$res = $this->db->get();
		if ( $res->num_rows()<= 0 )
			return FALSE;
		return $res->result();
	}

	function get_user($notin_ids=FALSE) {
		$this->db->select('id,displayname,firstname,lastname');
	   $this->db->from('user');
		$this->db->where('enabled', CMS_DB_BOOLEAN_TRUE);
	   if (!empty($notin_ids) && is_array($notin_ids)) {
	    	$this->db->where_not_in('id', $notin_ids);
	   }
		$this->db->where_not_in('username', array($this->config->item('cms_admin_username'),$this->config->item('cms_guest_username')));
	   //$this->db->order_by('displayname,firstname,lastname');
	   $res = $this->db->get();
		if ( $res->num_rows() <= 0 ) {
			return FALSE;
		}
		$user = $res->result();
		$result = array();
		foreach($user as $value) {
			//$displayname = osa_user_getname($value);
			$result[$value->id] = osa_user_getname($value);;
		}
		natcasesort($result);
		return $result;
	}

	function get_gradebyin($gmin,$gmax,$notin_ids=FALSE) {
		if (!is_numeric($gmin) || !is_numeric($gmax))
			return false;
		$sql ="select id, name,sort";
		$sql .=" from grade";
		//$this->db->select('id, name,sort');
	   // $this->db->from('grade');
	    //$this->db->order_by('id');
	    $sql .=" where (sort between $gmin and $gmax )";
	   	if ($notin_ids != FALSE && isset($notin_ids)!=FALSE && is_array($notin_ids)) {
	   		$id ='';
	   		$sep='';
	   		foreach($notin_ids as $value) {
	   			$id .=$sep.$value;
	   			$sep=',';
	   		}
	    $sql .=" and id not in ($id) ";

	   	}

	    $sql .=" order by sort";

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

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


		return $res->result();
	}
	function get_department() {
		$this->db->select('id, name ');
	    $this->db->from('department');
	    $this->db->where('enabled',CMS_ADMIN_ENABLE);
	    $this->db->order_by('name');
	    $res = $this->db->get();
		if ( $res->num_rows() <= 0 )
			return FALSE;

		$deps = $res->result();
		$result = array();
		$result[0] = lang('admin_department_ddldefualt');
		foreach($deps as $value) {
			$result[$value->id] = $value->name;
		}
		return $result;

	}

	function get_grade() {
		$this->db->select('id, name ');
	    $this->db->from('grade');
	    $this->db->order_by('sort');
	    $res = $this->db->get();
		if ( $res->num_rows() <= 0 )
			return FALSE;
		$accs = $res->result();
		$result = array();
		$result[0] = lang('admin_grade_ddldefualt');
		foreach($accs as $value) {
			$result[$value->id] = $value->name;
		}
		return $result;

	}

	function add_course($data) {
		$this->db->insert('course_sis', $data);
		return TRUE;
	}

    function update_course($courseid, $data) {
		$this->db->where('id',$courseid);
		$this->db->update('course_sis', $data);
		return TRUE;
	}

	function insert_user_course($courseid, $data, $iseditor=FALSE) {
		if ( !osa_is_int($courseid) ) {
			return FALSE;
		}
		if ( $iseditor ) {
			$iseditor = CMS_DB_BOOLEAN_TRUE;
		}
		else {
			$iseditor = CMS_DB_BOOLEAN_FALSE;
		}
		$user_course = $this->db->dbprefix('user_course');
      $sql = "INSERT INTO $user_course (userid,courseid,iseditor,is_from_sis) VALUES ";
      $sql_data = '';
      $sep = '';
      $ids = '';
      foreach ($data as $row) {
      	$userid = & $row['userid'];
      	$ids .= "{$sep}{$userid}";
         $sql_data .= $sep."($userid,$courseid,$iseditor," . CMS_DB_BOOLEAN_FALSE . ")";
         $sep = ',';
      }
      if ( $sql_data != '' ) {
	      if ( $iseditor ==  CMS_DB_BOOLEAN_TRUE ) {
	      	//We can assign read-only teachers to be editor so better delete all before inserting
	      	$this->del_user_courseall($courseid, $ids);
	      }
	      return $this->db->query($sql . $sql_data );
	      }
      return TRUE;
	}

	function insert_course_grade($courseid, $grade) {
		$user_course = $this->db->dbprefix('course_grade');
		$sql = "INSERT INTO $user_course (courseid,gradeid) VALUES ";
		$sql_data = '';

  		$sep = ',';
		$en = count($grade);
		for ($i=0;$i< $en;$i++) {
			$gradeid   = $grade[$i];
			if ($i==$en-1)
			$sep = '';
			$sql_data .= "($courseid,$gradeid)".$sep;
		}
		$this->del_course_grade($courseid);
		if ($en > 0 )
			$this->db->query($sql . $sql_data );
		return TRUE;
	}

	function del_user_courseall($courseid, $ids=FALSE, $iseditor=FALSE) {
		$this->db->where('courseid', $courseid);
		if ( $iseditor !== FALSE && ( $iseditor == CMS_DB_BOOLEAN_FALSE || $iseditor == CMS_DB_BOOLEAN_TRUE) ) {
			$this->db->where_in('iseditor', $iseditor);
		}
		if ( $ids ) {
			$this->db->where_in('userid', $ids);
		}
		return $this->db->delete('user_course');
	}

   function del_user_course($courseid,$userid) {
		$user_course = $this->db->dbprefix('user_course');
		$sql="delete from $user_course";
		$sql.=" where courseid=$courseid and userid=$userid ";
	    $this->db->query($sql);
		return TRUE;
	}
   function del_course_grade($courseid) {
		$user_course = $this->db->dbprefix('course_grade');
		$sql="delete from $user_course";
		$sql.=" where courseid=$courseid";
	    $this->db->query($sql);
		return TRUE;
	}
	function del_course($courseid,$course_enabled) {
		$this->db->where('id',$courseid);
		$this->db->update('course_sis', array('enabled'=>$course_enabled));
		return TRUE;
	}
	function get_gradebysort() {
		$this->db->select('id, sort ');
	    $this->db->from('grade');
	    $this->db->order_by('sort');
	    $res = $this->db->get();
		if ( $res->num_rows() <= 0 )
			return FALSE;
		$grade = $res->result();
		$result = array();
		foreach($grade as $value) {
			$result[$value->id] = $value->sort;
		}
		return $result;

	}



}
?>