关闭

机房收费系统—结账

190人阅读 评论(12) 收藏 举报
分类:

结账也是机房收费系统中难点之一,首先要理清楚怎样结账。

售卡张数:student_Info 表中 未结账的卡的总数
退卡张数:cancelCard_Info 表中 未结账 退卡的总数
总收卡张数:售卡张数-退卡张数
充值金额:recharge_Info 表中 未结账 的充值金额(包括注册时候充值的金额和充值金额)
退卡金额:cancelCard_Info 表中 未结账 的退卡金额
应收金额:充值金额—退卡金额
临时收费金额:临时用户应收的金额

Private Sub cmdaccounts_Click(Index As Integer)   '结账时,如果管理员开卡了了,也应该给管理员结账,不能只给操作员结账。
Dim txtSQL As String
Dim msgText As String
Dim mrc As ADODB.Recordset
Dim mrcc As ADODB.Recordset
Dim mrb As ADODB.Recordset
Dim mrbb As ADODB.Recordset
Dim mrd As ADODB.Recordset
Dim mrdd As ADODB.Recordset
Dim lastmoney As String
Dim nowmoney As String

'更改数据库中的结账状态
txtSQL = "select Ischeck from student_Info where UserID='" & Trim(ComboUserID.Text) & "' and IsCheck='未结账'"
Set mrc = ExecuteSQL(txtSQL, msgText)
    Do While Not mrc.EOF
        mrc.Fields(0) = Trim("结账")
        mrc.Update
        mrc.MoveNext
    Loop
        mrc.Close
        
txtSQL = "select * from cancelcard_info where UserID = '" & Trim(ComboUserID.Text) & "' and status = '" & "未结账" & "'"
Set mrcc = ExecuteSQL(txtSQL, msgText)
   Do While Not mrcc.EOF
      mrcc.Fields(6) = Trim("结账")
      mrcc.Update
      mrcc.MoveNext
   Loop
      mrcc.Close

txtSQL = "select * from recharge_info where UserID = '" & Trim(ComboUserID.Text) & "' and status = '未结账'"
Set mrbb = ExecuteSQL(txtSQL, msgText)
   Do While Not mrbb.EOF
      mrbb.Fields(7) = Trim("结账")
      mrbb.Update
      mrbb.MoveNext
   Loop
      mrbb.Close
      
 '修改成功,将刚才的操作写入日报表
 Call ReportDay
 Call ReportSum
 
 MsgBox "恭喜您,结账成功", vbOKOnly + vbExclamation '"警告"
 Unload Me
 
   Call viewdate
End Sub
Private Sub ReportDay()
    Dim txtSQL As String
    Dim msgText As String
    
    Dim mrCD As ADODB.Recordset
    Dim mrcSD As ADODB.Recordset
    Dim mrcRC As ADODB.Recordset
    Dim mrcL As ADODB.Recordset
    Dim mrcCD As ADODB.Recordset
    
    Dim CardCash As Double      '对应 本期充值卡余额
    Dim rechargeCash As Double  '对应 本日充值余额
    Dim lineCash As Double      '对应 本日消费金额
    Dim cancelcash As Double    '对应 本日退卡金额
    
    txtSQL = "select * from CheckDay_Info"      '等待写入
    Set mrcCD = ExecuteSQL(txtSQL, msgText)
      
    txtSQL = "select sum(cash)  from student_Info where status='使用'"
    Set mrcSD = ExecuteSQL(txtSQL, msgText)     '存放 本期充值卡余额
      
    '下面结的都是这一天中从早晨到结账这一时刻的总的金额。
    txtSQL = "select sum(addmoney) from ReCharge_Info where date='" & Date & "'"
    Set mrcRC = ExecuteSQL(txtSQL, msgText)     '存放 本日充值金额
      
    txtSQL = "select sum(consume) from Line_Info where offdate='" & Date & "'"
    Set mrcL = ExecuteSQL(txtSQL, msgText)      '存放 本日消费金额
      
    txtSQL = "select sum(CancelCash) from CancelCard_Info where date='" & Date & "'"
    Set mrcCC = ExecuteSQL(txtSQL, msgText)     '存放 本日退卡金额
    
    '逐一判断 记录集合 为空的情况。如果查询结果为空 则赋值为0
    If IsNull(mrcSD.Fields(0).Value) Then
        CardCash = 0
    Else
        CardCash = mrcSD.Fields(0)
    End If
      
    If IsNull(mrcRC.Fields(0).Value) Then
        rechargeCash = 0
    Else
        rechargeCash = mrcRC.Fields(0)
    End If
      
    If IsNull(mrcL.Fields(0).Value) Then
        lineCash = 0
    Else
        lineCash = mrcL.Fields(0)
    End If
      
    If IsNull(mrcCC.Fields(0).Value) Then
        cancelcash = 0
    Else
        cancelcash = mrcCC.Fields(0)
    End If
 '向日结账单中添加记录CheckDay 表
        mrcCD.AddNew
        mrcCD.Fields(0) = Val(CardCash) - Val(rechargeCash) + Val(lineCash) + Val(cancelcash) '上期充值卡金额
        mrcCD.Fields(1) = Val(rechargeCash)  '当日充值卡金额
        mrcCD.Fields(2) = Val(lineCash)      '当日消费金额
        mrcCD.Fields(3) = Val(cancelcash)    '当日退卡金额
        mrcCD.Fields(4) = Val(CardCash)      '本期充值卡金额
        mrcCD.Fields(5) = Date
        mrcCD.Update
        mrcCD.Close
End Sub
Private Sub ReportSum()
    Dim txtSQL As String
    Dim msgText As String
    Dim mrcCD As ADODB.Recordset
    Dim mrcCS As ADODB.Recordset
      
    txtSQL = "select * from CheckDay_Info where date='" & Date & "'"
    Set mrcCD = ExecuteSQL(txtSQL, msgText)
      
    txtSQL = "select * from checkWeek_Info where date='" & Date & "'"
    Set mrcCS = ExecuteSQL(txtSQL, msgText)
      
        '如果 总报表中 没有数据,那么把日汇总表 的内容写入
        mrcCS.AddNew
        mrcCS.Fields(0) = mrcCD.Fields(0)
        mrcCS.Fields(1) = mrcCD.Fields(1)
        mrcCS.Fields(2) = mrcCD.Fields(2)
        mrcCS.Fields(3) = mrcCD.Fields(3)
        mrcCS.Fields(4) = mrcCD.Fields(4)
        mrcCS.Fields(5) = mrcCD.Fields(5)  
    <span style="white-space:pre">	</span>mrcCS.Update        '更新
    <span style="white-space:pre">	</span>mrcCS.Close         '释放内存
        mrcCD.Close     
End Sub
Private Sub ComboUserName_click()
Dim msgText As String
Dim mrc As ADODB.Recordset
Dim txtSQL As String

'从数据库中调出操作员的卡号
txtSQL = "select * from User_Info where Level='" & "操作员" & "'"
Set mrc = ExecuteSQL(txtSQL, msgText)
Do While mrc.EOF = False

    ComboUserID.AddItem mrc.Fields(0)
    mrc.MoveNext
    
Loop

Call viewdate
End Sub
Private Sub ComboUserID_click()
'从数据库中调出所选操作员的姓名
Dim msgText As String
Dim mrc As ADODB.Recordset
Dim txtSQL As String

txtSQL = "select * from User_Info where userID='" & ComboUserID.Text & "'"
Set mrc = ExecuteSQL(txtSQL, msgText)
    ComboUserName.Text = mrc.Fields(3)
mrc.Close
  
Call viewdate
End Sub
Private Sub Form_Load()
Dim msgText As String
Dim mrc As ADODB.Recordset
Dim txtSQL As String

'从数据库中调出操作员的卡号
txtSQL = "select * from User_Info where Level='" & "操作员" & "'"
Set mrc = ExecuteSQL(txtSQL, msgText)
Do While mrc.EOF = False

    ComboUserID.AddItem mrc.Fields(0)
    mrc.MoveNext
    
Loop

SSTab1.Tab = 0 '设置刚刚载入窗体时候的选项卡的位置
End Sub
Private Sub viewdate()
Dim txtSQL As String
Dim mrc As ADODB.Recordset
Dim mrc1 As ADODB.Recordset
Dim mrc2 As ADODB.Recordset
Dim mrc3 As ADODB.Recordset
Dim mrc4 As ADODB.Recordset
Dim mrc5 As ADODB.Recordset
Dim msgText As String
Dim cancelcash As Variant
Dim linshicash As Variant
Dim rechargeCash As Variant

'注册时
    With myflexgrid1
        .CellAlignment = 4
        .Rows = 1
        .TextMatrix(0, 0) = "学号"
        .TextMatrix(0, 1) = "卡号"
        .TextMatrix(0, 2) = "日期"
        .TextMatrix(0, 3) = "时间"
        
        '选择学生表中用户名为combo、未结账数据
        txtSQL = "select * from student_Info where UserID ='" & Trim(ComboUserID.Text) & "' and Ischeck='" & "未结账" & "'"

        
            Do While Not mrc1.EOF
                .Rows = .Rows + 1
                .CellAlignment = 4
                .TextMatrix(.Rows - 1, 0) = Trim(mrc1.Fields(1) & "")
                .TextMatrix(.Rows - 1, 1) = Trim(mrc1.Fields(0) & "")
                .TextMatrix(.Rows - 1, 2) = Trim(mrc1.Fields(12) & "")
                .TextMatrix(.Rows - 1, 3) = Trim(mrc1.Fields(13) & "")
                mrc1.MoveNext
            Loop
    End With


 '当点击充值时
    With myflexgrid2
        .CellAlignment = 4
        .Rows = 1
        .TextMatrix(0, 0) = "学号"
        .TextMatrix(0, 1) = "卡号"
        .TextMatrix(0, 2) = "充值金额"
        .TextMatrix(0, 3) = "日期"
        .TextMatrix(0, 4) = "时间"
        
        '选择学生表中用户名为combo、未结账数据
        txtSQL = "select* from ReCharge_Info where UserID ='" & Trim(ComboUserID.Text) & "' and status='" & "未结账" & "' "

            Do While Not mrc2.EOF
                .Rows = .Rows + 1
                .CellAlignment = 4
                .TextMatrix(.Rows - 1, 0) = Trim(mrc2.Fields(1) & "")
                .TextMatrix(.Rows - 1, 1) = Trim(mrc2.Fields(2) & "")
                .TextMatrix(.Rows - 1, 2) = Trim(mrc2.Fields(3) & "")
                .TextMatrix(.Rows - 1, 3) = Trim(mrc2.Fields(4) & "")
                .TextMatrix(.Rows - 1, 4) = Trim(mrc2.Fields(5) & "")
                rechargeCash = rechargeCash + mrc2.Fields(3)          '汇总充值金额
                mrc2.MoveNext
            Loop
    End With


 '当点击退卡时
    With myflexgrid3
        .CellAlignment = 4
        .Rows = 1
        .TextMatrix(0, 0) = "学号"
        .TextMatrix(0, 1) = "卡号"
        .TextMatrix(0, 2) = "退卡金额"
        .TextMatrix(0, 3) = "日期"
        .TextMatrix(0, 4) = "时间"
        
        '选择学生表中用户名为combo、未结账数据
        txtSQL = "select* from CancelCard_Info where UserID ='" & Trim(ComboUserID.Text) & "' and status='" & "未结账" & "'"
        Set mrc3 = ExecuteSQL(txtSQL, msgText)

            Do While Not mrc3.EOF
                .Rows = .Rows + 1
                .CellAlignment = 4
                .TextMatrix(.Rows - 1, 0) = Trim(mrc3.Fields(0) & "")
                .TextMatrix(.Rows - 1, 1) = Trim(mrc3.Fields(1) & "")
                .TextMatrix(.Rows - 1, 2) = Trim(mrc3.Fields(2) & "")
                .TextMatrix(.Rows - 1, 3) = Trim(mrc3.Fields(3) & "")
                .TextMatrix(.Rows - 1, 4) = Trim(mrc3.Fields(4) & "")
                cancelcash = cancelcash + mrc3.Fields(2)              '汇总退卡金额
                mrc3.MoveNext
            Loop
    End With


  '当点击临时用户时
    With myflexgrid4
        .CellAlignment = 4
        .Rows = 1
        .TextMatrix(0, 0) = "学号"
        .TextMatrix(0, 1) = "卡号"
        .TextMatrix(0, 2) = "日期"
        .TextMatrix(0, 3) = "时间"
   End With
        '选择学生表中用户名为combo、临时用户、未结账数据
        txtSQL = "select* from student_Info where UserID ='" & Trim(ComboUserID.Text) & "' and Ischeck='未结账' and type='临时用户'"
        Set mrc = ExecuteSQL(txtSQL, msgText)
        If mrc.EOF = True Then
'            MsgBox "没有记录", vbOKOnly + vbExclamation, "警告"
            linshicash = "0"  '若没有临时用户,临时用户的金额未经0
        Else
            linshicash = mrc.Fields(7)
            txtSQL = "select * from line_Info where cardno='" & Trim(mrc.Fields(0)) & "'"
            Set mrc4 = ExecuteSQL(txtSQL, msgText)
            
            With myflexgrid4
            Do While Not mrc.EOF
                .Rows = .Rows + 1
                .CellAlignment = 4
                .TextMatrix(.Rows - 1, 0) = Trim(mrc.Fields(1) & "")
                .TextMatrix(.Rows - 1, 1) = Trim(mrc.Fields(0) & "")
                .TextMatrix(.Rows - 1, 2) = Trim(mrc.Fields(12) & "")
                .TextMatrix(.Rows - 1, 3) = Trim(mrc.Fields(13) & "")
                linshicash = Val(linshicash) + Val(Trim(mrc4.Fields(11)))    '临时收费金额=卡内剩余金额+消费金额
                mrc4.MoveNext
                mrc.MoveNext
            Loop
            End With
        End If
         
   '当点击结账时
    salecard.Text = mrc1.RecordCount
    cancelcard.Text = mrc3.RecordCount
    rechargemoney.Text = Val(rechargeCash)
    linshimoney.Text = Val(linshicash)
    cancelmoney.Text = Val(cancelcash)
    card.Text = Val(salecard.Text) - Val(cancelcard.Text)
    cash.Text = Val(rechargemoney.Text) - Val(cancelmoney.Text)

End Sub
Private Sub SSTab1_Click(previousTab As Integer)

If SSTab1.Tab = 5 Then   '当点击退出时
    Unload Me
End If

End Sub



0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:14507次
    • 积分:3669
    • 等级:
    • 排名:第8891名
    • 原创:75篇
    • 转载:1篇
    • 译文:0篇
    • 评论:1577条
    最新评论