<?php
	class Duite_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 getDataUser($user, $paswd)
		{
			$q=$this->db->get_where('karyawan1p', array('nik'=>$user, 'passwd'=> md5($paswd)));
			return $q->row();
		} */
		
		/* public function cek_login($email, $passwd)
		{
			$this->db->select("nik, tgl_lahir");
			$this->db->where('nik', $email);
			$this->db->where('passwd', md5($passwd));
			$q = $this->db->get('karyawan1p');
			
			return $q->num_rows()>0?TRUE:FALSE;
		} */
		
		//public function getBukuBesar($thn,$bulan,$blnakhir)
		public function getBukuBesar($thn)
		{
			//$q=$this->dbhost->query("SELECT a.`thn`, a.`kd_rek`, b.`nm_rek`, b.`tipe`, b.`balance`,a.`saldo_awal`, SUM(c.`debet`) AS debit, SUM(c.`kredit`) AS kredit, IF(b.`balance`='D',a.`saldo_awal` + SUM(c.`debet`) - SUM(c.`kredit`),a.`saldo_awal` - SUM(c.`debet`) + SUM(c.`kredit`)) AS saldo FROM rekeningtahun AS a LEFT JOIN rekening AS b ON a.`kd_rek`=b.`kd_rek` LEFT JOIN detailjurnal AS c ON b.`kd_rek`=c.`kd_rek` WHERE a.`thn`= LEFT(NOW(),4) GROUP BY a.`kd_rek` ;");
			
			//$q=$this->dbhost->query("SELECT a.`thn`, a.`kd_rek`, b.`nm_rek`, b.`tipe`, b.`balance`,a.`saldo_awal`, SUM(c.`debet`) AS debit, SUM(c.`kredit`) AS kredit,  IF(b.`balance`='D',a.`saldo_awal` + SUM(c.`debet`) - SUM(c.`kredit`),a.`saldo_awal` - SUM(c.`debet`) + SUM(c.`kredit`)) AS saldo FROM rekeningtahun AS a INNER JOIN rekening AS b ON a.`kd_rek`=b.`kd_rek` INNER JOIN detailjurnal AS c ON b.`kd_rek`=c.`kd_rek` INNER JOIN jurnal AS d ON c.`no_jurnal`=d.`no_jurnal` WHERE a.`thn`= LEFT(NOW(),4) AND mid(d.tgl_jurnal,6,2)='$bulan' GROUP BY a.`kd_rek`;");
			
			//$q=$this->dbhost->query("SELECT a.`thn`, a.`kd_rek`, b.`nm_rek`, b.`tipe`, b.`balance`, SUM(c.`debet`) AS debit, SUM(c.`kredit`) AS kredit FROM rekeningtahun AS a INNER JOIN rekening AS b ON a.`kd_rek`=b.`kd_rek` INNER JOIN detailjurnal AS c ON b.`kd_rek`=c.`kd_rek` INNER JOIN jurnal AS d ON c.`no_jurnal`=d.`no_jurnal` WHERE a.`thn`= '$thn' AND MID(d.tgl_jurnal,6,2) BETWEEN '$bulan' AND '$blnakhir' GROUP BY a.`kd_rek` ;");
			$q=$this->dbhost->query("SELECT a.`thn`, a.`kd_rek`, b.`nm_rek`, b.`tipe`,b.`balance` FROM rekeningtahun AS a INNER JOIN rekening AS b ON a.`kd_rek`=b.`kd_rek` WHERE  a.`thn`= '$thn' ORDER BY a.`kd_rek`;");

			return $q->result();
		}
		
		function getSaldoAwal($taun)
		{
			$q=$this->dbhost->query("SELECT b.`kd_rek`, a.saldo_awal,b.tipe, b.balance FROM rekeningtahun AS a INNER JOIN rekening AS b ON a.kd_rek=b.kd_rek WHERE a.thn LIKE '%$taun%';");
			if($q->num_rows()>0){
				foreach($q->result() as $i){
					$h[$i->kd_rek]=$i;
				}
				return $h;			
			}else{
				return $q->result();
			}
		}
		
		function getJmlDebKred($taun,$bulan,$bulankah)
		{
			$q=$this->dbhost->query("SELECT b.`kd_rek`, SUM(b.debet) AS debet,SUM(b.kredit) AS kredit
FROM jurnal AS a INNER JOIN detailjurnal AS b ON b.no_jurnal=a.no_jurnal 
WHERE a.tgl_jurnal BETWEEN '$taun-$bulan-01' AND '$taun-$bulankah-31' GROUP BY b.`kd_rek`;");
			if($q->num_rows()>0){
				foreach($q->result() as $i){
					$h[$i->kd_rek]=$i;
				}
				return $h;			
			}else{
				return $q->result();
			}
		}
		
		function getJmlDebKredPrev($taun,$bulan)
		{
			$q=$this->dbhost->query("SELECT b.`kd_rek`, SUM(b.debet) AS debet,SUM(b.kredit) AS kredit
FROM jurnal AS a INNER JOIN detailjurnal AS b ON b.no_jurnal=a.no_jurnal 
WHERE a.tgl_jurnal BETWEEN '$taun-01-01' AND '$taun-$bulan-31' GROUP BY b.`kd_rek`;");
			if($q->num_rows()>0){
				foreach($q->result() as $i){
					$h[$i->kd_rek]=$i;
				}
				return $h;			
			}else{
				return $q->result();
			}
		}
		function getBukuBesarRekening($key,$tglawal, $tglakhir)
		{
			$q=$this->dbhost->query("select d.`no_keluar` AS noph,e.`no_bukti` AS nomed,a.*, f.`balance`,d.`keterangan` as ket_harian, e.`keterangan` as ket_pesan,c.`saldo_awal`, SUM(DISTINCT(b.`debet`)) AS debit, SUM(DISTINCT(b.`kredit`)) AS kredit from jurnal as a left join detailjurnal as b on a.`no_jurnal`=b.`no_jurnal` left join rekeningtahun as c on b.`kd_rek`=c.`kd_rek` left join pengeluaran_harian as d on a.`no_bukti`=d.`no_keluar` left join bayar_pemesanan as e on a.`no_bukti`=e.`no_bukti` left join rekening as f on c.`kd_rek`=f.`kd_rek` where b.`kd_rek`='$key' and a.`tgl_jurnal` between '$tglawal' and '$tglakhir' group by a.`no_jurnal` ORDER BY a.`tgl_jurnal`,a.`jam_jurnal`;");

			return $q->result();
		}
		function getJmlDebKredRekening($key,$tglawal, $tglakhir)
		{
			$q=$this->dbhost->query("SELECT a.`no_jurnal`, SUM(b.`debet`) AS debet, SUM(b.`kredit`) AS kredit FROM jurnal AS a INNER JOIN detailjurnal AS b ON a.`no_jurnal`=b.`no_jurnal`
WHERE b.`kd_rek`='$key' AND a.`tgl_jurnal` BETWEEN '$tglawal' AND '$tglakhir' GROUP BY a.`no_jurnal`;");
			if($q->num_rows()>0){
				foreach($q->result() as $i){
					$h[$i->no_jurnal]=$i;
				}
				return $h;			
			}else{
				return $q->result();
			}
		}
		
		function getNoNotaForRekeningJurnal($tglawal, $tglakhir)
		{
			$q=$this->dbhost->query("SELECT a.`tanggal`,b.`tgl_jurnal`, b.`no_jurnal`, b.`no_bukti`, a.`no_rawat`, GROUP_CONCAT(DISTINCT(a.`no_nota`)) AS nota, COUNT(DISTINCT(a.`no_nota`)) AS jml FROM v_nota AS a JOIN jurnal AS b ON a.`no_rawat`=b.`no_bukti` 
WHERE  b.`tgl_jurnal` BETWEEN '$tglawal' AND '$tglakhir' GROUP BY a.`no_rawat`;
");
			if($q->num_rows()>0){
				foreach($q->result() as $i){
					$h[$i->no_bukti]=$i;
				}
				return $h;			
			}else{
				return $q->result();
			}
		}
		
		function getTglJurnalBedaPH($bulan)
		{
			$q=$this->dbhost->query("SELECT a.`no_jurnal`, a.`no_bukti`, a.`tgl_jurnal`, LEFT(b.`tanggal`,10) AS tgl_ph, a.`keterangan`, b.`keterangan` AS detail
FROM jurnal AS a
LEFT JOIN pengeluaran_harian AS b ON a.`no_bukti`=b.`no_keluar`
WHERE a.`tgl_jurnal` <> LEFT(b.`tanggal`,10) $bulan;");

			return $q->result();
		}
		
		function getTglJurnalBedaBP($bulan)
		{
			$q=$this->dbhost->query("SELECT a.`no_jurnal`, a.`no_bukti`, a.`tgl_jurnal`, LEFT(b.`tgl_bayar`,10) AS tgl_ph, a.`keterangan`, b.`catatan` AS detail
FROM jurnal AS a
LEFT JOIN bayar_piutang AS b ON a.`no_bukti`=b.`no_rawat`
WHERE a.`tgl_jurnal` <> LEFT(b.`tgl_bayar`,10) $bulan;");

			return $q->result();
		}
		
		function getTglJurnalBedaBMHP($bulan)
		{
			$q=$this->dbhost->query("SELECT a.`no_jurnal`, a.`no_bukti`, a.`tgl_jurnal`, LEFT(b.`tgl_bayar`,10) AS tgl_ph, a.`keterangan`, b.`keterangan` AS detail
FROM jurnal AS a
LEFT JOIN bayar_pemesanan AS b ON a.`no_bukti`=b.`no_bukti`
WHERE a.`tgl_jurnal` <> LEFT(b.`tgl_bayar`,10) $bulan;");

			return $q->result();
		}
		
		function getTglJurnalBedaNonBMHP($bulan)
		{
			$q=$this->dbhost->query("SELECT a.`no_jurnal`, a.`no_bukti`, a.`tgl_jurnal`, LEFT(b.`tgl_bayar`,10) AS tgl_ph, a.`keterangan`, b.`keterangan` AS detail
FROM jurnal AS a
LEFT JOIN `bayar_pemesanan_non_medis` AS b ON a.`no_bukti`=b.`no_bukti`
WHERE a.`tgl_jurnal` <> LEFT(b.`tgl_bayar`,10) $bulan;");

			return $q->result();
		}
		
		function updateTglJurnalBeda($baru,$nojurnal)
		{
			$this->dbhost->where('no_jurnal',$nojurnal);
			return $this->dbhost->update('jurnal',$baru);
		}
		
		function getPembayaranRalan()
		{
			$q=$this->dbhost->query("SELECT reg_periksa.no_rawat,reg_periksa.no_rkm_medis,pasien.nm_pasien,reg_periksa.tgl_registrasi,dokter.nm_dokter,poliklinik.nm_poli,penjab.`png_jawab`
FROM reg_periksa INNER JOIN pasien ON reg_periksa.no_rkm_medis=pasien.no_rkm_medis 
INNER JOIN poliklinik ON reg_periksa.kd_poli=poliklinik.kd_poli 
INNER JOIN dokter ON reg_periksa.kd_dokter=dokter.kd_dokter 
INNER JOIN penjab ON reg_periksa.kd_pj=penjab.kd_pj 
WHERE reg_periksa.status_lanjut='Ralan' AND reg_periksa.no_rawat NOT IN (SELECT piutang_pasien.no_rawat FROM piutang_pasien WHERE piutang_pasien.no_rawat=reg_periksa.no_rawat) AND 
reg_periksa.tgl_registrasi BETWEEN '2022-03-01' AND '2022-03-30' ORDER BY reg_periksa.kd_dokter,reg_periksa.tgl_registrasi;");

			/* if($q->num_rows()>0){
				foreach($q->result() as $i){
					$h[$i->no_rawat]=$i;
				}
				return $h;			
			}else{ */
				return $q->result();
			//}
		}
		
		function getNoNota()
		{
			$q=$this->dbhost->query("SELECT * FROM nota_jalan;");
			if($q->num_rows()>0){
				foreach($q->result() as $i){
					$h[$i->no_rawat]=$i;
				}
				return $h;			
			}else{
				return $q->result();
			}
		}
		
		function getPerujuk()
		{
			$q=$this->dbhost->query("SELECT * FROM rujuk_masuk;");
			if($q->num_rows()>0){
				foreach($q->result() as $i){
					$h[$i->no_rawat]=$i;
				}
				return $h;			
			}else{
				return $q->result();
			}
		}
		
		function getBiayaBilling()
		{
			$q=$this->dbhost->query("SELECT a.`no_rawat`, a.`nm_perawatan`, a.`totalbiaya`, a.`status` FROM billing AS a;");
			if($q->num_rows()>0){
				foreach($q->result() as $i){
					$h[$i->no_rawat]=$i;
				}
				return $h;			
			}else{
				return $q->result();
			}
		}
		
		//NERACA
		//public function getNeracaHL($thn,$bulan,$blnakhir)
		public function getNeracaHL($thn)
		{			
			//$q=$this->dbhost->query("SELECT a.`thn`, a.`kd_rek`, b.`nm_rek`, b.`tipe`, b.`balance`, SUM(c.`debet`) AS debit, SUM(c.`kredit`) AS kredit FROM rekeningtahun AS a INNER JOIN rekening AS b ON a.`kd_rek`=b.`kd_rek` INNER JOIN detailjurnal AS c ON b.`kd_rek`=c.`kd_rek` INNER JOIN jurnal AS d ON c.`no_jurnal`=d.`no_jurnal` WHERE LEFT(a.`kd_rek`,3)='1.1' AND a.`thn`= '$thn' AND MID(d.tgl_jurnal,6,2) BETWEEN '$bulan' AND '$blnakhir' GROUP BY a.`kd_rek` ;");
			$q=$this->dbhost->query("SELECT a.`thn`, a.`kd_rek`, b.`nm_rek`, b.`tipe`,b.`balance` FROM rekeningtahun AS a INNER JOIN rekening AS b ON a.`kd_rek`=b.`kd_rek` WHERE LEFT(a.`kd_rek`,3)='1.1' AND a.`thn`= '$thn' ORDER BY a.`kd_rek`;");

			return $q->result();
		}
		
		function getDebKredHL($thn,$bulan,$blnakhir)
		{
			$q=$this->dbhost->query("SELECT b.`kd_rek`, SUM(b.debet) AS debet,SUM(b.kredit) AS kredit FROM jurnal AS a INNER JOIN detailjurnal AS b ON b.no_jurnal=a.no_jurnal WHERE LEFT(b.`kd_rek`,3)='1.1' AND a.tgl_jurnal BETWEEN '$thn-$bulan-01' AND '$thn-$blnakhir-31' GROUP BY b.`kd_rek`;");
			if($q->num_rows()>0){
				foreach($q->result() as $i){
					$h[$i->kd_rek]=$i;
				}
				return $h;			
			}else{
				return $q->result();
			}
		}
		
		function getSaldoAwalHL($taun)
		{
			$q=$this->dbhost->query("SELECT b.`kd_rek`, a.saldo_awal,b.tipe, b.balance FROM rekeningtahun AS a INNER JOIN rekening AS b ON a.kd_rek=b.kd_rek WHERE LEFT(a.`kd_rek`,3)='1.1' AND a.thn LIKE '%$taun%';");
			if($q->num_rows()>0){
				foreach($q->result() as $i){
					$h[$i->kd_rek]=$i;
				}
				return $h;			
			}else{
				return $q->result();
			}
		}
		
		function getJmlDebKredPrevHL($taun,$bulan)
		{
			$q=$this->dbhost->query("SELECT b.`kd_rek`, SUM(b.debet) AS debet,SUM(b.kredit) AS kredit FROM jurnal AS a INNER JOIN detailjurnal AS b ON b.no_jurnal=a.no_jurnal WHERE LEFT(b.`kd_rek`,3)='1.1' AND a.tgl_jurnal BETWEEN '$taun-01-01' AND '$taun-$bulan-31' GROUP BY b.`kd_rek`;");
			if($q->num_rows()>0){
				foreach($q->result() as $i){
					$h[$i->kd_rek]=$i;
				}
				return $h;			
			}else{
				return $q->result();
			}
		}
		
		public function getNeracaHTL($thn)
		{			
			$q=$this->dbhost->query("SELECT a.`thn`, a.`kd_rek`, b.`nm_rek`, b.`tipe`,b.`balance` FROM rekeningtahun AS a INNER JOIN rekening AS b ON a.`kd_rek`=b.`kd_rek` WHERE LEFT(a.`kd_rek`,3)='1.2' AND a.`thn`= '$thn' ORDER BY a.`kd_rek`;");

			return $q->result();
		}
		
		function getDebKredHTL($thn,$bulan,$blnakhir)
		{
			$q=$this->dbhost->query("SELECT b.`kd_rek`, SUM(b.debet) AS debet,SUM(b.kredit) AS kredit FROM jurnal AS a INNER JOIN detailjurnal AS b ON b.no_jurnal=a.no_jurnal WHERE LEFT(b.`kd_rek`,3)='1.2' AND a.tgl_jurnal BETWEEN '$thn-$bulan-01' AND '$thn-$blnakhir-31' GROUP BY b.`kd_rek`;");
			if($q->num_rows()>0){
				foreach($q->result() as $i){
					$h[$i->kd_rek]=$i;
				}
				return $h;			
			}else{
				return $q->result();
			}
		}
		
		function getSaldoAwalHTL($taun)
		{
			$q=$this->dbhost->query("SELECT b.`kd_rek`, a.saldo_awal,b.tipe, b.balance FROM rekeningtahun AS a INNER JOIN rekening AS b ON a.kd_rek=b.kd_rek WHERE LEFT(a.`kd_rek`,3)='1.2' AND a.thn LIKE '%$taun%';");
			if($q->num_rows()>0){
				foreach($q->result() as $i){
					$h[$i->kd_rek]=$i;
				}
				return $h;			
			}else{
				return $q->result();
			}
		}
		
		function getJmlDebKredPrevHTL($taun,$bulan)
		{
			$q=$this->dbhost->query("SELECT b.`kd_rek`, SUM(b.debet) AS debet,SUM(b.kredit) AS kredit FROM jurnal AS a INNER JOIN detailjurnal AS b ON b.no_jurnal=a.no_jurnal WHERE LEFT(b.`kd_rek`,3)='1.2' AND a.tgl_jurnal BETWEEN '$taun-01-01' AND '$taun-$bulan-31' GROUP BY b.`kd_rek`;");
			if($q->num_rows()>0){
				foreach($q->result() as $i){
					$h[$i->kd_rek]=$i;
				}
				return $h;			
			}else{
				return $q->result();
			}
		}
		
		public function getNeracaKL($thn)
		{			
			$q=$this->dbhost->query("SELECT a.`thn`, a.`kd_rek`, b.`nm_rek`, b.`tipe`,b.`balance` FROM rekeningtahun AS a INNER JOIN rekening AS b ON a.`kd_rek`=b.`kd_rek` WHERE LEFT(a.`kd_rek`,3)='2.1' AND a.`thn`= '$thn' ORDER BY a.`kd_rek`;");

			return $q->result();
		}
		
		function getDebKredKL($thn,$bulan,$blnakhir)
		{
			$q=$this->dbhost->query("SELECT b.`kd_rek`, SUM(b.debet) AS debet,SUM(b.kredit) AS kredit FROM jurnal AS a INNER JOIN detailjurnal AS b ON b.no_jurnal=a.no_jurnal WHERE LEFT(b.`kd_rek`,3)='2.1' AND a.tgl_jurnal BETWEEN '$thn-$bulan-01' AND '$thn-$blnakhir-31' GROUP BY b.`kd_rek`;");
			if($q->num_rows()>0){
				foreach($q->result() as $i){
					$h[$i->kd_rek]=$i;
				}
				return $h;			
			}else{
				return $q->result();
			}
		}
		
		function getSaldoAwalKL($taun)
		{
			$q=$this->dbhost->query("SELECT b.`kd_rek`, a.saldo_awal,b.tipe, b.balance FROM rekeningtahun AS a INNER JOIN rekening AS b ON a.kd_rek=b.kd_rek WHERE LEFT(a.`kd_rek`,3)='2.1' AND a.thn LIKE '%$taun%';");
			if($q->num_rows()>0){
				foreach($q->result() as $i){
					$h[$i->kd_rek]=$i;
				}
				return $h;			
			}else{
				return $q->result();
			}
		}
		
		function getJmlDebKredPrevKL($taun,$bulan)
		{
			$q=$this->dbhost->query("SELECT b.`kd_rek`, SUM(b.debet) AS debet,SUM(b.kredit) AS kredit FROM jurnal AS a INNER JOIN detailjurnal AS b ON b.no_jurnal=a.no_jurnal WHERE LEFT(b.`kd_rek`,3)='2.1' AND a.tgl_jurnal BETWEEN '$taun-01-01' AND '$taun-$bulan-31' GROUP BY b.`kd_rek`;");
			if($q->num_rows()>0){
				foreach($q->result() as $i){
					$h[$i->kd_rek]=$i;
				}
				return $h;			
			}else{
				return $q->result();
			}
		}
		
		public function getNeracaKTL($thn)
		{			
			$q=$this->dbhost->query("SELECT a.`thn`, a.`kd_rek`, b.`nm_rek`, b.`tipe`,b.`balance` FROM rekeningtahun AS a INNER JOIN rekening AS b ON a.`kd_rek`=b.`kd_rek` WHERE LEFT(a.`kd_rek`,3)='2.2' AND a.`thn`= '$thn' ORDER BY a.`kd_rek`;");

			return $q->result();
		}
		
		function getDebKredKTL($thn,$bulan,$blnakhir)
		{
			$q=$this->dbhost->query("SELECT b.`kd_rek`, SUM(b.debet) AS debet,SUM(b.kredit) AS kredit FROM jurnal AS a INNER JOIN detailjurnal AS b ON b.no_jurnal=a.no_jurnal WHERE LEFT(b.`kd_rek`,3)='2.2' AND a.tgl_jurnal BETWEEN '$thn-$bulan-01' AND '$thn-$blnakhir-31' GROUP BY b.`kd_rek`;");
			if($q->num_rows()>0){
				foreach($q->result() as $i){
					$h[$i->kd_rek]=$i;
				}
				return $h;			
			}else{
				return $q->result();
			}
		}
		
		function getSaldoAwalKTL($taun)
		{
			$q=$this->dbhost->query("SELECT b.`kd_rek`, a.saldo_awal,b.tipe, b.balance FROM rekeningtahun AS a INNER JOIN rekening AS b ON a.kd_rek=b.kd_rek WHERE LEFT(a.`kd_rek`,3)='2.2' AND a.thn LIKE '%$taun%';");
			if($q->num_rows()>0){
				foreach($q->result() as $i){
					$h[$i->kd_rek]=$i;
				}
				return $h;			
			}else{
				return $q->result();
			}
		}
		
		function getJmlDebKredPrevKTL($taun,$bulan)
		{
			$q=$this->dbhost->query("SELECT b.`kd_rek`, SUM(b.debet) AS debet,SUM(b.kredit) AS kredit FROM jurnal AS a INNER JOIN detailjurnal AS b ON b.no_jurnal=a.no_jurnal WHERE LEFT(b.`kd_rek`,3)='2.2' AND a.tgl_jurnal BETWEEN '$taun-01-01' AND '$taun-$bulan-31' GROUP BY b.`kd_rek`;");
			if($q->num_rows()>0){
				foreach($q->result() as $i){
					$h[$i->kd_rek]=$i;
				}
				return $h;			
			}else{
				return $q->result();
			}
		}
		
		public function getNeracaModal($thn)
		{			
			$q=$this->dbhost->query("SELECT a.`thn`, a.`kd_rek`, b.`nm_rek`, b.`tipe`,b.`balance` FROM rekeningtahun AS a INNER JOIN rekening AS b ON a.`kd_rek`=b.`kd_rek` WHERE LEFT(a.`kd_rek`,1)='3' AND a.`thn`= '$thn' ORDER BY a.`kd_rek`;");

			return $q->result();
		}
		
		function getDebKredModal($thn,$bulan,$blnakhir)
		{
			$q=$this->dbhost->query("SELECT b.`kd_rek`, SUM(b.debet) AS debet,SUM(b.kredit) AS kredit FROM jurnal AS a INNER JOIN detailjurnal AS b ON b.no_jurnal=a.no_jurnal WHERE LEFT(b.`kd_rek`,1)='3' AND a.tgl_jurnal BETWEEN '$thn-$bulan-01' AND '$thn-$blnakhir-31' GROUP BY b.`kd_rek`;");
			if($q->num_rows()>0){
				foreach($q->result() as $i){
					$h[$i->kd_rek]=$i;
				}
				return $h;			
			}else{
				return $q->result();
			}
		}
		
		function getSaldoAwalModal($taun)
		{
			$q=$this->dbhost->query("SELECT b.`kd_rek`, a.saldo_awal,b.tipe, b.balance FROM rekeningtahun AS a INNER JOIN rekening AS b ON a.kd_rek=b.kd_rek WHERE LEFT(a.`kd_rek`,1)='3' AND a.thn LIKE '%$taun%';");
			if($q->num_rows()>0){
				foreach($q->result() as $i){
					$h[$i->kd_rek]=$i;
				}
				return $h;			
			}else{
				return $q->result();
			}
		}
		
		function getJmlDebKredPrevModal($taun,$bulan)
		{
			$q=$this->dbhost->query("SELECT b.`kd_rek`, SUM(b.debet) AS debet,SUM(b.kredit) AS kredit FROM jurnal AS a INNER JOIN detailjurnal AS b ON b.no_jurnal=a.no_jurnal WHERE LEFT(b.`kd_rek`,1)='3' AND a.tgl_jurnal BETWEEN '$taun-01-01' AND '$taun-$bulan-31' GROUP BY b.`kd_rek`;");
			if($q->num_rows()>0){
				foreach($q->result() as $i){
					$h[$i->kd_rek]=$i;
				}
				return $h;			
			}else{
				return $q->result();
			}
		}
		//end NERACA
		
		//Laba(rugi)
		public function getLabRugiPO($thn)
		{
			$q=$this->dbhost->query("SELECT a.`thn`, a.`kd_rek`, b.`nm_rek`, b.`tipe`,b.`balance` FROM rekeningtahun AS a INNER JOIN rekening AS b ON a.`kd_rek`=b.`kd_rek` WHERE LEFT(a.`kd_rek`,1)='4' AND a.`thn`= '$thn' ORDER BY a.`kd_rek`;");

			return $q->result();
		}
		
		function getDebKredPO($thn,$bulan,$blnakhir)
		{
			$q=$this->dbhost->query("SELECT b.`kd_rek`, SUM(b.debet) AS debet,SUM(b.kredit) AS kredit FROM jurnal AS a INNER JOIN detailjurnal AS b ON b.no_jurnal=a.no_jurnal WHERE LEFT(b.`kd_rek`,1)='4' AND a.tgl_jurnal BETWEEN '$thn-$bulan-01' AND '$thn-$blnakhir-31' GROUP BY b.`kd_rek`;");
			if($q->num_rows()>0){
				foreach($q->result() as $i){
					$h[$i->kd_rek]=$i;
				}
				return $h;			
			}else{
				return $q->result();
			}
		}
		
		function getSaldoAwalPO($taun)
		{
			$q=$this->dbhost->query("SELECT b.`kd_rek`, a.saldo_awal,b.tipe, b.balance FROM rekeningtahun AS a INNER JOIN rekening AS b ON a.kd_rek=b.kd_rek WHERE LEFT(a.`kd_rek`,1)='4' AND a.thn LIKE '%$taun%';");
			if($q->num_rows()>0){
				foreach($q->result() as $i){
					$h[$i->kd_rek]=$i;
				}
				return $h;			
			}else{
				return $q->result();
			}
		}
		
		function getJmlDebKredPrevPO($taun,$bulan)
		{
			$q=$this->dbhost->query("SELECT b.`kd_rek`, SUM(b.debet) AS debet,SUM(b.kredit) AS kredit FROM jurnal AS a INNER JOIN detailjurnal AS b ON b.no_jurnal=a.no_jurnal WHERE LEFT(b.`kd_rek`,1)='4' AND a.tgl_jurnal BETWEEN '$taun-01-01' AND '$taun-$bulan-31' GROUP BY b.`kd_rek`;");
			if($q->num_rows()>0){
				foreach($q->result() as $i){
					$h[$i->kd_rek]=$i;
				}
				return $h;			
			}else{
				return $q->result();
			}
		}
		
		public function getLabRugiBO($thn)
		{
			$q=$this->dbhost->query("SELECT a.`thn`, a.`kd_rek`, b.`nm_rek`, b.`tipe`,b.`balance` FROM rekeningtahun AS a INNER JOIN rekening AS b ON a.`kd_rek`=b.`kd_rek` WHERE LEFT(a.`kd_rek`,1)='5' AND a.`thn`= '$thn' ORDER BY a.`kd_rek`;");

			return $q->result();
		}
		
		function getDebKredBO($thn,$bulan,$blnakhir)
		{
			$q=$this->dbhost->query("SELECT b.`kd_rek`, SUM(b.debet) AS debet,SUM(b.kredit) AS kredit FROM jurnal AS a INNER JOIN detailjurnal AS b ON b.no_jurnal=a.no_jurnal WHERE LEFT(b.`kd_rek`,1)='5' AND a.tgl_jurnal BETWEEN '$thn-$bulan-01' AND '$thn-$blnakhir-31' GROUP BY b.`kd_rek`;");
			if($q->num_rows()>0){
				foreach($q->result() as $i){
					$h[$i->kd_rek]=$i;
				}
				return $h;			
			}else{
				return $q->result();
			}
		}
		
		function getSaldoAwalBO($taun)
		{
			$q=$this->dbhost->query("SELECT b.`kd_rek`, a.saldo_awal,b.tipe, b.balance FROM rekeningtahun AS a INNER JOIN rekening AS b ON a.kd_rek=b.kd_rek WHERE LEFT(a.`kd_rek`,1)='5' AND a.thn LIKE '%$taun%';");
			if($q->num_rows()>0){
				foreach($q->result() as $i){
					$h[$i->kd_rek]=$i;
				}
				return $h;			
			}else{
				return $q->result();
			}
		}
		
		function getJmlDebKredPrevBO($taun,$bulan)
		{
			$q=$this->dbhost->query("SELECT b.`kd_rek`, SUM(b.debet) AS debet,SUM(b.kredit) AS kredit FROM jurnal AS a INNER JOIN detailjurnal AS b ON b.no_jurnal=a.no_jurnal WHERE LEFT(b.`kd_rek`,1)='5' AND a.tgl_jurnal BETWEEN '$taun-01-01' AND '$taun-$bulan-31' GROUP BY b.`kd_rek`;");
			if($q->num_rows()>0){
				foreach($q->result() as $i){
					$h[$i->kd_rek]=$i;
				}
				return $h;			
			}else{
				return $q->result();
			}
		}
		
		public function getLabRugiPL($thn)
		{
			$q=$this->dbhost->query("SELECT a.`thn`, a.`kd_rek`, b.`nm_rek`, b.`tipe`,b.`balance` FROM rekeningtahun AS a INNER JOIN rekening AS b ON a.`kd_rek`=b.`kd_rek` WHERE LEFT(a.`kd_rek`,3)='6.1' AND a.`thn`= '$thn' ORDER BY a.`kd_rek`;");

			return $q->result();
		}
		
		function getDebKredPL($thn,$bulan,$blnakhir)
		{
			$q=$this->dbhost->query("SELECT b.`kd_rek`, SUM(b.debet) AS debet,SUM(b.kredit) AS kredit FROM jurnal AS a INNER JOIN detailjurnal AS b ON b.no_jurnal=a.no_jurnal WHERE LEFT(b.`kd_rek`,3)='6.1' AND a.tgl_jurnal BETWEEN '$thn-$bulan-01' AND '$thn-$blnakhir-31' GROUP BY b.`kd_rek`;");
			if($q->num_rows()>0){
				foreach($q->result() as $i){
					$h[$i->kd_rek]=$i;
				}
				return $h;			
			}else{
				return $q->result();
			}
		}
		
		function getSaldoAwalPL($taun)
		{
			$q=$this->dbhost->query("SELECT b.`kd_rek`, a.saldo_awal,b.tipe, b.balance FROM rekeningtahun AS a INNER JOIN rekening AS b ON a.kd_rek=b.kd_rek WHERE LEFT(a.`kd_rek`,3)='6.1' AND a.thn LIKE '%$taun%';");
			if($q->num_rows()>0){
				foreach($q->result() as $i){
					$h[$i->kd_rek]=$i;
				}
				return $h;			
			}else{
				return $q->result();
			}
		}
		
		function getJmlDebKredPrevPL($taun,$bulan)
		{
			$q=$this->dbhost->query("SELECT b.`kd_rek`, SUM(b.debet) AS debet,SUM(b.kredit) AS kredit FROM jurnal AS a INNER JOIN detailjurnal AS b ON b.no_jurnal=a.no_jurnal WHERE LEFT(b.`kd_rek`,3)='6.1' AND a.tgl_jurnal BETWEEN '$taun-01-01' AND '$taun-$bulan-31' GROUP BY b.`kd_rek`;");
			if($q->num_rows()>0){
				foreach($q->result() as $i){
					$h[$i->kd_rek]=$i;
				}
				return $h;			
			}else{
				return $q->result();
			}
		}
		
		public function getLabRugiBL($thn)
		{
			$q=$this->dbhost->query("SELECT a.`thn`, a.`kd_rek`, b.`nm_rek`, b.`tipe`,b.`balance` FROM rekeningtahun AS a INNER JOIN rekening AS b ON a.`kd_rek`=b.`kd_rek` WHERE LEFT(a.`kd_rek`,3)='6.2' AND a.`thn`= '$thn' ORDER BY a.`kd_rek`;");

			return $q->result();
		}
		
		function getDebKredBL($thn,$bulan,$blnakhir)
		{
			$q=$this->dbhost->query("SELECT b.`kd_rek`, SUM(b.debet) AS debet,SUM(b.kredit) AS kredit FROM jurnal AS a INNER JOIN detailjurnal AS b ON b.no_jurnal=a.no_jurnal WHERE LEFT(b.`kd_rek`,3)='6.2' AND a.tgl_jurnal BETWEEN '$thn-$bulan-01' AND '$thn-$blnakhir-31' GROUP BY b.`kd_rek`;");
			if($q->num_rows()>0){
				foreach($q->result() as $i){
					$h[$i->kd_rek]=$i;
				}
				return $h;			
			}else{
				return $q->result();
			}
		}
		
		function getSaldoAwalBL($taun)
		{
			$q=$this->dbhost->query("SELECT b.`kd_rek`, a.saldo_awal,b.tipe, b.balance FROM rekeningtahun AS a INNER JOIN rekening AS b ON a.kd_rek=b.kd_rek WHERE LEFT(a.`kd_rek`,3)='6.2' AND a.thn LIKE '%$taun%';");
			if($q->num_rows()>0){
				foreach($q->result() as $i){
					$h[$i->kd_rek]=$i;
				}
				return $h;			
			}else{
				return $q->result();
			}
		}
		
		function getJmlDebKredPrevBL($taun,$bulan)
		{
			$q=$this->dbhost->query("SELECT b.`kd_rek`, SUM(b.debet) AS debet,SUM(b.kredit) AS kredit FROM jurnal AS a INNER JOIN detailjurnal AS b ON b.no_jurnal=a.no_jurnal WHERE LEFT(b.`kd_rek`,3)='6.2' AND a.tgl_jurnal BETWEEN '$taun-01-01' AND '$taun-$bulan-31' GROUP BY b.`kd_rek`;");
			if($q->num_rows()>0){
				foreach($q->result() as $i){
					$h[$i->kd_rek]=$i;
				}
				return $h;			
			}else{
				return $q->result();
			}
		}
		
		function getJurnalNoBanalce($tglawal,$tglakhir)
		{
			$q=$this->dbhost->query("SELECT SUM(b.`debet`) AS tdebet, SUM(b.`kredit`) AS tkredit,GROUP_CONCAT(b.`kd_rek`) AS kd_rek, GROUP_CONCAT(c.`nm_rek`) AS rek,a.* 
FROM jurnal a INNER JOIN detailjurnal b ON a.`no_jurnal`=b.`no_jurnal` 
INNER JOIN rekening AS c ON b.`kd_rek`=c.`kd_rek`
WHERE a.`tgl_jurnal` BETWEEN '$tglawal' AND '$tglakhir' GROUP BY a.`no_jurnal`  HAVING tdebet <> tkredit;");

			return $q->result();
		}
		
		function getJurnalNoBanalceDetail($key)
		{
			$q=$this->dbhost->query("SELECT b.`debet`, b.`kredit`,b.`kd_rek`,c.`nm_rek`, a.* FROM jurnal a INNER JOIN detailjurnal b ON a.`no_jurnal`=b.`no_jurnal` 
INNER JOIN rekening AS c ON b.`kd_rek`=c.`kd_rek`
WHERE a.`no_bukti`='$key';");

			return $q->result();
		}
		
		function getRekeningJson()
		{
			
			$q=$this->dbhost->get('rekening');
			return $q->result();
		}
		
		function saveJurnalTidakLengkap($insjurnal)
		{
			return $this->dbhost->insert('jurnal', $insjurnal);
		}		
		function saveDetailJurnalTidakLengkap($insdetail)
		{
			return $this->dbhost->insert('detailjurnal', $insdetail);
		}
		
		function simpanDetailPindahJurnalGakKeren($updt,$nojurnal,$kdrek)
		{
			$this->dbhost->where('no_jurnal',$nojurnal);
			$this->dbhost->where('kd_rek',$kdrek);
			return $this->dbhost->update('detailjurnal',$updt);
		}
		
		function simpanUbahJurnalTidakValid($debit,$kredit,$upDBT,$upKRT,$nojurnal,$kdrek)
		{
			
			if($kredit==0){	
				$this->dbhost->where('no_jurnal',$nojurnal);
				$this->dbhost->where('kd_rek',$kdrek);
				$this->dbhost->where('debet <>',0);
			
				return $this->dbhost->update('detailjurnal',$upDBT); 
				//echo "Debit".$nojurnal;//exit();
				//print_r($upDBT);exit();
				
			}else{
				$this->dbhost->where('no_jurnal',$nojurnal);
				$this->dbhost->where('kd_rek',$kdrek);
				$this->dbhost->where('kredit <>',0);
				
				return $this->dbhost->update('detailjurnal',$upKRT); 
				//$debt="'kredit <>','0'";
				//echo "Kredit".$nojurnal;//exit();
				//print_r($upKRT);exit();
			}
			//$this->dbhost->where($debt);
			
			//return $updt;
		}
		
	}
	
