<?php


// +----------------------------------------------------------------------+
// | PHP version 4                                                        |
// +----------------------------------------------------------------------+
// | Copyright (c) 1997-2005 Snapshot Inc.                                |
// +----------------------------------------------------------------------+
// | License                                                              |
// | GNU General Public License                                           |
// +----------------------------------------------------------------------+
// | Authors: Takayuki Tanimoto <takayuki@snapshot.co.jp>                 |
// +----------------------------------------------------------------------+
//
// $Id: Subject.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/Admin.class.php');
require_once (OPT_DIR.'u-learning/data/AdminExam.class.php');
require_once (OPT_DIR.'u-learning/data/Student.class.php');
require_once (OPT_DIR.'u-learning/data/StudentExamDetail.class.php');
require_once (OPT_DIR.'u-learning/lib/TemplateUtils.class.php');

/**
 * Subject
 * 
 * 科目クラス
 */
class Subject extends DataObject {

    // 科目ID
    var $sid;

    // 教員ID
    var $aid;
    // 科目名
    var $name;
    // 概要
    //var $summary;
    // 受講フラグ
    var $usable;
    // 削除フラグ
    var $del;
    // 共有フラグ
    var $share;
    // 公開フラグ
    var $pub;

    // テスト
    var $exams;
    // 配信済テスト
    var $pub_exams;
    // 学生
    var $users;

    /**
     * コンストラクタ
     */
    function Subject($sid = null, $db = null) {
        if (!is_null($sid) && !is_null($db)) {
            $this->setDB($db);
            $this->sid = $sid;

            $sql = "SELECT * FROM admin_subject "." WHERE sid = ".$this->DB->qstr($sid);
            $row = $this->DB->GetRow($sql);
            $this->setValues($row);
        }
    }

    /**
     * 配列（ハッシュ）から値をセットする
     */
    function setValues($array) {
        $this->sid = $array['sid'];
        $this->aid = $array['aid'];
        $this->name = $array['name'];
        $this->usable = $array['usable'];
        $this->del = $array['del'];
        $this->share = $array['share'];
        $this->pub = $array['pub'];
    }

    /**
     * DBへ追加
     */
    function insert($db = null) {
        $this->setDB($db);
        $sql = "INSERT INTO admin_subject (aid, name, usable) VALUES (".$this->DB->qstr($this->aid).", ".$this->DB->qstr($this->name).", ".$this->DB->qstr($this->usable).")";
        return $this->DB->Execute($sql);
    }

    /**
     * DBを更新
     */
    function update($db = null) {
        $this->setDB($db);
        $sql = "UPDATE admin_subject SET "." name = ".$this->DB->qstr($this->name).", "." usable = ".$this->DB->qstr($this->usable).", "." del = ".$this->DB->qstr($this->del)." "." WHERE sid = $this->sid";
        return $this->DB->Execute($sql);
    }

    /**
     * メールで配信の通知を行う
     */
    function sendPublishMail($exam, $db = null) {
        $this->setDB($db);
        $select = "SELECT s.sid, i.*";
        $from = "FROM user_info i INNER JOIN user_subject s USING(aid,uid)";
        $where = "WHERE s.sid = ".$this->sid." AND i.del <> '1' AND i.regist = '1'"." AND i.aid = ".$this->DB->qstr($this->aid);
        $all = $this->DB->GetAll("$select $from $where");
        if (count($all) < 1)
            return null;

        // メールの送信
        //mb_language("Japanese");
        //mb_internal_encoding("UTF-8");
        $admin = $this->getAdmin($db);
        $header = "From: ".mb_encode_mimeheader($admin->aid).MAIL_DOMAIN."\n";
        $header .= "Content-Type: text/plain; charset=\"ISO-2022-JP\"\n";
        $subject = $admin->name." テスト配布";
        $subject = mb_encode_mimeheader(mb_convert_encoding($subject, "ISO-2022-JP", "auto"));
        $header = mb_convert_encoding($header, "ISO-2022-JP", "auto");
        foreach ($all as $row) {
            $msg = $this->makePublishMail($exam, $row['uid'], $row['pass']);
            $msg = mb_convert_encoding($msg, "ISO-2022-JP", "auto");
            mb_send_mail($row['mail'], $subject, $msg, $header);
        }
    }

    /**
     * テスト配布の通知メールを作成する
     */
    function makePublishMail($exam, $uid, $pass) {
        $deadline_label = $exam->getDeadlineLabel();
        $deadline_time = $exam->getDeadlineTime();
        $url = TemplateUtils :: userURL('Subject', 'List', array ('aid' => $this->aid, 'uid' => $uid, 'pass' => $pass, 'sid' => $this->sid));
        $msg =<<<__MSG__
テストが配布されました。

科目:$this->name
テスト名:$exam->name
締切:$deadline_label $deadline_time

$exam->summary

$url

__MSG__;

        return $msg;
    }

    /**
     * 教員オブジェクトを取得する
     */
    function getAdmin($db = null) {
        $this->setDB($db);
        return new Admin($this->aid, $db);
    }

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

    /**
     * 属するテストをセットする
     */
    function setExams($db = null) {
        $this->setDB($db);
        $sql = "SELECT * FROM admin_exam WHERE sid = ".$this->sid;
        $all = $this->DB->GetAll($sql);
        $this->exams = array ();
        foreach ($all as $row) {
            $exam = new AdminExam();
            $exam->setValues($row);
            array_push($this->exams, $exam);
        }
        return $this->exams;
    }

    /**
     * 属する学生をセットする
     */
    function setUsers($db = null) {
        $this->setDB($db);
        $select = "SELECT i.*";
        $from = "FROM user_subject s INNER JOIN user_info i USING(uid)";
        $where = "WHERE s.sid = ".$this->sid." AND i.regist = '1'";
        $order = "ORDER BY i.uid";
        $all = $this->DB->GetAll("$select $from $where $order");
        $this->users = array ();
        foreach ($all as $row) {
            $user = new Student();
            $user->setValues($row);
            array_push($this->users, $user);
        }
        return $this->users;
    }

    /**
     * 属する配信済テストをセットする
     */
    function setPubExams($db = null) {
        $this->setDB($db);
        $sql = "SELECT * FROM admin_exam WHERE sid = ".$this->sid." AND publish_time IS NOT NULL";
        $all = $this->DB->GetAll($sql);
        $this->pub_exams = array ();
        foreach ($all as $row) {
            $exam = new AdminExam();
            $exam->setValues($row);
            array_push($this->pub_exams, $exam);
        }
        return $this->pub_exams;
    }

    /**
     * 条件に合致する配信済テストを取得する
     */
    function getExamResults($eid, $uid) {
        $select = "SELECT ae.*";
        $from = "FROM (admin_exam ae INNER JOIN user_exam ue USING(eid))"." INNER JOIN user_info ui USING(uid)";
        $where = "WHERE ae.sid = ".$this->sid." AND ui.regist = '1'";
        if ($eid)
            $where .= " AND ue.eid = ".$eid;
        if ($uid)
            $where .= " AND ue.uid = ".$this->DB->qstr($uid);
        $all = $this->DB->GetAll("$select $from $where");
        $results = array ();
        foreach ($all as $row) {
            $exam = new AdminExam();
            $exam->setValues($row);
            array_push($results, $exam);
        }
        return $results;
    }

    /**
     * 条件に合致する学生を取得する
     */
    function getStudentResults($eid, $uid) {
        $select = "SELECT ui.*";
        $from = "FROM (admin_exam ae INNER JOIN user_exam ue USING(eid))"." INNER JOIN user_info ui USING(uid)";
        $where = "WHERE ui.regist = '1'"." AND ae.sid = ".$this->sid;
        if ($eid)
            $where .= " AND ue.eid = ".$eid;
        if ($uid)
            $where .= " AND ue.uid = ".$this->DB->qstr($uid);
        $all = $this->DB->GetAll("$select $from $where");
        $results = array ();
        foreach ($all as $row) {
            $student = new Student();
            $student->setValues($row);
            array_push($results, $student);
        }
        return $results;
    }

    /**
     * 学生結果詳細を取得する
     */
    function getStudentExamDetailByExam($eid) {
        $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 usub.sid = ".$this->sid." AND ue.eid = ".$eid." AND ui.uid IS NOT NULL"." AND ui.regist = '1'"." AND ue.reply_date IS NOT NULL";
        $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;
    }
    
    /**
     * 配信済テストの数を取得する
     */
    function getPubExamsCount() {
        $select = "SELECT count(*)";
        $from = "FROM admin_exam";
        $where = "WHERE sid = ".$this->sid.
                 " AND publish_time IS NOT NULL";
        return $this->DB->GetOne("$select $from $where");
    }
    
    /**
     * 配信済テストを始点と個数を指定して取得する
     */
    function getPubExamsWithLimit($start, $count) {
        $select = "SELECT *";
        $from = "FROM admin_exam";
        $where = "WHERE sid = ".$this->sid.
                 " AND publish_time IS NOT NULL";
        $order = "ORDER BY publish_time DESC";
        $limit = "LIMIT ".$start.", ".$count;
        $all = $this->DB->GetAll("$select $from $where $order $limit");
        $results = array();
        foreach($all as $row) {
            $exam = new AdminExam();
            $exam->setValues($row);
            array_push($results, $exam);
        }
        return $results;
    }

}
?>