<?php
	class Rekap_kunjungan_model extends CI_Model {
		function __construct()
		{
			// Call the Model constructor
			parent::__construct();
			
			$this->dbhost=$this->load->database('khanza', TRUE);
					
			date_default_timezone_set('Asia/Jakarta');
		}
		
		public function hasilRekapKunjunganPasien($followup,$statusx,$tglawal,$tglakhir)
		{
			
			$q=$this->dbhost->query("SELECT b.`nm_poli`,a.`kd_poli`, 
COUNT(IF(a.`kd_pj`='BPJ',a.`no_rawat`,NULL)) AS BPJS,
COUNT(IF(a.`kd_pj`='-' OR a.`kd_pj`='1',a.`no_rawat`,NULL)) AS Umum,
COUNT(IF(a.`kd_pj`<>'BPJ' AND a.`kd_pj`<>'-' AND a.`kd_pj`<>'1',a.`no_rawat`,NULL)) AS Lainnya,
COUNT(a.`no_rawat`) AS Total
FROM reg_periksa AS a 
RIGHT JOIN poliklinik AS b ON a.`kd_poli`=b.`kd_poli`
INNER JOIN dokter AS c ON a.`kd_dokter`=c.`kd_dokter`
INNER JOIN pasien AS d ON a.`no_rkm_medis`=d.`no_rkm_medis`
WHERE $followup $statusx a.`tgl_registrasi` BETWEEN '$tglawal' AND '$tglakhir'
GROUP BY a.`kd_poli`;");				
			
			return $q->result();
			
		}
		
		function getDataDetailRekapRajal($followupx,$status_pasien,$tglawal,$tglakhir,$poli)
		{
			$q=$this->dbhost->query("SELECT b.`nm_poli`, c.`nm_pasien`, d.`nm_dokter`,e.`png_jawab`, a.* 
FROM reg_periksa AS a 
JOIN poliklinik AS b ON a.`kd_poli`=b.`kd_poli`
JOIN pasien AS c ON a.`no_rkm_medis`=c.`no_rkm_medis`
JOIN dokter AS d ON a.`kd_dokter`=d.`kd_dokter`
JOIN penjab AS e ON a.`kd_pj`=e.`kd_pj`
WHERE $followupx $status_pasien a.`kd_poli`='$poli'
AND a.`tgl_registrasi` BETWEEN '$tglawal' AND '$tglakhir' ORDER BY c.`nm_pasien`;");				
			
			return $q->result();
			
			//print("SELECT b.`nm_poli`, c.`nm_pasien`, d.`nm_dokter`,e.`png_jawab`, a.* FROM reg_periksa AS a JOIN poliklinik AS b ON a.`kd_poli`=b.`kd_poli`JOIN pasien AS c ON a.`no_rkm_medis`=c.`no_rkm_medis`JOIN dokter AS d ON a.`kd_dokter`=d.`kd_dokter` JOIN penjab AS e ON a.`kd_pj`=e.`kd_pj`WHERE $followupx $status_pasien a.`kd_poli`='$poli' AND a.`tgl_registrasi` BETWEEN '$tglawal' AND '$tglakhir' ORDER BY c.`nm_pasien`;");exit();
		}
		
		/* $q=$this->dbhost->query("SELECT a.`PintuMasuk` AS idpm, b.`PintuMasuk`, 
COUNT(IF(a.`PershID`='1',a.`ID`,NULL)) AS BPJS,
COUNT(IF(a.`PershID`='0',a.`ID`,NULL)) AS Umum,
COUNT(IF((a.`PershID`<>'0' AND a.`PershID`<>'1'),a.`ID`,NULL)) AS Lainnya,
COUNT(a.`PershID`) AS Total
FROM pendaftaran AS a
LEFT JOIN `pintumasuk` AS b ON a.`PintuMasuk`=b.`ID`
WHERE  $followup  $statusx  b.`PintuMasuk` IS NOT NULL AND a.`PintuMasuk` NOT IN('15','16') AND a.`TanggalMasuk` BETWEEN '$tglawal' AND '$tglakhir'
GROUP BY a.`PintuMasuk`;");				
			$h=array();
			if($q->num_rows()>0):
				foreach($q->result() as $r):
					$h[$r->idpm] = array( 
					'idpoli'=>$r->idpm, 
					'poli'=>$r->PintuMasuk, 
					'BPJS'=>$r->BPJS,
					'Umum'=>$r->Umum,
					'Lainnya'=>$r->Lainnya,
					'Total'=>$r->Total,
				);
				endforeach;
			endif;
			//return $q->result();
			return $h;
		} */
		
		function getDataPasien($tglawal,$tglakhir)
		{
			$q=$this->dbhost->query("SELECT c.`png_jawab`,b.`tgl_registrasi`, a.*
FROM pasien AS a
LEFT JOIN reg_periksa AS b ON a.`no_rkm_medis`=b.`no_rkm_medis`
LEFT JOIN penjab AS c ON a.`kd_pj`=c.`kd_pj`
WHERE ((a.`no_ktp` IS NULL OR a.`no_ktp`='') OR (a.`tgl_lahir` IS NULL OR a.`tgl_lahir`='' OR a.`tgl_lahir`='0000-00-00')
OR (a.`kd_pj`='BPJ' AND (a.`no_peserta` IS NULL OR a.`no_peserta`=''))) 
AND b.`tgl_registrasi` BETWEEN '$tglawal' AND '$tglakhir' GROUP BY a.no_rkm_medis;
");
			
			return $q->result();
		}
		
		/* function getDataDiagnosaKsongRI($tglawal,$tglakhir)
		{
			$q=$this->dbhost->query("SELECT a.`PendaftaranID`,e.`Nama`,a.`KamarID`,g.`NamaKamar`,f.`Dokter`,`byUser`,b.`ICD10`,c.`Nama` AS NamaICD10, b.`NoICD10`,b.`sumTanggal` AS tanggal, b.`sumJam` AS jam 
FROM rawatinap AS a
LEFT JOIN `summery_pasien` AS b ON a.`PendaftaranID`=b.`PendaftaranID`
LEFT JOIN icd10 AS c ON b.`ICD10`=c.`ID`
LEFT JOIN pendaftaran AS d ON a.`PendaftaranID`=d.`ID`
LEFT JOIN datapasien AS e ON e.`ID`=d.`PasienID` 
LEFT JOIN dokter AS f ON b.`DokterID`=f.`ID`
LEFT JOIN kamar AS g ON a.`KamarID`=g.`ID`
WHERE b.`FollowUp`='2' AND (b.`ICD10` IS NULL AND b.`NoICD10` IS NULL)  AND a.`PendaftaranID`<>'0' 
AND a.`RI_TglMasuk` BETWEEN '$tglawal' AND '$tglakhir';");
			
			return $q->result();
		} */
		
		function getDataDiagnosaKsongRJ($follow,$tglawal,$tglakhir)
		{
			$q=$this->dbhost->query("SELECT a.`tgl_registrasi`,a.`no_rawat`,a.`no_rkm_medis`,c.`nm_pasien`,a.`status_lanjut`,a.`kd_poli`,d.`nm_poli`,
 e.`nm_dokter`, b.`kd_penyakit`,f.`nm_penyakit` ,g.`kode`,h.`deskripsi_panjang`
FROM reg_periksa a
LEFT JOIN `diagnosa_pasien` b ON a.`no_rawat`=b.`no_rawat`
LEFT JOIN `prosedur_pasien` AS g ON a.`no_rawat`=g.`no_rawat`
LEFT JOIN pasien c ON a.`no_rkm_medis`=c.`no_rkm_medis`
LEFT JOIN poliklinik AS d ON a.`kd_poli`=d.`kd_poli`
LEFT JOIN dokter AS e ON a.`kd_dokter`=e.`kd_dokter`
LEFT JOIN penyakit AS f ON b.`kd_penyakit`=f.`kd_penyakit`
LEFT JOIN icd9 AS h ON g.`kode`=h.`kode`
WHERE a.`kd_poli` NOT IN('POL24','POl25','POL27','POL30') AND a.`status_lanjut`='$follow'
AND (b.`kd_penyakit` IS NULL AND g.`kode` IS NULL) AND a.`tgl_registrasi` BETWEEN '$tglawal' AND '$tglakhir' ORDER BY a.tgl_registrasi;");
			
			return $q->result();
		}
		
		
		//Json
		function getKamar($idkamar)
		{
			//$this->db->select('ID,NamaKamar,Kapasitas,terisi');
			$q=$this->db->get_where('kamar', array('ID' => $idkamar));
			
			if($q->num_rows() > 0)
			{
				return $q->result();
			}
		}
				
		function saveUpdateKamar($update, $idk)
		{
			$this->db->where('ID', $idk);
			
			return $this->db->update('kamar', $update);
		}
		
		function getRegistrasiPasien($tanggal,$tglakhir)
		{
			$q=$this->dbhost->query("SELECT a.`no_rawat`, a.`no_rkm_medis`, b.`nm_pasien`, a.`tgl_registrasi`, a.`jam_reg`, a.`kd_poli`, d.`nm_poli`, a.`kd_pj`, c.`png_jawab`
FROM reg_periksa AS a
LEFT JOIN pasien AS b ON a.`no_rkm_medis`=b.`no_rkm_medis`
LEFT JOIN penjab AS c ON a.`kd_pj`=c.`kd_pj`
LEFT JOIN poliklinik AS d ON a.`kd_poli`=d.`kd_poli`
WHERE a.`tgl_registrasi` BETWEEN '$tanggal' AND '$tglakhir' ORDER BY a.`tgl_registrasi`, a.`jam_reg` DESC;");
			
			return $q->result();
		}
		
		function getJumlahPasienByDokter()
		{
			$q=$this->dbhost->query("SELECT b.`nm_dokter`, c.`nm_poli`, COUNT(a.`no_rawat`) AS jmlx 
FROM reg_periksa AS a
LEFT JOIN dokter AS b ON a.`kd_dokter`=b.`kd_dokter`
LEFT JOIN poliklinik AS c ON a.`kd_poli`=c.`kd_poli`
WHERE a.`tgl_registrasi`=LEFT(NOW(),10) GROUP BY a.`kd_dokter`,a.`kd_poli`; ");
			return $q->result();
		}
		
		function getJumlahPasienByDokterPeriode($tglawal,$tglakhir)
		{
			$q=$this->dbhost->query("SELECT b.`nm_dokter`,a.`kd_poli`,  c.`nm_poli`, COUNT(a.`no_rawat`) AS jmlx 
FROM reg_periksa AS a
LEFT JOIN dokter AS b ON a.`kd_dokter`=b.`kd_dokter`
LEFT JOIN poliklinik AS c ON a.`kd_poli`=c.`kd_poli`
WHERE a.`kd_poli` <> 'POL27' AND a.`stts`<>'Batal' AND a.`tgl_registrasi` BETWEEN '$tglawal' AND '$tglakhir' GROUP BY a.`kd_dokter`,a.`kd_poli` ORDER BY b.nm_dokter;");
			return $q->result();
		}
		
		function getJumalahPasienRadiologi($tglawal,$tglakhir)
		{
			$q=$this->dbhost->query("SELECT a.`tgl_permintaan` AS tgl_rad, COUNT(IF(b.`kd_pj`='BPJ',a.`noorder`,NULL)) AS BPJSrad,
COUNT(IF(b.`kd_pj`='-' OR b.`kd_pj`='1',a.`noorder`,NULL)) AS Umumrad,
COUNT(IF(b.`kd_pj`<>'BPJ' AND b.`kd_pj`<>'-' AND b.`kd_pj`<>'1',a.`noorder`,NULL)) AS Lainnyarad,
COUNT(a.`noorder`) AS jmlxrad
FROM permintaan_radiologi AS a
LEFT JOIN reg_periksa AS b ON a.`no_rawat`=b.`no_rawat`
WHERE a.`tgl_permintaan` BETWEEN '$tglawal' AND '$tglakhir'
GROUP BY a.tgl_permintaan;");
			return $q->result();
		}
		
		function getJumalahPasienRadiologiFU($follow,$tglawal,$tglakhir)
		{
			$q=$this->dbhost->query("SELECT a.`tgl_permintaan` AS tgl_rad, COUNT(IF(b.`kd_pj`='BPJ',a.`noorder`,NULL)) AS BPJSrad,
COUNT(IF(b.`kd_pj`='-' OR b.`kd_pj`='1',a.`noorder`,NULL)) AS Umumrad,
COUNT(IF(b.`kd_pj`<>'BPJ' AND b.`kd_pj`<>'-' AND b.`kd_pj`<>'1',a.`noorder`,NULL)) AS Lainnyarad,
COUNT(a.`noorder`) AS jmlxrad
FROM permintaan_radiologi AS a
LEFT JOIN reg_periksa AS b ON a.`no_rawat`=b.`no_rawat`
WHERE a.status='$follow' AND a.`tgl_permintaan` BETWEEN '$tglawal' AND '$tglakhir'
GROUP BY a.tgl_permintaan;");
			return $q->result();
		}
		
		function getJumalahPasienLaborat($tglawal,$tglakhir)
		{
			$q=$this->dbhost->query("SELECT a.`tgl_permintaan` AS tgl_lab, COUNT(IF(b.`kd_pj`='BPJ',a.`noorder`,NULL)) AS BPJSlab,
COUNT(IF(b.`kd_pj`='-' OR b.`kd_pj`='1',a.`noorder`,NULL)) AS Umumlab,
COUNT(IF(b.`kd_pj`<>'BPJ' AND b.`kd_pj`<>'-' AND b.`kd_pj`<>'1',a.`noorder`,NULL)) AS Lainnyalab,
COUNT(a.`noorder`) AS jmlxlab
FROM permintaan_lab AS a
LEFT JOIN reg_periksa AS b ON a.`no_rawat`=b.`no_rawat`
WHERE a.`tgl_permintaan` BETWEEN '$tglawal' AND '$tglakhir'
GROUP BY a.tgl_permintaan;");
			return $q->result();
		}
		
		function getJumalahPasienLaboratFU($follow, $tglawal,$tglakhir)
		{
			$q=$this->dbhost->query("SELECT a.`tgl_permintaan` AS tgl_lab, COUNT(IF(b.`kd_pj`='BPJ',a.`noorder`,NULL)) AS BPJSlab,
COUNT(IF(b.`kd_pj`='-' OR b.`kd_pj`='1',a.`noorder`,NULL)) AS Umumlab,
COUNT(IF(b.`kd_pj`<>'BPJ' AND b.`kd_pj`<>'-' AND b.`kd_pj`<>'1',a.`noorder`,NULL)) AS Lainnyalab,
COUNT(a.`noorder`) AS jmlxlab
FROM permintaan_lab AS a
LEFT JOIN reg_periksa AS b ON a.`no_rawat`=b.`no_rawat`
WHERE a.status='$follow' AND a.`tgl_permintaan` BETWEEN '$tglawal' AND '$tglakhir'
GROUP BY a.tgl_permintaan;");
			return $q->result();
		}
		
		function getJmlahPxByRuangan($tglawal,$tglakhir)
		{
			$q=$this->dbhost->query("SELECT c.`NurseStation` AS ruangan, COUNT(a.`PendaftaranID`) AS jml FROM rawatinap AS a LEFT JOIN kamar AS b ON a.`KamarID`=b.`ID` LEFT JOIN nursestation AS c ON b.`NurseStation`=c.`ID` LEFT JOIN pendaftaran AS d ON a.`PendaftaranID`=d.`ID` LEFT JOIN datapasien AS e ON d.`PasienID`=e.`ID` WHERE a.`RI_TglMasuk` BETWEEN '$tglawal' AND '$tglakhir' GROUP BY b.`NurseStation`;");
			return $q->result();
		}
		
	}
