简介
采用Spring Boot框架构建招标采购系统,采用前后端分离方式开发,招标采购系统主要功能有招标管理、采购管理、供应商管理等。招标管理功能涵盖招标、中标、发布招标等流程;采购管理功能负责进行采购流程,包括采购申请、采购计划、采购询价、供应商评审等;供应商管理功能负责管理供应商信息,包括供应商申请、供应商评审、供应商合同等。为分担系统压力将系统中静态图片资源、用户头像、供应商资质文件、项目招投标文件使用第三方文件存储、并且文件安全也得到保障。可以对文件数据进行容错容灾、备份、日志、安全管理、权限管理等功能进行设置进一步提高文件安全性。系统数据库中只存储文件路径大大降低了数据库压力。
相关技术
Spring Boot、MyBatis PULS、Redis数据库、VUE框架、Element UI、Spring Security、MySQL
用户角色
招标采购管理系统主要面向四类角色:医院内部负责招标采购的员工(项目负责人、系统管理员等),供应商、专家。
项目负责人:负责采购项目的申请、供应商资质的审核、专家信息的审核验证、采购项目的管理、通知公告的发布。
系统管理员:负责用户管理、权限管理、采购项目审核。
供应商:参与招标的用户、个人信息的管理、公司信息的管理。
专家:参与评标的用户、个人信息的管理、对供应商的投标评分。
主要业务流程
代码部分:
pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.7.2</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.example</groupId>
<artifactId>BiddingSystem</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>war</packaging>
<name>BiddingSystem</name>
<description>BiddingSystem</description>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-configuration-processor</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>commons-lang</groupId>
<artifactId>commons-lang</artifactId>
<version>2.6</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-devtools</artifactId>
<scope>runtime</scope>
<optional>true</optional>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-tomcat</artifactId>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.4.3</version>
<scope>compile</scope>
</dependency>
<dependency>
<groupId>commons-lang</groupId>
<artifactId>commons-lang</artifactId>
<version>2.6</version>
</dependency>
<dependency>
<groupId>com.google.guava</groupId>
<artifactId>guava</artifactId>
<version>21.0</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.23</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.22</version>
</dependency>
<dependency>
<groupId>com.github.xiaoymin</groupId>
<artifactId>knife4j-spring-boot-starter</artifactId>
<version>3.0.3</version>
</dependency>
<dependency>
<groupId>javax.mail</groupId>
<artifactId>javax.mail-api</artifactId>
<version>1.5.6</version>
</dependency>
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
<!-- redis-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-redis</artifactId>
</dependency>
<dependency>
<groupId>io.jsonwebtoken</groupId>
<artifactId>jjwt</artifactId>
<version>0.9.1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.alibaba/fastjson -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.73</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-security</artifactId>
</dependency>
<!-- 2.腾讯云sdk的 -->
<dependency>
<groupId>com.qcloud</groupId>
<artifactId>cos_api</artifactId>
<version>5.6.89</version>
</dependency>
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.5.9</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
application.yml
spring:
datasource:
username: root
password: root
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/bidingsystem?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8&zeroDateTimeBehavior=convertToNull
type: com.alibaba.druid.pool.DruidDataSource
mvc:
pathmatch:
matching-strategy: ant_path_matcher
redis:
host: 127.0.0.1 #????????
port: 6379 #????????
password: 123456
client-type: lettuce
timeout: 10000
jedis:
pool:
max-active: 16
servlet:
multipart:
max-file-size: 500MB #单个数据大小
max-request-size: 1024MB #总数据大小
server:
port: 8888
mybatis-plus:
mapper-locations: classpath*:mapper/**/*.xml
global-config:
db-config:
table-prefix: tab_
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
cos: #腾讯云对象存储配置
baseUrl: https://zhangbo-1306119450.cos.ap-chengdu.myqcloud.com/
accessKey: AKID9Jya44zpfVJiz8JLI9sSpW6Wmy7********
secretKey: oRmvhMxQmz6SddS2Wq2OwLZ*****
regionName: ap-chengdu
bucketName: zhangbo-******
目录结构
数据库结构
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for tab_admin
-- ----------------------------
DROP TABLE IF EXISTS `tab_admin`;
CREATE TABLE `tab_admin` (
`admin_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '管理员id',
`admin_name` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '账号用户名',
`employees_phone` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '员工联系电话',
`employees_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '员工号',
`employees_name` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '员工姓名',
`employees_email` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '员工邮箱',
`admin_type` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '管理员类型',
`admin_pass` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '管理员密码',
PRIMARY KEY (`admin_id`, `employees_id`) USING BTREE,
INDEX `admin_id`(`admin_id` ASC) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '管理员表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for tab_application
-- ----------------------------
DROP TABLE IF EXISTS `tab_application`;
CREATE TABLE `tab_application` (
`application_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '报名编号',
`purchase_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '项目编号',
`expert_account` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '评分专家编号',
`application_time` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '报名时间',
`purchase_contact` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '负责人姓名',
`purchase_bids_time` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '开标时间',
`vendor_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '供应商',
`purchase_phone` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '负责人联系方式',
`vendor_account` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '供应商联系人账号',
`purchase_name` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '项目名称',
`purchase_explain` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '项目说明',
`purchase_file_path` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '投标文件',
`vendor_score` int NULL DEFAULT NULL COMMENT '评分',
`purchase_account` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '项目负责人',
PRIMARY KEY (`application_id`) USING BTREE,
INDEX `purchase_id`(`purchase_id` ASC) USING BTREE,
CONSTRAINT `tab_application_ibfk_1` FOREIGN KEY (`purchase_id`) REFERENCES `tab_purchase` (`purchase_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '项目报名表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for tab_article
-- ----------------------------
DROP TABLE IF EXISTS `tab_article`;
CREATE TABLE `tab_article` (
`article_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '公告id',
`article_title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '公告标题',
`article_content` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '公告内容',
`article_time` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '发布时间',
`article_type` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '公告类型',
`article_record` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '发布人',
PRIMARY KEY (`article_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '公告表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for tab_expert
-- ----------------------------
DROP TABLE IF EXISTS `tab_expert`;
CREATE TABLE `tab_expert` (
`expert_id` int NOT NULL AUTO_INCREMENT COMMENT '编号',
`expert_name` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '姓名',
`expert_PS` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '政治面貌',
`expert_graduation` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '毕业学校',
`expert_ocupation` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '单位地址',
`expert_site` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '所在单位',
`expert_phone` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '单位电话',
`expert_email` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '单位邮箱',
`expert_introduce` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '个人介绍',
`expert_account` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '个人账号',
`expert_status` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '状态',
`expert_account_status` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '账号状态',
`expert_industry` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '所在行业',
PRIMARY KEY (`expert_id`, `expert_account`) USING BTREE,
INDEX `专家`(`expert_account` ASC) USING BTREE,
CONSTRAINT `专家` FOREIGN KEY (`expert_account`) REFERENCES `tab_user` (`user_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 45 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '专家表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for tab_menu
-- ----------------------------
DROP TABLE IF EXISTS `tab_menu`;
CREATE TABLE `tab_menu` (
`id` bigint NOT NULL AUTO_INCREMENT,
`menu_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT 'NULL' COMMENT '菜单名',
`path` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '路由地址',
`component` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '组件路径',
`visible` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT '0' COMMENT '菜单状态(0显示 1隐藏)',
`status` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT '0' COMMENT '菜单状态(0正常 1停用)',
`perms` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '权限标识',
`icon` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT '#' COMMENT '菜单图标',
`create_by` bigint NULL DEFAULT NULL,
`create_time` datetime NULL DEFAULT NULL,
`update_by` bigint NULL DEFAULT NULL,
`update_time` datetime NULL DEFAULT NULL,
`del_flag` int NULL DEFAULT 0 COMMENT '是否删除(0未删除 1已删除)',
`remark` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '备注',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 31 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '菜单表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for tab_opt
-- ----------------------------
DROP TABLE IF EXISTS `tab_opt`;
CREATE TABLE `tab_opt` (
`id` int NOT NULL AUTO_INCREMENT,
`type` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 16 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '供应类别、采购方式' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for tab_outcome
-- ----------------------------
DROP TABLE IF EXISTS `tab_outcome`;
CREATE TABLE `tab_outcome` (
`purchase_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '项目编号',
`purchase_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '项目名称',
`purchase_contract` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '合同',
`purchase_tenderer` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '招标人',
`purchase_tender_method` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '招标方式',
`purchase_company_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '中标人',
`purchase_vendor_contact` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '联系人',
`purchase_vendor_phone` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '投标人电话',
`purchase_vendor_email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '电子邮箱',
PRIMARY KEY (`purchase_id`) USING BTREE,
CONSTRAINT `tab_outcome_ibfk_1` FOREIGN KEY (`purchase_id`) REFERENCES `tab_purchase` (`purchase_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '合同表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for tab_purchase
-- ----------------------------
DROP TABLE IF EXISTS `tab_purchase`;
CREATE TABLE `tab_purchase` (
`purchase_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '项目编号',
`purchase_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '项目名称',
`purchase_explain` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '项目说明',
`purchase_budget` int NOT NULL COMMENT '项目预算',
`purchase_charge` int NOT NULL COMMENT '招标文件收费',
`purchase_deposit` int NOT NULL COMMENT '保证金',
`purchase_submission` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '投标文件递交方式',
`purchase_bidding` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '开标地点',
`purchase_bid_opening_method` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '评标方式',
`purchase_tender_method` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '招标方式',
`purchase_registration_deadline` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '报名截止时间',
`purchase_type` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '采购类别',
`purchase_contact` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '联系人',
`purchase_phone` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '固定电话',
`purchase_email` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '电子邮箱',
`purchase_file` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '招标文件',
`purchase_status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '项目状态',
`purchase_end` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '距离结束',
`purchase_account` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '项目负责人',
PRIMARY KEY (`purchase_id`) USING BTREE,
INDEX `purchase_account`(`purchase_account` ASC) USING BTREE,
CONSTRAINT `tab_purchase_ibfk_1` FOREIGN KEY (`purchase_account`) REFERENCES `tab_user` (`user_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '项目表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for tab_record
-- ----------------------------
DROP TABLE IF EXISTS `tab_record`;
CREATE TABLE `tab_record` (
`resource_id` int NOT NULL AUTO_INCREMENT COMMENT '操作编号',
`record_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '对应记录',
`record_type` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '操作类型(项目表:删除、更新)',
`record_update_time` varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '操作时间',
`record_operator` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '操作人(用户名)',
PRIMARY KEY (`resource_id`, `record_id`) USING BTREE,
INDEX `record_operator`(`record_operator` ASC) USING BTREE,
CONSTRAINT `tab_record_ibfk_1` FOREIGN KEY (`record_operator`) REFERENCES `tab_user` (`user_contact_name`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 41 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '操作记录表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for tab_role
-- ----------------------------
DROP TABLE IF EXISTS `tab_role`;
CREATE TABLE `tab_role` (
`id` bigint NOT NULL AUTO_INCREMENT,
`name` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`role_key` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '角色权限字符串',
`status` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT '0' COMMENT '角色状态(0正常 1停用)',
`del_flag` int NULL DEFAULT 0 COMMENT 'del_flag',
`create_by` bigint NULL DEFAULT NULL,
`create_time` datetime NULL DEFAULT NULL,
`update_by` bigint NULL DEFAULT NULL,
`update_time` datetime NULL DEFAULT NULL,
`remark` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '备注',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '角色表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for tab_role_menu
-- ----------------------------
DROP TABLE IF EXISTS `tab_role_menu`;
CREATE TABLE `tab_role_menu` (
`role_id` bigint NOT NULL AUTO_INCREMENT COMMENT '角色ID',
`menu_id` bigint NOT NULL DEFAULT 0 COMMENT '菜单id',
PRIMARY KEY (`role_id`, `menu_id`) USING BTREE,
INDEX `menu_id`(`menu_id` ASC) USING BTREE,
CONSTRAINT `tab_role_menu_ibfk_1` FOREIGN KEY (`role_id`) REFERENCES `tab_role` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `tab_role_menu_ibfk_2` FOREIGN KEY (`menu_id`) REFERENCES `tab_menu` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '角色与菜单关联表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for tab_score
-- ----------------------------
DROP TABLE IF EXISTS `tab_score`;
CREATE TABLE `tab_score` (
`score_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`expert_account` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`vendor_account` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`purchase_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`vendor_score` int NOT NULL,
PRIMARY KEY (`score_id`) USING BTREE,
INDEX `purchase_id`(`purchase_id` ASC) USING BTREE,
CONSTRAINT `tab_score_ibfk_1` FOREIGN KEY (`purchase_id`) REFERENCES `tab_application` (`purchase_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '项目评分表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for tab_structure
-- ----------------------------
DROP TABLE IF EXISTS `tab_structure`;
CREATE TABLE `tab_structure` (
`str_id` int NOT NULL AUTO_INCREMENT,
`tab_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`field_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`field_name_ch` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
PRIMARY KEY (`str_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 79 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '数据库表结构字典表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for tab_suggestion
-- ----------------------------
DROP TABLE IF EXISTS `tab_suggestion`;
CREATE TABLE `tab_suggestion` (
`id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`content` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`user_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`status` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`record` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '处理人',
`record_time` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '处理时间',
`suggestion_time` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '发生时间',
INDEX `user_id`(`user_id` ASC) USING BTREE,
CONSTRAINT `tab_suggestion_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tab_user` (`user_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '反馈与建议表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for tab_user
-- ----------------------------
DROP TABLE IF EXISTS `tab_user`;
CREATE TABLE `tab_user` (
`user_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '用户标识',
`user_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '用户名',
`user_phone` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '联系电话',
`user_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '用户类型',
`user_email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '电子邮箱',
`user_company_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '单位名称',
`user_status` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '账号状态',
`user_contact_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '姓名',
`user_image` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT 'https://zhangbo-1306119450.cos.ap-chengdu.myqcloud.com//avatar/581677739280521.png' COMMENT '头像',
`user_password` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '用户密码',
`user_register_time` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '注册时间',
PRIMARY KEY (`user_id`) USING BTREE,
INDEX `user_contact_name`(`user_contact_name` ASC) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '用户表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for tab_user_role
-- ----------------------------
DROP TABLE IF EXISTS `tab_user_role`;
CREATE TABLE `tab_user_role` (
`user_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '用户id',
`role_id` bigint NOT NULL DEFAULT 0 COMMENT '角色id',
INDEX `user_id`(`user_id` ASC) USING BTREE,
INDEX `role_id`(`role_id` ASC) USING BTREE,
CONSTRAINT `tab_user_role_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tab_user` (`user_id`) ON DELETE SET NULL ON UPDATE RESTRICT,
CONSTRAINT `tab_user_role_ibfk_2` FOREIGN KEY (`role_id`) REFERENCES `tab_role` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '用户与角色关联表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for tab_vendor
-- ----------------------------
DROP TABLE IF EXISTS `tab_vendor`;
CREATE TABLE `tab_vendor` (
`vendor_id` int NOT NULL AUTO_INCREMENT COMMENT '供应商编号',
`vendor_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '供应商名称',
`vendor_type` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '供应商类型',
`vendor_phone` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '供应商联系电话',
`vendor_email` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '供应商联系邮箱',
`vendor_level` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '供应商级别',
`vendor_aptitude` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '供应商资质文件',
`vendor_account` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '供应商账号',
`vendor_contact_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '联系人姓名',
`vendor_contact_phone` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '联系人电话',
`vendor_contact_email` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '联系人邮箱',
`vendor_status` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '供应商状态',
`vendor_account_status` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '供应商账号状态',
`vendor_add` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '公司地址',
PRIMARY KEY (`vendor_id`, `vendor_account`) USING BTREE,
INDEX `联系人`(`vendor_account` ASC) USING BTREE,
CONSTRAINT `联系人` FOREIGN KEY (`vendor_account`) REFERENCES `tab_user` (`user_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 177 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '供应商表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Triggers structure for table tab_admin
-- ----------------------------
DROP TRIGGER IF EXISTS `add_user`;
delimiter ;;
CREATE TRIGGER `add_user` AFTER INSERT ON `tab_admin` FOR EACH ROW begin
INSERT INTO tab_user(user_id,user_name,user_password,user_type,user_contact_name,user_email,user_phone,user_status,user_register_time)VALUES(new.admin_id,new.admin_name,new.admin_pass,new.admin_type,new.employees_name,new.employees_email,new.employees_phone,0,now());
END
;;
delimiter ;
-- ----------------------------
-- Triggers structure for table tab_user
-- ----------------------------
DROP TRIGGER IF EXISTS `add_userinfo`;
delimiter ;;
CREATE TRIGGER `add_userinfo` BEFORE INSERT ON `tab_user` FOR EACH ROW BEGIN
IF new.user_type='专家' then
INSERT INTO tab_expert(expert_name,expert_account,expert_phone,expert_email,expert_status,expert_account_status)VALUES(new.user_contact_name,new.user_id,new.user_phone,new.user_email,'未完善信息',new.user_status);
INSERT INTO tab_user_role(user_id,role_id)VALUES(new.user_id,4);
ELSEIF new.user_type='供应商' then
INSERT INTO tab_vendor(vendor_name,vendor_level,vendor_contact_name,vendor_contact_phone,vendor_account,vendor_account_status,vendor_contact_email,vendor_status,vendor_aptitude)VALUES(new.user_company_name,0,new.user_contact_name,new.user_phone,new.user_id,new.user_status,new.user_email,'未完善信息','未上传');
INSERT INTO tab_user_role(user_id,role_id)VALUES(new.user_id,3);
ELSEIF new.user_type='管理员' then
INSERT INTO tab_user_role(user_id,role_id)VALUES(new.user_id,5);
ELSEIF new.user_type='项目负责人' then
INSERT INTO tab_user_role(user_id,role_id)VALUES(new.user_id,2);
end if ;
END
;;
delimiter ;
-- ----------------------------
-- Triggers structure for table tab_user
-- ----------------------------
DROP TRIGGER IF EXISTS `update_userinfo`;
delimiter ;;
CREATE TRIGGER `update_userinfo` BEFORE UPDATE ON `tab_user` FOR EACH ROW BEGIN
if new.user_type='供应商' then
UPDATE tab_vendor SET vendor_name=new.user_company_name,vendor_contact_name=new.user_contact_name,vendor_contact_phone=new.user_phone,vendor_contact_email=new.user_email,vendor_status='待审核' WHERE (vendor_account= new.user_id) ;
ELSEIF new.user_type='专家' then
UPDATE tab_expert SET expert_name=new.user_contact_name,expert_phone=new.user_phone,expert_email=new.user_phone,expert_status='待审核'
WHERE (expert_account=new.user_id);
end if ;
END
;;
delimiter ;
-- ----------------------------
-- Triggers structure for table tab_user
-- ----------------------------
DROP TRIGGER IF EXISTS `delete_userinfo`;
delimiter ;;
CREATE TRIGGER `delete_userinfo` AFTER DELETE ON `tab_user` FOR EACH ROW BEGIN
IF old.user_type='供应商' then
DELETE from tab_vendor WHERE vendor_account=old.user_id;
ELSEIF old.user_type='专家' THEN
DELETE from tab_expert WHERE expert_account=old.user_id;
END if;
END
;;
delimiter ;
SET FOREIGN_KEY_CHECKS = 1;
触发器
1、user表
//更新触发器:User表发生更新时同步触发更新专家与供应商表
BEGIN
if new.user_type='供应商' then
UPDATE tab_vendor SET vendor_name=new.user_company_name,vendor_contact_name=new.user_contact_name,vendor_contact_phone=new.user_phone,vendor_contact_email=new.user_email,vendor_status='待审核' WHERE (vendor_account= new.user_id) ;
ELSEIF new.user_type='专家' then
UPDATE tab_expert SET expert_name=new.user_contact_name,expert_phone=new.user_phone,expert_email=new.user_phone,expert_status='待审核'
WHERE (expert_account=new.user_id);
end if ;
END
//删除触发器:User表发生删除时同步触发删除专家与供应商表
BEGIN
IF old.user_type='供应商' then
DELETE from tab_vendor WHERE vendor_account=old.user_id;
ELSEIF old.user_type='专家' THEN
DELETE from tab_expert WHERE expert_account=old.user_id;
END if;
END
//添加触发器:User表新增时同步触发新增至专家、供应商表,在权限表新增账号角色权限信息
BEGIN
IF new.user_type='专家' then
INSERT INTO tab_expert(expert_name,expert_account,expert_phone,expert_email,expert_status,expert_account_status)VALUES(new.user_contact_name,new.user_id,new.user_phone,new.user_email,'未完善信息',new.user_status);
INSERT INTO tab_user_role(user_id,role_id)VALUES(new.user_id,4);
ELSEIF new.user_type='供应商' then
INSERT INTO tab_vendor(vendor_name,vendor_level,vendor_contact_name,vendor_contact_phone,vendor_account,vendor_account_status,vendor_contact_email,vendor_status,vendor_aptitude)VALUES(new.user_company_name,0,new.user_contact_name,new.user_phone,new.user_id,new.user_status,new.user_email,'未完善信息','未上传');
INSERT INTO tab_user_role(user_id,role_id)VALUES(new.user_id,3);
ELSEIF new.user_type='管理员' then
INSERT INTO tab_user_role(user_id,role_id)VALUES(new.user_id,5);
ELSEIF new.user_type='项目负责人' then
INSERT INTO tab_user_role(user_id,role_id)VALUES(new.user_id,2);
end if ;
END
2、管理员表
//添加触发器:管理员表新增时同步触发新增至user表
INSERT INTO tab_user(user_id,user_name,user_password,user_type,user_contact_name,user_email,user_phone,user_status)VALUES(new.admin_id,new.admin_name,new.admin_pass,new.admin_type,new.employees_name,new.employees_email,new.employees_phone,0);
END
后续另发