<?php
ob_start();
session_name('CAKEPHP'); session_start();


include('includes/DateFunctions.inc');

include('exportconfig.php');

$lid = $_SESSION['lid'];
$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']);
}





class db_query
{
	function getField($table, $condition, $val, $field)
	{

		global $db;
		//echo "select ".$field." from ".$table." where ".$condition." ='".$val."'";
		$sql = mysqli_query($db, "select " . $field . " from " . $table . " where " . $condition . " ='" . $val . "'");
		$r = mysqli_fetch_array($sql);
		//echo "select ".$field." from ".$table." where ".$condition." ='".$val."'"; 
		return ($r[$field]);
	}
}
$s = new db_query();



function getGroupBalanceSheet($group_id, $from_date = '', $to_date = '')
{
	global $db;
	//echo "from---".$from_date."to---".$to_date;

	$sql1 = mysqli_query($db, "SELECT GroupId FROM accountgroupmaster WHERE MainGroupId=" . $group_id . " ");
	$ar = '';
	$ledger_arr = '';
	while ($res1 = mysqli_fetch_array($sql1)) {
		$ar[] = $res1['GroupId'];
	}
	//echo $group_id;
	//print_r($ar); echo "<br />";
	if ($ar != '') {
		$underLevels = getUnderGroupTree($ar, '');
		//echo "underlevels--";print_r($underLevels);echo "<br />";
		if ($underLevels != '') {
			$groups = $underLevels;
		} else {
			$groups = $ar;
		}
		//print_r($groups); echo "<br /><br />";
		$allGroups = $group_id . ',' . implode(',', $groups);
		$getDirectLedgers = "Select AccountsID from accountledger where UnderGroup in (" . $allGroups . ")";
		$directLedgersResult = mysqli_query($db, $getDirectLedgers);
	} else {
		$getDirectLedgers = "Select AccountsID from accountledger where UnderGroup=" . $group_id . "";
		$directLedgersResult = mysqli_query($db, $getDirectLedgers);
	}

	//echo "<b />****************************************<br />";
	$ledger_arr = '';
	$tot_VoucherDR = '';
	$tot_VoucherCR = '';
	$tot_CR_open = '';
	$tot_DR_open = '';
	$etaxDetails1 = '';
	$staxDetails1 = '';
	$vendorDetails1 = '';
	$clientDetails1 = '';
	$etaxDRArr = '';
	$etaxCRArr = '';
	$staxDRArr = '';
	$staxCRArr = '';
	$purchCRArr = '';
	$invDRArr = '';
	$legderDRArr = '';
	$legderCRArr = '';
	$a1 = '';
	$a2 = '';
	$b1 = '';
	$b2 = '';
	$c1 = '';
	$c2 = '';
	$d1 = '';
	$d2 = '';
	while ($directLedgers = mysqli_fetch_array($directLedgersResult)) {
		$ledger_arr[] = $directLedgers['AccountsID'];
	}
	//echo "-----".$group_id; echo "<br />";
	//echo "ledger_array--";print_r($ledger_arr); echo "<br /><br />";

	if ($ledger_arr != '') {

		$ledger_string = implode(',', $ledger_arr);
		if ($from_date != '' && $to_date != '') {
			$tot_VoucherDR = Get_AccountByLedger($ledger_string, 'DR', $from_date, $to_date);
			$tot_VoucherCR = Get_AccountByLedger($ledger_string, 'CR', $from_date, $to_date);
		} else {
			$tot_VoucherDR = Get_AccountByLedger($ledger_string, 'DR', '', '');
			$tot_VoucherCR = Get_AccountByLedger($ledger_string, 'CR', '', '');
		}

		if ($from_date != '' && $to_date != '') {
			$tot_CR_open = getLedgerOpenings($ledger_string, 'CR', $from_date, $to_date);
			$tot_DR_open = getLedgerOpenings($ledger_string, 'DR', $from_date, $to_date);
		} else {
			$tot_CR_open = getLedgerOpenings($ledger_string, 'CR', '', '');
			$tot_DR_open = getLedgerOpenings($ledger_string, 'DR', '', '');
		}

		$getLedgerType = mysqli_query($db, "Select AccountsID,vender_client,taxid,ledgertype from accountledger where AccountsID in ($ledger_string)");
		//echo "<br /><br />Select AccountsID,vender_client,taxid,ledgertype from accountledger where AccountsID in ($ledger_string)";
		while ($LedgerTypes = mysqli_fetch_array($getLedgerType)) {
			$ledgertype = $LedgerTypes['ledgertype'];
			$master_tax_id = $LedgerTypes['taxid'];
			$master_party_id = $LedgerTypes['vender_client'];
			if ($ledgertype != '') {
				if ($ledgertype == 'excisetax') {
					if ($from_date != '' && $to_date != '') {
						$etaxDetails1 = yearlyETaxAccountByTransactions($master_tax_id, $from_date, $to_date);
					} else {
						$etaxDetails1 = yearlyETaxAccountByTransactions($master_tax_id, '', '');
					}
					$etaxDR1 = $etaxDetails1[0];
					$etaxCR1 = $etaxDetails1[1];
					$etaxDRArr[] = $etaxDR1;
					$etaxCRArr[] = $etaxCR1;
				} else {
					$etaxDetails1 = '';
				}

				if ($ledgertype == 'salestax') {
					if ($from_date != '' && $to_date != '') {
						$staxDetails1 = yearlySTaxAccountByTransactions($master_tax_id, $from_date, $to_date);
					} else {
						$staxDetails1 = yearlySTaxAccountByTransactions($master_tax_id, '', '');
					}
					$staxDR1 = $staxDetails1[0];
					$staxCR1 = $staxDetails1[1];
					$staxDRArr[] = $staxDR1;
					$staxCRArr[] = $staxCR1;
				} else {
					$staxDetails1 = '';
				}

				if ($ledgertype == 'vendor') {
					if ($from_date != '' && $to_date != '') {
						$vendorDetails1 = yearlyVendorAccountByTransactions($master_party_id, $from_date, $to_date);
					} else {
						$vendorDetails1 = yearlyVendorAccountByTransactions($master_party_id, '', '');
					}
					$purchCR1 = $vendorDetails1;
					$purchCRArr[] = $purchCR1;
				} else {
					$purchCR1 = '';
				}

				if ($ledgertype == 'client') {
					if ($from_date != '' && $to_date != '') {
						$clientDetails1 = yearlyClientAccountByTransactions($master_party_id, $from_date, $to_date);
					} else {
						$clientDetails1 = yearlyClientAccountByTransactions($master_party_id, '', '');
					}
					$invDR1 = $clientDetails1;
					$invDRArr[] = $invDR1;
				} else {
					$clientDetails1 = '';
				}
			} else {
				if ($from_date != '' && $to_date != '') {
					$ledgerPosting1 = yearlyLedgerPostingByTransactions($LedgerTypes['AccountsID'], $from_date, $to_date);
				} else {
					$ledgerPosting1 = yearlyLedgerPostingByTransactions($LedgerTypes['AccountsID'], '', '');
				}
				$legderDR1 = $ledgerPosting1[0];
				$legderCR1 = $ledgerPosting1[1];
				$legderDRArr[] = $legderDR1;
				$legderCRArr[] = $legderCR1;
				$etaxDetails1 = '';
				$staxDetails1 = '';
				$vendorDetails1 = '';
				$clientDetails1 = '';
			}
		}

		if ($etaxDRArr != '') $a1 = array_sum($etaxDRArr); //echo "1--";print_r($etaxDRArr); echo "<br />";
		if ($etaxCRArr != '') $a2 = array_sum($etaxCRArr); //echo "2--";print_r($etaxCRArr);echo "<br />";
		if ($staxDRArr != '') $b1 = array_sum($staxDRArr); //echo "3--";print_r($staxDRArr);echo "<br />";
		if ($staxCRArr != '') $b2 = array_sum($staxCRArr); //echo "4--";print_r($staxCRArr);echo "<br />";
		if ($purchCRArr != '') $c2 = array_sum($purchCRArr); //echo "5--";print_r($purchCRArr);echo "<br />";
		if ($invDRArr != '') $c1 = array_sum($invDRArr); //echo "6--";print_r($invDRArr);echo "<br />";
		if ($legderDRArr != '') $d1 = array_sum($legderDRArr);  //echo "7--";print_r($legderDRArr);echo "<br />";
		if ($legderCRArr != '') $d2 = array_sum($legderCRArr); //echo "8--";print_r($legderCRArr);echo "<br />";
		//echo "<br />----------------<br /><br />";



	}
	$TD = $tot_VoucherDR + $a1 + $b1 + $c1 + $d1;
	$TC = $tot_VoucherCR + $a2 + $b2 + $c2 + $d2;

	$return_array[] = $TD;
	$return_array[] = $TC;
	$return_array[] = $tot_DR_open;
	$return_array[] = $tot_CR_open;
	return $return_array;
}



function getUnderGroupTree($gid, $Tarray)
{
	global $db;
	//echo "gid---";print_r($gid);
	$ID = implode(',', $gid);
	$array = '';
	$total_array = '';
	//echo "ID---";print_r($ID);
	//echo "<br />Tarray--";print_r($Tarray);
	$sql = "Select * from accountgroupmaster where MainGroupId in ($ID)"; //echo "<br />".$sql;
	$result = mysqli_query($db, $sql);
	while ($row = mysqli_fetch_array($result)) {
		$array[] = $row['GroupId'];
	}
	if ($array != '') {
		$total_array = array_merge($gid, $array);
	}
	if ($Tarray != '' && $array != '') {
		$final = array_merge($Tarray, $total_array);
	} else {
		$final = $total_array;
	}
	//echo "<br />final---";print_r($final);
	//print_r($array);echo "<br />-----------in function -----------<br />";
	if ($array != '') {
		return getUnderGroupTree($array, $final);
	} else {
		if ($Tarray != '') return array_unique($Tarray);
	}
}

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']);
	}
}



function yearlyETaxAccountByTransactions($taxId, $from_date = '', $to_date = '')
{
	global $db;
	$return_array = '';
	if ($from_date != '' && $to_date != '') {
		$query1 = mysqli_fetch_array(mysqli_query($db, "Select SUM(exciseValue) as total_inv_excise from invoice where exciseDuty='" . $taxId . "' AND invoiceDate>='" . str_replace("/", "-", $from_date) . "' and invoiceDate<='" . str_replace("/", "-", $to_date) . "'"));
		$query2 = mysqli_fetch_array(mysqli_query($db, "Select SUM(B.Excise_Duty_value) as total_pur_excise ,A.bill_book_date from purchase_bill_details B, purchase_billbook A where A.id=B.purch_billbookid and B.Excise_Duty='" . $taxId . "' AND A.bill_book_date>='" . str_replace("/", "-", $from_date) . "' and A.bill_book_date<='" . str_replace("/", "-", $to_date) . "'"));
		//echo "<br /><br />2---------Select SUM(B.Excise_Duty_value) as total_pur_excise ,A.bill_book_date from purchase_bill_details B, purchase_billbook A where A.id=B.purch_billbookid and B.Excise_Duty='".$taxId."' AND A.bill_book_date>='".str_replace("/","-",$from_date)."' and A.bill_book_date<='".str_replace("/","-",$to_date)."'";
	} else {
		$query1 = mysqli_fetch_array(mysqli_query($db, "Select SUM(exciseValue) as total_inv_excise from invoice where exciseDuty='" . $taxId . "' AND invoiceDate>='" . $_SESSION['financialYrStart'] . "' and invoiceDate<='" . $_SESSION['financialYrEnd'] . "'"));
		$query2 = mysqli_fetch_array(mysqli_query($db, "Select SUM(B.Excise_Duty_value) as total_pur_excise ,A.bill_book_date from purchase_bill_details B, purchase_billbook A where A.id=B.purch_billbookid and B.Excise_Duty='" . $taxId . "' AND A.bill_book_date>='" . $_SESSION['financialYrStart'] . "' and A.bill_book_date<='" . $_SESSION['financialYrEnd'] . "'"));
		//echo "Select SUM(B.Excise_Duty_value) as total_pur_excise ,A.bill_book_date from purchase_bill_details B, purchase_billbook A where A.id=B.purch_billbookid and B.Excise_Duty='".$taxId."' AND A.bill_book_date>='".$_SESSION['financialYrStart']."' and A.bill_book_date<='".$_SESSION['financialYrEnd']."' <br />";
	}
	//echo $query2['total_pur_excise'];
	$return_array[] = $query2['total_pur_excise'];
	$return_array[] = $query1['total_inv_excise'];
	return $return_array;
}




function getLedgerOpenings($ledgers, $type, $from_date, $to_date)
{
	global $db;
	if ($from_date != '' && $to_date != '') {
		$query = "Select SUM(openingBalance) as total_opening from accountledger where AccountsID in ($ledgers) and openingtype='$type' and openingdate<='" . str_replace('/', '-', $to_date) . "'";
	} else {
		$query = "Select SUM(openingBalance) as total_opening from accountledger where AccountsID in ($ledgers) and openingtype='$type'";
	}
	//echo $query;
	$res = mysqli_query($db, $query);
	$row = mysqli_fetch_array($res);
	return $row['total_opening'];
}


function yearlyVendorAccountByTransactions($vendorId, $from_date = '', $to_date = '')
{
	global $db;
	if ($vendorId != '') {
		if ($from_date != '' && $to_date != '') {
			$query = mysqli_fetch_array(mysqli_query($db, "Select SUM(GrandTotal) as purch_total from purchase_billbook where vendor=$vendorId AND bill_book_date>='" . str_replace("/", "-", $from_date) . "' and bill_book_date<='" . str_replace("/", "-", $to_date) . "'"));
			//echo "Select SUM(GrandTotal) as purch_total from purchase_billbook where vendor=$vendorId AND bill_book_date>='".str_replace("/","-",$from_date)."' and bill_book_date<='".str_replace("/","-",$to_date)."'";
		} else {
			$query = mysqli_fetch_array(mysqli_query($db, "Select SUM(GrandTotal) as purch_total from purchase_billbook where vendor=$vendorId AND bill_book_date>='" . $_SESSION['financialYrStart'] . "' and bill_book_date<='" . $_SESSION['financialYrEnd'] . "'"));
		}
		return $query['purch_total'];
	}
}



function yearlyLedgerPostingByTransactions($ledgerId, $from_date = '', $to_date = '')
{
	global $db;
	$return_array = '';
	if ($from_date != '' && $to_date != '') {
		$query1 = mysqli_fetch_array(mysqli_query($db, "Select SUM(basicAmount) as total_inv_basic from invoice where ledger_id=(" . $ledgerId . ") AND invoiceDate>='" . str_replace("/", "-", $from_date) . "' and invoiceDate<='" . str_replace("/", "-", $to_date) . "'"));

		$query2 = mysqli_fetch_array(mysqli_query($db, "Select SUM(total_item_amount) as total_pur_basic from purchase_billbook where ledger=(" . $ledgerId . ") AND bill_book_date>='" . str_replace("/", "-", $from_date) . "' and bill_book_date<='" . str_replace("/", "-", $to_date) . "'"));
	} else {
		$query1 = mysqli_fetch_array(mysqli_query($db, "Select SUM(basicAmount) as total_inv_basic from invoice where ledger_id in (" . $ledgerId . ") AND invoiceDate>='" . $_SESSION['financialYrStart'] . "' and invoiceDate<='" . $_SESSION['financialYrEnd'] . "'"));
		$query2 = mysqli_fetch_array(mysqli_query($db, "Select SUM(total_item_amount) as total_pur_basic from purchase_billbook where ledger in (" . $ledgerId . ") AND bill_book_date>='" . $_SESSION['financialYrStart'] . "' and bill_book_date<='" . $_SESSION['financialYrEnd'] . "'"));
		//echo "<br />Select SUM(total_item_amount) as total_pur_basic from purchase_billbook where ledger='".$ledgerId."' AND bill_book_date>='".$_SESSION['financialYrStart']."' and bill_book_date<='".$_SESSION['financialYrEnd']."'";
	}
	$return_array[] = $query2['total_pur_basic'];
	$return_array[] = $query1['total_inv_basic'];
	return $return_array;
}

function yearlyClientAccountByTransactions($clientId, $from_date = '', $to_date = '')
{
	global $db;
	if ($clientId != '') {
		if ($from_date != '' && $to_date != '') {
			$query = mysqli_fetch_array(mysqli_query($db, "Select SUM(grandTotal) as inv_total from invoice where client_id=$clientId AND invoiceDate>='" . str_replace("/", "-", $from_date) . "' and invoiceDate<='" . str_replace("/", "-", $to_date) . "'"));
		} else {
			$query = mysqli_fetch_array(mysqli_query($db, "Select SUM(grandTotal) as inv_total from invoice where client_id=$clientId AND invoiceDate>='" . $_SESSION['financialYrStart'] . "' and invoiceDate<='" . $_SESSION['financialYrEnd'] . "'"));
		}
		return $query['inv_total'];
	}
}



function getMonthsBalanceSheet($lid, $month, $yr, $flag = '')
{
	$inv_DR = '';
	$excise_DR = '';
	$salestax_DR = '';
	$ledger_DR = '';
	$excise_CR = '';
	$salestax_CR = '';
	$ledger_CR = '';
	$purch_CR = '';
	global $db;


	$QUERY11 = mysqli_fetch_array(mysqli_query($db, "Select vender_client from accountledger where AccountsID=$lid"));
	$QUERY22 = mysqli_fetch_array(mysqli_query($db, "Select taxid from accountledger where AccountsID=$lid"));
	$QUERY33 = mysqli_fetch_array(mysqli_query($db, "Select ledgertype from accountledger where AccountsID=$lid"));

	$master_party_id = $QUERY11['vender_client'];
	$master_tax_id = $QUERY22['taxid'];
	$ledgertype = $QUERY33['ledgertype'];
	//echo "<br />flag---".$flag;
	//echo "isset---".isset($flag);
	if ($flag != '0') {
		if (isset($flag) == 1 && $flag != '') {
			if ($flag == 1) {
				$cond1 = "A.Dates between '" . str_replace('/', '-', $from_date) . "' and '" . str_replace('/', '-', $to_date) . "'";
				$cond2 = "A.bill_book_date between '" . str_replace('/', '-', $from_date) . "' and '" . str_replace('/', '-', $to_date) . "'";
				$cond3 = "invoiceDate between '" . str_replace('/', '-', $from_date) . "' and '" . str_replace('/', '-', $to_date) . "'";
			} elseif ($flag == 2) {
				$cond1 = "A.Dates >= '" . str_replace('/', '-', $from_date) . "' and MONTH(A.Dates)=$month";
				$cond2 = "A.bill_book_date >= '" . str_replace('/', '-', $from_date) . "' and MONTH(A.bill_book_date)=$month";
				$cond3 = "invoiceDate >= '" . str_replace('/', '-', $from_date) . "' and MONTH(invoiceDate)=$month";
			} elseif ($flag == 3) {
				$cond1 = "A.Dates <= '" . str_replace('/', '-', $to_date) . "' and MONTH(A.Dates)=$month";
				$cond2 = "A.bill_book_date <= '" . str_replace('/', '-', $to_date) . "' and MONTH(A.bill_book_date)=$month";
				$cond3 = "invoiceDate <= '" . str_replace('/', '-', $to_date) . "' and MONTH(invoiceDate)=$month";
			} elseif ($flag == 4) {
				$cond1 = "MONTH(A.Dates)=$month";
				$cond2 = "MONTH(A.bill_book_date)=$month";
				$cond3 = "MONTH(invoiceDate)=$month";
			}
		} elseif (isset($flag) == 1 && $flag == '') {
			$cond1 = "MONTH(A.Dates)='$month' and YEAR(A.Dates)='$yr'";
			$cond2 = "MONTH(A.bill_book_date)=$month and YEAR(A.bill_book_date)=$yr";
			$cond3 = "MONTH(invoiceDate)=$month and YEAR(invoiceDate)=$yr";
		}

		//echo $cond1."<br />";
		//echo $cond2."<br />";echo $cond3."<br />";





		$getVoucherCR = mysqli_fetch_array(mysqli_query($db, "Select SUM(B.amount) as TCRAmount from account_book A, account_book_details B where A.id=B.account_bookid and B.ledgerid=$lid and B.amount_type='CR' and $cond1"));
		$getVoucherDR = mysqli_fetch_array(mysqli_query($db, "Select SUM(B.amount) as TDRAmount from account_book A, account_book_details B where A.id=B.account_bookid and B.ledgerid=$lid and B.amount_type='DR' and $cond1"));

		if ($ledgertype != '') {
			if ($ledgertype == 'vendor') {
				$query = mysqli_fetch_array(mysqli_query($db, "Select SUM(A.GrandTotal) as purch_total from purchase_billbook A where A.vendor=$master_party_id and $cond2"));
				$purch_CR = $query['purch_total'];
			} elseif ($ledgertype == 'client') {
				if ($master_party_id != '') {
					$query = mysqli_fetch_array(mysqli_query($db, "Select SUM(grandTotal) as inv_total from invoice where client_id=$master_party_id and $cond3"));
					$inv_DR = $query['inv_total'];
				}
			} elseif ($ledgertype == 'excisetax') {
				$query1 = mysqli_fetch_array(mysqli_query($db, "Select SUM(exciseValue) as total_inv_excise from invoice where exciseDuty='" . $master_tax_id . "' and $cond3"));
				$excise_CR = $query1['total_inv_excise'];

				$query2 = mysqli_fetch_array(mysqli_query($db, "Select SUM(B.Excise_Duty_value) as total_pur_excise ,A.bill_book_date from purchase_bill_details B, purchase_billbook A where A.id=B.purch_billbookid and B.Excise_Duty='" . $master_tax_id . "' and $cond2"));
				$excise_DR = $query2['total_pur_excise'];
			} elseif ($ledgertype == 'salestax') {
				$query1 = mysqli_fetch_array(mysqli_query($db, "Select SUM(VatCstValue) as total_inv_vatsct from invoice where VatCst='" . $master_tax_id . "' and $cond3"));
				$salestax_CR = $query1['total_inv_vatsct'];

				$query2 = mysqli_fetch_array(mysqli_query($db, "Select SUM(B.vat_cst_amt) as total_pur_vatsct from purchase_billbook A, purchase_bill_details B where A.id=B.purch_billbookid and B.vat_cst='" . $master_tax_id . "' and $cond2"));
				$salestax_DR = $query2['total_pur_vatsct'];
			}
		} else {
			$query1 = mysqli_fetch_array(mysqli_query($db, "Select SUM(basicAmount) as total_inv_basic from invoice where ledger_id='" . $lid . "' and $cond3"));
			$ledger_CR = $query1['total_inv_basic'];

			$query2 = mysqli_fetch_array(mysqli_query($db, "Select SUM(A.total_item_amount) as total_pur_basic from purchase_billbook A where A.ledger='" . $lid . "' and $cond2"));
			$ledger_DR = $query2['total_pur_basic'];
		}

		$totalDR = $getVoucherDR['TDRAmount'] + $inv_DR + $excise_DR + $salestax_DR + $ledger_DR;
		$totalCR = $getVoucherCR['TCRAmount'] + $purch_CR + $excise_CR + $salestax_CR + $ledger_CR;

		$return_array[] = $totalDR;
		$return_array[] = $totalCR;

		return $return_array;
	}
}


function yearlySTaxAccountByTransactions($taxId, $from_date = '', $to_date = '')
{
	global $db;
	$return_array = '';
	if ($from_date != '' && $to_date != '') {
		$query1 = mysqli_fetch_array(mysqli_query($db, "Select SUM(VatCstValue) as total_inv_vatsct from invoice where VatCst='" . $taxId . "' AND invoiceDate>='" . str_replace("/", "-", $from_date) . "' and invoiceDate<='" . str_replace("/", "-", $to_date) . "'"));

		$query2 = mysqli_fetch_array(mysqli_query($db, "Select SUM(B.vat_cst_amt) as total_pur_vatsct from purchase_billbook A, purchase_bill_details B where A.id=B.purch_billbookid and B.vat_cst='" . $taxId . "' AND A.bill_book_date>='" . str_replace("/", "-", $from_date) . "' and A.bill_book_date<='" . str_replace("/", "-", $to_date) . "'"));
	} else {
		$query1 = mysqli_fetch_array(mysqli_query($db, "Select SUM(VatCstValue) as total_inv_vatsct from invoice where VatCst='" . $taxId . "' AND invoiceDate>='" . $_SESSION['financialYrStart'] . "' and invoiceDate<='" . $_SESSION['financialYrEnd'] . "'"));
		$query2 = mysqli_fetch_array(mysqli_query($db, "Select SUM(B.vat_cst_amt) as total_pur_vatsct from purchase_billbook A, purchase_bill_details B where A.id=B.purch_billbookid and B.vat_cst='" . $taxId . "' AND A.bill_book_date>='" . $_SESSION['financialYrStart'] . "' and A.bill_book_date<='" . $_SESSION['financialYrEnd'] . "'"));
	}
	$return_array[] = $query2['total_pur_vatsct'];
	$return_array[] = $query1['total_inv_vatsct'];
	return $return_array;
}


$month_array = array('04' => 'April', '05' => 'May', '06' => 'June', '07' => 'July', '08' => 'August', '09' => 'September', '10' => 'October', '11' => 'November', '12' => 'December', '01' => 'January', '02' => 'February', '03' => 'March');
//echo $_SESSION['financialYrStart'];
//echo $_SESSION['financialYrEnd'];

if ($from_date != '' && $to_date != '') {
	$opening_bal = $s->GetFieldValue('accountledger', "AccountsID=$lid and openingdate<='" . $to_date . "'", 'openingBalance');
} else {
	$opening_bal = $s->getField('accountledger', 'AccountsID', $lid, 'openingBalance');
}

$opening_type = $s->getField('accountledger', 'AccountsID', $lid, 'openingtype');
$openingDR = '';
$openingCR = '';
$closeDR = '';
$closeCR = '';


if ($opening_type != '') {
	if ($opening_type == 'CR') {
		$openingCR = $opening_bal;
		$openingDR = '';
	} else {
		$openingDR = $opening_bal;
		$openingCR = '';
	}
}

$data = '';
$data .= "<tr>";
$data .= "<td colspan='4'><h3>" . $s->getField('accountledger', 'AccountsID', $lid, 'AccountName') . "</h3></td>";
$data .= "</tr>";
$data .= "<tr>";
$data .= "<td colspan='4'><h2>" . $period . "</h2></td>";
$data .= "</tr>";
$data .= "<table width='100%' border='0' style='border:#999999 solid 1px' cellspacing='3' cellpadding='3' >";
$data .= "<tr>";
$data .= "<th width='40%' style='border-bottom: #666666 solid 2px;border-left: #FFFFFF solid 2px;border-top: #FFFFFF solid 2px;border-right: #666666 solid 2px;'>Months</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;'>Debit</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;'>Credit</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;'>Closing</th>";
$data .= "</tr>";
foreach ($month_array as $key => $val) {
	$a = explode('-', $_SESSION['financialYrStart']);
	$b = explode('-', $_SESSION['financialYrEnd']);
	if ($key >= '04' && $key <= '12') {
		$yr = $a[0];
	} elseif ($key >= '01' && $key < '03') {
		$yr = $b[0];
	}
	$flag = '';
	if ($from_date != '' && $to_date != '') {
		$FDATEBRK = explode('/', $from_date);
		$TDATEBRK = explode('/', $to_date);
		$fmnth = $FDATEBRK[1];
		$tmnth = $TDATEBRK[1];
		$flag = 0;
		if ($fmnth == $tmnth) {
			if ($key == $fmnth) {
				$flag = 1;
			}
		} elseif ($fmnth != $tmnth) {
			if ($fmnth == $key) {
				$flag = 2;
			} else
				if ($tmnth == $key) {
				$flag = 3;
			}
		}
		$amounts = getMonthsBalanceSheet($lid, $key, $yr, $flag);
	} else {
		$amounts = getMonthsBalanceSheet($lid, $key, $yr, '');
	}
	$totalDR = $amounts[0];
	$totalCR = $amounts[1];

	$data .= "<tr>";
	$data .= "<td style='padding-left:5px'><strong><font color='#000000'>" . $val . "</font></strong></a></td>";
	$data .= "<td style='text-align:right; padding-right:5px;'><strong>";
	if ($totalDR == 0) {
		$data .= "";
	} else {
		$data .= $totalDR;
	}
	$arrayDR[] = $totalDR;
	$data .= "</strong></td>";
	$data .= "<td style='text-align:right; padding-right:5px;'><strong>";
	if ($totalCR == 0) {
		$data .= "";
	} else {
		$data .= $totalCR;
	}
	$arrayCR[] = $totalCR;
	$data .= "</strong></td>";
	$data .= "<td style='text-align:right; padding-right:5px;'><strong>";
	if ($totalDR == 0 && $totalCR == 0) {
		$closing = '';
		$type = '';
	} else
if ($totalDR > $totalCR) {
		$closing = $totalDR - $totalCR;
		$type = "DR";
	} else
if ($totalCR > $totalDR) {
		$closing = $totalCR - $totalDR;
		$type = "CR";
	}
	$data .= $closing . " " . $type;
	$data .= "</strong></td>";
	$data .= "</tr>";
}
$data .= "<tr>";
$data .= "<th width='40%' style='border-bottom: #666666 solid 2px;border-left: #FFFFFF solid 2px;border-top: #FFFFFF solid 2px;border-right: #666666 solid 2px; text-align:right'>Opening</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; text-align:right'>" . $openingDR . "</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; text-align:right'>" . $openingCR . "</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; text-align:right'>&nbsp;</th>";
$data .= "</tr>";
$data .= "<tr>";
$data .= "<th width='40%' style='border-bottom: #666666 solid 2px;border-left: #FFFFFF solid 2px;border-top: #FFFFFF solid 2px;border-right: #666666 solid 2px; text-align:right'>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; text-align:right'>";
$data .= $DR = array_sum($arrayDR);
$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; text-align:right'>";
$data .= $CR = array_sum($arrayCR);
$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; text-align:right'>";
//if($DR>$CR){echo $DR-$CR; echo " DR";}elseif($CR>$DR){echo $CR-$DR; echo " CR";}
$data .= "</th>";
$data .= "</tr>";
$data .= "<tr>";
$data .= "<th width='40%' style='border-bottom: #666666 solid 2px;border-left: #FFFFFF solid 2px;border-top: #FFFFFF solid 2px;border-right: #666666 solid 2px; text-align:right'>Total Closing</th>";
if ($opening_type != '') {
	if ($opening_type == 'DR') {
		$DRR = $DR + $opening_bal;
		$CRR = $CR;
	} elseif ($opening_type == 'CR') {
		$CRR = $CR + $opening_bal;
		$DRR = $DR;
	}
} else {
	$DRR = $DR;
	$CRR = $CR;
}
if ($DRR > $CRR) {
	$closeDR = $DRR - $CRR;
} elseif ($CRR > $DRR) {
	$closeCR = $CRR - $DRR;
}
$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; text-align:right'>";

$data .= $closeDR;
$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; text-align:right'>";
$data .= $closeCR;
$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; text-align:right'></th>";
$data .= "</tr>";
$data .= "</table>";
$header = '';
header("Content-type: application/x-msdownload");
header("Content-Disposition: attachment; filename=APRGroupWise-TrialBalance-Liabilities_Months_Exl.xls");
header("Pragma: no-cache");
header("Expires: 0");
print "$header\n$data";
