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

    class Template_payroll extends MY_Controller {
        public function __construct(){
            parent::__construct();
            $this->load->library(array('PHPExcel/Excel', 'PHPExcel/PHPExcel'));

            $this->load->model('payroll/gaji/Hitung_gaji_m');
        }

        public function index(){
        }

        public function _hitung_gaji($bulan="-", $periode_awal="-", $periode_akhir="-", $libur_reguler="0", $libur_nasional="0", $id_cabang="all", $id_departemen="all"){

            $bulan              = date_format(date_create($bulan."-01"),"Y-m");
            $periode_mulai      = date_format(date_create($periode_awal),"Y-m-d");
            $periode_selesai    = date_format(date_create($periode_akhir),"Y-m-d");

            $ttl_libur_reguler	= $libur_reguler;
            $ttl_libur_nasional	= $libur_nasional;
            $id_cabang 			= $id_cabang;
            $id_departemen 		= $id_departemen;

            $ttl_hari_kerja 	= $this->_ttl_hari_kerja($periode_mulai, $periode_selesai);

            $ttl_hari_kerja 	= $ttl_hari_kerja - $ttl_libur_reguler;

            // GET GAJI POKOK

            // GET UANG MAKAN
            $data_uang_makan = $this->Hitung_gaji_m->uang_makan($periode_mulai,$periode_selesai);

            // GET UANG LEMBUR
            $data_uang_lembur = $this->Hitung_gaji_m->uang_lembur($periode_mulai,$periode_selesai);

            // GET TUNJANGAN JABATAN
            $data_tunj_jabatan = $this->Hitung_gaji_m->tunj_jabatan();



            // GET INSENTIVE
            $data_insentive = $this->Hitung_gaji_m->insentive($bulan);

            // GET PENDAPATAN LAIN
            $data_pend_lain = $this->Hitung_gaji_m->pend_lain($bulan);

            // GET BONUS ONTIME
            // GET KOMISI LIBUR

            // HITUNG TUNJ BPJS KES && HITUNG TUNJ BPJS TK
            // HITUNG IURAN BPJS KES & HITUNG IURAN BPJS TK
            // TUNJ BPJS KES = 0.004;
            // TUNJ BPJS TK = 0.0424;
            // IURAN BPJS KES = 0.001;
            // IURAN BPJS TK = 0.002;

            // UMR, BONUS ONTIME, BONUS LIBUR, BONUS CUTI
            $p_umum = $this->Hitung_gaji_m->p_umum();

            ##// HITUNG GAJI KOTOR

            // GET PINJAMAN
            $data_pinjaman = $this->Hitung_gaji_m->pinjaman();

            // GET ANGSURAN PINJAMAN
            $data_angsuran = $this->Hitung_gaji_m->angsuran($bulan);

            ##// GET SISA PINJAMAN



            // GET GANTI KONS
            $data_ganti_kons = $this->Hitung_gaji_m->ganti_kons($bulan);

            // GET SELISIH SO
            $data_selisih_so = $this->Hitung_gaji_m->selisih_so($bulan);

            // GET POTONGAN LAIN
            $data_pot_lain = $this->Hitung_gaji_m->pot_lain($bulan);

            // HITUNG GAJI DITERIMA

            // HITUNG BPJS DIBAYAR PERUSAHAAN

            // HITUNG GAJI + BPJS DIBAYAR PERUSAHAAN

            $data_absensi = $this->Hitung_gaji_m->data_absensi($id_cabang, $id_departemen, $id_karyawan="all", $periode_mulai, $periode_selesai);
            // echo $this->db->last_query();
            // die();

            $data_karyawan = $this->Hitung_gaji_m->data_karyawan($id_cabang, $id_departemen);

            $data_gaji_karyawan = [];
            foreach($data_karyawan as $row){

                $join_date 	= $row->join_date;
                $masa_kerja = $this->_masaKerja($join_date, $periode_selesai);

                if($row->level_jabatan == "D221117000"){ // KSPV
                    $gapok = 3250000;
                }else if($row->level_jabatan == "D221117001"){ // SPV 1
                    $gapok = 3250000;
                }else if($row->level_jabatan == "D221117002"){ // SPV 2
                    $gapok = 2250000;
                }else if($masa_kerja <= 7){
                    $gapok = 600000;
                }else if($masa_kerja > 7 && $masa_kerja <= 12){
                    $gapok = 800000;
                }else if($masa_kerja > 12 && $masa_kerja <= 24){
                    $gapok = 950000;
                }else if($masa_kerja > 24 && $masa_kerja <= 36){
                    $gapok = 1100000;
                }else if($masa_kerja > 36 && $masa_kerja <= 48){
                    $gapok = 1200000;
                }else if($masa_kerja > 48){
                    $gapok = 1300000;
                }else{
                    $gapok = 0;
                }

                $umr 				= $p_umum[$row->id_kota]['umr'];
                $ttl_uang_makan		= $data_uang_makan[$row->id_karyawan]['ttl_uang_makan'];
                $ttl_lembur_rp		= $data_uang_lembur[$row->id_karyawan]['ttl_lembur_rp'];

                $tnj_jabatan		= $data_tunj_jabatan[$row->id_karyawan];
                $insentive			= $data_insentive[$row->id_karyawan];
                $pend_lain			= $data_pend_lain[$row->id_karyawan];

                $ttl_pinjaman 		= $data_pinjaman[$row->id_karyawan]['besar_pinjaman'];
                $ttl_bayar 			= $data_pinjaman[$row->id_karyawan]['ttl_bayar'];
                $pot_angsuran 		= $data_angsuran[$row->id_karyawan];

                $sisa_pinjaman		= $ttl_pinjaman - ($ttl_bayar + $pot_angsuran);

                $ganti_kons			= $data_ganti_kons[$row->id_karyawan];
                $ganti_so			= $data_selisih_so[$row->id_karyawan];
                $pot_lain			= $data_pot_lain[$row->id_karyawan];



                if($masa_kerja >= 7){
                    $tnj_bpjs_kes_ps	= 4;
                    $tnj_bpjs_kes_rp	= round(0.04 * $umr);
                    $tnj_bpjs_tk_ps		= 4.24;
                    $tnj_bpjs_tk_rp		= round(0.0424 * $umr);

                    $pot_bpjs_kes_ps 	= 1;
                    $pot_bpjs_kes_rp 	= round(0.01 * $umr);
                    $pot_bpjs_tk_ps 	= 2;
                    $pot_bpjs_tk_rp 	= round(0.02 * $umr);

                }else{
                    $tnj_bpjs_kes_ps	= 0;
                    $tnj_bpjs_kes_rp	= 0;
                    $tnj_bpjs_tk_ps		= 0;
                    $tnj_bpjs_tk_rp		= 0;

                    $pot_bpjs_kes_ps 	= 0;
                    $pot_bpjs_kes_rp 	= 0;
                    $pot_bpjs_tk_ps 	= 0;
                    $pot_bpjs_tk_rp 	= 0;
                }


                $ttl_tnj_bpjs		= $tnj_bpjs_kes_rp + $tnj_bpjs_tk_rp;
                $ttl_pot_bpjs_rp 	= $pot_bpjs_kes_rp + $pot_bpjs_tk_rp;

                $ttl_libur_reguler_karyawan	= $data_absensi[$row->id_karyawan]['ttl_reguler'];
                $ttl_libur_karyawan			= $data_absensi[$row->id_karyawan]['ttl_libur'];
                $ttl_sakit 					= $data_absensi[$row->id_karyawan]['ttl_sakit'];
                $ttl_cuti 					= $data_absensi[$row->id_karyawan]['ttl_cuti'];
                $ttl_izin 					= $data_absensi[$row->id_karyawan]['ttl_izin'];
                $ttl_alpa 					= $data_absensi[$row->id_karyawan]['ttl_alpa'];
                $ttl_pelanggaran			= $data_absensi[$row->id_karyawan]['ttl_pelanggaran'];


                if($ttl_libur_karyawan<= 5 && $ttl_pelanggaran == 0){
                    $bonus_ontime = $p_umum[$row->id_kota]['bonus_ontime'];
                }else{
                    $bonus_ontime = 0;
                }

                if($ttl_libur_reguler_karyawan == $ttl_libur_reguler){
                    if($ttl_sakit == 0 && $ttl_cuti == 0 && $ttl_izin && $ttl_alpa == 0){
                        $bonus_libur = $p_umum[$row->id_kota]['bonus_libur'];
                    }else{
                        $bonus_libur = 0;
                    }
                }else{
                    $bonus_libur = 0;
                }

                $gaji_kotor = $gapok +
                                $ttl_uang_makan +
                                $ttl_lembur_rp +
                                $tnj_jabatan +
                                $bonus_ontime +
                                $bonus_libur +
                                $insentive +
                                $pend_lain;

                $gaji_diterima = $gaji_kotor -
                                    ($pot_angsuran +
                                    $pot_bpjs_kes_rp +
                                    $pot_bpjs_tk_rp +
                                    $ganti_kons +
                                    $ganti_so +
                                    $pot_lain);

                $data_gaji_karyawan[] = [
                    "id_karyawan" 				=> $row->id_karyawan,
                    "nik" 				        => $row->nik,
                    "nm_karyawan" 				=> $row->nm_karyawan,
                    "lvl_jabatan" 				=> $row->kd_jabatan,
                    "nm_cabang" 				=> $row->nm_cabang,
                    "bulan"						=> $bulan,
                    "periode_mulai"				=> $periode_mulai,
                    "periode_selesai"			=> $periode_selesai,
                    "bank"						=> $row->nama_bank,
                    "rek_bank"					=> $row->no_rekening,
                    "masa_kerja"				=> $masa_kerja,
                    "hari_kerja"				=> $ttl_hari_kerja,
                    "ttl_hari_kerja_karyawan"	=> $data_absensi[$row->id_karyawan]['ttl_hari_kerja'],
                    "ttl_hari_libur_reguler"	=> $ttl_libur_reguler,
                    "ttl_hari_libur_nasional"	=> $ttl_libur_nasional,
                    "ttl_hari_libur_karyawan"	=> $ttl_libur_karyawan,
                    "ttl_sakit"					=> $ttl_sakit,
                    "ttl_cuti"					=> $ttl_cuti,
                    "ttl_izin"					=> $ttl_izin,
                    "ttl_alpa"					=> $ttl_alpa,
                    "umr"						=> $umr,
                    "gapok"						=> $gapok,
                    "ttl_uang_makan"			=> $ttl_uang_makan,
                    "ttl_lembur_menit"			=> $data_uang_lembur[$row->id_karyawan]['ttl_lembur_menit'],
                    "ttl_lembur_jam"			=> $data_uang_lembur[$row->id_karyawan]['ttl_lembur_jam'],
                    "ttl_lembur_rp"				=> $ttl_lembur_rp,
                    "tnj_jabatan"				=> $tnj_jabatan,
                    "bonus_ontime"				=> $bonus_ontime,
                    "bonus_libur"				=> $bonus_libur,
                    "insentive"					=> $insentive,
                    "pend_lain"					=> $pend_lain,
                    "tnj_bpjs_kes_ps"			=> $tnj_bpjs_kes_ps,
                    "tnj_bpjs_kes_rp"			=> $tnj_bpjs_kes_rp,
                    "tnj_bpjs_tk_ps"			=> $tnj_bpjs_tk_ps,
                    "tnj_bpjs_tk_rp"			=> $tnj_bpjs_tk_rp,
                    "ttl_tnj_bpjs_rp"			=> $ttl_tnj_bpjs,
                    "gaji_kotor"				=> $gaji_kotor,
                    "ttl_pinjaman"				=> $ttl_pinjaman,
                    "sisa_pinjaman"				=> $sisa_pinjaman,
                    "pot_angsuran"				=> $pot_angsuran,
                    "pot_bpjs_kes_ps"			=> $pot_bpjs_kes_ps,
                    "pot_bpjs_kes_rp"			=> $pot_bpjs_kes_rp,
                    "pot_bpjs_tk_ps"			=> $pot_bpjs_tk_ps,
                    "pot_bpjs_tk_rp"			=> $pot_bpjs_tk_rp,
                    "ttl_pot_bpjs_rp"			=> $ttl_pot_bpjs_rp,
                    "ganti_kons"				=> $ganti_kons,
                    "ganti_so"					=> $ganti_so,
                    "pot_lain"					=> $pot_lain,
                    "gaji_diterima"				=> $gaji_diterima,
                    "keterangan"				=> $keterangan,
                    "ttl_gaji_bpjs"				=> $gaji_diterima + $ttl_pot_bpjs_rp,
                    "id_company"				=> $this->id_company,
                    "user_input"				=> $this->id_karyawan
                ];
            }

            return $data_gaji_karyawan;
        }

        private function _ttl_hari_kerja($periode_mulai="", $periode_selesai=""){

            $tgl1 = new DateTime($periode_mulai);
            $tgl2 = new DateTime($periode_selesai);
            $d = $tgl2->diff($tgl1)->days + 1;

            return $d;
        }

        private function _masaKerja($join_date="", $periode_selesai=""){
            $start = new DateTime($join_date);
            $end = new DateTime($periode_selesai);
            $diff = $start->diff($end);

            $yearsInMonths = $diff->format('%r%y') * 12;
            $months = $diff->format('%r%m');
            $totalMonths = $yearsInMonths + $months;

            return $totalMonths; // 14
        }

        public function download($bulan="-", $periode_awal="-", $periode_akhir="-", $libur_reguler="0", $libur_nasional="0", $id_cabang="all", $id_departemen="all"){

            $bulan          = date_format(date_create("01-".$bulan),"Y-m");
            $periode_awal   = date_format(date_create($periode_awal),"Y-m-d");
            $periode_akhir  = date_format(date_create($periode_akhir),"Y-m-d");

            if($id_cabang == 'all'){
                $nama_cabang = "SEMUA CABANG";
            }else{
                $nama_cabang = $this->fungsi->get_nama_cab($id_cabang);
            }

            if($id_departemen == 'all'){
                $nama_departemen = "SEMUA DEPARTEMEN";
            }else{
                $nama_departemen = $this->fungsi->get_data_nama_dep($id_departemen);
            }

            // Load plugin PHPExcel nya
            include APPPATH.'third_party/PHPExcel/PHPExcel.php';

            // Panggil class PHPExcel nya
            $excel = new PHPExcel();
            // Settingan awal fil excel
            $excel->getProperties()->setCreator('Absenku')
                        ->setLastModifiedBy('Absenku')
                        ->setTitle("Template Payroll Gaji Sidepo")
                        ->setSubject("Template Payroll")
                        ->setDescription("Template Payroll Sidepo");
            // Buat sebuah variabel untuk menampung pengaturan style dari header tabel
            $style_header = array(
                'font' => array('bold' => true), // Set font nya jadi bold
                'alignment' => array(
                    'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER, // Set text jadi ditengah secara horizontal (center)
                    'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER // Set text jadi di tengah secara vertical (middle)
                ),
                'fill' => array(
                    'type' => PHPExcel_Style_Fill::FILL_SOLID,
                    'color' => array('rgb' => 'F55050')
                ),
            );
            // Buat sebuah variabel untuk menampung pengaturan style dari isi tabel
            $style_row = array(
                'alignment' => array(
                    'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER // Set text jadi di tengah secara vertical (middle)
                ),
                'borders' => array(
                    'top' => array('style'  => PHPExcel_Style_Border::BORDER_THIN), // Set border top dengan garis tipis
                    'right' => array('style'  => PHPExcel_Style_Border::BORDER_THIN),  // Set border right dengan garis tipis
                    'bottom' => array('style'  => PHPExcel_Style_Border::BORDER_THIN), // Set border bottom dengan garis tipis
                    'left' => array('style'  => PHPExcel_Style_Border::BORDER_THIN) // Set border left dengan garis tipis
                )
            );

            $style_center = array(
                'alignment' => array(
                    'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER, // Set text jadi ditengah secara horizontal (center)
                    'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER // Set text jadi di tengah secara vertical (middle)
                ),
            );

            $excel->setActiveSheetIndex(0)->setCellValue('A1', "TEMPLATE IMPORT PAYROLL SIDEPO"); // Set kolom A1 dengan tulisan "DATA SISWA"
            $excel->getActiveSheet()->mergeCells('A1:AF1'); // Set Merge Cell pada kolom A1 sampai E1
            $excel->getActiveSheet()->getStyle('A1')->getFont()->setBold(TRUE); // Set bold kolom A1
            $excel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); // Set text center untuk kolom A1
            /*$excel->getActiveSheet()->getStyle('A1')->getFont()->setSize(15);*/ // Set font size 15 untuk kolom A1

            $excel->setActiveSheetIndex(0)->setCellValue('A2', "BULAN ".$bulan." ".$tahun); // Set kolom A1 dengan tulisan "DATA SISWA"
            $excel->getActiveSheet()->mergeCells('A2:AF2'); // Set Merge Cell pada kolom A1 sampai E1
            $excel->getActiveSheet()->getStyle('A2')->getFont()->setBold(TRUE); // Set bold kolom A1
            $excel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); // Set text center untuk kolom A1

            $excel->setActiveSheetIndex(0)->setCellValue('A3', "CABANG : ".$nama_cabang); // Set kolom A1 dengan tulisan "DATA SISWA"
            $excel->getActiveSheet()->mergeCells('A3:AF3'); // Set Merge Cell pada kolom A1 sampai E1
            $excel->getActiveSheet()->getStyle('A3')->getFont()->setBold(TRUE); // Set bold kolom A1
            $excel->getActiveSheet()->getStyle('A3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); // Set text center untuk kolom A1

            $excel->setActiveSheetIndex(0)->setCellValue('A4', "DEPARTEMEN : ".$nama_departemen); // Set kolom A1 dengan tulisan "DATA SISWA"
            $excel->getActiveSheet()->mergeCells('A4:AF4'); // Set Merge Cell pada kolom A1 sampai E1
            $excel->getActiveSheet()->getStyle('A4')->getFont()->setBold(TRUE); // Set bold kolom A1
            $excel->getActiveSheet()->getStyle('A4')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); // Set text center untuk kolom A1

            $excel->getActiveSheet()->mergeCells('A5:AF5'); // Set Merge Cell pada kolom A1 sampai E1

            $excel->setActiveSheetIndex(0)->setCellValue('A6', "Catatan : tanda * wajib diisi"); // Set kolom A1 dengan tulisan "DATA SISWA"
            $excel->getActiveSheet()->mergeCells('A6:AF6'); // Set Merge Cell pada kolom A1 sampai E1
            $excel->getActiveSheet()->getStyle('A6')->getFont()->setBold(TRUE); // Set bold kolom A1

            //header
            $excel->setActiveSheetIndex(0)->setCellValue('A7', "NO");
            $excel->setActiveSheetIndex(0)->setCellValue('B7', "LOKASI");
            $excel->setActiveSheetIndex(0)->setCellValue('C7', "NAMA KARYAWAN");
            $excel->setActiveSheetIndex(0)->setCellValue('D7', "NIK");
            $excel->setActiveSheetIndex(0)->setCellValue('E7', "NOREK");
            $excel->setActiveSheetIndex(0)->setCellValue('F7', "JABATAN");
            $excel->setActiveSheetIndex(0)->setCellValue('G7', "UMR");
            $excel->setActiveSheetIndex(0)->setCellValue('H7', "BULAN");
            $excel->setActiveSheetIndex(0)->setCellValue('I7', "PERIODE GAJI");
            $excel->setActiveSheetIndex(0)->setCellValue('J7', "TTL LIBUR");
            $excel->setActiveSheetIndex(0)->setCellValue('L7', "TTL LIBUR KARYAWAN");
            $excel->setActiveSheetIndex(0)->setCellValue('M7', "MASA KERJA(BULAN)");
            $excel->setActiveSheetIndex(0)->setCellValue('N7', "HARI KERJA");
            $excel->setActiveSheetIndex(0)->setCellValue('O7', "TOTAL LEMBUR(JAM)");
            $excel->setActiveSheetIndex(0)->setCellValue('P7', "KEHADIRAN");
            $excel->setActiveSheetIndex(0)->setCellValue('T7', "PENDAPATAN");
            $excel->setActiveSheetIndex(0)->setCellValue('AB7', "T.BPJS KES(4%)");
            $excel->setActiveSheetIndex(0)->setCellValue('AC7', "T.BPJS TK(4,24%)");
            $excel->setActiveSheetIndex(0)->setCellValue('AD7', "GAJI KOTOR");
            $excel->setActiveSheetIndex(0)->setCellValue('AE7', "PINJAMAN");
            $excel->setActiveSheetIndex(0)->setCellValue('AF7', "SISA PINJAMAN");
            $excel->setActiveSheetIndex(0)->setCellValue('AG7', "POTONGAN");
            $excel->setActiveSheetIndex(0)->setCellValue('AM7', "GAJI DITERIMA");
            $excel->setActiveSheetIndex(0)->setCellValue('AN7', "BPJS DIBAYAR PERUSAHAAN");
            $excel->setActiveSheetIndex(0)->setCellValue('AO7', "GAJI+BPJS");

            // TTL LIBUR
            $excel->setActiveSheetIndex(0)->setCellValue('J8', "REGULER");
            $excel->setActiveSheetIndex(0)->setCellValue('K8', "NASIONAL");

            // KEHADIRAN
            $excel->setActiveSheetIndex(0)->setCellValue('P8', "S");
            $excel->setActiveSheetIndex(0)->setCellValue('Q8', "I");
            $excel->setActiveSheetIndex(0)->setCellValue('R8', "C");
            $excel->setActiveSheetIndex(0)->setCellValue('S8', "A");

            // PENDAPATAN
            $excel->setActiveSheetIndex(0)->setCellValue('T8', "GAPOK");
            $excel->setActiveSheetIndex(0)->setCellValue('U8', "U.MAKAN");
            $excel->setActiveSheetIndex(0)->setCellValue('V8', "U.LEMBUR");
            $excel->setActiveSheetIndex(0)->setCellValue('W8', "T.JABATAN");
            $excel->setActiveSheetIndex(0)->setCellValue('X8', "B.ONTIME");
            $excel->setActiveSheetIndex(0)->setCellValue('Y8', "K.LIBUR");
            $excel->setActiveSheetIndex(0)->setCellValue('Z8', "INSENTIVE");
            $excel->setActiveSheetIndex(0)->setCellValue('AA8', "LAIN2");

            // POTONGAN
            $excel->setActiveSheetIndex(0)->setCellValue('AG8', "ANGSURAN");
            $excel->setActiveSheetIndex(0)->setCellValue('AH8', "IURAN BPJS KES 1%");
            $excel->setActiveSheetIndex(0)->setCellValue('AI8', "IURAN BPJS TK 2%");
            $excel->setActiveSheetIndex(0)->setCellValue('AJ8', "GANTI KONS");
            $excel->setActiveSheetIndex(0)->setCellValue('AK8', "SELISIH SO");
            $excel->setActiveSheetIndex(0)->setCellValue('AL8', "LAIN2");

            //ROWSPAM
            $excel->getActiveSheet()->mergeCells('A7:A8'); // NO
            $excel->getActiveSheet()->mergeCells('B7:B8'); // LOKASI
            $excel->getActiveSheet()->mergeCells('C7:C8'); // NAMA KARYAWAN
            $excel->getActiveSheet()->mergeCells('D7:D8'); // NIK
            $excel->getActiveSheet()->mergeCells('E7:E8'); // NOREK
            $excel->getActiveSheet()->mergeCells('F7:F8'); // JABATAN
            $excel->getActiveSheet()->mergeCells('G7:G8'); // UMR
            $excel->getActiveSheet()->mergeCells('H7:H8'); // BULAN
            $excel->getActiveSheet()->mergeCells('I7:I8'); // PERIODE GAJI

            $excel->getActiveSheet()->mergeCells('L7:L8'); // TTL LIBUR KARYAWAN
            $excel->getActiveSheet()->mergeCells('M7:M8'); // MASA KERJA(BULAN)
            $excel->getActiveSheet()->mergeCells('N7:N8'); // HARI KERJA
            $excel->getActiveSheet()->mergeCells('O7:O8'); // TOTAL LEMBUR(JAM)


            $excel->getActiveSheet()->mergeCells('AB7:AB8'); // T.BPJS KES(4%)
            $excel->getActiveSheet()->mergeCells('AC7:AC8'); // T.BPJS TK(4,24%)
            $excel->getActiveSheet()->mergeCells('AD7:AD8'); // GAJI KOTOR
            $excel->getActiveSheet()->mergeCells('AE7:AE8'); // PINJAMAN
            $excel->getActiveSheet()->mergeCells('AF7:AF8'); // SISA PINJAMAN

            $excel->getActiveSheet()->mergeCells('AM7:AM8'); // GAJI DITERIMA
            $excel->getActiveSheet()->mergeCells('AN7:AN8'); // BPJS DIBAYAR PERUSAHAAN
            $excel->getActiveSheet()->mergeCells('AO7:AO8'); // GAJI+BPJS


            //COLSPAN
            $excel->getActiveSheet()->mergeCells('J7:K7'); // TTL LIBUR
            $excel->getActiveSheet()->mergeCells('P7:S7'); // KEHADIRAN
            $excel->getActiveSheet()->mergeCells('T7:AA7'); // PENDAPATAN
            $excel->getActiveSheet()->mergeCells('AG7:AL7'); // POTONGAN

            $excel->getActiveSheet()->getColumnDimension('A')->setWidth("5");
            $excel->getActiveSheet()->getColumnDimension('B')->setWidth("20");
            $excel->getActiveSheet()->getColumnDimension('C')->setWidth("33");
            $excel->getActiveSheet()->getColumnDimension('D')->setWidth("15");
            $excel->getActiveSheet()->getColumnDimension('E')->setWidth("15");
            $excel->getActiveSheet()->getColumnDimension('F')->setWidth("15");
            $excel->getActiveSheet()->getColumnDimension('G')->setWidth("15");
            $excel->getActiveSheet()->getColumnDimension('H')->setWidth("15");
            $excel->getActiveSheet()->getColumnDimension('I')->setWidth("23");
            $excel->getActiveSheet()->getColumnDimension('J')->setWidth("15");
            $excel->getActiveSheet()->getColumnDimension('K')->setWidth("15");
            $excel->getActiveSheet()->getColumnDimension('L')->setWidth("20");
            $excel->getActiveSheet()->getColumnDimension('M')->setWidth("19");
            $excel->getActiveSheet()->getColumnDimension('N')->setWidth("15");
            $excel->getActiveSheet()->getColumnDimension('O')->setWidth("19");
            $excel->getActiveSheet()->getColumnDimension('P')->setWidth("15");
            $excel->getActiveSheet()->getColumnDimension('Q')->setWidth("15");
            $excel->getActiveSheet()->getColumnDimension('R')->setWidth("15");
            $excel->getActiveSheet()->getColumnDimension('S')->setWidth("15");
            $excel->getActiveSheet()->getColumnDimension('T')->setWidth("15");
            $excel->getActiveSheet()->getColumnDimension('U')->setWidth("15");
            $excel->getActiveSheet()->getColumnDimension('V')->setWidth("15");
            $excel->getActiveSheet()->getColumnDimension('W')->setWidth("15");
            $excel->getActiveSheet()->getColumnDimension('X')->setWidth("15");
            $excel->getActiveSheet()->getColumnDimension('Y')->setWidth("15");
            $excel->getActiveSheet()->getColumnDimension('Z')->setWidth("15");
            $excel->getActiveSheet()->getColumnDimension('AA')->setWidth("15");
            $excel->getActiveSheet()->getColumnDimension('AB')->setWidth("15");
            $excel->getActiveSheet()->getColumnDimension('AC')->setWidth("15");
            $excel->getActiveSheet()->getColumnDimension('AD')->setWidth("15");
            $excel->getActiveSheet()->getColumnDimension('AE')->setWidth("15");
            $excel->getActiveSheet()->getColumnDimension('AF')->setWidth("15");
            $excel->getActiveSheet()->getColumnDimension('AG')->setWidth("15");
            $excel->getActiveSheet()->getColumnDimension('AH')->setWidth("17");
            $excel->getActiveSheet()->getColumnDimension('AI')->setWidth("17");
            $excel->getActiveSheet()->getColumnDimension('AJ')->setWidth("15");
            $excel->getActiveSheet()->getColumnDimension('AK')->setWidth("15");
            $excel->getActiveSheet()->getColumnDimension('AL')->setWidth("15");
            $excel->getActiveSheet()->getColumnDimension('AM')->setWidth("15");
            $excel->getActiveSheet()->getColumnDimension('AN')->setWidth("25");
            $excel->getActiveSheet()->getColumnDimension('AO')->setWidth("15");

            $data_payroll = $this->_hitung_gaji($bulan, $periode_awal, $periode_akhir, $libur_reguler, $libur_nasional, $id_cabang, $id_departemen);


            $no = 1; // Untuk penomoran tabel, di awal set dengan 1
            $numrow = 9; // Set baris pertama untuk isi tabel adalah baris ke 9
            $bulan          = date_format(date_create($bulan."-01"),"m-Y");
            foreach($data_payroll as $data){
                // "id_karyawan"
                // "bulan"
                // "periode_mulai"
                // "periode_selesai"
                // "bank"
                // "rek_bank"
                // "masa_kerja"
                // "hari_kerja"
                // "ttl_hari_kerja_karyawan"
                // "ttl_hari_libur_reguler"
                // "ttl_hari_libur_nasional"
                // "ttl_hari_libur_karyawan"
                // "ttl_sakit"
                // "ttl_cuti"
                // "ttl_izin"
                // "ttl_alpa"
                // "umr"
                // "gapok"
                // "ttl_uang_makan"
                // "ttl_lembur_menit"
                // "ttl_lembur_jam"
                // "ttl_lembur_rp"
                // "tnj_jabatan"
                // "bonus_ontime"
                // "bonus_libur"
                // "insentive"
                // "pend_lain"
                // "tnj_bpjs_kes_ps"
                // "tnj_bpjs_kes_rp"
                // "tnj_bpjs_tk_ps"
                // "tnj_bpjs_tk_rp"
                // "ttl_tnj_bpjs_rp"
                // "gaji_kotor"
                // "ttl_pinjaman"
                // "sisa_pinjaman"
                // "pot_angsuran"
                // "pot_bpjs_kes_ps"
                // "pot_bpjs_kes_rp"
                // "pot_bpjs_tk_ps"
                // "pot_bpjs_tk_rp"
                // "ttl_pot_bpjs_rp"
                // "ganti_kons"
                // "ganti_so"
                // "pot_lain"
                // "gaji_diterima"
                // "keterangan"
                // "ttl_gaji_bpjs"
                // "id_company"
                // "user_input"

                $excel->setActiveSheetIndex(0)->setCellValue('A'.$numrow, $no); // NO
                $excel->setActiveSheetIndex(0)->setCellValue('B'.$numrow, $data['nm_cabang']); // LOKASI
                $excel->setActiveSheetIndex(0)->setCellValue('C'.$numrow, $data['nm_karyawan']); // NAMA KARYAWAN
                $excel->setActiveSheetIndex(0)->setCellValue('D'.$numrow, $data['nik']); // NIK
                $excel->setActiveSheetIndex(0)->setCellValue('E'.$numrow, $data['rek_bank']); // NOREK
                $excel->setActiveSheetIndex(0)->setCellValue('F'.$numrow, $data['lvl_jabatan']); // JABATAN
                $excel->setActiveSheetIndex(0)->setCellValue('G'.$numrow, $data['umr']); // UMR
                $excel->setActiveSheetIndex(0)->setCellValue('H'.$numrow, $bulan); // BULAN
                $excel->setActiveSheetIndex(0)->setCellValue('I'.$numrow, date_format(date_create($data['periode_mulai']),"d-m-Y")." s/d ".date_format(date_create($data['periode_selesai']),"d-m-Y")); // PERIODE
                $excel->setActiveSheetIndex(0)->setCellValue('J'.$numrow, $data['ttl_hari_libur_reguler']); // TTL LIBUR REGULER
                $excel->setActiveSheetIndex(0)->setCellValue('K'.$numrow, $data['ttl_hari_libur_nasional']); // TTL LIBUR NASIONAL
                $excel->setActiveSheetIndex(0)->setCellValue('L'.$numrow, $data['ttl_hari_libur_karyawan']); // TTL LIBUR KARYAWAN
                $excel->setActiveSheetIndex(0)->setCellValue('M'.$numrow, $data['masa_kerja']); // MASA KERJA(BULAN)
                $excel->setActiveSheetIndex(0)->setCellValue('N'.$numrow, $data['ttl_hari_kerja_karyawan']); // HARI KERJA
                $excel->setActiveSheetIndex(0)->setCellValue('O'.$numrow, $data['ttl_lembur_jam']); // TOTAL LEMBUR(JAM)
                $excel->setActiveSheetIndex(0)->setCellValue('P'.$numrow, $data['ttl_sakit']); // S
                $excel->setActiveSheetIndex(0)->setCellValue('Q'.$numrow, $data['ttl_izin']); // I
                $excel->setActiveSheetIndex(0)->setCellValue('R'.$numrow, $data['ttl_cuti']); // C
                $excel->setActiveSheetIndex(0)->setCellValue('S'.$numrow, $data['ttl_alpa']); // A
                $excel->setActiveSheetIndex(0)->setCellValue('T'.$numrow, $data['gapok']); // GAPOK
                $excel->setActiveSheetIndex(0)->setCellValue('U'.$numrow, $data['ttl_uang_makan']); // U.MAKAN
                $excel->setActiveSheetIndex(0)->setCellValue('V'.$numrow, $data['ttl_lembur_rp']); // U.LEMBUR
                $excel->setActiveSheetIndex(0)->setCellValue('W'.$numrow, $data['tnj_jabatan']); // T.JABATAN
                $excel->setActiveSheetIndex(0)->setCellValue('X'.$numrow, $data['bonus_ontime']); // B.ONTIME
                $excel->setActiveSheetIndex(0)->setCellValue('Y'.$numrow, $data['bonus_libur']); // K.LIBUR
                $excel->setActiveSheetIndex(0)->setCellValue('Z'.$numrow, $data['insentive']); // INSENTIVE
                $excel->setActiveSheetIndex(0)->setCellValue('AA'.$numrow, $data['pend_lain']); // LAIN2
                $excel->setActiveSheetIndex(0)->setCellValue('AB'.$numrow, $data['tnj_bpjs_kes_rp']); // T.BPJS KES(4%);
                $excel->setActiveSheetIndex(0)->setCellValue('AC'.$numrow, $data['tnj_bpjs_tk_rp']); // T.BPJS TK(4,24%);
                $excel->setActiveSheetIndex(0)->setCellValue('AD'.$numrow, $data['gaji_kotor']); // GAJI KOTOR;
                $excel->setActiveSheetIndex(0)->setCellValue('AE'.$numrow, $data['ttl_pinjaman']); // PINJAMAN;
                $excel->setActiveSheetIndex(0)->setCellValue('AF'.$numrow, $data['sisa_pinjaman']); // SISA PINJAMAN;
                $excel->setActiveSheetIndex(0)->setCellValue('AG'.$numrow, $data['pot_angsuran']); // ANGSURAN
                $excel->setActiveSheetIndex(0)->setCellValue('AH'.$numrow, $data['pot_bpjs_kes_rp']); // IURAN BPJS KES 1%
                $excel->setActiveSheetIndex(0)->setCellValue('AI'.$numrow, $data['pot_bpjs_tk_rp']); // IURAN BPJS TK 2%
                $excel->setActiveSheetIndex(0)->setCellValue('AJ'.$numrow, $data['ganti_kons']); // GANTI KONS
                $excel->setActiveSheetIndex(0)->setCellValue('AK'.$numrow, $data['ganti_so']); // SELISIH SO
                $excel->setActiveSheetIndex(0)->setCellValue('AL'.$numrow, $data['pot_lain']); // LAIN2
                $excel->setActiveSheetIndex(0)->setCellValue('AM'.$numrow, $data['gaji_diterima']); // GAJI DITERIMA
                $excel->setActiveSheetIndex(0)->setCellValue('AN'.$numrow, $data['ttl_tnj_bpjs_rp']); // BPJS DIBAYAR PERUSAHAAN
                $excel->setActiveSheetIndex(0)->setCellValue('AO'.$numrow, $data['ttl_gaji_bpjs']); // GAJI+BPJS
            //     $excel->setActiveSheetIndex(0)->setCellValue('A'.$numrow, $no);
            //     $excel->setActiveSheetIndex(0)->setCellValue('B'.$numrow, $data->nik);
            //     $excel->setActiveSheetIndex(0)->setCellValue('C'.$numrow, $data->nama_lengkap);
            //     $excel->setActiveSheetIndex(0)->setCellValue('D'.$numrow, $data->kode_cabang.' - '.$data->nama_cabang);
            //     $excel->setActiveSheetIndex(0)->setCellValue('E'.$numrow, $data->kode_departemen.' - '.$data->nama_departemen);
            //     $excel->setActiveSheetIndex(0)->setCellValue('F'.$numrow, $data->kode_jabatan.' - '.$data->nama_jabatan);

            //     // Apply style row yang telah kita buat tadi ke masing-masing baris (isi tabel)
            //     $excel->getActiveSheet()->getStyle('A'.$numrow)->applyFromArray($style_row);
            //     $excel->getActiveSheet()->getStyle('B'.$numrow)->applyFromArray($style_row);
            //     $excel->getActiveSheet()->getStyle('C'.$numrow)->applyFromArray($style_row);
            //     $excel->getActiveSheet()->getStyle('D'.$numrow)->applyFromArray($style_row);
            //     $excel->getActiveSheet()->getStyle('E'.$numrow)->applyFromArray($style_row);
            //     $excel->getActiveSheet()->getStyle('F'.$numrow)->applyFromArray($style_row);

                $excel->getActiveSheet()->getProtection()->setSheet(true);
                # UNPROTECT THE CELL RANGE
                $excel->getActiveSheet()->getStyle('G'.$numrow.':AO'.$numrow)->getProtection()->setLocked(PHPExcel_Style_Protection::PROTECTION_UNPROTECTED);
                // $excel->getActiveSheet()->getStyle('AJ'.$numrow.':AL'.$numrow)->getProtection()->setLocked(PHPExcel_Style_Protection::PROTECTION_UNPROTECTED);

                // TOTAL GAJI KOTOR
                // $gaji_kotor =
                // $gapok +
                // $ttl_uang_makan +
                // $ttl_lembur_rp +
                // $tnj_jabatan +
                // $bonus_ontime +
                // $bonus_libur +
                // $insentive +
                // $pend_lain;

                $excel->getActiveSheet()->setCellValue('AD'.$numrow,'=SUM(T'.$numrow.':AA'.$numrow.')');

                //gaji diterima
                // $gaji_diterima = $gaji_kotor -
                //                     ($pot_angsuran +
                //                     $pot_bpjs_kes_rp +
                //                     $pot_bpjs_tk_rp +
                //                     $ganti_kons +
                //                     $ganti_so +
                //                     $pot_lain);
                $excel->getActiveSheet()->setCellValue('AM'.$numrow,'=AD'.$numrow.'-SUM(AG'.$numrow.':AL'.$numrow.')');

                // GAJI + BPJS
                $excel->getActiveSheet()->setCellValue('AO'.$numrow,'=AM'.$numrow.'+AN'.$numrow);




            //     $excel->setActiveSheetIndex(0)->setCellValueExplicit('AE'.$numrow, $data->id_karyawan,PHPExcel_Cell_DataType::TYPE_STRING);
            //     $excel->setActiveSheetIndex(0)->setCellValue('AF'.$numrow, $data->kode_cabang);
            //     $excel->setActiveSheetIndex(0)->setCellValue('AG'.$numrow, $data->nama_cabang);
            //     $excel->setActiveSheetIndex(0)->setCellValue('AH'.$numrow, $data->kode_departemen);
            //     $excel->setActiveSheetIndex(0)->setCellValue('AI'.$numrow, $data->nama_departemen);
            //     $excel->setActiveSheetIndex(0)->setCellValue('AJ'.$numrow, $data->kode_jabatan);
            //     $excel->setActiveSheetIndex(0)->setCellValue('AK'.$numrow, $data->nama_jabatan);

                $no++; // Tambah 1 setiap kali looping
                $numrow++; // Tambah 1 setiap kali looping
            }
            # FORMAT TEXT NIK REK
            $range = 'D9:E'.$numrow;
            $excel->getActiveSheet()
                ->getStyle($range)
                ->applyFromArray($style_center)
                ->getNumberFormat()
                // ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
                ->setFormatCode('0');


            # FORMAT TEXT UMR
            $range = 'G9:G'.$numrow;
            $excel->getActiveSheet()
                ->getStyle("G9:G".$numrow)
                ->getNumberFormat()
                ->setFormatCode("#,##");

            # FORMAT BULAN
            $range = 'H9:H'.$numrow;
            $excel->getActiveSheet()
                ->getStyle($range)
                ->applyFromArray($style_center);

            # FORMAT LIBUR REGULER S/D HARI KERJA
            $range = 'J9:N'.$numrow;
            $excel->getActiveSheet()
                ->getStyle($range)
                ->applyFromArray($style_center);

            # FORMAT KEHADIRAN
            $range = 'P9:S'.$numrow;
            $excel->getActiveSheet()
                ->getStyle($range)
                ->applyFromArray($style_center);

            # BORDER SEMUA KOLOM
            $excel->getActiveSheet()
                ->getStyle("A7:AO8")
                ->applyFromArray($style_header);

            $excel->getActiveSheet()
                ->getStyle("A7:AO".$numrow)
                ->applyFromArray(
                    array(
                        'borders' => array(
                            'allborders' => array(
                                'style' => PHPExcel_Style_Border::BORDER_THIN
                            )
                        )
                    )
                );

            $excel->getActiveSheet()
                ->getStyle("T9:AO".$numrow)
                ->getNumberFormat()
                ->setFormatCode("#,##");

            # FREEZE KOLOM
            $excel->getActiveSheet()->freezePane('D9');

            // # Text Wrap
            // $excel->getActiveSheet(0)->getStyle('U7')->getAlignment()->setWrapText(true);

            // # HIDDEN ID KARYAWAN
            // $excel->getActiveSheet()->getColumnDimension('AE')->setVisible(false);
            // $excel->getActiveSheet()->getColumnDimension('AF')->setVisible(false);
            // $excel->getActiveSheet()->getColumnDimension('AG')->setVisible(false);
            // $excel->getActiveSheet()->getColumnDimension('AH')->setVisible(false);
            // $excel->getActiveSheet()->getColumnDimension('AI')->setVisible(false);
            // $excel->getActiveSheet()->getColumnDimension('AJ')->setVisible(false);
            // $excel->getActiveSheet()->getColumnDimension('AK')->setVisible(false);

            // Set height semua kolom menjadi auto (mengikuti height isi dari kolommnya, jadi otomatis)
            $excel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(-1);
            // Set orientasi kertas jadi LANDSCAPE
            $excel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
            // Set judul file excel nya
            $excel->getActiveSheet(0)->setTitle("Template Payroll");
            $excel->setActiveSheetIndex(0);

            $filename = "TEMPLATE_PAYROLL_".str_replace(" ","_",$nama_cabang)."_".str_replace(" ","_",$nama_departemen)."_".$bulan."_".$tahun;
            // Proses file excel
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            header('Content-Disposition: attachment; filename='.$filename.'.xlsx'); // Set nama file excel nya
            header('Cache-Control: max-age=0');
            $write = PHPExcel_IOFactory::createWriter($excel, 'Excel2007');
            $write->save('php://output');
        }
    }
