frmquerycac.frm

数据库课程设计

VERSION 5.00
Object = "{5E9E78A0-531B-11CF-91F6-C2863C385E30}#1.0#0"; "Msflxgrd.ocx"
Begin VB.Form frmQueryCac 
   Caption         =   "查询"
   ClientHeight    =   4320
   ClientLeft      =   60
   ClientTop       =   345
   ClientWidth     =   6855
   LinkTopic       =   "Form1"
   ScaleHeight     =   4320
   ScaleWidth      =   6855
   StartUpPosition =   3  'Windows Default
   Begin MSFlexGridLib.MSFlexGrid fgPaidInfo 
      Height          =   2055
      Left            =   120
      TabIndex        =   7
      Top             =   2160
      Width           =   6615
      _ExtentX        =   11668
      _ExtentY        =   3625
      _Version        =   393216
   End
   Begin VB.Frame Frame1 
      Caption         =   "查询方式"
      Height          =   1695
      Left            =   120
      TabIndex        =   0
      Top             =   240
      Width           =   6615
      Begin VB.ComboBox cboDateOut 
         Height          =   315
         Left            =   1920
         TabIndex        =   9
         Text            =   "Combo1"
         Top             =   720
         Width           =   1815
      End
      Begin VB.OptionButton optType 
         Caption         =   "按结帐日期查询"
         Height          =   375
         Index           =   1
         Left            =   360
         TabIndex        =   8
         Top             =   720
         Width           =   1575
      End
      Begin VB.CommandButton cmdCancel 
         Caption         =   "取消(&C)"
         Height          =   375
         Left            =   4320
         TabIndex        =   6
         Top             =   960
         Width           =   1575
      End
      Begin VB.CommandButton cmdQuery 
         Caption         =   "查询(&Q)"
         Height          =   375
         Left            =   4320
         TabIndex        =   5
         Top             =   360
         Width           =   1575
      End
      Begin VB.ComboBox cboRoom 
         Height          =   315
         Left            =   1920
         TabIndex        =   4
         Text            =   "Combo1"
         Top             =   1200
         Width           =   1815
      End
      Begin VB.ComboBox cboDateIn 
         Height          =   315
         Left            =   1920
         TabIndex        =   3
         Text            =   "Combo1"
         Top             =   240
         Width           =   1815
      End
      Begin VB.OptionButton optType 
         Caption         =   "按房间号查询"
         Height          =   375
         Index           =   2
         Left            =   360
         TabIndex        =   2
         Top             =   1200
         Width           =   1575
      End
      Begin VB.OptionButton optType 
         Caption         =   "按入住日期查询"
         Height          =   375
         Index           =   0
         Left            =   360
         TabIndex        =   1
         Top             =   240
         Width           =   1575
      End
   End
End
Attribute VB_Name = "frmQueryCac"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Public msgText As String
Public sqlStr As String

Private Sub cmdCancel_Click()
Unload Me

End Sub

Private Sub cmdQuery_Click()
    Dim rs As ADODB.Recordset
    Dim i As Integer
    Dim j As Integer
    If optType(0).Value Then
       sqlStr = "select * from checkIn where hasPaid='是' AND date_in=#" & Trim(cboDateIn.Text) & "# "
    End If
    
    If optType(1).Value Then
        sqlStr = "select * from checkIn where hasPaid='是' AND date_out=#" & Trim(cboDateOut.Text) & "#"
    End If
    
    If optType(2).Value Then
        sqlStr = "select * from checkIn where hasPaid='是' AND roomno='" & Trim(cboRoom.Text) & "'"
    End If
    
    Set rs = ExecuteSQL(sqlStr, msgText)
    
    If rs.RecordCount = 0 Then

        MsgBox "没有查找满足条件的数据！", vbExclamation, "提示"
       
    Else
        fgPaidInfo.Rows = rs.RecordCount + 1
        
        fgPaidInfo.Cols = 8
        
        'Print fgPaidInfo.Rows
        
        '设定行高
        For i = 0 To fgPaidInfo.Rows - 1
           fgPaidInfo.RowHeight(i) = 280
        Next i
        
        '设定列的属性
        fgPaidInfo.Row = 0
        
        For i = 0 To fgPaidInfo.Cols - 1
           fgPaidInfo.Col = i  '指定当前列为第i列
           fgPaidInfo.FixedAlignment(i) = 4  '每列内容居中显示
           Select Case i
               Case 0
                  fgPaidInfo.ColWidth(i) = 620  '设定列宽
                  fgPaidInfo.Text = "序号"
               Case 1
                  fgPaidInfo.ColWidth(i) = 700  '设定列宽
                  fgPaidInfo.Text = "登记号"
               Case 2
                  fgPaidInfo.ColWidth(i) = 900  '设定列宽
                  fgPaidInfo.Text = "顾客姓名"
               Case 3
                  fgPaidInfo.ColWidth(i) = 1500  '设定列宽
                  fgPaidInfo.Text = "身份证号"
               Case 4
                  fgPaidInfo.ColWidth(i) = 700  '设定列宽
                  fgPaidInfo.Text = "房间号"
               Case 5
                  fgPaidInfo.ColWidth(i) = 1000  '设定列宽
                  fgPaidInfo.Text = "入住时间"
               Case 6
                  fgPaidInfo.ColWidth(i) = 700  '设定列宽
                  fgPaidInfo.Text = "折扣"
               Case 7
                  fgPaidInfo.ColWidth(i) = 1000  '设定列宽
                  fgPaidInfo.Text = "备注"

            End Select
               
        Next i
           
           
        'rs.MoveFirst
        
        i = 1
        
        While (Not rs.EOF)
        
           fgPaidInfo.Row = i
           For j = 0 To fgPaidInfo.Cols - 1
           
            fgPaidInfo.Col = j '设置当前为列为第j列
            fgPaidInfo.CellAlignment = 4  '每列内容居中显示
        
            
            Select Case j
                Case 0
                   fgPaidInfo.Text = "" & i
                Case 1
                   fgPaidInfo.Text = rs.Fields("bookno")
                Case 2
                   fgPaidInfo.Text = rs.Fields("customerName")
                Case 3
                   fgPaidInfo.Text = rs.Fields("customerID")
                Case 4
                   fgPaidInfo.Text = rs.Fields("roomNO")
                Case 5
                   fgPaidInfo.Text = rs.Fields("date_in")
                Case 6
                   fgPaidInfo.Text = rs.Fields("discount")
                Case 7
                   fgPaidInfo.Text = rs.Fields("memo")
                   
            End Select
          Next j
        
            rs.MoveNext
            i = i + 1
        Wend
        fgPaidInfo.Visible = True
    
    End If
    
    rs.Close


End Sub

Private Sub Form_Load()

'窗体居中显示
Me.Top = (Screen.Height - Me.Height) \ 2
Me.Left = (Screen.Width - Me.Width) \ 2

initDate
initRoom
End Sub

Sub initDate()
    Dim rstDate As ADODB.Recordset
    
    cboDateIn.Clear
    cboDateOut.Clear
    
    '初始化入住日期
    sqlStr = "select DISTINCT date_in from checkIn where hasPaid='是'"
    Set rstDate = ExecuteSQL(sqlStr, msgText)
    
    If Not rstDate.EOF Then
        
            Do While Not rstDate.EOF
                cboDateIn.AddItem Trim(rstDate.Fields(0))
                rstDate.MoveNext
            Loop
            cboDateIn.ListIndex = 0
        
    Else
        MsgBox "请添加客房类型！", vbOKOnly + vbExclamation, "警告"
        Exit Sub
    End If
    rstDate.Close
    
    '初始化结帐日期
    sqlStr = "select DISTINCT date_out from checkIn where hasPaid='是'"
    Set rstDate = ExecuteSQL(sqlStr, msgText)
    
    If Not rstDate.EOF Then
        
            Do While Not rstDate.EOF
                cboDateOut.AddItem Trim(rstDate.Fields("date_out"))
                rstDate.MoveNext
            Loop
            cboDateOut.ListIndex = 0
        
    Else
        MsgBox "请添加客房类型！", vbOKOnly + vbExclamation, "警告"
        Exit Sub
    End If
    rstDate.Close

    

End Sub

Sub initRoom()
    Dim rstRoom As ADODB.Recordset
    
    cboRoom.Clear
    
    '初始化入住日期
    sqlStr = "select DISTINCT roomno from checkIn where hasPaid='是'"
    Set rstRoom = ExecuteSQL(sqlStr, msgText)
    
    If Not rstRoom.EOF Then
        
            Do While Not rstRoom.EOF
                cboRoom.AddItem Trim(rstRoom.Fields(0))
                rstRoom.MoveNext
            Loop
            cboRoom.ListIndex = 0
        
    Else
        MsgBox "请添加客房类型！", vbOKOnly + vbExclamation, "警告"
        Exit Sub
    End If
    rstRoom.Close

End Sub