Menu
▾
▴
[Weberp-cvs] SF.net SVN: web-erp:[4454] trunk
|
From: <dai...@us...> - 2010-12-29 09:54:03
|
Revision: 4454
http://web-erp.svn.sourceforge.net/web-erp/?rev=4454&view=rev
Author: daintree
Date: 2010-12-29 09:53:54 +0000 (Wed, 29 Dec 2010)
Log Message:
-----------
upgrade script modification to use single script between version in plain ol SQL
Modified Paths:
--------------
trunk/InventoryPlanning.php
trunk/InventoryQuantities.php
trunk/InventoryValuation.php
trunk/ReorderLevel.php
trunk/ReorderLevelLocation.php
trunk/SelectProduct.php
trunk/UpgradeDatabase.php
trunk/Z_ChangeBranchCode.php
trunk/config.distrib.php
trunk/doc/Change.log.html
trunk/includes/ConnectDB.inc
trunk/includes/ConnectDB_mysql.inc
trunk/includes/ConnectDB_mysqli.inc
trunk/includes/GetConfig.php
trunk/includes/session.inc
trunk/sql/mysql/upgrade3.11.1-4.00.sql
Removed Paths:
-------------
trunk/sql/mysql/updates/
Modified: trunk/InventoryPlanning.php
===================================================================
--- trunk/InventoryPlanning.php 2010-12-27 11:33:59 UTC (rev 4453)
+++ trunk/InventoryPlanning.php 2010-12-29 09:53:54 UTC (rev 4454)
@@ -53,26 +53,26 @@
need QOH, QOO, QDem, Sales Mth -1, Sales Mth -2, Sales Mth -3, Sales Mth -4*/
if ($_POST['Location']=='All'){
$SQL = "SELECT stockmaster.categoryid,
- stockmaster.description,
- stockcategory.categorydescription,
- locstock.stockid,
- SUM(locstock.quantity) AS qoh
- FROM locstock,
- stockmaster,
- stockcategory
- WHERE locstock.stockid=stockmaster.stockid
- AND stockmaster.discontinued = 0
- AND stockmaster.categoryid=stockcategory.categoryid
- AND (stockmaster.mbflag='B' OR stockmaster.mbflag='M')
- AND stockmaster.categoryid >= '" . $_POST['FromCriteria'] . "'
- AND stockmaster.categoryid <= '" . $_POST['ToCriteria'] . "'
- GROUP BY stockmaster.categoryid,
- stockmaster.description,
- stockcategory.categorydescription,
- locstock.stockid,
- stockmaster.stockid
- ORDER BY stockmaster.categoryid,
- stockmaster.stockid";
+ stockmaster.description,
+ stockcategory.categorydescription,
+ locstock.stockid,
+ SUM(locstock.quantity) AS qoh
+ FROM locstock,
+ stockmaster,
+ stockcategory
+ WHERE locstock.stockid=stockmaster.stockid
+ AND stockmaster.discontinued = 0
+ AND stockmaster.categoryid=stockcategory.categoryid
+ AND (stockmaster.mbflag='B' OR stockmaster.mbflag='M')
+ AND stockmaster.categoryid >= '" . $_POST['FromCriteria'] . "'
+ AND stockmaster.categoryid <= '" . $_POST['ToCriteria'] . "'
+ GROUP BY stockmaster.categoryid,
+ stockmaster.description,
+ stockcategory.categorydescription,
+ locstock.stockid,
+ stockmaster.stockid
+ ORDER BY stockmaster.categoryid,
+ stockmaster.stockid";
} else {
$SQL = "SELECT stockmaster.categoryid,
locstock.stockid,
@@ -261,34 +261,24 @@
}
if ($_POST['Location']=='All'){
- $SQL = "SELECT SUM(purchorderdetails.quantityord*(CASE WHEN purchdata.conversionfactor IS NULL THEN 1 ELSE purchdata.conversionfactor END)
- - purchorderdetails.quantityrecd*(CASE WHEN purchdata.conversionfactor IS NULL THEN 1 ELSE purchdata.conversionfactor END)) as qtyonorder
- FROM purchorderdetails
- LEFT JOIN purchorders
- ON purchorderdetails.orderno = purchorders.orderno
- 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'";
+ $SQL = "SELECT SUM(purchorderdetails.quantityord - purchorderdetails.quantityrecd) as qtyonorder
+ FROM purchorderdetails INNER JOIN purchorders
+ ON purchorderdetails.orderno = purchorders.orderno
+ WHERE purchorderdetails.itemcode = '" . $InventoryPlan['stockid'] . "'
+ AND purchorderdetails.completed = 0
+ AND purchorders.status <> 'Cancelled'
+ AND purchorders.status <> 'Rejected'
+ AND purchorders.status <> 'Pending'";
} else {
- $SQL = "SELECT SUM(purchorderdetails.quantityord*(CASE WHEN purchdata.conversionfactor IS NULL THEN 1 ELSE purchdata.conversionfactor END)
- - purchorderdetails.quantityrecd*(CASE WHEN purchdata.conversionfactor IS NULL THEN 1 ELSE purchdata.conversionfactor END)) as qtyonorder
- FROM purchorderdetails
- LEFT JOIN purchorders
- ON purchorderdetails.orderno = purchorders.orderno
- 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'";
+ $SQL = "SELECT SUM(purchorderdetails.quantityord - purchorderdetails.quantityrecd) as qtyonorder
+ FROM purchorderdetails INNER JOIN purchorders
+ ON purchorderdetails.orderno = purchorders.orderno
+ 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'";
}
$DemandRow = DB_fetch_array($DemandResult);
@@ -300,9 +290,9 @@
$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?" . SID . "'>" . _('Back to the menu') . '</a>';
+ echo "<br /><a href='" .$rootpath .'/index.php?' . SID . "'>" . _('Back to the menu') . '</a>';
if ($debug==1){
- echo "<br />$SQL";
+ echo '<br />' . $SQL;
}
include('includes/footer.inc');
exit;
Modified: trunk/InventoryQuantities.php
===================================================================
--- trunk/InventoryQuantities.php 2010-12-27 11:33:59 UTC (rev 4453)
+++ trunk/InventoryQuantities.php 2010-12-29 09:53:54 UTC (rev 4454)
@@ -18,14 +18,14 @@
$line_height=12;
$Xpos = $Left_Margin+1;
- $wherecategory = " ";
- $catdescription = " ";
+ $WhereCategory = " ";
+ $CatDescription = " ";
if ($_POST['StockCat'] != 'All') {
- $wherecategory = " AND stockmaster.categoryid='" . $_POST['StockCat'] . "' AND stockcategory.stocktype<>'A'";
+ $WhereCategory = " AND stockmaster.categoryid='" . $_POST['StockCat'] . "'";
$sql= "SELECT categoryid, categorydescription FROM stockcategory WHERE categoryid='" . $_POST['StockCat'] . "' ";
$result = DB_query($sql,$db);
$myrow = DB_fetch_row($result);
- $catdescription = $myrow[1];
+ $CatDescription = $myrow[1];
}
if ($_POST['Selection'] == 'All') {
@@ -47,7 +47,7 @@
AND locstock.loccode=locations.loccode
AND locstock.quantity <> 0
AND (stockmaster.mbflag='B' OR stockmaster.mbflag='M') " .
- $wherecategory . " ORDER BY locstock.stockid,locstock.loccode";
+ $WhereCategory . " ORDER BY locstock.stockid,locstock.loccode";
} else {
// sql to only select parts in more than one location
// The SELECT statement at the beginning of the WHERE clause limits the selection to
@@ -73,7 +73,7 @@
AND locstock.loccode=locations.loccode
AND locstock.quantity <> 0
AND (stockmaster.mbflag='B' OR stockmaster.mbflag='M') " .
- $wherecategory . " ORDER BY locstock.stockid,locstock.loccode";
+ $WhereCategory . " ORDER BY locstock.stockid,locstock.loccode";
}
@@ -100,7 +100,7 @@
}
PrintHeader($pdf,$YPos,$PageNumber,$Page_Height,$Top_Margin,$Left_Margin,
- $Page_Width,$Right_Margin,$catdescription);
+ $Page_Width,$Right_Margin,$CatDescription);
$FontSize=8;
@@ -128,13 +128,13 @@
if ($YPos < $Bottom_Margin + $line_height){
PrintHeader($pdf,$YPos,$PageNumber,$Page_Height,$Top_Margin,$Left_Margin,$Page_Width,
- $Right_Margin,$catdescription);
+ $Right_Margin,$CatDescription);
}
} /*end while loop */
if ($YPos < $Bottom_Margin + $line_height){
PrintHeader($pdf,$YPos,$PageNumber,$Page_Height,$Top_Margin,$Left_Margin,$Page_Width,
- $Right_Margin,$catdescription);
+ $Right_Margin,$CatDescription);
}
/*Print out the grand totals */
@@ -192,7 +192,7 @@
} /*end of else not PrintPDF */
function PrintHeader(&$pdf,&$YPos,&$PageNumber,$Page_Height,$Top_Margin,$Left_Margin,
- $Page_Width,$Right_Margin,$catdescription) {
+ $Page_Width,$Right_Margin,$CatDescription) {
/*PDF page header for Reorder Level report */
if ($PageNumber>1){
@@ -212,7 +212,7 @@
$YPos -= $line_height;
$pdf->addTextWrap($Left_Margin,$YPos,50,$FontSize,_('Category'));
$pdf->addTextWrap(95,$YPos,50,$FontSize,$_POST['StockCat']);
- $pdf->addTextWrap(160,$YPos,150,$FontSize,$catdescription,'left');
+ $pdf->addTextWrap(160,$YPos,150,$FontSize,$CatDescription,'left');
$YPos -=(2*$line_height);
/*set up the headings */
Modified: trunk/InventoryValuation.php
===================================================================
--- trunk/InventoryValuation.php 2010-12-27 11:33:59 UTC (rev 4453)
+++ trunk/InventoryValuation.php 2010-12-29 09:53:54 UTC (rev 4454)
@@ -35,7 +35,6 @@
locstock
WHERE stockmaster.stockid=locstock.stockid
AND stockmaster.categoryid=stockcategory.categoryid
- AND stockcategory.stocktype<>'A'
GROUP BY stockmaster.categoryid,
stockcategory.categorydescription,
unitcost,
@@ -68,7 +67,6 @@
AND stockmaster.categoryid >= '" . $_POST['FromCriteria'] . "'
AND stockmaster.categoryid <= '" . $_POST['ToCriteria'] . "'
AND locstock.loccode = '" . $_POST['Location'] . "'
- AND stockcategory.stocktype<>'A'
ORDER BY stockmaster.categoryid,
stockmaster.stockid";
}
Modified: trunk/ReorderLevel.php
===================================================================
--- trunk/ReorderLevel.php 2010-12-27 11:33:59 UTC (rev 4453)
+++ trunk/ReorderLevel.php 2010-12-29 09:53:54 UTC (rev 4454)
@@ -21,8 +21,8 @@
$wherecategory = " ";
$catdescription = " ";
if ($_POST['StockCat'] != 'All') {
- $wherecategory = " AND stockmaster.categoryid='" . $_POST['StockCat'] . "' AND stockcategory.stocktype<>'A'";
- $sql= "SELECT categoryid, categorydescription FROM stockcategory WHERE categoryid='" . $_POST['StockCat'] . "' ";
+ $wherecategory = " AND stockmaster.categoryid='" . $_POST['StockCat'] . "'";
+ $sql= "SELECT categoryid, categorydescription FROM stockcategory WHERE categoryid='" . $_POST['StockCat'] . "'";
$result = DB_query($sql,$db);
$myrow = DB_fetch_row($result);
$catdescription = $myrow[1];
@@ -33,25 +33,25 @@
}
$sql = "SELECT locstock.stockid,
- stockmaster.description,
- locstock.loccode,
- locations.locationname,
- locstock.quantity,
- locstock.reorderlevel,
- stockmaster.decimalplaces,
- stockmaster.serialised,
- stockmaster.controlled
- FROM locstock,
- stockmaster
- LEFT JOIN stockcategory
- ON stockmaster.categoryid=stockcategory.categoryid,
- locations
- WHERE locstock.stockid=stockmaster.stockid " .
- $wherelocation .
- "AND locstock.loccode=locations.loccode
- AND locstock.reorderlevel > locstock.quantity
- AND (stockmaster.mbflag='B' OR stockmaster.mbflag='M') " .
- $wherecategory . " ORDER BY locstock.loccode,locstock.stockid";
+ stockmaster.description,
+ locstock.loccode,
+ locations.locationname,
+ locstock.quantity,
+ locstock.reorderlevel,
+ stockmaster.decimalplaces,
+ stockmaster.serialised,
+ stockmaster.controlled
+ FROM locstock,
+ stockmaster
+ LEFT JOIN stockcategory
+ ON stockmaster.categoryid=stockcategory.categoryid,
+ locations
+ WHERE locstock.stockid=stockmaster.stockid " .
+ $wherelocation .
+ "AND locstock.loccode=locations.loccode
+ AND locstock.reorderlevel > locstock.quantity
+ AND (stockmaster.mbflag='B' OR stockmaster.mbflag='M') " .
+ $wherecategory . " ORDER BY locstock.loccode,locstock.stockid";
$result = DB_query($sql,$db,'','',false,true);
Modified: trunk/ReorderLevelLocation.php
===================================================================
--- trunk/ReorderLevelLocation.php 2010-12-27 11:33:59 UTC (rev 4453)
+++ trunk/ReorderLevelLocation.php 2010-12-29 09:53:54 UTC (rev 4454)
@@ -3,7 +3,7 @@
/* $Id$*/
// ReorderLevelLocation.php - Report of reorder level by category
-//$PageSecurity = 2;
+//$PageSecurity = 2; now comes from DB
include('includes/session.inc');
@@ -181,14 +181,13 @@
echo '</select></td></tr>';
$SQL="SELECT categoryid, categorydescription
- FROM stockcategory
- WHERE stocktype<>'A'
- ORDER BY categorydescription";
+ FROM stockcategory
+ ORDER BY categorydescription";
$result1 = DB_query($SQL,$db);
echo '<tr><td>' . _('Category') . ':</td>
- <td><select name="StockCat">';
+ <td><select name="StockCat">';
while ($myrow1 = DB_fetch_array($result1)) {
echo '<option value="' . $myrow1['categoryid'] . '">' . $myrow1['categorydescription'];
Modified: trunk/SelectProduct.php
===================================================================
--- trunk/SelectProduct.php 2010-12-27 11:33:59 UTC (rev 4453)
+++ trunk/SelectProduct.php 2010-12-29 09:53:54 UTC (rev 4454)
@@ -29,10 +29,9 @@
}
// Always show the search facilities
$SQL = "SELECT categoryid,
- categorydescription
- FROM stockcategory
- WHERE stocktype<>'A'
- ORDER BY categorydescription";
+ categorydescription
+ FROM stockcategory
+ ORDER BY categorydescription";
$result1 = DB_query($SQL, $db);
if (DB_num_rows($result1) == 0) {
echo '<p><font size=4 color=red>' . _('Problem Report') . ':</font><br />' . _('There are no stock categories currently defined please use the link below to set them up').'</p>';
@@ -50,20 +49,20 @@
$StockID = $_SESSION['SelectedStockItem'];
}
$result = DB_query("SELECT stockmaster.description,
- stockmaster.mbflag,
- stockcategory.stocktype,
- stockmaster.units,
- stockmaster.decimalplaces,
- stockmaster.controlled,
- stockmaster.serialised,
- stockmaster.materialcost+stockmaster.labourcost+stockmaster.overheadcost AS cost,
- stockmaster.discontinued,
- stockmaster.eoq,
- stockmaster.volume,
- stockmaster.kgs
- FROM stockmaster INNER JOIN stockcategory
- ON stockmaster.categoryid=stockcategory.categoryid
- WHERE stockid='" . $StockID . "'", $db);
+ stockmaster.mbflag,
+ stockcategory.stocktype,
+ stockmaster.units,
+ stockmaster.decimalplaces,
+ stockmaster.controlled,
+ stockmaster.serialised,
+ stockmaster.materialcost+stockmaster.labourcost+stockmaster.overheadcost AS cost,
+ stockmaster.discontinued,
+ stockmaster.eoq,
+ stockmaster.volume,
+ stockmaster.kgs
+ FROM stockmaster INNER JOIN stockcategory
+ ON stockmaster.categoryid=stockcategory.categoryid
+ WHERE stockid='" . $StockID . "'", $db);
$myrow = DB_fetch_array($result);
$Its_A_Kitset_Assembly_Or_Dummy = false;
$Its_A_Dummy = false;
@@ -113,20 +112,19 @@
if (in_array($PricesSecurity, $_SESSION['AllowedPageSecurityTokens']) OR !isset($PricesSecurity)) {
echo '<tr><th colspan="2">' . _('Sell Price') . ':</th><td class="select">';
$PriceResult = DB_query("SELECT typeabbrev, price FROM prices
- WHERE currabrev ='" . $_SESSION['CompanyRecord']['currencydefault'] . "'
- AND typeabbrev = '" . $_SESSION['DefaultPriceList'] . "'
- AND debtorno=''
- AND branchcode=''
- AND stockid='" . $StockID . "'", $db);
+ WHERE currabrev ='" . $_SESSION['CompanyRecord']['currencydefault'] . "'
+ AND typeabbrev = '" . $_SESSION['DefaultPriceList'] . "'
+ AND debtorno=''
+ AND branchcode=''
+ AND stockid='" . $StockID . "'", $db);
if ($myrow['mbflag'] == 'K' OR $myrow['mbflag'] == 'A') {
- $CostResult = DB_query("SELECT SUM(bom.quantity*
- (stockmaster.materialcost+stockmaster.labourcost+stockmaster.overheadcost)) AS cost
- FROM bom INNER JOIN
- stockmaster
- ON bom.component=stockmaster.stockid
- WHERE bom.parent='" . $StockID . "'
- AND bom.effectiveto > '" . Date("Y-m-d") . "'
- AND bom.effectiveafter < '" . Date("Y-m-d") . "'", $db);
+ $CostResult = DB_query("SELECT SUM(bom.quantity * (stockmaster.materialcost+stockmaster.labourcost+stockmaster.overheadcost)) AS cost
+ FROM bom INNER JOIN
+ stockmaster
+ ON bom.component=stockmaster.stockid
+ WHERE bom.parent='" . $StockID . "'
+ AND bom.effectiveto > '" . Date('Y-m-d') . "'
+ AND bom.effectiveafter < '" . Date('Y-m-d') . "'", $db);
$CostRow = DB_fetch_row($CostResult);
$Cost = $CostRow[0];
} else {
@@ -160,14 +158,13 @@
}
}
if ($myrow['mbflag'] == 'K' OR $myrow['mbflag'] == 'A') {
- $CostResult = DB_query("SELECT SUM(bom.quantity*
- (stockmaster.materialcost+stockmaster.labourcost+stockmaster.overheadcost)) AS cost
- FROM bom INNER JOIN
- stockmaster
- ON bom.component=stockmaster.stockid
- WHERE bom.parent='" . $StockID . "'
- AND bom.effectiveto > '" . Date("Y-m-d") . "'
- AND bom.effectiveafter < '" . Date("Y-m-d") . "'", $db);
+ $CostResult = DB_query("SELECT SUM(bom.quantity * (stockmaster.materialcost+stockmaster.labourcost+stockmaster.overheadcost)) AS cost
+ FROM bom INNER JOIN
+ stockmaster
+ ON bom.component=stockmaster.stockid
+ WHERE bom.parent='" . $StockID . "'
+ AND bom.effectiveto > '" . Date('Y-m-d') . "'
+ AND bom.effectiveafter < '" . Date('Y-m-d') . "'", $db);
$CostRow = DB_fetch_row($CostResult);
$Cost = $CostRow[0];
} else {
@@ -179,26 +176,26 @@
// Item Category Property mod: display the item properties
echo '<table align="left">';
$CatValResult = DB_query("SELECT categoryid
- FROM stockmaster
- WHERE stockid='" . $StockID . "'", $db);
+ FROM stockmaster
+ WHERE stockid='" . $StockID . "'", $db);
$CatValRow = DB_fetch_row($CatValResult);
$CatValue = $CatValRow[0];
$sql = "SELECT stkcatpropid,
- label,
- controltype,
- defaultvalue
- FROM stockcatproperties
- WHERE categoryid ='" . $CatValue . "'
- AND reqatsalesorder =0
- ORDER BY stkcatpropid";
+ label,
+ controltype,
+ defaultvalue
+ FROM stockcatproperties
+ WHERE categoryid ='" . $CatValue . "'
+ AND reqatsalesorder =0
+ ORDER BY stkcatpropid";
$PropertiesResult = DB_query($sql, $db);
$PropertyCounter = 0;
$PropertyWidth = array();
while ($PropertyRow = DB_fetch_array($PropertiesResult)) {
$PropValResult = DB_query("SELECT value
- FROM stockitemproperties
- WHERE stockid='" . $StockID . "'
- AND stkcatpropid ='" . $PropertyRow['stkcatpropid']."'", $db);
+ FROM stockitemproperties
+ WHERE stockid='" . $StockID . "'
+ AND stkcatpropid ='" . $PropertyRow['stkcatpropid']."'", $db);
$PropValRow = DB_fetch_row($PropValResult);
$PropertyValue = $PropValRow[0];
echo '<tr><th align="right">' . $PropertyRow['label'] . ':</th>';
@@ -247,17 +244,15 @@
WHERE stockid = '" . $StockID . "'", $db);
$QOHRow = DB_fetch_row($QOHResult);
$QOH = number_format($QOHRow[0], $myrow['decimalplaces']);
- $QOOSQL="SELECT SUM(purchorderdetails.quantityord*(CASE WHEN purchdata.conversionfactor IS NULL THEN 1 ELSE purchdata.conversionfactor END) -
- purchorderdetails.quantityrecd*(CASE WHEN purchdata.conversionfactor IS NULL THEN 1 ELSE purchdata.conversionfactor END))
- FROM purchorders
- LEFT JOIN purchorderdetails
+ $QOOSQL="SELECT SUM(purchorderdetails.quantityord -purchorderdetails.quantityrecd) AS QtyOnOrder
+ FROM purchorders INNER JOIN purchorderdetails
ON purchorders.orderno=purchorderdetails.orderno
- LEFT JOIN purchdata
- ON purchorders.supplierno=purchdata.supplierno
- AND purchorderdetails.itemcode=purchdata.stockid
+ AND purchorderdetails.itemcode=purchdata.stockid
WHERE purchorderdetails.itemcode='" . $StockID . "'
- AND (purchorders.status<>'Cancelled'
- AND purchorders.status<>'Pending')";
+ 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;
@@ -267,10 +262,10 @@
}
//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 woitems.stockid='" . $StockID . "'";
+ FROM woitems INNER JOIN workorders
+ ON woitems.wo=workorders.wo
+ 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) {
@@ -282,25 +277,25 @@
}
$Demand = 0;
$DemResult = DB_query("SELECT SUM(salesorderdetails.quantity-salesorderdetails.qtyinvoiced) AS dem
- FROM salesorderdetails INNER JOIN salesorders
- ON salesorders.orderno = salesorderdetails.orderno
- WHERE salesorderdetails.completed=0
- AND salesorders.quotation=0
- AND salesorderdetails.stkcode='" . $StockID . "'", $db);
+ FROM salesorderdetails INNER JOIN salesorders
+ ON salesorders.orderno = salesorderdetails.orderno
+ WHERE salesorderdetails.completed=0
+ AND salesorders.quotation=0
+ AND salesorderdetails.stkcode='" . $StockID . "'", $db);
$DemRow = DB_fetch_row($DemResult);
$Demand = $DemRow[0];
$DemAsComponentResult = DB_query("SELECT SUM((salesorderdetails.quantity-salesorderdetails.qtyinvoiced)*bom.quantity) AS dem
- FROM salesorderdetails,
- salesorders,
- bom,
- stockmaster
- WHERE salesorderdetails.stkcode=bom.parent
- AND salesorders.orderno = salesorderdetails.orderno
- AND salesorderdetails.quantity-salesorderdetails.qtyinvoiced > 0
- AND bom.component='" . $StockID . "'
- AND stockmaster.stockid=bom.parent
- AND stockmaster.mbflag='A'
- AND salesorders.quotation=0", $db);
+ FROM salesorderdetails,
+ salesorders,
+ bom,
+ stockmaster
+ WHERE salesorderdetails.stkcode=bom.parent
+ AND salesorders.orderno = salesorderdetails.orderno
+ AND salesorderdetails.quantity-salesorderdetails.qtyinvoiced > 0
+ AND bom.component='" . $StockID . "'
+ AND stockmaster.stockid=bom.parent
+ AND stockmaster.mbflag='A'
+ AND salesorders.quotation=0", $db);
$DemAsComponentRow = DB_fetch_row($DemAsComponentResult);
$Demand+= $DemAsComponentRow[0];
//Also the demand for the item as a component of works orders
@@ -333,34 +328,30 @@
<th width="10%">' . _('Min Order Qty') . '</th>
<th width="5%">' . _('Prefer') . '</th></tr>';
$SuppResult = DB_query("SELECT suppliers.suppname,
- suppliers.currcode,
- suppliers.supplierid,
- purchdata.price,
- purchdata.effectivefrom,
- purchdata.leadtime,
- purchdata.conversionfactor,
- purchdata.minorderqty,
- purchdata.preferred
- FROM purchdata INNER JOIN suppliers
- ON purchdata.supplierno=suppliers.supplierid
- WHERE purchdata.stockid = '" . $StockID . "'
- ORDER BY purchdata.preferred DESC, purchdata.effectivefrom DESC", $db);
+ suppliers.currcode,
+ suppliers.supplierid,
+ purchdata.price,
+ purchdata.effectivefrom,
+ purchdata.leadtime,
+ purchdata.conversionfactor,
+ purchdata.minorderqty,
+ purchdata.preferred
+ FROM purchdata INNER JOIN suppliers
+ ON purchdata.supplierno=suppliers.supplierid
+ WHERE purchdata.stockid = '" . $StockID . "'
+ ORDER BY purchdata.preferred DESC, purchdata.effectivefrom DESC", $db);
while ($SuppRow = DB_fetch_array($SuppResult)) {
echo '<tr><td class="select">' . $SuppRow['suppname'] . '</td>
<td class="select">' . number_format($SuppRow['price'] / $SuppRow['conversionfactor'], 2) . '</td>
<td class="select">' . $SuppRow['currcode'] . '</td>
<td class="select">' . ConvertSQLDate($SuppRow['effectivefrom']) . '</td>
- <td class="select">' . $SuppRow['leadtime'] . '</td>
+ <td class="select">' . $SuppRow['leadtime'] . '</td>
<td class="select">' . $SuppRow['minorderqty'] . '</td>';
- switch ($SuppRow['preferred']) {
- /* 2008-08-19 ToPu */
- case 1:
- echo '<td class="select">' . _('Yes') . '</td>';
- break;
- case 0:
- echo '<td class="select">' . _('No') . '</td>';
- break;
+ if ($SuppRow['preferred']==1) { //then this is the preferred supplier
+ echo '<td class="select">' . _('Yes') . '</td>';
+ } else {
+ echo '<td class="select">' . _('No') . '</td>';
}
echo '<td class="select"><a href="' . $rootpath . '/PO_Header.php?' . SID . '&NewOrder=Yes' . '&SelectedSupplier=' .
$SuppRow['supplierid'] . '&StockID=' . $StockID . '&Quantity='.$SuppRow['minorderqty'].'">' . _('Order') . ' </a></td>';
@@ -402,31 +393,23 @@
if ($Its_A_Kitset_Assembly_Or_Dummy == False) {
echo '<a href="' . $rootpath . '/StockAdjustments.php?' . SID . '&StockID=' . $StockID . '">' . _('Quantity Adjustments') . '</a><br />';
echo '<a href="' . $rootpath . '/StockTransfers.php?' . SID . '&StockID=' . $StockID . '">' . _('Location Transfers') . '</a><br />';
- /**
- * 2008-08-19 ToPu
- * enter a purchase order for this SelectedStockItem and suppliers
- * supplierid -- one link for each supplierid.
- */
if ($myrow['mbflag'] == 'B') {
- /**/
echo '<br />';
$SuppResult = DB_query("SELECT suppliers.suppname,
- suppliers.supplierid,
- purchdata.preferred,
- purchdata.minorderqty
- FROM purchdata INNER JOIN suppliers
- ON purchdata.supplierno=suppliers.supplierid
- WHERE purchdata.stockid = '" . $StockID . "'", $db);
+ suppliers.supplierid,
+ purchdata.preferred,
+ purchdata.minorderqty
+ FROM purchdata INNER JOIN suppliers
+ ON purchdata.supplierno=suppliers.supplierid
+ WHERE purchdata.stockid = '" . $StockID . "'", $db);
while ($SuppRow = DB_fetch_array($SuppResult)) {
- /**/
- //
if ($myrow['eoq'] == 0) {
$EOQ = $SuppRow['minorderqty'];
} else {
$EOQ = $myrow['eoq'];
}
echo '<a href="' . $rootpath . '/PO_Header.php?' . SID . '&NewOrder=Yes' . '&SelectedSupplier=' . $SuppRow['supplierid'] .
- '&StockID=' . $StockID . '&Quantity=' . $EOQ . '">' . _('Purchase this Item from') . ' ' . $SuppRow['suppname'] . ' (default)</a><br />';
+ '&StockID=' . $StockID . '&Quantity=' . $EOQ . '">' . _('Purchase this Item from') . ' ' . $SuppRow['suppname'] . ' (' . _('default') . ')</a><br />';
/**/
} /* end of while */
} /* end of $myrow['mbflag'] == 'B' */
@@ -527,65 +510,63 @@
$SearchString = '%' . str_replace(' ', '%', $_POST['Keywords']) . '%';
if ($_POST['StockCat'] == 'All') {
$SQL = "SELECT stockmaster.stockid,
- stockmaster.description,
- SUM(locstock.quantity) AS qoh,
- stockmaster.units,
- stockmaster.mbflag,
- stockmaster.decimalplaces
- FROM stockmaster
- LEFT JOIN stockcategory
- ON stockmaster.categoryid=stockcategory.categoryid,
- locstock
- WHERE stockmaster.stockid=locstock.stockid
- AND stockmaster.description " . LIKE . " '$SearchString'
- AND stockcategory.stocktype<>'A'
- GROUP BY stockmaster.stockid,
- stockmaster.description,
- stockmaster.units,
- stockmaster.mbflag,
- stockmaster.decimalplaces
- ORDER BY stockmaster.stockid";
+ stockmaster.description,
+ SUM(locstock.quantity) AS qoh,
+ stockmaster.units,
+ stockmaster.mbflag,
+ stockmaster.decimalplaces
+ FROM stockmaster
+ LEFT JOIN stockcategory
+ ON stockmaster.categoryid=stockcategory.categoryid,
+ locstock
+ WHERE stockmaster.stockid=locstock.stockid
+ AND stockmaster.description " . LIKE . " '$SearchString'
+ GROUP BY stockmaster.stockid,
+ stockmaster.description,
+ stockmaster.units,
+ stockmaster.mbflag,
+ stockmaster.decimalplaces
+ ORDER BY stockmaster.stockid";
} else {
$SQL = "SELECT stockmaster.stockid,
- stockmaster.description,
- SUM(locstock.quantity) AS qoh,
- stockmaster.units,
- stockmaster.mbflag,
- stockmaster.decimalplaces
- FROM stockmaster,
- locstock
- WHERE stockmaster.stockid=locstock.stockid
- AND description " . LIKE . " '$SearchString'
- AND categoryid='" . $_POST['StockCat'] . "'
- GROUP BY stockmaster.stockid,
- stockmaster.description,
- stockmaster.units,
- stockmaster.mbflag,
- stockmaster.decimalplaces
- ORDER BY stockmaster.stockid";
+ stockmaster.description,
+ SUM(locstock.quantity) AS qoh,
+ stockmaster.units,
+ stockmaster.mbflag,
+ stockmaster.decimalplaces
+ FROM stockmaster,
+ locstock
+ WHERE stockmaster.stockid=locstock.stockid
+ AND description " . LIKE . " '$SearchString'
+ AND categoryid='" . $_POST['StockCat'] . "'
+ GROUP BY stockmaster.stockid,
+ stockmaster.description,
+ stockmaster.units,
+ stockmaster.mbflag,
+ stockmaster.decimalplaces
+ ORDER BY stockmaster.stockid";
}
} elseif (isset($_POST['StockCode'])) {
$_POST['StockCode'] = strtoupper($_POST['StockCode']);
if ($_POST['StockCat'] == 'All') {
$SQL = "SELECT stockmaster.stockid,
- stockmaster.description,
- stockmaster.mbflag,
- SUM(locstock.quantity) AS qoh,
- stockmaster.units,
- stockmaster.decimalplaces
- FROM stockmaster
- LEFT JOIN stockcategory
- ON stockmaster.categoryid=stockcategory.categoryid,
- locstock
- WHERE stockmaster.stockid=locstock.stockid
- AND stockmaster.stockid " . LIKE . " '%" . $_POST['StockCode'] . "%'
- AND stockcategory.stocktype<>'A'
- GROUP BY stockmaster.stockid,
- stockmaster.description,
- stockmaster.units,
- stockmaster.mbflag,
- stockmaster.decimalplaces
- ORDER BY stockmaster.stockid";
+ stockmaster.description,
+ stockmaster.mbflag,
+ SUM(locstock.quantity) AS qoh,
+ stockmaster.units,
+ stockmaster.decimalplaces
+ FROM stockmaster
+ INNER JOIN stockcategory
+ ON stockmaster.categoryid=stockcategory.categoryid,
+ locstock
+ WHERE stockmaster.stockid=locstock.stockid
+ AND stockmaster.stockid " . LIKE . " '%" . $_POST['StockCode'] . "%'
+ GROUP BY stockmaster.stockid,
+ stockmaster.description,
+ stockmaster.units,
+ stockmaster.mbflag,
+ stockmaster.decimalplaces
+ ORDER BY stockmaster.stockid";
} else {
$SQL = "SELECT stockmaster.stockid,
stockmaster.description,
@@ -618,7 +599,6 @@
ON stockmaster.categoryid=stockcategory.categoryid,
locstock
WHERE stockmaster.stockid=locstock.stockid
- AND stockcategory.stocktype<>'A'
GROUP BY stockmaster.stockid,
stockmaster.description,
stockmaster.units,
@@ -720,16 +700,16 @@
$k++;
}
if ($myrow['mbflag'] == 'D') {
- $qoh = 'N/A';
+ $qoh = _('N/A');
} else {
- $qoh = number_format($myrow["qoh"], $myrow['decimalplaces']);
+ $qoh = number_format($myrow['qoh'], $myrow['decimalplaces']);
}
echo "<td><input type='submit' name='Select' value='".$myrow['stockid']."' /></td>
<td>".$myrow['description']."</td>
<td class='number'>".$qoh."</td>
<td>".$myrow['units']."</td>
- <td><a target='_blank' href='" . $rootpath . "/StockStatus.php?" . SID . "&StockID=".$myrow['stockid']."'>" . _('View') . "</a></td>
- </tr>";
+ <td><a target='_blank' href='" . $rootpath . '/StockStatus.php?' . SID . '&StockID=' . $myrow['stockid']."'>" . _('View') . '</a></td>
+ </tr>';
$j++;
if ($j == 20 AND ($RowIndex + 1 != $_SESSION['DisplayRecordsMax'])) {
$j = 1;
Modified: trunk/UpgradeDatabase.php
===================================================================
--- trunk/UpgradeDatabase.php 2010-12-27 11:33:59 UTC (rev 4453)
+++ trunk/UpgradeDatabase.php 2010-12-29 09:53:54 UTC (rev 4454)
@@ -1,410 +1,112 @@
<?php
-
+/* $Id: $*/
$PageSecurity = 15;
-
include('includes/session.inc');
-
-$title = _('Database Upgrade');
-
-//ob_start(); /*what is this for? */
-
+$title = _('Upgrade webERP Database');
include('includes/header.inc');
-function executeSQL($sql, $db, $TrapErrors=False) {
-/* Run an sql statement and return an error code */
- $result = DB_query($sql, $db, '', '', false, $TrapErrors);
- return DB_error_no($db);
-}
-function updateDBNo($NewNumber, $db) {
- $sql="UPDATE config SET confvalue='".$NewNumber."' WHERE confname='DBUpdateNumber'";
- executeSQL($sql, $db);
- $_SESSION['DBUpdateNumber']=$NewNumber;
-}
+if (empty($_POST['DoUpgrade'])){
+
+ prnMsg(_('This script will run perform any modifications to the database since v 3.11 required to allow the additional functionality in later scripts'),'info');
-function CharacterSet($table, $db) {
- $sql="SELECT TABLE_COLLATION
- FROM information_schema.tables
- WHERE TABLE_SCHEMA='".$_SESSION['DatabaseName']."'
- AND TABLE_NAME='".$table."'";
- $result=DB_query($sql, $db);
- $myrow=DB_fetch_array($result);
- return $myrow['TABLE_COLLATION'];
+ echo "<p><form method='post' action='" . $_SERVER['PHP_SELF'] . '?' . SID . "'>";
+ echo '<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />';
+ echo '<div class="centre"><input type="submit" name="DoUpgrade" VALUE="' . _('Perform Database Upgrade') . '"></div>';
+ echo '</form>';
}
-function AddColumn($Column, $Table, $Type, $Null, $Default, $After, $db) {
- $sql="desc ".$Table." ".$Column;
- $result = DB_query($sql, $db);
- if (DB_num_rows($result)==0) {
- if ($Type=='text') {
- $response=executeSQL("ALTER TABLE `".$Table."` ADD COLUMN `".$Column."` ".$Type." ".$Null.
- " AFTER `".$After."`", $db, False);
- } else {
- $response=executeSQL("ALTER TABLE `".$Table."` ADD COLUMN `".$Column."` ".$Type." ".$Null." DEFAULT '".$Default.
- "' AFTER `".$After."`", $db, False);
- }
- if ($response==0) {
- OutputResult( _('The column').' '.$Column.' '._('has been inserted') , 'success');
- } else {
- OutputResult( _('The column').' '.$Column.' '._('could not be inserted') , 'error');
- }
- } else {
- OutputResult( _('The column').' '.$Column.' '._('already exists') , 'info');
- }
-}
+if ($_POST['DoUpgrade'] == _('Perform Database Upgrade')){
-function DropColumn($Column, $Table, $db) {
- $sql="desc ".$Table." ".$Column;
- $result = DB_query($sql, $db);
- if (DB_num_rows($result)!=0) {
- $response=executeSQL("ALTER TABLE `".$Table."` DROP `".$Column, $db, False);
- if ($response==0) {
- OutputResult( _('The column').' '.$Column.' '._('has been removed') , 'success');
- } else {
- OutputResult( _('The column').' '.$Column.' '._('could not be removed') , 'error');
- }
- } else {
- OutputResult( _('The column').' '.$Column.' '._('is already removed') , 'info');
- }
-}
+ echo '<br>';
+ prnMsg(_('If there are any failures then please check with your system administrator').
+ '. '._('Please read all notes carefully to ensure they are expected'),'info');
-function ChangeColumnSize($Column, $Table, $Type, $Null, $Default, $Size, $db) {
- $sql="SELECT CHARACTER_MAXIMUM_LENGTH
- FROM information_schema.columns
- WHERE TABLE_SCHEMA='".$_SESSION['DatabaseName']."'
- AND TABLE_NAME='".$Table."'
- AND COLUMN_NAME='".$Column."'";
- $result=DB_query($sql, $db);
- $myrow=DB_fetch_row($result);
- if ($myrow[0]<>$Size) {
- $response=executeSQL("ALTER TABLE ".$Table." CHANGE COLUMN ".$Column." ".$Column." ".$Type." ".$Null." DEFAULT '".$Default."'", $db, False);
- if ($response==0) {
- OutputResult( _('The column').' '.$Column.' '._('has been changed') , 'success');
- } else {
- OutputResult( _('The column').' '.$Column.' '._('could not be changed') , 'error');
+ if($_SESSION['DBUpdateNumber']< 1) { /* DBUpdateNumber set to 1 when upgrade3.11.1-4.00.sql is run */
+ if ($dbType=='mysql' OR $dbType =='mysqli'){
+ $SQLScripts[0] = './sql/mysql/upgrade3.11.1-4.00.sql';
}
- } else {
- OutputResult( _('The column').' '.$Column.' '._('is already changed') , 'info');
}
-}
+ $result = DB_IgnoreForeignKeys($db);
+
+ foreach ($SQLScripts AS $SQLScriptFile) {
+
+ $SQLEntries = file($SQLScriptFile);
+ $ScriptFileEntries = sizeof($SQLEntries);
+ $ErrMsg = _('The script to upgrade the database failed because');
+ $sql ='';
+ $InAFunction = false;
+ echo '<br><table>
+ <tr><th colspan=2>' . _('Applying') . ' ' . $SQLScriptFile . '</th></tr>';
-function ChangeColumnName($OldName, $Table, $Type, $Null, $Default, $NewName, $db, $AutoIncrement='') {
- $sql="SELECT CHARACTER_MAXIMUM_LENGTH
- FROM information_schema.columns
- WHERE TABLE_SCHEMA='".$_SESSION['DatabaseName']."'
- AND TABLE_NAME='".$Table."'
- AND COLUMN_NAME='".$OldName."'";
- $result=DB_query($sql, $db);
- if (DB_num_rows($result)>0) {
- if ($AutoIncrement=='') {
- $response=executeSQL("ALTER TABLE ".$Table." CHANGE COLUMN ".$OldName." ".$NewName." ".$Type." ".$Null." DEFAULT '".$Default."'", $db, False);
- } else {
- $response=executeSQL("ALTER TABLE ".$Table." CHANGE COLUMN ".$OldName." ".$NewName." ".$Type." ".$Null." ".$AutoIncrement, $db, False);
- }
- if ($response==0) {
- OutputResult( _('The column').' '.$OldName.' '._('has been renamed').' '.$NewName , 'success');
- } else {
- OutputResult( _('The column').' '.$OldName.' '._('could not be renamed') , 'error');
- }
- } else {
- OutputResult( _('The column').' '.$OldName.' '._('is already changed') , 'info');
- }
-}
+ for ($i=0; $i<=$ScriptFileEntries; $i++) {
+
+ $SQLEntries[$i] = trim($SQLEntries[$i]);
-function ChangeColumnType($Column, $Table, $Type, $Null, $Default, $db) {
- $sql="SELECT DATA_TYPE
- FROM information_schema.columns
- WHERE TABLE_SCHEMA='".$_SESSION['DatabaseName']."'
- AND TABLE_NAME='".$Table."'
- AND COLUMN_NAME='".$Column."'";
- $result=DB_query($sql, $db);
- $myrow=DB_fetch_row($result);
- if ($myrow[0]<>$Type) {
- $response=executeSQL("ALTER TABLE ".$Table." CHANGE COLUMN ".$Column." ".$Column." ".$Type." ".$Null." DEFAULT '".$Default."'", $db, False);
- if ($response==0) {
- OutputResult( _('The column').' '.$Column.' '._('has been changed') , 'success');
- } else {
- OutputResult( _('The column').' '.$Column.' '._('could not be changed') , 'error');
- }
- } else {
- OutputResult( _('The column').' '.$Column.' '._('is already changed') , 'info');
- }
-}
-
-function ChangeColumnDefault($Column, $Table, $Type, $Null, $Default, $db) {
- $sql="SELECT COLUMN_DEFAULT
- FROM information_schema.columns
- WHERE TABLE_SCHEMA='".$_SESSION['DatabaseName']."'
- AND TABLE_NAME='".$Table."'
- AND COLUMN_NAME='".$Column."'";
- $result=DB_query($sql, $db);
- $myrow=DB_fetch_row($result);
- if ($myrow[0]<>$Default) {
- $response=executeSQL("ALTER TABLE ".$Table." CHANGE COLUMN ".$Column." ".$Column." ".$Type." ".$Null." DEFAULT '".$Default."'", $db, False);
- if ($response==0) {
- OutputResult( _('The column').' '.$Column.' '._('has been changed') , 'success');
- } else {
- OutputResult( _('The column').' '.$Column.' '._('could not be changed') , 'error');
- }
- } else {
- OutputResult( _('The column').' '.$Column.' '._('is already changed') , 'info');
- }
-}
-
-function NewConfigValue($ConfName, $ConfValue, $db) {
- $sql="SELECT confvalue
- FROM config
- WHERE confname='".$ConfName."'";
- $result=DB_query($sql, $db);
- $myrow=DB_fetch_row($result);
- if (DB_num_rows($result)==0) {
- $response=executeSQL("INSERT INTO `config` (`confname`, `confvalue`) VALUES ('".$ConfName."', '".$ConfValue."')", $db, False);
- if ($response==0) {
- OutputResult( _('The config value').' '.$ConfName.' '._('has been inserted') , 'success');
- } else {
- OutputResult( _('The config value').' '.$ConfName.' '._('could not be inserted') , 'error');
- }
- } else {
- OutputResult( _('The config value').' '.$ConfName.' '._('is in') , 'info');
- }
-}
-
-function CreateTable($Table, $sql, $db) {
- $ShowSQL="SHOW TABLES WHERE Tables_in_".$_SESSION['DatabaseName']."='".$Table."'";
- $result=DB_Query($ShowSQL, $db);
-
- if (DB_num_rows($result)==0) {
- $response=executeSQL($sql, $db, False);
- if ($response==0) {
- OutputResult( _('The table').' '.$Table.' '._('has been created') , 'success');
- } else {
- OutputResult( _('The table').' '.$Table.' '._('could not be created') , 'error');
- }
- } else {
- OutputResult( _('The table').' '.$Table.' '._('already exists') , 'info');
- }
-}
-
-function ConstraintExists($Table, $Constraint, $db) {
- $sql="SELECT CONSTRAINT_NAME
- FROM information_schema.TABLE_CONSTRAINTS
- WHERE TABLE_SCHEMA='".$_SESSION['DatabaseName']."'
- AND TABLE_NAME='".$Table."'
- AND CONSTRAINT_NAME='".$Constraint."'";
- $result=DB_query($sql, $db);
- if (DB_num_rows($result)==0) {
- return false;
- } else {
- return true;
- }
-}
-
-function DropConstraint($Table, $Constraint, $db) {
- if (ConstraintExists($Table, $Constraint, $db)) {
- $response=executeSQL("ALTER TABLE `".$Table."` DROP FOREIGN KEY `".$Constraint."`", $db, False);
- if ($response==0) {
- OutputResult( _('The constraint').' '.$Constraint.' '._('has been removed') , 'success');
- } else {
- OutputResult( _('The constraint').' '.$Constraint.' '._('could not be removed') , 'error');
- }
- } else {
- OutputResult( _('The constraint').' '.$Constraint.' '._('does not exist') , 'info');
- }
-}
-
-function AddConstraint($Table, $Constraint, $Field, $ReferenceTable, $ReferenceField, $db) {
- if (!ConstraintExists($Table, $Constraint, $db)) {
- $response=executeSQL("ALTER TABLE ".$Table. " ADD CONSTRAINT ".$Constraint." FOREIGN KEY (".$Field.
- ") REFERENCES ".$ReferenceTable." (".$ReferenceField.")", $db, False);
- if ($response==0) {
- OutputResult( _('The constraint').' '.$Constraint.' '._('has been added') , 'success');
- } else {
- OutputResult( _('The constraint').' '.$Constraint.' '._('could not be added') , 'error');
- }
- } else {
- OutputResult( _('The constraint').' '.$Constraint.' '._('already exists') , 'info');
- }
-}
-
-function UpdateField($Table, $Field, $NewValue, $Criteria, $db) {
- $sql="SELECT ".$Field." FROM ".$Table." WHERE ".$Criteria;
- $result=DB_query($sql, $db);
- $myrow=DB_fetch_row($result);
- if ($myrow[0]!=$NewValue) {
- $response=executeSQL("UPDATE ".$Table." SET ".$Field."='".$NewValue."' WHERE ".$Criteria, $db, False);
- if ($response==0) {
- OutputResult( _('The field').' '.$Field.' '._('has been updated') , 'success');
- } else {
- OutputResult( _('The field').' '.$Field.' '._('could not be updated') , 'error');
- }
- } else {
- OutputResult( _('The field').' '.$Field.' '._('is already correct') , 'info');
- }
-}
-
-function DeleteRecords($Table, $Criteria, $db) {
- $sql="SELECT * FROM ".$Table." WHERE ".$Criteria;
- $result=DB_query($sql, $db);
- if (DB_num_rows($result)>0) {
- $response=executeSQL("DELETE FROM ".$Table." WHERE ".$Criteria, $db, False);
- if ($response==0) {
- OutputResult( _('Rows have been deleted from').' '.$Table , 'success');
- } else {
- OutputResult( _('Rows could not be deleted from').' '.$Table , 'error');
- }
- } else {
- OutputResult( _('There was nothing to delete from').' '.$Table , 'info');
- }
-}
-
-function DropTable($Table, $Field, $db) {
- $sql="SHOW tables WHERE Tables_in_".$_SESSION['DatabaseName']." ='".$Table."'";
- $result=DB_query($sql, $db);
- $CanDrop=False;
- if (DB_num_rows($result)>0) {
- $CanDrop=True;
- $sql="desc ".$Table." ".$Field;
- $result = DB_query($sql, $db);
- if (DB_num_rows($result)>0) {
- $CanDrop=True;
- } else {
- $CanDrop=False;
- }
- }
- if ($CanDrop) {
- $response=executeSQL("DROP TABLE IF EXISTS `".$Table."`", $db);
- if ($response==0) {
- OutputResult( _('The old table').' '.$Table.' '._('has been removed') , 'success');
- } else {
- OutputResult( _('The old table').' '.$Table.' '._('could not be removed') , 'error');
- }
- } else {
- OutputResult( _('The old table').' '.$Table.' '._('has already been removed') , 'info');
- }
-}
-
-function InsertRecord($Table, $CheckFields, $CheckValues, $Fields, $Values, $db) {
- $sql="SELECT * FROM ".$Table." WHERE ";
- for ($i=0;$i<sizeOf($CheckFields);$i++) {
- $sql = $sql.$CheckFields[$i]."='".$CheckValues[$i]."' AND ";
- }
- $sql=substr($sql, 0, strlen($sql)-5);
- $result=DB_query($sql, $db);
- if (DB_num_rows($result)==0) {
- $sql="INSERT INTO ".$Table." (";
- for ($i=0;$i<sizeOf($Fields);$i++) {
- $sql = $sql.$Fields[$i].",";
- }
- $sql=substr($sql, 0, strlen($sql)-1).") VALUES (";
- for ($i=0;$i<sizeOf($Values);$i++) {
- $sql = $sql."'".$Values[$i]."',";
- }
- $sql=substr($sql, 0, strlen($sql)-1).")";
- $response=executeSQL($sql, $db);
- if ($response==0) {
- OutputResult( _('The record has been inserted') , 'success');
- } else {
- OutputResult( _('The record could not be inserted') , 'error');
- }
- } else {
- OutputResult( _('The record is already in the table') , 'info');
- }
-}
-
-function DropPrimaryKey($Table, $OldKey, $db) {
- $Total=0;
- foreach ($OldKey as $Field) {
- $sql="select * from information_schema.key_column_usage where table_name='".$Table."' and constraint_name='primary' and
- table_schema='".$_SESSION['DatabaseName']."' AND COLUMN_NAME='".$Field."'";
- $result = DB_query($sql, $db);
- $Total = $Total + DB_num_rows($result);
- }
- if ($Total==sizeOf($OldKey)) {
- $response=executeSQL("ALTER TABLE ".$Table." DROP PRIMARY KEY", $db);
- if ($response==0) {
- OutputResult( _('The primary key in').' '.$Table.' '._('has been removed') , 'success');
- } else {
- OutputResult( _('The primary key in').' '.$Table.' '._('could not be removed') , 'error');
- }
- } else {
- OutputResult( _('The primary key in').' '.$Table.' '._('has already been removed') , 'info');
- }
-}
-
-function AddPrimaryKey($Table, $Fields, $db) {
- $sql="select * from information_schema.key_column_usage where table_name='".$Table."' and constraint_name='primary' and
- table_schema='".$_SESSION['DatabaseName']."'";
- $result = DB_query($sql, $db);
- if (DB_num_rows($result)!=sizeOf($Fields)) {
- $KeyString=implode(",", $Fields);
- $response=executeSQL("ALTER TABLE ".$Table." ADD PRIMARY KEY ( ".$KeyString." )", $db);
- if ($response==0) {
- OutputResult( _('The primary key in').' '.$Table.' '._('has been added') , 'success');
- } else {
- OutputResult( _('The primary key in').' '.$Table.' '._('could not be added') , 'error');
- }
- } else {
- OutputResult( _('The primary key in').' '.$Table.' '._('has already been added') , 'info');
- }
-}
-
-function RenameTable($OldName, $NewName, $db) {
- $sql="SHOW TABLES WHERE Tables_in_".$_SESSION['DatabaseName']."='".$OldName."'";
- $result=DB_Query($sql, $db);
-
- if (DB_num_rows($result)!=0) {
- $response=executeSQL("RENAME TABLE ".$OldName." to ".$NewName, $db, False);
- if ($response==0) {
- OutputResult( _('The table').' '.$OldName.' '._('has been renamed to').' '.$NewName , 'success');
- } else {
- OutputResult( _('The table').' '.$OldName.' '._('could not be renamed to').' '.$NewName , 'error');
- }
- } else {
- OutputResult( _('The table').' '.$NewName.' '._('already exists') , 'info');
- }
-}
-
-function OutputResult($msg, $status) {
- if ($status=='error') {
- echo '<td style="background-color: #fddbdb;color: red;">';
- } else if ($status=='success') {
- echo '<td style="background-color: #b9ecb4;color: #006400;">';
- } else {
- echo '<td style="background-color: #c7ccf6;color: navy;">';
- }
- echo $msg;
- echo '</td>';
-}
-
-echo '<p class="page_title_text"><img src="'.$rootpath.'/css/'.$theme.'/images/maintenance.png" title="' . _('Search') . '" alt="" />' . ' ' . $title.'</p>';
-
-if (!isset($_POST['continue'])) {
- echo '<form method="post" id="AccountGroups" action="' . $_SERVER['PHP_SELF'] . '?' . SID . '">';
- echo '<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />';
-
- echo '<div class="page_help_text">' . _('You have database updates that are required.').'<br />'.
- _('Please ensure that you have taken a backup of your current database before continuing.'). '</div><br />';
-
- echo '<div class="centre"><input type="submit" name="continue" value="'.('Continue With Updates').'" /></div>';
- echo '</form>';
-} else {
- $StartingUpdate=$_SESSION['DBUpdateNumber']+1;
- $EndingUpdate=$DBVersion;
- //ob_end_flush();
- echo '<table>';
- for($UpdateNumber=$StartingUpdate; $UpdateNumber<=$EndingUpdate; $UpdateNumber++) {
- ob_start();
- echo '<tr><td>'.$UpdateNumber.'</td>';
- $sql="SET FOREIGN_KEY_CHECKS=0";
- $result=DB_Query($sql, $db);
- include('sql/mysql/updates/'.$UpdateNumber.'.php');
- $sql="SET FOREIGN_KEY_CHECKS=1";
- $result=DB_Query($sql, $db);
- echo '</tr>';
- ob_end_flush();
- }
+ if (substr($SQLEntries[$i], 0, 2) != '--'
+ AND substr($SQLEntries[$i], 0, 3) != 'USE'
+ AND strstr($SQLEntries[$i],'/*')==FALSE
+ AND strlen($SQLEntries[$i])>1){
+
+ $sql .= ' ' . $SQLEntries[$i];
+
+ //check if this line kicks off a function definition - pg chokes otherwise
+ if (substr($SQLEntries[$i],0,15) == 'CREATE FUNCTION'){
+ $InAFunction = true;
+ }
+ //check if this line completes a function definition - pg chokes otherwise
+ if (substr($SQLEntries[$i],0,8) == 'LANGUAGE'){
+ $InAFunction = false;
+ }
+ if (strpos($SQLEntries[$i],';')>0 AND ! $InAFunction){
+ $sql = substr($sql,0,strlen($sql)-1);
+ $result = DB_query($sql, $db, $ErrMsg, $DBMsg, false, false);
+ switch (DB_error_no($db)) {
+ case 0:
+ echo '<tr><td>' . $sql . '</td><td bgcolor="green">'._('Success').'</td></tr>';
+ break;
+ case 1050:
+ echo '<tr><td>' . $sql . '</td><td bgcolor="yellow">'._('Note').' - '.
+ _('Table has already been created').'</td></tr>';
+ break;
+ case 1060:
+ echo '<tr><td>' . $sql . '</td><td bgcolor="yellow">'._('Note').' - '.
+ _('Column has already been created').'</td></tr>';
+ break;
+ case 1061:
+ echo '<tr><td>' . $sql . '</td><td bgcolor="yellow">'._('Note').' - '.
+ _('Index already exists').'</td></tr>';
+ break;
+ case 1062:
+ echo '<tr><td>' . $sql . '</td><td bgcolor="yellow">'._('Note').' - '.
+ _('Entry has already been done').'</td></tr>';
+ break;
+ case 1068:
+ echo '<tr><td>' . $sql . '</td><td bgcolor="yellow">'._('Note').' - '.
+ _('Primary key already exists').'</td></tr>';
+ break;
+ case 1091:
+ echo '<tr><td>' . $sql . '</td><td bgcolor="yellow">'._('Note').' - '.
+ _('Index already dropped previously').'</td></tr>';
+ break;
+ default:
+ echo '<tr><td>' . $sql . '</td><td bgcolor="red">'._('Failure').' - '.
+ _('Error number').' - '.DB_error_no($db) .'</td></tr>';
+ break;
+ }
+ unset($sql);
+ }
+ } //end if its a valid sql line not a comment
+ } //end of for loop around the lines of the sql script
echo '</table>';
-}
+ } //end of loop around SQLScripts to apply
+ $result =DB_ReinstateForeignKeys($db);
+ /*Now get the modified DBUpgradeNumber */
+ $result = DB_query('SELECT confvalue FROM config WHERE confname="DBUpdateNumber"',$db);
+ $myrow = DB_fetch_array($result);
+ $_SESSION['DBUpdateNumber'] = $myrow['confvalue'];
+} /*Dont do upgrade */
+
include('includes/footer.inc');
-?>
\ No newline at end of file
+?>
Modified: trunk/Z_ChangeBranchCode.php
===================================================================
--- trunk/Z_ChangeBranchCode.php 2010-12-27 11:33:59 UTC (rev 4453)
+++ trunk/Z_ChangeBranchCode.php 2010-12-29 09:53:54 UTC (rev 4454)
@@ -111,18 +111,18 @@
prnMsg (_('Changing customer transaction records'),'info');
$sql = "UPDATE debtortrans SET
- branchcode='" . $_POST['NewBranchCode'] . "'
- WHERE debtorno='" . $_POST['DebtorNo'] . "'
- AND branchcode='" . $_POST['OldBranchCode'] . "'";
+ branchcode='" . $_POST['NewBranchCode'] . "'
+ WHERE debtorno='" . $_POST['DebtorNo'] . "'
+ AND branchcode='" . $_POST['OldBranchCode'] . "'";
$ErrMsg = _('The SQL to update debtor transaction records failed because');
$result = DB_query($sql,$db,$ErrMsg,$DbgMsg,true);
prnMsg(_('Changing sales analysis records'),'info');
$sql = "UPDATE salesanalysis
- SET custbranch='" . $_POST['NewBranchCode'] . "'
- WHERE cust='" . $_POST['DebtorNo'] . "'
- AND custbranch='" . $_POST['OldBranchCode'] . "'";
+ SET custbranch='" . $_POST['NewBranchCode'] . "'
+ WHERE cust='" . $_POST['DebtorNo'] . "'
+ AND custbranch='" . $_POST['OldBranchCode'] . "'";
$ErrMsg = _('The SQL to update Sales Analysis records failed because');
$result = DB_query($sql,$db,$ErrMsg,$DbgMsg,true);
@@ -130,9 +130,9 @@
prnMsg(_('Changing order delivery differences records'),'info');
$sql = "UPDATE orderdeliverydifferenceslog
- SET branch='" . $_POST['NewBranchCode'] . "'
- WHERE debtorno='" . $_POST['DebtorNo'] . "'
- AND branch='" . $_POST['OldBranchCode'] . "'";
+ SET branch='" . $_POST['NewBranchCode'] . "'
+ WHERE debtorno='" . $_POST['DebtorNo'] . "'
+ AND branch='" . $_POST['OldBranchCode'] . "'";
$ErrMsg = _('The SQL to update order delivery differences records failed because');
$result = DB_query($sql,$db,$ErrMsg,$DbgMsg,true);
@@ -140,58 +140,59 @@
prnMsg (_('Changing pricing records'),'info');
$sql = "UPDATE prices
- SET branchcode='" . $_POST['NewBranchCode'] . "'
- WHERE debtorno='" . $_POST['DebtorNo'] . "'
- AND branchcode='" . $_POST['OldBranchCode'] . "'";
+ SET branchcode='" . $_POST['NewBranchCode'] . "'
+ WHERE debtorno='" . $_POST['DebtorNo'] . "'
+ AND branchcode='" . $_POST['OldBranchCode'] . "'";
$ErrMsg = _('The SQL to update the pricing records failed because');
$result = DB_query($sql,$db,$ErrMsg,$DbgMsg,true);
prnMsg(_('Changing sales orders records'),'info');
$sql = "UPDATE salesorders
- SET branchcode='" . $_POST['NewBranchCode'] . "'
- WHERE debtorno='" . $_POST['DebtorNo'] . "'
- AND branchcode='" . $_POST['OldBranchCode'] . "'";
+ SET branchcode='" . $_POST['NewBranchCode'] . "'
+ WHERE debtorno='" . $_POST['DebtorNo'] . "'
+ AND branchcode='" . $_POST['OldBranchCode'] . "'";
$ErrMsg = _('The SQL to update the sales order header records failed because');
$result = DB_query($sql,$db,$ErrMsg,$DbgMsg,true);
prnMsg(_('Changing stock movement records'),'info');
$sql = "UPDATE stockmoves
- SET branchcode='" . $_POST['NewBranchCode'] . "'
- WHERE debtorno='" . $_POST['DebtorNo'] . "'
- AND branchcode='" . $_POST['OldBranchCode'] . "'";
+ SET branchcode='" . $_POST['NewBranchCode'] . "'
+ WHERE debtorno='" . $_POST['DebtorNo'] . "'
+ AND branchcode='" . $_POST['OldBranchCode'] . "'";
$ErrMsg = _('The SQL to update the stock movement records failed because');
$result = DB_query($sql,$db,$ErrMsg,$DbgMsg,true);
prnMsg(_('Changing user default customer records'),'info');
$sql = "UPDATE www_users
- SET branchcode='" . $_POST['NewBranchCode'] . "'
- WHERE customerid='" . $_POST['DebtorNo'] . "'
- AND branchcode='" . $_POST['OldBranchCode'] . "'";;
+ SET branchcode='" . $_POST['NewBranchCode'] . "'
+ WHERE customerid='" . $_POST['DebtorNo'] . "'
+ AND branchcode='" . $_POST['OldBranchCode'] . "'";;
$ErrMsg = _('The SQL to update the user records failed');
$result = DB_query($sql,$db,$ErrMsg,$DbgMsg,true);
prnMsg(_('Changing the customer branch code in contract header records'),'info');
$sql = "UPDATE contracts
- SET branchcode='" . $_POST['NewBranchCode'] . "'
- WHERE debtorno='" . $_POST['DebtorNo'] . "'
- AND branchcode='" . $_POST['OldBranchCode'] . "'";
+ SET branchcode='" . $_POST['NewBranchCode'] . "'
+ WHERE debtorno='" . $_POST['DebtorNo'] . "'
+ AND branchcode='" . $_POST['OldBranchCode'] . "'";
$ErrMsg = _('The SQL to update contract header records failed because');
$result = DB_query($sql,$db,$ErrMsg,$DbgMsg,true);
$result = DB_Txn_Commit($db);
+ $result = DB_IgnoreForeignKeyChecks($db);
prnMsg(_('Deleting the old customer branch record'),'info');
$sql = "DELETE FROM custbranch
- WHERE debtorno='" . $_POST['DebtorNo'] . "'
- AND branchcode='" . $_POST['OldBranchCode'] . "'";
-
+ WHERE debtorno='" . $_POST['DebtorNo'] . "'
+ AND branchcode='" . $_POST['OldBranchCode'] . "'";
+
$ErrMsg = _('The SQL to delete the old customer branch record failed because');
$result = DB_query($sql,$db,$ErrMsg,$DbgMsg,true,true);
+ $result = DB_ReinstateForeignKeyChecks($db);
-
}
echo "<form action='" . $_SERVER['PHP_SELF'] . "?=" . $SID . "' method=post>";
Modified: trunk/config.distrib.php
===================================================================
--- trunk/config.distrib.php 2010-12-27 11:33:59 UTC (rev 4453)
+++ trunk/config.distrib.php 2010-12-29 09:53:54 UTC (rev 4454)
@@ -55,7 +55,6 @@
//this can be left commented out
//$SessionSavePath = '/tmp';
-
// which encryption function should be used
//$CryptFunction = "md5"; // MD5 Hash
$CryptFunction = "sha1"; // SHA1 Hash
Modified: trunk/doc/Change.log.html
===================================================================
--- trunk/doc/Change.log.html 2010-12-27 11:33:59 UTC (rev 4453)
+++ trunk/doc/Change.log.html 2010-12-29 09:53:54 UTC (rev 4454)
@@ -1,20 +1,21 @@
<p><font SIZE=4 COLOR=BLUE><b>webERP Change Log</b></font></p>
<p>/</p>
+<p>29/12/10 Phil: Reverted to single SQL upgrade file per release - but retaining Tim's upgrade mechanism if the DBUpgradeNumber is out of date. Removed pseudo SQL language required for upgrade script - just applies plain vanilla SQL from the scripts required</p>
+<p>29/12/10 Tim: DB upgrade mechanism with separate sudo SQL for each database change in a separate file + 52 files of updates since 3.11.4</p>
<p>21/12/10 Phil : Reworked PDFPrintLabels.php to conform to standards and corrected SQL for prices</p>
<p>21/12/10 Ricard Andreu: PDFPrintLabels.php was not checking for end date of prices - fixed</p>
-<p>19/12/10 Phil: SelectOrderItems.php ConfirmDispatch_Invoice.php add code to handle asset disposals.
+<p>19/12/10 Phil: SelectOrderItems.php ConfirmDispatch_Invoice.php add code to handle asset disposals.</p>
<p>14/12/10 Phil: modify purchasing scripts for coding conventions/readability</p>
<p>11/12/10 Phil: Have populated the new field stockcheckdate in stockcheckfreeze and modified PDFStockCheckComparison to use this field when posting the GL - stockmoves need to be on the current day otherwise historical balances will all need to be updated. But narrative shows the date of the stock check for which ...
[truncated message content] |
