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