<?php

namespace App\Models;

use App\Models\AreaModel;

use CodeIgniter\Model;

class CustomerModel extends Model
{
    protected $table            = 'tbl_customer';
    protected $primaryKey       = 'id';
    protected $useAutoIncrement = true;
    protected $returnType       = 'object';
    protected $useSoftDeletes   = false;
    protected $protectFields    = false;
    protected $allowedFields    = [];

    protected bool $allowEmptyInserts = false;
    protected bool $updateOnlyChanged = true;

    protected array $casts = [];
    protected array $castHandlers = [];

    // Dates
    protected $useTimestamps = false;
    protected $dateFormat    = 'datetime';
    protected $createdField  = 'created_at';
    protected $updatedField  = 'updated_at';
    protected $deletedField  = 'deleted_at';

    // Validation
    protected $validationRules      = [];
    protected $validationMessages   = [];
    protected $skipValidation       = false;
    protected $cleanValidationRules = true;

    // Callbacks
    protected $allowCallbacks = true;
    protected $beforeInsert   = [];
    protected $afterInsert    = [];
    protected $beforeUpdate   = [];
    protected $afterUpdate    = [];
    protected $beforeFind     = [];
    protected $afterFind      = [];
    protected $beforeDelete   = [];
    protected $afterDelete    = [];



    public function _set_query()
    {

        $this->join('tbl_package','tbl_package.id=tbl_customer.package_id');
        $this->join('tbl_area','tbl_area.id=tbl_customer.village_id');
        if(!empty($this->search)){
            $this->groupStart();
            $this->like('UPPER(tbl_customer.name)',strtoupper($this->search));
            $this->orLike('UPPER(tbl_customer.description)',strtoupper($this->search));
            $this->orLike('tbl_package.name',$this->search);
            $this->orLike('tbl_area.name',$this->search);
            $this->groupEnd();
        }
    }

    public function getAllData()
    {
        $data = [];

        if ($this->columns[$this->order[0]['column']]['data'] == 'no') {
            $this->columns[$this->order[0]['column']]['data'] = $this->table . '.id';
        }

        //all data
        $this->select($this->table . '.id');
        $this->countData = $this->countAllResults();

        //filtered
        $this->select($this->table . '.id');
        $this->_set_query();
        $this->filterData = $this->countAllResults();

        //view data
        $this->select('tbl_customer.*, tbl_package.name AS package_name, tbl_area.name AS village_name');
        $this->_set_query();
        $this->orderBy($this->columns[$this->order[0]['column']]['data'], $this->order[0]['dir']);
        $this->limit( intval($this->limit), intval($this->start));
        $getData = $this->findAll();
        $no      = $this->start;
        
        if ( count((array) $getData) > 0) {
            foreach ($getData as $row) {
                $row->no = ++$no;
                
                // $row->village_name = '<span class="text-warning"> '.$row->village_name.'</span>';

                $row->package_name = '<span class="text-warning">'.$row->package_name.'</span>';


                $row->option = '<p class="m-0 text-nowrap">';


                if(getAccessMenu(4,"is_read", session()->get('role_id'))){

                    $row->option = $row->option . '<a href="javascript:void(0)" title="Ubah data" data-link="' . base_url() . 'customer/detail/' . $row->id . '" class=" datatable-edit me-2"><i class="bx bx-show text-dark" style="font-size: 23px;"></i></a>';
                }

                if(getAccessMenu(4,"is_delete", session()->get('role_id'))){

                    $row->option = $row->option . '<a href="javascript:void(0)" title="Ubah data" data-link="' . base_url() . 'customer/edit/' . $row->id . '" class=" datatable-edit me-2"><i class="bx bx-edit text-warning" style="font-size: 20px;"></i></a>';
                }


                if(getAccessMenu(4,"is_update", session()->get('role_id'))){

                    $row->option = $row->option . ' <a href="javascript:void(0)" data-id="' . $row->id . '" title="Hapus data" data-link="' . base_url() . 'customer/delete" class=" datatable-delete"><i class="bx bx-trash text-danger" style="font-size: 20px;"></i></a>';
                }

                $row->option    = $row->option . '</p>';


                $data[]      = $row;
            }
        }

        $result = array(
            "draw"            => $this->draw,
            "recordsTotal"    => $this->countData,
            "recordsFiltered" => $this->filterData,
            "data"            => $data,
        );

        return json_encode($result);
    }



    public function _set_query_village()
    {

        $this->where('UPPER(type)',"VILLAGE");
        if(!empty($this->search)){
            $this->groupStart();
            $this->like('UPPER(name)',$this->search);
            $this->groupEnd();
        }
    }

    public function getAllDataVillage()
    {
        $data = [];

        if ($this->columns[$this->order[0]['column']]['data'] == 'no') {
            $this->columns[$this->order[0]['column']]['data'] = $this->table . '.id';
        }

        //all data
        $this->select($this->table . '.id');
        $this->countData = $this->countAllResults();

        //filtered
        $this->select($this->table . '.id');
        $this->_set_query_village();
        $this->filterData = $this->countAllResults();

        //view data
        $this->_set_query_village();
        $this->orderBy($this->columns[$this->order[0]['column']]['data'], $this->order[0]['dir']);
        $this->limit( intval($this->limit), intval($this->start));
        $getData = $this->findAll();
        $no      = $this->start;
        
        if ( count((array) $getData) > 0) {
            foreach ($getData as $row) {

                $row->parent_name = $this->find($row->parent_id)->name;

                $row->no = ++$no;
                
                $row->option = '<p class="m-0 text-nowrap">';

                if(getAccessMenu(2,"is_delete", session()->get('role_id'))){

                    $row->option = $row->option . '<a href="javascript:void(0)" title="Ubah data" data-link="' . base_url() . 'area/village/edit/' . $row->id . '" class=" datatable-edit me-2"><i class="bx bx-edit text-warning" style="font-size: 20px;"></i></a>';
                }


                if(getAccessMenu(2,"is_update", session()->get('role_id'))){

                    $row->option = $row->option . ' <a href="javascript:void(0)" data-id="' . $row->id . '" title="Hapus data" data-link="' . base_url() . 'area/village/delete" class=" datatable-delete"><i class="bx bx-trash text-danger" style="font-size: 20px;"></i></a>';
                }

                $row->option    = $row->option . '</p>';


                $data[]      = $row;
            }
        }

        $result = array(
            "draw"            => $this->draw,
            "recordsTotal"    => $this->countData,
            "recordsFiltered" => $this->filterData,
            "data"            => $data,
        );

        return json_encode($result);
    }

    

    public function getAllSubdistirctName()
    {

        $this->select('id');
        $this->where('UPPER(type)',"SUBDISTRICT");
        $this->countData    = $this->findAll();

        $this->select('id');
        $this->where('UPPER(type)',"SUBDISTRICT");
        $this->like('UPPER(name)', strtoupper($this->name));
        $this->filterData = $this->countAllResults();

        $this->where('UPPER(type)',"SUBDISTRICT");
        $this->like('UPPER(name)', strtoupper($this->name));
        $this->limit(intval($this->limit), intval($this->start));
        $getData        = $this->findAll();

        $data           = [];
        $no             = $this->start;

        if ( count((array) $getData) > 0) {
            foreach ($getData as $row) {
                $row->no    = $no + 1;
                $data[]     = $row;
            }
        }

        return json_encode(array(
            'recorsTotal'     => $this->countData,
            'recordsFiltered' => $this->filterData,
            'data'            => $data
        ));
    }


    public function getAllVillageName()
    {

        $this->select('id');
        $this->where('UPPER(type)',"VILLAGE");
        $this->countData    = $this->findAll();

        $this->select('id');
        $this->where('UPPER(type)',"VILLAGE");
        $this->like('UPPER(name)', strtoupper($this->name));
        $this->filterData = $this->countAllResults();

        $this->where('UPPER(type)',"VILLAGE");
        $this->like('UPPER(name)', strtoupper($this->name));
        $this->limit(intval($this->limit), intval($this->start));
        $getData        = $this->findAll();

        $data           = [];
        $no             = $this->start;

        if ( count((array) $getData) > 0) {
            foreach ($getData as $row) {
                $row->no    = $no + 1;
                $data[]     = $row;
            }
        }

        return json_encode(array(
            'recorsTotal'     => $this->countData,
            'recordsFiltered' => $this->filterData,
            'data'            => $data
        ));
    }


    public function getExportData($year,$villageId_)
    {

        $PaymentModel   =  new PaymentModel();
        $AreaModel      =  new AreaModel();



        if(strtoupper($villageId_)=="ALL"){

            $AreaModel->select('id,name');
            $AreaModel->where('UPPER(type)',"SUBDISTRICT");
            $subdistricts = $AreaModel->findAll();

            if(empty($subdistricts)){

            }

            $results = [];
            foreach($subdistricts AS $subdistrict){

                $subdistrict->villages = [];

                $AreaModel->select('id,name');
                $AreaModel->where('UPPER(type)',"VILLAGE");
                $AreaModel->where('parent_id',$subdistrict->id);
                $villages = $AreaModel->findAll();

                if(!empty($villages)){

                    foreach ($villages as $village) {

                        $village->customers = [];

                        $this->select('id, name');
                        $this->where('village_id',$village->id);
                        $customers = $this->findAll();
                        if(!empty($customers)){
                            foreach ($customers as $index => $customer) {

                                $customer_ = [($index + 1), $customer->name];
                                $month = 1;
                                while (true) {
                                    
                                    $monthName = $month;
                                    if($month<10){
                                        $monthName = '0'.$month;
                                    }

                                    $PaymentModel->select('id');
                                    $PaymentModel->where('customer_id',$customer->id);
                                    $PaymentModel->where('MONTH(date)',$monthName);
                                    $PaymentModel->where('YEAR(date)',$year);
                                    $PaymentModel->groupBy('customer_id');
                                    $pay = $PaymentModel->first();

                                    if(empty((array)$pay)){
                                        $customer_[] = false;
                                    }else{
                                        $customer_[] = true;
                                    }


                                    if($month>=12){
                                        break;
                                    }

                                    $month++;

                                }

                                $village->customers[] = $customer_;
                            }   
                        }   


                        $subdistrict->villages[] = $village;

                    }

                }



            }


            return $subdistricts;

        }else{
         
            $subdistrictOnlyId = $AreaModel->select('parent_id')->where('id',$villageId_)->where('UPPER(type)',"VILLAGE")->first()->parent_id;
            
            $AreaModel->select('id,name');
            $AreaModel->where('UPPER(type)',"SUBDISTRICT");
            $AreaModel->where('id',$subdistrictOnlyId);
            $subdistricts = $AreaModel->findAll();

            if(empty($subdistricts)){

            }

            $results = [];
            foreach($subdistricts AS $subdistrict){

                $subdistrict->villages = [];

                $AreaModel->select('id,name');
                $AreaModel->where('UPPER(type)',"VILLAGE");
                $AreaModel->where('id',$villageId_);
                $AreaModel->where('parent_id',$subdistrict->id);
                $villages = $AreaModel->findAll();

                if(!empty($villages)){

                    foreach ($villages as $village) {

                        $village->customers = [];

                        $this->select('id, name');
                        $this->where('village_id',$village->id);
                        $customers = $this->findAll();
                        if(!empty($customers)){
                            foreach ($customers as $index => $customer) {

                                $customer_ = [($index + 1), $customer->name];
                                $month = 1;
                                while (true) {
                                    
                                    $monthName = $month;
                                    if($month<10){
                                        $monthName = '0'.$month;
                                    }

                                    $PaymentModel->select('id');
                                    $PaymentModel->where('customer_id',$customer->id);
                                    $PaymentModel->where('MONTH(date)',$monthName);
                                    $PaymentModel->where('YEAR(date)',$year);
                                    $PaymentModel->groupBy('customer_id');
                                    $pay = $PaymentModel->first();

                                    if(empty((array)$pay)){
                                        $customer_[] = false;
                                    }else{
                                        $customer_[] = true;
                                    }


                                    if($month>=12){
                                        break;
                                    }

                                    $month++;

                                }

                                $village->customers[] = $customer_;
                            }   
                        }   


                        $subdistrict->villages[] = $village;

                    }

                }



            }


            return $subdistricts;

        }

    }



    public function getAllCustomerName()
    {

        $this->select('id');
        $this->countData    = $this->findAll();

        $this->select('id');
        $this->like('UPPER(name)', strtoupper($this->name));
        $this->filterData = $this->countAllResults();

        $this->like('UPPER(name)', strtoupper($this->name));
        $this->limit(intval($this->limit), intval($this->start));
        $getData        = $this->findAll();

        $data           = [];
        $no             = $this->start;

        if ( count((array) $getData) > 0) {
            foreach ($getData as $row) {
                $row->no    = $no + 1;
                $data[]     = $row;
            }
        }

        return json_encode(array(
            'recorsTotal'     => $this->countData,
            'recordsFiltered' => $this->filterData,
            'data'            => $data
        ));
    }



}
