<?php
session_name('CAKEPHP'); session_start();
ob_start();
$PageSecurity = 1;
include('config.php');
include('includes/ConnectDB.inc');
include('includes/function_lib.php');
include('includes/LanguageSetup.php');
include('includes/DateFunctions.inc');
include('Account/fun-Trial.php');?>
<?php 
?>
<script type="text/javascript" language="javascript">
  function toggle_visibility(id) {
	
	 
       var e = document.getElementById(id);
	       e.style.display = 'none';
		       //alert(e.style.display);
			   window.print();
    }

</script>
<?php $Comp=DB_query("select CompanyName from companies",$db); 
	  $CompRow=DB_fetch_array($Comp);
?> 
<?php 
$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'];
$_SESSION['fin_year_start']=$fin_year_start;
$_SESSION['fin_year_end']=$fin_year_end;

if($_REQUEST['Show']!='')
{
	$from_date=$_REQUEST['from'];
	$to_date=$_REQUEST['to'];
	
	$from_date_brk=explode('/',$from_date);
	$from_day=$from_date_brk[0];
	$from_month=$from_date_brk[1];
	$from_year=$from_date_brk[2];
	$from_Sqlformat=$from_year."-".$from_month."-".$from_day;
	$_SESSION['from_Sqlformat']=$from_Sqlformat;
	
	$to_date_brk=explode('/',$to_date);
	$to_day=$to_date_brk[0];
	$to_month=$to_date_brk[1];
	$to_year=$to_date_brk[2];
	$to_Sqlformat=$to_year."-".$to_month."-".$to_day;
	$_SESSION['to_Sqlformat']=$to_Sqlformat;
	
	$inv_cond="and invoiceDate>='".$from_Sqlformat."' and invoiceDate<='".$to_Sqlformat."'";
	$pur_cond="and bill_date>='".$from_Sqlformat."' and bill_date<='".$to_Sqlformat."'";
	$voucher_cond="and A.Dates>='".$from_Sqlformat."' and A.Dates<='".$to_Sqlformat."'";
}
else
{
	$inv_cond="and invoiceDate>='".$fin_year_start."' and invoiceDate<='".$fin_year_end."'";
	$pur_cond="and bill_date>='".$fin_year_start."' and bill_date<='".$fin_year_end."'";
	$voucher_cond="and A.Dates>='".$fin_year_start."' and A.Dates<='".$fin_year_end."'";
}

?>
<?php 
	

	if($_SESSION['from_Sqlformat']!='' && $_SESSION['to_Sqlformat']!=''){
	$inv_cond="and invoiceDate>='".$_SESSION['from_Sqlformat']."' and invoiceDate<='".$_SESSION['to_Sqlformat']."'";
	$pur_cond="and bill_date>='".$_SESSION['from_Sqlformat']."' and bill_date<='".$_SESSION['to_Sqlformat']."'";
	 $voucher_cond="and A.Dates>='".$_SESSION['from_Sqlformat']."' and A.Dates<='".$_SESSION['to_Sqlformat']."'";}
		
	$account_group_id=$_REQUEST['acc_group'];
	  $group_name=$s->getField('accountgroupmaster','GroupId',$account_group_id,'GroupName');
	  $get_ledger="Select * from accountledger where UnderGroup='".$account_group_id."' order by AccountName ASC ";
	$get_ledger_res=DB_query($get_ledger,$db);
	
?>

<?php 
$header="<table width='100%' border='0' >
<tr>
<td colspan='2' style='text-align:center; font-size:26px; font-family:Arial, Helvetica, sans-serif'><center><strong>".$CompRow['CompanyName']."<br  />".$group_name."</strong></center></td>
</tr>
<tr>
<td>";
if(!empty($_REQUEST['from'])) 
{ 
	$header.="FROM DATE:".$_REQUEST['from'];  
}
$header.="</td>
<td style=text-align:right;>";

if(!empty($_REQUEST['to'])) 
{ 
	$header.="TO DATE:".$_REQUEST['to'];  
}

$header.="</td>
</tr>
</table>";

$header.="<table width='100%' border='0' cellspacing='2' cellpadding='2' style='border: solid 1px #999999; background:#CCCCCC '>
  <tr>
  	<th align='left'><strong>S.No</strong></th>
    <th align='left'><strong>Ledger</strong></th>
	<th align='left'><strong>Opening Balance </strong></th>
    <th align='left'><strong>Total Debit </strong></th>
    <th align='left'><strong>Total Credit </strong></th>
    <th align='left'><strong>Closing Balance </strong></th>
    <th align='left'><strong>DR/CR</strong></th>
  </tr>";
  ?>
  <?php 
  $opening_array=array();
  $DR_array=array();
  $CR_array=array();
  $closing_array=array();
  
  if($_REQUEST['acc_group']=='')
	{
		$cout=12;
	}
	else 
	{
		$cout=20;
	}
	
  $i = 0;
  
  echo $header;
  $counter = 0;
  
  while($get_ledger_row=DB_fetch_array($get_ledger_res))
  {
  	//print_r($get_ledger_row);
  	if($counter%$cout == '0')
	{
		if($counter!='0' ){
			if($_REQUEST['acc_group']=='')
			{
				echo "";
				echo $header; 
			}
			else 
			{
				echo "";
				echo $header; 
			} 
		}
	 }	
	 $counter++;
	
  	$link="index.php?route=Account/AccountingReport&modid=".$_REQUEST['modid']."&frmid=".$_REQUEST['frmid']."&tabid=".$_REQUEST['tabid']."&page=ledger_monthly_transaction&acc_ledger=".$get_ledger_row['AccountsID']."";
	 $ledger_id=$get_ledger_row['AccountsID'];
	
	//***************VOUCHERS ENTRY********************//
		$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;
		
		 $vouchers="SELECT A.Dates, B.* FROM account_book A, account_book_details B where A.id=B.account_bookid and B.ledgerid='".$ledger_id."' $voucher_cond group by B.account_bookid";
		$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'];}
		}
	//*************************************************//
	
	//***************CHECKING PARTY AND TAX LEDGER MAPPING WITH MASTERS*************//
		$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."' $inv_cond";
			$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."' $pur_cond";
			$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."' $inv_cond";
		$invoice_excise_sql=DB_query("Select SUM(exciseValue) as total_inv_excise from invoice where exciseDuty='".$taxid."' $inv_cond",$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."' $pur_cond";
		$purchase_excise_sql=DB_query("Select SUM(Excise_Duty) as total_pur_excise from purchase_billbook where exciseDuty_id='".$taxid."' $pur_cond",$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."' $inv_cond";
		$invoice_vat_sql=DB_query("Select SUM(VatCstValue) as total_inv_vatsct from invoice where VatCst='".$taxid."' $inv_cond",$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."' $pur_cond";
		$purch_vat_sql=DB_query("Select SUM(taxvalue) as total_pur_vatsct from purchase_billbook where taxname='".$taxid."' $pur_cond",$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."' $inv_cond";
		$ledger_inv_sql=DB_query("Select SUM(basicAmount) as total_inv_basic from invoice where ledger_id='".$ledger_id."' $inv_cond",$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."' $pur_cond";
		$ledger_pur_sql=DB_query("Select SUM(basic_amount) as total_pur_basic from purchase_billbook where ledger_list='".$ledger_id."' $pur_cond",$db);
		$ledger_pur_row=DB_fetch_array($ledger_pur_sql);
		$ledger_DR=$ledger_DR+$ledger_pur_row['total_pur_basic'];
	}
  ?>
<tr>
	<td><?php echo $i;?></td>
    <td><?php echo $get_ledger_row['AccountName'];?></td>
    <td><?php echo $get_ledger_row['openingBalance']."  ".$get_ledger_row['openingtype']; $opening_array[]=$get_ledger_row['openingBalance'];?></td>
	 <td><?php $grand_DR=round($voucher_DR_amt+$invoice_DR_amt+$excise_DR+$vat_cst_DR+$ledger_DR); if($grand_DR=='0'){echo " ";}else{echo $grand_DR;} $DR_array[]=$grand_DR;?></td>
    <td>
	<?php
	 $grand_CR=round($voucher_CR_amt+$purch_CR_amt+$excise_CR+$vat_cst_CR+$ledger_CR); 
	 if($grand_CR=='0')
	 {
	 echo " ";
	 }
	 else
	 {
	 echo $grand_CR;
	 } 
	 $CR_array[]=$grand_CR;?></td>
	<?php 
	if($grand_DR>$grand_CR){
		$closing=$grand_DR-$grand_CR; 
		$type="DR";
	}
	elseif($grand_CR>$grand_DR){
		$closing=$grand_CR-$grand_DR; $type="CR";
	}
	else{$closing='';$type='';}
	?>
	<td><?php if($closing=='0'){echo "";}else{echo $closing;} ?></td>
    <td><?php echo $type;?></td>
  </tr>
  <?php 
  $i++;
  }?>
  
  <tr>
  	<th>Total</th>
	<th></th>
    <th><?php echo array_sum($DR_array); ?></th>
    <th><?php echo array_sum($CR_array); ?></th>
    <th><?php 
	if(array_sum($DR_array)>array_sum($CR_array)){echo array_sum($DR_array)-array_sum($CR_array); $bal_type="DR";}
	elseif(array_sum($CR_array)>array_sum($DR_array)){echo array_sum($CR_array)-array_sum($DR_array); $bal_type="CR";}
	?></th>
    <th><?php echo $bal_type;?></th>
  </tr>
</table>

<div id="foo"><p align="center">
<a href="#" onclick="javascript:toggle_visibility('foo');" >Print</a>  
</div><!-- Close 3 Para -->
