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
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
|
# DROP DATABASE IF EXISTS kraft;
# CREATE DATABASE kraft DEFAULT CHARACTER SET "utf8";
# use kraft;
CREATE TABLE preisArten (
preisArtID INT NOT NULL,
preisArt VARCHAR(64) NOT NULL,
PRIMARY KEY( preisArtID )
);
CREATE TABLE wordLists(
category VARCHAR(64),
word VARCHAR(255),
PRIMARY KEY( category, word )
);
CREATE TABLE CatalogSet(
catalogSetID INT NOT NULL AUTO_INCREMENT,
name VARCHAR(255),
description VARCHAR(255),
catalogType VARCHAR(64),
sortKey INT NOT NULL,
PRIMARY KEY(catalogSetID)
);
CREATE TABLE CatalogChapters(
chapterID INT NOT NULL AUTO_INCREMENT,
catalogSetID INT NOT NULL,
chapter VARCHAR(255),
sortKey INT NOT NULL,
PRIMARY KEY(chapterID),
INDEX(chapter)
);
CREATE TABLE Catalog (
TemplID INT NOT NULL AUTO_INCREMENT,
chapterID INT NOT NULL default 1,
unitID INT NOT NULL,
Floskel TEXT,
Gewinn DECIMAL(6,2) default 0,
zeitbeitrag TINYINT default 1,
enterDatum DATETIME,
modifyDatum TIMESTAMP(14),
Preisart INT NOT NULL default 1,
EPreis DECIMAL(10,2) default 0,
PRIMARY KEY( TemplID ),
INDEX ( chapterID )
);
UPDATE Catalog SET modifyDatum=enterDatum;
CREATE TABLE CalcTime (
TCalcID INT NOT NULL AUTO_INCREMENT,
TemplID INT NOT NULL,
name VARCHAR(255),
minutes INT default 0,
percent INT default 0,
stdHourSet INT default 0,
allowGlobal INT default 1,
modDate TIMESTAMP(14),
PRIMARY KEY( TCalcID),
INDEX( TemplID )
);
CREATE TABLE CalcFixed(
FCalcID INT NOT NULL AUTO_INCREMENT,
TemplID INT NOT NULL,
name VARCHAR(255),
amount DECIMAL(10,2) default 1.0,
price DECIMAL(10,2),
percent INT default 0,
modDate TIMESTAMP(14),
PRIMARY KEY(FCalcID),
INDEX(TemplID)
);
CREATE TABLE CalcMaterials(
MCalcID INT NOT NULL AUTO_INCREMENT,
TemplID INT NOT NULL,
name VARCHAR(255),
percent INT default 0,
modDate TIMESTAMP(14),
PRIMARY KEY(MCalcID),
INDEX(TemplID)
);
CREATE TABLE CalcMaterialDetails(
MCalcDetailID INT NOT NULL AUTO_INCREMENT,
CalcID INT NOT NULL,
materialID INT NOT NULL,
amount DECIMAL(10,2),
PRIMARY KEY(MCalcDetailID),
INDEX(CalcID)
);
CREATE TABLE units(
unitID INT NOT NULL,
unitShort VARCHAR(255),
unitLong VARCHAR(255),
unitPluShort VARCHAR(255),
unitPluLong VARCHAR(255),
PRIMARY KEY(unitID),
INDEX(unitShort)
);
CREATE TABLE stockMaterial (
matID INT NOT NULL AUTO_INCREMENT,
chapterID INT NOT NULL default 1,
material mediumtext,
unitID INT NOT NULL,
perPack DECIMAL(10,2),
priceIn DECIMAL(10,2),
priceOut DECIMAL(10,2),
enterDate DATETIME,
modifyDate TIMESTAMP(14),
PRIMARY KEY(matID),
INDEX(chapterID)
);
CREATE TABLE stdSaetze(
stdSaetzeID INT NOT NULL AUTO_INCREMENT,
name VARCHAR(255),
price DECIMAL(10,2),
sortKey int,
PRIMARY KEY(stdSaetzeID)
);
CREATE TABLE document(
docID INT NOT NULL AUTO_INCREMENT,
ident VARCHAR(32),
docType VARCHAR(255),
clientID VARCHAR(32),
clientAddress TEXT,
salut VARCHAR(255),
goodbye VARCHAR(128),
lastModified TIMESTAMP,
date DATE,
pretext TEXT,
posttext TEXT,
PRIMARY KEY( docID ),
INDEX(ident),
INDEX(clientID)
);
CREATE TABLE docposition(
positionID INT NOT NULL AUTO_INCREMENT,
docID INT NOT NULL,
ordNumber INT NOT NULL,
text TEXT,
amount DECIMAL(10,2),
unit INT,
price DECIMAL(10,2),
PRIMARY KEY( positionID ),
INDEX(docID),
UNIQUE( docID, ordNumber)
);
CREATE TABLE archdocStates(
stateID INT NOT NULL AUTO_INCREMENT,
state VARCHAR(32),
PRIMARY KEY( stateID )
);
CREATE TABLE archdoc(
archDocID INT NOT NULL AUTO_INCREMENT,
ident VARCHAR(32),
docType VARCHAR(255),
docDescription TEXT,
clientAddress TEXT,
salut VARCHAR(255),
goodbye VARCHAR(128),
printDate TIMESTAMP,
date DATE,
pretext TEXT,
posttext TEXT,
state int,
PRIMARY KEY( archDocID ),
INDEX(ident)
);
CREATE TABLE archdocpos(
archPosID INT NOT NULL AUTO_INCREMENT,
archDocID INT NOT NULL,
ordNumber INT NOT NULL,
text TEXT,
amount DECIMAL(10,2),
unit VARCHAR(64),
price DECIMAL(10,2),
vat DECIMAL(4,1),
PRIMARY KEY( archPosID ),
INDEX(archDocID),
UNIQUE( archDocID, ordNumber)
);
CREATE TABLE kraftsystem(
dbschemaversion INT NOT NULL,
updateUser VARCHAR(256)
);
INSERT INTO kraftsystem ( dbschemaversion ) VALUES ( 1 );
# message Database created.
|
