机房收费系统之结账

偏执的太偏执、 2022-08-04 09:11 259阅读 0赞

机房收费系统中一个重要的窗体之一就是结账窗体，因为涉及到数据库中的好多表所以刚开始会觉得很难，其实理清思路后按照自己的思路写完后便觉得没有想象中的那么难！好先来看一下窗体界面:

![Center][]

说明：操作员用户名：combo1,操作员真实姓名：combo2

总售卡数=售卡张数-退卡张数

临时收费金额=临时用户注册和充值的钱

应收金额=充值金额-退卡金额

一开始看到这个界面的控件时不知道是什么控件，但是我记得之前敲百例的时候用过这么一个控件所以我就把之前百例里面的涉及到这个控件的例子找到，知道了它是什么控件！

操作员用户名和操作员真实姓名我本来是从User\_Info表里查的，但是师傅说如果有好多操作员打开combox时里面有好多的数据，有的今天就没有登录或做售卡，充值，退卡等的操作的工作所以就没有必要显示！我后来想了想觉得结账是要每天就要结账所以我就打算用worklog\_Info这个表但是这个表里没有UserName所以我就在这个表里加了一列。

Private Sub Form_Load()
    Dim mrc As ADODB.Recordset
    Dim MsgText As String
    Dim txtSQL As String
    Dim i As Integer
    
    txtSQL = "select*from worklog_Info where LoginDate= '" & Trim(Date) & "'" & " and level= '操作员' "            '今天登录过的操作员
    Set mrc = ExecuteSQL(txtSQL, MsgText)
    
        
    For i = 1 To mrc.RecordCount '用户名   
        Combo1.AddItem mrc.Fields(1)
         mrc.MoveNext
    Next i
    mrc.Close
    End Sub

Private Sub Combo1_Click()
    Dim txtSQL, MsgText As String
    Dim mrc As ADODB.Recordset
    
    If Combo1.Text = "" Then
      MsgBox "请输入操作员用户名！", vbOKOnly + vbExclamation, "警告"
    Exit Sub
    End If
    
    
    txtSQL = "select * from worklog_Info where UserID='" & Trim(Combo1.Text) & "'" & " and LoginDate = '" & Trim(Date) & "'"     '今天登录过的操作员
    Set mrc = ExecuteSQL(txtSQL, MsgText)
    
    Combo2.Text = Trim(mrc.Fields(9))
    mrc.Close
    End Sub

SSTab里就仅列汇总窗体

Private Sub SSTab1_Click(PreviousTab As Integer)
    Dim txtSQL As String
    Dim txtSQL1 As String
    Dim txtSQL2 As String
    Dim txtSQL3 As String
    
    Dim MsgText As String
    Dim MsgText1 As String
    Dim Msgtext2 As String
    Dim msgText3 As String
    
    Dim mrc As ADODB.Recordset
    Dim mrc1 As ADODB.Recordset
    Dim mrc2 As ADODB.Recordset
    Dim mrc3 As ADODB.Recordset
    
    Dim a, b, c, d As Long
    Dim i As Integer
    
    Select Case SSTab1.Tab
    '汇总
    Case 4
      '售卡张数
      txtSQL = "select * from student_Info where UserID='" & Trim(Combo1.Text) & "'" & "and Ischeck='未结账'"
      Set mrc = ExecuteSQL(txtSQL, MsgText)
      txtsellcard.Text = mrc.RecordCount
      
      '退卡数
      txtSQL2 = "select * from CancelCard_Info where UserID='" & Trim(Combo1.Text) & "'" & "and status='未结账'"
      Set mrc2 = ExecuteSQL(txtSQL2, Msgtext2)
      txtcancelcard.Text = mrc2.RecordCount   '退卡张数
      txtallsellcard.Text = Val(txtsellcard.Text) - Val(txtcancelcard.Text)    '总售卡张数
      
      '充值金额
      txtSQL1 = "select sum(addmoney) from ReCharge_Info where UserID='" & Trim(Combo1.Text) & "'" & "and status='" & "未结账" & "'"
      Set mrc1 = ExecuteSQL(txtSQL1, MsgText1)
      If mrc1.EOF And mrc1.BOF Then
         a = 0
      Else
         If IsNull(Trim(mrc1.Fields(0))) Then
             a = 0
         Else
             a = mrc1.Fields(0)
         End If
      End If
      txtrecharge.Text = a
      
      '退卡金额
      txtSQL3 = "select sum(CancelCash) from CancelCard_Info where UserID='" & Trim(Combo1.Text) & "'" & "and status='未结账'"
      Set mrc3 = ExecuteSQL(txtSQL3, msgText3)
      
      If mrc3.BOF And mrc3.EOF Then
         b = 0
      Else
         If IsNull(Trim(mrc3.Fields(0))) Then
            b = 0
         Else
            b = mrc3.Fields(0)
         End If
      End If
     txtcancelcash.Text = b
      
      '临时收费金额
      txtSQL = "select sum(addmoney) from ReCharge_Info where UserID='" & Trim(Combo1.Text) & "'" & " and type= '临时用户' " & "and status='未结账'"
      Set mrc = ExecuteSQL(txtSQL, MsgText)
      
      If mrc.BOF And mrc.EOF Then
         c = 0
      Else
         If IsNull(Trim(mrc.Fields(0))) Then
         c = 0
         Else
         c = mrc.Fields(0)
         End If
      End If
     txttmpcash.Text = c
      
      '应收金额
      txtallcash.Text = a - b
    End Select
    
    End Sub

结账：

Private Sub cmdcheck_Click()
    Dim txtSQL As String
    Dim txtSQL1 As String
    Dim srtsql As String
    Dim MsgText As String
    Dim MsgText1 As String
    Dim Msgtext2 As String
    Dim mrc As ADODB.Recordset
    Dim mrc1 As ADODB.Recordset
    Dim rst As ADODB.Recordset
    Dim recharge As Long
    Dim consume As Long
    Dim returncash As Long
    Dim profit As Long
    Dim cash As Long
    
    '更新学生表
    txtSQL = "select * from student_Info where UserID ='" & Combo1.Text & "'" & "and Ischeck='未结账'"
    Set mrc = ExecuteSQL(txtSQL, MsgText)
    
    Do While Not mrc.EOF
      mrc!Ischeck = "已结账"
      mrc.MoveNext
    Loop
    mrc.Close
    
    '更新充值表
     txtSQL1 = "select * from ReCharge_Info where UserID='" & Trim(Combo1.Text) & "'" & "and status='未结账'"
     Set mrc1 = ExecuteSQL(txtSQL1, MsgText1)
     
     Do While Not mrc1.EOF
         mrc1!Status = "已结账"
         mrc1.MoveNext
     Loop
     mrc1.Close
     
     '更新退卡表
     txtSQL = "select * from CancelCard_Info where UserID='" & Trim(Combo1.Text) & "'" & "and status='未结账'"
     Set mrc = ExecuteSQL(txtSQL, MsgText)
     
     Do While Not mrc.EOF
        mrc!Status = "已结账"
        mrc.MoveNext
     Loop
     mrc.Close
     
     '消费金额
     txtSQL1 = "select sum(consume)from Line_Info where status = '" & "正常下机" & "'"
     Set mrc1 = ExecuteSQL(txtSQL1, MsgText1)
     
     If mrc1.BOF And mrc1.EOF Then
            consume = 0  '消费金额
       Else
            If IsNull(Trim(mrc1.Fields(0))) Then
                consume = 0
            Else
                consume = mrc1.Fields(0)
            End If
       End If
    
    '当日退卡金额
    txtSQL = "select sum(CancelCash) from CancelCard_Info where UserID='" & Trim(Combo1.Text) & "'" & "and status='未结账'"
    Set mrc = ExecuteSQL(txtSQL, MsgText)
      
      If mrc.BOF And mrc.EOF Then
         returncash = 0
      Else
         If IsNull(Trim(mrc.Fields(0))) Then
            returncash = 0
         Else
            returncash = mrc.Fields(0)
         End If
      End If
      
    '当日充值金额
    txtSQL1 = "select sum(addmoney) from Recharge_Info where UserID='" & Trim(Combo1.Text) & "'" & "and status='" & "未结账" & "'"
    Set mrc1 = ExecuteSQL(txtSQL1, MsgText1)
      If mrc.EOF And mrc.BOF Then
         recharge = 0
      Else
         If IsNull(Trim(mrc.Fields(0))) Then
             recharge = 0
         Else
             recharge = mrc.Fields(0)
         End If
      End If
      
      profit = Val(recharge - consume - returncash) '本期金额
    '  cash=
      
      
    '更新日结账单
     srtsql = "select * from CheckDay_Info "
     Set rst = ExecuteSQL(srtsql, Msgtext2)
        rst.MoveLast
        cash = rst.Fields(4)
        rst.AddNew
        rst.Fields(0) = cash
        rst.Fields(1) = recharge
        rst.Fields(2) = consume
        rst.Fields(3) = returncash
        rst.Fields(4) = profit
        rst.Fields(5) = Date
        rst.Update
        rst.Close
        MsgBox "结账成功！", vbOKOnly + vbInformation, "恭喜您"
      
    End Sub

其实只要理清关系写的时候就好写了！

[Center]: /images/20220731/3e223bad870746f7ab888ff494ca0ca0.png