importPackage(com.itac.mes.api.custom);
importPackage(java.lang);
importPackage(java.util);
importPackage(java.text);
importPackage(com.itac.mes.imsapi.domain.container);
importPackage(com.itac.persistence.producer);
importPackage(javax.inject.inject);
importPackage(javax.naming);
importPackage(java.io);
importPackage(javax.resource);
importPackage(javax.sql);
importPackage(javax.naming);

function getFailuresForStation(stationNumber){
  var failureDataResultKeys =["FAILURE_TYPE_CODE","FAILURE_TYPE_DESC" ]
 var result_mdataGetFailureDataForStation = imsApiService.mdataGetFailureDataForStation(imsApiSessionContext,
            stationNumber,
            0,
failureDataResultKeys)

var result = _createStructuredResults(result_mdataGetFailureDataForStation.failureDataResultValues, failureDataResultKeys);
var resultjson = JSON.stringify(result);
return _getErrorForText("0", "Done", [resultjson]);

}

function getActivities(stationNumber, workorder){


    var workplanFilter = new Array(
            new KeyValue("FUNC_MODE", 0),
            new KeyValue("WORKORDER_NUMBER", workorder),
            new KeyValue("STATION_BASED_WORKSTEPS", 0)
        );
         var workplanDataResultHMI = [ "AVO",  "Arbeitsschritt", "Prozesschritt", "Beschreibung",  "Rueckmeldepflichtig", "Artikelnummer", "Skill"];
        var workplanDataResultKeys = [ "WORKSTEP_AVO",  "WORKSTEP_NUMBER", "PROCESS_LAYER", "WORKSTEP_DESC", "OBLIGATORY_CONFIRM_FLAG", "PART_NUMBER", "SKILL_LEVEL"];
        var result_mdataGetWorkplanData = imsApiService.mdataGetWorkplanData(
            imsApiSessionContext,
            stationNumber, // String
            workplanFilter, // KeyValue[]
            workplanDataResultKeys // String[]
        );
var result = _createStructuredResults(result_mdataGetWorkplanData.workplanDataResultValues, workplanDataResultHMI);
var resultWorkplan = JSON.stringify(result);
var partNumber = result[0].Artikelnummer;
var recipe = _getRecipe(stationNumber, partNumber)
var resultrecipe = JSON.stringify(recipe);

return _getErrorForText("0", "Done", [resultWorkplan, resultrecipe]);

}

function _getRecipe(stationNumber, part){
var recipeResult=[  "MEASURE_NAME", "FUNCTION", "FUNCTION_DESC",
      "FUNCTION_STEP", "FUNCTION_STEP_DESC",
      "LOWER_LIMIT", "UPPER_LIMIT", "NOMINAL", "UNIT" ]

var recipeResultKeys =[  "MEASURE_NAME", "FUNCTION", "FUNCTION_DESC",
      "FUNCTION_STEP", "FUNCTION_STEP_DESC",
      "MIN_VALUE", "MAX_VALUE", "NOMINAL", "UNIT" ]
 var resultRezept = imsApiService.mdaGetRecipeData(
            imsApiSessionContext,
            stationNumber, // String
            "-1", 
            "-1", 
            "-1", 
            "-1", 
            "-1", 
            "-1", 
            "-1",
            "1",
            [new KeyValue("PART_NUMBER", part), new KeyValue("STATION_NUMBER", stationNumber)],
            recipeResultKeys)

      var tempresult = _createStructuredResults(resultRezept.recipeResultValues, recipeResult)     
      return tempresult; 
};


function _errorText(retCode){
     result = imsApiService.imsapiGetErrorText(imsApiSessionContext, retCode);
return result.errorString;
}
function _getErrorForText(returnValue, errorString, outArgs) {
  var returnvalue = parseInt(returnValue);
  var result = new Result_customFunctionCommon();
    result.outArgs = new Array(outArgs.length);
    result.return_value = returnvalue;
    result.errorString = errorString;
    for (var i = 0; i < outArgs.length; i++) {
        result.outArgs[i] = outArgs[i];
    }
    return result;
}
function _createStructuredResults(valuesArray, resultKeys) {
    var structuredResults = [];

    // Fall: Objekt mit numerischen Keys, das in ein Array gewandelt werden muss
    if (typeof valuesArray === "object" && !Array.isArray(valuesArray) && valuesArray !== null) {
        logHandler.logMessage("valuesArray ist ein Objekt - Versuch, daraus flaches Array zu generieren mit resultKeys.");

        var tempArray = [];
        for (var i = 0; valuesArray.hasOwnProperty(i); i++) {
            tempArray.push("" + valuesArray[i]);  // Werte als Strings sichern
        }

        // JSON-Struktur aufbauen, wenn resultKeys vorhanden sind
        if (Array.isArray(resultKeys) && resultKeys.length > 0) {
            var entrySize = resultKeys.length;

            for (var i = 0; i < tempArray.length; i += entrySize) {
                var entry = {};
                for (var j = 0; j < entrySize && (i + j) < tempArray.length; j++) {
                    entry[resultKeys[j]] = "" + tempArray[i + j];
                }
                structuredResults.push(entry);
            }

            logHandler.logMessage("JSON wurde direkt aus Object und resultKeys generiert.");
            return structuredResults;
        } else {
            logHandler.logMessage("Keine resultKeys vorhanden - Rückgabe als flaches Array.");
            return tempArray;
        }
    }

    // Fall: Bereits ein Array von Objekten
    if (Array.isArray(valuesArray) && valuesArray.length > 0 && typeof valuesArray[0] === "object") {
        return valuesArray;
    }

    // Fall: Flaches echtes Array mit resultKeys
    if (Array.isArray(valuesArray) && valuesArray.length > 0 && Array.isArray(resultKeys) && resultKeys.length > 0) {
        var entrySize = resultKeys.length;
        for (var i = 0; i < valuesArray.length; i += entrySize) {
            var entry = {};
            for (var j = 0; j < entrySize && (i + j) < valuesArray.length; j++) {
                entry[resultKeys[j]] = "" + valuesArray[i + j];
            }
            structuredResults.push(entry);
        }
        return structuredResults;
    }

    logHandler.logMessage("valuesArray ist leer oder unbrauchbar.");
    return [];
}

function getToDOs(stationNumber, serialNumber) {

  ///////////////////////////////////////////////////////////////
  // Helper (iTAC-safe)
  ///////////////////////////////////////////////////////////////
  function safeResults(values, keys) {
    if (!values || !values.length) return [];
    return _createStructuredResults(values, keys);
  }

  function safeStringify(obj) {
    try {
      return JSON.stringify(obj || []);
    } catch (e) {
      return "[]";
    }
  }
try{
  ///////////////////////////////////////////////////////////////
  // Result Keys
  ///////////////////////////////////////////////////////////////
  var bookingResultKeys = [
    "SERIAL_NUMBER","SERIAL_NUMBER_POS","STATION_DESC","STATION_DIAG_NUMBER",
    "STATION_NUMBER","WORKORDER_NUMBER","WORKSTEP_DESC","WORKSTEP_NUMBER",
    "BOOK_DATE","BOOK_STATE","CYCLE_TIME","PROCESS_LAYER"
  ];

  var failureDataResultKeys = [
    "BOOK_REFERENCE","COMP_NAME","FAILURE_ACTION_CODE","FAILURE_ACTION_INFO",
    "FAILURE_CAUSE_CODE","FAILURE_CAUSE_DESC","FAILURE_CAUSE_INFO",
    "FAILURE_IMAGE_CODE","FAILURE_IMAGE_DESC","FAILURE_MEASURE_CODE",
    "FAILURE_MEASURE_INFO","FAILURE_SEQUENCE_NUMBER","FAILURE_TYPE_CODE",
    "FAILURE_TYPE_DESC","FAILURE_TYPE_INFO","INFO","INFO_TXT_REPAIR",
    "MAJOR_FAILURE","PART_DESC","PART_NUMBER","REPAIRED","REPAIRED_DATE",
    "SERIAL_NUMBER","SERIAL_NUMBER_POS","STATION_DIAG_NUMBER",
    "STATION_NUMBER","STATION_REPAIR_DESC","WORKORDER_NUMBER"
  ];

  var failureSlipDataResultKeys = [
    "BOOK_DATE","BOOK_REFERENCE","COMP_NAME","FAILURE_SLIP_REFERENCE",
    "SEQUENCE_NUMBER","STATION_NUMBER","TEST_STEP_NAME",
    "WORKORDER_NUMBER","WORKSTEP_NUMBER"
  ];

  var measureDataResultKeys = [
    "FUNCTION","FUNCTION_DESC","FUNCTION_STEP","LOWER_LIMIT",
    "MEASURE_FAIL_CODE","MEASURE_NAME","MEASURE_TYPE","MEASURE_VALUE",
    "NOMINAL","SEQUENCE_NUMBER","SERIAL_NUMBER","SERIAL_NUMBER_POS",
    "STATION_DESC","STATION_NUMBER","TOLERANCE","UNIT","UPPER_LIMIT",
    "WORKORDER_NUMBER","WORKSTEP_NUMBER"
  ];

  var mergedPartsKeys = [
    "SERIAL_NUMBER","SERIAL_NUMBER_POS","SERIAL_SLAVE_NUMBER",
    "PART_NUMBER","PART_DESC","BOM_VERSION","BOM_VARIANT",
    "STATION_NUMBER","WORKORDER_NUMBER"
  ];

  var setupResultKeys = [
    "COMP_NAME","PART_NUMBER","MATERIAL_BIN_NUMBER","BOOK_DATE",
    "SETUP_POSITION", "WORKSTEP_NUMBER","STATION_NUMBER","STATION_DESC","WORKORDER_NUMBER"
  ];

  ///////////////////////////////////////////////////////////////
  // Serial History
  ///////////////////////////////////////////////////////////////
var result = imsApiService.trGetSerialNumberHistoryData(
  imsApiSessionContext,
  stationNumber,
  serialNumber,
  -1, // serialNumberPos
  2,  // processLayer
  1,  // desolvingSerialNumber
  0,  // desolvingLevel
  bookingResultKeys,
  failureDataResultKeys,
  failureSlipDataResultKeys,
  measureDataResultKeys
);
var layer =_getConfig(stationNumber);

var result_setupGetSetupDataBySerialNumber =imsApiService.setupGetSetupDataBySerialNumber( imsApiSessionContext, stationNumber, serialNumber,"-1", "-1", "-1", setupResultKeys );


  ///////////////////////////////////////////////////////////////
  // Safe Result Parsing
  ///////////////////////////////////////////////////////////////
  var bookingResult = safeResults(result.bookingResultValues, bookingResultKeys);
  var failureResult = safeResults(result.failureDataResultValues, failureDataResultKeys);
  var failureSlip   = safeResults(result.failureSlipDataResultValues, failureSlipDataResultKeys);
  var measurement   = safeResults(result.measureDataResultValues, measureDataResultKeys);
  var setup         = safeResults(result_setupGetSetupDataBySerialNumber.setupResultValues, setupResultKeys);
  var attributes = _attributes(stationNumber, result.workOrderNumber);
  var attributesSNR = _attributesSNR(stationNumber,serialNumber);
  var snrHist = _getSerialNumberHistoryDataBySql(serialNumber);

  var workorder = result.workOrderNumber;
  if (!workorder) {
    return _getErrorForText(result.return_value, _errorText(result.return_value), []);
  }

  ///////////////////////////////////////////////////////////////
  // Workplan
  ///////////////////////////////////////////////////////////////
  var workplanFilter = [
    new KeyValue("FUNC_MODE", 0),
    new KeyValue("WORKORDER_NUMBER", workorder),
    new KeyValue("WORKSTEP_FLAG", 1)
  ];

  var workplanDataResultHMI = [
    "AVO","Arbeitsschritt","Prozesslage", "Beschreibung",
    "Rueckmeldepflichtig","Artikelnummer","Skill"
  ];

  var workplanDataResultKeys = [
    "WORKSTEP_AVO","WORKSTEP_NUMBER","PROCESS_LAYER", "WORKSTEP_DESC",
    "OBLIGATORY_CONFIRM_FLAG","PART_NUMBER","SKILL_LEVEL"
  ];

  var wpResult = imsApiService.mdataGetWorkplanData(
    imsApiSessionContext,
    stationNumber,
    workplanFilter,
    workplanDataResultKeys
  );

  var resultwp = safeResults(wpResult.workplanDataResultValues, workplanDataResultHMI);

  if (!resultwp.length) {
    return _getErrorForText(wpResult.return_value, _errorText(wpResult.return_value), []);
  }

  ///////////////////////////////////////////////////////////////
  // Recipe (safe)
  ///////////////////////////////////////////////////////////////
  var partNumber = resultwp[0].Artikelnummer;
  var recipe = [];

  try {
    if (partNumber) {
      recipe = _getRecipe(stationNumber, partNumber);
    }
  } catch (e) {
    recipe = [];
    logHandler.logMessage("getRecipe failed: " + e);
  }

  ///////////////////////////////////////////////////////////////
  // SetupCheck
  ///////////////////////////////////////////////////////////////


  //var result_setupCheck = imsApiService.setupCheck(
  //  imsApiSessionContext,
  //  stationNumber,
  //  "-1",
  //  "-1",
  //  workorder,
  //  "-1",
  //  0,
  //  2
 // );
var result_setupCheck =
  imsApiService.setupGetMaterialSetup(
    imsApiSessionContext,
    stationNumber,
    "-1",
    0,
    "-1",
    "-1",
    [
      "PART_NUMBER",
      "MATERIAL_BIN_NUMBER",
      "MATERIAL_BIN_QTY_ACTUAL",
      "MATERIAL_BIN_STATE",
      "EXPIRATION_DATE",
      "SETUP_POSITION"
    ],
    []
  );

var setupOutput = [];

if (
  result_setupCheck.setupResultValues &&
  result_setupCheck.setupResultValues.length > 0
) {

  var values = result_setupCheck.setupResultValues;
  var COLS = 6; // Anzahl der ResultKeys

  if (values.length % COLS !== 0) {
    throw "setupGetMaterialSetup: Value count mismatch";
  }

  for (var i = 0; i < values.length; i += COLS) {

    setupOutput.push({
      partNumber: String(values[i]).trim(),
      materialBinNumber: String(values[i + 1]).trim(),
      materialBinQtyActual: String(values[i + 2]).trim(),
      materialBinState: String(values[i + 3]).trim(),
      expirationDate: String(values[i + 4]).trim(),
      setupPosition: String(values[i + 5]).trim()
    });
  }
}


  var bomDataResultKeys =  ["COMP_NAME", "PART_NUMBER", "PART_DESC", "PROCESS_LAYER","QUANTITY", "PRODUCT_FLAG", "SETUP_FLAG", "UNIT", "WORKSTEP_NUMBER" ,"SETUP_POSITION"];
var result_bomData = imsApiService.mdataGetBomData(
    imsApiSessionContext,
    stationNumber,
     [  new KeyValue("SERIAL_NUMBER", serialNumber)],
   bomDataResultKeys
  );
var createBomStructResults= _createStructuredResults(result_bomData.bomDataResultValues,bomDataResultKeys );





  
var setupOutputMerged= _mergeSetupToBom(createBomStructResults, setupOutput);
  ///////////////////////////////////////////////////////////////
  // Final Return (safe)
  ///////////////////////////////////////////////////////////////
  return _getErrorForText(
    "0",
    "Done",
    [
      safeStringify(resultwp),
      safeStringify(recipe),
      safeStringify(bookingResult),
      safeStringify(failureResult),
      safeStringify(failureSlip),
      safeStringify(measurement),
      safeStringify(setup),
      safeStringify(setupOutputMerged),
      safeStringify(attributes),
      safeStringify(attributesSNR),
      JSON.stringify(snrHist),
            result_setupCheck.return_value,
            layer

    ]
  );
}catch (e){
    return _getErrorForText(
    "-1",
    "error",
    [e])
}
}



function clearSetup(stationNumber){
 var result_trGSS = imsApiService.trGetStationSetting(
                imsApiSessionContext,
                stationNumber,
                ["PART_NUMBER", "BOM_VERSION_ERP", "PROCESS_LAYER", "WORKORDER_NUMBER", "QUANTITY"]
              );

              if (result_trGSS.return_value != 0) {
                Result(result_trGSS.return_value, getImsApiErrorText(result_trGSS.return_value), "")
              }
  var result_setupStateChange = imsApiService.setupStateChange(imsApiSessionContext,
    stationNumber,
    result_trGSS.stationSettingResultValues[2],
    result_trGSS.stationSettingResultValues[3],
    "-1",
    -1,
    2);

   return _getErrorForText("0", "Done", []);


}

function activateSetup(stationNumber){
  var result_trGSS = imsApiService.trGetStationSetting(
                imsApiSessionContext,
                stationNumber,
                ["PART_NUMBER", "BOM_VERSION_ERP", "PROCESS_LAYER", "WORKORDER_NUMBER", "QUANTITY"]
              );

              if (result_trGSS.return_value != 0) {
                Result(result_trGSS.return_value, getImsApiErrorText(result_trGSS.return_value), "")
              }
    var result_setupStateChange = imsApiService.setupStateChange(imsApiSessionContext,
    stationNumber,
    result_trGSS.stationSettingResultValues[2],
    result_trGSS.stationSettingResultValues[3],
        "-1",
        -1,
        0);
if (result_setupStateChange.return_value != 0) {
               return  _getErrorForText(result_setupStateChange.return_value, getImsApiErrorText(result_setupStateChange.return_value), "")
              }
  return _getErrorForText("0", "Done", []);


}

function setSetup(stationNumber,uniqueid, pos , mode){
    var setupstate = 0;
  if (mode==="setup"){
setupstate = 0
  } else {
   setupstate = 1 
  }
      var result_setupUpdateMaterialSetup = imsApiService.setupUpdateMaterialSetup(
        imsApiSessionContext,
        stationNumber,
        -1,
        "-1",
        "-1",
        "SMT",
        ["ERROR_CODE", "MATERIAL_BIN_NUMBER", "SETUP_POSITION", "SETUP_STATE"],
        [0, uniqueid, pos, setupstate],
        ["COMP_REFERENCE"],
        []);

        
 
    if (result_setupUpdateMaterialSetup.return_value != 0) {
               return  _getErrorForText(result_setupUpdateMaterialSetup.return_value, getImsApiErrorText(result_setupUpdateMaterialSetup.return_value), "")
              }
  return _getErrorForText("0", "Done", []);




}


function _getConfig(stationNumber) {
  var result = imsApiService.mdataGetStationConfiguration(
    imsApiSessionContext,
    stationNumber,
    [new KeyValue("CODE_NUMBER", 8310)],
    ["CODE_VALUE", "IS_USED"],
    [],
    []
  );

  var values = result.stationConfigCodeResultValues;

  if (values && values.length >= 2) {
    var codeValue = String(values[0]).trim();
    var isUsed = Number(values[1]);

    logHandler.logMessage(
      "used : " + isUsed + " | codeValue : " + codeValue
    );

    if (isUsed === 1 && codeValue === "A") {
      logHandler.logMessage("Return : A");
      return "A";
    }
  }

  logHandler.logMessage("Return : L");
  return "L";
}


   function getImsApiErrorText(retCode) {
      logHandler.logMessage(
        "ClientController.getImsApiErrorText() Code: " + retCode
      );
      var errorText = "";
      var result;
      try {
        result = imsApiService.imsapiGetErrorText(imsApiSessionContext, retCode);
      } catch (th) {
        logHandler.logMessage("custom function resetPipe failed: " + th);
        result.outArgs = logHandler.getMessages();
        result.return_value = 0;
        result.errorString = th;
        return result;
      }
      if (result != null) {
        errorText = result.errorString;
      }
      return errorText;
    }

function activateWorkorder(stationNumber, snr, avo, layer){
        var result_trGSNI = imsApiService.trGetSerialNumberInfo(
          imsApiSessionContext,
          stationNumber,
          snr,
          "-1",
          ["WORKORDER_NUMBER"]
        );
var wo  = result_trGSNI.serialNumberResultValues[0];
var layermapping;
 var config = String(_getConfig(stationNumber)).trim();
  if (config === "A") {
    layermapping = String(avo).trim();
  } else {
    layermapping = String(layer).trim();
  }

var result_trGetStationSetting= imsApiService.trGetStationSetting(imsApiSessionContext,
                    stationNumber,
                    ["WORKORDER_NUMBER", "PROCESS_LAYER"]);
                var wostation = String(result_trGetStationSetting.stationSettingResultValues[0]);
                var layerStation = String(result_trGetStationSetting.stationSettingResultValues[0]);
if (wo !=wostation && layerStation!=layermapping){
    var result_trActivateWorkOrder = imsApiService.trActivateWorkOrder(imsApiSessionContext,
       stationNumber,
       wo,
        "-1",
        "-1",
       layermapping,
        1);
 return _getErrorForText(result_trActivateWorkOrder.return_value, "Done", []);
    } else {
       return _getErrorForText(0, "Done", []);
    }
}

function _mergeSetupToBom(bomResults, setupOutput) {
logHandler.logMessage("setupOutput.length:"+ setupOutput.length);
logHandler.logMessage("bomResults.length:"+ bomResults.length);
  for (var i = 0; i < bomResults.length; i++) {
    var bom = bomResults[i];
    var partNumber = String(bom.PART_NUMBER).trim();

    var matches = [];

    for (var j = 0; j < setupOutput.length; j++) {
      var s = setupOutput[j];
      var spart = String(s.partNumber);
      logHandler.logMessage("Setuppart " +s.partNumber + "| BomPart :" +partNumber)
      if (spart === partNumber) {
        matches.push(s);
      }
    }

    if (matches.length > 0) {
      var positions = [];
      var binNumbers = [];
      var binQtys = [];
      var binStates = [];
      var expirationDates = [];

      for (var k = 0; k < matches.length; k++) {
        var m = matches[k];
        positions.push(m.setupPosition);
        binNumbers.push(m.materialBinNumber);
        binQtys.push(m.materialBinQtyActual);
        binStates.push(m.materialBinState);
        expirationDates.push(m.expirationDate);
      }

      bom.SETUP_POSITIONS = positions.join(", ");
      bom.MATERIAL_BIN_NUMBERS = binNumbers.join(", ");
      bom.MATERIAL_BIN_QTYS = binQtys.join(", ");
      bom.MATERIAL_BIN_STATES = binStates.join(", ");
      bom.EXPIRATION_DATES = expirationDates.join(", ");
    } else {
      bom.SETUP_POSITIONS = "";
      bom.MATERIAL_BIN_NUMBERS = "";
      bom.MATERIAL_BIN_QTYS = "";
      bom.MATERIAL_BIN_STATES = "";
      bom.EXPIRATION_DATES = "";
    }
  }

  return bomResults;
}






function _attributes(stationNumber, workorderNumber){

var wpIDresult = imsApiService.mdataGetWorkplanData(imsApiSessionContext, stationNumber, [new KeyValue("WORKORDER_NUMBER", workorderNumber)],["WORKPLAN_ID"]);
var wpID = wpIDresult.workplanDataResultValues[wpIDresult.workplanDataResultValues.length -1 ];


var attributeResult = ["ATTRIBUTE_CODE", "ATTRIBUTE_VALUE", "WORKSTEP_AVO", "WORKSTEP_NUMBER", "ERROR_CODE"];

  var attribGetAttributeCode_result = imsApiService.attribGetAttributeValues(imsApiSessionContext,
       stationNumber,
       12,
      wpID,
       -1,
      [],
      0,
      attributeResult);


     

var structAtrrib = _createStructuredResults(attribGetAttributeCode_result.attributeResultValues, attributeResult);



    return structAtrrib;

}


function _attributesSNR(stationNumber, serialNumber){


var attributeResult = ["ATTRIBUTE_CODE", "ATTRIBUTE_VALUE", "ERROR_CODE"];

  var attribGetAttributeCode_result = imsApiService.attribGetAttributeValues(imsApiSessionContext,
       stationNumber,
       0,
      serialNumber,
       -1,
      [],
      0,
      attributeResult);


     

var structAtrrib = _createStructuredResults(attribGetAttributeCode_result.attributeResultValues, attributeResult);



    return structAtrrib;

}



function _getSerialNumberHistoryDataBySql(refSNR) {
    var connection = null;
    var pstmt = null;
    var rs = null;

    var serialNumberArray = [];

    try {
        if (!refSNR) {
            return serialNumberArray;
        }

        // DB-Verbindung
        var InitialContext = javax.naming.InitialContext;
        var ctx = new InitialContext();
        var access = ctx.doLookup("dsMesMiiNJTA");
        connection = access.getConnection();

        // SQL holen
        var sql = _buildSerialHistorySql();

        pstmt = connection.prepareStatement(sql);

        // Parameter binden
        var paramIndex = 1;
        pstmt.setString(paramIndex++, String(refSNR));

        rs = pstmt.executeQuery();

        while (rs.next()) {
            var snr = rs.getString("SNR");
            var lastBookStation = rs.getString("STATION_NUMBER");
            var bookState = rs.getString("BOOK_STATE");
            var positionFromDb = rs.getString("POSITION");
            var ws = rs.getString("ARBEITSSCHRITT");

            serialNumberArray.push({
                serialNumber: String(snr),
                serialNumberPos: String(positionFromDb),
                bookState: String(bookState),
                workstep: String(ws),
                lastBookStation: String(lastBookStation)
            });
        }

        return serialNumberArray;



    } finally {
        if (rs !== null) try { rs.close(); } catch (e) {}
        if (pstmt !== null) try { pstmt.close(); } catch (e) {}
        if (connection !== null) try { connection.close(); } catch (e) {}
    }
}

function _buildSerialHistorySql() {
    return ""
        + "SELECT "
        + "    t.STATION_NUMBER, "
        + "    t.SNR, "
        + "    t.POSITION, "
        + "    t.BOOK_STATE, "
        + "    t.ARBEITSSCHRITT "
        + "FROM ( "
        + "    SELECT "
        + "        kst.KAP_NR AS STATION_NUMBER, "
        + "        cs.SNR_EXT AS SNR, "
        + "        cs.NUTZEN_POS AS POSITION, "
        + "        qs.AS_NR AS ARBEITSSCHRITT, "
        + "        (qs.GUT_NA - 10001) AS BOOK_STATE, "
        + "        ROW_NUMBER() OVER ( "
        + "            PARTITION BY cs.SNR_EXT, qs.AS_NR "
        + "            ORDER BY qs.SEQ_NR DESC "
        + "        ) AS RN "
        + "    FROM glo.qs_haupt qs "
        + "    JOIN glo.charge_snr cs "
        + "        ON cs.SNR_ID = qs.SNR_ID "
        + "    JOIN bde.kast kst "
        + "        ON kst.KAP_ID = qs.KAP_ID "
        + "    WHERE cs.AKTIV = 'J' "
        + "      AND cs.REF_SNR_ID = ( "
        + "            SELECT REF_SNR_ID "
        + "            FROM glo.charge_snr "
        + "            WHERE SNR_EXT = ? "
        + "      ) "
        + ") t "
        + "WHERE t.RN = 1 "
        + "ORDER BY t.SNR, t.ARBEITSSCHRITT";
}


function searchMeasurementData(inArgs) {

    var payload = JSON.parse(inArgs);

    var partNumber = payload.partNumber || "";
    var workorderNumber = payload.workorderNumber || "";

    var startDate = payload.startDate ? java.sql.Timestamp.valueOf(payload.startDate) : null;
    var endDate = payload.endDate ? java.sql.Timestamp.valueOf(payload.endDate) : null;


    var connection = null;
    var pstmt = null;
    var rs = null;

    var resultList = [];
function getStringSafe(rs, column) {
    var val = rs.getObject(column);
    return val != null ? String(val) : null;
}
    try {
        var InitialContext = javax.naming.InitialContext;
        var ctx = new InitialContext();
        var access = ctx.doLookup("dsMesMiiNJTA");
        connection = access.getConnection();

        var sql = ""
            + "SELECT "
            + "  STATION_NUMBER, STATION_DESC, PART_NUMBER, WORKORDER_NUMBER, "
            + "  WORKORDER_DESC, SEQUENCE_NUMBER, BOOK_DATE, MEASURE_STEP_NUMBER, "
            + "  MEASURE_NAME, UNIT, MEASURE_VALUE, LOWER_LIMIT, UPPER_LIMIT, "
            + "  NOMINAL, TOLERANCE, MEASURE_FAIL_CODE, MEASURE_TYPE "
            + "FROM IMS_VIEW.V_MEASUREMENT_DATA "
            + "WHERE 1 = 1 ";

        var params = [];

        if (partNumber != null && String(partNumber).trim() !== "") {
            sql += " AND PART_NUMBER = ? ";
            params.push({ type: "string", value: String(partNumber).trim() });
        }

        if (workorderNumber != null && String(workorderNumber).trim() !== "") {
            sql += " AND WORKORDER_NUMBER = ? ";
            params.push({ type: "string", value: String(workorderNumber).trim() });
        }

        if (startDate != null && endDate != null) {
            sql += " AND BOOK_DATE BETWEEN ? AND ? ";
            params.push({ type: "timestamp", value: new java.sql.Timestamp(startDate.getTime()) });
            params.push({ type: "timestamp", value: new java.sql.Timestamp(endDate.getTime()) });
        } else if (startDate != null) {
            sql += " AND BOOK_DATE >= ? ";
            params.push({ type: "timestamp", value: new java.sql.Timestamp(startDate.getTime()) });
        } else if (endDate != null) {
            sql += " AND BOOK_DATE <= ? ";
            params.push({ type: "timestamp", value: new java.sql.Timestamp(endDate.getTime()) });
        }

        sql += " ORDER BY BOOK_DATE ASC";

        pstmt = connection.prepareStatement(sql);

        for (var i = 0; i < params.length; i++) {
            var paramIndex = i + 1;
            if (params[i].type === "string") {
                pstmt.setString(paramIndex, params[i].value);
            } else if (params[i].type === "timestamp") {
                pstmt.setTimestamp(paramIndex, params[i].value);
            }
        }

        rs = pstmt.executeQuery();

        while (rs.next()) {
          resultList.push({
    stationNumber: getStringSafe(rs, "STATION_NUMBER"),
    stationDesc: getStringSafe(rs, "STATION_DESC"),
    partNumber: getStringSafe(rs, "PART_NUMBER"),
    workorderNumber: getStringSafe(rs, "WORKORDER_NUMBER"),
    workorderDesc: getStringSafe(rs, "WORKORDER_DESC"),
    sequenceNumber: getStringSafe(rs, "SEQUENCE_NUMBER"),
    bookDate: getStringSafe(rs, "BOOK_DATE"),
    measureStep: getStringSafe(rs, "MEASURE_STEP_NUMBER"),
    measureName: getStringSafe(rs, "MEASURE_NAME"),
    unit: getStringSafe(rs, "UNIT"),
    measureValue: getStringSafe(rs, "MEASURE_VALUE"),
    lowerLimit: getStringSafe(rs, "LOWER_LIMIT"),
    upperLimit: getStringSafe(rs, "UPPER_LIMIT"),
    nominal: getStringSafe(rs, "NOMINAL"),
    tolerance: getStringSafe(rs, "TOLERANCE"),
    failCode: getStringSafe(rs, "MEASURE_FAIL_CODE"),
    measureType: getStringSafe(rs, "MEASURE_TYPE")
});
        }

       return  _getErrorForText(
    "0",
    "Done",
    [JSON.stringify(resultList)]) ;

    } catch (e) {
        throw new Error("Fehler in _searchMeasurementData: " + e);
    } finally {
        if (rs !== null) {
            try { rs.close(); } catch (e1) {}
        }
        if (pstmt !== null) {
            try { pstmt.close(); } catch (e2) {}
        }
        if (connection !== null) {
            try { connection.close(); } catch (e3) {}
        }
    }
}