From 11861a415cd5166003ee6b150096ccfe2540d138 Mon Sep 17 00:00:00 2001 From: Joe Hunt Date: Wed, 15 Jul 2009 08:48:24 +0000 Subject: [PATCH] Audit-Trail report (systypes selector should include all systypes, extra parameter to function dup_systypes_list. Removed some warnings from reports. --- CHANGELOG.txt | 8 +++++++ gl/includes/db/gl_db_accounts.inc | 3 ++- reporting/includes/reports_classes.inc | 7 +++--- reporting/rep709.php | 2 +- reporting/rep710.php | 33 +++++++++++++------------- reporting/reports_main.php | 2 +- sql/alter2.2.sql | 1 + 7 files changed, 34 insertions(+), 22 deletions(-) diff --git a/CHANGELOG.txt b/CHANGELOG.txt index be91584a..86fdad7c 100644 --- a/CHANGELOG.txt +++ b/CHANGELOG.txt @@ -29,6 +29,14 @@ $ /reporting/rep101.php /reporting/rep202.php /reporting/rep203.php /reporting/rep709.php + /sql/alter2.2.sql +! Audit-Trail report (systypes selector should include all systypes, extra parameter to function dup_systypes_list. +$ /reporting/rep710.php + /reporting/reports_main.php + /reporting/includes/reports_classes.inc +# Removed warning from COA report +$ /gl/includes/db/gl_db_accounts.inc + /reporting/rep701.php 13-Jul-2009 Joe Hunt + Added Audit Trail Report diff --git a/gl/includes/db/gl_db_accounts.inc b/gl/includes/db/gl_db_accounts.inc index eb61b1c7..474ea713 100644 --- a/gl/includes/db/gl_db_accounts.inc +++ b/gl/includes/db/gl_db_accounts.inc @@ -55,7 +55,8 @@ function get_gl_accounts_all($balance=-1) $where ="WHERE ctype>0 AND ctype<".CL_INCOME; elseif ($balance == 0) $where ="WHERE ctype>".CL_EQUITY." OR ctype=0"; // backwards compatibility - $sql = "SELECT ".TB_PREF."chart_master.account_code, ".TB_PREF."chart_master.account_name, ".TB_PREF."chart_types.name AS AccountTypeName,".TB_PREF."chart_types.id AS AccountType, + $sql = "SELECT ".TB_PREF."chart_master.account_code, ".TB_PREF."chart_master.account_code2, ".TB_PREF."chart_master.account_name, + ".TB_PREF."chart_types.name AS AccountTypeName,".TB_PREF."chart_types.id AS AccountType, ".TB_PREF."chart_types.parent, ".TB_PREF."chart_class.class_name AS AccountClassName, ".TB_PREF."chart_class.cid AS ClassID, ".TB_PREF."chart_class.ctype AS ClassType FROM ".TB_PREF."chart_types INNER JOIN ".TB_PREF."chart_class ON ".TB_PREF."chart_types.class_id=".TB_PREF."chart_class.cid diff --git a/reporting/includes/reports_classes.inc b/reporting/includes/reports_classes.inc index 9a04b58e..62dcd582 100644 --- a/reporting/includes/reports_classes.inc +++ b/reporting/includes/reports_classes.inc @@ -197,7 +197,8 @@ class Report $st .= dup_simple_name_list("PARAM_$index", $sel); break; case 'SYS_TYPES': - $st .= dup_systypes_list("PARAM_$index", $dummy, true, _("No Type Filter"), true); + case 'SYS_TYPES_ALL': + $st .= dup_systypes_list("PARAM_$index", $dummy, true, _("No Type Filter"), ($param->param_type=='SYS_TYPES_ALL')); break; case 'TEXT': $st .= ""; @@ -421,7 +422,7 @@ function dup_simple_name_list($name, $selections) //------------------------------------------------------------------------------------------------ -function dup_systypes_list($name, &$selected_id, $all_option=false, $all_option_name=null) +function dup_systypes_list($name, &$selected_id, $all_option=false, $all_option_name=null, $all_types=false) { global $systypes_array; @@ -439,7 +440,7 @@ function dup_systypes_list($name, &$selected_id, $all_option=false, $all_option_ foreach ($systypes_array as $i => $value) { - if ($i == 16 || $i == 18 || $i == 25 || ($i >= 28 && $i <= 30) || $i >= 40) + if (!$all_types && ($i == 16 || $i == 18 || $i == 25 || ($i >= 28 && $i <= 30) || $i >= 40)) continue; $st .= "" . $value['name'] . "\n"; } diff --git a/reporting/rep709.php b/reporting/rep709.php index 6b1f1e40..e4598f01 100644 --- a/reporting/rep709.php +++ b/reporting/rep709.php @@ -98,7 +98,7 @@ function print_tax_report() 1 => array('text' => _('Period'), 'from' => $from, 'to' => $to), 2 => array('text' => _('Type'), 'from' => $summary, 'to' => '')); - $cols = array(0, 100, 130, 180, 290, 370, 455, 505, 555); + $cols = array(0, 100, 130, 180, 290, 370, 420, 470, 520); $headers = array(_('Trans Type'), _('Ref'), _('Date'), _('Name'), _('Branch Name'), _('Net'), _('Rate'), _('Tax')); diff --git a/reporting/rep710.php b/reporting/rep710.php index 9c1df5f1..76f232b7 100644 --- a/reporting/rep710.php +++ b/reporting/rep710.php @@ -33,22 +33,22 @@ function getTransactions($from, $to, $type, $user) $fromdate = date2sql($from); $todate = date2sql($to); - $sql = "SELECT ".TB_PREF."audit_trail.*, ".TB_PREF."gl_trans.tran_date, - SUM(IF(".TB_PREF."gl_trans.amount > 0, ".TB_PREF."gl_trans.amount, 0)) AS amount, - ".TB_PREF."users.user_id, - UNIX_TIMESTAMP(".TB_PREF."audit_trail.stamp) as unix_stamp - FROM ".TB_PREF."audit_trail, ".TB_PREF."gl_trans, ".TB_PREF."users - WHERE ".TB_PREF."audit_trail.type = ".TB_PREF."gl_trans.type - AND ".TB_PREF."audit_trail.trans_no = ".TB_PREF."gl_trans.type_no - AND ".TB_PREF."audit_trail.user = ".TB_PREF."users.id "; + $sql = "SELECT a.*, + SUM(IF(ISNULL(g.amount), NULL, IF(g.amount > 0, g.amount, 0))) AS amount, + u.user_id, + UNIX_TIMESTAMP(a.stamp) as unix_stamp + FROM ".TB_PREF."audit_trail AS a JOIN ".TB_PREF."users AS u + LEFT JOIN ".TB_PREF."gl_trans AS g ON (g.type_no=a.trans_no + AND g.type=a.type) + WHERE a.user = u.id "; if ($type != -1) - $sql .= "AND ".TB_PREF."gl_trans.type=$type "; + $sql .= "AND a.type=$type "; if ($user != -1) - $sql .= "AND ".TB_PREF."audit_trail.user='$user' "; - $sql .= "AND DATE(".TB_PREF."audit_trail.stamp) >= '$fromdate' - AND DATE(".TB_PREF."audit_trail.stamp) <= '$todate' - GROUP BY ".TB_PREF."gl_trans.type_no,".TB_PREF."audit_trail.gl_seq,".TB_PREF."audit_trail.stamp - ORDER BY ".TB_PREF."audit_trail.stamp,".TB_PREF."audit_trail.gl_seq"; + $sql .= "AND a.user='$user' "; + $sql .= "AND DATE(a.stamp) >= '$fromdate' + AND DATE(a.stamp) <= '$todate' + GROUP BY a.trans_no,a.gl_seq,a.stamp + ORDER BY a.stamp,a.gl_seq"; return db_query($sql,"No transactions were returned"); } //---------------------------------------------------------------------------------------------------- @@ -100,7 +100,7 @@ function print_audit_trail() else $rep->TextCol(1, 2, date("H:i:s", $myrow['unix_stamp'])); $rep->TextCol(2, 3, $myrow['user_id']); - $rep->TextCol(3, 4, sql2date($myrow['tran_date'])); + $rep->TextCol(3, 4, sql2date($myrow['gl_date'])); $rep->TextCol(4, 5, systypes::name($myrow['type'])); $rep->TextCol(5, 6, $myrow['trans_no']); if ($myrow['gl_seq'] == null) @@ -108,7 +108,8 @@ function print_audit_trail() else $action = _('Closed'); $rep->TextCol(6, 7, $action); - $rep->AmountCol(7, 8, $myrow['amount'], $dec); + if ($myrow['amount'] != null) + $rep->AmountCol(7, 8, $myrow['amount'], $dec); $rep->NewLine(1, 2); } $rep->Line($rep->row + 4); diff --git a/reporting/reports_main.php b/reporting/reports_main.php index bb5ea791..df7019f5 100644 --- a/reporting/reports_main.php +++ b/reporting/reports_main.php @@ -339,7 +339,7 @@ $reports->addReport(_('General Ledger'),709,_('Ta&x Report'), $reports->addReport(_('General Ledger'),710,_('Audit Trail'), array( new ReportParam(_('Start Date'),'DATEBEGINM'), new ReportParam(_('End Date'),'DATEENDM'), - new ReportParam(_('Type'),'SYS_TYPES'), + new ReportParam(_('Type'),'SYS_TYPES_ALL'), new ReportParam(_('User'),'USERS'), new ReportParam(_('Comments'),'TEXTBOX'), new ReportParam(_('Destination'),'DESTINATION'))); diff --git a/sql/alter2.2.sql b/sql/alter2.2.sql index f99abc0b..1327bd68 100644 --- a/sql/alter2.2.sql +++ b/sql/alter2.2.sql @@ -38,6 +38,7 @@ ALTER TABLE `0_users` ADD COLUMN `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 COLUMN `inactive` tinyint(1) NOT NULL default '0'; +ALTER TABLE `0_sys_types` DROP COLUMN `type_name`; ALTER TABLE `0_chart_class` DROP COLUMN `sign_convert`; ALTER TABLE `0_chart_class` CHANGE `balance_sheet` `ctype` TINYINT(1) NOT NULL DEFAULT '0'; -- 2.30.2