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

class Lthistory_model extends CI_Model {

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

	function get($coursequery, $type, $monthfrom, $yearfrom, $monthto, $yearto, $hist_type) {
		$target_text = $this->db->dbprefix('target_text');
		$target_text_hist = $this->db->dbprefix('target_text_hist');
		$benchmark = $this->db->dbprefix('benchmark');
		$standard_table = $this->db->dbprefix('standard');
		$strand_table = $this->db->dbprefix('strand');

		$where = '';
		if ($hist_type !=FALSE){
			$where = "lt.hist_type=$hist_type and ";
		}

		if ($monthfrom && $yearfrom && $monthto && $yearto) {
			$datefrom = $this->db_datefrom($monthfrom, $yearfrom);
			$dateto = $this->db_dateto($monthto, $yearto);

			$field = 'c.id courseid,c.name course,b.data benchmark,'.
					 'lt.id ltid, lt.data lt_history,'.
					 'l.data,l.select1 ks ,lt.hist_mod_data current_lt,lt.hist_type,'.
					 'lt.hist_date ,lt.select1 ks_history,lt.hist_mod_select1 ks_current';

			$where .= "lt.hist_date between '$datefrom' and '$dateto' ";

			$sql = "select $field ".
			   "from $target_text_hist lt ".
			   "left join $benchmark b on lt.benchmarkid=b.id ".
				"left join $standard_table sd on b.standardid=sd.id " .
				"left join $strand_table s on sd.strandid=s.id " .
			   "left join $target_text l on lt.id=l.id ".
			   "join ($coursequery) c on lt.courseid=c.id ".
			   "where $where ".
				"and s.enabled=" . CMS_DB_BOOLEAN_TRUE . " " .
				"and sd.enabled=" . CMS_DB_BOOLEAN_TRUE . " " .
				"and b.enabled=" . CMS_DB_BOOLEAN_TRUE . " " .
			   "order by lt.id,lt.hist_date desc";
		}
		else
			return FALSE;

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

	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";
	}


}
?>