前言
结账窗体难在把账缕清,不能让用户用你的系统亏钱啊。下面看看我对这个窗体细节的理解。
一、界面设计
我把操作员用户名改为了文本框,只能输入,而姓名栏改成了label框,当用户输入用户名时,名字自动显示。窗体中用到了新控件——SSTab,切换SSTab选项卡用tab属性即可。
二、与各表联系
1.购卡:student表中此操作员注册的卡
2.充值:recharge表中此操作员充值的,未结账的记录
3.退卡:cancelcard表中此操作员退卡的,未结账的记录
4.临时用户:student表中此操作员注册的,未结账,未退卡的临时用户的信息
5.汇总
(1)售卡张数=购卡选项卡的记录总数
(2)退卡张数=退卡选项卡的记录总数
(3)充值金额=recharge表充值金额总数
(4)临时收费金额=student表未结账的临时用户的金额总数
(5)退卡金额=cancelcard表信息的金额总数
(6)总售卡数=售卡张数-退卡张数
(7)应收金额=充值金额-退卡金额
结账:将student表,cancelcard表,recharge表中“未结账”改为“已结账”,更新日账单和周账单。
三、代码片段
结账代码
Private Sub cmdCount_Click()
Dim txtSQL As String
Dim MsgText As String
Dim mrc As ADODB.Recordset
Dim mrc1 As ADODB.Recordset
Dim mrc2 As ADODB.Recordset
Dim mrc3 As ADODB.Recordset
Dim mrc4 As ADODB.Recordset
Dim mrc5 As ADODB.Recordset
Dim consumecash As Integer
Dim sumcash As Integer
If txtUserId.Text = "" Then
MsgBox "请选择操作员后结账!", , "提示"
Exit Sub
End If
'计算上期消费金额
txtSQL = "select sum(cash) from student_info where userid='" & Trim(txtUserId.Text) & " ' and ischeck='" & "未结账" & "'"
Set mrc = ExecuteSQL(txtSQL, MsgText)
If IsNull(mrc.Fields(0)) Then
sumcash = 0
Else
sumcash = mrc.Fields(0)
End If
'计算当日消费金额
txtSQL = "select sum(consume) from line_info"
Set mrc1 = ExecuteSQL(txtSQL, MsgText)
If IsNull(mrc1.Fields(0)) Then
consumecash = 0
Else
consumecash = mrc1.Fields(0)
End If
'更新日账单
txtSQL = "select * from checkday_info where date='" & Date & "'"
Set mrc2 = ExecuteSQL(txtSQL, MsgText)
'当天结多次账的情况,在原有基础上更新
If mrc2.EOF = False Then
mrc2!rechargecash = Val(txtRecharge.Text) + mrc2!rechargecash
mrc2!consumecash = consumecash + mrc2!consumecash
mrc2!cancelcash = Val(txtBackCardMoney.Text) + mrc2!cancelcash
mrc2!allcash = (sumcash + Val(txtRecharge.Text) - Val(txtBackCardMoney.Text) - consumecash) + mrc2!allcash
mrc2!Date = Date
mrc2.Update
mrc2.Close
mrc1.Close
mrc.Close
Else
mrc2.AddNew
mrc2!rechargecash = Val(txtRecharge.Text)
mrc2!consumecash = consumecash
mrc2!cancelcash = Val(txtBackCardMoney.Text)
mrc2!allcash = sumcash + Val(txtRecharge.Text) - Val(txtBackCardMoney.Text) - consumecash
mrc2!Date = Date
mrc2.Update
mrc2.Close
mrc1.Close
mrc.Close
End If
'更新student表
txtSQL = "select * from student_info where userid='" & Trim(txtUserId.Text) & "' and ischeck='" & "未结账" & "'"
Set mrc3 = ExecuteSQL(txtSQL, MsgText)
Do While Not mrc3.EOF
mrc3!ischeck = "已结账"
mrc3.Update
mrc3.MoveNext
Loop
mrc3.Close
'更新recharge表
txtSQL = "select * from recharge_info where userid='" & Trim(txtUserId.Text) & "' and status='" & "未结账" & "'"
Set mrc4 = ExecuteSQL(txtSQL, MsgText)
Do While Not mrc4.EOF
mrc4!Status = "已结账"
mrc4.Update
mrc4.MoveNext
Loop
mrc4.Close
'更新cancelcard表
txtSQL = "select * from cancelcard_info where userid='" & Trim(txtUserId.Text) & "' and status='" & "未结账" & "'"
Set mrc5 = ExecuteSQL(txtSQL, MsgText)
Do While Not mrc5.EOF
mrc5!Status = "已结账"
mrc5.Update
mrc5.MoveNext
Loop
mrc5.Close
MsgBox "结账成功!", 48, "提示"
For Each ctr1 In Me.Controls
If TypeOf ctr1 Is TextBox Then
ctr1.Text = ""
End If
Next
Unload Me
End Sub