机房收费系统——结账

结账在整个系统中应该算是最难的部分了，难点并不是知识点有多么深奥，主要是“麻烦”，如果不了解业务和以及系统适合的范围，我们很难清楚结账到底是怎么结的，针对“谁”或者“谁们”进行的结账，不简简单单的是管理员进行的工作而已。

想明白怎样结账之后就得考虑那些“钱”的问题，究竟钱从哪里来，要到哪里去，最后以什么样的形式出现在账单里等等，关于这些内容每个人有每个人的想法，只要想法正确，符合逻辑实际情况，都是正确的结账方式。下面是我的结账窗体，以我的思路介绍一下我的结账方式，可能也有不完善，理解不到位的地方，希望大家指正。

一、界面：

二、   特殊控件的使用（SSTab）

       1、在“部件”中勾选：

2、设置属性：

三、   结账：

1、窗体加载：

‘组合框中显示所有的操作员

DimstrSQL As String

Dimobjrst As ADODB.Recordset

Dimmsgtext As String

strSQL= "select * from user_Info where Level='" & "操作员" & "'"

Setobjrst = ExecuteSQL(strSQL, msgtext)

DoWhile objrst.EOF = False

        cbouserID.AddItem objrst.Fields(0)

        objrst.MoveNext

Loop

2、SSTab1_Click事件：

‘购卡按钮，显示该操作员的购卡情况，从注册表和退卡表中中查询信息，其中的购卡包含了已经退了的卡。

IfSSTab1.Caption = "购卡"Then

MSHFlexGrid1.Visible= True                    

MSHFlexGrid1.ColWidth(0) = 2000

MSHFlexGrid1.ColWidth(1) = 2000

        MSHFlexGrid1.ColWidth(2)= 2000

MSHFlexGrid1.ColWidth(3) = 2000

Dim strSQL1 As String

Dim objrst1 As ADODB.Recordset

Dimmsgtext1 As String

DimStrSQL4 As String

Dim objrst4 As ADODB.Recordset

Dim msgtext4 As String

strSQL1 = "select * from student_Info where username='" &Trim(cbouserID.Text) & "'"

Set objrst1 = ExecuteSQL(strSQL1, msgtext1)

StrSQL4= "select * from cancelcard_Info where userID='" &Trim(cbouserID.Text) & "'"

Set objrst4 = ExecuteSQL(StrSQL4, msgtext4)

WithMSHFlexGrid1

.Rows= 1

.Cols= 4

.TextMatrix(0,0) = "卡号"

.TextMatrix(0,1) = "学号"

.TextMatrix(0,2) = "日期"

.TextMatrix(0,3) = "时间"

DoWhile objrst1.EOF = False

                 .Rows = .Rows + 1

.Cols= 4

.TextMatrix(.Rows- 1, 0) = objrst1.Fields(0)

.TextMatrix(.Rows - 1, 1) =objrst1.Fields(1)

.TextMatrix(.Rows- 1, 2) = objrst1.Fields(11)

.TextMatrix(.Rows- 1, 3) = objrst1.Fields(12)                  

objrst1.MoveNext

Loop

DoWhile objrst4.EOF = False

   .Rows = .Rows + 1

     .Cols= 4

   .TextMatrix(.Rows - 1, 0) =objrst4.Fields(0)

   .TextMatrix(.Rows - 1, 1) =objrst4.Fields(6)

   .TextMatrix(.Rows - 1, 2) = objrst4.Fields(3)

    .TextMatrix(.Rows - 1, 3) =objrst4.Fields(4)

   objrst4.MoveNext

Loop

EndWith

End If

‘充值从充值表中查询信息，包含了某个卡号的初始充值记录和每一次的充值记录

If SSTab1.Caption = "充值" Then

MSHFlexGrid2.Visible = True

MSHFlexGrid2.ColWidth(0)= 2000

MSHFlexGrid2.ColWidth(1)= 2000

MSHFlexGrid2.ColWidth(2)= 2000

MSHFlexGrid2.ColWidth(3)= 2000

MSHFlexGrid2.ColWidth(4)= 2000

DimStrSQL2 As String

Dimobjrst2 As ADODB.Recordset

Dimmsgtext2 As String

StrSQL2 = "select * fromrecharge_Info where userID='" & Trim(cbouserID.Text) &"'"

Setobjrst2 = ExecuteSQL(StrSQL2, msgtext2)

WithMSHFlexGrid2

.Rows= 1

.Cols= 5

.TextMatrix(0,0) = "卡号"

.TextMatrix(0,1) = "学号"

.TextMatrix(0,2) = "充值金额"

.TextMatrix(0,3) = "日期"

.TextMatrix(0,4) = "时间"

                    

DoWhile objrst2.EOF = False

        .Rows = .Rows + 1

        .Cols = 5

        .TextMatrix(.Rows- 1, 0) =objrst2.Fields(0)

        .TextMatrix(.Rows- 1, 1) =objrst2.Fields(3)

        .TextMatrix(.Rows- 1, 2) =objrst2.Fields(1)

        .TextMatrix(.Rows- 1, 3) =objrst2.Fields(5)

        .TextMatrix(.Rows-1,4) =objrst2.Fields(6)

        objrst2.MoveNext

Loop

EndWith

End If

       ‘退卡信息，从退卡表中查询信息

If SSTab1.Caption = "退卡" Then

MSHFlexGrid3.Visible = True

MSHFlexGrid3.ColWidth(0)= 2000

MSHFlexGrid3.ColWidth(1)= 2000

MSHFlexGrid3.ColWidth(2)= 2000

MSHFlexGrid3.ColWidth(3)= 2000

MSHFlexGrid3.ColWidth(4)= 2000

DimStrSQL3 As String

Dimobjrst3 As ADODB.Recordset

Dimmsgtext3 As String

StrSQL3 = "select * fromcancelcard_Info where userID='" & Trim(cbouserID.Text) &"'"

WithMSHFlexGrid3

.Rows= 1

.Cols= 5

.TextMatrix(0,0) = "卡号"

.TextMatrix(0,1) = "学号"

.TextMatrix(0,2) = "日期"

.TextMatrix(0,3) = "时间"

.TextMatrix(0,4) = "退卡金额"

DoWhile objrst3.EOF = False

     .Rows = .Rows + 1

     .Cols = 5

     .TextMatrix(.Rows - 1, 0) =objrst3.Fields(0)

     .TextMatrix(.Rows - 1, 1) =objrst3.Fields(6)

      .TextMatrix(.Rows - 1, 2) =objrst3.Fields(3)

      .TextMatrix(.Rows - 1, 3) =objrst3.Fields(4)

      .TextMatrix(.Rows - 1, 4) = objrst3.Fields(1)

      objrst3.MoveNext

Loop

EndWith

           

End If

If SSTab1.Caption = "退出" Then

UnloadMe

End If

If SSTab1.Caption = "临时用户" Then

Label3.Visible= True

Label3.Caption = "暂时没有添加功能！"

End If

‘汇总卡的情况，我针对的是单个操作员的汇总，结账的也是单个操作员。

If SSTab1.Caption = "汇总" Then

Frame1.Visible= True

End If

3、 结账按钮：

先检查有没有需要结账的用户：

strSQL(7) = "select * from student_Info whereusername='" & Trim(cbouserID.Text) & "' and ischeck='未结账'"

Set objrst(7) = ExecuteSQL(strSQL(7), msgtext(7))

If objrst(7).RecordCount = 0 Then

MsgBox "没有需要结账的用户！", vbOKOnly +vbExclamation, "提示"

Else

       

'售卡张数

strSQL(0) = "select cardno from student_Infowhere username='" & Trim(cbouserID.Text) & "'"

Set objrst(0) =ExecuteSQL(strSQL(0), msgtext(0))

If objrst(0).EOF = False Then

Text1.Text= objrst(0).RecordCount

Else

      Text1.Text = 0

End If

'退卡张数

strSQL(1) = "select cardno from cancelcard_Infowhere userID='" & Trim(cbouserID.Text) & "'"

Set objrst(1) = ExecuteSQL(strSQL(1), msgtext(1))

If objrst(1).RecordCount = 0 Then

    Text2.Text = 0

MsgBox"没有记录", vbOKOnly +vbExclamation, "提示"

Else

If objrst(1).EOF = False Then

          Text2.Text= objrst(1).RecordCount

End If

End If

'充值金额

strSQL(2) = "select addmoney from recharge_Infowhere userID='" & Trim(cbouserID.Text) & "'"

Set objrst(2) = ExecuteSQL(strSQL(2), msgtext(2))

strSQL(3) = "select recharge from cancelcard_Infowhere userID='" & Trim(cbouserID.Text) & "'"

Set objrst(3) = ExecuteSQL(strSQL(3), msgtext(3))

recash1 = 0

objrst(2).MoveFirst

  Do While Not(objrst(2).EOF)

        recash1= recash1 + objrst(2).Fields("addmoney")

        objrst(2).MoveNext

  Loop

  recash2 = 0

If objrst(3).RecordCount = 0 Then

        Text3.Text= recash1

Else

        objrst(3).MoveFirst

        Do WhileNot (objrst(3).EOF)

                      recash2 = recash2 +objrst(3).Fields("recharge")

               objrst(3).MoveNext

         Loop

         Text3.Text= recash1 + recash2

End If

'退还金额

strSQL(4) = "select returncash fromcancelcard_Info where userID='" & Trim(cbouserID.Text) &"'"

Set objrst(4) = ExecuteSQL(strSQL(4), msgtext(4))

If objrst(4).RecordCount = 0 Then

Text5.Text= 0

Else

returncash= 0

      objrst(4).MoveFirst

      Do WhileNot (objrst(4).EOF)

            returncash = returncash +objrst(4).Fields("returncash")

           objrst(4).MoveNext

            Text5.Text = returncash

       Loop

End If

'总售卡张数

strSQL(5) = "select cardno from student_Infowhere username='" & Trim(cbouserID.Text) & "'"

Set objrst(5) = ExecuteSQL(strSQL(5), msgtext(5))

If objrst(5).EOF = False Then

          text6.Text= objrst(5).RecordCount + objrst(1).RecordCount

End If

 '应收总金额

text7.Text = Val(Trim(Text3.Text) - Trim(Text5.Text))

strSQL(6) = "select * from student_Info where username='"& Trim(cbouserID.Text) & "' and ischeck='未结账'"

Set objrst(6) = ExecuteSQL(strSQL(6), msgtext(6))

If objrst(6).RecordCount >0 Then

objrst(6).MoveFirst

DoWhile objrst(6).EOF = False

       objrst(6).Fields(10) = "已结账"

           objrst(6).MoveNext

         Loop

           objrst(6).Close

Else

           MsgBox"没有需要结账的用户！",vbOKOnly + vbExclamation, "提示"

           Exit Sub

End If

       MsgBox "结账成功！",vbOKOnly + vbExclamation, "提示"

End If

 

‘填写日结账单表的内容

'上期余额

strSQL(9) = "select addmoney from recharge_Infowhere date='" & Date & "'and userID='" &cbouserID.Text & "'"

Set objrst(9) = ExecuteSQL(strSQL(9), msgtext(9))

Dim m As Integer

strSQL(13) = "select * from line_Info whereloginondate='" & Date & "'and username='" &cbouserID.Text & "'"

Set objrst(13) = ExecuteSQL(strSQL(13), msgtext(13))

If objrst(13).RecordCount = 0 Then

       m = 0

Else

m= objrst(13).RecordCount - 1

End If

recash7 = 0

Do While Not (objrst(9).EOF)

        recash7 = recash7 +objrst(9).Fields("addmoney")

      objrst(9).MoveNext

 Loop

      recash8 =0

Do While Not (objrst(13).EOF)

      recash8 =recash8 + objrst(13).Fields("consumcash")

      objrst(13).MoveNext

  Loop

strSQL(14) = "select top " & m &" * from line_Info where loginondate='" & Date & "'andusername='" & cbouserID.Text & "'order by serial"

Set objrst(14) = ExecuteSQL(strSQL(14), msgtext(14))

      recash10 =0

If objrst(14).RecordCount = 0 Then

       recash10= 0

Else

Do While Not (objrst(14).EOF)

           recash10 = recash10 +objrst(14).Fields("consumcash")

           objrst(14).MoveNext

Loop

End If

       

'今日充值金额

strSQL(8) = "select addmoney from recharge_Infowhere date='" & Date & "'and userID='" &cbouserID.Text & "'"

Set objrst(8) = ExecuteSQL(strSQL(8), msgtext(8))

recash3 = 0

Do While Not (objrst(8).EOF)

       recash3 =recash3 + objrst(8).Fields("addmoney")

       objrst(8).MoveNext

  Loop

'今日消费金额

strSQL(11) = "select consumcash from line_Infowhere loginondate='" & Date & "'and username='" &cbouserID.Text & "'"

Set objrst(11) = ExecuteSQL(strSQL(11), msgtext(11))

recash5 = 0

Do While Not (objrst(11).EOF)

        recash5= recash5 + objrst(11).Fields("consumcash")

        objrst(11).MoveNext

  Loop

'今日退卡金额

strSQL(12) = "select * from cancelcard_Info whereloginondate='" & Date & "'and userID='" &cbouserID.Text & "'"

Set objrst(12) = ExecuteSQL(strSQL(12), msgtext(12))

Do While Not (objrst(12).EOF)

        recash6= recash6 + objrst(12).Fields("returncash")

        objrst(12).MoveNext

Loop

strSQL(10) = "select * from checkday_Info wheredate='" & Date & "'and username='" & cbouserID.Text& "'"

Set objrst(10) = ExecuteSQL(strSQL(10), msgtext(10))

'如果是第一次添加的信息

If objrst(10).RecordCount = 0 Then

       objrst(10).AddNew

objrst(10).Fields(6)= Trim(cbouserID.Text)

objrst(10).Fields(0)= 0

       objrst(10).Fields(1) = recash3

      objrst(10).Fields(2)= recash5

           If objrst(12).RecordCount = 0 Then

                   objrst(10).Fields(3) = 0

           Else

                    objrst(10).Fields(3) = recash6

            End If

objrst(10).Fields(4)= recash7 - recash8

                    objrst(10).Fields(5) = Date

                    objrst(10).Update

               

     Else

           objrst(10).Fields(0)= recash7 - recash10

           objrst(10).Fields(1)= recash3

           objrst(10).Fields(2)= recash5

           If objrst(12).RecordCount = 0 Then

               objrst(10).Fields(3) = 0

           Else

               objrst(10).Fields(3) = recash6

           End If

'本次余额

  Dim strSQLn AsString

  Dim objrstn AsADODB.Recordset

  Dim msgtextnAs String

  strSQLn ="select * from line_Info where loginondate='" & Date &"'and username='" & cbouserID.Text & "'"

  Set objrstn =ExecuteSQL(strSQLn, msgtextn)

 recash9 = 0

 Do While Not(objrstn.EOF)

           recash9 = recash9 +objrstn.Fields("consumcash")

            objrstn.MoveNext

      Loop

   objrst(10).Fields(4)= recash7 - recash9

   objrst(10).Fields(5)= Date

   objrst(10).Fields(6)= Trim(cbouserID.Text)

   objrst(10).Update

End If

    日结账单主要是对每一天的操作进行总结，我认为关键是结账的方式，因为对于一个或者多个操作员，每天可以结账若干次，我管理员要对每次的结账内容汇总，随时更新数据表中的数据，这个时候的关键就是上期余额的获取，用什么方法在更新数据之前先保存本次余额作为下一次的上期余额等。

我做的方法有漏洞的地方，我采用了标识字段的方法，用总的充值金额和消费金额做减法，至于消费金额分为总的消费金额和前n-1次的消费金额，如何查询到钱n-1次的消费金额，采用的查询条件是什么，这些是对数据库进行的操作。

       对于数据库的操作，我在做整个系统的过程中尽量每种方式都尝试着换着用，增删改查的功能都以不同的形式展示了，下一篇博客详细介绍整个机房中对数据库中的四种基本功能的运用总结。