· 7 years ago · Jan 16, 2019, 10:20 AM
1-- create table if not exists logger
2-- (
3-- id serial primary key,
4-- operation text,
5-- on_table text,
6-- record_id integer,
7-- old_data text,
8-- new_data text
9-- );
10
11-- create table if not exists customer
12-- (
13-- id serial primary key,
14-- first_name text,
15-- last_name text,
16-- address text,
17-- phone_number text unique
18-- );
19
20-- create table if not exists product
21-- (
22-- id serial primary key,
23-- nane text,
24-- available_quantity text
25-- );
26
27
28-- create table if not exists orders
29-- (
30-- id serial primary key,
31-- customer_id integer,
32-- additional_info text,
33-- status text,
34-- Foreign key(customer_id) references customer(id)
35-- );
36
37-- create table if not exists orders_extended
38-- (
39-- id serial primary key,
40-- orders_id integer,
41-- product_id integer,
42-- quantity integer,
43-- Foreign key(orders_id) references orders(id),
44-- Foreign key(product_id) references product(id)
45-- );
46
47
48-- INSERT into customer(first_name, last_name, address, phone_number) values('Mateusz', 'Roczek', 'Limanowskiego 12, Lodz, 97-410 Lodz', '111222333');
49-- INSERT into customer(first_name, last_name, address, phone_number) values('Mateusz', 'Motylinski', 'Zgierska 12, Zgierz, 97-215 Zgierz', '222333111');
50-- INSERT into customer(first_name, last_name, address, phone_number) values('Dominik', 'Mjerzejewski', 'Pogonowskiego 15, Warszawa, 23-120 Warszawa', '444222333');
51-- INSERT into customer(first_name, last_name, address, phone_number) values('Radoslaw', 'Skorzewski', '3-go Maja 23, Lutomiersk, 95-083 Lutomiersk', '333333111');
52-- INSERT into customer(first_name, last_name, address, phone_number) values('Mateusz', 'Roczek', 'Limanowskiego 24/2, Krakow, 12-534 Krakow', '666222333');
53
54
55-- INSERT into product(name, available_quantity) values('Pepsi 2L', 20);
56-- INSERT into product(name, available_quantity) values('Zapiekanki', 14);
57-- INSERT into product(name, available_quantity) values('Bulki do HotDogow', 50);
58-- INSERT into product(name, available_quantity) values('Parowki', 10);
59-- INSERT into product(name, available_quantity) values('Pizza 4 Sery', 5);
60-- INSERT into product(name, available_quantity) values('Pizza z Kurczakiem', 2);
61-- INSERT into product(name, available_quantity) values('Jablko', 0);
62
63-- INSERT into orders(customer_id, additional_info, status) values(1, 'MA BYC NA JUTRO', 'Przyjete');
64
65
66
67
68-- ALTER TABLE orders ALTER COLUMN customer_id SET NOT NULL;
69-- ALTER TABLE orders_extended ALTER COLUMN quantity SET NOT NULL;
70
71--ALTER TABLE product ALTER COLUMN quantity drop default;
72
73
74-- select * from product;
75
76/*
77CREATE OR REPLACE FUNCTION is_product_available()
78RETURNS TRIGGER AS $$
79BEGIN
80 IF ((Select is_avaiable from product where id = new.product_id) = 'true') THEN
81 RAISE NOTICE 'Product: %, is avaiable.', (Select name from product where id = new.product_id);
82 RETURN NEW;
83 END IF;
84 RAISE NOTICE 'Product: %, is not avaiable.', (Select name from product where id = new.product_id);
85 RETURN NULL;
86END;
87$$ LANGUAGE plpgsql;
88
89
90CREATE TRIGGER product_availablity
91BEFORE
92--(BEFORE)
93--(INSTEAD OF)
94INSERT OR UPDATE
95ON orders_extended
96FOR EACH ROW
97EXECUTE PROCEDURE is_product_available();
98*/
99
100/*
101CREATE OR REPLACE FUNCTION is_product_quantity()
102RETURNS TRIGGER AS $$
103BEGIN
104 IF ((Select quantity from product where id = new.product_id) > new.quantity or (Select quantity from product where id = new.product_id) = new.quantity) THEN
105 RAISE NOTICE 'The given quantity of product: %, is avaiable.', (Select name from product where id = new.product_id);
106 RETURN NEW;
107 END IF;
108 RAISE EXCEPTION 'Product: %, is not avaiable with that amount of quantity.', (Select name from product where id = new.product_id) || ' Precced to abort.';
109 RETURN NULL;
110END;
111$$ LANGUAGE plpgsql;
112
113
114CREATE TRIGGER product_quantity
115BEFORE
116--(BEFORE)
117--(INSTEAD OF)
118INSERT OR UPDATE
119ON orders_extended
120FOR EACH ROW
121EXECUTE PROCEDURE is_product_quantity();
122*/
123
124/*
125CREATE OR REPLACE FUNCTION create_order(IN a_client_id int, a_additional_info text)
126RETURNS TEXT
127AS $$
128BEGIN
129 IF ((Select id from customer where id = a_client_id) IS NOT NULL) THEN
130 INSERT INTO orders(customer_id, additional_info) values(a_client_id, a_additional_info);
131 RETURN 'Order has been successfully created!';
132 ELSE
133 RETURN 'Order creation has failed, client with provided id does not exists!';
134 END IF;
135END;
136$$ LANGUAGE plpgsql;
137*/
138
139/*
140CREATE OR REPLACE FUNCTION add_to_order(IN a_order_id int, a_product_id int, a_quantity int)
141RETURNS TEXT
142AS $$
143BEGIN
144 IF ((Select id from orders where id = a_order_id) IS NOT NULL AND
145 (Select id from product where id = a_product_id) IS NOT NULL) THEN
146 INSERT INTO orders_extended(orders_id, product_id, quantity) values(a_order_id, a_product_id, a_quantity);
147 UPDATE product SET quantity = quantity - a_quantity WHERE id = a_product_id;
148 RETURN 'Product has been added to order!';
149 ELSE
150 RETURN 'Order creation has failed, client with provided id or product does not exists!';
151 END IF;
152END;
153$$ LANGUAGE plpgsql;
154*/
155
156/*
157CREATE OR REPLACE FUNCTION edit_order(IN a_id int, a_product_id int, a_quantity int)
158RETURNS TEXT
159AS $$
160DECLARE
161 v_old_quantity orders_extended.quantity%TYPE;
162BEGIN
163 IF ((Select id from orders_extended where id = a_id) IS NOT NULL AND
164 a_quantity > 0) THEN
165
166 v_old_quantity := (SELECT quantity FROM orders_extended WHERE id = a_id);
167
168 UPDATE orders_extended SET product_id = a_product_id,
169 quantity = a_quantity WHERE id = a_id;
170 UPDATE product SET quantity = quantity - ABS(a_quantity - v_old_quantity) WHERE id = a_product_id;
171 RETURN 'Product has been added to order!';
172 ELSE
173 RETURN 'Order product have to exists and quantity must be greater than 0!';
174 END IF;
175END;
176$$ LANGUAGE plpgsql;
177*/
178
179-- CREATE OR REPLACE FUNCTION showAvailableProducts()
180-- RETURNS TEXT
181-- AS $$
182-- DECLARE
183-- kursorek refcursor;
184-- v_id product.id%TYPE;
185-- v_name product.name%TYPE;
186-- v_quantity product.quantity%TYPE;
187
188-- v_counter int;
189
190-- BEGIN
191-- OPEN kursorek FOR SELECT id, name, quantity
192-- FROM product p
193-- WHERE p.is_avaiable = 'true';
194
195-- v_counter := 0;
196
197-- LOOP
198-- FETCH kursorek INTO v_id, v_name, v_quantity;
199-- EXIT WHEN NOT FOUND;
200-- v_counter = v_counter + 1;
201-- RAISE NOTICE 'ID: %, Name: %, Quantity: %', v_id, v_name, v_quantity;
202
203-- END LOOP;
204
205-- CLOSE kursorek;
206-- RETURN 'Shop has: ' || v_counter || ' avaiable products.';
207-- END;
208-- $$ LANGUAGE plpgsql;
209
210
211/*
212CREATE OR REPLACE FUNCTION order_proceed(IN a_order_id int)
213RETURNS TEXT
214AS $$
215DECLARE
216 v_status orders.status%TYPE;
217BEGIN
218 IF ((SELECT id FROM orders WHERE id = a_order_id) IS NOT NULL) THEN
219 v_status := (SELECT status FROM orders WHERE id = a_order_id);
220 IF (v_status = 'Pending') THEN
221 UPDATE orders SET status = 'Approved' WHERE id = a_order_id;
222 RETURN 'Order status has been changed to Approved';
223 ELSIF (v_status = 'Approved') THEN
224 UPDATE orders SET status = 'Completed' WHERE id = a_order_id;
225 RETURN 'Order status has been changed to Completed';
226 ELSIF (v_status = 'Completed') THEN
227 RETURN 'Order is completed, there is nothing more to do.';
228 ELSE
229 RETURN 'Order is Cancelled';
230 END IF;
231 ELSE
232 RETURN 'Order with given id does not exists!';
233 END IF;
234END;
235$$ LANGUAGE plpgsql;
236*/
237
238/*
239CREATE OR REPLACE FUNCTION order_cancel(IN a_order_id int)
240RETURNS TEXT
241AS $$
242DECLARE
243 v_status orders.status%TYPE;
244 rec RECORD;
245BEGIN
246 IF ((SELECT id FROM orders WHERE id = a_order_id) IS NOT NULL) THEN
247 v_status := (SELECT status FROM orders WHERE id = a_order_id);
248 IF (v_status = 'Pending') THEN
249
250 FOR rec in SELECT * FROM orders_extended WHERE orders_id = a_order_id
251 LOOP
252 UPDATE product SET quantity = quantity + rec.quantity WHERE id = rec.product_id;
253 END LOOP;
254
255 UPDATE orders SET status = 'Cancelled' WHERE id = a_order_id;
256 RETURN 'Order has been Cancelled';
257 ELSE
258 RETURN 'Orders with status higher than Pending cannot be cancelled!';
259 END IF;
260 ELSE
261 RETURN 'Order with given id does not exists!';
262 END IF;
263END;
264$$ LANGUAGE plpgsql;
265*/
266
267/*
268CREATE OR REPLACE FUNCTION orders_logger()
269RETURNS TRIGGER AS $$
270DECLARE
271 TYP_OPER TEXT;
272 v_OLD_DATA TEXT;
273 v_NEW_DATA TEXT;
274BEGIN
275 TYP_OPER := TG_OP;
276 IF (TG_OP = 'INSERT') THEN
277 v_OLD_DATA := '';
278 v_NEW_DATA := 'ID: ' || NEW.id || ' Customer_ID: ' || NEW.customer_id || ' Additional_info: ' || NEW.additional_info || ' Status' || NEW.status;
279 INSERT INTO LOGGER(operation, on_table, record_id, old_data, new_data, changed_at) VALUES(TYP_OPER, TG_TABLE_NAME, NEW.ID, v_OLD_DATA, v_NEW_DATA, CURRENT_DATE);
280 RETURN NEW;
281 ELSEIF (TG_OP = 'UPDATE') THEN
282 v_OLD_DATA := 'ID: ' || OLD.id || ' Customer_ID: ' || OLD.customer_id || ' Additional_info: ' || OLD.additional_info || ' Status' || OLD.status;
283 v_NEW_DATA := 'ID: ' || NEW.id || ' Customer_ID: ' || NEW.customer_id || ' Additional_info: ' || NEW.additional_info || ' Status' || NEW.status;
284 INSERT INTO LOGGER(operation, on_table, record_id, old_data, new_data, changed_at) VALUES(TYP_OPER, TG_TABLE_NAME, NEW.ID, v_OLD_DATA, v_NEW_DATA, CURRENT_DATE);
285 RETURN NEW;
286 ELSE
287 v_OLD_DATA := '';
288 v_NEW_DATA:= '';
289 INSERT INTO LOGGER(operation, on_table, record_id, old_data, new_data, changed_at) VALUES(TYP_OPER, TG_TABLE_NAME, NULL, v_OLD_DATA, v_NEW_DATA, CURRENT_DATE);
290 RETURN OLD;
291 END IF;
292END;
293$$ LANGUAGE plpgsql;
294
295
296CREATE TRIGGER orders_log_t
297AFTER
298--(BEFORE)
299--(INSTEAD OF)
300INSERT OR UPDATE OR DELETE
301ON orders
302FOR EACH ROW
303EXECUTE PROCEDURE orders_logger();
304*/
305
306/*
307CREATE OR REPLACE FUNCTION add_to_orders_logger()
308RETURNS TRIGGER AS $$
309DECLARE
310 TYP_OPER TEXT;
311 v_OLD_DATA TEXT;
312 v_NEW_DATA TEXT;
313BEGIN
314 TYP_OPER := TG_OP;
315 IF (TG_OP = 'INSERT') THEN
316 v_OLD_DATA := '';
317 v_NEW_DATA := 'ID:' || NEW.id || ' Orders_ID: ' || NEW.orders_id || ' Product_ID: ' || NEW.product_id || ' Quantity:' || NEW.quantity;
318 INSERT INTO LOGGER(operation, on_table, record_id, old_data, new_data, changed_at) VALUES(TYP_OPER, TG_TABLE_NAME, NEW.ID, v_OLD_DATA, v_NEW_DATA, CURRENT_DATE);
319 RETURN NEW;
320 ELSEIF (TG_OP = 'UPDATE') THEN
321 v_OLD_DATA := 'ID:' || OLD.id || ' Orders_ID: ' || OLD.orders_id || ' Product_ID: ' || OLD.product_id || ' Quantity:' || OLD.quantity;
322 v_NEW_DATA := 'ID:' || NEW.id || ' Orders_ID: ' || NEW.orders_id || ' Product_ID: ' || NEW.product_id || ' Quantity:' || NEW.quantity;
323 INSERT INTO LOGGER(operation, on_table, record_id, old_data, new_data, changed_at) VALUES(TYP_OPER, TG_TABLE_NAME, NEW.ID, v_OLD_DATA, v_NEW_DATA, CURRENT_DATE);
324 RETURN NEW;
325 ELSE
326 v_OLD_DATA := '';
327 v_NEW_DATA:= '';
328 INSERT INTO LOGGER(operation, on_table, record_id, old_data, new_data, changed_at) VALUES(TYP_OPER, TG_TABLE_NAME, NULL, v_OLD_DATA, v_NEW_DATA, CURRENT_DATE);
329 RETURN OLD;
330 END IF;
331END;
332$$ LANGUAGE plpgsql;
333
334
335CREATE TRIGGER add_to_orders_log_t
336AFTER
337--(BEFORE)
338--(INSTEAD OF)
339INSERT OR UPDATE OR DELETE
340ON orders_extended
341FOR EACH ROW
342EXECUTE PROCEDURE add_to_orders_logger();
343*/
344
345-- INSERT INTO orders_extended(orders_id, product_id, quantity) values(1, 1, 2);
346
347-- INSERT INTO orders_extended(orders_id, product_id, quantity) values(1, 3, 50);
348
349-- SELECT create_order(1,'LOL');
350-- SELECT add_to_order(2, 1, 1);
351
352--SELECT showAvailableProducts();
353
354-- SELECT order_proceed(1);
355
356-- SELECT order_cancel(1);
357
358-- SELECT add_to_order(2, 1, 1);
359
360
361-- SELECT create_order(4, 'Test');
362
363-- SELECT add_to_order(4, 5, 20);
364
365-- SELECT create_order(1, 'Wiadomosc');
366
367--SELECT add_to_order(1, , 5);
368
369
370
371
372SELECT create_order(1, 'Test2');
373
374SELECT add_to_order(6, 1, 5);
375
376SELECT order_cancel(6);