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

class Laporan_pph_uu24 extends MY_Controller
{

    public function __construct()
    {
        parent::__construct();
        $this->load->model('payroll/laporan_pph/Laporan_pph_uu24_m', 'model');
        $this->load->model('payroll/gaji/Komponen_m');
        $this->load->helper('payroll');
        $this->load->library(array('PHPExcel/Excel', 'PHPExcel/PHPExcel'));
    }

    public function index()
    {
        $data['judul']   = 'Laporan PPH21';
        $data['aktif']   = 'laporan_pph';
        $data['menu']    = $this->load->view('main_menu', $data, true);
        $data['content'] = $this->load->view('payroll/laporan_pph_uu24/home', $data, true);
        $this->load->view('main_template', $data, false);
    }

    public function view_data($bulan = "00-0000", $cabang = "all", $departemen = "all", $karyawan = "all")
    {
        $data['judul']         = 'Laporan PPH21';
        $data['aktif']         = 'laporan_pph';
        $data['bulan']         = $bulan;
        $data['id_cabang']     = $cabang;
        $data['id_departemen'] = $departemen;
        $data['id_karyawan']   = $karyawan;
        $this->load->view('payroll/laporan_pph_uu24/list_data', $data, false);
    }

    /**
     * mengubah angka minus menjadi kurung
     * ex. -1000 menjadi (1000)
     *
     * @param string|int $num
     * @return void
     */
    private function payroll_minus($num)
    {
        if (substr($num, 0, 1) == '-') {
            $num = substr($num, 1);
            return '(' . number_format($num, 0, ',', ',') . ')';
        }
        return number_format($num, 0, ',', ',');
    }

    public function list_data($bulan = "00-0000", $cabang = "all", $departemen = "all", $karyawan = "all")
    {
        $bulan = $this->periode($bulan);
        $list  = $this->model->get_datatables($bulan, $cabang, $departemen, $karyawan);
        $data  = array();
        $no    = $_GET['start'];
        foreach ($list as $field) {
            $no++;
            $row = array();
            $row[] = $no;
            $row[] = date('d-m-Y', strtotime($field->tgl_pemotongan));
            $row[] = $field->penerima_penghasilan;
            $row[] = $field->npwp;
            $row[] = $field->no_ktp;
            $row[] = $field->nm_karyawan;
            $row[] = $field->alamat_ktp;
            $row[] = $field->kd_objek_pajak;
            $row[] = $field->penandatangan_jenis;
            $row[] = $field->penandatangan_npwp;
            $row[] = $field->penandatangan_ktp;
            $row[] = $field->kd_ptkp;
            $row[] = $field->pegawai_harian;
            $row[] = $field->gross_up;
            $row[] = $this->payroll_minus($field->gaji_bruto);
            $row[] = $field->fasilitas;
            $row[] = $field->no_skb_dtp;
            $row[] = $this->payroll_minus($field->pph21);

            $data[] = $row;
        }

        $output = array(
            "draw"            => $_GET['draw'],
            "recordsTotal"    => $this->model->count_all($bulan, $cabang, $departemen, $karyawan),
            "recordsFiltered" => $this->model->count_filtered($bulan, $cabang, $departemen, $karyawan),
            "data"            => $data
        );

        $output = __response($this->MY_response, $output);

        echo json_encode($output);
    }

    public function download_excel($periode = "00-0000", $cabang = "all", $departemen = "all", $karyawan = "all")
    {
        $periode = $this->periode($periode);
        list($tahun, $bulan) = explode('-', $periode);

        // FILENAME
        if ($karyawan != "all") {
            $nama_karyawan = $this->model->nama_karyawan($karyawan);
            $filename      = "DATA PPH21 " . $bulan . " " . $nama_karyawan;
        } else {
            if ($departemen != "all") {
                $nama_cab = $this->model->nama_cabang($cabang);
                $nama_dep = $this->model->nama_departemen($departemen);
                $nama     = "DATA PPH21 " . $bulan . " Cabang " . $nama_cab . " Departemen " . str_replace("/", "-", $nama_dep);
                $filename = $nama;
            } else {
                if ($cabang != "all") {
                    $nama     = $this->model->nama_cabang($cabang);
                    $filename = "DATA PPH21 " . $bulan . " Cabang " . $nama . " Semua Departemen";
                } else {
                    $nama     = "DATA PPH21 " . $bulan . " Semua Cabang";
                    $filename = $nama;
                }
            }
        }

        $data["filename"] = $filename;
        $data["periode"]  = $this->fungsi->konversi_bulan($bulan) . " " . $tahun;
        $data["data"]     = $this->model->get_datatables($periode, $cabang, $departemen, $karyawan);
        $sb_view          = $this->load->view('payroll/laporan_pph_uu24/download_excel', $data, true);
        // print_r($sb_view);
        // exit;
        $column_settings = ['A' => ['width' => 10, 'align' => 'center', 'valign' => 'middle']];
        $column_nominal  = ['O', 'R'];
        for ($i = 'A'; $i < 'S'; $i++) {
            if (in_array($i, $column_nominal)) {
                $column_settings[$i] = ['rupiah' => true, 'format' => 'text', 'valign' => 'middle'];
            } else {
                $column_settings[$i] = ['align' => 'left', 'format' => 'text', 'valign' => 'middle'];
            }
        }
        $param          = [
            'download'   => true,
            'cLength'    => 17,
            'file_name'  => strtoupper(str_replace(" ", "_", $filename) . '_' . $bulan),
            'paper_size' => 'A4',
            'font-size'  => 9,
            // 'pagebreak'   => true,
            'page_margin' => ['top' => 0, 'right' => 0.75, 'left' => 0.75, 'botom' => 0],
            'orientation' => 'potrait',
            'header'      => [
                '1' => ['merge' => true, 'align' => 'center', 'weight' => 'bold'],
                '2' => ['merge' => true, 'align' => 'center', 'weight' => 'bold'],
                '3' => ['merge' => true, 'align' => 'center', 'weight' => 'bold']
            ],
            'freezecolumn' => ['A5'],
            'thead'        => ['4'],
            'thead_styles' => ['row' => 4, 'height' => 30],
            'tbody'        => [
                'start'  => '4',
                'height' => 15,
                'wrap'   => ['B', 'C'],
                'column' => $column_settings,
            ],
        ];

        $this->load->library('PHPExcel/Excel');
        $this->excel->generateExcel($sb_view, $param);
    }

    public function download_excel_djp($periode = "00-0000", $cabang = "all", $departemen = "all", $karyawan = "all")
    {
        $periode = $this->periode($periode);
        list($tahun, $bulan) = explode('-', $periode);

        // FILENAME
        if ($karyawan != "all") {
            $nama_karyawan = $this->laporan_pph->nama_karyawan($karyawan);
            $filename      = "1721 I Bulan DTP-UU HPP " . $bulan . " " . $nama_karyawan;
        } else {
            if ($departemen != "all") {
                $nama_cab = $this->laporan_pph->nama_cabang($cabang);
                $nama_dep = $this->laporan_pph->nama_departemen($departemen);
                $nama     = "1721 I Bulan DTP-UU HPP " . $bulan . " Cabang " . $nama_cab . " Departemen " . str_replace("/", "-", $nama_dep);
                $filename = $nama;
            } else {
                if ($cabang != "all") {
                    $nama     = $this->laporan_pph->nama_cabang($cabang);
                    $filename = "1721 I Bulan DTP-UU HPP " . $bulan . " Cabang " . $nama . " Semua Departemen";
                } else {
                    $nama     = "1721 I Bulan DTP-UU HPP " . $bulan . " Semua Cabang";
                    $filename = $nama;
                }
            }
        }
        // data
        $data = $this->model->get_datatables($periode, $cabang, $departemen, $karyawan);

        // 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("Laporan PPH Absenku")
            ->setSubject("Laporan PPH")
            ->setDescription("Laporan PPH Absenku Profesioanal");

        // worksheet for tab Rekap
        $this->tabRekap($excel, $tahun, $bulan, count($data));

        // Create a new worksheet for tab 21
        $this->tab21($excel, $data);

        // Create a new worksheet for tab 26
        $this->tab26($excel);

        // Create a new worksheet for tab Ref Daftar Objek Pajak
        $this->tabObjekPajak($excel);

        // Create a new worksheet for tab Ref Daftar Kode Negara
        $this->tabKodeNegara($excel);

        // Create a new worksheet for tab Ref Daftar PTKP
        $this->tabPtkp($excel);

        $excel->setActiveSheetIndex(0);
        // 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');
    }

    /**
     * worksheet for tab Rekap
     *
     * @param object $excel => library PHPExcel
     * @param string $tahun => YYYY
     * @param string $bulan => MM
     * @param int $count_data => jumlah data pph21
     * @return void
     */
    private function tabRekap($excel, $tahun, $bulan, $count_data)
    {
        $excel->setActiveSheetIndex(0);
        $sheet = $excel->getActiveSheet();
        // setup sheet
        $sheet->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $sheet->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
        $sheet->setTitle("Rekap");

        // set width
        $sheet->getColumnDimension('A')->setWidth(8);
        $sheet->getColumnDimension('B')->setWidth(15);
        $sheet->getColumnDimension('C')->setWidth(8);
        $sheet->getColumnDimension('D')->setWidth(15);
        $sheet->getColumnDimension('E')->setWidth(15);
        $sheet->getColumnDimension('F')->setWidth(8);
        $sheet->getColumnDimension('G')->setWidth(15);
        $sheet->getColumnDimension('H')->setWidth(8);

        // merge cells
        $sheet->mergeCells('B2:C2');
        $sheet->mergeCells('E2:F2');
        $sheet->mergeCells('B3:F3');
        $sheet->mergeCells('B4:F4');
        // set borders
        $sheet->getStyle('B2:G4')->applyFromArray(array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_MEDIUM))));
        // set font size
        $sheet->getStyle('B2')->getFont()->setSize(16);
        $sheet->getStyle('E2')->getFont()->setSize(16);
        $sheet->getStyle('D2')->getFont()->setSize(20);
        $sheet->getStyle('G2:G4')->getFont()->setSize(20);
        $sheet->getStyle('B3:B4')->getFont()->setSize(13);
        // def color
        $dark_grey = '808080';
        $grey = 'dddedd';
        // set styles
        $this->setCellBackgroundColor($sheet, 'A1:H1', $dark_grey);
        $this->setCellBackgroundColor($sheet, 'A5:H5', $dark_grey);
        $this->setCellBackgroundColor($sheet, 'A2:A4', $dark_grey);
        $this->setCellBackgroundColor($sheet, 'H2:H4', $dark_grey);
        $this->setCellBackgroundColor($sheet, 'B2:B4', $grey);
        $this->setCellBackgroundColor($sheet, 'E2', $grey);

        // set value
        $sheet->setCellValue('B2', 'Tahun Pajak');
        $sheet->setCellValue('B3', 'Jumlah Bukti Potong PPh Pasal 21');
        $sheet->setCellValue('B4', 'Jumlah Bukti Potong PPh Pasal 26');
        $sheet->setCellValue('D2', $tahun);
        $sheet->setCellValue('E2', 'Masa Pajak');
        $sheet->setCellValue('G2', (int)$bulan);
        $sheet->setCellValue('G3', $count_data);
        $sheet->setCellValue('G4', '0');
    }

    /**
     * worksheet for tab 21
     *
     * @param object $excel => library PHPExcel
     * @param object $data => data pph21
     * @return void
     */
    private function tab21($excel, $data)
    {
        $excel->createSheet();
        $excel->setActiveSheetIndex(1);
        $sheet = $excel->getActiveSheet();
        // setup sheet
        $sheet->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
        $sheet->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
        $sheet->setTitle("21");
        // set width
        $sheet->getColumnDimension('A')->setWidth(5);
        $sheet->getColumnDimension('B')->setWidth(15);
        $sheet->getColumnDimension('C')->setWidth(14);
        $sheet->getColumnDimension('D')->setWidth(20);
        $sheet->getColumnDimension('E')->setWidth(20);
        $sheet->getColumnDimension('F')->setWidth(20);
        $sheet->getColumnDimension('G')->setWidth(20);
        $sheet->getColumnDimension('H')->setWidth(16);
        $sheet->getColumnDimension('I')->setWidth(20);
        $sheet->getColumnDimension('J')->setWidth(23);
        $sheet->getColumnDimension('K')->setWidth(20);
        $sheet->getColumnDimension('L')->setWidth(17);
        $sheet->getColumnDimension('M')->setWidth(17);
        $sheet->getColumnDimension('N')->setWidth(17);
        $sheet->getColumnDimension('O')->setWidth(17);
        $sheet->getColumnDimension('P')->setWidth(25);
        $sheet->getColumnDimension('Q')->setWidth(35);
        // set height
        $sheet->getRowDimension(2)->setRowHeight(45.75);
        // set borders
        $sheet->getStyle('A2:Q2')->applyFromArray(array('borders' => array(
            'top' => array('style' => PHPExcel_Style_Border::BORDER_MEDIUM),
            'bottom' => array('style' => PHPExcel_Style_Border::BORDER_MEDIUM),
        )));
        // def color
        $green = '06b257';
        $satin = 'eeece1';
        $blue = '4f81bd';
        // set styles
        $this->setCellBackgroundColor($sheet, 'A1:Q1', $green);
        $this->setCellBackgroundColor($sheet, 'A2:P2', $satin);
        $this->setCellBackgroundColor($sheet, 'Q2', $blue);
        $sheet->getStyle('A2:Q2')->getFont()->setBold(true);
        $sheet->getStyle('A2:Q2')->getAlignment()->setWrapText(true);
        $sheet->getStyle('O')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
        $sheet->getStyle('A2:Q2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle('A2:Q2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
        $sheet->getStyle('A')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        // set value header
        $sheet->setCellValue('A2', 'No');
        $sheet->setCellValue('B2', 'Tgl Pemotongan (dd/MM/yyyy)');
        $sheet->setCellValue('C2', 'Penerima Penghasilan? (NPWP/NIK)');
        $sheet->setCellValue('D2', 'NPWP (tanpa format/tanda baca)');
        $sheet->setCellValue('E2', 'NIK (tanpa format/tanda baca)');
        $sheet->setCellValue('F2', 'Nama Penerima Penghasilan Sesuai NIK');
        $sheet->setCellValue('G2', 'Alamat Penerima Penghasilan Sesuai NIK');
        $sheet->setCellValue('H2', 'Kode Objek Pajak');
        $sheet->setCellValue('I2', 'Penandatangan Menggunakan? (NPWP/NIK)');
        $sheet->setCellValue('J2', 'NPWP Penandatangan (tanpa format/tanda baca)');
        $sheet->setCellValue('K2', 'NIK Penandatangan (tanpa format/tanda baca)');
        $sheet->setCellValue('L2', 'Kode PTKP');
        $sheet->setCellValue('M2', 'Pegawai Harian? (Ya/Tidak)');
        $sheet->setCellValue('N2', 'Menggunakan Gross Up? (Ya/Tidak)');
        $sheet->setCellValue('O2', 'Penghasilan Bruto');
        $sheet->setCellValue('P2', 'Mendapatkan Fasilitas ? (N/SKB/DTP)');
        $sheet->setCellValue('Q2', 'Nomor SKB/Nomor DTP');

        // set value with data
        $no = 1;
        $row = 3;
        foreach ($data as $value) {
            $sheet->setCellValue('A' . $row, $no);
            $sheet->setCellValue('B' . $row, date('d/m/Y', strtotime($value->tgl_pemotongan)));
            $sheet->setCellValue('C' . $row, $value->penerima_penghasilan);
            $sheet->setCellValueExplicit('D' . $row, $value->npwp);
            $sheet->setCellValueExplicit('E' . $row, $value->no_ktp);
            $sheet->setCellValue('F' . $row, $value->nm_karyawan);
            $sheet->setCellValue('G' . $row, $value->alamat_ktp);
            $sheet->setCellValue('H' . $row, $value->kd_objek_pajak);
            $sheet->setCellValue('I' . $row, $value->penandatangan_jenis);
            $sheet->setCellValue('J' . $row, $value->penandatangan_npwp);
            $sheet->setCellValue('K' . $row, $value->penandatangan_ktp);
            $sheet->setCellValue('L' . $row, $value->kd_ptkp);
            $sheet->setCellValue('M' . $row, $value->pegawai_harian);
            $sheet->setCellValue('N' . $row, $value->gross_up);
            $sheet->setCellValueExplicit('O' . $row, $value->gaji_bruto);
            $sheet->setCellValue('P' . $row, $value->fasilitas);
            $sheet->setCellValueExplicit('Q' . $row, $value->no_skb_dtp);

            $no++;
            $row++;
        }
    }

    /**
     * worksheet for tab 26
     *
     * @param object $excel => library PHPExcel
     * @return void
     */
    private function tab26($excel)
    {
        $excel->createSheet();
        $excel->setActiveSheetIndex(2);
        $sheet = $excel->getActiveSheet();
        // setup sheet
        $sheet->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
        $sheet->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
        $sheet->setTitle("26");
        // set width
        $sheet->getColumnDimension('A')->setWidth(5);
        $sheet->getColumnDimension('B')->setWidth(15);
        $sheet->getColumnDimension('C')->setWidth(14);
        $sheet->getColumnDimension('D')->setWidth(20);
        $sheet->getColumnDimension('E')->setWidth(20);
        $sheet->getColumnDimension('F')->setWidth(20);
        $sheet->getColumnDimension('G')->setWidth(20);
        $sheet->getColumnDimension('H')->setWidth(16);
        $sheet->getColumnDimension('I')->setWidth(20);
        $sheet->getColumnDimension('J')->setWidth(23);
        $sheet->getColumnDimension('K')->setWidth(20);
        $sheet->getColumnDimension('L')->setWidth(17);
        $sheet->getColumnDimension('M')->setWidth(17);
        $sheet->getColumnDimension('N')->setWidth(17);
        $sheet->getColumnDimension('O')->setWidth(17);
        $sheet->getColumnDimension('P')->setWidth(25);
        $sheet->getColumnDimension('Q')->setWidth(35);
        // set height
        $sheet->getRowDimension(2)->setRowHeight(45.75);
        // set borders
        $sheet->getStyle('A2:Q2')->applyFromArray(array('borders' => array(
            'top' => array('style' => PHPExcel_Style_Border::BORDER_MEDIUM),
            'bottom' => array('style' => PHPExcel_Style_Border::BORDER_MEDIUM),
        )));
        // def color
        $green = '06b257';
        $satin = 'eeece1';
        $blue = '4f81bd';
        // set styles
        $this->setCellBackgroundColor($sheet, 'A1:Q1', $green);
        $this->setCellBackgroundColor($sheet, 'A2:P2', $satin);
        $this->setCellBackgroundColor($sheet, 'Q2', $blue);
        $sheet->getStyle('A2:Q2')->getFont()->setBold(true);
        $sheet->getStyle('A2:Q2')->getAlignment()->setWrapText(true);
        $sheet->getStyle('O')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
        $sheet->getStyle('A2:Q2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle('A2:Q2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
        $sheet->getStyle('A')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        // set value header
        $sheet->setCellValue('A2', 'No');
        $sheet->setCellValue('B2', 'Tgl Pemotongan (dd/MM/yyyy)');
        $sheet->setCellValue('C2', 'Penerima Penghasilan? (NPWP/NIK)');
        $sheet->setCellValue('D2', 'NPWP (tanpa format/tanda baca)');
        $sheet->setCellValue('E2', 'NIK (tanpa format/tanda baca)');
        $sheet->setCellValue('F2', 'Nama Penerima Penghasilan Sesuai NIK');
        $sheet->setCellValue('G2', 'Alamat Penerima Penghasilan Sesuai NIK');
        $sheet->setCellValue('H2', 'Kode Objek Pajak');
        $sheet->setCellValue('I2', 'Penandatangan Menggunakan? (NPWP/NIK)');
        $sheet->setCellValue('J2', 'NPWP Penandatangan (tanpa format/tanda baca)');
        $sheet->setCellValue('K2', 'NIK Penandatangan (tanpa format/tanda baca)');
        $sheet->setCellValue('L2', 'Kode PTKP');
        $sheet->setCellValue('M2', 'Pegawai Harian? (Ya/Tidak)');
        $sheet->setCellValue('N2', 'Menggunakan Gross Up? (Ya/Tidak)');
        $sheet->setCellValue('O2', 'Penghasilan Bruto');
        $sheet->setCellValue('P2', 'Mendapatkan Fasilitas ? (N/SKB/DTP)');
        $sheet->setCellValue('Q2', 'Nomor SKB/Nomor DTP');
    }

    /**
     * worksheet for tab Ref Daftar Objek Pajak
     *
     * @param object $excel => library PHPExcel
     * @return void
     */
    private function tabObjekPajak($excel)
    {
        $excel->createSheet();
        $excel->setActiveSheetIndex(3);
        $sheet = $excel->getActiveSheet();
        // setup sheet
        $sheet->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
        $sheet->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
        $sheet->setTitle("Ref Daftar Objek Pajak")->getTabColor()->setRGB('00B050');
        // set width
        $sheet->getColumnDimension('A')->setWidth(2);
        $sheet->getColumnDimension('B')->setWidth(16);
        $sheet->getColumnDimension('C')->setWidth(130);
        $sheet->getColumnDimension('D')->setWidth(10);
        $sheet->getColumnDimension('E')->setWidth(10);
        $sheet->getColumnDimension('F')->setWidth(14);
        // set borders
        $sheet->getStyle('B2:F16')->applyFromArray(array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN))));
        // def color
        $grey = 'dcdcdb';
        // set styles
        $this->setCellBackgroundColor($sheet, 'B2:F2', $grey);
        $sheet->getStyle('A2:F2')->getFont()->setBold(true);
        $sheet->getStyle('D3:E16')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

        // set value header
        $sheet->setCellValue('B2', 'Kode Objek Pajak');
        $sheet->setCellValue('C2', 'Nama Objek Pajak');
        $sheet->setCellValue('D2', 'KAP');
        $sheet->setCellValue('E2', 'KJS');
        $sheet->setCellValue('F2', 'KETERANGAN');

        // set value with data
        $data = $this->db->select('kode, nama, kap, kjs, keterangan')
            ->get('payroll_pph21_objek_pajak')
            ->result();
        $row = 3;
        foreach ($data as $value) {
            $sheet->setCellValue('B' . $row, $value->kode);
            $sheet->setCellValue('C' . $row, $value->nama);
            $sheet->setCellValue('D' . $row, $value->kap);
            $sheet->setCellValue('E' . $row, $value->kjs);
            $sheet->setCellValue('F' . $row, $value->keterangan);
            $row++;
        }
    }

    /**
     * worksheet for tab Ref Daftar Kode Negara
     *
     * @param object $excel => library PHPExcel
     * @return void
     */
    private function tabKodeNegara($excel)
    {
        $excel->createSheet();
        $excel->setActiveSheetIndex(4);
        $sheet = $excel->getActiveSheet();
        // setup sheet
        $sheet->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
        $sheet->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
        $sheet->setTitle("Ref Daftar Kode Negara")->getTabColor()->setRGB('00B050');
        // set width
        $sheet->getColumnDimension('A')->setWidth(2);
        $sheet->getColumnDimension('B')->setWidth(9);
        $sheet->getColumnDimension('C')->setWidth(35);
        // set borders
        $sheet->getStyle('B2:C250')->applyFromArray(array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN))));
        // def color
        $grey = 'dcdcdb';
        // set styles
        $this->setCellBackgroundColor($sheet, 'B2:C2', $grey);
        $sheet->getStyle('B2:C2')->getFont()->setBold(true);

        // set value header
        $sheet->setCellValue('B2', 'KODE');
        $sheet->setCellValue('C2', 'Nama Negara');

        // set value with data
        $data = $this->db->select('kode, nm_negara')
            ->order_by('kode', 'asc')
            ->get('payroll_pph21_kd_negara')
            ->result();
        $row = 3;
        foreach ($data as $value) {
            $sheet->setCellValue('B' . $row, $value->kode);
            $sheet->setCellValue('C' . $row, $value->nm_negara);
            $row++;
        }
    }

    /**
     * worksheet for tab Ref Daftar PTKP
     *
     * @param object $excel => library PHPExcel
     * @return void
     */
    private function tabPtkp($excel)
    {
        $excel->createSheet();
        $excel->setActiveSheetIndex(5);
        $sheet = $excel->getActiveSheet();
        // setup sheet
        $sheet->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
        $sheet->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
        $sheet->setTitle("Ref Daftar PTKP")->getTabColor()->setRGB('00B050');
        // set width
        $sheet->getColumnDimension('A')->setWidth(2);
        $sheet->getColumnDimension('B')->setWidth(15);
        $sheet->getColumnDimension('C')->setWidth(15);
        // set borders
        $sheet->getStyle('B2:C10')->applyFromArray(array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN))));
        // def color
        $grey = 'dcdcdb';
        // set styles
        $this->setCellBackgroundColor($sheet, 'B2:C2', $grey);
        $sheet->getStyle('B2:C2')->getFont()->setBold(true);
        $sheet->getStyle('C3:C10')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

        // set value header
        $sheet->setCellValue('B2', 'PTKP');
        $sheet->setCellValue('C2', 'Nominal');

        // set value with data
        $data = $this->db->select('kode, nominal')
            ->order_by('no_urut', 'asc')
            ->limit(8)
            ->get('ptkp')
            ->result();
        $row = 3;
        foreach ($data as $value) {
            $sheet->setCellValue('B' . $row, $value->kode);
            $sheet->setCellValue('C' . $row, $value->nominal);
            $row++;
        }
    }

    /**
     * set cells background color
     *
     * @param object $sheet => active sheet
     * @param string $cell => ex: A1 or A1:H1
     * @param string $color => ex: FF0000
     * @return void
     */
    private function setCellBackgroundColor($sheet, $cell, $color)
    {
        $sheet->getStyle($cell)
            ->getFill()
            ->setFillType(PHPExcel_Style_Fill::FILL_SOLID)
            ->getStartColor()
            ->setRGB($color);
    }

    private function periode($bulan)
    {
        return date_format(date_create("01-" . $bulan), "Y-m");
    }
}
