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

class Karyawan_m extends CI_Model {
    var $table = 'data_karyawan';
    public function __construct(){
        parent::__construct();
    }

    # BEGIN DATA TABLE TABS JABATAN
    function get_datatables_tabs_jabatan($jenis=null, $id_cabang="0", $id_departemen="0", $id_jabatan="0", $status="0",$id_karyawan="0", $order_by="default"){
        $this->_get_datatables_query_tabs_jabatan($jenis, $id_cabang, $id_departemen, $id_jabatan, $status, $id_karyawan, $order_by);
        if($_GET['length'] != -1){
            $this->db->limit($_GET['length'], $_GET['start']);
        }
        $query = $this->db->get();
        return $query->result();
    }

    function count_filtered_tabs_jabatan($jenis=null, $id_cabang="0", $id_departemen="0", $id_jabatan="0", $status="0",$id_karyawan="0", $order_by="default"){
        $this->_get_datatables_query_tabs_jabatan($jenis, $id_cabang, $id_departemen, $id_jabatan, $status, $id_karyawan, $order_by);
        $query = $this->db->get();
        return $query->num_rows();
    }

    public function count_all_tabs_jabatan($jenis=null, $id_cabang="0", $id_departemen="0", $id_jabatan="0", $status="0",$id_karyawan="0", $order_by="default"){
        $this->_get_datatables_query_tabs_jabatan($jenis, $id_cabang, $id_departemen, $id_jabatan, $status, $id_karyawan, $order_by);
        return $this->db->count_all_results();
    }
    private function _get_datatables_query_tabs_jabatan($jenis=null, $id_cabang="0", $id_departemen="0", $id_jabatan="0", $status="0",$id_karyawan="0", $order_by="default"){

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

        if($id_karyawan != "0"){
            $this->db->where("data_karyawan.id_karyawan",$id_karyawan);
        }else{
            if($this->level_user == "7"){
                $this->db->group_start();
                $this->db->where("data_karyawan.supervisi",$this->id_karyawan);
                $this->db->or_where("data_karyawan.id_karyawan",$this->id_karyawan);
                $this->db->group_end();
            }
        }

        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);
                }
            }else if($this->level_user == "2"){
                $this->db->where("data_karyawan.id_cabang",$this->id_cabang);
            }
        }

        if($id_departemen != "0") {
            $this->db->where("data_karyawan.id_departemen",$id_departemen);
        }else{
            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_in("data_karyawan.id_departemen",$this->id_departemen);
                }
            }
        }

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

        $this->db->select("data_karyawan.id_karyawan,
                            data_karyawan.nama_lengkap,
                            data_karyawan.nik,
							data_karyawan.foto,
                            data_karyawan.status,
                            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,
                            master_jabatan.level_user,
                            data_karyawan.jatah_cuti,
                            data_karyawan.tgl_aktif_bekerja,
                            data_karyawan.tgl_berhenti_bekerja,
                            data_karyawan.status,
                            data_karyawan.jenis_kelamin,
                            data_karyawan.email,
                            data_karyawan.telepon,
                            data_karyawan.tempat_lahir,
                            data_karyawan.tgl_lahir,
                            data_karyawan.alamat_domisili,
                            data_karyawan.golongan_darah,
                            data_karyawan.agama");
        $this->db->from($this->table);
        $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->join('master_jabatan', 'master_jabatan.id_jabatan = data_karyawan.id_jabatan', 'LEFT');
        $this->db->where('data_karyawan.id_company',$this->id_company);

        $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');

        if(isset($_GET['order'])){
            $this->db->order_by($column_order[$_GET['order']['0']['column']], $_GET['order']['0']['dir']);
        }
        else{
            if($order_by == "default"){
                $order_by = array("data_karyawan.id_karyawan"=>"DESC");
                $this->db->order_by(key($order_by), $order_by[key($order_by)]);
            }else{
                $this->db->order_by("master_cabang.nama ASC, master_departemen.nama ASC, master_jabatan.nama ASC, data_karyawan.nik ASC");
            }


        }
    }
    # END DATA TABLE TABS JABATAN


    # BEGIN DATA TABLE TABS PENGGUNA AKTIF
    function get_datatables_tabs_pengguna_aktif($jenis=null, $id_cabang="0", $id_departemen="0", $id_jabatan="0", $id_karyawan="0"){
        $this->_get_datatables_query_tabs_pengguna_aktif($jenis, $id_cabang, $id_departemen, $id_jabatan, $id_karyawan);
        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", $id_departemen="0", $id_jabatan="0", $id_karyawan="0"){
        $this->_get_datatables_query_tabs_pengguna_aktif($jenis, $id_cabang, $id_departemen, $id_jabatan, $id_karyawan);
        $query = $this->db->get();
        return $query->num_rows();
    }

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

    private function _get_datatables_query_tabs_pengguna_aktif($jenis=null, $id_cabang="0", $id_departemen="0", $id_jabatan="0", $id_karyawan="0"){

        // if($status != "0"){
        //     $this->db->where("data_karyawan.status",$status);
        // }

        if($id_karyawan != "0"){
            $this->db->where("data_karyawan.id_karyawan",$id_karyawan);
        }else{
            if($this->level_user == "7"){
                $this->db->group_start();
                $this->db->where("data_karyawan.supervisi",$this->id_karyawan);
                $this->db->or_where("data_karyawan.id_karyawan",$this->id_karyawan);
                $this->db->group_end();
            }
        }

        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);
                }
            }else if($this->level_user == "2"){
                $this->db->where("data_karyawan.id_cabang",$this->id_cabang);
            }
        }

        if($id_departemen != "0") {
            $this->db->where("data_karyawan.id_departemen",$id_departemen);
        }else{
            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_in("data_karyawan.id_departemen",$this->id_departemen);
                }
            }
        }

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

        $this->db->select("data_karyawan.id_karyawan,
                            data_karyawan.nama_lengkap,
                            data_karyawan.nik,
							data_karyawan.foto,
                            data_karyawan.status,
                            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', 'LEFT');
        $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', 'LEFT');
        $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"]);

        $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');

        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($jenis=null, $id_cabang="0", $id_departemen="0", $id_jabatan="0", $pendidikan="0",$id_karyawan="0"){
        $this->_get_datatables_query_tabs_pendidikan($jenis, $id_cabang, $id_departemen, $id_jabatan, $pendidikan, $id_karyawan);
        if($_GET['length'] != -1){
            $this->db->limit($_GET['length'], $_GET['start']);
        }
        $query = $this->db->get();
        return $query->result();
    }

    function count_filtered_tabs_pendidikan($jenis=null, $id_cabang="0", $id_departemen="0", $id_jabatan="0", $pendidikan="0",$id_karyawan="0"){
        $this->_get_datatables_query_tabs_pendidikan($jenis, $id_cabang, $id_departemen, $id_jabatan, $pendidikan, $id_karyawan);
        $query = $this->db->get();
        return $query->num_rows();
    }

    public function count_all_tabs_pendidikan($jenis=null, $id_cabang="0", $id_departemen="0", $id_jabatan="0", $pendidikan="0",$id_karyawan="0"){
        $this->_get_datatables_query_tabs_pendidikan($jenis, $id_cabang, $id_departemen, $id_jabatan, $pendidikan, $id_karyawan);
        return $this->db->count_all_results();
    }

    private function _get_datatables_query_tabs_pendidikan($jenis=null, $id_cabang="0", $id_departemen="0", $id_jabatan="0", $pendidikan="0",$id_karyawan="0"){

        if($pendidikan != "0"){
            $this->db->where("data_karyawan.pendidikan_terakhir",$pendidikan);
        }

        if($id_karyawan != "0"){
            $this->db->where("data_karyawan.id_karyawan",$id_karyawan);
        }else{
            if($this->level_user == "7"){
                $this->db->group_start();
                $this->db->where("data_karyawan.supervisi",$this->id_karyawan);
                $this->db->or_where("data_karyawan.id_karyawan",$this->id_karyawan);
                $this->db->group_end();
            }
        }

        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);
                }
            }else if($this->level_user == "2"){
                $this->db->where("data_karyawan.id_cabang",$this->id_cabang);
            }
        }

        if($id_departemen != "0") {
            $this->db->where("data_karyawan.id_departemen",$id_departemen);
        }else{
            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_in("data_karyawan.id_departemen",$this->id_departemen);
                }
            }
        }

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

        $this->db->select("data_karyawan.id_karyawan,
                            data_karyawan.nama_lengkap,
                            data_karyawan.nik,
                            data_karyawan.foto,
                            data_karyawan.pendidikan_terakhir,
                            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', 'LEFT');
        $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', 'LEFT');
        $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"]);

        $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');

        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


    # BEGIN DATA TABLE TABS LOCK LOKASI
    function get_datatables_tabs_lokasi($jenis=null, $id_cabang="-", $id_departemen="-", $id_jabatan="-", $lock_lokasi="-",$id_karyawan="-"){
        $this->_get_datatables_query_tabs_lokasi($jenis, $id_cabang, $id_departemen, $id_jabatan, $lock_lokasi, $id_karyawan);
        if($_GET['length'] != -1){
            $this->db->limit($_GET['length'], $_GET['start']);
        }
        $query = $this->db->get();
        return $query->result();
    }

    function count_filtered_tabs_lokasi($jenis=null, $id_cabang="-", $id_departemen="-", $id_jabatan="-", $lock_lokasi="-",$id_karyawan="-"){
        $this->_get_datatables_query_tabs_lokasi($jenis, $id_cabang, $id_departemen, $id_jabatan, $lock_lokasi, $id_karyawan);
        $query = $this->db->get();
        return $query->num_rows();
    }

    public function count_all_tabs_lokasi($jenis=null, $id_cabang="-", $id_departemen="-", $id_jabatan="-", $lock_lokasi="-",$id_karyawan="-"){
        $this->_get_datatables_query_tabs_lokasi($jenis, $id_cabang, $id_departemen, $id_jabatan, $lock_lokasi, $id_karyawan);
        return $this->db->count_all_results();
    }

    private function _get_datatables_query_tabs_lokasi($jenis=null, $id_cabang="-", $id_departemen="-", $id_jabatan="-", $lock_lokasi="-",$id_karyawan="0"){

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

        if($id_karyawan != "0"){
            $this->db->where("data_karyawan.id_karyawan",$id_karyawan);
        }else{
            if($this->level_user == "7"){
                $this->db->group_start();
                $this->db->where("data_karyawan.supervisi",$this->id_karyawan);
                $this->db->or_where("data_karyawan.id_karyawan",$this->id_karyawan);
                $this->db->group_end();
            }
        }

        if($id_cabang != "-") {
            $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);
            }
        }

        if($id_departemen != "-") {
            $this->db->where("data_karyawan.id_departemen",$id_departemen);
        }else{
            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_in("data_karyawan.id_departemen",$this->id_departemen);
                }
            }
        }

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

        $this->db->select("data_karyawan.id_karyawan,
                            data_karyawan.nama_lengkap,
                            data_karyawan.nik,
                            data_karyawan.foto,
                            data_karyawan.pendidikan_terakhir,
                            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,
                            data_karyawan.lock_lokasi");
        $this->db->from($this->table);
        $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->join('master_jabatan', 'master_jabatan.id_jabatan = data_karyawan.id_jabatan', 'LEFT');
        $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"]);

        $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');

        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 LOCK LOKASI

    # BEGIN DATA TABLE TABS LOCK DEVICE
    function get_datatables_tabs_device($jenis=null, $id_cabang="-", $id_departemen="-", $id_jabatan="-", $lock_device="-",$id_karyawan="-"){
        $this->_get_datatables_query_tabs_device($jenis, $id_cabang, $id_departemen, $id_jabatan, $lock_device, $id_karyawan);
        if($_GET['length'] != -1){
            $this->db->limit($_GET['length'], $_GET['start']);
        }
        $query = $this->db->get();
        return $query->result();
    }

    function count_filtered_tabs_device($jenis=null, $id_cabang="-", $id_departemen="-", $id_jabatan="-", $lock_device="-",$id_karyawan="-"){
        $this->_get_datatables_query_tabs_device($jenis, $id_cabang, $id_departemen, $id_jabatan, $lock_device, $id_karyawan);
        $query = $this->db->get();
        return $query->num_rows();
    }

    public function count_all_tabs_device($jenis=null, $id_cabang="-", $id_departemen="-", $id_jabatan="-", $lock_device="-",$id_karyawan="-"){
        $this->_get_datatables_query_tabs_device($jenis, $id_cabang, $id_departemen, $id_jabatan, $lock_device, $id_karyawan);
        return $this->db->count_all_results();
    }

    private function _get_datatables_query_tabs_device($jenis=null, $id_cabang="-", $id_departemen="-", $id_jabatan="-", $lock_device="-",$id_karyawan="0"){
        if($lock_device != "-"){
            $this->db->where("users.lock_device",$lock_device);
        }

        if($id_karyawan != "0"){
            $this->db->where("data_karyawan.id_karyawan",$id_karyawan);
        }else{
            if($this->level_user == "7"){
                $this->db->group_start();
                $this->db->where("data_karyawan.supervisi",$this->id_karyawan);
                $this->db->or_where("data_karyawan.id_karyawan",$this->id_karyawan);
                $this->db->group_end();
            }
        }

        if($id_cabang != "-") {
            $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);
            }
        }

        if($id_departemen != "-") {
            $this->db->where("data_karyawan.id_departemen",$id_departemen);
        }else{
            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_in("data_karyawan.id_departemen",$this->id_departemen);
                }
            }
        }

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

        $this->db->select("data_karyawan.id_karyawan,
                            data_karyawan.nama_lengkap,
                            data_karyawan.nik,
                            data_karyawan.foto,
                            data_karyawan.pendidikan_terakhir,
                            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,
                            users.lock_device");
        $this->db->from($this->table);
        $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->join('master_jabatan', 'master_jabatan.id_jabatan = data_karyawan.id_jabatan', 'LEFT');
        $this->db->join('users', 'users.id_karyawan = data_karyawan.id_karyawan', 'LEFT');
        $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"]);

        $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');

        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 LOCK DEVICE


    # BEGIN DATA TABLE TABS LOCK FACE RECOGNITION
    function get_datatables_tabs_facerecognition($jenis=null, $id_cabang="-", $id_departemen="-", $id_jabatan="-", $lock_facerecognition="-",$id_karyawan="-"){
        $this->_get_datatables_query_tabs_facerecognition($jenis, $id_cabang, $id_departemen, $id_jabatan, $lock_facerecognition, $id_karyawan);
        if($_GET['length'] != -1){
            $this->db->limit($_GET['length'], $_GET['start']);
        }
        $query = $this->db->get();
        return $query->result();
    }

    function count_filtered_tabs_facerecognition($jenis=null, $id_cabang="-", $id_departemen="-", $id_jabatan="-", $lock_facerecognition="-",$id_karyawan="-"){
        $this->_get_datatables_query_tabs_facerecognition($jenis, $id_cabang, $id_departemen, $id_jabatan, $lock_facerecognition, $id_karyawan);
        $query = $this->db->get();
        return $query->num_rows();
    }

    public function count_all_tabs_facerecognition($jenis=null, $id_cabang="-", $id_departemen="-", $id_jabatan="-", $lock_facerecognition="-",$id_karyawan="-"){
        $this->_get_datatables_query_tabs_facerecognition($jenis, $id_cabang, $id_departemen, $id_jabatan, $lock_facerecognition, $id_karyawan);
        return $this->db->count_all_results();
    }

    private function _get_datatables_query_tabs_facerecognition($jenis=null, $id_cabang="-", $id_departemen="-", $id_jabatan="-", $lock_facerecognition="-",$id_karyawan="0"){
        if($lock_facerecognition != "-"){
            $this->db->where("users.lock_facerecognition",$lock_facerecognition);
        }

        if($id_karyawan != "0"){
            $this->db->where("data_karyawan.id_karyawan",$id_karyawan);
        }else{
            if($this->level_user == "7"){
                $this->db->group_start();
                $this->db->where("data_karyawan.supervisi",$this->id_karyawan);
                $this->db->or_where("data_karyawan.id_karyawan",$this->id_karyawan);
                $this->db->group_end();
            }
        }

        if($id_cabang != "-") {
            $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);
            }
        }

        if($id_departemen != "-") {
            $this->db->where("data_karyawan.id_departemen",$id_departemen);
        }else{
            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_in("data_karyawan.id_departemen",$this->id_departemen);
                }
            }
        }

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

        $this->db->select("data_karyawan.id_karyawan,
                            data_karyawan.nama_lengkap,
                            data_karyawan.nik,
                            data_karyawan.foto,
                            data_karyawan.pendidikan_terakhir,
                            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,
                            users.lock_facerecognition");
        $this->db->from($this->table);
        $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->join('master_jabatan', 'master_jabatan.id_jabatan = data_karyawan.id_jabatan', 'LEFT');
        $this->db->join('users', 'users.id_karyawan = data_karyawan.id_karyawan', 'LEFT');
        $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"]);

        $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');

        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 LOCK FACE RECOGNITION


    # BEGIN DATA TABLE TABS LOCK FACE KAMERA
    function get_datatables_tabs_camera($jenis=null, $id_cabang="-", $id_departemen="-", $id_jabatan="-", $camera="-",$id_karyawan="-"){
        $this->_get_datatables_query_tabs_camera($jenis, $id_cabang, $id_departemen, $id_jabatan, $camera, $id_karyawan);
        if($_GET['length'] != -1){
            $this->db->limit($_GET['length'], $_GET['start']);
        }
        $query = $this->db->get();
        return $query->result();
    }

    function count_filtered_tabs_camera($jenis=null, $id_cabang="-", $id_departemen="-", $id_jabatan="-", $camera="-",$id_karyawan="-"){
        $this->_get_datatables_query_tabs_camera($jenis, $id_cabang, $id_departemen, $id_jabatan, $camera, $id_karyawan);
        $query = $this->db->get();
        return $query->num_rows();
    }

    public function count_all_tabs_camera($jenis=null, $id_cabang="-", $id_departemen="-", $id_jabatan="-", $camera="-",$id_karyawan="-"){
        $this->_get_datatables_query_tabs_camera($jenis, $id_cabang, $id_departemen, $id_jabatan, $camera, $id_karyawan);
        return $this->db->count_all_results();
    }

    private function _get_datatables_query_tabs_camera($jenis=null, $id_cabang="-", $id_departemen="-", $id_jabatan="-", $camera="-",$id_karyawan="0"){

        if($camera != "-"){
            $this->db->where("users.kamera",$camera);
        }

        if($id_karyawan != "0"){
            $this->db->where("data_karyawan.id_karyawan",$id_karyawan);
        }else{
            if($this->level_user == "7"){
                $this->db->group_start();
                $this->db->where("data_karyawan.supervisi",$this->id_karyawan);
                $this->db->or_where("data_karyawan.id_karyawan",$this->id_karyawan);
                $this->db->group_end();
            }
        }

        if($id_cabang != "-") {
            $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);
            }
        }

        if($id_departemen != "-") {
            $this->db->where("data_karyawan.id_departemen",$id_departemen);
        }else{
            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_in("data_karyawan.id_departemen",$this->id_departemen);
                }
            }
        }

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

        $this->db->select("data_karyawan.id_karyawan,
                            data_karyawan.nama_lengkap,
                            data_karyawan.nik,
                            data_karyawan.foto,
                            data_karyawan.pendidikan_terakhir,
                            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,
                            users.kamera");
        $this->db->from($this->table);
        $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->join('master_jabatan', 'master_jabatan.id_jabatan = data_karyawan.id_jabatan', 'LEFT');
        $this->db->join('users', 'users.id_karyawan = data_karyawan.id_karyawan', 'LEFT');
        $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"]);

        $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');

        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 LOCK FACE KAMERA


    public function getDataById($id=null){
        $select = $this->db->select("dk.id_karyawan,
                                    dk.nik,
                                    dk.nama_lengkap,
                                    dk.gelar_depan,
                                    dk.gelar_belakang,
                                    dk.no_ktp,
                                    dk.jenis_kelamin,
                                    dk.golongan_darah,
                                    dk.agama,
                                    dk.tempat_lahir,
                                    dk.tgl_lahir,
                                    dk.alamat_domisili,
                                    dk.rt,
                                    dk.rw,
                                    master_provinsi.nama as nama_prov,
                                    master_kota.id_kota,
                                    master_kota.nama as nama_kota,
                                    master_kecamatan.id_kecamatan,
                                    master_kecamatan.nama as nama_kec,
                                    master_kelurahan.id_kelurahan,
                                    master_kelurahan.nama as nama_kel,
                                    dk.kode_pos,
                                    dk.telepon,
                                    dk.email,
                                    dk.tgl_aktif_bekerja,
                                    dk.tgl_berhenti_bekerja,
                                    dk.id_departemen,
                                    dk.id_cabang,
                                    dk.id_jabatan,
                                    dk.supervisi,
                                    dk.jatah_cuti,
                                    dk.pendidikan_terakhir,
                                    dk.status,
                                    dk.foto,
                                    master_cabang.id_cabang,
                                    master_cabang.kode as kode_cabang,
                                    master_cabang.nama as nama_cabang,
                                    master_departemen.id_departemen,
                                    master_departemen.kode as kode_departemen,
                                    master_departemen.nama as nama_departemen,
                                    master_jabatan.id_jabatan,
                                    master_jabatan.kode_jabatan,
                                    master_jabatan.nama as nama_jabatan,
                                    master_jabatan.level_user,
                                    spv.id_karyawan as id_supervisi,
                                    spv.nik as nik_supervisi,
                                    spv.nama_lengkap as nama_supervisi")
                            ->join("master_cabang","master_cabang.id_cabang = dk.id_cabang","LEFT")
                            ->join("master_departemen","master_departemen.id_departemen = dk.id_departemen","LEFT")
                            ->join("master_jabatan","master_jabatan.id_jabatan = dk.id_jabatan","LEFT")
                            ->join("master_provinsi","master_provinsi.id_prov = dk.id_prov","LEFT")
                            ->join("data_karyawan AS spv","spv.id_karyawan = dk.supervisi","LEFT")
                            ->join("master_kota","master_kota.id_kota = dk.id_kab","LEFT")
                            ->join("master_kecamatan","master_kecamatan.id_kecamatan = dk.id_kec","LEFT")
                            ->join("master_kelurahan","master_kelurahan.id_kelurahan = dk.id_kel","LEFT")
                            ->where("md5(dk.id_karyawan)",$id)
                            ->from("data_karyawan AS dk")
                            ->get()
                            ->row();
        return $select;
    }

    public function delete($id_karyawan=null){

        $this->db->trans_start();

        # DELETE DATA KARYAWAN
        $this->db->where(array('id_karyawan'=>$id_karyawan));
        $this->db->where('id_company',$this->id_company);
        $this->db->delete($this->table);

        # DELETE DATA KELUARGA
        $this->db->where(array('id_karyawan'=>$id_karyawan));
        $this->db->where('id_company',$this->id_company);
        $this->db->delete("data_keluarga");

        # DELETE DATA RIWAYAT PENDIDIKAN
        $this->db->where(array('id_karyawan'=>$id_karyawan));
        $this->db->where('id_company',$this->id_company);
        $this->db->delete("riwayat_pendidikan");

        # DELETE DATA PENGALAMAN KERJA
        $this->db->where(array('id_karyawan'=>$id_karyawan));
        $this->db->where('id_company',$this->id_company);
        $this->db->delete("riwayat_pekerjaan");

        # DELETE KARIR PEKERJAAN
        $this->db->where(array('id_karyawan'=>$id_karyawan));
        $this->db->where('id_company',$this->id_company);
        $this->db->delete("karir_pekerjaan");

        # DELETE DATA SERTIFIKAT
        $this->db->where(array('id_karyawan'=>$id_karyawan));
        $this->db->where('id_company',$this->id_company);
        $this->db->delete("data_sertifikat");

        # DELETE DATA USERS
        $this->db->where(array('id_karyawan'=>$id_karyawan));
        $this->db->where('id_company',$this->id_company);
        $this->db->delete("users");

        # DELETE DATA KELENGKAPAN FILE
        $this->db->where(array('id_karyawan'=>$id_karyawan));
        $this->db->where('id_company',$this->id_company);
        $this->db->delete("data_kelengkapan_file");

        // JIKA PAKET PAYROLL AKTIF
        // DELETE MASTER GAPOK
        $this->db->where(array('id_karyawan'=>$id_karyawan));
        $this->db->where('id_company',$this->id_company);
        $this->db->delete("payroll_gapok_m");

        // DELETE MASTER GAPOK LOG
        $this->db->where(array('id_karyawan'=>$id_karyawan));
        $this->db->where('id_company',$this->id_company);
        $this->db->delete("payroll_gapok_log");

        // DELETE MASTER TUNJ TETAP
        $this->db->where(array('id_karyawan'=>$id_karyawan));
        $this->db->where('id_company',$this->id_company);
        $this->db->delete("payroll_tunj_tetap_m");

        // DELETE MASTER POT TETAP
        $this->db->where(array('id_karyawan'=>$id_karyawan));
        $this->db->where('id_company',$this->id_company);
        $this->db->delete("payroll_pot_tetap_m");

        $this->db->trans_complete(); # Completing transaction


        if($this->db->trans_status() === FALSE) {
            # Something went wrong.
            $this->db->trans_rollback();
            return __response_delete(false);
        }else{
            $this->db->trans_commit();
            $delete = $this->fungsi->delete_directory("karyawan",$id_karyawan);
            return __response_delete(true);
        }

        return  __response_delete($delete);
    }

    public function getDataById_numrows(){
        $select = $this->db->select("dk.id_karyawan,
                                    master_jabatan.level_user")
                            ->join("master_cabang","master_cabang.id_cabang = dk.id_cabang","LEFT")
                            ->join("master_departemen","master_departemen.id_departemen = dk.id_departemen","LEFT")
                            ->join("master_jabatan","master_jabatan.id_jabatan = dk.id_jabatan","LEFT")
                            ->join("master_provinsi","master_provinsi.id_prov = dk.id_prov","LEFT")
                            ->join("data_karyawan AS spv","spv.id_karyawan = dk.supervisi","LEFT")
                            ->join("master_kota","master_kota.id_kota = dk.id_kab","LEFT")
                            ->join("master_kecamatan","master_kecamatan.id_kecamatan = dk.id_kec","LEFT")
                            ->join("master_kelurahan","master_kelurahan.id_kelurahan = dk.id_kel","LEFT")
                            ->where("dk.id_company",$this->id_company)
                            ->where("master_jabatan.level_user",1)
                            ->from("data_karyawan AS dk")
                            ->get()
                            ->num_rows();
        return $select;
    }

    public function getDataByIFnc_numrows(){
        $select = $this->db->select("dk.id_karyawan,
                                    master_jabatan.level_user")
                            ->join("master_cabang","master_cabang.id_cabang = dk.id_cabang","LEFT")
                            ->join("master_departemen","master_departemen.id_departemen = dk.id_departemen","LEFT")
                            ->join("master_jabatan","master_jabatan.id_jabatan = dk.id_jabatan","LEFT")
                            ->join("master_provinsi","master_provinsi.id_prov = dk.id_prov","LEFT")
                            ->join("data_karyawan AS spv","spv.id_karyawan = dk.supervisi","LEFT")
                            ->join("master_kota","master_kota.id_kota = dk.id_kab","LEFT")
                            ->join("master_kecamatan","master_kecamatan.id_kecamatan = dk.id_kec","LEFT")
                            ->join("master_kelurahan","master_kelurahan.id_kelurahan = dk.id_kel","LEFT")
                            ->where("dk.id_company",$this->id_company)
                            ->where("master_jabatan.level_user",6)
                            ->from("data_karyawan AS dk")
                            ->get()
                            ->num_rows();
        return $select;
    }

}