-
Notifications
You must be signed in to change notification settings - Fork 19
/
stored_procedures.txt
166 lines (139 loc) · 7.34 KB
/
stored_procedures.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
DELIMITER //
CREATE PROCEDURE todays_service_count(IN today_date varchar(50))
BEGIN
SELECT count(*) as amount, "laundry" as type FROM laundry_service WHERE laundry_date=today_date UNION ALL SELECT count(*) as amount, "massage" as type FROM massage_service WHERE massage_date=today_date UNION ALL SELECT count(*) as amount, "roomservice" as type FROM get_roomservice WHERE roomservice_date=today_date UNION ALL SELECT count(*) as amount, "medicalservice" as type FROM get_medicalservice WHERE medicalservice_date=today_date UNION ALL SELECT count(*) as amount, "sport" as type FROM do_sport WHERE dosport_date=today_date
UNION ALL SELECT count(*) as amount, "restaurant" as type FROM restaurant_booking WHERE book_date=today_date;
END//
DELIMITER //
CREATE PROCEDURE get_available_rooms(IN o_room_type varchar(50), IN o_checkin_date varchar(50), IN o_checkout_date varchar(50))
BEGIN
SELECT * FROM `room` WHERE room_type=o_room_type AND NOT EXISTS (
SELECT room_id FROM reservation WHERE reservation.room_id=room.room_id AND checkout_date >= o_checkin_date AND checkin_date <= o_checkout_date
UNION ALL
SELECT room_id FROM room_sales WHERE room_sales.room_id=room.room_id AND checkout_date >= o_checkin_date AND checkin_date <= o_checkout_date
);
END//
DELIMITER //
CREATE PROCEDURE get_customers(IN today_date varchar(50))
BEGIN
SELECT * FROM `room_sales` NATURAL JOIN `customer` WHERE checkout_date >= today_date AND checkin_date <= today_date;
END//
DELIMITER //
CREATE PROCEDURE get_all_customers(IN id INT)
BEGIN
SELECT count(*) FROM customer;
END//
Triggers
============
DROP TRIGGER IF EXISTS `after_insert_sport_service`;
DELIMITER //
CREATE TRIGGER `after_insert_sport_service` AFTER INSERT ON `do_sport`
FOR EACH ROW BEGIN
UPDATE room_sales SET room_sales.total_service_price = room_sales.total_service_price + NEW.dosport_price WHERE room_sales.customer_id = NEW.customer_id AND room_sales.checkin_date <= NEW.dosport_date AND room_sales.checkout_date >= NEW.dosport_date;
END
//
DELIMITER ;
DROP TRIGGER IF EXISTS `before_delete_sport_service`;
DELIMITER //
CREATE TRIGGER `before_delete_sport_service` BEFORE DELETE ON `do_sport`
FOR EACH ROW BEGIN
UPDATE room_sales SET room_sales.total_service_price = room_sales.total_service_price - OLD.dosport_price WHERE room_sales.customer_id = OLD.customer_id AND room_sales.checkin_date <= OLD.dosport_date AND room_sales.checkout_date >= OLD.dosport_date;
END
//
DELIMITER ;
DROP TRIGGER IF EXISTS `after_insert_medical_service`;
DELIMITER //
CREATE TRIGGER `after_insert_medical_service` AFTER INSERT ON `get_medicalservice`
FOR EACH ROW BEGIN
UPDATE room_sales SET room_sales.total_service_price = room_sales.total_service_price + NEW.medicalservice_price WHERE room_sales.customer_id = NEW.customer_id AND room_sales.checkin_date <= NEW.medicalservice_date AND room_sales.checkout_date >= NEW.medicalservice_date;
END
//
DELIMITER ;
DROP TRIGGER IF EXISTS `after_delete_medical_service`;
DELIMITER //
CREATE TRIGGER `after_delete_medical_service` BEFORE DELETE ON `get_medicalservice`
FOR EACH ROW BEGIN
UPDATE room_sales SET room_sales.total_service_price = room_sales.total_service_price - OLD.medicalservice_price WHERE room_sales.customer_id = OLD.customer_id AND room_sales.checkin_date <= OLD.medicalservice_date AND room_sales.checkout_date >= OLD.medicalservice_date;
END
//
DELIMITER ;
DROP TRIGGER IF EXISTS `after_insert_room_service`;
DELIMITER //
CREATE TRIGGER `after_insert_room_service` AFTER INSERT ON `get_roomservice`
FOR EACH ROW BEGIN
UPDATE room_sales SET room_sales.total_service_price = room_sales.total_service_price + NEW.roomservice_price WHERE room_sales.customer_id = NEW.customer_id AND room_sales.checkin_date <= NEW.roomservice_date AND room_sales.checkout_date >= NEW.roomservice_date;
END
//
DELIMITER ;
DROP TRIGGER IF EXISTS `before_delete_room_service`;
DELIMITER //
CREATE TRIGGER `before_delete_room_service` BEFORE DELETE ON `get_roomservice`
FOR EACH ROW BEGIN
UPDATE room_sales SET room_sales.total_service_price = room_sales.total_service_price - OLD.roomservice_price WHERE room_sales.customer_id = OLD.customer_id AND room_sales.checkin_date <= OLD.roomservice_date AND room_sales.checkout_date >= OLD.roomservice_date;
END
//
DELIMITER ;
DROP TRIGGER IF EXISTS `after_insert_laundry_service`;
DELIMITER //
CREATE TRIGGER `after_insert_laundry_service` AFTER INSERT ON `laundry_service`
FOR EACH ROW BEGIN
UPDATE room_sales SET room_sales.total_service_price = room_sales.total_service_price + NEW.laundry_price WHERE room_sales.customer_id = NEW.customer_id AND room_sales.checkin_date <= NEW.laundry_date AND room_sales.checkout_date >= NEW.laundry_date;
END
//
DELIMITER ;
DROP TRIGGER IF EXISTS `before_delete_laundry_service`;
DELIMITER //
CREATE TRIGGER `before_delete_laundry_service` BEFORE DELETE ON `laundry_service`
FOR EACH ROW BEGIN
UPDATE room_sales SET room_sales.total_service_price = room_sales.total_service_price - OLD.laundry_price WHERE room_sales.customer_id = OLD.customer_id AND room_sales.checkin_date <= OLD.laundry_date AND room_sales.checkout_date >= OLD.laundry_date;
END
//
DELIMITER ;
DROP TRIGGER IF EXISTS `after_insert_restaurant_service`;
DELIMITER //
CREATE TRIGGER `after_insert_restaurant_service` AFTER INSERT ON `restaurant_booking`
FOR EACH ROW BEGIN
UPDATE room_sales SET room_sales.total_service_price = room_sales.total_service_price + NEW.book_price WHERE room_sales.customer_id = NEW.customer_id AND room_sales.checkin_date <= NEW.book_date AND room_sales.checkout_date >= NEW.book_date;
END
//
DELIMITER ;
DROP TRIGGER IF EXISTS `before_delete_restaurant_service`;
DELIMITER //
CREATE TRIGGER `before_delete_restaurant_service` BEFORE DELETE ON `restaurant_booking`
FOR EACH ROW BEGIN
UPDATE room_sales SET room_sales.total_service_price = room_sales.total_service_price - OLD.book_price WHERE room_sales.customer_id = OLD.customer_id AND room_sales.checkin_date <= OLD.book_date AND room_sales.checkout_date >= OLD.book_date;
END
//
DELIMITER ;
DROP TRIGGER IF EXISTS `after_insert_massage_service`;
DELIMITER //
CREATE TRIGGER `after_insert_massage_service` AFTER INSERT ON `massage_service`
FOR EACH ROW BEGIN
UPDATE room_sales SET room_sales.total_service_price = room_sales.total_service_price + NEW.massage_price WHERE room_sales.customer_id = NEW.customer_id AND room_sales.checkin_date <= NEW.massage_date AND room_sales.checkout_date >= NEW.massage_date;
END
//
DELIMITER ;
DROP TRIGGER IF EXISTS `before_delete_massage_service`;
DELIMITER //
CREATE TRIGGER `before_delete_massage_service` BEFORE DELETE ON `massage_service`
FOR EACH ROW BEGIN
UPDATE room_sales SET room_sales.total_service_price = room_sales.total_service_price - OLD.massage_price WHERE room_sales.customer_id = OLD.customer_id AND room_sales.checkin_date <= OLD.massage_date AND room_sales.checkout_date >= OLD.massage_date;
END
//
DELIMITER ;
DROP TRIGGER IF EXISTS `after_insert_room`;
DELIMITER //
CREATE TRIGGER `after_insert_room` AFTER INSERT ON `room`
FOR EACH ROW BEGIN
UPDATE room_type SET room_type.room_quantity =room_type.room_quantity + 1 WHERE room_type.room_type = NEW.room_type;
END
//
DELIMITER ;
DROP TRIGGER IF EXISTS `before_delete_room`;
DELIMITER //
CREATE TRIGGER `before_delete_room` BEFORE DELETE ON `room`
FOR EACH ROW BEGIN
UPDATE room_type SET room_type.room_quantity =room_type.room_quantity - 1 WHERE room_type.room_type = OLD.room_type;
END
//
DELIMITER ;