第一次机房收费系统——结账

Private Sub Form_Load()
    Dim txtsql As String
    Dim MsgText As String
    Dim mrc As ADODB.Recordset
    '设置用户名部分
     txtsql = "select * from user_info where level='操作员'"
    Set mrc = ExecuteSQL(txtsql, MsgText)
    Do While Not mrc.EOF
            cmbuserid.AddItem mrc.Fields(0)
            mrc.MoveNext
    Loop
End Sub

Private Sub CmbUserId_Click()
Dim txtsql As String
    Dim MsgText As String
    Dim mrc1 As ADODB.Recordset
'找到id对应的操作员的真实姓名
    txtsql = "select * from user_info where userid='" & cmbuserid.Text & "'"
    Set mrc1 = ExecuteSQL(txtsql, MsgText)
    cmbusername.Text = mrc1.Fields(3)
End Sub

Private Sub CmbUsername_change()
Dim txtsql As String
Dim MsgText As String
a = Format(Now, "yyyy/mm/dd")
'购卡
  txtsql = "select * from student_Info where UserID='" &          Trim(cmbuserid.Text) & "'and  Ischeck='δ????'and date = '" & a & "'"
    Set mrc2 = ExecuteSQL(txtsql, MsgText)
    With MSFlexGrid1
    .Rows = 1
    .CellAlignment = 4
    .TextMatrix(0, 0) = "学号"
    .TextMatrix(0, 1) = "卡号"
    .TextMatrix(0, 2) = "日期"
    .TextMatrix(0, 3) = "时间"
    .TextMatrix(0, 4) = "金额"
    End With
    If mrc2.EOF = False Then
        mrc2.MoveFirst
    End If
     While Not mrc2.EOF
     With MSFlexGrid1
    .Rows = .Rows + 1
    .CellAlignment = 4
    .TextMatrix(.Rows - 1, 0) = Trim(mrc2.Fields(1))
    .TextMatrix(.Rows - 1, 1) = Trim(mrc2.Fields(0))
    .TextMatrix(.Rows - 1, 2) = Trim(mrc2.Fields(12))
    .TextMatrix(.Rows - 1, 3) = Trim(mrc2.Fields(13))
     .TextMatrix(.Rows - 1, 4) = Trim(mrc2.Fields(7))
       mrc2.MoveNext
    End With
    Wend
    mrc2.Close
    '充值
 With MSFlexGrid2
                .CellAlignment = 4
                .Rows = 1
                .TextMatrix(0, 0) = "学号"
                .TextMatrix(0, 1) = "卡号"
                .TextMatrix(0, 2) = "充值金额"
                .TextMatrix(0, 3) = "日期"
                .TextMatrix(0, 4) = "时间"
            End With
 '将recharge_info数据同步到表
            txtsql = "select * from recharge_Info where UserID='" & Trim(cmbuserid.Text) & "' and Status='未结账'and date = '" & a & "'"
'
            Set mrc3 = ExecuteSQL(txtsql, MsgText)
             If mrc3.EOF = False Then
                mrc3.MoveFirst
             End If
             While mrc3.EOF = False
                With MSFlexGrid2
                    .CellAlignment = 4
                    .Rows = .Rows + 1
                    .TextMatrix(.Rows - 1, 0) = Trim(mrc3.Fields(1))
                    .TextMatrix(.Rows - 1, 1) = Trim(mrc3.Fields(2))
                    .TextMatrix(.Rows - 1, 2) = Trim(mrc3.Fields(3))
                    .TextMatrix(.Rows - 1, 3) = Trim(mrc3.Fields(4))
                    .TextMatrix(.Rows - 1, 4) = Trim(mrc3.Fields(5))
                    mrc3.MoveNext
                End With
               Wend
               mrc3.Close
 '退卡
    MSFlexGrid3.Clear
    txtsql = "select * from CancelCard_Info where UserID='" &      Trim(cmbuserid.Text) & "'and status='δ????'and date = '" & a & "'"
    Set mrc4 = ExecuteSQL(txtsql, MsgText)
    With MSFlexGrid3
    .Rows = 1
    .CellAlignment = 4
    .TextMatrix(0, 0) = "学号"
    .TextMatrix(0, 1) = "卡号"
    .TextMatrix(0, 2) = "日期"
    .TextMatrix(0, 3) = "时间"
    .TextMatrix(0, 4) = "退卡金额"
    End With
    If mrc4.EOF = False Then
        mrc4.MoveFirst
    End If
    While Not mrc4.EOF
    With MSFlexGrid3
    .Rows = .Rows + 1
    .CellAlignment = 4
    .TextMatrix(.Rows - 1, 0) = Trim(mrc4.Fields(0))
    .TextMatrix(.Rows - 1, 1) = Trim(mrc4.Fields(1))
    .TextMatrix(.Rows - 1, 2) = Trim(mrc4.Fields(3))
    .TextMatrix(.Rows - 1, 3) = Trim(mrc4.Fields(4))
    .TextMatrix(.Rows - 1, 4) = Trim(mrc4.Fields(2))
    mrc4.MoveNext
    End With
    Wend
    mrc4.Close
'临时用户
MSFlexGrid4.Clear
   txtsql = "select * from student_Info where UserID='" & Trim(cmbuserid.Text) & "'and type='??????'and ischeck='δ????'and date = '" & a & "'"
   Set mrc5 = ExecuteSQL(txtsql, MsgText)
    With MSFlexGrid4
    .Rows = 1
    .CellAlignment = 4
    .TextMatrix(0, 0) = "学号"
    .TextMatrix(0, 1) = "卡号"
    .TextMatrix(0, 2) = "日期"
    .TextMatrix(0, 3) = "时间"
    .TextMatrix(0, 4) = "金额"
    End With
     If mrc5.EOF = False Then
            mrc5.MoveFirst
     End If
 While Not mrc5.EOF
      With MSFlexGrid4
    .Rows = .Rows + 1
    .CellAlignment = 4
    .TextMatrix(.Rows - 1, 0) = Trim(mrc5.Fields(1))
    .TextMatrix(.Rows - 1, 1) = Trim(mrc5.Fields(0))
    .TextMatrix(.Rows - 1, 2) = Trim(mrc5.Fields(12))
    .TextMatrix(.Rows - 1, 3) = Trim(mrc5.Fields(13))
    .TextMatrix(.Rows - 1, 4) = Trim(mrc5.Fields(7))
    mrc5.MoveNext
    End With
 Wend
 mrc5.Close
'汇总
 For i = 1 To MSFlexGrid3.Rows - 1                         '退卡金额累加
                 s = s + Val(MSFlexGrid3.TextMatrix(i, 4))
             Next
             For i = 1 To MSFlexGrid2.Rows - 1                         '充值金额累加
                 s1 = s1 + Val(MSFlexGrid2.TextMatrix(i, 2))
             Next
             For i = 1 To MSFlexGrid4.Rows - 1                          '临时收费金额累加
                 s2 = s2 + Val(MSFlexGrid4.TextMatrix(i, 3))
             Next
             Text1.Text = MSFlexGrid1.Rows - 1 + MSFlexGrid4.Rows - 1    '售卡张数=购卡+临时用户选项卡记录总数
             Text2.Text = Val(s)                                '退卡金额
             Text3.Text = MSFlexGrid3.Rows - 1                 '退卡张数
             Text4.Text = Val(Text1.Text) - Val(Text3.Text)     '总售卡数=售卡张数-退卡张数
             Text5.Text = Val(s1)                               '充值金额
             Text6.Text = Val(s1) - Val(s)                      '应收金额=充值金额-退卡金额
             Text7.Text = Val(s2)        '临时收费金额
end   sub
'第五个选项卡
Private Sub SSTab1_Click(PreviousTab As Integer)
  Select Case SSTab1.Tab
      Case 5     '退出
      Unload Me
   End Select
End Sub