Public Class CycleCountInquiryDAO

  Private type As String

  Private ReadOnly Property Sql() As String
    Get
      
      'This is where the sql is created
      Return Sql
    End Get
  End Property

  Public Function SelectRecords(ByVal Type As String, ByVal min As Date, ByVal max As DateTime, ByVal conn As IDbConnection) As List(Of CycleCountInquiryDataItem)
    Dim allRecords As New List(Of CycleCountInquiryDataItem)
    Dim param As IDbDataParameter
    Me.type = Type

    Using command As IDbCommand = conn.CreateCommand()
      'based on the type the sql command will be adjusted
      command.CommandText = Sql

      param = command.CreateParameter()
      param.DbType = DbType.Date
      param.ParameterName = "p_start"
      param.Value = min
      command.Parameters.Add(param)

      param = command.CreateParameter()
      param.DbType = DbType.Date
      param.ParameterName = "p_end"
      param.Value = max
      command.Parameters.Add(param)

      Using reader As IDataReader = command.ExecuteReader()
        While reader.Read()
          allRecords.Add(New CycleCountInquiryDataItem)

          With allRecords(allRecords.Count - 1)
            .AuditDate = reader.GetDateTime(0)
            .CycleClassCode = reader.GetString(1)
            .SystemDollars = reader.GetDouble(2)
            .ActualDollars = reader.GetDouble(3)
            .DollarVariance = reader.GetDouble(4)
            .AbsoluteDollarDelta = reader.GetDouble(5)
            .AbsoluteAccuracyPercent = reader.GetDouble(6)
            .AccuracyPercent = reader.GetDouble(7)
            .TotalAudits = reader.GetInt32(8)
            .OkAudits = reader.GetInt32(9)
            .BadAudits = reader.GetInt32(10)
            .Accuracy = reader.GetDouble(11)
            .SystemPieces = reader.GetInt32(12)
            .PiecesVariance = reader.GetInt32(13)
            .AbsolutePiecesAccuracy = reader.GetDouble(14)
          End With
        End While

      End Using
    End Using

    Return allRecords
  End Function

  Public Function TotalRecords(ByVal Type As String, ByVal min As Date, ByVal max As DateTime, ByVal conn As IDbConnection) As IList(Of CycleCountInquiryDataItem)
    Return New List(Of CycleCountInquiryDataItem)((From r In SelectRecords(Type, min, max, conn) Where r.CycleClassCode = "XX" Order By r.AuditDate Select r).ToList())
  End Function

  Public Function DetailRecords(ByVal Type As String, ByVal min As Date, ByVal max As DateTime, ByVal conn As IDbConnection) As IList(Of CycleCountInquiryDataItem)
    Return New List(Of CycleCountInquiryDataItem)((From r In SelectRecords(Type, min, max, conn) Where r.CycleClassCode <> "XX" Order By r.AuditDate Select r).ToList())
  End Function

End Class

Public Class CycleCountInquiryDataItem
  Public _AuditDate As Date
  Public _CycleClassCode As String
  Public _SystemDollars As Double
  Public _ActualDollars As Double
  Public _DollarVariance As Double
  Public _AbsoluteDollarDelta As Double
  Public _AbsoluteAccuracyPercent As Double
  Public _AccuracyPercent As Double
  Public _TotalAudits As Int32
  Public _OkAudits As Int32
  Public _BadAudits As Int32
  Public _Accuracy As Double
  Public _SystemPieces As Int32
  Public _PiecesVariance As Int32
  Public _AbsolutePiecesAccuracy As Double

  Public Property AuditDate() As Date
    Get
      Return _AuditDate
    End Get
    Set(ByVal value As Date)
      _AuditDate = value
    End Set
  End Property

  Public Property CycleClassCode() As String
    Get
      Return _CycleClassCode
    End Get
    Set(ByVal value As String)
      _CycleClassCode = value
    End Set
  End Property

  Public Property SystemDollars() As Double
    Get
      Return _SystemDollars
    End Get
    Set(ByVal value As Double)
      _SystemDollars = value
    End Set
  End Property

  Public Property ActualDollars() As Double
    Get
      Return _ActualDollars
    End Get
    Set(ByVal value As Double)
      _ActualDollars = value
    End Set
  End Property

  Public Property DollarVariance() As Double
    Get
      Return _DollarVariance
    End Get
    Set(ByVal value As Double)
      _DollarVariance = value
    End Set
  End Property

  Public Property AbsoluteDollarDelta() As Double
    Get
      Return _AbsoluteDollarDelta
    End Get
    Set(ByVal value As Double)
      _AbsoluteDollarDelta = value
    End Set
  End Property

  Public Property AbsoluteAccuracyPercent() As Double
    Get
      Return _AbsoluteAccuracyPercent
    End Get
    Set(ByVal value As Double)
      _AbsoluteAccuracyPercent = value
    End Set
  End Property

  Public Property AccuracyPercent() As Double
    Get
      Return _AccuracyPercent
    End Get
    Set(ByVal value As Double)
      _AccuracyPercent = value
    End Set
  End Property

  Public Property TotalAudits() As Int32
    Get
      Return _TotalAudits
    End Get
    Set(ByVal value As Int32)
      _TotalAudits = value
    End Set
  End Property

  Public Property OkAudits() As Int32
    Get
      Return _OkAudits
    End Get
    Set(ByVal value As Int32)
      _OkAudits = value
    End Set
  End Property

  Public Property BadAudits() As Int32
    Get
      Return _BadAudits
    End Get
    Set(ByVal value As Int32)
      _BadAudits = value
    End Set
  End Property

  Public Property Accuracy() As Double
    Get
      Return _Accuracy
    End Get
    Set(ByVal value As Double)
      _Accuracy = value
    End Set
  End Property

  Public Property SystemPieces() As Int32
    Get
      Return _SystemPieces
    End Get
    Set(ByVal value As Int32)
      _SystemPieces = value
    End Set
  End Property

  Public Property PiecesVariance() As Int32
    Get
      Return _PiecesVariance
    End Get
    Set(ByVal value As Int32)
      _PiecesVariance = value
    End Set
  End Property

  Public Property AbsolutePiecesAccuracy() As Double
    Get
      Return _AbsolutePiecesAccuracy
    End Get
    Set(ByVal value As Double)
      _AbsolutePiecesAccuracy = value
    End Set
  End Property

End Class









<ig:WebHierarchicalDataSource ID="WebHierarchicalDataSource1" runat="server">
  <DataViews>
   <ig:DataView ID="dsTotals_DefaultView" DataSourceID="dsTotals" DataMember="DefaultView" />
   <ig:DataView ID="dsDetail_DefaultView" DataSourceID="dsDetail" DataMember="DefaultView" />
  </DataViews>
  <DataRelations>
   <ig:DataRelation ChildColumns="AuditDate" ChildDataViewID="dsDetail_DefaultView" ParentColumns="AuditDate" ParentDataViewID="dsTotals_DefaultView" />
  </DataRelations>
 </ig:WebHierarchicalDataSource>
 <ig:WebHierarchicalDataGrid ID="WebHierarchicalDataGrid1" runat="server" DataSourceID="WebHierarchicalDataSource1" AutoGenerateBands="False" AutoGenerateColumns="False" Height="350px" Width="900px" DataKeyFields="AuditDate" Visible="False" InitialDataBindDepth="0">
  <Columns>
   <ig:BoundDataField DataFieldName="AuditDate" Key="AuditDate">
    <Header Text="AuditDate" />
   </ig:BoundDataField>
   <ig:BoundDataField DataFieldName="CycleClassCode" Key="CycleClassCode">
    <Header Text="CycleClassCode" />
   </ig:BoundDataField>
   <ig:BoundDataField DataFieldName="SystemDollars" Key="SystemDollars">
    <Header Text="SystemDollars" />
   </ig:BoundDataField>
   <ig:BoundDataField DataFieldName="ActualDollars" Key="ActualDollars">
    <Header Text="ActualDollars" />
   </ig:BoundDataField>
   <ig:BoundDataField DataFieldName="DollarVariance" Key="DollarVariance">
    <Header Text="DollarVariance" />
   </ig:BoundDataField>
   <ig:BoundDataField DataFieldName="AbsoluteDollarDelta" Key="AbsoluteDollarDelta">
    <Header Text="AbsoluteDollarDelta" />
   </ig:BoundDataField>
   <ig:BoundDataField DataFieldName="AbsoluteAccuracyPercent" Key="AbsoluteAccuracyPercent">
    <Header Text="AbsoluteAccuracyPercent" />
   </ig:BoundDataField>
   <ig:BoundDataField DataFieldName="AccuracyPercent" Key="AccuracyPercent">
    <Header Text="AccuracyPercent" />
   </ig:BoundDataField>
   <ig:BoundDataField DataFieldName="TotalAudits" Key="TotalAudits">
    <Header Text="TotalAudits" />
   </ig:BoundDataField>
   <ig:BoundDataField DataFieldName="OkAudits" Key="OkAudits">
    <Header Text="OkAudits" />
   </ig:BoundDataField>
   <ig:BoundDataField DataFieldName="BadAudits" Key="BadAudits">
    <Header Text="BadAudits" />
   </ig:BoundDataField>
   <ig:BoundDataField DataFieldName="Accuracy" Key="Accuracy">
    <Header Text="Accuracy" />
   </ig:BoundDataField>
   <ig:BoundDataField DataFieldName="SystemPieces" Key="SystemPieces">
    <Header Text="SystemPieces" />
   </ig:BoundDataField>
   <ig:BoundDataField DataFieldName="PiecesVariance" Key="PiecesVariance">
    <Header Text="PiecesVariance" />
   </ig:BoundDataField>
   <ig:BoundDataField DataFieldName="AbsolutePiecesAccuracy" Key="AbsolutePiecesAccuracy">
    <Header Text="AbsolutePiecesAccuracy" />
   </ig:BoundDataField>
  </Columns>
  <Bands>
   <ig:Band DataMember="dsDetail_DefaultView" Key="Details" DataKeyFields="AuditDate" AutoGenerateColumns="False" ShowHeader="False">
    <Columns>
     <ig:BoundDataField DataFieldName="AuditDate" Key="AuditDate">
      <Header Text="AuditDate" />
     </ig:BoundDataField>
     <ig:BoundDataField DataFieldName="CycleClassCode" Key="CycleClassCode">
      <Header Text="CycleClassCode" />
     </ig:BoundDataField>
     <ig:BoundDataField DataFieldName="SystemDollars" Key="SystemDollars">
      <Header Text="SystemDollars" />
     </ig:BoundDataField>
     <ig:BoundDataField DataFieldName="ActualDollars" Key="ActualDollars">
      <Header Text="ActualDollars" />
     </ig:BoundDataField>
     <ig:BoundDataField DataFieldName="DollarVariance" Key="DollarVariance">
      <Header Text="DollarVariance" />
     </ig:BoundDataField>
     <ig:BoundDataField DataFieldName="AbsoluteDollarDelta" Key="AbsoluteDollarDelta">
      <Header Text="AbsoluteDollarDelta" />
     </ig:BoundDataField>
     <ig:BoundDataField DataFieldName="AbsoluteAccuracyPercent" Key="AbsoluteAccuracyPercent">
      <Header Text="AbsoluteAccuracyPercent" />
     </ig:BoundDataField>
     <ig:BoundDataField DataFieldName="AccuracyPercent" Key="AccuracyPercent">
      <Header Text="AccuracyPercent" />
     </ig:BoundDataField>
     <ig:BoundDataField DataFieldName="TotalAudits" Key="TotalAudits">
      <Header Text="TotalAudits" />
     </ig:BoundDataField>
     <ig:BoundDataField DataFieldName="OkAudits" Key="OkAudits">
      <Header Text="OkAudits" />
     </ig:BoundDataField>
     <ig:BoundDataField DataFieldName="BadAudits" Key="BadAudits">
      <Header Text="BadAudits" />
     </ig:BoundDataField>
     <ig:BoundDataField DataFieldName="Accuracy" Key="Accuracy">
      <Header Text="Accuracy" />
     </ig:BoundDataField>
     <ig:BoundDataField DataFieldName="SystemPieces" Key="SystemPieces">
      <Header Text="SystemPieces" />
     </ig:BoundDataField>
     <ig:BoundDataField DataFieldName="PiecesVariance" Key="PiecesVariance">
      <Header Text="PiecesVariance" />
     </ig:BoundDataField>
     <ig:BoundDataField DataFieldName="AbsolutePiecesAccuracy" Key="AbsolutePiecesAccuracy">
      <Header Text="AbsolutePiecesAccuracy" />
     </ig:BoundDataField>
    </Columns>
   </ig:Band>
  </Bands>
 </ig:WebHierarchicalDataGrid>
 <asp:ObjectDataSource ID="dsTotals" runat="server" SelectMethod="TotalRecords" TypeName="Intranet.CycleCountInquiryDAO">
  <SelectParameters>
   <asp:ControlParameter ControlID="RadioButtonList1" Name="Type" PropertyName="SelectedValue" Type="String" />
   <asp:ControlParameter ControlID="wdcBegin" Name="min" PropertyName="Value" Type="DateTime" />
   <asp:ControlParameter ControlID="wdcEnd" Name="max" PropertyName="Value" Type="DateTime" />
   <asp:Parameter Name="conn" Type="Object" />
  </SelectParameters>
 </asp:ObjectDataSource>
 <asp:ObjectDataSource ID="dsDetail" runat="server" SelectMethod="DetailRecords" TypeName="Intranet.CycleCountInquiryDAO">
  <SelectParameters>
   <asp:ControlParameter ControlID="RadioButtonList1" Name="Type" PropertyName="SelectedValue" Type="String" />
   <asp:ControlParameter ControlID="wdcBegin" Name="min" PropertyName="Value" Type="DateTime" />
   <asp:ControlParameter ControlID="wdcEnd" Name="max" PropertyName="Value" Type="DateTime" />
   <asp:Parameter Name="conn" Type="Object" />
  </SelectParameters>
 </asp:ObjectDataSource>