GeMIS_WEB/GUI/Tools/DBAbgleich.p

/*------------------------------------------------------------------------
    File        : DBAbgleich.p
    Purpose     : 

    Syntax      :

    Description : 

    Author(s)   : walter.riechsteiner
    Created     : Mon Jun 29 17:48:36 CEST 2020
    Notes       :
  ----------------------------------------------------------------------*/

/* ***************************  Definitions  ************************** */

BLOCK-LEVEL ON ERROR UNDO, THROW.

DEFINE VARIABLE hQueryA  AS HANDLE    NO-UNDO.
DEFINE VARIABLE hBufferA AS HANDLE    NO-UNDO.
DEFINE VARIABLE htTable  AS HANDLE    NO-UNDO.
DEFINE VARIABLE htBuffer AS HANDLE    NO-UNDO.

DEFINE VARIABLE cWhere   AS CHARACTER NO-UNDO.
DEFINE VARIABLE cUnique  AS CHARACTER NO-UNDO.
DEFINE VARIABLE lFirma   AS LOGICAL   NO-UNDO.

DEFINE VARIABLE i1       AS INTEGER   NO-UNDO.
DEFINE VARIABLE iNeu     AS INTEGER   NO-UNDO.
DEFINE VARIABLE iAlt     AS INTEGER   NO-UNDO.
DEFINE VARIABLE lFound   AS LOGICAL   NO-UNDO.
DEFINE VARIABLE cFeld    AS CHARACTER NO-UNDO EXTENT 9.
DEFINE VARIABLE cType    AS CHARACTER NO-UNDO EXTENT 9.
DEFINE VARIABLE cParam   AS CHARACTER NO-UNDO EXTENT 9.
DEFINE VARIABLE lEqual   AS LOGICAL   NO-UNDO.
DEFINE VARIABLE cDatei   AS CHARACTER NO-UNDO.
/* ********************  Preprocessor Definitions  ******************** */


/* ***************************  Main Block  *************************** */

cDatei = 'C:\Entwicklung\TEMP\DBABGLEICH\Huber_db.log'.
OUTPUT TO VALUE(cDatei) APPEND NO-MAP NO-CONVERT.
PUT CONTROL CHR(10).
OUTPUT CLOSE.

FOR EACH Anadat._File NO-LOCK 
    WHERE NOT Anadat._File._File-Name BEGINS '_'
    AND   NOT Anadat._File._File-name BEGINS 'SYS':
        
/*     IF  NOT Anadat._File._File-Name BEGINS 'Adr' AND        */
/*         NOT Anadat._File._File-Name BEGINS 'Art' AND        */
/*         NOT Anadat._File._File-name BEGINS 'Auf' AND        */
/*         NOT Anadat._File._File-name BEGINS 'Rue' AND        */
/*         NOT Anadat._File._File-name BEGINS 'Sp'  THEN NEXT. */
        
    IF Anadat._File._File-Name = 'Artbw' THEN NEXT.
    
    FIND FIRST Anadat._Field NO-LOCK 
        WHERE Anadat._Field._File-Recid = RECID(Anadat._File)
        AND   Anadat._Field._Field-Name = 'Firma' NO-ERROR.
    lFirma = (IF AVAILABLE Anadat._Field THEN TRUE ELSE FALSE).

    CREATE BUFFER hBufferA FOR TABLE Anadat._File._File-Name.         
    CREATE TEMP-TABLE htTable.
    htTable:CREATE-LIKE(hBufferA).
    htTable:TEMP-TABLE-PREPARE(SUBSTITUTE('t&1', Anadat._File._File-Name)).
    htBuffer = htTable:DEFAULT-BUFFER-HANDLE.
    
    CREATE QUERY hQueryA.
    hQueryA:SET-BUFFERS (hBufferA).
    IF lFirma THEN cWhere = SUBSTITUTE('FOR EACH Anadat.&1 WHERE Anadat.&1.Firma = "1000" ', Anadat._File._File-Name).
    ELSE           cWhere = SUBSTITUTE('FOR EACH Anadat.&1 ', Anadat._File._File-Name).
    hQueryA:QUERY-PREPARE(cWhere).
    hQueryA:QUERY-OPEN().
    hQueryA:GET-FIRST().
    i1 = 0.
    DO WHILE NOT hQueryA:QUERY-OFF-END:
        htBuffer:BUFFER-CREATE().
        htBuffer:BUFFER-COPY(hBufferA).
        i1 = i1 + 1.
        hQueryA:GET-NEXT().
    END.
    hQueryA:QUERY-CLOSE().
    DELETE OBJECT hQueryA.
    DELETE OBJECT hBufferA.
    
    CONNECT '-db Ge_MIS -N TCP -S 17010 -ld TEST ' NO-ERROR.
    
    FIND Anadat._Index WHERE RECID(Anadat._Index) = Anadat._File._Prime-Index.
    cWhere  = ''.
    cFeld   = ''.
    cType   = ''.
    cUnique = ''.
    i1      = 0.
    iNeu    = 0.
    iAlt    = 0.
    FOR EACH Anadat._Index-Field OF Anadat._Index,
        FIRST Anadat._Field NO-LOCK
        WHERE RECID(Anadat._Field) = Anadat._Index-Field._Field-Recid.
        i1 = i1 + 1.
        cFeld[i1] = Anadat._Field._Field-Name.
        cType[i1] = Anadat._Field._Data-Type.
        cUnique = cUnique
            + (IF cUnique = '' THEN 'WHERE ' ELSE 'AND   ').
        cUnique = cUnique
            + SUBSTITUTE('TEST.&1.&2 = ', Anadat._File._File-Name, Anadat._Field._Field-Name).
        CASE cType[i1].
            WHEN 'character' THEN 
                cUnique = cUnique + '"&' + STRING(i1,"9") + '" '.
            OTHERWISE             
            cUnique = cUnique + '&'   + STRING(i1,"9") + ' '. 
        END CASE.
    END.
    
    CREATE BUFFER hBufferA FOR TABLE SUBSTITUTE('TEST.&1', Anadat._File._File-Name).
    hBufferA:DISABLE-LOAD-TRIGGERS(FALSE).
    
    CREATE QUERY hQueryA.
    hQueryA:SET-BUFFERS (htBuffer).
    cWhere = SUBSTITUTE('FOR EACH &1 ', htBuffer:NAME).
    hQueryA:QUERY-PREPARE(cWhere).
    hQueryA:QUERY-OPEN().
    hQueryA:GET-FIRST().
    i1 = 0.
    DO WHILE NOT hQueryA:QUERY-OFF-END TRANSACTION:
        cParam = ''.
        DO i1 = 1 TO 9:
            IF cFeld[i1] = '' THEN LEAVE.
            cParam[i1]   = htBuffer:BUFFER-FIELD(cFeld[i1]):BUFFER-VALUE(0).
        END.
        cWhere = SUBSTITUTE(cUnique, cParam[01], cParam[02], cParam[03], cParam[04], cParam[05], cParam[06], cParam[07], cParam[08], cParam[09]).
        lFound = hBufferA:FIND-UNIQUE(cWhere, EXCLUSIVE-LOCK, NO-WAIT) NO-ERROR.
        IF hBufferA:LOCKED THEN DO:
            hQueryA:GET-NEXT().
            NEXT.
        END.
        IF NOT lFound THEN 
        DO:
            hBufferA:BUFFER-CREATE().
            hBufferA:BUFFER-COPY(htBuffer).
            hBufferA:BUFFER-RELEASE.
            iNeu = iNeu + 1.
        END.
        ELSE 
        DO:
            lEqual = htBuffer:BUFFER-COMPARE(hBufferA).
            IF NOT lEqual THEN DO:
                hBufferA:BUFFER-COPY(htBuffer).
                iAlt = iAlt + 1.
            END.
            hBufferA:BUFFER-RELEASE.
        END.
        hQueryA:GET-NEXT().
    END.
    hQueryA:QUERY-CLOSE().
    DELETE OBJECT hQueryA.
    
    DELETE OBJECT htTable.
    DELETE OBJECT hBufferA.
    
    DISCONNECT 'TEST'.
    
    OUTPUT TO VALUE(cDatei) APPEND NO-MAP NO-CONVERT.
    PUT CONTROL
        STRING(TODAY,'99.99.9999')      ' '
        STRING(TIME ,'HH:MM:SS')        ' '
        STRING(Anadat._File._File-Name,"x(20)")
        STRING(iNeu,'zzz,zzz,zz9')      ' '         
        STRING(iAlt,'zzz,zzz,zz9')      ' ' CHR(10).         
    OUTPUT CLOSE.    
END.