<?php

class Roomquery_m extends CI_Model {

    function __construct() {
        parent::__construct();
        $this->load->library('mydate');
    }

    function get_all($start_date, $end_date, $status, $fieldsearch, $txtsearch, $offset, $per_page = 20, $url = '/roomquery/ajax_get_grid', $member_id = '') {
        $this->create_tmp_table();

        $sql = "SELECT tbreserv.id, tbreserv.meet_name, tbreserv.rdate, tbreserv.start_time, tbreserv.end_time,";
        $sql.= " tbreserv.status, tbroom.name as room_name, tbdepart.name as depart_name FROM tbreserv ";
        $sql.= " Inner Join tbroom ON tbreserv.room_id = tbroom.id ";
        $sql.= " Inner Join tbmember ON tbreserv.member_id = tbmember.id ";
        $sql.= " Inner Join tbdepart ON tbmember.depart_id = tbdepart.id ";
        if ($status == '99') {
            $sql.= " Where tbreserv.status <> '99'";
        } else {
            $sql.= " Where tbreserv.status = '$status'";
        }

        $sql.=" And (tbreserv.rdate >= '$start_date' And tbreserv.rdate <= '$end_date')";
        if ($txtsearch != '') {
            $sql.= " And ($fieldsearch like '%$txtsearch%')";
        }

        if ($member_id != '') {
            $sql.= " And (tbreserv.member_id='$member_id')";
        }
        $result = $this->db->query($sql)->result_array();

        foreach ($result as $r) {
            $data = array(
                'doc_id' => $r['id'],
                'meet_name' => $r['meet_name'],
                'rdate' => $r['rdate'],
                'start_time' => $r['start_time'],
                'end_time' => $r['end_time'],
                'room_name' => $r['room_name'],
                'depart_name' => $r['depart_name'],
                'status_text' => $this->get_status($r['status']),
            );
            $this->db->insert('tmp_roomquery', $data);
        }


        //loop ตารางเพิ่มวันจอง
        $sql = "SELECT tbreserv.id, tbreserv.meet_name, tbdepart.name as depart_name, tbbooking.rdate, tbbooking.start_time,";
        $sql.= " tbbooking.end_time,tbroom.name as room_name,tbreserv.status FROM tbreserv Inner Join tbmember ON tbreserv.member_id = tbmember.id";
        $sql.= " Inner Join tbdepart ON tbmember.depart_id = tbdepart.id Inner Join tbbooking ON tbbooking.doc_id = tbreserv.id";
        $sql.= " Inner Join tbroom ON tbbooking.room_id = tbroom.id";
        if ($status == '99') {
            $sql.= " Where tbreserv.status <> '99'";
        } else {
            $sql.= " Where tbreserv.status = '$status'";
        }

        $sql.=" And (tbbooking.rdate >= '$start_date' And tbbooking.rdate <= '$end_date')";
        if ($txtsearch != '') {
            $sql.= " And ($fieldsearch like '%$txtsearch%')";
        }
        if ($member_id != '') {
            $sql.= " And (tbreserv.member_id='$member_id')";
        }
        $result = $this->db->query($sql)->result_array();

        foreach ($result as $r) {
            $data = array(
                'doc_id' => $r['id'],
                'meet_name' => $r['meet_name'],
                'rdate' => $r['rdate'],
                'start_time' => $r['start_time'],
                'end_time' => $r['end_time'],
                'room_name' => $r['room_name'],
                'depart_name' => $r['depart_name'],
                'status_text' => $this->get_status($r['status']),
            );
            $this->db->insert('tmp_roomquery', $data);
        }

        $total = $this->db->get('tmp_roomquery')->num_rows();

        $this->db->limit($per_page, $offset);
        $this->db->order_by('rdate', 'asc');
        $query = $this->db->get('tmp_roomquery')->result_array();

        $this->load->library('pagination');
        $data['pagination'] = $this->pagination->pagin($total, $url, $per_page);
        $data['query'] = $query;
        return $data;
    }

    //สร้างตารางชั่วคราว
    function create_tmp_table() {
        $query = $this->db->query("DROP TEMPORARY TABLE IF EXISTS tmp_roomquery;");
        $sql = "CREATE TEMPORARY TABLE IF NOT EXISTS tmp_roomquery
            (
                `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
                `doc_id` INT NULL ,
                `meet_name` TEXT NULL ,
                `rdate` DATE NULL ,
                `start_time` VARCHAR( 10 ) NULL ,
                `end_time` VARCHAR( 10 ) NULL ,
                `room_name` TEXT NULL ,
                `depart_name` TEXT NULL,
                `status_text` TEXT NULL 
             ) ENGINE = MYISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;";
        $this->db->query($sql);
        return true;
    }

    function get_status($status) {
        $st_text = "รออนุมัติ";
        switch ($status) {
            case '1':
                $st_text = "อนุมัติ";
                break;
            case '2':
                $st_text = "ไม่อนุมัติ";
                break;
            case '3':
                $st_text = "ยกเลิกจอง";
                break;
        }

        return $st_text;
    }

    //ดึงตามหน่วยงานที่เลือก
    function get_allbyteam($start_date, $end_date, $team_name) {
        $this->create_tmp_table();

        $sql = "SELECT tbreserv.id, tbreserv.meet_name, tbreserv.rdate, tbreserv.start_time, tbreserv.end_time,";
        $sql.= " tbreserv.status, tbroom.name as room_name, tbdepart.name as depart_name, tbteam.name as team_name FROM tbreserv ";
        $sql.= " Inner Join tbroom ON tbreserv.room_id = tbroom.id ";
        $sql.= " Inner Join tbmember ON tbreserv.member_id = tbmember.id ";
        $sql.= " Inner Join tbdepart ON tbmember.depart_id = tbdepart.id ";
        $sql.= " Inner Join tbteam ON tbdepart.team_id = tbteam.id ";
        $sql.= " Where tbreserv.status = '1' And tbteam.name ='$team_name'";

        $sql.=" And (tbreserv.rdate >= '$start_date' And tbreserv.rdate <= '$end_date')";
        $result = $this->db->query($sql)->result_array();

        foreach ($result as $r) {
            $data = array(
                'doc_id' => $r['id'],
                'meet_name' => $r['meet_name'],
                'rdate' => $r['rdate'],
                'start_time' => $r['start_time'],
                'end_time' => $r['end_time'],
                'room_name' => $r['room_name'],
                'depart_name' => $r['team_name'],
                'status_text' => $this->get_status($r['status']),
            );
            $this->db->insert('tmp_roomquery', $data);
        }


        //loop ตารางเพิ่มวันจอง
        $sql = "SELECT tbreserv.id, tbreserv.meet_name, tbdepart.name as depart_name, tbbooking.rdate, tbbooking.start_time,";
        $sql.= " tbbooking.end_time,tbroom.name as room_name,tbreserv.status,tbteam.name as team_name FROM tbreserv ";
        $sql.= " Inner Join tbmember ON tbreserv.member_id = tbmember.id";
        $sql.= " Inner Join tbdepart ON tbmember.depart_id = tbdepart.id ";
        $sql.= " Inner Join tbbooking ON tbbooking.doc_id = tbreserv.id";
        $sql.= " Inner Join tbroom ON tbbooking.room_id = tbroom.id";
        $sql.= " Inner Join tbteam ON tbdepart.team_id = tbteam.id ";
        $sql.= " Where tbreserv.status = '1' And tbteam.name ='$team_name'";
        $sql.=" And (tbreserv.rdate >= '$start_date' And tbreserv.rdate <= '$end_date')";
        $result = $this->db->query($sql)->result_array();

        foreach ($result as $r) {
            $data = array(
                'doc_id' => $r['id'],
                'meet_name' => $r['meet_name'],
                'rdate' => $r['rdate'],
                'start_time' => $r['start_time'],
                'end_time' => $r['end_time'],
                'room_name' => $r['room_name'],
                'depart_name' => $r['team_name'],
                'status_text' => $this->get_status($r['status']),
            );
            $this->db->insert('tmp_roomquery', $data);
        }

        $total = $this->db->get('tmp_roomquery')->num_rows();

        $this->db->limit($per_page, $offset);
        $this->db->order_by('rdate', 'asc');
        $query = $this->db->get('tmp_roomquery')->result_array();

        $this->load->library('pagination');
        $data['pagination'] = $this->pagination->pagin($total, $url, $per_page);
        $data['query'] = $query;
        return $data;
    }

}

?>
