<?php
if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Laporan_patroli_m extends CI_Model {
    var $table = 'patroli_trx';
    public function __construct(){
        parent::__construct();
    }

    function get_datatables($tgl_mulai=0, $tgl_selesai=0, $id_karyawan=0, $id_cabang=0, $id_departemen=0, $rekap=false){
        $this->_get_datatables_query($tgl_mulai, $tgl_selesai, $id_karyawan, $id_cabang, $id_departemen,$rekap);
        if($_GET['length'] != -1){
            $this->db->limit($_GET['length'], $_GET['start']);
        }
        $query = $this->db->get();
        return $query->result();
    }

    function count_filtered($tgl_mulai=0, $tgl_selesai=0, $id_karyawan=0, $id_cabang=0, $id_departemen=0, $rekap=false){
        $this->_get_datatables_query($tgl_mulai, $tgl_selesai, $id_karyawan, $id_cabang, $id_departemen,$rekap);
        $query = $this->db->get();
        return $query->num_rows();
    }

    public function count_all($tgl_mulai=0, $tgl_selesai=0, $id_karyawan=0, $id_cabang=0, $id_departemen=0, $rekap=false){
        $this->_get_datatables_query($tgl_mulai, $tgl_selesai, $id_karyawan, $id_cabang, $id_departemen,$rekap);
        return $this->db->count_all_results();
    }
    private function _get_datatables_query($tgl_mulai=0, $tgl_selesai=0, $id_karyawan=0, $id_cabang=0, $id_departemen=0, $rekap=false, $download=false){
        if($rekap == true){
            $this->db->select("count(patroli_trx.id_karyawan) AS jml_patroli");
            $this->db->group_by("patroli_trx.id_karyawan");
        }

        if($id_karyawan != "0") {
            $this->db->where("patroli_trx.id_karyawan", $id_karyawan);
        }else{
            if($this->level_user == "7"){
                $this->db->where("data_karyawan.supervisi", $this->id_karyawan);
            }else if($this->level_user == "3"){
                $this->db->where("data_karyawan.id_karyawan", $this->id_karyawan);
            }
        }

        if($id_cabang != "0") {
            $this->db->where('data_karyawan.id_cabang', $id_cabang);
        }else{
            if($this->level_user == "5"){
                if($this->multi_cabang== true){
                    $this->db->where_in('data_karyawan.id_cabang', $this->id_cabang_arr);
                }else{
                    $this->db->where('data_karyawan.id_cabang', $this->id_cabang);
                }
            }else if($this->level_user == "2"){
                $this->db->where('data_karyawan.id_cabang', $this->id_cabang);
            }
        }

        if($id_departemen != "0") {
            $this->db->where('data_karyawan.id_departemen', $id_departemen);
        }else{
            if($this->level_user == "2"){
                if($this->multi_departemen == true){
                    $this->db->where_in('data_karyawan.id_departemen', $this->id_departemen_arr);
                }else{
                    $this->db->where('data_karyawan.id_departemen', $this->id_departemen);
                }
            }
        }

        $this->db->select("patroli_trx.id_patroli,
                            patroli_trx.tgl_cp,
                            patroli_trx.nm_jadwal as patroli,
                            patroli_trx.min_absen,
                            patroli_trx.max_absen,
                            patroli_trx.nm_lokasi,
                            patroli_trx.nm_qr,
                            patroli_trx.gmt,
                            patroli_trx.latitude,
                            patroli_trx.longitude,
                            patroli_trx.path_foto,
                            patroli_trx.ket_pat,
                            data_karyawan.id_karyawan,
                            data_karyawan.nama_lengkap,
                            data_karyawan.nik,
                            master_cabang.kode as kode_cabang,
                            master_cabang.nama as nama_cabang,
                            master_departemen.kode as kode_departemen,
                            master_departemen.nama as nama_departemen,
                            master_jabatan.kode_jabatan,
                            master_jabatan.nama as nama_jabatan");
        $this->db->join('data_karyawan','patroli_trx.id_karyawan  = data_karyawan.id_karyawan');
        $this->db->join('master_cabang','master_cabang.id_cabang  = data_karyawan.id_cabang');
        $this->db->join('master_departemen','master_departemen.id_departemen  = data_karyawan.id_departemen',"LEFT");
        $this->db->join('master_jabatan','master_jabatan.id_jabatan  = data_karyawan.id_jabatan');
        $this->db->where("patroli_trx.id_company", $this->id_company);
        $this->db->group_start();
        $this->db->where("DATE_FORMAT(patroli_trx.tgl_cp,'%Y-%m-%d') >=", $tgl_mulai);
        $this->db->where("DATE_FORMAT(patroli_trx.tgl_cp,'%Y-%m-%d') <=", $tgl_selesai);
        $this->db->group_end();
        $this->db->from($this->table);

        $column_search = array('patroli_trx.ket_pat');
        $i = 0;

        foreach ($column_search as $item){  // looping awal
            if($_GET['search']['value']){ // jika datatable mengirimkan pencarian dengan metode POST

                if($i===0){ // looping awal
                    $this->db->group_start();
                    $this->db->like($item, $_GET['search']['value']);
                }else{
                    $this->db->or_like($item, $_GET['search']['value']);
                }

                if(count($column_search) - 1 == $i){
                    $this->db->group_end();
                }
            }
            $i++;
        }

        $column_order = array('','patroli_trx.tgl_cp','','','','','','');

        if(isset($_GET['order'])){
            $this->db->order_by($column_order[$_GET['order']['0']['column']], $_GET['order']['0']['dir']);
        }
        else{
            // $order_by = array("data_aktivitas.waktu"=>$order);
            if($download){
                $this->db->order_by("master_cabang.flag ASC, master_cabang.kode ASC, master_departemen.kode ASC, master_jabatan.kode_jabatan ASC, data_karyawan.nama_lengkap ASC, patroli_trx.tgl_cp ASC");
            }else{
                $this->db->order_by("patroli_trx.tgl_cp, patroli_trx.min_absen DESC");
            }
        }
    }

    public function getDataById($id=null){
        $select = $this->db->query("SELECT *
                                    FROM $this->table
                                    WHERE MD5(id_patroli) = '$id'
                                    AND id_company = '".$this->id_company."'")->row();
        return $select;
    }

    public function lokasiAktivitas($tgl_mulai=0, $tgl_selesai=0, $id_karyawan=0, $id_cabang=0, $id_departemen=0){
        if($id_karyawan != "0") {
            $this->db->where("patroli_trx.id_karyawan", $id_karyawan);
        }else{
            if($this->level_user == "7"){
                $this->db->where("data_karyawan.supervisi", $this->id_karyawan);
            }else if($this->level_user == "3"){
                $this->db->where("data_karyawan.id_karyawan", $this->id_karyawan);
            }
        }

        if($id_cabang != "0") {
            $this->db->where('data_karyawan.id_cabang', $id_cabang);
        }else{
            if($this->level_user == "5"){
                if($this->multi_cabang== true){
                    $this->db->where_in('data_karyawan.id_cabang', $this->id_cabang_arr);
                }else{
                    $this->db->where('data_karyawan.id_cabang', $this->id_cabang);
                }
            }else if($this->level_user == "2"){
                $this->db->where('data_karyawan.id_cabang', $this->id_cabang);
            }
        }

        if($id_departemen != "0") {
            $this->db->where('data_karyawan.id_departemen', $id_departemen);
        }else{
            if($this->level_user == "2"){
                if($this->multi_departemen == true){
                    $this->db->where_in('data_karyawan.id_departemen', $this->id_departemen_arr);
                }else{
                    $this->db->where('data_karyawan.id_departemen', $this->id_departemen);
                }
            }
        }

        $this->db->select("data_karyawan.nama_lengkap AS nama_karyawan,
                            data_karyawan.nik,
                            patroli_trx.tgl_cp,
                            patroli_trx.gmt,
                            patroli_trx.ket_pat,
                            patroli_trx.nm_lokasi,
                            patroli_trx.latitude,
                            patroli_trx.longitude
                            ");
        $this->db->join('data_karyawan','patroli_trx.id_karyawan  = data_karyawan.id_karyawan');
        $this->db->where("patroli_trx.id_company", $this->id_company);
        $this->db->group_start();
        $this->db->where("DATE_FORMAT(patroli_trx.tgl_cp,'%Y-%m-%d') >=", date_format(date_create($tgl_mulai),"Y-m-d"));
        $this->db->where("DATE_FORMAT(patroli_trx.tgl_cp,'%Y-%m-%d') <=", date_format(date_create($tgl_selesai),"Y-m-d"));
        $this->db->group_end();
        $this->db->from("patroli_trx");

        $query = $this->db->get();
        return $query->result();
    }

    function download($tanggal_mulai="0", $tanggal_selesai="0", $id_karyawan="0", $id_cabang="0", $id_departemen="0"){
        $this->_get_datatables_query($tanggal_mulai, $tanggal_selesai, $id_karyawan, $id_cabang, $id_departemen, $rekap=false, $download=true);
        $query = $this->db->get();
        $result = $query->result();

        $array = [];
        if ($result != null) {
            foreach ($result as $key => $value) {
                if (!isset($array[$value->id_karyawan])) {
                    $d['id_karyawan']       = $value->id_karyawan;
                    $d['nik']               = $value->nik;
                    $d['nama_karyawan']     = $value->nama_lengkap;
                    $d['kode_cabang']       = $value->kode_cabang;
                    $d['nama_cabang']       = $value->nama_cabang;
                    $d['kode_departemen']   = $value->kode_departemen;
                    $d['nama_departemen']   = $value->nama_departemen;
                    $d['kode_jabatan']      = $value->kode_jabatan;
                    $d['nama_jabatan']      = $value->nama_jabatan;

                    $array[$value->id_karyawan] = ['data_karyawan' => $d];
                }

                if (!isset($array[$value->id_karyawan]['data_patroli'][$value->tgl_cp])) {
                    $a['waktu']      = date('d-m-Y H:i', strtotime($value->tgl_cp))." ".$this->fungsi->_gmt($value->gmt);
                    $a['tgl']        = date('d-m-Y', strtotime($value->tgl_cp));
                    $a['jam']        = date('H:i', strtotime($value->tgl_cp));
                    $a['min_absen']  = date('H:i', strtotime($value->min_absen));
                    $a['max_absen']  = date('H:i', strtotime($value->max_absen));
                    $a['patroli']    = $value->patroli;
                    $a['lokasi']     = $value->nm_lokasi;
                    $a['qr']         = $value->nm_qr;
                    $a['latitude']   = $value->latitude;
                    $a['longitude']  = $value->longitude;
                    $a['keterangan'] = $value->ket_pat;
                    $array[$value->id_karyawan]['data_patroli'][$value->tgl_cp] = $a;
                }
            }
        }

        // echo '<pre>' . var_export('jml karyawan : '.count($array), true) . '</pre>';
        // echo '<pre>' . var_export($array, true) . '</pre>';
        // exit;
        if (count($array) > 0) {
            $res = $array;
        } else {
            $res = null;
        }

        return $res;
    }

    function download2($tanggal_mulai="0", $tanggal_selesai="0", $id_karyawan="0", $id_cabang="0", $id_departemen="0"){
        $this->_get_datatables_query($tanggal_mulai, $tanggal_selesai, $id_karyawan, $id_cabang, $id_departemen, $rekap=false);
        $query = $this->db->get();
        $result = $query->result();
        return $result;
    }

    public function delete($id=null){
        $this->db->where(array('md5(id_patroli)'=>$id, 'id_company'=>$this->id_company));
        $delete =$this->db->delete($this->table);
        return  __response_delete($delete);
    }
}