<?php
DropConstraint('stockcatproperties', 'stockcatproperties_ibfk_2', $db);
DropConstraint('stockcatproperties', 'stockcatproperties_ibfk_3', $db);
RemoveScript('FixedAssetList.php', $db);
RemoveScript('MenuAccess.php', $db);
RemoveScript('OrderEntryDiscountPricing.php', $db);
RemoveScript('PrintSalesOrder.php', $db);
RemoveScript('ReportBug.php', $db);
RemoveScript('ReportletContainer.php', $db);
RemoveScript('SystemCheck.php', $db);
UpdateDBNo(basename(__FILE__, '.php'), $db);
<?php
DropConstraint('recurringsalesorders', 'recurrsalesorderdetails_ibfk_1', $db);
AddConstraint('recurrsalesorderdetails', 'recurrsalesorderdetails_ibfk_1', 'recurrorderno', 'recurringsalesorders', 'recurrorderno', $db);
UpdateDBNo(59, $db);
<?php
/* Remove foreign key from debtortrans table
*/
DropConstraint('debtortrans', 'debtortrans_ibfk_1', $db);
UpdateDBNo(53, $db);
CreateTable('reports', "CREATE TABLE `reports` (\n `id` int(5) NOT NULL auto_increment,\n `reportname` varchar(30) NOT NULL default '',\n `reporttype` char(3) NOT NULL default 'rpt',\n `groupname` varchar(9) NOT NULL default 'misc',\n `defaultreport` enum('1','0') NOT NULL default '0',\n `papersize` varchar(15) NOT NULL default 'A4,210,297',\n `paperorientation` enum('P','L') NOT NULL default 'P',\n `margintop` int(3) NOT NULL default '10',\n `marginbottom` int(3) NOT NULL default '10',\n `marginleft` int(3) NOT NULL default '10',\n `marginright` int(3) NOT NULL default '10',\n `coynamefont` varchar(20) NOT NULL default 'Helvetica',\n `coynamefontsize` int(3) NOT NULL default '12',\n `coynamefontcolor` varchar(11) NOT NULL default '0,0,0',\n `coynamealign` enum('L','C','R') NOT NULL default 'C',\n `coynameshow` enum('1','0') NOT NULL default '1',\n `title1desc` varchar(50) NOT NULL default '%reportname%',\n `title1font` varchar(20) NOT NULL default 'Helvetica',\n `title1fontsize` int(3) NOT NULL default '10',\n `title1fontcolor` varchar(11) NOT NULL default '0,0,0',\n `title1fontalign` enum('L','C','R') NOT NULL default 'C',\n `title1show` enum('1','0') NOT NULL default '1',\n `title2desc` varchar(50) NOT NULL default 'Report Generated %date%',\n `title2font` varchar(20) NOT NULL default 'Helvetica',\n `title2fontsize` int(3) NOT NULL default '10',\n `title2fontcolor` varchar(11) NOT NULL default '0,0,0',\n `title2fontalign` enum('L','C','R') NOT NULL default 'C',\n `title2show` enum('1','0') NOT NULL default '1',\n `filterfont` varchar(10) NOT NULL default 'Helvetica',\n `filterfontsize` int(3) NOT NULL default '8',\n `filterfontcolor` varchar(11) NOT NULL default '0,0,0',\n `filterfontalign` enum('L','C','R') NOT NULL default 'L',\n `datafont` varchar(10) NOT NULL default 'Helvetica',\n `datafontsize` int(3) NOT NULL default '10',\n `datafontcolor` varchar(10) NOT NULL default 'black',\n `datafontalign` enum('L','C','R') NOT NULL default 'L',\n `totalsfont` varchar(10) NOT NULL default 'Helvetica',\n `totalsfontsize` int(3) NOT NULL default '10',\n `totalsfontcolor` varchar(11) NOT NULL default '0,0,0',\n `totalsfontalign` enum('L','C','R') NOT NULL default 'L',\n `col1width` int(3) NOT NULL default '25',\n `col2width` int(3) NOT NULL default '25',\n `col3width` int(3) NOT NULL default '25',\n `col4width` int(3) NOT NULL default '25',\n `col5width` int(3) NOT NULL default '25',\n `col6width` int(3) NOT NULL default '25',\n `col7width` int(3) NOT NULL default '25',\n `col8width` int(3) NOT NULL default '25',\n `table1` varchar(25) NOT NULL default '',\n `table2` varchar(25) default NULL,\n `table2criteria` varchar(75) default NULL,\n `table3` varchar(25) default NULL,\n `table3criteria` varchar(75) default NULL,\n `table4` varchar(25) default NULL,\n `table4criteria` varchar(75) default NULL,\n `table5` VARCHAR(25) ,\n `table5criteria` VARCHAR(75) ,\n `table6` VARCHAR(25),\n `table6criteria` VARCHAR(75),\n PRIMARY KEY (`id`),\n KEY `name` (`reportname`,`groupname`)\n)", $db);
CreateTable('reportfields', "CREATE TABLE `reportfields` (\n `id` int(8) NOT NULL auto_increment,\n `reportid` int(5) NOT NULL default '0',\n `entrytype` varchar(15) NOT NULL default '',\n `seqnum` int(3) NOT NULL default '0',\n `fieldname` varchar(35) NOT NULL default '',\n `displaydesc` varchar(25) NOT NULL default '',\n `visible` enum('1','0') NOT NULL default '1',\n `columnbreak` enum('1','0') NOT NULL default '1',\n `params` text,\n PRIMARY KEY (`id`),\n KEY `reportid` (`reportid`)\n)", $db);
CreateTable('reportlinks', "CREATE TABLE IF NOT EXISTS `reportlinks` (\n `table1` varchar(25) NOT NULL default '',\n `table2` varchar(25) NOT NULL default '',\n `equation` varchar(75) NOT NULL default ''\n)", $db);
/* Need to find a way of making this rdbms agnostic
* but for the time being do it as mysql only
*/
if ($DBType == 'mysql' or $DBType == 'mysqli') {
$sql = "INSERT INTO reportlinks SELECT table_name, referenced_table_name, concat(table_name, '.', column_name, '=' , referenced_table_name, '.', referenced_column_name) FROM information_schema.key_column_usage WHERE referenced_table_name is not null and table_schema = '" . $_SESSION['DatabaseName'] . "'";
executeSQL($sql, $db);
}
NewConfigValue('WikiApp', 'Disabled', $db);
NewConfigValue('WikiPath', 'wiki', $db);
NewConfigValue('ProhibitJournalsToControlAccounts', '0', $db);
NewConfigValue('InvoicePortraitFormat', '0', $db);
NewConfigValue('ProhibitPostingsBefore', '2006-01-01', $db);
NewConfigValue('WeightedAverageCosting', '1', $db);
NewConfigValue('AllowOrderLineItemNarrative', '1', $db);
NewConfigValue('vtiger_integration', '0', $db);
NewConfigValue('DB_Maintenance', '-1', $db);
NewConfigValue('HTTPS_Only', '0', $db);
AddIndex(array('serialno'), 'stockserialitems', 'serialno', $db);
AddIndex(array('serialno'), 'stockserialmoves', 'serialno', $db);
InsertRecord('taxcategories', array('taxcatname'), array('Freight'), array('taxcatname'), array('Freight'), $db);
DropIndex('custbranch', 'BranchCode', $db);
AddColumn('stdcostunit', 'grns', 'double', 'NOT NULL', '0', 'supplierid', $db);
DropConstraint('stockcheckfreeze', 'stockcheckfreeze_ibfk_1', $db);
DropPrimaryKey('stockcheckfreeze', array('stockid'), $db);
AddPrimaryKey('stockcheckfreeze', array('stockid', 'loccode'), $db);
AddConstraint('stockcheckfreeze', 'stockcheckfreeze_ibfk_1', 'stockid', 'stockmaster', 'stockid', $db);
UpdateDBNo(basename(__FILE__, '.php'), $db);
/* Create all the tables required for the new petty cash module
*/
CreateTable("pcashdetails", "CREATE TABLE `pcashdetails` (\n `counterindex` int(20) NOT NULL AUTO_INCREMENT,\n `tabcode` varchar(20) NOT NULL,\n `date` date NOT NULL,\n `codeexpense` varchar(20) NOT NULL,\n `amount` double NOT NULL,\n `authorized` date NOT NULL COMMENT 'date cash assigment was revised and authorized by authorizer from tabs table',\n `posted` tinyint(4) NOT NULL COMMENT 'has (or has not) been posted into gltrans',\n `notes` text NOT NULL,\n `receipt` text COMMENT 'filename or path to scanned receipt or code of receipt to find physical receipt if tax guys or auditors show up',\n PRIMARY KEY (`counterindex`)\n)", $db);
CreateTable("pcexpenses", "CREATE TABLE `pcexpenses` (\n `codeexpense` varchar(20) NOT NULL COMMENT 'code for the group',\n `description` varchar(50) NOT NULL COMMENT 'text description, e.g. meals, train tickets, fuel, etc',\n `glaccount` int(11) NOT NULL COMMENT 'GL related account',\n PRIMARY KEY (`codeexpense`),\n KEY (`glaccount`)\n)", $db);
CreateTable("pctabexpenses", "CREATE TABLE `pctabexpenses` (\n `typetabcode` varchar(20) NOT NULL,\n `codeexpense` varchar(20) NOT NULL,\n KEY (`typetabcode`),\n KEY (`codeexpense`)\n)", $db);
CreateTable("pctabs", "CREATE TABLE `pctabs` (\n `tabcode` varchar(20) NOT NULL,\n `usercode` varchar(20) NOT NULL COMMENT 'code of user employee from www_users',\n `typetabcode` varchar(20) NOT NULL,\n `currency` char(3) NOT NULL,\n `tablimit` double NOT NULL,\n `authorizer` varchar(20) NOT NULL COMMENT 'code of user from www_users',\n `glaccountassignment` int(11) NOT NULL COMMENT 'gl account where the money comes from',\n `glaccountpcash` int(11) NOT NULL,\n PRIMARY KEY (`tabcode`),\n KEY (`usercode`),\n KEY (`typetabcode`),\n KEY (`currency`),\n KEY (`authorizer`),\n KEY (`glaccountassignment`)\n)", $db);
CreateTable("pctypetabs", "CREATE TABLE `pctypetabs` (\n `typetabcode` varchar(20) NOT NULL COMMENT 'code for the type of petty cash tab',\n `typetabdescription` varchar(50) NOT NULL COMMENT 'text description, e.g. tab for CEO',\n PRIMARY KEY (`typetabcode`)\n)", $db);
AddConstraint('pcexpenses', 'pcexpenses_ibfk_1', 'glaccount', 'chartmaster', 'accountcode', $db);
AddConstraint('pctabexpenses', 'pctabexpenses_ibfk_1', 'typetabcode', 'pctypetabs', 'typetabcode', $db);
AddConstraint('pctabexpenses', 'pctabexpenses_ibfk_2', 'codeexpense', 'pcexpenses', 'codeexpense', $db);
AddConstraint('pctabs', 'pctabs_ibfk_1', 'usercode', 'www_users', 'userid', $db);
AddConstraint('pctabs', 'pctabs_ibfk_2', 'typetabcode', 'pctypetabs', 'typetabcode', $db);
AddConstraint('pctabs', 'pctabs_ibfk_3', 'currency', 'currencies', 'currabrev', $db);
AddConstraint('pctabs', 'pctabs_ibfk_4', 'authorizer', 'www_users', 'userid', $db);
AddConstraint('pctabs', 'pctabs_ibfk_5', 'glaccountassignment', 'chartmaster', 'accountcode', $db);
DropConstraint('suppliers', 'suppliers_ibfk_4', $db);
UpdateField('suppliers', 'factorcompanyid', 0, '`factorcompanyid`=1', $db);
DeleteRecords('factorcompanies', "coyname='None'", $db);
/* New security token for petty cash usage */
UpdateField('securitytokens', 'tokenname', 'Petty Cash', 'tokenid=6', $db);
/* Add input date to transaction tables so that transactions can be
* reported on by their input date
*/
AddColumn('inputdate', 'supptrans', 'datetime', 'NOT NULL', '0000-00-00', 'duedate', $db);
AddColumn('inputdate', 'debtortrans', 'datetime', 'NOT NULL', '0000-00-00', 'trandate', $db);
/* Change the size of the fieldname field in the report writer as
* the previous size was not big enough to hold all field names
*/
ChangeColumnSize('fieldname', 'reportfields', 'varchar(60)', 'NOT NULL', '', 60, $db);
/* Database changes needed for the picking list functionality
*/
CreateTable('audittrail', "CREATE TABLE `audittrail` (\n\t`transactiondate` datetime NOT NULL default '0000-00-00',\n\t`userid` varchar(20) NOT NULL default '',\n\t`querystring` text,\n\tKEY `UserID` (`userid`),\n CONSTRAINT `audittrail_ibfk_1` FOREIGN KEY (`userid`) REFERENCES `www_users` (`userid`)\n)", $db);
ChangeColumnType('contactemail', 'salesorders', 'VARCHAR(40)', 'NOT NULL', '', $db);
NewConfigValue('MonthsAuditTrail', '1', $db);
CreateTable('factorcompanies', "CREATE TABLE `factorcompanies` (\n `id` int(11) NOT NULL auto_increment,\n `coyname` varchar(50) NOT NULL default '',\n `address1` varchar(40) NOT NULL default '',\n `address2` varchar(40) NOT NULL default '',\n `address3` varchar(40) NOT NULL default '',\n `address4` varchar(40) NOT NULL default '',\n `address5` varchar(20) NOT NULL default '',\n `address6` varchar(15) NOT NULL default '',\n `contact` varchar(25) NOT NULL default '',\n `telephone` varchar(25) NOT NULL default '',\n `fax` varchar(25) NOT NULL default '',\n `email` varchar(55) NOT NULL default '',\n PRIMARY KEY (`id`)\n)", $db);
InsertRecord('factorcompanies', array('id', 'coyname'), array(NULL, 'None'), array('id', 'coyname'), array(NULL, 'None'), $db);
AddColumn('factorcompanyid', 'suppliers', 'INT(11)', 'NOT NULL', '1', 'taxgroupid', $db);
AddConstraint('suppliers', 'suppliers_ibfk_4', 'factorcompanyid', 'factorcompanies', 'id', $db);
AddColumn('perishable', 'stockmaster', 'TINYINT(1)', 'NOT NULL', '0', 'serialised', $db);
AddColumn('appendfile', 'stockmaster', 'VARCHAR(40)', 'NOT NULL', "none", 'serialised', $db);
AddColumn('expirationdate', 'stockserialitems', 'DATETIME', 'NOT NULL', "0000-00-00", 'serialno', $db);
AddColumn('currcode', 'bankaccounts', 'CHAR( 3 )', 'NOT NULL', '', 'accountcode', $db);
AddIndex(array('currcode'), 'bankaccounts', 'currcode', $db);
ChangeColumnType('exrate', 'banktrans', 'DOUBLE', 'NOT NULL', '1.0', $db);
AddColumn('functionalexrate', 'banktrans', 'DOUBLE', 'NOT NULL', " 1", 'exrate', $db);
DropConstraint('worequirements', 'worequirements_ibfk_3', $db);
AddConstraint('worequirements', 'worequirements_ibfk_3', array('wo', 'parentstockid'), 'woitems', array('wo', 'stockid'), $db);
NewConfigValue('ProhibitNegativeStock', '1', $db);
InsertRecord('systypes', array('typeid', 'typename'), array('0', _('Journal - GL')), array('typeid', 'typename'), array('0', _('Journal - GL')), $db);
InsertRecord('systypes', array('typeid', 'typename'), array('1', _('Payment - GL')), array('typeid', 'typename'), array('1', _('Payment - GL')), $db);
InsertRecord('systypes', array('typeid', 'typename'), array('2', _('Receipt - GL')), array('typeid', 'typename'), array('2', _('Receipt - GL')), $db);
InsertRecord('systypes', array('typeid', 'typename'), array('3', _('Standing Journal')), array('typeid', 'typename'), array('3', _('Standing Journal')), $db);
InsertRecord('systypes', array('typeid', 'typename'), array('10', _('Sales Invoice')), array('typeid', 'typename'), array('10', _('Sales Invoice')), $db);
InsertRecord('systypes', array('typeid', 'typename'), array('11', _('Credit Note')), array('typeid', 'typename'), array('11', _('Credit Note')), $db);
InsertRecord('systypes', array('typeid', 'typename'), array('12', _('Receipt')), array('typeid', 'typename'), array('12', _('Receipt')), $db);
InsertRecord('systypes', array('typeid', 'typename'), array('15', _('Journal - Debtors')), array('typeid', 'typename'), array('15', _('Journal - Debtors')), $db);
InsertRecord('systypes', array('typeid', 'typename'), array('16', _('Location Transfer')), array('typeid', 'typename'), array('16', _('Location Transfer')), $db);
InsertRecord('systypes', array('typeid', 'typename'), array('17', _('Stock Adjustment')), array('typeid', 'typename'), array('17', _('Stock Adjustment')), $db);
InsertRecord('systypes', array('typeid', 'typename'), array('18', _('Purchase Order')), array('typeid', 'typename'), array('18', _('Purchase Order')), $db);
InsertRecord('systypes', array('typeid', 'typename'), array('19', _('Picking List')), array('typeid', 'typename'), array('19', _('Picking List')), $db);
InsertRecord('systypes', array('typeid', 'typename'), array('20', _('Purchase Invoice')), array('typeid', 'typename'), array('20', _('Purchase Invoice')), $db);
AddColumn('opencashdrawer', 'paymentmethods', 'TINYINT', 'NOT NULL', '0', 'usepreprintedstationery', $db);
DropConstraint('bankaccounts', 'bankaccounts_ibfk_1', $db);
DropConstraint('banktrans', 'banktrans_ibfk_2', $db);
ChangeColumnType('accountcode', 'bankaccounts', 'varchar(20)', 'NOT NULL', '0', $db);
ChangeColumnType('bankact', 'banktrans', 'varchar(20)', 'NOT NULL', '0', $db);
AddConstraint('banktrans', 'banktrans_ibfk_2', 'bankact', 'bankaccounts', 'accountcode', $db);
DropConstraint('chartdetails', 'chartdetails_ibfk_1', $db);
DropPrimaryKey('chartdetails', array('accountcode', 'period'), $db);
ChangeColumnType('accountcode', 'chartdetails', 'varchar(20)', 'NOT NULL', '0', $db);
AddPrimaryKey('chartdetails', array('accountcode', 'period'), $db);
DropConstraint('gltrans', 'gltrans_ibfk_1', $db);
ChangeColumnType('account', 'gltrans', 'varchar(20)', 'NOT NULL', '0', $db);
AddConstraint('gltrans', 'gltrans_ibfk_1', 'account', 'chartmaster', 'accountcode', $db);
DropConstraint('pcexpenses', 'pcexpenses_ibfk_1', $db);
ChangeColumnType('glaccount', 'pcexpenses', 'varchar(20)', 'NOT NULL', '0', $db);
AddConstraint('pcexpenses', 'pcexpenses_ibfk_1', 'glaccount', 'chartmaster', 'accountcode', $db);
DropConstraint('pctabs', 'pctabs_ibfk_5', $db);
ChangeColumnType('glaccountassignment', 'pctabs', 'varchar(20)', 'NOT NULL', '0', $db);
ChangeColumnType('glaccountpcash', 'pctabs', 'varchar(20)', 'NOT NULL', '0', $db);
AddConstraint('pctabs', 'pctabs_ibfk_5', 'glaccountassignment', 'chartmaster', 'accountcode', $db);
DropConstraint('taxauthorities', 'taxauthorities_ibfk_1', $db);
ChangeColumnType('taxglcode', 'taxauthorities', 'varchar(20)', 'NOT NULL', '0', $db);
AddConstraint('taxauthorities', 'taxauthorities_ibfk_1', 'taxglcode', 'chartmaster', 'accountcode', $db);
DropConstraint('taxauthorities', 'taxauthorities_ibfk_2', $db);
ChangeColumnType('purchtaxglaccount', 'taxauthorities', 'varchar(20)', 'NOT NULL', '0', $db);
AddConstraint('taxauthorities', 'taxauthorities_ibfk_2', 'purchtaxglaccount', 'chartmaster', 'accountcode', $db);
AddConstraint('bankaccounts', 'bankaccounts_ibfk_1', 'accountcode', 'chartmaster', 'accountcode', $db);
AddConstraint('chartdetails', 'chartdetails_ibfk_1', 'accountcode', 'chartmaster', 'accountcode', $db);
NewScript('NoSalesItems.php', '2', $db);
ChangeConfigValue('VersionNumber', '4.08', $db);
UpdateDBNo(basename(__FILE__, '.php'), $db);
<?php
DropConstraint('internalstockcatrole', 'internalstockcatrole_ibfk_3', $db);
DropConstraint('internalstockcatrole', 'internalstockcatrole_ibfk_4', $db);
DropConstraint('internalstockcatrole', 'secroleid', $db);
DropConstraint('stockitemproperties', 'stockitemproperties_ibfk_3', $db);
DropConstraint('stockitemproperties', 'stockitemproperties_ibfk_4', $db);
DropConstraint('stockitemproperties', 'stockitemproperties_ibfk_5', $db);
DropConstraint('stockitemproperties', 'stockitemproperties_ibfk_6', $db);
DropConstraint('stockmovestaxes', 'stockmovestaxes_ibfk_3', $db);
DropConstraint('stockmovestaxes', 'stockmovestaxes_ibfk_4', $db);
DropConstraint('stockrequest', 'stockrequest_ibfk_3', $db);
DropConstraint('stockrequest', 'stockrequest_ibfk_4', $db);
DropConstraint('stockrequestitems', 'dispatchid', $db);
DropConstraint('stockrequestitems', 'stockrequestitems_ibfk_3', $db);
DropConstraint('stockrequestitems', 'stockrequestitems_ibfk_4', $db);
DropConstraint('workorders', 'worksorders_ibfk_1', $db);
UpdateDBNo(basename(__FILE__, '.php'), $db);
