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

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

        public function index(){
        }

        public function download($periode="-", $id_cabang="-", $id_departemen="-"){

            $bulan 		 			= $this->fungsi->get_bulan($periode);
            $tahun 					= $this->fungsi->get_tahun($periode);
            if($id_cabang == '-'){
                $nama_cabang = "SEMUA CABANG";
            }else{
                $nama_cabang = $this->fungsi->get_nama_cab($id_cabang);
            }

            if($id_departemen == '-'){
                $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 Infromasi Gaji Absenku")
                        ->setSubject("Template Infromasi Gaji")
                        ->setDescription("Template Informasi Gaji Absenku Profesioanal");
            // 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)
                ),
            );
            // 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
                )
            );

            $excel->setActiveSheetIndex(0)->setCellValue('A1', "TEMPLATE IMPORT INFORMASI GAJI"); // 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', "NO INDUK KARYAWAN"); 
            $excel->setActiveSheetIndex(0)->setCellValue('C7', "NAMA KARYAWAN");
            $excel->setActiveSheetIndex(0)->setCellValue('D7', "NAMA CABANG");
            $excel->setActiveSheetIndex(0)->setCellValue('E7', "NAMA DEPARTEMEN");
            $excel->setActiveSheetIndex(0)->setCellValue('F7', "NAMA JABATAN"); 
            $excel->setActiveSheetIndex(0)->setCellValue('G7', "GAJI POKOK *"); 
            $excel->setActiveSheetIndex(0)->setCellValue('H7', "TUNJANGAN");
            $excel->setActiveSheetIndex(0)->setCellValue('P7', "TOTAL TUNJANGAN *");
            $excel->setActiveSheetIndex(0)->setCellValue('Q7', "TOTAL PENERIMAAN *");
            $excel->setActiveSheetIndex(0)->setCellValue('R7', "TUNJANGAN YANG DIBIAYAI PERUSAHAAN");
            $excel->setActiveSheetIndex(0)->setCellValue('V7', "POTONGAN");  
            $excel->setActiveSheetIndex(0)->setCellValue('AC7', "TOTAL POTONGAN *");
            $excel->setActiveSheetIndex(0)->setCellValue('AD7', "GAJI DITERIMA *");      

            $excel->setActiveSheetIndex(0)->setCellValue('H8', "JABATAN"); 
            $excel->setActiveSheetIndex(0)->setCellValue('I8', "TRANSPORT"); 
            $excel->setActiveSheetIndex(0)->setCellValue('J8', "MAKAN"); 
            $excel->setActiveSheetIndex(0)->setCellValue('K8', "TUNJ.ISTRI"); 
            $excel->setActiveSheetIndex(0)->setCellValue('L8', "TUNJ.ANAK");
            $excel->setActiveSheetIndex(0)->setCellValue('M8', "LEMBUR"); 
            $excel->setActiveSheetIndex(0)->setCellValue('N8', "KOMISI"); 
            $excel->setActiveSheetIndex(0)->setCellValue('O8', "LAIN-LAIN");

            $excel->setActiveSheetIndex(0)->setCellValue('R8', "BPJS KETENAGAKERJAAN"); 
            $excel->setActiveSheetIndex(0)->setCellValue('S8', "BPJS KESEHATAN"); 
            $excel->setActiveSheetIndex(0)->setCellValue('T8', "LAIN-LAIN");
            $excel->setActiveSheetIndex(0)->setCellValue('U7', "TOTAL TUNJANGAN YANG DIBIAYAI PERUSAHAAN");

            $excel->setActiveSheetIndex(0)->setCellValue('V8', "ABSENSI"); 
            $excel->setActiveSheetIndex(0)->setCellValue('W8', "KETERLAMBATAN"); 
            $excel->setActiveSheetIndex(0)->setCellValue('X8', "KOPERASI"); 
            $excel->setActiveSheetIndex(0)->setCellValue('Y8', "ASURANSI KESEHATAN"); 
            $excel->setActiveSheetIndex(0)->setCellValue('Z8', "BPJS KETENAGAKERJAAN");
            $excel->setActiveSheetIndex(0)->setCellValue('AA8', "BPJS KESEHATAN"); 
            $excel->setActiveSheetIndex(0)->setCellValue('AB8', "LAIN-LAIN"); 

            //ROWSPAM
            $excel->getActiveSheet()->mergeCells('A7:A8');
            $excel->getActiveSheet()->mergeCells('B7:B8');
            $excel->getActiveSheet()->mergeCells('C7:C8');
            $excel->getActiveSheet()->mergeCells('D7:D8');
            $excel->getActiveSheet()->mergeCells('E7:E8');
            $excel->getActiveSheet()->mergeCells('F7:F8');
            $excel->getActiveSheet()->mergeCells('G7:G8');
            $excel->getActiveSheet()->mergeCells('P7:P8');
            $excel->getActiveSheet()->mergeCells('Q7:Q8');
            $excel->getActiveSheet()->mergeCells('U7:U8');
            $excel->getActiveSheet()->mergeCells('AC7:AC8');
            $excel->getActiveSheet()->mergeCells('AD7:AD8');

            //COLSPAN
            $excel->getActiveSheet()->mergeCells('H7:O7');
            $excel->getActiveSheet()->mergeCells('R7:T7');
            $excel->getActiveSheet()->mergeCells('V7:AB7');

            //SET WIDTH
            $excel->getActiveSheet()->getColumnDimension('A')->setWidth("5");
            $excel->getActiveSheet()->getColumnDimension('B')->setWidth("30");
            $excel->getActiveSheet()->getColumnDimension('C')->setWidth("40");
            $excel->getActiveSheet()->getColumnDimension('D')->setWidth("40");
            $excel->getActiveSheet()->getColumnDimension('E')->setWidth("40");
            $excel->getActiveSheet()->getColumnDimension('F')->setWidth("40");
            $excel->getActiveSheet()->getColumnDimension('G')->setWidth("20");
            $excel->getActiveSheet()->getColumnDimension('H')->setWidth("20");
            $excel->getActiveSheet()->getColumnDimension('I')->setWidth("20");
            $excel->getActiveSheet()->getColumnDimension('J')->setWidth("20");
            $excel->getActiveSheet()->getColumnDimension('K')->setWidth("20");
            $excel->getActiveSheet()->getColumnDimension('L')->setWidth("20");
            $excel->getActiveSheet()->getColumnDimension('M')->setWidth("20");
            $excel->getActiveSheet()->getColumnDimension('N')->setWidth("20");
            $excel->getActiveSheet()->getColumnDimension('O')->setWidth("20");
            $excel->getActiveSheet()->getColumnDimension('P')->setWidth("20");
            $excel->getActiveSheet()->getColumnDimension('Q')->setWidth("20");
            $excel->getActiveSheet()->getColumnDimension('R')->setWidth("20");
            $excel->getActiveSheet()->getColumnDimension('S')->setWidth("20");
            $excel->getActiveSheet()->getColumnDimension('T')->setWidth("20");
            $excel->getActiveSheet()->getColumnDimension('U')->setWidth("20");
            $excel->getActiveSheet()->getColumnDimension('V')->setWidth("30");
            $excel->getActiveSheet()->getColumnDimension('W')->setWidth("20");
            $excel->getActiveSheet()->getColumnDimension('X')->setWidth("20");
            $excel->getActiveSheet()->getColumnDimension('Y')->setWidth("20");
            $excel->getActiveSheet()->getColumnDimension('Z')->setWidth("20");
            $excel->getActiveSheet()->getColumnDimension('AA')->setWidth("20");
            $excel->getActiveSheet()->getColumnDimension('AB')->setWidth("30");
            $excel->getActiveSheet()->getColumnDimension('AC')->setWidth("30");
            $excel->getActiveSheet()->getColumnDimension('AD')->setWidth("20");
            //header

            $this->db->select("data_karyawan.id_karyawan, 
                            data_karyawan.nik, 
                            data_karyawan.nama_lengkap, 
                            master_cabang.kode as kode_cabang, 
                            master_cabang.nama as nama_cabang, 
                            master_departemen.kode as kode_departemen, 
                            master_departemen.nama as nama_departemen, 
                            master_jabatan.kode_jabatan as kode_jabatan, 
                            master_jabatan.nama as nama_jabatan");
            $this->db->from("data_karyawan");
            $this->db->join('master_cabang','data_karyawan.id_cabang  = master_cabang.id_cabang', 'left');
            $this->db->join('master_departemen','data_karyawan.id_departemen  = master_departemen.id_departemen', 'left');
            $this->db->join('master_jabatan','data_karyawan.id_jabatan  = master_jabatan.id_jabatan', 'left');
            $this->db->where("data_karyawan.id_company", $this->id_company);
            $this->db->where_in("data_karyawan.status",["1","2","3","4"]);
            $this->db->where_not_in("master_jabatan.level_user",["1","6"]);
            $this->db->order_by("master_cabang.kode","ASC");
            $this->db->order_by("master_departemen.kode","ASC");
            $this->db->order_by("master_jabatan.kode_jabatan","ASC");
            $this->db->order_by("data_karyawan.nik","ASC");
        
            if($id_cabang != '-') {
                $this->db->where('data_karyawan.id_cabang', $id_cabang);  
            }

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

            $this->db->order_by("master_cabang.kode","ASC");
            $this->db->order_by("master_departemen.kode","ASC");
            $this->db->order_by("master_jabatan.kode_jabatan","ASC");
            $this->db->order_by("data_karyawan.nik","ASC");

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

            $no = 1; // Untuk penomoran tabel, di awal set dengan 1
            $numrow = 9; // Set baris pertama untuk isi tabel adalah baris ke 9
            foreach($data_karyawan as $data){
                $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);
                $excel->getActiveSheet()->protectCells('A'.$numrow.':F'.$numrow, 'PHPExcel');
                // UNPROTECT THE CELL RANGE
                $excel->getActiveSheet()->getStyle('G'.$numrow.':O'.$numrow)->getProtection()->setLocked(PHPExcel_Style_Protection::PROTECTION_UNPROTECTED);
                $excel->getActiveSheet()->getStyle('R'.$numrow.':T'.$numrow)->getProtection()->setLocked(PHPExcel_Style_Protection::PROTECTION_UNPROTECTED);
                $excel->getActiveSheet()->getStyle('V'.$numrow.':AB'.$numrow)->getProtection()->setLocked(PHPExcel_Style_Protection::PROTECTION_UNPROTECTED);

                //total tunjangan
                $excel->getActiveSheet()->setCellValue('P'.$numrow,'=SUM(H'.$numrow.':O'.$numrow.')');

                //total penerimaan
                $excel->getActiveSheet()->setCellValue('Q'.$numrow,'=SUM(G'.$numrow.'+P'.$numrow.')');

                //TOTAL TUNJANGAN YANG DIBIAYAI PERUSAHAAN
                $excel->getActiveSheet()->setCellValue('U'.$numrow,'=SUM(R'.$numrow.':T'.$numrow.')');

                //total potongan
                $excel->getActiveSheet()->setCellValue('AC'.$numrow,'=SUM(V'.$numrow.':AB'.$numrow.')');

                //gaji diterima	  
                $excel->getActiveSheet()->setCellValue('AD'.$numrow,'=SUM(Q'.$numrow.'-AC'.$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
            $range = 'B9:B'.$numrow;
            $excel->getActiveSheet()
                ->getStyle($range)
                ->getNumberFormat()
                ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);

            # FORMAT TEXT
            $range = 'AD9:AJ'.$numrow;
            $excel->getActiveSheet()
                ->getStyle($range)
                ->getNumberFormat()
                ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);

            # BORDER SEMUA KOLOM
            $excel->getActiveSheet()
                ->getStyle("A7:AD8")
                ->applyFromArray($style_header);
                
            $excel->getActiveSheet()
                ->getStyle("A7:AD".$numrow)
                ->applyFromArray(
                    array(
                        'borders' => array(
                            'allborders' => array(
                                'style' => PHPExcel_Style_Border::BORDER_THIN
                            )
                        )
                    )
                );

            # FORMAT NUMBER
            $excel->getActiveSheet()
                ->getStyle("G9:AD".$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 Informasi Gaji");
            $excel->setActiveSheetIndex(0);

            $filename = "TEMPLATE_INFORMASI_GAJI_".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');
        }
    }
