VERSION 5.00
Begin {78E93846-85FD-11D0-8487-00A0C90DC8A9} ReportUmsatzbericht 
   BorderStyle     =   1  'Fest Einfach
   Caption         =   "Berichte"
   ClientHeight    =   6720
   ClientLeft      =   90
   ClientTop       =   285
   ClientWidth     =   4395
   MaxButton       =   0   'False
   MinButton       =   0   'False
   _ExtentX        =   7752
   _ExtentY        =   11853
   _Version        =   393216
   _DesignerVersion=   100684101
   ReportWidth     =   3815
   BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
      Name            =   "Arial"
      Size            =   8.25
      Charset         =   0
      Weight          =   400
      Underline       =   0   'False
      Italic          =   0   'False
      Strikethrough   =   0   'False
   EndProperty
   GridX           =   1
   GridY           =   1
   LeftMargin      =   200
   RightMargin     =   50
   BottomMargin    =   200
   _Settings       =   23
   NumSections     =   3
   SectionCode0    =   1
   BeginProperty Section0 {1C13A8E0-A0B6-11D0-848E-00A0C90DC8A9} 
      _Version        =   393216
      Name            =   "Bereich4"
      Object.Height          =   2265
      NumControls     =   4
      ItemType0       =   7
      BeginProperty Item0 {1C13A8E5-A0B6-11D0-848E-00A0C90DC8A9} 
         _Version        =   393216
         Name            =   "Symbol1"
         Object.Width           =   3800
         Object.Height          =   500
         SizeMode        =   2
      EndProperty
      ItemType1       =   3
      BeginProperty Item1 {1C13A8E1-A0B6-11D0-848E-00A0C90DC8A9} 
         _Version        =   393216
         Name            =   "lblTagesumsatz"
         Object.Top             =   1476
         Object.Width           =   3800
         Object.Height          =   396
         BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
            Name            =   "Arial"
            Size            =   14.25
            Charset         =   0
            Weight          =   700
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Object.Caption         =   "Umsatzbericht"
         Alignment       =   2
      EndProperty
      ItemType2       =   3
      BeginProperty Item2 {1C13A8E1-A0B6-11D0-848E-00A0C90DC8A9} 
         _Version        =   393216
         Name            =   "lblDatum"
         Object.Top             =   1890
         Object.Width           =   3390
         Object.Height          =   225
         BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
            Name            =   "Arial"
            Size            =   9.75
            Charset         =   0
            Weight          =   700
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Object.Caption         =   "Datum"
         Alignment       =   2
      EndProperty
      ItemType3       =   3
      BeginProperty Item3 {1C13A8E1-A0B6-11D0-848E-00A0C90DC8A9} 
         _Version        =   393216
         Name            =   "lblAdresse"
         Object.Top             =   1065
         Object.Width           =   3800
         Object.Height          =   225
         BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
            Name            =   "Arial"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Object.Caption         =   "Adresse"
         Alignment       =   2
      EndProperty
   EndProperty
   SectionCode1    =   4
   BeginProperty Section1 {1C13A8E0-A0B6-11D0-848E-00A0C90DC8A9} 
      _Version        =   393216
      Name            =   "Bereich1"
      Object.Height          =   720
      NumControls     =   4
      ItemType0       =   4
      BeginProperty Item0 {1C13A8E2-A0B6-11D0-848E-00A0C90DC8A9} 
         _Version        =   393216
         Name            =   "txtOffen"
         Object.Left            =   2670
         Object.Top             =   15
         Object.Width           =   1125
         Object.Height          =   240
         BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
            Name            =   "Arial"
            Size            =   11.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Alignment       =   1
         BeginProperty DataFormat {6D835690-900B-11D0-9484-00A0C91110ED} 
            Type            =   1
            Format          =   "#####0.00"
            HaveTrueFalseNull=   0
            FirstDayOfWeek  =   0
            FirstWeekOfYear =   0
            LCID            =   1031
            SubFormatType   =   0
         EndProperty
      EndProperty
      ItemType1       =   3
      BeginProperty Item1 {1C13A8E1-A0B6-11D0-848E-00A0C90DC8A9} 
         _Version        =   393216
         Name            =   "lblOffen"
         Object.Left            =   30
         Object.Top             =   15
         Object.Width           =   2070
         Object.Height          =   240
         BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
            Name            =   "Arial"
            Size            =   11.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Object.Caption         =   "Umsatz Getränke"
      EndProperty
      ItemType2       =   3
      BeginProperty Item2 {1C13A8E1-A0B6-11D0-848E-00A0C90DC8A9} 
         _Version        =   393216
         Name            =   "lblKassiert"
         Object.Left            =   30
         Object.Top             =   330
         Object.Width           =   2040
         Object.Height          =   240
         BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
            Name            =   "Arial"
            Size            =   11.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Object.Caption         =   "Umsatz Speisen"
      EndProperty
      ItemType3       =   4
      BeginProperty Item3 {1C13A8E2-A0B6-11D0-848E-00A0C90DC8A9} 
         _Version        =   393216
         Name            =   "txtKassiert"
         Object.Left            =   2640
         Object.Top             =   330
         Object.Width           =   1155
         Object.Height          =   240
         BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
            Name            =   "Arial"
            Size            =   11.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Alignment       =   1
         BeginProperty DataFormat {6D835690-900B-11D0-9484-00A0C91110ED} 
            Type            =   1
            Format          =   "#####0.00"
            HaveTrueFalseNull=   0
            FirstDayOfWeek  =   0
            FirstWeekOfYear =   0
            LCID            =   1031
            SubFormatType   =   0
         EndProperty
      EndProperty
   EndProperty
   SectionCode2    =   8
   BeginProperty Section2 {1C13A8E0-A0B6-11D0-848E-00A0C90DC8A9} 
      _Version        =   393216
      Name            =   "Bereich5"
      Object.Height          =   1455
      NumControls     =   7
      ItemType0       =   3
      BeginProperty Item0 {1C13A8E1-A0B6-11D0-848E-00A0C90DC8A9} 
         _Version        =   393216
         Name            =   "lblTotal"
         Object.Top             =   800
         Object.Width           =   930
         Object.Height          =   330
         BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
            Name            =   "Arial"
            Size            =   12
            Charset         =   0
            Weight          =   700
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Object.Caption         =   "TOTAL"
      EndProperty
      ItemType1       =   5
      BeginProperty Item1 {1C13A8E3-A0B6-11D0-848E-00A0C90DC8A9} 
         _Version        =   393216
         Name            =   "Linie1"
         Object.Left            =   15
         Object.Width           =   3800
      EndProperty
      ItemType2       =   3
      BeginProperty Item2 {1C13A8E1-A0B6-11D0-848E-00A0C90DC8A9} 
         _Version        =   393216
         Name            =   "lblNetto"
         Object.Left            =   15
         Object.Top             =   75
         Object.Width           =   1125
         Object.Height          =   235
         BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
            Name            =   "Arial"
            Size            =   11.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Object.Caption         =   "Netto"
      EndProperty
      ItemType3       =   3
      BeginProperty Item3 {1C13A8E1-A0B6-11D0-848E-00A0C90DC8A9} 
         _Version        =   393216
         Name            =   "lblMwSt"
         Object.Top             =   330
         Object.Width           =   1155
         Object.Height          =   240
         BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
            Name            =   "Arial"
            Size            =   11.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Object.Caption         =   "MwSt"
      EndProperty
      ItemType4       =   3
      BeginProperty Item4 {1C13A8E1-A0B6-11D0-848E-00A0C90DC8A9} 
         _Version        =   393216
         Name            =   "lblSummeMwSt"
         Object.Left            =   2730
         Object.Top             =   330
         Object.Width           =   1065
         Object.Height          =   225
         BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
            Name            =   "Arial"
            Size            =   11.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Object.Caption         =   "MwSt"
         Alignment       =   1
      EndProperty
      ItemType5       =   3
      BeginProperty Item5 {1C13A8E1-A0B6-11D0-848E-00A0C90DC8A9} 
         _Version        =   393216
         Name            =   "lblSummeNetto"
         Object.Left            =   2715
         Object.Top             =   75
         Object.Width           =   1065
         Object.Height          =   225
         BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
            Name            =   "Arial"
            Size            =   11.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Object.Caption         =   "Netto"
         Alignment       =   1
      EndProperty
      ItemType6       =   3
      BeginProperty Item6 {1C13A8E1-A0B6-11D0-848E-00A0C90DC8A9} 
         _Version        =   393216
         Name            =   "lblSummeBrutto"
         Object.Left            =   2160
         Object.Top             =   800
         Object.Width           =   1635
         Object.Height          =   330
         BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
            Name            =   "Arial"
            Size            =   12
            Charset         =   0
            Weight          =   700
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Object.Caption         =   "Bezeichnung1"
         Alignment       =   1
      EndProperty
   EndProperty
End
Attribute VB_Name = "ReportUmsatzbericht"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Sub BerichtUmsatz(VarDatum1, VarDatum2)
Dim objConn As ADODB.Connection
Dim rsGetraenke As ADODB.Recordset
Dim rsSpeisen As ADODB.Recordset
Dim rsBerichte As ADODB.Recordset
Dim rsAktuel As ADODB.Recordset

Dim strPath As String

Set objConn = New ADODB.Connection
Set rsGetraenke = New ADODB.Recordset
Set rsSpeisen = New ADODB.Recordset
Set rsBerichte = New ADODB.Recordset
Set rsAktuel = New ADODB.Recordset

  On Error GoTo err_Handler

  strPath = App.Path
  If Right$(strPath, 1) <> "\" Then strPath = strPath & "\"


  With objConn
    .Provider = "Microsoft Jet 4.0 OLE DB Provider"
    .Properties("Jet OLEDB:Database Password") = VarPasswordDatenbank
    .ConnectionString = "Data Source=" & strPath & "asql.mdb"
    .CursorLocation = adUseClient
    .Open
  End With
  
   'Tabelle OffeneTische öffnen
   With rsGetraenke
     .ActiveConnection = objConn
     .CursorLocation = adUseClient
     .LockType = adLockOptimistic
     .Open "Select Sum(GPREIS) AS SummeBruttoGetraenke from RechnungDetails Where Datum >= #" & Format$(VarDatum1, "mm-dd-yyyy") & "# and  Datum <= #" & Format$(VarDatum2, "mm-dd-yyyy") & "# and  IDWG = 'Getränke'"
   End With

 
  With rsSpeisen
     .ActiveConnection = objConn
     .CursorLocation = adUseClient
     .LockType = adLockOptimistic
     .Open "Select Sum(GPREIS) As SummeBruttoSpeisen from RechnungDetails Where Datum >= #" & Format$(VarDatum1, "mm-dd-yyyy") & "# and  Datum <= #" & Format$(VarDatum2, "mm-dd-yyyy") & "# and  IDWG = 'Speisen'"

   End With

  With rsBerichte
     .ActiveConnection = objConn
     .CursorLocation = adUseClient
     .LockType = adLockOptimistic
     .Open "Select *from AktuellesBericht"
   End With
 

 For I = 1 To rsBerichte.RecordCount
 rsBerichte.Delete
 rsBerichte.MoveNext
 Next I

rsBerichte.AddNew


 While Not rsGetraenke.EOF
   If IsNull(rsGetraenke.Fields("SummeBruttoGetraenke").Value) Then
    rsBerichte.Fields("Offen").Value = rsGetraenke.Fields("SummeBruttoGetraenke").Value
   Else
    rsBerichte.Fields("Offen").Value = Format(rsGetraenke.Fields("SummeBruttoGetraenke").Value, "####0.#0")
   End If
   rsGetraenke.MoveNext
 Wend


 While Not rsSpeisen.EOF
   If IsNull(rsSpeisen.Fields("SummeBruttoSpeisen").Value) Then
    rsBerichte.Fields("Kassiert").Value = rsSpeisen.Fields("SummeBruttoSpeisen").Value
   Else
    rsBerichte.Fields("Kassiert").Value = Format(rsSpeisen.Fields("SummeBruttoSpeisen").Value, "####0.#0")
   End If
   rsSpeisen.MoveNext
 Wend


rsBerichte.Update
rsBerichte.Requery
 
 With rsAktuel
     .ActiveConnection = objConn
     .CursorLocation = adUseClient
     .LockType = adLockOptimistic
     .Open "Select sum(Offen) as Summe1, sum(Kassiert) as Summe2 from AktuellesBericht"
  End With

  If IsNull(rsAktuel.Fields("Summe1").Value) And IsNull(rsAktuel.Fields("Summe2").Value) Then
   k1 = 0
   k2 = 0
  ElseIf IsNull(rsAktuel.Fields("Summe1").Value) And Not IsNull(rsAktuel.Fields("Summe2").Value) Then
   k1 = 0
   k2 = CDbl(rsAktuel.Fields("Summe2").Value)
  ElseIf IsNull(rsAktuel.Fields("Summe2").Value) And Not IsNull(rsAktuel.Fields("Summe1").Value) Then
   k1 = CDbl(rsAktuel.Fields("Summe1").Value)
   k2 = 0
  Else
   k1 = CDbl(rsAktuel.Fields("Summe1").Value)
   k2 = CDbl(rsAktuel.Fields("Summe2").Value)
 End If
 
 VarBrutto = k1 + k2
 

 
 VarNetto = Format(VarBrutto / 1.19, "#####0.#0")
 VarMwSt = Format((VarBrutto - VarNetto), "#####0.#0")
 

 Set ReportUmsatzbericht.DataSource = rsBerichte
  Set Me.Sections("Bereich4").Controls("Symbol1").Picture = LoadPicture(App.Path & "\Logos\Logo_Lokal.emf")

   Me.Sections("Bereich4").Controls("lblAdresse").Caption = "Gutenbergstr. 20 - 67346 Speyer - Tel:74611"

   'ReportUmsatzbericht.Sections("Bereich4").Controls("lblTagesumsatz").Caption = "Kellnerbericht"
   
   If VarDatum1 = VarDatum2 Then
   ReportUmsatzbericht.Sections("Bereich4").Controls("lblDatum").Caption = VarDatum1
   Else
   ReportUmsatzbericht.Sections("Bereich4").Controls("lblDatum").Caption = "von " & VarDatum1 & " bis " & VarDatum2
   End If

  
 ReportUmsatzbericht.Sections("Bereich1").Controls("txtOffen").DataField = rsBerichte.Fields("Offen").Name
 ReportUmsatzbericht.Sections("Bereich1").Controls("txtKassiert").DataField = rsBerichte.Fields("Kassiert").Name
 
 ReportUmsatzbericht.Sections("Bereich5").Controls("lblNetto").Caption = "Netto"
 ReportUmsatzbericht.Sections("Bereich5").Controls("lblSummeNetto").Caption = VarNetto
    
 ReportUmsatzbericht.Sections("Bereich5").Controls("lblMwSt").Caption = "MwSt. 19%"
 ReportUmsatzbericht.Sections("Bereich5").Controls("lblSummeMwSt").Caption = Format(VarMwSt, "#####0.#0")
    
 ReportUmsatzbericht.Sections("Bereich5").Controls("lblSummeBrutto").Caption = Format(VarBrutto, "Currency")


  'ReportUmsatzbericht.Show 1
  ReportUmsatzbericht.PrintReport



Set objConn = Nothing
Set rsOffeneTische = Nothing
Set rsRechnungDetails = Nothing
Set rsBerichte = Nothing
Set rsKellner = Nothing
Set rsAktuel = Nothing

exit_Sub:
  On Error GoTo 0

Set objConn = Nothing
Set rsOffeneTische = Nothing
Set rsRechnungDetails = Nothing
Set rsBerichte = Nothing
Set rsKellner = Nothing
Set rsAktuel = Nothing
  
  Exit Sub

err_Handler:
    MsgBox "Fehlernummer " & Err.Number & Chr$(13) & Error$(Err), _
            vbCritical, "Fehler"
    
  Resume exit_Sub

Set objConn = Nothing
Set rsOffeneTische = Nothing
Set rsRechnungDetails = Nothing
Set rsBerichte = Nothing
Set rsKellner = Nothing
Set rsAktuel = Nothing
End Sub

Private Sub DataReport_Initialize()
Call frmBerichte.BerichtePositionieren(ReportUmsatzbericht)
End Sub