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

class Home extends MY_Controller
{   
    public function index(){
        $data['judul'] = 'Demografi Pelanggan';
        $this->load->view('sales_activity/demografi_pelanggan/index', $data);
    }

    public function tabs($tabs = null)
    {   
        switch ($tabs) {
            case "pelanggan" :
                $this->load->view('sales_activity/demografi_pelanggan/pelanggan/home');
                break;
            case "source" :
                $this->load->view('sales_activity/demografi_pelanggan/source/home');
                break;
            case "wilayah" :
                $this->load->view('sales_activity/demografi_pelanggan/wilayah/index');
                break;
            case "jenis_pelanggan" :
                $this->load->view('sales_activity/demografi_pelanggan/jenis_pelanggan/tabs');
                break;
            case "product" :
                $this->load->view('sales_activity/demografi_pelanggan/product/home');
                break;
            case "group_product" :
                $this->load->view('sales_activity/demografi_pelanggan/group_product/home');
                break;
            default:
                echo $this->lang->line('data_tidak_ditemukan');
        }
    }

    public function dropdown_group_area(){
        $search         = $this->input->get("q");
        $tahun          = $this->input->get("tahun");
        $bulan          = $this->input->get("bulan");
        $tahun_sekarang = date('Y');

        if ($tahun < $tahun_sekarang) {
            $this->db->select('id_group_m, nama_group');
            $this->db->from('sales_pipeline_trx');
            $this->db->where('id_company', $this->id_company);
            $this->db->where('tahun', $tahun);
            $this->db->where('id_group_m !=', "");
            $this->db->where('nama_group !=', "");
            $this->db->group_by('id_group_m');

            if($this->is_sales_admin == true){
                if (!in_array($this->sales_admin_group, ["all", ""])) {
                    $this->db->where_in('id_group_m', explode(',', $this->sales_admin_group));
                }
            }

            if($bulan !== "all"){
                $this->db->where('bulan', sprintf('%02d', $bulan));
            }

            if(!empty($search)){
                $this->db->like('nama_group', $search);
            }

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

        }else{
             // Binding Tahun
            $binding[] = $tahun;

            // Binding Bulan
            if($bulan != "all"){
                $where_bulan = "AND bulan = ?";
                $binding[]   = sprintf('%02d', $bulan);
            }else{
                $where_bulan = "";
            }

            // Binding Search
            if(!empty($search)){
                $binding[]    = "%".$search."%";
                $where_search = "WHERE combined.nama_group LIKE ?";
            }else{
                $where_search = "";
            }
            
            // Query jika admin sales
            if($this->is_sales_admin == true){
                if (!in_array($this->sales_admin_group, ["all", ""])) {
                    if(!empty($where_search)){
                        $where_adm = "AND combined.id_group_m IN ('" . str_replace(',', "', '", $this->sales_admin_group) . "')";
                    }else{
                        $where_adm = "WHERE combined.id_group_m IN ('" . str_replace(',', "', '", $this->sales_admin_group) . "')";
                    }
                }
            }else{
                $where_adm = "";
            }

            $sql = "
                SELECT * FROM (
                    SELECT
                        id_group_m,
                        CONCAT(kode, ' - ', nama) AS nama_group
                    FROM sales_group_m 
                    WHERE id_company = '" . $this->id_company . "'

                    UNION ALL

                    SELECT 
                        id_group_m,
                        nama_group 
                    FROM sales_pipeline_trx
                    WHERE id_company = '" . $this->id_company . "'
                    AND tahun = ?
                    ".$where_bulan."
                    AND id_group_m != ''
                    GROUP BY id_group_m
                ) AS combined
                ".$where_search."
                ".$where_adm."
                GROUP BY combined.id_group_m
                ORDER BY combined.nama_group ASC
            ";

            $result = $this->db->query($sql, $binding)->result();
        }

        $json = [];
        foreach ($result as $rows) {
            $json[] = [
                'id'         => $rows->id_group_m,
                'text'       => $rows->nama_group,
                'nama_group' => $rows->nama_group
            ];
        }
        echo json_encode($json);
    }

    public function download_semua_modal(){
		$this->load->view('sales_activity/demografi_pelanggan/modal_download_all');
    }

    public function download_semua_action(){
        // Model
        $this->load->model('sales_activity/demografi_pelanggan/Pelanggan_m');
        $this->load->model('sales_activity/demografi_pelanggan/Source_m');
        $this->load->model('sales_activity/demografi_pelanggan/wilayah/Provinsi_m');
        $this->load->model('sales_activity/demografi_pelanggan/wilayah/Kota_m');
        $this->load->model('sales_activity/demografi_pelanggan/wilayah/Kecamatan_m');
        $this->load->model('sales_activity/demografi_pelanggan/wilayah/Kelurahan_m');
        $this->load->model('sales_activity/demografi_pelanggan/Jenis_pelanggan_m');
        $this->load->model('sales_activity/demografi_pelanggan/Jenis_perusahaan_m');
        $this->load->model('sales_activity/demografi_pelanggan/Jenis_perorangan_m');
        $this->load->model('sales_activity/demografi_pelanggan/Grup_produk_m');
        $this->load->model('sales_activity/demografi_pelanggan/Product_m');
        $this->load->library(array('PHPExcel/Excel', 'PHPExcel/PHPExcel'));

        // Input
        $tahun      = $this->input->get('tahun');
        $bulan      = $this->input->get('bulan');
        $id_group_m = $this->input->get('id_group_m');

        // Filename
        if($bulan == "all"){
            $filename      = "DEMOGRAFI PELANGGAN SEMUA KATEGORI ". $tahun;
        }else{
            $nama_bulan    = $this->fungsi->konversi_bulan($bulan);
            $filename      = "DEMOGRAFI PELANGGAN SEMUA KATEGORI TAHUN ". $tahun. " BULAN ". strtoupper($nama_bulan);
        }

        if($id_group_m == "all"){
            $nama_group  = "SEMUA GROUP";
            $filename   .= " SEMUA GROUP";
        }else{
            $nama_group  = $this->input->get('nama_group');
            $nama_group  = strtoupper($nama_group);
            $filename   .= " ". $nama_group;
        }

		// 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("Demografi Pelanggan")
            ->setSubject("Demografi Pelanggan")
            ->setDescription("Demografi Pelanggan");

        $this->_excel_tab_pelanggan($excel, $tahun, $bulan, $id_group_m, $nama_group);
        $this->_excel_tab_source($excel, $tahun, $bulan, $id_group_m, $nama_group);
        $this->_excel_tab_provinsi($excel, $tahun, $bulan, $id_group_m, $nama_group);
        $this->_excel_tab_kota($excel, $tahun, $bulan, $id_group_m, $nama_group);
        $this->_excel_tab_kecamatan($excel, $tahun, $bulan, $id_group_m, $nama_group);
        $this->_excel_tab_kelurahan($excel, $tahun, $bulan, $id_group_m, $nama_group);
        $this->_excel_tab_jenis_pelanggan($excel, $tahun, $bulan, $id_group_m, $nama_group);
        $this->_excel_tab_jenis_perusahaan($excel, $tahun, $bulan, $id_group_m, $nama_group);
        $this->_excel_tab_jenis_perorangan($excel, $tahun, $bulan, $id_group_m, $nama_group);
        $this->_excel_tab_group_product($excel, $tahun, $bulan, $id_group_m, $nama_group);
        $this->_excel_tab_product($excel, $tahun, $bulan, $id_group_m, $nama_group);
        $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');
    }

    # 1. TAB PELANGGAN
    private function _excel_tab_pelanggan($excel, $tahun="0000", $bulan="00", $id_group_m="all", $nama_group="-"){
        $excel->setActiveSheetIndex(0);
        $sheet = $excel->getActiveSheet();
        // $sheet->setShowGridlines(false);

        // setup sheet
        $sheet->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $sheet->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
        $sheet->setTitle("Pelanggan");

        // 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)
            ),
        );
        
        // set width
        $sheet->getColumnDimension('A')->setWidth(5);
        $sheet->getColumnDimension('B')->setWidth(70);
        $sheet->getColumnDimension('C')->setWidth(12);
        $sheet->getColumnDimension('D')->setWidth(20);
        
        // start header
        $sheet->setCellValue('A1', "DEMOGRAFI PELANGGAN BERDASARKAN NAMA PELANGGAN"); 
        $sheet->mergeCells('A1:D1'); 
        $sheet->getStyle('A1')->getFont()->setBold(TRUE)->setSize(11); 
        $sheet->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); 
        
        if($bulan == "all"){
            $nama_bulan = '';
        }else{
            $nama_bulan    = $this->fungsi->konversi_bulan($bulan);
            $nama_bulan    = ' BULAN '. strtoupper($nama_bulan);
        }
        
        $sheet->setCellValue('A2', 'TAHUN '. $tahun. $nama_bulan);
        $sheet->mergeCells('A2:D2'); 
        $sheet->getStyle('A2')->getFont()->setBold(TRUE)->setSize(11); 
        $sheet->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $sheet->setCellValue('A3', $nama_group);
        $sheet->mergeCells('A3:D3'); 
        $sheet->getStyle('A3')->getFont()->setBold(TRUE)->setSize(11); 
        $sheet->getStyle('A3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $sheet->setCellValue('A5', "NO"); 
        $sheet->setCellValue('B5', "PELANGGAN"); 
        $sheet->setCellValue('C5', "JUMLAH");
        $sheet->setCellValue('D5', "NOMINAL"); 
        // end header

        // fill data
        $tahun = $this->input->get('tahun');
        $bulan = $this->input->get('bulan');
        $list  = $this->Pelanggan_m->get_datatables($tahun, $bulan, $id_group_m);

        $no          = 1;
        $numrow      = 6;
        $ttl_jumlah  = 0;
        $ttl_nominal = 0;
        foreach($list as $field){
            $sheet->setCellValue('A'.$numrow, $no);
            $sheet->setCellValue('B'.$numrow, $field->nama_perusahaan);
            $sheet->setCellValue('C'.$numrow, $field->jumlah);
            $sheet->setCellValue('D'.$numrow, (int)$field->nominal);

            $no++; 
            $numrow++;
            $ttl_jumlah  += $field->jumlah;
            $ttl_nominal += (int)$field->nominal;
        }

        // footer total
        $sheet->mergeCells('A'. $numrow. ':B'. $numrow);
        $sheet->setCellValue('A'. $numrow, "GRAND TOTAL");
        $sheet->setCellValue('C'. $numrow, $ttl_jumlah);
        $sheet->setCellValue('D'. $numrow, $ttl_nominal);

        // bold kolom
        $sheet->getStyle("A5:D5")
            ->applyFromArray($style_header);
        $sheet->getStyle('A'. $numrow. ':D'. $numrow)
            ->applyFromArray($style_header);

        // border kolom
        $sheet->getStyle("A5:D".$numrow)
            ->applyFromArray(
                array(
                    'borders' => array(
                        'allborders' => array(
                            'style' => PHPExcel_Style_Border::BORDER_THIN
                        )
                    )
                )
            );
        
        // set align column
        $sheet->getStyle('B5'. ':B'.$numrow)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
        $sheet->getStyle('D6'. ':D'.$numrow)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

        // format number column
        $sheet->getStyle('D6:D'. $numrow)->getNumberFormat()->setFormatCode('#,##0');
        
        // Set height semua kolom menjadi auto (mengikuti height isi dari kolommnya, jadi otomatis)
        $sheet->getDefaultRowDimension()->setRowHeight(-1);
        // Set orientasi kertas jadi LANDSCAPE
        $sheet->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
    }

    # 2. TAB SOURCE
    private function _excel_tab_source($excel, $tahun="0000", $bulan="00", $id_group_m="all", $nama_group="-"){
        $excel->createSheet();
        $excel->setActiveSheetIndex(1);
        $sheet = $excel->getActiveSheet();

        // setup sheet
        $sheet->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $sheet->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
        $sheet->setTitle("Source");

        // 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)
            ),
        );
        
        // set width
        $sheet->getColumnDimension('A')->setWidth(5);
        $sheet->getColumnDimension('B')->setWidth(70);
        $sheet->getColumnDimension('C')->setWidth(12);
        $sheet->getColumnDimension('D')->setWidth(20);
        
        // start header
        $sheet->setCellValue('A1', "DEMOGRAFI PELANGGAN BERDASARKAN SOURCE"); 
        $sheet->mergeCells('A1:D1'); 
        $sheet->getStyle('A1')->getFont()->setBold(TRUE)->setSize(11); 
        $sheet->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        
        if($bulan == "all"){
            $nama_bulan = '';
        }else{
            $nama_bulan    = $this->fungsi->konversi_bulan($bulan);
            $nama_bulan    = ' BULAN '. strtoupper($nama_bulan);
        }
        
        $sheet->setCellValue('A2', 'TAHUN '. $tahun. $nama_bulan); 
        $sheet->mergeCells('A2:D2'); 
        $sheet->getStyle('A2')->getFont()->setBold(TRUE)->setSize(11); 
        $sheet->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);


        $sheet->setCellValue('A3', $nama_group);
        $sheet->mergeCells('A3:D3'); 
        $sheet->getStyle('A3')->getFont()->setBold(TRUE)->setSize(11); 
        $sheet->getStyle('A3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $sheet->setCellValue('A5', "NO"); 
        $sheet->setCellValue('B5', "SOURCE"); 
        $sheet->setCellValue('C5', "JUMLAH"); 
        $sheet->setCellValue('D5', "NOMINAL"); 
        // end header

        // fill data
        $tahun = $this->input->get('tahun');
        $bulan = $this->input->get('bulan');
        $list  = $this->Source_m->get_datatables($tahun, $bulan, $id_group_m, "all");

        $no          = 1;
        $numrow      = 6;
        $ttl_jumlah  = 0;
        $ttl_nominal = 0;
        foreach($list as $field){
            $sheet->setCellValue('A'.$numrow, $no);
            $sheet->setCellValue('B'.$numrow, ucfirst($field->nama_lead_source));
            $sheet->setCellValue('C'.$numrow, $field->jumlah);
            $sheet->setCellValue('D'.$numrow, (int)$field->nominal);

            $no++; 
            $numrow++;
            $ttl_jumlah  += $field->jumlah;
            $ttl_nominal += (int)$field->nominal;
        }

        // footer total
        $sheet->mergeCells('A'. $numrow. ':B'. $numrow);
        $sheet->setCellValue('A'. $numrow, "GRAND TOTAL");
        $sheet->setCellValue('C'. $numrow, $ttl_jumlah);
        $sheet->setCellValue('D'. $numrow, $ttl_nominal);

        // bold kolom
        $sheet->getStyle("A5:D5")
            ->applyFromArray($style_header);
        $sheet->getStyle('A'. $numrow. ':D'. $numrow)
            ->applyFromArray($style_header);

        // border kolom
        $sheet->getStyle("A5:D".$numrow)
            ->applyFromArray(
                array(
                    'borders' => array(
                        'allborders' => array(
                            'style' => PHPExcel_Style_Border::BORDER_THIN
                        )
                    )
                )
            );
        
        // set align column
        $sheet->getStyle('B5'. ':B'.$numrow)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
        $sheet->getStyle('D6'. ':D'.$numrow)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

        // format number column
        $sheet->getStyle('D6:D'. $numrow)->getNumberFormat()->setFormatCode('#,##0');
        
        // Set height semua kolom menjadi auto (mengikuti height isi dari kolommnya, jadi otomatis)
        $sheet->getDefaultRowDimension()->setRowHeight(-1);
        // Set orientasi kertas jadi LANDSCAPE
        $sheet->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
    }

    # 3. TAB PROVINSI
    private function _excel_tab_provinsi($excel, $tahun="0000", $bulan="00", $id_group_m="all", $nama_group="-"){
        $excel->createSheet();
        $excel->setActiveSheetIndex(2);
        $sheet = $excel->getActiveSheet();

        // setup sheet
        $sheet->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $sheet->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
        $sheet->setTitle("Provinsi");

        // 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)
            ),
        );
        
        // set width
        $sheet->getColumnDimension('A')->setWidth(5);
        $sheet->getColumnDimension('B')->setWidth(70);
        $sheet->getColumnDimension('C')->setWidth(12);
        $sheet->getColumnDimension('D')->setWidth(20);
        
        // start header
        $sheet->setCellValue('A1', "DEMOGRAFI PELANGGAN BERDASARKAN PROVINSI"); 
        $sheet->mergeCells('A1:D1'); 
        $sheet->getStyle('A1')->getFont()->setBold(TRUE)->setSize(11); 
        $sheet->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); 
        
        if($bulan == "all"){
            $nama_bulan = '';
        }else{
            $nama_bulan    = $this->fungsi->konversi_bulan($bulan);
            $nama_bulan    = ' BULAN '. strtoupper($nama_bulan);
        }
        
        $sheet->setCellValue('A2', 'TAHUN '. $tahun. $nama_bulan); 
        $sheet->mergeCells('A2:D2'); 
        $sheet->getStyle('A2')->getFont()->setBold(TRUE)->setSize(11); 
        $sheet->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $sheet->setCellValue('A3', $nama_group);
        $sheet->mergeCells('A3:D3'); 
        $sheet->getStyle('A3')->getFont()->setBold(TRUE)->setSize(11); 
        $sheet->getStyle('A3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $sheet->setCellValue('A5', "NO"); 
        $sheet->setCellValue('B5', "PROVINSI"); 
        $sheet->setCellValue('C5', "JUMLAH"); 
        $sheet->setCellValue('D5', "NOMINAL"); 
        // end header

        // fill data
        $tahun = $this->input->get('tahun');
        $bulan = $this->input->get('bulan');
        $list  = $this->Provinsi_m->get_datatables($tahun, $bulan, $id_group_m, "all");

        $no          = 1;
        $numrow      = 6;
        $ttl_jumlah  = 0;
        $ttl_nominal = 0;
        foreach($list as $field){
            $sheet->setCellValue('A'.$numrow, $no);
            $sheet->setCellValue('B'.$numrow, $field->nama_provinsi);
            $sheet->setCellValue('C'.$numrow, $field->jumlah);
            $sheet->setCellValue('D'.$numrow, (int)$field->nominal);

            $no++; 
            $numrow++;
            $ttl_jumlah  += $field->jumlah;
            $ttl_nominal += (int)$field->nominal;
        }

        // footer total
        $sheet->mergeCells('A'. $numrow. ':B'. $numrow);
        $sheet->setCellValue('A'. $numrow, "GRAND TOTAL");
        $sheet->setCellValue('C'. $numrow, $ttl_jumlah);
        $sheet->setCellValue('D'. $numrow, $ttl_nominal);

        // bold kolom
        $sheet->getStyle("A5:D5")
            ->applyFromArray($style_header);
        $sheet->getStyle('A'. $numrow. ':D'. $numrow)
            ->applyFromArray($style_header);

        // border kolom
        $sheet->getStyle("A5:D".$numrow)
            ->applyFromArray(
                array(
                    'borders' => array(
                        'allborders' => array(
                            'style' => PHPExcel_Style_Border::BORDER_THIN
                        )
                    )
                )
            );
        
        // set align column
        $sheet->getStyle('B5'. ':B'.$numrow)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
        $sheet->getStyle('D5'. ':D'.$numrow)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

        // format number column
        $sheet->getStyle('D6:D'. $numrow)->getNumberFormat()->setFormatCode('#,##0');
        
        // Set height semua kolom menjadi auto (mengikuti height isi dari kolommnya, jadi otomatis)
        $sheet->getDefaultRowDimension()->setRowHeight(-1);
        // Set orientasi kertas jadi LANDSCAPE
        $sheet->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
    }

    # 4. TAB KOTA
    private function _excel_tab_kota($excel, $tahun="0000", $bulan="00", $id_group_m="all", $nama_group="-"){
        $excel->createSheet();
        $excel->setActiveSheetIndex(3);
        $sheet = $excel->getActiveSheet();

        // setup sheet
        $sheet->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $sheet->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
        $sheet->setTitle("Kota");

        // 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)
            ),
        );
        
        // set width
        $sheet->getColumnDimension('A')->setWidth(5);
        $sheet->getColumnDimension('B')->setWidth(50);
        $sheet->getColumnDimension('C')->setWidth(50);
        $sheet->getColumnDimension('D')->setWidth(12);
        $sheet->getColumnDimension('E')->setWidth(20);
        
        // start header
        $sheet->setCellValue('A1', "DEMOGRAFI PELANGGAN BERDASARKAN KOTA"); 
        $sheet->mergeCells('A1:E1'); 
        $sheet->getStyle('A1')->getFont()->setBold(TRUE)->setSize(11); 
        $sheet->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); 
        
        if($bulan == "all"){
            $nama_bulan = '';
        }else{
            $nama_bulan    = $this->fungsi->konversi_bulan($bulan);
            $nama_bulan    = ' BULAN '. strtoupper($nama_bulan);
        }
        
        $sheet->setCellValue('A2', 'TAHUN '. $tahun. $nama_bulan); 
        $sheet->mergeCells('A2:E2'); 
        $sheet->getStyle('A2')->getFont()->setBold(TRUE)->setSize(11); 
        $sheet->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $sheet->setCellValue('A3', $nama_group);
        $sheet->mergeCells('A3:E3'); 
        $sheet->getStyle('A3')->getFont()->setBold(TRUE)->setSize(11); 
        $sheet->getStyle('A3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $sheet->setCellValue('A5', "NO"); 
        $sheet->setCellValue('B5', "PROVINSI"); 
        $sheet->setCellValue('C5', "KOTA"); 
        $sheet->setCellValue('D5', "JUMLAH"); 
        $sheet->setCellValue('E5', "NOMINAL"); 
        // end header

        // fill data
        $tahun = $this->input->get('tahun');
        $bulan = $this->input->get('bulan');
        $list  = $this->Kota_m->get_datatables($tahun, $bulan, $id_group_m, "all");

        $no          = 1;
        $numrow      = 6;
        $ttl_jumlah  = 0;
        $ttl_nominal = 0;
        foreach($list as $field){
            $sheet->setCellValue('A'.$numrow, $no);
            $sheet->setCellValue('B'.$numrow, $field->nama_provinsi);
            $sheet->setCellValue('C'.$numrow, $field->nama_kota);
            $sheet->setCellValue('D'.$numrow, $field->jumlah);
            $sheet->setCellValue('E'.$numrow, (int)$field->nominal);

            $no++; 
            $numrow++;
            $ttl_jumlah  += $field->jumlah;
            $ttl_nominal += (int)$field->nominal;
        }

        // footer total
        $sheet->mergeCells('A'. $numrow. ':C'. $numrow);
        $sheet->setCellValue('A'. $numrow, "GRAND TOTAL");
        $sheet->setCellValue('D'. $numrow, $ttl_jumlah);
        $sheet->setCellValue('E'. $numrow, $ttl_nominal);

        // bold kolom
        $sheet->getStyle("A5:E5")
            ->applyFromArray($style_header);
        $sheet->getStyle('A'. $numrow. ':E'. $numrow)
            ->applyFromArray($style_header);

        // border kolom
        $sheet->getStyle("A5:E".$numrow)
            ->applyFromArray(
                array(
                    'borders' => array(
                        'allborders' => array(
                            'style' => PHPExcel_Style_Border::BORDER_THIN
                        )
                    )
                )
            );
        
        // set align column
        $sheet->getStyle('B5'. ':B'.$numrow)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
        $sheet->getStyle('C5'. ':C'.$numrow)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
        $sheet->getStyle('E6'. ':E'.$numrow)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

        // format number column
        $sheet->getStyle('E6:E'. $numrow)->getNumberFormat()->setFormatCode('#,##0');
        
        // Set height semua kolom menjadi auto (mengikuti height isi dari kolommnya, jadi otomatis)
        $sheet->getDefaultRowDimension()->setRowHeight(-1);
        // Set orientasi kertas jadi LANDSCAPE
        $sheet->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
    }

    # 5. TAB KECAMATAN
    private function _excel_tab_kecamatan($excel, $tahun="0000", $bulan="00", $id_group_m="all", $nama_group="-"){
        $excel->createSheet();
        $excel->setActiveSheetIndex(4);
        $sheet = $excel->getActiveSheet();

        // setup sheet
        $sheet->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $sheet->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
        $sheet->setTitle("Kecamatan");

        // 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)
            ),
        );
        
        // set width
        $sheet->getColumnDimension('A')->setWidth(5);
        $sheet->getColumnDimension('B')->setWidth(40);
        $sheet->getColumnDimension('C')->setWidth(40);
        $sheet->getColumnDimension('D')->setWidth(40);
        $sheet->getColumnDimension('E')->setWidth(12);
        $sheet->getColumnDimension('F')->setWidth(20);
        
        // start header
        $sheet->setCellValue('A1', "DEMOGRAFI PELANGGAN BERDASARKAN KECAMATAN"); 
        $sheet->mergeCells('A1:F1'); 
        $sheet->getStyle('A1')->getFont()->setBold(TRUE)->setSize(11); 
        $sheet->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); 
        
        if($bulan == "all"){
            $nama_bulan = '';
        }else{
            $nama_bulan    = $this->fungsi->konversi_bulan($bulan);
            $nama_bulan    = ' BULAN '. strtoupper($nama_bulan);
        }
        
        $sheet->setCellValue('A2', 'TAHUN '. $tahun. $nama_bulan); 
        $sheet->mergeCells('A2:F2'); 
        $sheet->getStyle('A2')->getFont()->setBold(TRUE)->setSize(11); 
        $sheet->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $sheet->setCellValue('A3', $nama_group);
        $sheet->mergeCells('A3:F3'); 
        $sheet->getStyle('A3')->getFont()->setBold(TRUE)->setSize(11); 
        $sheet->getStyle('A3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $sheet->setCellValue('A5', "NO"); 
        $sheet->setCellValue('B5', "PROVINSI"); 
        $sheet->setCellValue('C5', "KOTA"); 
        $sheet->setCellValue('D5', "KECAMATAN"); 
        $sheet->setCellValue('E5', "JUMLAH"); 
        $sheet->setCellValue('F5', "NOMINAL"); 
        // end header

        // fill data
        $tahun = $this->input->get('tahun');
        $bulan = $this->input->get('bulan');
        $list  = $this->Kecamatan_m->get_datatables($tahun, $bulan, $id_group_m, "all");

        $no          = 1;
        $numrow      = 6;
        $ttl_jumlah  = 0;
        $ttl_nominal = 0;
        foreach($list as $field){
            $sheet->setCellValue('A'.$numrow, $no);
            $sheet->setCellValue('B'.$numrow, $field->nama_provinsi);
            $sheet->setCellValue('C'.$numrow, $field->nama_kota);
            $sheet->setCellValue('D'.$numrow, $field->nama_kecamatan);
            $sheet->setCellValue('E'.$numrow, $field->jumlah);
            $sheet->setCellValue('F'.$numrow, (int)$field->nominal);

            $no++; 
            $numrow++;
            $ttl_jumlah  += $field->jumlah;
            $ttl_nominal += (int)$field->nominal;
        }

        // footer total
        $sheet->mergeCells('A'. $numrow. ':D'. $numrow);
        $sheet->setCellValue('A'. $numrow, "GRAND TOTAL");
        $sheet->setCellValue('E'. $numrow, $ttl_jumlah);
        $sheet->setCellValue('F'. $numrow, $ttl_nominal);

        // bold kolom
        $sheet->getStyle("A5:F5")
            ->applyFromArray($style_header);
        $sheet->getStyle('A'. $numrow. ':F'. $numrow)
            ->applyFromArray($style_header);

        // border kolom
        $sheet->getStyle("A5:F".$numrow)
            ->applyFromArray(
                array(
                    'borders' => array(
                        'allborders' => array(
                            'style' => PHPExcel_Style_Border::BORDER_THIN
                        )
                    )
                )
            );
        
        // set align column
        $sheet->getStyle('B5'. ':B'.$numrow)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
        $sheet->getStyle('C5'. ':C'.$numrow)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
        $sheet->getStyle('D5'. ':D'.$numrow)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
        $sheet->getStyle('F6'. ':F'.$numrow)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

        // format number column
        $sheet->getStyle('F6:F'. $numrow)->getNumberFormat()->setFormatCode('#,##0');
        
        // Set height semua kolom menjadi auto (mengikuti height isi dari kolommnya, jadi otomatis)
        $sheet->getDefaultRowDimension()->setRowHeight(-1);
        // Set orientasi kertas jadi LANDSCAPE
        $sheet->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
    }

    # 6. TAB KELURAHAN
    private function _excel_tab_kelurahan($excel, $tahun="0000", $bulan="00", $id_group_m="all", $nama_group="-"){
        $excel->createSheet();
        $excel->setActiveSheetIndex(5);
        $sheet = $excel->getActiveSheet();

        // setup sheet
        $sheet->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $sheet->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
        $sheet->setTitle("Kelurahan");

        // 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)
            ),
        );
        
        // set width
        $sheet->getColumnDimension('A')->setWidth(5);
        $sheet->getColumnDimension('B')->setWidth(40);
        $sheet->getColumnDimension('C')->setWidth(40);
        $sheet->getColumnDimension('D')->setWidth(40);
        $sheet->getColumnDimension('E')->setWidth(40);
        $sheet->getColumnDimension('F')->setWidth(12);
        $sheet->getColumnDimension('G')->setWidth(20);
        
        // start header
        $sheet->setCellValue('A1', "DEMOGRAFI PELANGGAN BERDASARKAN KELURAHAN"); 
        $sheet->mergeCells('A1:G1'); 
        $sheet->getStyle('A1')->getFont()->setBold(TRUE)->setSize(11); 
        $sheet->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); 
        
        if($bulan == "all"){
            $nama_bulan = '';
        }else{
            $nama_bulan    = $this->fungsi->konversi_bulan($bulan);
            $nama_bulan    = ' BULAN '. strtoupper($nama_bulan);
        }
        
        $sheet->setCellValue('A2', 'TAHUN '. $tahun. $nama_bulan); 
        $sheet->mergeCells('A2:G2'); 
        $sheet->getStyle('A2')->getFont()->setBold(TRUE)->setSize(11); 
        $sheet->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $sheet->setCellValue('A3', $nama_group);
        $sheet->mergeCells('A3:G3'); 
        $sheet->getStyle('A3')->getFont()->setBold(TRUE)->setSize(11); 
        $sheet->getStyle('A3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $sheet->setCellValue('A5', "NO"); 
        $sheet->setCellValue('B5', "PROVINSI"); 
        $sheet->setCellValue('C5', "KOTA"); 
        $sheet->setCellValue('D5', "KECAMATAN"); 
        $sheet->setCellValue('E5', "KELURAHAN"); 
        $sheet->setCellValue('F5', "JUMLAH"); 
        $sheet->setCellValue('G5', "NOMINAL"); 
        // end header

        // fill data
        $tahun = $this->input->get('tahun');
        $bulan = $this->input->get('bulan');
        $list  = $this->Kelurahan_m->get_datatables($tahun, $bulan, $id_group_m, "all");

        $no          = 1;
        $numrow      = 6;
        $ttl_jumlah  = 0;
        $ttl_nominal = 0;
        foreach($list as $field){
            $sheet->setCellValue('A'.$numrow, $no);
            $sheet->setCellValue('B'.$numrow, $field->nama_provinsi);
            $sheet->setCellValue('C'.$numrow, $field->nama_kota);
            $sheet->setCellValue('D'.$numrow, $field->nama_kecamatan);
            $sheet->setCellValue('E'.$numrow, $field->nama_kelurahan);
            $sheet->setCellValue('F'.$numrow, $field->jumlah);
            $sheet->setCellValue('G'.$numrow, (int)$field->nominal);

            $no++; 
            $numrow++;
            $ttl_jumlah  += $field->jumlah;
            $ttl_nominal += (int)$field->nominal;
        }

        // footer total
        $sheet->mergeCells('A'. $numrow. ':E'. $numrow);
        $sheet->setCellValue('A'. $numrow, "GRAND TOTAL");
        $sheet->setCellValue('F'. $numrow, $ttl_jumlah);
        $sheet->setCellValue('G'. $numrow, $ttl_nominal);

        // bold kolom
        $sheet->getStyle("A5:G5")
            ->applyFromArray($style_header);
        $sheet->getStyle('A'. $numrow. ':G'. $numrow)
            ->applyFromArray($style_header);

        // border kolom
        $sheet->getStyle("A5:G".$numrow)
            ->applyFromArray(
                array(
                    'borders' => array(
                        'allborders' => array(
                            'style' => PHPExcel_Style_Border::BORDER_THIN
                        )
                    )
                )
            );
        
        // set align column
        $sheet->getStyle('B5'. ':B'.$numrow)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
        $sheet->getStyle('C5'. ':C'.$numrow)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
        $sheet->getStyle('D5'. ':D'.$numrow)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
        $sheet->getStyle('E5'. ':E'.$numrow)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
        $sheet->getStyle('G6'. ':G'.$numrow)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

        // format number column
        $sheet->getStyle('G6:G'. $numrow)->getNumberFormat()->setFormatCode('#,##0');
        
        // Set height semua kolom menjadi auto (mengikuti height isi dari kolommnya, jadi otomatis)
        $sheet->getDefaultRowDimension()->setRowHeight(-1);
        // Set orientasi kertas jadi LANDSCAPE
        $sheet->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
    }

    # 7. TAB JENIS PELANGGAN
    private function _excel_tab_jenis_pelanggan($excel, $tahun="0000", $bulan="00", $id_group_m="all", $nama_group="-"){
        $excel->createSheet();
        $excel->setActiveSheetIndex(6);
        $sheet = $excel->getActiveSheet();

        // setup sheet
        $sheet->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $sheet->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
        $sheet->setTitle("Jenis Pelanggan");

        // 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)
            ),
        );
        
        // set width
        $sheet->getColumnDimension('A')->setWidth(5);
        $sheet->getColumnDimension('B')->setWidth(70);
        $sheet->getColumnDimension('C')->setWidth(12);
        $sheet->getColumnDimension('D')->setWidth(20);
        
        // start header
        $sheet->setCellValue('A1', "DEMOGRAFI PELANGGAN BERDASARKAN JENIS PELANGGAN"); 
        $sheet->mergeCells('A1:D1'); 
        $sheet->getStyle('A1')->getFont()->setBold(TRUE)->setSize(11); 
        $sheet->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); 
        
        if($bulan == "all"){
            $nama_bulan = '';
        }else{
            $nama_bulan    = $this->fungsi->konversi_bulan($bulan);
            $nama_bulan    = ' BULAN '. strtoupper($nama_bulan);
        }
        
        $sheet->setCellValue('A2', 'TAHUN '. $tahun. $nama_bulan); 
        $sheet->mergeCells('A2:D2'); 
        $sheet->getStyle('A2')->getFont()->setBold(TRUE)->setSize(11); 
        $sheet->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $sheet->setCellValue('A3', $nama_group);
        $sheet->mergeCells('A3:D3'); 
        $sheet->getStyle('A3')->getFont()->setBold(TRUE)->setSize(11); 
        $sheet->getStyle('A3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $sheet->setCellValue('A5', "NO"); 
        $sheet->setCellValue('B5', "JENIS PELANGGAN"); 
        $sheet->setCellValue('C5', "JUMLAH"); 
        $sheet->setCellValue('D5', "NOMINAL"); 
        // end header

        // fill data
        $tahun = $this->input->get('tahun');
        $bulan = $this->input->get('bulan');
        $list  = $this->Jenis_pelanggan_m->get_datatables($tahun, $bulan, $id_group_m, "all");

        $no          = 1;
        $numrow      = 6;
        $ttl_jumlah  = 0;
        $ttl_nominal = 0;
        foreach($list as $field){
            $sheet->setCellValue('A'.$numrow, $no);
            $sheet->setCellValue('B'.$numrow, ucfirst($field->kategori_pelanggan));
            $sheet->setCellValue('C'.$numrow, $field->jumlah);
            $sheet->setCellValue('D'.$numrow, (int)$field->nominal);

            $no++; 
            $numrow++;
            $ttl_jumlah  += $field->jumlah;
            $ttl_nominal += (int)$field->nominal;
        }

        // footer total
        $sheet->mergeCells('A'. $numrow. ':B'. $numrow);
        $sheet->setCellValue('A'. $numrow, "GRAND TOTAL");
        $sheet->setCellValue('C'. $numrow, $ttl_jumlah);
        $sheet->setCellValue('D'. $numrow, $ttl_nominal);

        // bold kolom
        $sheet->getStyle("A5:D5")
            ->applyFromArray($style_header);
        $sheet->getStyle('A'. $numrow. ':D'. $numrow)
            ->applyFromArray($style_header);

        // border kolom
        $sheet->getStyle("A5:D".$numrow)
            ->applyFromArray(
                array(
                    'borders' => array(
                        'allborders' => array(
                            'style' => PHPExcel_Style_Border::BORDER_THIN
                        )
                    )
                )
            );
        
        // set align column
        $sheet->getStyle('B5'. ':B'.$numrow)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
        $sheet->getStyle('D6'. ':D'.$numrow)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

        // format number column
        $sheet->getStyle('D6:D'. $numrow)->getNumberFormat()->setFormatCode('#,##0');
        
        // Set height semua kolom menjadi auto (mengikuti height isi dari kolommnya, jadi otomatis)
        $sheet->getDefaultRowDimension()->setRowHeight(-1);
        // Set orientasi kertas jadi LANDSCAPE
        $sheet->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
    }

    # 8. TAB JENIS PERUSAHAAN
    private function _excel_tab_jenis_perusahaan($excel, $tahun="0000", $bulan="00", $id_group_m="all", $nama_group="-"){
        $excel->createSheet();
        $excel->setActiveSheetIndex(7);
        $sheet = $excel->getActiveSheet();

        // setup sheet
        $sheet->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $sheet->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
        $sheet->setTitle("Jenis Perusahaan");

        // 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)
            ),
        );
        
        // set width
        $sheet->getColumnDimension('A')->setWidth(5);
        $sheet->getColumnDimension('B')->setWidth(70);
        $sheet->getColumnDimension('C')->setWidth(12);
        $sheet->getColumnDimension('D')->setWidth(20);
        
        // start header
        $sheet->setCellValue('A1', "DEMOGRAFI PELANGGAN BERDASARKAN JENIS PERUSAHAAN"); 
        $sheet->mergeCells('A1:D1'); 
        $sheet->getStyle('A1')->getFont()->setBold(TRUE)->setSize(11); 
        $sheet->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); 
        
        if($bulan == "all"){
            $nama_bulan = '';
        }else{
            $nama_bulan    = $this->fungsi->konversi_bulan($bulan);
            $nama_bulan    = ' BULAN '. strtoupper($nama_bulan);
        }
        
        $sheet->setCellValue('A2', 'TAHUN '. $tahun. $nama_bulan); 
        $sheet->mergeCells('A2:D2'); 
        $sheet->getStyle('A2')->getFont()->setBold(TRUE)->setSize(11); 
        $sheet->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $sheet->setCellValue('A3', $nama_group);
        $sheet->mergeCells('A3:D3'); 
        $sheet->getStyle('A3')->getFont()->setBold(TRUE)->setSize(11); 
        $sheet->getStyle('A3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $sheet->setCellValue('A5', "NO"); 
        $sheet->setCellValue('B5', "JENIS PERUSAHAAN"); 
        $sheet->setCellValue('C5', "JUMLAH"); 
        $sheet->setCellValue('D5', "NOMINAL"); 
        // end header

        // fill data
        $tahun = $this->input->get('tahun');
        $bulan = $this->input->get('bulan');
        $list  = $this->Jenis_perusahaan_m->get_datatables($tahun, $bulan, $id_group_m, "all");

        $no          = 1;
        $numrow      = 6;
        $ttl_jumlah  = 0;
        $ttl_nominal = 0;
        foreach($list as $field){
            $sheet->setCellValue('A'.$numrow, $no);
            $sheet->setCellValue('B'.$numrow, ucfirst($field->nama_jenis_perusahaan));
            $sheet->setCellValue('C'.$numrow, $field->jumlah);
            $sheet->setCellValue('D'.$numrow, (int)$field->nominal);

            $no++; 
            $numrow++;
            $ttl_jumlah  += $field->jumlah;
            $ttl_nominal += (int)$field->nominal;
        }

        // footer total
        $sheet->mergeCells('A'. $numrow. ':B'. $numrow);
        $sheet->setCellValue('A'. $numrow, "GRAND TOTAL");
        $sheet->setCellValue('C'. $numrow, $ttl_jumlah);
        $sheet->setCellValue('D'. $numrow, $ttl_nominal);

        // bold kolom
        $sheet->getStyle("A5:D5")
            ->applyFromArray($style_header);
        $sheet->getStyle('A'. $numrow. ':D'. $numrow)
            ->applyFromArray($style_header);

        // border kolom
        $sheet->getStyle("A5:D".$numrow)
            ->applyFromArray(
                array(
                    'borders' => array(
                        'allborders' => array(
                            'style' => PHPExcel_Style_Border::BORDER_THIN
                        )
                    )
                )
            );
        
        // set align column
        $sheet->getStyle('B5'. ':B'.$numrow)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
        $sheet->getStyle('D6'. ':D'.$numrow)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

        // format number column
        $sheet->getStyle('D6:D'. $numrow)->getNumberFormat()->setFormatCode('#,##0');
        
        // Set height semua kolom menjadi auto (mengikuti height isi dari kolommnya, jadi otomatis)
        $sheet->getDefaultRowDimension()->setRowHeight(-1);
        // Set orientasi kertas jadi LANDSCAPE
        $sheet->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
    }

    # 9. TAB JENIS PERORANGAN
    private function _excel_tab_jenis_perorangan($excel, $tahun="0000", $bulan="00", $id_group_m="all", $nama_group="-"){
        $excel->createSheet();
        $excel->setActiveSheetIndex(8);
        $sheet = $excel->getActiveSheet();

        // setup sheet
        $sheet->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $sheet->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
        $sheet->setTitle("Jenis Perorangan");

        // 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)
            ),
        );
        
        // set width
        $sheet->getColumnDimension('A')->setWidth(5);
        $sheet->getColumnDimension('B')->setWidth(70);
        $sheet->getColumnDimension('C')->setWidth(12);
        $sheet->getColumnDimension('D')->setWidth(20);
        
        // start header
        $sheet->setCellValue('A1', "DEMOGRAFI PELANGGAN BERDASARKAN JENIS PERORANGAN"); 
        $sheet->mergeCells('A1:D1'); 
        $sheet->getStyle('A1')->getFont()->setBold(TRUE)->setSize(11); 
        $sheet->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); 
        
        if($bulan == "all"){
            $nama_bulan = '';
        }else{
            $nama_bulan    = $this->fungsi->konversi_bulan($bulan);
            $nama_bulan    = ' BULAN '. strtoupper($nama_bulan);
        }
        
        $sheet->setCellValue('A2', 'TAHUN '. $tahun. $nama_bulan); 
        $sheet->mergeCells('A2:D2'); 
        $sheet->getStyle('A2')->getFont()->setBold(TRUE)->setSize(11); 
        $sheet->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $sheet->setCellValue('A3', $nama_group);
        $sheet->mergeCells('A3:D3'); 
        $sheet->getStyle('A3')->getFont()->setBold(TRUE)->setSize(11); 
        $sheet->getStyle('A3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $sheet->setCellValue('A5', "NO"); 
        $sheet->setCellValue('B5', "JENIS PERORANGAN"); 
        $sheet->setCellValue('C5', "JUMLAH"); 
        $sheet->setCellValue('D5', "NOMINAL"); 
        // end header

        // fill data
        $tahun = $this->input->get('tahun');
        $bulan = $this->input->get('bulan');
        $list  = $this->Jenis_perorangan_m->get_datatables($tahun, $bulan, $id_group_m, "all");

        $no          = 1;
        $numrow      = 6;
        $ttl_jumlah  = 0;
        $ttl_nominal = 0;
        foreach($list as $field){
            $sheet->setCellValue('A'.$numrow, $no);
            $sheet->setCellValue('B'.$numrow, ucfirst($field->nama_jenis_perusahaan));
            $sheet->setCellValue('C'.$numrow, $field->jumlah);
            $sheet->setCellValue('D'.$numrow, (int)$field->nominal);

            $no++; 
            $numrow++;
            $ttl_jumlah  += $field->jumlah;
            $ttl_nominal += (int)$field->nominal;
        }

        // footer total
        $sheet->mergeCells('A'. $numrow. ':B'. $numrow);
        $sheet->setCellValue('A'. $numrow, "GRAND TOTAL");
        $sheet->setCellValue('C'. $numrow, $ttl_jumlah);
        $sheet->setCellValue('D'. $numrow, $ttl_nominal);

        // bold kolom
        $sheet->getStyle("A5:D5")
            ->applyFromArray($style_header);
        $sheet->getStyle('A'. $numrow. ':D'. $numrow)
            ->applyFromArray($style_header);

        // border kolom
        $sheet->getStyle("A5:D".$numrow)
            ->applyFromArray(
                array(
                    'borders' => array(
                        'allborders' => array(
                            'style' => PHPExcel_Style_Border::BORDER_THIN
                        )
                    )
                )
            );
        
        // set align column
        $sheet->getStyle('B5'. ':B'.$numrow)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
        $sheet->getStyle('D6'. ':D'.$numrow)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

        // format number column
        $sheet->getStyle('D6:D'. $numrow)->getNumberFormat()->setFormatCode('#,##0');
        
        // Set height semua kolom menjadi auto (mengikuti height isi dari kolommnya, jadi otomatis)
        $sheet->getDefaultRowDimension()->setRowHeight(-1);
        // Set orientasi kertas jadi LANDSCAPE
        $sheet->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
    }

    # 10. TAB GROUP PRODUCT
    private function _excel_tab_group_product($excel, $tahun="0000", $bulan="00", $id_group_m="all", $nama_group="-"){
        $excel->createSheet();
        $excel->setActiveSheetIndex(9);
        $sheet = $excel->getActiveSheet();

        // setup sheet
        $sheet->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $sheet->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
        $sheet->setTitle("Group Product");

        // 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)
            ),
        );
        
        // set width
        $sheet->getColumnDimension('A')->setWidth(5);
        $sheet->getColumnDimension('B')->setWidth(70);
        $sheet->getColumnDimension('C')->setWidth(12);
        $sheet->getColumnDimension('D')->setWidth(20);
        
        // start header
        $sheet->setCellValue('A1', "DEMOGRAFI PELANGGAN BERDASARKAN GROUP PRODUCT"); 
        $sheet->mergeCells('A1:D1'); 
        $sheet->getStyle('A1')->getFont()->setBold(TRUE)->setSize(11); 
        $sheet->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); 
        
        if($bulan == "all"){
            $nama_bulan = '';
        }else{
            $nama_bulan    = $this->fungsi->konversi_bulan($bulan);
            $nama_bulan    = ' BULAN '. strtoupper($nama_bulan);
        }
        
        $sheet->setCellValue('A2', 'TAHUN '. $tahun. $nama_bulan); 
        $sheet->mergeCells('A2:D2'); 
        $sheet->getStyle('A2')->getFont()->setBold(TRUE)->setSize(11); 
        $sheet->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $sheet->setCellValue('A3', $nama_group);
        $sheet->mergeCells('A3:D3'); 
        $sheet->getStyle('A3')->getFont()->setBold(TRUE)->setSize(11); 
        $sheet->getStyle('A3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $sheet->setCellValue('A5', "NO"); 
        $sheet->setCellValue('B5', "GROUP PRODUCT"); 
        $sheet->setCellValue('C5', "JUMLAH"); 
        $sheet->setCellValue('D5', "NOMINAL"); 
        // end header

        // fill data
        $tahun = $this->input->get('tahun');
        $bulan = $this->input->get('bulan');
        $list  = $this->Grup_produk_m->get_datatables($tahun, $bulan, $id_group_m, "all");

        $no          = 1;
        $numrow      = 6;
        $ttl_jumlah  = 0;
        $ttl_nominal = 0;
        foreach($list as $field){
            $sheet->setCellValue('A'.$numrow, $no);
            $sheet->setCellValue('B'.$numrow, ucfirst($field->nama_product_group));
            $sheet->setCellValue('C'.$numrow, $field->jumlah);
            $sheet->setCellValue('D'.$numrow, (int)$field->nominal);

            $no++; 
            $numrow++;
            $ttl_jumlah  += $field->jumlah;
            $ttl_nominal += (int)$field->nominal;
        }

        // footer total
        $sheet->mergeCells('A'. $numrow. ':B'. $numrow);
        $sheet->setCellValue('A'. $numrow, "GRAND TOTAL");
        $sheet->setCellValue('C'. $numrow, $ttl_jumlah);
        $sheet->setCellValue('D'. $numrow, $ttl_nominal);

        // bold kolom
        $sheet->getStyle("A5:D5")
            ->applyFromArray($style_header);
        $sheet->getStyle('A'. $numrow. ':D'. $numrow)
            ->applyFromArray($style_header);

        // border kolom
        $sheet->getStyle("A5:D".$numrow)
            ->applyFromArray(
                array(
                    'borders' => array(
                        'allborders' => array(
                            'style' => PHPExcel_Style_Border::BORDER_THIN
                        )
                    )
                )
            );
        
        // set align column
        $sheet->getStyle('B5'. ':B'.$numrow)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
        $sheet->getStyle('D6'. ':D'.$numrow)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

        // format number column
        $sheet->getStyle('D6:D'. $numrow)->getNumberFormat()->setFormatCode('#,##0');
        
        // Set height semua kolom menjadi auto (mengikuti height isi dari kolommnya, jadi otomatis)
        $sheet->getDefaultRowDimension()->setRowHeight(-1);
        // Set orientasi kertas jadi LANDSCAPE
        $sheet->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
    }

    # 11. TAB PRODUCT
    private function _excel_tab_product($excel, $tahun="0000", $bulan="00", $id_group_m="all", $nama_group="-"){
        $excel->createSheet();
        $excel->setActiveSheetIndex(10);
        $sheet = $excel->getActiveSheet();

        // setup sheet
        $sheet->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $sheet->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
        $sheet->setTitle("Product");

        // 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)
            ),
        );
        
        // set width
        $sheet->getColumnDimension('A')->setWidth(5);
        $sheet->getColumnDimension('B')->setWidth(50);
        $sheet->getColumnDimension('C')->setWidth(50);
        $sheet->getColumnDimension('D')->setWidth(12);
        $sheet->getColumnDimension('E')->setWidth(20);
        
        // start header
        $sheet->setCellValue('A1', "DEMOGRAFI PELANGGAN BERDASARKAN PRODUCT"); 
        $sheet->mergeCells('A1:E1'); 
        $sheet->getStyle('A1')->getFont()->setBold(TRUE)->setSize(11); 
        $sheet->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); 
        
        if($bulan == "all"){
            $nama_bulan = '';
        }else{
            $nama_bulan    = $this->fungsi->konversi_bulan($bulan);
            $nama_bulan    = ' BULAN '. strtoupper($nama_bulan);
        }
        
        $sheet->setCellValue('A2', 'TAHUN '. $tahun. $nama_bulan); 
        $sheet->mergeCells('A2:E2'); 
        $sheet->getStyle('A2')->getFont()->setBold(TRUE)->setSize(11); 
        $sheet->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $sheet->setCellValue('A3', $nama_group);
        $sheet->mergeCells('A3:E3'); 
        $sheet->getStyle('A3')->getFont()->setBold(TRUE)->setSize(11); 
        $sheet->getStyle('A3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $sheet->setCellValue('A5', "NO"); 
        $sheet->setCellValue('B5', "GROUP PRODUCT"); 
        $sheet->setCellValue('C5', "PRODUCT"); 
        $sheet->setCellValue('D5', "JUMLAH"); 
        $sheet->setCellValue('E5', "NOMINAL"); 
        // end header

        // fill data
        $tahun = $this->input->get('tahun');
        $bulan = $this->input->get('bulan');
        $list  = $this->Product_m->get_datatables($tahun, $bulan, $id_group_m, "all");

        $no          = 1;
        $numrow      = 6;
        $ttl_jumlah  = 0;
        $ttl_nominal = 0;
        foreach($list as $field){
            $sheet->setCellValue('A'.$numrow, $no);
            $sheet->setCellValue('B'.$numrow, ucfirst($field->nama_product_group));
            $sheet->setCellValue('C'.$numrow, ucfirst($field->nama_product));
            $sheet->setCellValue('D'.$numrow, $field->jumlah);
            $sheet->setCellValue('E'.$numrow, (int)$field->nominal);

            $no++; 
            $numrow++;
            $ttl_jumlah  += $field->jumlah;
            $ttl_nominal += (int)$field->nominal;
        }

        // footer total
        $sheet->mergeCells('A'. $numrow. ':C'. $numrow);
        $sheet->setCellValue('A'. $numrow, "GRAND TOTAL");
        $sheet->setCellValue('D'. $numrow, $ttl_jumlah);
        $sheet->setCellValue('E'. $numrow, $ttl_nominal);

        // bold kolom
        $sheet->getStyle("A5:E5")
            ->applyFromArray($style_header);
        $sheet->getStyle('A'. $numrow. ':E'. $numrow)
            ->applyFromArray($style_header);

        // border kolom
        $sheet->getStyle("A5:E".$numrow)
            ->applyFromArray(
                array(
                    'borders' => array(
                        'allborders' => array(
                            'style' => PHPExcel_Style_Border::BORDER_THIN
                        )
                    )
                )
            );
        
        // set align column
        $sheet->getStyle('B5'. ':B'.$numrow)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
        $sheet->getStyle('C5'. ':C'.$numrow)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
        $sheet->getStyle('E6'. ':E'.$numrow)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

        // format number column
        $sheet->getStyle('E6:E'. $numrow)->getNumberFormat()->setFormatCode('#,##0');
        
        // Set height semua kolom menjadi auto (mengikuti height isi dari kolommnya, jadi otomatis)
        $sheet->getDefaultRowDimension()->setRowHeight(-1);
        // Set orientasi kertas jadi LANDSCAPE
        $sheet->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
    }

    // public function dropdown_bulan()
    // {
    //     $json = [
    //         ['id' => 1, 'text' => 'Januari'],
    //         ['id' => 2, 'text' => 'Februari'],
    //         ['id' => 3, 'text' => 'Maret'],
    //         ['id' => 4, 'text' => 'April'],
    //         ['id' => 5, 'text' => 'Mei'],
    //         ['id' => 6, 'text' => 'Juni'],
    //         ['id' => 7, 'text' => 'Juli'],
    //         ['id' => 8, 'text' => 'Agustus'],
    //         ['id' => 9, 'text' => 'September'],
    //         ['id' => 10, 'text' => 'Oktober'],
    //         ['id' => 11, 'text' => 'November'],
    //         ['id' => 12, 'text' => 'Desember']
    //     ];
        
    //     $search = $this->input->get("q");
    //     if ($search) {
    //         $json = array_filter($json, function($item) use ($search) {
    //             return stripos($item['text'], $search) !== false;
    //         });
    //     }
    
    //     echo json_encode(array_values($json));
    // }
}