敲机房最头痛的就是结账了,思路不清晰,代码就无法实现,想了好几天才完成。
首先要明确的是对象,即:谁给谁结账。
系统涉及三个级别的用户:一般用户,管理员,操作员。首先排除权限最低的一般用户,不难想出:管理员给操作员结账。
接下来就是要明确界面要达到的预期效果。
通过MsFlexGrid的单击事件,选择操作员,显示操作员信息。
然后分模块显示需要的信息,这里用到了选项卡控件。
接下来是对选项卡要显示的内容,通过代码实现。
就拿购卡来说吧,需要我们调的是student_info表,而且限定条件:status="未结账";userid=选中的操作员。这样才能保证调出的记录是正确的、
<strong><span style="font-family:KaiTi_GB2312;font-size:18px;">Private Sub SSTab1_Click(PreviousTab As Integer)
Select Case SSTab1.Tab
Case 0
txtSQL = "select * from student_info where userid='" & Trim(cmbid.Text) & "'" and status='未结账' "
Set mrc = ExecuteSQL(txtSQL, msgtext)
With MSFlexGrid1
.Rows = 1
.CellAlignment = 4
.TextMatrix(0, 0) = "学号"
.TextMatrix(0, 1) = "卡号"
.TextMatrix(0, 2) = "日期"
.TextMatrix(0, 3) = "时间"
While Not mrc.EOF
.Rows = .Rows + 1
.CellAlignment = 4
.TextMatrix(.Rows - 1, 0) = Trim(mrc!studentno)
.TextMatrix(.Rows - 1, 1) = Trim(mrc!cardno)
.TextMatrix(.Rows - 1, 2) = Trim(mrc!Date)
.TextMatrix(.Rows - 1, 3) = Trim(mrc!Time)
mrc.MoveNext
Wend
mrc.Close
End With </span></strong>
充值、临时用户显示跟购卡类似。
最后就是结账了。
售卡张数=注册卡的数量
退卡张数=退卡的数量
总售卡数=售卡总数-退卡总数
充值金额=符合条件的所有充值记录的总和
退卡金额=符合条件的所有退卡记录的总和
应收金额=充值金额-退卡金额
代码实现:
'售卡张数
txtSQL = "select count(cardno) from student_Info where UserID='" & Trim(cmbid.Text) & "' and ischeck = '" & "未结账" & "'"
Set mrc = ExecuteSQL(txtSQL, msgtext)
If mrc.EOF And mrc.BOF Then
Rcharge = 0
Else
Rcharge = mrc.Fields(0)
End If
txtsalenum.Text = mrc.Fields(0)
'退卡张数
txtSQL = "select count(cardno) from cancelcard_Info where UserID='" & Trim(cmbid.Text) & "' and status= '" & "未结账" & "'"
Set mrc = ExecuteSQL(txtSQL, msgtext)
If MSFlexGrid1.Rows = 1 Then
Tuicharge = 0
Else
Tuicharge = mrc.Fields(0)
End If
txtcancelcard.Text = mrc.Fields(0)
mrc.Close
'售卡总张数
txtallsellcard.Text = Rcharge - Tuicharge
'充值金额
txtSQL6 = "select sum(addmoney) from recharge_Info where UserID='" & Trim(cmbid.Text) & "' "
Set mrc6 = ExecuteSQL(txtSQL6, MsgText6)
If mrc6.EOF Then
RchargeMoney = 0
Else
RchargeMoney = mrc6.Fields(0)
End If
txtrechargecash.Text = RchargeMoney
'退卡
txtSQL5 = "select sum(CancelCash ) from cancelcard_info where UserID='" & Trim(cmbid.Text) & "'"
Set mrc5 = ExecuteSQL(txtSQL5, MsgText5)
If IsNull(Trim(mrc5.Fields(0))) Then
txtcancelcash.Text = 0
Else
txtcancelcash.Text = Trim(mrc5.Fields(0))
End If
'应收总金额=充值金额-退卡金额
txtallcash.Text = RchargeMoney - TuichargeMoney
'结账的同时要更改数据库用户结账状态,
txtSQL = "select * from student_Info where UserID='" & Trim(cmbid.Text) & "'" & " and Ischeck = '" & "未结账" & "'"
Set objrs = ExecuteSQL(txtSQL, msgtext)
Do While objrs.EOF = False
objrs!ischeck = "已结账"
objrs.MoveNext
Loop
objrs.Close
txtsql1 = "select * from cancelcard_Info where UserID='" & Trim(cmbid.Text) & "'" & " and Ischeck = '" & "未结账" & "'"
Set objrs1 = ExecuteSQL(txtSQL, msgtext)
If Not objrs1.EOF Then
objrs1!ischeck = "已结账"
objrs1.MoveNext
End If
objrs1.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(txtrechargecash.Text)
objrs.Fields(2) = Trim(txtrechargecash.Text) - Trim(txtcancelcash.Text)
objrs.Fields(3) = Trim(txtcancelcash.Text)
objrs.Fields(4) = lastmoney
objrs.Fields(5) = Date
objrs.Update
objrs.Close
MsgBox "结账成功", vbOKOnly + vbExclamation, "提示"
在做后面的周结账的时候,会调一段时间的结账记录,为了方便起见,我是把结账记录直接也同步到了checkweek_info表中,即:在此处编写代码,实现checkweek_info表更新。
小结:
思路是灯,照亮了敲代码之路!
无需畏惧和烦躁,就这么一直努力着,一定能出现自己想要的结果!