<?php 
session_start();
error_reporting(0);
include "config/koneksi.php";
include "config/fungsi_indotgl.php";
include "config/library.php";

//$ta = mysql_fetch_array(mysql_query("SELECT * FROM tahun_ajaran where idTahunAjaran='$_GET[tahun]'"));
//$idTahun = $ta['idTahunAjaran'];
$idt = mysql_fetch_array(mysql_query("SELECT * FROM identitas "));
$pos = mysql_fetch_array(mysql_query("SELECT * FROM pos_bayar where idPosBayar='$_GET[pos]'"));

$PosBayar= $_GET[pos];
$OpsiBayar=$_GET[opsi];
$MulaiTgl = $_GET[tgl1];
$SampaiTgl = $_GET[tgl2];
?>
<!DOCTYPE html>
<html>
<head>
<title>Cetak - Laporan Pembayaran Per Pos Bayar</title>
<link rel="stylesheet" href="bootstrap/css/printer.css">
</head>
<body>
<table width="100%">
		<tr>
			<td width="100px" align="left"><img src="./gambar/logo/<?php echo $idt['logo_kiri']; ?>" height="60px"></td>
			<td valign="top">
				<h3 align="center" style="margin-bottom:8px ">
						<?php echo $idt['nmSekolah']; ?> 
				</h3>
				<center><?php echo $idt['alamat']; ?></center>
			</td>
			<td width="100px" align="right"><img src="./gambar/logo/<?php echo $idt['logo_kanan']; ?>" height="60px"></td>
		</tr>
	</table>
	<hr>
	<h3 align="center">LAPORAN PEMBAYARAN</h3>
<hr/>

<table border="1" class="table table-bordered table-striped">
					<thead>
						<tr>
							<th>No.</th>
							<th>Pos Bayar</th>
							<th>Jenis Pembayaran</th>
							<th>Jumlah Pembayaran</th>
						</tr>
					</thead>
					<tbody>
					<?php
					$no=1;
					$totDibayar = 0;
					if (($PosBayar == 'Semua Pos Bayar') AND ($OpsiBayar == 'Semua Opsi Bayar')){
						//bulanan
						$sqlLapBayBul = mysql_query("SELECT * FROM view_laporan_bayar_bulanan WHERE(DATE(tglBayar) BETWEEN '$MulaiTgl' AND '$SampaiTgl') GROUP BY idJenisBayar");
						while($djb=mysql_fetch_array($sqlLapBayBul)){
							$sqlJenBay = mysql_fetch_array(mysql_query("SELECT * FROM jenis_bayar WHERE idJenisBayar='$djb[idJenisBayar]'"));
							$sqlpOS =mysql_fetch_array(mysql_query("SELECT * FROM pos_bayar WHERE idPosBayar='$sqlJenBay[idPosBayar]'"));
							$dbayar = mysql_fetch_array(mysql_query("SELECT
										jenis_bayar.idPosBayar,
										pos_bayar.nmPosBayar,
										jenis_bayar.nmJenisBayar,
										Sum(tagihan_bulanan.jumlahBayar) AS TotalPembayaranPerJenis,
										tagihan_bulanan.statusBayar
										FROM
										tagihan_bulanan
										INNER JOIN jenis_bayar ON tagihan_bulanan.idJenisBayar = jenis_bayar.idJenisBayar
										INNER JOIN pos_bayar ON jenis_bayar.idPosBayar = pos_bayar.idPosBayar
										WHERE tagihan_bulanan.idJenisBayar='$djb[idJenisBayar]' AND tagihan_bulanan.statusBayar='1' AND jenis_bayar.tipeBayar='bulanan' AND (DATE(tagihan_bulanan.tglBayar) BETWEEN '$MulaiTgl' AND '$SampaiTgl')
										GROUP BY
										tagihan_bulanan.idJenisBayar"));
							$jBayar 	= $dbayar['TotalPembayaranPerJenis'];

							echo "<tr>
								<td align='center'>$no</td>
								<td>$sqlpOS[nmPosBayar]</td>
								<td>$djb[nmJenisBayar]</td>
								<td align='right'>".buatRp($jBayar)."</td>
								</tr>";
								$no++;
								$totDibayar += $jBayar;
						}
						//bebas
						$sqlLapBayBeb = mysql_query("SELECT * FROM tagihan_bebas_bayar INNER JOIN tagihan_bebas ON tagihan_bebas_bayar.idTagihanBebas = tagihan_bebas.idTagihanBebas INNER JOIN jenis_bayar ON tagihan_bebas.idJenisBayar = jenis_bayar.idJenisBayar WHERE (DATE(tagihan_bebas_bayar.tglBayar) BETWEEN '$MulaiTgl' AND '$SampaiTgl') GROUP BY tagihan_bebas.idJenisBayar");
						while($djb=mysql_fetch_array($sqlLapBayBeb)){
							$sqlJenBay = mysql_fetch_array(mysql_query("SELECT * FROM jenis_bayar WHERE idJenisBayar='$djb[idJenisBayar]'"));
							$sqlpOS =mysql_fetch_array(mysql_query("SELECT * FROM pos_bayar WHERE idPosBayar='$sqlJenBay[idPosBayar]'"));
							$dbayar = mysql_fetch_array(mysql_query("SELECT
												tagihan_bebas.idJenisBayar,
												jenis_bayar.nmJenisBayar,
												tagihan_bebas_bayar.idTagihanBebas,
												Sum(tagihan_bebas_bayar.jumlahBayar) AS TotalPembayaranPerJenis,
												tagihan_bebas_bayar.ketBayar
												FROM
												tagihan_bebas_bayar
												INNER JOIN tagihan_bebas ON tagihan_bebas_bayar.idTagihanBebas = tagihan_bebas.idTagihanBebas
												INNER JOIN jenis_bayar ON tagihan_bebas.idJenisBayar = jenis_bayar.idJenisBayar
												WHERE tagihan_bebas.idJenisBayar='$djb[idJenisBayar]' AND (DATE(tagihan_bebas_bayar.tglBayar) BETWEEN '$MulaiTgl' AND '$SampaiTgl') AND jenis_bayar.tipeBayar='bebas'
												GROUP BY jenis_bayar.idJenisBayar"));
									$jBayar 	= $dbayar['TotalPembayaranPerJenis'];

									echo "<tr>
										<td align='center'>$no</td>
										<td>$sqlpOS[nmPosBayar]</td>
										<td>$djb[nmJenisBayar]</td>
										<td align='right'>".buatRp($jBayar)."</td>
									</tr>";
									$no++;
									$totDibayar += $jBayar;
						}

					} else if (($PosBayar != 'Semua Pos Bayar') AND ($OpsiBayar == 'Semua Opsi Bayar')){
						//bulanan
						$sqlLapBayBul = mysql_query("SELECT * FROM view_laporan_bayar_bulanan INNER JOIN jenis_bayar ON view_laporan_bayar_bulanan.idJenisBayar = jenis_bayar.idJenisBayar WHERE jenis_bayar.idPosBayar='$PosBayar' AND (DATE(view_laporan_bayar_bulanan.tglBayar) BETWEEN '$MulaiTgl' AND '$SampaiTgl') GROUP BY view_laporan_bayar_bulanan.idJenisBayar");
						while($djb=mysql_fetch_array($sqlLapBayBul)){	
							$sqlJenBay = mysql_fetch_array(mysql_query("SELECT * FROM jenis_bayar WHERE idJenisBayar='$djb[idJenisBayar]'"));
							$sqlpOS =mysql_fetch_array(mysql_query("SELECT * FROM pos_bayar WHERE idPosBayar='$sqlJenBay[idPosBayar]'"));
							$dbayar = mysql_fetch_array(mysql_query("SELECT
										jenis_bayar.idPosBayar,
										pos_bayar.nmPosBayar,
										jenis_bayar.nmJenisBayar,
										Sum(tagihan_bulanan.jumlahBayar) AS TotalPembayaranPerJenis,
										tagihan_bulanan.statusBayar
										FROM
										tagihan_bulanan
										INNER JOIN jenis_bayar ON tagihan_bulanan.idJenisBayar = jenis_bayar.idJenisBayar
										INNER JOIN pos_bayar ON jenis_bayar.idPosBayar = pos_bayar.idPosBayar
										WHERE tagihan_bulanan.idJenisBayar='$djb[idJenisBayar]' AND tagihan_bulanan.statusBayar='1' AND jenis_bayar.tipeBayar='bulanan' AND jenis_bayar.idPosBayar='$PosBayar' AND (DATE(tagihan_bulanan.tglBayar) BETWEEN '$MulaiTgl' AND '$SampaiTgl')
										GROUP BY tagihan_bulanan.idJenisBayar"));
									$jBayar 	= $dbayar['TotalPembayaranPerJenis'];

									echo "<tr>
										<td align='center'>$no</td>
										<td>$sqlpOS[nmPosBayar]</td>
										<td>$djb[nmJenisBayar]</td>
										<td align='right'>".buatRp($jBayar)."</td>
									</tr>";
									$no++;
									$totDibayar += $jBayar;
						}	
						//bebas
						$sqlLapBayBeb = mysql_query("SELECT * FROM tagihan_bebas_bayar INNER JOIN tagihan_bebas ON tagihan_bebas_bayar.idTagihanBebas = tagihan_bebas.idTagihanBebas INNER JOIN jenis_bayar ON tagihan_bebas.idJenisBayar = jenis_bayar.idJenisBayar WHERE jenis_bayar.idPosBayar='$PosBayar' AND (DATE(tagihan_bebas_bayar.tglBayar) BETWEEN '$MulaiTgl' AND '$SampaiTgl') GROUP BY tagihan_bebas.idJenisBayar");
						while($djb=mysql_fetch_array($sqlLapBayBeb)){
							$sqlJenBay = mysql_fetch_array(mysql_query("SELECT * FROM jenis_bayar WHERE idJenisBayar='$djb[idJenisBayar]'"));
							$sqlpOS =mysql_fetch_array(mysql_query("SELECT * FROM pos_bayar WHERE idPosBayar='$sqlJenBay[idPosBayar]'"));
							$dbayar = mysql_fetch_array(mysql_query("SELECT
												tagihan_bebas.idJenisBayar,
												jenis_bayar.nmJenisBayar,
												tagihan_bebas_bayar.idTagihanBebas,
												Sum(tagihan_bebas_bayar.jumlahBayar) AS TotalPembayaranPerJenis,
												tagihan_bebas_bayar.ketBayar
												FROM
												tagihan_bebas_bayar
												INNER JOIN tagihan_bebas ON tagihan_bebas_bayar.idTagihanBebas = tagihan_bebas.idTagihanBebas
												INNER JOIN jenis_bayar ON tagihan_bebas.idJenisBayar = jenis_bayar.idJenisBayar
												WHERE tagihan_bebas.idJenisBayar='$djb[idJenisBayar]'  AND (DATE(tagihan_bebas_bayar.tglBayar) BETWEEN '$MulaiTgl' AND '$SampaiTgl') AND jenis_bayar.tipeBayar='bebas' AND jenis_bayar.idPosBayar='$PosBayar'
												GROUP BY
												tagihan_bebas.idJenisBayar"));
									$jBayar 	= $dbayar['TotalPembayaranPerJenis'];

									echo "<tr>
										<td align='center'>$no</td>
										<td>$sqlpOS[nmPosBayar]</td>
										<td>$djb[nmJenisBayar]</td>
										<td align='right'>".buatRp($jBayar)."</td>
									</tr>";
									$no++;
									$totDibayar += $jBayar;
						}
					
					} else if (($PosBayar == 'Semua Pos Bayar') AND ($OpsiBayar != 'Semua Opsi Bayar')){
						//bulanan
						$sqlLapBayBul = mysql_query("SELECT * FROM view_laporan_bayar_bulanan INNER JOIN jenis_bayar ON view_laporan_bayar_bulanan.idJenisBayar = jenis_bayar.idJenisBayar WHERE view_laporan_bayar_bulanan.caraBayar='$OpsiBayar' AND (DATE(view_laporan_bayar_bulanan.tglBayar) BETWEEN '$MulaiTgl' AND '$SampaiTgl') GROUP BY view_laporan_bayar_bulanan.idJenisBayar");
						while($djb=mysql_fetch_array($sqlLapBayBul)){	
							$sqlJenBay = mysql_fetch_array(mysql_query("SELECT * FROM jenis_bayar WHERE idJenisBayar='$djb[idJenisBayar]'"));
							$sqlpOS =mysql_fetch_array(mysql_query("SELECT * FROM pos_bayar WHERE idPosBayar='$sqlJenBay[idPosBayar]'"));
							$dbayar = mysql_fetch_array(mysql_query("SELECT
										jenis_bayar.idPosBayar,
										pos_bayar.nmPosBayar,
										jenis_bayar.nmJenisBayar,
										Sum(tagihan_bulanan.jumlahBayar) AS TotalPembayaranPerJenis,
										tagihan_bulanan.statusBayar
										FROM
										tagihan_bulanan
										INNER JOIN jenis_bayar ON tagihan_bulanan.idJenisBayar = jenis_bayar.idJenisBayar
										INNER JOIN pos_bayar ON jenis_bayar.idPosBayar = pos_bayar.idPosBayar
										WHERE tagihan_bulanan.idJenisBayar='$djb[idJenisBayar]' AND tagihan_bulanan.statusBayar='1' AND jenis_bayar.tipeBayar='bulanan' AND (DATE(tagihan_bulanan.tglBayar) BETWEEN '$MulaiTgl' AND '$SampaiTgl') AND tagihan_bulanan.caraBayar='$OpsiBayar'
										GROUP BY
										tagihan_bulanan.idJenisBayar"));
									$jBayar 	= $dbayar['TotalPembayaranPerJenis'];

									echo "<tr>
										<td align='center'>$no</td>
										<td>$sqlpOS[nmPosBayar]</td>
										<td>$djb[nmJenisBayar]</td>
										<td align='right'>".buatRp($jBayar)."</td>
									</tr>";
									$no++;
									$totDibayar += $jBayar;
						}		
						//bebas
						$sqlLapBayBeb = mysql_query("SELECT * FROM tagihan_bebas_bayar INNER JOIN tagihan_bebas ON tagihan_bebas_bayar.idTagihanBebas = tagihan_bebas.idTagihanBebas INNER JOIN jenis_bayar ON tagihan_bebas.idJenisBayar = jenis_bayar.idJenisBayar WHERE tagihan_bebas_bayar.caraBayar='$OpsiBayar' AND (DATE(tagihan_bebas_bayar.tglBayar) BETWEEN '$MulaiTgl' AND '$SampaiTgl') GROUP BY tagihan_bebas.idJenisBayar");
						while($djb=mysql_fetch_array($sqlLapBayBeb)){
							$sqlJenBay = mysql_fetch_array(mysql_query("SELECT * FROM jenis_bayar WHERE idJenisBayar='$djb[idJenisBayar]'"));
							$sqlpOS =mysql_fetch_array(mysql_query("SELECT * FROM pos_bayar WHERE idPosBayar='$sqlJenBay[idPosBayar]'"));
							$dbayar = mysql_fetch_array(mysql_query("SELECT
												tagihan_bebas.idJenisBayar,
												jenis_bayar.nmJenisBayar,
												tagihan_bebas_bayar.idTagihanBebas,
												Sum(tagihan_bebas_bayar.jumlahBayar) AS TotalPembayaranPerJenis,
												tagihan_bebas_bayar.ketBayar
												FROM
												tagihan_bebas_bayar
												INNER JOIN tagihan_bebas ON tagihan_bebas_bayar.idTagihanBebas = tagihan_bebas.idTagihanBebas
												INNER JOIN jenis_bayar ON tagihan_bebas.idJenisBayar = jenis_bayar.idJenisBayar
												WHERE tagihan_bebas.idJenisBayar='$djb[idJenisBayar]'  AND (DATE(tagihan_bebas_bayar.tglBayar) BETWEEN '$MulaiTgl' AND '$SampaiTgl') AND jenis_bayar.tipeBayar='bebas' AND tagihan_bebas_bayar.caraBayar='$OpsiBayar'
												GROUP BY
												tagihan_bebas.idJenisBayar"));
									$jBayar 	= $dbayar['TotalPembayaranPerJenis'];

									echo "<tr>
										<td align='center'>$no</td>
										<td>$sqlpOS[nmPosBayar]</td>
										<td>$djb[nmJenisBayar]</td>
										<td align='right'>".buatRp($jBayar)."</td>
									</tr>";
									$no++;
									$totDibayar += $jBayar;
						}

					
					} else {
						//bulanan
						$sqlLapBayBul = mysql_query("SELECT * FROM view_laporan_bayar_bulanan INNER JOIN jenis_bayar ON view_laporan_bayar_bulanan.idJenisBayar = jenis_bayar.idJenisBayar WHERE jenis_bayar.idPosBayar='$PosBayar' AND view_laporan_bayar_bulanan.caraBayar='$OpsiBayar' AND (DATE(view_laporan_bayar_bulanan.tglBayar) BETWEEN '$MulaiTgl' AND '$SampaiTgl') GROUP BY view_laporan_bayar_bulanan.idJenisBayar");
						while($djb=mysql_fetch_array($sqlLapBayBul)){	
							$sqlJenBay = mysql_fetch_array(mysql_query("SELECT * FROM jenis_bayar WHERE idJenisBayar='$djb[idJenisBayar]'"));
							$sqlpOS =mysql_fetch_array(mysql_query("SELECT * FROM pos_bayar WHERE idPosBayar='$sqlJenBay[idPosBayar]'"));
							$dbayar = mysql_fetch_array(mysql_query("SELECT
										jenis_bayar.idPosBayar,
										pos_bayar.nmPosBayar,
										jenis_bayar.nmJenisBayar,
										Sum(tagihan_bulanan.jumlahBayar) AS TotalPembayaranPerJenis,
										tagihan_bulanan.statusBayar
										FROM
										tagihan_bulanan
										INNER JOIN jenis_bayar ON tagihan_bulanan.idJenisBayar = jenis_bayar.idJenisBayar
										INNER JOIN pos_bayar ON jenis_bayar.idPosBayar = pos_bayar.idPosBayar
										WHERE tagihan_bulanan.idJenisBayar='$djb[idJenisBayar]' AND tagihan_bulanan.statusBayar='1' AND jenis_bayar.tipeBayar='bulanan' AND (DATE(tagihan_bulanan.tglBayar) BETWEEN '$MulaiTgl' AND '$SampaiTgl') AND tagihan_bulanan.caraBayar='$OpsiBayar'
										GROUP BY
										tagihan_bulanan.idJenisBayar"));
									$jBayar 	= $dbayar['TotalPembayaranPerJenis'];

									echo "<tr>
										<td align='center'>$no</td>
										<td>$sqlpOS[nmPosBayar]</td>
										<td>$djb[nmJenisBayar]</td>
										<td align='right'>".buatRp($jBayar)."</td>
									</tr>";
									$no++;
									$totDibayar += $jBayar;
						}

						//bebas
						$sqlLapBayBeb = mysql_query("SELECT * FROM tagihan_bebas_bayar INNER JOIN tagihan_bebas ON tagihan_bebas_bayar.idTagihanBebas = tagihan_bebas.idTagihanBebas INNER JOIN jenis_bayar ON tagihan_bebas.idJenisBayar = jenis_bayar.idJenisBayar WHERE jenis_bayar.idPosBayar='$PosBayar' AND tagihan_bebas_bayar.caraBayar='$OpsiBayar' AND (DATE(tagihan_bebas_bayar.tglBayar) BETWEEN '$MulaiTgl' AND '$SampaiTgl') GROUP BY tagihan_bebas.idJenisBayar");
						while($djb=mysql_fetch_array($sqlLapBayBeb)){
							$sqlJenBay = mysql_fetch_array(mysql_query("SELECT * FROM jenis_bayar WHERE idJenisBayar='$djb[idJenisBayar]'"));
							$sqlpOS =mysql_fetch_array(mysql_query("SELECT * FROM pos_bayar WHERE idPosBayar='$sqlJenBay[idPosBayar]'"));
							$dbayar = mysql_fetch_array(mysql_query("SELECT
												tagihan_bebas.idJenisBayar,
												jenis_bayar.nmJenisBayar,
												tagihan_bebas_bayar.idTagihanBebas,
												Sum(tagihan_bebas_bayar.jumlahBayar) AS TotalPembayaranPerJenis,
												tagihan_bebas_bayar.ketBayar
												FROM
												tagihan_bebas_bayar
												INNER JOIN tagihan_bebas ON tagihan_bebas_bayar.idTagihanBebas = tagihan_bebas.idTagihanBebas
												INNER JOIN jenis_bayar ON tagihan_bebas.idJenisBayar = jenis_bayar.idJenisBayar
												WHERE tagihan_bebas.idJenisBayar='$djb[idJenisBayar]' AND (DATE(tagihan_bebas_bayar.tglBayar) BETWEEN '$MulaiTgl' AND '$SampaiTgl') AND jenis_bayar.tipeBayar='bebas' AND tagihan_bebas_bayar.caraBayar='$OpsiBayar' AND jenis_bayar.idPosBayar='$PosBayar'
												GROUP BY
												tagihan_bebas.idJenisBayar"));
									$jBayar 	= $dbayar['TotalPembayaranPerJenis'];

									echo "<tr>
										<td align='center'>$no</td>
										<td>$sqlpOS[nmPosBayar]</td>
										<td>$djb[nmJenisBayar]</td>
										<td align='right'>".buatRp($jBayar)."</td>
									</tr>";
									$no++;
									$totDibayar += $jBayar;
						}
					}
					?>
					<tr>
						<td colspan="3">Jumlah Pembayaran</td>
						<td align="right"><b><?php echo buatRp($totDibayar); ?></b></td>
					</tr>
					</tbody>
				</table>

<br/>
<table width="100%">
<tr>
	<td align="center"></td>
	<td align="center" width="400px">
		<?php echo $idt['kabupaten']; ?>, <?php echo tgl_raport(date("Y-m-d")); ?>
		<br/>Bendahara,<br/><br/><br/><br/>
		<b><u><?php echo $idt['nmBendahara']; ?></u><br/><?php echo $idt['nipBendahara']; ?></b>
	</td>
</tr>
</table>
</body>
<script>
	window.print()
</script>
</html>