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

diff --git a/includes/db/audit_trail_db.inc b/includes/db/audit_trail_db.inc
index 261193f8..f9efe9a0 100644
--- a/includes/db/audit_trail_db.inc
+++ b/includes/db/audit_trail_db.inc
@@ -13,20 +13,29 @@
 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)
-			VALUES($trans_type, $trans_no,"
+		. " (type, trans_no, user, fiscal_year, gl_date, description, gl_seq)
+			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). ")";
+			. 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=".db_escape($trans_type)." AND trans_no="
+		.db_escape($trans_no)." AND id!=".db_insert_id();
+
+	db_query($sql, "Cannot update audit gl_seq");
 }
 
 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");
 }
@@ -34,7 +43,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 ORDER BY id desc";
+		." 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)
@@ -43,4 +53,88 @@ 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");
+		}
+	}
+}
+/*
+	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);
+}
+
 ?>