<?php
	class Pendapatan_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');
		}
		
		function getPendapatanByPenjaminRalan($tglawal,$tglakhir)
		{
			//$q=$this->dbhost->query("SELECT a.`tgl_nota`, COUNT(IF(a.`kd_pj`='BPJ',a.`no_rawat`,NULL)) AS px_bpjs, COUNT(IF(a.`kd_pj`='-' OR a.`kd_pj`='1',a.`no_rawat`,NULL)) AS px_umum, COUNT(IF(a.`kd_pj`<>'BPJ' AND a.`kd_pj`<>'-' AND a.`kd_pj`<>'1',a.`no_rawat`,NULL)) AS px_lainnya, COUNT(a.`no_rawat`) AS px_jml, SUM(IF(a.`kd_pj`='BPJ',a.`bayar`,NULL)) AS bpjs, SUM(IF(a.`kd_pj`='-' OR a.`kd_pj`='1',a.`bayar`,NULL)) AS umum, SUM(IF(a.`kd_pj`<>'BPJ' AND a.`kd_pj`<>'-' AND a.`kd_pj`<>'1',a.`bayar`,NULL)) AS lainnya, SUM(a.`bayar`) AS subtotal FROM v_income_rj AS a WHERE a.`tgl_nota` BETWEEN '$tglawal' AND '$tglakhir' GROUP BY a.`tgl_nota` ;");
		/*	$q=$this->dbhost->query("SELECT a.`tgl_nota`,
 COUNT(IF(a.`kd_pj`='BPJ' AND a.`nama_bayar`<>'Tunai',a.`no_rawat`,NULL)) AS px_bpjs,
 COUNT(IF(a.`kd_pj`='BPJ' AND a.`nama_bayar`='Tunai',a.`no_rawat`,NULL)) AS px_exesbpjs,
 COUNT(DISTINCT(IF(a.`kd_pj`='-' OR a.`kd_pj`='1',a.`no_rawat`,NULL))) AS px_umum,
 COUNT(IF(a.`kd_pj`='-' OR a.`kd_pj`='1',a.`no_rawat`,NULL)) AS px_umumx,
 COUNT(IF(a.`kd_pj`<>'BPJ' AND a.`kd_pj`<>'-' AND a.`kd_pj`<>'1' AND a.`nama_bayar`<>'Tunai',a.`no_rawat`,NULL)) AS px_lainnya,
 COUNT(IF(a.`kd_pj`<>'BPJ' AND a.`kd_pj`<>'-' AND a.`kd_pj`<>'1' AND a.`nama_bayar`='Tunai',a.`no_rawat`,NULL)) AS px_exesas,
 COUNT(a.`no_rawat`) AS px_jml,
 SUM(IF(a.`kd_pj`='BPJ'AND a.`nama_bayar`<> 'Tunai',a.`bayar`,0)) AS bpjs,
 SUM(IF(a.`kd_pj`='BPJ' AND a.`nama_bayar`= 'Tunai',a.`bayar`,0)) AS exes_bpjs,
 SUM(IF(a.`kd_pj`='-' OR a.`kd_pj`='1',a.`bayar`,0)) AS umum,
 SUM(IF(a.`kd_pj`<>'BPJ' AND a.`kd_pj`<>'-' AND a.`kd_pj`<>'1' AND a.`nama_bayar`<>'Tunai',a.`bayar`,0)) AS lainnya,
 SUM(IF(a.`kd_pj`<>'BPJ' AND a.`kd_pj`<>'-' AND a.`kd_pj`<>'1' AND a.`nama_bayar`='Tunai',a.`bayar`,0)) AS exes_lainnya,
 SUM(a.`bayar`) AS subtotal
 FROM v_income_rajal AS a WHERE a.`tgl_nota` BETWEEN '$tglawal' AND '$tglakhir' GROUP BY a.`tgl_nota` ;"); */
			/*$q=$this->dbhost->query("SELECT a.`tgl_nota`,
 COUNT(IF(a.`kd_pj`='BPJ' AND (a.`nama_bayar`<>'Tunai' OR a.`nama_bayar`<>'Bank Centra Asia (BCA)' OR a.`nama_bayar`<>'Bank Muamalat Indonesia (BMI)' OR a.`nama_bayar`<>'Bank Rakyat Indonesia (BRI)' OR a.`nama_bayar`<>'Kas Pengeluaran' OR a.`nama_bayar`<>'QRIS Bank Syariah Indonesia (BSI)'),a.`no_rawat`,NULL)) AS px_bpjs,
 COUNT(IF(a.`kd_pj`='BPJ' AND (a.`nama_bayar`='Tunai' OR a.`nama_bayar`='Bank Centra Asia (BCA)' OR a.`nama_bayar`='Bank Muamalat Indonesia (BMI)' OR a.`nama_bayar`='Bank Rakyat Indonesia (BRI)' OR a.`nama_bayar`='Kas Pengeluaran' OR a.`nama_bayar`='QRIS Bank Syariah Indonesia (BSI)'),a.`no_rawat`,NULL)) AS px_exesbpjs,
 COUNT(DISTINCT(IF(a.`kd_pj`='-' OR a.`kd_pj`='1',a.`no_rawat`,NULL))) AS px_umum,
 COUNT(IF(a.`kd_pj`='-' OR a.`kd_pj`='1',a.`no_rawat`,NULL)) AS px_umumx,
 COUNT(IF(a.`kd_pj`<>'BPJ' AND a.`kd_pj`<>'-' AND a.`kd_pj`<>'1' AND (a.`nama_bayar`<>'Tunai' OR a.`nama_bayar`<>'Bank Centra Asia (BCA)' OR a.`nama_bayar`<>'Bank Muamalat Indonesia (BMI)' OR a.`nama_bayar`<>'Bank Rakyat Indonesia (BRI)' OR a.`nama_bayar`<>'Kas Pengeluaran' OR a.`nama_bayar`<>'QRIS Bank Syariah Indonesia (BSI)'),a.`no_rawat`,NULL)) AS px_lainnya,
 COUNT(IF(a.`kd_pj`<>'BPJ' AND a.`kd_pj`<>'-' AND a.`kd_pj`<>'1' AND (a.`nama_bayar`='Tunai' OR a.`nama_bayar`='Bank Centra Asia (BCA)' OR a.`nama_bayar`='Bank Muamalat Indonesia (BMI)' OR a.`nama_bayar`='Bank Rakyat Indonesia (BRI)' OR a.`nama_bayar`='Kas Pengeluaran' OR a.`nama_bayar`='QRIS Bank Syariah Indonesia (BSI)'),a.`no_rawat`,NULL)) AS px_exesas,
 COUNT(DISTINCT(a.`no_rawat`)) AS px_jml,
 COUNT(a.`no_rawat`) AS px_jmlx,
 SUM(IF(a.`kd_pj`='BPJ'AND (a.`nama_bayar`<>'Tunai' OR a.`nama_bayar`<>'Bank Centra Asia (BCA)' OR a.`nama_bayar`<>'Bank Muamalat Indonesia (BMI)' OR a.`nama_bayar`<>'Bank Rakyat Indonesia (BRI)' OR a.`nama_bayar`<>'Kas Pengeluaran' OR a.`nama_bayar`<>'QRIS Bank Syariah Indonesia (BSI)'),a.`bayar`,0)) AS bpjs,
 SUM(IF(a.`kd_pj`='BPJ' AND (a.`nama_bayar`='Tunai' OR a.`nama_bayar`='Bank Centra Asia (BCA)' OR a.`nama_bayar`='Bank Muamalat Indonesia (BMI)' OR a.`nama_bayar`='Bank Rakyat Indonesia (BRI)' OR a.`nama_bayar`='Kas Pengeluaran' OR a.`nama_bayar`='QRIS Bank Syariah Indonesia (BSI)'),a.`bayar`,0)) AS exes_bpjs,
 SUM(IF(a.`kd_pj`='-' OR a.`kd_pj`='1',a.`bayar`,0)) AS umum,
 SUM(IF(a.`kd_pj`<>'BPJ' AND a.`kd_pj`<>'-' AND a.`kd_pj`<>'1' AND (a.`nama_bayar`<>'Tunai' OR a.`nama_bayar`<>'Bank Centra Asia (BCA)' OR a.`nama_bayar`<>'Bank Muamalat Indonesia (BMI)' OR a.`nama_bayar`<>'Bank Rakyat Indonesia (BRI)' OR a.`nama_bayar`<>'Kas Pengeluaran' OR a.`nama_bayar`<>'QRIS Bank Syariah Indonesia (BSI)'),a.`bayar`,0)) AS lainnya,
 SUM(IF(a.`kd_pj`<>'BPJ' AND a.`kd_pj`<>'-' AND a.`kd_pj`<>'1' AND (a.`nama_bayar`='Tunai' OR a.`nama_bayar`='Bank Centra Asia (BCA)' OR a.`nama_bayar`='Bank Muamalat Indonesia (BMI)' OR a.`nama_bayar`='Bank Rakyat Indonesia (BRI)' OR a.`nama_bayar`='Kas Pengeluaran' OR a.`nama_bayar`='QRIS Bank Syariah Indonesia (BSI)'),a.`bayar`,0)) AS exes_lainnya,
 SUM(a.`bayar`) AS subtotal
 FROM v_income_rajal AS a WHERE a.`tgl_nota` BETWEEN '$tglawal' AND '$tglakhir' GROUP BY a.`tgl_nota` ;");
*/			$q=$this->dbhost->query("SELECT a.`tgl_registrasi` as tgl_nota,
 COUNT(IF(a.`kd_pj`='BPJ' AND a.`nama_bayar`<>'Tunai' AND a.`nama_bayar`<>'Bank Centra Asia (BCA)' AND a.`nama_bayar`<>'Bank Muamalat Indonesia (BMI)' AND a.`nama_bayar`<>'Bank Rakyat Indonesia (BRI)' AND a.`nama_bayar`<>'Kas Pengeluaran' AND a.`nama_bayar`<>'QRIS Bank Syariah Indonesia (BSI)' AND a.`nama_bayar` <>'Piutang Pasien Umum' AND a.`nama_bayar`<>'Piutang Karyawan',a.`no_rawat`,NULL)) AS px_bpjs,
 COUNT(DISTINCT(IF(a.`kd_pj`='BPJ' AND (a.`nama_bayar`='Tunai' OR a.`nama_bayar`='Bank Centra Asia (BCA)' OR a.`nama_bayar`='Bank Muamalat Indonesia (BMI)' OR a.`nama_bayar`='Bank Rakyat Indonesia (BRI)' OR a.`nama_bayar`='Kas Pengeluaran' OR a.`nama_bayar`='QRIS Bank Syariah Indonesia (BSI)'),a.`no_rawat`,NULL))) AS px_exesbpjs,
 COUNT(DISTINCT(IF((a.`kd_pj`='-' OR a.`kd_pj`='1') AND (a.`nama_bayar` <>'Piutang Pasien Umum' AND a.`nama_bayar`<>'Piutang Karyawan'),a.`no_rawat`,NULL))) AS px_umum,
 COUNT(IF(a.`kd_pj`='-' OR a.`kd_pj`='1',a.`no_rawat`,NULL)) AS px_umumx,
 COUNT(IF(((a.`kd_pj`<>'BPJ' AND a.`kd_pj`<>'-' AND a.`kd_pj`<>'1') AND (a.`nama_bayar`<>'Tunai' AND a.`nama_bayar`<>'Bank Centra Asia (BCA)' AND a.`nama_bayar`<>'Bank Muamalat Indonesia (BMI)' AND a.`nama_bayar`<>'Bank Rakyat Indonesia (BRI)' AND a.`nama_bayar`<>'Kas Pengeluaran' AND a.`nama_bayar`<>'QRIS Bank Syariah Indonesia (BSI)') 
  OR (a.`nama_bayar` ='Piutang Pasien Umum' OR a.`nama_bayar`='Piutang Karyawan')),a.`no_rawat`,NULL)) AS px_lainnya,
 COUNT(IF(a.`kd_pj`<>'BPJ' AND a.`kd_pj`<>'-' AND a.`kd_pj`<>'1' AND (a.`nama_bayar`='Tunai' OR a.`nama_bayar`='Bank Centra Asia (BCA)' OR a.`nama_bayar`='Bank Muamalat Indonesia (BMI)' OR a.`nama_bayar`='Bank Rakyat Indonesia (BRI)' OR a.`nama_bayar`='Kas Pengeluaran' OR a.`nama_bayar`='QRIS Bank Syariah Indonesia (BSI)'),a.`no_rawat`,NULL)) AS px_exesas,
 COUNT(DISTINCT(a.`no_rawat`)) AS px_jml,
 COUNT(a.`no_rawat`) AS px_jmlx,
 SUM(IF(a.`kd_pj`='BPJ'AND a.`nama_bayar`<>'Tunai' AND a.`nama_bayar`<>'Bank Centra Asia (BCA)' AND a.`nama_bayar`<>'Bank Muamalat Indonesia (BMI)' AND a.`nama_bayar`<>'Bank Rakyat Indonesia (BRI)' AND a.`nama_bayar`<>'Kas Pengeluaran' AND a.`nama_bayar`<>'QRIS Bank Syariah Indonesia (BSI)' AND a.`nama_bayar` <>'Piutang Pasien Umum' AND a.`nama_bayar`<>'Piutang Karyawan',a.`bayar`,0)) AS bpjs,
 SUM(IF(a.`kd_pj`='BPJ' AND (a.`nama_bayar`='Tunai' OR a.`nama_bayar`='Bank Centra Asia (BCA)' OR a.`nama_bayar`='Bank Muamalat Indonesia (BMI)' OR a.`nama_bayar`='Bank Rakyat Indonesia (BRI)' OR a.`nama_bayar`='Kas Pengeluaran' OR a.`nama_bayar`='QRIS Bank Syariah Indonesia (BSI)'),a.`bayar`,0)) AS exes_bpjs,
 SUM(IF((a.`kd_pj`='-' OR a.`kd_pj`='1') AND (a.`nama_bayar` <>'Piutang Pasien Umum' AND a.`nama_bayar`<>'Piutang Karyawan'),a.`bayar`,0)) AS umum,
 SUM(IF(((a.`kd_pj`<>'BPJ' AND a.`kd_pj`<>'-' AND a.`kd_pj`<>'1') AND (a.`nama_bayar`<>'Tunai' AND a.`nama_bayar`<>'Bank Centra Asia (BCA)' AND a.`nama_bayar`<>'Bank Muamalat Indonesia (BMI)' AND a.`nama_bayar`<>'Bank Rakyat Indonesia (BRI)' AND a.`nama_bayar`<>'Kas Pengeluaran' AND a.`nama_bayar`<>'QRIS Bank Syariah Indonesia (BSI)') 
  OR (a.`nama_bayar` ='Piutang Pasien Umum' OR a.`nama_bayar`='Piutang Karyawan')),a.`bayar`,0)) AS lainnya,
 SUM(IF(a.`kd_pj`<>'BPJ' AND a.`kd_pj`<>'-' AND a.`kd_pj`<>'1' AND (a.`nama_bayar`='Tunai' OR a.`nama_bayar`='Bank Centra Asia (BCA)' OR a.`nama_bayar`='Bank Muamalat Indonesia (BMI)' OR a.`nama_bayar`='Bank Rakyat Indonesia (BRI)' OR a.`nama_bayar`='Kas Pengeluaran' OR a.`nama_bayar`='QRIS Bank Syariah Indonesia (BSI)'),a.`bayar`,0)) AS exes_lainnya,
 SUM(a.`bayar`) AS subtotal
 FROM v_income_rajal AS a
 WHERE a.`tgl_nota` BETWEEN '$tglawal' AND '$tglakhir'
 GROUP BY a.`tgl_nota` ;");
			return $q->result();
		}
		
		function getDataDetailPxfromPendapatanByPenjamin($tgl,$pilih,$grop,$pj)
		{
			//$q=$this->dbhost->query("SELECT b.`nm_pasien`,c.`nm_poli`, d.`nm_dokter`, e.`png_jawab`, a.* FROM v_income_rajal AS a JOIN pasien AS b ON a.`no_rkm_medis`=b.`no_rkm_medis` LEFT JOIN poliklinik AS c ON a.`kd_poli`=c.`kd_poli` LEFT JOIN dokter AS d ON a.`kd_dokter`=d.`kd_dokter` LEFT JOIN penjab AS e ON a.`kd_pj`=e.`kd_pj` WHERE a.`tgl_nota`='$tgl' $pj ;");
			$q=$this->dbhost->query("SELECT a.`no_rawat`,a.`tgl_registrasi`,a.tgl_nota, a.`no_nota`,a.`no_rkm_medis`, b.`nm_pasien`,c.`nm_poli`, d.`nm_dokter`, e.`png_jawab`,a.`status_bayar`, a.`status`,$pilih
 FROM v_income_rajal AS a
 JOIN pasien AS b ON a.`no_rkm_medis`=b.`no_rkm_medis`
 LEFT JOIN poliklinik AS c ON a.`kd_poli`=c.`kd_poli`
 LEFT JOIN dokter AS d ON a.`kd_dokter`=d.`kd_dokter`
 LEFT JOIN penjab AS e ON a.`kd_pj`=e.`kd_pj` WHERE a.`tgl_nota`='$tgl' $pj $grop;");
			return $q->result();
		}
		
		function getDataDetailPxfromPendapatanByPenjaminAll($tgl)
		{			
			$q=$this->dbhost->query("SELECT a.`no_rawat`,a.`tgl_registrasi`,a.`tgl_nota`, a.`no_nota`,a.`no_rkm_medis`, b.`nm_pasien`,c.`nm_poli`, d.`nm_dokter`,a.`kd_pj`, e.`png_jawab`,a.`status_bayar`, a.`status`,SUM(a.`bayar`) AS bayar,GROUP_CONCAT(a.`nama_bayar`) AS nama_bayar
 FROM v_income_rajal AS a
 JOIN pasien AS b ON a.`no_rkm_medis`=b.`no_rkm_medis`
 LEFT JOIN poliklinik AS c ON a.`kd_poli`=c.`kd_poli`
 LEFT JOIN dokter AS d ON a.`kd_dokter`=d.`kd_dokter`
 LEFT JOIN penjab AS e ON a.`kd_pj`=e.`kd_pj`
 WHERE a.`tgl_nota`='$tgl' AND (a.`kd_pj`='-' OR a.`kd_pj`='1') AND (a.`nama_bayar` <>'Piutang Pasien Umum' AND a.`nama_bayar`<>'Piutang Karyawan')
 GROUP BY a.`no_rawat` 
 UNION
 SELECT a.`no_rawat`,a.`tgl_nota`, a.`no_nota`,a.`no_rkm_medis`, b.`nm_pasien`,c.`nm_poli`, d.`nm_dokter`, a.`kd_pj`,e.`png_jawab`,a.`status_bayar`, a.`status`,
 a.`bayar` AS bayar, a.`nama_bayar`
 FROM v_income_rajal AS a
 JOIN pasien AS b ON a.`no_rkm_medis`=b.`no_rkm_medis`
 LEFT JOIN poliklinik AS c ON a.`kd_poli`=c.`kd_poli`
 LEFT JOIN dokter AS d ON a.`kd_dokter`=d.`kd_dokter`
 LEFT JOIN penjab AS e ON a.`kd_pj`=e.`kd_pj`
 WHERE a.`tgl_nota`='$tgl' AND a.`kd_pj`='BPJ' AND a.`nama_bayar`<>'Tunai' AND a.`nama_bayar`<>'Bank Centra Asia (BCA)' AND a.`nama_bayar`<>'Bank Muamalat Indonesia (BMI)' AND a.`nama_bayar`<>'Bank Rakyat Indonesia (BRI)' AND a.`nama_bayar`<>'Kas Pengeluaran' AND a.`nama_bayar`<>'QRIS Bank Syariah Indonesia (BSI)' AND a.`nama_bayar` <>'Piutang Pasien Umum' AND a.`nama_bayar`<>'Piutang Karyawan'
UNION
SELECT a.`no_rawat`,a.`tgl_nota`, a.`no_nota`,a.`no_rkm_medis`, b.`nm_pasien`,c.`nm_poli`, d.`nm_dokter`, a.`kd_pj`,e.`png_jawab`,a.`status_bayar`, a.`status`,
 a.`bayar` AS bayar, a.`nama_bayar`
 FROM v_income_rajal AS a
 JOIN pasien AS b ON a.`no_rkm_medis`=b.`no_rkm_medis`
 LEFT JOIN poliklinik AS c ON a.`kd_poli`=c.`kd_poli`
 LEFT JOIN dokter AS d ON a.`kd_dokter`=d.`kd_dokter`
 LEFT JOIN penjab AS e ON a.`kd_pj`=e.`kd_pj`
 WHERE a.`tgl_nota`='$tgl' AND ((a.`kd_pj`<>'BPJ' AND a.`kd_pj`<>'-' AND a.`kd_pj`<>'1') AND (a.`nama_bayar`<>'Tunai' AND a.`nama_bayar`<>'Bank Centra Asia (BCA)' AND a.`nama_bayar`<>'Bank Muamalat Indonesia (BMI)' AND a.`nama_bayar`<>'Bank Rakyat Indonesia (BRI)' AND a.`nama_bayar`<>'Kas Pengeluaran' AND a.`nama_bayar`<>'QRIS Bank Syariah Indonesia (BSI)') OR (a.`nama_bayar` ='Piutang Pasien Umum' OR a.`nama_bayar`='Piutang Karyawan'));");
			return $q->result();
		}
		
		function getPendapatanByPenjaminRanap($tglawal,$tglakhir)
		{
			//$q=$this->dbhost->query("SELECT a.`tgl_nota`, COUNT(IF(a.`kd_pj`='BPJ',a.`no_rawat`,NULL)) AS px_bpjs, COUNT(IF(a.`kd_pj`='-' OR a.`kd_pj`='1',a.`no_rawat`,NULL)) AS px_umum, COUNT(IF(a.`kd_pj`<>'BPJ' AND a.`kd_pj`<>'-' AND a.`kd_pj`<>'1',a.`no_rawat`,NULL)) AS px_lainnya, COUNT(a.`no_rawat`) AS px_jml, SUM(IF(a.`kd_pj`='BPJ',a.`bayar`,'0')) AS bpjs, SUM(IF(a.`kd_pj`='-' OR a.`kd_pj`='1',a.`bayar`,'0')) AS umum, SUM(IF(a.`kd_pj`<>'BPJ' AND a.`kd_pj`<>'-' AND a.`kd_pj`<>'1',a.`bayar`,'0')) AS lainnya, SUM(a.`bayar`) AS subtotal FROM v_income_ri AS a WHERE a.`tgl_nota` BETWEEN '$tglawal' AND '$tglakhir' GROUP BY a.`tgl_nota` ;");
			/* $q=$this->dbhost->query("SELECT a.`tgl_nota`,
 COUNT(IF(a.`kd_pj`='BPJ' AND (a.`nama_bayar`<>'Tunai' OR a.`nama_bayar`<>'Bank Centra Asia (BCA)' OR a.`nama_bayar`<>'Bank Muamalat Indonesia (BMI)' OR a.`nama_bayar`<>'Bank Rakyat Indonesia (BRI)' OR a.`nama_bayar`<>'Kas Pengeluaran' OR a.`nama_bayar`<>'QRIS Bank Syariah Indonesia (BSI)'),a.`no_rawat`,NULL)) AS px_bpjs,
 COUNT(IF(a.`kd_pj`='BPJ' AND (a.`nama_bayar`='Tunai' OR a.`nama_bayar`='Bank Centra Asia (BCA)' OR a.`nama_bayar`='Bank Muamalat Indonesia (BMI)' OR a.`nama_bayar`='Bank Rakyat Indonesia (BRI)' OR a.`nama_bayar`='Kas Pengeluaran' OR a.`nama_bayar`='QRIS Bank Syariah Indonesia (BSI)'),a.`no_rawat`,NULL)) AS px_exesbpjs,
 COUNT(DISTINCT(IF(a.`kd_pj`='-' OR a.`kd_pj`='1',a.`no_rawat`,NULL))) AS px_umum,
 COUNT(IF(a.`kd_pj`='-' OR a.`kd_pj`='1',a.`no_rawat`,NULL)) AS px_umumx,
 COUNT(IF(a.`kd_pj`<>'BPJ' AND a.`kd_pj`<>'-' AND a.`kd_pj`<>'1' AND (a.`nama_bayar`<>'Tunai' OR a.`nama_bayar`<>'Bank Centra Asia (BCA)' OR a.`nama_bayar`<>'Bank Muamalat Indonesia (BMI)' OR a.`nama_bayar`<>'Bank Rakyat Indonesia (BRI)' OR a.`nama_bayar`<>'Kas Pengeluaran' OR a.`nama_bayar`<>'QRIS Bank Syariah Indonesia (BSI)'),a.`no_rawat`,NULL)) AS px_lainnya,
 COUNT(IF(a.`kd_pj`<>'BPJ' AND a.`kd_pj`<>'-' AND a.`kd_pj`<>'1' AND (a.`nama_bayar`='Tunai' OR a.`nama_bayar`='Bank Centra Asia (BCA)' OR a.`nama_bayar`='Bank Muamalat Indonesia (BMI)' OR a.`nama_bayar`='Bank Rakyat Indonesia (BRI)' OR a.`nama_bayar`='Kas Pengeluaran' OR a.`nama_bayar`='QRIS Bank Syariah Indonesia (BSI)'),a.`no_rawat`,NULL)) AS px_exesas,
 COUNT(DISTINCT(a.`no_rawat`)) AS px_jml,
 COUNT(a.`no_rawat`) AS px_jmlx,
 SUM(IF(a.`kd_pj`='BPJ'AND (a.`nama_bayar`<>'Tunai' OR a.`nama_bayar`<>'Bank Centra Asia (BCA)' OR a.`nama_bayar`<>'Bank Muamalat Indonesia (BMI)' OR a.`nama_bayar`<>'Bank Rakyat Indonesia (BRI)' OR a.`nama_bayar`<>'Kas Pengeluaran' OR a.`nama_bayar`<>'QRIS Bank Syariah Indonesia (BSI)'),a.`bayar`,0)) AS bpjs,
 SUM(IF(a.`kd_pj`='BPJ' AND (a.`nama_bayar`='Tunai' OR a.`nama_bayar`='Bank Centra Asia (BCA)' OR a.`nama_bayar`='Bank Muamalat Indonesia (BMI)' OR a.`nama_bayar`='Bank Rakyat Indonesia (BRI)' OR a.`nama_bayar`='Kas Pengeluaran' OR a.`nama_bayar`='QRIS Bank Syariah Indonesia (BSI)'),a.`bayar`,0)) AS exes_bpjs,
 SUM(IF(a.`kd_pj`='-' OR a.`kd_pj`='1',a.`bayar`,0)) AS umum,
 SUM(IF(a.`kd_pj`<>'BPJ' AND a.`kd_pj`<>'-' AND a.`kd_pj`<>'1' AND (a.`nama_bayar`<>'Tunai' OR a.`nama_bayar`<>'Bank Centra Asia (BCA)' OR a.`nama_bayar`<>'Bank Muamalat Indonesia (BMI)' OR a.`nama_bayar`<>'Bank Rakyat Indonesia (BRI)' OR a.`nama_bayar`<>'Kas Pengeluaran' OR a.`nama_bayar`<>'QRIS Bank Syariah Indonesia (BSI)'),a.`bayar`,0)) AS lainnya,
 SUM(IF(a.`kd_pj`<>'BPJ' AND a.`kd_pj`<>'-' AND a.`kd_pj`<>'1' AND (a.`nama_bayar`='Tunai' OR a.`nama_bayar`='Bank Centra Asia (BCA)' OR a.`nama_bayar`='Bank Muamalat Indonesia (BMI)' OR a.`nama_bayar`='Bank Rakyat Indonesia (BRI)' OR a.`nama_bayar`='Kas Pengeluaran' OR a.`nama_bayar`='QRIS Bank Syariah Indonesia (BSI)'),a.`bayar`,0)) AS exes_lainnya,
 SUM(a.`bayar`) AS subtotal 
 FROM v_income_ranap AS a WHERE a.`tgl_nota` BETWEEN '$tglawal' AND '$tglakhir' GROUP BY a.`tgl_nota` ;");  */
			
			$q=$this->dbhost->query("SELECT a.tgl_nota,
 COUNT(IF(a.`kd_pj`='BPJ' AND a.`nama_bayar`<>'Tunai' AND a.`nama_bayar`<>'Bank Centra Asia (BCA)' AND a.`nama_bayar`<>'Bank Muamalat Indonesia (BMI)' AND a.`nama_bayar`<>'Bank Rakyat Indonesia (BRI)' AND a.`nama_bayar`<>'Kas Pengeluaran' AND a.`nama_bayar`<>'QRIS Bank Syariah Indonesia (BSI)' AND a.`nama_bayar` <>'Piutang Pasien Umum' AND a.`nama_bayar`<>'Piutang Karyawan',a.`no_rawat`,NULL)) AS px_bpjs,
 COUNT(DISTINCT(IF(a.`kd_pj`='BPJ' AND (a.`nama_bayar`='Tunai' OR a.`nama_bayar`='Bank Centra Asia (BCA)' OR a.`nama_bayar`='Bank Muamalat Indonesia (BMI)' OR a.`nama_bayar`='Bank Rakyat Indonesia (BRI)' OR a.`nama_bayar`='Kas Pengeluaran' OR a.`nama_bayar`='QRIS Bank Syariah Indonesia (BSI)'),a.`no_rawat`,NULL))) AS px_exesbpjs,
 COUNT(DISTINCT(IF((a.`kd_pj`='-' OR a.`kd_pj`='1') AND (a.`nama_bayar` <>'Piutang Pasien Umum' AND a.`nama_bayar`<>'Piutang Karyawan') OR a.`nama_bayar` IS NULL,a.`no_rawat`,NULL))) AS px_umum,
 COUNT(IF(a.`kd_pj`='-' OR a.`kd_pj`='1',a.`no_rawat`,NULL)) AS px_umumx,
 COUNT(IF(((a.`kd_pj`<>'BPJ' AND a.`kd_pj`<>'-' AND a.`kd_pj`<>'1') AND (a.`nama_bayar`<>'Tunai' AND a.`nama_bayar`<>'Bank Centra Asia (BCA)' AND a.`nama_bayar`<>'Bank Muamalat Indonesia (BMI)' AND a.`nama_bayar`<>'Bank Rakyat Indonesia (BRI)' AND a.`nama_bayar`<>'Kas Pengeluaran' AND a.`nama_bayar`<>'QRIS Bank Syariah Indonesia (BSI)') 
  OR (a.`nama_bayar` ='Piutang Pasien Umum' OR a.`nama_bayar`='Piutang Karyawan')),a.`no_rawat`,NULL)) AS px_lainnya,
 COUNT(IF(a.`kd_pj`<>'BPJ' AND a.`kd_pj`<>'-' AND a.`kd_pj`<>'1' AND (a.`nama_bayar`='Tunai' OR a.`nama_bayar`='Bank Centra Asia (BCA)' OR a.`nama_bayar`='Bank Muamalat Indonesia (BMI)' OR a.`nama_bayar`='Bank Rakyat Indonesia (BRI)' OR a.`nama_bayar`='Kas Pengeluaran' OR a.`nama_bayar`='QRIS Bank Syariah Indonesia (BSI)'),a.`no_rawat`,NULL)) AS px_exesas,
 COUNT(DISTINCT(a.`no_rawat`)) AS px_jml,
 COUNT(a.`no_rawat`) AS px_jmlx,
 SUM(IF(a.`kd_pj`='BPJ'AND a.`nama_bayar`<>'Tunai' AND a.`nama_bayar`<>'Bank Centra Asia (BCA)' AND a.`nama_bayar`<>'Bank Muamalat Indonesia (BMI)' AND a.`nama_bayar`<>'Bank Rakyat Indonesia (BRI)' AND a.`nama_bayar`<>'Kas Pengeluaran' AND a.`nama_bayar`<>'QRIS Bank Syariah Indonesia (BSI)' AND a.`nama_bayar` <>'Piutang Pasien Umum' AND a.`nama_bayar`<>'Piutang Karyawan',a.`bayar`,0)) AS bpjs,
 SUM(IF(a.`kd_pj`='BPJ' AND (a.`nama_bayar`='Tunai' OR a.`nama_bayar`='Bank Centra Asia (BCA)' OR a.`nama_bayar`='Bank Muamalat Indonesia (BMI)' OR a.`nama_bayar`='Bank Rakyat Indonesia (BRI)' OR a.`nama_bayar`='Kas Pengeluaran' OR a.`nama_bayar`='QRIS Bank Syariah Indonesia (BSI)'),a.`bayar`,0)) AS exes_bpjs,
 SUM(IF((a.`kd_pj`='-' OR a.`kd_pj`='1') AND (a.`nama_bayar` <>'Piutang Pasien Umum' AND a.`nama_bayar`<>'Piutang Karyawan')  OR a.`nama_bayar` IS NULL,a.`bayar`,0)) AS umum,
 SUM(IF(((a.`kd_pj`<>'BPJ' AND a.`kd_pj`<>'-' AND a.`kd_pj`<>'1') AND (a.`nama_bayar`<>'Tunai' AND a.`nama_bayar`<>'Bank Centra Asia (BCA)' AND a.`nama_bayar`<>'Bank Muamalat Indonesia (BMI)' AND a.`nama_bayar`<>'Bank Rakyat Indonesia (BRI)' AND a.`nama_bayar`<>'Kas Pengeluaran' AND a.`nama_bayar`<>'QRIS Bank Syariah Indonesia (BSI)') 
  OR (a.`nama_bayar` ='Piutang Pasien Umum' OR a.`nama_bayar`='Piutang Karyawan')),a.`bayar`,0)) AS lainnya,
 SUM(IF(a.`kd_pj`<>'BPJ' AND a.`kd_pj`<>'-' AND a.`kd_pj`<>'1' AND (a.`nama_bayar`='Tunai' OR a.`nama_bayar`='Bank Centra Asia (BCA)' OR a.`nama_bayar`='Bank Muamalat Indonesia (BMI)' OR a.`nama_bayar`='Bank Rakyat Indonesia (BRI)' OR a.`nama_bayar`='Kas Pengeluaran' OR a.`nama_bayar`='QRIS Bank Syariah Indonesia (BSI)'),a.`bayar`,0)) AS exes_lainnya,
 SUM(a.`bayar`) AS subtotal
 FROM v_income_ranap AS a
 WHERE a.`tgl_nota` BETWEEN '$tglawal' AND '$tglakhir' GROUP BY a.`tgl_nota` ;");
			return $q->result();
		}
		
		function getDataDetailPxfromPendapatanByPenjaminRanap($tgl,$pilih,$grop,$pj)
		{
			$q=$this->dbhost->query("SELECT a.`no_rawat`,a.tgl_keluar,a.tgl_nota, a.`no_nota`,a.`no_rkm_medis`, b.`nm_pasien`,c.`nm_poli`, d.`nm_dokter`, e.`png_jawab`,a.`status_bayar`, a.`status`,$pilih
 FROM v_income_ranap AS a
 JOIN pasien AS b ON a.`no_rkm_medis`=b.`no_rkm_medis`
 JOIN poliklinik AS c ON a.`kd_poli`=c.`kd_poli`
 JOIN dokter AS d ON a.`kd_dokter`=d.`kd_dokter`
 JOIN penjab AS e ON a.`kd_pj`=e.`kd_pj`
 JOIN kamar AS f ON a.`kd_kamar`=f.`kd_kamar`
 JOIN bangsal AS g ON f.`kd_bangsal`=g.`kd_bangsal`
 WHERE a.`tgl_nota`='$tgl' $pj $grop ;");
			return $q->result();
		}
		
		function getDataDetailPxfromPendapatanByPenjaminRanapAll($tgl)
		{
			$q=$this->dbhost->query("SELECT a.`no_rawat`,a.tgl_keluar,a.`tgl_nota`, a.`no_nota`,a.`no_rkm_medis`, b.`nm_pasien`,c.`nm_poli`, d.`nm_dokter`, a.`kd_pj`,e.`png_jawab`,a.`status_bayar`, a.`status`,
 a.`bayar` AS bayar, a.`nama_bayar`
 FROM v_income_ranap AS a
 JOIN pasien AS b ON a.`no_rkm_medis`=b.`no_rkm_medis`
 JOIN poliklinik AS c ON a.`kd_poli`=c.`kd_poli`
 JOIN dokter AS d ON a.`kd_dokter`=d.`kd_dokter`
 JOIN penjab AS e ON a.`kd_pj`=e.`kd_pj`
 JOIN kamar AS f ON a.`kd_kamar`=f.`kd_kamar`
 WHERE a.`tgl_nota`='$tgl' AND a.`kd_pj`='BPJ' AND a.`nama_bayar`<>'Tunai' AND a.`nama_bayar`<>'Bank Centra Asia (BCA)' AND a.`nama_bayar`<>'Bank Muamalat Indonesia (BMI)' AND a.`nama_bayar`<>'Bank Rakyat Indonesia (BRI)' AND a.`nama_bayar`<>'Kas Pengeluaran' AND a.`nama_bayar`<>'QRIS Bank Syariah Indonesia (BSI)' AND a.`nama_bayar` <>'Piutang Pasien Umum' AND a.`nama_bayar`<>'Piutang Karyawan'
 UNION
 SELECT a.`no_rawat`,a.tgl_keluar,a.`tgl_nota`, a.`no_nota`,a.`no_rkm_medis`, b.`nm_pasien`,c.`nm_poli`, d.`nm_dokter`, a.`kd_pj`,e.`png_jawab`,a.`status_bayar`, a.`status`,
 a.`bayar` AS bayar, a.`nama_bayar`
 FROM v_income_ranap AS a
 JOIN pasien AS b ON a.`no_rkm_medis`=b.`no_rkm_medis`
 JOIN poliklinik AS c ON a.`kd_poli`=c.`kd_poli`
 JOIN dokter AS d ON a.`kd_dokter`=d.`kd_dokter`
 JOIN penjab AS e ON a.`kd_pj`=e.`kd_pj`
 JOIN kamar AS f ON a.`kd_kamar`=f.`kd_kamar`
 WHERE a.`tgl_nota`='$tgl' AND ((a.`kd_pj`<>'BPJ' AND a.`kd_pj`<>'-' AND a.`kd_pj`<>'1') AND (a.`nama_bayar`<>'Tunai' AND a.`nama_bayar`<>'Bank Centra Asia (BCA)' AND a.`nama_bayar`<>'Bank Muamalat Indonesia (BMI)' AND a.`nama_bayar`<>'Bank Rakyat Indonesia (BRI)' AND a.`nama_bayar`<>'Kas Pengeluaran' AND a.`nama_bayar`<>'QRIS Bank Syariah Indonesia (BSI)') OR (a.`nama_bayar` ='Piutang Pasien Umum' OR a.`nama_bayar`='Piutang Karyawan'))
 UNION
 SELECT a.`no_rawat`,a.tgl_keluar, a.`tgl_nota`, a.`no_nota`,a.`no_rkm_medis`, b.`nm_pasien`,c.`nm_poli`, d.`nm_dokter`,a.`kd_pj`, e.`png_jawab`,a.`status_bayar`, a.`status`,SUM(a.`bayar`) AS bayar,GROUP_CONCAT(a.`nama_bayar`) AS nama_bayar
 FROM v_income_ranap AS a
 JOIN pasien AS b ON a.`no_rkm_medis`=b.`no_rkm_medis`
 JOIN poliklinik AS c ON a.`kd_poli`=c.`kd_poli`
 JOIN dokter AS d ON a.`kd_dokter`=d.`kd_dokter`
 JOIN penjab AS e ON a.`kd_pj`=e.`kd_pj`
 JOIN kamar AS f ON a.`kd_kamar`=f.`kd_kamar`
 WHERE a.`tgl_nota`='$tgl' AND ((a.`kd_pj`='-' OR a.`kd_pj`='1') AND (a.`nama_bayar` <>'Piutang Pasien Umum' AND a.`nama_bayar`<>'Piutang Karyawan') OR a.`nama_bayar` IS NULL);");
			return $q->result();
		}

		function getPendapatanByPenjaminAll($tglawal,$tglakhir)
		{
			//$q=$this->dbhost->query(" SELECT a.`tgl_nota`, COUNT(IF(a.`strawat`='1' AND a.`kd_pj`='BPJ',a.`no_rawat`,NULL)) AS px_bpjs_rajal, COUNT(IF(a.`strawat`='1' AND (a.`kd_pj`='-' OR a.`kd_pj`='1'),a.`no_rawat`,NULL)) AS px_umum_rajal, COUNT(IF(a.`strawat`='1' AND (a.`kd_pj`<>'BPJ' AND a.`kd_pj`<>'-' AND a.`kd_pj`<>'1'),a.`no_rawat`,NULL)) AS px_lainnya_rajal, COUNT(IF(a.`strawat`='1',a.`no_rawat`,NULL)) AS px_jml_rajal, SUM(IF(a.`strawat`='1' AND a.`kd_pj`='BPJ',a.`bayar`,'0')) AS bpjs_rajal, SUM(IF(a.`strawat`='1' AND (a.`kd_pj`='-' OR a.`kd_pj`='1'),a.`bayar`,'0')) AS umum_rajal, SUM(IF(a.`strawat`='1' AND (a.`kd_pj`<>'BPJ' AND a.`kd_pj`<>'-' AND a.`kd_pj`<>'1'),a.`bayar`,'0')) AS lainnya_rajal, SUM(IF(a.`strawat`='1',a.`bayar`,'0')) AS subtotal_rajal,  COUNT(IF(a.`strawat`='2' AND a.`kd_pj`='BPJ',a.`no_rawat`,NULL)) AS px_bpjs_ranap, COUNT(IF(a.`strawat`='2' AND (a.`kd_pj`='-' OR a.`kd_pj`='1'),a.`no_rawat`,NULL)) AS px_umum_ranap, COUNT(IF(a.`strawat`='2' AND (a.`kd_pj`<>'BPJ' AND a.`kd_pj`<>'-' AND a.`kd_pj`<>'1'),a.`no_rawat`,NULL)) AS px_lainnya_ranap, COUNT(IF(a.`strawat`='2',a.`no_rawat`,NULL)) AS px_jml_ranap, SUM(IF(a.`strawat`='2' AND a.`kd_pj`='BPJ',a.`bayar`,'0')) AS bpjs_ranap, SUM(IF(a.`strawat`='2' AND (a.`kd_pj`='-' OR a.`kd_pj`='1'),a.`bayar`,'0')) AS umum_ranap, SUM(IF(a.`strawat`='2' AND (a.`kd_pj`='-' OR a.`kd_pj`='1'),a.`uang_muka`,'0')) AS depositrium, SUM(IF(a.`strawat`='2' AND (a.`kd_pj`<>'BPJ' AND a.`kd_pj`<>'-' AND a.`kd_pj`<>'1'),a.`bayar`,'0')) AS lainnya_ranap, SUM(IF(a.`strawat`='2',a.`bayar`,'0')) AS subtotal_ranap FROM v_income AS a WHERE a.`tgl_nota` BETWEEN '$tglawal' AND '$tglakhir' GROUP BY a.`tgl_nota` ;");
			$q=$this->dbhost->query("SELECT a.tgl_nota,
 COUNT(IF(a.`strawat`='1' AND a.`kd_pj`='BPJ' AND a.`nama_bayar`<>'Tunai' AND a.`nama_bayar`<>'Bank Centra Asia (BCA)' AND a.`nama_bayar`<>'Bank Muamalat Indonesia (BMI)' AND a.`nama_bayar`<>'Bank Rakyat Indonesia (BRI)' AND a.`nama_bayar`<>'Kas Pengeluaran' AND a.`nama_bayar`<>'QRIS Bank Syariah Indonesia (BSI)' AND a.`nama_bayar` <>'Piutang Pasien Umum' AND a.`nama_bayar`<>'Piutang Karyawan',a.`no_rawat`,NULL)) AS px_bpjs_rajal,
 COUNT(DISTINCT(IF(a.`strawat`='1' AND a.`kd_pj`='BPJ' AND (a.`nama_bayar`='Tunai' OR a.`nama_bayar`='Bank Centra Asia (BCA)' OR a.`nama_bayar`='Bank Muamalat Indonesia (BMI)' OR a.`nama_bayar`='Bank Rakyat Indonesia (BRI)' OR a.`nama_bayar`='Kas Pengeluaran' OR a.`nama_bayar`='QRIS Bank Syariah Indonesia (BSI)'),a.`no_rawat`,NULL))) AS px_bpjs_rajalexe,
 COUNT(DISTINCT(IF(a.`strawat`='1' AND (a.`kd_pj`='-' OR a.`kd_pj`='1') AND (a.`nama_bayar` <>'Piutang Pasien Umum' AND a.`nama_bayar`<>'Piutang Karyawan'),a.`no_rawat`,NULL))) AS px_umum_rajal,
 COUNT(IF(a.`strawat`='1' AND ((a.`kd_pj`<>'BPJ' AND a.`kd_pj`<>'-' AND a.`kd_pj`<>'1') AND (a.`nama_bayar`<>'Tunai' AND a.`nama_bayar`<>'Bank Centra Asia (BCA)' AND a.`nama_bayar`<>'Bank Muamalat Indonesia (BMI)' AND a.`nama_bayar`<>'Bank Rakyat Indonesia (BRI)' AND a.`nama_bayar`<>'Kas Pengeluaran' AND a.`nama_bayar`<>'QRIS Bank Syariah Indonesia (BSI)') 
  OR (a.`nama_bayar` ='Piutang Pasien Umum' OR a.`nama_bayar`='Piutang Karyawan')),a.`no_rawat`,NULL)) AS px_lainnya_rajal,
 COUNT(IF(a.`strawat`='1' AND a.`kd_pj`<>'BPJ' AND a.`kd_pj`<>'-' AND a.`kd_pj`<>'1' AND (a.`nama_bayar`='Tunai' OR a.`nama_bayar`='Bank Centra Asia (BCA)' OR a.`nama_bayar`='Bank Muamalat Indonesia (BMI)' OR a.`nama_bayar`='Bank Rakyat Indonesia (BRI)' OR a.`nama_bayar`='Kas Pengeluaran' OR a.`nama_bayar`='QRIS Bank Syariah Indonesia (BSI)'),a.`no_rawat`,NULL)) AS px_lainnya_rajalexe, 
 COUNT(DISTINCT(IF(a.`strawat`='1',a.`no_rawat`,NULL))) AS px_jml_rajal,
 SUM(IF(a.`strawat`='1' AND a.`kd_pj`='BPJ'AND a.`nama_bayar`<>'Tunai' AND a.`nama_bayar`<>'Bank Centra Asia (BCA)' AND a.`nama_bayar`<>'Bank Muamalat Indonesia (BMI)' AND a.`nama_bayar`<>'Bank Rakyat Indonesia (BRI)' AND a.`nama_bayar`<>'Kas Pengeluaran' AND a.`nama_bayar`<>'QRIS Bank Syariah Indonesia (BSI)' AND a.`nama_bayar` <>'Piutang Pasien Umum' AND a.`nama_bayar`<>'Piutang Karyawan',a.`bayar`,'0')) AS bpjs_rajal,
 SUM(IF(a.`strawat`='1' AND a.`kd_pj`='BPJ' AND (a.`nama_bayar`='Tunai' OR a.`nama_bayar`='Bank Centra Asia (BCA)' OR a.`nama_bayar`='Bank Muamalat Indonesia (BMI)' OR a.`nama_bayar`='Bank Rakyat Indonesia (BRI)' OR a.`nama_bayar`='Kas Pengeluaran' OR a.`nama_bayar`='QRIS Bank Syariah Indonesia (BSI)'),a.`bayar`,'0')) AS bpjs_rajalexe,
 SUM(IF(a.`strawat`='1' AND (a.`kd_pj`='-' OR a.`kd_pj`='1') AND (a.`nama_bayar` <>'Piutang Pasien Umum' AND a.`nama_bayar`<>'Piutang Karyawan'),a.`bayar`,'0')) AS umum_rajal,
 SUM(IF(a.`strawat`='1' AND ((a.`kd_pj`<>'BPJ' AND a.`kd_pj`<>'-' AND a.`kd_pj`<>'1') AND (a.`nama_bayar`<>'Tunai' AND a.`nama_bayar`<>'Bank Centra Asia (BCA)' AND a.`nama_bayar`<>'Bank Muamalat Indonesia (BMI)' AND a.`nama_bayar`<>'Bank Rakyat Indonesia (BRI)' AND a.`nama_bayar`<>'Kas Pengeluaran' AND a.`nama_bayar`<>'QRIS Bank Syariah Indonesia (BSI)') 
  OR (a.`nama_bayar` ='Piutang Pasien Umum' OR a.`nama_bayar`='Piutang Karyawan')),a.`bayar`,'0')) AS lainnya_rajal,
 SUM(IF(a.`strawat`='1' AND a.`kd_pj`<>'BPJ' AND a.`kd_pj`<>'-' AND a.`kd_pj`<>'1' AND (a.`nama_bayar`='Tunai' OR a.`nama_bayar`='Bank Centra Asia (BCA)' OR a.`nama_bayar`='Bank Muamalat Indonesia (BMI)' OR a.`nama_bayar`='Bank Rakyat Indonesia (BRI)' OR a.`nama_bayar`='Kas Pengeluaran' OR a.`nama_bayar`='QRIS Bank Syariah Indonesia (BSI)'),a.`bayar`,'0')) AS lainnya_rajalexe,
 SUM(IF(a.`strawat`='1',a.`bayar`,'0')) AS subtotal_rajal,
 COUNT(IF(a.`strawat`='2' AND a.`kd_pj`='BPJ' AND a.`nama_bayar`<>'Tunai' AND a.`nama_bayar`<>'Bank Centra Asia (BCA)' AND a.`nama_bayar`<>'Bank Muamalat Indonesia (BMI)' AND a.`nama_bayar`<>'Bank Rakyat Indonesia (BRI)' AND a.`nama_bayar`<>'Kas Pengeluaran' AND a.`nama_bayar`<>'QRIS Bank Syariah Indonesia (BSI)' AND a.`nama_bayar` <>'Piutang Pasien Umum' AND a.`nama_bayar`<>'Piutang Karyawan',a.`no_rawat`,NULL)) AS px_bpjs_ranap,
 COUNT(DISTINCT(IF(a.`strawat`='2' AND a.`kd_pj`='BPJ' AND (a.`nama_bayar`='Tunai' OR a.`nama_bayar`='Bank Centra Asia (BCA)' OR a.`nama_bayar`='Bank Muamalat Indonesia (BMI)' OR a.`nama_bayar`='Bank Rakyat Indonesia (BRI)' OR a.`nama_bayar`='Kas Pengeluaran' OR a.`nama_bayar`='QRIS Bank Syariah Indonesia (BSI)'),a.`no_rawat`,NULL))) AS px_bpjs_ranapexe,
 COUNT(DISTINCT(IF(a.`strawat`='2' AND ((a.`kd_pj`='-' OR a.`kd_pj`='1') AND (a.`nama_bayar` <>'Piutang Pasien Umum' AND a.`nama_bayar`<>'Piutang Karyawan') OR a.`nama_bayar` IS NULL),a.`no_rawat`,NULL))) AS px_umum_ranap,
 COUNT(IF(a.`strawat`='2' AND ((a.`kd_pj`<>'BPJ' AND a.`kd_pj`<>'-' AND a.`kd_pj`<>'1') AND (a.`nama_bayar`<>'Tunai' AND a.`nama_bayar`<>'Bank Centra Asia (BCA)' AND a.`nama_bayar`<>'Bank Muamalat Indonesia (BMI)' AND a.`nama_bayar`<>'Bank Rakyat Indonesia (BRI)' AND a.`nama_bayar`<>'Kas Pengeluaran' AND a.`nama_bayar`<>'QRIS Bank Syariah Indonesia (BSI)') 
  OR (a.`nama_bayar` ='Piutang Pasien Umum' OR a.`nama_bayar`='Piutang Karyawan')),a.`no_rawat`,NULL)) AS px_lainnya_ranap,
 COUNT(IF(a.`strawat`='2' AND a.`kd_pj`<>'BPJ' AND a.`kd_pj`<>'-' AND a.`kd_pj`<>'1' AND (a.`nama_bayar`='Tunai' OR a.`nama_bayar`='Bank Centra Asia (BCA)' OR a.`nama_bayar`='Bank Muamalat Indonesia (BMI)' OR a.`nama_bayar`='Bank Rakyat Indonesia (BRI)' OR a.`nama_bayar`='Kas Pengeluaran' OR a.`nama_bayar`='QRIS Bank Syariah Indonesia (BSI)'),a.`no_rawat`,NULL)) AS px_lainnya_ranapexe,
 COUNT(DISTINCT(IF(a.`strawat`='2',a.`no_rawat`,NULL))) AS px_jml_ranap,
 SUM(IF(a.`strawat`='2' AND a.`kd_pj`='BPJ'AND a.`nama_bayar`<>'Tunai' AND a.`nama_bayar`<>'Bank Centra Asia (BCA)' AND a.`nama_bayar`<>'Bank Muamalat Indonesia (BMI)' AND a.`nama_bayar`<>'Bank Rakyat Indonesia (BRI)' AND a.`nama_bayar`<>'Kas Pengeluaran' AND a.`nama_bayar`<>'QRIS Bank Syariah Indonesia (BSI)' AND a.`nama_bayar` <>'Piutang Pasien Umum' AND a.`nama_bayar`<>'Piutang Karyawan',a.`bayar`,'0')) AS bpjs_ranap,
 SUM(IF(a.`strawat`='2' AND a.`kd_pj`='BPJ' AND (a.`nama_bayar`='Tunai' OR a.`nama_bayar`='Bank Centra Asia (BCA)' OR a.`nama_bayar`='Bank Muamalat Indonesia (BMI)' OR a.`nama_bayar`='Bank Rakyat Indonesia (BRI)' OR a.`nama_bayar`='Kas Pengeluaran' OR a.`nama_bayar`='QRIS Bank Syariah Indonesia (BSI)'),a.`bayar`,'0')) AS bpjs_ranapexe,
 SUM(IF(a.`strawat`='2' AND ((a.`kd_pj`='-' OR a.`kd_pj`='1') AND (a.`nama_bayar` <>'Piutang Pasien Umum' AND a.`nama_bayar`<>'Piutang Karyawan') OR a.`nama_bayar` IS NULL),a.`bayar`,'0')) AS umum_ranap,
 SUM(IF(a.`strawat`='2' AND (a.`kd_pj`='-' OR a.`kd_pj`='1'),a.`uang_muka`,'0')) AS depositrium,
 SUM(IF(a.`strawat`='2' AND ((a.`kd_pj`<>'BPJ' AND a.`kd_pj`<>'-' AND a.`kd_pj`<>'1') AND (a.`nama_bayar`<>'Tunai' AND a.`nama_bayar`<>'Bank Centra Asia (BCA)' AND a.`nama_bayar`<>'Bank Muamalat Indonesia (BMI)' AND a.`nama_bayar`<>'Bank Rakyat Indonesia (BRI)' AND a.`nama_bayar`<>'Kas Pengeluaran' AND a.`nama_bayar`<>'QRIS Bank Syariah Indonesia (BSI)') 
  OR (a.`nama_bayar` ='Piutang Pasien Umum' OR a.`nama_bayar`='Piutang Karyawan')),a.`bayar`,'0')) AS lainnya_ranap,
 SUM(IF(a.`strawat`='2' AND a.`kd_pj`<>'BPJ' AND a.`kd_pj`<>'-' AND a.`kd_pj`<>'1' AND (a.`nama_bayar`='Tunai' OR a.`nama_bayar`='Bank Centra Asia (BCA)' OR a.`nama_bayar`='Bank Muamalat Indonesia (BMI)' OR a.`nama_bayar`='Bank Rakyat Indonesia (BRI)' OR a.`nama_bayar`='Kas Pengeluaran' OR a.`nama_bayar`='QRIS Bank Syariah Indonesia (BSI)'),a.`bayar`,'0')) AS lainnya_ranapexe,
 SUM(IF(a.`strawat`='2',a.`bayar`,'0')) AS subtotal_ranap
 FROM v_income AS a WHERE a.`tgl_nota` BETWEEN '$tglawal' AND '$tglakhir' GROUP BY a.`tgl_nota` ;");
			return $q->result();
		}
		
		function getRekapTotal($tglawal,$tglakhir)
		{
			$q=$this->dbhost->query("SELECT a.tgl_nota,
 COUNT(IF(a.`kd_pj`='BPJ' AND a.`nama_bayar`<>'Tunai' AND a.`nama_bayar`<>'Bank Centra Asia (BCA)' AND a.`nama_bayar`<>'Bank Muamalat Indonesia (BMI)' AND a.`nama_bayar`<>'Bank Rakyat Indonesia (BRI)' AND a.`nama_bayar`<>'Kas Pengeluaran' AND a.`nama_bayar`<>'QRIS Bank Syariah Indonesia (BSI)' AND a.`nama_bayar` <>'Piutang Pasien Umum' AND a.`nama_bayar`<>'Piutang Karyawan',a.`no_rawat`,NULL)) AS px_bpjs,
 COUNT(DISTINCT(IF(a.`kd_pj`='BPJ' AND (a.`nama_bayar`='Tunai' OR a.`nama_bayar`='Bank Centra Asia (BCA)' OR a.`nama_bayar`='Bank Muamalat Indonesia (BMI)' OR a.`nama_bayar`='Bank Rakyat Indonesia (BRI)' OR a.`nama_bayar`='Kas Pengeluaran' OR a.`nama_bayar`='QRIS Bank Syariah Indonesia (BSI)'),a.`no_rawat`,NULL))) AS px_bpjs_exe,
 COUNT(DISTINCT(IF((a.`kd_pj`='-' OR a.`kd_pj`='1') AND (a.`nama_bayar` <>'Piutang Pasien Umum' AND a.`nama_bayar`<>'Piutang Karyawan') OR a.`nama_bayar` IS NULL,a.`no_rawat`,NULL))) AS px_umum,
 COUNT(IF(((a.`kd_pj`<>'BPJ' AND a.`kd_pj`<>'-' AND a.`kd_pj`<>'1') AND (a.`nama_bayar`<>'Tunai' AND a.`nama_bayar`<>'Bank Centra Asia (BCA)' AND a.`nama_bayar`<>'Bank Muamalat Indonesia (BMI)' AND a.`nama_bayar`<>'Bank Rakyat Indonesia (BRI)' AND a.`nama_bayar`<>'Kas Pengeluaran' AND a.`nama_bayar`<>'QRIS Bank Syariah Indonesia (BSI)') 
  OR (a.`nama_bayar` ='Piutang Pasien Umum' OR a.`nama_bayar`='Piutang Karyawan')),a.`no_rawat`,NULL)) AS px_lainnya,
 COUNT(IF(a.`kd_pj`<>'BPJ' AND a.`kd_pj`<>'-' AND a.`kd_pj`<>'1' AND (a.`nama_bayar`='Tunai' OR a.`nama_bayar`='Bank Centra Asia (BCA)' OR a.`nama_bayar`='Bank Muamalat Indonesia (BMI)' OR a.`nama_bayar`='Bank Rakyat Indonesia (BRI)' OR a.`nama_bayar`='Kas Pengeluaran' OR a.`nama_bayar`='QRIS Bank Syariah Indonesia (BSI)'),a.`no_rawat`,NULL)) AS px_lainnya_exe, 
 COUNT(DISTINCT(a.`no_rawat`)) AS px_jml,
 SUM(IF(a.`kd_pj`='BPJ'AND a.`nama_bayar`<>'Tunai' AND a.`nama_bayar`<>'Bank Centra Asia (BCA)' AND a.`nama_bayar`<>'Bank Muamalat Indonesia (BMI)' AND a.`nama_bayar`<>'Bank Rakyat Indonesia (BRI)' AND a.`nama_bayar`<>'Kas Pengeluaran' AND a.`nama_bayar`<>'QRIS Bank Syariah Indonesia (BSI)' AND a.`nama_bayar` <>'Piutang Pasien Umum' AND a.`nama_bayar`<>'Piutang Karyawan',a.`bayar`,'0')) AS bpjs,
 SUM(IF(a.`kd_pj`='BPJ' AND (a.`nama_bayar`='Tunai' OR a.`nama_bayar`='Bank Centra Asia (BCA)' OR a.`nama_bayar`='Bank Muamalat Indonesia (BMI)' OR a.`nama_bayar`='Bank Rakyat Indonesia (BRI)' OR a.`nama_bayar`='Kas Pengeluaran' OR a.`nama_bayar`='QRIS Bank Syariah Indonesia (BSI)'),a.`bayar`,'0')) AS bpjs_exe,
 SUM(IF((a.`kd_pj`='-' OR a.`kd_pj`='1') AND (a.`nama_bayar` <>'Piutang Pasien Umum' AND a.`nama_bayar`<>'Piutang Karyawan') OR a.`nama_bayar` IS NULL,a.`bayar`,'0')) AS umum,
 SUM(IF(((a.`kd_pj`<>'BPJ' AND a.`kd_pj`<>'-' AND a.`kd_pj`<>'1') AND (a.`nama_bayar`<>'Tunai' AND a.`nama_bayar`<>'Bank Centra Asia (BCA)' AND a.`nama_bayar`<>'Bank Muamalat Indonesia (BMI)' AND a.`nama_bayar`<>'Bank Rakyat Indonesia (BRI)' AND a.`nama_bayar`<>'Kas Pengeluaran' AND a.`nama_bayar`<>'QRIS Bank Syariah Indonesia (BSI)') 
  OR (a.`nama_bayar` ='Piutang Pasien Umum' OR a.`nama_bayar`='Piutang Karyawan')),a.`bayar`,'0')) AS lainnya,
 SUM(IF(a.`kd_pj`<>'BPJ' AND a.`kd_pj`<>'-' AND a.`kd_pj`<>'1' AND (a.`nama_bayar`='Tunai' OR a.`nama_bayar`='Bank Centra Asia (BCA)' OR a.`nama_bayar`='Bank Muamalat Indonesia (BMI)' OR a.`nama_bayar`='Bank Rakyat Indonesia (BRI)' OR a.`nama_bayar`='Kas Pengeluaran' OR a.`nama_bayar`='QRIS Bank Syariah Indonesia (BSI)'),a.`bayar`,'0')) AS lainnya_exe,
 SUM(a.`bayar`) AS subtotal 
 FROM v_income AS a
 WHERE a.`tgl_nota` BETWEEN '$tglawal' AND '$tglakhir'
 GROUP BY a.`tgl_nota` ;");
			return $q->result();
		}
		
		function getRekapPendapatanPerbulan($awal, $akhir)
		{
			$q=$this->dbhost->query("SELECT a.`kd_pj`,b.`png_jawab`,
  SUM(IF(MID(`a`.`tgl_nota`,6,2) = '01', a.`bayar`,0)) AS `bln1`,  
  SUM(IF(MID(`a`.`tgl_nota`,6,2) = '02', a.`bayar`,0)) AS `bln2`,
  SUM(IF(MID(`a`.`tgl_nota`,6,2) = '03', a.`bayar`,0)) AS `bln3`,
  SUM(IF(MID(`a`.`tgl_nota`,6,2) = '04', a.`bayar`,0)) AS `bln4`,
  SUM(IF(MID(`a`.`tgl_nota`,6,2) = '05', a.`bayar`,0)) AS `bln5`,
  SUM(IF(MID(`a`.`tgl_nota`,6,2) = '06', a.`bayar`,0)) AS `bln6`,
  SUM(IF(MID(`a`.`tgl_nota`,6,2) = '07', a.`bayar`,0)) AS `bln7`,
  SUM(IF(MID(`a`.`tgl_nota`,6,2) = '08', a.`bayar`,0)) AS `bln8`,
  SUM(IF(MID(`a`.`tgl_nota`,6,2) = '09', a.`bayar`,0)) AS `bln9`,
  SUM(IF(MID(`a`.`tgl_nota`,6,2) = '10', a.`bayar`,0)) AS `bln10`,
  SUM(IF(MID(`a`.`tgl_nota`,6,2) = '11', a.`bayar`,0)) AS `bln11`,
  SUM(IF(MID(`a`.`tgl_nota`,6,2) = '12', a.`bayar`,0)) AS `bln12`
  FROM v_income_rajal AS a JOIN penjab AS b ON a.`kd_pj`=b.`kd_pj`
  WHERE LEFT(a.`tgl_nota`,7) BETWEEN '$awal' AND '$akhir'
  GROUP BY a.`kd_pj`");
			return $q->result();
		}
		
		function getRekapPendapatanPerbulanRI($awal, $akhir)
		{
			$q=$this->dbhost->query("SELECT a.`kd_pj`,b.`png_jawab`,
  SUM(IF(MID(`a`.`tgl_nota`,6,2) = '01', a.`bayar`,0)) AS `bln1`,  
  SUM(IF(MID(`a`.`tgl_nota`,6,2) = '02', a.`bayar`,0)) AS `bln2`,
  SUM(IF(MID(`a`.`tgl_nota`,6,2) = '03', a.`bayar`,0)) AS `bln3`,
  SUM(IF(MID(`a`.`tgl_nota`,6,2) = '04', a.`bayar`,0)) AS `bln4`,
  SUM(IF(MID(`a`.`tgl_nota`,6,2) = '05', a.`bayar`,0)) AS `bln5`,
  SUM(IF(MID(`a`.`tgl_nota`,6,2) = '06', a.`bayar`,0)) AS `bln6`,
  SUM(IF(MID(`a`.`tgl_nota`,6,2) = '07', a.`bayar`,0)) AS `bln7`,
  SUM(IF(MID(`a`.`tgl_nota`,6,2) = '08', a.`bayar`,0)) AS `bln8`,
  SUM(IF(MID(`a`.`tgl_nota`,6,2) = '09', a.`bayar`,0)) AS `bln9`,
  SUM(IF(MID(`a`.`tgl_nota`,6,2) = '10', a.`bayar`,0)) AS `bln10`,
  SUM(IF(MID(`a`.`tgl_nota`,6,2) = '11', a.`bayar`,0)) AS `bln11`,
  SUM(IF(MID(`a`.`tgl_nota`,6,2) = '12', a.`bayar`,0)) AS `bln12`
  FROM `v_income_ranap` AS a JOIN penjab AS b ON a.`kd_pj`=b.`kd_pj`
  WHERE LEFT(a.`tgl_nota`,7) BETWEEN '$awal' AND '$akhir'
  GROUP BY a.`kd_pj`");
			return $q->result();
		}
		
		function getRekapPendapatanRajalByDokter($tglawal,$tglakhir)
		{
			/* $q=$this->dbhost->query("SELECT a.`kd_dokter`,b.`nm_dokter`,
 COUNT(IF(a.`kd_pj`='BPJ' AND a.`nama_bayar`<>'Tunai',a.`no_rawat`,NULL)) AS px_bpjs,
 COUNT(IF(a.`kd_pj`='BPJ' AND a.`nama_bayar`='Tunai',a.`no_rawat`,NULL)) AS px_exesbpjs,
 COUNT(IF(a.`kd_pj`='-' OR a.`kd_pj`='1',a.`no_rawat`,NULL)) AS px_umum,
 COUNT(IF(a.`kd_pj`<>'BPJ' AND a.`kd_pj`<>'-' AND a.`kd_pj`<>'1' AND a.`nama_bayar`<>'Tunai',a.`no_rawat`,NULL)) AS px_lainnya,
 COUNT(IF(a.`kd_pj`<>'BPJ' AND a.`kd_pj`<>'-' AND a.`kd_pj`<>'1' AND a.`nama_bayar`='Tunai',a.`no_rawat`,NULL)) AS px_exesas,
 COUNT(a.`no_rawat`) AS px_jml,
 SUM(IF(a.`kd_pj`='BPJ'AND a.`nama_bayar`<> 'Tunai',a.`bayar`,0)) AS bpjs,
 SUM(IF(a.`kd_pj`='BPJ' AND a.`nama_bayar`= 'Tunai',a.`bayar`,0)) AS exes_bpjs,
 SUM(IF(a.`kd_pj`='-' OR a.`kd_pj`='1',a.`bayar`,0)) AS umum,
 SUM(IF(a.`kd_pj`<>'BPJ' AND a.`kd_pj`<>'-' AND a.`kd_pj`<>'1' AND a.`nama_bayar`<>'Tunai',a.`bayar`,0)) AS lainnya,
 SUM(IF(a.`kd_pj`<>'BPJ' AND a.`kd_pj`<>'-' AND a.`kd_pj`<>'1' AND a.`nama_bayar`='Tunai',a.`bayar`,0)) AS exes_lainnya,
 SUM(a.`bayar`) AS subtotal
 FROM v_income_rajal AS a
 LEFT JOIN dokter AS b ON a.`kd_dokter`=b.`kd_dokter`
 WHERE a.`tgl_nota` BETWEEN '$tglawal' AND '$tglakhir'
 GROUP BY a.`kd_dokter` ORDER BY b.`nm_dokter` ;"); */
			$q=$this->dbhost->query("SELECT a.`kd_dokter`,b.`nm_dokter`,
 COUNT(IF(a.`kd_pj`='BPJ' AND a.`nama_bayar`<>'Tunai' AND a.`nama_bayar`<>'Bank Centra Asia (BCA)' AND a.`nama_bayar`<>'Bank Muamalat Indonesia (BMI)' AND a.`nama_bayar`<>'Bank Rakyat Indonesia (BRI)' AND a.`nama_bayar`<>'Kas Pengeluaran' AND a.`nama_bayar`<>'QRIS Bank Syariah Indonesia (BSI)' AND a.`nama_bayar` <>'Piutang Pasien Umum' AND a.`nama_bayar`<>'Piutang Karyawan',a.`no_rawat`,NULL)) AS px_bpjs,
 COUNT(DISTINCT(IF(a.`kd_pj`='BPJ' AND (a.`nama_bayar`='Tunai' OR a.`nama_bayar`='Bank Centra Asia (BCA)' OR a.`nama_bayar`='Bank Muamalat Indonesia (BMI)' OR a.`nama_bayar`='Bank Rakyat Indonesia (BRI)' OR a.`nama_bayar`='Kas Pengeluaran' OR a.`nama_bayar`='QRIS Bank Syariah Indonesia (BSI)'),a.`no_rawat`,NULL))) AS px_exesbpjs,
 COUNT(DISTINCT(IF((a.`kd_pj`='-' OR a.`kd_pj`='1') AND (a.`nama_bayar` <>'Piutang Pasien Umum' AND a.`nama_bayar`<>'Piutang Karyawan'),a.`no_rawat`,NULL))) AS px_umum,
 COUNT(IF(((a.`kd_pj`<>'BPJ' AND a.`kd_pj`<>'-' AND a.`kd_pj`<>'1') AND (a.`nama_bayar`<>'Tunai' AND a.`nama_bayar`<>'Bank Centra Asia (BCA)' AND a.`nama_bayar`<>'Bank Muamalat Indonesia (BMI)' AND a.`nama_bayar`<>'Bank Rakyat Indonesia (BRI)' AND a.`nama_bayar`<>'Kas Pengeluaran' AND a.`nama_bayar`<>'QRIS Bank Syariah Indonesia (BSI)') OR (a.`nama_bayar` ='Piutang Pasien Umum' OR a.`nama_bayar`='Piutang Karyawan')),a.`no_rawat`,NULL)) AS px_lainnya,
 COUNT(IF(a.`kd_pj`<>'BPJ' AND a.`kd_pj`<>'-' AND a.`kd_pj`<>'1' AND (a.`nama_bayar`='Tunai' OR a.`nama_bayar`='Bank Centra Asia (BCA)' OR a.`nama_bayar`='Bank Muamalat Indonesia (BMI)' OR a.`nama_bayar`='Bank Rakyat Indonesia (BRI)' OR a.`nama_bayar`='Kas Pengeluaran' OR a.`nama_bayar`='QRIS Bank Syariah Indonesia (BSI)'),a.`no_rawat`,NULL)) AS px_exesas,
 COUNT(DISTINCT(a.`no_rawat`)) AS px_jml,
 SUM(IF(a.`kd_pj`='BPJ'AND a.`nama_bayar`<>'Tunai' AND a.`nama_bayar`<>'Bank Centra Asia (BCA)' AND a.`nama_bayar`<>'Bank Muamalat Indonesia (BMI)' AND a.`nama_bayar`<>'Bank Rakyat Indonesia (BRI)' AND a.`nama_bayar`<>'Kas Pengeluaran' AND a.`nama_bayar`<>'QRIS Bank Syariah Indonesia (BSI)' AND a.`nama_bayar` <>'Piutang Pasien Umum' AND a.`nama_bayar`<>'Piutang Karyawan',a.`bayar`,0)) AS bpjs,
 SUM(IF(a.`kd_pj`='BPJ' AND (a.`nama_bayar`='Tunai' OR a.`nama_bayar`='Bank Centra Asia (BCA)' OR a.`nama_bayar`='Bank Muamalat Indonesia (BMI)' OR a.`nama_bayar`='Bank Rakyat Indonesia (BRI)' OR a.`nama_bayar`='Kas Pengeluaran' OR a.`nama_bayar`='QRIS Bank Syariah Indonesia (BSI)'),a.`bayar`,0)) AS exes_bpjs,
 SUM(IF((a.`kd_pj`='-' OR a.`kd_pj`='1') AND (a.`nama_bayar` <>'Piutang Pasien Umum' AND a.`nama_bayar`<>'Piutang Karyawan'),a.`bayar`,0)) AS umum,
 SUM(IF(((a.`kd_pj`<>'BPJ' AND a.`kd_pj`<>'-' AND a.`kd_pj`<>'1') AND (a.`nama_bayar`<>'Tunai' AND a.`nama_bayar`<>'Bank Centra Asia (BCA)' AND a.`nama_bayar`<>'Bank Muamalat Indonesia (BMI)' AND a.`nama_bayar`<>'Bank Rakyat Indonesia (BRI)' AND a.`nama_bayar`<>'Kas Pengeluaran' AND a.`nama_bayar`<>'QRIS Bank Syariah Indonesia (BSI)') OR (a.`nama_bayar` ='Piutang Pasien Umum' OR a.`nama_bayar`='Piutang Karyawan')),a.`bayar`,0)) AS lainnya,
 SUM(IF(a.`kd_pj`<>'BPJ' AND a.`kd_pj`<>'-' AND a.`kd_pj`<>'1' AND (a.`nama_bayar`='Tunai' OR a.`nama_bayar`='Bank Centra Asia (BCA)' OR a.`nama_bayar`='Bank Muamalat Indonesia (BMI)' OR a.`nama_bayar`='Bank Rakyat Indonesia (BRI)' OR a.`nama_bayar`='Kas Pengeluaran' OR a.`nama_bayar`='QRIS Bank Syariah Indonesia (BSI)'),a.`bayar`,0)) AS exes_lainnya,
 SUM(a.`bayar`) AS subtotal
 FROM v_income_rajal AS a
 LEFT JOIN dokter AS b ON a.`kd_dokter`=b.`kd_dokter`
 WHERE a.`tgl_nota` BETWEEN '$tglawal' AND '$tglakhir'
 GROUP BY a.`kd_dokter` ORDER BY b.`nm_dokter` ;");
			return $q->result();
		}
		
		function getDataDetailPxfromPendapatanByDokter($kode,$tgl,$tglb,$pilih,$grop,$pj)
		{			
			$q=$this->dbhost->query("SELECT a.`no_rawat`,a.`tgl_nota`, a.`no_nota`,a.`no_rkm_medis`, b.`nm_pasien`,c.`nm_poli`, d.`nm_dokter`, a.`kd_pj`,e.`png_jawab`,a.`status_bayar`, a.`status`,$pilih
   FROM v_income_rajal AS a
 JOIN pasien AS b ON a.`no_rkm_medis`=b.`no_rkm_medis`
 LEFT JOIN poliklinik AS c ON a.`kd_poli`=c.`kd_poli`
 LEFT JOIN dokter AS d ON a.`kd_dokter`=d.`kd_dokter`
 LEFT JOIN penjab AS e ON a.`kd_pj`=e.`kd_pj`
 WHERE a.`kd_dokter`='$kode' AND (a.`tgl_nota` BETWEEN '$tgl' AND '$tglb') $pj $grop;");
			return $q->result();
		}
		
		function getDataDetailPxfromPendapatanByDokterAll($kode,$tgl,$tglb)
		{			
			$q=$this->dbhost->query("SELECT a.`no_rawat`,a.`tgl_nota`, a.`no_nota`,a.`no_rkm_medis`, b.`nm_pasien`,c.`nm_poli`, d.`nm_dokter`, a.`kd_pj`,e.`png_jawab`,a.`status_bayar`, a.`status`,
 a.`bayar` AS bayar, a.`nama_bayar`
 FROM v_income_rajal AS a
 JOIN pasien AS b ON a.`no_rkm_medis`=b.`no_rkm_medis`
 LEFT JOIN poliklinik AS c ON a.`kd_poli`=c.`kd_poli`
 LEFT JOIN dokter AS d ON a.`kd_dokter`=d.`kd_dokter`
 LEFT JOIN penjab AS e ON a.`kd_pj`=e.`kd_pj`
 WHERE a.`kd_dokter`='$kode' AND (a.`tgl_nota` BETWEEN '$tgl' AND '$tglb') AND a.`kd_pj`='BPJ' AND a.`nama_bayar`<>'Tunai' AND a.`nama_bayar`<>'Bank Centra Asia (BCA)' AND a.`nama_bayar`<>'Bank Muamalat Indonesia (BMI)' AND a.`nama_bayar`<>'Bank Rakyat Indonesia (BRI)' AND a.`nama_bayar`<>'Kas Pengeluaran' AND a.`nama_bayar`<>'QRIS Bank Syariah Indonesia (BSI)' AND a.`nama_bayar` <>'Piutang Pasien Umum' AND a.`nama_bayar`<>'Piutang Karyawan'
 UNION
 SELECT a.`no_rawat`,a.`tgl_nota`, a.`no_nota`,a.`no_rkm_medis`, b.`nm_pasien`,c.`nm_poli`, d.`nm_dokter`, a.`kd_pj`,e.`png_jawab`,a.`status_bayar`, a.`status`,
 a.`bayar` AS bayar, a.`nama_bayar`
 FROM v_income_rajal AS a
 JOIN pasien AS b ON a.`no_rkm_medis`=b.`no_rkm_medis`
 LEFT JOIN poliklinik AS c ON a.`kd_poli`=c.`kd_poli`
 LEFT JOIN dokter AS d ON a.`kd_dokter`=d.`kd_dokter`
 LEFT JOIN penjab AS e ON a.`kd_pj`=e.`kd_pj`
 WHERE a.`kd_dokter`='$kode' AND (a.`tgl_nota` BETWEEN '$tgl' AND '$tglb') AND ((a.`kd_pj`<>'BPJ' AND a.`kd_pj`<>'-' AND a.`kd_pj`<>'1') AND (a.`nama_bayar`<>'Tunai' AND a.`nama_bayar`<>'Bank Centra Asia (BCA)' AND a.`nama_bayar`<>'Bank Muamalat Indonesia (BMI)' AND a.`nama_bayar`<>'Bank Rakyat Indonesia (BRI)' AND a.`nama_bayar`<>'Kas Pengeluaran' AND a.`nama_bayar`<>'QRIS Bank Syariah Indonesia (BSI)') OR (a.`nama_bayar` ='Piutang Pasien Umum' OR a.`nama_bayar`='Piutang Karyawan'))
 UNION
 SELECT a.`no_rawat`,a.`tgl_nota`, a.`no_nota`,a.`no_rkm_medis`, b.`nm_pasien`,c.`nm_poli`, d.`nm_dokter`,a.`kd_pj`, e.`png_jawab`,a.`status_bayar` , a.`status`,SUM(a.`bayar`) AS bayar,GROUP_CONCAT(a.`nama_bayar`) AS nama_bayar
 FROM v_income_rajal AS a
 JOIN pasien AS b ON a.`no_rkm_medis`=b.`no_rkm_medis`
 LEFT JOIN poliklinik AS c ON a.`kd_poli`=c.`kd_poli`
 LEFT JOIN dokter AS d ON a.`kd_dokter`=d.`kd_dokter`
 LEFT JOIN penjab AS e ON a.`kd_pj`=e.`kd_pj`
 WHERE a.`kd_dokter`='$kode' AND (a.`tgl_nota` BETWEEN '$tgl' AND '$tglb') AND (a.`kd_pj`='-' OR a.`kd_pj`='1') AND (a.`nama_bayar` <>'Piutang Pasien Umum' AND a.`nama_bayar`<>'Piutang Karyawan') 
 GROUP BY a.`no_rawat` ;");
			return $q->result();
		}
		
		function getRekapPendapatanRanapByDokter($tglawal,$tglakhir)
		{
			//$q=$this->dbhost->query("SELECT a.`kd_dokter`,b.`nm_dokter`, SUM(IF(a.`kd_pj`='BPJ',a.`bayar`,'0')) AS bpjs, SUM(IF(a.`kd_pj`='-' OR a.`kd_pj`='1',a.`bayar`,'0')) AS umum, SUM(IF(a.`kd_pj`<>'BPJ' AND a.`kd_pj`<>'-' AND a.`kd_pj`<>'1',a.`bayar`,'0')) AS lainnya, SUM(a.`bayar`) AS subtotal FROM v_income_ranap AS a LEFT JOIN dokter AS b ON a.`kd_dokter`=b.`kd_dokter` WHERE a.`tgl_nota` BETWEEN '$tglawal' AND '$tglakhir' GROUP BY a.`kd_dokter` ORDER BY b.`nm_dokter` ;");
			/* $q=$this->dbhost->query("SELECT a.`kd_dokter`,b.`nm_dokter`,
 COUNT(IF(a.`kd_pj`='BPJ' AND a.`nama_bayar`<>'Tunai',a.`no_rawat`,NULL)) AS px_bpjs,
 COUNT(IF(a.`kd_pj`='BPJ' AND a.`nama_bayar`='Tunai',a.`no_rawat`,NULL)) AS px_exesbpjs,
 COUNT(IF(a.`kd_pj`='-' OR a.`kd_pj`='1',a.`no_rawat`,NULL)) AS px_umum,
 COUNT(IF(a.`kd_pj`<>'BPJ' AND a.`kd_pj`<>'-' AND a.`kd_pj`<>'1' AND a.`nama_bayar`<>'Tunai',a.`no_rawat`,NULL)) AS px_lainnya,
 COUNT(IF(a.`kd_pj`<>'BPJ' AND a.`kd_pj`<>'-' AND a.`kd_pj`<>'1' AND a.`nama_bayar`='Tunai',a.`no_rawat`,NULL)) AS px_exesas,
 COUNT(a.`no_rawat`) AS px_jml,
 SUM(IF(a.`kd_pj`='BPJ'AND a.`nama_bayar`<> 'Tunai',a.`bayar`,0)) AS bpjs,
 SUM(IF(a.`kd_pj`='BPJ' AND a.`nama_bayar`= 'Tunai',a.`bayar`,0)) AS exes_bpjs,
 SUM(IF(a.`kd_pj`='-' OR a.`kd_pj`='1',a.`bayar`,0)) AS umum,
 SUM(IF(a.`kd_pj`<>'BPJ' AND a.`kd_pj`<>'-' AND a.`kd_pj`<>'1' AND a.`nama_bayar`<>'Tunai',a.`bayar`,0)) AS lainnya,
 SUM(IF(a.`kd_pj`<>'BPJ' AND a.`kd_pj`<>'-' AND a.`kd_pj`<>'1' AND a.`nama_bayar`='Tunai',a.`bayar`,0)) AS exes_lainnya,
 SUM(a.`bayar`) AS subtotal FROM v_income_ranap AS a LEFT JOIN dokter AS b ON a.`kd_dokter`=b.`kd_dokter` WHERE a.`tgl_nota` BETWEEN '$tglawal' AND '$tglakhir' GROUP BY a.`kd_dokter` ORDER BY b.`nm_dokter` ;"); */
			$q=$this->dbhost->query("SELECT a.`kd_dokter`,b.`nm_dokter`,
 COUNT(IF(a.`kd_pj`='BPJ' AND a.`nama_bayar`<>'Tunai' AND a.`nama_bayar`<>'Bank Centra Asia (BCA)' AND a.`nama_bayar`<>'Bank Muamalat Indonesia (BMI)' AND a.`nama_bayar`<>'Bank Rakyat Indonesia (BRI)' AND a.`nama_bayar`<>'Kas Pengeluaran' AND a.`nama_bayar`<>'QRIS Bank Syariah Indonesia (BSI)' AND a.`nama_bayar` <>'Piutang Pasien Umum' AND a.`nama_bayar`<>'Piutang Karyawan',a.`no_rawat`,NULL)) AS px_bpjs,
 COUNT(DISTINCT(IF(a.`kd_pj`='BPJ' AND (a.`nama_bayar`='Tunai' OR a.`nama_bayar`='Bank Centra Asia (BCA)' OR a.`nama_bayar`='Bank Muamalat Indonesia (BMI)' OR a.`nama_bayar`='Bank Rakyat Indonesia (BRI)' OR a.`nama_bayar`='Kas Pengeluaran' OR a.`nama_bayar`='QRIS Bank Syariah Indonesia (BSI)'),a.`no_rawat`,NULL))) AS px_exesbpjs,
 COUNT(DISTINCT(IF((a.`kd_pj`='-' OR a.`kd_pj`='1') AND (a.`nama_bayar` <>'Piutang Pasien Umum' AND a.`nama_bayar`<>'Piutang Karyawan') OR a.`nama_bayar` IS NULL,a.`no_rawat`,NULL))) AS px_umum,
 COUNT(IF(((a.`kd_pj`<>'BPJ' AND a.`kd_pj`<>'-' AND a.`kd_pj`<>'1') AND (a.`nama_bayar`<>'Tunai' AND a.`nama_bayar`<>'Bank Centra Asia (BCA)' AND a.`nama_bayar`<>'Bank Muamalat Indonesia (BMI)' AND a.`nama_bayar`<>'Bank Rakyat Indonesia (BRI)' AND a.`nama_bayar`<>'Kas Pengeluaran' AND a.`nama_bayar`<>'QRIS Bank Syariah Indonesia (BSI)') OR (a.`nama_bayar` ='Piutang Pasien Umum' OR a.`nama_bayar`='Piutang Karyawan')),a.`no_rawat`,NULL)) AS px_lainnya,
 COUNT(IF(a.`kd_pj`<>'BPJ' AND a.`kd_pj`<>'-' AND a.`kd_pj`<>'1' AND (a.`nama_bayar`='Tunai' OR a.`nama_bayar`='Bank Centra Asia (BCA)' OR a.`nama_bayar`='Bank Muamalat Indonesia (BMI)' OR a.`nama_bayar`='Bank Rakyat Indonesia (BRI)' OR a.`nama_bayar`='Kas Pengeluaran' OR a.`nama_bayar`='QRIS Bank Syariah Indonesia (BSI)'),a.`no_rawat`,NULL)) AS px_exesas,
 COUNT(DISTINCT(a.`no_rawat`)) AS px_jml,
 SUM(IF(a.`kd_pj`='BPJ'AND a.`nama_bayar`<>'Tunai' AND a.`nama_bayar`<>'Bank Centra Asia (BCA)' AND a.`nama_bayar`<>'Bank Muamalat Indonesia (BMI)' AND a.`nama_bayar`<>'Bank Rakyat Indonesia (BRI)' AND a.`nama_bayar`<>'Kas Pengeluaran' AND a.`nama_bayar`<>'QRIS Bank Syariah Indonesia (BSI)' AND a.`nama_bayar` <>'Piutang Pasien Umum' AND a.`nama_bayar`<>'Piutang Karyawan',a.`bayar`,0)) AS bpjs,
 SUM(IF(a.`kd_pj`='BPJ' AND (a.`nama_bayar`='Tunai' OR a.`nama_bayar`='Bank Centra Asia (BCA)' OR a.`nama_bayar`='Bank Muamalat Indonesia (BMI)' OR a.`nama_bayar`='Bank Rakyat Indonesia (BRI)' OR a.`nama_bayar`='Kas Pengeluaran' OR a.`nama_bayar`='QRIS Bank Syariah Indonesia (BSI)'),a.`bayar`,0)) AS exes_bpjs,
 SUM(IF((a.`kd_pj`='-' OR a.`kd_pj`='1') AND (a.`nama_bayar` <>'Piutang Pasien Umum' AND a.`nama_bayar`<>'Piutang Karyawan') OR a.`nama_bayar` IS NULL,a.`bayar`,0)) AS umum,
 SUM(IF(((a.`kd_pj`<>'BPJ' AND a.`kd_pj`<>'-' AND a.`kd_pj`<>'1') AND (a.`nama_bayar`<>'Tunai' AND a.`nama_bayar`<>'Bank Centra Asia (BCA)' AND a.`nama_bayar`<>'Bank Muamalat Indonesia (BMI)' AND a.`nama_bayar`<>'Bank Rakyat Indonesia (BRI)' AND a.`nama_bayar`<>'Kas Pengeluaran' AND a.`nama_bayar`<>'QRIS Bank Syariah Indonesia (BSI)') OR (a.`nama_bayar` ='Piutang Pasien Umum' OR a.`nama_bayar`='Piutang Karyawan')),a.`bayar`,0)) AS lainnya,
 SUM(IF(a.`kd_pj`<>'BPJ' AND a.`kd_pj`<>'-' AND a.`kd_pj`<>'1' AND (a.`nama_bayar`='Tunai' OR a.`nama_bayar`='Bank Centra Asia (BCA)' OR a.`nama_bayar`='Bank Muamalat Indonesia (BMI)' OR a.`nama_bayar`='Bank Rakyat Indonesia (BRI)' OR a.`nama_bayar`='Kas Pengeluaran' OR a.`nama_bayar`='QRIS Bank Syariah Indonesia (BSI)'),a.`bayar`,0)) AS exes_lainnya,
 SUM(a.`bayar`) AS subtotal FROM v_income_ranap AS a LEFT JOIN dokter AS b ON a.`kd_dokter`=b.`kd_dokter` WHERE a.`tgl_nota` BETWEEN '$tglawal' AND '$tglakhir' GROUP BY a.`kd_dokter` ORDER BY b.`nm_dokter` ;");
			
			return $q->result();
		}
		
		function getDataDetailPxfromPendapatanByDokterRanap($kode,$tgl,$tglb,$pilih,$grop,$pj)
		{			
			$q=$this->dbhost->query("SELECT a.`no_rawat`,a.`tgl_nota`, a.`no_nota`,a.`no_rkm_medis`, b.`nm_pasien`,c.`nm_poli`, d.`nm_dokter`, a.`kd_pj`,e.`png_jawab`,a.`status_bayar`, a.`status`,$pilih
   FROM v_income_ranap AS a
 JOIN pasien AS b ON a.`no_rkm_medis`=b.`no_rkm_medis`
 LEFT JOIN poliklinik AS c ON a.`kd_poli`=c.`kd_poli`
 LEFT JOIN dokter AS d ON a.`kd_dokter`=d.`kd_dokter`
 LEFT JOIN penjab AS e ON a.`kd_pj`=e.`kd_pj`
 WHERE a.`kd_dokter`='$kode' AND (a.`tgl_nota` BETWEEN '$tgl' AND '$tglb') $pj $grop;");
			return $q->result();
		}
		
		function getDataDetailPxfromPendapatanByDokterRanapAll($kode,$tgl,$tglb)
		{			
			$q=$this->dbhost->query("SELECT a.`no_rawat`,a.`tgl_nota`, a.`no_nota`,a.`no_rkm_medis`, b.`nm_pasien`,c.`nm_poli`, d.`nm_dokter`, a.`kd_pj`,e.`png_jawab`,a.`status_bayar`, a.`status`,
 a.`bayar` AS bayar, a.`nama_bayar`
 FROM v_income_ranap AS a
 JOIN pasien AS b ON a.`no_rkm_medis`=b.`no_rkm_medis`
 LEFT JOIN poliklinik AS c ON a.`kd_poli`=c.`kd_poli`
 LEFT JOIN dokter AS d ON a.`kd_dokter`=d.`kd_dokter`
 LEFT JOIN penjab AS e ON a.`kd_pj`=e.`kd_pj`
 WHERE a.`kd_dokter`='$kode' AND (a.`tgl_nota` BETWEEN '$tgl' AND '$tglb') AND a.`kd_pj`='BPJ' AND a.`nama_bayar`<>'Tunai' AND a.`nama_bayar`<>'Bank Centra Asia (BCA)' AND a.`nama_bayar`<>'Bank Muamalat Indonesia (BMI)' AND a.`nama_bayar`<>'Bank Rakyat Indonesia (BRI)' AND a.`nama_bayar`<>'Kas Pengeluaran' AND a.`nama_bayar`<>'QRIS Bank Syariah Indonesia (BSI)' AND a.`nama_bayar` <>'Piutang Pasien Umum' AND a.`nama_bayar`<>'Piutang Karyawan'
 UNION
 SELECT a.`no_rawat`,a.`tgl_nota`, a.`no_nota`,a.`no_rkm_medis`, b.`nm_pasien`,c.`nm_poli`, d.`nm_dokter`, a.`kd_pj`,e.`png_jawab`,a.`status_bayar`, a.`status`,
 a.`bayar` AS bayar, a.`nama_bayar`
 FROM v_income_ranap AS a
 JOIN pasien AS b ON a.`no_rkm_medis`=b.`no_rkm_medis`
 LEFT JOIN poliklinik AS c ON a.`kd_poli`=c.`kd_poli`
 LEFT JOIN dokter AS d ON a.`kd_dokter`=d.`kd_dokter`
 LEFT JOIN penjab AS e ON a.`kd_pj`=e.`kd_pj`
 WHERE a.`kd_dokter`='$kode' AND (a.`tgl_nota` BETWEEN '$tgl' AND '$tglb') AND ((a.`kd_pj`<>'BPJ' AND a.`kd_pj`<>'-' AND a.`kd_pj`<>'1') AND (a.`nama_bayar`<>'Tunai' AND a.`nama_bayar`<>'Bank Centra Asia (BCA)' AND a.`nama_bayar`<>'Bank Muamalat Indonesia (BMI)' AND a.`nama_bayar`<>'Bank Rakyat Indonesia (BRI)' AND a.`nama_bayar`<>'Kas Pengeluaran' AND a.`nama_bayar`<>'QRIS Bank Syariah Indonesia (BSI)') OR (a.`nama_bayar` ='Piutang Pasien Umum' OR a.`nama_bayar`='Piutang Karyawan'))
 UNION
 SELECT a.`no_rawat`,a.`tgl_nota`, a.`no_nota`,a.`no_rkm_medis`, b.`nm_pasien`,c.`nm_poli`, d.`nm_dokter`,a.`kd_pj`, e.`png_jawab`,a.`status_bayar` , a.`status`,SUM(a.`bayar`) AS bayar,GROUP_CONCAT(a.`nama_bayar`) AS nama_bayar
 FROM v_income_ranap AS a
 JOIN pasien AS b ON a.`no_rkm_medis`=b.`no_rkm_medis`
 LEFT JOIN poliklinik AS c ON a.`kd_poli`=c.`kd_poli`
 LEFT JOIN dokter AS d ON a.`kd_dokter`=d.`kd_dokter`
 LEFT JOIN penjab AS e ON a.`kd_pj`=e.`kd_pj`
 WHERE a.`kd_dokter`='$kode' AND (a.`tgl_nota` BETWEEN '$tgl' AND '$tglb') AND ((a.`kd_pj`='-' OR a.`kd_pj`='1') AND (a.`nama_bayar` <>'Piutang Pasien Umum' AND a.`nama_bayar`<>'Piutang Karyawan') OR a.`nama_bayar` IS NULL) 
 GROUP BY a.`no_rawat` ;");
			return $q->result();
		}
		
		function getRekapPendapatanRanapByKelasPerawatan($tglawal,$tglakhir)
		{
			$q=$this->dbhost->query(" SELECT a.`tgl_nota`,
 COUNT(IF(b.`kelas`='Kelas VIP',a.`no_rawat`,NULL)) AS pxvip,
 COUNT(IF(b.`kelas`='Kelas 1',a.`no_rawat`,NULL)) AS px1,
 COUNT(IF(b.`kelas`='Kelas 2',a.`no_rawat`,NULL)) AS px2,
 COUNT(IF(b.`kelas`='Kelas 3',a.`no_rawat`,NULL)) AS px3,
 COUNT(a.`no_rawat`) AS jmlpx,
 SUM(IF(b.`kelas`='Kelas VIP',a.`bayar`,0)) AS bayarvip,
 SUM(IF(b.`kelas`='Kelas 1',a.`bayar`,0)) AS bayar1,
 SUM(IF(b.`kelas`='Kelas 2',a.`bayar`,0)) AS bayar2,
 SUM(IF(b.`kelas`='Kelas 3',a.`bayar`,0)) AS bayar3,
 SUM(a.`bayar`) AS jmlbayar
 FROM v_income_ranap AS a
 JOIN kamar AS b ON a.`kd_kamar`=b.`kd_kamar`
 WHERE a.`tgl_nota` BETWEEN '$tglawal' AND '$tglakhir'
 GROUP BY a.`tgl_nota` ;");
			return $q->result();
		}
		
		function getPendapatanByKamarRanap($tglawal,$tglakhir)
		{			
			$q=$this->dbhost->query("SELECT c.`nm_bangsal`,LEFT(a.`kd_kamar`,5) AS kode,b.`kelas`,
 COUNT(IF(a.`kd_pj`='BPJ' AND a.`nama_bayar`<>'Tunai' AND a.`nama_bayar`<>'Bank Centra Asia (BCA)' AND a.`nama_bayar`<>'Bank Muamalat Indonesia (BMI)' AND a.`nama_bayar`<>'Bank Rakyat Indonesia (BRI)' AND a.`nama_bayar`<>'Kas Pengeluaran' AND a.`nama_bayar`<>'QRIS Bank Syariah Indonesia (BSI)' AND a.`nama_bayar` <>'Piutang Pasien Umum' AND a.`nama_bayar`<>'Piutang Karyawan',a.`no_rawat`,NULL)) AS px_bpjs,
 COUNT(DISTINCT(IF(a.`kd_pj`='BPJ' AND (a.`nama_bayar`='Tunai' OR a.`nama_bayar`='Bank Centra Asia (BCA)' OR a.`nama_bayar`='Bank Muamalat Indonesia (BMI)' OR a.`nama_bayar`='Bank Rakyat Indonesia (BRI)' OR a.`nama_bayar`='Kas Pengeluaran' OR a.`nama_bayar`='QRIS Bank Syariah Indonesia (BSI)'),a.`no_rawat`,NULL))) AS px_exesbpjs,
 COUNT(DISTINCT(IF((a.`kd_pj`='-' OR a.`kd_pj`='1') AND (a.`nama_bayar` <>'Piutang Pasien Umum' AND a.`nama_bayar`<>'Piutang Karyawan') OR a.`nama_bayar` IS NULL,a.`no_rawat`,NULL))) AS px_umum,
 COUNT(IF(a.`kd_pj`='-' OR a.`kd_pj`='1',a.`no_rawat`,NULL)) AS px_umumx,
 COUNT(IF(((a.`kd_pj`<>'BPJ' AND a.`kd_pj`<>'-' AND a.`kd_pj`<>'1') AND (a.`nama_bayar`<>'Tunai' AND a.`nama_bayar`<>'Bank Centra Asia (BCA)' AND a.`nama_bayar`<>'Bank Muamalat Indonesia (BMI)' AND a.`nama_bayar`<>'Bank Rakyat Indonesia (BRI)' AND a.`nama_bayar`<>'Kas Pengeluaran' AND a.`nama_bayar`<>'QRIS Bank Syariah Indonesia (BSI)') OR (a.`nama_bayar` ='Piutang Pasien Umum' OR a.`nama_bayar`='Piutang Karyawan')),a.`no_rawat`,NULL)) AS px_lainnya,
 COUNT(IF(a.`kd_pj`<>'BPJ' AND a.`kd_pj`<>'-' AND a.`kd_pj`<>'1' AND (a.`nama_bayar`='Tunai' OR a.`nama_bayar`='Bank Centra Asia (BCA)' OR a.`nama_bayar`='Bank Muamalat Indonesia (BMI)' OR a.`nama_bayar`='Bank Rakyat Indonesia (BRI)' OR a.`nama_bayar`='Kas Pengeluaran' OR a.`nama_bayar`='QRIS Bank Syariah Indonesia (BSI)'),a.`no_rawat`,NULL)) AS px_exesas,
 COUNT(DISTINCT(a.`no_rawat`)) AS px_jml,
 COUNT(a.`no_rawat`) AS px_jmlx,
 SUM(IF(a.`kd_pj`='BPJ' AND a.`nama_bayar`<>'Tunai' AND a.`nama_bayar`<>'Bank Centra Asia (BCA)' AND a.`nama_bayar`<>'Bank Muamalat Indonesia (BMI)' AND a.`nama_bayar`<>'Bank Rakyat Indonesia (BRI)' AND a.`nama_bayar`<>'Kas Pengeluaran' AND a.`nama_bayar`<>'QRIS Bank Syariah Indonesia (BSI)' AND a.`nama_bayar` <>'Piutang Pasien Umum' AND a.`nama_bayar`<>'Piutang Karyawan',a.`bayar`,0)) AS bpjs,
 SUM(IF(a.`kd_pj`='BPJ' AND (a.`nama_bayar`='Tunai' OR a.`nama_bayar`='Bank Centra Asia (BCA)' OR a.`nama_bayar`='Bank Muamalat Indonesia (BMI)' OR a.`nama_bayar`='Bank Rakyat Indonesia (BRI)' OR a.`nama_bayar`='Kas Pengeluaran' OR a.`nama_bayar`='QRIS Bank Syariah Indonesia (BSI)'),a.`bayar`,0)) AS exes_bpjs,
 SUM(IF((a.`kd_pj`='-' OR a.`kd_pj`='1') AND (a.`nama_bayar` <>'Piutang Pasien Umum' AND a.`nama_bayar`<>'Piutang Karyawan')  OR a.`nama_bayar` IS NULL,a.`bayar`,0)) AS umum,
 SUM(IF(((a.`kd_pj`<>'BPJ' AND a.`kd_pj`<>'-' AND a.`kd_pj`<>'1') AND (a.`nama_bayar`<>'Tunai' AND a.`nama_bayar`<>'Bank Centra Asia (BCA)' AND a.`nama_bayar`<>'Bank Muamalat Indonesia (BMI)' AND a.`nama_bayar`<>'Bank Rakyat Indonesia (BRI)' AND a.`nama_bayar`<>'Kas Pengeluaran' AND a.`nama_bayar`<>'QRIS Bank Syariah Indonesia (BSI)') OR (a.`nama_bayar` ='Piutang Pasien Umum' OR a.`nama_bayar`='Piutang Karyawan')),a.`bayar`,0)) AS lainnya,
 SUM(IF(a.`kd_pj`<>'BPJ' AND a.`kd_pj`<>'-' AND a.`kd_pj`<>'1' AND (a.`nama_bayar`='Tunai' OR a.`nama_bayar`='Bank Centra Asia (BCA)' OR a.`nama_bayar`='Bank Muamalat Indonesia (BMI)' OR a.`nama_bayar`='Bank Rakyat Indonesia (BRI)' OR a.`nama_bayar`='Kas Pengeluaran' OR a.`nama_bayar`='QRIS Bank Syariah Indonesia (BSI)'),a.`bayar`,0)) AS exes_lainnya,
 SUM(a.`bayar`) AS subtotal
 FROM v_income_ranap AS a
 JOIN kamar AS b ON a.`kd_kamar`=b.`kd_kamar`
 JOIN bangsal AS c ON b.`kd_bangsal`=c.`kd_bangsal`
 WHERE a.`tgl_nota` BETWEEN '$tglawal' AND '$tglakhir'
 GROUP BY LEFT(a.`kd_kamar`,5) ;"); 
 
			return $q->result();
		}
		
		function getDataDetailPxfromPendapatanByKamar($kode,$tgl,$tglb,$pilih,$grop,$pj)
		{
			$q=$this->dbhost->query("SELECT a.`no_rawat`,a.`tgl_nota`, a.`no_nota`,a.`no_rkm_medis`, b.`nm_pasien`,c.`nm_poli`, d.`nm_dokter`, e.`png_jawab`, a.`kd_kamar`, g.`nm_bangsal`,a.`status_bayar` , a.`status`, $pilih
 FROM v_income_ranap AS a
 JOIN pasien AS b ON a.`no_rkm_medis`=b.`no_rkm_medis`
 JOIN poliklinik AS c ON a.`kd_poli`=c.`kd_poli`
 JOIN dokter AS d ON a.`kd_dokter`=d.`kd_dokter`
 JOIN penjab AS e ON a.`kd_pj`=e.`kd_pj`
 JOIN kamar AS f ON a.`kd_kamar`=f.`kd_kamar`
 JOIN bangsal AS g ON f.`kd_bangsal`=g.`kd_bangsal` 
 WHERE LEFT(a.`kd_kamar`,5)='$kode' AND (a.`tgl_nota` BETWEEN '$tgl' AND '$tglb') $pj $grop ;"); 
 
			return $q->result();
		}
		
		function getDataDetailPxfromPendapatanByKamarAll($kode,$tgl,$tglb)
		{
			$q=$this->dbhost->query("SELECT a.`no_rawat`,a.`tgl_nota`, a.`no_nota`,a.`no_rkm_medis`, b.`nm_pasien`,c.`nm_poli`, d.`nm_dokter`, e.`png_jawab`, a.`kd_kamar`, g.`nm_bangsal`,a.`status_bayar` , a.`status`,a.`bayar`,a.`nama_bayar`
 FROM v_income_ranap AS a
 JOIN pasien AS b ON a.`no_rkm_medis`=b.`no_rkm_medis`
 JOIN poliklinik AS c ON a.`kd_poli`=c.`kd_poli`
 JOIN dokter AS d ON a.`kd_dokter`=d.`kd_dokter`
 JOIN penjab AS e ON a.`kd_pj`=e.`kd_pj`
 JOIN kamar AS f ON a.`kd_kamar`=f.`kd_kamar`
 JOIN bangsal AS g ON f.`kd_bangsal`=g.`kd_bangsal` 
 WHERE LEFT(a.`kd_kamar`,5)='$kode' AND (a.`tgl_nota` BETWEEN '$tgl' AND '$tglb') AND a.`kd_pj`='BPJ' AND a.`nama_bayar`<>'Tunai' AND a.`nama_bayar`<>'Bank Centra Asia (BCA)' AND a.`nama_bayar`<>'Bank Muamalat Indonesia (BMI)' AND a.`nama_bayar`<>'Bank Rakyat Indonesia (BRI)' AND a.`nama_bayar`<>'Kas Pengeluaran' AND a.`nama_bayar`<>'QRIS Bank Syariah Indonesia (BSI)' AND a.`nama_bayar` <>'Piutang Pasien Umum' AND a.`nama_bayar`<>'Piutang Karyawan'
 UNION
 SELECT a.`no_rawat`,a.`tgl_nota`, a.`no_nota`,a.`no_rkm_medis`, b.`nm_pasien`,c.`nm_poli`, d.`nm_dokter`, e.`png_jawab`, a.`kd_kamar`, g.`nm_bangsal`,a.`status_bayar` , a.`status`,a.`bayar`,a.`nama_bayar`
 FROM v_income_ranap AS a
 JOIN pasien AS b ON a.`no_rkm_medis`=b.`no_rkm_medis`
 JOIN poliklinik AS c ON a.`kd_poli`=c.`kd_poli`
 JOIN dokter AS d ON a.`kd_dokter`=d.`kd_dokter`
 JOIN penjab AS e ON a.`kd_pj`=e.`kd_pj`
 JOIN kamar AS f ON a.`kd_kamar`=f.`kd_kamar`
 JOIN bangsal AS g ON f.`kd_bangsal`=g.`kd_bangsal` 
 WHERE LEFT(a.`kd_kamar`,5)='$kode' AND (a.`tgl_nota` BETWEEN '$tgl' AND '$tglb') AND ((a.`kd_pj`<>'BPJ' AND a.`kd_pj`<>'-' AND a.`kd_pj`<>'1') AND (a.`nama_bayar`<>'Tunai' AND a.`nama_bayar`<>'Bank Centra Asia (BCA)' AND a.`nama_bayar`<>'Bank Muamalat Indonesia (BMI)' AND a.`nama_bayar`<>'Bank Rakyat Indonesia (BRI)' AND a.`nama_bayar`<>'Kas Pengeluaran' AND a.`nama_bayar`<>'QRIS Bank Syariah Indonesia (BSI)') OR (a.`nama_bayar` ='Piutang Pasien Umum' OR a.`nama_bayar`='Piutang Karyawan'))
 UNION
 SELECT a.`no_rawat`,a.`tgl_nota`, a.`no_nota`,a.`no_rkm_medis`, b.`nm_pasien`,c.`nm_poli`, d.`nm_dokter`, e.`png_jawab`,a.`kd_kamar`, g.`nm_bangsal`,a.`status_bayar` , a.`status`,SUM(a.`bayar`) AS bayar,GROUP_CONCAT(a.`nama_bayar`) AS nama_bayar
 FROM v_income_ranap AS a
 JOIN pasien AS b ON a.`no_rkm_medis`=b.`no_rkm_medis`
 JOIN poliklinik AS c ON a.`kd_poli`=c.`kd_poli`
 JOIN dokter AS d ON a.`kd_dokter`=d.`kd_dokter`
 JOIN penjab AS e ON a.`kd_pj`=e.`kd_pj`
 JOIN kamar AS f ON a.`kd_kamar`=f.`kd_kamar`
 JOIN bangsal AS g ON f.`kd_bangsal`=g.`kd_bangsal` 
 WHERE LEFT(a.`kd_kamar`,5)='$kode' AND (a.`tgl_nota` BETWEEN '$tgl' AND '$tglb') AND ((a.`kd_pj`='-' OR a.`kd_pj`='1') AND (a.`nama_bayar` <>'Piutang Pasien Umum' AND a.`nama_bayar`<>'Piutang Karyawan') OR a.`nama_bayar` IS NULL) 
 GROUP BY a.`no_rawat`"); 
 
			return $q->result();
		}
		
		function getPendapatanByDiagnosa($tglawal,$tglakhir,$pj)
		{
			//$q=$this->dbhost->query("SELECT a.`no_rawat`,a.`no_rkm_medis`, a.`nm_pasien`, a.`png_jawab`, a.`nm_poli`,a.`tgl_registrasi` AS tgl_masuk, a.`tgl_registrasi` AS tgl_keluar, a.`nm_dokter`, b.`kd_penyakit`,GROUP_CONCAT(b.`prioritas` SEPARATOR '|') AS prioritas,GROUP_CONCAT(b.`nm_penyakit` SEPARATOR '|') AS penyakit  FROM v_registrasi AS a  LEFT JOIN v_diagnosa AS b ON a.`no_rawat`=b.`no_rawat`  WHERE a.`status_lanjut`='ralan' AND a.`stts` NOT IN('batal','Belum') AND $pj a.`tgl_registrasi` BETWEEN '$tglawal' AND '$tglakhir' GROUP BY a.`no_rawat` ;"); 
			$q=$this->dbhost->query("SELECT a.`no_rawat`,a.`no_rkm_medis`, a.`nm_pasien`,a.`stts`, a.`png_jawab`,c.`nama_bayar`,c.`bayar` AS besar_bayar, a.`nm_poli`,a.`tgl_registrasi` AS tgl_masuk, a.`tgl_registrasi` AS tgl_keluar, a.`nm_dokter`, b.`kd_penyakit`,GROUP_CONCAT(b.`prioritas` SEPARATOR '|') AS prioritas,GROUP_CONCAT(b.`nm_penyakit` SEPARATOR '|') AS penyakit
 FROM v_registrasi AS a
 LEFT JOIN v_diagnosa AS b ON a.`no_rawat`=b.`no_rawat`
 LEFT JOIN v_income_rajal AS c ON a.`no_rawat`=c.`no_rawat`  
			WHERE a.`status_lanjut`='ralan' AND a.`stts` NOT IN('batal') AND $pj c.`tgl_nota` BETWEEN '$tglawal' AND '$tglakhir' GROUP BY a.`no_rawat` ;"); 
 
			return $q->result();
		}
		
		function getBiayaByDiagnosaRajal($tglawal,$tglakhir,$pj)
		{
			$q=$this->dbhost->query("SELECT a.`tgl_registrasi`,a.`tgl_nota`,a.`stts`,a.`no_rawat`, b.`konsultasi` ,b.`registrasi`, (b.`ralandr`+b.`ralandrpr`+b.`ralanpr`+b.`ranapdr`+b.`ranappr`+b.`ranapdrpr`) AS tindakan,
 b.`operasi`,b.`laborat`,b.`radiologi`,b.`kamar`,b.`harian`,b.`dokter`,b.`perawat` ,(b.`obat`+b.`reseppulang`+b.`returobat`) AS obat, 
 b.`service`,(b.`tambahan`+b.`potongan`) AS tampot
 FROM v_income_rajal  AS a
 JOIN v_billdiagnosa AS b ON a.`no_rawat`=b.`no_rawat`
 WHERE a.`status_lanjut`='ralan' AND $pj  a.`tgl_nota` BETWEEN '$tglawal' AND '$tglakhir' GROUP BY a.`no_rawat`;"); 
 
			if($q->num_rows()>0){
				foreach($q->result() as $i){
					$h[$i->no_rawat]=$i;
				}
				return $h;			
			}else{
				return $q->result();
			}
		}
		
		function getBiayaExsesByDiagnosaRajal($tglawal,$tglakhir)
		{
			$q=$this->dbhost->query("SELECT a.`no_nota`,a.`no_rawat`,a.`kd_pj`,a.`tgl_registrasi`,a.`tgl_nota`,a.`bayar`,b.`nama_bayar`,b.`besar_bayar` FROM v_income_rajal a 
 JOIN detail_nota_jalan AS b ON a.`no_rawat`=b.`no_rawat`
 WHERE a.`kd_pj` NOT IN('-','1') AND (a.`nama_bayar`='Tunai' OR a.`nama_bayar`='Bank Centra Asia (BCA)' OR a.`nama_bayar`='Bank Muamalat Indonesia (BMI)' OR a.`nama_bayar`='Bank Rakyat Indonesia (BRI)' OR a.`nama_bayar`='Kas Pengeluaran' OR a.`nama_bayar`='QRIS Bank Syariah Indonesia (BSI)')
 AND a.`tgl_nota` BETWEEN '$tglawal' AND '$tglakhir';"); 
 
			if($q->num_rows()>0){
				foreach($q->result() as $i){
					$h[$i->no_rawat]=$i;
				}
				return $h;			
			}else{
				return $q->result();
			}
		}
		
		//ranap
		function getPendapatanByDiagnosaRanap($tglawal,$tglakhir,$pj)
		{
			$q=$this->dbhost->query("SELECT a.`no_rawat`,a.`no_rkm_medis`,a.`nm_pasien`,a.`kd_pj`,a.`png_jawab`,a.`kelas`,a.`nm_bangsal`,a.`tgl_masuk`,a.`tgl_keluar`,a.`dokter_dpjp`, 
GROUP_CONCAT(DISTINCT(b.`prioritas`) SEPARATOR '|') AS prioritas,GROUP_CONCAT(DISTINCT(b.`nm_penyakit`) SEPARATOR '|') AS penyakit
FROM v_pasien_inap AS a
LEFT JOIN v_diagnosa AS b ON a.`no_rawat`=b.`no_rawat` 
WHERE $pj a.`tgl_keluar` BETWEEN '$tglawal' AND '$tglakhir' GROUP BY a.`no_rawat` ORDER BY a.`tgl_keluar` ;"); 
 
			return $q->result();
		}
		
		function getBiayaByDiagnosaRanap($tglawal,$tglakhir,$pj)
		{
			$q=$this->dbhost->query("SELECT a.`tgl_masuk`,a.`tgl_registrasi`,a.`stts`,a.`no_rawat`,(b.`registrasi` + b.`administrasi`) AS administrasi,b.`konsultasi`,b.`visite` ,b.`registrasi`,
 (b.`ralandr`+b.`ralandrpr`+b.`ralanpr`+b.`ranapdr`+b.`ranappr`+b.`ranapdrpr`) AS tindakan,
 b.`operasi`,b.`laborat`,b.`radiologi`,b.`kamar`,b.`visite`,b.`harian`,b.`dokter`,b.`perawat` ,(b.`obat`+b.`reseppulang`+b.`returobat`) AS obat, 
 b.`service`,(b.`tambahan`+b.`potongan`) AS tampot
 FROM v_pasien_inap  AS a
 JOIN v_billdiagnosa AS b ON a.`no_rawat`=b.`no_rawat`
 WHERE $pj a.`tgl_keluar` BETWEEN '$tglawal' AND '$tglakhir' GROUP BY a.`no_rawat`;"); 
 
			if($q->num_rows()>0){
				foreach($q->result() as $i){
					$h[$i->no_rawat]=$i;
				}
				return $h;			
			}else{
				return $q->result();
			}
		}
		
		function getBiayaExsesByDiagnosaRanap($tglawal,$tglakhir)
		{
			$q=$this->dbhost->query("SELECT a.`no_nota`,a.`no_rawat`,a.`kd_pj`,a.`tgl_registrasi`,a.`tgl_nota`,a.`bayar`,b.`nama_bayar`,b.`besar_bayar` FROM `v_income_ranap` a 
 JOIN `detail_nota_inap` AS b ON a.`no_rawat`=b.`no_rawat`
 WHERE a.`kd_pj` NOT IN('-','1') AND (a.`nama_bayar`='Tunai' OR a.`nama_bayar`='Bank Centra Asia (BCA)' OR a.`nama_bayar`='Bank Muamalat Indonesia (BMI)' OR a.`nama_bayar`='Bank Rakyat Indonesia (BRI)' OR a.`nama_bayar`='Kas Pengeluaran' OR a.`nama_bayar`='QRIS Bank Syariah Indonesia (BSI)')
 AND a.`tgl_keluar` BETWEEN '$tglawal' AND '$tglakhir';"); 
 
			if($q->num_rows()>0){
				foreach($q->result() as $i){
					$h[$i->no_rawat]=$i;
				}
				return $h;			
			}else{
				return $q->result();
			}
		}
		
		function getDataBPJSForAdjusment($blnawal,$blnakhir)
		{
			$q=$this->dbhost->query("  SELECT LEFT(a.`tgl_nota`,7) AS bulan, SUM(a.`bayar`) AS jml
  FROM v_income_rajal AS a
  WHERE a.`kd_pj`='BPJ' AND LEFT(a.`tgl_nota`,7) BETWEEN '$blnawal' AND '$blnakhir'
  GROUP BY LEFT(a.`tgl_nota`,7)"); 
 
			return $q->result();
		}
		
		function getDataBPJSForAdjusmentRanap($blnawal,$blnakhir)
		{
			$q=$this->dbhost->query("  SELECT LEFT(a.`tgl_nota`,7) AS bulan, SUM(a.`bayar`) AS jml
  FROM v_income_ranap AS a
  WHERE a.`kd_pj`='BPJ' AND LEFT(a.`tgl_nota`,7) BETWEEN '$blnawal' AND '$blnakhir'
  GROUP BY LEFT(a.`tgl_nota`,7)"); 
 
			return $q->result();
		}
		
		function simpanReplaceAdjusmentBPJS($data)
		{
			$q = $this->db->insert_string('pendapatan_bpjs', $data);
			$q = str_replace('INSERT INTO','REPLACE INTO',$q);
			return $this->db->query($q);
		}
		
		function getPendapatanBPJSRajal($tgla,$tglb)
		{
			$q=$this->db->query("SELECT * FROM pendapatan_bpjs AS a WHERE a.`status`='1' AND a.`bulan` BETWEEN '$tgla' AND '$tglb';");
			
			if($q->num_rows()>0){
				foreach($q->result() as $i){
					$h[$i->bulan]=$i;
				}
				return $h;			
			}else{
				return $q->result();
			}
					
		}
		
		function getPendapatanBPJSRanap($tgla,$tglb)
		{
			$q=$this->db->query("SELECT * FROM pendapatan_bpjs AS a WHERE a.`status`='2' AND a.`bulan` BETWEEN '$tgla' AND '$tglb';");
			
			if($q->num_rows()>0){
				foreach($q->result() as $i){
					$h[$i->bulan]=$i;
				}
				return $h;			
			}else{
				return $q->result();
			}
					
		}
		
		function getPendapatanBPJSBulanan($tgla,$tglb)
		{
			$q=$this->db->query("SELECT a.status,
SUM(IF(RIGHT(a.`bulan`,2)='01', a.`bayar`,0)) AS bl1,
SUM(IF(RIGHT(a.`bulan`,2)='02', a.`bayar`,0)) AS bl2,
SUM(IF(RIGHT(a.`bulan`,2)='03', a.`bayar`,0)) AS bl3,
SUM(IF(RIGHT(a.`bulan`,2)='04', a.`bayar`,0)) AS bl4,
SUM(IF(RIGHT(a.`bulan`,2)='05', a.`bayar`,0)) AS bl5,
SUM(IF(RIGHT(a.`bulan`,2)='06', a.`bayar`,0)) AS bl6,
SUM(IF(RIGHT(a.`bulan`,2)='07', a.`bayar`,0)) AS bl7,
SUM(IF(RIGHT(a.`bulan`,2)='08', a.`bayar`,0)) AS bl8,
SUM(IF(RIGHT(a.`bulan`,2)='09', a.`bayar`,0)) AS bl9,
SUM(IF(RIGHT(a.`bulan`,2)='10', a.`bayar`,0)) AS bl10,
SUM(IF(RIGHT(a.`bulan`,2)='11', a.`bayar`,0)) AS bl11,
SUM(IF(RIGHT(a.`bulan`,2)='12', a.`bayar`,0)) AS bl12,
SUM(IF(RIGHT(a.`bulan`,2)='01',a.`persen`,0)) AS p1,
SUM(IF(RIGHT(a.`bulan`,2)='02',a.`persen`,0)) AS p2,
SUM(IF(RIGHT(a.`bulan`,2)='03',a.`persen`,0)) AS p3,
SUM(IF(RIGHT(a.`bulan`,2)='04',a.`persen`,0)) AS p4,
SUM(IF(RIGHT(a.`bulan`,2)='05',a.`persen`,0)) AS p5,
SUM(IF(RIGHT(a.`bulan`,2)='06',a.`persen`,0)) AS p6,
SUM(IF(RIGHT(a.`bulan`,2)='07',a.`persen`,0)) AS p7,
SUM(IF(RIGHT(a.`bulan`,2)='08',a.`persen`,0)) AS p8,
SUM(IF(RIGHT(a.`bulan`,2)='09',a.`persen`,0)) AS p9,
SUM(IF(RIGHT(a.`bulan`,2)='10',a.`persen`,0)) AS p10,
SUM(IF(RIGHT(a.`bulan`,2)='11',a.`persen`,0)) AS p11,
SUM(IF(RIGHT(a.`bulan`,2)='12',a.`persen`,0)) AS p12
FROM pendapatan_bpjs AS a
WHERE a.`bulan` BETWEEN '$tgla' AND '$tglb' 
GROUP BY a.`status` ;");

			return $q->result();
		}
	}
