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

class Pengajuan_lembur_m extends CI_Model {
    var $table = 'lembur_mulai';

    public function __construct()
    {
        parent::__construct();
    }

    function get_datatables($tgl_mulai=0, $tgl_selesai=0, $status=0, $id_karyawan=0, $id_cabang=0, $id_departemen=0){
        $this->_get_datatables_query($tgl_mulai, $tgl_selesai, $status, $id_karyawan, $id_cabang, $id_departemen);
        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, $status=0, $id_karyawan=0, $id_cabang=0, $id_departemen=0){
        $this->_get_datatables_query($tgl_mulai, $tgl_selesai, $status, $id_karyawan, $id_cabang, $id_departemen);
        $query = $this->db->get();
        return $query->num_rows();
    }

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

    private function _get_datatables_query($tgl_mulai=0, $tgl_selesai=0, $status=0, $id_karyawan=0, $id_cabang=0, $id_departemen=0, $order = null){

        if($id_karyawan != "0") {
            $this->db->where("lembur_mulai.id_karyawan", $id_karyawan);
        }else{
            if($this->level_user == "7"){
                $this->db->where("data_karyawan.supervisi", $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);
                }
            }
        }
        if($this->level_user == "3"){
            $this->db->where_in('lembur_mulai.status', $status['appv_sts']);
        }else{
            $this->db->group_start();
            if($this->level_user == '1'){
                $this->db->where_in($status['appv_lvl'], $status['appv_sts']);
                $this->db->or_where_in('lembur_mulai.status', $status['appv_sts']);
                $this->db->or_where('lembur_mulai.id_karyawan', $this->id_karyawan);
            }else{
                $this->db->where_in($status['appv_lvl'], $status['appv_sts']);
                if(!in_array($status['appv_flt'], ['1', '2']))
                {
                    if($status['appv_flt'] == '0')
                    {
                        $status['appv_sts'] = ['4'];
                    }
                    $this->db->or_where_in('lembur_mulai.status', $status['appv_sts']);
                }

            }
            $this->db->group_end();
        }

        $this->db->select("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,
                            data_karyawan.id_jabatan,
                            data_karyawan.id_cabang,
                            master_jabatan.level_user,
                            lembur_mulai.id_lemmulai,
                            lembur_mulai.id_karyawan,
                            lembur_mulai.tgl_absen,
                            lembur_mulai.jam_absen,
                            lembur_mulai.lokasi_absen,
                            lembur_mulai.timezone,
                            lembur_mulai.gmt,
                            lembur_mulai.latitude,
                            lembur_mulai.longitude,
                            lembur_mulai.keterangan,
                            lembur_mulai.foto,
                            lembur_mulai.id_company,
                            lembur_mulai.approve,
                            lembur_mulai.status,
                            lembur_mulai.approval_spv,
                            lembur_mulai.approval_kedep,
                            lembur_mulai.approval_kacab,
                            lembur_mulai.approval_hrd,
                            lembur_mulai.approval_direksi,
                            lembur_mulai.user_spv,
                            lembur_mulai.user_kedep,
                            lembur_mulai.user_kacab,
                            lembur_mulai.user_hrd,
                            lembur_mulai.user_direksi,
                            lembur_mulai.tgl_apv_spv,
                            lembur_mulai.tgl_apv_kedep,
                            lembur_mulai.tgl_apv_kacab,
                            lembur_mulai.tgl_apv_hrd,
                            lembur_mulai.tgl_apv_direksi,
                            lembur_selesai.id_lemselesai,
                            lembur_selesai.jam_absen AS jam_lembur_selesai,
                            lembur_selesai.lokasi_absen AS ls_lokasi,
                            lembur_selesai.keterangan AS ls_keterangan,
                            p_approval_lembur.level_approval");
        $this->db->from($this->table);
        $this->db->join('lembur_selesai', 'lembur_selesai.id_lemmulai=lembur_mulai.id_lemmulai');
        $this->db->join('data_karyawan','lembur_mulai.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->join('p_approval_lembur', 'p_approval_lembur.id_cabang=data_karyawan.id_cabang AND p_approval_lembur.level_user=master_jabatan.level_user','LEFT');

        $this->db->where("lembur_mulai.id_company", $this->id_company);
        $this->db->where("lembur_mulai.tgl_absen >=", $tgl_mulai);
        $this->db->where("lembur_mulai.tgl_absen <=", $tgl_selesai);



        $column_search = array('data_karyawan.nama_lengkap');
        $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('','lembur_mulai.id_karyawan','lembur_mulai.tgl_absen','lembur_mulai.jam_absen');

        if(isset($_GET['order'])){
            $this->db->order_by($column_order[$_GET['order']['0']['column']], $_GET['order']['0']['dir']);
        }
        else{
            if($order == null){
                $order_by = array("lembur_mulai.id_lemmulai"=>"DESC");
            }else{
                $order_by = $order;
            }
            $this->db->order_by(key($order_by), $order_by[key($order_by)]);
        }
    }

    function download($tgl_mulai="0", $tgl_selesai="0", $status="0", $id_karyawan="0", $id_cabang="0", $id_departemen="0"){
        // echo $tgl_mulai."<br>";
        // echo $tgl_selesai."<br>";
        // print_r($status)."<br>";
        // echo $id_karyawan."<br>";
        // echo $id_cabang."<br>";
        // echo $id_departemen."<br>";
        // echo $order;
        // die();
        $order = ["lembur_mulai.tgl_absen"=>"ASC"];
        $this->_get_datatables_query($tgl_mulai, $tgl_selesai, $status, $id_karyawan, $id_cabang, $id_departemen, $order);
        $query = $this->db->get();
        $result = $query->result();
        //0=tidak perlu persetujuan, 1=pengajuan, 2=dibatalkan, 3=ditolak, 4=disetujui, 5=proses
        //0=tidak perlu persetujuan, 1=pengajuan, 2=dibatalkan, 3=ditolak, 4=disetujui, 5=proses
        $status_aprroval = ["0" => "",
                            "1" => $this->lang->line('proses'),
                            "2" => $this->lang->line('dibatalkan'),
                            "3" => $this->lang->line('ditolak'),
                            "4" => $this->lang->line('disetujui'),
                            "5" => $this->lang->line('menunggu')
                        ];

        $status = [ "0" => "",
                    "1" => $this->lang->line('belum_disetujui'),
                    "2" => $this->lang->line('dibatalkan'),
                    "3" => $this->lang->line('ditolak'),
                    "4" => $this->lang->line('disetujui'),
                    "5" => $this->lang->line('belum_disetujui')
                ];

        $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_lembur'][$value->jam_absen])) {
                    $a['tgl_absen']             = date('d-m-Y', strtotime($value->tgl_absen));

                    $a['lm_jam']        = date_format(date_create($value->jam_absen),"d-m-Y H:i");
                    $a['lm_lokasi']     = $value->lokasi_absen;
                    $a['lm_keterangan'] = $value->keterangan;

                    $a['ls_jam']        = date_format(date_create($value->jam_lembur_selesai),"d-m-Y H:i");
                    $a['ls_lokasi']     = $value->ls_lokasi;
                    $a['ls_keterangan'] = $value->ls_keterangan;

                    $a['apv_spv']       = $status_aprroval[$value->approval_spv];
                    $a['apv_kedep']     = $status_aprroval[$value->approval_kedep];
                    $a['apv_kacab']     = $status_aprroval[$value->approval_kacab];
                    $a['apv_direksi']   = $status_aprroval[$value->approval_direksi];
                    $a['apv_hrd']       = $status_aprroval[$value->approval_hrd];
                    $a['status']        = $status[$value->status];

                    $array[$value->id_karyawan]['data_lembur'][$value->jam_absen] = $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 getDataById($id=null){
        $slq = 'lembur_mulai.id_lemmulai, lembur_mulai.id_karyawan, lembur_mulai.tgl_absen, lembur_mulai.jam_absen, lembur_mulai.lokasi_absen, lembur_mulai.timezone, lembur_mulai.gmt, lembur_mulai.latitude, lembur_mulai.longitude,
                lembur_mulai.keterangan, lembur_mulai.foto, lembur_mulai.id_company, lembur_mulai.approve, lembur_mulai.status, lembur_mulai.approval_spv, lembur_mulai.approval_kedep, lembur_mulai.approval_kacab, lembur_mulai.approval_hrd,
                lembur_mulai.approval_direksi, lembur_mulai.user_spv, lembur_mulai.user_kedep, lembur_mulai.user_kacab, lembur_mulai.user_hrd, lembur_mulai.user_direksi, lembur_mulai.tgl_apv_spv, lembur_mulai.tgl_apv_kedep, lembur_mulai.tgl_apv_kacab,
                lembur_mulai.tgl_apv_hrd, lembur_mulai.tgl_apv_direksi, lembur_mulai.tgl_input';

        $select = $this->db->query("SELECT ".$slq."
                                    FROM $this->table
                                    WHERE MD5(id_lemmulai) = '$id'
                                    AND id_company = '".$_SESSION["id_company"]."'")->row();
        return $select;
    }

    public function delete($id_lemmulai=null, $status, $id_karyawan, $tgl_absen, $jam_lembur_mulai){

        if ($status != '4') {
            $this->db->delete('lembur_selesai', array('md5(id_lemmulai)' => $id_lemmulai));
            $this->db->where(array('md5(id_lemmulai)'=>$id_lemmulai));
            $delete = $this->db->delete($this->table);
        }else{
            $this->db->delete('lembur_selesai', array('md5(id_lemmulai)' => $id_lemmulai));
            $this->db->delete('lembur_mulai', array('md5(id_lemmulai)'=> $id_lemmulai));
            $this->db->where(array('id_company' => $this->id_company, 'id_karyawan' => $id_karyawan, 'tgl_lembur' => $tgl_absen, 'Date_format(lembur_mulai,"%H:%i:%s")' => $jam_lembur_mulai));
            $delete = $this->db->delete('r_lembur');
        }
        $this->logger->create_log('pengajuan_lembur', 'delete', $this->db->last_query());
        return  __response_delete($delete);
    }


    public function delete_lembur_selesai($id=null){
        $delete =$this->db->delete('lembur_selesai', array('id_company' => $this->id_company,'md5(id_lemmulai)' => $id));
        $this->logger->create_log('lembur_selesai', 'delete', $this->db->last_query());
        return  __response_delete($delete);
    }

    function list_karyawan($search = null){

        $this->db->select('data_karyawan.id_karyawan,
                            data_karyawan.nik,
                            data_karyawan.nama_lengkap,
                            data_karyawan.id_company,
                            data_karyawan.id_cabang,
                            data_karyawan.id_departemen');
        $this->db->where('data_karyawan.id_company', $this->id_company);

        if($this->level_user == "5"){ # KEPALA CABANG
            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"){ # KEPALA DEPARTEMEN
            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);
            }
        }else if($this->level_user == "7"){ # SUPERVISI
            $this->db->where('data_karyawan.supervisi', $this->id_karyawan);
        }else if($this->level_user == "3"){ # STAFF
            $this->db->where('data_karyawan.id_karyawan', $this->id_karyawan);
        }

        $this->db->group_start();
        $this->db->like('data_karyawan.nama_lengkap', $search);
        $this->db->or_like('data_karyawan.nik', $search);
        $this->db->group_end();
        $this->db->order_by('data_karyawan.nama_lengkap', 'ASC');

        $this->db->join('master_jabatan', 'master_jabatan.id_jabatan = data_karyawan.id_jabatan');
        $this->db->where_not_in('master_jabatan.level_user', ['1', '6']);

        $result = $this->db->get('data_karyawan')->result();
        $json = [];
        foreach ($result as $rows) {
            $json[] = ['id'=>$rows->id_karyawan,
                        'idcompany'=>$rows->id_company,
                        'idcabang'=>$rows->id_cabang,
                        'iddepartemen'=>$rows->id_departemen,
                        'text'=>$rows->nik." - ".$rows->nama_lengkap];
        }
        echo json_encode($json);
    }

    public function get_karyawan($cabang = null, $departemen = null, $search = null){
        $id_company = $this->session->userdata('id_company');

        $this->db->select('nama_lengkap, id_karyawan, nik');
        $this->db->where('data_karyawan.id_company', $id_company);
        $this->db->where('id_cabang', $cabang);
        $this->db->where('id_departemen', $departemen);

        if (in_array($this->session->userdata('level_user'), array("7"))) {
            $this->load->model('Db_model', 'db_model');
            $id_kar = $this->session->userdata('id_karyawan');
            $karyawan = $this->db->query("SELECT id_karyawan FROM data_karyawan WHERE supervisi = '$id_kar'")->result();

            $target_name_arr = array();
            foreach ($karyawan as $dat) {
                $target_name_arr[] = $dat->id_karyawan;
            }
            $target_id = implode("','",$target_name_arr);
            /*var_dump($target_id);
            exit();*/
            $this->db->where_in('id_karyawan',$target_name_arr);
        }

        $this->db->group_start();
        $this->db->like('nama_lengkap', $search);
        $this->db->or_like('nik', $search);
        $this->db->group_end();
        $this->db->order_by('nama_lengkap', 'ASC');

        $result = $this->db->get('data_karyawan')->result();
        $this->db->join('master_jabatan', 'master_jabatan.id_jabatan = data_karyawan.id_jabatan');
        $this->db->where_not_in('master_jabatan.level_user', ['1', '6']);

        /*echo $this->db->last_query();
        exit();*/
        $json = [];
        //$json[] = ['id'=> 0, 'text'=>'Semua Cabang'];
        foreach ($result as $rows) {
          $json[] = ['id'=>$rows->id_karyawan, 'text'=>$rows->nik." - ".$rows->nama_lengkap];
        }
        echo json_encode($json);
    }

    public function get_cabang($id = null, $search = null){
        $id_company = $this->session->userdata('id_company');
        $id_cabang  = $this->session->userdata('id_cabang');
        $level      = $this->session->userdata('level_user');

        $this->db->select('nama, id_cabang, kode');
        $this->db->where('id_company', $id_company);

        if ($level == 5 || $level == 2 || $level == 3 || $level == 7) {
            $this->db->where('id_cabang', $id_cabang);
        }

        $this->db->group_start();
        $this->db->like('nama', $search);
        $this->db->or_like('kode', $search);
        $this->db->group_end();
        $this->db->order_by('id_cabang', 'ASC');

        $result = $this->db->get('master_cabang')->result();
        $json = [];
        //$json[] = ['id'=> 0, 'text'=>'Semua Cabang'];
        foreach ($result as $rows) {
          $json[] = ['id'=>$rows->id_cabang, 'text'=>$rows->kode." - ".$rows->nama];
        }
        echo json_encode($json);
    }

    public function get_departemen($id = null, $search = null){
        $id_company     = $this->session->userdata('id_company');
        $id_departemen  = $this->session->userdata('id_departemen');
        $level          = $this->session->userdata('level_user');

        $this->db->select('nama, id_departemen, kode');
        $this->db->where('id_company', $id_company);

        if ($level == 2 || $level == 3 || $level == 7) {
            $this->db->where('id_departemen', $id_departemen);
        }

        if ($id != null) {
            $dep = array();
            $get_departemen = $this->db->query("SELECT id_departemen FROM data_karyawan WHERE id_cabang = '$id' GROUP BY id_departemen");

            if ($get_departemen->num_rows() > 0) {
                foreach ($get_departemen->result_array() as $key) {
                    $dep[] = $key['id_departemen'];
                }
            }else{
                $dep[] = 0;
            }

            $this->db->where_in('id_departemen', $dep);
        }

        $this->db->group_start();
        $this->db->like('nama', $search);
        $this->db->or_like('kode', $search);
        $this->db->group_end();
        $this->db->order_by('id_departemen', 'ASC');

        $result = $this->db->get('master_departemen')->result();
        $json = [];
        //$json[] = ['id'=> 0, 'text'=>'Semua Cabang'];
        foreach ($result as $rows) {
          $json[] = ['id'=>$rows->id_departemen, 'text'=>$rows->kode." - ".$rows->nama];
        }
        echo json_encode($json);
    }

}