VERSION 5.00
Object = "{CDE57A40-8B86-11D0-B3C6-00A0C90AEA82}#1.0#0"; "MSDATGRD.OCX"
Begin VB.Form frmStornobericht 
   BorderStyle     =   1  'Fest Einfach
   Caption         =   "Form1"
   ClientHeight    =   5925
   ClientLeft      =   45
   ClientTop       =   345
   ClientWidth     =   5985
   LinkTopic       =   "Form1"
   MaxButton       =   0   'False
   MinButton       =   0   'False
   ScaleHeight     =   5925
   ScaleWidth      =   5985
   StartUpPosition =   3  'Windows-Standard
   Begin MSDataGridLib.DataGrid DataGrid1 
      Height          =   3090
      Left            =   480
      TabIndex        =   0
      Top             =   120
      Width           =   2730
      _ExtentX        =   4815
      _ExtentY        =   5450
      _Version        =   393216
      HeadLines       =   1
      RowHeight       =   19
      BeginProperty HeadFont {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
         Name            =   "MS Sans Serif"
         Size            =   9.75
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
         Name            =   "Arial"
         Size            =   9.75
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      ColumnCount     =   2
      BeginProperty Column00 
         DataField       =   ""
         Caption         =   ""
         BeginProperty DataFormat {6D835690-900B-11D0-9484-00A0C91110ED} 
            Type            =   0
            Format          =   ""
            HaveTrueFalseNull=   0
            FirstDayOfWeek  =   0
            FirstWeekOfYear =   0
            LCID            =   1031
            SubFormatType   =   0
         EndProperty
      EndProperty
      BeginProperty Column01 
         DataField       =   ""
         Caption         =   ""
         BeginProperty DataFormat {6D835690-900B-11D0-9484-00A0C91110ED} 
            Type            =   0
            Format          =   ""
            HaveTrueFalseNull=   0
            FirstDayOfWeek  =   0
            FirstWeekOfYear =   0
            LCID            =   1031
            SubFormatType   =   0
         EndProperty
      EndProperty
      SplitCount      =   1
      BeginProperty Split0 
         BeginProperty Column00 
         EndProperty
         BeginProperty Column01 
         EndProperty
      EndProperty
   End
   Begin VB.Label lblTischStorno 
      Alignment       =   1  'Rechts
      BackColor       =   &H80000014&
      BorderStyle     =   1  'Fest Einfach
      Caption         =   "lblTischSumme"
      BeginProperty DataFormat 
         Type            =   1
         Format          =   "0,00"
         HaveTrueFalseNull=   0
         FirstDayOfWeek  =   0
         FirstWeekOfYear =   0
         LCID            =   1031
         SubFormatType   =   1
      EndProperty
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   14.25
         Charset         =   0
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      ForeColor       =   &H00800000&
      Height          =   495
      Left            =   1440
      TabIndex        =   1
      Top             =   4320
      Width           =   1635
   End
End
Attribute VB_Name = "frmStornobericht"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Private objConn As ADODB.Connection
Private rsMain  As ADODB.Recordset
Private Sub Form_Load()
AbstandInnen = MDIHauptmenu.ScaleHeight / 60
Me.Top = frmBerichte.PictureBerichte.Top + frmBerichte.PictureBerichte.Height + frmBerichte.PictureBerichte.Height / 4
Me.Width = 0.9 * frmBerichte.ScaleWidth
Me.Left = frmBerichte.Left + (frmBerichte.ScaleWidth - Me.Width) / 2
Me.Height = frmBerichte.Height - frmBerichte.PictureBerichte.Height - frmBerichte.PictureBerichte.Height / 9

Me.DataGrid1.Top = AbstandInnen
Me.DataGrid1.Left = AbstandInnen
Me.DataGrid1.Height = Me.ScaleHeight - 4 * AbstandInnen
Me.DataGrid1.Width = Me.ScaleWidth - 2 * AbstandInnen

Me.lblTischStorno.Top = Me.DataGrid1.Top + Me.DataGrid1.Height
Me.lblTischStorno.Left = Me.DataGrid1.Left + Me.DataGrid1.Width - Me.lblTischStorno.Width
Me.lblTischStorno.Height = Me.ScaleHeight - Me.DataGrid1.Height - 2 * AbstandInnen
Me.lblTischStorno.FontSize = "10"

Me.DataGrid1.RowHeight = 2 * AbstandInnen
Call Stornos(frmBerichte.DTPickerStartdatum.Value, frmBerichte.DTPickerEnddatum.Value, frmBerichte.ComboKellner.Text)
End Sub
Private Sub DataGrid1_BeforeColEdit(ByVal ColIndex As Integer, ByVal KeyAscii As Integer, Cancel As Integer)
'If DataGrid1.SelBookmarks.Count <> 0 Then DataGrid1.SelBookmarks.Remove 0
    'DataGrid1.SelBookmarks.Add DataGrid1.Bookmark
SendKeys "{ESC}"
End Sub
Private Sub DataGrid1_ButtonClick(ByVal ColIndex As Integer)
'SendKeys "{ESC}"
End Sub

Private Sub DataGrid1_KeyUp(KeyCode As Integer, Shift As Integer)
On Error Resume Next
If DataGrid1.SelBookmarks.Count <> 0 Then DataGrid1.SelBookmarks.Remove 0
   DataGrid1.SelBookmarks.Add DataGrid1.Bookmark
End Sub

Private Sub DataGrid1_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
If frmTastenMenu2.txtPLU <> "" Then
frmTastenMenu2.txtPLU = ""
End If
SendKeys "{ESC}"
End Sub
Private Sub DataGrid1_RowColChange(LastRow As Variant, ByVal LastCol As Integer)
'On Error Resume Next
'If DataGrid1.SelBookmarks.Count <> 0 Then DataGrid1.SelBookmarks.Remove 0
 '  DataGrid1.SelBookmarks.Add DataGrid1.Bookmark
SendKeys "{ESC}"
End Sub

Sub Stornos(VarDatum1, VarDatum2, VarStornoKellner)
Dim objConn As ADODB.Connection
Dim rsStornos As ADODB.Recordset
Dim strPath As String

Set objConn = New ADODB.Connection
Set rsStornos = 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"
    .ConnectionString = "Data Source=" & strPath & "asql.mdb"
    .CursorLocation = adUseClient
    .Open
  End With
 
   'Tabelle TischNeuesGericht öffnen
   With rsStornos
     .ActiveConnection = objConn
     .CursorLocation = adUseClient
     .LockType = adLockOptimistic
    If VarStornoKellner = "alle Kellner" Then
     .Open "Select ID,Datum,Zeit,Station,Kellner,Tisch,PLU,Menge,Gericht,EPREIS AS [E-Preis],Menge*EPREIS AS [G-Preis] FROM Stornos Where Datum >= #" & Format$(VarDatum1, "mm-dd-yyyy") & "# and  Datum <= #" & Format$(VarDatum2, "mm-dd-yyyy") & "#"
    Else
     .Open "Select ID,Datum,Zeit,Station,Kellner,Tisch,PLU,Menge,Gericht,EPREIS AS [E-Preis],Menge*EPREIS AS [G-Preis] FROM Stornos Where Datum >= #" & Format$(VarDatum1, "mm-dd-yyyy") & "# and  Datum <= #" & Format$(VarDatum2, "mm-dd-yyyy") & "# and Kellner = '" & VarStornoKellner & "'"
    End If
   End With
 
 
 
 
 Set Me.DataGrid1.DataSource = rsStornos
 Me.DataGrid1.Columns("ID").Width = 0.08 * Me.DataGrid1.Width
 Me.DataGrid1.Columns("Datum").Width = 0.09 * Me.DataGrid1.Width
 Me.DataGrid1.Columns("Zeit").Width = 0.06 * Me.DataGrid1.Width
 Me.DataGrid1.Columns("Station").Width = 0.08 * Me.DataGrid1.Width
 Me.DataGrid1.Columns("Kellner").Width = 0.08 * Me.DataGrid1.Width
 Me.DataGrid1.Columns("Tisch").Width = 0.06 * Me.DataGrid1.Width
 Me.DataGrid1.Columns("PLU").Width = 0.07 * Me.DataGrid1.Width
 Me.DataGrid1.Columns("Menge").Width = 0.07 * Me.DataGrid1.Width
 Me.DataGrid1.Columns("Gericht").Width = 0.2 * Me.DataGrid1.Width
 Me.DataGrid1.Columns("E-Preis").Width = 0.07 * Me.DataGrid1.Width
 Me.DataGrid1.Columns("G-Preis").Width = 0.07 * Me.DataGrid1.Width
 
 Me.DataGrid1.Columns("Menge").Alignment = dbgCenter
 Me.DataGrid1.Columns("E-Preis").Alignment = dbgRight
 Me.DataGrid1.Columns("G-Preis").Alignment = dbgRight

  If Me.DataGrid1.Row >= 0 Then
     Me.DataGrid1.Scroll 0, 1000
     Me.DataGrid1.Row = Me.DataGrid1.VisibleRows - 1
     'Selection modus setzen
    If Me.DataGrid1.SelBookmarks.Count <> 0 Then Me.DataGrid1.SelBookmarks.Remove 0
    Me.DataGrid1.SelBookmarks.Add Me.DataGrid1.Bookmark
    SendKeys "{ESC}"
  End If
 
 Call StornoSumme(frmBerichte.DTPickerStartdatum.Value, frmBerichte.DTPickerEnddatum.Value, frmBerichte.ComboKellner.Text)

Set objConn = Nothing
Set rsStornos = Nothing


exit_Sub:
  On Error GoTo 0
Set rsStornos = Nothing
Set objConn = Nothing
  
  Exit Sub

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

Set rsStornos = Nothing
Set objConn = Nothing

End Sub

Sub StornoSumme(VarDatum1, VarDatum2, VarStornoKellner)
Dim objConn As New ADODB.Connection
Dim rsSummeStornos As New ADODB.Recordset


Dim strPath As String

Set objConn = New ADODB.Connection
Set rsSummeStornos = 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"
    .ConnectionString = "Data Source=" & strPath & "asql.mdb"
    .CursorLocation = adUseClient
    .Open
  End With

With rsSummeStornos
    .ActiveConnection = objConn
    .CursorLocation = adUseClient
    .LockType = adLockOptimistic
    .Source = "Select  "
    If VarStornoKellner = "alle Kellner" Then
     .Open "Select Sum(abs(Menge*EPREIS)) As SummeStornos FROM Stornos Where Datum >= #" & Format$(VarDatum1, "mm-dd-yyyy") & "# and  Datum <= #" & Format$(VarDatum2, "mm-dd-yyyy") & "#"
    Else
     .Open "Select Sum(abs(Menge*EPREIS)) As SummeStornos FROM Stornos Where Datum >= #" & Format$(VarDatum1, "mm-dd-yyyy") & "# and  Datum <= #" & Format$(VarDatum2, "mm-dd-yyyy") & "# and Kellner = '" & VarStornoKellner & "'"
    End If


End With
  
  IstNullWert = CStr(IsNull(rsSummeStornos("SummeStornos")))

 If IstNullWert = True Then
  Me.lblTischStorno.Caption = ""
  Else
    Me.lblTischStorno.Caption = Format(CStr(rsSummeStornos("SummeStornos")), "Currency")
 End If

   'Formatierung von DataGrid1 Tabelle vornehmen
   Set fmt = New StdDataFormat
   fmt.Type = fmtCustom
   fmt.Format = "Currency"
   Set Me.DataGrid1.Columns("E-Preis").DataFormat = fmt
   Set Me.DataGrid1.Columns("G-Preis").DataFormat = fmt
Set objConn = Nothing
Set rsSummeStornos = Nothing
Set fmt = Nothing
exit_Sub:
  On Error GoTo 0
  Exit Sub

err_Handler:
  MsgBox "Fehlernummer " & Err.Number & Chr$(13) & Error$(Err), _
            vbCritical, "Fehler"
    Me.DataGrid1.ReBind
  Resume exit_Sub
Set objConn = Nothing
Set rsSummeStornos = Nothing
End Sub