frmcashie.frm

vb+sql 用于公交点钞结算和报表结合的相关软件

   End
   Begin MSComCtl2.DTPicker dtpDate 
      Height          =   315
      Left            =   2130
      TabIndex        =   5
      Top             =   6360
      Width           =   1605
      _ExtentX        =   2831
      _ExtentY        =   556
      _Version        =   393216
      Enabled         =   0   'False
      CustomFormat    =   "yyyy年MM月dd日"
      Format          =   24576003
      CurrentDate     =   37070
   End
   Begin VB.Shape Shape1 
      BackStyle       =   1  'Opaque
      BorderColor     =   &H80000006&
      FillColor       =   &H00C0C0C0&
      FillStyle       =   0  'Solid
      Height          =   510
      Index           =   3
      Left            =   60
      Top             =   7410
      Width           =   9735
   End
   Begin VB.Label lbltotal 
      ForeColor       =   &H00C00000&
      Height          =   285
      Left            =   7080
      TabIndex        =   10
      Top             =   6390
      Width           =   2925
   End
   Begin VB.Label Label2 
      AutoSize        =   -1  'True
      Caption         =   "点钞员："
      Height          =   180
      Index           =   1
      Left            =   5340
      TabIndex        =   7
      Top             =   6420
      Width           =   720
   End
   Begin VB.Label Label1 
      AutoSize        =   -1  'True
      Caption         =   "浏览日期："
      Height          =   180
      Left            =   1230
      TabIndex        =   6
      Top             =   6420
      Width           =   900
   End
   Begin VB.Line Line1 
      X1              =   0
      X2              =   9150
      Y1              =   6810
      Y2              =   6810
   End
End
Attribute VB_Name = "frmCashIE"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Dim totalnum As Integer
Private Sub chkCountBill_Click()
    If chkCountBill.Value = 1 Then
        txtDcOPNo.Enabled = True
        lbltotal.Visible = True
    Else
        txtDcOPNo.Enabled = False
        lbltotal.Visible = False
    End If
End Sub

Private Sub chkDate_Click()
    If chkDate.Value = 1 Then
        dtpDate.Enabled = True
    Else
        dtpDate.Enabled = False
    End If
End Sub

Private Sub cmdExit_Click()
    Unload Me
End Sub

Private Sub cmdOK_Click()
    Dim recSur As String
    Dim iNum As Integer
    Dim dMoney As Double
    Dim sumbill(12) As Integer
    On Error Resume Next
    Unload DatGrdDisPlay(1)
    Load DatGrdDisPlay(1)
    DatGrdDisPlay(1).Visible = True
    DatGrdDisPlay(1).Visible = False
    Set DatGrdDisPlay(1).DataSource = adodcRec
    adodcRec.ConnectionString = strCollection
    If chkCountBill.Value = 1 And chkDate.Value = 1 Then
        recSur = "SELECT DRIVEROPNO 司机工号, DRIVER_NAME 司机姓名, " + _
        "DEPT_NAME '司  别', LINE_NO 线路号,BUS_NO 车辆号,  DAY_TOTALMONEY 日收入, DAY_TOTALSUM 日张数,SXW 上下午," + _
        " TRADEDATE 投币日期, COUNTBILLOPNO 点钞员号, CHECKOPNO 复核员号, M100,M50,M20,M10,M5,M2,M1,M05," + _
        "M02, M01, M005,M002,M001" + _
        " FROM ZY_INPUT_VIEW where convert(char(10),tradedate,112)='" + Format(dtpDate.Value, "yyyy") + Format(dtpDate.Value, "MM") + Format(dtpDate.Value, "dd") + "'and countbillopno='" + txtDcOPNo + "' "
        If optDri.Value = True Then
            recSur = recSur + "order by driveropno,dept_no,line_no, bus_no"
        ElseIf optBus.Value = True Then
            recSur = recSur + "order by bus_no,dept_no,line_no"
        ElseIf optLine.Value = True Then
            recSur = recSur + "order by line_no,dept_no, bus_no"
        Else
            recSur = recSur + "order by countbillopno,dept_no,line_no, bus_no"
        End If
        adodcRec.RecordSource = recSur
        adodcRec.Refresh
    ElseIf chkCountBill.Value <> 1 And chkDate.Value <> 1 Then
        recSur = "SELECT DRIVEROPNO 司机工号, DRIVER_NAME 司机姓名, " + _
        "DEPT_NAME as '司  别', LINE_NO 线路号, BUS_NO 车辆号, DAY_TOTALMONEY 日收入, DAY_TOTALSUM 日张数,SXW 上下午, " + _
        "  TRADEDATE 投币日期,COUNTBILLOPNO 点钞员号, CHECKOPNO 复核员号, M100,M50,M20,M10,M5,M2,M1,M05," + _
        "M02, M01, M005,M002,M001" + _
        " FROM ZY_INPUT_VIEW where convert(char(10),tradedate,112)='" + Format(Now, "yyyy") + Format(Now, "MM") + Format(Now - 1, "dd") + "'"
        If optDri.Value = True Then
            recSur = recSur + "order by driveropno,dept_no,line_no, bus_no"
        ElseIf optBus.Value = True Then
            recSur = recSur + "order by bus_no,dept_no,line_no"
        ElseIf optLine.Value = True Then
            recSur = recSur + "order by line_no,dept_no, bus_no"
        Else
            recSur = recSur + "order by countbillopno,dept_no,line_no, bus_no"
        End If
        adodcRec.RecordSource = recSur
        adodcRec.Refresh
    ElseIf chkCountBill.Value <> 1 And chkDate.Value = 1 Then
        recSur = "SELECT DRIVEROPNO 司机工号, DRIVER_NAME 司机姓名, " + _
        "DEPT_NAME '司  别', LINE_NO 线路号, BUS_NO 车辆号, DAY_TOTALMONEY 日收入, DAY_TOTALSUM 日张数,SXW 上下午," + _
        "  TRADEDATE 投币日期, COUNTBILLOPNO 点钞员号, CHECKOPNO 复核员号, M100,M50,M20,M10,M5,M2,M1,M05," + _
        "M02, M01, M005,M002,M001" + _
        " FROM ZY_INPUT_VIEW where convert(char(10),tradedate,112)='" + Format(dtpDate.Value, "yyyy") + Format(dtpDate.Value, "MM") + Format(dtpDate.Value, "dd") + "' "
                If optDri.Value = True Then
            recSur = recSur + "order by driveropno,dept_no,line_no, bus_no"
        ElseIf optBus.Value = True Then
            recSur = recSur + "order by bus_no,dept_no,line_no"
        ElseIf optLine.Value = True Then
            recSur = recSur + "order by line_no,dept_no, bus_no"
        Else
            recSur = recSur + "order by countbillopno,dept_no,line_no, bus_no"
        End If
        adodcRec.RecordSource = recSur
        adodcRec.Refresh
    ElseIf chkCountBill.Value = 1 And chkDate.Value <> 1 Then
       recSur = "SELECT DRIVEROPNO 司机工号, DRIVER_NAME 司机姓名, " + _
        "DEPT_NAME '司  别',  LINE_NO 线路号, BUS_NO 车辆号, DAY_TOTALMONEY 日收入, DAY_TOTALSUM 日张数,SXW 上下午, " + _
        " TRADEDATE 投币日期, COUNTBILLOPNO 点钞员号, CHECKOPNO 复核员号, M100,M50,M20,M10,M5,M2,M1,M05," + _
        "M02, M01, M005,M002,M001" + _
        " FROM ZY_INPUT_VIEW where convert(char(10),tradedate,112)='" + Format(Now, "yyyy") + Format(Now, "MM") + Format(Now - 1, "dd") + "'and countbillopno='" + txtDcOPNo + "' "
        
        If optDri.Value = True Then
            recSur = recSur + "order by driveropno,dept_no,line_no, bus_no"
        ElseIf optBus.Value = True Then
            recSur = recSur + "order by bus_no,dept_no,line_no"
        ElseIf optLine.Value = True Then
            recSur = recSur + "order by line_no,dept_no, bus_no"
        Else
            recSur = recSur + "order by countbillopno,dept_no,line_no, bus_no"
        End If
        adodcRec.RecordSource = recSur
        adodcRec.Refresh
    Else
        DatGrdDisPlay(1).Visible = True
        MsgBox "没有符合条件的记录!", vbInformation, "提示..."
        Exit Sub
    End If
    With DatGrdDisPlay(1)
    
        .Columns(0).Width = 1000
        .Columns(1).Width = 1000
        .Columns(2).Width = 1000
        .Columns(3).Width = 800
        .Columns(4).Width = 800
        .Columns(5).Width = 800
        .Columns(6).Width = 800
        .Columns(7).Width = 800
        .Columns(8).Width = 1000
        .Columns(9).Width = 700
        .Columns(10).Width = 1250
        .Columns(11).Width = 600
        .Columns(12).Width = 600
        .Columns(13).Width = 600
        .Columns(14).Width = 600
        .Columns(15).Width = 600
        .Columns(16).Width = 600
        .Columns(17).Width = 600
        .Columns(18).Width = 600
        .Columns(19).Width = 600
        .Columns(20).Width = 600
        .Columns(21).Width = 600
        .Columns(22).Width = 600
        .Columns(23).Width = 600
    End With
    iNum = 0
    dMoney = 0
    For i = 0 To adodcRec.Recordset.RecordCount - 1
        'DatGrdDisPlay(1).Row = i
        'DatGrdDisPlay(1).Col = 7
        iNum = iNum + adodcRec.Recordset.Fields(6) 'DatGrdDisPlay(1).Text
        DatGrdDisPlay(1).Col = 6
        dMoney = dMoney + adodcRec.Recordset.Fields(5) 'DatGrdDisPlay(1).Text
        sumbill(0) = sumbill(0) + adodcRec.Recordset.Fields(11)
        
        sumbill(1) = sumbill(1) + adodcRec.Recordset.Fields(12)
        sumbill(2) = sumbill(2) + adodcRec.Recordset.Fields(13)
        sumbill(3) = sumbill(3) + adodcRec.Recordset.Fields(14)
        sumbill(4) = sumbill(4) + adodcRec.Recordset.Fields(15)
        sumbill(5) = sumbill(5) + adodcRec.Recordset.Fields(16)
        sumbill(6) = sumbill(6) + adodcRec.Recordset.Fields(17)
        sumbill(7) = sumbill(7) + adodcRec.Recordset.Fields(18)
        sumbill(8) = sumbill(8) + adodcRec.Recordset.Fields(19)
        sumbill(9) = sumbill(9) + adodcRec.Recordset.Fields(20)
        sumbill(10) = sumbill(10) + adodcRec.Recordset.Fields(21)
        sumbill(11) = sumbill(11) + adodcRec.Recordset.Fields(22)
        sumbill(12) = sumbill(12) + adodcRec.Recordset.Fields(23)
        adodcRec.Recordset.MoveNext
    Next
    txtDisplay.Text = addSpace("100") + addSpace("50") + addSpace("20") + addSpace("10") + addSpace("5") + addSpace("2") + addSpace("1") + addSpace("0.5") + _
                        addSpace("0.2") + addSpace("0.1") + addSpace("0.05") + addSpace("0.02") + addSpace("0.01") + _
                         vbCrLf + addSpace(CStr(sumbill(0))) + addSpace(CStr(sumbill(1))) + addSpace(CStr(sumbill(2))) + addSpace(CStr(sumbill(3))) + addSpace(CStr(sumbill(4))) + addSpace(CStr(sumbill(5))) + addSpace(CStr(sumbill(6))) + addSpace(CStr(sumbill(7))) + addSpace(CStr(sumbill(8))) + addSpace(CStr(sumbill(9))) _
                         + addSpace(CStr(sumbill(10))) + addSpace(CStr(sumbill(11))) + addSpace(CStr(sumbill(12)))
    lbltotal.Caption = "张数:" + CStr(iNum) + "张, 金额:" + CStr(dMoney) + "元"
    totalnum = adodcRec.Recordset.RecordCount
    DatGrdDisPlay(1).Caption = "总共 " + CStr(totalnum) + " 条记录:    当前为第 " + CStr(DatGrdDisPlay(1).Row + 1) + " 条记录"
    DatGrdDisPlay(1).Refresh
    DatGrdDisPlay(1).Visible = True
End Sub

Private Sub DatGrdDisPlay_MouseUp(Index As Integer, Button As Integer, Shift As Integer, X As Single, Y As Single)
    DatGrdDisPlay(1).Refresh
    DatGrdDisPlay(1).Caption = "总共 " + CStr(totalnum) + " 条记录:    当前为第 " + CStr(DatGrdDisPlay(1).Row + 1) + " 条记录"

End Sub

Private Sub DatGrdDisPlay_RowColChange(Index As Integer, LastRow As Variant, ByVal LastCol As Integer)
    DatGrdDisPlay(1).Caption = "总共 " + CStr(totalnum) + " 条记录:    当前为第 " + CStr(DatGrdDisPlay(1).Row + 1) + " 条记录"
End Sub

Private Sub Form_Load()
    dtpDate.Value = Now - 1
End Sub

Private Sub txtDcOPNo_Change()
    If Not IsNumeric(txtDcOPNo) Then
        txtDcOPNo = 0
    End If
End Sub

Private Sub txtDcOPNo_GotFocus()
    txtDcOPNo.SelLength = Len(txtDcOPNo)
End Sub

Private Sub txtDcOPNo_LostFocus()
'    If txtDcName = "" Then
'        MsgBox "无此点钞员工号，请检查重输!", vbExclamation, "提示..."
'        Exit Sub
'    End If
End Sub

Private Function addSpace(aa As String) As String
    Dim iLenStr As Integer
    iLenStr = Len(aa)
        Select Case iLenStr
            Case 1
                addSpace = "     " + aa
            Case 2
                addSpace = "    " + aa
            Case 3
                addSpace = "   " + aa
            Case 4
                addSpace = "  " + aa
            Case 5
                addSpace = " " + aa
            Case Else
                addSpace = aa
        End Select

End Function