572 lines
30 KiB
VB.net
572 lines
30 KiB
VB.net
Public Class cBMD_Interface
|
|
|
|
Shared SQL As New VERAG_PROG_ALLGEMEIN.SQL
|
|
'STAMMDATEN
|
|
|
|
|
|
'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 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],[Telefon] "
|
|
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='5601' "
|
|
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='5601' "
|
|
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 = ""
|
|
getPersonenKto &= row("Konto-Nr") & ";"
|
|
getPersonenKto &= FormatStringDVO(row("Name")) & ";"
|
|
getPersonenKto &= FormatStringDVO(row("Name2")) & ";"
|
|
getPersonenKto &= FormatStringDVO(row("Strasse")) & ";"
|
|
|
|
getPersonenKto &= cProgramFunctions.getISO2Land(FormatStringDVO(row("LandKz"))) & ";"
|
|
getPersonenKto &= FormatStringDVO(row("PLZ")) & ";"
|
|
getPersonenKto &= FormatStringDVO(row("Ort")) & ";"
|
|
getPersonenKto &= row("ZZiel") & ";"
|
|
getPersonenKto &= FormatStringDVO(row("UST-Id-Nummer")) & ";"
|
|
|
|
getPersonenKto &= FormatStringDVO(row("Telefon")) & ";"
|
|
getPersonenKto &= FormatStringDVO(row("E-Mail")) & ";"
|
|
|
|
|
|
getPersonenKto &= row("BLZ") & ";"
|
|
getPersonenKto &= row("KTO") & ";"
|
|
|
|
End Function
|
|
|
|
|
|
Public Shared Function genDatensatzCSV_Stammdaten(Optional OpenFile As Boolean = False) As Boolean ' Optional OpenFile = False, Optional boolKunden = True, Optional boolBuchungen = True ) As Boolean
|
|
Try
|
|
|
|
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
|
|
|
|
'DATEIPFAD für Stammdaten
|
|
'-------------------------------------------------------------------------------------------
|
|
Dim pathArchivKD = "\\srv07.unisu.at\Unisped Daten\VERAG AR-Daten\" '"\\192.168.1.20\Daten\DVO - DATENORDNER MIT IMPORTDATEIEN\"
|
|
Dim pathKD = "\\share01\Datenarchiv\UNISPED\BMD\STAMMDATEN\" & Geschaeftsjahr & "\" ' "\\192.168.1.20\Daten\DVO - DATENORDNER MIT IMPORTDATEIEN\Archiv\"
|
|
'pathKD = pathArchivKD 'TEST
|
|
|
|
If VERAG_PROG_ALLGEMEIN.cAllgemein.TESTSYSTEM Then
|
|
'path = VERAG_PROG_ALLGEMEIN.DATENVERVER_OPTIONS.getRootDir
|
|
'pathArchivKD = ""
|
|
End If
|
|
If Not IO.Directory.Exists(pathKD) Then IO.Directory.CreateDirectory(pathKD)
|
|
If Not IO.Directory.Exists(pathArchivKD) Then IO.Directory.CreateDirectory(pathArchivKD)
|
|
'-------------------------------------------------------------------------------------------
|
|
|
|
|
|
If True Then 'boolKunden Then
|
|
'----------------- STAMMDATEN -----------------
|
|
Dim dt_Stamm As DataTable = getStammdaten()
|
|
If dt_Stamm IsNot Nothing AndAlso dt_Stamm.Rows.Count > 0 Then
|
|
|
|
Dim fileKD As System.IO.StreamWriter
|
|
Dim DateinameKD = "BMD_KD_" & Now.ToString("ddMMyyyy_HHmm") & ".csv"
|
|
fileKD = My.Computer.FileSystem.OpenTextFileWriter(pathKD & DateinameKD, True, System.Text.Encoding.GetEncoding("iso-8859-15"))
|
|
|
|
|
|
fileKD.WriteLine(genStammdatenBeginn())
|
|
|
|
|
|
|
|
initKDNr(dt_Stamm)
|
|
For Each r In dt_Stamm.Rows
|
|
fileKD.WriteLine(getPersonenKto(r))
|
|
Next
|
|
bool_DatenVorhanden = True
|
|
|
|
|
|
fileKD.Close()
|
|
|
|
'In Archiv sichern
|
|
If pathArchivKD <> "" Then
|
|
If Not IO.Directory.Exists(pathArchivKD) Then IO.Directory.CreateDirectory(pathArchivKD)
|
|
If pathKD <> pathArchivKD Then IO.File.Copy(pathKD & DateinameKD, pathArchivKD & DateinameKD)
|
|
End If
|
|
|
|
If bool_DatenVorhanden Then
|
|
If OpenFile Then Process.Start(pathKD & DateinameKD)
|
|
Else
|
|
IO.File.Delete(pathKD & DateinameKD)
|
|
End If
|
|
|
|
updateFibu() ' UPDATE FiBuSchnittstelle --> 0
|
|
|
|
End If
|
|
End If
|
|
|
|
|
|
|
|
Catch ex As Exception
|
|
MsgBox(ex.Message & ex.StackTrace)
|
|
End Try
|
|
Return False
|
|
End Function
|
|
|
|
|
|
|
|
Public Shared Function genDatensatzCSV_ALL(Optional OpenFile As Boolean = False, Optional boolKunden As Boolean = True, Optional boolBuchungen As Boolean = True) As Boolean
|
|
genDatensatzCSV_ALL = True
|
|
If boolKunden Then
|
|
If Not genDatensatzCSV_Stammdaten(OpenFile) Then genDatensatzCSV_ALL = False
|
|
End If
|
|
If boolBuchungen Then
|
|
If Not genDatensatzCSV_Buchungen(21, OpenFile) Then genDatensatzCSV_ALL = False
|
|
If Not genDatensatzCSV_Buchungen(22, OpenFile) Then genDatensatzCSV_ALL = False
|
|
If Not genDatensatzCSV_Buchungen(23, OpenFile) Then genDatensatzCSV_ALL = False
|
|
End If
|
|
End Function
|
|
|
|
Public Shared Function genDatensatzCSV_Buchungen(ByRef Firma_ID As Integer, Optional OpenFile As Boolean = False) As Boolean ' Optional OpenFile = False, Optional boolKunden = True, Optional boolBuchungen = True ) As Boolean
|
|
Try
|
|
Dim FIRMA As New VERAG_PROG_ALLGEMEIN.cFirmen(Firma_ID)
|
|
|
|
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
|
|
|
|
'DATEIPFAD für Buchungsdaten
|
|
'-------------------------------------------------------------------------------------------
|
|
Dim pathArchiv = "\\srv07.unisu.at\Unisped Daten\VERAG AR-Daten\" '"\\192.168.1.20\Daten\DVO - DATENORDNER MIT IMPORTDATEIEN\"
|
|
Dim path = "\\share01\Datenarchiv\UNISPED\BMD\BUCHUNGEN\" & Geschaeftsjahr & "\" ' "\\192.168.1.20\Daten\DVO - DATENORDNER MIT IMPORTDATEIEN\Archiv\"
|
|
' path = pathArchiv 'TEST
|
|
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)
|
|
If Not IO.Directory.Exists(pathArchiv) Then IO.Directory.CreateDirectory(pathArchiv)
|
|
'-------------------------------------------------------------------------------------------
|
|
|
|
|
|
Dim NameZusatz = ""
|
|
Select Case FIRMA.Firma_ID
|
|
Case 21 : NameZusatz = "UNISPED_AT_"
|
|
Case 22 : NameZusatz = "UNISPED_DE_"
|
|
Case 23 : NameZusatz = "UNISPED_ZS_"
|
|
End Select
|
|
|
|
|
|
'----------------- Buchungssätze -----------------
|
|
If True Then 'boolBuchungen Then
|
|
TrdInvoice_FUNCTIONS.TrdinSchreiben(FIRMA.Firma_ID) 'Lädt die Daten aus der Tabelle Rechnungsausgang in die Tabelle trdInvoice (nur für 20 = IMEX )
|
|
Dim dt_Buchungen As DataTable = getBuchungen(FIRMA.Firma_ID)
|
|
If dt_Buchungen IsNot Nothing AndAlso dt_Buchungen.Rows.Count > 0 Then
|
|
Try
|
|
Dim file As System.IO.StreamWriter
|
|
Dim Dateiname = "BMD_IMP_" & NameZusatz & Now.ToString("ddMMyyyy_HHmm") & ".csv"
|
|
file = My.Computer.FileSystem.OpenTextFileWriter(path & Dateiname, True, System.Text.Encoding.GetEncoding("iso-8859-15"))
|
|
|
|
' initKDNr(dt_Buchungen)
|
|
Dim SummeBuchungen As Double = 0
|
|
file.WriteLine(genBuchungenBeginn())
|
|
For Each r In dt_Buchungen.Rows
|
|
file.WriteLine(genBuchungsZeile(r, SummeBuchungen, Geschaeftsjahr, FIRMA))
|
|
Next
|
|
' file.WriteLine(genBuchungenEnd(SummeBuchungen))
|
|
bool_DatenVorhanden = True
|
|
|
|
'Update Filename TrdIn
|
|
SQL.doSQL("update [tblTrdinInvoice] Set TrdinDateiname='" & Now.ToString("ddMMyyyyHHmm") & "' WHERE [Firma_ID] = " & FIRMA.Firma_ID & " AND TrdinDateiname is null ", "FMZOLL")
|
|
|
|
file.Close()
|
|
|
|
'In Archiv sichern
|
|
If pathArchiv <> "" Then
|
|
If Not IO.Directory.Exists(pathArchiv) Then IO.Directory.CreateDirectory(pathArchiv)
|
|
If path <> pathArchiv Then 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
|
|
|
|
End If
|
|
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(",", "")
|
|
'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 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")
|
|
|
|
End Function
|
|
|
|
Public Shared Function genBuchungenBeginn() As String
|
|
Return "satzart;konto;gkonto;belegdatum;belegnr;extbelegnr;buchsymbol;buchcode;steuercode;prozent;betrag;steuer;text;zziel;kost"
|
|
End Function
|
|
Public Shared Function genBuchungenEnd() As String
|
|
Return ""
|
|
End Function
|
|
|
|
|
|
Public Shared Function genStammdatenBeginn() As String
|
|
Return "Kto-Nr;Nachname;Zusatzname;Straße;Staat;Plz;Ort;ZZiel;UID-Nummer;Telefonnummer;Mail;IBAN;BIC"
|
|
End Function
|
|
Public Shared Function genStammdatenEnd() As String
|
|
Return ""
|
|
End Function
|
|
|
|
Public Shared Function genBuchungsZeile(row As DataRow, ByRef SummeBuchungen As Double, ByRef Geschäftsjahr As Integer, ByRef FIRMA As VERAG_PROG_ALLGEMEIN.cFirmen) As String
|
|
genBuchungsZeile = "0;"
|
|
genBuchungsZeile &= (initKDNr2(row("KundenNrZentrale"))) & ";"
|
|
genBuchungsZeile &= fill0Stellen(row("Sachkonto"), 4) & ";"
|
|
genBuchungsZeile &= "" & CDate(row("Rechnungsdatum")).ToString("dd.MM.yyyy") & ";"
|
|
Dim GJ_short = Geschäftsjahr.ToString.Substring(2, 2)
|
|
Select Case FIRMA.Firma_ID
|
|
Case 21 : genBuchungsZeile &= GJ_short & "-" & FormatStringDVO(row("Rechnungsnummer")) & ";"
|
|
Case 22 : genBuchungsZeile &= GJ_short & "-" & FormatStringDVO(row("Rechnungsnummer")) & ";"
|
|
Case 23 : genBuchungsZeile &= GJ_short & "-" & FormatStringDVO(row("Rechnungsnummer")) & ";"
|
|
End Select
|
|
|
|
genBuchungsZeile &= SQL.DLookup("KdAuftragsNr", "Rechnungsausgang", "rechnungsnr=" & row("Rechnungsnummer") & " and Firma_ID=" & FIRMA.Firma_ID & "", "FMZOLL", "") & ";" 'extbelegnr
|
|
|
|
genBuchungsZeile &= If(row("BelegartenNr") = "70", "AR", "AG") & ";" ' Rg/GS 70=Rechnung 'AG ????????
|
|
genBuchungsZeile &= If(row("BelegartenNr") = "70", "1", "2") & ";" ' Rg/GS 70=Rechnung 'AG ????????
|
|
|
|
' genBuchungsZeile &= "1;" 'buchcode === 1 oder 2??
|
|
genBuchungsZeile &= getSteuercode(row, FIRMA) & ";"
|
|
If CBool(row("Steuerpflichtig")) Then
|
|
genBuchungsZeile &= (getDbl2(row("Steuersatz")) * 100) & ";" 'buchcode === 1 oder 2??
|
|
Else
|
|
genBuchungsZeile &= "0;" 'buchcode === 1 oder 2??
|
|
End If
|
|
|
|
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 --> SONST PROBLKEM BEI UNISPED; DA STEUER vom NETTO abgezogen wird!
|
|
|
|
genBuchungsZeile &= getDbltr(getDbl(row("SteuerpflichtigerBetragLokal")) + getDbl(row("SteuerfreierBetragLokal")) + getDbl(row("SteuerpflichtigerBetragFremd")) + getDbl(row("SteuerfreierBetragFremd")) + If(steuerbetragLokal <> 0, getDbltr(steuerbetragLokal), "0")) & ";" 'betrag + steuer (brutto)
|
|
genBuchungsZeile &= "" & If(steuerbetragLokal <> 0, getDbltr(steuerbetragLokal), "0") & ";" 'steuer
|
|
|
|
genBuchungsZeile &= FormatStringDVO(SQL.DLookup("[Ordnungsbegriff]", "Adressen", " AdressenNr= " & row("KundenNrZentrale"), "FMZOLL", "")) & ";" ' BuchungsText bei UNISPED ist der FIRMENNAME!!
|
|
' genBuchungsZeile &= FormatStringDVO(row("Buchungstext")) & ";" ' Text
|
|
genBuchungsZeile &= FormatStringDVO(row("Nettozahlungsziel")) & ";" ' Text
|
|
|
|
'... 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")) > 2000000 Then r("Konto-Nr") = (CInt(r("Konto-Nr")) - 2000000)
|
|
' 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) > 2000000 Then ktoNr = (CInt(ktoNr) - 2000000) '+ 200000
|
|
Return ktoNr
|
|
End Function
|
|
' Trdin=false
|
|
|
|
Shared Function getSteuercode(r As DataRow, FIRMA As VERAG_PROG_ALLGEMEIN.cFirmen) As String
|
|
Select Case r("Sachkonto")
|
|
'Case "3550", "3553", "3558"
|
|
' Return "999"
|
|
Case Else
|
|
|
|
If Not CBool(r("Steuerpflichtig")) Then
|
|
'Vorher immer return "" - galube es soll bei eu aber 77 sein...
|
|
If SQL.DLookup("[MitgliedslandEU]", "[Währungstabelle]", "[LandKz]='" & r("RechnungsLandKz") & "'", "FMZOLL", False) Then
|
|
Return "77" ' EU-LAND
|
|
Else
|
|
Return "" ' Drittland
|
|
End If
|
|
Else
|
|
If (r("RechnungsLandKz")).ToString.Replace("AT", "A").Replace("DE", "D") = FIRMA.Firma_LandKz Then 'Abhängig von FIRMA --> INLAND!!!!!!
|
|
Return "1" 'Steuerpflichtig und AT
|
|
Else
|
|
If SQL.DLookup("[MitgliedslandEU]", "[Währungstabelle]", "[LandKz]='" & r("RechnungsLandKz") & "'", "FMZOLL", False) Then
|
|
Return "77" ' EU-LAND
|
|
Else
|
|
Return "" ' 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 |