UpdateERP.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.sql.ResultSet;
import java.util.*;

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;

@Path("/updateERP")
public class UpdateERP {

    private final DBService dbService = new DBService("dsTranNJTA");

    @POST
    @Consumes(MediaType.APPLICATION_JSON)
    @Produces(MediaType.APPLICATION_JSON)
    public Response doUpdate(String jsonBody) {
        try {
            JSONObject json = new JSONObject(jsonBody);
            String station = json.getString("station");
            JSONArray dataArray = json.getJSONArray("data");

            String vendor = dbService.getDatabaseProductName();
            boolean isMSSQL = "Microsoft SQL Server".equals(vendor);
            boolean isOracle = "Oracle".equals(vendor);

            GetMetadata metadata = new GetMetadata();
            JSONObject extPlantResponse = new JSONObject(metadata.getExtPlantJson(station));

            if (!"0".equals(extPlantResponse.optString("errorCode", "0"))) {
                return Response.status(Response.Status.BAD_REQUEST).entity(extPlantResponse.toString()).build();
            }

            JSONArray werkArr = new JSONArray(extPlantResponse.optString("data", "[]"));
            String WERK_NR = null, COMPANY_NO = null, 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);
            }

            if (isMSSQL) {
                dbService.dbConnect(
                        "INSERT INTO xtran.tran_idocstatus (ID, DATE_CREATION, ewstatus, errorcode, content_type, source) VALUES ((NEXT VALUE FOR xtran.seq_tranidocstatus), getdate(), 1, 0, 61, 0)");
            } else if (isOracle) {
                dbService.dbConnect(
                        "INSERT INTO tran.tran_idocstatus (ID, DATE_CREATION, ewstatus, errorcode, content_type, source) VALUES (tran.seq_tranidocstatus.nextval, sysdate, 1, 0, 61, 0)");
            }

            Connection conn = dbService.getConnection();
            conn.setAutoCommit(false);

            try {
                Map<String, Long> groupIdMap = new HashMap<>();
                Set<String> processedGroups = new HashSet<>();

                for (int i = 0; i < dataArray.length(); i++) {
                    JSONObject obj = dataArray.getJSONObject(i);

                    // Tolerantes Auslesen
                    String groupNumber = obj.has("ERP_GROUP_NUMBER")
                            ? obj.optString("ERP_GROUP_NUMBER", null)
                            : obj.optString("erp_group_number", null);

                    // NULL oder leer überspringen
                    if (groupNumber == null || groupNumber.trim().isEmpty()) {
                        continue; // überspringe diesen Datensatz
                    }

                    if (!processedGroups.contains(groupNumber.toLowerCase())) {
                        long tranId = insertERPGroupOnly(conn, obj, CLIENT_NO, WERK_NR, COMPANY_NO, isMSSQL, isOracle);
                        groupIdMap.put(groupNumber.toLowerCase(), tranId); // optional lowercase
                        processedGroups.add(groupNumber.toLowerCase());
                    }
                }

                for (int i = 0; i < dataArray.length(); i++) {
                    JSONObject obj = dataArray.getJSONObject(i);
                    String groupNumber = obj.has("ERP_GROUP_NUMBER")
                            ? obj.optString("ERP_GROUP_NUMBER", null)
                            : obj.optString("erp_group_number", null);

                    // NULL oder leer überspringen
                    if (groupNumber == null || groupNumber.trim().isEmpty()) {
                        continue; // überspringe diesen Datensatz
                    }
                    insertERPGroupStationOnly(conn, obj, CLIENT_NO, WERK_NR, COMPANY_NO, isMSSQL, isOracle);
                }

                conn.commit();
            } catch (SQLException e) {
                conn.rollback();
                throw e;
            } finally {
                conn.setAutoCommit(true);
                conn.close();
            }

            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 long insertERPGroupOnly(Connection conn, JSONObject obj, String clientno, String plantno, String werknummer,
            boolean isMSSQL, boolean isOracle) throws SQLException {
        String timestamp = isMSSQL ? "GETDATE()" : "SYSDATE";
        String schemaPrefix = isOracle ? "tran" : "xtran";

        String seqSql = isOracle ? "SELECT tran.SEQ_TRAN_ERP_GROUP.NEXTVAL FROM DUAL"
                : "SELECT NEXT VALUE FOR xtran.SEQ_TRAN_ERP_GROUP";
        long tranId;

        try (PreparedStatement ps = conn.prepareStatement(seqSql); ResultSet rs = ps.executeQuery()) {
            if (rs.next()) {
                tranId = rs.getLong(1);
            } else {
                throw new SQLException("Fehler beim Abrufen der TRAN_ID aus SEQ_TRAN_ERP_GROUP.");
            }
        }

        String sql = "INSERT INTO " + schemaPrefix
                + ".tran_erp_group (IDOC_ID, TRAN_ID, SOURCE, STATUS, CREATED, STAMP, COMPANY_NO, CLIENT_NO, PLANT_NO, ERP_GROUP_NUMBER, ERP_GROUP_DESC, SEPARATION_FLAG, SEQUENTIAL) "
                +
                "VALUES ((SELECT MAX(id) FROM " + schemaPrefix + ".tran_idocstatus), ?, 0, 0, " + timestamp + ", "
                + timestamp + ", ?, ?, ?, ?, ?, 'N', 'J')";

        String groupNumber = obj.has("ERP_GROUP_NUMBER") ? obj.optString("ERP_GROUP_NUMBER", null)
                : obj.optString("erp_group_number", null);
        String groupDesc = obj.has("ERP_GROUP_DESC") ? obj.optString("ERP_GROUP_DESC", null)
                : obj.optString("erp_group_desc", null);

        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setLong(1, tranId);
            ps.setString(2, plantno);
            ps.setString(3, clientno);
            ps.setString(4, werknummer);
            ps.setString(5, groupNumber);
            ps.setString(6, groupDesc);
            ps.executeUpdate();
        }

        return tranId;
    }

    private void insertERPGroupStationOnly(Connection conn, JSONObject obj, String clientno, String plantno,
            String werknummer,
            boolean isMSSQL, boolean isOracle) throws SQLException {
        String timestamp = isMSSQL ? "GETDATE()" : "SYSDATE";
        String schemaPrefix = isOracle ? "tran" : "xtran";

        String seqSql = isOracle ? "SELECT tran.SEQ_TRAN_ERP_GROUP_STATION.NEXTVAL FROM DUAL"
                : "SELECT NEXT VALUE FOR xtran.SEQ_TRAN_ERP_GROUP_STATION";
        long tranId;

        try (PreparedStatement ps = conn.prepareStatement(seqSql); ResultSet rs = ps.executeQuery()) {
            if (rs.next()) {
                tranId = rs.getLong(1);
            } else {
                throw new SQLException("Fehler beim Abrufen der TRAN_ID aus SEQ_TRAN_ERP_GROUP_STATION.");
            }
        }

        String sql = "INSERT INTO " + schemaPrefix
                + ".tran_erp_group_station (IDOC_ID, TRAN_ID, SOURCE, STATUS, CREATED, STAMP, COMPANY_NO, CLIENT_NO, PLANT_NO, ERP_GROUP_NUMBER, STATION_NUMBER, STATION_TYPE, STATE, WORKORDER_UPDATE) "
                +
                "VALUES ((SELECT MAX(id) FROM " + schemaPrefix + ".tran_idocstatus), ?, 0, 0, " + timestamp + ", "
                + timestamp + ", ?, ?, ?, ?, ?, ?, 1, 0)";
        String groupNumber = obj.has("ERP_GROUP_NUMBER") ? obj.optString("ERP_GROUP_NUMBER", null)
                : obj.optString("erp_group_number", null);
        String kapnr = obj.has("KAP_NR") ? obj.optString("KAP_NR", null) : obj.optString("kap_nr", null);
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setLong(1, tranId);
            ps.setString(2, plantno);
            ps.setString(3, clientno);
            ps.setString(4, werknummer);
            ps.setString(5, groupNumber);
            ps.setString(6, kapnr);
            String stationType = obj.optString("STATION_TYPE", "T");
            if (stationType == null || stationType.trim().isEmpty()) {
                stationType = "T";
            }
            ps.setString(7, stationType);

            ps.executeUpdate();
        }
    }
}