· 9 years ago · May 05, 2017, 07:28 PM
1-- MySQL Administrator dump 1.4
2--
3-- ------------------------------------------------------
4-- Server version 5.1.32-community-log
5
6
7/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
8/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
9/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
10/*!40101 SET NAMES utf8 */;
11
12/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
13/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
14/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
15
16
17--
18-- Create schema water_uwi
19--
20
21CREATE DATABASE IF NOT EXISTS water_uwi;
22USE water_uwi;
23
24--
25-- Temporary table structure for view `calctime`
26--
27DROP TABLE IF EXISTS `calctime`;
28DROP VIEW IF EXISTS `calctime`;
29CREATE TABLE `calctime` (
30 `delivery_id` int(11),
31 `LAST_DELIVERY` time,
32 `delivery_date` timestamp,
33 `description` varchar(100)
34);
35
36--
37-- Temporary table structure for view `last_call_time`
38--
39DROP TABLE IF EXISTS `last_call_time`;
40DROP VIEW IF EXISTS `last_call_time`;
41CREATE TABLE `last_call_time` (
42 `call_id` int(11),
43 `description` varchar(100),
44 `call_date` datetime,
45 `notes` varchar(500),
46 `status` varchar(45)
47);
48
49--
50-- Temporary table structure for view `last_delivery`
51--
52DROP TABLE IF EXISTS `last_delivery`;
53DROP VIEW IF EXISTS `last_delivery`;
54CREATE TABLE `last_delivery` (
55 `delivery_id` int(11),
56 `delivery_date` timestamp,
57 `driver_name` varchar(45),
58 `capacity` int(11),
59 `capacity_measure` varchar(45),
60 `description` varchar(100)
61);
62
63--
64-- Temporary table structure for view `tank_cap`
65--
66DROP TABLE IF EXISTS `tank_cap`;
67DROP VIEW IF EXISTS `tank_cap`;
68CREATE TABLE `tank_cap` (
69 `tank_id` varchar(10),
70 `capacity` int(11),
71 `capacity_measure` varchar(45),
72 `description` varchar(100)
73);
74
75--
76-- Temporary table structure for view `tank_list`
77--
78DROP TABLE IF EXISTS `tank_list`;
79DROP VIEW IF EXISTS `tank_list`;
80CREATE TABLE `tank_list` (
81 `location_code` varchar(45),
82 `description` varchar(100),
83 `capacity` int(11),
84 `capacity_measure` varchar(45)
85);
86
87--
88-- Definition of table `cap_measure`
89--
90
91DROP TABLE IF EXISTS `cap_measure`;
92CREATE TABLE `cap_measure` (
93 `meter_id` int(11) NOT NULL AUTO_INCREMENT,
94 `cap_measure` varchar(45) NOT NULL,
95 PRIMARY KEY (`meter_id`)
96) ENGINE=MyISAM AUTO_INCREMENT=2324 DEFAULT CHARSET=latin1;
97
98--
99-- Dumping data for table `cap_measure`
100--
101
102/*!40000 ALTER TABLE `cap_measure` DISABLE KEYS */;
103/*!40000 ALTER TABLE `cap_measure` ENABLE KEYS */;
104
105
106--
107-- Definition of table `delivery`
108--
109
110DROP TABLE IF EXISTS `delivery`;
111CREATE TABLE `delivery` (
112 `delivery_id` int(11) NOT NULL AUTO_INCREMENT,
113 `delivery_date` timestamp NULL DEFAULT NULL,
114 `truck_id` varchar(6) NOT NULL,
115 `location_id` int(11) NOT NULL,
116 `tank_id` varchar(10) NOT NULL,
117 PRIMARY KEY (`delivery_id`),
118 KEY `idtruck` (`truck_id`),
119 KEY `dev_loc` (`location_id`),
120 KEY `tank_d` (`tank_id`),
121 CONSTRAINT `dev_loc` FOREIGN KEY (`location_id`) REFERENCES `tank_location` (`tank_location_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
122 CONSTRAINT `idtruck` FOREIGN KEY (`truck_id`) REFERENCES `truck` (`truck_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
123 CONSTRAINT `tank_d` FOREIGN KEY (`tank_id`) REFERENCES `tank` (`tank_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
124) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
125
126--
127-- Dumping data for table `delivery`
128--
129
130/*!40000 ALTER TABLE `delivery` DISABLE KEYS */;
131INSERT INTO `delivery` (`delivery_id`,`delivery_date`,`truck_id`,`location_id`,`tank_id`) VALUES
132 (1,'2010-02-05 03:04:44','TR123',2325,'43434'),
133 (2,'2010-02-04 03:03:00','ST453',2324,'43434'),
134 (4,'2010-02-06 07:05:22','ST453',2325,'43434');
135/*!40000 ALTER TABLE `delivery` ENABLE KEYS */;
136
137
138--
139-- Definition of table `location`
140--
141
142DROP TABLE IF EXISTS `location`;
143CREATE TABLE `location` (
144 `location_id` int(11) NOT NULL AUTO_INCREMENT,
145 `location_code` varchar(45) NOT NULL,
146 PRIMARY KEY (`location_id`)
147) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
148
149--
150-- Dumping data for table `location`
151--
152
153/*!40000 ALTER TABLE `location` DISABLE KEYS */;
154INSERT INTO `location` (`location_id`,`location_code`) VALUES
155 (1,'Mary Secole'),
156 (2,'Taylor'),
157 (3,'Rex'),
158 (4,'Preston'),
159 (5,'Chanc');
160/*!40000 ALTER TABLE `location` ENABLE KEYS */;
161
162
163--
164-- Definition of table `phone_call`
165--
166
167DROP TABLE IF EXISTS `phone_call`;
168CREATE TABLE `phone_call` (
169 `call_id` int(11) NOT NULL AUTO_INCREMENT,
170 `location_id` int(11) NOT NULL,
171 `call_date` datetime NOT NULL,
172 `status` int(11) DEFAULT NULL,
173 `notes` varchar(500) DEFAULT NULL,
174 PRIMARY KEY (`call_id`),
175 KEY `call_loc_id` (`location_id`),
176 KEY `stat` (`status`),
177 CONSTRAINT `call_loc_id` FOREIGN KEY (`location_id`) REFERENCES `tank_location` (`tank_location_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
178 CONSTRAINT `stat` FOREIGN KEY (`status`) REFERENCES `status` (`status_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
179) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;
180
181--
182-- Dumping data for table `phone_call`
183--
184
185/*!40000 ALTER TABLE `phone_call` DISABLE KEYS */;
186INSERT INTO `phone_call` (`call_id`,`location_id`,`call_date`,`status`,`notes`) VALUES
187 (1,2324,'2010-02-05 02:41:36',1,NULL),
188 (12,2325,'2010-02-12 03:08:45',2,NULL);
189/*!40000 ALTER TABLE `phone_call` ENABLE KEYS */;
190
191
192--
193-- Definition of table `status`
194--
195
196DROP TABLE IF EXISTS `status`;
197CREATE TABLE `status` (
198 `status_id` int(11) NOT NULL AUTO_INCREMENT,
199 `description` varchar(45) NOT NULL,
200 PRIMARY KEY (`status_id`)
201) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
202
203--
204-- Dumping data for table `status`
205--
206
207/*!40000 ALTER TABLE `status` DISABLE KEYS */;
208INSERT INTO `status` (`status_id`,`description`) VALUES
209 (1,'empty'),
210 (2,'full');
211/*!40000 ALTER TABLE `status` ENABLE KEYS */;
212
213
214--
215-- Definition of table `tank`
216--
217
218DROP TABLE IF EXISTS `tank`;
219CREATE TABLE `tank` (
220 `tank_id` varchar(10) NOT NULL,
221 `location_id` int(11) NOT NULL,
222 `capacity` int(11) NOT NULL,
223 `capacity_measure` varchar(45) NOT NULL,
224 PRIMARY KEY (`tank_id`),
225 KEY `tank_loc_id` (`location_id`),
226 CONSTRAINT `tank_loc_id` FOREIGN KEY (`location_id`) REFERENCES `tank_location` (`tank_location_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
227) ENGINE=InnoDB DEFAULT CHARSET=latin1;
228
229--
230-- Dumping data for table `tank`
231--
232
233/*!40000 ALTER TABLE `tank` DISABLE KEYS */;
234INSERT INTO `tank` (`tank_id`,`location_id`,`capacity`,`capacity_measure`) VALUES
235 ('23223',2325,23232,'M'),
236 ('43434',2324,232323,'L');
237/*!40000 ALTER TABLE `tank` ENABLE KEYS */;
238
239
240--
241-- Definition of table `tank_location`
242--
243
244DROP TABLE IF EXISTS `tank_location`;
245CREATE TABLE `tank_location` (
246 `tank_location_id` int(11) NOT NULL AUTO_INCREMENT,
247 `location_id` int(11) NOT NULL,
248 `description` varchar(100) NOT NULL,
249 PRIMARY KEY (`tank_location_id`),
250 KEY `tank_loc` (`location_id`),
251 CONSTRAINT `tank_loc` FOREIGN KEY (`location_id`) REFERENCES `location` (`location_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
252) ENGINE=InnoDB AUTO_INCREMENT=2326 DEFAULT CHARSET=latin1;
253
254--
255-- Dumping data for table `tank_location`
256--
257
258/*!40000 ALTER TABLE `tank_location` DISABLE KEYS */;
259INSERT INTO `tank_location` (`tank_location_id`,`location_id`,`description`) VALUES
260 (1,1,'2 Floor'),
261 (2324,1,'Front Gate'),
262 (2325,2,'Roosters');
263/*!40000 ALTER TABLE `tank_location` ENABLE KEYS */;
264
265
266--
267-- Definition of table `truck`
268--
269
270DROP TABLE IF EXISTS `truck`;
271CREATE TABLE `truck` (
272 `truck_id` varchar(6) NOT NULL,
273 `driver_name` varchar(45) DEFAULT NULL,
274 `capacity` int(11) DEFAULT NULL,
275 `capacity_measure` varchar(45) DEFAULT NULL,
276 PRIMARY KEY (`truck_id`)
277) ENGINE=InnoDB DEFAULT CHARSET=latin1;
278
279--
280-- Dumping data for table `truck`
281--
282
283/*!40000 ALTER TABLE `truck` DISABLE KEYS */;
284INSERT INTO `truck` (`truck_id`,`driver_name`,`capacity`,`capacity_measure`) VALUES
285 ('FT322','jim brown',22221,'M'),
286 ('ST453','andre pink',22556,'M'),
287 ('TR123','john brown',232323,'CM'),
288 ('TR234','joe black',22222,'G'),
289 ('WQ232','daine k',3434322,'L');
290/*!40000 ALTER TABLE `truck` ENABLE KEYS */;
291
292
293--
294-- Definition of table `user`
295--
296
297DROP TABLE IF EXISTS `user`;
298CREATE TABLE `user` (
299 `user_id` int(11) NOT NULL AUTO_INCREMENT,
300 `username` varchar(45) NOT NULL,
301 `user_pass` varchar(45) NOT NULL,
302 `user_type` int(11) NOT NULL,
303 PRIMARY KEY (`user_id`),
304 KEY `u_type` (`user_type`),
305 CONSTRAINT `u_type` FOREIGN KEY (`user_type`) REFERENCES `user_type` (`usertype_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
306) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
307
308--
309-- Dumping data for table `user`
310--
311
312/*!40000 ALTER TABLE `user` DISABLE KEYS */;
313INSERT INTO `user` (`user_id`,`username`,`user_pass`,`user_type`) VALUES
314 (1,'admin','password',1),
315 (2,'test','test',2);
316/*!40000 ALTER TABLE `user` ENABLE KEYS */;
317
318
319--
320-- Definition of table `user_type`
321--
322
323DROP TABLE IF EXISTS `user_type`;
324CREATE TABLE `user_type` (
325 `usertype_id` int(11) NOT NULL,
326 `description` varchar(45) NOT NULL,
327 PRIMARY KEY (`usertype_id`)
328) ENGINE=InnoDB DEFAULT CHARSET=latin1;
329
330--
331-- Dumping data for table `user_type`
332--
333
334/*!40000 ALTER TABLE `user_type` DISABLE KEYS */;
335INSERT INTO `user_type` (`usertype_id`,`description`) VALUES
336 (1,'admin'),
337 (2,'normal');
338/*!40000 ALTER TABLE `user_type` ENABLE KEYS */;
339
340
341--
342-- Definition of procedure `get`
343--
344
345DROP PROCEDURE IF EXISTS `get`;
346
347DELIMITER $$
348
349/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
350CREATE DEFINER=`root`@`localhost` PROCEDURE `get`(user_nm VARCHAR(50))
351BEGIN
352 SELECT user_pass FROM user WHERE username = user_um ;
353END $$
354/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
355
356DELIMITER ;
357
358--
359-- Definition of procedure `get_pass`
360--
361
362DROP PROCEDURE IF EXISTS `get_pass`;
363
364DELIMITER $$
365
366/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
367CREATE DEFINER=`root`@`localhost` PROCEDURE `get_pass`(user_nm VARCHAR(50))
368BEGIN
369 SELECT user_pass FROM user WHERE username = user_um ;
370END $$
371/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
372
373DELIMITER ;
374
375--
376-- Definition of procedure `insert_delivery`
377--
378
379DROP PROCEDURE IF EXISTS `insert_delivery`;
380
381DELIMITER $$
382
383/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
384CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_delivery`(delivery_dt DATETIME, truck_num VARCHAR(6),loc_id INT,tank_num VARCHAR(6))
385BEGIN
386 INSERT INTO delivery (delivery_date,truck_id,location_id,tank_id) VALUES (delivery_dt,truck_num,loc_id,tank_num);
387END $$
388/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
389
390DELIMITER ;
391
392--
393-- Definition of procedure `insert_location`
394--
395
396DROP PROCEDURE IF EXISTS `insert_location`;
397
398DELIMITER $$
399
400/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
401CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_location`(loc_code VARCHAR(50))
402BEGIN
403 INSERT INTO location (location_code) VALUES (loc_code);
404END $$
405/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
406
407DELIMITER ;
408
409--
410-- Definition of procedure `insert_phone_call`
411--
412
413DROP PROCEDURE IF EXISTS `insert_phone_call`;
414
415DELIMITER $$
416
417/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
418CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_phone_call`(loc_id INT,call_dt DATETIME, stat INT, nt VARCHAR(500))
419BEGIN
420 INSERT INTO phone_call(location_id,call_date,status,notes) VALUES (loc_id,call_dt,stat,nt);
421END $$
422/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
423
424DELIMITER ;
425
426--
427-- Definition of procedure `insert_tank`
428--
429
430DROP PROCEDURE IF EXISTS `insert_tank`;
431
432DELIMITER $$
433
434/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
435CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_tank`(tank_num VARCHAR(10), loc_id INT, cap INT, cap_meter VARCHAR(45))
436BEGIN
437 INSERT INTO tank (tank_id,location_id,capacity,capacity_measure) VALUES (tank_num,loc_id,cap,cap_meter);
438END $$
439/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
440
441DELIMITER ;
442
443--
444-- Definition of procedure `insert_tank_location`
445--
446
447DROP PROCEDURE IF EXISTS `insert_tank_location`;
448
449DELIMITER $$
450
451/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
452CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_tank_location`(loc_id INT, descript VARCHAR(100))
453BEGIN
454 INSERT INTO tank_location (location_id,description) VALUES (loc_id,descript);
455END $$
456/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
457
458DELIMITER ;
459
460--
461-- Definition of procedure `insert_truck`
462--
463
464DROP PROCEDURE IF EXISTS `insert_truck`;
465
466DELIMITER $$
467
468/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
469CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_truck`(truck_license VARCHAR(6), driver_nm VARCHAR(50), cap INT, cap_meter VARCHAR(45))
470BEGIN
471 INSERT INTO truck (truck_id,driver_name,capacity,capacity_measure) VALUES (tank_license,driver_nm,cap,cap_meter);
472END $$
473/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
474
475DELIMITER ;
476
477--
478-- Definition of procedure `insert_user`
479--
480
481DROP PROCEDURE IF EXISTS `insert_user`;
482
483DELIMITER $$
484
485/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
486CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_user`(user_nm VARCHAR(50),user_ps VARCHAR(50), user_t INT)
487BEGIN
488 INSERT INTO user(username,user_pass,user_type) VALUES (user_nm,user_ps,user_t);
489END $$
490/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
491
492DELIMITER ;
493
494--
495-- Definition of procedure `query_calltime`
496--
497
498DROP PROCEDURE IF EXISTS `query_calltime`;
499
500DELIMITER $$
501
502/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
503CREATE DEFINER=`root`@`localhost` PROCEDURE `query_calltime`(call_dt DATETIME)
504BEGIN
505 SELECT
506 phone_call.call_id,
507 phone_call.call_date,
508 phone_call.status,
509 phone_call.notes,
510 tank_location.description
511FROM phone_call
512Inner Join tank_location ON tank_location.tank_location_id = phone_call.location_id
513WHERE phone_call.call_date = call_dt;
514END $$
515/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
516
517DELIMITER ;
518
519--
520-- Definition of procedure `query_date`
521--
522
523DROP PROCEDURE IF EXISTS `query_date`;
524
525DELIMITER $$
526
527/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
528CREATE DEFINER=`root`@`localhost` PROCEDURE `query_date`(dt DATETIME)
529BEGIN
530 SELECT
531 delivery.delivery_id,
532 delivery.delivery_date,
533 tank_location.description,
534 truck.driver_name,
535 truck.capacity,
536 truck.capacity_measure,
537 tank.tank_id,
538 tank.capacity,
539 tank.capacity_measure
540FROM
541delivery
542Inner Join truck ON truck.truck_id = delivery.truck_id
543Inner Join tank_location ON tank_location.tank_location_id = delivery.location_id
544Inner Join tank ON tank.tank_id = delivery.tank_id
545WHERE delivery.delivery_date = dt;
546
547END $$
548/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
549
550DELIMITER ;
551
552--
553-- Definition of procedure `query_daterange`
554--
555
556DROP PROCEDURE IF EXISTS `query_daterange`;
557
558DELIMITER $$
559
560/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
561CREATE DEFINER=`root`@`localhost` PROCEDURE `query_daterange`()
562BEGIN
563select timediff(`delivery`.`delivery_date`,current_timestamp()) AS `delivery_date`
564from ((`delivery` join `truck` on((`truck`.`truck_id` = `delivery`.`truck_id`))) join `tank_location` on((`tank_location`.`tank_location_id` = `delivery`.`location_id`)))
565WHERE
566delivery.delivery_date BETWEEN current_timestamp() AND delivery.delivery_date
567order by `delivery`.`delivery_date` DESC LIMIT 1;
568
569END $$
570/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
571
572DELIMITER ;
573
574--
575-- Definition of procedure `query_lastdelivery`
576--
577
578DROP PROCEDURE IF EXISTS `query_lastdelivery`;
579
580DELIMITER $$
581
582/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
583CREATE DEFINER=`root`@`localhost` PROCEDURE `query_lastdelivery`()
584BEGIN
585SELECT
586delivery.delivery_id AS delivery_id,
587delivery.delivery_date AS delivery_date,
588truck.driver_name AS driver_name,
589truck.capacity AS capacity,
590truck.capacity_measure AS capacity_measure,
591tank_location.description
592from ((`delivery` join `truck` on((`truck`.`truck_id` = `delivery`.`truck_id`))) join `tank_location` on((`tank_location`.`tank_location_id` = `delivery`.`location_id`)))
593order by `delivery`.`delivery_date` DESC LIMIT 1;
594
595END $$
596/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
597
598DELIMITER ;
599
600--
601-- Definition of procedure `query_location`
602--
603
604DROP PROCEDURE IF EXISTS `query_location`;
605
606DELIMITER $$
607
608/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
609CREATE DEFINER=`root`@`localhost` PROCEDURE `query_location`()
610BEGIN
611 SELECT * from tank_location;
612END $$
613/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
614
615DELIMITER ;
616
617--
618-- Definition of procedure `query_tank`
619--
620
621DROP PROCEDURE IF EXISTS `query_tank`;
622
623DELIMITER $$
624
625/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
626CREATE DEFINER=`root`@`localhost` PROCEDURE `query_tank`(loc_id INT)
627BEGIN
628 SELECT * from tank where location_id = loc_id;
629END $$
630/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
631
632DELIMITER ;
633
634--
635-- Definition of procedure `validate_user`
636--
637
638DROP PROCEDURE IF EXISTS `validate_user`;
639
640DELIMITER $$
641
642/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
643CREATE DEFINER=`root`@`localhost` PROCEDURE `validate_user`(user_nm VARCHAR(50),user_ps VARCHAR(50), user_t INT)
644BEGIN
645 SELECT user_type FROM user WHERE username = um AND user_pass = user_ps;
646END $$
647/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
648
649DELIMITER ;
650
651--
652-- Definition of view `calctime`
653--
654
655DROP TABLE IF EXISTS `calctime`;
656DROP VIEW IF EXISTS `calctime`;
657CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `calctime` AS select `delivery`.`delivery_id` AS `delivery_id`,timediff(now(),`delivery`.`delivery_date`) AS `LAST_DELIVERY`,`delivery`.`delivery_date` AS `delivery_date`,`tank_location`.`description` AS `description` from (`delivery` join `tank_location` on((`tank_location`.`tank_location_id` = `delivery`.`location_id`)));
658
659--
660-- Definition of view `last_call_time`
661--
662
663DROP TABLE IF EXISTS `last_call_time`;
664DROP VIEW IF EXISTS `last_call_time`;
665CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `last_call_time` AS select `phone_call`.`call_id` AS `call_id`,`tank_location`.`description` AS `description`,`phone_call`.`call_date` AS `call_date`,`phone_call`.`notes` AS `notes`,`status`.`description` AS `status` from ((`phone_call` join `tank_location` on((`tank_location`.`tank_location_id` = `phone_call`.`location_id`))) join `status` on((`status`.`status_id` = `phone_call`.`status`)));
666
667--
668-- Definition of view `last_delivery`
669--
670
671DROP TABLE IF EXISTS `last_delivery`;
672DROP VIEW IF EXISTS `last_delivery`;
673CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `last_delivery` AS select `delivery`.`delivery_id` AS `delivery_id`,`delivery`.`delivery_date` AS `delivery_date`,`truck`.`driver_name` AS `driver_name`,`truck`.`capacity` AS `capacity`,`truck`.`capacity_measure` AS `capacity_measure`,`tank_location`.`description` AS `description` from ((`delivery` join `truck` on((`truck`.`truck_id` = `delivery`.`truck_id`))) join `tank_location` on((`tank_location`.`tank_location_id` = `delivery`.`location_id`)));
674
675--
676-- Definition of view `tank_cap`
677--
678
679DROP TABLE IF EXISTS `tank_cap`;
680DROP VIEW IF EXISTS `tank_cap`;
681CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `tank_cap` AS select `tank`.`tank_id` AS `tank_id`,`tank`.`capacity` AS `capacity`,`tank`.`capacity_measure` AS `capacity_measure`,`tank_location`.`description` AS `description` from (`tank` join `tank_location` on((`tank_location`.`tank_location_id` = `tank`.`location_id`)));
682
683--
684-- Definition of view `tank_list`
685--
686
687DROP TABLE IF EXISTS `tank_list`;
688DROP VIEW IF EXISTS `tank_list`;
689CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `tank_list` AS select `location`.`location_code` AS `location_code`,`tank_location`.`description` AS `description`,`tank`.`capacity` AS `capacity`,`tank`.`capacity_measure` AS `capacity_measure` from ((`tank_location` join `tank` on((`tank_location`.`tank_location_id` = `tank`.`location_id`))) join `location` on((`location`.`location_id` = `tank_location`.`location_id`)));
690
691
692
693/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
694/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
695/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
696/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
697/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
698/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
699/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;