GeMIS_WEB/utilities/selectboxHandler.cls

 
/*------------------------------------------------------------------------
   File        : queryHandler
   Purpose     : 
   Syntax      : 
   Description : 
   Author(s)   : walter.riechsteiner
   Created     : Wed Jul 10 18:09:37 CEST 2024
   Notes       : 
 ----------------------------------------------------------------------*/

USING Progress.Json.ObjectModel.JsonObject FROM PROPATH.
USING OpenEdge.Net.HTTP.IHttpRequest FROM PROPATH.
USING System.Drawing.CharacterRange FROM ASSEMBLY.
USING Progress.Json.ObjectModel.JsonArray FROM PROPATH.

CLASS utilities.selectboxHandler: 

    DEFINE VARIABLE i1 AS INTEGER NO-UNDO.
    DEFINE VARIABLE i2 AS INTEGER NO-UNDO.
    
    DEFINE TEMP-TABLE ttSelect
        FIELD ind    AS INTEGER   SERIALIZE-HIDDEN
        FIELD cValue AS CHARACTER SERIALIZE-NAME 'value'
        FIELD cLabel AS CHARACTER SERIALIZE-NAME 'label'.
    DEFINE VARIABLE httSelect     AS HANDLE NO-UNDO.
    DEFINE VARIABLE httSelectBuff AS HANDLE NO-UNDO.
    
    DEFINE TEMP-TABLE ttParam
        FIELD company     AS CHARACTER
        FIELD user_name   AS CHARACTER 
        FIELD table_name  AS CHARACTER 
        FIELD field_name  AS CHARACTER 
        FIELD language_id AS INTEGER 
        FIELD field_value AS CHARACTER.
    DEFINE VARIABLE httParam     AS HANDLE NO-UNDO.
    DEFINE VARIABLE httParamBuff AS HANDLE NO-UNDO.  
        
    /*------------------------------------------------------------------------------
     Purpose:
     Notes:
    ------------------------------------------------------------------------------*/

    METHOD PUBLIC HANDLE getSelboxCompany(
        INPUT cFileName AS CHARACTER
        ):
        
        DEFINE VARIABLE cttBufferName AS CHARACTER NO-UNDO.
        
        DEFINE VARIABLE httSelect     AS HANDLE    NO-UNDO.
        DEFINE VARIABLE httBuffer     AS HANDLE    NO-UNDO.
        
        cttBufferName = SUBSTITUTE('&1_company', cFileName).
        
        CREATE TEMP-TABLE httSelect.
        httSelect:ADD-NEW-FIELD('value', 'character').
        httSelect:ADD-NEW-FIELD('label', 'character').
        httSelect:TEMP-TABLE-PREPARE (cttBufferName).
        httBuffer = httSelect:DEFAULT-BUFFER-HANDLE.

        FOR EACH companies NO-LOCK 
            WHERE companies.active :
            httBuffer:BUFFER-CREATE ().
            ASSIGN 
                httBuffer:BUFFER-FIELD('value'):BUFFER-VALUE = companies.company
                httBuffer:BUFFER-FIELD('label'):BUFFER-VALUE = companies.company_name.
        END.

        RETURN httBuffer.

    END METHOD.

    /*------------------------------------------------------------------------------
     Purpose:
     Notes:
    ------------------------------------------------------------------------------*/

    METHOD PUBLIC HANDLE getSelboxLanguage(
        INPUT cFileName AS CHARACTER
        ):
        
        DEFINE VARIABLE cttBufferName AS CHARACTER NO-UNDO.
        
        DEFINE VARIABLE httSelect     AS HANDLE    NO-UNDO.
        DEFINE VARIABLE httBuffer     AS HANDLE    NO-UNDO.
        
        cttBufferName = SUBSTITUTE('&1_language_id', cFileName).

        CREATE TEMP-TABLE httSelect.
        httSelect:ADD-NEW-FIELD('value', 'integer'   ).
        httSelect:ADD-NEW-FIELD('label', 'character' ).
        httSelect:TEMP-TABLE-PREPARE ('cttBufferName').
        httBuffer = httSelect:DEFAULT-BUFFER-HANDLE.

        FOR EACH languages NO-LOCK: 
            httBuffer:BUFFER-CREATE ().
            ASSIGN 
                httBuffer:BUFFER-FIELD('value'):BUFFER-VALUE = languages.language_id
                httBuffer:BUFFER-FIELD('label'):BUFFER-VALUE = languages.language_name.
        END.

        RETURN httBuffer.

    END METHOD.

    /*------------------------------------------------------------------------------
     Purpose:
     Notes:
    ------------------------------------------------------------------------------*/

    METHOD PUBLIC HANDLE getSelboxTables(
        INPUT cFileName AS CHARACTER
        ):
        
        DEFINE VARIABLE cttBufferName AS CHARACTER NO-UNDO.
        
        DEFINE VARIABLE httSelect     AS HANDLE    NO-UNDO.
        DEFINE VARIABLE httBuffer     AS HANDLE    NO-UNDO.
        
        cttBufferName = SUBSTITUTE('&1_table_name', cFileName).

        CREATE TEMP-TABLE httSelect.
        httSelect:ADD-NEW-FIELD('value', 'character'   ).
        httSelect:ADD-NEW-FIELD('label', 'character' ).
        httSelect:TEMP-TABLE-PREPARE (cttBufferName).
        httBuffer = httSelect:DEFAULT-BUFFER-HANDLE.

        httBuffer:BUFFER-CREATE ().
        ASSIGN 
            httBuffer:BUFFER-FIELD('value'):BUFFER-VALUE = 'table'
            httBuffer:BUFFER-FIELD('label'):BUFFER-VALUE = 'Default-Value (table)'.
        FOR EACH Ge_MIS._File NO-LOCK
            WHERE NOT Ge_MIS._File._File-Name BEGINS '_'
            AND   NOT Ge_MIS._File._File-Name BEGINS 'SYS': 
            httBuffer:BUFFER-CREATE ().
            ASSIGN 
                httBuffer:BUFFER-FIELD('value'):BUFFER-VALUE = Ge_MIS._File._File-Name
                httBuffer:BUFFER-FIELD('label'):BUFFER-VALUE = Ge_MIS._File._File-Name.
        END.
        FOR EACH FRAMEWORK._File NO-LOCK
            WHERE NOT FRAMEWORK._File._File-Name BEGINS '_'
            AND   NOT FRAMEWORK._File._File-Name BEGINS 'SYS': 
            httBuffer:BUFFER-CREATE ().
            ASSIGN 
                httBuffer:BUFFER-FIELD('value'):BUFFER-VALUE = FRAMEWORK._File._File-Name
                httBuffer:BUFFER-FIELD('label'):BUFFER-VALUE = FRAMEWORK._File._File-Name.
        END.

        RETURN httBuffer.

    END METHOD.

    /*------------------------------------------------------------------------------
     Purpose:
     Notes:
    ------------------------------------------------------------------------------*/

    METHOD PUBLIC HANDLE getSelboxUsers(
        INPUT cFileName AS CHARACTER
        ):
        
        DEFINE VARIABLE cttBufferName AS CHARACTER NO-UNDO.
        
        DEFINE VARIABLE httSelect     AS HANDLE    NO-UNDO.
        DEFINE VARIABLE httBuffer     AS HANDLE    NO-UNDO.
        
        cttBufferName = SUBSTITUTE('&1_user_name', cFileName).

        CREATE TEMP-TABLE httSelect.
        httSelect:ADD-NEW-FIELD('value', 'character'   ).
        httSelect:ADD-NEW-FIELD('label', 'character' ).
        httSelect:TEMP-TABLE-PREPARE (cttBufferName).
        httBuffer = httSelect:DEFAULT-BUFFER-HANDLE.
        
        FOR EACH users NO-LOCK: 
            httBuffer:BUFFER-CREATE ().
            ASSIGN 
                httBuffer:BUFFER-FIELD('value'):BUFFER-VALUE = users.user_name
                httBuffer:BUFFER-FIELD('label'):BUFFER-VALUE = users.display_name.
        END.

        RETURN httBuffer.

    END METHOD.

    /*------------------------------------------------------------------------------
     Purpose:
     Notes:
    ------------------------------------------------------------------------------*/

    METHOD PUBLIC VOID getSelectBoxContacts(
        INPUT  iAddress_Id  AS INT64     ,
        INPUT  oJsonIdent   AS JsonObject,
        OUTPUT oJsonBoxEval AS JsonArray
        ):
        
        DEFINE VARIABLE cttBufferName AS CHARACTER NO-UNDO.
        DEFINE VARIABLE cCompany      AS CHARACTER NO-UNDO.
        
        DEFINE VARIABLE httSelect     AS HANDLE    NO-UNDO.
        DEFINE VARIABLE httBuffer     AS HANDLE    NO-UNDO.
        
        cCompany = oJsonIdent:GetCharacter('company').
        
        cttBufferName = 'contact_persons'.

        CREATE TEMP-TABLE httSelect.
        httSelect:ADD-NEW-FIELD('value', 'character'   ).
        httSelect:ADD-NEW-FIELD('label', 'character' ).
        httSelect:TEMP-TABLE-PREPARE (cttBufferName).
        httBuffer = httSelect:DEFAULT-BUFFER-HANDLE.

        FOR EACH contact_persons NO-LOCK
            WHERE contact_persons.company    = cCompany
            AND   contact_persons.address_id = iAddress_id,
            
            FIRST addresses NO-LOCK
            WHERE addresses.address_id = iAddress_Id:
                 
            httBuffer:BUFFER-CREATE ().
            ASSIGN 
                httBuffer:BUFFER-FIELD('value'):BUFFER-VALUE = contact_persons.contact_id
                httBuffer:BUFFER-FIELD('label'):BUFFER-VALUE = SUBSTITUTE('(&1) &2', contact_persons.contact_id, contact_persons.address_display).
        END.
        
        /*        httBuffer:SERIALIZE-NAME = 'cFileName'.*/
        
        oJsonBoxEval = NEW JsonArray().
        httBuffer:WRITE-JSON('JsonArray', oJsonBoxEval). 

        RETURN.

    END METHOD.

    /*------------------------------------------------------------------------------
     Purpose:
     Notes:
    ------------------------------------------------------------------------------*/

    METHOD PUBLIC VOID getSelectBoxCountry( 
        INPUT  iLanguage_Id AS INTEGER   ,
        INPUT  cFile_Name   AS CHARACTER ,
        OUTPUT oJsonBoxEval AS JsonArray
        ):
        
        DEFINE VARIABLE cttBufferName AS CHARACTER NO-UNDO.
        
        DEFINE VARIABLE httSelect     AS HANDLE    NO-UNDO.
        DEFINE VARIABLE httBuffer     AS HANDLE    NO-UNDO.
        
        cttBufferName = SUBSTITUTE('&1_country_code', cFile_Name).

        CREATE TEMP-TABLE httSelect.
        httSelect:ADD-NEW-FIELD('value', 'character'   ).
        httSelect:ADD-NEW-FIELD('label', 'character' ).
        httSelect:TEMP-TABLE-PREPARE (cttBufferName).
        httBuffer = httSelect:DEFAULT-BUFFER-HANDLE.
        
        FOR EACH countries NO-LOCK
            WHERE countries.language_id = iLanguage_id: 
            httBuffer:BUFFER-CREATE ().
            ASSIGN 
                httBuffer:BUFFER-FIELD('value'):BUFFER-VALUE = countries.country_code
                httBuffer:BUFFER-FIELD('label'):BUFFER-VALUE = countries.country_name.
        END.
        
        /*        httBuffer:SERIALIZE-NAME = 'cFileName'.*/
        
        oJsonBoxEval = NEW JsonArray().
        httBuffer:WRITE-JSON('JsonArray', oJsonBoxEval). 

        RETURN.

    END METHOD.

    /*------------------------------------------------------------------------------
     Purpose:
     Notes:
    ------------------------------------------------------------------------------*/

    METHOD PUBLIC VOID getSelectBoxData(
        INPUT  oJsonIdent  AS JsonObject,
        INPUT  cField_Name AS CHARACTER,
        OUTPUT oJsonBox    AS JsonArray 
        ):
            
        DEFINE VARIABLE httParam      AS HANDLE    NO-UNDO.
        DEFINE VARIABLE httParamBuff  AS HANDLE    NO-UNDO.
        DEFINE VARIABLE hQuery        AS HANDLE    NO-UNDO.
        DEFINE VARIABLE httSelect     AS HANDLE    NO-UNDO.
        DEFINE VARIABLE httSelectBuff AS HANDLE    NO-UNDO.
        DEFINE VARIABLE hBuffer       AS HANDLE    NO-UNDO.
        
        DEFINE VARIABLE ii            AS INTEGER   NO-UNDO.
        DEFINE VARIABLE cReplace      AS CHARACTER NO-UNDO.
        DEFINE VARIABLE cFileName     AS CHARACTER NO-UNDO.
        DEFINE VARIABLE cWhere        AS CHARACTER NO-UNDO.
        DEFINE VARIABLE ccompany      AS CHARACTER NO-UNDO.
        DEFINE VARIABLE cuser_name    AS CHARACTER NO-UNDO.
        DEFINE VARIABLE ilanguage_id  AS INTEGER   NO-UNDO.
        DEFINE VARIABLE ctable_name   AS CHARACTER NO-UNDO.
        DEFINE VARIABLE oJsonBoxEval  AS JsonArray NO-UNDO.

        ccompany     = oJsonIdent:GetCharacter('company')     NO-ERROR.
        cuser_name   = oJsonIdent:GetCharacter('user_name')   NO-ERROR.
        ctable_name  = oJsonIdent:GetCharacter('file_name')   NO-ERROR.
        ilanguage_id = oJsonIdent:GetInteger  ('language_id') NO-ERROR.
        
        cFileName   = ENTRY(1, cField_Name, '_').
        cReplace    = cFileName + '_'.
        cField_Name = REPLACE(cField_Name, cReplace, '').
        
        IF cField_Name = 'country_code' THEN 
        DO:
            getSelectBoxCountry (
                INPUT  iLanguage_id ,
                INPUT  cFileName    ,
                OUTPUT oJsonBoxEval
                ).
            oJsonBox = oJsonBoxEval.
            RETURN.
        END.
        IF cField_Name = 'salutation_code' THEN 
        DO:
            getSelectBoxSalutation (
                INPUT  iLanguage_id ,
                INPUT  cFileName    ,
                OUTPUT oJsonBoxEval
                ).
            oJsonBox = oJsonBoxEval.
            RETURN.
        END.
        //
        httParam     = BUFFER ttParam:HANDLE.
        httParamBuff = TEMP-TABLE ttParam:DEFAULT-BUFFER-HANDLE.
        //
        //  Felder der Temptable initial füllen
        //
        EMPTY TEMP-TABLE ttParam.
        EMPTY TEMP-TABLE ttSelect.
        CREATE  ttParam.
        ASSIGN
            ttParam.company     = ccompany
            ttParam.user_name   = 'admin'
            ttParam.table_name  = 'table'
            ttParam.field_name  = cField_Name
            ttParam.language_id = iLanguage_id
            .
        //            
        CREATE QUERY hQuery.
        hBuffer = BUFFER selections:HANDLE.
        hQuery:SET-BUFFERS(hBuffer).
        
        DO ii = 1 TO 4:
// IF hQuery:QUERY-OPEN () THEN hQuery:QUERY-CLOSE ().
            
            FIND FIRST ttParam.
            
            cWhere = ''.
            cWhere = cWhere + SUBSTITUTE('WHERE selections.company     = &1 AND selections.user_name  = &2 ', QUOTER(ttParam.company)   , QUOTER(ttParam.user_name) ).
            cWhere = cWhere + SUBSTITUTE('AND   selections.table_name  = &1 AND selections.field_name = &2 ', QUOTER(ttParam.table_name), QUOTER(ttParam.field_name)).
            cWhere = cWhere + SUBSTITUTE('AND   selections.language_id = &1 ', ttParam.language_id).
            cWhere = 'FOR EACH selections NO-LOCK ' + cWhere.
            
            hQuery:QUERY-PREPARE(cWhere).
            hQuery:QUERY-OPEN ().
            hQuery:GET-FIRST () NO-ERROR.
            
            DO WHILE NOT hQuery:QUERY-OFF-END:
                CREATE  ttSelect.
                ASSIGN 
                    ttSelect.ind    = ii
                    ttSelect.cvalue = hBuffer::field_value
                    ttSelect.cLabel = hBuffer::field_show
                    .
                hQuery:GET-NEXT () NO-ERROR.
            END.
            
            CASE ii:
                WHEN 1 THEN 
                    DO:
                        ASSIGN 
                            ttParam.table_name = cFileName.
                    END.
                WHEN 2 THEN 
                    DO:
                        IF cuser_name = 'admin' THEN LEAVE. 
                        ASSIGN 
                            ttParam.user_name  = cuser_name
                            ttParam.table_name = 'table'.
                    END.
                WHEN 3 THEN 
                    DO:
                        ASSIGN 
                            ttParam.user_name  = cuser_name
                            ttParam.table_name = cFileName.
                    END.
            END CASE.
        END.
        
        FOR EACH ttSelect
            BREAK
            BY ttSelect.cValue
            BY ttSelect.ind:
                
            IF LAST-OF ( ttSelect.cValue) THEN NEXT.
            DELETE ttSelect.
        END.
        
        httSelectBuff = TEMP-TABLE ttSelect:DEFAULT-BUFFER-HANDLE.
        httSelectBuff:SERIALIZE-NAME = 'cFileName'.
        
        oJsonBox = NEW JsonArray().
        httSelectBuff:WRITE-JSON('JsonArray', oJsonBox). 
        
        RETURN.

    END METHOD.

    /*------------------------------------------------------------------------------
     Purpose:
     Notes:
    ------------------------------------------------------------------------------*/

    METHOD PUBLIC VOID getSelectBoxSalutation( 
        INPUT  iLanguage_Id AS INTEGER   ,
        INPUT  cFile_Name   AS CHARACTER ,
        OUTPUT oJsonBoxEval AS JsonArray
        ):
        
        DEFINE VARIABLE cttBufferName AS CHARACTER NO-UNDO.
        
        DEFINE VARIABLE httSelect     AS HANDLE    NO-UNDO.
        DEFINE VARIABLE httBuffer     AS HANDLE    NO-UNDO.
        
        cttBufferName = SUBSTITUTE('&1_salutation_code', cFile_Name).

        CREATE TEMP-TABLE httSelect.
        httSelect:ADD-NEW-FIELD('value', 'character'   ).
        httSelect:ADD-NEW-FIELD('label', 'character' ).
        httSelect:TEMP-TABLE-PREPARE (cttBufferName).
        httBuffer = httSelect:DEFAULT-BUFFER-HANDLE.
        
        FOR EACH salutations NO-LOCK
            WHERE salutations.language_id = iLanguage_id: 
            httBuffer:BUFFER-CREATE ().
            ASSIGN 
                httBuffer:BUFFER-FIELD('value'):BUFFER-VALUE = salutations.salutation_code.
            httBuffer:BUFFER-FIELD('label'):BUFFER-VALUE = salutations.salutation_display.
        END.
        
        /*        httBuffer:SERIALIZE-NAME = 'cFileName'.*/
        
        oJsonBoxEval = NEW JsonArray().
        httBuffer:WRITE-JSON('JsonArray', oJsonBoxEval). 

        RETURN.


    END METHOD.

    /*------------------------------------------------------------------------------
     Purpose:
     Notes:
    ------------------------------------------------------------------------------*/

    METHOD PUBLIC HANDLE getUserRoleSelect(
        INPUT  ipccompany AS CHARACTER
        ):
        
        DEFINE VARIABLE httBuffer AS HANDLE NO-UNDO.

        CREATE BUFFER httBuffer.
        httBuffer:ADD-NEW-FIELD ('value', 'CHARACTER').
        httBuffer:ADD-NEW-FIELD ('label', 'CHARACTER').
        httBuffer:TEMP-TABLE-PREPARE ('users_role').
        
        httBuffer:BUFFER-CREATE().
        ASSIGN 
            httBuffer:BUFFER-FIELD('value'):buffer-value = 'admin'
            httBuffer:BUFFER-FIELD('label'):buffer-value = 'admin'.        
        httBuffer:BUFFER-CREATE().
        ASSIGN 
            httBuffer:BUFFER-FIELD('value'):buffer-value = 'user'
            httBuffer:BUFFER-FIELD('label'):buffer-value = 'Benutzer'.        
        
        RETURN httBuffer.

    END METHOD.

    /*------------------------------------------------------------------------------
     Purpose:
     Notes:
    ------------------------------------------------------------------------------*/

    METHOD PUBLIC LOGICAL getVariableSelectBoxes(
        INPUT  oJsonIdent       AS JsonObject,
        OUTPUT oJsonSelectBoxes AS JsonObject
        ):
        
        DEFINE VARIABLE lRetVal    AS LOGICAL   NO-UNDO INIT FALSE.
        DEFINE VARIABLE ii         AS INTEGER   NO-UNDO.
        
        DEFINE VARIABLE cCompany   AS CHARACTER NO-UNDO.
        DEFINE VARIABLE cFormId    AS CHARACTER NO-UNDO.
        DEFINE VARIABLE cFormUser  AS CHARACTER NO-UNDO.
        DEFINE VARIABLE cFieldName AS CHARACTER NO-UNDO.
        DEFINE VARIABLE cFileName  AS CHARACTER NO-UNDO.
        
        DEFINE VARIABLE oJsonBox   AS JsonArray NO-UNDO.
        
        cCompany  = oJsonIdent:GetCharacter('company').
        cFormId   = oJsonIdent:GetCharacter('formId').
        cFormUser = oJsonIdent:GetCharacter('formUser').

        oJsonSelectBoxes = NEW JsonObject().
        FOR EACH selectboxfields NO-LOCK
            WHERE selectboxfields.company       = ccompany
            AND   selectboxfields.program       = cformId
            AND   selectboxfields.user_name     = cFormUser
            AND   selectboxfields.boxfields <> '':

            DO ii = 1 TO NUM-ENTRIES(selectboxfields.boxfields  , ';'):
                cFieldName = ENTRY(ii, selectboxfields.boxfields, ';').
                cFileName  = ENTRY(1, cFieldName, '_').
                getSelectBoxData(
                    INPUT  oJsonIdent,
                    INPUT  cFieldName,
                    OUTPUT oJsonBox
                    ).
// oJsonBox:WriteFile('C:\TEMP\JsonBox.json', TRUE).
                oJsonSelectBoxes:ADD(cFieldName, oJsonBox).
// oJsonSelectBoxes:WriteFile(SUBSTITUTE('C:\TEMP\&1_JsonSelectBoxes.json', cFieldName), TRUE).
            END.
        END.

        lRetVal = TRUE.
        RETURN lRetVal.

    END METHOD.

    /*------------------------------------------------------------------------------
     Purpose:
     Notes:
    ------------------------------------------------------------------------------*/

    DESTRUCTOR PUBLIC selectboxHandler ( ):
// MESSAGE 'Destructor selectboxHandler '.
    END DESTRUCTOR.

END CLASS.