|
|
Imports System.Windows.Forms
|
|
|
Imports System.Drawing
|
|
|
Public Class FrmReportSelect
|
|
|
|
|
|
#Region "Deklarationen"
|
|
|
Dim auswertungen As New TKB.Auswertung.clsAuswertung
|
|
|
Dim CtrlList As New List(Of Control)
|
|
|
Dim splits() As String
|
|
|
Dim dr As DataRow
|
|
|
Dim s As String = ""
|
|
|
Dim SQLWhere As String = ""
|
|
|
Friend selectPoint As New System.Drawing.Point()
|
|
|
Dim Auswertungsdaten As New DataSet
|
|
|
Dim On_Load As Boolean = False
|
|
|
Dim DescriptionToolTip As New ToolTip
|
|
|
Dim WhereModified As Boolean = False
|
|
|
Dim sec As New Utils.MySecurity
|
|
|
#End Region
|
|
|
|
|
|
#Region "Formular"
|
|
|
''' <summary>
|
|
|
''' Formular schliessen
|
|
|
''' </summary>
|
|
|
''' <param name="sender"></param>
|
|
|
''' <param name="e"></param>
|
|
|
''' <remarks></remarks>
|
|
|
Private Sub btnAbbruch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
|
|
|
Me.Close()
|
|
|
End Sub
|
|
|
''' <summary>
|
|
|
''' Formualr schliessen
|
|
|
''' </summary>
|
|
|
''' <param name="sender"></param>
|
|
|
''' <param name="e"></param>
|
|
|
''' <remarks></remarks>
|
|
|
Private Sub BeendenToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BeendenToolStripMenuItem.Click
|
|
|
btnAbbruch_Click(sender, e)
|
|
|
End Sub
|
|
|
|
|
|
''' <summary>
|
|
|
''' Formular schliessen
|
|
|
''' </summary>
|
|
|
''' <param name="sender"></param>
|
|
|
''' <param name="e"></param>
|
|
|
''' <remarks></remarks>
|
|
|
Private Sub TSBtnQuit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TSBtnQuit.Click
|
|
|
btnAbbruch_Click(sender, e)
|
|
|
End Sub
|
|
|
#End Region
|
|
|
|
|
|
Private Sub FrmReportSelect_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
|
|
|
Try
|
|
|
auswertungen.Get_Auswertungen(Me.TreeAuswertungen)
|
|
|
Me.TreeAuswertungen.ExpandAll()
|
|
|
Me.TreeAuswertungen.SelectedNode = Me.TreeAuswertungen.Nodes(0)
|
|
|
Me.TreeAuswertungen.SelectedNode.ExpandAll()
|
|
|
Catch
|
|
|
End Try
|
|
|
sec.Set_Form_Security(Me)
|
|
|
End Sub
|
|
|
|
|
|
Private Sub btnAufbereiten_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAufbereitenCSV.Click
|
|
|
Me.Cursor = Cursors.WaitCursor
|
|
|
|
|
|
If Me.WhereModified = False Then Get_SQLWhere()
|
|
|
Me.Auswertungsdaten = Me.auswertungen.Get_Auswertungsdaten(Me.auswertungen.Auswertung.sSQL.Value, SQLWhere, Me.auswertungen.Auswertung.sSQLType.Value)
|
|
|
Dim f As New frmAuswertung(Me.TreeAuswertungen.SelectedNode.Text, Me.Auswertungsdaten)
|
|
|
f.MdiParent = Me.MdiParent
|
|
|
Me.Cursor = Cursors.Default
|
|
|
f.Show()
|
|
|
End Sub
|
|
|
|
|
|
Private Sub BtnAufbereitenCR_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnAufbereitenCR.Click
|
|
|
showreport()
|
|
|
End Sub
|
|
|
Public Sub showreport()
|
|
|
Me.Cursor = Cursors.WaitCursor
|
|
|
Me.auswertungen.TitelZeile1 = Me.txtTitel1.Text
|
|
|
Me.auswertungen.TitelZeile2 = Me.txtTitel2.Text
|
|
|
|
|
|
If Me.WhereModified = False Then Get_SQLWhere()
|
|
|
Me.Auswertungsdaten = Me.auswertungen.Get_Auswertungsdaten(Me.auswertungen.Auswertung.sSQL.Value, SQLWhere, Me.auswertungen.Auswertung.sSQLType.Value)
|
|
|
Dim f As New frmcrreporting(Me.Auswertungsdaten, Me.auswertungen.Auswertung.iAuswertungNr.Value, Me.auswertungen, Me.checkboxprintparam.Checked, False)
|
|
|
f.Text = "Auswertung " + Me.TreeAuswertungen.SelectedNode.Text
|
|
|
f.MdiParent = Me.MdiParent
|
|
|
Me.Cursor = Cursors.Default
|
|
|
f.Show()
|
|
|
f.DisplayReport()
|
|
|
End Sub
|
|
|
|
|
|
Public Sub showreport_withdata(daten As DataSet, ByVal Auswertungsnr As Integer, design As Boolean, ByVal export_to_pdf As Boolean)
|
|
|
Me.Cursor = Cursors.WaitCursor
|
|
|
'Me.auswertungen.TitelZeile1 = Me.txtTitel1.Text
|
|
|
'Me.auswertungen.TitelZeile2 = Me.txtTitel2.Text
|
|
|
|
|
|
Me.Auswertungsdaten = daten
|
|
|
Dim f As New frmcrreporting(Me.Auswertungsdaten, Auswertungsnr, Me.auswertungen, False, design)
|
|
|
Try
|
|
|
f.Text = "Auswertung " + Me.TreeAuswertungen.SelectedNode.Text
|
|
|
|
|
|
Catch ex As Exception
|
|
|
|
|
|
End Try
|
|
|
|
|
|
f.MdiParent = Me.MdiParent
|
|
|
Me.Cursor = Cursors.Default
|
|
|
If design = True Then
|
|
|
f.DesignReport()
|
|
|
Exit Sub
|
|
|
End If
|
|
|
If export_to_pdf = True Then
|
|
|
f.Visible = False
|
|
|
f.Height = 0
|
|
|
f.WindowState = FormWindowState.Minimized
|
|
|
End If
|
|
|
|
|
|
f.Show()
|
|
|
If export_to_pdf = False Then
|
|
|
f.DisplayReport()
|
|
|
Else
|
|
|
Dim fn As String = f.Export_To_PDF
|
|
|
f.Close()
|
|
|
Process.Start(fn)
|
|
|
End If
|
|
|
End Sub
|
|
|
|
|
|
|
|
|
Private Sub TreeAuswertungen_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles TreeAuswertungen.DoubleClick
|
|
|
'If Me.TreeAuswertungen.SelectedNode.Tag > -1 Then btnAufbereiten_Click(sender, e)
|
|
|
End Sub
|
|
|
|
|
|
''' <summary>
|
|
|
''' Mousedown-Ereignis auf dem Tree
|
|
|
''' </summary>
|
|
|
''' <param name="sender"></param>
|
|
|
''' <param name="e"></param>
|
|
|
''' <remarks></remarks>
|
|
|
Private Sub TreeStruktur_MouseDown(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles TreeAuswertungen.MouseDown
|
|
|
Try
|
|
|
Dim mouseEvents As MouseEventArgs
|
|
|
mouseEvents = e
|
|
|
selectPoint.Y = mouseEvents.Y
|
|
|
selectPoint.X = mouseEvents.X
|
|
|
Me.TreeAuswertungen.SelectedNode = Me.TreeAuswertungen.GetNodeAt(selectPoint)
|
|
|
Catch ex As Exception
|
|
|
MsgBox(ex.Message)
|
|
|
End Try
|
|
|
End Sub
|
|
|
''' <summary>
|
|
|
''' Nach Selektion eines Nodes Parameter neu aufbereiten und Auswerungsm<73>glichkeit (CR/CSV) anzeigen
|
|
|
''' </summary>
|
|
|
''' <param name="sender"></param>
|
|
|
''' <param name="e"></param>
|
|
|
''' <remarks></remarks>
|
|
|
Private Sub TreeAuswertungen_AfterSelect(ByVal sender As System.Object, ByVal e As System.Windows.Forms.TreeViewEventArgs) Handles TreeAuswertungen.AfterSelect
|
|
|
Me.On_Load = True
|
|
|
Me.WhereModified = False
|
|
|
Me.PictureBox1.Visible = False
|
|
|
Try
|
|
|
Me.txtTitel1.Text = Me.TreeAuswertungen.SelectedNode.Text
|
|
|
Me.txtTitel2.Text = ""
|
|
|
Me.checkboxprintparam.Checked = True
|
|
|
Me.txtbeschreibung.Text = Me.TreeAuswertungen.SelectedNode.ToolTipText
|
|
|
DescriptionToolTip.SetToolTip(Me.txtbeschreibung, Me.txtbeschreibung.Text)
|
|
|
If Me.TreeAuswertungen.SelectedNode.Tag < 1 Then
|
|
|
Me.GrpParameter.Enabled = False
|
|
|
Me.BtnAufbereitenCR.Enabled = False
|
|
|
Me.btnAufbereitenCSV.Enabled = False
|
|
|
Me.grpcr.Enabled = False
|
|
|
Me.grpdb.Enabled = False
|
|
|
Exit Sub
|
|
|
End If
|
|
|
|
|
|
Me.auswertungen.Get_Auswertung(Me.TreeAuswertungen.SelectedNode.Tag)
|
|
|
If Me.auswertungen.MitarbeiterAuswertungsparameter.Rows.Count > 0 Then
|
|
|
Me.ComboboxMAParameter.Visible = True
|
|
|
Try
|
|
|
Me.ComboboxMAParameter.DataSource = Nothing
|
|
|
Me.ComboboxMAParameter.DataSource = Me.auswertungen.MitarbeiterAuswertungsparameter
|
|
|
Me.ComboboxMAParameter.DisplayMember = "Beschreibung"
|
|
|
Me.ComboboxMAParameter.ValueMember = "Mitarbeiter_AuswertungsparameterNr"
|
|
|
Me.ComboboxMAParameter.SelectedIndex = -1
|
|
|
Catch ex As Exception
|
|
|
End Try
|
|
|
Else
|
|
|
Me.ComboboxMAParameter.Visible = False
|
|
|
End If
|
|
|
If Me.auswertungen.AuswertungParameter.Rows.Count = 0 Then
|
|
|
Me.GrpParameter.Enabled = False
|
|
|
Else
|
|
|
Me.GrpParameter.Enabled = True
|
|
|
End If
|
|
|
If Me.auswertungen.Auswertung.bReport.Value = True Then
|
|
|
Me.BtnAufbereitenCR.Enabled = True
|
|
|
Me.grpcr.Enabled = True
|
|
|
Else
|
|
|
Me.BtnAufbereitenCR.Enabled = False
|
|
|
Me.grpcr.Enabled = False
|
|
|
End If
|
|
|
If Me.auswertungen.Auswertung.bExcel_Report.Value = True Then
|
|
|
Me.btnAufbereitenCSV.Enabled = True
|
|
|
Me.grpdb.Enabled = True
|
|
|
Else
|
|
|
Me.btnAufbereitenCSV.Enabled = False
|
|
|
Me.grpdb.Enabled = False
|
|
|
End If
|
|
|
|
|
|
Init_Params()
|
|
|
|
|
|
Catch ex As Exception
|
|
|
End Try
|
|
|
Me.On_Load = False
|
|
|
End Sub
|
|
|
|
|
|
#Region "Parameters"
|
|
|
|
|
|
#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
|
|
|
|
|
|
''' <summary>
|
|
|
''' Parameter initialisieren
|
|
|
''' </summary>
|
|
|
''' <remarks></remarks>
|
|
|
Private Sub Init_Params(Optional ByVal Initialize_ComboBox As Boolean = True)
|
|
|
If Initialize_ComboBox = True Then Me.ComboboxMAParameter.SelectedIndex = -1
|
|
|
Me.CtrlList.Clear()
|
|
|
Me.GetControl(Me, "cb*", CtrlList)
|
|
|
For Each x As ComboBox In CtrlList
|
|
|
Try
|
|
|
x.DataSource = Nothing
|
|
|
x.Items.Clear()
|
|
|
x.Enabled = False
|
|
|
x.Text = ""
|
|
|
Catch ex As Exception
|
|
|
End Try
|
|
|
Next
|
|
|
|
|
|
CtrlList.Clear()
|
|
|
Me.GetControl(Me, "cbandor*", CtrlList)
|
|
|
For Each x As ComboBox In CtrlList
|
|
|
x.Items.Clear()
|
|
|
x.Items.Add("und")
|
|
|
x.Items.Add("oder")
|
|
|
Next
|
|
|
|
|
|
Me.cbparam1.Enabled = True
|
|
|
Me.cbop1.Enabled = True
|
|
|
Me.cbvalue1.Enabled = True
|
|
|
Me.cbandor1.Enabled = True
|
|
|
|
|
|
Me.CtrlList.Clear()
|
|
|
Me.GetControl(Me, "cbparam*", CtrlList)
|
|
|
For Each x As ComboBox In CtrlList
|
|
|
For Each dr As DataRow In Me.auswertungen.AuswertungParameter.Rows
|
|
|
x.Items.Add(dr.Item("Bezeichnung"))
|
|
|
Next
|
|
|
Next
|
|
|
End Sub
|
|
|
|
|
|
''' <summary>
|
|
|
''' Selektion Parameter
|
|
|
''' </summary>
|
|
|
''' <param name="sender"></param>
|
|
|
''' <param name="e"></param>
|
|
|
''' <remarks></remarks>
|
|
|
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, cbparam6.SelectedIndexChanged, cbparam7.SelectedIndexChanged
|
|
|
If Me.On_Load Then Exit Sub
|
|
|
Me.WhereModified = False
|
|
|
Me.PictureBox1.Visible = False
|
|
|
|
|
|
Dim s As String = ParamNr(sender.name)
|
|
|
Dim cbop As String = "cbop" + s
|
|
|
dr = Findrow(sender.text)
|
|
|
|
|
|
'Operatoren festlegen
|
|
|
CtrlList.Clear()
|
|
|
Me.GetControl(Me, "cbop" + s, CtrlList)
|
|
|
For Each ob As ComboBox In CtrlList
|
|
|
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
|
|
|
|
|
|
' Wenn ein Datenbezug ab DB oder eingegeben, dann aufbereiten
|
|
|
If dr.Item("Feldbezug").ToString <> "" Then
|
|
|
'Feldbezug ab DB
|
|
|
If UCase(dr.Item("feldbezug").ToString.Substring(0, 3)) = "SP_" Then
|
|
|
Dim fb As New DataTable
|
|
|
fb = Me.auswertungen.get_rptparam_values(dr.Item("Feldbezug"))
|
|
|
CtrlList.Clear()
|
|
|
Me.GetControl(Me, "cbvalue" + s, CtrlList)
|
|
|
For Each x As ComboBox In CtrlList
|
|
|
x.DataSource = Nothing
|
|
|
x.Items.Clear()
|
|
|
x.DataSource = fb
|
|
|
x.DisplayMember = "Bezeichnung"
|
|
|
x.ValueMember = "KeyValue"
|
|
|
Next
|
|
|
Else
|
|
|
If UCase(dr.Item("Feldbezug").ToString.Substring(0, 6)) = "SELECT" Then
|
|
|
Dim fb As New DataTable
|
|
|
fb = Me.auswertungen.get_rptparam_values(dr.Item("Feldbezug"))
|
|
|
CtrlList.Clear()
|
|
|
Me.GetControl(Me, "cbvalue" + s, CtrlList)
|
|
|
For Each x As ComboBox In CtrlList
|
|
|
x.DataSource = Nothing
|
|
|
x.Items.Clear()
|
|
|
x.DataSource = fb
|
|
|
x.DisplayMember = "Bezeichnung"
|
|
|
x.ValueMember = "KeyValue"
|
|
|
Next
|
|
|
Else
|
|
|
'Fixer Feldbezug
|
|
|
If dr.Item("Feldbezug").ToString <> "" Then
|
|
|
Dim selval() As String = dr.Item("Feldbezug").ToString.Split(";")
|
|
|
Dim cbvalue As String = "cbvalue" + s
|
|
|
CtrlList.Clear()
|
|
|
Me.GetControl(Me, "cbvalue" + s, CtrlList)
|
|
|
For Each x As ComboBox In CtrlList
|
|
|
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 If
|
|
|
End Sub
|
|
|
|
|
|
''' <summary>
|
|
|
''' Datenrow in den zur Auswertung geh<65>renden Parameterliste suchen
|
|
|
''' </summary>
|
|
|
''' <param name="key"></param>
|
|
|
''' <returns></returns>
|
|
|
''' <remarks></remarks>
|
|
|
Private Function Findrow(ByVal key As String) As DataRow
|
|
|
For Each dr As DataRow In Me.auswertungen.AuswertungParameter.Rows
|
|
|
If dr.Item("Bezeichnung") = key Then
|
|
|
Return dr
|
|
|
End If
|
|
|
Next
|
|
|
End Function
|
|
|
|
|
|
'Param-Nr auslesen
|
|
|
Private Function ParamNr(ByVal s As String) As String
|
|
|
Return s.Substring(Len(s) - 1, 1)
|
|
|
End Function
|
|
|
''' <summary>
|
|
|
''' Where Bedingung f<>r die Abfrage zusammenstellen
|
|
|
''' </summary>
|
|
|
''' <remarks></remarks>
|
|
|
Private Sub Get_SQLWhere()
|
|
|
Me.auswertungen.ParamCollection.Clear()
|
|
|
Dim s As String
|
|
|
Dim i As Integer
|
|
|
Me.SQLWhere = ""
|
|
|
|
|
|
If Me.cbparam1.Text <> "" And Me.cbop1.Text <> "" And Me.cbvalue1.Text <> "" Then
|
|
|
Me.auswertungen.ParamCollection.Add(cbparam1.Text & " " & cbop1.Text & " " & cbvalue1.Text)
|
|
|
s = cbparam1.Text & " " & cbop1.Text & " " & cbvalue1.Text
|
|
|
SQLWhere = SQLWhere + get_where(1)
|
|
|
End If
|
|
|
If Me.cbandor1.Text <> "" And Me.cbparam2.Text <> "" And Me.cbop2.Text <> "" And Me.cbvalue2.Text <> "" Then
|
|
|
If Me.cbandor1.Text = "und" Then SQLWhere = SQLWhere + " and " + get_where(2)
|
|
|
If Me.cbandor1.Text = "oder" Then SQLWhere = SQLWhere + " or " + get_where(2)
|
|
|
Me.auswertungen.ParamCollection.Add(cbandor1.Text + " " + cbparam2.Text & " " & cbop2.Text & " " & cbvalue2.Text)
|
|
|
s = s & "' & Chr(10) & Chr(13) & '" & cbandor1.Text & " " & cbparam2.Text & " " & cbop2.Text & " " & cbvalue2.Text
|
|
|
End If
|
|
|
If Me.cbandor2.Text <> "" And Me.cbparam3.Text <> "" And Me.cbop3.Text <> "" And Me.cbvalue3.Text <> "" Then
|
|
|
If Me.cbandor2.Text = "und" Then SQLWhere = SQLWhere + " and " + get_where(3)
|
|
|
If Me.cbandor2.Text = "oder" Then SQLWhere = SQLWhere + " or " + get_where(3)
|
|
|
Me.auswertungen.ParamCollection.Add(cbandor2.Text + " " + cbparam3.Text & " " & cbop3.Text & " " & cbvalue3.Text)
|
|
|
s = s + " " + cbandor2.Text + " " + cbparam3.Text & " " & cbop3.Text & " " & cbvalue3.Text
|
|
|
End If
|
|
|
If Me.cbandor3.Text <> "" And Me.cbparam4.Text <> "" And Me.cbop4.Text <> "" And Me.cbvalue4.Text <> "" Then
|
|
|
If Me.cbandor3.Text = "und" Then SQLWhere = SQLWhere + " and " + get_where(4)
|
|
|
If Me.cbandor3.Text = "oder" Then SQLWhere = SQLWhere + " or " + get_where(4)
|
|
|
Me.auswertungen.ParamCollection.Add(cbandor3.Text + " " + cbparam4.Text & " " & cbop4.Text & " " & cbvalue4.Text)
|
|
|
s = s + " " + cbandor3.Text + " " + cbparam4.Text & " " & cbop4.Text & " " & cbvalue4.Text
|
|
|
End If
|
|
|
|
|
|
If Me.cbandor4.Text <> "" And Me.cbparam5.Text <> "" And Me.cbop5.Text <> "" And Me.cbvalue5.Text <> "" Then
|
|
|
If Me.cbandor4.Text = "und" Then SQLWhere = SQLWhere + " and " + get_where(5)
|
|
|
If Me.cbandor4.Text = "oder" Then SQLWhere = SQLWhere + " or " + get_where(5)
|
|
|
Me.auswertungen.ParamCollection.Add(cbandor4.Text + " " + cbparam5.Text & " " & cbop5.Text & " " & cbvalue5.Text)
|
|
|
s = s + " " + cbandor4.Text + " " + cbparam5.Text & " " & cbop5.Text & " " & cbvalue5.Text
|
|
|
End If
|
|
|
If Me.cbandor5.Text <> "" And Me.cbparam6.Text <> "" And Me.cbop6.Text <> "" And Me.cbvalue6.Text <> "" Then
|
|
|
If Me.cbandor5.Text = "und" Then SQLWhere = SQLWhere + " and " + get_where(6)
|
|
|
If Me.cbandor5.Text = "oder" Then SQLWhere = SQLWhere + " or " + get_where(6)
|
|
|
Me.auswertungen.ParamCollection.Add(cbandor5.Text + " " + cbparam6.Text & " " & cbop6.Text & " " & cbvalue6.Text)
|
|
|
s = s + " " + cbandor5.Text + " " + cbparam6.Text & " " & cbop6.Text & " " & cbvalue6.Text
|
|
|
End If
|
|
|
If Me.cbandor6.Text <> "" And Me.cbparam7.Text <> "" And Me.cbop7.Text <> "" And Me.cbvalue7.Text <> "" Then
|
|
|
If Me.cbandor6.Text = "und" Then SQLWhere = SQLWhere + " and " + get_where(7)
|
|
|
If Me.cbandor6.Text = "oder" Then SQLWhere = SQLWhere + " or " + get_where(7)
|
|
|
Me.auswertungen.ParamCollection.Add(cbandor6.Text + " " + cbparam7.Text & " " & cbop7.Text & " " & cbvalue7.Text)
|
|
|
s = s + " " + cbandor6.Text + " " + cbparam7.Text & " " & cbop7.Text & " " & cbvalue7.Text
|
|
|
End If
|
|
|
Me.auswertungen.FullParam = s
|
|
|
End Sub
|
|
|
|
|
|
''' <summary>
|
|
|
''' Where zusammenstellen
|
|
|
''' </summary>
|
|
|
''' <param name="nr"></param>
|
|
|
''' <returns></returns>
|
|
|
''' <remarks></remarks>
|
|
|
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
|
|
|
Dim wertvalue As String = ""
|
|
|
Dim wertdbfeld As String = ""
|
|
|
Dim wertop As String = op.Text
|
|
|
|
|
|
CtrlList.Clear()
|
|
|
Me.GetControl(Me, "cbparam" + Trim(Str(nr)), CtrlList)
|
|
|
param = CtrlList.Item(0)
|
|
|
|
|
|
CtrlList.Clear()
|
|
|
Me.GetControl(Me, "cbop" + Trim(Str(nr)), CtrlList)
|
|
|
op = CtrlList.Item(0)
|
|
|
|
|
|
CtrlList.Clear()
|
|
|
Me.GetControl(Me, "cbvalue" + Trim(Str(nr)), CtrlList)
|
|
|
val = CtrlList.Item(0)
|
|
|
|
|
|
dr = Me.Findrow(param.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 "DATEPART"
|
|
|
Select Case wertvalue
|
|
|
Case "letzte Woche"
|
|
|
Return wertdbfeld & " between dbo.get_dateperiode('LastWeek',1) and dbo.get_dateperiode('LastWeek',0)"
|
|
|
Case "letzter Monat"
|
|
|
Return wertdbfeld & " between dbo.get_dateperiode('LastMonth',1) and dbo.get_dateperiode('LastMonth',0)"
|
|
|
Case "letztes Quartal"
|
|
|
Return wertdbfeld & " between dbo.get_dateperiode('LastQuarter',1) and dbo.get_dateperiode('LastQuarter',0)"
|
|
|
Case "letztes Jahr"
|
|
|
Return wertdbfeld & " between dbo.get_dateperiode('LastYear',1) and dbo.get_dateperiode('LastYear',0)"
|
|
|
Case "aktuelle Woche"
|
|
|
Return wertdbfeld & " between dbo.get_dateperiode('ActWeek',1) and dbo.get_dateperiode('ActWeek',0)"
|
|
|
Case "aktueller Monat"
|
|
|
Return wertdbfeld & " between dbo.get_dateperiode('ActMonth',1) and dbo.get_dateperiode('ActMonth',0)"
|
|
|
Case "aktuelles Quartal"
|
|
|
Return wertdbfeld & " between dbo.get_dateperiode('ActQuarter',1) and dbo.get_dateperiode('ActQuarter',0)"
|
|
|
Case "aktuelles Jahr"
|
|
|
Return wertdbfeld & " between dbo.get_dateperiode('ActYear',1) and dbo.get_dateperiode('ActYear',0)"
|
|
|
End Select
|
|
|
'Dim dtfrom As DateTime
|
|
|
'Dim dtto As DateTime
|
|
|
'dtfrom = Today
|
|
|
'CalcDate(wertvalue, dtfrom, dtto)
|
|
|
'Return wertdbfeld & " between '" & Formateddate(dtfrom, True) & "' and '" & Formateddate(dtto, False) + "'"
|
|
|
'Dim dp As String
|
|
|
'Select Case wertvalue
|
|
|
' Case "letzte Woche"
|
|
|
' CalcDate(wertvalue, dtfrom, dtto)
|
|
|
' Case "letzte Woche"
|
|
|
' Return "cast(datepart(week," & wertdbfeld & ") as int) " & wertop & " cast(datepart(week, getdate()) as int) - 1"
|
|
|
' Case "letzer Monat"
|
|
|
' Return "cast(datepart(month," & wertdbfeld & ") as int) " & wertop & " cast(datepart(month, getdate()) as int) - 1"
|
|
|
' Case "letztes Quartal"
|
|
|
' Return "cast(datepart(quarter," & wertdbfeld & ") as int) " & wertop & " cast(datepart(quarter, getdate()) as int) - 1"
|
|
|
' Case "letztes Jahr"
|
|
|
' Return "year(" & wertdbfeld & ") " & wertop & " year(getdate()) - 1"
|
|
|
' Case "aktuelle Woche"
|
|
|
' Return "cast(datepart(week," & wertdbfeld & ") as int) " & wertop & " cast(datepart(week, getdate()) as int)"
|
|
|
' Case "aktueller Monat"
|
|
|
' Return "cast(datepart(month," & wertdbfeld & ") as int) " & wertop & " cast(datepart(month, getdate()) as int)"
|
|
|
' Case "aktuelles Quartal"
|
|
|
' Return "cast(datepart(quarter," & wertdbfeld & ") as int) " & wertop & " cast(datepart(quarter, getdate()) as int)"
|
|
|
' Case "aktuelles Jahr"
|
|
|
' Return "year(" & wertdbfeld & ") " & wertop & " year(getdate())"
|
|
|
'End Select
|
|
|
Case "DATUM"
|
|
|
Select Case wertop
|
|
|
Case "="
|
|
|
Return wertdbfeld + " > convert(datetime,'" + wertvalue + " 00:00:00',104) and " + wertdbfeld + " < convert(datetime,'" + wertvalue + " 23:59:59',104)"
|
|
|
Case "<>"
|
|
|
Return wertdbfeld + " < convert(datetime,'" + wertvalue + " 00:00:00',104) and " + wertdbfeld + " > convert(datetime,'" + wertvalue + " 23:59:59',104)"
|
|
|
Case ">="
|
|
|
Return wertdbfeld + " " + wertop + " convert(datetime,'" + wertvalue + " 00:00:00',104)"
|
|
|
Case ">"
|
|
|
Return wertdbfeld + " " + wertop + " convert(datetime,'" + wertvalue + " 23:59:59',104)"
|
|
|
Case "<"
|
|
|
Return wertdbfeld + " " + wertop + " convert(datetime,'" + wertvalue + " 00:00:00',104)"
|
|
|
Case "<="
|
|
|
Return wertdbfeld + " " + wertop + " convert(datetime,'" + wertvalue + " 23:59:59',104)"
|
|
|
Case Else
|
|
|
Return wertdbfeld + " " + wertop + " convert(datetime,'" + wertvalue + " 00:00:00',104)"
|
|
|
End Select
|
|
|
Case "DATUM+1"
|
|
|
Dim d As DateTime = wertvalue
|
|
|
d = DateAdd(DateInterval.Day, 1, d)
|
|
|
wertvalue = d.ToString
|
|
|
Return wertdbfeld + " " + wertop + " convert(datetime,'" + wertvalue + " 00:00:00',104)"
|
|
|
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
|
|
|
#End Region
|
|
|
|
|
|
|
|
|
|
|
|
Private Sub cbvalue1_DropDown(ByVal sender As Object, ByVal e As System.EventArgs) Handles cbvalue1.DropDown, cbvalue2.DropDown, _
|
|
|
cbvalue3.DropDown, cbvalue4.DropDown, cbvalue5.DropDown, cbvalue6.DropDown, cbvalue7.DropDown
|
|
|
Try
|
|
|
Dim s As String = ParamNr(sender.name)
|
|
|
Dim nr As Integer = s
|
|
|
Dim param As New ComboBox
|
|
|
Dim op As New ComboBox
|
|
|
Dim val As New ComboBox
|
|
|
CtrlList.Clear()
|
|
|
Me.GetControl(Me, "cbparam" + Trim(Str(nr)), CtrlList)
|
|
|
param = CtrlList.Item(0)
|
|
|
|
|
|
CtrlList.Clear()
|
|
|
Me.GetControl(Me, "cbvalue" + Trim(Str(nr)), CtrlList)
|
|
|
val = CtrlList.Item(0)
|
|
|
|
|
|
dr = Me.Findrow(param.Text)
|
|
|
|
|
|
If dr.Item("Paramtype") = "Datum" Then
|
|
|
Dim LocalMousePosition As Point
|
|
|
LocalMousePosition = val.PointToClient(Cursor.Position)
|
|
|
|
|
|
Dim f As New frmCalendar
|
|
|
|
|
|
f.Top = Me.Top + val.Top + 200
|
|
|
f.Left = Me.Left + val.Width + 399
|
|
|
f.ShowDialog()
|
|
|
If f.DialogResult = Windows.Forms.DialogResult.OK Then
|
|
|
CtrlList.Clear()
|
|
|
Me.GetControl(Me, "cbvalue" + Trim(Str(nr)), CtrlList)
|
|
|
param = CtrlList.Item(0)
|
|
|
param.Text = f.MonthCalendar1.SelectionRange.Start.Date
|
|
|
param.Focus()
|
|
|
End If
|
|
|
End If
|
|
|
Catch ex As Exception
|
|
|
End Try
|
|
|
End Sub
|
|
|
|
|
|
Private Sub BtnInitParam_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnInitParam.Click
|
|
|
Me.Init_Params()
|
|
|
End Sub
|
|
|
|
|
|
Private Sub cbandor1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cbandor1.SelectedIndexChanged, _
|
|
|
cbandor2.SelectedIndexChanged, cbandor3.SelectedIndexChanged, cbandor3.SelectedIndexChanged, cbandor4.SelectedIndexChanged, cbandor5.SelectedIndexChanged, _
|
|
|
cbandor6.SelectedIndexChanged
|
|
|
If Me.On_Load Then Exit Sub
|
|
|
Me.WhereModified = False
|
|
|
Me.PictureBox1.Visible = False
|
|
|
s = Me.ParamNr(sender.name)
|
|
|
s = s + 1
|
|
|
CtrlList.Clear()
|
|
|
Me.GetControl(Me, "cbparam" + s, CtrlList)
|
|
|
For Each x As ComboBox In CtrlList
|
|
|
x.Enabled = True
|
|
|
Next
|
|
|
CtrlList.Clear()
|
|
|
Me.GetControl(Me, "cbop" + s, CtrlList)
|
|
|
For Each x As ComboBox In CtrlList
|
|
|
x.Enabled = True
|
|
|
Next
|
|
|
CtrlList.Clear()
|
|
|
Me.GetControl(Me, "cbvalue" + s, CtrlList)
|
|
|
For Each x As ComboBox In CtrlList
|
|
|
x.Enabled = True
|
|
|
Next
|
|
|
CtrlList.Clear()
|
|
|
Me.GetControl(Me, "cbandor" + s, CtrlList)
|
|
|
For Each x As ComboBox In CtrlList
|
|
|
x.Enabled = True
|
|
|
Next
|
|
|
End Sub
|
|
|
|
|
|
Private Sub Init_ParamRow(ByVal s As String)
|
|
|
s = s + 1
|
|
|
CtrlList.Clear()
|
|
|
Me.GetControl(Me, "cbparam" + s, CtrlList)
|
|
|
For Each x As ComboBox In CtrlList
|
|
|
Try
|
|
|
x.Text = ""
|
|
|
Catch ex As Exception
|
|
|
End Try
|
|
|
x.Enabled = False
|
|
|
Next
|
|
|
CtrlList.Clear()
|
|
|
Me.GetControl(Me, "cbop" + s, CtrlList)
|
|
|
For Each x As ComboBox In CtrlList
|
|
|
x.Text = ""
|
|
|
x.Enabled = False
|
|
|
Next
|
|
|
CtrlList.Clear()
|
|
|
Me.GetControl(Me, "cbvalue" + s, CtrlList)
|
|
|
For Each x As ComboBox In CtrlList
|
|
|
x.Text = ""
|
|
|
x.Enabled = False
|
|
|
Next
|
|
|
CtrlList.Clear()
|
|
|
Me.GetControl(Me, "cbandor" + s, CtrlList)
|
|
|
For Each x As ComboBox In CtrlList
|
|
|
x.Text = ""
|
|
|
Next
|
|
|
End Sub
|
|
|
|
|
|
|
|
|
Private Sub BtnParamDel1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnParamDel1.Click, BtnParamDel2.Click, _
|
|
|
BtnParamDel3.Click, BtnParamDel4.Click, BtnParamDel5.Click, BtnParamDel6.Click
|
|
|
Init_ParamRow(Me.ParamNr(sender.name))
|
|
|
End Sub
|
|
|
|
|
|
Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
|
|
|
Me.On_Load = True
|
|
|
Dim s As String
|
|
|
CtrlList.Clear()
|
|
|
Me.GetControl(Me, "cbandor*", CtrlList)
|
|
|
For Each x As ComboBox In CtrlList
|
|
|
s = s + ParamNr(x.Name) + "~" + x.Text + ";"
|
|
|
Next
|
|
|
s = s + "|"
|
|
|
CtrlList.Clear()
|
|
|
Me.GetControl(Me, "cbparam*", CtrlList)
|
|
|
For Each x As ComboBox In CtrlList
|
|
|
s = s + ParamNr(x.Name) + "~" + x.Text + ";"
|
|
|
Next
|
|
|
s = s + "|"
|
|
|
CtrlList.Clear()
|
|
|
Me.GetControl(Me, "cbop*", CtrlList)
|
|
|
For Each x As ComboBox In CtrlList
|
|
|
s = s + ParamNr(x.Name) + "~" + x.Text + ";"
|
|
|
Next
|
|
|
s = s + "|"
|
|
|
CtrlList.Clear()
|
|
|
Me.GetControl(Me, "cbval*", CtrlList)
|
|
|
For Each x As ComboBox In CtrlList
|
|
|
s = s + ParamNr(x.Name) + "~" + x.Text + ";"
|
|
|
Next
|
|
|
Dim Bez As String = InputBox("Bezeichnung f<>r die Parameter:", "Auswertungsparameter sichern")
|
|
|
If Me.WhereModified = False Then
|
|
|
If Bez <> "" Then Me.auswertungen.Save_MAParameter(Me.auswertungen.Auswertung.iAuswertungNr.Value, Bez, s, Me.txtTitel1.Text, Me.txtTitel2.Text, Me.checkboxprintparam.Checked, "")
|
|
|
Else
|
|
|
If Bez <> "" Then Me.auswertungen.Save_MAParameter(Me.auswertungen.Auswertung.iAuswertungNr.Value, Bez, s, Me.txtTitel1.Text, Me.txtTitel2.Text, Me.checkboxprintparam.Checked, Me.SQLWhere)
|
|
|
End If
|
|
|
If Me.auswertungen.MitarbeiterAuswertungsparameter.Rows.Count > 0 Then
|
|
|
Me.ComboboxMAParameter.Visible = True
|
|
|
Try
|
|
|
Me.ComboboxMAParameter.DataSource = Nothing
|
|
|
Me.ComboboxMAParameter.DataSource = Me.auswertungen.MitarbeiterAuswertungsparameter
|
|
|
Me.ComboboxMAParameter.DisplayMember = "Beschreibung"
|
|
|
Me.ComboboxMAParameter.ValueMember = "Mitarbeiter_AuswertungsparameterNr"
|
|
|
Catch ex As Exception
|
|
|
End Try
|
|
|
Else
|
|
|
Me.ComboboxMAParameter.Visible = False
|
|
|
End If
|
|
|
Me.On_Load = False
|
|
|
Try
|
|
|
For Each dr As DataRow In Me.auswertungen.MitarbeiterAuswertungsparameter.Rows
|
|
|
If dr.Item("Beschreibung") = Bez Then
|
|
|
Me.ComboboxMAParameter.SelectedValue = dr.Item(0)
|
|
|
Me.ComboboxMAParameter_SelectedIndexChanged(sender, e)
|
|
|
End If
|
|
|
Next
|
|
|
Catch
|
|
|
End Try
|
|
|
|
|
|
End Sub
|
|
|
|
|
|
Private Sub ComboboxMAParameter_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ComboboxMAParameter.SelectedIndexChanged
|
|
|
If Me.On_Load = True Then Exit Sub
|
|
|
Dim wheremod As Boolean = False
|
|
|
Me.WhereModified = False
|
|
|
Me.PictureBox1.Visible = False
|
|
|
Try
|
|
|
Dim s As String = ""
|
|
|
For Each dr As DataRow In Me.auswertungen.MitarbeiterAuswertungsparameter.Rows
|
|
|
If dr.Item("Mitarbeiter_AuswertungsparameterNr") = Me.ComboboxMAParameter.SelectedValue Then
|
|
|
s = dr.Item("Parameterdaten")
|
|
|
Me.txtTitel1.Text = dr.Item("Titelzeile1")
|
|
|
Me.txtTitel2.Text = dr.Item("Titelzeile2")
|
|
|
Me.checkboxprintparam.Checked = dr.Item("ParamPrint")
|
|
|
If dr.Item("SQLWhere").ToString <> "" Then
|
|
|
Me.SQLWhere = dr.Item("SQLWhere").ToString
|
|
|
Me.WhereModified = True
|
|
|
wheremod = True
|
|
|
Me.PictureBox1.Visible = True
|
|
|
End If
|
|
|
End If
|
|
|
Next
|
|
|
If s = "" Then Exit Sub
|
|
|
|
|
|
Me.Init_Params(False)
|
|
|
|
|
|
|
|
|
Dim grp() As String = s.Split("|")
|
|
|
Dim andor As String = grp(0)
|
|
|
Dim param As String = grp(1)
|
|
|
Dim op As String = grp(2)
|
|
|
Dim val As String = grp(3)
|
|
|
|
|
|
Dim tmpvalue() As String
|
|
|
Dim ValueItem() As String
|
|
|
Dim tmpparam() As String
|
|
|
Dim ParamItem() As String
|
|
|
Dim tmpop() As String
|
|
|
Dim opItem() As String
|
|
|
Dim tmpandor() As String
|
|
|
Dim andorItem() As String
|
|
|
Dim tmpcbidx As Integer
|
|
|
Dim vals() As String
|
|
|
|
|
|
Dim CTLIndex As Integer
|
|
|
Dim CTLAndOr As String
|
|
|
Dim CTLParam As String
|
|
|
Dim CTLOP As String
|
|
|
Dim CTLValue As String
|
|
|
Dim CTLSplit() As String
|
|
|
Dim SelIndexSet As Boolean
|
|
|
|
|
|
|
|
|
Dim i As Integer
|
|
|
For i = 6 To 0 Step -1
|
|
|
tmpvalue = val.ToString.Split(";")
|
|
|
If tmpvalue(i).ToString.Length > 2 Then
|
|
|
ValueItem = tmpvalue(i).Split("~")
|
|
|
CTLIndex = ValueItem(0)
|
|
|
CTLValue = ValueItem(1)
|
|
|
|
|
|
tmpparam = param.Split(";")
|
|
|
ParamItem = tmpparam(i).Split("~")
|
|
|
CTLParam = ParamItem(1)
|
|
|
|
|
|
tmpop = op.Split(";")
|
|
|
opItem = tmpop(i).Split("~")
|
|
|
CTLOP = opItem(1)
|
|
|
|
|
|
If i > 1 Then
|
|
|
tmpandor = andor.Split(";")
|
|
|
andorItem = tmpandor(i - 1).Split("~")
|
|
|
CTLAndOr = andorItem(1)
|
|
|
End If
|
|
|
|
|
|
'Parameter
|
|
|
Dim l As New List(Of Control)
|
|
|
Me.GetControl(Me, "cbparam" + Trim(Str(CTLIndex)), l)
|
|
|
Dim x As ComboBox = l(0)
|
|
|
|
|
|
SelIndexSet = False
|
|
|
Dim ii As Integer = 0
|
|
|
For ii = 0 To x.Items.Count - 1
|
|
|
If x.Items(ii).ToString = CTLParam Then
|
|
|
x.SelectedIndex = ii
|
|
|
SelIndexSet = True
|
|
|
End If
|
|
|
Next
|
|
|
If SelIndexSet = False Then x.Text = CTLParam
|
|
|
|
|
|
'Operator
|
|
|
l.Clear()
|
|
|
Me.GetControl(Me, "cbop" + Trim(Str(CTLIndex)), l)
|
|
|
x = l(0)
|
|
|
SelIndexSet = False
|
|
|
For ii = 0 To x.Items.Count - 1
|
|
|
If x.Items(ii).ToString = CTLOP Then
|
|
|
x.SelectedIndex = ii
|
|
|
SelIndexSet = True
|
|
|
End If
|
|
|
Next
|
|
|
If SelIndexSet = False Then x.Text = CTLOP
|
|
|
|
|
|
'value
|
|
|
l.Clear()
|
|
|
Me.GetControl(Me, "cbvalue" + Trim(Str(CTLIndex)), l)
|
|
|
x = l(0)
|
|
|
SelIndexSet = False
|
|
|
For ii = 0 To x.Items.Count - 1
|
|
|
If x.Items(ii).ToString = CTLValue Then
|
|
|
x.SelectedIndex = ii
|
|
|
SelIndexSet = True
|
|
|
End If
|
|
|
Next
|
|
|
If SelIndexSet = False Then x.Text = CTLValue
|
|
|
|
|
|
'andor
|
|
|
l.Clear()
|
|
|
Me.GetControl(Me, "cbandor" + Trim(Str(CTLIndex)), l)
|
|
|
x = l(0)
|
|
|
SelIndexSet = False
|
|
|
For ii = 0 To x.Items.Count - 1
|
|
|
If x.Items(ii).ToString = CTLAndOr Then
|
|
|
x.SelectedIndex = ii
|
|
|
SelIndexSet = True
|
|
|
End If
|
|
|
Next
|
|
|
If SelIndexSet = False Then x.Text = CTLAndOr
|
|
|
|
|
|
End If
|
|
|
Next
|
|
|
|
|
|
If wheremod = True Then
|
|
|
Me.WhereModified = True
|
|
|
Me.PictureBox1.Visible = True
|
|
|
End If
|
|
|
|
|
|
|
|
|
Catch ex As Exception
|
|
|
End Try
|
|
|
End Sub
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Private Sub ComboboxMAParameter_VisibleChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ComboboxMAParameter.VisibleChanged
|
|
|
Me.btnDeleteSavedParam.Visible = Me.ComboboxMAParameter.Visible
|
|
|
End Sub
|
|
|
|
|
|
Private Sub btnDeleteSavedParam_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDeleteSavedParam.Click
|
|
|
Me.On_Load = True
|
|
|
If Me.ComboboxMAParameter.SelectedIndex = -1 Then Exit Sub
|
|
|
|
|
|
Me.auswertungen.Delete_Parameter(Me.ComboboxMAParameter.SelectedValue)
|
|
|
'Me.Init_Params()
|
|
|
Me.txtTitel1.Text = Me.TreeAuswertungen.SelectedNode.Text
|
|
|
Me.txtTitel2.Text = ""
|
|
|
Me.checkboxprintparam.Checked = True
|
|
|
|
|
|
If Me.auswertungen.MitarbeiterAuswertungsparameter.Rows.Count > 0 Then
|
|
|
Me.ComboboxMAParameter.Visible = True
|
|
|
Try
|
|
|
Me.ComboboxMAParameter.DataSource = Nothing
|
|
|
Me.ComboboxMAParameter.DataSource = Me.auswertungen.MitarbeiterAuswertungsparameter
|
|
|
Me.ComboboxMAParameter.DisplayMember = "Beschreibung"
|
|
|
Me.ComboboxMAParameter.ValueMember = "Mitarbeiter_AuswertungsparameterNr"
|
|
|
Me.ComboboxMAParameter.SelectedIndex = -1
|
|
|
Catch ex As Exception
|
|
|
End Try
|
|
|
Me.On_Load = False
|
|
|
Else
|
|
|
Me.ComboboxMAParameter.Visible = False
|
|
|
End If
|
|
|
|
|
|
End Sub
|
|
|
|
|
|
Private Sub AlleKnotenSchliessenToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AlleKnotenSchliessenToolStripMenuItem.Click
|
|
|
Me.TreeAuswertungen.CollapseAll()
|
|
|
End Sub
|
|
|
|
|
|
Private Sub AlleKnoten<EFBFBD>ffnenToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AlleKnoten<EFBFBD>ffnenToolStripMenuItem.Click
|
|
|
Me.TreeAuswertungen.ExpandAll()
|
|
|
End Sub
|
|
|
|
|
|
'#Region "Datumsfunktionen"
|
|
|
|
|
|
' Private Function CalcDate(ByVal typ As String, ByRef dtfrom As DateTime, ByRef dtto As DateTime)
|
|
|
' Select Case UCase(typ)
|
|
|
' Case "LETZTE WOCHE"
|
|
|
' GetDates(dtfrom, dtto)
|
|
|
' Case "LETZTER MONAT"
|
|
|
' dtfrom = DateAdd(DateInterval.Month, -1, dtfrom)
|
|
|
' dtto = GetLastDayOfMonth(dtfrom)
|
|
|
' dtfrom = GetFirstDayOfMonth(dtfrom)
|
|
|
' Case "LETZTES QUARTAL"
|
|
|
' Dim d As DateTime
|
|
|
' d = DateAdd(DateInterval.Quarter, -1, dtfrom)
|
|
|
' dtfrom = FirstDayOfQuarter(d)
|
|
|
' dtto = LastDayOfQuarter(d)
|
|
|
' Case "LETZTES JAHR"
|
|
|
' dtfrom = DateAdd(DateInterval.Year, -1, dtfrom)
|
|
|
' Dim y As Integer = Year(dtfrom)
|
|
|
' dtfrom = "01.01." + Str(y)
|
|
|
' dtto = "31.12." + Str(y)
|
|
|
' Case "AKTUELLE WOCHE"
|
|
|
' dtfrom = DateAdd(DateInterval.Day, 7, dtfrom)
|
|
|
' GetDates(dtfrom, dtto)
|
|
|
' Case "AKTUELLER MONAT"
|
|
|
' dtfrom = dtfrom
|
|
|
' dtto = GetLastDayOfMonth(dtfrom)
|
|
|
' dtfrom = GetFirstDayOfMonth(dtfrom)
|
|
|
' Case "AKTUELLES QUARTAL"
|
|
|
' Dim d As DateTime
|
|
|
' d = dtfrom
|
|
|
' dtfrom = FirstDayOfQuarter(d)
|
|
|
' dtto = LastDayOfQuarter(d)
|
|
|
' Case "AKTUELLES JAHR"
|
|
|
' Dim y As Integer = Year(dtfrom)
|
|
|
' dtfrom = "01.01." + Str(y)
|
|
|
' dtto = "31.12." + Str(y)
|
|
|
' End Select
|
|
|
' End Function
|
|
|
|
|
|
' Private Sub GetDates(ByRef stDate As Date, ByRef endDate As Date)
|
|
|
' Dim offset As Double = 0
|
|
|
' Select Case stDate.DayOfWeek
|
|
|
' Case DayOfWeek.Monday : offset = 0
|
|
|
' Case DayOfWeek.Tuesday : offset = -1
|
|
|
' Case DayOfWeek.Wednesday : offset = -2
|
|
|
' Case DayOfWeek.Thursday : offset = -3
|
|
|
' Case DayOfWeek.Friday : offset = -4
|
|
|
' Case DayOfWeek.Saturday : offset = -5
|
|
|
' Case DayOfWeek.Sunday : offset = -6
|
|
|
' End Select
|
|
|
' endDate = DateAdd(DateInterval.Day, (offset - 1), stDate)
|
|
|
' stDate = DateAdd(DateInterval.Day, -7 + offset, stDate)
|
|
|
' End Sub
|
|
|
|
|
|
' Private Function GetFirstDayOfMonth(ByVal dtDate As Date) As DateTime
|
|
|
' Dim dtFrom As Date = dtDate
|
|
|
' dtFrom = dtFrom.AddDays(-(dtFrom.Day - 1))
|
|
|
' Return dtFrom
|
|
|
' End Function
|
|
|
|
|
|
|
|
|
' Private Function GetLastDayOfMonth(ByVal dtDate As Date) As DateTime
|
|
|
' Dim dtTo As Date = dtDate
|
|
|
' dtTo = dtTo.AddMonths(1)
|
|
|
' dtTo = dtTo.AddDays(-(dtTo.Day))
|
|
|
' Return dtTo
|
|
|
' End Function
|
|
|
|
|
|
' Public Function FirstDayOfQuarter(ByVal DateIn As DateTime) _
|
|
|
' As DateTime
|
|
|
' ' Calculate first day of DateIn quarter,
|
|
|
' ' with quarters starting at the beginning of Jan/Apr/Jul/Oct
|
|
|
' Dim intQuarterNum As Integer = (Month(DateIn) - 1) \ 3 + 1
|
|
|
' Return DateSerial(Year(DateIn), 3 * intQuarterNum - 2, 1)
|
|
|
' End Function
|
|
|
|
|
|
|
|
|
' Public Function LastDayOfQuarter(ByVal DateIn As Date) As Date
|
|
|
' ' Calculate last day of DateIn quarter,
|
|
|
' ' with quarters ending at the end of Mar/Jun/Sep/Dec
|
|
|
' Dim intQuarterNum As Integer = (Month(DateIn) - 1) \ 3 + 1
|
|
|
' Return DateSerial(Year(DateIn), 3 * intQuarterNum + 1, 0)
|
|
|
' End Function
|
|
|
|
|
|
|
|
|
' Private Function Formateddate(ByVal dt As DateTime, ByVal start As Boolean) As String
|
|
|
' If start Then
|
|
|
' Return Format(dt, Globals.clsapplication.sReport_Datum_Format.Value) & " 00:00:00"
|
|
|
' Else
|
|
|
' Return Format(dt, Globals.clsapplication.sReport_Datum_Format.Value) & " 23:59:59"
|
|
|
' End If
|
|
|
|
|
|
' End Function
|
|
|
'#End Region
|
|
|
|
|
|
|
|
|
Private Sub btnEditSQL_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEditSQL.Click
|
|
|
If Me.WhereModified = False Then Get_SQLWhere()
|
|
|
Dim f As New frmEditWhere
|
|
|
f.sql = Me.SQLWhere
|
|
|
f.ShowDialog()
|
|
|
If f.DialogResult = Windows.Forms.DialogResult.OK Then
|
|
|
If f.sql <> "" Then
|
|
|
Me.SQLWhere = f.sql
|
|
|
Me.WhereModified = True
|
|
|
Me.PictureBox1.Visible = True
|
|
|
Else
|
|
|
Me.WhereModified = False
|
|
|
Me.PictureBox1.Visible = False
|
|
|
End If
|
|
|
End If
|
|
|
f.Dispose()
|
|
|
End Sub
|
|
|
|
|
|
Private Sub cbop1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cbop1.SelectedIndexChanged, cbop2.SelectedIndexChanged, _
|
|
|
cbop3.SelectedIndexChanged, cbop4.SelectedIndexChanged, cbop5.SelectedIndexChanged, cbop6.SelectedIndexChanged, cbop7.SelectedIndexChanged
|
|
|
If Me.On_Load Then Exit Sub
|
|
|
Me.WhereModified = False
|
|
|
Me.PictureBox1.Visible = False
|
|
|
End Sub
|
|
|
|
|
|
Private Sub cbvalue1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cbvalue1.SelectedIndexChanged, _
|
|
|
cbvalue2.SelectedIndexChanged, cbvalue3.SelectedIndexChanged, cbvalue4.SelectedIndexChanged, cbvalue5.SelectedIndexChanged, cbvalue6.SelectedIndexChanged, cbvalue7.SelectedIndexChanged, _
|
|
|
cbvalue1.TextChanged, cbvalue2.TextChanged, cbvalue3.TextChanged, cbvalue4.TextChanged, cbvalue5.TextChanged, cbvalue6.TextChanged, cbvalue7.TextChanged
|
|
|
If Me.On_Load Then Exit Sub
|
|
|
Me.WhereModified = False
|
|
|
Me.PictureBox1.Visible = False
|
|
|
End Sub
|
|
|
|
|
|
|
|
|
Private Sub tsbtnReportDesign_Click(sender As Object, e As EventArgs) Handles tsbtnReportDesign.Click
|
|
|
Me.Cursor = Cursors.WaitCursor
|
|
|
Me.auswertungen.TitelZeile1 = Me.txtTitel1.Text
|
|
|
Me.auswertungen.TitelZeile2 = Me.txtTitel2.Text
|
|
|
|
|
|
If Me.WhereModified = False Then Get_SQLWhere()
|
|
|
Me.Auswertungsdaten = Me.auswertungen.Get_Auswertungsdaten(Me.auswertungen.Auswertung.sSQL.Value, SQLWhere, Me.auswertungen.Auswertung.sSQLType.Value)
|
|
|
Dim f As New frmcrreporting(Me.Auswertungsdaten, Me.auswertungen.Auswertung.iAuswertungNr.Value, Me.auswertungen, Me.checkboxprintparam.Checked, True)
|
|
|
f.Text = "Auswertung " + Me.TreeAuswertungen.SelectedNode.Text
|
|
|
f.MdiParent = Me.MdiParent
|
|
|
Me.Cursor = Cursors.Default
|
|
|
f.Show()
|
|
|
f.DesignReport()
|
|
|
|
|
|
End Sub
|
|
|
|
|
|
Private Sub TSBtnSetSecurityObject_Click(sender As Object, e As EventArgs) Handles TSBtnSetSecurityObject.Click
|
|
|
If InputBox("Passwort:") <> "341211" Then Exit Sub
|
|
|
sec.List_Form_Controls(Me)
|
|
|
sec.Print_Screen(Me)
|
|
|
End Sub
|
|
|
End Class |
