includes/db/audit_trail_db.inc

   1 <?php
   2 /**********************************************************************
   3         Copyright (C) FrontAccounting, LLC.
   4         Released under the terms of the GNU General Public License, GPL,
   5         as published by the Free Software Foundation, either version 3
   6         of the License, or (at your option) any later version.
   7         This program is distributed in the hope that it will be useful,
   8         but WITHOUT ANY WARRANTY; without even the implied warranty of
   9         MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
  10         See the License here <http://www.gnu.org/licenses/gpl-3.0.html>.
  11 ***********************************************************************/
  12
  13 function add_audit_trail($trans_type, $trans_no, $trans_date, $descr='')
  14 {
  15         $sql = "INSERT INTO ".TB_PREF."audit_trail"
  16                 . " (type, trans_no, user, fiscal_year, gl_date, description, gl_seq)
  17                         VALUES(".db_escape($trans_type).", ".db_escape($trans_no).","
  18                         . $_SESSION["wa_current_user"]->user. ","
  19                         . get_company_pref('f_year') .","
  20                         . "'". date2sql($trans_date) ."',"
  21                         . db_escape($descr). ", 0)";
  22
  23         db_query($sql, "Cannot add audit info");
  24
  25         // all audit records beside latest one should have gl_seq set to NULL
  26         // to avoid need for subqueries (not existing in MySQL 3) all over the code
  27         $sql = "UPDATE ".TB_PREF."audit_trail SET gl_seq = NULL"
  28                 . " WHERE type=".db_escape($trans_type)." AND trans_no="
  29                 .db_escape($trans_no)." AND id!=".db_insert_id();
  30
  31         db_query($sql, "Cannot update audit gl_seq");
  32 }
  33
  34 function get_audit_trail_all($trans_type, $trans_no)
  35 {
  36         $sql = "SELECT * FROM ".TB_PREF."audit_trail"
  37                 ." WHERE type=".db_escape($trans_type)." AND trans_no="
  38                 .db_escape($trans_no);
  39
  40         return db_query($sql, "Cannot get all audit info for transaction");
  41 }
  42
  43 function get_audit_trail_last($trans_type, $trans_no)
  44 {
  45         $sql = "SELECT * FROM ".TB_PREF."audit_trail"
  46                 ." WHERE type=".db_escape($trans_type).
  47                         " AND trans_no=".db_escape($trans_no)." AND NOT ISNULL(gl_seq)";
  48
  49         $res = db_query($sql, "Cannot get last audit info for transaction");
  50         if ($res)
  51                 $row = db_fetch($res);
  52
  53         return $row;
  54 }
  55
  56 /*
  57         Confirm and close for edition all transactions up to date $todate,
  58         and reindex     journal.
  59 */
  60 function close_transactions($todate) {
  61         $errors = 0;
  62         $sql = "SELECT DISTINCT a.id, a.gl_date, a.fiscal_year"
  63                 ." FROM ".TB_PREF."gl_trans gl"
  64                 ." LEFT JOIN ". TB_PREF."audit_trail a ON
  65                         (gl.type=a.type AND gl.type_no=a.trans_no)"
  66                 . " WHERE gl_date<='". date2sql($todate) ."'"
  67                 . " AND NOT ISNULL(gl_seq)"
  68                 . " ORDER BY a.fiscal_year, a.gl_date, a.id";
  69
  70         $result = db_query($sql, "Cannot select transactions for closing");
  71
  72         if (db_num_rows($result)) {
  73                 $last_year = 0;
  74
  75                 while ($row = db_fetch($result)) {
  76                         if ($row['fiscal_year'] == null) {
  77                                 $errors = 1; continue;
  78                         }
  79                         if ($last_year != $row['fiscal_year']) {
  80                                 $last_year = $row['fiscal_year'];
  81                                 $counter = 1; // reset counter on fiscal year change
  82                         } else
  83                                 $counter++;
  84                         $sql2 = "UPDATE ".TB_PREF."audit_trail SET"
  85                                 . " gl_seq=$counter"
  86                                 . " WHERE id=".$row['id'];
  87
  88                         db_query($sql2, "Cannot reindex journal");
  89                 }
  90         }
  91
  92         if ($errors)
  93                 display_warning(_("Some transactions journal GL postings were not indexed due to lack of audit trail record."));
  94 }
  95
  96 /*
  97         Reopen all transactions for edition up from date $fromdate
  98 */
  99 function open_transactions($fromdate) {
 100
 101         $sql = "SELECT a.id, a.gl_date, a.fiscal_year"
 102                 ." FROM ".TB_PREF."gl_trans gl"
 103                 ." LEFT JOIN ". TB_PREF."audit_trail a ON
 104                         (gl.type=a.type AND gl.type_no=a.trans_no)"
 105                 . " WHERE gl_date>='". date2sql($fromdate) ."'"
 106                 . " AND !ISNULL(gl_seq)"
 107                 . " ORDER BY a.fiscal_year, a.gl_date, a.id";
 108
 109         $result = db_query($sql, "Cannot select transactions for openning");
 110
 111         if (db_num_rows($result)) {
 112                 $last_year = 0;
 113
 114                 while ($row = db_fetch($result)) {
 115                         if ($row['fiscal_year'] == null) {
 116                                 continue;
 117                         }
 118                         $sql2 = "UPDATE ".TB_PREF."audit_trail SET"
 119                                 . " gl_seq=0"
 120                                 . " WHERE id=".$row['id'];
 121
 122                         db_query($sql2, "Cannot clear journal order");
 123                 }
 124         }
 125 }
 126 /*
 127         Closed transactions have gl_seq number assigned.
 128 */
 129 function is_closed_trans($type, $trans_no) {
 130         $sql = "SELECT  gl_seq  FROM ".TB_PREF."audit_trail"
 131                 . " WHERE type=".db_escape($type)
 132                 ." AND trans_no=".db_escape($trans_no)
 133                 ." AND gl_seq>0";
 134
 135         $res = db_query($sql, "Cannot check transaction");
 136
 137         return db_num_rows($res);
 138 }
 139
 140 ?>