Public Class cDVO_Interface

    Shared SQL As New VERAG_PROG_ALLGEMEIN.SQL

    Public Shared Function genStammdatenBuRef() As Boolean
        Try
            'RTRIM([Name 1] + ' ' + isnull([Name 2],'')) as [Name]
            Dim sqlStr = " select   adressennr as [Konto-Nr],isnull([Name 1],'') as Name,isnull([Name 2],'') as [Name2],(isnull(UstIdKz,'') + isnull(UstIdNr,'')) as [UST-Id-Nummer],isnull(UstIdGeprüft,'') as [UID-Datum],[Strasse],Adressen.landkz as [St],PLZ as [Postltz],LTRIM([Ort]) as Ort,isnull(BLZ,'') as [Bankltz],isnull(KTO,'') as [Bankkonto-Nr], CASE WHEN CAST(Zahlungsziel as VARCHAR(3)) ='0' THEN '' ELSE CAST(Zahlungsziel as VARCHAR(3)) END   as [ZZiel],/*'' as [SPz],'' as [STg],*/isnull([E-Mail],'') as [mail],'' AS Info1,'' AS Inf2 "
            sqlStr &= "  FROM Adressen INNER JOIN Kunden ON Kundennr=AdressenNr "
            sqlStr &= " LEFT JOIN tblKundenErweitert ON KundenNr=kde_KundenNr "
            sqlStr &= " LEFT JOIN [Länderverzeichnis für die Außenhandelsstatistik] as St ON St.LandKz=Adressen.LandKz "
            sqlStr &= " LEFT JOIN Währungstabelle ON St.LandNr = Währungstabelle.Währungsschlüssel "
            sqlStr &= " WHERE FiBuSchnittstelle=1 AND Auswahl='A' AND FilialenNr='5501' "
            sqlStr &= " Order by [Konto-Nr] "

            '  "Konto-Nr;Name;UST-Id-Nummer;Strasse;St;Postltz;Ort;Bankltz;Bankkonto-Nr;ZZiel;SPz;STg"

            Dim dt As DataTable = SQL.loadDgvBySql(sqlStr, "FMZOLL")
            If dt IsNot Nothing AndAlso dt.Rows.Count > 0 Then
                initKDNr(dt)
                Dim ExcelPath = cProgramFunctions.genExcelFromDT_NEW(dt, , False,,, False) '{"C1:C" & (dt.Rows.Count + 1), "C1:C" & (dt.Rows.Count + 1)})
                If ExcelPath <> "" Then
                    Process.Start(ExcelPath)
                    Return True
                End If
            Else
                Return True ' wenn keine da sind, auch ok...
            End If

            ' UPDATE FiBuSchnittstelle --> 0

        Catch ex As Exception
            MsgBox(ex.Message & ex.StackTrace)
        End Try
        Return False
    End Function

    'BetreibsstammSatz
    '10 Personenkonten
    '100 Beginn Buchungsblock
    '110 Buchungen
    '111 Summe/Ende Buchungsblock 


    Public Shared Function genBetreibsstammSatz(Geschaeftsjahr As Integer) As String
        Return "1,0910,""" & Geschaeftsjahr & """,""0101" & Geschaeftsjahr & """,4,6,""EUR"""
    End Function


    Public Shared Function genBuchungenBeginn(Geschaeftsjahr As Integer) As String
        Return "100,""AR "",4,""" & Now.ToString("ddMMyyyy") & """," & Now.AddDays(-1).Month & ",0"
    End Function


    Public Shared Function genBuchungenEnd(SummeBuchungen As Double) As String
        Return "111," & SummeBuchungen.ToString("N2").Replace(",", ".")
    End Function

    Public Shared Function genStammdatenANSI(Optional OpenFile = False, Optional boolKunden = True, Optional boolBuchungen = True) As Boolean
        Try
            'RTRIM([Name 1] + ' ' + isnull([Name 2],'')) as [Name]
            Dim bool_DatenVorhanden As Boolean = False
            Dim Geschaeftsjahr = Now.Year 'If(Now.Month = 1, Now.Year - 1, Now.Year) --> Bei IMEX ist das GJ=Wirtschaftsjahr
            '  "Konto-Nr;Name;UST-Id-Nummer;Strasse;St;Postltz;Ort;Bankltz;Bankkonto-Nr;ZZiel;SPz;STg"
            '    Dim Encoding As New System.Text.ASCIIEncoding()
            '  Dim Encoding As New System.Text.UTF8Encoding(False)
            Dim file As System.IO.StreamWriter
            '  Dim path = DATENVERVER_OPTIONS.getTMPPath("dvo", ".txt", , False)
            Dim path = "\\192.168.1.20\Daten\DVO - DATENORDNER MIT IMPORTDATEIEN\"
            Dim pathArchiv = "\\192.168.1.20\Daten\DVO - DATENORDNER MIT IMPORTDATEIEN\Archiv\"

            If VERAG_PROG_ALLGEMEIN.cAllgemein.TESTSYSTEM Then
                path = VERAG_PROG_ALLGEMEIN.DATENVERVER_OPTIONS.getRootDir
                pathArchiv = ""
            End If

            If Not IO.Directory.Exists(path) Then IO.Directory.CreateDirectory(path)


            'Zuerst alle IMP-Daten löschen, damit es zu keinen Missverständnissen kommt
            'For Each f In IO.Directory.GetFiles(path)
            '    If f.ToString.Contains("DVO_IMP_") Then
            '        Try
            '            IO.File.Delete(f)
            '        Catch ex As Exception
            '        End Try
            '    End If
            'Next

            Dim Dateiname = "DVO_IMP_" & Now.ToString("ddMMyyyy_HHmm") & ".txt"
            file = My.Computer.FileSystem.OpenTextFileWriter(path & Dateiname, True, System.Text.Encoding.GetEncoding("iso-8859-15"))

            file.WriteLine(genBetreibsstammSatz(Geschaeftsjahr))
            If boolKunden Then
                '----------------- STAMMDATEN  -----------------
                Dim dt_Stamm As DataTable = getStammdaten()
                If dt_Stamm IsNot Nothing AndAlso dt_Stamm.Rows.Count > 0 Then
                    initKDNr(dt_Stamm)
                    For Each r In dt_Stamm.Rows
                        file.WriteLine(getPersonenKto(r))
                    Next
                    bool_DatenVorhanden = True
                End If
                updateFibu() ' UPDATE FiBuSchnittstelle --> 0

                '----------------- STAMMDATEN LG -----------------
                Dim dt_StammLG As DataTable = getStammdatenLG()
                If dt_StammLG IsNot Nothing AndAlso dt_StammLG.Rows.Count > 0 Then
                    initKDNrLG(dt_StammLG)
                    For Each r In dt_StammLG.Rows
                        file.WriteLine(getPersonenKto(r))
                    Next
                    bool_DatenVorhanden = True
                End If
                updateFibuLG() ' UPDATE FiBuSchnittstelleLG --> 0
                '----------------------------------
            End If


            '----------------- Buchungssätze -----------------
            If boolBuchungen Then
                TrdInvoice_FUNCTIONS.TrdinSchreiben(20) 'Lädt die Daten aus der Tabelle Rechnungsausgang in die Tabelle trdInvoice (nur für 20 = IMEX )
                Dim dt_Buchungen As DataTable = getBuchungen(20)
                If dt_Buchungen IsNot Nothing AndAlso dt_Buchungen.Rows.Count > 0 Then
                    Try
                        '   initKDNr(dt_Buchungen)
                        Dim SummeBuchungen As Double = 0
                        file.WriteLine(genBuchungenBeginn(Geschaeftsjahr))
                        For Each r In dt_Buchungen.Rows
                            file.WriteLine(genBuchungsZeile(r, SummeBuchungen, Geschaeftsjahr))
                        Next
                        file.WriteLine(genBuchungenEnd(SummeBuchungen))
                        bool_DatenVorhanden = True
                    Catch ex As Exception
                        MsgBox(ex.Message & ex.StackTrace)
                    End Try
                    'Update Filename TrdIn
                    SQL.doSQL("update  [tblTrdinInvoice] set TrdinDateiname='" & Now.ToString("ddMMyyyyHHmm") & "' WHERE [Firma_ID] = 20 AND TrdinDateiname is null ", "FMZOLL")
                    'SQL.doSQL("update [VERAG].[dbo].[tblTrdinInvoice] set TrdinDateiname='" & Dateiname & "' WHERE [Firma_ID] = 20 AND TrdinDateiname is null ", "FMZOLL") --> Auf 12 Zeichen beschränkt!
                End If
            End If
            '----------------------------------

            file.Close()

            'In Archiv sichern
            If pathArchiv <> "" Then
                If Not IO.Directory.Exists(pathArchiv) Then IO.Directory.CreateDirectory(pathArchiv)
                IO.File.Copy(path & Dateiname, pathArchiv & Dateiname)
            End If

            If bool_DatenVorhanden Then
                If OpenFile Then Process.Start(path & Dateiname)
            Else
                IO.File.Delete(path & Dateiname)
            End If

        Catch ex As Exception
            MsgBox(ex.Message & ex.StackTrace)
        End Try
        Return False
    End Function

    Shared Function FormatStringDVO(s As Object, Optional length As Integer = -1, Optional defaultValue As String = "") As String
        If s Is Nothing Then Return defaultValue
        If s Is DBNull.Value Then Return defaultValue
        FormatStringDVO = s.ToString.Replace(",", "")
        FormatStringDVO = s.ToString.Replace("""", "\22")
        If length > 0 Then
            If FormatStringDVO.Length > length Then FormatStringDVO = FormatStringDVO.Substring(0, length)
        End If
    End Function

    Shared Function getStammdaten() As DataTable
        Dim sqlStr = " Select   KundenNrZentrale As [Konto-Nr],isnull([Name 1],'') as Name,isnull([Name 2],'') as [Name2],(isnull(UstIdKz,'') + isnull(UstIdNr,'')) as [UST-Id-Nummer],isnull(UstIdGeprüft,'') as [UID-Datum],[Strasse],Adressen.landkz  ,PLZ,LTRIM([Ort]) as Ort,isnull(BLZ,'') as [BLZ],isnull(KTO,'') as [KTO], CASE WHEN CAST(Zahlungsziel as VARCHAR(3)) ='0' THEN '' ELSE CAST(Zahlungsziel as VARCHAR(3)) END   as [ZZiel],/*'' as [SPz],'' as [STg],*/isnull([E-Mail],'') as [mail],'' AS Info1,'' AS Inf2, Zahlungsziel2, Zahlungsziel3,[E-Mail]  "
        sqlStr &= "  ,Ordnungsbegriff "
        sqlStr &= "  FROM Adressen INNER JOIN Kunden ON Kundennr=AdressenNr "
        sqlStr &= " LEFT JOIN tblKundenErweitert ON KundenNr=kde_KundenNr "
        sqlStr &= " LEFT JOIN [Länderverzeichnis für die Außenhandelsstatistik] as St ON St.LandKz=Adressen.LandKz "
        sqlStr &= " LEFT JOIN Währungstabelle ON St.LandNr = Währungstabelle.Währungsschlüssel "
        sqlStr &= " WHERE FiBuSchnittstelle=1 AND Auswahl='A' AND FilialenNr='5501' "
        sqlStr &= " Order by [Konto-Nr] "
        Return SQL.loadDgvBySql(sqlStr, "FMZOLL")
    End Function
    Shared Function updateFibu() As DataTable
        Dim sqlStr = " UPDATE Kunden "
        sqlStr &= " SET FiBuSchnittstelle = '0' "
        sqlStr &= " FROM Adressen INNER JOIN Kunden ON Kundennr=AdressenNr "
        sqlStr &= " LEFT JOIN tblKundenErweitert ON KundenNr=kde_KundenNr "
        sqlStr &= " WHERE FiBuSchnittstelle=1 AND Auswahl='A' AND FilialenNr='5501' "
        Return SQL.loadDgvBySql(sqlStr, "FMZOLL")
    End Function
    Shared Function updateFibuLG() As DataTable
        Dim sqlStr = " UPDATE tblKundenErweitert "
        sqlStr &= " SET FiBuSchnittstelleLG = '0' "
        sqlStr &= " FROM Adressen INNER JOIN Kunden ON Kundennr=AdressenNr "
        sqlStr &= " LEFT JOIN tblKundenErweitert ON KundenNr=kde_KundenNr "
        sqlStr &= " WHERE FiBuSchnittstelleLG=1 AND Auswahl='A' AND FilialenNr='5501' "
        Return SQL.loadDgvBySql(sqlStr, "FMZOLL")
    End Function

    Shared Function getStammdatenLG() As DataTable
        Dim sqlStr = " Select   KundenNrZentrale As [Konto-Nr],isnull([Name 1],'') as Name,isnull([Name 2],'') as [Name2],(isnull(UstIdKz,'') + isnull(UstIdNr,'')) as [UST-Id-Nummer],isnull(UstIdGeprüft,'') as [UID-Datum],[Strasse],Adressen.landkz  ,PLZ,LTRIM([Ort]) as Ort,isnull(BLZ,'') as [BLZ],isnull(KTO,'') as [KTO], CASE WHEN CAST(Zahlungsziel as VARCHAR(3)) ='0' THEN '' ELSE CAST(Zahlungsziel as VARCHAR(3)) END   as [ZZiel],/*'' as [SPz],'' as [STg],*/isnull([E-Mail],'') as [mail],'' AS Info1,'' AS Inf2, Zahlungsziel2, Zahlungsziel3,[E-Mail] "
        sqlStr &= "  ,Ordnungsbegriff "
        sqlStr &= "  FROM Adressen INNER JOIN Kunden ON Kundennr=AdressenNr "
        sqlStr &= " LEFT JOIN tblKundenErweitert ON KundenNr=kde_KundenNr "
        sqlStr &= " LEFT JOIN [Länderverzeichnis für die Außenhandelsstatistik] as St ON St.LandKz=Adressen.LandKz "
        sqlStr &= " LEFT JOIN Währungstabelle ON St.LandNr = Währungstabelle.Währungsschlüssel "
        sqlStr &= " WHERE FiBuSchnittstelleLG=1 AND Auswahl='A' AND FilialenNr='5501' "
        sqlStr &= " Order by [Konto-Nr] "
        Return SQL.loadDgvBySql(sqlStr, "FMZOLL")
    End Function

    Public Shared Function getPersonenKto(row As DataRow) As String
        getPersonenKto = "10,"
        getPersonenKto &= fill0Stellen(row("Konto-Nr"), 7) & ","
        getPersonenKto &= """" & FormatStringDVO(row("Ordnungsbegriff"), 39) & ""","
        getPersonenKto &= """" & FormatStringDVO(row("Ordnungsbegriff"), 11) & ""","
        getPersonenKto &= """" & FormatStringDVO(row("UST-Id-Nummer")) & ""","
        If IsDate(row("UID-Datum")) Then
            getPersonenKto &= """" & CDate(row("UID-Datum")).ToString("ddMMyyyy") & ""","
        Else
            getPersonenKto &= ","
        End If
        getPersonenKto &= ","
        getPersonenKto &= """Firma"","
        getPersonenKto &= """" & FormatStringDVO(row("Name")) & ""","
        getPersonenKto &= """" & FormatStringDVO(row("Name2")) & ""","
        getPersonenKto &= """"","
        getPersonenKto &= """"","
        getPersonenKto &= """" & FormatStringDVO(row("Strasse")) & ""","
        getPersonenKto &= """" & cProgramFunctions.getISO2Land(FormatStringDVO(row("LandKz"))) & ""","
        getPersonenKto &= """" & FormatStringDVO(row("PLZ")) & ""","
        getPersonenKto &= """" & FormatStringDVO(row("Ort")) & ""","
        getPersonenKto &= """" & row("BLZ") & ""","
        getPersonenKto &= """" & row("KTO") & ""","
        getPersonenKto &= row("ZZiel") & ","
        getPersonenKto &= "0,"
        getPersonenKto &= "," 'getPersonenKto &= FormatStringDVO(row("Zahlungsziel2"),, 0) & ","
        getPersonenKto &= "," 'getPersonenKto &= "0,"
        getPersonenKto &= "," 'getPersonenKto &= FormatStringDVO(row("Zahlungsziel3"),, 0) & ","
        getPersonenKto &= "," 'getPersonenKto &= "0,"

        getPersonenKto &= "0,"
        getPersonenKto &= """""," 'IBAN
        getPersonenKto &= """""," 'BIC
        getPersonenKto &= """""," 'Bankeinzug
        getPersonenKto &= """""," 'Prüfdatum2
        getPersonenKto &= """""," 'ISOCOde
        getPersonenKto &= """""," 'Nicht In Zahlungsvorschläge aufnehemn
        getPersonenKto &= """""," 'Kundendaten
        getPersonenKto &= """""," 'Datum Einzugsermächtigung
        getPersonenKto &= """""," 'Mandatsref
        getPersonenKto &= """" & FormatStringDVO(row("E-Mail")) & """" 'e-Mail

    End Function



    Shared Function getBuchungen(Firma_ID As Integer) As DataTable

        Dim sqlStr = " SELECT TOP (1000) IVO.Rechnungsdatum,IVO.Rechnungsnummer,min(Speditionsbuch) as Speditionsbuch	,min(FilialenNr) as FilialenNr,min(AbfertigungsNr) as AbfertigungsNr,min(UnterNr) as UnterNr,min(Abfertigungsdatum) as Abfertigungsdatum,min(BelegartenNr) as BelegartenNr
                        ,min(KundenNrZentrale) as [KundenNrZentrale],min(RechnungsKundenNr) as RechnungsKundenNr,min(RechnungsLandKz) as RechnungsLandKz,min(RechnungsUstIdKz) as RechnungsUstIdKz,min(RechnungsUstIdNr) as RechnungsUstIdNr
                        ,min(Lastschrift+0) as Lastschrift,min(Steuerschlüssel) as Steuerschlüssel,min([Steuersatz %]) as Steuersatz,min(WährungLokal) as WährungLokal,min(SteuerbetragLokal) as SteuerbetragLokal,min(RechnungsbetragLokal) as RechnungsbetragLokal
                        ,min(WährungFremd) as WährungFremd,min(SteuerbetragFremd) as SteuerbetragFremd,min(RechnungsbetragFremd) as RechnungsbetragFremd,min(TrdinDateiname) as TrdinDateiname,min(Abgleich_op_debitor+0) as Abgleich_op_debitor
                        ,min(Nettozahlungsziel) as Nettozahlungsziel,min(Firma_ID) as Firma_ID,min(UStVAn_ID)  as UStVAn_ID	  ,Leistungsnummer,Leistungsbezeichnung,Steuerpflichtig,sum(PROD.SteuerpflichtigerBetragLokal) as SteuerpflichtigerBetragLokal
                        ,sum(PROD.SteuerfreierBetragLokal) as SteuerfreierBetragLokal,sum(PROD.SteuerpflichtigerBetragFremd) as SteuerpflichtigerBetragFremd,sum(PROD.SteuerfreierBetragFremd) as SteuerfreierBetragFremd,min(Belegdaten) as Belegdaten
                        ,min(Sachkonto) as Sachkonto,min(Buchungstext) as Buchungstext
                       FROM VERAG.dbo.tblTrdinInvoice as IVO left JOIN tblTrdinProduct as PROD on IVO.Rechnungsnummer=PROD.Rechnungsnummer
                       WHERE Firma_ID ='" & Firma_ID & "'  AND TrdinDateiname Is Null	 
                       group by Firma_id, IVO.Rechnungsnummer,IVO.rechnungsdatum,Leistungsnummer,Leistungsbezeichnung,Steuerpflichtig
                       ORDER BY Rechnungsdatum, Rechnungsnummer "
        ' --and IVO.Rechnungsnummer=19400002
        Return SQL.loadDgvBySql(sqlStr, "FMZOLL", 9800)

    End Function
    Public Shared Function genBuchungsZeile(row As DataRow, ByRef SummeBuchungen As Double, ByRef Geschäftsjahr As Integer) As String
        genBuchungsZeile = "110,"
        genBuchungsZeile &= fill0Stellen(initKDNr2(row("KundenNrZentrale")), 7) & ","
        genBuchungsZeile &= fill0Stellen(row("Sachkonto"), 6) & ","
        genBuchungsZeile &= """" & CDate(row("Rechnungsdatum")).ToString("ddMMyyyy") & ""","
        genBuchungsZeile &= """" & FormatStringDVO(row("Rechnungsnummer")) & ""","
        genBuchungsZeile &= """" & FormatStringDVO(row("Rechnungsnummer")) & ""","
        genBuchungsZeile &= getDbltr(getDbl(row("SteuerpflichtigerBetragLokal")) + getDbl(row("SteuerfreierBetragLokal")) + getDbl(row("SteuerpflichtigerBetragFremd")) + getDbl(row("SteuerfreierBetragFremd"))) & ","
        genBuchungsZeile &= getSteuercode(row) & ","
        genBuchungsZeile &= """""," '   genBuchungsZeile &= """" & cProgramFunctions.getISO2Land(FormatStringDVO(row("RechnungsLandKz"))) & ""","
        Dim steuerbetragLokal As Double = 0
        If CBool(row("Steuerpflichtig")) Then
            steuerbetragLokal = CDbl((getDbl2(row("SteuerpflichtigerBetragLokal")) * getDbl2(row("Steuersatz"))))
            steuerbetragLokal += CDbl((getDbl2(row("SteuerpflichtigerBetragFremd")) * getDbl2(row("Steuersatz"))))
        End If
        If steuerbetragLokal < 0 Then steuerbetragLokal = steuerbetragLokal * -1
        genBuchungsZeile &= "" & If(steuerbetragLokal <> 0, getDbltr(steuerbetragLokal), "") & ","
        genBuchungsZeile &= """" & FormatStringDVO(FormatStringDVO(row("RechnungsUstIdKz")) & FormatStringDVO(row("RechnungsUstIdNr")), 15) & ""","
        genBuchungsZeile &= "1," ' Kostenstelle
        genBuchungsZeile &= """""," ' Fremdwährung
        genBuchungsZeile &= "," ' Fremdwährung-Betrag
        genBuchungsZeile &= """" & FormatStringDVO(row("Buchungstext")) & """," ' Text
        genBuchungsZeile &= If(row("BelegartenNr") = "70", """R"",", """G"",") ' Rg/GS 70=Rechnung
        genBuchungsZeile &= "," ' Fäll-Tage1
        genBuchungsZeile &= "," ' Fäll-% 1
        genBuchungsZeile &= "," ' Fäll-Tage2
        genBuchungsZeile &= "," ' Fäll-% 2
        genBuchungsZeile &= "," ' Fäll-Tage3
        genBuchungsZeile &= "," ' Fäll-% 3
        genBuchungsZeile &= """""," ' Storno --> S=Storno
        genBuchungsZeile &= "," ' Kurs
        genBuchungsZeile &= Geschäftsjahr & "," ' GJ
        genBuchungsZeile &= """""," ' ISO
        genBuchungsZeile &= """""," ' DL
        genBuchungsZeile &= """""," ' Imp,Exp
        genBuchungsZeile &= """""," ' Barcode
        genBuchungsZeile &= "," ' DocuWare
        genBuchungsZeile &= "," ' Nova
        genBuchungsZeile &= FormatStringDVO(row("Lastschrift")) & "," ' Bankeinzug 0/1 
        '... aus ...
        SummeBuchungen += getDbl(getDbl(row("SteuerpflichtigerBetragLokal")) + getDbl(row("SteuerfreierBetragLokal")))
    End Function
    Shared Sub initKDNr(ByRef dt As DataTable)
        For Each r In dt.Rows
            If IsNumeric(r("Konto-Nr")) AndAlso CInt(r("Konto-Nr")) > 1000000 Then r("Konto-Nr") = (CInt(r("Konto-Nr")) - 1000000) + 200000
            '     MsgBox((CInt(r("Konto-Nr")) - 1000000) + 200000)
        Next
    End Sub
    Shared Sub initKDNrLG(ByRef dt As DataTable)
        For Each r In dt.Rows
            If IsNumeric(r("Konto-Nr")) AndAlso CInt(r("Konto-Nr")) > 1000000 Then r("Konto-Nr") = (CInt(r("Konto-Nr")) - 1000000) + 500000
            '     MsgBox((CInt(r("Konto-Nr")) - 1000000) + 200000)
        Next
    End Sub
    Shared Function initKDNr2(ktoNr As String) As String
        If IsNumeric(ktoNr) AndAlso CInt(ktoNr) > 1000000 Then ktoNr = (CInt(ktoNr) - 1000000) + 200000
        Return ktoNr
    End Function
    ' Trdin=false

    Shared Function getSteuercode(r As DataRow) As Double
        Select Case r("Sachkonto")
            Case "3550", "3553", "3558"
                Return "999"
            Case Else

                If CBool(r("Steuerpflichtig")) Then
                    Return "320"
                Else
                    If (r("RechnungsLandKz")) = "A" Or (r("RechnungsLandKz")) = "AT" Then
                        Return "999" 'Steuerpflichtig und AT
                    Else
                        If SQL.DLookup("[MitgliedslandEU]", "[Währungstabelle]", "[LandKz]='" & r("RechnungsLandKz") & "'", "FMZOLL", False) Then
                            Return "969" ' EU-LAND
                        Else
                            Return "973" ' Drittland
                        End If
                    End If
                End If

        End Select
        Return ""

    End Function



    Shared Function getDbl(o As Object) As Double
        Try
            If o Is Nothing Then Return 0
            If o Is DBNull.Value Then Return 0
            Return CDbl(o)
        Catch ex As Exception
            Return 0
        End Try
    End Function
    Shared Function getDbl2(o As Object) As Double
        Try
            If o Is Nothing Then Return 0
            If o Is DBNull.Value Then Return 0
            Return CDbl(o.ToString.Replace(".", ","))
        Catch ex As Exception
            Return 0
        End Try
    End Function


    Shared Function getDbltr(o As Object) As String
        Try
            If o Is Nothing Then Return 0
            If o Is DBNull.Value Then Return 0
            Return CDbl(o).ToString("N2").Replace(".", "").Replace(",", ".")
        Catch ex As Exception
            Return 0
        End Try
    End Function

    Shared Function fill0Stellen(i As Object, l As Integer) As String
        If i Is DBNull.Value Then i = ""
        If i Is Nothing Then i = ""
        While i.ToString.Length < l
            i &= "0"
        End While
        Return i
    End Function


End Class