From cd8bfeae7e776987d5dd2314180a11218639d26d Mon Sep 17 00:00:00 2001
From: Joe Hunt <joe.hunt.consulting@gmail.com>
Date: Thu, 10 Mar 2011 11:45:45 +0100
Subject: [PATCH] [0000608] Need better Negative stock prevention checking

---
 includes/db/inventory_db.inc | 71 +++++++++++++++++++++++-------------
 sales/customer_delivery.php  | 45 ++++++++++++++---------
 2 files changed, 73 insertions(+), 43 deletions(-)

diff --git a/includes/db/inventory_db.inc b/includes/db/inventory_db.inc
index 5336576..56e9419 100644
--- a/includes/db/inventory_db.inc
+++ b/includes/db/inventory_db.inc
@@ -11,35 +11,41 @@
 ***********************************************************************/
 function get_qoh_on_date($stock_id, $location=null, $date_=null, $exclude=0)
 {
-	if ($date_ == null)
-		$date_ = Today();
-
-	$date = date2sql($date_);
-
-	$sql = "SELECT SUM(qty) FROM ".TB_PREF."stock_moves
-		WHERE stock_id=".db_escape($stock_id)."
-		AND tran_date <= '$date'";
-
-	if ($location != null)
-		$sql .= " AND loc_code = ".db_escape($location);
+    if ($date_ == null)
+    {
+        $sql = "SELECT SUM(qty) FROM ".TB_PREF."stock_moves
+            WHERE stock_id=".db_escape($stock_id);
+        $date_ = Today();
+        $date = date2sql($date_);
+    }
+    else
+    {
+        $date = date2sql($date_);
+        $sql = "SELECT SUM(qty) FROM ".TB_PREF."stock_moves
+            WHERE stock_id=".db_escape($stock_id)."
+            AND tran_date <= '$date'"; 
+    }
+        
+    if ($location != null)
+        $sql .= " AND loc_code = ".db_escape($location);
 
-	$result = db_query($sql, "QOH calulcation failed");
+    $result = db_query($sql, "QOH calulcation failed");
 
-	$myrow = db_fetch_row($result);
-	if ($exclude > 0)
-	{
-		$sql = "SELECT SUM(qty) FROM ".TB_PREF."stock_moves
-			WHERE stock_id=".db_escape($stock_id)
-			." AND type=".db_escape($exclude)
-			." AND tran_date = '$date'";
-
-		$result = db_query($sql, "QOH calulcation failed");
-		$myrow2 = db_fetch_row($result);
-		if ($myrow2 !== false)
-			$myrow[0] -= $myrow2[0];
-	}
+    $myrow = db_fetch_row($result);
+    if ($exclude > 0)
+    {
+        $sql = "SELECT SUM(qty) FROM ".TB_PREF."stock_moves
+            WHERE stock_id=".db_escape($stock_id)
+            ." AND type=".db_escape($exclude)
+            ." AND tran_date = '$date'";
+
+        $result = db_query($sql, "QOH calulcation failed");
+        $myrow2 = db_fetch_row($result);
+        if ($myrow2 !== false)
+            $myrow[0] -= $myrow2[0];
+    }
 
-	return $myrow[0];
+    return $myrow[0];
 }
 
 //--------------------------------------------------------------------------------------
@@ -101,6 +107,19 @@ function last_negative_stock_begin_date($stock_id, $to)
 
 //-------------------------------------------------------------------
 // Newly written
+
+function get_already_delivered($stock_id, $location, $trans_no)
+{
+	$sql = "SELECT ".TB_PREF."stock_moves.qty
+		FROM ".TB_PREF."stock_moves
+		WHERE ".TB_PREF."stock_moves.stock_id = ".db_escape($stock_id)."
+		AND ".TB_PREF."stock_moves.loc_code = ".db_escape($location)."
+		AND type=".ST_CUSTDELIVERY." AND trans_no=".db_escape($trans_no);
+	$result = db_query($sql, "Could not get stock moves");
+	$row = db_fetch_row($result);
+	return $row[0];
+}
+
 function last_negative_stock_trans_id($stock_id, $to)
 {
 	$sql = "SELECT * from ".TB_PREF."stock_moves
diff --git a/sales/customer_delivery.php b/sales/customer_delivery.php
index d854552..c7e051e 100644
--- a/sales/customer_delivery.php
+++ b/sales/customer_delivery.php
@@ -266,24 +266,35 @@ function check_quantities()
 
 function check_qoh()
 {
-	global $SysPrefs;
-
-	if (!$SysPrefs->allow_negative_stock())	{
-		foreach ($_SESSION['Items']->line_items as $itm) {
-
-			if ($itm->qty_dispatched && has_stock_holding($itm->mb_flag)) {
-				$qoh = get_qoh_on_date($itm->stock_id, $_POST['Location'], $_POST['DispatchDate']);
-
-				if ($itm->qty_dispatched > $qoh) {
-					display_error(_("The delivery cannot be processed because there is an insufficient quantity for item:") .
-						" " . $itm->stock_id . " - " .  $itm->item_description);
-					return false;
-				}
-			}
-		}
-	}
-	return true;
+    global $SysPrefs;
+    $dn = &$_SESSION['Items'];
+    $newdelivery = ($dn->trans_no==0);
+    if (!$SysPrefs->allow_negative_stock()) {
+        foreach ($_SESSION['Items']->line_items as $itm) {
+
+            if ($itm->qty_dispatched && has_stock_holding($itm->mb_flag)) {
+                $qoh_by_date = get_qoh_on_date($itm->stock_id, $_POST['Location'], $_POST['DispatchDate']);
+                $qoh_abs = get_qoh_on_date($itm->stock_id, $_POST['Location'], null);
+                //If editing current delivery delivered qty should be added 
+                if (!$newdelivery)
+                {
+                    $delivered = get_already_delivered($itm->stock_id, $_POST['Location'], key($dn->trans_no));
+                    
+                    $qoh_abs = $qoh_abs - $delivered;
+                    $qoh_by_date = $qoh_by_date - $delivered;
+                }
+                $qoh = ($qoh_by_date < $qoh_abs ? $qoh_by_date : $qoh_abs); 
+                if ($itm->qty_dispatched > $qoh) {
+                    display_error(_("The delivery cannot be processed because there is an insufficient quantity for item:") .
+                        " " . $itm->stock_id . " - " . $itm->item_description);
+                    return false;
+                }
+            }
+        }
+    }
+    return true;
 }
+
 //------------------------------------------------------------------------------
 
 if (isset($_POST['process_delivery']) && check_data() && check_qoh()) {
-- 
2.30.2