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

Public Class clsdb
    Dim mdebug As Boolean = False
    Property Debug As Boolean
        Get
            Return mdebug
        End Get
        Set(value As Boolean)
            mdebug = value
        End Set
    End Property
    Dim mlogfile As String = ""
    Property Logfile As String
        Get
            Return mlogfile

        End Get
        Set(value As String)
            mlogfile = value
        End Set
    End Property
    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
    Dim m_connectionstring_DokTypMapping As String
    Property Connectionstring_DokTypMapping As String
        Get
            Return m_connectionstring_DokTypMapping
        End Get
        Set(value As String)
            m_connectionstring_DokTypMapping = value
        End Set
    End Property
    Public Sub PDebug(ByVal istring As String)
        Dim s As String
        s = Now.ToString("u")
        istring = s + " - " + istring
        If Debug = True Then
            Console.WriteLine(istring)
            If Logfile <> "" Then
                Try
                    FileOpen(1, Logfile, OpenMode.Append)
                    WriteLine(1, istring)
                    FileClose(1)
                Catch

                End Try
            End If
        End If
    End Sub
    Public Function Get_Pendente_BCK() 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, ""))
        scmCmdToExecute.CommandType = CommandType.StoredProcedure
        scmCmdToExecute.Connection = connection
        Try
            sdaAdapter.Fill(ds)
            Return ds.Tables(0)
        Catch ex As Exception
            PDebug(ex.Message)
        End Try
    End Function

    Public Function Get_DokType(ByVal dokumentid As String) As String
        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, 8))
        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)
            If ds.Tables(0).Rows.Count > 0 Then
                Return ds.Tables(0).Rows(0).Item(0)
            Else
                Return ""
            End If

        Catch ex As Exception
            'pdebug("Fehler:Get_OnBase_Datastream: "+ex.Message)
        End Try
    End Function
    Public Function Get_DokTyp_Mapping(ByVal Dokumentid As String) As DataSet
        Dim doktyp As String
        doktyp = Get_DokType(Dokumentid)
        If doktyp = "" Then
            PDebug("Dokumenttyp zu Dokumentid: " + Dokumentid + " nicht vorhanden")
            Exit Function
        End If

        Dim connection As New SqlConnection(Me.Connectionstring_DokTypMapping)
        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 = "select * from havi_lookup_Dokumenttyp_Mapping where edoka_dokumenttypnr=" + doktyp
        scmCmdToExecute.CommandType = CommandType.Text

        scmCmdToExecute.Connection = connection
        Try
            sdaAdapter.Fill(ds)
            Return ds
        Catch ex As Exception
            PDebug("Fehler:Get_OnBase_Datastream: " + ex.Message)
        End Try
    End Function


    Public Function Get_OnBase_Datastream(ByVal dokumentid As String) As DataSet
        'Dim dsdoktyp As New DataSet
        'dsdoktyp = Get_DokTyp_Mapping(dokumentid)

        'Try
        '    If dsdoktyp.Tables(0).Rows.Count < 1 Then
        '        PDebug("EDOKA-OnBase-Mapping für " + dokumentid + " nicht möglich")
        '        Exit Function
        '    End If
        'Catch
        '    PDebug("EDOKA-OnBase-Mapping für " + dokumentid + " nicht möglcih")
        '    Exit Function
        'End 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, 2))
        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, ""))
        scmCmdToExecute.Parameters.Add(New SqlParameter("@value2", SqlDbType.VarChar, 255, ParameterDirection.Input, True, 0, 0, "", DataRowVersion.Proposed, ""))
        scmCmdToExecute.Parameters.Add(New SqlParameter("@value3", SqlDbType.VarChar, 255, ParameterDirection.Input, True, 0, 0, "", DataRowVersion.Proposed, ""))
        scmCmdToExecute.Parameters.Add(New SqlParameter("@value4", SqlDbType.VarChar, 255, ParameterDirection.Input, True, 0, 0, "", DataRowVersion.Proposed, ""))
        scmCmdToExecute.Parameters.Add(New SqlParameter("@value5", SqlDbType.VarChar, 255, ParameterDirection.Input, True, 0, 0, "", DataRowVersion.Proposed, ""))
        scmCmdToExecute.Parameters.Add(New SqlParameter("@value6", SqlDbType.VarChar, 255, ParameterDirection.Input, True, 0, 0, "", DataRowVersion.Proposed, ""))
        '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, dsdoktyp.Tables(0).Rows(0).Item(0)))
        'scmCmdToExecute.Parameters.Add(New SqlParameter("@value2", SqlDbType.VarChar, 255, ParameterDirection.Input, True, 0, 0, "", DataRowVersion.Proposed, dsdoktyp.Tables(0).Rows(0).Item(1)))
        'scmCmdToExecute.Parameters.Add(New SqlParameter("@value3", SqlDbType.VarChar, 255, ParameterDirection.Input, True, 0, 0, "", DataRowVersion.Proposed, dsdoktyp.Tables(0).Rows(0).Item(2)))
        'scmCmdToExecute.Parameters.Add(New SqlParameter("@value4", SqlDbType.VarChar, 255, ParameterDirection.Input, True, 0, 0, "", DataRowVersion.Proposed, dsdoktyp.Tables(0).Rows(0).Item(3)))
        'scmCmdToExecute.Parameters.Add(New SqlParameter("@value5", SqlDbType.VarChar, 255, ParameterDirection.Input, True, 0, 0, "", DataRowVersion.Proposed, dsdoktyp.Tables(0).Rows(0).Item(4)))
        'scmCmdToExecute.Parameters.Add(New SqlParameter("@value6", SqlDbType.VarChar, 255, ParameterDirection.Input, True, 0, 0, "", DataRowVersion.Proposed, dsdoktyp.Tables(0).Rows(0).Item(5)))

        scmCmdToExecute.CommandType = CommandType.StoredProcedure
        scmCmdToExecute.Connection = connection
        Try
            sdaAdapter.Fill(ds)
            Return ds
        Catch ex As Exception
            PDebug("Fehler:Get_OnBase_Datastream: " + 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() 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, ""))
            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
                '         MsgBox(ex.Message)
            End Try
        Catch ex As Exception
            Threading.Thread.Sleep(1000)

            '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