Imports Stock_Control.sqlDB
Imports System.Web.Configuration

'NOTES
'v 1.2.6 - Changed brand/repQF to sku/Customer QF
'QF[sku][p][c] = CustomerSales[c][sku][f] / TotalSales[sku][f] * QF[sku][p]

Public Class RepQF
  Private arrQF(,,) As Rep
  Private CustArray(), PeriodArray(), PeriodArrayNew(), ProdArray() As String
  Private db As New sqlDB()
  Private ds As DataSet
  Private dr As DataRow
  Private Complete As Boolean
  Public StartDate, EndDate, Message, sqlBrandTotal, sqlCustomerBrandTotal As String
  Const DECIMALS = 8

  Structure Rep
    Public dblPerc As Double
    Public dblSales As Double
    Public dblQF As Double
  End Structure

  Public Sub New()
    StartDate = Date.Today.ToString("yyyyMM")
    EndDate = Date.Today.ToString("yyyyMM")
    Message = ""
  End Sub

  Public Sub New(ByVal strStartDate As String, ByVal strEndDate As String)
    StartDate = strStartDate
    EndDate = strEndDate
    Message = ""
  End Sub

  Public Function Run(ByVal strStartDate As String, ByVal strEndDate As String) As Boolean
    StartDate = strStartDate
    EndDate = strEndDate
    Return Me.Run()
  End Function

  Public Sub Check()
    Me.PostCheck()
  End Sub

  Public Function Run() As Boolean
    Message = ""
    Complete = False
    'STEPS:
    'pop arr
    'get %
    'delete existing qfs
    'insert new qfs
    'postprocess, check for missing sku's

    'QF[sku][p][c] = CustomerSales[c][sku][f] / TotalSales[sku][f] * QF[sku][p]
    If Me.Pop Then

      If Me.Calulate Then

        If Me.Delete Then

          If Me.Insert Then

            If Me.PostCheck Then
              Complete = True
              Return True
            Else
              Return False
            End If

          Else
            Return False
          End If

        Else
          Return False
        End If

      Else
        Return False
      End If

    Else
      Return False
    End If

  End Function

  Private Function Pop() As Boolean
    Dim b, p, r As Integer

    ds = db.doQueryDS("SELECT stkProductPeriod.mstProdCode +'F' AS mstProdCode FROM stkProductPeriod WHERE dblQF > 0 AND mstPeriod >= '" & Me.StartDate & "' AND mstPeriod <= '" & Me.EndDate & "' GROUP BY stkProductPeriod.mstProdCode ORDER BY stkProductPeriod.mstProdCode")
    ReDim ProdArray(ds.Tables(0).Rows.Count - 1)
    For b = 0 To ds.Tables(0).Rows.Count - 1
      ProdArray(b) = ds.Tables(0).Rows(b)!mstProdCode
    Next

    ds = db.doQueryDS("select '' as strPeriod Union SELECT strPeriod FROM mstPeriod WHERE strPeriod >= '" & Me.StartDate & "' AND strPeriod <= '" & Me.EndDate & "' ORDER BY strPeriod  ")
    ReDim PeriodArrayNew(ds.Tables(0).Rows.Count - 1)
    For p = 0 To ds.Tables(0).Rows.Count - 1
      PeriodArrayNew(p) = ds.Tables(0).Rows(p)!strPeriod
    Next

    ds = db.doQueryDS("select '' as strPeriod Union SELECT strPeriod FROM mstPeriod WHERE strPeriod >= '" & CInt(Me.StartDate) - 100 & "' AND strPeriod <= '" & CInt(Me.EndDate) - 100 & "' ORDER BY strPeriod  ")
    ReDim PeriodArray(ds.Tables(0).Rows.Count - 1)
    For p = 0 To ds.Tables(0).Rows.Count - 1
      PeriodArray(p) = ds.Tables(0).Rows(p)!strPeriod             'min 12 + (1 x strPeriod='0')
    Next

    ds = db.doQueryDS("SELECT mstCustomer.strCustomerNo FROM mstBranch INNER JOIN dwfSales INNER JOIN mstCustomer ON dwfSales.refCustomerNo = mstCustomer.strCustomerNo ON mstBranch.BranchID = mstCustomer.refBranchID INNER JOIN stkProductPeriod ON left(dwfSales.refProdCode,5) = stkProductPeriod.mstProdCode WHERE stkProductPeriod.dblQF <> 0 AND refPeriod >= '" & CInt(Me.StartDate) - 200 & "' AND refPeriod <= '" & CInt(Me.EndDate) & "' AND (NOT (mstBranch.strCode LIKE '%x%' OR mstBranch.strCode LIKE '%y%')) GROUP BY mstCustomer.strCustomerNo HAVING (SUM(dwfSales.dbl9l) <> 0) ORDER BY mstCustomer.strCustomerNo") '
    ReDim CustArray(ds.Tables(0).Rows.Count - 1)
    For r = 0 To ds.Tables(0).Rows.Count - 1
      CustArray(r) = ds.Tables(0).Rows(r)!strCustomerNo
    Next

    ReDim arrQF(b, p, r)

    db.doQueryDS("DELETE FROM tmpQFSC")
    db.doQueryDS("INSERT INTO tmpQFSC (strProdCodeF, strPeriod, dblQF) SELECT mstProdCode + 'F', mstPeriod, dblQF FROM stkProductPeriod WHERE dblQF > 0 AND mstPeriod >= '" & Me.StartDate & "' AND mstPeriod <= '" & Me.EndDate & "'")
    Return True

  End Function

  Private Function Calulate() As Boolean
    Dim t, p, b, r, tmp As Double
    Dim sql, sql2, sql3, sql4, union, strStartDate, strEndDate As String
    Dim ds2 As DataSet
    Dim dr2 As DataRow

    sql = ""
    sql2 = ""
    sql3 = ""
    sql4 = ""
    union = ""

    Try
      'new: 01/04/2009: get the Sales for the last completed fiscal of the brands principal
      ds2 = db.doQueryDS(Me.get_Principal)
      For Each dr2 In ds2.Tables(0).Rows
        tmp = CInt(Me.StartDate.Substring(0, 4))
        If CInt(tmp & dr2!strFYEnd) > CInt(Me.StartDate) Then
          tmp = tmp - 1
        End If
        If dr2!strFYStart > dr2!strFYEnd Then
          strStartDate = (tmp - 1) & dr2!strFYStart
          strEndDate = tmp & dr2!strFYEnd
        Else
          strStartDate = tmp & dr2!strFYStart
          strEndDate = tmp & dr2!strFYEnd
        End If
        sql &= Me.get_Sales_Totals_by_Period_Union(strStartDate, strEndDate, dr2!PrincipalID, union)
        sql2 &= Me.get_Cust_Sales_Totals_by_Period_Union(strStartDate, strEndDate, dr2!PrincipalID, union)
        sql3 &= Me.get_Brand_Sales_Totals_by_Period_Union(strStartDate, strEndDate, dr2!PrincipalID, union)
        sql4 &= Me.get_Brand_Cust_Sales_Totals_by_Period_Union(strStartDate, strEndDate, dr2!PrincipalID, union)

        union = " UNION "
      Next

      sql &= " ORDER BY strProdCodeF"
      sql2 &= " ORDER BY strProdCodeF, mstCustomer.strCustomerNo"
      sql3 &= " ORDER BY strBrand"
      sql4 &= " ORDER BY strBrand, mstCustomer.strCustomerNo"

      Me.sqlBrandTotal = sql3
      Me.sqlCustomerBrandTotal = sql4


      dr2 = Nothing
      ds2 = Nothing

      Try
        ds = db.doQueryDS(sql2)
        For Each dr In ds.Tables(0).Rows
          Try

            b = Array.IndexOf(ProdArray, dr!strProdCodeF)
            p = 0 'Array.IndexOf(PeriodArray, dr!strPeriod)
            r = Array.IndexOf(CustArray, dr!strCustomerNo)

            If b > -1 And r > -1 Then
              arrQF(b, p, r).dblSales = Math.Round(db.nz(dr!dblSales9L, 0.0), DECIMALS, MidpointRounding.AwayFromZero)
            End If

          Catch ex As Exception
            Message &= "||" & ex.Message

          End Try
        Next

      Catch ex As Exception
        Message &= "||" & ex.Message
        Return False
      End Try

      ds = db.doQueryDS(sql)
      For Each dr In ds.Tables(0).Rows
        t = db.nz(dr!dblSales9L, 0.0)
        b = Array.IndexOf(ProdArray, dr!strProdCodeF)
        p = 0 'Array.IndexOf(PeriodArray, dr!strPeriod)
        If b > -1 And t <> 0 Then
          For r = 0 To CustArray.Length - 1
            Try
              If arrQF(b, p, r).dblSales <> 0 Then
                arrQF(b, p, r).dblPerc = Math.Round(arrQF(b, p, r).dblSales / t, DECIMALS + 2, MidpointRounding.AwayFromZero)
              End If
            Catch ex As Exception                                                'round DECIMALS dec places: .Perc = 0.087555 == 8.75%
              Message &= "||" & ex.Message
            End Try
          Next 'i
        End If
        db.doQueryDS("DELETE FROM tmpQFSC WHERE strProdCodeF = '" & dr!strProdCodeF & "'")

      Next           'dr

      ds = db.doQueryDS(Me.get_Prod_Totals_by_Period(Me.StartDate, Me.EndDate))       'prod qf per period forward
      For Each dr In ds.Tables(0).Rows
        t = db.nz(dr!dblQF, 0.0)
        b = Array.IndexOf(ProdArray, dr!mstProdCodeF)
        p = Array.IndexOf(PeriodArrayNew, dr!strPeriod)       'periodNew!
        For r = 0 To CustArray.Length - 1
          Try
            arrQF(b, p, r).dblQF = Math.Round(arrQF(b, 0, r).dblPerc * t, DECIMALS, MidpointRounding.AwayFromZero)
            arrQF(b, p, r).dblPerc = arrQF(b, 0, r).dblPerc
          Catch ex As Exception
            Message &= "||" & ex.Message
          End Try
        Next          'i

      Next

      'Me.Check()
      'Return False

      Return True
    Catch ex As Exception
      Message &= "||" & ex.Message
      Return False
    End Try

  End Function

  Private Function Delete() As Boolean
    Try
      db.doQueryDS("DELETE FROM dwfQF WHERE strPeriod >='" & Me.StartDate & "' And strPeriod <='" & Me.EndDate & "' ")

      Return True
    Catch ex As Exception
      Message &= "||" & ex.Message
      Return False
    End Try

  End Function

  Private Function Insert() As Boolean
    Dim ip As Integer
    Dim ib, b, p, r, ir As String
    Dim iQF As Rep

    Try

      For ip = 1 To PeriodArrayNew.Length - 1                  'skip the 1rst period=''
        p = PeriodArrayNew(ip)
        For Each b In ProdArray
          For Each r In CustArray
            Try
              ib = Array.IndexOf(ProdArray, b)
              ir = Array.IndexOf(CustArray, r)
              iQF = arrQF(ib, ip, ir)
              If iQF.dblPerc <> 0 Or iQF.dblQF <> 0 Then
                db.doQueryDS(Me.insert_QF(b, p, r, Math.Round(iQF.dblQF, DECIMALS, MidpointRounding.AwayFromZero), Math.Round(iQF.dblPerc, DECIMALS + 2, MidpointRounding.AwayFromZero)))
              End If

            Catch ex As Exception
              Message &= "||" & ex.Message
            End Try
          Next
        Next
      Next

      db.doQueryDS("DELETE FROM dwfQF WHERE strPeriod = '' OR strProdCode = '' OR strCustomerNo = ''")

      Return True
    Catch ex As Exception
      Return False
    End Try

  End Function

  Private Function PostCheck() As Boolean
    Dim b, p, sku, c, tmp, perc, qf, i As Double
    Dim sql, sql2, sql3, union, strStartDate, strEndDate, tmpProdCode, curBrandID As String
    Dim ds2 As DataSet
    Dim dr2 As DataRow
    Dim dr3 As DataRow
    Dim BrandArray(28) As Integer
    Dim BrandRow(28) As DataRow
    'Dim CustomerBrandArray() As Integer
    Dim CustomerRow() As DataRow

    Try
      'foreach QF[sku][p] check if records exist in QF[sku][p]
      'check 1: check all periods in stkPP vs dwdQF

      'INI load totals per brand
      ds = db.doQueryDS(Me.sqlBrandTotal)
      ReDim BrandArray(ds.Tables(0).Rows.Count - 1)
      ReDim BrandRow(ds.Tables(0).Rows.Count - 1)
      For b = 0 To ds.Tables(0).Rows.Count - 1
        BrandArray(b) = ds.Tables(0).Rows(b)!BrandID
        BrandRow(b) = ds.Tables(0).Rows(b)
      Next

      ds = db.doQueryDS(Me.sqlCustomerBrandTotal)
      'ReDim CustomerBrandArray(ds.Tables(0).Rows.Count - 1)
      ReDim CustomerRow(ds.Tables(0).Rows.Count - 1)
      For c = 0 To ds.Tables(0).Rows.Count - 1
        'CustomerBrandArray(c) = ds.Tables(0).Rows(c)!BrandID
        CustomerRow(c) = ds.Tables(0).Rows(c)
      Next


      ds = db.doQueryDS("SELECT tmpQFSC.strProdCodeF, tmpQFSC.strPeriod, mstProduct.refBrandID, ROUND(tmpQFSC.dblQF * mstProduct.intPack * mstProduct.intSize / 9000, 8) AS qf FROM tmpQFSC INNER JOIN mstProduct ON tmpQFSC.strProdCodeF = mstProduct.strProdCode")
      For Each dr In ds.Tables(0).Rows
        'get brand TotalSales[f]
        b = Array.IndexOf(BrandArray, dr!refBrandID)
        For i = 0 To CustomerRow.Length - 1
          If CustomerRow(i)!BrandID = dr!refBrandID Then
            perc = Math.Round(CustomerRow(i)!dblSales9L / BrandRow(b)!dblSales9L, DECIMALS, MidpointRounding.AwayFromZero)
            qf = Math.Round(perc * dr!qf, DECIMALS, MidpointRounding.AwayFromZero)
            db.doQueryDS(Me.insert_QF(dr!strProdCodeF, dr!strPeriod, CustomerRow(i)!strCustomerNo, Math.Round(qf, DECIMALS, MidpointRounding.AwayFromZero), Math.Round(perc, DECIMALS + 2, MidpointRounding.AwayFromZero)))

          End If
        Next
        db.doQueryDS("DELETE FROM tmpQFSC WHERE strProdCodeF = '" & dr!strProdCodeF & "' AND strPeriod = '" & dr!strPeriod & "'")
      Next
      'check 2 check period by period in stkPP vs dwdQF

      Return True
    Catch ex As Exception
      Return False
    End Try
  End Function
  '	////	SQL Functions

  Public Function get_Principal()
    Return "SELECT DISTINCT mstPrincipal.PrincipalID, mstPrincipal.strPrincipal, mstPrincipal.strFYStart, mstPrincipal.strFYEnd " & _
        "FROM mstPrincipal INNER JOIN mstBrand ON mstPrincipal.PrincipalID = mstBrand.refPrincipalID " & _
        "WHERE mstBrand.blnActive = 1"
  End Function

  Public Function get_Sales_Totals_by_Period_Union(ByVal strStartDate As String, ByVal strEndDate As String, ByVal refPrincipalID As Integer, ByVal Union As String) As String
    Return Union & "SELECT Left([strProdCode],5)+'F' AS strProdCodeF, Round(Sum(dwfSales.dbl9l)," & DECIMALS & ") AS dblSales9L " & _
     "FROM (mstCustomer INNER JOIN (dwfSales INNER JOIN (mstProduct INNER JOIN mstBrand ON mstProduct.refBrandID = mstBrand.BrandID) ON dwfSales.refProdCode = mstProduct.strProdCode) ON mstCustomer.strCustomerNo = dwfSales.refCustomerNo) INNER JOIN mstBranch ON mstCustomer.refBranchID = mstBranch.BranchID " & _
     "WHERE (NOT (mstBranch.strCode LIKE '%x%' Or mstBranch.strCode LIKE '%y%')) AND refPeriod >= '" & strStartDate & "' And refPeriod <= '" & strEndDate & "' AND mstBrand.refPrincipalID = " & refPrincipalID & " " & _
     "GROUP BY LEFT(mstProduct.strProdCode, 5) + 'F' "
    '"ORDER BY mstBrand.strBrand"'mstProduct.blnActive=1 AND mstBrand.blnActive = 1 AND 
  End Function

  Public Function get_Cust_Sales_Totals_by_Period_Union(ByVal strStartDate As String, ByVal strEndDate As String, ByVal refPrincipalID As Integer, ByVal Union As String) As String
    'v 1.2.6 - Changed repQF to Customer QF
    Return Union & "SELECT LEFT(mstProduct.strProdCode, 5) + 'F' AS strProdCodeF, mstCustomer.strCustomerNo, mstProduct.strProductDesc, ROUND(SUM(dwfSales.dbl9l), 8) AS dblSales9L " & _
      "FROM mstCustomer INNER JOIN dwfSales INNER JOIN mstProduct INNER JOIN mstBrand ON mstProduct.refBrandID = mstBrand.BrandID ON dwfSales.refProdCode = mstProduct.strProdCode ON mstCustomer.strCustomerNo = dwfSales.refCustomerNo INNER JOIN mstBranch ON mstCustomer.refBranchID = mstBranch.BranchID " & _
      "WHERE (NOT (mstBranch.strCode LIKE '%x%' OR mstBranch.strCode LIKE '%y%')) AND (refPeriod >= '" & strStartDate & "') AND (refPeriod <= '" & strEndDate & "') AND  mstBrand.refPrincipalID = " & refPrincipalID & " " & _
      "GROUP BY LEFT(mstProduct.strProdCode, 5) + 'F', mstProduct.strProductDesc, mstCustomer.strCustomerNo "

    'Return "SELECT LEFT(dwfSales.refProdCode, 5) + 'F' AS strProdCodeF, mstCustomer.strCustomerNo, mstCustomer.strCustomerName, ROUND(SUM(dwfSales.dbl9l), " & DECIMALS & ") AS dblSales9L " & _
    ' "FROM mstCustomer INNER JOIN dwfSales ON mstCustomer.strCustomerNo = dwfSales.refCustomerNo INNER JOIN mstBranch ON mstCustomer.refBranchID = mstBranch.BranchID " & _
    ' "WHERE (dwfSales.refPeriod >= '" & strStartDate & "') AND (dwfSales.refPeriod <= '" & strEndDate & "') AND (NOT (mstBranch.strCode LIKE '%x%' OR  mstBranch.strCode LIKE '%y%')) AND ((SELECT SUM(dblQF) AS dblQF FROM stkProductPeriod WHERE mstProdCode = LEFT(dwfSales.refProdCode, 5)) > 0) " & _
    ' "GROUP BY mstCustomer.strCustomerNo, mstCustomer.strCustomerName, LEFT(dwfSales.refProdCode, 5) " & _
    ' "ORDER BY strProdCodeF, mstCustomer.strCustomerNo"
  End Function

  Public Function get_Prod_Totals_by_Period(ByVal strStartDate As String, ByVal strEndDate As String) As String
    Return "SELECT stkProductPeriod.mstProdCode + 'F' AS mstProdCodeF, mstProduct.strProductDesc, ROUND(SUM(stkProductPeriod.dblQF * mstProduct.intPack * mstProduct.intSize / 9000), " & DECIMALS & ") AS dblQF, stkProductPeriod.mstPeriod AS strPeriod " & _
     "FROM stkProductPeriod INNER JOIN mstProduct ON stkProductPeriod.mstProdCode + 'F' = mstProduct.strProdCode " & _
     "GROUP BY stkProductPeriod.mstPeriod, stkProductPeriod.mstProdCode, mstProduct.strProductDesc " & _
     "HAVING stkProductPeriod.mstPeriod >= '" & strStartDate & "' And stkProductPeriod.mstPeriod <= '" & strEndDate & "' AND SUM(stkProductPeriod.dblQF) > 0 " & _
     "ORDER BY mstProdCodeF, mstPeriod"
  End Function

  Public Function insert_QF(ByVal strProdCode As String, ByVal strPeriod As String, ByVal strCustomerNo As String, ByVal dblQF9L As Double, ByVal dblPercentage As Double) As String
    Return "INSERT INTO dwfQF (strPeriod, strProdCode, strCustomerNo, dblBudget9l, dblPercentage, strLastUser, dtStamp) " & _
    "VALUES ('" & strPeriod & "', '" & strProdCode & "', '" & strCustomerNo & "', " & dblQF9L & ", " & dblPercentage & ", '" & WebConfigurationManager.AppSettings("UNAME") & "','" & dbDate() & "')"
  End Function

  'v1.2.7 - 
  Public Function get_Brand_Sales_Totals_by_Period_Union(ByVal strStartDate As String, ByVal strEndDate As String, ByVal refPrincipalID As Integer, ByVal Union As String) As String
    Return Union & "SELECT mstBrand.BrandID, mstBrand.strBrand, Round(Sum(dwfSales.dbl9l)," & DECIMALS & ") AS dblSales9L " & _
     "FROM (mstCustomer INNER JOIN (dwfSales INNER JOIN (mstProduct INNER JOIN mstBrand ON mstProduct.refBrandID = mstBrand.BrandID) ON dwfSales.refProdCode = mstProduct.strProdCode) ON mstCustomer.strCustomerNo = dwfSales.refCustomerNo) INNER JOIN mstBranch ON mstCustomer.refBranchID = mstBranch.BranchID " & _
     "WHERE (NOT (mstBranch.strCode LIKE '%x%' Or mstBranch.strCode LIKE '%y%')) AND refPeriod >= '" & strStartDate & "' And refPeriod <= '" & strEndDate & "' AND mstBrand.refPrincipalID = " & refPrincipalID & " " & _
     "GROUP BY mstBrand.BrandID, mstBrand.strBrand "
    '"ORDER BY mstBrand.strBrand"'mstProduct.blnActive=1 AND mstBrand.blnActive = 1 AND 
  End Function

  Public Function get_Brand_Cust_Sales_Totals_by_Period_Union(ByVal strStartDate As String, ByVal strEndDate As String, ByVal refPrincipalID As Integer, ByVal Union As String) As String
    'v 1.2.7 - Changed repQF to Customer QF
    Return Union & "SELECT mstBrand.BrandID, mstBrand.strBrand, mstCustomer.strCustomerNo, Round(Sum(dwfSales.dbl9l)," & DECIMALS & ") AS dblSales9L " & _
     "FROM (mstCustomer INNER JOIN (dwfSales INNER JOIN (mstProduct INNER JOIN mstBrand ON mstProduct.refBrandID = mstBrand.BrandID) ON dwfSales.refProdCode = mstProduct.strProdCode) ON mstCustomer.strCustomerNo = dwfSales.refCustomerNo) INNER JOIN mstBranch ON mstCustomer.refBranchID = mstBranch.BranchID " & _
     "WHERE (NOT (mstBranch.strCode LIKE '%x%' Or mstBranch.strCode LIKE '%y%')) AND refPeriod >= '" & strStartDate & "' And refPeriod <= '" & strEndDate & "' AND mstBrand.refPrincipalID = " & refPrincipalID & " " & _
     "GROUP BY mstBrand.BrandID, mstBrand.strBrand, mstCustomer.strCustomerNo "
  End Function

  Public Function getCheckQF1(ByVal strStartDate As String, ByVal strEndDate As String, ByVal tmpProdCode As String)
    Return "SELECT stkProductPeriod.mstProdCode + 'F' AS mstProdCodeF, stkProductPeriod.mstPeriod, ROUND(SUM(stkProductPeriod.dblQF * dwdProduct.intPack * dwdProduct.intSize / 9000), " & DECIMALS & ") AS qf, dwdProduct.strPrincipalFYStart, dwdProduct.strPrincipalFYEnd, dwdProduct.strBrand, dwdProduct.refBrandID " & _
        "FROM dwdProduct, stkProductPeriod " & _
        "WHERE(mstPeriod >= '" & strStartDate & "') AND (mstPeriod <= '" & strEndDate & "') " & _
        "GROUP BY stkProductPeriod.mstProdCode + 'F', stkProductPeriod.mstPeriod, dwdProduct.strPrincipalFYStart, dwdProduct.strPrincipalFYEnd, dwdProduct.strBrand, dwdProduct.refBrandID " & _
        "HAVING SUM(stkProductPeriod.dblQF) > 0 AND stkProductPeriod.mstProdCode + 'F' NOT IN(" & tmpProdCode & ") " & _
        "ORDER BY mstProdCodeF, mstPeriod"
  End Function

  Public Function getCheckQF2(ByVal strStartDate As String, ByVal strEndDate As String)
    Return "SELECT strProdCode FROM dwfQF WHERE (strPeriod >= '" & strStartDate & "') AND (strPeriod <= '" & strEndDate & "') GROUP BY strProdCode"
  End Function
End Class