// --------------------------------------------------------------------------------------------------------
// ValueSets_Common.groovy 
// --------------------------------------------------------------------------------------------------------
//
// Grrovy Wertemengen Übersetzungen
//
// --------------------------------------------------------------------------------------------------------
//
// Historie:
// 26.06.2017  phoer/dvelop   Erste Version zur Umstellung der dynamischen Wertemengen und Übersetzung
//
// --------------------------------------------------------------------------------------------------------

import com.dvelop.d3.server.Entrypoint;
import com.dvelop.d3.server.core.D3Interface;
import com.dvelop.d3.server.Document;
import com.dvelop.d3.server.User;
import com.dvelop.d3.server.DocumentType;
import com.dvelop.d3.server.RepositoryField;
import com.dvelop.d3.server.UserOrUserGroup;
import com.dvelop.d3.server.ValueSet;
import com.dvelop.d3.server.ValueSetTranslation;
import com.dvelop.d3.server.Validation;
import com.dvelop.d3.server.Translation;
import com.dvelop.d3.server.*;

// zusätzliche Imports für oData Zugriff 21.01.2019
import java.io.InputStreamReader;
import java.io.Reader;
import java.net.URL;
import java.net.URLConnection;
import javax.net.ssl.HostnameVerifier;
import javax.net.ssl.HttpsURLConnection;
import javax.net.ssl.SSLContext;
import javax.net.ssl.SSLSession;
import javax.net.ssl.TrustManager;
import javax.net.ssl.X509TrustManager;
import java.security.cert.X509Certificate;
import org.json.JSONArray;
import org.json.JSONObject;
import org.apache.commons.io.IOUtils;
import groovy.sql.GroovyRowResult;
import groovy.io.FileType;
 

class ValueSets_Translation
{

   @ValueSetTranslation(entrypoint="SubjectValues")
	def SubjectValuesTranslation(D3Interface d3, Translation transl)
   {
      def lang = transl.locale.language

      d3.log.info("Groovy - ValueSets: start SubjectValueTranslation " + lang + " - locale: " + transl.locale);

      if( lang == "de" ) {
         lang = "DEU"
      } else if( lang == "en" ) {
         lang = "ENG"
      }

		// IMUE 26.08.2019 neue Wertemenge bei Umstellung auf Schlüssel statt LangKey
		// NEU = def lSQL = """select fs.dok_dat_feld_${Const.gDFF_SCHLUESSEL} as ShortKey, value_char as TransVal 
		// ALT = def lSQL = """select fs.dok_dat_feld_${Const.gDDF_ORGENTRY} as LangKey, value_char as TransVal
		
		def lSQL = """select fs.dok_dat_feld_${Const.gDFF_SCHLUESSEL} as ShortKey, value_char as TransVal
                      from firm_spez_mult_val as mv
                      join firmen_spezifisch as fs on fs.doku_id = mv.doku_id
                     where field_no = '${Const.gDDF_UEBERSETZUNG}'
                       and mv.doku_id in (
                          select doku_id
                            from firmen_spezifisch
                           where kue_dokuart = '${Const.gDTS_AZTXT}'
                             AND dok_dat_feld_${Const.gDFF_SCHLUESSEL} like 'AK%' )
                       and row_number in (
                          select row_number
                            from firm_spez_mult_val as mv, firmen_spezifisch as fs
                            where value_char like '${lang}%'
                             and mv.field_no = '${Const.gDDF_ISOCODE}'
                             and fs.kue_dokuart = '${Const.gDTS_AZTXT}'
                             and fs.doku_id = mv.doku_id  )""";

      def resultlist = d3.sql.executeAndGet( lSQL );

      d3.log.info("Es wurden " + resultlist.size() + " Eintraege gefunden!");

		for ( def i = 0; i < resultlist.size(); i++ ) {
			transl.set( resultlist.get(i).get("ShortKey"), resultlist.get(i).get("TransVal") );
		}
   } 

   @ValueSetTranslation(entrypoint="MProjNumValues")
	def getMProjNumValuesTranslation(D3Interface d3, Translation transl)
   {
      def lang = transl.locale.language

      if ( lang != "DEU" ) {
         transl.set( "keine Submission", "No Submission" );
      }
   }
   
   @ValueSetTranslation(entrypoint="DokumentuntergruppeValues")
	def DokumentuntergruppeValuesTranslation(D3Interface d3, Translation transl)
   {
      def lang = transl.locale.language

      d3.log.info("Groovy - ValueSets: start DokumentuntergruppeValueTranslation " + lang + " - locale: " + transl.locale);
      if( lang == "de" ) {
         lang = "DEU"
      } else if( lang == "en" ) {
         lang = "ENG"
      }


      // IMUE 05.03.2019 neue Wertemenge bei Umstellung auf Schlüssel statt LangKey 
      // ALT = def lSQL = """select fs.dok_dat_feld_${Const.gDDF_ORGENTRY} as LangKey, value_char as TransVal   

      def lSQL = """select fs.dok_dat_feld_${Const.gDFF_SCHLUESSEL} as ShortKey, value_char as TransVal  
                      from firm_spez_mult_val as mv
                      join firmen_spezifisch as fs on fs.doku_id = mv.doku_id
                     where field_no = '${Const.gDDF_UEBERSETZUNG}'
                       and mv.doku_id in (
                          select doku_id
                            from firmen_spezifisch
                           where kue_dokuart = '${Const.gDTS_AZTXT}'
                             AND dok_dat_feld_${Const.gDFF_SCHLUESSEL} like 'DG%' )
                       and row_number in (
                          select row_number
                            from firm_spez_mult_val as mv, firmen_spezifisch as fs
                            where value_char like '${lang}%'
                             and field_no = '${Const.gDDF_ISOCODE}' 
                             and fs.kue_dokuart = '${Const.gDTS_AZTXT}'
                             and fs.doku_id = mv.doku_id )""";

      def resultlist = d3.sql.executeAndGet( lSQL );

      d3.log.info("Es wurden " + resultlist.size() + " Eintraege gefunden!");

      for ( def i = 0; i < resultlist.size(); i++ )
      {
         // IMUE 05.03.2019 neue Wertemenge bei Umstellung auf Schlüssel statt LangKey 
         // Alt = transl.set( resultlist.get(i).get("LangKey"), resultlist.get(i).get("TransVal") );
         transl.set( resultlist.get(i).get("ShortKey"), resultlist.get(i).get("TransVal") ); 
      }
   }
   
   @ValueSetTranslation(entrypoint="ProzessValues")
	def ProzessValuesTranslation(D3Interface d3, Translation transl)
   {
      def lang = transl.locale.language

      d3.log.info("Groovy - ValueSets: start ProzessValuesTranslation " + lang + " - locale: " + transl.locale);

      if( lang == "de" ) {
         lang = "DEU"
      } else if( lang == "en" ) {
         lang = "ENG"
      }
      
      // IMUE 05.02.2019 - Folgendes Statement ergibt die Schlüssel und die jeweilige Übersetzung nach Prozessumbau
      def lSQL = """ SELECT fs.dok_dat_feld_${Const.gDFF_SCHLUESSEL} as LangKey, mv.value_char as TransVal
                        FROM firmen_spezifisch fs 
                        LEFT JOIN firm_spez_mult_val mv ON fs.doku_id = mv.doku_id
                        LEFT JOIN firm_spez_mult_val mv2 ON fs.doku_id = mv2.doku_id AND mv.row_number = mv2.row_number
                        WHERE fs.kue_dokuart = '${Const.gDTS_AZTXT}'
                        AND mv.field_no = '${Const.gDDF_UEBERSETZUNG}' 
                        AND mv2.field_no = '${Const.gDDF_ISOCODE}' 
                        AND mv2.value_char LIKE '${lang}%'
                        ORDER BY mv2.value_char asc """;
      
      /*
      def lSQL = """select fs.dok_dat_feld_${Const.gDDF_ORGENTRY} as LangKey, value_char as TransVal
                      from firm_spez_mult_val as mv
                      join firmen_spezifisch as fs on fs.doku_id = mv.doku_id
                     where field_no = '${Const.gDDF_UEBERSETZUNG}'
                       and mv.doku_id in (
                          select doku_id
                            from firmen_spezifisch
                           where kue_dokuart = '${Const.gDTS_AZTXT}'
                             AND dok_dat_feld_${Const.gDFF_SCHLUESSEL} like 'PZ%' )
                       and row_number in (
                          select row_number
                            from firm_spez_mult_val as mv, firmen_spezifisch as fs
                            where value_char like '${lang}%'
                             and field_no = '${Const.gDDF_ISOCODE}' 
                             and fs.kue_dokuart = '${Const.gDTS_AZTXT}'
                             and fs.doku_id = mv.doku_id )""";
       */

      def resultlist = d3.sql.executeAndGet( lSQL );

      d3.log.info("Es wurden " + resultlist.size() + " Eintraege gefunden!");

      for ( def i = 0; i < resultlist.size(); i++ )
      {
         transl.set( resultlist.get(i).get("LangKey"), resultlist.get(i).get("TransVal") );
      }
   }
   
   @ValueSetTranslation(entrypoint="ProzArtValues")
	def ProzArtValuesTranslation(D3Interface d3, Translation transl)
   {
      def lang = transl.locale.language

      d3.log.info("Groovy - ValueSets: start ProzArtValuesTranslation " + lang + " - locale: " + transl.locale);

      if( lang == "de" ) {
         lang = "DEU"
      } else if( lang == "en" ) {
         lang = "ENG"
      }
      
      def lSQL = """ SELECT fs.dok_dat_feld_${Const.gDFF_SCHLUESSEL} as LangKey, mv.value_char as TransVal
                        FROM firmen_spezifisch fs 
                        LEFT JOIN firm_spez_mult_val mv ON fs.doku_id = mv.doku_id
                        LEFT JOIN firm_spez_mult_val mv2 ON fs.doku_id = mv2.doku_id AND mv.row_number = mv2.row_number
                        WHERE fs.kue_dokuart = '${Const.gDTS_AZTXT}'
                        AND mv.field_no = '${Const.gDDF_UEBERSETZUNG}' 
                        AND mv2.field_no = '${Const.gDDF_ISOCODE}' 
                        AND mv2.value_char LIKE '${lang}%' """;     

      /*def lSQL = """select fs.dok_dat_feld_${Const.gDDF_ORGENTRY} as LangKey, value_char as TransVal
                      from firm_spez_mult_val as mv
                      join firmen_spezifisch as fs on fs.doku_id = mv.doku_id
                     where field_no = '${Const.gDDF_UEBERSETZUNG}'
                       and mv.doku_id in (
                          select doku_id
                            from firmen_spezifisch
                           where kue_dokuart = '${Const.gDTS_AZTXT}'
                             AND dok_dat_feld_${Const.gDFF_SCHLUESSEL} like 'PA%' )
                       and row_number in (
                          select row_number
                            from firm_spez_mult_val as mv, firmen_spezifisch as fs
                            where value_char like '${lang}%'
                             and field_no = '${Const.gDDF_ISOCODE}' 
                             and fs.kue_dokuart = '${Const.gDTS_AZTXT}'
                             and fs.doku_id = mv.doku_id )""";
      */

      def resultlist = d3.sql.executeAndGet( lSQL );

      d3.log.info("Es wurden " + resultlist.size() + " Eintraege gefunden!");

      for ( def i = 0; i < resultlist.size(); i++ )
      {
         transl.set( resultlist.get(i).get("LangKey"), resultlist.get(i).get("TransVal") );
      }
   }

   // 07.2019 wird produktiv noch nicht genutzt
   @ValueSetTranslation(entrypoint="MachineDocumentModuls")
	def MachineDocumentsModulsTranslation(D3Interface d3, Translation transl)
   {
         // Persistierung nur der eindeutigen ID - In der Wertemenge wird über die Translation "ID - Beschreibung" angezeigt"
         def lang = transl.locale.language
         lang                 = lang.toUpperCase();
         def fallbackLangEng  = "EN";

         /* 
            // Create a trust manager that does not validate certificate chains -> entfernt 13.02.2019
            Dafür ist es erforderlich das Zertifikat in Java als vertrauenswürdig hinzuzufügen:
            cd D:\d3\d3server.prg\JRE\bin keytool.exe -import -keystore "D:\d3\d3server.prg\JRE\lib\security\cacerts" -file "E:\optima_group_ca.cer"     
         */
		 
		 if(SystemSpecificConst.needCertificateCheck == false) {
			deactivateCertificateCheck();
		 }
         
         URL url = new URL( 'https://' + SystemSpecificConst.baseOdata + '/sap/opu/odata/sap/ZPS_MACHINE_MASTER_SRV/ModuleTextSet?$filter=Deleted%20eq%20false%20&$format=json');
         
         try {
            URLConnection con = url.openConnection();

            String userpass = SystemSpecificConst.lUserOdata + ":" + SystemSpecificConst.lPassOdata;
            String basicAuth = "Basic " + new String(Base64.getEncoder().encode(userpass.getBytes()));
            con.setRequestProperty ("Authorization", basicAuth);

            InputStream is = con.getInputStream();
            String responseValue = IOUtils.toString(is, "UTF-8");
            is.close();
            
            JSONObject obj = new JSONObject(responseValue);
            JSONArray arr = obj.get("d").getJSONArray("results");
         
            List<String> guidList = new ArrayList<>();
            HashMap<String, JSONObject> guidJsonObMap = new HashMap<>(); 
      
            if(arr.length() > 0) {
               for (int i = 0; i < arr.length(); i++) {
                  JSONObject metadataObj = arr.getJSONObject(i);
                  
                  // Eindeutige GUID einmalig holen
                  String guid = metadataObj.getString("ModuleGUID");      
                  boolean exist = guidList.contains(guid);
                  if(exist == false) {
                     guidList.add(guid);
                  }  
                  // Je GUID das JsonObject zwischernspeichern - Eine GUID ist nur je Sprache eindeutig, daher Zusammensetzung für den HashMap-Key notwendig
                  String language = metadataObj.getString("LanguageSap");
                  guidJsonObMap.put(guid + "_" + language, metadataObj);
               }
            } else {
               d3.log.error( " ## ValueSetTranslation for MachineDocumentModuls - JSONArray Size = 0 ") ; 
            }
            
            // für jede eindeutige GUID müssen die weiteren Informationen in der Anmeldesprache gefunden werden
            // wenn kein Ergebnis gefunden wurde - Fallback Englisch
            for(String guid : guidList) {
               JSONObject jsonTempOb = guidJsonObMap.get(guid + "_" + lang);
               // wenn es Informationen zu der GUID und der Anmeldesprache gibt
               if(jsonTempOb != null) {
                  // Eindeutige ID holen
                  String id = jsonTempOb.getString("Id");
                  // Beschreibung holen
                  String description = jsonTempOb.getString("Description");
                  transl.set( guid, id + " - " +  description);
               } else {
                  // Fallback - gibt es keine Informationen in der Anmeldesprache, nehme Englisch
                  JSONObject jsonTempObEng = guidJsonObMap.get(guid + "_" + fallbackLangEng);
                  // Eindeutige ID holen
                  String id = jsonTempObEng.getString("Id");
                  // Beschreibung holen
                  String description = jsonTempObEng.getString("Description");
                  //d3.log.error( " ## ValueSetTranslation for MachineDocumentModuls - " + fallbackLangEng + " - " + guid + " - " + id + " - " + description);
                  transl.set( guid, id + " - " +  description);
               }   
            }
            
         } catch(Exception e) {
                d3.log.error( " ## ValueSetTranslation for MachineDocumentModuls - Exception = " + e.getMessage() ); 
         }
         
   }
   
   // 07.2019 wird produktiv noch nicht genutzt
   @ValueSetTranslation(entrypoint="MachineDocumentFunctions")
	def MachineDocumentsFunctionsTranslation(D3Interface d3, Translation transl)
   {
         // Persistierung nur der eindeutigen ID - In der Wertemenge wird über die Translation "ID - Beschreibung" angezeigt"
         def lang = transl.locale.language
         lang                 = lang.toUpperCase();
         def fallbackLangEng  = "EN";

         /* 
            // Create a trust manager that does not validate certificate chains -> entfernt 13.02.2019
            Dafür ist es erforderlich das Zertifikat in Java als vertrauenswürdig hinzuzufügen:
            cd D:\d3\d3server.prg\JRE\bin keytool.exe -import -keystore "D:\d3\d3server.prg\JRE\lib\security\cacerts" -file "E:\optima_group_ca.cer"     
         */
		 
		 if(SystemSpecificConst.needCertificateCheck == false) {
			deactivateCertificateCheck();
		 }
       
         // aktuell ist es noch nicht möglich ohne Filterung den oData Aufruf auszuführen
         URL url = new URL('https://' + SystemSpecificConst.baseOdata + '/sap/opu/odata/sap/ZPS_MACHINE_MASTER_SRV/FunctionTextSet?$filter=Deleted%20eq%20false%20&$format=json');

         try {
            URLConnection con = url.openConnection();
             
            String userpass = SystemSpecificConst.lUserOdata + ":" + SystemSpecificConst.lPassOdata;
            String basicAuth = "Basic " + new String(Base64.getEncoder().encode(userpass.getBytes()));
            con.setRequestProperty ("Authorization", basicAuth);

            InputStream is = con.getInputStream();
            String responseValue = IOUtils.toString(is, "UTF-8");
            is.close();
            
            JSONObject obj = new JSONObject(responseValue);
            JSONArray arr = obj.get("d").getJSONArray("results");
     
            List<String> guidList = new ArrayList<>();
            HashMap<String, JSONObject> guidJsonObMap = new HashMap<>(); 
            
            if(arr.length() > 0) {
               for (int i = 0; i < arr.length(); i++) {
                  JSONObject metadataObj = arr.getJSONObject(i);
                  
                  // Eindeutige GUID einmalig holen
                  String guid = metadataObj.getString("FunctionGUID");      
                  boolean exist = guidList.contains(guid);
                  if(exist == false) {
                     guidList.add(guid);
                  }  
                  // Je GUID das JsonObject zwischernspeichern - Eine GUID ist nur je Sprache eindeutig, daher Zusammensetzung für den HashMap-Key notwendig
                  String language = metadataObj.getString("LanguageSap");
                  guidJsonObMap.put(guid + "_" + language, metadataObj);
               }
            } else {
               d3.log.error( " ## ValueSetTranslation for MachineDocumentFunctions - JSONArray Size = 0 ") ; 
            }
            
            // für jede eindeutige GUID müssen die weiteren Informationen in der Anmeldesprache gefunden werden
            // wenn kein Ergebnis gefunden wurde - Fallback Englisch
            for(String guid : guidList) {
               JSONObject jsonTempOb = guidJsonObMap.get(guid + "_" + lang);
               // wenn es Informationen zu der GUID und der Anmeldesprache gibt
               if(jsonTempOb != null) {
                  // Eindeutige ID holen
                  String id = jsonTempOb.getString("Id");
                  // Beschreibung holen
                  String description = jsonTempOb.getString("Description");
                  transl.set( guid, id + " - " +  description);
               } else {
                  // Fallback - gibt es keine Informationen in der Anmeldesprache, nehme Englisch
                  JSONObject jsonTempObEng = guidJsonObMap.get(guid + "_" + fallbackLangEng);
                  // Eindeutige ID holen
                  String id = jsonTempObEng.getString("Id");
                  // Beschreibung holen
                  String description = jsonTempObEng.getString("Description");
                  //d3.log.error( " ## ValueSetTranslation for MachineDocumentFunctions - " + fallbackLangEng + " - " + guid + " - " + id + " - " + description);
                  transl.set( guid, id + " - " +  description);
               }   
            }
            
         } catch(Exception e) {
                d3.log.error( " ## ValueSetTranslation for MachineDocumentFunctions - Exception = " + e.getMessage() ); 
         } 
         
   }
    
   // 07.2019 wird produktiv noch nicht genutzt
   @ValueSetTranslation(entrypoint="MachineDocumentMachineType")
	def MachineDocumentMachineTypeTranslation(D3Interface d3, Translation transl)
   {
         // Persistierung nur der eindeutigen ID - In der Wertemenge wird über die Translation "ID - Beschreibung" angezeigt"
         def lang = transl.locale.language
         lang     = lang.toUpperCase();
        
         /* 
            // Create a trust manager that does not validate certificate chains -> entfernt 13.02.2019
            Dafür ist es erforderlich das Zertifikat in Java als vertrauenswürdig hinzuzufügen:
            cd D:\d3\d3server.prg\JRE\bin keytool.exe -import -keystore "D:\d3\d3server.prg\JRE\lib\security\cacerts" -file "E:\optima_group_ca.cer"     
         */
		 
		 if(SystemSpecificConst.needCertificateCheck == false) {
			deactivateCertificateCheck();
		 }

         URL url = new URL('https://' + SystemSpecificConst.baseOdata + '/sap/opu/odata/sap/ZPS_MACHINE_MASTER_SRV/MachineSimpleSet?$format=json');
                 
         try {
            URLConnection con = url.openConnection();

            String userpass = SystemSpecificConst.lUserOdata + ":" + SystemSpecificConst.lPassOdata;
            String basicAuth = "Basic " + new String(Base64.getEncoder().encode(userpass.getBytes()));
            con.setRequestProperty ("Authorization", basicAuth);

            InputStream is = con.getInputStream();
            String responseValue = IOUtils.toString(is, "UTF-8");
            is.close();
                        
            JSONObject obj = new JSONObject(responseValue);
            JSONArray arr = obj.get("d").getJSONArray("results");

            for (int i = 0; i < arr.length(); i++)
            {
                JSONObject metadataObj = arr.getJSONObject(i);
               
                // Eindeutige Id holen
                String id = metadataObj.getString("Id");
                   
                // Beschreibung holen
                String description = metadataObj.getString("Description");
                transl.set( id, id + " - " +  description);
               
            }
            
         } catch(Exception e) {
                d3.log.error( " ## ValueSetTranslation for MachineDocumentModuls - Exception = " + e.getMessage() ); 
         } 
         
   }
   
   @ValueSetTranslation(entrypoint="VorlagenProzArtValues")
	def VorlagenProzArtValuesTranslation(D3Interface d3, Translation transl)
   {      
      def lang = transl.locale.language
     
      if( lang == "de" ) {
         lang = "DEU"
      } else if( lang == "en" ) {
         lang = "ENG"
      }
      
      def lSQL = """select fs.dok_dat_feld_${Const.gDFF_SCHLUESSEL} as LangKey, value_char as TransVal
                      from firm_spez_mult_val as mv
                      join firmen_spezifisch as fs on fs.doku_id = mv.doku_id
                     where field_no = '${Const.gDDF_UEBERSETZUNG}'
                       and mv.doku_id in (
                          select doku_id
                            from firmen_spezifisch
                           where kue_dokuart = '${Const.gDTS_AZTXT}' )
                       and row_number in (
                          select row_number
                            from firm_spez_mult_val as mv, firmen_spezifisch as fs
                            where value_char like '${lang}%'
                             and field_no = '${Const.gDDF_ISOCODE}' 
                             and fs.kue_dokuart = '${Const.gDTS_AZTXT}'
                             and fs.doku_id = mv.doku_id )""";

      def resultlist = d3.sql.executeAndGet( lSQL );

      for ( def i = 0; i < resultlist.size(); i++ )
      {
         transl.set( resultlist.get(i).get("LangKey"), resultlist.get(i).get("TransVal") );
      }
      
   }

   @ValueSetTranslation(entrypoint="RegisterValues")
	def RegisterTranslation(D3Interface d3, Translation transl)
   {
      //d3.log.error("RegisterValues Translation started");
      
      def lang = transl.locale.language
      
      // Erweiterte Eigenschaft Register hat die ID 51 und muss pro System geprüft werden
      def registerResultsetTransMan = d3.sql.executeAndGet("SELECT value_char_allowed, value_char_transl FROM dbo.doc_field_val_lang WHERE predef_value_id = 51");  
      
      
      
      if( lang == "de" ) {    
         // manuelle Wertemenge
         for ( int i = 0; i < registerResultsetTransMan.size(); i++ ) {
            transl.set( registerResultsetTransMan.get(i).get("value_char_allowed"), registerResultsetTransMan.get(i).get("value_char_allowed") );
         }
         // dynamische Wertemenge
         def registerResultsetTransDyn = d3.sql.executeAndGet("SELECT fs.dok_dat_feld_${Const.gDFF_SCHLUESSEL} as ShortKey, mv.value_char as ValueTrans FROM firmen_spezifisch fs LEFT JOIN firm_spez_mult_val mv ON fs.doku_id = mv.doku_id WHERE fs.kue_dokuart = '${Const.gDTS_AZTXT}' AND fs.dok_dat_feld_${Const.gDFF_SCHLUESSEL} LIKE 'DG%' AND field_no = ${Const.gDDF_UEBERSETZUNG} AND mv.row_number = 1");
         for ( int i = 0; i < registerResultsetTransDyn.size(); i++ ) {
            transl.set( registerResultsetTransDyn.get(i).get("ShortKey"), registerResultsetTransDyn.get(i).get("ValueTrans") );
         }
      } else if( lang == "en" ) {
         // manuelle Wertemenge
         for ( int i = 0; i < registerResultsetTransMan.size(); i++ ) {
            transl.set( registerResultsetTransMan.get(i).get("value_char_allowed"), registerResultsetTransMan.get(i).get("value_char_transl") );
         }
         // dynamische Wertemenge
         def registerResultsetTransDyn = d3.sql.executeAndGet("SELECT fs.dok_dat_feld_${Const.gDFF_SCHLUESSEL} as ShortKey, mv.value_char as ValueTrans FROM firmen_spezifisch fs LEFT JOIN firm_spez_mult_val mv ON fs.doku_id = mv.doku_id WHERE fs.kue_dokuart = '${Const.gDTS_AZTXT}' AND fs.dok_dat_feld_${Const.gDFF_SCHLUESSEL} LIKE 'DG%' AND field_no = ${Const.gDDF_UEBERSETZUNG} AND mv.row_number = 2");
         for ( int i = 0; i < registerResultsetTransDyn.size(); i++ ) {
            transl.set( registerResultsetTransDyn.get(i).get("ShortKey"), registerResultsetTransDyn.get(i).get("ValueTrans") );
         }
      }
     
   }   
   
   @ValueSetTranslation(entrypoint="OGValues")
	def OGValuesTranslation(D3Interface d3, Translation transl)
   {
      //def lang = transl.locale.language

      //d3.log.info("Groovy - ValueSets: start OGValuesTranslation " + lang + " - locale: " + transl.locale);
      
      //if( lang == "de" ) {
         //lang = "DEU"
      //} else if( lang == "en" ) {
         //lang = "ENG"
      //}
      
      //def lSQL = """ SELECT fs.dok_dat_feld_${Const.gDFF_SCHLUESSEL} as LangKey, mv.value_char as TransVal
      //                  FROM firmen_spezifisch fs 
      //                  LEFT JOIN firm_spez_mult_val mv ON fs.doku_id = mv.doku_id
      //                  LEFT JOIN firm_spez_mult_val mv2 ON fs.doku_id = mv2.doku_id AND mv.row_number = mv2.row_number
      //                  WHERE fs.kue_dokuart = '${Const.gDTS_AZTXT}'
      //                  AND mv.field_no = '${Const.gDDF_UEBERSETZUNG}' 
      //                  AND mv2.field_no = '${Const.gDDF_ISOCODE}' 
      //                  AND mv2.value_char LIKE '${lang}%' """;      
    
      //def resultlist = d3.sql.executeAndGet( lSQL );

      //d3.log.info("Es wurden " + resultlist.size() + " Eintraege gefunden!");

      //for ( def i = 0; i < resultlist.size(); i++ )
      //{
      //   transl.set( resultlist.get(i).get("LangKey"), resultlist.get(i).get("TransVal") );
      //}
	  
      // Persistierung nur der eindeutigen ID - In der Wertemenge wird über die Translation "ID - Beschreibung" angezeigt"
      def lang = transl.locale.language
      lang     = lang.toUpperCase();

      /* 
         // Create a trust manager that does not validate certificate chains -> entfernt 13.02.2019
         Dafür ist es erforderlich das Zertifikat in Java als vertrauenswürdig hinzuzufügen:
         cd D:\d3\d3server.prg\JRE\bin keytool.exe -import -keystore "D:\d3\d3server.prg\JRE\lib\security\cacerts" -file "E:\optima_group_ca.cer"     
      */
	  
	  if(SystemSpecificConst.needCertificateCheck == false) {
		deactivateCertificateCheck();
	  }
      
      URL url = new URL('https://' + SystemSpecificConst.baseOdata + '/sap/opu/odata/sap/ZCA_D3_AUTHORIZATIONS_SRV/OrgUnitSet?$format=json');
              
      try {
         URLConnection con = url.openConnection();

         String userpass = SystemSpecificConst.lUserOdata + ":" + SystemSpecificConst.lPassOdata;
         String basicAuth = "Basic " + new String(Base64.getEncoder().encode(userpass.getBytes()));
         con.setRequestProperty ("Authorization", basicAuth);

         InputStream is = con.getInputStream();
         String responseValue = IOUtils.toString(is, "UTF-8");
         is.close();
                     
         JSONObject obj = new JSONObject(responseValue);
         JSONArray arr = obj.get("d").getJSONArray("results");
         
         for (int i = 0; i < arr.length(); i++)
         {
             JSONObject metadataObj = arr.getJSONObject(i);
            
             // Eindeutige Id holen
             String id = metadataObj.getString("Id");
                
             // Beschreibung holen
             String description = metadataObj.getString("DisplayName");
             transl.set( id, description);
            
         }
         
      } catch(Exception e) {
             d3.log.error( " ## ValueSetTranslation for OGValuesTranslation - Exception = " + e.getMessage() ); 
      } 
         	  
	  
   }
   
   @ValueSetTranslation(entrypoint="BuchungskreisValues")
   def getValuesBuchungskreis(D3Interface d3, Translation transl) 
   {         
         // Persistierung nur der eindeutigen ID - In der Wertemenge wird über die Translation "ID - Beschreibung" angezeigt"
         
         List<String> buchungskreisResultset = [];
                
		 if(SystemSpecificConst.needCertificateCheck == false) {
			deactivateCertificateCheck();
		 }
		 
         URL url = new URL('https://' + SystemSpecificConst.baseOdata + '/sap/opu/odata/sap/ZCA_D3_VALUE_LISTS_SRV/CompanySet?$format=json&$orderby=CompanyCode');
         
         try {
            if(url != null) {
               URLConnection con = url.openConnection();

               String userpass = SystemSpecificConst.lUserOdata + ":" + SystemSpecificConst.lPassOdata;
               String basicAuth = "Basic " + new String(Base64.getEncoder().encode(userpass.getBytes()));
               con.setRequestProperty ("Authorization", basicAuth);
               
               InputStream is = con.getInputStream();
               String responseValue = IOUtils.toString(is, "UTF-8");
               is.close();
                              
               JSONObject obj = new JSONObject(responseValue);

               JSONArray arr = obj.get("d").getJSONArray("results");
               for (int i = 0; i < arr.length(); i++)
               {
                   JSONObject metadataObj = arr.getJSONObject(i);
                   // Buchungskreis holen
                   String companyCode = metadataObj.getString("CompanyCode");
                      
                   // Beschreibung holen
                   String description = metadataObj.getString("Description");
                   transl.set( companyCode, companyCode + " | " +  description);
                   
               }
            }
         } catch(Exception e) {
                d3.log.error( " ## BuchungskreisTranslationValues - Exception = " + e.getMessage() ); 
         }
         
   }

	@ValueSetTranslation(entrypoint="ServicBelegartValues")
	def ServicBelegartTransValues(D3Interface d3, Translation transl) {
      
      def lang = transl.locale.language

		d3.log.info("Groovy - ValueSets: start ServicBelegartValues " + lang + " - locale: " + transl.locale);
		
		if( lang == "de" ) {
			lang = "DE"
		} else if( lang == "en" ) {
			lang = "EN"
		}

		List<String> buchungskreisResultset = [];
      
	  	if(SystemSpecificConst.needCertificateCheck == false) {
			deactivateCertificateCheck();
		}
		 
        URL url = new URL("https://" + SystemSpecificConst.baseOdata + "/sap/opu/odata/sap/ZCA_D3_VALUE_LISTS_SRV/DocumentTypeSet?\$format=json&\$filter=LanguageIso%20eq%20'" + lang + "'");

		try {
			if(url != null) {
				URLConnection con = url.openConnection();

				String userpass = SystemSpecificConst.lUserOdata + ":" + SystemSpecificConst.lPassOdata;
				String basicAuth = "Basic " + new String(Base64.getEncoder().encode(userpass.getBytes()));
				con.setRequestProperty ("Authorization", basicAuth);

				InputStream is = con.getInputStream();
				String responseValue = IOUtils.toString(is, "UTF-8");
				is.close();

				JSONObject obj = new JSONObject(responseValue);

				JSONArray arr = obj.get("d").getJSONArray("results");
				for (int i = 0; i < arr.length(); i++) {
					JSONObject metadataObj = arr.getJSONObject(i);
					// Belegart holen
					String documentTypeShort = metadataObj.getString("Id");
					// Beschreibung holen
					String documentTypeDescription = metadataObj.getString("Description");
					transl.set( documentTypeShort, documentTypeDescription );
				}
			}
		} catch(Exception e) {
			d3.log.error( " ## ServicBelegartTransValues - Exception = " + e.getMessage() );
		}
									
	}
	
	// Deaktivierung der Zertifikatsprüfung
	def deactivateCertificateCheck() {
		// CONNECT WITHOUT SSL CHECK 
		TrustManager[] trustAllCerts =  new TrustManager[1]
		def tm = new X509TrustManager() {
			public java.security.cert.X509Certificate[] getAcceptedIssuers() { return null; }
			public void checkClientTrusted(X509Certificate[] certs, String authType) { }
			public void checkServerTrusted(X509Certificate[] certs, String authType) { }
		}
		trustAllCerts[0] = tm;
		SSLContext sc = SSLContext.getInstance("SSL");
		sc.init(null, trustAllCerts, new java.security.SecureRandom());
		HttpsURLConnection.setDefaultSSLSocketFactory(sc.getSocketFactory());
		HostnameVerifier allHostsValid = new HostnameVerifier() {
			public boolean verify(String hostname, SSLSession session) { return true; }
		};
		HttpsURLConnection.setDefaultHostnameVerifier(allHostsValid);  
		// CONNECT WITHOUT SSL CHECK
	}

} // End of file