|
|
Imports System
|
|
|
Imports System.Data
|
|
|
Imports System.Data.SqlTypes
|
|
|
Imports System.Data.SqlClient
|
|
|
Imports System.Windows.Forms
|
|
|
|
|
|
Public Class frmAuswertungsParameter
|
|
|
Dim l As New List(Of Control)
|
|
|
Friend selectPoint As New System.Drawing.Point()
|
|
|
|
|
|
Dim m_sqlwhere As String
|
|
|
Property SQLWhere() As String
|
|
|
Get
|
|
|
Return m_sqlwhere
|
|
|
End Get
|
|
|
Set(ByVal value As String)
|
|
|
m_sqlwhere = value
|
|
|
End Set
|
|
|
End Property
|
|
|
Dim parameter As DataTable
|
|
|
Sub New()
|
|
|
InitializeComponent()
|
|
|
End Sub
|
|
|
|
|
|
Sub New(ByRef Params As DataTable)
|
|
|
InitializeComponent()
|
|
|
Me.parameter = Params
|
|
|
End Sub
|
|
|
|
|
|
Private Sub frmAuswertungsParameter_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
|
|
|
init_params()
|
|
|
End Sub
|
|
|
Private Sub init_params()
|
|
|
|
|
|
|
|
|
l.Clear()
|
|
|
Me.GetControl(Me, "cbox*", l)
|
|
|
For Each x As CheckBox In l
|
|
|
x.Checked = False
|
|
|
Next
|
|
|
l.Clear()
|
|
|
Me.GetControl(Me, "cbp*", l)
|
|
|
For Each x As ComboBox In l
|
|
|
Try
|
|
|
x.DataSource = Nothing
|
|
|
x.Items.Clear()
|
|
|
Catch ex As Exception
|
|
|
|
|
|
End Try
|
|
|
Next
|
|
|
l.Clear()
|
|
|
Me.GetControl(Me, "cbl*", l)
|
|
|
For Each x As ComboBox In l
|
|
|
Try
|
|
|
x.DataSource = Nothing
|
|
|
x.Items.Clear()
|
|
|
Catch ex As Exception
|
|
|
|
|
|
End Try
|
|
|
Next
|
|
|
l.Clear()
|
|
|
Me.GetControl(Me, "cbv*", l)
|
|
|
For Each x As ComboBox In l
|
|
|
Try
|
|
|
x.DataSource = Nothing
|
|
|
x.Items.Clear()
|
|
|
Catch ex As Exception
|
|
|
|
|
|
End Try
|
|
|
Next
|
|
|
l.Clear()
|
|
|
Me.GetControl(Me, "cb*", l)
|
|
|
For Each c As Control In l
|
|
|
c.Enabled = False
|
|
|
Next
|
|
|
|
|
|
Me.cboxand1.Enabled = True
|
|
|
Me.cboxor1.Enabled = True
|
|
|
Me.cbparam1.Enabled = True
|
|
|
Me.cbop1.Enabled = True
|
|
|
Me.cbvalue1.Enabled = True
|
|
|
|
|
|
For Each dr As DataRow In parameter.Rows
|
|
|
cbparam1.Items.Add(dr.Item("Bezeichnung"))
|
|
|
cbparam2.Items.Add(dr.Item("Bezeichnung"))
|
|
|
cbparam3.Items.Add(dr.Item("Bezeichnung"))
|
|
|
cbparam4.Items.Add(dr.Item("Bezeichnung"))
|
|
|
cbparam5.Items.Add(dr.Item("Bezeichnung"))
|
|
|
Next
|
|
|
|
|
|
End Sub
|
|
|
|
|
|
Private Sub cbparam1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cbparam1.SelectedIndexChanged, cbparam2.SelectedIndexChanged, _
|
|
|
cbparam3.SelectedIndexChanged, cbparam4.SelectedIndexChanged, cbparam5.SelectedIndexChanged
|
|
|
Dim s As String = sender.name
|
|
|
Dim splits() As String
|
|
|
|
|
|
|
|
|
s = s.Substring(Len(s) - 1, 1)
|
|
|
Dim cbop As String = "cbop" + s
|
|
|
|
|
|
Dim dr As DataRow
|
|
|
dr = Findrow(sender.text)
|
|
|
|
|
|
l.Clear()
|
|
|
Me.GetControl(Me, cbop, l)
|
|
|
|
|
|
For Each ob As ComboBox In l
|
|
|
ob.Items.Clear()
|
|
|
splits = dr.Item("Operator").ToString.Split(";")
|
|
|
For Each x As String In splits
|
|
|
ob.Items.Add(x)
|
|
|
Next
|
|
|
ob.SelectedIndex = 0
|
|
|
Next
|
|
|
If dr.Item("Feldbezug").ToString <> "" Then
|
|
|
If UCase(dr.Item("feldbezug").ToString.Substring(0, 3)) = "SP_" Then
|
|
|
Dim fb As New DataTable
|
|
|
fb = get_rptparam_values(dr.Item("Feldbezug"))
|
|
|
|
|
|
Dim cbvalue As String = "cbvalue" + s
|
|
|
l.Clear()
|
|
|
Me.GetControl(Me, cbvalue, l)
|
|
|
For Each x As ComboBox In l
|
|
|
x.DataSource = Nothing
|
|
|
x.Items.Clear()
|
|
|
x.DataSource = fb
|
|
|
x.DisplayMember = "Bezeichnung"
|
|
|
x.ValueMember = "KeyValue"
|
|
|
Next
|
|
|
Else
|
|
|
If dr.Item("Feldbezug").ToString <> "" Then
|
|
|
Dim selval() As String = dr.Item("Feldbezug").ToString.Split(";")
|
|
|
Dim cbvalue As String = "cbvalue" + s
|
|
|
l.Clear()
|
|
|
Me.GetControl(Me, cbvalue, l)
|
|
|
For Each x As ComboBox In l
|
|
|
x.DataSource = Nothing
|
|
|
x.Items.Clear()
|
|
|
For Each ss As String In selval
|
|
|
x.Items.Add(ss)
|
|
|
Next
|
|
|
Next
|
|
|
End If
|
|
|
End If
|
|
|
|
|
|
End If
|
|
|
End Sub
|
|
|
Private Function Findrow(ByVal key As String) As DataRow
|
|
|
For Each dr As DataRow In Me.parameter.Rows
|
|
|
If dr.Item("Bezeichnung") = key Then
|
|
|
Return dr
|
|
|
End If
|
|
|
Next
|
|
|
End Function
|
|
|
|
|
|
Private 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
|
|
|
|
|
|
|
|
|
#Region "Utils"
|
|
|
''' <summary>
|
|
|
''' Sucht in den Base-Controls s<>mtliche Controls mit dem Namen in "Key" (Wildcards * m<>glich) und listet
|
|
|
''' die gefundnen Controls in der Liste L zur weiteren Bearbeitung
|
|
|
''' </summary>
|
|
|
''' <param name="BaseControl">Base-Contrlo (z.B. aktuelles Formular</param>
|
|
|
''' <param name="Key">Schl<68>ssel welcher gesucht werden soll</param>
|
|
|
''' <param name="L">Liste der gefundenen Objekte</param>
|
|
|
''' <returns>True wenn eines oder mehr Controls gefunden wurden, false wenn kein Control gefunden wurde.
|
|
|
''' </returns>
|
|
|
''' <remarks></remarks>
|
|
|
Private Function GetControl(ByVal BaseControl As Control, ByVal Key As String, ByRef L As List(Of Control), Optional ByVal ReturnAtFirstElement As Boolean = False) As Boolean
|
|
|
If L Is Nothing Then L = New List(Of Control)
|
|
|
Dim Gut As Boolean
|
|
|
Dim ReturnFlag As Boolean = False
|
|
|
If Key IsNot Nothing Then Key = Key.ToLower
|
|
|
|
|
|
If BaseControl.HasChildren = True Then
|
|
|
For Each ctl As Control In BaseControl.Controls
|
|
|
Gut = False
|
|
|
If Key Is Nothing Then
|
|
|
Gut = True
|
|
|
Else
|
|
|
If ctl.Name.Length >= Key.Length Then
|
|
|
Key = Key.ToLower
|
|
|
If Key.StartsWith("*") Then
|
|
|
If Key.Substring(1) = ctl.Name.ToLower.Substring(ctl.Name.Length - (Key.Length - 1), Key.Length - 1) Then Gut = True
|
|
|
ElseIf Key.EndsWith("*") Then
|
|
|
If Key.Substring(0, Key.Length - 1) = ctl.Name.ToLower.Substring(0, Key.Length - 1) Then Gut = True
|
|
|
Else
|
|
|
If Key = ctl.Name.ToLower Then Gut = True
|
|
|
End If
|
|
|
End If
|
|
|
End If
|
|
|
|
|
|
If Gut = True Then
|
|
|
L.Add(ctl)
|
|
|
If ReturnAtFirstElement = True Then ReturnFlag = True
|
|
|
End If
|
|
|
If ReturnFlag = False Then
|
|
|
Call GetControl(ctl, Key, L)
|
|
|
End If
|
|
|
Next
|
|
|
End If
|
|
|
|
|
|
If L.Count - 1 > -1 Then
|
|
|
Return True
|
|
|
Else
|
|
|
Return False
|
|
|
End If
|
|
|
End Function
|
|
|
#End Region
|
|
|
|
|
|
Private Sub cboxand1_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboxand1.CheckedChanged, _
|
|
|
cboxand2.CheckedChanged, cboxand3.CheckedChanged, cboxand4.CheckedChanged, cboxor1.CheckedChanged, cboxor2.CheckedChanged, _
|
|
|
cboxor3.CheckedChanged, cboxor4.CheckedChanged
|
|
|
Dim s As String = sender.name
|
|
|
l.Clear()
|
|
|
s = s.Substring(Len(s) - 1, 1)
|
|
|
Dim feld As String = ""
|
|
|
|
|
|
If sender.name.ToString.IndexOf("and") > 0 Then feld = "cboxor" + s Else feld = "cboxand" + s
|
|
|
Me.GetControl(Me, feld, l)
|
|
|
For Each x As CheckBox In l
|
|
|
x.Checked = False
|
|
|
Next
|
|
|
s = s + 1
|
|
|
feld = "cbparam" + s
|
|
|
l.Clear()
|
|
|
Me.GetControl(Me, feld, l)
|
|
|
For Each x As ComboBox In l
|
|
|
x.Enabled = True
|
|
|
Next
|
|
|
feld = "cbop" + s
|
|
|
l.Clear()
|
|
|
Me.GetControl(Me, feld, l)
|
|
|
For Each x As ComboBox In l
|
|
|
x.Enabled = True
|
|
|
Next
|
|
|
feld = "cbvalue" + s
|
|
|
l.Clear()
|
|
|
Me.GetControl(Me, feld, l)
|
|
|
For Each x As ComboBox In l
|
|
|
x.Enabled = True
|
|
|
Next
|
|
|
|
|
|
feld = "cboxand" + s
|
|
|
l.Clear()
|
|
|
Me.GetControl(Me, feld, l)
|
|
|
For Each x As ComboBox In l
|
|
|
x.Enabled = True
|
|
|
Next
|
|
|
feld = "cboxor" + s
|
|
|
l.Clear()
|
|
|
Me.GetControl(Me, feld, l)
|
|
|
For Each x As ComboBox In l
|
|
|
x.Enabled = True
|
|
|
Next
|
|
|
|
|
|
End Sub
|
|
|
|
|
|
Private Sub btnAufbereiten_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAufbereiten.Click
|
|
|
Dim sqlwhere As String = ""
|
|
|
If Me.cbvalue1.Text = "" Then
|
|
|
Me.SQLWhere = ""
|
|
|
Me.DialogResult = Windows.Forms.DialogResult.OK
|
|
|
Me.Close()
|
|
|
Exit Sub
|
|
|
End If
|
|
|
sqlwhere = sqlwhere + get_where(1)
|
|
|
If Me.cboxand1.Checked = True Then
|
|
|
sqlwhere = sqlwhere + " and " + get_where(2)
|
|
|
End If
|
|
|
If Me.cboxor1.Checked = True Then
|
|
|
sqlwhere = sqlwhere + " or " + get_where(2)
|
|
|
End If
|
|
|
If Me.cboxand2.Checked = True Then
|
|
|
sqlwhere = sqlwhere + " and " + get_where(3)
|
|
|
End If
|
|
|
If Me.cboxor2.Checked = True Then
|
|
|
sqlwhere = sqlwhere + " or " + get_where(3)
|
|
|
End If
|
|
|
If Me.cboxand3.Checked = True Then
|
|
|
sqlwhere = sqlwhere + " and " + get_where(4)
|
|
|
End If
|
|
|
If Me.cboxor3.Checked = True Then
|
|
|
sqlwhere = sqlwhere + " or " + get_where(4)
|
|
|
End If
|
|
|
If Me.cboxand4.Checked = True Then
|
|
|
sqlwhere = sqlwhere + " and " + get_where(5)
|
|
|
End If
|
|
|
If Me.cboxor4.Checked = True Then
|
|
|
sqlwhere = sqlwhere + " or " + get_where(5)
|
|
|
End If
|
|
|
Me.SQLWhere = sqlwhere
|
|
|
Me.DialogResult = Windows.Forms.DialogResult.OK
|
|
|
Me.Close()
|
|
|
End Sub
|
|
|
|
|
|
Private Function get_where(ByVal nr As Integer) As String
|
|
|
Dim param As New ComboBox
|
|
|
Dim op As New ComboBox
|
|
|
Dim val As New ComboBox
|
|
|
|
|
|
l.Clear()
|
|
|
Dim feld As String
|
|
|
Dim dr As DataRow
|
|
|
feld = "cbparam" + Trim(Str(nr))
|
|
|
Me.GetControl(Me, feld, l)
|
|
|
param = l.Item(0)
|
|
|
|
|
|
l.Clear()
|
|
|
feld = "cbop" + Trim(Str(nr))
|
|
|
Me.GetControl(Me, feld, l)
|
|
|
op = l.Item(0)
|
|
|
|
|
|
l.Clear()
|
|
|
feld = "cbvalue" + Trim(Str(nr))
|
|
|
Me.GetControl(Me, feld, l)
|
|
|
val = l.Item(0)
|
|
|
|
|
|
dr = Me.Findrow(param.Text)
|
|
|
|
|
|
Dim wertvalue As String = ""
|
|
|
Dim wertdbfeld As String = ""
|
|
|
Dim wertop As String = op.Text
|
|
|
|
|
|
wertop = op.Text
|
|
|
|
|
|
Dim paramtyp As String = ""
|
|
|
Dim paramsplit() As String = dr.Item("Paramtype").ToString.Split(";")
|
|
|
|
|
|
Dim dbfeldsplit() As String = dr.Item("dbfeldname").ToString.Split(";")
|
|
|
If dbfeldsplit.Length > 1 And val.SelectedIndex > -1 Then
|
|
|
wertvalue = val.SelectedValue
|
|
|
wertdbfeld = dbfeldsplit(0)
|
|
|
paramtyp = paramsplit(0)
|
|
|
Else
|
|
|
wertvalue = val.Text
|
|
|
If dbfeldsplit.Length > 1 Then
|
|
|
wertdbfeld = dbfeldsplit(1)
|
|
|
paramtyp = paramsplit(1)
|
|
|
Else
|
|
|
wertdbfeld = dbfeldsplit(0)
|
|
|
paramtyp = paramsplit(0)
|
|
|
End If
|
|
|
End If
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Select Case UCase(paramtyp)
|
|
|
Case "VARCHAR", "STRING", "CHAR"
|
|
|
Return wertdbfeld + " " + wertop + " '" + wertvalue + "'"
|
|
|
Case "INTEGER", "INT"
|
|
|
Return wertdbfeld + " " + wertop + " " + wertvalue + ""
|
|
|
Case "DATUM"
|
|
|
Return wertdbfeld + " " + wertop + " '" + wertvalue + " 00:00:00'"
|
|
|
Case "DATUM+1"
|
|
|
Dim d As DateTime = wertvalue
|
|
|
d = DateAdd(DateInterval.Day, 1, d)
|
|
|
wertvalue = d.ToString
|
|
|
Return wertdbfeld + " " + wertop + " '" + wertvalue + " 00:00:00'"
|
|
|
Case "BOOLEAN"
|
|
|
If UCase(wertvalue) = "TRUE" Or UCase(wertvalue) = "JA" Then
|
|
|
Return wertdbfeld + " " + wertop + " 1"
|
|
|
Else
|
|
|
Return wertdbfeld + " " + wertop + " 0"
|
|
|
End If
|
|
|
Case Else
|
|
|
|
|
|
End Select
|
|
|
|
|
|
End Function
|
|
|
|
|
|
Private Sub cbvalue1_DropDown(ByVal sender As Object, ByVal e As System.EventArgs) Handles cbvalue1.DropDown, cbvalue2.DropDown, _
|
|
|
cbvalue3.DropDown, cbvalue4.DropDown, cbvalue5.DropDown
|
|
|
Try
|
|
|
Dim s As String = sender.name
|
|
|
s = s.Substring(Len(s) - 1, 1)
|
|
|
Dim nr As Integer = s
|
|
|
Dim param As New ComboBox
|
|
|
|
|
|
Dim op As New ComboBox
|
|
|
Dim val As New ComboBox
|
|
|
|
|
|
|
|
|
Dim feld As String
|
|
|
Dim dr As DataRow
|
|
|
feld = "cbparam" + Trim(Str(nr))
|
|
|
l.Clear()
|
|
|
Me.GetControl(Me, feld, l)
|
|
|
param = l.Item(0)
|
|
|
|
|
|
feld = "cbvalue" + Trim(Str(nr))
|
|
|
l.Clear()
|
|
|
Me.GetControl(Me, feld, l)
|
|
|
val = l.Item(0)
|
|
|
|
|
|
dr = Me.Findrow(param.Text)
|
|
|
|
|
|
If dr.Item("Paramtype") = "Datum" Then
|
|
|
Dim f As New frmCalendar
|
|
|
|
|
|
f.Top = val.Top + val.Height
|
|
|
f.Left = val.Left + val.Width - 10
|
|
|
f.ShowDialog()
|
|
|
If f.DialogResult = Windows.Forms.DialogResult.OK Then
|
|
|
l.Clear()
|
|
|
feld = "cbvalue" + Trim(Str(nr))
|
|
|
l.Clear()
|
|
|
Me.GetControl(Me, feld, l)
|
|
|
param = l.Item(0)
|
|
|
param.Text = f.MonthCalendar1.SelectionRange.Start.Date
|
|
|
End If
|
|
|
End If
|
|
|
Catch ex As Exception
|
|
|
|
|
|
End Try
|
|
|
End Sub
|
|
|
Private Sub cbvalue1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cbvalue1.SelectedIndexChanged
|
|
|
|
|
|
End Sub
|
|
|
|
|
|
Private Sub frmAuswertungsParameter_MouseMove(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles Me.MouseMove
|
|
|
selectPoint.Y = e.Y
|
|
|
selectPoint.X = e.X
|
|
|
End Sub
|
|
|
End Class |
