1 -- @tag: project_bob_attributes
2 -- @description: Projekte: Zusätzliche Tabellen und Spalten
3 -- @depends: project_customer_type_valid
7 -- no scon/support_contract values here
8 -- no include or expclude flags for workload
9 -- statuses renamed to status (we dont use rails weird plurals)
10 -- created_at/updated_at renamed to itime/mtime,
11 -- varchars retyped to text
12 -- hours (numeric) changed to minutes (integer) since the code already calculates in minutes
13 -- note: flags changing the behaviour of hours are still called so and not minutes
14 -- no refcounts. we use adhoc counts to decide wether delete is possible or not
16 -- nothing relevant to notifications
21 CREATE TABLE project_status (
22 id SERIAL NOT NULL PRIMARY KEY,
24 description TEXT NOT NULL,
25 position INTEGER NOT NULL,
26 itime TIMESTAMP DEFAULT 'now()',
30 ALTER TABLE project ADD COLUMN start_date DATE;
31 ALTER TABLE project ADD COLUMN end_date DATE;
32 ALTER TABLE project ADD COLUMN billable_customer_id INTEGER REFERENCES customer(id);
33 ALTER TABLE project ADD COLUMN budget_cost NUMERIC(15,5) NOT NULL DEFAULT 0;
34 ALTER TABLE project ADD COLUMN order_value NUMERIC(15,5) NOT NULL DEFAULT 0;
35 ALTER TABLE project ADD COLUMN budget_minutes INTEGER NOT NULL DEFAULT 0;
36 ALTER TABLE project ADD COLUMN timeframe BOOLEAN NOT NULL DEFAULT FALSE;
37 ALTER TABLE project ADD COLUMN project_status_id INTEGER REFERENCES project_status(id);
39 ALTER TABLE project_types ADD COLUMN internal BOOLEAN NOT NULL DEFAULT FALSE;
41 CREATE TABLE project_phases (
42 id SERIAL NOT NULL PRIMARY KEY,
43 project_id INTEGER REFERENCES project(id),
47 description TEXT NOT NULL,
48 budget_minutes INTEGER NOT NULL DEFAULT 0,
49 budget_cost NUMERIC (15,5) NOT NULL DEFAULT 0,
50 general_minutes INTEGER NOT NULL DEFAULT 0,
51 general_cost_per_hour NUMERIC (15,5) NOT NULL DEFAULT 0,
52 itime TIMESTAMP DEFAULT 'now()',
56 CREATE TABLE project_roles (
57 id SERIAL NOT NULL PRIMARY KEY,
59 description TEXT NOT NULL,
60 position INTEGER NOT NULL,
61 itime TIMESTAMP DEFAULT 'now()',
65 CREATE TABLE project_participants (
66 id SERIAL NOT NULL PRIMARY KEY,
67 project_id INTEGER NOT NULL REFERENCES project(id),
68 employee_id INTEGER NOT NULL REFERENCES employee(id),
69 project_role_id INTEGER NOT NULL REFERENCES project_roles(id),
70 minutes INTEGER NOT NULL DEFAULT 0,
71 cost_per_hour NUMERIC (15,5),
72 itime TIMESTAMP DEFAULT 'now()',
76 CREATE TABLE project_phase_participants (
77 id SERIAL NOT NULL PRIMARY KEY,
78 project_phase_id INTEGER NOT NULL REFERENCES project_phases(id),
79 employee_id INTEGER NOT NULL REFERENCES employee(id),
80 project_role_id INTEGER NOT NULL REFERENCES project_roles(id),
81 minutes INTEGER NOT NULL DEFAULT 0,
82 cost_per_hour NUMERIC (15,5),
83 itime TIMESTAMP DEFAULT 'now()',
87 CREATE TRIGGER mtime_project_status BEFORE UPDATE ON project_status FOR EACH ROW EXECUTE PROCEDURE set_mtime();
88 CREATE TRIGGER mtime_project_phases BEFORE UPDATE ON project_phases FOR EACH ROW EXECUTE PROCEDURE set_mtime();
89 CREATE TRIGGER mtime_project_roles BEFORE UPDATE ON project_roles FOR EACH ROW EXECUTE PROCEDURE set_mtime();
90 CREATE TRIGGER mtime_project_participants BEFORE UPDATE ON project_participants FOR EACH ROW EXECUTE PROCEDURE set_mtime();
91 CREATE TRIGGER mtime_project_phase_paticipants BEFORE UPDATE ON project_phase_participants FOR EACH ROW EXECUTE PROCEDURE set_mtime();