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

class Dashboard_absensi_m extends CI_Model {
    var $table = 'absensi_masuk';

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

        $this->load->model('Db_model', 'db_model');

		$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($cabang="all", $departemen="all", $jadwal="all", $status=0){
        // $this->_get_datatables_query($cabang,$departemen,$jadwal,$status);
        if($status == "A"){
            $this->_get_datatables_belum_absen_query($cabang,$departemen,$jadwal,$status);
        }else{
            $this->_get_datatables_query($cabang,$departemen,$jadwal,$status);
        }
        if($_POST['length'] != -1){
            $this->db->limit($_POST['length'], $_POST['start']);
        }
        $query = $this->db->get();
        return $query->result();
    }

    function count_filtered($cabang="all", $departemen="all", $jadwal="all", $status=0){
        if($status == "A"){
            $this->_get_datatables_belum_absen_query($cabang,$departemen,$jadwal,$status);
        }else{
            $this->_get_datatables_query($cabang,$departemen,$jadwal,$status);
        }
        $query = $this->db->get();
        return $query->num_rows();
    }

    public function count_all($cabang="all", $departemen="all", $jadwal="all", $status=0){
        if($status == "A"){
            $this->_get_datatables_belum_absen_query($cabang,$departemen,$jadwal,$status);
        }else{
            $this->_get_datatables_query($cabang,$departemen,$jadwal,$status);
        }

        return $this->db->count_all_results();
    }

    private function _get_datatables_query($id_cabang="all", $id_departemen="all", $jadwal="all", $status="all"){
        $tanggal = date("Y-m-d");

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

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

        if($this->level_user == "7"){
            $this->db->group_start();
            $this->db->where('dk.supervisi', $this->id_karyawan);
            $this->db->or_where('dk.id_karyawan', $this->id_karyawan);
            $this->db->group_end();
        }

        $this->db->select("am.id_absensi_masuk,
                            am.foto,
                            dk.nik,
                            dk.nama_lengkap AS nama_karyawan,
                            mc.kode AS kode_cabang,
                            mc.nama AS nama_cabang,
                            md.kode AS kode_departemen,
                            md.nama AS nama_departemen,
                            am.lokasi_absen,
                            am.keterangan AS ket_absen_masuk,
                            am.kode_absen,
                            am.jam_absen as absen_masuk,
                            am.terlambat,
                            am.gmt,
                            ap.id_absensi_pulang,
                            ap.jam_absen as absen_pulang,
                            ap.gmt as gmt_pulang");
        $this->db->join("data_karyawan AS dk","dk.id_karyawan = am.id_karyawan and dk.id_company = am.id_company");
        $this->db->join("master_jabatan AS mj","mj.id_jabatan = dk.id_jabatan and mj.id_company = am.id_company");
        $this->db->join("master_cabang AS mc","mc.id_cabang = dk.id_cabang and mc.id_company = am.id_company");
        $this->db->join("master_departemen AS md","md.id_departemen = dk.id_departemen and md.id_company = am.id_company","left");
        $this->db->join("absensi_pulang AS ap","ap.id_masuk = am.id_absensi_masuk and ap.id_karyawan = am.id_karyawan and am.tgl_absen = ap.tgl_absen and am.id_company = ap.id_company","left");
        $this->db->where("am.id_company",$this->id_company);
        $this->db->where("am.tgl_absen",$tanggal);
        $this->db->from("absensi_masuk as am");

        if($jadwal != "all"){
            if($jadwal == "0"){
                $this->db->where("dk.id_karyawan NOT IN (SELECT id_karyawan FROM data_shift_karyawan WHERE tanggal = '$tanggal')");
                if($status == "T"){
                    $this->db->where('am.terlambat >',0);
                }else if($status == "I"){
                    $this->db->where('am.jenis_absen','izin');
                }else if($status == "S"){
                    $this->db->where('am.jenis_absen','sakit');
                }else if($status == "C"){
                    $this->db->where('am.jenis_absen','cuti');
                }
            }else{
                $this->db->join("data_shift_karyawan AS dsk","dsk.id_karyawan = dk.id_karyawan");
                $this->db->where("dsk.tanggal",$tanggal);
                $this->db->where("dsk.id_master_shift",$jadwal);

                if($status == "T"){
                    $this->db->where('am.terlambat >',0);
                }else if($status == "I"){
                    $this->db->where('am.jenis_absen','izin');
                }else if($status == "S"){
                    $this->db->where('am.jenis_absen','sakit');
                }else if($status == "C"){
                    $this->db->where('am.jenis_absen','cuti');
                }
            }
        }else{
            if($status == "T"){
                $this->db->where('am.terlambat >',0);
            }else if($status == "I"){
                $this->db->where('am.jenis_absen','izin');
            }else if($status == "S"){
                $this->db->where('am.jenis_absen','sakit');
            }else if($status == "C"){
                $this->db->where('am.jenis_absen','cuti');
            }else{
                $this->db->where_in('am.jenis_absen', ['reguler','shift']);
            }
        }




        $column_search = array('dk.nik','dk.nama_lengkap','md.kode','md.nama');
        $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('','','dk.nama_pegawai','md.nama_departemen','','am.jam_masuk','ap.jam_pulang');
        if(isset($_POST['order'])){
            $this->db->order_by($column_order[$_POST['order']['0']['column']], $_POST['order']['0']['dir']);
        }
        else{
            if($status == "A") {
                $order_by = array("dk.id_karyawan"=>"DESC");
            } else {
                $order_by = array("am.jam_absen"=>"DESC");
            }
            $this->db->order_by(key($order_by), $order_by[key($order_by)]);
        }
    }

    private function _get_datatables_belum_absen_query($id_cabang="all", $id_departemen="all", $jadwal="all", $status="all"){
        $tanggal = date("Y-m-d");

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

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

        if($this->level_user == "7"){
            $this->db->group_start();
            $this->db->where('dk.supervisi', $this->id_karyawan);
            $this->db->or_where('dk.id_karyawan', $this->id_karyawan);
            $this->db->group_end();
        }

        $this->db->select(" '' AS id_absensi_masuk,
                            dk.foto,
                            dk.nik,
                            dk.nama_lengkap AS nama_karyawan,
                            mc.kode AS kode_cabang,
                            mc.nama AS nama_cabang,
                            md.kode AS kode_departemen,
                            md.nama AS nama_departemen,
                            '' AS lokasi_absen,
                            '' AS ket_absen_masuk,
                            '' AS kode_absen,
                            '' AS absen_masuk,
                            '' AS terlambat,
                            '' AS id_absensi_pulang,
                            '' AS absen_pulang,
                            '' AS gmt_pulang");
        $this->db->join("master_jabatan AS mj","mj.id_jabatan = dk.id_jabatan");
        $this->db->join("master_cabang AS mc","mc.id_cabang = dk.id_cabang");
        $this->db->join("master_departemen AS md","md.id_departemen = dk.id_departemen","left");
        $this->db->where("dk.id_company",$this->id_company);
        $this->db->where_in("dk.status",['1','2','3','4']);
        $this->db->where_not_in("mj.level_user",['1','6']);
        $this->db->where("dk.id_karyawan NOT IN (SELECT id_karyawan FROM absensi_masuk WHERE tgl_absen = '$tanggal')");
        $this->db->where("dk.id_karyawan NOT IN (SELECT id_karyawan FROM data_shift_karyawan
                                                                    JOIN master_shift ON master_shift.id_master_shift = data_shift_karyawan.id_master_shift
                                                                    WHERE tanggal = '$tanggal' AND libur = '1')");
        $this->db->from("data_karyawan as dk");

        if($jadwal != "all"){
            if($jadwal == "0"){
                $this->db->where("dk.id_karyawan NOT IN (SELECT id_karyawan FROM data_shift_karyawan WHERE tanggal = '$tanggal')");
            }else{
                $this->db->join("data_shift_karyawan AS dsk","dsk.id_karyawan = dk.id_karyawan");
                $this->db->where("dsk.tanggal",$tanggal);
                $this->db->where("dsk.id_master_shift",$jadwal);
            }
        }


        $column_search = array('dk.nik','dk.nama_lengkap','md.kode','md.nama');
        $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('','','dk.nama_pegawai','md.nama_departemen','','','');
        if(isset($_POST['order'])){
            $this->db->order_by($column_order[$_POST['order']['0']['column']], $_POST['order']['0']['dir']);
        }
        else{
            if($status == "A") {
                $order_by = array("dk.id_karyawan"=>"DESC");
            } else {
                $order_by = array("am.jam_absen"=>"DESC");
            }
            $this->db->order_by(key($order_by), $order_by[key($order_by)]);
        }
    }

    function list_absensi($id_company, $usergroup, $key=null){
        if($usergroup == 2) {
            $id_departemen = $this->session->userdata('id_departemen');
            $this->db->where('id_departemen', $id_departemen);
        }
        return $this->db->select('id_departemen, kode, nama')
                        ->where('id_company', $id_company)
                        ->where('(kode LIKE "%'.$key.'%" OR nama LIKE "%'.$key.'%")')
                        // ->like('kode', $key)
                        // ->or_like('nama_sub_bagian', $key)
                        ->get('master_departemen')->result();
    }

    function data_departemen($id_departemen) {
        $id_company = $_SESSION['id_company'];
        return $this->db->select('id_departemen, kode, nama')
                        ->where('id_departemen', $id_departemen)
                        ->where('id_company', $id_company)
                        ->get('master_departemen')->row();
    }

    function export($tanggal) {
        $id_company = $_SESSION['id_company'];
        return $this->db->select("data_karyawan.nama_lengkap as nama_pegawai, concat(kode,' - ',master_departemen.nama) as nama_departemen")
                        ->from("data_karyawan")
                        ->join('master_departemen', 'master_departemen.id_departemen = data_karyawan.id_departemen','left')
                        ->join('master_jabatan', 'master_jabatan.id_jabatan = data_karyawan.id_jabatan')
                        ->where_not_in('master_jabatan.level_user', ['1','6'])
                        ->where_in('data_karyawan.status',['1','2','3','4'])
                        ->where('data_karyawan.id_company', $id_company)
                        ->where_not_in("data_karyawan.id_karyawan", "(SELECT id_karyawan FROM absensi_masuk WHERE tgl_absen = '".$tanggal."' GROUP BY id_karyawan)", false)
                        ->get()->result();
    }

    function lokasi($jenis, $id) {
        $id_company = $_SESSION['id_company'];
        $this->db->select('absen.id_karyawan,
                            data_karyawan.nama_lengkap,
                            jam_absen,
                            timezone,
                            gmt,
                            DATE_FORMAT(tgl_absen, "%d/%m/%Y") as tanggal,
                            DATE_FORMAT(tgl_absen, "%Y") as tahun,
                            DATE_FORMAT(tgl_absen, "%m%d") as bulan,
                            keterangan,
                            lokasi_absen,
                            latitude,
                            longitude,
                            absen.foto');
        $this->db->join('data_karyawan', 'absen.id_karyawan = data_karyawan.id_karyawan');
        $this->db->where('data_karyawan.id_company', $id_company);
        if($jenis == "masuk") {
            $this->db->select('kode_absen');
            $this->db->where("id_absensi_masuk", $id);
            return $this->db->get("absensi_masuk as absen")->row();
        } else {
            $this->db->where("id_absensi_pulang", $id);
            return $this->db->get("absensi_pulang as absen")->row();
        }
    }

    function hadir($tanggal, $id_cabang=0, $id_departemen=0, $id_shift=0, $jenis=0) {

        if($this->level_user == '7') {
            $this->db->where('data_karyawan.supervisi',$this->id_karyawan);
        }

        if($jenis != "0" && $id_shift == '0') {

            if($jenis == "I") {
                $where = array("I");
            }elseif ($jenis == "S") {
                $where = array("S");
            }elseif ($jenis == "C") {
                $where = array("C");
            }

            $this->db->where_in('absensi_masuk.kode_absen', $where);
        }

        if($id_cabang != '0') {
            // $this->db->where("data_karyawan.id_cabang", $id_cabang);
            if($id_cabang != "all"){
                $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 ($id_shift == "0" || $id_shift == "all") {
            $this->db->select("COUNT(id_absensi_masuk) as absen");
            $this->db->from("absensi_masuk");
            $this->db->join('data_karyawan','absensi_masuk.id_karyawan  = data_karyawan.id_karyawan');
            $this->db->join('master_jabatan','data_karyawan.id_jabatan  = master_jabatan.id_jabatan');
            $this->db->where("absensi_masuk.tgl_absen",$tanggal);
            $this->db->where("data_karyawan.id_company",$this->id_company);

            $status = array('1', '2', '3');
            $this->db->where_in("data_karyawan.status",$status);
            $this->db->where_not_in("master_jabatan.level_user",['1','6']);
            if($id_shift == "all"){
                $this->db->where_in("absensi_masuk.jenis_absen",["reguler","shift"]);
            }else{
                $this->db->where("absensi_masuk.jenis_absen","reguler");
            }

        }else{
            $this->db->select("COUNT(id_absensi_masuk) as absen");
            $this->db->from("absensi_masuk");
            $this->db->join('data_karyawan','absensi_masuk.id_karyawan  = data_karyawan.id_karyawan');
            $this->db->join('master_jabatan','data_karyawan.id_jabatan  = master_jabatan.id_jabatan');
            $this->db->join('data_shift_karyawan','absensi_masuk.id_karyawan  = data_shift_karyawan.id_karyawan');

            $this->db->where("absensi_masuk.tgl_absen",$tanggal);
            $this->db->where("data_shift_karyawan.id_master_shift",$id_shift);
            $this->db->where("data_shift_karyawan.tanggal",$tanggal);

            $this->db->where("data_karyawan.id_company",$this->id_company);

            $status = array('1', '2', '3');
            $this->db->where_in("data_karyawan.status",$status);
            $this->db->where_not_in("master_jabatan.level_user",['1','6']);
            // if($id_cabang != "all"){
            //     $this->db->where("data_karyawan.id_cabang",$id_cabang);
            // }

        }
        $query = $this->db->get();
        // echo $this->db->last_query();
        return $query;
    }

    function belum_absen($tanggal, $id_cabang=0, $id_departemen=0, $id_shift=0, $jenis=0) {

        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);
            if($id_cabang != "all"){
                $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 ($id_shift == "0" || $id_shift == "all") {
            $this->db->select("COUNT(data_karyawan.id_karyawan) as karyawan");
            $this->db->from("data_karyawan");
            $this->db->join('master_jabatan','data_karyawan.id_jabatan  = master_jabatan.id_jabatan');



            if($id_shift == "all"){
                $query2 = "SELECT id_karyawan FROM absensi_masuk WHERE tgl_absen = '$tanggal' AND id_company = '$this->id_company' AND jenis_absen IN ('reguler','shift')";
                $this->db->where_not_in("data_karyawan.id_karyawan ",$query2,false);
            }else{
                $query1 = "SELECT id_karyawan FROM data_shift_karyawan WHERE tanggal = '$tanggal' AND id_company = '$this->id_company' ";
                $this->db->where_not_in("data_karyawan.id_karyawan ",$query1,false);

                $query2 = "SELECT id_karyawan FROM absensi_masuk WHERE tgl_absen = '$tanggal' AND id_company = '$this->id_company' AND jenis_absen = 'reguler'";
                $this->db->where_not_in("data_karyawan.id_karyawan ",$query2,false);
            }


            $this->db->where("data_karyawan.id_company",$this->id_company);

            $status = array('1', '2', '3', '8');
            $this->db->where_in("data_karyawan.status",$status);
            $this->db->where_not_in("master_jabatan.level_user",['1','6']);

        }else{
            $this->db->select("COUNT(data_karyawan.id_karyawan) as karyawan");
            $this->db->from("data_karyawan");
            $this->db->join('master_jabatan','data_karyawan.id_jabatan  = master_jabatan.id_jabatan');

            $query1 = "SELECT id_karyawan FROM data_shift_karyawan WHERE tanggal = '$tanggal' AND id_master_shift = '$id_shift' AND id_company = '$this->id_company'";
            $this->db->where_in("data_karyawan.id_karyawan ",$query1,false);

            $query2 = "SELECT id_karyawan FROM absensi_masuk WHERE tgl_absen = '$tanggal' AND id_company = '$this->id_company' AND jenis_absen = 'shift'";
            $this->db->where_not_in("data_karyawan.id_karyawan ",$query2,false);

            $this->db->where("data_karyawan.id_company",$this->id_company);

            $status = array('1', '2', '3', '8');
            $this->db->where_in("data_karyawan.status",$status);
            $this->db->where_not_in("master_jabatan.level_user",['1','6']);

        }
        $query = $this->db->get();
        // echo $this->db->last_query();
        return $query;
    }

    function izin($tanggal, $id_cabang=0, $id_departemen=0, $id_shift=0, $jenis=0) {

        if($this->level_user == '7') {
            $this->db->where('data_karyawan.supervisi',$id_karyawan);
        }

        if($jenis != "0" && $id_shift == '0') {

            if($jenis == "I") {
                $where = array("I");
            }elseif ($jenis == "S") {
                $where = array("S");
            }elseif ($jenis == "C") {
                $where = array("C");
            }elseif ($jenis == "H") {
                $where = array("H");
            }

            $this->db->where_in('absensi_masuk.kode_absen', $where);
        }

        if($id_cabang != '0') {
            // $this->db->where("data_karyawan.id_cabang", $id_cabang);
            if($id_cabang != "all"){
                $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 ($id_shift == "0") {
            $this->db->select("COUNT(id_absensi_masuk) as izin");
            $this->db->from("absensi_masuk");
            $this->db->join('data_karyawan','absensi_masuk.id_karyawan  = data_karyawan.id_karyawan', 'left');
            $this->db->join('master_jabatan','data_karyawan.id_jabatan  = master_jabatan.id_jabatan');
            $status = array('1', '2', '3', '8');
            $this->db->where_in("data_karyawan.status",$status);
            $this->db->where_not_in("master_jabatan.level_user",['1','6']);

            $this->db->where("absensi_masuk.tgl_absen",$tanggal);

            $this->db->where("absensi_masuk.jenis_absen","izin");

            $query = "SELECT id_karyawan FROM data_shift_karyawan WHERE tanggal = '$tanggal' AND id_company = '$this->id_company'";
            $this->db->where_not_in("absensi_masuk.id_karyawan",$query);

            $this->db->where("data_karyawan.id_company",$this->id_company);

        }else{
            $this->db->select("COUNT(id_absensi_masuk) as izin");
            $this->db->from("absensi_masuk");
            $this->db->join('data_karyawan','absensi_masuk.id_karyawan  = data_karyawan.id_karyawan', 'left');
            $this->db->join('master_jabatan','data_karyawan.id_jabatan  = master_jabatan.id_jabatan');
            $status = array('1', '2', '3', '8');
            $this->db->where_in("data_karyawan.status",$status);
            $this->db->where_not_in("master_jabatan.level_user",['1','6']);

            $this->db->where("absensi_masuk.tgl_absen",$tanggal);

            $this->db->where("absensi_masuk.jenis_absen","izin");

            if($id_shift != "all"){
                $query = "SELECT id_karyawan FROM data_shift_karyawan WHERE tanggal = '$tanggal' AND id_master_shift = '$id_shift' AND id_company = '$this->id_company'";
                $this->db->where_in("absensi_masuk.id_karyawan",$query);
            }

            $this->db->where("data_karyawan.id_company",$this->id_company);

        }

        return $this->db->get();
    }

    function cuti($tanggal, $id_cabang=0, $id_departemen=0, $id_shift=0, $jenis=0) {

        if ($this->level_user == '7') {
            $this->db->where('data_karyawan.supervisi',$this->id_karyawan);
        }

        if($jenis != "0" && $id_shift == '0') {

            if($jenis == "I") {
                $where = array("I");
            }elseif ($jenis == "S") {
                $where = array("S");
            }elseif ($jenis == "C") {
                $where = array("C");
            }elseif ($jenis == "H") {
                $where = array("H");
            }

            $this->db->where_in('absensi_masuk.kode_absen', $where);
        }


        if($id_cabang != '0') {
            if($id_cabang != "all"){
                $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 ($id_shift == "0") {
            $this->db->select("COUNT(id_absensi_masuk) as cuti");
            $this->db->from("absensi_masuk");
            $this->db->join('data_karyawan','absensi_masuk.id_karyawan  = data_karyawan.id_karyawan', 'left');
            $this->db->join('master_jabatan','data_karyawan.id_jabatan  = master_jabatan.id_jabatan');
            $status = array('1', '2', '3', '8');
            $this->db->where_in("data_karyawan.status",$status);
            $this->db->where_not_in("master_jabatan.level_user",['1','6']);

            $this->db->where("absensi_masuk.tgl_absen",$tanggal);

            $this->db->where("absensi_masuk.jenis_absen","cuti");

            $query = "SELECT id_karyawan FROM data_shift_karyawan WHERE tanggal = '$tanggal' AND id_company = '$this->id_company'";
            $this->db->where_not_in("absensi_masuk.id_karyawan",$query);

            $this->db->where("data_karyawan.id_company",$this->id_company);
            $this->db->where("data_karyawan.id_cabang",$id_cabang);

        }else{
            $this->db->select("COUNT(id_absensi_masuk) as cuti");
            $this->db->from("absensi_masuk");
            $this->db->join('data_karyawan','absensi_masuk.id_karyawan  = data_karyawan.id_karyawan', 'left');
            $this->db->join('master_jabatan','data_karyawan.id_jabatan  = master_jabatan.id_jabatan');
            $status = array('1', '2', '3', '8');
            $this->db->where_in("data_karyawan.status",$status);
            $this->db->where_not_in("master_jabatan.level_user",['1','6']);

            $this->db->where("absensi_masuk.tgl_absen",$tanggal);

            $this->db->where("absensi_masuk.jenis_absen","cuti");

            if($id_shift != "all"){
                $query = "SELECT id_karyawan FROM data_shift_karyawan WHERE tanggal = '$tanggal' AND id_master_shift = '$id_shift' AND id_company = '$this->id_company'";
                $this->db->where_in("absensi_masuk.id_karyawan",$query);
            }

            $this->db->where("data_karyawan.id_company",$this->id_company);
            $this->db->where("data_karyawan.id_cabang",$id_cabang);

        }
        return $this->db->get();

    }

    function sakit($tanggal, $id_cabang=0, $id_departemen=0, $id_shift=0, $jenis=0) {

        if ($this->level_user == '7') {
            $this->db->where('data_karyawan.supervisi',$this->id_karyawan);
        }

        if($jenis != "0" && $id_shift == '0') {

            if($jenis == "I") {
                $where = array("I");
            }elseif ($jenis == "S") {
                $where = array("S");
            }elseif ($jenis == "C") {
                $where = array("C");
            }elseif ($jenis == "H") {
                $where = array("H");
            }

            $this->db->where_in('absensi_masuk.kode_absen', $where);
        }


        if($id_cabang != '0') {
            if($id_cabang != "all"){
                $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 ($id_shift == "0") {
            $this->db->select("COUNT(id_absensi_masuk) as sakit");
            $this->db->from("absensi_masuk");
            $this->db->join('data_karyawan','absensi_masuk.id_karyawan  = data_karyawan.id_karyawan', 'left');
            $this->db->join('master_jabatan','data_karyawan.id_jabatan  = master_jabatan.id_jabatan');
            $status = array('1', '2', '3', '8');
            $this->db->where_in("data_karyawan.status",$status);
            $this->db->where_not_in("master_jabatan.level_user",['1','6']);

            $this->db->where("absensi_masuk.tgl_absen",$tanggal);

            $this->db->where("absensi_masuk.jenis_absen","sakit");

            $query = "SELECT id_karyawan FROM data_shift_karyawan WHERE tanggal = '$tanggal' AND id_company = '$this->id_company'";
            $this->db->where_not_in("absensi_masuk.id_karyawan",$query);

            $this->db->where("data_karyawan.id_company",$this->id_company);

        }else{
            $this->db->select("COUNT(id_absensi_masuk) as sakit");
            $this->db->from("absensi_masuk");
            $this->db->join('data_karyawan','absensi_masuk.id_karyawan  = data_karyawan.id_karyawan', 'left');
            $this->db->join('master_jabatan','data_karyawan.id_jabatan  = master_jabatan.id_jabatan');
            $status = array('1', '2', '3', '8');
            $this->db->where_in("data_karyawan.status",$status);
            $this->db->where_not_in("master_jabatan.level_user",['1','6']);

            $this->db->where("absensi_masuk.tgl_absen",$tanggal);

            $this->db->where("absensi_masuk.jenis_absen","sakit");

            if($id_shift != "all"){
                $query = "SELECT id_karyawan FROM data_shift_karyawan WHERE tanggal = '$tanggal' AND id_master_shift = '$id_shift' AND id_company = '$this->id_company'";
                $this->db->where_in("absensi_masuk.id_karyawan",$query);
            }

            $this->db->where("data_karyawan.id_company",$this->id_company);

        }

        return $this->db->get();
    }


    function kode_absen($id_karyawan, $tgl_absen) {
        $this->db->select('kode_absen');
        $this->db->where('id_karyawan', $id_karyawan);
        $this->db->where('tgl_absen', $tgl_absen);
        $this->db->where('id_company', $this->id_company);
        return $this->db->get('absensi_masuk');
    }

    public function get_cabang($id = null, $search = null){

        $this->db->select('nama, id_cabang, kode');
        $this->db->where('id_company', $this->id_company);
        $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');


        if(in_array($this->level_user,[5,2,7,3])){
            $this->db->where('id_cabang', $this->id_cabang);
        }

        $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);
    }

}
