<?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> </td>";
$echo .= "</tr>";
} else {
$tb_loss = $tb_balance;
$echo .= "<tr bgcolor=\"#dd3300\"><td>Trading Loss</td>";
$echo .= "<td> </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 & 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> </td>";
$echo .= "</tr>";
} else {
$pl_loss = $pl_balance;
$echo .= "<tr bgcolor=\"#dd3300\"><td>Net Loss</td>";
$echo .= "<td> </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;
?>