X-Git-Url: https://delta.frontaccounting.com/gitweb/?a=blobdiff_plain;f=sales%2Fincludes%2Fdb%2Fsales_order_db.inc;h=0d8c618aaae685396a96ceb2c3951214febd3256;hb=777422e8d5a3ba188e7420719e33322098075313;hp=3cdceada63e519a81a63b75fb1d2152d8b6e1b6e;hpb=86e4d1627183fb4d97fe145a5dd33b43b03284ee;p=fa-stable.git

diff --git a/sales/includes/db/sales_order_db.inc b/sales/includes/db/sales_order_db.inc
index 3cdceada..0d8c618a 100644
--- a/sales/includes/db/sales_order_db.inc
+++ b/sales/includes/db/sales_order_db.inc
@@ -12,18 +12,20 @@
 //----------------------------------------------------------------------------------------
 function add_sales_order(&$order)
 {
-	global $loc_notification, $path_to_root;
+	global $loc_notification, $path_to_root, $Refs;
 
 	begin_transaction();
 
+	$order_no = get_next_trans_no($order->trans_type);
 	$del_date = date2sql($order->due_date);
 	$order_type = 0; // this is default on new order
-	$sql = "INSERT INTO ".TB_PREF."sales_orders (type, debtor_no, branch_code, customer_ref, comments, ord_date,
+	$sql = "INSERT INTO ".TB_PREF."sales_orders (order_no, type, debtor_no, trans_type, branch_code, customer_ref, reference, comments, ord_date,
 		order_type, ship_via, deliver_to, delivery_address, contact_phone,
 		contact_email, freight_cost, from_stk_loc, delivery_date)
-		VALUES (" .db_escape($order_type) . "," . db_escape($order->customer_id) .
-		 ", " . db_escape($order->Branch) . ", ".
+		VALUES (" .db_escape($order_no) . "," .db_escape($order_type) . "," . db_escape($order->customer_id) .
+		 ", " .db_escape($order->trans_type) . "," .db_escape($order->Branch) . ", ".
 			db_escape($order->cust_ref) .",". 
+			db_escape($order->reference) .",". 
 			db_escape($order->Comments) .",'" . 
 			date2sql($order->document_date) . "', " .
 			db_escape($order->sales_type) . ", " .
@@ -38,7 +40,6 @@ function add_sales_order(&$order)
 
 	db_query($sql, "order Cannot be Added");
 
-	$order_no = db_insert_id();
 	$order->trans_no = array($order_no=>0);
 
 	if ($loc_notification == 1)
@@ -76,8 +77,8 @@ function add_sales_order(&$order)
 			}
 		}
 
-		$sql = "INSERT INTO ".TB_PREF."sales_order_details (order_no, stk_code, description, unit_price, quantity, discount_percent) VALUES (";
-		$sql .= $order_no .
+		$sql = "INSERT INTO ".TB_PREF."sales_order_details (order_no, trans_type, stk_code, description, unit_price, quantity, discount_percent) VALUES (";
+		$sql .= $order_no . ",".$order->trans_type .
 				",".db_escape($line->stock_id).", "
 				.db_escape($line->item_description).", $line->price,
 				$line->quantity,
@@ -86,6 +87,8 @@ function add_sales_order(&$order)
 
 	} /* inserted line items into sales order details */
 
+	add_audit_trail($order->trans_type, $order_no, $order->document_date);
+	$Refs->save($order->trans_type, $order_no, $order->reference);
 	commit_transaction();
 
 	if ($loc_notification == 1 && count($st_ids) > 0)
@@ -111,16 +114,20 @@ function add_sales_order(&$order)
 
 //----------------------------------------------------------------------------------------
 
-function delete_sales_order($order_no)
+function delete_sales_order($order_no, $trans_type)
 {
 	begin_transaction();
 
-	$sql = "DELETE FROM ".TB_PREF."sales_orders WHERE order_no=" . $order_no;
+	$sql = "DELETE FROM ".TB_PREF."sales_orders WHERE order_no=" . db_escape($order_no) 
+		. " AND trans_type=".db_escape($trans_type);
+
 	db_query($sql, "order Header Delete");
 
-	$sql = "DELETE FROM ".TB_PREF."sales_order_details WHERE order_no =" . $order_no;
+	$sql = "DELETE FROM ".TB_PREF."sales_order_details WHERE order_no =" 
+		.db_escape($order_no) . " AND trans_type=".db_escape($trans_type);
 	db_query($sql, "order Detail Delete");
 
+	add_audit_trail($trans_type, $order_no, Today(), _("Deleted."));
 	commit_transaction();
 }
 
@@ -131,7 +138,7 @@ function update_sales_order_version($order)
 {
   foreach ($order as $so_num => $so_ver) {
   $sql= 'UPDATE '.TB_PREF.'sales_orders SET version=version+1 WHERE order_no='. $so_num.
-	' AND version='.$so_ver;
+	' AND version='.$so_ver . " AND trans_type=30";
   db_query($sql, 'Concurrent editing conflict while sales order update');
   }
 }
@@ -140,7 +147,7 @@ function update_sales_order_version($order)
 
 function update_sales_order($order)
 {
-	global $loc_notification, $path_to_root;
+	global $loc_notification, $path_to_root, $Refs;
 
 	$del_date = date2sql($order->due_date);
 	$ord_date = date2sql($order->document_date);
@@ -149,10 +156,11 @@ function update_sales_order($order)
 
 	begin_transaction();
 
-	$sql = "UPDATE ".TB_PREF."sales_orders SET type =".$order->so_type." ,
+	$sql = "UPDATE ".TB_PREF."sales_orders SET type =".db_escape($order->so_type)." ,
 		debtor_no = " . db_escape($order->customer_id) . ",
 		branch_code = " . db_escape($order->Branch) . ",
 		customer_ref = ". db_escape($order->cust_ref) .",
+		reference = ". db_escape($order->reference) .",
 		comments = ". db_escape($order->Comments) .",
 		ord_date = " . db_escape($ord_date) . ",
 		order_type = " .db_escape($order->sales_type) . ",
@@ -166,10 +174,10 @@ function update_sales_order($order)
 		delivery_date = " .db_escape($del_date). ",
 		version = ".($version+1)."
 	 WHERE order_no=" . $order_no ."
-	 AND version=".$version;
+	 AND trans_type=".$order->trans_type." AND version=".$version;
 	db_query($sql, "order Cannot be Updated, this can be concurrent edition conflict");
 
-	$sql = "DELETE FROM ".TB_PREF."sales_order_details WHERE order_no =" . $order_no;
+	$sql = "DELETE FROM ".TB_PREF."sales_order_details WHERE order_no =" . $order_no . " AND trans_type=".$order->trans_type;
 
 	db_query($sql, "Old order Cannot be Deleted");
 
@@ -191,8 +199,8 @@ function update_sales_order($order)
 				FROM ".TB_PREF."loc_stock, "
 				  .TB_PREF."locations
 				WHERE ".TB_PREF."loc_stock.loc_code=".TB_PREF."locations.loc_code
-				 AND ".TB_PREF."loc_stock.stock_id = '" . $line->stock_id . "'
-				 AND ".TB_PREF."loc_stock.loc_code = '" . $order->Location . "'";
+				 AND ".TB_PREF."loc_stock.stock_id = ".db_escape($line->stock_id)."
+				 AND ".TB_PREF."loc_stock.loc_code = ".db_escape($order->Location);
 			$res = db_query($sql,"a location could not be retreived");
 			$loc = db_fetch($res);
 			if ($loc['email'] != "")
@@ -211,10 +219,10 @@ function update_sales_order($order)
 			}
 		}
 		$sql = "INSERT INTO ".TB_PREF."sales_order_details
-		 (order_no, stk_code,  description, unit_price, quantity,
+		 (order_no, trans_type, stk_code,  description, unit_price, quantity,
 		  discount_percent, qty_sent)
 		 VALUES (";
-		$sql .= $order_no . ","
+		$sql .= $order_no . ",".$order->trans_type.","
 		  .db_escape($line->stock_id) . ","
 		  .db_escape($line->item_description) . ", "
 		  .db_escape($line->price) . ", "
@@ -226,6 +234,9 @@ function update_sales_order($order)
 
 	} /* inserted line items into sales order details */
 
+	add_audit_trail($order->trans_type, $order_no, $order->document_date, _("Updated."));
+	$Refs->delete($order->trans_type, $order_no);
+	$Refs->save($order->trans_type, $order_no, $order->reference);
 	commit_transaction();
 	if ($loc_notification == 1 && count($st_ids) > 0)
 	{
@@ -251,7 +262,7 @@ function update_sales_order($order)
 
 //----------------------------------------------------------------------------------------
 
-function get_sales_order_header($order_no)
+function get_sales_order_header($order_no, $trans_type)
 {
 	$sql = "SELECT ".TB_PREF."sales_orders.*, "
 	  .TB_PREF."debtors_master.name, "
@@ -279,7 +290,9 @@ function get_sales_order_header($order_no)
 		AND ".TB_PREF."sales_orders.debtor_no = ".TB_PREF."debtors_master.debtor_no
 		AND ".TB_PREF."locations.loc_code = ".TB_PREF."sales_orders.from_stk_loc
 		AND ".TB_PREF."shippers.shipper_id = ".TB_PREF."sales_orders.ship_via
-		AND ".TB_PREF."sales_orders.order_no = " . $order_no ;
+		AND ".TB_PREF."sales_orders.trans_type = " . db_escape($trans_type) ."
+		AND ".TB_PREF."sales_orders.order_no = " . db_escape($order_no );
+
 	$result = db_query($sql, "order Retreival");
 
 	$num = db_num_rows($result);
@@ -298,7 +311,7 @@ function get_sales_order_header($order_no)
 
 //----------------------------------------------------------------------------------------
 
-function get_sales_order_details($order_no) {
+function get_sales_order_details($order_no, $trans_type) {
 	$sql = "SELECT id, stk_code, unit_price, "
 		.TB_PREF."sales_order_details.description,"
 		.TB_PREF."sales_order_details.quantity,
@@ -310,17 +323,18 @@ function get_sales_order_details($order_no) {
 			.TB_PREF."stock_master.overhead_cost AS standard_cost
 	FROM ".TB_PREF."sales_order_details, ".TB_PREF."stock_master
 	WHERE ".TB_PREF."sales_order_details.stk_code = ".TB_PREF."stock_master.stock_id
-	AND order_no =" . $order_no . " ORDER BY id";
+	AND order_no =" . db_escape($order_no) 
+		." AND trans_type = " . db_escape($trans_type) . " ORDER BY id";
 
 	return db_query($sql, "Retreive order Line Items");
 }
 //----------------------------------------------------------------------------------------
 
-function read_sales_order($order_no, &$order)
+function read_sales_order($order_no, &$order, $trans_type)
 {
-	$myrow = get_sales_order_header($order_no);
+	$myrow = get_sales_order_header($order_no, $trans_type);
 
-	$order->trans_type = 30;
+	$order->trans_type = $myrow['trans_type'];
 	$order->so_type =  $myrow["type"];
 	$order->trans_no = array($order_no=> $myrow["version"]);
 
@@ -340,11 +354,12 @@ function read_sales_order($order_no, &$order)
 
 	$order->cust_ref = $myrow["customer_ref"];
 	$order->sales_type =$myrow["order_type"];
+	$order->reference = $myrow["reference"];
 	$order->Comments = $myrow["comments"];
 	$order->due_date = sql2date($myrow["delivery_date"]);
 	$order->document_date = sql2date($myrow["ord_date"]);
 
-	$result = get_sales_order_details($order_no);
+	$result = get_sales_order_details($order_no, $order->trans_type);
 	if (db_num_rows($result) > 0)
 	{
 		$line_no=0;
@@ -365,7 +380,8 @@ function read_sales_order($order_no, &$order)
 function sales_order_has_deliveries($order_no)
 {
 	$sql = "SELECT SUM(qty_sent) FROM ".TB_PREF.
-	"sales_order_details WHERE order_no=$order_no";
+	"sales_order_details WHERE order_no=".db_escape($order_no)
+	." AND trans_type=".ST_SALESORDER."";
 
 	$result = db_query($sql, "could not query for sales order usage");
 
@@ -378,9 +394,10 @@ function sales_order_has_deliveries($order_no)
 
 function close_sales_order($order_no)
 {
- 	// set the quantity of each item to the already sent quantity. this will mark item as closed.
+	// set the quantity of each item to the already sent quantity. this will mark item as closed.
 	$sql = "UPDATE ".TB_PREF."sales_order_details
-		SET quantity = qty_sent	WHERE order_no = $order_no";
+		SET quantity = qty_sent WHERE order_no = ".db_escape($order_no)
+		." AND trans_type=".ST_SALESORDER."";
 
 	db_query($sql, "The sales order detail record could not be updated");
 }
@@ -391,11 +408,11 @@ function get_invoice_duedate($debtorno, $invdate)
 {
 	if (!is_date($invdate))
 	{
-		return Today();
+		return new_doc_date();
 	}
 	$sql = "SELECT ".TB_PREF."debtors_master.debtor_no, ".TB_PREF."debtors_master.payment_terms, ".TB_PREF."payment_terms.* FROM ".TB_PREF."debtors_master,
 		".TB_PREF."payment_terms WHERE ".TB_PREF."debtors_master.payment_terms = ".TB_PREF."payment_terms.terms_indicator AND
-		".TB_PREF."debtors_master.debtor_no = '$debtorno'";
+		".TB_PREF."debtors_master.debtor_no = ".db_escape($debtorno);
 
 	$result = db_query($sql,"The customer details could not be retrieved");
 	$myrow = db_fetch($result);
@@ -430,7 +447,7 @@ function get_customer_to_order($customer_id) {
 		WHERE ".TB_PREF."debtors_master.sales_type="
 		  .TB_PREF."sales_types.id
 		AND ".TB_PREF."debtors_master.credit_status=".TB_PREF."credit_status.id
-		AND ".TB_PREF."debtors_master.debtor_no = '" . $customer_id . "'";
+		AND ".TB_PREF."debtors_master.debtor_no = ".db_escape($customer_id);
 
 	$result =db_query($sql,"Customer Record Retreive");
 	return 	db_fetch($result);
@@ -452,8 +469,8 @@ function get_branch_to_order($customer_id, $branch_id) {
 			  .TB_PREF."locations
 			WHERE ".TB_PREF."cust_branch.tax_group_id = ".TB_PREF."tax_groups.id
 				AND ".TB_PREF."locations.loc_code=default_location
-				AND ".TB_PREF."cust_branch.branch_code='" . $branch_id . "'
-				AND ".TB_PREF."cust_branch.debtor_no = '" . $customer_id . "'";
+				AND ".TB_PREF."cust_branch.branch_code=".db_escape($branch_id)."
+				AND ".TB_PREF."cust_branch.debtor_no = ".db_escape($customer_id);
 
   	    return db_query($sql,"Customer Branch Record Retreive");
 }