Location: PHPKode > scripts > Simple Accounting System > SimpleAccounts/postAction.php
<?php
/*
Author : Prem Kumar Kannan 
Version : 1
*/
session_start();
include_once "member.php";
include_once "lib.dbconnect.php";
include_once "lib.actualGroupHeadConfig.php";
//mysql_select_db($_COOKIE['username']);

$debug=false;
$echo = "";
$xml_file_name = "ac.php";

if ($debug) { $echo .= $_GET[isShowLedger]; }


if($_GET['isShowLedger'] != ""){ call_user_func($_GET['isShowLedger']); }


function saveDayBook() {
	global $echo,$debug;
	if( $_GET[debit]         != "" && 
		$_GET[credit]        != "" && 
		$_GET[dayBookDate]   != "" && 
		$_GET[dayBookContra] != "" && 
		$_GET[dayBookAmount] != "" && 
		$_GET[description]   != "" 
	)
	$query = "select acc_name from  ".$_COOKIE['username']."_account_name where (acc_name = '".trim(strtoupper($_GET['debit']))."')";
	$result = mysql_query($query);
	if (mysql_num_rows($result) == 0)
	{
		$echo .= "<br /> Debit Account not available.";
		return false;
	}
	$query = "select acc_name from  ".$_COOKIE['username']."_account_name where (acc_name = '".trim(strtoupper($_GET['credit']))."')";
	$result = mysql_query($query);
	if (mysql_num_rows($result) == 0)
	{
		$echo .= "<br /> Credit Account not available.";
		return false;
	}
	// Debit Column cannot have credit entries
	$query = "select group_head from  ".$_COOKIE['username']."_account_name where (acc_name = '".trim(strtoupper($_GET['debit']))."' and group_head = 'credit')";
	$result = mysql_query($query);
	if (mysql_num_rows($result) > 0)
	{
		$echo .= "<br /> ".$_GET['debit']." cannot be Debited";
		return false;
	}
	// Credit Column cannot have debit entries
	$query = "select group_head from  ".$_COOKIE['username']."_account_name where (acc_name = '".trim(strtoupper($_GET['credit']))."' and group_head = 'debit')";
	$result = mysql_query($query);
	if (mysql_num_rows($result) > 0)
	{
		$echo .= "<br /> ".$_GET['credit']." cannot be Credited.";
		return false;
	}	
	
	
	// Cash Balance cannot go negative.
	$query = "select acc_name from  ".$_COOKIE['username']."_account_name where (acc_name='".$_GET['credit']."' and act_group_head='CASH')";
	$result = mysql_query($query);
	if (mysql_num_rows($result) > 0) 
	{
		$query = calculateCashBalance($_GET['credit']);
		$result = mysql_query($query);
		//$echo .= "<table>";
		if(mysql_num_rows($result) != 0)
		{
			//$echo .= "<tr bgcolor=\"#bbbbbb\"><td></td><td>Debit</td><td>Credit</td><td>Balance</td></tr>";
			$total_balance = 0;
			$total_debit_balance = 0;
			$total_credit_balance = 0;

			while($row = mysql_fetch_array($result)) {
				//$echo .= "<tr bgcolor=\"#dddddd\"><td>".$row[name]."</td>";
				//$echo .= "<td>".$row[debitBalance]."</td>"; 
				$total_debit_balance += $row[debitBalance];
				//$echo .= "<td>".$row[creditBalance]."</td>"; 
				$total_credit_balance += $row[creditBalance];				
				//$echo .= "<td>".$row[balance]."</td>"; 
				$total_balance +=  $row[balance];
				//$echo .= "</tr>";
			}
			//$echo .= "<tr bgcolor=\"#bbbbbb\"><td>".$_GET['credit']."</td><td>$total_debit_balance</td><td>$total_credit_balance</td><td>$total_balance</td></tr>";
			//$echo .= "</table> \n";
			
			echo $_GET['credit']." Balance :".$total_balance."<br />";
			$check_balance = $total_balance-$_GET['dayBookAmount'];
			//echo "Balance : ".$check_balance." <br />";
			if ($check_balance < 0)
			{
				$echo .= "<br />".$_GET['credit']." Do not have enough balance to credit.";			
				return false;
			}
		} else {
			// First Time credit entry not allowed for cash.
			$echo .= "<br />".$_GET['credit']." Do not have enough balance to credit.";
			return false;
		}
	} else {
		
	}
	
	$sql = "INSERT INTO ".$_COOKIE['username']."_daybook(dayBookDate,debit,credit,dayBookContra,dayBookAmount,description) VALUES('".$_GET['dayBookDate']."','".trim(strtoupper($_GET['debit']))."','".trim(strtoupper($_GET['credit']))."','".$_GET['dayBookContra']."',".trim($_GET['dayBookAmount']).",'".trim($_GET['description'])."')";
	
	if ($debug) $echo .= $sql;
	
	$insertion_successfull = mysql_query($sql);
	
	if($insertion_successfull) 
	{
		$echo .= "<br> Data Successfully Saved";
		$sql = "SELECT * FROM ".$_COOKIE['username']."_daybook WHERE (dayBookDate between '".$_GET['dayBookDate']."' and '".$_GET['dayBookDate']."') ORDER BY daybookDate";
		$result = mysql_query($sql);		
		$echo .= "<table> \n";
		// Account Name on the top
		$echo .= "<tr bgcolor=\"#999999\"><td colspan=\"6\"> Day Book </td></tr>";
		// Table Header 
		$echo .= "<tr bgcolor=\"#bbbbbb\"><td>Date</td><td>Debit</td><td>Credit</td><td>Debit Amount</td><td>Credit Amount</td><td>Description</td></tr>";
		if(mysql_num_rows($result) > 0) {
			while ($row = mysql_fetch_array($result)) {
				if($row[dayBookContra] == "Y") {
					$echo .= "<tr bgcolor=\"#cccccc\"> \n";
					$echo .= "<td> $row[dayBookDate] </td> \n";
					$echo .= "<td> $row[debit] </td> \n";
					$echo .= "<td> $row[credit] </td> \n";
					//$echo .= "<td> $row[dayBookContra] </td> \n";
					$echo .= "<td> $row[dayBookAmount] </td> \n";
					$echo .= "<td  </td> \n";
					$echo .= "<td> $row[description] </td> \n";
					$echo .= "</tr>";
					$echo .= "<tr bgcolor=\"#eeeeee\"> \n";
					$echo .= "<td> $row[dayBookDate] </td> \n";
					$echo .= "<td> $row[credit] </td> \n";
					$echo .= "<td> $row[debit] </td> \n";
					//$echo .= "<td> $row[dayBookContra] </td> \n";
					$echo .= "<td>  </td> \n";			
					$echo .= "<td> $row[dayBookAmount] </td> \n";
					$echo .= "<td> $row[description] </td> \n";
					$echo .= "</tr>";
				} else if($row[dayBookContra] == "N") {
					$echo .= "<tr bgcolor=\"#cc3333\"> \n";
					$echo .= "<td> $row[dayBookDate] </td> \n";
					$echo .= "<td> $row[debit] </td> \n";
					$echo .= "<td> $row[credit] </td> \n";
					//$echo .= "<td> $row[dayBookContra] </td> \n";
					$echo .= "<td> $row[dayBookAmount] </td> \n";
					$echo .= "<td>  </td> \n";
					$echo .= "<td> $row[description] </td> \n";
					$echo .= "</tr>";
				}	
			}
			$echo .= "</table> \n";
		}
	} else {
		$echo .= "<br> Failed to Insert the data";
	}
}

function searchDayBook() 
{
	global $echo,$debug;
	$form_query = "";
	$select_query = "SELECT * FROM ".$_COOKIE['username']."_daybook WHERE ";
	$key = array_keys($_GET);
	for ($i =0; $i < count($key); $i++) {
		if($_GET[$key[$i]] != "") {
			if ($key[$i] == "dayBookAmount") {
				$form_query .= " AND $key[$i] = ".$_GET['dayBookAmount']; 
			} else if ($key[$i] == "isShowLedger"){				
		    } else if ($key[$i] == "dayBookDate"){
				$form_query .= " AND (dayBookDate between '".$_GET['dayBookDate']."' and '".$_GET['dayBookDateTo']."') ";
			} else if ($key[$i] == "dayBookDateTo"){
			} else {
				$form_query .= " AND $key[$i]='".$_GET[$key[$i]]."'";
			}
		}
	}	

	$form_query = substr($form_query, 5 , strlen($form_query)); 
	$select_query .= $form_query." ORDER BY dayBookDate DESC";	
	if ($debug) $echo .= "<br>".$select_query;

	// To avoid OR while appending with the select query 
	$result = mysql_query($select_query);
	$echo .= "<table> \n";
	// Account Name on the top
	$echo .= "<tr bgcolor=\"#dddddd\"><td colspan=\"6\"> Day Book for the period from ".$_GET['dayBookDate']." to ".$_GET['dayBookDateTo']."</td></tr>";
	

	// Table Header 
	$echo .= "<tr bgcolor=\"#bbbbbb\"><td>Date</td><td>Debit</td><td>Credit</td><td>Debit Amount</td><td>Credit Amount</td><td>Description</td></tr>";
	
	//if ($debug) $echo = "No of rows fetched....".mysql_num_rows($result);
	if(mysql_num_rows($result) > 0) {
		while ($row = mysql_fetch_array($result)) {
			if($row[dayBookContra] == "Y") {
				$echo .= "<tr bgcolor=\"#cccccc\"> \n";
				$echo .= "<td> $row[dayBookDate] </td> \n";
				$echo .= "<td> $row[debit] </td> \n";
				$echo .= "<td> $row[credit] </td> \n";
				//$echo .= "<td> $row[dayBookContra] </td> \n";
				$echo .= "<td> $row[dayBookAmount] </td> \n";
				$echo .= "<td  </td> \n";
				$echo .= "<td> $row[description] </td> \n";
				$echo .= "</tr>";
				$echo .= "<tr bgcolor=\"#eeeeee\"> \n";
				$echo .= "<td> $row[dayBookDate] </td> \n";
				$echo .= "<td> $row[credit] </td> \n";
				$echo .= "<td> $row[debit] </td> \n";
				//$echo .= "<td> $row[dayBookContra] </td> \n";
				$echo .= "<td>  </td> \n";			
				$echo .= "<td> $row[dayBookAmount] </td> \n";
				$echo .= "<td> $row[description] </td> \n";
				$echo .= "</tr>";
			} else if($row[dayBookContra] == "N") {
				$echo .= "<tr bgcolor=\"#cc3333\"> \n";
				$echo .= "<td> $row[dayBookDate] </td> \n";
				$echo .= "<td> $row[debit] </td> \n";
				$echo .= "<td> $row[credit] </td> \n";
				//$echo .= "<td> $row[dayBookContra] </td> \n";
				$echo .= "<td> $row[dayBookAmount] </td> \n";
				$echo .= "<td>  </td> \n";
				$echo .= "<td> $row[description] </td> \n";
				$echo .= "</tr>";
			}	
		}
		$echo .= "</table> \n";
	} else {
		$echo .= "<tr bgcolor=\"#999999\"><td colspan=\"6\">No Records Found </td>";
		$echo .= "</table> \n";
	}
}

function showLedger() {
	global $echo,$debug;
	$select_query = "SELECT refid,dayBookDate,debit,credit,dayBookContra,dayBookAmount,description,status,backup FROM ".$_COOKIE['username']."_daybook WHERE ";
	$key = array_keys($_GET);
	for ($i =0; $i < count($key); $i++) {
		if($_GET[$key[$i]] != "") {
			if ($key[i] == "dayBookAmount") {
				//$form_query .= " AND $key[$i] = ".$_GET[dayBookAmount]; 
			} else if ($key[$i] == "isShowLedger" || $key[$i] == "dayBookContra" ) {		
			} else if ($key[$i] == "dayBookDate"){
				$form_query .= " AND (dayBookDate between '".$_GET[dayBookDate]."' and '".$_GET[dayBookDateTo]."') ";
			} else if ($key[$i] == "dayBookDateTo"){			
			} else if ($key[$i] == "debit") {				
					$form_query .= " AND (debit='".trim(strtoupper($_GET[debit]))."' OR credit = '".trim(strtoupper($_GET[debit]))."')";				
			} else {
					//$form_query .= " AND $key[$i]='".$_GET[$key[$i]]."'";
			}
		}
	}	
	$form_query = substr($form_query, 5 , strlen($form_query)); 
	$select_query .= $form_query." ORDER BY dayBookDate DESC";
	if ($debug) $echo .= "<br>".$select_query;

	// To avoid OR while appending with the select query 
	$result = mysql_query($select_query);	
	$echo .= "<table> \n";
	
// Account Name on the top
	$echo .= "<tr  bgcolor=\"#999999\"><td colspan=\"6\">". $_GET[debit]." for the period from ".$_GET['dayBookDate']." to ".$_GET['dayBookDateTo']."</td>";

// Table Header 
	$echo .= "<tr bgcolor=\"#bbbbbb\"><td>Date</td><td>Account Name</td><td>Debit</td><td>Credit</td><td>Balance</td><td>Description</td></tr>";

// Opening Balance Work out
	
	$opening_balance_query = "select opening_balance,opening_balance_type,group_head from  ".$_COOKIE['username']."_account_name where acc_name='".$_GET['debit']."'";
	$opening_balance_result = mysql_query($opening_balance_query);
	if (mysql_num_rows($opening_balance_result) > 0){
		while ($row = mysql_fetch_array($opening_balance_result)) {
			if ($row[1] == "debit"){
	$echo .= "<tr bgcolor=\"#bbbbbb\"><td></td><td>Opening Balance</td><td>".$row[0]."</td><td></td><td>".$row[0]."</td><td>Description</td></tr>";
				$debit_total = $row[0];				
			} else if ($row[1] == "credit"){
	$echo .= "<tr bgcolor=\"#bbbbbb\"><td></td><td>Opening Balance</td><td></td><td>".$row[0]."</td><td>".$row[0]."</td><td>Description</td></tr>";
				$credit_total = $row[0];
			}
			//$opening_balance = $row[0];
		}	
	}
	
// Account 
	if(mysql_num_rows($result) != 0)
	while ($row = mysql_fetch_array($result)) {
		if($row[debit] == $_GET[debit]) {			
			$echo .= "<tr bgcolor=\"#cccccc\"> \n";
			$echo .= "<td> $row[dayBookDate] </td> \n";
			//$echo .= "<td> $row[debit] </td> \n";
			$echo .= "<td> $row[credit] </td> \n";
			//$echo .= "<td> $row[dayBookContra] </td> \n";
			$echo .= "<td> $row[dayBookAmount] </td> \n";
			$echo .= "<td  </td> \n";			
			$debit_total += $row[dayBookAmount];
			$balance = $debit_total - $credit_total;
			$echo .= "<td align=right> $balance	 </td> \n";
			$echo .= "<td> $row[description] </td> \n";
			$echo .= "</tr>";
			
		} else if(($row[credit] == $_GET[debit]) && ($row[dayBookContra] == "Y")) {
			$echo .= "<tr bgcolor=\"#dddddd\"> \n";
			$echo .= "<td> $row[dayBookDate] </td> \n";
			$echo .= "<td> $row[debit] </td> \n";
			//$echo .= "<td> $row[credit] </td> \n";
			//$echo .= "<td> $row[dayBookContra] </td> \n";
			$echo .= "<td>  </td> \n";
			$echo .= "<td> $row[dayBookAmount] </td> \n";			
			$credit_total += $row[dayBookAmount];
			$balance = $debit_total - $credit_total;
			$echo .= "<td align=right> $balance	 </td> \n";
			$echo .= "<td> $row[description] </td> \n";
			$echo .= "</tr>";
		}
	}
	$echo .= "<tr bgcolor=\"#bbbbbb\"><td> </td><td> Total </td><td align=\"right\">".$debit_total."</td><td align=\"right\">".$credit_total."</td><td align=\"right\">".$balance."</td><td>  </td></tr>";
	$echo .= "</table> \n";
}

function showTrialBalance() {
	global $echo,$debug;	
	$query = "SELECT name, sum(debitBalance) as debit, sum(creditBalance) as credit, sum(debitBalance)-sum(creditBalance) as balance from ";
	$query .= " (";
	$query .= "  SELECT name, sum( a ) as debitBalance , sum( b ) as creditBalance, sum(a)-sum(b) as balance FROM ";
	$query .= "  (";
	$query .= "   SELECT debit AS name, sum( daybookamount ) AS a, 0 AS b, 0 AS c FROM ".$_COOKIE['username']."_daybook ";
   	if($_GET[dayBookDate] != "") {
		$query .= " WHERE (dayBookDate between '".$_GET[dayBookDate]."' and '".$_GET[dayBookDateTo]."') ";			
	}
	$query .= " GROUP BY debit ";
	$query .= " UNION ";
	$query .= " SELECT credit AS name, 0 AS a, sum( daybookamount ) AS b, 0 AS c FROM ".$_COOKIE['username']."_daybook ";
	if($_GET[dayBookDate] != "") {
		$query .= " WHERE (dayBookDate between '".$_GET[dayBookDate]."' and '".$_GET[dayBookDateTo]."') ";			
	}
	$query .= "   GROUP BY credit ";
	$query .= "  ) AS TT1 GROUP BY TT1.name ";
    $query .= "  union";
	$query .= " SELECT name, a as debitBalance, b as creditBalance, sum(a)- sum(b) as balance from ";
	$query .= " (";
	$query .= " SELECT acc_name as name, opening_balance as a, 0 as b, 0 as balance from  ".$_COOKIE['username']."_account_name where opening_balance_type='debit' ";
	$query .= " union";
	$query .= " SELECT acc_name as name, 0 as a, opening_balance as b, 0 as balance from  ".$_COOKIE['username']."_account_name where opening_balance_type='credit'";
	$query .= " ) as TT2 group by TT2.name";
 	$query .= " ) as TT3 group by TT3.name ";

	$total_debit_balance = "";
	$total_credit_balance = "";
	$total_balance = "";
		
	$echo .= "<table>";
	
	$echo .= "<tr  bgcolor=\"#999999\"><td colspan=\"6\">Trail Balance for the period from ".$_GET['dayBookDate']." to ".$_GET['dayBookDateTo']."</td>";	
	$echo .= "<tr bgcolor=\"#aaaaaa\"><td>Account Name</td><td>Debit</td><td>Credit<td>Balance</td></tr>";
	if ($debug) echo $query; 	 
	
	$result = mysql_query($query);
	if(mysql_num_rows($result) > 0)
	{
		while($row = mysql_fetch_array($result)) {
			$echo .= "<tr bgcolor=\"#dddddd\"><td>".$row[0]."</td>";
			$echo .= "<td>".$row[1]."</td>";
			$echo .= "<td>".$row[2]."</td>";
			$echo .= "<td>".$row[3]."</td></tr>";
			$total_debit_balance += $row[1];
			$total_credit_balance += abs($row[2]);
			$total_balance += $row[3];
		} 
	}
	$echo .= "<tr bgcolor=\"#aaaaaa\"><td>Total</td><td>".$total_debit_balance."</td><td>".$total_credit_balance."</td><td>".$total_balance."</td></tr>";
	$echo .= "</table>";	
}

function showBalanceSheet() {    
	global $echo,$debug,$xml_file_name;
	$tb_balance = "";
	// Trading Account Work Out
	
	
	$echo .= "<table class='trading'>";	
	$echo .= "<tr bgcolor=\"#bbbbbb\"><td colspan=\"3\">Trading Account for the period from ".$_GET['dayBookDate']." to ".$_GET['dayBookDateTo']."</td></tr>";
	$echo .= "<tr bgcolor=\"#bbbbbb\"><td>Trading Account</td><td>Debit</td><td>Credit</td></tr>";
	$query = " select sum(opening_balance) AS opening_stock from ".$_COOKIE['username']."_account_name where act_group_head='STOCK' ";
	$result = mysql_query($query);
	if(mysql_num_rows($result) > 0)
	{
		while($row = mysql_fetch_array($result)) {
			$echo .= "<tr><td>Opening Stock</td><td bgcolor=\"#bbbbbb\">".$row[opening_stock]."</td><td></td></tr>";
			$tb_balance = $tb_balance + $row[opening_stock];
		}
		
	}
	$query = " select an.act_group_head as actgrouphead,sum(db.daybookamount) as debit, 0 as credit from ".$_COOKIE['username']."_daybook as db inner join  ".$_COOKIE['username']."_account_name as an on db.debit=an.acc_name where an.acc_head='tr' ";
   	if($_GET[dayBookDate] != "") {
		$query .= " and (dayBookDate between '".$_GET[dayBookDate]."' and '".$_GET[dayBookDateTo]."') ";			
	}	
	$query .= "	group by an.act_group_head";
	$query .= " union ";
	$query .= " select an.act_group_head as actgrouphead,0 as debit, sum(db.daybookamount) as credit from ".$_COOKIE['username']."_daybook as db inner join  ".$_COOKIE['username']."_account_name as an on db.credit=an.acc_name where an.acc_head='tr' ";
   	if($_GET[dayBookDate] != "") {
		$query .= " and (dayBookDate between '".$_GET[dayBookDate]."' and '".$_GET[dayBookDateTo]."') ";			
	}	
	$query .= " group by an.act_group_head";
	//echo $query; 	 
	$result = mysql_query($query);
	if(mysql_num_rows($result) > 0)
	{
		while($row = mysql_fetch_array($result)) {
			$echo .= "<tr><td bgcolor=\"#bbbbbb\">".$row[actgrouphead]."</td>";
			$echo .= "<td>".$row[debit]."</td>";
			$echo .= "<td>".$row[credit]."</td>";
			$tb_balance += $row[debit] - $row[credit];
		} 
	}

	$query = " select sum(closing_balance) AS closing_stock from ".$_COOKIE['username']."_account_name where act_group_head='STOCK' ";
	$result = mysql_query($query);
	if(mysql_num_rows($result) > 0)
	{
		while($row = mysql_fetch_array($result)) {
			$echo .= "<tr><td>Closing Stock</td><td></td><td bgcolor=\"#bbbbbb\">".$row[closing_stock]."</td></tr>";
			$tb_balance = $tb_balance - $row[closing_stock];
			$closing_stock = $row[closing_stock];
		}
	}
	
	
	//$tb_balance = $tb_balance + 10000000;	
	
	if ($tb_balance <= 0) {
		$tb_profit = $tb_balance;
		$echo .= "<tr bgcolor=\"#33dd00\"><td>Trading Profit</td>";
		$echo .= "<td>".abs($tb_profit)."</td>";
		$echo .= "<td>&nbsp;</td>";
		$echo .= "</tr>";
	} else {
		$tb_loss = $tb_balance;		
		$echo .= "<tr bgcolor=\"#dd3300\"><td>Trading Loss</td>";
		$echo .= "<td>&nbsp;</td>";
		$echo .= "<td>".$tb_loss."</td>";
		$echo .= "</tr>";
	}
	$echo .= "</table>";
	
	
	$pl_balance = "";
	
	// Profit and Loss Work Out
	$echo .= "<table class='pl'>";
	$echo .= "<tr bgcolor=\"#bbbbbb\"><td colspan=\"3\">Profit &amp; Loss for the period from ".$_GET['dayBookDate']." to ".$_GET['dayBookDateTo']."</td></tr>";	
	$echo .= "<tr bgcolor=\"#bbbbbb\"><td>Profit & Loss</td><td>Debit</td><td>Credit</td></tr>";
	$query = " select an.act_group_head as actgrouphead,sum(db.daybookamount) as debit, 0 as credit from ".$_COOKIE['username']."_daybook as db inner join  ".$_COOKIE['username']."_account_name as an on db.debit=an.acc_name where an.acc_head='pl' ";
   	if($_GET[dayBookDate] != "") {
		$query .= " and (dayBookDate between '".$_GET[dayBookDate]."' and '".$_GET[dayBookDateTo]."') ";			
	}

	$query .= " group by an.act_group_head";
	$query .= " union ";
	$query .= " select an.act_group_head as actgrouphead,0 as debit, sum(db.daybookamount) as credit from ".$_COOKIE['username']."_daybook as db inner join  ".$_COOKIE['username']."_account_name as an on db.credit=an.acc_name where an.acc_head='pl' ";
   	if($_GET[dayBookDate] != "") {
		$query .= " and (dayBookDate between '".$_GET[dayBookDate]."' and '".$_GET[dayBookDateTo]."') ";			
	}

	$query .= " group by an.act_group_head";

	//echo $query; 	 

	$result = mysql_query($query);
	if(mysql_num_rows($result) > 0)
	{
		while($row = mysql_fetch_array($result)) {
			$echo .= "<tr><td bgcolor=\"#bbbbbb\"><a href=\"javascript:postActionGroupName('".$row[actgrouphead]."','showGroup')\">".$row[actgrouphead]."</a></td>";
			$echo .= "<td>".$row[debit]."</td>";
			$echo .= "<td>".$row[credit]."</td>";
			$pl_balance += $row[debit] - $row[credit];
		} 
	}
	//$tb_balance = $tb_balance + 10000000;
	$pl_balance = $pl_balance + $tb_profit + $tb_loss;
	if ($pl_balance <= 0) {
		$pl_profit = $pl_balance;
		$echo .= "<tr bgcolor=\"#33dd00\"><td>Net Profit</td>";
		$echo .= "<td>".abs($pl_profit)."</td>";
		$echo .= "<td>&nbsp;</td>";
		$echo .= "</tr>";
	} else {
		$pl_loss = $pl_balance;		
		$echo .= "<tr bgcolor=\"#dd3300\"><td>Net Loss</td>";
		$echo .= "<td>&nbsp;</td>";
		$echo .= "<td>".$pl_loss."</td>";
		$echo .= "</tr>";
	}
	$echo .= "</table>";

	// Balance Sheet Work Out
	
	$query = "select actgrouphead, sum(debit) as debit, sum(credit) as credit, sum(debit-credit) as balance from";
            $query .=  " (";
			$query .=  "   select actgrouphead, sum(a) as debit, sum(b) as credit , sum(a-b) as balance from ";
			$query .=  "   (";
			$query .=  "    select an.act_group_head as actgrouphead,sum(db.daybookamount) as a, 0 as b, 0 as c from ".$_COOKIE['username']."_daybook as db inner join  ".$_COOKIE['username']."_account_name as an on db.debit=an.acc_name where an.acc_head='bs' ";
			if($_GET[dayBookDate] != "") {
				$query .= " and (dayBookDate between '".$_GET[dayBookDate]."' and '".$_GET[dayBookDateTo]."') ";			
			}	
			  $query .= " group by an.act_group_head";
			  $query .= "   union";
			  $query .= "	select an.act_group_head as actgrouphead,0 as a, sum(db.daybookamount) as b, 0 as c from ".$_COOKIE['username']."_daybook as db inner join  ".$_COOKIE['username']."_account_name as an on db.credit=an.acc_name where an.acc_head='bs' ";
			if($_GET[dayBookDate] != "") {
				$query .= " and (dayBookDate between '".$_GET[dayBookDate]."' and '".$_GET[dayBookDateTo]."') ";			
			}
			$query .=  " group by an.act_group_head";
			$query .=  "	  ) ";
			$query .=  "	  as TT1 group by TT1.actgrouphead";
			$query .=  "	  union";
			$query .=  "	  select actgrouphead, sum(debit) as debit, sum(credit) as credit, sum(debit-credit) as balance from ";
			$query .=  "	  (";
			$query .=  "	    select act_group_head as actgrouphead, sum(opening_balance) as debit, 0 as credit, 0 as balance from  ".$_COOKIE['username']."_account_name where opening_balance_type='debit'";
			$query .=  " group by act_group_head";
			$query .=  " union";
			$query .=  " select act_group_head as actgrouphead, 0 as debit, sum(opening_balance) as credit, 0 as balance from  ".$_COOKIE['username']."_account_name where opening_balance_type='credit' ";
			$query .=  " group by act_group_head";
			$query .=  "	  )";
			$query .=  "    as TT2 group by TT2.actgrouphead";
			$query .=  "	)";
			$query .=  "	as TT3 where TT3.actgrouphead <> 'STOCK' group by TT3.actgrouphead"; // Avoid showing Stock
	//echo $query ;
	$result = mysql_query ($query);
	
	
	$echo .= "<table class='bs'>";
	$echo .= "<tr bgcolor=\"#bbbbbb\"><td colspan=\"3\">Balance Sheet for the period from ".$_GET['dayBookDate']." to ".$_GET['dayBookDateTo']."</td></tr>";	
	$echo .= "<tr bgcolor=\"#bbbbbb\"> <td>Balance Sheet</td><td>Liability</td><td>Asset</td></tr>";
	
	if(mysql_num_rows($result) > 0)
	{
		while($row = mysql_fetch_array($result)) {
			if ($row[3] < 0){
				$echo .= "<tr><td bgcolor=\"#bbbbbb\"><a href=\"javascript:postActionGroupName('".$row[0]."','showGroup')\">".$row[0]."</td><td>".abs($row[3])."</td><td></td></tr>";				
				$liability_balance += abs($row[3]);
			} else {
				$echo .= "<tr><td bgcolor=\"#bbbbbb\"><a href=\"javascript:postActionGroupName('".$row[0]."','showGroup')\">".$row[0]."</td><td></td><td>".$row[3]."</td></tr>";				
				$asset_balance += abs($row[3]);
			}
		}
	}
	$asset_balance += $closing_stock;
	$echo .= "<tr bgcolor=\"#bbbbbb\"> <td>STOCK</td><td></td><td>".$closing_stock."</td></tr>";
	//$bs_total = $pl_profit-$pl_loss;
	$liability_balance += abs($pl_profit);
	$asset_balance += $pl_loss;
	$echo .= "<tr><td>Net Profit / Net Loss</td><td>".abs($pl_profit)."</td><td>".$pl_loss."</td></tr>";
	$echo .= "<tr><td>Total</td><td>".$liability_balance."</td><td>".$asset_balance."</td></tr>";
	$echo .= "</table>";
}

function calculateCashBalance($cash){
	$query = "SELECT name, sum(debitBalance) as debit, sum(creditBalance) as credit, sum(debitBalance)-sum(creditBalance) as balance from ( SELECT name, sum( a ) as debitBalance , sum( b ) as creditBalance, sum(a)-sum(b) as balance FROM ( SELECT debit AS name, sum( daybookamount ) AS a, 0 AS b, 0 AS c FROM ".$_COOKIE['username']."_daybook GROUP BY debit UNION SELECT credit AS name, 0 AS a, sum( daybookamount ) AS b, 0 AS c FROM ".$_COOKIE['username']."_daybook GROUP BY credit ) AS TT1 GROUP BY TT1.name union SELECT name, a as debitBalance, b as creditBalance, sum(a)- sum(b) as balance from ( SELECT acc_name as name, opening_balance as a, 0 as b, 0 as balance from  ".$_COOKIE['username']."_account_name where opening_balance_type='debit' union SELECT acc_name as name, 0 as a, opening_balance as b, 0 as balance from  ".$_COOKIE['username']."_account_name where opening_balance_type='credit' ) as TT2 group by TT2.name ) as TT3 where TT3.name='".$cash."' group by TT3.name";
	return $query;
}

function showGroup(){
	global $echo,$debug;	
	$query = "SELECT name, sum(debitBalance) as debit, sum(creditBalance) as credit, sum(debitBalance)-sum(creditBalance) as balance from ";
	$query .= " (";
	$query .= "  SELECT name, sum( a ) as debitBalance , sum( b ) as creditBalance, sum(a)-sum(b) as balance FROM ";
	$query .= "  (";
	$query .= "   SELECT debit AS name, sum( daybookamount ) AS a, 0 AS b, 0 AS c FROM ".$_COOKIE['username']."_daybook ";
   	if($_GET[dayBookDate] != "") {
		$query .= " WHERE (dayBookDate between '".$_GET[dayBookDate]."' and '".$_GET[dayBookDateTo]."') ";			
	}
	$query .= " GROUP BY debit ";
	$query .= " UNION ";
	$query .= " SELECT credit AS name, 0 AS a, sum( daybookamount ) AS b, 0 AS c FROM ".$_COOKIE['username']."_daybook ";
	if($_GET[dayBookDate] != "") {
		$query .= " WHERE (dayBookDate between '".$_GET[dayBookDate]."' and '".$_GET[dayBookDateTo]."') ";			
	}
	$query .= "   GROUP BY credit ";
	$query .= "  ) AS TT1 GROUP BY TT1.name ";
    $query .= "  union";
	$query .= " SELECT name, a as debitBalance, b as creditBalance, sum(a)- sum(b) as balance from ";
	$query .= " (";
	$query .= " SELECT acc_name as name, opening_balance as a, 0 as b, 0 as balance from  ".$_COOKIE['username']."_account_name where opening_balance_type='debit' ";
	$query .= " union";
	$query .= " SELECT acc_name as name, 0 as a, opening_balance as b, 0 as balance from  ".$_COOKIE['username']."_account_name where opening_balance_type='credit'";
	$query .= " ) as TT2 group by TT2.name";
 	$query .= " ) as TT3 inner join  ".$_COOKIE['username']."_account_name as TT4 on TT3.name=TT4.acc_name where TT4.act_group_head='".$_GET['groupName']."' group by TT3.name";
	
	$total_debit_balance = "";
	$total_credit_balance = "";
	$total_balance = "";
		
	$echo .= "<table>";	
	$echo .= "<tr  bgcolor=\"#999999\"><td colspan=\"6\">".$_GET['groupName']." for the period from ".$_GET['dayBookDate']." to ".$_GET['dayBookDateTo']."</td>";	
	$echo .= "<tr bgcolor=\"#aaaaaa\"><td>Account Name</td><td>Debit</td><td>Credit<td>Balance</td></tr>";
	if ($debug) echo $query; 	 
	
	$result = mysql_query($query);
	if(mysql_num_rows($result) > 0)
	{
		while($row = mysql_fetch_array($result)) {
			$echo .= "<tr bgcolor=\"#dddddd\"><td><a href=\"javascript:postActionGroupName('".$row[0]."','showLedger');\">".$row[0]."</a></td>";
			$echo .= "<td>".$row[1]."</td>";
			$echo .= "<td>".$row[2]."</td>";
			$echo .= "<td>".$row[3]."</td></tr>";
			$total_debit_balance += $row[1];
			$total_credit_balance += abs($row[2]);
			$total_balance += $row[3];
		} 
	}
	$echo .= "<tr bgcolor=\"#aaaaaa\"><td>Total</td><td>".$total_debit_balance."</td><td>".$total_credit_balance."</td><td>".$total_balance."</td></tr>";
	$echo .= "</table>";	 
}


echo $echo;



?>
Return current item: Simple Accounting System