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

class Laporan_pph_m extends CI_Model {
    private $table = 'payroll_pph21_trx';

    function get_datatables($bulan="0000-00", $cabang="all", $departemen="all", $karyawan="all"){
        $this->_get_datatables_query($bulan, $cabang, $departemen, $karyawan);
        if($_GET['length'] != -1){
            $this->db->limit($_GET['length'], $_GET['start']);
        }
        $query = $this->db->get();
        return $query->result();
    }

    function count_filtered($bulan="0000-00", $cabang="all", $departemen="all", $karyawan="all"){
        $this->_get_datatables_query($bulan, $cabang, $departemen, $karyawan);
        $query = $this->db->get();
        return $query->num_rows();
    }

    public function count_all($bulan="0000-00", $cabang="all", $departemen="all", $karyawan="all"){
        $this->_get_datatables_query($bulan, $cabang, $departemen, $karyawan);
        return $this->db->count_all_results();
    }

    private function _get_datatables_query($bulan="0000-00", $cabang="all", $departemen="all", $karyawan="all"){
        // $parts = explode('-', $bulan);
        // $bulan = $parts[1]. "-". $parts[0];

        $this->db->where("pph.periode",$bulan);
        $this->db->where("pph.id_company",$this->id_company);

        if($karyawan != "all"){
            $this->db->where("MD5(pph.id_karyawan)",$karyawan);
        }else{
            if($cabang != "all"){
                $this->db->where("pph.id_cabang",$cabang);
            }

            if($departemen != "all"){
                $this->db->where("pph.id_departemen",$departemen);
            }
        }

        $this->db->select("
            pph.id_karyawan,
            pph.nik,
            pph.nm_karyawan,
            pph.no_ktp,
            pph.npwp,
            pph.local_expat,
            pph.tanggungan,
            pph.mulai_kerja_bulan,
            pph.akhir_kerja_bulan,
            pph.id_jabatan,
            pph.kd_jabatan,
            pph.nm_jabatan,
            pph.id_cabang,
            pph.kd_cabang,
            pph.nm_cabang,
            pph.id_departemen,
            pph.kd_departemen,
            pph.nm_departemen,
            pph.periode,

            pph.gapok,
            pph.tunj_pph,
            pph.tunj_lain,
            pph.honorarium,
            pph.premi_asuransi,
            pph.natura,
            pph.peng_bruto_ttl,
            pph.bonus_thr,
            pph.ttl_bruto_bonus,
            pph.pot_jabatan_atas_peng_bruto,
            pph.pot_jabatan_atas_ttl_bruto_bonus,
            pph.pot_iuran_pensiun_tht,
            pph.ttl_potongan,
            pph.jml_ph_neto_disetahunkan,
            pph.ptkp,
            pph.ttl_pkp_disetahunkan,
            pph.pph21_dtp,
            pph.pph21_non_dtp,

            pph.id_company,
            pph.user_input,
            pph.tgl_input,

            dk.bank_atas_nama,
            dk.bank_rek,
            dk.bank_nama,

            gaji.thp,

            bpjs_kes.tunj_perus_rp as tunj_bpjs_kes,
            (bpjs_tk.tunj_jkk_rp + bpjs_tk.tunj_jkm_rp) as tunj_bpjs_tk,
            bpjs_kes.pot_kry_rp as pot_bpjs_kes,
            (bpjs_tk.pot_jht_kry_rp + bpjs_tk.pot_jp_kry_rp) as pot_bpjs_tk
        ");

        $this->db->from($this->table.' AS pph');
        $this->db->join('data_karyawan as dk', 'dk.id_karyawan = pph.id_karyawan');
        $this->db->join('payroll_gaji_trx as gaji', 'gaji.id_karyawan = pph.id_karyawan and gaji.periode = "'.$bulan.'"');
        $this->db->join('payroll_bpjs_kes_trx as bpjs_kes', 'bpjs_kes.id_karyawan = pph.id_karyawan and bpjs_kes.periode = "'.$bulan.'"', 'left');
        $this->db->join('payroll_bpjs_tk_trx as bpjs_tk', 'bpjs_tk.id_karyawan = pph.id_karyawan and bpjs_tk.periode = "'.$bulan.'"', 'left');

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

    function nama_karyawan($id_karyawan) {
        return $this->db->select('nama_lengkap')
            ->where('MD5(id_karyawan)', $id_karyawan)
            ->get('data_karyawan')->row()->nama_lengkap;
    }

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

    function nama_departemen($id_departemen) {
        return $this->db->select('nama' )
            ->where('id_departemen', $id_departemen)
            ->get('master_departemen')->row()->nama;
    }
}
