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

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

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

    function count_filtered($periode=0, $id_karyawan=0, $kode_cabang=0, $kode_departemen=0){
        $this->_get_datatables_query($periode, $id_karyawan, $kode_cabang, $kode_departemen);
        $query = $this->db->get();
        return $query->num_rows();
    }

    public function count_all($periode=0, $id_karyawan=0, $kode_cabang=0, $kode_departemen=0){
        $this->_get_datatables_query($periode, $id_karyawan, $kode_cabang, $kode_departemen);
        return $this->db->count_all_results();
    }
    private function _get_datatables_query($periode=0, $id_karyawan=0, $kode_cabang=0, $kode_departemen=0){
        $this->db->select("*")->from($this->table);
        $this->db->where("id_company", $this->id_company);
        $this->db->where("periode", $periode);

        if(in_array($this->session->userdata("level_user"),['1'])){
            $this->db->where("publish", "1");
        }

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

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

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

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

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

        $column_order = array('',
                            'nik',
                            'kode_cabang',
                            'kode_departemen',
                            'kode_jabatan',
                            'gaji_pokok',
                            'ttl_tunjangan',
                            'ttl_tbp',
                            'ttl_penerimaan',
                            'ttl_potongan',
                            'gaji_diterima');

        if(isset($_POST['order'])){
            $this->db->order_by($column_order[$_POST['order']['0']['column']], $_POST['order']['0']['dir']);
        }
        else{
            $order_by = array("kode_cabang"=>"ASC","kode_departemen","kode_jabatan","nik");
            $this->db->order_by(key($order_by), $order_by[key($order_by)]);
        }
    }

    public function getDataById($id=null){
        $select = $this->db->query("SELECT *
                                    FROM $this->table
                                    WHERE MD5(id) = '$id'
                                    AND id_company = '".$_SESSION["id_company"]."'")->row();
        return $select;
    }

    public function save($data,$id = null){
        if($id != null){
            $this->db->where(array('md5(id)'=>$id, 'id_company'=>$this->id_company));
            $update = $this->db->update($this->table,$data);
            return  __response_update($update);
        }else{
            $nextId = $this->fungsi->getNextId($this->table,'id',$this->id_company,4);
            $id = array('id' => $nextId);
            $save = $this->db->insert($this->table, array_merge($data,$id));

            return __response_save($save);
        }
    }

    public function delete($id=null){
        $this->db->where(array('md5(id)'=>$id));
        $delete =$this->db->delete('informasi_gaji');
        return  __response_delete($delete);
    }

    public function dropdown_karyawan($key = null){
        $select = "SELECT id_karyawan,nik, nama_karyawan
                    FROM (SELECT id_karyawan, nik, nama_lengkap AS nama_karyawan
                        FROM data_karyawan
                        WHERE id_company = ?
                        AND (nama_lengkap LIKE ? OR nik LIKE ?)
                        UNION ALL
                        SELECT id_karyawan,nik, nama_karyawan
                        FROM informasi_gaji
                        WHERE id_company = ?
                        AND (nama_karyawan LIKE ? OR nik LIKE ?)
                    GROUP BY CONCAT(id_karyawan,' - ',nama_karyawan)
                        ) AS a
                    GROUP BY nik
                    ORDER BY nik ASC";
        $query = $this->db->query($select,array($this->id_company, '%'.$key.'%', '%'.$key.'%', $this->id_company, '%'.$key.'%', '%'.$key.'%'));
        return $query->result();
    }

    public function dropdown_cabang($id = null, $search = null){
        $result = $this->db->query("SELECT CONCAT(kode_cabang,' - ',nama_cabang) AS cabang, kode_cabang
                                FROM (SELECT kode AS kode_cabang, nama AS nama_cabang
                                    FROM master_cabang
                                    WHERE id_company = '".$this->id_company."'
                                    AND (nama LIKE ? OR kode LIKE ?)
                                    UNION ALL
                                    SELECT kode_cabang,nama_cabang
                                    FROM informasi_gaji WHERE id_company = '".$this->id_company."' AND (nama_cabang LIKE ? OR kode_cabang LIKE ?)
                                    GROUP BY CONCAT(kode_cabang,' - ',nama_cabang)
                                    ) AS a
                                GROUP BY kode_cabang
                                ORDER BY kode_cabang ASC",['%'.$search.'%', '%'.$search.'%','%'.$search.'%', '%'.$search.'%'])->result();
        $json = [];
        foreach ($result as $rows) {
            $json[] = ['id'=>$rows->kode_cabang, 'text'=>$rows->cabang];
        }
        echo json_encode($json);
    }

    public function dropdown_departemen($kode_cabang = null, $search = null){
        $result = $this->db->query("SELECT kode_departemen, nama_departemen
                                    FROM (SELECT master_departemen.kode AS kode_departemen,
                                                master_departemen.nama AS nama_departemen
                                            FROM data_karyawan
                                            JOIN master_departemen ON master_departemen.id_departemen = data_karyawan.id_departemen
                                            JOIN master_cabang ON master_cabang.id_cabang = data_karyawan.id_cabang
                                            WHERE master_cabang.kode = ?
                                            AND (master_departemen.kode LIKE ? OR master_departemen.nama LIKE ?)
                                            AND data_karyawan.id_company = '".$this->id_company."'
                                            UNION ALL
                                            SELECT  kode_departemen, nama_departemen
                                            FROM informasi_gaji
                                            WHERE kode_cabang = ?
                                            AND (kode_departemen LIKE ? OR nama_departemen LIKE ?)
                                            AND id_company = '".$this->id_company."'
                                            GROUP BY CONCAT(kode_departemen,' - ',nama_departemen)
                                        ) AS a
                                    GROUP BY kode_departemen
                                    ORDER BY kode_departemen ASC",[$kode_cabang, '%'.$search.'%', '%'.$search.'%', $kode_cabang, '%'.$search.'%', '%'.$search.'%'])->result();
            $json = [];
        foreach ($result as $rows) {
            $json[] = ['id'=>$rows->kode_departemen, 'text'=>$rows->kode_departemen." - ".$rows->nama_departemen];
        }
        echo json_encode($json);
    }

}