<?php
if (!defined('BASEPATH')) exit('No direct script access allowed');

class Jenis_perorangan_m extends CI_Model
{
    function get_datatables($tahun="0000", $bulan="00", $id_group_m="all", $nama_jenis_perusahaan="all"){
        $this->_get_datatables_query($tahun, $bulan, $id_group_m, $nama_jenis_perusahaan);
        if ($_GET['length'] != -1) {
            $this->db->limit($_GET['length'], $_GET['start']);
        }
        $query = $this->db->get();
        return $query->result();
        }
    
    function count_filtered($tahun="0000", $bulan="00", $id_group_m="all", $nama_jenis_perusahaan="all"){
        $this->_get_datatables_query($tahun, $bulan, $id_group_m, $nama_jenis_perusahaan);
        $query = $this->db->get();
        return $query->num_rows();
    }
    
    public function count_all($tahun="0000", $bulan="00", $id_group_m="all", $nama_jenis_perusahaan="all"){
        $this->_get_datatables_query($tahun, $bulan, $id_group_m, $nama_jenis_perusahaan);
        return $this->db->count_all_results();
    }
    
    private function _get_datatables_query($tahun="0000", $bulan="00", $id_group_m="all", $nama_jenis_perusahaan="all"){
        $this->db->select('
            sptx.nama_jenis_perusahaan,
            COUNT(sptx.jumlah_layanan) AS jumlah,
            SUM(sptx.nominal_layanan) AS nominal
        ');
        $this->db->from('sales_pipeline_trx AS sptx');
        $this->db->join('sales_pelanggan AS pelanggan', 'pelanggan.id_pelanggan = sptx.id_pelanggan');
        $this->db->where('sptx.id_company', $this->id_company);
        $this->db->where('sptx.tahun', $tahun);
        $this->db->where('sptx.jenis_tahapan', "wont");
        $this->db->where('pelanggan.kategori_pelanggan', "perorangan");
        $this->db->group_by('sptx.nama_jenis_perusahaan');

        if($bulan != "all"){
            $this->db->where('sptx.bulan', sprintf('%02d', $bulan));
        }

        if($id_group_m != "all"){
            $this->db->where('sptx.id_group_m', $id_group_m);
        }

        if($nama_jenis_perusahaan != "all"){
            $this->db->where('sptx.nama_jenis_perusahaan', $nama_jenis_perusahaan);
        }

        if($this->is_sales_admin == true){
            if (!in_array($this->sales_admin_group, ["all", ""])) {
                $this->db->where_in('sptx.id_group_m', explode(',', $this->sales_admin_group));
            }
        }
        
        // COLUMN ORDER
        $column_order = array('', 'sptx.nama_jenis_perusahaan', 'COUNT(sptx.jumlah_layanan)', 'SUM(sptx.nominal_layanan)');
        if (isset($_GET['order'])) {
            $this->db->order_by($column_order[$_GET['order']['0']['column']], $_GET['order']['0']['dir']);
        } else {
            $this->db->order_by("COUNT(sptx.jumlah_layanan) DESC, sptx.nama_jenis_perusahaan ASC");
        }
    }

    public function get_total_jumlah_dan_nominal($tahun="0000", $bulan="00", $id_group_m="all", $nama_jenis_perusahaan="all"){
        $this->db->select('
            COUNT(sptx.jumlah_layanan) AS jumlah,
            SUM(sptx.nominal_layanan) AS nominal
        ');
        $this->db->from('sales_pipeline_trx AS sptx');
        $this->db->join('sales_pelanggan AS pelanggan', 'pelanggan.id_pelanggan = sptx.id_pelanggan');
        $this->db->where('sptx.id_company', $this->id_company);
        $this->db->where('sptx.tahun', $tahun);
        $this->db->where('sptx.jenis_tahapan', "wont");
        $this->db->where('pelanggan.kategori_pelanggan', "perorangan");
        $this->db->group_by('sptx.id_company');

        if($bulan != "all"){
            $this->db->where('sptx.bulan', sprintf('%02d', $bulan));
        }

        if($id_group_m != "all"){
            $this->db->where('sptx.id_group_m', $id_group_m);
        }

        if($nama_jenis_perusahaan != "all"){
            $this->db->where('sptx.nama_jenis_perusahaan', $nama_jenis_perusahaan);
        }

        if($this->is_sales_admin == true){
            if (!in_array($this->sales_admin_group, ["all", ""])) {
                $this->db->where_in('sptx.id_group_m', explode(',', $this->sales_admin_group));
            }
        }

        return $this->db->get()->row();
    }
    
    public function dropdown_jenis_perorangan($search = "")
    {
        $this->db->select('nama');
        $this->db->where('id_company', $this->id_company);
        $this->db->where('flag_jenis', "2");
        $this->db->order_by('nama', 'ASC');

        if ($search != '') {
            $this->db->like('nama', $search);
        }

        $result = $this->db->get('sales_jenis_perusahaan_m')->result();
        $json = [];
        foreach ($result as $rows) {
            $json[] = ['id' => $rows->nama, 'text' => $rows->nama];
        }
        echo json_encode($json);
    }
}