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

class Data_komponen_import extends MY_Controller {

	public function __construct(){
        parent::__construct();
		$this->load->model('payroll/data_komponen_tdk_tetap/Data_komponen_import_m', 'model');
        $this->load->library(array('PHPExcel/Excel', 'PHPExcel/PHPExcel', 'Uploadfile'));
    }

	public function index(){
		$data['judul'] = 'Import Data Komponen Tidak Tetap';
		$data['aktif'] = 'data_komponen_tdk_tetap';
        $data['menu'] = $this->load->view('main_menu', $data, true);
		$data['content'] = $this->load->view('payroll/data_komponen_tdk_tetap/import/home', $data, true);
		$this->load->view('main_template', $data, false);
	}

	public function form_template_import(){
		$this->load->view('payroll/data_komponen_tdk_tetap/import/form_template_import');
	}

	public function view_data(){
        $data['master'] = json_decode($this->model->master_komponen(), true);
		$this->load->view('payroll/data_komponen_tdk_tetap/import/list_data', $data);
	}

	public function list_data(){
		$get_pend = json_decode($this->model->tambah_pendapatan(), true);
		$get_pot  = json_decode($this->model->tambah_potongan(), true);
		$temp     = ((count($get_pend) > 0) ? 'pend' : 'pot');
		$list     = $this->model->get_datatables($temp);
        // print_r($get_pot);
		$data = array();
		$no   = $_GET['start'];
		foreach ($list as $field) {
			$no++;
            $row   = array();
            $row[] = $no;
            $row[] = ($field->id_karyawan) ? $field->id_karyawan : '-';
            $row[] = $field->nm_karyawan."<br><b>".$field->nik."</b>";
            $row[] = indo_date(strtotime($field->bulan.'-01'), 'F Y');
            $row[] = $field->cabang;
            $row[] = $field->departemen;
            $row[] = $field->jabatan;

            if (count($get_pend[$field->id_karyawan]) > 0) {
                foreach ($get_pend[$field->id_karyawan] as $komponen) {
                    $row[] = number_format($komponen['nominal_tunjangan'],0,',','.');
                }
            }

            if (count($get_pot[$field->id_karyawan]) > 0) {
                foreach ($get_pot[$field->id_karyawan] as $komponen) {
                    $row[] = number_format($komponen['nominal_potongan'],0,',','.');
                }
            }

			$data[] = $row;
		}

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

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

		echo json_encode($output);
	}

	function download_template($bulan, $id_cabang="-", $id_departemen="-")
    {
        if($id_cabang != "-") {
            $nama_cabang = $this->db->select("nama")
                ->from("master_cabang")
                ->where("id_cabang", $id_cabang)
                ->where("id_company",$this->id_company)
                ->get()
                ->row("nama");
			$filename = "Template Import Data Komponen Tidak Tetap Cabang " . $nama_cabang;
        }else{
			$filename = "Template Import Data Komponen Tidak Tetap Semua Cabang ";
        }

		if($id_departemen != "-") {
			$nama_departemen = $this->db->select("nama")
                ->from("master_departemen")
                ->where("id_departemen", $id_departemen)
                ->where("id_company",$this->id_company)
                ->get()
                ->row("nama");
			$filename .= " Departemen " . $nama_departemen;
		}else{
			$filename .= " Semua Departemen";
		}

		$data['nama']     = $filename;
		$data['bulan']    = $bulan;
		$data['data']     = $this->model->getDataKaryawan($id_cabang, $id_departemen);
		$data['master']   = json_decode($this->model->master_komponen(), true);
		$data['is_excel'] = true;
		$output           = true;
		$sb_view          = $this->load->view('payroll/data_komponen_tdk_tetap/import/download', $data, $output);
        // echo $sb_view;exit;

        $komponen     = $data['master'];
        $total_column = count($komponen['pendapatan']) + count($komponen['potongan']) + 7;
        // jika terdapat komponen pendapatan/potongan
        if ($total_column > 7) {
            if (count($komponen['pendapatan']) > 0) {
                $merge_komponen = array_merge($komponen['pendapatan']);
            }
            if (count($komponen['potongan']) > 0) {
                if (count($komponen['pendapatan']) > 0) {
                    $merge_komponen = array_merge($merge_komponen, $komponen['potongan']);
                }else{
                    $merge_komponen = array_merge($komponen['potongan']);
                }
            }
        }else{
            // pendapatan + potongan  = 0
            return ;
        }

        // column dinamis dari header table
        $column_param = array(
            'A'    => ['align' => 'center', 'valign' => 'middle'],
            'B'    => ['width' => 10, 'align' => 'center', 'valign' => 'middle', 'format'=>'text'],
            'C'    => ['width' => 40, 'lock' => true],
            'D'    => ['width' => 10, 'lock' => true],
            'E'    => ['width' => 20, 'lock' => true],
            'F'    => ['width' => 20, 'lock' => true],
            'G'    => ['width' => 20, 'lock' => true],
            'H'    => ['width' => 20, 'align' => 'right', 'valign' => 'middle', 'rupiah' => true, 'lock' => true],
        );
        $col = 'I';
        for ($i=0; $i < ($total_column-7); $i++) {
            // jika dari persentase gapok maka di lock
            if ($merge_komponen[$i]['flag_persentase_gapok'] == '1') {
                $column_param[$col] = ['align' => 'right', 'valign' => 'middle', 'format'=>'text', 'rupiah' => true, 'lock' => true];
            }else{
                $column_param[$col] = ['align' => 'right', 'valign' => 'middle', 'format'=>'text', 'rupiah' => true];
            }
            $col++;
        }

		$param = [
			'download'   => true,
			'cLength'    => $total_column,
			'file_name'  => strtoupper($filename),
			'paper_size' => 'A4',
			'font-size'  => 12,
			  // 'pagebreak'		=> true,
			'page_margin' => ['top' => 0, 'right' => 0.75, 'left' => 0.75, 'botom' => 0],
			'orientation' => 'potrait',
			'header'      => [
				'1' => ['merge' => true, 'align' => 'center', 'weight' => 'bold'],
				'2' => ['merge' => true, 'align' => 'center', 'weight' => 'bold'],
				'3' => ['merge' => true, 'align' => 'center', 'weight' => 'bold'],
			],
			// 'freezecolumn'	   => ['D7'],
			'thead'        => ['4', '5'],
			'tbody'        => [
				'start' => '4',
				'height'	=> 13,
				// 'runFunction'	=> ['highlightlibur' => 'P'],
				'wrap' => ['B', 'C', 'D', 'E', 'F'],
				'column' => $column_param,
			],
		];
		$this->excel->generateExcel($sb_view, $param);
	}

	public function import_data_excel()
    {
        if(!empty($_FILES['file']['name'])) {
            $tahunbulan                   = date('Ym');
            $fileUpload['fname']          = 'file';
            $fileUpload['location']       = 'template_data_komponen/'.$tahunbulan;
            $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]);
        }

        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]);
        }

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

		$highestColumm++;
        for ($row = 6; $row < ($highestRow-4); $row++) {
            $dataset = array();
            for ($column = 'B'; $column != $highestColumm; $column++) {
				$head = $objPHPExcel->setActiveSheetIndex(0)->getCell($column . '5')->getValue();
                if (empty($head)) {
                    $head = $objPHPExcel->setActiveSheetIndex(0)->getCell($column . '4')->getValue();
                }
                if (str_contains($head, '(')) {
                    $head = (substr($head, -8, 1) != '(') ? substr($head, 0, -8) : substr($head, 0, -9);
                }
				$cell = $objPHPExcel->setActiveSheetIndex(0)->getCell($column . $row)->getValue();

                $dataset[$head] = $cell;
			}
            $data_insert[] = $dataset;

		}
        $results         = array_filter($data_insert);
        $master_komponen = $this->model->master_komponen();
        $komponen        = json_decode($master_komponen, true);
        $jml_header      = 7;
        $jml_pend        = count($komponen['pendapatan']);
        $jml_pot         = count($komponen['potongan']);

        $data_pendapatan = array();
        $data_potongan   = array();
        for ($i_data=0; $i_data < count($results); $i_data++) {
            if ($jml_pend > 0) {
                for ($i_pend=$jml_header; $i_pend < ($jml_header+$jml_pend); $i_pend++) {
                    $nm_pendapatan = array_keys($results[$i_data])[$i_pend];
                    $data_pendapatan[] = [
                        'nik'               => $results[$i_data][array_keys($results[$i_data])[0]],
                        'nm_karyawan'       => $results[$i_data][array_keys($results[$i_data])[1]],
                        'bulan'             => date('Y-m', strtotime('01-'.$results[$i_data][array_keys($results[$i_data])[2]])),
                        'cabang'            => $results[$i_data][array_keys($results[$i_data])[3]],
                        'departemen'        => $results[$i_data][array_keys($results[$i_data])[4]],
                        'jabatan'           => $results[$i_data][array_keys($results[$i_data])[5]],
                        'gapok'             => $results[$i_data][array_keys($results[$i_data])[6]],
                        'nominal_tunjangan' => $results[$i_data][$nm_pendapatan],
                        'nm_tunjangan'      => $nm_pendapatan,
                        'user_input'        => $this->nik_karyawan. ' - ' .$this->nm_karyawan,
                        'id_company'        => $this->id_company
                    ];
                }
            }

            if ($jml_pot > 0) {
                if ($jml_pend > 0) {
                    $header = $jml_header+$jml_pend;
                }else{
                    $header = $jml_header;
                }
                for ($i_pot=$header; $i_pot < ($header+$jml_pot); $i_pot++) {
                    $nm_potongan = array_keys($results[$i_data])[$i_pot];
                    $data_potongan[] = [
                        'nik'               => $results[$i_data][array_keys($results[$i_data])[0]],
                        'nm_karyawan'       => $results[$i_data][array_keys($results[$i_data])[1]],
                        'bulan'             => date('Y-m', strtotime('01-'.$results[$i_data][array_keys($results[$i_data])[2]])),
                        'cabang'            => $results[$i_data][array_keys($results[$i_data])[3]],
                        'departemen'        => $results[$i_data][array_keys($results[$i_data])[4]],
                        'jabatan'           => $results[$i_data][array_keys($results[$i_data])[5]],
                        'gapok'             => $results[$i_data][array_keys($results[$i_data])[6]],
                        'nominal_potongan'  => $results[$i_data][$nm_potongan],
                        'nm_potongan'       => $nm_potongan,
                        'user_input'        => $this->nik_karyawan. ' - ' .$this->nm_karyawan,
                        'id_company'        => $this->id_company
                    ];
                }
            }
        }

        $this->db->trans_strict();
		$this->db->trans_start();

        if (count($data_pendapatan) > 0) {
            // hapus data payroll_tunj_tdk_tetap_temp
            $this->db->where("id_company", $this->id_company);
            $this->db->delete("payroll_tunj_tdk_tetap_temp");

            // insert data payroll_tunj_tdk_tetap_temp
            if ($this->db->trans_status()) {
                $this->db->insert_batch('payroll_tunj_tdk_tetap_temp', $data_pendapatan);
            }
        }

        if (count($data_potongan) > 0) {
            // hapus data payroll_pot_tdk_tetap_temp
            $this->db->where("id_company", $this->id_company);
            $this->db->delete("payroll_pot_tdk_tetap_temp");

            // insert data payroll_pot_tdk_tetap_temp
            if ($this->db->trans_status()) {
                $this->db->insert_batch('payroll_pot_tdk_tetap_temp', $data_potongan);
            }
        }

		$this->db->trans_complete();

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

	public function publish_all()
    {
        $get_pend = json_decode($this->model->tambah_pendapatan(), true);
        $get_pot  = json_decode($this->model->tambah_potongan(), true);
        $temp     = ((count($get_pend) > 0) ? 'pend' : 'pot');
		$list     = $this->model->get_datatables($temp);
        $data_pendapatan        = array();
        $data_potongan          = array();
        $data_pendapatan_delete = array();  // utk table payroll_tunj_tdk_tetap_temp
        $data_potongan_delete   = array();  // utk table payroll_pot_tdk_tetap_temp
        $bulan                  = '';
        foreach ($list as $field) {
            if (count($get_pend[$field->id_karyawan]) > 0) {
                foreach ($get_pend[$field->id_karyawan] as $key) {
                    if (!empty($field->id_karyawan)) {
                        $data_pendapatan[] = [
                            'id_karyawan'           => $key['id_karyawan'],
                            'id_item'               => $key['id_item'],
                            'periode'               => $key['bulan'],
                            'cabang'                => $key['cabang'],
                            'departemen'            => $key['departemen'],
                            'jabatan'               => $key['jabatan'],
                            'jenis_komponen'        => $key['jenis_komponen'],
                            'jenis_master'          => $key['jenis_master'],
                            'nm_tunjangan'          => $key['nm_tunjangan'],
                            'flag_persentase_gapok' => $key['flag_persentase_gapok'],
                            'gapok_persen'          => $key['gapok_persen'],
                            'gapok_rp'              => $key['gapok'],
                            'nominal_tunjangan'     => $key['nominal_tunjangan'],
                            'flag_bpjs'             => '0',
                            'flag_pph21'            => '0',
                            'flag_pot_absensi'      => '0',
                            'id_company'            => $this->id_company,
                            'user_input'            => $this->nik_karyawan. ' - ' .$this->nm_karyawan
                        ];
                        $data_pendapatan_delete[] = $key['nik'].$key['bulan'].$key['nm_tunjangan'].$this->id_company;
                    }
                    $bulan = $key['bulan'];
                }
            }
            if (count($get_pot[$field->id_karyawan]) > 0) {
                foreach ($get_pot[$field->id_karyawan] as $key) {
                    if (!empty($field->id_karyawan)) {
                        $data_potongan[] = [
                            'id_karyawan'           => $key['id_karyawan'],
                            'id_item'               => $key['id_item'],
                            'periode'               => $key['bulan'],
                            'cabang'                => $key['cabang'],
                            'departemen'            => $key['departemen'],
                            'jabatan'               => $key['jabatan'],
                            'jenis_komponen'        => $key['jenis_komponen'],
                            'jenis_master'          => $key['jenis_master'],
                            'nm_potongan'           => $key['nm_potongan'],
                            'flag_persentase_gapok' => $key['flag_persentase_gapok'],
                            'gapok_persen'          => $key['gapok_persen'],
                            'gapok_rp'              => $key['gapok'],
                            'nominal_potongan'      => $key['nominal_potongan'],
                            'flag_bpjs'             => '0',
                            'flag_pph21'            => '0',
                            'flag_pot_absensi'      => '0',
                            'id_company'            => $this->id_company,
                            'user_input'            => $this->nik_karyawan. ' - ' .$this->nm_karyawan
                        ];
                        $data_potongan_delete[] = $key['nik'].$key['bulan'].$key['nm_potongan'].$this->id_company;
                    }
                    $bulan = $key['bulan'];
                }
            }
        }

		$this->db->trans_start();

        if(count($data_pendapatan_delete) > 0){
			$this->db->where_in('concat(nik,bulan,nm_tunjangan,id_company)', $data_pendapatan_delete);
			$this->db->delete('payroll_tunj_tdk_tetap_temp');
		}
        if (count($data_pendapatan) > 0) {
            // hapus semua pot tdk tetap pada periode yang di import
            $this->db->where('periode', $bulan);
            $this->db->delete('payroll_tunj_tdk_tetap_m');
            // insert
            $this->db->insert_batch('payroll_tunj_tdk_tetap_m', $data_pendapatan);
        }

        if(count($data_potongan_delete) > 0){
			$this->db->where_in('concat(nik,bulan,nm_potongan,id_company)', $data_potongan_delete);
			$this->db->delete('payroll_pot_tdk_tetap_temp');
		}
        if (count($data_potongan) > 0) {
            // hapus semua pot tdk tetap pada periode yang di import
            $this->db->where('periode', $bulan);
            $this->db->delete('payroll_pot_tdk_tetap_m');
            // insert
            $this->db->insert_batch('payroll_pot_tdk_tetap_m', $data_potongan);
        }

		$this->db->trans_complete();

		if($this->db->trans_status() === FALSE) {
			$this->db->trans_rollback();
			return __response_save(false);
		}else{
			$this->db->trans_commit();
			return __response_save(true);
		}
	}

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

		$this->db->where("id_company", $this->id_company);
		$this->db->delete("payroll_tunj_tdk_tetap_temp");

		$this->db->where("id_company", $this->id_company);
		$this->db->delete("payroll_pot_tdk_tetap_temp");

		$this->db->trans_complete();

		if($this->db->trans_status() === FALSE) {
			$this->db->trans_rollback();
			return __response_delete(false);
		}else{
			$this->db->trans_commit();
			return __response_delete(true);
		}
	}
}
