餐厅报表.frm

Hotel Prog in VB using mdb

'--------------------------------------------------------------
'本份代码仅提供给程序太平洋的所有朋友学习,研究之用.
'其它网站一律不得转载,否则为侵权行为,本人保留法律追诉权力.
'这也是本人最早的VB版程序,代码质量不好.望笑纳.:)
'--------------------------------------------------------------
'提供日期:2003-05-31    提供者:帅
'--------------------------------------------------------------
'系统提从与ACCESS或SQL相接,在登录时,选择全局数据库,就与SQL数据库
'连接,字符串存放在SERVER.DAT文本文件中;选择本地数据库,与本地ACCESS
'数据库相连,连接字符串存放在LOCAT.DAT文件中.(当前存放为e:\hotel2\room.mdb)
'---------------------------------------------------------------
'将ACCESS中所有表导入SQL中,并将有的表中的ID字段改为自动编码,就可以使用.
'---------------------------------------------------------------------
'
'
Dim DJ_query As Boolean
Dim DATA_edit As Boolean
Dim Field_Type As Integer
Dim Field_Value As String
Dim FindFieldC As String
Dim ZH_find As String
Dim ZH_find1 As String
Dim ZH_ysh As String
Dim SF_BG As Boolean
Dim BD_BG As Boolean
Dim Or_Field As String
Dim US_Field As String
Dim Data_Rec As Long
Dim TY_XG As Boolean
Dim FB_table1 As String

Private Sub Form_Load()
    Dim Total1(80) As Currency
    Dim Total2(80) As Long
    Dim i As Long
    
    For i = 0 To 19
        Total1(i) = 0
        Total2(i) = 0
    Next
    
    Left = 0
    Top = 0
    
    FindFieldC = "帐号"
    ZH_find = ""
    ZH_find1 = ""
    DJ_query = False
    ZH_ysh = " like "
    SF_BG = False
    BD_BG = False
    Or_Field = " 帐号"
    US_Field = ""
    Select Case Query_Type
    Case "餐厅报表"
        Me.Caption = "餐厅营业报表"
        Me.Label1.Caption = "餐厅营业报表"
        Me.Label2.Caption = "餐厅营业报表"
        Adodc1.ConnectionString = My_PROVIDER
        Adodc1.CommandType = adCmdText
        Adodc1.RecordSource = "select * from 餐厅营业表 where 发票号 like '999999' and 操作员 like '" & CzyName & "'"
        Adodc1.Refresh
        If Adodc1.Recordset.RecordCount > 0 Then
            While Not Adodc1.Recordset.EOF
                Adodc1.Recordset.Delete
                Adodc1.Recordset.Update
                Adodc1.Recordset.MoveNext
            Wend
        End If
        Adodc1.Recordset.Close
        Adodc1.ConnectionString = My_PROVIDER
        Adodc1.CommandType = adCmdText
        Adodc1.RecordSource = "select * from 餐厅营业表"
        Adodc1.Refresh
        FB_table2 = "  餐厅营业表 where 日期 like '" & System_Date & "'"
        US_Field = "   餐厅营业表 where 日期 like '" & System_Date & "'"
        FB_table1 = FB_table2

        If Adodc1.Recordset.RecordCount > 0 Then
            Adodc1.Recordset.MoveFirst
            While Not Adodc1.Recordset.EOF
                For i = 0 To Adodc1.Recordset.Fields.Count - 1
                    If Adodc1.Recordset.Fields(i).Type = adCurrency Then
                        Total1(i) = Total1(i) + Adodc1.Recordset.Fields(i)
                    End If
                Next
                Adodc1.Recordset.MoveNext
            Wend
            Adodc1.Recordset.AddNew
            For i = 0 To Adodc1.Recordset.Fields.Count - 1
                If Total1(i) <> 0 Then
                    Adodc1.Recordset.Fields(i) = Total1(i)
                End If
            Next
            Adodc1.Recordset.Fields("日期") = System_Date
            Adodc1.Recordset.Fields("发票号") = "999999"
            Adodc1.Recordset.Fields("房号桌号") = "合计"
            Adodc1.Recordset.Fields("操作员") = CzyName
            Adodc1.Recordset.Update
            
        End If
        Set DataGrid1.DataSource = Adodc1
        DataGrid1.ReBind
        DataGrid1.Refresh
'餐厅营业收入表
    Case "餐厅营业收入表"
        Me.Caption = "餐厅营业收入表"
        Me.Label1.Caption = "餐厅营业收入表"
        Me.Label2.Caption = "餐厅营业收入表"
        Adodc1.ConnectionString = My_PROVIDER
        Adodc1.CommandType = adCmdText
        Adodc1.RecordSource = "select * from 餐厅营业表 where 日期 like '" & CStr(CDate(System_Date) - 1) & "' and 发票号 like '999999'"
        Adodc1.Refresh
        If Adodc1.Recordset.RecordCount > 0 Then
            While Not Adodc1.Recordset.EOF
                Adodc1.Recordset.Delete
                Adodc1.Recordset.Update
                Adodc1.Recordset.MoveNext
            Wend
        End If

        Adodc1.ConnectionString = My_PROVIDER
        Adodc1.CommandType = adCmdText
        Adodc1.RecordSource = "select * from 餐厅营业表 where 日期 like '" & CStr(CDate(System_Date) - 1) & "' and 贷方<>0"
        Adodc1.Refresh
        
        FB_table2 = "  餐厅营业表 where 日期 like '" & CStr(CDate(System_Date) - 1) & "'"
        US_Field = "   餐厅营业表 where 日期 like '" & CStr(CDate(System_Date) - 1) & "'"
        FB_table1 = FB_table2
        If Adodc1.Recordset.RecordCount > 0 Then
            Adodc1.Recordset.MoveFirst
            While Not Adodc1.Recordset.EOF
                For i = 0 To Adodc1.Recordset.Fields.Count - 1
                    If Adodc1.Recordset.Fields(i).Type = adCurrency Then
                        Total1(i) = Total1(i) + Adodc1.Recordset.Fields(i)
                    End If
                Next
                Adodc1.Recordset.MoveNext
            Wend
            Adodc1.Recordset.AddNew
            For i = 0 To Adodc1.Recordset.Fields.Count - 1
                If Total1(i) <> 0 Then
                    Adodc1.Recordset.Fields(i) = Total1(i)
                End If
            Next
            Adodc1.Recordset.Fields("发票号") = "999999"
            Adodc1.Recordset.Fields("房号桌号") = "合计"
            Adodc1.Recordset.Update
        End If
        Set DataGrid1.DataSource = Adodc1
        DataGrid1.ReBind
        DataGrid1.Refresh

    Case "当日单位帐户帐务报表"
        Me.Caption = "当日单位帐户帐务报表"
        Me.Label1.Caption = "当日单位帐户帐务报表"
        Me.Label2.Caption = "当日单位帐户帐务报表"
        Adodc1.ConnectionString = My_PROVIDER
        Adodc1.CommandType = adCmdText
        Adodc1.RecordSource = "select * from 往来帐户挂帐记录 where 日期 like '" & System_Date & "' and 帐号 like '999999' and 操作员 like '" & CzyName & "'"
        Adodc1.Refresh
        If Adodc1.Recordset.RecordCount > 0 Then
            While Not Adodc1.Recordset.EOF
                Adodc1.Recordset.Delete
                Adodc1.Recordset.Update
                Adodc1.Recordset.MoveNext
            Wend
        End If

        Adodc1.ConnectionString = My_PROVIDER
        Adodc1.CommandType = adCmdText
        Adodc1.RecordSource = "select * from 往来帐户挂帐记录 where 日期 like '" & System_Date & "'"
        Adodc1.Refresh
        
        FB_table2 = "  往来帐户挂帐记录 where 日期 like '" & System_Date & "'"
        US_Field = "   往来帐户挂帐记录 where 日期 like '" & System_Date & "'"
        FB_table1 = FB_table2
        If Adodc1.Recordset.RecordCount > 0 Then
            Adodc1.Recordset.MoveFirst
            While Not Adodc1.Recordset.EOF
                For i = 0 To Adodc1.Recordset.Fields.Count - 1
                    If Adodc1.Recordset.Fields(i).Type = adCurrency Then
                        Total1(i) = Total1(i) + Adodc1.Recordset.Fields(i)
                    End If
                Next
                Adodc1.Recordset.MoveNext
            Wend
            Adodc1.Recordset.AddNew
            For i = 0 To Adodc1.Recordset.Fields.Count - 1
                If Total1(i) <> 0 Then
                    Adodc1.Recordset.Fields(i) = Total1(i)
                End If
            Next
            Adodc1.Recordset.Fields("日期") = System_Date
            Adodc1.Recordset.Fields("帐号") = "999999"
            Adodc1.Recordset.Fields("房号桌号") = "合计"
            Adodc1.Recordset.Fields("操作员") = CzyName
            Adodc1.Recordset.Update
        End If
        Set DataGrid1.DataSource = Adodc1
        DataGrid1.ReBind
        DataGrid1.Refresh
'日营业收入报表
    Case "日营业收入报表"
        Me.Caption = CStr(CDate(System_Date) - 1) & "日营业收入报表"
        Me.Label1.Caption = CStr(CDate(System_Date) - 1) & "日营业收入报表"
        Me.Label2.Caption = CStr(CDate(System_Date) - 1) & "日营业收入报表"
        Adodc1.ConnectionString = My_PROVIDER
        Adodc1.CommandType = adCmdText
        Adodc1.RecordSource = "select * from 日营业收入报表 where 编码 like '999999'"
        Adodc1.Refresh
        If Adodc1.Recordset.RecordCount > 0 Then
            While Not Adodc1.Recordset.EOF
                Adodc1.Recordset.Delete
                Adodc1.Recordset.Update
                Adodc1.Recordset.MoveNext
            Wend
        End If

        Adodc1.ConnectionString = My_PROVIDER
        Adodc1.CommandType = adCmdText
        Adodc1.RecordSource = "select * from 日营业收入报表 order by 编码"
        Adodc1.Refresh
        
        FB_table2 = " 日营业收入报表 order by 编码"
        US_Field = " 日营业收入报表"
        FB_table1 = FB_table2
        If Adodc1.Recordset.RecordCount > 0 Then
            Adodc1.Recordset.MoveFirst
            While Not Adodc1.Recordset.EOF
                If Len(Trim(Adodc1.Recordset.Fields("编码"))) = 6 Then
                If Right(Adodc1.Recordset.Fields("编码"), 4) = "9999" Then
                For i = 0 To Adodc1.Recordset.Fields.Count - 1
                    If Adodc1.Recordset.Fields(i).Type = adCurrency Then
                        Total1(i) = Total1(i) + Adodc1.Recordset.Fields(i)
                    End If
                Next
                End If
                End If
                Adodc1.Recordset.MoveNext
            Wend
            Adodc1.Recordset.AddNew
            For i = 0 To Adodc1.Recordset.Fields.Count - 1
                If Total1(i) <> 0 Then
                    Adodc1.Recordset.Fields(i) = Total1(i)
                End If
            Next
            Adodc1.Recordset.Fields("编码") = "999999"
            Adodc1.Recordset.Fields("名称") = "合计"
            Adodc1.Recordset.Update

        End If
        Set DataGrid1.DataSource = Adodc1
        DataGrid1.ReBind
        DataGrid1.Refresh
        For i = 2 To DataGrid1.Columns.Count - 1
            DataGrid1.Columns(i).Width = 1000
        Next
    Case "挂帐明细表"
        Me.Caption = "挂帐明细表"
        Me.Label1.Caption = "挂帐明细表"
        Me.Label2.Caption = "挂帐明细表"
        Adodc1.ConnectionString = My_PROVIDER
        Adodc1.CommandType = adCmdText
        Adodc1.RecordSource = "select 帐号,姓名,房号,平衡金额,预付款,费用类,帐务笔数,到店日期,到店时间,离店日期,离店时间,天数,房价,结帐方式,挂帐金额,日期  from 住宿情况 where 当前状态 like '离店' and 结帐方式 like '挂帐' and 日期 like '" & System_Date & "' and 操作员 like '" & CzyName & "' and 帐号 like '999999'"
        Adodc1.Refresh
        If Adodc1.Recordset.RecordCount > 0 Then
            While Not Adodc1.Recordset.EOF
                Adodc1.Recordset.Delete
                Adodc1.Recordset.Update
                Adodc1.Recordset.MoveNext
            Wend
        End If

        Adodc1.ConnectionString = My_PROVIDER
        Adodc1.CommandType = adCmdText
        Adodc1.RecordSource = "select 帐号,姓名,房号,平衡金额,预付款,费用类,帐务笔数,到店日期,到店时间,离店日期,离店时间,天数,房价,结帐方式,挂帐金额,日期,操作员,当前状态 from 住宿情况 where 当前状态 like '离店' and 结帐方式 like '挂帐' and 日期 like '" & System_Date & "' order by 帐号 desc"
        Adodc1.Refresh
        
        FB_table2 = " 住宿情况 where 当前状态 like '离店' and 结帐方式 like '挂帐' and 日期 like '" & System_Date & "' order by 帐号 desc"
        US_Field = "  住宿情况 where 当前状态 like '离店' and 结帐方式 like '挂帐' and 日期 like '" & System_Date & "' order by 帐号 desc"
        FB_table1 = FB_table2
        If Adodc1.Recordset.RecordCount > 0 Then
            Adodc1.Recordset.MoveFirst
            While Not Adodc1.Recordset.EOF
                
                For i = 0 To Adodc1.Recordset.Fields.Count - 1
                    If Adodc1.Recordset.Fields(i).Type = adCurrency Then
                        Total1(i) = Total1(i) + Adodc1.Recordset.Fields(i)
                    End If
                Next
                Adodc1.Recordset.MoveNext
            Wend
            Adodc1.Recordset.AddNew
            For i = 0 To Adodc1.Recordset.Fields.Count - 1
                If Total1(i) <> 0 Then
                    Adodc1.Recordset.Fields(i) = Total1(i)
                End If
            Next
            Adodc1.Recordset.Fields("日期") = System_Date
            Adodc1.Recordset.Fields("帐号") = "999999"
            Adodc1.Recordset.Fields("姓名") = "合计"
            Adodc1.Recordset.Fields("操作员") = CzyName
            Adodc1.Recordset.Fields("当前状态") = "离店"
            Adodc1.Recordset.Fields("结帐方式") = "挂帐"
            Adodc1.Recordset.Update

        End If
        Set DataGrid1.DataSource = Adodc1
        DataGrid1.ReBind
        DataGrid1.Refresh
    
    End Select

    
End Sub

Private Sub Toolbar1_ButtonClick(ByVal Button As ComctlLib.Button)
    Dim i As Integer
    
    'On Error GoTo E3008

    Select Case Button.Key
        Case "关闭"

            Unload Me

        
        Case "全部"
            Adodc1.ConnectionString = My_PROVIDER
            Adodc1.CommandType = adCmdText
            Adodc1.RecordSource = "select * from " & FB_table1
            Adodc1.Refresh

            
        Case "打印"
            BB_type = "报表"
            BB_title = Me.Caption
            万能报表.Show
    End Select
End Sub