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

class Gaji_m extends CI_Model
{
    var $table = "payroll_gaji_trx";

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

    function count_filtered($bulan = "", $id_cabang = "all", $id_departemen = "all", $id_karyawan = "all")
    {
        if ($id_karyawan != "all") {
            $this->db->where("md5(pg.id_karyawan)", $id_karyawan);
        } else {
            if ($id_cabang != "all") {
                $this->db->where("pg.id_cabang", $id_cabang);
            }

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

        $this->db->select("pg.id_karyawan");
        $this->db->from($this->table . " AS pg");
        $this->db->where("pg.periode", $bulan);
        $this->db->where("pg.id_company", $this->id_company);
        $query = $this->db->get();
        return $query->num_rows();
    }

    public function count_all($bulan = "", $id_cabang = "all", $id_departemen = "all", $id_karyawan = "all")
    {
        if ($id_karyawan != "all") {
            $this->db->where("md5(pg.id_karyawan)", $id_karyawan);
        } else {
            if ($id_cabang != "all") {
                $this->db->where("pg.id_cabang", $id_cabang);
            }

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

        $this->db->select("pg.id_karyawan");
        $this->db->from($this->table . " AS pg");
        $this->db->where("pg.periode", $bulan);
        $this->db->where("pg.id_company", $this->id_company);
        return $this->db->count_all_results();
    }
    private function _get_datatables_query($bulan = "", $id_cabang = "all", $id_departemen = "all", $id_karyawan = "all")
    {

        if ($id_karyawan != "all") {
            $this->db->where("md5(pg.id_karyawan)", $id_karyawan);
        } else {
            if ($id_cabang != "all") {
                $this->db->where("pg.id_cabang", $id_cabang);
            }

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

        $this->db->select("
                            pg.nm_cabang,
                            pg.id_cabang,
                            pg.kd_cabang,
                            pg.id_departemen,
                            pg.nm_departemen,
                            pg.kd_departemen,
                            pg.id_jabatan,
                            pg.nm_jabatan,
                            pg.kd_jabatan,
                            pg.nik,
                            pg.nm_karyawan,
                            mc.nama as nm_company,
                            mc.alamat,
                            mc.url_logo,
                            pg.id_karyawan,
                            pg.nm_jabatan,
                            pg.periode,
                            pg.tgl_mulai,
                            pg.tgl_selesai,
                            pg.gapok,
                            pg.tunj_tetap_ttl,
                            pg.tunj_tdk_tetap_ttl,
                            pg.ttl_tunjangan,
                            pg.lembur_menit,
                            pg.lembur_per_jam,
                            pg.lembur_rp,
                            pg.pot_tetap_ttl,
                            pg.pot_tdk_tetap_ttl,
                            pg.pot_absensi_mak,
                            pg.pot_absensi_actual,
                            pg.pot_absensi_acc,
                            pg.tunj_bpjs_kes,
                            pg.pot_bpjs_kes,
                            pg.tunj_bpjs_tk,
                            pg.pot_bpjs_tk,
                            pg.ttl_potongan,
                            pg.ttl_tunj_dibiayai_perus,
                            pg.pot_pph21,
                            pg.thp,
                            pg.bank_rek,
                            pg.bank_nama,
                            pg.publish_user,
                            pg.publish_sch,
                            pg.publish_flag,
                            pg.publish_tgl
                        ");
        $this->db->from($this->table . " AS pg");
        $this->db->join('data_karyawan as dk', 'dk.id_karyawan = pg.id_karyawan');
        $this->db->join('master_company as mc', 'mc.id_company = pg.id_company');
        $this->db->join("master_jabatan as mj", "mj.id_jabatan = pg.id_jabatan");
        $this->db->where("pg.periode", $bulan);
        $this->db->where("pg.id_company", $this->id_company);
        $this->db->where_in('dk.status', ['1', '2', '3', '4']);
        $this->db->where_not_in("mj.level_user", ["1", "6"]);

        $column_search = array('');
        $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('');

        if (isset($_GET['order'])) {
            $this->db->order_by($column_order[$_GET['order']['0']['column']], $_GET['order']['0']['dir']);
        } else {
            $this->db->order_by('pg.kd_cabang ASC, pg.kd_departemen ASC, pg.nm_karyawan ASC');
            // $this->db->order_by("dk.nik ASC");
        }
    }

    public function slip($token = '')
    {
        $this->db->select("nm_cabang,
                            kd_cabang,
                            nm_departemen,
                            kd_departemen,
                            nm_jabatan,
                            kd_jabatan,
                            nik,
                            nm_karyawan,
                            mc.nama as nm_company,
                            mc.alamat,
                            mc.url_logo,
                            pg.nm_jabatan,
                            pg.periode,
                            pg.tgl_mulai,
                            pg.tgl_selesai,
                            pg.gapok,
                            pg.tunj_tetap_ttl,
                            pg.tunj_tdk_tetap_ttl,
                            pg.ttl_tunjangan,
                            pg.lembur_menit,
                            pg.lembur_per_jam,
                            pg.lembur_rp,
                            pg.pot_tetap_ttl,
                            pg.pot_tdk_tetap_ttl,
                            pg.pot_absensi_mak,
                            pg.pot_absensi_actual,
                            pg.pot_absensi_acc,
                            pg.tunj_bpjs_kes,
                            pg.pot_bpjs_kes,
                            pg.tunj_bpjs_tk,
                            pg.pot_bpjs_tk,
                            pg.ttl_potongan,
                            pg.ttl_tunj_dibiayai_perus,
                            pg.pot_pph21,
                            pg.thp,
                            pg.bank_rek,
                            pg.bank_nama,
                            pg.publish_user,
                            pg.publish_sch,
                            pg.publish_flag,
                            pg.publish_tgl
                        ");
        $this->db->from($this->table . " AS pg");
        $this->db->join('master_company as mc', 'mc.id_company = pg.id_company');
        $this->db->where('md5(concat(pg.id_karyawan,pg.periode))', $token);
        $query = $this->db->get()->row();
        return $query;
    }

    public function save($data, $data_potongan, $bulan, $id_cabang = 'all', $id_departemen = 'all', $id_karyawan = 'all')
    {
        // DELETE GAJI
        $this->db->where("periode", $bulan);
        $this->db->where("id_company", $this->id_company);
        if ($id_cabang != "all") {
            $this->db->where("id_cabang", $id_cabang);
        }
        if ($id_departemen != "all") {
            $this->db->where("id_departemen", $id_departemen);
        }
        if ($id_karyawan != "all") {
            $this->db->where("md5(id_karyawan)", $id_karyawan);
        }
        $this->db->delete("payroll_gaji_trx");

        // INSERT GAJI TRX
        $this->db->insert_batch("payroll_gaji_trx", $data);

        // DELETE TUNJ TETAP TRX
        $this->db->where("periode", $bulan);
        $this->db->where("id_company", $this->id_company);
        if ($id_cabang != "all") {
            // $this->db->where("id_cabang", $id_cabang);
            $where_cabang = ' AND mc.id_cabang = "' . $id_cabang . '"';
        }
        if ($id_departemen != "all") {
            // $this->db->where("id_departemen", $id_departemen);
            $where_departemen = ' AND md.id_departemen = "' . $id_departemen . '"';
        }
        if ($id_karyawan != "all") {
            $this->db->where("md5(id_karyawan)", $id_karyawan);
            $where_karyawan = ' AND md5(a.id_karyawan) = "' . $id_karyawan . '"';
        }
        $this->db->delete("payroll_tunj_tetap_trx");

        // INSERT TUNJ TETAP TRX
        $this->db->query('
		INSERT INTO
			payroll_tunj_tetap_trx (id_item, id_karyawan, periode, nm_tunjangan, nominal_tunjangan, id_company, no_urut)
		SELECT
			a.id_item, a.id_karyawan, "' . $bulan . '", a.nm_tunjangan, a.nominal_tunjangan, a.id_company, a.no_urut
		FROM
			payroll_tunj_tetap_m as a
            JOIN data_karyawan as dk ON dk.id_karyawan = a.id_karyawan
            JOIN master_cabang as mc ON mc.id_cabang = dk.id_cabang
            LEFT JOIN master_departemen as md ON md.id_departemen = dk.id_departemen
		WHERE
			a.id_company = "' . $this->id_company . '" ' . $where_cabang . $where_departemen . $where_karyawan . '
        ');

        // DELETE TUNJ TIDAK TETAP TRX
        $this->db->where("periode", $bulan);
        $this->db->where("id_company", $this->id_company);
        if ($id_cabang != "all") {
            // $this->db->where("id_cabang", $id_cabang);
            $where_cabang = ' AND mc.id_cabang = "' . $id_cabang . '"';
        }
        if ($id_departemen != "all") {
            // $this->db->where("id_departemen", $id_departemen);
            $where_departemen = ' AND md.id_departemen = "' . $id_departemen . '"';
        }
        if ($id_karyawan != "all") {
            $this->db->where("md5(id_karyawan)", $id_karyawan);
            $where_karyawan = ' AND md5(a.id_karyawan) = "' . $id_karyawan . '"';
        }
        $this->db->delete("payroll_tunj_tdk_tetap_trx");

        // INSERT TUNJ TIDAK TETAP TRX
        $this->db->query('
		INSERT INTO
			payroll_tunj_tdk_tetap_trx (id_item, id_karyawan, periode, nm_tunjangan, nominal_tunjangan, id_company, no_urut)
		SELECT
			a.id_item, a.id_karyawan, "' . $bulan . '", a.nm_tunjangan, a.nominal_tunjangan, a.id_company, a.no_urut
		FROM
			payroll_tunj_tdk_tetap_m as a
            JOIN data_karyawan as dk ON dk.id_karyawan = a.id_karyawan
            JOIN master_cabang as mc ON mc.id_cabang = dk.id_cabang
            LEFT JOIN master_departemen as md ON md.id_departemen = dk.id_departemen
		WHERE
			a.id_company = "' . $this->id_company . '" AND  a.periode = "' . $bulan . '" ' . $where_cabang . $where_departemen . $where_karyawan . '
        ');

        // DELETE POT TETAP TRX
        $this->db->where("periode", $bulan);
        $this->db->where("id_company", $this->id_company);
        if ($id_cabang != "all") {
            // $this->db->where("id_cabang", $id_cabang);
            $where_cabang = ' AND mc.id_cabang = "' . $id_cabang . '"';
        }
        if ($id_departemen != "all") {
            // $this->db->where("id_departemen", $id_departemen);
            $where_departemen = ' AND md.id_departemen = "' . $id_departemen . '"';
        }
        if ($id_karyawan != "all") {
            $this->db->where("md5(id_karyawan)", $id_karyawan);
            $where_karyawan = ' AND md5(a.id_karyawan) = "' . $id_karyawan . '"';
        }
        $this->db->delete("payroll_pot_tetap_trx");

        // INSERT POT TETAP TRX
        $this->db->query('
		INSERT INTO
			payroll_pot_tetap_trx (id_item, id_karyawan, periode, nm_potongan, nominal_potongan, id_company, no_urut, flag_pph21)
		SELECT
			a.id_item, a.id_karyawan, "' . $bulan . '", a.nm_potongan, a.nominal_potongan, a.id_company, a.no_urut, a.flag_pph21
		FROM
			payroll_pot_tetap_m as a
            JOIN data_karyawan as dk ON dk.id_karyawan = a.id_karyawan
            JOIN master_cabang as mc ON mc.id_cabang = dk.id_cabang
            LEFT JOIN master_departemen as md ON md.id_departemen = dk.id_departemen
		WHERE
			a.id_company = "' . $this->id_company . '" ' . $where_cabang . $where_departemen . $where_karyawan . '
		');

        // DELETE POT TIDAK TETAP TRX
        $this->db->where("periode", $bulan);
        $this->db->where("id_company", $this->id_company);
        if ($id_cabang != "all") {
            // $this->db->where("id_cabang", $id_cabang);
            $where_cabang = ' AND mc.id_cabang = "' . $id_cabang . '"';
        }
        if ($id_departemen != "all") {
            // $this->db->where("id_departemen", $id_departemen);
            $where_departemen = ' AND md.id_departemen = "' . $id_departemen . '"';
        }
        if ($id_karyawan != "all") {
            $this->db->where("md5(id_karyawan)", $id_karyawan);
            $where_karyawan = ' AND md5(a.id_karyawan) = "' . $id_karyawan . '"';
        }
        $this->db->delete("payroll_pot_tdk_tetap_trx");

        // INSERT POT TIDAK TETAP TRX
        $this->db->query('
		INSERT INTO
			payroll_pot_tdk_tetap_trx (id_item, id_karyawan, periode, nm_potongan, nominal_potongan, id_company, no_urut)
		SELECT
			a.id_item, a.id_karyawan, "' . $bulan . '", a.nm_potongan, a.nominal_potongan, a.id_company, a.no_urut
		FROM
			payroll_pot_tdk_tetap_m as a
            JOIN data_karyawan as dk ON dk.id_karyawan = a.id_karyawan
            JOIN master_cabang as mc ON mc.id_cabang = dk.id_cabang
            LEFT JOIN master_departemen as md ON md.id_departemen = dk.id_departemen
		WHERE
			a.id_company = "' . $this->id_company . '" AND  a.periode = "' . $bulan . '" ' . $where_cabang . $where_departemen . $where_karyawan . '
		');

        // DELETE BPJS KESEHATAN TRX
        $this->db->where("periode", $bulan);
        $this->db->where("id_company", $this->id_company);
        if ($id_cabang != "all") {
            // $this->db->where("id_cabang", $id_cabang);
            $where_cabang = ' AND mc.id_cabang = "' . $id_cabang . '"';
        }
        if ($id_departemen != "all") {
            // $this->db->where("id_departemen", $id_departemen);
            $where_departemen = ' AND md.id_departemen = "' . $id_departemen . '"';
        }
        if ($id_karyawan != "all") {
            $this->db->where("md5(id_karyawan)", $id_karyawan);
            $where_karyawan = ' AND md5(a.id_karyawan) = "' . $id_karyawan . '"';
        }
        $this->db->delete("payroll_bpjs_kes_trx");

        // INSERT BPJS KESEHATAN TRX
        $this->db->query('
		INSERT INTO
			payroll_bpjs_kes_trx (id_karyawan, periode, ttl_gaji_terdaftar, tunj_perus_persen, tunj_perus_rp, pot_kry_persen, pot_kry_rp, id_company, user_input)
		SELECT
			a.id_karyawan, "' . $bulan . '", a.ttl_gaji_terdaftar, a.tunj_perus_persen, a.tunj_perus_rp, a.pot_kry_persen, a.pot_kry_rp, a.id_company, a.user_input
		FROM
			payroll_bpjs_kes_m as a
            JOIN data_karyawan as dk ON dk.id_karyawan = a.id_karyawan
            JOIN master_cabang as mc ON mc.id_cabang = dk.id_cabang
            LEFT JOIN master_departemen as md ON md.id_departemen = dk.id_departemen
		WHERE
			a.id_company = "' . $this->id_company . '" ' . $where_cabang . $where_departemen . $where_karyawan . '
		');

        // DELETE BPJS TK TRX
        $this->db->where("periode", $bulan);
        $this->db->where("id_company", $this->id_company);
        if ($id_cabang != "all") {
            // $this->db->where("id_cabang", $id_cabang);
            $where_cabang = ' AND mc.id_cabang = "' . $id_cabang . '"';
        }
        if ($id_departemen != "all") {
            // $this->db->where("id_departemen", $id_departemen);
            $where_departemen = ' AND md.id_departemen = "' . $id_departemen . '"';
        }
        if ($id_karyawan != "all") {
            $this->db->where("md5(id_karyawan)", $id_karyawan);
            $where_karyawan = ' AND md5(a.id_karyawan) = "' . $id_karyawan . '"';
        }
        $this->db->delete("payroll_bpjs_tk_trx");

        // INSERT BPJS TK TRX
        $this->db->query('
		INSERT INTO
			payroll_bpjs_tk_trx (id_karyawan, periode, ttl_gaji_terdaftar, tunj_jht_perus_persen, tunj_jht_perus_rp, pot_jht_kry_persen, pot_jht_kry_rp, flag_jht, tunj_jkk_persen, tunj_jkk_rp, flag_jkk, tunj_jp_perus_persen, tunj_jp_perus_rp, pot_jp_kry_persen, pot_jp_kry_rp, flag_jp, tunj_jkm_persen, tunj_jkm_rp, flag_jkm, id_company, user_input)
		SELECT
			a.id_karyawan, "' . $bulan . '", a.ttl_gaji_terdaftar, a.tunj_jht_perus_persen, a.tunj_jht_perus_rp, a.pot_jht_kry_persen, a.pot_jht_kry_rp, a.flag_jht, a.tunj_jkk_persen, a.tunj_jkk_rp, a.flag_jkk, a.tunj_jp_perus_persen, a.tunj_jp_perus_rp, a.pot_jp_kry_persen, a.pot_jp_kry_rp, a.flag_jp, a.tunj_jkm_persen, a.tunj_jkm_rp, a.flag_jkm, a.id_company, a.user_input
		FROM
			payroll_bpjs_tk_m as a
            JOIN data_karyawan as dk ON dk.id_karyawan = a.id_karyawan
            JOIN master_cabang as mc ON mc.id_cabang = dk.id_cabang
            LEFT JOIN master_departemen as md ON md.id_departemen = dk.id_departemen
		WHERE
			a.id_company = "' . $this->id_company . '" ' . $where_cabang . $where_departemen . $where_karyawan . '
		');

        // DELETE POTONGAN ABSENSI TRX
        $this->db->where("periode", $bulan);
        $this->db->where("id_company", $this->id_company);
        // if($id_cabang != "all"){
        //     $this->db->where("id_cabang", $id_cabang);
        //     $where_cabang = ' AND id_cabang = "'.$id_cabang.'"';
        // }
        // if($id_departemen != "all"){
        //     $this->db->where("id_departemen", $id_departemen);
        //     $where_departemen = ' AND id_departemen = "'.$id_departemen.'"';
        // }
        if ($id_karyawan != "all") {
            $this->db->where("md5(id_karyawan)", $id_karyawan);
            $where_karyawan = ' AND md5(a.id_karyawan) = "' . $id_karyawan . '"';
        }
        $this->db->delete("payroll_pot_absensi_trx");

        // INSERT POTONGAN ABSENSI TRX
        foreach ($data_potongan as $data) {
            if (count($data) > 0) {
                $this->db->insert_batch("payroll_pot_absensi_trx", $data);
            }
        }

        $where_update = [
            "periode"         => $bulan,
            "id_company"    => $this->id_company
        ];

        // UPDATE POT TIDAK TETAP
        $this->db->update("payroll_pot_tdk_tetap_m", ["flag_gaji" => "0"], $where_update);
        $this->db->update("payroll_pot_tdk_tetap_m", ["flag_gaji" => "1"], $where_update);

        // UPDATE TUNJ TIDAK TETAP
        $this->db->update("payroll_tunj_tdk_tetap_m", ["flag_gaji" => "0"], $where_update);
        $this->db->update("payroll_tunj_tdk_tetap_m", ["flag_gaji" => "1"], $where_update);
    }

    // public function delete($key=null){
    //         $this->db->where(array('id_kota'=>$key, 'id_company' => $this->id_company));
    //         $delete =$this->db->delete($this->table);
    //         return __response_delete($delete);
    // }

    public function gapok()
    {
        $this->db->select("pg.id_karyawan, pg.nominal, pg.lembur_per_jam");
        $this->db->join("data_karyawan as dk", "dk.id_karyawan = pg.id_karyawan");
        $this->db->where("pg.id_company", $this->id_company);
        $select_gapok = $this->db->get("payroll_gapok_m AS pg")->result();

        $data_gapok = [];
        foreach ($select_gapok as $row) {
            $data_gapok[$row->id_karyawan] = [
                "nominal"        => $row->nominal,
                "lembur_per_jam" => $row->lembur_per_jam
            ];
        };
        return $data_gapok;
    }

    public function tunj_tetap()
    {
        $this->db->select("
            id_karyawan,
            SUM(nominal_tunjangan) as nominal,
            SUM(IF(flag_pot_absensi='1',nominal_tunjangan,0)) as nominal_maks,
            SUM(IF(flag_pph21 = '1', nominal_tunjangan, 0)) as nominal_pph21
        ", FALSE);
        $this->db->where("id_company", $this->id_company);
        $this->db->group_by("id_karyawan");
        $this->db->order_by("id_karyawan", "ASC");
        $select_tunj_tetap = $this->db->get("payroll_tunj_tetap_m")->result();

        $data_tunj_tetap = [];
        foreach ($select_tunj_tetap as $row) {
            $data_tunj_tetap[$row->id_karyawan] = [
                "nominal"       => $row->nominal,
                "nominal_pph21" => $row->nominal_pph21,
                "proteksi"      => $row->nominal_maks
            ];
        };
        return $data_tunj_tetap;
    }

    public function tunj_tdk_tetap($bulan = "")
    {
        $this->db->select("
            id_karyawan,
            SUM(nominal_tunjangan) as nominal,
            SUM(IF(flag_pot_absensi='1',nominal_tunjangan,0)) as nominal_maks,
            SUM(IF(flag_pph21 = '1', nominal_tunjangan, 0)) as nominal_pph21
        ", FALSE);
        $this->db->where("periode", $bulan);
        $this->db->where("id_company", $this->id_company);
        $this->db->group_by("id_karyawan");
        $this->db->order_by("id_karyawan", "ASC");
        $select_tunj_tdk_tetap = $this->db->get("payroll_tunj_tdk_tetap_m")->result();

        $data_tunj_tdk_tetap = [];
        foreach ($select_tunj_tdk_tetap as $row) {
            $data_tunj_tdk_tetap[$row->id_karyawan] = [
                "nominal"       => $row->nominal,
                "nominal_pph21" => $row->nominal_pph21,
                "proteksi"      => $row->nominal_maks
            ];
        };
        return $data_tunj_tdk_tetap;
    }

    public function pot_tetap()
    {
        $this->db->select("id_karyawan, SUM(nominal_potongan) as nominal");
        $this->db->where("id_company", $this->id_company);
        $this->db->group_by("id_karyawan");
        $this->db->order_by("id_karyawan", "ASC");
        $select_pot_tetap = $this->db->get("payroll_pot_tetap_m")->result();

        $data_pot_tetap = [];
        foreach ($select_pot_tetap as $row) {
            $data_pot_tetap[$row->id_karyawan] = $row->nominal;
        };
        return $data_pot_tetap;
    }

    public function pot_tdk_tetap($bulan = "")
    {
        $this->db->select("id_karyawan, SUM(nominal_potongan) as nominal");
        $this->db->where("periode", $bulan);
        $this->db->where("id_company", $this->id_company);
        $this->db->group_by("id_karyawan");
        $this->db->order_by("id_karyawan", "ASC");
        $select_pot_tdk_tetap = $this->db->get("payroll_pot_tdk_tetap_m")->result();

        $data_pot_tdk_tetap = [];
        foreach ($select_pot_tdk_tetap as $row) {
            $data_pot_tdk_tetap[$row->id_karyawan] = $row->nominal;
        };

        return $data_pot_tdk_tetap;
    }

    // potongan absensi terlambat, pc,tap, alpa
    public function pengaturan_potongan()
    {
        $this->db->select("jenis, flag_aktif, flag_persentase_gapok, persentase, nominal_potongan, menit, kelipatan");
        $this->db->where("id_company", $this->id_company);
        $this->db->where("flag_aktif", "1");
        $select_p_potongan = $this->db->get("payroll_pot_absensi_p")->result();

        $data_p_potongan = [];
        foreach ($select_p_potongan as $row) {
            $data_p_potongan[$row->jenis] = [
                "nominal_potongan"      => $row->nominal_potongan,
                "menit"                 => $row->menit,
                "kelipatan"             => $row->kelipatan,
                "flag_persentase_gapok" => $row->flag_persentase_gapok,
                "persentase"            => $row->persentase,
                "flag"                  => $row->flag_aktif,
            ];
        };

        return $data_p_potongan;
    }

    // bpjs kesehatan
    public function bpjs_kes()
    {
        $this->db->select("id_karyawan, tunj_perus_rp, pot_kry_rp");
        $this->db->where("id_company", $this->id_company);
        $select_bpjs_kes = $this->db->get("payroll_bpjs_kes_m")->result();

        $data_bpjs_kes = [];
        foreach ($select_bpjs_kes as $row) {
            $data_bpjs_kes[$row->id_karyawan] = [
                "tunjangan" => $row->tunj_perus_rp,
                "potongan"  => $row->pot_kry_rp,
            ];
        };

        return $data_bpjs_kes;
    }

    // bpjs tk
    public function bpjs_tk()
    {
        $this->db->select("
                id_karyawan,
                (tunj_jht_perus_rp+tunj_jkk_rp+tunj_jp_perus_rp+tunj_jkm_rp) as tunjangan_tk,
                ttl_pot_karyawan,
                (tunj_jkk_rp+tunj_jkm_rp) as premi_asuransi,
                (pot_jht_kry_rp+pot_jp_kry_rp) as iuran_pensiun", FALSE);
        $this->db->where("id_company", $this->id_company);
        $select_bpjs_tk = $this->db->get("payroll_bpjs_tk_m")->result();

        $data_bpjs_tk = [];
        foreach ($select_bpjs_tk as $row) {
            $data_bpjs_tk[$row->id_karyawan] = [
                "tunjangan"      => $row->tunjangan_tk,
                "potongan"       => $row->ttl_pot_karyawan,
                "premi_asuransi" => $row->premi_asuransi,
                "iuran_pensiun"  => $row->iuran_pensiun
            ];
        };

        return $data_bpjs_tk;
    }

    public function data_absensi($id_cabang = "all", $id_departemen = "all", $id_karyawan = "all", $periode_mulai = "", $periode_selesai = "", $menit_terlambat = 0, $menit_pc = 0)
    {

        $date_now = date("Y-m-d");

        // jika periode selesai != hari ini
        if ($periode_selesai != $date_now) {
            // table data_karyawan
            $this->db->select("
                dk.id_karyawan,
                dk.nik,
                dk.nama_lengkap as nm_karyawan,
                mj.id_jabatan as id_jabatan,
                mj.kode_jabatan as kd_jabatan,
                mj.nama as nm_jabatan,
                mc.id_cabang as id_cabang,
                mc.kode as kd_cabang,
                mc.nama as nm_cabang,
                md.id_departemen as id_departemen,
                md.kode as kd_departemen,
                md.nama as nm_departemen,
                0 AS reguler,
                0 AS shift,
                0 AS alpa,
                0 AS tap,
                0 AS terlambat,
                0 AS ttl_terlambat,
                0 AS pc,
                0 AS ttl_pc,
                dk.no_ktp,
                dk.tanggungan,
                dk.npwp,
                dk.join_date,
                dk.tgl_berhenti_bekerja,
                ptkp.nominal
            ");
            $this->db->from("data_karyawan as dk");
            $this->db->join("master_jabatan as mj", "mj.id_jabatan = dk.id_jabatan");
            $this->db->join("master_cabang as mc", "mc.id_cabang = dk.id_cabang");
            $this->db->join("master_departemen as md", "md.id_departemen = dk.id_departemen", "LEFT");
            $this->db->join('ptkp', 'ptkp.kode = dk.tanggungan', 'left');
            $this->db->where("dk.id_company", $this->id_company);
            $this->db->where_in('dk.status', ['1', '2', '3', '4']);
            $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);
            }

            if ($id_karyawan != "all") {
                $this->db->where("md5(dk.id_karyawan)", $id_karyawan);
            }

            $this->db->group_start();
            $this->db->where_in("dk.status", ["1", "2", "3", "4"]);
            $this->db->or_where_in("dk.status", ["5", "6", "7"]);
            $this->db->group_start();
            $this->db->where("dk.tgl_berhenti_bekerja >=", $periode_mulai);
            $this->db->where("dk.tgl_berhenti_bekerja <=", $periode_selesai);
            $this->db->group_end();
            $this->db->group_end();
            $this->db->group_by("dk.id_karyawan");
            $select_data_karyawan = $this->db->get_compiled_select();

            // table r_absensi
            $this->db->select("
                dk.id_karyawan,
                dk.nik,
                dk.nama_lengkap as nm_karyawan,
                mj.id_jabatan as id_jabatan,
                mj.kode_jabatan as kd_jabatan,
                mj.nama as nm_jabatan,
                mc.id_cabang as id_cabang,
                mc.kode as kd_cabang,
                mc.nama as nm_cabang,
                md.id_departemen as id_departemen,
                md.kode as kd_departemen,
                md.nama as nm_departemen,
                SUM(IF(ra.jenis_absen = 'reguler', 1, 0)) AS reguler,
                SUM(IF(ra.jenis_absen = 'shift', 1, 0)) AS shift,
                SUM(IF(ra.jenis_absen = 'alpha', 1, 0)) AS alpa,
                SUM(IF(ra.kode_absensi like '%TAP%', 1, 0)) AS tap,
                SUM(IF(ra.terlambat > " . $menit_terlambat . ", 1, 0)) AS terlambat,
                SUM(IF(ra.terlambat > " . $menit_terlambat . ", ra.terlambat, 0)) AS ttl_terlambat,
                SUM(IF(ra.pulang_cepat > " . $menit_pc . ", 1, 0)) AS pc,
                SUM(IF(ra.pulang_cepat > " . $menit_pc . ", ra.pulang_cepat, 0)) AS ttl_pc,
                dk.no_ktp,
                dk.tanggungan,
                dk.npwp,
                dk.join_date,
                dk.tgl_berhenti_bekerja,
                ptkp.nominal
            ");
            $this->db->from("data_karyawan as dk");
            $this->db->join("r_absensi as ra", "dk.id_karyawan = ra.id_karyawan", "LEFT");
            $this->db->join("master_jabatan as mj", "mj.id_jabatan = dk.id_jabatan");
            $this->db->join("master_cabang as mc", "mc.id_cabang = dk.id_cabang");
            $this->db->join("master_departemen as md", "md.id_departemen = dk.id_departemen", "LEFT");
            $this->db->join('ptkp', 'ptkp.kode = dk.tanggungan', 'left');
            $this->db->where("dk.id_company", $this->id_company);
            $this->db->group_start();
            $this->db->where("tgl_absen >=", $periode_mulai);
            $this->db->where("tgl_absen <=", $periode_selesai);
            $this->db->group_end();

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

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

            if ($id_karyawan != "all") {
                $this->db->where("md5(dk.id_karyawan)", $id_karyawan);
            }

            $this->db->group_start();
            $this->db->where_in("dk.status", ["1", "2", "3", "4"]);
            $this->db->or_where_in("dk.status", ["5", "6", "7"]);
            $this->db->group_start();
            $this->db->where("dk.tgl_berhenti_bekerja >=", $periode_mulai);
            $this->db->where("dk.tgl_berhenti_bekerja <=", $periode_selesai);
            $this->db->group_end();
            $this->db->group_end();
            $this->db->group_by("dk.id_karyawan");
            $select_r_absensi = $this->db->get_compiled_select();

            // gabungan tabel data_karyawan & r_absensi
            $this->db->select("
                id_karyawan,
                nik,
                nm_karyawan,
                id_jabatan,
                kd_jabatan,
                nm_jabatan,
                id_cabang,
                kd_cabang,
                nm_cabang,
                id_departemen,
                kd_departemen,
                nm_departemen,
                SUM(reguler) as reguler,
                SUM(shift) as shift,
                SUM(alpa) as alpa,
                SUM(tap) as tap,
                SUM(terlambat) as terlambat,
                SUM(ttl_terlambat) as ttl_terlambat,
                SUM(pc) as pc,
                SUM(ttl_pc) as ttl_pc,
                no_ktp,
                tanggungan,
                npwp,
                join_date,
                tgl_berhenti_bekerja,
                nominal as ptkp
            ");
            $this->db->from("(" . $select_data_karyawan . " UNION ALL " . $select_r_absensi . ") as u");
            $this->db->group_by("id_karyawan");
            $this->db->order_by("nm_karyawan", "ASC");
        } else {
            // table data_karyawan
            $this->db->select("
                dk.id_karyawan,
                dk.nik,
                dk.nama_lengkap as nm_karyawan,
                mj.id_jabatan as id_jabatan,
                mj.kode_jabatan as kd_jabatan,
                mj.nama as nm_jabatan,
                mc.id_cabang as id_cabang,
                mc.kode as kd_cabang,
                mc.nama as nm_cabang,
                md.id_departemen as id_departemen,
                md.kode as kd_departemen,
                md.nama as nm_departemen,
                0 AS reguler,
                0 AS shift,
                0 AS alpa,
                0 AS tap,
                0 AS terlambat,
                0 AS ttl_terlambat,
                0 AS pc,
                0 AS ttl_pc,
                dk.no_ktp,
                dk.tanggungan,
                dk.npwp,
                dk.join_date,
                dk.tgl_berhenti_bekerja,
                ptkp.nominal
            ");
            $this->db->from("data_karyawan as dk");
            $this->db->join("master_jabatan as mj", "mj.id_jabatan = dk.id_jabatan");
            $this->db->join("master_cabang as mc", "mc.id_cabang = dk.id_cabang");
            $this->db->join("master_departemen as md", "md.id_departemen = dk.id_departemen", "LEFT");
            $this->db->join('ptkp', 'ptkp.kode = dk.tanggungan', 'left');
            $this->db->where("dk.id_company", $this->id_company);

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

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

            if ($id_karyawan != "all") {
                $this->db->where("md5(dk.id_karyawan)", $id_karyawan);
            }

            $this->db->group_start();
            $this->db->where_in("dk.status", ["1", "2", "3", "4"]);
            $this->db->or_where_in("dk.status", ["5", "6", "7"]);
            $this->db->group_start();
            $this->db->where("dk.tgl_berhenti_bekerja >=", $periode_mulai);
            $this->db->where("dk.tgl_berhenti_bekerja <=", $periode_selesai);
            $this->db->group_end();
            $this->db->group_end();
            $this->db->group_by("dk.id_karyawan");
            $select_data_karyawan = $this->db->get_compiled_select();

            // table r_absensi
            $this->db->select("
                dk.id_karyawan,
                dk.nik,
                dk.nama_lengkap as nm_karyawan,
                mj.id_jabatan as id_jabatan,
                mj.kode_jabatan as kd_jabatan,
                mj.nama as nm_jabatan,
                mc.id_cabang as id_cabang,
                mc.kode as kd_cabang,
                mc.nama as nm_cabang,
                md.id_departemen as id_departemen,
                md.kode as kd_departemen,
                md.nama as nm_departemen,
                SUM(IF(ra.jenis_absen = 'reguler', 1, 0)) AS reguler,
                SUM(IF(ra.jenis_absen = 'shift', 1, 0)) AS shift,
                SUM(IF(ra.jenis_absen = 'alpha', 1, 0)) AS alpa,
                SUM(IF(ra.kode_absensi like '%TAP%', 1, 0)) AS tap,
                SUM(IF(ra.terlambat > " . $menit_terlambat . ", 1, 0)) AS terlambat,
                SUM(IF(ra.terlambat > " . $menit_terlambat . ", ra.terlambat, 0)) AS ttl_terlambat,
                SUM(IF(ra.pulang_cepat > " . $menit_pc . ", 1, 0)) AS pc,
                SUM(IF(ra.pulang_cepat > " . $menit_pc . ", ra.pulang_cepat, 0)) AS ttl_pc,
                dk.no_ktp,
                dk.tanggungan,
                dk.npwp,
                dk.join_date,
                dk.tgl_berhenti_bekerja,
                ptkp.nominal
            ");
            $this->db->from("data_karyawan as dk");
            $this->db->join("r_absensi as ra", "dk.id_karyawan = ra.id_karyawan", "LEFT");
            $this->db->join("master_jabatan as mj", "mj.id_jabatan = dk.id_jabatan");
            $this->db->join("master_cabang as mc", "mc.id_cabang = dk.id_cabang");
            $this->db->join("master_departemen as md", "md.id_departemen = dk.id_departemen", "LEFT");
            $this->db->join('ptkp', 'ptkp.kode = dk.tanggungan', 'left');
            $this->db->where("dk.id_company", $this->id_company);
            $this->db->group_start();
            $this->db->where("tgl_absen >=", $periode_mulai);
            $this->db->where("tgl_absen <=", $periode_selesai);
            $this->db->group_end();

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

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

            if ($id_karyawan != "all") {
                $this->db->where("md5(dk.id_karyawan)", $id_karyawan);
            }

            $this->db->group_by("dk.id_karyawan");
            $select_r_absensi = $this->db->get_compiled_select();

            // tabel absensi_masuk_pulang
            $this->db->select("am.id_karyawan,
                                dk.nik,
                                dk.nama_lengkap as nm_karyawan,
                                mj.id_jabatan as id_jabatan,
                                mj.kode_jabatan as kd_jabatan,
                                mj.nama as nm_jabatan,
                                mc.id_cabang as id_cabang,
                                mc.kode as kd_cabang,
                                mc.nama as nm_cabang,
                                md.id_departemen as id_departemen,
                                md.kode as kd_departemen,
                                md.nama as nm_departemen,
                                SUM(IF(am.jenis_absen = 'reguler', 1, 0)) AS reguler,
                                SUM(IF(am.jenis_absen = 'shift', 1, 0)) AS shift,
                                SUM(IF(am.jenis_absen = 'alpha', 1, 0)) AS alpa,
                                COUNT(IF(ap.tgl_absen IS NULL,1,0)) AS tap,
                                SUM(IF(am.terlambat > " . $menit_terlambat . ", 1, 0)) AS terlambat,
                                SUM(IF(am.terlambat > " . $menit_terlambat . ", am.terlambat, 0)) AS ttl_terlambat,
                                SUM(IF(ap.pulang_cepat > " . $menit_pc . ", 1, 0)) AS pc,
                                SUM(IF(ap.pulang_cepat > " . $menit_pc . ", ap.pulang_cepat, 0)) AS ttl_pc,
                                dk.no_ktp,
                                dk.tanggungan,
                                dk.npwp,
                                dk.join_date,
                                dk.tgl_berhenti_bekerja,
                                ptkp.nominal");
            $this->db->from("absensi_masuk as am");
            $this->db->join("data_karyawan as dk", "dk.id_karyawan = am.id_karyawan");
            $this->db->join('ptkp', 'ptkp.kode = dk.tanggungan', 'left');
            $this->db->join("absensi_pulang as ap", "ap.id_masuk = am.id_absensi_masuk", "LEFT");
            $this->db->join("master_jabatan as mj", "mj.id_jabatan = dk.id_jabatan");
            $this->db->join("master_cabang as mc", "mc.id_cabang = dk.id_cabang");
            $this->db->join("master_departemen as md", "md.id_departemen = dk.id_departemen", "LEFT");
            $this->db->where("am.id_company", $this->id_company);
            // $this->db->where("am.tgl_absen =", $date_now);
            $this->db->group_start();
            $this->db->where("am.tgl_absen >=", $periode_mulai);
            $this->db->where("am.tgl_absen <=", $periode_selesai);
            $this->db->group_end();

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

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

            if ($id_karyawan != "all") {
                $this->db->where("md5(dk.id_karyawan)", $id_karyawan);
            }
            $this->db->group_by("dk.id_karyawan");
            $select_absensi_masuk_pulang = $this->db->get_compiled_select();

            // gabungan tabel r_absensi & absensi_masuk & absensi_pulang
            $this->db->select("id_karyawan,
                                nik,
                                nm_karyawan,
                                id_jabatan,
                                kd_jabatan,
                                nm_jabatan,
                                id_cabang,
                                kd_cabang,
                                nm_cabang,
                                id_departemen,
                                kd_departemen,
                                nm_departemen,
                                SUM(reguler) as reguler,
                                SUM(shift) as shift,
                                SUM(alpa) as alpa,
                                SUM(tap) as tap,
                                SUM(terlambat) as terlambat,
                                SUM(ttl_terlambat) as ttl_terlambat,
                                SUM(pc) as pc,
                                SUM(ttl_pc) as ttl_pc,
                                no_ktp,
                                tanggungan,
                                npwp,
                                join_date,
                                tgl_berhenti_bekerja,
                                nominal as ptkp");
            $this->db->from("(" . $select_data_karyawan . " UNION ALL " . $select_r_absensi . " UNION ALL " . $select_absensi_masuk_pulang . ") as u");
            $this->db->group_by("id_karyawan");
            $this->db->order_by("nm_karyawan", "ASC");
        }

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

        return $select_absensi;
    }

    public function getPotAbsensi($id_cabang = "", $id_departemen = "", $id_karyawan = "", $bulan = "", $periode_mulai = "", $periode_selesai = "", $pengaturan_potongan)
    {

        // print_r($pengaturan_potongan);exit;
        $date_now = date("Y-m-d");

        // jika periode selesai != hari ini
        if ($periode_selesai != $date_now) {
            $this->db->select("
                ra.id_karyawan,
                '" . $bulan . "' as periode,
                ra.tgl_absen,
                ra.jenis_absen,
                ra.kode_absensi as kode_absen,
                ra.terlambat,
                ra.pulang_cepat
            ");
            $this->db->from("r_absensi as ra");
            $this->db->group_start();
            $this->db->where("tgl_absen >=", $periode_mulai);
            $this->db->where("tgl_absen <=", $periode_selesai);
            $this->db->group_end();
            $this->db->where("ra.id_company", $this->id_company);

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

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

            if ($id_karyawan != "") {
                $this->db->where("ra.id_karyawan", $id_karyawan);
            }
        } else {
            // tabel r_absensi
            $this->db->select("
                ra.id_karyawan,
                '" . $bulan . "' as periode,
                ra.tgl_absen,
                ra.jenis_absen,
                ra.kode_absensi as kode_absen,
                ra.terlambat,
                ra.pulang_cepat
            ");
            $this->db->from("r_absensi as ra");
            $this->db->group_start();
            $this->db->where("ra.tgl_absen >=", $periode_mulai);
            $this->db->where("ra.tgl_absen <=", $periode_selesai);
            $this->db->group_end();
            $this->db->where("ra.id_company", $this->id_company);

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

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

            if ($id_karyawan != "") {
                $this->db->where("ra.id_karyawan", $id_karyawan);
            }
            $select_r_absensi = $this->db->get_compiled_select();

            // tabel absensi masuk & pulang
            $this->db->select("
                am.id_karyawan,
                '" . $bulan . "' as periode,
                am.tgl_absen,
                am.jenis_absen,
                am.kode_absen,
                am.terlambat,
                ap.pulang_cepat
            ");
            $this->db->from("absensi_masuk as am");
            $this->db->join("data_karyawan as dk", "dk.id_karyawan = am.id_karyawan");
            $this->db->join("absensi_pulang as ap", "ap.id_masuk = am.id_absensi_masuk", "LEFT");
            $this->db->where("am.tgl_absen =", $date_now);
            $this->db->where("am.id_company", $this->id_company);

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

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

            if ($id_karyawan != "") {
                $this->db->where("dk.id_karyawan", $id_karyawan);
            }
            $this->db->group_by("dk.id_karyawan");
            $select_absensi_masuk_pulang = $this->db->get_compiled_select();

            // gabungan tabel r_absensi & absensi_masuk_pulang
            $this->db->select("
                id_karyawan,
                periode,
                tgl_absen,
                jenis_absen,
                kode_absen,
                terlambat,
                pulang_cepat
            ");
            $this->db->from("(" . $select_r_absensi . " UNION " . $select_absensi_masuk_pulang . ") as u");
        }

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

        $data_potongan_absensi = [];
        foreach ($data_absensi as $row) {
            // terlambat
            if ($pengaturan_potongan['terlambat']['flag'] == 1) {
                $t_nominal      = $pengaturan_potongan['terlambat']['nominal'];
                $t_menit_p      = $pengaturan_potongan['terlambat']['menit'];
                $t_kelipatan_p  = $pengaturan_potongan['terlambat']['kelipatan'];
                if ($t_kelipatan_p != 0) {
                    $t_ttl      = (floor($row->terlambat / $t_menit_p) * $t_nominal);
                } else {
                    if ($row->terlambat > $t_menit_p) {
                        $t_ttl = $t_nominal;
                    } else {
                        $t_ttl = 0;
                    }
                }
            } else {
                $t_nominal      = 0;
                $t_menit_p      = 0;
                $t_kelipatan_p  = 0;
                $t_ttl          = 0;
            }
            // pulang cepat
            if ($pengaturan_potongan['pc']['flag'] == 1) {
                $pc_nominal      = $pengaturan_potongan['pc']['nominal'];
                $pc_menit_p      = $pengaturan_potongan['pc']['menit'];
                $pc_kelipatan_p  = $pengaturan_potongan['pc']['kelipatan'];
                if ($pc_kelipatan_p != 0) {
                    $pc_ttl      = (floor($row->pulang_cepat / $pc_menit_p) * $pc_nominal);
                } else {
                    if ($row->pulang_cepat > $pc_menit_p) {
                        $pc_ttl = $pc_nominal;
                    } else {
                        $pc_ttl = 0;
                    }
                }
            } else {
                $pc_nominal      = 0;
                $pc_menit_p      = 0;
                $pc_kelipatan_p  = 0;
                $pc_ttl          = 0;
            }
            // tap
            if ($pengaturan_potongan['tap']['flag'] == 1) {
                $tap_nominal      = $pengaturan_potongan['tap']['nominal'];
                if (str_contains('(TAP)', $row->kode_absen)) {
                    $tap_ttl      = $tap_nominal;
                } else {
                    $tap_ttl      = 0;
                }
            } else {
                $tap_nominal      = 0;
                $tap_ttl          = 0;
            }
            // alpa
            if ($pengaturan_potongan['alpa']['flag'] == 1) {
                $alpa_nominal      = $pengaturan_potongan['alpa']['nominal'];
                if (preg_match('/^[A]/i', $row->kode_absen)) {
                    $alpa_ttl      = $alpa_nominal;
                } else {
                    $alpa_ttl      = 0;
                }
            } else {
                $alpa_nominal      = 0;
                $alpa_ttl          = 0;
            }
            $ttl_potongan = $t_ttl + $pc_ttl + $tap_ttl + $alpa_ttl;

            $data_potongan_absensi[] = [
                "id_karyawan"       => $row->id_karyawan,
                "periode"           => $row->periode,
                "tgl_absen"         => $row->tgl_absen,
                "jenis_absen"       => $row->jenis_absen,
                "kode_absen"        => $row->kode_absen,
                "t_nominal"         => $t_nominal,
                "t_menit_p"         => $t_menit_p,
                "t_kelipatan_p"     => $t_kelipatan_p,
                "t_menit"           => ($row->terlambat) ? $row->terlambat : '0',
                "t_ttl"             => $t_ttl,
                "pc_nominal"        => $pc_nominal,
                "pc_menit_p"        => $pc_menit_p,
                "pc_kelipatan_p"    => $pc_kelipatan_p,
                "pc_menit"          => ($row->pulang_cepat) ? $row->pulang_cepat : '0',
                "pc_ttl"            => $pc_ttl,
                "tap_nominal"       => $tap_nominal,
                "tap_ttl"           => $tap_ttl,
                "alpa_nominal"      => $alpa_nominal,
                "alpa_ttl"          => $alpa_ttl,
                "ttl_potongan"      => $ttl_potongan,
                "id_company"        => $this->id_company,
                "user_input"        => $this->nik_karyawan . ' - ' . $this->nm_karyawan,
                "tgl_input"         => date('Y-m-d H:i:s'),
            ];
        };
        return $data_potongan_absensi;
    }

    function download_excel($bulan = "", $id_cabang = "all", $id_departemen = "all", $id_karyawan = "all")
    {
        $this->_get_datatables_query($bulan, $id_cabang, $id_departemen, $id_karyawan);
        $query = $this->db->get();
        return $query->result();
    }

    public function tunjTetapKaryawan($id_karyawan = "", $bulan = "", $token = "")
    {
        $this->db->select("ppt.nm_tunjangan, ppt.nominal_tunjangan as nominal");
        $this->db->join("data_karyawan as dk", "dk.id_karyawan = ppt.id_karyawan");
        if (empty($token)) {
            $this->db->where("md5(ppt.id_karyawan)", $id_karyawan);
            $this->db->where("ppt.periode", $bulan);
            $this->db->where("ppt.id_company", $this->id_company);
        } else {
            $this->db->where("md5(concat(ppt.id_karyawan,ppt.periode))", $token);
        }
        $this->db->order_by('no_urut', 'asc');
        return $this->db->get("payroll_tunj_tetap_trx AS ppt")->result();
    }

    public function tunjTdkTetapKaryawan($id_karyawan = "", $bulan = "", $token = "")
    {
        $this->db->select("ppt.nm_tunjangan, ppt.nominal_tunjangan as nominal");
        $this->db->join("data_karyawan as dk", "dk.id_karyawan = ppt.id_karyawan");
        if (empty($token)) {
            $this->db->where("md5(ppt.id_karyawan)", $id_karyawan);
            $this->db->where("ppt.periode", $bulan);
            $this->db->where("ppt.id_company", $this->id_company);
        } else {
            $this->db->where("md5(concat(ppt.id_karyawan,ppt.periode))", $token);
        }
        $this->db->order_by('no_urut', 'asc');
        return $this->db->get("payroll_tunj_tdk_tetap_trx AS ppt")->result();
    }

    public function potTetapKaryawan($id_karyawan = "", $bulan = "", $token = "")
    {
        $this->db->select("ppt.nm_potongan, ppt.nominal_potongan as nominal");
        if (empty($token)) {
            $this->db->where("md5(ppt.id_karyawan)", $id_karyawan);
            $this->db->where("ppt.periode", $bulan);
            $this->db->where("ppt.id_company", $this->id_company);
        } else {
            $this->db->where("md5(concat(ppt.id_karyawan,ppt.periode))", $token);
        }
        $this->db->order_by('no_urut', 'asc');
        return $this->db->get("payroll_pot_tetap_trx AS ppt")->result();
    }

    public function potTdkTetapKaryawan($id_karyawan = "", $bulan = "", $token = "")
    {
        $this->db->select("ppl.nm_potongan, ppl.nominal_potongan as nominal");
        $this->db->join("data_karyawan as dk", "dk.id_karyawan = ppl.id_karyawan");
        if (empty($token)) {
            $this->db->where("md5(ppl.id_karyawan)", $id_karyawan);
            $this->db->where("ppl.periode", $bulan);
            $this->db->where("ppl.id_company", $this->id_company);
        } else {
            $this->db->where("md5(concat(ppl.id_karyawan,ppl.periode))", $token);
        }
        $this->db->order_by('no_urut', 'asc');
        return $this->db->get("payroll_pot_tdk_tetap_trx AS ppl")->result();
    }

    public function ttl_lembur($id_karyawan, $periode_mulai, $periode_selesai)
    {
        $this->db->select_sum('ttl_lembur');
        $this->db->from('r_lembur');
        $this->db->where('tgl_lembur >=', $periode_mulai);
        $this->db->where('tgl_lembur <=', $periode_selesai);
        $this->db->where('id_karyawan', $id_karyawan);
        $this->db->where("id_company", $this->id_company);
        return $this->db->get()->row()->ttl_lembur;
    }

    public function download_excel_transfer_bank($bulan = "0000-00", $id_cabang = "all", $id_departemen = "all", $id_karyawan = "all")
    {
        $this->_get_datatables_query($bulan, $id_cabang, $id_departemen, $id_karyawan);
        $query = $this->db->get();
        return $query->result();
    }

    // public function getTotalGaji($bulan = null)
    // {
    //     $this->db->select("SUM(IF(pg.id_karyawan!='',1,0)) as ttl_karyawan,
    //                     SUM(IF(pg.id_karyawan!='',pg.gaji_diterima,0)) as ttl_gaji_karyawan,
    //                     SUM(IF(dk.status IN ('1','2','3','4'),1,0)) as ttl_karyawan_aktif,
    //                     SUM(IF(dk.status IN ('1','2','3','4'),pg.gaji_diterima,0)) as ttl_gaji_karyawan_aktif,
    //                     SUM(IF(dk.status = '5',1,0)) as ttl_karyawan_resign,
    //                     SUM(IF(dk.status = '5',pg.gaji_diterima,0)) as ttl_gaji_karyawan_resign");
    //     $this->db->from($this->table.' as pg');
    //     $this->db->join('data_karyawan as dk', 'dk.id_karyawan = pg.id_karyawan');
    //     $this->db->where('pg.id_company', $this->id_company);
    //     if (!empty($bulan)) {
    //         $this->db->where('pg.periode', $bulan);
    //     }

    //     return $this->db->get()->row();
    // }
}
