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