Menu
▾
▴
[Web-erp-svn] SF.net SVN: web-erp:[6907] trunk
|
From: <te...@us...> - 2014-10-05 13:15:00
|
Revision: 6907
http://sourceforge.net/p/web-erp/reponame/6907
Author: tehonu
Date: 2014-10-05 13:14:49 +0000 (Sun, 05 Oct 2014)
Log Message:
-----------
Use of GetQuantityOnOrderDueToPurchaseOrders() and GetQuantityOnOrderDueToWorkOrders() to calculate QOO
Modified Paths:
--------------
trunk/CounterReturns.php
trunk/CounterSales.php
trunk/InventoryPlanning.php
trunk/InventoryPlanningPrefSupplier.php
trunk/SelectOrderItems.php
trunk/SelectProduct.php
trunk/StockLocStatus.php
trunk/StockStatus.php
trunk/TopItems.php
trunk/includes/SQL_CommonFunctions.inc
Modified: trunk/CounterReturns.php
===================================================================
--- trunk/CounterReturns.php 2014-10-04 03:40:57 UTC (rev 6906)
+++ trunk/CounterReturns.php 2014-10-05 13:14:49 UTC (rev 6907)
@@ -1724,39 +1724,11 @@
$DemandQty = 0;
}
- // Find the quantity on purchase orders
- $sql = "SELECT SUM(purchorderdetails.quantityord-purchorderdetails.quantityrecd) AS QOO
- FROM purchorderdetails INNER JOIN purchorders
- ON purchorderdetails.orderno=purchorders.orderno
- WHERE purchorderdetails.completed=0
- AND purchorders.status <>'Cancelled'
- AND purchorders.status <>'Rejected'
- AND purchorderdetails.itemcode='" . $myrow['stockid'] . "'";
+ // Get the QOO due to Purchase orders for all locations. Function defined in SQL_CommonFunctions.inc
+ $QOO= GetQuantityOnOrderDueToPurchaseOrders($myrow['stockid'], "", $db);
+ // Get the QOO dues to Work Orders for all locations. Function defined in SQL_CommonFunctions.inc
+ $QOO += GetQuantityOnOrderDueToWorkOrders($myrow['stockid'], "", $db);
- $ErrMsg = _('The order details for this product cannot be retrieved because');
- $PurchResult = DB_query($sql,$db,$ErrMsg);
-
- $PurchRow = DB_fetch_row($PurchResult);
- if ($PurchRow[0]!=null){
- $PurchQty = $PurchRow[0];
- } else {
- $PurchQty = 0;
- }
-
- // Find the quantity on works orders
- $sql = "SELECT SUM(woitems.qtyreqd - woitems.qtyrecd) AS dedm
- FROM woitems
- WHERE stockid='" . $myrow['stockid'] ."'";
- $ErrMsg = _('The order details for this product cannot be retrieved because');
- $WoResult = DB_query($sql,$db,$ErrMsg);
-
- $WoRow = DB_fetch_row($WoResult);
- if ($WoRow[0]!=null){
- $WoQty = $WoRow[0];
- } else {
- $WoQty = 0;
- }
-
if ($k==1){
echo '<tr class="EvenTableRows">';
$k=0;
@@ -1764,9 +1736,8 @@
echo '<tr class="OddTableRows">';
$k=1;
}
- $OnOrder = $PurchQty + $WoQty;
- $Available = $QOH - $DemandQty + $OnOrder;
+ $Available = $QOH - $DemandQty + $QOO;
printf('<td>%s</td>
<td title="%s">%s</td>
@@ -1783,7 +1754,7 @@
$myrow['units'],
locale_number_format($QOH, $myrow['decimalplaces']),
locale_number_format($DemandQty, $myrow['decimalplaces']),
- locale_number_format($OnOrder, $myrow['decimalplaces']),
+ locale_number_format($QOO, $myrow['decimalplaces']),
locale_number_format($Available, $myrow['decimalplaces']),
$i,
$i,
Modified: trunk/CounterSales.php
===================================================================
--- trunk/CounterSales.php 2014-10-04 03:40:57 UTC (rev 6906)
+++ trunk/CounterSales.php 2014-10-05 13:14:49 UTC (rev 6907)
@@ -1183,29 +1183,11 @@
$AssemblyDemandRow = DB_fetch_row($AssemblyDemandResult);
$QuantityAssemblyDemand = $AssemblyDemandRow[0];
- $SQL = "SELECT SUM(purchorderdetails.quantityord - purchorderdetails.quantityrecd) as qtyonorder
- FROM purchorderdetails INNER JOIN purchorders
- ON purchorderdetails.orderno = purchorders.orderno
- WHERE purchorderdetails.itemcode = '" . $StockItem->StockID . "'
- AND purchorderdetails.completed = 0
- AND purchorders.status<>'Cancelled'
- AND purchorders.status<>'Rejected'
- AND purchorders.status<>'Pending'
- AND purchorders.status<>'Completed'";
- $PurchOrdersResult = DB_query($SQL,$db);
- $PurchOrdersRow = DB_fetch_row($PurchOrdersResult);
- $QuantityPurchOrders = $PurchOrdersRow[0];
+ // Get the QOO due to Purchase orders for all locations. Function defined in SQL_CommonFunctions.inc
+ $QuantityPurchOrders= GetQuantityOnOrderDueToPurchaseOrders($StockItem->StockID, "", $db);
+ // Get the QOO dues to Work Orders for all locations. Function defined in SQL_CommonFunctions.inc
+ $QuantityWorkOrders = GetQuantityOnOrderDueToWorkOrders($StockItem->StockID, "", $db);
- $SQL = "SELECT SUM(woitems.qtyreqd - woitems.qtyrecd) as qtyonorder
- FROM woitems INNER JOIN workorders
- ON woitems.wo=workorders.wo
- WHERE woitems.stockid = '" . $StockItem->StockID . "'
- AND woitems.qtyreqd > woitems.qtyrecd
- AND workorders.closed = 0";
- $WorkOrdersResult = DB_query($SQL,$db);
- $WorkOrdersRow = DB_fetch_row($WorkOrdersResult);
- $QuantityWorkOrders = $WorkOrdersRow[0];
-
//Now we have the data - do we need to make any more?
$ShortfallQuantity = $QOH-$QuantityDemand-$QuantityAssemblyDemand+$QuantityPurchOrders+$QuantityWorkOrders;
@@ -2153,38 +2135,12 @@
} else {
$DemandQty = 0;
}
- // Find the quantity on purchase orders
- $sql = "SELECT SUM(purchorderdetails.quantityord-purchorderdetails.quantityrecd) AS QOO
- FROM purchorderdetails INNER JOIN purchorders
- ON purchorderdetails.orderno=purchorders.orderno
- WHERE purchorderdetails.completed=0
- AND purchorders.status<>'Cancelled'
- AND purchorders.status<>'Rejected'
- AND purchorderdetails.itemcode='" . $myrow['stockid'] . "'";
- $ErrMsg = _('The order details for this product cannot be retrieved because');
- $PurchResult = DB_query($sql,$db,$ErrMsg);
+ // Get the QOO due to Purchase orders for all locations. Function defined in SQL_CommonFunctions.inc
+ $QOO = GetQuantityOnOrderDueToPurchaseOrders($myrow['stockid'], "", $db);
+ // Get the QOO dues to Work Orders for all locations. Function defined in SQL_CommonFunctions.inc
+ $QOO += GetQuantityOnOrderDueToWorkOrders($myrow['stockid'], "", $db);
- $PurchRow = DB_fetch_row($PurchResult);
- if ($PurchRow[0]!=null){
- $PurchQty = $PurchRow[0];
- } else {
- $PurchQty = 0;
- }
-
- // Find the quantity on works orders
- $sql = "SELECT SUM(woitems.qtyreqd - woitems.qtyrecd) AS dedm
- FROM woitems
- WHERE stockid='" . $myrow['stockid'] ."'";
- $ErrMsg = _('The order details for this product cannot be retrieved because');
- $WoResult = DB_query($sql,$db,$ErrMsg);
- $WoRow = DB_fetch_row($WoResult);
- if ($WoRow[0]!=null){
- $WoQty = $WoRow[0];
- } else {
- $WoQty = 0;
- }
-
if ($k==1){
echo '<tr class="EvenTableRows">';
$k=0;
@@ -2192,9 +2148,8 @@
echo '<tr class="OddTableRows">';
$k=1;
}
- $OnOrder = $PurchQty + $WoQty;
- $Available = $QOH - $DemandQty + $OnOrder;
+ $Available = $QOH - $DemandQty + $QOO;
printf('<td>%s</td>
<td>%s</td>
@@ -2212,7 +2167,7 @@
$myrow['units'],
$QOH,
$DemandQty,
- $OnOrder,
+ $QOO,
$Available,
$i,
$i,
@@ -2332,39 +2287,11 @@
$DemandQty = 0;
}
- // Find the quantity on purchase orders
- $sql = "SELECT SUM(purchorderdetails.quantityord-purchorderdetails.quantityrecd) AS QOO
- FROM purchorderdetails INNER JOIN purchorders
- WHERE purchorderdetails.completed=0
- AND purchorders.status <>'Cancelled'
- AND purchorders.status <>'Rejected'
- AND purchorders.status <>'Completed'
- AND purchorderdetails.itemcode='" . $myrow['stockid'] . "'";
+ // Get the QOO due to Purchase orders for all locations. Function defined in SQL_CommonFunctions.inc
+ $QOO = GetQuantityOnOrderDueToPurchaseOrders($myrow['stockid'], "", $db);
+ // Get the QOO dues to Work Orders for all locations. Function defined in SQL_CommonFunctions.inc
+ $QOO += GetQuantityOnOrderDueToWorkOrders($myrow['stockid'], "", $db);
- $ErrMsg = _('The order details for this product cannot be retrieved because');
- $PurchResult = DB_query($sql,$db,$ErrMsg);
-
- $PurchRow = DB_fetch_row($PurchResult);
- if ($PurchRow[0]!=null){
- $PurchQty = $PurchRow[0];
- } else {
- $PurchQty = 0;
- }
-
- // Find the quantity on works orders
- $sql = "SELECT SUM(woitems.qtyreqd - woitems.qtyrecd) AS dedm
- FROM woitems
- WHERE stockid='" . $myrow['stockid'] ."'";
- $ErrMsg = _('The order details for this product cannot be retrieved because');
- $WoResult = DB_query($sql,$db,$ErrMsg);
-
- $WoRow = DB_fetch_row($WoResult);
- if ($WoRow[0]!=null){
- $WoQty = $WoRow[0];
- } else {
- $WoQty = 0;
- }
-
if ($k==1){
echo '<tr class="EvenTableRows">';
$k=0;
@@ -2372,9 +2299,8 @@
echo '<tr class="OddTableRows">';
$k=1;
}
- $OnOrder = $PurchQty + $WoQty;
- $Available = $QOH - $DemandQty + $OnOrder;
+ $Available = $QOH - $DemandQty + $QOO;
printf('<td>%s</td>
<td>%s</td>
@@ -2390,7 +2316,7 @@
$myrow['units'],
locale_number_format($QOH, $myrow['decimalplaces']),
locale_number_format($DemandQty, $myrow['decimalplaces']),
- locale_number_format($OnOrder, $myrow['decimalplaces']),
+ locale_number_format($QOO, $myrow['decimalplaces']),
locale_number_format($Available, $myrow['decimalplaces']),
$i,
$i,
Modified: trunk/InventoryPlanning.php
===================================================================
--- trunk/InventoryPlanning.php 2014-10-04 03:40:57 UTC (rev 6906)
+++ trunk/InventoryPlanning.php 2014-10-05 13:14:49 UTC (rev 6907)
@@ -7,6 +7,8 @@
$ViewTopic= "Inventory";
$BookMark = "PlanningReport";
+include ('includes/SQL_CommonFunctions.inc');
+
if (isset($_POST['PrintPDF'])
and isset($_POST['FromCriteria'])
and mb_strlen($_POST['FromCriteria'])>=1
@@ -274,50 +276,20 @@
exit;
}
+ // Get the QOO due to Purchase orders for all locations. Function defined in SQL_CommonFunctions.inc
+ // Get the QOO dues to Work Orders for all locations. Function defined in SQL_CommonFunctions.inc
if ($_POST['Location']=='All'){
- $SQL = "SELECT SUM(purchorderdetails.quantityord - purchorderdetails.quantityrecd) as qtyonorder
- FROM purchorderdetails INNER JOIN purchorders
- ON purchorderdetails.orderno = purchorders.orderno
- INNER JOIN locationusers ON locationusers.loccode=purchorders.intostocklocation AND locationusers.userid='" . $_SESSION['UserID'] . "' AND locationusers.canview=1
- WHERE purchorderdetails.itemcode = '" . $InventoryPlan['stockid'] . "'
- AND purchorderdetails.completed = 0
- AND purchorders.status <> 'Cancelled'
- AND purchorders.status <> 'Rejected'
- AND purchorders.status <> 'Pending'
- AND purchorders.status <> 'Completed'";
+ $QOO = GetQuantityOnOrderDueToPurchaseOrders($InventoryPlan['stockid'], "", $db);
+ $QOO += GetQuantityOnOrderDueToWorkOrders($InventoryPlan['stockid'], "", $db);
} else {
- $SQL = "SELECT SUM(purchorderdetails.quantityord - purchorderdetails.quantityrecd) as qtyonorder
- FROM purchorderdetails INNER JOIN purchorders
- ON purchorderdetails.orderno = purchorders.orderno
- INNER JOIN locationusers ON locationusers.loccode=purchorders.intostocklocation AND locationusers.userid='" . $_SESSION['UserID'] . "' AND locationusers.canview=1
- WHERE purchorderdetails.itemcode = '" . $InventoryPlan['stockid'] . "'
- AND purchorderdetails.completed = 0
- AND purchorders.intostocklocation= '" . $_POST['Location'] . "'
- AND purchorders.status <> 'Cancelled'
- AND purchorders.status <> 'Rejected'
- AND purchorders.status <> 'Pending'
- AND purchorders.status <> 'Completed'";
+ $QOO = GetQuantityOnOrderDueToPurchaseOrders($InventoryPlan['stockid'], $_POST['Location'], $db);
+ $QOO += GetQuantityOnOrderDueToWorkOrders($InventoryPlan['stockid'], $_POST['Location'], $db);
}
$DemandRow = DB_fetch_array($DemandResult);
$BOMDemandRow = DB_fetch_array($BOMDemandResult);
$TotalDemand = $DemandRow['qtydemand'] + $BOMDemandRow['dem'];
- $OnOrdResult = DB_query($SQL,$db,'','',false,false);
- if (DB_error_no($db) !=0) {
- $Title = _('Inventory Planning') . ' - ' . _('Problem Report') . '....';
- include('includes/header.inc');
- prnMsg( _('The purchase order quantities could not be retrieved by the SQL because') . ' - ' . DB_error_msg($db),'error');
- echo '<br /><a href="' .$RootPath .'/index.php">' . _('Back to the menu') . '</a>';
- if ($debug==1){
- echo '<br />' . $SQL;
- }
- include('includes/footer.inc');
- exit;
- }
-
- $OnOrdRow = DB_fetch_array($OnOrdResult);
-
$LeftOvers = $pdf->addTextWrap($Left_Margin, $YPos, 110, $FontSize, $InventoryPlan['stockid'], 'left');
$LeftOvers = $pdf->addTextWrap(130, $YPos, 120,6,$InventoryPlan['description'],'left');
$LeftOvers = $pdf->addTextWrap(251, $YPos, 40,$FontSize,locale_number_format($SalesRow['prd5'],0),'right');
@@ -343,9 +315,9 @@
$LeftOvers = $pdf->addTextWrap(597, $YPos, 40,$FontSize,locale_number_format($InventoryPlan['qoh'],0),'right');
$LeftOvers = $pdf->addTextWrap(638, $YPos, 40,$FontSize,locale_number_format($TotalDemand,0),'right');
- $LeftOvers = $pdf->addTextWrap(679, $YPos, 40,$FontSize,locale_number_format($OnOrdRow['qtyonorder'],0),'right');
+ $LeftOvers = $pdf->addTextWrap(679, $YPos, 40,$FontSize,locale_number_format($QOO,0),'right');
- $SuggestedTopUpOrder = $IdealStockHolding - $InventoryPlan['qoh'] + $TotalDemand - $OnOrdRow['qtyonorder'];
+ $SuggestedTopUpOrder = $IdealStockHolding - $InventoryPlan['qoh'] + $TotalDemand - $QOO;
if ($SuggestedTopUpOrder <=0){
$LeftOvers = $pdf->addTextWrap(720, $YPos, 40,$FontSize,' ','right');
Modified: trunk/InventoryPlanningPrefSupplier.php
===================================================================
--- trunk/InventoryPlanningPrefSupplier.php 2014-10-04 03:40:57 UTC (rev 6906)
+++ trunk/InventoryPlanningPrefSupplier.php 2014-10-05 13:14:49 UTC (rev 6907)
@@ -97,6 +97,7 @@
}
include('includes/session.inc');
+include ('includes/SQL_CommonFunctions.inc');
if (isset($_POST['PrintPDF'])){
@@ -142,59 +143,38 @@
/*Now figure out the inventory data to report for the category range under review
need QOH, QOO, QDem, Sales Mth -1, Sales Mth -2, Sales Mth -3, Sales Mth -4*/
+ $SQL = "SELECT stockmaster.description,
+ stockmaster.eoq,
+ locstock.stockid,
+ purchdata.supplierno,
+ suppliers.suppname,
+ purchdata.leadtime/30 AS monthsleadtime,
+ SUM(locstock.quantity) AS qoh
+ FROM locstock
+ INNER JOIN locationusers
+ ON locationusers.loccode=locstock.loccode
+ AND locationusers.userid='" . $_SESSION['UserID'] . "' AND locationusers.canview=1,
+ stockmaster,
+ purchdata,
+ suppliers
+ WHERE locstock.stockid=stockmaster.stockid
+ AND purchdata.supplierno=suppliers.supplierid
+ AND (stockmaster.mbflag='B' OR stockmaster.mbflag='M')
+ AND purchdata.stockid=stockmaster.stockid
+ AND purchdata.preferred=1";
if ($_POST['Location']=='All'){
-
- $SQL = "SELECT stockmaster.description,
- stockmaster.eoq,
- locstock.stockid,
- purchdata.supplierno,
- suppliers.suppname,
- purchdata.leadtime/30 AS monthsleadtime,
- SUM(locstock.quantity) AS qoh
- FROM locstock
- INNER JOIN locationusers ON locationusers.loccode=locstock.loccode AND locationusers.userid='" . $_SESSION['UserID'] . "' AND locationusers.canview=1,
- stockmaster,
- purchdata,
- suppliers
- WHERE locstock.stockid=stockmaster.stockid
- AND purchdata.supplierno=suppliers.supplierid
- AND (stockmaster.mbflag='B' OR stockmaster.mbflag='M')
- AND purchdata.stockid=stockmaster.stockid
- AND purchdata.preferred=1
- GROUP BY
- purchdata.supplierno,
- stockmaster.description,
- stockmaster.eoq,
- locstock.stockid
- ORDER BY purchdata.supplierno,
- stockmaster.stockid";
-
+ $SQL .= " GROUP BY
+ purchdata.supplierno,
+ stockmaster.description,
+ stockmaster.eoq,
+ locstock.stockid
+ ORDER BY purchdata.supplierno,
+ stockmaster.stockid";
} else {
-
- $SQL = "SELECT
- stockmaster.description,
- stockmaster.eoq,
- purchdata.supplierno,
- suppliers.suppname,
- locstock.stockid,
- purchdata.leadtime/30 AS monthsleadtime,
- locstock.quantity AS qoh
- FROM locstock
- INNER JOIN locationusers ON locationusers.loccode=locstock.loccode AND locationusers.userid='" . $_SESSION['UserID'] . "' AND locationusers.canview=1,
- stockmaster,
- purchdata,
- suppliers
- WHERE locstock.stockid=stockmaster.stockid
- AND purchdata.supplierno=suppliers.supplierid
- AND (stockmaster.mbflag='B' OR stockmaster.mbflag='M')
- AND purchdata.stockid=stockmaster.stockid
- AND locstock.loccode = '" . $_POST['Location'] . "'
- AND purchdata.preferred=1
- ORDER BY purchdata.supplierno,
- stockmaster.stockid";
-
-
+ $SQL .= " AND locstock.loccode = '" . $_POST['Location'] . "'
+ ORDER BY purchdata.supplierno,
+ stockmaster.stockid";
}
$InventoryResult = DB_query($SQL, $db, '', '', false, false);
$ListCount = DB_num_rows($InventoryResult);
@@ -221,7 +201,6 @@
$Period_3 = $CurrentPeriod -3;
$Period_4 = $CurrentPeriod -4;
-
while ($InventoryPlan = DB_fetch_array($InventoryResult,$db)){
if ($SupplierID!=$InventoryPlan['supplierno']){
@@ -239,32 +218,21 @@
$YPos -=$line_height;
-
- if ($_POST['Location']=='All'){
- $SQL = "SELECT SUM(CASE WHEN (prd>='" . $Period_1 . "' OR
- prd<='" . $Period_4 . "') THEN -qty ELSE 0 END) AS 4mthtotal,
- SUM(CASE WHEN prd='" . $Period_1 . "' THEN -qty ELSE 0 END) AS prd1,
- SUM(CASE WHEN prd='" . $Period_2 . "' THEN -qty ELSE 0 END) AS prd2,
- SUM(CASE WHEN prd='" . $Period_3 . "' THEN -qty ELSE 0 END) AS prd3,
- SUM(CASE WHEN prd='" . $Period_4 . "' THEN -qty ELSE 0 END) AS prd4
- FROM stockmoves
- INNER JOIN locationusers ON locationusers.loccode=stockmoves.loccode AND locationusers.userid='" . $_SESSION['UserID'] . "' AND locationusers.canview=1
- WHERE stockid='" . $InventoryPlan['stockid'] . "'
- AND (type=10 OR type=11)
- AND stockmoves.hidemovt=0";
- } else {
- $SQL = "SELECT SUM(CASE WHEN (prd>='" . $Period_1 . "' OR
- prd<='" . $Period_4 . "') THEN -qty ELSE 0 END) AS 4mthtotal,
- SUM(CASE WHEN prd='" . $Period_1 . "' THEN -qty ELSE 0 END) AS prd1,
- SUM(CASE WHEN prd='" . $Period_2 . "' THEN -qty ELSE 0 END) AS prd2,
- SUM(CASE WHEN prd='" . $Period_3 . "' THEN -qty ELSE 0 END) AS prd3,
- SUM(CASE WHEN prd='" . $Period_4 . "' THEN -qty ELSE 0 END) AS prd4
- FROM stockmoves
- INNER JOIN locationusers ON locationusers.loccode=stockmoves.loccode AND locationusers.userid='" . $_SESSION['UserID'] . "' AND locationusers.canview=1
- WHERE stockid='" . $InventoryPlan['stockid'] . "'
- AND stockmoves.loccode ='" . $_POST['Location'] . "'
- AND (stockmoves.type=10 OR stockmoves.type=11)
- AND stockmoves.hidemovt=0";
+ $SQL = "SELECT SUM(CASE WHEN (prd>='" . $Period_1 . "' OR prd<='" . $Period_4 . "') THEN -qty ELSE 0 END) AS 4mthtotal,
+ SUM(CASE WHEN prd='" . $Period_1 . "' THEN -qty ELSE 0 END) AS prd1,
+ SUM(CASE WHEN prd='" . $Period_2 . "' THEN -qty ELSE 0 END) AS prd2,
+ SUM(CASE WHEN prd='" . $Period_3 . "' THEN -qty ELSE 0 END) AS prd3,
+ SUM(CASE WHEN prd='" . $Period_4 . "' THEN -qty ELSE 0 END) AS prd4
+ FROM stockmoves
+ INNER JOIN locationusers
+ ON locationusers.loccode=stockmoves.loccode
+ AND locationusers.userid='" . $_SESSION['UserID'] . "'
+ AND locationusers.canview=1
+ WHERE stockid='" . $InventoryPlan['stockid'] . "'
+ AND (stockmoves.type=10 OR stockmoves.type=11)
+ AND stockmoves.hidemovt=0";
+ if ($_POST['Location']!='All'){
+ $SQL .= " AND stockmoves.loccode ='" . $_POST['Location'] . "'";
}
$SalesResult=DB_query($SQL,$db,'','',FALSE,FALSE);
@@ -283,28 +251,19 @@
$SalesRow = DB_fetch_array($SalesResult);
- if ($_POST['Location']=='All'){
- $SQL = "SELECT SUM(salesorderdetails.quantity - salesorderdetails.qtyinvoiced) AS qtydemand
- FROM salesorderdetails INNER JOIN salesorders
- ON salesorderdetails.orderno=salesorders.orderno
- INNER JOIN locationusers ON locationusers.loccode=salesorders.fromstkloc AND locationusers.userid='" . $_SESSION['UserID'] . "' AND locationusers.canview=1
- WHERE salesorderdetails.stkcode = '" . $InventoryPlan['stockid'] . "'
- AND salesorderdetails.completed = 0
- AND salesorders.quotation=0";
- } else {
- $SQL = "SELECT SUM(salesorderdetails.quantity - salesorderdetails.qtyinvoiced) AS qtydemand
- FROM salesorderdetails INNER JOIN salesorders
- ON salesorderdetails.orderno=salesorders.orderno
- INNER JOIN locationusers ON locationusers.loccode=salesorders.fromstkloc AND locationusers.userid='" . $_SESSION['UserID'] . "' AND locationusers.canview=1
- WHERE salesorders.fromstkloc ='" . $_POST['Location'] . "'
- AND salesorderdetails.stkcode = '" . $InventoryPlan['stockid'] . "'
- AND salesorderdetails.completed = 0
- AND salesorders.quotation=0";
+ $SQL = "SELECT SUM(salesorderdetails.quantity - salesorderdetails.qtyinvoiced) AS qtydemand
+ FROM salesorderdetails INNER JOIN salesorders
+ ON salesorderdetails.orderno=salesorders.orderno
+ INNER JOIN locationusers ON locationusers.loccode=salesorders.fromstkloc AND locationusers.userid='" . $_SESSION['UserID'] . "' AND locationusers.canview=1
+ WHERE salesorderdetails.stkcode = '" . $InventoryPlan['stockid'] . "'
+ AND salesorderdetails.completed = 0
+ AND salesorders.quotation=0";
+ if ($_POST['Location']!='All'){
+ $SQL .= " AND salesorders.fromstkloc ='" . $_POST['Location'] . "'";
}
$DemandResult = DB_query($SQL, $db, '', '', false, false);
-
if (DB_error_no($db) !=0) {
$Title = _('Inventory Planning') . ' - ' . _('Problem Report') . '....';
include('includes/header.inc');
@@ -319,35 +278,21 @@
// Also need to add in the demand as a component of an assembly items if this items has any assembly parents.
- if ($_POST['Location']=='All'){
- $SQL = "SELECT SUM((salesorderdetails.quantity-salesorderdetails.qtyinvoiced)*bom.quantity) AS dem
- FROM salesorderdetails INNER JOIN bom
- ON salesorderdetails.stkcode=bom.parent
- INNER JOIN stockmaster
- ON stockmaster.stockid=bom.parent
- INNER JOIN salesorders
- ON salesorders.orderno = salesorderdetails.orderno
- INNER JOIN locationusers ON locationusers.loccode=salesorders.fromstkloc AND locationusers.userid='" . $_SESSION['UserID'] . "' AND locationusers.canview=1
- WHERE salesorderdetails.quantity-salesorderdetails.qtyinvoiced > 0
- AND bom.component='" . $InventoryPlan['stockid'] . "'
- AND stockmaster.mbflag='A'
- AND salesorderdetails.completed=0
- AND salesorders.quotation=0";
- } else {
- $SQL = "SELECT SUM((salesorderdetails.quantity-salesorderdetails.qtyinvoiced)*bom.quantity) AS dem
- FROM salesorderdetails INNER JOIN bom
- ON salesorderdetails.stkcode=bom.parent
- INNER JOIN stockmaster
- ON stockmaster.stockid=bom.parent
- INNER JOIN salesorders
- ON salesorders.orderno = salesorderdetails.orderno
- INNER JOIN locationusers ON locationusers.loccode=salesorders.fromstkloc AND locationusers.userid='" . $_SESSION['UserID'] . "' AND locationusers.canview=1
- WHERE salesorderdetails.quantity-salesorderdetails.qtyinvoiced > 0
- AND bom.component='" . $InventoryPlan['stockid'] . "'
- AND salesorders.fromstkloc ='" . $_POST['Location'] . "'
- AND stockmaster.mbflag='A'
- AND salesorderdetails.completed=0
- AND salesorders.quotation=0";
+ $SQL = "SELECT SUM((salesorderdetails.quantity-salesorderdetails.qtyinvoiced)*bom.quantity) AS dem
+ FROM salesorderdetails INNER JOIN bom
+ ON salesorderdetails.stkcode=bom.parent
+ INNER JOIN stockmaster
+ ON stockmaster.stockid=bom.parent
+ INNER JOIN salesorders
+ ON salesorders.orderno = salesorderdetails.orderno
+ INNER JOIN locationusers ON locationusers.loccode=salesorders.fromstkloc AND locationusers.userid='" . $_SESSION['UserID'] . "' AND locationusers.canview=1
+ WHERE salesorderdetails.quantity-salesorderdetails.qtyinvoiced > 0
+ AND bom.component='" . $InventoryPlan['stockid'] . "'
+ AND stockmaster.mbflag='A'
+ AND salesorderdetails.completed=0
+ AND salesorders.quotation=0";
+ if ($_POST['Location']!='All'){
+ $SQL .= " AND salesorders.fromstkloc ='" . $_POST['Location'] . "'";
}
$BOMDemandResult = DB_query($SQL,$db,'','',false,false);
@@ -364,58 +309,20 @@
exit;
}
+ // Get the QOO due to Purchase orders for all locations. Function defined in SQL_CommonFunctions.inc
+ // Get the QOO dues to Work Orders for all locations. Function defined in SQL_CommonFunctions.inc
if ($_POST['Location']=='All'){
- $SQL = "SELECT SUM(purchorderdetails.quantityord- purchorderdetails.quantityrecd) as qtyonorder
- FROM purchorderdetails
- LEFT JOIN purchorders
- ON purchorderdetails.orderno = purchorders.orderno
- INNER JOIN locationusers ON locationusers.loccode=purchorders.intostocklocation AND locationusers.userid='" . $_SESSION['UserID'] . "' AND locationusers.canview=1
- LEFT JOIN purchdata
- ON purchorders.supplierno=purchdata.supplierno
- AND purchorderdetails.itemcode=purchdata.stockid
- WHERE purchorderdetails.itemcode = '" . $InventoryPlan['stockid'] . "'
- AND purchorderdetails.completed = 0
- AND purchorders.status <> 'Cancelled'
- AND purchorders.status <> 'Rejected'
- AND purchorders.status <> 'Pending'
- AND purchorders.status <> 'Completed'";
+ $QOO = GetQuantityOnOrderDueToPurchaseOrders($InventoryPlan['stockid'], "", $db);
+ $QOO += GetQuantityOnOrderDueToWorkOrders($InventoryPlan['stockid'], "", $db);
} else {
- $SQL = "SELECT SUM(purchorderdetails.quantityord - purchorderdetails.quantityrecd) as qtyonorder
- FROM purchorderdetails
- LEFT JOIN purchorders
- ON purchorderdetails.orderno = purchorders.orderno
- INNER JOIN locationusers ON locationusers.loccode=purchorders.intostocklocation AND locationusers.userid='" . $_SESSION['UserID'] . "' AND locationusers.canview=1
- LEFT JOIN purchdata
- ON purchorders.supplierno=purchdata.supplierno
- AND purchorderdetails.itemcode=purchdata.stockid
- WHERE purchorderdetails.itemcode = '" . $InventoryPlan['stockid'] . "'
- AND purchorderdetails.completed = 0
- AND purchorders.intostocklocation= '" . $_POST['Location'] . "'
- AND purchorders.status <> 'Cancelled'
- AND purchorders.status <> 'Rejected'
- AND purchorders.status <> 'Pending'
- AND purchorders.status <> 'Completed'";
+ $QOO = GetQuantityOnOrderDueToPurchaseOrders($InventoryPlan['stockid'], $_POST['Location'], $db);
+ $QOO += GetQuantityOnOrderDueToWorkOrders($InventoryPlan['stockid'], $_POST['Location'], $db);
}
$DemandRow = DB_fetch_array($DemandResult);
$BOMDemandRow = DB_fetch_array($BOMDemandResult);
$TotalDemand = $DemandRow['qtydemand'] + $BOMDemandRow['dem'];
- $OnOrdResult = DB_query($SQL, $db, '', '', false, false);
- if (DB_error_no($db) !=0) {
- $Title = _('Inventory Planning') . ' - ' . _('Problem Report') . '....';
- include('includes/header.inc');
- prnMsg( _('The purchase order quantities could not be retrieved by the SQL because') . ' - ' . DB_error_msg($db),'error');
- echo '<br /><a href="' .$RootPath .'/index.php">' . _('Back to the menu') . '</a>';
- if ($debug==1){
- echo '<br />' . $SQL;
- }
- include('includes/footer.inc');
- exit;
- }
-
- $OnOrdRow = DB_fetch_array($OnOrdResult);
-
$LeftOvers = $pdf->addTextWrap($Left_Margin, $YPos, 60, $FontSize, $InventoryPlan['stockid'], 'left');
$LeftOvers = $pdf->addTextWrap(100, $YPos, 150,6,$InventoryPlan['description'],'left');
$AverageOfLast4Months = $SalesRow['4mthtotal']/4;
@@ -436,9 +343,9 @@
$LeftOvers = $pdf->addTextWrap(597, $YPos, 40,$FontSize,locale_number_format($InventoryPlan['qoh'],0),'right');
$LeftOvers = $pdf->addTextWrap(638, $YPos, 40,$FontSize,locale_number_format($TotalDemand,0),'right');
- $LeftOvers = $pdf->addTextWrap(679, $YPos, 40,$FontSize,locale_number_format($OnOrdRow['qtyonorder'],0),'right');
+ $LeftOvers = $pdf->addTextWrap(679, $YPos, 40,$FontSize,locale_number_format($QOO,0),'right');
- $SuggestedTopUpOrder = $RequiredStockInSupplyChain - $InventoryPlan['qoh'] + $TotalDemand - $OnOrdRow['qtyonorder'];
+ $SuggestedTopUpOrder = $RequiredStockInSupplyChain - $InventoryPlan['qoh'] + $TotalDemand - $QOO;
if ($SuggestedTopUpOrder <=0){
$LeftOvers = $pdf->addTextWrap(730, $YPos, 40,$FontSize,_('Nil'),'center');
Modified: trunk/SelectOrderItems.php
===================================================================
--- trunk/SelectOrderItems.php 2014-10-04 03:40:57 UTC (rev 6906)
+++ trunk/SelectOrderItems.php 2014-10-05 13:14:49 UTC (rev 6907)
@@ -1494,39 +1494,11 @@
} else {
$DemandQty = 0;
}
- // Find the quantity on purchase orders
- $sql = "SELECT SUM(purchorderdetails.quantityord-purchorderdetails.quantityrecd) AS qoo
- FROM purchorderdetails INNER JOIN purchorders
- ON purchorderdetails.orderno=purchorders.orderno
- WHERE purchorderdetails.completed=0
- AND purchorders.status<> 'Completed'
- AND purchorders.status<> 'Rejected'
- AND purchorders.status<> 'Cancelled'
- AND purchorderdetails.itemcode='" . $myrow['stockid'] . "'";
+ // Get the QOO due to Purchase orders for all locations. Function defined in SQL_CommonFunctions.inc
+ $PurchQty = GetQuantityOnOrderDueToPurchaseOrders($myrow['stockid'], "", $db);
+ // Get the QOO dues to Work Orders for all locations. Function defined in SQL_CommonFunctions.inc
+ $WoQty = GetQuantityOnOrderDueToWorkOrders($myrow['stockid'], "", $db);
- $ErrMsg = _('The order details for this product cannot be retrieved because');
- $PurchResult = DB_query($sql,$db,$ErrMsg);
-
- $PurchRow = DB_fetch_row($PurchResult);
- if ($PurchRow[0]!=null){
- $PurchQty = $PurchRow[0];
- } else {
- $PurchQty = 0;
- }
-
- // Find the quantity on works orders
- $sql = "SELECT SUM(woitems.qtyreqd - woitems.qtyrecd) AS qwo
- FROM woitems
- WHERE stockid='" . $myrow['stockid'] ."'";
- $ErrMsg = _('The order details for this product cannot be retrieved because');
- $WoResult = DB_query($sql,$db,$ErrMsg);
- $WoRow = DB_fetch_row($WoResult);
- if ($WoRow[0]!=null){
- $WoQty = $WoRow[0];
- } else {
- $WoQty = 0;
- }
-
if ($k==1){
echo '<tr class="EvenTableRows">';
$k=0;
@@ -1686,41 +1658,11 @@
$DemandQty = 0;
}
- // Find the quantity on purchase orders
- $sql = "SELECT SUM(purchorderdetails.quantityord-purchorderdetails.quantityrecd) AS qoo
- FROM purchorderdetails INNER JOIN purchorders
- ON purchorderdetails.orderno=purchorders.orderno
- WHERE purchorderdetails.completed=0
- AND purchorders.status<>'Cancelled'
- AND purchorders.status<>'Rejected'
- AND purchorders.status<>'Pending'
- AND purchorders.status<>'Completed'
- AND purchorderdetails.itemcode='" . $myrow['stockid'] . "'";
+ // Get the QOO due to Purchase orders for all locations. Function defined in SQL_CommonFunctions.inc
+ $PurchQty = GetQuantityOnOrderDueToPurchaseOrders($myrow['stockid'], "", $db);
+ // Get the QOO dues to Work Orders for all locations. Function defined in SQL_CommonFunctions.inc
+ $WoQty = GetQuantityOnOrderDueToWorkOrders($myrow['stockid'], "", $db);
- $ErrMsg = _('The order details for this product cannot be retrieved because');
- $PurchResult = DB_query($sql,$db,$ErrMsg);
-
- $PurchRow = DB_fetch_row($PurchResult);
- if ($PurchRow[0]!=null){
- $PurchQty = $PurchRow[0];
- } else {
- $PurchQty = 0;
- }
-
- // Find the quantity on works orders
- $sql = "SELECT SUM(woitems.qtyreqd - woitems.qtyrecd) AS dedm
- FROM woitems
- WHERE stockid='" . $myrow['stockid'] ."'";
- $ErrMsg = _('The order details for this product cannot be retrieved because');
- $WoResult = DB_query($sql,$db,$ErrMsg);
-
- $WoRow = DB_fetch_row($WoResult);
- if ($WoRow[0]!=null){
- $WoQty = $WoRow[0];
- } else {
- $WoQty = 0;
- }
-
if ($k==1){
echo '<tr class="EvenTableRows">';
$k=0;
Modified: trunk/SelectProduct.php
===================================================================
--- trunk/SelectProduct.php 2014-10-04 03:40:57 UTC (rev 6906)
+++ trunk/SelectProduct.php 2014-10-05 13:14:49 UTC (rev 6907)
@@ -11,6 +11,7 @@
$BookMark = 'SelectingInventory';
include ('includes/header.inc');
+include ('includes/SQL_CommonFunctions.inc');
if (isset($_GET['StockID'])) {
//The page is called with a StockID
@@ -262,35 +263,12 @@
WHERE stockid = '" . $StockID . "'", $db);
$QOHRow = DB_fetch_row($QOHResult);
$QOH = locale_number_format($QOHRow[0], $myrow['decimalplaces']);
- $QOOSQL="SELECT SUM(purchorderdetails.quantityord -purchorderdetails.quantityrecd) AS QtyOnOrder
- FROM purchorders INNER JOIN purchorderdetails
- ON purchorders.orderno=purchorderdetails.orderno
- INNER JOIN locationusers ON locationusers.loccode=purchorders.intostocklocation AND locationusers.userid='" . $_SESSION['UserID'] . "' AND locationusers.canview=1
- WHERE purchorderdetails.itemcode='" . $StockID . "'
- AND purchorderdetails.completed =0
- AND purchorders.status<>'Cancelled'
- AND purchorders.status<>'Pending'
- AND purchorders.status<>'Rejected'";
- $QOOResult = DB_query($QOOSQL, $db);
- if (DB_num_rows($QOOResult) == 0) {
- $QOO = 0;
- } else {
- $QOORow = DB_fetch_row($QOOResult);
- $QOO = $QOORow[0];
- }
- //Also the on work order quantities
- $sql = "SELECT SUM(woitems.qtyreqd-woitems.qtyrecd) AS qtywo
- FROM woitems INNER JOIN workorders
- ON woitems.wo=workorders.wo
- INNER JOIN locationusers ON locationusers.loccode=workorders.loccode AND locationusers.userid='" . $_SESSION['UserID'] . "' AND locationusers.canview=1
- WHERE workorders.closed=0
- AND woitems.stockid='" . $StockID . "'";
- $ErrMsg = _('The quantity on work orders for this product cannot be retrieved because');
- $QOOResult = DB_query($sql, $db, $ErrMsg);
- if (DB_num_rows($QOOResult) == 1) {
- $QOORow = DB_fetch_row($QOOResult);
- $QOO+= $QOORow[0];
- }
+
+ // Get the QOO due to Purchase orders for all locations. Function defined in SQL_CommonFunctions.inc
+ $QOO = GetQuantityOnOrderDueToPurchaseOrders($StockID, "", $db);
+ // Get the QOO dues to Work Orders for all locations. Function defined in SQL_CommonFunctions.inc
+ $QOO += GetQuantityOnOrderDueToWorkOrders($StockID, "", $db);
+
$QOO = locale_number_format($QOO, $myrow['decimalplaces']);
break;
}
Modified: trunk/StockLocStatus.php
===================================================================
--- trunk/StockLocStatus.php 2014-10-04 03:40:57 UTC (rev 6906)
+++ trunk/StockLocStatus.php 2014-10-05 13:14:49 UTC (rev 6907)
@@ -7,6 +7,7 @@
$Title = _('All Stock Status By Location/Category');
include('includes/header.inc');
+include ('includes/SQL_CommonFunctions.inc');
if (isset($_GET['StockID'])){
$StockID = trim(mb_strtoupper($_GET['StockID']));
@@ -14,7 +15,6 @@
$StockID = trim(mb_strtoupper($_POST['StockID']));
}
-
echo '<form action="' . htmlspecialchars($_SERVER['PHP_SELF'],ENT_QUOTES,'UTF-8') . '" method="post">';
echo '<div>';
echo '<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />';
@@ -239,25 +239,11 @@
$DemandQty += $DemandRow[0];
}
+ // Get the QOO due to Purchase orders for all locations. Function defined in SQL_CommonFunctions.inc
+ $QOO = GetQuantityOnOrderDueToPurchaseOrders($StockID, $myrow['loccode'], $db);
+ // Get the QOO dues to Work Orders for all locations. Function defined in SQL_CommonFunctions.inc
+ $QOO += GetQuantityOnOrderDueToWorkOrders($StockID, $myrow['loccode'], $db);
- $sql = "SELECT SUM(purchorderdetails.quantityord - purchorderdetails.quantityrecd) AS qoo
- FROM purchorderdetails
- INNER JOIN purchorders
- ON purchorderdetails.orderno=purchorders.orderno
- WHERE purchorders.intostocklocation='" . $myrow['loccode'] . "'
- AND purchorderdetails.itemcode='" . $StockID . "'
- AND (purchorders.status = 'Authorised' OR purchorders.status='Printed')";
-
- $ErrMsg = _('The quantity on order for this product to be received into') . ' ' . $myrow['loccode'] . ' ' . _('cannot be retrieved because');
- $QOOResult = DB_query($sql,$db,$ErrMsg);
-
- if (DB_num_rows($QOOResult)==1){
- $QOORow = DB_fetch_row($QOOResult);
- $QOO = $QOORow[0];
- } else {
- $QOO = 0;
- }
-
if (($_POST['BelowReorderQuantity']=='Below' AND ($myrow['quantity']-$myrow['reorderlevel']-$DemandQty)<0)
OR $_POST['BelowReorderQuantity']=='All' OR $_POST['BelowReorderQuantity']=='NotZero'
OR ($_POST['BelowReorderQuantity']=='OnOrder' AND $QOO != 0)){
Modified: trunk/StockStatus.php
===================================================================
--- trunk/StockStatus.php 2014-10-04 03:40:57 UTC (rev 6906)
+++ trunk/StockStatus.php 2014-10-05 13:14:49 UTC (rev 6907)
@@ -7,6 +7,7 @@
$Title = _('Stock Status');
include('includes/header.inc');
+include ('includes/SQL_CommonFunctions.inc');
if (isset($_GET['StockID'])){
$StockID = trim(mb_strtoupper($_GET['StockID']));
@@ -175,41 +176,11 @@
}
if ($Its_A_KitSet_Assembly_Or_Dummy == False){
+ // Get the QOO due to Purchase orders for all locations. Function defined in SQL_CommonFunctions.inc
+ $QOO = GetQuantityOnOrderDueToPurchaseOrders($StockID, $myrow['loccode'], $db);
+ // Get the QOO dues to Work Orders for all locations. Function defined in SQL_CommonFunctions.inc
+ $QOO += GetQuantityOnOrderDueToWorkOrders($StockID, $myrow['loccode'], $db);
- $sql="SELECT SUM(purchorderdetails.quantityord-purchorderdetails.quantityrecd)
- FROM purchorders LEFT JOIN purchorderdetails
- ON purchorders.orderno=purchorderdetails.orderno
- WHERE purchorderdetails.itemcode='" . $StockID . "'
- AND purchorders.intostocklocation='" . $myrow['loccode'] . "'
- AND (purchorders.status<>'Cancelled'
- AND purchorders.status<>'Pending'
- AND purchorders.status<>'Rejected'
- AND purchorders.status<>'Completed')";
- $ErrMsg = _('The quantity on order for this product to be received into') . ' ' . $myrow['loccode'] . ' ' . _('cannot be retrieved because');
- $QOOResult = DB_query($sql,$db,$ErrMsg, $DbgMsg);
-
- if (DB_num_rows($QOOResult)==1){
- $QOORow = DB_fetch_row($QOOResult);
- $QOO = $QOORow[0];
- } else {
- $QOO = 0;
- }
-
- //Also the on work order quantities
- $sql = "SELECT SUM(woitems.qtyreqd-woitems.qtyrecd) AS qtywo
- FROM woitems INNER JOIN workorders
- ON woitems.wo=workorders.wo
- WHERE workorders.closed=0
- AND workorders.loccode='" . $myrow['loccode'] . "'
- AND woitems.stockid='" . $StockID . "'";
- $ErrMsg = _('The quantity on work orders for this product to be received into') . ' ' . $myrow['loccode'] . ' ' . _('cannot be retrieved because');
- $QOOResult = DB_query($sql,$db,$ErrMsg, $DbgMsg);
-
- if (DB_num_rows($QOOResult)==1){
- $QOORow = DB_fetch_row($QOOResult);
- $QOO += $QOORow[0];
- }
-
$InTransitSQL="SELECT SUM(shipqty-recqty) as intransit
FROM loctransfers
WHERE stockid='" . $StockID . "'
Modified: trunk/TopItems.php
===================================================================
--- trunk/TopItems.php 2014-10-04 03:40:57 UTC (rev 6906)
+++ trunk/TopItems.php 2014-10-05 13:14:49 UTC (rev 6907)
@@ -7,6 +7,8 @@
include ('includes/session.inc');
$Title = _('Top Items Searching');
include ('includes/header.inc');
+include ('includes/SQL_CommonFunctions.inc');
+
//check if input already
if (!(isset($_POST['Search']))) {
@@ -198,35 +200,11 @@
WHERE stockid = '" . DB_escape_string($myrow['stkcode']) . "'", $db);
$QOHRow = DB_fetch_row($QOHResult);
$QOH = $QOHRow[0];
- $QOOSQL="SELECT SUM(purchorderdetails.quantityord -purchorderdetails.quantityrecd) AS QtyOnOrder
- FROM purchorders INNER JOIN purchorderdetails
- ON purchorders.orderno=purchorderdetails.orderno
- INNER JOIN locationusers ON locationusers.loccode=purchorders.intostocklocation AND locationusers.userid='" . $_SESSION['UserID'] . "' AND locationusers.canview=1
- WHERE purchorderdetails.itemcode='" . DB_escape_string($myrow['stkcode']) . "'
- AND purchorderdetails.completed =0
- AND purchorders.status<>'Cancelled'
- AND purchorders.status<>'Pending'
- AND purchorders.status<>'Rejected'";
- $QOOResult = DB_query($QOOSQL, $db);
- if (DB_num_rows($QOOResult) == 0) {
- $QOO = 0;
- } else {
- $QOORow = DB_fetch_row($QOOResult);
- $QOO = $QOORow[0];
- }
- //Also the on work order quantities
- $sql = "SELECT SUM(woitems.qtyreqd-woitems.qtyrecd) AS qtywo
- FROM woitems INNER JOIN workorders
- ON woitems.wo=workorders.wo
- INNER JOIN locationusers ON locationusers.loccode=workorders.loccode AND locationusers.userid='" . $_SESSION['UserID'] . "' AND locationusers.canview=1
- WHERE workorders.closed=0
- AND woitems.stockid='" . DB_escape_string($myrow['stkcode']) . "'";
- $ErrMsg = _('The quantity on work orders for this product cannot be retrieved because');
- $QOOResult = DB_query($sql, $db, $ErrMsg);
- if (DB_num_rows($QOOResult) == 1) {
- $QOORow = DB_fetch_row($QOOResult);
- $QOO+= $QOORow[0];
- }
+
+ // Get the QOO due to Purchase orders for all locations. Function defined in SQL_CommonFunctions.inc
+ $QOO = GetQuantityOnOrderDueToPurchaseOrders($myrow['stkcode'], "", $db);
+ // Get the QOO dues to Work Orders for all locations. Function defined in SQL_CommonFunctions.inc
+ $QOO += GetQuantityOnOrderDueToWorkOrders($myrow['stkcode'], "", $db);
break;
}
if(is_numeric($QOH) and is_numeric($QOO)){
Modified: trunk/includes/SQL_CommonFunctions.inc
===================================================================
--- trunk/includes/SQL_CommonFunctions.inc 2014-10-04 03:40:57 UTC (rev 6906)
+++ trunk/includes/SQL_CommonFunctions.inc 2014-10-05 13:14:49 UTC (rev 6907)
@@ -369,4 +369,83 @@
}
}
+/* GetQuantityOnOrderDueToPurchaseOrders return the QOO for an item due to Purchase orders (not WO).
+ If Location is empty, it returns the QOO for all locations
+ If location is NOT empty, returns the QOO for that specific location.
+*/
+function GetQuantityOnOrderDueToPurchaseOrders($StockID, $Location, $db){
+ if ($Location == ""){
+ // All locations to be considered
+ $WhereLocation = "";
+ $ErrMsg = _('The quantity on order due to purchase orders for') . ' ' . $StockID . ' ' . _('to be received into all locations cannot be retrieved because');
+ }else{
+ // Just 1 location to consider
+ $WhereLocation = " AND purchorders.intostocklocation = '" . $Location . "'";
+ $ErrMsg = _('The quantity on order due to purchase orders for') . ' ' . $StockID . ' ' . _('to be received into') . ' ' . $Location . ' ' . _('cannot be retrieved because');
+ }
+
+ $SQL="SELECT SUM(purchorderdetails.quantityord -purchorderdetails.quantityrecd) AS QtyOnOrder
+ FROM purchorders
+ INNER JOIN purchorderdetails
+ ON purchorders.orderno=purchorderdetails.orderno
+ INNER JOIN locationusers
+ ON locationusers.loccode=purchorders.intostocklocation
+ AND locationusers.userid='" . $_SESSION['UserID'] . "'
+ AND locationusers.canview=1
+ WHERE purchorderdetails.itemcode='" . $StockID . "'
+ AND purchorderdetails.completed = 0
+ AND purchorders.status<>'Cancelled'
+ AND purchorders.status<>'Pending'
+ AND purchorders.status<>'Rejected'
+ AND purchorders.status<>'Completed'" .
+ $WhereLocation;
+
+ $QOOResult = DB_query($SQL, $db, $ErrMsg);
+ if (DB_num_rows($QOOResult) == 0) {
+ $QOO = 0;
+ } else {
+ $QOORow = DB_fetch_row($QOOResult);
+ $QOO = $QOORow[0];
+ }
+ return $QOO;
+}
+
+
+/* GetQuantityOnOrderDueToWorkOrders return the QOO for an item due to Work orders (not PO).
+ If Location is empty, it returns the QOO for all locations
+ If location is NOT empty, returns the QOO for that specific location.
+*/
+function GetQuantityOnOrderDueToWorkOrders($StockID, $Location, $db){
+ if ($Location == ""){
+ // All locations to be considered
+ $WhereLocation = "";
+ $ErrMsg = _('The quantity on order due to work orders for') . ' ' . $StockID . ' ' . _('to be received into all locations cannot be retrieved because');
+ }else{
+ // Just 1 location to consider
+ $WhereLocation = " AND workorders.loccode='" . $Location . "'";
+ $ErrMsg = _('The quantity on order due to work orders for') . ' ' . $StockID . ' ' . _('to be received into') . ' ' . $Location . ' ' . _('cannot be retrieved because');
+ }
+
+ $SQL="SELECT SUM(woitems.qtyreqd-woitems.qtyrecd) AS qtywo
+ FROM woitems
+ INNER JOIN workorders
+ ON woitems.wo=workorders.wo
+ INNER JOIN locationusers
+ ON locationusers.loccode=workorders.loccode
+ AND locationusers.userid='" . $_SESSION['UserID'] . "'
+ AND locationusers.canview=1
+ WHERE workorders.closed=0
+ AND woitems.stockid='" . $StockID . "'" .
+ $WhereLocation;
+
+ $QOOResult = DB_query($SQL, $db, $ErrMsg);
+ if (DB_num_rows($QOOResult) == 0) {
+ $QOO = 0;
+ } else {
+ $QOORow = DB_fetch_row($QOOResult);
+ $QOO = $QOORow[0];
+ }
+ return $QOO;
+}
+
?>
\ No newline at end of file
|
