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

'20110111 - v0.0.3 - Apply OpenSeason Checks
'20110318 - v1.0.1 - removed planning gv - pj
'20110328 - v1.0.1 - Limiting brand selection if user not Special
'20110328 - v1.0.1 - limiting region selection if rsm
'20110628 - v1.0.2 - added Recalc Regions based on sales Rhythm & Global for period

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

  Private Shared Brand As Branding

  Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    If Not Page.IsPostBack Then
      'ini - unset gvAdj
      gvAdjusted.DataSourceID = "" ' unset dsID and reset DS = dt

    End If
  End Sub

  '**********************************************************************************************
  '***FUNCTIONS***
  '**********************************************************************************************

  Protected Sub LoadBrand()
    If CDbl(ddlBrand.SelectedValue) > 0 And CDbl(ddlFinYear.SelectedValue) > 0 Then

      Dim dt As New DataTable

      Brand = New Branding(ddlFinYear.SelectedValue, ddlBrand.SelectedValue, ddlRegion.SelectedValue)

      lblBrand.Text = Brand.BrandLabel

      'planned
      '20110318 - planning gv is now History & Plan- pj
      Brand.PlannedDT = Brand.GetPlannedAndHistoricFullYear()
      gvPlanned.DataSource = Brand.PlannedDT
      gvPlanned.DataBind()

      'Stats
      dt = Brand.getStatsAndGrowth()
      gvStats.DataSource = dt
      gvStats.DataBind()
      gvStats.Visible = True

      'adjusted
      Brand.LogDT = Brand.GetLogFullYear()
      Brand.AppliedDT = Brand.GetAppliedFullYear()

      Brand.AdjustedDT = Brand.GetAdjustedFullYear()
      gvAdjusted.DataSourceID = "" ' unset dsID and reset DS = dt
      gvAdjusted.DataSource = Brand.AdjustedDT
      gvAdjusted.DataBind()
      gvAdjusted.Visible = True

      'actual
      Brand.ActualDT = Brand.GetActualFullYear()
      gvActual.DataSource = Brand.ActualDT
      gvActual.DataBind()
      gvActual.Visible = True

      'Var Actual vs Planned
      'dt = Brand.GetVarianceFullYear1()
      'gvVariance1.DataSource = dt
      'gvVariance1.DataBind()
      'gvVariance1.Visible = True

      'Var Actua2 vs Planned
      'dt = Brand.GetVarianceFullYear2()
      'gvVariance2.DataSource = dt
      'gvVariance2.DataBind()
      'gvVariance2.Visible = True

      btnExport.Enabled = True

      btnSave.Enabled = False
      btnApply.Enabled = False

      Try 'LAME! admin.Page_Load has not triggered yet so Session doesn't have a value in bedug. wtf microshot?!
        '20110111 - v0.0.3 - Apply OpenSeason Checks
        Dim dr As DataRow
        dr = db.getRow("SELECT refBrandID, strDateStart, strDateEnd FROM mstOpenSeason " & _
          "WHERE (refBrandID = 0 OR refBrandID = " & ddlBrand.SelectedValue & ") AND strDateStart <= '" & Date.Today.ToString("yyyyMMdd") & "' AND strDateEnd >= '" & Date.Today.ToString("yyyyMMdd") & "' ORDER BY refBrandID DESC")
        If Not dr Is Nothing Then
          lblMessage.Text = " Model Unlocked: " & DisplayDate(dr!strDateStart) & " - " & DisplayDate(dr!strDateEnd) & "."
          btnSave.Enabled = CBool(Session.Item("USER")!blnEdit)
        Else
          lblMessage.Text = " Model Locked."
        End If

      Catch ex As Exception
        btnSave.Enabled = False
      End Try
      Try
        btnApply.Enabled = CBool(Session.Item("USER")!blnSpecial)
      Catch ex As Exception
        btnApply.Enabled = False
      End Try
      Try '20110628 - added btnRecalc
        If Session.Item("USER")!strUserGroup = "RSM" Then
          btnRecalc.Enabled = False
        Else
          btnRecalc.Enabled = True
        End If
      Catch ex As Exception
        btnRecalc.Enabled = False
      End Try

    Else
      btnExport.Enabled = False
      btnSave.Enabled = False
      btnApply.Enabled = False
      btnRecalc.Enabled = False

      lblBrand.Text = ""
    End If 'eoIF val>0

  End Sub

  Private Sub Save()
    Dim sql, strPeriod, strCustomerNo As String
    Dim dblQF As Double
    Dim dtAdjusted As DataTable
    Dim txtQF As TextBox
    Dim dtStamp As Date = Date.Now
    Dim idxDataKey, idxCol, cnt As Integer
    Dim drNew As GridViewRow
    Dim drOld As DataRow

    Dim lblOutput As WebControls.Label
    lblOutput = Page.Master.FindControl("ctl00_lblOutput")

    'ini
    'get old values
    sql = ""

    dtAdjusted = Brand.AdjustedDT
    'set idx for datakey
    idxDataKey = dtAdjusted.Columns.IndexOf("Region")

    'start
    For idxRow = 0 To gvAdjusted.Rows.Count - 1
      'L1 ini
      drNew = gvAdjusted.Rows(idxRow)
      drOld = dtAdjusted.Rows(idxRow)
      strCustomerNo = Branding.getCustomerNo(drNew.Cells(idxDataKey).Text)

      If strCustomerNo <> "" Then 'excludes "Total" region
        For idxCol = idxDataKey + 1 To dtAdjusted.Columns.Count - 1
          'L2 ini
          'get Period, cust, valueNew, valueOld
          strPeriod = Brand.getPeriod(dtAdjusted.Columns(idxCol).ToString)
          If strPeriod <> "" Then

            txtQF = DirectCast(drNew.Cells(idxCol).Controls(1), TextBox)
            Try
              If txtQF.Text = "" Then txtQF.Text = "0.0"
              'txtQF.Text = txtQF.Text.ToString.Replace(Chr(160), ",") 'lame
              dblQF = CDbl(txtQF.Text.Replace(",", ""))

              'removed: perc is not viable for doing the apply, need to rebuild brand QF
              'If CDbl(drOld.Item(idxCol)) <> 0 Then
              '  percAdjusted = dblQF / CDbl(drOld.Item(idxCol))
              'Else
              '  percAdjusted = -1.0
              'End If, percAdjusted = ROUND(" & percAdjusted & ",8)

              'L2 start
              If Math.Round(CDbl(dblQF), 0, MidpointRounding.AwayFromZero) <> Math.Round(drOld.Item(idxCol), 0, MidpointRounding.AwayFromZero) Then

                'if values have changed save, else next
                sql = "UPDATE dwfQFAdjustment SET dblQF = ROUND(" & dblQF & ",4), blnApplied = 0, strLog = '[new: " & dblQF & ", old: " & drOld.Item(idxCol) & " - " & dtStamp.ToString("d MMM yyyy HH:mm:ss") & " - " & Session.Item("User")!strUserName & "] ' + CHAR(13) + strLog, strLastUser = '" & Session.Item("User")!strUserName & "', dtStamp = '" & dtStamp.ToString("yyyyMMddHHmm") & "' " & _
                      "WHERE strCustomerNo = '" & strCustomerNo & "' AND refBrandID = " & Brand.BrandID & " AND strPeriod = '" & strPeriod & "'"

                If db.doQuery(sql) = 0 Then 'if #Rows = 0 then
                  Dim blnApplied As String = "0"

                  sql = "INSERT INTO dwfQFAdjustment (strPeriod, refBrandID, strCustomerNo, dblQF, blnApplied, strNotes, strLog, strLastUser, dtStamp) " & _
                  "VALUES ('" & strPeriod & _
                  "'," & Brand.BrandID & _
                  ",'" & strCustomerNo & _
                  "',ROUND(" & dblQF & ",4)" & _
                  ", 0" & _
                  ",''" & _
                  ",'[new: " & dblQF & ", old: n/a - " & dtStamp.ToString("d MMM yyyy HH:mm:ss") & " - " & Session.Item("User")!strUserName & "] ' + CHAR(13)" & _
                  ",'" & Session.Item("User")!strUserName & "'" & _
                  ",'" & dtStamp.ToString("yyyyMMddHHmm") & "')"
                  db.doQuery(sql)
                End If
                cnt += 1

                'load log & comments
                Dim dr As DataRow
                dr = db.getRow("SELECT CASE WHEN dwfQFAdjustment.strNotes <> '' THEN dwfQFAdjustment.strNotes + CHAR(13) + CHAR(13) + dwfQFAdjustment.strLog ELSE dwfQFAdjustment.strLog END AS newToolTip " & _
                      "FROM dwfQFAdjustment " & _
                      "WHERE strCustomerNo = '" & strCustomerNo & "' AND refBrandID = " & Brand.BrandID & " AND strPeriod = '" & strPeriod & "'")
                txtQF.ToolTip = dr!newToolTip
                Brand.setDtCellLog(drNew.Cells(idxDataKey).Text, dtAdjusted.Columns(idxCol).ToString, dr!newToolTip)

                'reset control
                txtQF.CssClass = "textNumeric controlTextbox controlEdit"
              End If ' eoIF changed
            Catch icEx As InvalidCastException
              txtQF.CssClass = "textNumeric controlTextbox controlError"
              txtQF.Focus()
            Catch ex As Exception
              txtQF.CssClass = "textNumeric controlTextbox controlError"
              txtQF.Focus()
              txtQF.ToolTip = ex.GetType.ToString & Chr(13) & ex.Message & Chr(13) & ex.StackTrace
            End Try

          End If 'eoIF Period

          'L2 post
          'lblOutput.Text &= sql
        Next

      End If 'eoIF Customer
    Next

    If cnt > 0 Then

      'adjusted
      'Brand.Log = Brand.GetLogFullYear()

      gvAdjusted.DataSourceID = "" ' unset dsID and reset DS = dt
      gvAdjusted.DataSource = Brand.GetAdjustedFullYear()
      gvAdjusted.DataBind()
      gvAdjusted.Visible = True
    End If
    'post
    'reset ddl control jic some twat has gone and changed them without re-loading
    ddlBrand.SelectedValue = Brand.BrandID
    ddlFinYear.SelectedValue = Brand.FinYear

    'hide loading gif
    Me.ShowLoading(False)
  End Sub

  Private Sub Apply()

    btnExport.Enabled = False
    btnSave.Enabled = False
    btnApply.Enabled = False

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

      lblMessage.Text = "Brand QFs will be processed shortly."
      lblMessage.CssClass = "boxGood"

    Catch ex As Exception
      lblMessage.Text = ex.Message & "<BR>" & ex.StackTrace
    Finally

      btnExport.Enabled = True

      Try 'LAME! admin.Page_Load has not triggered yet so Session doesn't have a value in bedug. wtf microshit?!
        btnSave.Enabled = CBool(Session.Item("USER")!blnEdit)
      Catch ex As Exception
        btnSave.Enabled = False
      End Try
      Try
        btnApply.Enabled = CBool(Session.Item("USER")!blnSpecial)
      Catch ex As Exception
        btnApply.Enabled = False
      End Try
    End Try

  End Sub

  Private Sub Export()
    Dim dt As DataTable

    'hide loading gif
    Me.ShowLoading(False)

    Response.Clear()
    Response.Buffer = True
    Response.ContentType = "application/vnd.ms-excel"
    Response.Charset = ""
    Response.AddHeader("Content-Disposition", "attachment;filename=Adjustments_" & Brand.BrandName.Replace(" ", "") & "_" & Date.Now.ToString("yyyyMMddHHmm") & ".xls")
    Me.EnableViewState = False

    Dim oStringWriter As New System.IO.StringWriter()
    Dim oHtmlTextWriter As New System.Web.UI.HtmlTextWriter(oStringWriter)
    Dim frm As HtmlForm = New HtmlForm()
    Dim phContainer As New PlaceHolder
    Me.Controls.Add(frm)

    ' note: gvAdjustments vs gvAdjusted. adjusted has textboxes for the editable stuff
    Dim gvAdjustments As New GridView
    dt = Brand.AdjustedDT
    gvAdjustments.DataSource = dt
    gvAdjustments.DataBind()
    gvAdjustments.Caption = gvAdjusted.Caption
    GribView_PreRender(gvAdjustments)

    'add controls
    phContainer.Controls.Add(lblBrand)
    phContainer.Controls.Add(gvStats)
    phContainer.Controls.Add(gvPlanned)
    phContainer.Controls.Add(gvAdjustments)
    phContainer.Controls.Add(gvActual)
    'phContainer.Controls.Add(gvVariance1)
    'phContainer.Controls.Add(gvVariance2)

    frm.Controls.Add(phContainer)

    frm.RenderControl(oHtmlTextWriter) ' remember to turn EnableEventValidation="False" off in the page derective else vb fucks out. lame!
    Response.Write(oStringWriter.ToString())
    'Response.Write("<style>table,td{border-style:solid;border-color:pink;border-width:thin}</style>")
    Response.End()

  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 "gvPlanned"
        '  gv.Caption = "FY " & Brand.FinYear & " - Planned Forecast 9L"

        Case "gvAdjusted"
          gv.Caption = "FY " & Brand.FinYear & " - Adjusted Forecast 9L"

        Case "gvActual"
          gv.Caption = "FY " & Brand.FinYear & " - Actual 9L"

        Case "gvVariance1"
          gv.Caption = "FY " & Brand.FinYear & " - Variance: Actual vs. Planned 9L"
          blnPerc = True
        Case "gvVariance2"
          gv.Caption = "FY " & Brand.FinYear & " - Variance: Actual vs. Adjusted 9L"
          blnPerc = True
          'Case "gvStats"
          '  gv.Caption = "BRAND GROWTH & STATS"
          'blnPerc = True
      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, chkDecimal.Checked, 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 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
  '**********************************************************************************************
  '***HANDLES***
  '**********************************************************************************************
  Protected Sub btnLoad_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnLoad.Click
    LoadBrand()
  End Sub

  Protected Sub btnSave_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnSave.Click
    Save()

    LoadBrand()
  End Sub


  Protected Sub btnApply_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnApply.Click
    Save()
    'Apply changes to SC & dwfQF

    Apply()

    LoadBrand()
  End Sub

  Protected Sub btnExport_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnExport.Click
    'Save() ' don't save and export = will have to write back values to underlying DT structures
    Export()
  End Sub

  Private Sub gv_PreRender(ByVal sender As Object, ByVal e As System.EventArgs) Handles gvActual.PreRender, gvAdjusted.PreRender, gvPlanned.PreRender, gvVariance1.PreRender, gvVariance2.PreRender, gvStats.PreRender
    'format gv
    GribView_PreRender(sender)
  End Sub

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

  End Sub

  Private Sub ddlBrand_PreRender(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlBrand.PreRender

    Dim ds As New DataSet
    Dim arrMyBrands As New Collection
    Dim j As Integer = ddlBrand.Items.Count - 1

    '20110328 - v1.0.1 - limiting brand selection if not Special
    If Not Page.IsPostBack Then
      If Session.Item("User") Is Nothing Then 'lame
        LogginUser(Request, Session)
      End If


      If Session.Item("USER")!strUserGroup = "RSM" Then

        ds = db.doQuery("SELECT * FROM mstUserBrand WHERE refUserID = " & Session.Item("USER")!UserID)
        For Each row In ds.Tables(0).Rows
          arrMyBrands.Add(row!refBrandID, row!refBrandID)
        Next

        For i = j To 1 Step -1

          If Not arrMyBrands.Contains(ddlBrand.Items(i).Value.ToString) Then
            ddlBrand.Items.RemoveAt(i)
          End If
        Next

        If arrMyBrands.Count = 0 Then
          lblMessage.Text = "User has no brands. Click <a href='MyBrands.aspx'>here</a> to setup your region and brands."
        Else
        End If
      End If
    End If
  End Sub

  Private Sub ddlRegion_PreRender(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlRegion.PreRender

    '20110328 - v1.0.1 - limiting region selection if rsm
    If Not Page.IsPostBack Then
      If Session.Item("USER")!strUserGroup = "RSM" Then
        For i = ddlRegion.Items.Count - 1 To 0 Step -1
          If ddlRegion.Items(i).Value <> Session.Item("USER")!strRegion Then
            ddlRegion.Items.RemoveAt(i)
          End If
        Next
      End If
    End If

    Try
      If Session("USER")!strRegion = "" Then
        lblMessage.Text = "User has no region. Click <a href='MyBrands.aspx'>here</a> to setup your region and brands."
      Else
      End If
    Catch ex As Exception
    End Try
  End Sub

  '20110628 - v1.0.2 - added Recalc Regions based on sales Rhythm & Global for period
  Protected Sub btnRecalc_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnRecalc.Click
    RecalcRegions()
  End Sub

  Private Sub RecalcRegions()
    Dim idxDataKey, idxCol, idxPercCQDLY As Integer
    Dim strPeriod As String
    Dim dblQF, dblGlobal As Double
    Dim dtAdjusted As DataTable
    Dim txtQF, txtGlobal As TextBox
    Dim dtStamp As Date = Date.Now
    Dim drGlobal, drNew As GridViewRow
    Dim drOld, drFiscal As DataRow
    Dim tempBrand As Branding

    Dim lblOutput As WebControls.Label
    lblOutput = Page.Master.FindControl("ctl00_lblOutput")

    'get planning stats for %C(ontribution) QF Sales LY [by region]
    drFiscal = db.getRow("SELECT * FROM mstPeriod WHERE strPeriod = '" & Date.Today.ToString("yyyyMM") & "'")
    'load settings pannel
    tempBrand = New Branding(drFiscal!intFiscalYear, Brand.BrandID)
    Brand.PlanningStatsDT = Brand.getStatsAndProjections(tempBrand, Brand, 100)
    idxPercCQDLY = Brand.PlanningStatsDT.Columns.IndexOf("%C QF LY")

    dtAdjusted = Brand.AdjustedDT
    idxDataKey = dtAdjusted.Columns.IndexOf("Region")

    For idxCol = idxDataKey + 1 To dtAdjusted.Columns.Count - 1
      strPeriod = Brand.getPeriod(dtAdjusted.Columns(idxCol).ToString)
      If strPeriod <> "" Then

        drGlobal = gvAdjusted.Rows(0)
        drOld = dtAdjusted.Rows(0)

        txtGlobal = DirectCast(drGlobal.Cells(idxCol).Controls(1), TextBox)
        Try
          If txtGlobal.Text = "" Then txtGlobal.Text = "0.0"
          dblGlobal = CDbl(txtGlobal.Text.Replace(",", ""))

          If Math.Round(CDbl(dblGlobal), 0, MidpointRounding.AwayFromZero) <> Math.Round(drOld.Item(idxCol), 0, MidpointRounding.AwayFromZero) Then
            'if new G value != old G value then apply to child regions
            For idxRow = 1 To dtAdjusted.Rows.Count - 1
              drNew = gvAdjusted.Rows(idxRow)
              txtQF = DirectCast(drNew.Cells(idxCol).Controls(1), TextBox)
              txtQF.Text = "0"

              Try
                For idxP As Integer = 0 To Brand.PlanningStatsDT.Rows.Count - 1
                  If Brand.PlanningStatsDT.Rows(idxP).Item(idxDataKey).ToString = dtAdjusted.Rows(idxRow).Item(idxDataKey).ToString Then

                    If Me.chkDecimal.Checked Then
                      dblQF = Math.Round(dblGlobal * Brand.PlanningStatsDT.Rows(idxRow - 1).Item(idxPercCQDLY), My.Settings.intDecimals, MidpointRounding.AwayFromZero)
                    Else
                      dblQF = Math.Round(dblGlobal * Brand.PlanningStatsDT.Rows(idxRow - 1).Item(idxPercCQDLY), 0, MidpointRounding.AwayFromZero)
                    End If

                    txtQF.Text = dblQF
                    Exit For
                  End If 'eoIF Region Planning == Region Adj
                Next ' eoFor Planning: chance exists that Planning will not have a % for this region ie. planning dt and adj dt not on sync
              Catch ex As Exception
                txtQF.CssClass = "controlTextbox textNumeric textError"
                txtQF.ToolTip = ex.Message & " " & ex.StackTrace
              End Try
            Next 'eoForeach Region
          End If 'eoIF old != new

        Catch ex As Exception

        End Try

      End If ' if Period

    Next 'eoCol

  End Sub
End Class