-
Notifications
You must be signed in to change notification settings - Fork 0
/
projectx.sql
258 lines (238 loc) · 16.9 KB
/
projectx.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
/*
Navicat Premium Data Transfer
Source Server : localhost
Source Server Type : MariaDB
Source Server Version : 100148
Source Host : localhost:3306
Source Schema : projectx
Target Server Type : MariaDB
Target Server Version : 100148
File Encoding : 65001
Date: 28/07/2022 00:16:12
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for ClearingCompany
-- ----------------------------
DROP TABLE IF EXISTS `ClearingCompany`;
CREATE TABLE `ClearingCompany` (
`clearingCompany_id` int(11) NOT NULL AUTO_INCREMENT,
`clearingCompany_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '清運公司名稱',
`clearingCompany_uniformNumbers` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '清運公司統編',
`clearingCompany_principalName` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '負責人姓名',
`clearingCompany_identityCard` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '身分證號碼',
`clearingCompany_phone` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '清運公司電話',
`clearingCompany_address` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '清運公司地址',
`user_id` int(11) NOT NULL COMMENT '使用者id',
`permission_id` int(11) NOT NULL COMMENT '權限id',
`created_at` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP,
`updated_date` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP(0),
PRIMARY KEY (`clearingCompany_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Table structure for ClearingDriver
-- ----------------------------
DROP TABLE IF EXISTS `ClearingDriver`;
CREATE TABLE `ClearingDriver` (
`clearingDriver_id` int(11) NOT NULL AUTO_INCREMENT,
`clearingDriver_name` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '駕駛姓名',
`clearingDriver_identityCard` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '駕駛身分證號碼',
`clearingDriver_licensePlate` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '清運司機車牌',
`clearingDriver_phone` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '清運司機連絡電話',
`clearingDriver_bloodType` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '清運司機血型',
`clearingCompany_id` int(10) NOT NULL COMMENT '清運公司外來鍵',
`user_id` int(11) NOT NULL COMMENT '使用者id',
`permission_id` int(11) NOT NULL COMMENT '權限id',
`created_at` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP,
`updated_date` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP(0),
PRIMARY KEY (`clearingDriver_id`) USING BTREE,
INDEX `ClearingDriver_clearingCompany_id_foreign`(`clearingCompany_id`) USING BTREE,
CONSTRAINT `ClearingDriver_clearingCompany_id_foreign` FOREIGN KEY (`clearingCompany_id`) REFERENCES `ClearingCompany` (`clearingCompany_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Table structure for ContainmentCompany
-- ----------------------------
DROP TABLE IF EXISTS `ContainmentCompany`;
CREATE TABLE `ContainmentCompany` (
`containmentCompany_id` int(11) NOT NULL AUTO_INCREMENT,
`containmentCompany_uniformNumbers` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '收容公司統編',
`containmentCompany_name` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '收容公司名稱',
`containmentCompany_principalName` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '負責人姓名',
`containmentCompany_principalPhone` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '負責人電話',
`containmentCompany_placeAddress` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '收容公司場所地址',
`containmentCompany_address` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '收容公司地址',
`user_id` int(11) NOT NULL COMMENT '使用者id',
`permission_id` int(11) NOT NULL COMMENT '權限id',
`created_at` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP,
`updated_date` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP(0),
PRIMARY KEY (`containmentCompany_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Table structure for ContractingCompany
-- ----------------------------
DROP TABLE IF EXISTS `ContractingCompany`;
CREATE TABLE `ContractingCompany` (
`contracting_id` int(11) NOT NULL AUTO_INCREMENT,
`contracting_companyName` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '承造公司名稱',
`contracting_uniformNumbers` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '承造公司統編',
`contracting_contractUserName` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '承造人姓名',
`contracting_contractUserPhone` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '承造人電話',
`contracting_contractWatcherName` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '監造人姓名',
`contracting_contractWatcherPhone` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '監造人電話',
`contracting_companyAddress` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '承造公司地址',
`user_id` int(11) NOT NULL COMMENT '使用者id',
`permission_id` int(11) NOT NULL COMMENT '權限id',
`created_at` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP,
`updated_date` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP(0),
PRIMARY KEY (`contracting_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Table structure for EngineeringManagement
-- ----------------------------
DROP TABLE IF EXISTS `EngineeringManagement`;
CREATE TABLE `EngineeringManagement` (
`engineering_id` int(11) NOT NULL AUTO_INCREMENT,
`engineering_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '工程名稱',
`engineering_projectNumber` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '工程流向編號',
`contractCompany_id` int(11) NOT NULL COMMENT '承造公司外來鍵',
`created_at` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP,
`updated_date` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP(0),
PRIMARY KEY (`engineering_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Table structure for Government
-- ----------------------------
DROP TABLE IF EXISTS `Government`;
CREATE TABLE `Government` (
`government_id` int(11) NOT NULL AUTO_INCREMENT,
`government_name` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '政府單位名稱',
`government_principalName` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '政府單位負責人姓名',
`government_principalPhone` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '政府單位負責人電話',
`government_address` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '政府單位地址',
`user_id` int(11) NOT NULL COMMENT '使用者id',
`permission_id` int(11) NOT NULL COMMENT '權限id',
`created_at` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP,
`updated_date` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP(0),
PRIMARY KEY (`government_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Table structure for PdfDocument
-- ----------------------------
DROP TABLE IF EXISTS `PdfDocument`;
CREATE TABLE `PdfDocument` (
`pdf_id` int(11) NOT NULL AUTO_INCREMENT,
`pdf_fileNumber` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '文件序號',
`pdf_effectiveDate` date NULL DEFAULT NULL COMMENT '文件有效日期',
`pdf_buildingName` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '建物或拆除物名稱',
`pdf_constructNumber` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '建造號碼',
`pdf_buildingAddress` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '建築物地址',
`pdf_starterName` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '起造人姓名',
`pdf_starterPhone` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '起造人電話',
`pdf_contractingCompanyId` int(11) NOT NULL COMMENT '承造公司外來鍵',
`pdf_clearingDriverId` int(11) NOT NULL COMMENT '清運司機外來鍵',
`pdf_clearingCompanyId` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '清運公司外來鍵',
`pdf_transportationRoute` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '運輸路線',
`pdf_shippingQuantity` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '土石載運數量',
`pdf_shippingContents` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '載運內容',
`pdf_containmentCompanyId` int(11) NOT NULL COMMENT '收容單位外來鍵',
`pdf_containmentPlaceEearthFlowNumer` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '收容場所土石流向管制編號',
`pdf_certifiedDocumentsIssuingUnit` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '證明文件核發單位',
`pdf_contractingSign` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '承造公司簽名圖片位置',
`pdf_driverSign` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '駕駛簽名圖片位置',
`pdf_containmentPlaceSign` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '收容場所圖片位置',
`pdf_contractingSignDate` datetime(0) NULL DEFAULT NULL COMMENT '承造簽名日期',
`pdf_driverSignDate` datetime(0) NULL DEFAULT NULL COMMENT '駕駛簽名日期',
`pdf_containmentPlaceSignDate` datetime(0) NULL DEFAULT NULL COMMENT '收容場所簽名日期',
`status_id` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '文件狀態外來鍵',
`engineering_id` int(11) NOT NULL COMMENT '工程管理外來鍵',
`pdf_carFront` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '車頭圖片',
`pdf_carBody` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '車斗圖片',
`created_at` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP,
`updated_date` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP(0),
PRIMARY KEY (`pdf_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Table structure for PdfStatus
-- ----------------------------
DROP TABLE IF EXISTS `PdfStatus`;
CREATE TABLE `PdfStatus` (
`status_id` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '狀態編號',
`status_remark` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '狀態備註',
`created_at` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP,
`updated_date` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP(0),
PRIMARY KEY (`status_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Records of PdfStatus
-- ----------------------------
INSERT INTO `PdfStatus` VALUES ('1', '創建完成', '2022-07-28 00:15:52', '2022-07-28 00:15:52');
INSERT INTO `PdfStatus` VALUES ('2', '承造簽名完成', '2022-07-28 00:15:52', '2022-07-28 00:15:52');
INSERT INTO `PdfStatus` VALUES ('3', '司機簽名完成', '2022-07-28 00:15:52', '2022-07-28 00:15:52');
INSERT INTO `PdfStatus` VALUES ('4', '收容簽名完成', '2022-07-28 00:15:52', '2022-07-28 00:15:52');
INSERT INTO `PdfStatus` VALUES ('5', '簽名完畢', '2022-07-28 00:15:52', '2022-07-28 00:15:52');
-- ----------------------------
-- Table structure for Permission
-- ----------------------------
DROP TABLE IF EXISTS `Permission`;
CREATE TABLE `Permission` (
`permission_id` int(11) NOT NULL,
`permission_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '權限名稱',
`created_at` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP,
`updated_date` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP(0),
PRIMARY KEY (`permission_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Records of Permission
-- ----------------------------
INSERT INTO `Permission` VALUES (1, 'root', '2022-07-28 00:15:52', '2022-07-28 00:15:52');
INSERT INTO `Permission` VALUES (2, '承造廠商(公司)', '2022-07-28 00:15:52', '2022-07-28 00:15:52');
INSERT INTO `Permission` VALUES (3, '清運廠商(公司)', '2022-07-28 00:15:52', '2022-07-28 00:15:52');
INSERT INTO `Permission` VALUES (4, '清運司機', '2022-07-28 00:15:52', '2022-07-28 00:15:52');
INSERT INTO `Permission` VALUES (5, '收容場所', '2022-07-28 00:15:52', '2022-07-28 00:15:52');
INSERT INTO `Permission` VALUES (6, '政府單位', '2022-07-28 00:15:52', '2022-07-28 00:15:52');
-- ----------------------------
-- Table structure for User
-- ----------------------------
DROP TABLE IF EXISTS `User`;
CREATE TABLE `User` (
`user_id` int(11) NOT NULL AUTO_INCREMENT,
`user_email` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '使用者帳號',
`user_password` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '使用者密碼',
`permission_id` int(11) NOT NULL COMMENT '權限id',
`created_at` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP,
`updated_date` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP(0),
PRIMARY KEY (`user_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Records of User
-- ----------------------------
INSERT INTO `User` VALUES (1, '[email protected]', 'e0a0a002818058f7dafaeb28b8c27bb21c942037', 1, '2022-07-28 00:15:52', '2022-07-28 00:15:52');
-- ----------------------------
-- Table structure for migrations
-- ----------------------------
DROP TABLE IF EXISTS `migrations`;
CREATE TABLE `migrations` (
`id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`version` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`class` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`group` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`namespace` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`time` int(11) NOT NULL,
`batch` int(11) UNSIGNED NOT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Records of migrations
-- ----------------------------
INSERT INTO `migrations` VALUES (1, '2022-07-14-135717', 'App\\Database\\Migrations\\ContractingCompany', 'default', 'App', 1658938529, 1);
INSERT INTO `migrations` VALUES (2, '2022-07-14-142438', 'App\\Database\\Migrations\\ClearingCompany', 'default', 'App', 1658938529, 1);
INSERT INTO `migrations` VALUES (3, '2022-07-14-150203', 'App\\Database\\Migrations\\ClearingDriver', 'default', 'App', 1658938529, 1);
INSERT INTO `migrations` VALUES (4, '2022-07-14-155118', 'App\\Database\\Migrations\\ContainmentCompany', 'default', 'App', 1658938529, 1);
INSERT INTO `migrations` VALUES (5, '2022-07-15-164708', 'App\\Database\\Migrations\\Permission', 'default', 'App', 1658938529, 1);
INSERT INTO `migrations` VALUES (6, '2022-07-15-164930', 'App\\Database\\Migrations\\User', 'default', 'App', 1658938529, 1);
INSERT INTO `migrations` VALUES (7, '2022-07-18-164439', 'App\\Database\\Migrations\\PdfDocument', 'default', 'App', 1658938529, 1);
INSERT INTO `migrations` VALUES (8, '2022-07-19-064413', 'App\\Database\\Migrations\\EngineeringManagement', 'default', 'App', 1658938529, 1);
INSERT INTO `migrations` VALUES (9, '2022-07-21-053733', 'App\\Database\\Migrations\\PdfStatus', 'default', 'App', 1658938529, 1);
INSERT INTO `migrations` VALUES (10, '2022-07-24-172216', 'App\\Database\\Migrations\\Government', 'default', 'App', 1658938529, 1);
SET FOREIGN_KEY_CHECKS = 1;