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

$ta = mysqli_fetch_array(mysqli_query($conn,"SELECT * FROM tahun_ajaran where idTahunAjaran='$_GET[tahun]'"));
$idTahun = $ta['idTahunAjaran'];
$idt = mysqli_fetch_array(mysqli_query($conn,"SELECT * FROM identitas "));
$pos = mysqli_fetch_array(mysqli_query($conn,"SELECT * FROM pos_bayar where idPosBayar='$_GET[pos]'"));

?>
<!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 />
	<h3 class="box-title">Pos Pembayaran <?php echo $pos['nmPosBayar']; ?> <?php echo $ta['nmTahunAjaran']; ?></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>
				<th>Jumlah Tagihan</th>
			</tr>
		</thead>
		<tbody>
			<?php
			$no = 1;
			$totDibayar = 0;
			$totTagihan = 0;
			$sqlJenisBayar = mysqli_query($conn,"SELECT * FROM jenis_bayar WHERE idTahunAjaran='$idTahun' AND idPosBayar='$pos[idPosBayar]'");
			while ($djb = mysqli_fetch_array($sqlJenisBayar)) {
				if ($djb['tipeBayar'] == 'bulanan') {
					//menghitung semua tagihan bulanan
					$tgbul 	=	mysqli_fetch_array(mysqli_query($conn,"SELECT
									jenis_bayar.idPosBayar,
									pos_bayar.nmPosBayar,
									Sum(tagihan_bulanan.jumlahBayar) AS TotalSemuaTagihanBulanan,
									 sum(tagihan_bulanan.dispensasiBayar) as dispen
									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]'
									GROUP BY
									jenis_bayar.idPosBayar"));
					$semuaTagihan = $tgbul['TotalSemuaTagihanBulanan'] - $tgbul['dispen'];

					$dbayar = mysqli_fetch_array(mysqli_query($conn,"SELECT
									jenis_bayar.idPosBayar,
									pos_bayar.nmPosBayar,
									jenis_bayar.idTahunAjaran,
									tahun_ajaran.nmTahunAjaran,
									jenis_bayar.nmJenisBayar,
									Sum(tagihan_bulanan.jumlahBayar) AS TotalPembayaranPerJenis,
									 sum(tagihan_bulanan.dispensasiBayar) as dispens,
									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
									INNER JOIN tahun_ajaran ON jenis_bayar.idTahunAjaran = tahun_ajaran.idTahunAjaran
									WHERE tagihan_bulanan.idJenisBayar='$djb[idJenisBayar]' AND tagihan_bulanan.statusBayar='1'
									GROUP BY
									tagihan_bulanan.idJenisBayar"));
					$jBayar 	= $dbayar['TotalPembayaranPerJenis'] - $dbayar['dispens'];
					$tagihan 	= $semuaTagihan - $jBayar;
				} else {
					//menghitung semua tagihan bebas
					$tgb 	= 	mysqli_fetch_array(mysqli_query($conn,"SELECT
										tagihan_bebas.idTagihanBebas,
										jenis_bayar.idPosBayar,
										pos_bayar.nmPosBayar,
										SUM(tagihan_bebas.totalTagihan) As TotalSemuaTagihanBebas
										 
										FROM
										tagihan_bebas
										INNER JOIN jenis_bayar ON tagihan_bebas.idJenisBayar = jenis_bayar.idJenisBayar
										INNER JOIN pos_bayar ON jenis_bayar.idPosBayar = pos_bayar.idPosBayar
										WHERE tagihan_bebas.idJenisBayar='$djb[idJenisBayar]'
										GROUP BY
										jenis_bayar.idPosBayar"));
					$semuaTagihan = $tgb['TotalSemuaTagihanBebas'];

					$dbayar = mysqli_fetch_array(mysqli_query($conn,"SELECT
									tagihan_bebas.idJenisBayar,
									jenis_bayar.nmJenisBayar,
									jenis_bayar.idTahunAjaran,
									tahun_ajaran.nmTahunAjaran,
									tagihan_bebas_bayar.idTagihanBebas,
									Sum(tagihan_bebas_bayar.jumlahBayar) AS TotalPembayaranPerJenis,
									 sum(tagihan_bebas_bayar.dispensasiBayar) as dispens,
									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
									INNER JOIN tahun_ajaran ON jenis_bayar.idTahunAjaran = tahun_ajaran.idTahunAjaran
									WHERE tagihan_bebas_bayar.idTagihanBebas='$tgb[idTagihanBebas]'
									GROUP BY
									tagihan_bebas_bayar.idTagihanBebas"));
					$jBayar 	= $dbayar['TotalPembayaranPerJenis'] - $dbayar['dispens'];
					$jBayars 	= $dbayar['TotalPembayaranPerJenis'];
					$tagihan 	= $semuaTagihan - $jBayars;
				}
				echo "<tr>
							<td align='center'>$no</td>
							<td>$pos[nmPosBayar]</td>
							<td>$djb[nmJenisBayar]</td>
							<td align='right'>" . buatRp($jBayar) . "</td>
							<td align='right'>" . buatRp($tagihan) . "</td>
						</tr>";
				$no++;
				$totDibayar += $jBayar;
				$totTagihan += $tagihan;
			}
			?>
			<tr>
				<td></td>
				<td colspan="2">Jumlah Pembayaran</td>
				<td align="right"><b><?php echo buatRp($totDibayar); ?></b></td>
				<td align="right"><b><?php echo buatRp($totTagihan); ?></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 />Kepala Sekolah,<br /><br /><br /><br />
				<b><u><?php echo $idt['nmKepsek']; ?></u></b>
			</td>
		</tr>
	</table>
	<table width="100%">
		<tr>
			<td align="center">
				<br />Kepala Sekolah,<br /><br /><br /><br />
				<b><u><?php echo $idt['nmKepsek']; ?></u></b>
			</td>
			<td align="center" width="400px">
				<?php echo $idt['kabupaten']; ?>, <?php echo tgl_raport(date("Y-m-d")); ?>
				<br />Bendahara Sekolah,<br /><br /><br /><br />
				<b><u><?php echo $idt['nmBendahara']; ?></u></b>

			</td>
		</tr>
	</table>
</body>
<script>
	window.print()
</script>

</html>