<?php

use app\libraries\CommonHelper;
use app\libraries\DateHelper;
use app\libraries\Tenants;

if (!defined('BASEPATH'))
    exit('No direct script access allowed');
  
class Teacher_model extends MY_Model
{

    //    protected $table = 'rbac_users';

    use Tenants;

    public $short = "a";
    public $table;
    public $tbl;
    public $eDate = ['0', '1', '2', '3', '4', '5', '6', '/'];
    public $cDate = ['日', '一', '二', '三', '四', '五', '六', '／'];
    //    public $database = 'default';

    public function __construct()
    {
        parent::__construct();
        $this->database = 'default';
        $this->tbl = 'admin';
        $this->table = $this->tbl . ' ' . $this->short;
    }

    private function _appLogin($teacherID, $tenant, $db)
    {
        $fields = [
            '`e`.`id` AS event_id',
            '`c`.`id`',
            '`cc`.`name` AS coursecategory_name',
            '`ccs`.`name` AS coursesubcategory_name',
            /* '`u`.`id` as unit_id',
			  '`u`.`title` as unit_name', */
            '`e`.`weekdays`',
            '`ts`.`starttime`',
            '`ts`.`endtime`',
            '`c`.`code` AS course_code',
            '`c`.`name` AS course_name',
            '`r`.`name` AS room_name',
        ];

        //		$sqlapp = "SELECT distinct " . implode(",", $fields) . " FROM `mas_event` `e` " .
        //			"INNER JOIN (SELECT `event_id`, `starttime`, `endtime` FROM `mas_event_timeslot` GROUP BY `event_id`, `starttime`, `endtime`) `ts` ON `e`.`id` = `ts`.`event_id` " .
        //			"INNER JOIN `mas_event_course` `ec` ON `e`.`id` = `ec`.`event_id` " .
        //			"INNER JOIN `mas_course` `c` ON `ec`.`course_id` = `c`.`id` " .
        //			"INNER JOIN `mas_course_category` `cc` ON `c`.`course_category_id` = `cc`.`id` " .
        //			"INNER JOIN `mas_course_category_sub` `ccs` ON `c`.`course_category_sub_id`=`ccs`.`id` " .
        //			"INNER JOIN `mas_room` `r` ON `e`.`room_id` = `r`.`id` " .
        //			"WHERE `e`.`pic_1` = " . $teacherID;
        //		//log_message('debug',$sql);
        //		$tmpapp = $db->query($sqlapp,[$tenant['code']])->result_array();
        //		$sql = "SELECT distinct  FROM " . $this->database_prefix . $tenant['code'] . ".`mas_event` `e` INNER JOIN (SELECT `event_id`, `starttime`, `endtime` FROM " . $this->database_prefix . $tenant['code'] . ".`mas_event_timeslot` GROUP BY `event_id`, `starttime`, `endtime`) `ts` ON `e`.`id` = `ts`.`event_id` INNER JOIN " . $this->database_prefix . $tenant['code'] . ".`mas_event_course` `ec` ON `e`.`id` = `ec`.`event_id` INNER JOIN " . $this->database_prefix . $tenant['code'] . ".`mas_course` `c` ON `ec`.`course_id` = `c`.`id` inner join mas_unit u on c.id=u.course_id WHERE c.status=1 and u.status=1 and u.tenant_no=? and `e`.`pic_1` = " . $teacherID;
        //		$unitsapp = $sysdb->query($sql,[$tenant['code']])->result_array();
        //		$sql = "SELECT distinct n.id as noteId, n.unit_id as unitId,From_UnixTime(n.create_time,'%Y-%m-%d') as createdate, u.title as unitname,n.note_code as notecode,n.title as notename,n.tenant_no as tenantno,n.filename,n.content FROM " . $this->database_prefix . $tenant['code'] . ".`mas_event` `e` INNER JOIN (SELECT `event_id`, `starttime`, `endtime` FROM " . $this->database_prefix . $tenant['code'] . ".`mas_event_timeslot` GROUP BY `event_id`, `starttime`, `endtime`) `ts` ON `e`.`id` = `ts`.`event_id` INNER JOIN " . $this->database_prefix . $tenant['code'] . ".`mas_event_course` `ec` ON `e`.`id` = `ec`.`event_id` INNER JOIN " . $this->database_prefix . $tenant['code'] . ".`mas_course` `c` ON `ec`.`course_id` = `c`.`id` inner join mas_unit u on c.id=u.course_id inner join mas_note n on u.id=n.unit_id WHERE c.status=1 and u.status=1 and n.status=1 and n.filename!='' and n.tenant_no=? and `e`.`pic_1` =" . $teacherID;
        //		$notesapp = $sysdb->query($sql,[$tenant['code']])->result_array();
        //		$sql = "SELECT distinct q.id as questionId,q.unit_id as unitId,u.title as unitname, q.question_code as questioncode,q.question,q.answer1,q.answer2,q.answer3,q.answer4,replace(replace(replace(replace(q.correct, '4','D'),'3','C'),'2','B'),'1','A') as correct,q.solution,q.solution_filename as solutionfilename,q.solution_youtube as solutionyoutube, q.remark,q.filename FROM " . $this->database_prefix . $tenant['code'] . ".`mas_event` `e` INNER JOIN (SELECT `event_id`, `starttime`, `endtime` FROM " . $this->database_prefix . $tenant['code'] . ".`mas_event_timeslot` GROUP BY `event_id`, `starttime`, `endtime`) `ts` ON `e`.`id` = `ts`.`event_id` INNER JOIN " . $this->database_prefix . $tenant['code'] . ".`mas_event_course` `ec` ON `e`.`id` = `ec`.`event_id` INNER JOIN " . $this->database_prefix . $tenant['code'] . ".`mas_course` `c` ON `ec`.`course_id` = `c`.`id` inner join mas_unit u on c.id=u.course_id inner join mas_questions q on u.id=q.unit_id WHERE c.status=1 and u.status=1 and q.status=1 and q.tenant_no=? and `e`.`pic_1` =" . $teacherID;
        //		$questionsapp = $sysdb->query($sql,[$tenant['code']])->result_array();

        $db->select(implode(",", $fields));
        $db->distinct();
        $db->from($this->table_prefix . 'event `e`');
        $db->join('(SELECT `event_id`, `starttime`, `endtime` FROM `' . $this->table_prefix . 'event_timeslot` GROUP BY `event_id`, `starttime`, `endtime`) `ts`', 'e.id = ts.event_id', 'INNER', null);
        $db->join($this->table_prefix . 'event_course `ec`', 'e.id = ec.event_id');
        $db->join($this->table_prefix . 'course `c`', 'ec.course_id = c.id');
        $db->join($this->table_prefix . 'course_category `cc`', 'c.course_category_id = cc.id');
        $db->join($this->table_prefix . 'course_category_sub `ccs`', 'c.course_category_sub_id = ccs.id');
        $db->join($this->table_prefix . 'room `r`', 'e.room_id = r.id');
        $db->where('e.pic_1', $teacherID);
        $tmpapp = $db->get()->result_array();

        $sysdb = $this->db;
        $sysdb->select('u.id AS unitId, u.course_id AS courseId, u.unit_code AS unitcode, u.title AS unitname');
        $sysdb->distinct();
        $sysdb->from($this->database_prefix . $tenant['code'] . "." . $this->table_prefix . "event `e`");
        $sysdb->join("(SELECT `event_id`, `starttime`, `endtime` FROM " . $this->database_prefix . $tenant['code'] . "." . $this->table_prefix . "event_timeslot GROUP BY `event_id`, `starttime`, `endtime`) `ts`", "e.id = ts.event_id", 'INNER', null);
        $sysdb->join($this->database_prefix . $tenant['code'] . "." . $this->table_prefix . "event_course `ec`", "e.id = ec.event_id");
        $sysdb->join($this->database_prefix . $tenant['code'] . "." . $this->table_prefix . "course `c`", "ec.course_id = c.id");
        $sysdb->join($this->table_prefix . "unit `u`", "c.id = u.course_id");
        $sysdb->where([
            'c.status' => 1,
            'u.status' => 1,
            'u.tenant_no' => $tenant['code'],
            'e.pic_1 = ' . $teacherID,
        ]);
        $unitsapp = $sysdb->get()->result_array();

        $sysdb->select("n.id AS noteId, n.unit_id AS unitId, FROM_UNIXTIME(n.create_time,'%Y-%m-%d') AS createdate, u.title AS unitname, n.note_code AS notecode, n.title AS notename, n.tenant_no AS tenantno, n.filename, n.content");
        $sysdb->distinct();
        $sysdb->from($this->database_prefix . $tenant['code'] . "." . $this->table_prefix . "event `e`");
        $sysdb->join("(SELECT `event_id`, `starttime`, `endtime` FROM " . $this->database_prefix . $tenant['code'] . "." . $this->table_prefix . "event_timeslot GROUP BY `event_id`, `starttime`, `endtime`) `ts`", "e.id = ts.event_id", 'INNER', null);
        $sysdb->join($this->database_prefix . $tenant['code'] . "." . $this->table_prefix . "event_course `ec`", "e.id = ec.event_id");
        $sysdb->join($this->database_prefix . $tenant['code'] . "." . $this->table_prefix . "course `c`", "ec.course_id = c.id");
        $sysdb->join($this->table_prefix . "unit `u`", "c.id = u.course_id");
        $sysdb->join($this->table_prefix . "note `n`", "u.id = n.unit_id");
        $sysdb->where([
            'c.status' => 1,
            'u.status' => 1,
            'n.status' => 1,
            'n.filename !=' => '',
            'n.tenant_no' => $tenant['code'],
            'e.pic_1 = ' . $teacherID,
        ]);
        $notesapp = $sysdb->get()->result_array();

        $sysdb->select("q.id AS questionId, q.unit_id AS unitId, u.title AS unitname, q.question_code AS questioncode, q.question, q.answer1, q.answer2, q.answer3, q.answer4, REPLACE(REPLACE(REPLACE(REPLACE(q.correct, '4','D'),'3','C'),'2','B'),'1','A') AS correct, q.solution, q.solution_filename AS solutionfilename, q.solution_youtube AS solutionyoutube, q.remark, q.filename");
        $sysdb->distinct();
        $sysdb->from($this->database_prefix . $tenant['code'] . "." . $this->table_prefix . "event `e`");
        $sysdb->join("(SELECT `event_id`, `starttime`, `endtime` FROM " . $this->database_prefix . $tenant['code'] . "." . $this->table_prefix . "event_timeslot GROUP BY `event_id`, `starttime`, `endtime`) `ts`", "e.id = ts.event_id", 'INNER', null);
        $sysdb->join($this->database_prefix . $tenant['code'] . "." . $this->table_prefix . "event_course `ec`", "e.id = ec.event_id");
        $sysdb->join($this->database_prefix . $tenant['code'] . "." . $this->table_prefix . "course `c`", "ec.course_id = c.id");
        $sysdb->join($this->table_prefix . "unit `u`", "c.id = u.course_id");
        $sysdb->join($this->table_prefix . "questions `q`", "u.id = q.unit_id");
        $sysdb->where([
            'c.status' => 1,
            'u.status' => 1,
            'q.status' => 1,
            'q.tenant_no' => $tenant['code'],
            'e.pic_1 = ' . $teacherID,
        ]);
        $questionsapp = $sysdb->get()->result_array();

        $eventsapp = [];
        foreach ($tmpapp as $idx => $row) {
            $eventsapp[] = [
                'eventId' => trim($row['event_id']),
                'courseId' => $row['id'],
                'coursecategoryname' => trim($row['coursecategory_name']),
                'coursesubcategoryname' => trim($row['coursesubcategory_name']),
                'weekdays' => str_replace($this->eDate, $this->cDate, trim($row['weekdays'])),
                'starttime' => $this->fixTime(trim($row['starttime'])),
                'endtime' => $this->fixTime(trim($row['endtime'])),
                'coursecode' => trim($row['course_code']),
                'coursename' => trim($row['course_name']),
                'location' => trim($tenant['name']),
                'roomname' => trim($row['room_name']),
            ];
        }

        $data = [];
        $data['events'] = $eventsapp;
        $data['units'] = $unitsapp;
        $data['notes'] = $notesapp;
        $data['questions'] = $questionsapp;
        return $data;
    }

    private function _webLogin($teacherID, $tenant, $db)
    {
        $fields = [
            '`e`.`id` AS event_id',
            '`c`.`id`',
            '`cc`.`name` AS coursecategory_name',
            '`ccs`.`name` AS coursesubcategory_name',
            /* '`u`.`id` as unit_id',
			  '`u`.`title` as unit_name', */
            '`e`.`weekdays`',
            '`ts`.`starttime`',
            '`ts`.`endtime`',
            '`c`.`code` AS course_code',
            '`c`.`name` AS course_name',
            '`r`.`name` AS room_name',
        ];
        $db->select(implode(",", $fields));
        $db->from($this->table_prefix . 'event `e`');
        $db->join('(SELECT `event_id`, `starttime`, `endtime` FROM `' . $this->table_prefix . 'event_timeslot` GROUP BY `event_id`, `starttime`, `endtime`) `ts`', 'e.id = ts.event_id', 'inner', null);
        $db->join($this->table_prefix . 'event_course `ec`', 'e.id = ec.event_id');
        $db->join($this->table_prefix . 'course `c`', 'ec.course_id = c.id');
        $db->join($this->table_prefix . 'course_category `cc`', 'c.course_category_id = cc.id');
        $db->join($this->table_prefix . 'course_category_sub `ccs`', 'c.course_category_sub_id = ccs.id');
        $db->join($this->table_prefix . 'room `r`', 'e.room_id = r.id');
        $db->where('e.pic_1', $teacherID);
        //		$sql = "SELECT " . implode(",", $fields) . " FROM `mas_event` `e` " .
        //			"INNER JOIN (SELECT `event_id`, `starttime`, `endtime` FROM `mas_event_timeslot` GROUP BY `event_id`, `starttime`, `endtime`) `ts` ON `e`.`id` = `ts`.`event_id` " .
        //			"INNER JOIN `mas_event_course` `ec` ON `e`.`id` = `ec`.`event_id` " .
        //			"INNER JOIN `mas_course` `c` ON `ec`.`course_id` = `c`.`id`" .
        //			"INNER JOIN `mas_course_category` `cc` ON `c`.`course_category_id` = `cc`.`id`" .
        //			"INNER JOIN `mas_course_category_sub` `ccs` ON `c`.`course_category_sub_id`=`ccs`.`id`" .
        //			//"INNER JOIN `mas_unit` `u` ON `c`.`id` = `u`.`course_id`" .
        //			"INNER JOIN `mas_room` `r` ON `e`.`room_id` = `r`.`id`" .
        //			"WHERE `e`.`pic_1` = " . $teacherID;
        //log_message('debug',$sql);
        //		$tmp = $db->query($sql)->result_array();
        $tmp = $db->get()->result_array();
        $event = [];
        foreach ($tmp as $idx => $row) {
            $event[] = [
                'event_id' => trim($row['event_id']),
                'course_id' => $row['id'],
                'weekdays' => str_replace($this->eDate, $this->cDate, $row['weekdays']),
                'starttime' => $this->fixTime($row['starttime']),
                'endtime' => $this->fixTime($row['endtime']),
                'course_code' => $row['course_code'],
                'course_name' => $row['course_name'],
                'location' => $tenant['name'],
                'room_name' => $row['room_name'],
            ];
        }
        return $event;
    }

    public function login($username, $password, $device = 'app')
    {
        $result = $this->db->from($this->table_prefix . $this->table)
            ->join($this->table_prefix . 'admin_tenant at', 'a.username=at.username')
            ->where(array($this->short . '.username' => $username, $this->short . '.password' => sha1($password)))
            ->select($this->short . '.id,' . $this->short . '.chi_name,at.tenant_no')
            ->get()
            ->result_array();

        if (empty($result)) {
            throw new Exception('帳號名稱或密碼錯誤！');
        }

        if (count($result) != 1) {
            throw new Exception('抱歉！系統不支援多分校的帳號進行登入。');
        }

        $tenantno = $result[0]['tenant_no'];
        $db = $this->load->database($tenantno, TRUE);
        unset($result);
        $result = $db->select("u.id, u.chi_name, CONCAT(u.eng_firstname, ' ', u.eng_lastname) AS eng_name, r.name AS role_name")
            ->from('rbac_users u')
            ->join('rbac_users_roles ur', 'ur.user_id = u.id')
            ->join('rbac_roles r', 'ur.role_id = r.id')
            ->where(['u.username' => $username])
            ->get()
            ->row_array();
        if (empty($result)) {
            throw new Exception('發生錯誤！系統在分校資料庫找不到相關數據。');
        }

        $data = array(
            'userinfo' => array(
                'id' => $result['id'],
                'name' => is_null($result['chi_name']) ? $result['eng_name'] : $result['chi_name'],
                'tenant_no' => $tenantno,
            )
        );

        $this->load->model('Tenant_model', 'tenants_model');
        //         log_message('debug','tenantno:'.$tenantno);//main
        $tenant = $this->tenants_model->detail_no($tenantno);
        //        log_message('debug','tenantname:'.$tenant['name']);

        switch ($device) {
            case "app":
                $event = $this->_appLogin($result['id'], $tenant, $db);
                $data['eventsapp'] = $event['events'];
                $data['unitsapp'] = $event['units'];
                $data['notesapp'] = $event['notes'];
                $data['questionsapp'] = $event['questions'];
                break;
            default:
                $data['event'] = $this->_webLogin($result['id'], $tenant, $db);
                break;
        }
        return $data;
    }

    private function fixTime($time)
    {
        list($hour, $min) = explode(":", $time);
        return sprintf("%02d", $hour) . ":" . sprintf("%02d", $min);
    }

    public function listing()
    {
        $this->tenant();

        return $this->getAll($this->database, $this->tbl, ['id', 'username', 'display_name', 'chi_name', 'eng_name', 'eng_firstname', 'eng_lastname', 'gender', 'mobile', 'email'], ['status' => 1]);
    }

    public function listing_selection()
    {
        $this->tenant();

        return $this->db->from('rbac_users')
            ->join('rbac_users_roles', 'rbac_users.id = rbac_users_roles.user_id')
            ->join('rbac_roles', 'rbac_users_roles.role_id = rbac_roles.id')
            ->group_start()
            ->like('rbac_roles.name', 'teacher')
            ->or_like('rbac_roles.name', 'Teacher')
            ->group_end()
            ->group_by('rbac_users.id')
            ->select('rbac_users.*')
            ->get()
            ->result_array();
    }

    public function detail($id)
    {
        $tenantNo = $this->getTenantNo();
        if (!$tenantNo) {
            $tenantNo = $this->session->userdata('tenant_no');
        }
        return  $this->getOne($tenantNo, 'rbac_users', ['display_name', 'chi_name', 'eng_firstname', 'eng_lastname', 'eng_name'], $id, 'id', false);
    }

    public function detail_custom($id)
    {
        $this->tenant();

        return $this->db->from('rbac_users')
            ->join('rbac_users_roles', 'rbac_users.id = rbac_users_roles.user_id')
            ->join('rbac_roles', 'rbac_users_roles.role_id = rbac_roles.id')
            ->group_start()
            ->like('rbac_roles.name', 'teacher')
            ->or_like('rbac_roles.name', 'Teacher')
            ->group_end()
            ->where('rbac_users.id', $id)
            ->get()
            ->row_array();
    }

    public function detail_chiName($chiName)
    {
        $this->tenant();

        return $this->db->from('rbac_users')
            ->join('rbac_users_roles', 'rbac_users.id = rbac_users_roles.user_id')
            ->join('rbac_roles', 'rbac_users_roles.role_id = rbac_roles.id')
            ->group_start()
            ->like('rbac_roles.name', 'teacher')
            ->or_like('rbac_roles.name', 'Teacher')
            ->group_end()
            ->group_start()
            ->where('rbac_users.chi_name', $chiName)
            ->or_where(sprintf('CONCAT(rbac_users.eng_firstname, " ", rbac_users.eng_lastname) = "%s"', $chiName), null, false)
            ->group_end()
            ->group_by('rbac_users.id')
            ->select('rbac_users.*')
            ->get()
            ->row_array();
    }

   /* public function save($params, $mode, $id = 0)
    {
        if ($mode === 'c') {
            $this->create($params);
        } else {
            $this->update($params, $id);
        }
        return $this->db->insert_id();
    }*/

    public function all()
    {
        $fields = [$this->short . '.id', $this->short . '.chi_name as name', $this->short . '.status'];
        return $this->getAll($this->database, $this->table, $fields, $this->short . '.chi_name is not null');
    }

    public function allInTenant()
    {
        $fields = ['id', 'chi_name as name', 'status'];
        return $this->getAll($this->session->userdata('tenant_no'), 'rbac_users', $fields, 'chi_name is not null', false);
    }
}
