Topic: Customer Ledger as Inquiry
Here is the Code for Customer Ledger as Inquiry. For quick review of Customer's Ledger this is useful.
Here is the screen shot
<?php
/**********************************************************************
Copyright (C) FrontAccounting, LLC.
Released under the terms of the GNU General Public License, GPL,
as published by the Free Software Foundation, either version 3
of the License, or (at your option) any later version.
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
See the License here <http://www.gnu.org/licenses/gpl-3.0.html>.
***********************************************************************/
$page_security = 'SA_GLTRANSVIEW';
$path_to_root = "../..";
include_once($path_to_root . "/includes/session.inc");
include($path_to_root . "/includes/db_pager.inc");
include_once($path_to_root . "/admin/db/fiscalyears_db.inc");
include_once($path_to_root . "/includes/date_functions.inc");
include_once($path_to_root . "/includes/ui.inc");
include_once($path_to_root . "/includes/data_checks.inc");
include_once($path_to_root . "/modules/party_ledger/includes/addon.inc");// added by faisal
include_once($path_to_root . "/gl/includes/gl_db.inc");
$js = '';
set_focus('account');
if ($SysPrefs->use_popup_windows)
$js .= get_js_open_window(800, 500);
if (user_use_date_picker())
$js .= get_js_date_picker();
page(_($help_context = "General Ledger Inquiry"), false, false, '', $js);
//----------------------------------------------------------------------------------------------------
// Ajax updates
//
if (get_post('Show'))
{
$Ajax->activate('trans_tbl');
}
if (isset($_GET["account"]))
$_POST["account"] = $_GET["account"];
if (isset($_GET["TransFromDate"]))
$_POST["TransFromDate"] = $_GET["TransFromDate"];
if (isset($_GET["TransToDate"]))
$_POST["TransToDate"] = $_GET["TransToDate"];
if (isset($_GET["Dimension"]))
$_POST["Dimension"] = $_GET["Dimension"];
if (isset($_GET["Dimension2"]))
$_POST["Dimension2"] = $_GET["Dimension2"];
if (isset($_GET["amount_min"]))
$_POST["amount_min"] = $_GET["amount_min"];
if (isset($_GET["amount_max"]))
$_POST["amount_max"] = $_GET["amount_max"];
if (!isset($_POST["amount_min"]))
$_POST["amount_min"] = price_format(0);
if (!isset($_POST["amount_max"]))
$_POST["amount_max"] = price_format(0);
//----------------------------------------------------------------------------------------------------
function gl_inquiry_controls()
{
$dim = get_company_pref('use_dimension');
start_form();
start_table(TABLESTYLE_NOBORDER);
start_row();
//modified below by faisal
// gl_all_accounts_list_cells(_("Account:"), 'account', null, false, false, _("All Accounts"));
customer_list_cells(_('Select customer'), 'customer_id', null, false, true); //Edited by Phuong(Flat theme)
hidden('account', get_company_pref('debtors_act'));
//faisal modification ends
date_cells(_("from:"), 'TransFromDate', '', null, -user_transaction_days());
date_cells(_("to:"), 'TransToDate');
end_row();
end_table();
start_table(TABLESTYLE_NOBORDER);
start_row();
if ($dim >= 1)
dimensions_list_cells(_("Dimension")." 1:", 'Dimension', null, true, " ", false, 1);
if ($dim > 1)
dimensions_list_cells(_("Dimension")." 2:", 'Dimension2', null, true, " ", false, 2);
ref_cells(_("Memo:"), 'Memo', '',null, _('Enter memo fragment or leave empty'));
small_amount_cells(_("Amount min:"), 'amount_min', null, " ");
small_amount_cells(_("Amount max:"), 'amount_max', null, " ");
check_cells( _("By Entry Date:"), 'ByEntryDate', null);//added by faisal for Search by Entry Date
submit_cells('Show',_("Show"),'','', 'default');
end_row();
end_table();
echo '<hr>';
end_form();
}
//----------------------------------------------------------------------------------------------------
function show_results()
{
global $path_to_root, $systypes_array;
if (!isset($_POST["account"]))
$_POST["account"] = null;
$act_name = $_POST["account"] ? get_gl_account_name($_POST["account"]) : "";
$dim = get_company_pref('use_dimension');
/*Now get the transactions */
if (!isset($_POST['Dimension']))
$_POST['Dimension'] = 0;
if (!isset($_POST['Dimension2']))
$_POST['Dimension2'] = 0;
$result = get_gl_transactions($_POST['TransFromDate'], $_POST['TransToDate'], -1,
$_POST["account"], $_POST['Dimension'], $_POST['Dimension2'], null,
input_num('amount_min'), input_num('amount_max'),PT_CUSTOMER, $_POST['customer_id'], $_POST['Memo'], check_value('ByEntryDate')); //modified by faisal
$colspan = ($dim == 2 ? "8" : ($dim == 1 ? "5" : "4")); //modified by faisal to accommodate entrydate
if ($_POST["account"] != null)
display_heading($_POST["account"]. " ".$act_name);
// Only show balances if an account is specified AND we're not filtering by amounts
$show_balances = $_POST["account"] != null &&
input_num("amount_min") == 0 &&
input_num("amount_max") == 0;
start_table(TABLESTYLE);
$first_cols = array(_("Type"), _("#"),_("Reference"), _("Date"), _("Entry Date"));
if ($_POST["account"] == null)
$account_col = array(_("Account"));
else
$account_col = array();
if ($dim == 2)
$dim_cols = array(_("Dimension")." 1", _("Dimension")." 2");
elseif ($dim == 1)
$dim_cols = array(_("Dimension"));
else
$dim_cols = array();
if ($show_balances)
$remaining_cols = array(_("Person/Item"), _("Debit"), _("Credit"), _("Balance"), _("Memo"), "");
else
$remaining_cols = array(_("Person/Item"), _("Debit"), _("Credit"), _("Memo"), "");
$th = array_merge($first_cols, $account_col, $dim_cols, $remaining_cols);
table_header($th);
if ($_POST["account"] != null && is_account_balancesheet($_POST["account"]))
$begin = "";
else
{
$begin = get_fiscalyear_begin_for_date($_POST['TransFromDate']);
if (date1_greater_date2($begin, $_POST['TransFromDate']))
$begin = $_POST['TransFromDate'];
$begin = add_days($begin, -1);
}
$bfw = 0;
if ($show_balances) {
$bfw = bx_get_gl_balance_from_to($begin, $_POST['TransFromDate'], $_POST["account"], $_POST['Dimension'], $_POST['Dimension2'], $_POST['customer_id']); //modified by faisal
start_row("class='inquirybg'");
label_cell("<b>"._("Opening Balance")." - ".$_POST['TransFromDate']."</b>", "colspan=$colspan");
display_debit_or_credit_cells($bfw, true);
//below block added by faisal
if ($bfw>0)
{
$debit_total += $bfw;
}
else
{
$credit_total += ABS($bfw);
}
label_cell("");
label_cell("");
end_row();
}
$running_total = $bfw;
$j = 1;
$k = 0; //row colour counter
$debit_total = 0; //added by faisal
$credit_total =0;
while ($myrow = db_fetch($result))
{
alt_table_row_color($k);
$running_total += $myrow["amount"];
//below block added by faisal
if ($myrow["amount"]>0)
{
$debit_total += $myrow["amount"];
}
else
{
$credit_total += ABS($myrow["amount"]);
}
$trandate = sql2date($myrow["tran_date"]);
$entrydate = sql2date($myrow["stamp"]); //added by faisal
label_cell($systypes_array[$myrow["type"]]);
label_cell(get_gl_view_str($myrow["type"], $myrow["type_no"], $myrow["type_no"], true));
label_cell(get_trans_view_str($myrow["type"],$myrow["type_no"],$myrow['reference']));
label_cell($trandate);
label_cell($entrydate); //added by faisal
if ($_POST["account"] == null)
label_cell($myrow["account"] . ' ' . get_gl_account_name($myrow["account"]));
if ($dim >= 1)
label_cell(get_dimension_string($myrow['dimension_id'], true));
if ($dim > 1)
label_cell(get_dimension_string($myrow['dimension2_id'], true));
label_cell(payment_person_name($myrow["person_type_id"],$myrow["person_id"]));
display_debit_or_credit_cells($myrow["amount"]);
if ($show_balances)
amount_cell($running_total);
if ($myrow['memo_'] == "")
$myrow['memo_'] = get_comments_string($myrow['type'], $myrow['type_no']);
label_cell($myrow['memo_']);
if ($myrow["type"] == ST_JOURNAL)
echo "<td>" . trans_editor_link( $myrow["type"], $myrow["type_no"]) . "</td>";
else
label_cell("");
end_row();
$j++;
if ($j == 12)
{
$j = 1;
table_header($th);
}
}
//end of while loop
if ($show_balances) {
start_row("class='inquirybg'");
label_cell("<b>" . _("Ending Balance") ." - ".$_POST['TransToDate']. "</b>", "colspan=$colspan");
amount_cell($debit_total);
amount_cell($credit_total); //Added to display Debit and Creidt Total by Faisal
// display_debit_or_credit_cells($running_total, true); //commented by faisal
label_cell("");
label_cell("");
end_row();
}
end_table(2);
if (db_num_rows($result) == 0)
display_note(_("No general ledger transactions have been created for the specified criteria."), 0, 1);
}
//----------------------------------------------------------------------------------------------------
gl_inquiry_controls();
div_start('trans_tbl');
if (get_post('Show') || get_post('account'))
show_results();
div_end();
//----------------------------------------------------------------------------------------------------
end_page();This page uses following function
function bx_get_gl_balance_from_to($from_date, $to_date, $account, $dimension=0, $dimension2=0, $person_id=null)
{
$from = date2sql($from_date);
$to = date2sql($to_date);
$sql = "SELECT SUM(amount) FROM ".TB_PREF."gl_trans
WHERE account='$account'";
if ($from_date != "")
$sql .= " AND tran_date > '$from'";
if ($to_date != "")
$sql .= " AND tran_date < '$to'";
if ($dimension != 0)
$sql .= " AND dimension_id = ".($dimension<0 ? 0 : db_escape($dimension));
if ($dimension2 != 0)
$sql .= " AND dimension2_id = ".($dimension2<0 ? 0 : db_escape($dimension2));
if ($person_id)
$sql .= " AND person_id=".db_escape($person_id);
$result = db_query($sql, "The starting balance for account $account could not be calculated");
$row = db_fetch_row($result);
return $row[0];
}core Function get_gl_transactions is customized to view ledger ByEntryDate. Sometimes it is needed for Audit purpose. If you dont' want to update core then rename it and use for this Inquiry module.
function get_gl_transactions($from_date, $to_date, $trans_no=0,
$account=null, $dimension=0, $dimension2=0, $filter_type=null,
$amount_min=null, $amount_max=null,$person_type=null, $person_id=null, $memo = '', $byentrydate=0) //argument added by faisal
{
global $SysPrefs;
$from = date2sql($from_date);
$to = date2sql($to_date);
//added a.stamp by faisal
if ($byentrydate == false)
{
$sql = "SELECT gl.*, a.stamp, j.event_date, j.doc_date, a.gl_seq, u.user_id, st.supp_reference, gl.person_id subcode,
IFNULL(IFNULL(sup.supp_name, debt.name), bt.person_id) as person_name,
IFNULL(gl.person_id, IFNULL(sup.supplier_id, IFNULL(debt.debtor_no, bt.person_id))) as person_id,
IF(gl.person_id, gl.person_type_id, IF(sup.supplier_id,". PT_SUPPLIER . "," . "IF(debt.debtor_no," . PT_CUSTOMER . "," .
"IF(bt.person_id != '' AND !ISNULL(bt.person_id), bt.person_type_id, -1)))) as person_type_id,
IFNULL(st.tran_date, IFNULL(dt.tran_date, IFNULL(bt.trans_date, IFNULL(grn.delivery_date, gl.tran_date)))) as doc_date,
coa.account_name, ref.reference, IF(ISNULL(c.memo_), gl.memo_, CONCAT(gl.memo_,' ',c.memo_)) AS memo
FROM "
.TB_PREF."gl_trans gl
LEFT JOIN ".TB_PREF."voided v ON gl.type_no=v.id AND v.type=gl.type
LEFT JOIN ".TB_PREF."supp_trans st ON gl.type_no=st.trans_no AND st.type=gl.type AND (gl.type NOT IN (".ST_JOURNAL.",".ST_BULKDEPOSIT.") OR gl.person_id=st.supplier_id)
LEFT JOIN ".TB_PREF."grn_batch grn ON grn.id=gl.type_no AND gl.type=".ST_SUPPRECEIVE."
LEFT JOIN ".TB_PREF."debtor_trans dt ON gl.type_no=dt.trans_no AND dt.type=gl.type AND (gl.type NOT IN (".ST_JOURNAL.",".ST_BULKDEPOSIT.") OR gl.person_id=dt.debtor_no)
LEFT JOIN ".TB_PREF."suppliers sup ON st.supplier_id=sup.supplier_id
LEFT JOIN ".TB_PREF."cust_branch branch ON dt.branch_code=branch.branch_code
LEFT JOIN ".TB_PREF."debtors_master debt ON dt.debtor_no=debt.debtor_no
LEFT JOIN ".TB_PREF."bank_trans bt ON bt.type=gl.type AND bt.trans_no=gl.type_no AND bt.amount!=0
AND (bt.person_id != '' AND !ISNULL(bt.person_id))
LEFT JOIN ".TB_PREF."journal j ON j.type=gl.type AND j.trans_no=gl.type_no
LEFT JOIN ".TB_PREF."audit_trail a ON a.type=gl.type AND a.trans_no=gl.type_no AND NOT ISNULL(gl_seq)
LEFT JOIN ".TB_PREF."users u ON a.user=u.id
LEFT JOIN ".TB_PREF."comments c ON c.id=gl.type_no AND c.type=gl.type
LEFT JOIN ".TB_PREF."refs ref ON ref.type=gl.type AND ref.id=gl.type_no,"
.TB_PREF."chart_master coa
WHERE coa.account_code=gl.account
AND ISNULL(v.date_)
AND gl.tran_date >= '$from'
AND gl.tran_date <= '$to'
AND gl.amount <> 0";
if ($trans_no > 0)
$sql .= " AND gl.type_no LIKE ".db_escape('%'.$trans_no);;
if ($account != null)
$sql .= " AND gl.account = ".db_escape($account);
if ($dimension != 0)
$sql .= " AND gl.dimension_id = ".($dimension<0 ? 0 : db_escape($dimension));
if ($dimension2 != 0)
$sql .= " AND gl.dimension2_id = ".($dimension2<0 ? 0 : db_escape($dimension2));
if ($filter_type != null)
$sql .= " AND gl.type IN (" . $filter_type .")";
if ($amount_min != null)
$sql .= " AND ABS(gl.amount) >= ABS(".db_escape($amount_min).")";
if ($amount_max != null)
$sql .= " AND ABS(gl.amount) <= ABS(".db_escape($amount_max).")";
if ($memo)
$sql .= " AND (gl.memo_ LIKE ". db_escape("%$memo%") . " OR c.memo_ LIKE " . db_escape("%$memo%") . ")";
$sql .= " GROUP BY counter";
$sql .= " HAVING TRUE";
if ($person_type != 0)
$sql .= " AND person_type_id=".db_escape($person_type);
if ($person_id != 0)
$sql .= " AND person_id=".db_escape($person_id);
$sql .= " ORDER BY tran_date, counter";
}
else {
$sql = "SELECT gl.*, a.stamp, j.event_date, j.doc_date, a.gl_seq, u.user_id, st.supp_reference, gl.person_id subcode,
IFNULL(IFNULL(sup.supp_name, debt.name), bt.person_id) as person_name,
IFNULL(gl.person_id, IFNULL(sup.supplier_id, IFNULL(debt.debtor_no, bt.person_id))) as person_id,
IF(gl.person_id, gl.person_type_id, IF(sup.supplier_id,". PT_SUPPLIER . "," . "IF(debt.debtor_no," . PT_CUSTOMER . "," .
"IF(bt.person_id != '' AND !ISNULL(bt.person_id), bt.person_type_id, -1)))) as person_type_id,
IFNULL(st.tran_date, IFNULL(dt.tran_date, IFNULL(bt.trans_date, IFNULL(grn.delivery_date, gl.tran_date)))) as doc_date,
coa.account_name, ref.reference, IF(ISNULL(c.memo_), gl.memo_, CONCAT(gl.memo_,' ',c.memo_)) AS memo
FROM "
.TB_PREF."gl_trans gl
LEFT JOIN ".TB_PREF."voided v ON gl.type_no=v.id AND v.type=gl.type
LEFT JOIN ".TB_PREF."supp_trans st ON gl.type_no=st.trans_no AND st.type=gl.type AND (gl.type NOT IN (".ST_JOURNAL.",".ST_BULKDEPOSIT.") OR gl.person_id=st.supplier_id)
LEFT JOIN ".TB_PREF."grn_batch grn ON grn.id=gl.type_no AND gl.type=".ST_SUPPRECEIVE." AND gl.person_id=grn.supplier_id
LEFT JOIN ".TB_PREF."debtor_trans dt ON gl.type_no=dt.trans_no AND dt.type=gl.type AND (gl.type NOT IN (".ST_JOURNAL.",".ST_BULKDEPOSIT.") OR gl.person_id=dt.debtor_no)
LEFT JOIN ".TB_PREF."suppliers sup ON st.supplier_id=sup.supplier_id OR grn.supplier_id=sup.supplier_id
LEFT JOIN ".TB_PREF."cust_branch branch ON dt.branch_code=branch.branch_code
LEFT JOIN ".TB_PREF."debtors_master debt ON dt.debtor_no=debt.debtor_no
LEFT JOIN ".TB_PREF."bank_trans bt ON bt.type=gl.type AND bt.trans_no=gl.type_no AND bt.amount!=0
AND bt.person_type_id=gl.person_type_id AND bt.person_id=gl.person_id
LEFT JOIN ".TB_PREF."journal j ON j.type=gl.type AND j.trans_no=gl.type_no
LEFT JOIN ".TB_PREF."audit_trail a ON a.type=gl.type AND a.trans_no=gl.type_no AND NOT ISNULL(gl_seq)
LEFT JOIN ".TB_PREF."users u ON a.user=u.id
LEFT JOIN ".TB_PREF."comments c ON c.id=gl.type_no AND c.type=gl.type
LEFT JOIN ".TB_PREF."refs ref ON ref.type=gl.type AND ref.id=gl.type_no,"
.TB_PREF."chart_master coa
WHERE coa.account_code=gl.account
AND ISNULL(v.date_)
AND a.stamp >= '$from'
AND a.stamp <= '$to'
AND gl.amount <> 0";
if ($trans_no > 0)
$sql .= " AND gl.type_no LIKE ".db_escape('%'.$trans_no);;
if ($account != null)
$sql .= " AND gl.account = ".db_escape($account);
if ($dimension != 0)
$sql .= " AND gl.dimension_id = ".($dimension<0 ? 0 : db_escape($dimension));
if ($dimension2 != 0)
$sql .= " AND gl.dimension2_id = ".($dimension2<0 ? 0 : db_escape($dimension2));
if ($filter_type != null)
$sql .= " AND gl.type IN (" . $filter_type .")";
if ($amount_min != null)
$sql .= " AND ABS(gl.amount) >= ABS(".db_escape($amount_min).")";
if ($amount_max != null)
$sql .= " AND ABS(gl.amount) <= ABS(".db_escape($amount_max).")";
if ($memo)
$sql .= " AND (gl.memo_ LIKE ". db_escape("%$memo%") . " OR c.memo_ LIKE " . db_escape("%$memo%") . ")";
$sql .= " GROUP BY counter";
$sql .= " HAVING TRUE";
if ($person_type != 0)
$sql .= " AND person_type_id=".db_escape($person_type);
if ($person_id != 0)
$sql .= " AND person_id=".db_escape($person_id);
$sql .= " ORDER BY tran_date, counter";
return db_query($sql, "The transactions for could not be retrieved");
}
return db_query($sql, "The transactions for could not be retrieved");
}www.boxygen.pk