<?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');

$gid=$_SESSION['gid'];
$from_date='';
$to_date='';
//include('includes/function_lib.php');
if(isset($_SESSION['FDATE']))
{
$from_date=$_SESSION['FDATE'];
}
if(isset($_SESSION['TDATE']))
{
$to_date=$_SESSION['TDATE'];
}

if($from_date!='' && $to_date!='')
{
$period=ConvertSQLDate($from_date).' to '.ConvertSQLDate($to_date);
}
else
{
$period=ConvertSQLDate($_SESSION['financialYrStart']).' to '.ConvertSQLDate($_SESSION['financialYrEnd']);
}











$data='';
$data.="<tr>";
$data.="<td colspan='5'><h3>".$s->getField('accountgroupmaster','GroupId',$gid,'GroupName')."</h3></td>";
$data.="</tr>";
$data.="<tr>";
$data.="<td colspan='5'><h2>".$period."</h2></td>";
$data.="</tr>";

$data.="<table width='100%' border='0' style='border:#999999 solid 1px'>";
$data.="<tr>";
$data.="<th width='70%' style='border-bottom: #666666 solid 2px;border-left: #FFFFFF solid 2px;border-top: #FFFFFF solid 2px;border-right: #666666 solid 2px;'>Particulars</th>";
$data.="<th width='10%' style='border-bottom: #666666 solid 2px;border-left: #FFFFFF solid 2px;border-top: #FFFFFF solid 2px;border-right: #666666 solid 2px;'>Opening</th>";
$data.="<th width='10%' style='border-bottom: #666666 solid 2px;border-left: #FFFFFF solid 2px;border-top: #FFFFFF solid 2px;border-right: #666666 solid 2px;'>Debit</th>";
$data.="<th width='10%' style='border-bottom: #666666 solid 2px;border-left: #FFFFFF solid 2px;border-top: #FFFFFF solid 2px;border-right: #666666 solid 2px;'>Credit</th>";
$data.="<th width='10%' style='border-bottom: #666666 solid 2px;border-left: #FFFFFF solid 2px;border-top: #FFFFFF solid 2px;border-right: #666666 solid 2px;'>Closing</th>";
$data.="</tr>";
$getSubGroups=DB_query("Select * from accountgroupmaster where MainGroupId=$gid",$db);
$openingCR_array=''; $openingDR_array='';
		$TDA='';
		$TCA='';
  while($subGroups=DB_fetch_array($getSubGroups))
  {
  		if($from_date!='' && $to_date!=''){
  		$res=getGroupBalanceSheet($subGroups['GroupId'],$from_date,$to_date);}
		else
		{$res=getGroupBalanceSheet($subGroups['GroupId'],'','');}
		//print_r($res);
		$TD=$res[0];
		$TC=$res[1];
$data.="<tr>";
$data.="<td style='padding-left:5px'><strong>".$subGroups['GroupName']."</strong></a></td>";
$data.="<td style='text-align:right; padding-right:5px'><strong>"; 
$DROPEN=$res[2];
$CROPEN=$res[3];
if($DROPEN>$CROPEN)
{
$data.=$TOTALOPDR=$DROPEN-$CROPEN; 
$data.="&nbsp;DR"; 
$openingDR_array[]=$TOTALOPDR; $TOTALOPCR='';}
elseif($CROPEN>$DROPEN)
{
$data.=$TOTALOPCR=$CROPEN-$DROPEN; 
$data.="&nbsp;CR"; 
$openingCR_array[]=$TOTALOPCR; 
$TOTALOPDR='';
}else
{
$TOTALOPCR=''; 
$TOTALOPDR='';
}
$data.="</strong></td>";
$data.="<td style='text-align:right; padding-right:5px'><strong>";
if($TD==0)
{
$data.="";
}
else 
$data.=$TD; 
$TDA[]=$TD;
$data.="</strong></td>";
$data.="<td style='text-align:right; padding-right:5px'><strong>";
if($TC==0)
{
$data.="";
}
else 
$data.=$TC; 
$TCA[]=$TC;
$data.="</strong></td>";
 $Gtotal_DR=$TOTALOPDR+$TD;
 $Gtotal_CR=$TOTALOPCR+$TC;
 if($Gtotal_DR>$Gtotal_CR){$closing=$Gtotal_DR-$Gtotal_CR; $type="DR";}
 elseif($Gtotal_CR>$Gtotal_DR){$closing=$Gtotal_CR-$Gtotal_DR; $type="CR";}else{$closing='';$type="";} 
$data.="<td style='text-align:right; padding-right:5px'><strong>".$closing.'&nbsp;'.$type."</strong></td>";
$data.="</tr>"; 
}
$data.="<tr height='5px'></tr>";
  $getLedgers=DB_query("Select * from accountledger where UnderGroup=$gid order by AccountName ASC",$db);
  		$etaxDetails='';
		$staxDetails='';
		$vendorDetails='';
		$clientDetails='';
		$etaxDR=''; 
		$etaxCR=''; 
		$staxDR='';
		$staxCR=''; 
		$purchCR=''; 
		$invDR='';
		$legderDR='';
		$legderCR='';
		$tot_op_DR='';
		$ARRDR='';
		$ARRCR='';
		$opDR='';
		$opCR='';

  while($Ledgers=DB_fetch_array($getLedgers))
  {
  	$ledger_id=$Ledgers['AccountsID'];
	
	if($from_date!='' && $to_date!='')
	{$opening_bal=$s->GetFieldValue('accountledger',"AccountsID=$ledger_id and openingdate<='".$to_date."'",'openingBalance');}
	else{$opening_bal=$s->getField('accountledger','AccountsID',$ledger_id,'openingBalance');}
	$opening_type=$s->getField('accountledger','AccountsID',$ledger_id,'openingtype');

  	if($from_date!='' && $to_date!='')
	{
	
	$totVoucherDR=Get_AccountByLedger($Ledgers['AccountsID'],'DR',$from_date,$to_date);
  	$totVoucherCR=Get_AccountByLedger($Ledgers['AccountsID'],'CR',$from_date,$to_date);}
	else{

	$totVoucherDR=Get_AccountByLedger($Ledgers['AccountsID'],'DR','','');
  	$totVoucherCR=Get_AccountByLedger($Ledgers['AccountsID'],'CR','','');}
	
	$master_party_id=$s->getField('accountledger','AccountsID',$ledger_id,'vender_client');	
	$master_tax_id=$s->getField('accountledger','AccountsID',$ledger_id,'taxid');	
	$ledgertype=$s->getField('accountledger','AccountsID',$ledger_id,'ledgertype');	
	if($ledgertype!=''){
	if($ledgertype=='excisetax'){
	if($from_date!='' && $to_date!='')
	{$etaxDetails=yearlyETaxAccountByTransactions($master_tax_id,$from_date,$to_date);}else
		{$etaxDetails=yearlyETaxAccountByTransactions($master_tax_id,'','');} $etaxDR=$etaxDetails[0]; $etaxCR=$etaxDetails[1];
		}else{$etaxDetails='';}
	if($ledgertype=='salestax')
	{
	if($from_date!='' && $to_date!='')
	{$staxDetails=yearlySTaxAccountByTransactions($master_tax_id,$from_date,$to_date);}else
		{$staxDetails=yearlySTaxAccountByTransactions($master_tax_id,'','');} $staxDR=$staxDetails[0]; $staxCR=$staxDetails[1];
	}else{$staxDetails='';}
	if($ledgertype=='vendor')
	{
		if($from_date!='' && $to_date!='')
		{$vendorDetails=yearlyVendorAccountByTransactions($master_party_id,$from_date,$to_date);}else
		{$vendorDetails=yearlyVendorAccountByTransactions($master_party_id,'','');} $purchCR=$vendorDetails;
	}else{$vendorDetails='';}
	if($ledgertype=='client')
	{
		if($from_date!='' && $to_date!='')
		{$clientDetails=yearlyClientAccountByTransactions($master_party_id,$from_date,$to_date);}
		else{$clientDetails=yearlyClientAccountByTransactions($master_party_id,'','');} $invDR=$clientDetails;
	}else{$clientDetails='';}}
	else
	{
		if($from_date!='' && $to_date!='')
		{
		$ledgerPosting=yearlyLedgerPostingByTransactions($ledger_id,$from_date,$to_date);
		}
		else
		{
		$ledgerPosting=yearlyLedgerPostingByTransactions($ledger_id,'','');
		} 
		$legderDR=$ledgerPosting[0]; $legderCR=$ledgerPosting[1];
		$etaxDetails='';
		$staxDetails='';
		$vendorDetails='';
		$clientDetails='';
		$etaxDR=''; $etaxCR=''; $staxDR='';$staxCR=''; $purchCR=''; $invDR='';
	}
$data.="<tr>";
$data.="<td style='padding-left:5px'><strong><font color='#000000'>".$Ledgers['AccountName']."</font></strong></td>";
$data.="<td style='text-align:right; padding-right:5px'><strong>";
if($opening_bal!='')
{
$data.=$opening_bal."&nbsp;".$opening_type; 
}
if($opening_type=='CR')
{
$openingCR_array[]=$opening_bal; $OPCR=$opening_bal; $OPDR='';
}
else
{
$openingDR_array[]=$opening_bal; 
$OPDR=$opening_bal;
 $OPCR='';
 }
$data.="</strong></td>";
$data.="<td style='text-align:right; padding-right:5px'><strong>";
$total_DR=$totVoucherDR+$etaxDR+$staxDR+$invDR+$legderDR; 
if($total_DR==0)
$data.= " "; 
else 
$data.=$total_DR; 
$ARRDR[]=$total_DR;
$data.="</strong></td>";
$data.="<td style='text-align:right; padding-right:5px'><strong>";
$total_CR=$totVoucherCR+$etaxCR+$staxCR+$purchCR+$legderCR; 
if($total_CR==0)
$data.= " "; 
else 
$data.=$total_CR; 
$ARRCR[]=$total_CR;
$data.="</strong></td>";
$Gtotal_DR1=$OPDR+$total_DR;
$Gtotal_CR1=$OPCR+$total_CR;
if($Gtotal_DR1>$Gtotal_CR1)
{
$closing=$Gtotal_DR1-$Gtotal_CR1; $type="DR"; 
}
elseif($Gtotal_CR1>$Gtotal_DR1)
{
$closing=$Gtotal_CR1-$Gtotal_DR1; $type="CR";
}
else
{
$closing='';$type="";
}
$data.="<td style='text-align:right; padding-right:5px'><strong>".$closing.'&nbsp;'.$type."</strong></td>";
$data.="</tr>"; 
} 
$data.="<tr>";
$data.="<th width='60%' style='border-bottom: #666666 solid 2px;border-left: #FFFFFF solid 2px;border-top: #FFFFFF solid 2px;border-right: #666666 solid 2px;'>Total</th>";
$data.="<th width='20%' style='border-bottom: #666666 solid 2px;border-left: #FFFFFF solid 2px;border-top: #FFFFFF solid 2px;border-right: #666666 solid 2px;'>";
if($openingDR_array!='')$opDR=array_sum($openingDR_array);
if($openingCR_array!='')$opCR=array_sum($openingCR_array);
if($opDR>$opCR)
{
$data.=$tot_op_DR=$opDR-$opCR; 
$data.="&nbsp;DR";
}
else
{
$data.=$tot_op_CR=$opCR-$opDR; 
$data.="&nbsp;CR";
}
$data.="</th>";
$data.="<th width='20%' style='border-bottom: #666666 solid 2px;border-left: #FFFFFF solid 2px;border-top: #FFFFFF solid 2px;border-right: #666666 solid 2px;'>";
if($TDA!='')
{
$xx=array_sum($TDA);
}
else
{
$xx=0;
}
if($ARRDR!='')
{
$yy=array_sum($ARRDR);
}
else
{
$yy=0;
}
$data.=$xx+$yy;
$data.="</th>";
$data.="<th width='20%' style='border-bottom: #666666 solid 2px;border-left: #FFFFFF solid 2px;border-top: #FFFFFF solid 2px;border-right: #666666 solid 2px;'>";
if($TCA!='')
{
$mm=array_sum($TCA);
}
else
{
$mm=0;
}
if($ARRCR!='')
{
$nn=array_sum($ARRCR);
}
else
{
$nn=0;
}
$data.=$mm+$nn;
$data.="</th>";
$FinalDR=$tot_op_DR+$xx+$yy;
$FinalCR=$tot_op_CR+$mm+$nn;
if($FinalDR>$FinalCR)
{$closing=$FinalDR-$FinalCR; 
$type="DR"; 
}
elseif($FinalCR>$FinalDR)
{
$closing=$FinalCR-$FinalDR; 
$type="CR";
}
else
{
$closing='';
$type="";
}
$data.="<th width='20%' style='border-bottom: #666666 solid 2px;border-left: #FFFFFF solid 2px;border-top: #FFFFFF solid 2px;border-right: #666666 solid 2px;'>".$closing.'&nbsp;'.$type."</th>";
$data.="</tr>";
$data.="</table>";
//echo $data;
$header='';  	
header("Content-type: application/x-msdownload"); 
header("Content-Disposition: attachment; filename=APRGroupWise-BalanceSheet-Liabilities_Exl.xls"); 
header("Pragma: no-cache"); 
header("Expires: 0"); 
print "$header\n$data"; 