<?php

namespace App\Controllers\Report;

use \App\Controllers\Ps\PS_Controller;
use CodeIgniter\HTTP\RequestInterface;
use CodeIgniter\HTTP\ResponseInterface;
use Psr\Log\LoggerInterface;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Spreadsheet;

class Reportmutasiwip extends PS_Controller
{
    private $id_column_name = '';

    public function initController(RequestInterface $request, ResponseInterface $response, LoggerInterface $logger)
    {
        parent::initController($request, $response, $logger); // TODO: Change the autogenerated stub
        parent::setPageTitleBreadcrumb(true, 'Mutasi WIP', array('l1' => 'Laporan', 'l2' => 'Mutasi WIP'));
        parent::setURLActionTable($this->class);
        parent::setContentView($this->class);
        parent::setBaseContAndModel($this->class, 'ReportMutasibahanbaku');
        $this->validation_input = $this->validation->setRules([
            "role_name" => ['label' => 'Role', 'rules' => 'required']
        ]);
    }

    public function index()
    {
        parent::filterStoreLoad();
        $this->session->set('trash', false);
        $this->session->set('keyword', '');
        $start = date('Y-m-01');
        $end = date('Y-m-t');
        $barangId = '';
        $show = false;

        if ($_POST) {
            $start = $this->request->getVar('start');
            $end = $this->request->getVar('end');
            $barangId = $this->request->getVar('item');
            if ($_POST['rpt'] == 'export') {
                $this->export($start, $end, $barangId);
            }            
            if ($_POST['rpt'] == 'exportpdf') {
                $this->exportpdf($start, $end, $barangId);
            }
            if ($_POST['rpt'] == 'show') {
                 $show = true;
            }
        }
        
        $dtl = new \App\Models\MasterBarang();
        $data = $dtl->where('mb_id', $barangId)->first();

        $barang = new  \App\Models\MasterBarang();
        $this->view_data['_barang'] = $barang->where('deleted_at', null)->findAll();

        $this->view_data['show'] = $show;
        $this->view_data['data'] = $data;
        $this->view_data['start'] = $start;
        $this->view_data['end'] = $end;
        $this->view_data['barangId'] = $barangId;
        $this->render->viewAdmin($this->content_views['table_view'], $this);
    }

    public function exportpdf($start, $end, $barang)
    {
        $dtl = new \App\Models\MasterBarang();
        $data = $dtl->where('mb_id', $barang)->first();

        $barang = new  \App\Models\MasterBarang();
        $this->view_data['_barang'] = $barang->where('deleted_at', null)->findAll();

        $this->view_data['data'] = $data;
        $this->view_data['start'] = $start;
        $this->view_data['end'] = $end;
                
        $html = view('admin/pages/custom/report/reportmutasiwip/printpdf', $this->view_data);
        $filename = 'Laporan_mutasi_wip_' . time().'.pdf';
        $mpdf = new \Mpdf\Mpdf(['orientation' => 'L', 'mode' => 'utf-8', 'format' => [215.9, 330]]);
        $mpdf->WriteHTML($html);
        $this->response->setHeader('Content-Type', 'application/pdf');
        $mpdf->Output($filename, 'D');
    }
    public function export($start, $end, $barang)
    {
        $spreadsheet = new Spreadsheet();
        $spreadsheet->getDefaultStyle()->getFont()->setName('Arial Narrow');
        $spreadsheet->getDefaultStyle()->getFont()->setSize(10);
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setTitle('Laporan Mutasi Bahan Baku');
        $sheet->mergeCells('A1:M1');
        $sheet->setCellValue('A1', 'Laporan Mutasi Bahan Baku');
        $sheet->mergeCells('A2:A3');
        $sheet->setCellValue('A2', 'No');
        $sheet->mergeCells('B2:B3');
        $sheet->setCellValue('B2', 'Kode Barang');
        $sheet->mergeCells('C2:C3');
        $sheet->setCellValue('C2', 'Nama Barang');
        $sheet->mergeCells('D2:D3');
        $sheet->setCellValue('D2', 'Satuan');
        $sheet->mergeCells('E2:E3');
        $sheet->setCellValue('E2', 'Saldo Awal');
        $sheet->mergeCells('F2:H2');
        $sheet->setCellValue('F2', 'Pemasukan');
        $sheet->setCellValue('F3', 'Nomor NPBD');
        $sheet->setCellValue('G3', 'Tanggal NPBD');
        $sheet->setCellValue('H3', 'Jumlah');
        $sheet->mergeCells('I2:K2');
        $sheet->setCellValue('I2', 'Pengeluaran');
        $sheet->setCellValue('I3', 'Nomor F.PRO.02');
        $sheet->setCellValue('J3', 'Tanggal F.PRO.02');
        $sheet->setCellValue('K3', 'Jumlah');
        $sheet->mergeCells('L2:L3');
        $sheet->setCellValue('L2', 'Saldo Akhir');
        $sheet->mergeCells('M2:M3');
        $sheet->setCellValue('M2', 'Gudang');
        $row = 4;
        $no = 1;


        $dtl = new \App\Models\MasterBarang();
        $data = $dtl->where('mb_id', $barang)->first();
        

                    $no = 1;
                    $begin = new \DateTime($start);
                    $ends = new \DateTime($end);

                    $interval = \DateInterval::createFromDateString('1 day');
                    $period = new \DatePeriod($begin, $interval, $ends);

                    // for($i = $begin; $i <= $end; $i->modify('+1 day')){
                    $saldo = 0;
                    foreach ($period as $dt) {
                        $dateRange = $dt->format("Y-m-d");

                        $id = $data->mb_id;
                        // //masuk dari sc
                        $qty_pemasukan = 0;
                        $npbdno = '';
                        $npbddate = '';
                        $fprono = '';
                        $frpodate = '';
                        $datasc = new \App\Models\KirimBarangMkt();
                        $_datasc = $datasc->where('deleted_at', null)
                                ->where('tkb_mkt_status_ic', 1)
                                ->where('tkb_mkt_status_mkt', 2)
                                ->where('date(created_at)', $dateRange)
                                ->orderBy('created_at', 'ASC')
                                ->findAll();
                        foreach($_datasc as $s){
                                        // echo json_encode($s->tkb_mkt_item);
                            $json = json_decode($s->tkb_mkt_item);                           
                            foreach($json as $j){
                                $i = $j->item;
                                $q = $j->qty;
                                if($id == $i){
                                    $qty_pemasukan = $q;
                                    $npbdno .= $s->tkb_mkt_npbdno;
                                    $npbddate = date("d/m/Y", strtotime($s->tkb_mkt_npbddate ?? ''));

                        $sheet->setCellValue('A' . $row, $no);
                        $sheet->setCellValue('B' . $row, $data->mb_kode);
                        $sheet->setCellValue('C' . $row, $data->mb_name);
                        $sheet->setCellValue('D' . $row, $data->mb_satuan);
                         $saldoTemp = '-';
                        if($saldo > 0){
                            $saldoTemp = $saldo;
                        }
                        $sheet->setCellValue('E' . $row, $saldoTemp);
                        $sheet->setCellValue('F' . $row, $npbdno);
                        $sheet->setCellValue('G' . $row, $npbddate);
                        $qty_pemasukanTemp = '-';
                        if($qty_pemasukan > 0){
                            $qty_pemasukanTemp = $qty_pemasukan;
                        }
                        $sheet->setCellValue('H' . $row, $qty_pemasukan);
                        $sheet->setCellValue('I' . $row, '-');
                        $sheet->setCellValue('J' . $row, '-');
                        $sheet->setCellValue('K' . $row, '-');
                        $saldo += $qty_pemasukan;
                        $saldoTemp = '-';
                        if($saldo > 0){
                            $saldoTemp = $saldo;
                        }
                        $sheet->setCellValue('L' . $row, $saldoTemp);
                        $sheet->setCellValue('M' . $row, 'PT Mahkota Tri Angjaya');
                        $no++;
                        $row++;
                    }
                }

            }

                        //pemasukan adjustment
                        $dataadjustadd = new \App\Models\MasterStok();
                        $_dataadjustadd = $dataadjustadd->where('deleted_at', null)
                            ->where('ms_stok_bahanbaku >', 0)
                            ->where('is_adjusted', 1)
                            ->where('date(created_at)', $dateRange)
                            ->orderBy('created_at', 'ASC')
                            ->findAll();
                        foreach($_dataadjustadd as $s){
                            if($id == $s->mb_id){
                                    $qty_pemasukan = $s->ms_stok_bahanbaku;

                                    $sheet->setCellValue('A' . $row, $no);
                                    $sheet->setCellValue('B' . $row, $data->mb_kode);
                                    $sheet->setCellValue('C' . $row, $data->mb_name);
                                    $sheet->setCellValue('D' . $row, $data->mb_satuan);
                                     $saldoTemp = '-';
                                    if($saldo > 0){
                                        $saldoTemp = $saldo;
                                    }
                                    $sheet->setCellValue('E' . $row, $saldoTemp);
                                    $sheet->setCellValue('F' . $row, '-');
                                    $sheet->setCellValue('G' . $row, '-');
                                    $qty_pemasukanTemp = '-';
                                    if($qty_pemasukan > 0){
                                        $qty_pemasukanTemp = $qty_pemasukan;
                                    }
                                    $sheet->setCellValue('H' . $row, $qty_pemasukan);
                                    $sheet->setCellValue('I' . $row, '-');
                                    $sheet->setCellValue('J' . $row, '-');
                                    $sheet->setCellValue('K' . $row, '-');
                                    $saldo += $qty_pemasukan;
                                    $saldoTemp = '-';
                                    if($saldo > 0){
                                        $saldoTemp = $saldo;
                                    }
                                    $sheet->setCellValue('L' . $row, $saldoTemp);
                                    $sheet->setCellValue('M' . $row, 'PT Mahkota Tri Angjaya');
                                    $no++;
                                    $row++;
                                }
                            }


                        $qty_pengeluaran = 0;
                        //pengeluaran adjustment
                        $dataadjustdec = new \App\Models\MasterStok();
                        $_dataadjustdec = $dataadjustdec->where('deleted_at', null)
                            ->where('ms_stok_bahanbaku <', 0)
                            ->where('is_adjusted', 1)
                            ->where('date(created_at)', $dateRange)
                            ->orderBy('created_at', 'ASC')
                            ->findAll();
                        foreach($_dataadjustdec as $s){
                            if($id == $s->mb_id){
                                    $qty_pengeluaran = abs($s->ms_stok_bahanbaku);
                        $sheet->setCellValue('A' . $row, $no);
                        $sheet->setCellValue('B' . $row, $data->mb_kode);
                        $sheet->setCellValue('C' . $row, $data->mb_name);
                        $sheet->setCellValue('D' . $row, $data->mb_satuan);
                         $saldoTemp = '-';
                        if($saldo > 0){
                            $saldoTemp = $saldo;
                        }
                        $sheet->setCellValue('E' . $row, $saldo);
                        $sheet->setCellValue('F' . $row, '-');
                        $sheet->setCellValue('G' . $row, '-');
                        $sheet->setCellValue('H' . $row, '-');
                        $sheet->setCellValue('I' . $row, '-');
                        $sheet->setCellValue('J' . $row, '-');
                        $qty_pengeluaranTemp = '-';
                        if($qty_pengeluaran > 0){
                            $qty_pengeluaranTemp = $qty_pengeluaran;
                        }
                        $sheet->setCellValue('K' . $row, $qty_pengeluaranTemp);
                        $saldo -= $qty_pengeluaran;
                        $saldoTemp = '-';
                        if($saldo > 0){
                            $saldoTemp = $saldo;
                        }
                        $sheet->setCellValue('L' . $row, $saldoTemp);
                        $sheet->setCellValue('M' . $row, 'PT Mahkota Tri Angjaya');
                        $no++;
                        $row++;
                    }
                }

            
                        //waste
                        $datawaste = new \App\Models\PemusnahanBarangIc();
                        $_datawaste = $datawaste->where('deleted_at', null)
                                ->where('pb_ic_status', 1)
                                ->where('date(created_at)', $dateRange)
                                ->orderBy('created_at', 'ASC')
                                ->findAll();
                        foreach($_datawaste as $s){
                            $json = json_decode($s->pb_ic_item);                           
                            foreach($json as $j){
                                $i = $j->item;
                                $q = $j->qty;
                                if($id == $i){
                                    $qty_pengeluaran = $q;
                        $sheet->setCellValue('A' . $row, $no);
                        $sheet->setCellValue('B' . $row, $data->mb_kode);
                        $sheet->setCellValue('C' . $row, $data->mb_name);
                        $sheet->setCellValue('D' . $row, $data->mb_satuan);
                         $saldoTemp = '-';
                        if($saldo > 0){
                            $saldoTemp = $saldo;
                        }
                        $sheet->setCellValue('E' . $row, $saldo);
                        $sheet->setCellValue('F' . $row, '-');
                        $sheet->setCellValue('G' . $row, '-');
                        $sheet->setCellValue('H' . $row, '-');
                        $sheet->setCellValue('I' . $row, '-');
                        $sheet->setCellValue('J' . $row, '-');
                        $qty_pengeluaranTemp = '-';
                        if($qty_pengeluaran > 0){
                            $qty_pengeluaranTemp = $qty_pengeluaran;
                        }
                        $sheet->setCellValue('K' . $row, $qty_pengeluaranTemp);
                        $saldo -= $qty_pengeluaran;
                        $saldoTemp = '-';
                        if($saldo > 0){
                            $saldoTemp = $saldo;
                        }
                        $sheet->setCellValue('L' . $row, $saldoTemp);
                        $sheet->setCellValue('M' . $row, 'PT Mahkota Tri Angjaya');
                        $no++;
                        $row++;
                    }
                }

            }
                        //keluar ke pro
                        $datapro = new \App\Models\KirimBarangIc();
                        $_datapro = $datapro->where('deleted_at', null)
                        ->where('tkb_ic_status', 1)
                        ->where('tkb_ic_status_pro', 1)
                        ->where('date(created_at)', $dateRange)
                        ->orderBy('created_at', 'ASC')
                        ->findAll();
                        foreach($_datapro as $p){
                        // echo json_encode($p->tkb_ic_item);
                            $jsons = json_decode($p->tkb_ic_item);                           
                            foreach($jsons as $js){
                                $is = $js->item;
                                $qs = $js->qty;
                                if($id == $is){
                                    $qty_pengeluaran = $qs;
                                    $fprono = $p->tkb_ic_fpro_no;
                                    $frpodate = date("d/m/Y", strtotime($p->tkb_ic_fpro_date ?? ''));
                        $sheet->setCellValue('A' . $row, $no);
                        $sheet->setCellValue('B' . $row, $data->mb_kode);
                        $sheet->setCellValue('C' . $row, $data->mb_name);
                        $sheet->setCellValue('D' . $row, $data->mb_satuan);
                         $saldoTemp = '-';
                        if($saldo > 0){
                            $saldoTemp = $saldo;
                        }
                        $sheet->setCellValue('E' . $row, $saldo);
                        $sheet->setCellValue('F' . $row, '-');
                        $sheet->setCellValue('G' . $row, '-');
                        $sheet->setCellValue('H' . $row, '-');
                        $sheet->setCellValue('I' . $row, $fprono);
                        $sheet->setCellValue('J' . $row, $frpodate);
                        $qty_pengeluaranTemp = '-';
                        if($qty_pengeluaran > 0){
                            $qty_pengeluaranTemp = $qty_pengeluaran;
                        }
                        $sheet->setCellValue('K' . $row, $qty_pengeluaranTemp);
                        $saldo -= $qty_pengeluaran;
                        $saldoTemp = '-';
                        if($saldo > 0){
                            $saldoTemp = $saldo;
                        }
                        $sheet->setCellValue('L' . $row, $saldoTemp);
                        $sheet->setCellValue('M' . $row, 'PT Mahkota Tri Angjaya');
                        $no++;
                        $row++;
                    }
                }

            }




        }
        // $sheet->mergeCells('A' . $row . ':D' . $row);

        $this->setCenter($sheet, 1, 3, 'A', 'M');
        $this->setBold($sheet, $row, $row, 'A', 'M');
        $this->setBold($sheet, 1, 3, 'A', 'M');
        $this->setBorderAutoFit($sheet, 1, $row, 0, 'A', 'M');
        $filename = 'Laporan Mutasi Bahan Baku - '. $start . ' - ' . $end;;
        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xls');
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $filename . '.xls"');
        header('Cache-Control: max-age=0');
//            ob_end_clean();
        $writer->save('php://output');

    }

    public function setBorderAutoFit($sheet, $start, $row, $minus, $l_start, $l_end, $ls_start = '', $ls_end = '', $repeat = 0)
    {
        $styleArray = array(
            'borders' => [
                'top' => [
                    'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
                ],
                'left' => [
                    'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
                ],
                'right' => [
                    'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
                ],
                'bottom' => [
                    'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
                ],
            ],
        );
        $temp = $l_start;
        foreach (range($l_start, $l_end) as $letter) {
            for ($m = $start; $m <= $row - $minus; $m++) {
                $sheet->getStyle($letter . $m)->applyFromArray($styleArray);
            }
        }

        $l_start = $temp;
        foreach (range($l_start, $l_end) as $columnID) {
            $sheet->getColumnDimension($columnID)
                ->setAutoSize(true);
        }

        unset($styleArray);
    }

    public function setBold($sheet, $row, $end, $l_start, $l_end, $ls_start = '', $ls_end = '', $repeat = 0)
    {
        $style = array(
            'font' => [
                'bold' => true,
            ],
        );
        foreach (range($l_start, $l_end) as $letter) {
            for ($m = $row; $m <= $end; $m++) {
                $sheet->getStyle($letter . $m)->applyFromArray($style);
            }
        }
        if ($ls_start != '') {
            foreach (range($l_start, $l_end) as $letter) {
                $sheet->getStyle($letter . $row)->applyFromArray($style);

            }
            foreach (range($l_start, $l_end) as $letter1) {
                foreach (range($ls_start, $ls_end) as $letter2) {
                    $sheet->getStyle($letter1 . $letter2 . $row)->applyFromArray($style);
                }
                break;
            }
            foreach (range($l_start, $l_end) as $letter) {
                $sheet->getStyle($letter . $end)->applyFromArray($style);
            }
            if ($repeat == 0) {
                foreach (range($l_start, $l_end) as $letter1) {
                    foreach (range($ls_start, $ls_end) as $letter2) {
                        $sheet->getStyle($letter1 . $letter2 . $row)->applyFromArray($style);
                    }
                    break;
                }
                foreach (range($l_start, $l_end) as $letter1) {
                    foreach (range($ls_start, $ls_end) as $letter2) {
                        $sheet->getStyle($letter1 . $letter2 . $end)->applyFromArray($style);
                    }
                    break;
                }
            } else {
                for ($i = 0; $i <= $repeat; $i++) {
                    foreach (range($l_start, $l_end) as $letter1) {
                        foreach (range($ls_start, $ls_end) as $letter2) {
                            $sheet->getStyle($letter1 . $letter2 . $row)->applyFromArray($style);
                        }
                        break;
                    }
                    foreach (range($l_start, $l_end) as $letter1) {
                        foreach (range($ls_start, $ls_end) as $letter2) {
                            $sheet->getStyle($letter1 . $letter2 . $end)->applyFromArray($style);
                        }
                        break;
                    }
                    $l_start++;
                }
            }
        }
    }

    public function setCenter($sheet, $row, $end, $l_start, $l_end, $ls_start = '', $ls_end = '', $repeat = 0)
    {
        $style = array(
            'alignment' => array(
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
            )
        );
        foreach (range($l_start, $l_end) as $letter) {
            for ($m = $row; $m <= $end; $m++) {
                $sheet->getStyle($letter . $m)->applyFromArray($style);
            }
        }
        if ($ls_start != '') {
            foreach (range($l_start, $l_end) as $letter) {
                $sheet->getStyle($letter . $row)->applyFromArray($style);

            }
            foreach (range($l_start, $l_end) as $letter1) {
                foreach (range($ls_start, $ls_end) as $letter2) {
                    $sheet->getStyle($letter1 . $letter2 . $row)->applyFromArray($style);
                }
                break;
            }
            foreach (range($l_start, $l_end) as $letter) {
                $sheet->getStyle($letter . $end)->applyFromArray($style);

            }
            if ($repeat == 0) {
                foreach (range($l_start, $l_end) as $letter1) {
                    foreach (range($ls_start, $ls_end) as $letter2) {
                        $sheet->getStyle($letter1 . $letter2 . $row)->applyFromArray($style);
                    }
                    break;
                }
                foreach (range($l_start, $l_end) as $letter1) {
                    foreach (range($ls_start, $ls_end) as $letter2) {
                        $sheet->getStyle($letter1 . $letter2 . $end)->applyFromArray($style);
                    }
                    break;
                }
            } else {
                for ($i = 0; $i <= $repeat; $i++) {
                    foreach (range($l_start, $l_end) as $letter1) {
                        foreach (range($ls_start, $ls_end) as $letter2) {
                            $sheet->getStyle($letter1 . $letter2 . $row)->applyFromArray($style);
                        }
                        break;
                    }
                    foreach (range($l_start, $l_end) as $letter1) {
                        foreach (range($ls_start, $ls_end) as $letter2) {
                            $sheet->getStyle($letter1 . $letter2 . $end)->applyFromArray($style);
                        }
                        break;
                    }
                    $l_start++;
                }
            }
        }
    }

    public function setNumber($sheet, $row, $l_start, $l_end, $ls_start = '', $ls_end = '')
    {
        foreach (range($l_start, $l_end) as $letter) {
            $sheet->getStyle($letter . $row)->getNumberFormat()
                ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);

        }
        if ($ls_start != '') {
            foreach (range('A', $l_end) as $letter1) {
                foreach (range($ls_start, $ls_end) as $letter2) {
                    $sheet->getStyle($letter1 . $letter2 . $row)->getNumberFormat()
                        ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
                }
                break;
            }
        }
    }

}