Support for periodic journal trans closing/indexing.

[fa-stable.git] / includes / db / audit_trail_db.inc

diff --git a/includes/db/audit_trail_db.inc b/includes/db/audit_trail_db.inc
index 261193f8255e9d662094de625cca689d5f727331..6113bfd3421c4f36a4ea7dc5b30f4f27e76e4adc 100644 (file)
--- a/includes/db/audit_trail_db.inc
+++ b/includes/db/audit_trail_db.inc
@@ -13,14 +13,21 @@
 function add_audit_trail($trans_type, $trans_no, $trans_date, $descr='')
 {
        $sql = "INSERT INTO ".TB_PREF."audit_trail"
-               . " (type, trans_no, user, fiscal_year, gl_date, description)
+               . " (type, trans_no, user, fiscal_year, gl_date, description, gl_seq)
                        VALUES($trans_type, $trans_no,"
                        . $_SESSION["wa_current_user"]->user. ","
                        . get_company_pref('f_year') .","
                        . "'". date2sql($trans_date) ."',"
-                       . db_escape($descr). ")";
+                       . db_escape($descr). ", 0)";
 
        db_query($sql, "Cannot add audit info");
+       
+       // all audit records beside latest one should have gl_seq set to NULL
+       // to avoid need for subqueries (not existing in MySQL 3) all over the code
+       $sql = "UPDATE ".TB_PREF."audit_trail SET gl_seq = NULL"
+               . " WHERE type=$trans_type AND trans_no=$trans_no AND id!=".db_insert_id();
+
+       db_query($sql, "Cannot update audit gl_seq");
 }
 
 function get_audit_trail_all($trans_type, $trans_no)
@@ -34,7 +41,7 @@ function get_audit_trail_all($trans_type, $trans_no)
 function get_audit_trail_last($trans_type, $trans_no)
 {
        $sql = "SELECT * FROM ".TB_PREF."audit_trail"
-               ." WHERE type=$trans_type AND trans_no=$trans_no ORDER BY id desc";
+               ." WHERE type=$trans_type AND trans_no=$trans_no AND NOT ISNULL(gl_seq)";
 
        $res = db_query($sql, "Cannot get last audit info for transaction");
        if ($res)
@@ -43,4 +50,75 @@ function get_audit_trail_last($trans_type, $trans_no)
        return $row;
 }
 
+/*
+       Confirm and close for edition all transactions up to date $todate, 
+       and reindex     journal.
+*/
+function close_transactions($todate) {
+       $errors = 0;
+       $sql = "SELECT DISTINCT a.id, a.gl_date, a.fiscal_year"
+               ." FROM ".TB_PREF."gl_trans gl"
+               ." LEFT JOIN ". TB_PREF."audit_trail a ON 
+                       (gl.type=a.type AND gl.type_no=a.trans_no)"
+               . " WHERE gl_date<='". date2sql($todate) ."'"
+               . " AND NOT ISNULL(gl_seq)"
+               . " ORDER BY a.fiscal_year, a.gl_date, a.id";
+
+       $result = db_query($sql, "Cannot select transactions for closing");
+
+       if (db_num_rows($result)) {
+               $last_year = 0;
+
+               while ($row = db_fetch($result)) {
+                       if ($row['fiscal_year'] == null) {
+                               $errors = 1; continue;
+                       }
+                       if ($last_year != $row['fiscal_year']) {
+                               $last_year = $row['fiscal_year'];
+                               $counter = 1; // reset counter on fiscal year change
+                       } else
+                               $counter++;
+                       $sql2 = "UPDATE ".TB_PREF."audit_trail SET"
+                               . " gl_seq=$counter"
+                               . " WHERE id=".$row['id'];
+                                                                                       
+                       db_query($sql2, "Cannot reindex journal");
+               }
+       }
+       
+       if ($errors) 
+               display_warning(_("Some transactions journal GL postings were not indexed due to lack of audit trail record."));
+}
+
+/*
+       Reopen all transactions for edition up from date $fromdate
+*/
+function open_transactions($fromdate) {
+
+       $sql = "SELECT a.id, a.gl_date, a.fiscal_year"
+               ." FROM ".TB_PREF."gl_trans gl"
+               ." LEFT JOIN ". TB_PREF."audit_trail a ON 
+                       (gl.type=a.type AND gl.type_no=a.trans_no)"
+               . " WHERE gl_date>='". date2sql($fromdate) ."'"
+               . " AND !ISNULL(gl_seq)"
+               . " ORDER BY a.fiscal_year, a.gl_date, a.id";
+
+       $result = db_query($sql, "Cannot select transactions for openning");
+
+       if (db_num_rows($result)) {
+               $last_year = 0;
+
+               while ($row = db_fetch($result)) {
+                       if ($row['fiscal_year'] == null) {
+                               continue;
+                       }
+                       $sql2 = "UPDATE ".TB_PREF."audit_trail SET"
+                               . " gl_seq=0"
+                               . " WHERE id=".$row['id'];
+                                                                                       
+                       db_query($sql2, "Cannot clear journal order");
+               }
+       }
+}
+
 ?>