[Weberp-svn] SF.net SVN: weberp:[4780] trunk
Brought to you by:
sotandeka,
tim_schofield
From: <tim...@us...> - 2011-03-22 20:11:33
|
Revision: 4780 http://weberp.svn.sourceforge.net/weberp/?rev=4780&view=rev Author: tim_schofield Date: 2011-03-22 20:11:26 +0000 (Tue, 22 Mar 2011) Log Message: ----------- Extra work on sales unit of measure Modified Paths: -------------- trunk/SelectOrderItems.php trunk/includes/ConnectDB_mysqli.inc trunk/includes/DefineCartClass.php trunk/includes/SelectOrderItems_IntoCart.inc Modified: trunk/SelectOrderItems.php =================================================================== --- trunk/SelectOrderItems.php 2011-03-13 13:54:13 UTC (rev 4779) +++ trunk/SelectOrderItems.php 2011-03-22 20:11:26 UTC (rev 4780) @@ -763,26 +763,51 @@ if ($_POST['StockCat']=='All'){ $SQL = "SELECT stockmaster.stockid, stockmaster.description, - stockmaster.units - FROM stockmaster, - stockcategory + stockmaster.units as stockunits, + prices.units as customerunits, + prices.conversionfactor, + prices.price, + prices.currabrev + FROM stockmaster + LEFT JOIN prices + ON stockmaster.stockid=prices.stockid, + stockcategory WHERE stockmaster.categoryid=stockcategory.categoryid AND (stockcategory.stocktype='F' OR stockcategory.stocktype='D') AND stockmaster.mbflag <>'G' AND stockmaster.description " . LIKE . " '$SearchString' AND stockmaster.discontinued=0 + AND ((prices.debtorno='".$_SESSION['Items'.$identifier]->DebtorNo."') + OR (prices.debtorno='' AND (SELECT COUNT(stockid) from prices where debtorno='".$_SESSION['Items'.$identifier]->DebtorNo. + "' AND stockid=stockmaster.stockid)=0 AND prices.currabrev='".$_SESSION['Items'.$identifier]->DefaultCurrency."') + OR (prices.debtorno is NULL OR (prices.debtorno='' AND prices.currabrev<>'".$_SESSION['Items'.$identifier]->DefaultCurrency."') + AND (SELECT COUNT(stockid) from prices where debtorno='".$_SESSION['Items'.$identifier]->DebtorNo. + "' AND stockid=stockmaster.stockid)=0)) ORDER BY stockmaster.stockid"; } else { $SQL = "SELECT stockmaster.stockid, stockmaster.description, - stockmaster.units - FROM stockmaster, stockcategory + stockmaster.units as stockunits, + prices.units as customerunits, + prices.conversionfactor, + prices.price, + prices.currabrev + FROM stockmaster + LEFT JOIN prices + ON stockmaster.stockid=prices.stockid, + stockcategory WHERE stockmaster.categoryid=stockcategory.categoryid AND (stockcategory.stocktype='F' OR stockcategory.stocktype='D') AND stockmaster.mbflag <>'G' AND stockmaster.discontinued=0 AND stockmaster.description " . LIKE . " '" . $SearchString . "' AND stockmaster.categoryid='" . $_POST['StockCat'] . "' + AND ((prices.debtorno='".$_SESSION['Items'.$identifier]->DebtorNo."') + OR (prices.debtorno='' AND (SELECT COUNT(stockid) from prices where debtorno='".$_SESSION['Items'.$identifier]->DebtorNo. + "' AND stockid=stockmaster.stockid)=0 AND prices.currabrev='".$_SESSION['Items'.$identifier]->DefaultCurrency."') + OR (prices.debtorno is NULL OR (prices.debtorno='' AND prices.currabrev<>'".$_SESSION['Items'.$identifier]->DefaultCurrency."') + AND (SELECT COUNT(stockid) from prices where debtorno='".$_SESSION['Items'.$identifier]->DebtorNo. + "' AND stockid=stockmaster.stockid)=0)) ORDER BY stockmaster.stockid"; } @@ -794,25 +819,51 @@ if ($_POST['StockCat']=='All'){ $SQL = "SELECT stockmaster.stockid, stockmaster.description, - stockmaster.units - FROM stockmaster, stockcategory + stockmaster.units as stockunits, + prices.units as customerunits, + prices.conversionfactor, + prices.price, + prices.currabrev + FROM stockmaster + LEFT JOIN prices + ON stockmaster.stockid=prices.stockid, + stockcategory WHERE stockmaster.categoryid=stockcategory.categoryid AND (stockcategory.stocktype='F' OR stockcategory.stocktype='D') AND stockmaster.stockid " . LIKE . " '" . $SearchString . "' AND stockmaster.mbflag <>'G' AND stockmaster.discontinued=0 + AND ((prices.debtorno='".$_SESSION['Items'.$identifier]->DebtorNo."') + OR (prices.debtorno='' AND (SELECT COUNT(stockid) from prices where debtorno='".$_SESSION['Items'.$identifier]->DebtorNo. + "' AND stockid=stockmaster.stockid)=0 AND prices.currabrev='".$_SESSION['Items'.$identifier]->DefaultCurrency."') + OR (prices.debtorno is NULL OR (prices.debtorno='' AND prices.currabrev<>'".$_SESSION['Items'.$identifier]->DefaultCurrency."') + AND (SELECT COUNT(stockid) from prices where debtorno='".$_SESSION['Items'.$identifier]->DebtorNo. + "' AND stockid=stockmaster.stockid)=0)) ORDER BY stockmaster.stockid"; } else { $SQL = "SELECT stockmaster.stockid, stockmaster.description, - stockmaster.units - FROM stockmaster, stockcategory + stockmaster.units as stockunits, + prices.units as customerunits, + prices.conversionfactor, + prices.price, + prices.currabrev + FROM stockmaster + LEFT JOIN prices + ON stockmaster.stockid=prices.stockid, + stockcategory WHERE stockmaster.categoryid=stockcategory.categoryid AND (stockcategory.stocktype='F' OR stockcategory.stocktype='D') AND stockmaster.stockid " . LIKE . " '" . $SearchString . "' AND stockmaster.mbflag <>'G' AND stockmaster.discontinued=0 AND stockmaster.categoryid='" . $_POST['StockCat'] . "' + AND ((prices.debtorno='".$_SESSION['Items'.$identifier]->DebtorNo."') + OR (prices.debtorno='' AND (SELECT COUNT(stockid) from prices where debtorno='".$_SESSION['Items'.$identifier]->DebtorNo. + "' AND stockid=stockmaster.stockid)=0 AND prices.currabrev='".$_SESSION['Items'.$identifier]->DefaultCurrency."') + OR (prices.debtorno is NULL OR (prices.debtorno='' AND prices.currabrev<>'".$_SESSION['Items'.$identifier]->DefaultCurrency."') + AND (SELECT COUNT(stockid) from prices where debtorno='".$_SESSION['Items'.$identifier]->DebtorNo. + "' AND stockid=stockmaster.stockid)=0)) ORDER BY stockmaster.stockid"; } @@ -820,23 +871,50 @@ if ($_POST['StockCat']=='All'){ $SQL = "SELECT stockmaster.stockid, stockmaster.description, - stockmaster.units - FROM stockmaster, stockcategory + stockmaster.units as stockunits, + prices.debtorno, + prices.units as customerunits, + prices.conversionfactor, + prices.price, + prices.currabrev + FROM stockmaster + LEFT JOIN prices + ON stockmaster.stockid=prices.stockid, + stockcategory WHERE stockmaster.categoryid=stockcategory.categoryid AND (stockcategory.stocktype='F' OR stockcategory.stocktype='D') AND stockmaster.mbflag <>'G' AND stockmaster.discontinued=0 + AND ((prices.debtorno='".$_SESSION['Items'.$identifier]->DebtorNo."') + OR (prices.debtorno='' AND (SELECT COUNT(stockid) from prices where debtorno='".$_SESSION['Items'.$identifier]->DebtorNo. + "' AND stockid=stockmaster.stockid)=0 AND prices.currabrev='".$_SESSION['Items'.$identifier]->DefaultCurrency."') + OR (prices.debtorno is NULL OR (prices.debtorno='' AND prices.currabrev<>'".$_SESSION['Items'.$identifier]->DefaultCurrency."') + AND (SELECT COUNT(stockid) from prices where debtorno='".$_SESSION['Items'.$identifier]->DebtorNo. + "' AND stockid=stockmaster.stockid)=0)) ORDER BY stockmaster.stockid"; } else { $SQL = "SELECT stockmaster.stockid, stockmaster.description, - stockmaster.units - FROM stockmaster, stockcategory + stockmaster.units as stockunits, + prices.units as customerunits, + prices.conversionfactor, + prices.price, + prices.currabrev + FROM stockmaster + LEFT JOIN prices + ON stockmaster.stockid=prices.stockid, + stockcategory WHERE stockmaster.categoryid=stockcategory.categoryid AND (stockcategory.stocktype='F' OR stockcategory.stocktype='D') AND stockmaster.mbflag <>'G' AND stockmaster.discontinued=0 AND stockmaster.categoryid='" . $_POST['StockCat'] . "' + AND ((prices.debtorno='".$_SESSION['Items'.$identifier]->DebtorNo."') + OR (prices.debtorno='' AND (SELECT COUNT(stockid) from prices where debtorno='".$_SESSION['Items'.$identifier]->DebtorNo. + "' AND stockid=stockmaster.stockid)=0 AND prices.currabrev='".$_SESSION['Items'.$identifier]->DefaultCurrency."') + OR (prices.debtorno is NULL OR (prices.debtorno='' AND prices.currabrev<>'".$_SESSION['Items'.$identifier]->DefaultCurrency."') + AND (SELECT COUNT(stockid) from prices where debtorno='".$_SESSION['Items'.$identifier]->DebtorNo. + "' AND stockid=stockmaster.stockid)=0)) ORDER BY stockmaster.stockid"; } } @@ -1438,7 +1516,8 @@ <th>' . _('On Demand') . '</th> <th>' . _('On Order') . '</th> <th>' . _('Available') . '</th> - <th>' . _('Quantity') . '</th></tr>'; + <th>' . _('Quantity') . '</th> + <th>' . _('Price') . '</th></tr>'; echo $TableHeader; $j = 1; $k=0; //row colour counter @@ -1447,15 +1526,20 @@ // This code needs sorting out, but until then : $ImageSource = _('No Image'); // Find the quantity in stock at location - $QOHSQL = "SELECT sum(locstock.quantity) AS qoh, - stockmaster.decimalplaces - FROM locstock INNER JOIN stockmaster - ON locstock.stockid=stockmaster.stockid + $DecimalPlacesSQL="SELECT decimalplaces + FROM stockmaster + WHERE stockid='" .$myrow['stockid'] . "'"; + $DecimalPlacesResult = DB_query($DecimalPlacesSQL, $db); + $DecimalPlacesRow = DB_fetch_array($DecimalPlacesResult); + $DecimalPlaces = $DecimalPlacesRow['decimalplaces']; + + $QOHSQL = "SELECT sum(locstock.quantity) AS qoh + FROM locstock WHERE locstock.stockid='" .$myrow['stockid'] . "' AND loccode = '" . $_SESSION['Items'.$identifier]->Location . "'"; $QOHResult = DB_query($QOHSQL,$db); $QOHRow = DB_fetch_array($QOHResult); - $QOH = $QOHRow['qoh']; + $QOH = $QOHRow['qoh']*$myrow['conversionfactor']; // Find the quantity on outstanding sales orders $sql = "SELECT SUM(salesorderdetails.quantity-salesorderdetails.qtyinvoiced) AS dem @@ -1532,10 +1616,10 @@ $myrow['stockid'], $myrow['description'], $myrow['units'], - number_format($QOH, $QOHRow['decimalplaces']), - number_format($DemandQty, $QOHRow['decimalplaces']), - number_format($OnOrder, $QOHRow['decimalplaces']), - number_format($Available, $QOHRow['decimalplaces']), + number_format($QOH, $DecimalPlaces), + number_format($DemandQty, $DecimalPlaces), + number_format($OnOrder, $DecimalPlaces), + number_format($Available, $DecimalPlaces), $ImageSource, $rootpath, SID, @@ -1622,24 +1706,32 @@ <th>' . _('On Demand') . '</th> <th>' . _('On Order') . '</th> <th>' . _('Available') . '</th> - <th>' . _('Quantity') . '</th></tr>'; + <th>' . _('Quantity') . '</th> + <th>' . _('Price') . '</th></tr>'; echo $TableHeader; $ImageSource = _('No Image'); $k=0; //row colour counter while ($myrow=DB_fetch_array($SearchResult)) { + if ($myrow['conversionfactor']=='') { + $myrow['conversionfactor']=1; + } + // Find the quantity in stock at location + $DecimalPlacesSQL="SELECT decimalplaces + FROM stockmaster + WHERE stockid='" .$myrow['stockid'] . "'"; + $DecimalPlacesResult = DB_query($DecimalPlacesSQL, $db); + $DecimalPlacesRow = DB_fetch_array($DecimalPlacesResult); + $DecimalPlaces = $DecimalPlacesRow['decimalplaces']; - // Find the quantity in stock at location - $QOHSQL = "SELECT sum(quantity) AS qoh, - stockmaster.decimalplaces - FROM locstock INNER JOIN stockmaster - ON locstock.stockid = stockmaster.stockid + $QOHSQL = "SELECT sum(locstock.quantity) AS qoh + FROM locstock WHERE locstock.stockid='" .$myrow['stockid'] . "' AND loccode = '" . $_SESSION['Items'.$identifier]->Location . "'"; $QOHResult = DB_query($QOHSQL,$db); $QOHRow = DB_fetch_array($QOHResult); - $QOH = $QOHRow['qoh']; + $QOH = $QOHRow['qoh']*$myrow['conversionfactor']; // Find the quantity on outstanding sales orders $sql = "SELECT SUM(salesorderdetails.quantity-salesorderdetails.qtyinvoiced) AS dem @@ -1663,7 +1755,8 @@ // Find the quantity on purchase orders $sql = "SELECT SUM(purchorderdetails.quantityord-purchorderdetails.quantityrecd) AS dem - FROM purchorderdetails INNER JOIN purchorders + FROM purchorderdetails LEFT JOIN purchorders + ON purchorderdetails.orderno=purchorders.orderno WHERE purchorderdetails.completed=0 AND purchorders.status<>'Cancelled' AND purchorders.status<>'Rejected' @@ -1701,29 +1794,26 @@ $k=1; } $OnOrder = $PurchQty + $WoQty; - $Available = $QOH - $DemandQty + $OnOrder; - - printf('<td>%s</font></td> - <td>%s</td> - <td>%s</td> - <td class="number">%s</td> - <td class="number">%s</td> - <td class="number">%s</td> - <td class="number">%s</td> + $Available = $QOH - $DemandQty + $OnOrder*$myrow['conversionfactor']; + if ($myrow['customerunits']=='') { + $myrow['units']=$myrow['stockunits']; + } else { + $myrow['units']=$myrow['customerunits']; + } + if ($myrow['currabrev']<>$_SESSION['Items'.$identifier]->DefaultCurrency) { + $myrow['price']=0; + } + echo '<td>'.$myrow['stockid'].'</font></td> + <td>'.$myrow['description'].'</td> + <td>'.$myrow['units'].'</td> + <td class="number">'.number_format($QOH,$DecimalPlaces).'</td> + <td class="number">'.number_format($DemandQty,$DecimalPlaces).'</td> + <td class="number">'.number_format($OnOrder*$myrow['conversionfactor'],$DecimalPlaces).'</td> + <td class="number">'.number_format($Available,$DecimalPlaces).'</td> <td><font size=1><input class="number" tabindex='.number_format($j+7).' type="textbox" size=6 name="itm'.$myrow['stockid'].'" value=0> + <td class="number">'.number_format($myrow['price'],2).'</td> </td> - </tr>', - $myrow['stockid'], - $myrow['description'], - $myrow['units'], - number_format($QOH,$QOHRow['decimalplaces']), - number_format($DemandQty,$QOHRow['decimalplaces']), - number_format($OnOrder,$QOHRow['decimalplaces']), - number_format($Available,$QOHRow['decimalplaces']), - $ImageSource, - $rootpath, - SID, - $myrow['stockid']); + </tr>'; if ($j==1) { $jsCall = '<script type="text/javascript">if (document.SelectParts) {defaultControl(document.SelectParts.itm'.$myrow['stockid'].');}</script>'; } Modified: trunk/includes/ConnectDB_mysqli.inc =================================================================== --- trunk/includes/ConnectDB_mysqli.inc 2011-03-13 13:54:13 UTC (rev 4779) +++ trunk/includes/ConnectDB_mysqli.inc 2011-03-22 20:11:26 UTC (rev 4780) @@ -14,7 +14,7 @@ $mysqlport = 3306; } $db = mysqli_connect($host , $dbuser, $dbpassword,$_SESSION['DatabaseName'], $mysqlport); - +//$result=DB_query("SET sql_mode = ONLY_FULL_GROUP_BY", $db); //printf("tried making mysql-i Connection.\n"); //$varabc = mysqli_client_encoding($db); //printf("client encoding is %s\n", $varabc); Modified: trunk/includes/DefineCartClass.php =================================================================== --- trunk/includes/DefineCartClass.php 2011-03-13 13:54:13 UTC (rev 4779) +++ trunk/includes/DefineCartClass.php 2011-03-22 20:11:26 UTC (rev 4780) @@ -75,7 +75,8 @@ $Descr, $Price, $Disc=0, - $UOM, + $Units, + $ConversionFactor=1, $Volume, $Weight, $QOHatLoc=0, @@ -114,7 +115,8 @@ $Qty, $Price, $Disc, - $UOM, + $Units, + $ConversionFactor, $Volume, $Weight, $QOHatLoc, @@ -150,17 +152,21 @@ stkcode, quantity, unitprice, + units, + conversionfactor, discountpercent, itemdue, poline) - VALUES(" . $this->LineCounter . ", - " . $_SESSION['ExistingOrder'] . ", + VALUES('" . $this->LineCounter . "', + '" . $_SESSION['ExistingOrder'] . "', '" . trim(strtoupper($StockID)) ."', - " . $Qty . ", - " . $Price . ", - " . $Disc . ",' - " . $ItemDue . "', - " . $POLine . ")"; + '" . $Qty . "', + '" . $Price . "', + '" . $Units . "', + '" . $ConversionFactor . "', + '" . $Disc . "', + '" . $ItemDue . "', + '" . $POLine . "')"; $result = DB_query($sql, $db , _('The order line for') . ' ' . strtoupper($StockID) . ' ' ._('could not be inserted')); @@ -175,6 +181,8 @@ function update_cart_item( $UpdateLineNumber, $Qty, $Price, + $Units, + $ConversionFactor=1, $Disc, $Narrative, $UpdateDB='No', @@ -186,6 +194,8 @@ $this->LineItems[$UpdateLineNumber]->Quantity = $Qty; } $this->LineItems[$UpdateLineNumber]->Price = $Price; + $this->LineItems[$UpdateLineNumber]->Units = $Units; + $this->LineItems[$UpdateLineNumber]->ConversionFactor = $ConversionFactor; $this->LineItems[$UpdateLineNumber]->DiscountPercent = $Disc; $this->LineItems[$UpdateLineNumber]->Narrative = $Narrative; $this->LineItems[$UpdateLineNumber]->ItemDue = $ItemDue; @@ -193,14 +203,16 @@ $this->LineItems[$UpdateLineNumber]->GPPercent = $GPPercent; if ($UpdateDB=='Yes'){ global $db; - $result = DB_query("UPDATE salesorderdetails SET quantity=" . $Qty . ", - unitprice=" . $Price . ", - discountpercent=" . $Disc . ", + $result = DB_query("UPDATE salesorderdetails SET quantity='" . $Qty . "', + unitprice='" . $Price . "', + units='" . $Units . "', + conversionfactor='" . $ConversionFactor . "', + discountpercent='" . $Disc . "', narrative ='" . DB_escape_string($Narrative) . "', itemdue = '" . FormatDateForSQL($ItemDue) . "', poline = '" . DB_escape_string($POLine) . "' - WHERE orderno=" . $_SESSION['ExistingOrder'] . " - AND orderlineno=" . $UpdateLineNumber + WHERE orderno='" . $_SESSION['ExistingOrder'] . "' + AND orderlineno='" . $UpdateLineNumber . "'" , $db , _('The order line number') . ' ' . $UpdateLineNumber . ' ' . _('could not be updated')); } @@ -413,6 +425,7 @@ Var $Price; Var $DiscountPercent; Var $Units; + var $ConversionFactor; Var $Volume; Var $Weight; Var $ActDispDate; @@ -442,7 +455,8 @@ $Qty, $Prc, $DiscPercent, - $UOM, + $Units, + $ConversionFactor, $Volume, $Weight, $QOHatLoc, @@ -469,7 +483,8 @@ $this->Quantity = $Qty; $this->Price = $Prc; $this->DiscountPercent = $DiscPercent; - $this->Units = $UOM; + $this->Units = $Units; + $this->ConversionFactor = $ConversionFactor; $this->Volume = $Volume; $this->Weight = $Weight; $this->ActDispDate = $ActDispatchDate; Modified: trunk/includes/SelectOrderItems_IntoCart.inc =================================================================== --- trunk/includes/SelectOrderItems_IntoCart.inc 2011-03-13 13:54:13 UTC (rev 4779) +++ trunk/includes/SelectOrderItems_IntoCart.inc 2011-03-22 20:11:26 UTC (rev 4780) @@ -24,13 +24,13 @@ if (strcasecmp($OrderItem->StockID, $NewItem)==0) { $AlreadyOnThisOrder = 1; prnMsg(_('The item') . ' ' . $NewItem . ' ' . _('is already on this order the system is set up to prevent the same item being on the order more than once. However you can change the quantity ordered of the existing line if necessary')); - } + } } /* end of the foreach loop to look for preexisting items of the same code */ } if ($AlreadyOnThisOrder!=1){ - $sql = "SELECT stockmaster.description, + $sql = "SELECT stockmaster.description, stockmaster.stockid, stockmaster.units, stockmaster.volume, @@ -50,16 +50,16 @@ WHERE locstock.loccode='" . $_SESSION['Items'.$identifier]->Location . "' AND stockmaster.stockid = '". $NewItem . "'"; - $ErrMsg = _('The details for') . ' ' . $NewItem . ' ' . _('could not be retrieved because'); - $DbgMsg = _('The SQL used to retrieve the pricing details but failed was'); - $result1 = DB_query($sql,$db,$ErrMsg,$DbgMsg); + $ErrMsg = _('The details for') . ' ' . $NewItem . ' ' . _('could not be retrieved because'); + $DbgMsg = _('The SQL used to retrieve the pricing details but failed was'); + $result1 = DB_query($sql,$db,$ErrMsg,$DbgMsg); - if (DB_num_rows($result1)==0){ + if (DB_num_rows($result1)==0){ prnMsg(_('The item code') . ' ' . $NewItem . ' ' . _('could not be found in the database') . ' - ' . _('it has not been added to the order'),'warn',_('Item Does Not Exist')); - } elseif ($myItemRow = DB_fetch_array($result1)){ + } elseif ($myItemRow = DB_fetch_array($result1)){ - if ($myItemRow['discontinued']==1){ + if ($myItemRow['discontinued']==1){ prnMsg(_('The item') . ' ' . $NewItem . ' ' . _('could not be added to the order because it has been flagged as obsolete'),'error',_('Obsolete Item')); } elseif (($_SESSION['AllowSalesOfZeroCostItems'] == false @@ -113,8 +113,8 @@ if (!isset($_SESSION['WarnOnce']) and $_SESSION['Items'.$identifier]->SpecialInstructions) { prnMsg($_SESSION['Items'.$identifier]->SpecialInstructions,'warn'); - $WarnOnce=1; - } + $WarnOnce=1; + } if ($_SESSION['CheckCreditLimits'] > 0){ /*Check credit limits is 1 for warn and 2 for prohibit sales */ $_SESSION['Items'.$identifier]->CreditAvailable -= round(($NewItemQty * $Price * (1- $Discount)),2); @@ -158,9 +158,9 @@ $ExRate); } - } else { + } else { prnMsg(_('The item code') . ' ' . $NewItem . ' ' . _('does not have a cost set up and order entry is set up to prohibit sales of items with no cost data entered'),'warn'); - } + } } } /* end of if not already on the order */ ?> This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |