<?php

namespace App\Models;

use CodeIgniter\Model;
use App\Libraries\GeneralLib;

class PangkalanModel extends Model
{
    protected $table      = 'pangkalan';
    protected $primaryKey = 'id_pangkalan';

    protected $useAutoIncrement = true;

    protected $returnType     = 'array';
    protected $useSoftDeletes = true;

    protected $allowedFields = ['id_agen', 'tanggal_pengajuan', 'id_tipe_pangkalan', 'id_registrasi', 'nama_pangkalan', 'no_telepon', 'kuantiti', 'no_sku', 'sku', 'tipe_pembayaran', 'foto_luar', 'foto_dalam', 'nama_pemilik', 'no_ktp_pemilik', 'ktp', 'selfie_ktp', 'no_hp_pemilik', 'email', 'alamat', 'provinsi', 'kabupaten', 'kecamatan', 'kelurahan', 'kode_pos', 'lat', 'lng', 'status'];

    // 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 GetDT($request,$extra="")
    {
        $genlib = new GeneralLib();
        $db = db_connect();
        //$builder = $db->table('vpangkalan');
        
        $return_data = array();
        $return_data['draw'] = $request['draw'];

        $where = array();
        foreach($request['columns'] as $item){
            if (($item['searchable']=='true') && (!empty($item['search']['value'])))
            {
                $where[] = $item['data']." like '%".$item['search']['value']."%'";
            }
        }

        if (!empty($request['search']['value']))
        {
            $where = array();
            foreach($request['columns'] as $item){
                if (($item['searchable']=='true'))
                {
                    $where[] = $item['data']." like '%".$request['search']['value']."%'";
                }
            }
        }

        if(isset($request['searchBuilder']))
        {
            $filter = $genlib->dt_traverse_search_builder($request['searchBuilder']);
        }else{
            $filter = implode(" or ",$where);
        }
        //die(var_dump($filter));

        $order_by = $request['columns'][$request['order'][0]['column']]['data'];
        $order = $request['order'][0]['dir'];
        
        $squery = "SELECT
                    pangkalan.id_pangkalan as DT_RowId,
                    pangkalan.id_pangkalan, 
                    pangkalan.id_agen, 
                    pangkalan.tanggal_pengajuan, 
                    pangkalan.id_tipe_pangkalan, 
                    pangkalan.id_registrasi, 
                    pangkalan.nama_pangkalan, 
                    pangkalan.kuantiti, 
                    pangkalan.no_sku, 
                    pangkalan.sku, 
                    pangkalan.tipe_pembayaran, 
                    pangkalan.foto_luar, 
                    pangkalan.foto_dalam, 
                    pangkalan.nama_pemilik, 
                    pangkalan.no_ktp_pemilik, 
                    pangkalan.ktp, 
                    pangkalan.selfie_ktp,
                    pangkalan.no_hp_pemilik, 
                    pangkalan.email, 
                    pangkalan.provinsi, 
                    pangkalan.kabupaten, 
                    pangkalan.kecamatan, 
                    pangkalan.kelurahan, 
                    pangkalan.kode_pos, 
                    pangkalan.lat, 
                    pangkalan.lng, 
                    pangkalan.`status`, 
                    agen.sold_id, 
                    agen.nama_perusahaan, 
                    tipe_pangkalan.nama_tipe, 
                    geo_provinsi.nama_provinsi, 
                    geo_kabupaten.nama_kabupaten, 
                    geo_kecamatan.nama_kecamatan, 
                    geo_kelurahan.nama_kelurahan, 
                    pangkalan.no_telepon, 
                    pangkalan.alamat
                FROM
                    pangkalan
                    LEFT JOIN
                    agen
                    ON 
                        pangkalan.id_agen = agen.id_agen
                    LEFT JOIN
                    tipe_pangkalan
                    ON 
                        pangkalan.id_tipe_pangkalan = tipe_pangkalan.id_tipe_pangkalan
                    LEFT JOIN
                    geo_provinsi
                    ON 
                        pangkalan.provinsi = geo_provinsi.kode_provinsi
                    LEFT JOIN
                    geo_kabupaten
                    ON 
                        pangkalan.kabupaten = geo_kabupaten.kode_kabupaten
                    LEFT JOIN
                    geo_kecamatan
                    ON 
                        pangkalan.kecamatan = geo_kecamatan.kode_kecamatan
                    LEFT JOIN
                    geo_kelurahan
                    ON 
                        pangkalan.kelurahan = geo_kelurahan.kode_kelurahan";

        $squery_total = "SELECT
                    count(pangkalan.id_pangkalan) as jumlah
                    FROM
                    pangkalan";

        $squery_total_filtered = "SELECT
                    count(pangkalan.id_pangkalan) as jumlah
                    FROM
                    pangkalan LEFT JOIN
                    agen
                    ON 
                        pangkalan.id_agen = agen.id_agen
                    LEFT JOIN
                    tipe_pangkalan
                    ON 
                        pangkalan.id_tipe_pangkalan = tipe_pangkalan.id_tipe_pangkalan
                    LEFT JOIN
                    geo_provinsi
                    ON 
                        pangkalan.provinsi = geo_provinsi.kode_provinsi
                    LEFT JOIN
                    geo_kabupaten
                    ON 
                        pangkalan.kabupaten = geo_kabupaten.kode_kabupaten
                    LEFT JOIN
                    geo_kecamatan
                    ON 
                        pangkalan.kecamatan = geo_kecamatan.kode_kecamatan
                    LEFT JOIN
                    geo_kelurahan
                    ON 
                        pangkalan.kelurahan = geo_kelurahan.kode_kelurahan";

        if (!empty($filter))
        {
            $filter = "(".$filter.")";
        }

        if (!empty($extra)){
            $squery_total .= " where ".$extra;
            if (empty($filter)){
                $filter = $extra;
            }else{
                $filter .= " and (".$extra.")";
            }
        }
        
        if (!empty($order_by))
        {
            $order_by = " order by ".$order_by." ".$order;
        }
        
        if (!empty($filter))
        {
            //$data = $builder->where($filter)->orderBy($order_by, $order)->limit($request['start'],$request['length'])->get()->getResult();
            $squery .= " where ".$filter.$order_by." limit ".$request['start'].",".$request['length'];
            $squery_total_filtered .= " where ".$filter;
        }else{
            //$data = $builder->orderBy($order_by, $order)->limit($request['start'],$request['length'])->get()->getResult();
            $squery .= $order_by." limit ".$request['start'].",".$request['length'];
        }
        //die(var_dump($squery));
        //die(var_dump($registrasi));
        $query_total = $db->query($squery_total);
        $query_total_filtered = $db->query($squery_total_filtered);
        $query = $db->query($squery);

        $row_total = $query_total->getRow();
        $row_total_filtered = $query_total_filtered->getRow();
        $data = $query->getResult();

        $return_data['recordsTotal'] = $row_total->jumlah;
        $return_data['recordsFiltered'] =  $row_total_filtered->jumlah;
        $return_data['data'] = $data;

        return json_encode($return_data);
    }

    public function GetDTAgen($request,$id_agen,$extra="")
    {
        $genlib = new GeneralLib();
        $db = db_connect();
        //$builder = $db->table('vpangkalan');
        
        $return_data = array();
        $return_data['draw'] = $request['draw'];

        $where = array();
        foreach($request['columns'] as $item){
            if (($item['searchable']=='true') && (!empty($item['search']['value'])))
            {
                $where[] = $item['data']." like '%".$item['search']['value']."%'";
            }
        }

        if (!empty($request['search']['value']))
        {
            $where = array();
            foreach($request['columns'] as $item){
                if (($item['searchable']=='true'))
                {
                    $where[] = $item['data']." like '%".$request['search']['value']."%'";
                }
            }
        }

        if(isset($request['searchBuilder']))
        {
            $filter = $genlib->dt_traverse_search_builder($request['searchBuilder']);
        }else{
            $filter = implode(" or ",$where);
        }
        //die(var_dump($filter));

        $order_by = $request['columns'][$request['order'][0]['column']]['data'];
        $order = $request['order'][0]['dir'];
        
        $squery = "SELECT
                    pangkalan.id_pangkalan as DT_RowId,
                    pangkalan.id_pangkalan, 
                    pangkalan.id_agen, 
                    pangkalan.tanggal_pengajuan, 
                    pangkalan.id_tipe_pangkalan, 
                    pangkalan.id_registrasi, 
                    pangkalan.nama_pangkalan, 
                    pangkalan.kuantiti, 
                    pangkalan.no_sku, 
                    pangkalan.sku, 
                    pangkalan.tipe_pembayaran, 
                    pangkalan.foto_luar, 
                    pangkalan.foto_dalam, 
                    pangkalan.nama_pemilik, 
                    pangkalan.no_ktp_pemilik, 
                    pangkalan.ktp, 
                    pangkalan.selfie_ktp,
                    pangkalan.no_hp_pemilik, 
                    pangkalan.email, 
                    pangkalan.provinsi, 
                    pangkalan.kabupaten, 
                    pangkalan.kecamatan, 
                    pangkalan.kelurahan, 
                    pangkalan.kode_pos, 
                    pangkalan.lat, 
                    pangkalan.lng, 
                    pangkalan.`status`, 
                    agen.sold_id, 
                    agen.nama_perusahaan, 
                    tipe_pangkalan.nama_tipe, 
                    geo_provinsi.nama_provinsi, 
                    geo_kabupaten.nama_kabupaten, 
                    geo_kecamatan.nama_kecamatan, 
                    geo_kelurahan.nama_kelurahan, 
                    pangkalan.no_telepon, 
                    pangkalan.alamat
                FROM
                    pangkalan
                    LEFT JOIN
                    agen
                    ON 
                        pangkalan.id_agen = agen.id_agen
                    LEFT JOIN
                    tipe_pangkalan
                    ON 
                        pangkalan.id_tipe_pangkalan = tipe_pangkalan.id_tipe_pangkalan
                    LEFT JOIN
                    geo_provinsi
                    ON 
                        pangkalan.provinsi = geo_provinsi.kode_provinsi
                    LEFT JOIN
                    geo_kabupaten
                    ON 
                        pangkalan.kabupaten = geo_kabupaten.kode_kabupaten
                    LEFT JOIN
                    geo_kecamatan
                    ON 
                        pangkalan.kecamatan = geo_kecamatan.kode_kecamatan
                    LEFT JOIN
                    geo_kelurahan
                    ON 
                        pangkalan.kelurahan = geo_kelurahan.kode_kelurahan where pangkalan.id_agen='".$id_agen."'";

        $squery_total = "SELECT
                    count(pangkalan.id_pangkalan) as jumlah
                    FROM
                    pangkalan where id_agen='".$id_agen."'";

        $squery_total_filtered = "SELECT
                    count(pangkalan.id_pangkalan) as jumlah
                    FROM
                    pangkalan
                    LEFT JOIN
                    agen
                    ON 
                        pangkalan.id_agen = agen.id_agen
                    LEFT JOIN
                    tipe_pangkalan
                    ON 
                        pangkalan.id_tipe_pangkalan = tipe_pangkalan.id_tipe_pangkalan
                    LEFT JOIN
                    geo_provinsi
                    ON 
                        pangkalan.provinsi = geo_provinsi.kode_provinsi
                    LEFT JOIN
                    geo_kabupaten
                    ON 
                        pangkalan.kabupaten = geo_kabupaten.kode_kabupaten
                    LEFT JOIN
                    geo_kecamatan
                    ON 
                        pangkalan.kecamatan = geo_kecamatan.kode_kecamatan
                    LEFT JOIN
                    geo_kelurahan
                    ON 
                        pangkalan.kelurahan = geo_kelurahan.kode_kelurahan where pangkalan.id_agen='".$id_agen."'";

        if (!empty($filter))
        {
            $filter = "(".$filter.")";
        }

        if (!empty($extra)){
            $squery_total .= " and ".$extra;
            if (empty($filter)){
                $filter = $extra;
            }else{
                $filter .= " and (".$extra.")";
            }
        }
        
        if (!empty($order_by))
        {
            $order_by = " order by ".$order_by." ".$order;
        }
        
        if (!empty($filter))
        {
            //$data = $builder->where($filter)->orderBy($order_by, $order)->limit($request['start'],$request['length'])->get()->getResult();
            $squery .= " and ".$filter.$order_by." limit ".$request['start'].",".$request['length'];
            $squery_total_filtered .= " and ".$filter;
        }else{
            //$data = $builder->orderBy($order_by, $order)->limit($request['start'],$request['length'])->get()->getResult();
            $squery .= $order_by." limit ".$request['start'].",".$request['length'];
        }
        //die(var_dump($squery));
        //die(var_dump($registrasi));
        $query_total = $db->query($squery_total);
        $query_total_filtered = $db->query($squery_total_filtered);
        $query = $db->query($squery);

        $row_total = $query_total->getRow();
        $row_total_filtered = $query_total_filtered->getRow();
        $data = $query->getResult();

        $return_data['recordsTotal'] = $row_total->jumlah;
        $return_data['recordsFiltered'] =  $row_total_filtered->jumlah;
        $return_data['data'] = $data;

        return json_encode($return_data);
    }

    public function get_stat_provinsi($id_provinsi)
    {
        $db = db_connect();
        $builder = $db->table('vcountpangkalanprovinsi');
        $builder->where('kode_provinsi',$id_provinsi);
        $data = $builder->get()->getResult();
        return $data;
    }

    public function get_stat_kabupaten($id_kabupaten)
    {
        $db = db_connect();
        $builder = $db->table('vcountpangkalankabupaten');
        $builder->where('kode_kabupaten',$id_kabupaten);
        $data = $builder->get()->getResult();
        return $data;
    }

    public function get_stat_general_kabupaten($id_kabupaten)
    {
        $db = db_connect();
        $builder = $db->table('vkabupaten_stat');
        $builder->where('kode_kabupaten',$id_kabupaten);
        $data = $builder->get()->getResult();
        return $data;
    }

    public function get_stat_kecamatan($id_kecamatan)
    {
        $db = db_connect();
        $builder = $db->table('vcountpangkalankecamatan');
        $builder->where('kode_kecamatan',$id_kecamatan);
        $data = $builder->get()->getResult();
        return $data;
    }

    public function get_stat_kelurahan($id_kelurahan)
    {
        $db = db_connect();
        $builder = $db->table('vcountpangkalankelurahan');
        $builder->where('kode_kelurahan',$id_kelurahan);
        $data = $builder->get()->getResult();
        return $data;
    }

    public function get_count_pangkalan_pengajuan($id = "")
    {
        $db = db_connect();
        $squery = "select count(id_pangkalan) as jumlah from pangkalan where (status<>'approve' and status<>'aktif' and status<>'reject')";
        if (!empty($id))
        {
            $squery .= " and id_agen='".$id."'";
        }

        $hasil = $db->query($squery);
        
        if ($hasil == false){
            return 0;
        }else{
            if (empty($hasil->getRow()->jumlah))
            {
                return 0;
            }else{
                return $hasil->getRow()->jumlah;
            }
        }
    }

    public function get_count_pangkalan_waiting($id = "")
    {
        $db = db_connect();
        $squery = "select count(id_pangkalan) as jumlah from pangkalan where status='waiting'";
        if (!empty($id))
        {
            $squery .= " and id_agen='".$id."'";
        }

        $hasil = $db->query($squery);
        if ($hasil == false){
            return 0;
        }else{
            return $hasil->getRow()->jumlah;
        }
    }

    public function get_count_pangkalan_revision($id = "")
    {
        $db = db_connect();
        $squery = "select count(id_pangkalan) as jumlah from pangkalan where status='revision'";
        if (!empty($id))
        {
            $squery .= " and id_agen='".$id."'";
        }

        $hasil = $db->query($squery);
        if ($hasil == false){
            return 0;
        }else{
            return $hasil->getRow()->jumlah;
        }
    }

    public function get_count_pangkalan_reject($id = "")
    {
        $db = db_connect();
        $squery = "select count(id_pangkalan) as jumlah from pangkalan where status='reject'";
        if (!empty($id))
        {
            $squery .= " and id_agen='".$id."'";
        }

        $hasil = $db->query($squery);
        if ($hasil == false){
            return 0;
        }else{
            return $hasil->getRow()->jumlah;
        }
    }

    public function get_count_pangkalan_approve($id = "")
    {
        $db = db_connect();
        $squery = "select count(id_pangkalan) as jumlah from pangkalan where status='approve' or status='aktif'";
        if (!empty($id))
        {
            $squery .= " and id_agen='".$id."'";
        }

        $hasil = $db->query($squery);
        if ($hasil == false){
            return 0;
        }else{
            return $hasil->getRow()->jumlah;
        }
    }

    public function get_total_alokasi($id="")
    {
        $db = db_connect();
        $squery = "select sum(kuantiti) as jumlah from pangkalan where status='approve' or status='aktif'";
        if (!empty($id))
        {
            $squery .= " and id_agen='".$id."'";
        }

        $hasil = $db->query($squery);
        if ($hasil == false){
            return 0;
        }else{
            return $hasil->getRow()->jumlah;
        }
    }
}
?>