<?php

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

class Detail extends MY_Controller {
    public function __construct(){
		parent::__construct();
        $this->load->model('kpi/user/approval/Aspek_kepribadian_m');
        $this->load->model('kpi/user/approval/Nilai_individu_m');
        $this->load->model('Karyawan_m');
        $this->load->helper('kpi/kpi_helper');
	}

    public function view_detail($id=""){
        $data['id']   = $id;
        $data['data'] = detailData($id);
        $this->load->view('kpi/admin/laporan/detail/home', $data);
    }

    public function tabs($tabs = null, $id= ""){
        // $data['data'] = detailData($id);

        switch ($tabs) {
            case "aspek_kinerja" :
                $this->load->view('kpi/admin/laporan/detail/aspek_kinerja');
                break;
            case "aspek_kepribadian" :
                $this->load->view('kpi/admin/laporan/detail/aspek_kepribadian');
                break;
            case "aspek_kedisiplinan" :
                $this->load->view('kpi/admin/laporan/detail/aspek_kedisiplinan');
                break;
            case "nilai_individu" :
                $this->load->view('kpi/admin/laporan/detail/nilai_individu');
                break;
            default:
                echo $this->lang->line('data_tidak_ditemukan');
        }
    }

    public function download($id) {
        /** Error reporting */
        // error_reporting(E_ALL);
        // ini_set('display_errors', TRUE);
        // ini_set('display_startup_errors', TRUE);
        date_default_timezone_set('Europe/London');

        if (PHP_SAPI == 'cli')
            die('This example should only be run from a Web Browser');

        require_once(APPPATH.'libraries/PHPExcel.php');

        $data['detail']            = detailData($id);
        if(!in_array($data['detail']['status'], ['4', '9'])) {
            echo "KPI belum diapprove";
            exit;
        };

        // Get Company
        $company = $this->db->select('nama, url_logo')
                        ->from('master_company')
                        ->where('id_company', $this->id_company)
                        ->get()
                        ->row();

        
        $data['company']           = $company;
        $data['skor']              = $this->dataSkor($data['detail']['tahun'], $data['detail']['bulan'], $data['detail']['data_karyawan']->id_karyawan);
        $data['atasan']            = $this->namaAtasan($data['detail']['tahun'], $data['detail']['bulan'], $data['detail']['data_karyawan']->id_karyawan);
        $data['personalSkor']      = $this->Nilai_individu_m->getPersonalSkor($data['detail']['data_karyawan']->id_karyawan, $data['detail']['tahun'], $data['detail']['bulan']);
        $data['performanceTarget'] = $this->detail_m->download($data['detail']['data_karyawan']->id_karyawan, $data['detail']['tahun'], $data['detail']['bulan']);
        $data['aspekKepribadian']  = $this->Aspek_kepribadian_m->download($data['detail']['data_karyawan']->id_karyawan, $data['detail']['tahun'], $data['detail']['bulan'], $data['detail']['data_karyawan']->id_jabatan);
        $data['bobotPerformance']  = $this->Nilai_individu_m->getBobotKinerja($data['detail']['data_karyawan']->id_karyawan, $data['detail']['tahun'], $data['detail']['bulan'], $data['detail']['data_karyawan']->id_jabatan);
        $data['bobotPribadi']      = $this->Nilai_individu_m->getBobotPribadi($data['detail']['data_karyawan']->id_karyawan, $data['detail']['tahun'], $data['detail']['bulan'], $data['detail']['data_karyawan']->id_jabatan);
        $data['dataKedisiplinan']  = $this->detail_m->get_data_kedisiplinan($data['detail']['data_karyawan']->id_karyawan, $data['detail']['tahun'], $data['detail']['bulan']);
        $content                   = $this->load->view('kpi/admin/laporan/detail/download', $data, true);

        $tmpfile = tempnam(sys_get_temp_dir(), 'html');
        file_put_contents($tmpfile, $content);
        
        $objPHPExcel = new PHPExcel();

        $excelHTMLReader = PHPExcel_IOFactory::createReader('HTML');
        $excelHTMLReader->loadIntoExisting($tmpfile, $objPHPExcel);
        $mergeCells = $objPHPExcel->getActiveSheet()->getMergeCells();

        unlink($tmpfile);

        // set row height
        $highestRow    = $objPHPExcel->getActiveSheet()->getHighestRow();
        $highestColumn = $objPHPExcel->getActiveSheet()->getHighestColumn();
        for($i = 1; $i <= $highestRow; $i++) {
            $objPHPExcel->getActiveSheet()->getRowDimension($i)->setRowHeight(12);
        }

        // set font size
        $objPHPExcel->getActiveSheet()->getStyle('A1:'.$highestColumn.$highestRow)->getFont()->setSize(10);
        $objPHPExcel->getActiveSheet()->getStyle('A1:'.$highestColumn.$highestRow)->getAlignment()->setWrapText(true);
        $styleBorderNone = [
            'borders' => [
                'allborders' => [
                    'style' => PHPExcel_Style_Border::BORDER_THIN,
                    'color' => ['rgb' => 'FFFFFF']
                ]
            ]
        ];
        $objPHPExcel->getActiveSheet()->getStyle('A1:Z200')->applyFromArray($styleBorderNone);

        // set border
        $startRange   = $endRange = "";
        $table        = 1;
        $dataExcel    = $objPHPExcel->getActiveSheet()->toArray();
        $paramFormula = "";
        $ttl_skor_index = 1;
        foreach($dataExcel as $rowIndex => $row) {
            if(!array_filter($row)) {
                if($startRange != "" and $endRange != "") {
                    $styleBorder = [
                        'borders' => [
                            'allborders' => [
                                'style' => PHPExcel_Style_Border::BORDER_THIN,
                                'color' => ['rgb' => '000000']
                            ]
                        ]
                    ];

                    $range        = "$startRange:$endRange";
                    if($table == 1) {
                        $styleBorder['font'] = [ 'name' => 'Trebuchet MS', 'size' => 10 ];
                        $objPHPExcel->getActiveSheet()->getStyle($range)->applyFromArray($styleBorder);
                    }elseif($table == 2) {
                        foreach($mergeCells as $rangeMerge) {
                            // if(str_contains($rangeMerge, $startRange)) {
                            //     $range = $rangeMerge;
                            //     break;
                            // }
                        }

                        $styleBorder_2['font']      = [ 'size' => 12, 'bold' => true ];
                        $styleBorder_2['alignment'] = [ 'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER ];
                        
                        $objPHPExcel->getActiveSheet()->getStyle($range)->applyFromArray($styleBorder_2);
                    } elseif($table == 3) {
                        $styleBorder['font'] = [ 'size' => 10, 'bold' => true ];
                        $objPHPExcel->getActiveSheet()->getStyle($range)->applyFromArray($styleBorder);
                        // format currency
                        $cellCurrency = $highestColumn.((int)str_replace($highestColumn, "", $endRange)-1);
                        $objPHPExcel->getActiveSheet()->getStyle($cellCurrency)->getNumberFormat()->setFormatCode('#,##0.00');
                    } elseif($table == 4) {
                        $style = [
                            'font' => [ 'bold' => true]
                        ];
                        $objPHPExcel->getActiveSheet()->getStyle($range)->applyFromArray($style);
                    } elseif(in_array($table, [6, 8])) {
                        // set header bold, center, and background blue
                        $rowAwal  = (int)str_replace("B", "", $startRange);
                        $rowAkhir = $rowAwal+1;
                        $objPHPExcel->getActiveSheet()->getStyle("B".$rowAwal.":".$highestColumn.$rowAkhir)->getFont()->setBold(true);
                        $objPHPExcel->getActiveSheet()->getStyle("B".$rowAwal.":".$highestColumn.$rowAkhir)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                        if($table == 6) {
                            $objPHPExcel->getActiveSheet()->getStyle("C".$rowAwal.":D".$rowAkhir)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('bdd7ee');
                        }
                        
                        $rowAkhir = str_replace("F", "", $endRange);
                        if($table == 8) {
                            $persen_bobot_performance = $data['bobotPerformance']/100;
                            $persen_bobot_pribadi     = $data['bobotPribadi']/100;
                            $sumValue        = $paramFormula."*".$persen_bobot_performance."+".$highestColumn.$rowAkhir."*".$persen_bobot_pribadi."+".$data['personalSkor']['disiplin'];
                            $rumusGrade      = "=IFS(";
                            $rumusKeterangan = "=IFS(";
                            foreach(json_decode($data['skor']) as $row) {
                                if($row->grade == 'A') {
                                    $rumusGrade      .= $sumValue.' >= '.$row->batas_bawah.', "'.$row->grade.'"';
                                    $rumusKeterangan .= $sumValue.' >= '.$row->batas_bawah.', "'.$row->keterangan.'"';
                                } elseif($row->grade == 'D') {
                                    $rumusGrade      .= ', '.$sumValue.' >= '.$row->batas_bawah.', "'.$row->grade.'")';
                                    $rumusKeterangan .= ', '.$sumValue.' >= '.$row->batas_bawah.', "'.$row->keterangan.'")';
                                } else {
                                    $rumusGrade      .= ', '.$sumValue.' >= '.$row->batas_bawah.', "'.$row->grade.'"';
                                    $rumusKeterangan .= ', '.$sumValue.' >= '.$row->batas_bawah.', "'.$row->keterangan.'"';
                                }
                            }
                            
                            // $objPHPExcel->getActiveSheet()->setCellValue("L14", $rumusGrade);
                            // $objPHPExcel->getActiveSheet()->setCellValue("L15", $rumusKeterangan);
                            // $objPHPExcel->getActiveSheet()->setCellValueExplicit("L14", $rumusGrade, PHPExcel_Cell_DataType::TYPE_STRING);
                            // $objPHPExcel->getActiveSheet()->setCellValueExplicit("L15", $rumusKeterangan, PHPExcel_Cell_DataType::TYPE_STRING);
                            $objPHPExcel->getActiveSheet()->setCellValue("L14", $data['personalSkor']['skor_ttl']);
                            $objPHPExcel->getActiveSheet()->setCellValue("L15", $data['personalSkor']['predikat']. " - " .$data['personalSkor']['remark']);

                        } else {
                            $paramFormula = $highestColumn.$rowAkhir;
                        }
                        $objPHPExcel->getActiveSheet()->getStyle("B".$rowAwal.":B".$rowAkhir)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                        $objPHPExcel->getActiveSheet()->getStyle("B".$rowAkhir.":".$highestColumn.$rowAkhir)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                        $objPHPExcel->getActiveSheet()->getStyle("E".((int)$rowAwal+1).":E".((int)$rowAkhir-1))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
                        $objPHPExcel->getActiveSheet()->getStyle("F".((int)$rowAwal+1).":J".((int)$rowAkhir-1))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

                        for($i = ((int)$rowAwal+2); $i <= ((int)$rowAkhir-1); $i++) {
                            $objPHPExcel->getActiveSheet()->setCellValue("L$i", "=E$i*K$i");
                        }
                        $objPHPExcel->getActiveSheet()->setCellValue("E".$rowAkhir, "=SUM(E".((int)$rowAwal+2).":E".((int)$rowAkhir-1).")");

                        if($ttl_skor_index == 1){
                            $objPHPExcel->getActiveSheet()->setCellValue("L".$rowAkhir, "=SUM(L".((int)$rowAwal+2).":L".((int)$rowAkhir-1).")*". $data['bobotPerformance']. "/100");
                        }else{
                            $objPHPExcel->getActiveSheet()->setCellValue("L".$rowAkhir, "=SUM(L".((int)$rowAwal+2).":L".((int)$rowAkhir-1).")*". $data['bobotPribadi']. "/100");
                            // $objPHPExcel->getActiveSheet()->SetCellValue("L".$rowAkhir)->getStyle()->getNumberFormat()->setFormatCode('0.00');
                        }
                        $ttl_skor_index++;

                        $objPHPExcel->getActiveSheet()->getStyle("E".((int)$rowAwal+1).":E".$rowAkhir)->getNumberFormat()->applyFromArray([
                            "code" => PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE
                        ]);
                        if($table == 8) {
                            $objPHPExcel->getActiveSheet()->getStyle("F".((int)$rowAwal+2).":J".((int)$rowAkhir-1))->getFont()->setSize(8);
                            $objPHPExcel->getActiveSheet()->getStyle("E".((int)$rowAwal+2).":E".((int)$rowAkhir-1))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                            for($i = ((int)$rowAwal+2); $i <= ((int)$rowAkhir-1); $i++) {
                                $objPHPExcel->getActiveSheet()->getRowDimension($i)->setRowHeight(160);
                            }
                        }

                        $objPHPExcel->getActiveSheet()->getRowDimension($rowAkhir)->setRowHeight(24);

                        $objPHPExcel->getActiveSheet()->getStyle(str_replace("F", $highestColumn, $range))->applyFromArray($styleBorder);
                        $objPHPExcel->getActiveSheet()->getStyle("B".$rowAkhir.":".$highestColumn.$rowAkhir)->getFont()->setBold(true);
                    } elseif(!in_array($table, [5, 7])) {
                        $objPHPExcel->getActiveSheet()->getStyle($range)->applyFromArray($styleBorder);
                    }
                    
                    if($table >= 8) {
                        $styleBorder  = [
                            'borders' => [
                                'allborders' => [
                                    'style' => PHPExcel_Style_Border::BORDER_THIN,
                                    'color' => ['rgb' => '000000']
                                ]
                            ]
                        ];
                        $styleBorderOutline  = [
                            'borders' => [
                                'outline' => [
                                    'style' => PHPExcel_Style_Border::BORDER_THIN,
                                    'color' => ['rgb' => '000000']
                                ]
                            ]
                        ];

                        // TABEL KEDISIPLINAN
                        $dis_table_all = "B".((int)str_replace("F", "", $endRange)+4).":L".((int)str_replace("F", "", $endRange)+9);
                        $dis_table_header = "B".((int)str_replace("F", "", $endRange)+4).":L".((int)str_replace("F", "", $endRange)+4);
                        $dis_table_footer = "B".((int)str_replace("F", "", $endRange)+9).":L".((int)str_replace("F", "", $endRange)+9);
                        $objPHPExcel->getActiveSheet()->getStyle($dis_table_all)->applyFromArray($styleBorder);
                        $objPHPExcel->getActiveSheet()->getStyle($dis_table_all)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                        $objPHPExcel->getActiveSheet()->getStyle($dis_table_header)->getFont()->setBold(true);
                        $objPHPExcel->getActiveSheet()->getStyle($dis_table_footer)->getFont()->setBold(true);

                        // TABEL FOOTER & TTD
                        $rangeApv     = "J".((int)str_replace("F", "", $endRange)+12).":L".((int)str_replace("F", "", $endRange)+21);
                        $rangeDev11   = "B".((int)str_replace("F", "", $endRange)+13).":H".((int)str_replace("F", "", $endRange)+13);
                        $rangeDev12   = "B".((int)str_replace("F", "", $endRange)+14).":H".((int)str_replace("F", "", $endRange)+17);
                        $rangeDev13   = "B".((int)str_replace("F", "", $endRange)+18).":H".((int)str_replace("F", "", $endRange)+21);
                        $rangeDev14   = "E".((int)str_replace("F", "", $endRange)+14).":H".((int)str_replace("F", "", $endRange)+21);
                        $rangeExist11 = "B".((int)str_replace("F", "", $endRange)+14).":H".((int)str_replace("F", "", $endRange)+14);
                        $rangeExist12 = "B".((int)str_replace("F", "", $endRange)+15).":H".((int)str_replace("F", "", $endRange)+17);
                        $exist12      = "B".((int)str_replace("F", "", $endRange)+15);
                        $rangeApvBy   = "J".((int)str_replace("F", "", $endRange)+13).":L".((int)str_replace("F", "", $endRange)+14);
                        $rangeApvBy11 = "J".((int)str_replace("F", "", $endRange)+15).":K".((int)str_replace("F", "", $endRange)+19);
                        $rangeApvBy12 = "L".((int)str_replace("F", "", $endRange)+15).":L".((int)str_replace("F", "", $endRange)+19);
                        $apvBy21      = "J".((int)str_replace("F", "", $endRange)+20);
                        $rangeApvBy21 = "J".((int)str_replace("F", "", $endRange)+20).":K".((int)str_replace("F", "", $endRange)+21);
                        $rangeApvBy22 = "L".((int)str_replace("F", "", $endRange)+20).":L".((int)str_replace("F", "", $endRange)+21);
                        $apvBy31      = "J".((int)str_replace("F", "", $endRange)+22);
                        $rangeApvBy31 = "J".((int)str_replace("F", "", $endRange)+22).":K".((int)str_replace("F", "", $endRange)+23);
                        $rangeApvBy32 = "L".((int)str_replace("F", "", $endRange)+22).":L".((int)str_replace("F", "", $endRange)+23);
                        $rangeMS1     = "E".((int)str_replace("F", "", $endRange)+14).":G".((int)str_replace("F", "", $endRange)+17);
                        $rangeTF1     = "H".((int)str_replace("F", "", $endRange)+14).":H".((int)str_replace("F", "", $endRange)+17);
                        $rangeExist21 = "B".((int)str_replace("F", "", $endRange)+18).":H".((int)str_replace("F", "", $endRange)+18);
                        $rangeExist22 = "B".((int)str_replace("F", "", $endRange)+19).":H".((int)str_replace("F", "", $endRange)+21);
                        $exist22      = "B".((int)str_replace("F", "", $endRange)+19);
                        $rangeMS2     = "E".((int)str_replace("F", "", $endRange)+18).":G".((int)str_replace("F", "", $endRange)+21);
                        $rangeTF2     = "H".((int)str_replace("F", "", $endRange)+18).":H".((int)str_replace("F", "", $endRange)+21);
                        $rangeEmply11 = "B".((int)str_replace("F", "", $endRange)+23).":H".((int)str_replace("F", "", $endRange)+23);
                        $emply12      = "B".((int)str_replace("F", "", $endRange)+24);
                        $rangeEmply12 = "B".((int)str_replace("F", "", $endRange)+24).":H".((int)str_replace("F", "", $endRange)+26);

                        $objPHPExcel->getActiveSheet()->getStyle($rangeApv)->applyFromArray($styleBorder);
                        $objPHPExcel->getActiveSheet()->getStyle($rangeDev11)->applyFromArray($styleBorder);
                        $objPHPExcel->getActiveSheet()->getStyle($rangeDev12)->applyFromArray($styleBorderOutline);
                        $objPHPExcel->getActiveSheet()->getStyle($rangeDev13)->applyFromArray($styleBorderOutline);
                        $objPHPExcel->getActiveSheet()->mergeCells($rangeExist11);
                        $objPHPExcel->getActiveSheet()->mergeCells($rangeExist12);
                        $objPHPExcel->getActiveSheet()->setCellValue($exist12, $data['detail']['existing_role']);
                        $objPHPExcel->getActiveSheet()->mergeCells($rangeExist21);
                        $objPHPExcel->getActiveSheet()->mergeCells($rangeExist22);
                        $objPHPExcel->getActiveSheet()->setCellValue($exist22, $data['detail']['career_dev']);
                        $objPHPExcel->getActiveSheet()->mergeCells($rangeEmply12);
                        $objPHPExcel->getActiveSheet()->setCellValue($emply12, $data['detail']['ctt_terima']);
                        $objPHPExcel->getActiveSheet()->mergeCells($rangeApvBy);
                        $objPHPExcel->getActiveSheet()->mergeCells($rangeApvBy11);
                        $objPHPExcel->getActiveSheet()->mergeCells($rangeApvBy12);
                        if($data['detail']['user_approval'] != '') {
                            $apvBy = explode(" - ", $data['detail']['user_approval']);
                            $objPHPExcel->getActiveSheet()->setCellValue($apvBy21, $apvBy[1]);
                            $objPHPExcel->getActiveSheet()->setCellValue($apvBy31, $apvBy[0]);
                        }
                        $objPHPExcel->getActiveSheet()->mergeCells($rangeApvBy21);
                        $objPHPExcel->getActiveSheet()->mergeCells($rangeApvBy22);
                        $objPHPExcel->getActiveSheet()->mergeCells($rangeApvBy31);
                        $objPHPExcel->getActiveSheet()->mergeCells($rangeApvBy32);
                        $objPHPExcel->getActiveSheet()->getStyle($rangeEmply11)->applyFromArray($styleBorder);
                        $objPHPExcel->getActiveSheet()->getStyle($rangeEmply12)->applyFromArray($styleBorderOutline);

                        $objPHPExcel->getActiveSheet()->getStyle("B".((int)str_replace("F", "", $endRange)+4).":".$highestColumn.((int)str_replace("F", "", $endRange)+4))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                        $objPHPExcel->getActiveSheet()->getStyle($rangeApv)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                        $objPHPExcel->getActiveSheet()->getStyle($rangeApvBy)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
                        // $objPHPExcel->getActiveSheet()->getStyle($rangeApvBy21)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                        // $objPHPExcel->getActiveSheet()->getStyle($rangeApvBy31)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                        break;
                    }
                    $table++;

                    $startRange = $endRange = "";
                } else {
                    continue;
                }
            } else {
                foreach($row as $columnIndex => $value) {
                    if($value != NULL) {
                        if($startRange == "") {
                            $startRange = strtoupper($this->al($columnIndex)).($rowIndex+1);
                            $endRange = strtoupper($this->al($columnIndex)).($rowIndex+1);
                        } else {
                            $endRange = strtoupper($this->al($columnIndex)).($rowIndex+1);
                        }
                    }
                }
            }
        }

        // set column width
        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(3);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(30);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(45);
        $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(20);

        // set column center
        $objPHPExcel->getActiveSheet()->getStyle('D1:D'.$highestRow)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('K1:K'.$highestRow)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('L1:L'.$highestRow)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        // set all cell vertical center
        $objPHPExcel->getActiveSheet()->getStyle('A1:'.$highestColumn.$highestRow)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle($exist12)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
        $objPHPExcel->getActiveSheet()->getStyle($exist22)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
        $objPHPExcel->getActiveSheet()->getStyle($emply12)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);

        // set background and font color
        $objPHPExcel->getActiveSheet()->getStyle('J4:L4')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('ffff00'); //yellow
        $objPHPExcel->getActiveSheet()->getStyle('J4:L4')->getFont()->setSize(11);
        $objPHPExcel->getActiveSheet()->getRowDimension(4)->setRowHeight(12);
        $objPHPExcel->getActiveSheet()->getStyle('J5:L5')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('333399'); //blue
        $objPHPExcel->getActiveSheet()->getStyle('J5:L5')->getFont()->getColor()->setRGB('ffffff');

        // add company logo
        if(empty($company->url_logo)){
            $asset_logo = "assets/images/logo/logoexcel.png";
        }else{
            $location = $this->config->item("base_upload");
            $asset_logo =  $location. $company->url_logo;
            if (!file_exists($asset_logo)) {
                $asset_logo = "assets/images/logo/logoexcel.png";
            }
        }

        $objDrawing = new PHPExcel_Worksheet_Drawing();
        $objDrawing->setName('Logo');
        $objDrawing->setDescription('Logo');
        $objDrawing->setPath(FCPATH.$asset_logo);
        $objDrawing->setCoordinates('B2');
        $objDrawing->setWidth(110);
        $objDrawing->setHeight(110);
        $objDrawing->setWorksheet($objPHPExcel->getActiveSheet());

        $file = FCPATH.'temp/KPI '.trim($data['detail']['data_karyawan']->nama_lengkap).' '.trim($data['detail']['periode']).'.xlsx';

        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $objWriter->save($file);
        
        header('Content-type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Pragma: ');
        header('Cache-Control: ');
        header('Content-disposition: attachment; filename="' . basename($file) . '"');
        readfile($file);
        unlink($file); // each asset can only be accessed once, delete after access
        exit();
    }

    private function namaAtasan($tahun, $bulan, $id_karyawan) {
        $this->load->model('karyawan_m');
        $idAtasan = $this->detail_m->getDataTrx($tahun, $bulan, $id_karyawan)->row()->id_atasan;
        return $this->karyawan_m->getDataById(md5($idAtasan))->nama_lengkap;
    }

    private function dataSkor($tahun, $bulan, $id_karyawan) {
        $status = $this->detail_m->getDataTrx($tahun, $bulan, $id_karyawan)->row()->status;
        if(belumDisetujui($status)) {
            return json_encode($this->Nilai_individu_m->skorMaster());
        } else {
            return $this->Nilai_individu_m->getDataSkor($id_karyawan, $tahun, $bulan);
        }
    }

    private function al($data)
	{
		$alphabet = array('a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l', 'm', 'n', 'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w', 'x', 'y', 'z');
		$alpha_flip = array_flip($alphabet);
		if ($data <= 25) {
			return $alphabet[$data];
		} elseif ($data > 25) {
			$dividend = ($data + 1);
			$alpha = '';
			// $modulo;
			while ($dividend > 0) {
				$modulo = ($dividend - 1) % 26;
				$alpha = $alphabet[$modulo] . $alpha;
				$dividend = floor((($dividend - $modulo) / 26));
			}
			return $alpha;
		}
	}
}