Imports System.Web.Configuration
Imports Stock_Control.sqlDB

Partial Public Class BMAReport
	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") = "Variance 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 blnMC 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

		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
		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

			blnMC = 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!dblMonths, 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
				If dblMC > dblVariance + 0.5 Or dblMC < dblVariance - 0.5 Then
					blnMC = True
				End If

				'End If
			Next

			If blnMC = False And chkAll.Checked = False Then
				dt.Rows(i).Delete()
				j += -1
			End If
		Next

		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 18
				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=VarianceReport" & 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