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 ***********************************************************************/
13 function add_audit_trail($trans_type, $trans_no, $trans_date, $descr='')
15 $descr = substr($descr, 0, 60);
18 $date = date2sql($trans_date);
19 $sql = "INSERT INTO ".TB_PREF."audit_trail"
20 . " (type, trans_no, user, gl_date, description, stamp)
21 VALUES(".db_escape($trans_type).", ".db_escape($trans_no).","
22 . $_SESSION["wa_current_user"]->user . ","
23 . "'$date',". db_escape($descr). ", CURRENT_TIMESTAMP)";
25 db_query($sql, "Cannot add audit info");
26 // all audit records beside just inserted one should have gl_seq set to NULL
27 // to avoid need for subqueries (not existing in MySQL 3) all over the code
28 $sql = "UPDATE ".TB_PREF."audit_trail audit LEFT JOIN ".TB_PREF."fiscal_year year ON year.begin<='$date' AND year.end>='$date'
29 SET audit.gl_seq = IF(audit.id=".db_insert_id().", 0, NULL),"
30 ."audit.fiscal_year=year.id"
31 . " WHERE type=".db_escape($trans_type)." AND trans_no="
32 . db_escape($trans_no);
34 db_query($sql, "Cannot update audit gl_seq");
38 function get_audit_trail_all($trans_type, $trans_no)
40 $sql = "SELECT * FROM ".TB_PREF."audit_trail"
41 ." WHERE type=".db_escape($trans_type)." AND trans_no="
42 .db_escape($trans_no);
44 return db_query($sql, "Cannot get all audit info for transaction");
47 function get_audit_trail_last($trans_type, $trans_no)
49 $sql = "SELECT * FROM ".TB_PREF."audit_trail"
50 ." WHERE type=".db_escape($trans_type).
51 " AND trans_no=".db_escape($trans_no)." AND NOT ISNULL(gl_seq)";
53 $res = db_query($sql, "Cannot get last audit info for transaction");
55 $row = db_fetch($res);
61 Confirm and close for edition all transactions up to date $todate,
64 function close_transactions($todate) {
69 // select only those audit trail records which produce any GL postings
70 $sql = "SELECT a.id, gl.tran_date, a.fiscal_year, a.gl_seq,
71 gl.tran_date <= '". date2sql($todate) ."' as closed"
72 ." FROM ".TB_PREF."gl_trans gl"
73 ." LEFT JOIN ". TB_PREF."audit_trail a ON
74 (gl.type=a.type AND gl.type_no=a.trans_no)"
75 . " WHERE NOT ISNULL(a.gl_seq) AND gl.amount!=0" // skip old audit records and voided transactions
76 . " GROUP BY a.id, gl.tran_date, a.fiscal_year, a.gl_seq ORDER BY a.fiscal_year, gl.tran_date, a.id";
78 $result = db_query($sql, "Cannot select transactions for closing");
80 if (db_num_rows($result)) {
83 while ($row = db_fetch($result)) {
84 if ($row['fiscal_year'] == null) {
85 $errors = 1; continue;
87 if ($last_year != $row['fiscal_year']) {
88 $last_year = $row['fiscal_year'];
89 $counter = 0; // reset counter on fiscal year change
92 $seq = $row['closed'] ? ++$counter : 0;
93 if ($row['gl_seq'] != $seq) { // update transaction status only when gl_seq has changed
94 $sql2 = "UPDATE ".TB_PREF."audit_trail SET"
96 . " WHERE id=".$row['id'];
97 db_query($sql2, "Cannot reindex journal");
103 display_warning(_("Some transactions journal GL postings were not indexed due to lack of audit trail record."));
105 update_company_prefs(array('gl_closing_date'=> date2sql($todate)));
106 commit_transaction();
111 function get_journal_number($type, $trans_no) {
113 $cdate = get_company_pref('gl_closing_date');
117 // FIXME: gl_date can be badly entered for some transactions due to bug in previous FA versions
118 $sql = "SELECT MAX(gl_seq) as gl_seq FROM ".TB_PREF."audit_trail"
119 . " WHERE type=".db_escape($type)
120 ." AND trans_no=".db_escape($trans_no)
121 ." AND gl_date<='$cdate'"; // date is stored in sql format
122 // ." AND (gl_date<='$cdate'" // some transaction can be not sequenced due to 0 amount, however after edition this could change
124 $res = db_query($sql, "Cannot check transaction");
125 if (db_num_rows($res))
127 $myrow =db_fetch($res);
128 return $myrow['gl_seq'] ? $myrow['gl_seq'] : 0;
134 Closed transactions have gl_seq number assigned.
136 function is_closed_trans($type, $trans_no) {
138 return get_journal_number($type, $trans_no) > 0;