<?php 
class M_pka_ev_tri extends CI_Model{	
    function __construct() {
        parent::__construct();
    }
    function kode_user(){
        return $this->session->userdata("kode_user");
    }
    function unit_misioner(){
        return $this->session->userdata("unit_misioner");
    }


    function data_ev_pka_tri($no_pka){
        $sql = "SELECT p.kma,jk.nama nm_jk, um.nama nm_um , bd.nama nm_bd,p.nama_kegiatan,p.tujuan_kegiatan,p.ev_tri
            FROM tb_pka p
            LEFT JOIN tb_user u ON u.kode_user = p.created_by
            LEFT JOIN tb_jenis_kegiatan jk ON jk.id_jenis_kegiatan = p.jenis_kegiatan
            LEFT JOIN tb_bidang bd ON bd.id_bidang = p.bidang
            LEFT JOIN tb_unit_misioner um ON um.id_unit_misioner = p.unit_misioner
            WHERE p.no_pka='$no_pka' AND p.is_deleted=0";

        return $this->db->query($sql);
    }
    function data_ev_pka_tri_pka($periode_thn){
        $unit_misioner = $this->unit_misioner();
        if ($periode_thn == "all") {
            $sql = "SELECT Distinct p.no_pka,p.kode_pka,p.periode_thn,p.acc,p.created_at,p.status_ev_tri,u.username,u.email,um.nama nm_um,p.evaluasi,count(p.kode_pka) jml FROM tb_pka p
                LEFT JOIN tb_user u ON u.kode_user = p.created_by
                LEFT JOIN tb_unit_misioner um ON um.id_unit_misioner = p.unit_misioner 
                WHERE  p.is_deleted =0 AND p.unit_misioner = '$unit_misioner'
                GROUP BY p.no_pka";

        }else{
            $sql = "SELECT Distinct p.no_pka,p.kode_pka,p.periode_thn,p.acc,p.created_at,p.status_ev_tri,u.username,u.email,um.nama nm_um,p.evaluasi,count(p.kode_pka) jml FROM tb_pka p
                LEFT JOIN tb_user u ON u.kode_user = p.created_by
                LEFT JOIN tb_unit_misioner um ON um.id_unit_misioner = p.unit_misioner 
                WHERE  p.is_deleted =0 AND p.unit_misioner = '$unit_misioner' AND p.periode_thn ='".str_replace("-","/",$periode_thn)."'
                GROUP BY p.no_pka";
        }
        
        return $this->db->query($sql);
    }
    function data_ev_pka_tri_pka_adm($periode_thn){
        $unit_misioner = $this->unit_misioner();
        if ($periode_thn == "all") {
            $sql = "SELECT Distinct p.no_pka,p.kode_pka,p.periode_thn,p.acc,p.created_at,p.status_ev_tri,u.username,u.email,um.nama nm_um,p.evaluasi,count(p.kode_pka) jml FROM tb_pka p
                LEFT JOIN tb_user u ON u.kode_user = p.created_by
                LEFT JOIN tb_unit_misioner um ON um.id_unit_misioner = p.unit_misioner 
                WHERE  p.is_deleted =0
                GROUP BY p.no_pka";

        }else{
            $sql = "SELECT Distinct p.no_pka,p.kode_pka,p.periode_thn,p.acc,p.created_at,p.status_ev_tri,u.username,u.email,um.nama nm_um,p.evaluasi,count(p.kode_pka) jml FROM tb_pka p
                LEFT JOIN tb_user u ON u.kode_user = p.created_by
                LEFT JOIN tb_unit_misioner um ON um.id_unit_misioner = p.unit_misioner 
                WHERE  p.is_deleted =0 AND p.periode_thn ='".str_replace("-","/",$periode_thn)."'
                GROUP BY p.no_pka";
        }
        
        return $this->db->query($sql);
    }
    function add_sesi_ev_pka_tri($data){
        $kode_user = $this->kode_user();
        $sql = 
        "INSERT INTO `tb_sesi_ev_pka_tri`(
            `kode_sesi_ev_pka_tri`, `periode_thn`, `triwulan`, `dari`, `sampai`, `keterangan`, `publish`, `updated_at`, `updated_by`, `is_deleted`) 
        VALUES (
            '$data[kode_sesi_ev_pka_tri]','$data[periode_thn]','$data[triwulan]','$data[dari]','$data[sampai]','$data[keterangan]','0',NOW(),'$kode_user',0
        )";
        return $this->db->query($sql);
        // return $sql;
    }
    function get_sesi_ev_tri(){
        $sql = 
        "SELECT * FROM tb_sesi_ev_pka_tri";
        return $this->db->query($sql);
        // return $sql;
    }
    function update_sesi_ev_pka_tri($data){
        $kode_user = $this->kode_user();
        $sql = 
        "UPDATE `tb_sesi_ev_pka_tri` 
        SET 
            `periode_thn`='$data[periode_thn]',
            `triwulan`='$data[triwulan]',
            `dari`='$data[dari]',
            `sampai`='$data[sampai]',
            `keterangan`='$data[keterangan]',
            `updated_at`='NOW()',
            `updated_by`='$kode_user'";
        return $this->db->query($sql);
        // return $sql;
    }
    function data_sesi_ev_pka_tri(){
        $sql = "SELECT * FROM `tb_sesi_ev_pka_tri` limit 1";
        return $this->db->query($sql);
    }
    function publish_sesi_ev_pka_tri($data){
        $kode_user = $this->kode_user();
        $sql = "UPDATE `tb_sesi_ev_pka_tri` SET `publish`=1";
        return $this->db->query($sql);
    }
    function ambil_sesi_triwulan(){
        $sql = "SELECT triwulan FROM `tb_sesi_ev_pka_tri` LIMIT 1";
        return $this->db->query($sql);
    }
    function add_ev_tri($data){
        $kode_user = $this->kode_user();
        $sql = "INSERT INTO `tb_ev_tri`(
            `kma`, `triwulan_evaluasi`, `bulan_kegiatan`, `pencapaian`, `atp`, `realisasi_kualitatif`, `realisasi_penerimaan`, `realisasi_pengeluaran`, `analisa_keuangan`, `terlaksana`, `created_at`, `created_by`, `updated_at`, `updated_by`, `is_deleted`) 
        VALUES (
            '$data[kma]','$data[triwulan_evaluasi]','$data[bulan_kegiatan]','$data[pencapaian]','$data[atp]','$data[realisasi_kualitatif]','$data[realisasi_penerimaan]','$data[realisasi_pengeluaran]','$data[analisa_keuangan]','$data[terlaksana]',NOW(),'$kode_user',NOW(),'$kode_user',0
        )";
        return $this->db->query($sql);
    }
    function update_ev_tri_pka($kma,$status=1){
        $kode_user = $this->kode_user();
        $sql = "UPDATE `tb_pka` SET `ev_tri`=$status WHERE `kma` = '$kma'";
        return $this->db->query($sql);
    }
    function row_ev_tri($kma){
        $sql = "SELECT * FROM `tb_ev_tri` WHERE `kma` = '$kma'";
        return $this->db->query($sql);
    }
    function update_ev_tri($data){
        $kode_user = $this->kode_user();
        $sql = 
        "UPDATE `tb_ev_tri` SET 
            `bulan_kegiatan`='$data[bulan_kegiatan]',
            `pencapaian`='$data[pencapaian]',
            `atp`='$data[atp]',
            `realisasi_kualitatif`='$data[realisasi_kualitatif]',
            `realisasi_penerimaan`='$data[realisasi_penerimaan]',
            `realisasi_pengeluaran`='$data[realisasi_pengeluaran]',
            `analisa_keuangan`='$data[analisa_keuangan]',
            `terlaksana`='$data[terlaksana]',
            `status`='0',
            `updated_at`=NOW(),
            `updated_by`='$kode_user'
        WHERE `kma` = '$data[kma]'";
        return $this->db->query($sql);
    }
    function data_ev_pka_tri_adm($no_pka){
            $sql = "SELECT p.kma,jk.nama nm_jk, um.nama nm_um , bd.nama nm_bd,p.nama_kegiatan,p.tujuan_kegiatan,p.ev_tri
            FROM tb_pka p
            LEFT JOIN tb_user u ON u.kode_user = p.created_by
            LEFT JOIN tb_jenis_kegiatan jk ON jk.id_jenis_kegiatan = p.jenis_kegiatan
            LEFT JOIN tb_bidang bd ON bd.id_bidang = p.bidang
            LEFT JOIN tb_unit_misioner um ON um.id_unit_misioner = p.unit_misioner
            WHERE p.no_pka='$no_pka' AND p.is_deleted=0";
       
        return $this->db->query($sql);
    }
    function approve_ev_tri($no_pka){
        $kode_user = $this->kode_user();
        $sql = 
        "UPDATE `tb_pka` SET 
            `status_ev_tri`='1'
        WHERE `no_pka` = '$no_pka'";
        return $this->db->query($sql);
    }
    function reject_ev_tri($no_pka){
        $kode_user = $this->kode_user();
        $sql = 
        "UPDATE `tb_pka` SET 
            `status_ev_tri`='3'
        WHERE `no_pka` = '$no_pka'";
        return $this->db->query($sql);
    }
    function ajukan_ev_tri($no_pka){
        $kode_user = $this->kode_user();
        $sql = 
        "UPDATE `tb_pka` SET 
            `status_ev_tri`='2'
        WHERE `no_pka` = '$no_pka'";
        return $this->db->query($sql);
    }
}