UpdateStation.java
package com.workbenchclassic;
import org.json.JSONArray;
import org.json.JSONObject;
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("/updateStations")
public class UpdateStation {
/** 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"); // z.B. "TEST"
JSONArray dataArray = json.getJSONArray("data"); // Liste mit Objekten
// 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);
}
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, 60, 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, 60, 0)";
dbService.dbConnect(insertIdocstatusOracle);
} else {
System.out.println("DB-Vendor nicht implementiert: " + vendor);
}
// 5) Für JEDES Element in "data" => Insert in cell, machine_group, station
for (int i = 0; i < dataArray.length(); i++) {
JSONObject obj = dataArray.getJSONObject(i);
String lineID = obj.getString("line_nr");
String lineBez = obj.getString("line_bez");
String maGroupID = obj.getString("ma_grp_nr");
String maGroupBez = obj.getString("ma_grp_bez");
String kapID = obj.getString("kap_nr");
String kapBez = obj.getString("kap_bez");
if (isMSSQL) {
// tran_cell
String insertCellMSSQL = "INSERT INTO xtran.tran_cell "
+ "(IDOC_ID, TRAN_ID, SOURCE, STATUS, CREATED, STAMP, CLIENT_NO, COMPANY_NO, PLANT_NO, CELL_NUMBER, CELL_DESC) "
+ "VALUES ( (SELECT MAX(id) FROM xtran.tran_idocstatus),"
+ " (NEXT VALUE FOR xtran.SEQ_TRAN_CELL),"
+ " 0,0, getdate(), getdate(),"
+ " '" + (CLIENT_NO != null ? CLIENT_NO : "") + "','" + (WERK_NR != null ? WERK_NR : "")
+ "', '" + (COMPANY_NO != null ? COMPANY_NO : "") + "',"
+ " '" + lineID + "', '" + lineBez + "')";
dbService.dbConnect(insertCellMSSQL);
// tran_machine_group
String insertMachineGroupMSSQL = "INSERT INTO xtran.tran_machine_group "
+ "(IDOC_ID, TRAN_ID, SOURCE, STATUS, CREATED, STAMP, CLIENT_NO, COMPANY_NO, PLANT_NO, "
+ " PARENT_CELL_NUMBER, MACHINE_GROUP_NUMBER, MACHINE_GROUP_DESC) "
+ "VALUES ( (SELECT MAX(id) FROM xtran.tran_idocstatus),"
+ " (NEXT VALUE FOR xtran.SEQ_TRAN_MACHINE_GROUP),"
+ " 0,0, getdate(), getdate(),"
+ " '" + (CLIENT_NO != null ? CLIENT_NO : "") + "','" + (WERK_NR != null ? WERK_NR : "")
+ "', '" + (COMPANY_NO != null ? COMPANY_NO : "") + "',"
+ " '" + lineID + "', '" + maGroupID + "', '" + maGroupBez + "')";
dbService.dbConnect(insertMachineGroupMSSQL);
// tran_station
String insertStationMSSQL = "INSERT INTO xtran.tran_station "
+ "(IDOC_ID, TRAN_ID, SOURCE, STATUS, CREATED, STAMP, CLIENT_NO, COMPANY_NO, PLANT_NO, "
+ " PARENT_MACHINE_GROUP_NUMBER, STATION_NUMBER, STATION_DESC, STATION_TYPE) "
+ "VALUES ( (SELECT MAX(id) FROM xtran.tran_idocstatus),"
+ " (NEXT VALUE FOR xtran.SEQ_TRAN_STATION),"
+ " 0,0, getdate(), getdate(),"
+ " '" + (CLIENT_NO != null ? CLIENT_NO : "") + "','" + (WERK_NR != null ? WERK_NR : "")
+ "', '" + (COMPANY_NO != null ? COMPANY_NO : "") + "',"
+ " '" + maGroupID + "', '" + kapID + "', '" + kapBez + "', 'B')";
dbService.dbConnect(insertStationMSSQL);
} else if (isOracle) {
// tran_cell
String insertCellOracle = "INSERT INTO tran.tran_cell "
+ "(IDOC_ID, TRAN_ID, SOURCE, STATUS, CREATED, STAMP, CLIENT_NO, COMPANY_NO, PLANT_NO, CELL_NUMBER, CELL_DESC) "
+ "VALUES ( (SELECT MAX(id) FROM tran.tran_idocstatus), "
+ " tran.seq_tran_cell.nextval, 0,0, sysdate, sysdate, "
+ " '" + (CLIENT_NO != null ? CLIENT_NO : "") + "','" + (WERK_NR != null ? WERK_NR : "")
+ "', '" + (COMPANY_NO != null ? COMPANY_NO : "") + "',"
+ " '" + lineID + "', '" + lineBez + "')";
dbService.dbConnect(insertCellOracle);
// tran_machine_group
String insertMachineGroupOracle = "INSERT INTO tran.tran_machine_group "
+ "(IDOC_ID, TRAN_ID, SOURCE, STATUS, CREATED, STAMP, CLIENT_NO, COMPANY_NO, PLANT_NO, "
+ " PARENT_CELL_NUMBER, MACHINE_GROUP_NUMBER, MACHINE_GROUP_DESC) "
+ "VALUES ( (SELECT MAX(id) FROM tran.tran_idocstatus), "
+ " tran.seq_tran_machine_group.nextval, 0,0, sysdate, sysdate, "
+ " '" + (CLIENT_NO != null ? CLIENT_NO : "") + "','" + (WERK_NR != null ? WERK_NR : "")
+ "', '" + (COMPANY_NO != null ? COMPANY_NO : "") + "',"
+ " '" + lineID + "', '" + maGroupID + "', '" + maGroupBez + "')";
dbService.dbConnect(insertMachineGroupOracle);
// tran_station
String insertStationOracle = "INSERT INTO tran.tran_station "
+ "(IDOC_ID, TRAN_ID, SOURCE, STATUS, CREATED, STAMP, CLIENT_NO, COMPANY_NO, PLANT_NO, "
+ " PARENT_MACHINE_GROUP_NUMBER, STATION_NUMBER, STATION_DESC, STATION_TYPE) "
+ "VALUES ( (SELECT MAX(id) FROM tran.tran_idocstatus), "
+ " tran.seq_tran_station.nextval, 0,0, sysdate, sysdate, "
+ " '" + (CLIENT_NO != null ? CLIENT_NO : "") + "','" + (WERK_NR != null ? WERK_NR : "")
+ "', '" + (COMPANY_NO != null ? COMPANY_NO : "") + "',"
+ " '" + maGroupID + "', '" + kapID + "', '" + kapBez + "', 'B')";
dbService.dbConnect(insertStationOracle);
} else {
System.out.println("Anderer/nicht implementierter DB-Vendor: " + vendor);
}
}
// 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();
}
}
}