· 8 years ago · Feb 11, 2018, 12:04 PM
1-- Adminer 4.2.5 MySQL dump
2
3SET NAMES utf8;
4SET time_zone = '+00:00';
5SET foreign_key_checks = 0;
6SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
7
8DELIMITER ;;
9
10DROP PROCEDURE IF EXISTS `add_auditorium`;;
11CREATE PROCEDURE `add_auditorium`(IN `aud_no` int(40), IN `aud_name` varchar(60), IN `area` int(40), IN `type` varchar(60), IN `b_id` int(40))
12insert auditorium(Aud_no,Aud_name,Area,type,B_id) values(aud_no,aud_name,area,type,b_id);;
13
14DROP PROCEDURE IF EXISTS `add_garden`;;
15CREATE PROCEDURE `add_garden`(IN `g_no` int(40), IN `g_area` int(40), IN `type` varchar(60), IN `g_name` varchar(60), IN `B_id` int(40))
16insert garden(g_no,g_area,g_name,g_type,B_id) values (g_no,g_area,g_name,type,B_id);;
17
18DROP PROCEDURE IF EXISTS `add_marriage_hall`;;
19CREATE PROCEDURE `add_marriage_hall`(IN `hall_no` int(40), IN `hall_area` int(40), IN `type` varchar(60), IN `b_id` int(40))
20insert marriage_hall(Hall_no,Hall_area,type,B_id) values (hall_no,hall_area,type,b_id);;
21
22DROP PROCEDURE IF EXISTS `add_new_customer`;;
23CREATE PROCEDURE `add_new_customer`(IN `customer_id` int(40), IN `first_name` varchar(60), IN `last_name` varchar(60), IN `country` varchar(60), IN `city` varchar(60), IN `mob_no` decimal(10), IN `email_id` varchar(60), IN `b_id` int(40))
24insert customer(Customer_id,First_name,Last_name,Country,City,Mob_no,Email,B_id) values (customer_id,first_name,Last_name,country,city,mob_no,email_id,b_id);;
25
26DROP PROCEDURE IF EXISTS `add_room`;;
27CREATE PROCEDURE `add_room`(IN `room_no` int(40), IN `type` varchar(60), IN `B_id` int(40))
28insert rooms(Room_no,type,B_id) values (room_no,type,B_id);;
29
30DROP PROCEDURE IF EXISTS `book_Auditorium`;;
31CREATE PROCEDURE `book_Auditorium`(IN `Audi_id` int, IN `c_id` int, IN `day` int, IN `p_mode` varchar(60), IN `booked_from` datetime, IN `Audi_type` varchar(60), IN `area` int(40))
32begin
33if((select A_b_status from auditorium where Aud_no=Audi_id)=0)
34then
35/*inserting into booking datails */
36call insertBookingDetails('auditorium',p_mode,day,(select Charge*day from aud_type where type=Audi_type),booked_from);
37update auditorium set A_b_status=1 where Aud_no=Audi_id;
38insert into auditorium_booking_details
39values(c_id,Audi_id,(select max(Booking_id) from booking_details) );
40else
41select 'auditorium is already booked' as error;
42end if;
43end;;
44
45DROP PROCEDURE IF EXISTS `book_garden`;;
46CREATE PROCEDURE `book_garden`(IN `g_id` int, IN `c_id` int, IN `day` int, IN `p_mode` varchar(60), IN `booked_from` datetime, IN `g_type` varchar(60), IN `g_area` int(40))
47begin
48if((select g_b_status from garden where garden.G_no=g_id)=0)
49then
50/*inserting into booking datails */
51call insertBookingDetails('garden',p_mode,day,(select charge*day from garden_type where type=g_type and area=g_area),booked_from);
52update garden set g_b_status=1 where G_no=g_id;
53insert into garden_booking_details
54values(c_id,g_id,(select max(Booking_id) from booking_details) );
55else
56select 'Garden is already booked' as error;
57end if;
58end;;
59
60DROP PROCEDURE IF EXISTS `book_marriage_hall`;;
61CREATE PROCEDURE `book_marriage_hall`(IN `hall_id` int, IN `c_id` int, IN `day` int, IN `p_mode` varchar(60), IN `booked_from` datetime, IN `hall_type` varchar(60), IN `area` int(40))
62begin
63if((select H_B_status from marriage_hall where Hall_no=hall_id )=0)
64then
65/*inserting into booking datails */
66call insertBookingDetails('marriage_hall',p_mode,day,(select charge*day from marriage_hall_type where type=hall_type and Area=area),booked_from);
67update marriage_hall set H_B_status=1 where Hall_no=hall_id;
68insert into marriage_hall_booking_details
69values(c_id,hall_id,(select max(Booking_id) from booking_details) );
70else
71select 'hall is already booked' as error;
72end if;
73end;;
74
75DROP PROCEDURE IF EXISTS `book_room`;;
76CREATE PROCEDURE `book_room`(IN `r_id` int, IN `c_id` int, IN `day` int, IN `p_mode` varchar(60), IN `booked_from` datetime, IN `r_type` varchar(60))
77begin
78if((select R_b_status from rooms where Room_no=r_id)=0)
79then
80/*inserting into booking datails */
81call insertBookingDetails('rooms',p_mode,day,(select charge*day from room_type where type=r_type),booked_from);
82update rooms set R_b_status=1 where Room_no=r_id;
83insert into room_booking_details
84values(c_id,r_id,(select max(Booking_id) from booking_details) );
85else
86select 'room is already booked' as error;
87end if;
88end;;
89
90DROP PROCEDURE IF EXISTS `gardenWithFacility`;;
91CREATE PROCEDURE `gardenWithFacility`(IN `branchId` tinyint, IN `gardenType` varchar(60))
92begin
93select G_name
94from garden
95where g_type=gardenType and B_id=branchId;
96end;;
97
98DROP PROCEDURE IF EXISTS `insertBookingDetails`;;
99CREATE PROCEDURE `insertBookingDetails`(IN `booking_of` varchar(60), IN `p_mode` varchar(60), IN `day` int, IN `amount` bigint, IN `booking_from` datetime)
100begin
101insert into booking_details(Booking_of,payment_method,amount,booked_on,booked_from,booked_till)
102values(booking_of,p_mode,amount,now(),booking_from,DATE_ADD(booking_from, INTERVAL day DAY));
103end;;
104
105DROP PROCEDURE IF EXISTS `insert_aud_type`;;
106CREATE PROCEDURE `insert_aud_type`(IN `area` int(40), IN `type` varchar(60), IN `charge` int(40))
107insert aud_type(Area,type,Charge) values (area,type,charge);;
108
109DROP PROCEDURE IF EXISTS `insert_garden_type`;;
110CREATE PROCEDURE `insert_garden_type`(IN `area` int(40), IN `type` varchar(60), IN `charge` int(40))
111insert garden_type values(area,type,charge);;
112
113DROP PROCEDURE IF EXISTS `insert_marriage_hall_type`;;
114CREATE PROCEDURE `insert_marriage_hall_type`(IN `area` int(40), IN `type` varchar(60), IN `charge` int(40))
115insert marriage_hall_type values (area,type,charge);;
116
117DROP PROCEDURE IF EXISTS `insert_room_type`;;
118CREATE PROCEDURE `insert_room_type`(IN `type` varchar(60), IN `charge` int(40))
119insert room_type(type,charge) values (type,charge);;
120
121DROP PROCEDURE IF EXISTS `open_branch`;;
122CREATE PROCEDURE `open_branch`(IN `id` int(40), IN `name` varchar(60), IN `location` varchar(60))
123begin
124insert into branch
125values(id,name,location);
126
127
128
129
130
131
132
133end;;
134
135DROP PROCEDURE IF EXISTS `register_garden`;;
136CREATE PROCEDURE `register_garden`(IN `name` varchar(30), IN `area` float, IN `branch_id` int)
137begin
138insert into garden(G_name,g_area,B_id) values(name,area,branch_id);
139end;;
140
141DROP PROCEDURE IF EXISTS `showAllAvailableGardenType`;;
142CREATE PROCEDURE `showAllAvailableGardenType`()
143begin
144 select *
145from garden_type;
146end;;
147
148DROP PROCEDURE IF EXISTS `showAllCustomerOfBranch`;;
149CREATE PROCEDURE `showAllCustomerOfBranch`(IN `branchId` int(40))
150begin
151select *
152from customer
153where B_id=branchId;
154end;;
155
156DROP PROCEDURE IF EXISTS `showCustomerDetail`;;
157CREATE PROCEDURE `showCustomerDetail`(IN `c_id` int(40))
158begin
159select * from customer
160where c_id=Customer_id;
161
162end;;
163
164DROP PROCEDURE IF EXISTS `showCustomerInRoom`;;
165CREATE PROCEDURE `showCustomerInRoom`(IN `roomNumber` int(40))
166begin
167select *
168from customer
169where Customer_id in(
170select Customer_id
171from room_booking_details
172where Room_no=roomNumber);
173end;;
174
175DROP PROCEDURE IF EXISTS `show_branch`;;
176CREATE PROCEDURE `show_branch`()
177begin
178select *from branch;
179
180end;;
181
182DELIMITER ;
183
184DROP TABLE IF EXISTS `auditorium`;
185CREATE TABLE `auditorium` (
186 `Aud_no` int(40) NOT NULL AUTO_INCREMENT,
187 `Aud_name` varchar(60) NOT NULL,
188 `Area` int(40) NOT NULL,
189 `type` varchar(60) NOT NULL,
190 `A_b_status` int(40) NOT NULL DEFAULT '0',
191 `B_id` int(40) NOT NULL,
192 PRIMARY KEY (`Aud_no`),
193 KEY `B_id` (`B_id`),
194 KEY `Area` (`Area`,`type`),
195 CONSTRAINT `auditorium_ibfk_1` FOREIGN KEY (`B_id`) REFERENCES `branch` (`B_id`),
196 CONSTRAINT `auditorium_ibfk_2` FOREIGN KEY (`Area`, `type`) REFERENCES `aud_type` (`Area`, `type`)
197) ENGINE=InnoDB DEFAULT CHARSET=latin1;
198
199INSERT INTO `auditorium` (`Aud_no`, `Aud_name`, `Area`, `type`, `A_b_status`, `B_id`) VALUES
200(1, 'Krishna Auditorium', 2000, 'full AC', 0, 1),
201(2, 'basudev auditorium', 2000, 'full AC', 1, 2);
202
203DROP TABLE IF EXISTS `auditorium_booking_details`;
204CREATE TABLE `auditorium_booking_details` (
205 `Customer_id` int(40) NOT NULL,
206 `Aud_no` int(40) NOT NULL,
207 `Booking_id` int(40) NOT NULL,
208 KEY `Customer_id` (`Customer_id`),
209 KEY `Aud_no` (`Aud_no`),
210 KEY `Booking_id` (`Booking_id`),
211 CONSTRAINT `auditorium_booking_details_ibfk_1` FOREIGN KEY (`Customer_id`) REFERENCES `customer` (`Customer_id`),
212 CONSTRAINT `auditorium_booking_details_ibfk_2` FOREIGN KEY (`Aud_no`) REFERENCES `auditorium` (`Aud_no`),
213 CONSTRAINT `auditorium_booking_details_ibfk_3` FOREIGN KEY (`Booking_id`) REFERENCES `booking_details` (`Booking_id`)
214) ENGINE=InnoDB DEFAULT CHARSET=latin1;
215
216INSERT INTO `auditorium_booking_details` (`Customer_id`, `Aud_no`, `Booking_id`) VALUES
217(2, 2, 14);
218
219DROP TABLE IF EXISTS `aud_type`;
220CREATE TABLE `aud_type` (
221 `Area` int(40) NOT NULL,
222 `type` varchar(60) NOT NULL,
223 `Charge` int(40) NOT NULL,
224 PRIMARY KEY (`Area`,`type`)
225) ENGINE=InnoDB DEFAULT CHARSET=latin1;
226
227INSERT INTO `aud_type` (`Area`, `type`, `Charge`) VALUES
228(2000, 'full AC', 3000);
229
230DROP TABLE IF EXISTS `booking_details`;
231CREATE TABLE `booking_details` (
232 `Booking_id` int(40) NOT NULL AUTO_INCREMENT,
233 `Booking_of` varchar(60) NOT NULL,
234 `Payment_method` varchar(60) NOT NULL,
235 `Amount` int(40) NOT NULL,
236 `booked_on` datetime NOT NULL,
237 `Booked_from` datetime NOT NULL,
238 `Booked_till` datetime NOT NULL,
239 PRIMARY KEY (`Booking_id`)
240) ENGINE=InnoDB DEFAULT CHARSET=latin1;
241
242INSERT INTO `booking_details` (`Booking_id`, `Booking_of`, `Payment_method`, `Amount`, `booked_on`, `Booked_from`, `Booked_till`) VALUES
243(12, 'marriage_hall', 'bhim api', 40000, '2018-01-05 00:16:43', '2018-01-05 00:16:43', '2018-01-07 00:16:43'),
244(13, 'garden', 'tez', 200000, '2018-01-05 00:20:38', '2018-01-05 00:20:38', '2018-01-07 00:20:38'),
245(14, 'auditorium', 'tez', 18000, '2018-01-05 00:25:38', '2018-01-07 11:00:00', '2018-01-11 00:25:38'),
246(15, 'rooms', 'cash', 12000, '2018-01-05 00:29:51', '2018-01-07 23:02:12', '2018-01-15 23:02:12'),
247(16, 'garden', 'cash', 300000, '2018-01-05 01:08:04', '2018-01-05 01:08:04', '2018-01-08 01:08:04');
248
249DROP TABLE IF EXISTS `branch`;
250CREATE TABLE `branch` (
251 `B_id` int(40) NOT NULL AUTO_INCREMENT,
252 `B_name` varchar(60) NOT NULL,
253 `B_location` varchar(60) NOT NULL,
254 PRIMARY KEY (`B_id`)
255) ENGINE=InnoDB DEFAULT CHARSET=latin1;
256
257INSERT INTO `branch` (`B_id`, `B_name`, `B_location`) VALUES
258(1, 'Garh Govind 1', 'Sardarpura'),
259(2, 'Garh Govind 2', 'Ratanada'),
260(3, 'garh_govind3', 'mandaur'),
261(4, 'GarhGovind4', 'New Delhi'),
262(5, 'Garh Govind 5', 'Mathura'),
263(6, 'Garh Govind 6', 'Vrindawan'),
264(7, 'Garh Govind 7', 'Dwarika');
265
266DROP TABLE IF EXISTS `customer`;
267CREATE TABLE `customer` (
268 `Customer_id` int(40) NOT NULL AUTO_INCREMENT,
269 `First_name` varchar(60) NOT NULL,
270 `Last_name` varchar(60) NOT NULL,
271 `Country` varchar(60) NOT NULL,
272 `City` varchar(60) NOT NULL,
273 `Mob_no` decimal(10,0) NOT NULL,
274 `Email` varchar(60) NOT NULL,
275 `B_id` int(40) NOT NULL,
276 PRIMARY KEY (`Customer_id`),
277 KEY `B_id` (`B_id`),
278 CONSTRAINT `customer_ibfk_1` FOREIGN KEY (`B_id`) REFERENCES `branch` (`B_id`)
279) ENGINE=InnoDB DEFAULT CHARSET=latin1;
280
281INSERT INTO `customer` (`Customer_id`, `First_name`, `Last_name`, `Country`, `City`, `Mob_no`, `Email`, `B_id`) VALUES
282(1, 'Kumar', 'Anshu', 'India', 'Madhubani', 9472906848, 'kumaranshumbm@gmail.com', 1),
283(2, 'Kaushal', 'Raj', 'India', 'Nalanda', 7219920239, 'kaushal@gmail.com', 1),
284(3, 'Deepak ', 'singh', 'India', 'Gopalganj', 7219920242, 'rasdeep@gmail.com', 2),
285(4, 'Anshu', 'Kumar', 'U.S.A', 'California', 9472906848, 'iitiananshu@gmail.com', 1);
286
287DROP TABLE IF EXISTS `garden`;
288CREATE TABLE `garden` (
289 `G_no` int(40) NOT NULL AUTO_INCREMENT,
290 `G_name` varchar(60) NOT NULL,
291 `g_area` int(40) NOT NULL,
292 `g_type` varchar(60) NOT NULL,
293 `G_b_status` bit(40) NOT NULL DEFAULT b'0',
294 `B_id` int(40) NOT NULL,
295 PRIMARY KEY (`G_no`),
296 KEY `B_id` (`B_id`),
297 KEY `g_area` (`g_area`,`g_type`),
298 CONSTRAINT `garden_ibfk_1` FOREIGN KEY (`B_id`) REFERENCES `branch` (`B_id`),
299 CONSTRAINT `garden_ibfk_2` FOREIGN KEY (`g_area`, `g_type`) REFERENCES `garden_type` (`Area`, `type`)
300) ENGINE=InnoDB DEFAULT CHARSET=latin1;
301
302INSERT INTO `garden` (`G_no`, `G_name`, `g_area`, `g_type`, `G_b_status`, `B_id`) VALUES
303(1, 'Radha Udyan', 5000, 'with_flower', CONV('1', 2, 10) + 0, 1),
304(2, 'krishna udayan', 5000, 'with_flower', CONV('1', 2, 10) + 0, 2),
305(3, 'yashoda udayan', 6000, 'without_flower', CONV('0', 2, 10) + 0, 1);
306
307DROP TABLE IF EXISTS `garden_booking_details`;
308CREATE TABLE `garden_booking_details` (
309 `Customer_id` int(40) NOT NULL,
310 `G_no` int(40) NOT NULL,
311 `Booking_id` int(40) NOT NULL,
312 KEY `Customer_id` (`Customer_id`),
313 KEY `G_no` (`G_no`),
314 KEY `Booking_id` (`Booking_id`),
315 CONSTRAINT `garden_booking_details_ibfk_1` FOREIGN KEY (`Customer_id`) REFERENCES `customer` (`Customer_id`),
316 CONSTRAINT `garden_booking_details_ibfk_2` FOREIGN KEY (`G_no`) REFERENCES `garden` (`G_no`),
317 CONSTRAINT `garden_booking_details_ibfk_3` FOREIGN KEY (`Booking_id`) REFERENCES `booking_details` (`Booking_id`)
318) ENGINE=InnoDB DEFAULT CHARSET=latin1;
319
320INSERT INTO `garden_booking_details` (`Customer_id`, `G_no`, `Booking_id`) VALUES
321(3, 2, 13),
322(1, 1, 16);
323
324DROP TABLE IF EXISTS `garden_type`;
325CREATE TABLE `garden_type` (
326 `Area` int(40) NOT NULL,
327 `type` varchar(60) NOT NULL,
328 `charge` int(40) NOT NULL,
329 PRIMARY KEY (`Area`,`type`)
330) ENGINE=InnoDB DEFAULT CHARSET=latin1;
331
332INSERT INTO `garden_type` (`Area`, `type`, `charge`) VALUES
333(5000, 'with_flower', 100000),
334(6000, 'without_flower', 3000);
335
336DROP TABLE IF EXISTS `marriage_hall`;
337CREATE TABLE `marriage_hall` (
338 `Hall_no` int(40) NOT NULL AUTO_INCREMENT,
339 `Hall_area` int(40) NOT NULL,
340 `type` varchar(60) NOT NULL,
341 `H_B_status` int(40) NOT NULL DEFAULT '0',
342 `B_id` int(40) NOT NULL,
343 PRIMARY KEY (`Hall_no`),
344 KEY `B_id` (`B_id`),
345 KEY `H_area` (`Hall_area`,`type`),
346 CONSTRAINT `marriage_hall_ibfk_1` FOREIGN KEY (`B_id`) REFERENCES `branch` (`B_id`),
347 CONSTRAINT `marriage_hall_ibfk_2` FOREIGN KEY (`Hall_area`, `type`) REFERENCES `marriage_hall_type` (`Area`, `type`)
348) ENGINE=InnoDB DEFAULT CHARSET=latin1;
349
350INSERT INTO `marriage_hall` (`Hall_no`, `Hall_area`, `type`, `H_B_status`, `B_id`) VALUES
351(1, 1000, 'full AC', 0, 1),
352(2, 1000, 'full AC', 1, 2);
353
354DROP TABLE IF EXISTS `marriage_hall_booking_details`;
355CREATE TABLE `marriage_hall_booking_details` (
356 `Customer_id` int(40) NOT NULL,
357 `Hall_no` int(40) NOT NULL,
358 `Booking_id` int(40) NOT NULL,
359 KEY `Customer_id` (`Customer_id`),
360 KEY `Hall_no` (`Hall_no`),
361 KEY `Booking_id` (`Booking_id`),
362 CONSTRAINT `marriage_hall_booking_details_ibfk_1` FOREIGN KEY (`Customer_id`) REFERENCES `customer` (`Customer_id`),
363 CONSTRAINT `marriage_hall_booking_details_ibfk_2` FOREIGN KEY (`Hall_no`) REFERENCES `marriage_hall` (`Hall_no`),
364 CONSTRAINT `marriage_hall_booking_details_ibfk_3` FOREIGN KEY (`Booking_id`) REFERENCES `booking_details` (`Booking_id`)
365) ENGINE=InnoDB DEFAULT CHARSET=latin1;
366
367INSERT INTO `marriage_hall_booking_details` (`Customer_id`, `Hall_no`, `Booking_id`) VALUES
368(2, 2, 12);
369
370DROP TABLE IF EXISTS `marriage_hall_type`;
371CREATE TABLE `marriage_hall_type` (
372 `Area` int(40) NOT NULL,
373 `type` varchar(60) NOT NULL,
374 `charge` int(40) NOT NULL,
375 PRIMARY KEY (`Area`,`type`)
376) ENGINE=InnoDB DEFAULT CHARSET=latin1;
377
378INSERT INTO `marriage_hall_type` (`Area`, `type`, `charge`) VALUES
379(1000, 'full AC', 20000);
380
381DROP TABLE IF EXISTS `rooms`;
382CREATE TABLE `rooms` (
383 `Room_no` int(40) NOT NULL AUTO_INCREMENT,
384 `type` varchar(60) NOT NULL,
385 `R_b_status` int(40) NOT NULL DEFAULT '0',
386 `B_id` int(11) NOT NULL,
387 PRIMARY KEY (`Room_no`),
388 KEY `type` (`type`),
389 CONSTRAINT `rooms_ibfk_1` FOREIGN KEY (`type`) REFERENCES `room_type` (`type`) ON UPDATE CASCADE
390) ENGINE=InnoDB DEFAULT CHARSET=latin1;
391
392INSERT INTO `rooms` (`Room_no`, `type`, `R_b_status`, `B_id`) VALUES
393(1, 'single bed AC', 0, 1),
394(2, 'single bed AC', 1, 1);
395
396DROP TABLE IF EXISTS `room_booking_details`;
397CREATE TABLE `room_booking_details` (
398 `Customer_id` int(40) NOT NULL,
399 `Room_no` int(40) NOT NULL,
400 `Booking_id` int(40) NOT NULL,
401 KEY `Customer_id` (`Customer_id`),
402 KEY `Room_no` (`Room_no`),
403 KEY `Booking_id` (`Booking_id`),
404 CONSTRAINT `room_booking_details_ibfk_1` FOREIGN KEY (`Customer_id`) REFERENCES `customer` (`Customer_id`),
405 CONSTRAINT `room_booking_details_ibfk_2` FOREIGN KEY (`Room_no`) REFERENCES `rooms` (`Room_no`),
406 CONSTRAINT `room_booking_details_ibfk_3` FOREIGN KEY (`Booking_id`) REFERENCES `booking_details` (`Booking_id`)
407) ENGINE=InnoDB DEFAULT CHARSET=latin1;
408
409INSERT INTO `room_booking_details` (`Customer_id`, `Room_no`, `Booking_id`) VALUES
410(3, 2, 15);
411
412DROP TABLE IF EXISTS `room_type`;
413CREATE TABLE `room_type` (
414 `type` varchar(60) NOT NULL,
415 `Charge` int(40) NOT NULL,
416 PRIMARY KEY (`type`)
417) ENGINE=InnoDB DEFAULT CHARSET=latin1;
418
419INSERT INTO `room_type` (`type`, `Charge`) VALUES
420('single bed AC', 1500);
421
422-- 2018-01-28 05:00:56