Imports C1.Win.C1TrueDBGrid
Imports System
Imports System.Data
Imports System.Data.SqlTypes
Imports System.Data.SqlClient
Imports System.Windows.Forms

Namespace TKB.Auswertung
    Public Class clsAuswertung

        Private Auswertungen As New DataSet
        Private Mitarbeiter_Auswerungsparameter As New TKB.VV.DB.clsMitarbeiter_Auswertungsparameter
        Public AuswertungParameter As New DataTable
        Public Auswertung As New TKB.VV.DB.clsAuswertung
        Public MitarbeiterAuswertungsparameter As New DataTable
        Dim sheader1 As String
        Property TitelZeile1() As String
            Get
                Return sheader1
            End Get
            Set(ByVal value As String)
                sheader1 = value
            End Set
        End Property
        Dim sheader2 As String
        Property TitelZeile2() As String
            Get
                Return sheader2
            End Get
            Set(ByVal value As String)
                sheader2 = value
            End Set
        End Property
        Dim scparamcollection As New Collection
        Property ParamCollection() As Collection
            Get
                Return scparamcollection
            End Get
            Set(ByVal value As Collection)
                scparamcollection = value
            End Set
        End Property
        Dim mFullparam As String
        Property FullParam() As String
            Get
                Return mFullparam
            End Get
            Set(ByVal value As String)
                mFullparam = value
            End Set
        End Property

        Public Sub Get_Auswertungen(ByRef tree As TreeView)
            Read_Auswertungen()
            Me.treedata_for_search = Me.Auswertungen.Tables(0).Copy
            Load_Treeview(Auswertungen, tree)
        End Sub



        ''' <summary>
        ''' Auswertungen, für welche der User berechtigt ist, auslesen
        ''' </summary>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Private Function Read_Auswertungen() 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_Auswertung_Get_Auswertungen"
            scmCmdToExecute.CommandType = CommandType.StoredProcedure
            scmCmdToExecute.Connection = conn.scoDBConnection
            Try
                scmCmdToExecute.Parameters.Add(New SqlParameter("@mitarbeiternr", SqlDbType.Int, 4, ParameterDirection.Input, True, 10, 0, "", DataRowVersion.Proposed, Globals.clsmitarbeiter.iMitarbeiternr.Value))
                sdaAdapter.Fill(dtToReturn)
                Auswertungen.Tables.Clear()
                Auswertungen.Tables.Add(dtToReturn)
            Catch ex As Exception
                Throw New Exception("clsAuswertung::" & scmCmdToExecute.CommandText & "::Error occured." & ex.Message, ex)
            Finally
                scmCmdToExecute.Dispose()
                sdaAdapter.Dispose()
            End Try
        End Function

        ''' <summary>
        ''' Interne Relation aufbauen und Tree aufbauen
        ''' </summary>
        ''' <param name="oSourceData"></param>
        ''' <param name="tree"></param>
        ''' <remarks></remarks>
        Private Sub Load_Treeview(ByVal oSourceData As DataSet, ByRef tree As TreeView)
            If Not (oSourceData Is Nothing) Then
                Dim oView As DataView = oSourceData.Tables(0).DefaultView
                Dim oTable As DataTable = oView.Table
                Dim oDS As DataSet = New DataSet()
                oDS.Tables.Add(oTable.Copy())

                If oDS.Relations.Contains("SelfRefenceRelation") = False Then
                    oDS.Relations.Add("SelfRefenceRelation", _
                        oDS.Tables(0).Columns("id"), _
                        oDS.Tables(0).Columns("Parentid"))
                End If
                oTable.Dispose()
                oTable = Nothing
                LoadTreeView(oDS, tree)
                oDS.Dispose()
                oDS = Nothing
            End If
        End Sub

        ''' <summary>
        ''' Tree aufbauen
        ''' </summary>
        ''' <param name="oDS"></param>
        ''' <param name="oTreeview"></param>
        ''' <remarks></remarks>
        Private Sub LoadTreeView(ByVal oDS As DataSet, ByRef oTreeview As TreeView)
            'Dim oTreeView As TreeView = New TreeView()
            Dim oDataRow As DataRow
            For Each oDataRow In oDS.Tables(0).Rows
                If Not oDataRow.IsNull("Parentid") Then
                    If oDataRow.Item("Parentid") = 0 Then
                        Dim oNode As New TreeNode()
                        oNode.Text = oDataRow("Bezeichnung").ToString()
                        oNode.Tag = oDataRow("auswertungnr").ToString
                        oNode.ToolTipText = oDataRow("Beschreibung").ToString
                        oNode.ImageIndex = 0
                        oNode.SelectedImageIndex = 0
                        oNode.StateImageIndex = 0
                        oTreeview.Nodes.Add(oNode)
                        RecursivelyLoadTree(oDataRow, oNode)
                    End If
                End If
            Next oDataRow
            oDS.Dispose()
            oDS = Nothing
        End Sub

        ''' <summary>
        ''' Child-Nodes hinzufügen
        ''' </summary>
        ''' <param name="oDataRow"></param>
        ''' <param name="oNode"></param>
        ''' <remarks></remarks>
        Private Sub RecursivelyLoadTree(ByVal oDataRow As DataRow, ByRef oNode As TreeNode)
            Dim oChildRow As DataRow
            For Each oChildRow In oDataRow.GetChildRows("SelfRefenceRelation")
                Dim oChildNode As New TreeNode()
                oChildNode.Text = oChildRow("Bezeichnung").ToString()
                oChildNode.Tag = oChildRow("Auswertungnr").ToString()
                If oChildRow("Auswertungnr") = 0 Then
                    oChildNode.ImageIndex = 0
                    oChildNode.SelectedImageIndex = 0
                    oChildNode.StateImageIndex = 0
                    oChildNode.ToolTipText = oChildRow("Beschreibung").ToString
                Else
                    oChildNode.ImageIndex = 1
                    oChildNode.SelectedImageIndex = 1
                    oChildNode.StateImageIndex = 1
                    oChildNode.ToolTipText = oChildRow("Beschreibung").ToString
                End If
                oNode.Nodes.Add(oChildNode)
                RecursivelyLoadTree(oChildRow, oChildNode)
            Next oChildRow
        End Sub

        Public Function Get_Auswertung(ByVal Auswertungnr As Integer) As Boolean
            Try
                Me.Auswertung.iAuswertungNr = New SqlInt32(CType(Auswertungnr, Int32))
                Me.Auswertung.cpMainConnectionProvider = Globals.conn
                Me.Auswertung.SelectOne()
                Me.AuswertungParameter = Get_Auswertungsparameter(Me.Auswertung.iAuswertungNr.Value)
                Me.MitarbeiterAuswertungsparameter = Me.Get_MAParameter(Me.Auswertung.iAuswertungNr.Value)
                Return True
            Catch ex As Exception
                Return False
            End Try
        End Function

        Public Function Get_Auswertungsparameter(ByVal Auswertungnr 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_rpt_get_auswertungparameter"
            scmCmdToExecute.CommandType = CommandType.StoredProcedure
            scmCmdToExecute.Connection = conn.scoDBConnection
            Try
                scmCmdToExecute.Parameters.Add(New SqlParameter("@Auswertungnr", SqlDbType.VarChar, 50, ParameterDirection.Input, True, 0, 0, "", DataRowVersion.Proposed, Auswertungnr))
                sdaAdapter.Fill(dtToReturn)
                Return dtToReturn
            Catch ex As Exception
                ' // some error occured. Bubble it to caller and encapsulate Exception object
                Throw New Exception("frmAuswertung::" & scmCmdToExecute.CommandText & "::Error occured." & ex.Message, ex)
            Finally
                scmCmdToExecute.Dispose()
                sdaAdapter.Dispose()
            End Try

        End Function

        Public Function get_rptparam_values(ByVal sp 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 = sp
            scmCmdToExecute.CommandType = CommandType.StoredProcedure
            scmCmdToExecute.Connection = conn.scoDBConnection
            Try
                sdaAdapter.Fill(dtToReturn)
                Return dtToReturn
            Catch ex As Exception
                ' // some error occured. Bubble it to caller and encapsulate Exception object
                Throw New Exception("frmAuswertung::" & scmCmdToExecute.CommandText & "::Error occured." & ex.Message, ex)
            Finally
                scmCmdToExecute.Dispose()
                sdaAdapter.Dispose()
            End Try

        End Function

        Public Function Get_Auswertungsdaten(ByVal sp As String, ByVal paramcollection As Collection) As DataTable
            'If sp.IndexOf("(CurrentUser)") > 0 Then sp = sp.Substring(0, sp.IndexOf("(CurrentUser)") - 1)
            Dim scmCmdToExecute As SqlCommand = New SqlCommand()
            Dim dtToReturn As DataTable = New DataTable()
            Dim sdaAdapter As SqlDataAdapter = New SqlDataAdapter(scmCmdToExecute)
            scmCmdToExecute.CommandText = sp
            scmCmdToExecute.CommandType = CommandType.StoredProcedure
            scmCmdToExecute.Connection = conn.scoDBConnection
            If paramcollection.Count = 0 Then paramcollection.Add(New RptParams("@sqlwhere", ""))
            Try
                Dim i As Integer
                For i = 1 To paramcollection.Count
                    Dim rptp As RptParams = paramcollection.Item(i)
                    scmCmdToExecute.Parameters.Add(New SqlParameter(rptp.Paramname, SqlDbType.VarChar, 1024, ParameterDirection.Input, True, 0, 0, "", DataRowVersion.Proposed, rptp.paramvalue))
                Next
                'scmCmdToExecute.Parameters.Add(New SqlParameter("@sqlwhere", SqlDbType.VarChar, 1024, ParameterDirection.Input, True, 0, 0, "", DataRowVersion.Proposed, sqlwhere))
                'scmCmdToExecute.Parameters.Add(New SqlParameter("@Rolle", SqlDbType.VarChar, 50, ParameterDirection.Output, True, 0, 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("frmAuswertung::" & scmCmdToExecute.CommandText & "::Error occured." & ex.Message, ex)
            Finally
                scmCmdToExecute.Dispose()
                sdaAdapter.Dispose()
            End Try

        End Function
        Public Function Get_Auswertungsdaten(ByVal sp As String, ByVal paramcollection As Collection, ByVal txp As Boolean) As DataTable
            If paramcollection.Count = 0 Then paramcollection.Add(New RptParams("@sqlwhere", ""))

            Dim con As New SqlConnection(Get_TXP_Connection)
            Dim scmCmdToExecute As SqlCommand = New SqlCommand()
            Dim dtToReturn As DataTable = New DataTable()
            Dim sdaAdapter As SqlDataAdapter = New SqlDataAdapter(scmCmdToExecute)
            scmCmdToExecute.CommandText = sp
            scmCmdToExecute.CommandType = CommandType.StoredProcedure
            scmCmdToExecute.Connection = con
            scmCmdToExecute.CommandTimeout = 30000

            Try
                For i = 1 To paramcollection.Count
                    Dim rptp As RptParams = paramcollection.Item(i)
                    scmCmdToExecute.Parameters.Add(New SqlParameter(rptp.Paramname, SqlDbType.VarChar, 1024, ParameterDirection.Input, True, 0, 0, "", DataRowVersion.Proposed, rptp.paramvalue))
                Next
                'scmCmdToExecute.Parameters.Add(New SqlParameter("@sqlwhere", SqlDbType.VarChar, 1024, ParameterDirection.Input, True, 0, 0, "", DataRowVersion.Proposed, sqlwhere))
                sdaAdapter.Fill(dtToReturn)
                Return dtToReturn
            Catch ex As Exception
                ' // some error occured. Bubble it to caller and encapsulate Exception object
                Throw New Exception("frmAuswertung::" & scmCmdToExecute.CommandText & "::Error occured." & ex.Message, ex)
            Finally
                con.Close()
                scmCmdToExecute.Dispose()
                sdaAdapter.Dispose()
                con.Dispose()
            End Try

        End Function

        Private Function Get_TXP_Connection() As String
            Dim sCstr As String = ""
            Dim txpfile As System.IO.File
            Dim txpread As System.IO.StreamReader
            txpread = txpfile.OpenText(Application.StartupPath + "\TXPConn.cfg")
            sCstr = txpread.ReadLine
            sCstr = Crypto.DecryptText(sCstr, "HutterundMueller")
            sCstr = Left(sCstr, Len(sCstr) - 1)
            txpread.Close()

            Return sCstr
        End Function
        Public Function Save_MAParameter(ByVal auswertungnr As Integer, ByVal Bezeichnung As String, ByVal Parameter As String, ByVal Titel1 As String, ByVal Titel2 As String, ByVal PrintParam As Boolean, ByVal sqlwhere As String)
            Me.Mitarbeiter_Auswerungsparameter.cpMainConnectionProvider = Globals.conn
            Me.Mitarbeiter_Auswerungsparameter.iAuswertungnr = New SqlInt32(CType(auswertungnr, Int32))
            Me.Mitarbeiter_Auswerungsparameter.iMitarbeiternr = New SqlInt32(CType(Globals.clsmitarbeiter.iMitarbeiternr.Value, Int32))
            Me.Mitarbeiter_Auswerungsparameter.sBeschreibung = New SqlString(CType(Bezeichnung, String))
            Me.Mitarbeiter_Auswerungsparameter.sParameterdaten = New SqlString(CType(Parameter, String))
            Me.Mitarbeiter_Auswerungsparameter.sTitelzeile1 = New SqlString(CType(Titel1, String))
            Me.Mitarbeiter_Auswerungsparameter.sTitelzeile2 = New SqlString(CType(Titel2, String))
            Me.Mitarbeiter_Auswerungsparameter.sSQLWhere = New SqlString(CType(sqlwhere, String))
            If PrintParam = True Then
                Me.Mitarbeiter_Auswerungsparameter.bParamPrint = New SqlBoolean(CType(True, Boolean))
            Else
                Me.Mitarbeiter_Auswerungsparameter.bParamPrint = New SqlBoolean(CType(False, Boolean))
            End If
            Globals.conn.OpenConnection()
            Me.Mitarbeiter_Auswerungsparameter.Insert()
            Globals.conn.CloseConnection(True)
            Me.MitarbeiterAuswertungsparameter = Me.Get_MAParameter(auswertungnr)
        End Function


        Public Function Get_MAParameter(ByVal Auswertungnr 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_mitarbeiter_auswertungparamter_selectall"
            scmCmdToExecute.CommandType = CommandType.StoredProcedure
            scmCmdToExecute.Connection = conn.scoDBConnection
            Try
                scmCmdToExecute.Parameters.Add(New SqlParameter("@mitarbeiternr", SqlDbType.Int, 4, ParameterDirection.Input, True, 0, 0, "", DataRowVersion.Proposed, Globals.clsmitarbeiter.iMitarbeiternr.Value))
                scmCmdToExecute.Parameters.Add(New SqlParameter("@Auswertungnr", SqlDbType.Int, 4, ParameterDirection.Input, True, 0, 0, "", DataRowVersion.Proposed, Auswertungnr))
                sdaAdapter.Fill(dtToReturn)
                Return dtToReturn
            Catch ex As Exception
                ' // some error occured. Bubble it to caller and encapsulate Exception object
                Throw New Exception("frmAuswertung::" & scmCmdToExecute.CommandText & "::Error occured." & ex.Message, ex)
            Finally
                scmCmdToExecute.Dispose()
                sdaAdapter.Dispose()
            End Try
        End Function

        Public Function Delete_Parameter(ByVal AuswertungParameterNr As Integer)
            Me.Mitarbeiter_Auswerungsparameter.iMitarbeiter_AuswertungsparameterNr = New SqlInt32(CType(AuswertungParameterNr, Int32))
            Me.Mitarbeiter_Auswerungsparameter.cpMainConnectionProvider = Globals.conn
            Globals.conn.OpenConnection()
            Me.Mitarbeiter_Auswerungsparameter.Delete()
            Globals.conn.CloseConnection(True)
            Me.MitarbeiterAuswertungsparameter = Me.Get_MAParameter(Me.Auswertung.iAuswertungNr.Value)
        End Function


#Region "Suche"

        Public treedata_for_search As New DataTable
        Dim FoundNode As Integer = 0
        Dim Searchstring As String = ""
        Public Treesearch As New TreeView

        Public Function Init_Search()
            FoundNode = 0
            Searchstring = ""
            Me.Treesearch.Nodes.Clear()
        End Function

        Public Function SearchNode(ByRef tree As TreeView, ByVal SearchString As String) As TreeNode
            Me.Treesearch.Nodes.Clear()
            For Each dr As DataRow In treedata_for_search.Rows
                If InStr(UCase(dr.Item("Bezeichnung")), UCase(SearchString)) > 0 Then
                    Dim tn As New TreeNode
                    tn = Me.Treesearch.Nodes.Add(dr.Item("Bezeichnung"))
                    tn.Tag = dr.Item("Auswertungnr")
                End If
            Next
            Return FindFirst(tree)
        End Function

        Public Function FindFirst(ByRef Tree As TreeView) As TreeNode
            Try
                If Me.Treesearch.Nodes.Count > 0 Then Me.Treesearch.SelectedNode = Me.Treesearch.Nodes(0)
                Return FindNode(Tree.Nodes, Treesearch.SelectedNode.Tag, "")
            Catch
            End Try
        End Function



        Public Function FindNextNode(ByRef tree As TreeView) As TreeNode
            Try
                Treesearch.SelectedNode = Treesearch.SelectedNode.NextNode
                If Treesearch.SelectedNode Is Nothing Then
                    Return FindFirst(tree)
                End If
                Return Me.FindNode(tree.Nodes, Treesearch.SelectedNode.Tag, "")
            Catch ex As Exception

            End Try
        End Function

        Public Function FindPrevNode(ByRef tree As TreeView) As TreeNode
            Try
                Treesearch.SelectedNode = Treesearch.SelectedNode.PrevNode
                If Treesearch.SelectedNode Is Nothing Then
                    Return FindLastNode(tree)
                End If
                Return Me.FindNode(tree.Nodes, Treesearch.SelectedNode.Tag, "")
            Catch ex As Exception
            End Try
        End Function

        Public Function FindLastNode(ByRef tree As TreeView) As TreeNode
            Try
                Treesearch.SelectedNode = Treesearch.Nodes(Treesearch.Nodes.Count - 1)
                Return Me.FindNode(tree.Nodes, Treesearch.SelectedNode.Tag, "")
            Catch ex As Exception
            End Try

        End Function

        Public Function FindNode(ByVal _nodeCollection As TreeNodeCollection, ByVal SearchVal As Integer, ByVal SearchString As String) As TreeNode
            Dim tmpNode As TreeNode
            For Each _child As TreeNode In _nodeCollection
                If _child.Tag = SearchVal Then

                    Return _child
                End If
                ' If InStr(UCase(_child.Text), UCase(SearchString)) > 0 Then
                ' Return _child
                ' End If
                tmpNode = FindNode(_child.Nodes, SearchVal, SearchString)
                If Not tmpNode Is Nothing Then
                    Return tmpNode
                End If
            Next
            Return Nothing
        End Function
#End Region




    End Class

    Public Class RptParams
        Dim m_paramname As String
        Property Paramname As String
            Get
                Return m_paramname
            End Get
            Set(value As String)
                m_paramname = value
            End Set
        End Property

        Dim m_paramvalue As String
        Property paramvalue As String
            Get
                Return m_paramvalue
            End Get
            Set(value As String)
                m_paramvalue = value
            End Set
        End Property

        Sub New(pname As String, pvalue As String)
            Me.Paramname = pname
            Me.paramvalue = pvalue
        End Sub
    End Class

End Namespace