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

class Import extends MY_Controller {
	public function __construct(){
        parent::__construct();
		$this->load->model('pkwt/perpanjang/Import_m');
        $this->load->model('pkwt/master/template_m');
        $this->load->library(array('PHPExcel/Excel', 'uploadfile'));
    }

    public function index(){
		$data['judul'] = 'PKWT Perpanjangan';
		$data['aktif'] = 'pkwt_perpanjangan';
        $data['menu'] = $this->load->view('main_menu', $data, true);
		$data['content'] = $this->load->view('pkwt/perpanjang/import/home', $data, true);
		$this->load->view('main_template', $data, false);
	}

    public function list_data(){
		$list = $this->Import_m->get_datatables();
		$data = array();
		$no = $_GET['start'];
		foreach ($list as $field) {
			$no++;
			$row = array();
			$row[] = $no;
			$row[] = $field->nm_karyawan. "<br><b>". $field->nik.'</b>';
			$row[] = $field->nm_jabatan. "<br><b>". $field->nm_lvl_user.'</b>';
			$row[] = $field->nm_cabang. "<br>". $field->nm_departemen;
			$row[] = indo_date(strtotime($field->tgl_pkwt_mulai), "d F Y"). " <br>s/d<br>". indo_date(strtotime($field->tgl_pkwt_selesai), "d F Y");
			$row[] = '<a href="'.site_url('pkwt/perpanjangan/download/pdf/'.md5($field->id_pkwt_temp)).'/temp" title="Detail" class="text-primary" target="_blank">
							<i class="btn_detail fa fa-info-circle (alias) "> </i>
						</a>&nbsp;
						<a href="#" class="text-warning" title="EDIT">
							<i id="' . md5($field->id_pkwt_temp) . '" class="btn_edit_pkwt fa fa-edit (alias) "> </i></a>&nbsp;
						<a href="#" class="text-danger" title="HAPUS">
							<i id="' . md5($field->id_pkwt_temp) . '" class="btn_delete_pkwt fa fa-trash"></i>
						</a>';

            $row[] = $field->flag;
			$data[] = $row;
		}

		$output = array("draw"            => $_GET['draw'],
						"recordsTotal"    => $this->Import_m->count_all(),
						"recordsFiltered" => $this->Import_m->count_filtered(),
						"data"            => $data);

		$output = __response($this->MY_response, $output);

		echo json_encode($output);
	}

    public function form_template(){
        $this->load->view('pkwt/perpanjang/import/form_template');
    }

    public function form_edit($id=null){
		$data['data'] = $this->Import_m->getDataById($id);
		$this->load->view('pkwt/perpanjang/import/form',$data);
	}

    public function update(){
        $this->nama_lengkap = $this->session->userdata('nama_user');
		$this->nik 			= $this->session->userdata('nik');

		$id_karyawan 		= $this->input->post('id_karyawan');
		$nik 				= $this->input->post('nik');
		$nm_karyawan 		= $this->input->post('nm_karyawan');
		$lahir_tempat 		= $this->input->post('lahir_tempat');
		$lahir_tgl 			= $this->input->post('lahir_tgl');
		$alamat 			= $this->input->post('alamat');
		$nm_level_user		= $this->input->post('nm_level_user');
		$nm_jabatan 		= $this->input->post('nm_jabatan');
		$nm_departemen 		= $this->input->post('nm_departemen');
		$nm_cabang 			= $this->input->post('nm_cabang');
		$tgl_pkwt 			= $this->input->post('tgl_pkwt');
		$tgl_pkwt_mulai 	= $this->input->post('tgl_pkwt_mulai');
		$tgl_pkwt_selesai 	= $this->input->post('tgl_pkwt_selesai');

        $tgl_pkwt             = date('Y-m-d', strtotime(str_replace("/", "-", $tgl_pkwt)));
        $tgl_pkwt_mulai       = date('Y-m-d', strtotime(str_replace("/", "-", $tgl_pkwt_mulai)));
        $tgl_pkwt_selesai     = date('Y-m-d', strtotime(str_replace("/", "-", $tgl_pkwt_selesai)));
        $draft_pkwt_parameter = $this->input->post('draft_pkwt_parameter');

		$draft_pkwt_value = [];
		foreach(json_decode($draft_pkwt_parameter, 1) as $key => $value) {
			$draft_pkwt_value[$key] = $value == 'nominal' ? str_replace('.','',htmlentities($this->input->post('param_'.$key))) : htmlentities($this->input->post('param_'.$key));
		}

		$id_pkwt			= $this->input->post('id_pkwt');
        $id_pkwt_decrypt	= $this->_get_id_pkwt($id_pkwt);

		$data_pkwt	= array('id_karyawan'		=> $id_karyawan,
							'tgl_pkwt'			=> $tgl_pkwt,
							'tgl_pkwt_mulai'	=> $tgl_pkwt_mulai,
							'tgl_pkwt_selesai'	=> $tgl_pkwt_selesai,
							'status'			=> '0',
							'apv_karyawan'		=> '0',
							'apv_direksi'		=> '0',
							'id_company'		=> $this->id_company,
							'tgl_update'		=> date("Y-m-d H:i:s"),
							'user_update'		=> $this->nik. " - ". $this->nama_lengkap,
						);
        if(count($draft_pkwt_value) > 0) {
            $data_pkwt['draft_pkwt_value'] = json_encode($draft_pkwt_value);
        }
        $this->db->trans_start();

        $this->db->where('MD5(id_pkwt_temp)', $id_pkwt);
        $this->db->where('id_company', $this->id_company);
        $this->db->update('pkwt_data_temp', $data_pkwt);

        $this->db->trans_complete();
        unlink($dir_file);
        if ($this->db->trans_status() === FALSE) {
            $this->db->trans_rollback();
            $message = '<font color="#FF0000"><i class="fa fa-exclamation-triangle">&nbsp;</i>Data gagal diperbahurui</font>';
            return __response_update(false,["message"=>$message]);
        }else{
            $this->db->trans_commit();
            $message = '<font color="#008000"><i class="fa fa-check-square">&nbsp;</i>Data berhasil diperbahurui</font>';
            return __response_update(true,["message"=>$message]);
        }
    }

    private function _get_id_pkwt($id){
		$query = $this->db->select('id_pkwt')
							->from('pkwt_data')
							->where('id_company', $this->id_company)
							->where('MD5(id_pkwt)', $id)
							->get()
							->row()
							->id_pkwt;
		return $query;
	}

    public function delete(){
		$id_pkwt = $this->input->post('id_pkwt');
		$this->db->trans_start();

		$this->db->where('id_company', $this->id_company);
		$this->db->where('MD5(id_pkwt_temp)', $id_pkwt);
		$this->db->delete('pkwt_data_temp');

		$this->db->trans_complete();

		if ($this->db->trans_status() === FALSE) {
			$this->db->trans_rollback();
			$message = '<font color="#FFFFF"><i class="fa fa-exclamation-triangle">&nbsp;</i>Data gagal dihapus</font>';
			return __response_update(false,["message"=>$message]);
		}else{
			$this->db->trans_commit();
			$message = '<font color="#FFFFF"><i class="fa fa-check-square">&nbsp;</i>Data berhasil dihapus</font>';
			return __response_update(true,["message"=>$message]);
		}
    }

    public function template_excel(){
        $id_template   = $this->input->get("id_template");
        $id_cabang     = $this->input->get("id_cabang");
        $id_departemen = $this->input->get("id_departemen");
        $id_kar_arr    = $this->input->get("id_karyawan");
        $id_karyawan   = explode(",", $id_kar_arr);

        #FILENAME DAN NAMA HEADER
        $filename = "Template Import PKWT";
        if($id_cabang != "all") {
            $nama_cabang = "Cabang ". $this->fungsi->get_nama_cab($id_cabang);
            $filename .= " ". $nama_cabang;
        } else {
            $nama_cabang = "Semua Cabang";
            $filename .= " ". $nama_cabang;
        }

        if($id_departemen != "all") {
            $nama_departemen = "Departemen ". $this->fungsi->get_data_nama_dep($id_departemen);
            $filename .= " ". $nama_departemen;
        } else {
            $nama_departemen = "Semua Departemen";
            $filename .= " ". $nama_departemen;
        }

        # Loop nama karyawan
        $nama_karyawan_arr = $this->Import_m->nama_karyawan_arr($id_karyawan);
        $hasil = '';
        foreach($nama_karyawan_arr as $karyawan) {
            $hasil .= $karyawan->nama_lengkap . ", ";
        }
        $hasil = rtrim($hasil, ', ');

        if($id_kar_arr != null) {
            $nama_karyawan = " ". $hasil;
            $filename .= " ". $nama_karyawan;
        } else {
            $nama_karyawan = "Semua Karyawan";
            $filename .= " ". $nama_karyawan;
        }

         // Load plugin PHPExcel nya
         include APPPATH.'third_party/PHPExcel/PHPExcel.php';

         // Panggil class PHPExcel nya
         $excel = new PHPExcel();
         // Settingan awal fil excel
         $excel->getProperties()->setCreator('Absenku')
                     ->setLastModifiedBy('Absenku')
                     ->setTitle("Template Import PKWT")
                     ->setSubject("Template Import PKWT")
                     ->setDescription("Template Import PKWT Absenku");
         // Buat sebuah variabel untuk menampung pengaturan style dari header tabel
         $style_header = array(
             'font' => array('bold' => true), // Set font nya jadi bold
             'alignment' => array(
                 'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER, // Set text jadi ditengah secara horizontal (center)
                 'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER // Set text jadi di tengah secara vertical (middle)
             ),
         );
         // Buat sebuah variabel untuk menampung pengaturan style dari isi tabel
         $style_row = array(
             'alignment' => array(
                 'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER // Set text jadi di tengah secara vertical (middle)
             ),
             'borders' => array(
                 'top' => array('style'  => PHPExcel_Style_Border::BORDER_THIN), // Set border top dengan garis tipis
                 'right' => array('style'  => PHPExcel_Style_Border::BORDER_THIN),  // Set border right dengan garis tipis
                 'bottom' => array('style'  => PHPExcel_Style_Border::BORDER_THIN), // Set border bottom dengan garis tipis
                 'left' => array('style'  => PHPExcel_Style_Border::BORDER_THIN) // Set border left dengan garis tipis
             )
         );

         $style = $excel->getActiveSheet()->getStyle('A5:F6');
         $style->getFill()
             ->setFillType(PHPExcel_Style_Fill::FILL_SOLID)
             ->getStartColor()->setRGB('ebf1de');
 
         $excel->getActiveSheet()->setSharedStyle($style, 'A5:F6');

         $excel->setActiveSheetIndex(0)->setCellValue('A1', "TEMPLATE IMPORT PKWT"); 
         $excel->getActiveSheet()->mergeCells('A1:H1'); 
         $excel->getActiveSheet()->getStyle('A1')->getFont()->setBold(TRUE); 
         $excel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

         $excel->setActiveSheetIndex(0)->setCellValue('A2', $nama_cabang.' '.$nama_departemen); 
         $excel->getActiveSheet()->mergeCells('A2:H2'); 
         $excel->getActiveSheet()->getStyle('A2')->getFont()->setBold(TRUE); 
         $excel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

         $excel->setActiveSheetIndex(0)->setCellValue('A3', $nama_karyawan); 
         $excel->getActiveSheet()->mergeCells('A3:H3'); 
         $excel->getActiveSheet()->getStyle('A3')->getFont()->setBold(TRUE); 
         $excel->getActiveSheet()->getStyle('A3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

         $excel->setActiveSheetIndex(0)->setCellValue('A4', "Catatan : tanda * wajib diisi");
         $excel->getActiveSheet()->mergeCells('A4:H4'); 
         $excel->getActiveSheet()->getStyle('A4')->getFont()->setBold(TRUE);

        //header
        $excel->setActiveSheetIndex(0)->setCellValue('A5', "NO");
        $excel->setActiveSheetIndex(0)->setCellValue('B5', "NIK *");
        $excel->setActiveSheetIndex(0)->setCellValue('C5', "NAMA");
        $excel->setActiveSheetIndex(0)->setCellValue('D5', "JABATAN"); 
        $excel->setActiveSheetIndex(0)->setCellValue('E5', "CABANG");
        $excel->setActiveSheetIndex(0)->setCellValue('F5', "DEPARTEMEN");
        $excel->setActiveSheetIndex(0)->setCellValue('G5', "TANGGAL PKWT *\n(FORMAT DD/MM/YYYY)");
        $excel->setActiveSheetIndex(0)->freezePane('G7'); 
        $excel->setActiveSheetIndex(0)->setCellValue('H5', "TANGGAL HUBUNGAN KERJA");
        $excel->setActiveSheetIndex(0)->setCellValue('H6', "TANGGAL MULAI *\n(FORMAT DD/MM/YYYY)");
        $excel->getActiveSheet()->getStyle('G5')->getAlignment()->setWrapText(true);
        $excel->getActiveSheet()->getStyle('H6')->getAlignment()->setWrapText(true);
        $excel->setActiveSheetIndex(0)->setCellValue('I6', "TANGGAL SELESAI *\n(FORMAT DD/MM/YYYY)"); 
        $excel->getActiveSheet()->getStyle('I6')->getAlignment()->setWrapText(true);

        // parameter dinamis
        $parameter = $this->template_m->getDataById(md5($id_template))->parameter;
        $kolom_angka = 9;
        $kolom_tipe_nominal = [];
        foreach(json_decode($parameter, true) as $kolom => $tipe) {
            $kolom_angka++;
            if($kolom_angka == 10) {
                $excel->setActiveSheetIndex(0)->setCellValue($this->_angkaKeHuruf($kolom_angka).'5', "Parameter"); 
            }
            $excel->setActiveSheetIndex(0)->setCellValue($this->_angkaKeHuruf($kolom_angka).'6', $kolom." *"); 
            $excel->getActiveSheet()->getColumnDimension($this->_angkaKeHuruf($kolom_angka))->setWidth("20");
            if($tipe == 'nominal') {
                $kolom_tipe_nominal[] = $kolom_angka;
            }
        }
        $excel->setActiveSheetIndex(0)->setCellValueExplicit('I7', $id_template);
        $excel->setActiveSheetIndex(0)->setCellValue('J7', $parameter);
        $excel->getActiveSheet()->getRowDimension('7')->setRowHeight('0');
        
        // set width
        $excel->getActiveSheet()->getColumnDimension('A')->setWidth("5");
        $excel->getActiveSheet()->getColumnDimension('B')->setWidth("20");
        $excel->getActiveSheet()->getColumnDimension('C')->setWidth("20");
        $excel->getActiveSheet()->getColumnDimension('D')->setWidth("20");
        $excel->getActiveSheet()->getColumnDimension('E')->setWidth("20");
        $excel->getActiveSheet()->getColumnDimension('F')->setWidth("20");
        $excel->getActiveSheet()->getColumnDimension('G')->setWidth("25");
        $excel->getActiveSheet()->getColumnDimension('H')->setWidth("25");
        $excel->getActiveSheet()->getColumnDimension('I')->setWidth("25");
      
        // header rowspan
        $excel->getActiveSheet()->mergeCells('A5:A6');
        $excel->getActiveSheet()->mergeCells('B5:B6');
        $excel->getActiveSheet()->mergeCells('C5:C6');
        $excel->getActiveSheet()->mergeCells('D5:D6');
        $excel->getActiveSheet()->mergeCells('E5:E6');
        $excel->getActiveSheet()->mergeCells('F5:F6');
        $excel->getActiveSheet()->mergeCells('G5:G6');
        $excel->getActiveSheet()->mergeCells('H5:I5');
        if($kolom_angka > 9) {
            $excel->getActiveSheet()->mergeCells('J5:'.$this->_angkaKeHuruf($kolom_angka).'5');
        }
            
        # QUERY
        if($id_cabang != "all"){
            $this->db->where('dk.id_cabang', $id_cabang);
        }
        if($id_departemen != "all"){
            $this->db->where('dk.id_departemen', $id_departemen);
        }
        if($id_kar_arr != null){
            $this->db->where_in('dk.id_karyawan', $id_karyawan);
        }

        $this->db->select(" dk.id_karyawan,
                            dk.nik,
                            dk.nama_lengkap,
                            dk.tgl_berhenti_bekerja,
                            mj.nama AS nama_jabatan,
                            group.level AS level_user,
                            mc.kode AS kode_cabang,
                            mc.nama AS nama_cabang,
                            md.kode AS kode_departemen,
                            md.nama AS nama_departemen")
                    ->from('data_karyawan AS dk')
                    ->join('master_jabatan AS mj', 'mj.id_jabatan = dk.id_jabatan')
                    ->join('usergroup AS group', 'group.id = mj.level_user')
                    ->join('master_cabang AS mc', 'mc.id_cabang = dk.id_cabang')
                    ->join('master_departemen AS md', 'md.id_departemen = dk.id_departemen', 'LEFT')
                    ->where("dk.id_company",$this->id_company)
                    ->where("dk.id_karyawan NOT IN (SELECT id_karyawan FROM pkwt_data WHERE id_company = '" . $this->id_company . "' AND status != '3')")
                    ->where('dk.status', '1')
                    ->where_not_in('mj.level_user',['1','6']);

        $tgl_awal   = date('Y-m-d');
        $tgl_akhir  = date('Y-m-d', strtotime('+1 month'));
        
        $this->db->group_start();
        $this->db->where('dk.tgl_berhenti_bekerja >=', $tgl_awal);
        $this->db->where('dk.tgl_berhenti_bekerja <=', $tgl_akhir);
        $this->db->group_end();

        $data_karyawan = $this->db->get()->result();
        $excel->getActiveSheet()->getProtection()->setSheet(true);

        $no = 1; // Untuk penomoran tabel, di awal set dengan 1
        $numrow = 8; // Set baris pertama untuk isi tabel adalah baris ke 9
        foreach($data_karyawan as $data){
            $excel->setActiveSheetIndex(0)->setCellValue('A'.$numrow, $no);
            $excel->setActiveSheetIndex(0)->setCellValueExplicit('B'.$numrow, $data->nik);
            $excel->setActiveSheetIndex(0)->setCellValue('C'.$numrow, $data->nama_lengkap);
            $excel->setActiveSheetIndex(0)->setCellValue('D'.$numrow, $data->level_user);
            $excel->setActiveSheetIndex(0)->setCellValue('E'.$numrow, $data->nama_cabang);
            $excel->setActiveSheetIndex(0)->setCellValue('F'.$numrow, $data->nama_departemen);

            $excel->getActiveSheet()->getStyle('G'.$numrow.':'.$this->_angkaKeHuruf($kolom_angka).$numrow)->getProtection()->setLocked(PHPExcel_Style_Protection::PROTECTION_UNPROTECTED);
            if(!empty($kolom_tipe_nominal)) {
                foreach($kolom_tipe_nominal as $angka) {
                    $excel->getActiveSheet()->getStyle($this->_angkaKeHuruf($angka).$numrow)->getNumberFormat()->setFormatCode("#,##");
                }
            }
            
            $no++; // Tambah 1 setiap kali looping
            $numrow++; // Tambah 1 setiap kali looping
        }

        # BORDER SEMUA KOLOM
        $excel->getActiveSheet()
            ->getStyle("A5:".$this->_angkaKeHuruf($kolom_angka)."6")
            ->applyFromArray($style_header);
            
        $excel->getActiveSheet()
            ->getStyle("A5:".$this->_angkaKeHuruf($kolom_angka).($numrow-1))
            ->applyFromArray(
                array(
                    'borders' => array(
                        'allborders' => array(
                            'style' => PHPExcel_Style_Border::BORDER_THIN
                        )
                    )
                )
            );

        // Set height semua kolom menjadi auto (mengikuti height isi dari kolommnya, jadi otomatis)
        $excel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(-1);
        // Set orientasi kertas jadi LANDSCAPE
        $excel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
        // Set judul file excel nya
        $excel->getActiveSheet(0)->setTitle("Template Import PKWT");
        $excel->setActiveSheetIndex(0);
        
        // echo $filename; die();
        $filename = "TEMPLATE_IMPORT_PKWT";

        // Proses file excel
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment; filename='.$filename.'.xlsx'); // Set nama file excel nya
        header('Cache-Control: max-age=0');
        $write = PHPExcel_IOFactory::createWriter($excel, 'Excel2007');
        $write->save('php://output');
    }

    private function _angkaKeHuruf($angka) {
		if ($angka >= 1 && $angka <= 26) {
			return chr($angka + 64);
		} else {
			switch ($angka) {
				case 27:
					return "AA";
					break;
				case 28:
					return "AB";
					break;
				case 29:
					return "AC";
					break;
				case 30:
					return "AD";
					break;
				case 31:
					return "AE";
					break;
				case 32:
					return "AF";
					break;
				default:
				  	return "Angka diluar rentang pemetaan.";
			  }
		}
	}

    // Untuk Download
    private function _get_tunj_header(){
        $select = $this->db->select('id_komponen,nm_komp,no_urut')
                            ->from('pkwt_komp_m')
                            ->where('id_company', $this->id_company)
                            ->order_by('no_urut')
                            ->get()
                            ->result();
        $data = [];
        foreach ($select as $key) {
            $data[] = [
                'id_komponen'   => $key->id_komponen,
                'nm_komp'       => $key->nm_komp,
                'no_urut'       => $key->no_urut
            ];
        }
        return $data;
    }

    public function import_preview(){
        # Library Excel
		if (!empty($_FILES['file']['name'])) {
			$tahunbulan                     = date('Ym');
			$fileUpload['fname']            = 'file';
			$fileUpload['location']         = 'pkwt/import';
			$fileUpload['allowed']          = 'xls|xlsx';
			$fileUpload['compress_image']   = false;
			
			$upload = $this->uploadfile->doUpload($fileUpload);
			if ($upload["success"] == true) {
				$dir_file = $this->config->item('base_upload') . $upload['url'];
			} else {
				$message = ["message" => $upload["message"]];
				return __response_save(false, $message);
			}
		} else {
			$message = ["message" => '<font color="#eb3a28"><i class="fa fa-exclamation-triangle">&nbsp;</i>Data gagal import</font>'];
			return __response_save(false, ["message" => $message]);
		}
		
		# Library Excel
		try {
			$inputFileType  = PHPExcel_IOFactory::identify($dir_file);
			$objReader      = PHPExcel_IOFactory::createReader($inputFileType);
			$objPHPExcel    = $objReader->load($dir_file);
		} catch (Exception $e) {
			$message = ["message" => 'Error loading file "' . pathinfo($dir_file, PATHINFO_BASENAME) . '": ' . $e->getMessage()];
			return __response_save(false, ["message" => $message]);
		}

        $row = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);
		$end = sizeof($row);

        $highestColumm = $objPHPExcel->setActiveSheetIndex(0)->getHighestColumn();
        $highestRow    = $objPHPExcel->setActiveSheetIndex(0)->getHighestRow();
		
        $data          = array();
        $data_insert   = array();
        $data_template = '';

        $id_pkwt_temp = $this->fungsi->getNextId('pkwt_data_temp','id_pkwt_temp',$this->id_company,4);
        $id_template  = $row[7]['I'];
        if($id_template == '') {
            return __response_save(false, ["message" => '<span class="text-danger"><i class="fa fa-exclamation-triangle"></i> template tidak ditemukan</span>']);
        } else {
            $data_template = $this->template_m->getDataById(md5($id_template));
            if($data_template->id_template == null) {
                return __response_save(false, ["message" => '<span class="text-danger"><i class="fa fa-exclamation-triangle"></i> template tidak ditemukan</span>']);
            }
        }
        $parameter = $row[7]['J'];
        if($parameter == '') {
            return __response_save(false, ["message" => '<span class="text-danger"><i class="fa fa-exclamation-triangle"></i> parameter tidak ditemukan</span>']);
        } elseif(count(json_decode($parameter, 1)) == 0) {
            return __response_save(false, ["message" => '<span class="text-danger"><i class="fa fa-exclamation-triangle"></i> parameter tidak ditemukan</span>']);
        }
        for ($i = 8; $i <= $end; $i++) {
            $nik              = (isset($row[$i]['B']) && $row[$i]['B'] != NULL) ? $row[$i]['B'] : '';
            $get_data_pegawai = $this->_get_data_pegawai($nik);

            if(!empty($nik)){
                $tgl_pkwt         = (isset($row[$i]['G']) && $row[$i]['G'] != NULL) ? $row[$i]['G'] : '';
                $tgl_pkwt_mulai   = (isset($row[$i]['H']) && $row[$i]['H'] != NULL) ? $row[$i]['H'] : '';
                $tgl_pkwt_selesai = (isset($row[$i]['I']) && $row[$i]['I'] != NULL) ? $row[$i]['I'] : '';

                $tgl_pkwt         = date('Y-m-d', strtotime(str_replace("/", "-", $tgl_pkwt)));
                $tgl_pkwt_mulai   = date('Y-m-d', strtotime(str_replace("/", "-", $tgl_pkwt_mulai)));
                $tgl_pkwt_selesai = date('Y-m-d', strtotime(str_replace("/", "-", $tgl_pkwt_selesai)));

                $data = array(  'id_pkwt_temp'      => $id_pkwt_temp,
                                'id_karyawan'          => $get_data_pegawai->id_karyawan,
                                'nik'                  => $get_data_pegawai->nik,
                                'nm_karyawan'          => $get_data_pegawai->nama_lengkap,
                                'lahir_tempat'         => $get_data_pegawai->tempat_lahir,
                                'lahir_tgl'            => $get_data_pegawai->tgl_lahir,
                                'alamat'               => $get_data_pegawai->alamat_domisili,
                                'nm_lvl_user'          => $get_data_pegawai->nama_jabatan,
                                'nm_jabatan'           => $get_data_pegawai->nama_level_user,
                                'nm_departemen'        => $get_data_pegawai->nama_cabang,
                                'nm_cabang'            => $get_data_pegawai->nama_departemen,
                                'tgl_pkwt'             => $tgl_pkwt,
                                'tgl_pkwt_mulai'       => $tgl_pkwt_mulai,
                                'tgl_pkwt_selesai'     => $tgl_pkwt_selesai,
                                'jenis_pkwt'           => 'perpanjangan',
                                'id_template'          => $id_template,
                                'nm_draft_pkwt'        => $data_template->template,
                                'draft_pkwt_parameter' => $parameter,
                                'id_company'           => $this->id_company,
                                'user_input'           => $this->nik_karyawan. ' - '. $this->nm_karyawan,
                                'tgl_input'            => date("Y-m-d H:i:s"),
                            );

                // draft pkwt value
                $temp_value  = [];
                $kolom_angka = 9;
                $parameter_valid = true;
                foreach(json_decode($parameter, 1) as $kolom => $tipe) {
                    $kolom_angka++;

                    $value = $row[$i][$this->_angkaKeHuruf($kolom_angka)];
                    $temp_value[$kolom] = isset($value) ? '' : '';
                    if(isset($value)) {
                        if($value != '') {
                            $temp_value[$kolom] = $tipe == 'nominal' ? str_replace(",", "", $value) : $value;
                        } else {
                            $parameter_valid = false;
                        }
                    } else {
                        $parameter_valid = false;
                    }
                }

                if(count($temp_value) > 0) {
                    $data['draft_pkwt_value'] = json_encode($temp_value);
                }

                if (!empty($tgl_pkwt) && !empty($tgl_pkwt_mulai) && !empty($tgl_pkwt_selesai) && $parameter_valid == true) {
                    $flag = "1"; // data valid
                }else{
                    $flag = "2"; // data tidak valid
                }

                $data_existing = $this->Import_m->getDataExisting($get_data_pegawai->id_karyawan);
                if ($data_existing != 0){
                    $flag = "3"; // data sudah ada
                }

                $flag_arr = array('flag' => $flag);
                            
                $data_insert[] = array_merge($data,$flag_arr);

                $last_id = substr($id_pkwt_temp,-4);
                $next_id = $last_id+1;
                $id_pkwt_temp = substr($id_pkwt_temp,0,-4).sprintf("%04d", $next_id);
            }
        }

        $this->db->trans_start();

        $this->db->where('id_company', $this->id_company);
        $this->db->delete('pkwt_data_temp');
        $this->db->insert_batch('pkwt_data_temp', $data_insert);

        $this->db->trans_complete();

        if ($this->db->trans_status() === FALSE) {
            $this->db->trans_rollback();
            $message = '<font color="#FFFFF"><i class="fa fa-exclamation-triangle">&nbsp;</i>Data gagal disimpan</font>';
            return __response_save(false,["message"=>$message]);
        }else{
            $this->db->trans_commit();
            $message = '<font color="#FFFFF"><i class="fa fa-check-square">&nbsp;</i>Data berhasil disimpan</font>';
            return __response_save(true,["message"=>$message]);
        }
    }

    private function _get_data_pegawai($nik){
        $this->db->select(" dk.id_karyawan,
                            dk.nik,
                            dk.nama_lengkap,
                            dk.tempat_lahir,
                            dk.tgl_lahir,
                            dk.alamat_domisili,
                            mj.kode_jabatan,
                            mj.nama AS nama_jabatan,
                            usergroup.level AS nama_level_user,
                            mc.nama AS nama_cabang,
                            md.nama AS nama_departemen");
        $this->db->from("data_karyawan AS dk");
        $this->db->join("master_jabatan AS mj","mj.id_jabatan = dk.id_jabatan");
        $this->db->join("usergroup","usergroup.id = mj.level_user");
        $this->db->join("master_cabang AS mc","mc.id_cabang = dk.id_cabang");
        $this->db->join("master_departemen AS md","md.id_departemen = md.id_departemen", 'LEFT');
        $this->db->where("dk.nik",$nik);
        $this->db->where("dk.id_company",$this->id_company);

        $query = $this->db->get();
        return $query->row();
    }

    public function simpan_semua(){
        $get_data_temp = $this->db->select('*')
                              ->from('pkwt_data_temp')
                              ->where('id_company',$this->id_company)
                              ->get()
                              ->result();

		$data_update = array();
		$data_insert = array();
   	 	$data = array();

        $id_pkwt = $this->fungsi->getNextId('pkwt_data','id_pkwt',$this->id_company,4);

		foreach ($get_data_temp as $rows) {
			if($rows->flag == '1'){
				$data = array(  'id_pkwt' => $id_pkwt,
                                'id_karyawan'          => $rows->id_karyawan,
                                'jenis_pkwt'           => $rows->jenis_pkwt,
                                'nik'                  => $rows->nik,
                                'nm_karyawan'          => $rows->nm_karyawan,
                                'lahir_tempat'         => $rows->lahir_tempat,
                                'lahir_tgl'            => $rows->lahir_tgl,
                                'alamat'               => $rows->alamat,
                                'nm_lvl_user'          => $rows->nm_lvl_user,
                                'nm_jabatan'           => $rows->nm_jabatan,
                                'nm_departemen'        => $rows->nm_departemen,
                                'nm_cabang'            => $rows->nm_cabang,
                                'tgl_pkwt'             => $rows->tgl_pkwt,
                                'tgl_pkwt_mulai'       => $rows->tgl_pkwt_mulai,
                                'tgl_pkwt_selesai'     => $rows->tgl_pkwt_selesai,
                                'nm_direktur'          => $rows->nm_direktur,
                                'ttd_direktur'         => $rows->ttd_direktur,
                                'ttd_karyawan'         => $rows->ttd_karyawan,
                                'nm_draft_pkwt'        => $rows->nm_draft_pkwt,
                                'draft_pkwt_parameter' => $rows->draft_pkwt_parameter,
                                'draft_pkwt_value'     => $rows->draft_pkwt_value,
                                'status'               => $rows->status,
                                'apv_karyawan'         => $rows->apv_karyawan,
                                'apv_direksi'          => $rows->apv_direksi,
                                'tgl_apv_karyawan'     => $rows->tgl_apv_karyawan,
                                'tgl_apv_direksi'      => $rows->tgl_apv_direksi,
                                'user_apv_karyawan'    => $rows->user_apv_karyawan,
                                'user_apv_direksi'     => $rows->user_apv_direksi,
                                'id_company'           => $rows->id_company,
                                'user_input'           => $rows->user_input,
                                'tgl_input'            => $rows->tgl_input,
                                'user_update'          => $rows->user_update,
                                'tgl_update'           => $rows->tgl_update);
	

                $data_insert[] = $data;
                $id_pkwt_temp_arr[] = $rows->id_pkwt_temp;

                $last_id = substr($id_pkwt,-4);
                $next_id = $last_id+1;
                $id_pkwt = substr($id_pkwt,0,-4).sprintf("%04d", $next_id);
			}
		}

        $this->db->trans_start();
        
        if(count($data_insert) > 0){
            $this->db->insert_batch('pkwt_data', $data_insert);
        }

        $this->db->where('flag', '1');
        $this->db->where('id_company', $this->id_company);
        $this->db->delete('pkwt_data_temp');
        $this->db->trans_complete();

        if ($this->db->trans_status() === FALSE) {
            $this->db->trans_rollback();
            $message = '<font color="#FFFFF"><i class="fa fa-exclamation-triangle">&nbsp;</i>Data gagal disimpan</font>';
            return __response_save(false,["message"=>$message]);
        }else{
            $this->db->trans_commit();

            $message = '<font color="#FFFFF"><i class="fa fa-check-square">&nbsp;</i>Data berhasil disimpan</font>';
            return __response_save(true,["message"=>$message]);
        }
    }

    public function delete_semua(){
        $this->db->trans_start();

        $this->db->where('id_company', $this->id_company);
        $this->db->delete('pkwt_data_temp');
        $this->db->trans_complete();

        if ($this->db->trans_status() === FALSE) {
            $this->db->trans_rollback();
            $message = '<font color="#FFFFF"><i class="fa fa-exclamation-triangle">&nbsp;</i>Data gagal dihapus</font>';
            return __response_save(false,["message"=>$message]);
        }else{
            $this->db->trans_commit();
            $message = '<font color="#FFFFF"><i class="fa fa-check-square">&nbsp;</i>Data berhasil dihapus</font>';
            return __response_save(true,["message"=>$message]);
        }
    }
}