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

class Kanban_detail_m extends CI_Model
{

    /**
     * update data perusahaan/perorangan
     *
     * @param string|array $data_pelanggan
     * @param array $data_pipeline_main
     * @param string|int $id_pipeline_main
     * @return void
     */
    public function update_perusahaan($data_pelanggan, $data_pipeline_main, $id_pipeline_main, $id_pelanggan_update)
    {
        $this->db->trans_begin();

        // insert data pelanggan
        if (!empty($id_pelanggan_update)) {
            // Action Update
            $data_pipeline_main['id_pelanggan'] = $id_pelanggan_update;
            
            $this->db->where('id_company', $this->id_company);
            $this->db->where('id_pelanggan', $id_pelanggan_update);
            $this->db->update('sales_pelanggan', $data_pelanggan);
        } else {
            // Action Insert
            $data_pipeline_main['id_pelanggan'] = $data_pelanggan['id_pelanggan'];
            $this->db->insert('sales_pelanggan', $data_pelanggan);
        }

        // update data pipeline main
        $this->db->update('sales_pipeline_main', $data_pipeline_main, ['id_pipeline_main' => $id_pipeline_main, 'id_company' => $this->id_company]);

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

    /**
     * For add new layanan
     *
     * @param mixed $data => data yang akan diupdate
     * @param mixed $status
     * @param mixed $jenis_tahapan
     * @return void
     */
    public function save_layanan($data, $status, $jenis_tahapan)
    {
        $this->db->trans_begin();

        $this->db->insert('sales_pipeline_layanan', $data['pipeline_layanan']);
        if ($status == 'approve') {
            $new_nominal = $data['pipeline_trx']['update'];
            $this->db->set('`nominal_layanan`', "`nominal_layanan` + $new_nominal", FALSE);
            $this->db->set('`jumlah_layanan`', "(`jumlah_layanan` + 1)", FALSE);
            $this->db->where($data['pipeline_trx']['condition']);
            $this->db->update('sales_pipeline_trx');

            if ($jenis_tahapan == 'wont') {
                $new_nominal = $data['personil_r']['nominal'];
                $this->db->set('`nominal_realisasi`', "`nominal_realisasi` + $new_nominal", FALSE);
                $this->db->update('sales_target_personil_r', $data['personil_r']['update'], $data['personil_r']['condition']);
            }
        }

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

    /**
     * For update layanan
     *
     * @param mixed $data => data yang akan diupdate
     * @param mixed $status
     * @param mixed $jenis_tahapan
     * @return void
     */
    public function update_layanan($data, $status, $jenis_tahapan)
    {
        $this->db->trans_begin();

        $this->db->update('sales_pipeline_layanan', $data['pipeline_layanan']['update'], $data['pipeline_layanan']['condition']);
        if ($status == 'approve') {
            $this->db->update('sales_pipeline_trx', $data['pipeline_trx']['update'], $data['pipeline_trx']['condition']);
            if ($jenis_tahapan == 'wont') {
                $new_nominal = $data['personil_r']['nominal'];
                $this->db->set('`nominal_realisasi`', "`nominal_realisasi` - $new_nominal", FALSE);
                $this->db->update('sales_target_personil_r', $data['personil_r']['update'], $data['personil_r']['condition']);
            }
        }

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

    /**
     * For delete layanan
     *
     * @param mixed $data => data yang akan diupdate
     * @param mixed $status
     * @param mixed $jenis_tahapan
     * @return void
     */
    public function delete_layanan($data, $status, $jenis_tahapan)
    {
        $this->db->trans_begin();

        $this->db->delete('sales_pipeline_layanan', ['id_pipeline_layanan' => $data['pipeline_layanan']]);
        if ($status == 'approve') {
            $new_nominal = $data['pipeline_trx']['update'];
            $this->db->set('`nominal_layanan`', "`nominal_layanan` - $new_nominal", FALSE);
            $this->db->set('`jumlah_layanan`', "(`jumlah_layanan` - 1)", FALSE);
            $this->db->where($data['pipeline_trx']['condition']);
            $this->db->update('sales_pipeline_trx');

            if ($jenis_tahapan == 'wont') {
                $new_nominal = $data['personil_r']['nominal'];
                $this->db->set('`nominal_realisasi`', "`nominal_realisasi` - $new_nominal", FALSE);
                $this->db->update('sales_target_personil_r', $data['personil_r']['update'], $data['personil_r']['condition']);
            }
        }

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

    /**
     * Get data jumlah dan nominal layanan
     *
     * @param string|int $id_pipeline_main
     * @param string|int $id_layanan => id_layanan untuk single select
     * @param string|int $exclude_layanan => id_layanan tidak ikut dihitung
     * @return mixed
     */
    public function get_ttl_layanan($id_pipeline_main, $id_layanan = '', $exclude_layanan = '')
    {
        if ($id_layanan != '') {
            $this->db->where('id_pipeline_layanan', $id_layanan);
        } else {
            if ($exclude_layanan != '') {
                $this->db->where("id_pipeline_layanan != '$exclude_layanan'");
            }
        }
        return $this->db->select('
                COUNT(*) as jumlah_layanan,
                SUM(harga_total) as nominal_layanan
            ')
            ->from('sales_pipeline_layanan')
            ->where('id_pipeline_main', $id_pipeline_main)
            ->where('id_company', $this->id_company)
            ->group_by('id_pipeline_main')
            ->get()
            ->row();
    }

    public function get_detail_layanan($id, $id_layanan = '')
    {
        if ($id_layanan != '') {
            $this->db->where('layanan.id_pipeline_layanan', $id_layanan);
        } else {
            $this->db->where('layanan.id_pipeline_main', $id);
        }

        return $this->db->select('
                layanan.id_pipeline_layanan,
                layanan.id_pipeline_main,
                layanan.id_product_m,
                layanan.harga,
                layanan.jumlah,
                layanan.diskon,
                layanan.harga_total,
                product.harga as harga_product,
                product.nama as nama_product
            ')
            ->from('sales_pipeline_layanan layanan')
            ->join('sales_product_m product', 'product.id_product_m = layanan.id_product_m')
            ->where('layanan.id_company', $this->id_company)
            ->get();
    }

    public function get_detail_aktivitas($id)
    {
        // jika ingin melihat aktivitas dari pelanggan
        // $id_pelanggan = $this->db->select('id_pelanggan')
        //     ->from('sales_pipeline_main')
        //     ->where('id_pipeline_main', $id)
        //     ->where('id_company', $this->id_company)
        //     ->get()
        //     ->row()
        //     ->id_pelanggan;

        $log = $this->db->select('
                "log" as tabel,
                log.status,
                log.tahapan_sebelum_nm as tahapan_sebelum,
                log.tahapan_sesudah_nm as tahapan_sekarang,
                "" as kategori,
                IF(log.tahapan_sebelum_id = "", "New", "Move") as aktivitas,
                "" as 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')
            ->where('log.id_company', $this->id_company)
            ->where('main.id_pipeline_main', $id)
            ->get_compiled_select();

        $activity = $this->db->select('
                "activity" as tabel,
                activity.status,
                "" as tahapan_sebelum,
                "" as tahapan_sekarang,
                log.tahapan_sesudah_nm as kategori,
                activity.jenis_activity as aktivitas,
                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_log log', 'log.id_pipeline_main_log = activity.id_pipeline_main_log')
            ->where('activity.id_company', $this->id_company)
            ->where('activity.id_pipeline_main', $id)
            ->get_compiled_select();

        return $this->db->select('
                tabel,
                status,
                tahapan_sebelum,
                tahapan_sekarang,
                kategori,
                aktivitas,
                keterangan,
                waktu,
                user,
                user_id
            ')
            ->from('(' . $log . ' UNION ALL ' . $activity . ') as aktivitas')
            ->order_by('waktu', 'asc')
            ->get()
            ->result();
    }

    public function preview_aktivitas($id, $po_or_fail = "")
    {   
        if($po_or_fail == ""){
            $split       = explode('_', $id);
            $id_karyawan = $split[0];
            $tgl         = $split[1];
            $jam         = str_replace('-', ':', $split[2]);
    
            return $this->db->select('
                    id_karyawan,
                    status,
                    tgl_activity as tgl,
                    jam_activity as jam,
                    jenis_activity as aktivitas,
                    keterangan,
                    flag_lokasi,
                    flag_upload_foto,
                    lat,
                    long,
                    path_foto,
                    user_input as user
                ')
                ->from('sales_pipeline_activity')
                ->where('id_company', $this->id_company)
                ->where('id_karyawan', $id_karyawan)
                ->where('tgl_activity', $tgl)
                ->where('jam_activity', $jam)
                ->get()
                ->row();

        }else{
            return $this->db->select('
                main.id_karyawan,
                "approve" AS status,
                DATE_FORMAT(main.tgl_update, "%Y-%m-%d") AS tgl,
                DATE_FORMAT(main.tgl_update, "%H:%i") AS jam,
                "'.$po_or_fail.'" AS aktivitas,
                log.keterangan,
                log.user_input AS user
            ')
            ->from('sales_pipeline_main_log AS log')
            ->join('sales_pipeline_main AS main', 'main.id_pipeline_main = log.id_pipeline_main')
            ->where('log.id_pipeline_main_log', $id)
            ->get()
            ->row();
        }
    }

    public function save_aktivitas($data)
    {
        $this->db->trans_begin();
        $this->db->insert('sales_pipeline_activity', $data);
        if ($this->db->trans_status() === FALSE) {
            $this->db->trans_rollback();
            return __response_save(false);
        } else {
            $this->db->trans_commit();
            return __response_save(true);
        }
    }

    public function update_aktivitas($data, $id)
    {
        $split       = explode('_', $id);
        $id_karyawan = $split[0];
        $tgl         = $split[1];
        $jam         = str_replace('-', ':', $split[2]);

        $this->db->trans_begin();
        $this->db->update('sales_pipeline_activity', $data, [
            'id_company'   => $this->id_company,
            'id_karyawan'  => $id_karyawan,
            'tgl_activity' => $tgl,
            'jam_activity' => $jam,
        ]);
        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 get_detail_pipeline($id)
    {
        return $this->db->select('
                main.id_pipeline_main,
                main.id_karyawan,
                main.tahun,
                main.bulan,
                main.id_tahapan_m,
                main.id_pelanggan,
                main.status,
                main.title,
                main.keterangan,
                main.user_input,
                main.tgl_update,
                sales.nama_lengkap as sales_nama,
                sales.email as sales_email,
                sales.telepon as sales_telp,
                pelanggan.kategori_pelanggan,
                pelanggan.nama_perusahaan,
                pelanggan.alamat,
                pelanggan.id_jenis_perusahaan_m as id_jenis_perusahaan,
                j_perusahaan.nama as jenis_perusahaan,
                main.id_lead_m as id_lead_source,
                main.nama_lead_source as lead_source,
                prov.id_prov as id_provinsi,
                prov.nama as provinsi,
                kota.id_kota as id_kota,
                kota.nama as kota,
                m_kec.id_kecamatan,
                m_kec.nama AS kecamatan,
                m_kel.id_kelurahan,
                m_kel.nama AS kelurahan,
                main.pic_nama,
                main.pic_email,
                main.pic_telp,
                tahapan.nama as tahapan,
                tahapan.jenis,
                tahapan.warna,
                tahapan.warna_text
            ')
            ->from('sales_pipeline_main main')
            ->join('data_karyawan as sales', 'sales.id_karyawan = main.id_karyawan', 'left')
            ->join('sales_tahapan_trx tahapan', 'tahapan.id_tahapan_m = main.id_tahapan_m and tahapan.tahun = main.tahun and tahapan.bulan = main.bulan and tahapan.id_company = main.id_company')
            ->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 m_kec', 'm_kec.id_kecamatan = pelanggan.id_kecamatan', 'LEFT')
            ->join('master_kelurahan m_kel', 'm_kel.id_kelurahan = pelanggan.id_kelurahan', 'LEFT')
            ->where('main.id_company', $this->id_company)
            ->where('main.id_pipeline_main', $id)
            ->get()
            ->row();
    }

    public function get_status_pipeline($id)
    {
        return $this->db->select('
                id_pipeline_main,
                status,
                jenis,
                trx.nama AS nama_tahapan,
                main.tgl_update
            ')
            ->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')
            ->where('main.id_pipeline_main', $id)
            ->get()
            ->row();
    }

    public function get_keterangan_wont_lost($id_pipeline_main="", $nama_tahapan=""){
        return $this->db->select('
                log.id_pipeline_main_log,
                log.id_pipeline_main,
                log.tahapan_sesudah_nm AS nama_tahapan,
                log.keterangan,
                main.tgl_update,
            ')
            ->from('sales_pipeline_main_log AS log')
            ->join('sales_pipeline_main AS main', 'main.id_pipeline_main = log.id_pipeline_main')
            ->where('log.id_pipeline_main', $id_pipeline_main)
            ->where('log.tahapan_sesudah_nm', $nama_tahapan)
            ->get()
            ->row();
    }
}
