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

class Laporan_aktivitas_m extends CI_Model {
    var $table = 'data_aktivitas';
    public function __construct(){
        parent::__construct();
        $this->level_user           = $this->session->userdata('level_user');
        $this->id_company           = $this->session->userdata('id_company');
        $this->multi_cabang         = $this->session->userdata('multi_cabang');
        $this->id_cabang            = $this->session->userdata('id_cabang');
        $this->id_cabang_arr        = $this->session->userdata('id_cabang_arr');
        $this->multi_departemen     = $this->session->userdata('multi_departemen');
        $this->id_departemen        = $this->session->userdata('id_departemen');
        $this->id_departemen_arr    = $this->session->userdata('id_departemen_arr');
        $this->id_karyawan          = $this->session->userdata('id_karyawan');
    }

    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($_POST['length'] != -1){
            $this->db->limit($_POST['length'], $_POST['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(data_aktivitas.id_karyawan) AS jml_aktivitas");
            $this->db->group_by("data_aktivitas.id_karyawan");
        }

        if($id_karyawan != "0") {
            $this->db->where("data_aktivitas.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_aktivitas.id_aktivitas,
                            data_aktivitas.waktu,
                            data_aktivitas.gmt,
                            data_aktivitas.keterangan,
                            data_aktivitas.lokasi,
                            data_aktivitas.latitude,
                            data_aktivitas.longitude,
                            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','data_aktivitas.id_karyawan  = data_karyawan.id_karyawan');
        $this->db->join('master_cabang','master_cabang.id_cabang  = data_karyawan.id_cabang',"LEFT");
        $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',"LEFT");
        $this->db->where("data_aktivitas.id_company", $this->id_company);
        $this->db->group_start();
        $this->db->where("DATE_FORMAT(data_aktivitas.waktu,'%Y-%m-%d') >=", $tgl_mulai);
        $this->db->where("DATE_FORMAT(data_aktivitas.waktu,'%Y-%m-%d') <=", $tgl_selesai);
        $this->db->group_end();
        $this->db->from($this->table);

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

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

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

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

        $column_order = array('','data_aktivitas.waktu','data_karyawan.nik','data_aktivitas.keterangan');

        if(isset($_POST['order'])){
            $this->db->order_by($column_order[$_POST['order']['0']['column']], $_POST['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, data_aktivitas.waktu ASC");
            }else{
                $this->db->order_by("data_aktivitas.waktu DESC");
            }
        }
    }

    public function getDataById($id=null){
        $select = $this->db->query("SELECT *
                                    FROM $this->table
                                    WHERE MD5(id_aktivitas) = '$id'
                                    AND id_company = '".$_SESSION["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("data_aktivitas.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,
                            data_aktivitas.waktu,
                            data_aktivitas.gmt,
                            data_aktivitas.keterangan,
                            data_aktivitas.lokasi,
                            data_aktivitas.latitude,
                            data_aktivitas.longitude
                            ");
        $this->db->join('data_karyawan','data_aktivitas.id_karyawan  = data_karyawan.id_karyawan');
        $this->db->where("data_aktivitas.id_company", $this->id_company);
        $this->db->group_start();
        $this->db->where("DATE_FORMAT(data_aktivitas.waktu,'%Y-%m-%d') >=", date_format(date_create($tgl_mulai),"Y-m-d"));
        $this->db->where("DATE_FORMAT(data_aktivitas.waktu,'%Y-%m-%d') <=", date_format(date_create($tgl_selesai),"Y-m-d"));
        $this->db->group_end();
        $this->db->from("data_aktivitas");

        $query = $this->db->get();
        // echo $this->db->last_query();
        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;

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

                if (!isset($array[$value->id_karyawan]['data_aktivitas'][$value->waktu])) {
                    $a['waktu']                 = date('d-m-Y H:i:s', strtotime($value->waktu))." ".$this->fungsi->_gmt($value->gmt);
                    $a['lokasi']                = $value->lokasi;
                    $a['keterangan']            = $value->keterangan;
                    $array[$value->id_karyawan]['data_aktivitas'][$value->waktu] = $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){
        $this->db->where(array('md5(id_aktivitas)'=>$id, 'id_company'=>$this->id_company));
        $delete =$this->db->delete($this->table);
        return  __response_delete($delete);
    }
}