第一次机房收费系统——结账
前言:谈到结账,大家都觉得挺难的,当然我也是这么觉得的,但是通过一段时间的磨练,最终还是解决了,那么就来分享一下把!
页面
首先谈到页面,我将临时收费金额改成了上网收费金额。
汇总的计算
售卡张数
就是你今天所出售的卡数
注意: 是今天售的卡数。
退卡张数
即你今天所退的的卡数
充值金额
我认为注册基础余额也是一种充值,所以我的充值是充值的钱+注册时候的钱。
上网收费金额
即是上机产生的消费。
退卡金额
即你今天退卡所返还给用户的钱。
总售卡数
即售卡张数-退卡张数
应收金额
即充值金额(充值金额包括充值金额+注册时所充值的金额)-退卡金额
代码
添加操作员用户名
Private Sub Form_Load()
'让窗体居中
Me.Left = Screen.Width / 2 - Me.Width / 2
Me.Top = Screen.Height / 2 - Me.Height / 2
BorderStyle = 1
WindowState = 0
'添加操作员用户名
txtSQL = "select *from user_info where level='" & "操作员" & "' or level='" & "管理员" & "'"
Set mrc_User = ExecuteSQL(txtSQL, MsgText)
If mrc_User.EOF Then
Exit Sub
End If
mrc_User.MoveFirst
While Not mrc_User.EOF
ComboUserID.AddItem mrc_User.Fields(0)
mrc_User.MoveNext
Wend
mrc_User.Close
'调用过程,添加表名
Call AddName
End Sub
添加操作员真实姓名和四个控件中的内容
Private Sub ComboUserID_click()
'添加操作员姓名
txtSQL = "select *from user_info where userid='" & Trim(ComboUserID.Text) & "'"
Set mrc_User = ExecuteSQL(txtSQL, MsgText)
If mrc_User.EOF Then
mrc_User.Close
Exit Sub
End If
lblUsrName.Caption = mrc_User.Fields(3)
mrc_User.Close
'调用过程,添加表名
Call AddName
'购卡
SellNum = 0 '售卡张数为0
SellMoney = 0
txtSQL = "select*from student_info where userid='" & ComboUserID.Text & "'" & "and " & _
"ischeck='" & "未结账" & "'and date='" & Date & "'"
Set mrc_Stu_Sum = ExecuteSQL(txtSQL, MsgText)
If mrc_Stu_Sum.EOF = False Then
mrc_Stu_Sum.MoveFirst
End If
While Not mrc_Stu_Sum.EOF
With FlexGridSell
.Rows = .Rows + 1
.CellAlignment = 4
.ColAlignment = 4
.TextMatrix(.Rows - 1, 0) = Trim(mrc_Stu_Sum.Fields(1)) '学号
.TextMatrix(.Rows - 1, 1) = Trim(mrc_Stu_Sum.Fields(0)) ' "卡号"
.TextMatrix(.Rows - 1, 2) = Trim(mrc_Stu_Sum.Fields(13)) '"日期"
.TextMatrix(.Rows - 1, 3) = Trim(mrc_Stu_Sum.Fields(14)) '"时间"
SellMoney = SellMoney + Val(mrc_Stu_Sum.Fields(7))
SellNum = SellNum + 1
mrc_Stu_Sum.MoveNext
End With
Wend
'关闭数据库
mrc_Stu_Sum.Close
'充值
AddMoney = 0
txtSQL = "select*from recharge_info where userid='" & ComboUserID.Text & "'" & "and " & _
"status='" & "未结账" & "' and date='" & Date & "'"
Set mrc_RE = ExecuteSQL(txtSQL, MsgText)
If mrc_RE.EOF = False Then
mrc_RE.MoveFirst
End If
While Not mrc_RE.EOF
With FlexGridRe
.Rows = .Rows + 1
.CellAlignment = 4
.ColAlignment = 4
.TextMatrix(.Rows - 1, 0) = Trim(mrc_RE.Fields(1)) '学号
.TextMatrix(.Rows - 1, 1) = Trim(mrc_RE.Fields(2)) ' "卡号"
.TextMatrix(.Rows - 1, 2) = Trim(mrc_RE.Fields(3)) '"充值金额"
.TextMatrix(.Rows - 1, 3) = Trim(mrc_RE.Fields(4)) '"日期"
.TextMatrix(.Rows - 1, 4) = Trim(mrc_RE.Fields(5)) '"时间"
AddMoney = AddMoney + Val(mrc_RE.Fields(3))
mrc_RE.MoveNext
End With
Wend
mrc_RE.Close
'退卡
BackCard = 0
txtSQL = "select*from cancelcard_info where userid='" & ComboUserID.Text & "'" & "and " & _
"status='" & "未结账" & "' and date='" & Date & "'"
Set mrc_Stu_Off = ExecuteSQL(txtSQL, MsgText)
If mrc_Stu_Off.EOF = False Then
mrc_Stu_Off.MoveFirst
End If
While Not mrc_Stu_Off.EOF
With FlexGridOff
.Rows = .Rows + 1
.CellAlignment = 4
.ColAlignment = 4
.TextMatrix(.Rows - 1, 0) = Trim(mrc_Stu_Off.Fields(1)) '学号
.TextMatrix(.Rows - 1, 1) = Trim(mrc_Stu_Off.Fields(0)) ' "卡号"
.TextMatrix(.Rows - 1, 2) = Trim(mrc_Stu_Off.Fields(3)) '"日期"
.TextMatrix(.Rows - 1, 3) = Trim(mrc_Stu_Off.Fields(4)) '"时间"
.TextMatrix(.Rows - 1, 4) = Trim(mrc_Stu_Off.Fields(2)) '"退卡金额"
BackNum = BackNum + 1
BackMoney = BackMoney + Val(mrc_Stu_Off.Fields(2))
mrc_Stu_Off.MoveNext
End With
Wend
mrc_Stu_Off.Close
'临时用户
txtSQL = "select*from student_info where userid='" & ComboUserID.Text & "'" & "and " & _
"ischeck='" & "未结账" & "'" & "and " & _
"type='" & "临时用户" & "' and date='" & Date & "'"
Set mrc_Stu_Tem = ExecuteSQL(txtSQL, MsgText)
If mrc_Stu_Tem.EOF = False Then
mrc_Stu_Tem.MoveFirst
End If
While Not mrc_Stu_Tem.EOF
With FlexGridTem
.Rows = .Rows + 1
.CellAlignment = 4
.ColAlignment = 4
.TextMatrix(.Rows - 1, 0) = Trim(mrc_Stu_Tem.Fields(1)) '学号
.TextMatrix(.Rows - 1, 1) = Trim(mrc_Stu_Tem.Fields(0)) ' "卡号"
.TextMatrix(.Rows - 1, 2) = Trim(mrc_Stu_Tem.Fields(13)) '"日期"
.TextMatrix(.Rows - 1, 3) = Trim(mrc_Stu_Tem.Fields(14)) '"时间"
.TextMatrix(.Rows - 1, 4) = Trim(mrc_Stu_Tem.Fields(7)) '金额
mrc_Stu_Tem.MoveNext
End With
Wend
mrc_Stu_Tem.Close
End Sub
结账按钮
Private Sub lblOK_Click()
'购卡
txtSQL = "select*from student_info where userid='" & ComboUserID.Text & "'" & "and " & _
"ischeck='" & "未结账" & "' and date='" & Date & "'"
Set mrc_Stu_Sum = ExecuteSQL(txtSQL, MsgText)
If mrc_Stu_Sum.EOF = False Then
mrc_Stu_Sum.MoveFirst
End If
While Not mrc_Stu_Sum.EOF
mrc_Stu_Sum.Fields(11) = "结账"
mrc_Stu_Sum.Update
mrc_Stu_Sum.MoveNext
Wend
'关闭数据库
mrc_Stu_Sum.Close
'充值
AddMoney = 0
txtSQL = "select*from recharge_info where userid='" & ComboUserID.Text & "'" & "and " & _
"status='" & "未结账" & "' and date='" & Date & "'"
Set mrc_RE = ExecuteSQL(txtSQL, MsgText)
If mrc_RE.EOF = False Then
mrc_RE.MoveFirst
End If
While Not mrc_RE.EOF
AddMoney = AddMoney + Trim(mrc_RE.Fields(3))
mrc_RE.Fields(7) = "结账"
mrc_RE.Update
mrc_RE.MoveNext
Wend
mrc_RE.Close
'退卡
BackCard = 0
txtSQL = "select*from cancelcard_info where userid='" & ComboUserID.Text & "'" & "and " & _
"status='" & "未结账" & "' and date='" & Date & "'"
Set mrc_Stu_Off = ExecuteSQL(txtSQL, MsgText)
If mrc_Stu_Off.EOF = False Then
mrc_Stu_Off.MoveFirst
End If
While Not mrc_Stu_Off.EOF
mrc_Stu_Off.Fields(6) = "结账"
mrc_Stu_Off.Update
mrc_Stu_Off.MoveNext
Wend
mrc_Stu_Off.Close
'临时用户
'' TemMoney = 0 '临时收费金额
txtSQL = "select*from student_info where userid='" & ComboUserID.Text & "'" & "and " & _
"ischeck='" & "未结账" & "'" & "and " & _
"type='" & "临时用户" & "' and date='" & Date & "'"
Set mrc_Stu_Tem = ExecuteSQL(txtSQL, MsgText)
If mrc_Stu_Tem.EOF = False Then
mrc_Stu_Tem.MoveFirst
End If
While Not mrc_Stu_Tem.EOF
With FlexGridTem
.Rows = .Rows + 1
.CellAlignment = 4
.ColAlignment = 4
mrc_Stu_Tem.Fields(11) = "结账"
mrc_Stu_Tem.MoveNext
End With
Wend
mrc_Stu_Tem.Close
'下机
'' TemMoney = 0 '临时收费金额
txtSQL = "select*from line_info where userid='" & ComboUserID.Text & "'" & "and " & _
"statu='" & "未结账" & "' and offdate='" & Date & "'"
Set mrc_Line = ExecuteSQL(txtSQL, MsgText)
If mrc_Line.EOF = False Then
mrc_Line.MoveFirst
End If
While Not mrc_Line.EOF
mrc_Line.Fields(16) = "结账"
mrc_Line.MoveNext
Wend
mrc_Line.Close
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'调用过程,添加表名
Call AddName
'临时收费金额
txtSQL = "select * from line_info where offdate='" & Format(Date, "yyyy-MM-dd") & "'" & _
"and userid='" & ComboUserID.Text & "' and offdate='" & Date & "'"
Set mrc_Line = ExecuteSQL(txtSQL, MsgText)
If mrc_Line.EOF Then
TemMoney = 0
Else
While Not mrc_Line.EOF
TemMoney = TemMoney + Val(Trim(mrc_Line.Fields(11)))
mrc_Line.MoveNext
Wend
End If
' TemMoney = mrc_Line.Fields(10)
'售卡张数
lblSellCardSum.Caption = SellNum
'退卡张数
lblBackCardSum.Caption = BackNum
'充值金额
lblRecharge.Caption = AddMoney
'临时收费金额
lblTemRecharge.Caption = TemMoney
'退卡金额
lblBackCardMoney.Caption = BackMoney
'总售卡数
SumCard = SellNum - BackNum
lblSellCardActual.Caption = SumCard
'所售金额
SunMoney = AddMoney - BackMoney
lblCollectMoney.Caption = SunMoney
'传值去日结账单
txtSQL = "select *from checkday_info "
Set mrc_CheckDay = ExecuteSQL(txtSQL, MsgText)
If mrc_CheckDay.EOF Then
LastMoney = 0
Else
mrc_CheckDay.MoveLast
LastMoney = Trim(mrc_CheckDay.Fields(0))
End If
mrc_CheckDay.AddNew
mrc_CheckDay.Fields(0) = Trim(LastMoney) '上次余额
mrc_CheckDay.Fields(1) = AddMoney '充值金额
mrc_CheckDay.Fields(2) = TemMoney '消费金额
mrc_CheckDay.Fields(3) = BackMoney '退卡金额
mrc_CheckDay.Fields(4) = SunMoney '所售金额
mrc_CheckDay.Fields(5) = Format(Date, "yyyy-MM-dd") '日期
mrc_CheckDay.Update
mrc_CheckDay.Close
End Sub
为了避免代码重复过大,所以写的添加导航属性的过程!
Public Sub AddName()
'购卡添加表头
With FlexGridSell
.Rows = 1
.CellAlignment = 4
.ColAlignment = 4
.TextMatrix(0, 0) = "学号"
.TextMatrix(0, 1) = "卡号"
.TextMatrix(0, 2) = "日期"
.TextMatrix(0, 3) = "时间"
End With
'充值添加表头
With FlexGridRe
.Rows = 1
.CellAlignment = 4
.ColAlignment = 4
.TextMatrix(0, 0) = "学号"
.TextMatrix(0, 1) = "卡号"
.TextMatrix(0, 2) = "充值金额"
.TextMatrix(0, 3) = "日期"
.TextMatrix(0, 4) = "时间"
End With
'退卡添加表头
With FlexGridOff
.Rows = 1
.CellAlignment = 4
.ColAlignment = 4
.TextMatrix(0, 0) = "学号"
.TextMatrix(0, 1) = "卡号"
.TextMatrix(0, 2) = "日期"
.TextMatrix(0, 3) = "时间"
.TextMatrix(0, 4) = "退卡金额"
End With
'临时用户添加表头
With FlexGridTem
.Rows = 1
.CellAlignment = 4
.ColAlignment = 4
.TextMatrix(0, 0) = "学号"
.TextMatrix(0, 1) = "卡号"
.TextMatrix(0, 2) = "日期"
.TextMatrix(0, 3) = "时间"
.TextMatrix(0, 4) = "消费金额"
End With
End Sub
希望我的分享能你有所帮助!
感谢您的阅读,如果您有更好的方法,欢迎分享给我,谢谢您!