<?php
/**
 * bb_isb.php - The driver for ISB Blackbaud
 *
 * @author $Author: dtong $
 * @version $Id: bb_isb.php,v 1.3 2009/08/17 03:25:38 dtong Exp $
 * @copyright Copyright (c) 2009, Tiller Software Co., Ltd.
*/
require_once (APPPATH . 'libraries/batch/driver_base.php');

class cms_batch_driver extends batch_driver_base{

	//This sql is shared in 2 methods so put it here
	public $user_sql = "SELECT f.EA7FACULTYID, f.EA7RECORDSID, r.LASTNAME, r.FIRSTNAME,
					r.MIDDLENAME, r.NAMEFORDISPLAY, r.ONLINEUSERID, r.IMPORTID
				FROM EA7FACULTY f
				LEFT JOIN EA7RECORDS r ON r.EA7RECORDSID=f.EA7RECORDSID
				WHERE f.TERMINATED=0";
			//WHERE f.TERMINATED=0 AND r.ONLINEUSERID IS NOT NULL";

	//Make $hack_usernames empty array when not needed
	//These are missing usernames in BB, the index number is the BB faculty id
	public $hack_usernames = array(92=>'cltuanmu',221=>'tfitter',345=>'spwong',
			352=>'xli',373=>'jgao',402=>'swong',406=>'lsteele',423=>'dandreassen',
			433=>'ccurtis',437=>'jleiner',441=>'hhebron',446=>'eltan',447=>'gthomas',
			448=>'jluan',453=>'nshi',453=>'dwu',457=>'lyu',600=>'rker');

	function __construct($config) {
		parent::__construct($config);
		$this->CI->load->model('batch/mssql_model');
		$ret = $this->CI->mssql_model->connect($config->db_server,
				 	$config->db_user, $config->db_password, $config->db_dbname);
	}

	function __destruct() {
		$this->CI->mssql_model->close();
	}

	function get_user_course() {
		$sql="SELECT cf.EA7COURSESID,ff.ONLINEUSERID,ff.EA7FACULTYID FROM EA7COURSEFACULTY cf
				JOIN ($this->user_sql) ff ON ff.EA7FACULTYID = cf.EA7FACULTYID";
		$user_course = $this->CI->mssql_model->query($sql);
		if ( !$user_course || count($user_course) <= 0 )
			return FALSE;
		$ret = array();
		foreach($user_course as $item) {
			$sis_courseid = $item['EA7COURSESID'];
			$username = $item['ONLINEUSERID'];
			if ( is_null($username) || trim($username) == '' ) {
				$sis_facultyid = $item['EA7FACULTYID'];
				if ( !array_key_exists($sis_facultyid, $this->hack_usernames) )
					continue; //No username so skip
				$username = $this->hack_usernames[$sis_facultyid];
			}
			$ret[] = array('sis_courseid'=>$sis_courseid, 'username'=>$username);
		}
		return $ret;
	}

	function get_course_grade() {
		$grade_trans = array(301=>15,302=>14,303=>13,304=>1,305=>2,306=>3,307=>4,
			308=>5,309=>6,309=>6,310=>7,311=>8,312=>9,313=>10,314=>11,315=>12);
		$sql = "SELECT cg.*,t.ENTRYID,t.DESCRIPTION  FROM EA7COURSEGRADELEVELS cg
				  JOIN TABLEENTRIES t ON t.TABLEENTRIESID=cg.GRADELEVEL";
		$coursegrade = $this->CI->mssql_model->query($sql);
		//var_dump($coursegrade); exit;
		if ( !$coursegrade || count($coursegrade) <= 0 )
			return FALSE;
		$ret = array();
		foreach ( $coursegrade as $item ) {
			$sis_grade_level = $item['GRADELEVEL'];
			//Can't find grade in translation, bad grade level in SIS, just skip
			if ( !array_key_exists($sis_grade_level, $grade_trans) )
				continue;
			$sis_grade_level = $grade_trans[$sis_grade_level];
			$ret[] = array('sis_courseid'=>$item['EA7COURSESID'], 'cms_gradeid'=>$sis_grade_level);
		}
		return $ret;
	}

	function get_courses() {
		/*$sql =
			"SELECT *
			FROM EA7COURSES WHERE DEPARTMENTSTRING IS NOT NULL AND
			DEPARTMENTSTRING NOT IN ('<None>','<all>')
			AND len(DEPARTMENTSTRING) > 2 AND NOLONGEROFFERED=0 ORDER BY COURSENAME"; */
		//the IDs are due to bad BB data
		$skip_ids = "53,107,125,176,177,37,38,43,44,47,48,195,198,63,70,138,139,142,143,171,180,224,978,928,923,973,980,293,927,895,294,275,930,287,901,931,899,968,975,249,270,989,964,262,244,282,292,919,926,916,929,910,932,893,906,279,965,925,924,948,281,949,954,984,985,274,251,942,986,276,892,913,912,911,252,977,277,246,897,243,933,945";
		$add_ids = "346,362,377,347,363,378,343,359,375,353,369,352,368,336,351,367,349,365,380,521,174,170,166,167,144,132,130,116,114,111,91,67,64,60,49";
		$hack_sql = " and EA7COURSESID not in ($skip_ids) or EA7COURSESID in ($add_ids)";
		$sql = "
			SELECT EA7COURSESID,COURSEID,COURSENAME,DESCRIPTION,SCHOOLSID,
				IMPORTID,COURSETYPESTRING,DEPARTMENTSTRING FROM EA7COURSES WHERE EA7COURSESID IN
			(SELECT EA7COURSESID FROM EA7CLASSES WHERE STARTTERM IN
			(SELECT EA7TERMSID FROM EA7TERMS WHERE ENDDATE >= getdate()))";

		$sql .= $hack_sql;

		$courses = $this->CI->mssql_model->query($sql);
		if ( !$courses || count($courses) <= 0 )
			return FALSE;
		$ret = array();
		foreach ($courses as $row) {
			$ret[] = array('dbid'=>$row['EA7COURSESID'], 'courseid'=>$row['COURSEID'],
				'name'=>$row['COURSENAME'], 'schoolid'=>$row['SCHOOLSID'],
				'importid'=>$row['IMPORTID'], 'department'=>$row['DEPARTMENTSTRING']);
		}
		return $ret;
	}

	function get_users() {
		$email_domain = $this->config_value('email_domain');
		if ( $email_domain == NULL ) {
			echo "Error, failed to get email domain, not processing users.\n";
			return FALSE;
		}
		$sql =$this->user_sql;
		//Make $hack_usernames empty array when not needed
		$hack_usernames = $this->hack_usernames;
		$users = $this->CI->mssql_model->query($sql);
		if ( !$users || count($users) <= 0 )
			return FALSE;
		$ret = array();
		foreach ($users as $row) {
			$username = $row['ONLINEUSERID'];
			if ( is_null ($username) || trim($username)=='' ) {
				if ( !array_key_exists($row['EA7FACULTYID'], $hack_usernames) )
					continue;
				$username = $hack_usernames[$row['EA7FACULTYID']];
			}
			$ret[] = array('dbid'=>$row['EA7FACULTYID'],'lastname'=>$row['LASTNAME'],
				'firstname'=>$row['FIRSTNAME'],'middlename'=>$row['MIDDLENAME'],
				'displayname'=>$row['NAMEFORDISPLAY'],'userid'=>$row['IMPORTID'],
				'username'=>$username, 'email'=>"{$username}@{$email_domain}",
				'title'=>'');
		}
		return $ret;
	}
}

?>