&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: */ /* 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: 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: 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: 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' + '
' + '
' + SUBSTITUTE('Anzahl OP aus Ge_MIS --> &1 ', STRING(iAnzOp,'zzz,zz9') ) + '
' + SUBSTITUTE('Gesamtbetrag aus Ge_MIS --> &1 ', STRING(nTotBetr,'z,zzz,zz9.99-') ) + '
' + '
'. 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