<?php


// +----------------------------------------------------------------------+
// | PHP version 4                                                        |
// +----------------------------------------------------------------------+
// | Copyright (c) 1997-2005 Snapshot Inc.                                |
// +----------------------------------------------------------------------+
// | License                                                              |
// | GNU General Public License                                           |
// +----------------------------------------------------------------------+
// | Authors: Takayuki Tanimoto <takayuki@snapshot.co.jp>                 |
// +----------------------------------------------------------------------+
//
// $Id: Admin.class.php,v 1.1 2005/06/20 09:31:26 snapshot Exp $
//
// Description:
// 2005/05/19
//

require_once (OPT_DIR.'u-learning/data/DataObject.class.php');
require_once (OPT_DIR.'u-learning/data/Subject.class.php');
require_once (OPT_DIR.'u-learning/data/AdminExam.class.php');

/**
 * Admin
 * 
 * 教員クラス
 */
class Admin extends DataObject {

    // 教員ID
    var $aid;

    // パスワード
    var $pass;
    // 氏名
    var $name;
    // ログインメッセージ
    var $message;
    // お知らせ
    var $information;
    
    // 予備１
    var $reserve1;

    // 科目
    var $subjects;
    // 学生
    var $users;

    /**
     * コンストラクタ
     */
    function Admin($aid = null, $db = null) {
        if (!is_null($aid) && !is_null($db)) {
            $this->setDB($db);
            $sql = "SELECT * FROM admin_info WHERE aid = ".$this->DB->qstr($aid);
            $row = $this->DB->GetRow($sql);
            $this->setValues($row);
        }
    }

    /**
     * 配列に含まれるプロパティをすべてセットする
     */
    function setValues($array) {
        $this->aid = $array['aid'];
        $this->pass = $array['pass'];
        $this->name = $array['name'];
        $this->message = $array['message'];
        $this->information = $array['information'];
        $this->reserve1 = $array['reserve1'];
    }

    /**
     * 自分自身をDBに追加する
     */
    function insert() {
        $insert = "INSERT INTO admin_info (aid, pass, name, message, information, reserve1) VALUES ("
        	.$this->DB->qstr($this->aid).", "
        	.$this->DB->qstr($this->pass).", "
        	.$this->DB->qstr($this->name).", "
        	.$this->DB->qstr($this->message).", "
        	.$this->DB->qstr($this->information).", "
        	.$this->DB->qstr($this->reserve1).")";
        return $this->DB->Execute($insert);
    }

    /**
     * DBを更新する
     */
    function update($db = null) {
        $this->setDB($db);
        $sql = "UPDATE admin_info SET "
        	."pass = ".$this->DB->qstr($this->pass).", "
        	."name = ".$this->DB->qstr($this->name).", "
        	."message = ".$this->DB->qstr($this->message).", "
        	."information = ".$this->DB->qstr($this->information).", "
        	."reserve1 = ".$this->DB->qstr($this->reserve1)." "
        	." WHERE aid = '$this->aid'";
        return $this->DB->Execute($sql);
    }

    /**
     * 同じキーのデータがすでに存在するかどうか確認する
     */
    function keyExists() {
        $select = "SELECT count(*) FROM admin_info WHERE aid = ".$this->DB->qstr($this->aid);
        if ($this->DB->GetOne($select))
            return true;
        return false;
    }

    /**
     * 教員が所有する科目をすべて取得する
     */
    function getSubjects($db = null) {
        $this->setDB($db);
        if (is_array($this->subjects)) {
            return $this->subjects;
        }
        return $this->setSubjects($db);
    }

    /**
     * 教員が所有する科目をDBから属性にセットする
     */
    function setSubjects($db = null) {
        $this->setDB($db);
        $sql = "SELECT * FROM admin_subject "." WHERE aid = ".$this->DB->qstr($this->aid)." AND del <> '1'"." ORDER BY sid";
        $all = $this->DB->GetAll($sql);
        $this->subjects = array ();
        foreach ($all as $row) {
            $subject = new Subject();
            $subject->setValues($row);
            $subject->setDB($this->DB);
            array_push($this->subjects, $subject);
        }
        return $this->subjects;
    }

    /**
     * 受講可能な科目を取得する
     */
    function getUsableSubjects($db = null) {
        $this->setDB($db);
        $subjects = $this->getSubjects($db);
        $usables = array ();
        foreach ($subjects as $subject) {
            if ($subject->usable) {
                array_push($usables, $subject);
            }
        }
        return $usables;
    }

    /**
     * 教員が所有するテストをすべて取得する
     */
    function getAllExams($db = null) {
        $this->setDB($db);
        $sql = "SELECT s.name as sname, e.* "." FROM admin_exam e "." INNER JOIN admin_subject s "." ON e.sid = s.sid "." WHERE s.del <> '1' AND s.aid = ".$this->DB->qstr($this->aid);
        $all = $this->DB->GetAll($sql);
        $exams = array ();
        foreach ($all as $row) {
            $exam = new AdminExam();
            $exam->setExamValues($row);
            array_push($exams, $exam);
        }
        return $exams;
    }

    /**
     * 教員の科目を受講しているすべての学生をセットする
     */
    function setAllUsers($db = null) {
        $this->setDB($db);
        $select = "SELECT ui.*";
        $from = "FROM (admin_subject asub INNER JOIN user_subject usub USING(sid))"." INNER JOIN user_info ui USING(uid)";
        $where = "WHERE asub.del <> '1' AND asub.aid = ".$this->DB->qstr($this->aid)." AND ui.regist = '1'";
        $group = "GROUP BY ui.uid";
        $order = "ORDER BY ui.uid";
        $all = $this->DB->GetAll("$select $from $where $group $order");
        $this->users = array ();
        foreach ($all as $row) {
            $std = new Student();
            $std->setValues($row);
            array_push($this->users, $std);
        }
        return $this->users;
    }

    /**
     * 教員が所有する配信済みのテストを取得する
     */
    function getPubExams($num1 = null, $num2 = null, $db = null) {
        $this->setDB($db);
        $select = "SELECT s.name as sname, e.*";
        $from = "FROM admin_exam e INNER JOIN admin_subject s ON e.sid = s.sid";
        $where = "WHERE s.aid = ".$this->DB->qstr($this->aid)." AND s.del <> '1'"." AND e.publish_time IS NOT NULL";
        $order = "ORDER BY publish_time DESC";

        // 取得する番号を制限する
        // !!!! MySQL以外では使えない
        $limit = '';
        if (!is_null($num1) && !is_null($num2)) {
            $limit .= ' LIMIT '. ($num1).', '.$num2;
        }
        elseif (!is_null($num1) && is_null($num2)) {
            $limit .= ' LIMIT 0, '.$num1;
        }

        $all = $this->DB->GetAll("$select $from $where $order $limit");
        $exams = array ();
        foreach ($all as $row) {
            $exam = new AdminExam();
            $exam->setExamValues($row);
            array_push($exams, $exam);
        }
        return $exams;
    }

    /**
     * 教員が所有する配信済テストの総数を取得する
     */
    function getPubExamsCount($db = null) {
        $this->setDB($db);
        $select = "SELECT count(*)";
        $from = "FROM admin_exam e INNER JOIN admin_subject s ON e.sid = s.sid";
        $where = "WHERE s.aid = ".$this->DB->qstr($this->aid)." AND s.del <> '1'"." AND e.publish_time IS NOT NULL";
        return $this->DB->GetOne("$select $from $where");
    }

    /**
     * 教員が所有する未配信のテストをすべて取得する
     */
    function getUnpubExams($db = null) {
        $this->setDB($db);
        $select = "SELECT s.name as sname, e.*";
        $from = "FROM admin_exam e INNER JOIN admin_subject s ON e.sid = s.sid";
        $where = "WHERE s.aid = ".$this->DB->qstr($this->aid)." AND s.del <> '1'"." AND e.publish_time IS NULL";
        $order = "ORDER BY update_time DESC";
        $all = $this->DB->GetAll("$select $from $where $order");
        $exams = array ();
        foreach ($all as $row) {
            $exam = new AdminExam();
            $exam->setExamValues($row);
            array_push($exams, $exam);
        }
        return $exams;
    }

    /**
     * テストを削除する
     */
    function deleteExam($eid, $db = null) {
        $this->setDB($db);
        $sql = "DELETE FROM admin_exam WHERE eid = ".$eid;
        $this->DB->Execute($sql);
        $sql = "DELETE FROM admin_question WHERE eid = ".$eid;
        $this->DB->Execute($sql);
        $sql = "DELETE FROM admin_choice WHERE eid = ".$eid;
        $this->DB->Execute($sql);
    }

    /**
     * 学生を削除する
     */
    function deleteUser($uid) {
        $sql = "DELETE FROM user_info"." WHERE aid = ".$this->DB->qstr($this->aid)." AND uid = ".$this->DB->qstr($uid);
        $result = $this->DB->Execute($sql);
        if(!$result) return $result;
        $sql = "DELETE FROM user_subject WHERE aid = ".$this->DB->qstr($this->aid)." AND uid = ".$this->DB->qstr($uid);
        $result = $this->DB->Execute($sql);
        if(!$result) return $result;
        $sql = "DELETE FROM user_exam WHERE aid = ".$this->DB->qstr($this->aid)." AND uid = ".$this->DB->qstr($uid);
        $result = $this->DB->Execute($sql);
        if(!$result) return $result;
        $sql = "DELETE FROM user_question WHERE aid = ".$this->DB->qstr($this->aid)." AND uid = ".$this->DB->qstr($uid);
        $result = $this->DB->Execute($sql);
        if(!$result) return $result;
        $sql = "DELETE FROM user_choice WHERE aid = ".$this->DB->qstr($this->aid)." AND uid = ".$this->DB->qstr($uid);
        $result = $this->DB->Execute($sql);
        if(!$result) return $result;
    }

    /**
     * 配信済のテストを検索する
     */
    function & searchPubExams($sid) {
        $select = "SELECT e.*";
        $from = "FROM admin_subject s INNER JOIN admin_exam e USING(sid)";
        $where = "WHERE s.del <> '1' AND s.aid = ".$this->DB->qstr($this->aid)." AND publish_time IS NOT NULL";
        if ($sid)
            $where .= " AND s.sid = ".$sid;
        $order = "ORDER BY publish_time DESC";
        $all = $this->DB->GetAll("$select $from $where $order");
        $results = array ();
        foreach ($all as $row) {
            $exam = new AdminExam();
            $exam->setValues($row);
            $exam->setDB($this->DB);
            array_push($results, $exam);
        }
        return $results;
    }

    /**
     * 学生を検索する
     */
    function & searchUsers($sid) {
        $select = "SELECT ui.*";
        $from = "FROM (admin_subject asub INNER JOIN user_subject usub USING(sid))"." INNER JOIN user_info ui USING(uid)";
        $where = "WHERE asub.del <> '1' AND asub.aid = ".$this->DB->qstr($this->aid)." AND ui.regist = '1'";
        if ($sid)
            $where .= " AND asub.sid = ".$sid;
        $group = "GROUP BY ui.uid";
        $order = "ORDER BY ui.uid";
        $all = $this->DB->GetAll("$select $from $where $group $order");
        $results = array ();
        foreach ($all as $row) {
            $std = new Student();
            $std->setValues($row);
            $std->setDB($this->DB);
            array_push($results, $std);
        }
        return $results;
    }

    /**
     * 学生結果詳細を取得する
     */
    function getStudentExamDetailByUser($uid, $sid = null) {
        $select = "SELECT ue.aid, ue.uid,"." asub.sid, asub.name sname,"." ue.rid, ue.eid, ue.reply_date, ue.points,"." ae.name ename, ae.update_time, ae.retry, ae.correct, ae.share, ae.pub, ae.publish_time, ae.summary, ae.deadline_time,"." ui.pass, ui.name uname, ui.mail, ui.number unumber, ui.year uyear, ui.regist, ui.del";
        $from = "FROM ( ( ( user_exam ue LEFT JOIN user_subject usub ON ue.aid = usub.aid AND ue.uid = usub.uid )"." LEFT JOIN admin_exam ae ON ue.eid = ae.eid )"." LEFT JOIN admin_subject asub ON ae.sid = asub.sid )"." LEFT JOIN user_info ui ON usub.aid = ui.aid AND ue.uid = ui.uid";
        $where = "WHERE asub.del <> '1' AND ue.uid = ".$uid." AND ui.regist = '1'"." AND ue.reply_date IS NOT NULL";
        if ($sid)
            $where .= " AND ae.sid = ".$sid;
        $group = "GROUP BY ue.rid";
        $order = "ORDER BY ue.reply_date DESC";
        $all = $this->DB->GetAll("$select $from $where $group $order");
        $results = array ();
        foreach ($all as $row) {
            $sed = new StudentExamDetail();
            $sed->setDB($this->DB);
            $sed->setValues($row);
            $sed->setQuestions();
            array_push($results, $sed);
        }
        return $results;
    }

}
?>