GeMIS_WEB/GUI/SESAMSQL.p

&ANALYZE-SUSPEND _VERSION-NUMBER AB_v10r12 - ADM2
&ANALYZE-RESUME
{adecomm/appserv.i}
&ANALYZE-SUSPEND _UIB-CODE-BLOCK _CUSTOM _DEFINITIONS Procedure 
/*------------------------------------------------------------------------
    File        : 
    Purpose     :

    Syntax      :

    Description :

    Author(s)   :
    Created     :
    Notes       :
  ----------------------------------------------------------------------*/
/*          This .W file was created with the Progress AppBuilder.      */
/*----------------------------------------------------------------------*/

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


DEFINE VARIABLE ObjRecordSet  AS COMPONENT-HANDLE NO-UNDO.
DEFINE VARIABLE ObjConnection AS COMPONENT-HANDLE NO-UNDO.
DEFINE VARIABLE ObjCommand    AS COMPONENT-HANDLE NO-UNDO.
DEFINE VARIABLE ODBC-DSN      AS CHARACTER        NO-UNDO.
DEFINE VARIABLE ODBC-SERVER   AS CHARACTER        NO-UNDO.
DEFINE VARIABLE ODBC-USERID   AS CHARACTER        NO-UNDO.
DEFINE VARIABLE ODBC-PASSWD   AS CHARACTER        NO-UNDO.
DEFINE VARIABLE ODBC-QUERY    AS CHARACTER        NO-UNDO.
DEFINE VARIABLE ODBC-STATUS   AS CHARACTER        NO-UNDO.
DEFINE VARIABLE ODBC-RECCOUNT AS INTEGER          NO-UNDO.
DEFINE VARIABLE ODBC-NULL     AS CHARACTER        NO-UNDO.
DEFINE VARIABLE ODBC-CURSOR   AS INTEGER          NO-UNDO.

DEFINE VARIABLE fEnde         AS LOG              NO-UNDO.
DEFINE VARIABLE cTempName     AS CHARACTER        NO-UNDO.
DEFINE VARIABLE cTempDir      AS CHARACTER        NO-UNDO.
DEFINE VARIABLE cJournal      AS CHARACTER        NO-UNDO.
DEFINE VARIABLE WebString     AS CHARACTER        NO-UNDO.

DEFINE VARIABLE AllesOK       AS LOG              NO-UNDO.
DEFINE VARIABLE Sprcd         AS INTEGER          NO-UNDO.
DEFINE VARIABLE LVAdx         AS CHARACTER        NO-UNDO EXTENT 15.
DEFINE VARIABLE LVAdy         AS CHARACTER        NO-UNDO EXTENT 15.
DEFINE VARIABLE cOrt          AS CHARACTER        NO-UNDO.
DEFINE VARIABLE Firma         AS CHARACTER        NO-UNDO INIT '9999'.
DEFINE VARIABLE GVFirma       AS CHARACTER        NO-UNDO INIT '9999'.
DEFINE VARIABLE ja            AS LOG              NO-UNDO.
DEFINE VARIABLE CrLf          AS CHARACTER        NO-UNDO.
DEFINE VARIABLE cMessage      AS CHARACTER        NO-UNDO.
DEFINE VARIABLE iAnzOp        AS INTEGER          NO-UNDO.
DEFINE VARIABLE nTotBetr      AS DECIMAL          NO-UNDO.
DEFINE VARIABLE lAnmeldung    AS LOGICAL          NO-UNDO INIT FALSE.
DEFINE VARIABLE cBenutzer    AS CHARACTER NO-UNDO.
DEFINE VARIABLE cPasswort    AS CHARACTER NO-UNDO.
DEFINE VARIABLE cMandant     AS CHARACTER NO-UNDO.

DEFINE STREAM LogStream.
DEFINE STREAM Logfile.

DEFINE TEMP-TABLE tMwstBetr
    FIELD SammelKonto AS CHARACTER
    FIELD MwstCd      AS INTEGER
    FIELD MwstPfl     AS DECIMAL   DECIMALS 2
    FIELD MwstBet     AS DECIMAL   DECIMALS 2
    FIELD MwstAns     AS DECIMAL   DECIMALS 2.

DEFINE TEMP-TABLE tDebop   
    FIELD DocType     AS INTEGER
    FIELD OpId        AS CHARACTER
    FIELD KndNr       AS INTEGER
    FIELD SammelKonto AS CHARACTER
    FIELD OpText      AS CHARACTER
    FIELD OpDate      AS DATE
    FIELD ZabTyp      AS CHARACTER
    FIELD SortId      AS CHARACTER
    FIELD Firma       AS CHARACTER
    FIELD Zusatz1     AS CHARACTER
    FIELD Strasse     AS CHARACTER
    FIELD Plz         AS CHARACTER
    FIELD Ort         AS CHARACTER
    FIELD Telefon1    AS CHARACTER
    FIELD Telefon4    AS CHARACTER
    FIELD EsrNr       AS CHARACTER
    FIELD Sprache     AS CHARACTER
    FIELD iStatus     AS INTEGER
    
    INDEX tDebop-k1 IS PRIMARY
    OpId
    .
DEFINE TEMP-TABLE TZaKo    
    FIELD ZabTyp  AS CHARACTER
    FIELD ZabTyp2 AS CHARACTER.

DEFINE TEMP-TABLE TDetail
    FIELD DocKey       AS INTEGER
    FIELD DocLine      AS INTEGER
    FIELD SammelKonto  AS CHARACTER
    FIELD cText        AS CHARACTER
    FIELD MwstTyp      AS CHARACTER
    FIELD Betrag       AS DECIMAL   DECIMALS 2
    FIELD BetragMwst   AS DECIMAL   DECIMALS 2
    FIELD BetragFW     AS DECIMAL   DECIMALS 2
    FIELD BuchTyp      AS CHARACTER
    FIELD Kostenstelle AS CHARACTER
    FIELD cCode        AS CHARACTER
    FIELD iStatus      AS INTEGER.

DEFINE TEMP-TABLE tOpSaldo 
    FIELD OpId     AS CHARACTER
    FIELD PkNr     AS INTEGER
    FIELD Betrag   AS DECIMAL
    FIELD BetragFW AS DECIMAL
    INDEX Kunde IS PRIMARY
    PkNr
    INDEX Faknr
    OpId.

DEFINE TEMP-TABLE TBeleg       
    FIELD OpID   AS CHARACTER
    FIELD KndNr  AS INTEGER
    FIELD DocKey AS INTEGER
    
    INDEX Faknr IS PRIMARY
    OpId
    .

DEFINE TEMP-TABLE TBelegDetail 
    FIELD DocKey  AS INTEGER
    FIELD DocLine AS INTEGER
    INDEX DokNr IS PRIMARY
    DocKey.

DEFINE TEMP-TABLE xDetail
    FIELD DocKey       AS INTEGER
    FIELD DocLine      AS INTEGER
    FIELD Konto        AS CHARACTER
    FIELD cText        AS CHARACTER
    FIELD MwstTyp      AS CHARACTER
    FIELD Betrag       AS DECIMAL   DECIMALS 2
    FIELD BetragMwst   AS DECIMAL   DECIMALS 2
    FIELD BetragFW     AS DECIMAL   DECIMALS 2
    FIELD BuchTyp      AS CHARACTER
    FIELD Kostenstelle AS CHARACTER
    FIELD cCode        AS CHARACTER
    FIELD iStatus      AS INTEGER.
    
DEFINE TEMP-TABLE xOpSaldo LIKE tOpSaldo.

{ funktionen.i }
{ ttsendmail.i }
{ properties.i }

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME


&ANALYZE-SUSPEND _UIB-PREPROCESSOR-BLOCK 

/* ********************  Preprocessor Definitions  ******************** */

&Scoped-define PROCEDURE-TYPE Procedure
&Scoped-define DB-AWARE no



/* _UIB-PREPROCESSOR-BLOCK-END */
&ANALYZE-RESUME


/* ************************  Function Prototypes ********************** */

&IF DEFINED(EXCLUDE-cancelSQLTransaction) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD cancelSQLTransaction Procedure 
FUNCTION cancelSQLTransaction RETURNS LOGICAL
    ( /* parameter-definitions */ )  FORWARD.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-CloseConnection) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD CloseConnection Procedure 
FUNCTION CloseConnection RETURNS LOGICAL
    ( /* parameter-definitions */ )  FORWARD.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-closeSQLTransaction) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD closeSQLTransaction Procedure 
FUNCTION closeSQLTransaction RETURNS LOGICAL
    ( /* parameter-definitions */ )  FORWARD.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-CreateConnection) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD CreateConnection Procedure 
FUNCTION CreateConnection RETURNS LOGICAL
    ( /* parameter-definitions */ )  FORWARD.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-CreateSetDaten) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD CreateSetDaten Procedure 
FUNCTION CreateSetDaten RETURNS CHARACTER
    ( iphBuffer AS HANDLE, ipArt AS INTEGER )  FORWARD.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF
&IF DEFINED(EXCLUDE-getLogName) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD getLogName Procedure 
FUNCTION getLogName RETURNS CHARACTER
    ( /* parameter-definitions */ )  FORWARD.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF
&IF DEFINED(EXCLUDE-lesenBeleg) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD lesenBeleg Procedure 
FUNCTION lesenBeleg RETURNS INTEGER
    ( /* parameter-definitions */ )  FORWARD.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-lesenBelegDetail) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD lesenBelegDetail Procedure 
FUNCTION lesenBelegDetail RETURNS INTEGER
    ( /* parameter-definitions */ )  FORWARD.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-lesenOpSaldo) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD lesenOpSaldo Procedure 
FUNCTION lesenOpSaldo RETURNS INTEGER
    ( /* parameter-definitions */ )  FORWARD.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF
&IF DEFINED(EXCLUDE-SendMail) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD SendMail Procedure
FUNCTION SendMail RETURNS LOGICAL 
    (  ) FORWARD.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME


&ENDIF

&IF DEFINED(EXCLUDE-startSQLTransaction) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD startSQLTransaction Procedure 
FUNCTION startSQLTransaction RETURNS LOGICAL
    ( /* parameter-definitions */ )  FORWARD.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-updateBeleg) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD updateBeleg Procedure 
FUNCTION updateBeleg RETURNS LOGICAL
    ( ipTHandle AS HANDLE, ipArt AS INTEGER )  FORWARD.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-updateBelegDetail) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD updateBelegDetail Procedure 
FUNCTION updateBelegDetail RETURNS LOGICAL
    ( ipTHandle AS HANDLE, ipArt AS INTEGER )  FORWARD.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF
&IF DEFINED(EXCLUDE-writeLogFile) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD writeLogFile Procedure 
FUNCTION writeLogFile RETURNS LOGICAL
    ( ipMessage AS CHARACTER  )  FORWARD.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF


/* *********************** Procedure Settings ************************ */

&ANALYZE-SUSPEND _PROCEDURE-SETTINGS
/* Settings for THIS-PROCEDURE
   Type: Procedure
   Allow: 
   Frames: 0
   Add Fields to: Neither
   Other Settings: CODE-ONLY COMPILE APPSERVER
 */
&ANALYZE-RESUME _END-PROCEDURE-SETTINGS

/* *************************  Create Window  ************************** */

&ANALYZE-SUSPEND _CREATE-WINDOW
/* DESIGN Window definition (used by the UIB) 
  CREATE WINDOW Procedure ASSIGN
         HEIGHT             = 27.48
         WIDTH              = 132.8.
/* END WINDOW DEFINITION */
                                                                        */
&ANALYZE-RESUME

 


&ANALYZE-SUSPEND _UIB-CODE-BLOCK _CUSTOM _MAIN-BLOCK Procedure 


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


REPEAT TRANSACTION ON ERROR UNDO, LEAVE:
    RUN ANMELDEN.
    IF NOT lAnmeldung THEN QUIT.
    LEAVE.
END.

cTempName = DYNAMIC-FUNCTION('getLogName':U) NO-ERROR.
cJournal  = REPLACE(cTempName, '.log', '.csv').
    
GET-KEY-VALUE SECTION 'Grundeinstellungen'
    KEY     'SESAM_SQL_LOG'
    VALUE   cTempDir.
IF cTempDir = ?  THEN cTempDir = SESSION:TEMP-DIRECTORY.
IF cTempDir = '' THEN cTempDir = SESSION:TEMP-DIRECTORY.
    
cMessage = 'Start Datentransfer Ge_MIS <-> SAGE '.
DYNAMIC-FUNCTION('writeLogFile':U, cMessage) NO-ERROR.

IF TIME > 21600 THEN 
DO:    /*  Test auf zweimalige Ausführung nur nach 06:00 Uhr   */
    FIND Steuer NO-LOCK
        WHERE Steuer.Firma = cMandant.
    IF Steuer.Fwi30 = 1 THEN 
    DO:
        cMessage = 'Es ist bereits ein Task am Arbeiten '.
        DYNAMIC-FUNCTION('writeLogFile':U, cMessage) NO-ERROR.
        QUIT.
    END.
END.
    
ja = DYNAMIC-FUNCTION('CreateConnection':U) NO-ERROR.
IF NOT ja THEN QUIT.
    
REPEAT TRANSACTION:
    FIND Steuer
        WHERE Steuer.Firma = cMandant.
    Steuer.Fwi30 = 1.
    RELEASE Steuer.
    LEAVE.
END.
    
RUN FUELLEN_OP.
RUN LESEN_OFFENESALDI.

DYNAMIC-FUNCTION('CloseConnection':U) NO-ERROR.

cMessage = 'Ende Datentransfer Ge_MIS <-> SAGE ' + CHR(10).
DYNAMIC-FUNCTION('writeLogFile':U, cMessage) NO-ERROR.

DYNAMIC-FUNCTION ('sendMail':U) NO-ERROR.
    
REPEAT TRANSACTION:
    FIND Steuer
        WHERE Steuer.Firma = cMandant.
    Steuer.Fwi30 = 0.
    RELEASE Steuer.
    LEAVE.
END.

QUIT.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME


/* **********************  Internal Procedures  *********************** */

&IF DEFINED(EXCLUDE-ANMELDEN) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE ANMELDEN Procedure
PROCEDURE ANMELDEN:
    /*------------------------------------------------------------------------------*/
    /*  Purpose:                                                                    */
    /*  Parameters:  <none>                                                         */
    /*  Notes:                                                                      */
    /*------------------------------------------------------------------------------*/

    DEFINE VARIABLE cString AS CHARACTER NO-UNDO.
    DEFINE VARIABLE ix      AS INTEGER   NO-UNDO.
    
    DO WHILE TRUE:

        ASSIGN  
            cBenutzer = ''
            cPasswort = ''
            cMandant  = ''.
            
        DO ix = 1 TO NUM-ENTRIES(SESSION:PARAMETER, ';'):
            CASE ix:
                WHEN 1 THEN 
                    cBenutzer = ENTRY(ix, SESSION:PARAMETER, ';') NO-ERROR.
                WHEN 2 THEN 
                    cPasswort = ENTRY(ix, SESSION:PARAMETER, ';') NO-ERROR.
                WHEN 3 THEN 
                    cMandant  = ENTRY(ix, SESSION:PARAMETER, ';') NO-ERROR.
            END CASE.
        END.
    
        FIND FIRST Benutzer NO-LOCK USE-INDEX Benutzer-k1
            WHERE Benutzer.Benutzer = cBenutzer
            AND   Benutzer.Kennwort = cPasswort NO-ERROR.
        IF NOT AVAILABLE Benutzer THEN 
        DO:
            LEAVE.
        END.
    
        FIND Mandant NO-LOCK USE-INDEX Mandant-k1
            WHERE Mandant.Firma = cMandant NO-ERROR.
        IF NOT AVAILABLE Mandant THEN 
        DO:
            LEAVE.
        END.
    
        cString = cBenutzer + CHR(01) + cPasswort + CHR(01) + cMandant.
        RUN ANMELDUNG ( INPUT cString ).
        RUN AUFTRAGFUNKTIONENINIT.
    
        lAnmeldung = TRUE.
        LEAVE.
    END.

END PROCEDURE.
    
/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME


&ENDIF


&IF DEFINED(EXCLUDE-FUELLEN_OP) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE FUELLEN_OP Procedure 
PROCEDURE FUELLEN_OP :
    /*------------------------------------------------------------------------------
      Purpose:     
      Parameters:  <none>
      Notes:       
    ------------------------------------------------------------------------------*/

    DEFINE VARIABLE htDebop  AS HANDLE    NO-UNDO.
    DEFINE VARIABLE htDetail AS HANDLE    NO-UNDO.
    DEFINE VARIABLE xx       AS INTEGER   NO-UNDO.
    DEFINE VARIABLE cOrt     AS CHARACTER NO-UNDO.
    DEFINE VARIABLE dFakBetr AS DECIMAL   DECIMALS 2 NO-UNDO.
    DEFINE VARIABLE BuchBetr AS DECIMAL   DECIMALS 2 NO-UNDO.
    DEFINE VARIABLE Ansatz   AS DECIMAL   NO-UNDO.
    DEFINE VARIABLE Inkl     AS LOG       NO-UNDO.
    DEFINE VARIABLE ii       AS INTEGER   NO-UNDO.
    DEFINE VARIABLE cString  AS CHARACTER NO-UNDO.
    DEFINE VARIABLE iDocKey  AS INTEGER   NO-UNDO.
    DEFINE VARIABLE cAdresse AS CHARACTER NO-UNDO.
    DEFINE VARIABLE dMwst    AS DECIMAL   NO-UNDO.
    DEFINE VARIABLE lErst    AS LOG       NO-UNDO.
    DEFINE VARIABLE lMwstAdd AS LOG       NO-UNDO.
    DEFINE VARIABLE nDiff    AS DECIMAL   DECIMALS 2 NO-UNDO.
    DEFINE VARIABLE cExport  AS CHARACTER NO-UNDO.
    DEFINE VARIABLE iMwstCd  AS INTEGER   NO-UNDO.

    EMPTY TEMP-TABLE tDebop  .
    EMPTY TEMP-TABLE tDetail .
    EMPTY TEMP-TABLE xDetail .
    EMPTY TEMP-TABLE xOpSaldo.
    htDebop  = BUFFER tDebop :HANDLE.
    htDetail = BUFFER xDetail:HANDLE.
    
    CREATE xDetail.

    FIND Steuer WHERE Steuer.Firma = cMandant NO-LOCK.

    cMessage = ' Einlesen Daten aus SESAM gestartet'.
    DYNAMIC-FUNCTION('writeLogFile':U, cMessage) NO-ERROR.
    
    xx = DYNAMIC-FUNCTION('lesenOpSaldo':U) NO-ERROR.
    
    cMessage = SUBSTITUTE('  Anzahl eingelesene verarbeitete Rechnungen aus SESAM -> &1 ', xx).
    DYNAMIC-FUNCTION('writeLogFile':U, cMessage) NO-ERROR.

    xx = DYNAMIC-FUNCTION('lesenBeleg':U) NO-ERROR.

    cMessage = SUBSTITUTE('  Anzahl eingelesene Belege aus SESAM -> &1 ', xx).
    DYNAMIC-FUNCTION('writeLogFile':U, cMessage) NO-ERROR.

    xx = DYNAMIC-FUNCTION('lesenBelegDetail':U) NO-ERROR.
    
    cMessage = SUBSTITUTE(' Anzahl eingelesene Beleg-Details aus SESAM -> &1 ', xx).
    DYNAMIC-FUNCTION('writeLogFile':U, cMessage) NO-ERROR.

    cMessage = ' Einlesen Daten aus SESAM beendet'.
    DYNAMIC-FUNCTION('writeLogFile':U, cMessage) NO-ERROR.
    
    cMessage = ' '.
    DYNAMIC-FUNCTION('writeLogFile':U, cMessage) NO-ERROR.
    
    cExport = SUBSTITUTE('&1Beleg.csv', cTempDir).
    OUTPUT TO VALUE(cExport) NO-MAP NO-CONVERT.
    FOR EACH tBeleg:
        EXPORT DELIMITER ';' tBeleg.
    END.
    OUTPUT CLOSE.

    cExport = SUBSTITUTE('&1BelegDetail.csv', cTempDir).
    OUTPUT TO VALUE(cExport) NO-MAP NO-CONVERT.
    FOR EACH tBelegDetail:
        EXPORT DELIMITER ';' tBelegDetail.
    END.
    OUTPUT CLOSE.

    xx = 0.
    FOR EACH Debop NO-LOCK
        WHERE Debop.Firma    = cMandant
        AND   Debop.Faknr   >= 30000
        AND ((Debop.Fakbetr <> 0 AND
        Debop.Saldo   <> 0.0)
        OR  (Debop.Fakbetr  = 0)) ,
        
        FIRST Adresse NO-LOCK
        WHERE Adresse.Firma = Debop.Firma
        AND   Adresse.Knr   = Debop.Knr :

        FIND FIRST tBeleg NO-LOCK
            WHERE tBeleg.OpId = TRIM(STRING(Debop.Faknr,'>>>>>>>')) NO-ERROR.
        IF AVAILABLE tBeleg THEN NEXT.

        IF Debop.Fakbetr = 0 THEN 
        DO:
            dMwst = 0.
            DO ii = 1 TO 10:
                dMwst = dMwst + Debop.FakWpfl[ii].
            END.
            IF dMwst = 0 THEN NEXT.
        END.
                      
        FIND Orte OF Adresse NO-LOCK NO-ERROR.
        IF Adresse.Ort <> ''   THEN cOrt = Adresse.Ort.
        ELSE IF AVAILABLE Orte THEN cOrt = Orte.Ort.
            ELSE                        cOrt = '??????????'.
        FIND Sprache OF Adresse NO-LOCK NO-ERROR.
        IF Adresse.Firma1 <> '' THEN cAdresse = TRIM(Adresse.Firma1).
        ELSE                         cAdresse = TRIM(Adresse.Name + ' ' + Adresse.Vorname).
        cAdresse = TRIM(SUBSTRING(cAdresse,01,30)).

        CREATE  tDebop.
        ASSIGN  
            tDebop.DocTyp      = 0
            tDebop.OpId        = TRIM(STRING(Debop.Faknr,'>>>>>>>>'))
            tDebop.KndNr       = Debop.Knr
            tDebop.SammelKonto = Debop.Konto
            tDebop.OpDate      = Debop.Fakdat
            tDebop.ZabTyp      = STRING(Debop.Kond,'99')
            tDebop.Firma       = cAdresse
            tDebop.SortId      = TRIM(SUBSTRING(Adresse.Suchbe ,01,10))
            tDebop.Zusatz1     = TRIM(SUBSTRING(Adresse.Zusatz1,01,30))
            tDebop.Strasse     = TRIM(SUBSTRING(Adresse.Strasse,01,30))
            tDebop.Plz         = TRIM(SUBSTRING(Adresse.Plz    ,01,06))
            tDebop.Ort         = TRIM(SUBSTRING(cOrt           ,01,24))
            tDebop.Telefon1    = TRIM(SUBSTRING(Adresse.Tel-1  ,01,20))
            tDebop.Telefon4    = TRIM(SUBSTRING(Adresse.Tel-3  ,01,20))
            tDebop.EsrNr       = ''
            tDebop.Sprache     = (IF AVAILABLE Sprache THEN SUBSTRING(Sprache.Bez,01,01) ELSE 'D')
            tDebop.iStatus     = 9.
        .
        IF tDebop.Firma = ''
            THEN tDebop.Firma = TRIM(SUBSTRING((Adresse.NAME + ' ' + Adresse.Vorname),01,30)).

        dFakBetr = Debop.FakBetr.
        EMPTY TEMP-TABLE tMwstBetr.
        FOR EACH Interf NO-LOCK
            WHERE Interf.Firma    = Steuer.Fwc09
            AND   Interf.TrNr1    = Debop.TrNr1 
            AND   Interf.TrNr2    = Debop.TrNr2
            AND   Interf.Herk    >= 50
            AND   Interf.Herk    <= 59
            AND   Interf.MWSt_Art = 0
            BREAK BY Interf.Firma
            BY Interf.TrNr1
            BY Interf.TrNr2
            BY Interf.Trnr :
                                    
            FIND FIRST tMwstBetr
                WHERE tMwstbetr.SammelKonto  = Interf.Kto2
                AND   tMwstbetr.MwstCd       = Interf.Mwst_Cd NO-ERROR.
            IF NOT AVAILABLE tMwstBetr THEN 
            DO:
                CREATE  tMwstBetr.
                ASSIGN  
                    tMwstBetr.SammelKonto = Interf.Kto2
                    tMwstBetr.MwstCd      = Interf.Mwst_Cd
                    tMwstBetr.MwstAns     = Debop.Fakwpro[Interf.Mwst_Cd].
            END.
            ASSIGN  
                tMwstBetr.MwstPfl = tMwstBetr.MwstPfl + Interf.Betrag.
            IF NOT LAST-OF ( Interf.TrNr2 ) THEN NEXT.
            
            BuchBetr = 0.
            lMwstAdd = FALSE.
            FOR EACH tMwstBetr
                BREAK BY tMwstBetr.MwstCd:
                IF FIRST-OF ( tMwstBetr.MwstCd ) THEN BuchBetr = 0.
                BuchBetr = BuchBetr + tMwstBetr.MwstPfl.
                IF NOT LAST-OF ( tMwstBetr.MwstCd ) THEN NEXT.
                
                ii = tMwstBetr.MwstCd.
                IF ABS(Debop.FakWpfl[ii] - BuchBetr) < ABS(Debop.FakWpfl[ii] * 0.01) THEN 
                DO.
                    lMwstAdd = TRUE.
                    LEAVE.
                END.
            END.

            /*  lMwstAdd = TRUE --> Beim Betrag muss die Mwst dazugerechnet werden  */
            /*  In der Schnittstelle werden nur Inklusiv-Beträge übermittelt        */
            IF lMwstAdd THEN 
            DO:
                FOR EACH tMwstBetr :
                    tMwstBetr.MwstPfl = tMwstBetr.MwstPfl * (100 + tMwstBetr.MwstAns) / 100.
                END.
            END.
           
            /*  Different zwischen Fakbetr und einzelnen Buchungen errechnen    */
            /*  Differenz auf dem Konto mit dem grössten Betrag ausgleichen     */
            Buchbetr = 0.
            FOR EACH tMwstBetr:
                tMwstBetr.MwstPfl = DECIMAL(STRING(tMwstBetr.MwstPfl,'999999999.99-')).
                BuchBetr          = BuchBetr + tMwstBetr.MwstPfl.
            END.
            Buchbetr = DECIMAL(STRING(Buchbetr,'999999999.99-')).
            nDiff    = dFakBetr - Buchbetr.
            FOR FIRST tMwstBetr
                BY tMwstBetr.MwstPfl DESCENDING:
                tMwstBetr.MwstPfl = tMwstBetr.MwstPfl + nDiff.
            END.

            ii = 0.            
            FOR EACH tMwstBetr
                BY tMwstBetr.SammelKonto
                BY tMwstBetr.MwstCd:
                iMwstCd = (IF tMwstBetr.MwstCd < 5 OR
                    tMwstBetr.MwstCd > 6 THEN tMwstBetr.MwstCd ELSE 13).
                ii = ii + 1.
                CREATE  tDetail.
                ASSIGN  
                    tDetail.DocKey       = Debop.Faknr
                    tDetail.DocLine      = ii
                    tDetail.SammelKonto  = tMwstBetr.SammelKonto
                    tDetail.cText        = ''
                    tDetail.MwstTyp      = STRING(iMWStCd,'99')
                    tDetail.Betrag       = tMwstBetr.MwstPfl
                    tDetail.BetragMwst   = 0
                    tDetail.BetragFW     = 0
                    tDetail.BuchTyp      = ''
                    tDetail.Kostenstelle = ''
                    tDetail.cCode        = ''
                    tDetail.iStatus      = 9.
            END.
        END.
        xx = xx + 1.
    END.

    cMessage = SUBSTITUTE(' Anzahl beruecksichtigte Rechnungen aus Ge_MIS -> &1', xx).
    DYNAMIC-FUNCTION('writeLogFile':U, cMessage) NO-ERROR.

    iAnzOp   = 0.
    nTotBetr = 0.00.
    
    xx = 0.
    DYNAMIC-FUNCTION('startSQLTransaction':U) NO-ERROR.
    FOR EACH tDebop:
        FIND FIRST tBeleg WHERE tBeleg.OpId = tDebop.OpID NO-ERROR.
        IF AVAILABLE tBeleg THEN NEXT.
        
        ja = DYNAMIC-FUNCTION('updateBeleg':U, INPUT htDebop, INPUT 1) NO-ERROR.
        tDebop.iStatus = (IF ja THEN 1 ELSE 0).
        
        IF tDebop.iStatus < 9 THEN 
        DO:
            CREATE xOpSaldo.
            ASSIGN  
                xOpSaldo.OpId = tDebop.OpId
                xOpSaldo.PkNr = tDebop.KndNr.
        END.
        
        xx = xx + 1.
        IF xx MOD 100 = 0 THEN 
        DO:
            DYNAMIC-FUNCTION('closeSQLTransaction':U) NO-ERROR.
            cMessage = SUBSTITUTE(' -> bereits eingefuegte Belege -> &1', xx).
            DYNAMIC-FUNCTION('writeLogFile':U, cMessage) NO-ERROR.
            
            ii = ETIME(TRUE).
            DO WHILE ETIME < 1000:
            END.

            DYNAMIC-FUNCTION('startSQLTransaction':U) NO-ERROR.
        END.

        FIND Debop NO-LOCK
            WHERE Debop.Firma = cMandant
            AND   Debop.Knr   = tDebop.KndNr
            AND   Debop.Faknr = INTEGER(tDebop.OpId) NO-ERROR.
        CREATE  tOpSaldo.
        ASSIGN  
            tOpSaldo.OpId     = TRIM(STRING(Debop.Faknr,'>>>>>>>>'))
            tOpSaldo.PkNr     = Debop.Knr
            tOpSaldo.Betrag   = Debop.Saldo
            tOpSaldo.BetragFW = Debop.Saldo NO-ERROR.
        IF tDebop.iStatus = 1 THEN
            ASSIGN  
                iAnzOp   = iAnzOp + 1
                nTotBetr = nTotBetr + Debop.Fakbetr.
    END.
    DYNAMIC-FUNCTION('closeSQLTransaction':U) NO-ERROR.
    
    cMessage = SUBSTITUTE(' Anzahl eingefügte Belege -> &1', xx).
    DYNAMIC-FUNCTION('writeLogFile':U, cMessage) NO-ERROR.

    ii = ETIME(TRUE).
    DO WHILE ETIME < 1000:
    END.

    xx = DYNAMIC-FUNCTION('lesenBeleg':U) NO-ERROR.

    cMessage = SUBSTITUTE(' Anzahl eingelesene Belege aus SESAM nach Belegverbuchen -> &1', xx).
    DYNAMIC-FUNCTION('writeLogFile':U, cMessage) NO-ERROR.

    xx = 0.
    DYNAMIC-FUNCTION('startSQLTransaction':U) NO-ERROR.
    FOR EACH tDetail:
        
        FIND FIRST tBeleg WHERE tBeleg.OpId = TRIM(STRING(tDetail.DocKey,'>>>>>>>>')) NO-ERROR.
        IF NOT AVAILABLE tBeleg THEN NEXT.
        
        iDocKey = tBeleg.DocKey.
        FIND FIRST tBelegDetail
            WHERE tBelegDetail.DocKey  = iDocKey
            AND   tBelegDetail.DocLine = tDetail.DocLine NO-ERROR.
        IF AVAILABLE tBelegDetail THEN NEXT.
        
        FIND FIRST xDetail.
        BUFFER-COPY tDetail EXCEPT DocKey SammelKonto TO xDetail
            ASSIGN  
            xDetail.DocKey = iDocKey
            xDetail.Konto  = tDetail.SammelKonto.
        /*
        tDetail.DocKey = cDocKey.
        IF tDetail.DocKey = ? THEN NEXT.
        IF tDetail.DocKey = 0 THEN NEXT.
        ja = DYNAMIC-FUNCTION('ifBelegDetail':U,
                               INPUT tDetail.DocKey,
                               INPUT tDetail.DocLine) NO-ERROR.
        IF ja = ?    THEN NEXT.
        IF ja = TRUE THEN NEXT.
        */
        DYNAMIC-FUNCTION('updateBelegDetail':U, INPUT htDetail, INPUT 1) NO-ERROR.
        tDetail.iStatus = (IF ja THEN 1 ELSE 0).
        xx = xx + 1.
        IF xx MOD 100 = 0 THEN 
        DO:
            DYNAMIC-FUNCTION('closeSQLTransaction':U) NO-ERROR.
            cMessage = SUBSTITUTE(' -> bereits eingefuegte BelegDetail -> &1', xx).
            DYNAMIC-FUNCTION('writeLogFile':U, cMessage) NO-ERROR.
            
            ii = ETIME(TRUE).
            DO WHILE ETIME < 500:
            END.

            DYNAMIC-FUNCTION('startSQLTransaction':U) NO-ERROR.
        END.
    END.
    DYNAMIC-FUNCTION('closeSQLTransaction':U) NO-ERROR.

    cMessage = SUBSTITUTE(' Anzahl eingefuegte BelegDetail -> &1', xx).
    DYNAMIC-FUNCTION('writeLogFile':U, cMessage) NO-ERROR.

    OUTPUT TO VALUE(cJournal) NO-MAP NO-CONVERT.
    FOR EACH tDebop:
        PUT CONTROL tDebop.OpId
            ';'
            tDebop.KndNr
            ';'
            tDebop.Firma
            ';'
            tDebop.Zusatz
            ';'
            tDebop.Ort
            ';'
            tDebop.iStatus
            ';'
            tDebop.SammelKonto
            ';'.
        lErst = TRUE.
        FIND FIRST tBeleg WHERE tBeleg.OpId = tDebop.OpId.
        FOR EACH tDetail WHERE tDetail.DocKey = INTEGER(tDebop.OpID):
            IF NOT lErst THEN PUT CONTROL ';;;;;;;'.
            PUT CONTROL tDetail.SammelKonto
                ';'
                tDetail.cText
                ';'
                tDetail.MwstTyp
                ';'
                TRIM(STRING(tDetail.BetragMwst,'->>>>>>>>9.99'))
                ';'
                TRIM(STRING(tDetail.Betrag    ,'->>>>>>>>9.99'))
                ';'
                tDetail.BuchTyp
                ';'
                tDetail.iStatus
                CHR(10).
            lErst = FALSE.
        END.
    END.
    OUTPUT CLOSE.
    
END PROCEDURE.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF
&IF DEFINED(EXCLUDE-LESEN_OFFENESALDI) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE LESEN_OFFENESALDI Procedure 
PROCEDURE LESEN_OFFENESALDI :
    /*------------------------------------------------------------------------------
      Purpose:     
      Parameters:  <none>
      Notes:       
    ------------------------------------------------------------------------------*/

    DEFINE VARIABLE Saldo   AS DECIMAL   NO-UNDO.
    DEFINE VARIABLE Diff    AS DECIMAL   NO-UNDO.
    DEFINE VARIABLE cExport AS CHARACTER NO-UNDO.
    
    DEFINE BUFFER BDebst FOR Debst.

    cExport = SUBSTITUTE('&1tOpSaldo.csv', cTempDir).
    OUTPUT TO VALUE(cExport) NO-MAP NO-CONVERT.
    FOR EACH tOpSaldo:
        EXPORT DELIMITER ';' tOpSaldo.
    END.
    OUTPUT CLOSE.

    FIND FIRST tOpSaldo NO-LOCK NO-ERROR.
    IF NOT AVAILABLE tOpSaldo THEN RETURN.

    FOR EACH Debst NO-LOCK:
        Saldo = 0.
        
        FOR EACH Debop USE-INDEX Debop-k1
            WHERE Debop.Firma = Debst.Firma
            AND   Debop.Knr   = Debst.Knr:
                
            FIND FIRST xOpSaldo NO-LOCK USE-INDEX Faknr
                WHERE xOpSaldo.OpId = TRIM(STRING(Debop.Faknr,'>>>>>>>>')) NO-ERROR.
            IF  AVAILABLE xOpSaldo THEN NEXT.
                
            FIND FIRST tOpSaldo NO-LOCK USE-INDEX Faknr
                WHERE tOpSaldo.OpId = TRIM(STRING(Debop.Faknr,'>>>>>>>>'))
                AND   tOpSaldo.PkNr = Debst.Knr NO-ERROR.
            IF AVAILABLE tOpSaldo THEN Diff = Debop.Saldo - tOpSaldo.Betrag.
            ELSE                       Diff = (IF Debop.Fakdat < (TODAY - 7) THEN Debop.Saldo ELSE 0).
            IF Diff <> 0 THEN 
            DO:
                Debop.ZahBetr = Debop.ZahBetr + Diff.
                Debop.Saldo   = Debop.Fakbetr - Debop.Zahbetr - Debop.Skonto.
                Debop.Zahdat  = TODAY.
            END.
            Saldo = Saldo + Debop.Saldo.
            IF Debop.Saldo  <> 0            THEN NEXT.
            IF Debop.FakDat > (TODAY - 360) THEN NEXT.
        /*
        DELETE  Debop.
        */
        END.
        IF Saldo = Debst.Saldo THEN NEXT.
        FIND BDebst WHERE RECID(BDebst) = RECID(Debst)
            EXCLUSIVE-LOCK NO-WAIT NO-ERROR.
        IF NOT AVAILABLE BDebst THEN NEXT.
        BDebst.Saldo = Saldo.
        RELEASE BDebst.
    END.

END PROCEDURE.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF
&IF DEFINED(EXCLUDE-RUNDEN) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE RUNDEN Procedure 
PROCEDURE RUNDEN :
    /*------------------------------------------------------------------------------
      Purpose:     
      Parameters:  <none>
      Notes:       
    ------------------------------------------------------------------------------*/

    DEFINE INPUT        PARAMETER Rundcode     AS INTEGER              NO-UNDO.
    DEFINE INPUT-OUTPUT PARAMETER Rundbetr     AS DECIMAL  DECIMALS 4  NO-UNDO.

    DEFINE VARIABLE VBetr  AS DECIMAL   DECIMALS 4 NO-UNDO.
    DEFINE VARIABLE VOp    AS DECIMAL   INIT 0.2 NO-UNDO.
    DEFINE VARIABLE XPChar AS CHARACTER NO-UNDO.

    DO TRANSACTION:
        VBetr = Rundbetr.

        IF Rundcode = 1 THEN 
        DO:
            VBetr = ROUND((VBetr / 100 * VOp), 4) / VOp * 100.
        END.
        IF Rundcode = 2 THEN 
        DO:
            VBetr = ROUND((VBetr / 100), 3) * 100.
        END.
        IF Rundcode = 3 THEN 
        DO:
            VBetr = VBetr + 0.0499.
            VBetr = ROUND((VBetr / 100), 3) * 100.
        END.
        IF Rundcode = 4 THEN 
        DO:
            VBetr = VBetr - 0.0500.
            VBetr = ROUND((VBetr / 100), 3) * 100.
        END.
        IF Rundcode = 5 THEN 
        DO:
            VBetr = ROUND((VBetr / 100), 2) * 100.
        END.
        IF Rundcode = 6 THEN 
        DO:
            VBetr = VBetr + 0.4999.
            VBetr = ROUND((VBetr / 100), 2) * 100.
        END.
        IF Rundcode = 7 THEN 
        DO:
            VBetr = VBetr - 0.5000.
            VBetr = ROUND((VBetr / 100), 2) * 100.
        END.
        IF Rundcode = 8 THEN 
        DO:
            VBetr = ROUND((VBetr / 1000), 2) * 1000.
        END.
        IF Rundcode = 9 THEN 
        DO:
            VBetr = VBetr + 4.9999.
            VBetr = ROUND((VBetr / 1000), 2) * 1000.
        END.
        IF Rundcode = 10 THEN 
        DO:
            VBetr = VBetr - 5.0000.
            VBetr = ROUND((VBetr / 1000), 2) * 1000.
        END.

        IF Rundcode = 99 THEN 
        DO:
            XPChar = STRING(VBetr,"-99999999.999").
            VBetr  = DECIMAL(SUBSTRING(XPChar,01,12)).
            IF SUBSTRING(XPChar,13,01) > "4" THEN VBetr = VBetr + 0.01.
        END.

        Rundbetr = VBetr.

    END.

END PROCEDURE.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF
/* ************************  Function Implementations ***************** */

&IF DEFINED(EXCLUDE-cancelSQLTransaction) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION cancelSQLTransaction Procedure 
FUNCTION cancelSQLTransaction RETURNS LOGICAL
    ( /* parameter-definitions */ ) :
    /*------------------------------------------------------------------------------
      Purpose:  
        Notes:  
    ------------------------------------------------------------------------------*/

    ODBC-QUERY = 'ROLLBACK'.

    ASSIGN  
        ObjCommand:CommandText = ODBC-QUERY
        ObjRecordSet           = ObjCommand:EXECUTE( OUTPUT ODBC-NULL, "", 32 ).

END FUNCTION.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-CloseConnection) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION CloseConnection Procedure 
FUNCTION CloseConnection RETURNS LOGICAL
    ( /* parameter-definitions */ ) :
    /*------------------------------------------------------------------------------
      Purpose:  
        Notes:  
    ------------------------------------------------------------------------------*/

    ObjConnection:Close NO-ERROR.

    RELEASE OBJECT ObjConnection NO-ERROR.
    RELEASE OBJECT ObjCommand    NO-ERROR.
    RELEASE OBJECT ObjRecordSet  NO-ERROR.

    RETURN TRUE.

END FUNCTION.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-closeSQLTransaction) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION closeSQLTransaction Procedure 
FUNCTION closeSQLTransaction RETURNS LOGICAL
    ( /* parameter-definitions */ ) :
    /*------------------------------------------------------------------------------
      Purpose:  
        Notes:  
    ------------------------------------------------------------------------------*/

    ODBC-QUERY = 'COMMIT'.

    ASSIGN  
        ObjCommand:CommandText = ODBC-QUERY
        ObjRecordSet           = ObjCommand:EXECUTE( OUTPUT ODBC-NULL, "", 32 ).

END FUNCTION.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-CreateConnection) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION CreateConnection Procedure 
FUNCTION CreateConnection RETURNS LOGICAL
    ( /* parameter-definitions */ ) :
    /*------------------------------------------------------------------------------
      Purpose:  
        Notes:  
    ------------------------------------------------------------------------------*/

    DEFINE VARIABLE cConnCommand AS CHARACTER NO-UNDO.
    DEFINE VARIABLE cDSN         AS CHARACTER NO-UNDO.
    DEFINE VARIABLE cServer      AS CHARACTER NO-UNDO.
    DEFINE VARIABLE ix           AS INTEGER   NO-UNDO.
    DEFINE VARIABLE ii           AS INTEGER   NO-UNDO.
    DEFINE VARIABLE cMessage     AS CHARACTER NO-UNDO.
    DEFINE VARIABLE cBenutzer    AS CHARACTER NO-UNDO.
    DEFINE VARIABLE cPassword    AS CHARACTER NO-UNDO.

    CREATE "ADODB.Connection"   ObjConnection NO-ERROR.
    CREATE "ADODB.RecordSet"    ObjRecordSet  NO-ERROR.
    CREATE "ADODB.Command"      ObjCommand    NO-ERROR.

    GET-KEY-VALUE SECTION 'Grundeinstellungen'
        KEY     'SQLServer'
        VALUE   cServer.
    IF cServer = ?  THEN RETURN FALSE.
    IF cServer = '' THEN RETURN FALSE.
  
    GET-KEY-VALUE SECTION 'Grundeinstellungen'
        KEY     'SQLDSN'
        VALUE   cDSN.
    IF cDSN    = ?  THEN RETURN FALSE.
    IF cDSN    = '' THEN RETURN FALSE.
  
    GET-KEY-VALUE SECTION 'Grundeinstellungen'
        KEY     'SQLDSNUser'
        VALUE   cBenutzer.
    IF cBenutzer = ?  THEN RETURN FALSE.
    IF cBenutzer = '' THEN RETURN FALSE.
  
    GET-KEY-VALUE SECTION 'Grundeinstellungen'
        KEY     'SQLDSNPwd'
        VALUE   cPassword.
    IF cPassword = ?  THEN RETURN FALSE.
    IF cPassword = '' THEN RETURN FALSE.
  
    ASSIGN  
        ODBC-STATUS = ''
        ODBC-NULL   = ''
        ODBC-DSN    = cDSN
        ODBC-SERVER = cServer
        ODBC-USERID = cBenutzer
        ODBC-PASSWD = cPassword
        ODBC-QUERY  = ''.

    cConnCommand = 'data source='
        + ODBC-DSN
        + ';server='
        + ODBC-SERVER
        .
    ObjConnection:Open ( cConnCommand, ODBC-USERID, ODBC-PASSWD, 0 ) NO-ERROR.
    IF ( ERROR-STATUS:NUM-MESSAGES > 0 ) THEN 
    DO:
        ii = ERROR-STATUS:NUM-MESSAGES.
        ODBC-STATUS = "Error: keine Verbindung zu Datenserver "
            + cConnCommand.
        cMessage = ODBC-STATUS.
        DO ix = 1 TO ii:
            cMessage = cMessage
                + CHR(10)
                + ERROR-STATUS:GET-MESSAGE(ix).
        END.
        DYNAMIC-FUNCTION('writeLogFile':U, cMessage) NO-ERROR.
        RETURN FALSE.
    END.

    ASSIGN  
        ObjCommand:ActiveConnection  = ObjConnection
        ObjCommand:CommandType       = 1 /* adCmdText       */
        ObjConnection:CursorLocation = 3 /* adUseClient     */
        ObjRecordSet:CursorType      = 3 /* adOpenStatic    */.

    cMessage = 'Start SESAM SQL mit Connection ' + cConnCommand.
    DYNAMIC-FUNCTION('writeLogFile':U, cMessage) NO-ERROR.
    
    RETURN TRUE.

END FUNCTION.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-CreateSetDaten) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION CreateSetDaten Procedure 
FUNCTION CreateSetDaten RETURNS CHARACTER
    ( iphBuffer AS HANDLE, ipArt AS INTEGER ) :
    /*------------------------------------------------------------------------------
      Purpose:  
        Notes:  
    ------------------------------------------------------------------------------*/

    DEFINE VARIABLE cSet     AS CHARACTER NO-UNDO.
    DEFINE VARIABLE ii       AS INTEGER   NO-UNDO.
    DEFINE VARIABLE hFeld    AS HANDLE    NO-UNDO.
    DEFINE VARIABLE cFeld    AS CHARACTER NO-UNDO.
    DEFINE VARIABLE cInhalt  AS CHARACTER NO-UNDO.
    DEFINE VARIABLE cFelder  AS CHARACTER NO-UNDO.
    DEFINE VARIABLE cInhalte AS CHARACTER NO-UNDO.
    DEFINE VARIABLE xFeld    AS CHARACTER NO-UNDO.
    DEFINE VARIABLE xInhalt  AS CHARACTER NO-UNDO.

    cSet     = ''.
    cFelder  = ''.
    cInhalte = ''.
    DO ii = 1 TO iphBuffer:NUM-FIELDS:
        hFeld = iphBuffer:BUFFER-FIELD(ii).
        IF hFeld:BUFFER-VALUE(0) = ? THEN NEXT.
        cFeld = hFeld:NAME.
        IF cFeld = 'cCode'   THEN cFeld = 'Code'. /*  ProgressSchlüsselFeld  */
        IF cFeld = 'cText'   THEN cFeld = 'Text'. /*  ProgressSchlüsselFeld  */
        IF cFeld = 'iStatus' THEN NEXT.           /*  Kein SQL-Feld          */
        DO WHILE TRUE:
            IF hFeld:BUFFER-VALUE(0) <> ?   AND
                hFeld:BUFFER-VALUE(0) <> '?' THEN LEAVE.

            CASE hFeld:DATA-TYPE:
                WHEN 'INTEGER' THEN 
                    hFeld:BUFFER-VALUE(0) = '0'.
                WHEN 'DECIMAL' THEN 
                    hFeld:BUFFER-VALUE(0) = '0.0'.
                WHEN 'DATE'    THEN 
                    hFeld:BUFFER-VALUE(0) = ''.
                WHEN 'LOGICAL' THEN 
                    hFeld:BUFFER-VALUE(0) = STRING(FALSE).
                OTHERWISE 
                hFeld:BUFFER-VALUE(0) = ''.
            END CASE.

            LEAVE.
        END.

        xFeld   = "[" + TRIM(cFeld) + "]".
        xInhalt = TRIM(hFeld:BUFFER-VALUE(0)).
        xInhalt = REPLACE(xInhalt, "'", '"').
        xInhalt = "'" + xInhalt + "'".

        cInhalte = cInhalte + "," + xInhalt.
        cFelder  = cFelder  + "," + xFeld.
    END.
    cInhalte = SUBSTRING(cInhalte,02).
    cFelder  = SUBSTRING(cFelder ,02).

    CASE ipArt:
        WHEN 1 THEN 
            DO:
                cSet = '('
                    + cFelder
                    + ') VALUES ('
                    + cInhalte
                    + ')'.
            END.
        WHEN 0 THEN 
            DO:
                cSet = 'SET '.
                DO ii = 1 TO NUM-ENTRIES(cFelder, ','):
                    cSet = cSet + ENTRY(ii, cFelder, ',')
                        + ' = '
                        + ENTRY(ii, cInhalte, ',').
                    IF ii < NUM-ENTRIES(cFelder, ',') THEN cSet = cSet + ', '.
                END.
            END.
    END CASE.

    RETURN cSet.

END FUNCTION.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF
&IF DEFINED(EXCLUDE-getLogName) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION getLogName Procedure 
FUNCTION getLogName RETURNS CHARACTER
    ( /* parameter-definitions */ ) :
    /*------------------------------------------------------------------------------
      Purpose:  
        Notes:  
    ------------------------------------------------------------------------------*/

    DEFINE VARIABLE cPath AS CHARACTER NO-UNDO.
    
    GET-KEY-VALUE SECTION 'GrundEinstellungen'
        KEY     'SESAM_SQL_LOG'
        VALUE   cPath.
    IF cPath = '' OR
        cPath = ?  THEN cPath = SESSION:TEMP-DIRECTORY.
    IF SUBSTRING(cPath, LENGTH(cPath), 01) <> '/' AND
        SUBSTRING(cPath, LENGTH(cPath), 01) <> '\' THEN cPath = cPath + '\'.
    cPath = cPath
        + 'SESAMSQL-'
        + REPLACE(STRING(TODAY,'99.99.9999'), '.', '')
        + REPLACE(STRING(TIME ,'HH:MM:SS')  , ':', '') + '.Log'.

    RETURN cPath.

END FUNCTION.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF
&IF DEFINED(EXCLUDE-lesenBeleg) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION lesenBeleg Procedure 
FUNCTION lesenBeleg RETURNS INTEGER
    ( /* parameter-definitions */ ) :
    /*------------------------------------------------------------------------------
      Purpose:  
        Notes:  
    ------------------------------------------------------------------------------*/

    EMPTY TEMP-TABLE tBeleg.

    ODBC-QUERY = 'SELECT [OpId],[KndNr],[DocKey] FROM [g.connect].[dbo].[Beleg]'.

    ASSIGN  
        ObjCommand:CommandText = ODBC-QUERY
        ObjRecordSet           = ObjCommand:EXECUTE ( OUTPUT ODBC-NULL, "", 32 )
        ODBC-RECCOUNT          = ObjRecordSet:RecordCount NO-ERROR.

    DO WHILE TRUE:
        IF ODBC-RECCOUNT = 0 THEN LEAVE.
        IF ODBC-RECCOUNT = ? THEN LEAVE.

        ODBC-CURSOR = 0.
        ObjRecordSet:MoveFirst NO-ERROR.

        DO WHILE ODBC-CURSOR < ODBC-RECCOUNT:
            CREATE  tBeleg.
            ASSIGN  
                tBeleg.Opid  = ObjRecordSet:FIELDS("OpId")  :VALUE
                tBeleg.KndNr = ObjRecordSet:FIELDS("KndNr") :VALUE.
            tBeleg.DocKey = ObjRecordSet:FIELDS("DocKey"):VALUE.
            ASSIGN  
                ODBC-CURSOR = ODBC-CURSOR + 1.
            ObjRecordSet:MoveNext NO-ERROR.
        END.
        LEAVE.
    END.

    RETURN ODBC-RECCOUNT.

END FUNCTION.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-lesenBelegDetail) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION lesenBelegDetail Procedure 
FUNCTION lesenBelegDetail RETURNS INTEGER
    ( /* parameter-definitions */ ) :
    /*------------------------------------------------------------------------------
      Purpose:  
        Notes:  
    ------------------------------------------------------------------------------*/

    EMPTY TEMP-TABLE TBelegDetail.

    ODBC-QUERY = 'SELECT [DocKey],[DocLine] FROM [g.connect].[dbo].[BelegDetail]'.

    ASSIGN  
        ObjCommand:CommandText = ODBC-QUERY
        ObjRecordSet           = ObjCommand:EXECUTE ( OUTPUT ODBC-NULL, "", 32 )
        ODBC-RECCOUNT          = ObjRecordSet:RecordCount NO-ERROR.

    DO WHILE TRUE:
        IF ODBC-RECCOUNT = 0 THEN LEAVE.
        IF ODBC-RECCOUNT = ? THEN LEAVE.

        ODBC-CURSOR = 0.
        ObjRecordSet:MoveFirst NO-ERROR.

        DO WHILE ODBC-CURSOR < ODBC-RECCOUNT:
            CREATE  TBelegDetail.
            ASSIGN  
                TBelegDetail.DocKey  = ObjRecordSet:FIELDS("DocKey") :VALUE
                TBelegDetail.DocLine = ObjRecordSet:FIELDS("DocLine"):VALUE.
            ASSIGN  
                ODBC-CURSOR = ODBC-CURSOR + 1.
            ObjRecordSet:MoveNext NO-ERROR.
        END.
        LEAVE.
    END.

    RETURN ODBC-RECCOUNT.

END FUNCTION.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-lesenOpSaldo) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION lesenOpSaldo Procedure 
FUNCTION lesenOpSaldo RETURNS INTEGER
    ( /* parameter-definitions */ ) :
    /*------------------------------------------------------------------------------
      Purpose:  
        Notes:  
    ------------------------------------------------------------------------------*/

    EMPTY TEMP-TABLE tOpSaldo.
    /*
    FOR EACH xOpSaldo:
        CREATE  tOpSaldo.
        BUFFER-COPY xOpSaldo TO tOpSaldo.
    END.
    */
    ODBC-QUERY = "SELECT * FROM [g.connect].[dbo].[OPListe] WHERE OpId > '300000' ".

    ASSIGN  
        ObjCommand:CommandText = ODBC-QUERY
        ObjRecordSet           = ObjCommand:EXECUTE ( OUTPUT ODBC-NULL, "", 32 )
        ODBC-RECCOUNT          = ObjRecordSet:RecordCount NO-ERROR.

    DO WHILE TRUE:
        IF ODBC-RECCOUNT = 0 THEN LEAVE.
        IF ODBC-RECCOUNT = ? THEN LEAVE.

        ODBC-CURSOR = 0.
        ObjRecordSet:MoveFirst NO-ERROR.

        DO WHILE ODBC-CURSOR < ODBC-RECCOUNT:
            CREATE  tOpSaldo.
            ASSIGN  
                tOpSaldo.Opid     = ObjRecordSet:FIELDS("OpId")    :VALUE
                tOpSaldo.PkNr     = ObjRecordSet:FIELDS("PkNr")    :VALUE
                tOpSaldo.Betrag   = ObjRecordSet:FIELDS("Betrag")  :VALUE
                tOpSaldo.BetragFW = ObjRecordSet:FIELDS("BetragFW"):VALUE.
            ASSIGN  
                ODBC-CURSOR = ODBC-CURSOR + 1.
            ObjRecordSet:MoveNext NO-ERROR.
        END.
        LEAVE.
    END.

    RETURN ODBC-RECCOUNT.

END FUNCTION.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF
&IF DEFINED(EXCLUDE-SendMail) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION SendMail Procedure
FUNCTION SendMail RETURNS LOGICAL 
    (  ):
    /*------------------------------------------------------------------------------
     Purpose:
     Notes:
    ------------------------------------------------------------------------------*/

    DEFINE VARIABLE cBody AS CHARACTER INIT '' NO-UNDO.

    cBody = 'Protokoll der SAGE-Schnittstellenverbuchung'
        + '</br>' + '</br>'
        + SUBSTITUTE('Anzahl OP aus Ge_MIS    --> &1 ', STRING(iAnzOp,'zzz,zz9') )
        + '</br>'
        + SUBSTITUTE('Gesamtbetrag aus Ge_MIS --> &1 ', STRING(nTotBetr,'z,zzz,zz9.99-') )
        + '</br>' + '</br>'.

    REPEAT TRANSACTION ON ERROR UNDO, LEAVE:          
        CREATE  ASMutation.
        ASSIGN  
            ASMutation.asmutation_id = NEXT-VALUE(asmutation_id)
            ASMutation.MutArt        = 'MAIL'
            ASMutation.Aktiv         = TRUE
            ASMutation.cStatus       = ''
            ASMutation.Datum         = TODAY
            ASMutation.Firma         = cMandant
            ASMutation.cFeld_1       = SUBSTITUTE('TO:&1;CC:&2', cMailToSage, 'wr@adprime.ch')
            ASMutation.cFeld_2       = SUBSTITUTE('Ge_MIS - SAGE vom &1 um &2', STRING(TODAY,'99.99.9999'), STRING(TIME,'HH:MM:SS') )
            ASMutation.cFeld_3       = cBody
            ASMutation.cKey_1        = ''
            ASMutation.cKey_2        = ''.
        RELEASE ASMutation.
        LEAVE.
    END.
          
    RUN 'SendMailStart.p'.
    
    RETURN TRUE.
    
END FUNCTION.
    
/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME


&ENDIF

&IF DEFINED(EXCLUDE-startSQLTransaction) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION startSQLTransaction Procedure 
FUNCTION startSQLTransaction RETURNS LOGICAL
    ( /* parameter-definitions */ ) :
    /*------------------------------------------------------------------------------
      Purpose:  
        Notes:  
    ------------------------------------------------------------------------------*/

    ODBC-QUERY = 'BEGIN TRANSACTION'.

    ASSIGN  
        ObjCommand:CommandText = ODBC-QUERY
        ObjRecordSet           = ObjCommand:EXECUTE( OUTPUT ODBC-NULL, "", 32 ).

END FUNCTION.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-updateBeleg) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION updateBeleg Procedure 
FUNCTION updateBeleg RETURNS LOGICAL
    ( ipTHandle AS HANDLE, ipArt AS INTEGER ) :
    /*------------------------------------------------------------------------------
      Purpose:  
        Notes:  
    ------------------------------------------------------------------------------*/

    DEFINE VARIABLE cSet  AS CHARACTER NO-UNDO.
    DEFINE VARIABLE hFeld AS HANDLE    NO-UNDO.
    DEFINE VARIABLE cFeld AS CHARACTER NO-UNDO.

    cSet  = DYNAMIC-FUNCTION('CreateSetDaten':U, INPUT ipTHandle, INPUT ipArt) NO-ERROR.
    hFeld = ipTHandle:BUFFER-FIELD('OpId').
    cFeld = hFeld:BUFFER-VALUE(0).

    IF ipArt = 1 THEN 
    DO:   /*  INSERT  */
        ODBC-QUERY = "INSERT INTO [g.connect].[dbo].[Beleg] "
            + cSet.
    END.
    IF ipArt = 0 THEN 
    DO:   /*  UPDATE  */
        ODBC-QUERY = "UPDATE [g.connect].[dbo].[Beleg] "
            + cSet
            + " WHERE OpId = '"
            + cFeld
            + "' ".
    END.

    ASSIGN  
        ObjCommand:CommandText = ODBC-QUERY
        ObjRecordSet           = ObjCommand:EXECUTE(OUTPUT ODBC-NULL, "", 32)
            NO-ERROR.

    IF ODBC-NULL <> '1' THEN 
    DO:
        OUTPUT TO VALUE(cTempName) NO-MAP NO-CONVERT APPEND.
        PUT CONTROL 'Fehler ' ODBC-NULL ' bei ' ODBC-QUERY CHR(10).
        OUTPUT CLOSE.
        RETURN FALSE.
    END.

    RETURN TRUE.

END FUNCTION.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-updateBelegDetail) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION updateBelegDetail Procedure 
FUNCTION updateBelegDetail RETURNS LOGICAL
    ( ipTHandle AS HANDLE, ipArt AS INTEGER ) :
    /*------------------------------------------------------------------------------
      Purpose:  
        Notes:  
    ------------------------------------------------------------------------------*/

    DEFINE VARIABLE cSet   AS CHARACTER NO-UNDO.
    DEFINE VARIABLE hFeld  AS HANDLE    NO-UNDO.
    DEFINE VARIABLE cFaknr AS CHARACTER NO-UNDO.
    DEFINE VARIABLE cIndex AS CHARACTER NO-UNDO.

    cSet   = DYNAMIC-FUNCTION('CreateSetDaten':U, INPUT ipTHandle, INPUT ipArt) NO-ERROR.
    hFeld  = ipTHandle:BUFFER-FIELD('DocKey').
    cFaknr = hFeld:BUFFER-VALUE(0).
    hFeld  = ipTHandle:BUFFER-FIELD('DocLine').
    cIndex = hFeld:BUFFER-VALUE(0).

    IF ipArt = 1 THEN 
    DO:   /*  INSERT  */
        ODBC-QUERY = "INSERT INTO [g.connect].[dbo].[BelegDetail] "
            + cSet.
    END.
    IF ipArt = 0 THEN 
    DO:   /*  UPDATE  */
        ODBC-QUERY = "UPDATE [g.connect].[dbo].[BelegDetail] "
            + cSet
            + " WHERE DocKey = '"
            + cFaknr
            + "' "
            + "AND  DocLine = '"
            + cIndex
            + "'".
    END.

    ASSIGN  
        ObjCommand:CommandText = ODBC-QUERY
        ObjRecordSet           = ObjCommand:EXECUTE( OUTPUT ODBC-NULL, "", 32 )
            NO-ERROR.

    IF ODBC-NULL <> '1' THEN 
    DO:
        OUTPUT TO VALUE(cTempName) NO-MAP NO-CONVERT APPEND.
        PUT CONTROL 'Fehler ' ODBC-NULL ' bei ' ODBC-QUERY CHR(10).
        OUTPUT CLOSE.
        RETURN FALSE.
    END.

    RETURN TRUE.

END FUNCTION.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF
&IF DEFINED(EXCLUDE-writeLogFile) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION writeLogFile Procedure 
FUNCTION writeLogFile RETURNS LOGICAL
    ( ipMessage AS CHARACTER  ) :
    /*------------------------------------------------------------------------------
      Purpose:  
        Notes:  
    ------------------------------------------------------------------------------*/

    DEFINE VARIABLE cString AS CHARACTER NO-UNDO.

    cString = SUBSTITUTE('&1 &2 -> &3', STRING(TODAY,'99.99.9999'), STRING(TIME,'HH:MM:SS'), ipMessage).
    OUTPUT STREAM LogStream TO VALUE(cTempName) APPEND.
    PUT STREAM LogStream CONTROL cString CHR(10).
    OUTPUT STREAM LogStream CLOSE.

    RETURN TRUE.

END FUNCTION.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF