From 9fc1f546b1ce0e6a674f595ee6dae616d94a5873 Mon Sep 17 00:00:00 2001 From: Joe Hunt Date: Fri, 7 Nov 2008 12:18:54 +0000 Subject: [PATCH] [0000081] Changed Trial Balance to show all debits and credits (option to only show balances) --- CHANGELOG.txt | 6 + gl/inquiry/gl_trial_balance.php | 128 ++++++++++----------- purchasing/includes/ui/invoice_ui.inc | 2 +- reporting/rep708.php | 154 +++++++++++++++----------- reporting/reports_main.php | 3 + 5 files changed, 164 insertions(+), 129 deletions(-) diff --git a/CHANGELOG.txt b/CHANGELOG.txt index 4786f688..deb6b823 100644 --- a/CHANGELOG.txt +++ b/CHANGELOG.txt @@ -19,6 +19,12 @@ Legend: ! -> Note $ -> Affected files +07-Nov-2008 Joe Hunt +! [0000081] Changed Trial Balance to show all debits and credits (option to only show balances) +$ /gl/inquiry/gl_trial_balance.php + /reporting/reports_main.php + /reporting/rep708.php + 03-Nov-2008 Janusz Dobrowolski # Fixed price priority in automatic calculations. $ sales/includes/sales_db.inc diff --git a/gl/inquiry/gl_trial_balance.php b/gl/inquiry/gl_trial_balance.php index bb52ae9a..4f44d5b3 100644 --- a/gl/inquiry/gl_trial_balance.php +++ b/gl/inquiry/gl_trial_balance.php @@ -35,6 +35,7 @@ function gl_inquiry_controls() date_cells(_("From:"), 'TransFromDate', '', null, -30); date_cells(_("To:"), 'TransToDate'); check_cells(_("No zero values"), 'NoZero', null); + check_cells(_("Only balances"), 'Balance', null); submit_cells('Show',_("Show"),'','', true); end_table(); @@ -43,33 +44,29 @@ function gl_inquiry_controls() //---------------------------------------------------------------------------------------------------- -function get_balance($account, $from, $to, $from_incl=true, $to_incl=true) { - - $sql = "SELECT SUM(amount) As TransactionSum FROM ".TB_PREF."gl_trans - WHERE account='$account'"; - - if ($from) - { - $from_date = date2sql($from); - if ($from_incl) - $sql .= " AND tran_date >= '$from_date'"; - else - $sql .= " AND tran_date > '$from_date'"; - } - - if ($to) - { - $to_date = date2sql($to); - if ($to_incl) - $sql .= " AND tran_date <= '$to_date' "; - else - $sql .= " AND tran_date < '$to_date' "; - } +function get_balance($account, $from, $to, $from_incl=true, $to_incl=true) +{ + $sql = "SELECT SUM(IF(amount >= 0, amount, 0)) as debit, SUM(IF(amount < 0, -amount, 0)) as credit, SUM(amount) as balance + FROM ".TB_PREF."gl_trans,".TB_PREF."chart_master,".TB_PREF."chart_types, ".TB_PREF."chart_class + WHERE ".TB_PREF."gl_trans.account=".TB_PREF."chart_master.account_code AND ".TB_PREF."chart_master.account_type=".TB_PREF."chart_types.id + AND ".TB_PREF."chart_types.class_id=".TB_PREF."chart_class.cid AND"; + + if ($account != null) + $sql .= " account='$account' AND"; + $from_date = date2sql($from); + if ($from_incl) + $sql .= " tran_date >= '$from_date' AND"; + else + $sql .= " tran_date > IF(".TB_PREF."chart_class.balance_sheet=1, '0000-00-00', '$from_date') AND"; + $to_date = date2sql($to); + if ($to_incl) + $sql .= " tran_date <= '$to_date' "; + else + $sql .= " tran_date < '$to_date' "; $result = db_query($sql,"No general ledger accounts were returned"); - $row = db_fetch_row($result); - return $row[0]; + return db_fetch($result); } //---------------------------------------------------------------------------------------------------- @@ -99,61 +96,64 @@ function display_trial_balance() $k = 0; - $totprevd = $totprevc = $totcurrd = $totcurrc = 0.0; $accounts = get_gl_accounts(); + $begin = begin_fiscalyear(); + if (date1_greater_date2($begin, $_POST['TransFromDate'])) + $begin = $_POST['TransFromDate']; + $begin = add_days($begin, -1); + while ($account = db_fetch($accounts)) { - if (is_account_balancesheet($account["account_code"])) - $begin = null; - else - { - $begin = begin_fiscalyear(); - if (date1_greater_date2($begin, $_POST['TransFromDate'])) - $begin = $_POST['TransFromDate']; - $begin = add_days($begin, -1); - } - $prev_balance = get_balance($account["account_code"], $begin, $_POST['TransFromDate'], false, false); - - $curr_balance = get_balance($account["account_code"], $_POST['TransFromDate'], $_POST['TransToDate']); - if (check_value("NoZero") && !$prev_balance && !$curr_balance) + $prev = get_balance($account["account_code"], $begin, $_POST['TransFromDate'], false, false); + $curr = get_balance($account["account_code"], $_POST['TransFromDate'], $_POST['TransToDate'], true, true); + $tot = get_balance($account["account_code"], $begin, $_POST['TransToDate'], false, true); + if (check_value("NoZero") && !$prev['balance'] && !$curr['balance'] && !$tot['balance']) continue; - if ($prev_balance >= 0.0) - $totprevd += $prev_balance; - else - $totprevc += $prev_balance; - if ($curr_balance >= 0.0) - $totcurrd += $curr_balance; - else - $totcurrc += $curr_balance; alt_table_row_color($k); $url = "" . $account["account_code"] . ""; label_cell($url); label_cell($account["account_name"]); - - display_debit_or_credit_cells($prev_balance); - display_debit_or_credit_cells($curr_balance); - display_debit_or_credit_cells($prev_balance + $curr_balance); + if (check_value('Balance')) + { + display_debit_or_credit_cells($prev['balance']); + display_debit_or_credit_cells($curr['balance']); + display_debit_or_credit_cells($tot['balance']); + } + else + { + amount_cell($prev['debit']); + amount_cell($prev['credit']); + amount_cell($curr['debit']); + amount_cell($curr['credit']); + amount_cell($tot['debit']); + amount_cell($tot['credit']); + } end_row(); } - start_row("class='inquirybg' style='font-weight:bold'"); - label_cell(_("Total") ." - ".$_POST['TransToDate'], "colspan=2"); - amount_cell($totprevd); - amount_cell(abs($totprevc)); - amount_cell($totcurrd); - amount_cell(abs($totcurrc)); - amount_cell($totprevd + $totcurrd); - amount_cell(abs($totprevc + $totcurrc)); - end_row(); - $totprev = $totprevd + $totprevc; - $totcurr = $totcurrd + $totcurrc; + + $prev = get_balance(null, $begin, $_POST['TransFromDate'], false, false); + $curr = get_balance(null, $_POST['TransFromDate'], $_POST['TransToDate'], true, true); + $tot = get_balance(null, $begin, $_POST['TransToDate'], false, true); + if (!check_value('Balance')) + { + start_row("class='inquirybg' style='font-weight:bold'"); + label_cell(_("Total") ." - ".$_POST['TransToDate'], "colspan=2"); + amount_cell($prev['debit']); + amount_cell($prev['credit']); + amount_cell($curr['debit']); + amount_cell($curr['credit']); + amount_cell($tot['debit']); + amount_cell($tot['credit']); + end_row(); + } start_row("class='inquirybg' style='font-weight:bold'"); label_cell(_("Ending Balance") ." - ".$_POST['TransToDate'], "colspan=2"); - display_debit_or_credit_cells($totprev); - display_debit_or_credit_cells($totcurr); - display_debit_or_credit_cells($totprev + $totcurr); + display_debit_or_credit_cells($prev['balance']); + display_debit_or_credit_cells($curr['balance']); + display_debit_or_credit_cells($tot['balance']); end_row(); end_table(1); diff --git a/purchasing/includes/ui/invoice_ui.inc b/purchasing/includes/ui/invoice_ui.inc index cea7a10c..d72b0dcb 100644 --- a/purchasing/includes/ui/invoice_ui.inc +++ b/purchasing/includes/ui/invoice_ui.inc @@ -141,7 +141,7 @@ function invoice_totals(&$supp_trans) label_row(_("Invoice Total:"), $display_total, "align=right style='font-weight:bold;'", "align=right style='font-weight:bold;'"); else label_row(_("Credit Note Total"), - $display_total, "align=right style='font-weight:bold;color:red;'", "nowrap align=right style='font-weight:bold;'"); + $display_total, "align=right style='font-weight:bold;color:red;'", "nowrap align=right style='font-weight:bold;color:red;'"); end_table(); br(1); diff --git a/reporting/rep708.php b/reporting/rep708.php index 6d72454b..2c8ebb32 100644 --- a/reporting/rep708.php +++ b/reporting/rep708.php @@ -19,6 +19,36 @@ include_once($path_to_root . "gl/includes/gl_db.inc"); // trial_inquiry_controls(); print_trial_balance(); +//---------------------------------------------------------------------------------------------------- +function get_balance($account, $dimension, $dimension2, $from, $to, $from_incl=true, $to_incl=true) +{ + $sql = "SELECT SUM(IF(amount >= 0, amount, 0)) as debit, SUM(IF(amount < 0, -amount, 0)) as credit, SUM(amount) as balance + FROM ".TB_PREF."gl_trans,".TB_PREF."chart_master,".TB_PREF."chart_types, ".TB_PREF."chart_class + WHERE ".TB_PREF."gl_trans.account=".TB_PREF."chart_master.account_code AND ".TB_PREF."chart_master.account_type=".TB_PREF."chart_types.id + AND ".TB_PREF."chart_types.class_id=".TB_PREF."chart_class.cid AND"; + + if ($account != null) + $sql .= " account='$account' AND"; + if ($dimension > 0) + $sql .= " dimension_id=$dimension AND"; + if ($dimension2 > 0) + $sql .= " dimension2_id=$dimension2 AND"; + $from_date = date2sql($from); + if ($from_incl) + $sql .= " tran_date >= '$from_date' AND"; + else + $sql .= " tran_date > IF(".TB_PREF."chart_class.balance_sheet=1, '0000-00-00', '$from_date') AND"; + $to_date = date2sql($to); + if ($to_incl) + $sql .= " tran_date <= '$to_date' "; + else + $sql .= " tran_date < '$to_date' "; + + $result = db_query($sql,"No general ledger accounts were returned"); + + return db_fetch($result); +} + //---------------------------------------------------------------------------------------------------- function print_trial_balance() @@ -32,20 +62,21 @@ function print_trial_balance() $from = $_POST['PARAM_0']; $to = $_POST['PARAM_1']; $zero = $_POST['PARAM_2']; + $balances = $_POST['PARAM_3']; if ($dim == 2) { - $dimension = $_POST['PARAM_3']; - $dimension2 = $_POST['PARAM_4']; - $comments = $_POST['PARAM_5']; + $dimension = $_POST['PARAM_4']; + $dimension2 = $_POST['PARAM_5']; + $comments = $_POST['PARAM_6']; } else if ($dim == 1) { - $dimension = $_POST['PARAM_3']; - $comments = $_POST['PARAM_4']; + $dimension = $_POST['PARAM_4']; + $comments = $_POST['PARAM_5']; } else { - $comments = $_POST['PARAM_3']; + $comments = $_POST['PARAM_4']; } $dec = user_price_dec(); @@ -91,57 +122,47 @@ function print_trial_balance() $rep->Font(); $rep->Info($params, $cols, $headers, $aligns, $cols2, $headers2, $aligns2); $rep->Header(); - $totprevd = $totprevc = $totcurrd = $totcurrc = 0.0; $accounts = get_gl_accounts(); + $begin = begin_fiscalyear(); + if (date1_greater_date2($begin, $from)) + $begin = $from; + $begin = add_days($begin, -1); while ($account=db_fetch($accounts)) { + $prev = get_balance($account["account_code"], $dimension, $dimension2, $begin, $from, false, false); + $curr = get_balance($account["account_code"], $dimension, $dimension2, $from, $to, true, true); + $tot = get_balance($account["account_code"], $dimension, $dimension2, $begin, $to, false, true); - if (is_account_balancesheet($account["account_code"])) - $begin = ""; - else - { - $begin = begin_fiscalyear(); - if (date1_greater_date2($begin, $from)) - $begin = $from; - $begin = add_days($begin, -1); - } - - $prev_balance = get_gl_balance_from_to($begin, $from, $account["account_code"], $dimension, $dimension2); - - $curr_balance = get_gl_trans_from_to($from, $to, $account["account_code"], $dimension, $dimension2); - - if ($zero == 0 && !$prev_balance && !$curr_balance) + if ($zero == 0 && !$prev['balance'] && !$curr['balance'] && !$tot['balance']) continue; $rep->TextCol(0, 1, $account['account_code']); $rep->TextCol(1, 2, $account['account_name']); - - if ($prev_balance >= 0.0) - { - $totprevd += $prev_balance; - $rep->TextCol(2, 3, number_format2(abs($prev_balance), $dec)); - } - else - { - $totprevc += $prev_balance; - $rep->TextCol(3, 4, number_format2(abs($prev_balance), $dec)); - } - if ($curr_balance >= 0.0) + if ($balances != 0) { - $totcurrd += $curr_balance; - $rep->TextCol(4, 5, number_format2(abs($curr_balance), $dec)); - } + if ($prev['balance'] >= 0.0) + $rep->TextCol(2, 3, number_format2($prev['balance'], $dec)); + else + $rep->TextCol(3, 4, number_format2(abs($prev['balance']), $dec)); + if ($curr['balance'] >= 0.0) + $rep->TextCol(4, 5, number_format2($curr['balance'], $dec)); + else + $rep->TextCol(5, 6, number_format2(abs($curr['balance']), $dec)); + if ($tot['balance'] >= 0.0) + $rep->TextCol(6, 7, number_format2($tot['balance'], $dec)); + else + $rep->TextCol(7, 8, number_format2(abs($tot['balance']), $dec)); + } else { - $totcurrc += $curr_balance; - $rep->TextCol(5, 6, number_format2(abs($curr_balance), $dec)); + $rep->TextCol(2, 3, number_format2($prev['debit'], $dec)); + $rep->TextCol(3, 4, number_format2($prev['credit'], $dec)); + $rep->TextCol(4, 5, number_format2($curr['debit'], $dec)); + $rep->TextCol(5, 6, number_format2($curr['credit'], $dec)); + $rep->TextCol(6, 7, number_format2($tot['debit'], $dec)); + $rep->TextCol(7, 8, number_format2($tot['credit'], $dec)); } - if ($curr_balance + $prev_balance >= 0.0) - $rep->TextCol(6, 7, number_format2(abs($curr_balance + $prev_balance), $dec)); - else - $rep->TextCol(7, 8, number_format2(abs($curr_balance + $prev_balance), $dec)); - $rep->NewLine(); if ($rep->row < $rep->bottomMargin + $rep->lineHeight) @@ -153,31 +174,36 @@ function print_trial_balance() $rep->Line($rep->row); $rep->NewLine(); $rep->Font('bold'); - $rep->TextCol(0, 2, _("Total")); - - $rep->TextCol(2, 3, number_format2(abs($totprevd), $dec)); - $rep->TextCol(3, 4, number_format2(abs($totprevc), $dec)); - $rep->TextCol(4, 5, number_format2(abs($totcurrd), $dec)); - $rep->TextCol(5, 6, number_format2(abs($totcurrc), $dec)); - $rep->TextCol(6, 7, number_format2(abs($totcurrd + $totprevd), $dec)); - $rep->TextCol(7, 8, number_format2(abs($totcurrc + $totprevc), $dec)); - $rep->NewLine(); - $totprev = $totprevd + $totprevc; - $totcurr = $totcurrd + $totcurrc; + + $prev = get_balance(null, $dimension, $dimension2, $begin, $from, false, false); + $curr = get_balance(null, $dimension, $dimension2, $from, $to, true, true); + $tot = get_balance(null, $dimension, $dimension2, $begin, $to, false, true); + + if ($balances == 0) + { + $rep->TextCol(0, 2, _("Total")); + $rep->TextCol(2, 3, number_format2($prev['debit'], $dec)); + $rep->TextCol(3, 4, number_format2($prev['credit'], $dec)); + $rep->TextCol(4, 5, number_format2($curr['debit'], $dec)); + $rep->TextCol(5, 6, number_format2($curr['credit'], $dec)); + $rep->TextCol(6, 7, number_format2($tot['debit'], $dec)); + $rep->TextCol(7, 8, number_format2($tot['credit'], $dec)); + $rep->NewLine(); + } $rep->TextCol(0, 2, _("Ending Balance")); - if ($totprev >= 0.0) - $rep->TextCol(2, 3, number_format2(abs($totprev), $dec)); + if ($prev['balance'] >= 0.0) + $rep->TextCol(2, 3, number_format2($prev['balance'], $dec)); else - $rep->TextCol(3, 4, number_format2(abs($totprev), $dec)); - if ($totcurr >= 0.0) - $rep->TextCol(4, 5, number_format2(abs($totcurr), $dec)); + $rep->TextCol(3, 4, number_format2(abs($prev['balance']), $dec)); + if ($curr['balance'] >= 0.0) + $rep->TextCol(4, 5, number_format2($curr['balance'], $dec)); else - $rep->TextCol(5, 6, number_format2(abs($totcurr), $dec)); - if ($totcurr + $totprev >= 0.0) - $rep->TextCol(6, 7, number_format2(abs($totcurr + $totprev), $dec)); + $rep->TextCol(5, 6, number_format2(abs($curr['balance']), $dec)); + if ($tot['balance'] >= 0.0) + $rep->TextCol(6, 7, number_format2($tot['balance'], $dec)); else - $rep->TextCol(7, 8, number_format2(abs($totcurr + $totprev), $dec)); + $rep->TextCol(7, 8, number_format2(abs($tot['balance']), $dec)); $rep->Line($rep->row - 6); diff --git a/reporting/reports_main.php b/reporting/reports_main.php index 580f3660..3b03af35 100644 --- a/reporting/reports_main.php +++ b/reporting/reports_main.php @@ -203,6 +203,7 @@ if ($dim == 2) array( new ReportParam(_('Start Date'),'DATEBEGINM'), new ReportParam(_('End Date'),'DATEENDM'), new ReportParam(_('Zero values'),'YES_NO'), + new ReportParam(_('Only balances'),'YES_NO'), new ReportParam(_('Dimension')." 1", 'DIMENSIONS1'), new ReportParam(_('Dimension')." 2", 'DIMENSIONS2'), new ReportParam(_('Comments'),'TEXTBOX'))); @@ -237,6 +238,7 @@ else if ($dim == 1) array( new ReportParam(_('Start Date'),'DATEBEGINM'), new ReportParam(_('End Date'),'DATEENDM'), new ReportParam(_('Zero values'),'YES_NO'), + new ReportParam(_('Only balances'),'YES_NO'), new ReportParam(_('Dimension'), 'DIMENSIONS1'), new ReportParam(_('Comments'),'TEXTBOX'))); } @@ -266,6 +268,7 @@ else array( new ReportParam(_('Start Date'),'DATEBEGINM'), new ReportParam(_('End Date'),'DATEENDM'), new ReportParam(_('Zero values'),'YES_NO'), + new ReportParam(_('Only balances'),'YES_NO'), new ReportParam(_('Comments'),'TEXTBOX'))); } $reports->addReport(_('General Ledger'),709,_('Tax Report'), -- 2.30.2