X-Git-Url: https://delta.frontaccounting.com/gitweb/?a=blobdiff_plain;f=admin%2Fdb%2Ftransactions_db.inc;h=293e8f4d0aad1dcec2ef45825cd603864260aa4e;hb=90b3d069d96b99671af51726e2953352738abb75;hp=09814938f1386bf9ed2a2af72d3f0a5c0286fff3;hpb=4221bf69f713a2fb0bb4c4cae6f8f4f819c3ec31;p=fa-stable.git

diff --git a/admin/db/transactions_db.inc b/admin/db/transactions_db.inc
index 09814938..293e8f4d 100644
--- a/admin/db/transactions_db.inc
+++ b/admin/db/transactions_db.inc
@@ -23,20 +23,40 @@ function get_sql_for_view_transactions($filtertype, $from, $to, &$trans_ref)
 	$type_name = $db_info[1];
 	$trans_no_name = $db_info[2];
 	$trans_ref = $db_info[3];
+	$trans_date = $db_info[4];
 
-	$sql = "SELECT DISTINCT $trans_no_name as trans_no";
+	$sql = "SELECT t.$trans_no_name as trans_no";
 
 	if ($trans_ref)
-		$sql .= " ,$trans_ref ";
-
-	$sql .= ", ".$filtertype." as type FROM $table_name
-		WHERE $trans_no_name >= ".db_escape($from). "
-		AND  $trans_no_name <= ".db_escape($to);
-
-	if ($type_name != null)
-		$sql .= " AND `$type_name` = ".db_escape($filtertype);
-
-	$sql .= " ORDER BY $trans_no_name";
+		$sql .= " ,t.$trans_ref as ref ";
+	else
+		$sql .= ", r.reference as ref";
+ 	$sql .= ",t.$trans_date as trans_date";
+ 	if ($type_name)
+		$sql .= ", t.$type_name as type";
+	$sql .= " FROM $table_name t LEFT JOIN ".TB_PREF."voided v ON"
+		." t.$trans_no_name=v.id AND v.type=$filtertype";
+	if (!$trans_ref)
+		$sql .= " LEFT JOIN ".TB_PREF."refs r ON t.$trans_no_name=r.id AND r.type=$filtertype";
+
+	$sql .= " WHERE ISNULL(v.`memo_`)";
+	if ($from != null && $to != null)
+	{
+		$sql .= " AND t.$trans_no_name >= ".db_escape($from). "
+			AND  t.$trans_no_name <= ".db_escape($to);
+		if ($type_name != null)
+			$sql .= " AND t.`$type_name` = ".db_escape($filtertype);
+	}
+	elseif ($type_name != null)
+		$sql .= " AND t.`$type_name` = ".db_escape($filtertype);
+
+	// the ugly hack below is necessary to exclude old gl_trans records lasting after edition,
+	// otherwise old data transaction can be retrieved instead of current one.
+	if ($table_name==TB_PREF.'gl_trans')
+		$sql .= " AND t.`amount` <> 0";
+
+	$sql .= " GROUP BY ".($type_name ? "t.$type_name," : '')." t.$trans_no_name";
+	$sql .= " ORDER BY t.$trans_no_name";
 	return $sql;
 }