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

$GetLedger=$_SESSION['qry'];
$from_date='';
$to_date='';
if($_SESSION['from_date'])
{
$from_date=FormatDateForSQL($_SESSION['from_date']);
}
if($_SESSION['to_date'])
{
$to_date=FormatDateForSQL($_SESSION['to_date']);
}


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




$data='';

$data.="<table width='100%' border='0' cellpadding='3'  cellspacing='3' style='border:#999999 solid 1px;'>";
$data.="<tr>";
$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;'>Ledger Name </th>";
$data.="<th width='11%' style='border-bottom: #666666 solid 2px;border-left: #FFFFFF solid 2px;border-top: #FFFFFF solid 2px;border-right: #666666 solid 2px;'>Account Type </th>";
$data.="<th width='11%' style='border-bottom: #666666 solid 2px;border-left: #FFFFFF solid 2px;border-top: #FFFFFF solid 2px;border-right: #666666 solid 2px;'> Opening  (DR) </th>";
$data.="<th width='11%' style='border-bottom: #666666 solid 2px;border-left: #FFFFFF solid 2px;border-top: #FFFFFF solid 2px;border-right: #666666 solid 2px;'>Opening  (CR)</th>";
$data.="<th width='11%' style='border-bottom: #666666 solid 2px;border-left: #FFFFFF solid 2px;border-top: #FFFFFF solid 2px;border-right: #666666 solid 2px;'>Total Debit </th>";
$data.="<th width='11%' style='border-bottom: #666666 solid 2px;border-left: #FFFFFF solid 2px;border-top: #FFFFFF solid 2px;border-right: #666666 solid 2px;'>Total Credit </th>";
$data.="<th width='11%' style='border-bottom: #666666 solid 2px;border-left: #FFFFFF solid 2px;border-top: #FFFFFF solid 2px;border-right: #666666 solid 2px;'>Closing (DR) </th>";
$data.="<th width='11%' style='border-bottom: #666666 solid 2px;border-left: #FFFFFF solid 2px;border-top: #FFFFFF solid 2px;border-right: #666666 solid 2px;'>Closing (CR) </th>";
$data.="</tr>";  
  	 //$GetLedger=DB_query("select * from accountledger where 1=1  ",$db);
  	// echo "select * from accountledger where openingBalance!=''   order by AccountName ASC";
	 $GetLedger=DB_query($GetLedger,$db);
 	//echo DB_num_rows($GetLedger);
	
	
	if(DB_num_rows($GetLedger)>0)
	{
	$add_opening=array();
	$add_TD=array();
	$add_TC=array();
	$add_CL=array();
	$closing_DR='';
	$closing_CR='';
	 while($RowGetLedger=DB_fetch_array($GetLedger))
	 {
	 
	 	$etaxDetails='';
		$staxDetails='';
		$vendorDetails='';
		$clientDetails='';
		$roundDetails='';
		$etaxDR=''; $etaxCR=''; $staxDR='';$staxCR=''; $purchCR=''; $invDR='';$ledgerPosting='';$legderDR=''; $legderCR='';
		$cessDetails='';$cessDR='';$roundDR=''; $roundCR='';
	 
	 	
	 	$master_party_id=$RowGetLedger['vender_client'];	
		$master_tax_id=$RowGetLedger['taxid'];	
		$ledgertype=$RowGetLedger['ledgertype'];	
	 
	 
	 if($_REQUEST['from_date']!='' && $_REQUEST['to_date']!='')
	 {
	 	   $TDR= Get_AccountByLedger($RowGetLedger['AccountsID'],'DR',$from_date,$to_date);
	  	   $TCR= Get_AccountByLedger($RowGetLedger['AccountsID'],'CR',$from_date,$to_date);
			//echo "++++".$TDR."<br />";
		if($ledgertype!='')
	   {
		if($ledgertype=='excisetax'){
			$etaxDetails=yearlyETaxAccountByTransactions($master_tax_id,$from_date,$to_date); $etaxDR=$etaxDetails[0]; $etaxCR=$etaxDetails[1];
			}else{$etaxDetails='';}
		if($ledgertype=='salestax')
		{
			$staxDetails=yearlySTaxAccountByTransactions($master_tax_id,$from_date,$to_date); $staxDR=$staxDetails[0]; $staxCR=$staxDetails[1];
		}else{$staxDetails='';}
		if($ledgertype=='vendor')
		{
			$vendorDetails=yearlyVendorAccountByTransactions($master_party_id,$from_date,$to_date); $purchCR=$vendorDetails;
		}else{$vendorDetails='';$purchCR='';}
		if($ledgertype=='client')
		{
			$clientDetails=yearlyClientAccountByTransactions($master_party_id,$from_date,$to_date); $invDR=$clientDetails;
		}else{$clientDetails='';}
		if($ledgertype=='freight')
		{
			$freightDetails=yearlyFreightAccountByTransactions($RowGetLedger['AccountsID'],$from_date,$to_date);
			$freightDR=$freightDetails;
		}else{$freightDetails='';$freightDR='';}
		if($ledgertype=='surcharge')
		{
			$surDetails=yearlySurchargeAccountByTransactions($RowGetLedger['AccountsID'],$from_date,$to_date);
			$surDR=$surDetails;
		}else{$surDetails='';$surDR='';}
		if($ledgertype=='pckcharge')
		{
			$packDetails=yearlyPkgChargesAccountByTransactions($RowGetLedger['AccountsID'],$from_date,$to_date);
			$packDR=$packDetails;
		}else{$packDetails='';$packDR='';}
		if($ledgertype=='addcharge')
		{
			$addDetails=yearlyAddChargesAccountByTransactions($RowGetLedger['AccountsID'],$from_date,$to_date);
			$addDR=$addDetails;
		}else{$addDetails='';$addDR='';}
		if($ledgertype=='cess')		
		{
			$cessDetails=yearlyCessAccountByTransactions($master_tax_id,$from_date,$to_date);
			$cessDR=$cessDetails;
		}else{$cessDetails='';$cessDR='';}
		if($ledgertype=='round')
		{
			$roundDetails=yearlyRoundOffByTransactions($RowGetLedger['AccountsID'],$from_date,$to_date);
			$roundDR=$roundDetails[0];
			$roundCR=$roundDetails[1];
		}
		else{$roundDetails=''; $roundDR=''; $roundCR='';}
		
	   }
	   else
	   {
		$ledgerPosting=yearlyLedgerPostingByTransactions($RowGetLedger['AccountsID'],$from_date,$to_date); $legderDR=$ledgerPosting[0]; $legderCR=$ledgerPosting[1];
		$etaxDetails='';
		$staxDetails='';
		$vendorDetails='';
		$clientDetails='';
		$roundDetails='';
		$etaxDR=''; $etaxCR=''; $staxDR='';$staxCR=''; $purchCR=''; $invDR='';$freightDR='';$surDR='';$packDR=''; $addDR='';				
		$cessDetails='';$cessDR='';$roundDR=''; $roundCR='';
	}
	
	
	 }
	 else
	 {
	   $TDR= Get_AccountByLedger($RowGetLedger['AccountsID'],'DR','','');
	   $TCR= Get_AccountByLedger($RowGetLedger['AccountsID'],'CR','','');
	   
	   if($ledgertype!='')
	   {
		if($ledgertype=='excisetax'){
			$etaxDetails=yearlyETaxAccountByTransactions($master_tax_id,'',''); $etaxDR=$etaxDetails[0]; $etaxCR=$etaxDetails[1];
			}else{$etaxDetails='';}

		if($ledgertype=='salestax')
		{
			$staxDetails=yearlySTaxAccountByTransactions($master_tax_id,'',''); $staxDR=$staxDetails[0]; $staxCR=$staxDetails[1];
		}else{$staxDetails='';}
		if($ledgertype=='vendor')
		{
			$vendorDetails=yearlyVendorAccountByTransactions($master_party_id,'',''); $purchCR=$vendorDetails;
		}else{$vendorDetails='';}
		if($ledgertype=='client')
		{
			$clientDetails=yearlyClientAccountByTransactions($master_party_id,'',''); $invDR=$clientDetails;
		}else{$clientDetails='';$invDR='';}
	  	if($ledgertype=='freight')
		{
			$freightDetails=yearlyFreightAccountByTransactions($RowGetLedger['AccountsID'],'',''); 
			$freightDR=$freightDetails;
		}else{$freightDetails='';$freightDR='';}			
		if($ledgertype=='surcharge')
		{
			$surDetails=yearlySurchargeAccountByTransactions($RowGetLedger['AccountsID'],'','');
			$surDR=$surDetails;
		}else{$surDetails='';$surDR='';}			
		if($ledgertype=='pckcharge')
		{
			$packDetails=yearlyPkgChargesAccountByTransactions($RowGetLedger['AccountsID'],'','');
			$packDR=$packDetails;
		}else{$packDetails='';$packDR='';}
		if($ledgertype=='addcharge')
		{
			$addDetails=yearlyAddChargesAccountByTransactions($RowGetLedger['AccountsID'],'','');
			$addDR=$addDetails;
		}else{$addDetails='';$addDR='';}
		if($ledgertype=='cess')		
		{
			$cessDetails=yearlyCessAccountByTransactions($master_tax_id,'','');
			$cessDR=$cessDetails;
		}else{$cessDetails='';$cessDR='';}
		if($ledgertype=='round')
		{
			$roundDetails=yearlyRoundOffByTransactions($RowGetLedger['AccountsID'],'','');
			$roundDR=$roundDetails[0];
			$roundCR=$roundDetails[1];
		}
		else{$roundDetails=''; $roundDR=''; $roundCR='';}
					
		$ledgerPosting='';$legderDR=''; $legderCR='';
	   }
	   else
	   {
		$ledgerPosting=yearlyLedgerPostingByTransactions($RowGetLedger['AccountsID'],'',''); $legderDR=$ledgerPosting[0]; $legderCR=$ledgerPosting[1];
		$etaxDetails='';
		$staxDetails='';
		$vendorDetails='';
		$clientDetails='';
		$roundDetails='';
		$etaxDR=''; $etaxCR=''; $staxDR='';$staxCR=''; $purchCR=''; $invDR='';$freightDR='';$surDR='';$packDR=''; $addDR='';
		$cessDetails='';$cessDR=''; $roundDR=''; $roundCR='';
	}
	 }
	 $total_DR=$TDR+$etaxDR+$staxDR+$invDR+$legderDR+$freightDR+$surDR+$packDR+$addDR+$cessDR+$roundDR;
	 $total_CR=$TCR+$etaxCR+$staxCR+$purchCR+$legderCR+$roundCR;
	 
	if($RowGetLedger['openingtype']=='DR')
	{$openingDR=$RowGetLedger['openingBalance']; $openingCR='';}
	elseif($RowGetLedger['openingtype']=='CR')
	{$openingCR=$RowGetLedger['openingBalance']; $openingDR='';}
	else{$openingCR='';$openingDR='';}
		//echo "<br /><br />DR1---".$openingDR;
		//echo "<br /><br />CR1---".$openingCR;
	if($_REQUEST['from_date']!='' && $_REQUEST['to_date']!='')
	{
		$prevdate=date('Y-m-d', strtotime('-1 day', strtotime(FormatDateForSQL($_REQUEST['from_date']))));
		$currentMonthOpeningDR=monthlyLedgerAccount($RowGetLedger['AccountsID'],'','','DR','',$prevdate);
		$currentMonthOpeningCR=monthlyLedgerAccount($RowGetLedger['AccountsID'],'','','CR','',$prevdate);
		
		$res=monthlyLedgerOpenings($RowGetLedger['AccountsID'],'','','',$prevdate);
		//print_r($res);echo "<br />-----<br />";
		$TotalDROpening=$currentMonthOpeningDR+$openingDR;
		$TotalCROpening=$currentMonthOpeningCR+$openingCR;
		//echo "<br /><br />DR---".$currentMonthOpeningDR;
		//echo "<br /><br />CR---".$currentMonthOpeningCR;
		if($TotalDROpening>$TotalCROpening){$OPDR=$TotalDROpening-$TotalCROpening; $OPCR='';}
		elseif($TotalCROpening>$TotalDROpening){$OPCR=$TotalCROpening-$TotalDROpening; $OPDR='';}
		else{$OPCR='';$OPDR='';}
	}
	else
	{$OPDR=$openingDR; $OPCR=$openingCR;}
	 
	   if($total_DR!='' || $total_CR!='' || $OPDR!='' || $OPCR!='')
	   {
$data.="<tr bgcolor='#FFFFFF'>";
$data.="<td style='padding:5px'>".$RowGetLedger['AccountName']."</td>";
$data.="<td style='padding:5px'>".GetAccountGroupmaster($RowGetLedger['UnderGroup'])."</td>";
$data.="<td style='padding:5px; text-align:right'>";
 //echo $RowGetLedger['openingtype']."<br/ >";
if($OPDR!='' && $OPDR!='0.00')
{
$data.=$OPDR;
}
else
{
$data.="";
}
$add_OPDR[]=$OPDR;
$data.="&nbsp;</td>";
$data.="<td style='padding:5px; text-align:right'>";
if($OPCR!='' && $OPCR!='0.00')
{
$data.=$OPCR;
}
else
{
$data.="";
}
$add_OPCR[]=$OPCR;
$data.="&nbsp;</td>";
$data.="<td style='padding:5px; text-align:right'>";
if($total_DR!='')
$add_TD[]=$total_DR;
$data.=$total_DR ;
$data.="&nbsp;</td>";
$data.="<td style='padding:5px; text-align:right'>";
if($total_CR!='')
$add_TC[]=$total_CR;
$data.=$total_CR ;
$data.="&nbsp;</td>";
	$Gtotal_DR=$OPDR+$total_DR;
	$Gtotal_CR=$OPCR+$total_CR;
	if($Gtotal_DR>$Gtotal_CR)
	{$closing_DR=$Gtotal_DR-$Gtotal_CR; $closing_CR='';}
	elseif($Gtotal_CR>$Gtotal_DR)
	{$closing_CR=$Gtotal_CR-$Gtotal_DR; $closing_DR='';}
	else{$closing_CR='0'; $closing_DR='0';}
$data.="<td style='padding:5px; text-align:right'>";
if($closing_DR!='')
{
$data.=$closing_DR;
}
else
{
$data.="";
} 
$add_closeDR[]=$closing_DR;
$data.="</td>";
$data.="<td style='padding:5px; text-align:right'>";
if($closing_CR!='')
{
$data.=$closing_CR;
}
else
{
$data.="";
} 
$add_closeCR[]=$closing_CR;
$data.="</td>";
$data.="</tr>";
  }
  } 
$data.="<tr>";
$data.="<td style='border-bottom: #666666 solid 2px;border-left: #FFFFFF solid 2px;border-top: #FFFFFF solid 2px;border-right: #666666 solid 2px;'><strong><font size='5px'>Total</font></strong></td>";
$data.="<td style='border-bottom: #666666 solid 2px;border-left: #FFFFFF solid 2px;border-top: #FFFFFF solid 2px;border-right: #666666 solid 2px;'>&nbsp;</td>";
$data.="<td style='border-bottom: #666666 solid 2px;border-left: #FFFFFF solid 2px;border-top: #FFFFFF solid 2px;border-right: #666666 solid 2px; text-align:right'><strong>";
if($add_OPDR!='')
$data.=array_sum($add_OPDR);
$data.="</strong></td>";
$data.="<td style='border-bottom: #666666 solid 2px;border-left: #FFFFFF solid 2px;border-top: #FFFFFF solid 2px;border-right: #666666 solid 2px; text-align:right'><strong>";
if($add_OPCR!='')
$data.=array_sum($add_OPCR);
$data.="</strong></td>";
$data.="<td style='border-bottom: #666666 solid 2px;border-left: #FFFFFF solid 2px;border-top: #FFFFFF solid 2px;border-right: #666666 solid 2px; text-align:right'><strong><font size='5px'>";
// echo Get_TotalAccountByLedger('DR' ,$from_date,$to_date);
 //echo "<br>";
$data.=array_sum($add_TD);
$data.="</font></strong></td>";
$data.="<td style='border-bottom: #666666 solid 2px;border-left: #FFFFFF solid 2px;border-top: #FFFFFF solid 2px;border-right: #666666 solid 2px; text-align:right'><strong><font size='5px'>";
$data.=array_sum($add_TC);
$data.="</font></strong></td>";
$data.="<td style='border-bottom: #666666 solid 2px;border-left: #FFFFFF solid 2px;border-top: #FFFFFF solid 2px;border-right: #666666 solid 2px; text-align:right'><strong>";
$data.=array_sum($add_closeDR);
$data.="</strong></td>";
$data.="<td style='border-bottom: #666666 solid 2px;border-left: #FFFFFF solid 2px;border-top: #FFFFFF solid 2px;border-right: #666666 solid 2px; text-align:right'><strong>";
$data.=array_sum($add_closeCR);
$data.="</strong></td>";
$data.="</tr>";
} 
$data.="</table>";
//echo $data;
$header='';  	
header("Content-type: application/x-msdownload"); 
header("Content-Disposition: attachment; filename=LedgerWiseTrialBalance.xls"); 
header("Pragma: no-cache"); 
header("Expires: 0"); 
print "$header\n$data"; 