<?php

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

class Dashboard_model extends CI_Model {

    function __construct() {
        parent::__construct();
        //$this->load->library('bahasa');
        //$this->bhs = $this->bahasa->get_lang();
    }

/* ========== IKT RS ========== */
// indikator RS
// select per tahun
    function get_bor_thn() {
        $this->db->select('bln, thn, bor AS jml');
        $this->db->from('ikt_bor');
        $this->db->where('thn', date('Y'));
        return $this->db->get();
    }

    function get_avlos_thn() {
        $this->db->select('bln, thn, avlos AS jml');
        $this->db->from('ikt_avlos');
        $this->db->where('thn', date('Y'));
        return $this->db->get();
    }

    function get_bto_thn() {
        $this->db->select('bln, thn, bto AS jml');
        $this->db->from('ikt_bto');
        $this->db->where('thn', date('Y'));
        return $this->db->get();
    }

    function get_ndr_thn() {
        $this->db->select('bln, thn, ndr AS jml');
        $this->db->from('ikt_ndr');
        $this->db->where('thn', date('Y'));
        return $this->db->get();
    }

    function get_toi_thn() {
        $this->db->select('bln, thn, toi AS jml');
        $this->db->from('ikt_toi');
        $this->db->where('thn', date('Y'));
        return $this->db->get();
    }

// select per bulan
    function get_bor() {
        $this->db->select('*');
        $this->db->from('ikt_bor');
        $this->db->where('bln', date('m'));
        $this->db->where('thn', date('Y'));
        return $this->db->get()->row()->bor;
    }

    function get_avlos() {
        $this->db->select('*');
        $this->db->from('ikt_avlos');
        $this->db->where('bln', date('m'));
        $this->db->where('thn', date('Y'));
        return $this->db->get()->row()->avlos;
    }

    function get_bto() {
        $this->db->select('*');
        $this->db->from('ikt_bto');
        $this->db->where('bln', date('m'));
        $this->db->where('thn', date('Y'));
        return $this->db->get()->row()->bto;
    }

    function get_ndr() {
        $this->db->select('*');
        $this->db->from('ikt_ndr');
        $this->db->where('bln', date('m'));
        $this->db->where('thn', date('Y'));
        return $this->db->get()->row()->ndr;
    }

    function get_toi() {
        $this->db->select('*');
        $this->db->from('ikt_toi');
        $this->db->where('bln', date('m'));
        $this->db->where('thn', date('Y'));
        return $this->db->get()->row()->toi;
    }

// kunjungan pasien
    function get_kunj_irja() {
        $this->db->select('*');
        $this->db->from('ikt_kunj_irja');
        $this->db->where('bln', date('m'));
        $this->db->where('thn', date('Y'));
        return $this->db->get()->row()->kunj_irja;
    }

    function get_kunj_irna() {
        $this->db->select('*');
        $this->db->from('ikt_kunj_irna');
        $this->db->where('bln', date('m'));
        $this->db->where('thn', date('Y'));
        return $this->db->get()->row()->kunj_irna;
    }

    function get_kunj_igd() {
        $this->db->select('*');
        $this->db->from('ikt_kunj_igd');
        $this->db->where('bln', date('m'));
        $this->db->where('thn', date('Y'));
        return $this->db->get()->row()->kunj_igd;
    }

    function get_kunj_jkn() {
        $this->db->select('*');
        $this->db->from('ikt_kunj_jkn');
        $this->db->where('bln', date('m'));
        $this->db->where('thn', date('Y'));
        return $this->db->get()->row()->kunj_jkn;
    }

    function get_kunj_umum() {
        $this->db->select('*');
        $this->db->from('ikt_kunj_umum');
        $this->db->where('bln', date('m'));
        $this->db->where('thn', date('Y'));
        return $this->db->get()->row()->kunj_umum;
    }

    function get_kunj_total() {
        $this->db->select('*');
        $this->db->from('ikt_kunj_total');
        $this->db->where('bln', date('m'));
        $this->db->where('thn', date('Y'));
        return $this->db->get()->row()->kunj_total;
    }

// jml pengunjung
    function get_pengunj_irja() {
        $this->db->select('*');
        $this->db->from('ikt_pengunj_irja');
        $this->db->where('bln', date('m'));
        $this->db->where('thn', date('Y'));
        return $this->db->get()->row()->pengunj_irja;
    }

    function get_pengunj_irna() {
        $this->db->select('*');
        $this->db->from('ikt_pengunj_irna');
        $this->db->where('bln', date('m'));
        $this->db->where('thn', date('Y'));
        return $this->db->get()->row()->pengunj_irna;
    }

    function get_pengunj_igd() {
        $this->db->select('*');
        $this->db->from('ikt_pengunj_igd');
        $this->db->where('bln', date('m'));
        $this->db->where('thn', date('Y'));
        return $this->db->get()->row()->pengunj_igd;
    }

    function get_pengunj_jkn() {
        $this->db->select('*');
        $this->db->from('ikt_pengunj_jkn');
        $this->db->where('bln', date('m'));
        $this->db->where('thn', date('Y'));
        return $this->db->get()->row()->pengunj_jkn;
    }

    function get_pengunj_umum() {
        $this->db->select('*');
        $this->db->from('ikt_pengunj_umum');
        $this->db->where('bln', date('m'));
        $this->db->where('thn', date('Y'));
        return $this->db->get()->row()->pengunj_umum;
    }

    function get_pengunj_total() {
        $this->db->select('*');
        $this->db->from('ikt_pengunj_total');
        $this->db->where('bln', date('m'));
        $this->db->where('thn', date('Y'));
        return $this->db->get()->row()->pengunj_total;
    }

// tarif ranap
    function get_tarif_ranap_kelas1() {
        $this->db->select('*');
        $this->db->from('ikt_tarif_ranap');
        $this->db->where('id = 1');
        return $this->db->get()->row()->tarif;
    }

    function get_tarif_ranap_kelas2() {
        $this->db->select('*');
        $this->db->from('ikt_tarif_ranap');
        $this->db->where('id = 2');
        return $this->db->get()->row()->tarif;
    }

    function get_tarif_ranap_kelas3() {
        $this->db->select('*');
        $this->db->from('ikt_tarif_ranap');
        $this->db->where('id = 3');
        return $this->db->get()->row()->tarif;
    }

// jumlah bed
    function get_jml_bed() {
        $this->db->select('*');
        $this->db->from('bed_jml');
        $this->db->where('id = 1');
        return $this->db->get()->row()->jumlah_bed;
    }

/* ========== GIZI ========== */
    // persediaan
    function persediaan_list() {
        $this->db->select('*');
        $this->db->from('gizi_persediaan');
        $this->db->where('Bulan', date('n'));
        $this->db->where('Tahun', date('Y'));
        return $this->db->get();
    }

    function persediaan_input() {
        $DB3 = $this->load->database('limasatu_gizi', TRUE);
        return $DB3->query("EXEC MM_GZ_Rekap_LaporanPersediaanBulanBerjalan")->result();
    }

    // penerimaan
    function penerimaan_list() {
        $this->db->select('*');
        $this->db->from('gizi_penerimaan');
        $this->db->where('bulan', date('n'));
        $this->db->where('tahun', date('Y'));
        return $this->db->get();
    }

    function penerimaan_input() {
        $DB3 = $this->load->database('limasatu_gizi', TRUE);
        return $DB3->query("EXEC MM_GZ_Rekap_PenerimaanBarang '".date('n')."', '".date('Y')."'")->result();
    }

    // distribusi
    function distribusi_list() {
        $this->db->select('*');
        $this->db->from('gizi_distribusi');
        $this->db->where('bulan', date('n'));
        $this->db->where('tahun', date('Y'));
        return $this->db->get();
    }

    function distribusi_input() {
        $DB3 = $this->load->database('limasatu_gizi', TRUE);
        return $DB3->query("EXEC MM_GZ_Rekap_DistribusiBarang '".date('n')."', '".date('Y')."'")->result();
    }

/* ========== SDM ========== */
    function profil_sdm() {
        $this->db->select('*');
        $this->db->from('profil_sdm');
        return $this->db->get();
    }

    function profil_dewas() {
        $this->db->select('*');
        $this->db->from('profil_dewas');
        return $this->db->get();
    }

    function get_profil_sdm() {
        $DB1 = $this->load->database('tujuhsatu_simka', TRUE);
        $DB1->select('kj.profesi, count(j.jabatan_id) as jml', false);
        $DB1->from('pegawai p', false);
        $DB1->join('m_jabatan j', 'j.jabatan_id = p.jabatan', false);
        $DB1->join('m_kelompok_jabatan kj', 'kj.kel_jabatan_id = j.kelompok_jabatan', false);
        $DB1->where('p.status_aktif = 1');
        $DB1->group_by('profesi');
        $DB1->order_by('profesi','asc');
        return $DB1->get();
    }

/* ========== data keuangan blu ========== */
    function list_data_keuangan_blu() {
        $this->db->select('*');
        $this->db->from('data_keuangan_blu');
        return $this->db->get();
    }
    
/* ==========data layanan blu ========== */
    function list_data_layanan_blu() {
        $this->db->select('*');
        $this->db->from('data_layanan_blu');
        return $this->db->get();
    }
    
    /*
    function ambilDataLayananBLU() {
        $DB3 = $this->load->database('limasatu_hmisdata', TRUE);
        $KunjunganPasienRAJAL = $DB3->query("EXEC procBIOSKunjunganPasien 'OUT','1','".date('Y')."'")->row()->Jml;
        $KunjunganPasienIGD = $DB3->query("EXEC procBIOSKunjunganPasien 'EMG','1','".date('Y')."'")->row()->Jml;
        $KunjunganPasienRANAP = $DB3->query("EXEC procBIOSKunjunganPasien 'IN','1','".date('Y')."'")->row()->Jml;
        $KunjunganPasienUMUM = NULL; //$DB3->query("EXEC procBIOSKunjungangJKN '0','1','".date('Y')."'")->row()->Jml;
        $KunjunganPasienJAMINAN = $DB3->query("EXEC procBIOSKunjungangJKN '0','1','".date('Y')."'")->row()->JmlJKN;
        $KunjunganPasienTOTAL = $DB3->query("EXEC procBIOSKunjungangTotal '1','".date('Y')."'")->row()->JmlJKN;
        $BOR = NULL; //$DB3->query("EXEC procBIOSHitungBOR '1','2017'")->row()->Bor;
        $AVLOS = $DB3->query("EXEC procBIOSAVLos '1','".date('Y')."'")->row()->AVLOS;
        $BTO = $DB3->query("EXEC procBIOSBTO '1','".date('Y')."'")->row()->BTO;
        $NDR = $DB3->query("EXEC procBIOSHitungNDR '1','".date('Y')."'")->row()->NDR;
        $jmlBED = $DB3->query("EXEC procBIOSJumlahBed")->row()->JmlTT;
        $JumlahOrangRAJAL = $DB3->query("EXEC procBIOSPengunjungRS 'OUT','1','".date('Y')."'")->row()->Jml;
        $JumlahOrangIGD = $DB3->query("EXEC procBIOSPengunjungRS 'EMG','1','".date('Y')."'")->row()->Jml;
        $JumlahOrangUMUM = NULL; //$DB3->query("EXEC procBIOSPengunjungJKN '1','1','".date('Y')."'")->row()->Jml;
        $JumlahOrangJAMINAN = $DB3->query("EXEC procBIOSPengunjungJKN '1','1','".date('Y')."'")->row()->JmlJKN;
        $JumlahOrangTOTAL = $DB3->query("EXEC procBIOSPengunjungTotal  '1','".date('Y')."'")->row()->JmlPengunjung;
        $TarifKelas1 = $DB3->query("EXEC procBIOSAkomodasi '02'")->row()->Amount;
        $TarifKelas2 = $DB3->query("EXEC procBIOSAkomodasi '03'")->row()->Amount;
        $TarifKelas3 = $DB3->query("EXEC procBIOSAkomodasi '04'")->row()->Amount;
        $hasil = array($KunjunganPasienRAJAL, 
                        $KunjunganPasienIGD, 
                        $KunjunganPasienRANAP, 
                        $KunjunganPasienUMUM, 
                        $KunjunganPasienJAMINAN, 
                        //$BOR, 
                        $AVLOS, 
                        $BTO, 
                        $NDR, 
                        $jmlBED, 
                        $JumlahOrangRAJAL, 
                        $JumlahOrangIGD, 
                        $JumlahOrangUMUM, 
                        $JumlahOrangJAMINAN, 
                        $JumlahOrangTOTAL, 
                        $TarifKelas1, 
                        $TarifKelas2, 
                        $TarifKelas3); 
        return $hasil;
    }
    */
    
/* ========== kelengkapan drm ranap ========== */
    function drm_ranap_bln() {
        $this->db->select('*');
        $this->db->from('drm_ranap_bln');
        $this->db->where('bulan', date('m'));
        $this->db->where('tahun', date('Y'));
        return $this->db->get();
    }

    function drm_ranap_thn() {
        $this->db->select('*');
        $this->db->from('drm_ranap_thn');
        $this->db->where('tahun', date('Y'));
        return $this->db->get();
    }

    function insert_drm_ranap_bln() {
        $DB2 = $this->load->database('tigasatu_simrs', TRUE);
        return $DB2->query("EXEC procCetakBerkasBulanan_Rev2 '".date('m')."', '".date('Y')."'")->result();
    }

    function insert_drm_ranap_thn() {
        $DB2 = $this->load->database('tigasatu_simrs', TRUE);
        return $DB2->query("EXEC procCetakBerkasTahunan2 '".date('Y')."'")->result();
    }

/* ========== kepuasan pelanggan ========== */
    function kepuasanpelanggan_web() {
        $DB1 = $this->load->database('webrsdk', TRUE);
        $DB1->select('count(tingkat_kepuasan) as jumlah, tingkat_kepuasan');
        $DB1->from('dev_kepuasan_pelanggan');
        $DB1->group_by('tingkat_kepuasan');
        return $DB1->get();
    }

    function kepuasanpelanggan_rs() {
        $DB1 = $this->load->database('webrsdk', TRUE);
        $DB1->select('count(tingkat_kepuasan) as jumlah, tingkat_kepuasan');
        $DB1->from('dev_kepuasan_pelayanan');
        $DB1->group_by('tingkat_kepuasan');
        return $DB1->get();
    }

/* ========== poli ========== */
    function get_poli() {
        $this->db->select('*');
        $this->db->from('dev_kodepoli');
        return $this->db->get();
    }

/* ========== ip ========== */
    function get_ip_public1() {
        $this->db->select('*');
        $this->db->from('config');
        $this->db->where('id=1');
        return $this->db->get();
    }

    function get_ip_public2() {
        $this->db->select('*');
        $this->db->from('config');
        $this->db->where('id=2');
        return $this->db->get();
    }

    function get_ip_local1() {
        $this->db->select('*');
        $this->db->from('config');
        $this->db->where('id=4');
        return $this->db->get();
    }

    function get_ip_local2() {
        $this->db->select('*');
        $this->db->from('config');
        $this->db->where('id=3');
        return $this->db->get();
    }

/* ========== top 10 ========== */
    function sepuluh_besar_irja() {
        $this->db->select('*');
        $this->db->from('10besarirj');
        return $this->db->get();
    }

    function sepuluh_besar_irna() {
        $this->db->select('*');
        $this->db->from('10besariri');
        return $this->db->get();
    }
    
/* ========== kunjungan irja ========== */
    function kunjungan_irja_unit() {
        $this->db->select('i.polycode, i.polyname, k.unit_name, k.unit_alias');
        $this->db->from('kunjungan_irja i');
        $this->db->join('dev_kodepoli k', 'k.poly_code = i.polycode');
        $this->db->group_by('k.unit_alias');
        return $this->db->get();
    }
    
    function kunjungan_irja_hrini() {
        $this->db->select('k.unit_alias, SUM(i.jkn_non_pbi) as nonpbi, SUM(i.jkn_pbi) as pbi, SUM(i.jamkesda) as jamkesda, SUM(i.umum) as umum, SUM(i.jml) as total');
        $this->db->from('kunjungan_irja i');
        $this->db->join('dev_kodepoli k', 'k.poly_code = i.polycode');
        $this->db->like('i.last_update', ''.hari().'/'.bulan().'/'.tahun().'');
        $this->db->group_by('k.unit_alias');
        return $this->db->get();
    }

    function kunjungan_irja_blnini() {
        $this->db->select('k.unit_alias, SUM(i.jkn_non_pbi) as nonpbi, SUM(i.jkn_pbi) as pbi, SUM(i.jamkesda) as jamkesda, SUM(i.umum) as umum, SUM(i.jml) as total');
        $this->db->from('kunjungan_irja i');
        $this->db->join('dev_kodepoli k', 'k.poly_code = i.polycode');
        $this->db->like('i.last_update', ''.bulan().'/'.tahun().'');
        $this->db->group_by('k.unit_alias');
        return $this->db->get();
    }
    
    function kunjungan_irja_thnini() {
        $this->db->select('k.unit_alias, SUM(i.jkn_non_pbi) as nonpbi, SUM(i.jkn_pbi) as pbi, SUM(i.jamkesda) as jamkesda, SUM(i.umum) as umum, SUM(i.jml) as total');
        $this->db->from('kunjungan_irja i');
        $this->db->join('dev_kodepoli k', 'k.poly_code = i.polycode');
        $this->db->like('i.last_update', ''.tahun().'');
        $this->db->group_by('k.unit_alias');
        return $this->db->get();
    }
    
/*
    function kunjungan_irja_waktu() {
        $this->db->select('last_update, SUM(jkn_non_pbi) as nonpbi, SUM(jkn_pbi) as pbi, SUM(jamkesda) as jamkesda, SUM(umum) as umum, SUM(jml) as total');
        $this->db->group_by('last_update');
        $this->db->order_by('last_update', 'asc');
        return $this->db->get('kunjungan_irja');
    }
*/

    function merpati() {
        $this->db->select('i.polycode, i.polyname, k.unit_alias, SUM(i.jkn_non_pbi) as nonpbi, SUM(i.jkn_pbi) as pbi, SUM(i.jamkesda) as jamkesda, SUM(i.umum) as umum, SUM(i.jml) as total');
        $this->db->from('kunjungan_irja i');
        $this->db->join('dev_kodepoli k', 'k.poly_code = i.polycode');
        $this->db->group_by('polycode');
        $this->db->where('k.unit_alias = "MER"');
        return $this->db->get();
    }

    function elang() {
        $this->db->select('i.polycode, i.polyname, k.unit_alias, SUM(i.jkn_non_pbi) as nonpbi, SUM(i.jkn_pbi) as pbi, SUM(i.jamkesda) as jamkesda, SUM(i.umum) as umum, SUM(i.jml) as total');
        $this->db->from('kunjungan_irja i');
        $this->db->join('dev_kodepoli k', 'k.poly_code = i.polycode');
        $this->db->group_by('polycode');
        $this->db->where('k.unit_alias = "ELG"');
        return $this->db->get();
    }

    function murai() {
        $this->db->select('i.polycode, i.polyname, k.unit_alias, SUM(i.jkn_non_pbi) as nonpbi, SUM(i.jkn_pbi) as pbi, SUM(i.jamkesda) as jamkesda, SUM(i.umum) as umum, SUM(i.jml) as total');
        $this->db->from('kunjungan_irja i');
        $this->db->join('dev_kodepoli k', 'k.poly_code = i.polycode');
        $this->db->group_by('polycode');
        $this->db->where('k.unit_alias = "MUR"');
        return $this->db->get();
    }

    function ugd() {
        $this->db->select('i.polycode, i.polyname, k.unit_alias, SUM(i.jkn_non_pbi) as nonpbi, SUM(i.jkn_pbi) as pbi, SUM(i.jamkesda) as jamkesda, SUM(i.umum) as umum, SUM(i.jml) as total');
        $this->db->from('kunjungan_irja i');
        $this->db->join('dev_kodepoli k', 'k.poly_code = i.polycode');
        $this->db->group_by('polycode');
        $this->db->where('k.unit_alias = "IGD"');
        return $this->db->get();
    }

    function garuda() {
        $this->db->select('i.polycode, i.polyname, k.unit_alias, SUM(i.jkn_non_pbi) as nonpbi, SUM(i.jkn_pbi) as pbi, SUM(i.jamkesda) as jamkesda, SUM(i.umum) as umum, SUM(i.jml) as total');
        $this->db->from('kunjungan_irja i');
        $this->db->join('dev_kodepoli k', 'k.poly_code = i.polycode');
        $this->db->group_by('polycode');
        $this->db->where('k.unit_alias = "GRD"');
        return $this->db->get();
    }

    function radiologi() {
        $this->db->select('i.polycode, i.polyname, k.unit_alias, SUM(i.jkn_non_pbi) as nonpbi, SUM(i.jkn_pbi) as pbi, SUM(i.jamkesda) as jamkesda, SUM(i.umum) as umum, SUM(i.jml) as total');
        $this->db->from('kunjungan_irja i');
        $this->db->join('dev_kodepoli k', 'k.poly_code = i.polycode');
        $this->db->group_by('polycode');
        $this->db->where('k.unit_alias = "RAD"');
        return $this->db->get();
    }

/*    
    function kunjungan_irja_lima($num = '1') {
        $this->db->select('KodePoli, NamaPoli, COUNT(IF(JenisPembayaran="JKN NON PBI",1, null)) as nonpbi, COUNT(IF(JenisPembayaran="JKN PBI",1, null)) as pbi, COUNT(JenisPembayaran) as total');
        $this->db->group_by('KodePoli');
        $this->db->limit($num);
        $this->db->order_by('total', 'desc');
        return $this->db->get('kunjungan_irj');
    }
*/

/* ========== kunjungan irna ========== */
    function kunjungan_irna_kelas() {
        $this->db->select('*');
        $this->db->from('kunjungan_iri');
        return $this->db->get();
    }

    function kunjungan_irna_hrini() {
        $this->db->select('last_update, SUM(jkn_non_pbi) as nonpbi, SUM(jkn_pbi) as pbi, SUM(jamkesda) as jamkesda, SUM(umum) as umum');
        $this->db->from('kunjungan_irna');
        $this->db->like('last_update', ''.hari().'/'.bulan().'/'.tahun().'');
        //$this->db->group_by('k.unit_alias');
        return $this->db->get();
    }

    function kunjungan_irna_blnini() {
        $this->db->select('last_update, SUM(jkn_non_pbi) as nonpbi, SUM(jkn_pbi) as pbi, SUM(jamkesda) as jamkesda, SUM(umum) as umum');
        $this->db->from('kunjungan_irna');
        $this->db->like('last_update', ''.bulan().'/'.tahun().'');
        return $this->db->get();
    }
    
    function kunjungan_irna_thnini() {
        $this->db->select('last_update, SUM(jkn_non_pbi) as nonpbi, SUM(jkn_pbi) as pbi, SUM(jamkesda) as jamkesda, SUM(umum) as umum');
        $this->db->from('kunjungan_irna');
        $this->db->like('last_update', ''.tahun().'');
        return $this->db->get();
    }

/*
    function kunjungan_irna_waktu() {
        $this->db->select('last_update, SUM(jkn_non_pbi) as nonpbi, SUM(jkn_pbi) as pbi, SUM(jamkesda) as jamkesda, SUM(umum) as umum, SUM(jml) as total');
        $this->db->group_by('last_update');
        $this->db->order_by('last_update', 'asc');
        return $this->db->get('kunjungan_irna');
    }
*/
/* ========== alkes ========== */
    function alkes() {
        $this->db->select('*');
        $this->db->from('alkes');
        return $this->db->get();
    }

/* ========== obat ========== */
    function obat() {
        $this->db->select('*');
        $this->db->from('obat');
        return $this->db->get();
    }

/* ========== selisih biaya ========== */
    function selisih_diagnosa_in() {
        $this->db->select('*');
        $this->db->from('selisihbiaya_diagnosa');
        $this->db->where('Type','IN');
        return $this->db->get();
    }
    
    function selisih_diagnosa_out() {
        $this->db->select('*');
        $this->db->from('selisihbiaya_diagnosa');
        $this->db->where('Type','OUT');
        return $this->db->get();
    }

    function selisih_dokter_in() {
        $this->db->select('*');
        $this->db->from('selisihbiaya_dokter');
        $this->db->where('Type','IN');
        return $this->db->get();
    }
    
    function selisih_dokter_out() {
        $this->db->select('*');
        $this->db->from('selisihbiaya_dokter');
        $this->db->where('Type','OUT');
        return $this->db->get();
    }

}