10.28

MES系统
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.Scanner;

public class MES_System {
private static final String DB_URL = "jdbc:mysql://localhost:3306/abc";
private static final String USER = "root";
private static final String PASS = "aaa666";
private static final DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");

public static void main(String[] args) {
    Scanner scanner = new Scanner(System.in);

    try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS)) {
        while (true) {
            System.out.println("\n请选择操作:");
            System.out.println("1. 提交日报");
            System.out.println("2. 修改日报");
            System.out.println("3. 删除日报");
            System.out.println("4. 查询日报");
            System.out.println("5. 退出");
            System.out.print("请输入您的选择: ");
            int choice = scanner.nextInt();
            scanner.nextLine();
            switch (choice) {
                case 1:
                    submitReport(conn, scanner);
                    break;
                case 2:
                    modifyReport(conn, scanner);
                    break;
                case 3:
                    deleteReport(conn, scanner);
                    break;
                case 4:
                    queryReport(conn, scanner);
                    break;
                case 5:
                    System.out.println("程序已退出.");
                    return;
                default:
                    System.out.println("无效的选择，请重新输入.");
            }
        }
    } catch (SQLException e) {
        System.out.println("数据库连接失败: " + e.getMessage());
    }
}

private static void submitReport(Connection conn, Scanner scanner) throws SQLException {
    System.out.print("请输入生产批次: ");
    String batchID = scanner.nextLine();
    System.out.print("请输入工号: ");
    String workerID = scanner.nextLine();
    System.out.print("请输入工人姓名: ");
    String workerName = scanner.nextLine();

    System.out.print("请输入转出总数: ");
    int totalProduced = scanner.nextInt();
    scanner.nextLine();
    System.out.print("请输入转出合格数: ");
    int qualified = scanner.nextInt();
    scanner.nextLine();
    System.out.print("请输入次品数: ");
    int defective = scanner.nextInt();
    scanner.nextLine();
    System.out.print("请输入丢失数: ");
    int lost = scanner.nextInt();
    scanner.nextLine();
    System.out.print("请输入上报日期(格式：yyyy-mm-dd): ");
    String reportDateStr = scanner.nextLine();
    LocalDate reportDate = LocalDate.parse(reportDateStr, formatter);

    if (totalProduced != (qualified + defective + lost)) {
        System.out.println("上报数据有误，请重新检查数据！");
        return;
    }

    String sql = "INSERT INTO ProductionBatch (ProductionBatchID, WorkerID, WorkerName, ReportDate, TotalProduced, Qualified, Defective, Lost) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
    try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
        pstmt.setString(1, batchID);
        pstmt.setString(2, workerID);
        pstmt.setString(3, workerName);
        pstmt.setDate(4, java.sql.Date.valueOf(reportDate));
        pstmt.setInt(5, totalProduced);
        pstmt.setInt(6, qualified);
        pstmt.setInt(7, defective);
        pstmt.setInt(8, lost);
        int affectedRows = pstmt.executeUpdate();
        if (affectedRows > 0) {
            System.out.println("数据已成功提交！");
        } else {
            System.out.println("数据提交失败！");
        }
    }
}

private static void modifyReport(Connection conn, Scanner scanner) throws SQLException {
    System.out.print("请输入生产批次: ");
    String batchID = scanner.nextLine();
    System.out.print("请输入上报日期(格式：yyyy-mm-dd)进行修改: ");
    String reportDateStr = scanner.nextLine();
    LocalDate reportDate = LocalDate.parse(reportDateStr, formatter);
    String sql = "SELECT * FROM ProductionBatch WHERE ProductionBatchID = ? AND ReportDate = ?";
    try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
        pstmt.setString(1, batchID);
        pstmt.setDate(2, java.sql.Date.valueOf(reportDate));
        ResultSet rs = pstmt.executeQuery();
        if (!rs.next()) {
            System.out.println("未找到指定的生产批次和日期！");
            return;
        }

        System.out.print("请输入新的转出总数: ");
        int totalProduced = scanner.nextInt();
        scanner.nextLine();
        System.out.print("请输入新的转出合格数: ");
        int qualified = scanner.nextInt();
        scanner.nextLine();
        System.out.print("请输入新的次品数: ");
        int defective = scanner.nextInt();
        scanner.nextLine();
        System.out.print("请输入新的丢失数: ");
        int lost = scanner.nextInt();
        scanner.nextLine();

        if (totalProduced != (qualified + defective + lost)) {
            System.out.println("上报数据有误，请重新检查数据！");
            return;
        }

        sql = "UPDATE ProductionBatch SET TotalProduced = ?, Qualified = ?, Defective = ?, Lost = ? WHERE ProductionBatchID = ? AND ReportDate = ?";
        try (PreparedStatement pstmtUpdate = conn.prepareStatement(sql)) {
            pstmtUpdate.setInt(1, totalProduced);
            pstmtUpdate.setInt(2, qualified);
            pstmtUpdate.setInt(3, defective);
            pstmtUpdate.setInt(4, lost);
            pstmtUpdate.setString(5, batchID);
            pstmtUpdate.setDate(6, java.sql.Date.valueOf(reportDate));
            int affectedRows = pstmtUpdate.executeUpdate();
            if (affectedRows > 0) {
                System.out.println("数据已成功更新！");
            } else {
                System.out.println("数据更新失败！");
            }
        }
    }
}

private static void deleteReport(Connection conn, Scanner scanner) throws SQLException {
    System.out.print("请输入生产批次: ");
    String batchID = scanner.nextLine();
    System.out.print("请输入上报日期(格式：yyyy-mm-dd)进行修改: ");
    String reportDateStr = scanner.nextLine();
    LocalDate reportDate = LocalDate.parse(reportDateStr, formatter);
    String sql = "DELETE FROM ProductionBatch WHERE ProductionBatchID = ? AND ReportDate = ?";
    try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
        pstmt.setString(1, batchID);
        pstmt.setDate(2, java.sql.Date.valueOf(reportDate));
        int affectedRows = pstmt.executeUpdate();
        if (affectedRows > 0) {
            System.out.println("数据已成功删除！");
        } else {
            System.out.println("未找到指定的生产批次和日期！");
        }
    }
}

private static void queryReport(Connection conn, Scanner scanner) throws SQLException {
    System.out.println("请选择查询条件:");
    System.out.println("1. 上报日期");
    System.out.println("2. 生产批次");
    System.out.println("3. 工号");
    System.out.println("4. 工人姓名");
    System.out.print("请输入查询条件编号: ");
    int queryBy = scanner.nextInt();
    scanner.nextLine();
    switch (queryBy) {
        case 1:
            System.out.print("请输入上报日期(格式：yyyy-mm-dd): ");
            String queryDateStr = scanner.nextLine();
            LocalDate queryDate = LocalDate.parse(queryDateStr, formatter);
            queryByDate(conn, queryDate);
            break;
        case 2:
            System.out.print("请输入生产批次: ");
            String queryBatchID = scanner.nextLine();
            queryByBatchID(conn, queryBatchID);
            break;
        case 3:
            System.out.print("请输入工号: ");
            String queryWorkerID = scanner.nextLine();
            queryByWorkerID(conn, queryWorkerID);
            break;
        case 4:
            System.out.print("请输入工人姓名: ");
            String queryWorkerName = scanner.nextLine();
            queryByWorkerName(conn, queryWorkerName);
            break;
        default:
            System.out.println("无效的选择，请重新输入.");
    }
}

private static void queryByDate(Connection conn, LocalDate date) throws SQLException {
    String sql = "SELECT * FROM ProductionBatch WHERE ReportDate = ?";
    try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
        pstmt.setDate(1, java.sql.Date.valueOf(date));
        ResultSet rs = pstmt.executeQuery();
        while (rs.next()) {
            System.out.println("生产批次: " + rs.getString("ProductionBatchID"));
            System.out.println("工号: " + rs.getString("WorkerID"));
            System.out.println("工人姓名: " + rs.getString("WorkerName"));
            System.out.println("上报日期: " + rs.getDate("ReportDate"));
            System.out.println("转出总数: " + rs.getInt("TotalProduced"));
            System.out.println("转出合格数: " + rs.getInt("Qualified"));
            System.out.println("次品数: " + rs.getInt("Defective"));
            System.out.println("丢失数: " + rs.getInt("Lost"));
            System.out.println();
        }
    }
}

private static void queryByBatchID(Connection conn, String batchID) throws SQLException {
    String sql = "SELECT * FROM ProductionBatch WHERE ProductionBatchID = ?";
    try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
        pstmt.setString(1, batchID);
        ResultSet rs = pstmt.executeQuery();
        while (rs.next()) {
            System.out.println("生产批次: " + rs.getString("ProductionBatchID"));
            System.out.println("工号: " + rs.getString("WorkerID"));
            System.out.println("工人姓名: " + rs.getString("WorkerName"));
            System.out.println("上报日期: " + rs.getDate("ReportDate").toLocalDate());
            System.out.println("转出总数: " + rs.getInt("TotalProduced"));
            System.out.println("转出合格数: " + rs.getInt("Qualified"));
            System.out.println("次品数: " + rs.getInt("Defective"));
            System.out.println("丢失数: " + rs.getInt("Lost"));
            System.out.println();
        }
    }
}

private static void queryByWorkerID(Connection conn, String workerID) throws SQLException {
    String sql = "SELECT * FROM ProductionBatch WHERE WorkerID = ?";
    try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
        pstmt.setString(1, workerID);
        ResultSet rs = pstmt.executeQuery();
        while (rs.next()) {
            System.out.println("生产批次: " + rs.getString("ProductionBatchID"));
            System.out.println("工号: " + rs.getString("WorkerID"));
            System.out.println("工人姓名: " + rs.getString("WorkerName"));
            System.out.println("上报日期: " + rs.getDate("ReportDate").toLocalDate());
            System.out.println("转出总数: " + rs.getInt("TotalProduced"));
            System.out.println("转出合格数: " + rs.getInt("Qualified"));
            System.out.println("次品数: " + rs.getInt("Defective"));
            System.out.println("丢失数: " + rs.getInt("Lost"));
            System.out.println();
        }
    }
}

private static void queryByWorkerName(Connection conn, String workerName) throws SQLException {
    String sql = "SELECT * FROM ProductionBatch WHERE WorkerName = ?";
    try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
        pstmt.setString(1, workerName);
        ResultSet rs = pstmt.executeQuery();
        while (rs.next()) {
            System.out.println("生产批次: " + rs.getString("ProductionBatchID"));
            System.out.println("工号: " + rs.getString("WorkerID"));
            System.out.println("工人姓名: " + rs.getString("WorkerName"));
            System.out.println("上报日期: " + rs.getDate("ReportDate").toLocalDate());
            System.out.println("转出总数: " + rs.getInt("TotalProduced"));
            System.out.println("转出合格数: " + rs.getInt("Qualified"));
            System.out.println("次品数: " + rs.getInt("Defective"));
            System.out.println("丢失数: " + rs.getInt("Lost"));
            System.out.println();
        }
    }
}

}