· 9 years ago · May 02, 2017, 09:40 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=6 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 (5,'2010-02-05 17:51:50','TR234',2325,'43434');
136/*!40000 ALTER TABLE `delivery` ENABLE KEYS */;
137
138
139--
140-- Definition of table `location`
141--
142
143DROP TABLE IF EXISTS `location`;
144CREATE TABLE `location` (
145 `location_id` int(11) NOT NULL AUTO_INCREMENT,
146 `location_code` varchar(45) NOT NULL,
147 PRIMARY KEY (`location_id`)
148) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
149
150--
151-- Dumping data for table `location`
152--
153
154/*!40000 ALTER TABLE `location` DISABLE KEYS */;
155INSERT INTO `location` (`location_id`,`location_code`) VALUES
156 (1,'Mary Secole'),
157 (2,'Taylor'),
158 (3,'Rex'),
159 (4,'Preston'),
160 (5,'Chanc');
161/*!40000 ALTER TABLE `location` ENABLE KEYS */;
162
163
164--
165-- Definition of table `phone_call`
166--
167
168DROP TABLE IF EXISTS `phone_call`;
169CREATE TABLE `phone_call` (
170 `call_id` int(11) NOT NULL AUTO_INCREMENT,
171 `location_id` int(11) NOT NULL,
172 `call_date` datetime NOT NULL,
173 `status` int(11) DEFAULT NULL,
174 `notes` varchar(500) DEFAULT NULL,
175 PRIMARY KEY (`call_id`),
176 KEY `call_loc_id` (`location_id`),
177 KEY `stat` (`status`),
178 CONSTRAINT `call_loc_id` FOREIGN KEY (`location_id`) REFERENCES `tank_location` (`tank_location_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
179 CONSTRAINT `stat` FOREIGN KEY (`status`) REFERENCES `status` (`status_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
180) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;
181
182--
183-- Dumping data for table `phone_call`
184--
185
186/*!40000 ALTER TABLE `phone_call` DISABLE KEYS */;
187INSERT INTO `phone_call` (`call_id`,`location_id`,`call_date`,`status`,`notes`) VALUES
188 (1,2324,'2010-02-05 02:41:36',1,NULL),
189 (12,2325,'2010-02-12 03:08:45',2,NULL);
190/*!40000 ALTER TABLE `phone_call` ENABLE KEYS */;
191
192
193--
194-- Definition of table `status`
195--
196
197DROP TABLE IF EXISTS `status`;
198CREATE TABLE `status` (
199 `status_id` int(11) NOT NULL AUTO_INCREMENT,
200 `description` varchar(45) NOT NULL,
201 PRIMARY KEY (`status_id`)
202) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
203
204--
205-- Dumping data for table `status`
206--
207
208/*!40000 ALTER TABLE `status` DISABLE KEYS */;
209INSERT INTO `status` (`status_id`,`description`) VALUES
210 (1,'empty'),
211 (2,'full');
212/*!40000 ALTER TABLE `status` ENABLE KEYS */;
213
214
215--
216-- Definition of table `tank`
217--
218
219DROP TABLE IF EXISTS `tank`;
220CREATE TABLE `tank` (
221 `tank_id` varchar(10) NOT NULL,
222 `location_id` int(11) NOT NULL,
223 `capacity` int(11) NOT NULL,
224 `capacity_measure` varchar(45) NOT NULL,
225 PRIMARY KEY (`tank_id`),
226 KEY `tank_loc_id` (`location_id`),
227 CONSTRAINT `tank_loc_id` FOREIGN KEY (`location_id`) REFERENCES `tank_location` (`tank_location_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
228) ENGINE=InnoDB DEFAULT CHARSET=latin1;
229
230--
231-- Dumping data for table `tank`
232--
233
234/*!40000 ALTER TABLE `tank` DISABLE KEYS */;
235INSERT INTO `tank` (`tank_id`,`location_id`,`capacity`,`capacity_measure`) VALUES
236 ('23223',2325,23232,'M'),
237 ('43434',2324,232323,'L');
238/*!40000 ALTER TABLE `tank` ENABLE KEYS */;
239
240
241--
242-- Definition of table `tank_location`
243--
244
245DROP TABLE IF EXISTS `tank_location`;
246CREATE TABLE `tank_location` (
247 `tank_location_id` int(11) NOT NULL AUTO_INCREMENT,
248 `location_id` int(11) NOT NULL,
249 `description` varchar(100) NOT NULL,
250 PRIMARY KEY (`tank_location_id`),
251 KEY `tank_loc` (`location_id`),
252 CONSTRAINT `tank_loc` FOREIGN KEY (`location_id`) REFERENCES `location` (`location_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
253) ENGINE=InnoDB AUTO_INCREMENT=2326 DEFAULT CHARSET=latin1;
254
255--
256-- Dumping data for table `tank_location`
257--
258
259/*!40000 ALTER TABLE `tank_location` DISABLE KEYS */;
260INSERT INTO `tank_location` (`tank_location_id`,`location_id`,`description`) VALUES
261 (1,1,'2 Floor'),
262 (2324,1,'Front Gate'),
263 (2325,2,'Roosters');
264/*!40000 ALTER TABLE `tank_location` ENABLE KEYS */;
265
266
267--
268-- Definition of table `truck`
269--
270
271DROP TABLE IF EXISTS `truck`;
272CREATE TABLE `truck` (
273 `truck_id` varchar(6) NOT NULL,
274 `driver_name` varchar(45) DEFAULT NULL,
275 `capacity` int(11) DEFAULT NULL,
276 `capacity_measure` varchar(45) DEFAULT NULL,
277 PRIMARY KEY (`truck_id`)
278) ENGINE=InnoDB DEFAULT CHARSET=latin1;
279
280--
281-- Dumping data for table `truck`
282--
283
284/*!40000 ALTER TABLE `truck` DISABLE KEYS */;
285INSERT INTO `truck` (`truck_id`,`driver_name`,`capacity`,`capacity_measure`) VALUES
286 ('FT322','jim brown',22221,'M'),
287 ('ST453','andre pink',22556,'M'),
288 ('TR123','john brown',232323,'CM'),
289 ('TR234','joe black',22222,'G'),
290 ('WQ232','daine k',3434322,'L');
291/*!40000 ALTER TABLE `truck` ENABLE KEYS */;
292
293
294--
295-- Definition of table `user`
296--
297
298DROP TABLE IF EXISTS `user`;
299CREATE TABLE `user` (
300 `user_id` int(11) NOT NULL AUTO_INCREMENT,
301 `username` varchar(45) NOT NULL,
302 `user_pass` varchar(45) NOT NULL,
303 `user_type` int(11) NOT NULL,
304 PRIMARY KEY (`user_id`),
305 KEY `u_type` (`user_type`),
306 CONSTRAINT `u_type` FOREIGN KEY (`user_type`) REFERENCES `user_type` (`usertype_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
307) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
308
309--
310-- Dumping data for table `user`
311--
312
313/*!40000 ALTER TABLE `user` DISABLE KEYS */;
314INSERT INTO `user` (`user_id`,`username`,`user_pass`,`user_type`) VALUES
315 (1,'admin','password',1),
316 (2,'test','test',2);
317/*!40000 ALTER TABLE `user` ENABLE KEYS */;
318
319
320--
321-- Definition of table `user_type`
322--
323
324DROP TABLE IF EXISTS `user_type`;
325CREATE TABLE `user_type` (
326 `usertype_id` int(11) NOT NULL,
327 `description` varchar(45) NOT NULL,
328 PRIMARY KEY (`usertype_id`)
329) ENGINE=InnoDB DEFAULT CHARSET=latin1;
330
331--
332-- Dumping data for table `user_type`
333--
334
335/*!40000 ALTER TABLE `user_type` DISABLE KEYS */;
336INSERT INTO `user_type` (`usertype_id`,`description`) VALUES
337 (1,'admin'),
338 (2,'normal');
339/*!40000 ALTER TABLE `user_type` ENABLE KEYS */;
340
341
342--
343-- Definition of procedure `get_delivery`
344--
345
346DROP PROCEDURE IF EXISTS `get_delivery`;
347
348DELIMITER $$
349
350/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
351CREATE DEFINER=`root`@`localhost` PROCEDURE `get_delivery`()
352BEGIN
353 SELECT * FROM delivery;
354END $$
355/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
356
357DELIMITER ;
358
359--
360-- Definition of procedure `get_pass`
361--
362
363DROP PROCEDURE IF EXISTS `get_pass`;
364
365DELIMITER $$
366
367/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
368CREATE DEFINER=`root`@`localhost` PROCEDURE `get_pass`(user_nm VARCHAR(50))
369BEGIN
370 SELECT user_pass FROM user WHERE username = user_um ;
371END $$
372/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
373
374DELIMITER ;
375
376--
377-- Definition of procedure `get_tank`
378--
379
380DROP PROCEDURE IF EXISTS `get_tank`;
381
382DELIMITER $$
383
384/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
385CREATE DEFINER=`root`@`localhost` PROCEDURE `get_tank`()
386BEGIN
387 SELECT * from tank;
388END $$
389/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
390
391DELIMITER ;
392
393--
394-- Definition of procedure `get_truck`
395--
396
397DROP PROCEDURE IF EXISTS `get_truck`;
398
399DELIMITER $$
400
401/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
402CREATE DEFINER=`root`@`localhost` PROCEDURE `get_truck`()
403BEGIN
404 SELECT * FROM truck;
405END $$
406/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
407
408DELIMITER ;
409
410--
411-- Definition of procedure `insert_delivery`
412--
413
414DROP PROCEDURE IF EXISTS `insert_delivery`;
415
416DELIMITER $$
417
418/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
419CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_delivery`(delivery_dt DATETIME, truck_num VARCHAR(6),loc_id INT,tank_num VARCHAR(6))
420BEGIN
421 INSERT INTO delivery (delivery_date,truck_id,location_id,tank_id) VALUES (delivery_dt,truck_num,loc_id,tank_num);
422END $$
423/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
424
425DELIMITER ;
426
427--
428-- Definition of procedure `insert_location`
429--
430
431DROP PROCEDURE IF EXISTS `insert_location`;
432
433DELIMITER $$
434
435/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
436CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_location`(loc_code VARCHAR(50))
437BEGIN
438 INSERT INTO location (location_code) VALUES (loc_code);
439END $$
440/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
441
442DELIMITER ;
443
444--
445-- Definition of procedure `insert_phone_call`
446--
447
448DROP PROCEDURE IF EXISTS `insert_phone_call`;
449
450DELIMITER $$
451
452/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
453CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_phone_call`(loc_id INT,call_dt DATETIME, stat INT, nt VARCHAR(500))
454BEGIN
455 INSERT INTO phone_call(location_id,call_date,status,notes) VALUES (loc_id,call_dt,stat,nt);
456END $$
457/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
458
459DELIMITER ;
460
461--
462-- Definition of procedure `insert_tank`
463--
464
465DROP PROCEDURE IF EXISTS `insert_tank`;
466
467DELIMITER $$
468
469/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
470CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_tank`(tank_num VARCHAR(10), loc_id INT, cap INT, cap_meter VARCHAR(45))
471BEGIN
472 INSERT INTO tank (tank_id,location_id,capacity,capacity_measure) VALUES (tank_num,loc_id,cap,cap_meter);
473END $$
474/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
475
476DELIMITER ;
477
478--
479-- Definition of procedure `insert_tank_location`
480--
481
482DROP PROCEDURE IF EXISTS `insert_tank_location`;
483
484DELIMITER $$
485
486/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
487CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_tank_location`(loc_id INT, descript VARCHAR(100))
488BEGIN
489 INSERT INTO tank_location (location_id,description) VALUES (loc_id,descript);
490END $$
491/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
492
493DELIMITER ;
494
495--
496-- Definition of procedure `insert_truck`
497--
498
499DROP PROCEDURE IF EXISTS `insert_truck`;
500
501DELIMITER $$
502
503/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
504CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_truck`(truck_license VARCHAR(6), driver_nm VARCHAR(50), cap INT, cap_meter VARCHAR(45))
505BEGIN
506 INSERT INTO truck (truck_id,driver_name,capacity,capacity_measure) VALUES (tank_license,driver_nm,cap,cap_meter);
507END $$
508/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
509
510DELIMITER ;
511
512--
513-- Definition of procedure `insert_user`
514--
515
516DROP PROCEDURE IF EXISTS `insert_user`;
517
518DELIMITER $$
519
520/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
521CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_user`(user_nm VARCHAR(50),user_ps VARCHAR(50), user_t INT)
522BEGIN
523 INSERT INTO user(username,user_pass,user_type) VALUES (user_nm,user_ps,user_t);
524END $$
525/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
526
527DELIMITER ;
528
529--
530-- Definition of procedure `query_calltime`
531--
532
533DROP PROCEDURE IF EXISTS `query_calltime`;
534
535DELIMITER $$
536
537/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
538CREATE DEFINER=`root`@`localhost` PROCEDURE `query_calltime`(call_dt DATETIME)
539BEGIN
540 SELECT
541 phone_call.call_id,
542 phone_call.call_date,
543 phone_call.status,
544 phone_call.notes,
545 tank_location.description
546FROM phone_call
547Inner Join tank_location ON tank_location.tank_location_id = phone_call.location_id
548WHERE phone_call.call_date = call_dt;
549END $$
550/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
551
552DELIMITER ;
553
554--
555-- Definition of procedure `query_date`
556--
557
558DROP PROCEDURE IF EXISTS `query_date`;
559
560DELIMITER $$
561
562/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
563CREATE DEFINER=`root`@`localhost` PROCEDURE `query_date`(dt DATETIME)
564BEGIN
565 SELECT
566 delivery.delivery_id,
567 delivery.delivery_date,
568 tank_location.description,
569 truck.driver_name,
570 truck.capacity,
571 truck.capacity_measure,
572 tank.tank_id,
573 tank.capacity,
574 tank.capacity_measure
575FROM
576delivery
577Inner Join truck ON truck.truck_id = delivery.truck_id
578Inner Join tank_location ON tank_location.tank_location_id = delivery.location_id
579Inner Join tank ON tank.tank_id = delivery.tank_id
580WHERE delivery.delivery_date = dt;
581
582END $$
583/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
584
585DELIMITER ;
586
587--
588-- Definition of procedure `query_daterange`
589--
590
591DROP PROCEDURE IF EXISTS `query_daterange`;
592
593DELIMITER $$
594
595/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
596CREATE DEFINER=`root`@`localhost` PROCEDURE `query_daterange`()
597BEGIN
598select timediff(`delivery`.`delivery_date`,current_timestamp()) AS `delivery_date`
599from ((`delivery` join `truck` on((`truck`.`truck_id` = `delivery`.`truck_id`))) join `tank_location` on((`tank_location`.`tank_location_id` = `delivery`.`location_id`)))
600WHERE
601delivery.delivery_date BETWEEN current_timestamp() AND delivery.delivery_date
602order by `delivery`.`delivery_date` DESC LIMIT 1;
603
604END $$
605/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
606
607DELIMITER ;
608
609--
610-- Definition of procedure `query_lastdelivery`
611--
612
613DROP PROCEDURE IF EXISTS `query_lastdelivery`;
614
615DELIMITER $$
616
617/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
618CREATE DEFINER=`root`@`localhost` PROCEDURE `query_lastdelivery`()
619BEGIN
620SELECT
621delivery.delivery_id AS delivery_id,
622delivery.delivery_date AS delivery_date,
623truck.driver_name AS driver_name,
624truck.capacity AS capacity,
625truck.capacity_measure AS capacity_measure,
626tank_location.description
627from ((`delivery` join `truck` on((`truck`.`truck_id` = `delivery`.`truck_id`))) join `tank_location` on((`tank_location`.`tank_location_id` = `delivery`.`location_id`)))
628order by `delivery`.`delivery_date` DESC LIMIT 1;
629
630END $$
631/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
632
633DELIMITER ;
634
635--
636-- Definition of procedure `query_location`
637--
638
639DROP PROCEDURE IF EXISTS `query_location`;
640
641DELIMITER $$
642
643/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
644CREATE DEFINER=`root`@`localhost` PROCEDURE `query_location`()
645BEGIN
646 SELECT * from location;
647END $$
648/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
649
650DELIMITER ;
651
652--
653-- Definition of procedure `query_tank`
654--
655
656DROP PROCEDURE IF EXISTS `query_tank`;
657
658DELIMITER $$
659
660/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
661CREATE DEFINER=`root`@`localhost` PROCEDURE `query_tank`(loc_id INT)
662BEGIN
663 SELECT * from tank where location_id = loc_id;
664END $$
665/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
666
667DELIMITER ;
668
669--
670-- Definition of procedure `query_truck`
671--
672
673DROP PROCEDURE IF EXISTS `query_truck`;
674
675DELIMITER $$
676
677/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
678CREATE DEFINER=`root`@`localhost` PROCEDURE `query_truck`()
679BEGIN
680 SELECT * FROM truck;
681END $$
682/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
683
684DELIMITER ;
685
686--
687-- Definition of procedure `validate_user`
688--
689
690DROP PROCEDURE IF EXISTS `validate_user`;
691
692DELIMITER $$
693
694/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
695CREATE DEFINER=`root`@`localhost` PROCEDURE `validate_user`(user_nm VARCHAR(50),user_ps VARCHAR(50), user_t INT)
696BEGIN
697 SELECT user_type FROM user WHERE username = um AND user_pass = user_ps;
698END $$
699/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
700
701DELIMITER ;
702
703--
704-- Definition of view `calctime`
705--
706
707DROP TABLE IF EXISTS `calctime`;
708DROP VIEW IF EXISTS `calctime`;
709CREATE 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`)));
710
711--
712-- Definition of view `last_call_time`
713--
714
715DROP TABLE IF EXISTS `last_call_time`;
716DROP VIEW IF EXISTS `last_call_time`;
717CREATE 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`)));
718
719--
720-- Definition of view `last_delivery`
721--
722
723DROP TABLE IF EXISTS `last_delivery`;
724DROP VIEW IF EXISTS `last_delivery`;
725CREATE 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`)));
726
727--
728-- Definition of view `tank_cap`
729--
730
731DROP TABLE IF EXISTS `tank_cap`;
732DROP VIEW IF EXISTS `tank_cap`;
733CREATE 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`)));
734
735--
736-- Definition of view `tank_list`
737--
738
739DROP TABLE IF EXISTS `tank_list`;
740DROP VIEW IF EXISTS `tank_list`;
741CREATE 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`)));
742
743
744
745/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
746/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
747/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
748/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
749/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
750/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
751/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;