<?php 


class M_pka extends CI_Model{	


    


    function __construct() {


        parent::__construct();


    }


    


    function kode_user(){


        return $this->session->userdata("kode_user");


    }


    function data_pka(){


        $kode_user = $this->kode_user();


        $sql = "SELECT Distinct p.no_pka,p.kode_pka,p.periode_thn,p.acc,p.created_at,u.username,u.email,um.nama nm_um,p.evaluasi 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.created_by = '$kode_user'


                GROUP BY p.no_pka";


        return $this->db->query($sql);


    }





    function insert_pka($data){


        $kode_user = $this->kode_user();


        $sql = "INSERT INTO `tb_pka`(


                    `no_pka`,


                    `kode_pka`,


                    `periode_thn`,


                    `kma`, 


                    `unit_misioner`, 


                    `jenis_kegiatan`, 


                    `bidang`, 


                    `nama_kegiatan`, 


                    `nomor_strategi`, 


                    `tujuan_kegiatan`, 


                    `lokasi_pp`, 


                    `pendukung_program`, 


                    `pen_jawab_program`, 


                    `w_awal_pp`, 


                    `w_akhir_pp`, 


                    `indikator_kuantitatif`, 


                    `indikator_kualitatif`, 


                    `prioritas_kegiatan`, 


                    `triwulan`, 


                    `keterangan`, 


                    `created_at`, 


                    `created_by`


                ) VALUES (


                    '$data[no_pka]',


                    '$data[kode_pka]',


                    '$data[periode_thn]',


                    '$data[kma]',


                    '$data[unit_misioner]',


                    '$data[jenis_kegiatan]',


                    '$data[bidang]',


                    '$data[nama_kegiatan]',


                    '$data[nomor_strategi]',


                    '$data[tujuan_kegiatan]',


                    '$data[lokasi_pp]',


                    '$data[pendukung_program]',


                    '$data[pen_jawab_program]',


                    '$data[w_awal_pp]',


                    '$data[w_akhir_pp]',


                    '$data[indikator_kuantitatif]',


                    '$data[indikator_kualitatif]',


                    '$data[prioritas_kegiatan]',


                    '$data[triwulan]',


                    '$data[keterangan]',


                    NOW(),


                    '$kode_user'


                    )";


        return $this->db->query($sql);


    }


    function insert_pka_pengeluaran($data){


        $sql = "INSERT INTO `tb_pka_pengeluaran`(


                    `kode_pka`, 


                    `jenis_pengeluaran`, 


                    `jumlah_satuan`, 


                    `bulanan`, 


                    `tahunan`, 


                    `nilai_satuan`, 


                    `pengeluaran`


                ) VALUES (


                    '$data[kode_pka]',


                    '$data[jenis_pengeluaran]',


                    '$data[jumlah_satuan]',


                    '$data[bulanan]',


                    '$data[tahunan]',


                    '$data[nilai_satuan]',


                    '$data[pengeluaran]'


                )";


        return $this->db->query($sql);


    }


    function insert_pka_penerimaan($data){


        $sql = "INSERT INTO `tb_pka_penerimaan`(


                    `kode_pka`, 


                    `jenis_penerimaan`, 


                    `jumlah_satuan`, 


                    `bulanan`, 


                    `tahunan`, 


                    `nilai_satuan`, 


                    `penerimaan`


                ) VALUES (


                    '$data[kode_pka]',


                    '$data[jenis_penerimaan]',


                    '$data[jumlah_satuan]',


                    '$data[bulanan]',


                    '$data[tahunan]',


                    '$data[nilai_satuan]',


                    '$data[penerimaan]'


                )";


        return $this->db->query($sql);


    }


    function data_refisi_pka(){


        $kode_user = $this->kode_user();


        $sql = "SELECT p.no_pka,p.kode_pka,p.no_pka,p.periode_thn,p.acc,p.created_at,u.username,u.email,count(p.kode_pka) jml,um.nama nm_um 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.created_by = '$kode_user' AND p.acc = 2


                GROUP BY p.no_pka";


        


        return $this->db->query($sql);


    }


    function delete_refisi_pka($no_pka){


        $sql = "DELETE FROM `tb_pka` WHERE no_pka = '$no_pka'";


        return $this->db->query($sql);


    }


    function delete_refisi_pka_pengeluaran($kode_pka){


        $sql = "DELETE FROM `tb_pka_pengeluaran` WHERE kode_pka = '$kode_pka'";


        return $this->db->query($sql);


    }


    function delete_refisi_pka_penerimaan($kode_pka){


        $sql = "DELETE FROM `tb_pka_penerimaan` WHERE kode_pka = '$kode_pka'";


        return $this->db->query($sql);


    }


    function data_evaluasi_pka(){


        $kode_user = $this->kode_user();


        $sql = "SELECT p.no_pka,p.kode_pka,p.no_pka,p.periode_thn,p.acc,p.evaluasi,p.created_at,u.username,u.email,count(p.kode_pka) jml,um.nama nm_um 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.created_by = '$kode_user' AND p.evaluasi = 2


                GROUP BY p.no_pka


                ORDER BY p.created_at DESC";


        return $this->db->query($sql);


    }


    function ambil_data_pka_evaluasi($data){


        $sql = "SELECT pka.*,pka.created_at dibuat,u.username,um.nama um FROM tb_pka pka


                LEFT JOIN tb_user as u ON pka.created_by = u.kode_user 


                LEFT JOIN tb_unit_misioner as um ON um.id_unit_misioner = u.unit_misioner 


                WHERE pka.no_pka = '$data[no_pka]'";


        return $this->db->query($sql);


    }


    function ambil_data_pka_evaluasi_pengeluaran($kode_pka){


        $sql = "SELECT * FROM tb_pka_pengeluaran WHERE kode_pka = '$kode_pka'";


        return $this->db->query($sql);


    }


    function ambil_data_pka_evaluasi_penerimaan($kode_pka){


        $sql = "SELECT * FROM tb_pka_penerimaan WHERE kode_pka = '$kode_pka'";


        return $this->db->query($sql);


    }


    





    function insert_evaluasi_pka($data){


        $kode_user = $this->kode_user();


        $sql = "INSERT INTO `tb_evaluasi`(


                                `no_pka`,


                                `kode_pka`,


                                `pencapaian`, 


                                `atp`, 


                                `target_kehadiran`,


                                `realisasi_kehadiran`, 


                                `realisasi_penerimaan`, 


                                `realisasi_pengeluaran`, 


                                `epsk`, 


                                `created_at`, 


                                `created_by`) 


                VALUES (


                                '$data[no_pka]',


                                '$data[kode_pka]',


                                '$data[pencapaian]',


                                '$data[atp]',


                                '$data[target_kehadiran]',


                                '$data[realisasi_kehadiran]',


                                '$data[realisasi_penerimaan]',


                                '$data[realisasi_pengeluaran]',


                                '$data[epsk]',


                                NOW(),


                                '$kode_user')";


        return $this->db->query($sql);


    }


    function update_evaluasi_pka($data){


        $sql = "UPDATE `tb_pka` SET `evaluasi`= 1 WHERE no_pka = '$data[no_pka]'";


        return $this->db->query($sql);


    }


    function ambil_detail_pka_evaluasi($kode_pka){


        $sql = "SELECT * FROM tb_evaluasi WHERE kode_pka = '$kode_pka'";


        return $this->db->query($sql);


    }


    function ambil_detail_pka($kode_pka){


        $kode_user = $this->kode_user();


        $sql = "SELECT p.no_pka,p.kode_pka,p.no_pka,p.periode_thn,p.acc,p.evaluasi,p.created_at,u.username,u.email,count(p.kode_pka) jml,um.nama nm_um 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.created_by = '$kode_user' AND p.kode_pka = '$kode_pka'


                GROUP BY p.no_pka


                ORDER BY p.created_at DESC";


        return $this->db->query($sql);


    }


    function data_rev_ev_pka(){


        $sql = "SELECT e.*,count(e.kode_pka) jml_page,u.username  


                FROM tb_evaluasi e


                LEFT JOIN tb_user u ON u.kode_user = e.created_by


                WHERE e.acc_ev = '2' GROUP BY e.no_pka";


        return $this->db->query($sql);


    }


    function update_revisi_evaluasi_pka($data){


        $kode_user = $this->kode_user();


        $sql = "INSERT INTO `tb_evaluasi`(


                                `no_pka`,


                                `kode_pka`,


                                `pencapaian`, 


                                `atp`, 


                                `target_kehadiran`,


                                `realisasi_kehadiran`, 


                                `realisasi_penerimaan`, 


                                `realisasi_pengeluaran`, 


                                `epsk`, 


                                `acc_ev`,


                                `created_at`, 


                                `created_by`) 


                VALUES (


                                '$data[no_pka]',


                                '$data[kode_pka]',


                                '$data[pencapaian]',


                                '$data[atp]',


                                '$data[target_kehadiran]',


                                '$data[realisasi_kehadiran]',


                                '$data[realisasi_penerimaan]',


                                '$data[realisasi_pengeluaran]',


                                '$data[epsk]',


                                '0',


                                NOW(),


                                '$kode_user')";


        return $this->db->query($sql);


    }


    function data_pka_um($unit_misioner,$periode_thn){


        if ($unit_misioner == "all" & $periode_thn == "all") {


            $sql = "SELECT p.no_pka,p.kode_pka,count(p.kode_pka) jml,p.acc,p.evaluasi,p.created_at,p.periode_thn,u.username,u.email,um.nama nm_um 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


                GROUP BY p.no_pka";


        }elseif ($unit_misioner != "all" & $periode_thn == "all") {


            $sql = "SELECT p.no_pka,p.kode_pka,count(p.kode_pka) jml,p.acc,p.evaluasi,p.created_at,p.periode_thn,u.username,u.email,um.nama nm_um 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.unit_misioner ='$unit_misioner' 


                GROUP BY p.no_pka";


        }elseif ($unit_misioner == "all" & $periode_thn != "all") {


            $sql = "SELECT p.no_pka,p.kode_pka,count(p.kode_pka) jml,p.acc,p.evaluasi,p.created_at,p.periode_thn,u.username,u.email,um.nama nm_um 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.periode_thn ='".str_replace("-","/",$periode_thn)."' 


                GROUP BY p.no_pka";


        }else{


            $sql = "SELECT p.no_pka,p.kode_pka,count(p.kode_pka) jml,p.acc,p.evaluasi,p.created_at,p.periode_thn,u.username,u.email,um.nama nm_um 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.unit_misioner ='$unit_misioner' AND p.periode_thn ='".str_replace("-","/",$periode_thn)."' 


                GROUP BY p.no_pka";


        }


        return $this->db->query($sql);


        // return $periode_thn;


    }


//////////////////////////////////////////////////////////////////////////////////////////////////////////


///////admin////////////admin/////////////////////////admin////////////////////////////////////admin//////////////////////////


/////////////////////////////////////////////////////////////////////////////////


    function select_periode_thn(){


        $sql = "SELECT DISTINCT periode_thn FROM `tb_pka` ORDER BY periode_thn  ASC";


        return $this->db->query($sql);


    }


    function data_pka_admin($periode_thn){


        if ($periode_thn == "all") {


            $sql = "SELECT p.no_pka,p.kode_pka,count(p.kode_pka) jml,p.acc,p.evaluasi,p.created_at,p.periode_thn,u.username,u.email,um.nama nm_um 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


                GROUP BY p.no_pka";


        }else{


            $sql = "SELECT p.no_pka,p.kode_pka,count(p.kode_pka) jml,p.acc,p.evaluasi,p.created_at,p.periode_thn,u.username,u.email,um.nama nm_um 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.periode_thn ='".str_replace("-","/",$periode_thn)."'


                GROUP BY p.no_pka";


        }


        return $this->db->query($sql);


        // return $periode_thn;


    }


    function data_sesi_pka(){


        $sql = "SELECT * FROM tb_sesi_pka limit 1";


        return $this->db->query($sql);


    }


    function update_sesi_pka($data){


        $kode_user = $this->kode_user();


        $sql = "UPDATE `tb_sesi_pka` SET `dari`='$data[dari]',`sampai`='$data[sampai]',`keterangan`='$data[keterangan]',`publish`=0,`updated_at`=NOW(),`updated_by`='$kode_user' WHERE kode_sesi_pka = '$data[kode_sesi]'";


        return $this->db->query($sql);


    }


    function publish_sesi_pka($data){


        $kode_user = $this->kode_user();


        $sql = "UPDATE `tb_sesi_pka` SET `publish`=1 WHERE kode_sesi_pka = '$data[kode_sesi]'";


        return $this->db->query($sql);


    }


    function cek_sesi_pka(){


        $sql = "SELECT * FROM tb_sesi_pka WHERE publish=1 limit 1";


        return $this->db->query($sql);


    }


    function data_detail_pka($data){


        $sql = "SELECT pka.*,um.nama nm_um FROM tb_pka pka


                LEFT JOIN tb_unit_misioner um ON pka.unit_misioner = um.id_unit_misioner


                WHERE pka.no_pka='$data[no_pka]' ORDER BY pka.created_at ASC";


        return $this->db->query($sql);


    }


    function select_data_pka_penerimaan($data){


        $sql = "SELECT * FROM tb_pka_penerimaan WHERE kode_pka='$data[kode_pka]'";


        return $this->db->query($sql);


    }


    function select_data_pka_pengeluaran($data){


        $sql = "SELECT * FROM tb_pka_pengeluaran WHERE kode_pka='$data[kode_pka]'";


        return $this->db->query($sql);


    }


    function acc_pka_admin($data){


        $sql = "UPDATE `tb_pka` SET `acc`= 1 WHERE no_pka = '$data[no_pka]'";


        return $this->db->query($sql);


    }


    function refisi_pka_admin($data){


        $sql = "UPDATE `tb_pka` SET `acc`= 2, ket_refisi = '$data[message]' WHERE no_pka = '$data[no_pka]'";


        return $this->db->query($sql);


    }


    function ambil_email($data){


        $sql = "SELECT u.email FROM tb_pka as p 


                LEFT JOIN tb_user u ON u.kode_user = p.created_by


                WHERE p.no_pka = '$data[no_pka]'";


        return $this->db->query($sql);


    }


    function hapus_pka_admin($data){


        $sql = "DELETE FROM `tb_pka` WHERE no_pka = '$data[no_pka]'";


        return $this->db->query($sql);


    }


    function select_hapus_pka_admin($data){


        $sql = "SELECT kode_pka FROM `tb_pka` WHERE no_pka = '$data[no_pka]'";


        return $this->db->query($sql);


    }


    function hapus_pka_pengeluaran_admin($data){


        $sql = "DELETE FROM `tb_pka_pengeluaran` WHERE kode_pka = '$data'";


        return $this->db->query($sql);


    }


    function hapus_pka_penerimaan_admin($data){


        $sql = "DELETE FROM `tb_pka_penerimaan` WHERE kode_pka = '$data'";


        return $this->db->query($sql);


    }


    function insert_edit_pka($data){


        $kode_user = $this->kode_user();


        $sql = "INSERT INTO `tb_pka`(


                    `no_pka`,


                    `kode_pka`,


                    `periode_thn`,


                    `kma`, 


                    `unit_misioner`, 


                    `jenis_kegiatan`, 


                    `bidang`, 


                    `nama_kegiatan`, 


                    `nomor_strategi`, 


                    `tujuan_kegiatan`, 


                    `lokasi_pp`, 


                    `pendukung_program`, 


                    `pen_jawab_program`, 


                    `w_awal_pp`, 


                    `w_akhir_pp`, 


                    `indikator_kuantitatif`, 


                    `indikator_kualitatif`, 


                    `prioritas_kegiatan`, 


                    `triwulan`, 


                    `keterangan`, 


                    `acc`,


                    `evaluasi`,


                    `created_at`, 


                    `created_by`,


                    `updated_at`,


                    `updated_by`


                ) VALUES (


                    '$data[no_pka]',


                    '$data[kode_pka]',


                    '$data[periode_thn]',


                    '$data[kma]',


                    '$data[unit_misioner]',


                    '$data[jenis_kegiatan]',


                    '$data[bidang]',


                    '$data[nama_kegiatan]',


                    '$data[nomor_strategi]',


                    '$data[tujuan_kegiatan]',


                    '$data[lokasi_pp]',


                    '$data[pendukung_program]',


                    '$data[pen_jawab_program]',


                    '$data[w_awal_pp]',


                    '$data[w_akhir_pp]',


                    '$data[indikator_kuantitatif]',


                    '$data[indikator_kualitatif]',


                    '$data[prioritas_kegiatan]',


                    '$data[triwulan]',


                    '$data[keterangan]',


                    '$data[acc]',


                    '$data[evaluasi]',


                    '$data[created_at]',


                    '$data[created_by]',


                    NOW(),


                    '$kode_user'


                    )";


        return $this->db->query($sql);


    }


    function kirim_evaluasi_pka_admin($data){


        $sql = "UPDATE `tb_pka` SET `evaluasi`= '2' WHERE no_pka = '$data[no_pka]'";


        return $this->db->query($sql);


    }


    function data_evaluasi_pka_admin($periode_thn){


        if ($periode_thn == "all") {


            $sql = "SELECT p.no_pka,p.kode_pka,count(p.kode_pka) jml,p.acc,p.created_at,p.periode_thn,p.evaluasi,u.username,u.email,um.nama nm_um 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.evaluasi !=0


                GROUP BY p.no_pka";


        }else{


            $sql = "SELECT p.no_pka,p.kode_pka,count(p.kode_pka) jml,p.acc,p.created_at,p.periode_thn,p.evaluasi,u.username,u.email,um.nama nm_um 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.evaluasi !=0 AND p.periode_thn ='".str_replace("-","/",$periode_thn)."'


                GROUP BY p.no_pka";


        }


        return $this->db->query($sql);


    }


    ////////////////////////


    function data_sesi_ev_pka(){


        $sql = "SELECT * FROM tb_sesi_evaluasi_pka limit 1";


        return $this->db->query($sql);


    }


    function update_sesi_ev_pka($data){


        $kode_user = $this->kode_user();


        $sql = "UPDATE `tb_sesi_evaluasi_pka` SET periode_thn='$data[periode_thn]', `dari`='$data[dari]',`sampai`='$data[sampai]',`keterangan`='$data[keterangan]',`publish`=0,`updated_at`=NOW(),`updated_by`='$kode_user' WHERE kode_sesi_ev_pka = '$data[kode_sesi]'";


        return $this->db->query($sql);


    }


    function cek_sesi_ev_pka(){


        $sql = "SELECT * FROM tb_sesi_evaluasi_pka WHERE publish=1 limit 1";


        return $this->db->query($sql);


    }


    function publish_sesi_ev_pka($data){


        $kode_user = $this->kode_user();


        $sql = "UPDATE `tb_sesi_evaluasi_pka` SET `publish`=1 WHERE kode_sesi_ev_pka = '$data[kode_sesi]'";


        return $this->db->query($sql);


    }


    function refisi_ev_pka_admin($data){


        $sql = "UPDATE `tb_evaluasi` SET `acc_ev`= 2, msg_rev = '$data[message]' WHERE no_pka = '$data[no_pka]'";


        return $this->db->query($sql);


    }


    function acc_ev_pka_admin($data){


        $sql = "UPDATE `tb_evaluasi` SET `acc_ev`= 1 WHERE no_pka = '$data[no_pka]'";


        return $this->db->query($sql);


    }


    function delete_evaluasi_pka($no_pka){


        $sql = "DELETE FROM `tb_evaluasi` WHERE no_pka = '$no_pka'";


        return $this->db->query($sql);


    }


    function update_data_evaluasi_pka($data){


        $kode_user = $this->kode_user();


        $sql = "INSERT INTO `tb_evaluasi`(


                                `no_pka`,


                                `kode_pka`,


                                `pencapaian`, 


                                `atp`, 


                                `target_kehadiran`,


                                `realisasi_kehadiran`, 


                                `realisasi_penerimaan`, 


                                `realisasi_pengeluaran`, 


                                `epsk`, 


                                `acc_ev`,


                                `created_at`, 


                                `created_by`) 


                VALUES (


                                '$data[no_pka]',


                                '$data[kode_pka]',


                                '$data[pencapaian]',


                                '$data[atp]',


                                '$data[target_kehadiran]',


                                '$data[realisasi_kehadiran]',


                                '$data[realisasi_penerimaan]',


                                '$data[realisasi_pengeluaran]',


                                '$data[epsk]',


                                '1',


                                NOW(),


                                '$kode_user')";


        return $this->db->query($sql);


    }


    ///////////////////

















    function ambil_detail_pka_edit($no_pka){


        $sql = "SELECT * FROM tb_pka WHERE no_pka ='$no_pka' limit 1";


        return $this->db->query($sql);


    }


    function ambil_um_pka($no_pka){


        $sql = "SELECT um.kode,um.nama FROM tb_pka p


                LEFT JOIN tb_unit_misioner um ON um.id_unit_misioner = p.unit_misioner


                WHERE p.no_pka = '$no_pka'";


        return $this->db->query($sql);


    }











}