<?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_atas_nama,
                            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_atas_nama,
                            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;
    }

    /**
     * save saat proses hitung gaji
     *
     * @param array $data => data yang akan di insert kedalam table payroll_gaji_trx
     * @param array $data_potongan => data potongan absensi
     * @param string $bulan => format YYYY-MM
     * @param string $id_cabang
     * @param string $id_departemen
     * @param string $id_karyawan => sudah terenkripsi md5
     * @param array $data_kehadiran => data update ttl_kehadiran dan ttl_tunjangan
     *
     * @return void
     */
    public function save($data, $data_potongan, $bulan, $id_cabang = 'all', $id_departemen = 'all', $id_karyawan = 'all', $data_kehadiran)
    {
        // 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 GAPOK TRX
        $this->db->where("periode", $bulan);
        $this->db->where("id_company", $this->id_company);
        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_gapok_trx");

        // INSERT GAPOK TRX
        $this->db->query('
		INSERT INTO
			payroll_gapok_trx (id_karyawan, periode, nominal, id_company, flag_pot_absensi)
		SELECT
			a.id_karyawan, "' . $bulan . '", a.nominal, a.id_company, a.flag_pot_absensi
		FROM
			payroll_gapok_m as a
		WHERE
			a.id_company = "' . $this->id_company . '" ' . $where_karyawan . '
        ');

        // DELETE TUNJ KEHADIRAN 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_kehadiran_trx");

        // INSERT TUNJ KEHADIRAN TRX
        $this->db->query('
		INSERT INTO
			payroll_tunj_kehadiran_trx (id_item, id_karyawan, id_cabang, id_departemen, periode, nm_tunjangan, nominal_tunjangan, id_company, flag_sakit, flag_izin, flag_cuti, flag_upah_lembur, flag_pot_absensi, flag_pph21, flag_bpjs, no_urut)
		SELECT
			a.id_item, a.id_karyawan, dk.id_cabang, dk.id_departemen, "' . $bulan . '", a.nm_tunjangan, a.nominal_tunjangan, a.id_company, a.flag_sakit, a.flag_izin, a.flag_cuti, a.flag_upah_lembur, a.flag_pot_absensi, a.flag_pph21, a.flag_bpjs, a.no_urut
		FROM
			payroll_tunj_kehadiran_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 . '
        ');

        // UPDATE TUNJ KEHADIRAN TRX
        $this->db->update_batch("payroll_tunj_kehadiran_trx", $data_kehadiran, "concat(id_company,id_karyawan,periode,id_item)");

        // 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, id_cabang, id_departemen, periode, nm_tunjangan, nominal_tunjangan, id_company, flag_upah_lembur, flag_pot_absensi, flag_pph21, flag_bpjs, no_urut)
		SELECT
			a.id_item, a.id_karyawan, dk.id_cabang, dk.id_departemen, "' . $bulan . '", a.nm_tunjangan, a.nominal_tunjangan, a.id_company, a.flag_upah_lembur, a.flag_pot_absensi, a.flag_pph21, a.flag_bpjs, 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, id_cabang, id_departemen, periode, nm_tunjangan, nominal_tunjangan, flag_pot_absensi, flag_pph21, id_company, no_urut)
		SELECT
			a.id_item, a.id_karyawan, dk.id_cabang, dk.id_departemen, "' . $bulan . '", a.nm_tunjangan, a.nominal_tunjangan, a.flag_pot_absensi, a.flag_pph21, 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, id_cabang, id_departemen, periode, nm_potongan, nominal_potongan, id_company, no_urut, flag_pph21)
		SELECT
			a.id_item, a.id_karyawan, dk.id_cabang, dk.id_departemen, "' . $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, id_cabang, id_departemen, periode, nm_potongan, nominal_potongan, id_company, no_urut)
		SELECT
			a.id_item, a.id_karyawan, dk.id_cabang, dk.id_departemen, "' . $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, id_cabang, id_departemen, ttl_gaji_terdaftar, tunj_perus_persen, tunj_perus_rp, pot_kry_persen, pot_kry_rp, id_company, user_input)
		SELECT
			a.id_karyawan, "' . $bulan . '", dk.id_cabang, dk.id_departemen, 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, id_cabang, id_departemen, 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 . '", dk.id_cabang, dk.id_departemen, 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("id_company", $this->id_company);
        $this->db->where("periode", $bulan);
        // 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
        $data_pot_arr = [];
        foreach (array_filter($data_potongan) as $potongan) {
            foreach (array_filter($potongan) as $values) {
                $data_pot_arr[] = $values;
            }
        }
        $this->db->insert_batch("payroll_pot_absensi_trx", $data_pot_arr);

        $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 gapok($id_cabang, $id_departemen, $id_karyawan)
    {
        if ($id_karyawan != "all") {
            $this->db->where("md5(dk.id_karyawan)", $id_karyawan);
        } else {
            if ($id_cabang != "all") {
                $this->db->where("dk.id_cabang", $id_cabang);
            }

            if ($id_departemen != "all") {
                $this->db->where("dk.id_departemen", $id_departemen);
            }
        }
        $this->db->select("pg.id_karyawan, pg.nominal, pg.lembur_per_jam, pg.flag_pot_absensi");
        $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,
                "flag_pot_absensi" => $row->flag_pot_absensi,
                "lembur_per_jam"   => $row->lembur_per_jam
            ];
        }
        return $data_gapok;
    }

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

            if ($id_departemen != "all") {
                $this->db->where("dk.id_departemen", $id_departemen);
            }
        }
        $this->db->select("
            ptk.id_karyawan,
            ptk.id_item,
            ptk.nm_tunjangan,
            ptk.nominal_tunjangan,
            ptk.flag_bpjs,
            ptk.flag_pph21,
            ptk.flag_pot_absensi,
            ptk.flag_upah_lembur,
            ptk.flag_cuti,
            ptk.flag_izin,
            ptk.flag_sakit
        ", FALSE);
        $this->db->join("data_karyawan as dk", "dk.id_karyawan = ptk.id_karyawan");
        $this->db->where("ptk.id_company", $this->id_company);
        $this->db->order_by("ptk.no_urut", "ASC");
        $select_tunj_tetap = $this->db->get("payroll_tunj_kehadiran_m as ptk")->result();

        $data_tunj_tetap = [];
        foreach ($select_tunj_tetap as $row) {
            $data_tunj_tetap[$row->id_karyawan][] = [
                "id_item"          => $row->id_item,
                "nm_komponen"      => $row->nm_tunjangan,
                "nominal"          => $row->nominal_tunjangan,
                "flag_upah_lembur" => $row->flag_upah_lembur,
                "flag_pot_absensi" => $row->flag_pot_absensi,
                "flag_pph21"       => $row->flag_pph21,
                "flag_cuti"        => $row->flag_cuti,
                "flag_izin"        => $row->flag_izin,
                "flag_sakit"       => $row->flag_sakit,
            ];
        }
        return $data_tunj_tetap;
    }

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

            if ($id_departemen != "all") {
                $this->db->where("dk.id_departemen", $id_departemen);
            }
        }
        $this->db->select("
            ptt.id_karyawan,
            ptt.id_item,
            ptt.nm_tunjangan,
            ptt.nominal_tunjangan,
            ptt.flag_pph21,
            ptt.flag_pot_absensi,
            ptt.flag_upah_lembur
        ", FALSE);
        $this->db->join("data_karyawan as dk", "dk.id_karyawan = ptt.id_karyawan");
        $this->db->where("ptt.id_company", $this->id_company);
        // $this->db->group_by("ptt.id_karyawan");
        $this->db->order_by("ptt.id_karyawan", "ASC");
        $select_tunj_tetap = $this->db->get("payroll_tunj_tetap_m as ptt")->result();

        $data_tunj_tetap = [];
        foreach ($select_tunj_tetap as $row) {
            $data_tunj_tetap[$row->id_karyawan][] = [
                "id_item"          => $row->id_item,
                "nm_komponen"      => $row->nm_tunjangan,
                "nominal"          => $row->nominal_tunjangan,
                "flag_pot_absensi" => $row->flag_pot_absensi,
                "flag_upah_lembur" => $row->flag_upah_lembur,
                "flag_pph21"       => $row->flag_pph21
            ];
        }
        return $data_tunj_tetap;
    }

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

            if ($id_departemen != "all") {
                $this->db->where("dk.id_departemen", $id_departemen);
            }
        }
        $this->db->select("
            pttt.id_karyawan,
            pttt.id_item,
            pttt.nm_tunjangan,
            pttt.nominal_tunjangan,
            pttt.flag_pph21,
            pttt.flag_pot_absensi
        ", FALSE);
        $this->db->join("data_karyawan as dk", "dk.id_karyawan = pttt.id_karyawan");
        $this->db->where("pttt.periode", $bulan);
        $this->db->where("pttt.id_company", $this->id_company);
        $this->db->order_by("pttt.id_karyawan", "ASC");
        $select_tunj_tdk_tetap = $this->db->get("payroll_tunj_tdk_tetap_m as pttt")->result();

        $data_tunj_tdk_tetap = [];
        foreach ($select_tunj_tdk_tetap as $row) {
            $data_tunj_tdk_tetap[$row->id_karyawan][] = [
                "id_item"          => $row->id_item,
                "nm_komponen"      => $row->nm_tunjangan,
                "nominal"          => $row->nominal_tunjangan,
                "flag_pph21"       => $row->flag_pph21,
                "flag_pot_absensi" => $row->flag_pot_absensi,
            ];
        }
        return $data_tunj_tdk_tetap;
    }

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

            if ($id_departemen != "all") {
                $this->db->where("dk.id_departemen", $id_departemen);
            }
        }
        $this->db->select("
            ppt.id_karyawan,
            ppt.id_item,
            ppt.flag_pph21,
            nominal_potongan as nominal
        ");
        $this->db->join("data_karyawan as dk", "dk.id_karyawan = ppt.id_karyawan");
        $this->db->where("ppt.id_company", $this->id_company);
        // $this->db->group_by("ppt.id_karyawan");
        $this->db->order_by("ppt.id_karyawan", "ASC");
        $select_pot_tetap = $this->db->get("payroll_pot_tetap_m as ppt")->result();

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

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

            if ($id_departemen != "all") {
                $this->db->where("dk.id_departemen", $id_departemen);
            }
        }
        $this->db->select("
            pptt.id_karyawan,
            pptt.id_item,
            pptt.flag_pph21,
            pptt.nominal_potongan as nominal
        ");
        $this->db->join("data_karyawan as dk", "dk.id_karyawan = pptt.id_karyawan");
        $this->db->where("pptt.periode", $bulan);
        $this->db->where("pptt.id_company", $this->id_company);
        // $this->db->group_by("pptt.id_karyawan");
        $this->db->order_by("pptt.id_karyawan", "ASC");
        $select_pot_tdk_tetap = $this->db->get("payroll_pot_tdk_tetap_m as pptt")->result();

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

        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()
    {
        $flag_bpjs = $this->db->select('flag')
            ->where('id_company', $this->id_company)
            ->get('payroll_bpjs_kes_comp_p')
            ->row()->flag;
        // jika bpjs kesehatan aktif
        if ($flag_bpjs == '1') {
            $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,
                (pot_jht_kry_rp + pot_jp_kry_rp) as 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->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,
                dk.bank_atas_nama,
                dk.bank_rek,
                dk.bank_nama,
                dk.status,
                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 cuti,
                0 AS izin,
                0 AS sakit,
                0 AS tap,
                0 AS terlambat,
                0 AS ttl_terlambat,
                0 AS pc,
                0 AS ttl_pc,
                dk.no_ktp,
                dk.alamat_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,
                dk.bank_atas_nama,
                dk.bank_rek,
                dk.bank_nama,
                dk.status,
                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.jenis_absen = 'cuti', 1, 0)) AS cuti,
                SUM(IF(ra.jenis_absen = 'izin', 1, 0)) AS izin,
                SUM(IF(ra.jenis_absen = 'sakit', 1, 0)) AS sakit,
                SUM(IF(ra.kode_absensi like '%TAP%', 1, 0)) AS tap,
                SUM(IF(ra.terlambat " . ($menit_terlambat == 0 ? "> 0" : ">= " . $menit_terlambat) . ", 1, 0)) AS terlambat,
                SUM(IF(ra.terlambat " . ($menit_terlambat == 0 ? "> 0" : ">= " . $menit_terlambat) . ", ra.terlambat, 0)) AS ttl_terlambat,
                SUM(IF(ra.pulang_cepat " . ($menit_pc == 0 ? "> 0" : ">= " . $menit_pc) . ", 1, 0)) AS pc,
                SUM(IF(ra.pulang_cepat " . ($menit_pc == 0 ? "> 0" : ">= " . $menit_pc) . ", ra.pulang_cepat, 0)) AS ttl_pc,
                dk.no_ktp,
                dk.alamat_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,
                bank_atas_nama,
                bank_rek,
                bank_nama,
                status,
                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(cuti) as cuti,
                SUM(izin) as izin,
                SUM(sakit) as sakit,
                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,
                alamat_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,
                dk.bank_atas_nama,
                dk.bank_rek,
                dk.bank_nama,
                dk.status,
                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 cuti,
                0 AS izin,
                0 AS sakit,
                0 AS tap,
                0 AS terlambat,
                0 AS ttl_terlambat,
                0 AS pc,
                0 AS ttl_pc,
                dk.no_ktp,
                dk.alamat_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_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,
                dk.bank_atas_nama,
                dk.bank_rek,
                dk.bank_nama,
                dk.status,
                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.jenis_absen = 'cuti', 1, 0)) AS cuti,
                SUM(IF(ra.jenis_absen = 'izin', 1, 0)) AS izin,
                SUM(IF(ra.jenis_absen = 'sakit', 1, 0)) AS sakit,
                SUM(IF(ra.kode_absensi like '%TAP%', 1, 0)) AS tap,
                SUM(IF(ra.terlambat " . ($menit_terlambat == 0 ? "> 0" : ">= " . $menit_terlambat) . ", 1, 0)) AS terlambat,
                SUM(IF(ra.terlambat " . ($menit_terlambat == 0 ? "> 0" : ">= " . $menit_terlambat) . ", ra.terlambat, 0)) AS ttl_terlambat,
                SUM(IF(ra.pulang_cepat " . ($menit_pc == 0 ? "> 0" : ">= " . $menit_pc) . ", 1, 0)) AS pc,
                SUM(IF(ra.pulang_cepat " . ($menit_pc == 0 ? "> 0" : ">= " . $menit_pc) . ", ra.pulang_cepat, 0)) AS ttl_pc,
                dk.no_ktp,
                dk.alamat_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 <", date('Y-m-d', strtotime('-1 days', strtotime($periode_selesai))));
            $this->db->group_end();
            $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_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,
                                dk.bank_atas_nama,
                                dk.bank_rek,
                                dk.bank_nama,
                                dk.status,
                                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,
                                SUM(IF(am.jenis_absen = 'cuti', 1, 0)) AS cuti,
                                SUM(IF(am.jenis_absen = 'izin', 1, 0)) AS izin,
                                SUM(IF(am.jenis_absen = 'sakit', 1, 0)) AS sakit,
                                COUNT(IF(ap.tgl_absen IS NULL,1,0)) AS tap,
                                SUM(IF(am.terlambat " . ($menit_terlambat == 0 ? "> 0" : ">= " . $menit_terlambat) . ", 1, 0)) AS terlambat,
                                SUM(IF(am.terlambat " . ($menit_terlambat == 0 ? "> 0" : ">= " . $menit_terlambat) . ", am.terlambat, 0)) AS ttl_terlambat,
                                SUM(IF(ap.pulang_cepat " . ($menit_pc == 0 ? "> 0" : ">= " . $menit_pc) . ", 1, 0)) AS pc,
                                SUM(IF(ap.pulang_cepat " . ($menit_pc == 0 ? "> 0" : ">= " . $menit_pc) . ", ap.pulang_cepat, 0)) AS ttl_pc,
                                dk.no_ktp,
                                dk.alamat_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 >=", date('Y-m-d', strtotime('-1 days', strtotime($periode_selesai))));
            $this->db->where("am.tgl_absen <=", $periode_selesai);
            $this->db->group_end();
            $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_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,
                                bank_atas_nama,
                                bank_rek,
                                bank_nama,
                                status,
                                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(cuti) as cuti,
                                SUM(izin) as izin,
                                SUM(sakit) as sakit,
                                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,
                                alamat_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)
    {

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

        // jika periode selesai != hari ini
        if ($periode_selesai != $date_now) {
            $this->db->select("
                id_karyawan,
                '" . $bulan . "' as periode,
                id_cabang,
                id_departemen,
                tgl_absen,
                jenis_absen,
                kode_absensi as kode_absen,
                terlambat,
                pulang_cepat
            ");
            $this->db->from("r_absensi");
            $this->db->where("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_karyawan != "") {
                $this->db->where("id_karyawan", $id_karyawan);
            }

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

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

        } else {
            // tabel r_absensi
            $this->db->select("
                ra.id_karyawan,
                '" . $bulan . "' as periode,
                ra.id_cabang,
                ra.id_departemen,
                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,
                dk.id_cabang,
                dk.id_departemen,
                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,
                id_cabang,
                id_departemen,
                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 (preg_match('/\(TAP\)/i', $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 ($row->kode_absen == 'A' && $row->jenis_absen == 'alpha') {
                    $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,
                "id_cabang"      => $row->id_cabang,
                "id_departemen"  => $row->id_departemen,
                "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"  => (string)$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" => (string)$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();
    }

    /**
     * Get data tunjangan tetap trx dari satu karyawan
     *
     * @param string $token => md5 dari id_karyawan dan periode (parameter untuk download slip gaji dari mobile)
     * @param string $id => md5(id_karyawan)
     * @param string $bulan => YYYY-MM
     * @return void
     */
    public function tunj_tetap_karyawan($token, $id = "", $bulan = "")
    {
        $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);
            $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();
    }

    /**
     * Get data tunjangan tidak tetap trx dari satu karyawan
     *
     * @param string $token => md5 dari id_karyawan dan periode (parameter untuk download slip gaji dari mobile)
     * @param string $id => md5(id_karyawan)
     * @param string $bulan => YYYY-MM
     * @return void
     */
    public function tunj_tdk_tetap_karyawan($token, $id = "", $bulan = "")
    {
        $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);
            $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();
    }

    /**
     * Get data tunjangan kehadiran trx dari satu karyawan
     *
     * @param string $token => md5 dari id_karyawan dan periode (parameter untuk download slip gaji dari mobile)
     * @param string $id => md5(id_karyawan)
     * @param string $bulan => YYYY-MM
     * @return void
     */
    public function tunj_kehadiran_karyawan($token, $id = "", $bulan = "")
    {
        $this->db->select("ppt.nm_tunjangan, ppt.ttl_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);
            $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_kehadiran_trx AS ppt")->result();
    }

    /**
     * Get data potongan tetap trx dari satu karyawan
     *
     * @param string $token => md5 dari id_karyawan dan periode (parameter untuk download slip gaji dari mobile)
     * @param string $id => md5(id_karyawan)
     * @param string $bulan => YYYY-MM
     * @return void
     */
    public function pot_tetap_karyawan($token, $id = "", $bulan = "")
    {
        $this->db->select("ppt.nm_potongan, ppt.nominal_potongan 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);
            $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();
    }

    /**
     * Get data potongan tidak tetap trx dari satu karyawan
     *
     * @param string $token => md5 dari id_karyawan dan periode (parameter untuk download slip gaji dari mobile)
     * @param string $id => md5(id_karyawan)
     * @param string $bulan => YYYY-MM
     * @return void
     */
    public function pot_tdk_tetap_karyawan($token, $id = "", $bulan = "")
    {
        $this->db->select("ppt.nm_potongan, ppt.nominal_potongan 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);
            $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_tdk_tetap_trx AS ppt")->result();
    }

    /**
     * get data dari table payroll_lembur_trx
     * untuk detail lembur
     *
     * @param string $id => id_karyawan & periode
     * @return array
     */
    public function get_data_lembur_trx($id): array
    {
        return $this->db->select('
                tgl_lembur,
                ttl_lembur_rounded,
                ttl_lembur_dikali,
                nominal_lembur_per_jam,
                ttl_lembur_rp
            ')
            ->from('payroll_lembur_trx')
            ->where('id_company', $this->id_company)
            ->where('concat(id_karyawan,periode)', $id)
            ->order_by('tgl_lembur', 'asc')
            ->get()
            ->result();
    }

    /**
     * get data dari table payroll_pot_absensi_trx
     * untuk detail potongan absensi
     *
     * @param string $id => id_karyawan & periode
     * @return array
     */
    public function get_data_pot_absensi_trx($id): array
    {
        return $this->db->select('
                tgl_absen,
                jenis_absen,
                kode_absen,
                t_nominal,
                t_menit_p,
                t_kelipatan_p,
                t_menit,
                t_ttl,
                pc_nominal,
                pc_menit_p,
                pc_kelipatan_p,
                pc_menit,
                pc_ttl,
                tap_nominal,
                tap_ttl,
                alpa_nominal,
                alpa_ttl,
                ttl_potongan
            ')
            ->from('payroll_pot_absensi_trx')
            ->where('id_company', $this->id_company)
            ->where('concat(id_karyawan,periode)', $id)
            ->order_by('tgl_absen', 'asc')
            ->get()
            ->result();
    }

    /**
     * get data dari table payroll_gapok_trx
     * untuk detail potongan absensi
     *
     * @param string $id => id_karyawan & periode
     * @return void
     */
    public function get_data_gapok_trx($id)
    {
        return $this->db->select('nominal')
            ->from('payroll_gapok_trx')
            ->where('id_company', $this->id_company)
            ->where('concat(id_karyawan,periode)', $id)
            ->where('flag_pot_absensi', '1')
            ->get()
            ->row()
            ->nominal;
    }

    /**
     * get data dari table payroll_lembur_trx
     * untuk detail potongan absensi
     *
     * @param string $id => id_karyawan & periode
     * @return void
     */
    public function get_data_upah_lembur_trx($id)
    {
        return $this->db->select('SUM(ttl_lembur_rp) as ttl_upah_lembur')
            ->from('payroll_lembur_trx')
            ->where('id_company', $this->id_company)
            ->where('concat(id_karyawan,periode)', $id)
            ->where('flag_pot_absensi', '1')
            ->group_by('id_karyawan')
            ->get()
            ->row()
            ->ttl_upah_lembur;
    }

    /**
     * get data dari table payroll_tunj_tetap_trx
     * untuk detail potongan absensi
     *
     * @param string $id => id_karyawan & periode
     * @return array
     */
    public function get_data_tunj_tetap_trx($id): array
    {
        return $this->db->select('
                nm_tunjangan,
                nominal_tunjangan
            ')
            ->from('payroll_tunj_tetap_trx')
            ->where('id_company', $this->id_company)
            ->where('concat(id_karyawan,periode)', $id)
            ->where('flag_pot_absensi', '1')
            ->order_by('no_urut', 'asc')
            ->get()
            ->result();
    }

    /**
     * get data dari table payroll_tunj_tetap_trx
     * untuk detail potongan absensi
     *
     * @param string $id => id_karyawan & periode
     * @return array
     */
    public function get_data_tunj_tdk_tetap_trx($id): array
    {
        return $this->db->select('
                nm_tunjangan,
                nominal_tunjangan
            ')
            ->from('payroll_tunj_tdk_tetap_trx')
            ->where('id_company', $this->id_company)
            ->where('concat(id_karyawan,periode)', $id)
            ->where('flag_pot_absensi', '1')
            ->order_by('no_urut', 'asc')
            ->get()
            ->result();
    }

    /**
     * get data dari table payroll_tunj_kehadiran_trx
     * untuk detail potongan absensi
     *
     * @param string $id => id_karyawan & periode
     * @return array
     */
    public function get_data_tunj_kehadiran_trx($id): array
    {
        return $this->db->select('
                nm_tunjangan,
                nominal_tunjangan,
                ttl_kehadiran,
                ttl_tunjangan
            ')
            ->from('payroll_tunj_kehadiran_trx')
            ->where('id_company', $this->id_company)
            ->where('concat(id_karyawan,periode)', $id)
            ->where('flag_pot_absensi', '1')
            ->order_by('no_urut', 'asc')
            ->get()
            ->result();
    }

    public function data_lembur($list_karyawan, $periode_mulai, $periode_selesai)
    {
        // get data lembur karyawan
        $dataLembur     = $this->db->select('id_karyawan, id_lembur, id_cabang, tgl_lembur, ttl_lembur')
            ->from('r_lembur')
            ->group_start()
            ->where('tgl_lembur >=', $periode_mulai)
            ->where('tgl_lembur <=', $periode_selesai)
            ->group_end()
            ->where('id_company', $this->id_company)
            ->get()
            ->result();
        $listDataLembur = [];
        foreach ($dataLembur as $value) {
            $listDataLembur[$value->id_karyawan][] = [
                'id_lembur_r' => $value->id_lembur,
                'id_karyawan' => $value->id_karyawan,
                'id_cabang'   => $value->id_cabang,
                'tgl'         => $value->tgl_lembur,
                'ttl_lembur'  => $value->ttl_lembur,
            ];
        }

        // get data holiday
        $dataHoliday     = $this->db->select('tanggal, jenis')
            ->from('holidays')
            ->group_start()
            ->where('tanggal >=', $periode_mulai)
            ->where('tanggal <=', $periode_selesai)
            ->group_end()
            ->where('id_company', $this->id_company)
            ->get()
            ->result();
        $listDataHoliday = [];
        foreach ($dataHoliday as $value) {
            $listDataHoliday[$value->tanggal] = $value->jenis;
        }

        // get data shift karyawan
        $dataShift     = $this->db->select('dsk.id_karyawan, ms.libur')
            ->from('data_shift_karyawan dsk')
            ->join('master_shift ms', 'dsk.id_master_shift = ms.id_master_shift')
            ->group_start()
            ->where('dsk.tanggal >=', $periode_mulai)
            ->where('dsk.tanggal <=', $periode_selesai)
            ->group_end()
            ->where('dsk.id_company', $this->id_company)
            ->get()
            ->result();
        $listDataShift = [];
        foreach ($dataShift as $value) {
            $listDataShift[$value->id_karyawan] = $value->libur;
        }

        // get data jam kerja
        $dataJamKerja = $this->db->select('id_cabang, hari, libur')
            ->from('jam_kerja')
            ->where('id_company', $this->id_company)
            ->get()
            ->result();
        $listJamKerja = [];
        foreach ($dataJamKerja as $value) {
            $listJamKerja[$value->id_cabang][$value->hari] = $value->libur;
        }

        // get data master lembur karyawan
        $dataMasterLembur     = $this->db->select('
                id_karyawan,
                lembur_hari_kerja,
                lembur_libur_off,
                lembur_libur_nasional,
                lembur_libur_khusus
            ')
            ->from('payroll_lembur_m')
            ->where('id_company', $this->id_company)
            ->get()
            ->result();
        $listDataMasterLembur = [];
        foreach ($dataMasterLembur as $value) {
            $listDataMasterLembur[$value->id_karyawan] = [
                'lembur_hari_kerja'     => $value->lembur_hari_kerja,
                'lembur_libur_off'      => $value->lembur_libur_off,
                'lembur_libur_nasional' => $value->lembur_libur_nasional,
                'lembur_libur_khusus'   => $value->lembur_libur_khusus
            ];
        }

        // get data master pengaturan lembur
        $dataMasterPengaturanLembur = $this->db->select('
                id_lembur_p,
                flag_rounding,
                jenis_upah_lembur,
                nominal_flat,
                divide_prorate,
                flag_pph21,
                flag_pot_absensi
            ')
            ->from('payroll_lembur_p')
            ->where('id_company', $this->id_company)
            ->get()
            ->result();
        $dataPengaturanPembulatan   = $this->db->select('
                id_lembur,
                menit_mulai,
                menit_selesai,
                menit_dihitung
            ')
            ->from('payroll_lembur_p_pembulatan')
            ->where('id_company', $this->id_company)
            ->get()
            ->result();
        $listPembulatan             = [];
        foreach ($dataPengaturanPembulatan as $value) {
            $listPembulatan[$value->id_lembur][] = [
                'menit_mulai'    => $value->menit_mulai,
                'menit_selesai'  => $value->menit_selesai,
                'menit_dihitung' => $value->menit_dihitung
            ];
        }
        $dataPengaturanPerkalian = $this->db->select('
                id_lembur,
                jam_mulai,
                jam_selesai,
                perkalian
            ')
            ->from('payroll_lembur_p_perkalian')
            ->where('id_company', $this->id_company)
            ->get()
            ->result();
        $listPerkalian           = [];
        foreach ($dataPengaturanPerkalian as $value) {
            $listPerkalian[$value->id_lembur][] = [
                'jam_mulai'   => $value->jam_mulai,
                'jam_selesai' => $value->jam_selesai,
                'perkalian'   => $value->perkalian
            ];
        }
        // merge data pengaturan lembur dengan data pembulatan dan perkalian
        $listPengaturanLembur = [];
        foreach ($dataMasterPengaturanLembur as $value) {
            $listPengaturanLembur[$value->id_lembur_p] = [
                'flag_rounding'     => $value->flag_rounding,
                'jenis_upah_lembur' => $value->jenis_upah_lembur,
                'nominal_flat'      => $value->nominal_flat,
                'divide_prorate'    => $value->divide_prorate,
                'flag_pph21'        => $value->flag_pph21,
                'flag_pot_absensi'  => $value->flag_pot_absensi,
                'pembulatan'        => $listPembulatan[$value->id_lembur_p],
                'perkalian'         => $listPerkalian[$value->id_lembur_p]
            ];
        }

        // collect data lembur per karyawan
        $result = [];
        foreach ($list_karyawan as $id) {
            if ($listDataLembur[$id]) {
                foreach ($listDataLembur[$id] as $value) {
                    if ($listDataHoliday[$value['tgl']]) {
                        if ($listDataHoliday[$value['tgl']] == 'nasional') {
                            $id_lembur = $listDataMasterLembur[$id]['lembur_libur_nasional'];
                        }
                        if ($listDataHoliday[$value['tgl']] == 'khusus') {
                            $id_lembur = $listDataMasterLembur[$id]['lembur_libur_khusus'];
                        }
                    }
                    if ($listDataShift[$id]) {
                        if ($listDataShift[$id] == '1') {
                            $id_lembur = $listDataMasterLembur[$id]['lembur_libur_off'];
                        }
                        if ($listDataShift[$id] == '0') {
                            $id_lembur = $listDataMasterLembur[$id]['lembur_hari_kerja'];
                        }
                    }
                    if ($listJamKerja[$value['id_cabang']][date_format(date_create($value['tgl']), 'N')] == '1') {
                        $id_lembur = $listDataMasterLembur[$id]['lembur_libur_off'];
                    }
                    if ($listJamKerja[$value['id_cabang']][date_format(date_create($value['tgl']), 'N')] == '0') {
                        $id_lembur = $listDataMasterLembur[$id]['lembur_hari_kerja'];
                    }

                    $result[$id][] = [
                        'id_lembur_r'   => $value['id_lembur_r'],
                        'id_karyawan'   => $value['id_karyawan'],
                        'tgl_lembur'    => $value['tgl'],
                        'ttl_lembur'    => $value['ttl_lembur'],
                        'aturan_lembur' => $listPengaturanLembur[$id_lembur]
                    ];
                }
            }
        }

        return $result;
    }

    public function ttl_lembur($id_karyawan, $periode_mulai, $periode_selesai)
    {
        // get data lembur karyawan
        $dataLembur = $this->db->select('tgl_lembur, ttl_lembur')
            ->from('r_lembur')
            ->group_start()
            ->where('tgl_lembur >=', $periode_mulai)
            ->where('tgl_lembur <=', $periode_selesai)
            ->group_end()
            ->where('id_karyawan', $id_karyawan)
            ->where("id_company", $this->id_company)
            ->get()
            ->result();
    }

    public function insert_master_lembur($data)
    {
        if (count($data) > 0) {
            // hapus data jika ada
            $id = array_column($data, 'id_lembur_trx');
            $this->db->where('id_company', $this->id_company);
            $this->db->where_in('id_lembur_trx', $id);
            $this->db->delete('payroll_lembur_trx');

            // insert data
            $this->db->insert_batch('payroll_lembur_trx', $data);
        }
    }

    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 get_ptkp_karyawan($id_cabang = 'all', $id_departemen = 'all', $id_karyawan = 'all', $periode_mulai = '', $periode_selesai = '')
    {
        $this->db->select('SUM(IF(dk.tanggungan = "", 1, 0)) as ptkp')
            ->from('data_karyawan as dk')
            ->join('master_jabatan as mj', 'mj.id_jabatan = dk.id_jabatan')
            ->join('master_cabang as mc', 'mc.id_cabang = dk.id_cabang')
            ->join('master_departemen as md', 'md.id_departemen = dk.id_departemen', 'LEFT')
            ->where('dk.id_company', $this->id_company)
            ->where_not_in('mj.level_user', ['1', '6']);

        if ($id_karyawan != 'all') $this->db->where('md5(dk.id_karyawan)', $id_karyawan);
        if ($id_cabang != 'all') $this->db->where('dk.id_cabang', $id_cabang);
        if ($id_departemen != 'all') $this->db->where('dk.id_departemen', $id_departemen);

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

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

    // 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();
    // }
}
