hQM8Pn3y

1-------------
2	
3create table reservation (
4	id SERIAL PRIMARY key,
5	parent_id integer, 
6	parent_name character(35),
7	created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
8);
9create table reservation_servplan (
10	id SERIAL PRIMARY key,
11	reservation_id integer REFERENCES reservation(id),
12	chid_id integer not null,
13	service_plan_id integer not null,
14	servplan_quantity integer ,
15	state integer not null default 1,
16	created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
17);
18CREATE TABLE IF NOT EXISTS invoice_line_service (
19   reservation_servplan_id serial PRIMARY KEY,
20   invoice_number SERIAL not null,
21   service_plan_id integer not null,
22   servplan_quantity integer,
23   servplan_price float NOT NULL, 
24   state integer not null default 1,
25   created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
26   FOREIGN KEY (reservation_servplan_id) REFERENCES reservation_servplan(id),
27   FOREIGN KEY (invoice_number) REFERENCES invoice(invoice_number)
28);
29create table invoice (
30	invoice_number SERIAL PRIMARY key,
31	reservation_id integer REFERENCES reservation(id),
32	invoice_date date,
33	details text,
34	state integer not null default 1,
35	created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
36);
37-------------------
38
39INSERT INTO public.reservation(parent_id, parent_name, created_at)VALUES(1, 'Smith', CURRENT_TIMESTAMP);
40
41INSERT INTO public.reservation_servplan(reservation_id, chid_id, service_plan_id, servplan_quantity, state, created_at) VALUES(1, 1, 9, 1, 1, CURRENT_TIMESTAMP);
42INSERT INTO public.reservation_servplan(reservation_id, chid_id, service_plan_id, servplan_quantity, state, created_at) VALUES(1, 2, 9, 1, 1, CURRENT_TIMESTAMP);
43-------------------
44
45
46CREATE OR REPLACE FUNCTION create_invoice() RETURNS TRIGGER AS $create_invoice$
47	DECLARE 
48		invoice_id INT;
49		re_id INT;
50		temprow record;
51    BEGIN
52        IF (TG_OP = 'DELETE') THEN
53            INSERT INTO courses_elastic
54                SELECT 'D', now(), user, o.* FROM old_table o;
55        ELSIF (TG_OP = 'UPDATE') then
56        	INSERT INTO invoice(reservation_id, invoice_date , state, created_at) 
57           	SELECT new_table.id, now(), 1, now() FROM new_table 
58           	returning invoice_number, reservation_id into invoice_id, re_id;
59   		
60			FOR temprow IN
61				SELECT * FROM reservation_servplan where reservation_servplan.reservation_id = re_id
62			LOOP
63				INSERT INTO invoice_line_service(reservation_servplan_id, invoice_number, service_plan_id, servplan_quantity, servplan_price, state, created_at)
64				VALUES (temprow.id, invoice_id, temprow.service_plan_id, temprow.servplan_quantity, 10, temprow.state, now());
65			END LOOP;      
66        END IF;
67        RETURN NULL;
68    END;
69$create_invoice$ LANGUAGE plpgsql;
70
71---------------------
72
73CREATE TRIGGER process_reservation_servplan_audit_update
74    AFTER UPDATE ON reservation
75    REFERENCING NEW TABLE AS new_table
76    FOR EACH ROW 
77    EXECUTE FUNCTION create_invoice();
78
79
80---------------
81drop trigger process_reservation_servplan_audit_update on reservation;
82drop function if exists create_invoice cascade;
83
84UPDATE public.reservation_servplan SET state=2 WHERE id=2;
85
86truncate table invoice cascade;
87truncate table invoice_line_service cascade;