fmcheck.frm

<Visual Basic 数据库开发实例精粹（第二版）>一书首先介绍了Visual Basic(简称VB)开发的技巧和重点技术

Private Sub CombRType_KeyPress(KeyAscii As Integer)
  KeyAscii = 0
End Sub

Private Sub ComdCancel_Click()
  'Me.TextEndTime.Text = ""
  Unload Me
End Sub

Private Sub ComdOK_Click()
    
  '在开放单表中删除此记录
  Dim DelORInfo As New ADODB.Recordset
  Dim Str As String
  Str = "select * from RoomConsumed where RoomID='" & Trim(Me.CombRID.Text) & "'"
  DelORInfo.Open Str, g_DBConn, adOpenStatic, adLockOptimistic
  DelORInfo.Delete
  DelORInfo.Close

  '把结帐单写入结账单表
  Dim SqlStr As String
  Dim sOPTime As String
  Dim sCLTime As String
  sOPTime = Left(Now, 9) & " " & Right(Trim(Me.TextORTime.Text), 8)
  sCLTime = Left(Now, 9) & " " & Right(Trim(Me.TextEndTime.Text), 8)

  SqlStr = "insert into CheckInfo(CheckID,RoomID,RoomType,RoomPrice,ClientName,Agio,OpenRoomTime,EndTime,Prepay,PayMode,RoomMoney,OpenDate,MemberID)" & "values ('" & Trim(Me.TextCID.Text) & "','" & Trim(Me.CombRID.Text) & "','" & Trim(Me.CombRType.Text) & "'," & Trim(Me.CombRPrice.Text) & ",'" & Trim(Me.TextClientName.Text) & "'" & "," & Trim(Me.CombAgio.Text) & ",'" & sOPTime & "','" & sCLTime & "'," & Trim(Me.TextPrepay.Text) & ",'" & Trim(Me.CombPayMode.Text) & "'" & "," & Trim(Me.LabelMoney.Caption) & ",'" & Trim(Me.LabelDate.Caption) & "','" & Trim(Me.LabelMemID.Caption) & "');"
  g_DBConn.Execute SqlStr
    
  '改变房间信息表中的房间状态
  Dim sRS As String
  Dim sRoomID As String
  sRoomID = Trim(Me.CombRID.Text)
  sRS = "update RoomInfo set RoomState='打扫' where RoomID='" & sRoomID & "'"
  g_DBConn.Execute sRS
    
  '更新会员信息表
  Dim AddMemberInfo As New ADODB.Recordset
  Dim MStr As String
  Dim iConsumedTime As Integer
  Dim fConsumedIntegral As Double
  Dim iSignCheck As Integer

  If Me.LabelMemID <> "000" Then
    MStr = "select SignCheck,ConsumedTime,ConsumedIntegral from MemberInfo where MemberID='" & Trim(Me.LabelMemID.Caption) & "'"
    AddMemberInfo.Open MStr, g_DBConn, adOpenStatic, adLockOptimistic
        
    iConsumedTime = AddMemberInfo.Fields("ConsumedTime").Value
    fConsumedIntegral = AddMemberInfo.Fields("ConsumedIntegral").Value
        
    AddMemberInfo.Fields("ConsumedTime").Value = iConsumedTime + 1
    AddMemberInfo.Fields("ConsumedIntegral").Value = Trim(Me.LabelMoney.Caption) + fConsumedIntegral
    AddMemberInfo.Update
        
    '更新会员信息表中的签单额度
    fConsumedIntegral = AddMemberInfo.Fields("ConsumedIntegral").Value
    iSignCheck = AddMemberInfo.Fields("SignCheck").Value

    If fConsumedIntegral > 5000 Then
      AddMemberInfo.Fields("SignCheck").Value = 500
    ElseIf fConsumedIntegral > 10000 Then
      AddMemberInfo.Fields("SignCheck").Value = 1000
    ElseIf fConsumedIntegral > 50000 Then
      AddMemberInfo.Fields("SignCheck").Value = 5000
    End If

    AddMemberInfo.Update
    AddMemberInfo.Close
        
  End If

  Unload Me
End Sub

Private Sub ComdOKEmpty_Click()
    
  '在开放单表中删除此记录
  Dim DelORInfo As New ADODB.Recordset
  Dim Str As String
  Str = "select * from RoomConsumed where RoomID='" & Trim(Me.CombRID.Text) & "'"
  DelORInfo.Open Str, g_DBConn, adOpenStatic, adLockOptimistic
  DelORInfo.Delete
  DelORInfo.Close

  '把结帐单写入结帐单表
  Dim SqlStr As String
  Dim sOPTime As String
  Dim sCLTime As String
  sOPTime = Left(Now, 9) & " " & Right(Trim(Me.TextORTime.Text), 8)
  sCLTime = Left(Now, 9) & " " & Right(Trim(Me.TextEndTime.Text), 8)

  SqlStr = "insert into CheckInfo(CheckID,RoomID,RoomType,RoomPrice," & "ClientName,Agio,OpenRoomTime,EndTime,Prepay,PayMode," & "RoomMoney,OpenDate,MemberID) " & "values ('" & Trim(Me.TextCID.Text) & "'" & ",'" & Trim(Me.CombRID.Text) & "'" & ",'" & Trim(Me.CombRType.Text) & "'" & "," & Trim(Me.CombRPrice.Text) & "" & ",'" & Trim(Me.TextClientName.Text) & "'" & "," & Trim(Me.CombAgio.Text) & "" & ",'" & sOPTime & "'" & ",'" & sCLTime & "'" & "," & Trim(Me.TextPrepay.Text) & "" & ",'" & Trim(Me.CombPayMode.Text) & "'" & "," & Trim(Me.LabelMoney.Caption) & "" & ",'" & Trim(Me.LabelDate.Caption) & "'" & ",'" & Trim(Me.LabelMemID.Caption) & "');"
  g_DBConn.Execute SqlStr
    
  '改变房间信息表中的房间状态
  Dim sRS As String
  Dim sRoomID As String
  sRoomID = Trim(Me.CombRID.Text)
  sRS = "update RoomInfo set RoomState='空房' where RoomID='" & sRoomID & "'"
  g_DBConn.Execute sRS
    
  '更新会员信息表
  Dim AddMemberInfo As New ADODB.Recordset
  Dim MStr As String
  Dim iConsumedTime As Integer
  Dim fConsumedIntegral As Double
  Dim iSignCheck As Integer

  If Me.LabelMemID <> "000" Then
    MStr = "select SignCheck,ConsumedTime,ConsumedIntegral from " & "MemberInfo where MemberID='" & Trim(Me.LabelMemID.Caption) & "'"
    AddMemberInfo.Open MStr, g_DBConn, adOpenStatic, adLockOptimistic
        
    iConsumedTime = AddMemberInfo.Fields("ConsumedTime").Value
    fConsumedIntegral = AddMemberInfo.Fields("ConsumedIntegral").Value
        
    AddMemberInfo.Fields("ConsumedTime").Value = iConsumedTime + 1
    AddMemberInfo.Fields("ConsumedIntegral").Value = Trim(Me.LabelMoney.Caption) + fConsumedIntegral
    AddMemberInfo.Update
        
    '更新会员信息表中的签单额度
    fConsumedIntegral = AddMemberInfo.Fields("ConsumedIntegral").Value
    iSignCheck = AddMemberInfo.Fields("SignCheck").Value

    If fConsumedIntegral > 5000 Then
      AddMemberInfo.Fields("SignCheck").Value = 500
    ElseIf fConsumedIntegral > 10000 Then
      AddMemberInfo.Fields("SignCheck").Value = 1000
    ElseIf fConsumedIntegral > 50000 Then
      AddMemberInfo.Fields("SignCheck").Value = 5000
    End If

    AddMemberInfo.Update
    AddMemberInfo.Close
        
  End If

  Unload Me
End Sub

Private Sub ComOKRepair_Click()

  '在开放单表中删除此记录
  Dim DelORInfo As New ADODB.Recordset
  Dim Str As String
  Str = "select * from RoomConsumed where RoomID='" & Trim(Me.CombRID.Text) & "'"
  DelORInfo.Open Str, g_DBConn, adOpenStatic, adLockOptimistic
  DelORInfo.Delete
  DelORInfo.Close

  '把结帐单写入开放历史表
  Dim SqlStr As String
  Dim sOPTime As String
  Dim sCLTime As String
  sOPTime = Left(Now, 10) & " " & Right(Trim(Me.TextORTime.Text), 8)
  sCLTime = Left(Now, 10) & " " & Right(Trim(Me.TextEndTime.Text), 8)

  SqlStr = "insert into CheckInfo(CheckID,RoomID,RoomType,RoomPrice,ClientName,Agio,OpenRoomTime,EndTime,Prepay,PayMode,RoomMoney,OpenDate,MemberID)" & "values ('" & Trim(Me.TextCID.Text) & "','" & Trim(Me.CombRID.Text) & "','" & Trim(Me.CombRType.Text) & "'," & Trim(Me.CombRPrice.Text) & ",'" & Trim(Me.TextClientName.Text) & "'" & "," & Trim(Me.CombAgio.Text) & ",'" & sOPTime & "','" & sCLTime & "'," & Trim(Me.TextPrepay.Text) & ",'" & Trim(Me.CombPayMode.Text) & "'" & "," & Trim(Me.LabelMoney.Caption) & ",'" & Trim(Me.LabelDate.Caption) & "','" & Trim(Me.LabelMemID.Caption) & "');"
  g_DBConn.Execute SqlStr
    
  '改变房间信息表中的房间状态
  Dim sRS As String
  Dim sRoomID As String
  sRoomID = Trim(Me.CombRID.Text)
  sRS = "update RoomInfo set RoomState='空房' where RoomID='" & sRoomID & "'"
  g_DBConn.Execute sRS
    
  '更新会员信息表
  Dim AddMemberInfo As New ADODB.Recordset
  Dim MStr As String
  Dim iConsumedTime As Integer
  Dim fConsumedIntegral As Double
  Dim iSignCheck As Integer

  If Me.LabelMemID <> "000" Then
    MStr = "select SignCheck,ConsumedTime,ConsumedIntegral from MemberInfo where MemberID='" & Trim(Me.LabelMemID.Caption) & "'"
    AddMemberInfo.Open MStr, g_DBConn, adOpenStatic, adLockOptimistic
        
    iConsumedTime = AddMemberInfo.Fields("ConsumedTime").Value
    fConsumedIntegral = AddMemberInfo.Fields("ConsumedIntegral").Value
        
    AddMemberInfo.Fields("ConsumedTime").Value = iConsumedTime + 1
    AddMemberInfo.Fields("ConsumedIntegral").Value = Trim(Me.LabelMoney.Caption) + fConsumedIntegral
    AddMemberInfo.Update
        
    '更新会员信息表中的签单额度
    fConsumedIntegral = AddMemberInfo.Fields("ConsumedIntegral").Value
    iSignCheck = AddMemberInfo.Fields("SignCheck").Value

    If fConsumedIntegral > 5000 Then
      AddMemberInfo.Fields("SignCheck").Value = 500
    ElseIf fConsumedIntegral > 10000 Then
      AddMemberInfo.Fields("SignCheck").Value = 1000
    ElseIf fConsumedIntegral > 50000 Then
      AddMemberInfo.Fields("SignCheck").Value = 5000
    End If

    AddMemberInfo.Update
    AddMemberInfo.Close
        
  End If

  Unload Me
  'FMRepair.Combo1.Text = Me.CombRID.Text
  FMRepair.Show
End Sub

Private Sub Form_Load()
  Dim DBStr As String
  Dim AutoCheckID As New ADODB.Recordset
  Dim i As Integer
  Dim sCID As String
  Dim ReadOpenRoom As New ADODB.Recordset
  Dim Str As String

  Me.Top = (Screen.Height - Me.Height) / 2    '垂直方向居中
  Me.Left = (Screen.Width - Me.Height) / 2    '水平方向居中
    
  '开房日期
  Me.LabelDate.Caption = Date
    
  '自动生成结帐单编号
  DBStr = "select CheckID from CheckInfo"
  AutoCheckID.CursorType = adOpenStatic
  AutoCheckID.CursorLocation = adUseClient
  AutoCheckID.Open DBStr, g_DBConn, adOpenStatic, adLockOptimistic
    
  If AutoCheckID.EOF Then
    sCID = "0001"
    Me.TextCID.Text = sCID
  Else
    AutoCheckID.MoveLast
    i = AutoCheckID.Fields("CheckID").Value + 1

    If i < 10 Then
      Me.TextCID.Text = "000" & i
    ElseIf i >= 10 & i < 100 Then
      Me.TextCID.Text = "00" & i
    End If
        
  End If

  AutoCheckID.Close

  Dim iRoomID As Integer
  Dim strRoomID As String
  Dim iRow, iCol As Integer

  iRow = FMMain.MSFlexGrid1.Row
  iCol = FMMain.MSFlexGrid1.Col
  iRoomID = 10 * iRow + iCol + 1

  If iRoomID < 10 Then
    strRoomID = "0" & iRoomID
  ElseIf iRoomID < 100 And iRoomID >= 10 Then
    strRoomID = iRoomID
  End If

  FMCheck.CombRID.Text = strRoomID
    
  If Me.CombRID.Text = "" Then
    MsgBox "请选择房间！"
    Exit Sub
  Else
    
    Str = "select RoomType,RoomPrice,ClientName,OpenTime,Prepay,PayMode," & "Agio,MemberID from RoomConsumed where " & "RoomID='" & Trim(Me.CombRID.Text) & "'"
    ReadOpenRoom.Open Str, g_DBConn, adOpenStatic, adLockOptimistic
        
    If ReadOpenRoom.EOF Then
    Else
      Me.CombRType.Text = ReadOpenRoom.Fields("RoomType").Value
      Me.CombRPrice.Text = ReadOpenRoom.Fields("RoomPrice").Value
      Me.TextClientName.Text = ReadOpenRoom.Fields("ClientName").Value
      Me.TextORTime.Text = Right(ReadOpenRoom.Fields("OpenTime").Value, 8)
      Me.TextPrepay.Text = ReadOpenRoom.Fields("Prepay").Value
      Me.CombPayMode.Text = ReadOpenRoom.Fields("PayMode").Value
      Me.CombAgio.Text = ReadOpenRoom.Fields("Agio").Value
      Me.LabelMemID.Caption = ReadOpenRoom.Fields("MemberID").Value
      ReadOpenRoom.Close
    End If

    'Me.CombRType.Enabled = False
    'Me.CombRPrice.Enabled = False
    'Me.TextORTime.Enabled = False
        
  End If
    
  If Me.LabelMemID.Caption = "000" Then
    Me.LabelMemID.Visible = False
    Me.Label9.Visible = False
  End If
    
  '结束时间
  Me.TextEndTime.Text = Time
    
  '调用存储过程
    
  Dim strS As String
  Dim adoconn As New ADODB.Connection 'Connection 对象代表了打开与数据源的连接。
  Dim adocomm As New ADODB.Command 'Command 对象定义了将对数据源执行的指定命令。
  'Dim ReturnValue As Integer '调用存储过程的返回值
  adoconn.ConnectionString = g_ConnStr
  adoconn.Open
  Set adocomm.ActiveConnection = adoconn '指示指定的 Command对象当前所属的 Connection对象。
  adocomm.CommandText = "up_check" '设置Command对象源。
  adocomm.CommandType = adCmdStoredProc '通知提供者CommandText属性有什么，它可能包括Command对象的源类型。设置这个属性优化了该命令的执行。

  adocomm.Parameters.Item("@cOpenTime").Value = Trim(Me.TextORTime.Text)
  adocomm.Parameters.Item("@cCloseTime").Value = Time
  adocomm.Parameters.Item("@cRoomID").Value = Trim(Me.CombRID.Text)
  adocomm.Parameters.Item("@cPrice").Value = Trim(Me.CombRPrice.Text)
  adocomm.Parameters.Item("@cAgio").Value = Trim(Me.CombAgio.Text)
  adocomm.Parameters.Item("@cPrepay").Value = Trim(Me.TextPrepay.Text)

  'adocomm.Parameters(1) = "1"
  'adocomm.Parameters(2) = "@cMoney" 'OutputParameters可以为任意的字符串或数字
  adocomm.Execute

  'ReturnValue = adocomm.Parameters(0) '存储过程的返回值，返回0则成功执行。
  If adocomm.Parameters(0) = 0 Then

    strS = CInt(adocomm.Parameters.Item("@cMoney").Value) '把存储过程的输出参数的值赋给变量strS
    Me.LabelMoney.Caption = strS
  Else
    MsgBox "调用存储过程不成功！"
  End If
    
  Set adocomm = Nothing
    
End Sub

Private Sub TextCID_KeyPress(KeyAscii As Integer)
  KeyAscii = 0
End Sub

Private Sub TextClientName_KeyPress(KeyAscii As Integer)
  KeyAscii = 0
End Sub

Private Sub TextEndTime_KeyPress(KeyAscii As Integer)
  KeyAscii = 0
End Sub

Private Sub TextORTime_KeyPress(KeyAscii As Integer)
  KeyAscii = 0
End Sub

Private Sub TextPrepay_KeyPress(KeyAscii As Integer)
  KeyAscii = 0
End Sub