<?php
/**
 * calendar_model.php - Model file for calendar search
 *
 * @author $Author: dtong $
 * @version $Id: calendar_model.php,v 1.11 2011/02/11 09:09:24 dtong Exp $
 * @copyright Copyright (c) 2009, Tiller Software Co., Ltd.
*/

class Calendar_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($courses, $mycourses, $grades, $departments, $sort=FALSE, $monthfrom=FALSE, $yearfrom=FALSE,
						  $monthto=FALSE, $yearto=FALSE, $showunit=FALSE, $status_array=FALSE) {
		$course_ids = '';
		$grade_ids = '';
		$dept_ids = '';
		//$this->db->dbprefix('resource')
		/*if ($mycourses[0] == CMS_SELECT_ALL) {
			//No need to precess others becuase we are showing all courses
			$final_sql = "select id from $course_table where enabled=1";
		}
		else {*/
			//Get the course ids by combining courses and my courses
		$comma = '';
		if ( $courses ) {
			$course_ids .= osa_getarrayvalue($courses);
			$comma = ',';
		}
		if ( $mycourses ) {
			$course_ids .= $comma . osa_getarrayvalue($mycourses);
		}
		//Get the grades and departments
		if ( $grades ) {
			$grade_ids = osa_getarrayvalue($grades);
		}
		if ( $departments ) {
				$dept_ids = osa_getarrayvalue($departments);
		}
		//Let's figure out the grades and depts
		$grade_sql='';
		if ( $grade_ids != '') {
			/*
			if ( $usedisplaygrade )
				$grade_course_table = $this->db->dbprefix('display_grade');
			else
				$grade_course_table = $this->db->dbprefix('course_grade');
			*/
			$grade_course_table = $this->db->dbprefix(CMS_GRADE_TABLE);
			$grade_sql = "select distinct(courseid) from $grade_course_table where gradeid in ($grade_ids)";
		}

		$sql = '';
		$course_table = $this->db->dbprefix('course_sis');
		//This is used by all searches, ass more fields from course_sis table here if necessary
		$course_fields = 'id,name,departmentid ';
		if ($course_ids != '') {
			//This is the sql used by all searches.
			$sql = "select $course_fields from $course_table where enabled=1 and hidden=0 and id in ($course_ids)";
			//$sql = "select $course_fields from $course_table where id in ($course_ids)";
			$and_where = 'and';
		}
		else {
			//This is the sql used by all searches.
			$sql = "select $course_fields from $course_table where enabled=1 and hidden=0";
			$and_where = 'and';
		}
		//Add more query conditions
		if ( $dept_ids != '' ) {
			$sql .= " $and_where departmentid in ($dept_ids)";
			$and_where = 'and';
		}
		if ( $grade_sql != '') {
			$sql .= " $and_where id in ($grade_sql)";
			$and_where = 'and';
		}
		//Don't want to show data related to hidden and diabled courses in the searches
		//$sql .= " $and_where enabled=1 and hidden=0 ";

		//} //else
		if (!$showunit) {
			//This Query is used by all searches so be careful what you do to it!
			//We just want the course query which is used by all queries
			return $sql; //This only gets the course id etc. This is used by all searches
		}

		$default_status = 'u.status=' . CMS_UNIT_STATUS_PUBLISH;
		$status_where = '';
		if ( !is_array($status_array) || count($status_array) < 1 )
			$status_where = $default_status;
		else {
			$status_where = ''; $or = '';
			foreach ($status_array as $status_number) {
				if ( is_numeric($status_number) ) {
					$status_where .= "$or u.status=$status_number";
					$or = ' or';
				}
			}
		}
		if ( empty($status_where) )
			$status_where = $default_status;

		//$sql = "select u.id,u.title,u.startdate,u.enddate,u.description," .
				 //"c.id courseid,c.name coursename from unit u,($sql) c " .
		$sql = "select u.*, c.name coursename from unit u,($sql) c " .
				 "where u.hidden=0 and u.courseid=c.id and ($status_where) ";
		if ($monthfrom && $yearfrom && $monthto && $yearto) {
			$datefrom = $this->db_datefrom($monthfrom, $yearfrom);
			$dateto = $this->db_dateto($monthto, $yearto);
			$sql .= " and ((enddate between '$datefrom' and '$dateto') and " .
					  "(startdate between '$datefrom' and '$dateto'))";
		}
		if ( $sort )
			$sql .= " order by $sort";
		return $sql;
	}

	private function db_datefrom($month, $year) {
		if ( $month < 10 )
			$month = "0{$month}";
		return "$year-$month-01 00:00:00";
	}

	private function db_dateto($month, $year) {

		if ( checkdate($month, 31, $year) )
			$day = 31;
		elseif (checkdate($month, 30, $year))
			$day = 30;
		elseif (checkdate($month, 29, $year))
			$day = 29;
		else
			$day = 28;
		if ( $month < 10 )
			$month = "0{$month}";
		return "$year-$month-$day 23:59:59";
	}
}
?>