X-Git-Url: https://delta.frontaccounting.com/gitweb/?a=blobdiff_plain;f=sales%2Fincludes%2Fdb%2Fcust_trans_db.inc;h=13493b73bf0d5a83ae8cc4fe776450e95d7d8e0d;hb=98a185d9959e4e273142042b354d42f77e013cdf;hp=c9fccf11522d61421f37cfa8943edcf13f52119e;hpb=acfca8f2a9dbcd41058c165adcfbfee32bbb02bd;p=fa-stable.git

diff --git a/sales/includes/db/cust_trans_db.inc b/sales/includes/db/cust_trans_db.inc
index c9fccf11..13493b73 100644
--- a/sales/includes/db/cust_trans_db.inc
+++ b/sales/includes/db/cust_trans_db.inc
@@ -9,38 +9,6 @@
     MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  
     See the License here <http://www.gnu.org/licenses/gpl-3.0.html>.
 ***********************************************************************/
-//------------------------------------------------------------------------------
-//	Retreive parent document number(s) for given transaction
-//
-function get_parent_trans($trans_type, $trans_no) {
-
-	$sql = 'SELECT trans_link FROM
-			'.TB_PREF.'debtor_trans WHERE
-			(trans_no='.db_escape($trans_no).' AND type='.db_escape($trans_type).' AND trans_link!=0)';
-
-	$result = db_query($sql, 'Parent document numbers cannot be retrieved');
-
-	if (db_num_rows($result)) {
-		$link = db_fetch($result);
-		return array($link['trans_link']);
-	}
-	if ($trans_type!=ST_SALESINVOICE) return 0;	// this is credit note with no parent invoice
-	// invoice: find batch invoice parent trans.
-	$sql = 'SELECT trans_no FROM
-			'.TB_PREF.'debtor_trans WHERE
-			(trans_link='.db_escape($trans_no).' AND type='. get_parent_type($trans_type) .')';
-
-	$result = db_query($sql, 'Delivery links cannot be retrieved');
-
-	$delivery = array();
-	if(db_num_rows($result)>0) {
-		while($link = db_fetch($result)) {
-			$delivery[] = $link['trans_no'];
-		}
-	}
-	return count($delivery) ? $delivery : 0;
-}
-
 //----------------------------------------------------------------------------------------
 // Mark changes in debtor_trans_details
 //
@@ -87,7 +55,7 @@ function get_customer_trans_version($type, $trans_no) {
 // date_ is display date (non-sql)
 function write_customer_trans($trans_type, $trans_no, $debtor_no, $BranchNo,
 	$date_, $reference, $Total, $discount=0, $Tax=0, $Freight=0, $FreightTax=0,
-	$sales_type=0, $order_no=0, $trans_link=0, $ship_via=0, $due_date="",
+	$sales_type=0, $order_no=0, $ship_via=0, $due_date="",
 	$AllocAmt=0, $rate=0, $dimension_id=0, $dimension2_id=0, $payment_terms=null)
 {
 	$new = $trans_no==0;
@@ -114,14 +82,14 @@ function write_customer_trans($trans_type, $trans_no, $debtor_no, $BranchNo,
 		reference, tpe,
 		order_, ov_amount, ov_discount,
 		ov_gst, ov_freight, ov_freight_tax,
-		rate, ship_via, alloc, trans_link,
+		rate, ship_via, alloc,
 		dimension_id, dimension2_id, payment_terms
 		) VALUES ($trans_no, ".db_escape($trans_type).",
 		".db_escape($debtor_no).", ".db_escape($BranchNo).",
 		'$SQLDate', '$SQLDueDate', ".db_escape($reference).",
 		".db_escape($sales_type).", ".db_escape($order_no).", $Total, ".db_escape($discount).", $Tax,
 		".db_escape($Freight).",
-		$FreightTax, $rate, ".db_escape($ship_via).", $AllocAmt, ".db_escape($trans_link).",
+		$FreightTax, $rate, ".db_escape($ship_via).", $AllocAmt,
 		".db_escape($dimension_id).", ".db_escape($dimension2_id).", "
 		.db_escape($payment_terms).")";
 	} else {	// may be optional argument should stay unchanged ?
@@ -131,7 +99,7 @@ function write_customer_trans($trans_type, $trans_no, $debtor_no, $BranchNo,
 		reference=".db_escape($reference).", tpe=".db_escape($sales_type).", order_=".db_escape($order_no).",
 		ov_amount=$Total, ov_discount=".db_escape($discount).", ov_gst=$Tax,
 		ov_freight=".db_escape($Freight).", ov_freight_tax=$FreightTax, rate=$rate,
-		ship_via=".db_escape($ship_via).", alloc=$AllocAmt, trans_link=$trans_link,
+		ship_via=".db_escape($ship_via).", alloc=$AllocAmt,
 		dimension_id=".db_escape($dimension_id).", dimension2_id=".db_escape($dimension2_id).",
 		payment_terms=".db_escape($payment_terms)."
 		WHERE trans_no=$trans_no AND type=".db_escape($trans_type);
@@ -145,12 +113,59 @@ function write_customer_trans($trans_type, $trans_no, $debtor_no, $BranchNo,
 
 //----------------------------------------------------------------------------------------
 
+function reinsert_customer_trans($trans_type, $trans_no, $debtor_no, $BranchNo,
+	$date_, $reference, $Total, $discount=0, $Tax=0, $Freight=0, $FreightTax=0,
+	$sales_type=0, $order_no=0, $ship_via=0, $due_date="",
+	$AllocAmt=0, $rate=0, $dimension_id=0, $dimension2_id=0)
+{
+	if ($trans_no == '')
+		display_db_error('Invalid call to function reinsert_customer_trans');
+		
+	$curr = get_customer_currency($debtor_no);
+	if ($rate == 0)
+		$rate = get_exchange_rate_from_home_currency($curr, $date_);
+
+	$SQLDate = date2sql($date_);
+	if ($due_date == "")
+		$SQLDueDate = "0000-00-00";
+	else
+		$SQLDueDate = date2sql($due_date);
+	
+	if ($trans_type == ST_BANKPAYMENT)
+		$Total = -$Total;
+
+	$sql = "INSERT INTO ".TB_PREF."debtor_trans (
+		trans_no, type,
+		debtor_no, branch_code,
+		tran_date, due_date,
+		reference, tpe,
+		order_, ov_amount, ov_discount,
+		ov_gst, ov_freight, ov_freight_tax,
+		rate, ship_via, alloc,
+		dimension_id, dimension2_id
+		) VALUES ($trans_no, ".db_escape($trans_type).",
+		".db_escape($debtor_no).", ".db_escape($BranchNo).",
+		'$SQLDate', '$SQLDueDate', ".db_escape($reference).",
+		".db_escape($sales_type).", ".db_escape($order_no).", $Total, ".db_escape($discount).", $Tax,
+		".db_escape($Freight).",
+		$FreightTax, $rate, ".db_escape($ship_via).", $AllocAmt,
+		".db_escape($dimension_id).", ".db_escape($dimension2_id).")";
+
+	db_query($sql, "The debtor transaction record could not be inserted");
+
+	add_audit_trail($trans_type, $trans_no, $date_, _("Updated."));
+
+	return $trans_no;
+}
+
+//----------------------------------------------------------------------------------------
+
 function get_customer_trans($trans_id, $trans_type)
 {
 	$sql = "SELECT trans.*,"
 		."ov_amount+ov_gst+ov_freight+ov_freight_tax+ov_discount AS Total,"
 		."cust.name AS DebtorName, cust.address, "
-		."cust.email AS email2, cust.curr_code, "
+		."cust.curr_code, "
 		."cust.tax_id ";
 
 	if ($trans_type == ST_CUSTPAYMENT) {
@@ -170,8 +185,6 @@ function get_customer_trans($trans_id, $trans_type)
 		.TB_PREF."tax_groups.id AS tax_group_id ";
 	}
 
-	$sql .= ", IF(ISNULL(branch.rep_lang),cust.rep_lang,branch.rep_lang) AS rep_lang";
-
 	$sql .= " FROM ".TB_PREF."debtor_trans trans, ".TB_PREF."debtors_master cust";
 
 	if ($trans_type == ST_CUSTPAYMENT) {
@@ -220,7 +233,6 @@ function get_customer_trans($trans_id, $trans_type)
 	}
 
 	$row = db_fetch($result);
-	$row['email'] = $row['email2'];
 	return $row;
 }
 
@@ -253,13 +265,6 @@ function get_customer_trans_order($type, $type_no)
 
 //----------------------------------------------------------------------------------------
 
-function get_related_documents($type, $trans_no)
-{
-	$sql = "SELECT * FROM ".TB_PREF."debtor_trans WHERE type=$type AND order_=".db_escape($trans_no);
-	return db_query($sql,"The related documents could not be retreived");
-}
-//----------------------------------------------------------------------------------------
-
 function get_customer_details_from_trans($type, $type_no)
 {
 	$sql = "SELECT ".TB_PREF."debtors_master.name, ".TB_PREF."debtors_master.curr_code, ".TB_PREF."cust_branch.br_name
@@ -285,6 +290,15 @@ function void_customer_trans($type, $type_no)
 
 //----------------------------------------------------------------------------------------
 
+function clear_customer_trans($type, $type_no)
+{
+	// Delete
+	$sql = "DELETE FROM ".TB_PREF."debtor_trans WHERE type=".db_escape($type)." AND trans_no=".db_escape($type_no);
+
+	db_query($sql, "could not clear debtor transactions for type=$type and trans_no=$type_no");
+}
+//----------------------------------------------------------------------------------------
+
 function post_void_customer_trans($type, $type_no)
 {
 	switch ($type) {
@@ -303,15 +317,6 @@ function post_void_customer_trans($type, $type_no)
 
 //----------------------------------------------------------------------------------------
 
-function get_customer_trans_link($type, $type_no)
-{
-	$row = db_query("SELECT trans_link from ".TB_PREF."debtor_trans
-		WHERE type=".db_escape($type)." AND trans_no=".db_escape($type_no),
-		"could not get transaction link for type=$type and trans_no=$type_no");
-	return $row[0];
-}
-//----------------------------------------------------------------------------------------
-
 function get_sql_for_customer_inquiry()
 {
     $date_after = date2sql($_POST['TransAfterDate']);
@@ -338,9 +343,12 @@ function get_sql_for_customer_inquiry()
 //			(trans.ov_amount + trans.ov_gst + trans.ov_freight + trans.ov_freight_tax + trans.ov_discount))) , ";
 		$sql .= "trans.alloc AS Allocated,
 		((trans.type = ".ST_SALESINVOICE.")
-			AND trans.due_date < '" . date2sql(Today()) . "') AS OverDue
+			AND trans.due_date < '" . date2sql(Today()) . "') AS OverDue ,
+		Sum(line.quantity-line.qty_done) AS Outstanding
 		FROM "
-			.TB_PREF."debtor_trans as trans, "
+			.TB_PREF."debtor_trans as trans
+			LEFT JOIN ".TB_PREF."debtor_trans_details as line
+				ON trans.trans_no=line.debtor_trans_no AND trans.type=line.debtor_trans_type,"
 			.TB_PREF."debtors_master as debtor, "
 			.TB_PREF."cust_branch as branch
 		WHERE debtor.debtor_no = trans.debtor_no
@@ -383,6 +391,8 @@ function get_sql_for_customer_inquiry()
 				trans.ov_freight + trans.ov_discount - trans.alloc > 0) ";
     	}
    	}
+	$sql .= " GROUP BY trans.trans_no, trans.type";
+
    	return $sql;
 }