<?php
	class Laborat_panel_model extends CI_Model {
		function __construct()
		{
			// Call the Model constructor
			parent::__construct();
			
			$this->dbhost=$this->load->database('hospistar', TRUE);
					
			date_default_timezone_set('Asia/Jakarta');
		}
				
		
		function getDataPasienLaborat()
		{
			$q=$this->dbhost->query("SELECT a.`PendaftaranID`,a.`LabID`,b.`TanggalMasuk`,c.`NoRM`, c.`Nama`,e.`Item`, c.`Alamat`,d.`nmkecamatan`,c.`NoHP`,c.`TglLhr`,a.`LabTanggal`
 FROM laboratorium_pasien AS a
 LEFT JOIN pendaftaran AS b ON a.`PendaftaranID`=b.`ID`
 LEFT JOIN datapasien AS c ON b.`PasienID`=c.`ID`
 LEFT JOIN kecamatan AS d ON c.`Kecamatan`=d.`ID`
 LEFT JOIN ket_nama AS e ON c.`ketNamaID`=e.`ID`
 WHERE a.`LabTanggal`=LEFT(NOW(),10)  AND a.`LabID`='529'
 GROUP BY a.`PendaftaranID`
 ORDER BY a.`LabTanggal` DESC;");
			
			return $q->result();
		}
			
		function getCariDataPasienLaborat($key)
		{
			$q=$this->dbhost->query("SELECT a.`PendaftaranID`,a.`LabID`,b.`TanggalMasuk`,c.`NoRM`, c.`Nama`,e.`Item`, c.`Alamat`,d.`nmkecamatan`,c.`NoHP`,c.`TglLhr`,a.`LabTanggal`
 FROM laboratorium_pasien AS a
 LEFT JOIN pendaftaran AS b ON a.`PendaftaranID`=b.`ID`
 LEFT JOIN datapasien AS c ON b.`PasienID`=c.`ID`
 LEFT JOIN kecamatan AS d ON c.`Kecamatan`=d.`ID`
 LEFT JOIN ket_nama AS e ON c.`ketNamaID`=e.`ID`
 WHERE a.`LabID`='529' AND $key
 GROUP BY a.`PendaftaranID`
 ORDER BY a.`LabTanggal` DESC;");
			
			return $q->result();
		}
		
		function getPasienLaboratByIDReg($key,$keyx)
		{
			$q=$this->dbhost->query("SELECT a.`PasienID`,a.`TanggalMasuk`, a.`ID`, b.`Status`,b.`NoRM`, b.`Nama`, b.`NIK`,a.`Umur`,g.`Item`,b.NoHP, b.`TglLhr`, b.`Alamat`,i.`nmkecamatan`,j.`namakab`,b.`Kelamin`,c.`LabTanggal`,c.`LabJam`,c.`LabJam`, c.`LabSEQ`, c.`LabNo`,c.`LabID`,e.`LabDesc`, d.`LabHasil`, f.`LabMetDesc`
,c.`PelaksanaID`,c.`LabHasil` AS pj, h.`Dokter` AS atlm
FROM pendaftaran AS a
LEFT JOIN datapasien AS b ON a.`PasienID`=b.`ID`
LEFT JOIN laboratorium_pasien AS c ON a.`ID`=c.`PendaftaranID` 
LEFT JOIN laboratorium_pasien_detail AS d ON c.`PendaftaranID`=d.`PendaftaranID` AND c.`LabID`=d.`LabID`
LEFT JOIN laboratorium AS e ON c.`LabID`=e.`LabID`
LEFT JOIN laboratoriummetode AS f ON e.`LabMetID`=f.`LabMetID` 
LEFT JOIN ket_nama AS g ON b.`ketNamaID`=g.`ID`
LEFT JOIN dokter AS h ON c.`PelaksanaID`=h.`ID`
LEFT JOIN kecamatan AS i ON b.`Kecamatan`=i.`ID`
LEFT JOIN kabupaten AS j ON b.`Kab_Kota`=j.`kodekab`
WHERE a.`ID`='$key' and c.LabID='$keyx'
GROUP BY a.`ID`;");
			
			return $q->result();
		}
		
		function getPasienLaboratAntigenByIDReg($key,$keyx,$tgl)
		{
			$q=$this->dbhost->query("SELECT a.`PasienID`,a.`TanggalMasuk`, a.`ID`, b.`Status`,b.`NoRM`, b.`Nama`, b.`NIK`,a.`Umur`,g.`Item`,b.NoHP, b.`TglLhr`, b.`Alamat`,i.`nmkecamatan`,j.`namakab`,b.`Kelamin`,c.`LabTanggal`,c.`LabJam`,c.`LabJam`, c.`LabSEQ`, c.`LabNo`,c.`LabID`,e.`LabDesc`, d.`LabHasil`, f.`LabMetDesc`
,c.`PelaksanaID`,c.`LabHasil` AS pj, h.`Dokter` AS atlm
FROM pendaftaran AS a
LEFT JOIN datapasien AS b ON a.`PasienID`=b.`ID`
LEFT JOIN laboratorium_pasien AS c ON a.`ID`=c.`PendaftaranID` 
LEFT JOIN laboratorium_pasien_detail AS d ON c.`PendaftaranID`=d.`PendaftaranID` AND c.`LabID`=d.`LabID`
LEFT JOIN laboratorium AS e ON c.`LabID`=e.`LabID`
LEFT JOIN laboratoriummetode AS f ON e.`LabMetID`=f.`LabMetID` 
LEFT JOIN ket_nama AS g ON b.`ketNamaID`=g.`ID`
LEFT JOIN dokter AS h ON c.`PelaksanaID`=h.`ID`
LEFT JOIN kecamatan AS i ON b.`Kecamatan`=i.`ID`
LEFT JOIN kabupaten AS j ON b.`Kab_Kota`=j.`kodekab`
WHERE a.`ID`='$key' and c.LabID='$keyx' and c.`LabTanggal`='$tgl'
GROUP BY a.`ID`;");
			
			return $q->result();
		}
		
		function getIDlabPxDetail()
		{
			$q=$this->dbhost->query("SELECT ID FROM laboratorium_pasien_detail ORDER BY ID DESC LIMIT 1;");
			
			return $q->result();
		}
		
		function cekDetailPxLab($noreg,$labno,$labid,$labseq)
		{
			/* $this->dbhost->where('PendaftaranID', $noreg);			
			$this->dbhost->where('LabNo', $labno);
			$this->dbhost->where('LabID', $labid);
			$this->dbhost->where('LabSEQ', $labseq); */
			
			$q=$this->dbhost->query("SELECT * FROM laboratorium_pasien_detail AS b WHERE b.`PendaftaranID`='$noreg' AND b.`LabNo`='$labno' AND b.`LabID`='$labid' AND b.`LabSEQ`='$labseq';");
			
			return $q->num_rows()>0?TRUE:FALSE;
			//return $q->result();
		}
		
		function insertLabPxDetailRapid($baru)
		{
			return $this->dbhost->insert('laboratorium_pasien_detail', $baru);
		}
		
		function updLabPxDetailRapit($upd,$noreg,$labno,$labid,$labseq)
		{
			$this->dbhost->where('PendaftaranID', $noreg);			
			$this->dbhost->where('LabNo', $labno);
			$this->dbhost->where('LabID', $labid);
			$this->dbhost->where('LabSEQ', $labseq);
			
			return $this->dbhost->update('laboratorium_pasien',$upd);
		}
		
		function insertLogLabPxDetailRapit($log)
		{
			return $this->db->insert('log_laboratorium_pasien_detail', $log);
		}
		
		function getPxlaboratAntigen()
		{
			
			$q=$this->dbhost->query("SELECT a.`PendaftaranID`,a.`LabID`,b.`TanggalMasuk`,c.`NoRM`, c.`Nama`,e.`Item`, c.`Alamat`,d.`nmkecamatan`,c.`NoHP`,c.`TglLhr`,a.`LabTanggal`
 FROM laboratorium_pasien AS a
 LEFT JOIN pendaftaran AS b ON a.`PendaftaranID`=b.`ID`
 LEFT JOIN datapasien AS c ON b.`PasienID`=c.`ID`
 LEFT JOIN kecamatan AS d ON c.`Kecamatan`=d.`ID`
 LEFT JOIN ket_nama AS e ON c.`ketNamaID`=e.`ID`
 WHERE a.`LabTanggal`=LEFT(NOW(),10)  AND a.`LabID`='541'
 GROUP BY a.`PendaftaranID`,a.`LabTanggal`
 ORDER BY a.`LabTanggal` DESC;");
			
			return $q->result();
		
		}
		
		function getCariPxlaboratAntigen($key)
		{
			$q=$this->dbhost->query("SELECT a.`PendaftaranID`,a.`LabID`,b.`TanggalMasuk`,c.`NoRM`, c.`Nama`,e.`Item`, c.`Alamat`,d.`nmkecamatan`,c.`NoHP`,c.`TglLhr`,a.`LabTanggal`
 FROM laboratorium_pasien AS a
 LEFT JOIN pendaftaran AS b ON a.`PendaftaranID`=b.`ID`
 LEFT JOIN datapasien AS c ON b.`PasienID`=c.`ID`
 LEFT JOIN kecamatan AS d ON c.`Kecamatan`=d.`ID`
 LEFT JOIN ket_nama AS e ON c.`ketNamaID`=e.`ID`
 WHERE a.`LabID`='541' AND $key
 GROUP BY a.`PendaftaranID`,a.`LabTanggal`
 ORDER BY a.`LabTanggal` DESC;");
			
			return $q->result();
		}
		
		function getPxlaboratPCR()
		{
			
			$q=$this->dbhost->query("SELECT a.`PendaftaranID`,a.`LabID`,b.`TanggalMasuk`,c.`NoRM`, c.`Nama`,e.`Item`, c.`Alamat`,d.`nmkecamatan`,c.`NoHP`,c.`TglLhr`,a.`LabTanggal`
 FROM laboratorium_pasien AS a
 LEFT JOIN pendaftaran AS b ON a.`PendaftaranID`=b.`ID`
 LEFT JOIN datapasien AS c ON b.`PasienID`=c.`ID`
 LEFT JOIN kecamatan AS d ON c.`Kecamatan`=d.`ID`
 LEFT JOIN ket_nama AS e ON c.`ketNamaID`=e.`ID`
 WHERE a.`LabTanggal`=LEFT(NOW(),10)  AND a.`LabID` IN('530','539','540')
 GROUP BY a.`PendaftaranID`
 ORDER BY a.`LabTanggal` DESC;");
			
			return $q->result();
		
		}
		
		function getCariPxlaboratPCR($key)
		{
			$q=$this->dbhost->query("SELECT a.`PendaftaranID`,a.`LabID`,b.`TanggalMasuk`,c.`NoRM`, c.`Nama`,e.`Item`, c.`Alamat`,d.`nmkecamatan`,c.`NoHP`,c.`TglLhr`,a.`LabTanggal`
 FROM laboratorium_pasien AS a
 LEFT JOIN pendaftaran AS b ON a.`PendaftaranID`=b.`ID`
 LEFT JOIN datapasien AS c ON b.`PasienID`=c.`ID`
 LEFT JOIN kecamatan AS d ON c.`Kecamatan`=d.`ID`
 LEFT JOIN ket_nama AS e ON c.`ketNamaID`=e.`ID`
 WHERE a.`LabID` IN('530','539','540') AND $key
 GROUP BY a.`PendaftaranID`
 ORDER BY a.`LabTanggal` DESC;");
			
			return $q->result();
		}
		
		function cekDetailPxLabPCR($noreg,$labno,$labid,$labseq)
		{
			/* $this->dbhost->where('PendaftaranID', $noreg);			
			$this->dbhost->where('LabNo', $labno);
			$this->dbhost->where('LabID', $labid);
			$this->dbhost->where('LabSEQ', $labseq); */
			
			$q=$this->db->query("SELECT * FROM hasil_lab AS b WHERE b.`noreg`='$noreg' AND b.`labno`='$labno' AND b.`lab_id`='$labid' AND b.`labseq`='$labseq';");
			
			return $q->num_rows()>0?TRUE:FALSE;
			//return $q->result();
		}
		
		function insertLabPxPCR($baru)
		{
			return $this->db->insert('hasil_lab', $baru);
		}
		
		function getPXPCRLokal()
		{
			$q=$this->db->query("SELECT `id_hasil`, `lab_id`, `labno`, `labseq`, `noreg`, `norm`, `nama`, `tgl_lahir`, `usia`, `jkel`, `nik`, `alamat`, `kecamatan`,ket_nama,no_telp,
  `kota`, `tgl_periksa`, `jam_periksa`, `kd_spesimen`, `spesimen`, `tgl_hasil`, `kode_petugas`, `petugas_lab`, `pj_lab`, `sip_pj`, `petugas_entry`, `tgl_entry` FROM `hasil_lab` AS a WHERE LEFT(a.`tgl_entry`,10)=LEFT(NOW(),10);");
			
			return $q->result();
		}
		
		function getPXPCRLokalCari($keylok)
		{
			$q=$this->db->query("SELECT `id_hasil`, `lab_id`, `labno`, `labseq`, `noreg`, `norm`, `nama`, `tgl_lahir`, `usia`, `jkel`, `nik`, `alamat`, `kecamatan`,ket_nama,no_telp,
  `kota`, `tgl_periksa`, `jam_periksa`, `kd_spesimen`, `spesimen`, `tgl_hasil`, `kode_petugas`, `petugas_lab`, `pj_lab`, `sip_pj`, `petugas_entry`, `tgl_entry` FROM `hasil_lab` a WHERE $keylok;");
			
			return $q->result();
		}
		
		function getPXPCRLokalByID($key,$labid)
		{
			$q=$this->db->query("SELECT `id_hasil`, `lab_id`, `labno`, `labseq`, `noreg`, `norm`, `nama`, `tgl_lahir`, `usia`, `jkel`, `nik`, `alamat`, `kecamatan`,ket_nama,no_telp,
  `kota`, `tgl_periksa`, `jam_periksa`, `kd_spesimen`, `spesimen`, `tgl_hasil`, `kode_petugas`, `petugas_lab`, `pj_lab`, `sip_pj`, `petugas_entry`, `tgl_entry` FROM `hasil_lab` a WHERE a.`noreg`='$key' AND a.`lab_id`='$labid';");
			
			return $q->result();
		}
		
		function cekPXPCRLokalCari($keylok)
		{
			$q=$this->db->query("SELECT `id_hasil`, `lab_id`, `labno`, `labseq`, `noreg`, `norm`, `nama`, `tgl_lahir`, `usia`, `jkel`, `nik`, `alamat`, `kecamatan`,
  `kota`, `tgl_periksa`, `jam_periksa`, `kd_spesimen`, `spesimen`, `tgl_hasil`, `kode_petugas`, `petugas_lab`, `pj_lab`, `sip_pj`, `petugas_entry`, `tgl_entry` FROM `hasil_lab` a WHERE $keylok;");
			
			return $q->num_rows()>0?TRUE:FALSE;
		}
		
		function cekPXPCRLokal()
		{
			$q=$this->db->query("SELECT `id_hasil`, `lab_id`, `labno`, `labseq`, `noreg`, `norm`, `nama`, `tgl_lahir`, `usia`, `jkel`, `nik`, `alamat`, `kecamatan`,
  `kota`, `tgl_periksa`, `jam_periksa`, `kd_spesimen`, `spesimen`, `tgl_hasil`, `kode_petugas`, `petugas_lab`, `pj_lab`, `sip_pj`, `petugas_entry`, `tgl_entry` FROM `hasil_lab` AS a WHERE a.`tgl_periksa`=LEFT(NOW(),10);");
			
			return $q->num_rows()>0?TRUE:FALSE;
		}
		
		
		function getDataLaboratReportPeriode($tglawal,$tglakhir)
		{
			$q=$this->dbhost->query("SELECT a.`PendaftaranID`, a.`LabTanggal`,a.`LabJam`, d.`Nama`,g.`Item`, d.`TglLhr`, d.`NoRM`,a.`LabID`,a.`LabSEQ`,a.`LabNo`,getJmlActLab(a.`PendaftaranID`,a.`LabTanggal`) AS jmlact, GROUP_CONCAT(f.`LabDesc`) AS periksa,
c.`FollowUp`,IF(c.`PershID`='1' AND c.`JnsPenjamin`='2','BPJS',IF(c.`PershID`='0' OR c.`JnsPenjamin`='1','UMUM',IF(c.`JnsPenjamin`='3' OR (c.`PershID`<>'0' AND c.`PershID`<>'1'),'Asuransi',''))) AS penjamin
, e.`Dokter`, a.`byUser`
FROM laboratorium_pasien AS a
LEFT JOIN laboratorium_pasien_detail AS b ON a.`PendaftaranID`=b.`PendaftaranID` AND a.`LabSEQ`=b.`LabSEQ` AND a.`LabNo`=b.`LabNo` AND a.`LabID`=b.`LabID`
LEFT JOIN laboratorium AS f ON a.`LabID`=f.`LabID`
LEFT JOIN pendaftaran AS c ON a.`PendaftaranID`=c.`ID`
LEFT JOIN datapasien AS d ON c.`PasienID`=d.`ID`
LEFT JOIN dokter AS e ON a.`DokterID`=e.`ID`
LEFT JOIN ket_nama AS g ON d.`ketNamaID`=g.`ID`
WHERE a.`LabTanggal` BETWEEN '$tglawal' AND '$tglakhir'
GROUP BY a.`PendaftaranID`,a.`LabTanggal`  ORDER BY a.`LabTanggal`;");
			
			return $q->result();
		}

		function getDataLaboratReportAllNow()
		{
			$q=$this->dbhost->query("SELECT a.`PendaftaranID`, a.`LabTanggal`,a.`LabJam`, d.`Nama`,g.`Item`, d.`TglLhr`, d.`NoRM`,a.`LabID`,a.`LabSEQ`,a.`LabNo`,getJmlActLab(a.`PendaftaranID`,a.`LabTanggal`) AS jmlact, GROUP_CONCAT(f.`LabDesc`) AS periksa,
c.`FollowUp`,IF(c.`PershID`='1' AND c.`JnsPenjamin`='2','BPJS',IF(c.`PershID`='0' OR c.`JnsPenjamin`='1','UMUM',IF(c.`JnsPenjamin`='3' OR (c.`PershID`<>'0' AND c.`PershID`<>'1'),'Asuransi',''))) AS penjamin
, e.`Dokter`, a.`byUser`
FROM laboratorium_pasien AS a
LEFT JOIN laboratorium_pasien_detail AS b ON a.`PendaftaranID`=b.`PendaftaranID` AND a.`LabSEQ`=b.`LabSEQ` AND a.`LabNo`=b.`LabNo` AND a.`LabID`=b.`LabID`
LEFT JOIN laboratorium AS f ON a.`LabID`=f.`LabID`
LEFT JOIN pendaftaran AS c ON a.`PendaftaranID`=c.`ID`
LEFT JOIN datapasien AS d ON c.`PasienID`=d.`ID`
LEFT JOIN dokter AS e ON a.`DokterID`=e.`ID`
LEFT JOIN ket_nama AS g ON d.`ketNamaID`=g.`ID`
WHERE a.`LabTanggal`='LEFT(NOW(),10)'
GROUP BY a.`PendaftaranID`,a.`LabTanggal` ORDER BY a.`LabTanggal`;");
			
			return $q->result();
		}
		
		function getDataLaboratRekapPeriode($tglawal,$tglakhir)
		{
			$q=$this->dbhost->query("SELECT IF(a.`DokterID` IS NULL,'APS',c.`Dokter`) AS dokter,
COUNT(IF(b.`PershID`='1' AND b.`JnsPenjamin`='2',a.`PendaftaranID`,NULL)) AS BPJS,
COUNT(IF(b.`PershID`='0' OR b.`JnsPenjamin`='1',a.`PendaftaranID`,NULL)) AS Umum,
COUNT(IF((b.`JnsPenjamin`='3' OR (b.`PershID`<>'0' AND b.`PershID`<>'1')),a.`PendaftaranID`,NULL)) AS Asuransi,
COUNT(a.`PendaftaranID`) AS total
FROM laboratorium_pasien AS a
LEFT JOIN pendaftaran AS b ON a.`PendaftaranID`=b.`ID`
LEFT JOIN dokter AS c ON a.`DokterID`=c.`ID`
WHERE a.`LabTanggal` BETWEEN '$tglawal' AND '$tglakhir'
GROUP BY a.`DokterID` ORDER BY total DESC;");
			
			return $q->result();
		}
		
		
		//dummy		
		function getPxRujukanByID($idpx)
		{
			$q=$this->db
				//->select('b.`tindakan` AS nmtindakan, a.*')
				->select('LEFT(a.`ID`,2) AS idreg,a.*, b.`namaprop`, c.`namakab`, d.`nmkecamatan`, e.`Item` AS penjamin, h.`PintuMasuk` AS poli,i.`Dokter` AS nmdokter, k.`hari`, j.`JamStart`,j.`JamFinish`, IF(a.`JnsPenjamin`="2", f.`NamaLengkap`,g.`NamaLengkap`) AS perusahaan,IF(LEFT(a.`user`,2)="20",l.`nama`,a.`user`) AS perujuk')
				->from('`pendaftaran` AS a')
				->join('`propinsi` AS b','a.`Propinsi`=b.`ID`','left')
				->join('kabupaten AS c',' a.`Kota`=c.`kodekab`','left')
				->join('kecamatan AS d','a.`Kecamatan`=d.`ID`','left')
				->join('`jenis_penjamin` AS e','a.`JnsPenjamin`=e.`ID`','left')
				->join('`datapasien_asuransi` AS f','a.`JnsPenjamin`="2" AND a.`PershID`=f.`ID`','left')
				->join('`datapasien_persh` AS g','a.`JnsPenjamin`="3" AND a.`PershID`=g.`ID`','left')
				->join('pintumasuk AS h','a.`PintuMasuk`=h.`ID`','left')
				->join('dokter AS i','a.`Dokter`=i.`ID`','left')
				->join('jadwal_praktek_dokter AS j','a.`jadwalDokter`=j.`JadwalID`','left')
				->join('hari AS k','j.`DayID`=k.`ID`','left')
				->join('`perujuk` AS l','a.`user`=l.`nik`','left')
				->where('a.`ID`', $idpx)
				//->order_by('a.`TanggalMasuk`', 'DESC')
				->get(); 
		
			return $q->result();
		}
		
		function updatePxRujukanByAdmision($noreg,$updt)
		{
			$this->db->where('ID',$noreg);
			return $this->db->update('pendaftaran', $updt);		
			
		}
	}
