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

$fromdate   = $_REQUEST['fromdate'];
$todate     = $_REQUEST['todate'];
$j_acctno   = $_REQUEST['j_acctno']; 
if($j_acctno != "BLANK"){
	$searchh    = "AND j_acctno='$j_acctno'";
}else{
	$searchh    = "";
}

$sqlShow 	= mysql_query("SELECT prof_name FROM profil");
$dataShow 	= mysql_fetch_array($sqlShow);

?>
<table width="100%" border="0">
    <tr>
  		<td colspan="13">BUKU BESAR</td>
    </tr>
    <tr>
  		<td colspan="13"><?=strtoupper($dataShow['prof_name']);?></td>
    </tr>
    <tr>
  		<td colspan="13">PERIODE : <?=$fromdate;?>S/D<?=$todate;?></td>
    </tr>
    <tr>
  		<td colspan="13">&nbsp;</td>
    </tr>
</table>	
<table width="100%" border="1">	
	<tr height="25px;">
	<th rowspan="2">No</th>
	<th rowspan="2">Tanggal</th>
	<th rowspan="2">No.Jurnal</th>
	<th rowspan="2">Keterangan</th>
	<th rowspan="2">No Faktur</th>
	<th rowspan="2">No Invoice</th>
	<th rowspan="2">Sup./Cust.</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
  	$qcoa = mysql_query("SELECT j_acctno, CONCAT_WS(' - ', j_acctno, j_name1) codename, j_rumusbb FROM jurnal_master WHERE j_status=10 $searchh ORDER BY j_acctno ASC");
	while($dcoa=mysql_fetch_array($qcoa)){
	?>
	<tr>
    	<td colspan="12"><strong>ACCOUNT : <?=$dcoa['codename'];?></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=10 AND sa_acctno='$dcoa[j_acctno]'
			ORDER BY id DESC LIMIT 0,1"));
		?> 
		<tr>
			<td>&nbsp;</td>
			<td colspan="9">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 =$sa['sa_price']; $t_amountin=0; $t_amountout=0; $saldo_usd =$sa['sa_price_usd']; $t_amountin_usd=0; $t_amountout_usd=0;
	$qd=mysql_query("SELECT a.tanggal, a.jurnal_no, a.keterangan, a.jurnal_nofaktur, a.custsup_name, a.amountin, a.amountin_usd, a.amountout, a.amountout_usd, a.flag, a.jurnal_invno FROM
			(SELECT a.id, a.jurnal_date tanggal, a.jurnal_no, b.jurnald_desc keterangan, a.jurnal_nofaktur, 
			CASE WHEN a.jurnal_custname<>'' THEN a.jurnal_custname ELSE a.jurnal_suppname END AS custsup_name, b.jurnald_amount amountin, b.jurnald_amount_usd amountin_usd, 
			0 amountout, 0 amountout_usd, 1 flag, a.jurnal_invno FROM jurnal a INNER JOIN jurnal_detail b ON b.jurnal_no=a.jurnal_no
			WHERE a.jurnal_status=20 AND a.jurnal_date BETWEEN '$fromdate' AND '$todate' AND jurnald_acctno='$dcoa[j_acctno]' AND b.jurnald_status=10		
			UNION ALL
			SELECT a.id, a.jurnal_date tanggal, a.jurnal_no, b.jurnald_desc keterangan, a.jurnal_nofaktur, 
			CASE WHEN a.jurnal_custname<>'' THEN a.jurnal_custname ELSE a.jurnal_suppname END AS custsup_name, 0 amountin, 0 amountin_usd,
			b.jurnald_amount amountout, b.jurnald_amount_usd amountout_usd, 2 flag, a.jurnal_invno FROM jurnal a INNER JOIN jurnal_detail b ON b.jurnal_no=a.jurnal_no
			WHERE a.jurnal_status=20 AND a.jurnal_date BETWEEN '$fromdate' AND '$todate' AND jurnald_acctno='$dcoa[j_acctno]' AND b.jurnald_status=20) a
			ORDER BY a.tanggal ASC");
	$num = mysql_num_rows($qd);		
	if($num==0){
		$t_saldo_usd = $saldo_usd;
		$t_saldo     = $saldo;
	}else{		
	while($dd=mysql_fetch_array($qd)){	
		if($dcoa['j_rumusbb']=='A'){
			$saldo_usd 	= $saldo_usd+$dd['amountin_usd']-$dd['amountout_usd'];
			$saldo 		= $saldo+$dd['amountin']-$dd['amountout'];
		}else{
			$saldo_usd 	= $saldo_usd-$dd['amountin_usd']+$dd['amountout_usd'];
			$saldo 		= $saldo-$dd['amountin']+$dd['amountout'];
		}
		$t_saldo_usd = $saldo_usd;
		$t_saldo	 = $saldo;
		$t_amountin_usd  = $t_amountin_usd+$dd['amountin_usd'];
		$t_amountin  	 = $t_amountin+$dd['amountin'];
		$t_amountout_usd = $t_amountout_usd+$dd['amountout_usd'];
		$t_amountout 	 = $t_amountout+$dd['amountout'];
	?>
	 <tr onmouseover="setTrColor(this, '#FF9900');">
		<td align="center"><?=$no;?>.</td>
		<td align="left"><?=$dd['tanggal'];?></td>
		<td align="left"><?=$dd['jurnal_no'];?></td>
        <td align="left"><?=$dd['keterangan'];?></td>
		<td align="left"><?=$dd['jurnal_nofaktur'];?></td>
		<td align="left"><?=$dd['jurnal_invno'];?></td>
		<td align="left"><?=$dd['custsup_name'];?></td>
        <td align="right"><?=number_format($dd['amountin_usd'],2,'.','');?></td>
		<td align="right"><?=number_format($dd['amountin'],2,'.','');?></td>
        <td align="right"><?=number_format($dd['amountout_usd'],2,'.','');?></td>
		<td align="right"><?=number_format($dd['amountout'],2,'.','');?></td>
        <td align="right"><?=number_format($saldo_usd,2,'.','');?></td>
		<td align="right"><?=number_format($saldo,2,'.','');?></td>
	  </tr>	  
  <?php $no++; } } ?> 
  	  <tr>
      	<td>&nbsp;</td>
        <td colspan="6"><b>Total</b></td>
        <td align="right"><b><?=number_format($t_amountin_usd,2,'.','');?></b></td>
		<td align="right"><b><?=number_format($t_amountin,2,'.','');?></b></td>
        <td align="right"><b><?=number_format($t_amountout_usd,2,'.','');?></b></td>
		<td align="right"><b><?=number_format($t_amountout,2,'.','');?></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="#CCFFFF">
	  	<td colspan="13">&nbsp;</td>
	  </tr>
  <?php } ?>  
</table>