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

class Source_m extends CI_Model
{
    var $table = 'sales_pipeline_trx';

    function get_datatables($tahun="0000", $bulan="00", $id_group_m="all", $source="all"){
        $this->_get_datatables_query($tahun, $bulan, $id_group_m, $source);
        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", $source="all"){
        $this->_get_datatables_query($tahun, $bulan, $id_group_m, $source);
        $query = $this->db->get();
        return $query->num_rows();
    }
    
    public function count_all($tahun="0000", $bulan="00", $id_group_m="all", $source="all"){
        $this->_get_datatables_query($tahun, $bulan, $id_group_m, $source);
        return $this->db->count_all_results();
    }
    
    private function _get_datatables_query($tahun="0000", $bulan="00", $id_group_m="all", $source="all"){
        $this->db->select('
            sptx.nama_lead_source,
            COUNT(sptx.jumlah_layanan) AS jumlah,
            SUM(sptx.nominal_layanan) AS nominal
        ');
        $this->db->from('sales_pipeline_trx AS sptx');
        $this->db->where('sptx.id_company', $this->id_company);
        $this->db->where('sptx.tahun', $tahun);
        $this->db->where('sptx.jenis_tahapan', "wont");
        $this->db->group_by('sptx.nama_lead_source');
        
        if($bulan != "all"){
            $this->db->where('sptx.bulan', sprintf('%02d', $bulan));
        }

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

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

        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_lead_source', 'COUNT(sptx.jumlah_layanan)', 'SUM(spl.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_lead_source ASC");
        }
    }

    public function get_total_jumlah_dan_nominal($tahun="0000", $bulan="00", $id_group_m="all", $source="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->where('sptx.id_company', $this->id_company);
        $this->db->where('sptx.tahun', $tahun);
        $this->db->where('sptx.jenis_tahapan', "wont");
        $this->db->group_by('sptx.id_company');

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

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

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

        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_source($search = "")
    {
        $this->db->select('id_lead_m, nama');
        $this->db->where('id_company', $this->id_company);
        $this->db->order_by('no_urut', 'ASC');

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

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