1ALTER TABLE `banktrans` ADD `chequeno` VARCHAR(16) NOT NULL DEFAULT '' AFTER `currcode`; 2ALTER TABLE `custbranch` CHANGE `lat` `lat` FLOAT(12,8) NOT NULL DEFAULT '0.00000000'; 3ALTER TABLE `custbranch` CHANGE `lng` `lng` FLOAT(12,8) NOT NULL DEFAULT '0.00000000'; 4ALTER TABLE `supptrans` ADD `chequeno` VARCHAR(16) NOT NULL DEFAULT '' AFTER `hold`; 5ALTER TABLE `supptrans` ADD `void` TINYINT(1) NOT NULL DEFAULT 0 AFTER `chequeno`; 6ALTER table `supptrans` DROP KEY `TypeTransNo`; 7ALTER table `supptrans` ADD KEY `TypeTransNo`(`transno`,`type`); 8 9ALTER TABLE `pcexpenses` ADD COLUMN `taxcatid` TINYINT(4) NOT NULL DEFAULT 1 AFTER `tag`; 10ALTER TABLE `pctabs` ADD COLUMN `defaulttag` TINYINT(4) NOT NULL DEFAULT 0 AFTER `glaccountpcash`; 11ALTER TABLE `pctabs` ADD COLUMN `taxgroupid` TINYINT(4) NOT NULL DEFAULT 1 AFTER `defaulttag`; 12ALTER TABLE `pctabs` ADD COLUMN `authorizerexpenses` VARCHAR(20) NOT NULL AFTER `authorizer`; 13UPDATE `pctabs` SET authorizerexpenses=authorizer; 14ALTER TABLE `pcashdetails` ADD COLUMN `tag` INT(11) NOT NULL DEFAULT 0 AFTER `tabcode`; 15INSERT INTO `scripts` (`script`, `pagesecurity`, `description`) VALUES ('PcAuthorizeCash.php', '6', 'Authorisation of assigned cash'); 16INSERT IGNORE INTO `scripts` (`script`, `pagesecurity`, `description`) VALUES ('Z_RemovePurchaseBackOrders.php', '1', 'Removes all purchase order back orders'); 17CREATE TABLE `pcashdetailtaxes` ( 18 `counterindex` INT(20) NOT NULL AUTO_INCREMENT, 19 `pccashdetail` INT(20) NOT NULL DEFAULT 0, 20 `calculationorder` TINYINT(4) NOT NULL DEFAULT 0, 21 `description` VARCHAR(40) NOT NULL DEFAULT '', 22 `taxauthid` TINYINT(4) NOT NULL DEFAULT '0', 23 `purchtaxglaccount` VARCHAR(20) NOT NULL DEFAULT '', 24 `taxontax` TINYINT(4) NOT NULL DEFAULT 0, 25 `taxrate` DOUBLE NOT NULL DEFAULT 0.0, 26 `amount` DOUBLE NOT NULL DEFAULT 0.0, 27 PRIMARY KEY(counterindex) 28) Engine=InnoDB DEFAULT CHARSET=utf8; 29 30ALTER TABLE pcashdetails MODIFY receipt text COMMENT 'Column redundant. Replaced by receipt file upload. Nov 2017.'; 31 32ALTER TABLE `stockserialitems` ADD `createdate` timestamp NULL DEFAULT CURRENT_TIMESTAMP, ADD INDEX (`createdate`); 33UPDATE stockserialitems SET createdate = NULL; 34 35UPDATE stockserialitems as stockserialitems SET createdate = 36(SELECT trandate FROM (select trandate, stockserialitems.serialno, stockserialitems.stockid FROM stockserialitems 37LEFT JOIN stockserialmoves ON stockserialitems.serialno=stockserialmoves.serialno 38LEFT JOIN stockmoves ON stockserialmoves.stockmoveno=stockmoves.stkmoveno 39GROUP BY stockserialitems.stockid, stockserialitems.serialno 40ORDER BY trandate) AS ssi 41WHERE ssi.serialno=stockserialitems.serialno 42AND ssi.stockid=stockserialitems.stockid); 43 44ALTER TABLE `salesorders` ADD `internalcomment` BLOB NULL DEFAULT NULL; 45 46-- BEGIN: INSERT INTO section: 47INSERT INTO `config` (`confname`, `confvalue`) VALUES ('MaxSerialItemsIssued','50'); 48INSERT INTO `scripts` (`script`, `pagesecurity`, `description`) VALUES ('BankAccountBalances.php', '1', 'Shows bank accounts authorised for with balances'); 49INSERT INTO `scripts` (`script`, `pagesecurity`, `description`) VALUES ('GeneratePickingList.php', '11', 'Generate a picking list'); 50INSERT INTO `scripts` (`script`, `pagesecurity`, `description`) VALUES ('GLAccountGraph.php', '8', 'Shows a graph of GL account transactions'); 51INSERT INTO `scripts` (`script`, `pagesecurity`, `description`) VALUES ('PDFAck.php', '15', 'Print an acknowledgement'); 52INSERT INTO `scripts` (`script`, `pagesecurity`, `description`) VALUES ('PDFShipLabel.php', '15', 'Print a ship label'); 53INSERT INTO `scripts` (`script`, `pagesecurity`, `description`) VALUES ('PickingListsControlled.php', '11', 'Picking List Maintenance - Controlled'); 54INSERT INTO `scripts` (`script`, `pagesecurity`, `description`) VALUES ('PickingLists.php', '11', 'Picking List Maintenance'); 55INSERT INTO `scripts` (`script`, `pagesecurity`, `description`) VALUES ('SelectPickingLists.php', '11', 'Select a picking list'); 56INSERT INTO `scripts` (`script`, `pagesecurity`, `description`) VALUES ('Z_ChangeSalesmanCode.php', '15', 'Utility to change a salesman code'); 57INSERT INTO `scripts` (`script`, `pagesecurity`, `description`) VALUES ('Z_Fix1cAllocations.php', '9', ''); 58-- END: INSERT INTO section. 59 60CREATE TABLE IF NOT EXISTS pickreq ( 61 `prid` int not null auto_increment, 62 `initiator` varchar(20) not null default '', 63 `shippedby` varchar(20) not null default '', 64 `initdate` date not null default '0000-00-00', 65 `requestdate` date not null default '0000-00-00', 66 `shipdate` date not null default '0000-00-00', 67 `status` varchar(12) not null default '', 68 `comments` text default null, 69 `closed` tinyint not null default '0', 70 `loccode` varchar(5) not null default '', 71 `orderno` int not null default '1', 72 `consignment` varchar(15) NOT NULL DEFAULT '', 73 `packages` int(11) NOT NULL DEFAULT '1' COMMENT 'number of cartons', 74 PRIMARY KEY (`prid`), 75 key (`orderno`), 76 key (`requestdate`), 77 key (`shipdate`), 78 key (`status`), 79 key (`closed`), 80 key (`loccode`), 81 CONSTRAINT FOREIGN KEY(`loccode`) REFERENCES `locations`(`loccode`), 82 constraint foreign key (`orderno`) REFERENCES salesorders(`orderno`) 83) Engine=InnoDB DEFAULT CHARSET=utf8; 84 85CREATE TABLE IF NOT EXISTS pickreqdetails ( 86 `detailno` int not null auto_increment, 87 `prid` int not null default '1', 88 `orderlineno` int not null default '0', 89 `stockid` varchar(20) not null default '', 90 `qtyexpected` double not null default '0', 91 `qtypicked` double not null default '0', 92 `invoicedqty` double not null default '0', 93 `shipqty` double not null default '0', 94 PRIMARY KEY (`detailno`), 95 key (`prid`), 96 key (`stockid`), 97 constraint foreign key (`stockid`) REFERENCES stockmaster(`stockid`), 98 constraint foreign key (`prid`) REFERENCES pickreq(`prid`) 99) Engine=InnoDB DEFAULT CHARSET=utf8; 100 101CREATE TABLE IF NOT EXISTS pickserialdetails ( 102 `serialmoveid` int not null auto_increment, 103 `detailno` int not null default '1', 104 `stockid` varchar(20) not null default '', 105 `serialno` varchar(30) not null default '', 106 `moveqty` double not null default '0', 107 PRIMARY KEY (`serialmoveid`), 108 key (`detailno`), 109 key (`stockid`,`serialno`), 110 key (`serialno`), 111 CONSTRAINT FOREIGN KEY (`detailno`) REFERENCES pickreqdetails (`detailno`), 112 CONSTRAINT FOREIGN KEY (`stockid`,`serialno`) REFERENCES `stockserialitems`(`stockid`,`serialno`) 113) Engine=InnoDB DEFAULT CHARSET=utf8; 114 115-- TABLE pickinglists (pickinglistno, orderno, pickinglistdate, dateprinted, deliverynotedate) 116INSERT INTO pickreq (prid, initdate, requestdate, shipdate, orderno, closed, loccode) 117 SELECT pickinglists.pickinglistno, dateprinted, pickinglistdate, deliverynotedate, pickinglists.orderno, IF(qtyexpected = qtypicked, 1, 0), fromstkloc 118 FROM pickinglists 119 JOIN pickinglistdetails ON pickinglists.pickinglistno = pickinglistdetails.pickinglistno 120 JOIN salesorders ON pickinglists.orderno = salesorders.orderno; 121 122INSERT INTO pickreqdetails (prid, orderlineno, stockid, qtyexpected, qtypicked, invoicedqty, shipqty) 123 SELECT pickinglistdetails.pickinglistno, pickinglistdetails.orderlineno, stkcode, qtyexpected, qtypicked, qtypicked, qtypicked 124 FROM pickinglistdetails 125 JOIN pickinglists ON pickinglistdetails.pickinglistno = pickinglists.pickinglistno 126 JOIN salesorderdetails ON salesorderdetails.orderno = pickinglists.orderno; 127 128CREATE TABLE `pcreceipts` ( 129 `counterindex` INT(20) NOT NULL AUTO_INCREMENT, 130 `pccashdetail` INT(20) NOT NULL DEFAULT 0 COMMENT 'Expenses record identity', 131 `hashfile` VARCHAR(32) NOT NULL DEFAULT '' COMMENT 'MD5 hash of uploaded receipt file', 132 `type` varchar(80) NOT NULL DEFAULT '' COMMENT 'Mime type of uploaded receipt file', 133 `extension` varchar(4) NOT NULL DEFAULT '' COMMENT 'File extension of uploaded receipt', 134 `size` int(20) NOT NULL DEFAULT 0 COMMENT 'File size of uploaded receipt', 135 PRIMARY KEY (`counterindex`), 136 CONSTRAINT `pcreceipts_ibfk_1` FOREIGN KEY (`pccashdetail`) REFERENCES `pcashdetails` (`counterindex`) 137) ENGINE=InnoDB DEFAULT CHARSET=utf8; 138 139ALTER TABLE pcashdetails ADD COLUMN purpose text NULL AFTER posted; 140 141 142UPDATE config SET confvalue='4.15' WHERE confname='VersionNumber'; 143 144 145