<?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 = "Karyawan ".$jenis." 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 .= ", 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 .= ", 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('Januari'     => '01',
        //                 'Februari'  => '02',
        //                 'Maret'     => '03',
        //                 'April'     => '04',
        //                 'Mei'       => '05',
        //                 'Juni'      => '06',
        //                 'Juli'      => '07',
        //                 'Agustus'   => '08',
        //                 'September' => '09',
        //                 'Oktober'   => '10',
        //                 'November'  => '11',
        //                 'Desember'  => '12');
        $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){
        $data = array('01'     => 'Jan',
                        '02' => 'Feb',
                        '03' => 'Mar',
                        '04' => 'Apr',
                        '05' => 'Mei',
                        '06' => 'Jun',
                        '07' => 'Jul',
                        '08' => 'Agt',
                        '09' => 'Sep',
                        '10' => 'Okt',
                        '11' => 'Nov',
                        '12' => 'Des');
        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);
    }
}
