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

Public Class Statushandling

#Region "Deklarationen"

    Dim m_aktuellerstatus As Integer
    Dim m_neuerstatus As Integer
    Dim m_abgeschlossen As Boolean
    Dim m_ausgangsarchivierung As Boolean
    Dim m_eingangsarchivierung As Boolean
    Dim m_folgestatus As Boolean
    Dim m_dokumentid As String

    'SHU 2004-12-01
    Dim m_datum_erneute_bearbeitung As DateTime
    Dim m_erneute_bearbeitung As Boolean = False
    Property Erneute_Bearbeitung() As Boolean
        Get
            Return m_erneute_bearbeitung
        End Get
        Set(ByVal Value As Boolean)
            m_erneute_bearbeitung = Value
        End Set
    End Property

    Dim m_erneut_bza As Integer = 0
    Property Erneut_BZA() As Integer
        Get
            Return m_erneut_bza
        End Get
        Set(ByVal Value As Integer)
            m_erneut_bza = Value
        End Set
    End Property


    Property Dokumentid() As String
        Get
            Return m_dokumentid
        End Get
        Set(ByVal Value As String)
            m_dokumentid = Value
        End Set
    End Property

    Property AktullerStatus() As Integer
        Get
            Return m_aktuellerstatus
        End Get
        Set(ByVal Value As Integer)
            m_aktuellerstatus = Value
        End Set
    End Property

    Property NeuerStatus() As Integer
        Get
            Return m_neuerstatus
        End Get
        Set(ByVal Value As Integer)
            m_neuerstatus = Value
        End Set
    End Property

    Property Ablgeschossen() As Boolean
        Get
            Return m_abgeschlossen
        End Get
        Set(ByVal Value As Boolean)
            m_abgeschlossen = Value
        End Set
    End Property

    Property Ausgangsarchivierung() As Boolean
        Get
            Return m_ausgangsarchivierung
        End Get
        Set(ByVal Value As Boolean)
            m_ausgangsarchivierung = Value
        End Set
    End Property
    Property Eingangsarchivierung() As Boolean
        Get
            Return m_eingangsarchivierung
        End Get
        Set(ByVal Value As Boolean)
            m_eingangsarchivierung = Value
        End Set
    End Property
    Property Folgestatus() As Boolean
        Get
            Return m_folgestatus
        End Get
        Set(ByVal Value As Boolean)
            m_folgestatus = Value
        End Set
    End Property
#End Region
    Dim dokumentwerte As DataTable

#Region "Meldungen"

    Public Sub Meldung_Verantwortlicher(ByVal dokumentid As String, ByVal verantwortlicher As Integer)
        Try
            dokumentwerte = GetDokumentwerte(dokumentid, 100, 0)
            insert_Message(1, dokumentid, Meldungstext_aufbereiten, parstext(dokumentwerte.Rows(0).Item("betreff")), 9999, verantwortlicher, 0)
        Catch
        End Try
    End Sub

    Public Sub Meldung_Verantwortlicher_aufhebung(ByVal dokumentid As String, ByVal verantwortlicher As Integer)
        Try
            dokumentwerte = GetDokumentwerte(dokumentid, 110, 0)
            insert_Message(1, dokumentid, Meldungstext_aufbereiten, parstext(dokumentwerte.Rows(0).Item("betreff")), 9999, verantwortlicher, 0)
        Catch
        End Try
    End Sub


    Public Sub Meldung_Status(ByVal dokumentid As String, ByVal status As String)
        Try
            dokumentwerte = GetDokumentwerte(dokumentid, 0, status)
            insert_Message(0, dokumentid, Meldungstext_aufbereiten, parstext(dokumentwerte.Rows(0).Item("betreff")), 9999, 0, status)
        Catch
        End Try
    End Sub

    Public Sub Meldung_Eingang(ByVal dokumentid As String, ByVal status As Integer, ByVal empfaenger As Integer)
        'BUD - 2006.10.05 - Try
        Try
            dokumentwerte = GetDokumentwerte(dokumentid, 104, 0)
            insert_Message(2, dokumentid, Meldungstext_aufbereiten, parstext(dokumentwerte.Rows(0).Item("betreff")), 9999, empfaenger, 0)
        Catch ex As Exception
            m_log.Log("Statushandling:Meldung_Eingang:DokID=" & dokumentid & "::" & ex.Message & ex.StackTrace, Common.Common.JournalEntryType.Warning)
        End Try
    End Sub

    Public Sub Meldung_FehlerVerantwortlicher(ByVal dokumentid As String, ByVal Fehlernr As Integer)
        dokumentwerte = GetDokumentwerte(dokumentid, Fehlernr, 0)
        Try
            insert_Message(3, dokumentid, Meldungstext_aufbereiten, parstext(dokumentwerte.Rows(0).Item("betreff")), 9999, 0, 0)
        Catch
        End Try
    End Sub

    Public Function Meldungstext_aufbereiten() As String
        Dim s As String
        s = ""
        Try
            s = dokumentwerte.Rows(0).Item("meldung") + vbCrLf + dokumentwerte.Rows(0).Item("idvmeldung") + vbCrLf + dokumentwerte.Rows(0).Item("footer")
            s = parstext(s)
        Catch ex As Exception
        End Try
        Meldungstext_aufbereiten = s
    End Function

    Public Function parstext(ByVal s As String) As String
        s = s.Replace("&dokumentid&", dokumentwerte.Rows(0).Item("dokumentid"))
        s = s.Replace("&nrpar00&", dokumentwerte.Rows(0).Item("nrpar00"))
        s = s.Replace("&bkpar00&", dokumentwerte.Rows(0).Item("bkpar00"))
        s = s.Replace("&dokumenttyp&", dokumentwerte.Rows(0).Item("bezeichnung"))
        s = s.Replace("&status&", dokumentwerte.Rows(0).Item("status"))
        s = s.Replace("&statusdatum&", Today)
        s = s.Replace("&verantwortlich&", dokumentwerte.Rows(0).Item("verantwortlich"))
        s = s.Replace("&absender&", dokumentwerte.Rows(0).Item("absender"))
        s = s.Replace("&empfaenger&", dokumentwerte.Rows(0).Item("empfaenger"))
        Return s
    End Function

    Public Function GetDokumentwerte(ByVal Dokumentid As String, ByVal typ As Integer, ByVal status As Integer) As DataTable
        Dim scmCmdToExecute As SqlCommand = New SqlCommand
        Dim dtToReturn As DataTable = New DataTable
        Dim sdaAdapter As SqlDataAdapter = New SqlDataAdapter(scmCmdToExecute)
        scmCmdToExecute.CommandText = "dbo.SP_message_getdokumentwerte"
        scmCmdToExecute.CommandType = CommandType.StoredProcedure
        scmCmdToExecute.Connection = Globals.conn_edoka.scoDBConnection
        Try
            scmCmdToExecute.Parameters.Add(New SqlParameter("@dokumentid", SqlDbType.VarChar, 22, ParameterDirection.Input, True, 10, 0, "", DataRowVersion.Proposed, Dokumentid))
            scmCmdToExecute.Parameters.Add(New SqlParameter("@mitarbeiternr", SqlDbType.VarChar, 22, ParameterDirection.Input, True, 10, 0, "", DataRowVersion.Proposed, 9999))
            scmCmdToExecute.Parameters.Add(New SqlParameter("@typ", SqlDbType.Int, 4, ParameterDirection.Input, True, 10, 0, "", DataRowVersion.Proposed, typ))
            scmCmdToExecute.Parameters.Add(New SqlParameter("@status", SqlDbType.Int, 4, ParameterDirection.Input, True, 10, 0, "", DataRowVersion.Proposed, status))
            sdaAdapter.Fill(dtToReturn)
            Return dtToReturn
        Catch ex As Exception
            'MsgBox(ex.Message)
            ' // some error occured. Bubble it to caller and encapsulate Exception object
            Threading.Thread.Sleep(1000)
            'Throw New Exception("Dokument_Information_Wert::" & scmCmdToExecute.CommandText & "::Error occured." & ex.Message, ex)
            Return Nothing
        Finally
            scmCmdToExecute.Dispose()
            sdaAdapter.Dispose()
        End Try
    End Function

    Public Function insert_Message(ByVal typ As Integer, ByVal Dokumentid As String, ByVal message As String, ByVal betreff As String, _
                                   ByVal absender As Integer, ByVal empfaenger As Integer, ByVal status As Integer) As DataTable
        Dim scmCmdToExecute As SqlCommand = New SqlCommand
        Dim dtToReturn As DataTable = New DataTable
        Dim sdaAdapter As SqlDataAdapter = New SqlDataAdapter(scmCmdToExecute)
        scmCmdToExecute.CommandText = "dbo.SP_message_insert"
        scmCmdToExecute.CommandType = CommandType.StoredProcedure
        scmCmdToExecute.Connection = conn_edoka.scoDBConnection
        Try
            scmCmdToExecute.Parameters.Add(New SqlParameter("@absender", SqlDbType.Int, 4, ParameterDirection.Input, True, 10, 0, "", DataRowVersion.Proposed, absender))
            scmCmdToExecute.Parameters.Add(New SqlParameter("@empfaenger", SqlDbType.Int, 4, ParameterDirection.Input, True, 10, 0, "", DataRowVersion.Proposed, empfaenger))
            scmCmdToExecute.Parameters.Add(New SqlParameter("@betreff", SqlDbType.VarChar, 1024, ParameterDirection.Input, True, 10, 0, "", DataRowVersion.Proposed, betreff))
            scmCmdToExecute.Parameters.Add(New SqlParameter("@meldung", SqlDbType.VarChar, 1024, ParameterDirection.Input, True, 10, 0, "", DataRowVersion.Proposed, message))
            scmCmdToExecute.Parameters.Add(New SqlParameter("@dokumentid", SqlDbType.VarChar, 22, ParameterDirection.Input, True, 10, 0, "", DataRowVersion.Proposed, Dokumentid))
            scmCmdToExecute.Parameters.Add(New SqlParameter("@status", SqlDbType.Int, 4, ParameterDirection.Input, True, 10, 0, "", DataRowVersion.Proposed, status))
            scmCmdToExecute.Parameters.Add(New SqlParameter("@weiterleiten", SqlDbType.Int, 4, ParameterDirection.Input, True, 10, 0, "", DataRowVersion.Proposed, 0))
            scmCmdToExecute.Parameters.Add(New SqlParameter("@typ", SqlDbType.Int, 4, ParameterDirection.Input, True, 10, 0, "", DataRowVersion.Proposed, typ))
            sdaAdapter.Fill(dtToReturn)
            Return dtToReturn
        Catch ex As Exception
            ' // some error occured. Bubble it to caller and encapsulate Exception object
            Throw New Exception("Dokument_Information_Wert::" & scmCmdToExecute.CommandText & "::Error occured." & ex.Message, ex)
        Finally
            scmCmdToExecute.Dispose()
            sdaAdapter.Dispose()
        End Try

    End Function


#End Region

#Region "Datenzugriffe"
    Private Function Generic_Select(ByVal typ As Integer) As DataTable
        Dim scmCmdToExecute As SqlCommand = New SqlCommand()
        Select Case typ
            Case 1
                scmCmdToExecute.CommandText = "dbo.SP_Dokumentstatus_statushandling_Select"
            Case Else
        End Select
        scmCmdToExecute.CommandType = CommandType.StoredProcedure
        Dim dtToReturn As DataTable = New DataTable()
        Dim sdaAdapter As SqlDataAdapter = New SqlDataAdapter(scmCmdToExecute)

        scmCmdToExecute.Connection = conn_edoka.scoDBConnection
        Try
            Select Case typ
                Case 1
                    scmCmdToExecute.Parameters.Add(New SqlParameter("@dokumentid", SqlDbType.VarChar, 22, ParameterDirection.Input, True, 10, 0, "", DataRowVersion.Proposed, Me.Dokumentid))
                Case Else
            End Select
            If typ = 1 Then
            End If
            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()
            sdaAdapter.Dispose()
        End Try
    End Function

#End Region

#Region "Archivierung"

    Public Function Set_Ausgangsarchiviert(ByVal dokumentid As String) As Integer
        Dim da As DataTable
        Dim currentstatus As Integer
        Dim i, i1 As Integer
        Me.Dokumentid = dokumentid
        da = Generic_Select(1)
        currentstatus = da.Rows(0).Item("dokument_statusnr")
        For i = 0 To da.Rows.Count - 1
            If da.Rows(i).Item("status_bezeichnungnr") = 3 Then
                For i1 = 0 To da.Rows.Count - 1
                    If da.Rows(i1).Item("status_bezeichnungnr") = 4 Or _
                       da.Rows(i1).Item("status_bezeichnungnr") = 5 Then
                        'SHU 2004-12-01
                        If Check_Erneute_Bearbeitung(dokumentid) = True Then
                            Me.Erneute_Bearbeitung = True
                            Me.insert_history_status(da.Rows(i1).Item("dokument_statusnr"), dokumentid, 9999, Me.m_datum_erneute_bearbeitung)
                            Me.set_ausgangsarchivierung_ende(dokumentid)
                            Return 0
                            'Return da.Rows(i1).Item("dokument_statusnr")
                        Else
                            Me.insert_history_status(da.Rows(i1).Item("dokument_statusnr"), dokumentid, 9999)
                            Me.set_ausgangsarchivierung_ende(dokumentid)
                            Return da.Rows(i1).Item("dokument_statusnr")
                        End If
                    End If
                Next
            End If
        Next
        da.Dispose()
    End Function

    'SHU 2004-12-01
    Public Function Check_Erneute_Bearbeitung(ByVal dokumentid As String) As Boolean
        Dim scmCmdToExecute As SqlCommand = New SqlCommand
        Dim dtToReturn As DataTable = New DataTable
        Dim sdaAdapter As SqlDataAdapter = New SqlDataAdapter(scmCmdToExecute)
        scmCmdToExecute.CommandText = "dbo.SP_archiv_check_erneute_bearbeitung"
        scmCmdToExecute.CommandType = CommandType.StoredProcedure
        scmCmdToExecute.Connection = conn_edoka.scoDBConnection
        Try
            scmCmdToExecute.Parameters.Add(New SqlParameter("@dokumentid", SqlDbType.VarChar, 22, ParameterDirection.Input, True, 10, 0, "", DataRowVersion.Proposed, dokumentid))
            scmCmdToExecute.Parameters.Add(New SqlParameter("@erneut_bearbeitet", SqlDbType.Int, 4, ParameterDirection.Output, True, 10, 0, "", DataRowVersion.Proposed, 0))
            scmCmdToExecute.Parameters.Add(New SqlParameter("@erneut_bza", SqlDbType.Int, 4, ParameterDirection.Output, True, 10, 0, "", DataRowVersion.Proposed, 0))
            scmCmdToExecute.Parameters.Add(New SqlParameter("@datumzeit", SqlDbType.DateTime, 8, ParameterDirection.Output, True, 10, 0, "", DataRowVersion.Proposed, Now))
            sdaAdapter.Fill(dtToReturn)
            If scmCmdToExecute.Parameters("@erneut_bza").Value = 1 Then
                Me.Erneut_BZA = 1
            Else
                Me.Erneut_BZA = 0
            End If
            If scmCmdToExecute.Parameters("@erneut_bearbeitet").Value > 0 Then
                Me.m_datum_erneute_bearbeitung = scmCmdToExecute.Parameters("@datumzeit").Value
                Return True
            End If
            Return False
        Catch ex As Exception
            ' // some error occured. Bubble it to caller and encapsulate Exception object
            Throw New Exception("Dokument_Information_Wert::" & scmCmdToExecute.CommandText & "::Error occured." & ex.Message, ex)
        Finally
            scmCmdToExecute.Dispose()
            sdaAdapter.Dispose()
        End Try

    End Function

    'SHU 2004-12-01
    Public Sub set_ausgangsarchivierung_ende(ByVal dokumentid As String)
        Dim scmCmdToExecute As SqlCommand = New SqlCommand
        Dim dtToReturn As DataTable = New DataTable
        Dim sdaAdapter As SqlDataAdapter = New SqlDataAdapter(scmCmdToExecute)
        scmCmdToExecute.CommandText = "dbo.SP_archiv_set_ausgangsarchivierung_ende"
        scmCmdToExecute.CommandType = CommandType.StoredProcedure
        scmCmdToExecute.Connection = conn_edoka.scoDBConnection
        Try
            scmCmdToExecute.Parameters.Add(New SqlParameter("@dokumentid", SqlDbType.VarChar, 22, ParameterDirection.Input, True, 10, 0, "", DataRowVersion.Proposed, dokumentid))
            sdaAdapter.Fill(dtToReturn)
        Catch ex As Exception
            Throw New Exception("Dokument_Information_Wert::" & scmCmdToExecute.CommandText & "::Error occured." & ex.Message, ex)
        Finally
            scmCmdToExecute.Dispose()
            sdaAdapter.Dispose()
        End Try
    End Sub

    Public Function Set_Eingangsarchiviert(ByVal dokumentid As String) As Integer
        'BUD - 2006.10.05 - Try
        Try
            Dim da As DataTable
            Dim currentstatus As Integer
            Dim i1 As Integer
            Me.Dokumentid = dokumentid
            da = Generic_Select(1)
            currentstatus = da.Rows(0).Item("dokument_statusnr")
            For i1 = 0 To da.Rows.Count - 1
                If da.Rows(i1).Item("status_bezeichnungnr") = 6 Or _
                da.Rows(i1).Item("status_bezeichnungnr") = 7 Then
                    '               da.Rows(i1).Item("status_bezeichnungnr") = 11 Then
                    Me.insert_history_status(da.Rows(i1).Item("dokument_statusnr"), dokumentid, 9999)
                    Return da.Rows(i1).Item("dokument_statusnr")
                End If
            Next
            da.Dispose()
        Catch ex As Exception
            m_log.Log("Statushandling:Set_Eingangsarchiviert:" & ex.Message & ex.StackTrace, Common.Common.JournalEntryType.Warning)
        End Try
    End Function

    Public Function Set_Eingangsarchiviert(ByVal dokumentid As String, ByVal datum As DateTime) As Integer
        'BUD - 2006.10.05 - Try
        Try
            Dim da As DataTable
            Dim currentstatus As Integer
            Dim i1 As Integer
            Me.Dokumentid = dokumentid
            da = Generic_Select(1)
            currentstatus = da.Rows(0).Item("dokument_statusnr")
            For i1 = 0 To da.Rows.Count - 1
                If da.Rows(i1).Item("status_bezeichnungnr") = 6 Or _
                da.Rows(i1).Item("status_bezeichnungnr") = 7 Then
                    '               da.Rows(i1).Item("status_bezeichnungnr") = 11 Then
                    Me.insert_history_status(da.Rows(i1).Item("dokument_statusnr"), dokumentid, 9999, datum)
                    Return da.Rows(i1).Item("dokument_statusnr")
                End If
            Next
            da.Dispose()
        Catch ex As Exception
            m_log.Log("Statushandling:Set_Eingangsarchiviert_2Param:" & ex.Message & ex.StackTrace, Common.Common.JournalEntryType.Warning)
        End Try
    End Function

    'EDEX Banklagernd
    Public Function Set_Eingangsarchiviert_blindex(ByVal dokumentid As String) As Integer
        Dim da As DataTable
        Dim currentstatus As Integer
        Dim i1 As Integer
        Me.Dokumentid = dokumentid
        da = Generic_Select(1)
        If da.Rows.Count > 0 Then
            currentstatus = da.Rows(0).Item("dokument_statusnr")
            For i1 = 0 To da.Rows.Count - 1
                If da.Rows(i1).Item("status_bezeichnungnr") = 12 Or _
                da.Rows(i1).Item("status_bezeichnungnr") = 13 Then
                    Me.insert_history_status(da.Rows(i1).Item("dokument_statusnr"), dokumentid, 9999)
                    Return da.Rows(i1).Item("dokument_statusnr")
                End If
            Next
        End If
        da.Dispose()
    End Function

#End Region
    Dim ds As New edokadb.clsDokument_status

    Public Sub Dispose()
        ds.Dispose()
    End Sub

    Public Sub Check_Neuer_Status()
        ds.cpMainConnectionProvider = conn_edoka
        ds.iDokument_statusnr = New SqlInt32(CType(Me.NeuerStatus, Int32))
        ds.SelectOne()

        If ds.bFolgestatus_durch_anderen_verantwortlichen.Value = True Then
            Me.Folgestatus = True
        End If
        Me.Ausgangsarchivierung = False
        If ds.iStatus_bezeichnungnr.Value = 3 Or ds.bDokument_ausgangsarchivierung.Value = True Then
            Me.Ausgangsarchivierung = True
        End If
        If ds.bDokument_bearbeitung_abgeschlossen.Value = True Then
            Me.Ablgeschossen = True
        End If
    End Sub

    Public Sub insert_history_status(ByVal statusnr As Integer, ByVal dokumentid As String, ByVal Verantwortlicher As Integer)
        Try
            Dim sh As New edokadb.clsStatushistory
            Dim dbkey As New edokadb.clsMyKey_Tabelle
            Dim key As Long
            dbkey.cpMainConnectionProvider = conn_edoka
            conn_edoka.OpenConnection()
            key = dbkey.get_dbkey("statushistory")
            Try
                conn_edoka.CloseConnection(True)
            Catch
            End Try
            sh.cpMainConnectionProvider = conn_edoka
            sh.iStatushistorynr = New SqlInt32(CType(key, Int32))
            sh.iStatus = New SqlInt32(CType(statusnr, Int32))
            sh.iMandantnr = New SqlInt32(CType(1, Int32))
            sh.iMutierer = New SqlInt32(CType(9999, Int32))
            sh.bAktiv = New SqlBoolean(True)
            sh.daErstellt_am = New SqlDateTime(CType(Now, DateTime))
            sh.daMutiert_am = New SqlDateTime(CType(Now, DateTime))
            sh.sDokumentid = New SqlString(CType(dokumentid, String))
            sh.iVerantwortlich = New SqlInt32(CType(Verantwortlicher, Int32))
            conn_edoka.OpenConnection()
            sh.Insert()
            conn_edoka.CloseConnection(True)
            dbkey.Dispose()
            sh.Dispose()
        Catch ex As Exception
            Threading.Thread.Sleep(1000)

            'MsgBox(ex.Message)
        End Try

    End Sub

    Public Sub insert_history_status(ByVal statusnr As Integer, ByVal dokumentid As String, ByVal Verantwortlicher As Integer, ByVal datum As DateTime)
        Try
            Dim sh As New edokadb.clsStatushistory
            Dim dbkey As New edokadb.clsMyKey_Tabelle
            Dim key As Long
            dbkey.cpMainConnectionProvider = conn_edoka
            conn_edoka.OpenConnection()
            key = dbkey.get_dbkey("statushistory")
            Try
                conn_edoka.CloseConnection(True)
            Catch
            End Try
            sh.cpMainConnectionProvider = conn_edoka
            sh.iStatushistorynr = New SqlInt32(CType(key, Int32))
            sh.iStatus = New SqlInt32(CType(statusnr, Int32))
            sh.iMandantnr = New SqlInt32(CType(1, Int32))
            sh.iMutierer = New SqlInt32(CType(9999, Int32))
            sh.bAktiv = New SqlBoolean(True)
            sh.daErstellt_am = New SqlDateTime(CType(datum, DateTime))
            sh.daMutiert_am = New SqlDateTime(CType(datum, DateTime))
            sh.sDokumentid = New SqlString(CType(dokumentid, String))
            sh.iVerantwortlich = New SqlInt32(CType(Verantwortlicher, Int32))
            conn_edoka.OpenConnection()
            sh.Insert()
            conn_edoka.CloseConnection(True)
            dbkey.Dispose()
            sh.Dispose()
        Catch ex As Exception
            Threading.Thread.Sleep(1000)

            'MsgBox(ex.Message)
        End Try

    End Sub

    Public Function Alle_Status(ByVal Dokumentid As String) As DataTable
        Dim scmCmdToExecute As SqlCommand = New SqlCommand
        Dim dtToReturn As DataTable = New DataTable
        Dim sdaAdapter As SqlDataAdapter = New SqlDataAdapter(scmCmdToExecute)
        scmCmdToExecute.CommandText = "dbo.SP_Trefferliste_Select_Status"
        scmCmdToExecute.CommandType = CommandType.StoredProcedure
        scmCmdToExecute.Connection = conn_edoka.scoDBConnection
        Try
            scmCmdToExecute.Parameters.Add(New SqlParameter("@dokumentid", SqlDbType.VarChar, 22, ParameterDirection.Input, True, 10, 0, "", DataRowVersion.Proposed, ""))
            sdaAdapter.Fill(dtToReturn)
            Return dtToReturn
        Catch ex As Exception
            ' // some error occured. Bubble it to caller and encapsulate Exception object
            Throw New Exception("Dokument_Information_Wert::" & scmCmdToExecute.CommandText & "::Error occured." & ex.Message, ex)
        Finally
            scmCmdToExecute.Dispose()
            sdaAdapter.Dispose()
        End Try

    End Function

    Public Function Status_Erstellen(ByVal dokumentid As String) As DataTable
        Dim scmCmdToExecute As SqlCommand = New SqlCommand
        Dim dtToReturn As DataTable = New DataTable
        Dim sdaAdapter As SqlDataAdapter = New SqlDataAdapter(scmCmdToExecute)
        scmCmdToExecute.CommandText = "dbo.sp_dokumentberabeigung_status_erstellen"
        scmCmdToExecute.CommandType = CommandType.StoredProcedure
        scmCmdToExecute.Connection = conn_edoka.scoDBConnection
        Try
            scmCmdToExecute.Parameters.Add(New SqlParameter("@dokumentid", SqlDbType.VarChar, 22, ParameterDirection.Input, True, 10, 0, "", DataRowVersion.Proposed, dokumentid))
            scmCmdToExecute.Parameters.Add(New SqlParameter("@bck", SqlDbType.Int, 4, ParameterDirection.Input, True, 10, 0, "", DataRowVersion.Proposed, 0))
            sdaAdapter.Fill(dtToReturn)
            Return dtToReturn
        Catch ex As Exception
            ' // some error occured. Bubble it to caller and encapsulate Exception object
            Throw New Exception("Dokument_Information_Wert::" & scmCmdToExecute.CommandText & "::Error occured." & ex.Message, ex)
        Finally
            scmCmdToExecute.Dispose()
            sdaAdapter.Dispose()
        End Try
    End Function

    Public Function check_abschluss(ByVal dokumentid As String, ByVal mitarbeiternr As Integer) As DataTable
        Dim scmCmdToExecute As SqlCommand = New SqlCommand
        Dim i As Integer
        Dim dtToReturn As DataTable = New DataTable
        Dim sdaAdapter As SqlDataAdapter = New SqlDataAdapter(scmCmdToExecute)
        scmCmdToExecute.CommandText = "dbo.sp_dokumentberarbeitung_abschluss"
        scmCmdToExecute.CommandType = CommandType.StoredProcedure
        scmCmdToExecute.Connection = conn_edoka.scoDBConnection
        Try
            scmCmdToExecute.Parameters.Add(New SqlParameter("@dokumentid", SqlDbType.VarChar, 22, ParameterDirection.Input, True, 10, 0, "", DataRowVersion.Proposed, dokumentid))
            scmCmdToExecute.Parameters.Add(New SqlParameter("@abschlussstatus", SqlDbType.Int, 4, ParameterDirection.Output, True, 10, 0, "", DataRowVersion.Proposed, 0))
            sdaAdapter.Fill(dtToReturn)
            i = scmCmdToExecute.Parameters.Item("@abschlussstatus").Value
            If i <> 0 Then
                insert_history_status(i, dokumentid, mitarbeiternr)
                If Globals.Excellib.Check_Excel(dokumentid) = True Then
                    Globals.Excellib.Get_Excel_Values(dokumentid)
                End If
                Dokument_Abschliessen(dokumentid, i)

            End If

            Return dtToReturn
        Catch ex As Exception
            Threading.Thread.Sleep(1000)
            'MsgBox(ex.Message)
            ' // some error occured. Bubble it to caller and encapsulate Exception object
            Throw New Exception("Dokument_Information_Wert::" & scmCmdToExecute.CommandText & "::Error occured." & ex.Message, ex)
        Finally
            scmCmdToExecute.Dispose()
            sdaAdapter.Dispose()
        End Try
    End Function

    'Public Function Dokument_Abschliessen(ByVal dokumentid As String, ByVal status As Integer)
    '    Dim dok As New edokadb.clsDokument()
    '    dok.cpMainConnectionProvider = conn_edoka

    '    dok.sDokumentid = New SqlString(CType(dokumentid, String))
    '    Try
    '        dok.SelectOne()
    '        conn_edoka.OpenConnection()
    '        dok.iStatusnr = New SqlInt32(CType(status, Integer))
    '        dok.iVerantwortlich = New SqlInt32(CType(9999, Integer))
    '        dok.Update()
    '        conn_edoka.CloseConnection(True)
    '        Meldung_Status(dokumentid, status)
    '    Catch ex As Exception
    '        MsgBox(ex.Message)
    '    End Try
    'End Function

    Public Sub Dokument_Abschliessen(ByVal dokumentid As String, ByVal status As Integer)
        'Excel-Werte auslesen und Dokumentwerge befüllen
        Excel_Werte_Auslesen(dokumentid)
        Dim dok As New edokadb.clsDokument
        Dim dt As New edokadb.clsDokumenttyp
        Dim par As New edokadb.clsPartner
        Dim loeschung As Boolean = False
        Dim verantwortlich As Integer = 0

        dok.cpMainConnectionProvider = conn_edoka
        par.cpMainConnectionProvider = conn_edoka
        dt.cpMainConnectionProvider = conn_edoka

        dok.sDokumentid = New SqlString(CType(dokumentid, String))
        Try
            dok.SelectOne()

            par.iNRPAR00 = New SqlInt32(CType(dok.iNrpar00.Value, Int32))
            par.SelectOne()
            If par.bSaldiert.Value = True Then
                'Meldung_Aufhebung(dok.sDokumentid.Value, Globals.MitarbeiterNr)
                loeschung = True
            End If

            conn_edoka.OpenConnection()
            dok.iStatusnr = New SqlInt32(CType(status, Integer))
            verantwortlich = dok.iVerantwortlich.Value
            dok.iVerantwortlich = New SqlInt32(CType(9999, Integer))
            dok.Update()
            conn_edoka.CloseConnection(True)
            Meldung_Status(dokumentid, status)

            dt.iDokumenttypnr = New SqlInt32(CType(dok.iDokumenttypnr.Value, Int32))
            dt.SelectOne()
            If dt.bVertrag.Value = True Then
                If loeschung Then
                    If loeschart(dok.sDokumentid.Value) = 1 Then
                        Insert_Note(dok.sDokumentid.Value, "Dokument automatisch aufgehoben, da Partner saldiert ist.")
                        set_aufgehoben(dok.sDokumentid.Value)
                    Else
                        Insert_Note(dok.sDokumentid.Value, "Dokument automatisch gelöscht, da Partner saldiert ist.")
                        set_geloescht(dok.sDokumentid.Value)
                    End If
                    Me.Meldung_Verantwortlicher_aufhebung(dok.sDokumentid.Value, verantwortlich)
                End If
            End If
        Catch ex As Exception
            'MsgBox(ex.Message)
        End Try
        Try
            dok.Dispose()
            par.Dispose()
            dt.Dispose()
        Catch
        End Try
    End Sub

    Sub Excel_Werte_Auslesen(ByVal Dokumentid As String)

    End Sub
    Private Function loeschart(ByVal dokid As String) As Integer
        Dim scmCmdToExecute As SqlCommand = New SqlCommand
        scmCmdToExecute.CommandText = "dbo.SP_check_aufhebung_loeschung"
        scmCmdToExecute.CommandType = CommandType.StoredProcedure
        scmCmdToExecute.Connection = conn_edoka.scoDBConnection
        conn_edoka.OpenConnection()
        Try
            scmCmdToExecute.Parameters.Add(New SqlParameter("@dokumentid", SqlDbType.VarChar, 22, ParameterDirection.Input, True, 10, 0, "", DataRowVersion.Proposed, dokid))
            scmCmdToExecute.Parameters.Add(New SqlParameter("@res", SqlDbType.Int, 4, ParameterDirection.Output, True, 10, 0, "", DataRowVersion.Proposed, 0))
            scmCmdToExecute.ExecuteNonQuery()
            Return scmCmdToExecute.Parameters("@res").Value
        Catch ex As Exception
            Throw New Exception("Dokument_Information_Wert::" & scmCmdToExecute.CommandText & "::Error occured." & ex.Message, ex)
        Finally
            scmCmdToExecute.Dispose()
            conn_edoka.CloseConnection(True)
        End Try
    End Function

    Private Sub set_geloescht(ByVal dokid As String)
        Dim archivfnkt As New EDOKA.clsarchivfnkt
        Dim scmCmdToExecute As SqlCommand = New SqlCommand
        scmCmdToExecute.CommandText = "dbo.SP_Dokument_delete"
        scmCmdToExecute.CommandType = CommandType.StoredProcedure
        scmCmdToExecute.Connection = conn_edoka.scoDBConnection
        conn_edoka.OpenConnection()
        Try
            scmCmdToExecute.Parameters.Add(New SqlParameter("@dokumentid", SqlDbType.VarChar, 22, ParameterDirection.Input, True, 10, 0, "", DataRowVersion.Proposed, dokid))
            scmCmdToExecute.Parameters.Add(New SqlParameter("@mitarbeiter", SqlDbType.Int, 4, ParameterDirection.Input, True, 10, 0, "", DataRowVersion.Proposed, 9999))
            scmCmdToExecute.Parameters.Add(New SqlParameter("@loeschgrund", SqlDbType.VarChar, 1024, ParameterDirection.Input, True, 10, 0, "", DataRowVersion.Proposed, "Gelöscht, da Partner saldiert ist"))
            scmCmdToExecute.ExecuteNonQuery()
        Catch ex As Exception
            Throw New Exception("Dokument_Information_Wert::" & scmCmdToExecute.CommandText & "::Error occured." & ex.Message, ex)
        Finally
            scmCmdToExecute.Dispose()
            conn_edoka.CloseConnection(True)
            Dim dt As DataTable
            dt = get_coldindex_and_statusnr(dokid, False, False)
            archivfnkt.insert_coldupdate_status(dt, dokid, "Alt")
        End Try
        archivfnkt = Nothing
    End Sub

    Public Sub set_aufgehoben(ByVal dokumentid As String)
        Dim archivfnkt As New EDOKA.clsarchivfnkt
        Dim dt As DataTable
        dt = get_coldindex_and_statusnr(dokumentid, True, False)
        archivfnkt.insert_coldupdate_status(dt, dokumentid, "Alt")
        archivfnkt = Nothing
    End Sub

    Public Function get_coldindex_and_statusnr(ByVal dokumentid As String, ByVal aufheben As Boolean, ByVal reaktivieren As Boolean) As DataTable
        Dim scmCmdToExecute As SqlCommand = New SqlCommand
        Dim i As Integer
        Dim dtToReturn As DataTable = New DataTable
        Dim sdaAdapter As SqlDataAdapter = New SqlDataAdapter(scmCmdToExecute)
        scmCmdToExecute.CommandText = "dbo.sp_get_coldindex_and_aufhebungsstatus"
        scmCmdToExecute.CommandType = CommandType.StoredProcedure
        scmCmdToExecute.Connection = conn_edoka.scoDBConnection
        Try
            scmCmdToExecute.Parameters.Add(New SqlParameter("@dokumentid", SqlDbType.VarChar, 22, ParameterDirection.Input, True, 10, 0, "", DataRowVersion.Proposed, dokumentid))
            scmCmdToExecute.Parameters.Add(New SqlParameter("@reaktivieren", SqlDbType.Int, 4, ParameterDirection.Input, True, 10, 0, "", DataRowVersion.Proposed, reaktivieren))
            scmCmdToExecute.Parameters.Add(New SqlParameter("@aufhebungsstatus", SqlDbType.Int, 4, ParameterDirection.Output, True, 10, 0, "", DataRowVersion.Proposed, 0))
            sdaAdapter.Fill(dtToReturn)
            i = scmCmdToExecute.Parameters.Item("@aufhebungsstatus").Value
            If (i <> 0 And aufheben) Or (i <> 0 And reaktivieren) Then
                insert_history_status(i, dokumentid, 9999)
                Dim d As New edokadb.clsDokument
                d.cpMainConnectionProvider = conn_edoka
                d.sDokumentid = New SqlString(CType(dokumentid, String))
                d.SelectOne()
                d.iStatusnr = New SqlInt32(CType(i, Int32))
                conn_edoka.OpenConnection()
                d.Update()
                conn_edoka.CloseConnection(True)
                d.Dispose()
            End If
            Return dtToReturn
        Catch ex As Exception
            Threading.Thread.Sleep(1000)
            'MsgBox(ex.Message)
            ' // some error occured. Bubble it to caller and encapsulate Exception object
            Throw New Exception("Dokument_Information_Wert::" & scmCmdToExecute.CommandText & "::Error occured." & ex.Message, ex)
        Finally
            scmCmdToExecute.Dispose()
            sdaAdapter.Dispose()
        End Try
    End Function

    Private Sub Insert_Note(ByVal dokumentid As String, ByVal meldung As String)
        Dim scmCmdToExecute As SqlCommand = New SqlCommand
        scmCmdToExecute.CommandText = "dbo.SP_Dokument_Notizen"
        scmCmdToExecute.CommandType = CommandType.StoredProcedure
        scmCmdToExecute.Connection = conn_edoka.scoDBConnection
        scmCmdToExecute.Connection.Open()
        Try
            scmCmdToExecute.Parameters.Add(New SqlParameter("@dokumentid", SqlDbType.VarChar, 22, ParameterDirection.Input, True, 10, 0, "", DataRowVersion.Proposed, dokumentid))
            scmCmdToExecute.Parameters.Add(New SqlParameter("@notiznr", SqlDbType.Int, 4, ParameterDirection.Input, True, 10, 0, "", DataRowVersion.Proposed, 0))
            scmCmdToExecute.Parameters.Add(New SqlParameter("@betreff", SqlDbType.VarChar, 255, ParameterDirection.Input, True, 10, 0, "", DataRowVersion.Proposed, meldung))
            scmCmdToExecute.Parameters.Add(New SqlParameter("@value", SqlDbType.VarChar, 1024, ParameterDirection.Input, True, 10, 0, "", DataRowVersion.Proposed, meldung))
            scmCmdToExecute.Parameters.Add(New SqlParameter("@aktiv", SqlDbType.Int, 4, ParameterDirection.Input, True, 10, 0, "", DataRowVersion.Proposed, 1))
            scmCmdToExecute.ExecuteNonQuery()
        Catch ex As Exception
            'MsgBox(ex.Message)
            ' // some error occured. Bubble it to caller and encapsulate Exception object
            Threading.Thread.Sleep(1000)

            'Throw New Exception("Dokument_Information_Wert::" & scmCmdToExecute.CommandText & "::Error occured." & ex.Message, ex)
        Finally
            scmCmdToExecute.Connection.Close()
            scmCmdToExecute.Dispose()
        End Try
    End Sub


End Class