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

class Data_karyawan extends MY_Controller {

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

	public function index(){
	}

    public function dataKaryawan($id_cabang="all", $id_departemen="all"){

        $min_tahun  = $this->_minKaryawanIn();
        $mx_tahun   = date('Y');

        $karyawan_in_arr = $this->_karyawanIn($id_cabang, $id_departemen);
        $karyawan_out_arr = $this->_karyawanOut($id_cabang, $id_departemen);

        $label_arr          = [];
        $ttl_karyawan_arr   = [];
        $no = 0;

        for($i=$min_tahun; $i<=$mx_tahun; $i++){
            $label_arr[]        = $i;
            $karyawan_in    = (isset($karyawan_in_arr[$i]))?$karyawan_in_arr[$i]:0;
            $karyawan_out   = (isset($karyawan_out_arr[$i]))?$karyawan_out_arr[$i]:0;
            $jml_karyawan   = $jml_karyawan + $karyawan_in - $karyawan_out;
            $ttl_karyawan_arr[] = $jml_karyawan;
        }

        $response = ["success" => true,
                    "data" =>[
                        "label"         => $label_arr,
                        "ttl_karyawan"  => $ttl_karyawan_arr
                    ]
                ];

        echo json_encode($response);
    }

    private function _karyawanIn($id_cabang="all", $id_departemen="all"){
        $this->db->where("dk.id_company",$this->id_company);
        $this->db->where_not_in("mj.level_user",['1','6']);

        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') as tahun, count(dk.id_karyawan) as total");
        $this->db->join("master_jabatan mj","mj.id_jabatan = dk.id_jabatan");
        $this->db->group_by("DATE_FORMAT(dk.join_date,'%Y')");
        $this->db->order_by("DATE_FORMAT(dk.join_date,'%Y') ASC");
        $query = $this->db->get("data_karyawan dk")->result();

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

        return $ttl_karyawan_arr;

    }

    private function _karyawanOut($id_cabang="all", $id_departemen="all"){
        $this->db->where("dk.id_company",$this->id_company);
        $this->db->where_not_in("mj.level_user",['1','6']);
        $this->db->where_in("dk.status",['5', '6', '7']);

        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') as tahun, count(dk.id_karyawan) as total");
        $this->db->join("master_jabatan mj","mj.id_jabatan = dk.id_jabatan");
        $this->db->group_by("DATE_FORMAT(dk.join_date,'%Y')");
        $this->db->order_by("DATE_FORMAT(dk.join_date,'%Y') ASC");
        $query = $this->db->get("data_karyawan dk")->result();

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

        return $ttl_karyawan_arr;

    }

    private function _minKaryawanIn(){
        $this->db->select("MIN(DATE_FORMAT(dk.join_date,'%Y')) as tahun");
        $this->db->join("master_jabatan mj","mj.id_jabatan = dk.id_jabatan");
        $this->db->where("dk.id_company",$this->id_company);
        $this->db->where_not_in("mj.level_user",['1','6']);
        $this->db->where_in("dk.status",['1', '2', '3', '4']);
        $this->db->where("DATE_FORMAT(dk.join_date,'%Y') != '0000'");
        $this->db->group_by("DATE_FORMAT(dk.join_date,'%Y')");
        $query = $this->db->get("data_karyawan dk")->row('tahun');
        return $query;
    }

    public function form($tahun="", $id_cabang="all", $id_departemen="all"){
        $title = "Data Karyawan 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.data.karyawan.data.tabel')."/".$tahun."/".$id_cabang."/".$id_departemen;
        $this->load->view('dashboards/form_chart',$data);
    }

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

		$list = $this->_get_datatables($tahun, $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($tahun, $id_cabang, $id_departemen),
						"recordsFiltered" => $this->_count_filtered($tahun, $id_cabang, $id_departemen),
						"data"            => $data);

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

		echo json_encode($output);
	}

    private function _get_datatables($tahun="", $id_cabang="all", $id_departemen="all"){
        $this->_get_datatables_query($tahun, $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($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("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->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($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_not_in("DATE_FORMAT(dk.join_date,'%Y')",['0000','1970']);
        $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->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,
                            dk.join_date AS tanggal");
        $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");
            $this->db->order_by("dk.join_date DESC, mc.kode ASC, md.kode ASC, mj.kode_jabatan ASC, dk.nik ASC");
        }
    }

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