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

class Laporan_sos_m extends CI_Model {
    var $table = 'sos_data';
    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(sos_data.id_karyawan) AS jml_sos");
            $this->db->group_by("sos_data.id_karyawan");
        }

        if($id_karyawan != "0") {
            $this->db->where("sos_data.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("sos_data.id,
                            sos_data.tanggal,
                            sos_data.keterangan,
                            sos_data.tgl_input,
                            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','sos_data.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("sos_data.id_company", $this->id_company);
        $this->db->group_start();
        $this->db->where("DATE_FORMAT(sos_data.tanggal,'%Y-%m-%d') >=", $tgl_mulai);
        $this->db->where("DATE_FORMAT(sos_data.tanggal,'%Y-%m-%d') <=", $tgl_selesai);
        $this->db->group_end();
        $this->db->from($this->table);

        $column_search = array('sos_data.keterangan');
        $i = 0;

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

                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('','sos_data.tanggal','data_karyawan.nik','sos_data.keterangan');

        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, sos_data.tanggal ASC");
            }else{
                $this->db->order_by("sos_data.tanggal DESC");
            }
        }
    }

    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;

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

                if (!isset($array[$value->id_karyawan]['sos_data'][$value->waktu])) {
					$a['tanggal']    = date('d-m-Y', strtotime($value->tanggal));
					$a['keterangan'] = $value->keterangan;
                    $array[$value->id_karyawan]['sos_data'][$value->tgl_input] = $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;
    }

    public function delete($id=null){
        $predir_file = $this->db->select("file")
                                        ->from('sos_file')
                                        ->where("md5(id_sos)",$id)
                                        ->where("id_company",$this->id_company)
                                        ->get()
                                        ->row("file");

        $this->db->where(array('md5(id)'=>$id,'id_company'=>$this->id_company));
        $delete =$this->db->delete($this->table);
        if($delete){
            $this->fungsi->delete_fileupload($predir_file);
            return  __response_delete(true);
        }else{
            return  __response_delete(false);
        }
    }
}
