<?php
/** Generated Model Class **/ 

Class Report {
	public static function gajiDokter($bln,$dokter = 0) {
		if ($dokter != 0) {
			$where = "where from_unixtime(tgl,'%m-%Y') = '$bln' and id_dokter = $dokter";
		} else {
			$where = "where from_unixtime(tgl,'%m-%Y') = '$bln'";
		}
		
		$qk = Self::queryGajiDokter($where,1); //query konsultasi
		$qt = Self::queryGajiDokter($where,2); //query tindakan
		$q = "$qk UNION $qt ORDER by nama, hari, id_golongan";
		$data = R::getAll($q);
		
		$report = array();
		foreach($data as $d){
			$n = $d['id'];
			$t = substr($d['hari'],0,2);
			$report[$n]['nama'] = $d['nama'];
			$report[$n]['poli'] = Poli::getNama($d['id_poli']);
			if (!isset($report[$n]['total'])) {
				$report[$n]['total'] = 0;
			}
			if (!isset($report[$n]['detail'][$t]['subtotal'])) {
				$report[$n]['detail'][$t]['subtotal'] = 0;
			}
			if ($d["tipe"] == 1) {
				$report[$n]["detail"][$t]["k".$d["id_golongan"]."px"] = $d["px"];
				$report[$n]["detail"][$t]["k".$d["id_golongan"]."nominal"] = $d["nominal"];
			} else {
				$report[$n]["detail"][$t]["t".$d["id_golongan"]."px"] = $d["px"];
				$report[$n]["detail"][$t]["t".$d["id_golongan"]."nominal"] = $d["nominal"];
			}
			$report[$n]["detail"][$t]["subtotal"] = $report[$n]["detail"][$t]["subtotal"] + $d["nominal"];
			$report[$n]["total"] = $report[$n]["total"] + $d["nominal"];
		}
		
		return $report;
	}
	
	public static function gajiDokterDetail($bln,$dokter = 0) {
		// get kunjungan
		$q = "SELECT k.id, FROM_UNIXTIME(k.tgl,'%d') as tanggal, p.nama, g.nama as golongan FROM kunjungan k 
				INNER JOIN m_pasien p ON k.id_pasien = p.id 
				INNER JOIN m_golongan g ON k.id_golongan = g.id 
				WHERE FROM_UNIXTIME(k.tgl,'%m-%Y') = '$bln' AND k.id_dokter = $dokter ORDER BY k.tgl";
		$data = R::getAll($q);
		
		// get tindakan dan bagi per kunjungan
		$k1 = 0;
		$k2 = 0;
		foreach ($data as $k=>$d) {
			if ($k > 0) {
				if ($d['tanggal'] != $data[$k-1]['tanggal']) {
					$k1 += 1;
					$k2 = 0;
					$report[$k1]['tanggal'] = $d['tanggal'];
					$report[$k1]['total'] = 0;
				} else {
					$k2 += 1;
				}
			} else {
				$report[$k1]['tanggal'] = $d['tanggal'];
				$report[$k1]['total'] = 0;
			}
			$report[$k1]['pasien'][$k2] = $d;
			$qt = "SELECT k.kode, t.nama, kt.harga, kt.jumlah, b.dokter as persen FROM kunjungan_tindakan kt
					INNER JOIN m_tindakan t ON kt.id_tindakan = t.id 
					INNER JOIN m_bagi b ON t.id_bagi = b.id 
					INNER JOIN m_kategori_tindakan k ON t.id_kategori = k.id
					WHERE kt.id_kunjungan = ".$d['id'];
			$report[$k1]['pasien'][$k2]['tindakan'] = R::getAll($qt);
			foreach ($report[$k1]['pasien'][$k2]['tindakan'] as $k3=>$t) {	// hitung bagi hasil
				$report[$k1]['pasien'][$k2]['tindakan'][$k3]['hasil'] = $t['jumlah']*$t['harga']*$t['persen']/100;
				$report[$k1]['pasien'][$k2]['tindakan'][$k3]['total'] = $t['jumlah']*$t['harga'];
				$report[$k1]['total'] += $t['jumlah']*$t['harga']*$t['persen']/100;
			}
		}
		
		return $report;
	}
	
	private static function queryGajiDokter($where,$tipe) {
		$where1 = $where;
		
		if ($tipe == 1) {
			$where2 = "where mt.id_bagi = 1";
		} else {
			$where2 = "where mt.id_bagi != 1";
		}
		
		$q = "select c.id, c.nama, c.id_poli, d.id_golongan, d.hari, sum(d.tot) as nominal, count(d.id_pasien) as px, '$tipe' as tipe 
				from 
				(select u.id,u.id_poli, p.nama 
					from user u 
					inner join m_profil p 
					on u.id_profil = p.id) c 
				inner join 
				(select a.id_golongan, a.id_pasien, a.hari, a.id_dokter, round(sum(a.jml*b.dokter*0.01)) as tot 
					from 
					(select k.id_golongan, k.id_pasien, from_unixtime(k.tgl,'%d-%m-%Y') as hari, k.id_dokter, kt.id_tindakan, kt.harga*kt.jumlah as jml 
						from kunjungan k 
						inner join kunjungan_tindakan kt 
						on k.id = kt.id_kunjungan 
						$where1) a 
					inner join 
					(select mt.id, mt.id_bagi, mb.dokter 
						from m_tindakan mt 
						inner join m_bagi mb 
						on mt.id_bagi = mb.id 
						$where2) b 
					on a.id_tindakan = b.id 
					group by id_dokter,hari,id_golongan,id_pasien) d 
				on c.id = d.id_dokter 
				group by id_dokter,hari,id_golongan";
	
		return $q;
	}
	
	public static function pemasukan() {
		
	}
	
	public static function kunjungan($time) {
		$report = array();
		$report["totald"] = 0;
		$report["totalk"] = 0;
		$report["totalu"] = 0;
		$report["totalm"] = 0;
		$report["totala"] = 0;
		
		$q = "select from_unixtime(tgl,'%d %M %Y') as t, id_golongan as gol, count(*) as jml from kunjungan where from_unixtime(tgl,'%m-%Y') = '$time' group by from_unixtime(tgl,'%d-%m-%Y'), id_golongan";
		$data = R::getAll($q);
		
		foreach($data as $d) {
			if(!isset($report["tabel"][$d["t"]]["tgl"])) {
				$report["tabel"][$d["t"]]["tgl"] = $d["t"];
				$report["tabel"][$d["t"]]["d"] = 0;
				$report["tabel"][$d["t"]]["k"] = 0;
				$report["tabel"][$d["t"]]["u"] = 0;
				$report["tabel"][$d["t"]]["m"] = 0;
				$report["tabel"][$d["t"]]["total"] = 0;
			}
			
			if($d["gol"] == 1) {
				$report["tabel"][$d["t"]]["d"] = $d["jml"];
				$report["totald"] = $report["totald"] + $d["jml"];
			} elseif($d["gol"] == 2) {
				$report["tabel"][$d["t"]]["k"] = $d["jml"];
				$report["totalk"] = $report["totalk"] + $d["jml"];
			} elseif($d["gol"] == 3) {
				$report["tabel"][$d["t"]]["u"] = $d["jml"];
				$report["totalu"] = $report["totalu"] + $d["jml"];
			} else {
				$report["tabel"][$d["t"]]["m"] = $d["jml"];
				$report["totalm"] = $report["totalm"] + $d["jml"];
			}
			
			$report["tabel"][$d["t"]]["total"] = $report["tabel"][$d["t"]]["total"] + $d["jml"];
			$report["totala"] = $report["totala"] + $d["jml"];
		}
		
		return $report;
	}
	
	public static function laporanHarian($time,$poli = 0) {
		$report = array();
		$report["totald"] = 0;
		$report["totalk"] = 0;
		$report["totalu"] = 0;
		$report["totalm"] = 0;
		$report["totala"] = 0;
		
		if($poli != 0) {
			$wherepoli = "and k.id_poli = ".$poli;
		} else {
			$wherepoli = "";
		}
		$q = "select from_unixtime(k.tgl,'%d') as t, k.tgl, k.id_golongan as gol, k.baru, p.sex from kunjungan k inner join m_pasien p where k.id_pasien = p.id and from_unixtime(k.tgl,'%m-%Y') = '$time' $wherepoli order by t";
		$data = R::getAll($q);
		
		foreach($data as $d) {
			if(!isset($report["tabel"][$d["t"]]["tgl"])) {
				$report["tabel"][$d["t"]]["tgl"] = $d["t"];
				$report["tabel"][$d["t"]]["plaki"] = 0;
				$report["tabel"][$d["t"]]["pperempuan"] = 0;
				$report["tabel"][$d["t"]]["pbaru"] = 0;
				$report["tabel"][$d["t"]]["plama"] = 0;
				$report["tabel"][$d["t"]]["pd"] = 0;
				$report["tabel"][$d["t"]]["pk"] = 0;
				$report["tabel"][$d["t"]]["pu"] = 0;
				$report["tabel"][$d["t"]]["pm"] = 0;
				$report["tabel"][$d["t"]]["slaki"] = 0;
				$report["tabel"][$d["t"]]["sperempuan"] = 0;
				$report["tabel"][$d["t"]]["sbaru"] = 0;
				$report["tabel"][$d["t"]]["slama"] = 0;
				$report["tabel"][$d["t"]]["sd"] = 0;
				$report["tabel"][$d["t"]]["sk"] = 0;
				$report["tabel"][$d["t"]]["su"] = 0;
				$report["tabel"][$d["t"]]["sm"] = 0;
				$report["tabel"][$d["t"]]["total"] = 0;
				$report["tabel"][$d["t"]]["ptotal"] = 0;
				$report["tabel"][$d["t"]]["stotal"] = 0;
			}
			
			if(date("H",$d["tgl"]) < 13) {
				$shift = "p";
			} else {
				$shift = "s";
			}
			
			if($d["sex"] == "L") {
				$report["tabel"][$d["t"]][$shift."laki"]++;
			} else {
				$report["tabel"][$d["t"]][$shift."perempuan"]++;
			}
			
			if($d["baru"] == "y") {
				$report["tabel"][$d["t"]][$shift."baru"]++;
			} else {
				$report["tabel"][$d["t"]][$shift."lama"]++;
			}
			
			$report["tabel"][$d["t"]]["total"]++;
			$report["tabel"][$d["t"]][$shift."total"]++;
			
			if($d["gol"] == 1) {
				$report["tabel"][$d["t"]][$shift."d"]++;
				$report["totald"]++;
			} elseif($d["gol"] == 2 || $d["gol"] == 4) {
				$report["tabel"][$d["t"]][$shift."k"]++;
				$report["totalk"]++;
			} elseif($d["gol"] == 3) {
				$report["tabel"][$d["t"]][$shift."u"]++;
				$report["totalu"]++;
			} else {
				$report["tabel"][$d["t"]][$shift."m"]++;
				$report["totalm"]++;
			}
			
			$report["totala"]++;
		}
		
		return $report;
	}
	
	public static function kunjunganHarian($time) {
		$q = "select a.id, a.golongan, a.poli, a.id_dokter, n.nama from 
				(select kg.*, p.nama as poli from 
					(select k.*, g.nama as golongan from 
						kunjungan k 
						inner join m_golongan g on k.id_golongan = g.id where FROM_UNIXTIME(tgl,'%d-%m-%Y') = '$time' and k.status = 'bayar') kg 
					inner join m_poli p on kg.id_poli = p.id) a 
				inner join m_pasien n on a.id_pasien = n.id order by a.id";
		$report = R::getAll($q);
		
		foreach($report as $k=>$r) {
			$report[$k]['tindakan'] = KunjunganTindakan::getByKunjungan($r['id']);
			$report[$k]['obat'] = ObatKeluarDetail::getBySumber("kunjungan",$r['id']);
			$report[$k]['biaya'] = SumberBiaya::getBySumber("kunjungan",$r['id']);
			$report[$k]['pembayaran'] = Transaksi::getPembayaranKunjungan($r['id']);
			$report[$k]['dokter'] = User::getNama($r['id_dokter']);
			$report[$k]['tottindakan'] = 0;
			foreach($report[$k]['tindakan'] as $kt=>$rt) {
				$report[$k]['tindakan'][$kt]['subtot'] = $rt['jumlah']*$rt['harga'];
				$report[$k]['tottindakan'] += $report[$k]['tindakan'][$kt]['subtot'];
			}
			$report[$k]['totobat'] = 0;
			foreach($report[$k]['obat'] as $ko=>$ro) {
				$report[$k]['obat'][$ko]['subtot'] = $ro['jumlah']*$ro['harga'];
				$report[$k]['totobat'] += $report[$k]['obat'][$ko]['subtot'];
			}
			$report[$k]['totbiaya'] = 0;
			foreach($report[$k]['biaya'] as $kb=>$rb) {
				$report[$k]['biaya'][$kb]['subtot'] = $rb['harga'];
				$report[$k]['totbiaya'] += $report[$k]['biaya'][$kb]['subtot'];
			}
			$report[$k]['total'] = $report[$k]['tottindakan'] + $report[$k]['totobat'] + $report[$k]['totbiaya'];
		}
		// var_dump($report);die();
		return $report;
	}
	
	public static function neracaPercobaan($tahun) {
		$start = TahunBuku::getStart();
		$q = "SELECT a.kode, a.subkode, a.id, a.nama, a.id_jenis, a.posisi, b.debit, b.kredit, a.normal, a.awal FROM akun a LEFT OUTER JOIN 
			(SELECT ta.id_akun, SUM(ta.debit) as debit, SUM(ta.kredit) AS kredit 
			FROM transaksi_akun ta INNER JOIN transaksi t ON ta.id_transaksi = t.id 
			WHERE RIGHT(t.tgl,4) <= '$tahun' AND RIGHT(t.tgl,4) >= '$start' AND t.status = 'av' GROUP BY ta.id_akun) b ON b.id_akun = a.id 
			WHERE a.status <> 'parent' ORDER BY a.kode, a.subkode";
		$data = R::getAll($q);
		
		$totalDebit = 0;
		$totalKredit = 0;
		foreach ($data as $k=>$d) {
			if ($d['awal'] > 0 || $d['debit'] > 0 || $d['kredit'] > 0) {
				$report['list'][$k]['nama'] = $d['nama'];
				if ($d['normal'] == "debit") {
					$report['list'][$k]['debit'] = $d['awal'] + $d['debit'] - $d['kredit'];
					$report['list'][$k]['kredit'] = 0;
					$totalDebit += $report['list'][$k]['debit'];
				} else {
					$report['list'][$k]['kredit'] = $d['awal'] + $d['kredit'] - $d['debit'];
					$report['list'][$k]['debit'] = 0;
					$totalKredit += $report['list'][$k]['kredit'];
				}
			}
		}
		$report['totalDebit'] = $totalDebit;
		$report['totalKredit'] = $totalKredit;
		
		return $report;
	}
	
	public static function neraca($tahun) {
		$totalDebit = 0;
		$totalKredit = 0;
		$report['totaldebit'] = 0;
		$report['totalkredit'] = 0;
		
		$jenisAkun = JenisAkun::getByPosisi("neraca");
		foreach ($jenisAkun as $k=>$j) {
			$report['jenis'.$j['normal']][$j['id']]['nama'] = $j['nama'];
			$normal[$j['id']] = $j['normal'];
			$report['jenis'.$j['normal']][$j['id']]['total'] = 0;
		}

		$start = TahunBuku::getStart();
		$q = "SELECT a.kode, a.subkode, a.id, a.nama, a.id_jenis, a.posisi, b.debit, b.kredit, a.normal, a.awal FROM akun a LEFT OUTER JOIN 
			(SELECT ta.id_akun, SUM(ta.debit) as debit, SUM(ta.kredit) AS kredit 
			FROM transaksi_akun ta INNER JOIN transaksi t ON ta.id_transaksi = t.id 
			WHERE RIGHT(t.tgl,4) <= '$tahun' AND RIGHT(t.tgl,4) >= '$start' AND t.status = 'av' GROUP BY ta.id_akun) b ON b.id_akun = a.id 
			WHERE a.status <> 'parent' ORDER BY a.kode, a.subkode";
		$data = R::getAll($q);
		foreach ($data as $k=>$d) {
			$debit = ($d['debit'] == null ? 0 : $d['debit']);
			$kredit = ($d['kredit'] == null ? 0 : $d['kredit']);
			if ($d['posisi'] == "neraca") {	// hitung akun neraca
				$n = $normal[$d['id_jenis']];
				$report['jenis'.$n][$d['id_jenis']]['akun'][$d['id']]['kode'] = $d['kode']."-".$d['subkode'];
				$report['jenis'.$n][$d['id_jenis']]['akun'][$d['id']]['nama'] = $d['nama'];
				if ($d['normal'] == "debit") {
					$total = $d['awal'] + $debit - $kredit;
				} else {
					$total = $d['awal'] + $kredit - $debit;
				}
				$report['jenis'.$n][$d['id_jenis']]['akun'][$d['id']]['total'] = $total;
				if ($d['normal'] != $n) {
					$report['jenis'.$n][$d['id_jenis']]['akun'][$d['id']]['nilai'] = "minus";
					$report['jenis'.$n][$d['id_jenis']]['total'] -= $total;
					$report['total'.$n] -= $total;
				} else {
					$report['jenis'.$n][$d['id_jenis']]['akun'][$d['id']]['nilai'] = "plus";
					$report['jenis'.$n][$d['id_jenis']]['total'] += $total;
					$report['total'.$n] += $total;
				}
			} else {	// totalan akun labarugi
				$totalDebit += $debit;
				$totalKredit += $kredit;
			}
		}
		
		// masukkan totalan labarugi ke akun hasil usaha
		$labarugi = $totalKredit - $totalDebit;
		$totalusaha = $report['jeniskredit'][5]['akun'][24]['total'] + $labarugi;
		$report['jeniskredit'][5]['akun'][24]['total'] += $labarugi;
		$report['totalkredit'] += + $labarugi;
		$report['jeniskredit'][5]['total'] += + $labarugi;
		if ($totalusaha < 0) {	//	jika minus
			$report['jeniskredit'][5]['akun'][24]['total'] *= -1;
			$report['jeniskredit'][5]['akun'][24]['nilai'] = "minus";
		} else {
			$report['jeniskredit'][5]['akun'][24]['nilai'] = "plus";
		}
		
		return $report;
	}
	
	public static function bukuBesar($bentuk,$idAkun,$arrTgl) {
		$t = Rumus::addZero($arrTgl['tanggal']);
		$b = Rumus::addZero($arrTgl['bulan']);
		switch($bentuk){
			case "tahunan":
				$tgl = "01-01-".$arrTgl['tahun'];
				$q = "SELECT RIGHT(t.tgl,7) as bulan, SUM(ta.debit) as debit, SUM(ta.kredit) as kredit FROM transaksi_akun ta 
					LEFT OUTER JOIN transaksi t ON ta.id_transaksi = t.id 
					WHERE ta.id_akun = ".$idAkun." and RIGHT(t.tgl,4) = '".$arrTgl['tahun']."' AND t.status = 'av' 
					GROUP BY bulan ORDER BY bulan ASC";
			break;
			case "bulanan":
				$bulan = $b."-".$arrTgl['tahun'];
				$tgl = "01-".$b."-".$arrTgl['tahun'];
				$q = "SELECT t.tgl, SUM(ta.debit) as debit, SUM(ta.kredit) as kredit FROM transaksi_akun ta 
					LEFT OUTER JOIN transaksi t ON ta.id_transaksi = t.id 
					WHERE ta.id_akun = ".$idAkun." and RIGHT(t.tgl,7) = '".$bulan."' AND t.status = 'av' 
					GROUP BY t.tgl ORDER BY t.tgl ASC";
			break;
			case "harian":
				$tgl = $t."-".$b."-".$arrTgl['tahun'];
				$q = "SELECT t.tgl, t.uraian, ta.debit, ta.kredit FROM transaksi_akun ta 
					LEFT OUTER JOIN transaksi t ON ta.id_transaksi = t.id 
					WHERE ta.id_akun = ".$idAkun." AND t.tgl = '$tgl' AND t.status = 'av' ORDER BY ta.id ASC";
			break;
		}
		$report['data'] = R::getAll($q);
		
		// hitung saldo awal
		$saldoAwal = Akun::getSaldoAwal($idAkun);
		$saldoAkun = TransaksiAkun::getSaldo($idAkun,$tgl);
		$akunNormal = Akun::getNormal($idAkun);
		if ($akunNormal == "debit") {
			$saldo = $saldoAwal + $saldoAkun['debit'] - $saldoAkun['kredit'];
		} else {
			$saldo = $saldoAwal - $saldoAkun['debit'] + $saldoAkun['kredit'];
		}
		$report['saldoAwal'] = $saldo;
		
		// hitung saldo per row
		$debit = 0;
		$kredit = 0;
		foreach ($report['data'] as $k=>$i) {
			if ($akunNormal == "debit") {
				$saldo = $saldo + $i['debit'] - $i['kredit'];
			} else {
				$saldo = $saldo - $i['debit'] + $i['kredit'];
			}
			$debit += $i['debit'];
			$kredit += $i['kredit'];
			$report['data'][$k]['saldo'] = $saldo;
		}
		$report['debitAkhir'] = $debit;
		$report['kreditAkhir'] = $kredit;
		$report['saldoAkhir'] = $saldo;
		
		return $report;
	}
	
	public static function labaRugi($tahun,$bulan = 0) {
		if ($bulan > 0) {
			$b = Rumus::addZero($bulan);
			$whereBulan = "AND MID(t.tgl,4,2) = $b";
		} else {
			$whereBulan = "";
		}
		$report = JenisAkun::getByPosisi("laba-rugi");
		foreach ($report as $k=>$ja) {
			$j = $ja['id'];
			$q = "SELECT a.nama, a.kode, a.subkode, SUM(ta.debit) as debit, SUM(ta.kredit) as kredit, a.normal 
				FROM transaksi_akun ta INNER JOIN akun a on ta.id_akun = a.id INNER JOIN transaksi t ON t.id = ta.id_transaksi 
				WHERE a.id_jenis = $j AND RIGHT(t.tgl,4) = '$tahun' $whereBulan GROUP BY a.nama";
			$report[$k]['akun'] = R::getAll($q);
			$total = 0;
			foreach ($report[$k]['akun'] as $k2=>$t) {
				if ($t['normal'] == "debit") {
					$nominal = $t['debit'] - $t['kredit'];
				} else {
					$nominal = $t['kredit'] - $t['debit'];
				}
				$report[$k]['akun'][$k2]['nominal'] = $nominal;
				$total += $nominal;
			}
			$report[$k]['total'] = $total;
		}
		
		return $report;
	}
	
	public static function getStockObat($id = 0) {
		if ($id != 0) {
			$where = "WHERE a.id_obat = $id";
		} else {
			$where = "";
		}
		
		$q = "SELECT a.*, b.keluar as keluar FROM 
				(SELECT om.id_obat, o.nama, SUM(om.jumlah) as masuk, s.kode as satuan FROM obat_masuk om 
					INNER JOIN m_obat o ON om.id_obat = o.id 
					INNER JOIN m_satuan s ON o.id_satuan_kecil = s.id GROUP BY om.id_obat) a 
				LEFT JOIN (SELECT id_obat, SUM(jumlah) as keluar FROM obat_keluar GROUP BY id_obat) b ON a.id_obat = b.id_obat $where";
		$data = R::getAll($q);
		foreach ($data as $k=>$d) {
			$data[$k]['jumlah'] = $d['masuk'] - $d['keluar'];
		}
		return $data;
	}
	
	public static function getStockLengkap() {
		$q = "SELECT a.*, b.keluar as keluar, c.exp, c.pbf, c.harga_beli, c.harga_jual FROM 
				(SELECT om.id_obat, o.kode, o.nama, SUM(om.jumlah) as masuk, s.kode as satuan FROM obat_masuk om 
					INNER JOIN m_obat o ON om.id_obat = o.id 
					INNER JOIN m_satuan s ON o.id_satuan_kecil = s.id GROUP BY om.id_obat) a 
				LEFT JOIN (SELECT id_obat, SUM(jumlah) as keluar FROM obat_keluar GROUP BY id_obat) b ON a.id_obat = b.id_obat 
				INNER JOIN (SELECT o1.*, p.nama as pbf FROM obat_masuk o1 
					LEFT JOIN obat_masuk o2 ON (o1.id_obat = o2.id_obat AND o1.id < o2.id) 
					LEFT JOIN m_pemasok p ON o1.id_pemasok = p.id WHERE o2.id IS NULL) c ON a.id_obat = c.id_obat 
				ORDER BY a.nama";
		$data = R::getAll($q);
		foreach ($data as $k=>$d) {
			$data[$k]['jumlah'] = $d['masuk'] - $d['keluar'];
			$data[$k]['exp'] = Rumus::reverseDate($d['exp']);
		}
		return $data;
	}
	
	public static function totalPenjualan($tgl) {	// tgl YYYY-MM-DD or YYYY-MM or YYYY
		
		$t = explode("-",$tgl);
		switch (count($t)) {
			case 1 : $whereTgl = "AND FROM_UNIXTIME(t.tgl_unix,'%Y') LIKE '$tgl'";
			break;
			case 2 : $whereTgl = "AND FROM_UNIXTIME(t.tgl_unix,'%Y-%m') = '$tgl'";
			break;
			case 3 : $whereTgl = "AND FROM_UNIXTIME(t.tgl_unix,'%Y-%m-%d') = '$tgl'";
			break;
		}
		
		$q = "SELECT SUM(ta.debit) AS total FROM transaksi t 
				INNER JOIN transaksi_akun ta ON t.id = ta.id_transaksi 
				WHERE t.sumber = 'penjualan' AND ta.debit > 0 $whereTgl";
		$data = R::getCell($q)*1;
		if (is_null($data)) {
			$data = 0;
		}

		return $data;
	}
	
	public static function totalHutangBelanja() {
		$q = "SELECT SUM(ta.debit) as bayar, SUM(ta.kredit) as hutang FROM transaksi t 
				INNER JOIN transaksi_akun ta ON t.id = ta.id_transaksi 
				WHERE (t.sumber = 'pemasok' OR t.sumber = 'transaksi') AND ta.id_akun = 17";
		
		$d = R::getRow($q);
		if (is_null($d)) {
			$data = 0;
		} else {
			$data = $d['hutang'] - $d['bayar'];
		}
		
		return $data;
	}
	
	public static function belanjaBulan($bln) {
		$q = "SELECT SUM(ta.debit) as total FROM transaksi t 
				INNER JOIN transaksi_akun ta ON t.id = ta.id_transaksi 
				WHERE FROM_UNIXTIME(t.tgl_unix,'%Y-%m') = $bln AND ta.id_akun = 29";
		
		$data = R::getCell($q);
		if (is_null($data)) {
			$data = 0;
		}
		
		return $data;
	}
	
	public static function dataPenjualanObat($awal,$akhir) {	// YYYY-MM-DD
		$q = "SELECT t.*, FROM_UNIXTIME(tgl_unix,'%d-%m-%Y') as tgl, FROM_UNIXTIME(tgl_unix,'%H:%i') as jam, o.nama, ok.jumlah, ok.harga, ok.diskon, ok.tipe_diskon 
				FROM transaksi t 
				INNER JOIN obat_keluar ok ON t.id = ok.id_sumber 
				INNER JOIN m_obat o ON ok.id_obat = o.id 
				WHERE ok.sumber = 'transaksi' AND FROM_UNIXTIME(t.tgl_unix,'%Y-%m-%d') >= '$awal' AND FROM_UNIXTIME(t.tgl_unix,'%Y-%m-%d') <= '$akhir'";
		
		$data = R::getAll($q);
		foreach ($data as $k=>$d) {
			$total = $d['jumlah'] * $d['harga'];
			if ($d['tipe_diskon'] == 1) {
				$diskon = $total * $d['diskon'] / 100;
			} else {
				$diskon = $d['diskon'];
			}
			$data[$k]['total'] = $total - $diskon;
		}
		
		return $data;
	}
	
	private static function spreadData($type,$data) {
		$report = array();
		$loop = 0;
		switch($type){
			case "15m":
				$loop = 31;
			break;
			case "daily":
				$loop = 31;
			break;
			case "monthly":
				$loop = 12;
			break;
			case "hourly":
				$loop = 24;
			break;
		}
		for ($i = 1;$i<=$loop;$i++) {
			if (count($data) > 0) {
				foreach ($data as $k=>$d) {
					$e = explode('-',$d['tgl']);
					if (!isset($report[$i])) $report[$i] = 0;;
					if ($e[0] == $i && $report[$i] == 0) {
						$report[$i] = $d['count'];
					} 
				}
			} else {
				$report[$i] = 0;
			}
		}
		
		return $report;
	}
	
	public static function minuteDiff($t1,$t2) {
		$t2_time=strtotime($t2);
		$t1_time=strtotime($t1);
		
		if ($t2_time > $t1_time) {
			return round(abs($t2_time - $t1_time) / 60,2);
		}
		else {
			return 0;
		}
	}
	
	public static function formatMinute($min) {
	
		return str_pad(floor($min / 60), 2, "0", STR_PAD_LEFT) .":".  str_pad(($min - (floor($min / 60) * 60)), 2, "0", STR_PAD_LEFT); 
	}
	
	public static function addMinutes($t,$add) {
		$sep = ".";
		$t = explode(".",$t);
		
		if (count($t) == 0) {
			$sep = ".";
			$t = explode(":",$t);
		}
		$h = $t[0] * 1;
		$m = $t[1] * 1;
		
		$m += $add;
		if ($m > 59) {
			$m = 0;
			$h += 1;
		} 
		
		if ($h > 23) {
			$h = 0;
		}
		
		$m = str_pad($m, 2, "0", STR_PAD_LEFT);
		$h = str_pad($h, 2, "0", STR_PAD_LEFT);
		return "$h{$sep}$m";
	}

	public static function total_month($t,$tt) {
		$date1 = mktime(0,0,0,$t[1],$t[0],$t[2]); // m d y, use 0 for day
		$date2 = mktime(0,0,0,$tt[1],$tt[0],$tt[2]); // m d y, use 0 for day
		
		return floor(($date2-$date1) / 60 / 60 / 24 / 30) ;

	}
	
	public static function compare_date($a,$t) {
		$aa = explode("-",$a);
		$tt = explode("-",$t);
		
		if ($aa[0] * 1 == $tt[0] * 1 && $aa[1] * 1 == $tt[1] * 1 && $aa[2] * 1 == $tt[2] * 1)
			return true;
		else 
			return false;
	}

	public static function generatePage($current_page,$count_per_page,$countAll,$base_url,$find = "") {
		$total_page = ceil($countAll/$count_per_page);
		for ($i = 1;$i <= $total_page;$i++) {
			$active = ($i == $current_page ? "yes" : "no");
			$page[] = array("number"=>$i,"active"=>$active,"link"=>$base_url."?p=$i&find=$find");
		}
		return  (isset($page) ? $page : array());
	}
	
	private static function generateWeeklyWhere($date,$find = "") {
		$d = explode("-",$date);
		$start = "{$d[2]}-{$d[1]}-{$d[0]}";
		$timestamp = strtotime("+1 week",mktime(0, 0, 0, $d[1], $d[0], $d[2]));
		if (date('m',$timestamp) > $d[1]) {
			$lastdate = date('d',strtotime('-1 second',strtotime('+1 month',strtotime($d[1].'/01/'.$d[2].' 00:00:00'))));
			$success = "{$d[2]}-{$d[1]}-" . $lastdate;
		} else {
			$success = date('Y-m-d',$timestamp);
		}
		if ($find == "")
			$where = "where from_unixtime(date) >= '$start' and  from_unixtime(date) < '$success'";
		else
			$where = "where from_unixtime(date) >= '$start' and  from_unixtime(date) < '$success' and (a.id like '%{$find}%' or c.nama like '%{$find}%' or c.alamat like '%{$find}%' or c.telepon like '%{$find}%' or store_nama like '%{$find}%' or status like '%{$find}%')";
		return $where;
	}
	
	public static function generateDate($field_name,$array) {
		if (isset($array[$field_name.'_date']) && isset($array[$field_name.'_month']) && isset($array[$field_name.'_year']))
		return Rumus::addZero($array[$field_name.'_date']) ."-". Rumus::addZero($array[$field_name.'_month']) ."-". $array[$field_name.'_year'];
		
		if (isset($array[$field_name.'_month']) && isset($array[$field_name.'_year']))
		return Rumus::addZeroDate($array[$field_name.'_month']) ."-". $array[$field_name.'_year'];
		
		if (isset($array[$field_name.'_year']))
		return $array[$field_name.'_year'];
		
	}
}
?>