Imports System.Data.SqlTypes
Imports System.Data.SqlClient
Imports DevComponents
Public Class clsPruefschritt
    Inherits DB.clsPruefschritt


#Region "Deklarationen"
    Public daten As New DataTable
    Public Neuer_Datensatz As Boolean = False
    Public Mitarbeiterdaten As New DataTable
    Public PruefschrittResultattext As New DataTable

    Dim stammdaten As New DB.clsStammdaten
    Dim SpaltenTitel As New Utils.Tabellenspalte

    Public Pruefschrittstatus As DataTable

    Dim mMutierer As String
    Property MutiererText() As String
        Get
            Return mMutierer
        End Get
        Set(ByVal value As String)
            mMutierer = value
        End Set
    End Property
#End Region


    Sub New()
        MyBase.New()
        Pruefschrittstatus = stammdaten.Get_Stammdaten("pruefschrittstatus", "")
        PruefschrittResultattext = stammdaten.Get_Stammdaten("PruefschrittResultatText", "PruefschrittTextnr")

    End Sub

    Public Overloads Sub dispose()
        MyBase.Dispose()
        Try
            Me.stammdaten = Nothing
            Me.Pruefschrittstatus.Dispose()
        Catch
        End Try
    End Sub

    ''' <summary>
    ''' Mutierer auslesen
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function Get_Mutierer(ByVal nr As Integer) As String
        Try
            Dim ma As New DB.clsMitarbeiter
            Dim dt As New DataTable
            Dim Retvalue As String
            ma.cpMainConnectionProvider = Globals.conn
            ma.iMitarbeiternr = New SqlInt32(CType(nr, Int32))
            dt = ma.SelectOne()
            If dt.Rows.Count = 0 Then
                Retvalue = ("{" + nr.ToString + "}")
            Else
                Retvalue = ma.sName.ToString + " " + ma.sVorname.ToString + ", " + ma.sTgnummer.ToString
            End If
            ma.Dispose()
            dt.Dispose()
            Return Retvalue
        Catch
            Return ""
        End Try

    End Function


    Public Function Get_Data(ByVal Nr As Integer)
        Me.cpMainConnectionProvider = Globals.conn
        Me.iPruefschrittNr = New SqlInt32(CType(Nr, Int32))
        Globals.conn.OpenConnection()
        Me.daten = Me.SelectOne()
        Globals.conn.CloseConnection(True)
        Try
            Me.MutiererText = Get_Mutierer(Me.iMutierer.Value)
        Catch ex As Exception
            Me.MutiererText = ""

        End Try

    End Function

    Public Function Save_Data() As Integer
        Me.cpMainConnectionProvider = Globals.conn
        Me.iMutierer = New SqlInt32(CType(Globals.clsmitarbeiter.iMitarbeiternr, Int32))
        Me.daMutiert_am = New SqlDateTime(CType(Now, DateTime))
        Globals.conn.OpenConnection()
        Me.Update()
        Globals.conn.CloseConnection(True)
        Me.Neuer_Datensatz = False
    End Function


    ''' <summary>
    ''' Löschen eines Datensatzes erstellen.
    ''' </summary>
    ''' <param name="Basenr">Ursprungs-Person: Ist dieser Wert nicht 0, werden die Daten mit BaseNr zuerst gelesen</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function Delete_Data(Optional ByVal Basenr As Integer = 0) As Integer
        If Basenr <> 0 Then
            Get_Data(Basenr)
        End If
        If bAktiv.Value = False Then Exit Function

        Me.cpMainConnectionProvider = Globals.conn
        Me.bAktiv = New SqlBoolean(CType(False, Boolean))
        Me.daMutiert_am = New SqlDateTime(CType(Now, DateTime))
        Me.iMutierer = New SqlInt32(CType(Globals.clsmitarbeiter.iMitarbeiternr, Int32))
        Globals.conn.OpenConnection()
        Me.Update()
        Globals.conn.CloseConnection(True)
        Me.Neuer_Datensatz = False
    End Function

    Public Overloads Function Delete(Optional ByVal Basenr As Integer = 0) As Integer
        If Basenr <> 0 Then
            Get_Data(Basenr)
        End If
        Me.cpMainConnectionProvider = Globals.conn
        Globals.conn.OpenConnection()
        MyBase.Delete()
        Globals.conn.CloseConnection(True)
        Me.Neuer_Datensatz = False
    End Function

    ''' <summary>
    ''' Neue Person einfügen
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function Add_New() As Integer
        Dim db As New DB.clsMyKey_Tabelle
        db.cpMainConnectionProvider = Globals.conn
        Dim newkey = db.get_dbkey("Pruefschritt")
        db.Dispose()
        Me.bAktiv = New SqlBoolean(CType(True, Boolean))
        Me.daErstellt_am = New SqlDateTime(CType(Now, DateTime))
        Me.daMutiert_am = New SqlDateTime(CType(Now, DateTime))
        Me.iMutierer = New SqlInt32(CType(Globals.clsmitarbeiter.iMitarbeiternr, Int32))
        Me.cpMainConnectionProvider = Globals.conn
        Try
            Globals.conn.OpenConnection()
            Me.Insert()
            Globals.conn.CloseConnection(True)
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        Me.Neuer_Datensatz = True
        Return Me.iPruefschrittNr.Value
    End Function

    Public Function Get_Eintraege(ByRef c1daten As C1.Win.C1TrueDBGrid.C1TrueDBGrid, ByVal Mitarbeiternr As Integer, ByRef tree As DevComponents.AdvTree.AdvTree)
        Try
            Dim ds As New DataSet
            Dim connection As New SqlConnection()
            Dim da As New SqlDataAdapter("", connection)
            Dim sqlcmd As New SqlCommand

            sqlcmd.CommandText = "sp_get_pruefschritt"
            sqlcmd.Parameters.Add(New SqlParameter("@manr", SqlDbType.Int, 4, ParameterDirection.Input, True, 10, 0, "", DataRowVersion.Proposed, Mitarbeiternr))
            sqlcmd.CommandType = CommandType.StoredProcedure
            sqlcmd.Connection = connection
            Try
                connection.ConnectionString = Globals.sConnectionString
                connection.Open()
                da.SelectCommand = sqlcmd
                da.Fill(ds, "Datatable1")
                Dim status As New clsStatus
                c1daten.DataSource = Nothing
                c1daten.DataSource = ds.Tables(0)
                c1daten.DataMember = ds.Tables(0).TableName
                SpaltenTitel.Spaltentitel_aktualisieren(c1daten, "Pruefschritt", ds.Tables(0))

                Dim intarray(Me.Pruefschrittstatus.Rows.Count) As Integer
                For i As Integer = 1 To Me.Pruefschrittstatus.Rows.Count
                    intarray(i) = 0
                Next
                For Each r As DataRow In ds.Tables(0).Rows
                    intarray(r.Item("Pruefschrittstatusnr")) = intarray(r.Item("Pruefschrittstatusnr")) + 1
                Next

                For Each n As DevComponents.AdvTree.Node In tree.Nodes
                    n.Text = n.Text + " (" + intarray(n.Tag).ToString + ")"

                Next

            Catch ex As Exception
                MsgBox(ex.Message)
            Finally
                connection.Close()
                da.Dispose()
                sqlcmd.Dispose()
            End Try
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Function

    Public Function Get_Vorgaben(ByRef c1daten As C1.Win.C1TrueDBGrid.C1TrueDBGrid, ByVal key As Integer)
        Dim ds As New DataSet
        Dim connection As New SqlConnection()
        Dim da As New SqlDataAdapter("", connection)
        Dim sqlcmd As New SqlCommand

        sqlcmd.CommandText = "sp_get_vorgabedaten"
        sqlcmd.Parameters.Add(New SqlParameter("@key", SqlDbType.Int, 4, ParameterDirection.Input, True, 10, 0, "", DataRowVersion.Proposed, key))
        sqlcmd.CommandType = CommandType.StoredProcedure
        sqlcmd.Connection = connection
        Try
            connection.ConnectionString = Globals.sConnectionString
            connection.Open()
            da.SelectCommand = sqlcmd
            da.Fill(ds, "Datatable1")
            Dim status As New clsStatus
            c1daten.DataSource = Nothing
            c1daten.DataSource = ds.Tables(0)
            c1daten.DataMember = ds.Tables(0).TableName
            SpaltenTitel.Spaltentitel_aktualisieren(c1daten, "_Pruefschritt_Vorgaben", ds.Tables(0))


        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            connection.Close()
            da.Dispose()
            sqlcmd.Dispose()
        End Try


    End Function

    Public Sub Get_Massnahmen(ByRef tree As DevComponents.AdvTree.AdvTree)

        Dim ds As New DataSet
        Dim connection As New SqlConnection()
        Dim da As New SqlDataAdapter("", connection)
        Dim sqlcmd As New SqlCommand

        sqlcmd.CommandText = "sp_get_massnahme_tree"
        sqlcmd.Parameters.Add(New SqlParameter("@ma", SqlDbType.Int, 4, ParameterDirection.Input, True, 10, 0, "", DataRowVersion.Proposed, Globals.clsmitarbeiter.iMitarbeiternr.Value))
        sqlcmd.Parameters.Add(New SqlParameter("@node1", SqlDbType.VarChar, 255, ParameterDirection.Output, True, 10, 0, "", DataRowVersion.Proposed, ""))
        sqlcmd.Parameters.Add(New SqlParameter("@node2", SqlDbType.VarChar, 255, ParameterDirection.Output, True, 10, 0, "", DataRowVersion.Proposed, ""))
        sqlcmd.Parameters.Add(New SqlParameter("@node3", SqlDbType.VarChar, 255, ParameterDirection.Output, True, 10, 0, "", DataRowVersion.Proposed, ""))
        sqlcmd.CommandType = CommandType.StoredProcedure
        sqlcmd.Connection = connection
        Try
            connection.ConnectionString = Globals.sConnectionString
            connection.Open()
            sqlcmd.ExecuteNonQuery()
            Try
                tree.Nodes.Clear()
            Catch
            End Try

            Dim i As Integer
            For i = 0 To 2
                Dim tn As New DevComponents.AdvTree.Node
                tn.ImageIndex = i
                tn.Tag = i
                tn.Text = sqlcmd.Parameters(i + 1).Value
                tree.Nodes.Add(tn)
            Next



        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            connection.Close()
            da.Dispose()
            sqlcmd.Dispose()
        End Try
    End Sub

    Public Sub Get_Massnahmen_Data(ByRef c1daten As C1.Win.C1TrueDBGrid.C1TrueDBGrid, typ As Integer)
        Try
            Dim ds As New DataSet
            Dim connection As New SqlConnection()
            Dim da As New SqlDataAdapter("", connection)
            Dim sqlcmd As New SqlCommand

            sqlcmd.CommandText = "sp_get_pruefschritt_massnahmen"
            sqlcmd.Parameters.Add(New SqlParameter("@manr", SqlDbType.Int, 4, ParameterDirection.Input, True, 10, 0, "", DataRowVersion.Proposed, Globals.clsmitarbeiter.iMitarbeiternr.Value))
            sqlcmd.Parameters.Add(New SqlParameter("@typ", SqlDbType.Int, 4, ParameterDirection.Input, True, 10, 0, "", DataRowVersion.Proposed, typ))

            sqlcmd.CommandType = CommandType.StoredProcedure
            sqlcmd.Connection = connection
            Try
                connection.ConnectionString = Globals.sConnectionString
                connection.Open()
                da.SelectCommand = sqlcmd
                da.Fill(ds, "Datatable1")
                Dim status As New clsStatus
                c1daten.DataSource = Nothing
                c1daten.DataSource = ds.Tables(0)
                c1daten.DataMember = ds.Tables(0).TableName
                SpaltenTitel.Spaltentitel_aktualisieren(c1daten, "Pruefschritt", ds.Tables(0))

                'Dim intarray(Me.Pruefschrittstatus.Rows.Count) As Integer
                'For i As Integer = 1 To Me.Pruefschrittstatus.Rows.Count
                '    intarray(i) = 0
                'Next
                'For Each r As DataRow In ds.Tables(0).Rows
                '    intarray(r.Item("Pruefschrittstatusnr")) = intarray(r.Item("Pruefschrittstatusnr")) + 1
                'Next

                'For Each n As DevComponents.AdvTree.Node In tree.Nodes
                '    n.Text = n.Text + " (" + intarray(n.Tag).ToString + ")"

                'Next

            Catch ex As Exception
                MsgBox(ex.Message)
            Finally
                connection.Close()
                da.Dispose()
                sqlcmd.Dispose()
            End Try
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Public Sub Insert_Message(ByVal Typ As Integer, pruefschrittnr As Integer, Empfaenger As Integer, Urspruenglicher_MA As Integer, Optional Notiz As String = "")
        Try
            Dim ds As New DataSet
            Dim connection As New SqlConnection()
            Dim da As New SqlDataAdapter("", connection)
            Dim sqlcmd As New SqlCommand

            sqlcmd.CommandText = "sp_insert_message"
            sqlcmd.Parameters.Add(New SqlParameter("@type", SqlDbType.Int, 4, ParameterDirection.Input, True, 10, 0, "", DataRowVersion.Proposed, Typ))
            sqlcmd.Parameters.Add(New SqlParameter("@pruefschrittnr", SqlDbType.Int, 4, ParameterDirection.Input, True, 10, 0, "", DataRowVersion.Proposed, pruefschrittnr))
            sqlcmd.Parameters.Add(New SqlParameter("@empfaenger", SqlDbType.Int, 4, ParameterDirection.Input, True, 10, 0, "", DataRowVersion.Proposed, Empfaenger))
            sqlcmd.Parameters.Add(New SqlParameter("@absender", SqlDbType.Int, 4, ParameterDirection.Input, True, 10, 0, "", DataRowVersion.Proposed, Urspruenglicher_MA))
            sqlcmd.Parameters.Add(New SqlParameter("@Notiz", SqlDbType.VarChar, 1024, ParameterDirection.Input, True, 10, 0, "", DataRowVersion.Proposed, Notiz))


            sqlcmd.CommandType = CommandType.StoredProcedure
            sqlcmd.Connection = connection
            Try
                connection.ConnectionString = Globals.sConnectionString
                connection.Open()
                sqlcmd.ExecuteNonQuery()

            Catch ex As Exception
                MsgBox(ex.Message)
            Finally
                connection.Close()
                da.Dispose()
                sqlcmd.Dispose()
            End Try
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Public Function Is_IsiMitarbeiter(ByVal Mitarbeiternr As Integer) As Boolean
        Dim Verantwortungisi As New DataTable
        Verantwortungisi = stammdaten.Get_Stammdaten("VerantwortungIsi", "Bezeichnung")
        For Each r As DataRow In Verantwortungisi.Rows
            If r.Item(0) = Mitarbeiternr Then
                Return True
                Exit For
            End If
        Next
        Return False
    End Function

End Class

Public Class clsPruefschrittStatus
    Inherits DB.clsPruefSchrittStatus


End Class

Public Class clsPruefschrittNotiz
    Inherits DB.clsPruefschrittnotiz

    Public daten As New DataTable
    Public Neuer_Datensatz As Boolean = False
    Public Mitarbeiterdaten As New DataTable

    Dim stammdaten As New DB.clsStammdaten
    Dim SpaltenTitel As New Utils.Tabellenspalte


    Dim mMutierer As String

    Sub New()
    End Sub
    Property MutiererText() As String
        Get
            Return mMutierer
        End Get
        Set(ByVal value As String)
            mMutierer = value
        End Set
    End Property

    Public Function Get_Mutierer(ByVal nr As Integer) As String
        Try
            Dim ma As New DB.clsMitarbeiter
            Dim dt As New DataTable
            Dim Retvalue As String
            ma.cpMainConnectionProvider = Globals.conn
            ma.iMitarbeiternr = New SqlInt32(CType(nr, Int32))
            dt = ma.SelectOne()
            If dt.Rows.Count = 0 Then
                Retvalue = ("{" + nr.ToString + "}")
            Else
                Retvalue = ma.sName.ToString + " " + ma.sVorname.ToString + ", " + ma.sTgnummer.ToString
            End If
            ma.Dispose()
            dt.Dispose()
            Return Retvalue
        Catch
            Return ""
        End Try

    End Function


    Public Function Get_Data(ByVal Nr As Integer)
        Me.cpMainConnectionProvider = Globals.conn
        Me.iPruefschrittnr = New SqlInt32(CType(Nr, Int32))
        Globals.conn.OpenConnection()
        Me.daten = Me.SelectOne()
        Globals.conn.CloseConnection(True)
        Try
            Me.MutiererText = Get_Mutierer(Me.iMutierer.Value)
        Catch ex As Exception
            Me.MutiererText = ""

        End Try

    End Function

    Public Function Save_Data() As Integer
        Me.cpMainConnectionProvider = Globals.conn
        Me.iMutierer = New SqlInt32(CType(Globals.clsmitarbeiter.iMitarbeiternr, Int32))
        Me.daMutiert_am = New SqlDateTime(CType(Now, DateTime))
        Globals.conn.OpenConnection()
        Me.Update()
        Globals.conn.CloseConnection(True)
        Me.Neuer_Datensatz = False
    End Function


    ''' <summary>
    ''' Löschen eines Datensatzes erstellen.
    ''' </summary>
    ''' <param name="Basenr">Ursprungs-Person: Ist dieser Wert nicht 0, werden die Daten mit BaseNr zuerst gelesen</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function Delete_Data(Optional ByVal Basenr As Integer = 0) As Integer
        If Basenr <> 0 Then
            Get_Data(Basenr)
        End If
        If bAktiv.Value = False Then Exit Function

        Me.cpMainConnectionProvider = Globals.conn
        Me.bAktiv = New SqlBoolean(CType(False, Boolean))
        Me.daMutiert_am = New SqlDateTime(CType(Now, DateTime))
        Me.iMutierer = New SqlInt32(CType(Globals.clsmitarbeiter.iMitarbeiternr, Int32))
        Globals.conn.OpenConnection()
        Me.Update()
        Globals.conn.CloseConnection(True)
        Me.Neuer_Datensatz = False
    End Function

    Public Overloads Function Delete(Optional ByVal Basenr As Integer = 0) As Integer
        If Basenr <> 0 Then
            Get_Data(Basenr)
        End If
        Me.cpMainConnectionProvider = Globals.conn
        Globals.conn.OpenConnection()
        MyBase.Delete()
        Globals.conn.CloseConnection(True)
        Me.Neuer_Datensatz = False
    End Function

    ''' <summary>
    ''' Neue Person einfügen
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function Add_New() As Integer
        Dim db As New DB.clsMyKey_Tabelle
        db.cpMainConnectionProvider = Globals.conn
        Dim newkey = db.get_dbkey("Pruefschrittnotiz")
        db.Dispose()
        Me.bAktiv = New SqlBoolean(CType(True, Boolean))
        Me.daErstellt_am = New SqlDateTime(CType(Now, DateTime))
        Me.daMutiert_am = New SqlDateTime(CType(Now, DateTime))
        Me.iMutierer = New SqlInt32(CType(Globals.clsmitarbeiter.iMitarbeiternr, Int32))
        Me.sNotiz = New SqlString(CType("", String))
        Me.iPruefschrittnotizNr = New SqlInt32(CType(newkey, Int32))
        Me.iPruefschrittnr = New SqlInt32(CType(0, Int32))
        Me.cpMainConnectionProvider = Globals.conn

        Try
            Globals.conn.OpenConnection()
            Me.Insert()
            Globals.conn.CloseConnection(True)
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        Me.Neuer_Datensatz = True
        Return Me.iPruefschrittnr.Value
    End Function
    Public Function Get_Eintraege(ByRef c1daten As C1.Win.C1TrueDBGrid.C1TrueDBGrid, ByVal Pruefschrittnr As Integer)
        Try
            Dim ds As New DataSet
            Dim connection As New SqlConnection()
            Dim da As New SqlDataAdapter("", connection)
            Dim sqlcmd As New SqlCommand

            sqlcmd.CommandText = "sp_get_pruefschrittNotiz"
            sqlcmd.Parameters.Add(New SqlParameter("@key", SqlDbType.Int, 4, ParameterDirection.Input, True, 10, 0, "", DataRowVersion.Proposed, Pruefschrittnr))
            sqlcmd.CommandType = CommandType.StoredProcedure
            sqlcmd.Connection = connection
            Try
                connection.ConnectionString = Globals.sConnectionString
                connection.Open()
                da.SelectCommand = sqlcmd
                da.Fill(ds, "Datatable1")
                c1daten.DataSource = Nothing
                c1daten.DataSource = ds.Tables(0)
                c1daten.DataMember = ds.Tables(0).TableName
                SpaltenTitel.Spaltentitel_aktualisieren(c1daten, "Pruefschrittnotiz", ds.Tables(0))

            Catch ex As Exception
                MsgBox(ex.Message)
            Finally
                connection.Close()
                da.Dispose()
                sqlcmd.Dispose()
            End Try
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Function


End Class