<?php 
  $ledger_id=$_REQUEST['acc_ledger'];
$get_financial_yr=DB_query("Select * from companies where CompanyId='".$_SESSION['ACGCompanyId']."'",$db);
$result=DB_fetch_array($get_financial_yr);
$fin_year_start=$result['AccountingYearStart'];
$fin_year_end=$result['AccountingYearEnd'];
$fin_year_start_brk=explode('-',$fin_year_start);
$year1=$fin_year_start_brk[0];
$fin_year_end_brk=explode('-',$fin_year_end);
$year2=$fin_year_end_brk[0];

$month_array=array(4=>'April',5=>'May',6=>'June',7=>'July',8=>'August',9=>'September',10=>'October',11=>'November',12=>'December',1=>'January',2=>'February',3=>'March',);
?>
<p align="right"><a href="javascript:history.go(-1)"><input type="button" name="" value="Back" /></a></p>
<h2 align="center"><strong>Monthly Transactions&nbsp;<?php echo $s->GetFieldValue('accountledger',"AccountsID   = '".$ledger_id."'" ,'AccountName');?></strong></h2>
<table width="100%" border="0" cellspacing="2" cellpadding="2" style="border: solid 1px #999999; background:#666666">
<tr>
<th>&nbsp;</th>
<th><strong>Debit</strong></th>
<th><strong>Credit</strong></th>
<th><strong>Closing</strong></th>
</tr>
<?php 
$array_CR=array();
$array_DR=array();
$array_CR_closing=array();
$array_DR_closing=array();
foreach($month_array as $key=>$val)
{
	if($key>=4 && $key<=12){$yr=$year1;}elseif($key>=1 && $key<=3){$yr=$year2;}
	if($key>=1 && $key<=9){$month="0".$key;}else{$month=$key;}
	
	$link="index.php?route=Account/AccountingReport&modid=".$_REQUEST['modid']."&frmid=".$_REQUEST['frmid']."&tabid=".$_REQUEST['tabid']."&page=".$_REQUEST['page']."&acc_ledger=".$_REQUEST['acc_ledger']."&m=".$month."&y=".$yr."";
	
	$voucher_CR_amt=0;
	$voucher_DR_amt=0;
	$invoice_DR_amt=0;
	$purch_CR_amt=0;
	$excise_CR=0;
	$excise_DR=0;
	$vat_cst_CR=0;
	$vat_cst_DR=0;
	$ledger_CR=0;
	$ledger_DR=0;
	if($_SESSION['APRfrom']!='' && $_SESSION['APRto']!='')
{
 	  $from_date=$_SESSION['APRfrom'];
	  $to_date=$_SESSION['APRto'];
		 $vouchers="SELECT A.Dates, B.* FROM account_book A, account_book_details B where A.id=B.account_bookid and B.ledgerid='".$ledger_id."' and MONTH(Dates)='".$month."' and YEAR(Dates)='".$yr."'  and  A.Dates between '".$from_date."' and '".$to_date."' group by B.account_bookid";

}else{
 	$from_date='';
	$to_date='';
	
		 $vouchers="SELECT A.Dates, B.* FROM account_book A, account_book_details B where A.id=B.account_bookid and B.ledgerid='".$ledger_id."' and MONTH(Dates)='".$month."' and YEAR(Dates)='".$yr."'  group by B.account_bookid";

 }

	//echo "<br>******************<br>";
		$vouchers_res=DB_query($vouchers,$db);
		while($vouchers_row=DB_fetch_array($vouchers_res))
		{
			if($vouchers_row['amount_type']=='CR'){$voucher_CR_amt=$voucher_CR_amt+$vouchers_row['amount'];}
			if($vouchers_row['amount_type']=='DR'){$voucher_DR_amt=$voucher_DR_amt+$vouchers_row['amount'];}
		}
	
		$party_id_sql=DB_query("Select taxid,vender_client,ledgertype from accountledger where AccountsID='".$ledger_id."'",$db);
		$party_id_row=DB_fetch_array($party_id_sql,$db);
		$party_id=$party_id_row['vender_client'];
		$ledgertype=$party_id_row['ledgertype'];
		$taxid=$party_id_row['taxid'];
		
		if($party_id!='' && $taxid=='')
		{
		if($ledgertype=='client')
		{
			//***************SALES*****************************//
			$invoice_sql="SELECT * FROM `invoice` where client_id='".$party_id."' and MONTH(invoiceDate)='".$month."' and YEAR(invoiceDate)='".$yr."'";
			$invoice_res=DB_query($invoice_sql,$db);
			while($invoice_row=DB_fetch_array($invoice_res))
			{
				$invoice_DR_amt=$invoice_DR_amt+$invoice_row['grandTotal'];
			}
		}
		elseif($ledgertype=='vendor')
		{
			//***************PURCHASE*****************************//
			$Vendor_id=$s->getField('suppliers','id',$party_id,'vendor_code');
			$purch_sql="SELECT * FROM `purchase_billbook` where vendor='".$Vendor_id."' and MONTH(bill_date)='".$month."' and YEAR(bill_date)='".$yr."'";
			$purch_res=DB_query($purch_sql,$db);
			while($purch_row=DB_fetch_array($purch_res))
			{
				$purch_CR_amt=$purch_CR_amt+$purch_row['grand_total'];
			}
		}
		}
		elseif($taxid!='' && $party_id=='')
		{
		if($ledgertype=='excisetax')
		{
		//echo "<br>"."Select SUM(exciseValue) as total_inv_excise from invoice where exciseDuty='".$taxid."' and MONTH(invoiceDate)='".$month."' and YEAR(invoiceDate)='".$yr."'";
		$invoice_excise_sql=DB_query("Select SUM(exciseValue) as total_inv_excise from invoice where exciseDuty='".$taxid."' and MONTH(invoiceDate)='".$month."' and YEAR(invoiceDate)='".$yr."'",$db);
		$invoice_excise_row=DB_fetch_array($invoice_excise_sql);
		$excise_CR=$excise_CR+$invoice_excise_row['total_inv_excise'];
			
		//echo "<br>"."Select SUM(Excise_Duty) as total_pur_excise from purchase_billbook where exciseDuty_id='".$taxid."' and MONTH(bill_date)='".$month."' and YEAR(bill_date)='".$yr."'";
		$purchase_excise_sql=DB_query("Select SUM(Excise_Duty) as total_pur_excise from purchase_billbook where exciseDuty_id='".$taxid."' and MONTH(bill_date)='".$month."' and YEAR(bill_date)='".$yr."'",$db);
		$purchase_excise_row=DB_fetch_array($purchase_excise_sql);
		$excise_DR=$excise_DR+$purchase_excise_row['total_pur_excise'];
		}
		elseif($ledgertype=='salestax')
		{
		//echo "<br>"."Select SUM(VatCstValue) as total_inv_vatsct from invoice where VatCst='".$taxid."' and MONTH(invoiceDate)='".$month."' and YEAR(invoiceDate)='".$yr."'";
		$invoice_vat_sql=DB_query("Select SUM(VatCstValue) as total_inv_vatsct from invoice where VatCst='".$taxid."' and MONTH(invoiceDate)='".$month."' and YEAR(invoiceDate)='".$yr."'",$db);
		$invoice_vat_row=DB_fetch_array($invoice_vat_sql);
		$vat_cst_CR=$vat_cst_CR+$invoice_vat_row['total_inv_vatsct'];
		
		//echo "<br>"."Select SUM(taxvalue) as total_pur_vatsct from purchase_billbook where taxname='".$taxid."' and MONTH(bill_date)='".$month."' and YEAR(bill_date)='".$yr."'";
		$purch_vat_sql=DB_query("Select SUM(taxvalue) as total_pur_vatsct from purchase_billbook where taxname='".$taxid."' and MONTH(bill_date)='".$month."' and YEAR(bill_date)='".$yr."'",$db);
		$purch_vat_row=DB_fetch_array($purch_vat_sql);
		$vat_cst_DR=$vat_cst_DR+$purch_vat_row['total_pur_vatsct'];
		}
	}
	else
	{
		//echo "<br>"."Select SUM(basicAmount) as total_inv_basic from invoice where ledger_id='".$ledger_id."' and MONTH(invoiceDate)='".$month."' and YEAR(invoiceDate)='".$yr."'";
		$ledger_inv_sql=DB_query("Select SUM(basicAmount) as total_inv_basic from invoice where ledger_id='".$ledger_id."' and MONTH(invoiceDate)='".$month."' and YEAR(invoiceDate)='".$yr."'",$db);
		$ledger_inv_row=DB_fetch_array($ledger_inv_sql);
		$ledger_CR=$ledger_CR+$ledger_inv_row['total_inv_basic'];
		
		//echo "<br>"."Select SUM(basic_amount) as total_pur_basic from purchase_billbook where ledger_list='".$ledger_id."' and MONTH(bill_date)='".$month."' and YEAR(bill_date)='".$yr."'";
		$ledger_pur_sql=DB_query("Select SUM(basic_amount) as total_pur_basic from purchase_billbook where ledger_list='".$ledger_id."' and MONTH(bill_date)='".$month."' and YEAR(bill_date)='".$yr."'",$db);
		$ledger_pur_row=DB_fetch_array($ledger_pur_sql);
		$ledger_DR=$ledger_DR+$ledger_pur_row['total_pur_basic'];
	}
		
	
?> 
  <tr bgcolor="#FFFFFF">
    <td><strong><?php echo $val; echo ", ".$yr;?></strong> </td>
    <td><strong><?php $total_DR=round($voucher_DR_amt+$invoice_DR_amt+$excise_DR+$vat_cst_DR+$ledger_DR); if($total_DR=='0'){echo "";}else{echo $total_DR;}$array_DR[]=$total_DR;?></strong></td>
    <td><strong><?php $total_CR=round($voucher_CR_amt+$purch_CR_amt+$excise_CR+$vat_cst_CR+$ledger_CR); if($total_CR=='0'){echo "";}else{echo $total_CR;}$array_CR[]=$total_CR;?></strong></td>
    <td><strong>
      <?php if($total_DR>$total_CR){echo $closing_DR=$total_DR-$total_CR." DR";}elseif($total_CR>$total_DR){echo $closing_CR=$total_CR-$total_DR." CR";}?>
    </strong></td>
  </tr>
  <?php } ?>
  <tr>
    <th><strong>Grand Total</strong></th>
    <th><strong><?php echo (array_sum($array_DR)!=0?array_sum($array_DR):'');//print_r($array_DR); ?></strong></th>
    <th><strong><?php echo(array_sum($array_CR)!=0?array_sum($array_CR):'') ;//print_r($array_CR);?></strong></th>
    <th><strong><?php 
	if(array_sum($array_DR)>array_sum($array_CR)){echo array_sum($array_DR)-array_sum($array_CR)." DR";}elseif(array_sum($array_CR)>array_sum($array_DR)){echo array_sum($array_CR)-array_sum($array_DR)." CR";}
	
	?></strong></th>
  </tr>
</table>