Imports System.Data.SqlClient

Public Class EABelegPositionen
    Dim SQL As New SQL
    Property Mandant As String = "XXXX"
    Property Niederlassung As String = "XXX"
    Property Benutzer As Integer = -1
    Property BelegDat As DateTime = Now
    Property BelegNr As Integer = -1
    Property PreislistenPos As Integer = -1
    Property LeistungsNr As Integer = -1
    Property LeistungsBez As String = ""
    Property Leistungsbeschreibung As String = ""
    Property Abfertigungsart As Object = Nothing 'int
    Property PreislistenNr As Object = Nothing 'int
    Property PreistabellenNr As Object = Nothing 'int
    Property Preis As Double = 0
    Property TW1 As String = Nothing
    Property TW2 As String = Nothing
    Property Anzahl As Integer = 1
    Property Anzahlmultiplikator As Integer = 0
    Property Betrag As Double = 0
    Property Preisbindung As Integer = 0
    Property EABPo_Zeitstempel As DateTime = Now

    Property FilialenNr As Integer = -1
    Property AbfertigungsNr As Integer = -1

    Public Function SAVE() As Boolean 'obj As Object, tablename As String, where As String) As Boolean
        Dim list As List(Of VERAG_PROG_ALLGEMEIN.MyListItem2) = getParameterList()

        Dim sqlstr = " BEGIN TRAN IF EXISTS(SELECT * FROM tblEABelegposition WITH(updlock,serializable) WHERE [Mandant]=@Mandant AND Niederlassung=@Niederlassung AND Benutzer=@Benutzer AND BelegNr=@BelegNr AND CAST(BelegDat as date)=@BelegDat AND PreislistenPos=@PreislistenPos AND LeistungsNr=@LeistungsNr AND FilialenNr=@FilialenNr AND AbfertigungsNr=@AbfertigungsNr) " &
                       " BEGIN " & getUpdateCmd() & " END " &
                       " Else " &
                       " BEGIN " & getInsertCmd() & " END " &
                       " commit tran "
        'MsgBox(sqlstr)
        Return SQL.doSQL(sqlstr, "FMZOLL", , list)
    End Function


    Public Sub LOAD(Mandant As String, Niederlassung As String, Benutzer As Integer, BelegNr As Integer, BelegDat As Date, PreislistenPos As Integer, LeistungsNr As Integer) ' As EABeleg
        Try
            Using conn As SqlConnection = cSqlDb.GetNewOpenConnectionFMZOLL()
                Using cmd As New SqlCommand("SELECT * FROM [tblEABelegposition] WHERE [Mandant]=@Mandant AND Niederlassung=@Niederlassung AND Benutzer=@Benutzer AND BelegNr=@BelegNr AND CAST(BelegDat as date)=@BelegDat AND PreislistenPos=@PreislistenPos AND LeistungsNr=@LeistungsNr  AND FilialenNr=@FilialenNr AND AbfertigungsNr=@AbfertigungsNr", conn)
                    cmd.Parameters.AddWithValue("@Mandant", Mandant)
                    cmd.Parameters.AddWithValue("@Niederlassung", Niederlassung)
                    cmd.Parameters.AddWithValue("@Benutzer", Benutzer)
                    cmd.Parameters.AddWithValue("@BelegNr", BelegNr)
                    cmd.Parameters.AddWithValue("@BelegDat", BelegDat)
                    cmd.Parameters.AddWithValue("@PreislistenPos", PreislistenPos)
                    cmd.Parameters.AddWithValue("@LeistungsNr", LeistungsNr)

                    Dim dr = cmd.ExecuteReader()
                    If dr.Read Then
                        Me.Mandant = cSqlDb.checkNullStr(dr.Item("Mandant"))
                        Me.Niederlassung = cSqlDb.checkNullStr(dr.Item("Niederlassung"))
                        Me.Benutzer = cSqlDb.checkNullStr(dr.Item("Benutzer"))
                        Me.BelegDat = cSqlDb.checkNullDate(dr.Item("BelegDat"))
                        Me.BelegNr = cSqlDb.checkNullInteger(dr.Item("BelegNr"))

                        Me.PreislistenPos = cSqlDb.checkNullInteger(dr.Item("PreislistenPos"))
                        Me.LeistungsNr = cSqlDb.checkNullInteger(dr.Item("LeistungsNr"))
                        Me.LeistungsBez = cSqlDb.checkNullStr(dr.Item("LeistungsBez"))
                        Me.Leistungsbeschreibung = cSqlDb.checkNullStr(dr.Item("Leistungsbeschreibung"))
                        Me.Abfertigungsart = cSqlDb.checkNullInteger(dr.Item("Abfertigungsart"))
                        Me.PreislistenNr = cSqlDb.checkNullInteger(dr.Item("PreislistenNr"))
                        Me.PreistabellenNr = cSqlDb.checkNullInteger(dr.Item("PreistabellenNr"))
                        Me.Preis = cSqlDb.checkNulDbl(dr.Item("Preis"))
                        Me.TW1 = cSqlDb.checkNullStr(dr.Item("TW1"))
                        Me.TW2 = cSqlDb.checkNullStr(dr.Item("TW2"))
                        Me.Anzahl = cSqlDb.checkNullInteger(dr.Item("Anzahl"))
                        Me.Anzahlmultiplikator = cSqlDb.checkNullStr(dr.Item("Anzahlmultiplikator"))
                        Me.Betrag = cSqlDb.checkNulDbl(dr.Item("Betrag"))
                        '  MsgBox(cSqlDb.checkNulDbl(dr.Item("Betrag"))

                        Me.Preisbindung = cSqlDb.checkNullBool(dr.Item("Preisbindung"))
                        Me.EABPo_Zeitstempel = cSqlDb.checkNullStr(dr.Item("EABPo_Zeitstempel"))

                        Me.FilialenNr = cSqlDb.checkNullInteger(dr.Item("FilialenNr"))
                        Me.AbfertigungsNr = cSqlDb.checkNullInteger(dr.Item("AbfertigungsNr"))


                    End If
                    dr.Close()
                End Using
            End Using
            'Return Nothing
        Catch ex As Exception
            MsgBox("Fehler in der Funktion '" & System.Reflection.MethodInfo.GetCurrentMethod.Name & "'" & vbNewLine & vbNewLine & ex.Message & vbNewLine & vbNewLine & ex.StackTrace)
        End Try
        '  Return Nothing
    End Sub

    Function getParameterList() As List(Of VERAG_PROG_ALLGEMEIN.MyListItem2)
        Dim list As New List(Of VERAG_PROG_ALLGEMEIN.MyListItem2)
        list.Add(New VERAG_PROG_ALLGEMEIN.MyListItem2("Mandant", Mandant))
        list.Add(New VERAG_PROG_ALLGEMEIN.MyListItem2("Niederlassung", Niederlassung))
        list.Add(New VERAG_PROG_ALLGEMEIN.MyListItem2("Benutzer", Benutzer))
        '  list.Add(New VERAG_PROG_ALLGEMEIN.MyListItem2("BelegDat", BelegDat.ToString("yyyy-MM-dd HH:mm:ss.fff"))) '.ToShortDateString))'("dd.MM.yyyy HH:mm")))
        list.Add(New VERAG_PROG_ALLGEMEIN.MyListItem2("BelegDat", BelegDat.ToShortDateString)) '.ToShortDateString))'("dd.MM.yyyy HH:mm")))
        list.Add(New VERAG_PROG_ALLGEMEIN.MyListItem2("BelegNr", BelegNr))


        '  MsgBox(Mandant)
        ' MsgBox(Niederlassung)
        ' MsgBox(Benutzer)
        'MsgBox(BelegDat)
        '  MsgBox(BelegNr)


        list.Add(New VERAG_PROG_ALLGEMEIN.MyListItem2("PreislistenPos", PreislistenPos))
        list.Add(New VERAG_PROG_ALLGEMEIN.MyListItem2("LeistungsNr", LeistungsNr))
        list.Add(New VERAG_PROG_ALLGEMEIN.MyListItem2("LeistungsBez", LeistungsBez))
        list.Add(New VERAG_PROG_ALLGEMEIN.MyListItem2("Leistungsbeschreibung", Leistungsbeschreibung))
        list.Add(New VERAG_PROG_ALLGEMEIN.MyListItem2("Abfertigungsart", Abfertigungsart))
        list.Add(New VERAG_PROG_ALLGEMEIN.MyListItem2("PreislistenNr", PreislistenNr))
        list.Add(New VERAG_PROG_ALLGEMEIN.MyListItem2("PreistabellenNr", PreistabellenNr))
        list.Add(New VERAG_PROG_ALLGEMEIN.MyListItem2("Preis", Preis))
        list.Add(New VERAG_PROG_ALLGEMEIN.MyListItem2("TW1", TW1))
        list.Add(New VERAG_PROG_ALLGEMEIN.MyListItem2("TW2", TW2))
        list.Add(New VERAG_PROG_ALLGEMEIN.MyListItem2("Anzahl", Anzahl))
        list.Add(New VERAG_PROG_ALLGEMEIN.MyListItem2("Anzahlmultiplikator", Anzahlmultiplikator))
        list.Add(New VERAG_PROG_ALLGEMEIN.MyListItem2("Betrag", Betrag))
        list.Add(New VERAG_PROG_ALLGEMEIN.MyListItem2("Preisbindung", Preisbindung))
        list.Add(New VERAG_PROG_ALLGEMEIN.MyListItem2("FilialenNr", FilialenNr))
        list.Add(New VERAG_PROG_ALLGEMEIN.MyListItem2("AbfertigungsNr", AbfertigungsNr))


        '      list.Add(New VERAG_PROG_ALLGEMEIN.MyListItem2("EABPo_Zeitstempel", EABPo_Zeitstempel))
        'For Each p As System.Reflection.PropertyInfo In Me.GetType().GetProperties()
        'If p.CanRead Then
        'Dim name = p.Name
        '  list.Add(New VERAG_PROG_ALLGEMEIN.MyListItem2(name, p.GetValue(Me, Nothing)))
        ' End If
        ' Next
        Return list
    End Function

    Public Function getUpdateCmd() As String
        Try
            Dim list As List(Of VERAG_PROG_ALLGEMEIN.MyListItem2) = getParameterList()

            Dim str As String = ""
            For Each i In list
                str &= "[" & i.Text & "] = @" & i.Text.Replace("-", "").Replace(" ", "") & ","
            Next
            str = str.Substring(0, str.Length - 1) 'wg. ','
            Return (" UPDATE tblEABelegposition SET " & str & " WHERE [Mandant]=@Mandant AND Niederlassung=@Niederlassung AND Benutzer=@Benutzer AND BelegNr=@BelegNr AND CAST(BelegDat as date)=@BelegDat AND PreislistenPos=@PreislistenPos AND LeistungsNr=@LeistungsNr  AND FilialenNr=@FilialenNr AND AbfertigungsNr=@AbfertigungsNr")

        Catch ex As Exception
            MsgBox("Fehler in der Funktion '" & System.Reflection.MethodInfo.GetCurrentMethod.Name & "'" & vbNewLine & vbNewLine & ex.Message & vbNewLine & vbNewLine & ex.StackTrace)
        End Try
        Return ""
    End Function

    Public Function getInsertCmd() As String
        Try
            Dim list As List(Of VERAG_PROG_ALLGEMEIN.MyListItem2) = getParameterList()
            Dim str As String = ""
            Dim values As String = ""
            For Each i In list
                str &= "[" & i.Text & "],"
                values &= "@" & i.Text.Replace("-", "").Replace(" ", "") & ","
            Next
            str = str.Substring(0, str.Length - 1) 'wg. ','
            values = values.Substring(0, values.Length - 1) 'wg. ','
            Return (" INSERT INTO tblEABelegposition  (" & str & ") VALUES(" & values & ")  ")
        Catch ex As Exception
            MsgBox("Fehler in der Funktion '" & System.Reflection.MethodInfo.GetCurrentMethod.Name & "'" & vbNewLine & vbNewLine & ex.Message & vbNewLine & vbNewLine & ex.StackTrace)
        End Try
        Return ""
    End Function


    Public Function UPDATE() As Boolean 'obj As Object, tablename As String, where As String) As Boolean
        Try
            Dim list As List(Of VERAG_PROG_ALLGEMEIN.MyListItem2) = getParameterList()

            Dim str As String = ""
            For Each i In list
                str &= "[" & i.Text & "] = @" & i.Text.Replace("-", "").Replace(" ", "") & ","
            Next
            str = str.Substring(0, str.Length - 1) 'wg. ','
            Dim sqlstr = (" UPDATE tblEABelegposition SET " & str & " WHERE [Mandant]=@Mandant AND Niederlassung=@Niederlassung AND Benutzer=@Benutzer AND BelegNr=@BelegNr AND CAST(BelegDat as date)=@BelegDat AND PreislistenPos=@PreislistenPos AND LeistungsNr=@LeistungsNr  AND FilialenNr=@FilialenNr AND AbfertigungsNr=@AbfertigungsNr")

            Using cmd As New SqlCommand(sqlstr, cSqlDb.GetNewOpenConnectionFMZOLL())
                For Each i In list
                    cmd.Parameters.AddWithValue("@" & i.Text.Replace("-", "").Replace(" ", "") & "", IIf(i.Value Is Nothing, DBNull.Value, i.Value))
                Next
                cmd.ExecuteNonQuery()
            End Using

            Return True
        Catch ex As Exception
            MsgBox("Fehler in der Funktion '" & System.Reflection.MethodInfo.GetCurrentMethod.Name & "'" & vbNewLine & vbNewLine & ex.Message & vbNewLine & vbNewLine & ex.StackTrace)
        End Try
        Return False
    End Function

    Public Function INSERT() 'obj As Object, tablename As String, Optional conn_art As String = "SDL") As Boolean
        Try
            Dim list As List(Of VERAG_PROG_ALLGEMEIN.MyListItem2) = getParameterList()
            Dim str As String = ""
            Dim values As String = ""
            For Each i In list
                str &= "[" & i.Text & "],"
                values &= "@" & i.Text.Replace("-", "").Replace(" ", "") & ","
            Next
            str = str.Substring(0, str.Length - 1) 'wg. ','
            values = values.Substring(0, values.Length - 1) 'wg. ','
            Dim sqlstr = (" INSERT INTO tblEABelegposition  (" & str & ") VALUES(" & values & ")  ")
            MsgBox(sqlstr)
            Using cmd As New SqlCommand(sqlstr, cSqlDb.GetNewOpenConnectionFMZOLL())
                For Each i In list
                    cmd.Parameters.AddWithValue("@" & i.Text.Replace("-", "").Replace(" ", "") & "", IIf(i.Value Is Nothing, DBNull.Value, i.Value))
                Next
                cmd.ExecuteNonQuery()
            End Using

            Return True
        Catch ex As Exception
            MsgBox("Fehler in der Funktion '" & System.Reflection.MethodInfo.GetCurrentMethod.Name & "'" & vbNewLine & vbNewLine & ex.Message & vbNewLine & vbNewLine & ex.StackTrace)
        End Try
        Return False
    End Function



End Class



Public Class EABeleg
    Dim SQL As New SQL

    Property Mandant As String = "XXXX"
    Property Niederlassung As String = "XXX"
    Property Benutzer As Integer = -1
    Property BelegDat As DateTime = Now
    Property BelegNr As Integer = -1
    Property Belegart As Integer = 1
    Property FilialenNr As Integer = -1
    Property AbfertigungsNr As Integer = -1
    Property Ordnungsbegriff As String = ""
    Property KundenNr As Integer = -1
    Property Name1 As String = ""
    Property Name2 As String = ""
    Property Straße As String = ""
    Property LandKz As String = ""
    Property PLZ As String = ""
    Property Ort As String = ""
    Property UstIdKz As String = ""
    Property UstIdNr As String = ""
    Property Frachtführer As String = ""
    Property FrachtführerKundenNr As Integer = -1
    Property LKW_Kennzeichen As String = ""
    Property Fahrer As String = ""
    Property Passnummer As String = ""
    Property PreislistenNr As String = ""
    Property Steuerschlüssel As String = ""
    Property DruckDatumUhrzeit As Object = Nothing
    Property storniert As Boolean = False
    Property Stornogrund As String = ""
    Property gebucht As Boolean = False
    Property gebuchtStorno As Boolean = False
    Property SgutKompl As Object = Nothing
    Property rksv_id As Integer = Nothing
    Property rksv_BelegId As Integer = Nothing
    Property rksv_BelegIdStorno As Object = Nothing
    Property rksv_jws As String = Nothing
    Property rksv_qr As String = Nothing
    Property rksv_umsatz As Double = Nothing
    Property rksv_aktiv As Boolean = False
    Property Beleg_TYP As String = Nothing
    Property Auftraggeber_KdAuftragsnummer As Object = Nothing
    Property Auftraggeber_Provision As Object = Nothing

    Property LKW_Id As Object = Nothing
    Property LKW_Beleg As Object = Nothing
    Property Benutzer_LG As Object = Nothing


    Property POS As New List(Of EABelegPositionen)

    Public Sub New(Mandant As String, Niederlassung As String, Benutzer As Integer)
        Me.Mandant = Mandant
        Me.Niederlassung = Niederlassung
        Me.Benutzer = Benutzer
    End Sub

    Public Sub New(op As String, Mandant As String, Niederlassung As String, Benutzer As Integer, BelegNr As Integer, BelegDat As Date)
        Select Case op
            Case "LOAD" : LOAD(Mandant, Niederlassung, Benutzer, BelegNr, BelegDat)
            Case "INSERT"
        End Select
    End Sub

    Public Sub LOAD(Mandant As String, Niederlassung As String, Benutzer As Integer, BelegNr As Integer, BelegDat As Date, Optional loadPosAswell As Boolean = True) ' As EABeleg
        Try
            Using conn As SqlConnection = cSqlDb.GetNewOpenConnectionFMZOLL()
                Using cmd As New SqlCommand("SELECT *  FROM [tblEABelegnummer]  WHERE [Mandant]=@Mandant AND Niederlassung=@Niederlassung AND Benutzer=@Benutzer AND BelegNr=@BelegNr AND CAST(BelegDat as date)=@BelegDat", conn)
                    cmd.Parameters.AddWithValue("@Mandant", Mandant)
                    cmd.Parameters.AddWithValue("@Niederlassung", Niederlassung)
                    cmd.Parameters.AddWithValue("@Benutzer", Benutzer)
                    cmd.Parameters.AddWithValue("@BelegNr", BelegNr)
                    cmd.Parameters.AddWithValue("@BelegDat", BelegDat.ToShortDateString)
                    Dim dr = cmd.ExecuteReader()
                    If dr.Read Then

                        Me.Mandant = cSqlDb.checkNullStr(dr.Item("Mandant"))
                        Me.Niederlassung = cSqlDb.checkNullStr(dr.Item("Niederlassung"))
                        Me.Benutzer = cSqlDb.checkNullInteger(dr.Item("Benutzer"))
                        Me.BelegDat = cSqlDb.checkNullDate(dr.Item("BelegDat"))
                        Me.BelegNr = cSqlDb.checkNullInteger(dr.Item("BelegNr"))
                        Me.Belegart = cSqlDb.checkNullInteger(dr.Item("Belegart"))
                        Me.FilialenNr = cSqlDb.checkNullInteger(dr.Item("FilialenNr"))
                        Me.AbfertigungsNr = cSqlDb.checkNullInteger(dr.Item("AbfertigungsNr"))
                        Me.Ordnungsbegriff = cSqlDb.checkNullStr(dr.Item("Ordnungsbegriff"))
                        Me.KundenNr = cSqlDb.checkNullInteger(dr.Item("KundenNr"))
                        Me.Name1 = cSqlDb.checkNullStr(dr.Item("Name1"))
                        Me.Name2 = cSqlDb.checkNullStr(dr.Item("Name2"))
                        Me.Straße = cSqlDb.checkNullStr(dr.Item("Straße"))
                        Me.LandKz = cSqlDb.checkNullStr(dr.Item("LandKz"))
                        Me.PLZ = cSqlDb.checkNullStr(dr.Item("PLZ"))
                        Me.Ort = cSqlDb.checkNullStr(dr.Item("Ort"))
                        Me.UstIdKz = cSqlDb.checkNullStr(dr.Item("UstIdKz"))
                        Me.UstIdNr = cSqlDb.checkNullStr(dr.Item("UstIdNr"))
                        Me.Frachtführer = cSqlDb.checkNullStr(dr.Item("Frachtführer"))
                        Me.FrachtführerKundenNr = cSqlDb.checkNullInteger(dr.Item("FrachtführerKundenNr"))
                        Me.LKW_Kennzeichen = cSqlDb.checkNullStr(dr.Item("LKW Kennzeichen"))
                        Me.Fahrer = cSqlDb.checkNullStr(dr.Item("Fahrer"))
                        Me.Passnummer = cSqlDb.checkNullStr(dr.Item("Passnummer"))
                        Me.PreislistenNr = cSqlDb.checkNullInteger(dr.Item("PreislistenNr"))
                        Me.Steuerschlüssel = cSqlDb.checkNullInteger(dr.Item("Steuerschlüssel"))
                        Me.DruckDatumUhrzeit = cSqlDb.checkNullDateNothing(dr.Item("DruckDatumUhrzeit"))
                        Me.storniert = cSqlDb.checkNullStr(dr.Item("storniert"))
                        Me.Stornogrund = cSqlDb.checkNullStr(dr.Item("Stornogrund"))
                        Me.gebucht = cSqlDb.checkNullBool(dr.Item("gebucht"))
                        Me.gebuchtStorno = cSqlDb.checkNullBool(dr.Item("gebuchtStorno"))
                        Me.SgutKompl = cSqlDb.checkNullReturnValue(dr.Item("SgutKompl"), Nothing)
                        Me.rksv_BelegId = cSqlDb.checkNullReturnValue(dr.Item("rksv_BelegId"), Nothing)
                        Me.rksv_BelegIdStorno = cSqlDb.checkNullReturnValue(dr.Item("rksv_BelegIdStorno"), Nothing)
                        Me.rksv_id = cSqlDb.checkNullReturnValue(dr.Item("rksv_id"), Nothing)

                        Me.rksv_jws = cSqlDb.checkNullReturnValue(dr.Item("rksv_jws"), Nothing)
                        Me.rksv_qr = cSqlDb.checkNullReturnValue(dr.Item("rksv_qr"), Nothing)
                        Me.rksv_umsatz = cSqlDb.checkNullReturnValue(dr.Item("rksv_umsatz"), Nothing)
                        Me.rksv_aktiv = cSqlDb.checkNullReturnValue(dr.Item("rksv_aktiv"), False)
                        Me.Beleg_TYP = cSqlDb.checkNullReturnValue(dr.Item("Beleg_TYP"), Nothing)

                        Me.Auftraggeber_KdAuftragsnummer = cSqlDb.checkNullReturnValue(dr.Item("Auftraggeber_KdAuftragsnummer"), Nothing)
                        Me.Auftraggeber_Provision = cSqlDb.checkNullReturnValue(dr.Item("Auftraggeber_Provision"), Nothing)
                        Me.LKW_Id = cSqlDb.checkNullReturnValue(dr.Item("LKW_Id"), Nothing)
                        Me.LKW_Beleg = cSqlDb.checkNullReturnValue(dr.Item("LKW_Beleg"), Nothing)
                        Me.Benutzer_LG = cSqlDb.checkNullReturnValue(dr.Item("Benutzer_LG"), Nothing)

                        If loadPosAswell Then POS = LOAD_POS_List(Mandant, Niederlassung, Benutzer, BelegNr, BelegDat)
                        ' MsgBox(POS.Count)

                    End If
                    dr.Close()
                End Using
            End Using
            'Return Nothing
        Catch ex As Exception
            MsgBox("Fehler in der Funktion '" & System.Reflection.MethodInfo.GetCurrentMethod.Name & "'" & vbNewLine & vbNewLine & ex.Message & vbNewLine & vbNewLine & ex.StackTrace)
        End Try
        '  Return Nothing

    End Sub

    Public Function LOAD_POS_List(Mandant As String, Niederlassung As String, Benutzer As Integer, BelegNr As Integer, BelegDat As Date) As List(Of EABelegPositionen)
        Dim ll As New List(Of EABelegPositionen)
        Try
            Using conn As SqlConnection = cSqlDb.GetNewOpenConnectionFMZOLL()
                Using cmd As New SqlCommand("SELECT * FROM [tblEABelegposition]  WHERE [Mandant]=@Mandant AND Niederlassung=@Niederlassung AND Benutzer=@Benutzer AND BelegNr=@BelegNr AND CAST(BelegDat as date)=@BelegDat ", conn)
                    cmd.Parameters.AddWithValue("@Mandant", Mandant)
                    cmd.Parameters.AddWithValue("@Niederlassung", Niederlassung)
                    cmd.Parameters.AddWithValue("@Benutzer", Benutzer)
                    cmd.Parameters.AddWithValue("@BelegNr", BelegNr)
                    cmd.Parameters.AddWithValue("@BelegDat", BelegDat.ToShortDateString)
                    Dim dr = cmd.ExecuteReader()
                    While dr.Read
                        Dim l As New EABelegPositionen
                        l.Mandant = cSqlDb.checkNullStr(dr.Item("Mandant"))
                        l.Niederlassung = cSqlDb.checkNullStr(dr.Item("Niederlassung"))
                        l.Benutzer = cSqlDb.checkNullStr(dr.Item("Benutzer"))
                        l.BelegDat = cSqlDb.checkNullDate(dr.Item("BelegDat"))
                        l.BelegNr = cSqlDb.checkNullStr(dr.Item("BelegNr"))

                        l.PreislistenPos = cSqlDb.checkNullInteger(dr.Item("PreislistenPos"))
                        l.LeistungsNr = cSqlDb.checkNullInteger(dr.Item("LeistungsNr"))
                        l.LeistungsBez = cSqlDb.checkNullStr(dr.Item("LeistungsBez"))
                        l.Leistungsbeschreibung = cSqlDb.checkNullStr(dr.Item("Leistungsbeschreibung"))
                        l.Abfertigungsart = cSqlDb.checkNullInteger(dr.Item("Abfertigungsart"))
                        l.PreislistenNr = cSqlDb.checkNullInteger(dr.Item("PreislistenNr"))
                        l.PreistabellenNr = cSqlDb.checkNullInteger(dr.Item("PreistabellenNr"))
                        l.Preis = cSqlDb.checkNulDbl(dr.Item("Preis"))
                        l.TW1 = cSqlDb.checkNullStr(dr.Item("TW1"))
                        l.TW2 = cSqlDb.checkNullStr(dr.Item("TW2"))
                        l.Anzahl = cSqlDb.checkNullInteger(dr.Item("Anzahl"))
                        l.Anzahlmultiplikator = cSqlDb.checkNullStr(dr.Item("Anzahlmultiplikator"))
                        l.Betrag = cSqlDb.checkNulDbl(dr.Item("Betrag"))
                        l.Preisbindung = cSqlDb.checkNullBool(dr.Item("Preisbindung"))
                        l.EABPo_Zeitstempel = cSqlDb.checkNullStr(dr.Item("EABPo_Zeitstempel"))
                        l.AbfertigungsNr = cSqlDb.checkNullInteger(dr.Item("AbfertigungsNr"))
                        l.FilialenNr = cSqlDb.checkNullInteger(dr.Item("FilialenNr"))
                        ll.Add(l)

                    End While
                    dr.Close()
                End Using
            End Using

        Catch ex As Exception
            MsgBox("Fehler in der Funktion '" & System.Reflection.MethodInfo.GetCurrentMethod.Name & "'" & vbNewLine & vbNewLine & ex.Message & vbNewLine & vbNewLine & ex.StackTrace)
        End Try
        Return ll
    End Function

    Public Function SAVE(Optional savePOSaswell As Boolean = True) As Boolean 'obj As Object, tablename As String, where As String) As Boolean
        Dim list = getParameterList()
        If Me.BelegNr < 0 Then
            Me.BelegNr = SQL.getValueTxtBySql("SELECT isNull(MAX(BelegNr)+1,1) as BelegNr FROM tblEABelegnummer WHERE  [Mandant]=@Mandant AND Niederlassung=@Niederlassung AND Benutzer=@Benutzer ", "FMZOLL", list)
            For Each p In Me.POS : p.BelegNr = BelegNr : Next ' Alle POS durchlaufen, BelgNr ersetzen
            list = getParameterList() 'BelegNr neu laden
        End If

        SQL.doSQL(" BEGIN TRAN IF EXISTS(SELECT * FROM tblEABelegnummer WITH(updlock,serializable) WHERE  [Mandant]=@Mandant AND Niederlassung=@Niederlassung AND Benutzer=@Benutzer AND BelegNr=@BelegNr AND CAST(BelegDat as date)=@BelegDat ) " &
                           " BEGIN " & getUpdateCmd() & " END " &
                           " ELSE " &
                           " BEGIN " & getInsertCmd() & " END " &
                           " COMMIT TRAN", "FMZOLL", , list)
        ' MsgBox(POS.Count)
        If savePOSaswell Then SAVE_POS_List()
        Return True
    End Function

    Public Function SAVE_POS_List() As Boolean 'obj As Object, tablename As String, where As String) As Boolean
        If POS.Count = 0 Then Return True
        Dim b As Boolean = True
        Dim list As List(Of VERAG_PROG_ALLGEMEIN.MyListItem2) = getParameterList()

        SQL.doSQL(" DELETE FROM tblEABelegposition WHERE  [Mandant]=@Mandant AND Niederlassung=@Niederlassung AND Benutzer=@Benutzer AND BelegNr=@BelegNr AND CAST(BelegDat as date)=@BelegDat  ", "FMZOLL", , list)

        For Each p In POS
            If p.SAVE() = False Then b = False
        Next
        Return b
    End Function

    Public Function DEL_POS_List() As Boolean 'obj As Object, tablename As String, where As String) As Boolean
        If POS.Count = 0 Then Return True
        Dim list As List(Of VERAG_PROG_ALLGEMEIN.MyListItem2) = getParameterList()
        Return SQL.doSQL(" DELETE FROM tblEABelegposition WHERE  [Mandant]=@Mandant AND Niederlassung=@Niederlassung AND Benutzer=@Benutzer AND BelegNr=@BelegNr AND CAST(BelegDat as date)=@BelegDat  ", "FMZOLL", , list)
    End Function

    Public Function DELETE() As Boolean 'obj As Object, tablename As String, where As String) As Boolean
        DEL_POS_List()
        Dim list As List(Of VERAG_PROG_ALLGEMEIN.MyListItem2) = getParameterList()
        Return SQL.doSQL(" DELETE FROM tblEABelegnummer WHERE  [Mandant]=@Mandant AND Niederlassung=@Niederlassung AND Benutzer=@Benutzer AND BelegNr=@BelegNr AND CAST(BelegDat as date)=@BelegDat  ", "FMZOLL", , list)
    End Function


    Public Function getInsertCmd() As String 'obj As Object, tablename As String, Optional conn_art As String = "SDL") As Boolean
        Try
            Dim list As List(Of VERAG_PROG_ALLGEMEIN.MyListItem2) = getParameterList()
            Dim str As String = ""
            Dim values As String = ""
            For Each i In list
                Dim name = i.Text
                If name = "LKWKennzeichen" Then name = "LKW Kennzeichen"
                str &= "[" & name & "],"
                values &= "@" & i.Text.Replace("-", "").Replace(" ", "") & ","
            Next
            str = str.Substring(0, str.Length - 1) 'wg. ','
            values = values.Substring(0, values.Length - 1) 'wg. ','
            Return (" INSERT INTO tblEABelegnummer  (" & str & ") VALUES(" & values & ")  ")

        Catch ex As Exception
            MsgBox("Fehler in der Funktion '" & System.Reflection.MethodInfo.GetCurrentMethod.Name & "'" & vbNewLine & vbNewLine & ex.Message & vbNewLine & vbNewLine & ex.StackTrace)
        End Try
        Return ""
    End Function

    Function getParameterList() As List(Of VERAG_PROG_ALLGEMEIN.MyListItem2)
        Dim list As New List(Of VERAG_PROG_ALLGEMEIN.MyListItem2)
        list.Add(New VERAG_PROG_ALLGEMEIN.MyListItem2("Mandant", Mandant))
        list.Add(New VERAG_PROG_ALLGEMEIN.MyListItem2("Niederlassung", Niederlassung))
        list.Add(New VERAG_PROG_ALLGEMEIN.MyListItem2("Benutzer", Benutzer))
        ' list.Add(New VERAG_PROG_ALLGEMEIN.MyListItem2("BelegDat", BelegDat.ToString("yyyy-MM-dd HH:mm:ss.fff"))) '.ToString("dd.MM.yyyy HH:mm")))
        list.Add(New VERAG_PROG_ALLGEMEIN.MyListItem2("BelegDat", BelegDat.ToShortDateString)) '.ToString("dd.MM.yyyy HH:mm")))
        list.Add(New VERAG_PROG_ALLGEMEIN.MyListItem2("BelegNr", BelegNr))
        list.Add(New VERAG_PROG_ALLGEMEIN.MyListItem2("Belegart", Belegart))
        list.Add(New VERAG_PROG_ALLGEMEIN.MyListItem2("FilialenNr", FilialenNr))
        list.Add(New VERAG_PROG_ALLGEMEIN.MyListItem2("AbfertigungsNr", AbfertigungsNr))
        list.Add(New VERAG_PROG_ALLGEMEIN.MyListItem2("Ordnungsbegriff", Ordnungsbegriff))
        list.Add(New VERAG_PROG_ALLGEMEIN.MyListItem2("KundenNr", KundenNr))
        list.Add(New VERAG_PROG_ALLGEMEIN.MyListItem2("Name1", Name1))
        list.Add(New VERAG_PROG_ALLGEMEIN.MyListItem2("Name2", Name2))
        list.Add(New VERAG_PROG_ALLGEMEIN.MyListItem2("Straße", Straße))
        list.Add(New VERAG_PROG_ALLGEMEIN.MyListItem2("LandKz", LandKz))
        list.Add(New VERAG_PROG_ALLGEMEIN.MyListItem2("PLZ", PLZ))
        list.Add(New VERAG_PROG_ALLGEMEIN.MyListItem2("Ort", Ort))
        list.Add(New VERAG_PROG_ALLGEMEIN.MyListItem2("UstIdKz", UstIdKz))
        list.Add(New VERAG_PROG_ALLGEMEIN.MyListItem2("UstIdNr", UstIdNr))
        list.Add(New VERAG_PROG_ALLGEMEIN.MyListItem2("Frachtführer", Frachtführer))
        list.Add(New VERAG_PROG_ALLGEMEIN.MyListItem2("FrachtführerKundenNr", FrachtführerKundenNr))
        list.Add(New VERAG_PROG_ALLGEMEIN.MyListItem2("LKWKennzeichen", LKW_Kennzeichen))
        list.Add(New VERAG_PROG_ALLGEMEIN.MyListItem2("Fahrer", Fahrer))
        list.Add(New VERAG_PROG_ALLGEMEIN.MyListItem2("Passnummer", Passnummer))
        list.Add(New VERAG_PROG_ALLGEMEIN.MyListItem2("PreislistenNr", PreislistenNr))
        list.Add(New VERAG_PROG_ALLGEMEIN.MyListItem2("Steuerschlüssel", Steuerschlüssel))
        list.Add(New VERAG_PROG_ALLGEMEIN.MyListItem2("DruckDatumUhrzeit", IIf(DruckDatumUhrzeit Is Nothing, DBNull.Value, CDate(DruckDatumUhrzeit))))
        list.Add(New VERAG_PROG_ALLGEMEIN.MyListItem2("storniert", storniert))
        list.Add(New VERAG_PROG_ALLGEMEIN.MyListItem2("Stornogrund", Stornogrund))
        list.Add(New VERAG_PROG_ALLGEMEIN.MyListItem2("gebucht", gebucht))
        list.Add(New VERAG_PROG_ALLGEMEIN.MyListItem2("gebuchtStorno", gebuchtStorno))
        list.Add(New VERAG_PROG_ALLGEMEIN.MyListItem2("SgutKompl", SgutKompl))

        list.Add(New VERAG_PROG_ALLGEMEIN.MyListItem2("rksv_id", rksv_id))
        list.Add(New VERAG_PROG_ALLGEMEIN.MyListItem2("rksv_BelegId", rksv_BelegId))
        list.Add(New VERAG_PROG_ALLGEMEIN.MyListItem2("rksv_BelegIdStorno", rksv_BelegIdStorno))

        list.Add(New VERAG_PROG_ALLGEMEIN.MyListItem2("rksv_jws", rksv_jws))
        list.Add(New VERAG_PROG_ALLGEMEIN.MyListItem2("rksv_qr", rksv_qr))
        list.Add(New VERAG_PROG_ALLGEMEIN.MyListItem2("rksv_umsatz", rksv_umsatz))
        list.Add(New VERAG_PROG_ALLGEMEIN.MyListItem2("rksv_aktiv", rksv_aktiv))
        list.Add(New VERAG_PROG_ALLGEMEIN.MyListItem2("Beleg_TYP", Beleg_TYP))
        list.Add(New VERAG_PROG_ALLGEMEIN.MyListItem2("Auftraggeber_KdAuftragsnummer", Auftraggeber_KdAuftragsnummer))
        list.Add(New VERAG_PROG_ALLGEMEIN.MyListItem2("Auftraggeber_Provision", Auftraggeber_Provision))
        list.Add(New VERAG_PROG_ALLGEMEIN.MyListItem2("LKW_Id", LKW_Id))
        list.Add(New VERAG_PROG_ALLGEMEIN.MyListItem2("LKW_Beleg", LKW_Beleg))
        list.Add(New VERAG_PROG_ALLGEMEIN.MyListItem2("Benutzer_LG", Benutzer_LG))

        'For Each p As System.Reflection.PropertyInfo In Me.GetType().GetProperties()
        'If p.CanRead Then
        'Dim name = p.Name
        ' If name = "LKW_Kennzeichen" Then name = "LKW Kennzeichen"
        '  list.Add(New VERAG_PROG_ALLGEMEIN.MyListItem2(name, p.GetValue(Me, Nothing)))
        ' End If
        ' Next
        Return list
    End Function

    Public Function getUpdateCmd() As String     'obj As Object, tablename As String, where As String) As Boolean
        Try
            Dim list As List(Of VERAG_PROG_ALLGEMEIN.MyListItem2) = getParameterList()
            Dim str As String = ""
            For Each i In list
                Dim name = i.Text
                If name = "LKWKennzeichen" Then name = "LKW Kennzeichen"
                str &= "[" & name & "] = @" & i.Text.Replace("-", "").Replace(" ", "") & ","
            Next
            str = str.Substring(0, str.Length - 1) 'wg. ','
            Return (" UPDATE tblEABelegnummer SET " & str & " WHERE [Mandant]=@Mandant AND Niederlassung=@Niederlassung AND Benutzer=@Benutzer AND BelegNr=@BelegNr AND CAST(BelegDat as date)=@BelegDat ")
        Catch ex As Exception
            MsgBox("Fehler in der Funktion '" & System.Reflection.MethodInfo.GetCurrentMethod.Name & "'" & vbNewLine & vbNewLine & ex.Message & vbNewLine & vbNewLine & ex.StackTrace)
        End Try
        Return ""
    End Function

    Public Function INSERT() 'obj As Object, tablename As String, Optional conn_art As String = "SDL") As Boolean
        Try
            Dim list As List(Of VERAG_PROG_ALLGEMEIN.MyListItem2) = getParameterList()
            Dim str As String = ""
            Dim values As String = ""
            For Each i In list
                Dim name = i.Text
                If name = "LKWKennzeichen" Then name = "LKW Kennzeichen"
                str &= "[" & name & "],"
                values &= "@" & i.Text.Replace("-", "").Replace(" ", "") & ","
            Next
            str = str.Substring(0, str.Length - 1) 'wg. ','
            values = values.Substring(0, values.Length - 1) 'wg. ','
            Dim sqlstr = (" INSERT INTO tblEABelegnummer  (" & str & ") VALUES(" & values & ")  ")

            Using cmd As New SqlCommand(sqlstr, cSqlDb.GetNewOpenConnectionFMZOLL())
                For Each i In list
                    cmd.Parameters.AddWithValue("@" & i.Text.Replace("-", "").Replace(" ", "") & "", IIf(i.Value Is Nothing, DBNull.Value, i.Value))
                Next
                cmd.ExecuteNonQuery()
            End Using

            Return True
        Catch ex As Exception
            MsgBox("Fehler in der Funktion '" & System.Reflection.MethodInfo.GetCurrentMethod.Name & "'" & vbNewLine & vbNewLine & ex.Message & vbNewLine & vbNewLine & ex.StackTrace)
        End Try
        Return False
    End Function

    Public Function UPDATE() As Boolean 'obj As Object, tablename As String, where As String) As Boolean
        Try
            Dim list As List(Of VERAG_PROG_ALLGEMEIN.MyListItem2) = getParameterList()

            Dim str As String = ""
            For Each i In list
                Dim name = i.Text
                If name = "LKWKennzeichen" Then name = "LKW Kennzeichen"
                str &= "[" & name & "] = @" & i.Text.Replace("-", "").Replace(" ", "") & ","
            Next
            str = str.Substring(0, str.Length - 1) 'wg. ','
            Dim sqlstr = (" UPDATE tblEABelegnummer SET " & str & " WHERE [Mandant]=@Mandant AND Niederlassung=@Niederlassung AND Benutzer=@Benutzer AND BelegNr=@BelegNr AND CAST(BelegDat as date)=@BelegDat ")

            Using cmd As New SqlCommand(sqlstr, cSqlDb.GetNewOpenConnectionFMZOLL())
                For Each i In list
                    cmd.Parameters.AddWithValue("@" & i.Text.Replace("-", "").Replace(" ", "") & "", IIf(i.Value Is Nothing, DBNull.Value, i.Value))
                Next
                cmd.ExecuteNonQuery()
            End Using

            Return True
        Catch ex As Exception
            MsgBox("Fehler in der Funktion '" & System.Reflection.MethodInfo.GetCurrentMethod.Name & "'" & vbNewLine & vbNewLine & ex.Message & vbNewLine & vbNewLine & ex.StackTrace)
        End Try
        Return False
    End Function


End Class

