1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
|
# message Creating attributes table...
CREATE TABLE attributes (
hostObject VARCHAR(64),
hostId INT NOT NULL,
name VARCHAR(64),
value MEDIUMTEXT,
PRIMARY KEY( hostObject, hostId, name )
);
# message Creating attributes for archived documents
CREATE TABLE archPosAttribs (
archPosAttribId INT NOT NULL AUTO_INCREMENT,
archDocID INT NOT NULL,
name VARCHAR(64),
value VARCHAR(64),
PRIMARY KEY( archPosAttribId )
);
# message Adding position type and overall price ot archdocpositions
ALTER TABLE archdocpos ADD COLUMN kind VARCHAR(64) AFTER ordNumber;
ALTER TABLE archdocpos ADD COLUMN overallPrice DECIMAL(10,2) AFTER price;
# message Changing old kinds to Normal
UPDATE archdocpos SET kind = "Normal";
# message Calculating archive position price
UPDATE archdocpos SET overallPrice = ROUND( price * amount, 2);
# message Creating Document Type table
CREATE TABLE DocTypes (
docTypeID INT NOT NULL AUTO_INCREMENT,
name VARCHAR(255),
PRIMARY KEY( docTypeID )
);
# message Filling doc type attributes
INSERT INTO DocTypes (name) VALUES ( 'Offer' );
SET @dtId := LAST_INSERT_ID();
INSERT INTO attributes VALUES ('DocType', @dtId, 'AllowDemand', 'true');
INSERT INTO attributes VALUES ('DocType', @dtId, 'AllowAlternative', 'true');
INSERT INTO DocTypes (name) VALUES ( 'Acceptance of Order' );
SET @dtId := LAST_INSERT_ID();
INSERT INTO attributes VALUES ('DocType', @dtId, 'AllowDemand', 'true');
INSERT INTO attributes VALUES ('DocType', @dtId, 'AllowAlternative', 'true');
INSERT INTO DocTypes (name) VALUES ( 'Invoice' );
# message Filling doc type attributes
INSERT INTO DocTypes (name) VALUES ( 'Angebot' );
SET @dtId := LAST_INSERT_ID();
INSERT INTO attributes VALUES ('DocType', @dtId, 'AllowDemand', 'true');
INSERT INTO attributes VALUES ('DocType', @dtId, 'AllowAlternative', 'true');
INSERT INTO DocTypes (name) VALUES ( 'Auftragsbestätigung' );
SET @dtId := LAST_INSERT_ID();
INSERT INTO attributes VALUES ('DocType', @dtId, 'AllowDemand', 'true');
INSERT INTO attributes VALUES ('DocType', @dtId, 'AllowAlternative', 'true');
INSERT INTO DocTypes (name) VALUES ( 'Rechnung' );
# message Drop an unused table archdocStates
DROP TABLE IF EXISTS archdocStates;
|
