Imports Superbowl.RepQF
Imports System.Web.Configuration

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

	Private Shared intDateColumn, intBudgetColumn As Integer
	Private Shared dblRows, dblTotal As Double
	Private Shared blnRunOnce As Boolean
	Private Shared strGVFilter As String = ""
	Private dt As DataTable
	Private ds As DataSet
	Private db As New sqlDB

	Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
		'If Not Page.IsPostBack Then
		'End If

		blnRunOnce = False
		dblTotal = 0
		dblRows = 0
		lblMessage.Text = ""
	End Sub

	Private Sub Page_LoadComplete(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.LoadComplete

		If Not Page.IsPostBack Then
			Dim arrFilter(0) As String
			Dim intStart, intEnd As Integer
			intDateColumn = -1
			intBudgetColumn = -1

			gvRepQF.PageSize = WebConfigurationManager.AppSettings("PAGESIZE")
			'btnSearch_Click(sender, e)
		End If

		dblRows = gvRepQF.PageIndex * gvRepQF.PageSize		 '< has to run for every action				
	End Sub

	Private Sub Page_PreRenderComplete(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.PreRenderComplete
		Dim s As String = ""

		btnExport.Enabled = True
		If Not Page.IsPostBack Then
			ddlStartDate.Items.FindByValue(Date.Today.ToString("yyyyMM")).Selected = True
			ddlEndDate.Items.FindByValue(sqlDB.getGridEndDate(Date.Today.ToString("yyyyMM"))).Selected = True

			btnSearch_Click(sender, e)
		End If
		
	End Sub

	Private Sub gvRepQF_Sorting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewSortEventArgs) Handles gvRepQF.Sorting
		sdsRepQF.SelectCommand = strGVFilter
		dblRows = 0
		dblTotal = 0
	End Sub

	Private Sub gvRepQF_PageIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles gvRepQF.PageIndexChanged
		sdsRepQF.SelectCommand = strGVFilter
		WebConfigurationManager.AppSettings("intListIndex") = gvRepQF.PageIndex
	End Sub

	Private Sub gvRepQF_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles gvRepQF.RowDataBound

		If e.Row.RowType = DataControlRowType.DataRow And intDateColumn > -1 Then
			Try
				dblRows += 1
				e.Row.Cells(0).Text = dblRows
				e.Row.Cells(intDateColumn).Text = sqlDB.SqlDate(e.Row.Cells(intDateColumn).Text, "d MMM yyyy")	' "d MMM yyyy HH:mm")
			Catch ex As Exception
				e.Row.Cells(intDateColumn).Text = Date.Now.ToString("n/a")
			End Try
			'Try
			'	dblTotal += CDbl(e.Row.Cells(intBudgetColumn).Text)
			'	'gvRepQF.Columns(intBudgetColumn).FooterText = dblTotal
			'Catch ex As Exception

			'End Try

		End If
	End Sub

	Protected Sub btnSearch_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnSearch.Click
		'sdsRepQF.FilterExpression = "1=1"
		dblRows = 0
		dblTotal = 0
		Dim comma As String = ""
		'force period checked
		If chkPeriod.Checked = False And chkBranch.Checked = False And chkPrincipal.Checked = False And chkBranch.Checked = False And chkRep.Checked = False Then
			chkPeriod.Checked = True
		End If

		Dim sqlSelect, sqlFrom, sqlWhere, sqlGroupBy, sqlOrderBy, s As String

		For i As Integer = 0 To gvRepQF.Columns.Count - 1
			Try
				gvRepQF.Columns.RemoveAt(0)
				gvExport.Columns.RemoveAt(0)
			Catch ex As Exception
			End Try
		Next

    sqlSelect = "SELECT '' AS '#', Max(dwfQF.strLastUser) as strLastUser, MAX(dwfQF.dtStamp) as dtStamp, ROUND(SUM(dwfQF.dblBudget9l),4) AS dblbudget9l, ROUND(SUM(dwfQF.dblPercentage),3)*100 AS dblPercentage"
    sqlFrom = " FROM dwfQF INNER JOIN mstBrand ON dwfQF.refBrandID = mstBrand.BrandID INNER JOIN mstRep INNER JOIN mstBranch ON mstRep.refBranchID = mstBranch.BranchID ON dwfQF.refRepID = mstRep.RepID INNER JOIN mstPrincipal ON mstBrand.refPrincipalID = mstPrincipal.PrincipalID "
    sqlWhere = " WHERE (mstBrand.blnActive = 1) AND [blnGenerateQF] = 1 AND [strPeriod] >= '" & ddlStartDate.SelectedValue & _
    "' AND [strPeriod] <= '" & ddlEndDate.SelectedValue & "' "

    sqlGroupBy = " GROUP BY "
    sqlOrderBy = " ORDER BY '#'"

    gvRepQF.Columns.Add(gvColumns.Columns(0))           'triggers onDataBound()	 

    If chkPeriod.Checked Then
      sqlSelect &= ", dwfQF.strPeriod"
      sqlGroupBy &= comma & " dwfQF.strPeriod"
      sqlOrderBy &= ", dwfQF.strPeriod"
      gvRepQF.Columns.Add(gvColumns.Columns(1))
      gvExport.Columns.Add(gvColumns.Columns(1))
      comma = ","
    End If
    If chkBrand.Checked Then
      sqlSelect &= ", mstBrand.strBrand"
      sqlGroupBy &= comma & " mstBrand.strBrand"
      sqlOrderBy &= ", mstBrand.strBrand"
      gvRepQF.Columns.Add(gvColumns.Columns(2))
      gvExport.Columns.Add(gvColumns.Columns(2))
      comma = ","
    End If
    If chkPrincipal.Checked Then
      sqlSelect &= ", mstPrincipal.strPrincipal"
      sqlGroupBy &= comma & " mstPrincipal.strPrincipal"
      sqlOrderBy &= ", mstPrincipal.strPrincipal"
      gvRepQF.Columns.Add(gvColumns.Columns(3))
      gvExport.Columns.Add(gvColumns.Columns(3))
      comma = ","
    End If
    If chkBranch.Checked Then
      sqlSelect &= ", strBranch +' '+ strCode AS strBranch"
      sqlGroupBy &= comma & " strBranch +' '+ strCode"
      sqlOrderBy &= ", mstBranch.strBranch"
      gvRepQF.Columns.Add(gvColumns.Columns(4))
      gvExport.Columns.Add(gvColumns.Columns(4))
      comma = ","
    End If
    If chkRep.Checked Then
      sqlSelect &= ", mstRep.strRepName +' - '+ dwfQF.refRepID AS strRepName"
      sqlGroupBy &= comma & " mstRep.strRepName +' - '+ dwfQF.refRepID"
      sqlOrderBy &= ", mstRep.strRepName"
      gvRepQF.Columns.Add(gvColumns.Columns(5))
      gvExport.Columns.Add(gvColumns.Columns(5))
      comma = ","
    End If
    If chkZero.Checked Then
      sqlWhere &= " AND dwfQF.dblBudget9l <> 0"
    End If

    If ddlPrincipal.SelectedIndex > 0 Then
      sqlWhere &= " AND refPrincipalID = " & ddlPrincipal.SelectedValue
    End If
    If ddlBrand.SelectedIndex > 0 Then
      sqlWhere &= " AND refBrandID = " & ddlBrand.SelectedValue
    End If
    If ddlBranch.SelectedIndex > 0 Then
      sqlWhere &= " AND refBranchID = " & ddlBranch.SelectedValue
    End If
    If ddlRep.SelectedIndex > 0 Then
      sqlWhere &= " AND refRepID = '" & ddlRep.SelectedValue & "' "
    End If

    s = sqlSelect & sqlFrom & sqlWhere & sqlGroupBy & sqlOrderBy

    gvRepQF.Columns.Add(gvColumns.Columns(6))
    gvExport.Columns.Add(gvColumns.Columns(6))
    intBudgetColumn = gvRepQF.Columns.Count - 1
    If chkBrand.Checked Then
      gvRepQF.Columns.Add(gvColumns.Columns(7))
      gvExport.Columns.Add(gvColumns.Columns(7))
    End If
    gvRepQF.Columns.Add(gvColumns.Columns(8))
    gvExport.Columns.Add(gvColumns.Columns(8))

    gvRepQF.Columns.Add(gvColumns.Columns(9))
    gvExport.Columns.Add(gvColumns.Columns(9))


    dt = sqlDB.doQueryDS(s).Tables(0)
    strGVFilter = s
    WebConfigurationManager.AppSettings("intListFilter") = strGVFilter
    sdsRepQF.SelectCommand = strGVFilter

    If sqlDB.CountRows(s) = 0 Then
      lblMessage.Text = "<div width='100%' align='center'>- No Sales QF's found -</div>"
      btnExport.Enabled = False
      gvRepQF.Visible = False
    Else
      Try
        Dim dr As DataRow
        dr = db.GetRow("SELECT ROUND(SUM(dwfQF.dblBudget9l),4) AS dblBudget9l " & sqlFrom & sqlWhere)
        gvRepQF.Columns(intBudgetColumn).FooterText = dr!dblBudget9l
        gvRepQF.Columns(intBudgetColumn - 1).FooterText = "Total:"
        gvRepQF.Columns(intBudgetColumn + 1).FooterText = " 9L"
      Catch ex As Exception
      End Try

      gvRepQF.Visible = True
      intDateColumn = gvRepQF.Columns.Count - 1       'onDataBound() if intDateColumn > -1
      gvRepQF.DataSourceID = "sdsRepQF"            'triggers onDataBound()
      ''gvRepQF.DataBind()		
      gvExport.DataSource = dt
      gvExport.DataBind()   'triggers onDataBound()


    End If


	End Sub

	Protected Sub btnExport_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnExport.Click
		sdsRepQF.SelectCommand = strGVFilter
		gvExport.Visible = True

		Response.Clear()
		Response.Buffer = True
		Response.ContentType = "application/vnd.ms-excel"
		Response.Charset = ""
      Response.AddHeader("Content-Disposition", _
       "attachment;filename=RepQFList_" & Session.Item("User").Replace(" ", "") & "_" & Date.Today.ToString("yyyyMMdd") & ".xls") 'v1.4.5 - 20160913 - Fixed logged In user & savings (i.e use session instead) - maanie
		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(gvExport)
		frm.RenderControl(oHtmlTextWriter)
		Response.Write(oStringWriter.ToString())

		gvExport.Visible = False

		Response.End()
	End Sub

	Protected Sub chkResize_CheckedChanged(ByVal sender As Object, ByVal e As EventArgs) Handles chkResize.CheckedChanged
		gvRepQF.PageIndex = 0
		dblTotal = 0
		sdsRepQF.SelectCommand = strGVFilter
		If chkResize.Checked Then
			gvRepQF.PageSize = WebConfigurationManager.AppSettings("MAXPAGESIZE")
			WebConfigurationManager.AppSettings("ShowMore") = True
		Else
			gvRepQF.PageSize = WebConfigurationManager.AppSettings("PAGESIZE")
			WebConfigurationManager.AppSettings("ShowMore") = False
		End If
	End Sub

	Protected Sub ddlPrincipal_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) Handles ddlPrincipal.SelectedIndexChanged
		sdsBrand.FilterExpression = "1=1"
		If ddlPrincipal.SelectedIndex > 0 Then sdsBrand.FilterExpression = "refPrincipalID = 0 OR refPrincipalID = " & ddlPrincipal.SelectedValue
	End Sub

	Protected Sub ddlBranch_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) Handles ddlBranch.SelectedIndexChanged
		sdsRep.FilterExpression = "1=1"
		If ddlBranch.SelectedIndex > 0 Then sdsRep.FilterExpression = "refBranchID = 0 OR refBranchID = " & ddlBranch.SelectedValue
	End Sub

	Protected Sub ddlStartDate_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) Handles ddlStartDate.SelectedIndexChanged, ddlEndDate.SelectedIndexChanged
		If ddlStartDate.SelectedValue > ddlEndDate.SelectedValue Then ddlEndDate.SelectedValue = ddlStartDate.SelectedValue
	End Sub
End Class