<?php

namespace App\Models\Potensiwilayah;

use App\Models\BaseModel;

class DatanasabahModel extends BaseModel
{
    protected $table            = 'nasabah_calon';
    protected $primaryKey       = 'id';
    protected $allowedFields    = ['nama_calon', 'tgl_lahir', 'alamat', 'no_wa', 'nik', 'emailx', 'maiden', 'pekerjaan', 'ket', 'foto_ktp', 'foto_closing'];

    public function find_by_id($id)
    {
        $db = \Config\Database::connect();
        $builder = $db->table('nasabah_calon as n');
        $builder->select('n.*, p.nama_giat, pr.nama_kegiatan, l.nama_lokasi, lk.nama_kat, p.tgl_1, p.tgl_2, pd.uraian');
        $builder->join('promosi_main as p', 'n.id_kegiatan = p.id', 'left');
        $builder->join('pdk_promo as pr', 'p.id_jenis = pr.id', 'left');
        $builder->join('lokasi_main as l', 'p.id_lokasi = l.id', 'left');
        $builder->join('lokasi_kate as lk', 'p.id_katelok = lk.id', 'left');
        $builder->join('pdk_laym as pd', 'n.id_produk = pd.id', 'left');
        $builder->where('n.id', $id);
        $builder->where('n.closing', 1);

        return $builder->get()->getRow();
    }

    public function kantor_user($id_user)
    {
        $db = \Config\Database::connect();
        $builder = $db->table('users');
        $builder->select('id_kantor');
        $builder->where('id', $id_user);
        $res = $builder->get()->getRow();
        return $res->id_kantor;
    }

    function get_rows2($limit, $start = 0, $filterRules = NULL, $sortOrder = NULL, $id_kantor) {
        $db = \Config\Database::connect();
        $builder = $db->table('nasabah_calon as n');
        $builder->select('n.id, n.nama_calon, n.nik, n.tgl_lahir, n.tgl_kegiatan, n.no_wa, n.closing, n.no_cif, n.tgl_closing, n.emailx, p.nama_giat, l.nama_lokasi, u.username, n.wkt_rek, u2.username as username_closing, n.wkt_clo');
        $builder->join('promosi_main as p', 'n.id_kegiatan = p.id', 'left');
        $builder->join('lokasi_main as l', 'p.id_lokasi = l.id', 'left');
        $builder->join('users as u', 'n.id_rek = u.id', 'left');
        $builder->join('users as u2', 'n.id_clo = u2.id', 'left');
        $builder->where('p.id_kantor', $id_kantor);
        $builder->where('n.closing', 1);
        
        if ($filterRules) {
            $filterRules = json_decode($filterRules);

            foreach ($filterRules as $filter) {
                $value = $filter->value;
                $pecah = explode(';', $value);
                if (count($pecah) > 1) {
                    $builder->groupStart();
                    foreach ($pecah as $p) {
                        if (trim($p) <> '') {
                            $field = $filter->field;
                            if (trim($filter->field) == 'nama_calon') {
                                $field = 'n.nama_calon';
                            }
                            if (trim($filter->field) == 'nik') {
                                $field = 'n.nik';
                            }
                            if (trim($filter->field) == 'tgl_lahir') {
                                $field = 'n.tgl_lahir';
                            }
                            if (trim($filter->field) == 'tgl_kegiatan') {
                                $field = 'n.tgl_kegiatan';
                            }
                            if (trim($filter->field) == 'no_wa') {
                                $field = 'n.no_wa';
                            }
                            if (trim($filter->field) == 'no_cif') {
                                $field = 'n.no_cif';
                            }
                            if (trim($filter->field) == 'tgl_closing') {
                                $field = 'n.tgl_closing';
                            }
                            if (trim($filter->field) == 'emailx') {
                                $field = 'n.emailx';
                            }
                            if (trim($filter->field) == 'nama_giat') {
                                $field = 'p.nama_giat';
                            }
                            if (trim($filter->field) == 'nama_lokasi') {
                                $field = 'l.nama_lokasi';
                            }
                            if (trim($filter->field) == 'username') {
                                $field = 'u.username';
                            }
                            if (trim($filter->field) == 'username_closing') {
                                $field = 'u2.username';
                            }
                            if (trim($filter->field) == 'wkt_rek') {
                                $field = 'n.wkt_rek';
                            }
                            if (trim($filter->field) == 'wkt_clo') {
                                $field = 'n.wkt_clo';
                            }
                            
                            $builder->orLike($field, trim(urldecode($p)));
                        }
                    }
                    $builder->groupEnd();
                } else {
                    $field = $filter->field;
                    if (trim($filter->field) == 'nama_calon') {
                        $field = 'n.nama_calon';
                    }
                    if (trim($filter->field) == 'nik') {
                        $field = 'n.nik';
                    }
                    if (trim($filter->field) == 'tgl_lahir') {
                        $field = 'n.tgl_lahir';
                    }
                    if (trim($filter->field) == 'no_wa') {
                        $field = 'n.no_wa';
                    }
                    if (trim($filter->field) == 'no_cif') {
                        $field = 'n.no_cif';
                    }
                    if (trim($filter->field) == 'tgl_closing') {
                        $field = 'n.tgl_closing';
                    }
                    if (trim($filter->field) == 'emailx') {
                        $field = 'n.emailx';
                    }
                    if (trim($filter->field) == 'nama_giat') {
                        $field = 'p.nama_giat';
                    }
                    if (trim($filter->field) == 'nama_lokasi') {
                        $field = 'l.nama_lokasi';
                    }
                    if (trim($filter->field) == 'username') {
                        $field = 'u.username';
                    }
                    if (trim($filter->field) == 'username_closing') {
                        $field = 'u2.username';
                    }
                    if (trim($filter->field) == 'wkt_rek') {
                        $field = 'n.wkt_rek';
                    }
                    if (trim($filter->field) == 'wkt_clo') {
                        $field = 'n.wkt_clo';
                    }
                    
                    $builder->like($field, trim(urldecode($value)));
                }
            }
        }

        if ($sortOrder) {
            $builder->orderBy($sortOrder);
        } else {
            if ($this->orderBy <> '') {
                $builder->orderBy($this->orderBy);
            } else {
                $builder->orderBy($this->primaryKey, $this->sort);
            }
        }

        $builder->limit($limit, $start);
                
        return $builder->get()->getResult();
    }

    public function get_total2($filterRules = null, $id_kantor)
    {
        $db = \Config\Database::connect();
        $builder = $db->table('nasabah_calon as n');
        $builder->select('n.id, n.nama_calon, n.nik, n.tgl_lahir, n.tgl_kegiatan, n.no_wa, n.closing, n.no_cif, n.tgl_closing, n.emailx, p.nama_giat, l.nama_lokasi, u.username, n.wkt_rek, u2.username as username_closing, n.wkt_clo');
        $builder->join('promosi_main as p', 'n.id_kegiatan = p.id', 'left');
        $builder->join('lokasi_main as l', 'p.id_lokasi = l.id', 'left');
        $builder->join('users as u', 'n.id_rek = u.id', 'left');
        $builder->join('users as u2', 'n.id_clo = u2.id', 'left');
        $builder->where('p.id_kantor', $id_kantor);
        $builder->where('n.closing', 1);

        if ($filterRules) {
            $filterRules = json_decode($filterRules);
            
            foreach ($filterRules as $filter) {
                $value = $filter->value;
                $pecah = explode(';', $value);
                if (count($pecah) > 1) {
                    $builder->groupStart();
                    foreach ($pecah as $p) {
                        if (trim($p) <> '') {
                            $field = $filter->field;
                            if (trim($filter->field) == 'nama_calon') {
                                $field = 'n.nama_calon';
                            }
                            if (trim($filter->field) == 'nik') {
                                $field = 'n.nik';
                            }
                            if (trim($filter->field) == 'tgl_lahir') {
                                $field = 'n.tgl_lahir';
                            }
                            if (trim($filter->field) == 'tgl_kegiatan') {
                                $field = 'n.tgl_kegiatan';
                            }
                            if (trim($filter->field) == 'no_wa') {
                                $field = 'n.no_wa';
                            }
                            if (trim($filter->field) == 'no_cif') {
                                $field = 'n.no_cif';
                            }
                            if (trim($filter->field) == 'tgl_closing') {
                                $field = 'n.tgl_closing';
                            }
                            if (trim($filter->field) == 'emailx') {
                                $field = 'n.emailx';
                            }
                            if (trim($filter->field) == 'nama_giat') {
                                $field = 'p.nama_giat';
                            }
                            if (trim($filter->field) == 'nama_lokasi') {
                                $field = 'l.nama_lokasi';
                            }
                            if (trim($filter->field) == 'username') {
                                $field = 'u.username';
                            }
                            if (trim($filter->field) == 'username_closing') {
                                $field = 'u2.username';
                            }
                            if (trim($filter->field) == 'wkt_rek') {
                                $field = 'n.wkt_rek';
                            }
                            if (trim($filter->field) == 'wkt_clo') {
                                $field = 'n.wkt_clo';
                            }
                            
                            $builder->orLike($field, trim(urldecode($p)));
                        }
                    }
                    $builder->groupEnd();
                } else {
                    $field = $filter->field;
                    if (trim($filter->field) == 'nama_calon') {
                        $field = 'n.nama_calon';
                    }
                    if (trim($filter->field) == 'nik') {
                        $field = 'n.nik';
                    }
                    if (trim($filter->field) == 'tgl_lahir') {
                        $field = 'n.tgl_lahir';
                    }
                    if (trim($filter->field) == 'no_wa') {
                        $field = 'n.no_wa';
                    }
                    if (trim($filter->field) == 'no_cif') {
                        $field = 'n.no_cif';
                    }
                    if (trim($filter->field) == 'tgl_closing') {
                        $field = 'n.tgl_closing';
                    }
                    if (trim($filter->field) == 'emailx') {
                        $field = 'n.emailx';
                    }
                    if (trim($filter->field) == 'nama_giat') {
                        $field = 'p.nama_giat';
                    }
                    if (trim($filter->field) == 'nama_lokasi') {
                        $field = 'l.nama_lokasi';
                    }
                    if (trim($filter->field) == 'username') {
                        $field = 'u.username';
                    }
                    if (trim($filter->field) == 'username_closing') {
                        $field = 'u2.username';
                    }
                    if (trim($filter->field) == 'wkt_rek') {
                        $field = 'n.wkt_rek';
                    }
                    if (trim($filter->field) == 'wkt_clo') {
                        $field = 'n.wkt_clo';
                    }
                    
                    $builder->like($field, trim(urldecode($value)));
                }
            }
        }

        return $builder->countAllResults();
    }

    function get_user_kantor($id_user)
    {
        $db = \Config\Database::connect();
        $builder = $db->table('users');
        $builder->select('id_kantor');
        $builder->where('id', $id_user);
        $result = $builder->get()->getRow();
        return $result ? $result->id_kantor : null;
    }

    function get_bday_now()
    {
        $db = \Config\Database::connect();
        $builder = $db->table('nasabah_calon as n');
        $builder->select('n.id, n.nama_calon, n.no_wa, s.satker');
        $builder->join('promosi_main as p', 'n.id_kegiatan = p.id', 'left');
        $builder->join('pdk_satker as s', 'p.id_kantor = s.id', 'left');
        $builder->where('n.closing', 1);
        $builder->where('DAY(n.tgl_lahir)', date('d'));
        $builder->where('MONTH(n.tgl_lahir)', date('m'));
        $builder->where('n.no_wa !=', '');
        
        return $builder->get()->getResult();
    }
}
