X-Git-Url: https://delta.frontaccounting.com/gitweb/?a=blobdiff_plain;f=includes%2Fdb%2Faudit_trail_db.inc;h=feb85a991f744b6ed3facf54fd4fffaff18d7964;hb=c7fa25a75b6a5c51aac91dec186217b134d7bcbc;hp=f9efe9a01861a2d3016dbdbf21e7c4d1fcec4904;hpb=d67f3efea0aa65cea4f875e2b46d0dacc3e838ef;p=fa-stable.git

diff --git a/includes/db/audit_trail_db.inc b/includes/db/audit_trail_db.inc
index f9efe9a0..feb85a99 100644
--- a/includes/db/audit_trail_db.inc
+++ b/includes/db/audit_trail_db.inc
@@ -12,23 +12,26 @@
 
 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)
+		. " (type, trans_no, user, gl_date, description, stamp)
 			VALUES(".db_escape($trans_type).", ".db_escape($trans_no).","
-			. $_SESSION["wa_current_user"]->user. ","
-			. get_company_pref('f_year') .","
-			. "'". date2sql($trans_date) ."',"
-			. db_escape($descr). ", 0)";
+			. $_SESSION["wa_current_user"]->user . ","
+			. "'$date',". db_escape($descr). ", CURRENT_TIMESTAMP)";
 
 	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"
+	$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, stamp=CURRENT_TIMESTAMP"
 		. " WHERE type=".db_escape($trans_type)." AND trans_no="
-		.db_escape($trans_no)." AND id!=".db_insert_id();
+		. db_escape($trans_no);
 
 	db_query($sql, "Cannot update audit gl_seq");
+	commit_transaction();
 }
 
 function get_audit_trail_all($trans_type, $trans_no)
@@ -58,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");
 
@@ -78,63 +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."));
-}
+	else
+		update_company_prefs(array('gl_closing_date'=> date2sql($todate)));
+	commit_transaction();
 
-/*
-	Reopen all transactions for edition up from date $fromdate
-*/
-function open_transactions($fromdate) {
+	return $errors;
+}
 
-	$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";
+function get_journal_number($type, $trans_no) {
 
-	$result = db_query($sql, "Cannot select transactions for openning");
+	$cdate = get_company_pref('gl_closing_date');
+	if (!$cdate)
+		return false;
 
-	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");
-		}
+// 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=".db_escape($type)
-		." AND trans_no=".db_escape($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;
 }
 
-?>