<?php

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class SPIChartDekstopController extends BaseController
{

    public function getAll()
    {
        $getParams = $this->request->get();

        $getParams['limit'] = $this->getDefaultLimit($getParams);
        $getParams['offset'] = array_key_exists('offset', $getParams) ? $getParams['offset'] : 0;
        $getParams['keyword'] = array_key_exists('keyword', $getParams) ? $getParams['keyword'] : '';
        $getParams['tahun'] = array_key_exists('tahun', $getParams) ? $getParams['tahun'] : '-9';
        $getParams['instansi'] = array_key_exists('instansi', $getParams) ? $getParams['instansi'] : -9;

        $params = $this->validate(
          $getParams,
          [
            'limit' => ['validators' => ['digit']],
            'offset' => ['validators' => ['digit']],
            'keyword' => ['validators' => []],
            'instansi' => ['validators' => []],
          ]
        );
        
        $kp = SPIChartDekstop::find([
            'conditions' => "(tahun ilike :keyword: or nama_klpd ilike :keyword:)
                and (tahun = :tahun: or :tahun: = '-9')
                and (code_klpd = :instansi: or :instansi: = -9)
                and is_active = true",
            'bind' => [
                'keyword' => '%' . $params['keyword'] . '%',
                'tahun' => $getParams['tahun'],
                'instansi' => $getParams['instansi'],
            ],
            'limit' => $params['limit'],
            'offset' => $params['offset']
        ]);


        $total_record = SPIChartDekstop::find([
            'conditions' => "(tahun ilike :keyword: or nama_klpd ilike :keyword:)
                and (tahun = :tahun: or :tahun: = '-9')
                and (code_klpd = :instansi: or :instansi: = -9)
                and is_active = true",
            'bind' => [
                'keyword' => '%' . $params['keyword'] . '%',
                'tahun' => $getParams['tahun'],
                'instansi' => $getParams['instansi'],
            ],
        ])->count();

        return new PaginatedResponse($kp, $total_record, $params['limit'], $params['offset']);
    }

    public function detail($id)
    {
        $kp = SPIChartDekstop::findFirst([
            'conditions' => 'id = :id: and is_active = true',
            'bind' => [
                'id' => $id
            ]
        ]);

        if (empty($kp)) :
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Hasil SPI KLPD dengan id' . $id . 'tidak diketemukan');
        endif;

        $result = $kp->toDetail();

        return new CollectionPaginatedResponse($result);
    }

    public function delete($id)
    {
        $this->checkScopes(['spi.manage_master_data']);

        $kp = SPIChartDekstop::findFirst($id);

        if (empty($kp)) :
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'id ' . $id . ' tidak diketemukan');
        endif;
        if (!empty($kp->deleted_at)) :
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'id ' . $id . ' mungkin sudah terhapus');
        endif;

        $date = date('Y-m-d H:i:s');
        $kp->is_active = false;
        $kp->updated_at = $date;
        $kp->deleted_at = $date;
        $kp->updated_by = $this->shared->user->um_id;
        $status = $kp->save();

        if (!$status) {
            $errors = $kp->getMessages();
            $error_messages = [];

            foreach ($errors as $key => $error) :
                $error_messages[] = [
                    "field" => $error->getField(),
                    "message" => $error->getMessage()
                ];
            endforeach;

            throw new ValidationException($error_messages);
        } else {
            return new CollectionPaginatedResponse([$kp]);
        }
    }

    public function store()
    {
        $this->db->begin();

        try {
             $this->checkScopes(['spi.manage_master_data']);

            $rawBody = $this->request->getJsonRawBody(true);
            $params = $this->validate(
                $rawBody,
                [
                    'tahun' => ['validators' => ['required']],
                    'code_klpd' => ['validators' => ['required']],

                    'female_num_ek' => ['validators' => ['required']],
                    'female_num_in' => ['validators' => ['required']],

                    'hasil_spi' => ['validators' => ['required']],
                    'hasil_spi_eksternal' => ['validators' => ['required']],
                    'hasil_spi_expert' => ['validators' => ['required']],
                    'hasil_spi_internal' => ['validators' => ['required']],
                    
                    'jbtn_ex_s1' => ['validators' => ['required']],
                    'jbtn_ex_sarjana' => ['validators' => ['required']],
                    
                    'male_num_ek' => ['validators' => ['required']],
                    'male_num_in' => ['validators' => ['required']],
                    
                    'max50_ek' => ['validators' => ['required']],
                    'max50_in' => ['validators' => ['required']],
                    
                    'min30_ek' => ['validators' => ['required']],
                    'min30_in' => ['validators' => ['required']],
                    
                    'range30to50_ek' => ['validators' => ['required']],
                    'range30to50_in' => ['validators' => ['required']],
                ]
            );

            $tahun = $params['tahun'];
            $code_klpd = $params['code_klpd'];
            $total_record = SPIChartDekstop::find([
                'conditions' => "is_active = true and tahun = '$tahun' and code_klpd = '$code_klpd'"
            ])->count();

            if ($total_record > 0) {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Report pada $tahun Sudah digunakan");
            }

            $instansi = Instansi::findFirst([
                'conditions' => 'id = :id:',
                'bind' => [
                    'id' => $code_klpd
                ]
            ]);

            if ($instansi == null) {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Instansi tidak ditemukan");
            }

            $kp = new SPIChartDekstop();
            $kp->id = null;
            $kp->tahun = $tahun;
            $kp->code_klpd = $code_klpd;
            $kp->nama_klpd = $instansi->nama;

            $kp->female_num_ek = $params['female_num_ek'];
            $kp->female_num_in = $params['female_num_in'];

            $kp->hasil_spi = $params['hasil_spi'];
            $kp->hasil_spi_eksternal = $params['hasil_spi_eksternal'];
            $kp->hasil_spi_expert = $params['hasil_spi_expert'];
            $kp->hasil_spi_internal = $params['hasil_spi_internal'];

            $kp->jbtn_ex_s1 = $params['jbtn_ex_s1'];
            $kp->jbtn_ex_sarjana = $params['jbtn_ex_sarjana'];

            $kp->male_num_ek = $params['male_num_ek'];
            $kp->male_num_in = $params['male_num_in'];

            $kp->max50_ek = $params['max50_ek'];
            $kp->max50_in = $params['max50_in'];

            $kp->min30_ek = $params['min30_ek'];
            $kp->min30_in = $params['min30_in'];

            $kp->range30to50_ek = $params['range30to50_ek'];
            $kp->range30to50_in = $params['range30to50_in'];

            $kp->is_active = true;
            $kp->created_at = date('Y-m-d H:i:s');
            $kp->created_by = $this->shared->user->um_id;
            $status = $kp->save();

            if (!$status) {
                $errors = $kp->getMessages();
                $error_messages = [];

                foreach ($errors as $key => $error) :
                    $error_messages[] = [
                        "field" => $error->getField(),
                        "message" => $error->getMessage()
                    ];
                endforeach;

                throw new ValidationException($error_messages);
            }
            
            $this->db->commit();
        } catch (\Exception $ex) {
            $this->db->rollback();
            throw $ex;
        }
        
        return new CollectionPaginatedResponse([$kp]);
    }

    public function update($id)
    {
        $this->db->begin();

        try {
             $this->checkScopes(['spi.manage_master_data']);
            $kp = SPIChartDekstop::findFirst($id);
    
            if (empty($kp)) :
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'id' . $id . 'tidak ditemukan');
            endif;
    
            if (!empty($kp->deleted_at)) :
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'id ' . $id  . 'mungkin sudah terhapus');
            endif;
    
            $rawBody = $this->request->getJsonRawBody(true);
            $params = $this->validate(
                $rawBody,
                [
                    'tahun' => ['validators' => ['required']],
                    'code_klpd' => ['validators' => ['required']],

                    'female_num_ek' => ['validators' => ['required']],
                    'female_num_in' => ['validators' => ['required']],

                    'hasil_spi' => ['validators' => ['required']],
                    'hasil_spi_eksternal' => ['validators' => ['required']],
                    'hasil_spi_expert' => ['validators' => ['required']],
                    'hasil_spi_internal' => ['validators' => ['required']],
                    
                    'jbtn_ex_s1' => ['validators' => ['required']],
                    'jbtn_ex_sarjana' => ['validators' => ['required']],
                    
                    'male_num_ek' => ['validators' => ['required']],
                    'male_num_in' => ['validators' => ['required']],
                    
                    'max50_ek' => ['validators' => ['required']],
                    'max50_in' => ['validators' => ['required']],
                    
                    'min30_ek' => ['validators' => ['required']],
                    'min30_in' => ['validators' => ['required']],
                    
                    'range30to50_ek' => ['validators' => ['required']],
                    'range30to50_in' => ['validators' => ['required']],
                ]
            );
    
            $tahun = $params['tahun'];
            $code_klpd = $params['code_klpd'];
            $total_record = SPIChartDekstop::find([
                'conditions' => "is_active = true and tahun = '$tahun' and code_klpd = '$code_klpd' and id != '$id'"
            ])->count();
    
            if ($total_record > 0) {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Report pada $tahun Sudah digunakan");
            }

            $instansi = Instansi::findFirst([
                'conditions' => 'id = :id:',
                'bind' => [
                    'id' => $code_klpd
                ]
            ]);

            if ($instansi == null) {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Instansi tidak ditemukan");
            }
            
            $kp->tahun = $tahun;
            $kp->code_klpd = $code_klpd;
            $kp->nama_klpd = $instansi->nama;

            $kp->female_num_ek = $params['female_num_ek'];
            $kp->female_num_in = $params['female_num_in'];

            $kp->hasil_spi = $params['hasil_spi'];
            $kp->hasil_spi_eksternal = $params['hasil_spi_eksternal'];
            $kp->hasil_spi_expert = $params['hasil_spi_expert'];
            $kp->hasil_spi_internal = $params['hasil_spi_internal'];

            $kp->jbtn_ex_s1 = $params['jbtn_ex_s1'];
            $kp->jbtn_ex_sarjana = $params['jbtn_ex_sarjana'];

            $kp->male_num_ek = $params['male_num_ek'];
            $kp->male_num_in = $params['male_num_in'];

            $kp->max50_ek = $params['max50_ek'];
            $kp->max50_in = $params['max50_in'];

            $kp->min30_ek = $params['min30_ek'];
            $kp->min30_in = $params['min30_in'];

            $kp->range30to50_ek = $params['range30to50_ek'];
            $kp->range30to50_in = $params['range30to50_in'];

            $kp->is_active = true;

            $kp->updated_at = date('Y-m-d H:i:s');
            $kp->updated_by = $this->shared->user->um_id;
            $status = $kp->save();
    
            if (!$status) {
                $errors = $kp->getMessages();
                $error_messages = [];
    
                foreach ($errors as $key => $error) :
                    $error_messages[] = [
                        "field" => $error->getField(),
                        "message" => $error->getMessage()
                    ];
                endforeach;
    
                throw new ValidationException($error_messages);
            }

            $this->db->commit();
        } catch (\Exception $ex) {
            $this->db->rollback();
            throw $ex;
        }
        
        return new CollectionPaginatedResponse([$kp]);
    }
    
}