} elseif ($InputError != 1) {
/*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,\n\t\t typeabbrev,\n\t\t\t\t\t\t\t\t\tcurrabrev,\n\t\t\t\t\t\t\t\t\tdebtorno,\n\t\t\t\t\t\t\t\t\tprice,\n\t\t\t\t\t\t\t\t\tunits,\n\t\t\t\t\t\t\t\t\tconversionfactor,\n\t\t\t\t\t\t\t\t\tdecimalplaces,\n\t\t\t\t\t\t\t\t\tbranchcode,\n\t\t\t\t\t\t\t\t\tstartdate,\n\t\t\t\t\t\t\t\t\tenddate)\n\t\t\t\t\t\t\tVALUES (\n\t\t\t\t\t\t\t\t\t'" . $Item . "',\n\t\t\t\t\t\t\t\t\t'" . $SalesType . "',\n\t\t\t\t\t\t\t\t\t'" . $CurrCode . "',\n\t\t\t\t\t\t\t\t\t'" . $_SESSION['CustomerID'] . "',\n\t\t\t\t\t\t\t\t\t'" . filter_currency_input($_POST['Price']) . "',\n\t\t\t\t\t\t\t\t\t'" . $_POST['Units'] . "',\n\t\t\t\t\t\t\t\t\t'" . filter_number_input($_POST['ConversionFactor']) . "',\n\t\t\t\t\t\t\t\t\t'" . round($_POST['DecimalPlaces'], 0) . "',\n\t\t\t\t\t\t\t\t\t'" . $_POST['Branch'] . "',\n\t\t\t\t\t\t\t\t\t'" . FormatDateForSQL($_POST['StartDate']) . "',\n\t\t\t\t\t\t\t\t\t'" . FormatDateForSQL($_POST['EndDate']) . "'\n\t\t\t\t\t\t\t\t\t)";
$msg = _('Price added') . '.';
}
//run the SQL from either of the above possibilites
if ($InputError != 1) {
$result = DB_query($sql, $db, '', '', false, false);
if (DB_error_no($db) != 0) {
if ($msg == _('Price Updated')) {
$msg = _('The price could not be updated because') . ' - ' . DB_error_msg($db);
} else {
$msg = _('The price could not be added because') . ' - ' . DB_error_msg($db);
}
} else {
ReSequenceEffectiveDates($Item, $SalesType, $CurrCode, $_SESSION['CustomerID'], $db);
unset($_POST['EndDate']);
unset($_POST['StartDate']);
unset($_POST['Price']);
unset($_POST['Units']);
unset($_POST['ConversionFactor']);
unset($_POST['DecimalPlaces']);
}
}
prnMsg($msg);
} elseif (isset($_GET['delete'])) {
//the link to delete a selected record was clicked instead of the submit button
$sql = "DELETE FROM prices\n\t\t\tWHERE prices.stockid = '" . $Item . "'\n\t\t\tAND prices.typeabbrev='" . $SalesType . "'\n\t\t\tAND prices.currabrev ='" . $CurrCode . "'\n\t\t\tAND prices.debtorno='" . $_SESSION['CustomerID'] . "'\n\t\t\tAND prices.branchcode='" . $_GET['Branch'] . "'\n\t\t\tAND prices.startdate='" . $_GET['StartDate'] . "'\n\t\t\tAND prices.enddate='" . $_GET['EndDate'] . "'";
$result = DB_query($sql, $db);
prnMsg(_('This price has been deleted') . '!', 'success');
}
if (Is_Date($_POST['StartDate'])) {
$SQLStartDate = FormatDateForSQL($_POST['StartDate']);
}
$sql = "SELECT COUNT(salestype)\r\n\t\t\t\tFROM pricematrix\r\n\t\t\tWHERE stockid='" . $StockID . "'\r\n\t\t\tAND startdate='" . $SQLStartDate . "'\r\n\t\t\tAND enddate='" . $SQLEndDate . "'\r\n\t\t AND salestype='" . $_POST['TypeAbbrev'] . "'\r\n\t\t\tAND currabrev='" . $_POST['currabrev'] . "'\r\n\t\t\tAND quantitybreak='" . $_POST['quantitybreak'] . "'";
$result = DB_query($sql);
$myrow = DB_fetch_row($result);
if ($myrow[0] != 0 and !isset($_POST['OldTypeAbbrev']) and !isset($_POST['OldCurrAbrev'])) {
prnMsg(_('This price has already been entered. To change it you should edit it'), 'warn');
$InputError = 1;
}
if (isset($_POST['OldTypeAbbrev']) and isset($_POST['OldCurrAbrev']) and mb_strlen($StockID) > 1 and $InputError != 1) {
/* Update existing prices */
$sql = "UPDATE pricematrix SET \r\n\t\t\t\t\tsalestype='" . $_POST['SalesType'] . "',\r\n\t\t\t\t\tcurrabrev='" . $_POST['CurrAbrev'] . "',\r\n\t\t\t\t\tprice='" . filter_number_format($_POST['Price']) . "',\r\n\t\t\t\t\tstartdate='" . $SQLStartDate . "',\r\n\t\t\t\t\tenddate='" . $SQLEndDate . "',\r\n\t\t\t\t\tquantitybreak='" . filter_number_format($_POST['QuantityBreak']) . "'\r\n\t\t\t\tWHERE stockid='" . $StockID . "'\r\n\t\t\t\tAND startdate='" . $_POST['OldStartDate'] . "'\r\n\t\t\t\tAND enddate='" . $_POST['OldEndDate'] . "'\r\n\t\t\t\tAND salestype='" . $_POST['OldTypeAbbrev'] . "'\r\n\t\t\t\tAND currabrev='" . $_POST['OldCurrAbrev'] . "'\r\n\t\t\t\tAND quantitybreak='" . filter_number_format($_POST['OldQuantityBreak']) . "'";
$ErrMsg = _('Could not be update the existing prices');
$result = DB_query($sql, $ErrMsg);
ReSequenceEffectiveDates($StockID, $_POST['SalesType'], $_POST['CurrAbrev'], $_POST['QuantityBreak'], $db);
prnMsg(_('The price has been updated'), 'success');
} elseif ($InputError != 1) {
/* actions to take once the user has clicked the submit button
ie the page has called itself with some user input */
$sql = "INSERT INTO pricematrix (salestype,\r\n\t\t\t\t\t\t\tstockid,\r\n\t\t\t\t\t\t\tquantitybreak,\r\n\t\t\t\t\t\t\tprice,\r\n\t\t\t\t\t\t\tcurrabrev,\r\n\t\t\t\t\t\t\tstartdate,\r\n\t\t\t\t\t\t\tenddate)\r\n\t\t\t\t\tVALUES('" . $_POST['SalesType'] . "',\r\n\t\t\t\t\t\t'" . $_POST['StockID'] . "',\r\n\t\t\t\t\t\t'" . filter_number_format($_POST['QuantityBreak']) . "',\r\n\t\t\t\t\t\t'" . filter_number_format($_POST['Price']) . "',\r\n\t\t\t\t\t\t'" . $_POST['CurrAbrev'] . "',\r\n\t\t\t\t\t\t'" . $SQLStartDate . "',\r\n\t\t\t\t\t\t'" . $SQLEndDate . "')";
$ErrMsg = _('Failed to insert price data');
$result = DB_query($sql, $ErrMsg);
prnMsg(_('The price matrix record has been added'), 'success');
echo '<br />';
unset($_POST['StockID']);
unset($_POST['SalesType']);
unset($_POST['QuantityBreak']);
unset($_POST['Price']);
unset($_POST['CurrAbrev']);
unset($_POST['StartDate']);
$InputError = 1;
}
if (isset($_POST['OldTypeAbbrev']) and isset($_POST['OldCurrAbrev']) and mb_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\n\t\t\t\t\ttypeabbrev='" . $_POST['TypeAbbrev'] . "',\n\t\t\t\t\tcurrabrev='" . $_POST['CurrAbrev'] . "',\n\t\t\t\t\tprice='" . filter_number_format($_POST['Price']) . "',\n\t\t\t\t\tstartdate='" . FormatDateForSQL($_POST['StartDate']) . "',\n\t\t\t\t\tenddate='" . $SQLEndDate . "'\n\t\t\t\tWHERE prices.stockid='" . $Item . "'\n\t\t\t\tAND startdate='" . $_POST['OldStartDate'] . "'\n\t\t\t\tAND enddate ='" . $_POST['OldEndDate'] . "'\n\t\t\t\tAND prices.typeabbrev='" . $_POST['OldTypeAbbrev'] . "'\n\t\t\t\tAND prices.currabrev='" . $_POST['OldCurrAbrev'] . "'\n\t\t\t\tAND prices.debtorno=''";
$ErrMsg = _('Could not be update the existing prices');
$result = DB_query($sql, $ErrMsg);
ReSequenceEffectiveDates($Item, $_POST['TypeAbbrev'], $_POST['CurrAbrev'], $db);
prnMsg(_('The price has been updated'), 'success');
} elseif ($InputError != 1) {
/*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,\n\t\t\t\t\t\t\t\t\ttypeabbrev,\n\t\t\t\t\t\t\t\t\tcurrabrev,\n\t\t\t\t\t\t\t\t\tstartdate,\n\t\t\t\t\t\t\t\t\tenddate,\n\t\t\t\t\t\t\t\t\tprice)\n\t\t\t\t\t\t\tVALUES ('" . $Item . "',\n\t\t\t\t\t\t\t\t'" . $_POST['TypeAbbrev'] . "',\n\t\t\t\t\t\t\t\t'" . $_POST['CurrAbrev'] . "',\n\t\t\t\t\t\t\t\t'" . FormatDateForSQL($_POST['StartDate']) . "',\n\t\t\t\t\t\t\t\t'" . $SQLEndDate . "',\n\t\t\t\t\t\t\t\t'" . filter_number_format($_POST['Price']) . "')";
$ErrMsg = _('The new price could not be added');
$result = DB_query($sql, $ErrMsg);
ReSequenceEffectiveDates($Item, $_POST['TypeAbbrev'], $_POST['CurrAbrev'], $db);
prnMsg(_('The new price has been inserted'), 'success');
}
unset($_POST['Price']);
unset($_POST['StartDate']);
unset($_POST['EndDate']);
} elseif (isset($_GET['delete'])) {
//the link to delete a selected record was clicked instead of the submit button
$sql = "DELETE FROM prices\n\t\t\tWHERE prices.stockid = '" . $Item . "'\n\t\t\tAND prices.typeabbrev='" . $_GET['TypeAbbrev'] . "'\n\t\t\tAND prices.currabrev ='" . $_GET['CurrAbrev'] . "'\n\t\t\tAND prices.startdate = '" . $_GET['StartDate'] . "'\n\t\t\tAND prices.enddate = '" . $_GET['EndDate'] . "'\n\t\t\tAND prices.debtorno=''";
$ErrMsg = _('Could not delete this price');
$result = DB_query($sql, $ErrMsg);
prnMsg(_('The selected price has been deleted'), 'success');
}
//Always do this stuff
$sql = "SELECT\n\t\tcurrencies.currency,\n salestypes.sales_type,\n\t\tprices.price,\n\t\tprices.stockid,\n\t\tprices.typeabbrev,\n\t\tprices.currabrev,\n\t\tprices.startdate,\n\t\tprices.enddate,\n\t\tcurrencies.decimalplaces AS currdecimalplaces\n\tFROM prices\n\tINNER JOIN salestypes\n\t\tON prices.typeabbrev = salestypes.typeabbrev\n\tINNER JOIN currencies\n\t\tON prices.currabrev=currencies.currabrev\n\tWHERE prices.stockid='" . $Item . "'\n\tAND prices.debtorno=''\n\tORDER BY prices.currabrev,\n\t\tprices.typeabbrev,\n\t\tprices.startdate";
$result = DB_query($sql);