Menu
▾
▴
[Weberp-cvs] SF.net SVN: web-erp:[3459] trunk
|
From: <dai...@us...> - 2010-05-15 10:37:03
|
Revision: 3459
http://web-erp.svn.sourceforge.net/web-erp/?rev=3459&view=rev
Author: daintree
Date: 2010-05-15 10:36:56 +0000 (Sat, 15 May 2010)
Log Message:
-----------
GetPrices.inc Prices.php and Prices_Customer.php - modified to allow default prices - with no end dates - reducing requirement to administer - also updated Prices section of the manual
Modified Paths:
--------------
trunk/Prices.php
trunk/Prices_Customer.php
trunk/doc/Change.log.html
trunk/doc/Manual/ManualPrices.html
trunk/includes/ConnectDB_mysqli.inc
trunk/includes/GetPrice.inc
trunk/includes/UserLogin.php
trunk/includes/session.inc
trunk/sql/mysql/upgrade3.11.1-3.12.sql
Modified: trunk/Prices.php
===================================================================
--- trunk/Prices.php 2010-05-12 05:23:41 UTC (rev 3458)
+++ trunk/Prices.php 2010-05-15 10:36:56 UTC (rev 3459)
@@ -33,7 +33,10 @@
echo "<a href='" . $rootpath . '/SelectProduct.php?' . SID . "'>" . _('Back to Items') . '</a><br>';
-$result = DB_query("SELECT stockmaster.description, stockmaster.mbflag FROM stockmaster WHERE stockmaster.stockid='$Item'",$db);
+$result = DB_query("SELECT stockmaster.description,
+ stockmaster.mbflag
+ FROM stockmaster
+ WHERE stockmaster.stockid='$Item'",$db);
$myrow = DB_fetch_row($result);
if (DB_num_rows($result)==0){
@@ -66,7 +69,8 @@
ie the page has called itself with some user input */
//first off validate inputs sensible
-
+ $ZeroDate = Date($_SESSION['DefaultDateFormat'],Mktime(0,0,0,0,0,0));
+
if (!is_double((double) trim($_POST['Price'])) OR $_POST['Price']=="") {
$InputError = 1;
prnMsg( _('The price entered must be numeric'),'error');
@@ -75,36 +79,44 @@
$InputError =1;
prnMsg (_('The date this price is to take effect from must be entered in the format') . ' ' . $_SESSION['DefaultDateFormat'],'error');
}
- if (! Is_Date($_POST['EndDate'])){
+ if (! Is_Date($_POST['EndDate']) AND $_POST['EndDate']!=''){
$InputError =1;
prnMsg (_('The date this price is be in effect to must be entered in the format') . ' ' . $_SESSION['DefaultDateFormat'],'error');
}
- if (Date1GreaterThanDate2($_POST['StartDate'],$_POST['EndDate'])){
+ if (Date1GreaterThanDate2($_POST['StartDate'],$_POST['EndDate']) AND $_POST['EndDate']!='' AND $_POST['EndDate']!=$ZeroDate){
$InputError =1;
+
+ echo 'Debug - start date ' . $_POST['StartDate'] . ' end date: ' . $_POST['EndDate'];
+
prnMsg (_('The end date is expected to be after the start date, enter an end date after the start date for this price'),'error');
}
- if (Date1GreaterThanDate2(Date($_SESSION['DefaultDateFormat']),$_POST['EndDate'])){
+ if (Date1GreaterThanDate2(Date($_SESSION['DefaultDateFormat']),$_POST['EndDate']) AND $_POST['EndDate']!='' AND $_POST['EndDate']!=$ZeroDate){
$InputError =1;
prnMsg(_('The end date is expected to be after today. There is no point entering a new price where the effective date is before today!'),'error');
}
-
+ if (Is_Date($_POST['EndDate'])){
+ $SQLEndDate = FormatDateForSQL($_POST['EndDate']);
+ } else {
+ $SQLEndDate = '0000-00-00';
+ }
if (isset($_POST['OldTypeAbbrev']) AND isset($_POST['OldCurrAbrev']) AND strlen($Item)>1 AND $InputError !=1) {
/* Need to see if there is also a price entered that has an end date after the start date of this price and if so we will need to update it so there is no ambiguity as to which price will be used*/
+
//editing an existing price
$sql = "UPDATE prices SET
- typeabbrev='" . $_POST['TypeAbbrev'] . "',
- currabrev='" . $_POST['CurrAbrev'] . "',
- price=" . $_POST['Price'] . ",
- startdate='" . FormatDateForSQL($_POST['StartDate']) . "',
- enddate='" . FormatDateForSQL($_POST['EndDate']) . "'
- WHERE prices.stockid='$Item'
- AND startdate='" .$_POST['OldStartDate'] . "'
- AND enddate ='" . $_POST['OldEndDate'] . "'
- AND prices.typeabbrev='" . $_POST['OldTypeAbbrev'] . "'
- AND prices.currabrev='" . $_POST['OldCurrAbrev'] . "'
- AND prices.debtorno=''";
+ typeabbrev='" . $_POST['TypeAbbrev'] . "',
+ currabrev='" . $_POST['CurrAbrev'] . "',
+ price=" . $_POST['Price'] . ",
+ startdate='" . FormatDateForSQL($_POST['StartDate']) . "',
+ enddate='" . $SQLEndDate . "'
+ WHERE prices.stockid='$Item'
+ AND startdate='" .$_POST['OldStartDate'] . "'
+ AND enddate ='" . $_POST['OldEndDate'] . "'
+ AND prices.typeabbrev='" . $_POST['OldTypeAbbrev'] . "'
+ AND prices.currabrev='" . $_POST['OldCurrAbrev'] . "'
+ AND prices.debtorno=''";
$ErrMsg = _('Could not be update the existing prices');
$result = DB_query($sql,$db,$ErrMsg);
@@ -118,22 +130,22 @@
/*Selected price is null cos no item selected on first time round so must be adding a record must be submitting new entries in the new price form */
$sql = "INSERT INTO prices (stockid,
- typeabbrev,
- currabrev,
- startdate,
- enddate,
- price)
- VALUES ('$Item',
- '" . $_POST['TypeAbbrev'] . "',
- '" . $_POST['CurrAbrev'] . "',
- '" . FormatDateForSQL($_POST['StartDate']) . "',
- '" . FormatDateForSQL($_POST['EndDate']). "',
- " . $_POST['Price'] . ")";
- $ErrMsg = _('The new price could not be added');
- $result = DB_query($sql,$db,$ErrMsg);
-
- ReSequenceEffectiveDates ($Item, $_POST['TypeAbbrev'], $_POST['CurrAbrev'], $db) ;
- prnMsg(_('The new price has been inserted'),'success');
+ typeabbrev,
+ currabrev,
+ startdate,
+ enddate,
+ price)
+ VALUES ('$Item',
+ '" . $_POST['TypeAbbrev'] . "',
+ '" . $_POST['CurrAbrev'] . "',
+ '" . FormatDateForSQL($_POST['StartDate']) . "',
+ '" . $SQLEndDate. "',
+ " . $_POST['Price'] . ")";
+ $ErrMsg = _('The new price could not be added');
+ $result = DB_query($sql,$db,$ErrMsg);
+
+ ReSequenceEffectiveDates ($Item, $_POST['TypeAbbrev'], $_POST['CurrAbrev'], $db) ;
+ prnMsg(_('The new price has been inserted'),'success');
}
unset($_POST['Price']);
unset($_POST['StartDate']);
@@ -195,7 +207,11 @@
echo '<tr class="OddTableRows">';
$k=1;
}
-
+ if ($myrow['enddate']=='0000-00-00'){
+ $EndDateDisplay = _('No End Date');
+ } else {
+ $EndDateDisplay = ConvertSQLDate($myrow['enddate']);
+ }
/*Only allow access to modify prices if securiy token 5 is allowed */
if (in_array(5,$_SESSION['AllowedPageSecurityTokens'])) {
@@ -210,7 +226,7 @@
$myrow['sales_type'],
$myrow['price'],
ConvertSQLDate($myrow['startdate']),
- ConvertSQLDate($myrow['enddate']),
+ $EndDateDisplay,
$_SERVER['PHP_SELF'],
SID,
$myrow['stockid'],
@@ -237,7 +253,7 @@
$myrow['sales_type'],
$myrow['price'],
ConvertSQLDate($myrow['startdate']),
- ConvertSQLDate($myrow['enddate']));
+ $EndDateDisplay;
}
}
@@ -257,7 +273,11 @@
$_POST['TypeAbbrev'] = $_GET['TypeAbbrev'];
$_POST['Price'] = $_GET['Price'];
$_POST['StartDate'] = ConvertSQLDate($_GET['StartDate']);
- $_POST['EndDate'] = ConvertSQLDate($_GET['EndDate']);
+ if ($_GET['EndDate']==''){
+ $_POST['EndDate'] ='';
+ } else {
+ $_POST['EndDate'] = ConvertSQLDate($_GET['EndDate']);
+ }
}
$SQL = "SELECT currabrev, currency FROM currencies";
@@ -297,7 +317,7 @@
}
if (!isset($_POST['EndDate'])){
- $_POST['EndDate'] = Date($_SESSION['DefaultDateFormat'],Mktime(0,0,0,12,31,(Date('y')+20)));
+ $_POST['EndDate'] = '';
}
echo '<tr><td>' . _('Price Effective From Date') . ':</td>
<td><input type=text name="StartDate" value="' . $_POST['StartDate'] . '"></td></tr>';
@@ -330,25 +350,30 @@
function ReSequenceEffectiveDates ($Item, $PriceList, $CurrAbbrev, $db) {
+ /*This is quite complicated - the idea is that prices set up should be unique and there is no way two prices could be returned as valid - when getting a price in includes/GetPrice.inc the logic is to first look for a price of the salestype/currency within the effective start and end dates - then if not get the price with a start date prior but a blank end date (the default price). We would not want two prices where one price falls inside another effective date range except in the case of a blank end date - ie no end date - the default price for the currency/salestype.
+ I first thought that we would need to update the previous default price (blank end date), when a new default price is entered, to have an end date of the startdate of this new default price less 1 day - but this is converting a default price into a special price which could result in having two special prices over the same date range - best to leave it unchanged and use logic in the GetPrice.inc to ensure the correct default price is returned
+ */
+ //this is just the case where debtorno='' - see the Prices_Customer.php script for customer special prices
$SQL = "SELECT price,
- startdate,
- enddate
+ startdate,
+ enddate
FROM prices
WHERE debtorno=''
AND stockid='" . $Item . "'
AND currabrev='" . $CurrAbbrev . "'
AND typeabbrev='" . $PriceList . "'
+ AND enddate <>'0000-00-00'
ORDER BY startdate, enddate";
$result = DB_query($SQL,$db);
$NextStartDate = Date($_SESSION['DefaultDateFormat']);
unset($EndDate);
unset($NextStartDate);
- while ($myrow = DB_fetch_array($result)){
-
+ while ($myrow = DB_fetch_array($result)){
if (isset($NextStartDate)){
if (Date1GreaterThanDate2(ConvertSQLDate($myrow['startdate']),$NextStartDate)){
$NextStartDate = ConvertSQLDate($myrow['startdate']);
- if (isset($EndDate)) {
+ //Only if the previous enddate is after the new start date do we need to look at updates
+ if (Date1GreaterThanDate2(ConvertSQLDate($EndDate),ConvertSQLDate($myrow['startdate']))) {
/*Need to make the end date the new start date less 1 day */
$SQL = "UPDATE prices SET enddate = '" . FormatDateForSQL(DateAdd($NextStartDate,'d',-1)) . "'
WHERE stockid ='" .$Item . "'
@@ -367,7 +392,7 @@
$StartDate = $myrow['startdate'];
$EndDate = $myrow['enddate'];
$Price = $myrow['price'];
- }
-}
+ } // end of loop around all prices
+} // end function ReSequenceEffectiveDates
?>
\ No newline at end of file
Modified: trunk/Prices_Customer.php
===================================================================
--- trunk/Prices_Customer.php 2010-05-12 05:23:41 UTC (rev 3458)
+++ trunk/Prices_Customer.php 2010-05-15 10:36:56 UTC (rev 3459)
@@ -25,23 +25,32 @@
}
$result = DB_query("SELECT debtorsmaster.name,
- debtorsmaster.currcode,
- debtorsmaster.salestype
- FROM
- debtorsmaster
- WHERE
- debtorsmaster.debtorno='" . $_SESSION['CustomerID'] . "'",$db);
+ debtorsmaster.currcode,
+ debtorsmaster.salestype
+ FROM
+ debtorsmaster
+ WHERE
+ debtorsmaster.debtorno='" . $_SESSION['CustomerID'] . "'",$db);
$myrow = DB_fetch_row($result);
echo '<font color=BLUE><b>' . $myrow[0] . ' ' . _('in') . ' ' . $myrow[1] . '<br>' . ' ' . _('for') . ' ';
$CurrCode = $myrow[1];
$SalesType = $myrow[2];
-$result = DB_query("SELECT stockmaster.description
- FROM stockmaster
- WHERE stockmaster.stockid='" . $Item . "'",$db);
+$result = DB_query("SELECT stockmaster.description,
+ stockmaster.mbflag
+ FROM stockmaster
+ WHERE stockmaster.stockid='" . $Item . "'",$db);
$myrow = DB_fetch_row($result);
+if (DB_num_rows($result)==0){
+ prnMsg( _('The part code entered does not exist in the database') . '. ' . _('Only valid parts can have prices entered against them'),'error');
+ $InputError=1;
+}
+if ($myrow[1]=='K'){
+ prnMsg(_('The part selected is a kit set item') .', ' . _('these items explode into their components when selected on an order') . ', ' . _('prices must be set up for the components and no price can be set for the whole kit'),'error');
+ exit;
+}
echo $Item . ' - ' . $myrow[0] . '</b></font><hr>';
@@ -77,15 +86,15 @@
$InputError =1;
$msg = _('The date this price is to take effect from must be entered in the format') . ' ' . $_SESSION['DefaultDateFormat'];
}
- if (! Is_Date($_POST['EndDate'])){
+ if (! Is_Date($_POST['EndDate']) AND $_POST['EndDate']!=''){ //EndDate can also be blank for default prices
$InputError =1;
$msg = _('The date this price is be in effect to must be entered in the format') . ' ' . $_SESSION['DefaultDateFormat'];
}
- if (Date1GreaterThanDate2($_POST['StartDate'],$_POST['EndDate'])){
+ if (Date1GreaterThanDate2($_POST['StartDate'],$_POST['EndDate']) AND $_POST['EndDate']!=''){
$InputError =1;
$msg = _('The end date is expected to be after the start date, enter an end date after the start date for this price');
}
- if (Date1GreaterThanDate2(Date($_SESSION['DefaultDateFormat']),$_POST['EndDate'])){
+ if (Date1GreaterThanDate2(Date($_SESSION['DefaultDateFormat']),$_POST['EndDate']) AND $_POST['EndDate']!=''){
$InputError =1;
$msg = _('The end date is expected to be after today. There is no point entering a new price where the effective date is before today!');
}
@@ -195,13 +204,18 @@
} else {
echo '<tr><th>' . _('Normal Price') . '</th></tr>';
while ($myrow = DB_fetch_array($result)) {
+ if ($myrow['enddate']=='0000-00-00'){
+ $EndDateDisplay = _('No End Date');
+ } else {
+ $EndDateDisplay = ConvertSQLDate($myrow['enddate']);
+ }
printf('<tr class="EvenTableRows">
<td class=number>%0.2f</td>
<td class=date>%s</td>
<td class=date>%s</td></tr>',
$myrow['price'],
ConvertSQLDate($myrow['startdate']),
- ConvertSQLDate($myrow['enddate']));
+ $EndDateDisplay);
}
}
@@ -244,7 +258,11 @@
} else {
$Branch = $myrow['brname'];
}
-
+ if ($myrow['enddate']=='0000-00-00'){
+ $EndDateDisplay = _('No End Date');
+ } else {
+ $EndDateDisplay = ConvertSQLDate($myrow['enddate']);
+ }
printf("<tr bgcolor='#CCCCCC'>
<td class=number>%0.2f</td>
<td>%s</td>
@@ -255,7 +273,7 @@
$myrow['price'],
$Branch,
ConvertSQLDate($myrow['startdate']),
- ConvertSQLDate($myrow['enddate']),
+ $EndDateDisplay,
$_SERVER['PHP_SELF'],
$Item,
$myrow['price'],
@@ -288,7 +306,11 @@
$_POST['Price']=$_GET['Price'];
$_POST['Branch']=$_GET['Branch'];
$_POST['StartDate'] = ConvertSQLDate($_GET['StartDate']);
- $_POST['EndDate'] = ConvertSQLDate($_GET['EndDate']);
+ if (Is_Date($_GET['EndDate'])){
+ $_POST['EndDate'] = ConvertSQLDate($_GET['EndDate']);
+ } else {
+ $_POST['EndDate']='';
+ }
}
if (!isset($_POST['Branch'])) {
@@ -303,15 +325,15 @@
}
if (!isset($_POST['EndDate'])){
- $_POST['EndDate'] = Date($_SESSION['DefaultDateFormat'],Mktime(0,0,0,12,31,(Date('y')+20)));
+ $_POST['EndDate'] = '';
}
echo '<table><tr><td>' . _('Branch') . ':</td>
<td><input type="Text" name="Branch" size=11 maxlength=10 value=' . $_POST['Branch'] . '></td></tr>';
- echo '<table><tr><td>' . _('Start Date') . ':</td>
+ echo '<tr><td>' . _('Start Date') . ':</td>
<td><input type="Text" name="StartDate" size=11 maxlength=10 value=' . $_POST['StartDate'] . '></td></tr>';
- echo '<table><tr><td>' . _('End Date') . ':</td>
+ echo '<tr><td>' . _('End Date') . ':</td>
<td><input type="Text" name="EndDate" size=11 maxlength=10 value=' . $_POST['EndDate'] . '></td></tr>';
echo '<tr><td>' . _('Price') . ':</td>
@@ -326,32 +348,40 @@
function ReSequenceEffectiveDates ($Item, $PriceList, $CurrAbbrev, $CustomerID, $db) {
+ /*This is quite complicated - the idea is that prices set up should be unique and there is no way two prices could be returned as valid - when getting a price in includes/GetPrice.inc the logic is to first look for a price of the salestype/currency within the effective start and end dates - then if not get the price with a start date prior but a blank end date (the default price). We would not want two prices where the effective dates fall between an existing price so it is necessary to update enddates of prices - with me - I am just hanging on here myself
+
+ Prices with no end date are default prices and need to be ignored in this resquence*/
+
$SQL = "SELECT branchcode,
- startdate,
- enddate
+ startdate,
+ enddate
FROM prices
WHERE debtorno='" . $CustomerID . "'
AND stockid='" . $Item . "'
AND currabrev='" . $CurrAbbrev . "'
AND typeabbrev='" . $PriceList . "'
- ORDER BY branchcode,
- startdate,
- enddate";
+ AND enddate<>''
+ ORDER BY
+ branchcode,
+ startdate,
+ enddate";
+
$result = DB_query($SQL,$db);
unset($BranchCode);
while ($myrow = DB_fetch_array($result)){
if ($BranchCode != $myrow['branchcode']){
+ unset($NextDefaultStartDate); //a price with a blank end date
unset($NextStartDate);
unset($EndDate);
unset($StartDate);
$BranchCode = $myrow['branchcode'];
}
- if (isset($NextStartDate)){
+ if (isset($NextStartDate)){
if (Date1GreaterThanDate2(ConvertSQLDate($myrow['startdate']),$NextStartDate)){
$NextStartDate = ConvertSQLDate($myrow['startdate']);
- if (isset($EndDate)) {
+ if (Date1GreaterThanDate2(ConvertSQLDate($EndDate),ConvertSQLDate($myrow['startdate']))) {
/*Need to make the end date the new start date less 1 day */
$SQL = "UPDATE prices SET enddate = '" . FormatDateForSQL(DateAdd($NextStartDate,'d',-1)) . "'
WHERE stockid ='" .$Item . "'
Modified: trunk/doc/Change.log.html
===================================================================
--- trunk/doc/Change.log.html 2010-05-12 05:23:41 UTC (rev 3458)
+++ trunk/doc/Change.log.html 2010-05-15 10:36:56 UTC (rev 3459)
@@ -1,5 +1,6 @@
<p><font SIZE=4 COLOR=BLUE><b>webERP Change Log</b></font></p>
<p></p>
+<p>15/5/10 Phil: GetPrices.inc Prices.php and Prices_Customer.php - modified to allow default prices - with no end dates - reducing requirement to administer - also updated Prices section of the manual
<p>11/05/10 Tim: Exit MRP scripts gracefully if no MRP calculation has been done.</p>
<p>08/05/10 Lindsay: API was broken after adding a test for global variable $DatabaseName in ConnectDB_mysql* for this variable being set and using it as DB name if so. The variable of that name in the api_php.php has been changed to $api_DatabaseName.</p>
<p>08/05/10 Phil: GetPrices.inc now uses the new price startdate and enddate to return the price which falls within the date range base on the current date. Changes to Prices.php and Prices_Customer.php to allow entry of effective from and effective to dates and updating/deleting of prices with appropriate error trapping and rescheduling of enddates where start and end dates would otherwise overlap.</p>
Modified: trunk/doc/Manual/ManualPrices.html
===================================================================
--- trunk/doc/Manual/ManualPrices.html 2010-05-12 05:23:41 UTC (rev 3458)
+++ trunk/doc/Manual/ManualPrices.html 2010-05-15 10:36:56 UTC (rev 3459)
@@ -8,13 +8,19 @@
<br><br>
Prices are held by Sales Type and Currency. When setting up a customer account the sales type applicable to the customer is defined as is the currency applicable to invoices to the customer. There is no limit to the number of sales types that can be defined and therefore no limit to the number of different price lists that can be maintained. Further flexibility is available in that where necessary prices specific to a customer can also be defined, normally pricing will be specific to a customer but the system also has the capability of making prices specific to a particular branch of a customer. If a price is flagged as specific to a branch it will not be used for all other sales of the item to the customer, the default price of the item will be used. Further flexibility in discounting is available through the discount matrix (see below). If an item has an additional discount this can be entered at the time of order. The user also has the ability to change the price manually at the time of order. Sales analysis records allow reporting on sales types, so the analysis will report the volume of business done under each set of special pricing arrangements.
<br><br>
+<font size="+1"><b>Prices Effectivity Dates</b></font>
+<br><br>
+In many businesses a change in price has to come into effect at some later date and be published to customers in advance of the price being effective. This functionality was introduced in 2010. The date that a price is to be effective from must be specified at the time of entry. If the price is expected to continue indefinitely then it is not necessary to specify an end date. Any subsequent prices entered without an end date will superseed previous entries with starting dates prior.
+<br><br>
+If a price is entered with an end date then this takes precidence over prices with no end date - where the date of entry of an order falls within the date range for the price. It is the current server date that is used to compare against the date range in determining the price to use.
+<br><br>
<font size="+1"><b>Maintaining Prices</b></font>
<br><br>
-To maintain pricing, first an item must be selected. This can be done from the link "Select an Item " on any page or from the inventory tab of the menu the link "Modify Or Delete An Inventory Item Or Item Pricing" allows for a part to be selected. Once selected the options available to work with the item are shown, there is a link to "Maintain Pricing". The pricing page displays, the sales type and the currency of all prices defined for the item. The existing prices can be deleted or modified from this screen and new prices can be entered. It is not possible to enter two prices for the same sales type and currency.
+To maintain pricing, first an item must be selected. This can be done from the link "Select an Item " on any page or from the inventory tab of the menu the link "Modify Or Delete An Inventory Item Or Item Pricing" allows for a part to be selected. Once selected the options available to work with the item are shown, there is a link to "Maintain Pricing". The pricing page displays, the sales type and the currency of all prices defined for the item. The existing prices can be deleted or modified from this screen and new prices can be entered. It is possible to enter many prices for a given sales type and currency with different effective from and to dates. Normally the date that the price should be effective to can be left blank. Subsequent entries of prices with no end date can be entered and these will be used after the start date of these prices. When entering prices for a fixed date range - this price will take precidence over the default price with no end date. The system automatically adjusts the end date of prices where a new price with a fixed date range is entered with an overlapping period to a previously entered price. The end date of the previous price is adjusted to be the day prior to the start date of the new price. This ensures that there can only be one price returned in a given date range.
<br><br>
To enter prices for a specific customer and branch, the customer must first be selected. (See selecting customers). Going to the select an item page after having selected an item now shows an additional link to maintain the special pricing "Special Prices for customer - customer code". Clicking this link shows, the normal price for the sales type and currency of the customer selected and any special prices defined for this item/customer combination. If no special pricing is defined then the page shows only the normal price with a note that no special pricing is defined for this item and allows entry of the new special price. If a special price exists then it can be deleted or changed. A customer with many branches can have a special price for each branch or just a single branch having a special price beyond the special customer price. A special price applicable to only one branch will not apply to any of the other branches. Also, a special price set up for the customer account but with a blank branch field will apply to all branches unless there is a specific price set up for the branch.
<br><br>
-Prices can be amended at any time. However, changes to a price do not automatically cascade into sales orders for the item that are yet to be delivered, these would have to be changed manually as necessary.
+Prices can be amended at any time. However, changes to a price do not automatically cascade into sales orders for the item that are yet to be delivered, these orders would have to be changed manually as necessary.
<br><br>
There is a utility page that has some options for making bulk changes to pricing avoiding extensive re-keying exercises. Great care should be taken with this facility. System administrator permission is required to access this page accordingly.
<br><br>
Modified: trunk/includes/ConnectDB_mysqli.inc
===================================================================
--- trunk/includes/ConnectDB_mysqli.inc 2010-05-12 05:23:41 UTC (rev 3458)
+++ trunk/includes/ConnectDB_mysqli.inc 2010-05-15 10:36:56 UTC (rev 3459)
@@ -45,11 +45,11 @@
//DB wrapper functions to change only once for whole application
function DB_query ($SQL,
- &$Conn,
- $ErrorMessage='',
- $DebugMessage= '',
- $Transaction=false,
- $TrapErrors=true){
+ &$Conn,
+ $ErrorMessage='',
+ $DebugMessage= '',
+ $Transaction=false,
+ $TrapErrors=true){
global $debug;
global $PathPrefix;
Modified: trunk/includes/GetPrice.inc
===================================================================
--- trunk/includes/GetPrice.inc 2010-05-12 05:23:41 UTC (rev 3458)
+++ trunk/includes/GetPrice.inc 2010-05-15 10:36:56 UTC (rev 3459)
@@ -3,31 +3,8 @@
function GetPrice ($StockID, $DebtorNo, $BranchCode, $db){
$Price = 0;
-
- $sql="SELECT prices.price,
- prices.debtorno,
- prices.branchcode
- FROM prices,
- debtorsmaster
- WHERE debtorsmaster.salestype=prices.typeabbrev
- AND debtorsmaster.debtorno='" . $DebtorNo . "'
- AND prices.stockid = '" . $StockID . "'
- AND prices.currabrev = debtorsmaster.currcode
- AND prices.debtorno=debtorsmaster.debtorno
- 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){
-
- /* No result returned for customer and branch search try for just a customer match */
- $sql = "SELECT prices.price,
- prices.debtorno,
- prices.branchcode
+ /*Search by branch and customer for a date specified price */
+ $sql="SELECT prices.price
FROM prices,
debtorsmaster
WHERE debtorsmaster.salestype=prices.typeabbrev
@@ -35,54 +12,141 @@
AND prices.stockid = '" . $StockID . "'
AND prices.currabrev = debtorsmaster.currcode
AND prices.debtorno=debtorsmaster.debtorno
- AND prices.branchcode=''
+ AND prices.branchcode='" . $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){
-
- /*No special pricing use the customers normal price list */
- $sql = "SELECT prices.price,
- prices.debtorno,
- prices.branchcode
+ 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){
+ /*Need to try same specific search but for a default price with a zero end date */
+ $sql="SELECT prices.price,
+ prices.startdate
FROM prices,
debtorsmaster
WHERE debtorsmaster.salestype=prices.typeabbrev
AND debtorsmaster.debtorno='" . $DebtorNo . "'
AND prices.stockid = '" . $StockID . "'
- AND prices.debtorno=''
AND prices.currabrev = debtorsmaster.currcode
+ AND prices.debtorno=debtorsmaster.debtorno
+ AND prices.branchcode='" . $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){
-
- /* Now use the default salestype/price list cos all else has failed */
- $sql="SELECT prices.price
+ 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,
- debtorsmaster
- WHERE prices.stockid = '" . $StockID . "'
+ debtorsmaster
+ WHERE debtorsmaster.salestype=prices.typeabbrev
+ AND debtorsmaster.debtorno='" . $DebtorNo . "'
+ AND prices.stockid = '" . $StockID . "'
AND prices.currabrev = debtorsmaster.currcode
- AND debtorsmaster.debtorno='" . $DebtorNo . "'
- AND prices.typeabbrev='" . $_SESSION['DefaultPriceList'] . "'
- AND prices.debtorno=''
+ AND prices.debtorno=debtorsmaster.debtorno
+ AND prices.branchcode=''
AND prices.startdate <='" . Date('Y-m-d') . "'
- AND prices.enddate >='" . 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
+ $sql = "SELECT prices.price,
+ prices.startdate
+ FROM prices,
+ debtorsmaster
+ WHERE debtorsmaster.salestype=prices.typeabbrev
+ AND debtorsmaster.debtorno='" . $DebtorNo . "'
+ AND prices.stockid = '" . $StockID . "'
+ AND prices.currabrev = debtorsmaster.currcode
+ AND prices.debtorno=debtorsmaster.debtorno
+ AND prices.branchcode=''
+ AND prices.startdate <='" . Date('Y-m-d') . "'
+ AND prices.enddate >='0000-00-00'
+ ORDER BY prices.startdate DESC";
+
+ $result = DB_query($sql,$db,$ErrMsg);
- $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 */
- prnMsg(_('There are no prices set up for') . ' ' . $StockID,'warn');
- Return 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,
+ debtorsmaster
+ WHERE debtorsmaster.salestype=prices.typeabbrev
+ AND debtorsmaster.debtorno='" . $DebtorNo . "'
+ AND prices.stockid = '" . $StockID . "'
+ 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,
+ prices.startdate
+ FROM prices,
+ debtorsmaster
+ WHERE debtorsmaster.salestype=prices.typeabbrev
+ AND debtorsmaster.debtorno='" . $DebtorNo . "'
+ AND prices.stockid = '" . $StockID . "'
+ 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){
+
+ /* Now use the default salestype/price list cos all else has failed */
+ $sql="SELECT prices.price
+ FROM prices,
+ debtorsmaster
+ WHERE prices.stockid = '" . $StockID . "'
+ AND prices.currabrev = debtorsmaster.currcode
+ AND debtorsmaster.debtorno='" . $DebtorNo . "'
+ AND prices.typeabbrev='" . $_SESSION['DefaultPriceList'] . "'
+ 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){
+
+ /* Now use the default salestype/price list cos all else has failed */
+ $sql="SELECT prices.price
+ prices.startdate
+ FROM prices,
+ debtorsmaster
+ WHERE prices.stockid = '" . $StockID . "'
+ AND prices.currabrev = debtorsmaster.currcode
+ AND debtorsmaster.debtorno='" . $DebtorNo . "'
+ AND prices.typeabbrev='" . $_SESSION['DefaultPriceList'] . "'
+ AND prices.debtorno=''
+ 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 */
+ prnMsg(_('There are no prices set up for') . ' ' . $StockID,'warn');
+ Return 0;
+ }
+ }
+ }
+ }
}
-
}
}
}
Modified: trunk/includes/UserLogin.php
===================================================================
--- trunk/includes/UserLogin.php 2010-05-12 05:23:41 UTC (rev 3458)
+++ trunk/includes/UserLogin.php 2010-05-15 10:36:56 UTC (rev 3459)
@@ -19,6 +19,7 @@
*/
function userLogin($Name, $Password, $db) {
+
if (!isset($_SESSION['AccessLevel']) OR $_SESSION['AccessLevel'] == '' OR
(isset($Name) AND $Name != '')) {
/* if not logged in */
@@ -113,11 +114,7 @@
}
} // End of userid/password check
// Run with debugging messages for the system administrator(s) but not anyone else
- if (in_array(15, $_SESSION['AllowedPageSecurityTokens'])) {
- $debug = 1;
- } else {
- $debug = 0;
- }
+
return UL_OK; /* All is well */
}
Modified: trunk/includes/session.inc
===================================================================
--- trunk/includes/session.inc 2010-05-12 05:23:41 UTC (rev 3458)
+++ trunk/includes/session.inc 2010-05-15 10:36:56 UTC (rev 3459)
@@ -248,10 +248,13 @@
include($PathPrefix . 'includes/footer.inc');
exit;
}
-
-
}
+if (in_array(15, $_SESSION['AllowedPageSecurityTokens'])) {
+ $debug = 1;
+} else {
+ $debug = 0;
+}
function CryptPass( $Password ) {
global $CryptFunction;
if ( $CryptFunction == 'sha1' ) {
Modified: trunk/sql/mysql/upgrade3.11.1-3.12.sql
===================================================================
--- trunk/sql/mysql/upgrade3.11.1-3.12.sql 2010-05-12 05:23:41 UTC (rev 3458)
+++ trunk/sql/mysql/upgrade3.11.1-3.12.sql 2010-05-15 10:36:56 UTC (rev 3459)
@@ -153,7 +153,7 @@
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `systypes` VALUES(19, 'Picking List', 0);
-ALTER TABLE `prices` ADD `startdate` DATE NOT NULL , ADD `enddate` DATE NOT NULL DEFAULT '9999-12-31';
+ALTER TABLE `prices` ADD `startdate` DATE NOT NULL , ADD `enddate` DATE NOT NULL;
ALTER TABLE prices DROP PRIMARY KEY ,
ADD PRIMARY KEY ( `stockid` , `typeabbrev` , `currabrev` , `debtorno` , `startdate` , `enddate` ) ;
-UPDATE prices SET startdate='1999-01-01', enddate='9999-12-31';
+UPDATE prices SET startdate='1999-01-01';
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
