<?php AddColumn('parentarea', 'areas', 'CHAR( 3 )', 'NOT NULL', '', 'areacode', $db); AddColumn('salesarea', 'salesman', 'CHAR( 3 )', 'NOT NULL', '', 'salesmanname', $db); AddColumn('manager', 'salesman', 'INT( 1 )', 'NOT NULL', '0', 'salesarea', $db); AddConstraint('salesman', 'fk_salesman_1', 'salesarea', 'areas', 'areacode', $db); UpdateDBNo(basename(__FILE__, '.php'), $db);
CreateTable('internalstockcatrole', "CREATE TABLE IF NOT EXISTS `internalstockcatrole` (\n `categoryid` varchar(6) NOT NULL,\n `secroleid` int(11) NOT NULL,\n KEY `internalstockcatrole_ibfk_1` (`categoryid`),\n KEY `internalstockcatrole_ibfk_2` (`secroleid`)\n)", $db); NewScript('InternalStockCategoriesByRole.php', '15', $db); AddColumn('internalrequest', 'locations', 'TINYINT(1)', 'NOT NULL', '1', 'cashsalebranch', $db); ChangeColumnType('shipdate', 'loctransfers', 'DATETIME', 'NOT NULL', "0000-00-00 00:00:00", $db); ChangeColumnType('recdate', 'loctransfers', 'DATETIME', 'NOT NULL', "0000-00-00 00:00:00", $db); NewScript('GLJournalInquiry.php', '15', $db); NewScript('PDFGLJournal.php', '15', $db); AddColumn('department', 'www_users', 'INT(11)', 'NOT NULL', '0', 'pdflanguage', $db); NewConfigValue('WorkingDaysWeek', '5', $db); ChangeColumnType('address6', 'suppliers', 'VARCHAR(40)', 'NOT NULL', '', $db); ChangeColumnType('braddress6', 'custbranch', 'VARCHAR(40)', 'NOT NULL', '', $db); ChangeColumnType('address6', 'debtorsmaster', 'VARCHAR(40)', 'NOT NULL', '', $db); AddConstraint('stockcatproperties', 'categoryid', 'categoryid', 'stockcategory', 'categoryid', $db); AddConstraint('stockitemproperties', 'stockid', 'stockid', 'stockmaster', 'stockid', $db); AddConstraint('stockitemproperties', 'stkcatpropid', 'stkcatpropid', 'stockcatproperties', 'stkcatpropid', $db); AddConstraint('stockmovestaxes', 'stkmoveno', 'stkmoveno', 'stockmoves', 'stkmoveno', $db); AddIndex(array('loccode'), 'stockrequest', 'loccode', $db); AddConstraint('stockrequest', 'loccode', 'loccode', 'locations', 'loccode', $db); AddIndex(array('departmentid'), 'stockrequest', 'departmentid', $db); AddConstraint('stockrequest', 'departmentid', 'departmentid', 'departments', 'departmentid', $db); AddPrimaryKey('stockrequestitems', array('dispatchitemsid', 'dispatchid'), $db); AddIndex(array('dispatchid'), 'stockrequestitems', 'dispatchid', $db); AddIndex(array('stockid'), 'stockrequestitems', 'stockid', $db); AddConstraint('stockrequestitems', 'dispatchid', 'dispatchid', 'stockrequest', 'dispatchid', $db); AddConstraint('stockrequestitems', 'stockrequestitems_ibfk_2', 'stockid', 'stockmaster', 'stockid', $db); AddPrimaryKey('internalstockcatrole', array('categoryid', 'secroleid'), $db); AddConstraint('internalstockcatrole', 'internalstockcatrole_ibfk_1', 'categoryid', 'stockcategory', 'categoryid', $db); AddConstraint('internalstockcatrole', 'secroleid', 'secroleid', 'securityroles', 'secroleid', $db); NewScript('PDFQuotationPortrait.php', '2', $db); ChangeConfigValue('VersionNumber', '4.09', $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);
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);
<?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);
AddColumn('offhold', 'purchorderauth', 'tinyint(1)', 'NOT NULL', '0', 'authlevel', $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
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);
AddColumn('revised', 'purchorders', 'DATE', 'NOT NULL', "0000-00-00", 'version', $db); AddColumn('realorderno', 'purchorders', 'VARCHAR(16)', 'NOT NULL', '', 'revised', $db); AddColumn('deliveryby', 'purchorders', 'VARCHAR(100)', 'NOT NULL', '', 'realorderno', $db); AddColumn('deliverydate', 'purchorders', 'DATE', 'NOT NULL', "0000-00-00", 'deliveryby', $db); AddColumn('status', 'purchorders', 'VARCHAR(12)', 'NOT NULL', '', 'deliverydate', $db); AddColumn('stat_comment', 'purchorders', 'TEXT', 'NOT NULL', '', 'status', $db); AddColumn('itemno', 'purchorderdetails', 'VARCHAR(50)', 'NOT NULL', '', 'itemcode', $db); AddColumn('uom', 'purchorderdetails', 'VARCHAR(50)', 'NOT NULL', '', 'itemno', $db); AddColumn('subtotal_amount', 'purchorderdetails', 'VARCHAR(50)', 'NOT NULL', '', 'uom', $db); AddColumn('package', 'purchorderdetails', 'VARCHAR(50)', 'NOT NULL', '', 'subtotal_amount', $db); AddColumn('pcunit', 'purchorderdetails', 'VARCHAR(50)', 'NOT NULL', '', 'package', $db); AddColumn('nw', 'purchorderdetails', 'VARCHAR(50)', 'NOT NULL', '', 'pcunit', $db); AddColumn('suppliers_partno', 'purchorderdetails', 'VARCHAR(50)', 'NOT NULL', '', 'nw', $db); AddColumn('gw', 'purchorderdetails', 'VARCHAR(50)', 'NOT NULL', '', 'suppliers_partno', $db); AddColumn('cuft', 'purchorderdetails', 'VARCHAR(50)', 'NOT NULL', '', 'gw', $db); AddColumn('total_quantity', 'purchorderdetails', 'VARCHAR(50)', 'NOT NULL', '', 'cuft', $db); AddColumn('total_amount', 'purchorderdetails', 'VARCHAR(50)', 'NOT NULL', '', 'total_quantity', $db); AddColumn('phn', 'suppliers', 'VARCHAR(50)', 'NOT NULL', '', 'taxref', $db); AddColumn('port', 'suppliers', 'VARCHAR(50)', 'NOT NULL', '', 'phn', $db); AddColumn('netweight', 'stockmaster', 'DECIMAL(20,4)', 'NOT NULL', "0.0", 'nextserialno', $db); AddColumn('suppliers_partno', 'purchdata', 'VARCHAR(50)', 'NOT NULL', '', 'effectivefrom', $db); ChangeColumnType('note', 'custnotes', 'TEXT', 'NOT NULL', '', $db); AddColumn('bankaccountcode', 'bankaccounts', 'VARCHAR(50)', 'NOT NULL', '', 'currcode', $db); AddColumn('invoice', 'bankaccounts', 'SMALLINT(2)', 'NOT NULL', "0", 'bankaccountcode', $db); AddColumn('salesman', 'www_users', 'CHAR( 3 )', 'NOT NULL', '', 'customerid', $db); ChangeColumnType('shipvia', 'debtortrans', 'INT(11)', 'NOT NULL', '0', $db); CreateTable('audittrail', "CREATE TABLE IF NOT EXISTS `audittrail` (\n `transactiondate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',\n `userid` varchar(20) NOT NULL DEFAULT '',\n `querystring` text,\n KEY `UserID` (`userid`)\n)", $db); AddConstraint('audittrail', 'audittrail_ibfk_1', 'userid', 'www_users', 'userid', $db); CreateTable('deliverynotes', "CREATE TABLE IF NOT EXISTS `deliverynotes` (\n `deliverynotenumber` int(11) NOT NULL,\n `deliverynotelineno` tinyint(4) NOT NULL,\n `salesorderno` int(11) NOT NULL,\n `salesorderlineno` int(11) NOT NULL,\n `qtydelivered` double NOT NULL DEFAULT '0',\n `printed` tinyint(4) NOT NULL DEFAULT '0',\n `invoiced` tinyint(4) NOT NULL DEFAULT '0',\n `deliverydate` date NOT NULL DEFAULT '0000-00-00',\n PRIMARY KEY (`deliverynotenumber`,`deliverynotelineno`)\n)", $db); AddConstraint('deliverynotes', 'deliverynotes_ibfk_1', array('salesorderno', 'salesorderlineno'), 'salesorderdetails', array('orderno', 'orderlineno'), $db); UpdateDBNo(basename(__FILE__, '.php'), $db);
CreateTable('stockmovestaxes', "CREATE TABLE stockmovestaxes (\n\tstkmoveno int NOT NULL,\n\ttaxauthid tinyint NOT NULL,\n\ttaxontax TINYINT DEFAULT 0 NOT NULL,\n\ttaxcalculationorder TINYINT NOT NULL,\n\ttaxrate double DEFAULT 0 NOT NULL,\n\tPRIMARY KEY (stkmoveno,taxauthid),\n\tKEY (taxauthid),\n\tKEY (taxcalculationorder)\n)", $db); AddConstraint('stockmovestaxes', 'stockmovestaxes_ibfk_1', 'taxauthid', 'taxauthorities', 'taxid', $db); DropColumn('taxrate', 'stockmoves', $db); CreateTable('debtortranstaxes', "CREATE TABLE debtortranstaxes (\n\t`debtortransid` INT NOT NULL ,\n\t`taxauthid` TINYINT NOT NULL ,\n\t`taxamount` DOUBLE NOT NULL,\n\tPRIMARY KEY(debtortransid, taxauthid),\n\tKEY (taxauthid)\n)", $db); AddConstraint('debtortranstaxes', 'debtortranstaxes_ibfk_1', 'taxauthid', 'taxauthorities', 'taxid', $db); AddConstraint('debtortranstaxes', 'debtortranstaxes_ibfk_2', 'debtortransid', 'debtortrans', 'id', $db); DropConstraint('custbranch', 'custbranch_ibfk_5', $db); ChangeColumnName('taxauthority', 'custbranch', 'TINYINT(4)', 'NOT NULL', '1', 'taxgroupid', $db); DropIndex('custbranch', 'area_2', $db); DropIndex('custbranch', 'taxauthority', $db); AddIndex(array('taxgroupid'), 'custbranch', 'taxgroupid', $db); //InsertRecord('taxgroups', array('taxgroupid', 'taxgroupdescription'), array(NULL,'Default tax group'), array('taxgroupid', 'taxgroupdescription'), array(NULL,'Default tax group'), $db); AddConstraint('custbranch', 'custbranch_ibfk_7', 'taxgroupid', 'taxgroups', 'taxgroupid', $db); RenameTable('taxauthlevels', 'taxauthrates', $db); AddConstraint('taxauthrates', 'taxauthrates_ibfk_1', 'dispatchtaxprovince', 'taxprovinces', 'taxprovinceid', $db); ChangeColumnName('taxlevel', 'stockmaster', 'TINYINT(4)', 'NOT NULL', '1', 'taxcatid', $db); AddIndex(array('taxcatid'), 'stockmaster', 'stockmaster_ibix_1', $db); AddConstraint('stockmaster', 'stockmaster_ibfk_2', 'taxcatid', 'taxcategories', 'taxcatid', $db); DropPrimaryKey('salesorderdetails', array('orderno', 'stkcode'), $db); AddColumn('orderlineno', 'salesorderdetails', 'INT(11)', 'NOT NULL', '0', 'orderno', $db); AddPrimaryKey('salesorderdetails', array('orderno', 'orderlineno'), $db); NewConfigValue('FreightTaxCategory', '1', $db); NewConfigValue('SO_AllowSameItemMultipleTimes', '1', $db); CreateTable('supptranstaxes', "CREATE TABLE `supptranstaxes` (\n `supptransid` int(11) NOT NULL default '0',\n `taxauthid` tinyint(4) NOT NULL default '0',\n `taxamount` double NOT NULL default '0',\n PRIMARY KEY (`supptransid`,`taxauthid`),\n KEY `taxauthid` (`taxauthid`),\n CONSTRAINT `supptranstaxes_ibfk_1` FOREIGN KEY (`taxauthid`) REFERENCES `taxauthorities` (`taxid`),\n CONSTRAINT `supptranstaxes_ibfk_2` FOREIGN KEY (`supptransid`) REFERENCES `supptrans` (`id`)\n)", $db); DropConstraint('suppliers', 'suppliers_ibfk_3', $db); ChangeColumnName('taxauthority', 'suppliers', 'TINYINT', 'NOT NULL', '1', 'taxgroupid', $db); DropIndex('suppliers', 'taxauthority', $db); AddIndex(array('taxgroupid'), 'suppliers', 'taxgroupid', $db); AddConstraint('suppliers', 'suppliers_ibfk_3', 'taxgroupid', 'taxgroups', 'taxgroupid', $db); AddColumn('managed', 'locations', 'TINYINT', 'NOT NULL', '0', 'taxprovinceid', $db); UpdateDBNo(basename(__FILE__, '.php'), $db);
<?php CreateTable('custcontacts', "CREATE TABLE `custcontacts` (\n `contid` int(11) NOT NULL auto_increment,\n `debtorno` varchar(10) NOT NULL,\n `contactname` varchar(40) NOT NULL,\n `role` varchar(40) NOT NULL,\n `phoneno` varchar(20) NOT NULL,\n `notes` varchar(255) NOT NULL,\n PRIMARY KEY (`contid`)\n)", $db); AddColumn('taxref', 'suppliers', 'VARCHAR(20)', 'NOT NULL', '', 'factorcompanyid', $db); CreateTable('tags', "CREATE TABLE `tags` (\n`tagref` tinyint(4) NOT NULL auto_increment,\n`tagdescription` varchar(50) NOT NULL,\nPRIMARY KEY (`tagref`)\n)", $db); AddColumn('tag', 'gltrans', 'TINYINT(4)', 'NOT NULL', "0", 'jobref', $db); DropColumn('vtiger_accountid', 'custbranch', $db); DropColumn('vtiger_accountid', 'salesorders', $db); DropColumn('vtiger_productid', 'stockmaster', $db); DeleteConfigValue('vtiger_integration', $db); AddColumn('lat', 'custbranch', 'FLOAT( 10, 6 )', 'NOT NULL', "0.0", 'braddress6', $db); AddColumn('lng', 'custbranch', 'FLOAT( 10, 6 )', 'NOT NULL', "0.0", 'lat', $db); AddColumn('lat', 'suppliers', 'FLOAT( 10, 6 )', 'NOT NULL', "0.0", 'address6', $db); AddColumn('lng', 'suppliers', 'FLOAT( 10, 6 )', 'NOT NULL', "0.0", 'lat', $db); CreateTable('geocode_param', "CREATE TABLE `geocode_param` (\n `geocodeid` varchar(4) NOT NULL default '',\n `geocode_key` varchar(200) NOT NULL default '',\n `center_long` varchar(20) NOT NULL default '',\n `center_lat` varchar(20) NOT NULL default '',\n `map_height` varchar(10) NOT NULL default '',\n `map_width` varchar(10) NOT NULL default '',\n `map_host` varchar(50) NOT NULL default ''\n)", $db); NewConfigValue('geocode_integration', '0', $db); NewConfigValue('DefaultCustomerType', '1', $db); CreateTable('debtortype', "CREATE TABLE `debtortype` (\n`typeid` tinyint(4) NOT NULL auto_increment,\n`typename` varchar(100) NOT NULL,\nPRIMARY KEY (`typeid`)\n)", $db); InsertRecord('debtortype', array('typeid', 'typename'), array(NULL, 'Default'), array('typeid', 'typename'), array(NULL, 'Default'), $db); AddColumn('typeid', 'debtorsmaster', 'TINYINT(4)', 'NOT NULL', "1", 'customerpoline', $db); AddConstraint('debtorsmaster', 'debtorsmaster_ibfk_5', 'typeid', 'debtortype', 'typeid', $db); AddColumn('effectivefrom', 'purchdata', 'DATE', 'NOT NULL', "0000-00-00", 'preferred', $db); CreateTable('debtortypenotes', "CREATE TABLE `debtortypenotes` (\n`noteid` tinyint(4) NOT NULL auto_increment,\n`typeid` tinyint(4) NOT NULL default '0',\n`href` varchar(100) NOT NULL,\n`note` varchar(200) NOT NULL,\n`date` date NOT NULL default '0000-00-00',\n`priority` varchar(20) NOT NULL,\nPRIMARY KEY (`noteid`)\n)", $db); CreateTable('custnotes', "CREATE TABLE `custnotes` (\n`noteid` tinyint(4) NOT NULL auto_increment,\n`debtorno` varchar(10) NOT NULL default '0',\n`href` varchar(100) NOT NULL,\n`note` varchar(200) NOT NULL,\n`date` date NOT NULL default '0000-00-00',\n`priority` varchar(20) NOT NULL,\nPRIMARY KEY (`noteid`)\n)", $db); NewConfigValue('Extended_CustomerInfo', '0', $db); NewConfigValue('Extended_SupplierInfo', '0', $db); ChangeColumnType('area', 'salesglpostings', 'VARCHAR(3)', 'NOT NULL', '', $db); ChangeColumnType('area', 'salesanalysis', 'VARCHAR(3)', 'NOT NULL', '', $db); ChangeColumnType('trandate', 'debtortrans', 'DATE', 'NOT NULL', '0000-00-00', $db); UpdateDBNo(basename(__FILE__, '.php'), $db);