12.18
一、基础配置文件（必替换）
- pom.xml（修正依赖语法错误）
xml
<groupId>com.company</groupId>
<artifactId>EngineeringQualityDetectionSystem</artifactId>
<version>1.0-SNAPSHOT</version>
<packaging>war</packaging>
<<dependencies>
<!-- Servlet依赖 -->
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>javax.servlet-api</artifactId>
<version>4.0.1</version>
<scope>provided</scope>
</dependency>
<!-- JSP依赖 -->
<dependency>
<groupId>javax.servlet.jsp</groupId>
<artifactId>jsp-api</artifactId>
<version>2.2</version>
<scope>provided</scope>
</dependency>
<!-- JSTL依赖 -->
<dependency>
<groupId>javax.servlet.jsp.jstl</groupId>
<artifactId>jstl-api</artifactId>
<version>1.2</version>
</dependency>
<dependency>
<groupId>taglibs</groupId>
<artifactId>standard</artifactId>
<version>1.1.2</version>
</dependency>
<!-- MySQL驱动 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.33</version>
</dependency>
<!-- Lombok（简化实体类） -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.30</version>
<scope>provided</scope>
</dependency>
</</dependencies>
<build>
<finalName>EngineeringQualityDetectionSystem</finalName>
<plugins>
<!-- 编译插件 -->
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<version>3.8.1</version>
<configuration>
<source>1.8</source>
<target>1.8</target>
<encoding>UTF-8</encoding>
</configuration>
</plugin>
<!-- Tomcat插件（本地运行） -->
<plugin>
<groupId>org.apache.tomcat.maven</groupId>
<artifactId>tomcat7-maven-plugin</artifactId>
<version>2.2</version>
<configuration>
<port>8080</port>
<path>/EngineeringQualityDetectionSystem</path>
</configuration>
</plugin>
</plugins>
</build>
-- 2. 新增委托客户测试数据（可直接登录）
INSERT INTO t_user (user_id, user_name, user_pwd, user_category, group_no) VALUES
('20250009', '某委托客户', '123456', 3, 0);
-- 3. 新增任务序列表（可选：解决任务编号重复风险，建议执行）
CREATE TABLE t_seq (
seq_name VARCHAR(20) PRIMARY KEY,
current_val INT NOT NULL DEFAULT 1
);
INSERT INTO t_seq (seq_name) VALUES ('task_seq');
-- 4. 新增获取序列的存储过程（配合任务编号优化）
DELIMITER //
CREATE PROCEDURE get_task_seq(OUT seq INT)
BEGIN
UPDATE t_seq SET current_val = current_val + 1 WHERE seq_name = 'task_seq';
SELECT current_val INTO seq FROM t_seq WHERE seq_name = 'task_seq';
END //
DELIMITER ;
二、DAO 层核心文件（必替换）
- TaskDao.java（新增客户查询方法）
java
运行
package com.company.detection.dao;
import com.company.detection.entity.Task;
import java.util.List;
public interface TaskDao {
// 1. 委托客户：提交委托申请
int addTask(Task task);
// 2. 公司总监：查询待确认委托
List<Task> queryPendingConfirmTasks();
// 3. 公司总监：确认委托（生成任务编号）
int confirmTask(String id, String state);
// 4. 分配任务：总监分配经理/经理分配检测员
int assignTask(String id, String manageId, String inspectorId);
// 5. 浏览任务：按角色权限查询（检测员-个人/经理-本组/总监-全部）
List<Task> queryTasksByRole(String userId, Integer userCategory, Integer groupNo, String state);
// 6. 检测员：填写/修改检测单
int updateTaskForm(Task task);
// 7. 检测员：删除检测单
int deleteTaskForm(String id);
// 8. 审批：经理/总监审批（同意/退回）
int approveTask(String id, String state, String rejectReason);
// 9. 委托客户：查询已完成任务详情
Task queryTaskDetail(String id);
// 10. 总监：统计指定时间段检测员完成项目数量
List<Task> statCompletedTasks(String startDate, String endDate);
// 新增：委托客户查询自己提交的任务（按委托单位名）
List<Task> queryTasksByClient(String clientName);
}
2. TaskDaoImpl.java（实现新增方法 + 修正逻辑）
java
运行
package com.company.detection.dao.impl;
import com.company.detection.dao.TaskDao;
import com.company.detection.entity.Task;
import com.company.detection.util.DBUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class TaskDaoImpl implements TaskDao {
// 1. 提交委托申请
@Override
public int addTask(Task task) {
Connection conn = null;
PreparedStatement pstmt = null;
int result = 0;
String sql = "INSERT INTO t_task (id, commit_date, client_name, item_name, item_content, state) " +
"VALUES (?, ?, ?, ?, ?, '待总监确认')";
try {
conn = DBUtil.getConnection();
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, task.getId());
pstmt.setString(2, task.getCommitDate());
pstmt.setString(3, task.getClientName());
pstmt.setString(4, task.getItemName());
pstmt.setString(5, task.getItemContent());
result = pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.close(pstmt, conn);
}
return result;
}
// 2. 总监查询待确认委托
@Override
public List<Task> queryPendingConfirmTasks() {
return queryTasksByState("待总监确认");
}
// 通用：按状态查询任务
private List<Task> queryTasksByState(String state) {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
List<Task> list = new ArrayList<>();
String sql = "SELECT * FROM t_task WHERE state = ? ORDER BY commit_date DESC";
try {
conn = DBUtil.getConnection();
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, state);
rs = pstmt.executeQuery();
list = parseTaskResultSet(rs);
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.close(rs, pstmt, conn);
}
return list;
}
// 解析ResultSet为Task列表
private List<Task> parseTaskResultSet(ResultSet rs) throws SQLException {
List<Task> list = new ArrayList<>();
while (rs.next()) {
Task task = new Task();
task.setId(rs.getString("id"));
task.setCommitDate(rs.getString("commit_date"));
task.setClientName(rs.getString("client_name"));
task.setItemName(rs.getString("item_name"));
task.setItemContent(rs.getString("item_content"));
task.setClassType(rs.getString("class"));
task.setMethod(rs.getString("method"));
task.setSampleName(rs.getString("sample_name"));
task.setDryDensity(rs.getDouble("dry_density"));
task.setMoistDensity(rs.getDouble("moist_density"));
task.setState(rs.getString("state"));
task.setOperatingTime(rs.getTimestamp("operating_time"));
task.setInspectorId(rs.getString("inspector_id"));
task.setManageId(rs.getString("manage_id"));
task.setRejectReason(rs.getString("reject_reason"));
list.add(task);
}
return list;
}
// 3. 总监确认委托
@Override
public int confirmTask(String id, String state) {
Connection conn = null;
PreparedStatement pstmt = null;
int result = 0;
String sql = "UPDATE t_task SET state = ?, operating_time = NOW() WHERE id = ?";
try {
conn = DBUtil.getConnection();
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, state);
pstmt.setString(2, id);
result = pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.close(pstmt, conn);
}
return result;
}
// 4. 分配任务
@Override
public int assignTask(String id, String manageId, String inspectorId) {
Connection conn = null;
PreparedStatement pstmt = null;
int result = 0;
String sql = "UPDATE t_task SET manage_id = ?, inspector_id = ?, state = '进行中', operating_time = NOW() WHERE id = ?";
try {
conn = DBUtil.getConnection();
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, manageId);
pstmt.setString(2, inspectorId);
pstmt.setString(3, id);
result = pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.close(pstmt, conn);
}
return result;
}
// 5. 按角色查询任务
@Override
public List<Task> queryTasksByRole(String userId, Integer userCategory, Integer groupNo, String state) {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
List<Task> list = new ArrayList<>();
StringBuilder sql = new StringBuilder("SELECT * FROM t_task WHERE 1=1");
// 按角色过滤
if (userCategory == 0) { // 检测员：仅个人任务
sql.append(" AND inspector_id = ?");
} else if (userCategory == 1) { // 项目经理：本组任务
sql.append(" AND manage_id = ?");
}
// 按状态过滤（可选）
if (state != null && !state.isEmpty()) {
sql.append(" AND state = ?");
}
sql.append(" ORDER BY commit_date DESC");
try {
conn = DBUtil.getConnection();
pstmt = conn.prepareStatement(sql.toString());
int paramIndex = 1;
if (userCategory == 0 || userCategory == 1) {
pstmt.setString(paramIndex++, userId);
}
if (state != null && !state.isEmpty()) {
pstmt.setString(paramIndex++, state);
}
rs = pstmt.executeQuery();
list = parseTaskResultSet(rs);
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.close(rs, pstmt, conn);
}
return list;
}
// 6. 填写/修改检测单
@Override
public int updateTaskForm(Task task) {
Connection conn = null;
PreparedStatement pstmt = null;
int result = 0;
String sql = "UPDATE t_task SET `class` = ?, method = ?, sample_name = ?, dry_density = ?, moist_density = ?, " +
"operating_time = NOW() WHERE id = ?";
try {
conn = DBUtil.getConnection();
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, task.getClassType());
pstmt.setString(2, task.getMethod());
pstmt.setString(3, task.getSampleName());
pstmt.setDouble(4, task.getDryDensity());
pstmt.setDouble(5, task.getMoistDensity());
pstmt.setString(6, task.getId());
result = pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.close(pstmt, conn);
}
return result;
}
// 7. 删除检测单
@Override
public int deleteTaskForm(String id) {
Connection conn = null;
PreparedStatement pstmt = null;
int result = 0;
// 仅删除检测单内容，不删除任务记录
String sql = "UPDATE t_task SET `class` = NULL, method = NULL, sample_name = NULL, dry_density = NULL, " +
"moist_density = NULL WHERE id = ?";
try {
conn = DBUtil.getConnection();
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, id);
result = pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.close(pstmt, conn);
}
return result;
}
// 8. 审批任务
@Override
public int approveTask(String id, String state, String rejectReason) {
Connection conn = null;
PreparedStatement pstmt = null;
int result = 0;
String sql = "UPDATE t_task SET state = ?, reject_reason = ?, operating_time = NOW() WHERE id = ?";
try {
conn = DBUtil.getConnection();
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, state);
pstmt.setString(2, rejectReason);
pstmt.setString(3, id);
result = pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.close(pstmt, conn);
}
return result;
}
// 9. 查询任务详情
@Override
public Task queryTaskDetail(String id) {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
Task task = null;
String sql = "SELECT * FROM t_task WHERE id = ?";
try {
conn = DBUtil.getConnection();
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, id);
rs = pstmt.executeQuery();
if (rs.next()) {
task = new Task();
task.setId(rs.getString("id"));
task.setCommitDate(rs.getString("commit_date"));
task.setClientName(rs.getString("client_name"));
task.setItemName(rs.getString("item_name"));
task.setItemContent(rs.getString("item_content"));
task.setClassType(rs.getString("class"));
task.setMethod(rs.getString("method"));
task.setSampleName(rs.getString("sample_name"));
task.setDryDensity(rs.getDouble("dry_density"));
task.setMoistDensity(rs.getDouble("moist_density"));
task.setState(rs.getString("state"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.close(rs, pstmt, conn);
}
return task;
}
// 10. 统计完成任务
@Override
public List<Task> statCompletedTasks(String startDate, String endDate) {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
List<Task> list = new ArrayList<>();
String sql = "SELECT u.user_name, COUNT(t.id) AS completed_count " +
"FROM t_task t JOIN t_user u ON t.inspector_id = u.user_id " +
"WHERE t.state = '已完成' AND t.commit_date BETWEEN ? AND ? " +
"GROUP BY u.user_name ORDER BY completed_count DESC";
try {
conn = DBUtil.getConnection();
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, startDate);
pstmt.setString(2, endDate);
rs = pstmt.executeQuery();
while (rs.next()) {
Task task = new Task();
task.setUserName(rs.getString("user_name"));
// 用dryDensity暂存完成数量（考试场景可接受）
task.setDryDensity(rs.getDouble("completed_count"));
list.add(task);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.close(rs, pstmt, conn);
}
return list;
}
// 新增：委托客户查询自己提交的任务
@Override
public List<Task> queryTasksByClient(String clientName) {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
List<Task> list = new ArrayList<>();
String sql = "SELECT * FROM t_task WHERE client_name = ? ORDER BY commit_date DESC";
try {
conn = DBUtil.getConnection();
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, clientName);
rs = pstmt.executeQuery();
list = parseTaskResultSet(rs);
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.close(rs, pstmt, conn);
}
return list;
}
}
浙公网安备 33010602011771号