<?php
/**
 * ps_lcis.php - The driver for LCIS PowerSchool
 *
 * @author $Author: sathaporn $
 * @version $Id: ps_lcis.php,v 1.6 2010/06/30 06:38:07 sathaporn Exp $
 * @copyright Copyright (c) 2009, Tiller Software Co., Ltd.
*/
//require_once (APPPATH . 'libraries/batch/driver/general_driver.php');
require_once ('general_driver.php');
//require_once (APPPATH . 'libraries/batch/driver_base.php');

class cms_batch_driver extends general_driver {
	private $db = 'isb_ps.';
	public $user_sql = "SELECT 
									case when trim(t.email_addr) is null then null else SUBSTR(lower(t.email_addr),0,INSTR(trim(t.email_addr),'@',1,1)-1 ) end as username,
									trim(t.nameasimported) displayname, 
									trim(t.last_name) lastname, trim(t.first_name) firstname, trim(t.middle_name) middlename,
									trim(t.email_addr) email,
									trim(t.title) title,
									trim(t.dcid) userid,
									trim(t.teachernumber) sis_teacher_number
							FROM isb_ps.teachers t
							WHERE not email_addr is  null
							ORDER BY lower(t.email_addr)
								"; //FROM teachers limit 0, 140
	
	public $department_sql = "  SELECT id, name, shortname from department;";
	//public $course_sql = "  SELECT id dbid, dcid courseid, course_name name, schoolid, course_number importid, 0 department FROM courses order by schoolid, course_name ;";

	public $course_sql1 = "	SELECT deptname department, id dbid, dcid courseid, course_name name, schoolid, course_number importid
									FROM (
										SELECT d.id department, d.shortname deptname, c.*
										FROM courses c
										join course_temp t on trim(t.c2)=trim(c.course_number) and trim(t.c3) = trim(c.course_name )
										join department d on trim(d.shortname) = trim(t.c5)									
									) c
									order by deptname,schoolid, course_name;";
	/*
	public $user_course_sql = " SELECT DISTINCT 
										c.course_number,c.course_name, 
										case when trim(t.email_addr) is null then null else SUBSTR(lower(t.email_addr),0,INSTR(trim(t.email_addr),'@',1,1)-1 ) end as username
									FROM isb_ps.courses c
									JOIN isb_ps.sections s on c.course_number = s.course_number
									JOIN isb_ps.teachers t on t.id = s.teacher
									";
									*/
	/*
	public $user_course_sql = " SELECT DISTINCT 
										c.course_number, c.course_name,  
										case when trim(t.email_addr) is null then null else SUBSTR(lower(t.email_addr),0,INSTR(trim(t.email_addr),'@',1,1)-1 ) end as username
									FROM isb_ps.courses c
									JOIN isb_ps.sections s on c.course_number = s.course_number
									JOIN isb_ps.teachers t on t.id = s.teacher
									";	
									*/
	
	public $user_course_sql = " SELECT DISTINCT 
										c.id sis_courseid,
										c.course_number, c.course_name,  
										case when trim(t.email_addr) is null then null else SUBSTR(lower(t.email_addr),0,INSTR(trim(t.email_addr),'@',1,1)-1 ) end as username
									FROM isb_ps.courses c
									JOIN isb_ps.sections s on c.course_number = s.course_number
									JOIN isb_ps.teachers t on t.id = s.teacher
									order by c.course_name
									";	
	public $course_grade_sql = " SELECT id, course_number FROM courses;";
	//public $course_sql = "select rownum, c.dcid dbid, c.course_number sis_course_number, c.course_name, c.sched_department department   
	//							from isb_ps.courses c where rownum < 10 order by c.course_number  ";
	public $course_sql = "select rownum, c.id sis_course_dbid, c.course_number sis_course_number, c.course_name name, 
								case when c.sched_department is null then 'Other' else c.sched_department end departmentid 
								from isb_ps.courses c where c.course_number is not null and c.id is not null order by c.sched_department, c.course_number  "; //,1 autogen
	
	//This structure field of department table.
	public $department = array('fields' => array('name', 'shortname') );
	public $course = array('fields' => array('sis_course_dbid', 'sis_course_number','name','departmentid','autogen')); 	
	public $user = array('fields' => array('displayname','username','lastname','firstname','middlename','email','title','userid','sis_teacher_number')); 
	//public $user_course = array('fields' => array('course_number','username')); 
	public $user_course = array('fields' => array('sis_courseid','username'));
	
	
	function update_department() {
		$success = parent::update_department();
		if ($this->CI->config->item('cms_use_sync_department') === true) {
			if ($success === true)
				echo '<br /><font color="gray">Update Departments</font> : <font color="green"><b>Success</b></font><br />';
			else
				echo '<br /><font color="gray">Update Departments</font> : <font color="red"><b>Fail</b></font><br />';
		}
	}	
	function update_courses() {
		$success = parent::update_courses();
		if ($success === true)
				echo '<br /><font color="gray">Update Courses</font> : <font color="green"><b>Success</b></font><br />';
			else
				echo '<br /><font color="gray">Update Courses</font> : <font color="red"><b>Fail</b></font><br />';
	}
	function update_users() {
		$success = parent::update_users();
		if ($success === true)
				echo '<br /><font color="gray">Update Users</font> : <font color="green"><b>Success</b></font><br />';
			else
				echo '<br /><font color="gray">Update Users</font> : <font color="red"><b>Fail</b></font><br />';
	}
	function update_course_grade() {
		$success = parent::update_course_grade();
		if ($success === true)
				echo '<br /><font color="gray">Update Course Grade</font> : <font color="green"><b>Success</b></font><br />';
			else
				echo '<br /><font color="gray">Update Course Grade</font> : <font color="red"><b>Fail</b></font><br />';
	}
	function update_user_course() {
		$success = parent::update_user_course();
		if ($success === true)
				echo '<br /><font color="gray">Update User Course</font> : <font color="green"><b>Success</b></font><br />';
			else
				echo '<br /><font color="gray">Update User Course</font> : <font color="red"><b>Fail</b></font><br />';
	}
}

?>