Imports System.Data.SqlClient
Imports System.Reflection

Public Class cFirmen
    Property Firma_ID As Integer
    Property Firma_Bez As String
    Property Firma_BackColor As Object = Nothing '       INT             DEFAULT ((16777215)) NOT NULL,
    Property Firma_Logo As Object = Nothing '            VARBINARY (MAX) NULL,
    Property Firma_Firmenbuch As Object = Nothing '      NVARCHAR (22)   NULL,
    Property Firma_UID As Object = Nothing '             NVARCHAR (16)   NULL,
    Property Firma_EORI As Object = Nothing '            NVARCHAR (21)   NULL,
    Property Firma_Bankverbindung1 As Object = Nothing ' NVARCHAR (50)   NULL,
    Property Firma_BLZ1 As Object = Nothing '            NVARCHAR (33)   NULL,
    Property Firma_KTO1 As Object = Nothing '            NVARCHAR (18)   NULL,
    Property Firma_IBAN1 As Object = Nothing '           NVARCHAR (38)   NULL,
    Property Firma_BIC1 As Object = Nothing '            NVARCHAR (16)   NULL,
    Property Firma_Bankverbindung2 As Object = Nothing ' NVARCHAR (50)   NULL,
    Property Firma_BLZ2 As Object = Nothing '            NVARCHAR (33)   NULL,
    Property Firma_KTO2 As Object = Nothing '            NVARCHAR (18)   NULL,
    Property Firma_IBAN2 As Object = Nothing '           NVARCHAR (38)   NULL,
    Property Firma_BIC2 As Object = Nothing '            NVARCHAR (16)   NULL,
    Property SSMA_TimeStamp As Object = Nothing '        ROWVERSION      NOT NULL,
    Property Firma_Ort As Object = Nothing '             NVARCHAR (40)   NULL,
    Property Firma_Straße As Object = Nothing '          NVARCHAR (40)   NULL,
    Property Firma_Telefon As Object = Nothing '         NVARCHAR (40)   NULL,
    Property Firma_Telefax As Object = Nothing '         NVARCHAR (40)   NULL,
    Property Firma_E_Mail As Object = Nothing '          NVARCHAR (40)   NULL, -----
    Property Firma_Webadresse As Object = Nothing '      NVARCHAR (40)   NULL,
    Property Firma_LandKz As Object = Nothing '      NVARCHAR (40)   NULL,



    Dim SQL As New SQL

    Sub New()
    End Sub

    Sub New(Firma_ID)
        Me.Firma_ID = Firma_ID
        LOAD()
    End Sub

    Public Function getParameterList() As List(Of VERAG_PROG_ALLGEMEIN.SQLVariable)
        Dim list As New List(Of VERAG_PROG_ALLGEMEIN.SQLVariable)
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("Firma_ID", Firma_ID, , True, True))

        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("Firma_Bez", Firma_Bez))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("Firma_BackColor", Firma_BackColor))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("Firma_Logo", Firma_Logo))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("Firma_Firmenbuch", Firma_Firmenbuch))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("Firma_UID", Firma_UID))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("Firma_EORI", Firma_EORI))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("Firma_Bankverbindung1", Firma_Bankverbindung1))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("Firma_BLZ1", Firma_BLZ1))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("Firma_KTO1", Firma_KTO1))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("Firma_IBAN1", Firma_IBAN1))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("Firma_BIC1", Firma_BIC1))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("Firma_Bankverbindung2", Firma_Bankverbindung2))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("Firma_BLZ2", Firma_BLZ2))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("Firma_KTO2", Firma_KTO2))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("Firma_IBAN2", Firma_IBAN2))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("Firma_BIC2", Firma_BIC2))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("SSMA_TimeStamp", SSMA_TimeStamp))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("Firma_Ort", Firma_Ort))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("Firma_Straße", Firma_Straße))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("Firma_Telefon", Firma_Telefon))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("Firma_Telefax", Firma_Telefax))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("Firma_E-Mail", Firma_E_Mail, "Firma_E_Mail"))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("Firma_Webadresse", Firma_Webadresse))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("Firma_LandKz", Firma_LandKz))


        Return list
    End Function



    Public Sub LOAD()
        Try
            Using conn As SqlConnection = SQL.GetNewOpenConnectionFMZOLL()
                Using cmd As New SqlCommand("SELECT * FROM tblFirma WHERE Firma_ID=@Firma_ID  ", conn)
                    cmd.Parameters.AddWithValue("@Firma_ID", Firma_ID)
                    Dim dr = cmd.ExecuteReader()
                    If dr.Read Then
                        For Each li In getParameterList()
                            Dim propInfo As PropertyInfo = Me.GetType.GetProperty(li.Scalarvariable)

                            If dr.Item(li.Text) Is DBNull.Value Then
                                propInfo.SetValue(Me, Nothing)
                            Else
                                propInfo.SetValue(Me, dr.Item(li.Text))
                            End If

                        Next

                    End If
                    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
    End Sub

    Public Shared Function LOAD_LIST() As List(Of cFirmen)
        Dim FIRMEN As New List(Of cFirmen)
        Try
            Using conn As SqlConnection = SQL.GetNewOpenConnectionFMZOLL()
                Using cmd As New SqlCommand("SELECT * FROM tblFirma ", conn)
                    Dim dr = cmd.ExecuteReader()
                    While dr.Read
                        Dim FIMRA As New cFirmen
                        For Each li In FIMRA.getParameterList()
                            Dim propInfo As PropertyInfo = FIMRA.GetType.GetProperty(li.Scalarvariable)

                            If dr.Item(li.Text) Is DBNull.Value Then
                                propInfo.SetValue(FIMRA, Nothing)
                            Else
                                propInfo.SetValue(FIMRA, dr.Item(li.Text))
                            End If

                        Next
                        FIRMEN.Add(FIMRA)
                    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 FIRMEN
    End Function


End Class
