Imports System
Imports System.Data
Imports System.Data.SqlTypes
Imports System.Data.SqlClient

Namespace Sysadmin

    Public Class DomainTable

#Region "Deklarationen"

        Dim UpdateCommand As New SqlCommand
        Dim InsertCommand As New SqlCommand
        Dim selectcommand As New SqlCommand
        Dim BaseData As New DataSet
        Dim connection As New SqlConnection()
        Dim da As New SqlDataAdapter("", connection)

        Dim m_tablename As String
        Property Tablename() As String
            Get
                Return m_tablename
            End Get
            Set(ByVal value As String)
                m_tablename = value
            End Set
        End Property

        Dim m_selectproc As String
        Property Select_Proc() As String
            Get
                Return "pr_" & Tablename & "_selectall"
            End Get
            Set(ByVal value As String)
                m_selectproc = "pr_" & Tablename & "_selectall"
            End Set
        End Property
        Property Update_Proc() As String
            Get
                Return "pr_" & Tablename & "_Update"
            End Get
            Set(ByVal value As String)
                m_selectproc = "pr_" & Tablename & "_Update"
            End Set
        End Property

        Property Insert_Proc() As String
            Get
                Return "pr_" & Tablename & "_Insert"
            End Get
            Set(ByVal value As String)
                m_selectproc = "pr_" & Tablename & "_Update"
            End Set
        End Property

        Property Select_Proc_Bottomtable() As String
            Get
                Return "pr_" & Tablename & "_selectall_bottomtable"
            End Get
            Set(ByVal value As String)
                m_selectproc = "pr_" & Tablename & "_selectall_bottomtable"
            End Set
        End Property
        Property Select_Proc_Bottomtable2() As String
            Get
                Return "pr_" & Tablename & "_selectall_bottomtable2"
            End Get
            Set(ByVal value As String)
                m_selectproc = "pr_" & Tablename & "_selectall_bottomtable2"
            End Set
        End Property
        Property Update_Proc_Bottomtable() As String
            Get
                Return "pr_" & Tablename & "_Update_bottomtable"
            End Get
            Set(ByVal value As String)
                m_selectproc = "pr_" & Tablename & "_Update_bottomtable"
            End Set
        End Property

        Property Insert_Proc_Bottomtable() As String
            Get
                Return "pr_" & Tablename & "_Insert_bottomtable"
            End Get
            Set(ByVal value As String)
                m_selectproc = "pr_" & Tablename & "_Update_bottomtable"
            End Set
        End Property

        Dim m_Tabledata As New DataSet
        Property Tabledata() As DataSet
            Get
                Return m_Tabledata
            End Get
            Set(ByVal value As DataSet)
                m_Tabledata = value
            End Set
        End Property

#End Region

        ''' <summary>
        ''' Tabellenname übernehmen und Daten ab DB laden
        ''' </summary>
        ''' <param name="tablename"></param>
        ''' <remarks></remarks>
        Sub New(ByVal tablename As String, Optional ByVal Fokus As Integer = 0, Optional ByVal Keyvalue As String = "")
            Me.Tablename = tablename
            If Keyvalue = "" Then
                Load_Data()
            Else
                Load_Bootom_Table(tablename, Fokus, Keyvalue)
            End If
        End Sub

        Sub New(ByVal tablename As String, ByVal Fokus As Integer, ByVal Keyvalue As String, ByVal mitarbeiternr As Integer)
            Me.Tablename = tablename
            If Keyvalue = "" Then
                Load_Data()
            Else
                Load_Data_MA_Fokus(tablename, Fokus, Keyvalue, mitarbeiternr)
            End If
        End Sub


        Sub New(ByVal tablename As String, ByVal keyvalue As String, ByVal mitarbeiternr As Integer)
            Me.Tablename = tablename
            Load_Data_MA(tablename, keyvalue, mitarbeiternr)
        End Sub

        Sub New(ByVal tablename As String, ByVal Focus As Integer, ByVal keyvalue As String, ByVal mitarbeiternr As Integer, ByVal Key2 As String)
            Me.Tablename = tablename
            Load_Data_2Key(tablename, keyvalue, mitarbeiternr, Key2)
        End Sub


        Public Sub Load_Data_MA(ByVal tablename As String, ByVal KeyValue As String, ByVal mitarbeiternr As Integer)
            Tabledata.Tables.Clear()
            Dim sqlcmd As New SqlCommand
            sqlcmd.CommandText = Me.Select_Proc_Bottomtable
            sqlcmd.Parameters.Add("@iErrorCode", SqlDbType.Int, 4)
            sqlcmd.Parameters.Add("@Fokus", SqlDbType.Int, 4)
            sqlcmd.Parameters.Add("@KeyValue", SqlDbType.VarChar, 255)
            sqlcmd.Parameters.Add("@mitarbeiternr", SqlDbType.VarChar, 255)
            sqlcmd.Parameters(0).Value = 0
            sqlcmd.Parameters(1).Value = 0
            sqlcmd.Parameters(2).Value = KeyValue
            sqlcmd.Parameters(3).Value = Globals.clsmitarbeiter.iMitarbeiternr.Value

            sqlcmd.CommandType = CommandType.StoredProcedure
            sqlcmd.Connection = connection
            Try
                connection.ConnectionString = Globals.sConnectionString
                connection.Open()
                da.SelectCommand = sqlcmd
                da.Fill(Tabledata, "Domaintable")
            Catch ex As Exception
            Finally
                connection.Close()
                da.Dispose()
                sqlcmd.Dispose()
            End Try
        End Sub

        Public Sub Load_Data_2Key(ByVal tablename As String, ByVal KeyValue As String, ByVal mitarbeiternr As Integer, ByVal KeyValue2 As String)
            Tabledata.Tables.Clear()
            Dim sqlcmd As New SqlCommand
            sqlcmd.CommandText = Me.Select_Proc_Bottomtable2
            sqlcmd.Parameters.Add("@iErrorCode", SqlDbType.Int, 4)
            sqlcmd.Parameters.Add("@Fokus", SqlDbType.Int, 4)
            sqlcmd.Parameters.Add("@KeyValue", SqlDbType.VarChar, 255)
            sqlcmd.Parameters.Add("@KeyValue2", SqlDbType.VarChar, 255)
            sqlcmd.Parameters.Add("@mitarbeiternr", SqlDbType.VarChar, 255)
            sqlcmd.Parameters(0).Value = 0
            sqlcmd.Parameters(1).Value = 0
            sqlcmd.Parameters(2).Value = KeyValue
            sqlcmd.Parameters(3).Value = KeyValue2
            sqlcmd.Parameters(4).Value = Globals.clsmitarbeiter.iMitarbeiternr.Value

            sqlcmd.CommandType = CommandType.StoredProcedure
            sqlcmd.Connection = connection
            Try
                connection.ConnectionString = Globals.sConnectionString
                connection.Open()
                da.SelectCommand = sqlcmd
                da.Fill(Tabledata, "Domaintable")
            Catch ex As Exception
            Finally
                connection.Close()
                da.Dispose()
                sqlcmd.Dispose()
            End Try
        End Sub

        Public Sub Load_Data_MA_Fokus(ByVal tablename As String, ByVal Fokus As Integer, ByVal KeyValue As String, ByVal mitarbeiternr As Integer)
            Tabledata.Tables.Clear()
            Dim sqlcmd As New SqlCommand
            sqlcmd.CommandText = Me.Select_Proc_Bottomtable
            sqlcmd.Parameters.Add("@iErrorCode", SqlDbType.Int, 4)
            sqlcmd.Parameters.Add("@Fokus", SqlDbType.Int, 4)
            sqlcmd.Parameters.Add("@KeyValue", SqlDbType.VarChar, 255)
            sqlcmd.Parameters.Add("@mitarbeiternr", SqlDbType.VarChar, 255)
            sqlcmd.Parameters(0).Value = 0
            sqlcmd.Parameters(1).Value = Fokus
            sqlcmd.Parameters(2).Value = KeyValue
            sqlcmd.Parameters(3).Value = Globals.clsmitarbeiter.iMitarbeiternr.Value

            sqlcmd.CommandType = CommandType.StoredProcedure
            sqlcmd.Connection = connection
            Try
                connection.ConnectionString = Globals.sConnectionString
                connection.Open()
                da.SelectCommand = sqlcmd
                da.Fill(Tabledata, "Domaintable")
            Catch ex As Exception
            Finally
                connection.Close()
                da.Dispose()
                sqlcmd.Dispose()
            End Try
        End Sub

        'Sub New()
        'End Sub
        ''' <summary>
        ''' Daten ab Datenbank laden
        ''' </summary>
        ''' <remarks></remarks>
        Public Overridable Sub Load_Data()
            Tabledata.Tables.Clear()

            selectcommand.CommandText = Me.Select_Proc
            selectcommand.Parameters.Add("@iErrorCode", SqlDbType.Int, 4)
            selectcommand.Parameters(0).Value = 0

            selectcommand.CommandType = CommandType.StoredProcedure
            selectcommand.Connection = connection
            Try
                connection.ConnectionString = Globals.sConnectionString
                connection.Open()
                da.SelectCommand = selectcommand
                da.Fill(Tabledata, "Domaintable")
            Catch ex As Exception
            Finally
                connection.Close()
                da.Dispose()
                selectcommand.Dispose()
            End Try
        End Sub
        ''' <summary>
        ''' Basis-Datentabelle laden. Diese wird für die dynamische Generierung der Insert- und Update-Statements benötigt
        ''' </summary>
        ''' <remarks></remarks>
        Private Sub Load_BaseData()
            BaseData.Tables.Clear()
            Dim sqlcmd As New SqlCommand

            sqlcmd.CommandText = Me.Select_Proc
            sqlcmd.Parameters.Add("@iErrorCode", SqlDbType.Int, 4)
            sqlcmd.Parameters(0).Value = 0

            sqlcmd.CommandType = CommandType.StoredProcedure
            sqlcmd.Connection = connection
            Try
                connection.ConnectionString = Globals.sConnectionString
                connection.Open()
                da.SelectCommand = sqlcmd
                da.Fill(BaseData, "Basedata")
            Catch ex As Exception
            Finally
                connection.Close()
                da.Dispose()
                sqlcmd.Dispose()
            End Try
        End Sub

        ''' <summary>
        ''' Update-Statement dynamisch für das UpdateCommand generieren
        ''' </summary>
        ''' <remarks></remarks>
        Private Sub Generate_Update_Statement()
            Dim col As DataColumn
            Dim col1 As DataColumn
            Dim UseCol As Boolean = False

            UpdateCommand.CommandText = Me.Update_Proc
            UpdateCommand.CommandType = System.Data.CommandType.StoredProcedure
            UpdateCommand.Connection = connection

            UpdateCommand.Parameters.Clear()
            For Each col In Me.Tabledata.Tables(0).Columns
                UseCol = False
                For Each col1 In Me.BaseData.Tables(0).Columns
                    If col.ColumnName = col1.ColumnName Then
                        UseCol = True
                        Exit For
                    End If
                Next
                If UseCol Then UpdateCommand.Parameters.Add(New System.Data.SqlClient.SqlParameter(Get_Prefix(col) & col.ColumnName, Get_SqlDBType(col), Get_Data_Fieldlen(col), col.ColumnName))
            Next
            UpdateCommand.Parameters.Add(New System.Data.SqlClient.SqlParameter("@iErrorcode", SqlDbType.Int, 4))
            UpdateCommand.Parameters("@iErrorcode").Value = 0
            da.UpdateCommand = UpdateCommand
        End Sub
        ''' <summary>
        ''' Insert-Statement dynamisch für das InsertCommand generieren
        ''' </summary>
        ''' <remarks></remarks>
        Private Sub Generate_Insert_Statement()
            Dim col As DataColumn
            Dim col1 As DataColumn
            Dim UseCol As Boolean = False

            InsertCommand.CommandText = Me.Insert_Proc
            InsertCommand.CommandType = System.Data.CommandType.StoredProcedure
            InsertCommand.Connection = connection

            InsertCommand.Parameters.Clear()
            For Each col In Me.Tabledata.Tables(0).Columns
                UseCol = False
                For Each col1 In Me.BaseData.Tables(0).Columns
                    If col.ColumnName = col1.ColumnName Then
                        UseCol = True
                        Exit For
                    End If
                Next
                If UseCol Then InsertCommand.Parameters.Add(New System.Data.SqlClient.SqlParameter(Get_Prefix(col) & col.ColumnName, Get_SqlDBType(col), Get_Data_Fieldlen(col), col.ColumnName))
            Next
            InsertCommand.Parameters.Add(New System.Data.SqlClient.SqlParameter("@iErrorcode", SqlDbType.Int, 4))
            InsertCommand.Parameters("@iErrorcode").Value = 0
            da.InsertCommand = InsertCommand
        End Sub
        ''' <summary>
        ''' Prefixt für den SP-Übergabeparameter generieren
        ''' </summary>
        ''' <param name="col">Aktuelle Columnt</param>
        ''' <returns>Prefis für SP-Übergabeparameter</returns>
        ''' <remarks></remarks>
        Private Function Get_Prefix(ByVal col As DataColumn) As String
            If col.DataType.Name = "DateTime" Then Return "@da"
            If col.DataType.Name = "Double" Then Return "@f"
            Return "@" & col.DataType.Name.Substring(0, 1)
        End Function
        ''' <summary>
        ''' SQL-DB-Type für den SP-Übergabeparameter festlegen
        ''' </summary>
        ''' <param name="col">Aktuelle Column</param>
        ''' <returns>SQLDBType</returns>
        ''' <remarks></remarks>
        Private Function Get_SqlDBType(ByVal col As DataColumn) As SqlDbType
            If col.DataType.Name = "Integer" Then Return SqlDbType.Int
            If col.DataType.Name = "Int32" Then Return SqlDbType.Int
            If col.DataType.Name = "String" Then Return SqlDbType.VarChar
            If col.DataType.Name = "Boolean" Then Return SqlDbType.Bit
            If col.DataType.Name = "DateTime" Then Return SqlDbType.DateTime
            If col.DataType.Name = "Double" Then Return SqlDbType.Float
            MsgBox(col.DataType.Name)
        End Function

        ''' <summary>
        ''' Feldlänge für den SP-Übergabeparemter festlegen
        ''' </summary>
        ''' <param name="col">Aktulle Column</param>
        ''' <returns>Feldlänge</returns>
        ''' <remarks></remarks>
        Private Function Get_Data_Fieldlen(ByVal col As DataColumn) As Integer
            Return col.MaxLength
        End Function

        ''' <summary>
        ''' Datesichern. Dabei wird das Update- sowie das Insert-Statement dynamisch generiert
        ''' </summary>
        ''' <remarks></remarks>
        Public Sub Save_Data()
            Load_BaseData()
            Generate_Update_Statement()
            Generate_Insert_Statement()
            Try
                da.Update(Me.Tabledata, Me.Tabledata.Tables(0).TableName)

            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
        End Sub
        ''' <summary>
        ''' Dispose von Tabledata
        ''' </summary>
        ''' <remarks></remarks>
        Public Sub dispose()
            Me.Tabledata.Dispose()
        End Sub

#Region "Verknüpfungseditor"
        ''' <summary>
        ''' Load der Verbindungstabelle
        ''' </summary>
        ''' <param name="tablename"></param>
        ''' <param name="Fokus"></param>
        ''' <param name="KeyValue"></param>
        ''' <remarks></remarks>
        Public Sub Load_Bootom_Table(ByVal tablename As String, ByVal Fokus As Integer, ByVal KeyValue As String)
            Tabledata.Tables.Clear()
            Dim sqlcmd As New SqlCommand
            sqlcmd.CommandText = Me.Select_Proc_Bottomtable
            sqlcmd.Parameters.Add("@iErrorCode", SqlDbType.Int, 4)
            sqlcmd.Parameters.Add("@Fokus", SqlDbType.Int, 4)
            sqlcmd.Parameters.Add("@KeyValue", SqlDbType.VarChar, 255)
            sqlcmd.Parameters(0).Value = 0
            sqlcmd.Parameters(1).Value = Fokus
            sqlcmd.Parameters(2).Value = KeyValue

            sqlcmd.CommandType = CommandType.StoredProcedure
            sqlcmd.Connection = connection
            Try
                connection.ConnectionString = Globals.sConnectionString
                connection.Open()
                da.SelectCommand = sqlcmd
                da.Fill(Tabledata, "Domaintable")
            Catch ex As Exception
            Finally
                connection.Close()
                da.Dispose()
                sqlcmd.Dispose()
            End Try
        End Sub
        ''' <summary>
        ''' Neuer Eintrag in der Tabelle eintragen. Sind neben den Defaultwerten weitere Attribute vorhanden, werde diese abhängig vom Datentype mit Defaultwerten befüllt.
        ''' </summary>
        ''' <param name="key1"></param>
        ''' <param name="keyvalue1"></param>
        ''' <param name="key2"></param>
        ''' <param name="keyvalue2"></param>
        ''' <remarks></remarks>
        Public Sub Insert_Bottom_Table(ByVal key1 As String, ByVal keyvalue1 As Integer, ByVal key2 As String, ByVal keyvalue2 As String)
            Dim dbkey As New db.clsMyKey_Tabelle
            dbkey.cpMainConnectionProvider = Globals.conn
            conn.OpenConnection()
            Dim newkey As Integer = dbkey.get_dbkey(Me.Tablename)
            conn.CloseConnection(True)
            dbkey.Dispose()
            Dim dr As DataRow
            dr = Me.Tabledata.Tables(0).NewRow
            dr.Item(0) = newkey
            Dim i As Integer
            For i = 1 To Me.Tabledata.Tables(0).Columns.Count - 1
                Select Case UCase(Me.Tabledata.Tables(0).Columns(i).ColumnName)
                    Case "AKTIV"
                        dr.Item(i) = 1
                    Case "ERSTELLT_AM"
                        dr.Item(i) = Now
                    Case "MUTIERT_AM"
                        dr.Item(i) = Now
                    Case "MUTIERER"
                        dr.Item(i) = Globals.clsmitarbeiter.iMitarbeiternr.Value
                    Case "MANDANTNR"
                        dr.Item(i) = Globals.clsmitarbeiter.iMandantnr.Value
                    Case "MANDANT"
                        dr.Item(i) = Globals.clsmitarbeiter.iMandantnr.Value
                    Case UCase(key1)
                        dr.Item(i) = keyvalue1
                    Case UCase(key2)
                        dr.Item(i) = keyvalue2
                    Case Else
                        Select Case Type.GetTypeCode(Me.Tabledata.Tables(0).Columns(i).DataType)
                            Case TypeCode.DateTime
                                dr.Item(i) = Now
                            Case TypeCode.Double
                                dr.Item(i) = 0
                            Case TypeCode.String
                                dr.Item(i) = ""
                            Case TypeCode.Int32
                                dr.Item(i) = 0
                            Case TypeCode.Boolean
                                dr.Item(i) = True
                            Case TypeCode.Int16
                                dr.Item(i) = 0
                            Case TypeCode.Int32
                                dr.Item(i) = 0
                            Case TypeCode.Int64
                                dr.Item(i) = 0
                        End Select
                End Select
            Next
            Me.Tabledata.Tables(0).Rows.Add(dr)
        End Sub
#End Region


    End Class

End Namespace