<?php
/* Ensure that all tablse use the utf8_general_cli
* character set
*/
$sql = 'SHOW TABLES';
$result = DB_Query($sql, $db);
while ($table = DB_fetch_array($result)) {
if (CharacterSet($table[0], $db) != 'utf8_general_ci') {
$response = executeSQL('ALTER TABLE ' . $table[0] . ' CONVERT TO CHARACTER SET utf8', $db);
if ($response == 0) {
OutputResult(_('The character set of') . ' ' . $table[0] . ' ' . _('has been changed to utf8_general_ci'), 'success');
} else {
OutputResult(_('The character set of') . ' ' . $table[0] . ' ' . _('could not be changed to utf8_general_ci'), 'error');
}
} else {
OutputResult(_('The character set of') . ' ' . $table[0] . ' ' . _('is already utf8_general_ci'), 'info');
}
}
UpdateDBNo(1, $db);
<?php
/* sql script pricing end dates per Ricard
*/
ChangeColumnDefault('enddate', 'prices', 'Date', 'NOT NULL', '0000-00-00', $db);
$sql = "DESC prices enddate";
$result = DB_query($sql, $db);
if (DB_num_rows($result) > 0) {
$sql = "SELECT price FROM prices WHERE enddate='9999-12-31'";
$result = DB_query($sql, $db);
if (DB_num_rows($result) > 0) {
$response = executeSQL("UPDATE prices SET startdate='1999-01-01', enddate='0000-00-00' WHERE enddate='9999-12-31'", $db, False);
if ($response == 0) {
OutputResult(_('The enddate column in the prices table has been updated'), 'success');
} else {
OutputResult(_('The enddate column in the prices table cannot be updated'), 'error');
}
} else {
OutputResult(_('The enddate column in the prices table is already correct'), 'info');
}
} else {
$response = executeSQL("UPDATE prices SET startdate='1999-01-01', enddate='0000-00-00' WHERE enddate='9999-12-31'", $db, False);
}
UpdateDBNo(49, $db);
<?php
/* Ensure the correct conversion factor is stored in the purchase
* order line record
*/
if (!isset($SQLFile)) {
$sql = "SELECT podetailitem,\n\t\t\titemcode,\n\t\t\tpurchorders.supplierno\n\t\tFROM purchorderdetails\n\t\tLEFT JOIN purchorders\n\t\t\tON purchorders.orderno=purchorderdetails.orderno\n\t\tWHERE conversionfactor=0";
$result = DB_query($sql, $db);
while ($myrow = DB_fetch_array($result)) {
$ConversionFactorSQL = "SELECT conversionfactor\n\t\t\t\t\t\t\tFROM purchdata\n\t\t\t\t\t\t\tWHERE supplierno='" . $myrow['supplierno'] . "'\n\t\t\t\t\t\t\t\tAND stockid='" . $myrow['itemcode'] . "'";
$ConversionFactorResult = DB_query($ConversionFactorSQL, $db);
if (DB_num_rows($ConversionFactorResult) > 0) {
$ConversionFactorRow = DB_fetch_array($ConversionFactorResult);
$ConversionFactor = $ConversionFactorRow['conversionfactor'];
} else {
$ConversionFactor = 1;
}
$UpdateSQL = "UPDATE purchorderdetails\n\t\t\t\tSET conversionfactor='" . $ConversionFactor . "'\n\t\t\t\tWHERE podetailitem='" . $myrow['podetailitem'] . "'";
$UpdateResult = executeSQL($UpdateSQL, $db);
}
OutputResult(_('Purchase order details have been correctly updated'), 'success');
}
UpdateDBNo(57, $db);
$response = executeSQL("DELETE stockmaster.* FROM stockmaster INNER JOIN stockcategory ON stockmaster.categoryid=stockcategory.categoryid WHERE stockcategory.stocktype='A'", $db, False);
if ($response == 0) {
OutputResult(_('The fixed assets have been removed from stockmaster table'), 'success');
} else {
OutputResult(_('The fixed assets could not be removed from stockmaster table'), 'error');
}
} else {
OutputResult(_('The fixed assets have already been removed from stockmaster table'), 'info');
}
ChangeColumnName('id', 'fixedassets', 'int(11)', 'NOT NULL', 0, 'assetid', $db, 'AUTO_INCREMENT');
$sql = "SELECT categoryid FROM stockcategory WHERE stocktype='A'";
$result = DB_query($sql, $db);
if (DB_num_rows($result) != 0) {
$response = executeSQL("DELETE FROM stockcategory WHERE stocktype='A'", $db, False);
if ($response == 0) {
OutputResult(_('The fixed assets have been removed from stockmaster table'), 'success');
} else {
OutputResult(_('The fixed assets could not be removed from stockmaster table'), 'error');
}
} else {
OutputResult(_('The fixed assets have already been removed from stockmaster table'), 'info');
}
DropColumn('stockid', 'fixedassets', $db);
InsertRecord('systypes', array('typeid', 'typename'), array('41', 'Asset Addition'), array('typeid', 'typename', 'typeno'), array('41', 'Asset Addition', '1'), $db);
InsertRecord('systypes', array('typeid', 'typename'), array('42', 'Asset Category Change'), array('typeid', 'typename', 'typeno'), array('42', 'Asset Category Change', '1'), $db);
InsertRecord('systypes', array('typeid', 'typename'), array('43', 'Delete w/down asset'), array('typeid', 'typename', 'typeno'), array('43', 'Delete w/down asset', '1'), $db);
InsertRecord('systypes', array('typeid', 'typename'), array('44', 'Depreciation'), array('typeid', 'typename', 'typeno'), array('44', 'Depreciation', '1'), $db);
CreateTable('fixedassettrans', "CREATE TABLE fixedassettrans(\nid INT( 11 ) NOT NULL AUTO_INCREMENT ,\nassetid INT( 11 ) NOT NULL ,\ntranstype TINYINT( 4 ) NOT NULL ,\ntransdate DATE NOT NULL,\ntransno INT NOT NULL ,\nperiodno SMALLINT( 6 ) NOT NULL ,\ninputdate DATE NOT NULL ,\nfixedassettranstype varchar(8) NOT NULL ,\namount DOUBLE NOT NULL ,\nPRIMARY KEY ( id ) ,\nINDEX ( assetid, transtype, transno ) ,\nINDEX ( inputdate ),\nINDEX (transdate)\n) ENGINE = InnoDB DEFAULT CHARSET = utf8", $db);
AddColumn('assetid', 'purchorderdetails', 'int(11)', 'NOT NULL', 0, 'total_amount', $db);
InsertRecord('systypes', array('typeid', 'typename'), array('49', 'Import Fixed Assets'), array('typeid', 'typename', 'typeno'), array('49', 'Import Fixed Assets', '1'), $db);
UpdateDBNo(42, $db);
InsertRecord('menu', array('id', 'caption', 'parent', 'href'), array(176, 'PC Expenses', 173, 'PcExpenses.php?'), array('id', 'caption', 'parent', 'href'), array(176, 'PC Expenses', 173, 'PcExpenses.php?'), $db);
InsertRecord('menu', array('id', 'caption', 'parent', 'href'), array(177, 'Expenses for Type of PC Tab', 173, 'PcExpensesTypeTab.php?'), array('id', 'caption', 'parent', 'href'), array(177, 'Expenses for Type of PC Tab', 173, 'PcExpensesTypeTab.php?'), $db);
InsertRecord('menu', array('id', 'caption', 'parent', 'href'), array(178, 'Configuration', -1, '#'), array('id', 'caption', 'parent', 'href'), array(178, 'Configuration', -1, '#'), $db);
InsertRecord('menu', array('id', 'caption', 'parent', 'href'), array(179, 'General Settings', 178, '#'), array('id', 'caption', 'parent', 'href'), array(179, 'General Settings', 178, '#'), $db);
InsertRecord('menu', array('id', 'caption', 'parent', 'href'), array(180, 'Company Preferences', 179, 'CompanyPreferences.php?'), array('id', 'caption', 'parent', 'href'), array(180, 'Company Preferences', 179, 'CompanyPreferences.php?'), $db);
InsertRecord('menu', array('id', 'caption', 'parent', 'href'), array(181, 'Configuration Settings', 179, 'SystemParameters.php?'), array('id', 'caption', 'parent', 'href'), array(181, 'Configuration Settings', 179, 'SystemParameters.php?'), $db);
InsertRecord('menu', array('id', 'caption', 'parent', 'href'), array(188, 'Page Security Settings', 179, 'PageSecurity.php?'), array('id', 'caption', 'parent', 'href'), array(188, 'Page Security Settings', 179, 'PageSecurity.php?'), $db);
InsertRecord('menu', array('id', 'caption', 'parent', 'href'), array(182, 'Users', 178, '#'), array('id', 'caption', 'parent', 'href'), array(182, 'Users', 178, '#'), $db);
InsertRecord('menu', array('id', 'caption', 'parent', 'href'), array(183, 'User Maintenance', 182, 'WWW_Users.php?'), array('id', 'caption', 'parent', 'href'), array(183, 'User Maintenance', 182, 'WWW_Users.php?'), $db);
InsertRecord('menu', array('id', 'caption', 'parent', 'href'), array(184, 'Role Permissions', 182, 'WWW_Access.php?'), array('id', 'caption', 'parent', 'href'), array(184, 'Role Permissions', 182, 'WWW_Access.php?'), $db);
InsertRecord('menu', array('id', 'caption', 'parent', 'href'), array(185, 'Menu Access Rights', 182, 'MenuAccess.php?'), array('id', 'caption', 'parent', 'href'), array(185, 'Menu Access Rights', 182, 'MenuAccess.php?'), $db);
InsertRecord('menu', array('id', 'caption', 'parent', 'href'), array(186, 'General Ledger Setup', 178, '#'), array('id', 'caption', 'parent', 'href'), array(186, 'General Ledger Setup', 178, '#'), $db);
InsertRecord('menu', array('id', 'caption', 'parent', 'href'), array(187, 'Bank Accounts', 186, 'BankAccounts.php?'), array('id', 'caption', 'parent', 'href'), array(187, 'Bank Accounts', 186, 'BankAccounts.php?'), $db);
CreateTable('usermenurights', "CREATE TABLE `usermenurights` (\n\t`userid` varchar(20) NOT NULL DEFAULT '',\n\t`menuid` int(11) NOT NULL DEFAULT 0,\n\t`access` tinyint NOT NULL DEFAULT 1,\n\tPRIMARY KEY (`userid`, `menuid`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8", $db);
if (DB_table_exists('usermenurights', $db)) {
$sql = "select userid FROM usermenurights";
$result = DB_query($sql, $db);
if (DB_num_rows($result) == 0) {
$response = executeSQL("INSERT INTO `usermenurights` (userid,menuid,access) SELECT www_users.userid, menu.`id`, 1 FROM menu, www_users", $db, False);
if ($response == 0) {
OutputResult(_('The usermenurights table has been populated'), 'success');
} else {
OutputResult(_('The usermenurights table could not be populated'), 'error');
}
} else {
OutputResult(_('The usermenurights table is already populated'), 'info');
}
} else {
$response = executeSQL("INSERT INTO `usermenurights` (userid,menuid,access) SELECT www_users.userid, menu.`id`, 1 FROM menu, www_users", $db, False);
}
UpdateDBNo(46, $db);
<?php
$CompanyDir = $path_to_root . '/companies/' . $_POST['company_name'];
$Result = mkdir($CompanyDir . '/FormDesigns');
if ($Result) {
copy($path_to_root . '/companies/weberpdemo/FormDesigns/GoodsReceived.xml', $CompanyDir . '/FormDesigns/GoodsReceived.xml');
copy($path_to_root . '/companies/weberpdemo/FormDesigns/PickingList.xml', $CompanyDir . '/FormDesigns/PickingList.xml');
copy($path_to_root . '/companies/weberpdemo/FormDesigns/PurchaseOrder.xml', $CompanyDir . '/FormDesigns/PurchaseOrder.xml');
copy($path_to_root . '/companies/weberpdemo/FormDesigns/SalesInvoice.xml', $CompanyDir . '/FormDesigns/SalesInvoice.xml');
OutputResult(_('The contents of the Form Design folder has been copied to the company folder'), 'info');
} else {
OutputResult(_('The contents of the Form Design folder could not be copied to the company folder'), 'info');
}
UpdateDBNo(62, $db);
<?php
$sql = "SELECT userid, modulesallowed FROM www_users";
$result = DB_query($sql, $db);
while ($myrow = DB_fetch_array($result)) {
$NewModulesAllowed = '';
if (mb_strlen($myrow['modulesallowed']) == 16) {
$SysConfig = mb_substr($myrow['modulesallowed'], 14, 2);
$NewModulesAllowed = mb_substr($myrow['modulesallowed'], 0, 14) . '1,1,' . $SysConfig;
} else {
if (mb_strlen($myrow['modulesallowed']) == 18) {
$SysConfig = mb_substr($myrow['modulesallowed'], 16, 2);
$NewModulesAllowed = mb_substr($myrow['modulesallowed'], 0, 16) . '1,' . $SysConfig;
}
}
if (mb_strlen($NewModulesAllowed) > 0) {
$response = executeSQL("UPDATE www_users SET modulesallowed='" . $NewModulesAllowed . "' WHERE userid='" . $myrow['userid'] . "'", $db, False);
if ($response == 0) {
OutputResult(_('The modules allowed field has been updated for the latest modules'), 'success');
} else {
OutputResult(_('The modules allowed field cannot be updated for the latest modules') . ' ' . $NewName, 'error');
}
} else {
OutputResult(_('The modules allowed field is already the right length'), 'info');
}
}
UpdateDBNo(50, $db);
<?php
/* Update config value and default price end date
*/
$UpdateSQL = "UPDATE config SET confvalue = '4.0' WHERE confname='VersionNumber'";
$UpdateSQL2 = "UPDATE prices SET enddate = '2030-01-01' WHERE enddate = '0000-00-00'";
$UpdateSQL3 = "ALTER TABLE prices ALTER COLUMN enddate SET DEFAULT '2030-01-01'";
$UpdateResult = executeSQL($UpdateSQL, $db);
$UpdateResult2 = executeSQL($UpdateSQL2, $db);
$UpdateResult3 = executeSQL($UpdateSQL3, $db);
OutputResult(_('Default end date for prices updated and set to 2030-01-01'), 'success');
UpdateDBNo(58, $db);
