protected void btnExport_Click(object sender, EventArgs e) { SQLDatabase sql = new SQLDatabase(); sql.CommandTimeout = 120; SQLParamList sqlParams = Master.GetFilters(); sql.CommandTimeout = 120; DataTable dt = sql.ExecStoredProcedureDataTable("spReports_KeyDriver", sqlParams); if (sql.HasError) { TopMessage.ErrorMessage = "Oops. Something went wrong when generating the data. Please try again. (EKD100)"; } else { using (ExcelPackage p = new ExcelPackage()) { p.Workbook.Worksheets.Add("Top 2 Box"); p.Workbook.Worksheets.Add("Correlation"); p.Workbook.Worksheets.Add("Room for Improvement"); ExcelWorksheet wsT2B = p.Workbook.Worksheets[1]; ExcelWorksheet wsCorr = p.Workbook.Worksheets[2]; ExcelWorksheet wsR4I = p.Workbook.Worksheets[3]; wsT2B.Cells.Style.Font.Size = 11; //Default font size for whole sheet wsT2B.Cells.Style.Font.Name = "Calibri"; //Default Font name for whole sheet wsCorr.Cells.Style.Font.Size = 11; //Default font size for whole sheet wsCorr.Cells.Style.Font.Name = "Calibri"; //Default Font name for whole sheet wsR4I.Cells.Style.Font.Size = 11; //Default font size for whole sheet wsR4I.Cells.Style.Font.Name = "Calibri"; //Default Font name for whole sheet DateRangeFilterControl drDateRange = Master.GetFilter <DateRangeFilterControl>("fltDateRange"); ReportFilterListBox ddlProperty = Master.GetFilter <ReportFilterListBox>("fltProperty"); ReportFilterListBox ddlAgeRange = Master.GetFilter <ReportFilterListBox>("fltAgeRange"); ReportFilterListBox ddlGender = Master.GetFilter <ReportFilterListBox>("fltGender"); ReportFilterListBox ddlTenure = Master.GetFilter <ReportFilterListBox>("fltTenure"); ReportFilterListBox ddlTier = Master.GetFilter <ReportFilterListBox>("fltTier"); wsT2B.Cells[1, 1].Value = "Start Date: " + drDateRange.BeginDate.Value.ToString("yyyy-MM-dd"); wsT2B.Cells[2, 1].Value = "End Date: " + drDateRange.EndDate.Value.ToString("yyyy-MM-dd"); wsT2B.Cells[3, 1].Value = GetFilterDetails("Properties: ", ddlProperty.Items); wsT2B.Cells[4, 1].Value = GetFilterDetails("Age Range: ", ddlAgeRange.Items); wsT2B.Cells[5, 1].Value = GetFilterDetails("Gender: ", ddlGender.Items); wsT2B.Cells[6, 1].Value = GetFilterDetails("Tenure: ", ddlTenure.Items); wsT2B.Cells[7, 1].Value = GetFilterDetails("Tier: ", ddlTier.Items); int rowNum = DATA_ROW_START; int altRowOffset = 1 - DATA_ROW_START; //Set the header styles wsT2B.Row(rowNum).Style.Font.Bold = true; wsT2B.Row(rowNum).Style.Font.Size = 12; wsCorr.Row(rowNum + altRowOffset).Style.Font.Bold = true; wsCorr.Row(rowNum + altRowOffset).Style.Font.Size = 12; wsR4I.Row(rowNum + altRowOffset).Style.Font.Bold = true; wsR4I.Row(rowNum + altRowOffset).Style.Font.Size = 12; AddValue(wsT2B, rowNum, 1, "Question", null); AddValue(wsT2B, rowNum, 2, "Section", null); AddValue(wsT2B, rowNum, 3, "Label", null); AddValue(wsT2B, rowNum, 4, "Top 2 Box", null); AddValue(wsT2B, rowNum, 5, "Average", null); AddValue(wsCorr, rowNum + altRowOffset, 1, "Question", null); AddValue(wsCorr, rowNum + altRowOffset, 2, "Label", null); AddValue(wsCorr, rowNum + altRowOffset, 3, "Correlation", null); AddValue(wsR4I, rowNum + altRowOffset, 1, "Question", null); AddValue(wsR4I, rowNum + altRowOffset, 2, "Label", null); AddValue(wsR4I, rowNum + altRowOffset, 3, "Room for Improvement", null); rowNum++; DataRow dr = dt.Rows[0]; string lastSection = String.Empty; List <Tuple <string, string, object> > corrRows = new List <Tuple <string, string, object> >(); List <Tuple <string, string, object> > r4iRows = new List <Tuple <string, string, object> >(); foreach (DataColumn dc in dt.Columns) { string[] part1 = dc.ColumnName.Split('_'); //Check and make sure we haven't done these (they're done with the correlation columns). if (part1.Length < 2 || part1[1].Equals("T2B") || part1[1].Equals("R4I")) { continue; } string[] part2 = part1[1].Split(new string[] { " - " }, 2, StringSplitOptions.None); string dbColumn = part1[0]; string section = part2[0]; string label = part2[1]; //T2B scores AddValue(wsT2B, rowNum, 1, dbColumn, null); //Skip section values so the multi-level category labels will show up correctly if (section != lastSection) { AddValue(wsT2B, rowNum, 2, section, null); lastSection = section; } AddValue(wsT2B, rowNum, 3, label, null); AddValue(wsT2B, rowNum, 4, dr[dbColumn + "_T2B"], r => { r.Style.Numberformat.Format = "0.0%"; }); AddValue(wsT2B, rowNum, 5, null, r => { r.Formula = "AVERAGE(D:D)"; }); //Correlation scores corrRows.Add(new Tuple <string, string, object>(dbColumn, part1[1], dr[dc.ColumnName])); //Room for Improvement r4iRows.Add(new Tuple <string, string, object>(dbColumn, part1[1], dr[dbColumn + "_R4I"])); rowNum++; } //Add sorted items to correlation sheet rowNum = DATA_ROW_START + 1; foreach (var rowDeets in corrRows.OrderByDescending(r => { return(r.Item3.ToString()); })) { AddValue(wsCorr, rowNum + altRowOffset, 1, rowDeets.Item1, null); AddValue(wsCorr, rowNum + altRowOffset, 2, rowDeets.Item2, null); AddValue(wsCorr, rowNum + altRowOffset, 3, rowDeets.Item3, null); rowNum++; } //Add sorted items to room for improvement sheet rowNum = DATA_ROW_START + 1; foreach (var rowDeets in r4iRows.OrderByDescending(r => { return(r.Item3.ToString()); })) { AddValue(wsR4I, rowNum + altRowOffset, 1, rowDeets.Item1, null); AddValue(wsR4I, rowNum + altRowOffset, 2, rowDeets.Item2, null); AddValue(wsR4I, rowNum + altRowOffset, 3, rowDeets.Item3, null); rowNum++; } rowNum--; //Set to last row //Set up T2B sheet //Auto fit the columns wsT2B.Cells[DATA_ROW_START, 1, rowNum, 4].AutoFitColumns(); double rowWidth = 0; for (int i = 1; i <= 5; i++) { rowWidth += wsT2B.Column(i).Width; } rowWidth /= 0.1423; rowWidth += 20; //Extra padding //Add charts var t2bChart = wsT2B.Drawings.AddChart("T2BChart", eChartType.ColumnClustered); //Set position and size t2bChart.SetPosition(DATA_ROW_START * 20, (int)rowWidth); t2bChart.SetSize(1200, 800); t2bChart.Title.Text = "Top 2 Box Scores"; t2bChart.XAxis.LabelPosition = eTickLabelPosition.NextTo; t2bChart.XAxis.MajorTickMark = eAxisTickMark.None; t2bChart.XAxis.MinorTickMark = eAxisTickMark.Out; t2bChart.YAxis.MaxValue = 1; t2bChart.YAxis.MinValue = 0; t2bChart.YAxis.MinorTickMark = eAxisTickMark.None; //Add main series. var series = t2bChart.Series.Add(wsT2B.Cells[DATA_ROW_START + 1, 4, rowNum, 4], wsT2B.Cells[DATA_ROW_START + 1, 3, rowNum, 3]); series.Header = "Top 2 Box Score"; //Hide the average column wsT2B.Column(5).Hidden = true; using (ExcelRange r = wsT2B.Cells[DATA_ROW_START + 1, 5, rowNum - 1, 5]) { r.Calculate(); } var lineChart = t2bChart.PlotArea.ChartTypes.Add(eChartType.Line); var avgT2BSeries = lineChart.Series.Add(wsT2B.Cells[DATA_ROW_START + 1, 5, rowNum, 5], wsT2B.Cells[DATA_ROW_START + 1, 3, rowNum, 3]); avgT2BSeries.Header = "Average Top 2"; lineChart.ShowHiddenData = true; //Set up Correlation sheet //Auto fit the columns wsCorr.Cells[1, 1, rowNum + altRowOffset, 3].AutoFitColumns(); rowWidth = 0; for (int i = 1; i <= 3; i++) { rowWidth += wsCorr.Column(i).Width; } rowWidth /= 0.1423; rowWidth += 40; //Extra padding //Add charts var corrChart = wsCorr.Drawings.AddChart("CorrelationChart", eChartType.ColumnClustered); //Set position and size corrChart.SetPosition(20, (int)rowWidth); corrChart.SetSize(1200, 800); corrChart.Title.Text = "GEI Correlation Rankings"; corrChart.XAxis.LabelPosition = eTickLabelPosition.NextTo; corrChart.XAxis.MajorTickMark = eAxisTickMark.None; corrChart.XAxis.MinorTickMark = eAxisTickMark.Out; corrChart.YAxis.MinorTickMark = eAxisTickMark.None; //Add main series. series = corrChart.Series.Add(wsCorr.Cells[2, 3, rowNum + altRowOffset, 3], wsCorr.Cells[2, 2, rowNum + altRowOffset, 2]); series.Header = "Correlation Value"; //Set up Room for Improvement sheet //Auto fit the columns wsR4I.Cells[1, 1, rowNum + altRowOffset, 3].AutoFitColumns(); rowWidth = 0; for (int i = 1; i <= 3; i++) { rowWidth += wsR4I.Column(i).Width; } rowWidth /= 0.1423; rowWidth += 40; //Extra padding //Add charts var r4iChart = wsR4I.Drawings.AddChart("CorrelationChart", eChartType.ColumnClustered); //Set position and size r4iChart.SetPosition(20, (int)rowWidth); r4iChart.SetSize(1200, 800); r4iChart.Title.Text = "Room / Need for Improvement"; r4iChart.XAxis.LabelPosition = eTickLabelPosition.NextTo; r4iChart.XAxis.MajorTickMark = eAxisTickMark.None; r4iChart.XAxis.MinorTickMark = eAxisTickMark.Out; r4iChart.YAxis.MinorTickMark = eAxisTickMark.None; //Add main series. series = r4iChart.Series.Add(wsR4I.Cells[2, 3, rowNum + altRowOffset, 3], wsR4I.Cells[2, 2, rowNum + altRowOffset, 2]); series.Header = "Room For Improvement"; string lFileName = string.Format("KeyDriverAnalysis-{0}.xlsx", ReportingTools.AdjustAndDisplayDate(DateTime.Now, "yyyy-MM-dd-hh-mm-ss-fff", User)); const string lPath = "~/Files/Cache/"; string lOutput = string.Concat(MapPath(lPath), lFileName); FileInfo fi = new FileInfo(lOutput); p.SaveAs(fi); hlDownload.Text = "Download File - " + lFileName; hlDownload.NavigateUrl = String.Format("/Files/Cache/{0}", lFileName); } } }
protected void Page_Init(object sender, EventArgs e) { fltDateRange.User = User; Filters = new IReportFilter[] { fltDateRange, fltRegion, fltProperty, fltSurveyType, fltBusinessUnit, fltSource, fltStatus, fltFeedbackAge, fltFeedbackTier, fltFBVenue, fltEncoreNumber, fltPlayerEmail, fltAgeRange, fltGender, fltLanguage, fltVisits, fltSegments, fltTenure, fltTier, fltTextSearch }; if (IsPropertyUser) { fltProperty.OnClear = fltProperty_OnClear; fltProperty.SelectedIndexChanged += fltProperty_SelectedIndexChanged; fltProperty.SelectedValue = ((int)User.Property).ToString(); } //if (IsMultiPropertyUser) //{ // List<GCCProperty> myprop = new List<GCCProperty>(); // myprop.Add(GCCProperty.CasinoNewBrunswick); // fltProperty.Items.Clear(); // fltProperty.DataSource = myprop; // fltProperty.DataBind(); // ////fltProperty.OnClear = fltProperty_OnClear; // //List<PropData> data = new List<PropData>(); // //data.Add(new PropData() { Value = 2, Text = "River Rock Casino Resort" }); // //data.Add(new PropData() { Value = 3, Text = "Hard Rock Casino Vancouver" }); // //fltProperty.DataSource = data; // //fltProperty.DataMember = "Text"; // //fltProperty.DataBind(); // //fltProperty.SelectedIndexChanged += fltProperty_SelectedIndexChanged; // //fltProperty.SelectedValue = ((int)User.Property).ToString(); //} //Set filters from query string int sf = RequestVars.Get("sf", 0); if (sf == 1) { foreach (IReportFilter flt in Filters) { string getVal = RequestVars.Get <string>(flt.SessionKey, null); if (!String.IsNullOrWhiteSpace(getVal)) { ReportFilterListBox rflb = flt as ReportFilterListBox; if (rflb != null) { string[] vals = getVal.Split(','); foreach (ListItem li in rflb.Items) { if (vals.Contains(li.Value)) { li.Selected = true; } } flt.Save(); continue; } ReportFilterTextBox rftb = flt as ReportFilterTextBox; if (rftb != null) { rftb.Text = getVal; flt.Save(); continue; } DateRangeFilterControl drfc = flt as DateRangeFilterControl; if (drfc != null) { string[] vals = getVal.Split(','); if (vals.Length == 2) { drfc.SetValues(vals[0], vals[1]); flt.Save(); continue; } } ReportFilterDropDownList rfddl = flt as ReportFilterDropDownList; if (rfddl != null) { rfddl.SelectedValue = getVal; flt.Save(); continue; } } } //Remove the query string and refresh Response.Redirect(Request.Path); } string rm = RequestVars.Get("rm", String.Empty); if (!String.IsNullOrEmpty(rm)) { foreach (IReportFilter flt in Filters) { if (flt.ID.Equals(rm)) { flt.Clear(); break; } } Response.Redirect(Request.Path); } // Jan 2017 Date range was default from 2016-12-01 to Jan 2017 but data was too much to load so resetting it to Jan 01,2017 to current date //if (DateTime.Today.Month == 1) //{ // fltDateRange.DefaultBeginDate = DateTime.Now.AddDays(-DateTime.Now.AddMonths(-1).Day + 1).Date; //} //else //{ // connection timeout set to = 120 in Feddbacklist Page load complete //fltDateRange.DefaultBeginDate = DateTime.Now.AddMonths(-1).AddDays(-DateTime.Now.AddMonths(-1).Day + 1).Date; // } fltDateRange.DefaultBeginDate = DateTime.Now.AddMonths(-1).AddDays(-DateTime.Now.AddMonths(-1).Day + 1).Date; // For Archived Portal setting up from Jan to Dec 2016 //fltDateRange.DefaultBeginDate = new DateTime(2016, 01, 01, 00, 00, 01); ; fltDateRange.DefaultEndDate = DateTime.Now; }
protected void Page_LoadComplete(object sender, EventArgs e) { DateRangeFilterControl dr = Master.Filters[0] as DateRangeFilterControl; if (dr != null) { bool change = false; if (dr.BeginDate.HasValue && dr.EndDate.HasValue) { TimeSpan ts = dr.EndDate.Value - dr.BeginDate.Value; if (ts.TotalDays < 90 || ts.TotalDays > 90) { change = true; } } else { change = true; } if (change) { dr.BeginDate = DateTime.Now.AddMonths(-5).AddDays(-DateTime.Now.AddMonths(-1).Day + 1).Date; dr.EndDate = DateTime.Now.Date; dr.Save(); TopMessage.WarningMessage = "In order to show more meaningful data, the date filter has been updated to show the past six months."; } } SQLDatabase sql = new SQLDatabase(); sql.CommandTimeout = 120; SQLParamList sqlParams = Master.GetFilters(); if (Master.IsPropertyUser) { SQLParamList sp2 = new SQLParamList(); foreach (SqlParameter sp in sqlParams.ToArray()) { if (sp.ParameterName.Equals("@Property")) { sp2.Add("@PropertyID", (int)User.PropertyShortCode); } else { sp2.Add(sp); } } sqlParams = sp2; } else { sqlParams.Add("@PropertyID", (int)PropertyShortCode); } DataSet ds = sql.ExecStoredProcedureDataSet("[spReports_PropertyDashboard]", sqlParams); if (!sql.HasError) { Data = ds; if (Data.Tables.Count > DATA_CURRENT_STATS) { StatRow = Data.Tables[DATA_CURRENT_STATS].Rows[0]; } if (Data.Tables[DATA_TOTAL].Rows.Count > 0) { Master.RecordCount = ds.Tables[DATA_TOTAL].Rows[0]["TotalRecordsForFilter"].ToString(); } } }