formbal.frm

运输管理系统

VERSION 5.00
Begin VB.Form FormBal 
   BorderStyle     =   1  'Fixed Single
   Caption         =   "每日结算"
   ClientHeight    =   3285
   ClientLeft      =   5610
   ClientTop       =   5085
   ClientWidth     =   4095
   Icon            =   "FormBal.frx":0000
   LinkTopic       =   "Form1"
   MaxButton       =   0   'False
   MDIChild        =   -1  'True
   ScaleHeight     =   3285
   ScaleWidth      =   4095
   Begin VB.CommandButton CmdOK 
      Caption         =   "确定"
      Default         =   -1  'True
      Height          =   495
      Left            =   2640
      TabIndex        =   8
      Top             =   2640
      Width           =   1215
   End
   Begin VB.TextBox TxtPayoff 
      Height          =   375
      Left            =   1800
      TabIndex        =   6
      Top             =   2040
      Width           =   2055
   End
   Begin VB.TextBox TxtEarning 
      Height          =   375
      Left            =   1800
      TabIndex        =   4
      Top             =   1440
      Width           =   2055
   End
   Begin VB.TextBox TxtPay 
      Height          =   375
      Left            =   1800
      TabIndex        =   3
      Top             =   840
      Width           =   2055
   End
   Begin VB.TextBox TxtDate 
      Height          =   375
      Left            =   1800
      TabIndex        =   1
      Top             =   240
      Width           =   2055
   End
   Begin VB.Label Label4 
      Caption         =   "盈利："
      Height          =   495
      Left            =   480
      TabIndex        =   7
      Top             =   2160
      Width           =   1215
   End
   Begin VB.Label Label3 
      Caption         =   "昨天总收入："
      Height          =   495
      Left            =   480
      TabIndex        =   5
      Top             =   1560
      Width           =   1215
   End
   Begin VB.Label Label2 
      Caption         =   "昨天总支出："
      Height          =   375
      Left            =   480
      TabIndex        =   2
      Top             =   960
      Width           =   1095
   End
   Begin VB.Label Label1 
      Caption         =   "今天是："
      Height          =   255
      Left            =   480
      TabIndex        =   0
      Top             =   360
      Width           =   855
   End
End
Attribute VB_Name = "FormBal"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit

'确定按钮，退出本界面
Private Sub CmdOK_Click()
    Unload Me
End Sub

Private Sub Form_Load()
Dim SqlStr As String
Dim LastDay As Date
Dim RsDB As New ADODB.Recordset
Dim sEarning As Single
Dim sPay As Single
Dim i As Integer

    '显示日期
    Me.TxtDate.Text = Format(Now, "yyyy-mm-dd")
    LastDay = DateAdd("d", -1, Now)
    
    '计算盈利:昨日收入(车辆运营)-昨日支出(违章罚款+维修费用+事故花费)
    '1. 车辆运营收入
    '从WorkRec记录表中查找所有运营日期为昨日的记录，并计算运营收入总和
    SqlStr = "select WorkEarning from WorkRec where WorkDate =#"
    SqlStr = SqlStr & Format(LastDay, "yyyy-mm-dd") & "#"
    Debug.Print SqlStr
    RsDB.Open SqlStr, DBCn, adOpenStatic, adLockReadOnly, -1
    
    If RsDB.RecordCount > 0 Then RsDB.MoveFirst
    For i = 1 To RsDB.RecordCount
        sEarning = sEarning + RsDB.Fields("WorkEarning").Value
        If Not RsDB.EOF Then RsDB.MoveNext
    Next i
    RsDB.Close
    '显示总收入
    Me.TxtEarning.Text = sEarning
    
    '2. -车辆违章罚款
    '从PecRec记录表中查找所有违章日期为昨日的记录，并计算违章罚款总和
    SqlStr = "select PecCost from PecRec where PecDate =#"
    SqlStr = SqlStr & Format(LastDay, "yyyy-mm-dd") & "#"
    Debug.Print SqlStr
    RsDB.Open SqlStr, DBCn, adOpenStatic, adLockReadOnly, -1
    
    If RsDB.RecordCount > 0 Then RsDB.MoveFirst
    For i = 1 To RsDB.RecordCount
        sPay = sPay + RsDB.Fields("PecCost").Value
        If Not RsDB.EOF Then RsDB.MoveNext
    Next i
    RsDB.Close
    
    '3. -车辆维修费用
    '从RepairRec记录表中查找所有维修日期为昨日的记录，并计算维修费用总和
    SqlStr = "select RepairPay from RepairRec where RepairDate =#"
    SqlStr = SqlStr & Format(LastDay, "yyyy-mm-dd") & "#"
    Debug.Print SqlStr
    RsDB.Open SqlStr, DBCn, adOpenStatic, adLockReadOnly, -1
    
    If RsDB.RecordCount > 0 Then RsDB.MoveFirst
    For i = 1 To RsDB.RecordCount
        sPay = sPay + RsDB.Fields("RepairPay").Value
        If Not RsDB.EOF Then RsDB.MoveNext
    Next i
    RsDB.Close
    
    '4. -车辆事故费用
    '从AccRec记录表中查找所有事故日期为昨日的记录，并计算事故费用总和
    SqlStr = "select AcciComPay from AccRec where AcciDate =#"
    SqlStr = SqlStr & Format(LastDay, "yyyy-mm-dd") & "#"
    Debug.Print SqlStr
    RsDB.Open SqlStr, DBCn, adOpenStatic, adLockReadOnly, -1
    
    If RsDB.RecordCount > 0 Then RsDB.MoveFirst
    For i = 1 To RsDB.RecordCount
        sPay = sPay + RsDB.Fields("AcciComPay").Value
        If Not RsDB.EOF Then RsDB.MoveNext
    Next i
    RsDB.Close
    
    '显示总支出和盈利
    Me.TxtPay.Text = sPay
    Me.TxtPayoff.Text = sEarning - sPay
    
End Sub