Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlDbType
Imports System.IO

Public Class clsdb

    Dim m_connectionstring As String
    Property Connectionstring As String
        Get
            Return m_connectionstring
        End Get
        Set(value As String)
            m_connectionstring = value
        End Set
    End Property

    Public Function Get_Pendente_BCK(ByVal dokumentid As String) As DataTable
        Dim connection As New SqlConnection(Connectionstring)
        Dim scmCmdToExecute As SqlCommand = New SqlCommand()
        Dim dtToReturn As DataTable = New DataTable()
        Dim ds As New DataSet
        Dim sdaAdapter As SqlDataAdapter = New SqlDataAdapter(scmCmdToExecute)
        scmCmdToExecute.CommandText = "dbo.sp_edoka_onbase"
        scmCmdToExecute.Parameters.Add(New SqlParameter("@fnkt", SqlDbType.Int, 4, ParameterDirection.Input, True, 0, 0, "", DataRowVersion.Proposed, 1))
        scmCmdToExecute.Parameters.Add(New SqlParameter("@dokumentid", SqlDbType.VarChar, 255, ParameterDirection.Input, True, 0, 0, "", DataRowVersion.Proposed, dokumentid))
        scmCmdToExecute.CommandType = CommandType.StoredProcedure
        scmCmdToExecute.Connection = connection
        Try
            sdaAdapter.Fill(ds)
            Return ds.Tables(0)
        Catch ex As Exception
            '         MsgBox(ex.Message)
        End Try
    End Function

    Public Function Get_OnBase_Datastream(ByVal dokumentid As String) As DataSet
        Dim connection As New SqlConnection(Connectionstring)
        Dim scmCmdToExecute As SqlCommand = New SqlCommand()
        Dim dtToReturn As DataTable = New DataTable()
        Dim ds As New DataSet
        Dim sdaAdapter As SqlDataAdapter = New SqlDataAdapter(scmCmdToExecute)
        scmCmdToExecute.CommandText = "dbo.sp_edoka_onbase"
        scmCmdToExecute.Parameters.Add(New SqlParameter("@fnkt", SqlDbType.Int, 4, ParameterDirection.Input, True, 0, 0, "", DataRowVersion.Proposed, 2))
        scmCmdToExecute.Parameters.Add(New SqlParameter("@dokumentid", SqlDbType.VarChar, 255, ParameterDirection.Input, True, 0, 0, "", DataRowVersion.Proposed, dokumentid))
        scmCmdToExecute.CommandType = CommandType.StoredProcedure
        scmCmdToExecute.Connection = connection
        Try
            sdaAdapter.Fill(ds)
            Return ds
        Catch ex As Exception
            '         MsgBox(ex.Message)
        End Try
    End Function

    Public Function Get_Status(ByVal Dokumentid As String)
        Dim scmCmdToExecute As SqlCommand = New SqlCommand()
        scmCmdToExecute.CommandText = "dbo.SP_Dokumentstatus_statushandling_Select"

        Try
            scmCmdToExecute.CommandType = CommandType.StoredProcedure
            Dim dtToReturn As DataTable = New DataTable()
            Dim sdaAdapter As SqlDataAdapter = New SqlDataAdapter(scmCmdToExecute)

            Dim connection As New SqlConnection(Connectionstring)
            scmCmdToExecute.Parameters.Add(New SqlParameter("@dokumentid", SqlDbType.VarChar, 22, ParameterDirection.Input, True, 10, 0, "", DataRowVersion.Proposed, Dokumentid))
            scmCmdToExecute.Connection = connection
            sdaAdapter.Fill(dtToReturn)
            Return dtToReturn
        Catch ex As Exception
            ' // some error occured. Bubble it to caller and encapsulate Exception object
            Throw New Exception("Dokumenterstellung::Generic_Select::" & scmCmdToExecute.CommandText & "::Error occured." & ex.Message, ex)
        Finally
            scmCmdToExecute.Dispose()
        End Try
    End Function

    Public Sub insert_history_status(ByVal statusnr As Integer, ByVal dokumentid As String)
        Try
            Dim connection As New SqlConnection(Connectionstring)
            Dim scmCmdToExecute As SqlCommand = New SqlCommand()
            Dim dtToReturn As DataTable = New DataTable()
            Dim ds As New DataSet
            Dim sdaAdapter As SqlDataAdapter = New SqlDataAdapter(scmCmdToExecute)
            scmCmdToExecute.CommandText = "dbo.sp_edoka_onbase"
            scmCmdToExecute.Parameters.Add(New SqlParameter("@fnkt", SqlDbType.Int, 4, ParameterDirection.Input, True, 0, 0, "", DataRowVersion.Proposed, 3))
            scmCmdToExecute.Parameters.Add(New SqlParameter("@dokumentid", SqlDbType.VarChar, 255, ParameterDirection.Input, True, 0, 0, "", DataRowVersion.Proposed, dokumentid))
            scmCmdToExecute.Parameters.Add(New SqlParameter("@value1", SqlDbType.VarChar, 255, ParameterDirection.Input, True, 0, 0, "", DataRowVersion.Proposed, statusnr.ToString))
            scmCmdToExecute.CommandType = CommandType.StoredProcedure
            scmCmdToExecute.Connection = connection
            Try
                sdaAdapter.Fill(ds)
            Catch ex As Exception
                '         MsgBox(ex.Message)
            End Try
        Catch ex As Exception
            Threading.Thread.Sleep(1000)

            'MsgBox(ex.Message)
        End Try
    End Sub

    Public Sub update_dokument(ByVal statusnr As Integer, ByVal dokumentid As String)
        Try
            Dim connection As New SqlConnection(Connectionstring)
            Dim scmCmdToExecute As SqlCommand = New SqlCommand()
            Dim dtToReturn As DataTable = New DataTable()
            Dim ds As New DataSet
            Dim sdaAdapter As SqlDataAdapter = New SqlDataAdapter(scmCmdToExecute)
            scmCmdToExecute.CommandText = "dbo.sp_edoka_onbase"
            scmCmdToExecute.Parameters.Add(New SqlParameter("@fnkt", SqlDbType.Int, 4, ParameterDirection.Input, True, 0, 0, "", DataRowVersion.Proposed, 4))
            scmCmdToExecute.Parameters.Add(New SqlParameter("@dokumentid", SqlDbType.VarChar, 255, ParameterDirection.Input, True, 0, 0, "", DataRowVersion.Proposed, dokumentid))
            scmCmdToExecute.Parameters.Add(New SqlParameter("@value1", SqlDbType.VarChar, 255, ParameterDirection.Input, True, 0, 0, "", DataRowVersion.Proposed, statusnr.ToString))
            scmCmdToExecute.CommandType = CommandType.StoredProcedure
            scmCmdToExecute.Connection = connection
            Try
                sdaAdapter.Fill(ds)
            Catch ex As Exception
                '         MsgBox(ex.Message)
            End Try
        Catch ex As Exception
            Threading.Thread.Sleep(1000)

            'MsgBox(ex.Message)
        End Try
    End Sub
    Public Function Get_Archivdokumente(ByVal dokumentid As String) As DataTable
        Try
            Dim connection As New SqlConnection(Connectionstring)
            Dim scmCmdToExecute As SqlCommand = New SqlCommand()
            Dim dtToReturn As DataTable = New DataTable()
            Dim ds As New DataSet
            Dim sdaAdapter As SqlDataAdapter = New SqlDataAdapter(scmCmdToExecute)
            scmCmdToExecute.CommandText = "dbo.sp_edoka_onbase"
            scmCmdToExecute.Parameters.Add(New SqlParameter("@fnkt", SqlDbType.Int, 4, ParameterDirection.Input, True, 0, 0, "", DataRowVersion.Proposed, 5))
            scmCmdToExecute.Parameters.Add(New SqlParameter("@dokumentid", SqlDbType.VarChar, 255, ParameterDirection.Input, True, 0, 0, "", DataRowVersion.Proposed, dokumentid))
            scmCmdToExecute.CommandType = CommandType.StoredProcedure
            scmCmdToExecute.Connection = connection
            Try
                sdaAdapter.Fill(ds)
                Return ds.Tables(0)
            Catch ex As Exception
                '         MsgBox(ex.Message)
            End Try
        Catch ex As Exception
            Threading.Thread.Sleep(1000)

            'MsgBox(ex.Message)
        End Try
    End Function

    Public Function Get_Dokument(ByVal dokumentid As String) As DataTable
        Try
            Dim connection As New SqlConnection(Connectionstring)
            Dim scmCmdToExecute As SqlCommand = New SqlCommand()
            Dim dtToReturn As DataTable = New DataTable()
            Dim ds As New DataSet
            Dim sdaAdapter As SqlDataAdapter = New SqlDataAdapter(scmCmdToExecute)
            scmCmdToExecute.CommandText = "dbo.sp_edoka_onbase"
            scmCmdToExecute.Parameters.Add(New SqlParameter("@fnkt", SqlDbType.Int, 4, ParameterDirection.Input, True, 0, 0, "", DataRowVersion.Proposed, 6))
            scmCmdToExecute.Parameters.Add(New SqlParameter("@dokumentid", SqlDbType.VarChar, 255, ParameterDirection.Input, True, 0, 0, "", DataRowVersion.Proposed, dokumentid))
            scmCmdToExecute.CommandType = CommandType.StoredProcedure
            scmCmdToExecute.Connection = connection
            Try
                sdaAdapter.Fill(ds)
                Return ds.Tables(0)
            Catch ex As Exception
                PDebug(ex.Message)
            End Try
        Catch ex As Exception
            Threading.Thread.Sleep(1000)
            PDebug(ex.Message)
            'MsgBox(ex.Message)
        End Try
    End Function

    Public Function Get_From_DB(ByVal sDokumentID As String, ByVal sDokumentName As String) As Boolean
        Dim connection As New SqlConnection()
        Dim da As New SqlDataAdapter("Select * From doks where DokumentID='" + sDokumentID + "'", connection)
        Dim CB As SqlCommandBuilder = New SqlCommandBuilder(da)
        Dim ds As New DataSet()

        Try
            'Connectionstring zur Datenbank
            connection.ConnectionString = Connectionstring
            connection.Open()
            da.Fill(ds, "docs")
            Dim myRow As DataRow
            myRow = ds.Tables(0).Rows(0)
            Dim MyData() As Byte
            MyData = myRow.Item(1)
            Dim K As Long
            K = UBound(MyData)
            Dim fs As New FileStream(sDokumentName, FileMode.OpenOrCreate, FileAccess.Write)
            fs.Write(MyData, 0, K)
            fs.Close()
            fs = Nothing
        Catch ex As Exception
            Return False
        End Try
        CB = Nothing
        ds = Nothing
        da = Nothing
        connection.Close()
        connection = Nothing
        Return True
    End Function

    Public Function Update_EDOKA_IL_Status(ByVal Dokumentid As String, ByVal response As String, status As String)
        Try
            Dim connection As New SqlConnection(Connectionstring)
            Dim scmCmdToExecute As SqlCommand = New SqlCommand()
            Dim dtToReturn As DataTable = New DataTable()
            Dim ds As New DataSet
            Dim sdaAdapter As SqlDataAdapter = New SqlDataAdapter(scmCmdToExecute)
            scmCmdToExecute.CommandText = "dbo.sp_edoka_onbase"
            scmCmdToExecute.Parameters.Add(New SqlParameter("@fnkt", SqlDbType.Int, 4, ParameterDirection.Input, True, 0, 0, "", DataRowVersion.Proposed, 7))
            scmCmdToExecute.Parameters.Add(New SqlParameter("@dokumentid", SqlDbType.VarChar, 255, ParameterDirection.Input, True, 0, 0, "", DataRowVersion.Proposed, Dokumentid))
            scmCmdToExecute.Parameters.Add(New SqlParameter("@value1", SqlDbType.VarChar, 4096, ParameterDirection.Input, True, 0, 0, "", DataRowVersion.Proposed, response))
            scmCmdToExecute.Parameters.Add(New SqlParameter("@value2", SqlDbType.VarChar, 255, ParameterDirection.Input, True, 0, 0, "", DataRowVersion.Proposed, status))
            scmCmdToExecute.CommandType = CommandType.StoredProcedure
            scmCmdToExecute.Connection = connection
            Try
                sdaAdapter.Fill(ds)
                Return ds.Tables(0)
            Catch ex As Exception
                '         MsgBox(ex.Message)
            End Try
        Catch ex As Exception
            Threading.Thread.Sleep(1000)

            'MsgBox(ex.Message)
        End Try
    End Function
End Class