From 7fae690528df9b851fba309b71bf7c3b961c9dd9 Mon Sep 17 00:00:00 2001 From: Joe Hunt Date: Fri, 7 May 2010 16:31:09 +0000 Subject: [PATCH] Added Account tags selection in Annual Expense Breakdown, Balanse Sheet and Profit and Loss Sheet --- CHANGELOG.txt | 10 ++++++ admin/db/tags_db.inc | 30 ++++++++++++++++++ reporting/includes/reports_classes.inc | 3 +- reporting/rep705.php | 35 +++++++++++++------- reporting/rep706.php | 41 +++++++++++++++--------- reporting/rep707.php | 44 ++++++++++++++++---------- reporting/reports_main.php | 9 ++++++ 7 files changed, 128 insertions(+), 44 deletions(-) diff --git a/CHANGELOG.txt b/CHANGELOG.txt index bd8af76f..c0832085 100644 --- a/CHANGELOG.txt +++ b/CHANGELOG.txt @@ -19,6 +19,16 @@ Legend: ! -> Note $ -> Affected files +07-May-2010 Joe Hunt ++ Added Account tags selection in Annual Expense Breakdown, Balanse Sheet + and Profit and Loss Sheet +$ /admin/db/tags_db.inc + /reporting/rep705.php + /reporting/rep706.php + /reporting/rep707.php + /reporting/reports_main.php + /reporting/includes/reports_classes.inc + 07-May-2010 Janusz Dobrowolski # Fixed bug in array_selector for multiply lists $ /includes/ui/ui_lists.inc diff --git a/admin/db/tags_db.inc b/admin/db/tags_db.inc index 6b6aec4d..2ca09804 100644 --- a/admin/db/tags_db.inc +++ b/admin/db/tags_db.inc @@ -80,6 +80,22 @@ function get_tag_name($id) return $row[0]; } +//---------------------------------------------------------------------------------------------------- +function get_tag_names($tags) +{ + $str = ""; + if ($tags == -1) + return $str; + foreach($tags as $id) + { + $tag = get_tag_name($id); + if ($str == "") + $str .= $tag; + else + $str .= ", ".$tag; + } + return $str; +} //-------------------------------------------------------------------------------------- function get_tag_description($id) @@ -190,4 +206,18 @@ function get_tags_associated_with_record($type, $recordid) //-------------------------------------------------------------------------------------- +function is_record_in_tags($tags, $type, $recordid) +{ + foreach($tags as $id) + { + $sql = "SELECT ta.record_id FROM ".TB_PREF."tag_associations AS ta + INNER JOIN ".TB_PREF."tags AS tags ON tags.id = ta.tag_id + WHERE tags.type = $type AND tags.id = $id AND ta.record_id = ".db_escape($recordid); + $res = db_query($sql, "could not get tags associations for record"); + if (db_num_rows($res) == 0) + return false; + } + return true; +} + ?> \ No newline at end of file diff --git a/reporting/includes/reports_classes.inc b/reporting/includes/reports_classes.inc index 9de492e2..51012b11 100644 --- a/reporting/includes/reports_classes.inc +++ b/reporting/includes/reports_classes.inc @@ -395,7 +395,8 @@ class BoxReports $tag_type = TAG_ACCOUNT; else $tag_type = TAG_DIMENSION; - return tag_list($name, 5, $tag_type, true, _("No tags")); + //return tag_list($name, 5, $tag_type, true, false, _("No tags")); + return tag_list($name, 5, $tag_type, true); } return ''; diff --git a/reporting/rep705.php b/reporting/rep705.php index b2aaf68e..e035869f 100644 --- a/reporting/rep705.php +++ b/reporting/rep705.php @@ -22,6 +22,7 @@ include_once($path_to_root . "/includes/session.inc"); include_once($path_to_root . "/includes/date_functions.inc"); include_once($path_to_root . "/includes/data_checks.inc"); include_once($path_to_root . "/gl/includes/gl_db.inc"); +include_once($path_to_root . "/admin/db/tags_db.inc"); //---------------------------------------------------------------------------------------------------- @@ -71,7 +72,7 @@ function getPeriods($yr, $mo, $account, $dimension, $dimension2) //---------------------------------------------------------------------------------------------------- -function display_type ($type, $typename, $yr, $mo, $convert, &$dec, &$rep, $dimension, $dimension2) +function display_type ($type, $typename, $yr, $mo, $convert, &$dec, &$rep, $dimension, $dimension2, $tags) { $ctotal = array(1 => 0,0,0,0,0,0,0,0,0,0,0,0,0,0); $total = array(1 => 0,0,0,0,0,0,0,0,0,0,0,0,0,0); @@ -83,6 +84,11 @@ function display_type ($type, $typename, $yr, $mo, $convert, &$dec, &$rep, $dime $result = get_gl_accounts(null, null, $type); while ($account=db_fetch($result)) { + if ($tags != -1) + { + if (!is_record_in_tags($tags, TAG_ACCOUNT, $account['account_code'])) + continue; + } $bal = getPeriods($yr, $mo, $account["account_code"], $dimension, $dimension2); if (!$bal['per01'] && !$bal['per02'] && !$bal['per03'] && !$bal['per04'] && !$bal['per05'] && !$bal['per06'] && !$bal['per07'] && !$bal['per08'] && !$bal['per09'] && !$bal['per10'] && @@ -167,21 +173,24 @@ function print_annual_expense_breakdown() $year = $_POST['PARAM_0']; $dimension = $_POST['PARAM_1']; $dimension2 = $_POST['PARAM_2']; - $comments = $_POST['PARAM_3']; - $destination = $_POST['PARAM_4']; + $tags = (isset($_POST['PARAM_3']) ? $_POST['PARAM_3'] : -1); + $comments = $_POST['PARAM_4']; + $destination = $_POST['PARAM_5']; } else if ($dim == 1) { $year = $_POST['PARAM_0']; $dimension = $_POST['PARAM_1']; - $comments = $_POST['PARAM_2']; - $destination = $_POST['PARAM_3']; + $tags = (isset($_POST['PARAM_2']) ? $_POST['PARAM_2'] : -1); + $comments = $_POST['PARAM_3']; + $destination = $_POST['PARAM_4']; } else { $year = $_POST['PARAM_0']; - $comments = $_POST['PARAM_1']; - $destination = $_POST['PARAM_2']; + $tags = (isset($_POST['PARAM_1']) ? $_POST['PARAM_1'] : -1); + $comments = $_POST['PARAM_2']; + $destination = $_POST['PARAM_3']; } if ($destination) include_once($path_to_root . "/reporting/includes/excel_report.inc"); @@ -237,7 +246,8 @@ function print_annual_expense_breakdown() 'from' => get_dimension_string($dimension), 'to' => ''), 3 => array('text' => _("Dimension")." 2", 'from' => get_dimension_string($dimension2), 'to' => ''), - 4 => array('text' => _('Info'), 'from' => _('Amounts in thousands'), + 4 => array('text' => _('Tags'), 'from' => get_tag_names($tags), 'to' => ''), + 5 => array('text' => _('Info'), 'from' => _('Amounts in thousands'), 'to' => '')); } else if ($dim == 1) @@ -247,7 +257,8 @@ function print_annual_expense_breakdown() 'from' => $year, 'to' => ''), 2 => array('text' => _('Dimension'), 'from' => get_dimension_string($dimension), 'to' => ''), - 3 => array('text' => _('Info'), 'from' => _('Amounts in thousands'), + 3 => array('text' => _('Tags'), 'from' => get_tag_names($tags), 'to' => ''), + 4 => array('text' => _('Info'), 'from' => _('Amounts in thousands'), 'to' => '')); } else @@ -255,7 +266,8 @@ function print_annual_expense_breakdown() $params = array( 0 => $comments, 1 => array('text' => _("Year"), 'from' => $year, 'to' => ''), - 2 => array('text' => _('Info'), 'from' => _('Amounts in thousands'), + 2 => array('text' => _('Tags'), 'from' => get_tag_names($tags), 'to' => ''), + 3 => array('text' => _('Info'), 'from' => _('Amounts in thousands'), 'to' => '')); } @@ -283,7 +295,8 @@ function print_annual_expense_breakdown() $typeresult = get_account_types(false, $class['cid'], -1); while ($accounttype=db_fetch($typeresult)) { - $classtotal = display_type($accounttype["id"], $accounttype["name"], $yr, $mo, $convert, $dec, $rep, $dimension, $dimension2); + $classtotal = display_type($accounttype["id"], $accounttype["name"], $yr, $mo, $convert, $dec, $rep, $dimension, + $dimension2, $tags); for ($i = 1; $i <= 12; $i++) $ctotal[$i] += $classtotal[$i]; } diff --git a/reporting/rep706.php b/reporting/rep706.php index 6f36b2f3..2fa624a6 100644 --- a/reporting/rep706.php +++ b/reporting/rep706.php @@ -22,16 +22,16 @@ include_once($path_to_root . "/includes/session.inc"); include_once($path_to_root . "/includes/date_functions.inc"); include_once($path_to_root . "/includes/data_checks.inc"); include_once($path_to_root . "/gl/includes/gl_db.inc"); +include_once($path_to_root . "/admin/db/tags_db.inc"); //---------------------------------------------------------------------------------------------------- -function display_type ($type, $typename, $from, $to, $convert, &$dec, &$rep, $dimension, $dimension2, &$pg, $graphics) +function display_type ($type, $typename, $from, $to, $convert, &$dec, &$rep, $dimension, $dimension2, $tags, &$pg, $graphics) { $code_open_balance = 0; $code_period_balance = 0; $open_balance_total = 0; $period_balance_total = 0; - unset($totals_arr); $totals_arr = array(); $printtitle = 0; //Flag for printing type name @@ -40,6 +40,11 @@ function display_type ($type, $typename, $from, $to, $convert, &$dec, &$rep, $di $result = get_gl_accounts(null, null, $type); while ($account=db_fetch($result)) { + if ($tags != -1) + { + if (!is_record_in_tags($tags, TAG_ACCOUNT, $account['account_code'])) + continue; + } $prev_balance = get_gl_balance_from_to("", $from, $account["account_code"], $dimension, $dimension2); $curr_balance = get_gl_trans_from_to($from, $to, $account["account_code"], $dimension, $dimension2); @@ -133,26 +138,29 @@ function print_balance_sheet() { $dimension = $_POST['PARAM_2']; $dimension2 = $_POST['PARAM_3']; + $tags = (isset($_POST['PARAM_4']) ? $_POST['PARAM_4'] : -1); + $decimals = $_POST['PARAM_5']; + $graphics = $_POST['PARAM_6']; + $comments = $_POST['PARAM_7']; + $destination = $_POST['PARAM_8']; + } + else if ($dim == 1) + { + $dimension = $_POST['PARAM_2']; + $tags = (isset($_POST['PARAM_3']) ? $_POST['PARAM_3'] : -1); $decimals = $_POST['PARAM_4']; $graphics = $_POST['PARAM_5']; $comments = $_POST['PARAM_6']; $destination = $_POST['PARAM_7']; } - else if ($dim == 1) + else { - $dimension = $_POST['PARAM_2']; + $tags = (isset($_POST['PARAM_2']) ? $_POST['PARAM_2'] : -1); $decimals = $_POST['PARAM_3']; $graphics = $_POST['PARAM_4']; $comments = $_POST['PARAM_5']; $destination = $_POST['PARAM_6']; } - else - { - $decimals = $_POST['PARAM_2']; - $graphics = $_POST['PARAM_3']; - $comments = $_POST['PARAM_4']; - $destination = $_POST['PARAM_5']; - } if ($destination) include_once($path_to_root . "/reporting/includes/excel_report.inc"); else @@ -182,19 +190,22 @@ function print_balance_sheet() 2 => array('text' => _('Dimension')." 1", 'from' => get_dimension_string($dimension), 'to' => ''), 3 => array('text' => _('Dimension')." 2", - 'from' => get_dimension_string($dimension2), 'to' => '')); + 'from' => get_dimension_string($dimension2), 'to' => ''), + 4 => array('text' => _('Tags'), 'from' => get_tag_names($tags), 'to' => '')); } else if ($dim == 1) { $params = array( 0 => $comments, 1 => array('text' => _('Period'),'from' => $from, 'to' => $to), 2 => array('text' => _('Dimension'), - 'from' => get_dimension_string($dimension), 'to' => '')); + 'from' => get_dimension_string($dimension), 'to' => ''), + 3 => array('text' => _('Tags'), 'from' => get_tag_names($tags), 'to' => '')); } else { $params = array( 0 => $comments, - 1 => array('text' => _('Period'),'from' => $from, 'to' => $to)); + 1 => array('text' => _('Period'),'from' => $from, 'to' => $to), + 2 => array('text' => _('Tags'), 'from' => get_tag_names($tags), 'to' => '')); } $rep = new FrontReport(_('Balance Sheet'), "BalanceSheet", user_pagesize()); @@ -225,7 +236,7 @@ function print_balance_sheet() while ($accounttype=db_fetch($typeresult)) { $classtotal = display_type($accounttype["id"], $accounttype["name"], $from, $to, $convert, $dec, - $rep, $dimension, $dimension2, $pg, $graphics); + $rep, $dimension, $dimension2, $tags, $pg, $graphics); $class_open_total += $classtotal[0]; $class_period_total += $classtotal[1]; } diff --git a/reporting/rep707.php b/reporting/rep707.php index 55bf0aa9..1d1f2fe3 100644 --- a/reporting/rep707.php +++ b/reporting/rep707.php @@ -22,16 +22,17 @@ include_once($path_to_root . "/includes/session.inc"); include_once($path_to_root . "/includes/date_functions.inc"); include_once($path_to_root . "/includes/data_checks.inc"); include_once($path_to_root . "/gl/includes/gl_db.inc"); +include_once($path_to_root . "/admin/db/tags_db.inc"); //---------------------------------------------------------------------------------------------------- -function display_type ($type, $typename, $from, $to, $begin, $end, $compare, $convert, &$dec, &$pdec, &$rep, $dimension, $dimension2, &$pg, $graphics) +function display_type ($type, $typename, $from, $to, $begin, $end, $compare, $convert, &$dec, &$pdec, &$rep, $dimension, $dimension2, + $tags, &$pg, $graphics) { $code_per_balance = 0; $code_acc_balance = 0; $per_balance_total = 0; $acc_balance_total = 0; - unset($totals_arr); $totals_arr = array(); $printtitle = 0; //Flag for printing type name @@ -40,6 +41,11 @@ function display_type ($type, $typename, $from, $to, $begin, $end, $compare, $co $result = get_gl_accounts(null, null, $type); while ($account=db_fetch($result)) { + if ($tags != -1) + { + if (!is_record_in_tags($tags, TAG_ACCOUNT, $account['account_code'])) + continue; + } $per_balance = get_gl_trans_from_to($from, $to, $account["account_code"], $dimension, $dimension2); if ($compare == 2) @@ -156,26 +162,29 @@ function print_profit_and_loss_statement() { $dimension = $_POST['PARAM_3']; $dimension2 = $_POST['PARAM_4']; + $tags = (isset($_POST['PARAM_5']) ? $_POST['PARAM_5'] : -1); + $decimals = $_POST['PARAM_6']; + $graphics = $_POST['PARAM_7']; + $comments = $_POST['PARAM_8']; + $destination = $_POST['PARAM_9']; + } + else if ($dim == 1) + { + $dimension = $_POST['PARAM_3']; + $tags = (isset($_POST['PARAM_4']) ? $_POST['PARAM_4'] : -1); $decimals = $_POST['PARAM_5']; $graphics = $_POST['PARAM_6']; $comments = $_POST['PARAM_7']; $destination = $_POST['PARAM_8']; } - else if ($dim == 1) + else { - $dimension = $_POST['PARAM_3']; + $tags = (isset($_POST['PARAM_3']) ? $_POST['PARAM_3'] : -1); $decimals = $_POST['PARAM_4']; $graphics = $_POST['PARAM_5']; $comments = $_POST['PARAM_6']; $destination = $_POST['PARAM_7']; } - else - { - $decimals = $_POST['PARAM_3']; - $graphics = $_POST['PARAM_4']; - $comments = $_POST['PARAM_5']; - $destination = $_POST['PARAM_6']; - } if ($destination) include_once($path_to_root . "/reporting/includes/excel_report.inc"); else @@ -205,19 +214,22 @@ function print_profit_and_loss_statement() 2 => array('text' => _('Dimension')." 1", 'from' => get_dimension_string($dimension), 'to' => ''), 3 => array('text' => _('Dimension')." 2", - 'from' => get_dimension_string($dimension2), 'to' => '')); + 'from' => get_dimension_string($dimension2), 'to' => ''), + 4 => array('text' => _('Tags'), 'from' => get_tag_names($tags), 'to' => '')); } else if ($dim == 1) { $params = array( 0 => $comments, 1 => array('text' => _('Period'),'from' => $from, 'to' => $to), 2 => array('text' => _('Dimension'), - 'from' => get_dimension_string($dimension), 'to' => '')); + 'from' => get_dimension_string($dimension), 'to' => ''), + 3 => array('text' => _('Tags'), 'from' => get_tag_names($tags), 'to' => '')); } else { $params = array( 0 => $comments, - 1 => array('text' => _('Period'),'from' => $from, 'to' => $to)); + 1 => array('text' => _('Period'),'from' => $from, 'to' => $to), + 2 => array('text' => _('Tags'), 'from' => get_tag_names($tags), 'to' => '')); } @@ -245,8 +257,6 @@ function print_profit_and_loss_statement() $rep->Info($params, $cols, $headers, $aligns); $rep->NewPage(); - $classper = 0.0; - $classacc = 0.0; $salesper = 0.0; $salesacc = 0.0; @@ -268,7 +278,7 @@ function print_profit_and_loss_statement() while ($accounttype=db_fetch($typeresult)) { $classtotal = display_type($accounttype["id"], $accounttype["name"], $from, $to, $begin, $end, $compare, $convert, $dec, - $pdec, $rep, $dimension, $dimension2, $pg, $graphics); + $pdec, $rep, $dimension, $dimension2, $tags, $pg, $graphics); $class_per_total += $classtotal[0]; $class_acc_total += $classtotal[1]; } diff --git a/reporting/reports_main.php b/reporting/reports_main.php index fe6db04f..a0fa4753 100644 --- a/reporting/reports_main.php +++ b/reporting/reports_main.php @@ -250,6 +250,7 @@ if ($dim == 2) array( _('Year') => 'TRANS_YEARS', _('Dimension')." 1" => 'DIMENSIONS1', _('Dimension')." 2" => 'DIMENSIONS2', + _('Account Tags') => 'ACCOUNTTAGS', _('Comments') => 'TEXTBOX', _('Destination') => 'DESTINATION')); $reports->addReport(RC_GL, 706, _('&Balance Sheet'), @@ -257,6 +258,7 @@ if ($dim == 2) _('End Date') => 'DATEENDM', _('Dimension')." 1" => 'DIMENSIONS1', _('Dimension')." 2" => 'DIMENSIONS2', + _('Account Tags') => 'ACCOUNTTAGS', _('Decimal values') => 'YES_NO', _('Graphics') => 'GRAPHIC', _('Comments') => 'TEXTBOX', @@ -267,6 +269,7 @@ if ($dim == 2) _('Compare to') => 'COMPARE', _('Dimension')." 1" => 'DIMENSIONS1', _('Dimension')." 2" => 'DIMENSIONS2', + _('Account Tags') => 'ACCOUNTTAGS', _('Decimal values') => 'YES_NO', _('Graphics') => 'GRAPHIC', _('Comments') => 'TEXTBOX', @@ -294,12 +297,14 @@ else if ($dim == 1) $reports->addReport(RC_GL, 705, _('Annual &Expense Breakdown'), array( _('Year') => 'TRANS_YEARS', _('Dimension') => 'DIMENSIONS1', + _('Account Tags') => 'ACCOUNTTAGS', _('Comments') => 'TEXTBOX', _('Destination') => 'DESTINATION')); $reports->addReport(RC_GL, 706, _('&Balance Sheet'), array( _('Start Date') => 'DATEBEGIN', _('End Date') => 'DATEENDM', _('Dimension') => 'DIMENSIONS1', + _('Account Tags') => 'ACCOUNTTAGS', _('Decimal values') => 'YES_NO', _('Graphics') => 'GRAPHIC', _('Comments') => 'TEXTBOX', @@ -309,6 +314,7 @@ else if ($dim == 1) _('End Date') => 'DATEENDM', _('Compare to') => 'COMPARE', _('Dimension') => 'DIMENSIONS1', + _('Account Tags') => 'ACCOUNTTAGS', _('Decimal values') => 'YES_NO', _('Graphics') => 'GRAPHIC', _('Comments') => 'TEXTBOX', @@ -333,11 +339,13 @@ else _('Destination') => 'DESTINATION')); $reports->addReport(RC_GL, 705, _('Annual &Expense Breakdown'), array( _('Year') => 'TRANS_YEARS', + _('Account Tags') => 'ACCOUNTTAGS', _('Comments') => 'TEXTBOX', _('Destination') => 'DESTINATION')); $reports->addReport(RC_GL, 706, _('&Balance Sheet'), array( _('Start Date') => 'DATEBEGIN', _('End Date') => 'DATEENDM', + _('Account Tags') => 'ACCOUNTTAGS', _('Decimal values') => 'YES_NO', _('Graphics') => 'GRAPHIC', _('Comments') => 'TEXTBOX', @@ -346,6 +354,7 @@ else array( _('Start Date') => 'DATEBEGINM', _('End Date') => 'DATEENDM', _('Compare to') => 'COMPARE', + _('Account Tags') => 'ACCOUNTTAGS', _('Decimal values') => 'YES_NO', _('Graphics') => 'GRAPHIC', _('Comments') => 'TEXTBOX', -- 2.30.2