<?php

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

defined('BASEPATH') or exit('No direct script access allowed');

/**
 * @property Tenant_model $Tenant_model
 * @property Student_model $Student_model
 */
class Shift_approval_model extends MY_Model
{

    use Tenants;

    public $short = "sm";
    public $table;
    public $tbl;
    public $database;
    public $status;

    public function __construct()
    {
        parent::__construct();
        $this->database = $this->getTenantNo();
        $this->tbl = 'student_makeup';
        $this->table = $this->tbl . ' ' . $this->short;
        $this->status = ['pending' => '待批', 'approved' => '批准', 'rejected' => '拒批'];
    }

    public function detail($id)
    {
        $this->db = $this->load->database($this->database, true);
        return $this->db->from($this->table_prefix . $this->tbl)->where('id', $id)->get()->row_array();
    }

    public function totalCount()
    {
        $db = $this->load->database($this->database, TRUE);
        $db->from($this->table_prefix . $this->table);
        return $db->count_all_results();
    }

    public function filteredCount($param)
    {
        /*
        $fields = [
            $this->short . '.id',
            $this->short . '.status',
            $this->short . '.from_item_id',
            $this->short . '.to_item_id',
            $this->short . '.student_id',
            's.chi_name',
            'Concat((SELECT c.name FROM `mas_student_makeup` main
join mas_event_enrollment_item ei on main.from_item_id=ei.id
join mas_event_course ec on ei.event_id=ec.event_id
join mas_course c on ec.course_id=c.id where main.id=' . $this->short . '.id)," ",(select concat(from_unixtime(et.startdate,"%Y-%m-%d")," ",et.starttime) from mas_event_timeslot et join mas_student_makeup main on et.id=main.from_item_id where main.id=' . $this->short . '.id)) as fromitemdate',
            'Concat((SELECT c.name FROM `mas_student_makeup` main
join mas_event_enrollment_item ei on main.to_item_id=ei.id
join mas_event_course ec on ei.event_id=ec.event_id
join mas_course c on ec.course_id=c.id where main.id=' . $this->short . '.id)," ",(select concat(from_unixtime(et.startdate,"%Y-%m-%d")," ",et.starttime) from mas_event_timeslot et join mas_student_makeup main on et.id=main.to_item_id where main.id=' . $this->short . '.id)) as toitemdate',
        ];
*/
        $fields = [
            $this->short . '.id',
            $this->short . '.status',
            $this->short . '.from_item_id',
            $this->short . '.to_item_id',
            $this->short . '.student_id',
            's.chi_name',
            'CONCAT(e.display_name, " ", FROM_UNIXTIME(et.startdate,"%Y-%m-%d"), " ", et.starttime) AS fromitemdate',
            'CONCAT(e1.display_name, " ", FROM_UNIXTIME(et1.startdate,"%Y-%m-%d"), " ", et1.starttime) AS toitemdate',
        ];
        $orders = [];
        foreach ($param['order'] as $order) {
            $colIdx = $order['column'];
            $colName = $colName = str_replace(array('chi_name', 'eng_name', 'display_name'), array('s.chi_name', 'a.eng_name', 'a.display_name'), $param['columns'][$colIdx]['data']);
            $dir = strtoupper($order['dir']);
            $orders[] = ($colName === 's.chi_name' || $colName === 'a.eng_name' || $colName === 'a.display_name' || $colName === 'fromitemdate' || $colName === 'toitemdate') ? $colName . ' ' . $dir : $this->short . "." . $colName . ' ' . $dir;
        }
        //        $tenant_no = $this->database_prefix . $this->session->userdata('tenant_no');
        $student_table = sprintf("%sstudent s", $this->table_prefix);
        $event_enrollment_item_table = sprintf("%sevent_enrollment_item ei", $this->table_prefix);
        $event_timeslot_table = sprintf("%sevent_timeslot et", $this->table_prefix);
        $event_table = sprintf("%sevent e", $this->table_prefix);
        $event_timeslot_table1 = sprintf("%sevent_timeslot et1", $this->table_prefix);
        $event_table1 = sprintf("%sevent e1", $this->table_prefix);
        $joins = [
            $student_table => $this->short . '.student_id = s.id',
            $event_enrollment_item_table => $this->short . '.from_item_id = ei.id',
            $event_timeslot_table => 'ei.event_timeslot_id = et.id',
            $event_table => 'ei.event_id = e.id',
            $event_timeslot_table1 => 'sm.to_item_id = et1.id',
            $event_table1 => 'et1.event_id = e1.id',
        ];
        $db = $this->load->database($this->database, TRUE);
        $tenantno = $this->session->userdata('tenant_no');
        $db->distinct();
        $db->select(implode(', ', $fields));
        $db->from($this->table_prefix . $this->table);
        if ($this->database == 'default') {
            list($dummy, $shortname) = explode(" ", $this->table);
            $db->like($shortname . '.tenant_no', $tenantno, 'none');
        }
        foreach ($joins as $joinTbl => $condition) {
            $db->join($joinTbl, $condition);
        }
        $search = [];
        foreach ($param['columns'] as $column) {
            if ($column['searchable'] === "true" && $column['search']['value'] !== '') {
                $colName = $column['data'];
                $searchfield = ($colName === 's.chi_name' || $colName === 'a.eng_name' || $colName === 'a.display_name') ? $colName : $this->short . "." . $colName;
                $search[$searchfield] = $column['search']['value'];
            }
        }
        if (!empty($search)) {
            foreach ($search as $k => $v) {
                //                 log_message('debug','@filtercount@ k:'.$k.';v:'.$v);
                if ($v !== '') {
                    $db->like($search, 'none');
                }
            }
        }
        return count($db->get()->result_array());
    }

    public function listing($param)
    {
        /*
         * <th>編號</th>
          <th>調堂者</th>
          <th>原本課堂及日期</th>
          <th>調至課堂及日期</th>
          <th>調堂狀態</th>
          <th>批核</th>
         */
        /*
        $fields = [
            $this->short . '.id',
            $this->short . '.status',
            $this->short . '.from_item_id', //event enrollment item id
            $this->short . '.to_item_id', //event_timeslot id
            $this->short . '.student_id',
            's.chi_name',
            'Concat((SELECT c.name FROM `mas_student_makeup` main
join mas_event_enrollment_item ei on main.from_item_id=ei.id
join mas_event_course ec on ei.event_id=ec.event_id
join mas_course c on ec.course_id=c.id where main.id=' . $this->short . '.id)," ",(select concat(from_unixtime(et.startdate,"%Y-%m-%d")," ",et.starttime) from mas_event_timeslot et join mas_student_makeup main on et.id=main.from_item_id where main.id=' . $this->short . '.id)) as fromitemdate',
            'Concat((SELECT c.name FROM `mas_student_makeup` main
join mas_event_timeslot ei on main.to_item_id=ei.id
join mas_event_course ec on ei.event_id=ec.event_id
join mas_course c on ec.course_id=c.id where main.id=' . $this->short . '.id)," ",(select concat(from_unixtime(et.startdate,"%Y-%m-%d")," ",et.starttime) from mas_event_timeslot et join mas_student_makeup main on et.id=main.to_item_id where main.id=' . $this->short . '.id)) as toitemdate',
        ];
*/
        $fields = [
            $this->short . '.id',
            $this->short . '.status',
            $this->short . '.from_item_id', //event enrollment item id
            $this->short . '.to_item_id', //event_timeslot id
            $this->short . '.student_id',
            's.chi_name',
            'CONCAT(e.display_name, " ", FROM_UNIXTIME(et.startdate,"%Y-%m-%d"), " ", et.starttime) AS fromitemdate',
            'CONCAT(e1.display_name, " ", FROM_UNIXTIME(et1.startdate,"%Y-%m-%d"), " ", et1.starttime) AS toitemdate',
        ];
        $orders = [];
        foreach ($param['order'] as $order) {
            $colIdx = $order['column'];
            $colName = $colName = str_replace(array('chi_name', 'eng_name', 'display_name'), array('s.chi_name', 'a.eng_name', 'a.display_name'), $param['columns'][$colIdx]['data']);
            $dir = strtoupper($order['dir']);
            $orders[] = ($colName === 's.chi_name' || $colName === 'a.eng_name' || $colName === 'a.display_name' || $colName === 'fromitemdate' || $colName === 'toitemdate') ? $colName . ' ' . $dir : $this->short . "." . $colName . ' ' . $dir;
        }
        //        log_message('debug',implode(',',$orders));
        $start = $param['start'];
        $length = $param['length'];
        $tenant_no = $this->session->userdata('tenant_no');
        $student_table = sprintf("%sstudent s", $this->table_prefix);
        $event_enrollment_item_table = sprintf("%sevent_enrollment_item ei", $this->table_prefix);
        $event_timeslot_table = sprintf("%sevent_timeslot et", $this->table_prefix);
        $event_table = sprintf("%sevent e", $this->table_prefix);
        $event_timeslot_table1 = sprintf("%sevent_timeslot et1", $this->table_prefix);
        $event_table1 = sprintf("%sevent e1", $this->table_prefix);
        $joins = [
            $student_table => $this->short . '.student_id = s.id',
            $event_enrollment_item_table => $this->short . '.from_item_id = ei.id',
            $event_timeslot_table => 'ei.event_timeslot_id = et.id',
            $event_table => 'ei.event_id = e.id',
            $event_timeslot_table1 => 'sm.to_item_id = et1.id',
            $event_table1 => 'et1.event_id = e1.id',
        ];
        $search = [];
        foreach ($param['columns'] as $column) {
            if ($column['searchable'] === "true" && $column['search']['value'] !== '') {
                $colName = $column['data'];
                $searchfield = ($colName === 's.chi_name' || $colName === 'a.eng_name' || $colName === 'a.display_name') ? $colName : $this->short . "." . $colName;
                $search[$searchfield] = $column['search']['value'];
                //                $search[$searchfield] = str_replace(['事假','病假'],['0','1'], $column['search']['value']);
                //                log_message('debug','searchfield:'.$searchfield.';searchvalue:'.$column['search']['value']);// searchfield:l.type;searchvalue:1
            }
        }
        $result = $this->getFromDBFilter($this->database, $this->table, $fields, $start, $length, $orders, $search, $joins);
        $data = [];

        foreach ($result as $row) {
            $status = trim($row['status']);
            $data[] = [
                'id' => trim($row['id']),
                'chi_name' => trim($row['chi_name']),
                'fromitemdate' => trim($row['fromitemdate']),
                'toitemdate' => trim($row['toitemdate']),
                //                'filepath' =>"<a href='static/uploads/$tenant_no/shifts/".trim($row['filename'])."' target='_blank'>".trim($row['filename'])."</a>",
                //                'startdate' => CommonHelper::getFormattedDate(trim($row['startdate'])),
                //                'enddate'=>CommonHelper::getFormattedDate(trim($row['enddate'])),
                'status' => $this->status[$status],
                'details' => $status === 'pending' ? '<button class="details approve"  data-fromitemid="' . trim($row['from_item_id']) . '" data-toitemid="' . trim($row['to_item_id']) . '" data-studentid="' . trim($row['student_id']) . '" data-id="' . trim($row['id']) . '" data-tenantno="' . $this->session->userdata('tenant_no') . '" data-adminid="' . $this->session->userdata('userid') . '">批准</button>&nbsp;&nbsp;<button class="details reject" data-id="' . trim($row['id']) . '">拒批</button>' : '',
                //                'typeval'=>$this->type[trim($row['type'])].'_'.trim($row['type']),
                //                'shiftstatus'=>$this->status[$status].'_'.$status,
            ];
        }
        //        log_message('debug','data count:'.count($data));
        return $data;
    }

    //overwrite
    public function getFromDBFilter($database, $table, $fields, $start, $length, $orders, $search, $joins = [], $keywordfieldname = '')
    {
        $db = $this->load->database($database, TRUE);
        $tenantno = $this->session->userdata('tenant_no');
        $db->distinct();
        $db->select(implode(', ', $fields));
        $db->from($this->table_prefix . $table);
        if ($database == 'default') {
            list($dummy, $shortname) = explode(" ", $table);
            $db->like($shortname . '.tenant_no', $tenantno, 'none');
        }

        if (count($joins) > 0) {
            foreach ($joins as $joinTbl => $condition) {
                $db->join($joinTbl, $condition);
            }
        }

        //        log_message('debug','not empty search?'.!empty($search));
        if (!empty($search)) {
            foreach ($search as $k => $v) {
                //                log_message('debug','@getfromfilter@ k:'.$k.';v:'.$v);//k:l.type;v:1
                if ($v !== '') {
                    $db->like($search, 'none');
                }
            }
        }

        $db->order_by(implode(', ', $orders));
        if ($start > -1) {
            $db->limit($length, $start);
        }
        //        log_message('debug','sql:'.$db->get_compiled_select());
        /*
         * SELECT DISTINCT `sm`.`id`, `sm`.`status`, `s`.`chi_name`

, Concat((SELECT c.name FROM `mas_student_makeup` main
          join mas_event_enrollment_item ei on main.from_item_id=ei.id
          join mas_event_course ec on ei.event_id=ec.event_id
          join mas_course c on ec.course_id=c.id where main.id=sm.id), " ", (select concat(from_unixtime(et.startdate, "%Y-%m-%d"), " ", et.starttime) from mas_event_timeslot et join mas_student_makeup main on et.id=main.from_item_id where main.id=sm.id)) as fromitemdate
          
          , Concat((SELECT c.name FROM `mas_student_makeup` main
          join mas_event_timeslot ei on main.to_item_id=ei.id
          join mas_event_course ec on ei.event_id=ec.event_id
          join mas_course c on ec.course_id=c.id where main.id=sm.id), " ", (select concat(from_unixtime(et.startdate, "%Y-%m-%d"), " ", et.starttime) from mas_event_timeslot et join mas_student_makeup main on et.id=main.to_item_id where main.id=sm.id)) as toitemdate
          
          FROM `mas_student_makeup` `sm`
          JOIN `mas_student` `s` ON `sm`.`student_id`=`s`.`id`
          ORDER BY `sm`.`id` DESC
          LIMIT 10
         */
        return $db->get()->result_array();
    }

    public function insert($params)
    {
        $id = $this->insertDB($this->database, $this->tbl, $params);

        /* Auto generate shift_approval_code */
        $unitCode = sprintf("%05d", $params['unit_id']);
        $_digit = 5;
        $codePrefix = 'NOTE';
        $sql = sprintf("UPDATE `%sshift_approval` SET `shift_approval_code` = UCASE(" .
            "   CONCAT ('%s', " .
            "       LPAD(" .
            "           CONVERT(" .
            "               SUBSTRING(" .
            "                   IFNULL((SELECT * FROM (SELECT MAX(`shift_approval_code`) FROM `%sshift_approval` WHERE `shift_approval_code` LIKE '%s%%') AS `temp`), 0)," .
            "               -%s)," .
            "           UNSIGNED INTEGER) + 1," .
            "       %s, 0)" .
            "   )) " .
            "WHERE `id` = %s", $this->table_prefix, $codePrefix . $unitCode, $this->table_prefix, $codePrefix . $unitCode, $_digit, $_digit, $id);
        $this->db->query($sql);
        $this->db->where('id', $id);
        $this->db->select('shift_approval_code');
        $newCode = $this->db->get($this->table_prefix . 'shift_approval')->result_array()[0]['shift_approval_code'];
        if (!is_null($params['ext'])) {
            $update = [];
            $update['filename'] = $newCode . "." . $params['ext'];
            $this->db->where('id', $id);
            $this->db->update($this->table_prefix . 'shift_approval', $update);
        }
        return $newCode;
    }

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

    public function one($id)
    {
        $data = $this->getOne($this->database, $this->tbl, [], $id);
        $this->load->model('Unit_model');
        $unit = $this->Unit_model->one($data['unit_id']);
        $this->load->model('Course_model');
        $course = $this->Course_model->detail($unit['course_id'], ['course_category_id', 'course_category_sub_id']);
        $data = array_merge(['course_id' => $unit['course_id']], $data);
        return array_merge($data, $course);
    }

    public function save($params, $id)
    {
        $this->saveDB($this->database, $this->tbl, $params, $id);
    }

    public function deleteMultiple($ids)
    {
        $this->deleteRelatedDB($this->database, array('shift_approval_price', $this->tbl), array('shift_approval_id', 'id'), $ids);
    }

    public function deleteAll()
    {
        $this->deleteAllDB($this->database, 'shift_approval_price');
        $this->deleteAllDB($this->database, $this->tbl);
    }

    public function getAllIds()
    {
        $ids = [];
        $all = $this->all();
        foreach ($all as $k => $v) {
            $ids[] = $v['id'];
        }
        return $ids;
    }

    public function getUnits($courseId)
    {
        $this->load->model('Unit_model');
        return $this->Unit_model->all($courseId);
    }

    public function getCategories()
    {
        $this->load->model('Course_category_model');
        return $this->Course_category_model->all();
    }

    public function getSubCategories($categoryId)
    {
        $this->load->model('Course_subcategory_model');
        return $this->Course_subcategory_model->all($categoryId);
    }

    public function getCourses($categoryId, $subcategoryId)
    {
        $this->load->model('Course_model');
        return $this->Course_model->all($categoryId, $subcategoryId);
    }

    public function unsent_messages()
    {
        $this->db = $this->load->database($this->database, true);

        $this->load->model('Tenant_model');
        $tenant = $this->Tenant_model->detail_no($this->getTenantNo());

        $table = $this->table_prefix . $this->tbl;
        return $this->db
            ->from($table)
            ->join($this->table_prefix . 'student s', $table . '.student_id = s.id')
            ->where('sent', 0)
            ->select(
                $table . '.*,
                "' . $tenant['name'] . '" as tenant_name,
                s.student_no, 
                s.chi_name as student_chi_name, 
                s.eng_name as student_eng_name',
                false
            )
            ->get()
            ->result_array();
    }

    // public function trigger_push_notification($id)
    // {
    //     $tenant_no = $this->getTenantNo();

    //     $existing = $this->getOne($this->database, $this->tbl, [], $id);

    //     $this->load->model('Student_model');
    //     $this->Student_model->setTenantNo($tenant_no);
    //     $student = $this->Student_model->getOne($this->Student_model->database, $this->Student_model->tbl, ['id', 'chi_name', 'eng_name'], $existing['student_id']);

    //     $this->load->model('Tenant_model');
    //     $tenant = $this->Tenant_model->getOne($this->Tenant_model->database, $this->Tenant_model->tbl, ['name'], $tenant_no, 'code');

    //     $this->push_notification($data = [
    //         'title' => '學生調課批核通知',
    //         'body' => sprintf(
    //             '學生 %s 已在 %s 分校 %s 申請請假，申請%s',
    //             !empty($student['chi_name']) ? $student['chi_name'] :  $student['eng_name'],
    //             $tenant['name'],
    //             date('Y-m-d', $existing['from_datetime']),
    //             $existing['status'] == 'approved' ? '已被接納。' : '已被拒絕。'
    //         )
    //     ], __CLASS__, $existing['id'], [$existing['student_id']]);
    // }
}
