<?php
include "../../include/config.php";
include "../../function/inc_function.php";
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="data_pending_piutang_'.get_datetime().'.xls"');
header('Cache-Control: max-age=0');

$npwp = isset($_REQUEST['npwp']) ? $_REQUEST['npwp'] : '';
$sup_name = isset($_REQUEST['sup_name']) ? $_REQUEST['sup_name'] : '';
$start_dateacct = get_startdateacct();
$end_dateacct   = get_enddateacct();
$sqlShow 	= mysql_query("SELECT prof_name FROM profil");
$dataShow 	= mysql_fetch_array($sqlShow);

?>
<table width="100%" border="0">
    <tr>
  		<td colspan="6">DATA PENDING PIUTANG</td>
    </tr>
    <tr>
  		<td colspan="6">NPWP : <?=$npwp;?> - <?=$sup_name;?></td>
    </tr>
</table>	
<table width="100%" border="1">	
	<tr>
		<th rowspan="2">No</th>
		<th rowspan="2">Tanggal</th>
		<th rowspan="2">No.Invoice</th>
		<th rowspan="2">Keterangan</th>
		<th colspan="2">Sisa Piutang</th>
	</tr>
	 <tr>
		<th>Non IDR</th>
		<th>IDR</th>
	  </tr>
		<?php
		#Nama Supplier
		#$nm = mysql_fetch_array(mysql_query("SELECT cust_code, cust_name FROM customer WHERE id='$cust_id'"));
		#$jd = mysql_fetch_array(mysql_query("SELECT jd_acctno FROM jurnal_master_detail WHERE jd_code='$supcust_code'"));
		$no=1;$tnilai=0;$tpay=0;$tsisa=0; $tnilai_usd=0;$tpay_usd=0;$tsisa_usd=0;
		$qd=mysql_query("SELECT a.* FROM (SELECT jurnal_idpemasok npwp, jurnal_date tanggal, jurnal_invno noreferensi, jurnal_desc keterangan, 0 debit_idr, 0 debit_nidr,
			jurnal_amount kredit_idr, jurnal_amount_usd kredit_nidr
			FROM jurnal	WHERE jurnal_status=20 AND jurnal_type='PT' AND jurnal_date BETWEEN '$start_dateacct' AND '$end_dateacct' AND jurnal_menu IN('BC','BCINC','BCOUT')
			UNION ALL
			SELECT a.jurnal_idpemasok npwp, a.jurnal_date tanggal, a.jurnal_invno noreferensi, b.jurnald_desc keterangan, 0 debit_idr, 0 debit_nidr,
			a.jurnal_amount kredit_idr, a.jurnal_amount_usd kredit_nidr
			FROM jurnal a	INNER JOIN jurnal_detail b ON b.jurnal_no=a.jurnal_no 
			WHERE a.jurnal_status=20 AND a.jurnal_type='PT' AND a.jurnal_date BETWEEN '$start_dateacct' AND '$end_dateacct' AND a.jurnal_menu='UMUM' AND b.jurnald_status=10
			GROUP BY a.jurnal_invno
			UNION ALL
			SELECT sa_idpemasok npwp, sa_date tanggal, sa_noref noreferensi, 'Saldo Awal' keterangan, 0 debit_idr, 0 debit_nidr,
			sa_price kredit_idr, sa_price_usd kredit_nidr
			FROM stock_acct	WHERE sa_status=30 AND sa_date BETWEEN '$start_dateacct' AND '$end_dateacct') a 
			WHERE a.npwp='$npwp' ORDER BY a.tanggal ASC");
		while($dd=mysql_fetch_array($qd)){	
			$py =mysql_fetch_array(mysql_query("SELECT SUM(a.jurnald_amount) payment FROM jurnal_detail a INNER JOIN jurnal b ON b.jurnal_no=a.jurnal_no 
				 WHERE a.jurnald_invno_acct='$dd[noreferensi]' AND a.jurnald_status<>100 AND b.jurnal_status=20 AND b.jurnal_type='AR'"));
			$py_usd =mysql_fetch_array(mysql_query("SELECT SUM(a.jurnald_amount_usd) payment FROM jurnal_detail a INNER JOIN jurnal b ON b.jurnal_no=a.jurnal_no 
				 WHERE a.jurnald_invno_acct='$dd[noreferensi]' AND a.jurnald_status<>100 AND b.jurnal_status=20 AND b.jurnal_type='AR'"));
			$sisa   = $dd['kredit_idr'] - $py['payment']; 
			$sisa_usd   = $dd['kredit_nidr'] - $py_usd['payment']; 
			if($sisa>0){
			$tnilai = $tnilai+$dd['kredit_idr'];
			$tpay   = $tpay+$py['payment'];
			$tsisa  = $tsisa+$sisa;	
			#usd
			$tnilai_usd = $tnilai_usd+$dd['kredit_nidr'];
			$tpay_usd   = $tpay_usd+$py_usd['payment'];
			$tsisa_usd  = $tsisa_usd+$sisa_usd;	
		?>
		 <tr>
			<td align="center"><?=$no;?>.</td>
			<td align="center"><?=$dd['tanggal'];?></td>
			<td align="center"><?=$dd['noreferensi'];?></td>
			<td align="left"><?=$dd['keterangan'];?></td>
			<td align="right"><?=number_format($sisa_usd,2,'.',',');?></td>
			<td align="right"><?=number_format($sisa,2,'.',',');?></td>			
		  </tr>	  
	  <?php $no++; } } ?> 
</table>