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

class Joint_vs_termination extends MY_Controller
{

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

    public function index()
    {
    }

    public function JoinVsTermination($tahun = "", $id_cabang = "all", $id_departemen = "all")
    {
        $join           = $this->_joint($tahun, $id_cabang, $id_departemen);
        $termination    = $this->_termination($tahun, $id_cabang, $id_departemen);

        $label_arr          = [];
        $joint_arr          = [];
        $termination_arr    = [];

        for ($i = 1; $i <= 12; $i++) {
            $bulan = sprintf("%02d", $i);
            // $label_arr[]            = $this->fungsi->get_bulan($i);
            $label_arr[]            = $this->_bulan_m($bulan);
            $joint_arr[]            = (isset($join[$tahun . "-" . $bulan])) ? $join[$tahun . "-" . $bulan] : 0;
            $termination_arr[]      = (isset($termination[$tahun . "-" . $bulan])) ? "-" . $termination[$tahun . "-" . $bulan] : 0;
        }


        $response = [
            "success" => true,
            "data" => [
                "label"         => $label_arr,
                "joint"         => $joint_arr,
                "termination"   => $termination_arr
            ]
        ];

        echo json_encode($response);
    }

    private function _joint($tahun = "", $id_cabang = "all", $id_departemen = "all")
    {

        $this->db->where("dk.id_company", $this->id_company);
        // $this->db->where_in("dk.status", ['1','2','3','4']);
        $this->db->where("join_date != '1970-01-01'");
        $this->db->where("join_date != '0000-00-00'");

        if ($tahun != "") {
            $this->db->where("DATE_FORMAT(dk.join_date,'%Y')", $tahun);
        }

        if ($id_cabang != "all") {
            $this->db->where("dk.id_cabang", $id_cabang);
        }

        if ($id_departemen != "all") {
            $this->db->where("dk.id_departemen", $id_departemen);
        }

        $this->db->select("DATE_FORMAT(dk.join_date,'%Y-%m') as bulan,
                            count(dk.id_karyawan) as total");
        $this->db->join("master_jabatan AS mj", "mj.id_jabatan = dk.id_jabatan");
        $this->db->where_not_in("mj.level_user", ["1", "6"]);
        $this->db->group_by("DATE_FORMAT(dk.join_date,'%Y-%m')");
        $this->db->order_by("DATE_FORMAT(dk.join_date,'%Y-%m') ASC");
        $query = $this->db->get("data_karyawan dk")->result();

        $data = [];
        foreach ($query as $row) {
            $data[$row->bulan] = $row->total;
        }

        return $data;
    }

    private function _termination($tahun = "", $id_cabang = "all", $id_departemen = "all")
    {

        $this->db->where("dk.id_company", $this->id_company);
        $this->db->where_in("dk.status", ['5', '6', '7']);
        $this->db->where("tgl_berhenti_bekerja != '1970-01-01'");
        $this->db->where("tgl_berhenti_bekerja != '0000-00-00'");

        if ($tahun != "") {
            $this->db->where("DATE_FORMAT(dk.tgl_berhenti_bekerja,'%Y')", $tahun);
        }

        if ($id_cabang != "all") {
            $this->db->where("dk.id_cabang", $id_cabang);
        }

        if ($id_departemen != "all") {
            $this->db->where("dk.id_departemen", $id_departemen);
        }

        $this->db->select("DATE_FORMAT(dk.tgl_berhenti_bekerja,'%Y-%m') as bulan,
                            count(dk.id_karyawan) as total");
        $this->db->group_by("DATE_FORMAT(dk.tgl_berhenti_bekerja,'%Y-%m')");
        $this->db->order_by("DATE_FORMAT(dk.tgl_berhenti_bekerja,'%Y-%m') ASC");
        $query = $this->db->get("data_karyawan dk")->result();

        $data = [];
        foreach ($query as $row) {
            $data[$row->bulan] = $row->total;
        }

        return $data;
    }


    public function form($jenis = "", $tahun = "", $bulan = "", $id_cabang = "all", $id_departemen = "all")
    {
        $title = $this->lang->line('karyawan') . " " . $jenis . " " . $this->lang->line('tahun') . " " . $tahun;
        if ($id_cabang != "all") {
            $this->db->select("nama");
            $this->db->where("id_cabang", $id_cabang);
            $this->db->where("id_company", $this->id_company);
            $nm_cabang = $this->db->get("master_cabang")->row("nama");
            $title .= ", " . $this->lang->line('cabang') . " " . $nm_cabang;
        }

        if ($id_departemen != "all") {
            $this->db->select("nama");
            $this->db->where("id_departemen", $id_departemen);
            $this->db->where("id_company", $this->id_company);
            $nm_departemen = $this->db->get("master_departemen")->row("nama");
            $title .= ", " . $this->lang->line('departemen') . " " . $nm_departemen;
        }

        $data["title"] = $title;
        $data["url"]   = route('dashboard.absensi.grafik.joint.termination.data.tabel') . "/" . $jenis . "/" . $tahun . "/" . $bulan . "/" . $id_cabang . "/" . $id_departemen;
        $this->load->view('dashboards/form_chart', $data);
    }

    public function dataTable($jenis = "", $tahun = "", $bulan = "", $id_cabang = "all", $id_departemen = "all")
    {

        $bulan = $this->_bulan($bulan);

        $list = $this->_get_datatables($jenis, $tahun, $bulan, $id_cabang, $id_departemen);
        $data = array();
        $no = $_POST['start'];
        foreach ($list as $field) {
            $no++;
            $row = array();
            $row[] = $no;
            $row[] = date_format(date_create($field->tanggal), "d-m-Y");
            $row[] = $field->nama_lengkap . "<br><b>" . $field->nik . "</b>";
            $row[] = $field->nm_jabatan . "<br><b>" . $field->kd_jabatan . "</b>";
            $row[] = $field->nm_cabang . "<br><b>" . $field->kd_cabang . "</b>";
            $row[] = $field->nm_departemen . "<br><b>" . $field->kd_departemen . "</b>";
            $route_detail_karyawan = route('master.karyawan.detail') . "/" . md5($field->id_karyawan);
            $row[] = "<a href='" . $route_detail_karyawan . "' target='_blank'><i class='fa fa-info-circle'></i></a>";
            $data[] = $row;
        }

        $output = array(
            "draw"            => $_POST['draw'],
            "recordsTotal"    => $this->_count_filtered($jenis, $tahun, $bulan, $id_cabang, $id_departemen),
            "recordsFiltered" => $this->_count_filtered($jenis, $tahun, $bulan, $id_cabang, $id_departemen),
            "data"            => $data
        );

        $output = __response($this->MY_response, $output);

        echo json_encode($output);
    }

    private function _get_datatables($jenis = "", $tahun = "", $bulan = "", $id_cabang = "all", $id_departemen = "all")
    {
        $this->_get_datatables_query($jenis, $tahun, $bulan, $id_cabang, $id_departemen);
        if ($_POST['length'] != -1) {
            $this->db->limit($_POST['length'], $_POST['start']);
        }
        $query = $this->db->get();
        return $query->result();
    }

    private function _count_filtered($jenis = "", $tahun = "", $bulan = "", $id_cabang = "all", $id_departemen = "all")
    {
        $this->db->where("dk.id_company", $this->id_company);
        if ($jenis == "Termination") {
            $this->db->where_in("dk.status", ['5', '6', '7']);
            $this->db->where("DATE_FORMAT(dk.tgl_berhenti_bekerja,'%Y')", $tahun);
            $this->db->where("DATE_FORMAT(dk.tgl_berhenti_bekerja,'%m')", $bulan);
        } else {
            // $this->db->where_in("dk.status", ['1','2','3','4']);
            $this->db->where("DATE_FORMAT(dk.join_date,'%Y')", $tahun);
            $this->db->where("DATE_FORMAT(dk.join_date,'%m')", $bulan);
        }


        if ($id_cabang != "all") {
            $this->db->where("dk.id_cabang", $id_cabang);
        }

        if ($id_departemen != "all") {
            $this->db->where("dk.id_departemen", $id_departemen);
        }

        $this->db->where_not_in("mj.level_user", ['1', '6']);

        $this->db->select("dk.id_karyawan");
        $this->db->join("master_jabatan AS mj", "mj.id_jabatan = dk.id_jabatan AND mj.id_company = dk.id_company");
        $this->db->from("data_karyawan AS dk");
        $query = $this->db->get();
        return $query->num_rows();
    }

    private function _get_datatables_query($jenis = "", $tahun = "", $bulan = "", $id_cabang = "all", $id_departemen = "all")
    {
        $this->db->where("dk.id_company", $this->id_company);
        if ($jenis == "Termination") {
            $this->db->where_in("dk.status", ['5', '6', '7']);
            $this->db->where("DATE_FORMAT(dk.tgl_berhenti_bekerja,'%Y')", $tahun);
            $this->db->where("DATE_FORMAT(dk.tgl_berhenti_bekerja,'%m')", $bulan);
            $this->db->select("dk.tgl_berhenti_bekerja AS tanggal");
            $this->db->order_by("dk.tgl_berhenti_bekerja, mc.kode, md.kode, mj.kode_jabatan, dk.nik ASC");
        } else {
            // $this->db->where_in("dk.status", ['1','2','3','4']);
            $this->db->where("DATE_FORMAT(dk.join_date,'%Y')", $tahun);
            $this->db->where("DATE_FORMAT(dk.join_date,'%m')", $bulan);
            $this->db->select("dk.join_date AS tanggal");
            $this->db->order_by("dk.join_date, mc.kode, md.kode, mj.kode_jabatan, dk.nik ASC");
        }


        if ($id_cabang != "all") {
            $this->db->where("dk.id_cabang", $id_cabang);
        }

        if ($id_departemen != "all") {
            $this->db->where("dk.id_departemen", $id_departemen);
        }

        $this->db->where_not_in("mj.level_user", ['1', '6']);


        $this->db->select("dk.id_karyawan,
                            dk.nik,
                            dk.nama_lengkap,
                            dk.foto,
                            mj.kode_jabatan AS kd_jabatan,
                            mj.nama AS nm_jabatan,
                            mc.kode AS kd_cabang,
                            mc.nama AS nm_cabang,
                            md.kode AS kd_departemen,
                            md.nama AS nm_departemen");
        $this->db->join("master_jabatan AS mj", "mj.id_jabatan = dk.id_jabatan AND mj.id_company = dk.id_company");
        $this->db->join("master_cabang AS mc", "mc.id_cabang = dk.id_cabang AND mc.id_company = dk.id_company");
        $this->db->join("master_departemen AS md", "md.id_departemen = dk.id_departemen AND md.id_company = dk.id_company", "LEFT");
        $this->db->from("data_karyawan AS dk");

        $column_search = array('dk.nip');
        $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->like($item, $_POST['search']['value']);
                } else {
                    $this->db->or_like($item, $_POST['search']['value']);
                }

                if (count($column_search) - 1 == $i);
            }
            $i++;
        }

        $column_order = array('', 'dk.nik');

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

    private function _bulan($bulan = "")
    {
        $data = array(
            'Jan' => '01',
            'Feb' => '02',
            'Mar' => '03',
            'Apr' => '04',
            'Mei' => '05',
            'Jun' => '06',
            'Jul' => '07',
            'Agt' => '08',
            'Sep' => '09',
            'Okt' => '10',
            'Nov' => '11',
            'Des' => '12'
        );
        return $data[$bulan];
    }

    private function _bulan_m($bulan)
    {
        $bulan_str = substr($this->lang->line("array_bulan_short"),2,80);
        $bulan_arr = explode("', '",$bulan_str);
        $data = array(
            '01' => $bulan_arr[0],
            '02' => $bulan_arr[1],
            '03' => $bulan_arr[2],
            '04' => $bulan_arr[3],
            '05' => $bulan_arr[4],
            '06' => $bulan_arr[5],
            '07' => $bulan_arr[6],
            '08' => $bulan_arr[7],
            '09' => $bulan_arr[8],
            '10' => $bulan_arr[9],
            '11' => $bulan_arr[10],
            '12' => $bulan_arr[11]
        );
        return $data[$bulan];
    }

    public function dropdown_tahun()
    {
        $this->db->select("DATE_FORMAT(join_date,'%Y') as tahun");
        $this->db->where("id_company", $this->id_company);
        $this->db->where("join_date != '1970-01-01'");
        $this->db->where("join_date != '0000-00-00'");
        $this->db->order_by("join_date", "ASC");
        $this->db->limit(1);
        $this->db->from("data_karyawan");
        $query = $this->db->get()->row();

        $min_tahun = $query->tahun;
        $max_tahun = date('Y');

        $json = [];

        for ($i = $min_tahun; $i <= $max_tahun; $i++) {
            $json[] = [
                'id' => $i,
                'text'  => $i
            ];
        }

        echo json_encode($json);
    }

    public function dropdown_cabang()
    {

        $search = $this->input->get("q");

        $this->db->select('dk.id_cabang,
                            mc.kode as kd_cabang,
                            mc.nama as nm_cabang');
        $this->db->join('master_cabang mc', 'mc.id_cabang = dk.id_cabang');
        $this->db->where('dk.id_company', $this->id_company);
        $this->db->group_start();
        $this->db->like('mc.kode', $search);
        $this->db->or_like('mc.nama', $search);
        $this->db->group_end();
        $this->db->group_by('dk.id_cabang');
        $this->db->order_by('mc.kode', 'ASC');

        $result = $this->db->get('data_karyawan dk')->result();

        $json = [];
        foreach ($result as $row) {
            $json[] = [
                'id'     => $row->id_cabang,
                'text'  => $row->kd_cabang . " - " . $row->nm_cabang
            ];
        }
        echo json_encode($json);
    }

    public function dropdown_departemen($id_cabang = "all")
    {
        $search = $this->input->get("q");

        $this->db->select('dk.id_departemen,
                            md.kode as kd_departemen,
                            md.nama as nm_departemen');
        $this->db->join('master_departemen md', 'md.id_departemen = dk.id_departemen');
        $this->db->where('dk.id_company', $this->id_company);
        $this->db->where('dk.id_cabang', $id_cabang);
        $this->db->group_start();
        $this->db->like('md.kode', $search);
        $this->db->or_like('md.nama', $search);
        $this->db->group_end();
        $this->db->group_by('dk.id_departemen');
        $this->db->order_by('md.kode', 'ASC');

        $result = $this->db->get('data_karyawan dk')->result();

        $json = [];
        foreach ($result as $row) {
            $json[] = [
                'id'     => $row->id_departemen,
                'text'  => $row->kd_departemen . " - " . $row->nm_departemen
            ];
        }
        echo json_encode($json);
    }
}
