<?php
/**
 * course_model.php - Course model file
 *
 * @author $Author: dtong $
 * @version $Id: course_model.php,v 1.44 2011/05/09 04:48:53 dtong Exp $
 * @copyright Copyright (c) 2009, Tiller Software Co., Ltd.
*/

class Course_model extends CI_Model{
	//This does our own caching
	static private $is_mycourse=array();
	static private $is_editor=array();
	private $display_grade_tab = '';

	function __construct() {
		parent::__construct();
		$this->display_grade_tab = $this->config->item('cms_gradetable_2save');
	}

	function  get_desc($courseid) {
		/*
		$this->db->select('course_description.*,course_sis.name as coursename');
		$this->db->from('course_description');
		$this->db->join('course_sis', 'course_description.courseid=course_sis.id');
		$this->db->where('courseid',$courseid);
		*/
		$this->db->select('course_description.*'); //,department.name as dept_name,department.shortname as dept_shortname');
		$this->db->from('course_description');
		//$this->db->join('course_sis', 'course_description.courseid=course_sis.id', 'left');
		//$this->db->join('department', 'course_sis.departmentid=department.id', 'left');
		$this->db->where('course_description.courseid',$courseid);
		$resobj = $this->db->get();
		if ( $resobj->num_rows() <= 0 )
			return FALSE;
		$rows = $resobj->result();
		return $rows[0];
	}

	function  get_descbyid($descid) {
		$this->db->select('*'); //,department.name as dept_name,department.shortname as dept_shortname');
		$this->db->from('course_description');
		$this->db->where('id',$descid);
		$resobj = $this->db->get();
		if ( $resobj->num_rows() <= 0 )
			return FALSE;
		$rows = $resobj->result();
		return $rows[0];
	}

	function get_course($courseid) {
		$this->db->select('course_sis.*,department.name as dept_name,department.shortname as dept_shortname,gmin.sort gmin,gmax.sort gmax');
		$this->db->from('course_sis');
		$this->db->join('department', 'course_sis.departmentid=department.id', 'left');
		 $this->db->join('grade gmin', 'course_sis.mingradeid=gmin.id', 'left');
	    $this->db->join('grade gmax', 'course_sis.maxgradeid=gmax.id', 'left');
		$this->db->where('course_sis.id',$courseid);
		$resobj = $this->db->get();
	   if ( $resobj->num_rows() <= 0 )
			return FALSE;
		$rows = $resobj->result();
		return $rows[0];
	}

	/*
	 * Check to see is the user an editor in the supplied course
	 * This does its own caching so welcome to call it as many times as you want
	 * TODO: Change this to use cms_cache
	 */
	function isCourseEditor($userid=FALSE, $courseid) {
		if ( !is_numeric($courseid) )
			return FALSE;

		if ($userid === FALSE) {
			$user = $this->login_model->getLogin();
			if ( !$user )
				return FALSE;
			$userid=$user->id;
		}
		$index = "{$userid}_$courseid";
		if (array_key_exists($index, self::$is_editor)) {
			return self::$is_editor[$index];
		}

		$this->db->select('iseditor');
		$this->db->from('user_course');
		$this->db->where('userid',$userid);
		$this->db->where('courseid',$courseid);
		$this->db->where('iseditor', '1');
	   $resobj = $this->db->get();
	   //$rowobj = $resobj->result();
	   if ($resobj->num_rows() > 0) {
	   	self::$is_editor[$index] = TRUE;
	   	return TRUE;
	   }
	   self::$is_editor[$index] = FALSE;
	   return FALSE;
	}

	/*
	 * Check to see is the user a teacher in the supplied course
	 * This does its own caching so welcome to call it as many times as you want
	 */
	function isMyCourse($userid=FALSE, $courseid) {
		//echo "my courses *********************************";
		if ( !is_numeric($courseid) )
			return FALSE;

		if ($userid === FALSE) {
			$user = $this->login_model->getLogin();
			if ( !$user )
				return FALSE;
			$userid=$user->id;
		}

		$index = "{$userid}_$courseid";

		if (array_key_exists($index, self::$is_mycourse)) {
			//echo "my courses";
			return self::$is_mycourse[$index];
		}

		$this->db->select('iseditor');
		$this->db->from('user_course');
		$this->db->where('userid',$userid);
		$this->db->where('courseid',$courseid);
	   $resobj = $this->db->get();
	   //$rowobj = $resobj->result();
	   if ($resobj->num_rows() > 0) {
	   	self::$is_mycourse[$index] = TRUE;
	   	return TRUE;
	   }
	   self::$is_mycourse[$index] = FALSE;
	   return FALSE;
	}

	function getEditorUsers($courseid,$iseditor=1) {
		$this->db->select('user.*');
		$this->db->from('user_course');
		$this->db->join('user', 'user_course.userid=user.id', 'left');
		$this->db->where('user_course.courseid',$courseid);
		if ($iseditor!==FALSE)
		$this->db->where('user_course.iseditor',$iseditor);
		$resobj = $this->db->get();
		if ( $resobj->num_rows() <= 0 )
			return FALSE;
		$rows = $resobj->result();
		return $rows;
	}

	//Get the grades by mingradeid and maxgradeid from the course id
	function get_grades_byrange($courseid) {
		$this->db->select('dg.courseid is_display,g2.*');
		$this->db->from('course_sis c');
		$this->db->join('grade g','g.id=c.mingradeid');
		$this->db->join('grade g1','g1.id=maxgradeid');
		$this->db->join('grade g2','g2.sort>=g.sort and g2.sort<=g1.sort');
		$this->db->join($this->display_grade_tab . ' dg',"dg.gradeid=g2.id and dg.courseid=$courseid",'left');
		$this->db->where('c.id',$courseid);
		$this->db->order_by('g2.sort');
		$resobj = $this->db->get();
		if ( $resobj->num_rows() <= 0 )
			return FALSE;
		return $resobj->result();
	}

	function get_grades($courseid) {
		//Always show grades based on mingrade and maxgrade
		if ( $this->config->item('cms_show_allgrades') )
			return $this->get_grades_byrange($courseid);
		$this->db->select('grade.*,dg.courseid as is_display');
		$this->db->from('course_grade');
		$this->db->join('grade','course_grade.gradeid=grade.id','left');
		$this->db->join($this->display_grade_tab . ' dg','course_grade.courseid = dg.courseid and course_grade.gradeid = dg.gradeid','left');
		$this->db->where('course_grade.courseid',$courseid);
		$this->db->order_by('grade.sort');
		$resobj = $this->db->get();
		if ( $resobj->num_rows() <= 0 ) {
			//return FALSE;
			//If no records in table course_grade then list by min and max grades
			return $this->get_grades_byrange($courseid);
		}
		return $resobj->result();
	 }

	 function get_displaygrades($courseid,$orderby=FALSE) {
	 	$this->db->select('gradeid');
	 	$this->db->from($this->display_grade_tab);
	 	$this->db->where('courseid', $courseid);
	 	if ($orderby!=FALSE)
	 	$this->db->order_by('gradeid','asc');
	 	$resobj = $this->db->get();
	 	if ( $resobj->num_rows() <= 0 )
	 		return FALSE;
	 	return $resobj->result();
	 }

	//Get users for select editor window
	/*
	function  listuser_all() {
 		$this->db->select('id,displayname,firstname,middlename,lastname');
		$this->db->from('user');
		$this->db->where_not_in('username', array($this->config->item('cms_admin_username'),$this->config->item('cms_guest_username')));
		$this->db->order_by('lastname,firstname,middlename');
 		$resobj = $this->db->get();
	   return $resobj->result();
	 }
*/
	function get_users_all() {
		$this->db->select('id,displayname,firstname,middlename,lastname');
		$this->db->from('user');
		$this->db->where_not_in('username', array($this->config->item('cms_admin_username'),$this->config->item('cms_guest_username')));
		$this->db->order_by('lastname,firstname,middlename');
 		$resobj = $this->db->get();
	   return $resobj->result();
	}

	//Gets users assigned to a course
	function  get_users($courseid) {
 		$this->db->select('id,displayname,firstname,middlename,lastname');
		$this->db->from('user');
		$this->db->join('user_course', 'user.id=user_course.userid', 'right');
		$this->db->where_not_in('username', array($this->config->item('cms_admin_username'),$this->config->item('cms_guest_username')));
		$this->db->where('user_course.courseid', $courseid);
		$this->db->order_by('lastname,firstname,middlename');
 		$resobj = $this->db->get();
	   return $resobj->result();
	 }

	function update_des($data, $courseid, $descid) {
		$this->db->where('id', $descid);
		//Use the course id so we are sure user has permission to write to desc
		//becuase check_access checks the courseid and not desc id
		$this->db->where('courseid', $courseid);
		return $this->db->update('course_description', $data);
	}

	function add_des($data) {
		return $this->db->insert('course_description', $data);
	}

	//I really don't like what I am doing here, but it is non-critical data and doesn't happen often
	//This should be able to support multiple grade levels already
	function update_displaygrade($courseid, $grades) {
		$gradestring = osa_getarrayvalue($grades);
		if ($gradestring == '')
			return false;
		foreach ($grades as $grade) {
			$this->db->select('*');
			$this->db->from($this->display_grade_tab);
			$this->db->where('courseid',$courseid);
			$this->db->where('gradeid', $grade);
			$resobj = $this->db->get();
			if ( $resobj->num_rows <= 0 )
				$this->db->insert($this->display_grade_tab, array('courseid'=>$courseid,'gradeid'=>$grade));
		}
		$this->db->where('courseid', $courseid);
		$this->db->where('gradeid not in (' . $gradestring . ')');
		$this->db->delete($this->display_grade_tab);
		return true;
	}

	function update_editor($courseid, $userid) {
		if (!is_numeric($courseid) || !is_numeric($userid))
			return;
		$this->db->select('*');
		$this->db->from('user_course');
		$this->db->where('userid', $userid);
		$this->db->where('courseid', $courseid);
		//$this->db->where('iseditor', '1');
		$resobj = $this->db->get();

		if ( $resobj->num_rows > 0 ) {
			$res = $resobj->result();
			$row = $res[0];
			if ( $row->iseditor ) //Already an editor so do nothing
				return TRUE;
		}
		else { //Have to add this user to the course, we are adding outside of the SIS, be careful here
			$this->db->insert('user_course', array('userid'=>$userid,'courseid'=>$courseid));
		}

		//if ( $resobj->num_rows <= 0 ) {
			$this->db->where('courseid', $courseid);
			$this->db->where('iseditor', '1');
			$this->db->update('user_course', array('iseditor'=>0));
			$this->db->where('courseid', $courseid);
			$this->db->where('userid', $userid);
			$this->db->update('user_course', array('iseditor'=>1));
		//}
		return TRUE;
	}

	function get_all_grades() {
		$res = $this->db->get('grade');
		if ( $res->num_rows <= 0 ) {
			return FALSE;
		}
		return $res->result();
	}
	function edit_showhide($courseid,$showhide) {
		if ( !is_numeric($courseid) && !is_numeric($showhide))
		return FALSE;
		$this->db->where('id', $courseid);
		$this->db->update('course_sis', array('hidden'=>$showhide));
		return TRUE;
	}
	function get_user($courseid) {
		//get getEditorUsers
		$in_editer = $this->getEditorUsers($courseid,0);
		if ( $in_editer !=FALSE ) {
		    foreach ($in_editer as $value) {
			   $in_ids[] = $value->id;
		     }
		}
		$notin_editer = $this->getEditorUsers($courseid,FALSE);
		if ( $notin_editer !=FALSE ) {
		    foreach ($notin_editer as $value) {
			   $notin_ids[] = $value->id;
		     }
		}
		if(isset($in_ids) && is_array($in_ids))
			$user_in = $this->user($in_ids);

		if(isset($notin_ids) && is_array($notin_ids)){
			$user_notin = $this->user($notin_ids,TRUE);
		}
		else{
			$user_notin = $this->user();
		}

		$result = array();
		$result['a'] =lang('desc_selected_course_editor');
		$result['b'] ='-----------------------';
		if (isset($user_in)&& is_array($in_ids)){
			foreach($user_in as $value) {
				$displayname = osa_user_getname($value);
				$result[$value->id] = $displayname;
			}
		}

		$result['c'] ='';
		$result['d'] =lang('desc_selected_teacher');
		$result['e'] ='-----------------------';
		if(isset($user_notin) && is_array($user_notin)){
			foreach($user_notin as $value) {
				$displayname = osa_user_getname($value);
				$result[$value->id] = $displayname;
			}
		}

		return $result;
	}
	private function user($notin_ids=FALSE,$flag=FALSE) {
		$this->db->select('id, displayname,firstname,lastname');
	    $this->db->from('user');
	   	if ($notin_ids != FALSE && isset($notin_ids)!=FALSE && is_array($notin_ids)){
	   		if ($flag==FALSE){
	   			$this->db->where_in('id', $notin_ids);
	   		}else{
	   			$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 array();

		return  $res->result();

	}
	function insert_course_editor($courseid, $data,$single=FALSE) {

		$user_course = $this->db->dbprefix('user_course');
        $sql = "INSERT INTO $user_course (userid, courseid, iseditor, is_from_sis) VALUES ";
        $sql_data = '';
          $sep = '';
          $skip_key = array('a','b','c','d','e');
          foreach ($data as $row) {
              $userid = $row['userid'];
              if ( isset($skip_key) && is_array($skip_key) && in_array($userid , $skip_key)==FALSE ) {
              	if ($single!==FALSE)
				$this->insert_course_editor_single($courseid);

              	$iseditor = $this->is_user_course($userid,$courseid);
              	if (isset($iseditor) && is_array($iseditor)){
              		$this->update_course_editor($courseid,$userid);
              	}else{
              		$sql_data .= $sep."($userid,$courseid, 1, 1)";
            		$sep = ',';
              	}
              }

          }
       if ($sql_data!='')
        $this->db->query($sql . $sql_data );
        return TRUE;
	}
	private function insert_course_editor_single($courseid) {
		$course_editer = $this->getEditorUsers($courseid);
		if (isset($course_editer) && is_array($course_editer)){
			$row = $course_editer[0];
			$this->update_course_editor($courseid,$row->id,0);
		}
		 return TRUE;
	}

	function update_course_editor($courseid,$userid,$iseditor=1) {
		$this->db->where('courseid',$courseid);
		$this->db->where('userid',$userid);
		$this->db->update('user_course', array('iseditor'=>$iseditor));
        return TRUE;
	}

	private function is_user_course($userid,$courseid) {
		$this->db->select('iseditor');
		$this->db->from('user_course');
		$this->db->where('userid',$userid);
		$this->db->where('courseid',$courseid);
		$resobj = $this->db->get();
		if ( $resobj->num_rows() <= 0 )
			return FALSE;

		return  $resobj->result();
	}

	function get_course_summary($courseid) {
		$this->db->select('c.name coursename,u.title,u.description,u.startdate,u.enddate,u.modifydate,us.displayname');
		$this->db->from('unit u');
		$this->db->join('course_sis c', 'u.courseid=c.id', 'left');
		$this->db->join('user us', 'u.lasteditorid=us.id', 'left');
		$this->db->where('u.courseid',$courseid);
		$this->db->where('u.status',CMS_UNIT_STATUS_PUBLISH);
		$this->db->order_by('u.startdate,u.enddate');
		//$this->db->where_in('u.status',array($this->STATUS_ARCHIVE,$this->$STATUS_CURRENT));
		$resobj = $this->db->get();
	    if ( $resobj->num_rows() <= 0 )
			return FALSE;
		return  $resobj->result();
	}

	//This should have the same result/logic as unit_total
	function get_unit_calendar($courseid) {
		//$this->db->select('u.id unitid,c.name coursename,u.title,u.description,u.startdate,u.enddate,u.modifydate,us.displayname');
		$this->db->select('u.id unitid,c.name coursename,u.title,u.description,u.startdate,u.enddate,u.modifydate');
		$this->db->from('unit u');
		$this->db->join('course_sis c', 'u.courseid=c.id', 'left');
		//$this->db->join('user us', 'u.lasteditorid=us.id', 'left');
		$this->db->where('u.courseid',$courseid);
		$this->db->where('u.status', CMS_UNIT_STATUS_PUBLISH);
		$this->db->where('u.hidden', CMS_DB_BOOLEAN_FALSE);
		$this->db->order_by('u.startdate,u.enddate,u.title');

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

	//This should have the same result/logic as unit_total
	function unit_total($courseid) {
		$this->db->select('count(u.id) unit_total');
		$this->db->from('unit u');
		$this->db->join('course_sis c', 'u.courseid=c.id', 'left');
		$this->db->where('u.courseid',$courseid);
		$this->db->where('u.status', CMS_UNIT_STATUS_PUBLISH);
		$this->db->where('u.hidden', CMS_DB_BOOLEAN_FALSE);
		$resobj = $this->db->get();
	   if ( $resobj->num_rows() <= 0 ) {
			return 0;
	   }
		$res = $resobj->result();
		$res = $res[0];
		return $res->unit_total;
	}

	//$courseid is an array, $desc_courseid is the current course
	function get_course_all($courseid, $desc_courseid, $use_target=FALSE, $not_in=FALSE) {
		if ( $not_in==FALSE && count($courseid)== 0 ) {
			//No selected courses so return empty array
			return array();
		}
		if ($use_target){
			$table = $this->db->dbprefix('target_text');
		}
		else{
			$table = $this->db->dbprefix('benchmark');
		}
		$this->db->select('c.id courseid,c.name coursename,c.departmentid');
		$this->db->from('course_sis c');
		if ($not_in==FALSE) {
			//This gets the selected course(s)
			//We do want to show disabled courses here
			$this->db->where_in('c.id',$courseid);
			$resobj = $this->db->get();
			$result = $resobj->result();
			osa_array_natsort($result, 'coursename');
		}
		else {
			$this->db->join("$table t", 't.courseid=c.id');
			//This gets the big list on the left
			//We don't want to show disabled courses here
			$this->db->where('c.id !=', $desc_courseid);
			$this->db->where('c.enabled', CMS_DB_BOOLEAN_TRUE);
			if (count($courseid)!= 0){
				$this->db->where_not_in('c.id', $courseid);
			}
			$this->db->group_by('c.id');
			$resobj = $this->db->get();
			$result = $resobj->result();
			$au = $resobj->result();
			osa_array_natsort($au, 'coursename');
			$result = array();
			foreach($au as $value) {
				$result[$value->courseid] = $value->coursename;
			}
		}
		return $result;
	}

	//Get all the fields plus course fields ot just the linked course ids.
	//Used by the os, lt controllers and all libraries
	function get_course_lt_link($courseid, $id_only=FALSE) {
		if ( !osa_is_int_one($courseid) ) {
			return FALSE;
		}

		if ( $id_only ) {
			$this->db->select('clt.lt_courseid');
		}
		else {
		$this->db->select('clt.courseid,clt.lt_courseid,clt.lt_departmentid,c.name coursename');
		}
		$this->db->from('course_lt_link clt');
		if ( !$id_only ) {
			//$this->db->join('course_sis c', 'c.id=clt.lt_courseid', 'left');
			$this->db->join('course_sis c', 'c.id=clt.lt_courseid');
		}
		$this->db->where('clt.courseid', $courseid);

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

	function insert_course_lt_link($courseid, $data) {
		if ( is_numeric($courseid) ) {
			if ( !empty($data) ) {
				$course_lt_link = $this->db->dbprefix('course_lt_link');
				$sql = "INSERT INTO $course_lt_link (courseid, lt_courseid, lt_departmentid) ";
				$sql_data = '';
				$sql_data = 'select '.$courseid.' courseid,id lt_courseid,departmentid lt_departmentid from course_sis';
				$sql_data .= ' where id in('.$data.')';
				if ( $this->del_course_lt_link($courseid) ) {
					return $this->db->query($sql . $sql_data );
				}
			}
			else {
				return $this->del_course_lt_link($courseid);
			}
		}
      return FALSE;
	}

	function del_course_lt_link($courseid) {
		if (is_numeric($courseid)){
			$this->db->where('courseid',$courseid);
			return $this->db->delete('course_lt_link');
		}
		return FALSE;
	}

	function get_gradebyin($gmin,$gmax,$notin_ids=FALSE) {
		if (!is_numeric($gmin) || !is_numeric($gmax))
			return false;

		$grade_table = $this->db->dbprefix('grade');
		$sql ="select id gradeid";
		$sql .=" from $grade_table";
	   $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();
	}
}
?>
