12.12

3. DAO 层（数据访问层）
   （1）UserDao.java（人员 DAO 接口）
   java
   运行
   package com.company.detection.dao;

import com.company.detection.entity.User;

/**

• 人员数据访问接口：登录验证
  */
  public interface UserDao {
  User login(String userId, String userPwd);
  }
  （2）UserDaoImpl.java（人员 DAO 实现）
  java
  运行
  package com.company.detection.dao.impl;

import com.company.detection.dao.UserDao;
import com.company.detection.entity.User;
import com.company.detection.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class UserDaoImpl implements UserDao {

@Override
public User login(String userId, String userPwd) {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    User user = null;

    // 登录SQL：匹配ID和密码
    String sql = "SELECT user_id, user_name, user_category, group_no FROM t_user WHERE user_id = ? AND user_pwd = ?";

    try {
        conn = DBUtil.getConnection();
        pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, userId);
        pstmt.setString(2, userPwd);
        rs = pstmt.executeQuery();

        if (rs.next()) {
            user = new User();
            user.setUserId(rs.getString("user_id"));
            user.setUserName(rs.getString("user_name"));
            user.setUserCategory(rs.getInt("user_category"));
            user.setGroupNo(rs.getInt("group_no"));
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        DBUtil.close(rs, pstmt, conn);
    }
    return user;
}

}
（3）TaskDao.java（任务 DAO 接口）
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 queryPendingConfirmTasks();

  // 3. 公司总监：确认委托（生成任务编号）
  int confirmTask(String id, String state);

  // 4. 分配任务：总监分配经理/经理分配检测员
  int assignTask(String id, String manageId, String inspectorId);

  // 5. 浏览任务：按角色权限查询（检测员-个人/经理-本组/总监-全部）
  List 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 statCompletedTasks(String startDate, String endDate);
  }
  （4）TaskDaoImpl.java（任务 DAO 实现）
  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;
}

}