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 -- statuses renamed to status (we dont use rails weird plurals)
9 -- created_at/updated_at renamed to itime/mtime,
10 -- varchars retyped to text
11 -- hours (numeric) changed to minutes (integer) since the code already calculates in minutes
12 -- note: flags changing the behaviour of hours are still called so and not minutes
13 -- no refcounts. we use adhoc counts to decide wether delete is possible or not
15 -- nothing relevant to notifications
20 CREATE TABLE project_status (
21 id SERIAL NOT NULL PRIMARY KEY,
23 description TEXT NOT NULL,
24 position INTEGER NOT NULL,
25 itime TIMESTAMP DEFAULT 'now()',
29 ALTER TABLE project ADD COLUMN start_date DATE;
30 ALTER TABLE project ADD COLUMN end_date DATE;
31 ALTER TABLE project ADD COLUMN billable_customer_id INTEGER REFERENCES customer(id);
32 ALTER TABLE project ADD COLUMN budget_cost NUMERIC(15,5) NOT NULL DEFAULT 0;
33 ALTER TABLE project ADD COLUMN order_value NUMERIC(15,5) NOT NULL DEFAULT 0;
34 ALTER TABLE project ADD COLUMN budget_minutes INTEGER NOT NULL DEFAULT 0;
35 ALTER TABLE project ADD COLUMN timeframe BOOLEAN NOT NULL DEFAULT FALSE;
36 ALTER TABLE project ADD COLUMN project_status_id INTEGER REFERENCES project_status(id);
38 ALTER TABLE project_types ADD COLUMN internal BOOLEAN NOT NULL DEFAULT FALSE;
40 CREATE TABLE project_phases (
41 id SERIAL NOT NULL PRIMARY KEY,
42 project_id INTEGER REFERENCES project(id),
46 description TEXT NOT NULL,
47 budget_minutes INTEGER NOT NULL DEFAULT 0,
48 budget_cost NUMERIC (15,5) NOT NULL DEFAULT 0,
49 general_minutes INTEGER NOT NULL DEFAULT 0,
50 general_cost_per_hour NUMERIC (15,5) NOT NULL DEFAULT 0,
51 itime TIMESTAMP DEFAULT 'now()',
55 CREATE TABLE project_roles (
56 id SERIAL NOT NULL PRIMARY KEY,
58 description TEXT NOT NULL,
59 position INTEGER NOT NULL,
60 itime TIMESTAMP DEFAULT 'now()',
64 CREATE TABLE project_participants (
65 id SERIAL NOT NULL PRIMARY KEY,
66 project_id INTEGER NOT NULL REFERENCES project(id),
67 employee_id INTEGER NOT NULL REFERENCES employee(id),
68 project_role_id INTEGER NOT NULL REFERENCES project_roles(id),
69 minutes INTEGER NOT NULL DEFAULT 0,
70 cost_per_hour NUMERIC (15,5),
71 itime TIMESTAMP DEFAULT 'now()',
75 CREATE TABLE project_phase_participants (
76 id SERIAL NOT NULL PRIMARY KEY,
77 project_phase_id INTEGER NOT NULL REFERENCES project_phases(id),
78 employee_id INTEGER NOT NULL REFERENCES employee(id),
79 project_role_id INTEGER NOT NULL REFERENCES project_roles(id),
80 minutes INTEGER NOT NULL DEFAULT 0,
81 cost_per_hour NUMERIC (15,5),
82 itime TIMESTAMP DEFAULT 'now()',
86 CREATE TRIGGER mtime_project_status BEFORE UPDATE ON project_status FOR EACH ROW EXECUTE PROCEDURE set_mtime();
87 CREATE TRIGGER mtime_project_phases BEFORE UPDATE ON project_phases FOR EACH ROW EXECUTE PROCEDURE set_mtime();
88 CREATE TRIGGER mtime_project_roles BEFORE UPDATE ON project_roles FOR EACH ROW EXECUTE PROCEDURE set_mtime();
89 CREATE TRIGGER mtime_project_participants BEFORE UPDATE ON project_participants FOR EACH ROW EXECUTE PROCEDURE set_mtime();
90 CREATE TRIGGER mtime_project_phase_paticipants BEFORE UPDATE ON project_phase_participants FOR EACH ROW EXECUTE PROCEDURE set_mtime();