X-Git-Url: https://delta.frontaccounting.com/gitweb/?a=blobdiff_plain;f=includes%2Fdb%2Faudit_trail_db.inc;h=6aa91d9833689c0fdfd4db3a70f2f653f747ab08;hb=7f32bbd73c782449684cf6c6bf987e5bfbae9eb7;hp=e7958805dd184fb7c4521d331a6f5c959746dbac;hpb=5b2360be6df88ae1d44f32753efb8151aec50c57;p=fa-stable.git diff --git a/includes/db/audit_trail_db.inc b/includes/db/audit_trail_db.inc index e7958805..6aa91d98 100644 --- a/includes/db/audit_trail_db.inc +++ b/includes/db/audit_trail_db.inc @@ -12,28 +12,33 @@ function add_audit_trail($trans_type, $trans_no, $trans_date, $descr='') { + begin_transaction(); + + $date = date2sql($trans_date); $sql = "INSERT INTO ".TB_PREF."audit_trail" - . " (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). ", 0)"; + . " (type, trans_no, user, gl_date, description) + VALUES(".db_escape($trans_type).", ".db_escape($trans_no)."," + . $_SESSION["wa_current_user"]->user . "," + . "'$date',". db_escape($descr). ")"; db_query($sql, "Cannot add audit info"); - - // all audit records beside latest one should have gl_seq set to NULL + // all audit records beside just inserted 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(); + $sql = "UPDATE ".TB_PREF."audit_trail audit LEFT JOIN ".TB_PREF."fiscal_year year ON year.begin<='$date' AND year.end>='$date' + SET audit.gl_seq = IF(audit.id=".db_insert_id().", 0, NULL)," + ."audit.fiscal_year=year.id" + . " WHERE type=".db_escape($trans_type)." AND trans_no=" + . db_escape($trans_no); db_query($sql, "Cannot update audit gl_seq"); + commit_transaction(); } function get_audit_trail_all($trans_type, $trans_no) { $sql = "SELECT * FROM ".TB_PREF."audit_trail" - ." WHERE type=$trans_type AND trans_no=$trans_no"; + ." WHERE type=".db_escape($trans_type)." AND trans_no=" + .db_escape($trans_no); return db_query($sql, "Cannot get all audit info for transaction"); } @@ -41,7 +46,8 @@ 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 AND NOT ISNULL(gl_seq)"; + ." WHERE type=".db_escape($trans_type). + " AND trans_no=".db_escape($trans_no)." AND NOT ISNULL(gl_seq)"; $res = db_query($sql, "Cannot get last audit info for transaction"); if ($res) @@ -55,14 +61,18 @@ function get_audit_trail_last($trans_type, $trans_no) and reindex journal. */ function close_transactions($todate) { + + begin_transaction(); + $errors = 0; - $sql = "SELECT DISTINCT a.id, a.gl_date, a.fiscal_year" + // select only those audit trail records which produce any GL postings + $sql = "SELECT a.id, gl.tran_date, a.fiscal_year, a.gl_seq, + gl.tran_date <= '". date2sql($todate) ."' as closed" ." 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"; + . " WHERE NOT ISNULL(a.gl_seq) AND gl.amount!=0" // skip old audit records and voided transactions + . " GROUP BY a.id, gl.tran_date, a.fiscal_year, a.gl_seq ORDER BY a.fiscal_year, gl.tran_date, a.id"; $result = db_query($sql, "Cannot select transactions for closing"); @@ -75,61 +85,55 @@ function close_transactions($todate) { } 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"); + $counter = 0; // reset counter on fiscal year change + } + + $seq = $row['closed'] ? ++$counter : 0; + if ($row['gl_seq'] != $seq) { // update transaction status only when gl_seq has changed + $sql2 = "UPDATE ".TB_PREF."audit_trail SET" + . " gl_seq=$seq" + . " 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) { + else + update_company_prefs(array('gl_closing_date'=> date2sql($todate))); + commit_transaction(); - $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"; + return $errors; +} - $result = db_query($sql, "Cannot select transactions for openning"); +function get_journal_number($type, $trans_no) { - if (db_num_rows($result)) { - $last_year = 0; + $cdate = get_company_pref('gl_closing_date'); + if (!$cdate) + return false; - 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"); - } +// FIXME: gl_date can be badly entered for some transactions due to bug in previous FA versions + $sql = "SELECT MAX(gl_seq) as gl_seq FROM ".TB_PREF."audit_trail" + . " WHERE type=".db_escape($type) + ." AND trans_no=".db_escape($trans_no) + ." AND gl_date<='$cdate'"; // date is stored in sql format +// ." AND (gl_date<='$cdate'" // some transaction can be not sequenced due to 0 amount, however after edition this could change +// ." OR gl_seq>0)"; + $res = db_query($sql, "Cannot check transaction"); + if (db_num_rows($res)) + { + $myrow =db_fetch($res); + return $myrow['gl_seq'] ? $myrow['gl_seq'] : _('None'); } + return "-"; } + /* Closed transactions have gl_seq number assigned. */ function is_closed_trans($type, $trans_no) { - $sql = "SELECT gl_seq FROM ".TB_PREF."audit_trail" - . " WHERE type=$type AND trans_no=$trans_no AND gl_seq>0"; - - $res = db_query($sql, "Cannot check transaction"); - return db_num_rows($res); + return get_journal_number($type, $trans_no) > 0; } -?>