<?php ob_start();
session_name('CAKEPHP'); session_start();
//include('Account/fun-Trial.php');
include('exportconfig.php');

function Get_AccountByLedger($LId, $amount_type, $from_date = '', $to_date = '')
{
	global $db;
	if ($from_date != '' && $to_date != '') {
		$q1 = mysqli_query($db, "select  SUM(B.amount) AS TAmount ,B.ledgerid from  account_book A,account_book_details B where A.id=B.account_bookid and A.Dates between '" . str_replace("/", "-", $from_date) . "' and '" . str_replace("/", "-", $to_date) . "' and  B.ledgerid in (" . $LId . ") and B.amount_type='" . $amount_type . "'");
		//echo "select  SUM(B.amount) AS TAmount ,B.ledgerid from  account_book A,account_book_details B where A.id=B.account_bookid and A.Dates between '".str_replace("/","-",$from_date)."' and '".str_replace("/","-",$to_date)."' and  B.ledgerid in (".$LId.") and B.amount_type='".$amount_type."' <br /><br />";
	} else {
		$q1 = mysqli_query($db, "select  SUM(B.amount) AS TAmount ,B.ledgerid from  account_book A,account_book_details B where A.id=B.account_bookid and  B.ledgerid in (" . $LId . ")   and B.amount_type='" . $amount_type . "'  AND A.Dates>='" . $_SESSION['financialYrStart'] . "' and A.Dates<='" . $_SESSION['financialYrEnd'] . "'");
		//echo "1-----------select  SUM(B.amount) AS TAmount ,B.ledgerid from  account_book A,account_book_details B where A.id=B.account_bookid and  B.ledgerid in (".$LId.")   and B.amount_type='".$amount_type."'  AND A.Dates>='".$_SESSION['financialYrStart']."' and A.Dates<='".$_SESSION['financialYrEnd']."' <br /><br />";
	}
	while ($RVPrefix = mysqli_fetch_array($q1)) {

		return ($RVPrefix['TAmount']);
	}
}
$ids = $_SESSION['ids'];
$data = '';
$data .= "<table width='100%' border='1'>";
$data .= "<tr>";
$data .= "<th style='width:500px;'>Particulars</th>";
$data .= "<th>Opening Balance</th>";
$data .= "<th>Debit Amount</th>";
$data .= "<th>Credit Amount</th>";
$data .= "<th>Closing Amount</th>";
$data .= "</tr>";

//echo 'select * from accountledger where UnderGroup in($ids)';
$TDR = array();
$TCR = array();
$OPB = array();
$CLB = array();
$openingamt1 = 0;
$openingamt2 = 0;
$openingamt = 0;
//echo "select * from accountledger where UnderGroup in($ids)";
$GetLedger = mysqli_query($db, "select AccountsID,vender_client,openingtype,openingBalance,AccountName from accountledger where UnderGroup in($ids)");
//echo DB_num_rows($GetLedger);


if (mysqli_num_rows($GetLedger) > 0) {
	while ($RowGetLedger = mysqli_fetch_array($GetLedger)) {
		if ($RowGetLedger['openingtype'] == 'CR') {
			$openingamt1 = $RowGetLedger['openingBalance'];
		} else {
			$openingamt2 = $RowGetLedger['openingBalance'];
		}

		$openingamt = $RowGetLedger['openingBalance'];
		$TotalDR = Get_AccountByLedger($RowGetLedger['AccountsID'], 'DR');
		$TotalCR = Get_AccountByLedger($RowGetLedger['AccountsID'], 'CR');

		if ($TotalDR > 0 || $TotalCR > 0) {
			$OPB[] = $openingamt;
			$TDR[] = $TotalDR;
			$TCR[] = $TotalCR;
			$data .= "<tr>";
			$data .= "<td style='width:500px;'>" . $RowGetLedger['AccountName'] . "</td>";
			$data .= "<td><div align='right'><span class='style1'>" . $openingamt . "</span></div></td>";
			$data .= "<td><div align='right'><strong>" . $TotalDR . "</strong></div></td>";
			$data .= "<td><div align='right'><strong>" . $TotalCR . "</strong></div></td>";
			if ($TotalDR > $TotalCR) {
				$closing = round($TotalDR - $TotalCR);
				$type = 'DR';
				$CLB[] = $closing;
			} elseif ($TotalCR > $TotalDR) {
				$closing = round($TotalCR - $TotalDR);
				$type = 'CR';
				$CLB[] = $closing;
			} else {
				$closing = $openingamt;
				$type = '';
			}
			if ($closing == '0') {
				$aks = '';
			} else {
				$aks = $closing;
			}
			$data .= "<td><div align='right'><span class='style2'>" . $aks . "
	</span> &nbsp;<strong>" . $type . "</strong></div></td>";
			$data .= "</tr>";
		}
	}
	$data .= "<tr bgcolor='#CCCCCC'>";
	$data .= "<td><div align='right'><strong>Total:</strong></div></td>";
	$data .= "<td><div align='right'><span class='style6'>" . array_sum($OPB) . "</span></div></td>";
	$data .= "<td><div align='right'><span class='style5'>" . array_sum($TDR) . "</span></div></td>";
	$data .= "<td><div align='right'><span class='style4'>" . array_sum($TCR) . "</span></div></td>";
	$data .= "<td><div align='right'><span class='style3'>" . array_sum($CLB) . "</span></div></td>";
	$data .= "</tr>";
	$data .= "</table>";


	$header = '';
	header("Content-type: application/x-msdownload");
	header("Content-Disposition: attachment; filename=accountgroupsumm_exl.xls");
	header("Pragma: no-cache");
	header("Expires: 0");
	print "$header\n$data";
}
