Imports System.Web.Configuration
Imports Stock_Control.sqlDB

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

  Private db As New sqlDB
  Private Shared strDate, strPeriod As String

  Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    WebConfigurationManager.AppSettings("strPage") = "Stock Control Report"
    strDate = Date.Now.ToString("yyyyMM")
    strPeriod = ddlPeriod.SelectedValue

    If Page.IsPostBack Then


    End If

  End Sub

  Protected Sub btnView_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnView.Click
    'strHeading = "<td style='border-bottom: 1px solid black; background-color: #EEEEEE;' class='headings' width='28.5%'>SKU</td>"

    'sql_get_PP()							
    Dim blnValue As Boolean
    Dim intDiff, i, j As Integer
    Dim dr, drR As DataRow
    Dim ds As DataSet
    Dim dt As DataTable
    Dim dblVariance, dblMC As Double
    Dim strStartDate, strEndDate, y, m, s As String
    Dim dblRowTotal, dblRowAverage As Double

    btnExport.Enabled = True

    sdsReport.FilterExpression = ""
    If ddlBM.SelectedIndex > 0 Then sdsReport.FilterExpression &= "strUserName = '" & ddlBM.SelectedValue & "' AND "
    If ddlBrand.SelectedIndex > 0 Then sdsReport.FilterExpression &= "BrandID = " & ddlBrand.SelectedValue & " AND "

    y = ddlPeriod.SelectedValue.Substring(0, 4)
    m = ddlPeriod.SelectedValue.Substring(4, 2)
    strStartDate = CInt(strPeriod)

    If CInt(m) = 1 Or CInt(m) = 2 Then
      strEndDate = CInt(strPeriod) + 11
    Else
      strEndDate = CInt(strPeriod) + 99
    End If

    'main getProduct Q
    s = sdsReport.SelectCommand.ToString
    i = s.IndexOf("WHERE")
    s = s.Substring(0, i + 6) & sdsReport.FilterExpression & s.Substring(i + 5)
    dt = db.doQueryDS(s).Tables(0)

    'set Period Headings
    i = 0
    ds = db.doQueryDS(db.sql_get_Period("", strStartDate, strEndDate))
    For Each dr In ds.Tables(0).Rows
      Try
        i += 1
        dt.Columns("dblMC" & i).ColumnName = db.nz(db.sqlDate(dr!strPeriod & "010101", "MMM yyyy"), db.sqlDate())

      Catch ex As Exception
      End Try
    Next
    j = 0

    'moved to ln111
    'dt.Columns(17).ColumnName = "Total " & ddlDisplayField.SelectedItem.Text
    'dt.Columns(18).ColumnName = "Average " & ddlDisplayField.SelectedItem.Text

    For i = 0 To dt.Rows.Count - 1
      j += 1
      drR = dt.Rows(i)
      'dblVariance = db.nz(drR.Item("Target Variance"), 2.5)
      dt.Rows(i).Item("#") = j

      dblRowTotal = 0
      dblRowAverage = 0
      blnValue = False
      ds = db.doQueryDS(db.sql_get_PP(drR!Code, strStartDate, strEndDate))
      For Each dr In ds.Tables(0).Rows
        dblMC = Math.Round(CDbl(db.nz(dr(ddlDisplayField.SelectedValue), 0.0)), 2, MidpointRounding.AwayFromZero)

        intDiff = ProductPeriod.monthDiff(dr!mstPeriod, strPeriod) + 1 'get the diff in period between dr!mstPeriod and the selected month

        'If intDiff > 0 Then
        dt.Rows(i).Item(db.sqlDate(dr!mstPeriod & "010101", "MMM yyyy").ToString) = dblMC 'db.sqlDate(dr!mstPeriod & "010101", "MMM yyyy").ToString
        If dblMC <> 0 Then
          blnValue = True
        End If
        dblRowTotal += CDbl(db.nz(dr(ddlDisplayField.SelectedValue), 0.0))
        'End If
      Next

      If dblRowTotal > 0 Then
        dblRowAverage = dblRowTotal / ds.Tables(0).Rows.Count
      Else
        dblRowAverage = 0
      End If
      dt.Rows(i).Item("Total") = Math.Round(dblRowTotal, 2, MidpointRounding.AwayFromZero)
      dt.Rows(i).Item("Average") = Math.Round(dblRowAverage, 2, MidpointRounding.AwayFromZero)
      'If blnValue = False And chkAll.Checked = False Then
      '  dt.Rows(i).Delete()
      '  j += -1
      'End If
    Next

    dt.Columns(17).ColumnName = "Total " & ddlDisplayField.SelectedItem.Text
    dt.Columns(18).ColumnName = "Average " & ddlDisplayField.SelectedItem.Text

    gvReport.DataSource = dt
    gvReport.DataBind()
    gvReport.Visible = True
  End Sub

  Private Sub ddlPeriod_PreRender(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlPeriod.PreRender, ddlBM.PreRender, ddlBrand.PreRender
    If Not Page.IsPostBack Then
      Dim ddl As DropDownList
      ddl = sender
      Try
        Select Case ddl.ID
          Case "ddlBM"
            ddlBM.Items.Insert(0, "- Any -")
          Case "ddlBrand"
            ddlBrand.Items.Insert(0, "- Any -")
          Case "ddlPeriod"
            If ddlPeriod.SelectedIndex = -1 Then ddlPeriod.Items.FindByValue(Date.Today.ToString("yyyyMM")).Selected = True
        End Select
      Catch ex As Exception
        ddl.ClearSelection()
      End Try
    End If
  End Sub

  'Protected Sub ddlPrincipal_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) Handles ddlPrincipal.SelectedIndexChanged
  '	ddlBrand.SelectedIndex = -1
  'End Sub

  Private Sub gvReport_PreRender(ByVal sender As Object, ByVal e As System.EventArgs) Handles gvReport.PreRender
    'format gv

    For Each row As GridViewRow In gvReport.Rows
      row.Cells(0).HorizontalAlign = HorizontalAlign.Right
      For i As Integer = 5 To row.Cells.Count - 1
        row.Cells(i).HorizontalAlign = HorizontalAlign.Right
      Next
    Next
  End Sub

  Protected Sub btnExport_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnExport.Click
    Dim lbrand, lBM As String

    If ddlBrand.SelectedIndex > 0 Then lbrand = "_" & ddlBrand.SelectedItem.Text.Replace(" ", "")
    If ddlBM.SelectedIndex > 0 Then lBM = "_" & ddlBM.SelectedItem.Text.Replace(" ", "")

    Response.Clear()
    Response.Buffer = True
    Response.ContentType = "application/vnd.ms-excel"
    Response.Charset = ""
    Response.AddHeader("Content-Disposition", "attachment;filename=StockControlReport" & lbrand & "_" & strPeriod & ".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()
    Me.Controls.Add(frm)
    frm.Controls.Add(gvReport)
    frm.RenderControl(oHtmlTextWriter)
    Response.Write(oStringWriter.ToString())
    Response.End()

  End Sub
End Class