1 -- @tag: project_bob_attributes
2 -- @description: Projekte: Zusätzliche Tabellen und Spalten
3 -- @depends: project_customer_type_valid
6 -- no scon/support_contract values here
7 -- no include or expclude flags for workload
8 -- created_at/updated_at renamed to itime/mtime,
9 -- varchars retyped to text
10 -- hours (numeric) changed to minutes (integer) since the code already calculates in minutes
11 -- note: flags changing the behaviour of hours are still called so and not minutes
12 -- no refcounts. we use adhoc counts to decide whether delete is possible or not
14 -- nothing relevant to notifications
19 CREATE TABLE project_status (
20 id SERIAL NOT NULL PRIMARY KEY,
22 description TEXT NOT NULL,
23 position INTEGER NOT NULL,
24 itime TIMESTAMP DEFAULT now(),
28 ALTER TABLE project ADD COLUMN start_date DATE;
29 ALTER TABLE project ADD COLUMN end_date DATE;
30 ALTER TABLE project ADD COLUMN billable_customer_id INTEGER REFERENCES customer(id);
31 ALTER TABLE project ADD COLUMN budget_cost NUMERIC(15,5) NOT NULL DEFAULT 0;
32 ALTER TABLE project ADD COLUMN order_value NUMERIC(15,5) NOT NULL DEFAULT 0;
33 ALTER TABLE project ADD COLUMN budget_minutes INTEGER NOT NULL DEFAULT 0;
34 ALTER TABLE project ADD COLUMN timeframe BOOLEAN NOT NULL DEFAULT FALSE;
35 ALTER TABLE project ADD COLUMN project_status_id INTEGER REFERENCES project_status(id);
37 ALTER TABLE project_types ADD COLUMN internal BOOLEAN NOT NULL DEFAULT FALSE;
39 CREATE TABLE project_phases (
40 id SERIAL NOT NULL PRIMARY KEY,
41 project_id INTEGER REFERENCES project(id),
45 description TEXT NOT NULL,
46 budget_minutes INTEGER NOT NULL DEFAULT 0,
47 budget_cost NUMERIC (15,5) NOT NULL DEFAULT 0,
48 general_minutes INTEGER NOT NULL DEFAULT 0,
49 general_cost_per_hour NUMERIC (15,5) NOT NULL DEFAULT 0,
50 itime TIMESTAMP DEFAULT now(),
54 CREATE TABLE project_roles (
55 id SERIAL NOT NULL PRIMARY KEY,
57 description TEXT NOT NULL,
58 position INTEGER NOT NULL,
59 itime TIMESTAMP DEFAULT now(),
63 CREATE TABLE project_participants (
64 id SERIAL NOT NULL PRIMARY KEY,
65 project_id INTEGER NOT NULL REFERENCES project(id),
66 employee_id INTEGER NOT NULL REFERENCES employee(id),
67 project_role_id INTEGER NOT NULL REFERENCES project_roles(id),
68 minutes INTEGER NOT NULL DEFAULT 0,
69 cost_per_hour NUMERIC (15,5),
70 itime TIMESTAMP DEFAULT now(),
74 CREATE TABLE project_phase_participants (
75 id SERIAL NOT NULL PRIMARY KEY,
76 project_phase_id INTEGER NOT NULL REFERENCES project_phases(id),
77 employee_id INTEGER NOT NULL REFERENCES employee(id),
78 project_role_id INTEGER NOT NULL REFERENCES project_roles(id),
79 minutes INTEGER NOT NULL DEFAULT 0,
80 cost_per_hour NUMERIC (15,5),
81 itime TIMESTAMP DEFAULT now(),
85 CREATE TRIGGER mtime_project_status BEFORE UPDATE ON project_status FOR EACH ROW EXECUTE PROCEDURE set_mtime();
86 CREATE TRIGGER mtime_project_phases BEFORE UPDATE ON project_phases FOR EACH ROW EXECUTE PROCEDURE set_mtime();
87 CREATE TRIGGER mtime_project_roles BEFORE UPDATE ON project_roles FOR EACH ROW EXECUTE PROCEDURE set_mtime();
88 CREATE TRIGGER mtime_project_participants BEFORE UPDATE ON project_participants FOR EACH ROW EXECUTE PROCEDURE set_mtime();
89 CREATE TRIGGER mtime_project_phase_paticipants BEFORE UPDATE ON project_phase_participants FOR EACH ROW EXECUTE PROCEDURE set_mtime();