本文主要是介绍第一次机房收费系统之管理员结账，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
操作流程：
点击操作员用户名的combox控件，会出现所有操作员用户以上用户ID，选中ID下面显示操作员姓名，显示购卡，充值等界面的记录，最后计算出各个售卡退卡张数以及金额。进行结账。
使用的数据库表：
user_info（存放用户信息）
student_info（存放学生信息）
recharge_info（存放充值记录信息）
cancelcard_info（存放退卡信息）
checkday_info（日结账单）
计算公式：
充值金额=此用户为学生注册的金额+此用户为学生充值的金额
收费金额=固定用户的消费金额+临时用户的消费金额
退卡金额=此用户操作的为学生退卡的金额
总售卡数=售卡数—退卡数
应收金额=充值金额+消费金额—退卡金额
具体代码：
显示的操作：
Private Sub comboControlUID_Click()'对User_info表操作Dim mrcuser As ADODB.Recordset '用于存放记录集Dim userSQL As String '用于存放SQL语句Dim userMsgText As String '用于存放返回信息userSQL = "select * from user_info where userID='" & Trim(comboControlUID.Text) & "'"Set mrcuser = ExecuteSQL(userSQL, userMsgText)If mrcuser.EOF = True Thenmrcuser.CloseExit SubElsetxtUserName.Text = mrcuser.Fields(3)End IfCall ShowData1 '购卡Call ShowData2 '充值Call ShowData3 '退卡Call ShowData4 '临时用户Call All '汇总
End SubPrivate Sub ShowData1()'对Student_info表操作Dim mrcstudent As ADODB.Recordset '用于存放记录集Dim studentSQL As String '用于存放SQL语句Dim studentMsgText As String '用于存放返回信息studentSQL = "select * from student_info where UserID='" & Trim(comboControlUID.Text) & "' and Ischeck='未结账'"Set mrcstudent = ExecuteSQL(studentSQL, studentMsgText)If mrcstudent.EOF = True Thenmrcstudent.CloseMSHFlexGrid1.ClearWith MSHFlexGrid1.Rows = 1.CellAlignment = 4.TextMatrix(0, 0) = "学号".TextMatrix(0, 1) = "卡号".TextMatrix(0, 2) = "日期".TextMatrix(0, 3) = "时间"End WithExit SubElseWith MSHFlexGrid1.Rows = 1Do While mrcstudent.EOF = False.Rows = .Rows + 1.CellAlignment = 4.TextMatrix(.Rows - 1, 0) = Trim(mrcstudent.Fields(1)).TextMatrix(.Rows - 1, 1) = Trim(mrcstudent.Fields(0)).TextMatrix(.Rows - 1, 2) = Trim(mrcstudent.Fields(12)).TextMatrix(.Rows - 1, 3) = Trim(mrcstudent.Fields(13))mrcstudent.MoveNextLoopmrcstudent.CloseEnd WithEnd If
End Sub
Private Sub ShowData2()'对recharge_info表操作Dim mrcrecharge As ADODB.Recordset '用于存放记录集Dim rechargeSQL As String '用于存放SQL语句Dim rechargeMsgText As String '用于存放返回信息rechargeSQL = "select * from recharge_info where UserID ='" & Trim(comboControlUID.Text) & "' and status='未结账'"Set mrcrecharge = ExecuteSQL(rechargeSQL, rechargeMsgText)If mrcrecharge.EOF = True Thenmrcrecharge.CloseMSHFlexGrid2.ClearWith MSHFlexGrid2.Rows = 1.CellAlignment = 4.TextMatrix(0, 0) = "学号".TextMatrix(0, 1) = "卡号".TextMatrix(0, 2) = "充值金额".TextMatrix(0, 3) = "日期".TextMatrix(0, 4) = "时间"End WithExit SubElseWith MSHFlexGrid2.Rows = 1Do While mrcrecharge.EOF = False.Rows = .Rows + 1.CellAlignment = 4.TextMatrix(.Rows - 1, 0) = Trim(mrcrecharge.Fields(1)).TextMatrix(.Rows - 1, 1) = Trim(mrcrecharge.Fields(2)).TextMatrix(.Rows - 1, 2) = Trim(mrcrecharge.Fields(3)).TextMatrix(.Rows - 1, 3) = Trim(mrcrecharge.Fields(4)).TextMatrix(.Rows - 1, 4) = Trim(mrcrecharge.Fields(5))mrcrecharge.MoveNextLoopmrcrecharge.CloseEnd WithEnd If
End SubPrivate Sub ShowData3()'对cancelcard_info表操作Dim mrccancelcard As ADODB.Recordset '用于存放记录集Dim cancelcardSQL As String '用于存放SQL语句Dim cancelcardMsgText As String '用于存放返回信息cancelcardSQL = "select * from cancelcard_info where UserID='" & Trim(comboControlUID.Text) & "' and status='未结账'"Set mrccancelcard = ExecuteSQL(cancelcardSQL, cancelcardMsgText)If mrccancelcard.EOF = True Thenmrccancelcard.CloseMSHFlexGrid3.ClearWith MSHFlexGrid3.Rows = 1.CellAlignment = 4.TextMatrix(0, 0) = "学号".TextMatrix(0, 1) = "卡号".TextMatrix(0, 2) = "日期".TextMatrix(0, 3) = "时间".TextMatrix(0, 4) = "退卡金额"End WithExit SubElseWith MSHFlexGrid3.Rows = 1Do While mrccancelcard.EOF = False.Rows = .Rows + 1.CellAlignment = 4.TextMatrix(.Rows - 1, 0) = Trim(mrccancelcard.Fields(1)).TextMatrix(.Rows - 1, 1) = Trim(mrccancelcard.Fields(2)).TextMatrix(.Rows - 1, 2) = Trim(mrccancelcard.Fields(3)).TextMatrix(.Rows - 1, 3) = Trim(mrccancelcard.Fields(4)).TextMatrix(.Rows - 1, 4) = Trim(mrccancelcard.Fields(2))mrccancelcard.MoveNextLoopmrccancelcard.CloseEnd WithEnd If
End SubPrivate Sub ShowData4()'再次对student_info表操作Dim mrcST As ADODB.RecordsetDim STSQL As StringDim STMsgText As StringSTSQL = "select * from student_info where UserID='" & Trim(comboControlUID.Text) & "' and Ischeck='未结账' and type='临时用户'"Set mrcST = ExecuteSQL(STSQL, STMsgText)If mrcST.EOF = True ThenmrcST.CloseWith MSHFlexGrid4.Rows = 1.CellAlignment = 4.TextMatrix(0, 0) = "学号".TextMatrix(0, 1) = "卡号".TextMatrix(0, 2) = "日期".TextMatrix(0, 3) = "时间"End WithElseWith MSHFlexGrid4.Rows = 1Do While mrcST.EOF = False.Rows = .Rows + 1.CellAlignment = 4.TextMatrix(.Rows - 1, 0) = Trim(mrcST.Fields(1)).TextMatrix(.Rows - 1, 1) = Trim(mrcST.Fields(0)).TextMatrix(.Rows - 1, 2) = Trim(mrcST.Fields(12)).TextMatrix(.Rows - 1, 3) = Trim(mrcST.Fields(13))mrcST.MoveNextLoopmrcST.CloseEnd WithEnd If
End Sub
Private Sub All()Dim Temporary As Double '临时收费Dim Money As Double '充值总金额Dim Cancel As Double '退卡金额'对Student_info表操作Dim mrcstudent As ADODB.Recordset '用于存放记录集Dim studentSQL As String '用于存放SQL语句Dim studentMsgText As String '用于存放返回信息'对recharge_info表操作Dim mrcrecharge As ADODB.Recordset '用于存放记录集Dim rechargeSQL As String '用于存放SQL语句Dim rechargeMsgText As String '用于存放返回信息'对cancelcard_info表操作Dim mrccancelcard As ADODB.Recordset '用于存放记录集Dim cancelcardSQL As String '用于存放SQL语句Dim cancelcardMsgText As String '用于存放返回信息'再次对student_info表操作Dim mrcST As ADODB.RecordsetDim STSQL As StringDim STMsgText As String'售卡张数studentSQL = "select * from student_info where UserID='" & Trim(comboControlUID.Text) & "' and Ischeck='未结账'"Set mrcstudent = ExecuteSQL(studentSQL, studentMsgText)If mrcstudent.RecordCount = 0 ThentxtSellCard.Text = "0"mrcstudent.CloseElsetxtSellCard.Text = mrcstudent.RecordCountmrcstudent.CloseEnd If'充值金额rechargeSQL = "select * from recharge_info where UserID ='" & Trim(comboControlUID.Text) & "' and status='未结账'"Set mrcrecharge = ExecuteSQL(rechargeSQL, rechargeMsgText)If mrcrecharge.EOF = True ThentxtReCharge.Text = "0"mrcrecharge.CloseElseMoney = 0Do While mrcrecharge.EOF = FalseMoney = Money + Val(mrcrecharge.Fields(3))mrcrecharge.MoveNextLooptxtReCharge.Text = Moneymrcrecharge.CloseEnd If'退卡张数和退卡金额cancelcardSQL = "select * from cancelcard_info where UserID='" & Trim(comboControlUID.Text) & "' and status='未结账'"Set mrccancelcard = ExecuteSQL(cancelcardSQL, cancelcardMsgText)If mrccancelcard.RecordCount = 0 ThentxtCancelCard.Text = "0"ElsetxtCancelCard.Text = mrccancelcard.RecordCountEnd IfIf mrccancelcard.EOF = True ThentxtCancelCharge.Text = "0"ElseCancel = 0Do While mrccancelcard.EOF = FalseCancel = Cancel + Val(mrccancelcard.Fields(2))mrccancelcard.MoveNextLooptxtCancelCharge = Cancelmrccancelcard.CloseEnd If'临时收费金额STSQL = "select * from student_info where UserID='" & Trim(comboControlUID.Text) & "' and Ischeck='未结账' and type='临时用户'"Set mrcST = ExecuteSQL(STSQL, STMsgText)If mrcST.EOF = True ThentxtTemporaryCharge.Text = "0"mrcST.CloseElseTemporary = 0Do While mrcST.EOF = FalseTemporary = Temporary + Val(mrcST.Fields(7))mrcST.MoveNextLooptxtTemporaryCharge.Text = TemporarymrcST.CloseEnd If'总售卡数txtAllSellCard.Text = Int(txtSellCard.Text) - Int(txtCancelCard.Text)'应收金额txtShouldCharge.Text = Val(txtReCharge.Text) + Val(txtTemporaryCharge.Text) - Val(txtCancelCharge.Text)End Sub
结账的操作
Private Sub cmdCloseCharge_Click()Dim ReMain As DoubleDim ReCharge As Double '充值金额Dim Cancel As Double '退卡金额Dim Consume As Double '总金额'对line_info表操作Dim mrcline As ADODB.Recordset '用于存放记录集Dim lineSQL As String '用于存放SQL语句Dim lineMsgText As String '用于存放返回信息'对Student_info表操作Dim mrcstudent As ADODB.Recordset '用于存放记录集Dim studentSQL As String '用于存放SQL语句Dim studentMsgText As String '用于存放返回信息'对recharge_info表操作Dim mrcrecharge As ADODB.Recordset '用于存放记录集Dim rechargeSQL As String '用于存放SQL语句Dim rechargeMsgText As String '用于存放返回信息'对cancelcard_info表操作Dim mrccancelcard As ADODB.Recordset '用于存放记录集Dim cancelcardSQL As String '用于存放SQL语句Dim cancelcardMsgText As String '用于存放返回信息'对checkday_info表再次操作Dim mrccheck As ADODB.Recordset '用于存放记录集Dim checkSQL As String '用于存放SQL语句Dim checkMsgText As String '用于存放返回信息'对CheckDay_info表操作Dim mrccheckday As ADODB.Recordset '用于存放记录集Dim checkdaySQL As String '用于存放SQL语句Dim checkdayMsgText As String '用于存放返回信息If comboControlUID.Text = "" ThenMsgBox "请选择人员！", vbOKOnly + vbInformation, "提示"comboControlUID.SetFocusExit SubElsecheckdaySQL = "select * from checkday_info where date='" & Date & "'"Set mrccheckday = ExecuteSQL(checkdaySQL, checkdayMsgText)lineSQL = "select * from line_info where status='正常下机' and offdate='" & Date & "'"Set mrcline = ExecuteSQL(lineSQL, lineMsgText)Consume = 0If mrcline.EOF = True ThenConsume = 0mrcline.CloseElseDo While mrcline.EOF = FalseConsume = Consume + Val(mrcline.Fields(11))mrcline.MoveNextLoopmrcline.CloseEnd If'student_info结账studentSQL = "select * from student_info where UserID='" & Trim(comboControlUID.Text) & "' and Ischeck='未结账'"Set mrcstudent = ExecuteSQL(studentSQL, studentMsgText)If mrcstudent.EOF = False ThenDo While mrcstudent.EOF = Falsemrcstudent.Fields(11) = "结账"mrcstudent.Updatemrcstudent.MoveNextLoopmrcstudent.CloseEnd If'recharge_info结账rechargeSQL = "select * from recharge_info where UserID ='" & Trim(comboControlUID.Text) & "' and status='未结账'"Set mrcrecharge = ExecuteSQL(rechargeSQL, rechargeMsgText)ReCharge = 0If mrcrecharge.EOF = False ThenDo While mrcrecharge.EOF = FalseReCharge = ReCharge + Val(mrcrecharge.Fields(3))mrcrecharge.Fields(7) = "结账"mrcrecharge.Updatemrcrecharge.MoveNextLoopmrcrecharge.CloseElseReCharge = 0mrcrecharge.CloseEnd If'cancelcard_info结账cancelcardSQL = "select * from cancelcard_info where UserID='" & Trim(comboControlUID.Text) & "' and status='未结账'"Set mrccancelcard = ExecuteSQL(cancelcardSQL, cancelcardMsgText)Cancel = 0If mrccancelcard.EOF = False ThenDo While mrccancelcard.EOF = FalseCancel = Cancel + Val(mrccancelcard.Fields(2))mrccancelcard.Fields(6) = "结账"mrccancelcard.Updatemrccancelcard.MoveNextLoopmrccancelcard.CloseElseCancel = 0mrccancelcard.CloseEnd IfcheckSQL = "select * from checkday_info where date='" & Date - 1 & "'"Set mrccheck = ExecuteSQL(checkSQL, checkMsgText)ReMain = 0If mrccheck.EOF = True ThenReMain = 0mrccheck.CloseElseReMain = Val(mrccheck.Fields(4))mrccheck.CloseEnd IfIf mrccheckday.EOF = True Thenmrccheckday.AddNewmrccheckday.Fields(0) = ReMainmrccheckday.Fields(1) = ReChargemrccheckday.Fields(2) = Consumemrccheckday.Fields(3) = Cancelmrccheckday.Fields(4) = ReCharge - Cancelmrccheckday.Fields(5) = Datemrccheckday.Updatemrccheckday.CloseMsgBox "结账成功！", vbOKOnly + vbInformation, "提示"Elsemrccheckday.Fields(0) = ReMainmrccheckday.Fields(1) = ReChargemrccheckday.Fields(2) = Consumemrccheckday.Fields(3) = Cancelmrccheckday.Fields(4) = ReCharge - Cancelmrccheckday.Fields(5) = Datemrccheckday.Updatemrccheckday.CloseMsgBox "结账成功！", vbOKOnly + vbInformation, "提示"End IfEnd If
End Sub
优化方面：
1.背景图随窗体改变而改变
Dim H As Single '定义窗体高的变量
Dim W As Single '定义窗体高的变量
Private Sub Form_Load()'对User_info表操作Dim mrcuser As ADODB.Recordset '用于存放记录集Dim userSQL As String '用于存放SQL语句Dim userMsgText As String '用于存放返回信息userSQL = "select * from user_info where level='操作员'or level='管理员'"Set mrcuser = ExecuteSQL(userSQL, userMsgText)If mrcuser.EOF = True ThenMsgBox "无用户数据记录！", vbOKOnly + vbInformation, "提示"Exit SubElseDo While mrcuser.EOF = FalsecomboControlUID.AddItem mrcuser.Fields(0)mrcuser.MoveNextLoopEnd IfWith MSHFlexGrid1.Rows = 1.CellAlignment = 4.TextMatrix(0, 0) = "学号".TextMatrix(0, 1) = "卡号".TextMatrix(0, 2) = "日期".TextMatrix(0, 3) = "时间"End WithWith MSHFlexGrid2.Rows = 1.CellAlignment = 4.TextMatrix(0, 0) = "学号".TextMatrix(0, 1) = "卡号".TextMatrix(0, 2) = "充值金额".TextMatrix(0, 3) = "日期".TextMatrix(0, 4) = "时间"End WithWith MSHFlexGrid3.Rows = 1.CellAlignment = 4.TextMatrix(0, 0) = "学号".TextMatrix(0, 1) = "卡号".TextMatrix(0, 2) = "日期".TextMatrix(0, 3) = "时间".TextMatrix(0, 4) = "退卡金额"End WithWith MSHFlexGrid4.Rows = 1.CellAlignment = 4.TextMatrix(0, 0) = "学号".TextMatrix(0, 1) = "卡号".TextMatrix(0, 2) = "日期".TextMatrix(0, 3) = "时间"End WithH = Me.HeightW = Me.Width
End SubPrivate Sub Form_Resize()Me.Height = HMe.Width = WMe.PaintPicture Me.Picture, 0, 0, Me.ScaleWidth, Me.ScaleHeight '实现背景图随窗体变大而改变
End Sub
2.不可输入值
Private Sub comboControlUID_KeyPress(KeyAscii As Integer)KeyAscii = 0 '不可输入值
End Sub3.禁止粘贴
Private Sub txtSellCard_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)If Button = 2 ThenClipboard.ClearEnd If
End Sub
4.限制字符类型
https://blog.csdn.net/TGB__15__ZYB/article/details/86636625
这篇关于第一次机房收费系统之管理员结账的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
