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

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

    function get_datatables($tgl_mulai="0", $tgl_selesai="0", $jenis = "SM", $status="0", $id_karyawan="0", $id_cabang="0", $id_departemen="0"){
        $this->_get_datatables_query($tgl_mulai, $tgl_selesai, $jenis, $status, $id_karyawan, $id_cabang, $id_departemen);
        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", $jenis = "SM", $status="0", $id_karyawan="0", $id_cabang="0", $id_departemen="0"){
        $this->_get_datatables_query($tgl_mulai, $tgl_selesai, $jenis, $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", $jenis = "SM", $status="0", $id_karyawan="0", $id_cabang="0", $id_departemen="0"){
        $this->_get_datatables_query($tgl_mulai, $tgl_selesai, $jenis, $status, $id_karyawan, $id_cabang, $id_departemen);

        return $this->db->count_all_results();
    }
    private function _get_datatables_query($tgl_mulai="0", $tgl_selesai="0", $jenis = "SM", $status="0", $id_karyawan="0", $id_cabang="0", $id_departemen="0"){
        $this->db->select("data_izin.id_izin,
                            data_karyawan.nama_lengkap,
                            data_karyawan.nik,
                            master_jabatan.level_user,
                            data_izin.id_cabang,
                            data_izin.nama_izin,
                            data_izin.tgl_pengajuan,
                            data_izin.tgl_mulai_izin,
                            data_izin.tgl_selesai_izin,
                            data_izin.jml_izin,
                            data_izin.ket_izin,
                            data_izin.approval_spv,
                            data_izin.approval_kedep,
                            data_izin.approval_kacab,
                            data_izin.approval_direksi,
                            data_izin.approval_hrd");
        $this->db->from($this->table);
        $this->db->join('data_karyawan','data_izin.id_karyawan  = data_karyawan.id_karyawan');
        $this->db->join('master_jabatan','master_jabatan.id_jabatan  = data_karyawan.id_jabatan');
        $this->db->where("data_izin.id_company", $this->id_company);
        $this->db->where("data_izin.status !=", '2');
        $this->db->where("DATE_FORMAT(data_izin.tgl_pengajuan,'%Y-%m-%d') >=", $tgl_mulai);
        $this->db->where("DATE_FORMAT(data_izin.tgl_pengajuan,'%Y-%m-%d') <=", $tgl_selesai);

        if($jenis != 'SM'){
            $this->db->where("data_izin.kode_izin", $jenis);
        }

        if($id_karyawan != "0") {
            $this->db->where("data_izin.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);
                }
            }
        }

        if($this->level_user == "3"){
            $this->db->where_in('data_izin.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('data_izin.status', $status['appv_sts']);
                $this->db->or_where('data_izin.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('data_izin.status', $status['appv_sts']);
                    $this->db->or_where('data_izin.id_karyawan', $this->id_karyawan);
                }
            }
            $this->db->group_end();
        }

        $column_search = array('data_karyawan.nama_lengkap','data_izin.nama_izin');
        $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_izin.id_karyawan','data_izin.jenis_izin','data_izin.tgl_pengajuan');

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

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

    public function getDetailIzin($id_izin = null){
        $this->db->select("data_izin.id_izin,
                            data_izin.tgl_pengajuan,
                            data_karyawan.nama_lengkap,
                            data_karyawan.nik,
                            data_izin.tgl_mulai_izin,
                            data_izin.tgl_selesai_izin,
                            data_izin.jml_izin,
                            data_izin.nama_izin,
                            data_izin.ket_izin,
                            data_izin.flag,
                            data_karyawan.jatah_cuti as sisa_cuti");
        $this->db->from("data_izin");
        $this->db->join("data_karyawan","data_karyawan.id_karyawan = data_izin.id_karyawan");
        $this->db->where("MD5(data_izin.id_izin)",$id_izin);

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

    public function save($data,$id = null){
		if($id != null) {
			$this->db->where(array('md5(id_izin)'=>$id, 'id_company'=>$this->id_company));
			return $this->db->update($this->table,$data);
		} else {
			$nextId = $this->fungsi->getNextId($this->table,'id_izin',$this->id_company,4);
			$id_izin = array('id_izin' => $nextId);
			return $this->db->insert($this->table, array_merge($data,$id_izin));
		}
    }

    public function delete($id=null){
        $predir_foto = $this->db->select("file")
                                    ->from('file_izin')
                                    ->where("md5(id_izin)",$id)
                                    ->get()
                                    ->row("file");

        $this->db->delete('file_izin', array('md5(id_izin)' => $id));
        $this->db->where(array('md5(id_izin)'=>$id));
        $delete =$this->db->delete($this->table);
        if($delete){
            $path = $this->config->item('base_upload_absensi').$predir_foto;
            unlink($path);
        }
        return  __response_delete($delete);
    }

    public function get_jenis($id = null, $search = null){
        $id_company = $this->session->userdata('id_company');

        $this->db->select('nama, id_jenis_izin, kode_izin');
        $this->db->where('id_company', $id_company);
        $this->db->group_start();
        $this->db->like('nama', $search);
        $this->db->or_like('kode_izin', $search);
        $this->db->group_end();
        $this->db->order_by('id_jenis_izin', 'ASC');

        $result = $this->db->get('master_jenis_izin')->result();
        $json = [];
        $json[] = ['id'=> 'SM', 'text'=>'Semua Jenis'];
        foreach ($result as $rows) {
            $json[] = ['id'=>$rows->kode_izin, 'text'=>$rows->nama." (".$rows->kode_izin.")"];
        }
        echo json_encode($json);
    }

    public function get_jenis_form($id = null, $search = null){
        $id_company = $this->session->userdata('id_company');

        $this->db->select('nama, id_jenis_izin, kode_izin');
        $this->db->where('id_company', $id_company);
        $this->db->group_start();
        $this->db->like('nama', $search);
        $this->db->or_like('kode_izin', $search);
        $this->db->group_end();
        $this->db->order_by('id_jenis_izin', 'ASC');

        $result = $this->db->get('master_jenis_izin')->result();
        $json = [];
        foreach ($result as $rows) {
          $json[] = ['id'=>$rows->kode_izin, 'text'=>$rows->nama." (".$rows->kode_izin.")"];
        }
        echo json_encode($json);
    }

    public function userApproval($id_karyawan = null){
        $this->db->select("nik, nama_lengkap");
        $this->db->where("id_karyawan",$id_karyawan);
        $this->db->from("data_karyawan");
        $query = $this->db->get()->row()->nama_lengkap;
        return $query;
    }


    function cek_punya_shift_dan_tidak_libur($id_karyawan, $tanggal, $id_company) {
        $where = array(
                        "data_shift_karyawan.id_karyawan" => $id_karyawan,
                        "data_shift_karyawan.tanggal"     => $tanggal,
                        "data_shift_karyawan.id_company"  => $id_company,
                        "master_shift.kode_shift NOT IN ('O', 'OFF')" => NULL
        );
        $this->db->select("id_shift_karyawan")
                ->from("data_shift_karyawan")
                ->join("master_shift", "data_shift_karyawan.id_master_shift = master_shift.id_master_shift")
                ->where($where);
        return $this->db->get()->num_rows();
    }

    function cek_jadwal_kerja_libur($id_cabang, $tanggal, $id_company) {
        $where = array(
            "id_cabang"   => $id_cabang,
            "id_company"  => $id_company,
            "hari"        => date("w", strtotime($tanggal))
        );
        $this->db->select("libur")
                ->from("jam_kerja")
                ->where($where);
        return $this->db->get()->row()->libur;
    }


	function get_holidays($date_start, $date_end) {
		$this->db->select('tanggal');
		$this->db->from('holidays');
		$this->db->where('tanggal >=', $date_start);
		$this->db->where('tanggal <=', $date_end);
		$this->db->where('id_company', $this->id_company);
		return $this->db->get();
	}

	function shift_off($id_karyawan, $date_start, $date_end) {
		$this->db->select('tanggal');
		$this->db->from('data_shift_karyawan ds');
		$this->db->join('master_shift ms','ms.id_master_shift = ds.id_master_shift');
		$this->db->where('ms.libur','1');
		$this->db->where('ds.tanggal >=', $date_start);
		$this->db->where('ds.tanggal <=', $date_end);
		$this->db->where('ds.id_karyawan', $id_karyawan);
		return $this->db->get();
	}

	function week_holidays($id_karyawan) {
		$this->db->select('jk.hari');
		$this->db->from('jam_kerja jk');
		$this->db->join('data_karyawan k','k.id_cabang = jk.id_cabang');
		$this->db->where('k.id_karyawan', $id_karyawan);
		$this->db->where('jk.libur',1);
		return $this->db->get();
	}

	function data_karyawan($id_karyawan) {
		$this->db->where('id_karyawan', $id_karyawan);
		return $this->db->get('data_karyawan')->row();
	}

	function detail_karyawan($id_karyawan) {
		$this->db->select('k.nik,k.nama_lengkap as nama_karyawan,k.id_cabang,c.kode as kode_cabang,
		c.nama as nama_cabang,k.id_departemen,d.kode as kode_departemen, d.nama as nama_departemen,
		k.id_jabatan,j.kode_jabatan,j.nama as nama_jabatan,k.supervisi,
		spv.nama_lengkap as nama_superivisi,k.id_company');
		$this->db->from('data_karyawan k');
		$this->db->join('master_cabang c','c.id_cabang = k.id_cabang','left');
		$this->db->join('master_departemen d','k.id_departemen = d.id_departemen','left');
		$this->db->join('master_jabatan j','j.id_jabatan = k.id_jabatan','left');
		$this->db->join('data_karyawan spv','k.supervisi = spv.id_karyawan','left');
		$this->db->where('k.id_karyawan', $id_karyawan);
		return $this->db->get()->row();
	}

	function detail_master_izin($id_jenis) {
		$this->db->select('max_izin,flag');
		$this->db->from('master_jenis_izin');
		$this->db->where('kode_izin', $id_jenis);
		$this->db->where('id_company', $this->id_company);
		return $this->db->get()->row();
	}

	function update_jatah_cuti($id_karyawan, $potongan) {
		$this->db->set('jatah_cuti','jatah_cuti-'.$potongan,FALSE);
		$this->db->where('id_karyawan', $id_karyawan);
		$this->db->update('data_karyawan');
	}

	function hapus_absensi($id_karyawan, $tanggal_arr) {
		$this->db->where('id_karyawan',$id_karyawan);
		$this->db->where_in('tgl_absen', $tanggal_arr);
		$this->db->delete('absensi_masuk');

		$this->db->where('id_karyawan',$id_karyawan);
		$this->db->where_in('tgl_absen', $tanggal_arr);
		$this->db->delete('absensi_pulang');

		$this->db->where('id_karyawan',$id_karyawan);
		$this->db->where_in('tgl_absen', $tanggal_arr);
		$this->db->delete('istirahat_mulai');

		$this->db->where('id_karyawan',$id_karyawan);
		$this->db->where_in('tgl_absen', $tanggal_arr);
		$this->db->delete('istirahat_selesai');

		$this->db->where('id_karyawan',$id_karyawan);
		$this->db->where_in('tgl_absen', $tanggal_arr);
		$this->db->delete('r_absensi');
	}
}
