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

class Kanban extends MY_Controller
{
    public function __construct()
    {
        parent::__construct();
        $this->load->model('sales_activity/kanban/Kanban_m', 'model');
    }

    public function index()
    {
        $data['judul']      = 'Kanban Board';
        $data['tahapan']    = $this->model->get_data_tahapan();
        $data['id_tahapan'] = [];
        foreach ($data['tahapan'] as $tahap) {
            $data['id_tahapan'][] = '#' . $tahap->id_tahapan_m;
        }
        $this->load->view('sales_activity/kanban/index', $data);
    }

    public function form($jenis = '', $id_pipeline = '')
    {
        if ($jenis === 'wont') {
            $data['id']      = $id_pipeline;
            $data['jenis']   = $jenis;
            $this->load->view('sales_activity/kanban/form_won', $data);
        } elseif ($jenis === 'lost') {
            $this->load->view('sales_activity/kanban/form_lost');
        } else {
            $this->load->view('sales_activity/kanban/form');
        }
    }

    public function form_layanan($id_pipeline = '')
    {
        $data['id']      = $id_pipeline;
        $data['layanan'] = $this->model->get_detail_layanan($id_pipeline)->result();
        $this->load->view('sales_activity/kanban/form_won_layanan', $data);
    }

    public function data_kanban($periode, $group, $pelanggan, $sales)
    {
        $periode ?: $periode = date('Y-m');
        list($tahun, $bulan) = explode('-', $periode);

        $task_exist = $this->model->check_task_exist($tahun, $bulan);
        if ($task_exist) {
            $data['tahapan'] = $this->model->get_data_tahapan_trx($tahun, $bulan);
        } else {
            $data['tahapan'] = $this->model->get_data_tahapan();
        }
        $data['periode']     = $periode;
        $data['list_kanban'] = $this->model->get_data_list_kanban($tahun, $bulan, $group, $pelanggan, $sales);
        $data['id_tahapan']  = [];
        foreach ($data['tahapan'] as $tahap) {
            $data['id_tahapan'][] = '#' . $tahap->id_tahapan_m;
        }
        $this->load->view('sales_activity/kanban/data_kanban', $data);
    }


    public function option_task($id, $action, $periode = '')
    {
        if ($action === 'detail') {
            $data['id'] = $id;
            $this->load->view('sales_activity/kanban/detail/index', $data);
        } elseif ($action === 'move_month') {
            $periode ?: $periode = date('Y-m');
            $split = explode('-', $periode);
            $tahun = $split[0];
            $bulan = $split[1];

            $list_tahapan = $this->model->get_data_tahapan_trx($tahun, $bulan);
            $won_lost     = [];
            foreach ($list_tahapan as $value) {
                if (in_array($value->jenis, ['wont', 'lost'])) {
                    $won_lost[] = "<b>$value->nama</b>";
                }
            }

            $data['id']       = $id;
            $data['periode']  = $periode;
            $data['won_lost'] = implode(' dan ', $won_lost);
            $this->load->view('sales_activity/kanban/form_pindah_bulan', $data);
        } else {
            if ($action === 'hapus') {
                $data['title'] = $this->lang->line('title_form_hapus_data');
            } elseif ($action === 'batal') {
                $data['title'] = $this->lang->line('title_form_batal_pengajuan');
            }
            $data['preview_kanban'] = $this->model->get_data_list_kanban('', '', '', '', '', $id);
            $this->load->view('sales_activity/kanban/form_preview', $data);
        }
    }

    public function save_pipeline()
    {
        $for_sales            = false;
        $sales                = $this->input->post('sales');
        $jenis_pelanggan      = $this->input->post('jenis_pelanggan');
        $pelanggan            = $this->input->post('pelanggan');
        $jenis_perusahaan     = $this->input->post('jenis_perusahaan');
        $id_lead_source       = $this->input->post('lead_source');
        $alamat_perusahaan    = $this->input->post('alamat_perusahaan');
        $provinsi_perusahaan  = $this->input->post('provinsi_perusahaan');
        $kota_perusahaan      = $this->input->post('kota_perusahaan');
        $kecamatan_perusahaan = $this->input->post('kecamatan_perusahaan');
        $kelurahan_perusahaan = $this->input->post('kelurahan_perusahaan');
        $nama_pic             = $this->input->post('nama_pic');
        $email_pic            = $this->input->post('email_pic');
        $telp_pic             = $this->input->post('telp_pic');
        $title                = $this->input->post('title');
        $keterangan           = $this->input->post('keterangan');
        $produk               = $this->input->post('produk');

        // validation
        if (!$jenis_pelanggan) {
            return __response_save(false, ['message' => '<font style="color:Crimson"><i class="fa fa-exclamation-triangle"></i> ' . $this->lang->line('error_jenis_pelanggan_kosong') . '</font>']);
        }
        if ($this->level_user === '1' || $this->is_sales_admin === true || $this->is_sales_atasan === true) {
            $for_sales = true;
            if (!$sales) {
                return __response_save(false, ['message' => '<font style="color:Crimson"><i class="fa fa-exclamation-triangle"></i> ' . $this->lang->line('error_pilih_sales_kosong') . '</font>']);
            }
        }
        if ($jenis_pelanggan == 'perusahaan') {
            if (!$pelanggan) {
                return __response_save(false, ['message' => '<font style="color:Crimson"><i class="fa fa-exclamation-triangle"></i> ' . $this->lang->line('error_pilih_perusahaan_kosong') . '</font>']);
            }
            if (!$jenis_perusahaan) {
                return __response_save(false, ['message' => '<font style="color:Crimson"><i class="fa fa-exclamation-triangle"></i> ' . $this->lang->line('error_pilih_jenis_perusahaan_kosong') . '</font>']);
            }
            if ($nama_pic == '') {
                return __response_save(false, ['message' => '<font style="color:Crimson"><i class="fa fa-exclamation-triangle"></i> ' . $this->lang->line('error_nama_kosong') . '</font>']);
            }
            // reformat data
            $nama_pic = preg_replace('/[^a-zA-Z0-9\.\,\-\' ]/', '', $nama_pic);
        } else {
            if (!$pelanggan) {
                return __response_save(false, ['message' => '<font style="color:Crimson"><i class="fa fa-exclamation-triangle"></i> ' . $this->lang->line('error_nama_kosong') . '</font>']);
            }
            // reformat data
            $pelanggan = preg_replace('/[^a-zA-Z0-9\.\,\-\' ]/', '', $pelanggan);
        }
        if (!$id_lead_source) {
            return __response_save(false, ['message' => '<font style="color:Crimson"><i class="fa fa-exclamation-triangle"></i> ' . $this->lang->line('error_pilih_lead_source_kosong') . '</font>']);
        }
        if ($alamat_perusahaan == '') {
            return __response_save(false, ['message' => '<font style="color:Crimson"><i class="fa fa-exclamation-triangle"></i> ' . $this->lang->line('error_alamat_perusahaan') . '</font>']);
        }
        if (!$provinsi_perusahaan) {
            return __response_save(false, ['message' => '<font style="color:Crimson"><i class="fa fa-exclamation-triangle"></i> ' . $this->lang->line('error_provinsi') . '</font>']);
        }
        if (!$kota_perusahaan) {
            return __response_save(false, ['message' => '<font style="color:Crimson"><i class="fa fa-exclamation-triangle"></i> ' . $this->lang->line('error_kota') . '</font>']);
        }
        if ($telp_pic == '') {
            return __response_save(false, ['message' => '<font style="color:Crimson"><i class="fa fa-exclamation-triangle"></i> ' . $this->lang->line('error_telp_pic_kosong') . '</font>']);
        }
        if ($title == '') {
            return __response_save(false, ['message' => '<font style="color:Crimson"><i class="fa fa-exclamation-triangle"></i> ' . $this->lang->line('error_judul') . '</font>']);
        }

        // reformat data
        $telp_pic = preg_replace('/[^0-9]/', '', $telp_pic);

        // data insert new
        $pelanggan_is_exist = $this->db->get_where('sales_pelanggan', ['id_company' => $this->id_company, 'id_pelanggan' => $pelanggan])->num_rows();
        $data_pelanggan = [
            'id_jenis_perusahaan_m' => $jenis_perusahaan,
            'id_lead_m'             => $id_lead_source,
            'kategori_pelanggan'    => $jenis_pelanggan,
            'alamat'                => $alamat_perusahaan,
            'id_provinsi'           => $provinsi_perusahaan,
            'id_kota'               => $kota_perusahaan,
            'id_kecamatan'          => !empty($kecamatan_perusahaan) ? $kecamatan_perusahaan : "",
            'id_kelurahan'          => !empty($kelurahan_perusahaan) ? $kelurahan_perusahaan : "",
            'pic_email'             => $email_pic,
            'pic_telp'              => $telp_pic,
            'id_company'            => $this->id_company,
            'user_input'            => $this->nik_karyawan . ' - ' . $this->nm_karyawan,
        ];

        if ($pelanggan_is_exist > 0) {
            $id_pelanggan_update        = $pelanggan;
            $data_pelanggan['pic_nama'] = $nama_pic;
        } else {
            $id_pelanggan_update = "";
            $data_pelanggan = array_merge($data_pelanggan, [
                'id_pelanggan'    => $this->fungsi->getNextId('sales_pelanggan', 'id_pelanggan', $this->id_company, 5),
                'pic_nama'        => ($jenis_pelanggan == 'perorangan') ? $pelanggan : $nama_pic,
                'nama_perusahaan' => $pelanggan,
                'jenis'           => '2',
            ]);
        }

        $lead_source = $this->db->get_where('sales_lead_m', ['id_lead_m' => $id_lead_source])->row();
        $tahapan     = $this->db->get_where('sales_tahapan_m', ['id_company' => $this->id_company, 'jenis' => 'incoming'])->row();
        $sales_group = $this->model->get_sales_group($for_sales ? $sales : $this->id_karyawan);

        $id_pipeline_main = $this->fungsi->getNextId('sales_pipeline_main', 'id_pipeline_main', $this->id_company, 5);
        $data_pipeline_main = [
            'id_pipeline_main' => $id_pipeline_main,
            'id_karyawan'      => ($for_sales ? $sales : $this->id_karyawan),
            'tahun'            => date('Y'),
            'bulan'            => date('m'),
            'id_lead_m'        => $lead_source->id_lead_m,
            'nama_lead_source' => $lead_source->nama,
            'id_tahapan_m'     => $tahapan->id_tahapan_m,
            'pic_nama'         => $data_pelanggan['pic_nama'],
            'pic_email'        => $email_pic,
            'pic_telp'         => $telp_pic,
            'title'            => $title,
            'keterangan'       => $keterangan,
            'id_group_m'       => $sales_group->id_group_m ?: '',
            'id_company'       => $this->id_company,
            'user_input'       => $this->nik_karyawan . ' - ' . $this->nm_karyawan,
        ];

        $data_pipeline_main_log = [
            'id_pipeline_main_log' => $this->fungsi->getNextId('sales_pipeline_main_log', 'id_pipeline_main_log', $this->id_company, 5),
            'id_pipeline_main'     => $id_pipeline_main,
            'tahapan_sesudah_id'   => $tahapan->id_tahapan_m,
            'tahapan_sesudah_nm'   => $tahapan->nama,
            'id_company'           => $this->id_company,
            'user_input'           => $this->nik_karyawan . ' - ' . $this->nm_karyawan,
        ];

        $data_pipeline_layanan = [];
        if (count($produk) > 0) {
            $id_pipeline_layanan = $this->fungsi->getNextId('sales_pipeline_layanan', 'id_pipeline_layanan', $this->id_company, 5);
            foreach ($produk as $item) {
                preg_match('/^([^#]+)/', $item[0], $matches);
                $id_product  = $matches[1];
                $harga       = (int)str_replace('.', '', $item[1]);
                $jumlah      = (int)str_replace('.', '', $item[2]);
                $diskon      = $item[3] ? (float)str_replace(',', '.', $item[3]) : 0;
                $harga_total = ($harga * $jumlah) - (($harga * $jumlah) * $diskon / 100);

                $data_pipeline_layanan[] = [
                    'id_pipeline_layanan' => $id_pipeline_layanan,
                    'id_pipeline_main'    => $id_pipeline_main,
                    'id_product_m'        => $id_product,
                    'harga'               => $harga,
                    'jumlah'              => $jumlah,
                    'diskon'              => $diskon,
                    'harga_total'         => $harga_total,
                    'id_company'          => $this->id_company,
                    'user_input'          => $this->nik_karyawan . ' - ' . $this->nm_karyawan,
                ];

                $last_id = substr($id_pipeline_layanan, -5);
                $id_pipeline_layanan = $this->id_company . date('Ym') . sprintf("%05d", ($last_id + 1));
            }
        }

        // tanpa approval
        $data_pipeline_trx = [];
        if ($this->is_sales_skip_approval === true) {
            if ($pelanggan_is_exist > 0) {
                $id_pelanggan = $pelanggan;
                $pelanggan    = $this->db->select('
                        sp.nama_perusahaan,
                        sp.alamat,
                        mk.nama as kota,
                        mp.nama as provinsi,
                        IF(m_kec.nama IS NULL, "", m_kec.nama) AS kecamatan,
                        IF(m_kel.nama IS NULL, "", m_kel.nama) AS kelurahan,
                        sjp.nama as jenis_perusahaan
                    ')
                    ->from('sales_pelanggan sp')
                    ->from('sales_jenis_perusahaan_m sjp', 'sjp.id_jenis_perusahaan_m = sp.id_jenis_perusahaan_m', 'left')
                    ->join('master_provinsi mp', 'mp.id_prov = sp.id_provinsi')
                    ->join('master_kota mk', 'mk.id_kota = sp.id_kota')
                    ->join('master_kecamatan m_kec', 'm_kec.id_kecamatan = sp.id_kecamatan', 'LEFT')
                    ->join('master_kelurahan m_kel', 'm_kel.id_kelurahan = sp.id_kelurahan', 'LEFT')
                    ->where('sp.id_pelanggan', $id_pelanggan)
                    ->get()
                    ->row();

                $nama_perusahaan       = $pelanggan->nama_perusahaan;
                $provinsi              = $pelanggan->provinsi;
                $kota                  = $pelanggan->kota;
                $kecamatan             = $pelanggan->kecamatan;
                $kelurahan             = $pelanggan->kelurahan;
                $alamat                = $pelanggan->alamat;
                $nama_jenis_perusahaan = $pelanggan->jenis_perusahaan;
            } else {
                $id_pelanggan          = $data_pelanggan['id_pelanggan'];
                $nama_perusahaan       = $pelanggan;
                $provinsi              = $this->db->get_where('master_provinsi', ['id_prov' => $provinsi_perusahaan])->row()->nama;
                $kota                  = $this->db->get_where('master_kota', ['id_kota' => $kota_perusahaan])->row()->nama;
                $kecamatan             = $this->db->get_where('master_kecamatan', ['id_kecamatan' => $kecamatan_perusahaan])->row()->nama;
                $kelurahan             = $this->db->get_where('master_kelurahan', ['id_kelurahan' => $kelurahan_perusahaan])->row()->nama;
                $alamat                = $alamat_perusahaan;
                $nama_jenis_perusahaan = $jenis_perusahaan ? $this->db->get_where('sales_jenis_perusahaan_m', ['id_jenis_perusahaan_m' => $jenis_perusahaan])->row()->nama : '';
            }

            $data_pipeline_main['status']          = 'approve';
            $data_pipeline_main_log['status']      = 'approve';
            $data_pipeline_main_log['tgl_approve'] = date('Y-m-d H:i:s');

            $data_pipeline_trx = [
                'id_pipeline_trx'       => $this->fungsi->getNextId('sales_pipeline_trx', 'id_pipeline_trx', $this->id_company, 5),
                'id_pipeline_main'      => $id_pipeline_main,
                'id_karyawan'           => ($for_sales ? $sales : $this->id_karyawan),
                'nik'                   => $this->nik_karyawan,
                'nama_karyawan'         => $this->nm_karyawan,
                'tahun'                 => date('Y'),
                'bulan'                 => date('m'),
                'id_tahapan'            => $tahapan->id_tahapan_m,
                'jenis_tahapan'         => $tahapan->jenis,
                'nama_tahapan'          => $tahapan->nama,
                'warna_tahapan'         => $tahapan->warna,
                'warna_text_tahapan'    => $tahapan->warna_text,
                'no_urut_tahapan'       => $tahapan->no_urut,
                'nominal_layanan'       => array_sum(array_column($data_pipeline_layanan, 'harga_total')),
                'jumlah_layanan'        => count(array_column($data_pipeline_layanan, 'id_pipeline_layanan')),
                'id_pelanggan'          => $id_pelanggan,
                'nama_perusahaan'       => $nama_perusahaan,
                'provinsi'              => $provinsi,
                'kota'                  => $kota,
                'kecamatan'             => !empty($kecamatan) ? $kecamatan : "",
                'kelurahan'             => !empty($kelurahan) ? $kelurahan : "",
                'alamat'                => $alamat,
                'nama_jenis_perusahaan' => $nama_jenis_perusahaan ?: '',
                'nama_lead_source'      => $lead_source->nama,
                'title'                 => $title ?: '',
                'id_group_m'            => $sales_group->id_group_m ?: '',
                'nama_group'            => $sales_group ? $sales_group->kode . ' - ' . $sales_group->nama : '',
                'id_company'            => $this->id_company,
                'tgl_incoming'          => date('Y-m-d H:i:s'),
                'tgl_approve'           => date('Y-m-d H:i:s'),
            ];
        }

        $this->model->save($data_pelanggan, $data_pipeline_main, $data_pipeline_main_log, $data_pipeline_layanan, $data_pipeline_trx, $id_pelanggan_update);
    }

    public function delete_pipeline()
    {
        $id_pipeline = $this->input->post('id_pipeline');

        // validation
        if (!$id_pipeline) {
            return __response_delete(false);
        }

        $this->model->delete_pipeline($id_pipeline);
    }

    public function update_pipeline()
    {
        $id       = $this->input->post('id_pipeline');
        $action   = $this->input->post('action');
        $periode  = $this->input->post('periode') ?: $periode = date('Y-m');
        $kategori = $this->input->post('kategori');
        $split    = explode('-', $periode);
        $tahun    = $split[0];
        $bulan    = $split[1];

        if (!$id) {
            return __response_update(false);
        }

        if ($kategori === 'move') {
            // batal pindah bulan
            $this->delete_pindah_bulan($id);
        } else {
            // action batal
            $data = $id;
            // action move
            if ($action === 'move') {
                $move_tahapan = $this->input->post('tahapan_sesudah');
                $tahapan      = $this->model->get_data_tahapan_trx($tahun, $bulan, $move_tahapan)[0];
                $pipeline     = array_values($this->model->get_data_list_kanban('', '', '', '', '', $id))[0][0];

                if ($pipeline['id_tahapan_m'] == $move_tahapan) {
                    return __response_update(true);
                }

                $keterangan = '';
                if (in_array($tahapan->jenis, ['wont', 'lost'])) {
                    $keterangan = $this->input->post('keterangan') ?: '';
                }

                if ($pipeline['status'] == 'pengajuan') {
                    if ($pipeline['tahapan_sebelum_id'] != $move_tahapan) {
                        // replace id tahapan
                        $for  = 'update';
                        $data = [
                            'main' => [
                                'id_tahapan_m' => $move_tahapan,
                                'user_update'  => $this->nik_karyawan . ' - ' . $this->nm_karyawan,
                                'tgl_update'   => date('Y-m-d H:i:s')
                            ],
                            'where_main' => [
                                'id_pipeline_main' => $id
                            ],
                            'log' => [
                                'tahapan_sesudah_id' => $move_tahapan,
                                'tahapan_sesudah_nm' => $tahapan->nama,
                                'keterangan'         => $keterangan,
                            ],
                            'where_log' => [
                                'id_pipeline_main_log' => $pipeline['id_pipeline_main_log']
                            ]
                        ];
                    }
                } else {
                    // for new move stage
                    $for  = 'insert';
                    $data = [
                        'main' => [
                            'id_tahapan_m' => $move_tahapan,
                            'status'       => 'pengajuan',
                            'user_update'  => $this->nik_karyawan . ' - ' . $this->nm_karyawan,
                            'tgl_update'   => date('Y-m-d H:i:s')
                        ],
                        'where_main' => [
                            'id_pipeline_main' => $id
                        ],
                        'log' => [
                            'id_pipeline_main_log' => $this->fungsi->getNextId('sales_pipeline_main_log', 'id_pipeline_main_log', $this->id_company, 5),
                            'id_pipeline_main'     => $id,
                            'tahapan_sebelum_id'   => $pipeline['id_tahapan_m'],
                            'tahapan_sebelum_nm'   => $pipeline['tahapan_sesudah_nm'],
                            'tahapan_sesudah_id'   => $move_tahapan,
                            'tahapan_sesudah_nm'   => $tahapan->nama,
                            'keterangan'           => $keterangan,
                            'id_company'           => $this->id_company,
                            'user_input'           => $this->nik_karyawan . ' - ' . $this->nm_karyawan,
                        ]
                    ];

                    // tanpa approval
                    if ($this->is_sales_skip_approval === true) {
                        $data_existing_trx = $this->model->get_data_existing_trx($this->id_karyawan, $pipeline['tahun'], $pipeline['bulan'], $pipeline['id_pipeline_main'], $tahapan->id_tahapan_m);
                        if (count($data_existing_trx) > 0) {
                            $data_trx = [
                                'for'  => 'update',
                                'data' => [
                                    'jenis_tahapan'      => $tahapan->jenis,
                                    'nama_tahapan'       => $tahapan->nama,
                                    'warna_tahapan'      => $tahapan->warna,
                                    'warna_text_tahapan' => $tahapan->warna_text,
                                    'no_urut_tahapan'    => $tahapan->no_urut,
                                    'nominal_layanan'    => $pipeline['harga'],
                                    'jumlah_layanan'     => $pipeline['layanan'],
                                    'user_approve'       => $this->nik_karyawan . ' - ' . $this->nm_karyawan,
                                    'tgl_approve'        => date('Y-m-d H:i:s'),
                                ],
                                'where_update' => $tahapan->id_tahapan_m,
                                'where_delete' => $tahapan->no_urut,
                            ];
                        } else {
                            $data_trx = [
                                'for'  => 'insert',
                                'data' => [
                                    'id_pipeline_trx'       => $this->fungsi->getNextId('sales_pipeline_trx', 'id_pipeline_trx', $this->id_company, 5),
                                    'id_pipeline_main'      => $pipeline['id_pipeline_main'],
                                    'id_karyawan'           => $this->id_karyawan,
                                    'nik'                   => $this->nik_karyawan,
                                    'nama_karyawan'         => $this->nm_karyawan,
                                    'tahun'                 => $pipeline['tahun'],
                                    'bulan'                 => $pipeline['bulan'],
                                    'id_tahapan'            => $tahapan->id_tahapan_m,
                                    'jenis_tahapan'         => $tahapan->jenis,
                                    'nama_tahapan'          => $tahapan->nama,
                                    'warna_tahapan'         => $tahapan->warna,
                                    'warna_text_tahapan'    => $tahapan->warna_text,
                                    'no_urut_tahapan'       => $tahapan->no_urut,
                                    'nominal_layanan'       => $pipeline['harga'],
                                    'jumlah_layanan'        => $pipeline['layanan'],
                                    'id_pelanggan'          => $pipeline['id_pelanggan'],
                                    'nama_perusahaan'       => $pipeline['nama_perusahaan'],
                                    'provinsi'              => $pipeline['provinsi'],
                                    'kota'                  => $pipeline['kota'],
                                    'kecamatan'             => $pipeline['kecamatan'] ?: '',
                                    'kelurahan'             => $pipeline['kelurahan'] ?: '',
                                    'alamat'                => $pipeline['alamat'],
                                    'nama_jenis_perusahaan' => $pipeline['nama_jenis_perusahaan'] ?: '',
                                    'nama_lead_source'      => $pipeline['nama_lead_source'],
                                    'title'                 => $pipeline['title'] ?: '',
                                    'id_group_m'            => $pipeline['id_group'] ?: '',
                                    'nama_group'            => $pipeline['id_group'] ? $pipeline['kode_group'] . ' - ' . $pipeline['nama_group'] : '',
                                    'id_company'            => $this->id_company,
                                    'tgl_incoming'          => $pipeline['tgl_input'],
                                    'tgl_approve'           => date('Y-m-d H:i:s'),
                                ],
                                'where_delete' => $tahapan->no_urut,
                            ];
                        }

                        if ($tahapan->jenis == 'wont') {
                            $realisasi_sales = $this->model->get_realisasi_sales($this->id_karyawan, $pipeline['tahun'], $pipeline['bulan'], $pipeline['id_group'] ?: '');
                            $target_personil_r = $this->db->get_where('sales_target_personil_r', [
                                'id_company'  => $this->id_company,
                                'id_karyawan' => $this->id_karyawan,
                                'tahun'       => $pipeline['tahun'],
                                'bulan'       => $pipeline['bulan'],
                                'id_group_m'  => $pipeline['id_group'] ?: '',
                            ])->num_rows();
                            if ($target_personil_r > 0) {
                                $data['target_personil_for'] = 'update';
                                $data['target_personil_r'] = [
                                    'nominal_realisasi' => ($realisasi_sales->nominal_realisasi != null) ? ($realisasi_sales->nominal_realisasi + $pipeline['harga']) : $pipeline['harga'],
                                    'jumlah_po'         => $realisasi_sales->jumlah_po + 1,
                                    'user_update'       => $this->nik_karyawan . ' - ' . $this->nm_karyawan,
                                    'tgl_update'        => date('Y-m-d H:i:s'),
                                    'id_group_m'        => $pipeline['id_group'] ?: '',
                                ];
                            } else {
                                $data['target_personil_for'] = 'insert';
                                $target_personil_p = $this->db->get_where('sales_target_personil_p', [
                                    'id_company'  => $this->id_company,
                                    'id_karyawan' => $this->id_karyawan,
                                    'tahun'       => $pipeline['tahun'],
                                    'bulan'       => $pipeline['bulan'],
                                ])->num_rows();
                                if ($target_personil_p == 0) {
                                    $data['target_personil_p'] = [
                                        'id_karyawan' => $this->id_karyawan,
                                        'tahun'       => $pipeline['tahun'],
                                        'bulan'       => $pipeline['bulan'],
                                        'nominal'     => 0,
                                        'id_company'  => $this->id_company,
                                        'user_input'  => $this->nik_karyawan . ' - ' . $this->nm_karyawan,
                                        'tgl_input'   => date('Y-m-d H:i:s'),
                                    ];
                                }
                                $data['target_personil_r'] = [
                                    'id_karyawan'       => $this->id_karyawan,
                                    'nik'               => $this->nik_karyawan,
                                    'nama_karyawan'     => $this->nm_karyawan,
                                    'tahun'             => $pipeline['tahun'],
                                    'bulan'             => $pipeline['bulan'],
                                    'nominal_target'    => 0,
                                    'nominal_realisasi' => ($realisasi_sales->nominal_realisasi != null) ? ($realisasi_sales->nominal_realisasi + $pipeline['harga']) : $pipeline['harga'],
                                    'jumlah_po'         => $realisasi_sales->jumlah_po + 1,
                                    'id_company'        => $this->id_company,
                                    'user_input'        => $this->nik_karyawan . ' - ' . $this->nm_karyawan,
                                    'tgl_input'         => date('Y-m-d H:i:s'),
                                    'id_group_m'        => $pipeline['id_group'] ?: '',
                                    'nama_group'        => $pipeline['id_group'] ? $pipeline['kode_group'] . ' - ' . $pipeline['nama_group'] : '',
                                ];
                            }
                            $data['pelanggan'] = [
                                'update' => [
                                    'jenis'       => '1',
                                    'user_update' => $this->nik_karyawan . ' - ' . $this->nm_karyawan,
                                    'tgl_update'  => date('Y-m-d H:i:s'),
                                ],
                                'where' => [
                                    'id_pelanggan' => $pipeline['id_pelanggan'],
                                ]
                            ];
                            $data['tahapan_jenis'] = $tahapan->jenis;
                        }

                        $data['trx'] = $data_trx;
                        $data['where_trx'] = [
                            'id_company'       => $this->id_company,
                            'id_karyawan'      => $this->id_karyawan,
                            'tahun'            => $pipeline['tahun'],
                            'bulan'            => $pipeline['bulan'],
                            'id_pipeline_main' => $pipeline['id_pipeline_main']
                        ];
                        $data['main']['status'] = 'approve';
                        $data['log']['status']  = 'approve';
                    }
                }
            }

            $this->model->update_pipeline($data, $for);
        }
    }

    public function update_pindah_bulan()
    {
        $id_pipeline = $this->input->post('id');
        $id_karyawan = $this->input->post('sales');

        // validation
        if ($this->level_user === '1' || $this->is_sales_admin === true || $this->is_sales_atasan === true) {
            if (!$id_karyawan) return __response_update(false, ['message' => '<font style="color:Crimson"><i class="fa fa-exclamation-triangle"></i> ' . $this->lang->line('error_pilih_sales_kosong') . '</font>']);
        }
        if (!$id_pipeline) return __response_update(false, ['message' => '<font style="color:Crimson"><i class="fa fa-exclamation-triangle"></i> ' . $this->lang->line('data_tidak_ditemukan') . '</font>']);
        $data_exist = $this->db->get_where('sales_pipeline_main', ['id_company' => $this->id_company, 'id_pindah_bulan' => $id_pipeline])->num_rows();
        if ($data_exist > 0) return __response_duplicate();
        $data_pipeline = $this->db->select('main.status, main.tahun, main.bulan, tahapan.jenis')
            ->from('sales_pipeline_main main')
            ->join('sales_tahapan_trx tahapan', 'main.tahun = tahapan.tahun AND main.bulan = tahapan.bulan AND tahapan.id_tahapan_m = main.id_tahapan_m AND main.id_company = tahapan.id_company')
            ->where('main.id_company', $this->id_company)
            ->where('main.id_pipeline_main', $id_pipeline)
            ->get()
            ->row();
        if ($data_pipeline->status !== 'approve') return __response_update(false, ['message' => '<font style="color:Crimson"><i class="fa fa-exclamation-triangle"></i> ' . $this->lang->line('error_pindah_status_pengajuan') . '</font>']);
        if (in_array($data_pipeline->jenis, ['wont', 'lost'])) return __response_update(false);
        $data_aktivitas = $this->db->get_where(
            'sales_pipeline_activity',
            [
                'id_company'       => $this->id_company,
                'id_pipeline_main' => $id_pipeline,
                'status'           => 'pengajuan'
            ]
        )->num_rows();
        if ($data_aktivitas > 0) return __response_update(false, ['message' => '<font style="color:Crimson"><i class="fa fa-exclamation-triangle"></i> ' . $this->lang->line('error_status_aktivitas_pengajuan') . '</font>']);

        $this->model->update_pindah_bulan($id_pipeline, $data_pipeline->tahun, $data_pipeline->bulan, $id_karyawan);
    }

    public function delete_pindah_bulan($id_pipeline)
    {
        // validation
        if (!$id_pipeline) {
            return __response_update(false);
        }

        $this->model->delete_pipeline($id_pipeline, 'move');
    }

    // for dropdown
    public function list_produk()
    {
        $search = $this->input->get('q');

        $this->db->select('id_product_m, kode, nama, harga')
            ->where('id_company', $this->id_company);

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

        $this->db->order_by('nama', 'ASC');

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

    public function list_pelanggan($kategori = 'all')
    {
        $search = $this->input->get('q');

        $this->db->select('
                sp.id_pelanggan,
                sp.nama_perusahaan,
                sp.alamat,
                jp.id_jenis_perusahaan_m as id_jenis_perusahaan,
                jp.nama as jenis_perusahaan,
                slm.id_lead_m as id_lead_source,
                slm.nama as lead_source,
                mp.id_prov as id_provinsi,
                mp.nama as provinsi,
                mk.id_kota as id_kota,
                mk.nama as kota,
                m_kec.id_kecamatan,
                m_kec.nama AS kecamatan,
                m_kel.id_kelurahan,
                m_kel.nama AS kelurahan,
                sp.pic_nama,
                sp.pic_email,
                sp.pic_telp
            ')
            ->from('sales_pelanggan sp')
            ->join('sales_jenis_perusahaan_m jp', 'jp.id_jenis_perusahaan_m = sp.id_jenis_perusahaan_m', 'left')
            ->join('sales_lead_m slm', 'slm.id_lead_m = sp.id_lead_m')
            ->join('master_provinsi mp', 'mp.id_prov = sp.id_provinsi')
            ->join('master_kota mk', 'mk.id_kota = sp.id_kota')
            ->join('master_kecamatan m_kec', 'm_kec.id_kecamatan = sp.id_kecamatan', 'LEFT')
            ->join('master_kelurahan m_kel', 'm_kel.id_kelurahan = sp.id_kelurahan', 'LEFT')
            ->where('sp.id_company', $this->id_company);

        if ($kategori === 'all') {
            $this->db->where_in('sp.kategori_pelanggan', ['perusahaan', 'perorangan']);
        } else {
            $this->db->where('sp.kategori_pelanggan', $kategori);
        }
        if ($search != '') {
            $this->db->like('sp.nama_perusahaan', $search);
        }
        $this->db->order_by('sp.nama_perusahaan', 'ASC');

        $result = $this->db->get()->result();
        $json = [];
        foreach ($result as $rows) {
            $json[] = ['id' => $rows->id_pelanggan, 'text' => $rows->nama_perusahaan, 'perusahaan' => [
                'nama_perusahaan'     => $rows->nama_perusahaan,
                'alamat'              => $rows->alamat,
                'id_jenis_perusahaan' => $rows->id_jenis_perusahaan,
                'jenis_perusahaan'    => $rows->jenis_perusahaan,
                'id_lead_source'      => $rows->id_lead_source,
                'lead_source'         => $rows->lead_source,
                'id_provinsi'         => $rows->id_provinsi,
                'provinsi'            => $rows->provinsi,
                'id_kota'             => $rows->id_kota,
                'kota'                => $rows->kota,
                'id_kecamatan'        => $rows->id_kecamatan,
                'kecamatan'           => $rows->kecamatan,
                'id_kelurahan'        => $rows->id_kelurahan,
                'kelurahan'           => $rows->kelurahan,
                'pic_nama'            => $rows->pic_nama,
                'pic_email'           => $rows->pic_email,
                'pic_telp'            => $rows->pic_telp,
            ]];
        }
        echo json_encode($json);
    }

    public function list_jenis_perusahaan()
    {
        $search = $this->input->get('q');
        $flag_jenis = $this->input->get('flag_jenis');

        $this->db->select('id_jenis_perusahaan_m, nama')
            ->where('id_company', $this->id_company);

        if ($flag_jenis == "perorangan") {
            $this->db->where('flag_jenis', "2");
        } else {
            $this->db->where('flag_jenis', "1");
        }

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

        $this->db->order_by('nama', 'ASC');

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

    public function list_lead_source()
    {
        $search = $this->input->get('q');

        $this->db->select('id_lead_m, nama')
            ->where('id_company', $this->id_company);

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

        $this->db->order_by('no_urut', 'ASC');

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

    public function list_group()
    {
        $search = $this->input->get('search');

        $this->db->select('
                id_group_m,
                kode,
                nama
            ')
            ->from('sales_group_m')
            ->where('id_company', $this->id_company);
        if ($this->level_user !== '1' && $this->is_sales_admin === true) {
            if (!in_array($this->sales_admin_group, ["all", ""])) {
                $group_ids = explode(',', $this->sales_admin_group);
                $this->db->group_start()
                    ->where_in('id_group_m', $group_ids)
                    ->or_where('id_group_m', '')
                    ->group_end();
            }
        }

        if ($search != '') {
            $this->db->group_start();
            $this->db->like('nama', $search);
            $this->db->or_like('kode', $search);
            $this->db->group_end();
        }
        $this->db->order_by('nama', 'ASC');
        $result = $this->db->get()->result();

        $json = [];
        foreach ($result as $rows) {
            $json[] = ['id' => $rows->id_group_m, 'text' => $rows->kode . ' - ' . $rows->nama];
        }
        echo json_encode($json);
    }

    public function list_sales()
    {
        $search = $this->input->get('search');
        $topic  = $this->input->get('topic');
        $range  = $this->input->get('range');
        $start  = $this->input->get('start');
        $end    = $this->input->get('end');

        if ($range == '1') {
            $start = $start ? date('Y-m-d', strtotime($start)) : date('Y-m-d');
            $end   = $end ? date('Y-m-d', strtotime($end)) : date('Y-m-d');
        } else {
            $start ?: $start = date('Y-m');
            list($year, $month) = explode('-', $start);
        }

        if ($this->is_sales_admin === false && $this->level_user !== '1') {
            if ($this->is_sales_atasan === true) {
                $bawahan = $this->db->select('id_karyawan')
                    ->from('sales_approver')
                    ->where('id_company', $this->id_company)
                    ->where('id_atasan', $this->id_karyawan)
                    ->get()
                    ->result_array();

                $list = array_column($bawahan, 'id_karyawan');
                if ($this->is_sales === true) {
                    $list[] = $this->id_karyawan;
                }

                $this->db->where('sa.id_company', $this->id_company);
                $this->db->where('sa.status', '1');
                $this->db->where_in('sa.id_karyawan', $list);
            }
        } else {
            $this->db->where('sa.id_company', $this->id_company);
            $this->db->where('sa.status', '1');
        }

        // gabungkan dengan halaman yang di load berdasarkan tabel
        $sales_approver = $this->db->select('sa.id_karyawan')
            ->from('sales_approver sa')
            ->get_compiled_select();

        if ($topic === 'kanban') {
            $with_table = $this->db->select('main.id_karyawan')
                ->from('sales_pipeline_main main')
                ->where('main.id_company', $this->id_company)
                ->where('main.tahun', $year)
                ->where('main.bulan', $month)
                ->group_by('main.id_karyawan')
                ->get_compiled_select();
        }

        if ($topic === 'log_activity') {
            $table_log = $this->db->select('main.id_karyawan')
                ->from('sales_pipeline_main_log log')
                ->join('sales_pipeline_main main', 'log.id_pipeline_main = main.id_pipeline_main')
                ->where('log.id_company', $this->id_company)
                ->group_start()
                ->where("DATE(`log`.`tgl_input`) BETWEEN '$start' AND '$end'")
                ->group_end()
                ->group_by('main.id_karyawan')
                ->get_compiled_select();

            $table_activity = $this->db->select('activity.id_karyawan')
                ->from('sales_pipeline_activity activity')
                ->where('activity.id_company', $this->id_company)
                ->group_start()
                ->where("DATE(`activity`.`tgl_activity`) BETWEEN '$start' AND '$end'")
                ->group_end()
                ->group_by('activity.id_karyawan')
                ->get_compiled_select();

            $with_table = $this->db->from("($table_log UNION $table_activity) as log_activity")
                ->get_compiled_select();
        }

        $this->db->select('
                    u.id_karyawan,
                    dk.nama_lengkap
                ')
            ->from("($sales_approver UNION $with_table) as u")
            ->join('data_karyawan dk', 'dk.id_karyawan = u.id_karyawan');

        if ($search != '') {
            $this->db->like('dk.nama_lengkap', $search);
        }
        $this->db->order_by('dk.nama_lengkap', 'ASC');
        $result = $this->db->get()->result();

        $json = [];
        foreach ($result as $rows) {
            $json[] = ['id' => $rows->id_karyawan, 'text' => $rows->nama_lengkap];
        }
        echo json_encode($json);
    }

    public function list_activity()
    {
        $search = $this->input->get('q');

        $this->db->select('
                id_activity_m,
                nama,
                poin
            ')
            ->from('sales_activity_m')
            ->where('id_company', $this->id_company)
            ->where('flag_lokasi', '0')
            ->where('flag_upload_foto', '0');
        if ($search != '') {
            $this->db->like('nama', $search);
        }
        $this->db->order_by('no_urut', 'ASC');

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