<?php

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

require './vendor/autoload.php';

class Download_dok extends MY_Controller {

    public function __construct() {
        parent::__construct();
        $this->load->model('m_download_dok', 'm_dok');
        $this->load->model('m_master');
        $this->load->model('m_amino');
    }

    function download_disposisi(){
        $param = $this->input->get();
        $data = $this->m_dok->get_disposisi($param);

        $path_file = $this->set_directory('disposisi.docx', 'disposisi', 'template_disposisi.docx');

        ## Initialize phpsreadsheet
        $file_template = $path_file;
        $templateProcessor = new \PhpOffice\PhpWord\TemplateProcessor($file_template);
        $templateProcessor->cloneRow('id_sampel', count($data));

        $items = 'jumlah_sampel';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities(count($data)), 1);

        $items = 'registrasi_lab';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities($data[0]['registrasi_laboratorium']), 1);
        
        $items = 'tanggal_penerimaan';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities($this->nbsp_filter(indo_datetime($data[0]['tgl_pukul_registrasi']))), 1);

        $param_global = [];
        $id_sampel_global = [];
        $x = 1;
        foreach($data as $r){
            $id_sampel_global[] = $r['id'];
            $item = 'nomor_urut';
            $items = $item.'#'.$x;
            $templateProcessor->setValue('${' . $items . '}', set_html_entities($x), 1);

            $item = 'id_sampel';
            $items = $item.'#'.$x;
            $templateProcessor->setValue('${' . $items . '}', set_html_entities($r[$item]), 1);

            $item = 'kualitas';
            $items = $item.'1#'.$x;
            $templateProcessor->setValue('${' . $items . '}', set_html_entities($r[$item] == 'Baik' ? '✓' : '-'), 1);
            $items = $item.'2#'.$x;
            $templateProcessor->setValue('${' . $items . '}', set_html_entities($r[$item] == 'Tidak Baik' ? '✓' : '-'), 1);


            $item = 'kemasan';
            $items = $item.'#'.$x;
            $templateProcessor->setValue('${' . $items . '}', set_html_entities($r[$item]), 1);

            $item = 'id_penanganan_sampel';
            $items = $item.'1#'.$x;
            $templateProcessor->setValue('${' . $items . '}', set_html_entities($r[$item] == '1' ? '✓' : '-'), 1);
            $items = $item.'2#'.$x;
            $templateProcessor->setValue('${' . $items . '}', set_html_entities($r[$item] == '2' ? '✓' : '-'), 1);
            $items = $item.'3#'.$x;
            $templateProcessor->setValue('${' . $items . '}', set_html_entities($r[$item] == '3' ? '✓' : '-'), 1);

            $item = 'id_kondisi_sampel';
            $items = $item.'1#'.$x;
            $templateProcessor->setValue('${' . $items . '}', set_html_entities($r[$item] == '1' ? $r['berat_sampel'] : '-'), 1);
            $items = $item.'2#'.$x;
            $templateProcessor->setValue('${' . $items . '}', set_html_entities($r[$item] == '2' ? $r['berat_sampel'] : '-'), 1);
            $items = $item.'3#'.$x;
            $templateProcessor->setValue('${' . $items . '}', set_html_entities($r[$item] == '3' ? $r['berat_sampel'] : '-'), 1);

            $item = 'jenis_pengujian';
            $parameter_uji = json_decode($r[$item]);
            foreach($parameter_uji as $pu){
                $param_global[] = $pu;
            }
            $items = $item.'1#'.$x;
            $templateProcessor->setValue('${' . $items . '}', ((in_array(1, $parameter_uji) || in_array(15, $parameter_uji) || in_array(16, $parameter_uji)) ? '✓' : '-'), 1);
            $items = $item.'2#'.$x;
            $templateProcessor->setValue('${' . $items . '}', ((in_array(2, $parameter_uji) || in_array(15, $parameter_uji) || in_array(16, $parameter_uji) || in_array(14, $parameter_uji)) ? '✓' : '-'), 1);
            $items = $item.'3#'.$x;
            $templateProcessor->setValue('${' . $items . '}', ((in_array(3, $parameter_uji) || in_array(15, $parameter_uji) || in_array(16, $parameter_uji)) ? '✓' : '-'), 1);
            $items = $item.'4#'.$x;
            $templateProcessor->setValue('${' . $items . '}', ((in_array(4, $parameter_uji) || in_array(15, $parameter_uji) || in_array(16, $parameter_uji)) ? '✓' : '-'), 1);
            $items = $item.'5#'.$x;
            $templateProcessor->setValue('${' . $items . '}', ((in_array(5, $parameter_uji) || in_array(15, $parameter_uji) || in_array(16, $parameter_uji)) ? '✓' : '-'), 1);
            $items = $item.'6#'.$x;
            $templateProcessor->setValue('${' . $items . '}', ((in_array(6, $parameter_uji) || in_array(13, $parameter_uji)) ? '✓' : '-'), 1);
            $items = $item.'7#'.$x;
            $templateProcessor->setValue('${' . $items . '}', ((in_array(7, $parameter_uji)) ? '✓' : '-'), 1);
            $items = $item.'8#'.$x;
            $templateProcessor->setValue('${' . $items . '}', ((in_array(8, $parameter_uji)) ? '✓' : '-'), 1);
            $items = $item.'9#'.$x;
            $templateProcessor->setValue('${' . $items . '}', ((in_array(9, $parameter_uji)) ? '✓' : '-'), 1);
            $items = $item.'10#'.$x;
            $templateProcessor->setValue('${' . $items . '}', ((in_array(10, $parameter_uji)) ? '✓' : '-'), 1);
            $items = $item.'11#'.$x;
            $templateProcessor->setValue('${' . $items . '}', ((in_array(11, $parameter_uji)) ? '✓' : '-'), 1);
            $items = $item.'12#'.$x;
            $templateProcessor->setValue('${' . $items . '}', ((in_array(12, $parameter_uji)) ? '✓' : '-'), 1);
            $items = $item.'13#'.$x;
            $templateProcessor->setValue('${' . $items . '}', ((in_array(13, $parameter_uji)) ? '✓' : '-'), 1);

            $item = 'id_sampel_kerja';
            $items = $item.'1#'.$x;
            $templateProcessor->setValue('${' . $items . '}', set_html_entities($r[$item] == '1' ? '✓' : '-'), 1);
            $items = $item.'2#'.$x;
            $templateProcessor->setValue('${' . $items . '}', set_html_entities($r[$item] == '2' ? '✓' : '-'), 1);
            $items = $item.'3#'.$x;
            $templateProcessor->setValue('${' . $items . '}', set_html_entities($r[$item] == '3' ? '✓' : '-'), 1);

            $item = 'id_sampel_arsip';
            $items = $item.'1#'.$x;
            $templateProcessor->setValue('${' . $items . '}', set_html_entities($r[$item] == '1' ? '✓' : '-'), 1);
            $items = $item.'2#'.$x;
            $templateProcessor->setValue('${' . $items . '}', set_html_entities($r[$item] == '2' ? '✓' : '-'), 1);
            $items = $item.'3#'.$x;
            $templateProcessor->setValue('${' . $items . '}', set_html_entities($r[$item] == '3' ? '✓' : '-'), 1);

            $item = 'ket_jenis_sampel';
            $items = $item.'#'.$x;
            $templateProcessor->setValue('${' . $items . '}', set_html_entities($r[$item]), 1);

            $x++;
        }
        
        $p_check   = ['path' => FCPATH.'/assets/img/dis-2.png', 'width' => 8, 'height' => 8, 'ratio' => false];
        $p_nocheck = ['path' => FCPATH.'/assets/img/dis-1.png', 'width' => 8, 'height' => 8, 'ratio' => false];
        
        $item_img = ['c_air', 'c_abu', 'c_pka', 'c_pkb', 'c_sk', 'c_lk', 'c_caa', 'c_cab', 'c_p', 'c_tdn', 'c_ur', 'c_af', 'c_andf', 'c_mbm', 'c_betn', 'c_pr', 'c_pnr', 'c_bpat', 'c_bpah', 'c_h', 'c_fsym', 'c_fsm'];
        $id_param_uji = [1, 2, 3, 14, 5, 4, 6, 13, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19];
        $param_global_unique = array_unique($param_global);

        $tabel_uji = ['kadar_air', 'kadar_abu', 'kadar_protein_kasar', 'kadar_pkd', 'kadar_serat_kasar', 'kadar_lemak_kasar', 'kadar_kalsium', 'kadar_ca', 'kadar_fosfor', 'kadar_tdn', 'kadar_ur', 'kadar_af', 'kadar_andf', 'kadar_mbm', 'kadar_tdn', 'data_sampel', 'data_sampel', 'data_sampel', 'data_sampel', 'data_sampel', 'data_sampel'];

        if(in_array(15, $param_global_unique)){
            $templateProcessor->setImageValue('c_air', $p_check);
            $templateProcessor->setImageValue('c_abu', $p_check);
            $templateProcessor->setImageValue('c_pka', $p_check);
            $templateProcessor->setImageValue('c_sk', $p_check);
            $templateProcessor->setImageValue('c_lk', $p_check);
        }

        if(in_array(16, $param_global_unique)){
            $templateProcessor->setImageValue('c_air', $p_check);
            $templateProcessor->setImageValue('c_abu', $p_check);
            $templateProcessor->setImageValue('c_pkb', $p_check);
            $templateProcessor->setImageValue('c_sk', $p_check);
            $templateProcessor->setImageValue('c_lk', $p_check);
        }

        $tgl_mulai_uji_global = [];
        foreach($item_img as $key => $ii){
            if(in_array($id_param_uji[$key], $param_global_unique)){
                foreach($id_sampel_global as $is){
                    if(!in_array($id_param_uji[$key], [13, 14, 15, 16, 17, 18, 19])){
                        $tgl_mulai_uji_global[] = $this->m_dok->get_mulai_uji($tabel_uji[$key], $is);
                    }else{
                        $tgl_mulai_uji_global[] = '';
                    }
                    
                }
                $templateProcessor->setImageValue($ii, $p_check);
            }else{
                $templateProcessor->setImageValue($ii, $p_nocheck);
            }
        }

        $tgl_mulai_uji  = $this->validasi_mulai_uji_disposisi($tgl_mulai_uji_global);
        if(isset($data[0]['tanggal_penghapusan']) && $data[0]['tanggal_penghapusan'] != ''){
            $tgl_pemusnahan = $data[0]['tanggal_penghapusan'];
        }else{
            $tgl_pemusnahan = $this->set_tgl_pemusnahan_disposisi($data[0]['perkiraan_estimasi_selesai_lhp']);
        }

        $items = 'tanggal_pengujian';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities($this->nbsp_filter(indo_datetime($tgl_mulai_uji))), 1);
        $items = 'rencana_tanggal_pemusnahan';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities($this->nbsp_filter(indo_datetime($tgl_pemusnahan))), 1);
        $items = 'rencana_tanggal_selesai';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities($this->nbsp_filter(indo_datetime($data[0]['perkiraan_estimasi_selesai_lhp']))), 1);

        $jl_img = ['aktif', 'pasif', 'khusus'];
        $id_layanan = [[1],[2,3,5,6],[4]];
        foreach($jl_img as $key => $ji){
            if(in_array($data[0]['id_layanan'], $id_layanan[$key])){
                $p_p   = ['path' => FCPATH.'/assets/img/'.$ji.'_check.png', 'width' => 65, 'height' => 18, 'ratio' => false];
                $templateProcessor->setImageValue('j_'.$ji, $p_p);
            }else{
                $p_p   = ['path' => FCPATH.'/assets/img/'.$ji.'.png', 'width' => 65, 'height' => 18, 'ratio' => false];
                $templateProcessor->setImageValue('j_'.$ji, $p_p);
            }
        }

        $templateProcessor->saveAS($path_file);
        $this->download_file($path_file);
    }

    function set_tgl_pemusnahan_disposisi($date='')
    {   
        $tgl_pemusnahan = '';
        if($date != ''){
            $tgl_pemusnahan = strtotime('+30 days', $date);
        }

        return $tgl_pemusnahan;
    }

    function validasi_mulai_uji_disposisi($tgl_mulai_uji_global=[])
    {
        $tgl_mulai_filter = [];
        if(count($tgl_mulai_uji_global) > 0){
            foreach($tgl_mulai_uji_global as $key => $k){
                if(!in_array($k, [0, '', null])){
                    $tgl_mulai_filter[] = (double)$k;
                }
            }
        }
        
        if(count($tgl_mulai_filter) > 0){
            $tgl_mulai_uji = min($tgl_mulai_filter);
        }else{
            $tgl_mulai_uji = '';
        }

        return $tgl_mulai_uji;
    }

    function cetak_lhp() {
        $data = $this->input->get();
        $data_uji = $this->m_dok->dataUji($data['id_uji']);
        $data = $this->m_dok->get_data_lhp($data['id_uji']);
        //$data = $this->m_dok->getAnalis($data);

        $nomor_permintaan_uji = nomor_id($data_uji['nomor_uji']);

        if(count($data) > 1)
            $nomor_permintaan_uji .= ' ('.nomor_id($data[0]['nomor_sampel']).'-'.nomor_id($data[intval(count($data)-1)]['id_sampel']).')';
        else
            $nomor_permintaan_uji .= ' ('.$data[0]['nomor_sampel'].')';

        $path_file = $this->set_directory('lhp.xlsx', 'lhp', 'template_lhp.xlsx');

        ## Initialize phpsreadsheet
        $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($path_file);
        $sheet = $spreadsheet->getActiveSheet();

        $x = 0;
        $row_data = 26;
        $sheet->getRowDimension(25)->setRowHeight(0);

        $ca_method = 13;
        $pk_method = 3;

        $id_param_uji = [];

        foreach ($data as $r) {
            $jenis_pengujian = json_decode($r['jenis_pengujian']);
            foreach($jenis_pengujian as $jkey => $js){
                $id_param_uji[] = $js;
            }

            $sheet->insertNewRowBefore($row_data + $x + 0, 1);
            $baris = $row_data + $x;
            $sheet->getRowDimension($baris)->setRowHeight(40);
            $sheet->setCellValue("A" . $baris, $x + 1);
            $sheet->setCellValue('B' . $baris, $r['nomor_sampel']);
            $sheet->setCellValue('D' . $baris, $r['jenis_sampel']."/\n".$r['asal_sampel']);
            $sheet->getStyle('D' . $baris)->getAlignment()->setWrapText(true);

            $sheet->setCellValue('F' . $baris, $r['air']);
            $sheet->setCellValue('G' . $baris, $r['sni_air']);

            $sheet->setCellValue('H' . $baris, $r['abu']);
            $sheet->setCellValue('I' . $baris, $r['sni_abu']);

            $pk = '-';
            if($r['protein_kasar'] != '' && !is_null($r['protein_kasar'])){
                $pk = $r['protein_kasar'];
                $sni_pk = $r['protein_kasar'];
                $pk_method = 3;
            }else{
                if($r['pkd'] != '' && !is_null($r['pkd'])){
                    $pk= $r['pkd'];
                    $sni_pk = $r['pkd'];
                    $pk_method = 14;
                }
            }

            $sheet->setCellValue('J' . $baris, $pk);
            $sheet->setCellValue('K' . $baris, $sni_pk);

            $sheet->setCellValue('L' . $baris, $r['lemak_kasar']);
            $sheet->setCellValue('M' . $baris, $r['sni_lemak_kasar']);

            $sheet->setCellValue('N' . $baris, $r['serat_kasar']);
            $sheet->setCellValue('O' . $baris, $r['sni_serat_kasar']);

            $kalsium = '-';
            if($r['ca'] != '' && !is_null($r['ca'])){
                $kalsium = $r['ca'];
                $sni_kalsium = $r['sni_ca'];
                $ca_method = 13;
            }else{
                if($r['kalsium'] != '' && !is_null($r['kalsium'])){
                    $kalsium = $r['kalsium'];
                    $sni_kalsium = $r['sni_kalsium'];
                    $ca_method = 6;
                }
            }

            $sheet->setCellValue('P' . $baris, $kalsium);
            $sheet->setCellValue('Q' . $baris, $sni_kalsium);

            $sheet->setCellValue('R' . $baris, $r['fosfor']);
            $sheet->setCellValue('S' . $baris, $r['sni_fosfor']);

            $sheet->setCellValue('T' . $baris, $r['betn']);
            $sheet->setCellValue('U' . $baris, $r['sni_betn']);

            $sheet->setCellValue('V' . $baris, $r['tdn']);
            $sheet->setCellValue('W' . $baris, $r['sni_tdn']);

            $sheet->setCellValue('X' . $baris, $r['andf']);
            $sheet->setCellValue('Y' . $baris, $r['sni_andf']);

            $sheet->setCellValue('Z' . $baris, $r['mbm']);
            $sheet->setCellValue('AA' . $baris, $r['sni_mbm']);

            $sheet->setCellValue('AB' . $baris, $r['af']);
            $sheet->setCellValue('AC' . $baris, $r['sni_af']);

            $sheet->setCellValue('AD' . $baris, $r['ur']);
            $sheet->setCellValue('AE' . $baris, $r['sni_ur']);

            $sheet->mergeCells("D".$baris.":E".$baris);
            $sheet->mergeCells("B".$baris.":C".$baris);

            $x++;
        }

        // MENCARI NAMA-NAMA JENIS PENGUJIAN
        $id_param_uji = array_unique($id_param_uji);
        sort($id_param_uji);
        $id_param_uji2 = $id_param_uji;

        $nama_jenis_pengujian = $this->nama_parameter_uji($id_param_uji);

        //----------------------------------------------------

        $mulai_row = count($data) + 26;
        $sheet->setCellValue('F' . $mulai_row, $this->m_dok->get_metode_uji_by_id(1)); # AIR
        $sheet->setCellValue('H' . $mulai_row, $this->m_dok->get_metode_uji_by_id(2)); # ABU
        $sheet->setCellValue('J' . $mulai_row, $this->m_dok->get_metode_uji_by_id($pk_method)); # PROTEIN KASAR
        $sheet->setCellValue('L' . $mulai_row, $this->m_dok->get_metode_uji_by_id(4)); # LEMAK KASAR
        $sheet->setCellValue('N' . $mulai_row, $this->m_dok->get_metode_uji_by_id(5)); # SERAT KASAR
        $sheet->setCellValue('P' . $mulai_row, $this->m_dok->get_metode_uji_by_id($ca_method)); # KALSIUM
        $sheet->setCellValue('R' . $mulai_row, $this->m_dok->get_metode_uji_by_id(7)); # FOSFOR
        $sheet->setCellValue('AD' . $mulai_row, $this->m_dok->get_metode_uji_by_id(9)); # UREA
        $sheet->setCellValue('AB' . $mulai_row, $this->m_dok->get_metode_uji_by_id(10)); # AF

        $sheet->setCellValue('T' . $mulai_row, $this->m_dok->get_metode_uji_by_id(1)); # BETN
        $sheet->setCellValue('V' . $mulai_row, $this->m_dok->get_metode_uji_by_id(8)); # TDN
        $sheet->setCellValue('X' . $mulai_row, $this->m_dok->get_metode_uji_by_id(11)); # ANDF
        $sheet->setCellValue('Z' . $mulai_row, $this->m_dok->get_metode_uji_by_id(12)); # MBM

        $id_param_uji2 = [ 1 ,  3 , 4 , 5 , 6 , 7 , 9 , 10, 17, 8 , 11 , 13 , 14 ]; #dummy data

        $c_param    = [ 1 , 2 , 3 , 4 , 5 , 6 , 7 , 9 , 10,  17, 8 ,  11 , 12, 13 , 14 ];
        $c_width    = ["F","H","J","L","N","P","R","AD","AB","T","V", "X","Z","P","J"];
        $c_width_sni= ["G","I","K","M","P","Q","S","AE","AC","U","X","Y","AA","Q","K"];

        $ab = 'W'; $e = 'E'; $h = 'G'; $s = 'S'; $o = 'O'; $u = 'V'; $w = 'W'; $aa = 'AA';
        

        $right_label = $this->abjad($ab, $id_param_uji2, $c_param, $c_width, $c_width_sni, 2);

        $o = $this->abjad('F', $id_param_uji2, $c_param, $c_width, $c_width_sni, -11);
        $h = $this->abjad($o, $id_param_uji2, $c_param, $c_width, $c_width_sni, 8);
        $u = $this->abjad($o, $id_param_uji2, $c_param, $c_width, $c_width_sni, -8);


        //var_dump($o);
        //var_dump($h);
        //var_dump($u);

        foreach($c_param as $ckey => $c){
            if(!in_array($c, $id_param_uji2)){
                $sheet->getColumnDimension($c_width[$ckey])->setWidth(0.0);
                $sheet->getColumnDimension($c_width_sni[$ckey])->setWidth(0.0);
                if($c_width[$ckey] == $ab || $c_width_sni[$ckey] == $ab){
                    $ab = $this->abjad($c_width[$ckey], $id_param_uji2, $c_param, $c_width, $c_width_sni);
                    $right_label = $this->abjad($c_width[$ckey], $id_param_uji2, $c_param, $c_width, $c_width_sni, 3);
                }

                if($c_width[$ckey] == $w || $c_width_sni[$ckey] == $w){
                    $w = $this->abjad($c_width[$ckey], $id_param_uji2, $c_param, $c_width, $c_width_sni);
                }

                if($c_width[$ckey] == $e || $c_width_sni[$ckey] == $e){
                    $e = $this->abjad($c_width[$ckey], $id_param_uji2, $c_param, $c_width, $c_width_sni);
                }

                if($c_width[$ckey] == $aa || $c_width_sni[$ckey] == $aa){
                    $aa = $this->abjad($c_width[$ckey], $id_param_uji2, $c_param, $c_width, $c_width_sni);
                }

                if($c_width[$ckey] == $o || $c_width_sni[$ckey] == $o) {
                    $o = $this->abjad($o, $id_param_uji2, $c_param, $c_width, $c_width_sni);
                    $h = $this->abjad($o, $id_param_uji2, $c_param, $c_width, $c_width_sni, 9);
                    $u = $this->abjad($o, $id_param_uji2, $c_param, $c_width, $c_width_sni, -8);
                }

            }
        }

        //var_dump($o);
        //var_dump($h);
        //var_dump($aa);
        //die();

        //tampil_array([$h, $o, $u]); die();

        $sheet->setCellValue($right_label.'14','Tanggal Diterima');
        $sheet->setCellValue($right_label.'15','Analisa/uji');
        $sheet->setCellValue($right_label.'16','Nomor Permintaan Uji');
        $sheet->setCellValue($right_label.'17','Tanggal Mulai Pengujian');
        $sheet->setCellValue($right_label.'18','Tanggal Selesai pengujian');
        $sheet->setCellValue($right_label.'19','Tanggal Selesai LHP');

        $sheet->setCellValue($ab.'14',': '.date('d/m/Y (H.i').' WIB)');
        $sheet->setCellValue($ab.'15',': '.$nama_jenis_pengujian);
        $sheet->setCellValue($ab.'16',': '.$nomor_permintaan_uji);
        $sheet->setCellValue($ab.'17',': '.get_str_date2($data_uji['tgl_mulai_uji']).' ('.get_str_time($data_uji['tgl_mulai_uji']).' WIB)');
        $sheet->setCellValue($ab.'18',': '.get_str_date2($data_uji['tgl_acc_lhu']).' ('.get_str_time($data_uji['tgl_acc_lhu']).' WIB)');
        $sheet->setCellValue($ab.'19',': '.date('d/m/Y (H.i').' WIB)');

        $sheet->setCellValue($e.'14',': '.$data_uji['instansi_umum']);
        $sheet->setCellValue($e.'15',': '.$data_uji['alamat']);
        $sheet->setCellValue($e.'16',': '.$data[0]['gol_jenis_sampel']);
        $sheet->setCellValue($e.'17',': '.$data[0]['kualitas']);

        $sheet->setCellValue($o.'2','PEMERINTAH PROVINSI JAWA TIMUR');
        $sheet->setCellValue($o.'3','DINAS PETERNAKAN');
        $sheet->setCellValue($o.'4','LABORATORIUM PAKAN');
        $sheet->setCellValue($o.'5','Jl. Jend. A. Yani 202 Telp. (031)  8292545-8280445-8285126 Faks. (031) 8291853');
        $sheet->setCellValue($o.'6','Website : disnak.jatimprov.go.id  E-Mail : disnak@jatimprov.go.id');
        $sheet->setCellValue($o.'7','SURABAYA (60235)');

        $sheet->setCellValue($o.'9','LAPORAN HASIL PENGUJIAN (LHP)');
        $sheet->setCellValue($o.'10','No: LP-LHP/'.$data_uji['nomor_uji'].'/    /'.angka_romawi(date('m')).'/'.date('Y'));

        $sheet->setCellValue($w.'8', 'Lampiran Surat : Surat Kepala Dinas  Peternakan Provinsi Jawa Timur');
        $sheet->setCellValue($w.'9', 'Tanggal   :        '.substr(str_replace('&nbsp;',' ', indo_datetime(date('Y-m-d H:i:s'))), 2));
        $sheet->setCellValue($w.'10', 'Nomor     :  524.6/           /122.2/'.date('Y'));
        $sheet->setCellValue($w.'11', 'Perihal     : Hasil Pengujian Sampel Pakan '.$data[0]['jenis_layanan'].' Tahun '.date('Y'));

        $pimpinan_unit_kerja = $this->m_dok->get_jabatan(1); // pimpinan unit kerja dan laboratorium pakan

        $sheet->setCellValue($aa.intval(29+count($data)), 'Surabaya, '.str_replace('&nbsp;',' ', indo_datetime(date('Y-m-d H:i:s'))));
        $sheet->setCellValue($aa.intval(30+count($data)), 'Pimpinan Unit Kerja');
        $sheet->setCellValue($aa.intval(31+count($data)), 'Laboratorium Pakan');
        $sheet->setCellValue($aa.intval(count($data)+34), $pimpinan_unit_kerja['nama']);
        $sheet->setCellValue($aa.intval(count($data)+35), 'NIP. '.$pimpinan_unit_kerja['nip']);

        $sheet->setCellValue($aa.intval(46+count($data)), 'No. Bagian :FM.7.8-2.');
        $sheet->setCellValue($aa.intval(47+count($data)), 'Halaman     :1 dari 1');

        $drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
            $path = FCPATH.'assets/img/lab_pakan02.png';
            $drawing->setName('Paid');
            $drawing->setDescription('Paid');
            $drawing->setPath($path);
            $drawing->setHeight(170);
            $drawing->setCoordinates($h.'2');
            $drawing->setWorksheet($spreadsheet->getActiveSheet());

        $drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
            $path = FCPATH.'assets/img/lhp_kan.png';
            $drawing->setPath($path);
            $drawing->setWidth(400);
            $drawing->setHeight(90);
            $drawing->setCoordinates($u.'2');
            $drawing->setWorksheet($spreadsheet->getActiveSheet());

        $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
        $writer->save($path_file);

        $this->download_file($path_file);
    }

    function spacing($value = 1)
    {
        $char = '';
        for($i=0;$i<$value;$i++){
            $char .= ' ';
        }
        return $char;
    }

    function nama_parameter_uji($id_param_uji = [])
    {
        $id_param_uji = json_encode($id_param_uji);
        if(strpos($id_param_uji, ',"12"') != false){
            $id_param_uji = str_replace(',"12"', '', $id_param_uji);
        }

        if(strpos($id_param_uji, '"13"') != false){
            $id_param_uji = str_replace(',"6"', '', $id_param_uji);
        }

        if(strpos($id_param_uji, '"1","2","3","4","5"') != false){
            $id_param_uji = str_replace('"1","2","3","4","5"', '"15"', $id_param_uji);
        }else{
            if(strpos($id_param_uji, '"1","2","4","5"') != false && strpos($id_param_uji, '"14"') != false){
                $id_param_uji = str_replace('"1","2","4","5"', '"16"', $id_param_uji);
                $id_param_uji = str_replace(',"14"', '', $id_param_uji);
            }
        }
        $id_param_uji = json_decode($id_param_uji);

        $nama_jenis_pengujian = '';
        $is_pro = '';
        $is_pk = '';
        
        foreach($id_param_uji as $pkey => $p){
            $nama_uji = $this->m_dok->get_jenis_pengujian_by_id($p)['nama'];
            if(in_array($nama_uji, ['Proksimat a', 'Proksimat b'])) $is_pro = $nama_uji = 'Proksimat';
            if(in_array($nama_uji, ['Kalsium a', 'Kalsium b'])) $nama_uji = 'Kalsium';
            if(in_array($nama_uji, ['Protein Kasar a', 'Protein Kasar b'])) $is_pk = $nama_uji = 'Protein Kasar';
            
            if($p == 8) $nama_uji = 'TDN';
            if($p == 11) $nama_uji = 'aNDF';
            if($p == 12) $nama_uji = 'MBM';

            $nama_jenis_pengujian .= $nama_uji.', ';
        }
        if(strpos('|'.$nama_jenis_pengujian, 'Proksimat') != false){
            $nama_jenis_pengujian = str_replace(', Protein Kasar', '', $nama_jenis_pengujian);
        }

        $nama_jenis_pengujian = substr($nama_jenis_pengujian, 0, '-2');

        return $nama_jenis_pengujian;
    }

    function download_lhp_amino(){
        $param =  $this->input->get();
        $data =  $this->m_amino->get_lhp_amino($param['id_uji']);
        $data_uji = $this->m_dok->dataUji($param['id_uji']);
        $data2 = $this->m_dok->getAnalis($param);

        //var_dump($data_uji);
        //die();

        $path_file = $this->set_directory('lhp_amino.xlsx', 'lhp_amino', 'template_lhp_amino2.xlsx');

        ## Initialize phpsreadsheet
        $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($path_file);
        $sheet = $spreadsheet->getActiveSheet();

        $x = 0;
        $row_data = 26;
        $sheet->getRowDimension(25)->setRowHeight(0);
        foreach ($data as $r) {
            $sheet->insertNewRowBefore($row_data + $x + 0, 1);
            $baris = $row_data + $x;
            $sheet->getRowDimension($baris)->setRowHeight(40);
            $sheet->setCellValue("A" . $baris, $x + 1);
            $sheet->setCellValue('B' . $baris, nomor_id($r['id_uji']));
            $sheet->setCellValue('D' . $baris, $data2[0]['nama'].'/'.$data2[0]['asal_sampel']);

            //$sheet->setCellValue('F' . $baris, $r['l_air']);
            //$sheet->setCellValue('G' . $baris, $r['kadar_air']);

            //$sheet->setCellValue('H' . $baris, $r['l_abu']);
            //$sheet->setCellValue('I' . $baris, $r['kadar_abu']);

            $sheet->setCellValue('J' . $baris, $r['lisin']);
            $sheet->setCellValue('K' . $baris, $r['min_lisin']);

            $sheet->setCellValue('L' . $baris, $r['metionin']);
            $sheet->setCellValue('M' . $baris, $r['min_metionin']);

            $sheet->setCellValue('N' . $baris, $r['metionin_sistin']);
            $sheet->setCellValue('O' . $baris, $r['min_metionin_sistin']);

            $sheet->setCellValue('P' . $baris, $r['treonin']);
            $sheet->setCellValue('Q' . $baris, $r['min_treonin']);

            $sheet->setCellValue('R' . $baris, $r['triptofan']);
            $sheet->setCellValue('S' . $baris, $r['min_triptofan']);

            $sheet->mergeCells("D".$baris.":i".$baris);
            $sheet->mergeCells("B".$baris.":C".$baris);

            $x++;
        }

        $sheet->setCellValue('Q14',': '.date('d/m/Y (H.i').' WIB)');
        $sheet->setCellValue('Q15',': '.'Asam Amino');
        $sheet->setCellValue('Q16',': '.nomor_id($param['id_uji']));//$nomor_permintaan_uji);
        $sheet->setCellValue('Q17',': '.date('d/m/Y (H.i', strtotime('-2Day', strtotime(date('Y-m-d H:i')))).' WIB)');
        $sheet->setCellValue('Q18',': '.date('d/m/Y (H.i', strtotime('-1Day', strtotime(date('Y-m-d H:i')))).' WIB)');
        $sheet->setCellValue('Q19',': '.date('d/m/Y (H.i').' WIB)');

        $sheet->setCellValue('Q'.intval(29+count($data)),'Surabaya, '.date('d-m-Y'));

        $sheet->setCellValue('E14',': '.$data_uji['instansi_umum']);
        $sheet->setCellValue('E15',': '.$data_uji['alamat']);
        $sheet->setCellValue('E16',': '.$data2[0]['gol_jenis_sampel']);
        $sheet->setCellValue('E17',': '.$data2[0]['kualitas']);

        $pimpinan_unit_kerja = $this->m_dok->get_jabatan(1); // pimpinan unit kerja dan laboratorium pakan
        $sheet->setCellValue('Q'.intval(count($data)+34), $pimpinan_unit_kerja['nama']);
        $sheet->setCellValue('Q'.intval(count($data)+35), 'NIP. '.$pimpinan_unit_kerja['nip']);

        $drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
            $path = FCPATH.'assets/img/lab_pakan02.png';
            $drawing->setName('Paid');
            $drawing->setDescription('Paid');
            $drawing->setPath($path);
            $drawing->setHeight(170);
            $drawing->setCoordinates('E2');
            $drawing->setWorksheet($spreadsheet->getActiveSheet());

        $drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
            $path = FCPATH.'assets/img/lhp_kan.png';
            $drawing->setPath($path);
            $drawing->setWidth(400);
            $drawing->setHeight(90);
            $drawing->setCoordinates('O2');
            $drawing->setWorksheet($spreadsheet->getActiveSheet());

        $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
        $writer->save($path_file);

        $this->download_file($path_file);

    }

    function download_persetujuan_amino(){
        $param =  $this->input->get();
        $data =  $this->m_dok->get_lhp_amino($param, 'uji');

        $path_file = $this->set_directory('lhp_amino.xlsx', 'lhp_amino', 'template_lhp_amino.xlsx');

        ## Initialize phpsreadsheet
        $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($path_file);
        $sheet = $spreadsheet->getActiveSheet();


        $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
        $writer->save($path_file);

        $this->download_file($path_file);
    }

    function download_npps($id, $id_status){
        $data =  $this->m_dok->get_npps($id);

        $path_file = $this->set_directory('npps.xlsx', 'npps', 'template_npps.xlsx');

        ## Initialize phpsreadsheet
        $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($path_file);
        $sheet = $spreadsheet->getActiveSheet();

        $x = 0;
        $row_data = 26;
        $sheet->getRowDimension(25)->setRowHeight(0);
        foreach ($data as $r) {
            $parameter_uji = json_decode($r['jenis_pengujian']);

            $jenis_pengujian = json_decode($r['jenis_pengujian']);
            $jenis_uji = '';
            foreach($jenis_pengujian as $jp){
                $jenis_uji .= $this->m_dok->get_parameter($jp)->row_array()['nama'];
            }

            $eparameter_uji = '';
            if(count($jenis_pengujian) > 1) $eparameter_uji = '-'.end($jenis_pengujian);

            $kode_pakan = isset($r['kode_pakan']) ? ' ('.$r['kode_pakan'].')' : '';

            $sheet->insertNewRowBefore($row_data + $x + 0, 1);
            $baris = $row_data + $x;

            $sheet->getRowDimension($baris)->setRowHeight(20);

            $sheet->setCellValue('A' . $baris, $x + 1);
            $sheet->setCellValue('B' . $baris, $r['merk_dagang'].$kode_pakan);
            $sheet->setCellValue('D' . $baris, '1');
            $sheet->setCellValue('E' . $baris, $r['kemasan']);
            $sheet->setCellValue('F' . $baris, $r['berat_sampel']);
            $sheet->setCellValue('G' . $baris, $r['bentuk_jenis']);
            $sheet->setCellValue('I' . $baris, $r['id_penanganan_sampel'] == '1' ? '√':'-');
            $sheet->setCellValue('J' . $baris, $r['id_penanganan_sampel'] == '2' ? '√':'-');
            $sheet->setCellValue('K' . $baris, $r['id_penanganan_sampel'] == '3' ? '√':'-');
            $sheet->setCellValue('L' . $baris, $jenis_pengujian[0].$eparameter_uji);
            $sheet->setCellValue('M' . $baris, $r['kualitas'] == 'Baik' ? '√':'-');
            $sheet->setCellValue('N' . $baris, $r['kualitas'] == 'Tidak Baik' ? '√':'-');

            $sheet->mergeCells("B".$baris.":C".$baris);
            $sheet->mergeCells("G".$baris.":H".$baris);

            $x++;
        }

        $cdata = count($data);
        $edata = '';
        if($cdata > 1){
            for($i=1;$i<$cdata;$i++){
                $edata .= ', '.nomor_id($data[$i]['id_sampel']);
            }
        }

        $sheet->setCellValue('D'.intval(26+$cdata), $cdata);

        $kaji_ulang_permintaan = '|'.(!is_null($data[0]['kaji_ulang_permintaan']) ? $data[0]['kaji_ulang_permintaan'] : '');
        $picon_array = array('B','D','F', 'I');
        $vp = array('sdm','metode','peralatan','bahan kimia');
        $picon = 'picon1.png';
        $xp = 0;

        foreach($picon_array as $pa){
            //if(strpos($kaji_ulang_permintaan, $vp[$xp]) != false) $picon = 'picon2.png';
            $drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
            $path = FCPATH.'assets/img/'.$picon;
            $drawing->setPath($path);
            $drawing->setCoordinates($pa.intval(40+$cdata));
            $drawing->setOffsetX(-17);
            $drawing->setWidthAndHeight(17, 17);
            $drawing->setWorksheet($spreadsheet->getActiveSheet());

            $xp++;
        }

        $sheet->setCellValue('D13', ': '.$data[0]['nomor_uji'].' ('.$data[0]['nomor_sampel'].$edata.')');
        $sheet->setCellValue('D14', ': '.$this->nbsp_filter(indo_datetime($data[0]['tgl_pukul_registrasi'], 'time')));
        $sheet->setCellValue('D15', ': '.$this->nbsp_filter(indo_datetime($data[0]['perkiraan_estimasi_selesai_lhp'], 'time')));
        $sheet->setCellValue('D16', ': '.$data[0]['nama_pemohon']);
        $sheet->setCellValue('D17', ': '.$data[0]['instansi_umum']);
        $sheet->setCellValue('D18', ': '.$data[0]['alamat']);
        $sheet->setCellValue('D19', ': '.$data[0]['nomor_telepon'].'/'.$data[0]['email_pemohon']);
        $sheet->setCellValue('D20', ': '.$data[0]['asal_sampel']);

        $sheet->setCellValue('A9', 'No : LP-NPPS/'.nomor_id($data[0]['id_uji']).'/'.angka_romawi(date('m')).'/'.date('m'));

        $sekretaris_dinas = $this->m_dok->get_jabatan(6); // sekretaris dinas/manajer administrasi
        //$penerima_sampel = $this->m_dok->get_jabatan(5);
        $id_penerima_sampel = (!is_null($data[0]['penerima_sampel']) && $data[0]['penerima_sampel'] != '') ? $data[0]['penerima_sampel']: $data[0]['last_modified_user'];
        $penerima_sampel = $this->m_dok->get_personil_by_id($id_penerima_sampel);
        
        $pemohon_sampel = $data[0]['nama_pemohon'];

        $sd_nip_niptk = $this->set_nip_niptk($sekretaris_dinas);
        $ps_nip_niptk = $this->set_nip_niptk($penerima_sampel);

        $sheet->mergeCells("B".intval(count($data)+78).":C".intval(count($data)+78));
        $sheet->mergeCells("B".intval(count($data)+79).":C".intval(count($data)+79));

        $sheet->setCellValue('B'.intval(count($data)+78), $sekretaris_dinas['nama']);
        $sheet->setCellValue('B'.intval(count($data)+79), $sd_nip_niptk);
        $sheet->setCellValue('F'.intval(count($data)+78), $penerima_sampel['nama']);
        $sheet->setCellValue('F'.intval(count($data)+79), $ps_nip_niptk);
        $sheet->setCellValue('I'.intval(count($data)+78), '('.$pemohon_sampel.')');

        $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
        $writer->save($path_file);

        $this->download_file($path_file);
    }

    function set_nip_niptk($personil = null)
    {
        $nip_niptk  = '-';
        if(isset($personil)){
            if(!is_null($personil['nip']) && $personil['nip'] != ''){
                $nip_niptk = 'NIP. '.$personil['nip'];
            }else{
                if(!is_null($personil['nip']) && $personil['nip'] != ''){
                    $nip_niptk = 'NIP. '.$personil['nip'];
                }else{
                    if(!is_null($personil['niptk']) && $personil['niptk'] != ''){
                        $nip_niptk = 'NIPPT-PK. '.$personil['niptk'];    
                    }else{
                        $nip_niptk = '-';
                    }  
                }
            }
        }
        return $nip_niptk;
    }

    function download_npps_vp($id, $id_status){
        $data =  $this->m_dok->get_npps($id);

        $path_file = $this->set_directory('npps.xlsx', 'npps', 'template_npps_vp.xlsx');

        ## Initialize phpsreadsheet
        $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($path_file);
        $sheet = $spreadsheet->getActiveSheet();

        $x = 0;
        $row_data = 26;
        $sheet->getRowDimension(25)->setRowHeight(0);
        foreach ($data as $r) {
            $jenis_pengujian = explode('|', $r['jenis_pengujian']);
            $parameter_uji = array();
            foreach($jenis_pengujian as $j){
                if($j != '' && isset($j)) $parameter_uji[] = $j;
            }

            $jenis_pengujian = json_decode($r['jenis_pengujian']);
            $jenis_uji = '';
            foreach($jenis_pengujian as $jp){
                $jenis_uji .= $this->m_dok->get_parameter($jp)->row_array()['nama'];
            }

            $eparameter_uji = '';
            if(count($jenis_pengujian) > 1) $eparameter_uji = '-'.end($jenis_pengujian);

            $kode_pakan = isset($r['kode_pakan']) ? ' ('.$r['kode_pakan'].')' : '';

            $sheet->insertNewRowBefore($row_data + $x + 0, 1);
            $baris = $row_data + $x;

            $sheet->getRowDimension($baris)->setRowHeight(20);

            $sheet->setCellValue('A' . $baris, $x + 1);
            $sheet->setCellValue('B' . $baris, $r['merk_dagang'].$kode_pakan);
            $sheet->setCellValue('D' . $baris, '1');
            $sheet->setCellValue('E' . $baris, $r['kemasan']);
            $sheet->setCellValue('F' . $baris, $r['berat_sampel']);
            $sheet->setCellValue('G' . $baris, $r['bentuk_jenis']);
            $sheet->setCellValue('I' . $baris, $r['id_penanganan_sampel'] == '1' ? '√':'-');
            $sheet->setCellValue('J' . $baris, $r['id_penanganan_sampel'] == '2' ? '√':'-');
            $sheet->setCellValue('K' . $baris, $r['id_penanganan_sampel'] == '3' ? '√':'-');
            $sheet->setCellValue('L' . $baris, $jenis_pengujian[0].$eparameter_uji);
            $sheet->setCellValue('M' . $baris, $r['kualitas'] == 'Baik' ? '√':'-');
            $sheet->setCellValue('N' . $baris, $r['kualitas'] == 'Tidak Baik' ? '√':'-');

            $sheet->mergeCells("B".$baris.":C".$baris);
            $sheet->mergeCells("G".$baris.":H".$baris);

            $x++;
        }

        $cdata = count($data);
        $edata = '';
        if($cdata > 1){
            for($i=1;$i<$cdata;$i++){
            $edata .= ', '.nomor_id($data[$i]['id_sampel']);
            }
        }

        $sheet->setCellValue('D'.intval(26+$cdata), $cdata);

        $kaji_ulang_permintaan = '|'.(!is_null($data[0]['kaji_ulang_permintaan']) ? $data[0]['kaji_ulang_permintaan'] : '');
        $picon_array = array('B','D','F', 'I');
        $vp = array('sdm','metode','peralatan','bahan kimia');
        $picon = 'picon1.png';
        $xp = 0;

        foreach($picon_array as $pa){
            if(strpos($kaji_ulang_permintaan, $vp[$xp]) != false) $picon = 'picon2.png';
            $drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
            $path = FCPATH.'assets/img/'.$picon;
            $drawing->setPath($path);
            $drawing->setCoordinates($pa.intval(40+$cdata));
            $drawing->setOffsetX(-17);
            $drawing->setWidthAndHeight(17, 17);
            $drawing->setWorksheet($spreadsheet->getActiveSheet());

            $xp++;
        }

        $sheet->setCellValue('D13', ': '.$data[0]['nomor_uji'].' ('.$data[0]['nomor_sampel'].$edata.')');
        $sheet->setCellValue('D14', ': '.$this->nbsp_filter(indo_datetime($data[0]['tgl_pukul_registrasi'])));
        $sheet->setCellValue('D15', ': '.$this->nbsp_filter(indo_datetime($data[0]['perkiraan_estimasi_selesai_lhp'])));
        $sheet->setCellValue('D16', ': '.$data[0]['nama_pemohon']);
        $sheet->setCellValue('D17', ': '.$data[0]['instansi_umum']);
        $sheet->setCellValue('D18', ': '.$data[0]['alamat']);
        $sheet->setCellValue('D19', ': '.$data[0]['nomor_telepon'].'/'.$data[0]['email_pemohon']);
        $sheet->setCellValue('D20', ': '.$data[0]['asal_sampel']);

        $sheet->setCellValue('A9', 'No : LP-NPPS/'.nomor_id($data[0]['id_uji']).'/'.angka_romawi(date('m')).'/'.date('m'));

        $sekretaris_dinas = $this->m_dok->get_jabatan(6); // sekretaris dinas/manajer administrasi
        //$penerima_sampel = $this->m_dok->get_jabatan(5);
        $id_penerima_sampel = (!is_null($data[0]['penerima_sampel']) && $data[0]['penerima_sampel'] != '') ? $data[0]['penerima_sampel']: $data[0]['last_modified_user'];
        $penerima_sampel = $this->m_dok->get_personil_by_id($id_penerima_sampel);
        $pemohon_sampel = $data[0]['nama_pemohon'];

            $sd_nip_niptk = $this->set_nip_niptk($sekretaris_dinas);
            $ps_nip_niptk = $this->set_nip_niptk($penerima_sampel);

            $sheet->mergeCells("B".intval(count($data)+78).":C".intval(count($data)+78));
            $sheet->mergeCells("B".intval(count($data)+79).":C".intval(count($data)+79));

            $sheet->setCellValue('B'.intval(count($data)+78), $sekretaris_dinas['nama']);
            $sheet->setCellValue('B'.intval(count($data)+79), $sd_nip_niptk);
            $sheet->setCellValue('F'.intval(count($data)+78), $penerima_sampel['nama']);
            $sheet->setCellValue('F'.intval(count($data)+79), $ps_nip_niptk);
        //$sheet->setCellValue('F'.intval(count($data)+78), $penerima_sampel['nama']);

        $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
        $writer->save($path_file);

        $this->download_file($path_file);
    }

    function download_ttpp($id_uji, $id_status) {
        $data =  $this->m_dok->get_ttpp($id_uji);
        $bendahara_pp = $this->m_dok->get_jabatan(7); // bendahara penerima pembantu
        $bp_nip_niptk = $this->set_nip_niptk($bendahara_pp);

        $path_file = $this->set_directory('ttpp.xlsx', 'ttpp', 'template_ttpp.xlsx');

        $getdata = [];
        foreach($data[1] as $key => $i){
            if(!empty($i['total'])){
                $getdata[] = $i;
            }
        }

        $data[1] = $getdata;

        ## Initialize phpsreadsheet
        $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($path_file);
        $sheet = $spreadsheet->getActiveSheet();

        $x = 0;
        $row_data = 24;
        $sheet->getRowDimension(23)->setRowHeight(0);
        $data1 = array();
        foreach ($data[1] as $r) {
            if(!empty($r['total'])){
            $sheet->insertNewRowBefore($row_data + $x + 1, 1);
            $baris = $row_data + $x;
            $sheet->getRowDimension($baris)->setRowHeight(20);
            $sheet->setCellValue('B' . $baris, $x + 1);
            
            switch ($r['nama']) {
                case 'Protein Kasar a':
                    $r['nama'] = 'Protein Kasar';
                    break;
                case 'Protein Kasar b':
                    $r['nama'] = 'Protein Kasar';
                    break;
                case 'Kalsium a':
                    $r['nama'] = 'Kalsium';
                    break;
                case 'Kalsium b':
                    $r['nama'] = 'Kalsium';
                    break;
                case 'Proksimat a':
                    $r['nama'] = 'Proksimat';
                    break;
                case 'Proksimat b':
                    $r['nama'] = 'Proksimat';
                    break;
                default:
                    $r['nama'] = $r['nama'];
                    break;
            }

            $sheet->setCellValue('C' . $baris, $r['nama']);
            $sheet->setCellValue('E' . $baris, $r['total']);
            $sheet->setCellValue('H' . $baris, $r['tarif_analis']);
            $sheet->setCellValue('J' . $baris, $r['jumlah_biaya']);

            $sheet->mergeCells("C".$baris.":D".$baris);
            $sheet->mergeCells("E".$baris.":F".$baris);
            $sheet->mergeCells("H".$baris.":I".$baris);
            $sheet->mergeCells("J".$baris.":K".$baris);

            $data1[] = (double)$r['jumlah_biaya'];
            }
            $x++;
        }

        $cdata = count($data[0]);
        $sdata = $data[0][0]['id'];
        $edata = '';
        $endat = '';
        if($cdata > 1){
            for($i=1;$i<$cdata;$i++){
            $edata .= ', '.$data[0][$i]['nomor_sampel'];
            }
            $endat = '-'.end($data[0])['nomor_sampel'];
        }

        $cduji = count($data[0]);
        $eduji = [];
        if($cdata > 1){
            for($i=0;$i<$cduji;$i++){
            $eduji[] = $data[0][$i]['nomor_uji'];
            }
        }

        sort($eduji);

        $enduji = $eduji[0];
        if(count($eduji) > 1){
            $enduji .= '-'.end($eduji);
        }

        $cdata1 = intval(count($data1)+$row_data);
        $harga_total_keseluruhan = array_sum($data1);
        $sheet->getRowDimension($cdata1)->setRowHeight(0);
        $sheet->setCellValue('J'.intval($cdata1+1), $harga_total_keseluruhan);

        $sheet->setCellValue('E12', ': '.$enduji.'/'.$sdata.$endat);
        $sheet->setCellValue('E13', ': '.$data[0][0]['nama_pemohon']);
        $sheet->setCellValue('E14', ': '.$data[0][0]['instansi_umum']);
        $sheet->setCellValue('E15', ': '.$data[0][0]['alamat']);
        $sheet->setCellValue('E17', ': '.$data[0][0]['nomor_telepon'].'/'.$data[0][0]['email_pemohon']);
        $sheet->setCellValue('I'.intval(count($data1)+35), $bendahara_pp['nama']);
        $sheet->setCellValue('I'.intval(count($data1)+36), $bp_nip_niptk);
        $sheet->setCellValue('I8', 'Surabaya, '.$this->nbsp_filter(indo_datetime(date('Y-m-d H:i:s'), 'cetak')));

        $sheet->setCellValue('D11', 'No : LP-TTPP/'.$enduji.'/'.angka_romawi(date('m')).'/'.date('Y'));

        $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
        $writer->save($path_file);

        $this->download_file($path_file);
    }

    function download_lhu(){
        $param =  $this->input->get();
        $data_uji = $this->m_dok->dataUji($param['id_uji']);
        $data = $this->m_dok->getAnalis($param);

        $path_file = $this->set_directory('lhu.xlsx', 'lhu', 'template_lhu.xlsx');

        ## Initialize phpsreadsheet
        $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($path_file);
        $sheet = $spreadsheet->getActiveSheet();

        $no_sampel = '';
        $parameter_uji = '';
        $x = 0;
        $row_data = 19;

        $mulai_uji_kalsium = [];
        $selesai_uji_kalsium = [];
        $ca_method = 13;

        $mulai_uji_pk = [];
        $selesai_uji_pk= [];
        $pk_method = 3;

        $id_param_uji = [];

        foreach ($data as $r) {
            $jenis_pengujian = json_decode($r['jenis_pengujian']);
            foreach($jenis_pengujian as $jkey => $js){
                $id_param_uji[] = $js;
            }

            $sheet->insertNewRowBefore($row_data + $x + 1, 1);
            $baris = $row_data + $x;
            $sheet->getRowDimension($baris)->setRowHeight(20);
            $sheet->setCellValue('A' . $baris, $x + 1);
            $sheet->setCellValue('B' . $baris, $r['nomor_sampel']);
            $sheet->setCellValue('C' . $baris, (isset($r['l_air']) && $r['l_air'] != '') ? $r['l_air'] : '-');
            $sheet->setCellValue('E' . $baris, (isset($r['l_abu']) && $r['l_abu'] != '') ? $r['l_abu'] : '-');

            $pk = '-';
            if($r['l_protein_kasar'] != '' && !is_null($r['l_protein_kasar'])){
                $pk = $r['l_protein_kasar'];
                $pk_method = 3;
                $mulai_uji_pk[] = $r['mulai_uji_protein_kasar'];
                $selesai_uji_pk[] = $r['selesai_uji_pkd'];
            }else{
                if(!empty($r['l_pkd']) && !in_array($r['l_pkd'], ['', null])){
                    $pk= $r['l_pkd'];
                    $pk_method = 14;
                    $mulai_uji_pkd[] = $r['mulai_uji_pkd'];
                    $selesai_uji_pkd[] = $r['selesai_uji_pkd'];
                }
            }
            $sheet->setCellValue('F' . $baris, $pk);
            $sheet->setCellValue('G' . $baris, (isset($r['l_lemak_kasar']) && $r['l_lemak_kasar'] != '') ? $r['l_lemak_kasar'] : '-');
            $sheet->setCellValue('H' . $baris, (isset($r['l_serat_kasar']) && $r['l_serat_kasar'] != '') ? $r['l_serat_kasar'] : '-');
            
            $kalsium = '-';
            if($r['l_ca'] != '' && !is_null($r['l_ca'])){
                $kalsium = $r['l_ca'];
                $ca_method = 13;
                $mulai_uji_kalsium[] = $r['mulai_uji_ca'];
                $selesai_uji_kalsium[] = $r['selesai_uji_ca'];
            }else{
                if($r['l_kalsium'] != '' && !is_null($r['l_kalsium'])){
                    $kalsium = $r['l_kalsium'];
                    $ca_method = 6;
                    $mulai_uji_kalsium[] = $r['mulai_uji_kalsium'];
                    $selesai_uji_kalsium[] = $r['selesai_uji_kalsium'];
                }
            }
            $sheet->setCellValue('J' . $baris, $kalsium);
            $sheet->setCellValue('K' . $baris, (isset($r['l_fosfor']) && $r['l_fosfor'] != '') ? $r['l_fosfor'] : '-');
            $sheet->setCellValue('T' . $baris, (isset($r['l_ur']) && $r['l_ur'] != '') ? $r['l_ur'] : '-');
            $sheet->setCellValue('S' . $baris, (isset($r['l_af']) && $r['l_af'] != '') ? $r['l_af'] : '-');

            $sheet->setCellValue('M' . $baris, (isset($r['l_betn']) && $r['l_betn'] != '') ? $r['l_betn'] : '-');
            $sheet->setCellValue('N' . $baris, (isset($r['l_tdn']) && $r['l_tdn'] != '') ? $r['l_tdn'] : '-');
            $sheet->setCellValue('O' . $baris, (isset($r['l_andf']) && $r['l_andf'] != '') ? $r['l_andf'] : '-');
            $sheet->setCellValue('R' . $baris, (isset($r['l_mbm']) && $r['l_mbm'] != '') ? $r['l_mbm'] : '-');
            
            $p_uji = $this->m_dok->get_parameter($r['id_sampel']);

            $no_sampel .= $r['nomor_sampel'].', ';

            $x++;
        }

        // MENCARI NAMA-NAMA JENIS PENGUJIAN
        $id_param_uji = array_unique($id_param_uji);
        sort($id_param_uji);
        $id_param_uji2 = $id_param_uji;

        $nama_jenis_pengujian = $this->nama_parameter_uji($id_param_uji);
        //----------------------------------------------------

        $sheet->setCellValue('A' . 10, 'No : LP-LHU/'.$data[0]['registrasi_laboratorium'].'/'.angka_romawi(date('m', strtotime($data[0]['tgl_pukul_registrasi']))).'/'.date('Y', $data[0]['tgl_pukul_registrasi']));

        $sheet->setCellValue('A' . 12, 'Nomor Permintaan Lab/ sampel : '.$data_uji['registrasi_laboratorium'].'/'.substr($no_sampel,0,-2));
        $sheet->setCellValue('A' . 13, 'Jumlah Sampel                               : '.count($data).'('.strtolower(angka_string(count($data))).')');
        $sheet->setCellValue('A' . 14, 'Jenis Pengujian                              : '.$nama_jenis_pengujian);
        
        $mulai_row = count($data) + 19 + 1;
        $sheet->setCellValue('C' . $mulai_row, $this->m_dok->get_metode_uji_by_id(1)); # AIR
        $sheet->setCellValue('E' . $mulai_row, $this->m_dok->get_metode_uji_by_id(2)); # ABU
        $sheet->setCellValue('F' . $mulai_row, $this->m_dok->get_metode_uji_by_id($pk_method)); # PROTEIN KASAR
        $sheet->setCellValue('G' . $mulai_row, $this->m_dok->get_metode_uji_by_id(4)); # LEMAK KASAR
        $sheet->setCellValue('H' . $mulai_row, $this->m_dok->get_metode_uji_by_id(5)); # SERAT KASAR
        $sheet->setCellValue('J' . $mulai_row, $this->m_dok->get_metode_uji_by_id($ca_method)); # KALSIUM
        $sheet->setCellValue('K' . $mulai_row, $this->m_dok->get_metode_uji_by_id(7)); # FOSFOR
        $sheet->setCellValue('T' . $mulai_row, $this->m_dok->get_metode_uji_by_id(9)); # UREA
        $sheet->setCellValue('S' . $mulai_row, $this->m_dok->get_metode_uji_by_id(10)); # AF

        $sheet->setCellValue('M' . $mulai_row, $this->m_dok->get_metode_uji_by_id(1)); # BETN
        $sheet->setCellValue('N' . $mulai_row, $this->m_dok->get_metode_uji_by_id(8)); # TDN
        $sheet->setCellValue('O' . $mulai_row, $this->m_dok->get_metode_uji_by_id(11)); # ANDF
        $sheet->setCellValue('R' . $mulai_row, $this->m_dok->get_metode_uji_by_id(12)); # MBM

        $mulai_row = count($data) + 19 + 2;
        $sheet->setCellValue('C' . $mulai_row, get_str_date2($data[0]['mulai_uji_air']));
        $sheet->setCellValue('E' . $mulai_row, get_str_date2($data[0]['mulai_uji_abu']));
        $sheet->setCellValue('F' . $mulai_row, (!empty($mulai_uji_pk[0])) ? get_str_date2($mulai_uji_pk[0]) : null);
        $sheet->setCellValue('G' . $mulai_row, get_str_date2($data[0]['mulai_uji_lemak_kasar']));
        $sheet->setCellValue('H' . $mulai_row, get_str_date2($data[0]['mulai_uji_serat_kasar']));
        $sheet->setCellValue('J' . $mulai_row, (!empty($mulai_uji_kalsium[0])) ? get_str_date2($mulai_uji_kalsium[0]) : null);
        $sheet->setCellValue('K' . $mulai_row, get_str_date2($data[0]['mulai_uji_fosfor']));
        $sheet->setCellValue('T' . $mulai_row, get_str_date2($data[0]['mulai_uji_ur']));
        $sheet->setCellValue('S' . $mulai_row, get_str_date2($data[0]['mulai_uji_af']));

        $sheet->setCellValue('M' . $mulai_row, get_str_date2($data[0]['mulai_uji_betn']));
        $sheet->setCellValue('N' . $mulai_row, get_str_date2($data[0]['mulai_uji_tdn']));
        $sheet->setCellValue('O' . $mulai_row, get_str_date2($data[0]['mulai_uji_andf']));
        $sheet->setCellValue('R' . $mulai_row, get_str_date2($data[0]['mulai_uji_mbm']));

        $selesai_row = count($data) + 19 + 3;
        $sheet->setCellValue('C' . $selesai_row, get_str_date2($data[0]['selesai_uji_air']));
        $sheet->setCellValue('E' . $selesai_row, get_str_date2($data[0]['selesai_uji_abu']));
        $sheet->setCellValue('F' . $selesai_row, (!empty($selesai_uji_pk[0])) ? get_str_date2($selesai_uji_pk[0]) : null);
        $sheet->setCellValue('G' . $selesai_row, get_str_date2($data[0]['selesai_uji_lemak_kasar']));
        $sheet->setCellValue('H' . $selesai_row, get_str_date2($data[0]['selesai_uji_serat_kasar']));
        $sheet->setCellValue('J' . $selesai_row, (!empty($selesai_uji_kalsium[0])) ? get_str_date2($selesai_uji_kalsium[0]) : null);
        $sheet->setCellValue('K' . $selesai_row, get_str_date2($data[0]['selesai_uji_fosfor']));
        $sheet->setCellValue('T' . $selesai_row, get_str_date2($data[0]['selesai_uji_ur']));
        $sheet->setCellValue('S' . $selesai_row, get_str_date2($data[0]['selesai_uji_af']));

        $sheet->setCellValue('M' . $selesai_row, get_str_date2($data[0]['selesai_uji_betn']));
        $sheet->setCellValue('N' . $selesai_row, get_str_date2($data[0]['selesai_uji_tdn']));
        $sheet->setCellValue('O' . $selesai_row, get_str_date2($data[0]['selesai_uji_andf']));
        $sheet->setCellValue('R' . $selesai_row, get_str_date2($data[0]['selesai_uji_mbm']));

        $id_penerima_sampel = (!is_null($data[0]['penerima_sampel']) && $data[0]['penerima_sampel'] != '') ? $data[0]['penerima_sampel']: $data[0]['last_modified_user'];

        $delrow = count($data) + 19;
        $sheet->getRowDimension($delrow)->setRowHeight(0);

        
        //$id_param_uji2 = [ 1 , 2 , 3 , 4 , 5 , 7, 17, 8 ,10, 11, 13, 14 ]; #dummy data

        $c_param = [ 1 , 2 , 3 , 4 , 5 , 6 , 7 , 9 , 10, 17, 8 ,11 ,12, 13, 14 ];
        $c_width = ["C","E","F","G","H","J","K","T","S","M","N","O","R","J","F"];

        $i = 'I';
        $q = 'Q';
        $t = 'T';
        $s = 'S';

        $sheet->getColumnDimension('D')->setWidth(0.0);
        $sheet->getColumnDimension('P')->setWidth(0.0);

        foreach($c_param as $ckey => $c){
            if(!in_array($c, $id_param_uji2)){
                $sheet->getColumnDimension($c_width[$ckey])->setWidth(0.0);
                if($c_width[$ckey] == 'H'){
                    $sheet->getColumnDimension('I')->setWidth(0.0);
                    $i = $this->abjad($c_width[$ckey], $id_param_uji2, $c_param, $c_width);
                }
                if($c_width[$ckey] == 'O'){
                    $sheet->getColumnDimension('Q')->setWidth(0.0);
                    $q = $this->abjad($c_width[$ckey], $id_param_uji2, $c_param, $c_width);

                    $sheet->getColumnDimension('I')->setWidth(0.0);
                    $i = $this->abjad('H', $id_param_uji2, $c_param, $c_width);
                }
                if($c_width[$ckey] == 'T'){
                    $t  = $this->abjad($c_width[$ckey], $id_param_uji2, $c_param, $c_width);
                }

                if($c_width[$ckey] == 'S'){
                    $s  = $this->abjad($c_width[$ckey], $id_param_uji2, $c_param, $c_width);
                }

            }
        }

        $sheet->setCellValue($t . 12, 'Tanggal Mulai Pengujian     : '.get_str_date2($data_uji['tgl_mulai_uji']).' ('.get_str_time($data_uji['tgl_mulai_uji']).' WIB)');
        $sheet->setCellValue($t . 13, 'Tanggal Selesai Pengujian : ' .get_str_date2($data_uji['tgl_selesai_uji']).' ('.get_str_time($data_uji['tgl_selesai_uji']).' WIB)');

        //$penerima_sampel = $this->m_dok->get_personil_by_id($id_penerima_sampel);
        $penerima_sampel = $this->m_dok->get_jabatan(10);
        $manajer_teknis = $this->m_dok->get_jabatan(2);
        $penyelia = $this->m_dok->get_jabatan(3);

        $mt_nip_niptk = $this->set_nip_niptk($manajer_teknis);
        $py_nip_niptk = $this->set_nip_niptk($penyelia);
        $ps_nip_niptk = $this->set_nip_niptk($penerima_sampel);
        
        $tgl_row = count($data) + 19 + 5;
        $ket_lhu = $data_uji['ket_lhu'];
        if(strpos($ket_lhu, '||') != false){
            $ket_lhu = "Keterangan:||".$ket_lhu;
            $ket_lhu = explode('||', $ket_lhu);
        }else{
            $ket_lhu[] = "Keterangan:";
            $ket_lhu[] = $ket_lhu;
        }

        foreach($ket_lhu as $kkey => $k){
            //$sheet->getRowDimension($tgl_row+$kkey)->setRowHeight(70);
            $sheet->getStyle('A'.($tgl_row+$kkey))->getFont()->setSize(12);
            $sheet->getStyle('A'.($tgl_row+$kkey))->getAlignment()->setHorizontal('left');
            $sheet->setCellValue('A'.($tgl_row+$kkey), $k);
        }
        
        
        $adr = 1 + count($ket_lhu);

        $tgl_row = count($data) + 19 + 5 + $adr;
        $sheet->setCellValue('B' . $tgl_row, 'Surabaya, '.nbsp_filter(indo_datetime(date('Y-m-d'))));
        $sheet->setCellValue($i . $tgl_row, 'Surabaya, '.nbsp_filter(indo_datetime(date('Y-m-d'))));
        $sheet->setCellValue($q . $tgl_row, 'Telah diterima oleh/Jam/Tgl : '.nbsp_filter(indo_datetime(date('Y-m-d H:i:s'), 'time')));

        $ket_row = count($data) + 19 + 6 + $adr;
        $sheet->setCellValue('B' . $ket_row, "Yang membuat laporan,");
        $sheet->setCellValue($i . $ket_row, "Mengetahui,");
        $sheet->setCellValue($q . $ket_row, "Petugas Penerima Sampel");

        $ket_row = count($data) + 19 + 7 + $adr;
        $sheet->setCellValue('B' . $ket_row, "Penyelia");
        $sheet->setCellValue($i . $ket_row, "Manajer Teknis");
        
        $ttd_row = count($data) + 19 + 11 + $adr;
        $sheet->setCellValue('B' . $ttd_row, $penyelia['nama']);
        $sheet->setCellValue($i . $ttd_row, $manajer_teknis['nama']);
        $sheet->setCellValue($q . $ttd_row, $penerima_sampel['nama']);

        $nip_row = count($data) + 19 + 12 + $adr;
        $sheet->setCellValue('B' . $nip_row, $mt_nip_niptk);
        $sheet->setCellValue($i . $nip_row, $py_nip_niptk);
        $sheet->setCellValue($q . $nip_row, $ps_nip_niptk);

        $nobag_row = count($data) + 19 + 14 + $adr;
        $sheet->setCellValue($s . $nobag_row, "No. Bagian   :  FM.7.8-1");

        $hal_row = count($data) + 19 + 15 + $adr;
        $sheet->setCellValue($s . $hal_row, "Halaman      : 1 dari 1");

        $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
        $writer->save($path_file);

        $this->download_file($path_file);
    }

    function abjad($char = '', $id_param_uji = [], $c_param = [], $c_width = [], $c_width_sni = [], $between = 0)
    {
        $char2 = 0;
        $id_char = 0;
        $new_position = '';
        $abjad = ["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","AA","AB","AC","AD","AE"];

        foreach($abjad as $akey => $a){
            if($char == $a){
                $char2 = $abjad[intval(($akey)-(1)-($between))];
            }
        }

        foreach($c_width as $wkey => $w){
            if($w == $char2){
                $id_char = $c_param[$wkey];
            }
            else if(count($c_width_sni) != 0 && $c_width_sni[$wkey] == $char2){
                $id_char = $c_param[$wkey];
            }
        }


        if(in_array($id_char, $id_param_uji)){
            $this->session->set_userdata('char', $char2);
        }else{
            $this->abjad($char2, $id_param_uji, $c_param, $c_width, $c_width_sni);
        }

        return $this->session->userdata('char');
    }

    function download_tdn(){
        $param =  $this->input->get();

        $data_uji = $this->m_dok->dataUji($param['id_uji']);
        $data = $this->m_dok->getAnalis($param);

        $path_file = $this->set_directory('tdn.xlsx', 'tdn', 'template_tdn.xlsx');

        ## Initialize phpsreadsheet
        $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($path_file);
        $sheet = $spreadsheet->getActiveSheet();

        $sheet->setCellValue('C11', "No : LP-TDN/".$data[0]['registrasi_laboratorium']."/".date('m')."/".date('Y')); //Nomor dokumen

        $sheet->setCellValue('C13', "No Permintaan Lab : ".$data[0]["registrasi_laboratorium"]);

        $no_sampel = '';
        $x = 0;
        $o = 0;
        $row_data = 17;
        foreach($data as $r){
            $sheet->insertNewRowBefore($row_data + $x + 1, 1);
            $baris = $row_data + $x;
            $baris2 = $row_data+1+ $x;
            $sheet->getRowDimension($baris)->setRowHeight(20);
            $sheet->setCellValue('C' . $baris, $o + 1);
            $sheet->setCellValue('D' . $baris, "Bahan Kering");
            $sheet->setCellValue('E' . $baris, $r['bk_air']);
            $sheet->setCellValue('F' . $baris, $r['bk_abu']);
            $sheet->setCellValue('G' . $baris, $r['bk_protein_kasar']);
            $sheet->setCellValue('H' . $baris, $r['bk_lemak_kasar']);
            $sheet->setCellValue('I' . $baris, $r['bk_serat_kasar']); 

            $sheet->setCellValue('D' . $baris2, "As fed");
            $sheet->setCellValue('E' . $baris2, $r['l_air']);
            $sheet->setCellValue('F' . $baris2, $r['l_abu']);
            $sheet->setCellValue('G' . $baris2, $r['l_protein_kasar']);
            $sheet->setCellValue('H' . $baris2, $r['l_lemak_kasar']);
            $sheet->setCellValue('I' . $baris2, $r['l_serat_kasar']);

            $no_sampel .= nomor_id($r['id_sampel']).', ';

            $sheet->mergeCells("C".$baris.":C".$baris2);
            $sheet->mergeCells("J".$baris.":J".$baris2);  
            $sheet->mergeCells("K".$baris.":K".$baris2);

            $sheet->setCellValue('J' . $baris, $r['bk_betn']);
            $sheet->setCellValue('K' . $baris, $r['bk_tdn']); 

            $o++;
            $x+=2;  
        }

        $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
        $writer->save($path_file);

        $this->download_file($path_file);
    }

    function set_directory($name_file, $folder, $name_temp){
        ## Setting filename, pathfile and template directory
        $filename = DATE("YmdHis") . '_'.$name_file;
        $temp_dir = FCPATH . 'files\/'.$folder.'\/';
        if (!file_exists($temp_dir)) {
            mkdir($temp_dir, 0775, true); // create folder
        } else { // empty folder
            $files = glob($temp_dir.'*'); // get all file names
            foreach($files as $file){ // iterate files
                if(is_file($file)) unlink($file); // delete file
            }
        }
        $path_file = $temp_dir . $filename;
        if (!copy(FCPATH . 'template/'.$name_temp, $path_file)) {
            echo 'gagal load template';
            die();
        }else{
            return $path_file;
        }
    }

    function download_file($path_file){
	    $path = rand().'|'.$path_file.'|'.date('Ymdhis');
	    $token = base64_encode($path);
        //redirect("http://view.officeapps.live.com/op/view.aspx?src=".site_url()."/view_dok?token=".$token);
        force_download($path_file, null);
    }

    function download_sertifikat(){
        $param = $this->input->get();

        $data = $this->m_dok->get_sertifikat($param['id']);
        $metode = $this->m_dok->get_metode();
        $asam_amino = $this->m_dok->get_data_amino($data['id_uji']);

        $path_file = $this->set_directory('sertifikat.docx', 'sertifikat', 'template_sertifikat.docx');

        ## Initialize phpsreadsheet
        $file_template = $path_file;
        $templateProcessor = new \PhpOffice\PhpWord\TemplateProcessor($file_template);

        $items = 'nomor_sertifikat';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities('893.3/.........../122.1/'.date('Y', $data['tgl_acc_lhp'])), 1);
        $items = 'nomor_sertifikat2';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities('893.3/.........../122.1/'.date('Y', $data['tgl_acc_lhp'])), 1);
        $items = 'nomor_sni';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities($data['nomor_sni']), 1);
        $items = 'nama_produsen';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities($data['asal_sampel']), 1);
        $items = 'alamat_produsen';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities($data['alamat']), 1);
        $items = 'nama_dagang';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities($data['merk_dagang']), 1);
        $items = 'kode_pakan';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities($data['kode']), 1);
        $items = 'kode_pakan2';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities($data['kode']), 1);
        $items = 'jenis_pakan';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities($data['nama']), 1);
        $items = 'jenis_pakan2';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities($data['nama']), 1);
        $items = 'jenis_pakan3';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities($data['nama']), 1);
        $items = 'tanggal_penerimaan_contoh';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities(str_replace('&nbsp;', ' ', indo_datetime($data['tgl_pukul_registrasi']))), 1);
        $items = 'nomor_uji';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities($data['nomor_uji'].'.'.$data['nomor_sampel']), 1);
        $items = 'penilaian_uji';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities($data['nomor_uji'].'.'.$data['nomor_sampel']), 1);

        $items = 'tanggal_uji_air';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities(str_replace('&nbsp;', ' ', indo_datetime($data['mulai_uji_air']))), 1);
        $items = 'tanggal_uji_abu';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities(str_replace('&nbsp;', ' ', indo_datetime($data['mulai_uji_abu']))), 1);
        $items = 'tanggal_uji_protein_kasar';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities(str_replace('&nbsp;', ' ', indo_datetime($data['mulai_uji_protein_kasar']))), 1);
        $items = 'tanggal_uji_lemak_kasar';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities(str_replace('&nbsp;', ' ', indo_datetime($data['mulai_uji_lemak_kasar']))), 1);
        $items = 'tanggal_uji_serat_kasar';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities(str_replace('&nbsp;', ' ', indo_datetime($data['mulai_uji_serat_kasar']))), 1);
        $items = 'tanggal_uji_kalsium';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities(str_replace('&nbsp;', ' ', indo_datetime($data['mulai_uji_kalsium']))), 1);
        $items = 'tanggal_uji_fosfor';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities(str_replace('&nbsp;', ' ', indo_datetime($data['mulai_uji_fosfor']))), 1);
        $items = 'tanggal_uji_aflatoksin';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities(str_replace('&nbsp;', ' ', indo_datetime($data['mulai_uji_af']))), 1);
        $items = 'tanggal_uji_asam_amino';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities(str_replace('&nbsp;', ' ', indo_datetime($data['tgl_pukul_registrasi']))), 1);
        $items = 'tanggal_uji_urea';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities(str_replace('&nbsp;', ' ', indo_datetime($data['mulai_uji_ur']))), 1);

        $items = 'acuan_metoda_air';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities($metode[0]['detail']), 1);
        $items = 'acuan_metoda_abu';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities($metode[1]['detail']), 1);
        $items = 'acuan_metoda_protein_kasar';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities($metode[2]['detail']), 1);
        $items = 'acuan_metoda_lemak_kasar';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities($metode[4]['detail']), 1);
        $items = 'acuan_metoda_serat_kasar';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities($metode[5]['detail']), 1);
        $items = 'acuan_metoda_kalsium';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities($metode[6]['detail']), 1);
        $items = 'acuan_metoda_fosfor';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities($metode[8]['detail']), 1);
        $items = 'acuan_metoda_aflatoksin';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities($metode[11]['detail']), 1);
        $items = 'acuan_metoda_asam_amino';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities('18-5-17/MU/SMM-SIG, UPLC'), 1);
        $items = 'acuan_metoda_asam_amino2';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities('18-5-63/MU/SMM-SIG, HPLC'), 1);
        $items = 'acuan_metoda_urea';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities($metode[10]['detail']), 1);

        $items = 'hasil_air';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities($data['air']), 1);
        $items = 'hasil_abu';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities($data['abu']), 1);
        $items = 'hasil_protein_kasar';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities($data['protein_kasar']), 1);
        $items = 'hasil_lemak_kasar';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities($data['lemak_kasar']), 1);
        $items = 'hasil_serat_kasar';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities($data['serat_kasar']), 1);
        $items = 'hasil_kalsium';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities($data['kalsium']), 1);
        $items = 'hasil_fosfor';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities($data['fosfor']), 1);
        $items = 'hasil_aflatoksin';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities($data['af']), 1);
        $items = 'hasil_lisin';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities($asam_amino['lisin']), 1);
        $items = 'hasil_metionin';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities($asam_amino['metionin']), 1);
        $items = 'hasil_metionin_sistin';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities($asam_amino['metionin_sistin']), 1);
        $items = 'hasil_treonin';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities($asam_amino['treonin']), 1);
        $items = 'hasil_triptofan';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities($asam_amino['triptofan']), 1);
        $items = 'hasil_urea';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities($data['ur']), 1);
        
        $items = 'sni_air';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities($data['sni_air']), 1);
        $items = 'sni_abu';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities($data['sni_abu']), 1);
        $items = 'sni_protein_kasar';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities($data['sni_protein_kasar']), 1);
        $items = 'sni_lemak_kasar';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities($data['sni_lemak_kasar']), 1);
        $items = 'sni_serat_kasar';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities($data['sni_serat_kasar']), 1);
        $items = 'sni_kalsium';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities($data['sni_ca']), 1);
        $items = 'sni_fosfor';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities($data['sni_fosfor']), 1);
        $items = 'sni_aflatoksin';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities($data['sni_af']), 1);
        $items = 'sni_lisin';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities($asam_amino['min_lisin']), 1);
        $items = 'sni_metionin';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities($asam_amino['min_metionin']), 1);
        $items = 'sni_metionin_sistin';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities($asam_amino['min_metionin_sistin']), 1);
        $items = 'sni_treonin';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities($asam_amino['min_treonin']), 1);
        $items = 'sni_triptofan';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities($asam_amino['min_triptofan']), 1);
        $items = 'sni_urea';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities($data['sni_urea']), 1);

        $kepala_dinas_peternakan = $this->m_dok->get_jabatan(8); 
        $kepala_seksi_pakan = $this->m_dok->get_jabatan(9);

        $items = 'kepala_dinas_peternakan';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities($kepala_dinas_peternakan['nama']), 1);
        $items = 'kepala_seksi_pakan';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities($kepala_seksi_pakan['nama']), 1);
        $items = 'nip_kepala_dinas_peternakan';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities($kepala_dinas_peternakan['nip']), 1);
        $items = 'nip_kepala_seksi';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities($kepala_seksi_pakan['nip']), 1);
        $items = 'jabatan';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities($kepala_dinas_peternakan['nama']), 1);
        $items = 'tanggal_sertifikat1';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities(str_replace('&nbsp;', ' ', indo_datetime(date('Y-m-d H:i:s')))), 1);
        $items = 'tanggal_sertifikat2';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities(str_replace('&nbsp;', ' ', indo_datetime(date('Y-m-d H:i:s')))), 1);
        $items = 'tanggal_sertifikat_inggris';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities(date('M d Y')), 1);
        $items = 'tanggal_sertifikat_inggris2';
        $templateProcessor->setValue('${' . $items . '}', set_html_entities(date('M d Y')), 1);

        
        $templateProcessor->saveAS($path_file);
        $this->download_file($path_file);
        
    }

    function download_lhp_af(){
        $data = $this->input->get();
        $data['id_uji'] = $data['id'];
        $data_uji = $this->m_dok->dataUji($data['id']);
        $data = $this->m_dok->getAnalis($data);

        $nomor_permintaan_uji = nomor_id($data_uji['id']);

        $path_file = $this->set_directory('lhp_af.xlsx', 'lhp_af', 'template_lhp_af.xlsx');

        ## Initialize phpsreadsheet
        $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($path_file);
        $sheet = $spreadsheet->getActiveSheet();

        $x = 0;
        //$row_data = 25;
        $row_data = 26;
        $sheet->getRowDimension(25)->setRowHeight(0);

        $sheet->setCellValue("A26", $x + 1);
        $sheet->setCellValue('B26', nomor_id($data[0]['id_sampel']));
        $sheet->setCellValue('D26', $data[0]['nama'].'/'.$data[0]['asal_sampel']);
        $sheet->setCellValue('N26', $data[0]['l_af']);
        $sheet->setCellValue('P26', $data[0]['aflatoksin']);
        $sheet->setCellValue('N27', $data[0]['l_andf']);
        $sheet->setCellValue('P26', $data[0]['andf']);
        


        $sheet->setCellValue('Q14',': '.date('d/m/Y (H.i').' WIB)');
        $sheet->setCellValue('Q16',': '.$nomor_permintaan_uji);
        $sheet->setCellValue('Q17',': '.date('d/m/Y (H.i', strtotime('-2Day', strtotime(date('Y-m-d H:i')))).' WIB)');
        $sheet->setCellValue('Q18',': '.date('d/m/Y (H.i', strtotime('-1Day', strtotime(date('Y-m-d H:i')))).' WIB)');
        $sheet->setCellValue('Q19',': '.date('d/m/Y (H.i').' WIB)');

        $sheet->setCellValue('Q'.intval(29+count($data)),'Surabaya, '.date('d-m-Y'));

        $sheet->setCellValue('E14',': '.$data_uji['instansi_umum']);
        $sheet->setCellValue('E15',': '.$data_uji['alamat']);
        $sheet->setCellValue('E16',': '.$data[0]['gol_jenis_sampel']);
        $sheet->setCellValue('E17',': '.$data[0]['kualitas']);

        $pimpinan_unit_kerja = $this->m_dok->get_jabatan(1); // pimpinan unit kerja dan laboratorium pakan
        $sheet->setCellValue('Q'.intval(count($data)+34), $pimpinan_unit_kerja['nama']);
        $sheet->setCellValue('Q'.intval(count($data)+35), 'NIP. '.$pimpinan_unit_kerja['nip']);

        $drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
            $path = FCPATH.'assets/img/lab_pakan02.png';
            $drawing->setName('Paid');
            $drawing->setDescription('Paid');
            $drawing->setPath($path);
            $drawing->setHeight(170);
            $drawing->setCoordinates('E2');
            $drawing->setWorksheet($spreadsheet->getActiveSheet());

        $drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
            $path = FCPATH.'assets/img/lhp_kan.png';
            $drawing->setPath($path);
            $drawing->setWidth(400);
            $drawing->setHeight(90);
            $drawing->setCoordinates('O2');
            $drawing->setWorksheet($spreadsheet->getActiveSheet());

        $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
        $writer->save($path_file);

        $this->download_file($path_file);

    }

    function download_lhp_tdn(){
        $data = $this->input->get();
        $data_uji = $this->m_dok->dataUji($data['id_uji']);
        $data = $this->m_dok->getAnalis($data);

        $nomor_permintaan_uji = nomor_id($data_uji['id_uji']);

        if(count($data) > 1)
            $nomor_permintaan_uji .= ' ('.nomor_id($data[0]['id_sampel']).'-'.nomor_id($data[intval(count($data)-1)]['id_sampel']).')';
        else
            $nomor_permintaan_uji .= ' ('.$data[0]['id_sampel'].')';

        $path_file = $this->set_directory('lhp_tdn.xlsx', 'lhp_tdn', 'template_lhp_tdn.xlsx');

        ## Initialize phpsreadsheet
        $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($path_file);
        $sheet = $spreadsheet->getActiveSheet();

        $x = 0;
        //$row_data = 25;
        $row_data = 26;
        $sheet->getRowDimension(25)->setRowHeight(0);
        foreach ($data as $r) {
            $sheet->insertNewRowBefore($row_data + $x + 0, 1);
            $baris = $row_data + $x;
            $sheet->getRowDimension($baris)->setRowHeight(40);
            $sheet->setCellValue("A" . $baris, $x + 1);
            $sheet->setCellValue('B' . $baris, nomor_id($r['id_sampel']));
            $sheet->setCellValue('D' . $baris, $r['nama'].'/'.$r['asal_sampel']);

            $sheet->setCellValue('F' . $baris, $r['l_air']);
            $sheet->setCellValue('G' . $baris, $r['kadar_air']);

            $sheet->setCellValue('H' . $baris, $r['l_abu']);
            $sheet->setCellValue('I' . $baris, $r['kadar_abu']);

            $sheet->setCellValue('J' . $baris, $r['l_protein_kasar']);
            $sheet->setCellValue('K' . $baris, $r['protein_kasar']);

            $sheet->setCellValue('L' . $baris, $r['l_lemak_kasar']);
            $sheet->setCellValue('M' . $baris, $r['lemak_kasar']);

            $sheet->setCellValue('N' . $baris, $r['l_serat_kasar']);
            $sheet->setCellValue('O' . $baris, $r['serat_kasar']);

            $sheet->setCellValue('P' . $baris, $r['l_kalsium']);
            $sheet->setCellValue('Q' . $baris, $r['kalsium']);

            $sheet->setCellValue('R' . $baris, $r['l_fosfor']);
            $sheet->setCellValue('S' . $baris, $r['fosfor']);

            $sheet->setCellValue('T' . $baris, $r['bk_tdn']);
            $sheet->setCellValue('U' . $baris, $r['tdn']);

            $sheet->mergeCells("D".$baris.":E".$baris);
            $sheet->mergeCells("B".$baris.":C".$baris);

            $x++;
        }

        $sheet->setCellValue('S14',': '.date('d/m/Y (H.i').' WIB)');
        $sheet->setCellValue('S16',': '.$nomor_permintaan_uji);
        $sheet->setCellValue('S17',': '.date('d/m/Y (H.i', strtotime('-2Day', strtotime(date('Y-m-d H:i')))).' WIB)');
        $sheet->setCellValue('S18',': '.date('d/m/Y (H.i', strtotime('-1Day', strtotime(date('Y-m-d H:i')))).' WIB)');
        $sheet->setCellValue('S19',': '.date('d/m/Y (H.i').' WIB)');

        $sheet->setCellValue('S'.intval(29+count($data)),'Surabaya, '.str_replace('&nbsp;',' ', indo_datetime(date('Y-m-d H:i:s'))));

        $sheet->setCellValue('E14',': '.$data_uji['instansi_umum']);
        $sheet->setCellValue('E15',': '.$data_uji['alamat']);
        $sheet->setCellValue('E16',': '.$data[0]['gol_jenis_sampel']);
        $sheet->setCellValue('E17',': '.$data[0]['kualitas']);

        $pimpinan_unit_kerja = $this->m_dok->get_jabatan(1); // pimpinan unit kerja dan laboratorium pakan
        $sheet->setCellValue('S'.intval(count($data)+34), $pimpinan_unit_kerja['nama']);
        $sheet->setCellValue('S'.intval(count($data)+35), 'NIP. '.$pimpinan_unit_kerja['nip']);

        $sheet->setCellValue('D'.intval(count($data)+39), str_replace('&nbsp;',' ', indo_datetime(date('Y-m-d H:i:s'))));

        $drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
            $path = FCPATH.'assets/img/lab_pakan02.png';
            $drawing->setName('Paid');
            $drawing->setDescription('Paid');
            $drawing->setPath($path);
            $drawing->setHeight(170);
            $drawing->setCoordinates('E2');
            $drawing->setWorksheet($spreadsheet->getActiveSheet());

        $drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
            $path = FCPATH.'assets/img/lhp_kan.png';
            $drawing->setPath($path);
            $drawing->setWidth(400);
            $drawing->setHeight(90);
            $drawing->setCoordinates('O2');
            $drawing->setWorksheet($spreadsheet->getActiveSheet());

        $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
        $writer->save($path_file);

        $this->download_file($path_file);
    }

    public function download_kelengkapan_administrasi($id_uji, $id_status)
    {
        $data =  $this->m_dok->get_kelengkapan_administrasi($id_uji);
        $id_sampel = $this->m_dok->get_id_sampel_by_id_uji($id_uji);

        $path_file = $this->set_directory('kelengkapan_administrasi.xlsx', 'kelengkapan_administrasi', 'template_kelengkapan_administrasi.xlsx');
        //$penerima_sampel = $this->m_dok->get_jabatan(5);
        $id_penerima_sampel = (!is_null($data[0]['penerima_sampel']) && $data[0]['penerima_sampel'] != '') ? $data[0]['penerima_sampel']: $data[0]['last_modified_user'];
        $penerima_sampel = $this->m_dok->get_personil_by_id($id_penerima_sampel);
        $pemohon_sampel = $data['nama_pemohon'];
        $sekretaris_dinas = $this->m_dok->get_jabatan(6);

        $sd_nip_niptk = $this->set_nip_niptk($sekretaris_dinas);
        $ps_nip_niptk = $this->set_nip_niptk($penerima_sampel);

        ## Initialize phpsreadsheet
        $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($path_file);
        $sheet = $spreadsheet->getActiveSheet();

        $x = 0;
        $row_data = 26;
        $sheet->getRowDimension(25)->setRowHeight(0);
        
        $sheet->setCellValue("E14", ': '.$data['id'].'/'.$id_sampel);
        $sheet->setCellValue("E15", ': '.str_replace('&nbsp;', ' ', indo_datetime($data['tgl_pukul_registrasi'], 'time')));
        $sheet->setCellValue("E16", ': '.str_replace('&nbsp;', ' ', indo_datetime($data['perkiraan_estimasi_selesai_lhp'])));
        $sheet->setCellValue("E17", ': '.$data['nama_pemohon']);
        $sheet->setCellValue("E18", ': '.$data['instansi_umum']);
        $sheet->setCellValue("E19", ': '.$data['alamat']);
        $sheet->setCellValue("E20", ': '.$data['nomor_telepon']);
        $sheet->setCellValue("E21", ': '.$data['instansi_umum']);
        $sheet->setCellValue('E10', 'No : LP-KASSMKP/'.nomor_id($data[0]['id_uji']).'/'.angka_romawi(date('m')).'/'.date('m'));
        
        $sheet->setCellValue("F27", '√');
        $sheet->setCellValue("F28", '√');
        $sheet->setCellValue("F29", '√');
        $sheet->setCellValue("F30", '√');
        $sheet->setCellValue("F31", '√');

        $sheet->setCellValue("G27", '√');
        $sheet->setCellValue("G28", '√');
        $sheet->setCellValue("G29", '√');
        $sheet->setCellValue("G30", '√');
        $sheet->setCellValue("G31", '√');

        
        $sheet->setCellValue('F43', $penerima_sampel['nama']);
        $sheet->setCellValue('G43', $data['nama_pemohon']);
        $sheet->setCellValue('C43', $sekretaris_dinas['nama']);

        $sheet->setCellValue('C44', $sd_nip_niptk);
        $sheet->setCellValue('F44', $ps_nip_niptk);
        

        $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
        $writer->save($path_file);

        $this->download_file($path_file);
    }

    public function download_kelengkapan_administrasi_vp($id_uji, $id_status)
    {
        $data =  $this->m_dok->get_kelengkapan_administrasi($id_uji);
        $id_sampel = $this->m_dok->get_id_sampel_by_id_uji($id_uji);

        $path_file = $this->set_directory('kelengkapan_administrasi_vp.xlsx', 'kelengkapan_administrasi_vp', 'template_kelengkapan_administrasi_vp.xlsx');
        //$penerima_sampel = $this->m_dok->get_jabatan(5);
        $id_penerima_sampel = (!is_null($data[0]['penerima_sampel']) && $data[0]['penerima_sampel'] != '') ? $data[0]['penerima_sampel']: $data[0]['last_modified_user'];
        $penerima_sampel = $this->m_dok->get_personil_by_id($id_penerima_sampel);

        $pemohon_sampel = $data['nama_pemohon'];
        $sekretaris_dinas = $this->m_dok->get_jabatan(6);
        
        ## Initialize phpsreadsheet
        $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($path_file);
        $sheet = $spreadsheet->getActiveSheet();

        $x = 0;
        $row_data = 26;
        $sheet->getRowDimension(25)->setRowHeight(0);
        
        $sheet->setCellValue("E14", ': '.$data['id'].'/'.$id_sampel);
        $sheet->setCellValue("E15", ': '.str_replace('&nbsp;', ' ', indo_datetime($data['tgl_pukul_registrasi'], 'time')));
        $sheet->setCellValue("E16", ': '.str_replace('&nbsp;', ' ', indo_datetime($data['perkiraan_estimasi_selesai_lhp'])));
        $sheet->setCellValue("E17", ': '.$data['nama_pemohon']);
        $sheet->setCellValue("E18", ': '.$data['instansi_umum']);
        $sheet->setCellValue("E19", ': '.$data['alamat']);
        $sheet->setCellValue("E20", ': '.$data['nomor_telepon']);
        $sheet->setCellValue("E21", ': '.$data['instansi_umum']);
        $sheet->setCellValue('E10', 'No : LP-KASSMKP/'.nomor_id($data[0]['id_uji']).'/'.angka_romawi(date('m')).'/'.date('m'));
        
        $sheet->setCellValue("F27", '√');
        $sheet->setCellValue("F28", '√');
        $sheet->setCellValue("F29", '√');
        $sheet->setCellValue("F30", '√');
        $sheet->setCellValue("F31", '√');

        $sheet->setCellValue("G27", '√');
        $sheet->setCellValue("G28", '√');
        $sheet->setCellValue("G29", '√');
        $sheet->setCellValue("G30", '√');
        $sheet->setCellValue("G31", '√');

        
        $sheet->setCellValue('F43', $penerima_sampel['nama']);
        //   $sheet->setCellValue('G43', $data['nama_pemohon']);
        $sheet->setCellValue('C43', $sekretaris_dinas['nama']);

        $sd_nip_niptk = $this->set_nip_niptk($sekretaris_dinas);
        $ps_nip_niptk = $this->set_nip_niptk($penerima_sampel);
        $sheet->setCellValue('C44', $sd_nip_niptk);
        $sheet->setCellValue('F44', $ps_nip_niptk);
        

        $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
        $writer->save($path_file);

        $this->download_file($path_file);
    }

    public function nbsp_filter($var = '')
    {
        if($var != ''){
            $var = str_replace('&nbsp;', ' ', $var);
        }

        return $var;
    }
}
