表_查询条件.frm

新世纪ERP设备管理源代码

Private Sub Command1_Click()   '一般过滤
    Dim aDo_Einfo As New Recordset
    Dim i As Integer
    Dim Ssql As String
    Dim D As String
    D = 1
    For i = 1 To Combo_I.Count - 1
        If Combo_I(i).Text <> "" Then
            If D = 1 Then
                If Val(Combo_I(i).Tag) = 1 And Combo_I(i).ListIndex >= 0 Then
                    Ssql = "b." & Label_C(i).Tag & "='" & Combo_ICode(i, Combo_I(i).ListIndex) & "'"
                Else
                    Ssql = Label_C(i).Tag & "='" & Combo_I(i).Text & "'"
                End If
                '--------------
            Else
                If Val(Combo_I(i).Tag) = 1 And Combo_I(i).ListIndex >= 0 Then
                    Ssql = Ssql & " and " & " b." & Label_C(i).Tag & "='" & Combo_ICode(i, Combo_I(i).ListIndex) & "'"
                Else
                    Ssql = Ssql & " and " & "b." & Label_C(i).Tag & "='" & Combo_I(i).Text & "'"
                End If
                '---------
            End If
            D = D + 1
        End If
    Next i
    
    '-----------------
    If Trim(Ssql) = "" Then
        Ssql = "b.DEVSort='" & Combo1.ItemData(Combo1.ListIndex) & "'"
    Else
        Ssql = Ssql & " and b.DEVSort='" & Combo1.ItemData(Combo1.ListIndex) & "'"
    End If
    P_Ssql = Ssql
    Enployeeinfo_Query Ssql
    '-----------------
    Me.Hide

End Sub

Private Sub Command2_Click()
Unload Me
End Sub

Private Sub Command3_Click()
    Dim c As Integer
    For c = 0 To 3
       Combo2(c).ListIndex = 0
       Combo3(c).ListIndex = 0
       Combo4(c).Text = ""
       Combo4(c).Clear
       Combo5(c).ListIndex = 0
    Next c
End Sub

Private Sub Command4_Click() '条件过滤
Dim Footing
Dim Footing1
Dim r As Integer
Dim Ssql As String

Footing = Array("", "=", "<>", "Like", ">", "<", ">=", "<=")
Footing1 = Array("", "AND", "OR")

'-------------------
For r = 0 To 3
If Trim(Combo2(r).Text) = "" Then MsgBox "项目错误！  ", 48, "过滤": Combo2(r).SetFocus: Exit Sub
If Trim(Combo3(r).Text) = "" Then MsgBox "关系错误！  ", 48, "过滤": Combo3(r).SetFocus: Exit Sub
'--------------------
 If Combo2(r).ItemData(Combo2(r).ListIndex) = 2 Then
    If IsDate(Combo4(r)) = False And Trim(Combo4(r)) <> "" Then
       MsgBox "非法日期格式！" & Format(Date, "yyyy-mm-dd"), 16
       Combo4(r).SetFocus
       Exit Sub
    Else
       Combo4(r).Text = Format(Trim(Combo4(r).Text), "yyyy-mm-dd")
    End If
 End If

'------------------------

    If Combo5(r).Text = "" Then
        '-----------------
       If Combo3(r).ListIndex <> 3 Then
            If Item_Code(Combo2(r).ListIndex) = 1 And Combo4(r).ListIndex >= 0 Then
               If Item_Root(Combo2(r).ListIndex) = "1" Then
                   Ssql = Ssql & "b." & FieldName(Combo2(r).ListIndex) & " " & Footing(Combo3(r).ListIndex) & " '" & Combo4_Code(r, Combo4(r).ListIndex) & "'"
                  Else
                   Ssql = Ssql & "a." & FieldName(Combo2(r).ListIndex) & " " & Footing(Combo3(r).ListIndex) & " '" & Combo4_Code(r, Combo4(r).ListIndex) & "'"
               End If
             Else
               If Item_Root(Combo2(r).ListIndex) = "1" Then
                   Ssql = Ssql & "b." & FieldName(Combo2(r).ListIndex) & " " & Footing(Combo3(r).ListIndex) & " '" & Trim(Combo4(r).Text) & "'"
                  Else
                   Ssql = Ssql & "a." & FieldName(Combo2(r).ListIndex) & " " & Footing(Combo3(r).ListIndex) & " '" & Trim(Combo4(r).Text) & "'"
               End If
            End If
            '--------------
          Else
           If Item_Code(Combo2(r).ListIndex) = 1 And Combo4(r).ListIndex >= 0 Then
               If Item_Root(Combo2(r).ListIndex) = "1" Then
                   Ssql = Ssql & "b." & FieldName(Combo2(r).ListIndex) & " " & Footing(Combo3(r).ListIndex) & " '" & Combo4_Code(r, Combo4(r).ListIndex) & "%'"
                  Else
                   Ssql = Ssql & "a." & FieldName(Combo2(r).ListIndex) & " " & Footing(Combo3(r).ListIndex) & " '" & Combo4_Code(r, Combo4(r).ListIndex) & "%'"
               End If
              Else
               If Item_Root(Combo2(r).ListIndex) = "1" Then
                   Ssql = Ssql & "b." & FieldName(Combo2(r).ListIndex) & " " & Footing(Combo3(r).ListIndex) & " '" & Trim(Combo4(r).Text) & "%'"
                  Else
                   Ssql = Ssql & "a." & FieldName(Combo2(r).ListIndex) & " " & Footing(Combo3(r).ListIndex) & " '" & Trim(Combo4(r).Text) & "%'"
               End If
           End If
            '--------------
       End If
       Exit For
       '-------------------
     Else
       '------------------
       If Combo3(r).ListIndex <> 3 Then
          If Item_Code(Combo2(r).ListIndex) = 1 And Combo4(r).ListIndex >= 0 Then
               If Item_Root(Combo2(r).ListIndex) = "1" Then
                   Ssql = Ssql & "b." & FieldName(Combo2(r).ListIndex) & " " & Footing(Combo3(r).ListIndex) & " '" & Combo4_Code(r, Combo4(r).ListIndex) & "'" & " " & Footing1(Combo5(r).ListIndex) & " "
                  Else
                   Ssql = Ssql & "a." & FieldName(Combo2(r).ListIndex) & " " & Footing(Combo3(r).ListIndex) & " '" & Combo4_Code(r, Combo4(r).ListIndex) & "'" & " " & Footing1(Combo5(r).ListIndex) & " "
               End If
             Else
               If Item_Root(Combo2(r).ListIndex) = "1" Then
                   Ssql = Ssql & "b." & FieldName(Combo2(r).ListIndex) & " " & Footing(Combo3(r).ListIndex) & " '" & Trim(Combo4(r).Text) & "'" & " " & Footing1(Combo5(r).ListIndex) & " "
                  Else
                   Ssql = Ssql & "a." & FieldName(Combo2(r).ListIndex) & " " & Footing(Combo3(r).ListIndex) & " '" & Trim(Combo4(r).Text) & "'" & " " & Footing1(Combo5(r).ListIndex) & " "
               End If
          End If
          '--------------
        Else
          If Item_Code(Combo2(r).ListIndex) = 1 And Combo4(r).ListIndex >= 0 Then
              If Item_Root(Combo2(r).ListIndex) = "1" Then
                  Ssql = Ssql & "b." & FieldName(Combo2(r).ListIndex) & " " & Footing(Combo3(r).ListIndex) & " '" & Combo4_Code(r, Combo4(r).ListIndex) & "%'" & " " & Footing1(Combo5(r).ListIndex) & " "
                 Else
                  Ssql = Ssql & "a." & FieldName(Combo2(r).ListIndex) & " " & Footing(Combo3(r).ListIndex) & " '" & Combo4_Code(r, Combo4(r).ListIndex) & "%'" & " " & Footing1(Combo5(r).ListIndex) & " "
              End If
             Else
              If Item_Root(Combo2(r).ListIndex) = "1" Then
                  Ssql = Ssql & "b." & FieldName(Combo2(r).ListIndex) & " " & Footing(Combo3(r).ListIndex) & " '" & Trim(Combo4(r).Text) & "%'" & " " & Footing1(Combo5(r).ListIndex) & " "
                 Else
                  Ssql = Ssql & "a." & FieldName(Combo2(r).ListIndex) & " " & Footing(Combo3(r).ListIndex) & " '" & Trim(Combo4(r).Text) & "%'" & " " & Footing1(Combo5(r).ListIndex) & " "
              End If
          End If
       End If
       '------------------
    End If
    '--------------------
    
Next

Enployeeinfo_Query Ssql
'---------------
Me.Hide
End Sub

Private Sub Command5_Click()
  Unload Me
End Sub

Private Sub Command6_Click()
  Dev_ItemQueryItem.Show 1
End Sub

Private Sub Form_Load()
    Dim aDo_Item As New Recordset
    Dim i As Integer
    List
    
    Set aDo_Item = Cw_DataEnvi.DataConnect.Execute("select * from dev_item ")
    With aDo_Item
        i = 1
        ReDim FieldName(aDo_Item.RecordCount + 1)
        ReDim Item_Code(aDo_Item.RecordCount + 1)
        ReDim ItmeCorrelation(aDo_Item.RecordCount + 1)
        ReDim Item_Root(aDo_Item.RecordCount + 1)
        Do While Not .EOF
           Combo2(0).AddItem !ItemChineseName
           Combo2(0).ItemData(i) = !ItemFieldType
           Combo2(1).AddItem !ItemChineseName
           Combo2(1).ItemData(i) = !ItemFieldType
           Combo2(2).AddItem !ItemChineseName
           Combo2(2).ItemData(i) = !ItemFieldType
           Combo2(3).AddItem !ItemChineseName
           FieldName(i) = !ItemFieldName
           Item_Code(i) = !yncode
           Item_Root(i) = !YNRoot
           ItmeCorrelation(i) = "" & !ItmeCorrelation
           i = i + 1
           .MoveNext
        Loop
        
        '-------------------
        Dim ado_Itemtf As New Recordset
        Dim aDo_Com As New Recordset
         
        Set aDo_Com = Cw_DataEnvi.DataConnect.Execute("select * from DEV_ItemSort")
         i = 0
        Combo1.Clear
        Do While Not aDo_Com.EOF
            Combo1.AddItem Trim(aDo_Com!ISName)
            Combo1.ItemData(i) = aDo_Com!ISId
            i = i + 1
            aDo_Com.MoveNext
        Loop
        Combo1.ListIndex = 0
        aDo_Com.Close
        
        '--------------------
    
    End With
End Sub



'------------------------------
Public Sub List()          '显示项目
    Dim B As Integer, TopInt As Integer
    Dim D As Integer
    Dim add_item As New Recordset
    For B = 1 To Combo_I.Count - 1
       Unload Combo_I(B): Unload Label_C(B)
    Next B
    '------------------------
    
     D = 1: TopInt = 1300
    Set add_item = Cw_DataEnvi.DataConnect.Execute("select * from dev_item where YNquery='1'")
        ReDim Combo_ICode(add_item.RecordCount, 0)
        ReDim Combo4_Code(4, 0)
        Do While Not add_item.EOF
        '---------------------------
    
           '创建下拉列表框
               
            Load Combo_I(D)
            Load Label_C(D)
            Label_C(D).Top = TopInt
            Label_C(D).Caption = add_item!ItemChineseName
            Label_C(D).Left = 1400 - Label_C(D).Width - 100
            
            Label_C(D).Tag = "" & add_item!ItemFieldName
            
            Combo_I(D).Left = 1400
            Combo_I(D).Top = Label_C(D).Top - 50
            If add_item!ItmeFieldLength * 105 > Me.Width - 1500 Then
               Combo_I(D).Width = 3500
               Else
               Combo_I(D).Width = add_item!ItmeFieldLength * 105
            End If
            Combo_I(D).Tag = "" & add_item!yncode
            
            ComBo_AddList Combo_I(D), "" & add_item!ItmeCorrelation, D, False
            
           '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
            Dim aDo_Com As New Recordset
            Dim i As Integer
            Dim Ssql As String
            If Trim(add_item!TableName) <> "" Then
                    If Trim(add_item!TableName) = "CorrelationList" Then
                       Ssql = "select * from DEV_CorrelationSort A,DEV_CorrelationList B where " _
                            & "A.SortCode=b.SortCode and A.SortName='" & Trim(add_item!ItmeCorrelation) & "' order by B.ListCode"
                       Else
                       Ssql = "select * from " & add_item!TableName
                    End If
                
                    Set aDo_Com = Cw_DataEnvi.DataConnect.Execute(Ssql)
                    
                    i = 1
                    
                    Combo_I(D).Clear
                    Combo_I(D).AddItem ""
                    Combo_I(D).ItemData(0) = 0
                    
                    Do While Not aDo_Com.EOF
                        If Trim(add_item!TableName) <> "CorrelationList" Then
                           Combo_I(D).AddItem Trim(aDo_Com(Trim(add_item!CloumnName2)))
                           Combo_I(D).ItemData(i) = aDo_Com(Trim(add_item!CloumnName1))
                        Else
                           Combo_I(D).AddItem Trim(aDo_Com!Listname)
                           Combo_I(D).ItemData(i) = aDo_Com!ListCode
                        End If
                        i = i + 1
                        aDo_Com.MoveNext
                    Loop
                    aDo_Com.Close
                    
            End If
           '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
           
            Combo_I(D).Visible = True
            Label_C(D).Visible = True
            D = D + 1
            TopInt = TopInt + 400
        '--------------------------------
        add_item.MoveNext
        Loop
        add_item.Close

End Sub

Private Sub Form_Unload(Cancel As Integer)
    If Not UnloadCheck.Value = 1 Then
        Me.Hide
    End If
End Sub
Public Sub Enployeeinfo_Query(Ssql_2 As String) '条件过滤
    Dim aDo_Reco As New Recordset
    Dim aDo_Item As New Recordset
    Dim h As Integer: Dim c As Integer
    Dim Str_Field As String
    Dim ssql_1 As String
    ssql_1 = Ssql_2
    If ssql_1 = "" Then
        ssql_1 = Item_Info & " where a.id=b.id"
    Else
        ssql_1 = Item_Info & " where a.id=b.id and " & ssql_1
    End If
    
    Set aDo_Reco = Cw_DataEnvi.DataConnect.Execute(ssql_1)
    Set aDo_Item = Cw_DataEnvi.DataConnect.Execute("select * from dev_Item a,DEV_ItemSortList b where a.ItemCode=b.ItemCode and b.isid=" & Dev_ItemQFilt.Combo1.ItemData(Dev_ItemQFilt.Combo1.ListIndex) & " order by tab")
    With Dev_ItemQInfoForm.vsFlexGrid1
        Dev_ItemQInfoForm.Tag = Dev_ItemQFilt.Combo1.ItemData(Dev_ItemQFilt.Combo1.ListIndex)
        Dev_ItemQInfoForm.vsFlexGrid1.Tag = Dev_ItemQFilt.Combo1.Text
        c = 1: h = 1
        .Rows = aDo_Reco.RecordCount + 1
        
        Do While Not aDo_Item.EOF
            Str_Field = Str_Field & "^" & aDo_Item!ItemChineseName & "|"
            aDo_Item.MoveNext
        Loop
        .Clear
        .FormatString = "<ID  |" & Str_Field
        .Cols = aDo_Item.RecordCount + 1
        aDo_Item.MoveFirst
        
        
        Do While Not aDo_Reco.EOF
            Do While Not aDo_Item.EOF
                If aDo_Item!yncode = 1 Then
                    .TextMatrix(h, c) = Trim("" & aDo_Reco("N_" & Trim(aDo_Item!ItemFieldName)))
                Else
                    .TextMatrix(h, c) = Trim("" & aDo_Reco(Trim(aDo_Item!ItemFieldName)))
                End If
                c = c + 1
                aDo_Item.MoveNext
            Loop
            .TextMatrix(h, 0) = h
            aDo_Item.MoveFirst
            c = 1
            h = h + 1
            aDo_Reco.MoveNext
        Loop
        aDo_Item.MoveFirst

        c = 1
        Do While Not aDo_Item.EOF
            If Val("" & aDo_Item!Width) < 1 Then
                .ColWidth(c) = 800
            Else
                .ColWidth(c) = Val("" & aDo_Item!Width)
            End If
            aDo_Item.MoveNext
            c = c + 1
        Loop
    
           
    End With
    aDo_Reco.Close
    aDo_Item.Close
End Sub


Public Sub ComBo_AddList(Com As ComboBox, Str As String, r As Integer, tf As Boolean)  '添加Combox
    Dim aDo_Com As New Recordset
    Dim i As Integer
    Dim Ssql As String
    Ssql = "select * from DEV_CorrelationSort A,DEV_CorrelationList B where " _
         & "A.SortCode=b.SortCode and A.SortName='" & Trim(Str) & "' order by B.ListCode"
    Set aDo_Com = Cw_DataEnvi.DataConnect.Execute(Ssql)
    '-----------
    If tf = False Then
        If aDo_Com.RecordCount > UBound(Combo_ICode, 2) Then
            ReDim Preserve Combo_ICode(UBound(Combo_ICode, 1), aDo_Com.RecordCount)
        End If
        i = 1
    Else
        If aDo_Com.RecordCount > UBound(Combo4_Code, 2) Then
            ReDim Preserve Combo4_Code(UBound(Combo4_Code, 1), aDo_Com.RecordCount)
        End If
        i = 0
    End If
    Com.Clear
    '----------
    Do While Not aDo_Com.EOF
        Com.AddItem Trim(aDo_Com!Listname)
        If tf = False Then
            Combo_ICode(r, i) = aDo_Com!ListCode
        Else
            Combo4_Code(r, i) = aDo_Com!ListCode
        End If
        i = i + 1
        aDo_Com.MoveNext
    Loop
    aDo_Com.Close

End Sub