<?php

namespace App\Models;

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

class Transaksimodel extends Model
{
    protected $table         = 'transaksi';
    protected $primaryKey = 'id_transaksi';
    protected $useAutoIncrement = true;
    protected $useSoftDeletes = true;

    protected $allowedFields = [
        'id_grup_transaksi', 'kode', 'tanggal', 'total', 'total_diskon', 'total_akhir', 'id_jenis_pembayaran', 'tanggal_pembayaran', 'pembayar', 'no_telp', 'id_operator', 'id_kolektor', 'flag_batal', 'flag_validasi', 'tanggal_validasi', 'keterangan_validasi', 'created_by', 'updated_by', 'deleted_by'
    ];

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

    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']);
            //die(var_dump($filter));
        }else{
            $filter = implode(" or ",$where);
        }
        //die(var_dump($filter));

        if(isset($request['order'][0]['column'])){
            $order_by = $request['columns'][$request['order'][0]['column']]['data'];
            $order = $request['order'][0]['dir'];
        }else{
            foreach($request['columns'] as $item){
                if($item['orderable']=='true'){
                    $order_by = $item['data'];
                }
            }
            $order = "asc";
        }
        
        $squery = "select * from vtransaksi";

        $squery_total = "SELECT
                    count(vtransaksi.id_transaksi) as jumlah
                    FROM
                    vtransaksi";

        $squery_total_filtered = "SELECT
                    count(vtransaksi.id_transaksi) as jumlah
                    FROM
                    vtransaksi";

        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;
        }
        
        $limit = "";
        if ($request['length'] != "-1"){
            $limit = " limit ".$request['start'].",".$request['length'];
        }
        
        if (!empty($filter))
        {
            $filter .= " and deleted_at is null ";
            //$data = $builder->where($filter)->orderBy($order_by, $order)->limit($request['start'],$request['length'])->get()->getResult();
            $squery .= " where ".$filter.$order_by.$limit;
            $squery_total_filtered .= " where ".$filter;
        }else{
            //$data = $builder->orderBy($order_by, $order)->limit($request['start'],$request['length'])->get()->getResult();
            $squery .= " where deleted_at is null ".$order_by.$limit;
        }
        //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 GetDTWarga($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']);
            //die(var_dump($filter));
        }else{
            $filter = implode(" or ",$where);
        }
        //die(var_dump($filter));

        if(isset($request['order'][0]['column'])){
            $order_by = $request['columns'][$request['order'][0]['column']]['data'];
            $order = $request['order'][0]['dir'];
        }else{
            foreach($request['columns'] as $item){
                if($item['orderable']=='true'){
                    $order_by = $item['data'];
                }
            }
            $order = "asc";
        }
        
        $squery = "select * from vkk_rekap";

        $squery_total = "SELECT
                    count(vkk_rekap.id_kk) as jumlah
                    FROM
                    vkk_rekap";

        $squery_total_filtered = "SELECT
                    count(vkk_rekap.id_kk) as jumlah
                    FROM
                    vkk_rekap";

        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;
        }
        
        $limit = "";
        if ($request['length'] != "-1"){
            $limit = " limit ".$request['start'].",".$request['length'];
        }

        if (!empty($filter))
        {
            $filter .= " and deleted_at is null ";
            //$data = $builder->where($filter)->orderBy($order_by, $order)->limit($request['start'],$request['length'])->get()->getResult();
            $squery .= " where ".$filter.$order_by.$limit;
            $squery_total_filtered .= " where ".$filter;
        }else{
            //$data = $builder->orderBy($order_by, $order)->limit($request['start'],$request['length'])->get()->getResult();
            $squery .= " where deleted_at is null ".$order_by.$limit;
        }
        //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 getNewKode(){
        $db = \Config\Database::connect();
        $builder = $db->table('transaksi_counter');
        $db->transStart();
        $yearMonth = date('Ym');
        $row = $builder
                ->where('tahunbulan', $yearMonth)
                ->getWhere([], 1, 0, true) // true = gunakan SQL_CALC_FOUND_ROWS
                ->getRow();
        if ($row) {
            $newNumber = $row->urutan + 1;

            $builder->where('tahunbulan', $yearMonth)
                    ->update(['urutan' => $newNumber]);
        } else {
            $newNumber = 1;
            $builder->insert([
                'tahunbulan' => $yearMonth,
                'urutan' => $newNumber
            ]);
        }

        $db->transComplete(); // Commit atau Rollback

        if ($db->transStatus() === false) {
            return "";
        }
        $db->transCommit();

        return 'TRANS/'.$yearMonth.'/'.str_pad($newNumber,5,"0",STR_PAD_LEFT);
    }

    public function getTotalTunggakan($id_kk=""){
        $db = \Config\Database::connect();
        $builder = $db->table('invoice');
        $builder->where("flag_close","0");
        $hasil = $builder->selectSum("nominal_akhir")->get()->getRow();
        if (!$hasil){
            return 0;
        }else{
            return $hasil->nominal_akhir;
        }
    }

    public function getTotalPenerimaan($start_date="", $end_date="", $id_grup=""){
        $db = \Config\Database::connect();
        $builder = $db->table('transaksi');
        $builder->where("flag_validasi","2");
        if(!empty($start_date) && !empty($end_date)){
            $builder->where("tanggal between '".$start_date."' and '".$end_date."'");
        }elseif(!empty($start_date)){
            $builder->where("tanggal",$start_date);
        }

        if(!empty($id_grup)){
            $builder->where("id_grup_transaksi",$start_date);
        }

        $hasil = $builder->selectSum("total_akhir")->get()->getRow();
        if (!$hasil){
            return 0;
        }else{
            return $hasil->total_akhir;
        }
    }

    public function getTotalTransaksiCompleted(){
        $db = \Config\Database::connect();
        $builder = $db->table('transaksi');
        $builder->where("flag_validasi","2");
        $hasil = $builder->selectCount("id_transaksi","transaksi_completed")->get()->getRow();
        if (!$hasil){
            return 0;
        }else{
            return $hasil->transaksi_completed;
        }
    }

    public function getTotalTransaksiDraft(){
        $db = \Config\Database::connect();
        $builder = $db->table('transaksi');
        $builder->where("flag_validasi","0");
        $hasil = $builder->selectCount("id_transaksi","transaksi_draft")->get()->getRow();
        if (!$hasil){
            return 0;
        }else{
            return $hasil->transaksi_draft;
        }
    }

    public function getTotalTransaksiPending(){
        $db = \Config\Database::connect();
        $builder = $db->table('transaksi');
        $builder->where("flag_validasi","1");
        $hasil = $builder->selectCount("id_transaksi","transaksi_pending")->get()->getRow();
        if (!$hasil){
            return 0;
        }else{
            return $hasil->transaksi_pending;
        }
    }

    public function getTotalTransaksiCanceled(){
        $db = \Config\Database::connect();
        $builder = $db->table('transaksi');
        $builder->where("flag_validasi","3");
        $hasil = $builder->selectCount("id_transaksi","transaksi_canceled")->get()->getRow();
        if (!$hasil){
            return 0;
        }else{
            return $hasil->transaksi_canceled;
        }
    }
}