机房收费系统之选择下机

      

 

       机房的上下机是是它的难点之一，主要是之前没有接触过类似的操作和应用，是学习对VB和数据库的新的应用。所以需要我们转变思路，去用另一个角度去思考它

       单纯主窗体的上下机来说，只要理清楚思路，画出流程图，把上机和下机的逻辑搞清楚，还是不是太难的，但是当下机学生过多时，我们不能一个学生一个学生的下机，所以为了方便，就有了选择下机和全部下机/

       选择下机和单独的下机相比，多了一个选择的功能，对软件使用者来说，可以完成多个学生卡号同时进行下机操作。在看了各位大神巨人的博客后，加上自己的总结，有了自己的选择下机的思路

       大致思路是，对选择的卡号做标记，进行选择下机操作后，判断是否存在做标记的卡号，将卡号一次赋值给一个变量，进行下机操作，再当前卡号下机完成后，将表中本条记录删除，判断表中是否还存在做标记的卡号，有，则继续进行下机操作，给变量赋值（下一个做标记的卡号）直到表中做标记的卡号全部完成下机操作，显示下机成功。

具体代码如下：

 

Rem:选择学生下机

Private Sub 选中学生下线_Click(Index As Integer)

博客

    Dim cnn As ADODB.Connection

    Dim txtSQL As String            '用来存放SQL语句,

    Dim txtsql1 As String

    Dim txtsql2 As String

    Dim txtSQL3 As String

    Dim txtSQL4 As String

 

    Dim msgText As String          '用来存放返回信息

    Dim mrc As New ADODB.Recordset

    Dim mrc1 As New ADODB.Recordset

    Dim mrc2 As New ADODB.Recordset

    Dim mrc3 As New ADODB.Recordset

    Dim mrc4 As New ADODB.Recordset     '是否上机

    Dim mrcOnline As New ADODB.Recordset

    Dim a As Integer   '计算消费时间

    Dim b As Integer    '计算消费金额

    Dim c As Integer    '计算余额

    Dim consumetime As String

    Dim consume As String

    Dim str As String   '获取选中内容

    Dim i As Integer

   

    '判断是否选中学生

    With myFlexgrid

        If .TextMatrix(.Row, 5) = "" Then     'If myFlexgrid.TextMatrix(i, 5) = "√" Then

        GoTo case2

    Else

        GoTo case1

    End If

    End With

    With myFlexgrid

case2:

    MsgBox "没有选中学生", , "提示"

     Exit Sub

case1:

        '获取选中卡号

        str = myFlexgrid.TextMatrix(myFlexgrid.Row, 0) 'TextMatrix方法

        '查询上机表

        txtSQL4 = "select * from ONLine_Info where cardno= '" & Trim(str) & "'"

        Set mrc4 = ExecuteSQL(txtSQL4, msgText)

            '计算上机时间

            OnTime = mrc4.Fields(9)     '上机时间

            '时间格式不一致，无法计算

            OffTime = Format(Now(), "yyyy-MM-dd H:mm:ss ")               '获取下机日期、时间

            consumetime = DateDiff("n", OnTime, OffTime)     '计算上机时间   n,代表返回的是分钟

 

            '判断上机时间

            txtsql2 = "select * from BasicData_Info where head = '" & UserName & "'"

            Set mrc2 = ExecuteSQL(txtsql2, msgText)

            '上下级记录

            strSQL = "select * from line_Info where Cardno='" & Trim(str) & "'and Status='正常上机'"

            Set mrc3 = ExecuteSQL(strSQL, msgText)

               

            If Not mrc3.EOF Then

                '判断用户类型

                '固定用户,小于准备时间

                If Trim(mrc4.Fields(1)) = "固定用户" And Trim(consumetime) < Trim(mrc2.Fields(4)) Then       '判断上机时间是否小于准备时间，是则消费金额为0

                    consume = 0

                    mrc3.Fields(8) = Date

                    mrc3.Fields(9) = Time

                    mrc3.Fields(10) = consumetime

                    mrc3.Fields(11) = consume

                Else

                End If

                '满足准备时间，少于最少上机时间，按60分钟收取费用

                If Trim(mrc4.Fields(1)) = "固定用户" And consumetime <= Trim(mrc2.Fields(2)) Then

                    consume = mrc2.Fields(5)     '计算固定用户金额

     

                    mrc3.Fields(8) = Date

                    mrc3.Fields(9) = Time

                    mrc3.Fields(10) = consumetime

                    mrc3.Fields(11) = consume       '消费金额

                Else

                End If

                '大于60分钟计算收费

                If Trim(mrc4.Fields(1)) = "固定用户" And consumetime > Trim(mrc2.Fields(2)) Then

                    '计算消费金额

                    consume = Int(mrc2.Fields(0) / 60 * Val(consumetime))     '计算固定用户金额

                    mrc3.Fields(8) = Date

                    mrc3.Fields(9) = Time

                    mrc3.Fields(10) = consumetime

                    mrc3.Fields(11) = Trim(consume)       '消费金额

                End If

            Else

                MsgBox "未查询到此用户", , "提示"

            End If

                '临时用户

 

                '计算消费金额 少于准备时间

                consume = Int(mrc2.Fields(1) / 60 * Val(consumetime))     '计算临时用户金额

                If Trim(mrc4.Fields(1)) = "临时用户" And Trim(consumetime) < Trim(mrc2.Fields(4)) Then        '判断上机时间是否小于准备时间，是则消费金额为0

                    consume = 0

                    mrc3.Fields(8) = Date

                    mrc3.Fields(9) = Time

                    mrc3.Fields(10) = consumetime

                    mrc3.Fields(11) = consume

                Else

           

                '判断大于准备时间，少于最少时间时，用户的消费金额和时间

                If Trim(mrc4.Fields(1)) = "临时用户" And consumetime <= Trim(mrc2.Fields(2)) Then      '满足准备时间，少于最少上机时间，按60分钟收取费用

                    consume = mrc2.Fields(5)

                    mrc3.Fields(8) = Date

                    mrc3.Fields(9) = Time

                    mrc3.Fields(10) = consumetime

                    mrc3.Fields(11) = Trim(consume)       '消费金额

                Else

                '大于60分钟，计算消费金额

                If Trim(mrc4.Fields(1)) = "临时用户" And consumetime > Trim(mrc2.Fields(2)) Then

                    consume = Int(mrc2.Fields(1) / 60 * Val(consumetime))     '计算临时用户金额

                    mrc3.Fields(8) = Format(Date, "yyyy-mm-dd")

                    mrc3.Fields(9) = Time

                    mrc3.Fields(10) = consumetime

                    mrc3.Fields(11) = Trim(consume)       '消费金额

                Else

                End If

                End If

                End If

                    '获取卡号信息，计算时间和金额

                    txtsql1 = "select * from student_Info where cardno= '" & Trim(str) & "'"

                    Set mrc1 = ExecuteSQL(txtsql1, msgText)

                    '计算余额

                    c = mrc1.Fields(7) - consume

                    '更新student表

                    mrc1.Fields(7) = c

                    mrc1.Update

                    '更新上下机表-Line_Info

                    mrc3.Fields(8) = Date

                    mrc3.Fields(9) = Time

                    mrc3.Fields(10) = consumetime

                    mrc3.Fields(12) = c

                    mrc3.Fields(13) = "正常下机"

                    mrc3.Update

                    '更新正在上机表--OnLine_Info

                    mrc4.Delete

                    mrc4.Update

                    '更新上机人数

                    myFlexgrid.RemoveItem myFlexgrid.Row '删除选中行

                    End With

                    With myFlexgrid

                        If .TextMatrix(.Row, 5) = "√" Then     'If myFlexgrid.TextMatrix(i, 5) = "√" Then

                        GoTo case1

                    Else

                        MsgBox "选中学生以下机", , "提示"

                    End If

                    End With

End Sub