You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
465 lines
21 KiB
465 lines
21 KiB
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
|