<?php

class Rpt_roombooking2 extends MY_Controller {

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

        $this->load->model('script_m');
        $this->load->model('report_m');
        $this->load->model('roomquery_m');

        $s_imeeting['activemenu'] = '009';
        $this->session->set_userdata('s_imeeting', $s_imeeting);
    }

    function create_tmp_table() {
        $query = $this->db->query("DROP TEMPORARY TABLE IF EXISTS tmp_static;");
        $sql = "CREATE TEMPORARY TABLE IF NOT EXISTS tmp_static
            (
                `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
                `name` TEXT NULL ,
                `rdate` DATE NULL ,
                `amount` INT NULL 
             ) ENGINE = MYISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;";
        $this->db->query($sql);
        return true;
    }

    function index() {
        $data['extraHeadContent'] = $this->script_m->calendar();
        $data['extraHeadContent'].= $this->script_m->textformat();

        $first_date = strftime('%Y') . '-' . strftime('%m') . '-01';
        $data['start_date'] = $this->mydate->dateToText($first_date);
        $data['end_date'] = $this->mydate->lastDate();

        $data['page_title'] = "รายงานสถิติการใช้ห้องประชุม แสดงตามหน่วยงาน";
        $data['content'] = 'report/roomstatic_rpt_v';
        $this->load->view('template_v', $data);
    }

    function preview() {
        $this->create_tmp_table();

        $start_date = $this->mydate->dateToMySql($this->input->post('start_date'));
        $end_date = $this->mydate->dateToMySql($this->input->post('end_date'));
        $rptstyle = $this->input->post('rptstyle');

        $sql = "SELECT tbreserv.meet_name, tbreserv.rdate, tbteam.name FROM tbreserv ";
        $sql.= " Inner Join tbmember ON tbreserv.member_id = tbmember.id Inner Join tbdepart ON tbmember.depart_id = tbdepart.id";
        $sql.= " Inner Join tbteam ON tbdepart.team_id = tbteam.id WHERE tbreserv.status='1' ";
        $sql.= " And (tbreserv.rdate >= '$start_date' And tbreserv.rdate <= '$end_date')";
        $query = $this->db->query($sql)->result_array();
        foreach ($query as $r) {
            $data = array(
                'name' => $r['name'],
                'rdate' => $r['rdate']
            );
            $this->db->insert('tmp_static', $data);
        }


        $sql = "SELECT tbreserv.rdate, tbteam.name, tbbooking.rdate 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 tbteam ON tbdepart.team_id = tbteam.id ";
        $sql.= " Inner Join tbbooking ON tbbooking.doc_id = tbreserv.id WHERE tbreserv.status='1'";
        $sql.= " And (tbbooking.rdate >= '$start_date' And tbbooking.rdate <= '$end_date')";
        $query = $this->db->query($sql)->result_array();
        foreach ($query as $r) {
            $data = array(
                'name' => $r['name'],
                'rdate' => $r['rdate']
            );
            $this->db->insert('tmp_static', $data);
        }

        $tbl = '<div class="caption"><b>รายงานสถิติการใช้ห้องประชุม แสดงตามหน่วยงาน</b></div>';
        $tbl.= '<div class="caption_rpt"><b>ประจำวันที่ </b>' . $this->mydate->dateToText($start_date) . " <b>ถึง</b> " . $this->mydate->dateToText($end_date) . "</div>";
        //สร้างรายงาน
        //**********ส่วนหัววันที่**********
        $tbl.= "<table id='tbl_1'>";
        $tbl.="<thead>";
        $tbl.="<tr>";
        $tbl.="<th width='160'>หน่วยงาน</th>";
        $date = $start_date;
        while (strtotime($date) <= strtotime($end_date)) {
            $tbl.="<th>" . $this->mydate->get_dateMySql($date) . "</th>";
            $date = date("Y-m-d", strtotime("+1 day", strtotime($date)));
        }
        $tbl.="<th width='60' class='center'>รวม</th>";
        $tbl.="</tr>";
        $tbl.="</thead>";
        $tbl.="<tbody>";
        //*****************************
        //ส่วนรายละเอียด
        $this->db->order_by('name', 'asc');
        $team = $this->db->get('tbteam')->result_array();
        foreach ($team as $r) {
            $tbl.="<tr>";
            $tbl.="<td>" . $r['name'] . "</td>";

            $date = $start_date;
            $total = 0;
            while (strtotime($date) <= strtotime($end_date)) {
                $this->db->where('rdate', $date);
                $this->db->where('name', $r['name']);
                $rcount = $this->db->get('tmp_static')->num_rows();
                $total+=$rcount;

                if ($rcount == 0)
                    $rcount = '&nbsp;';
                $tbl.="<td class='center'>$rcount</td>";

                $date = date("Y-m-d", strtotime("+1 day", strtotime($date)));
            }
            $tbl.="<td class='center'>" . number_format($total) . "</td>";
            $tbl.="</tr>";
        }

        //แถวสุดท้าย เป็นผลรวม
        $tbl.="<tr>";
        $tbl.="<td><b>รวมทั้งหมด</b></td>";
        $date = $start_date;
        $total = 0;
        while (strtotime($date) <= strtotime($end_date)) {
            $this->db->where('rdate', $date);
            $rcount = $this->db->get('tmp_static')->num_rows();
            $total+=$rcount;

            if ($rcount == 0)
                $rcount = '&nbsp;';
            $tbl.="<td class='center'><b>$rcount</b></td>";

            $date = date("Y-m-d", strtotime("+1 day", strtotime($date)));
        }
        $tbl.="<td class='center'><b>" . number_format($total) . "</b></td>";
        $tbl.="</tr>";
        $tbl.="</tbody>";
        $tbl.="</table>";
        
        if ($rptstyle == '0') {
            $this->mpdf = new mPDF('th');
            $this->mpdf->useGraphs = true;
            $this->mpdf->WriteHTML($this->report_m->get_css_pdf(), 1);
            $this->mpdf->WriteHTML($tbl);
            $this->mpdf->Output();
        } else {
            $data['tbl'] = $tbl;
            $this->load->view('report/excel_template', $data);
        }
    }
    
    function room_static() {
        $data['extraHeadContent'] = $this->script_m->calendar();
        $data['extraHeadContent'].= $this->script_m->textformat();

        $first_date = strftime('%Y') . '-' . strftime('%m') . '-01';
        $data['start_date'] = $this->mydate->dateToText($first_date);
        $data['end_date'] = $this->mydate->lastDate();

        $data['page_title'] = "รายงานสถิติการใช้ห้องประชุม แสดงตามห้องประชุม";
        $data['content'] = 'report/roomstatic_room_rpt_v';
        $this->load->view('template_v', $data);
    }

    function room_static_preview() {
        $this->create_tmp_table();

        $start_date = $this->mydate->dateToMySql($this->input->post('start_date'));
        $end_date = $this->mydate->dateToMySql($this->input->post('end_date'));
        $rptstyle = $this->input->post('rptstyle');

        $sql = "SELECT tbroom.name, tbreserv.rdate FROM tbroom Inner Join tbreserv ON tbreserv.room_id = tbroom.id";
        $sql.= " WHERE tbreserv.status='1' And (tbreserv.rdate >= '$start_date' And tbreserv.rdate <= '$end_date')";
        $query = $this->db->query($sql)->result_array();
        foreach ($query as $r) {
            $data = array(
                'name' => $r['name'],
                'rdate' => $r['rdate']
            );
            $this->db->insert('tmp_static', $data);
        }

        $sql = "SELECT tbbooking.rdate, tbroom.name FROM tbreserv Inner Join tbbooking ON tbbooking.doc_id = tbreserv.id";
        $sql.= " Inner Join tbroom ON tbbooking.room_id = tbroom.id WHERE tbreserv.status='1'";
        $sql.= " And (tbbooking.rdate >= '$start_date' And tbbooking.rdate <= '$end_date')";
        $query = $this->db->query($sql)->result_array();
        foreach ($query as $r) {
            $data = array(
                'name' => $r['name'],
                'rdate' => $r['rdate']
            );
            $this->db->insert('tmp_static', $data);
        }

        $tbl = '<div class="caption"><b>รายงานสถิติการใช้ห้องประชุม แสดงตามห้องประชุม</b></div>';
        $tbl.= '<div class="caption_rpt"><b>ประจำวันที่ </b>' . $this->mydate->dateToText($start_date) . " <b>ถึง</b> " . $this->mydate->dateToText($end_date) . "</div>";
        //สร้างรายงาน
        //**********ส่วนหัววันที่**********
        $tbl.= "<table>";
        $tbl.="<thead>";
        $tbl.="<tr>";
        $tbl.="<th width='160'>ห้องประชุม</th>";
        $date = $start_date;
        while (strtotime($date) <= strtotime($end_date)) {
            $tbl.="<th>" . $this->mydate->get_dateMySql($date) . "</th>";
            $date = date("Y-m-d", strtotime("+1 day", strtotime($date)));
        }
        $tbl.="<th width='60' class='center'>รวม</th>";
        $tbl.="</tr>";
        $tbl.="</thead>";
        $tbl.="<tbody>";
        //*****************************
        //ส่วนรายละเอียด
        $this->db->order_by('name', 'asc');
        $team = $this->db->get('tbroom')->result_array();
        foreach ($team as $r) {
            $tbl.="<tr>";
            $tbl.="<td>" . $r['name'] . "</td>";

            $date = $start_date;
            $total = 0;
            while (strtotime($date) <= strtotime($end_date)) {
                $this->db->where('rdate', $date);
                $this->db->where('name', $r['name']);
                $rcount = $this->db->get('tmp_static')->num_rows();
                $total+=$rcount;

                if ($rcount == 0)
                    $rcount = '&nbsp;';
                $tbl.="<td class='center'>$rcount</td>";

                $date = date("Y-m-d", strtotime("+1 day", strtotime($date)));
            }
            $tbl.="<td class='center'>" . number_format($total) . "</td>";
            $tbl.="</tr>";
        }

        //แถวสุดท้าย เป็นผลรวม
        $tbl.="<tr>";
        $tbl.="<td><b>รวมทั้งหมด</b></td>";
        $date = $start_date;
        $total = 0;
        while (strtotime($date) <= strtotime($end_date)) {
            $this->db->where('rdate', $date);
            $rcount = $this->db->get('tmp_static')->num_rows();
            $total+=$rcount;

            if ($rcount == 0)
                $rcount = '&nbsp;';
            $tbl.="<td class='center'><b>$rcount</b></td>";

            $date = date("Y-m-d", strtotime("+1 day", strtotime($date)));
        }
        $tbl.="<td class='center'><b>" . number_format($total) . "</b></td>";
        $tbl.="</tr>";
        $tbl.="</tbody>";
        $tbl.="</table>";

        if ($rptstyle == '0') {
            $this->mpdf = new mPDF('th');
            $this->mpdf->WriteHTML($this->report_m->get_css_pdf(), 1);
            $this->mpdf->WriteHTML($tbl);
            $this->mpdf->Output();
        } else {
            $data['tbl'] = $tbl;
            $this->load->view('report/excel_template', $data);
        }
    }

}

?>
