&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 ************************** */ DEF VAR ObjRecordSet AS COM-HANDLE NO-UNDO. DEF VAR ObjConnection AS COM-HANDLE NO-UNDO. DEF VAR ObjCommand AS COM-HANDLE NO-UNDO. DEF VAR ODBC-DSN AS CHAR NO-UNDO. DEF VAR ODBC-SERVER AS CHAR NO-UNDO. DEF VAR ODBC-USERID AS CHAR NO-UNDO. DEF VAR ODBC-PASSWD AS CHAR NO-UNDO. DEF VAR ODBC-QUERY AS CHAR NO-UNDO. DEF VAR ODBC-STATUS AS CHAR NO-UNDO. DEF VAR ODBC-RECCOUNT AS INT NO-UNDO. DEF VAR ODBC-NULL AS CHAR NO-UNDO. DEF VAR ODBC-CURSOR AS INT NO-UNDO. DEF VAR hBeleg AS HANDLE NO-UNDO. DEF VAR hBelegDetail AS HANDLE NO-UNDO. DEF VAR hOpListe AS HANDLE NO-UNDO. DEF VAR fEnde AS LOG NO-UNDO. DEF VAR TempName AS CHAR NO-UNDO. DEF VAR cJournal AS CHAR NO-UNDO. DEF VAR WebString AS CHAR NO-UNDO. DEF VAR AllesOK AS LOG NO-UNDO. DEF VAR Sprcd AS INT NO-UNDO. DEF VAR LVAdx AS CHAR NO-UNDO EXTENT 15. DEF VAR LVAdy AS CHAR NO-UNDO EXTENT 15. DEF VAR cOrt AS CHAR NO-UNDO. DEF VAR Firma AS CHAR NO-UNDO INIT '9999'. DEF VAR GVFirma AS CHAR NO-UNDO INIT '9999'. DEF VAR ja AS LOG NO-UNDO. DEF VAR CrLf AS CHAR NO-UNDO. DEF STREAM Logfile. DEF TEMP-TABLE tBeleg FIELD DocKey AS INT FIELD DocType AS INT FIELD OpID AS CHAR FIELD KndNr AS INT FIELD Konto AS INT FIELD opText AS CHAR FIELD opDate AS DATETIME FIELD ZabInd AS INT FIELD SortId AS CHAR FIELD Firma AS CHAR FIELD Zusatz1 AS CHAR FIELD Strasse AS CHAR FIELD Plz AS CHAR FIELD Ort AS CHAR FIELD Telefon1 AS CHAR FIELD Telefon4 AS CHAR FIELD EsrNr AS CHAR FIELD Sprache AS CHAR FIELD Transfer AS DATETIME FIELD FibuDatum AS DATETIME FIELD OPfaellig AS DATETIME FIELD OpCode AS CHAR FIELD BelegNr AS INT FIELD Zusatz2 AS CHAR FIELD Sammelkonto AS CHAR FIELD Land AS CHAR INDEX Faknr IS PRIMARY OpID INDEX DocKey DocKey. DEF TEMP-TABLE tBelegDetail FIELD DocLKey AS CHAR FIELD DocKey AS INT FIELD DocLine AS INT FIELD Konto AS CHAR FIELD cText AS CHAR FIELD MwstTyp AS CHAR FIELD Betrag AS DEC FIELD BetragMwst AS DEC FIELD BetragFW AS DEC FIELD BuchTyp AS CHAR FIELD Kostenstelle AS CHAR FIELD cCode AS CHAR INDEX DocKey IS PRIMARY DocKey Konto. DEF TEMP-TABLE tOpListe FIELD OpId AS CHAR FIELD PkNr AS INT FIELD Betrag AS DEC FIELD RechBetrag AS DEC FIELD Datum AS DATETIME INDEX OpID IS PRIMARY OpID. DEF TEMP-TABLE tDebop LIKE Debop FIELD Ansatz_8 AS DEC FIELD Ansatz_25 AS DEC FIELD Ansatz_0 AS DEC FIELD SageAns_8 AS DEC FIELD SageAns_25 AS DEC FIELD SageAns_0 AS DEC FIELD DetailBetr AS DEC FIELD SageSaldo AS DEC FIELD cTransfer AS CHAR FIELD lOpListe AS LOG . DEF TEMP-TABLE txBelegDetail LIKE tBelegDetail . DEF TEMP-TABLE tMwstBetr FIELD Konto AS CHAR FIELD MwstCd AS INT FIELD MwstPfl AS DEC DECIMALS 2 FIELD MwstBet AS DEC DECIMALS 2 FIELD MwstAns AS DEC DECIMALS 2 . DEF TEMP-TABLE tSollIst FIELD Knr AS INT FIELD Faknr AS INT FIELD Fakdat AS DATE FIELD Fakbetr AS Dec FIELD Konto AS CHAR FIELD MwstAns AS CHAR FIELD Soll AS DEC FIELD Ist AS DEC . /* _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-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 INT ) FORWARD. /* _UIB-CODE-BLOCK-END */ &ANALYZE-RESUME &ENDIF &IF DEFINED(EXCLUDE-deleteBelege) = 0 &THEN &ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD deleteBelege Procedure FUNCTION deleteBelege RETURNS INTEGER ( /* parameter-definitions */ ) FORWARD. /* _UIB-CODE-BLOCK-END */ &ANALYZE-RESUME &ENDIF &IF DEFINED(EXCLUDE-getDocKey) = 0 &THEN &ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD getDocKey Procedure FUNCTION getDocKey RETURNS INTEGER ( ipDocKey AS INT ) FORWARD. /* _UIB-CODE-BLOCK-END */ &ANALYZE-RESUME &ENDIF &IF DEFINED(EXCLUDE-getODBCDaten) = 0 &THEN &ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD getODBCDaten Procedure FUNCTION getODBCDaten RETURNS LOGICAL ( iphBuffer AS HANDLE ) 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-lesenOpListe) = 0 &THEN &ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD lesenOpListe Procedure FUNCTION lesenOpListe RETURNS INTEGER ( /* parameter-definitions */ ) FORWARD. /* _UIB-CODE-BLOCK-END */ &ANALYZE-RESUME &ENDIF &IF DEFINED(EXCLUDE-readDebop) = 0 &THEN &ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD readDebop Procedure FUNCTION readDebop RETURNS INTEGER ( /* parameter-definitions */ ) FORWARD. /* _UIB-CODE-BLOCK-END */ &ANALYZE-RESUME &ENDIF &IF DEFINED(EXCLUDE-reorgBelege) = 0 &THEN &ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD reorgBelege Procedure FUNCTION reorgBelege RETURNS LOGICAL ( /* parameter-definitions */ ) 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 INT ) 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 INT ) FORWARD. /* _UIB-CODE-BLOCK-END */ &ANALYZE-RESUME &ENDIF &IF DEFINED(EXCLUDE-writeLogFiles) = 0 &THEN &ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD writeLogFiles Procedure FUNCTION writeLogFiles RETURNS LOGICAL ( ipMessage AS CHAR ) 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 = 77.8. /* END WINDOW DEFINITION */ */ &ANALYZE-RESUME &ANALYZE-SUSPEND _UIB-CODE-BLOCK _CUSTOM _MAIN-BLOCK Procedure /* *************************** Main Block *************************** */ TempName = SUBSTITUTE('C:\Temp\SESAMSQL_&1_&2.log', STRING(TODAY,'99-99-9999'), REPLACE(STRING(TIME,'HH:MM:SS'), ':', '-')). cJournal = SUBSTITUTE('C:\Temp\SESAMSQL_&1_&2.csv', STRING(TODAY,'99-99-9999'), REPLACE(STRING(TIME,'HH:MM:SS'), ':', '-')). ja = DYNAMIC-FUNCTION('CreateConnection':U) NO-ERROR. IF NOT ja THEN QUIT. DYNAMIC-FUNCTION('writeLogFiles':U, 'Connected'). DYNAMIC-FUNCTION('deleteBelege':U). /* DYNAMIC-FUNCTION('readDebop':U). DYNAMIC-FUNCTION('lesenBeleg':U). DYNAMIC-FUNCTION('lesenBelegDetail':U). DYNAMIC-FUNCTION('lesenOpListe':U). OUTPUT TO 'C:\Temp\OffenePosten_Ge_MIS.csv' NO-MAP NO-CONVERT. FOR EACH tDebop: EXPORT DELIMITER ';' tDebop.Knr tDebop.Faknr tDebop.FakDat FORMAT "99.99.9999" tDebop.Fakbetr tDebop.Saldo tDebop.DetailBetr tDebop.SageSaldo tDebop.cTransfer tDebop.lOpListe tDebop.Ansatz_8 tDebop.Ansatz_25 tDebop.Ansatz_0 tDebop.SageAns_8 tDebop.SageAns_25 tDebop.SageAns_0 . END. OUTPUT CLOSE. DEF VAR dDatum AS DATE NO-UNDO. DEF VAR nSoll AS DEC NO-UNDO. DEF VAR nIst AS DEC NO-UNDO. DEF VAR nSaldo AS DEC NO-UNDO. EMPTY TEMP-TABLE tSollIst. FOR EACH tDebop: FIND FIRST tBeleg NO-LOCK WHERE tBeleg.OpId = TRIM(STRING(tDebop.Faknr,'>>>>>>>')). dDatum = DATE(tBeleg.Transfer) NO-ERROR. IF dDatum < 09/28/2017 THEN NEXT. FOR EACH tBelegDetail NO-LOCK WHERE tBelegDetail.DocKey = tBeleg.DocKey: CREATE tSollIst. ASSIGN tSollIst.Knr = tDebop.Knr tSollIst.Faknr = tDebop.Faknr tSollIst.Fakdat = tDebop.Fakdat tSollIst.Fakbetr = tDebop.Fakbetr tSollIst.Konto = tBelegDetail.Konto tSollIst.MwstAns = tBelegDetail.MwstTyp tSollIst.Ist = tBelegDetail.Betrag. END. FOR EACH txBelegDetail NO-LOCK WHERE txBelegDetail.DocKey = tBeleg.DocKey: FIND FIRST tSollIst WHERE tSollIst.Faknr = tDebop.Faknr AND tSollIst.Konto = txBelegDetail.Konto AND tSollIst.MwstAns = txBelegDetail.MwstTyp NO-ERROR. IF NOT AVAILABLE tSollIst THEN DO: CREATE tSollIst. ASSIGN tSollIst.Knr = tDebop.Knr tSollIst.Faknr = tDebop.Faknr tSollIst.Fakdat = tDebop.Fakdat tSollIst.Fakbetr = tDebop.Fakbetr tSollIst.Konto = txBelegDetail.Konto tSollIst.MwstAns = txBelegDetail.MwstTyp. END. tSollIst.Soll = txBelegDetail.Betrag. END. END. OUTPUT TO 'C:\Temp\Schnittstellen_Detail.csv'. FOR EACH tSollIst BREAK BY tSollIst.Knr BY tSollIst.Faknr BY tSollIst.Konto BY tSollIst.MwstAns: IF FIRST-OF ( tSollIst.Faknr ) THEN DO: FIND FIRST tDebop NO-LOCK WHERE tDebop.Firma = '1000' AND tDebop.Knr = tSollIst.Knr AND tDebop.Faknr = tSollIst.Faknr. ASSIGN nSoll = 0 nIst = 0 nSaldo = tDebop.Saldo. END. ASSIGN nSoll = nSoll + tSollIst.Soll nIst = nIst + tSollIst.Ist. IF FIRST-OF (tSollIst.Faknr) THEN EXPORT DELIMITER ';' tSollIst.Knr tSollIst.Faknr tSollIst.Fakdat tSollIst.Fakbetr tSollIst.Konto tSollIst.MwstAns tSollIst.Soll tSollIst.Ist (tSollIst.Soll - tSollIst.Ist). ELSE EXPORT DELIMITER ';' '' '' '' '' tSollIst.Konto tSollIst.MwstAns tSollIst.Soll tSollIst.Ist (tSollIst.Soll - tSollIst.Ist). IF NOT LAST-OF ( tSollIst.Faknr ) THEN NEXT. EXPORT DELIMITER ';' '' '' '' '' '' '' nSoll nIst '' (nSoll - nIst) (tDebop.Fakbetr - nSaldo). PUT CONTROL CHR(10). END. OUTPUT CLOSE. /* RUN FUELLEN_TBELEG. RUN LESEN_OFFENESALDI. */ */ DYNAMIC-FUNCTION('CloseConnection':U) NO-ERROR. /* QUIT. */ /* _UIB-CODE-BLOCK-END */ &ANALYZE-RESUME /* ********************** Internal Procedures *********************** */ &IF DEFINED(EXCLUDE-FUELLEN_TBELEG) = 0 &THEN &ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE FUELLEN_TBELEG Procedure PROCEDURE FUELLEN_TBELEG : /*------------------------------------------------------------------------------ Purpose: Parameters: Notes: ------------------------------------------------------------------------------*/ DEF VAR xx AS INT NO-UNDO. DEF VAR dDatum AS DATE NO-UNDO. xx = DYNAMIC-FUNCTION('lesenBeleg':U) NO-ERROR. message xx ' Records gelesen ' VIEW-AS ALERT-BOX. OUTPUT TO 'C:\TEMP\Dump_Beleg.csv' NO-MAP NO-CONVERT. FOR EACH tBeleg NO-LOCK: EXPORT DELIMITER ';' tBeleg. END. OUTPUT CLOSE. /* FOR EACH tBeleg: */ /* dDatum = DATE(tBeleg.Transfer) NO-ERROR. */ /* IF ERROR-STATUS:ERROR OR */ /* dDatum = ? THEN DELETE tBeleg. */ /* END. */ /* FOR EACH tBeleg */ /* BREAK BY tBeleg.OpID: */ /* IF FIRST-OF ( tBeleg.OpID ) THEN NEXT. */ /* END. */ /* */ /* FOR EACH tBeleg: */ /* dDatum = DATE(tBeleg.Transfer). */ /* IF dDatum >= 09/29/2017 THEN NEXT. */ /* DELETE tBeleg. */ /* END. */ OUTPUT TO VALUE(cJournal) NO-MAP NO-CONVERT APPEND. PUT CONTROL STRING(TODAY,'99.99.9999') ' ' STRING(TIME,'HH:MM:SS') ' Anzahl eingelesene Beleg-Details aus SESAM -> ' xx CHR(10). 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: ------------------------------------------------------------------------------*/ DEF VAR Saldo AS DEC NO-UNDO. DEF VAR Diff AS DEC NO-UNDO. DEF VAR dDatum AS DATE NO-UNDO. EMPTY TEMP-TABLE tDebop. FOR EACH Debop NO-LOCK WHERE Debop.Firma = '1000': FIND FIRST tBeleg NO-LOCK WHERE tBeleg.OpId = TRIM(STRING(Debop.Faknr,'>>>>>>>')) NO-ERROR. IF NOT AVAILABLE tBeleg THEN NEXT. CREATE tDebop. BUFFER-COPY Debop TO tDebop. END. DYNAMIC-FUNCTION('lesenOpSaldo':U) NO-ERROR. FOR EACH Debst NO-LOCK: FOR EACH tDebop USE-INDEX Debop-k1 WHERE tDebop.Firma = Debst.Firma AND tDebop.Knr = Debst.Knr: FIND FIRST tOpListe NO-LOCK USE-INDEX OpId WHERE tOpListe.OpId = TRIM(STRING(tDebop.Faknr,'>>>>>>>>')) AND tOpListe.PkNr = Debst.Knr NO-ERROR. IF AVAILABLE tOpListe THEN Diff = tDebop.Saldo - tOpListe.Betrag. ELSE Diff = (IF tDebop.Fakdat < (TODAY - 5) THEN tDebop.Saldo ELSE 0). IF Diff <> 0 THEN DO: tDebop.ZahBetr = tDebop.ZahBetr + Diff. tDebop.Saldo = tDebop.Fakbetr - tDebop.Zahbetr - tDebop.Skonto. tDebop.Zahdat = TODAY. END. END. END. OUTPUT TO 'C:\TEMP\tDebop_OPSaldo.csv' NO-MAP NO-CONVERT. FOR EACH tDebop NO-LOCK: FIND Adresse NO-LOCK WHERE Adresse.Firma = tDebop.Firma AND Adresse.Knr = tDebop.Knr. EXPORT DELIMITER ';' tDebop.Knr Adresse.Anzeig_Br tDebop.Faknr tDebop.FakDat tDebop.Fakbetr tDebop.ZahBetr tDebop.SageSaldo. END. OUTPUT CLOSE. FOR EACH tDebop: FIND FIRST tBeleg NO-LOCK WHERE tBeleg.OpID = TRIM(STRING(tDebop.Faknr,'>>>>>>>')) NO-ERROR. IF AVAILABLE tBeleg THEN NEXT. DELETE tDebop. END. OUTPUT TO 'C:\TEMP\tDebop_Schnittstelle.csv' NO-MAP NO-CONVERT. FOR EACH tDebop NO-LOCK: FIND Adresse NO-LOCK WHERE Adresse.Firma = tDebop.Firma AND Adresse.Knr = tDebop.Knr. EXPORT DELIMITER ';' tDebop.Knr Adresse.Anzeig_Br tDebop.Faknr tDebop.FakDat tDebop.Fakbetr tDebop.ZahBetr tDebop.Saldo. END. OUTPUT CLOSE. 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: ------------------------------------------------------------------------------*/ DEF INPUT PARAMETER Rundcode AS INT NO-UNDO. DEF INPUT-OUTPUT PARAMETER Rundbetr AS DEC DECIMALS 4 NO-UNDO. DEF VAR VBetr AS DEC DECIMALS 4 NO-UNDO. DEF VAR VOp AS DEC INIT 0.2 NO-UNDO. DEF VAR XPChar AS CHAR 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-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: ------------------------------------------------------------------------------*/ DEF VAR cConnCommand AS CHAR NO-UNDO. DEF VAR cDSN AS CHAR NO-UNDO. DEF VAR cServer AS CHAR NO-UNDO. DEF VAR ix AS INT NO-UNDO. DEF VAR ii AS INT NO-UNDO. DEF VAR cMessage AS CHAR 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. ASSIGN ODBC-STATUS = '' ODBC-NULL = '' ODBC-DSN = cDSN ODBC-SERVER = cServer ODBC-USERID = 'gebtools' ODBC-PASSWD = 'gebtools' 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 INT ) : /*------------------------------------------------------------------------------ Purpose: Notes: ------------------------------------------------------------------------------*/ DEF VAR cSet AS CHAR NO-UNDO. DEF VAR ii AS INT NO-UNDO. DEF VAR hFeld AS HANDLE NO-UNDO. DEF VAR cFeld AS CHAR NO-UNDO. DEF VAR cInhalt AS CHAR NO-UNDO. DEF VAR cFelder AS CHAR NO-UNDO. DEF VAR cInhalte AS CHAR NO-UNDO. DEF VAR xFeld AS CHAR NO-UNDO. DEF VAR xInhalt AS CHAR 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-deleteBelege) = 0 &THEN &ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION deleteBelege Procedure FUNCTION deleteBelege RETURNS INTEGER ( /* parameter-definitions */ ) : /*------------------------------------------------------------------------------ Purpose: Notes: ------------------------------------------------------------------------------*/ DEF VAR dDatum AS DATE NO-UNDO. DEF VAR ii AS INT NO-UNDO. DEF VAR iDocKey AS INT NO-UNDO. EMPTY TEMP-TABLE tBeleg. hBeleg = TEMP-TABLE tBeleg:DEFAULT-BUFFER-HANDLE. ODBC-QUERY = "SELECT * FROM Huber.dbo.Beleg WHERE OpID = '557768'". DYNAMIC-FUNCTION('writeLogFiles':U, ODBC-QUERY). ASSIGN ObjCommand:CommandText = ODBC-QUERY ObjRecordSet = ObjCommand:EXECUTE ( OUTPUT ODBC-NULL, "", 32 ) ODBC-RECCOUNT = ObjRecordSet:RecordCount . MESSAGE ODBC-RECCOUNT VIEW-AS ALERT-BOX INFORMATION BUTTONS OK. 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. DYNAMIC-FUNCTION('getODBCDaten':U, hBeleg ). ASSIGN ODBC-CURSOR = ODBC-CURSOR + 1. ObjRecordSet:MoveNext NO-ERROR. END. LEAVE. END. FOR EACH tBeleg: DISPLAY tbeleg. ii = ii + 1. END. /* ii = 0. */ /* FOR EACH tBeleg NO-LOCK: */ /* ODBC-QUERY = 'DELETE FROM [Huber].[dbo].[BelegDetail] ' */ /* + 'WHERE DocKey = ' + STRING(tBeleg.DocKey) + ' '. */ /* /* MESSAGE ODBC-QUERY VIEW-AS ALERT-BOX. */ */ /* /* */ /* ASSIGN ObjCommand:CommandText = ODBC-QUERY */ /* ObjRecordSet = ObjCommand:EXECUTE ( OUTPUT ODBC-NULL, "", 32 ) */ /* ODBC-RECCOUNT = ObjRecordSet:RecordCount NO-ERROR. */ /* */ /* MESSAGE 'Anzahl gelöschte BelegDetail = ' ODBC-RECCOUNT VIEW-AS ALERT-BOX. */ /* */ */ /* ODBC-QUERY = 'DELETE FROM [Huber].[dbo].[Beleg] ' */ /* + 'WHERE OpId = "' + tBeleg.OpId + '" AND DocKey = ' + STRING(tBeleg.DocKey) + ' '. */ /* MESSAGE ODBC-QUERY VIEW-AS ALERT-BOX. */ /* /* */ /* ASSIGN ObjCommand:CommandText = ODBC-QUERY */ /* ObjRecordSet = ObjCommand:EXECUTE ( OUTPUT ODBC-NULL, "", 32 ) */ /* ODBC-RECCOUNT = ObjRecordSet:RecordCount NO-ERROR. */ /* */ /* /* MESSAGE 'Anzahl gelöschte Belege = ' ODBC-RECCOUNT VIEW-AS ALERT-BOX. */ */ /* */ */ /* ii = ii + 1. */ /* END. */ /* message 'Anzahl gelöschte Belege = ' ii view-as alert-box. */ RETURN ii. END FUNCTION. /* _UIB-CODE-BLOCK-END */ &ANALYZE-RESUME &ENDIF &IF DEFINED(EXCLUDE-getDocKey) = 0 &THEN &ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION getDocKey Procedure FUNCTION getDocKey RETURNS INTEGER ( ipDocKey AS INT ) : /*------------------------------------------------------------------------------ Purpose: Notes: ------------------------------------------------------------------------------*/ DEF VAR iDocKey AS INT NO-UNDO. iDocKey = ipDocKey. ODBC-QUERY = "SELECT [DocKey] FROM [Huber].[dbo].[Beleg] " + "WHERE OpId = '" + STRING(ipDocKey,'999999') + "' ". 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. ObjRecordSet:MoveFirst NO-ERROR. iDocKey = ObjRecordSet:FIELDS("DocKey"):VALUE. LEAVE. END. RETURN iDocKey. END FUNCTION. /* _UIB-CODE-BLOCK-END */ &ANALYZE-RESUME &ENDIF &IF DEFINED(EXCLUDE-getODBCDaten) = 0 &THEN &ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION getODBCDaten Procedure FUNCTION getODBCDaten RETURNS LOGICAL ( iphBuffer AS HANDLE ) : /*------------------------------------------------------------------------------ Purpose: Notes: ------------------------------------------------------------------------------*/ DEF VAR ii AS INT NO-UNDO. DEF VAR hFeld AS HANDLE NO-UNDO. DEF VAR cFeld AS CHAR NO-UNDO. DO ii = 1 TO iphBuffer:NUM-FIELDS: hFeld = iphBuffer:BUFFER-FIELD(ii). cFeld = hFeld:NAME. IF cFeld = 'cText' THEN cFeld = 'Text'. IF cFeld = 'cCode' THEN cFeld = 'Code'. hFeld:BUFFER-VALUE(0) = ObjRecordSet:FIELDS(cFeld):VALUE NO-ERROR. END. 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: ------------------------------------------------------------------------------*/ DEF VAR dDatum AS DATE NO-UNDO. DEF VAR dFakBetr AS DEC DECIMALS 2 NO-UNDO. DEF VAR BuchBetr AS DEC DECIMALS 2 NO-UNDO. DEF VAR Ansatz AS DEC NO-UNDO. DEF VAR Inkl AS LOG NO-UNDO. DEF VAR ii AS INT NO-UNDO. DEF VAR cString AS CHAR NO-UNDO. DEF VAR iDocKey AS INT NO-UNDO. DEF VAR dMwst AS DEC NO-UNDO. DEF VAR lMwstAdd AS LOG NO-UNDO. DEF VAR nDiff AS DEC DECIMALS 2 NO-UNDO. EMPTY TEMP-TABLE txBelegDetail. EMPTY TEMP-TABLE tBeleg. hBeleg = TEMP-TABLE tBeleg:DEFAULT-BUFFER-HANDLE. ODBC-QUERY = 'SELECT * FROM [Huber].[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. DYNAMIC-FUNCTION('getODBCDaten':U, hBeleg ). ASSIGN ODBC-CURSOR = ODBC-CURSOR + 1. ObjRecordSet:MoveNext NO-ERROR. END. LEAVE. END. FOR EACH tDebop: FIND FIRST tBeleg WHERE tBeleg.OpId = TRIM(STRING(tDebop.Faknr,'>>>>>>>')) NO-ERROR. IF NOT AVAILABLE tBeleg THEN DO: DELETE tDebop. NEXT. END. dDatum = DATE(tBeleg.Transfer) NO-ERROR. IF ERROR-STATUS:ERROR OR dDatum = ? THEN DO: DELETE tBeleg. DELETE tDebop. END. END. FIND Steuer NO-LOCK WHERE Steuer.Firma = '1000' NO-ERROR. FOR EACH tDebop NO-LOCK: FIND FIRST tBeleg WHERE tBeleg.OpId = TRIM(STRING(tDebop.Faknr,'>>>>>>>')) NO-ERROR. dDatum = DATE(tBeleg.Transfer) NO-ERROR. IF dDatum <= 09/28/2017 THEN NEXT. dFakBetr = tDebop.FakBetr. EMPTY TEMP-TABLE tMwstBetr. FOR EACH Interf NO-LOCK WHERE Interf.Firma = Steuer.Fwc09 AND Interf.TrNr1 = tDebop.TrNr1 AND Interf.TrNr2 = tDebop.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.Konto = Interf.Kto2 AND tMwstbetr.MwstCd = Interf.Mwst_Cd NO-ERROR. IF NOT AVAILABLE tMwstBetr THEN DO: CREATE tMwstBetr. ASSIGN tMwstBetr.Konto = Interf.Kto2 tMwstBetr.MwstCd = Interf.Mwst_Cd tMwstBetr.MwstAns = tDebop.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(tDebop.FakWpfl[ii] - BuchBetr) < ABS(tDebop.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.Konto BY tMwstBetr.MwstCd: ii = ii + 1. CREATE txBelegDetail. ASSIGN txBelegDetail.DocKey = tBeleg.DocKey txBelegDetail.DocLine = ii txBelegDetail.Konto = tMwstBetr.Konto txBelegDetail.MwstTyp = STRING(tMwstBetr.MwstCd,'99') txBelegDetail.Betrag = tMwstBetr.MwstPfl txBelegDetail.BetragMwst = 0 txBelegDetail.BetragFW = 0 txBelegDetail.BuchTyp = '' txBelegDetail.Kostenstelle = '' txBelegDetail.cCode = ''. END. END. 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. hBelegDetail = TEMP-TABLE tBelegDetail:DEFAULT-BUFFER-HANDLE. ODBC-QUERY = 'SELECT * FROM [Huber].[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. DYNAMIC-FUNCTION('getODBCDaten':U, hBelegDetail ). ASSIGN ODBC-CURSOR = ODBC-CURSOR + 1. ObjRecordSet:MoveNext NO-ERROR. END. LEAVE. END. FOR EACH tBelegDetail: FIND FIRST tBeleg NO-LOCK USE-INDEX docKey WHERE tBeleg.DocKey = tBelegDetail.DocKey NO-ERROR. IF AVAILABLE tBeleg THEN NEXT. DELETE tBelegDetail. END. RETURN ODBC-RECCOUNT. END. /* _UIB-CODE-BLOCK-END */ &ANALYZE-RESUME &ENDIF &IF DEFINED(EXCLUDE-lesenOpListe) = 0 &THEN &ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION lesenOpListe Procedure FUNCTION lesenOpListe RETURNS INTEGER ( /* parameter-definitions */ ) : /*------------------------------------------------------------------------------ Purpose: Notes: ------------------------------------------------------------------------------*/ DEF VAR dDatum AS DATE NO-UNDO. EMPTY TEMP-TABLE tOpListe. hOpListe = TEMP-TABLE tOpListe:DEFAULT-BUFFER-HANDLE. ODBC-QUERY = "SELECT * FROM [Huber].[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 tOpListe. DYNAMIC-FUNCTION('getODBCDaten':U, hOpListe ). ASSIGN ODBC-CURSOR = ODBC-CURSOR + 1. ObjRecordSet:MoveNext NO-ERROR. END. LEAVE. END. FOR EACH tDebop: FIND FIRST tOpListe NO-LOCK WHERE tOpListe.OpID = TRIM(STRING(tDebop.Faknr,'>>>>>>>')) NO-ERROR. IF AVAILABLE tOpListe THEN DO: tDebop.SageSaldo = tOpListe.Betrag. tDebop.lOpListe = TRUE. END. FIND FIRST tBeleg NO-LOCK WHERE tBeleg.OpID = TRIM(STRING(tDebop.Faknr,'>>>>>>>')) NO-ERROR. IF AVAILABLE tBeleg THEN DO: dDatum = DATE(tBeleg.Transfer) NO-ERROR. IF ERROR-STATUS:ERROR OR dDatum = ? THEN tDebop.cTransfer = ''. ELSE tDebop.cTransfer = STRING(DATE(tBeleg.Transfer),'99.99.9999'). FOR EACH tBelegDetail NO-LOCK WHERE tBelegDetail.DocKey = tBeleg.DocKey: tDebop.DetailBetr = tDebop.DetailBetr + tBelegDetail.Betrag. CASE tBelegDetail.MwstTyp: WHEN '01' OR WHEN '02' THEN DO: tDebop.SageAns_8 = tDebop.SageAns_8 + tBelegDetail.Betrag. END. WHEN '03' OR WHEN '04' THEN DO: tDebop.SageAns_25 = tDebop.SageAns_25 + tBelegDetail.Betrag. END. WHEN '11' THEN DO: tDebop.SageAns_0 = tDebop.SageAns_0 + tBelegDetail.Betrag. END. END. END. END. IF NOT tDebop.lOpListe AND tDebop.cTransfer <> '' THEN tDebop.SageSaldo = tDebop.DetailBetr. END. RETURN ODBC-RECCOUNT. END FUNCTION. /* _UIB-CODE-BLOCK-END */ &ANALYZE-RESUME &ENDIF &IF DEFINED(EXCLUDE-readDebop) = 0 &THEN &ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION readDebop Procedure FUNCTION readDebop RETURNS INTEGER ( /* parameter-definitions */ ) : /*------------------------------------------------------------------------------ Purpose: Notes: ------------------------------------------------------------------------------*/ DEF VAR ii AS INT NO-UNDO. EMPTY TEMP-TABLE tDebop. FOR EACH Debop NO-LOCK WHERE Debop.Firma = '1000' /* AND Debop.Saldo <> 0 */ : CREATE tDebop. BUFFER-COPY Debop TO tDebop ASSIGN tDebop.lOpListe = FALSE tDebop.Ansatz_8 = tDebop.FakWpfl[01] + tDebop.FakWust[01] + tDebop.FakWpfl[02] + tDebop.FakWust[02] tDebop.Ansatz_25 = tDebop.FakWpfl[03] + tDebop.FakWust[03] + tDebop.FakWpfl[04] + tDebop.FakWust[04] tDebop.Ansatz_0 = tDebop.FakWpfl[11]. . ii = ii + 1. END. RETURN ii. END FUNCTION. /* _UIB-CODE-BLOCK-END */ &ANALYZE-RESUME &ENDIF &IF DEFINED(EXCLUDE-reorgBelege) = 0 &THEN &ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION reorgBelege Procedure FUNCTION reorgBelege RETURNS LOGICAL ( /* parameter-definitions */ ) : /*------------------------------------------------------------------------------ Purpose: Notes: ------------------------------------------------------------------------------*/ DEF VAR dDatum AS DATE NO-UNDO. FOR EACH tBeleg: dDatum = DATE(tBeleg.Transfer) NO-ERROR. IF NOT ERROR-STATUS:ERROR AND dDatum <> ? THEN NEXT. ODBC-QUERY = "DELETE FROM [Huber].[dbo].[BelegDetail] " + "WHERE DocKey = &1 ". ODBC-QUERY = SUBSTITUTE(ODBC-QUERY, tBeleg.DocKey). ASSIGN ObjCommand:CommandText = ODBC-QUERY ObjRecordSet = ObjCommand:EXECUTE( OUTPUT ODBC-NULL, "", 32 ) NO-ERROR. END. RETURN FALSE. /* Function return value. */ 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 INT ) : /*------------------------------------------------------------------------------ Purpose: Notes: ------------------------------------------------------------------------------*/ DEF VAR cSet AS CHAR NO-UNDO. DEF VAR hFeld AS HANDLE NO-UNDO. DEF VAR cFeld AS CHAR 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 [Huber].[dbo].[Beleg] " + cSet. END. IF ipArt = 0 THEN DO: /* UPDATE */ ODBC-QUERY = "UPDATE [Huber].[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(TempName) 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 INT ) : /*------------------------------------------------------------------------------ Purpose: Notes: ------------------------------------------------------------------------------*/ DEF VAR cSet AS CHAR NO-UNDO. DEF VAR hFeld AS HANDLE NO-UNDO. DEF VAR cFaknr AS CHAR NO-UNDO. DEF VAR cIndex AS CHAR 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 [Huber].[dbo].[BelegDetail] " + cSet. END. IF ipArt = 0 THEN DO: /* UPDATE */ ODBC-QUERY = "UPDATE [Huber].[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(TempName) 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-writeLogFiles) = 0 &THEN &ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION writeLogFiles Procedure FUNCTION writeLogFiles RETURNS LOGICAL ( ipMessage AS CHAR ) : /*------------------------------------------------------------------------------ Purpose: Notes: ------------------------------------------------------------------------------*/ DEF VAR cString AS CHAR NO-UNDO. cString = SUBSTITUTE('&1 &2 -> &3', STRING(TODAY,'99.99.9999'), STRING(TIME,'HH:MM:SS'), ipMessage). OUTPUT STREAM LogFile TO VALUE(TempName) APPEND. PUT STREAM LogFile CONTROL cString CHR(10). OUTPUT STREAM LogFile CLOSE. RETURN TRUE. END FUNCTION. /* _UIB-CODE-BLOCK-END */ &ANALYZE-RESUME &ENDIF