可以说结账和两个报表是最后认为比较麻烦的问题,因为结账就意味着涉及到钱的问题,对于钱当然得好好计算,不能让用户多付,还要让系统收钱收的合理。
结账窗体首先需要一个显示操作员各种工作记录的控件SSTab控件,在工程→部件→控件 中勾选“Microsoft Tabbed Dialog Control”控件,点击应用,之后的窗体如下:
编写它的TAB事件
主要查看需要查询的操作员的购卡、充值、退卡和汇总结账等工作记录:购卡,充值,退卡都是查询数据库未结账的用户记录,方法是一样的。以充值为例:
Private Sub SSTab1_Click(PreviousTab As Integer)
Dim txtSQL As String
Dim msgText As String
Dim objrs As ADODB.Recordset
Dim Rcharge As Integer
Dim Tuicharge As Integer
Dim RchargeMoney As Integer
Dim TuichargeMoney As Integer
'充值
If SSTab1.Tab = 1 Then
MSFlexGrid2.Rows = 1
MSFlexGrid2.Cols = 5
txtSQL = "select * from ReCharge_Info where UserID='" & Trim(cmbUserID.Text) & "' and status = '" & "未结账" & "'"
Set objrs = ExecuteSQL(txtSQL, msgText)
With MSFlexGrid2
.CellAlignment = 5
.TextMatrix(0, 0) = "卡号"
.TextMatrix(0, 1) = "学号"
.TextMatrix(0, 2) = "充值金额"
.TextMatrix(0, 3) = "日期"
.TextMatrix(0, 4) = "时间"
Do While Not objrs.EOF
.Rows = .Rows + 1
.TextMatrix(.Rows - 1, 0) = objrs.Fields(2)
.TextMatrix(.Rows - 1, 1) = objrs.Fields(1)
.TextMatrix(.Rows - 1, 2) = objrs.Fields(3)
.TextMatrix(.Rows - 1, 3) = objrs.Fields(4)
.TextMatrix(.Rows - 1, 4) = objrs.Fields(5)
objrs.MoveNext
Loop
End With
End If
'汇总
If SSTab1.Tab = 4 Then
'售卡张数
txtSQL = "select count(cardno) from student_Info where UserID='" & Trim(cmbUserID.Text) & "' and ischeck = '" & "未结账" & "'"
Set objrs = ExecuteSQL(txtSQL, msgText)
If objrs.EOF And objrs.BOF Then
Rcharge = 0
Else
Rcharge = objrs.Fields(0)
End If
txtScard.Text = objrs.Fields(0)
'退卡张数同售卡张数
'售卡总张数=售卡张数+退卡张数
txtSallcard = Rcharge + Tuicharge
'充值金额
txtSQL = "select sum(addmoney) from Recharge_Info where UserID='" & Trim(cmbUserID.Text) & "'"
Set objrs = ExecuteSQL(txtSQL, msgText)
If objrs.EOF And objrs.BOF Then
RchargeMoney = 0
Else
RchargeMoney = objrs.Fields(0)
End If
txtCharge.Text = objrs.Fields(0)
'退卡金额同充值金额
'应收总金额=充值金额-退卡金额
txtAllmoney.Text = RchargeMoney - TuichargeMoney
End If
End Sub
接下来就是结账的麻烦事:
Private Sub cmdPayAccounts_Click()
Dim txtSQL As String
Dim msgText As String
Dim objrs As ADODB.Recordset
Dim lastmoney As Integer
'结账的同时要更改数据库用户结账状态,在student_Info、ReCharge_Info、CancelCard_Info改为已结账
txtSQL = "select * from student_Info where UserID='" & Trim(cmbUserID.Text) & "'" & " and Ischeck = '" & "未结账" & "'"
Set objrs = ExecuteSQL(txtSQL, msgText)
Do While objrs.EOF = False
objrs!ischeck = "已结账"
objrs.MoveNext
Loop
objrs.Close
'将一些数据存入到日结账单中:上期剩余金额,充值金额,消费金额,退卡金额,汇总金额,日期
'上期剩余金额
txtSQL = "select * from CheckDay_Info"
Set objrs = ExecuteSQL(txtSQL, msgText)
objrs.MoveLast
lastmoney = objrs.Fields(4)
objrs.MoveFirst
'写入一条新结账记录
objrs.AddNew
objrs.Fields(0) = lastmoney
objrs.Fields(1) = Trim(txtCharge.Text)
objrs.Fields(2) = Trim(txtCharge.Text) - Trim(txtTuimoney.Text)
objrs.Fields(3) = Trim(txtTuimoney.Text)
objrs.Fields(4) = lastmoney
objrs.Fields(5) = Date
objrs.Update
objrs.Close
MsgBox "结账成功", vbOKOnly + vbExclamation, "提示"
End Sub
机房收费系统快要接近尾声了,到了最后有关钱的部分,在没有理清计算关系之前真的是一头雾水,就像是中间遇到过的一些困难,只要理清关系代码就很容易写了。
接下来就是日报表和周报表的问题了。