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

$fromdate   = $_REQUEST['fromdate'];
$todate     = $_REQUEST['todate'];
$npwp	 	= isset($_REQUEST['npwp']) ? $_REQUEST['npwp'] : '';
	 if($npwp=="BLANK"){
		$search    = "";
	 }else{
	 	$search    = "npwp='$npwp' AND";
	 }
	 $fromdate	 = $_REQUEST['fromdate'];
	 $todate     = $_REQUEST['todate'];
$sqlShow 	= mysql_query("SELECT prof_name FROM profil");
$dataShow 	= mysql_fetch_array($sqlShow);
?>
<table width="100%" border="0">
    <tr>
  		<td colspan="11">LAPORAN BUKU BESAR PIUTANG</td>
    </tr>
    <tr>
  		<td colspan="11">KAWASAN BERIKAT <?=strtoupper($dataShow['prof_name']);?></td>
    </tr>
    <!--<tr>
  		<td colspan="11">PERIODE : <?=$fromdate;?>s/d<?=$todate;?></td>
    </tr>-->
    <tr>
  		<td colspan="11">&nbsp;</td>
    </tr>
</table>	
<table width="100%" border="1">	
  <tr>
	<th rowspan="2">No</th>
	<th rowspan="2">Tanggal</th>
	<th rowspan="2">No.Invoice/Referensi</th>
	<th rowspan="2">Keterangan</th>
	<th colspan="2">Debit</th>
	<th colspan="2">Kredit</th>
    <th colspan="2">Saldo</th>
  </tr>
   <tr>
	<th>Non IDR</th>
	<th>IDR</th>
	<th>Non IDR</th>
	<th>IDR</th>
	<th>Non IDR</th>
	<th>IDR</th>
  </tr>
  <?php
  	$gt_saldo_usd=0; $gt_saldo=0; 
	$qh=mysql_query("SELECT a.npwp, a.nama FROM
					(SELECT sup_npwp npwp, sup_name nama FROM supplier WHERE sup_status=10
					UNION ALL
					SELECT cust_npwp npwp, cust_name nama FROM customer WHERE cust_status=10) a
					WHERE $search a.npwp is not null GROUP BY a.npwp ORDER BY a.nama ASC");
	while($dh=mysql_fetch_array($qh)){
	?> 
    <tr bgcolor="#FFFFCC">
        <td colspan="11"><strong>SUPPLIER/CUSTOMER : <?=$dh['nama'];?></strong></td>
    </tr>
	<?php
		$sa=mysql_fetch_array(mysql_query("SELECT id, sa_price, sa_price_usd FROM stock_acct WHERE sa_date='$fromdate' AND sa_status=30 AND sa_idpemasok='$dh[npwp]'"));
		?> 
		<tr>
			<td>&nbsp;</td>
			<td colspan="8">Saldo Awal</td>
			<td align="right"><?=number_format($sa['sa_price_usd'],2,'.','');?></td>
			<td align="right"><?=number_format($sa['sa_price'],2,'.','');?></td>
		</tr>
    <?php
	$no=1; $saldo_usd = $sa['sa_price_usd']; $saldo = $sa['sa_price']; $t_amountin_usd=0; $t_amountin=0; $t_amountout_usd=0; $t_amountout=0;
	$qd=mysql_query("SELECT a.* FROM (
				SELECT a.id, a.jurnal_no, a.jurnal_idpemasok npwp, a.jurnal_date tanggal, a.jurnal_invno noreferensi, a.jurnal_desc keterangan, b.jurnald_acctno, 0 tampil, b.jurnald_amount debit_idr, 
				b.jurnald_amount_usd debit_nidr, 0 kredit_idr, 0 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 '$fromdate' AND '$todate' AND b.jurnald_status=10 AND a.jurnal_menu<>'UMUM'				
				UNION ALL
				SELECT a.id, a.jurnal_no, a.jurnal_idpemasok npwp, a.jurnal_date tanggal, b.jurnald_invno_acct noreferensi, b.jurnald_desc keterangan, b.jurnald_acctno, 1 tampil, b.jurnald_amount debit_idr, 
				b.jurnald_amount_usd debit_nidr, 0 kredit_idr, 0 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 IN('PT') AND a.jurnal_date BETWEEN '$fromdate' AND '$todate' AND b.jurnald_status=10 AND a.jurnal_menu IN('UMUM')
				UNION ALL
				SELECT a.id, a.jurnal_no, a.jurnal_idpemasok npwp, a.jurnal_date tanggal, b.jurnald_invno_acct noreferensi, b.jurnald_desc keterangan, b.jurnald_acctno, 1 tampil, 0 debit_idr, 
				0 debit_nidr, b.jurnald_amount kredit_idr, b.jurnald_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 IN('AR') AND a.jurnal_date BETWEEN '$fromdate' AND '$todate' AND b.jurnald_status=20 AND a.jurnal_menu IN('BCALL','UMUM')) a 
				WHERE a.npwp='$dh[npwp]' ORDER BY a.id ASC");
	$num = mysql_num_rows($qd);		
	if($num==0){
		$t_saldo_usd=$saldo_usd;
		$t_saldo=$saldo;
	}else{		
	while($dd=mysql_fetch_array($qd)){	
		# cek settingan master GL
		$cekhtpt = mysql_fetch_array(mysql_query("SELECT j_tampilhtpt FROM jurnal_master WHERE j_acctno='$dd[jurnald_acctno]'"));
		if($cekhtpt['j_tampilhtpt']=="Y"){
		#Non IDR
		$saldo_usd = $saldo_usd - $dd['debit_nidr'] + $dd['kredit_nidr'];
		$t_saldo_usd=$saldo_usd;
		$t_amountin_usd=$t_amountin_usd+$dd['debit_nidr'];
		$t_amountout_usd=$t_amountout_usd+$dd['kredit_nidr'];
		#IDR
		$saldo = $saldo - $dd['debit_idr'] + $dd['kredit_idr'];
		$t_saldo=$saldo;
		$t_amountin=$t_amountin+$dd['debit_idr'];
		$t_amountout=$t_amountout+$dd['kredit_idr'];
	?>
	 <tr onmouseover="setTrColor(this, '#FF9900');">
		<td align="center"><?=$no;?>.</td>
		<td align="center"><?=$dd['jurnal_no'];?></td>
		<td align="left"><?=$dd['tanggal'];?></td>
		<td align="left"><?=$dd['noreferensi'];?></td>
        <td align="left"><?=$dd['keterangan'];?></td>
        <td align="right"><?=number_format($dd['debit_nidr'],2,'.','');?></td>
		<td align="right"><?=number_format($dd['debit_idr'],2,'.','');?></td>
        <td align="right"><?=number_format($dd['kredit_nidr'],2,'.','');?></td>
		<td align="right"><?=number_format($dd['kredit_idr'],2,'.','');?></td>
        <td align="right"><?=number_format($t_saldo_usd,2,'.','');?></td>
		<td align="right"><?=number_format($t_saldo,2,'.','');?></td>
	  </tr>	  
	<?php $no++; } } }?> 
  	  <tr>
      	<td>&nbsp;</td>
        <td colspan="8"><b>TOTAL</b></td>
        <td align="right"><b><?=number_format($t_saldo_usd,2,'.','');?></b></td>
		<td align="right"><b><?=number_format($t_saldo,2,'.','');?></b></td>
      </tr>
	  <tr bgcolor="#CCFFCC">
	  	<td colspan="11">&nbsp;</td>
	  </tr>
  <?php $gt_saldo_usd=$gt_saldo_usd+$t_saldo_usd;
  		$gt_saldo=$gt_saldo+$t_saldo;
  } ?> 	 
  	   <tr bgcolor="#CCFF00">
      	<td>&nbsp;</td>
        <td colspan="8"><b>GRAND TOTAL</b></td>
        <td align="right"><b><?=number_format($gt_saldo_usd,2,'.','');?></b></td>
		<td align="right"><b><?=number_format($gt_saldo,2,'.','');?></b></td>
      </tr> 
</table>