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

class Daily_activity_m extends CI_Model
{
    var $table = 'sales_pipeline_main_log';

    function get_datatables($mulai, $selesai, $status, $pelanggan, $karyawan, $group)
    {
        $this->_get_datatables_query($mulai, $selesai, $status, $pelanggan, $karyawan, $group);
        if ($_GET['length'] != -1) {
            $this->db->limit($_GET['length'], $_GET['start']);
        }
        $query = $this->db->get();
        return $query->result();
    }

    function count_filtered($mulai, $selesai, $status, $pelanggan, $karyawan, $group)
    {
        $this->_get_datatables_query($mulai, $selesai, $status, $pelanggan, $karyawan, $group);
        $query = $this->db->get();
        return $query->num_rows();
    }

    public function count_all($mulai, $selesai, $status, $pelanggan, $karyawan, $group)
    {
        $this->_get_datatables_query($mulai, $selesai, $status, $pelanggan, $karyawan, $group);
        return $this->db->count_all_results();
    }
    private function _get_datatables_query($mulai = '', $selesai = '', $status = 'all', $pelanggan = 'all', $karyawan = 'all', $group = 'all')
    {
        if ($karyawan !== 'all') {
            $this->db->where('log.id_company', $this->id_company);
            $this->db->where('main.id_karyawan', $karyawan);
        } else {
            if ($this->is_sales_admin === false && $this->level_user !== '1') {
                if ($this->is_sales_atasan === true) {
                    $get_bawahan = $this->get_data_bawahan($this->id_karyawan);
                    $list = array_column($get_bawahan, 'id_karyawan');
                    if ($this->is_sales === true) {
                        $list[] = $this->id_karyawan;
                    }
                    $this->db->where('log.id_company', $this->id_company);
                    $this->db->where_in('main.id_karyawan', $list);
                } else {
                    $this->db->where('log.id_company', $this->id_company);
                    $this->db->where('main.id_karyawan', $karyawan);
                }
            } else {
                $this->db->where('log.id_company', $this->id_company);
            }
        }
        if ($pelanggan !== 'all') {
            $this->db->where('main.id_pelanggan', $pelanggan);
        }
        if ($group !== 'all') {
            $this->db->group_start()
                ->where('main.id_group_m', $group)
                ->or_where('main.id_group_m', '')
                ->group_end();
        } else {
            if (!in_array($this->sales_admin_group, ["all", ""])) {
                $group_ids = explode(',', $this->sales_admin_group);
                $this->db->group_start()
                    ->where_in('main.id_group_m', $group_ids)
                    ->or_where('main.id_group_m', '')
                    ->group_end();
            }
        }
        if ($status !== 'all') {
            $this->db->where_in('log.status', explode('_', $status));
        }
        $log = $this->db->select('
                "log" as tabel,
                main.id_pelanggan,
                log.status,
                log.tahapan_sebelum_nm as tahapan_sebelum,
                log.tahapan_sesudah_nm as tahapan_sekarang,
                log.kategori,
                log.keterangan as aktivitas_pindah_bulan,
                IF(log.tahapan_sebelum_id = "", "New", "Move") as aktivitas,
                "" as poin,
                main.keterangan,
                log.tgl_input as waktu,
                log.user_input as user,
                main.id_karyawan as user_id,
                main.nama_lead_source,
                dk.nama_lengkap,
                dk.nik
            ')
            ->from('sales_pipeline_main_log log')
            ->join('sales_pipeline_main main', 'main.id_pipeline_main = log.id_pipeline_main')
            ->join('data_karyawan dk', 'dk.id_karyawan = main.id_karyawan')
            ->get_compiled_select();

        if ($karyawan !== 'all') {
            $this->db->where('activity.id_company', $this->id_company);
            $this->db->where('activity.id_karyawan', $karyawan);
        } else {
            if ($this->is_sales_admin === false && $this->level_user !== '1') {
                if ($this->is_sales_atasan === true) {
                    $get_bawahan = $this->get_data_bawahan($this->id_karyawan);
                    $list = array_column($get_bawahan, 'id_karyawan');
                    if ($this->is_sales === true) {
                        $list[] = $this->id_karyawan;
                    }
                    $this->db->where('activity.id_company', $this->id_company);
                    $this->db->where_in('activity.id_karyawan', $list);
                } else {
                    $this->db->where('activity.id_company', $this->id_company);
                    $this->db->where('activity.id_karyawan', $karyawan);
                }
            } else {
                $this->db->where('activity.id_company', $this->id_company);
            }
        }
        if ($pelanggan !== 'all') {
            $this->db->where('activity.id_pelanggan', $pelanggan);
        }
        if ($group !== 'all') {
            $this->db->group_start()
                ->where('main.id_group_m', $group)
                ->or_where('main.id_group_m', '')
                ->group_end();
        } else {
            if (!in_array($this->sales_admin_group, ["all", ""])) {
                $group_ids = explode(',', $this->sales_admin_group);
                $this->db->group_start()
                    ->where_in('main.id_group_m', $group_ids)
                    ->or_where('main.id_group_m', '')
                    ->group_end();
            }
        }
        if ($status !== 'all') {
            $this->db->where_in('activity.status', explode('_', $status));
        }
        $activity = $this->db->select('
                "activity" as tabel,
                activity.id_pelanggan,
                activity.status,
                "" as tahapan_sebelum,
                "" as tahapan_sekarang,
                "new" as kategori,
                "" as aktivitas_pindah_bulan,
                activity.jenis_activity as aktivitas,
                activity.poin,
                activity.keterangan,
                concat(activity.tgl_activity, " ", activity.jam_activity) as waktu,
                activity.user_input as user,
                activity.id_karyawan as user_id,
                main.nama_lead_source,
                dk.nama_lengkap,
                dk.nik
            ')
            ->from('sales_pipeline_activity activity')
            ->join('sales_pipeline_main main', 'main.id_pipeline_main = activity.id_pipeline_main')
            ->join('data_karyawan dk', 'dk.id_karyawan = activity.id_karyawan')
            ->get_compiled_select();

        if (!$mulai) $mulai = date('Y-m-') . '01';
        if (!$selesai) $selesai = date('Y-m-d');
        if ($karyawan !== 'all') {
            $this->db->where('user_id', $karyawan);
        } else {
            if ($this->is_sales_admin === false && $this->level_user !== '1') {
                if ($this->is_sales_atasan === true) {
                    $get_bawahan = $this->get_data_bawahan($this->id_karyawan);
                    $list = array_column($get_bawahan, 'id_karyawan');
                    if ($this->is_sales === true) {
                        $list[] = $this->id_karyawan;
                    }
                    $this->db->where_in('user_id', $list);
                } else {
                    $this->db->where('user_id', $karyawan);
                }
            }
        }
        $this->db->select('
                tabel,
                nama_perusahaan,
                aktivitas.id_pelanggan,
                status,
                tahapan_sebelum,
                tahapan_sekarang,
                kategori,
                aktivitas_pindah_bulan,
                aktivitas,
                poin,
                keterangan,
                waktu,
                user,
                user_id,
                nama_lead_source,
                nama_lengkap,
                nik
            ')
            ->from('(' . $log . ' UNION ALL ' . $activity . ') as aktivitas')
            ->join('sales_pelanggan as pelanggan', 'pelanggan.id_pelanggan = aktivitas.id_pelanggan')
            ->group_start()
            ->where('date(waktu) between "' . $mulai . '" and "' . $selesai . '"')
            ->group_end()
            ->order_by('waktu', 'desc');
    }

    public function get_data_bawahan($id): array
    {
        return $this->db->select('id_karyawan')
            ->from('sales_approver')
            ->where('id_company', $this->id_company)
            ->where('id_atasan', $id)
            ->get()
            ->result_array();
    }
}
