<?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_copy.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);

        $x = 1;
        foreach($data as $r){
            $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(nomor_id($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';
            $items = $item.'1#'.$x;
            $templateProcessor->setValue('${' . $items . '}', set_html_entities(strpos($r[$item], '1|') !== false ? '✓' : '-'), 1);
            $items = $item.'2#'.$x;
            $templateProcessor->setValue('${' . $items . '}', set_html_entities(strpos($r[$item], '2|') !== false ? '✓' : '-'), 1);
            $items = $item.'3#'.$x;
            $templateProcessor->setValue('${' . $items . '}', set_html_entities(strpos($r[$item], '3|') !== false ? '✓' : '-'), 1);
            $items = $item.'4#'.$x;
            $templateProcessor->setValue('${' . $items . '}', set_html_entities(strpos($r[$item], '4|') !== false ? '✓' : '-'), 1);
            $items = $item.'5#'.$x;
            $templateProcessor->setValue('${' . $items . '}', set_html_entities(strpos($r[$item], '5|') !== false ? '✓' : '-'), 1);
            $items = $item.'6#'.$x;
            $templateProcessor->setValue('${' . $items . '}', set_html_entities(strpos($r[$item], '6|') !== false ? '✓' : '-'), 1);
            $items = $item.'7#'.$x;
            $templateProcessor->setValue('${' . $items . '}', set_html_entities(strpos($r[$item], '7|') !== false ? '✓' : '-'), 1);
            $items = $item.'8#'.$x;
            $templateProcessor->setValue('${' . $items . '}', set_html_entities(strpos($r[$item], '8|') !== false ? '✓' : '-'), 1);
            $items = $item.'9#'.$x;
            $templateProcessor->setValue('${' . $items . '}', set_html_entities(strpos($r[$item], '9|') !== false ? '✓' : '-'), 1);
            $items = $item.'10#'.$x;
            $templateProcessor->setValue('${' . $items . '}', set_html_entities(strpos($r[$item], '10|') !== false ? '✓' : '-'), 1);
            $items = $item.'11#'.$x;
            $templateProcessor->setValue('${' . $items . '}', set_html_entities(strpos($r[$item], '11|') !== false ? '✓' : '-'), 1);
            $items = $item.'12#'.$x;
            $templateProcessor->setValue('${' . $items . '}', set_html_entities(strpos($r[$item], '12|') !== false ? '✓' : '-'), 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);

            $x++;
        }

        $templateProcessor->saveAS($path_file);
        //force_download($path_file, null);
        $this->download_file($path_file);
    }

    function cetak_lhp() {
        $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.xlsx', 'lhp', 'template_lhp.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_jenis'].'/'.$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['l_ur']);
            $sheet->setCellValue('U' . $baris, $r['fosfor']);

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

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

            $x++;
        }

        $sheet->setCellValue('U14',': '.date('d/m/Y (H.i').' WIB)');
        $sheet->setCellValue('U16',': '.$nomor_permintaan_uji);
        $sheet->setCellValue('U17',': '.date('d/m/Y (H.i', strtotime('-2Day', strtotime(date('Y-m-d H:i')))).' WIB)');
        $sheet->setCellValue('U18',': '.date('d/m/Y (H.i', strtotime('-1Day', strtotime(date('Y-m-d H:i')))).' WIB)');
        $sheet->setCellValue('U19',': '.date('d/m/Y (H.i').' WIB)');

        $sheet->setCellValue('U'.intval(29+count($data)),'Surabaya, '.str_replace('&nbsp;',' ', set_indo_date_from_timestemp(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']);

        $sheet->setCellValue('R8','Lampiran Surat : Surat Kepala Dinas  Peternakan Provinsi Jawa Timur
        Tanggal  :        '.str_replace('&nbsp;',' ', set_indo_date_from_timestemp(date('Y-m-d H:i:s'))).'        
        Nomor    :  524.6/           /122.2/ '.date('Y').'
        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('U'.intval(count($data)+34), $pimpinan_unit_kerja['nama']);
        $sheet->setCellValue('U'.intval(count($data)+35), 'NIP. '.$pimpinan_unit_kerja['nip']);

        $sheet->setCellValue('D'.intval(count($data)+39), ': '.str_replace('&nbsp;',' ', set_indo_date_from_timestemp(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('F2');
            $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('Q2');
            $drawing->setWorksheet($spreadsheet->getActiveSheet());

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

        $this->download_file($path_file);
    }

    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_jenis'].'/'.$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(){
        $param =  $this->input->get();
        $data =  $this->m_dok->get_npps($param, 'uji');

        $path_file = $this->set_directory('npps.xlsx', 'npps', 'template_npps_2020.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;
            }

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

            $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['merek_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, $parameter_uji[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');
        $vp = array('sdm','metode','peralatan');
        $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', ': '.nomor_id($data[0]['id_uji']).' ('.nomor_id($data[0]['id_sampel']).$edata.')');
        $sheet->setCellValue('D14', ': '.set_indo_time($data[0]['tgl_pukul_registrasi']));
        $sheet->setCellValue('D15', ': '.set_indo_time($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);
        $pemohon_sampel = $data[0]['nama_pemohon'];

        $sheet->setCellValue('B'.intval(count($data)+78), $sekretaris_dinas['nama']);
        $sheet->setCellValue('B'.intval(count($data)+79), 'NIP. '.$sekretaris_dinas['nip']);
        $sheet->setCellValue('F'.intval(count($data)+78), $penerima_sampel['nama']);
        $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 download_ttpp() {
      $param =  $this->input->get();
      $data =  $this->m_dok->get_ttpp($param, 'uji');
      $bendahara_pp = $this->m_dok->get_jabatan(7); // bendahara penerima pembantu

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

      ## 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);
          $sheet->setCellValue('C' . $baris, $r['nama_jenis']);
          $sheet->setCellValue('E' . $baris, $r['total']);
          $sheet->setCellValue('H' . $baris, $r['harga_uji']);
          $sheet->setCellValue('J' . $baris, $r['total_harga_uji']);

          $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['total_harga_uji'];
        }
          $x++;
      }

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

      $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', ': '.nomor_id($data[0][0]['id_uji']).'/'.$sdata.$edata);
      $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), 'NIP. '.$bendahara_pp['nip']);
      $sheet->setCellValue('I8', 'Surabaya, '.set_indo_date_from_timestemp(date('Y-m-d H:i:s'), 'cetak'));

      $sheet->setCellValue('D11', 'No : LP-TTPP/'.$sdata.$endat.'/'.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;
      foreach ($data as $r) {
        $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['l_air']);
        $sheet->setCellValue('C' . $baris, $r['l_abu']);
        $sheet->setCellValue('D' . $baris, $r['l_protein_kasar']);
        $sheet->setCellValue('E' . $baris, $r['l_lemak_kasar']);
        $sheet->setCellValue('F' . $baris, $r['l_protein_kasar']);
        $sheet->setCellValue('G' . $baris, $r['l_serat_kasar']);
        $sheet->setCellValue('H' . $baris, $r['l_kalsium']);
        $sheet->setCellValue('I' . $baris, $r['l_fosfor']);
        $sheet->setCellValue('J' . $baris, $r['l_ur']);
        $sheet->setCellValue('K' . $baris, $r['l_af']);

        $p_uji = $this->m_dok->get_parameter($r['id_sampel']);

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

        $x++;
      }

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

      $sheet->setCellValue('J' . 12, ': '.toi_date03($data[0]['mulai_uji_air']).' ('.date('H.i').' WIB)');
      $sheet->setCellValue('J' . 13, ': '.toi_date03($data[0]['selesai_uji_air']).' ('.date('H.i').' WIB)');

      $sheet->setCellValue('D' . 12, ': '.$data_uji['registrasi_laboratorium'].'/'.substr($no_sampel,0,-2));
      $sheet->setCellValue('D' . 13, ': '.count($data).'('.strtolower(angka_string(count($data))).')');

      $mulai_row = count($data) + 19 + 2;
      $sheet->setCellValue('B' . $mulai_row, toi_date03($r['mulai_uji_air']));
      $sheet->setCellValue('C' . $mulai_row, toi_date03($r['mulai_uji_abu']));
      $sheet->setCellValue('D' . $mulai_row, toi_date03($r['mulai_uji_protein_kasar']));
      $sheet->setCellValue('E' . $mulai_row, toi_date03($r['mulai_uji_lemak_kasar']));
      $sheet->setCellValue('F' . $mulai_row, toi_date03($r['mulai_uji_protein_kasar']));
      $sheet->setCellValue('G' . $mulai_row, toi_date03($r['mulai_uji_serat_kasar']));
      $sheet->setCellValue('H' . $mulai_row, toi_date03($r['mulai_uji_kalsium']));
      $sheet->setCellValue('I' . $mulai_row, toi_date03($r['mulai_uji_fosfor']));
      $sheet->setCellValue('J' . $mulai_row, toi_date03($r['mulai_uji_ur']));
      $sheet->setCellValue('K' . $mulai_row, toi_date03($r['mulai_uji_af']));

      $selesai_row = count($data) + 19 + 3;
      $sheet->setCellValue('B' . $selesai_row, toi_date03($r['selesai_uji_air']));
      $sheet->setCellValue('C' . $selesai_row, toi_date03($r['selesai_uji_abu']));
      $sheet->setCellValue('D' . $selesai_row, toi_date03($r['selesai_uji_protein_kasar']));
      $sheet->setCellValue('E' . $selesai_row, toi_date03($r['selesai_uji_lemak_kasar']));
      $sheet->setCellValue('F' . $selesai_row, toi_date03($r['selesai_uji_protein_kasar']));
      $sheet->setCellValue('G' . $selesai_row, toi_date03($r['selesai_uji_serat_kasar']));
      $sheet->setCellValue('H' . $selesai_row, toi_date03($r['selesai_uji_kalsium']));
      $sheet->setCellValue('I' . $selesai_row, toi_date03($r['selesai_uji_fosfor']));
      $sheet->setCellValue('J' . $selesai_row, toi_date03($r['selesai_uji_ur']));
      $sheet->setCellValue('K' . $selesai_row, toi_date03($r['selesai_uji_af']));


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

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

      $this->download_file($path_file);
    }

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

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

      //print('<pre>'.print_r($data, true).'</pre>');

      $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);
      //redirect("https://docs.google.com/viewer?url=".site_url()."/view_dok?token=".$token);
      echo $path_file;
      force_download($path_file);
    }

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

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

      //print('<pre>'.print_r($metode, true).'</pre>');
      //print('<pre>'.print_r($asam_amino, true).'</pre>'); die();
      $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 = 'nama_laboratorium_penguji';
      $templateProcessor->setValue('${' . $items . '}', set_html_entities('Laboratorium Pakan Propinsi Jawa Timur'), 1);
      $items = 'alamat_laboratorium';
      $templateProcessor->setValue('${' . $items . '}', set_html_entities('Jl. Jend. A. Yani 202'), 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['merek_dagang']), 1);
      $items = 'kode_pakan';
      $templateProcessor->setValue('${' . $items . '}', set_html_entities($data['kode_jenis']), 1);
      $items = 'kode_pakan2';
      $templateProcessor->setValue('${' . $items . '}', set_html_entities($data['kode_jenis']), 1);
      $items = 'jenis_pakan';
      $templateProcessor->setValue('${' . $items . '}', set_html_entities($data['nama_jenis']), 1);
      $items = 'jenis_pakan2';
      $templateProcessor->setValue('${' . $items . '}', set_html_entities($data['nama_jenis']), 1);
      $items = 'jenis_pakan3';
      $templateProcessor->setValue('${' . $items . '}', set_html_entities($data['nama_jenis']), 1);
      $items = 'tanggal_penerimaan_contoh';
      $templateProcessor->setValue('${' . $items . '}', set_html_entities(str_replace('&nbsp', ' ', set_indo_date_from_timestemp($data['tgl_pukul_registrasi']))), 1);
      $items = 'nomor_uji';
      $templateProcessor->setValue('${' . $items . '}', set_html_entities(nomor_id($data['id_uji'])), 1);
      $items = 'penilaian_uji';
      $templateProcessor->setValue('${' . $items . '}', set_html_entities(nomor_id($data['id_uji'])), 1);

      $items = 'tanggal_uji_air';
      $templateProcessor->setValue('${' . $items . '}', set_html_entities(str_replace('&nbsp;', ' ', set_indo_date_from_timestemp($data['mulai_uji_air']))), 1);
      $items = 'tanggal_uji_abu';
      $templateProcessor->setValue('${' . $items . '}', set_html_entities(str_replace('&nbsp;', ' ', set_indo_date_from_timestemp($data['mulai_uji_abu']))), 1);
      $items = 'tanggal_uji_protein_kasar';
      $templateProcessor->setValue('${' . $items . '}', set_html_entities(str_replace('&nbsp;', ' ', set_indo_date_from_timestemp($data['mulai_uji_protein_kasar']))), 1);
      $items = 'tanggal_uji_lemak_kasar';
      $templateProcessor->setValue('${' . $items . '}', set_html_entities(str_replace('&nbsp;', ' ', set_indo_date_from_timestemp($data['mulai_uji_lemak_kasar']))), 1);
      $items = 'tanggal_uji_serat_kasar';
      $templateProcessor->setValue('${' . $items . '}', set_html_entities(str_replace('&nbsp;', ' ', set_indo_date_from_timestemp($data['mulai_uji_serat_kasar']))), 1);
      $items = 'tanggal_uji_kalsium';
      $templateProcessor->setValue('${' . $items . '}', set_html_entities(str_replace('&nbsp;', ' ', set_indo_date_from_timestemp($data['mulai_uji_kalsium']))), 1);
      $items = 'tanggal_uji_fosfor';
      $templateProcessor->setValue('${' . $items . '}', set_html_entities(str_replace('&nbsp;', ' ', set_indo_date_from_timestemp($data['mulai_uji_fosfor']))), 1);
      $items = 'tanggal_uji_aflatoksin';
      $templateProcessor->setValue('${' . $items . '}', set_html_entities(str_replace('&nbsp;', ' ', set_indo_date_from_timestemp($data['mulai_uji_af']))), 1);
      $items = 'tanggal_uji_asam_amino';
      $templateProcessor->setValue('${' . $items . '}', set_html_entities(str_replace('&nbsp;', ' ', set_indo_date_from_timestemp($data['tgl_pukul_registrasi']))), 1);
      $items = 'tanggal_uji_urea';
      $templateProcessor->setValue('${' . $items . '}', set_html_entities(str_replace('&nbsp;', ' ', set_indo_date_from_timestemp($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_pk']), 1);
      $items = 'sni_lemak_kasar';
      $templateProcessor->setValue('${' . $items . '}', set_html_entities($data['sni_lk']), 1);
      $items = 'sni_serat_kasar';
      $templateProcessor->setValue('${' . $items . '}', set_html_entities($data['sni_sk']), 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_p']), 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(''), 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_jenis']), 1);
      $items = 'tanggal_sertifikat1';
      $templateProcessor->setValue('${' . $items . '}', set_html_entities(str_replace('&nbsp;', ' ', set_indo_date_from_timestemp(date('Y-m-d H:i:s')))), 1);
      $items = 'tanggal_sertifikat2';
      $templateProcessor->setValue('${' . $items . '}', set_html_entities(str_replace('&nbsp;', ' ', set_indo_date_from_timestemp(date('Y-m-d H:i:s')))), 1);

      
      $templateProcessor->saveAS($path_file);
      //force_download($path_file, null);
      $this->download_file($path_file);
      
    }

    function download_lhp_af(){
      $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']);

      //print('<pre>'.print_r($data, true).'</pre>'); die();

      $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);
        /*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_jenis'].'/'.$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' . $bacolspan="2"ris, $r['l_kalsium']);
            $sheet->setCellValue('Q' . $baris, $r['kalsium']);

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

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

            $x++;
        }*/

        //$sheet->mergeCells("D25:E25");
        //$sheet->mergeCells("B".$baris.":C".$baris);


        $sheet->setCellValue("A26", $x + 1);
        $sheet->setCellValue('B26', nomor_id($data[0]['id_sampel']));
        $sheet->setCellValue('D26', $data[0]['nama_jenis'].'/'.$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_jenis'].'/'.$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;',' ', set_indo_date_from_timestemp(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;',' ', set_indo_date_from_timestemp(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);
    }
}
