UpdatePart.java
package com.workbenchclassic;
import org.json.JSONArray;
import org.json.JSONObject;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.HashMap;
import javax.ws.rs.Consumes;
import javax.ws.rs.POST;
import javax.ws.rs.Path;
import javax.ws.rs.Produces;
import javax.ws.rs.core.MediaType;
import javax.ws.rs.core.Response;
/**
* UpdateStation performs database insert depending on DB vendor (MSSQL or
* Oracle).
*/
@Path("/updateParts")
public class UpdatePart {
/** Our DB service for queries (JNDI, etc.). */
private final DBService dbService = new DBService("dsTranNJTA");
@POST
@Consumes(MediaType.APPLICATION_JSON)
@Produces(MediaType.APPLICATION_JSON)
public Response doUpdate(String jsonBody) {
try {
// 1) JSON parsen
JSONObject json = new JSONObject(jsonBody);
String station = json.getString("station");
JSONArray dataArray = json.getJSONArray("data");
// 2) DB-Vendor abfragen
String vendor = dbService.getDatabaseProductName();
System.out.println("Aktueller DB-Vendor: " + vendor);
GetMetadata metadata = new GetMetadata();
String extPlantResponseString = metadata.getExtPlantJson(station);
JSONObject extPlantResponse = new JSONObject(extPlantResponseString);
String errorCode = extPlantResponse.optString("errorCode", "0");
if (!"0".equals(errorCode)) {
// Return an HTTP 400 or 500 with that JSON
return Response.status(Response.Status.BAD_REQUEST).entity(extPlantResponse.toString()).build();
}
// 3) werknummer ermitteln
// werkResultJson könnte z.B. so aussehen:
// {"errorCode":"0","errorMessage":"Done","data":"[{\"werknummer\":\"ABC\"}]"}
String werkData = extPlantResponse.optString("data", "[]");
JSONArray werkArr = new JSONArray(werkData);
String WERK_NR = null;
String COMPANY_NO = null;
String CLIENT_NO = null;
if (werkArr.length() > 0) {
JSONObject row = werkArr.getJSONObject(0);
WERK_NR = row.optString("ext_company_nr", null);
COMPANY_NO = row.optString("werk_nr", null);
CLIENT_NO = row.optString("client_nr", null);
}
System.out.println("Ermittelte werknummer: " + COMPANY_NO);
boolean isMSSQL = "Microsoft SQL Server".equals(vendor);
boolean isOracle = "Oracle".equals(vendor);
// 4) ZUERST den tran_idocstatus-Eintrag anlegen
if (isMSSQL) {
String insertIdocstatusMSSQL = "INSERT INTO xtran.tran_idocstatus (ID, DATE_CREATION, ewstatus, errorcode, content_type, source) VALUES ( (NEXT VALUE FOR xtran.seq_tranidocstatus), getdate(), 1, 0, 9, 0)";
dbService.dbConnect(insertIdocstatusMSSQL);
} else if (isOracle) {
String insertIdocstatusOracle = "INSERT INTO tran.tran_idocstatus (ID, DATE_CREATION, ewstatus, errorcode, content_type, source) VALUES ( tran.seq_tranidocstatus.nextval, sysdate, 1, 0, 9, 0)";
dbService.dbConnect(insertIdocstatusOracle);
} else {
System.out.println("DB-Vendor nicht implementiert: " + vendor);
}
// 5) Für JEDES Element in "data" => Insert in material
Connection conn = dbService.getConnection();
conn.setAutoCommit(false); // Transaktion starten
try {
for (int i = 0; i < dataArray.length(); i++) {
JSONObject obj = dataArray.getJSONObject(i);
insertMaterial(conn, obj, CLIENT_NO, WERK_NR, COMPANY_NO, isMSSQL, isOracle);
}
conn.commit(); // Transaktion bestätigen
} catch (SQLException e) {
conn.rollback(); // Transaktion rückgängig machen
throw e; // Weiterleitung des Fehlers
} finally {
conn.setAutoCommit(true); // Auto-Commit wieder aktivieren
conn.close(); // Verbindung schließen
}
// 6) Erfolgreich fertig
JSONObject response = new JSONObject();
response.put("status", "ok");
response.put("message", "Daten wurden erfolgreich verarbeitet.");
return Response.ok(response.toString()).build();
} catch (Exception e) {
e.printStackTrace();
JSONObject error = new JSONObject();
error.put("status", "error");
error.put("message", e.getMessage());
return Response.status(Response.Status.INTERNAL_SERVER_ERROR).entity(error.toString()).build();
}
}
private void insertMaterial(Connection conn, JSONObject obj, String clientno, String plantno, String werknummer,
boolean isMSSQL, boolean isOracle)
throws SQLException {
String sql;
if (isMSSQL) {
sql = "INSERT INTO xtran.tran_material (TRAN_ID, ATTRIB_ID, IDOC_ID, MATERIAL_NO, MATERIAL_DESC, UNIT, PRODUCT, "
+
"INFO1, INFO2, INFO3, SOURCE, STATUS, CLIENT_NO, COMPANY_NO, PLANT_NO, MATERIAL_GRP_TYPE, MATERIAL_GRP_NO, APS_TRANSFER) "
+
"VALUES (NEXT VALUE FOR xtran.SEQ_TRANMATERIAL, -1, (SELECT MAX(id) FROM xtran.tran_idocstatus), ?,?,?,?,'', '', '', 0, 0, ? ,?,?, ?, ?, 0)";
} else if (isOracle) {
sql = "INSERT INTO tran.tran_material ("
+ " TRAN_ID, ATTRIB_ID, IDOC_ID, MATERIAL_NO, MATERIAL_DESC, UNIT, PRODUCT,"
+ " INFO1, INFO2, INFO3, SOURCE, STATUS, CLIENT_NO, COMPANY_NO, PLANT_NO,"
+ " MATERIAL_GRP_TYPE, MATERIAL_GRP_NO, APS_TRANSFER"
+ ") VALUES ("
+ " tran.SEQ_TRANMATERIAL.nextVal," // TRAN_ID per Sequence
+ " -1," // ATTRIB_ID fest -1 (wie im MSSQL)
+ " (SELECT MAX(id) FROM tran.tran_idocstatus)," // IDOC_ID
+ " ?, ?, ?, ?," // MATERIAL_NO, MATERIAL_DESC, UNIT, PRODUCT
+ " '', '', ''," // INFO1, INFO2, INFO3
+ " 0, 0," // SOURCE, STATUS
+ " ?, ?, ?," // CLIENT_NO, COMPANY_NO, PLANT_NO
+ " ?, ?," // MATERIAL_GRP_TYPE, MATERIAL_GRP_NO
+ " 0" // APS_TRANSFER
+ ")";
} else {
throw new SQLException("DB-Vendor nicht implementiert");
}
try (PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setString(1, obj.getString("MATERIAL_NO"));
ps.setString(2, obj.getString("MATERIAL_DESC"));
String unit = obj.getString("UNIT");
try {
// Versuchen, den UNIT-Wert als Integer zu parsen
Integer.parseInt(unit);
ps.setString(3, unit); // Wenn es ein Integer ist, direkt einfügen
} catch (NumberFormatException e) {
// Wenn es kein Integer ist, das Mapping anwenden
String unitLower = unit.toLowerCase();
HashMap<String, String> unitMapping = new HashMap<>();
unitMapping.put("mm", "11000");
unitMapping.put("zoll", "11001");
unitMapping.put("stk", "11002");
unitMapping.put("pcs", "11002");
unitMapping.put("qty", "11002");
unitMapping.put("cm", "11003");
unitMapping.put("m", "11004");
unitMapping.put("g", "11005");
unitMapping.put("kg", "11006");
unitMapping.put("ml", "11007");
unitMapping.put("l", "11008");
String mappedUnit = unitMapping.get(unitLower);
if (mappedUnit != null) {
ps.setString(3, mappedUnit); // Mapping-Wert verwenden
} else {
ps.setString(3, unit); // Originalwert verwenden, wenn kein Mapping gefunden wurde
}
}
ps.setString(4, obj.getString("PRODUCT"));
ps.setString(5, clientno);
ps.setString(6, plantno);
ps.setString(7, werknummer);
ps.setString(8, obj.getString("MATERIAL_GRP_TYPE"));
ps.setString(9, obj.getString("MATERIAL_GRP_NO"));
ps.executeUpdate();
} catch (SQLException e) {
// Fehlerbehandlung
e.printStackTrace();
}
}
}