X-Git-Url: https://delta.frontaccounting.com/gitweb/?a=blobdiff_plain;f=purchasing%2Fincludes%2Fdb%2Fsuppalloc_db.inc;h=1c5a3581991168c01a6a2df7c178441b1edd2af8;hb=4657251eed9910c5669c859a54051dc54143aa54;hp=24fab3ba104f09a05ad1df4f6c5c1244aa40ff9c;hpb=da8311619dd73feae101d246a1957b972e00cbd2;p=fa-stable.git

diff --git a/purchasing/includes/db/suppalloc_db.inc b/purchasing/includes/db/suppalloc_db.inc
index 24fab3ba..1c5a3581 100644
--- a/purchasing/includes/db/suppalloc_db.inc
+++ b/purchasing/includes/db/suppalloc_db.inc
@@ -2,15 +2,15 @@
 
 //----------------------------------------------------------------------------------------
 
-function add_supp_allocation($amount, $trans_type_from, $trans_no_from, 
+function add_supp_allocation($amount, $trans_type_from, $trans_no_from,
 	$trans_type_to, $trans_no_to, $date_)
 {
 	$date = date2sql($date_);
 	$sql = "INSERT INTO ".TB_PREF."supp_allocations (
-		amt, date_alloc, 
-		trans_type_from, trans_no_from, trans_no_to, trans_type_to) 
+		amt, date_alloc,
+		trans_type_from, trans_no_from, trans_no_to, trans_type_to)
 		VALUES ($amount, '$date', $trans_type_from, $trans_no_from, $trans_no_to, $trans_type_to)";
-			
+
 	db_query($sql, "A supplier allocation could not be added to the database");
 }
 
@@ -27,11 +27,11 @@ function delete_supp_allocation($trans_id)
 
 function get_supp_trans_allocation_balance($trans_type, $trans_no)
 {
-	$sql = "SELECT (ov_amount+ov_gst-ov_discount-alloc) AS BalToAllocate 
+	$sql = "SELECT (ov_amount+ov_gst-ov_discount-alloc) AS BalToAllocate
 		FROM ".TB_PREF."supp_trans WHERE trans_no=$trans_no AND type=$trans_type";
 	$result = db_query($sql,"calculate the allocation");
-	$myrow = db_fetch_row($result);	
-	
+	$myrow = db_fetch_row($result);
+
 	return $myrow[0];
 }
 
@@ -46,77 +46,122 @@ function update_supp_trans_allocation($trans_type, $trans_no, $alloc)
 
 //-------------------------------------------------------------------------------------------------------------
 
-function void_supp_allocations($type, $type_no)
+function void_supp_allocations($type, $type_no, $date="")
 {
-	return clear_supp_alloctions($type, $type_no);
+	return clear_supp_alloctions($type, $type_no, $date);
 }
 
 //-------------------------------------------------------------------------------------------------------------
 
-function clear_supp_alloctions($type, $type_no)
+function clear_supp_alloctions($type, $type_no, $date="")
 {
 	// clear any allocations for this transaction
-	$sql = "SELECT * FROM ".TB_PREF."supp_allocations 
-		WHERE (trans_type_from=$type AND trans_no_from=$type_no) 
+	$sql = "SELECT * FROM ".TB_PREF."supp_allocations
+		WHERE (trans_type_from=$type AND trans_no_from=$type_no)
 		OR (trans_type_to=$type AND trans_no_to=$type_no)";
 	$result = db_query($sql, "could not void supp transactions for type=$type and trans_no=$type_no");
-	
+
 	while ($row = db_fetch($result))
 	{
 		$sql = "UPDATE ".TB_PREF."supp_trans SET alloc=alloc - " . $row['amt'] . "
-			WHERE (type= " . $row['trans_type_from'] . " AND trans_no=" . $row['trans_no_from'] . ") 
+			WHERE (type= " . $row['trans_type_from'] . " AND trans_no=" . $row['trans_no_from'] . ")
 			OR (type=" . $row['trans_type_to'] . " AND trans_no=" . $row['trans_no_to'] . ")";
 		//$sql = "UPDATE ".TB_PREF."supp_trans SET alloc=alloc - " . $row['amt'] . "
 		//	WHERE type=" . $row['trans_type_to'] . " AND trans_no=" . $row['trans_no_to'];
-		db_query($sql, "could not clear allocation");						
+		db_query($sql, "could not clear allocation");
+		// 2008-09-20 Joe Hunt
+		if ($date != "")
+			exchange_variation($type, $type_no, $row['trans_type_to'], $row['trans_no_to'], $date,
+				$row['amt'], payment_person_types::supplier(), true);
+		//////////////////////
 	}
-						
+
 
 	// remove any allocations for this transaction
-	$sql = "DELETE FROM ".TB_PREF."supp_allocations 
-		WHERE (trans_type_from=$type AND trans_no_from=$type_no) 
+	$sql = "DELETE FROM ".TB_PREF."supp_allocations
+		WHERE (trans_type_from=$type AND trans_no_from=$type_no)
 		OR (trans_type_to=$type AND trans_no_to=$type_no)";
-				
-	db_query($sql, "could not void supp transactions for type=$type and trans_no=$type_no");					
+
+	db_query($sql, "could not void supp transactions for type=$type and trans_no=$type_no");
+}
+
+//----------------------------------------------------------------------------------------
+function get_alloc_supp_sql($extra_fields=null, $extra_conditions=null, $extra_tables=null)
+{
+	$sql = "SELECT
+		trans.type,
+		trans.trans_no,
+		trans.reference,
+		trans.tran_date,
+		supplier.supp_name, 
+		supplier.curr_code, 
+		ov_amount+ov_gst+ov_discount AS Total,
+		trans.alloc,
+		trans.due_date,
+		trans.supplier_id,
+		supplier.address";
+/*	$sql = "SELECT trans.*,
+		ov_amount+ov_gst+ov_discount AS Total,
+		supplier.supp_name, supplier.address,
+		supplier.curr_code ";
+*/
+	if ($extra_fields)
+		$sql .= ", $extra_fields ";
+
+	$sql .= " FROM ".TB_PREF."supp_trans as trans, ".TB_PREF."suppliers as supplier";
+	if ($extra_tables)
+		$sql .= " ,$extra_tables ";
+
+	$sql .= " WHERE trans.supplier_id=supplier.supplier_id";
+
+	if ($extra_conditions)
+		$sql .= " AND $extra_conditions ";
+
+	return $sql;
 }
 
+
 //-------------------------------------------------------------------------------------------------------------
 
-function get_allocatable_from_supp_transactions($supplier_id, $settled)
+function get_allocatable_from_supp_sql($supplier_id, $settled)
 {
 	$settled_sql = "";
-	if (!$settled) 
+	if (!$settled)
 	{
 		$settled_sql = "AND round(ABS(ov_amount+ov_gst+ov_discount)-alloc,6) > 0";
 	}
-	
+
 	$supp_sql = "";
 	if ($supplier_id != null)
-		$supp_sql = " AND ".TB_PREF."supp_trans.supplier_id = $supplier_id";		
-	
-	return get_supplier_transactions("round(ABS(ov_amount+ov_gst+ov_discount)-alloc,6) <= 0 AS settled", 
-		"(type=22 OR type=21 OR type=1) AND (ov_amount < 0) " . $settled_sql . $supp_sql);	
+		$supp_sql = " AND trans.supplier_id = $supplier_id";
+
+	$sql = get_alloc_supp_sql("round(ABS(ov_amount+ov_gst+ov_discount)-alloc,6) <= 0 AS settled",
+		"(type=22 OR type=21 OR type=1) AND (ov_amount < 0) " . $settled_sql . $supp_sql);
+
+	return $sql;
 }
 
 //-------------------------------------------------------------------------------------------------------------
 
 function get_allocatable_to_supp_transactions($supplier_id, $trans_no=null, $type=null)
-{	
-	if ($trans_no != null && $type!= null) 
+{
+	if ($trans_no != null && $type!= null)
 	{
-		return get_supplier_transactions("amt", "".TB_PREF."supp_trans.trans_no = ".TB_PREF."supp_allocations.trans_no_to 
-			AND ".TB_PREF."supp_trans.type = ".TB_PREF."supp_allocations.trans_type_to
-			AND ".TB_PREF."supp_allocations.trans_no_from=$trans_no
-			AND ".TB_PREF."supp_allocations.trans_type_from=$type 
-			AND ".TB_PREF."supp_trans.supplier_id=$supplier_id", 
-			"".TB_PREF."supp_allocations");						
-	} 
-	else 
+		$sql = get_alloc_supp_sql("amt", "trans.trans_no = alloc.trans_no_to
+			AND trans.type = alloc.trans_type_to
+			AND alloc.trans_no_from=$trans_no
+			AND alloc.trans_type_from=$type
+			AND trans.supplier_id=$supplier_id",
+			"".TB_PREF."supp_allocations as alloc");
+	}
+	else
 	{
-		return get_supplier_transactions(null, "round(ABS(ov_amount+ov_gst+ov_discount)-alloc,6) > 0
-			AND ".TB_PREF."supp_trans.type != 22
-			AND ".TB_PREF."supp_trans.supplier_id=$supplier_id");
-	}															
+		$sql = get_alloc_supp_sql(null, "round(ABS(ov_amount+ov_gst+ov_discount)-alloc,6) > 0
+			AND trans.type != 22
+			AND trans.supplier_id=$supplier_id");
+	}
+
+	return db_query($sql." ORDER BY trans_no", "Cannot retreive alloc to transactions");
 }