From 4a4ad9da800763d6ac61c8458a3c8bbc53f2436f Mon Sep 17 00:00:00 2001 From: Joe Hunt Date: Fri, 24 Apr 2009 14:52:46 +0000 Subject: [PATCH] Added option to select how to present Balance Sheet and P&L Statement --- CHANGELOG.txt | 10 +++++- gl/includes/db/gl_db_account_types.inc | 19 +++++++++--- gl/manage/gl_account_classes.php | 20 +++++++++--- reporting/rep706.php | 43 ++++++++++++++------------ reporting/rep707.php | 27 +++++++++------- sql/alter2.2.sql | 2 ++ 6 files changed, 80 insertions(+), 41 deletions(-) diff --git a/CHANGELOG.txt b/CHANGELOG.txt index faa44470..38338c8c 100644 --- a/CHANGELOG.txt +++ b/CHANGELOG.txt @@ -18,7 +18,15 @@ Legend: - -> Removed ! -> Note $ -> Affected files - + +24-Apr-2009 Joe Hunt +! Added option to select how to present Balance Sheet and P&L Statement +$ /gl/manage/gl_account_classes.php + /gl/includes/db/gl_db_account_types.inc + /reporting/rep706.php + /reporting/rep707.php + /sql/alter2.2.sql + 22-Apr-2009 Janusz Dobrowolski + Added inactive records support. $ /sales/includes/db/credit_status_db.inc diff --git a/gl/includes/db/gl_db_account_types.inc b/gl/includes/db/gl_db_account_types.inc index 21a8c2ce..82de20a4 100644 --- a/gl/includes/db/gl_db_account_types.inc +++ b/gl/includes/db/gl_db_account_types.inc @@ -59,18 +59,18 @@ function delete_account_type($id) db_query($sql, "could not delete account type"); } -function add_account_class($id, $name, $balance) +function add_account_class($id, $name, $balance, $sign_conv) { - $sql = "INSERT INTO ".TB_PREF."chart_class (cid, class_name, balance_sheet) - VALUES ($id, ".db_escape($name).", $balance)"; + $sql = "INSERT INTO ".TB_PREF."chart_class (cid, class_name, balance_sheet, sign_convert) + VALUES ($id, ".db_escape($name).", $balance, $sign_conv)"; db_query($sql, "could not add account type"); } -function update_account_class($id, $name, $balance) +function update_account_class($id, $name, $balance, $sign_conv) { $sql = "UPDATE ".TB_PREF."chart_class SET class_name=".db_escape($name).", - balance_sheet=$balance WHERE cid = $id"; + balance_sheet=$balance, sign_convert=$sign_conv WHERE cid = $id"; db_query($sql, "could not update account type"); } @@ -101,7 +101,16 @@ function get_account_class_name($id) return $row[0]; } +function get_sign_convert($account_type) +{ + $sql = "SELECT sign_convert FROM ".TB_PREF."chart_class INNER JOIN ".TB_PREF."chart_types ON + ".TB_PREF."chart_class.cid = ".TB_PREF."chart_types.class_id WHERE ".TB_PREF."chart_types.id=$account_type"; + $result = db_query($sql, "could not get sign convert"); + $row = db_fetch_row($result); + return ($row[0]==1); +} + function delete_account_class($id) { $sql = "DELETE FROM ".TB_PREF."chart_class WHERE cid = $id"; diff --git a/gl/manage/gl_account_classes.php b/gl/manage/gl_account_classes.php index 47651ef9..f92d5b25 100644 --- a/gl/manage/gl_account_classes.php +++ b/gl/manage/gl_account_classes.php @@ -50,12 +50,12 @@ if ($Mode=='ADD_ITEM' || $Mode=='UPDATE_ITEM') if ($selected_id != -1) { - update_account_class($selected_id, $_POST['name'], $_POST['Balance']); + update_account_class($selected_id, $_POST['name'], $_POST['Balance'], $_POST['convert']); display_notification(_('Selected account class settings has been updated')); } else { - add_account_class($_POST['id'], $_POST['name'], $_POST['Balance']); + add_account_class($_POST['id'], $_POST['name'], $_POST['Balance'], $_POST['convert']); display_notification(_('New account class has been added')); } $Mode = 'RESET'; @@ -99,14 +99,14 @@ if ($Mode == 'Delete') if ($Mode == 'RESET') { $selected_id = -1; - $_POST['id'] = $_POST['name'] = $_POST['Balance'] = ''; + $_POST['id'] = $_POST['name'] = $_POST['Balance'] = $_POST['sign_convert'] = ''; } //----------------------------------------------------------------------------------- $result = get_account_classes(); start_form(); start_table($table_style); -$th = array(_("Class ID"), _("Class Name"), _("Balance Sheet"), "", ""); +$th = array(_("Class ID"), _("Class Name"), _("Balance Sheet"), _("Sign Convert"), "", ""); table_header($th); $k = 0; @@ -123,9 +123,18 @@ while ($myrow = db_fetch($result)) { $bs_text = _("Yes"); } + if ($myrow["sign_convert"] == 0) + { + $sc_text = _("No"); + } + else + { + $sc_text = _("Yes"); + } label_cell($myrow["cid"]); label_cell($myrow['class_name']); label_cell($bs_text); + label_cell($sc_text); edit_button_cell("Edit".$myrow["cid"], _("Edit")); delete_button_cell("Delete".$myrow["cid"], _("Delete")); end_row(); @@ -149,6 +158,7 @@ if ($selected_id != -1) $_POST['id'] = $myrow["cid"]; $_POST['name'] = $myrow["class_name"]; $_POST['Balance'] = $myrow["balance_sheet"]; + $_POST['convert'] = $myrow["sign_convert"]; hidden('selected_id', $selected_id); } hidden('id'); @@ -165,6 +175,8 @@ text_row_ex(_("Class Name:"), 'name', 50, 60); yesno_list_row(_("Balance Sheet:"), 'Balance', null, "", "", false); +yesno_list_row(_("Sign Convert (Balance Sheet/PL statement):"), 'convert', null, "", "", false); + end_table(1); submit_add_or_update_center($selected_id == -1, '', 'both'); diff --git a/reporting/rep706.php b/reporting/rep706.php index d045d558..cdf11df1 100644 --- a/reporting/rep706.php +++ b/reporting/rep706.php @@ -117,6 +117,7 @@ function print_balance_sheet() $assetsperiod = 0.0; $assetsclose = 0.0; $closeclass = false; + $convert = 1; $rep->NewLine(); $accounts = get_gl_accounts_all(1); @@ -129,7 +130,6 @@ function print_balance_sheet() if (!$prev_balance && !$curr_balance) continue; - if ($account['AccountClassName'] != $classname) { if ($classname != '') @@ -146,9 +146,9 @@ function print_balance_sheet() $rep->Line($rep->row); $rep->NewLine(); $rep->TextCol(0, 2, _('Total') . " " . $group); - $rep->AmountCol(2, 3, $totalopen, $dec); - $rep->AmountCol(3, 4, $totalperiod, $dec); - $rep->AmountCol(4, 5, $totalclose, $dec); + $rep->AmountCol(2, 3, $totalopen * $convert, $dec); + $rep->AmountCol(3, 4, $totalperiod * $convert, $dec); + $rep->AmountCol(4, 5, $totalclose * $convert, $dec); if ($graphics) { $pg->x[] = $group; @@ -163,9 +163,9 @@ function print_balance_sheet() $rep->NewLine(); $rep->Font('bold'); $rep->TextCol(0, 2, _('Total') . " " . $classname); - $rep->AmountCol(2, 3, $classopen, $dec); - $rep->AmountCol(3, 4, $classperiod, $dec); - $rep->AmountCol(4, 5, $classclose, $dec); + $rep->AmountCol(2, 3, $classopen * $convert, $dec); + $rep->AmountCol(3, 4, $classperiod * $convert, $dec); + $rep->AmountCol(4, 5, $classclose * $convert, $dec); $rep->Font(); $assetsopen += $classopen; $assetsperiod += $classperiod; @@ -183,6 +183,10 @@ function print_balance_sheet() $rep->NewLine(); } $group = $account['AccountTypeName']; + if (get_sign_convert($account['account_type'])) + $convert = -1; + else + $convert = 1; $rep->row -= 4; $rep->TextCol(0, 5, $account['AccountTypeName']); $rep->row -= 4; @@ -190,7 +194,6 @@ function print_balance_sheet() $rep->NewLine(); } $classname = $account['AccountClassName']; - $totalopen += $prev_balance; $totalperiod += $curr_balance; $totalclose = $totalopen + $totalperiod; @@ -200,9 +203,9 @@ function print_balance_sheet() $rep->TextCol(0, 1, $account['account_code']); $rep->TextCol(1, 2, $account['account_name']); - $rep->AmountCol(2, 3, $prev_balance, $dec); - $rep->AmountCol(3, 4, $curr_balance, $dec); - $rep->AmountCol(4, 5, $curr_balance + $prev_balance, $dec); + $rep->AmountCol(2, 3, $prev_balance * $convert, $dec); + $rep->AmountCol(3, 4, $curr_balance * $convert, $dec); + $rep->AmountCol(4, 5, ($curr_balance + $prev_balance) * $convert, $dec); $rep->NewLine(); @@ -227,9 +230,9 @@ function print_balance_sheet() $rep->Line($rep->row); $rep->NewLine(); $rep->TextCol(0, 2, _('Total') . " " . $group); - $rep->AmountCol(2, 3, $totalopen, $dec); - $rep->AmountCol(3, 4, $totalperiod, $dec); - $rep->AmountCol(4, 5, $totalclose, $dec); + $rep->AmountCol(2, 3, $totalopen * $convert, $dec); + $rep->AmountCol(3, 4, $totalperiod * $convert, $dec); + $rep->AmountCol(4, 5, $totalclose * $convert, $dec); if ($graphics) { $pg->x[] = $group; @@ -245,9 +248,9 @@ function print_balance_sheet() $rep->Line($rep->row); $rep->NewLine(); $rep->TextCol(0, 2, _('Calculated Return')); - $rep->AmountCol(2, 3, $calculateopen, $dec); - $rep->AmountCol(3, 4, $calculateperiod, $dec); - $rep->AmountCol(4, 5, $calculateclose, $dec); + $rep->AmountCol(2, 3, $calculateopen * $convert, $dec); + $rep->AmountCol(3, 4, $calculateperiod * $convert, $dec); + $rep->AmountCol(4, 5, $calculateclose * $convert, $dec); if ($graphics) { $pg->x[] = _('Calculated Return'); @@ -256,9 +259,9 @@ function print_balance_sheet() $rep->NewLine(2); $rep->Font('bold'); $rep->TextCol(0, 2, _('Total') . " " . $classname); - $rep->AmountCol(2, 3, -$assetsopen, $dec); - $rep->AmountCol(3, 4, -$assetsperiod, $dec); - $rep->AmountCol(4, 5, -$assetsclose, $dec); + $rep->AmountCol(2, 3, -$assetsopen * $convert, $dec); + $rep->AmountCol(3, 4, -$assetsperiod * $convert, $dec); + $rep->AmountCol(4, 5, -$assetsclose * $convert, $dec); $rep->Font(); $rep->NewLine(); } diff --git a/reporting/rep707.php b/reporting/rep707.php index 5dd3ff5c..7686b321 100644 --- a/reporting/rep707.php +++ b/reporting/rep707.php @@ -148,6 +148,7 @@ function print_profit_and_loss_statement() $classacc = 0.0; $salesper = 0.0; $salesacc = 0.0; + $convert = 1; $accounts = get_gl_accounts_all(0); @@ -178,8 +179,8 @@ function print_profit_and_loss_statement() $rep->Line($rep->row); $rep->NewLine(); $rep->TextCol(0, 2, _('Total') . " " . $group); - $rep->AmountCol(2, 3, $totalper, $dec); - $rep->AmountCol(3, 4, $totalacc, $dec); + $rep->AmountCol(2, 3, $totalper * $convert, $dec); + $rep->AmountCol(3, 4, $totalacc * $convert, $dec); $rep->AmountCol(4, 5, Achieve($totalper, $totalacc), $pdec); if ($graphics) { @@ -196,8 +197,8 @@ function print_profit_and_loss_statement() $rep->NewLine(); $rep->Font('bold'); $rep->TextCol(0, 2, _('Total') . " " . $classname); - $rep->AmountCol(2, 3, $classper, $dec); - $rep->AmountCol(3, 4, $classacc, $dec); + $rep->AmountCol(2, 3, $classper * $convert, $dec); + $rep->AmountCol(3, 4, $classacc * $convert, $dec); $rep->AmountCol(4, 5, Achieve($classper, $classacc), $pdec); $rep->Font(); $salesper += $classper; @@ -215,6 +216,10 @@ function print_profit_and_loss_statement() $rep->NewLine(); } $group = $account['AccountTypeName']; + if (get_sign_convert($account['account_type'])) + $convert = -1; + else + $convert = 1; $rep->row -= 4; $rep->TextCol(0, 5, $account['AccountTypeName']); $rep->row -= 4; @@ -229,11 +234,11 @@ function print_profit_and_loss_statement() $totalacc += $acc_balance; $classper += $per_balance; $classacc += $acc_balance; - $rep->TextCol(0, 1, $account['account_code']); + $rep->TextCol(0, 1, $account['account_code'].$convert); $rep->TextCol(1, 2, $account['account_name']); - $rep->AmountCol(2, 3, $per_balance, $dec); - $rep->AmountCol(3, 4, $acc_balance, $dec); + $rep->AmountCol(2, 3, $per_balance * $convert, $dec); + $rep->AmountCol(3, 4, $acc_balance * $convert, $dec); $rep->AmountCol(4, 5, Achieve($per_balance, $acc_balance), $pdec); $rep->NewLine(); @@ -259,8 +264,8 @@ function print_profit_and_loss_statement() $rep->Line($rep->row); $rep->NewLine(); $rep->TextCol(0, 2, _('Total') . " " . $group); - $rep->AmountCol(2, 3, $totalper, $dec); - $rep->AmountCol(3, 4, $totalacc, $dec); + $rep->AmountCol(2, 3, $totalper * $convert, $dec); + $rep->AmountCol(3, 4, $totalacc * $convert, $dec); $rep->AmountCol(4, 5, Achieve($totalper, $totalacc), $pdec); if ($graphics) { @@ -280,8 +285,8 @@ function print_profit_and_loss_statement() $rep->Font('bold'); $rep->TextCol(0, 2, _('Total') . " " . $classname); - $rep->AmountCol(2, 3, $classper, $dec); - $rep->AmountCol(3, 4, $classacc, $dec); + $rep->AmountCol(2, 3, $classper * $convert, $dec); + $rep->AmountCol(3, 4, $classacc * $convert, $dec); $rep->AmountCol(4, 5, Achieve($classper, $classacc), $pdec); $rep->NewLine(2); diff --git a/sql/alter2.2.sql b/sql/alter2.2.sql index 69acb54e..33df45e0 100644 --- a/sql/alter2.2.sql +++ b/sql/alter2.2.sql @@ -27,3 +27,5 @@ ALTER TABLE `0_users` ADD `sticky_doc_date` TINYINT(1) DEFAULT '0'; ALTER TABLE `0_debtors_master` MODIFY COLUMN `name` varchar(100) NOT NULL default ''; ALTER TABLE `0_cust_branch` ADD `inactive` tinyint(1) NOT NULL default '0'; +ALTER TABLE `0_chart_class` ADD `sign_convert` tinyint(1) NOT NULL default '0'; +UPDATE `0_chart_class` SET sign_convert=1 WHERE cid=3 OR cid=4 OR cid=5; -- 2.30.2