'20100906 - v1.0.0 - pj: Class Branding: Can produce Brand Artifacts
'                      : locking: important code piece: locks previous periods
'20110318 - v1.0.1 - added Region class var/filter - pj
'                  - added get12/6/3 month growth - pj
'20120404 - v1.0.3 - compensating for data bug: making sure all customers have region setup correctly - pj
'20150529 - v1.0.3 - unlocked all QFs

Imports System.Data

Imports RGBC_Forecast.Duodecimal
Imports RGBC_Forecast.SystemFunctions
Imports RGBC_Forecast.ProjectFunctions
Imports System.Globalization

Public Class Branding

   Public FinYear As Integer
   Public BrandID As Integer
   Public BrandName As String
   Public BrandLabel As String
   Public sqlBrandFilter As String
   Public Region As String

   Public DateFinYearStart As Date
   Public DateFinYearEnd As Date
   Public FinYearLabel As String

   Public DateFinYearStartMin1 As Date
   Public DateFinYearEndMin1 As Date
   Public FinYearLabelMin1 As String

   Public DateFinYearStartMin2 As Date
   Public DateFinYearEndMin2 As Date
   Public FinYearLabelMin2 As String

   Public DateFinYearStartMin3 As Date
   Public DateFinYearEndMin3 As Date
   Public FinYearLabelMin3 As String

   Public strProdCodesF As String
   Private intFullYearPeriods As Integer = 12

   Public Shared arrCustomers As Collection
   Public arrPeriods, arrProducts As Collection

   Private dtLog, dtAdjusted, dtPlanned, dtActual, dtApplied, dtPlanningStats, dtSalesRhythm As DataTable

   Public Sub New(ByVal intFinYear As Integer, Optional ByVal intBrandID As Integer = 0, Optional ByVal frRegion As String = "- All -")
      'set brand details, gets fin years
      'Dim ds As System.Data.Dataset
      Dim dr As DataRow

      Me.FinYear = intFinYear
      Me.BrandID = intBrandID
      Me.Region = frRegion

      'Me.FinYear = 2010
      'Me.BrandID = 6

      '20120404 - v1.0.3 - compensating for data bug: making sure all customers have region setup correctly - pj

      db.doQuery("UPDATE dwdCustomer SET strRegionDesc2 = mstBranch.strRegionDesc2, strRegionOrder = mstBranch.strRegionOrder " & _
                 "FROM dwdCustomer INNER JOIN mstCustomer ON dwdCustomer.strCustomerNo = mstCustomer.strCustomerNo INNER JOIN mstBranch ON mstCustomer.refBranchID = mstBranch.BranchID " & _
                 "WHERE (dwdCustomer.strRegionDesc2 IS NULL) OR (dwdCustomer.strRegionOrder IS NULL) OR (dwdCustomer.strRegionDesc2 <> mstBranch.strRegionDesc2) OR (dwdCustomer.strRegionOrder <> mstBranch.strRegionOrder)")

      '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)
         Me.FinYearLabel = Me.DateFinYearStart.ToString("MMM") & " - " & Me.DateFinYearEnd.ToString("MMM yyyy")

         Me.DateFinYearStartMin1 = New Date(Me.FinYear - 1, CInt(dr!strFYStart), 1)
         Me.DateFinYearEndMin1 = New Date(Me.FinYear - 1, CInt(dr!strFYEnd), 1)
         Me.FinYearLabelMin1 = Me.DateFinYearStartMin1.ToString("MMM") & " - " & Me.DateFinYearEndMin1.ToString("MMM yyyy")

         Me.DateFinYearStartMin2 = New Date(Me.FinYear - 2, CInt(dr!strFYStart), 1)
         Me.DateFinYearEndMin2 = New Date(Me.FinYear - 2, CInt(dr!strFYEnd), 1)
         Me.FinYearLabelMin2 = Me.DateFinYearStartMin2.ToString("MMM") & " - " & Me.DateFinYearEndMin2.ToString("MMM yyyy")

         Me.DateFinYearStartMin3 = New Date(Me.FinYear - 3, CInt(dr!strFYStart), 1)
         Me.DateFinYearEndMin3 = New Date(Me.FinYear - 3, CInt(dr!strFYEnd), 1)
         Me.FinYearLabelMin3 = Me.DateFinYearStartMin3.ToString("MMM") & " - " & Me.DateFinYearEndMin3.ToString("MMM yyyy")

      Else

         Me.DateFinYearStart = New Date(Me.FinYear - 1, CInt(dr!strFYStart), 1)
         Me.DateFinYearEnd = New Date(Me.FinYear, CInt(dr!strFYEnd), 1)
         Me.FinYearLabel = Me.DateFinYearStart.ToString("MMM yyyy") & " - " & Me.DateFinYearEnd.ToString("MMM yyyy")

         Me.DateFinYearStartMin1 = New Date(Me.FinYear - 2, CInt(dr!strFYStart), 1)
         Me.DateFinYearEndMin1 = New Date(Me.FinYear - 1, CInt(dr!strFYEnd), 1)
         Me.FinYearLabelMin1 = Me.DateFinYearStartMin1.ToString("MMM yyyy") & " - " & Me.DateFinYearEndMin1.ToString("MMM yyyy")

         Me.DateFinYearStartMin2 = New Date(Me.FinYear - 3, CInt(dr!strFYStart), 1)
         Me.DateFinYearEndMin2 = New Date(Me.FinYear - 2, CInt(dr!strFYEnd), 1)
         Me.FinYearLabelMin2 = Me.DateFinYearStartMin2.ToString("MMM yyyy") & " - " & Me.DateFinYearEndMin2.ToString("MMM yyyy")

         Me.DateFinYearStartMin3 = New Date(Me.FinYear - 4, CInt(dr!strFYStart), 1)
         Me.DateFinYearEndMin3 = New Date(Me.FinYear - 3, CInt(dr!strFYEnd), 1)
         Me.FinYearLabelMin3 = Me.DateFinYearStartMin3.ToString("MMM yyyy") & " - " & Me.DateFinYearEndMin3.ToString("MMM yyyy")
      End If

      'get Brand & Principal info
      If Me.BrandID > 0 Then
         dr = db.getRow("SELECT * FROM mstBrand LEFT JOIN mstPrincipal ON refPrincipalID = PrincipalID WHERE BrandID = " & Me.BrandID)
         Me.BrandName = dr!strBrand

         Me.BrandLabel = "<B class='textBlack'>" & dr!strBrand & "</b> [" & dr!strPrincipal & " " & dr!strFYStart & " - " & dr!strFYEnd & "]"
         Me.sqlBrandFilter = "BrandID = " & Me.BrandID
         'more sir, more?

         Dim ds As DataSet = db.doQuery("SELECT * FROM dwdProduct WHERE refBrandID = " & Me.BrandID & " AND blnQF = 1 AND strProdCode LIKE ('%F%')")
         arrProducts = New Collection
         strProdCodesF = ""
         Dim comma As String = ""
         For Each dr In ds.Tables(0).Rows
            arrProducts.Add(dr, dr!strProdCode)
            strProdCodesF &= comma & "'" & dr!strProdCode & "'"
            comma = ","
         Next
      End If

      Branding.LoadCustomers()
      Me.LoadPeriods()

   End Sub

   '***********************************
   '***PROPERTIES**********************
   '***********************************

   Public Property SalesRhythmDT() As DataTable
      Get
         Return Me.dtSalesRhythm
      End Get
      Set(ByVal value As DataTable)
         Try
            Me.dtSalesRhythm = value
            Dim pk(0) As System.Data.DataColumn
            pk(0) = Me.dtSalesRhythm.Columns(Me.dtSalesRhythm.Columns.IndexOf("Region"))
            Me.dtSalesRhythm.PrimaryKey = pk
         Catch ex As Exception

         End Try

      End Set
   End Property

   Public Property PlanningStatsDT() As DataTable
      Get
         Return Me.dtPlanningStats
      End Get
      Set(ByVal value As DataTable)
         Me.dtPlanningStats = value
         Dim pk(0) As System.Data.DataColumn
         pk(0) = Me.dtPlanningStats.Columns(Me.dtPlanningStats.Columns.IndexOf("Region"))
         Me.dtPlanningStats.PrimaryKey = pk

      End Set
   End Property

   Public Property LogDT() As DataTable
      Get
         Return Me.dtLog
      End Get
      Set(ByVal value As DataTable)
         Me.dtLog = value
         Dim pk(0) As System.Data.DataColumn 'lame
         pk(0) = Me.dtLog.Columns(Me.dtLog.Columns.IndexOf("Region")) 'lame
         Me.dtLog.PrimaryKey = pk 'lame

      End Set
   End Property

   Public Property PlannedDT() As DataTable
      Get
         Return Me.dtPlanned
      End Get
      Set(ByVal value As DataTable)
         Me.dtPlanned = value
         Dim pk(0) As System.Data.DataColumn
         pk(0) = Me.dtPlanned.Columns(Me.dtPlanned.Columns.IndexOf("Region"))
         Me.dtPlanned.PrimaryKey = pk

      End Set
   End Property

   Public Property AdjustedDT() As DataTable
      Get
         Return Me.dtAdjusted
      End Get
      Set(ByVal value As DataTable)
         Me.dtAdjusted = value
         Dim pk(0) As System.Data.DataColumn
         pk(0) = Me.dtAdjusted.Columns(Me.dtAdjusted.Columns.IndexOf("Region"))
         Me.dtAdjusted.PrimaryKey = pk

      End Set
   End Property

   Public Property ActualDT() As DataTable
      Get
         Return Me.dtActual
      End Get
      Set(ByVal value As DataTable)
         Me.dtActual = value
         Dim pk(0) As System.Data.DataColumn
         pk(0) = Me.dtActual.Columns(Me.dtActual.Columns.IndexOf("Region"))
         Me.dtActual.PrimaryKey = pk
      End Set

   End Property

   Public Property AppliedDT() As DataTable
      Get
         Return Me.dtApplied
      End Get
      Set(ByVal value As DataTable)
         Me.dtApplied = value
         Dim pk(0) As System.Data.DataColumn
         pk(0) = Me.dtApplied.Columns(Me.dtApplied.Columns.IndexOf("Region"))
         Me.dtApplied.PrimaryKey = pk
      End Set

   End Property

   '***********************************
   '***PROPERTIES FUNCTIONS************
   '***********************************

   Public Function getDtCellSalesRhythm(ByVal Fiscal As String, ByVal Period As String) As String
      Try
         Return Me.dtSalesRhythm.Rows.Find(Fiscal).Item(Me.dtSalesRhythm.Columns.IndexOf(Period)).ToString
      Catch ex As Exception
         Return ""
      End Try
   End Function

   Public Function getDtCellSalesRhythm(ByVal Fiscal As String, ByVal idxColumn As Integer) As String
      Try
         Return Me.dtSalesRhythm.Rows.Find(Fiscal).Item(idxColumn).ToString
      Catch ex As Exception
         Return ""
      End Try
   End Function

   Public Sub setDtCellSalesRhythm(ByVal Fiscal As String, ByVal Period As String, ByVal value As String)
      Try
         Me.dtSalesRhythm.Rows.Find(Fiscal).Item(Me.dtSalesRhythm.Columns.IndexOf(Period)) = value
      Catch ex As Exception
      End Try
   End Sub
   Public Function getDtCellPlanningStats(ByVal Region As String, ByVal ColumnName As String) As String
      Try
         Return Me.dtPlanningStats.Rows.Find(Region).Item(Me.dtPlanningStats.Columns.IndexOf(ColumnName)).ToString
      Catch ex As Exception
         Return ""
      End Try
   End Function

   Public Sub setDtCellPlanningStats(ByVal Region As String, ByVal ColumnName As String, ByVal value As String)
      Try
         Me.dtPlanningStats.Rows.Find(Region).Item(Me.dtPlanningStats.Columns.IndexOf(ColumnName)) = value
      Catch ex As Exception
      End Try
   End Sub

   Public Function getDtCellLog(ByVal Region As String, ByVal Period As String) As String
      Try
         Return Me.dtLog.Rows.Find(Region).Item(Me.dtLog.Columns.IndexOf(Period)).ToString
      Catch ex As Exception
         Return ""
      End Try
   End Function

   Public Sub setDtCellLog(ByVal Region As String, ByVal Period As String, ByVal value As String)
      Try
         Me.dtLog.Rows.Find(Region).Item(Me.dtLog.Columns.IndexOf(Period)) = value
      Catch ex As Exception
      End Try
   End Sub

   Public Function getDtCellAdjusted(ByVal Region As String, ByVal Period As String) As Double
      Try
         Return Me.dtAdjusted.Rows.Find(Region).Item(Me.dtAdjusted.Columns.IndexOf(Period))
      Catch ex As Exception
         Return 0.0
      End Try
   End Function

   Public Sub setDtCellAdjusted(ByVal Region As String, ByVal Period As String, ByVal value As Double)
      Try
         Me.dtAdjusted.Rows.Find(Region).Item(Me.dtAdjusted.Columns.IndexOf(Period)) = value
      Catch ex As Exception
      End Try
   End Sub

   Public Function getDtCellActual(ByVal Region As String, ByVal Period As String) As Double
      Try
         Return Me.dtActual.Rows.Find(Region).Item(Me.dtActual.Columns.IndexOf(Period))
      Catch ex As Exception
         Return 0.0
      End Try
   End Function

   Public Sub setDtCellActual(ByVal Region As String, ByVal Period As String, ByVal value As Double)
      Try
         Me.dtActual.Rows.Find(Region).Item(Me.dtActual.Columns.IndexOf(Period)) = value
      Catch ex As Exception
      End Try
   End Sub

   Public Function getDtCellApplied(ByVal Region As String, ByVal Period As String) As TriState
      Try
         Return Me.dtApplied.Rows.Find(Region).Item(Me.dtApplied.Columns.IndexOf(Period))
      Catch ex As Exception
         Return TriState.UseDefault
      End Try
   End Function

   Public Sub setDtCellApplied(ByVal Region As String, ByVal Period As String, ByVal value As Boolean)
      Try
         Me.dtApplied.Rows.Find(Region).Item(Me.dtApplied.Columns.IndexOf(Period)) = value
      Catch ex As Exception
      End Try
   End Sub

   Public Function getDtCellPlanned(ByVal Region As String, ByVal Period As String) As Double
      Try
         Return Me.dtPlanned.Rows.Find(Region).Item(Me.dtPlanned.Columns.IndexOf(Period))
      Catch ex As Exception
         Return 0.0
      End Try
   End Function

   Public Sub setDtCellPlanned(ByVal Region As String, ByVal Period As String, ByVal value As Double)
      Try
         Me.dtPlanned.Rows.Find(Region).Item(Me.dtPlanned.Columns.IndexOf(Period)) = value
      Catch ex As Exception
      End Try
   End Sub

   '***********************************
   '***BRANDING FUNCTIONS**************
   '***********************************

   Public Function GetPlannedFullYear() As System.Data.DataTable
      'builds/XTabs the full year for fact = Planned QF
      Dim dt As System.Data.DataTable
      Dim ds As New System.Data.DataSet

      'merge test
      'ds.Tables(0).Merge(db.doQuery("SELECT CompanyID, strCompany FROM mstCompany ").Tables(0).Copy())
      'ds.Tables(0).Merge(db.doQuery("SELECT CompanyID, strFYStart, strFYEnd  FROM mstCompany ").Tables(0).Copy())
      'dt = db.MergeDataSets(ds, "CompanyID")
      'dt = ds.Tables(0).Copy()

      ds.Tables.Add()
      ds = Me.getFullYearFact("QF PLANNED")
      dt = db.MergeDataSets(ds, "Region")

      Return dt
   End Function

   '20110318 - v1.0.1 - new function: gives mixed Planed [Global & Region] + Actual -1 [G & R] + Actual -2 [G&R]
   Public Function GetPlannedAndHistoricFullYear() As System.Data.DataTable
      'builds/XTabs the full year for fact = Planned QF
      Dim dt As System.Data.DataTable
      Dim ds As New System.Data.DataSet
      Dim bActual1, bActual2 As Branding

      'get Planning
      ds.Tables.Add()
      ds = Me.getFullYearFact("QF PLANNED", regionHeader:="Planned: ")

      'get Actual-1
      bActual1 = New Branding(Me.FinYear - 1, Me.BrandID, Me.Region)
      ds.Tables(0).Merge(bActual1.getFullYearFact("ACTUAL", regionHeader:="FY " & bActual1.FinYear & ": ").Tables(0).Copy)

      'getActual-2
      bActual2 = New Branding(Me.FinYear - 2, Me.BrandID, Me.Region)
      ds.Tables(0).Merge(bActual2.getFullYearFact("ACTUAL", regionHeader:="FY " & bActual2.FinYear & ": ").Tables(0).Copy)

      dt = db.MergeDataSets(ds, "Region")

      Return dt
   End Function

   Public Function GetLogFullYear() As System.Data.DataTable
      Dim dt As System.Data.DataTable
      Dim ds As New System.Data.DataSet

      ds.Tables.Add()
      ds = Me.getFullYearFactNoAgrigate("LOG")
      dt = db.MergeDataSets(ds, "Region")

      Return dt.Copy()

   End Function

   Public Function GetAppliedFullYear() As System.Data.DataTable
      Dim dt As System.Data.DataTable
      Dim ds As New System.Data.DataSet

      ds.Tables.Add()
      ds = Me.getFullYearFactNoAgrigate("APPLIED")
      dt = db.MergeDataSets(ds, "Region")

      Return dt.Copy()

   End Function


   Public Function GetAdjustedFullYear() As System.Data.DataTable
      'builds/XTabs the full year for fact = Actuals
      Dim dt As System.Data.DataTable
      Dim ds As New System.Data.DataSet

      ds.Tables.Add()
      ds = Me.getFullYearFact("QF ADJUSTED")
      dt = db.MergeDataSets(ds, "Region")

      Return dt.Copy()
   End Function

   Public Function GetActualFullYear() As System.Data.DataTable
      'builds/XTabs the full year for fact = Actuals
      Dim dt As System.Data.DataTable
      Dim ds As New System.Data.DataSet

      ds.Tables.Add()
      ds = Me.getFullYearFact("ACTUAL")

      '20110318 - v1.0.1 - added Actual vs QF Column
      ds.Tables(0).Merge(Me.getYtdActualVsQF().Tables(0).Copy())

      dt = db.MergeDataSets(ds, "Region")

      Return dt.Copy()
   End Function

   Public Function GetVarianceFullYear1() As System.Data.DataTable
      'builds/XTabs the full year for fact = VARIANCE 1: Planned / Actual
      Dim dt As System.Data.DataTable
      Dim ds As New System.Data.DataSet

      ds.Tables.Add()
      ds = Me.getFullYearVariance("VARIANCE PLANNED")
      dt = db.MergeDataSets(ds, "Region")

      Return dt.Copy()
   End Function

   Public Function GetVarianceFullYear2() As System.Data.DataTable
      'builds/XTabs the full year for fact = VARIANCE 1: Planned / Actual
      Dim dt As System.Data.DataTable
      Dim ds As New System.Data.DataSet

      ds.Tables.Add()
      ds = Me.getFullYearVariance("VARIANCE ADJUSTED")
      dt = db.MergeDataSets(ds, "Region")

      Return dt.Copy()
   End Function

   Public Function getStatsAndProjections(ByVal tempBrand As Branding, ByVal Brand As Branding, ByVal percCompleted As Double) As DataTable
      Dim ds As New System.Data.DataSet

      ds = Me.getPlanningStats(tempBrand, Brand, percCompleted)

      Return ds.Tables(0).Copy()
   End Function

   Public Function getStatsAndPerformance() As System.Data.DataTable
      Dim ds As New System.Data.DataSet
      Dim dt As System.Data.DataTable

      ds = Me.getStats()
      'ds = Me.getPerformanceYTD()

      'ds.Tables(0).Merge(Me.getPerformanceYTD().Tables(0).Copy())

      dt = db.MergeDataSets(ds, "Region")
      Return dt.Copy()
   End Function

   Public Function getStatsAndGrowth() As System.Data.DataTable
      Dim ds As New System.Data.DataSet
      Dim dt As System.Data.DataTable

      '20110318 - v1.0.1 - added get12/6/3 month growth - pj
      ds = Me.getGrowth()
      'ds = Me.getPerformanceYTD()

      'ds.Tables(0).Merge(Me.getPerformanceYTD().Tables(0).Copy())

      dt = db.MergeDataSets(ds, "Region")
      Return dt.Copy()
   End Function

   Public Function getSalesRhythm1(ByVal BrandID As Integer, ByVal StartPeriod As String) As DataTable
      Dim ds, dsMerge As New System.Data.DataSet
      Dim dt As DataTable
      Dim s, a, v As New Collection
      Dim sql As String
      Dim pk(0) As System.Data.DataColumn


      'default layout i want
      sql = "SELECT '1' AS '#', 'FY ' + CAST(intFiscalYear as varchar(20)) AS 'Region', 99999999.9999 AS 'Jul', 99999999.9999 AS 'Aug', 99999999.9999 AS 'Sep', 99999999.9999 AS 'Q1', 99999999.9999 AS 'Oct', 99999999.9999 AS 'Nov', 99999999.9999 AS 'Dec', 99999999.9999 AS 'Q2', 99999999.9999 AS 'Jan', 99999999.9999 AS 'Feb', 99999999.9999 AS 'Mar', 99999999.9999 AS 'Q3', 99999999.9999 AS 'Apr', 99999999.9999 AS 'May', 99999999.9999 AS 'Jun', 99999999.9999 AS 'Q4', 9999999999.9999 AS 'Total', 9999999999.9999 AS 'Average' FROM mstPeriod WHERE 1=0 "
      dsMerge = db.doQuery(sql)


      'xtab the sales group by period
      s.Add("#", "#")
      a.Add("'1'", "#")
      s.Add("Region", "Region")
      a.Add("'FY ' + CAST(intFiscalYear as varchar(20))", "Region")
      's.Add("Total", "Total")
      'a.Add("0.0", "Total")
      v.Add("Sum(dwfSales.dbl9lTotal)")
      v.Add("dbl9lTotal")
      sql = "SELECT '1' as '#', 'FY ' + CAST(intFiscalYear as varchar(20)) AS 'Region', left(mstPeriod.strMonth,3) AS Period, intFiscalMonth, ROUND(Sum(dwfSales.dbl9lTotal),4) as dbl9lTotal " & _
      "FROM mstPeriod INNER JOIN (dwdCustomer INNER JOIN (dwfSales INNER JOIN dwdProduct ON dwfSales.refProdCode = dwdProduct.strProdCode) ON dwdCustomer.strCustomerNo = dwfSales.refCustomerNo) ON mstPeriod.strPeriod = dwfSales.refPeriod  " & _
      "WHERE (((dwdProduct.refBrandID)=" & BrandID & ") AND ((dwfSales.refPeriod)>='" & StartPeriod & "')) " & _
      "GROUP BY mstPeriod.intFiscalYear, mstPeriod.strMonth, intFiscalMonth " & _
      "ORDER BY Region DESC, intFiscalMonth"
      ds = db.doQueryXTab(sql, s, "Period", v, a)
      dsMerge.Tables(0).Merge(ds.Tables(0), False, MissingSchemaAction.Ignore)

      'quarters
      sql = "SELECT '1' as '#', 'FY ' + CAST(intFiscalYear as varchar(20)) AS 'Region', 'Q' + CAST(intFiscalQuarter as varchar(4)) AS Quarter, ROUND(Sum(dwfSales.dbl9lTotal),4) as dbl9lTotal " & _
      "FROM mstPeriod INNER JOIN (dwdCustomer INNER JOIN (dwfSales INNER JOIN dwdProduct ON dwfSales.refProdCode = dwdProduct.strProdCode) ON dwdCustomer.strCustomerNo = dwfSales.refCustomerNo) ON mstPeriod.strPeriod = dwfSales.refPeriod  " & _
      "WHERE (((dwdProduct.refBrandID)=" & BrandID & ") AND ((dwfSales.refPeriod)>='" & StartPeriod & "')) " & _
      "GROUP BY mstPeriod.intFiscalYear, intFiscalQuarter " & _
      "ORDER BY Region DESC, intFiscalQuarter"
      ds = db.doQueryXTab(sql, s, "Quarter", v, a)
      dsMerge.Tables(0).Merge(ds.Tables(0), False, MissingSchemaAction.Ignore)

      'totals & avg
      sql = "SELECT '1' as '#', 'FY ' + CAST(intFiscalYear as varchar(20)) AS 'Region', ROUND(Sum(dwfSales.dbl9lTotal),4) as 'Total', ROUND(Sum(dwfSales.dbl9lTotal/12),4) AS 'Average' " & _
      "FROM mstPeriod INNER JOIN (dwdCustomer INNER JOIN (dwfSales INNER JOIN dwdProduct ON dwfSales.refProdCode = dwdProduct.strProdCode) ON dwdCustomer.strCustomerNo = dwfSales.refCustomerNo) ON mstPeriod.strPeriod = dwfSales.refPeriod  " & _
      "WHERE (((dwdProduct.refBrandID)=" & BrandID & ") AND ((dwfSales.refPeriod)>='" & StartPeriod & "')) " & _
      "GROUP BY mstPeriod.intFiscalYear " & _
      "ORDER BY Region DESC"
      ds = db.doQuery(sql)
      dsMerge.Tables(0).Merge(ds.Tables(0), False, MissingSchemaAction.Ignore)


      'dt = dsMerge.Tables(0)
      dt = db.MergeDataSets(dsMerge, "Region")

      Return dt.Copy()
   End Function

   Public Function getSalesRhythm2(ByVal dtRhythm As DataTable) As DataTable
      Dim ds As New System.Data.DataSet
      Dim dblTotal As Double
      Dim idxTotal, idxRegion, idxCol As Integer
      'notes: important: remove row 0 = YTD Sales
      idxTotal = dtRhythm.Columns.IndexOf("Total")
      idxRegion = dtRhythm.Columns.IndexOf("Region")

      dtRhythm.Columns.Remove("Average")
      Try
         If dtRhythm.Rows(0).Item(idxRegion).ToString.Contains(Me.FinYear.ToString) Then dtRhythm.Rows.RemoveAt(0)
      Catch ex As Exception
         'no sales
         Return Nothing
      End Try

      Dim drNew As DataRow
      drNew = dtRhythm.NewRow
      drNew = db.CleanData(drNew)
      drNew(0) = ""
      drNew(idxRegion) = "Average %"

      For idxCol = idxRegion + 1 To idxTotal - 1
         For Each row As DataRow In dtRhythm.Rows
            dblTotal = db.nz(row(idxTotal), 0.0)
            'run once
            If Not row(idxRegion).ToString.Contains("%") Then row(idxRegion) = row(idxRegion) & " %"
            Try
               drNew(idxCol) += row(idxCol) / dblTotal
               row(idxCol) = Math.Round(row(idxCol) / dblTotal, 8)
               'row(idxCol) = IIf(row(idxCol).Contains(","), row(idxCol).Replace(",", "."), row(idxCol))
            Catch ex As Exception
            End Try

         Next
         If (dtRhythm.Rows.Count <> 0) Then
            drNew(idxCol) = Math.Round(drNew(idxCol) / dtRhythm.Rows.Count, 8)
         Else
            drNew(idxCol) = 0
         End If
      Next

      dtRhythm.Rows.Add(drNew)
      dtRhythm.Columns.Remove("Total")

      Return dtRhythm.Copy()

   End Function

   'REPORT FUNCTIONS
   Public Function getReportForcast() As System.Data.DataTable
      Dim ds As New System.Data.DataSet
      Dim dt As System.Data.DataTable


      ds = Me.getStats()
      'ds = Me.getPerformanceYTD()

      ds.Tables(0).Merge(Me.getFullYearFact("QF ADJUSTED").Tables(0).Copy())

      dt = db.MergeDataSets(ds, "Region")
      Return dt.Copy()
   End Function

   '20110328 - v1.0.1 - New RSM Report: Shows Actuals 9L for past and current months AND QF Adjusted for future months
   Public Function getReportRSM() As System.Data.DataTable
      Dim ds As New System.Data.DataSet
      Dim dt As System.Data.DataTable
      Dim currentQ, idxRegion, idxTotal, i As Integer
      Dim dblTotal, dblQ As Double

      ds = Me.getFullYearMixedFact()

      dt = db.MergeDataSets(ds, "Region")
      'post sql process: need to recalc the totals, avg and quarters

      idxRegion = dt.Columns.IndexOf("Region")
      idxTotal = dt.Columns.IndexOf("Total")

      Dim quickDate As New Duodecimal(Me.DateFinYearStart)

      For Each row In dt.Rows
         For idxCol = idxRegion + 1 To idxTotal - 1
            i += 1
            If currentQ <> Math.Ceiling(i / 4) Then
               If currentQ > 0 Then
                  'write to ds'update the previous idx
                  row(idxCol - 1) = dblQ
               End If
               dblQ = 0
            End If

            If i Mod 4 > 0 Then

               dblQ += row(idxCol)
               dblTotal += row(idxCol)
            End If


            currentQ = Math.Ceiling(i / 4)
            quickDate.Add(1)
         Next 'L2

         row(idxTotal) = dblTotal
         row(idxTotal + 1) = Math.Round(dblTotal / 12, 4, MidpointRounding.AwayFromZero)

         dblTotal = 0
         dblQ = 0
         currentQ = 0
         i = 0
      Next 'L1



      Return dt.Copy()

   End Function


   'ARTEFACTS

   Private Function getPlanningStats(ByVal tempBrand As Branding, ByVal Brand As Branding, ByVal percCompleted As Double) As DataSet
      Dim ds As DataSet
      Dim sql, sqlLY, sqlLYQF, sqlYTD, sqlYTDQF As String
      Dim dblLYSales, dblLYSalesQF, dblYTDSales, dblYTDSalesQF, dblProjected As Double

      'get LY sales, QF and not QF
      ds = db.doQuery("Select blnQF, ROUND(SUM(dbl9lTotal), 4) As dbl9lTotal " & _
                     "FROM dwdCustomer INNER JOIN (dwfSales INNER JOIN dwdProduct On dwfSales.refProdCode = dwdProduct.strProdCode) ON dwdCustomer.strCustomerNo = dwfSales.refCustomerNo " & _
                     "WHERE refBrandID = " & tempBrand.BrandID & " " & _
                        "AND refPeriod >= '" & tempBrand.DateFinYearStartMin1.ToString("yyyyMM") & "' " & _
                        "AND refPeriod <= '" & tempBrand.DateFinYearEndMin1.ToString("yyyyMM") & "' " & _
                     "GROUP BY blnQF " & _
                     "ORDER BY blnQF ")
      For Each dr In ds.Tables(0).Rows
         If dr!blnQF = True Then dblLYSalesQF = dr!dbl9lTotal
         dblLYSales += dr!dbl9lTotal
      Next
      If dblLYSales = 0.0 Then dblLYSales = 1
      If dblLYSalesQF = 0.0 Then dblLYSalesQF = 1

      'get YTD sales, QF and not QF USING tempBrands.F
      ds = db.doQuery("SELECT blnQF, ROUND(SUM(dbl9lTotal),4) as dbl9lTotal " & _
                     "FROM dwdCustomer INNER JOIN (dwfSales INNER JOIN dwdProduct ON dwfSales.refProdCode = dwdProduct.strProdCode) ON dwdCustomer.strCustomerNo = dwfSales.refCustomerNo " & _
                     "WHERE refBrandID = " & Brand.BrandID & " " & _
                        "AND refPeriod >= '" & tempBrand.DateFinYearStart.ToString("yyyyMM") & "' " & _
                        "AND refPeriod <= '" & Duodecimal.Add(Date.Today, -1).ToString("yyyyMM") & "' " & _
                     "GROUP BY blnQF " & _
                     "ORDER BY blnQF ")
      For Each dr In ds.Tables(0).Rows
         If dr!blnQF = True Then dblYTDSalesQF = dr!dbl9lTotal
         dblYTDSales += dr!dbl9lTotal
      Next
      If dblYTDSales = 0.0 Then dblYTDSales = 1
      If dblYTDSalesQF = 0.0 Then dblYTDSalesQF = 1

      If percCompleted = 0.0 Then
         percCompleted = 1
         dblProjected = 0
      Else
         dblProjected = dblYTDSales / percCompleted
      End If

      ds = New DataSet
      ds.Tables.Add("xTab")

      sqlLY = "SELECT strRegionOrder, dwdCustomer.strRegionDesc2, Sum(dwfSales.dbl9lTotal) AS col1, Sum(dbl9lTotal/" & dblLYSales & ") AS col2, 0.0 AS col3, 0.0 AS col4, 0.0 AS col5, 0.0 AS col6, 0.0 AS col7, 0.0 AS col8 " & _
      "FROM dwdCustomer INNER JOIN (dwfSales INNER JOIN dwdProduct ON dwfSales.refProdCode = dwdProduct.strProdCode) ON dwdCustomer.strCustomerNo = dwfSales.refCustomerNo " & _
      "WHERE dwdProduct.refBrandID=" & tempBrand.BrandID & " AND dwfSales.refPeriod>='" & tempBrand.DateFinYearStartMin1.ToString("yyyyMM") & "' And dwfSales.refPeriod<='" & tempBrand.DateFinYearEndMin1.ToString("yyyyMM") & "' " & _
      "GROUP BY dwdCustomer.strRegionOrder, dwdCustomer.strRegionDesc2 "

      sqlLYQF = "SELECT strRegionOrder, dwdCustomer.strRegionDesc2, 0.0 AS col1, 0.0 AS col2, Sum(dwfSales.dbl9lTotal) AS col3, Sum(dbl9lTotal/" & dblLYSalesQF & ") AS col4, 0.0 AS col5, 0.0 AS col6, 0.0 AS col7, 0.0 AS col8 " & _
      "FROM dwdCustomer INNER JOIN (dwfSales INNER JOIN dwdProduct ON dwfSales.refProdCode = dwdProduct.strProdCode) ON dwdCustomer.strCustomerNo = dwfSales.refCustomerNo " & _
      "WHERE blnQF = 1 AND dwdProduct.refBrandID=" & tempBrand.BrandID & " AND dwfSales.refPeriod>='" & tempBrand.DateFinYearStartMin1.ToString("yyyyMM") & "' And dwfSales.refPeriod<='" & tempBrand.DateFinYearEndMin1.ToString("yyyyMM") & "' " & _
      "GROUP BY dwdCustomer.strRegionOrder, dwdCustomer.strRegionDesc2 "


      sqlYTD = "SELECT strRegionOrder, dwdCustomer.strRegionDesc2, 0.0 AS col1, 0.0 AS col2, 0.0 AS col3, 0.0 AS col4, Sum(dwfSales.dbl9lTotal) AS col5, Sum(dbl9lTotal/" & dblYTDSales & ") AS col6, 0.0 AS col7, 0.0 AS col8 " & _
      "FROM dwdCustomer INNER JOIN (dwfSales INNER JOIN dwdProduct ON dwfSales.refProdCode = dwdProduct.strProdCode) ON dwdCustomer.strCustomerNo = dwfSales.refCustomerNo " & _
      "WHERE dwdProduct.refBrandID=" & Brand.BrandID & " AND dwfSales.refPeriod>='" & tempBrand.DateFinYearStart.ToString("yyyyMM") & "' And dwfSales.refPeriod<='" & Duodecimal.Add(Date.Today, -1).ToString("yyyyMM") & "' " & _
      "GROUP BY dwdCustomer.strRegionOrder, dwdCustomer.strRegionDesc2 "

      sqlYTDQF = "SELECT strRegionOrder, dwdCustomer.strRegionDesc2, 0.0 AS col1, 0.0 AS col2, 0.0 AS col3, 0.0 AS col4, 0.0 AS col5, 0.0 AS col6, Sum(dwfSales.dbl9lTotal) AS col7, Sum(dbl9lTotal/" & dblYTDSalesQF & ") AS col8 " & _
      "FROM dwdCustomer INNER JOIN (dwfSales INNER JOIN dwdProduct ON dwfSales.refProdCode = dwdProduct.strProdCode) ON dwdCustomer.strCustomerNo = dwfSales.refCustomerNo " & _
      "WHERE blnQF = 1 AND dwdProduct.refBrandID=" & Brand.BrandID & " AND dwfSales.refPeriod>='" & tempBrand.DateFinYearStart.ToString("yyyyMM") & "' And dwfSales.refPeriod<='" & Duodecimal.Add(Date.Today, -1).ToString("yyyyMM") & "' " & _
      "GROUP BY dwdCustomer.strRegionOrder, dwdCustomer.strRegionDesc2 "

      '0.0 AS col1, 0.0 AS col2, 0.0 AS col3, 0.0 AS col4, 0.0 AS col5, 0.0 AS col6, 0.0 AS col7, 0.0 AS col8

      sql = "SELECT strRegionOrder AS '#', strRegionDesc2 AS Region" & _
      ", ROUND(Sum(col1),4) AS 'Sales LY'" & _
      ", ROUND(SUM(col2),6) AS '%C LY'" & _
      ", ROUND(SUM(col3),4) AS 'QF Sales LY'" & _
      ", ROUND(SUM(col4),6) AS '%C QF LY'" & _
      ", ROUND(Sum(col5),4) AS 'Sales YTD'" & _
      ", ROUND(SUM(col6),6) AS '%C YTD'" & _
      ", ROUND(SUM(col7),4) AS 'QF Sales YTD'" & _
      ", ROUND(SUM(col8),6) AS '%C QF YTD'" & _
      ", ROUND((SUM(col2) + SUM(col4) + SUM(col6) + SUM(col8)) / 4, 6) AS 'AVG %Contribution' " & _
      ", ROUND(SUM(col5)/" & percCompleted & ",4) as 'Projection 1'" & _
      ", ROUND((SUM(col2) + SUM(col4) + SUM(col6) + SUM(col8)) / 4 * " & dblProjected & ",4) as 'Projection 2'" & _
      "FROM (" & _
      sqlLY & " UNION ALL " & sqlLYQF & " UNION ALL " & sqlYTD & " UNION ALL " & sqlYTDQF & " " & _
      ") as tblDir " & _
      "GROUP BY strRegionOrder, strRegionDesc2 " & _
      "UNION ALL " & _
      "SELECT MAX(strRegionOrder)+1 AS '#', 'Total' AS Region" & _
      ", ROUND(Sum(col1),4) AS 'Sales LY'" & _
      ", ROUND(SUM(col2),6) AS '% LY'" & _
      ", ROUND(SUM(col3),4) AS 'QF Sales LY'" & _
      ", ROUND(SUM(col4),6) AS '% QF LY'" & _
      ", ROUND(Sum(col5),4) AS 'Sales YTD'" & _
      ", ROUND(SUM(col6),6) AS '% YTD'" & _
      ", ROUND(SUM(col7),4) AS 'QF Sales YTD'" & _
      ", ROUND(SUM(col8),6) AS '% QF YTD'" & _
      ", ROUND((SUM(col2) + SUM(col4) + SUM(col6) + SUM(col8)) / 4, 8) AS '% Contribution' " & _
      ", ROUND(SUM(col5)/" & percCompleted & ",4) as 'Projection 1'" & _
      ", ROUND((SUM(col2) + SUM(col4) + SUM(col6) + SUM(col8)) / 4 * " & dblProjected & ",4) as 'Projection 2'" & _
      "FROM (" & _
      sqlLY & " UNION ALL " & sqlLYQF & " UNION ALL " & sqlYTD & " UNION ALL " & sqlYTDQF & " " & _
      ") as tblDir2 " & _
      "ORDER BY strRegionOrder, strRegionDesc2"

      ds.Tables(0).Merge(db.doQuery(sql).Tables(0).Copy())

      Return ds

   End Function

   Private Function getPerformanceYTD() As System.Data.DataSet
      Dim ds, dsMerged As New System.Data.DataSet
      Dim strPeriodStart As String = ""
      Dim strPeriodEnd As String = ""
      Dim sql, sqlLY, sqlTY, sqlQF As String

      'notes: Adding to this is a pain! problem is its semi impossible getting the calculation shit right without doing it in code. this way its locked-in in sql. quick but complicated

      'ini

      ds.Tables.Add()

      'start

      'YTD This Year: col2
      Dim duo As New Duodecimal(Date.Today)
      sqlTY = "SELECT strRegionOrder, strRegionDesc2, 0 as col1, Round(SUM(dbl9lTotal),4) AS col2, 0 as col3 " & _
      "FROM (dwdProduct INNER JOIN dwfSales ON dwdProduct.strProdCode = dwfSales.refProdCode) INNER JOIN dwdCustomer ON dwfSales.refCustomerNo = dwdCustomer.strCustomerNo " & _
      "WHERE refBrandID = " & Me.BrandID & " AND refPeriod >= '" & Me.DateFinYearStart.ToString("yyyyMM") & "' AND refPeriod <= '" & duo.getPeriod() & "' " & _
      "GROUP BY dwdCustomer.strRegionOrder, dwdCustomer.strRegionDesc2 " & _
      "UNION ALL " & _
      "SELECT '0' AS strRegionOrder, 'Global' AS strRegionDesc2, 0 as col1, Round(SUM(dbl9lTotal),4) AS col2, 0 as col3 " & _
      "FROM (dwdProduct INNER JOIN dwfSales ON dwdProduct.strProdCode = dwfSales.refProdCode) INNER JOIN dwdCustomer ON dwfSales.refCustomerNo = dwdCustomer.strCustomerNo " & _
      "WHERE refBrandID = " & Me.BrandID & " AND refPeriod >= '" & Me.DateFinYearStart.ToString("yyyyMM") & "' AND refPeriod <= '" & duo.getPeriod() & "' "

      'YTD QF (ADJUSTED): col3
      sqlQF = "SELECT strRegionOrder, strRegionDesc2, 0 as col1, 0 as col2, Round(SUM(dblQF),4) as col3 " & _
      "FROM dwdCustomer INNER JOIN dwfQFAdjustment ON dwdCustomer.strCustomerNo = dwfQFAdjustment.strCustomerNo " & _
      "WHERE refBrandID = " & Me.BrandID & " AND strPeriod >= '" & Me.DateFinYearStart.ToString("yyyyMM") & "' AND strPeriod <= '" & duo.getPeriod() & "' " & _
      "GROUP BY dwdCustomer.strRegionOrder, dwdCustomer.strRegionDesc2 " & _
      "UNION ALL " & _
      "SELECT '0' AS strRegionOrder, 'Global' AS strRegionDesc2, 0 as col1, 0 as col2, Round(SUM(dblQF),4) as col3 " & _
      "FROM dwdCustomer INNER JOIN dwfQFAdjustment ON dwdCustomer.strCustomerNo = dwfQFAdjustment.strCustomerNo " & _
      "WHERE refBrandID = " & Me.BrandID & " AND strPeriod >= '" & Me.DateFinYearStart.ToString("yyyyMM") & "' AND strPeriod <= '" & duo.getPeriod() & "' "

      'YTD Last Year: col1
      duo.Add(-12)
      sqlLY = "SELECT strRegionOrder, strRegionDesc2, Round(SUM(dbl9lTotal),4) AS col1, 0 as col2, 0 as col3 " & _
      "FROM (dwdProduct INNER JOIN dwfSales ON dwdProduct.strProdCode = dwfSales.refProdCode) INNER JOIN dwdCustomer ON dwfSales.refCustomerNo = dwdCustomer.strCustomerNo " & _
      "WHERE refBrandID = " & Me.BrandID & " AND refPeriod >= '" & Me.DateFinYearStartMin1.ToString("yyyyMM") & "' AND refPeriod <= '" & duo.getPeriod() & "' " & _
      "GROUP BY dwdCustomer.strRegionOrder, dwdCustomer.strRegionDesc2 " & _
      "UNION ALL " & _
      "SELECT '0' AS strRegionOrder, 'Global' AS strRegionDesc2, Round(SUM(dbl9lTotal),4) AS col1, 0 as col2, 0 as col3 " & _
      "FROM (dwdProduct INNER JOIN dwfSales ON dwdProduct.strProdCode = dwfSales.refProdCode) INNER JOIN dwdCustomer ON dwfSales.refCustomerNo = dwdCustomer.strCustomerNo " & _
      "WHERE refBrandID = " & Me.BrandID & " AND refPeriod >= '" & Me.DateFinYearStartMin1.ToString("yyyyMM") & "' AND refPeriod <= '" & duo.getPeriod() & "' "


      'UNION DIR
      sql = "SELECT strRegionOrder AS '#', strRegionDesc2 AS Region" & _
      ", ROUND(Sum(col1),4) AS 'YTD LY'" & _
      ", ROUND(SUM(col2),4) AS 'YTD TY'" & _
      ", ROUND(SUM(col3),4) AS 'YTD QF'" & _
      ", CASE WHEN SUM(col2) <> 0 THEN ROUND(1-SUM(col1)/SUM(col2),4) ELSE 0.0 END as 'LY vs TY'" & _
      ", CASE WHEN SUM(col2) <> 0 THEN ROUND(1-SUM(col3)/SUM(col2),4) ELSE 0.0 END as 'QF vs TY'" & _
      "FROM (" & _
      sqlLY & " UNION ALL " & sqlTY & " UNION ALL " & sqlQF & _
      ") as tblDir " & _
      "GROUP BY strRegionOrder, strRegionDesc2 " & _
      "ORDER BY strRegionOrder, strRegionDesc2"

      ds.Tables(0).Merge(db.doQuery(sql).Tables(0).Copy())

      Return ds
      'query eg. in satan-server
      '    SELECT     strRegionOrder AS '#', strRegionDesc2 AS Region, ROUND(SUM(col1), 4) AS 'YTD LY', ROUND(SUM(col2), 4) AS 'YTD TY', ROUND(SUM(col3), 4) 
      '                      AS 'YTD QF', CASE WHEN SUM(col2) <> 0 THEN ROUND(1 - SUM(col1) / SUM(col2), 4) ELSE 0.0 END AS 'LY vs TY', CASE WHEN SUM(col2) 
      '                      <> 0 THEN ROUND(1 - SUM(col3) / SUM(col2), 4) ELSE 0.0 END AS 'QF vs TY'
      'FROM         (SELECT     dwdCustomer.strRegionOrder, dwdCustomer.strRegionDesc2, ROUND(SUM(dwfSales.dbl9lTotal), 4) AS col1, 0 AS col2, 0 AS col3
      '                       FROM          dwdProduct INNER JOIN
      '                                              dwfSales ON dwdProduct.strProdCode = dwfSales.refProdCode INNER JOIN
      '                                              dwdCustomer ON dwfSales.refCustomerNo = dwdCustomer.strCustomerNo
      '                       WHERE      (dwdProduct.refBrandID = 6) AND (dwfSales.refPeriod >= '200807') AND (dwfSales.refPeriod <= '200909')
      '                       GROUP BY dwdCustomer.strRegionOrder, dwdCustomer.strRegionDesc2
      '                       UNION ALL
      '                       SELECT     '99' AS strRegionOrder, 'Total' AS strRegionDesc2, ROUND(SUM(dwfSales_3.dbl9lTotal), 4) AS col1, 0 AS col2, 0 AS col3
      '                       FROM         dwdProduct AS dwdProduct_3 INNER JOIN
      '                                             dwfSales AS dwfSales_3 ON dwdProduct_3.strProdCode = dwfSales_3.refProdCode INNER JOIN
      '                                             dwdCustomer AS dwdCustomer_5 ON dwfSales_3.refCustomerNo = dwdCustomer_5.strCustomerNo
      '                       WHERE     (dwdProduct_3.refBrandID = 6) AND (dwfSales_3.refPeriod >= '200807') AND (dwfSales_3.refPeriod <= '200909')
      '                       UNION ALL
      '                       SELECT     dwdCustomer_4.strRegionOrder, dwdCustomer_4.strRegionDesc2, 0 AS col1, ROUND(SUM(dwfSales_2.dbl9lTotal), 4) AS col2, 
      '                                             0 AS col3
      '                       FROM         dwdProduct AS dwdProduct_2 INNER JOIN
      '                                             dwfSales AS dwfSales_2 ON dwdProduct_2.strProdCode = dwfSales_2.refProdCode INNER JOIN
      '                                             dwdCustomer AS dwdCustomer_4 ON dwfSales_2.refCustomerNo = dwdCustomer_4.strCustomerNo
      '                       WHERE     (dwdProduct_2.refBrandID = 6) AND (dwfSales_2.refPeriod >= '200907') AND (dwfSales_2.refPeriod <= '201009')
      '                       GROUP BY dwdCustomer_4.strRegionOrder, dwdCustomer_4.strRegionDesc2
      '                       UNION ALL
      '                       SELECT     '99' AS strRegionOrder, 'Total' AS strRegionDesc2, 0 AS col1, ROUND(SUM(dwfSales_1.dbl9lTotal), 4) AS col2, 0 AS col3
      '                       FROM         dwdProduct AS dwdProduct_1 INNER JOIN
      '                                             dwfSales AS dwfSales_1 ON dwdProduct_1.strProdCode = dwfSales_1.refProdCode INNER JOIN
      '                                             dwdCustomer AS dwdCustomer_3 ON dwfSales_1.refCustomerNo = dwdCustomer_3.strCustomerNo
      '                       WHERE     (dwdProduct_1.refBrandID = 6) AND (dwfSales_1.refPeriod >= '200907') AND (dwfSales_1.refPeriod <= '201009')
      '                       UNION ALL
      '                       SELECT     dwdCustomer_2.strRegionOrder, dwdCustomer_2.strRegionDesc2, 0 AS col1, 0 AS col2, ROUND(SUM(dwfQFAdjustment.dblQF), 4) 
      '                                             AS col3
      '                       FROM         dwdCustomer AS dwdCustomer_2 INNER JOIN
      '                                             dwfQFAdjustment ON dwdCustomer_2.strCustomerNo = dwfQFAdjustment.strCustomerNo
      '                       WHERE     (dwfQFAdjustment.refBrandID = 6) AND (dwfQFAdjustment.strPeriod >= '200907') AND (dwfQFAdjustment.strPeriod <= '201009')
      '                       GROUP BY dwdCustomer_2.strRegionOrder, dwdCustomer_2.strRegionDesc2
      '                       UNION ALL
      '                       SELECT     '99' AS strRegionOrder, 'Total' AS strRegionDesc2, 0 AS col1, 0 AS col2, ROUND(SUM(dwfQFAdjustment_1.dblQF), 4) AS col3
      '                       FROM         dwdCustomer AS dwdCustomer_1 INNER JOIN
      '                                             dwfQFAdjustment AS dwfQFAdjustment_1 ON dwdCustomer_1.strCustomerNo = dwfQFAdjustment_1.strCustomerNo
      '                       WHERE     (dwfQFAdjustment_1.refBrandID = 6) AND (dwfQFAdjustment_1.strPeriod >= '200907') AND (dwfQFAdjustment_1.strPeriod <= '201009')) 
      '                      AS tblDir
      'GROUP BY strRegionOrder, strRegionDesc2
      'ORDER BY '#', Region
   End Function

   '20110318 - v1.0.1 - added YTD Actual vs QF - pj
   ' get ytd actual - ytd QF
   Private Function getYtdActualVsQF() As System.Data.DataSet
      Dim ds, dsMerged As New System.Data.DataSet
      Dim sql, sqlActual, sqlQF As String
      Dim regionHAVING As String = ""

      ds.Tables.Add()

      'get YTD Actual
      sqlActual = "SELECT strRegionOrder, strRegionDesc2, Round(SUM(dbl9lTotal),4) AS col1, 0 as col2 " & _
      "FROM (dwdProduct INNER JOIN dwfSales ON dwdProduct.strProdCode = dwfSales.refProdCode) INNER JOIN dwdCustomer ON dwfSales.refCustomerNo = dwdCustomer.strCustomerNo " & _
      "WHERE refBrandID = " & Me.BrandID & " AND refPeriod >= '" & Me.DateFinYearStart.ToString("yyyyMM") & "' AND refPeriod < '" & Duodecimal.Add(Date.Today, 0).ToString("yyyyMM") & _
      "' GROUP BY dwdCustomer.strRegionOrder, dwdCustomer.strRegionDesc2 " & _
      "UNION ALL " & _
      "SELECT '0' AS strRegionOrder, 'Global' AS strRegionDesc2, Round(SUM(dbl9lTotal),4) AS col1, 0 as col2 " & _
      "FROM (dwdProduct INNER JOIN dwfSales ON dwdProduct.strProdCode = dwfSales.refProdCode) INNER JOIN dwdCustomer ON dwfSales.refCustomerNo = dwdCustomer.strCustomerNo " & _
      "WHERE refBrandID = " & Me.BrandID & " AND refPeriod >= '" & Me.DateFinYearStart.ToString("yyyyMM") & "' AND refPeriod < '" & Duodecimal.Add(Date.Today, 0).ToString("yyyyMM") & "' "

      'get YTD QF
      sqlQF = "SELECT strRegionOrder, strRegionDesc2, 0 as col1, Round(SUM(dwfQFAdjustment.dblQF),4) AS col2 " & _
      "FROM dwdCustomer LEFT JOIN dwfQFAdjustment ON dwdCustomer.strCustomerNo = dwfQFAdjustment.strCustomerNo " & _
      "WHERE refBrandID = " & Me.BrandID & " AND strPeriod >= '" & Me.DateFinYearStart.ToString("yyyyMM") & "' AND strPeriod < '" & Duodecimal.Add(Date.Today, 0).ToString("yyyyMM") & _
      "' GROUP BY dwdCustomer.strRegionOrder, dwdCustomer.strRegionDesc2 " & _
      "UNION ALL " & _
      "SELECT '0' AS strRegionOrder, 'Global' AS strRegionDesc2, 0 as col1, Round(SUM(dwfQFAdjustment.dblQF),4) AS col2 " & _
      "FROM dwdCustomer LEFT JOIN dwfQFAdjustment ON dwdCustomer.strCustomerNo = dwfQFAdjustment.strCustomerNo " & _
      "WHERE refBrandID = " & Me.BrandID & " AND strPeriod >= '" & Me.DateFinYearStart.ToString("yyyyMM") & "' AND strPeriod < '" & Duodecimal.Add(Date.Today, 0).ToString("yyyyMM") & "' "

      If Me.Region <> "- All -" Then regionHAVING = " AND strRegionDesc2 IN('" & Me.Region & "','Global') "
      'UNION DIR 
      sql = "SELECT strRegionOrder AS '#', strRegionDesc2 AS Region" & _
      ", SUM(col1)-SUM(col2) as 'YTD v.s. QF'" & _
      "FROM (" & _
      sqlActual & " UNION ALL " & sqlQF & _
      ") as tblDir " & _
      "GROUP BY strRegionOrder, strRegionDesc2 " & _
      "HAVING 1=1 " & regionHAVING & _
      "ORDER BY strRegionOrder, strRegionDesc2"

      ds.Tables(0).Merge(db.doQuery(sql).Tables(0).Copy())

      Return ds
   End Function

   '20110318 - v1.0.1 - added get12/6/3 month growth - pj
   Private Function getGrowth() As System.Data.DataSet
      'notes: X months grow = (last X months this year / same X months last year) - 1
      'exclude current month, eg. if now = march then last 12months = march ly to feb ty [ >= y-1 && < y]
      Dim ds, dsMerged As New System.Data.DataSet
      Dim sql, sql12ty, sql12ly, sql6ty, sql6ly, sql3ty, sql3ly As String
      Dim regionHAVING As String = ""

      ds.Tables.Add()
      'Last 12 Months this year: col1
      sql12ty = "SELECT strRegionOrder, strRegionDesc2, Round(SUM(dbl9lTotal),4) AS col1, 0 as col2, 0 as col3, 0 as col4, 0 AS col5, 0 AS col6 " & _
      "FROM (dwdProduct INNER JOIN dwfSales ON dwdProduct.strProdCode = dwfSales.refProdCode) INNER JOIN dwdCustomer ON dwfSales.refCustomerNo = dwdCustomer.strCustomerNo " & _
      "WHERE refBrandID = " & Me.BrandID & " AND refPeriod >= '" & Duodecimal.Add(Date.Today, -12).ToString("yyyyMM") & "' AND refPeriod < '" & Duodecimal.Add(Date.Today, 0).ToString("yyyyMM") & _
      "' GROUP BY dwdCustomer.strRegionOrder, dwdCustomer.strRegionDesc2 " & _
      "UNION ALL " & _
      "SELECT '0' AS strRegionOrder, 'Global' AS strRegionDesc2, Round(SUM(dbl9lTotal),4) AS col1, 0 as col2, 0 as col3, 0 as col4, 0 AS col5, 0 AS col6 " & _
      "FROM (dwdProduct INNER JOIN dwfSales ON dwdProduct.strProdCode = dwfSales.refProdCode) INNER JOIN dwdCustomer ON dwfSales.refCustomerNo = dwdCustomer.strCustomerNo " & _
      "WHERE refBrandID = " & Me.BrandID & " AND refPeriod >= '" & Duodecimal.Add(Date.Today, -12).ToString("yyyyMM") & "' AND refPeriod < '" & Duodecimal.Add(Date.Today, 0).ToString("yyyyMM") & "' "

      'Last 12 Months last year: col2
      sql12ly = "SELECT strRegionOrder, strRegionDesc2, 0 AS col1, Round(SUM(dbl9lTotal),4) as col2, 0 as col3, 0 as col4, 0 AS col5, 0 AS col6 " & _
      "FROM (dwdProduct INNER JOIN dwfSales ON dwdProduct.strProdCode = dwfSales.refProdCode) INNER JOIN dwdCustomer ON dwfSales.refCustomerNo = dwdCustomer.strCustomerNo " & _
      "WHERE refBrandID = " & Me.BrandID & " AND refPeriod >= '" & Duodecimal.Add(Date.Today, -24).ToString("yyyyMM") & "' AND refPeriod < '" & Duodecimal.Add(Date.Today, -12).ToString("yyyyMM") & _
      "' GROUP BY dwdCustomer.strRegionOrder, dwdCustomer.strRegionDesc2 " & _
      "UNION ALL " & _
      "SELECT '0' AS strRegionOrder, 'Global' AS strRegionDesc2, 0 AS col1, Round(SUM(dbl9lTotal),4) as col2, 0 as col3, 0 as col4, 0 AS col5, 0 AS col6 " & _
      "FROM (dwdProduct INNER JOIN dwfSales ON dwdProduct.strProdCode = dwfSales.refProdCode) INNER JOIN dwdCustomer ON dwfSales.refCustomerNo = dwdCustomer.strCustomerNo " & _
      "WHERE refBrandID = " & Me.BrandID & " AND refPeriod >= '" & Duodecimal.Add(Date.Today, -24).ToString("yyyyMM") & "' AND refPeriod < '" & Duodecimal.Add(Date.Today, -12).ToString("yyyyMM") & "' "

      'Last 6 Months this year: col3
      sql6ty = "SELECT strRegionOrder, strRegionDesc2, 0 AS col1, 0 as col2, Round(SUM(dbl9lTotal),4) as col3, 0 as col4, 0 AS col5, 0 AS col6 " & _
      "FROM (dwdProduct INNER JOIN dwfSales ON dwdProduct.strProdCode = dwfSales.refProdCode) INNER JOIN dwdCustomer ON dwfSales.refCustomerNo = dwdCustomer.strCustomerNo " & _
      "WHERE refBrandID = " & Me.BrandID & " AND refPeriod >= '" & Duodecimal.Add(Date.Today, -6).ToString("yyyyMM") & "' AND refPeriod < '" & Duodecimal.Add(Date.Today, 0).ToString("yyyyMM") & _
      "' GROUP BY dwdCustomer.strRegionOrder, dwdCustomer.strRegionDesc2 " & _
      "UNION ALL " & _
      "SELECT '0' AS strRegionOrder, 'Global' AS strRegionDesc2, 0 AS col1, 0 as col2, Round(SUM(dbl9lTotal),4) as col3, 0 as col4, 0 AS col5, 0 AS col6 " & _
      "FROM (dwdProduct INNER JOIN dwfSales ON dwdProduct.strProdCode = dwfSales.refProdCode) INNER JOIN dwdCustomer ON dwfSales.refCustomerNo = dwdCustomer.strCustomerNo " & _
      "WHERE refBrandID = " & Me.BrandID & " AND refPeriod >= '" & Duodecimal.Add(Date.Today, -6).ToString("yyyyMM") & "' AND refPeriod < '" & Duodecimal.Add(Date.Today, 0).ToString("yyyyMM") & "' "

      'Last 6 Months last year: col4
      sql6ly = "SELECT strRegionOrder, strRegionDesc2, 0 AS col1, 0 as col2, 0 as col3, Round(SUM(dbl9lTotal),4) as col4, 0 AS col5, 0 AS col6 " & _
      "FROM (dwdProduct INNER JOIN dwfSales ON dwdProduct.strProdCode = dwfSales.refProdCode) INNER JOIN dwdCustomer ON dwfSales.refCustomerNo = dwdCustomer.strCustomerNo " & _
      "WHERE refBrandID = " & Me.BrandID & " AND refPeriod >= '" & Duodecimal.Add(Date.Today, -18).ToString("yyyyMM") & "' AND refPeriod < '" & Duodecimal.Add(Date.Today, -12).ToString("yyyyMM") & _
      "' GROUP BY dwdCustomer.strRegionOrder, dwdCustomer.strRegionDesc2 " & _
      "UNION ALL " & _
      "SELECT '0' AS strRegionOrder, 'Global' AS strRegionDesc2, 0 AS col1, 0 as col2, 0 as col3, Round(SUM(dbl9lTotal),4) as col4, 0 AS col5, 0 AS col6 " & _
      "FROM (dwdProduct INNER JOIN dwfSales ON dwdProduct.strProdCode = dwfSales.refProdCode) INNER JOIN dwdCustomer ON dwfSales.refCustomerNo = dwdCustomer.strCustomerNo " & _
      "WHERE refBrandID = " & Me.BrandID & " AND refPeriod >= '" & Duodecimal.Add(Date.Today, -18).ToString("yyyyMM") & "' AND refPeriod < '" & Duodecimal.Add(Date.Today, -12).ToString("yyyyMM") & "' "

      'Last 3 Months this year: col5
      sql3ty = "SELECT strRegionOrder, strRegionDesc2, 0 AS col1, 0 as col2, 0 as col3, 0 as col4, Round(SUM(dbl9lTotal),4) AS col5, 0 AS col6 " & _
      "FROM (dwdProduct INNER JOIN dwfSales ON dwdProduct.strProdCode = dwfSales.refProdCode) INNER JOIN dwdCustomer ON dwfSales.refCustomerNo = dwdCustomer.strCustomerNo " & _
      "WHERE refBrandID = " & Me.BrandID & " AND refPeriod >= '" & Duodecimal.Add(Date.Today, -3).ToString("yyyyMM") & "' AND refPeriod < '" & Duodecimal.Add(Date.Today, 0).ToString("yyyyMM") & _
      "' GROUP BY dwdCustomer.strRegionOrder, dwdCustomer.strRegionDesc2 " & _
      "UNION ALL " & _
      "SELECT '0' AS strRegionOrder, 'Global' AS strRegionDesc2, 0 AS col1, 0 as col2, 0 as col3, 0 as col4, Round(SUM(dbl9lTotal),4) AS col5, 0 AS col6 " & _
      "FROM (dwdProduct INNER JOIN dwfSales ON dwdProduct.strProdCode = dwfSales.refProdCode) INNER JOIN dwdCustomer ON dwfSales.refCustomerNo = dwdCustomer.strCustomerNo " & _
      "WHERE refBrandID = " & Me.BrandID & " AND refPeriod >= '" & Duodecimal.Add(Date.Today, -3).ToString("yyyyMM") & "' AND refPeriod < '" & Duodecimal.Add(Date.Today, 0).ToString("yyyyMM") & "' "

      'Last 3 Months last year: col6
      sql3ly = "SELECT strRegionOrder, strRegionDesc2, 0 AS col1, 0 as col2, 0 as col3, 0 as col4, 0 AS col5, Round(SUM(dbl9lTotal),4) AS col6 " & _
      "FROM (dwdProduct INNER JOIN dwfSales ON dwdProduct.strProdCode = dwfSales.refProdCode) INNER JOIN dwdCustomer ON dwfSales.refCustomerNo = dwdCustomer.strCustomerNo " & _
      "WHERE refBrandID = " & Me.BrandID & " AND refPeriod >= '" & Duodecimal.Add(Date.Today, -15).ToString("yyyyMM") & "' AND refPeriod < '" & Duodecimal.Add(Date.Today, -12).ToString("yyyyMM") & _
      "' GROUP BY dwdCustomer.strRegionOrder, dwdCustomer.strRegionDesc2 " & _
      "UNION ALL " & _
      "SELECT '0' AS strRegionOrder, 'Global' AS strRegionDesc2, 0 as col1, 0 as col2, 0 as col3, 0 as col4, 0 AS col5, Round(SUM(dbl9lTotal),4) AS col6 " & _
      "FROM (dwdProduct INNER JOIN dwfSales ON dwdProduct.strProdCode = dwfSales.refProdCode) INNER JOIN dwdCustomer ON dwfSales.refCustomerNo = dwdCustomer.strCustomerNo " & _
      "WHERE refBrandID = " & Me.BrandID & " AND refPeriod >= '" & Duodecimal.Add(Date.Today, -15).ToString("yyyyMM") & "' AND refPeriod < '" & Duodecimal.Add(Date.Today, -12).ToString("yyyyMM") & "' "


      If Me.Region <> "- All -" Then regionHAVING = " AND strRegionDesc2 IN('" & Me.Region & "','Global') "
      'UNION DIR 
      sql = "SELECT strRegionOrder AS '#', strRegionDesc2 AS Region" & _
      ", CASE WHEN SUM(col2) <> 0 THEN ROUND(SUM(col1)/SUM(col2) -1,4) ELSE 0.0 END as '% Gro 12'" & _
      ", CASE WHEN SUM(col4) <> 0 THEN ROUND(SUM(col3)/SUM(col4) -1,4) ELSE 0.0 END as '% Gro 6'" & _
      ", CASE WHEN SUM(col6) <> 0 THEN ROUND(SUM(col5)/SUM(col6) -1,4) ELSE 0.0 END as '% Gro 3'" & _
      "FROM (" & _
      sql12ty & " UNION ALL " & sql12ly & " UNION ALL " & sql6ty & " UNION ALL " & sql6ly & " UNION ALL " & sql3ty & " UNION ALL " & sql3ly & _
      ") as tblDir " & _
      "GROUP BY strRegionOrder, strRegionDesc2 " & _
      "HAVING 1=1 " & regionHAVING & _
      "ORDER BY strRegionOrder, strRegionDesc2"

      '", ROUND(Sum(col1),4) AS 'FY " & Me.DateFinYearEndMin2.ToString("yyyy") & "'" & _
      '", ROUND(SUM(col2),4) AS 'FY " & Me.DateFinYearEndMin1.ToString("yyyy") & "'" & _
      '", CASE WHEN SUM(col2) <> 0 THEN ROUND(SUM(col3)/SUM(col1) -1,4) ELSE 0.0 END as '% Gro 12'" & _
      '", ROUND(SUM(col4),4) as 'QF Plan " & Me.FinYear & "'" & _
      '", CASE WHEN SUM(col2) <> 0 THEN ROUND(SUM(col4)/SUM(col1) -1,4) ELSE 0.0 END as '% Gro Plan'" & _
      '", ROUND(SUM(col5),4) as 'QF Adj " & Me.FinYear & "'" & _
      '", CASE WHEN SUM(col2) <> 0 THEN ROUND(SUM(col5)/SUM(col1) -1,4) ELSE 0.0 END as '% Gro Adj'" & _
      '", ROUND(SUM(col3),4) AS 'Last 12 Mnths'" & _

      ds.Tables(0).Merge(db.doQuery(sql).Tables(0).Copy())

      Return ds
   End Function

   Private Function getStats() As System.Data.DataSet
      Dim ds, dsMerged As New System.Data.DataSet
      Dim sql, sqlFY2, sqlFY1, sqlM12, sqlQFP, sqlQFA As String
      'notes: Adding to this is a pain! problem is its semi impossible getting the calculation shit right without doing it in code. this way its locked-in in sql. quick but complicated

      'ini

      ds.Tables.Add()

      'start: get the fields for the stats table:

      'FIN YEAR -2: col1
      sqlFY2 = "SELECT strRegionOrder, strRegionDesc2, Round(SUM(dbl9lTotal),4) AS col1, 0 as col2, 0 as col3, 0 as col4, 0 AS col5 " & _
      "FROM (dwdProduct INNER JOIN dwfSales ON dwdProduct.strProdCode = dwfSales.refProdCode) INNER JOIN dwdCustomer ON dwfSales.refCustomerNo = dwdCustomer.strCustomerNo " & _
      "WHERE refBrandID = " & Me.BrandID & " AND refPeriod >= '" & Me.DateFinYearStartMin2.ToString("yyyyMM") & "' AND refPeriod <= '" & Me.DateFinYearEndMin2.ToString("yyyyMM") & "' " & _
      "GROUP BY dwdCustomer.strRegionOrder, dwdCustomer.strRegionDesc2 " & _
      "UNION ALL " & _
      "SELECT '0' AS strRegionOrder, 'Global' AS strRegionDesc2, Round(SUM(dbl9lTotal),4) AS col1, 0 as col2, 0 as col3, 0 as col4, 0 AS col5 " & _
      "FROM (dwdProduct INNER JOIN dwfSales ON dwdProduct.strProdCode = dwfSales.refProdCode) INNER JOIN dwdCustomer ON dwfSales.refCustomerNo = dwdCustomer.strCustomerNo " & _
      "WHERE refBrandID = " & Me.BrandID & " AND refPeriod >= '" & Me.DateFinYearStartMin2.ToString("yyyyMM") & "' AND refPeriod <= '" & Me.DateFinYearEndMin2.ToString("yyyyMM") & "' "

      'FIN YEAR -1: col2
      sqlFY1 = "SELECT strRegionOrder, strRegionDesc2, 0 as col1, Round(SUM(dbl9lTotal),4) AS col2, 0 as col3, 0 as col4, 0 AS col5 " & _
      "FROM (dwdProduct INNER JOIN dwfSales ON dwdProduct.strProdCode = dwfSales.refProdCode) INNER JOIN dwdCustomer ON dwfSales.refCustomerNo = dwdCustomer.strCustomerNo " & _
      "WHERE refBrandID = " & Me.BrandID & " AND refPeriod >= '" & Me.DateFinYearStartMin1.ToString("yyyyMM") & "' AND refPeriod <= '" & Me.DateFinYearEndMin1.ToString("yyyyMM") & "' " & _
      "GROUP BY dwdCustomer.strRegionOrder, dwdCustomer.strRegionDesc2 " & _
      "UNION ALL " & _
      "SELECT '0' AS strRegionOrder, 'Global' AS strRegionDesc2, 0 as col1, Round(SUM(dbl9lTotal),4) AS col2, 0 as col3, 0 as col4, 0 AS col5 " & _
      "FROM (dwdProduct INNER JOIN dwfSales ON dwdProduct.strProdCode = dwfSales.refProdCode) INNER JOIN dwdCustomer ON dwfSales.refCustomerNo = dwdCustomer.strCustomerNo " & _
      "WHERE refBrandID = " & Me.BrandID & " AND refPeriod >= '" & Me.DateFinYearStartMin1.ToString("yyyyMM") & "' AND refPeriod <= '" & Me.DateFinYearEndMin1.ToString("yyyyMM") & "' "

      'Last 12 Months: col3
      sqlM12 = "SELECT strRegionOrder, strRegionDesc2, 0 as col1, 0 as col2, Round(SUM(dbl9lTotal),4) AS col3, 0 as col4, 0 AS col5 " & _
      "FROM (dwdProduct INNER JOIN dwfSales ON dwdProduct.strProdCode = dwfSales.refProdCode) INNER JOIN dwdCustomer ON dwfSales.refCustomerNo = dwdCustomer.strCustomerNo " & _
      "WHERE refBrandID = " & Me.BrandID & " AND refPeriod >= '" & Duodecimal.Add(Date.Today, -12).ToString("yyyyMM") & "' AND refPeriod <= '" & Duodecimal.Add(Date.Today, 0).ToString("yyyyMM") & _
      "' GROUP BY dwdCustomer.strRegionOrder, dwdCustomer.strRegionDesc2 " & _
      "UNION ALL " & _
      "SELECT '0' AS strRegionOrder, 'Global' AS strRegionDesc2, 0 as col1, 0 as col2, Round(SUM(dbl9lTotal),4) AS col3, 0 as col4, 0 AS col5 " & _
      "FROM (dwdProduct INNER JOIN dwfSales ON dwdProduct.strProdCode = dwfSales.refProdCode) INNER JOIN dwdCustomer ON dwfSales.refCustomerNo = dwdCustomer.strCustomerNo " & _
      "WHERE refBrandID = " & Me.BrandID & " AND refPeriod >= '" & Duodecimal.Add(Date.Today, -12).ToString("yyyyMM") & "' AND refPeriod <= '" & Duodecimal.Add(Date.Today, 0).ToString("yyyyMM") & "' "

      'QF PLANNED: col4
      sqlQFP = "SELECT strRegionOrder, strRegionDesc2, 0 as col1, 0 as col2, 0 AS col3, Round(SUM(dblQF),4) as col4, 0 AS col5 " & _
      "FROM dwdCustomer INNER JOIN dwfQFPlan ON dwdCustomer.strCustomerNo = dwfQFPlan.strCustomerNo " & _
      "WHERE refBrandID = " & Me.BrandID & " AND strPeriod >= '" & Me.DateFinYearStart.ToString("yyyyMM") & "' AND strPeriod <= '" & Me.DateFinYearEnd.ToString("yyyyMM") & "' " & _
      "GROUP BY dwdCustomer.strRegionOrder, dwdCustomer.strRegionDesc2 " & _
      "UNION ALL " & _
      "SELECT '0' AS strRegionOrder, 'Global' AS strRegionDesc2, 0 as col1, 0 as col2, 0 as col3, Round(SUM(dblQF),4) as col4, 0 AS col5 " & _
      "FROM dwdCustomer INNER JOIN dwfQFPlan ON dwdCustomer.strCustomerNo = dwfQFPlan.strCustomerNo " & _
      "WHERE refBrandID = " & Me.BrandID & " AND strPeriod >= '" & Me.DateFinYearStart.ToString("yyyyMM") & "' AND strPeriod <= '" & Me.DateFinYearEnd.ToString("yyyyMM") & "' "

      'QF ADJUSTED: col5
      sqlQFA = "SELECT strRegionOrder, strRegionDesc2, 0 as col1, 0 as col2, 0 AS col3, 0 AS col4, Round(SUM(dblQF),4) as col5 " & _
      "FROM dwdCustomer INNER JOIN dwfQFAdjustment ON dwdCustomer.strCustomerNo = dwfQFAdjustment.strCustomerNo " & _
      "WHERE refBrandID = " & Me.BrandID & " AND strPeriod >= '" & Me.DateFinYearStart.ToString("yyyyMM") & "' AND strPeriod <= '" & Me.DateFinYearEnd.ToString("yyyyMM") & "' " & _
      "GROUP BY dwdCustomer.strRegionOrder, dwdCustomer.strRegionDesc2 " & _
      "UNION ALL " & _
      "SELECT '0' AS strRegionOrder, 'Global' AS strRegionDesc2, 0 as col1, 0 as col2, 0 as col3, 0 AS col4, Round(SUM(dblQF),4) as col5 " & _
      "FROM dwdCustomer INNER JOIN dwfQFAdjustment ON dwdCustomer.strCustomerNo = dwfQFAdjustment.strCustomerNo " & _
      "WHERE refBrandID = " & Me.BrandID & " AND strPeriod >= '" & Me.DateFinYearStart.ToString("yyyyMM") & "' AND strPeriod <= '" & Me.DateFinYearEnd.ToString("yyyyMM") & "' "


      'UNION DIR //changed % Gro to colX/col1 pj - 20101029
      sql = "SELECT strRegionOrder AS '#', strRegionDesc2 AS Region" & _
      ", ROUND(Sum(col1),4) AS 'FY " & Me.DateFinYearEndMin2.ToString("yyyy") & "'" & _
      ", ROUND(SUM(col2),4) AS 'FY " & Me.DateFinYearEndMin1.ToString("yyyy") & "'" & _
      ", CASE WHEN SUM(col1) <> 0 THEN ROUND(SUM(col2)/SUM(col1) -1,4) ELSE 0.0 END as '% Gro'" & _
      ", ROUND(SUM(col3),4) AS 'Last 12 Mnths'" & _
      ", CASE WHEN SUM(col2) <> 0 THEN ROUND(SUM(col3)/SUM(col1) -1,4) ELSE 0.0 END as '% Gro 12'" & _
      ", ROUND(SUM(col4),4) as 'QF Plan " & Me.FinYear & "'" & _
      ", CASE WHEN SUM(col2) <> 0 THEN ROUND(SUM(col4)/SUM(col1) -1,4) ELSE 0.0 END as '% Gro Plan'" & _
      ", ROUND(SUM(col5),4) as 'QF Adj " & Me.FinYear & "'" & _
      ", CASE WHEN SUM(col2) <> 0 THEN ROUND(SUM(col5)/SUM(col1) -1,4) ELSE 0.0 END as '% Gro Adj'" & _
      "FROM (" & _
      sqlFY2 & " UNION ALL " & sqlFY1 & " UNION ALL " & sqlM12 & " UNION ALL " & sqlQFP & " UNION ALL " & sqlQFA & _
      ") as tblDir " & _
      "GROUP BY strRegionOrder, strRegionDesc2 " & _
      "ORDER BY strRegionOrder, strRegionDesc2"

      ds.Tables(0).Merge(db.doQuery(sql).Tables(0).Copy())

      Return ds
      'query eg. in satan-server
      '    SELECT     strRegionOrder AS '#', strRegionDesc2 AS Region, ROUND(SUM(col1), 4) AS 'FY 2008', ROUND(SUM(col2), 4) AS 'FY 2009', CASE WHEN SUM(col1) 
      '                      <> 0 THEN ROUND(1 - SUM(col2) / SUM(col1), 4) ELSE 0.0 END AS '% Gro', ROUND(SUM(col3), 4) AS 'Last 12 Mnths', CASE WHEN SUM(col2) 
      '                      <> 0 THEN ROUND(1 - SUM(col3) / SUM(col2), 4) ELSE 0.0 END AS '% Gro 12', ROUND(SUM(col4), 4) AS 'QF Plan 2010'
      'FROM         (SELECT     dwdCustomer.strRegionOrder, dwdCustomer.strRegionDesc2, ROUND(SUM(dwfSales.dbl9lTotal), 4) AS col1, 0 AS col2, 0 AS col3, 
      '                                              0 AS col4
      '                       FROM          dwdProduct INNER JOIN
      '                                              dwfSales ON dwdProduct.strProdCode = dwfSales.refProdCode INNER JOIN
      '                                              dwdCustomer ON dwfSales.refCustomerNo = dwdCustomer.strCustomerNo
      '                       WHERE      (dwdProduct.refBrandID = 6) AND (dwfSales.refPeriod >= '200707') AND (dwfSales.refPeriod <= '200806')
      '                       GROUP BY dwdCustomer.strRegionOrder, dwdCustomer.strRegionDesc2
      '                       UNION ALL
      '                       SELECT     dwdCustomer_5.strRegionOrder, dwdCustomer_5.strRegionDesc2, 0 AS col1, ROUND(SUM(dwfSales_3.dbl9lTotal), 4) AS col2, 0 AS col3, 
      '                                             0 AS col4
      '                       FROM         dwdProduct AS dwdProduct_3 INNER JOIN
      '                                             dwfSales AS dwfSales_3 ON dwdProduct_3.strProdCode = dwfSales_3.refProdCode INNER JOIN
      '                                             dwdCustomer AS dwdCustomer_5 ON dwfSales_3.refCustomerNo = dwdCustomer_5.strCustomerNo
      '                       WHERE     (dwdProduct_3.refBrandID = 6) AND (dwfSales_3.refPeriod >= '200807') AND (dwfSales_3.refPeriod <= '200906')
      '                       GROUP BY dwdCustomer_5.strRegionOrder, dwdCustomer_5.strRegionDesc2
      '                       UNION ALL
      '                       SELECT     dwdCustomer_4.strRegionOrder, dwdCustomer_4.strRegionDesc2, 0 AS col1, 0 AS col2, ROUND(SUM(dwfSales_2.dbl9lTotal), 4) AS col3, 
      '                                             0 AS col4
      '                       FROM         dwdProduct AS dwdProduct_2 INNER JOIN
      '                                             dwfSales AS dwfSales_2 ON dwdProduct_2.strProdCode = dwfSales_2.refProdCode INNER JOIN
      '                                             dwdCustomer AS dwdCustomer_4 ON dwfSales_2.refCustomerNo = dwdCustomer_4.strCustomerNo
      '                       WHERE     (dwdProduct_2.refBrandID = 6) AND (dwfSales_2.refPeriod >= '200909') AND (dwfSales_2.refPeriod <= '201009')
      '                       GROUP BY dwdCustomer_4.strRegionOrder, dwdCustomer_4.strRegionDesc2
      '                       UNION ALL
      '                       SELECT     '99' AS strRegionOrder, 'Total' AS strRegionDesc2, 0 AS col1, 0 AS col2, ROUND(SUM(dwfSales_1.dbl9lTotal), 4) AS col3, 0 AS col4
      '                       FROM         dwdProduct AS dwdProduct_1 INNER JOIN
      '                                             dwfSales AS dwfSales_1 ON dwdProduct_1.strProdCode = dwfSales_1.refProdCode INNER JOIN
      '                                             dwdCustomer AS dwdCustomer_3 ON dwfSales_1.refCustomerNo = dwdCustomer_3.strCustomerNo
      '                       WHERE     (dwdProduct_1.refBrandID = 6) AND (dwfSales_1.refPeriod >= '200909') AND (dwfSales_1.refPeriod <= '201009')
      '                       UNION ALL
      '                       SELECT     dwdCustomer_2.strRegionOrder, dwdCustomer_2.strRegionDesc2, 0 AS col1, 0 AS col2, 0 AS col3, ROUND(SUM(dwfQFPlan.dblQF), 4) 
      '                                             AS col4
      '                       FROM         dwdCustomer AS dwdCustomer_2 INNER JOIN
      '                                             dwfQFPlan ON dwdCustomer_2.strCustomerNo = dwfQFPlan.strCustomerNo
      '                       WHERE     (dwfQFPlan.refBrandID = 6) AND (dwfQFPlan.strPeriod >= '200909') AND (dwfQFPlan.strPeriod <= '201009')
      '                       GROUP BY dwdCustomer_2.strRegionOrder, dwdCustomer_2.strRegionDesc2
      '                       UNION ALL
      '                       SELECT     '99' AS strRegionOrder, 'Total' AS strRegionDesc2, 0 AS col1, 0 AS col2, 0 AS col3, ROUND(SUM(dwfQFPlan_1.dblQF), 4) AS col4
      '                       FROM         dwdCustomer AS dwdCustomer_1 INNER JOIN
      '                                             dwfQFPlan AS dwfQFPlan_1 ON dwdCustomer_1.strCustomerNo = dwfQFPlan_1.strCustomerNo
      '                       WHERE     (dwfQFPlan_1.refBrandID = 6) AND (dwfQFPlan_1.strPeriod >= '200909') AND (dwfQFPlan_1.strPeriod <= '201009')) AS tblDir
      'GROUP BY strRegionOrder, strRegionDesc2
      'ORDER BY '#', Region
   End Function

   Private Function getFullYearVariance(ByVal Fact As String, Optional ByVal blnQuaters As Boolean = True) As System.Data.DataSet
      'returns merged data set for X months for fact=fact
      Dim ds, dsMerged As New System.Data.DataSet
      Dim FROM As String = ""
      Dim FROM2 As String = ""
      Dim WHERE As String = ""
      Dim WHERE2 As String = ""
      Dim ORDERBY As String = ""
      Dim subWhere As String = ""
      Dim subWhere2 As String = ""
      Dim subFact As String = ""
      Dim subFact2 As String = ""
      Dim strPeriodStart As String = ""
      Dim strPeriodEnd As String = ""
      Dim strQuarterStart As String = ""
      Dim strQuarterEnd As String = ""

      'ini
      Dim quickDate As New Duodecimal(Me.DateFinYearStart)
      strPeriodStart = quickDate.getPeriod()

      ds.Tables.Add()

      'switch Fact { case "": FROM = "" }
      Select Case Fact
         Case "VARIANCE PLANNED"
            'note: NO LEFT JOIN NEEDED
            FROM = "FROM dwdProduct INNER JOIN (dwfSales INNER JOIN dwdCustomer ON dwfSales.refCustomerNo = dwdCustomer.strCustomerNo) ON dwdProduct.strProdCode = dwfSales.refProdCode"
            subFact = "Sum(dwfSales.dbl9lTotal)"
            subWhere = "dwfSales.refPeriod"

            FROM2 = "FROM dwdCustomer INNER JOIN dwfQFPlan ON dwdCustomer.strCustomerNo = dwfQFPlan.strCustomerNo"
            subFact2 = "Sum(dwfQFPlan.dblQF)"
            subWhere2 = "dwfQFPlan.strPeriod"

            ORDERBY = "Region"

         Case "VARIANCE ADJUSTED"
            'note: NO LEFT JOIN NEEDED
            FROM = "FROM dwdProduct INNER JOIN (dwfSales INNER JOIN dwdCustomer ON dwfSales.refCustomerNo = dwdCustomer.strCustomerNo) ON dwdProduct.strProdCode = dwfSales.refProdCode"
            subFact = "Sum(dwfSales.dbl9lTotal)"
            subWhere = "dwfSales.refPeriod"

            FROM2 = "FROM dwdCustomer INNER JOIN dwfQFAdjustment ON dwdCustomer.strCustomerNo = dwfQFAdjustment.strCustomerNo"
            subFact2 = "Sum(dwfQFAdjustment.dblQF)"
            subWhere2 = "dwfQFAdjustment.strPeriod"

            ORDERBY = "Region"

      End Select

      'for 1 to 12 
      'where = _
      'getPeriodFactByRegion()
      For intPeriod As Integer = 1 To Me.intFullYearPeriods
         'INI
         WHERE = " AND (" & subWhere & "='" & quickDate.getPeriod() & "') AND (refBrandID=" & Me.BrandID & ")"
         WHERE2 = " AND (" & subWhere2 & "='" & quickDate.getPeriod() & "') AND (refBrandID=" & Me.BrandID & ")"

         ds.Tables(0).Merge( _
               Me.getPeriodVarianceByRegion(subFact, subFact2, quickDate.DuoDate.ToString("MMM"), FROM, FROM2, WHERE, WHERE2, ORDERBY, True))

         If intPeriod Mod 3 = 1 And blnQuaters Then strQuarterStart = quickDate.getPeriod()
         If intPeriod Mod 3 = 0 And blnQuaters Then
            strQuarterEnd = quickDate.getPeriod()
            'if quarter add quarter column
            WHERE = " AND " & subWhere & ">='" & strQuarterStart & "' AND " & subWhere & "<='" & strQuarterEnd & "' AND refBrandID=" & Me.BrandID
            WHERE2 = " AND " & subWhere2 & ">='" & strQuarterStart & "' AND " & subWhere2 & "<='" & strQuarterEnd & "' AND refBrandID=" & Me.BrandID

            ds.Tables(0).Merge( _
               Me.getPeriodVarianceByRegion(subFact, subFact2, "Q" & CStr(intPeriod / 3), FROM, FROM2, WHERE, WHERE2, ORDERBY, True))

         End If

         strPeriodEnd = quickDate.getPeriod()

         'POST
         quickDate.Add(1) 'inc date object
      Next

      'no totals (not in scope)

      Return ds
   End Function

   Private Function getFullYearFactNoAgrigate(ByVal Fact As String) As System.Data.DataSet
      'returns merged data set for X months for fact=fact
      Dim ds, dsMerged As New System.Data.DataSet
      Dim FROM As String = ""
      Dim WHERE As String = ""
      Dim ORDERBY As String = ""
      Dim subWhere As String = ""
      Dim subFact As String = ""
      Dim strPeriodStart As String = ""
      Dim strPeriodEnd As String = ""
      Dim strQuarterStart As String = ""
      Dim strQuarterEnd As String = ""
      Dim GROUPBY As String = ""

      'ini
      Dim quickDate As New Duodecimal(Me.DateFinYearStart)
      strPeriodStart = quickDate.getPeriod()

      ds.Tables.Add()

      'switch Fact { case "": FROM = "" }
      Select Case Fact
         Case "LOG"
            FROM = "FROM dwdCustomer LEFT JOIN dwfQFAdjustment ON dwdCustomer.strCustomerNo = dwfQFAdjustment.strCustomerNo"
            subFact = "CASE WHEN dwfQFAdjustment.strNotes <> '' THEN dwfQFAdjustment.strNotes + CHAR(13) + CHAR(13) + dwfQFAdjustment.strLog ELSE dwfQFAdjustment.strLog END"
            subWhere = "dwfQFAdjustment.strPeriod"

            ORDERBY = "Region"
            GROUPBY = ", strLog, strNotes "

         Case "APPLIED"
            FROM = "FROM dwdCustomer LEFT JOIN dwfQFAdjustment ON dwdCustomer.strCustomerNo = dwfQFAdjustment.strCustomerNo"
            subFact = "dwfQFAdjustment.blnApplied"
            subWhere = "dwfQFAdjustment.strPeriod"

            ORDERBY = "Region"
            GROUPBY = ", blnApplied "

      End Select

      'for 1 to 12 
      'where = _
      'getPeriodFactByRegion()
      For intPeriod As Integer = 1 To Me.intFullYearPeriods
         'INI
         WHERE = " AND (" & subWhere & "='" & quickDate.getPeriod() & "') AND (refBrandID=" & Me.BrandID & ")"

         ds.Tables(0).Merge( _
               Me.getPeriodFactByRegion(subFact & " AS '" & quickDate.DuoDate.ToString("MMM") & "'", FROM, WHERE, ORDERBY, False, GROUPBY))

         'no quarter, Arrr!

         strPeriodEnd = quickDate.getPeriod()

         'POST
         quickDate.Add(1) 'inc date object
      Next

      'no get totals & avg

      Return ds

   End Function

   '20110318 - v1.0.1 - added frRegion filter - pj
   Private Function getFullYearFact(ByVal Fact As String, Optional ByVal blnQuaters As Boolean = True, Optional ByVal blnTotals As Boolean = True, Optional ByVal regionHeader As String = "") As System.Data.DataSet
      'returns merged data set for X months for fact=fact
      Dim ds, dsMerged As New System.Data.DataSet
      Dim FROM As String = ""
      Dim WHERE As String = ""
      Dim ORDERBY As String = ""
      Dim subWhere As String = ""
      Dim regionWhere As String = ""
      Dim subFact As String = ""
      Dim strPeriodStart As String = ""
      Dim strPeriodEnd As String = ""
      Dim strQuarterStart As String = ""
      Dim strQuarterEnd As String = ""
      Dim blnUnion As Boolean = True

      'ini
      Dim quickDate As New Duodecimal(Me.DateFinYearStart)
      strPeriodStart = quickDate.getPeriod()

      ds.Tables.Add()

      'switch Fact { case "": FROM = "" }
      Select Case Fact
         Case "QF PLANNED"
            'note: LEFT JOIN
            FROM = "FROM dwdCustomer LEFT JOIN dwfQFPlan ON dwdCustomer.strCustomerNo = dwfQFPlan.strCustomerNo"
            subFact = "Sum(dwfQFPlan.dblQF)"
            subWhere = "dwfQFPlan.strPeriod"

            ORDERBY = "Region"

         Case "QF ADJUSTED"
            'note: LEFT JOIN
            FROM = "FROM dwdCustomer LEFT JOIN dwfQFAdjustment ON dwdCustomer.strCustomerNo = dwfQFAdjustment.strCustomerNo"
            subFact = "Sum(dwfQFAdjustment.dblQF)"
            subWhere = "dwfQFAdjustment.strPeriod"

            ORDERBY = "Region"

         Case "ACTUAL"
            FROM = "FROM (dwdCustomer LEFT JOIN dwfSales ON dwdCustomer.strCustomerNo = dwfSales.refCustomerNo) INNER JOIN dwdProduct ON dwfSales.refProdCode = dwdProduct.strProdCode"
            subFact = "Sum(dwfSales.dbl9lTotal)"
            subWhere = "dwfSales.refPeriod"

            ORDERBY = "Region"
      End Select

      'for 1 to 12 
      'where = _
      'getPeriodFactByRegion()
      '20110318 - v1.0.1 - added frRegion filter - pj
      If Me.Region <> "- All -" Then regionWhere = " AND dwdCustomer.strRegionDesc2 = '" & Me.Region & "' "

      For intPeriod As Integer = 1 To Me.intFullYearPeriods
         'INI
         WHERE = " AND (" & subWhere & "='" & quickDate.getPeriod() & "' OR " & subWhere & " IS NULL) AND (refBrandID=" & Me.BrandID & " OR refBrandID IS NULL) "

         ds.Tables(0).Merge( _
               Me.getPeriodFactByRegion("ROUND(" & subFact & ",4) AS '" & quickDate.DuoDate.ToString("MMM") & "'", FROM, WHERE, ORDERBY, True, regionWHERE:=regionWhere, regionHeader:=regionHeader))

         If intPeriod Mod 3 = 1 And blnQuaters Then strQuarterStart = quickDate.getPeriod()
         If intPeriod Mod 3 = 0 And blnQuaters Then
            strQuarterEnd = quickDate.getPeriod()
            'if quarter add quarter column
            WHERE = " AND " & subWhere & ">='" & strQuarterStart & "' AND " & subWhere & "<='" & strQuarterEnd & "' AND refBrandID=" & Me.BrandID

            ds.Tables(0).Merge( _
               Me.getPeriodFactByRegion("ROUND(" & subFact & ",4) AS 'Q" & CStr(intPeriod / 3) & "'", FROM, WHERE, ORDERBY, blnUnion, regionWHERE:=regionWhere, regionHeader:=regionHeader))

         End If

         strPeriodEnd = quickDate.getPeriod()

         'POST
         quickDate.Add(1) 'inc date object
      Next

      'get totals & avg
      If blnTotals Then
         WHERE = " AND " & subWhere & ">='" & strPeriodStart & "' AND " & subWhere & "<='" & strPeriodEnd & "' AND refBrandID=" & Me.BrandID
         ds.Tables(0).Merge( _
               Me.getPeriodFactByRegion("ROUND(" & subFact & ",4) AS 'Total', ROUND(" & subFact & "/" & Me.intFullYearPeriods & ",4) AS 'Average'", FROM, WHERE, ORDERBY, blnUnion:=True, regionWHERE:=regionWhere, regionHeader:=regionHeader))
      End If

      Return ds

   End Function

   '20110328 - v1.0.1 - 
   Private Function getFullYearMixedFact(Optional ByVal blnQuaters As Boolean = True, Optional ByVal blnTotals As Boolean = True, Optional ByVal regionHeader As String = "") As System.Data.DataSet
      'returns merged data set for X months for fact=fact
      Dim ds, dsMerged As New System.Data.DataSet
      Dim FROM As String = ""
      Dim WHERE As String = ""
      Dim ORDERBY As String = ""
      Dim subWhere As String = ""
      Dim regionWhere As String = ""
      Dim subFact As String = ""
      Dim strPeriodStart As String = ""
      Dim strPeriodEnd As String = ""
      Dim strQuarterStart As String = ""
      Dim strQuarterEnd As String = ""
      Dim blnUnion As Boolean = True
      Dim Fact As String

      'ini
      Dim quickDate As New Duodecimal(Me.DateFinYearStart)
      strPeriodStart = quickDate.getPeriod()

      ds.Tables.Add()

      If Me.Region <> "- All -" Then regionWhere = " AND dwdCustomer.strRegionDesc2 = '" & Me.Region & "' "

      For intPeriod As Integer = 1 To Me.intFullYearPeriods
         If quickDate.DuoDate.ToString("yyyyMM") <= Date.Today.ToString("yyyyMM") Then
            Fact = "ACTUAL"
         Else
            Fact = "QF ADJUSTED"
         End If

         'switch Fact { case "": FROM = "" }
         Select Case Fact
            'Case "QF PLANNED"
            '  'note: LEFT JOIN
            '  FROM = "FROM dwdCustomer LEFT JOIN dwfQFPlan ON dwdCustomer.strCustomerNo = dwfQFPlan.strCustomerNo"
            '  subFact = "Sum(dwfQFPlan.dblQF)"
            '  subWhere = "dwfQFPlan.strPeriod"

            '  ORDERBY = "Region"

            Case "QF ADJUSTED"
               'note: LEFT JOIN
               FROM = "FROM dwdCustomer LEFT JOIN dwfQFAdjustment ON dwdCustomer.strCustomerNo = dwfQFAdjustment.strCustomerNo"
               subFact = "Sum(dwfQFAdjustment.dblQF)"
               subWhere = "dwfQFAdjustment.strPeriod"

               ORDERBY = "Region"

            Case "ACTUAL"
               FROM = "FROM (dwdCustomer LEFT JOIN dwfSales ON dwdCustomer.strCustomerNo = dwfSales.refCustomerNo) INNER JOIN dwdProduct ON dwfSales.refProdCode = dwdProduct.strProdCode"
               subFact = "Sum(dwfSales.dbl9lTotal)"
               subWhere = "dwfSales.refPeriod"

               ORDERBY = "Region"
         End Select

         'for 1 to 12 
         'where = _
         'getPeriodFactByRegion()

         'INI
         WHERE = " AND (" & subWhere & "='" & quickDate.getPeriod() & "' OR " & subWhere & " IS NULL) AND (refBrandID=" & Me.BrandID & " OR refBrandID IS NULL) "

         ds.Tables(0).Merge( _
               Me.getPeriodFactByRegion("ROUND(" & subFact & ",4) AS '" & quickDate.DuoDate.ToString("MMM") & "'", FROM, WHERE, ORDERBY, True, regionWHERE:=regionWhere, regionHeader:=regionHeader))

         If intPeriod Mod 3 = 1 And blnQuaters Then strQuarterStart = quickDate.getPeriod()
         If intPeriod Mod 3 = 0 And blnQuaters Then
            strQuarterEnd = quickDate.getPeriod()
            'if quarter add quarter column
            WHERE = " AND " & subWhere & ">='" & strQuarterStart & "' AND " & subWhere & "<='" & strQuarterEnd & "' AND refBrandID=" & Me.BrandID

            ds.Tables(0).Merge( _
               Me.getPeriodFactByRegion("ROUND(" & subFact & ",4) AS 'Q" & CStr(intPeriod / 3) & "'", FROM, WHERE, ORDERBY, blnUnion, regionWHERE:=regionWhere, regionHeader:=regionHeader))

         End If

         strPeriodEnd = quickDate.getPeriod()

         'POST
         quickDate.Add(1) 'inc date object
      Next

      'get totals & avg
      If blnTotals Then
         WHERE = " AND " & subWhere & ">='" & strPeriodStart & "' AND " & subWhere & "<='" & strPeriodEnd & "' AND refBrandID=" & Me.BrandID
         ds.Tables(0).Merge( _
               Me.getPeriodFactByRegion("ROUND(" & subFact & ",4) AS 'Total', ROUND(" & subFact & "/" & Me.intFullYearPeriods & ",4) AS 'Average'", FROM, WHERE, ORDERBY, blnUnion:=True, regionWHERE:=regionWhere, regionHeader:=regionHeader))
      End If

      Return ds

   End Function

   '20110318 - v1.0.1 - added optional regionWhere - pj
   Private Function getPeriodFactByRegion(ByVal Fact As String, ByVal FROM As String, Optional ByVal WHERE As String = "", Optional ByVal ORDERBY As String = "", Optional ByVal blnUnion As Boolean = True, Optional ByVal GROUPBY As String = "", Optional ByVal regionWHERE As String = "", Optional ByVal regionHeader As String = "") As System.Data.DataTable
      Dim ds As System.Data.DataSet
      Dim Union As String = ""

      If ORDERBY <> "" Then ORDERBY = ", " & ORDERBY
      If blnUnion Then
         Union = "UNION ALL SELECT '0' AS '#', '" & regionHeader & "Global' as 'Region', " & Fact & " " & FROM & " WHERE 1=1 " & WHERE
      End If

      'ds = db.doQuery("SELECT strRegionOrder AS '#', dwdCustomer.strRegionDesc2 AS Region, " & Fact & " " & FROM & " WHERE 1=1 " & WHERE & " GROUP BY dwdCustomer.strRegionDesc2, dwdCustomer.strRegionOrder " & GROUPBY & Union & " ORDER BY '#' ASC" & ORDERBY)
      'changed to MAX(#) live data is a bit weird - 20101001
      ds = db.doQuery("SELECT MAX(strRegionOrder) AS '#', '" & regionHeader & "' + dwdCustomer.strRegionDesc2 AS Region, " & Fact & " " & FROM & " WHERE 1=1 " & WHERE & regionWHERE & " GROUP BY dwdCustomer.strRegionDesc2 " & GROUPBY & Union & " ORDER BY '#' ASC" & ORDERBY)

      Return ds.Tables(0).Copy()

      'SELECT MAX(dwdCustomer.strRegionDesc2), Sum(dwfSales.dbl9lTotal) AS 200907
      'FROM (dwdCustomer INNER JOIN dwfSales ON dwdCustomer.strCustomerNo = dwfSales.refCustomerNo) INNER JOIN dwdProduct ON dwfSales.refProdCode = dwdProduct.strProdCode
      'WHERE (((dwfSales.refPeriod)="200907") AND ((dwdProduct.refBrandID)=6))
      'GROUP BY dwdCustomer.strRegionDesc2, dwfSales.refPeriod
      'ORDER BY dwdCustomer.strRegionDesc2, dwfSales.refPeriod
   End Function

   Private Function getPeriodVarianceByRegion(ByVal subFact As String, ByVal subFact2 As String, ByVal ColumnHeading As String, ByVal FROM As String, ByVal FROM2 As String, ByVal WHERE As String, ByVal WHERE2 As String, Optional ByVal ORDERBY As String = "", Optional ByVal blnUnion As Boolean = True) As System.Data.DataTable
      Dim ds As System.Data.DataSet
      Dim Union As String = ""
      Dim Union2 As String = ""

      If ORDERBY <> "" Then ORDERBY = ", " & ORDERBY
      If blnUnion Then
         Union = "UNION ALL SELECT '0' AS strRegionOrder, 'Global' as strRegionDesc2, " & subFact & " AS dblDivisor, 0 AS dblDividend " & FROM & " WHERE 1=1 " & WHERE
         Union2 = "UNION ALL SELECT '0' AS strRegionOrder, 'Global' as strRegionDesc2, 0 AS dblDivisor, " & subFact2 & " AS dblDividend " & FROM2 & " WHERE 1=1 " & WHERE2
      End If

      ds = db.doQuery("SELECT strRegionOrder AS '#', strRegionDesc2 AS Region, CASE WHEN SUM(dblDivisor) <> 0 THEN Round(1-Sum(dblDividend)/Sum(dblDivisor),4) ELSE '0.0' END AS '" & ColumnHeading & _
                        "' FROM (" & _
                        "SELECT strRegionOrder, strRegionDesc2, " & subFact & " AS dblDivisor, 0 AS dblDividend " & FROM & " WHERE 1=1 " & WHERE & " GROUP BY strRegionOrder, strRegionDesc2 " & Union & _
                        " UNION ALL " & _
                        " SELECT strRegionOrder, strRegionDesc2, 0 AS dblDivisor, " & subFact2 & " AS dblDividend " & FROM2 & " WHERE 1=1 " & WHERE2 & " GROUP BY strRegionOrder, strRegionDesc2 " & Union2 & _
                        " ) as tblDir GROUP BY strRegionOrder, strRegionDesc2 ORDER BY strRegionOrder" & ORDERBY)

      Return ds.Tables(0).Copy()

      'SELECT strRegionOrder AS '#', Region, Sum(dblQF) AS SumOfdblQF, Sum(dbl9lTotal) AS SumOfdbl9lTotal, Round(Sum(dblQF)/Sum(dbl9lTotal)-1,4) AS 'Jul'
      'FROM (

      'SELECT dwdCustomer.strRegionOrder, dwdCustomer.strRegionDesc2 as Region, Sum(dwfSales.dbl9lTotal) AS dbl9lTotal, 0 AS dblQF
      'FROM dwdProduct INNER JOIN (dwfSales INNER JOIN dwdCustomer ON dwfSales.refCustomerNo = dwdCustomer.strCustomerNo) ON dwdProduct.strProdCode = dwfSales.refProdCode
      'GROUP BY dwdCustomer.strRegionDesc2, dwdProduct.refBrandID, dwfSales.refPeriod, dwdCustomer.strRegionOrder
      'HAVING (((dwdProduct.refBrandID)=6) AND ((dwfSales.refPeriod)='200907'))

      'UNION ALL

      'SELECT dwdCustomer.strRegionOrder, dwdCustomer.strRegionDesc2 as Region, 0 AS dbl9lTotal, Sum(dwfQFPlan.dblQF) AS dblQF
      'FROM dwdCustomer INNER JOIN dwfQFPlan ON dwdCustomer.strCustomerNo = dwfQFPlan.strCustomerNo
      'GROUP BY dwdCustomer.strRegionDesc2, dwfQFPlan.strPeriod, dwfQFPlan.refBrandID, dwdCustomer.strRegionOrder
      'HAVING (((dwfQFPlan.strPeriod)='200907') AND ((dwfQFPlan.refBrandID)=6))

      'ORDER BY strRegionOrder

      ')  AS tblDir2
      'GROUP BY strRegionOrder, Region
   End Function

   Private Sub LoadPeriods()
      Me.arrPeriods = New Collection
      Dim duo As New Duodecimal(Me.DateFinYearStart)

      For i = 1 To Me.intFullYearPeriods
         Try
            Me.arrPeriods.Add(duo.getPeriod(), duo.DuoDate.ToString("MMM"))
            duo.Add(1)
         Catch ex As ArgumentException 'expected if more than 12 periods. ignore
         End Try

      Next
   End Sub

   Public Function getPeriod(ByVal key As String) As String
      Try
         Return Me.arrPeriods.Item(key)
      Catch argEx As ArgumentException
         Return "" 'if item not found
      End Try
   End Function

   'SHARED UTIL FUNCTIONS

   Public Shared Function colourDataRow(ByRef gv As GridView, ByVal dt As System.Data.DataTable, ByVal Brand As Branding, Optional ByVal blnDecimal As Boolean = False, Optional ByVal blnPerc As Boolean = False) As String
      'gv passed by ref to colour in a gridview
      'note the space after .CssClass &= "cssBleh ", because you are appending stuff

      'columns
      For idxCol As Integer = 0 To dt.Columns.Count - 1
         'col colours

         If gv.HeaderRow.Cells(idxCol).Width.IsEmpty Then gv.HeaderRow.Cells(idxCol).Width = My.Settings.intColumnWidthDefault

         Select Case dt.Columns(idxCol).ColumnName
            Case "#"
               gv.HeaderRow.Cells(idxCol).Width = "10"
            Case "Region"
               gv.HeaderRow.Cells(idxCol).Width = My.Settings.intColumnWidthRegion
            Case Is = Date.Today.ToString("MMM")
               gv.HeaderRow.Cells(idxCol).CssClass &= " columnYellow"
            Case "Total"
               gv.HeaderRow.Cells(idxCol).CssClass &= " columnYellow"
               gv.HeaderRow.Cells(idxCol).Width = My.Settings.intColumnWidthDefault + 10
            Case "YTD LY", "YTD TY"
               gv.HeaderRow.Cells(idxCol).CssClass &= " columnYellow"
            Case Else
               'gv.HeaderRow.Cells(idxCol).Width = My.Settings.intColumnWidthDefault
         End Select

         If dt.Columns(idxCol).ColumnName.Contains("Q") And dt.Columns(idxCol).ColumnName.Length = 2 Then gv.HeaderRow.Cells(idxCol).CssClass &= " columnOrange"

         'STATS
         If dt.Columns(idxCol).ColumnName.Contains("FY 20") Then gv.HeaderRow.Cells(idxCol).CssClass &= " columnYellow"
         If dt.Columns(idxCol).ColumnName.Contains("Last 12") Then gv.HeaderRow.Cells(idxCol).CssClass &= " columnYellow"
         If dt.Columns(idxCol).ColumnName.Contains("QF Plan") Then gv.HeaderRow.Cells(idxCol).CssClass &= " columnOrange"
         If dt.Columns(idxCol).ColumnName.Contains("QF Adj") Then gv.HeaderRow.Cells(idxCol).CssClass &= " columnOrange"

         'YTD
         If dt.Columns(idxCol).ColumnName.Contains("YTD LY") Then gv.HeaderRow.Cells(idxCol).CssClass &= " columnYellow"
         If dt.Columns(idxCol).ColumnName.Contains("YTD TY") Then gv.HeaderRow.Cells(idxCol).CssClass &= " columnYellow"
         If dt.Columns(idxCol).ColumnName.Contains("YTD QF") Then gv.HeaderRow.Cells(idxCol).CssClass &= " columnOrange"
         'STATS (Planning)
         If dt.Columns(idxCol).ColumnName.Contains("%C ") Then gv.HeaderRow.Cells(idxCol).CssClass &= " columnNotYellow"
         If dt.Columns(idxCol).ColumnName = "AVG %Contribution" Then gv.HeaderRow.Cells(idxCol).CssClass &= " columnNotOrange"
         If dt.Columns(idxCol).ColumnName = "Sales YTD" Then gv.HeaderRow.Cells(idxCol).CssClass &= " columnYellow"
         If dt.Columns(idxCol).ColumnName = "Projection 1" Then gv.HeaderRow.Cells(idxCol).CssClass &= " columnYellow"
         If dt.Columns(idxCol).ColumnName = "Projection 2" Then gv.HeaderRow.Cells(idxCol).CssClass &= " columnNotYellow"


      Next

      'columns & rows
      For idxCol As Integer = 0 To dt.Columns.Count - 1
         Dim idxRow As Integer = 0
         For Each row As GridViewRow In gv.Rows
            Try

               If dt.Columns(idxCol).ColumnName.Contains("#") Then
                  row.Cells(idxCol).CssClass &= " textNumeric"
               End If

               If dt.Columns(idxCol).ColumnName = "Region" Then
                  row.Cells(idxCol).CssClass &= " textNowrap"
               End If

               'col colours

               If dt.Columns(idxCol).ColumnName = Date.Today.ToString("MMM") Then
                  row.Cells(idxCol).CssClass &= " columnYellow"
               End If

               If dt.Columns(idxCol).ColumnName = "Total" Then
                  row.Cells(idxCol).CssClass &= " columnYellow"
               End If

               If dt.Columns(idxCol).ColumnName.Contains("Q") And dt.Columns(idxCol).ColumnName.Length = 2 Then
                  row.Cells(idxCol).CssClass &= " columnOrange"
               End If

               'STATS
               If dt.Columns(idxCol).ColumnName.Contains("FY 20") Then
                  row.Cells(idxCol).CssClass &= " columnYellow"
               End If

               If dt.Columns(idxCol).ColumnName.Contains("Last 12") Then
                  row.Cells(idxCol).CssClass &= " columnYellow"
               End If

               If dt.Columns(idxCol).ColumnName.Contains("QF Plan") Then
                  row.Cells(idxCol).CssClass &= " columnOrange"
               End If

               If dt.Columns(idxCol).ColumnName.Contains("QF Adj") Then
                  row.Cells(idxCol).CssClass &= " columnOrange"
               End If

               'STATS (Planning)
               If dt.Columns(idxCol).ColumnName.Contains("%C ") Then
                  row.Cells(idxCol).CssClass &= " columnNotYellow"
               End If
               If dt.Columns(idxCol).ColumnName = "AVG %Contribution" Then
                  row.Cells(idxCol).CssClass &= " columnNotOrange"
               End If
               If dt.Columns(idxCol).ColumnName = "Sales YTD" Then
                  row.Cells(idxCol).CssClass &= " columnYellow"
               End If
               If dt.Columns(idxCol).ColumnName = "Projection 1" Then
                  row.Cells(idxCol).CssClass &= " columnYellow"
               End If
               If dt.Columns(idxCol).ColumnName = "Projection 2" Then
                  row.Cells(idxCol).CssClass &= " columnNotYellow"
               End If

               'YTD
               If dt.Columns(idxCol).ColumnName.Contains("YTD LY") Then
                  row.Cells(idxCol).CssClass &= " columnYellow"
               End If

               If dt.Columns(idxCol).ColumnName.Contains("YTD TY") Then
                  row.Cells(idxCol).CssClass &= " columnYellow"
               End If

               If dt.Columns(idxCol).ColumnName.Contains("YTD QF") Then
                  row.Cells(idxCol).CssClass &= " columnOrange"
               End If

               'text alignments
               If gv.ID = "gvPlanning" Then
                  If idxCol = dt.Columns.Count - 2 Then
                     Try ' try for controls in gvPlanning
                        Dim txtQF As TextBox
                        txtQF = DirectCast(row.Cells(idxCol).Controls(1), TextBox)
                        txtQF.CssClass &= " textNumeric controlEdit"
                        txtQF.ReadOnly = False
                        txtQF.Attributes.Add("onBlur", "jsQFBlur(this)")
                        txtQF.Attributes.Add("onFocus", "jsQFFocus(this)")

                        row.Cells(idxCol).CssClass = " textNumeric"
                        If row.Cells(dt.Columns.IndexOf("Region")).Text = "Global" Or row.Cells(dt.Columns.IndexOf("Region")).Text = "%" Then
                           row.CssClass &= " rowTotal"
                           txtQF.CssClass = "rowTotal controlTextboxBig controlLabel" 'reset this txt
                           txtQF.ReadOnly = True
                        End If
                     Catch argEx As ArgumentException
                     Catch ex As Exception

                     End Try
                  Else
                     Try
                        If Math.Abs(CDbl(row.Cells(idxCol).Text)) >= 0 And Not dt.Columns(idxCol).ColumnName.Contains("#") Then 'moerse hack, i know
                           row.Cells(idxCol).CssClass &= " textNumeric"

                           'number & % formats
                           If dt.Columns(idxCol).ColumnName.Contains("%C") Then
                              row.Cells(idxCol).Text = Microsoft.VisualBasic.FormatPercent(row.Cells(idxCol).Text, My.Settings.intDecimals + 1)
                           Else
                              If dt.Columns(idxCol).ColumnName.Contains("% ") Or blnPerc Or dt.Columns(idxCol).ColumnName.Contains("vs") And Not dt.Columns(idxCol).ColumnName = "AVG %Contribution" Then
                                 row.Cells(idxCol).Text = FormatPercent(row.Cells(idxCol).Text, 2)
                              Else
                                 row.Cells(idxCol).Text = FormatNumber(row.Cells(idxCol).Text, 4)
                              End If
                           End If
                           'eoIF gvAdjusted
                        End If 'eoIF abs(CDbl())

                     Catch icEx As InvalidCastException
                        'expected cdbl will not work on strings
                     End Try
                  End If

               Else
                  'all other gv

                  Try
                     If Math.Abs(CDbl(row.Cells(idxCol).Text)) >= 0 And Not dt.Columns(idxCol).ColumnName.Contains("#") Then 'moerse hack, i know
                        row.Cells(idxCol).CssClass &= " textNumeric"

                        'number & % formats
                        If dt.Columns(idxCol).ColumnName.Contains("%C") Then
                           row.Cells(idxCol).Text = Microsoft.VisualBasic.FormatPercent(row.Cells(idxCol).Text, My.Settings.intDecimals + 1)
                        Else
                           If dt.Columns(idxCol).ColumnName.Contains("% ") Or blnPerc Or dt.Columns(idxCol).ColumnName.Contains("vs") And Not dt.Columns(idxCol).ColumnName = "AVG %Contribution" Then
                              If blnDecimal Then
                                 row.Cells(idxCol).Text = FormatPercent(row.Cells(idxCol).Text, My.Settings.intDecimals + 1)
                              Else
                                 row.Cells(idxCol).Text = FormatPercent(row.Cells(idxCol).Text, 0)
                              End If
                           Else
                              If blnDecimal Then
                                 row.Cells(idxCol).Text = FormatNumber(row.Cells(idxCol).Text, My.Settings.intDecimals)
                              Else
                                 row.Cells(idxCol).Text = FormatNumber(row.Cells(idxCol).Text, 0)
                              End If
                           End If
                        End If
                        'eoIF gvAdjusted
                     End If 'eoIF abs(CDbl())
                     '20110322 - v1.0.1 - added value conditional formatting (colouring) to YTD v.s QF column - pj
                     If dt.Columns(idxCol).ColumnName.Contains("YTD v.s. QF") Then
                        If CDbl(row.Cells(idxCol).Text) < 0 Then
                           row.Cells(idxCol).CssClass &= " textError"
                        Else
                           row.Cells(idxCol).CssClass &= " textBlack"
                        End If
                     End If
                  Catch icEx As InvalidCastException
                     'expected cdbl will not work on strings
                  End Try

               End If

               If gv.ID = "gvAdjusted" Then
                  Try ' try for controls in gvAdjusted
                     Dim txtQF As TextBox
                     txtQF = DirectCast(row.Cells(idxCol).Controls(1), TextBox)
                     txtQF.CssClass &= " textNumeric"

                     'number & % formats
                     If dt.Columns(idxCol).ColumnName.Contains("% ") Or blnPerc Or dt.Columns(idxCol).ColumnName.Contains("vs") Then
                        If blnDecimal Then 'note: Public Overloads Shared Function FormatPercent() is in ProjectFunctions.vb
                           txtQF.Text = FormatPercent(txtQF.Text, My.Settings.intDecimals + 1)
                        Else
                           txtQF.Text = FormatPercent(txtQF.Text, 0)
                        End If
                     Else
                        If blnDecimal Then
                           txtQF.Text = FormatNumber(txtQF.Text, My.Settings.intDecimals)
                        Else
                           txtQF.Text = FormatNumber(txtQF.Text, 0)
                        End If
                     End If

                     'locking:
                     txtQF.Attributes.Add("ondblclick", "jsViewComments(this,'" & Brand.getPeriod(dt.Columns(idxCol).ColumnName) & "'," & Brand.BrandID & ",'" & Branding.getCustomerNo(row.Cells(dt.Columns.IndexOf("Region")).Text) & "')")

                     txtQF.ToolTip = Brand.getDtCellLog(row.Cells(dt.Columns.IndexOf("Region")).Text, dt.Columns(idxCol).ColumnName)

                     If Brand.getDtCellApplied(row.Cells(dt.Columns.IndexOf("Region")).Text, dt.Columns(idxCol).ColumnName) = TriState.False And (txtQF.ToolTip <> "" Or txtQF.Text <> 0) Then
                        txtQF.CssClass &= " controlUnApplied"
                        txtQF.ReadOnly = False
                        txtQF.Attributes.Add("onBlur", "jsQFBlur(this)")
                        txtQF.Attributes.Add("onFocus", "jsQFFocus(this)")

                     Else

                        '20150529 - v1.0.3 - unlocked all QFs [AND FALSE]
#If DEBUG Then
                        'If Brand.getPeriod(dt.Columns(idxCol).ColumnName) < "201109" Then 'Date.Today.ToString("yyyyMM") Then
                        If Brand.getPeriod(dt.Columns(idxCol).ColumnName) < Date.Today.ToString("yyyyMM") And False Then
#Else
                        'If Brand.getPeriod(dt.Columns(idxCol).ColumnName) < Date.Today.ToString("yyyyMM") Then
                        If Brand.getPeriod(dt.Columns(idxCol).ColumnName) < Date.Today.ToString("yyyyMM") And False Then
                           '20150216 - v1.0.3 - remove FALSE later - temp workaround [RE: RGBC: Forecast Change] - pj
                           '20150302 - v1.0.3 - removed - pj
#End If
                           txtQF.CssClass &= " controlLocked"
                           txtQF.ReadOnly = True
                           'Brand.dtLog.Rows(idxRow).Item(idxCol).ToString() ' do not ref directly:  dtLog.Rows(idxRow = 6) does not exist means 'row transforms do not happen
                        Else
                           txtQF.CssClass &= " controlEdit"
                           txtQF.ReadOnly = False
                           txtQF.Attributes.Add("onBlur", "jsQFBlur(this)")
                           txtQF.Attributes.Add("onFocus", "jsQFFocus(this)")
                        End If

                     End If 'eoIf UnApllied


                     'row transforms
                     If row.Cells(dt.Columns.IndexOf("Region")).Text = "Global" Then
                        row.CssClass &= " rowTotal"
                        txtQF.CssClass &= " rowTotal" 'reset this txt XcontrolLabel
                        'txtQF.ReadOnly = True
                     End If
                  Catch argEx As ArgumentException

                  Catch ex As Exception

                  End Try
               End If ' eoIf gvAdjustment

               'row transforms
               If row.Cells(dt.Columns.IndexOf("Region")).Text.Contains("Global") Then
                  row.CssClass &= " rowTotal"
               End If
               If row.Cells(dt.Columns.IndexOf("Region")).Text.Contains("%") Then
                  For idxCell = idxCol + 1 To row.Cells.Count - 1
                     Dim cell As TableCell = row.Cells(idxCell)
                     Try
                        cell.Text = Microsoft.VisualBasic.FormatPercent(cell.Text, 1)
                        cell.CssClass = "textNumeric "
                     Catch ex As Exception
                     End Try
                  Next
               End If

            Catch ex As Exception
               Return ex.Message & "<BR/>" & ex.StackTrace
            End Try
            idxRow += 1
         Next
      Next

      Return ""
   End Function

   Public Shared Function LoadCustomers() As Collection
      Dim ds As System.Data.DataSet

      ds = db.doQuery("SELECT strCustomerNo, strRegionDesc2 as Region FROM dwdCustomer GROUP BY strCustomerNo, strRegionDesc2, strRegionOrder HAVING strCustomerNo LIKE ('HIST%') ORDER BY strRegionOrder, strCustomerNo ASC")
      Branding.arrCustomers = New Collection
      For Each row In ds.Tables(0).Rows
         Try
            Branding.arrCustomers.Add(row, row!Region)

         Catch argEx As ArgumentException
            'expected error: HIST customers might be 2 in the same region = work only with the primary eg. HISTJ1 and ignore HISTJ2
         End Try
      Next

      Return Branding.arrCustomers
   End Function

   Public Shared Function getCustomerNo(ByVal key As String) As String

      If Branding.arrCustomers.Count = 0 Then
         Branding.LoadCustomers()
      End If

      Try
         Return Branding.arrCustomers.Item(key)!strCustomerNo
      Catch argEx As ArgumentException
         Return "" 'if item not found
      End Try

   End Function

End Class