Imports RGBC_Forecast.SystemFunctions
Imports RGBC_Forecast.ProjectFunctions
Imports RGBC_Forecast.db
Imports RGBC_Forecast.Branding

'20101005 - v0.0.1 - new
'20101008 - v0.0.2 - Apply/GenerateBrand function complete. Using dummy dwfQF2 table to simulate values. 
'                  - g4 - stkPP UPDATE not active

'Variables: F: a Previous Fiscal, P: Period, B: Brand, Sku: Product, R: Region, C: Customer
'The Formula: QF[P][Sku][C] = %CustomerRegionalBrandContribution * %ProductRegionApplicableContribution * QFA 
'                           = (Sales[F][B][C] / Sales[F][B][R]) * (Sales[F][Sku] / SalesApplicable[F]) * QFA[P][B][R]
'methods needed: 
'public GenerateAll(FinYear)
'public GeneratePrincipal(FinYear, PprincipalID)
'public GenerateBrand(FinYear, BrandID)
'
'privat Generate(FinYear, BrandID)
'privat sqlGetSalesByCustomer(FinYear, BrandID) {Sales[F][B][C]} & {Sales[F][B][R]}
'privat sqlGetApplicableSalesBySku(FinYear, BrandID) {Sales[F][Sku]} & {SalesApplicable[F]}
'privat sqlGetQFA(FinYear, BrandID) {QFA[P][B][R]}

'privat CopyBrand(Period, fromBrand, toBrand)
'privat CopyQF(Period, fromProdCode, toProdCode)

Public Class QFGen 'erator

  'running variables used in sql
  Private FinYear As Integer  'RGBC FinYear
  Private BrandID As Integer = 0
  Private Fiscal, strProdCodes As String  'ref to start and end date as string

  Private DateFinYearStart As Date
  Private DateFinYearEnd As Date
  Private DateFiscalYearStart As Date
  Private DateFiscalYearEnd As Date

  Private dblBrandTotal As Double = 0.0


  Private intFullYearPeriods As Integer = 12 ' how many period to generate QFs for from/including start period
  Public arrQFA, arrProductSales, arrCustomerSales, arrRegion, arrPeriod As Collection

  Private ds As DataSet
  Private dr As DataRow

  Sub New(ByVal FinYear As Integer, ByVal BrandID As Integer)
    Me.FinYear = FinYear
    Me.BrandID = BrandID

    'get fin start and end dates
    dr = db.getRow("SELECT * FROM mstCompany WHERE CompanyID = 1")
    If dr!strFYStart = "01" Then
      Me.DateFinYearStart = New Date(Me.FinYear, CInt(dr!strFYStart), 1)
      Me.DateFinYearEnd = New Date(Me.FinYear, CInt(dr!strFYEnd), 1)
    Else
      Me.DateFinYearStart = New Date(Me.FinYear - 1, CInt(dr!strFYStart), 1)
      Me.DateFinYearEnd = New Date(Me.FinYear, CInt(dr!strFYEnd), 1)
    End If

    'get last completed fiscal
    Me.DateFiscalYearStart = getLastCompletedFiscalStart()
    Me.DateFiscalYearEnd = getLastCompletedFiscalEnd()

    Me.Fiscal = " AND strPeriod >= '" & Me.DateFinYearStart.ToString("yyyyMM") & "' AND strPeriod <= '" & Me.DateFinYearEnd.ToString("yyyyMM") & "'"

    arrRegion = New Collection
    arrRegion = Branding.LoadCustomers()
  End Sub

  Public Sub GenerateBrand()
    'used in Adjustments.aspx.Apply()
    'regenerates QF for unApplied QFAs

    'Me.Generate(Me.BrandID, True) ' should be true for this method.
    Me.Generate(Me.BrandID, True)

  End Sub

  Private Function getProducts() As Collection

    'method of access: arrProductSales("Total") = dr
    'INI
    Dim comma As String = ""
    Dim dblApplicableBrandTotal As Double = 0.0

    strProdCodes = ""
    arrProductSales = New Collection


    Me.Fiscal = " AND refPeriod >= '" & Me.DateFiscalYearStart.ToString("yyyyMM") & "' AND refPeriod <= '" & Me.DateFiscalYearEnd.ToString("yyyyMM") & "'"
    dr = db.getRow("SELECT ROUND(SUM(dbl9lTotal),4) as dblBrandTotal FROM dwdProduct INNER JOIN (dwfSales INNER JOIN dwdCustomer ON dwfSales.refCustomerNo = dwdCustomer.strCustomerNo) ON dwdProduct.strProdCode = dwfSales.refProdCode WHERE blnQF <> 2 AND refBrandID = " & Me.BrandID & " " & Me.Fiscal) 'needs to have same selection criteria as sqlGetSalesByCustomer()
    If dr Is Nothing Then
      Throw New Exception("No sales for this Brand @ g2 sqlGetSales()")
    Else
      Me.dblBrandTotal = db.nz(dr!dblBrandTotal, 1)
    End If

    ds = db.doQuery(QFGen.sqlGetApplicableSalesBySku(Me.Fiscal, Me.BrandID))
    'PROCESS
    dblApplicableBrandTotal = db.nz(ds.Tables(0).Rows(0)!dbl9lTotal, 0)
    If dblApplicableBrandTotal = 0 Then Throw New Exception("No sales for this Brand @ g2 sqlGetApplicableSalesBySku()")
    For Each dr In ds.Tables(0).Rows
      dr!percContribution = Math.Round(dr!dbl9lTotal / dblApplicableBrandTotal, 8)
      arrProductSales.Add(dr, dr!strProdCodeF)
      If dr!strProdCodeF <> "Total" Then
        strProdCodes &= comma & "'" & dr!strProdCodeF & "'"
        comma = ","
      End If
    Next
    Return arrProductSales

  End Function

  'Private, internal use only
  Private Sub Generate(ByVal BrandID As Integer, Optional ByVal blnOnlyApplyChanges As Boolean = False)
    '20101008 - v0.0.2
    'steps: 
    'g1: load QFA
    'g2: load SKU & Brand APLICABLE Sales
    'g3: Process Loop start {foreach Region}
    'g3.1: load Customer & Region Sales
    'g3.2: drop dwfQF
    'g3.3: insert new QFA (INSERT)
    'g3.4: update QFA
    'g4: Apply dwfQF/QFA to CS (UPDATE)

    Dim currentPeriod As String = ""
    Dim currentRegion As String = ""
    Dim strLastUser As String = "Generate QF"
    Dim sql As String
    Dim dtStamp As Date
    Dim arrQF As Collection

    Dim dblRegionTotal As Double = 0
    Dim dblQF, percRegion As Double
    Dim cnt As Integer = 0

    Dim blnStop As Boolean = False

    'g1: get QFA. 

    'method of access: arrQFA(idx1)(idx2) = dr OR arrQFA(key1)(key2) = dr OR any combo of key & idx also arrQFA("Gauteng")("200907")!strCustomerNo = "bleh"
    'INI
    arrQFA = New Collection
    arrQF = New Collection
    arrPeriod = New Collection

    Me.Fiscal = " AND strPeriod >= '" & Me.DateFinYearStart.ToString("yyyyMM") & "' AND strPeriod <= '" & Me.DateFinYearEnd.ToString("yyyyMM") & "'"
    ds = db.doQuery(QFGen.sqlGetQFA(Me.Fiscal, Me.BrandID))
    'PROCESS
    For Each dr In ds.Tables(0).Rows

      If currentRegion <> dr!Region Then
        If arrQF.Count > 0 Then
          arrQFA.Add(arrQF, currentRegion)
        End If

        arrQF = New Collection

      End If

      arrQF.Add(dr, dr!strPeriod)

      currentRegion = dr!Region
      Try
        arrPeriod.Add(dr!strPeriod, CStr(dr!strPeriod))
      Catch argEx As ArgumentException
      Catch ex As Exception
      End Try
    Next
    If arrQF.Count > 0 Then
      arrQFA.Add(arrQF, currentRegion)
    End If  'postread

    'g2: load SKU & Brand APLICABLE Sales 
    arrProductSales = Me.getProducts()



    cnt = 0
    'g3: Main Loop
    For idxRegion = arrRegion.Count To 1 Step -1

      'g3.1: load Customer & Region Sales

      'method of access: 
      'INI
      arrCustomerSales = New Collection
      currentRegion = arrRegion(idxRegion)!Region
      Me.Fiscal = " AND refPeriod >= '" & Me.DateFiscalYearStart.ToString("yyyyMM") & "' AND refPeriod <= '" & Me.DateFiscalYearEnd.ToString("yyyyMM") & "'"
      ds = db.doQuery(QFGen.sqlGetSalesByCustomer(Me.Fiscal, Me.BrandID, currentRegion))
      'PROCESS
      dblRegionTotal = db.nz(ds.Tables(0).Rows(0)!dbl9lTotal, 0.0)
      If dblRegionTotal = 0 Then 'Throw New Exception("No sales for this Region: " & currentRegion)
        ds.Tables(0).Rows(0)!dbl9lTotal = 0
        ds.Tables(0).Rows(0)!percRegion = 1
        arrCustomerSales.Add(ds.Tables(0).Rows(0), ds.Tables(0).Rows(0)!strCustomerNo)

        ds.Tables(0).Rows(0)!strCustomerNo = arrRegion(idxRegion)!strCustomerNo
        ds.Tables(0).Rows(0)!strCustomerName = "Dummy Region Customer"
        ds.Tables(0).Rows(0)!Region = arrRegion(idxRegion)!Region
        ds.Tables(0).Rows(0)!dbl9lTotal = 0
        ds.Tables(0).Rows(0)!percRegion = 1
        arrCustomerSales.Add(ds.Tables(0).Rows(0), ds.Tables(0).Rows(0)!strCustomerNo)

      Else
        For Each dr In ds.Tables(0).Rows
          dr!percRegion = Math.Round(dr!dbl9lTotal / dblRegionTotal, 8)
          arrCustomerSales.Add(dr, dr!strCustomerNo)
        Next
      End If

      'L1
      For idxPeriod = 1 To arrPeriod.Count
        ' ApplyOnly = 1 & applied = 1 == dont run
        ' ApplyOnly = 1 & applied = 0 == run
        ' ApplyOnly = 0 & applied = 1 == run
        ' ApplyOnly = 0 & applied = 0 == run
        Try
          If blnOnlyApplyChanges = True And arrQFA(arrRegion(idxRegion)!Region)(arrPeriod(idxPeriod))!blnApplied = True Then
            'don't run
          Else
            'run


            'L2
            For idxProduct = 2 To arrProductSales.Count
              'L2 INI: ignore product(idx=1) = "Total"
              Dim drProduct As DataRow = arrProductSales(idxProduct)

              dtStamp = Date.Now

              'L2 Process
              sql = ""

              'L3
              If CInt(arrQFA(arrRegion(idxRegion)!Region)(arrPeriod(idxPeriod))!dblQF) <> CInt(0) Then 'wtf?! why is 0.0 and 0 NOT THE SAME in an IF STATEMENT?! L.A.M.E!
                For idxCustomer = 2 To arrCustomerSales.Count
                  'L3 INI: ignore customer(idx=1) = "Total" 
                  Dim drCustomer As DataRow = arrCustomerSales(idxCustomer)
                  'L3 Process

                  'g3.2: drop dwfQF
                  sql = "DELETE FROM " & My.Settings.dwfQFTable & " WHERE strPeriod = '" & arrQFA(arrRegion(idxRegion)!Region)(arrPeriod(idxPeriod))!strPeriod & "' AND strProdCode = '" & drProduct!strProdCodeF & "' AND strCustomerNo = '" & drCustomer!strCustomerNo & "'; "
                  If db.doQuery(sql) = -1 Then Return

                  'g3.3: insert new QFA (INSERT)
                  percRegion = drCustomer!percRegion * drProduct!percContribution
                  dblQF = arrQFA(arrRegion(idxRegion)!Region)(arrPeriod(idxPeriod))!dblQF * percRegion

                  'note: keep on appending the sql
                  sql = "INSERT INTO " & My.Settings.dwfQFTable & " (strPeriod, strProdCode, strCustomerNo, dblBudget9l, percRegion, percProduct, strLastUser, dtStamp) " & _
                         "VALUES ('" & arrQFA(arrRegion(idxRegion)!Region)(arrPeriod(idxPeriod))!strPeriod & _
                         "', '" & drProduct!strProdCodeF & _
                         "', '" & drCustomer!strCustomerNo & _
                         "', ROUND(" & dblQF & _
                         ",8), ROUND(" & percRegion & _
                         ",8), ROUND(" & drProduct!percContribution & _
                         ",8), '" & strLastUser & _
                         "', '" & dtStamp.ToString("yyyyMMddHHmm") & "'); "

                  db.doQuery(sql)
                  cnt += 1

                  If blnStop = True Then Return ' debug stop measure

                  'If cnt > arrCustomerSales.Count / 50 Then
                  '  db.doQueryAsync(sql)
                  '  sql = ""
                  '  cnt = 0
                  'End If
                Next 'eoL3 / next Customer
              End If 'eoIf <> 0


              'L2 POST
              'If sql <> "" Then db.doQueryAsync(sql)


            Next 'eoL2 / next Product

            'L1 POST

            'g3.4: update QFA
            Dim blnApplied As String = "0"
            dr = db.getRow("SELECT dwdCustomer.strRegionDesc2 AS Region, strPeriod, Count(dwdCustomer.strCustomerNo) AS intRecords, Round(Sum(dblBudget9l),4) AS dblBudget9l, Round(Sum(percRegion),4) AS percRegion " & _
"FROM (" & My.Settings.dwfQFTable & " INNER JOIN dwdProduct ON " & My.Settings.dwfQFTable & ".strProdCode = dwdProduct.strProdCode) INNER JOIN dwdCustomer ON " & My.Settings.dwfQFTable & ".strCustomerNo = dwdCustomer.strCustomerNo " & _
"WHERE dwdProduct.blnQF = 1 And dwdProduct.refBrandID = " & Me.BrandID & " And dwdCustomer.strRegionDesc2='" & arrQFA(arrRegion(idxRegion)!Region)(arrPeriod(idxPeriod))!Region & "' AND strPeriod='" & arrQFA(arrRegion(idxRegion)!Region)(arrPeriod(idxPeriod))!strPeriod & "' " & _
"GROUP BY dwdCustomer.strRegionDesc2, strPeriod")
            Try
              If Math.Round(dr!dblBudget9L, 0, MidpointRounding.AwayFromZero) = Math.Round(arrQFA(arrRegion(idxRegion)!Region)(arrPeriod(idxPeriod))!dblQF, 0, MidpointRounding.AwayFromZero) Then
                blnApplied = "1"
              End If
              db.doQuery("UPDATE dwfQFAdjustment SET blnApplied = '" & blnApplied & "', strLog = '' " & _
                         "WHERE strPeriod = '" & arrQFA(arrRegion(idxRegion)!Region)(arrPeriod(idxPeriod))!strPeriod & "'  " & _
                         "AND refBrandID =" & Me.BrandID & " " & _
                         "AND strCustomerNo = '" & arrQFA(arrRegion(idxRegion)!Region)(arrPeriod(idxPeriod))!strCustomerNo & "'")
            Catch nrEx As NullReferenceException
              'expected, dr might be null if no records have been generated for [][][]
            End Try


            '#If DEBUG Then
            My.Response.Write("<BR>dwfQF: " & Date.Now.ToString("yyyy/MM/dd HH:mm:ss") & ": " & currentRegion & ", " & cnt)
            '#End If
          End If 'if Apply
        Catch Ex As Exception
          'if QFA[R][P] does not exist
          'delete all qf for that region and products
          For idxCustomer = 2 To arrCustomerSales.Count
            'L3 INI: ignore customer(idx=1) = "Total" 
            Dim drCustomer As DataRow = arrCustomerSales(idxCustomer)
            'drop dwfQF
            sql = "DELETE FROM " & My.Settings.dwfQFTable & " WHERE strPeriod = '" & arrPeriod(idxPeriod) & "' AND strProdCode IN(" & strProdCodes & ") AND strCustomerNo = '" & drCustomer!strCustomerNo & "'; "
            db.doQuery(sql)
            'slight problem: UPDATING stkPP
          Next
        End Try

      Next 'oeL1 / next period

    Next ' eoFor g3 / next Region


    'g4: Apply QFA to CS (UPDATE)
    'notes: SC[P][Sku] =  arrCustomer["Total"] * arrProduct[Sku] * QFA[P][Sku]["Total"]. assume that the record already exist in SC

    '20101008 - v0.0.2 - g4 - UPDATE stkPP inactive
    '20110628 - v1.0.2 - UPDATE stkPP active
    '#If DEBUG Then

    dblQF = 0
    For idxPeriod = 1 To arrPeriod.Count
      For idxProduct = 2 To arrProductSales.Count
        '#If DEBUG Then
        My.Response.Write("<BR>stkPP: " & Date.Now.ToString("yyyy/MM/dd HH:mm:ss") & ": " & currentRegion & ", " & cnt)
        '#End If
        'INI: ignore product(idx=1) = "Total"
        Dim drProduct As DataRow = arrProductSales(idxProduct)

        dr = db.getRow("SELECT COUNT(*) as intRecords, Round(SUM(dblBudget9l),8) as dblBudget9L FROM " & My.Settings.dwfQFTable & " WHERE strPeriod = '" & arrPeriod(idxPeriod) & "' AND strProdCode IN ('" & drProduct!strProdCodeF & "')")
        dblQF = 0.0
        If Not dr Is Nothing Then dblQF = nz(dr!dblBudget9L, 0.0)

        db.doQuery("UPDATE stkProductPeriod SET dblQF = Round(" & dblQF & "*9000/" & drProduct!intSize * drProduct!intPack & ",4)" & _
                 "WHERE mstPeriod = '" & DateAdd(DateInterval.Month, idxPeriod - 1, Me.DateFinYearStart).ToString("yyyyMM") & "'  " & _
                 "AND mstProdCode =  '" & drProduct!strProdCode5 & "' ")
      Next
    Next
'#End If

    'delete zeros
    sql = "DELETE FROM " & My.Settings.dwfQFTable & " WHERE percRegion = 0 "
    db.doQueryAsync(sql)

  End Sub

  Public Sub UpdateSTK() 'wip

    Dim dblQF As Double = 0
    For idxPeriod = 1 To arrPeriod.Count
      For idxProduct = 2 To arrProductSales.Count
        'INI: ignore product(idx=1) = "Total"
        Dim drProduct As DataRow = arrProductSales(idxProduct)

        dr = db.getRow("SELECT COUNT(*) as intRecords, Round(SUM(dblBudget9l),8) as dblBudget9L FROM " & My.Settings.dwfQFTable & " WHERE strPeriod = '" & arrPeriod(idxPeriod) & "' AND strProdCode IN ('" & drProduct!strProdCodeF & "')")
        dblQF = 0.0
        If Not dr Is Nothing Then dblQF = nz(dr!dblBudget9L, 0.0)

        db.doQuery("UPDATE stkProductPeriod SET dblQF = Round(" & dblQF & "*9000/" & drProduct!intSize * drProduct!intPack & ",4)" & _
                 "WHERE mstPeriod = '" & DateAdd(DateInterval.Month, idxPeriod - 1, Me.DateFinYearStart).ToString("yyyyMM") & "'  " & _
                 "AND mstProdCode =  '" & drProduct!strProdCode5 & "' ")
      Next
    Next

  End Sub

  Public Sub CopyBrand(ByVal CopyBrandID As Integer)
    Dim intRows

    Me.Fiscal = " AND strPeriod >= '" & Me.DateFinYearStart.ToString("yyyyMM") & "' AND strPeriod <= '" & Me.DateFinYearEnd.ToString("yyyyMM") & "'"

    dr = db.getRow("SELECT dwdProduct.strProdCode, Count(dwdCustomer.strCustomerNo) AS CountOfstrCustomerNo, Sum(" & My.Settings.dwfQFTable & ".dblBudget9l) AS dblBudget9l, dwdProduct.blnQF " & _
                    "FROM (dwdCustomer INNER JOIN " & My.Settings.dwfQFTable & " ON dwdCustomer.strCustomerNo = " & My.Settings.dwfQFTable & ".strCustomerNo) INNER JOIN dwdProduct ON " & My.Settings.dwfQFTable & ".strProdCode = dwdProduct.strProdCode " & _
                    "WHERE dwdProduct.refBrandID = " & CopyBrandID & " " & Me.Fiscal & " AND dwdProduct.blnQF = 1" & _
                    "GROUP BY dwdProduct.strProdCode, dwdProduct.blnQF " & _
                    "ORDER BY Sum(" & My.Settings.dwfQFTable & ".dblBudget9l) DESC")
    If Not dr Is Nothing Then
      intRows = CopyProductQF(dr!strProdCode)
      Throw New DataException("Copy Brand has generated " & intRows & " record(s).")
    Else
      Throw New DataException("Copy Brand has no QF.")
    End If
  End Sub

  Public Function CopyProductQF(ByVal CopyProdCode As String) As Integer
    Dim intRows, cnt
    Dim sql As String
    Dim drProduct As DataRow

    intRows = 0

    arrProductSales = getProducts()

    For idxProduct = 2 To arrProductSales.Count
      drProduct = arrProductSales(idxProduct)
      db.doQuery("DELETE FROM " & My.Settings.dwfQFTable & " WHERE strProdCode = '" & drProduct!strProdCodeF & "' AND strPeriod >= '" & Me.DateFinYearStart.ToString("yyyyMM") & "' And strPeriod <= '" & Me.DateFinYearEnd.ToString("yyyyMM") & "'")

      sql = "INSERT INTO " & My.Settings.dwfQFTable & " ( strPeriod, strCustomerNo, strProdCode, dblBudget9l, percRegion, percProduct, strLastUser, dtStamp ) " & _
                "SELECT " & My.Settings.dwfQFTable & ".strPeriod, " & My.Settings.dwfQFTable & ".strCustomerNo, '" & drProduct!strProdCodeF & "' as strXProdCode, Round(dblQF*percRegion/percProduct*1,8) AS dblXBudget9l, Round(percRegion/percProduct,8) AS percXRegion, Round(" & drProduct!percContribution & ",8) AS percXProduct, 'Copy SKU QF' AS strLastUser, '" & Date.Now.ToString("yyyyMMssHHmm") & "' AS dtStamp " & _
                "FROM (dwfQFPlan INNER JOIN ((" & My.Settings.dwfQFTable & " INNER JOIN dwdProduct ON " & My.Settings.dwfQFTable & ".strProdCode = dwdProduct.strProdCode) INNER JOIN dwdCustomer ON " & My.Settings.dwfQFTable & ".strCustomerNo = dwdCustomer.strCustomerNo) ON dwfQFPlan.strPeriod = " & My.Settings.dwfQFTable & ".strPeriod) INNER JOIN dwdCustomer AS dwdCustomer_1 ON (dwfQFPlan.strCustomerNo = dwdCustomer_1.strCustomerNo) AND (dwdCustomer.strRegionDesc2 = dwdCustomer_1.strRegionDesc2) " & _
                "WHERE dwfQFPlan.refBrandID=" & Me.BrandID & " AND dwfQFPlan.strPeriod>='" & Me.DateFinYearStart.ToString("yyyyMM") & "' And dwfQFPlan.strPeriod<='" & Me.DateFinYearEnd.ToString("yyyyMM") & "' AND " & My.Settings.dwfQFTable & ".strProdCode='" & CopyProdCode & "'"
      cnt = db.doQuery(sql)
      If Not cnt Is Nothing Then intRows += cnt


    Next

    'INSERT INTO dwfQF2 ( strPeriod, strProdCode, strCustomerNo, dblBudget9l, percRegion, percProduct, strLastUser, dtStamp )
    'SELECT dwfQF2.strPeriod, '10862F' AS strProdCode, dwfQF2.strCustomerNo, Round([dblQF]*[percRegion]/[percProduct]*1,8) AS dblXBudget9l, Round([percRegion]/[percProduct],8) AS percXRegion, Round(1,8) AS percXProduct, 'Copy SKU QF' AS strLastUser, '201010221200' AS dtStamp
    'FROM (dwfQFPlan INNER JOIN ((dwfQF2 INNER JOIN dwdProduct ON dwfQF2.strProdCode = dwdProduct.strProdCode) INNER JOIN dwdCustomer ON dwfQF2.strCustomerNo = dwdCustomer.strCustomerNo) ON dwfQFPlan.strPeriod = dwfQF2.strPeriod) INNER JOIN dwdCustomer AS dwdCustomer_1 ON (dwfQFPlan.strCustomerNo = dwdCustomer_1.strCustomerNo) AND (dwdCustomer.strRegionDesc2 = dwdCustomer_1.strRegionDesc2)
    'WHERE dwdProduct.refBrandID=47 AND dwfQFPlan.refBrandID=64 AND dwfQFPlan.strPeriod>='201107' And dwfQFPlan.strPeriod<='201206' AND dwfQF2.strProdCode='10868F';


    If intRows Is Nothing Then
      Throw New Exception("Copy Brand has Failed. ")
    Else
      Return intRows
    End If
  End Function


  Private Shared Function sqlGetSalesByCustomer(ByVal Fiscal As String, ByVal BrandID As Integer, ByVal Region As String) As String
    'note: blnQF <> 2 - currently doing all sales {Sales of Applicable & NotApplicable SKUs}
    'note: perContribution needs to be float, Total = 1.0 + ORDER BY percRegion :: Total needs to be the first record
    Return "SELECT dwdCustomer.strCustomerNo, dwdCustomer.strCustomerName, dwdCustomer.strRegionDesc2 AS Region, ROUND(Sum(dwfSales.dbl9lTotal),4) AS dbl9lTotal, 0.0 AS percRegion " & _
           "FROM dwdProduct INNER JOIN (dwfSales INNER JOIN dwdCustomer ON dwfSales.refCustomerNo = dwdCustomer.strCustomerNo) ON dwdProduct.strProdCode = dwfSales.refProdCode " & _
           "WHERE dwdCustomer.strRegionDesc2 = '" & Region & "' AND dwdProduct.blnQF <> 2 AND dwdProduct.refBrandID = " & BrandID & " " & Fiscal & " " & _
           "GROUP BY dwdCustomer.strCustomerNo, dwdCustomer.strCustomerName, dwdCustomer.strRegionDesc2 " & _
           "UNION ALL " & _
           "SELECT 'Total' AS strCustomerNo, 'Total' AS strCustomerName, 'Total' AS Region, ROUND(Sum(dwfSales.dbl9lTotal),4) AS dbl9lTotal, 1.0 AS percRegion " & _
           "FROM dwdProduct INNER JOIN (dwfSales INNER JOIN dwdCustomer ON dwfSales.refCustomerNo = dwdCustomer.strCustomerNo) ON dwdProduct.strProdCode = dwfSales.refProdCode " & _
           "WHERE dwdCustomer.strRegionDesc2 = '" & Region & "' AND dwdProduct.blnQF <> 2 AND dwdProduct.refBrandID = " & BrandID & " " & Fiscal & " " & _
           "ORDER BY percRegion DESC, strCustomerNo ASC "

    'SELECT dwdCustomer.strCustomerNo, dwdCustomer.strCustomerName, dwdCustomer.strRegionDesc2 AS Region, Sum(dwfSales.dbl9lTotal) AS dbl9lTotal, 0 AS percRegion
    'FROM dwdProduct INNER JOIN (dwfSales INNER JOIN dwdCustomer ON dwfSales.refCustomerNo = dwdCustomer.strCustomerNo) ON dwdProduct.strProdCode = dwfSales.refProdCode
    'WHERE (((dwdProduct.refBrandID)=6) AND ((dwdProduct.blnQF)=1) AND ([dwfSales].[refPeriod]>='200807' And [dwfSales].[refPeriod]<='200906'))
    'GROUP BY dwdCustomer.strCustomerNo, dwdCustomer.strCustomerName, dwdCustomer.strRegionDesc2
    'HAVING (((dwdCustomer.strRegionDesc2)="Gauteng"))
    'ORDER BY dwdCustomer.strCustomerNo;


  End Function

  Private Shared Function sqlGetApplicableSalesBySku(ByVal Fiscal As String, ByVal BrandID As Integer) As String
    'note: perContribution needs to be float, Total = 1.0 + ORDER BY percContribution :: Total needs to be the first record
    Return "SELECT LEFT(refProdCode,5) as strProdCode5, Left(refProdCode,5)+'F' AS strProdCodeF, ROUND(Sum(dwfSales.dbl9lTotal),4) AS dbl9lTotal, 0.0 AS percContribution, Max(dwdProduct.intPack) AS intPack, dwdProduct.intSize AS intSize " & _
           "FROM dwdProduct INNER JOIN (dwfSales INNER JOIN dwdCustomer ON dwfSales.refCustomerNo = dwdCustomer.strCustomerNo) ON dwdProduct.strProdCode = dwfSales.refProdCode " & _
           "WHERE dwdProduct.blnQF = 1 AND dwdProduct.refBrandID = " & BrandID & " " & Fiscal & " " & _
           "GROUP BY Left(refProdCode,5), dwdProduct.intSize " & _
           "UNION ALL " & _
           "SELECT 'Total' AS strProdCode5, 'Total' AS strProdCodeF, ROUND(Sum(dwfSales.dbl9lTotal),4) AS dbl9lTotal, 1.0 AS percContribution, AVG(intPack) AS intPack, AVG(intSize) AS intSize " & _
           "FROM dwdProduct INNER JOIN (dwfSales INNER JOIN dwdCustomer ON dwfSales.refCustomerNo = dwdCustomer.strCustomerNo) ON dwdProduct.strProdCode = dwfSales.refProdCode " & _
           "WHERE dwdProduct.blnQF = 1 AND dwdProduct.refBrandID = " & BrandID & " " & Fiscal & " " & _
           "ORDER BY percContribution DESC, strProdCodeF ASC"

    'SELECT Left(refProdCode,5)+'F' AS strProdCodeF, Sum(dwfSales.dbl9lTotal) AS dbl9lTotal, Max(dwdProduct.intPack) AS intPack, dwdProduct.intSize
    'FROM dwfSales INNER JOIN dwdProduct ON dwfSales.refProdCode = dwdProduct.strProdCode
    'WHERE dwdProduct.blnQF = 1 AND dwdProduct.refBrandID = 6 AND dwfSales.refPeriod >= '200807' And dwfSales.refPeriod <= '200906'
    'GROUP BY Left(refProdCode,5), dwdProduct.intSize
    'ORDER BY Left(refProdCode,5)';

  End Function

  Private Shared Function sqlGetQFA(ByVal Fiscal As String, ByVal BrandID As Integer) As String
    'note: By REGION by PERIOD
    Return "SELECT dwdCustomer.strRegionDesc2 as Region, dwfQFAdjustment.* " & _
          "FROM dwdCustomer INNER JOIN dwfQFAdjustment ON dwdCustomer.strCustomerNo = dwfQFAdjustment.strCustomerNo " & _
          "WHERE dwfQFAdjustment.refBrandID = " & BrandID & " " & Fiscal & _
          "ORDER BY dwdCustomer.strRegionOrder, dwfQFAdjustment.strPeriod "

    '"WHERE dwfQFAdjustment.refBrandID = " & BrandID & " " & Fiscal & _
    '"WHERE dwfQFAdjustment.refBrandID = 6 AND dwfQFAdjustment.strPeriod >= '200907' AND dwfQFAdjustment.strPeriod <= '201006' " & _

    'SELECT dwdCustomer.strRegionDesc2, dwfQFAdjustment.*
    'FROM dwdCustomer INNER JOIN dwfQFAdjustment ON dwdCustomer.strCustomerNo = dwfQFAdjustment.strCustomerNo
    'WHERE dwfQFAdjustment.refBrandID = 6 AND dwfQFAdjustment.strPeriod >= '200907' AND dwfQFAdjustment.strPeriod <= '201006'
    'ORDER BY dwfQFAdjustment.strPeriod, dwdCustomer.strRegionOrder

  End Function

End Class