function executeSQL($sql, $db, $TrapErrors = False)
{
global $SQLFile;
/* Run an sql statement and return an error code */
if (!isset($SQLFile)) {
DB_IgnoreForeignKeys($db);
$result = DB_query($sql, $db, '', '', false, $TrapErrors);
$ErrorNumber = DB_error_no($db);
DB_ReinstateForeignKeys($db);
return $ErrorNumber;
} else {
fwrite($SQLFile, $sql . ";\n");
}
}
}
if ($_POST['NewLocationName'] == '') {
prnMsg(_('The new location name to change the old name to must be entered as well'), 'error');
$InputError = 1;
}
/*Now check that the new code doesn't already exist */
$result = DB_query("SELECT loccode FROM locations WHERE loccode='" . $_POST['NewLocationID'] . "'", $db);
if (DB_num_rows($result) != 0) {
echo '<br /><br />';
prnMsg(_('The replacement location code') . ': ' . $_POST['NewLocationID'] . ' ' . _('already exists as a location code in the system') . ' - ' . _('a unique location code must be entered for the new code'), 'error');
$InputError = 1;
}
if ($InputError == 0) {
// no input errors
$result = DB_Txn_Begin($db);
DB_IgnoreForeignKeys($db);
echo '<br />' . _('Adding the new location record');
$sql = "INSERT INTO locations (loccode,\n\t\t\t\t\t\t\t\t\t\tlocationname,\n\t\t\t\t\t\t\t\t\t\tdeladd1,\n\t\t\t\t\t\t\t\t\t\tdeladd2,\n\t\t\t\t\t\t\t\t\t\tdeladd3,\n\t\t\t\t\t\t\t\t\t\tdeladd4,\n\t\t\t\t\t\t\t\t\t\tdeladd5,\n\t\t\t\t\t\t\t\t\t\tdeladd6,\n\t\t\t\t\t\t\t\t\t\ttel,\n\t\t\t\t\t\t\t\t\t\tfax,\n\t\t\t\t\t\t\t\t\t\temail,\n\t\t\t\t\t\t\t\t\t\tcontact,\n\t\t\t\t\t\t\t\t\t\ttaxprovinceid,\n\t\t\t\t\t\t\t\t\t\tmanaged,\n\t\t\t\t\t\t\t\t\t\tcashsalecustomer,\n\t\t\t\t\t\t\t\t\t\tcashsalebranch)\n\t\t\t\tSELECT '" . $_POST['NewLocationID'] . "',\n\t\t\t\t\t '" . $_POST['NewLocationName'] . "',\n\t\t\t\t\t\tdeladd1,\n\t\t\t\t\t\tdeladd2,\n\t\t\t\t\t\tdeladd3,\n\t\t\t\t\t\tdeladd4,\n\t\t\t\t\t\tdeladd5,\n\t\t\t\t\t\tdeladd6,\n\t\t\t\t\t\ttel,\n\t\t\t\t\t\tfax,\n\t\t\t\t\t\temail,\n\t\t\t\t\t\tcontact,\n\t\t\t\t\t\ttaxprovinceid,\n\t\t\t\t\t\tmanaged,\n\t\t\t\t\t\tcashsalecustomer,\n\t\t\t\t\t\tcashsalebranch\n\t\t\t\tFROM locations\n\t\t\t\tWHERE loccode='" . $_POST['OldLocationID'] . "'";
$DbgMsg = _('The SQL statement that failed was');
$ErrMsg = _('The SQL to insert the new location record failed');
$result = DB_query($sql, $db, $ErrMsg, $DbgMsg, true);
echo ' ... ' . _('completed');
echo '<br />' . _('Changing the BOM table records');
$sql = "UPDATE bom SET loccode='" . $_POST['NewLocationID'] . "' WHERE loccode='" . $_POST['OldLocationID'] . "'";
$ErrMsg = _('The SQL to update the BOM records failed');
$result = DB_query($sql, $db, $ErrMsg, $DbgMsg, true);
echo ' ... ' . _('completed');
echo '<br />' . _('Changing the config table records');
$sql = "UPDATE config SET confvalue='" . $_POST['NewLocationID'] . "' WHERE confvalue='" . $_POST['OldLocationID'] . "'";
$ErrMsg = _('The SQL to update the BOM records failed');
$result = DB_query($sql, $db, $ErrMsg, $DbgMsg, true);
$sql = "UPDATE salesorders SET debtorno='" . $_POST['NewDebtorNo'] . "' WHERE debtorno='" . $_POST['OldDebtorNo'] . "'";
$ErrMsg = _('The SQL to update the sales order header records failed');
$result = DB_query($sql, $db, $ErrMsg, $DbgMsg, true);
prnMsg(_('Changing stock movement records'), 'info');
$sql = "UPDATE stockmoves SET debtorno='" . $_POST['NewDebtorNo'] . "' WHERE debtorno='" . $_POST['OldDebtorNo'] . "'";
$ErrMsg = _('The SQL to update the sales order header records failed');
$result = DB_query($sql, $db, $ErrMsg, $DbgMsg, true);
prnMsg(_('Changing user default customer records'), 'info');
$sql = "UPDATE www_users SET customerid='" . $_POST['NewDebtorNo'] . "' WHERE customerid='" . $_POST['OldDebtorNo'] . "'";
$ErrMsg = _('The SQL to update the user records failed');
$result = DB_query($sql, $db, $ErrMsg, $DbgMsg, true);
prnMsg(_('Changing the customer code in contract header records'), 'info');
$sql = "UPDATE contracts SET debtorno='" . $_POST['NewDebtorNo'] . "' WHERE debtorno='" . $_POST['OldDebtorNo'] . "'";
$ErrMsg = _('The SQL to update contract header records failed');
$result = DB_query($sql, $db, $ErrMsg, $DbgMsg, true);
$result = DB_IgnoreForeignKeys($db);
prnMsg(_('Deleting the old customer branch records from the CustBranch table'), 'info');
$sql = "DELETE FROM custbranch WHERE debtorno='" . $_POST['OldDebtorNo'] . "'";
$ErrMsg = _('The SQL to delete the old CustBranch records for the old debtor record failed');
$result = DB_query($sql, $db, $ErrMsg, $DbgMsg, true);
prnMsg(_('Deleting the customer code from the DebtorsMaster table'), 'info');
$sql = "DELETE FROM debtorsmaster WHERE debtorno='" . $_POST['OldDebtorNo'] . "'";
$ErrMsg = _('The SQL to delete the old debtor record failed');
$result = DB_query($sql, $db, $ErrMsg, $DbgMsg, true);
$result = DB_Txn_Commit($db);
$result = DB_ReinstateForeignKeys($db);
}
echo '<form onSubmit="return VerifyForm(this);" action="' . htmlspecialchars($_SERVER['PHP_SELF'], ENT_QUOTES, 'UTF-8') . '" method="post" class="noPrint">';
echo '<div class="centre">';
echo '<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />';
echo '<br />
}
if ($_POST['NewLocationName'] == '') {
prnMsg(_('The new location name to change the old name to must be entered as well'), 'error');
$InputError = 1;
}
/*Now check that the new code doesn't already exist */
$result = DB_query("SELECT loccode FROM locations WHERE loccode='" . $_POST['NewLocationID'] . "'");
if (DB_num_rows($result) != 0) {
echo '<br /><br />';
prnMsg(_('The replacement location code') . ': ' . $_POST['NewLocationID'] . ' ' . _('already exists as a location code in the system') . ' - ' . _('a unique location code must be entered for the new code'), 'error');
$InputError = 1;
}
if ($InputError == 0) {
// no input errors
$result = DB_Txn_Begin();
DB_IgnoreForeignKeys();
echo '<br />' . _('Adding the new location record');
$sql = "INSERT INTO locations (loccode,\n\t\t\t\t\t\t\t\t\t\tlocationname,\n\t\t\t\t\t\t\t\t\t\tdeladd1,\n\t\t\t\t\t\t\t\t\t\tdeladd2,\n\t\t\t\t\t\t\t\t\t\tdeladd3,\n\t\t\t\t\t\t\t\t\t\tdeladd4,\n\t\t\t\t\t\t\t\t\t\tdeladd5,\n\t\t\t\t\t\t\t\t\t\tdeladd6,\n\t\t\t\t\t\t\t\t\t\ttel,\n\t\t\t\t\t\t\t\t\t\tfax,\n\t\t\t\t\t\t\t\t\t\temail,\n\t\t\t\t\t\t\t\t\t\tcontact,\n\t\t\t\t\t\t\t\t\t\ttaxprovinceid,\n\t\t\t\t\t\t\t\t\t\tmanaged,\n\t\t\t\t\t\t\t\t\t\tcashsalecustomer,\n\t\t\t\t\t\t\t\t\t\tcashsalebranch)\n\t\t\t\tSELECT '" . $_POST['NewLocationID'] . "',\n\t\t\t\t\t '" . $_POST['NewLocationName'] . "',\n\t\t\t\t\t\tdeladd1,\n\t\t\t\t\t\tdeladd2,\n\t\t\t\t\t\tdeladd3,\n\t\t\t\t\t\tdeladd4,\n\t\t\t\t\t\tdeladd5,\n\t\t\t\t\t\tdeladd6,\n\t\t\t\t\t\ttel,\n\t\t\t\t\t\tfax,\n\t\t\t\t\t\temail,\n\t\t\t\t\t\tcontact,\n\t\t\t\t\t\ttaxprovinceid,\n\t\t\t\t\t\tmanaged,\n\t\t\t\t\t\tcashsalecustomer,\n\t\t\t\t\t\tcashsalebranch\n\t\t\t\tFROM locations\n\t\t\t\tWHERE loccode='" . $_POST['OldLocationID'] . "'";
$DbgMsg = _('The SQL statement that failed was');
$ErrMsg = _('The SQL to insert the new location record failed');
$result = DB_query($sql, $ErrMsg, $DbgMsg, true);
echo ' ... ' . _('completed');
echo '<br />' . _('Changing the BOM table records');
$sql = "UPDATE bom SET loccode='" . $_POST['NewLocationID'] . "' WHERE loccode='" . $_POST['OldLocationID'] . "'";
$ErrMsg = _('The SQL to update the BOM records failed');
$result = DB_query($sql, $ErrMsg, $DbgMsg, true);
echo ' ... ' . _('completed');
echo '<br />' . _('Changing the config table records');
$sql = "UPDATE config SET confvalue='" . $_POST['NewLocationID'] . "' WHERE confvalue='" . $_POST['OldLocationID'] . "'";
$ErrMsg = _('The SQL to update the BOM records failed');
$result = DB_query($sql, $ErrMsg, $DbgMsg, true);
function executeSQL($sql, $db, $TrapErrors = False)
{
/* Run an sql statement and return an error code */
DB_IgnoreForeignKeys($db);
$result = DB_query($sql, $db, '', '', false, $TrapErrors);
$ErrorNumber = DB_error_no($db);
DB_ReinstateForeignKeys($db);
return $ErrorNumber;
}
$SQLScripts[] = './sql/mysql/upgrade4.11.4-4.12.sql';
case '4.12':
$SQLScripts[] = './sql/mysql/upgrade4.12-4.12.1.sql';
case '4.12.1':
$SQLScripts[] = './sql/mysql/upgrade4.12.1-4.12.2.sql';
case '4.12.2':
$SQLScripts[] = './sql/mysql/upgrade4.12.2-4.12.3.sql';
break;
}
//end switch
}
} else {
//$DBType is not mysql or mysqli
prnMsg(_('Only mysql upgrades are performed seamlessly at this time. Your database will need to be manually updated'), 'info');
}
$result = DB_IgnoreForeignKeys();
foreach ($SQLScripts as $SQLScriptFile) {
$SQLEntries = file($SQLScriptFile);
$ScriptFileEntries = sizeof($SQLEntries);
$sql = '';
$InAFunction = false;
echo '<br />
<table>
<tr>
<th colspan="2">' . _('Applying') . ' ' . $SQLScriptFile . '</th>
</tr>';
for ($i = 0; $i <= $ScriptFileEntries; $i++) {
$SQLEntries[$i] = trim($SQLEntries[$i]);
if (mb_substr($SQLEntries[$i], 0, 2) != '--' and mb_substr($SQLEntries[$i], 0, 3) != 'USE' and mb_strstr($SQLEntries[$i], '/*') == FALSE and mb_strlen($SQLEntries[$i]) > 1) {
$sql .= ' ' . $SQLEntries[$i];
//check if this line kicks off a function definition - pg chokes otherwise
