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

class Dashboard_m extends CI_Model {
    public function __construct(){
        parent::__construct();
        $this->load->model('Db_model', 'db_model');

		$this->id_company = $this->session->userdata('id_company');
        $this->id_cabang = $this->session->userdata('id_cabang');
        $this->id_departemen = $this->session->userdata('id_departemen');
        $this->id_karyawan = $this->session->userdata('id_karyawan');
        $this->level_user = $this->session->userdata('level_user');
	}

    function jumlah_pegawai($usergroup, $id_company, $cabang) {
        $this->load->model('Db_model', 'db_model');
        if($usergroup == 2) {
            $level = array("2","3","7");
            $id_cabang     = $this->session->userdata('id_cabang');
            $id_departemen = $this->session->userdata('id_departemen');
            $this->db->where('id_cabang', $id_cabang);
            $this->db->where('id_departemen', $id_departemen);
            $this->db->where_in('master_jabatan.level_user',$level);

        }elseif($usergroup == 7) {
            $karyawan = $this->db_model->get("data_karyawan", "id_karyawan", ['supervisi' => $this->session->userdata('id_karyawan')])->result();
            $target_name_arr = array();
            foreach ($karyawan as $dat) {
                $target_name_arr[] = $dat->id_karyawan;
            }
            $target_id = implode("','", $target_name_arr);
            $id_cabang     = $this->session->userdata('id_cabang');
            $id_departemen = $this->session->userdata('id_departemen');
            $this->db->where('id_cabang', $id_cabang);
            $this->db->where('id_departemen', $id_departemen);
            $this->db->where_in('id_karyawan',$target_name_arr);

        }elseif ($usergroup == 5) {
            $level = array("2","3","5","7");
            $id_cabang     = $this->session->userdata('id_cabang');
            $this->db->where('id_cabang', $id_cabang);
            $this->db->where_in('master_jabatan.level_user',$level);
        }else{
            $this->db->where('id_cabang', $cabang);
        }

        $status = array('1', '2', '3','4');

        return $this->db->select('data_karyawan.id_karyawan')
                        ->from('data_karyawan')
                        ->join('master_jabatan', 'master_jabatan.id_jabatan = data_karyawan.id_jabatan', 'left')
                        ->where_in('status',$status)
                        ->where('data_karyawan.id_company', $id_company)
                        ->get()->num_rows();
    }

    function absen($tanggal, $usergroup, $id_company) {
        $this->load->model('Db_model', 'db_model');
        $id_cabang = $this->session->userdata('id_cabang');
        $id_departemen = $this->session->userdata('id_departemen');
        $tanggal = date('Y-m-d');
        $timestamp = strtotime($tanggal);
        $day = date('w', $timestamp);
        $jam_masuk = $this->db_model->get("jam_kerja", "masuk",['id_cabang' => $id_cabang, 'hari' => $day])->row()->masuk;


        if($usergroup == '2') {
            $this->db->from("(SELECT kode_absen FROM absensi_masuk JOIN data_karyawan ON data_karyawan.id_karyawan = absensi_masuk.id_karyawan
                WHERE tgl_absen = '".$tanggal."' and id_cabang = ".$id_cabang."  and jam_kerja = '".$jam_masuk."' and id_departemen = ".$id_departemen." AND absensi_masuk.id_company = ".$id_company."
                GROUP BY absensi_masuk.id_karyawan, id_absensi_masuk) t");
        }elseif ($usergroup == '5') {
            $this->db->from("(SELECT kode_absen FROM absensi_masuk JOIN data_karyawan ON data_karyawan.id_karyawan = absensi_masuk.id_karyawan
                WHERE tgl_absen = '".$tanggal."' and id_cabang = ".$id_cabang."  and jam_kerja = '".$jam_masuk."' AND absensi_masuk.id_company = ".$id_company."
                GROUP BY absensi_masuk.id_karyawan, id_absensi_masuk) t");
        }elseif ($usergroup == '7') {
            $this->load->model('Db_model', 'db_model');
            $karyawan = $this->db_model->get("data_karyawan", "id_karyawan", ['supervisi' => $this->session->userdata('id_karyawan'), 'id_company' => $id_company])->result();
            $target_name_arr = array();
            foreach ($karyawan as $dat) {
                $target_name_arr[] = $dat->id_karyawan;
            }
            $target_id = implode("','", $target_name_arr);

            $this->db->from("(SELECT kode_absen FROM absensi_masuk JOIN data_karyawan ON data_karyawan.id_karyawan = absensi_masuk.id_karyawan
                WHERE tgl_absen = '".$tanggal."' and id_cabang = ".$id_cabang."  and jam_kerja = '".$jam_masuk."' AND id_departemen = ".$id_departemen." AND absensi_masuk.id_company = ".$id_company." AND absensi_masuk.id_karyawan IN ('".$target_id."') GROUP BY absensi_masuk.id_karyawan, absensi_masuk.id_absensi_masuk) t");
        }else {
            $this->db->from("(SELECT kode_absen FROM absensi_masuk JOIN data_karyawan ON data_karyawan.id_karyawan = absensi_masuk.id_karyawan
                WHERE tgl_absen = '".$tanggal."' and jam_kerja = '".$jam_masuk."' AND absensi_masuk.id_company = ".$id_company." GROUP BY absensi_masuk.id_karyawan, id_absensi_masuk) t");
        }
        return $this->db->select("COUNT(
            IF(t.kode_absen = 'H', 1, NULL)) as absen,
            COUNT(IF(t.kode_absen = 'C', 1, NULL)) as cuti,
            COUNT(IF(t.kode_absen = 'I', 1, NULL)) as izin,
            COUNT(IF(t.kode_absen = 'S', 1, NULL)) as sakit")->get();
    }

    function filter_absen($tanggal, $id_cabang, $jam_masuk) {
        $id_company = $this->session->userdata('id_company');
        $this->db->from("(SELECT kode_absen FROM absensi_masuk JOIN data_karyawan ON data_karyawan.id_karyawan = absensi_masuk.id_karyawan
                WHERE tgl_absen = '".$tanggal."' and id_cabang = ".$id_cabang." and jam_kerja = '".$jam_masuk."' AND absensi_masuk.id_company = ".$id_company." GROUP BY absensi_masuk.id_karyawan, id_absensi_masuk) t");
        return $this->db->select("COUNT(
            IF(t.kode_absen = 'H', 1, NULL)) as absen,
            COUNT(IF(t.kode_absen = 'C', 1, NULL)) as cuti,
            COUNT(IF(t.kode_absen = 'I', 1, NULL)) as izin,
            COUNT(IF(t.kode_absen = 'S', 1, NULL)) as sakit")->get();
    }

    function dashboard_izin($usergroup, $id_cabang, $id_departemen) {
        if($usergroup == 2) {
            $this->db->where('data_izin.id_cabang', $id_cabang);
            $this->db->where('data_izin.id_departemen', $id_departemen);
            //$this->db->where('data_izin.approval_kedep', "1");
        }elseif ($usergroup == 7) {
            $this->load->model('Db_model', 'db_model');
            $karyawan = $this->db_model->get("data_karyawan", "id_karyawan", ['supervisi' => $this->session->userdata('id_karyawan')])->result();
            $target_name_arr = array();
            foreach ($karyawan as $dat) {
                $target_name_arr[] = $dat->id_karyawan;
            }
            $target_id = implode("','", $target_name_arr);
            $this->db->where_in('data_izin.id_karyawan', $target_name_arr);

        }elseif ($usergroup == 5) {
            $this->db->where('data_izin.id_cabang', $id_cabang);
        }

        switch ($usergroup) {
            case '1':
                $str = 'data_izin.approval_hrd';
                break;
            case '2':
                $str = 'data_izin.approval_kedep';
                break;
            case '4':
                $str = 'data_izin.approval_direksi';
                break;
            case '5':
                $str = 'data_izin.approval_kacab';
                break;
            case '7':
                $str = 'data_izin.approval_spv';
                break;

            default:
                $str = 'data_izin.approval_hdr';
                break;
        }
        $appv_sts = [];
        $status = '1';
        if($status == '1')
        {
            $appv_sts = ['1'];
        }elseif($status == '2')
        {
            $appv_sts = ['3'];
        }elseif($status == '3')
        {
            $appv_sts = ['4'];
        }elseif($status == '0')
        {
            $appv_sts = ['1', '2', '3', '4'];
        }

        //list pengajuan izin dari pengajuan 3 terakhir
        $this->db->select("id_izin, jenis_izin, CONCAT(
            CASE WHEN DATE_FORMAT(tgl_pengajuan, '%w') = 0 THEN 'MINGGU, '
                WHEN DATE_FORMAT(tgl_pengajuan, '%w') = 1 THEN 'SENIN, '
                WHEN DATE_FORMAT(tgl_pengajuan, '%w') = 2 THEN 'SELASA, '
                WHEN DATE_FORMAT(tgl_pengajuan, '%w') = 3 THEN 'RABU, '
                WHEN DATE_FORMAT(tgl_pengajuan, '%w') = 4 THEN 'KAMIS, '
                WHEN DATE_FORMAT(tgl_pengajuan, '%w') = 5 THEN 'JUMAT, '
                ELSE 'SABTU, ' END) as hari_pengajuan, tgl_pengajuan,
            ket_izin as keterangan_izin, nama_lengkap as yang_mengajukan,
            DATE_FORMAT(tgl_mulai_izin, '%d/%m/%Y') as tanggal_mulai_izin,
            DATE_FORMAT(tgl_selesai_izin, '%d/%m/%Y') as tanggal_selesai_izin,
            CASE WHEN data_izin.status = 4 THEN 'Disetujui Atasan'
            WHEN data_izin.status = 3 THEN 'Tidak Disetujui Atasan'
            WHEN data_izin.status = 2 THEN 'Dibatalkan' ELSE 'Belum Disetujui Atasan' END as status_approve,
            data_izin.status,
            data_izin.id_karyawan,
            data_izin.approval_spv,
            data_izin.approval_kedep,
            data_izin.approval_kacab,
            data_izin.approval_hrd,
            data_izin.approval_direksi,
            CASE WHEN jenis_izin = 'cuti' THEN 'CUTI'
                 WHEN jenis_izin = 'izin' THEN 'IZIN'
                ELSE 'SAKIT' END as nama_jenis_izin");
        $this->db->from('data_izin');
        $this->db->where('data_izin.status !=', '2');
        $this->db->where('data_izin.id_company', $this->session->userdata('id_company'));

        $this->db->group_start();
        $this->db->where_in($str, $appv_sts);
        if(!in_array($status, ['1', '2']))
        {
            if($status == '0')
            {
                $appv_sts = ['4'];
            }
            $this->db->or_where_in('data_izin.status', $appv_sts);
            $this->db->or_where('data_izin.id_karyawan', $this->id_karyawan);
        }
        $this->db->group_end();
        // $this->db->where($str, '1');
        // $this->db->where('data_izin.status', '1');
        $this->db->join('data_karyawan', 'data_izin.id_karyawan = data_karyawan.id_karyawan');
        $this->db->order_by('data_izin.tgl_pengajuan', 'desc');
        $this->db->limit(3);
        return $this->db->get();
    }

    function dashboard_lembur($usergroup, $id_cabang, $id_departemen) {
        if($usergroup == 2) {
            $this->db->where('data_karyawan.id_cabang', $id_cabang);
            $this->db->where('data_karyawan.id_departemen', $id_departemen);
            //$this->db->where('lembur_mulai.approval_kedep', "1");

        }elseif ($usergroup == 7) {
            $this->load->model('Db_model', 'db_model');
            $karyawan = $this->db_model->get("data_karyawan", "id_karyawan", ['supervisi' => $this->session->userdata('id_karyawan')])->result();
            $target_name_arr = array();
            foreach ($karyawan as $dat) {
                $target_name_arr[] = $dat->id_karyawan;
            }

            if(count($target_name_arr) > 1){
                $target_id = implode("','", $target_name_arr);
                $this->db->where_in('lembur_mulai.id_karyawan', $target_name_arr);
                // $this->db->where('lembur_mulai.approval_spv', "1");
            }


        }elseif ($usergroup == 5) {
            $this->db->where('data_karyawan.id_cabang', $id_cabang);
            //$this->db->where('lembur_mulai.approval_kacab', "1");

        }

        switch ($usergroup) {
            case '1':
                $str = 'lembur_mulai.approval_hrd';
                break;
            case '2':
                $str = 'lembur_mulai.approval_kedep';
                break;
            case '4':
                $str = 'lembur_mulai.approval_direksi';
                break;
            case '5':
                $str = 'lembur_mulai.approval_kacab';
                break;
            case '7':
                $str = 'lembur_mulai.approval_spv';
                break;

            default:
                $str = 'lembur_mulai.approval_hdr';
                break;
        }
        $appv_sts = [];
        $status = '1';
        if($status == '1')
        {
            $appv_sts = ['1'];
        }elseif($status == '2')
        {
            $appv_sts = ['3'];
        }elseif($status == '3')
        {
            $appv_sts = ['4'];
        }elseif($status == '0')
        {
            $appv_sts = ['1', '2', '3', '4'];
        }
        //list pengajuan izin dari pengajuan 3 terakhir
        $this->db->select("*");
        $this->db->from('lembur_mulai');
        $this->db->where('lembur_mulai.id_company', $this->session->userdata('id_company'));

        $this->db->group_start();
        $this->db->where_in($str, $appv_sts);
        if(!in_array($status, ['1', '2']))
        {
            if($status == '0')
            {
                $appv_sts = ['4'];
            }
            $this->db->or_where_in('lembur_mulai.status', $appv_sts);
        }
        $this->db->group_end();
        // $this->db->where('lembur_mulai.status', '1');
        $this->db->join('data_karyawan', 'lembur_mulai.id_karyawan = data_karyawan.id_karyawan');
        $this->db->join('lembur_selesai', 'lembur_selesai.id_lemmulai = lembur_mulai.id_lemmulai');
        $this->db->order_by('lembur_mulai.tgl_absen', 'desc');
        $this->db->limit(3);
        return $this->db->get();
    }

    function data_cabang($id_cabang) {
        return $this->db->select('id_cabang, kode, nama')
                        ->where('id_cabang', $id_cabang)
                        ->where('id_company', $this->session->userdata('id_company'))
                        ->get('master_cabang')->row();
    }

}