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

class Aktivitas_m extends CI_Model
{
    var $table = 'data_karyawan';

    public function table_header($periode_mulai="00-00-0000", $periode_selesai="00-00-000")
    {
        $binding[] = date('Y-m-d', strtotime($periode_mulai));
        $binding[] = date('Y-m-d', strtotime($periode_selesai));

        $sql = "SELECT * FROM (
                    SELECT
                        nama AS nama_aktivitas,
                        no_urut
                    FROM sales_activity_m
                    WHERE id_company = '$this->id_company'
                    UNION ALL
                    SELECT
                        jenis_activity AS nama_aktivitas,
                        '999' AS no_urut
                    FROM sales_pipeline_activity
                    WHERE id_company = '$this->id_company'
                    AND (tgl_activity >= ? AND tgl_activity <= ?)
                ) AS combined_data
                GROUP BY nama_aktivitas
                ORDER BY CAST(no_urut AS UNSIGNED) ASC;";

        $query = $this->db->query($sql, $binding)->result();
        return $query;
    }

    function get_datatables($periode_mulai="00-00-0000", $periode_selesai="00-00-0000", $id_group="all", $id_karyawan="all")
    {
        $this->_get_datatables_query($periode_mulai, $periode_selesai, $id_group, $id_karyawan);
        if ($_GET['length'] != -1) {
            $this->db->limit($_GET['length'], $_GET['start']);
        }
        $query = $this->db->get();
        return $query->result();
    }

    function count_filtered($periode_mulai="00-00-0000", $periode_selesai="00-00-0000", $id_group="all", $id_karyawan="all")
    {
        $this->_get_datatables_query($periode_mulai, $periode_selesai, $id_group, $id_karyawan);
        $query = $this->db->get();
        return $query->num_rows();
    }

    public function count_all($periode_mulai="00-00-0000", $periode_selesai="00-00-0000", $id_group="all", $id_karyawan="all")
    {
        $this->_get_datatables_query($periode_mulai, $periode_selesai, $id_group, $id_karyawan);
        return $this->db->count_all_results();
    }

    private function _get_datatables_query($periode_mulai="00-00-0000", $periode_selesai="00-00-0000", $id_group="all", $id_karyawan="all")
    {
        $periode_mulai   = date_format(date_create($periode_mulai),"Y-m-d");
        $periode_selesai = date_format(date_create($periode_selesai),"Y-m-d");

        $this->db->select("id_karyawan,
                            nik,
                            nama_lengkap");
        $this->db->from("data_karyawan");
        $this->db->where("id_company", $this->id_company);

        if($id_karyawan != "all"){
            $this->db->where('id_karyawan', $id_karyawan);
        }else{
            $check_sa = $this->fungsi->check_atasan_get_bawahan_sa();
            if($check_sa['is_atasan'] == true){
                $this->db->where_in('id_karyawan', $check_sa['list_bawahan']);
            }else{
                $id_karyawan_arr = $this->_get_id_karyawan_union($periode_mulai, $periode_selesai, $id_group);
                $id_karyawan_arr = !empty($id_karyawan_arr) ? $id_karyawan_arr : [""];

                $this->db->where_in('id_karyawan', $id_karyawan_arr);
            }
        }

        $this->db->group_by("id_karyawan");

        $column_search = array('nik', 'nama_karyawan');
        $i = 0;
        foreach ($column_search as $item) {  // looping awal
            if ($_GET['search']['value']) { // jika datatable mengirimkan pencarian dengan metode POST

                if ($i === 0) { // looping awal
                    $this->db->group_start();
                    $this->db->like($item, $_GET['search']['value']);
                } else {
                    $this->db->or_like($item, $_GET['search']['value']);
                }

                if (count($column_search) - 1 == $i) {
                    $this->db->group_end();
                }
            }
            $i++;
        }

        // $column_order = array('', '');
        if (isset($_GET['order'])) {
            // $this->db->order_by($column_order[$_GET['order']['0']['column']], $_GET['order']['0']['dir']);
        } else {
            $order_by = array("nama_lengkap" => "ASC");
            $this->db->order_by(key($order_by), $order_by[key($order_by)]);
        }

    }

    public function get_total_activity($mulai = "", $selesai = "", $id_karyawan = "")
    {
        $id_karyawan_arr = $this->_get_id_karyawan_union($mulai, $selesai);
        $id_karyawan_arr = !empty($id_karyawan_arr) ? $id_karyawan_arr : [""];

        $tanggal_mulai   = date('Y-m-d', strtotime($mulai));
        $tanggal_selesai = date('Y-m-d', strtotime($selesai));

        $this->db->select("dk.id_karyawan,
                            dk.nik,
                            dk.nama_lengkap,
                            spe.jenis_activity AS nama_aktivitas,
                            COUNT(spe.jenis_activity) AS total_aktivitas")
            ->from('data_karyawan AS dk')
            ->join(
                'sales_pipeline_activity AS spe',
                'spe.id_karyawan = dk.id_karyawan
                    AND (spe.tgl_activity >= "' . $tanggal_mulai . '" AND spe.tgl_activity <= "' . $tanggal_selesai . '")'
            )
            ->where("dk.id_company", $this->id_company)
            ->where_in('dk.id_karyawan', $id_karyawan_arr)
            ->group_by("dk.id_karyawan, spe.jenis_activity");

        // if ($id_karyawan != "all") {
        //     $this->db->where('dk.id_karyawan', $id_karyawan);
        // }

        $query = $this->db->get()->result();
        $data = array();
        foreach ($query as $value) {
            $data[$value->id_karyawan][$value->nama_aktivitas]['total'] = $value->total_aktivitas;
            $data['total_aktivitas_all'][$value->nama_aktivitas] += (int)$value->total_aktivitas;
        }

        return $data;
    }

    public function dropdown_group($periode_mulai="0000-00-00", $periode_selesai="0000-00-00", $search=""){
        $where_group = "";
        if($this->is_sales_admin == TRUE && !in_array($this->sales_admin_group, ["all", ""])){
            $where_group = " AND g.id_group_m IN ('".str_replace(",","','",$this->sales_admin_group)."')";
        }

        $binding[] = $periode_mulai;
        $binding[] = $periode_selesai;

        $periode_bulan_mulai   = date_format(date_create($periode_mulai),"Ym");
        $periode_bulan_selesai = date_format(date_create($periode_selesai),"Ym");

        if($periode_bulan_mulai < date('Ym') && $periode_bulan_selesai < date('Ym')){

            if(!empty($search)){
                $binding[] = "%".$search."%";
                $where_search = " AND g.nama_group LIKE ? ";
            }else{
                $where_search = " ";
            }

            $sql = "SELECT g.id_group_m,
                            g.nama_group
                    FROM sales_pipeline_activity AS g
                    WHERE g.id_company = '" . $this->id_company . "'
                    AND (g.tgl_activity >= ? AND g.tgl_activity <= ?)
                    ".$where_group."
                    ".$where_search."
                    GROUP BY g.nama_group
                    ORDER BY g.nama_group ASC";
        }else{

            if(!empty($search)){
                $binding[] = "%".$search."%";
                $where_search = " WHERE combined.nama_group LIKE ? ";
            }else{
                $where_search = " ";
            }

            $sql = "SELECT * FROM (
                        SELECT g.id_group_m,
                                CONCAT(g.kode, ' - ',g.nama) AS nama_group
                        FROM sales_group_m AS g
                        WHERE g.id_company = '" . $this->id_company . "'
                        ".$where_group."

                        UNION ALL

                        SELECT g.id_group_m,
                                g.nama_group
                        FROM sales_pipeline_activity AS g
                        WHERE g.id_company = '" . $this->id_company . "'
                        AND (g.tgl_activity >= ? AND g.tgl_activity <= ?)
                        ".$where_group."
                        GROUP BY g.nama_group
                    ) AS combined
                    ".$where_search."
                    GROUP BY combined.nama_group
                    ORDER BY combined.nama_group ASC
                ";
        }

        $result = $this->db->query($sql, $binding)->result();
        $json = [];
        foreach ($result as $rows) {
            $json[] = ['id' => $rows->id_group_m, 'text' => $rows->nama_group, 'nm-group'=>$rows->nama_group];
        }
        echo json_encode($json);
    }

    public function dropdown_karyawan($periode_mulai="0000-00-00", $periode_selesai="0000-00-00", $id_group="all", $search=""){
        $check_sa = $this->fungsi->check_atasan_get_bawahan_sa();
        if($check_sa['is_atasan'] == true){
            $this->db->where_in('data_karyawan.id_karyawan', $check_sa['list_bawahan']);
        }

        if($id_group == 'all'){
            if($this->is_sales_admin == TRUE && !in_array($this->sales_admin_group, ["all", ""])){
                $where_group = " AND sgm.id_group_m IN ('".str_replace(",","','",$this->sales_admin_group)."')";
            }else{
                $where_group = " ";
            }
        }else{
            $where_group = " AND sgm.id_group_m = ? ";
            $binding[]   = $id_group;
            $binding[]   = $id_group;

        }

        $binding[] = $periode_mulai;
        $binding[] = $periode_selesai;

        if(!empty($search)){
            $where_search = " WHERE (combined.nik LIKE ? OR combined.nm_karyawan LIKE ?) ";
            $binding[] = "%".$search."%";
            $binding[] = "%".$search."%";
        }

        $sql = "SELECT combined.id_karyawan,
                            combined.nik,
                            combined.nm_karyawan
                    FROM (
                            SELECT dk.id_karyawan,
                                    dk.nik,
                                    dk.nama_lengkap as nm_karyawan
                            FROM sales_approver as sa
                            JOIN data_karyawan as dk on dk.id_karyawan = sa.id_karyawan
                            JOIN sales_group_m as sgm on sgm.id_group_m = sa.id_group_m
                            WHERE sa.id_company = '".$this->id_company."'
                            ".$where_group."
                            AND sa.status = '1'

                            UNION ALL

                            SELECT sgm.id_karyawan,
                                    sgm.nik,
                                    sgm.nama_karyawan as nm_karyawan
                            FROM sales_pipeline_activity as sgm
                            WHERE sgm.id_company = '".$this->id_company."'
                            ".$where_group."
                            AND (sgm.tgl_activity >= ? AND sgm.tgl_activity <= ?)
                            GROUP BY sgm.id_karyawan
                        ) AS combined
                    ".$where_search."
                    GROUP BY combined.id_karyawan
                    ORDER BY combined.nm_karyawan ASC";

        $result = $this->db->query($sql, $binding)->result();
        $json = [];
        foreach ($result as $rows) {
            $json[] = ['id' => $rows->id_karyawan,
                        'text'        => $rows->nik." - ".$rows->nm_karyawan,
                        'nm-karyawan' => $rows->nik." - ".$rows->nm_karyawan];
        }
        echo json_encode($json);
    }

    private function _get_id_karyawan_union($periode_mulai="00-00-0000", $periode_selesai="00-00-0000", $id_group="all"){
        $periode_bulan_mulai   = date_format(date_create($periode_mulai),"Ym");
        $periode_bulan_selesai = date_format(date_create($periode_selesai),"Ym");

        if($id_group == 'all'){
            if($this->is_sales_admin == TRUE && !in_array($this->sales_admin_group, ["all", ""])){
                $where_group = " AND sales.id_group_m IN ('".str_replace(",","','",$this->sales_admin_group)."') ";
            }else{
                $where_group = " ";
            }
        }else{
            $where_group = " AND sales.id_group_m = ? ";

            if($periode_bulan_mulai < date('Ym') && $periode_bulan_selesai < date('Ym')){
                $binding[] = $id_group;
            }else{
                $binding[] = $id_group;
                $binding[] = $id_group;
            }
        }

        $binding[] = $periode_mulai;
        $binding[] = $periode_selesai;

        if($periode_bulan_mulai < date('Ym') && $periode_bulan_selesai < date('Ym')){
            $select = "SELECT id_karyawan
                        FROM sales_pipeline_activity AS sales
                        WHERE id_company = '".$this->id_company."'
                        ".$where_group."
                        AND (sales.tgl_approve >= ? AND sales.tgl_approve <= ?)
                        GROUP BY id_karyawan
                    ";

        }else{
            $select = "SELECT id_karyawan
                                FROM (
                                        SELECT id_karyawan
                                        FROM sales_approver AS sales
                                        WHERE id_company = '".$this->id_company."'
                                        ".$where_group."
                                        AND status = '1'

                                        UNION ALL

                                        SELECT id_karyawan
                                        FROM sales_pipeline_activity AS sales
                                        WHERE id_company = '".$this->id_company."'
                                        ".$where_group."
                                        AND (sales.tgl_approve >= ? AND sales.tgl_approve <= ?)
                                        GROUP BY id_karyawan
                                    ) AS combined
                            GROUP BY combined.id_karyawan
                        ";
        }

        $query = $this->db->query($select, $binding)->result();

        $data = array();
        foreach ($query as $value) {
            $data[] = $value->id_karyawan;
        }

        return $data;
    }
}
