Hanjeon/Sub/MyControl(OleDb)v2/Spread.Net.v13/MySearchGrp.vb

Imports System.Text

Public Class MySearchGrp

    ''' <summary>
    ''' 이 개체에 커서를 이동합니다.
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub DB_SetFocus()
        '정렬된 아이템중에 1번쨰 아이템에 포커르를 줍니다.
        Dim A As ArrayList = GetSortedItem()
        Dim itm As Control = A(0)
        Dim ItmType As String = itm.GetType.Name.ToString.ToUpper
        Select Case ItmType
            Case "TEXTBOX"
                CType(itm, MyControlOLEDBv2.MyTextBox).Focus()
            Case "CHECKBOX"
                CType(itm, MyControlOLEDBv2.MyCheckBox).Focus()
            Case "COMBOBOX"
                CType(itm, MyControlOLEDBv2.MyCombo).Focus()
        End Select


    End Sub

    ''' <summary>
    ''' 탭순서를 이용하여 정렬된 아이템의 배열(ARRAYLIST)을 반환합니다.
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetSortedItem() As ArrayList
        Dim Iname As String
        Dim CAr As New ArrayList
        Dim CArC As New ArrayList

        For Each itm As Control In Me.Controls
            Iname = itm.GetType.Name.ToString.ToUpper
            If Iname = "TEXTBOX" OrElse Iname = "CHECKBOX" OrElse Iname = "COMBOBOX" Then
                CAr.Add(itm.TabIndex)
            End If
        Next
        CAr.Sort()

        For i As Integer = 0 To CAr.Count - 1
            For Each itm As Control In Me.Controls
                Iname = itm.GetType.Name.ToString.ToUpper
                If itm.TabIndex = CAr.Item(i) Then CArC.Add(itm)
            Next
        Next
        Return CArC
    End Function


    ''' <summary>
    ''' WHERE SQL을 반한합니다.(PROMPT=TRUE 이면 WHERE절이 붙어서 반환됩니다)
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetWhere(Optional ByVal HEADER As Boolean = False, Optional ByVal AddParam As Boolean = True, Optional ByVal ResetParam As Boolean = True) As String
        'where 절은    kcode = >= <= != 이런형태가 들어가서 만들어진다.
        'where 절내의 Db_item 타입이 true 인것들만 구문을 작성한다.
        Dim SQL As New StringBuilder(vbNullString)
        Dim CNT As Integer = 0

        If ResetParam Then
            ' MsgBox("파라미터 리셋")
            'DB.MyParam_Reset()

        End If

        For Each ITM As Object In Me.Controls

            ' MsgBox(ITM.GetType.Name)

            Select Case ITM.GetType.Name.ToUpper
                Case "MYMASKTEXTBOX"
                    Dim A As MyControlOLEDBv2.MyMaskBox = ITM
                    If A.DB_ITEM AndAlso A.Text.Trim <> "" Then
                        '기본값 WHERE_DEFAULT_VALUE 값을 확인합니다. 변경이되었다면 WHERE절을 생성합니다
                        If A.Text <> A.WHERE_DEFAULT_VALUE Then
                            CNT += 1
                            If CNT <> 1 Then SQL.Append(" AND ")
                            If Not AddParam Then    '//문자는 '를붙여준다
                                If A.DB_Type = OleDbType.VarChar Then
                                    SQL.Append(A.DB_ColName & Space(1) & A.DB_WhereType & "'" & A.Value & "'")
                                Else
                                    SQL.Append(A.DB_ColName & Space(1) & A.DB_WhereType & A.Value)
                                End If

                            Else
                                SQL.Append(A.DB_ColName & Space(1) & A.DB_WhereType & " :S" & CNT)
                            End If
                            'If AddParam Then DB.MyParam_Add(" :S" & CNT, A.DB_Type, A.Value)
                        End If
                    End If
                Case "TEXTBOX"

                    Dim A As MyControlOLEDBv2.MyTextBox = ITM
                    If A.DB_ITEM AndAlso A.Text.Trim <> "" Then
                        '기본값 WHERE_DEFAULT_VALUE 값을 확인합니다. 변경이되었다면 WHERE절을 생성합니다
                        If A.Text <> A.WHERE_DEFAULT_VALUE Then
                            CNT += 1
                            If CNT <> 1 Then SQL.Append(" AND ")
                            If Not AddParam Then
                                If A.DB_Type = OleDbType.VarChar Then
                                    SQL.Append(A.DB_ColName & Space(1) & A.DB_WhereType & "'" & A.Value & "'")
                                Else
                                    SQL.Append(A.DB_ColName & Space(1) & A.DB_WhereType & A.Value)
                                End If
                            Else
                                SQL.Append(A.DB_ColName & Space(1) & A.DB_WhereType & " :S" & CNT)
                            End If
                            'If AddParam Then DB.MyParam_Add(" :S" & CNT, A.DB_Type, A.Value)
                        End If
                    End If


                Case "CHECKBOX"

                    Dim A As MyControlOLEDBv2.MyCheckBox = ITM
                    If A.DB_ITEM Then
                        '기본값 WHERE_DEFAULT_VALUE 값을 확인합니다. 변경이되었다면 WHERE절을 생성합니다
                        CNT += 1
                        If CNT <> 1 Then SQL.Append(" AND ")
                        If Not AddParam Then
                            If A.DB_Type = OleDbType.VarChar Then
                                SQL.Append(A.DB_ColName & Space(1) & A.DB_WhereType & "'" & A.Value & "'")
                            Else
                                SQL.Append(A.DB_ColName & Space(1) & A.DB_WhereType & A.Value)
                            End If
                        Else
                            SQL.Append(A.DB_ColName & Space(1) & A.DB_WhereType & " :S" & CNT)
                        End If
                        'If AddParam Then DB.MyParam_Add(" :S" & CNT, A.DB_Type, A.Value)

                    End If

                Case "COMBOBOX"

                    Dim A As MyControlOLEDBv2.MyCombo = ITM
                    If A.DB_ITEM Then
                        '기본값 WHERE_DEFAULT_VALUE 값을 확인합니다. 변경이되었다면 WHERE절을 생성합니다
                        If (A.DropDownStyle <> ComboBoxStyle.DropDownList AndAlso A.Text.Trim <> "") OrElse (A.DropDownStyle = ComboBoxStyle.DropDownList AndAlso Not (A.SelectedIndex = 0 AndAlso A.SelectedValue = -1)) Then
                            CNT += 1
                            If CNT <> 1 Then SQL.Append(" AND ")
                            If Not AddParam Then
                                If A.DB_Type = OleDbType.VarChar Then
                                    SQL.Append(A.DB_ColName & Space(1) & A.DB_WhereType & "'" & A.Value & "'")
                                Else
                                    SQL.Append(A.DB_ColName & Space(1) & A.DB_WhereType & A.Value)
                                End If
                            Else
                                SQL.Append(A.DB_ColName & Space(1) & A.DB_WhereType & " :S" & CNT)
                            End If
                            ' If AddParam Then DB.MyParam_Add(" :S" & CNT, A.DB_Type, A.Value)
                        End If
                    End If
            End Select
        Next



        If SQL.ToString.Trim = "" Then    '//만들어진게 아예없을경우이다.
            Return vbNullString
        Else
            Return IIf(HEADER, " WHERE " & SQL.ToString, SQL.ToString)
        End If

    End Function

    ''' <summary>
    ''' 검색블럭내의 모든 콤보박스의 목록을 갱신합니다.
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub UpdateComboBox()
        For Each Itm As Control In Me.Controls
            Select Case Itm.GetType.Name.ToString.ToUpper
                Case "COMBOBOX"
                    CType(Itm, MyControlOLEDBv2.MyCombo).UpdateItem()
            End Select
        Next
    End Sub

End Class