Menu
▾
▴
[Web-erp-svn] SF.net SVN: web-erp:[5465] trunk
|
From: <dai...@us...> - 2012-06-29 23:40:14
|
Revision: 5465
http://web-erp.svn.sourceforge.net/web-erp/?rev=5465&view=rev
Author: daintree
Date: 2012-06-29 23:40:08 +0000 (Fri, 29 Jun 2012)
Log Message:
-----------
attempt to make quicker GetPrice function
Modified Paths:
--------------
trunk/StockAdjustments.php
trunk/WorkOrderEntry.php
trunk/api/api_php.php
trunk/api/api_webERPsettings.php
trunk/includes/GetPrice.inc
Modified: trunk/StockAdjustments.php
===================================================================
--- trunk/StockAdjustments.php 2012-06-29 18:54:34 UTC (rev 5464)
+++ trunk/StockAdjustments.php 2012-06-29 23:40:08 UTC (rev 5465)
@@ -455,7 +455,7 @@
}
echo '<input type="hidden" name="Quantity" value="' . $_SESSION['Adjustment' . $identifier]->Quantity . '" />
'.locale_number_format($_SESSION['Adjustment' . $identifier]->Quantity,$DecimalPlaces) .'
- [<a href="'.$rootpath.'/StockAdjustmentsControlled.php?AdjType=REMOVE&dentifier='.$identifier.'">'._('Remove').'</a>]
+ [<a href="'.$rootpath.'/StockAdjustmentsControlled.php?AdjType=REMOVE&identifier='.$identifier.'">'._('Remove').'</a>]
[<a href="'.$rootpath.'/StockAdjustmentsControlled.php?AdjType=ADD&identifier='.$identifier.'">'._('Add').'</a>]';
} else {
echo '<input type="text" class="number" name="Quantity" size="12" maxlength="12" value="' . locale_number_format($Quantity,$DecimalPlaces) . '" />';
Modified: trunk/WorkOrderEntry.php
===================================================================
--- trunk/WorkOrderEntry.php 2012-06-29 18:54:34 UTC (rev 5464)
+++ trunk/WorkOrderEntry.php 2012-06-29 23:40:08 UTC (rev 5465)
@@ -88,7 +88,7 @@
FROM stockmaster
INNER JOIN stockcategory
ON stockmaster.categoryid=stockcategory.categoryid
- WHERE (stockcategory.stocktype='F' OR stockcategory.stocktype='D')
+ WHERE (stockcategory.stocktype='F' OR stockcategory.stocktype='M')
AND stockmaster.description " . LIKE . " '$SearchString'
AND stockmaster.discontinued=0
AND mbflag='M'
@@ -100,7 +100,7 @@
FROM stockmaster
INNER JOIN stockcategory
ON stockmaster.categoryid=stockcategory.categoryid
- WHERE (stockcategory.stocktype='F' OR stockcategory.stocktype='D')
+ WHERE (stockcategory.stocktype='F' OR stockcategory.stocktype='M')
AND stockmaster.discontinued=0
AND stockmaster.description " . LIKE . " '" . $SearchString . "'
AND stockmaster.categoryid='" . $_POST['StockCat'] . "'
@@ -113,6 +113,7 @@
$_POST['StockCode'] = mb_strtoupper($_POST['StockCode']);
$SearchString = '%' . $_POST['StockCode'] . '%';
+ /* Only items of stock type F finished goods or M - raw materials can have work orders created - raw materials can include the manufacture of components (as noted by Bob Thomas! */
if ($_POST['StockCat']=='All'){
$SQL = "SELECT stockmaster.stockid,
stockmaster.description,
@@ -120,7 +121,7 @@
FROM stockmaster
INNER JOIN stockcategory
ON stockmaster.categoryid=stockcategory.categoryid
- WHERE (stockcategory.stocktype='F' OR stockcategory.stocktype='D')
+ WHERE (stockcategory.stocktype='F' OR stockcategory.stocktype='M')
AND stockmaster.stockid " . LIKE . " '" . $SearchString . "'
AND stockmaster.discontinued=0
AND mbflag='M'
@@ -132,7 +133,7 @@
FROM stockmaster
INNER JOIN stockcategory
ON stockmaster.categoryid=stockcategory.categoryid
- WHERE (stockcategory.stocktype='F' OR stockcategory.stocktype='D')
+ WHERE (stockcategory.stocktype='F' OR stockcategory.stocktype='M')
AND stockmaster.stockid " . LIKE . " '" . $SearchString . "'
AND stockmaster.discontinued=0
AND stockmaster.categoryid='" . $_POST['StockCat'] . "'
@@ -147,7 +148,7 @@
FROM stockmaster
INNER JOIN stockcategory
ON stockmaster.categoryid=stockcategory.categoryid
- WHERE (stockcategory.stocktype='F' OR stockcategory.stocktype='D')
+ WHERE (stockcategory.stocktype='F' OR stockcategory.stocktype='M')
AND stockmaster.discontinued=0
AND mbflag='M'
ORDER BY stockmaster.stockid";
@@ -158,7 +159,7 @@
FROM stockmaster
INNER JOIN stockcategory
ON stockmaster.categoryid=stockcategory.categoryid
- WHERE (stockcategory.stocktype='F' OR stockcategory.stocktype='D')
+ WHERE (stockcategory.stocktype='F' OR stockcategory.stocktype='M')
AND stockmaster.discontinued=0
AND stockmaster.categoryid='" . $_POST['StockCat'] . "'
AND mbflag='M'
@@ -339,12 +340,12 @@
nextlotsnref = '". $_POST['NextLotSNRef'.$i] ."',
stdcost ='" . $Cost . "'
WHERE wo='" . $_POST['WO'] . "'
- AND stockid='" . $_POST['OutputItem'.$i] . "'";
+ AND stockid='" . $_POST['OutputItem'.$i] . "'";
} elseif (isset($_POST['HasWOSerialNos'.$i]) AND $_POST['HasWOSerialNos'.$i]==false) {
$sql[] = "UPDATE woitems SET qtyreqd = '". $_POST['OutputQty' . $i] . "',
nextlotsnref = '". $_POST['NextLotSNRef'.$i] ."'
WHERE wo='" . $_POST['WO'] . "'
- AND stockid='" . $_POST['OutputItem'.$i] . "'";
+ AND stockid='" . $_POST['OutputItem'.$i] . "'";
}
}
@@ -375,8 +376,8 @@
// can't delete it there are open work issues
$HasTransResult = DB_query("SELECT transno
FROM stockmoves
- WHERE (stockmoves.type= 26 OR stockmoves.type=28)
- AND reference " . LIKE . " '%" . $_POST['WO'] . "%'",$db);
+ WHERE (stockmoves.type= 26 OR stockmoves.type=28)
+ AND reference " . LIKE . " '%" . $_POST['WO'] . "%'",$db);
if (DB_num_rows($HasTransResult)>0){
prnMsg(_('This work order cannot be deleted because it has issues or receipts related to it'),'error');
$CancelDelete=true;
@@ -429,9 +430,8 @@
startdate,
costissued,
closed
- FROM workorders
- INNER JOIN locations
- ON workorders.loccode=locations.loccode
+ FROM workorders INNER JOIN locations
+ ON workorders.loccode=locations.loccode
WHERE workorders.wo='" . $_POST['WO'] . "'";
$WOResult = DB_query($sql,$db);
@@ -454,9 +454,8 @@
serialised,
stockmaster.decimalplaces,
nextserialno
- FROM woitems
- INNER JOIN stockmaster
- ON woitems.stockid=stockmaster.stockid
+ FROM woitems INNER JOIN stockmaster
+ ON woitems.stockid=stockmaster.stockid
WHERE wo='" .$_POST['WO'] . "'",$db,$ErrMsg);
$NumberOfOutputs=DB_num_rows($WOItemsResult);
@@ -586,7 +585,7 @@
$SQL="SELECT categoryid,
categorydescription
FROM stockcategory
- WHERE stocktype='F' OR stocktype='D'
+ WHERE stocktype='F' OR stocktype='M'
ORDER BY categorydescription";
$result1 = DB_query($SQL,$db);
Modified: trunk/api/api_php.php
===================================================================
--- trunk/api/api_php.php 2012-06-29 18:54:34 UTC (rev 5464)
+++ trunk/api/api_php.php 2012-06-29 23:40:08 UTC (rev 5465)
@@ -15,6 +15,7 @@
include($PathPrefix . 'includes/SQL_CommonFunctions.inc');
/* Required for creating invoices/credits */
include($PathPrefix . 'includes/GetSalesTransGLCodes.inc');
+ include($PathPrefix . 'includes/POSDataCreation.php');
/* Get weberp authentication, and return a valid database
connection */
Modified: trunk/api/api_webERPsettings.php
===================================================================
--- trunk/api/api_webERPsettings.php 2012-06-29 18:54:34 UTC (rev 5464)
+++ trunk/api/api_webERPsettings.php 2012-06-29 23:40:08 UTC (rev 5465)
@@ -90,10 +90,9 @@
$ReturnValue[1]=$answer;
return $ReturnValue;
}
-
+
/* This function creates a POS zipped update file */
- include('../../includes/POSDataCreation.php');
function CreatePOSDataFull($POSDebtorNo, $POSBranchCode, $User, $Password) {
$Errors = array();
Modified: trunk/includes/GetPrice.inc
===================================================================
--- trunk/includes/GetPrice.inc 2012-06-29 18:54:34 UTC (rev 5464)
+++ trunk/includes/GetPrice.inc 2012-06-29 23:40:08 UTC (rev 5465)
@@ -15,7 +15,7 @@
AND prices.branchcode='" . $BranchCode . "'
AND prices.startdate <='" . Date('Y-m-d') . "'
AND prices.enddate >='" . Date('Y-m-d') . "'";
-
+
$ErrMsg = _('There is a problem in retrieving the pricing information for part') . ' ' . $StockID . ' ' . _('and for Customer') . ' ' . $DebtorNo . ' ' . _('the error message returned by the SQL server was');
$result = DB_query($sql, $db,$ErrMsg);
if (DB_num_rows($result)==0){
@@ -33,11 +33,11 @@
AND prices.startdate <='" . Date('Y-m-d') . "'
AND prices.enddate ='0000-00-00'
ORDER BY prices.startdate DESC";
-
+
$result = DB_query($sql, $db,$ErrMsg);
-
+
if (DB_num_rows($result)==0){
-
+
/* No result returned for customer and branch search try for just a customer match */
$sql = "SELECT prices.price
FROM prices,
@@ -50,8 +50,8 @@
AND prices.branchcode=''
AND prices.startdate <='" . Date('Y-m-d') . "'
AND prices.enddate >='" . Date('Y-m-d') . "'";
-
-
+
+
$result = DB_query($sql,$db,$ErrMsg);
if (DB_num_rows($result)==0){
//if no specific price between the dates maybe there is a default price with no end date specified
@@ -68,11 +68,11 @@
AND prices.startdate <='" . Date('Y-m-d') . "'
AND prices.enddate >='0000-00-00'
ORDER BY prices.startdate DESC";
-
+
$result = DB_query($sql,$db,$ErrMsg);
-
+
if (DB_num_rows($result)==0){
-
+
/*No special customer specific pricing use the customers normal price list but look for special limited time prices with specific end date*/
$sql = "SELECT prices.price
FROM prices,
@@ -80,13 +80,13 @@
WHERE debtorsmaster.salestype=prices.typeabbrev
AND debtorsmaster.debtorno='" . $DebtorNo . "'
AND prices.stockid = '" . $StockID . "'
- AND prices.debtorno=''
+ AND prices.debtorno=''
AND prices.currabrev = debtorsmaster.currcode
AND prices.startdate <='" . Date('Y-m-d') . "'
AND prices.enddate >='" . Date('Y-m-d') . "'";
-
+
$result = DB_query($sql,$db,$ErrMsg);
-
+
if (DB_num_rows($result)==0){
/*No special customer specific pricing use the customers normal price list but look for default price with 0000-00-00 end date*/
$sql = "SELECT prices.price,
@@ -96,19 +96,19 @@
WHERE debtorsmaster.salestype=prices.typeabbrev
AND debtorsmaster.debtorno='" . $DebtorNo . "'
AND prices.stockid = '" . $StockID . "'
- AND prices.debtorno=''
+ AND prices.debtorno=''
AND prices.currabrev = debtorsmaster.currcode
AND prices.startdate <='" . Date('Y-m-d') . "'
AND prices.enddate ='0000-00-00'
ORDER BY prices.startdate DESC";
-
+
$result = DB_query($sql,$db,$ErrMsg);
-
- if (DB_num_rows($result)==0){
-
+
+ if (DB_num_rows($result)==0){
+
/* Now use the default salestype/price list cos all else has failed */
$sql="SELECT prices.price
- FROM prices,
+ FROM prices,
debtorsmaster
WHERE prices.stockid = '" . $StockID . "'
AND prices.currabrev = debtorsmaster.currcode
@@ -117,11 +117,11 @@
AND prices.debtorno=''
AND prices.startdate <='" . Date('Y-m-d') . "'
AND prices.enddate >='" . Date('Y-m-d') . "'";;
-
+
$result = DB_query($sql, $db,$ErrMsg);
-
- if (DB_num_rows($result)==0){
-
+
+ if (DB_num_rows($result)==0){
+
/* Now use the default salestype/price list cos all else has failed */
$sql="SELECT prices.price,
prices.startdate
@@ -135,9 +135,9 @@
AND prices.startdate <='" . Date('Y-m-d') . "'
AND prices.enddate ='0000-00-00'
ORDER BY prices.startdate DESC";
-
+
$result = DB_query($sql, $db,$ErrMsg);
-
+
if (DB_num_rows($result)==0){
/*Not even a price set up in the default price list so return 0 */
if ($ReportZeroPrice ==1){
@@ -164,4 +164,61 @@
}
+function GetPriceQuick ($StockID, $DebtorNo, $BranchCode, $db){
+
+ $Price = 0;
+
+ $sql="SELECT prices.price, prices.debtorno, prices.branchcode, prices.enddate, prices.typeabbrev
+ FROM prices INNER JOIN debtorsmaster
+ AND prices.currabrev = debtorsmaster.currcode
+ WHERE debtorsmaster.debtorno='" . $DebtorNo . "'
+ AND (prices.typeabbrev = debtorsmaster.salestype OR prices.typeabbrev='" . $_SESSION['DefaultPriceList'] . "')
+ AND prices.stockid = '" . $StockID . "'
+ AND (prices.debtorno=debtorsmaster.debtorno OR prices.debtorno='')
+ AND (prices.branchcode='" . $BranchCode . "' OR prices.branchcode='')
+ AND prices.startdate <='" . Date('Y-m-d') . "'
+ AND (prices.enddate >='" . Date('Y-m-d') . "' OR prices.enddate='0000-00-00')";
+
+ $ErrMsg = _('There is a problem in retrieving the pricing information for part') . ' ' . $StockID . ' ' . _('and for Customer') . ' ' . $DebtorNo . ' ' . _('the error message returned by the SQL server was');
+ $result = DB_query($sql, $db,$ErrMsg);
+ if (DB_num_rows($result)==0){
+ return $Price;
+ } else {
+ $PricesArray = array();
+ $RankArray = array();
+ $i = 0;
+ while ($myrow=DB_fetch_row($result)){
+ $Prices[$i]['Price'] = $myrow['price'];
+ $Prices[$i]['DebtorNo'] = $myrow['debtorno'];
+ $Prices[$i]['BranchCode'] = $myrow['branchcode'];
+ $Prices[$i]['EndDate'] = $myrow['enddate'];
+ if ($myrow['debtorno']==$DebtorNo AND $myrow['branchcode']==$BranchCode AND $myrow['enddate']!='0000-00-00') {
+ $Rank[$i] = 1;
+ } elseif ($myrow['debtorno']==$DebtorNo AND $myrow['branchcode']==$BranchCode) {
+ $Rank[$i] = 2;
+ } elseif ($myrow['debtorno']==$DebtorNo AND $myrow['branchcode']=='' AND $myrow['enddate']!='0000-00-00'){
+ $Rank[$i] = 3;
+ } elseif ($myrow['debtorno']==$DebtorNo AND $myrow['branchcode']=='' ){
+ $Rank[$i] = 4;
+ } elseif ($myrow['debtorno']=='' AND $myrow['branchcode']=='' AND $myrow['typeabbrev']!=$_SESSION['DefaultPriceList'] AND $myrow['enddate']!='0000-00-00'){
+ $Rank[$i] = 5;
+ } elseif ($myrow['debtorno']=='' AND $myrow['branchcode']=='' AND $myrow['typeabbrev']!=$_SESSION['DefaultPriceList']){
+ $Rank[$i] = 6;
+ } elseif ($myrow['debtorno']=='' AND $myrow['branchcode']=='' AND $myrow['enddate']!='0000-00-00'){
+ $Rank[$i] = 7;
+ } elseif ($myrow['debtorno']=='' AND $myrow['branchcode']==''){
+ $Rank[$i] = 8;
+ }
+ $i++;
+ }
+ $LowestRank = 10;
+ foreach ($RankArray as $ArrayElement=>$Ranking) {
+ if ($Ranking < $LowestRank){
+ $LowestRankElement = $ArrayElement;
+ $LowestRank = $Ranking;
+ }
+ }
+ return $Prices[$ArrayElement]['Price'];
+ }
+}
?>
\ No newline at end of file
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
