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

class Kepegawaian_m extends CI_Model {
    var $table = 'data_karyawan';

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

    function get_datatables(){
        $this->_get_datatables_query();
        if($_GET['length'] != -1){
            $this->db->limit($_GET['length'], $_GET['start']);
        }
        $query = $this->db->get();
        return $query->result();
    }

    function count_filtered(){
        $this->_get_datatables_query();
        $query = $this->db->get();
        return $query->num_rows();
    }

    public function count_all(){
        $this->_get_datatables_query();
        return $this->db->count_all_results();
    }

    public function count_kontrak_habis() {
        $this->_get_datatables_query();
        $this->db->group_by('data_karyawan.id_karyawan');
        return $this->db->count_all_results();
    }

    private function _get_datatables_query(){
        $today = date("Y-m-d");
        $dt = strtotime(date("Y-m-d"));
        $next_month = date("Y-m-d", strtotime("+1 month", $dt));

        $this->db->select("data_karyawan.id_karyawan,
                        data_karyawan.nik,
                        data_karyawan.nama_lengkap,
                        master_jabatan.kode_jabatan,
                        master_jabatan.nama as nama_jabatan,
                        master_cabang.kode as kode_cabang,
                        master_cabang.nama as nama_cabang,
                        master_departemen.kode as kode_departemen,
                        master_departemen.nama as nama_departemen,
                        data_karyawan.tgl_aktif_bekerja,
                        data_karyawan.tgl_berhenti_bekerja,
                        DATEDIFF(data_karyawan.tgl_berhenti_bekerja, CURRENT_DATE()) as jml_hari");
        $this->db->from($this->table);
        $this->db->join("master_jabatan","master_jabatan.id_jabatan = data_karyawan.id_jabatan","LEFT");
        $this->db->join("master_cabang","master_cabang.id_cabang = data_karyawan.id_cabang","LEFT");
        $this->db->join("master_departemen","master_departemen.id_departemen = data_karyawan.id_departemen","LEFT");
        $this->db->where("data_karyawan.status","1");
        $this->db->where("DATEDIFF(data_karyawan.tgl_berhenti_bekerja, CURRENT_DATE()) <=",30);
        $this->db->where("data_karyawan.id_company",$this->id_company);

        $column_search = array('data_karyawan.nik','data_karyawan.nama_lengkap');
        $i = 0;

        foreach ($column_search as $item){  // looping awal
            if($_GET['search']['value']){ // jika datatable mengirimkan pencarian dengan metode POST

                if($i===0){ // looping awal
                    $this->db->group_start();
                    $this->db->like($item, $_GET['search']['value']);
                }else{
                    $this->db->or_like($item, $_GET['search']['value']);
                }

                if(count($column_search) - 1 == $i){
                    $this->db->group_end();
                }
            }
            $i++;
        }

        $column_order =array('data_karyawan.nik','master_cabang.kode','master_departemen.kode','master_jabatan.kode','data_karyawan.tgl_aktif_bekerja','data_karyawan.tgl_berhenti_bekerja');

        if(isset($_GET['order'])){
            $this->db->order_by($column_order[$_GET['order']['0']['column']], $_GET['order']['0']['dir']);
        }
        else{
            $order_by = array("data_karyawan.tgl_berhenti_bekerja"=>"ASC");
            $this->db->order_by(key($order_by), $order_by[key($order_by)]);
        }
    }

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

    public function get_cabang($id = null, $search = null){
        $id_company = $this->session->userdata('id_company');
        $id_cabang  = $this->session->userdata('id_cabang');
        $level      = $this->session->userdata('level_user');

        $this->db->select('nama, id_cabang, kode');
        $this->db->where('id_company', $id_company);


        if ($level == 5 || $level == 2 || $level == 3 || $level == 7) {
            $this->db->where('id_cabang', $id_cabang);
        }

        $this->db->group_start();
        $this->db->like('nama', $search);
        $this->db->or_like('kode', $search);
        $this->db->group_end();
        $this->db->order_by('id_cabang', 'ASC');

        $result = $this->db->get('master_cabang')->result();
        $json = [];

        if ($level == 1 || $level == 4) {
            $json[] = ['id'=> 0, 'text'=>'Semua'];
        }

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

    public function getStatusKaryawan($id_cabang = 0){
        if($id_cabang != 0){
            $this->db->where("data_karyawan.id_cabang",$id_cabang);
        }else{
            if($this->level_user == "5"){
                if($this->multi_cabang == true){
                    $this->db->where_in("data_karyawan.id_cabang",$this->id_cabang_arr);
                }else{
                    $this->db->where("data_karyawan.id_cabang",$this->id_cabang);
                }
            }
        }

        if($this->level_user == "2"){
            if($this->multi_departemen == true){
                $this->db->where_in("data_karyawan.id_departemen",$this->id_departemen_arr);
            }else{
                $this->db->where("data_karyawan.id_departemen",$this->id_company);
            }
        }

        if($this->level_user == "7"){
            $this->db->where("data_karyawan.supervisi",$this->id_karyawan);
        }

        $this->db->select("data_karyawan.status,
                            COUNT(data_karyawan.status) AS jumlah");
        $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']);
        $this->db->where("data_karyawan.id_cabang !=","");
        $this->db->where("data_karyawan.id_cabang !=","0");
        $this->db->where_not_in("master_jabatan.level_user",["1","6"]);
        $this->db->group_by("data_karyawan.status");

        $query = $this->db->get()->result();

        $kontrak        = 0;
        $tetap          = 0;
        // $izin_belajar   = 0;
        // $tugas_belajar  = 0;
        // $resign         = 0;
        // $phk            = 0;
        // $pensiun        = 0;

        foreach($query as $row){

            $status = $row->status;
            $jumlah = $row->jumlah;

            if($status == "1"){
                $kontrak = $jumlah;
            }else if($status == "2"){
                $tetap= $jumlah;
            }
            // else if($status == "3"){
            //     $izin_belajar = $jumlah;
            // }else if($status == "4"){
            //     $tugas_belajar = $jumlah;
            // }else if($status == "5"){
            //     $resign = $jumlah;
            // }else if($status == "6"){
            //     $phk = $jumlah;
            // }else if($status == "7"){
            //     $pensiun = $jumlah;
            // }
        }

        $data = array(
                        "kontrak" =>$kontrak,
                        "tetap"   =>$tetap,
                        // "izin_belajar"=>$izin_belajar,
                        // "tugas_belajar"=>$tugas_belajar,
                        // "resign"=>$resign,
                        // "phk"=>$phk,
                        // "pensiun"=>$pensiun
                    );
        return $data;
    }

    public function getDataPendidikan($id_cabang = 0){
        if($id_cabang != 0){
            $this->db->where("data_karyawan.id_cabang",$id_cabang);
        }else{
            if($this->level_user == "5"){
                if($this->multi_cabang == true){
                    $this->db->where_in("data_karyawan.id_cabang",$this->id_cabang_arr);
                }else{
                    $this->db->where("data_karyawan.id_cabang",$this->id_cabang);
                }
            }
        }

        if($this->level_user == "2"){
            if($this->multi_departemen == true){
                $this->db->where_in("data_karyawan.id_departemen",$this->id_departemen_arr);
            }else{
                $this->db->where("data_karyawan.id_departemen",$this->id_company);
            }
        }

        if($this->level_user == "7"){
            $this->db->where("data_karyawan.supervisi",$this->id_karyawan);
        }

        $this->db->select("data_karyawan.pendidikan_terakhir");
        $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_not_in("master_jabatan.level_user",["1","6"]);
        $this->db->where_in("data_karyawan.status",['1','2','3','4']);
        $this->db->where("data_karyawan.id_cabang !=","");
        $this->db->where("data_karyawan.id_cabang !=","0");

        $query = $this->db->get()->result();

        $sd     = 0;
        $smp    = 0;
        $sma    = 0;
        $d3     = 0;
        $d4     = 0;
        $s1     = 0;
        $s2     = 0;
        $s3     = 0;
        $s4     = 0;
        $tidak_diketahui = 0;

        foreach($query as $row){
            $pendidikan = $row->pendidikan_terakhir;

            if($pendidikan == "SD"){
                $sd = $sd + 1;
            }else if($pendidikan == "SMP"){
                $smp = $smp + 1;
            }else if($pendidikan == "SMA"){
                $sma = $sma + 1;
            }else if($pendidikan == "D3"){
                $d3 = $d3 + 1;
            }else if($pendidikan == "D4"){
                $d4 = $d4 + 1;
            }else if($pendidikan == "S1"){
                $s1 = $s1 + 1;
            }else if($pendidikan == "S2"){
                $s2 = $s2 + 1;
            }else if($pendidikan == "S3"){
                $s3 = $s3 + 1;
            }else{
                $tidak_diketahui = $tidak_diketahui + 1;
            }
        }

        $data = array("sd"    => $sd,
                    "smp"   => $smp,
                    "sma"   => $sma,
                    "d3"    => $d3,
                    "d4"    => $d4,
                    "s1"    => $s1,
                    "s2"    => $s2,
                    "s3"    => $s3,
                    "s4"    => $s4,
                    "tidak_diketahui" =>$tidak_diketahui);
        return $data;
    }

    private function subGetDataNotifKepegawaian() {
        $this->db->select('
            dk.nama_lengkap,
            dk.nik,
            group_concat(distinct(notif.menu)) as menu,
            notif.id_karyawan_pengajuan
        ');
        $this->db->from('notif_kepegawaian notif');
        $this->db->join('data_karyawan dk', 'notif.id_karyawan_pengajuan = dk.id_karyawan');
        $this->db->where('notif.id_karyawan', $this->id_karyawan);
        $this->db->where('notif.jenis', 'pengajuan');
        $this->db->where('notif.flag_baca', '0');
        $this->db->group_by('notif.id_karyawan_pengajuan');
        $this->db->order_by('notif.tgl_notif', 'desc');
    }

    public function getDataNotifKepegawaian()
    {
        $this->subGetDataNotifKepegawaian();
        return $this->db->get()->result();
    }

    public function totalNotifKepegawaian() {
        $this->subGetDataNotifKepegawaian();
        return $this->db->count_all_results();
    }

    # BEGIN DATA TABLE TABS PENGGUNA AKTIF
    function get_datatables_tabs_pengguna_aktif($jenis = null, $id_cabang = "0")
    {
        $this->_get_datatables_query_tabs_pengguna_aktif($jenis, $id_cabang);
        if ($_GET['length'] != -1) {
            $this->db->limit($_GET['length'], $_GET['start']);
        }
        $query = $this->db->get();
        return $query->result();
    }

    function count_filtered_tabs_pengguna_aktif($jenis = null, $id_cabang = "0")
    {
        $this->_get_datatables_query_tabs_pengguna_aktif($jenis, $id_cabang);
        $query = $this->db->get();
        return $query->num_rows();
    }

    public function count_all_tabs_pengguna_aktif($jenis = null, $id_cabang = "0")
    {
        $this->_get_datatables_query_tabs_pengguna_aktif($jenis, $id_cabang);
        return $this->db->count_all_results();
    }

    private function _get_datatables_query_tabs_pengguna_aktif($jenis = null, $id_cabang = "all")
    {

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

        $this->db->select("data_karyawan.id_karyawan,
                            data_karyawan.nama_lengkap,
                            data_karyawan.nik,
                            data_karyawan.foto,
                            data_karyawan.status,
                            data_karyawan.jenis_kelamin,
                            master_cabang.nama as nama_cabang,
                            master_cabang.kode as kode_cabang,
                            master_departemen.nama as nama_departemen ,
                            master_departemen.kode as kode_departemen,
                            master_jabatan.nama as nama_jabatan,
                            master_jabatan.kode_jabatan as kode_jabatan");
        $this->db->from($this->table);
        $this->db->join('master_cabang', 'master_cabang.id_cabang = data_karyawan.id_cabang');
        $this->db->join('master_departemen', 'master_departemen.id_departemen = data_karyawan.id_departemen', 'LEFT');
        $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("data_karyawan.status", $jenis);
        $this->db->where_not_in("master_jabatan.level_user", ["1", "6"]);

        $column_search = array('master_cabang.nama', 'master_departemen.nama', 'data_karyawan.id_karyawan', 'data_karyawan.nama_lengkap');
        $i = 0;

        foreach ($column_search as $item) {  // looping awal
            if ($_GET['search']['value']) { // jika datatable mengirimkan pencarian dengan metode POST

                if ($i === 0) { // looping awal
                    $this->db->group_start();
                    $this->db->like($item, $_GET['search']['value']);
                } else {
                    $this->db->or_like($item, $_GET['search']['value']);
                }

                if (count($column_search) - 1 == $i) {
                    $this->db->group_end();
                }
            }
            $i++;
        }

        $column_order = array('', '', 'data_karyawan.nama_lengkap', 'master_cabang.nama', 'master_departemen.nama', 'master_jabatan.nama', 'data_karyawan.jenis_kelamin');

        if (isset($_GET['order'])) {
            $this->db->order_by($column_order[$_GET['order']['0']['column']], $_GET['order']['0']['dir']);
        } else {
            $order_by = array("data_karyawan.id_karyawan" => "DESC");
            $this->db->order_by(key($order_by), $order_by[key($order_by)]);
        }
    }
    # END DATA TABLE TABS PENGGUNA AKTIF

    # BEGIN DATA TABLE TABS PENDIDIKAN
    function get_datatables_tabs_pendidikan($pendidikan, $id_cabang)
    {
        $this->_get_datatables_query_tabs_pendidikan($pendidikan, $id_cabang);
        if ($_GET['length'] != -1) {
            $this->db->limit($_GET['length'], $_GET['start']);
        }
        $query = $this->db->get();
        return $query->result();
    }

    function count_filtered_tabs_pendidikan($pendidikan, $id_cabang)
    {
        $this->_get_datatables_query_tabs_pendidikan($pendidikan, $id_cabang);
        $query = $this->db->get();
        return $query->num_rows();
    }

    public function count_all_tabs_pendidikan($pendidikan, $id_cabang)
    {
        $this->_get_datatables_query_tabs_pendidikan($pendidikan, $id_cabang);
        return $this->db->count_all_results();
    }

    private function _get_datatables_query_tabs_pendidikan($pendidikan, $id_cabang)
    {
        if ($id_cabang != "all") {
            $this->db->where("data_karyawan.id_cabang", $id_cabang);
        } else {
            if ($this->level_user == "5") {
                if ($this->multi_cabang == true) {
                    $this->db->where_in("data_karyawan.id_cabang", $this->id_cabang_arr);
                } else {
                    $this->db->where("data_karyawan.id_cabang", $this->id_cabang);
                }
            } else if ($this->level_user == "2") {
                $this->db->where("data_karyawan.id_cabang", $this->id_cabang);
            }
        }


        $this->db->select("data_karyawan.id_karyawan,
                            data_karyawan.nama_lengkap,
                            data_karyawan.nik,
                            data_karyawan.foto,
                            data_karyawan.pendidikan_terakhir,
                            data_karyawan.jenis_kelamin,
                            master_cabang.nama as nama_cabang,
                            master_cabang.kode as kode_cabang,
                            master_departemen.nama as nama_departemen ,
                            master_departemen.kode as kode_departemen,
                            master_jabatan.nama as nama_jabatan,
                            master_jabatan.kode_jabatan as kode_jabatan");
        $this->db->from($this->table);
        $this->db->join('master_cabang', 'master_cabang.id_cabang = data_karyawan.id_cabang');
        $this->db->join('master_departemen', 'master_departemen.id_departemen = data_karyawan.id_departemen', 'LEFT');
        $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"]);
        $this->db->where("data_karyawan.pendidikan_terakhir", $pendidikan);

        $column_search = array('data_karyawan.nik', 'data_karyawan.nama_lengkap', 'master_cabang.nama', 'master_departemen.nama', 'master_jabatan.nama');
        $i = 0;

        foreach ($column_search as $item) {  // looping awal
            if ($_GET['search']['value']) { // jika datatable mengirimkan pencarian dengan metode POST

                if ($i === 0) { // looping awal
                    $this->db->group_start();
                    $this->db->like($item, $_GET['search']['value']);
                } else {
                    $this->db->or_like($item, $_GET['search']['value']);
                }

                if (count($column_search) - 1 == $i) {
                    $this->db->group_end();
                }
            }
            $i++;
        }

        $column_order = array('', '', 'data_karyawan.nama_lengkap', 'master_cabang.nama', 'master_departemen.nama', 'master_jabatan.nama', 'data_karyawan.jenis_kelamin', null);

        if (isset($_GET['order'])) {
            $this->db->order_by($column_order[$_GET['order']['0']['column']], $_GET['order']['0']['dir']);
        } else {
            $order_by = array("data_karyawan.id_karyawan" => "DESC");
            $this->db->order_by(key($order_by), $order_by[key($order_by)]);
        }
    }
    # END DATA TABLE TABS PENDIDIKAN
}