Imports CAMS_ADMIN Imports CAMS_BL Imports CAMS_REPORTS Imports System.Data Imports System.Text Public Class frmPrintQSummary Inherits CAMSBasePage #Region " Web Form Designer Generated Code " 'This call is required by the Web Form Designer. Private Sub InitializeComponent() End Sub Protected WithEvents lblPrintDate As System.Web.UI.WebControls.Label Protected WithEvents lblUser As System.Web.UI.WebControls.Label Protected WithEvents lblYears As System.Web.UI.WebControls.Label Protected WithEvents lblQuarter As System.Web.UI.WebControls.Label Protected WithEvents rpGrantTypes As System.Web.UI.WebControls.Repeater Protected WithEvents rpTableA As System.Web.UI.WebControls.Repeater Protected WithEvents rpTableB As System.Web.UI.WebControls.Repeater Protected WithEvents rpGCDetails As System.Web.UI.WebControls.Repeater Protected WithEvents lblTotalCostQ1 As System.Web.UI.WebControls.Label Protected WithEvents lblTotalCostQ2 As System.Web.UI.WebControls.Label Protected WithEvents lblTotalCostQ3 As System.Web.UI.WebControls.Label Protected WithEvents lblTotalCostQ4 As System.Web.UI.WebControls.Label Protected WithEvents rpPerVisitsComp As System.Web.UI.WebControls.Repeater 'NOTE: The following placeholder declaration is required by the Web Form Designer. 'Do not delete or move it. Private designerPlaceholderDeclaration As System.Object Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init 'CODEGEN: This method call is required by the Web Form Designer 'Do not modify it using the code editor. InitializeComponent() End Sub #End Region Private strDateFormat As String = "dd/MM/yyyy HH:mm" Private strSymbol As String Private strQuarter As String = "" Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load FillReport() Response.ContentType = "application/vnd.ms-excel" Response.Charset = "" Response.AppendHeader("content-disposition", "attachment; filename=QuarterlySummaryReport.xls") Me.EnableViewState = False End Sub Private Sub FillReport() Dim oUser As EC_User = Me.GetUserFromSession Dim strYearIni As String = "", strYearEnd As String = "" If Not Request.QueryString("yearini") Is Nothing Then strYearIni = Request.QueryString("yearini").Trim End If If Not Request.QueryString("yearend") Is Nothing Then strYearEnd = Request.QueryString("yearend").Trim End If If Not Request.QueryString("quarter") Is Nothing Then strQuarter = Request.QueryString("quarter").Trim End If If Not oUser Is Nothing Then lblUser.Text = oUser.Name & " " & oUser.Surname End If lblPrintDate.Text = Now.ToString(strDateFormat) lblYears.Text = strYearIni & " - " & strYearEnd strSymbol = ConfigurationSettings.AppSettings("Symbol") Select Case strQuarter Case "Q1" lblQuarter.Text = "Quarter 1: April " & strYearIni.Substring(2) & " - " & " June " & strYearIni.Substring(2) Case "Q2" lblQuarter.Text = "Quarter 2: July " & strYearIni.Substring(2) & " - " & " September " & strYearIni.Substring(2) Case "Q3" lblQuarter.Text = "Quarter 3: October " & strYearIni.Substring(2) & " - " & " December " & strYearIni.Substring(2) Case "Q4" lblQuarter.Text = "Quarter 4: January " & strYearEnd.Substring(2) & " - " & " March " & strYearEnd.Substring(2) Case "All" lblQuarter.Text = "All Quarters: April " & strYearIni.Substring(2) & " - " & " March " & strYearEnd.Substring(2) End Select FillTableA(strYearIni, strYearEnd, strQuarter) FillTableB(strYearIni, strYearEnd, strQuarter) FillGCDetails(strYearIni, strYearEnd, strQuarter) FillGrantTypes(strYearIni, strYearEnd, strQuarter) FillPerVisitsComp(strYearIni, strYearEnd, strQuarter) End Sub Private Sub FillGrantTypes(ByVal prmYearIni As String, ByVal prmYearEnd As String, ByVal prmQuarter As String) Dim oCC_QSumm As New CC_QuarterlySummary Dim oEC_Quarters As New EC_Quarters Dim dsResults As DataSet With oEC_Quarters Select Case prmQuarter Case "Q1" .Q1DateIni = "01/04/" & prmYearIni .Q1DateEnd = "30/06/" & prmYearIni Case "Q2" .Q2DateIni = "01/07/" & prmYearIni .Q2DateEnd = "30/09/" & prmYearIni Case "Q3" .Q3DateIni = "01/10/" & prmYearIni .Q3DateEnd = "31/12/" & prmYearIni Case "Q4" .Q4DateIni = "01/01/" & prmYearEnd .Q4DateEnd = "31/03/" & prmYearEnd Case "All" .Q1DateIni = "01/04/" & prmYearIni .Q1DateEnd = "30/06/" & prmYearIni .Q2DateIni = "01/07/" & prmYearIni .Q2DateEnd = "30/09/" & prmYearIni .Q3DateIni = "01/10/" & prmYearIni .Q3DateEnd = "31/12/" & prmYearIni .Q4DateIni = "01/01/" & prmYearEnd .Q4DateEnd = "31/03/" & prmYearEnd End Select End With Try dsResults = oCC_QSumm.GetGrantTypes(oEC_Quarters) Catch ex As Exception dsResults = Nothing Finally rpGrantTypes.DataSource = dsResults rpGrantTypes.DataBind() End Try End Sub Private Sub FillPerVisitsComp(ByVal prmYearIni As String, ByVal prmYearEnd As String, ByVal prmQuarter As String) Dim oCC_QSumm As New CC_QuarterlySummary Dim oEC_Quarters As New EC_Quarters Dim dsResults As DataSet With oEC_Quarters Select Case prmQuarter Case "Q1" .Q1DateIni = "01/04/" & prmYearIni .Q1DateEnd = "30/06/" & prmYearIni Case "Q2" .Q2DateIni = "01/07/" & prmYearIni .Q2DateEnd = "30/09/" & prmYearIni Case "Q3" .Q3DateIni = "01/10/" & prmYearIni .Q3DateEnd = "31/12/" & prmYearIni Case "Q4" .Q4DateIni = "01/01/" & prmYearEnd .Q4DateEnd = "31/03/" & prmYearEnd Case "All" .Q1DateIni = "01/04/" & prmYearIni .Q1DateEnd = "30/06/" & prmYearIni .Q2DateIni = "01/07/" & prmYearIni .Q2DateEnd = "30/09/" & prmYearIni .Q3DateIni = "01/10/" & prmYearIni .Q3DateEnd = "31/12/" & prmYearIni .Q4DateIni = "01/01/" & prmYearEnd .Q4DateEnd = "31/03/" & prmYearEnd End Select End With Try dsResults = oCC_QSumm.GetPercentageOfVisitsComp(oEC_Quarters) Catch ex As Exception dsResults = Nothing Finally rpPerVisitsComp.DataSource = dsResults rpPerVisitsComp.DataBind() End Try End Sub Private Sub FillTableA(ByVal prmYearIni As String, ByVal prmYearEnd As String, ByVal prmQuarter As String) Dim oCC_QSumm As New CC_QuarterlySummary Dim oEC_Quarters As New EC_Quarters Dim dsResults As DataSet With oEC_Quarters Select Case prmQuarter Case "Q1" .Q1DateIni = "01/04/" & prmYearIni .Q1DateEnd = "30/06/" & prmYearIni Case "Q2" .Q2DateIni = "01/07/" & prmYearIni .Q2DateEnd = "30/09/" & prmYearIni Case "Q3" .Q3DateIni = "01/10/" & prmYearIni .Q3DateEnd = "31/12/" & prmYearIni Case "Q4" .Q4DateIni = "01/01/" & prmYearEnd .Q4DateEnd = "31/03/" & prmYearEnd Case "All" .Q1DateIni = "01/04/" & prmYearIni .Q1DateEnd = "30/06/" & prmYearIni .Q2DateIni = "01/07/" & prmYearIni .Q2DateEnd = "30/09/" & prmYearIni .Q3DateIni = "01/10/" & prmYearIni .Q3DateEnd = "31/12/" & prmYearIni .Q4DateIni = "01/01/" & prmYearEnd .Q4DateEnd = "31/03/" & prmYearEnd End Select End With Try dsResults = oCC_QSumm.GetTableA(oEC_Quarters) Catch ex As Exception dsResults = Nothing Finally rpTableA.DataSource = dsResults rpTableA.DataBind() End Try End Sub Private Sub FillTableB(ByVal prmYearIni As String, ByVal prmYearEnd As String, ByVal prmQuarter As String) Dim oCC_QSumm As New CC_QuarterlySummary Dim oEC_Quarters As New EC_Quarters Dim dsResults As DataSet With oEC_Quarters Select Case prmQuarter Case "Q1" .Q1DateIni = "01/04/" & prmYearIni .Q1DateEnd = "30/06/" & prmYearIni Case "Q2" .Q2DateIni = "01/07/" & prmYearIni .Q2DateEnd = "30/09/" & prmYearIni Case "Q3" .Q3DateIni = "01/10/" & prmYearIni .Q3DateEnd = "31/12/" & prmYearIni Case "Q4" .Q4DateIni = "01/01/" & prmYearEnd .Q4DateEnd = "31/03/" & prmYearEnd Case "All" .Q1DateIni = "01/04/" & prmYearIni .Q1DateEnd = "30/06/" & prmYearIni .Q2DateIni = "01/07/" & prmYearIni .Q2DateEnd = "30/09/" & prmYearIni .Q3DateIni = "01/10/" & prmYearIni .Q3DateEnd = "31/12/" & prmYearIni .Q4DateIni = "01/01/" & prmYearEnd .Q4DateEnd = "31/03/" & prmYearEnd End Select End With Try dsResults = oCC_QSumm.GetTableB(oEC_Quarters) Catch ex As Exception dsResults = Nothing Finally rpTableB.DataSource = dsResults rpTableB.DataBind() End Try End Sub Private Sub FillGCDetails(ByVal prmYearIni As String, ByVal prmYearEnd As String, ByVal prmQuarter As String) Dim oCC_QSumm As New CC_QuarterlySummary Dim oEC_Quarters As New EC_Quarters Dim dsResults As DataSet With oEC_Quarters Select Case prmQuarter Case "Q1" .Q1DateIni = "01/04/" & prmYearIni .Q1DateEnd = "30/06/" & prmYearIni lblTotalCostQ1.Visible = True Case "Q2" .Q2DateIni = "01/07/" & prmYearIni .Q2DateEnd = "30/09/" & prmYearIni lblTotalCostQ2.Visible = True Case "Q3" .Q3DateIni = "01/10/" & prmYearIni .Q3DateEnd = "31/12/" & prmYearIni lblTotalCostQ3.Visible = True Case "Q4" .Q4DateIni = "01/01/" & prmYearEnd .Q4DateEnd = "31/03/" & prmYearEnd lblTotalCostQ4.Visible = True Case "All" .Q1DateIni = "01/04/" & prmYearIni .Q1DateEnd = "30/06/" & prmYearIni .Q2DateIni = "01/07/" & prmYearIni .Q2DateEnd = "30/09/" & prmYearIni .Q3DateIni = "01/10/" & prmYearIni .Q3DateEnd = "31/12/" & prmYearIni .Q4DateIni = "01/01/" & prmYearEnd .Q4DateEnd = "31/03/" & prmYearEnd lblTotalCostQ1.Visible = True lblTotalCostQ2.Visible = True lblTotalCostQ3.Visible = True lblTotalCostQ4.Visible = True End Select End With Try dsResults = oCC_QSumm.GetGCDetails(oEC_Quarters) If Not dsResults Is Nothing Then If dsResults.Tables(0).Rows.Count = 2 Then Dim dGrant As Decimal = 0 Dim dCost As Decimal = 0 Dim dTotal As Decimal = 0 Try dGrant = IIf(dsResults.Tables(0).Rows(0)(1) Is DBNull.Value, 0, dsResults.Tables(0).Rows(0)(1)) dCost = IIf(dsResults.Tables(0).Rows(1)(1) Is DBNull.Value, 0, dsResults.Tables(0).Rows(1)(1)) dTotal = (dGrant / dCost) * 100 Catch dTotal = 0 Finally lblTotalCostQ1.Text = dTotal.ToString("#,###.00") & "%" End Try Try dGrant = IIf(dsResults.Tables(0).Rows(0)(2) Is DBNull.Value, 0, dsResults.Tables(0).Rows(0)(2)) dCost = IIf(dsResults.Tables(0).Rows(1)(2) Is DBNull.Value, 0, dsResults.Tables(0).Rows(1)(2)) dTotal = (dGrant / dCost) * 100 Catch dTotal = 0 Finally lblTotalCostQ2.Text = dTotal.ToString("#,###.00") & "%" End Try Try dGrant = IIf(dsResults.Tables(0).Rows(0)(3) Is DBNull.Value, 0, dsResults.Tables(0).Rows(0)(3)) dCost = IIf(dsResults.Tables(0).Rows(1)(3) Is DBNull.Value, 0, dsResults.Tables(0).Rows(1)(3)) dTotal = (dGrant / dCost) * 100 Catch dTotal = 0 Finally lblTotalCostQ3.Text = dTotal.ToString("#,###.00") & "%" End Try Try dGrant = IIf(dsResults.Tables(0).Rows(0)(4) Is DBNull.Value, 0, dsResults.Tables(0).Rows(0)(4)) dCost = IIf(dsResults.Tables(0).Rows(1)(4) Is DBNull.Value, 0, dsResults.Tables(0).Rows(1)(4)) dTotal = (dGrant / dCost) * 100 Catch dTotal = 0 Finally lblTotalCostQ4.Text = dTotal.ToString("#,###.00") & "%" End Try End If End If Catch ex As Exception dsResults = Nothing Finally rpGCDetails.DataSource = dsResults rpGCDetails.DataBind() End Try End Sub Private Sub rpTableA_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.RepeaterItemEventArgs) Handles rpTableA.ItemDataBound Dim lblTableAQ1 As Label Dim lblTableAQ2 As Label Dim lblTableAQ3 As Label Dim lblTableAQ4 As Label Dim drv As DataRowView = e.Item.DataItem If Not drv Is Nothing Then lblTableAQ1 = CType(e.Item.FindControl("lblTableAQ1"), Label) If Not lblTableAQ1 Is Nothing Then lblTableAQ1.Text = drv("Q1").ToString End If lblTableAQ2 = CType(e.Item.FindControl("lblTableAQ2"), Label) If Not lblTableAQ2 Is Nothing Then lblTableAQ2.Text = drv("Q2").ToString End If lblTableAQ3 = CType(e.Item.FindControl("lblTableAQ3"), Label) If Not lblTableAQ3 Is Nothing Then lblTableAQ3.Text = drv("Q3").ToString End If lblTableAQ4 = CType(e.Item.FindControl("lblTableAQ4"), Label) If Not lblTableAQ4 Is Nothing Then lblTableAQ4.Text = drv("Q4").ToString End If Select Case strQuarter Case "Q1" lblTableAQ1.Visible = True Case "Q2" lblTableAQ2.Visible = True Case "Q3" lblTableAQ3.Visible = True Case "Q4" lblTableAQ4.Visible = True Case "All" lblTableAQ1.Visible = True lblTableAQ2.Visible = True lblTableAQ3.Visible = True lblTableAQ4.Visible = True End Select End If End Sub Private Sub rpTableB_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.RepeaterItemEventArgs) Handles rpTableB.ItemDataBound Dim lblTableBQ1 As Label Dim lblTableBQ2 As Label Dim lblTableBQ3 As Label Dim lblTableBQ4 As Label Dim drv As DataRowView = e.Item.DataItem If Not drv Is Nothing Then lblTableBQ1 = CType(e.Item.FindControl("lblTableBQ1"), Label) If Not lblTableBQ1 Is Nothing Then lblTableBQ1.Text = drv("Q1").ToString End If lblTableBQ2 = CType(e.Item.FindControl("lblTableBQ2"), Label) If Not lblTableBQ2 Is Nothing Then lblTableBQ2.Text = drv("Q2").ToString End If lblTableBQ3 = CType(e.Item.FindControl("lblTableBQ3"), Label) If Not lblTableBQ3 Is Nothing Then lblTableBQ3.Text = drv("Q3").ToString End If lblTableBQ4 = CType(e.Item.FindControl("lblTableBQ4"), Label) If Not lblTableBQ4 Is Nothing Then lblTableBQ4.Text = drv("Q4").ToString End If Select Case strQuarter Case "Q1" lblTableBQ1.Visible = True Case "Q2" lblTableBQ2.Visible = True Case "Q3" lblTableBQ3.Visible = True Case "Q4" lblTableBQ4.Visible = True Case "All" lblTableBQ1.Visible = True lblTableBQ2.Visible = True lblTableBQ3.Visible = True lblTableBQ4.Visible = True End Select End If End Sub Private Sub rpGrantTypes_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.RepeaterItemEventArgs) Handles rpGrantTypes.ItemDataBound Dim lblGTQ1 As Label Dim lblGTQ2 As Label Dim lblGTQ3 As Label Dim lblGTQ4 As Label Dim drv As DataRowView = e.Item.DataItem If Not drv Is Nothing Then lblGTQ1 = CType(e.Item.FindControl("lblGTQ1"), Label) If Not lblGTQ1 Is Nothing Then lblGTQ1.Text = drv("Q1").ToString End If lblGTQ2 = CType(e.Item.FindControl("lblGTQ2"), Label) If Not lblGTQ2 Is Nothing Then lblGTQ2.Text = drv("Q2").ToString End If lblGTQ3 = CType(e.Item.FindControl("lblGTQ3"), Label) If Not lblGTQ3 Is Nothing Then lblGTQ3.Text = drv("Q3").ToString End If lblGTQ4 = CType(e.Item.FindControl("lblGTQ4"), Label) If Not lblGTQ4 Is Nothing Then lblGTQ4.Text = drv("Q4").ToString End If Select Case strQuarter Case "Q1" lblGTQ1.Visible = True Case "Q2" lblGTQ2.Visible = True Case "Q3" lblGTQ3.Visible = True Case "Q4" lblGTQ4.Visible = True Case "All" lblGTQ1.Visible = True lblGTQ2.Visible = True lblGTQ3.Visible = True lblGTQ4.Visible = True End Select End If End Sub Private Sub rpGCDetails_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.RepeaterItemEventArgs) Handles rpGCDetails.ItemDataBound Dim lblGCDQ1 As Label Dim lblGCDQ2 As Label Dim lblGCDQ3 As Label Dim lblGCDQ4 As Label Dim drv As DataRowView = e.Item.DataItem If Not drv Is Nothing Then lblGCDQ1 = CType(e.Item.FindControl("lblGCDQ1"), Label) If Not lblGCDQ1 Is Nothing Then lblGCDQ1.Text = String.Format("{0:#,###.00}", drv("Q1")) End If lblGCDQ2 = CType(e.Item.FindControl("lblGCDQ2"), Label) If Not lblGCDQ2 Is Nothing Then lblGCDQ2.Text = String.Format("{0:#,###.00}", drv("Q2")) End If lblGCDQ3 = CType(e.Item.FindControl("lblGCDQ3"), Label) If Not lblGCDQ3 Is Nothing Then lblGCDQ3.Text = String.Format("{0:#,###.00}", drv("Q3")) End If lblGCDQ4 = CType(e.Item.FindControl("lblGCDQ4"), Label) If Not lblGCDQ4 Is Nothing Then lblGCDQ4.Text = String.Format("{0:#,###.00}", drv("Q4")) End If Select Case strQuarter Case "Q1" lblGCDQ1.Visible = True Case "Q2" lblGCDQ2.Visible = True Case "Q3" lblGCDQ3.Visible = True Case "Q4" lblGCDQ4.Visible = True Case "All" lblGCDQ1.Visible = True lblGCDQ2.Visible = True lblGCDQ3.Visible = True lblGCDQ4.Visible = True End Select End If End Sub Private Sub rpPerVisitsComp_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.RepeaterItemEventArgs) Handles rpPerVisitsComp.ItemDataBound Dim lblPVCQ1 As Label Dim lblPVCQ2 As Label Dim lblPVCQ3 As Label Dim lblPVCQ4 As Label Dim drv As DataRowView = e.Item.DataItem If Not drv Is Nothing Then lblPVCQ1 = CType(e.Item.FindControl("lblPVCQ1"), Label) If Not lblPVCQ1 Is Nothing Then lblPVCQ1.Text = String.Format("{0:#,###.00}", drv("Q1")) & "%" End If lblPVCQ2 = CType(e.Item.FindControl("lblPVCQ2"), Label) If Not lblPVCQ2 Is Nothing Then lblPVCQ2.Text = String.Format("{0:#,###.00}", drv("Q2")) & "%" End If lblPVCQ3 = CType(e.Item.FindControl("lblPVCQ3"), Label) If Not lblPVCQ3 Is Nothing Then lblPVCQ3.Text = String.Format("{0:#,###.00}", drv("Q3")) & "%" End If lblPVCQ4 = CType(e.Item.FindControl("lblPVCQ4"), Label) If Not lblPVCQ4 Is Nothing Then lblPVCQ4.Text = String.Format("{0:#,###.00}", drv("Q4")) & "%" End If Select Case strQuarter Case "Q1" lblPVCQ1.Visible = True Case "Q2" lblPVCQ2.Visible = True Case "Q3" lblPVCQ3.Visible = True Case "Q4" lblPVCQ4.Visible = True Case "All" lblPVCQ1.Visible = True lblPVCQ2.Visible = True lblPVCQ3.Visible = True lblPVCQ4.Visible = True End Select End If End Sub End Class