· last year · Mar 26, 2024, 03:55 AM
1-- MySQL dump 10.13 Distrib 8.0.19, for Win64 (x86_64)
2--
3-- Host: localhost Database: shipment_management
4-- ------------------------------------------------------
5-- Server version 8.2.0
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/*!50503 SET NAMES utf8mb4 */;
11/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
12/*!40103 SET TIME_ZONE='+00:00' */;
13/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
14/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
15/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
16/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
17
18--
19-- Table structure for table `cities`
20--
21
22DROP TABLE IF EXISTS `cities`;
23/*!40101 SET @saved_cs_client = @@character_set_client */;
24/*!50503 SET character_set_client = utf8mb4 */;
25CREATE TABLE `cities` (
26 `id` int NOT NULL AUTO_INCREMENT,
27 `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
28 `metric` int NOT NULL,
29 PRIMARY KEY (`id`)
30) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
31/*!40101 SET character_set_client = @saved_cs_client */;
32
33--
34-- Dumping data for table `cities`
35--
36
37LOCK TABLES `cities` WRITE;
38/*!40000 ALTER TABLE `cities` DISABLE KEYS */;
39INSERT INTO `cities` VALUES (1,'Jakarta',0),(6,'Bandung',2),(8,'Semarang',6),(9,'Surabaya',10),(16,'Medan',-8);
40/*!40000 ALTER TABLE `cities` ENABLE KEYS */;
41UNLOCK TABLES;
42
43--
44-- Table structure for table `customers`
45--
46
47DROP TABLE IF EXISTS `customers`;
48/*!40101 SET @saved_cs_client = @@character_set_client */;
49/*!50503 SET character_set_client = utf8mb4 */;
50CREATE TABLE `customers` (
51 `id` int NOT NULL AUTO_INCREMENT,
52 `email` varchar(255) DEFAULT NULL,
53 `phone` varchar(20) DEFAULT NULL,
54 `fullname` varchar(100) DEFAULT NULL,
55 `return_address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
56 `password` varchar(100) NOT NULL,
57 `username` varchar(100) NOT NULL,
58 PRIMARY KEY (`id`),
59 UNIQUE KEY `customers_unique` (`username`),
60 UNIQUE KEY `customers_unique_1` (`email`)
61) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
62/*!40101 SET character_set_client = @saved_cs_client */;
63
64--
65-- Dumping data for table `customers`
66--
67
68LOCK TABLES `customers` WRITE;
69/*!40000 ALTER TABLE `customers` DISABLE KEYS */;
70INSERT INTO `customers` VALUES (1,'dummy@sms.id','112','customer dummy','-','b5a2c96250612366ea272ffac6d9744aaf4b45aacd96aa7cfcb931ee3b558259','dummy'),(6,'anya@sms.id','082257291200','Anya Fachri','Jl. Mantap Gaming','33939d07e25f54e6432ad3b382e8d3d9e68522b6c3ef868f5c00410308fb6805','anyafachri');
71/*!40000 ALTER TABLE `customers` ENABLE KEYS */;
72UNLOCK TABLES;
73
74--
75-- Table structure for table `delivery_requests`
76--
77
78DROP TABLE IF EXISTS `delivery_requests`;
79/*!40101 SET @saved_cs_client = @@character_set_client */;
80/*!50503 SET character_set_client = utf8mb4 */;
81CREATE TABLE `delivery_requests` (
82 `id` int NOT NULL AUTO_INCREMENT,
83 `sender_name` varchar(255) DEFAULT NULL,
84 `sender_phone` varchar(20) DEFAULT NULL,
85 `sender_address` varchar(255) DEFAULT NULL,
86 `recipient_name` varchar(255) DEFAULT NULL,
87 `recipient_phone` varchar(20) DEFAULT NULL,
88 `recipient_address` varchar(255) DEFAULT NULL,
89 `weight` float DEFAULT NULL,
90 `is_fragile` tinyint(1) DEFAULT '0',
91 `description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
92 `cost` float DEFAULT NULL,
93 `request_time` datetime DEFAULT NULL,
94 `customer_id` int NOT NULL,
95 `city_origin_id` int NOT NULL,
96 `city_dest_id` int NOT NULL,
97 `pickupTimeEst` datetime DEFAULT NULL,
98 `pickupTime` datetime DEFAULT NULL,
99 `isCanceled` tinyint(1) DEFAULT '0',
100 PRIMARY KEY (`id`),
101 KEY `customer_id` (`customer_id`),
102 KEY `city_origin_id` (`city_origin_id`),
103 KEY `city_dest_id` (`city_dest_id`),
104 CONSTRAINT `delivery_requests_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`),
105 CONSTRAINT `delivery_requests_ibfk_2` FOREIGN KEY (`city_origin_id`) REFERENCES `cities` (`id`),
106 CONSTRAINT `delivery_requests_ibfk_3` FOREIGN KEY (`city_dest_id`) REFERENCES `cities` (`id`)
107) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
108/*!40101 SET character_set_client = @saved_cs_client */;
109
110--
111-- Dumping data for table `delivery_requests`
112--
113
114LOCK TABLES `delivery_requests` WRITE;
115/*!40000 ALTER TABLE `delivery_requests` DISABLE KEYS */;
116INSERT INTO `delivery_requests` VALUES (5,'Asrofil','0822','Jl. Jalan Jalan','Fachrul','1200','Jl, Leumpang',1.2,1,'Cilok',19000,'2024-03-26 01:58:15',6,1,6,NULL,NULL,0);
117/*!40000 ALTER TABLE `delivery_requests` ENABLE KEYS */;
118UNLOCK TABLES;
119
120--
121-- Table structure for table `office_types`
122--
123
124DROP TABLE IF EXISTS `office_types`;
125/*!40101 SET @saved_cs_client = @@character_set_client */;
126/*!50503 SET character_set_client = utf8mb4 */;
127CREATE TABLE `office_types` (
128 `id` int NOT NULL AUTO_INCREMENT,
129 `name` varchar(255) NOT NULL,
130 PRIMARY KEY (`id`)
131) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
132/*!40101 SET character_set_client = @saved_cs_client */;
133
134--
135-- Dumping data for table `office_types`
136--
137
138LOCK TABLES `office_types` WRITE;
139/*!40000 ALTER TABLE `office_types` DISABLE KEYS */;
140INSERT INTO `office_types` VALUES (1,'HUB'),(2,'WAREHOUSE'),(3,'DC');
141/*!40000 ALTER TABLE `office_types` ENABLE KEYS */;
142UNLOCK TABLES;
143
144--
145-- Table structure for table `package_deliveries`
146--
147
148DROP TABLE IF EXISTS `package_deliveries`;
149/*!40101 SET @saved_cs_client = @@character_set_client */;
150/*!50503 SET character_set_client = utf8mb4 */;
151CREATE TABLE `package_deliveries` (
152 `id` int NOT NULL AUTO_INCREMENT,
153 `status` varchar(100) DEFAULT NULL,
154 `delivery_req_id` int NOT NULL,
155 `staff_id` int NOT NULL,
156 PRIMARY KEY (`id`),
157 KEY `package_deliveries_delivery_requests_FK` (`delivery_req_id`),
158 KEY `package_deliveries_staff_FK` (`staff_id`),
159 CONSTRAINT `package_deliveries_delivery_requests_FK` FOREIGN KEY (`delivery_req_id`) REFERENCES `delivery_requests` (`id`) ON DELETE RESTRICT,
160 CONSTRAINT `package_deliveries_staff_FK` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`id`)
161) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
162/*!40101 SET character_set_client = @saved_cs_client */;
163
164--
165-- Dumping data for table `package_deliveries`
166--
167
168LOCK TABLES `package_deliveries` WRITE;
169/*!40000 ALTER TABLE `package_deliveries` DISABLE KEYS */;
170/*!40000 ALTER TABLE `package_deliveries` ENABLE KEYS */;
171UNLOCK TABLES;
172
173--
174-- Table structure for table `package_histories`
175--
176
177DROP TABLE IF EXISTS `package_histories`;
178/*!40101 SET @saved_cs_client = @@character_set_client */;
179/*!50503 SET character_set_client = utf8mb4 */;
180CREATE TABLE `package_histories` (
181 `id` int NOT NULL AUTO_INCREMENT,
182 `time` date DEFAULT NULL,
183 `status` varchar(255) DEFAULT NULL,
184 `location` varchar(255) DEFAULT NULL,
185 `metadata_url` varchar(255) DEFAULT NULL,
186 `package_delivery_id` int NOT NULL,
187 `staff_id` int NOT NULL,
188 PRIMARY KEY (`id`),
189 KEY `package_histories_ibfk_1` (`package_delivery_id`),
190 KEY `package_histories_staff_FK` (`staff_id`),
191 CONSTRAINT `package_histories_ibfk_1` FOREIGN KEY (`package_delivery_id`) REFERENCES `package_deliveries` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT,
192 CONSTRAINT `package_histories_staff_FK` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`id`)
193) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
194/*!40101 SET character_set_client = @saved_cs_client */;
195
196--
197-- Dumping data for table `package_histories`
198--
199
200LOCK TABLES `package_histories` WRITE;
201/*!40000 ALTER TABLE `package_histories` DISABLE KEYS */;
202/*!40000 ALTER TABLE `package_histories` ENABLE KEYS */;
203UNLOCK TABLES;
204
205--
206-- Table structure for table `staff`
207--
208
209DROP TABLE IF EXISTS `staff`;
210/*!40101 SET @saved_cs_client = @@character_set_client */;
211/*!50503 SET character_set_client = utf8mb4 */;
212CREATE TABLE `staff` (
213 `id` int NOT NULL AUTO_INCREMENT,
214 `email` varchar(255) DEFAULT NULL,
215 `phone` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '-',
216 `fullname` varchar(100) DEFAULT NULL,
217 `office_city_id` int DEFAULT NULL,
218 `office_type_id` int DEFAULT NULL,
219 `password` varchar(100) NOT NULL,
220 `username` varchar(100) NOT NULL,
221 PRIMARY KEY (`id`),
222 UNIQUE KEY `staff_unique` (`email`,`phone`,`username`),
223 KEY `staff_cities_FK` (`office_city_id`),
224 KEY `staff_office_types_FK` (`office_type_id`),
225 CONSTRAINT `staff_cities_FK` FOREIGN KEY (`office_city_id`) REFERENCES `cities` (`id`),
226 CONSTRAINT `staff_office_types_FK` FOREIGN KEY (`office_type_id`) REFERENCES `office_types` (`id`)
227) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
228/*!40101 SET character_set_client = @saved_cs_client */;
229
230--
231-- Dumping data for table `staff`
232--
233
234LOCK TABLES `staff` WRITE;
235/*!40000 ALTER TABLE `staff` DISABLE KEYS */;
236INSERT INTO `staff` VALUES (1,'admin@admin.id','-','Admin',1,2,'8c6976e5b5410415bde908bd4dee15dfb167a9c873fc4bb8a81f6f2ab448a918','admin'),(2,'admin_wh.jkt@sms.id','-','Admin JKT WH',1,2,'f149d5dfcd5032d2994a5ee49e9d164e454f90b77a651d97d4bd073c7754580d','admin_jkt_wh'),(3,'admin_hub.jkt@sms.id','-','Admin JKT Hub',1,1,'3d84828ff98cf3d3f974f118f8e5e7cc33a6c4feed7ba2f72407e00382ada458','admin_jkt_hub'),(4,'admin_hub.me@sms.id','-','Admin ME Hub',16,1,'4eaea8e3259bdf1bae0c82809b3bd98f0325098d96bc76936d6c7d63c3e35c59','admin_me_hub'),(5,'admin_wh.me@sms.id','-','Admin ME WH',16,2,'c284acc2569010d3c89f9ad6db5499996dba67a37bfe2cc06af9bc5accffc6a5','admin_me_wh'),(6,'admin_dc.me@sms.id','-','Admin ME DC',16,3,'96b5433c4589dd4a18cc17dfb4192f393fcf7daa5fbd92517effcd03ed93858c','admin_me_dc'),(7,'admin_dc.jkt@sms.id','-','Admin JKT DC',1,3,'ab1bfb6e4b72bd9dee230579b899c9634fd169665e3102b8180b3b89c008c9d8','admin_jkt_dc'),(8,'admin_hub.bdg@sms.id','-','Admin BDG Hub',6,1,'f8302f6125a5fdaec8c65621177a6ad76aea8f97ba1ccaebedf3c5acec69b195','admin_bdg_hub'),(9,'admin_wh.bdg@sms.id','-','Admin BDG WH',6,2,'0b546ed500ec8b2bf120eaf27695d8fe18cd6b8467bf566de0b49ce552cc7714','admin_bdg_wh'),(10,'admin_dc.bdg@sms.id','-','Admin BDG DC',6,3,'9d1a5ab9edbf5b79046a03c46f179867b3ae0ead2f29f7e73bf5191c1f67b65d','admin_bdg_dc'),(11,'admin_hub.sem@sms.id','-','Admin SEM Hub',8,1,'83c552203a6c1434c0cf05a3625d35df0de223f1fb09d78819745373b40ec80d','admin_sem_hub'),(12,'admin_wh.sem@sms.id','-','Admin SEM WH',8,2,'2d4807f31edc5a1c81f9c742a813d0866622e08a5c8ea3901bdaea8bed9bd7e0','admin_sem_wh'),(13,'admin_dc.sem@sms.id','-','Admin SEM DC',8,3,'91c58ecc66b4141e7e58011dc7f3e2a99c8c58153df60462f62bdf21587cb617','admin_sem_dc'),(14,'admin_hub.sby@sms.id','-','Admin SBY Hub',9,1,'a95005af58ca8f2fe4690fba49ace4bf6e2cfb3d7fd6c48009d08ab6a8ad43de','admin_sby_hub'),(15,'admin_wh.sby@sms.id','-','Admin SBY WH',9,2,'f180d0cba49b8b1832f88f05b7fa0c6a2fd3005b328e2bb87eb0bd9c07a2e347','admin_sby_wh'),(16,'admin_dc.sby@sms.id','-','Admin SBY DC',9,3,'092c5d840440bee5e8fb8b57e320c134d8540ccb12e75d37fc3a2bc1dc447a99','admin_sby_dc');
237/*!40000 ALTER TABLE `staff` ENABLE KEYS */;
238UNLOCK TABLES;
239
240--
241-- Dumping routines for database 'shipment_management'
242--
243/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
244
245/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
246/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
247/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
248/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
249/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
250/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
251/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
252
253-- Dump completed on 2024-03-26 2:00:32
254