<?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');
    }


    public function paketLayanan()
    {
        $this->db->select("paket.nama AS nm_paket,
                            paket.max_karyawan");
        $this->db->join("master_company AS mc", "mc.id_tb_paket = paket.id_tb_paket");
        $this->db->where("mc.id_company", $this->id_company);
        $this->db->from("tb_paket AS paket");
        $query = $this->db->get()->row();
        return $query;
    }

    public function ttlKaryawanAktif()
    {
        $this->db->select('data_karyawan.id_karyawan');
        $this->db->from("data_karyawan");
        $this->db->join("master_jabatan", "master_jabatan.id_jabatan = data_karyawan.id_jabatan");
        $this->db->where("data_karyawan.id_company", $this->id_company);
        $this->db->where_in("data_karyawan.status", ['1', '2', '3', '4']);
        $this->db->where_not_in("master_jabatan.level_user", ["1", "6"]);
        $query =  $this->db->get()->num_rows();
        return $query;
    }

    public function masaLayanan()
    {
        $this->db->select('tgl_aktif, tgl_berakhir');
        $this->db->from("master_plan");
        $this->db->where("id_company", $this->id_company);
        $query = $this->db->get()->row();
        return $query;
    }

    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->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->id_cabang;
        $id_departemen = $this->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->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->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->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_hrd';
                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 '" . strtoupper($this->lang->line('minggu')) . ", '
                WHEN DATE_FORMAT(tgl_pengajuan, '%w') = 1 THEN '" . strtoupper($this->lang->line('senin')) . ", '
                WHEN DATE_FORMAT(tgl_pengajuan, '%w') = 2 THEN '" . strtoupper($this->lang->line('selasa')) . ", '
                WHEN DATE_FORMAT(tgl_pengajuan, '%w') = 3 THEN '" . strtoupper($this->lang->line('rabu')) . ", '
                WHEN DATE_FORMAT(tgl_pengajuan, '%w') = 4 THEN '" . strtoupper($this->lang->line('kamis')) . ", '
                WHEN DATE_FORMAT(tgl_pengajuan, '%w') = 5 THEN '" . strtoupper($this->lang->line('jumat')) . ", '
                ELSE '" . strtoupper($this->lang->line('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 '" . $this->lang->line('disetujui') . ' ' . $this->lang->line('oleh') . ' ' . $this->lang->line('atasan') . "'
            WHEN data_izin.status = 3 THEN '" . $this->lang->line('tidak_disetujui') . ' ' . $this->lang->line('oleh') . ' ' . $this->lang->line('atasan') . "'
            WHEN data_izin.status = 2 THEN '" . $this->lang->line('dibatalkan') . "' ELSE '" . $this->lang->line('belum_disetujui') . ' ' . $this->lang->line('oleh') . ' ' . $this->lang->line('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 '" . strtoupper($this->lang->line('cuti')) . "'
                 WHEN jenis_izin = 'izin' THEN '" . strtoupper($this->lang->line('izin')) . "'
                ELSE '" . strtoupper($this->lang->line('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->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->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);

        } elseif ($usergroup == 7) {
            $this->load->model('Db_model', 'db_model');
            $karyawan = $this->db_model->get("data_karyawan", "id_karyawan", ['supervisi' => $this->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);
            }
        } elseif ($usergroup == 5) {
            $this->db->where('data_karyawan.id_cabang', $id_cabang);
        }

        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_hrd';
                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->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->id_company)
            ->get('master_cabang')->row();
    }
}
