<?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()
    {
        $master_komponen = json_decode($this->model->master_komponen(), true);;
        $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;

            $col = [];
            if (count($get_pend[$field->id_karyawan]) > 0) {
                foreach ($master_komponen['pendapatan'] as $komponen) {
                    $data_komponen_pendapatan = $get_pend[$field->id_karyawan];
                    $index = array_search($komponen['id_item'], array_column($data_komponen_pendapatan, 'id_item'));
                    if ($index !== false) {
                        $row[] = number_format($data_komponen_pendapatan[$index]['nominal_tunjangan'], 0, ',', '.') ?: 0;
                        $col[] = $index . ' - ' . number_format($data_komponen_pendapatan[$index]['nominal_tunjangan'], 0, ',', '.') ?: 0;
                    } else {
                        $row[] = 0;
                        $col[] = 0;
                    }
                }
            } else {
                foreach ($master_komponen['pendapatan'] as $komponen) {
                    $row[] = 0;
                    $col[] = 0;
                }
            }
            // dd($col);
            if (count($get_pot[$field->id_karyawan]) > 0) {
                foreach ($master_komponen['potongan'] as $komponen) {
                    $data_komponen_potongan = $get_pot[$field->id_karyawan];
                    $index = array_search($komponen['id_item'], array_column($data_komponen_potongan, 'id_item'));
                    if ($index !== false) {
                        $row[] = number_format($data_komponen_potongan[$index]['nominal_potongan'], 0, ',', '.') ?: 0;
                    } else {
                        $row[] = 0;
                    }
                }
            } else {
                foreach ($master_komponen['potongan'] as $komponen) {
                    $row[] = 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'    => ['width' => 5, '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();
                }
                // menghapus tanda kurung dan konten di dalamnya
                $head = preg_replace('/\s*\([^)]*\)\s*$/', '', $head);
                // if (strpos($head, '(') !== false) {
                //     $head = (substr($head, -8, 1) != '(') ? substr($head, 0, -8) : substr($head, 0, -9);
                // }

                $cell = $objPHPExcel->setActiveSheetIndex(0)->getCell($column . $row)->getValue();

                // key dataset = head dan column karena untuk membedakan jika ada nama komponen yg sama
                $dataset[$head . ' (' . $column . ')'] = $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] ?: 0,
                        'nm_tunjangan'      => preg_replace('/\s*\([^)]*\)\s*$/', '', $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] ?: 0,
                        'nm_potongan'       => preg_replace('/\s*\([^)]*\)\s*$/', '', $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                  = '';
        $cabang                 = array();
        $departemen             = array();
        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_rp'],
                            'nominal_tunjangan'     => $key['nominal_tunjangan'],
                            'flag_bpjs'             => $key['flag_bpjs'],
                            'flag_pph21'            => $key['flag_pph21'],
                            'flag_pot_absensi'      => $key['flag_pot_absensi'],
                            'id_company'            => $this->id_company,
                            'user_input'            => $this->nik_karyawan . ' - ' . $this->nm_karyawan
                        ];
                        $data_pendapatan_delete[] = $key['nik'] . $key['bulan'] . $this->id_company;
                        $cabang[]                 = $key['cabang'];
                        $departemen[]             = $key['departemen'];
                    }
                    $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_rp'],
                            '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'] . $this->id_company;
                        $cabang[]               = $key['cabang'];
                        $departemen[]           = $key['departemen'];
                    }
                    $bulan = $key['bulan'];
                }
            }
        }

        $cabang = array_values(array_unique($cabang));
        $departemen = array_values(array_unique($departemen));

        $this->db->trans_start();

        if (!empty($data_pendapatan_delete)) {
            // hapus semua pend tdk tetap pada periode ditemporary
            $this->db->where_in('concat(nik,bulan,id_company)', $data_pendapatan_delete);
            $this->db->delete('payroll_tunj_tdk_tetap_temp');
        }
        if (!empty($data_pendapatan)) {
            // hapus semua pend tdk tetap pada periode yang di import
            $this->db->where('id_company', $this->id_company);
            $this->db->where('periode', $bulan);
            $this->db->where_in('id_karyawan', array_unique(array_column($data_pendapatan, 'id_karyawan')));
            $this->db->delete('payroll_tunj_tdk_tetap_m');
            // insert
            $this->db->insert_batch('payroll_tunj_tdk_tetap_m', $data_pendapatan);
        }

        if (!empty($data_potongan_delete)) {
            $this->db->where_in('concat(nik,bulan,id_company)', $data_potongan_delete);
            $this->db->delete('payroll_pot_tdk_tetap_temp');
        }
        if (!empty($data_potongan)) {
            // hapus semua pot tdk tetap pada periode yang di import
            $this->db->where('id_company', $this->id_company);
            $this->db->where('periode', $bulan);
            $this->db->where_in('id_karyawan', array_unique(array_column($data_potongan, 'id_karyawan')));
            $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);
        }
    }
}
