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();
}
}
}
