package com.zlj.ks;
import java.sql.*;
import java.util.Scanner;
public class t1 {
public static void main(String[] args) throws SQLException, ClassNotFoundException {
Scanner sc = new Scanner(System.in);
while (true) {
//列印選單
System.out.println("-------Mes系統-------");
System.out.println("1.提交日報");
System.out.println("2.刪除日報");
System.out.println("3.修改日報");
System.out.println("4.查詢日報");
System.out.println("5.退出系統");
System.out.println("請輸入你想執行的功能序號");
int choice = sc.nextInt();
if (choice == 1) {
System.out.println("請輸入生產批次：");
int lot = sc.nextInt();
System.out.println("請輸入工號：");
int id = sc.nextInt();
System.out.println("請輸入工人姓名：");
String name = sc.next();
System.out.println("請輸入轉出總數：");
int outnum = sc.nextInt();
System.out.println("請輸入轉出合格數：");
int goodnum = sc.nextInt();
System.out.println("請輸入次品數：");
int badnum = sc.nextInt();
System.out.println("請輸入丟失數：");
int lostnum = sc.nextInt();
// 檢查押平規則
if (outnum == (goodnum + badnum + lostnum)) {
try {
//載入驅動
Class.forName("com.mysql.cj.jdbc.Driver");
//獲取連線
String url = "jdbc:mysql://127.0.0.1:3306/kaoshi?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";
String username = "root";
String password = "root";
Connection conn = DriverManager.getConnection(url, username, password);
//建立會話
Statement sta = conn.createStatement();
//傳送SQL
int n = sta.executeUpdate("insert into t_report (id, name, outnum, goodnum, badnum, lostnum, lot) values(" + id + ", '" + name + "', " + outnum + ", " + goodnum + ", " + badnum + ", " + lostnum + ", " + lot + ")");
if (n > 0) {
System.out.println("提交成功！");
} else {
System.out.println("提交失敗！");
}
//關閉資料庫資源
sta.close();
conn.close();
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
} else {
System.out.println("上報資料有誤，請重新提交");
}
}
if (choice == 2) {
System.out.println("請輸入你要刪除工序的生產批次");
int lno = sc.nextInt();
int n = delByLno(lno);
if (n <= 0) {
System.out.println("刪除失敗！");
} else {
System.out.println("刪除成功！");
}
}
if (choice == 3) {
System.out.println("請輸入要修改工序的生產批次：");
int lno = sc.nextInt();
System.out.println("請選擇要修改的欄位：1.工號 2.工人姓名 3.轉出總數 4.轉出合格數 5.次品數 6.丟失數");
int fieldChoice = sc.nextInt();
System.out.println("請輸入新值：");
String newValue = sc.next();
String updateField = "";
switch (fieldChoice) {
case 1:
updateField = "id";
break;
case 2:
updateField = "name";
break;
case 3:
updateField = "outnum";
break;
case 4:
updateField = "goodnum";
break;
case 5:
updateField = "badnum";
break;
case 6:
updateField = "lostnum";
break;
}
try {
//載入驅動
Class.forName("com.mysql.cj.jdbc.Driver");
//獲取連線
String url = "jdbc:mysql://127.0.0.1:3306/kaoshi?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";
String username = "root";
String password = "root";
Connection conn = DriverManager.getConnection(url, username, password);
//建立會話
Statement sta = conn.createStatement();
//傳送SQL
int n = sta.executeUpdate("update t_report set " + updateField + " = '" + newValue + "' where lot = " + lno);
if (n > 0) {
System.out.println("修改成功！");
} else {
System.out.println("修改失敗！");
}
//關閉資料庫資源
sta.close();
conn.close();
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
}
if (choice == 4) {
System.out.println("請選擇查詢條件：");
System.out.println("1.按生產批次查詢");
System.out.println("2.按工號查詢");
System.out.println("3.按工人姓名查詢");
System.out.println("4.按轉出總數查詢");
System.out.println("5.按轉出合格數查詢");
System.out.println("6.按次品數查詢");
System.out.println("7.按丟失數查詢");
int queryChoice = sc.nextInt();
String queryField = "";
int queryValue = 0;
String queryValueStr = "";
switch (queryChoice) {
case 1:
queryField = "lot";
System.out.println("請輸入生產批次：");
queryValue = sc.nextInt();
break;
case 2:
queryField = "id";
System.out.println("請輸入工號：");
queryValue = sc.nextInt();
break;
case 3:
queryField = "name";
System.out.println("請輸入工人姓名：");
queryValueStr = sc.next();
break;
case 4:
queryField = "outnum";
System.out.println("請輸入轉出總數：");
queryValue = sc.nextInt();
break;
case 5:
queryField = "goodnum";
System.out.println("請輸入轉出合格數：");
queryValue = sc.nextInt();
break;
case 6:
queryField = "badnum";
System.out.println("請輸入次品數：");
queryValue = sc.nextInt();
break;
case 7:
queryField = "lostnum";
System.out.println("請輸入丟失數：");
queryValue = sc.nextInt();
break;
}
try {
//載入驅動
Class.forName("com.mysql.cj.jdbc.Driver");
//獲取連線
String url = "jdbc:mysql://127.0.0.1:3306/kaoshi?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";
String username = "root";
String password = "root";
Connection conn = DriverManager.getConnection(url, username, password);
//建立會話
Statement sta = conn.createStatement();
String sql = "";
if (queryValueStr.equals("")) {
sql = "select * from t_report where " + queryField + " = " + queryValue;
} else {
sql = "select * from t_report where " + queryField + " = '" + queryValueStr + "'";
}
ResultSet rs = sta.executeQuery(sql);
if (rs.next()) {
Report r = new Report();
int lot = rs.getInt("lot");
int id = rs.getInt("id");
String name = rs.getString("name");
int outnum = rs.getInt("outnum");
int goodnum = rs.getInt("goodnum");
int badnum = rs.getInt("badnum");
int lostnum = rs.getInt("lostnum");
r.setLot(lot);
r.setId(id);
r.setName(name);
r.setOutnum(outnum);
r.setBadnum(badnum);
r.setGoodnum(goodnum);
r.setLostnum(lostnum);
System.out.println(r);
System.out.println("生產批次" + r.getLot() + ' ' + "工號" + r.getId() + ' ' +
"工人姓名" + r.getName() + ' ' + "轉出總數" + r.getOutnum() + ' ' +
"轉出合格數" + r.getGoodnum() + ' ' + "次品數" + r.getBadnum() + ' ' +
"丟失數" + r.getLostnum());
} else {
System.out.println("未找到符合條件的記錄");
}
//關閉資料庫資源
sta.close();
conn.close();
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
}
if (choice == 5) {
System.out.println("即將退出系統");
break;
}
}
}
public static Report findByLno(int lno) throws ClassNotFoundException, SQLException {
Report r = null;
//載入驅動
Class.forName("com.mysql.cj.jdbc.Driver");
//獲取連線
String url = "jdbc:mysql://127.0.0.1:3306/kaoshi?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";
String username = "root";
String password = "root";
Connection conn = DriverManager.getConnection(url, username, password);
//建立會話
Statement sta = conn.createStatement();
//傳送SQL
ResultSet rs = sta.executeQuery("select * from t_report where lot =" + lno);
//處理結果
if (rs.next()) {
int lot = rs.getInt("lot");
int id = rs.getInt("id");
String name = rs.getString("name");
int outnum = rs.getInt("outnum");
int goodnum = rs.getInt("goodnum");
int badnum = rs.getInt("badnum");
int lostnum = rs.getInt("lostnum");
//封裝
r = new Report();
r.setLot(lot);
r.setId(id);
r.setName(name);
r.setOutnum(outnum);
r.setBadnum(badnum);
r.setGoodnum(goodnum);
r.setLostnum(lostnum);
}
//關閉資料庫資源
sta.close();
conn.close();
return r;
}
public static int delByLno(int lno) throws ClassNotFoundException, SQLException {
//載入驅動
Class.forName("com.mysql.cj.jdbc.Driver");
//獲取連線
String url = "jdbc:mysql://127.0.0.1:3306/kaoshi?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";
String username = "root";
String password = "root";
Connection conn = DriverManager.getConnection(url, username, password);
//建立會話
Statement sta = conn.createStatement();
//傳送SQL
int n = sta.executeUpdate("delete from t_report where lot = " + lno);
//關閉資料庫資源
sta.close();
conn.close();
return n;
}
}