'20101013 - v0.0.2 - Start of Planning Interface
'RULES:
'r1 - can only plan for future fiscals. if wanting to make changes to current fiscal go to the Adjustments
'r2 - saving the plan will override/recalc any adjustments

Imports RGBC_Forecast.SystemFunctions
Imports RGBC_Forecast.ProjectFunctions
Imports RGBC_Forecast.QFGen

Partial Public Class Planning
   Inherits System.Web.UI.Page

   Public dr As DataRow
   Public ds As DataSet

   Private Shared Brand As Branding

   Public Shared arrSales As New Collection
   Public Shared arrSalesYTD As New Collection
   Public Shared dblProjected As Double
   Public Shared percCompleted As Double = 1
   Public Shared dblQFCurrent As Double
   Public Shared dblQFTarget As Double

   Private Shared blnDebug = False


   Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

      If Not Page.IsPostBack Then

         Try
            txtQFTarget.Attributes.Add("onBlur", "jsQFTargetBlur(this);")
            txtQFTarget.Attributes.Add("onFocus", "jsQFFocus(this);")
            If CBool(Session("USER")!blnSpecial) <> True Then Response.Redirect("Home.aspx?Message=Insufficient rights to access this function.")
         Catch ex As Exception
         End Try

      End If
   End Sub

   Private Function getTotalSales(ByVal tempBrand As Branding) As Collection
      Dim arrSales As New Collection
      'note inner join to dwdCustomer not really required. local/dev db missing some customer, so joined to keep figures in line
      ds = db.doQuery("SELECT mstPeriod.intFiscalYear, ROUND(Sum(dwfSales.dbl9lTotal),4) AS dbl9lTotal " & _
      "FROM (mstPeriod INNER JOIN (dwfSales INNER JOIN dwdProduct ON dwfSales.refProdCode = dwdProduct.strProdCode) ON mstPeriod.strPeriod = dwfSales.refPeriod) INNER JOIN dwdCustomer ON dwfSales.refCustomerNo = dwdCustomer.strCustomerNo " & _
      "WHERE dwdProduct.refBrandID=" & tempBrand.BrandID & " AND dwfSales.refPeriod >='" & tempBrand.DateFinYearStartMin3.ToString("yyyyMM") & "' " & _
      "GROUP BY mstPeriod.intFiscalYear " & _
      "ORDER BY mstPeriod.intFiscalYear DESC")
      If Not ds Is Nothing Then
         For Each dr In ds.Tables(0).Rows
            arrSales.Add(dr, CStr(dr!intFiscalYear))
         Next
      End If

      Return arrSales

   End Function

   Private Function getYTDSales(ByVal tempBrand As Branding) As Collection
      Dim arrSalesYTD As New Collection
      Dim dtLastCompletedPeriod As Date
      Dim sql As String

      dtLastCompletedPeriod = Duodecimal.Add(Date.Today, -1)
      sql = "SELECT mstPeriod.intFiscalYear, ROUND(Sum(dwfSales.dbl9lTotal),4) AS dbl9lTotal " & _
       "FROM (mstPeriod INNER JOIN (dwfSales INNER JOIN dwdProduct ON dwfSales.refProdCode = dwdProduct.strProdCode) ON mstPeriod.strPeriod = dwfSales.refPeriod) INNER JOIN dwdCustomer ON dwfSales.refCustomerNo = dwdCustomer.strCustomerNo " & _
       "WHERE refBrandID = " & tempBrand.BrandID & " AND (" & _
        "(refPeriod >= '" & tempBrand.DateFinYearStart.ToString("yyyyMM") & "' AND refPeriod <= '" & dtLastCompletedPeriod.ToString("yyyyMM") & "') OR " & _
        "(refPeriod >= '" & tempBrand.DateFinYearStartMin1.ToString("yyyyMM") & "' AND refPeriod <= '" & Duodecimal.Add(dtLastCompletedPeriod, -12).ToString("yyyyMM") & "') OR " & _
        "(refPeriod >= '" & tempBrand.DateFinYearStartMin2.ToString("yyyyMM") & "' AND refPeriod <= '" & Duodecimal.Add(dtLastCompletedPeriod, -24).ToString("yyyyMM") & "') OR " & _
        "(refPeriod >= '" & tempBrand.DateFinYearStartMin3.ToString("yyyyMM") & "' AND refPeriod <= '" & Duodecimal.Add(dtLastCompletedPeriod, -36).ToString("yyyyMM") & "') " & _
       ") GROUP BY mstPeriod.intFiscalYear " & _
       "ORDER BY mstPeriod.intFiscalYear DESC"
      'SELECT mstPeriod.intFiscalYear, dwdProduct.blnQF, Sum(dwfSales.dbl9lTotal) AS dbl9lTotal
      'FROM mstPeriod INNER JOIN (dwfSales INNER JOIN dwdProduct ON dwfSales.refProdCode = dwdProduct.strProdCode) ON mstPeriod.strPeriod = dwfSales.refPeriod
      'WHERE (((dwdProduct.refBrandID)=6) AND ((dwfSales.refPeriod)>='200807' And (dwfSales.refPeriod)<='200809')) OR (((dwdProduct.refBrandID)=6) AND ((dwfSales.refPeriod)>='200907' And (dwfSales.refPeriod)<='200909'))
      'GROUP BY mstPeriod.intFiscalYear, dwdProduct.blnQF
      'ORDER BY mstPeriod.intFiscalYear DESC , dwdProduct.blnQF;
      ds = db.doQuery(sql)
      If Not ds Is Nothing Then
         For Each dr In ds.Tables(0).Rows
            arrSalesYTD.Add(dr, CStr(dr!intFiscalYear))
         Next
      End If

      Return arrSalesYTD

   End Function

   Private Sub LoadSalesRhythm(ByVal BrandID As Integer)
      Dim drFiscal As DataRow
      Dim tempBrand As Branding
      Dim dtRhythm As DataTable

      arrSales = New Collection
      arrSalesYTD = New Collection


      drFiscal = db.getRow("SELECT * FROM mstPeriod WHERE strPeriod = '" & Date.Today.ToString("yyyyMM") & "'")
      'load settings pannel
      tempBrand = New Branding(drFiscal!intFiscalYear, BrandID)

      gvSalesR1.Caption = "SALES RHYTHM FY " & tempBrand.DateFinYearEndMin1.ToString("yyyy")
      gvSalesR2.Caption = "SALES RHYTHM FY " & tempBrand.DateFinYearEndMin2.ToString("yyyy")

      dtRhythm = tempBrand.getSalesRhythm1(BrandID, tempBrand.DateFinYearStartMin3.ToString("yyyyMM"))
      gvSalesR1.DataSource = dtRhythm.Copy()
      Brand.SalesRhythmDT = tempBrand.getSalesRhythm2(dtRhythm.Copy())
      gvSalesR2.DataSource = Brand.SalesRhythmDT

      gvSalesR1.DataBind()
      gvSalesR1.Visible = True
      gvSalesR2.DataBind()
      gvSalesR2.Visible = True

      'get TotalSales
      arrSales = Me.getTotalSales(tempBrand)

      'getYTD Sales
      arrSalesYTD = Me.getYTDSales(tempBrand)

      'get AVG % Completed
      percCompleted = 0
      For i = 2 To arrSales.Count 'i=2 ignore the current year
         Try
            percCompleted += arrSalesYTD(CStr(arrSales(i)!intFiscalYear))!dbl9lTotal / arrSales(i)!dbl9lTotal
         Catch ex As Exception
         End Try
      Next
      If arrSalesYTD.Count - 1 <> 0 Then
         percCompleted = percCompleted / (arrSalesYTD.Count - 1)
      End If

      'gvStats
      If BrandID <> Brand.BrandID Then
         gvSalesR1.Caption = "ACTUAL for " & tempBrand.BrandName
         gvSalesR2.Caption = "SALES RHYTHM for " & tempBrand.BrandName
         gvStats.Caption = "STATS & PROJECTIONS: " & tempBrand.BrandName & " (YL " & tempBrand.DateFinYearEndMin1.ToString("yyyy") & ") & " & Brand.BrandName & " (YTD " & tempBrand.DateFinYearEnd.ToString("yyyy") & ")"
      Else
         gvSalesR1.Caption = "ACTUAL"
         gvSalesR2.Caption = "SALES RHYTHM"
         gvStats.Caption = "STATS & PROJECTIONS (LY " & tempBrand.DateFinYearEndMin1.ToString("yyyy") & " to YTD " & tempBrand.DateFinYearEnd.ToString("yyyy") & ")"
      End If
      Brand.PlanningStatsDT = Brand.getStatsAndProjections(tempBrand, Brand, percCompleted)
      gvStats.DataSource = Brand.PlanningStatsDT
      gvStats.DataBind()
      gvStats.Visible = True


      'if Copy Brand then reload Total & YTD Sales
      If BrandID <> Brand.BrandID Then
         tempBrand = New Branding(drFiscal!intFiscalYear, Brand.BrandID) 'need the F-1 & F-2 from tempBrand where BrandID = Brand.BrandID
         'get TotalSales
         arrSales = Me.getTotalSales(tempBrand)
         'getYTD Sales
         arrSalesYTD = Me.getYTDSales(tempBrand)
      End If


      Try
         dblProjected = Math.Round(arrSalesYTD(CStr(drFiscal!intFiscalYear))!dbl9lTotal / percCompleted, 4)
      Catch ex As Exception
         dblProjected = 0.0
      End Try

      dr = db.getRow("SELECT ROUND(SUM(dblQF),4) AS dblQF FROM dwfQFPlan WHERE refBrandID = " & Brand.BrandID & " AND strPeriod >= '" & tempBrand.DateFinYearStart.ToString("yyyyMM") & "' AND strPeriod <= '" & tempBrand.DateFinYearEnd.ToString("yyyyMM") & "'")
      If Not dr Is Nothing Then
         dblQFCurrent = db.nz(dr!dblQF, 0.0)
      End If
      dr = db.getRow("SELECT ROUND(SUM(dblQF),4) AS dblQF FROM dwfQFPlan WHERE refBrandID = " & Brand.BrandID & " AND strPeriod >= '" & Brand.DateFinYearStart.ToString("yyyyMM") & "' AND strPeriod <= '" & Brand.DateFinYearEnd.ToString("yyyyMM") & "'")
      If Not dr Is Nothing Then
         dblQFTarget = db.nz(dr!dblQF, 0.0)
      End If

      'set controls
      'col1
      lblBGLY.Text = drFiscal!intFiscalYear - 3 & " to " & drFiscal!intFiscalYear - 2
      lblBGTY.Text = drFiscal!intFiscalYear - 2 & " to " & drFiscal!intFiscalYear - 1
      lblBGProjected.Text = drFiscal!intFiscalYear - 1 & " to " & drFiscal!intFiscalYear
      lblBGForecast.Text = drFiscal!intFiscalYear & " to " & drFiscal!intFiscalYear + 1

      Try
         percBGLY.Text = FormatPercent(Math.Round(arrSales(CStr(drFiscal!intFiscalYear - 2))!dbl9lTotal / arrSales(CStr(drFiscal!intFiscalYear - 3))!dbl9lTotal - 1, 4), 2)
      Catch ex As Exception
         percBGLY.Text = "n/a"
      End Try
      Try
         percBGTY.Text = FormatPercent(Math.Round(arrSales(CStr(drFiscal!intFiscalYear - 1))!dbl9lTotal / arrSales(CStr(drFiscal!intFiscalYear - 2))!dbl9lTotal - 1, 4), 2)
      Catch ex As Exception
         percBGTY.Text = "n/a"
      End Try
      Try
         percBGProjected.Text = FormatPercent(Math.Round(dblProjected / arrSales(CStr(drFiscal!intFiscalYear - 1))!dbl9lTotal - 1, 4), 2)
      Catch ex As Exception
         percBGTY.Text = "n/a"
      End Try
      Try
         percBGForecast.Text = FormatPercent(Math.Round(dblQFTarget / dblProjected - 1, 4), 2)
      Catch ex As Exception
         percBGTY.Text = "n/a"
      End Try
      txtQFTarget.Text = FormatNumber(dblQFTarget)

      'col2
      Try
         ldblSalesLY.Text = FormatNumber(arrSales(CStr(drFiscal!intFiscalYear - 1))!dbl9lTotal)
      Catch ex As Exception
         ldblSalesLY.Text = "n/a"
      End Try
      Try
         ldblSalesTY.Text = FormatNumber(arrSalesYTD(CStr(drFiscal!intFiscalYear))!dbl9lTotal)
      Catch ex As Exception
         ldblSalesTY.Text = "n/a"
      End Try

      ldblSalesProjected.Text = FormatNumber(dblProjected)
      lpercCompleted.Text = Microsoft.VisualBasic.FormatPercent(percCompleted)
      ldblQFCurrent.Text = FormatNumber(dblQFCurrent)

   End Sub

   Private Function UpdatePlanningPercRow(ByVal dr As DataRow) As DataRow

      Dim idxTotalColumn, idxTotalRow As Integer

      idxTotalColumn = Brand.PlannedDT.Columns.IndexOf("Total")
      idxTotalRow = 0
      dr.Item("#") = ""
      dr!Region = "% of Total"
      For idxCol = 2 To idxTotalColumn
         dr.Item(idxCol) = Math.Round(Brand.PlannedDT.Rows(idxTotalRow).Item(idxCol) / Brand.PlannedDT.Rows(idxTotalRow).Item(idxTotalColumn), 4)
      Next

      Return dr

   End Function


   Protected Sub btnLoad_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnLoad.Click
      btnSave.Enabled = False
      lblMessage.Text = ""
      lblMessage.CssClass = "boxError"

      If CDbl(ddlBrand.SelectedValue) > 0 And CDbl(ddlFinYear.SelectedValue) > 0 Then
         Dim dt As New DataTable

         If ddlBrand.SelectedValue = ddlBrandCopy.SelectedValue Then
            ddlBrandCopy.ClearSelection()
            ddlBrandCopy.SelectedIndex = 0
         End If

         'load stuff
         Brand = New Branding(ddlFinYear.SelectedValue, ddlBrand.SelectedValue)

         lblBrand.Text = Brand.BrandLabel




         'load Sales Rhythms & stats
         valBrandCopy2.InitialValue = Brand.BrandID

         If ddlBrandCopy.SelectedValue > 0 Then
            LoadSalesRhythm(ddlBrandCopy.SelectedValue)
         Else
            LoadSalesRhythm(Brand.BrandID)
         End If


         'load Planning gv

         Brand.PlannedDT = Brand.GetPlannedFullYear()
         'testing adding rows to dt
         dr = Brand.PlannedDT.NewRow()
         dr = Me.UpdatePlanningPercRow(dr)

         Brand.PlannedDT.Rows.Add(dr)

         gvPlanning.DataSourceID = ""
         gvPlanning.DataSource = Brand.PlannedDT
         gvPlanning.DataBind()
         gvPlanning.Visible = True

      End If


      'r1 - enable btns
      Dim drFiscal As DataRow
      drFiscal = db.getRow("SELECT * FROM mstPeriod WHERE strPeriod = '" & Date.Today.ToString("yyyyMM") & "'")
      If CInt(ddlFinYear.SelectedValue) > CInt(drFiscal!intFiscalYear) Then
         'enable btns 'LAME! admin.Page_Load has not triggered yet so Session doesn't have a value in bedug. wtf microshit?!
         Try
            btnSave.Enabled = CBool(Session.Item("USER")!blnSpecial)
            If lblMessage.Text = "Can only generate Forecast for future Fiscals!" Then lblMessage.Text = ""
         Catch ex As Exception
         End Try
      Else
         lblMessage.Text = "Can only generate Forecast for future Fiscals!"
      End If

   End Sub


   Private Sub Page_PreRender(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.PreRender
      planningAnchor.Focus()

      'ClientScript.RegisterClientScriptBlock(Me.GetType, "", "alert('Arr'); d('ctl00_ContentPlaceHolder1_planningAnchor').focus();",True)
   End Sub

   Private Sub ShowLoading(ByVal blnShow As Boolean)
      Dim imgLoading As WebControls.Image = Page.Master.FindControl("imgLoading")

      If blnShow Then
         imgLoading.CssClass = "visible"
      Else
         imgLoading.CssClass = "invisible"
      End If

   End Sub
   Private Sub Page_PreRenderComplete(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.PreRenderComplete
      ShowLoading(False)

      'ClientScript.RegisterClientScriptBlock(Me.GetType, "", "alert('Arr'); d('ctl00_ContentPlaceHolder1_planningAnchor').focus();",True)
   End Sub

   Protected Sub btnSave_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnSave.Click
      Dim idxTotal, idxRegion, idxRow, idxCol As Integer
      Dim strCustomerNo, strPeriod, strLastUser As String
      Dim dblQF As Double
      Dim dtStamp As Date = Date.Now

      idxRegion = Brand.PlannedDT.Columns.IndexOf("Region")
      idxTotal = Brand.PlannedDT.Columns.IndexOf("Total")

      If Session.Item("User") Is Nothing Then 'lame
         LogginUser(Request, Session)
      End If

      strLastUser = Session.Item("User")!strUserName

      ' DELETE dwfQF & _P & _A
      ' for each Row
      '   for each Column
      '     dblQF = round(cell.text,4)
      '     save_dwfQFP(Period, Brand, Region, dblQF)
      ' Insert into dwfQFA select * from dwfQFP

      db.doQuery("DELETE FROM dwfQFPlan WHERE refBrandID = " & Brand.BrandID & " AND strPeriod >= '" & Brand.DateFinYearStart.ToString("yyyyMM") & "' AND strPeriod <= '" & Brand.DateFinYearEnd.ToString("yyyyMM") & "' ")
      db.doQuery("DELETE FROM dwfQFAdjustment WHERE refBrandID = " & Brand.BrandID & " AND strPeriod >= '" & Brand.DateFinYearStart.ToString("yyyyMM") & "' AND strPeriod <= '" & Brand.DateFinYearEnd.ToString("yyyyMM") & "' ")
      db.doQueryAsync("DELETE FROM " & My.Settings.dwfQFTable & " WHERE strProdCode IN (" & Brand.strProdCodesF & ") AND strPeriod >= '" & Brand.DateFinYearStart.ToString("yyyyMM") & "' AND strPeriod <= '" & Brand.DateFinYearEnd.ToString("yyyyMM") & "'")

      If blnDebug Then lblMessage.Text = ""

      For Each row As GridViewRow In gvPlanning.Rows

         If blnDebug Then lblMessage.Text &= "<BR/><BR/>" & row.Cells(idxRegion).Text

         strCustomerNo = Branding.getCustomerNo(row.Cells(idxRegion).Text)

         lblMessage.Text &= ": '" & strCustomerNo

         If strCustomerNo <> "" Then

            For idxCol = idxRegion + 1 To idxTotal - 1
               strPeriod = Brand.getPeriod(Brand.PlannedDT.Columns(idxCol).ColumnName)
               If strPeriod <> "" Then
                  Try
                     dblQF = CDbl(row.Cells(idxCol).Text.Replace(",", ""))
                  Catch icEx As InvalidCastException
                     dblQF = 0.0
                  End Try
                  dblQF = Math.Round(dblQF, 4, MidpointRounding.AwayFromZero)
                  If blnDebug Then lblMessage.Text &= "':: INSERT INTO dwfQFPlan (strPeriod, refBrandID, strCustomerNo, dblQF, strLastUser, dtStamp) " & _
                        "VALUES ('" & strPeriod & "', " & Brand.BrandID & ", '" & strCustomerNo & "', " & dblQF & ", '" & strLastUser & "', '" & dtStamp.ToString("yyyyMMddHHmm") & "')"

                  db.doQuery("INSERT INTO dwfQFPlan (strPeriod, refBrandID, strCustomerNo, dblQF, strLastUser, dtStamp) " & _
                        "VALUES ('" & strPeriod & "', " & Brand.BrandID & ", '" & strCustomerNo & "', " & dblQF & ", '" & strLastUser & "', '" & dtStamp.ToString("yyyyMMddHHmm") & "')")
               End If 'eoIF Period

            Next

         End If 'eoIF Customer
         idxRow += 1
      Next

      db.doQuery("INSERT INTO dwfQFAdjustment (strPeriod, refBrandID, strCustomerNo, dblQF, strLastUser, dtStamp, blnApplied, strLog) " & _
          "SELECT strPeriod, refBrandID, strCustomerNo, dblQF, strLastUser, dtStamp, 0, 'New Record from PLANNING - " & dtStamp.ToString("d MMM yyyy HH:mm") & " - " & strLastUser & "' FROM dwfQFPlan " & _
          "WHERE refBrandID = " & Brand.BrandID & " AND strPeriod >= '" & Brand.DateFinYearStart.ToString("yyyyMM") & "' AND strPeriod <= '" & Brand.DateFinYearEnd.ToString("yyyyMM") & "'")

      If blnDebug Then Exit Sub

      btnLoad_Click(sender, e)

      Try
         db.doQuery(sql_remove_duplicate_BrandQF_Job(Brand.BrandID, Brand.FinYear, Brand.BrandName, ddlBrandCopy.SelectedValue))
         db.doQuery(sql_insert_BrandQF_Job(Brand.BrandID, Brand.FinYear, Brand.BrandName, ddlBrandCopy.SelectedValue))

         lblMessage.Text = "Brand QFs will be processed shortly."
         lblMessage.CssClass = "boxGood"
         'Dim qfGen As QFGen
         'If ddlBrandCopy.SelectedValue = 0 Then
         '  qfGen = New QFGen(Brand.FinYear, Brand.BrandID)
         '  qfGen.GenerateBrand()
         'Else
         '  qfGen = New QFGen(Brand.FinYear, Brand.BrandID)
         '  qfGen.CopyBrand(ddlBrandCopy.SelectedValue)
         'End If
      Catch ex As Exception
         lblMessage.Text = ex.Message & ex.StackTrace
         lblMessage.CssClass = "boxError"
      End Try

   End Sub

   Private Sub setPlanning_RegionTotal()
      Dim idxTotal, idxRegion As Integer
      Dim Region As String = ""
      Dim dblQFTotal, dblQFTarget As Double

      idxRegion = Brand.PlannedDT.Columns.IndexOf("Region")
      idxTotal = Brand.PlannedDT.Columns.IndexOf("Total")

      Dim txtQF As TextBox
      dblQFTarget = CDbl(txtQFTarget.Text.Replace(",", ""))

      For Each row As GridViewRow In gvPlanning.Rows
         Try
            txtQF = DirectCast(row.Cells(idxTotal).Controls(1), TextBox)
            Region = row.Cells(idxRegion).Text
            dblQFTotal = Math.Round(dblQFTarget * CDbl(Brand.getDtCellPlanningStats(Region, "AVG %Contribution")), 4, MidpointRounding.AwayFromZero)
            txtQF.Text = FormatNumber(dblQFTotal, 2)

         Catch ex As Exception

         End Try
      Next

   End Sub

   Private Sub setPlanning_FullYear()
      Dim idxTotal, idxTotalRow, idxRegion As Integer
      Dim dblQFTotal, dblQF As Double

      idxRegion = Brand.PlannedDT.Columns.IndexOf("Region")
      idxTotal = Brand.PlannedDT.Columns.IndexOf("Total")

      're-total region totals
      dblQFTarget = 0
      For idxRow = 0 To gvPlanning.Rows.Count - 2
         Dim row As GridViewRow = gvPlanning.Rows(idxRow)
         Dim txtQF As TextBox = DirectCast(row.Cells(idxTotal).Controls(1), TextBox)

         If row.Cells(idxRegion).Text = "Global" Then
            idxTotalRow = idxRow
         Else
            dblQFTarget += CDbl(txtQF.Text.Replace(",", ""))
         End If

      Next
      txtQFTarget.Text = FormatNumber(dblQFTarget, 0)
      gvPlanning.Rows(idxTotalRow).Cells(idxTotal).Text = FormatNumber(dblQFTarget, 2)
      percBGForecast.Text = FormatPercent(Math.Round(dblQFTarget / CDbl(ldblSalesProjected.Text.Replace(",", "")) - 1, 4), 0)

      'process
      For idxRow = 0 To gvPlanning.Rows.Count - 1
         Dim row As GridViewRow = gvPlanning.Rows(idxRow)

         If row.Cells(idxRegion).Text = gvPlanning.Rows(gvPlanning.Rows.Count - 1).Cells(idxRegion).Text Then
            '% row
            row.Cells(idxTotal + 1).Text = ""

            For idxCol = idxRegion + 1 To idxTotal
               Try
                  dblQF = gvPlanning.Rows(idxTotalRow).Cells(idxCol).Text
                  dblQF = Math.Round(dblQF / dblQFTarget, 4, MidpointRounding.AwayFromZero)

                  row.Cells(idxCol).Text = Microsoft.VisualBasic.FormatPercent(dblQF, 1)
               Catch ex As Exception
               End Try
            Next
         Else
            'all other rows
            Try
               Try
                  Dim txtQF As TextBox = DirectCast(row.Cells(idxTotal).Controls(1), TextBox)
                  dblQFTotal = CDbl(txtQF.Text.Replace(",", ""))

               Catch argEx As ArgumentException ' RowTotal x ColTotal is not a textbox, just a lable/text
                  dblQFTotal = CDbl(row.Cells(idxTotal).Text.Replace(",", ""))
               Catch ex As Exception

               End Try


               'set averages column
               row.Cells(idxTotal + 1).Text = Math.Round(dblQFTotal / 12, 2, MidpointRounding.AwayFromZero)
               'the rest of the row
               For idxCol = idxRegion + 1 To idxTotal - 1
                  Try

                     dblQF = Math.Round(dblQFTotal * Brand.getDtCellSalesRhythm("Average %", idxCol), 4, MidpointRounding.AwayFromZero)


                     row.Cells(idxCol).Text = dblQF
                  Catch ex As Exception
                  End Try
               Next


            Catch ex As Exception

            End Try
         End If
      Next

      planningAnchor.Focus()
      'ClientScript.RegisterClientScriptBlock(Me.GetType, "", "jsQFTargetBlur(d('ctl00_ContentPlaceHolder1_txtQFTarget'));", True) ' weird: the view state stuff is overriding js changes to this control

   End Sub

   Protected Sub btnCalculate_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnCalculate.Click
      setPlanning_RegionTotal()
      setPlanning_FullYear()
   End Sub

   Protected Sub btnCalculateRe_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnCalculateRe.Click
      setPlanning_FullYear()
   End Sub


   Private Sub GribView_PreRender(ByRef gv As GridView)
      'handle all the gv bindings
      Dim blnPerc As Boolean = False

      Try
         Select Case gv.ID

            Case "gvPlanning"
               gv.Caption = "FY " & Brand.FinYear & " - Planned Forecast 9L"

               'Case "gvStats"
               '  gv.Caption = "STATS & PROJECTIONS (" & Brand.FinYearLabel & ")"
               'blnPerc = True
               'Case "gvSalesR1" 'moved
               '  gv.Caption = "SALES RHYTHM FY " & Brand.DateFinYearEndMin1.ToString("yyyy")

            Case "gvSalesR2"
               'blnPerc = True
               '  gv.Caption = "SALES RHYTHM FY " & Brand.DateFinYearEndMin2.ToString("yyyy")
         End Select

      Catch nrEx As NullReferenceException
         'expected: Brand not inially set
      Catch ex As Exception
         lblMessage.Text = ex.Message & "<BR/>" & ex.StackTrace
      End Try

      'should maybe pick up column definitions from a dead sdsReport with no data ? no- rather use a colour function in branding
      If Not gv.DataSource Is Nothing Then
         Try
            Dim dt As DataTable = DirectCast(gv.DataSource, System.Data.DataTable) 'could through ex if datasourse is missing, if so discontinue

            lblMessage.Text &= Branding.colourDataRow(gv, dt, Brand, False, blnPerc) 'append!!

         Catch ex As Exception
            lblMessage.Text = ex.Message & "<BR/>" & ex.StackTrace
         End Try

      End If 'eoIF

      gv.Visible = True

   End Sub

   Private Sub gv_PreRender(ByVal sender As Object, ByVal e As System.EventArgs) Handles gvPlanning.PreRender, gvStats.PreRender, gvSalesR1.PreRender, gvSalesR2.PreRender
      'format gv
      GribView_PreRender(sender)
   End Sub

End Class