<?php
ChangeColumnType('language', 'www_users', 'VARCHAR(10)', 'NOT NULL', 'en_GB.utf8', $db);
DropTable('buckets', 'availdate', $db);
UpdateDBNo(basename(__FILE__, '.php'), $db);
<?php
ChangeColumnType('area', 'custbranch', 'CHAR(3)', 'NOT NULL', '', $db);
AddColumn('specialinstructions', 'custbranch', 'TEXT', 'NOT NULL', '', 'brpostaddr6', $db);
AddColumn('parentgroupname', 'accountgroups', 'VARCHAR(30)', 'NOT NULL', '', 'sequenceintb', $db);
DropTable('worksorders', 'accumvalueissued', $db);
CreateTable('workorders', "CREATE TABLE `workorders` (\n wo int(11) NOT NULL,\n loccode char(5) NOT NULL default '',\n requiredby date NOT NULL default '0000-00-00',\n startdate date NOT NULL default '0000-00-00',\n costissued double NOT NULL default '0',\n closed tinyint(4) NOT NULL default '0',\n PRIMARY KEY (`wo`),\n KEY LocCode (`loccode`),\n KEY StartDate (`startdate`),\n KEY RequiredBy (`requiredby`)\n)", $db);
AddConstraint('workorders', 'workorders_ibfk_1', 'loccode', 'locations', 'loccode', $db);
CreateTable('woitems', "CREATE TABLE `woitems` (\n wo int(11) NOT NULL,\n stockid char(20) NOT NULL default '',\n qtyreqd double NOT NULL DEFAULT 1,\n qtyrecd double NOT NULL DEFAULT 0,\n stdcost double NOT NULL,\n nextlotsnref varchar(20) DEFAULT '',\n PRIMARY KEY (`wo`, `stockid`),\n KEY `stockid` (`stockid`)\n)", $db);
AddConstraint('woitems', 'woitems_ibfk_1', 'stockid', 'stockmaster', 'stockid', $db);
AddConstraint('woitems', 'woitems_ibfk_2', 'wo', 'workorders', 'wo', $db);
CreateTable('worequirements', "CREATE TABLE `worequirements` (\n wo int(11) NOT NULL,\n parentstockid varchar(20) NOT NULL,\n stockid varchar(20) NOT NULL,\n qtypu double NOT NULL DEFAULT 1,\n stdcost double NOT NULL DEFAULT 0,\n autoissue tinyint NOT NULL DEFAULT 0,\n PRIMARY KEY (`wo`, `parentstockid`,`stockid`)\n)", $db);
AddConstraint('worequirements', 'worequirements_ibfk_1', 'wo', 'workorders', 'wo', $db);
AddConstraint('worequirements', 'worequirements_ibfk_2', 'stockid', 'stockmaster', 'stockid', $db);
AddConstraint('worequirements', 'worequirements_ibfk_3', 'parentstockid', 'woitems', 'stockid', $db);
AddColumn('autoissue', 'bom', 'TINYINT', 'NOT NULL', '0', 'quantity', $db);
NewConfigValue('AutoIssue', '1', $db);
DropIndex('stockmoves', 'StockID', $db);
AddIndex(array('reference'), 'stockmoves', 'stockmoves', $db);
DropPrimaryKey('recurrsalesorderdetails', array('recurrorderno', 'stkcode'), $db);
UpdateDBNo(basename(__FILE__, '.php'), $db);
<?php
/* New database components required for contracts module
*/
DropTable('contracts', 'rate', $db);
DropTable('contractreqts', 'component', $db);
DropTable('contractbom', 'component', $db);
CreateTable('contractbom', "CREATE TABLE IF NOT EXISTS `contractbom` (\n contractref varchar(20) NOT NULL DEFAULT '0',\n `stockid` varchar(20) NOT NULL DEFAULT '',\n `workcentreadded` char(5) NOT NULL DEFAULT '',\n `quantity` double NOT NULL DEFAULT '1',\n PRIMARY KEY (`contractref`,`stockid`,`workcentreadded`),\n KEY `Stockid` (`stockid`),\n KEY `ContractRef` (`contractref`),\n KEY `WorkCentreAdded` (`workcentreadded`),\n CONSTRAINT `contractbom_ibfk_1` FOREIGN KEY (`workcentreadded`) REFERENCES `workcentres` (`code`),\n CONSTRAINT `contractbom_ibfk_3` FOREIGN KEY (`stockid`) REFERENCES `stockmaster` (`stockid`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8", $db);
CreateTable('contractreqts', "CREATE TABLE IF NOT EXISTS `contractreqts` (\n `contractreqid` int(11) NOT NULL AUTO_INCREMENT,\n `contractref` varchar(20) NOT NULL DEFAULT '0',\n `requirement` varchar(40) NOT NULL DEFAULT '',\n `quantity` double NOT NULL DEFAULT '1',\n `costperunit` double NOT NULL DEFAULT '0.0000',\n PRIMARY KEY (`contractreqid`),\n KEY `ContractRef` (`contractref`),\n CONSTRAINT `contractreqts_ibfk_1` FOREIGN KEY (`contractref`) REFERENCES `contracts` (`contractref`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8", $db);
CreateTable('contracts', "CREATE TABLE IF NOT EXISTS `contracts` (\n `contractref` varchar(20) NOT NULL DEFAULT '',\n `contractdescription` text NOT NULL DEFAULT '',\n `debtorno` varchar(10) NOT NULL DEFAULT '',\n `branchcode` varchar(10) NOT NULL DEFAULT '',\n `loccode` varchar(5) NOT NULL DEFAULT '',\n `status` tinyint NOT NULL DEFAULT 0,\n `categoryid` varchar(6) NOT NULL DEFAULT '',\n `orderno` int(11) NOT NULL DEFAULT '0',\n `customerref` VARCHAR( 20 ) NOT NULL DEFAULT '',\n `margin` double NOT NULL DEFAULT '1',\n `wo` int(11) NOT NULL DEFAULT '0',\n `requireddate` date NOT NULL DEFAULT '0000-00-00',\n `drawing` varchar(50) NOT NULL DEFAULT '',\n `exrate` double NOT NULL DEFAULT '1',\n PRIMARY KEY (`contractref`),\n KEY `OrderNo` (`orderno`),\n KEY `CategoryID` (`categoryid`),\n KEY `Status` (`status`),\n KEY `WO` (`wo`),\n KEY `loccode` (`loccode`),\n KEY `DebtorNo` (`debtorno`,`branchcode`),\n CONSTRAINT `contracts_ibfk_1` FOREIGN KEY (`debtorno`, `branchcode`) REFERENCES `custbranch` (`debtorno`, `branchcode`),\n CONSTRAINT `contracts_ibfk_2` FOREIGN KEY (`categoryid`) REFERENCES `stockcategory` (`categoryid`),\n CONSTRAINT `contracts_ibfk_3` FOREIGN KEY (`loccode`) REFERENCES `locations` (`loccode`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8", $db);
CreateTable('contractcharges', "CREATE TABLE IF NOT EXISTS `contractcharges` (\n `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,\n `contractref` varchar(20) NOT NULL,\n `transtype` SMALLINT NOT NULL DEFAULT 20,\n `transno` INT NOT NULL DEFAULT 0,\n `amount` double NOT NULL DEFAULT 0,\n `narrative` TEXT NOT NULL DEFAULT '',\n `anticipated` TINYINT NOT NULL DEFAULT 0,\n INDEX ( `contractref` , `transtype` , `transno` ),\n CONSTRAINT `contractcharges_ibfk_1` FOREIGN KEY (`contractref`) REFERENCES `contracts` (`contractref`),\n CONSTRAINT `contractcharges_ibfk_2` FOREIGN KEY (`transtype`) REFERENCES `systypes` (`typeid`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8", $db);
UpdateDBNo(25, $db);
$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);
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)", $db);
AddColumn('assetid', 'purchorderdetails', 'int(11)', 'NOT NULL', 0, 'total_amount', $db);
/* New database stuff to move the page security levels to a
* database table.
*/
DropTable('scripts', 'pagedescription', $db);
CreateTable('scripts', "CREATE TABLE `scripts` (\n\t`script` varchar(78) NOT NULL DEFAULT '',\n\t`pagesecurity` int(11) NOT NULL DEFAULT 1,\n\t`description` varchar(78) NOT NULL DEFAULT '',\n\tPRIMARY KEY (`script`)\n)", $db);
NewScript('AccountGroups.php', 10, $db);
NewScript('AccountSections.php', 10, $db);
NewScript('AddCustomerContacts.php', 3, $db);
NewScript('AddCustomerNotes.php', 3, $db);
NewScript('AddCustomerTypeNotes.php', 3, $db);
NewScript('AgedDebtors.php', 2, $db);
NewScript('AgedSuppliers.php', 2, $db);
NewScript('Areas.php', 3, $db);
NewScript('AuditTrail.php', 15, $db);
NewScript('BankAccounts.php', 10, $db);
NewScript('BankMatching.php', 7, $db);
NewScript('BankReconciliation.php', 7, $db);
NewScript('BOMExtendedQty.php', 2, $db);
NewScript('BOMIndented.php', 2, $db);
if (array_key_exists("p", $options)) {
$pass = $options["p"];
}
if (array_key_exists("h", $options)) {
$hostName = $options["h"];
}
$link = new mysqli($hostName, $user, $pass);
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit;
} else {
printf("Connection to MySQL Server Succesful\n");
}
CreateDatabase($link);
if (array_key_exists("create_table", $options)) {
DropTable($link);
CreateTable($link);
return;
}
$fileName = "users.csv";
if (array_key_exists("file", $options)) {
$fileName = $options["file"];
echo "Opened file " . $fileName . "\n";
}
$file = fopen($fileName, "r");
if ($file !== FALSE) {
$data2DArray = array();
// create array
// while there is still rows in the file
while (($data = fgetcsv($file, 1000, ",")) !== FALSE) {
$data2DArray[] = $data;
<?php
/* Drop table assetmanager if version 1 of the asset manager is still
* in the database
*/
DropTable('assetmanager', 'lifetime', $db);
UpdateDBNo(2, $db);