main_qtgl_ztfwgl.frm

一个比较完整的数据库编程技巧”洗浴管理系统“

      ForeColor       =   &H0000C000&
      Height          =   225
      Left            =   4080
      TabIndex        =   9
      Top             =   6300
      Width           =   1935
   End
   Begin VB.Label Labsy 
      BackStyle       =   0  'Transparent
      ForeColor       =   &H00FF0000&
      Height          =   225
      Left            =   1095
      TabIndex        =   8
      Top             =   6300
      Width           =   1935
   End
   Begin VB.Label Label2 
      BackStyle       =   0  'Transparent
      Caption         =   "使用箱号：                       空闲箱号：                       损坏箱号："
      Height          =   225
      Left            =   195
      TabIndex        =   7
      Top             =   6315
      Width           =   7035
   End
End
Attribute VB_Name = "main_qtgl_ztfwgl"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Dim i As Integer     '定义一个整型变量
Dim rs1 As New ADODB.Recordset '定义数据集对象
Dim rs2 As New ADODB.Recordset
Dim rs3 As New ADODB.Recordset
Dim rs4 As New ADODB.Recordset
Dim txtSQL, itmX, list As String     '定义字符串变量
Public Sub View_List()       '定义显示箱号状态及信息的函数
 Adodc1.RecordSource = "select * from 箱号信息表 where (所在大厅 ='" + Trim(Combo1.Text) + "'and 箱号 like + '" + Trim(Text1.Text) + "'+'%') order by 记录号"
 Adodc1.Refresh
 If Adodc1.Recordset.RecordCount > 0 Then
   Adodc1.Recordset.MoveFirst
   Do While Adodc1.Recordset.EOF = False
    If Adodc1.Recordset.Fields("状态") = "空闲" Then itmX = ListView1.ListItems.Add(, , Adodc1.Recordset.Fields("箱号"), 1)
    If Adodc1.Recordset.Fields("状态") = "使用" Then itmX = ListView1.ListItems.Add(, , Adodc1.Recordset.Fields("箱号"), 2)
    If Adodc1.Recordset.Fields("状态") = "损坏" Then itmX = ListView1.ListItems.Add(, , Adodc1.Recordset.Fields("箱号"), 3)
    Adodc1.Recordset.MoveNext
   Loop
  '分别统计空闲、使用、损坏的箱号信息
  txtSQL = "select 状态 as 状态1,count(*)as 数量1 from 箱号信息表 where 状态='空闲'and 所在大厅 ='" + Trim(Combo1.Text) + "'and 箱号 like + '" + Trim(Text1.Text) + "'+'%'group by 状态 "
  Set rs1 = ESQL(txtSQL)
  If rs1.RecordCount > 0 Then Labkx.Caption = rs1.Fields(1) Else Labkx.Caption = "0"
  txtSQL = "select 状态 as 状态1,count(*)as 数量1 from 箱号信息表 where 状态='使用'and 所在大厅 ='" + Trim(Combo1.Text) + "'and 箱号 like + '" + Trim(Text1.Text) + "'+'%'group by 状态 "
  Set rs1 = ESQL(txtSQL)
  If rs1.RecordCount > 0 Then Labsy.Caption = rs1.Fields(1) Else Labsy.Caption = "0"
  txtSQL = "select 状态 as 状态1,count(*)as 数量1 from 箱号信息表 where 状态='损坏'and 所在大厅 ='" + Trim(Combo1.Text) + "'and 箱号 like + '" + Trim(Text1.Text) + "'+'%'group by 状态 "
  Set rs1 = ESQL(txtSQL)
  If rs1.RecordCount > 0 Then Labsh.Caption = rs1.Fields(1) Else Labsh.Caption = "0"
 Else
  MsgBox ("无此箱号信息！")
  Labkx.Caption = "0"
  Labsy.Caption = "0"
  Labsh.Caption = "0"
 End If
End Sub

Private Sub Form_Load()
'添加大厅列表
 Combo1.AddItem ("中央大厅")
 Combo1.AddItem ("男大厅")
 Combo1.AddItem ("女大厅")
 Combo1.ListIndex = 0
 ListView1.ListItems.Clear
 Call View_List     '调用函数
 '设置窗体标题栏的内容
 Me.Caption = Me.Caption & "     操作员： " & frm_main.St1.Panels(3).Text
End Sub
Private Sub Form_Unload(Cancel As Integer)
 frm_main.Enabled = True
End Sub



Private Sub Text1_KeyPress(KeyAscii As Integer)
  If KeyAscii = vbKeyReturn Then CmdFind.SetFocus
End Sub

'///////////////////////////////////////////////////////////////////////////////////////////
Private Sub Toolbar1_ButtonClick(ByVal Button As MSComctlLib.Button)
 On Error Resume Next
 If ListView1.SelectedItem.Text = "" Then
    MsgBox "请选择箱号！"
    Exit Sub
 End If
 list = ListView1.SelectedItem.Text     '赋值给list
 '按箱号和所在大厅查询箱号信息
 Adodc1.RecordSource = "select * from 箱号信息表 where 箱号='" + Trim(list) + "'and 所在大厅 ='" + Trim(Combo1.Text) + "'"
 Adodc1.Refresh
 Select Case Button.key
  Case Is = "lp"     '领牌
   If Adodc1.Recordset.RecordCount > 0 And Adodc1.Recordset.Fields("状态") = "空闲" Then
    Adodc1.Recordset.Fields("状态") = "使用"
    Adodc1.Recordset.Update
    ListView1.ListItems.Clear
    Call View_List     '调用函数
   ElseIf Adodc1.Recordset.Fields("状态") = "使用" Then MsgBox "您选的箱号正在使用，请重新选择！"
   ElseIf Adodc1.Recordset.Fields("状态") = "损坏" Then MsgBox "您选的箱号已损坏，请重新选择！"
   End If
  Case Is = "fw"     '服务
   If Adodc1.Recordset.RecordCount > 0 And Adodc1.Recordset.Fields("状态") = "使用" Then
    Load main_qtgl_ztfwgl_fw
    main_qtgl_ztfwgl_fw.Show
    main_qtgl_ztfwgl_fw.Labxh.Caption = list
    main_qtgl_ztfwgl_fw.Labdt.Caption = Combo1.Text
    main_qtgl_ztfwgl.Enabled = False
   ElseIf Adodc1.Recordset.Fields("状态") = "空闲" Then MsgBox "请先领牌！"
   ElseIf Adodc1.Recordset.Fields("状态") = "损坏" Then MsgBox "您选的箱号已损坏，请重新选择！"
   End If
  Case Is = "ck"     '查看
   If Adodc1.Recordset.RecordCount > 0 And Adodc1.Recordset.Fields("状态") = "使用" Then
     '查询客人是否消费
     main_qtgl_ztfwgl_ck.Adodc1.RecordSource = "select * from 临时消费信息表 where 箱号='" + Trim(list) + "'and 所在大厅 ='" + Trim(Combo1.Text) + "'order by 记录号"
     main_qtgl_ztfwgl_ck.Adodc1.Refresh
     If main_qtgl_ztfwgl_ck.Adodc1.Recordset.RecordCount > 0 Then
     Load main_qtgl_ztfwgl_ck
     main_qtgl_ztfwgl_ck.Show
     '查看消费信息，统计点单、赠单金额
     main_qtgl_ztfwgl_ck.Adodc2.RecordSource = "select 消费状态 as 状态1,sum(金额小计)as 金额合计 from 临时消费信息表 where 箱号='" + Trim(list) + "'and 所在大厅 ='" + Trim(Combo1.Text) + "'group by 消费状态 order by 消费状态"
     main_qtgl_ztfwgl_ck.Adodc2.Refresh
     If main_qtgl_ztfwgl_ck.Adodc2.Recordset.RecordCount > 1 Then
        main_qtgl_ztfwgl_ck.Labdd.Caption = Format(Val(main_qtgl_ztfwgl_ck.Adodc2.Recordset.Fields(1)), "0.00")
        If main_qtgl_ztfwgl_ck.Adodc2.Recordset.EOF = False Then main_qtgl_ztfwgl_ck.Adodc2.Recordset.MoveNext
           main_qtgl_ztfwgl_ck.Labzd.Caption = Format(Val(main_qtgl_ztfwgl_ck.Adodc2.Recordset.Fields(1)), "0.00")
        ElseIf main_qtgl_ztfwgl_ck.Adodc2.Recordset.RecordCount = 1 Then
              If main_qtgl_ztfwgl_ck.Adodc2.Recordset.Fields(0) = "赠单" Then
                 main_qtgl_ztfwgl_ck.Labzd.Caption = Format(Val(main_qtgl_ztfwgl_ck.Adodc2.Recordset.Fields(1)), "0.00")
                 main_qtgl_ztfwgl_ck.Labdd.Caption = "0.00"
              Else
                 main_qtgl_ztfwgl_ck.Labdd.Caption = Format(Val(main_qtgl_ztfwgl_ck.Adodc2.Recordset.Fields(1)), "0.00")
                 main_qtgl_ztfwgl_ck.Labzd.Caption = "0.00"
           End If
        End If
        main_qtgl_ztfwgl_ck.Labzj.Caption = Format(Val(main_qtgl_ztfwgl_ck.Labdd.Caption) + Val(main_qtgl_ztfwgl_ck.Labzd.Caption), "0.00")
        main_qtgl_ztfwgl.Enabled = False
     Else
        MsgBox ("对不起，此箱号的客人未消费，请重新选择！")
     End If
     ElseIf Adodc1.Recordset.Fields("状态") = "空闲" Then MsgBox "请先领牌！"
     ElseIf Adodc1.Recordset.Fields("状态") = "损坏" Then MsgBox "您选的箱号已损坏，请重新选择！"
   End If
  Case Is = "tj"     '统计
   If Adodc1.Recordset.RecordCount > 0 And Adodc1.Recordset.Fields("状态") = "使用" Then
    '查询客人是否消费
    main_qtgl_ztfwgl_tj.Adodc1.RecordSource = "select * from 临时消费信息表 where 箱号='" + Trim(list) + "'and 所在大厅 ='" + Trim(Combo1.Text) + "'"
    main_qtgl_ztfwgl_tj.Adodc1.Refresh
    If main_qtgl_ztfwgl_tj.Adodc1.Recordset.RecordCount > 0 Then
     Load main_qtgl_ztfwgl_tj
     main_qtgl_ztfwgl_tj.Show
     '统计点单金额、赠单金额、金额合计
     main_qtgl_ztfwgl_tj.Adodc1.RecordSource = "select 消费状态 as 状态1,sum(金额小计)as 金额合计 from 临时消费信息表 where 箱号='" + Trim(list) + "'and 所在大厅 ='" + Trim(Combo1.Text) + "'group by 消费状态 order by 消费状态"
     main_qtgl_ztfwgl_tj.Adodc1.Refresh
     If main_qtgl_ztfwgl_tj.Adodc1.Recordset.RecordCount > 1 Then
      main_qtgl_ztfwgl_tj.Labdd.Caption = Format(Val(main_qtgl_ztfwgl_tj.Adodc1.Recordset.Fields(1)), "0.00")
      If main_qtgl_ztfwgl_tj.Adodc1.Recordset.EOF = False Then main_qtgl_ztfwgl_tj.Adodc1.Recordset.MoveNext
      main_qtgl_ztfwgl_tj.Labzd.Caption = Format(Val(main_qtgl_ztfwgl_tj.Adodc1.Recordset.Fields(1)), "0.00")
     ElseIf main_qtgl_ztfwgl_tj.Adodc1.Recordset.RecordCount = 1 Then
      If main_qtgl_ztfwgl_tj.Adodc1.Recordset.Fields(0) = "赠单" Then
       main_qtgl_ztfwgl_tj.Labzd.Caption = Format(Val(main_qtgl_ztfwgl_tj.Adodc1.Recordset.Fields(1)), "0.00")
       main_qtgl_ztfwgl_tj.Labdd.Caption = "0.00"
      Else
       main_qtgl_ztfwgl_tj.Labdd.Caption = Format(Val(main_qtgl_ztfwgl_tj.Adodc1.Recordset.Fields(1)), "0.00")
       main_qtgl_ztfwgl_tj.Labzd.Caption = "0.00"
      End If
     End If
     main_qtgl_ztfwgl_tj.Labzj.Caption = Format(Val(main_qtgl_ztfwgl_tj.Labdd.Caption) + Val(main_qtgl_ztfwgl_tj.Labzd.Caption), "0.00")
     main_qtgl_ztfwgl.Enabled = False
    Else
     MsgBox ("对不起，此箱号的客人未消费，请重新选择！")
    End If
   ElseIf Adodc1.Recordset.Fields("状态") = "空闲" Then MsgBox "请先领牌！"
   ElseIf Adodc1.Recordset.Fields("状态") = "损坏" Then MsgBox "您选的箱号已损坏，请重新选择！"
   End If
  Case Is = "jy"     '设置箱号禁用
   If Adodc1.Recordset.RecordCount > 0 And Adodc1.Recordset.Fields("状态") = "空闲" Then
    Adodc1.Recordset.Fields("状态") = "损坏"
    Adodc1.Recordset.Update
    ListView1.ListItems.Clear
    Call View_List     '调用函数
   ElseIf Adodc1.Recordset.Fields("状态") = "使用" Then MsgBox "您选的箱号正在使用，请重新选择！"
   ElseIf Adodc1.Recordset.Fields("状态") = "损坏" Then MsgBox "您选的箱号已损坏，请重新选择！"
   End If
  Case Is = "hf"     '恢复禁用箱号
   If Adodc1.Recordset.RecordCount > 0 And Adodc1.Recordset.Fields("状态") = "损坏" Then
    Adodc1.Recordset.Fields("状态") = "空闲"
    Adodc1.Recordset.Update
    ListView1.ListItems.Clear
    Call View_List     '调用函数
   ElseIf Adodc1.Recordset.Fields("状态") = "使用" Then MsgBox "您选的箱号正在使用，请重新选择！"
   ElseIf Adodc1.Recordset.Fields("状态") = "空闲" Then MsgBox "您选的箱号没有损坏，请重新选择！"
   End If
  Case Is = "hx"     '换箱
    If Adodc1.Recordset.RecordCount > 0 And Adodc1.Recordset.Fields("状态") = "使用" Then
    Load main_qtgl_ztfwgl_hx
    main_qtgl_ztfwgl_hx.Show
    main_qtgl_ztfwgl_hx.Labyxh.Caption = list
    main_qtgl_ztfwgl_hx.Labydt.Caption = Combo1.Text
    main_qtgl_ztfwgl.Enabled = False
   ElseIf Adodc1.Recordset.Fields("状态") = "空闲" Then MsgBox "您选的箱号空闲，请重新选择！"
   ElseIf Adodc1.Recordset.Fields("状态") = "损坏" Then MsgBox "您选的箱号已损坏，请重新选择！"
   End If
  Case Is = "jrbfxf"     '进入包房消费
   If Adodc1.Recordset.RecordCount > 0 And Adodc1.Recordset.Fields("状态") = "使用" Then
    main_qtgl_ztfwgl_jrbfxf.Adodc2.RecordSource = "select * from 包房消费临时表 where 箱号='" + Trim(list) + "'and 所在大厅 ='" + Trim(Combo1.Text) + "'"
    main_qtgl_ztfwgl_jrbfxf.Adodc2.Refresh
    If main_qtgl_ztfwgl_jrbfxf.Adodc2.Recordset.RecordCount > 0 Then
     MsgBox Combo1.Text & list & "箱号的客人已在包房消费，请重新选择！"
    Else
     Load main_qtgl_ztfwgl_jrbfxf
     main_qtgl_ztfwgl_jrbfxf.Show
     main_qtgl_ztfwgl_jrbfxf.Labxh.Caption = list
     main_qtgl_ztfwgl_jrbfxf.Labdt.Caption = Combo1.Text
     main_qtgl_ztfwgl.Enabled = False
    End If
    ElseIf Adodc1.Recordset.Fields("状态") = "空闲" Then MsgBox "您选的箱号空闲，请重新选择！"
    ElseIf Adodc1.Recordset.Fields("状态") = "损坏" Then MsgBox "您选的箱号已损坏，请重新选择！"
   End If
  Case Is = "jsbfxf"     '结束包房消费
   txtSQL = "select * from 包房消费临时表 where 箱号='" + Trim(list) + "'and 所在大厅 ='" + Trim(Combo1.Text) + "'"
   Set rs1 = ESQL(txtSQL)
   If rs1.RecordCount > 0 Then
     myval = MsgBox("是否结束包房消费？", vbYesNo)
     If myval = vbYes Then
      txtSQL = "select * from 临时消费信息表"
      Set rs2 = ESQL(txtSQL)
      '添加新记录到临时消费信息表中
      rs2.AddNew
      rs2.Fields("箱号") = list
      rs2.Fields("所在大厅") = Combo1.Text
      rs2.Fields("项目编号") = "包房消费"
      rs2.Fields("名称") = "在" & rs1.Fields("包房名称") & "包房消费"
      rs2.Fields("单位") = "分钟"
      rs2.Fields("单价") = Val(rs1.Fields("包房价格"))
      rs2.Fields("数量") = DateDiff("n", rs1.Fields("进入时间"), Now)
      rs2.Fields("消费状态") = "点单"
      rs2.Fields("隐藏状态") = "消费中"
      rs2.Fields("登记时间") = rs1.Fields("进入时间")
      rs2.Fields("折扣") = "10"
      rs2.Fields("金额小计") = Format((Val(rs1.Fields("包房价格")) / Val(rs1.Fields("分钟描述"))) * (DateDiff("n", rs1.Fields("进入时间"), Now)), "0.00")
      rs2.Update
      txtSQL = "select * from 包房消费历史表"
      Set rs4 = ESQL(txtSQL)
      '添加新记录到包房消费历史表中
      rs4.AddNew
      rs4.Fields("房间号") = rs1.Fields("房间号")
      rs4.Fields("包房名称") = rs1.Fields("包房名称")
      rs4.Fields("包房价格") = rs1.Fields("包房价格")
      rs4.Fields("箱号") = rs1.Fields("箱号")
      rs4.Fields("所在大厅") = rs1.Fields("所在大厅")
      rs4.Fields("进入时间") = rs1.Fields("进入时间")
      rs4.Fields("结束时间") = Now
      rs4.Fields("消费时间") = DateDiff("n", rs1.Fields("进入时间"), Now)
      rs4.Fields("消费金额") = Format((Val(rs1.Fields("包房价格")) / Val(rs1.Fields("分钟描述"))) * (DateDiff("n", rs1.Fields("进入时间"), Now)), "0.00")
      rs4.Fields("是否结帐") = "否"
      rs4.Update
      '按房间号查询包房信息表
      txtSQL = "select * from 包房信息表 where 房间号='" + rs1.Fields("房间号") + "'"
      Set rs3 = ESQL(txtSQL)
      rs3.Fields("状态") = "空闲"     '设置包房状态为空闲
      rs3.Update
      rs1.Delete
      rs1.Update
    End If
   Else
    MsgBox ("此箱号没有进行包房消费！")
   End If
  Case Is = "bfxftj"     '包房消费统计
   Load main_qtgl_ztfwgl_bfxftj
   main_qtgl_ztfwgl_bfxftj.Show
   frm_main.Enabled = False
 End Select
End Sub
'///////////////////////////////////////////////////////////////////////////////////////
Private Sub CmdFind_Click()     '查询
 ListView1.ListItems.Clear
 Call View_List
End Sub
Private Sub Command1_Click()
 Unload Me
 frm_main.Enabled = True
End Sub