form1.frm

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

VERSION 5.00
Object = "{86CF1D34-0C5F-11D2-A9FC-0000F8754DA1}#2.0#0"; "MSCOMCT2.OCX"
Begin VB.Form frmReport 
   BorderStyle     =   1  'Fixed Single
   Caption         =   "线路投币日收入报表"
   ClientHeight    =   1830
   ClientLeft      =   45
   ClientTop       =   330
   ClientWidth     =   5895
   Icon            =   "Form1.frx":0000
   LockControls    =   -1  'True
   MDIChild        =   -1  'True
   MinButton       =   0   'False
   ScaleHeight     =   1830
   ScaleWidth      =   5895
   Begin VB.ComboBox cmbCompany 
      Height          =   300
      Left            =   3840
      Style           =   2  'Dropdown List
      TabIndex        =   6
      Top             =   300
      Width           =   1545
   End
   Begin VB.CommandButton cmdExit 
      Caption         =   "退  出[&O]"
      Height          =   345
      Left            =   4140
      TabIndex        =   5
      Top             =   1080
      Width           =   1335
   End
   Begin VB.CommandButton cmdOutput 
      Caption         =   "输  出[&O]"
      Height          =   345
      Left            =   2760
      TabIndex        =   4
      Top             =   1080
      Width           =   1335
   End
   Begin VB.TextBox txtWeather 
      Height          =   285
      Left            =   3840
      TabIndex        =   3
      Top             =   300
      Visible         =   0   'False
      Width           =   1515
   End
   Begin MSComCtl2.DTPicker DTPicker1 
      Height          =   285
      Left            =   1350
      TabIndex        =   0
      Top             =   300
      Width           =   1635
      _ExtentX        =   2884
      _ExtentY        =   503
      _Version        =   393216
      CustomFormat    =   "yyyy年MM月dd日"
      Format          =   24576003
      CurrentDate     =   37114
   End
   Begin VB.Label Label2 
      AutoSize        =   -1  'True
      Caption         =   "统计日期："
      Height          =   180
      Left            =   420
      TabIndex        =   2
      Top             =   360
      Width           =   900
   End
   Begin VB.Label Label1 
      AutoSize        =   -1  'True
      Caption         =   "单位："
      Height          =   180
      Left            =   3210
      TabIndex        =   1
      Top             =   360
      Width           =   540
   End
   Begin VB.Line Line2 
      BorderColor     =   &H80000005&
      X1              =   390
      X2              =   5580
      Y1              =   1020
      Y2              =   1020
   End
   Begin VB.Line Line1 
      BorderColor     =   &H80000003&
      BorderWidth     =   2
      X1              =   390
      X2              =   5580
      Y1              =   1020
      Y2              =   1020
   End
End
Attribute VB_Name = "frmReport"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False

Private Sub cmdExit_Click()
    Unload Me
End Sub

Private Sub cmdOutput_Click()
    Dim myoutput As New ReportToExcel.mClass
    Dim MYPATH As String
    Dim i As Integer
    Dim rs As New ADODB.Recordset
    Dim rs1 As New ADODB.Recordset
'    Dim sWeather As String
'    Dim sWeek As String
    Dim sTableTitle As String
    Dim INPUTDATE As String
'    Dim STARTDATE As String
    Dim DEPTNO As String
    Dim ConOp_no As String
    Dim daysum As Double
    Dim daymoney As Double
'    Dim tdaysum As Double
'    Dim tdaymoney As Double

'    Dim monmoney As Double
'    INPUTDATE = Format(DTPicker1.Value, "yyyy") + Format(DTPicker1.Value, "MM") + Format(DTPicker1.Value, "dd")
'    If CInt(Mid(INPUTDATE, 7, 2)) < 26 Then
'        STARTDATE = CStr(CDbl(Left(INPUTDATE, 6)) - 1) + "26"
'    Else
'        STARTDATE = CStr(Left(INPUTDATE, 6)) + "26"
'    End If
'    Select Case Mid(Format(DTPicker1.Value, "yyyyMMddd"), 7, 3)
'        Case "Mon"
'            sWeek = "星期：一"
'        Case "Tue"
'            sWeek = "星期：二"
'        Case "Wen"
'            sWeek = "星期：三"
'        Case "Thu"
'            sWeek = "星期：四"
'        Case "Fri"
'            sWeek = "星期：五"
'        Case "Sta"
'            sWeek = "星期：六"
'        Case "Sun"
'            sWeek = "星期：日"
'    End Select
'    INPUTDATE = Format(DTPicker1.Value, "yyyyMMDD")
'    sTableTitle = "数据日期:" + Format(DTPicker1.Value, "yyyy年MM月dd日") + "          " + sWeek
'    Set rs = New ADODB.Recordset
'    rs.CursorLocation = adUseClient
'    rs.Open "EXEC ZYSP_DEPT_COUNT '" + INPUTDATE + "', '" + DEPTNO + "' ", cnn, adOpenStatic, adLockOptimistic
'    If Not rs.EOF Then
'        MYPATH = CStr(App.Path) + "\MONEY.xlt" '设置模版路径
'        myoutput.StartModel MYPATH, 3
'        myoutput.Addcell 2, 1, sTableTitle
'        i = 4
'        DEPTNO = rs.Fields(0)
'        Do While Not rs.EOF
'            If DEPTNO <> rs.Fields(0) Then
'                myoutput.Addcell i, 1, "合 计"
'                myoutput.Addcell i, 3, CStr(daymoney)
'                myoutput.Addcell i, 4, CStr(monmoney)
'                i = i + 1
'
'                tdaymoney = tdaymoney + daymoney
'                tmonmoney = tmonmoney + monmoney
'                daymoney = 0
'                monmoney = 0
'                DEPTNO = rs.Fields(0)
'            End If
'            daymoney = daymoney + rs.Fields(2)
'            monmoney = monmoney + rs.Fields(3)
'            myoutput.Addcell i, 1, rs.Fields(0)
'            myoutput.Addcell i, 2, rs.Fields(1)
'            myoutput.Addcell i, 3, rs.Fields(2)
'            myoutput.Addcell i, 4, rs.Fields(3)
'            rs.MoveNext
'            i = i + 1
'        Loop
'        tdaymoney = tdaymoney + daymoney
'        tmonmoney = tmonmoney + monmoney
'
'        myoutput.Addcell i, 1, "合 计"
'        myoutput.Addcell i, 3, CStr(daymoney)
'        myoutput.Addcell i, 4, CStr(monmoney)
'
'        myoutput.Addcell i + 1, 1, "总 计"
'        myoutput.Addcell i + 1, 3, CStr(tdaymoney)
'        myoutput.Addcell i + 1, 4, CStr(tmonmoney)
'        myoutput.Addcell i + 2, 1, "制表单位:收银中心          制表人:  " + Trim(cCheckName) + "       制表日期:" + Format(DTPicker1.Value, "yyyy年MM月dd日")
'        myoutput.unitecell "A" + CStr(i + 2), "D" + CStr(i + 2)
'        rs.Close
'    End If
    INPUTDATE = Format(DTPicker1.Value, "yyyyMMDD")
    sTableTitle = "单位:" + Trim(cmbCompany.Text) + "          " + "数据日期:" + Format(DTPicker1.Value, "yyyy年MM月dd日")
    MYPATH = CStr(App.Path) + "\MONEY.xlt" '设置模版路径
    
    Select Case ViewFlag
    Case 1
        i = 4
        myoutput.StartModel MYPATH, 3
        myoutput.Addcell 2, 1, sTableTitle
        
        'Set rs = New ADODB.Recordset
        rs.Open "select dept_no from zz_bus_ic.dbo.zy_dept_info where name='" + Trim(cmbCompany.Text) + "'", cnn, adOpenStatic, adLockOptimistic
        DEPTNO = rs(0)
        rs.Close
        rs.CursorLocation = adUseClient
        rs.Open "EXEC ZYSP_DEPT_COUNT '" + INPUTDATE + "', '" + DEPTNO + "' ", cnn, adOpenStatic, adLockOptimistic
        
        Do While Not rs.EOF
            myoutput.Addcell i, 1, rs(0)
            myoutput.Addcell i, 2, rs(1)
            myoutput.Addcell i, 3, rs(2)
            myoutput.Addcell i, 4, Trim(rs(3))
            myoutput.Addcell i, 5, rs(4)
    '        myoutput.Addcell i, 6, rs(5)
    '        myoutput.Addcell i, 7, rs(6)
            myoutput.Addcell i, 8, rs(5)
            myoutput.Addcell i, 9, rs(6)
            daysum = daysum + rs(5)
            daymoney = daymoney + rs(6)
            i = i + 1
            rs.MoveNext
        Loop
        myoutput.Addcell i, 1, "合计"
        myoutput.Addcell i, 8, CStr(daysum)
        myoutput.Addcell i, 9, CStr(daymoney)
    Case 2
        i = 4
        myoutput.StartModel MYPATH, 3
        myoutput.Addcell 2, 1, sTableTitle
        
        'Set rs = New ADODB.Recordset
        rs.Open "select dept_no from zz_bus_ic.dbo.zy_dept_info where name='" + Trim(cmbCompany.Text) + "'", cnn, adOpenStatic, adLockOptimistic
        DEPTNO = rs(0)
        rs.Close
        rs.CursorLocation = adUseClient
        rs.Open "EXEC ZYSP_DEPT_ticket_COUNT '" + INPUTDATE + "', '" + DEPTNO + "' ", cnn, adOpenStatic, adLockOptimistic
        
        Do While Not rs.EOF
            myoutput.Addcell i, 1, rs(0)
            myoutput.Addcell i, 2, rs(1)
            myoutput.Addcell i, 3, rs(2)
            myoutput.Addcell i, 4, Trim(rs(3))
            myoutput.Addcell i, 5, rs(4)
            myoutput.Addcell i, 6, rs(5)
            rs1.Open "select name from zz_bus_ic.dbo.zy_worker_info where op_no=" + CStr(rs(5)), cnn, adOpenStatic, adLockOptimistic
            If Not rs1.EOF Then
                myoutput.Addcell i, 7, rs1(0)
            End If
            rs1.Close
            myoutput.Addcell i, 8, rs(6)
            myoutput.Addcell i, 9, rs(7)
            daysum = daysum + rs(6)
            daymoney = daymoney + rs(7)
            i = i + 1
            rs.MoveNext
        Loop
        myoutput.Addcell i, 1, "合计"
        myoutput.Addcell i, 8, CStr(daysum)
        myoutput.Addcell i, 9, CStr(daymoney)
        
    Case 3
        i = 4
        sTableTitle = "数据日期:" + Format(DTPicker1.Value, "yyyy年MM月dd日")
        myoutput.StartModel MYPATH, 4
        myoutput.Addcell 2, 1, sTableTitle
        
        'Set rs = New ADODB.Recordset
        rs.CursorLocation = adUseClient
        rs.Open "EXEC ZYSP_bus_check '" + INPUTDATE + "'", cnn, adOpenStatic, adLockOptimistic
        CheckFlag = rs.RecordCount
        Do While Not rs.EOF
            myoutput.Addcell i, 1, rs(1)
            myoutput.Addcell i, 2, rs(2)
            myoutput.Addcell i, 3, rs(3)
            myoutput.Addcell i, 4, rs(4)
            i = i + 1
            rs.MoveNext
        Loop
        
    End Select
    rs.Close
    Set rs = Nothing
End Sub

Private Sub Form_Load()
    Dim rs As New ADODB.Recordset
    Set rs = New ADODB.Recordset
    rs.Open "select * from zz_bus_ic.dbo.zy_dept_info where dept_no<80 and is_trans=1", cnn, adOpenStatic, adLockOptimistic
    
    Do While Not rs.EOF
        cmbCompany.AddItem rs.Fields(1).Value
        rs.MoveNext
    Loop
    rs.Close
    cmbCompany.ListIndex = 0
    DTPicker1.Value = Now - 1
End Sub