<?php
	include '../../inc/inc.koneksi.php';

	$tahun= $_GET['tahun'];
	$thn  = $tahun-2000;
	$bln  = sprintf("%02s",$bulan);
	
	$text = "SELECT * FROM standar ";
	$sql  = mysql_query($text);
	$r	  = mysql_fetch_assoc($sql);

	$kwkar	 = $r[bpjs_kwkar];
	$tkkar	 = $r[bpjs_tkkar];
	$ptkpwp	 = $r[ptkp_wp]/12;
	$ptkpis	 = $r[ptkp_is]/12;
	$ptkpan	 = $r[ptkp_an]/12;
	$maxanak = $r[max_anak];
	$prbijab = $r[pros_bijab];
	$maxbijab= $r[max_bijab];
	$prpph1	 = $r[pros_pph1];
	$prpph2	 = $r[pros_pph2];
	$prpph3	 = $r[pros_pph3];
	$prpph4	 = $r[pros_pph4];
	$netgaji1= $r[net_gaji1];
	$netgaji2= $r[net_gaji2];
	$netgaji3= $r[net_gaji3];
	
	$sql2 = mysql_query("SELECT nama1 FROM company");
	$r2	  = mysql_fetch_assoc($sql2);
	$kop1 = $r2[nama1];
	
	$content = "<b><h3>$kop1</h3></b>
	<b><li>DAFTAR PERHITUNGAN PPH PASAL 21</li></b>
	<b><li>UNTUK TAHUN $tahun</li></b>
	<br>
	
	<table width='390' border='1' style='border-collapse:collapse'>
		<tr>
			<th>No.</th>
			<th>NIK</th>
			<th>Nama Karyawan</th>
			<th>Jabatan</th>
			<th>Jenis Kelamin</th>
			<th>Status</th>
			<th>Jml.Anak</th>
			<th>Gaji Pokok</th>
			<th>Penghasilan Bruto</th>
			<th>Biaya Jabatan ($prbijab % x Bruto)</th>
			<th>THT ($kwkar % x Gapok)</th>
			<th>PTKP</th>
			<th>PPh (Bruto-Bijab-THT-PTKP) x Prosentase</th>
		</tr>";
		
		$sql = "SELECT nik,nama,jabatan,sum(gapok) as jgapok,sum(tot_gaji) as totgaji,sum(pot_absen) as potabsen,count(nik) as jbulan FROM gaji WHERE tahun='$tahun' GROUP BY nik";
				
		$query = mysql_query($sql);
		$no=0;
		$t_gapok  = 0;
		$t_totgaji= 0;
		$t_ptkp   = 0;
		$t_bijab  = 0;
		$t_tht    = 0;
		$t_pph    = 0;
		
		while($r_data=mysql_fetch_array($query)){
			$nik	=$r_data[nik];
			$sql1	=mysql_query("SELECT sex,status_kwn,jml_anak FROM karyawan WHERE nik='$nik'");
			$r1		=mysql_fetch_assoc($sql1);
			$sex	=$r1[sex];
			$status =$r1[status_kwn];
			$anak	=$r1[jml_anak];
			
			$jptkpwp= $ptkpwp*$r_data[jbulan];
			$jptkpis= $ptkpis*$r_data[jbulan];
			$jptkpan= $ptkpan*$r_data[jbulan];
			
			if($anak>3) $anak=3;
			$totgaji=$r_data[totgaji]-$r_data[potabsen];
			$bijab	=$totgaji*$prbijab/100;
			
			if($bijab>$maxbijab) $bijab=$maxbijab;
			
			if($status=='KAWIN') $tht=$r_data[jgapok]*$kwkar/100;
			else $tht=$r_data[jgapok]*$tkkar/100;
				
			if($bulan==13) $ptkp=0;
			else{
				if($sex=='LAKI-LAKI'){
					if($status=='KAWIN') $ptkp=$jptkpwp+$jptkpis;
					else $ptkp=$jptkpwp;
					if($anak>0) $ptkp=$ptkp+($jptkpan*$anak);
				}else{
					if($status=='KAWIN') $ptkp=$jptkpwp;
					else $ptkp=$jptkpwp+($jptkpan*$anak);
				}				
			}
			
			$pkp=$totgaji-$bijab-$tht-$ptkp;
			if($pkp<($netgaji1+1)) $pph=$pkp*$prpph1/100;
			else{
				if($pkp<($netgaji2+1)){
					$pph1=$netgaji1*$prpph1/100;
					$pph2=($pkp-$netgaji1)*$prpph2/100;
					$pph =$pph1+$pph2;
				}else{
					if($pkp<($netgaji3+1)){
						$pph1=$netgaji1*$prpph1/100;
						$pph2=($netgaji2-$netgaji1)*$prpph2/100;
						$pph3=($pkp-$netgaji2)*$prpph3/100;
						$pph =$pph1+$pph2+$pph3;
					}else{
						$pph1=$netgaji1*$prpph1/100;
						$pph2=($netgaji2-$netgaji1)*$prpph2/100;
						$pph3=($netgaji3-$netgaji2)*$prpph3/100;
						$pph4=($pkp-$netgaji3)*$prpph4/100;
						$pph =$pph1+$pph2+$pph3+$pph4;
					}
				}
			}			
			if($pph<0) $pph=0;
		
			$content .="<tr>
					<td align='center'>$no</td>
					<td>$r_data[nik]</td>
					<td>$r_data[nama]</td>
					<td>$r_data[jabatan]</td>
					<td>$sex</td>
					<td>$status</td>
					<td align='center'>$anak</td>
					<td align='right'>".number_format($r_data[jgapok],0)."</td>
					<td align='right'>".number_format($totgaji,0)."</td>
					<td align='right'>".number_format($bijab,0)."</td>
					<td align='right'>".number_format($tht,0)."</td>
					<td align='right'>".number_format($ptkp,0)."</td>
					<td align='right'>".number_format($pph,0)."</td>
				</tr>";
			$no++;
			$t_gapok  =$t_gapok+$r_data[jgapok];
			$t_totgaji=$t_totgaji+$totgaji;
			$t_ptkp	  =$t_ptkp+$ptkp;
			$t_bijab  =$t_bijab+$bijab;
			$t_tht    =$t_tht+$tht;
			$t_pph    =$t_pph+$pph;
		}
		
		$content .= "<tr>
			<td></td>
			<td></td>
			<td></td>
			<td></td>
			<td></td>
			<td></td>
			<td align='right'>Total</td>
			<td aligh='right'>".number_format($t_gapok,0)."</td>
			<td align='right'>".number_format($t_totgaji,0)."</td>
			<td align='right'>".number_format($t_bijab,0)."</td>
			<td align='right'>".number_format($t_tht,0)."</td>
			<td align='right'>".number_format($t_ptkp,0)."</td>
			<td align='right'>".number_format($t_pph,0)."</td>
		</tr>
		</table>";
				
		header("Content-type: application/x-msdownload");
		header("Content-Disposition: attachment; filename=pph$thn.xls");
		header("Pragma: no-cache");
		header("Expires: 0");
		echo $content;
?>