frmquerycontract.frm

使用vb开发的课程设计

VERSION 5.00
Object = "{5E9E78A0-531B-11CF-91F6-C2863C385E30}#1.0#0"; "Msflxgrd.ocx"
Begin VB.Form frmQueryContract 
   Caption         =   "合同查询"
   ClientHeight    =   4185
   ClientLeft      =   60
   ClientTop       =   345
   ClientWidth     =   7575
   LinkTopic       =   "Form1"
   ScaleHeight     =   4185
   ScaleWidth      =   7575
   StartUpPosition =   3  'Windows Default
   Begin MSFlexGridLib.MSFlexGrid fgContracts 
      Height          =   1815
      Left            =   120
      TabIndex        =   11
      Top             =   2280
      Width           =   7335
      _ExtentX        =   12938
      _ExtentY        =   3201
      _Version        =   393216
   End
   Begin VB.CommandButton cmdCancel 
      Caption         =   "返回(&C)"
      Height          =   375
      Left            =   4080
      TabIndex        =   10
      Top             =   1680
      Width           =   1335
   End
   Begin VB.CommandButton cmdQuery 
      Caption         =   "查询(&Q)"
      Height          =   375
      Left            =   1920
      TabIndex        =   9
      Top             =   1680
      Width           =   1335
   End
   Begin VB.Frame Frame1 
      Caption         =   "查询条件"
      Height          =   1335
      Left            =   120
      TabIndex        =   0
      Top             =   120
      Width           =   7335
      Begin VB.ComboBox cboStatus 
         Height          =   315
         Left            =   5400
         TabIndex        =   8
         Top             =   840
         Width           =   1695
      End
      Begin VB.OptionButton optQueryContract 
         Caption         =   "按付款状态"
         Height          =   255
         Index           =   3
         Left            =   4080
         TabIndex        =   7
         Top             =   840
         Width           =   1215
      End
      Begin VB.ComboBox cboSigner 
         Height          =   315
         Left            =   5400
         TabIndex        =   6
         Top             =   360
         Width           =   1695
      End
      Begin VB.OptionButton optQueryContract 
         Caption         =   "按签约人"
         Height          =   255
         Index           =   2
         Left            =   4080
         TabIndex        =   5
         Top             =   360
         Width           =   1215
      End
      Begin VB.ComboBox cboClient 
         Height          =   315
         Left            =   1560
         TabIndex        =   4
         Top             =   840
         Width           =   1935
      End
      Begin VB.OptionButton optQueryContract 
         Caption         =   "按客户名称"
         Height          =   255
         Index           =   1
         Left            =   240
         TabIndex        =   3
         Top             =   840
         Width           =   1215
      End
      Begin VB.ComboBox cboContractNo 
         Height          =   315
         Left            =   1560
         TabIndex        =   2
         Top             =   360
         Width           =   1935
      End
      Begin VB.OptionButton optQueryContract 
         Caption         =   "按合同编号"
         Height          =   255
         Index           =   0
         Left            =   240
         TabIndex        =   1
         Top             =   360
         Width           =   1215
      End
   End
End
Attribute VB_Name = "frmQueryContract"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit

Public sqlStr As String
Public msgText As String

Private Sub cboClient_Click()
optQueryContract(1).Value = True

End Sub

Private Sub cboContractNo_Click()
optQueryContract(0).Value = True

End Sub

Private Sub cboSigner_Click()
optQueryContract(2).Value = True

End Sub

Private Sub cboStatus_Click()
optQueryContract(3).Value = True

End Sub

Private Sub cmdCancel_Click()
Unload Me

End Sub

Private Sub cmdQuery_Click()
queryContracts
End Sub

Sub queryContracts()
    Dim rs As ADODB.Recordset
    Dim i As Integer
    Dim j As Integer
    
    If optQueryContract(0).Value = True Then
         sqlStr = "select * from contract where" _
                & " contractNo='" & cboContractNo.Text & "'"
    End If
    
    If optQueryContract(1).Value = True Then
         sqlStr = "select * from contract where" _
                & " clientName='" & cboClient.Text & "'"
    End If
    
    If optQueryContract(2).Value = True Then
         sqlStr = "select * from contract where" _
                & " signer='" & cboSigner.Text & "'"
    End If
    
    If optQueryContract(3).Value = True Then
       If cboStatus.Text = "未付款" Then
         sqlStr = "select * from contract where payment=0"
       Else
         If cboStatus.Text = "部分付款" Then
             sqlStr = "select * from contract where" _
             & " payment<amount and payment<>0"
         Else
             sqlStr = "select * from contract where payment=amount"
         End If
       End If
    End If
    
    Set rs = ExecuteSQL(sqlStr, msgText)
    
    If rs.RecordCount = 0 Then
    
        MsgBox "没有查找满足条件的数据！", vbExclamation, "提示"
        fgContracts.Rows = 1
       
    Else
        
        fgContracts.Rows = rs.RecordCount + 1
        
        fgContracts.Cols = 8
        
        '设定行高
        For i = 0 To fgContracts.Rows - 1
           fgContracts.RowHeight(i) = 280
        Next i
        
        '设定列的属性
        fgContracts.Row = 0
        
        For i = 0 To fgContracts.Cols - 1
           fgContracts.Col = i  '指定当前列为第i列
           fgContracts.FixedAlignment(i) = 4  '每列内容居中显示
           Select Case i
               Case 0
                  fgContracts.ColWidth(i) = 1000  '设定列宽
                  fgContracts.Text = "合同编号"
               Case 1
                  fgContracts.ColWidth(i) = 1100  '设定列宽
                  fgContracts.Text = "合同名称"
               Case 2
                  fgContracts.ColWidth(i) = 1500  '设定列宽
                  fgContracts.Text = "合同描述"
               Case 3
                  fgContracts.ColWidth(i) = 1000  '设定列宽
                  fgContracts.Text = "客户名称"
               Case 4
                  fgContracts.ColWidth(i) = 1500  '设定列宽
                  fgContracts.Text = "合同金额"
               Case 5
                  fgContracts.ColWidth(i) = 1000  '设定列宽
                  fgContracts.Text = "签约人"
               Case 6
                  fgContracts.ColWidth(i) = 1000  '设定列宽
                  fgContracts.Text = "生效日期"
               Case 7
                  fgContracts.ColWidth(i) = 1000  '设定列宽
                  fgContracts.Text = "截至日期"
            End Select
               
        Next i
           
           
        'rs.MoveFirst
        
        i = 1
        
        While (Not rs.EOF)
        
           fgContracts.Row = i
           For j = 0 To fgContracts.Cols - 1
           
            fgContracts.Col = j '设置当前为列为第j列
            fgContracts.CellAlignment = 4  '每列内容居中显示
        
            
            Select Case j
               Case 0
                  fgContracts.Text = rs.Fields("contractNo")
               Case 1
                  fgContracts.Text = rs.Fields("contractName")
               Case 2
                  fgContracts.Text = rs.Fields("description")
               Case 3
                  fgContracts.Text = rs.Fields("clientName")
               Case 4
                  fgContracts.Text = rs.Fields("clientName")
               Case 5
                  fgContracts.Text = rs.Fields("amount")
               Case 6
                  fgContracts.Text = rs.Fields("signer")
               Case 7
                  fgContracts.Text = rs.Fields("startDate")
               Case 8
                  fgContracts.Text = rs.Fields("dueDate")
            End Select
          Next j
        
            rs.MoveNext
            i = i + 1
        Wend
    
    End If
    
    rs.Close

End Sub


Sub initClientName()
'在组合列表框中列出所有付款单位名称
Dim rstClient As ADODB.Recordset

    '从收款记录表中读取所有付款单位名称并添加到组合列表框中
    sqlStr = "select DISTINCT clientName from contract"
    Set rstClient = ExecuteSQL(sqlStr, msgText)
    cboClient.Clear
    
    If Not rstClient.EOF Then
        
            Do While Not rstClient.EOF
                cboClient.AddItem Trim(rstClient.Fields(0))
                rstClient.MoveNext
            Loop
        
    Else
        MsgBox "没有找到相关信息，请添加！", vbOKOnly + vbExclamation, "警告"
        
        Exit Sub
    End If
    rstClient.Close
End Sub

Sub initSigner()
'在组合列表框中列出所签约人
Dim rstSigner As ADODB.Recordset

    '从合同信息表中读取所有合同编号并添加到组合列表框中
    sqlStr = "select DISTINCT signer from contract"
    Set rstSigner = ExecuteSQL(sqlStr, msgText)
    cboSigner.Clear
    
    If Not rstSigner.EOF Then
        
            Do While Not rstSigner.EOF
                cboSigner.AddItem Trim(rstSigner.Fields(0))
                rstSigner.MoveNext
            Loop
        
    Else
        MsgBox "没有找到相关信息，请添加！", vbOKOnly + vbExclamation, "警告"
        
        Exit Sub
    End If
    rstSigner.Close
End Sub

Sub initContractNo()
'在组合列表框中列出所有合同编号
Dim rstContractNo As ADODB.Recordset

    '从合同信息表中读取所有合同编号并添加到组合列表框中
    sqlStr = "select DISTINCT contractNo from contract"
    Set rstContractNo = ExecuteSQL(sqlStr, msgText)
    cboContractNo.Clear
    
    If Not rstContractNo.EOF Then
        
            Do While Not rstContractNo.EOF
                cboContractNo.AddItem Trim(rstContractNo.Fields(0))
                rstContractNo.MoveNext
            Loop
        
    Else
        MsgBox "没有找到相关信息，请添加！", vbOKOnly + vbExclamation, "警告"
        
        Exit Sub
    End If
    rstContractNo.Close
End Sub

Private Sub Form_Load()
initContractNo
initClientName
initSigner
cboStatus.Clear
cboStatus.AddItem "未付款"
cboStatus.AddItem "部分付款"
cboStatus.AddItem "全额付款"
End Sub