<?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'];
$tahun = $ta['nmTahunAjaran'];

$idt = mysql_fetch_array(mysql_query("SELECT * FROM identitas where npsn='10700295'"));
$pos = mysql_fetch_array(mysql_query("SELECT * FROM pos_bayar where idPosBayar='$_GET[pos]'"));

$sqlSiswa= mysql_query("SELECT * FROM view_detil_siswa
			WHERE idKelas='$_GET[kelas]' AND statusSiswa='Aktif' ORDER BY nmSiswa ASC");
while($dtsiswa=mysql_fetch_array($sqlSiswa)){ //while per page

$idsiswa = $dtsiswa['idSiswa'];
$nissiswa = $dtsiswa['nisSiswa'];
$namasiswa = $dtsiswa['nmSiswa'];
$namakelas = $dtsiswa['nmKelas'];

header("Content-type: application/vnd-ms-excel");
header("Content-Disposition: attachment; filename=laporan_pembayaran_pos_".str_replace(" ", "_", $pos['nmPosBayar'])."_".date('dmyHis').".xls");
?>
<table width="100%">
		<tr>
			<td valign="top">
				<h3 align="center" style="margin-bottom:8px ">
						<?php echo $idt['nmSekolah']; ?> 
				</h3>
				<center><?php echo $idt['alamat']; ?></center>
			</td>
		</tr>
	</table>
	<hr>
<table width="100%">
	<tr>
		<td width="200px">NIS </td>
		<td width="10px">:</td>
		<td> <?php echo $nissiswa; ?></td>
	</tr>
	<tr>
		<td>Nama</td>
		<td>:</td>
		<td> <?php echo $namasiswa; ?></td>
	</tr>
	<tr>
		<td>Kelas </td>
		<td>:</td>
		<td> <?php echo $namakelas; ?></td>
	</tr>
	<tr>
		<td>Tahun Pelajaran</td>
		<td>:</td>
		<td> <?php echo $tahun; ?></td>
	</tr>
</table>
<br>
<table border="1" class="table table-bordered table-striped">
	<thead>
		<tr>
			<th>No.</th>
			<th>Pos Pembayaran</th>
			<th>Jenis Pembayaran</th>
			<th>Tagihan</th>
		</tr>
	</thead>
	<tbody>
	<?php
	$no=1;
	$totDibayar = 0;
	$totTagihan = 0;
	$sqlJenisBayar = mysql_query("SELECT jenis_bayar.*, pos_bayar.nmPosBayar 
				FROM jenis_bayar INNER JOIN pos_bayar ON jenis_bayar.idPosBayar = pos_bayar.idPosBayar
 				WHERE jenis_bayar.idTahunAjaran='$idTahun'");
	while($djb=mysql_fetch_array($sqlJenisBayar)){
		if($djb['tipeBayar']=='bulanan'){
			//menghitung semua tagihan bulanan
			$tgbul 	=	mysql_fetch_array(mysql_query("SELECT
						jenis_bayar.idPosBayar,
						pos_bayar.nmPosBayar,
						tagihan_bulanan.idSiswa,
						Sum(tagihan_bulanan.jumlahBayar) AS TotalSemuaTagihanBulanan
						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.idSiswa='$idsiswa'
						GROUP BY
						jenis_bayar.idPosBayar"));
			$semuaTagihan = $tgbul['TotalSemuaTagihanBulanan'];

			$dbayar = mysql_fetch_array(mysql_query("SELECT
						jenis_bayar.idPosBayar,
						pos_bayar.nmPosBayar,
						jenis_bayar.idTahunAjaran,
						tahun_ajaran.nmTahunAjaran,
						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
						INNER JOIN tahun_ajaran ON jenis_bayar.idTahunAjaran = tahun_ajaran.idTahunAjaran
						WHERE tagihan_bulanan.idJenisBayar='$djb[idJenisBayar]' AND tagihan_bulanan.statusBayar='1' AND tagihan_bulanan.idSiswa='$idsiswa'
						GROUP BY
						tagihan_bulanan.idJenisBayar"));
			$jBayar 	= $dbayar['TotalPembayaranPerJenis'];
			$tagihan 	= $semuaTagihan - $jBayar;

		}else{
			//menghitung semua tagihan bebas
			$tgb 	= 	mysql_fetch_array(mysql_query("SELECT
							tagihan_bebas.idTagihanBebas,
							jenis_bayar.idPosBayar,
							pos_bayar.nmPosBayar,
							tagihan_bebas.idSiswa,
							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]' AND tagihan_bebas.idSiswa='$idsiswa'
							GROUP BY
							jenis_bayar.idPosBayar"));
			$semuaTagihan = $tgb['TotalSemuaTagihanBebas'];

			$dbayar = mysql_fetch_array(mysql_query("SELECT
						tagihan_bebas.idJenisBayar,
						jenis_bayar.nmJenisBayar,
						jenis_bayar.idTahunAjaran,
						tahun_ajaran.nmTahunAjaran,
						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
						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'];
			$tagihan 	= $semuaTagihan - $jBayar;
		}
		echo "<tr>
				<td align='center'>$no</td>
				<td>$djb[nmPosBayar]</td>
				<td>$djb[nmJenisBayar]</td>
				<td align='right'>".buatRp($tagihan)."</td>
			</tr>";
			$no++;
			$totDibayar += $jBayar;
			$totTagihan += $tagihan;
	}
	?>
	<tr>
		<td colspan="3"><b>Total Tagihan</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/>Bendahara,<br/><br/><br/><br/>
		<b><u><?php echo $idt['nmBendahara']; ?></u><br/><?php echo $idt['nipBendahara']; ?></b>
	</td>
</tr>
</table>
<?php
} //end while per page
?>