<?php

class Carquery_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 = '/carquery/ajax_get_grid', $member_id = '') {
        $this->create_tmp_table();

        $sql = "SELECT tbcarreserv.id, tbcarreserv.name, tbcarreserv.rdate, tbcarreserv.start_time, tbcarreserv.end_time,";
        $sql.= " tbcarreserv.status, tbcar.code as car_code, tbdepart.name as depart_name FROM tbcarreserv ";
        $sql.= " Inner Join tbcar ON tbcarreserv.car_id = tbcar.id ";
        $sql.= " Inner Join tbmember ON tbcarreserv.member_id = tbmember.id ";
        $sql.= " Inner Join tbdepart ON tbmember.depart_id = tbdepart.id ";
        if ($status == '99') {
            $sql.= " Where tbcarreserv.status <> '99'";
        } else {
            $sql.= " Where tbcarreserv.status = '$status'";
        }

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

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


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

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

        $result = $this->db->query($sql)->result_array();

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

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

        $this->db->limit($per_page, $offset);
        $this->db->order_by('rdate', 'asc');
        $query = $this->db->get('tmp_carquery')->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_carquery;");
        $sql = "CREATE TEMPORARY TABLE IF NOT EXISTS tmp_carquery
            (
                `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
                `doc_id` INT NULL ,
                `name` TEXT NULL ,
                `rdate` DATE NULL ,
                `start_time` VARCHAR( 10 ) NULL ,
                `end_time` VARCHAR( 10 ) NULL ,
                `car_code` 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 tbcarreserv.id, tbcarreserv.name, tbcarreserv.rdate, tbcarreserv.start_time, tbcarreserv.end_time,";
        $sql.= " tbcarreserv.status, tbcar.code as car_code, tbdepart.name as depart_name FROM tbcarreserv ";
        $sql.= " Inner Join tbcar ON tbcarreserv.car_id = tbcar.id ";
        $sql.= " Inner Join tbmember ON tbcarreserv.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 tbcarreserv.status = '1' And tbteam.name ='$team_name'";
        $sql.=" And (tbcarreserv.rdate >= '$start_date' And tbcarreserv.rdate <= '$end_date')";
        $result = $this->db->query($sql)->result_array();

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


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

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

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

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

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

}

?>
