Imports System.Data
Imports System.Data.SqlClient


'''<summary>Datenzugriff auf Tabelle dokumentart</summary>
Public Class Dokumentart
    '''<summary>gibt eine dokumentart zurück</summary>
    '''<param name="ds">für datenrückgabe</param>
    '''<param name="dokumentartNr"></param>

    Public Shared Sub GetDokumentart(ByRef ds As DataSet, ByVal dokumentartNr As Integer)
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim da As New SqlDataAdapter()

        Try
            sqlConn.ConnectionString = Common.Datenbank.GetDSN()
            sqlCmd.CommandText = "sp_GetDokumentart"
            sqlCmd.CommandType = CommandType.StoredProcedure
            sqlCmd.Connection = sqlConn

            sqlCmd.Parameters.Add(New SqlParameter("@DokumentartNr", dokumentartNr))
            da.SelectCommand = sqlCmd
            da.Fill(ds, "Dokumentart")

        Catch ex As Exception
            TKBLib.Errorhandling.TraceHelper.Msg("EDOKALib.DA.Dokumentart.GetDokumentart", ex.Message + " " + ex.StackTrace, TraceLevel.Error)
            Throw ex
        Finally
            sqlConn.Dispose()
            sqlCmd.Dispose()
            da.Dispose()
        End Try
    End Sub

    '''<summary>gibt eine liste aller dokumentarten zurück</summary>
    '''<param name="ds">für datenrückgabe</param>
    '''<param name="aktiv">1 nur aktive; 0 nur inaktive; -1 alle</param>

    Public Shared Sub List(ByRef ds As DataSet, ByVal aktiv As Integer)
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim da As New SqlDataAdapter()

        Try
            sqlConn.ConnectionString = Common.Datenbank.GetDSN()
            sqlCmd.CommandText = "sp_ListDokumentart"
            sqlCmd.CommandType = CommandType.StoredProcedure
            sqlCmd.Connection = sqlConn

            If aktiv <> -1 Then
                sqlCmd.Parameters.Add(New SqlParameter("@Aktiv", aktiv))
            End If

            da.SelectCommand = sqlCmd
            da.Fill(ds, "Dokumentarten")

        Catch ex As Exception
            TKBLib.Errorhandling.TraceHelper.Msg("EDOKALib.DA.Dokumentart.List", ex.Message + " " + ex.StackTrace, TraceLevel.Error)
            Throw ex
        Finally
            sqlConn.Dispose()
            sqlCmd.Dispose()
            da.Dispose()
        End Try
    End Sub


    '''<summary>erstellt eine neue dokumentart</summary>
    '''<param name="dokumentartNr">gibt den neuen primarykey zurück</param>
    '''<param name="bezeichnung"></param>
    '''<param name="parentId"></param>
    '''<param name="sort"></param>
    '''<param name="imageIndex"></param>
    '''<param name="imageIndexOpen"></param>
    '''<param name="beschreibung"></param>
    '''<param name="mandantNr"></param>
    '''<param name="sprache"></param>
    '''<param name="aktiv"></param>
    '''<param name="mutierer"></param>
    '''<param name="coldApplication"></param>
    '''<param name="coldBezeichnung"></param>
    '''<returns>error</returns>
    Public Shared Function Add(ByRef dokumentartNr As Integer, ByVal bezeichnung As String, ByVal parentId As Integer, ByVal sort As Integer, _
        ByVal imageIndex As Integer, ByVal imageIndexOpen As Integer, ByVal beschreibung As String, ByVal mandantNr As Integer, ByVal sprache As Integer, _
        ByVal aktiv As Boolean, ByVal mutierer As Integer, ByVal coldApplication As String, ByVal coldBezeichnung As String) As Integer

        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()

        Try
            sqlConn.ConnectionString = Common.Datenbank.GetDSN()
            sqlCmd.CommandText = "sp_InsDokumentart"
            sqlCmd.CommandType = CommandType.StoredProcedure
            sqlCmd.Connection = sqlConn

            sqlCmd.Parameters.Add(New SqlParameter("@Bezeichnung", NullHandler.CToSqlString(bezeichnung)))
            sqlCmd.Parameters.Add(New SqlParameter("@ParentId", NullHandler.CToSqlInt(parentId)))
            sqlCmd.Parameters.Add(New SqlParameter("@Sort", NullHandler.CToSqlInt(sort)))
            sqlCmd.Parameters.Add(New SqlParameter("@ImageIndex", NullHandler.CToSqlInt(imageIndex)))
            sqlCmd.Parameters.Add(New SqlParameter("@ImageIndexOpen", NullHandler.CToSqlInt(imageIndexOpen)))
            sqlCmd.Parameters.Add(New SqlParameter("@Beschreibung", NullHandler.CToSqlString(beschreibung)))
            sqlCmd.Parameters.Add(New SqlParameter("@MandantNr", mandantNr))
            sqlCmd.Parameters.Add(New SqlParameter("@Sprache", sprache))
            sqlCmd.Parameters.Add(New SqlParameter("@Aktiv", aktiv))
            sqlCmd.Parameters.Add(New SqlParameter("@Mutierer", NullHandler.CToSqlInt(mutierer)))
            sqlCmd.Parameters.Add(New SqlParameter("@ColdApplication", NullHandler.CToSqlString(coldApplication)))
            sqlCmd.Parameters.Add(New SqlParameter("@ColdBezeichnung", NullHandler.CToSqlString(coldBezeichnung)))
            sqlCmd.Parameters.Add(New SqlParameter("@DokumentartNr", 0))
            sqlCmd.Parameters.Add(New SqlParameter("@Return", DBNull.Value))
            sqlCmd.Parameters("@DokumentartNr").Direction = ParameterDirection.Output
            sqlCmd.Parameters("@Return").Direction = ParameterDirection.ReturnValue

            sqlConn.Open()
            sqlCmd.ExecuteNonQuery()

            If CInt(sqlCmd.Parameters("@Return").Value) = 0 Then
                dokumentartNr = CInt(sqlCmd.Parameters("@DokumentartNr").Value)
            End If

            Return CInt(sqlCmd.Parameters("@Return").Value)

        Catch ex As Exception
            TKBLib.Errorhandling.TraceHelper.Msg("EDOKALib.DA.Dokumentart.Add", ex.Message + " " + ex.StackTrace, TraceLevel.Error)
            Throw ex
        Finally
            sqlConn.Dispose()
            sqlCmd.Dispose()
        End Try
    End Function


    '''<summary>Löscht eine bestehende dokumentenArt</summary>
    '''<param name="dokumentartNr"></param>
    '''<returns>error</returns>
    Public Shared Function Delete(ByVal dokumentartNr As Integer) As Integer
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()

        Try
            sqlConn.ConnectionString = Common.Datenbank.GetDSN()
            sqlCmd.CommandText = "sp_DelDokumentart"
            sqlCmd.CommandType = CommandType.StoredProcedure
            sqlCmd.Connection = sqlConn

            sqlCmd.Parameters.Add(New SqlParameter("@DokumentartNr", dokumentartNr))
            sqlCmd.Parameters.Add(New SqlParameter("@Return", DBNull.Value))
            sqlCmd.Parameters("@Return").Direction = ParameterDirection.ReturnValue

            sqlConn.Open()
            sqlCmd.ExecuteNonQuery()

            Return CInt(sqlCmd.Parameters("@Return").Value)

        Catch ex As Exception
            TKBLib.Errorhandling.TraceHelper.Msg("EDOKALib.DA.Dokumentart.Delete", ex.Message + " " + ex.StackTrace, TraceLevel.Error)
            Throw ex
        Finally
            sqlConn.Dispose()
            sqlCmd.Dispose()
        End Try
    End Function


    '''<summary>updatet eine dokumentenart</summary>
    '''<param name="dokumentartNr">criterium (keine aktualisierung für diesen param)</param>
    '''<param name="bezeichnung"></param>
    '''<param name="parentId"></param>
    '''<param name="sort"></param>
    '''<param name="imageIndex"></param>
    '''<param name="imageIndexOpen"></param>
    '''<param name="beschreibung"></param>
    '''<param name="mandantNr"></param>
    '''<param name="sprache"></param>
    '''<param name="aktiv"></param>
    '''<param name="mutierer"></param>
    '''<param name="coldApplication"></param>
    '''<param name="coldBezeichnung"></param>
    '''<returns>error</returns>
    Public Shared Function Update(ByVal dokumentartNr As Integer, ByVal bezeichnung As String, ByVal parentId As Integer, ByVal sort As Integer, _
        ByVal imageIndex As Integer, ByVal imageIndexOpen As Integer, ByVal beschreibung As String, ByVal mandantNr As Integer, ByVal sprache As Integer, _
        ByVal aktiv As Boolean, ByVal mutierer As Integer, ByVal coldApplication As String, ByVal coldBezeichnung As String) As Integer


        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()

        Try
            sqlConn.ConnectionString = Common.Datenbank.GetDSN()
            sqlCmd.CommandText = "sp_SetDokumentart"
            sqlCmd.CommandType = CommandType.StoredProcedure
            sqlCmd.Connection = sqlConn

            sqlCmd.Parameters.Add(New SqlParameter("@DokumentartNr", dokumentartNr))
            sqlCmd.Parameters.Add(New SqlParameter("@Bezeichnung", NullHandler.CToSqlString(bezeichnung)))
            sqlCmd.Parameters.Add(New SqlParameter("@ParentId", NullHandler.CToSqlInt(parentId)))
            sqlCmd.Parameters.Add(New SqlParameter("@Sort", NullHandler.CToSqlInt(sort)))
            sqlCmd.Parameters.Add(New SqlParameter("@ImageIndex", NullHandler.CToSqlInt(imageIndex)))
            sqlCmd.Parameters.Add(New SqlParameter("@ImageIndexOpen", NullHandler.CToSqlInt(imageIndexOpen)))
            sqlCmd.Parameters.Add(New SqlParameter("@Beschreibung", NullHandler.CToSqlString(beschreibung)))
            sqlCmd.Parameters.Add(New SqlParameter("@MandantNr", mandantNr))
            sqlCmd.Parameters.Add(New SqlParameter("@Sprache", sprache))
            sqlCmd.Parameters.Add(New SqlParameter("@Aktiv", aktiv))
            sqlCmd.Parameters.Add(New SqlParameter("@Mutierer", NullHandler.CToSqlInt(mutierer)))
            sqlCmd.Parameters.Add(New SqlParameter("@ColdApplication", NullHandler.CToSqlString(coldApplication)))
            sqlCmd.Parameters.Add(New SqlParameter("@ColdBezeichnung", NullHandler.CToSqlString(coldBezeichnung)))
            sqlCmd.Parameters.Add(New SqlParameter("@Return", DBNull.Value))
            sqlCmd.Parameters("@Return").Direction = ParameterDirection.ReturnValue

            sqlConn.Open()
            sqlCmd.ExecuteNonQuery()

            Return CInt(sqlCmd.Parameters("@Return").Value)

        Catch ex As Exception
            TKBLib.Errorhandling.TraceHelper.Msg("EDOKALib.DA.Dokumentart.Update", ex.Message + " " + ex.StackTrace, TraceLevel.Error)
            Throw ex
        Finally
            sqlConn.Dispose()
            sqlCmd.Dispose()
        End Try
    End Function
End Class