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ü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