1-- message: Creating document position calulation tables ;
2
3CREATE TABLE DocCalcTime (
4	TCalcID       INTEGER PRIMARY KEY ASC autoincrement,
5	TemplID       INT NOT NULL,
6
7	name          VARCHAR(255),
8	minutes	      INT default 0,
9	percent       INT default 0,
10	stdHourSet    INT default 0,
11	allowGlobal   INT default 1,
12
13	modDate	      TIMESTAMP(14)
14);
15CREATE INDEX calcTimeTemplIndx_2 ON DocCalcTime( TemplID );
16CREATE TRIGGER update_docCalcTime_modDate AFTER UPDATE ON DocCalcTime
17BEGIN
18  UPDATE DocCalcTime SET modDate = DATETIME('NOW')  WHERE TCalcID = new.TCalcID;
19END;
20
21
22CREATE TABLE DocCalcFixed(
23	FCalcID       INTEGER PRIMARY KEY ASC autoincrement,
24	TemplID       INT NOT NULL,
25
26	name          VARCHAR(255),
27	amount        DECIMAL(10,2) default 1.0,
28	price	      DECIMAL(10,2),
29	percent       INT default 0,
30	modDate	      TIMESTAMP(14)
31);
32CREATE INDEX CalcFixedTemplIndx_2 ON DocCalcFixed( TemplID );
33CREATE TRIGGER update_docCalcFixed_modDate AFTER UPDATE ON DocCalcFixed
34BEGIN
35  UPDATE DocCalcFixed SET modDate = DATETIME('NOW')  WHERE FCalcID = new.FCalcID;
36END;
37
38
39CREATE TABLE DocCalcMaterials(
40	MCalcID       INTEGER PRIMARY KEY ASC autoincrement,
41	TemplID       INT NOT NULL,
42	name          VARCHAR(255),
43	percent       INT default 0,
44	modDate	      TIMESTAMP(14)
45);
46CREATE INDEX CalcMaterialTemplIndx_2 ON DocCalcMaterials( TemplID );
47CREATE TRIGGER update_docCalcMaterials_modDate AFTER UPDATE ON DocCalcMaterials
48BEGIN
49  UPDATE DocCalcMaterials SET modDate = DATETIME('NOW')  WHERE MCalcID = new.MCalcID;
50END;
51
52CREATE TABLE DocCalcMaterialDetails(
53	MCalcDetailID INTEGER PRIMARY KEY ASC autoincrement,
54	CalcID        INT NOT NULL,
55
56	materialID    INT NOT NULL,
57	amount 	      DECIMAL(10,2)
58);
59CREATE INDEX CalcMaterialDetailsCalcIDIndx_2 ON DocCalcMaterialDetails( CalcID );
60
61