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

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

    function get_datatables($mulai, $selesai, $status, $karyawan, $group)
    {
        $this->_get_datatables_query($mulai, $selesai, $status, $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, $karyawan, $group)
    {
        $this->_get_datatables_query($mulai, $selesai, $status, $karyawan, $group);
        $query = $this->db->get();
        return $query->num_rows();
    }

    public function count_all($mulai, $selesai, $status, $karyawan, $group)
    {
        $this->_get_datatables_query($mulai, $selesai, $status, $karyawan, $group);
        return $this->db->count_all_results();
    }

    private function _get_datatables_query($mulai = '', $selesai = '', $status = 'all', $karyawan = 'all', $group = 'all')
    {
        $this->db->where('log.id_company', $this->id_company);
        if ($karyawan !== 'all') {
            $this->db->where('main.id_karyawan', $karyawan);
        }
        if ($group !== 'all') {
            $this->db->group_start()
                ->where('main.id_group_m', $group)
                ->or_where('main.id_group_m IS NULL', null, false)
                ->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 IS NULL', null, false)
                    ->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,
                log.id_pipeline_main_log as id,
                main.id_karyawan,
                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
            ')
            ->from('sales_pipeline_main_log log')
            ->join('sales_pipeline_main main', 'main.id_pipeline_main = log.id_pipeline_main')
            ->get_compiled_select();

        $this->db->where('activity.id_company', $this->id_company);
        if ($karyawan !== 'all') {
            $this->db->where('activity.id_karyawan', $karyawan);
        }
        if ($group !== 'all') {
            $this->db->group_start()
                ->where('main.id_group_m', $group)
                ->or_where('main.id_group_m IS NULL', null, false)
                ->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 IS NULL', null, false)
                    ->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,
                "" as id,
                activity.id_karyawan,
                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
            ')
            ->from('sales_pipeline_activity activity')
            ->join('sales_pipeline_main main', 'main.id_pipeline_main = activity.id_pipeline_main')
            ->get_compiled_select();

        if (!$mulai) $mulai = date('Y-m-') . '01';
        if (!$selesai) $selesai = date('Y-m-d');
        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('id_karyawan', $list);
            } else {
                $this->db->where('id_karyawan', $karyawan);
            }
        }
        $this->db->select('
                tabel,
                id,
                id_karyawan,
                nama_perusahaan,
                aktivitas.id_pelanggan,
                status,
                tahapan_sebelum,
                tahapan_sekarang,
                kategori,
                aktivitas_pindah_bulan,
                aktivitas,
                poin,
                keterangan,
                waktu,
                user,
                user_id
            ')
            ->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');
    }

    /**
     * single approve
     *
     * @param string $tabel => log/activity
     * @param array $data => data yang ditambah/diupdate/dihapus
     * @param string $action => New/Move, wajib jika tabel log
     * @return void
     */
    public function verify_approve(string $tabel, array $data, string $action = '')
    {
        $this->db->trans_begin();

        if ($tabel == 'log') {
            $data_trx       = $data[0];
            $data_main      = $data[1];
            $data_main_log  = $data[2];
            $data_realisasi = $data[3];
            $where_update   = $data[4];

            // jika pindah bulan
            if ($data_trx['for'] == 'clone') {
                $this->db->insert_batch('sales_pipeline_trx', $data_trx['data']);
            } else {
                if ($action == 'New') {
                    $this->db->insert('sales_pipeline_trx', $data_trx);
                } else {
                    $this->db->where('id_karyawan', $where_update['id_karyawan']);
                    $this->db->where('tahun', $where_update['tahun']);
                    $this->db->where('bulan', $where_update['bulan']);
                    $this->db->where('id_company', $where_update['id_company']);
                    $this->db->where('id_pipeline_main', $where_update['id_pipeline_main']);
                    $this->db->where('no_urut_tahapan > ' . $data_trx['where_delete']);
                    $this->db->delete('sales_pipeline_trx');

                    if ($data_trx['for'] === 'update') {
                        $where_update['id_tahapan'] = $data_trx['where_update'];
                        $this->db->update('sales_pipeline_trx', $data_trx['data'], $where_update);
                        unset($where_update['id_tahapan']);
                    } elseif ($data_trx['for'] === 'insert') {
                        $this->db->insert('sales_pipeline_trx', $data_trx['data']);
                    }
                }
            }

            if ($data_realisasi['jenis'] == 'wont') {
                $this->db->update('sales_pelanggan', $data_realisasi['update_pelanggan'], $data_realisasi['where_pelanggan']);
                if ($data_realisasi['target_personil_for'] == 'update') {
                    $$where_update['id_group_m'] = $data_realisasi['target_personil_r']['id_group_m'];
                    unset($data_realisasi['target_personil_r']['id_group_m']);
                    unset($where_update['id_pipeline_main']);
                    $this->db->update('sales_target_personil_r', $data_realisasi['target_personil_r'], $where_update);
                } elseif ($data_realisasi['target_personil_for'] == 'insert') {
                    $this->db->insert('sales_target_personil_r', $data_realisasi['target_personil_r']);
                    if ($data_realisasi['target_personil_p']) $this->db->insert('sales_target_personil_p', $data_realisasi['target_personil_p']);
                }
            }

            $this->db->update('sales_pipeline_main', $data_main['update'], $data_main['where']);
            $this->db->update('sales_pipeline_main_log', $data_main_log['update'], $data_main_log['where']);
        } else {
            $this->db->update('sales_pipeline_activity', $data['update'], $data['where']);
        }

        if ($this->db->trans_status() === FALSE) {
            $this->db->trans_rollback();
            return __response_approve(false);
        } else {
            $this->db->trans_commit();
            return __response_approve(true);
        }
    }

    /**
     * single reject
     *
     * @param string $tabel => log/activity
     * @param array $data => data yang ditambah/diupdate/dihapus
     * @param string $action => New/Move, wajib jika tabel log
     * @return void
     */
    public function verify_reject(string $tabel, array $data, string $action = '')
    {
        $this->db->trans_begin();

        if ($tabel == 'log') {
            $data_main            = $data[0];
            $data_main_log_update = $data[1];
            $data_main_log_insert = $data[2];

            $this->db->update('sales_pipeline_main', $data_main['update'], $data_main['where']);
            $this->db->update('sales_pipeline_main_log', $data_main_log_update['update'], $data_main_log_update['where']);

            if ($action == 'Move') {
                $this->db->insert('sales_pipeline_main_log', $data_main_log_insert);
            }
        } else {
            $this->db->update('sales_pipeline_activity', $data['update'], $data['where']);
        }

        if ($this->db->trans_status() === FALSE) {
            $this->db->trans_rollback();
            return __response_update(false);
        } else {
            $this->db->trans_commit();
            return __response_update(true);
        }
    }

    /**
     * mass approve
     *
     * @param string $tabel => log/activity
     * @param array $data => data yang ditambah/diupdate/dihapus
     * @return void
     */
    public function verify_mass_approve(string $tabel, array $data)
    {
        if ($tabel == 'log') {
            $data_trx       = $data[0];
            $data_main      = $data[1];
            $data_main_log  = $data[2];
            $data_realisasi = $data[3];

            if ($data_trx['delete']) {
                $conditions = [];
                foreach ($data_trx['delete'] as $value) {
                    $concatFields = "concat(id_karyawan, tahun, bulan, id_company, id_pipeline_main)";
                    $condition    = "($concatFields = '$value[0]' AND no_urut_tahapan > $value[1])";
                    $conditions[] = $condition;
                }
                $this->db->query("DELETE FROM sales_pipeline_trx WHERE " . implode(' OR ', $conditions));
            }
            $this->db->insert_batch('sales_pipeline_trx', $data_trx['insert']);
            $this->db->update_batch('sales_pipeline_trx', $data_trx['update'], 'concat(id_karyawan,tahun,bulan,id_company,id_pipeline_main,id_tahapan)');

            if ($data_realisasi) {
                if ($data_realisasi['insert']['target_personil_p']) $this->db->insert_batch('sales_target_personil_p', $data_realisasi['insert']['target_personil_p']);
                $this->db->insert_batch('sales_target_personil_r', $data_realisasi['insert']['target_personil_r']);
                $this->db->update_batch('sales_target_personil_r', $data_realisasi['update'], 'concat(id_karyawan,tahun,bulan,id_company,id_group_m)');
                $this->db->update_batch('sales_pelanggan', $data_realisasi['pelanggan'], 'id_pelanggan');
            }

            $this->db->update_batch('sales_pipeline_main', $data_main, 'concat(id_pipeline_main,id_company)');
            $this->db->update_batch('sales_pipeline_main_log', $data_main_log, 'concat(id_pipeline_main_log,id_company)');
        } else {
            $this->db->update_batch('sales_pipeline_activity', $data, 'concat(id_company,id_karyawan,tgl_activity,jam_activity)');
        }
    }

    /**
     * mass reject
     *
     * @param string $tabel => log/activity
     * @param array $data => data yang ditambah/diupdate/dihapus
     * @param string $action => New/Move, wajib jika tabel log
     * @return void
     */
    public function verify_mass_reject(string $tabel, array $data, string $action = '')
    {
        if ($tabel == 'log') {
            $data_main            = $data[0];
            $data_main_log_update = $data[1];
            $data_main_log_insert = $data[2];

            $this->db->update_batch('sales_pipeline_main', $data_main, 'id_pipeline_main');
            $this->db->update_batch('sales_pipeline_main_log', $data_main_log_update, 'id_pipeline_main_log');

            if ($action == 'Move') {
                $this->db->insert_batch('sales_pipeline_main_log', $data_main_log_insert);
            }
        } else {
            $this->db->update_batch('sales_pipeline_activity', $data, 'concat(id_company,id_karyawan,tgl_activity,jam_activity)');
        }
    }

    public function verify_reject_pindah_bulan($data)
    {
        $this->db->trans_begin();

        $this->db->update('sales_pipeline_main', [
            'status'          => 'tolak',
            'id_pindah_bulan' => ''
        ], ['id_pipeline_main' => $data->id_pipeline_main]);

        $this->db->update('sales_pipeline_main_log', [
            'status'       => 'tolak',
            'user_approve' => $this->nik_karyawan . ' - ' . $this->nm_karyawan,
            'tgl_approve'  => date('Y-m-d H:i:s'),
        ], ['id_pipeline_main_log' => $data->id_pipeline_main_log]);

        if ($this->db->trans_status() === FALSE) {
            $this->db->trans_rollback();
            return __response_update(false);
        } else {
            $this->db->trans_commit();
            return __response_update(true);
        }
    }

    public function preview_verifikasi($tabel, $combined_id, $type = 'single')
    {
        if ($tabel == 'log') {
            if ($type == 'single') {
                $this->db->where('log.id_pipeline_main_log', $combined_id);
            } else {
                $this->db->where_in('log.id_pipeline_main_log', $combined_id);
            }
            $this->db->select('
                    log.id_pipeline_main,
                    log.id_pipeline_main_log,
                    pelanggan.nama_perusahaan,
                    log.status,
                    log.tgl_input as waktu,
                    log.tahapan_sebelum_nm as tahapan_sebelum,
                    log.tahapan_sebelum_id as id_tahapan_sebelum,
                    log.tahapan_sesudah_nm as tahapan_sekarang,
                    log.tahapan_sesudah_id as id_tahapan_sekarang,
                    log.kategori,
                    log.keterangan as aktivitas_pindah_bulan,
                    IF(log.tahapan_sebelum_id = "", "New", "Move") as aktivitas,
                    "" as poin,
                    main.keterangan,
                    "0" as flag_lokasi,
                    "0" as flag_upload_foto,
                    "" as lat,
                    "" as long,
                    "#" as path_foto,
                    log.user_input as user,
                    main.id_karyawan as user_id,
                    main.nama_lead_source,
                    main.tahun,
                    main.bulan,
                    sales.nik,
                    sales.nama_lengkap as nama_karyawan
                ')
                ->from('sales_pipeline_main_log log')
                ->join('sales_pipeline_main main', 'main.id_pipeline_main = log.id_pipeline_main')
                ->join('data_karyawan sales', 'sales.id_karyawan = main.id_karyawan')
                ->join('sales_pelanggan as pelanggan', 'pelanggan.id_pelanggan = main.id_pelanggan')
                ->where('log.id_company', $this->id_company);
        } else {
            if ($type == 'single') {
                $split = explode('_', $combined_id);
                $id    = $split[0];
                $tgl   = $split[1];
                $jam   = str_replace('-', ':', $split[2]);

                $this->db->where('aktivitas.id_company', $this->id_company)
                    ->where('aktivitas.id_karyawan', $id)
                    ->where('aktivitas.tgl_activity', $tgl)
                    ->where('aktivitas.jam_activity', $jam);
            } else {
                $this->db->where_in('concat(aktivitas.id_company,aktivitas.id_karyawan,aktivitas.tgl_activity,aktivitas.jam_activity)', $combined_id);
            }
            $this->db->select('
                    aktivitas.id_pipeline_main,
                    aktivitas.id_pipeline_main_log,
                    pelanggan.nama_perusahaan,
                    aktivitas.status,
                    concat(aktivitas.tgl_activity, " ", aktivitas.jam_activity) as waktu,
                    log.tahapan_sesudah_nm as tahapan_sekarang,
                    "new" as kategori,
                    "" as aktivitas_pindah_bulan,
                    aktivitas.jenis_activity as aktivitas,
                    aktivitas.poin,
                    aktivitas.keterangan,
                    aktivitas.flag_lokasi,
                    aktivitas.flag_upload_foto,
                    aktivitas.lat,
                    aktivitas.long,
                    aktivitas.path_foto,
                    aktivitas.user_input as user,
                    aktivitas.id_karyawan as user_id,
                    main.nama_lead_source,
                    main.tahun,
                    main.bulan
                ')
                ->from('sales_pipeline_activity as aktivitas')
                ->join('sales_pipeline_main as main', 'main.id_pipeline_main = aktivitas.id_pipeline_main')
                ->join('sales_pelanggan as pelanggan', 'pelanggan.id_pelanggan = aktivitas.id_pelanggan')
                ->join('sales_pipeline_main_log as log', 'log.id_pipeline_main_log = aktivitas.id_pipeline_main_log');
        }

        if ($type == 'single') {
            return $this->db->get()->row();
        } else {
            return $this->db->get()->result();
        }
    }

    public function get_detail_pipeline(string $id)
    {
        return $this->db->select('
                main.id_karyawan,
                main.id_pipeline_main,
                main.title,
                main.nama_lead_source,
                main.tahun,
                main.bulan,
                main.tgl_input,
                main.id_pindah_bulan,
                group.id_group_m as id_group,
                group.kode as kode_group,
                group.nama as nama_group,
                sales.nik as sales_nik,
                sales.nama_lengkap as sales_nama,
                pelanggan.id_pelanggan,
                pelanggan.nama_perusahaan,
                pelanggan.alamat,
                j_perusahaan.nama as nama_jenis_perusahaan,
                prov.nama as provinsi,
                kota.nama as kota,
                kec.nama as kecamatan,
                kel.nama as kelurahan,
                SUM(IF(id_pipeline_layanan IS NOT NULL, 1, 0)) AS jumlah_layanan,
                SUM(IF(id_pipeline_layanan IS NOT NULL, harga_total, 0)) AS nominal_layanan
            ')
            ->from('sales_pipeline_main main')
            ->join('sales_group_m group', 'group.id_group_m = main.id_group_m', 'left')
            ->join('data_karyawan as sales', 'sales.id_karyawan = main.id_karyawan')
            ->join('sales_pelanggan pelanggan', 'pelanggan.id_pelanggan = main.id_pelanggan')
            ->join('sales_jenis_perusahaan_m j_perusahaan', 'j_perusahaan.id_jenis_perusahaan_m = pelanggan.id_jenis_perusahaan_m', 'left')
            ->join('master_provinsi prov', 'prov.id_prov = pelanggan.id_provinsi')
            ->join('master_kota kota', 'kota.id_kota = pelanggan.id_kota')
            ->join('master_kecamatan kec', 'kec.id_kecamatan = pelanggan.id_kecamatan', 'left')
            ->join('master_kelurahan kel', 'kel.id_kelurahan = pelanggan.id_kelurahan', 'left')
            ->join('sales_pipeline_layanan layanan', 'layanan.id_pipeline_main = main.id_pipeline_main', 'left')
            ->where('main.id_company', $this->id_company)
            ->where('main.id_pipeline_main', $id)
            ->group_by('main.id_pipeline_main')
            ->get()
            ->row();
    }

    public function get_detail_pipeline_by_log(array $id_log): array
    {
        $list_log = $this->db->select('
                main.id_karyawan,
                main.id_pipeline_main,
                main.title,
                main.nama_lead_source,
                main.tahun,
                main.bulan,
                main.tgl_input,
                main.id_pindah_bulan,
                group.id_group_m as id_group,
                group.kode as kode_group,
                group.nama as nama_group,
                sales.nik as sales_nik,
                sales.nama_lengkap as sales_nama,
                pelanggan.id_pelanggan,
                pelanggan.nama_perusahaan,
                pelanggan.alamat,
                j_perusahaan.nama as nama_jenis_perusahaan,
                prov.nama as provinsi,
                kota.nama as kota,
                kec.nama as kecamatan,
                kel.nama as kelurahan,
                SUM(IF(id_pipeline_layanan IS NOT NULL, 1, 0)) AS jumlah_layanan,
                SUM(IF(id_pipeline_layanan IS NOT NULL, harga_total, 0)) AS nominal_layanan
            ')
            ->from('sales_pipeline_main_log log')
            ->join('sales_pipeline_main as main', 'main.id_pipeline_main = log.id_pipeline_main')
            ->join('sales_group_m group', 'group.id_group_m = main.id_group_m', 'left')
            ->join('data_karyawan as sales', 'sales.id_karyawan = main.id_karyawan')
            ->join('sales_pelanggan pelanggan', 'pelanggan.id_pelanggan = main.id_pelanggan')
            ->join('sales_jenis_perusahaan_m j_perusahaan', 'j_perusahaan.id_jenis_perusahaan_m = pelanggan.id_jenis_perusahaan_m', 'left')
            ->join('master_provinsi prov', 'prov.id_prov = pelanggan.id_provinsi')
            ->join('master_kota kota', 'kota.id_kota = pelanggan.id_kota')
            ->join('master_kecamatan kec', 'kec.id_kecamatan = pelanggan.id_kecamatan', 'left')
            ->join('master_kelurahan kel', 'kel.id_kelurahan = pelanggan.id_kelurahan', 'left')
            ->join('sales_pipeline_layanan layanan', 'layanan.id_pipeline_main = main.id_pipeline_main', 'left')
            ->where('main.id_company', $this->id_company)
            ->where_in('log.id_pipeline_main_log', $id_log)
            ->group_by('main.id_pipeline_main')
            ->get()
            ->result();

        $data = [];
        foreach ($list_log as $value) {
            $data[$value->id_pipeline_main] = [
                'id_karyawan'           => $value->id_karyawan,
                'id_pipeline_main'      => $value->id_pipeline_main,
                'title'                 => $value->title,
                'nama_lead_source'      => $value->nama_lead_source,
                'tahun'                 => $value->tahun,
                'bulan'                 => $value->bulan,
                'tgl_input'             => $value->tgl_input,
                'id_pindah_bulan'       => $value->id_pindah_bulan,
                'id_group'              => $value->id_group,
                'kode_group'            => $value->kode_group,
                'nama_group'            => $value->nama_group,
                'sales_nik'             => $value->sales_nik,
                'sales_nama'            => $value->sales_nama,
                'id_pelanggan'          => $value->id_pelanggan,
                'nama_perusahaan'       => $value->nama_perusahaan,
                'alamat'                => $value->alamat,
                'nama_jenis_perusahaan' => $value->nama_jenis_perusahaan,
                'provinsi'              => $value->provinsi,
                'kota'                  => $value->kota,
                'kecamatan'             => $value->kecamatan,
                'kelurahan'             => $value->kelurahan,
                'jumlah_layanan'        => $value->jumlah_layanan,
                'nominal_layanan'       => $value->nominal_layanan
            ];
        }

        return $data;
    }

    public function get_data_tahapan_trx($tahun, $bulan, $id_tahapan_m = '')
    {
        $this->db->where('tahun = "' . $tahun . '"');
        $this->db->where('bulan = "' . $bulan . '"');
        if ($id_tahapan_m) {
            $this->db->where('id_tahapan_m', $id_tahapan_m);
        }
        $this->db->where('id_company', $this->id_company);

        return $this->db->select('
                id_tahapan_m,
                jenis,
                nama,
                warna,
                warna_text,
                no_urut
            ')
            ->from('sales_tahapan_trx')
            ->order_by('no_urut', 'asc')
            ->get()
            ->result();
    }

    public function get_data_existing_trx($id_karyawan, $tahun, $bulan, $id_pipeline_main = '', $id_tahapan_m = '')
    {
        $this->db->where('id_karyawan', $id_karyawan)
            ->where('tahun', $tahun)
            ->where('bulan', $bulan)
            ->where('id_company', $this->id_company);

        if ($id_pipeline_main != '') $this->db->where('id_pipeline_main', $id_pipeline_main);
        if ($id_tahapan_m != '') $this->db->where('id_tahapan', $id_tahapan_m);

        return $this->db->get('sales_pipeline_trx')->result();
    }

    public function get_realisasi_sales($id_karyawan, $tahun, $bulan, $id_group, $jenis = 'wont')
    {
        $get_realisasi = $this->db->select('
                SUM(IF(layanan.harga_total IS NOT NULL, layanan.harga_total, 0)) as nominal_realisasi
            ')
            ->from('sales_pipeline_main main')
            ->join('sales_tahapan_trx trx', 'trx.id_tahapan_m = main.id_tahapan_m and trx.tahun = main.tahun and trx.bulan = main.bulan and trx.id_company = main.id_company')
            ->join('sales_pipeline_layanan layanan', 'layanan.id_pipeline_main = main.id_pipeline_main', 'left')
            ->where('main.id_company', $this->id_company)
            ->where('main.id_karyawan', $id_karyawan)
            ->where("main.tahun = '$tahun'")
            ->where("main.bulan = '$bulan'")
            ->where('main.id_group_m', $id_group)
            ->where('main.status', 'approve')
            ->where('trx.jenis', $jenis)
            ->group_by('main.id_karyawan,main.id_pipeline_main')
            ->get_compiled_select();

        return $this->db->select('
                COUNT(*) as jumlah_po,
                SUM(nominal_realisasi) as nominal_realisasi
            ')
            ->from("($get_realisasi) as realisasi")
            ->get()
            ->row();
    }

    /**
     * get data pengajuan dari sales_pipeline_main_log dan sales_pipeline_activity
     * untuk mendapatkan id kebutuhan approve all
     *
     * @param string $mulai
     * @param string $selesai
     * @param string $karyawan => id_karyawan
     * @return void
     */
    public function get_data_pengajuan($mulai, $selesai, $karyawan = 'all')
    {
        $log = $this->db->select('
                "log" as tabel,
                log.id_pipeline_main_log as id,
                log.tgl_input as waktu,
                main.id_karyawan as user_id
            ')
            ->from('sales_pipeline_main_log log')
            ->join('sales_pipeline_main main', 'main.id_pipeline_main = log.id_pipeline_main')
            ->where('log.id_company', $this->id_company)
            ->where('log.status', 'pengajuan')
            ->get_compiled_select();

        $activity = $this->db->select('
                "activity" as tabel,
                "" as id,
                concat(activity.tgl_activity, " ", activity.jam_activity) as waktu,
                activity.id_karyawan as user_id
            ')
            ->from('sales_pipeline_activity activity')
            ->join('sales_pipeline_main_log log', 'log.id_pipeline_main_log = activity.id_pipeline_main_log')
            ->where('activity.id_company', $this->id_company)
            ->where('activity.status', 'pengajuan')
            ->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');
                    $this->db->where_in('user_id', $list);
                }
            }
        }
        $this->db->select('
                tabel,
                id,
                waktu,
                user_id
            ')
            ->from('(' . $log . ' UNION ALL ' . $activity . ') as aktivitas')
            ->group_start()
            ->where('date(waktu) between "' . $mulai . '" and "' . $selesai . '"')
            ->group_end()
            ->order_by('waktu', 'desc');

        return $this->db->get()->result();
    }

    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();
    }

    public function get_sales_group($id_karyawan)
    {
        return $this->db->select('
                group.id_group_m,
                group.kode,
                group.nama
            ')
            ->from('sales_approver sales')
            ->join('sales_group_m group', 'group.id_group_m = sales.id_group_m')
            ->where('sales.id_karyawan', $id_karyawan)
            ->get()
            ->row();
    }
}
