Option Explicit On
Imports System.ComponentModel
Imports System.IO
Imports System.Text
Imports IntraSell_DLL
Imports IntraSell_Net_Base

Module ModuleBuchVorgangDruck

    Public selectedOfAdressenWeitereId As String 'ausgewählte adresse
    Public currentIDNR As String


    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="Vorgangtyp"></param>
    ''' <param name="Vorgang_Nummer"></param>
    ''' <remarks></remarks>
    Public Sub OpenAusdruck_inWord(Vorgangtyp As String, Vorgang_Nummer As String)
        Call OpenAusdruck_inWord_Filename(Vorgangtyp, Vorgang_Nummer, "Vorlagen\Vorlage_Rechnung.doc")
    End Sub

    ''' <summary>
    ''' Oeffnet den Ausdruck mit Word formular
    ''' </summary>
    ''' <param name="VorgangTyp"></param>
    ''' <param name="VorgangNummer"></param>
    ''' <param name="Dateiname"></param>
    ''' <param name="silent"></param>
    ''' <returns>Dateiname nach der Konvertierung</returns>
    Public Function OpenAusdruck_inWord_Filename(VorgangTyp As String,
                                            VorgangNummer As String,
                                            Dateiname As String,
                                            Optional silent As Boolean = False) As String

        Dim App 'As Application
        Dim VorlageFilename

        Dim sql As String


        VorlageFilename = AppFolder() & Dateiname

        App = CreateObject("Word.Application")
        App.Visible = True

        App.Documents.Open(fileName:=VorlageFilename, ConfirmConversions:=False, readonly:=False, AddToRecentFiles:=False, Format:=0)

        sql = GetRecSource(VorgangTyp, VorgangNummer)
        Dim rs = OpenRecordsetInMemory(sql)

        If rs.Rows.Count <= 0 Then
            MsgBox("Es sind keine Daten vorhanden!", vbCritical)
            App.Quit()
            Return Nothing
        End If

        Dim idnr As String = rs.Rows(0)("IDNR")

        If CheckIfWeitereVorhanden(idnr, VorgangTyp) > 0 AndAlso VorgangTyp = VorgangTypen.LI Then
            If MsgBox("Es sind weitere Adressdaten vorhanden! Möchten Sie diese verwenden?", vbYesNo) Then
                Dim selectedIdNr As String = SelectEineWeitereAdresse(VorgangTyp, VorgangNummer)
                sql = GetRecSource_Weitere(VorgangTyp, VorgangNummer, selectedIdNr)
            End If
        End If

        rs = OpenRecordsetInMemory(sql)
        If rs.Rows.Count > 0 Then

            Call ReplaceInWordOnce(App, "[Idnr]", rs.Rows(0)("Idnr") & "")
            Call ReplaceInWordOnce(App, "[Kundennummer]", rs.Rows(0)("Idnr") & "")

            Call ReplaceInWordOnce(App, "[firma]", rs.Rows(0)("firma") & "")
            Call ReplaceInWordOnce(App, "[name]", rs.Rows(0)("namen") & "")
            Call ReplaceInWordOnce(App, "[strasse]", rs.Rows(0)("adresse") & "")
            Call ReplaceInWordOnce(App, "[plz ort]", rs.Rows(0)("plzort") & "")

            Call ReplaceInWordOnce(App, "[Titel]", VorgangTyp & "-" & VorgangNummer)
            Call ReplaceInWordOnce(App, "[Datum]", rs.Rows(0)("Datum") & "")
            Call ReplaceInWordOnce(App, "[DatumF]", Format(rs.Rows(0)("Datum"), "dd.MM.yyyy") & "")
            Call ReplaceInWordOnce(App, "[Betreuer]", "")
            Call ReplaceInWordOnce(App, "[Email]", rs.Rows(0)("Email") & "")

            Call ReplaceInWordOnce(App, "[Zahlungsbedingung]", rs.Rows(0)("ZahlungsBedingung") & "")
            Call ReplaceInWordOnce(App, "[Zahlungsmethode]", rs.Rows(0)("ZahlungsMethode") & "")
            Call ReplaceInWordOnce(App, "[Transportmethode]", rs.Rows(0)("TransportMethode") & "")

            Call ReplaceInWordOnce(App, "[Netto]", FormatNumber(rs("Summe"), 2))
            Call ReplaceInWordOnce(App, "[MWST]", FormatNumber(rs("SummeMWST"), 2))
            Call ReplaceInWordOnce(App, "[Total]", FormatNumber(rs("SummeBrutto"), 2))
        End If


        'PART II - Positionen

        sql = "select count(Id) as pos from `buchVorgang-Artikel` where Nummer=" & VorgangNummer & " and Typ = '" & VorgangTyp & "'"
        Dim rsArt = OpenRecordsetInMemory(sql)
        Dim Positionen
        If rsArt.Rows.Count > 0 Then

            Positionen = rsArt.Rows(0)("pos")

            'copy the artikel line times as needed
            App.Selection.MoveUp(Unit:=7, Count:=3)
            App.Selection.Find.ClearFormatting()
            App.Selection.Find.replacement.ClearFormatting()
            With App.Selection.Find
                .Text = "[Stk]"
                .replacement.Text = "1"
                .Forward = True
                .Wrap = 1
                .Format = False
                .MatchCase = False
                .MatchWholeWord = False
                .MatchWildcards = False
                .MatchSoundsLike = False
                .MatchAllWordForms = False
            End With


            Dim foundstk : foundstk = App.Selection.Find.execute
            App.Selection.EndKey(Unit:=5, Extend:=1)
            App.Selection.Copy()
            Dim i
            If foundstk Then
                For i = 1 To Positionen
                    App.Selection.Paste()
                Next i
            End If
            'replace artnr, preis etc.

            sql = "select va.*, Beschreibung from `buchVorgang-Artikel` va,  grArtikel a where va.artnr = a.artnr " &
                " and va.Nummer=" & VorgangNummer & " and va.Typ='" & VorgangTyp & "'"
            Dim rsArtT = OpenRecordsetInMemory(sql)
            For Each rsArtRow In rsArtT.Rows
                Call ReplaceInWordOnce(App, "[Stk]", rsArtRow("Stk"))
                Call ReplaceInWordOnce(App, "[ArtNR]", rsArtRow("ArtNR"))
                Call ReplaceInWordOnce(App, "[Bezeichnung]", rsArtRow("Bezeichnung") & "")
                Call ReplaceInWordOnce(App, "[Beschreibung]", rsArtRow("Beschreibung") & "")
                Call ReplaceInWordOnce(App, "[Preis]", FormatNumber(rsArtRow("Preis_Netto"), 2))
                Call ReplaceInWordOnce(App, "[Preis_Netto]", FormatNumber(rsArtRow("Preis_Netto"), 2))
                Call ReplaceInWordOnce(App, "[Preis_Brutto]", FormatNumber(rsArtRow("Preis_Brutto"), 2))
                Call ReplaceInWordOnce(App, "[MWST]", FormatNumber(rsArtRow("Mwst"), 2))
            Next
        End If

        'Eigenschaften
        sql = "select  * from buchVorgaengeEigenschaften where Nummer  = " & VorgangNummer & " and  vorgangtyp like '" & VorgangTyp & "'"
        Dim rsEigT = OpenRecordsetInMemory(sql)
        For Each rsEig In rsEigT.Rows
            Call ReplaceInWordOnce(App, "[" & rsEig("Name") & "]", rsEig("Value") & "")
        Next

        Dim saveAsFilename As String = AppFolder() & VorgangTyp & "_" & VorgangNummer & ".doc"
        If Not silent Then
            If MsgBox("Datei " & saveAsFilename & " speichern?", vbYesNo) = vbYes Then

                App.ActiveDocument.SaveAs(fileName:=saveAsFilename, FileFormat:=
                    0, LockComments:=False, Password:="", AddToRecentFiles:=
                    True, WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:=
                    False, SaveNativePictureFormat:=False, SaveFormsData:=False,
                    SaveAsAOCELetter:=False)

            End If
        Else
            App.ActiveDocument.SaveAs(fileName:=saveAsFilename, FileFormat:=
               0, LockComments:=False, Password:="", AddToRecentFiles:=
               True, WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:=
               False, SaveNativePictureFormat:=False, SaveFormsData:=False,
               SaveAsAOCELetter:=False)
        End If
        Try
            App.Quit()
        Catch ex As Exception
            'do nothing 
        End Try
        Return saveAsFilename
    End Function


    ''' <summary>
    ''' Erstellt eine WORD Datei mit den gegebenen Informationen
    ''' </summary>
    ''' <param name="IDNR"></param>
    ''' <param name="Subjekt"></param>
    ''' <param name="BriefText"></param>
    ''' <param name="Datum"></param>
    ''' <param name="PrintAndClose"></param>
    ''' <remarks></remarks>
    Public Sub OpenKorrespondenz_inWord(IDNR As String,
                                        Subjekt As String,
                                        BriefText As String,
                                        Datum As Date,
                                        PrintAndClose As Boolean)
        Dim App 'As Application
        Dim VorlageFilename

        VorlageFilename = DbFolder() & "Vorlage_Brief.doc"

        App = CreateObject("Word.Application")

        App.Visible = False
        App.Documents.Open(fileName:=VorlageFilename, ConfirmConversions:=False, readonly:=False, AddToRecentFiles:=False, Format:=0)
        Dim rs = OpenRecordsetInMemory("select * from qry_Adressfelder where idnr=" & IDNR)

        If rs.Rows.Count <= 0 Then
            MsgBox("Die Daten sind nicht vorhanden!", vbCritical)
            Try
                App.Quit()
            Catch ex As Exception
                'noting
            End Try
            Return
        End If

        Call ReplaceInWordOnce(App, "[IDNR]", rs.Rows(0)("idnr") & "")
        Call ReplaceInWordOnce(App, "[Kundennummer]", rs.Rows(0)("idnr") & "")
        Call ReplaceInWordOnce(App, "[firma]", rs.Rows(0)("firma") & "")
        Call ReplaceInWordOnce(App, "[name]", rs.Rows(0)("name") & "")
        Call ReplaceInWordOnce(App, "[strasse]", rs.Rows(0)("adresse") & "")
        Call ReplaceInWordOnce(App, "[plz ort]", rs.Rows(0)("plz") & " " & rs("ort") & "")
        Call ReplaceInWordOnce(App, "[plz]", rs.Rows(0)("plz") & "")
        Call ReplaceInWordOnce(App, "[ort]", rs.Rows(0)("ort") & "")
        Call ReplaceInWordOnce(App, "[Email]", rs.Rows(0)("Email") & "")

        Call ReplaceInWordOnce(App, "[Titel]", "" & Subjekt)
        Call ReplaceInWordOnce(App, "[Datum]", Datum & "")
        Call ReplaceInWordOnce(App, "[Text]", "")
        App.Selection.TypeText(BriefText & "")
        App.Visible = True
        Dim saveAsFilename As String = AppFolder() & "Korrespondenz_" & IDNR & "_" & Year(Today) & Month(Today) & (Today) & ".doc"
        If Not PrintAndClose Then
            If MsgBox("Datei " & saveAsFilename & " speichern?", vbYesNo) = vbYes Then
                App.ActiveDocument.SaveAs(fileName:=saveAsFilename, FileFormat:=
                    0, LockComments:=False, Password:="", AddToRecentFiles:=
                    True, WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:=
                    False, SaveNativePictureFormat:=False, SaveFormsData:=False,
                    SaveAsAOCELetter:=False)
            End If
        Else ' print and do not close word
            App.ActiveDocument.SaveAs(fileName:=saveAsFilename, FileFormat:=0)
            App.ActiveDocument.PrintOut()
            'the word instance will not be closed
            Return
        End If

        Try
            App.Quit()
        Catch ex As Exception
            'nothing
        End Try
    End Sub


    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="VorgangTyp"></param>
    ''' <param name="VorgangNummer"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetKundenEmail(VorgangTyp As String, VorgangNummer As String)
        Dim Email As String
        Dim sql As String

        sql = $"SELECT Email from ofAdressen where idnr in (select KundNr from buchVorgang WHERE Typ ='{VorgangTyp}' and Nummer={VorgangNummer})"

        If VorgangTyp = "LAU" Then
            sql = $"select Email from lieferantenAdressen where idnr in (select LieferantNr from buchVorgang WHERE  Typ ='{VorgangTyp}' and Nummer = {VorgangNummer})"
        End If

        Dim rsEmail = FirstRow(sql)
        Email = "kunden Email"
        If rsEmail IsNot Nothing AndAlso Not IsDBNull(rsEmail) Then
            Email = rsEmail
        End If
        Return Email
    End Function

    ''' <summary>
    ''' oeffnet den ausdruck mit Word Vorlage
    ''' </summary>
    ''' <param name="Vorgangtyp"></param>
    ''' <param name="Vorgang_Nummer"></param>
    ''' <param name="FormatNummer"></param>
    ''' <remarks></remarks>
    Public Sub OpenAusdruck_inWord_RTF(Vorgangtyp As String, Vorgang_Nummer As String, FormatNummer As String)
        Call OpenAusdruck_InWord_Filename_RTF(Vorgangtyp, Vorgang_Nummer, FormatNummer, "Vorlage_Rechnung.rtf")
    End Sub


    ''' <summary>
    ''' Opens a textfle and replaces the tags with the data from the database
    ''' Used for HTML Outlookmails too!
    ''' </summary>
    ''' <param name="Vorgangtyp"></param>
    ''' <param name="VorgangNummer"></param>
    ''' <param name="DateinameVorlage">Vorlage</param>
    ''' <param name="Silent">true -> File inWord öffnen</param>
    ''' <returns>Liefert Dateiname der erstellten Datei oder Nothing wenn ein Fehler</returns>
    ''' <remarks></remarks>
    Public Function OpenAusdruck_InWord_Filename_RTF(
        VorgangTyp As String,
        VorgangNummer As String,
        DateinameVorlage As String,
        Optional Silent As Boolean = False,
        Optional PositionStatus As String = "",
        Optional Ziel As String = "")

        Try
            Dim rsVorgang = OpenRecordsetInMemory(GetRecSource(VorgangTyp, VorgangNummer))

            If rsVorgang.Rows.Count = 0 Then
                If Not Silent Then
                    MsgBox("Vorgang " & VorgangTyp & "-" & VorgangNummer & " ist nicht vorhanden!", vbCritical)
                End If
                Return False
            End If

            Dim Vorgangbezeichnung As String
            Dim sql As String

            Vorgangbezeichnung = intrasell.vorgang.GetDruckForType(VorgangTyp)

            Dim stornoInfo As String = intrasell.vorgang.GetVorgangEigenschaft(VorgangTyp, VorgangNummer, "STORNO")
            Dim betreuer As String = ""
            Try
                betreuer = GetBetreuer(VorgangTyp, VorgangNummer)
            Catch ex As Exception
                HandleAppError(ex, Silent:=True)
            End Try

            'Anzahl Positionen
            sql = $"select count(*) as pos from `buchVorgang-Artikel` va where typ='{VorgangTyp}' and nummer={VorgangNummer}"
            If PositionStatus <> "" Then
                sql += " and " & PositionStatus
            End If
            Dim Positionen = CInt(FirstRow(sql))
            Dim fileContent As String = ReadFileContent(DateinameVorlage, Positionen)

            Dim rs = rsVorgang.Rows(0)
            Dim idnr As Integer = rs("idnr")

            If CheckIfWeitereVorhanden(idnr, VorgangTyp) > 0 AndAlso VorgangTyp = VorgangTypen.LI Then
                If MsgBox("Es sind weitere Adressdaten (für Lieferungen) vorhanden! Möchten Sie diese verwenden?", vbYesNo) = vbYes Then
                    Dim selectedIdNr As String = SelectEineWeitereAdresse(VorgangTyp, VorgangNummer)
                    rsVorgang = OpenRecordsetInMemory(GetRecSource_Weitere(VorgangTyp, VorgangNummer, selectedIdNr))
                    rs = rsVorgang.Rows(0)
                End If
            End If

            '#Region "Noch nicht umgestellt"
            'Falls ein Kunde es noch nicht umgestellt hat 
            If Not fileContent.Contains("[MWST_Tabelle]") Then
                fileContent = Replace(fileContent, "[MWST]", "[MWST]" + vbCrLf + "[MWST_Tabelle]")
            End If

            If Not fileContent.Contains("[Storno]") Then
                fileContent = fileContent & vbNewLine & "[Storno]"
            End If

            If Not fileContent.Contains("[Zustellzeit]") AndAlso fileContent.Contains("[Transportmethode]") Then
                fileContent = fileContent.Replace("[Transportmethode]", "[Transportmethode] [Zustellzeit]")
            End If
            '#End Region

            ErsetzePOSCommands(fileContent)

            'Vorgangsdaten
            fileContent = Replace(fileContent, "[Drucker]", Ziel, , , CompareMethod.Text)
            fileContent = Replace(fileContent, "[Idnr]", rs("Idnr") & "", , , CompareMethod.Text)
            fileContent = Replace(fileContent, "[Kundennummer]", rs("Idnr") & "", , , CompareMethod.Text)

            'DONE 20. Wenn Kunde ID = 1 dann sollten die Kundeninformationen nicht gedruckt werden.
            If rs("Idnr") <= 1 Then 'Barkunde ' dann werden keine Kundeninfos gedruckt
                fileContent = Replace(fileContent, "[Firma]", "", , , CompareMethod.Text)
                fileContent = Replace(fileContent, "[name]", "", , , CompareMethod.Text)
                fileContent = Replace(fileContent, "[strasse]", "", , , CompareMethod.Text)
                fileContent = Replace(fileContent, "[plz ort]", "", , , CompareMethod.Text)
                fileContent = Replace(fileContent, "[UID]", "", , , CompareMethod.Text)
                fileContent = Replace(fileContent, "[Email]", "", , , CompareMethod.Text)
                fileContent = Replace(fileContent, "[Tel]", "", , , CompareMethod.Text)
                fileContent = Replace(fileContent, "[Mobil]", "", , , CompareMethod.Text)
                fileContent = Replace(fileContent, "[Anrede]", "", , , CompareMethod.Text)
                fileContent = Replace(fileContent, "[AnredeTitel]", "", , , CompareMethod.Text)
            Else
                fileContent = Replace(fileContent, "[Firma]", rs("firma") & "", , , CompareMethod.Text)
                fileContent = Replace(fileContent, "[name]", rs("namen") & "", , , CompareMethod.Text)
                fileContent = Replace(fileContent, "[strasse]", rs("adresse") & "", , , CompareMethod.Text)
                fileContent = Replace(fileContent, "[plz ort]", rs("plzort") & "", , , CompareMethod.Text)
                fileContent = Replace(fileContent, "[UID]", rs("UID") & "", , , CompareMethod.Text)
                fileContent = Replace(fileContent, "[Email]", rs("Email") & "", , , CompareMethod.Text)
                fileContent = Replace(fileContent, "[Tel]", rs("Tel") & "", , , CompareMethod.Text)
                fileContent = Replace(fileContent, "[Mobil]", rs("Mobil") & "", , , CompareMethod.Text)
                fileContent = Replace(fileContent, "[Anrede]", rs("Anrede") & "", , , CompareMethod.Text)
                fileContent = Replace(fileContent, "[AnredeTitel]", rs("AnredeTitel") & "", , , CompareMethod.Text)
            End If


            fileContent = Replace(fileContent, "[Vorgangbezeichnung]", Vorgangbezeichnung, , , CompareMethod.Text)
            fileContent = Replace(fileContent, "[Typ]", VorgangTyp, , , CompareMethod.Text)
            fileContent = Replace(fileContent, "[Nummer]", VorgangTyp & "-" & VorgangNummer, , , CompareMethod.Text)
            fileContent = Replace(fileContent, "[Titel]", VorgangTyp & "-" & VorgangNummer, , , CompareMethod.Text)
            fileContent = Replace(fileContent, "[Datum]", rs("Datum") & "", , , CompareMethod.Text)
            fileContent = Replace(fileContent, "[DatumF]", Format(rs("Datum"), "dd.MM.yyyy") & "", , , CompareMethod.Text)
            fileContent = Replace(fileContent, "[Betreuer]", betreuer, , , CompareMethod.Text)
            fileContent = Replace(fileContent, "[Status]", rs("Status") & "", , , CompareMethod.Text)

            fileContent = Replace(fileContent, "[Zahlungsbedingung]", rs("ZahlungsBedingung") & "", , , CompareMethod.Text)
            fileContent = Replace(fileContent, "[Zahlungsmethode]", rs("ZahlungsMethode") & "", , , CompareMethod.Text)
            fileContent = Replace(fileContent, "[Transportmethode]", rs("TransportMethode") & "", , , CompareMethod.Text)
            fileContent = Replace(fileContent, "[Notiz]", rs("Notiz") & "", , , CompareMethod.Text)
            fileContent = Replace(fileContent, "[Woher]", rs("Woher") & "", , , CompareMethod.Text)
            'Woher 
            Dim Woher_System = ""
            If fileContent.Contains("[Woher_System]") Then
                Woher_System = GetWoherSystem(VorgangTyp, VorgangNummer)
            End If
            fileContent = Replace(fileContent, "[Woher_System]", Woher_System & "", , , CompareMethod.Text)
            fileContent = Replace(fileContent, "[System]", rs("System") & "", , , CompareMethod.Text)
            fileContent = Replace(fileContent, "[Zustellzeit]", rs("Zustellzeit") & "", , , CompareMethod.Text)
            fileContent = Replace(fileContent, "[Bestätigte Zeit]", rs("ZustellzeitBestaetigt") & "", , , CompareMethod.Text)

            fileContent = Replace(fileContent, "[Wohin]", rs("Woher") & "", , , CompareMethod.Text)
            fileContent = Replace(fileContent, "[Netto]", FormatNumber(rs("summe"), 2))
            fileContent = Replace(fileContent, "[MWST]", FormatNumber(rs("summeBrutto") - rs("summe"), 2))
            fileContent = Replace(fileContent, "[Total]", FormatNumber(rs("summeBrutto"), 2))

            If fileContent.Contains("[MWST_Tabelle]") Then
                Dim MWST_Tabelle As String = MWSTTabelle(VorgangTyp, VorgangNummer)
                fileContent = Replace(fileContent, "[MWST_Tabelle]", MWST_Tabelle) 'hier drucken wir alle MWST Klassen aus
            End If

            fileContent = Replace(fileContent, "[Storno]", stornoInfo & "", , , CompareMethod.Text)

            '#Region "Vorgang Positionen"

            'copy the artikel line times as needed
            sql = $"SELECT va.ID, va.Nummer, va.Typ,  va.ArtNr, va.Stk, va.EKPreis, va.Preis_Netto, va.Preis_Brutto, va.MWST, va.ArtikelIdentifikation, 
                        va.Bezeichnung, va.LieferantNr, va.Status, va.Referenz, va.Packung, va.Herkunft, va.Incoterm, va.Spezifikation, va.Zeitpunkt, va.ErstelltAm, 
                        a.Beschreibung, a.EAN, k.Name as Kategorie, a.Einheit as Einheit
                    FROM `buchVorgang-Artikel` va 
                    LEFT JOIN grArtikel a ON va.ArtNr=a.ArtNr 
                    LEFT JOIN `grArtikel-Kategorien` k ON a.ArtKatNr = k.ArtKatNr
                    WHERE va.Typ='{VorgangTyp}' and va.Nummer={VorgangNummer}"

            If PositionStatus <> "" Then
                sql += " and " & PositionStatus
            End If
            If VarValue_Default(PRINT_SORTIERUNG_KATEGORIE, "true") Then
                sql += " order by k.order, k.Name, va.Bezeichnung"
            Else
                sql += " order by va.Id"
            End If

            Dim RsArtikel = OpenRecordsetInMemory(sql)

            For Each RsArt In RsArtikel.Rows
                Dim StkDec As Decimal = RsArt("Stk")
                Dim StkKueche As Decimal = 0
                Try
                    If PositionStatus <> "" AndAlso Not IsDBNull(RsArt("Packung")) Then
                        If CStr(RsArt("Packung")).Contains(".") Then
                            StkKueche = CStr(RsArt("Packung")).Split(".")(0)
                        Else
                            StkKueche = RsArt("Packung")
                        End If

                        StkDec -= StkKueche
                    End If
                Catch ex As Exception
                    'Ignore
                End Try
                Dim Kategorie = RsArt("Kategorie") : If IsNull(Kategorie) Then Kategorie = ""
                Dim Bezeich = Pad(RsArt("Bezeichnung") & "", VarValue_Default(BARRECHNUNG_BEZEICHNUNG_LAENGE, "20"))
                Dim BezeichAlles = RsArt("Bezeichnung")
                Dim Stk = Format(StkDec, "N")
                Dim StkX = Format(StkDec, "F0")
                Dim IstZutat = (RsArt("Preis_Brutto") = 0) Or RsArt("Bezeichnung").ToString.StartsWith("+") Or RsArt("Bezeichnung").ToString.StartsWith("mit ") Or RsArt("Bezeichnung").ToString.StartsWith("extra ")
                Dim PreisNetto = FormatNumber(RsArt("Preis_Netto"), 2)
                Dim PreisBrutto = FormatNumber(RsArt("Preis_Brutto"), 2)
                Dim PreisNettoXStk = FormatNumber(RsArt("Preis_Netto") * StkDec, 2)
                Dim PreisBruttoXStk = FormatNumber(RsArt("Preis_Brutto") * StkDec, 2)
                Dim EAN = RsArt("EAN")
                Dim Spezifikation = RsArt("Spezifikation")
                Dim Einheit = RsArt("Einheit")
                If IsDBNull(Einheit) Then
                    Einheit = ""
                End If
                If IsDBNull(Spezifikation) Then Spezifikation = ""
                If IstZutat Then
                    Stk = "   + "
                    StkX = "   + "
                    If (RsArt("Preis_Brutto") = 0) Then
                        PreisNetto = ""
                        PreisBrutto = ""
                        PreisBruttoXStk = ""
                        EAN = "" 'Keine EAN Nr für Zutaten
                    End If
                    'Bezeich = Printing.ClassPrinter.PRINT_MODE_FONT_UNDERLINE_On + Bezeich + Printing.ClassPrinter.PRINT_MODE_FONT_UNDERLINE_Off
                    'BezeichAlles = Printing.ClassPrinter.PRINT_MODE_FONT_UNDERLINE_On + BezeichAlles + Printing.ClassPrinter.PRINT_MODE_FONT_UNDERLINE_Off
                    Kategorie = "" 'Kategorie bei Zutaten nicht andrucken
                End If

                fileContent = Replace(fileContent, "[Stk]", Stk, 1, 1)
                fileContent = Replace(fileContent, "[StkX]", StkX, 1, 1)
                fileContent = Replace(fileContent, "[ArtNR]", RsArt("ArtNR"), 1, 1)
                fileContent = Replace(fileContent, "[EAN]", EAN, 1, 1)
                fileContent = Replace(fileContent, "[Bezeichnung]", Bezeich, 1, 1)
                fileContent = Replace(fileContent, "[Bezeichnung_Alles]", BezeichAlles, 1, 1)
                fileContent = Replace(fileContent, "[Beschreibung]", RsArt("Beschreibung") & "", 1, 1)
                fileContent = Replace(fileContent, "[Preis]", PreisNetto, 1, 1)
                fileContent = Replace(fileContent, "[PreisXStk]", PreisNettoXStk, 1, 1)
                fileContent = Replace(fileContent, "[PreisBrutto]", PreisBrutto, 1, 1)
                fileContent = Replace(fileContent, "[PreisBruttoXStk]", PreisBruttoXStk, 1, 1)
                fileContent = Replace(fileContent, "[Kategorie]", Kategorie, 1, 1)
                fileContent = Replace(fileContent, "[Einheit]", Einheit, 1, 1)
                If String.IsNullOrEmpty(Spezifikation) Then
                    fileContent = Replace(fileContent, "[Spezifikation]", "", 1, 1)
                Else
                    Dim Spezifikationen = Replace(Spezifikation, ";", Chr(13) & Chr(10))
                    fileContent = Replace(fileContent, "[Spezifikation]", Chr(13) & Chr(10) & Spezifikationen & Chr(13) & Chr(10), 1, 1)
                End If

            Next

            '#End Region

            'set Eigenschaften
            sql = "select Name, Value from buchVorgaengeEigenschaften where Nummer  = " & VorgangNummer & " and vorgangtyp like '" & VorgangTyp & "'"
            Dim rsEigTable = OpenRecordsetInMemory(sql)
            For Each rsEig In rsEigTable.Rows
                fileContent = Replace(fileContent, "[" & rsEig("Name") & "]", rsEig("Value") & "", , , CompareMethod.Text)
                fileContent = Replace(fileContent, "[" & rsEig("Name") & "]", rsEig("Value") & "")
            Next

            'Variablen 
            fileContent = Replace(fileContent, "[Firma_Name]", VarValue_Default(Variablen.FIRMA_NAME, "TEST"), , , CompareMethod.Text)
            fileContent = Replace(fileContent, "[Firma_Adresse]", VarValue_Default(Variablen.FIRMA_ADRESSE, "Landstrasse 1"), , , CompareMethod.Text)
            fileContent = Replace(fileContent, "[Firma_PLZ]", VarValue_Default(Variablen.FIRMA_PLZ, "4020"), , , CompareMethod.Text)
            fileContent = Replace(fileContent, "[Firma_Ort]", VarValue_Default(Variablen.FIRMA_ORT, "Linz"), , , CompareMethod.Text)
            fileContent = Replace(fileContent, "[Firma_Tel]", VarValue_Default(Variablen.FIRMA_TEL, "0732 / 123 123"), , , CompareMethod.Text)
            fileContent = Replace(fileContent, "[Firma_Email]", VarValue_Default(Variablen.FIRMA_EMAIL, "office@griton.net"), , , CompareMethod.Text)
            fileContent = Replace(fileContent, "[Firma_Domain]", VarValue_Default(Variablen.FIRMA_DOMAIN, "www.griton.net"), , , CompareMethod.Text)
            fileContent = Replace(fileContent, "[Firma_UID]", VarValue_Default(Variablen.FIRMA_UID, "ATU0000000"), , , CompareMethod.Text)
            fileContent = Replace(fileContent, "[Firma_Oeffnungszeiten]", VarValue_Default(Variablen.Firma_Oeffnungszeiten, "MO-FR 09:00 18:00"), , , CompareMethod.Text)
            fileContent = Replace(fileContent, "[Firma_Bankverbindung]", VarValue_Default(Variablen.Firma_Bankverbindung, "IBAN: 1920 0000 0000 0000 0000"), , , CompareMethod.Text)

            fileContent = Replace(fileContent, "[KasseId]", VarValue_Default("KasseId", "Kassa1"), , , CompareMethod.Text)

            fileContent = Replace(fileContent, "Zeit: [Zustellzeit]", "",,, CompareMethod.Text)
            fileContent = Replace(fileContent, "[Zustellzeit]", "", ,, CompareMethod.Text)
            fileContent = Replace(fileContent, "[Bargeld_Gegeben]", "",,, CompareMethod.Text)
            fileContent = Replace(fileContent, "[Bargeld_Resto]", "", , , CompareMethod.Text) 'Remove wenn Bankomat


            ErsetzeGutscheinPlatzhalter(fileContent)

            fileContent = Trim(fileContent)

            '#Region "SAVE FILE"

            If Ziel <> "" Then
                Ziel += "_" & Ziel
            End If
            Ziel += "_" & Format(Now, "HHmmss")
            Dim Erweiterung = DateinameVorlage.Split(".")(1)
            Dim SaveAsFilename As String = Path.Combine(DbFolder(), VorgangTyp & "_" & VorgangNummer & Ziel & "." & Erweiterung)
            'delete file if exiss 
            If File.Exists(SaveAsFilename) Then
                File.Delete(SaveAsFilename)
            End If

            Using outfile As New StreamWriter(SaveAsFilename, True)
                outfile.Write(fileContent)
                outfile.Close()
            End Using

            '#End Region

            If Not Silent Then 'OPEN FILE IN WORD
                WordApplicationOpen(fileName:=SaveAsFilename)
            End If
            Return SaveAsFilename

        Catch ex As Exception
            HandleAppError(ex)
            Return Nothing
        End Try
    End Function

#Region "Hilfsfunktionen"
    Private Sub ReplaceInWordOnce(App As Object, what As String, withValue As String)
        Try
            App.Selection.MoveUp(Unit:=7, Count:=4)
            App.Selection.Find.ClearFormatting()
            App.Selection.Find.replacement.ClearFormatting()
            With App.Selection.Find
                .Text = what
                .replacement.Text = withValue
                .Forward = True
                .Wrap = 1
                .Format = False
                .MatchCase = False
                .MatchWholeWord = False
                .MatchWildcards = False
                .MatchSoundsLike = False
                .MatchAllWordForms = False
            End With
            App.Selection.Find.execute(Replace:=1) 'wdReplaceAll
        Catch ex As Exception
            Debug.Print("Cannot replace " & what)
        End Try

    End Sub


    ''' <summary>
    ''' Hilfe Funktion, liest die angegebene Datei und erstellt zusätzliche zeilen wenn [ArtNR] vorhanden ist
    ''' </summary>
    ''' <param name="DateinameVorlage"></param>
    ''' <param name="AnzahlPositionen"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function ReadFileContent(DateinameVorlage As String, AnzahlPositionen As Integer) As String
        'read RTF content
        Dim VorlageFilename As String = DateinameVorlage

        If Not File.Exists(VorlageFilename) Then
            VorlageFilename = DbFolder() & DateinameVorlage
        End If

        If Not File.Exists(VorlageFilename) Then
            Return $"Vorlage wurde nicht gefunden. Pfad: {VorlageFilename}"
        End If

        Dim fileContent As New StringBuilder, fileLine As String

        Using infile As New StreamReader(VorlageFilename)
            'Open VorlageFilename For Input As #1
            While Not infile.EndOfStream
                fileLine = infile.ReadLine

                fileContent.AppendLine(fileLine)

                'add lines für jede Position
                If InStr(fileLine, "[ArtNR]") > 0 _
                    OrElse InStr(fileLine, "[EAN]") > 0 _
                    OrElse InStr(fileLine, "[Stk]") > 0 _
                    OrElse InStr(fileLine, "[StkX]") > 0 _
                    OrElse InStr(fileLine, "[Bezeichnung]") > 0 Then 'das ist eine Positionszeile
                    If Not fileLine.Contains("[Spezifikation]") Then
                        fileLine += "[Spezifikation]"
                        fileContent.Append("[Spezifikation]")
                    End If
                    Dim i As Integer
                    For i = 1 To (AnzahlPositionen - 1)
                        fileContent.AppendLine(fileLine)
                    Next
                End If
            End While
        End Using

        Return fileContent.ToString
    End Function



    ''' <summary>
    ''' Adds spaces to the required length
    ''' </summary>
    ''' <param name="stringToPad"></param>
    ''' <param name="length"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function Pad(stringToPad As String, length As Integer)
        Dim i As Integer
        If Len(stringToPad) < length Then
            Dim missingChars : missingChars = length - Len(stringToPad)
            For i = 1 To missingChars
                stringToPad &= " "
            Next
            Return stringToPad
        Else ' more the length signs
            Return Left(stringToPad, length - 3) & "..."
        End If
    End Function


    Function SaubernFuerUI(content As String) As String
        content = content.Replace(Chr(0), "")
        content = content.Replace(Chr(1), "")
        Return content
    End Function

    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="fileContent"></param>
    ''' <remarks></remarks>
    Sub ErsetzePOSCommands(ByRef fileContent As String)
        'POS Commandos

        fileContent = Replace(fileContent, "[Reset]", Printing.ClassPrinter.INIT, , , CompareMethod.Text)
        fileContent = Replace(fileContent, "[Kassenschublade_oeffnen]", Printing.ClassPrinter.OPEN_CASSA, , , CompareMethod.Text)
        fileContent = Replace(fileContent, "[Fett_ein]", Printing.ClassPrinter.BoldOn, , , CompareMethod.Text)
        fileContent = Replace(fileContent, "[Fett_aus]", Printing.ClassPrinter.BoldOff, , , CompareMethod.Text)
        fileContent = Replace(fileContent, "[Doppel_ein]", Printing.ClassPrinter.DoubleOn, , , CompareMethod.Text)
        fileContent = Replace(fileContent, "[Doppel_aus]", Printing.ClassPrinter.DoubleOff, , , CompareMethod.Text)
        fileContent = Replace(fileContent, "[PaperCut]", Printing.ClassPrinter.TEAR, , , CompareMethod.Text)
        fileContent = Replace(fileContent, "[Zentriere]", Printing.ClassPrinter.JUSTIFICATION_CENTER, , , CompareMethod.Text)
        fileContent = Replace(fileContent, "[Links]", Printing.ClassPrinter.JUSTIFICATION_LEFT, , , CompareMethod.Text)
        fileContent = Replace(fileContent, "[Rechts]", Printing.ClassPrinter.JUSTIFICATION_RIGHT, , , CompareMethod.Text)
        fileContent = Replace(fileContent, "[Buzzer]", Printing.ClassPrinter.Buzzer, , , CompareMethod.Text)
        fileContent = Replace(fileContent, "[FontA]", Printing.ClassPrinter.PRINT_MODE_FONT_A, , , CompareMethod.Text)
        fileContent = Replace(fileContent, "[FontB]", Printing.ClassPrinter.PRINT_MODE_FONT_B, , , CompareMethod.Text)
        fileContent = Replace(fileContent, "[DoppelHoehe]", Printing.ClassPrinter.PRINT_MODE_FONT_DOUBLE_HEIGHT, , , CompareMethod.Text)
        fileContent = Replace(fileContent, "[DoppelBreite]", Printing.ClassPrinter.PRINT_MODE_FONT_DOUBLE_WIDTH, , , CompareMethod.Text)
        fileContent = Replace(fileContent, "[Unterstreichen_ein]", Printing.ClassPrinter.PRINT_MODE_FONT_UNDERLINE_On, , , CompareMethod.Text)
        fileContent = Replace(fileContent, "[Unterstreichen_aus]", Printing.ClassPrinter.PRINT_MODE_FONT_UNDERLINE_Off, , , CompareMethod.Text)

        fileContent = Replace(fileContent, "[Groesse0]", Printing.ClassPrinter.Size0, , , CompareMethod.Text)
        fileContent = Replace(fileContent, "[Groesse1]", Printing.ClassPrinter.Size1, , , CompareMethod.Text)
        fileContent = Replace(fileContent, "[Groesse2]", Printing.ClassPrinter.Size2, , , CompareMethod.Text)
        fileContent = Replace(fileContent, "[Groesse3]", Printing.ClassPrinter.Size3, , , CompareMethod.Text)
        fileContent = Replace(fileContent, "[Groesse4]", Printing.ClassPrinter.Size4, , , CompareMethod.Text)
        fileContent = Replace(fileContent, "[Groesse5]", Printing.ClassPrinter.Size5, , , CompareMethod.Text)
        fileContent = Replace(fileContent, "[Groesse6]", Printing.ClassPrinter.Size6, , , CompareMethod.Text)
        fileContent = Replace(fileContent, "[GermanCodePage]", Printing.ClassPrinter.GERMAN_CODE_PAGE, , , CompareMethod.Text)
    End Sub

    Public Sub KillWord()
        Dim p As Process
        For Each p In Process.GetProcessesByName("WINWORD")
            Try
                WriteLog("Kill WINWORD")
                p.Kill()
                p.WaitForExit()
            Catch winException As Win32Exception
                ' process was terminating or can't be terminated - deal with it
                WriteLog(winException.Message)
            Catch invalidException As InvalidOperationException
                WriteLog(invalidException.Message)
            End Try
        Next
    End Sub

    Private Sub WordApplicationOpen(fileName As String)
        Dim App 'As Application
        App = CreateObject("Word.Application")
        App.Visible = True
        App.Documents.Open(fileName:=fileName, ConfirmConversions:=False, readonly:=False, AddToRecentFiles:=False) ', Format:=0
        App.ActiveWindow.SetFocus()
    End Sub

#End Region


#Region "Daten"


    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="VorgangTyp"></param>
    ''' <param name="VorgangNummer"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetRecSource(VorgangTyp As String, VorgangNummer As Integer) As String
        Return $"SELECT v.Typ as VorgangTyp, v.Nummer, v.Status, a.Idnr,  concat(ifnull(a.name,''), ' ', ifnull(a.Vorname,'')) as Namen, 
                        a.Firma, a.Adresse, concat(a.plz, ' ' , a.ort)  AS PLZOrt, 
                        (select name from grLand where idnr = a.Land) as Land, a.UID, a.Tel, a.Mobil, a.Email, 
                        Sum(Stk*Preis_Netto) AS Summe, Sum(Stk*Preis_Brutto) AS SummeBrutto,  Sum(Stk*Preis_Brutto)-Sum(Stk*Preis_Netto) as SummeMWST , v.Datum,
                        v.Notiz as Notiz,  v.KundNr, v.ZahlungsBedingung,
                        v.Zahlungsmethode, v.TransportMethode,  v.Notiz, Woher, Wohin, a.Anrede , concat(a.Anrede,' ', a.Titel) as AnredeTitel,
                        v.KundNr1, v.KundNr2, v.MitarbeiterNr, v.System, v.Zustellzeit, v.ZustellzeitBestaetigt
                        FROM ofAdressen as a RIGHT JOIN buchVorgang as v ON a.IDNR = v.KundNr
                        INNER JOIN `buchVorgang-Artikel` as va  ON v.Nummer = va.Nummer  and v.Typ = va.Typ 
                         LEFT JOIN `ofAdressen-Weitere` as a1 ON a1.IDNR = v.KundNr1 
                         LEFT JOIN `ofAdressen-Weitere` as a2 ON a1.IDNR = v.KundNr2 
                         WHERE v.nummer={VorgangNummer} and v.typ='{VorgangTyp}'
                        GROUP BY  v.Typ, v.Nummer, a.Idnr, concat(a.Name, ' ', a.Vorname), a.Firma, a.Adresse,
                         v.Datum, v.Notiz, v.KundNr, v.ZahlungsBedingung, v.Zahlungsmethode, v.TransportMethode,
                         v.Notiz, v.Woher, v.Wohin, a.Anrede, a.Titel, a.Tel, a.Mobil, a.Email, v.KundNr1, v.KundNr2, v.MitarbeiterNr, v.System, v.Zustellzeit, v.ZustellzeitBestaetigt;"
    End Function


    ''' <summary>
    ''' liefet den recordsource wenn eine weitere adresse verwendet wird
    ''' </summary>
    ''' <param name="Vorgangtyp"></param>
    ''' <param name="Vorgang_Nummer"></param>
    ''' <param name="selectedId"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetRecSource_Weitere(Vorgangtyp, Vorgang_Nummer, selectedId)
        Return IntraSellVorgaengeAusdruck.getRecSource_Weitere(Vorgangtyp, Vorgang_Nummer, selectedId)
    End Function


    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="tablename"></param>
    ''' <param name="where"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetRecSource_Address(tablename As String, where As String) As String
        Return $"Select a.IDNR, a.Vorname, a.Name, concat(a.Name, ' ', a.Vorname) as Namen , a.Firma, a.Adresse, concat(a.plz, ' ' , a.ort) AS PLZOrt, 
                (SELECT name from grLand WHERE idnr = a.Land) As Land, a.UID, a.Tel, a.Fax, a.Email, a.UID 
                FROM {tablename} a WHERE {where}"
    End Function

    ''' <summary>
    ''' prueft ob eine weitere adresse vorhanden ist
    ''' </summary>
    ''' <param name="IDNR"></param>
    ''' <param name="Vorgangtyp"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function CheckIfWeitereVorhanden(IDNR, Vorgangtyp) As Integer
        Dim rs As DataTable
        Dim Sql As String = $"select count(*) as cnt from [ofAdressen-Weitere] where idnr={IDNR} AND Typ='{Vorgangtyp}'"
        rs = OpenRecordsetInMemory(Sql)
        If rs.Rows.Count > 0 Then
            Return rs.Rows(0)("cnt")
        Else
            Return 0
        End If
    End Function

    ''' <summary>
    ''' Liefert die Auswahl einer weiteren Adresse
    ''' </summary>
    ''' <param name="Vorgangtyp"></param>
    ''' <param name="VorgangNummer"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function SelectEineWeitereAdresse(VorgangTyp, VorgangNummer) As Integer

        'Falls mehrere weitere adressen vom gleichen Vorgangtyp definiert sind dann abfragen welche zu verwenden ist
        Dim IDNR As String = FirstRow("select KundNr from buchVorgang where Typ='" & VorgangTyp & "' and Nummer=" & VorgangNummer)
        Dim sqlMehrereWeitereAdressen As String = $"select * from [ofAdressen-Weitere] where idnr = {IDNR} and Typ like '{VorgangTyp}'"
        Dim rsMehrereWeitereAdressen = OpenRecordsetInMemory(sqlMehrereWeitereAdressen)

        Dim RecordCount = rsMehrereWeitereAdressen.Rows.Count

        If RecordCount = 1 Then
            Return rsMehrereWeitereAdressen.Rows(0)("ID")
        ElseIf RecordCount > 1 Then
            currentIDNR = IDNR
            Return selectedOfAdressenWeitereId
        End If
        Return IDNR
    End Function

    Function GetBetreuer(Typ As String, Nummer As String)
        Try
            Return FirstRow("Select name from ofMitarbeiter where idnr=(select MitarbeiterNr from buchVorgang where  typ='" & Typ & "' and nummer=" & Nummer & ")")
        Catch ex As Exception
            'Ignore
        End Try
        Return ""
    End Function

    Private Function GetWoherSystem(Typ As String, Nummer As Integer) As String
        Try
            Dim woher As String
            woher = FirstRow($"select woher from buchVorgang where typ='{Typ}' and Nummer={Nummer}")

            If woher.StartsWith(VorgangTypen.AU) AndAlso IsNumeric(woher.Replace(VorgangTypen.AU, "")) Then
                woher = FirstRow("select woher from buchVorgang where typ='AU' and Nummer=" & woher.Replace(VorgangTypen.AU, ""))
            End If
            If woher.Contains(":") Then
                Dim tokens = woher.Split(":")
                Return tokens(0)
            End If
            Return woher
        Catch ex As Exception
            WriteLog($"Ermittlung Woher_System fehlgeschlagen! Ursache: {ex.Message}")
            Return ""
        End Try
    End Function

#End Region

End Module
