· 7 years ago · Nov 24, 2018, 02:28 PM
1-- BUILD_INVOICE(): THIS BUILDS THE INVOICE TO A TEMPORARY TABLE
2CREATE DEFINER=`root`@`localhost` PROCEDURE `BUILD_INVOICE`()
3BEGIN
4 -- variables
5 DECLARE SEARCH_FLAG INT DEFAULT 0;
6 DECLARE CURRENT_ITEM CHAR(10);
7 DECLARE CURRENT_ITEM_AMOUNT INT;
8 DECLARE CURRENT_ITEM_TRANSACTION_ID CHAR(10);
9 DECLARE PREVIOUS_ITEM_TRANSACTION_ID CHAR(10) DEFAULT "0000000000";
10 DECLARE CURRENT_ITEM_CUSTOMER_ID CHAR(10);
11 DECLARE DATE DATE;
12 DECLARE NAME VARCHAR(45);
13 DECLARE GRAND_TOTAL BIGINT DEFAULT 0;
14
15 -- cursors
16 DECLARE ITEM_TRANSACTION_ID CURSOR FOR
17 SELECT TRANSACTION_ID FROM TRANSACTION_DETAILS;
18 DECLARE ITEM_AMOUNT CURSOR FOR
19 SELECT TRANSACTION_DETAILS_ITEMAMOUNT FROM TRANSACTION_DETAILS;
20 DECLARE ITEM_ID CURSOR FOR
21 SELECT TRANSACTION_DETAILS_ID FROM TRANSACTION_DETAILS;
22
23 -- handler if not found
24 DECLARE CONTINUE HANDLER FOR NOT FOUND
25 BEGIN
26 SET SEARCH_FLAG = 1;
27 END;
28
29 OPEN ITEM_ID;
30 OPEN ITEM_AMOUNT;
31 OPEN ITEM_TRANSACTION_ID;
32 TRANSLATE_ID: LOOP
33 FETCH ITEM_ID INTO CURRENT_ITEM;
34 FETCH ITEM_AMOUNT INTO CURRENT_ITEM_AMOUNT;
35 FETCH ITEM_TRANSACTION_ID INTO CURRENT_ITEM_TRANSACTION_ID;
36 IF CURRENT_ITEM_TRANSACTION_ID != PREVIOUS_ITEM_TRANSACTION_ID THEN
37 IF PREVIOUS_ITEM_TRANSACTION_ID != "0000000000" THEN
38 INSERT INTO TEMP_TRANSACTION(ITEM_NAME, PRICE_PER_ITEM, PRICE, EXTRA_DETAILS) VALUES ("________________________________________________________________________________________________________________________", "__________________________________________________", "__________________________________________________", "__________________________________________________");
39 INSERT INTO TEMP_TRANSACTION (ITEM_NAME, PRICE_PER_ITEM, PRICE, EXTRA_DETAILS) VALUES ("> GRAND TOTAL: ", CONCAT("Rp.", GRAND_TOTAL), "", "");
40 -- SELECT PREVIOUS_ITEM_TRANSACTION_ID, CURRENT_ITEM_TRANSACTION_ID;
41 UPDATE TRANSACTION AS T SET TRANSACTION_TOTAL = GRAND_TOTAL WHERE T.TRANSACTION_ID = PREVIOUS_ITEM_TRANSACTION_ID;
42 SET GRAND_TOTAL = 0;
43 INSERT INTO TEMP_TRANSACTION (ITEM_NAME, PRICE_PER_ITEM, PRICE, EXTRA_DETAILS) VALUES ("", "", "", "");
44 END IF;
45 SET PREVIOUS_ITEM_TRANSACTION_ID = CURRENT_ITEM_TRANSACTION_ID;
46 SET CURRENT_ITEM_CUSTOMER_ID = (SELECT CUSTOMER_ID FROM HOTEL_SBD.TRANSACTION T WHERE T.TRANSACTION_ID = CURRENT_ITEM_TRANSACTION_ID);
47 SET NAME = (SELECT CUSTOMER_NAME FROM CUSTOMER C WHERE C.CUSTOMER_ID = CURRENT_ITEM_CUSTOMER_ID);
48 -- SELECT CUSTOMER_ID FROM TRANSACTION T WHERE T.TRANSACTION_ID = CURRENT_ITEM_TRANSACTION_ID;
49 -- SELECT NAME, CURRENT_ITEM_TRANSACTION_ID, PREVIOUS_ITEM_TRANSACTION_ID, CURRENT_ITEM_CUSTOMER_ID;
50 SET DATE = (SELECT TRANSACTION_DATE FROM TRANSACTION AS T WHERE T.TRANSACTION_ID = CURRENT_ITEM_TRANSACTION_ID);
51 INSERT INTO TEMP_TRANSACTION(ITEM_NAME, PRICE_PER_ITEM, PRICE, EXTRA_DETAILS) VALUES ("________________________________________________________________________________________________________________________", "__________________________________________________", "__________________________________________________", "__________________________________________________");
52 INSERT INTO TEMP_TRANSACTION(ITEM_NAME, PRICE_PER_ITEM, PRICE, EXTRA_DETAILS) VALUES (CONCAT("MR./MRS. ", NAME), CONCAT("CUSTOMER ID: ", CURRENT_ITEM_CUSTOMER_ID), CONCAT("TRANSACTION ID: ", CURRENT_ITEM_TRANSACTION_ID), CONCAT("DATE OF TRANSACTION: ", DATE));
53 INSERT INTO TEMP_TRANSACTION(ITEM_NAME, PRICE_PER_ITEM, PRICE, EXTRA_DETAILS) VALUES ("________________________________________________________________________________________________________________________", "__________________________________________________", "__________________________________________________", "__________________________________________________");
54 INSERT INTO TEMP_TRANSACTION(ITEM_NAME, PRICE_PER_ITEM, PRICE, EXTRA_DETAILS) VALUES ("[ITEM NAME]", "[PRICE PER ITEM]", "[PRICE]", "[DETAILS]");
55 INSERT INTO TEMP_TRANSACTION(ITEM_NAME, PRICE_PER_ITEM, PRICE, EXTRA_DETAILS) VALUES ("________________________________________________________________________________________________________________________", "__________________________________________________", "__________________________________________________", "__________________________________________________");
56 END IF;
57 IF SEARCH_FLAG = 0 THEN
58 INSERT INTO TEMP_TRANSACTION (ITEM_NAME, PRICE_PER_ITEM, PRICE, EXTRA_DETAILS)
59 SELECT CONCAT("ROOM: ", ROOM_TYPE), CONCAT("@ Rp.", ROOM_PRICE), CONCAT("Rp.", ROOM_PRICE*CURRENT_ITEM_AMOUNT), CONCAT("(", ROOM_NO, ") DURATION: ", CURRENT_ITEM_AMOUNT, "DAY(S)")
60 FROM ROOM AS R
61 WHERE R.TRANSACTION_DETAILS_ID = CURRENT_ITEM;
62 IF EXISTS(SELECT ROOM_PRICE FROM ROOM AS R WHERE R.TRANSACTION_DETAILS_ID = CURRENT_ITEM) THEN
63 SET GRAND_TOTAL = (SELECT ROOM_PRICE FROM ROOM AS R WHERE R.TRANSACTION_DETAILS_ID = CURRENT_ITEM)*CURRENT_ITEM_AMOUNT + GRAND_TOTAL;
64 END IF;
65 INSERT INTO TEMP_TRANSACTION (ITEM_NAME, PRICE_PER_ITEM, PRICE, EXTRA_DETAILS)
66 SELECT FOOD_AND_BEVERAGE_NAME, CONCAT("@ Rp.", FOOD_AND_BEVERAGE_PRICE), CONCAT("Rp.", FOOD_AND_BEVERAGE_PRICE*CURRENT_ITEM_AMOUNT), CONCAT("AMOUNT: ", CURRENT_ITEM_AMOUNT)
67 FROM FOOD_AND_BEVERAGE AS FNB
68 WHERE FNB.TRANSACTION_DETAILS_ID = CURRENT_ITEM;
69 IF EXISTS(SELECT FOOD_AND_BEVERAGE_PRICE FROM FOOD_AND_BEVERAGE AS FNB WHERE FNB.TRANSACTION_DETAILS_ID = CURRENT_ITEM) THEN
70 SET GRAND_TOTAL = (SELECT FOOD_AND_BEVERAGE_PRICE FROM FOOD_AND_BEVERAGE AS FNB WHERE FNB.TRANSACTION_DETAILS_ID = CURRENT_ITEM)*CURRENT_ITEM_AMOUNT + GRAND_TOTAL;
71 END IF;
72 INSERT INTO TEMP_TRANSACTION (ITEM_NAME, PRICE_PER_ITEM, PRICE, EXTRA_DETAILS)
73 SELECT "RENT: BALLROOM", CONCAT("@ Rp.", BALLROOM_PRICE), CONCAT("Rp.", BALLROOM_PRICE*CURRENT_ITEM_AMOUNT), CONCAT("(", BALLROOM_ID, ") DURATION: ", CURRENT_ITEM_AMOUNT, "x24hr")
74 FROM BALLROOM AS BAL
75 WHERE BAL.TRANSACTION_DETAILS_ID = CURRENT_ITEM;
76 IF EXISTS(SELECT BALLROOM_PRICE FROM BALLROOM AS BAL WHERE BAL.TRANSACTION_DETAILS_ID = CURRENT_ITEM) THEN
77 SET GRAND_TOTAL = (SELECT BALLROOM_PRICE FROM BALLROOM AS BAL WHERE BAL.TRANSACTION_DETAILS_ID = CURRENT_ITEM)*CURRENT_ITEM_AMOUNT + GRAND_TOTAL;
78 END IF;
79 INSERT INTO TEMP_TRANSACTION (ITEM_NAME, PRICE_PER_ITEM, PRICE, EXTRA_DETAILS)
80 SELECT "RENT: FITNESS ROOM", CONCAT("@ Rp.", FITNESS_ROOM_PRICE), CONCAT("Rp.", FITNESS_ROOM_PRICE*CURRENT_ITEM_AMOUNT), CONCAT("(", FITNESS_ROOM_ID,") DURATION: ", CURRENT_ITEM_AMOUNT, "x15mins")
81 FROM FITNESS_ROOM AS FIT
82 WHERE FIT.TRANSACTION_DETAILS_ID = CURRENT_ITEM;
83 IF EXISTS(SELECT FITNESS_ROOM_PRICE FROM FITNESS_ROOM AS FIT WHERE FIT.TRANSACTION_DETAILS_ID = CURRENT_ITEM) THEN
84 SET GRAND_TOTAL = (SELECT FITNESS_ROOM_PRICE FROM FITNESS_ROOM AS FIT WHERE FIT.TRANSACTION_DETAILS_ID = CURRENT_ITEM)*CURRENT_ITEM_AMOUNT + GRAND_TOTAL;
85 END IF;
86 INSERT INTO TEMP_TRANSACTION (ITEM_NAME, PRICE_PER_ITEM, PRICE, EXTRA_DETAILS)
87 SELECT CONCAT("SPA: ", SPA_SERVICETYPE), CONCAT("@ Rp.", SPA_PRICE), CONCAT("Rp.", SPA_PRICE*CURRENT_ITEM_AMOUNT), CONCAT("(", SPA_ID,") DURATION: ", CURRENT_ITEM_AMOUNT, "x15mins")
88 FROM SPA AS S
89 WHERE S.TRANSACTION_DETAILS_ID = CURRENT_ITEM;
90 IF EXISTS(SELECT SPA_PRICE FROM SPA AS S WHERE S.TRANSACTION_DETAILS_ID = CURRENT_ITEM) THEN
91 SET GRAND_TOTAL = (SELECT SPA_PRICE FROM SPA AS S WHERE S.TRANSACTION_DETAILS_ID = CURRENT_ITEM)*CURRENT_ITEM_AMOUNT + GRAND_TOTAL;
92 END IF;
93 INSERT INTO TEMP_TRANSACTION (ITEM_NAME, PRICE_PER_ITEM, PRICE, EXTRA_DETAILS)
94 SELECT CONCAT("VEHICLE RIDE: ", TRANSPORTATION_MODEL), CONCAT("@ Rp.", TRANSPORTATION_PRICE), CONCAT("Rp.", TRANSPORTATION_PRICE*CURRENT_ITEM_AMOUNT), CONCAT("(", LICENSE_PLATE, ") ", CURRENT_ITEM_AMOUNT, "TRIP(S)")
95 FROM TRANSPORTATION AS TS
96 WHERE TS.TRANSACTION_DETAILS_ID = CURRENT_ITEM;
97 IF EXISTS(SELECT TRANSPORTATION_PRICE FROM TRANSPORTATION AS TS WHERE TS.TRANSACTION_DETAILS_ID = CURRENT_ITEM) THEN
98 SET GRAND_TOTAL = (SELECT TRANSPORTATION_PRICE FROM TRANSPORTATION AS TS WHERE TS.TRANSACTION_DETAILS_ID = CURRENT_ITEM)*CURRENT_ITEM_AMOUNT + GRAND_TOTAL;
99 END IF;
100 INSERT INTO TEMP_TRANSACTION (ITEM_NAME, PRICE_PER_ITEM, PRICE, EXTRA_DETAILS)
101 SELECT CONCAT("PACKAGE NUMBER: ", PACKAGE_ID), CONCAT("@ Rp.", PACKAGE_PRICE), CONCAT("Rp.", PACKAGE_PRICE*CURRENT_ITEM_AMOUNT), CONCAT("AMOUNT: ", CURRENT_ITEM_AMOUNT)
102 FROM PACKAGE AS P
103 WHERE P.TRANSACTION_DETAILS_ID = CURRENT_ITEM;
104 IF EXISTS(SELECT PACKAGE_PRICE FROM PACKAGE AS P WHERE P.TRANSACTION_DETAILS_ID = CURRENT_ITEM) THEN
105 SET GRAND_TOTAL = (SELECT PACKAGE_PRICE FROM PACKAGE AS P WHERE P.TRANSACTION_DETAILS_ID = CURRENT_ITEM)*CURRENT_ITEM_AMOUNT + GRAND_TOTAL;
106 END IF;
107 ELSE
108 INSERT INTO TEMP_TRANSACTION(ITEM_NAME, PRICE_PER_ITEM, PRICE, EXTRA_DETAILS) VALUES ("________________________________________________________________________________________________________________________", "__________________________________________________", "__________________________________________________", "__________________________________________________");
109 INSERT INTO TEMP_TRANSACTION (ITEM_NAME, PRICE_PER_ITEM, PRICE, EXTRA_DETAILS) VALUES ("> GRAND TOTAL: ", CONCAT("Rp.", GRAND_TOTAL), "", "");
110 UPDATE TRANSACTION AS T SET TRANSACTION_TOTAL = GRAND_TOTAL WHERE T.TRANSACTION_ID = PREVIOUS_ITEM_TRANSACTION_ID;
111 SET GRAND_TOTAL = 0;
112 LEAVE TRANSLATE_ID;
113 END IF;
114 END LOOP TRANSLATE_ID;
115 CLOSE ITEM_ID;
116 CLOSE ITEM_AMOUNT;
117 CLOSE ITEM_TRANSACTION_ID;
118END
119
120-- INVOICE_DUMP(): THIS CONVERTS THE TEMPORARY TABLE TO TEXT FORM
121CREATE DEFINER=`root`@`localhost` PROCEDURE `BUILD_INVOICE`()
122BEGIN
123 -- variables
124 DECLARE SEARCH_FLAG INT DEFAULT 0;
125 DECLARE CURRENT_ITEM CHAR(10);
126 DECLARE CURRENT_ITEM_AMOUNT INT;
127 DECLARE CURRENT_ITEM_TRANSACTION_ID CHAR(10);
128 DECLARE PREVIOUS_ITEM_TRANSACTION_ID CHAR(10) DEFAULT "0000000000";
129 DECLARE CURRENT_ITEM_CUSTOMER_ID CHAR(10);
130 DECLARE DATE DATE;
131 DECLARE NAME VARCHAR(45);
132 DECLARE GRAND_TOTAL BIGINT DEFAULT 0;
133
134 -- cursors
135 DECLARE ITEM_TRANSACTION_ID CURSOR FOR
136 SELECT TRANSACTION_ID FROM TRANSACTION_DETAILS;
137 DECLARE ITEM_AMOUNT CURSOR FOR
138 SELECT TRANSACTION_DETAILS_ITEMAMOUNT FROM TRANSACTION_DETAILS;
139 DECLARE ITEM_ID CURSOR FOR
140 SELECT TRANSACTION_DETAILS_ID FROM TRANSACTION_DETAILS;
141
142 -- handler if not found
143 DECLARE CONTINUE HANDLER FOR NOT FOUND
144 BEGIN
145 SET SEARCH_FLAG = 1;
146 END;
147
148 OPEN ITEM_ID;
149 OPEN ITEM_AMOUNT;
150 OPEN ITEM_TRANSACTION_ID;
151 TRANSLATE_ID: LOOP
152 FETCH ITEM_ID INTO CURRENT_ITEM;
153 FETCH ITEM_AMOUNT INTO CURRENT_ITEM_AMOUNT;
154 FETCH ITEM_TRANSACTION_ID INTO CURRENT_ITEM_TRANSACTION_ID;
155 IF CURRENT_ITEM_TRANSACTION_ID != PREVIOUS_ITEM_TRANSACTION_ID THEN
156 IF PREVIOUS_ITEM_TRANSACTION_ID != "0000000000" THEN
157 INSERT INTO TEMP_TRANSACTION(ITEM_NAME, PRICE_PER_ITEM, PRICE, EXTRA_DETAILS) VALUES ("________________________________________________________________________________________________________________________", "__________________________________________________", "__________________________________________________", "__________________________________________________");
158 INSERT INTO TEMP_TRANSACTION (ITEM_NAME, PRICE_PER_ITEM, PRICE, EXTRA_DETAILS) VALUES ("> GRAND TOTAL: ", CONCAT("Rp.", GRAND_TOTAL), "", "");
159 -- SELECT PREVIOUS_ITEM_TRANSACTION_ID, CURRENT_ITEM_TRANSACTION_ID;
160 UPDATE TRANSACTION AS T SET TRANSACTION_TOTAL = GRAND_TOTAL WHERE T.TRANSACTION_ID = PREVIOUS_ITEM_TRANSACTION_ID;
161 SET GRAND_TOTAL = 0;
162 INSERT INTO TEMP_TRANSACTION (ITEM_NAME, PRICE_PER_ITEM, PRICE, EXTRA_DETAILS) VALUES ("", "", "", "");
163 END IF;
164 SET PREVIOUS_ITEM_TRANSACTION_ID = CURRENT_ITEM_TRANSACTION_ID;
165 SET CURRENT_ITEM_CUSTOMER_ID = (SELECT CUSTOMER_ID FROM HOTEL_SBD.TRANSACTION T WHERE T.TRANSACTION_ID = CURRENT_ITEM_TRANSACTION_ID);
166 SET NAME = (SELECT CUSTOMER_NAME FROM CUSTOMER C WHERE C.CUSTOMER_ID = CURRENT_ITEM_CUSTOMER_ID);
167 -- SELECT CUSTOMER_ID FROM TRANSACTION T WHERE T.TRANSACTION_ID = CURRENT_ITEM_TRANSACTION_ID;
168 -- SELECT NAME, CURRENT_ITEM_TRANSACTION_ID, PREVIOUS_ITEM_TRANSACTION_ID, CURRENT_ITEM_CUSTOMER_ID;
169 SET DATE = (SELECT TRANSACTION_DATE FROM TRANSACTION AS T WHERE T.TRANSACTION_ID = CURRENT_ITEM_TRANSACTION_ID);
170 INSERT INTO TEMP_TRANSACTION(ITEM_NAME, PRICE_PER_ITEM, PRICE, EXTRA_DETAILS) VALUES ("________________________________________________________________________________________________________________________", "__________________________________________________", "__________________________________________________", "__________________________________________________");
171 INSERT INTO TEMP_TRANSACTION(ITEM_NAME, PRICE_PER_ITEM, PRICE, EXTRA_DETAILS) VALUES (CONCAT("MR./MRS. ", NAME), CONCAT("CUSTOMER ID: ", CURRENT_ITEM_CUSTOMER_ID), CONCAT("TRANSACTION ID: ", CURRENT_ITEM_TRANSACTION_ID), CONCAT("DATE OF TRANSACTION: ", DATE));
172 INSERT INTO TEMP_TRANSACTION(ITEM_NAME, PRICE_PER_ITEM, PRICE, EXTRA_DETAILS) VALUES ("________________________________________________________________________________________________________________________", "__________________________________________________", "__________________________________________________", "__________________________________________________");
173 INSERT INTO TEMP_TRANSACTION(ITEM_NAME, PRICE_PER_ITEM, PRICE, EXTRA_DETAILS) VALUES ("[ITEM NAME]", "[PRICE PER ITEM]", "[PRICE]", "[DETAILS]");
174 INSERT INTO TEMP_TRANSACTION(ITEM_NAME, PRICE_PER_ITEM, PRICE, EXTRA_DETAILS) VALUES ("________________________________________________________________________________________________________________________", "__________________________________________________", "__________________________________________________", "__________________________________________________");
175 END IF;
176 IF SEARCH_FLAG = 0 THEN
177 INSERT INTO TEMP_TRANSACTION (ITEM_NAME, PRICE_PER_ITEM, PRICE, EXTRA_DETAILS)
178 SELECT CONCAT("ROOM: ", ROOM_TYPE), CONCAT("@ Rp.", ROOM_PRICE), CONCAT("Rp.", ROOM_PRICE*CURRENT_ITEM_AMOUNT), CONCAT("(", ROOM_NO, ") DURATION: ", CURRENT_ITEM_AMOUNT, "DAY(S)")
179 FROM ROOM AS R
180 WHERE R.TRANSACTION_DETAILS_ID = CURRENT_ITEM;
181 IF EXISTS(SELECT ROOM_PRICE FROM ROOM AS R WHERE R.TRANSACTION_DETAILS_ID = CURRENT_ITEM) THEN
182 SET GRAND_TOTAL = (SELECT ROOM_PRICE FROM ROOM AS R WHERE R.TRANSACTION_DETAILS_ID = CURRENT_ITEM)*CURRENT_ITEM_AMOUNT + GRAND_TOTAL;
183 END IF;
184 INSERT INTO TEMP_TRANSACTION (ITEM_NAME, PRICE_PER_ITEM, PRICE, EXTRA_DETAILS)
185 SELECT FOOD_AND_BEVERAGE_NAME, CONCAT("@ Rp.", FOOD_AND_BEVERAGE_PRICE), CONCAT("Rp.", FOOD_AND_BEVERAGE_PRICE*CURRENT_ITEM_AMOUNT), CONCAT("AMOUNT: ", CURRENT_ITEM_AMOUNT)
186 FROM FOOD_AND_BEVERAGE AS FNB
187 WHERE FNB.TRANSACTION_DETAILS_ID = CURRENT_ITEM;
188 IF EXISTS(SELECT FOOD_AND_BEVERAGE_PRICE FROM FOOD_AND_BEVERAGE AS FNB WHERE FNB.TRANSACTION_DETAILS_ID = CURRENT_ITEM) THEN
189 SET GRAND_TOTAL = (SELECT FOOD_AND_BEVERAGE_PRICE FROM FOOD_AND_BEVERAGE AS FNB WHERE FNB.TRANSACTION_DETAILS_ID = CURRENT_ITEM)*CURRENT_ITEM_AMOUNT + GRAND_TOTAL;
190 END IF;
191 INSERT INTO TEMP_TRANSACTION (ITEM_NAME, PRICE_PER_ITEM, PRICE, EXTRA_DETAILS)
192 SELECT "RENT: BALLROOM", CONCAT("@ Rp.", BALLROOM_PRICE), CONCAT("Rp.", BALLROOM_PRICE*CURRENT_ITEM_AMOUNT), CONCAT("(", BALLROOM_ID, ") DURATION: ", CURRENT_ITEM_AMOUNT, "x24hr")
193 FROM BALLROOM AS BAL
194 WHERE BAL.TRANSACTION_DETAILS_ID = CURRENT_ITEM;
195 IF EXISTS(SELECT BALLROOM_PRICE FROM BALLROOM AS BAL WHERE BAL.TRANSACTION_DETAILS_ID = CURRENT_ITEM) THEN
196 SET GRAND_TOTAL = (SELECT BALLROOM_PRICE FROM BALLROOM AS BAL WHERE BAL.TRANSACTION_DETAILS_ID = CURRENT_ITEM)*CURRENT_ITEM_AMOUNT + GRAND_TOTAL;
197 END IF;
198 INSERT INTO TEMP_TRANSACTION (ITEM_NAME, PRICE_PER_ITEM, PRICE, EXTRA_DETAILS)
199 SELECT "RENT: FITNESS ROOM", CONCAT("@ Rp.", FITNESS_ROOM_PRICE), CONCAT("Rp.", FITNESS_ROOM_PRICE*CURRENT_ITEM_AMOUNT), CONCAT("(", FITNESS_ROOM_ID,") DURATION: ", CURRENT_ITEM_AMOUNT, "x15mins")
200 FROM FITNESS_ROOM AS FIT
201 WHERE FIT.TRANSACTION_DETAILS_ID = CURRENT_ITEM;
202 IF EXISTS(SELECT FITNESS_ROOM_PRICE FROM FITNESS_ROOM AS FIT WHERE FIT.TRANSACTION_DETAILS_ID = CURRENT_ITEM) THEN
203 SET GRAND_TOTAL = (SELECT FITNESS_ROOM_PRICE FROM FITNESS_ROOM AS FIT WHERE FIT.TRANSACTION_DETAILS_ID = CURRENT_ITEM)*CURRENT_ITEM_AMOUNT + GRAND_TOTAL;
204 END IF;
205 INSERT INTO TEMP_TRANSACTION (ITEM_NAME, PRICE_PER_ITEM, PRICE, EXTRA_DETAILS)
206 SELECT CONCAT("SPA: ", SPA_SERVICETYPE), CONCAT("@ Rp.", SPA_PRICE), CONCAT("Rp.", SPA_PRICE*CURRENT_ITEM_AMOUNT), CONCAT("(", SPA_ID,") DURATION: ", CURRENT_ITEM_AMOUNT, "x15mins")
207 FROM SPA AS S
208 WHERE S.TRANSACTION_DETAILS_ID = CURRENT_ITEM;
209 IF EXISTS(SELECT SPA_PRICE FROM SPA AS S WHERE S.TRANSACTION_DETAILS_ID = CURRENT_ITEM) THEN
210 SET GRAND_TOTAL = (SELECT SPA_PRICE FROM SPA AS S WHERE S.TRANSACTION_DETAILS_ID = CURRENT_ITEM)*CURRENT_ITEM_AMOUNT + GRAND_TOTAL;
211 END IF;
212 INSERT INTO TEMP_TRANSACTION (ITEM_NAME, PRICE_PER_ITEM, PRICE, EXTRA_DETAILS)
213 SELECT CONCAT("VEHICLE RIDE: ", TRANSPORTATION_MODEL), CONCAT("@ Rp.", TRANSPORTATION_PRICE), CONCAT("Rp.", TRANSPORTATION_PRICE*CURRENT_ITEM_AMOUNT), CONCAT("(", LICENSE_PLATE, ") ", CURRENT_ITEM_AMOUNT, "TRIP(S)")
214 FROM TRANSPORTATION AS TS
215 WHERE TS.TRANSACTION_DETAILS_ID = CURRENT_ITEM;
216 IF EXISTS(SELECT TRANSPORTATION_PRICE FROM TRANSPORTATION AS TS WHERE TS.TRANSACTION_DETAILS_ID = CURRENT_ITEM) THEN
217 SET GRAND_TOTAL = (SELECT TRANSPORTATION_PRICE FROM TRANSPORTATION AS TS WHERE TS.TRANSACTION_DETAILS_ID = CURRENT_ITEM)*CURRENT_ITEM_AMOUNT + GRAND_TOTAL;
218 END IF;
219 INSERT INTO TEMP_TRANSACTION (ITEM_NAME, PRICE_PER_ITEM, PRICE, EXTRA_DETAILS)
220 SELECT CONCAT("PACKAGE NUMBER: ", PACKAGE_ID), CONCAT("@ Rp.", PACKAGE_PRICE), CONCAT("Rp.", PACKAGE_PRICE*CURRENT_ITEM_AMOUNT), CONCAT("AMOUNT: ", CURRENT_ITEM_AMOUNT)
221 FROM PACKAGE AS P
222 WHERE P.TRANSACTION_DETAILS_ID = CURRENT_ITEM;
223 IF EXISTS(SELECT PACKAGE_PRICE FROM PACKAGE AS P WHERE P.TRANSACTION_DETAILS_ID = CURRENT_ITEM) THEN
224 SET GRAND_TOTAL = (SELECT PACKAGE_PRICE FROM PACKAGE AS P WHERE P.TRANSACTION_DETAILS_ID = CURRENT_ITEM)*CURRENT_ITEM_AMOUNT + GRAND_TOTAL;
225 END IF;
226 ELSE
227 INSERT INTO TEMP_TRANSACTION(ITEM_NAME, PRICE_PER_ITEM, PRICE, EXTRA_DETAILS) VALUES ("________________________________________________________________________________________________________________________", "__________________________________________________", "__________________________________________________", "__________________________________________________");
228 INSERT INTO TEMP_TRANSACTION (ITEM_NAME, PRICE_PER_ITEM, PRICE, EXTRA_DETAILS) VALUES ("> GRAND TOTAL: ", CONCAT("Rp.", GRAND_TOTAL), "", "");
229 UPDATE TRANSACTION AS T SET TRANSACTION_TOTAL = GRAND_TOTAL WHERE T.TRANSACTION_ID = PREVIOUS_ITEM_TRANSACTION_ID;
230 SET GRAND_TOTAL = 0;
231 LEAVE TRANSLATE_ID;
232 END IF;
233 END LOOP TRANSLATE_ID;
234 CLOSE ITEM_ID;
235 CLOSE ITEM_AMOUNT;
236 CLOSE ITEM_TRANSACTION_ID;
237END
238
239-- CREATE INVOICE: THIS QUERY COMPILE ALL THE PROCEDURE NEEDED TO CREATE INVOICE
240UPDATE TRANSACTION SET TRANSACTION_TOTAL = NULL;
241
242DROP TABLE IF EXISTS TEMP_TRANSACTION;
243DROP TABLE IF EXISTS INVOICE;
244
245CREATE TABLE TEMP_TRANSACTION (
246 ITEM_NAME VARCHAR(1000),
247 PRICE_PER_ITEM VARCHAR(1000),
248 PRICE VARCHAR(1000),
249 EXTRA_DETAILS VARCHAR(1000)
250);
251CREATE TABLE INVOICE (
252 INVOICE_TEXT VARCHAR(3000)
253);
254
255CALL BUILD_INVOICE();
256CALL INVOICE_DUMP();
257SELECT ITEM_NAME, IFNULL(PRICE_PER_ITEM, " "), IFNULL(PRICE, " "), IFNULL(EXTRA_DETAILS, " ") FROM TEMP_TRANSACTION;
258SELECT * FROM INVOICE;