protected string GetDataRow(string label, string colPrefix, DataRow currentRow, DataRow lastMonthRow, DataRow last12MonthsRow)
{
StringBuilder sb = new StringBuilder("<tr><th>");
sb.Append(label)
.Append("</th>");
double currentVal = currentRow[colPrefix + "Score"].ToString().StringToDbl(-1000);
double lastMonthVal = lastMonthRow[colPrefix + "Score"].ToString().StringToDbl(-1000);
double last12MonthsVal = last12MonthsRow[colPrefix + "Score"].ToString().StringToDbl(-1000);
sb.AppendFormat("<td>{0}</td>", ReportingTools.FormatPercent(currentRow[colPrefix + "Score"].ToString()));
sb.AppendFormat("<td>{0}</td>", ReportingTools.FormatPercent(lastMonthRow[colPrefix + "Score"].ToString()));
if (currentVal == -1000 || lastMonthVal == -1000)
{
sb.Append("<td>-</td>");
}
else
{
sb.AppendFormat("<td>{0}</td>", ReportingTools.FormatPercent((currentVal - lastMonthVal).ToString()));
}
sb.AppendFormat("<td>{0}</td>", ReportingTools.FormatPercent(last12MonthsRow[colPrefix + "Score"].ToString()));
if (currentVal == -1000 || last12MonthsVal == -1000)
{
sb.Append("<td>-</td>");
}
else
{
sb.AppendFormat("<td>{0}</td>", ReportingTools.FormatPercent((currentVal - last12MonthsVal).ToString()));
}
sb.Append("</tr>");
return(sb.ToString());
}
protected void Page_LoadComplete(object sender, EventArgs e)
{
SQLDatabase sql = new SQLDatabase(); sql.CommandTimeout = 120;
SQLParamList sqlParams = new SQLParamList();
var fltProperty = Master.GetFilter <ReportFilterListBox>("fltProperty");
fltProperty.AddToQuery(sqlParams);
DataTable dt = sql.ExecStoredProcedureDataTable("[spReports_SnapshotStatus]", sqlParams);
if (!sql.HasError)
{
Data = dt;
if (_runExport)
{
string fileName = String.Format("SnapshotStatus-{0}.csv", ReportingTools.AdjustAndDisplayDate(DateTime.Now, "yyyy-MM-dd-hh-mm-ss", User));
string fullPath = Server.MapPath(Path.Combine(Config.CacheFileDirectory, fileName));
if (Data.DataTableToCSV(fullPath, true))
{
Response.Clear();
Response.ContentType = "text/csv";
Response.AddHeader("content-disposition", String.Format(@"attachment;filename=""{0}""", fileName));
Response.WriteFile(fullPath);
Response.End();
}
}
}
}
protected void btnExport_Click(object sender, EventArgs e)
{
string[] dateDetails = ddlMonthYear.SelectedValue.Split('-');
SQLDatabase sql = new SQLDatabase(); sql.CommandTimeout = 120;
SQLParamList sqlParams = new SQLParamList()
.Add("@Year", dateDetails[0].StringToInt())
.Add("@Month", dateDetails[1].StringToInt());
SqlParameter isNewParam = new SqlParameter("@IsNew", SqlDbType.Bit);
isNewParam.Direction = ParameterDirection.Output;
sqlParams.Add(isNewParam);
DataTable dt = sql.ExecStoredProcedureDataTable("spAdmin_GetGEIWinners", sqlParams);
if (sql.HasError)
{
TopMessage.ErrorMessage = "Unable to query the details from the database.";
}
else
{
string fileName = String.Format("WinnerSelection-{0}.csv", ReportingTools.AdjustAndDisplayDate(DateTime.Now, "yyyy-MM-dd-hh-mm-ss", User));
string fullPath = Server.MapPath(Path.Combine(Config.CacheFileDirectory, fileName));
if (dt.DataTableToCSV(fullPath, true))
{
Response.Clear();
Response.ContentType = "text/csv";
Response.AddHeader("content-disposition", String.Format(@"attachment;filename=""{0}""", fileName));
Response.WriteFile(fullPath);
Response.End();
}
}
}
protected string GetPercentage(string value, string total)
{
double val = Conversion.StringToDbl(value);
double ttl = Conversion.StringToDbl(total);
if (ttl == 0)
{
return("-");
}
else
{
return(ReportingTools.FormatPercent(val / ttl, 1));
}
}
protected void btnExport_Click(object sender, EventArgs e)
{
if (Data != null)
{
string fileName = String.Format("UserExport-{0}.csv", ReportingTools.AdjustAndDisplayDate(DateTime.Now, "yyyy-MM-dd-hh-mm-ss", User));
string fullPath = Server.MapPath(Path.Combine(Config.CacheFileDirectory, fileName));
if (!Data.DataTableToCSV(fullPath, true, 16))
{
TopMessage.ErrorMessage = "Unable to export the user list. Please try again.";
}
else
{
Response.Clear();
Response.ContentType = "text/csv";
Response.AddHeader("content-disposition", String.Format(@"attachment;filename=""{0}""", fileName));
Response.WriteFile(fullPath);
Response.End();
}
}
}
public TestManager()
{
_report = new ReportingTools("QA Simulator");
ExcelWriter excelWriter = new ExcelWriter(_report.getFileLocation());
DateTime start = _report.getNow();
originStart = start;
List <Action> tests = new List <Action>();
tests.Add(Test_CreateNewClient);
tests.Add(Test_AddNewVisitPlan);
tests.Add(Test_SetProvider);
tests.Add(Test_CompleteScheduledVisit);
foreach (Action test in tests)
{
_report.createReportItem("");
start = _report.getNow();
_report.createReportItem(test.Method.Name);
try {
createSUT();
test();
_report.createReportItem($"{test.Method.Name}", start);
} catch (Exception e) {
ScreenCapturer.CaptureAndSave($@"S:\Dev_IMS\Development\Jordan\uiAutomation\systemMapping\%NOW%_{test.Method.Name}", CaptureMode.Screen);
_report.createReportItem($"Test failure {test.Method.Name}", start, $"Target: {e.TargetSite} Source: {e.Source} Message: {e.Message}");
excelWriter.WriteToFile(_report.getReport());
ShutDownSystemUnderTest close = new ShutDownSystemUnderTest(_application);
return;
}
}
_report.createReportItem("");
_report.createReportItem("Test Set Complete", originStart);
excelWriter.WriteToFile(_report.getReport());
}
protected void btnExport_Click(object sender, EventArgs e)
{
SQLDatabase sql = new SQLDatabase(); sql.CommandTimeout = 120;
sql.CommandTimeout = 90;
DataTable dt = sql.ExecStoredProcedureDataTable("spReports_Monthly_Hotel",
new SqlParameter("@MonthStart", ddlMonth.SelectedValue + "-01"),
new SqlParameter("@PropertyID", ddlProperty.SelectedValue));
if (sql.HasError)
{
TopMessage.ErrorMessage = "Unable to query report data from the database.";
return;
}
using (ExcelPackage p = new ExcelPackage())
{
string[] date = ddlMonth.SelectedValue.Split('-');
DateTime mon = new DateTime(date[0].StringToInt(2017), date[1].StringToInt(1), 1);
GCCPropertyShortCode sc = (GCCPropertyShortCode)ddlProperty.SelectedValue.StringToInt(0);
p.Workbook.Worksheets.Add(PropertyTools.GetCasinoName((int)sc));
ExcelWorksheet worksheet = p.Workbook.Worksheets[1];
worksheet.Cells.Style.Font.Size = 10; //Default font size for whole sheet
worksheet.Cells.Style.Font.Name = "Calibri"; //Default Font name for whole sheet
worksheet.Cells.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
worksheet.Cells.Style.Fill.BackgroundColor.SetColor(Color.White);
worksheet.Column(1).Width = 12.71f;
worksheet.Column(2).Width = 54.85f;
worksheet.Column(3).Width = 10f;
worksheet.Column(4).Width = 15.42f;
worksheet.Column(5).Width = 14.57f;
worksheet.Column(6).Width = 19.28f;
worksheet.Column(7).Width = 19.28f;
worksheet.Column(8).Width = 4.14f;
worksheet.Column(9).Width = 10.71f;
worksheet.Column(10).Width = 10.71f;
worksheet.Column(11).Width = 10.71f;
worksheet.Column(12).Width = 10.71f;;
//Sheet title
worksheet.Cells["A1"].Value = PropertyTools.GetCasinoName((int)sc).ToUpper() + " HOTEL GUEST EXPERIENCE REPORT";
using (ExcelRange r = worksheet.Cells["A1:C2"])
{
r.Merge = true;
r.Style.Font.Size = 14;
r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.CenterContinuous;
r.Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Bottom;
}
worksheet.Cells["A3"].Value = String.Format("REPORTING PERIOD: {0}", mon.ToString("MMMM, yyyy"));
using (ExcelRange r = worksheet.Cells["A3:B8"])
{
r.Merge = true;
r.Style.Font.Size = 14;
r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.CenterContinuous;
r.Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Top;
}
AddMergedCell(worksheet, "C7", "D7", "Green: +10% change or more", r => r.Style.Font.Bold = true);
AddMergedCell(worksheet, "E7", "F7", "Red: -10% change or more", r => r.Style.Font.Bold = true);
float titleFontSize = 10f;
AddMergedCell(worksheet, "C9", "C10", "CURRENT PERIOD", r =>
{
r.Style.WrapText = true;
r.Style.Font.Bold = true;
r.Style.Font.Size = titleFontSize;
r.Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
r.Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
r.Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
});
AddMergedCell(worksheet, "D9", "G9", "VARIANCE FROM", r =>
{
r.Style.Font.Bold = true;
r.Style.Font.Size = titleFontSize;
r.Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);
r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
});
AddMergedCell(worksheet, "I9", "L9", "PERFORMANCE " + (mon.AddYears(-1)).ToString("yyyy"), r =>
{
r.Style.Font.Bold = true;
r.Style.Font.Size = titleFontSize;
r.Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);
r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
});
AddMergedCell(worksheet, "A10", "B10", "Total Sample:", r =>
{
r.Style.Font.Bold = true;
r.Style.Font.Size = titleFontSize;
r.Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Right;
});
using (ExcelRange r = worksheet.Cells["D10"])
{
r.Value = "PREVIOUS PERIOD";
r.Style.Font.Size = titleFontSize;
r.Style.Font.Bold = true;
r.Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
}
using (ExcelRange r = worksheet.Cells["E10"])
{
r.Value = "M/M Change";
r.Style.Font.Size = titleFontSize;
r.Style.Font.Bold = true;
r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
}
using (ExcelRange r = worksheet.Cells["F10"]) {
r.Value = "Last 12 Mo";
r.Style.Font.Size = titleFontSize;
r.Style.Font.Bold = true;
r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
}
using (ExcelRange r = worksheet.Cells["G10"]) {
r.Value = "Change from L12M";
r.Style.Font.Size = titleFontSize;
r.Style.Font.Bold = true;
r.Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
}
using (ExcelRange r = worksheet.Cells["I10"])
{
r.Value = "Q1/FY" + (mon.AddYears(-1)).ToString("yy");
r.Style.Font.Size = titleFontSize;
r.Style.Font.Bold = true;
r.Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
}
using (ExcelRange r = worksheet.Cells["J10"])
{
r.Value = "Q2/FY" + (mon.AddYears(-1)).ToString("yy");
r.Style.Font.Size = titleFontSize;
r.Style.Font.Bold = true;
r.Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
}
using (ExcelRange r = worksheet.Cells["K10"])
{
r.Value = "Q3/FY" + (mon.AddYears(-1)).ToString("yy");
r.Style.Font.Size = titleFontSize;
r.Style.Font.Bold = true;
r.Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
}
using (ExcelRange r = worksheet.Cells["L10"])
{
r.Value = "Q4/FY" + (mon.AddYears(-1)).ToString("yy");
r.Style.Font.Size = titleFontSize;
r.Style.Font.Bold = true;
r.Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
r.Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
}
//Set up the lookup dictionary for getting rows when they're not all there
Dictionary <int, DataRow> lookup = new Dictionary <int, DataRow>();
foreach (DataRow dr in dt.Rows)
{
int rowid = dr["DateRange"].ToString()[0].ToString().StringToInt();
lookup.Add(rowid, dr);
}
int rowNum = 11;
AddDataRow(worksheet, lookup, rowNum++, true, "SAMPLE SIZE", "SampleCount", CellFormat.Number);
// Overall Stay
AddDataRow(worksheet, lookup, rowNum++, true, "OVERALL STAY", null);
AddDataRow(worksheet, lookup, rowNum++, false, "Satisfaction score", "Q1Overall");
// GSEI
AddDataRow(worksheet, lookup, rowNum++, true, "GUEST SERVICE EXPERIENCE INDEX (GSEI)", null);
AddDataRow(worksheet, lookup, rowNum++, false, "Overall GSEI", "Q2");
AddDataRow(worksheet, lookup, rowNum++, false, "Ensuring all of your needs were met", "Q1A");
AddDataRow(worksheet, lookup, rowNum++, false, "Making you feel welcome", "Q1B");
AddDataRow(worksheet, lookup, rowNum++, false, "Going above & beyond normal service", "Q1C");
AddDataRow(worksheet, lookup, rowNum++, false, "Speed of service", "Q1D");
AddDataRow(worksheet, lookup, rowNum++, false, "Encouraging you to visit again", "Q1E");
AddDataRow(worksheet, lookup, rowNum++, false, "Overall staff availability", "Q1F");
//ROOMS
AddDataRow(worksheet, lookup, rowNum++, true, "ROOMS", null);
AddDataRow(worksheet, lookup, rowNum++, false, "Overall Rooms Score", "RoomScore");
//Reservation, Front Desk
AddDataRow(worksheet, lookup, rowNum++, true, "Reservation, Front Desk", null);
AddDataRow(worksheet, lookup, rowNum++, false, "Overall Score", "ReservationScore");
AddDataRow(worksheet, lookup, rowNum++, false, "Friendliness of Reservation Agent", "Q3A");
AddDataRow(worksheet, lookup, rowNum++, false, "Helpfulness of Reservation Agent", "Q3B");
AddDataRow(worksheet, lookup, rowNum++, false, "Accuracy of reservation information upon check-in", "Q3C");
AddDataRow(worksheet, lookup, rowNum++, false, "Employee knowledge of the River Rock Casino Resort & Facilities", "Q3D");
AddDataRow(worksheet, lookup, rowNum++, false, "Efficiency of check-in", "Q3E");
AddDataRow(worksheet, lookup, rowNum++, false, "Friendliness of Front Desk staff", "Q3F");
AddDataRow(worksheet, lookup, rowNum++, false, "Helpfulness of Front Desk staff", "Q3G");
AddDataRow(worksheet, lookup, rowNum++, false, "Employees' 'can-do' attitude", "Q3H");
AddDataRow(worksheet, lookup, rowNum++, false, "Efficiency of check-out", "Q3I");
AddDataRow(worksheet, lookup, rowNum++, false, "Accuracy of bill at check-out", "Q3J");
// Housekeeping
AddDataRow(worksheet, lookup, rowNum++, true, "Housekeeping", null);
AddDataRow(worksheet, lookup, rowNum++, false, "Overall Score", "HousekeepingScore");
AddDataRow(worksheet, lookup, rowNum++, false, "Friendliness of Housekeeping staff", "Q4A");
AddDataRow(worksheet, lookup, rowNum++, false, "Room cleanliness", "Q4B");
AddDataRow(worksheet, lookup, rowNum++, false, "Bathroom cleanliness", "Q4C");
// Hotel Room
AddDataRow(worksheet, lookup, rowNum++, true, "Hotel Room", null);
AddDataRow(worksheet, lookup, rowNum++, false, "Overall Score", "HotelRoomScore");
AddDataRow(worksheet, lookup, rowNum++, false, "Towels & Linens", "Q5A");
AddDataRow(worksheet, lookup, rowNum++, false, "Proper functioning of lights, TV, etc.", "Q5B");
AddDataRow(worksheet, lookup, rowNum++, false, "Overall condition of the room", "Q5C");
AddDataRow(worksheet, lookup, rowNum++, false, "Adequate amenities", "Q5D");
// Fitness Centre
AddDataRow(worksheet, lookup, rowNum++, true, "Fitness Centre", null);
AddDataRow(worksheet, lookup, rowNum++, false, "% Used", "Q6FitnessCenter");
AddDataRow(worksheet, lookup, rowNum++, false, "Overall Score", "FitnessScore");
AddDataRow(worksheet, lookup, rowNum++, false, "Cleanliness of Fitness Center", "Q11A");
AddDataRow(worksheet, lookup, rowNum++, false, "Quality/ condition of fitness equipment", "Q11B");
AddDataRow(worksheet, lookup, rowNum++, false, "Availability of Fitness Center equipment", "Q11C");
AddDataRow(worksheet, lookup, rowNum++, false, "Variety of equipment", "Q11D");
// Pool / Hot Tub
AddDataRow(worksheet, lookup, rowNum++, true, "Pool / Hot Tub", null);
AddDataRow(worksheet, lookup, rowNum++, false, "% Used", "Q6PoolHotTub");
AddDataRow(worksheet, lookup, rowNum++, false, "Overall Score", "PoolScore");
AddDataRow(worksheet, lookup, rowNum++, false, "Cleanliness of pool area", "Q12A");
AddDataRow(worksheet, lookup, rowNum++, false, "Temperature of pool", "Q12B");
AddDataRow(worksheet, lookup, rowNum++, false, "Cleanliness of hot tub area", "Q12C");
AddDataRow(worksheet, lookup, rowNum++, false, "Temperature of hot tub", "Q12D");
AddDataRow(worksheet, lookup, rowNum++, false, "Cleanliness of changing rooms", "Q12E");
// Valet Parking
AddDataRow(worksheet, lookup, rowNum++, true, "Valet Parking", null);
AddDataRow(worksheet, lookup, rowNum++, false, "% Used", "Q6ValetParking");
AddDataRow(worksheet, lookup, rowNum++, false, "Overall Score", "ValetScore");
AddDataRow(worksheet, lookup, rowNum++, false, "Greeting upon arrival", "Q14A");
AddDataRow(worksheet, lookup, rowNum++, false, "Car returned in timely manner", "Q14B");
AddDataRow(worksheet, lookup, rowNum++, false, "Original mirror position", "Q14C");
AddDataRow(worksheet, lookup, rowNum++, false, "Original radio station", "Q14D");
AddDataRow(worksheet, lookup, rowNum++, false, "Original seat position", "Q14E");
AddDataRow(worksheet, lookup, rowNum++, false, "Valet driver drove care in respectful manner", "Q14F");
AddDataRow(worksheet, lookup, rowNum++, false, "Pleasant departure greeting", "Q14G");
// Concierge
AddDataRow(worksheet, lookup, rowNum++, true, "Concierge", null);
AddDataRow(worksheet, lookup, rowNum++, false, "% Used", "Q6Concierge");
AddDataRow(worksheet, lookup, rowNum++, false, "Overall Score", "ConciergeScore");
AddDataRow(worksheet, lookup, rowNum++, false, "Availability of Concierge", "Q15A");
AddDataRow(worksheet, lookup, rowNum++, false, "Friendliness of Concierge", "Q15B");
AddDataRow(worksheet, lookup, rowNum++, false, "Employee knowledge of the River Rock Casino Resort & Facilities", "Q15C");
AddDataRow(worksheet, lookup, rowNum++, false, "Staff member went out of way to provide excellent service", "Q15D");
AddDataRow(worksheet, lookup, rowNum++, false, "Pleasant departure greeting", "Q15E");
// Bell /Door Service
AddDataRow(worksheet, lookup, rowNum++, true, "Bell /Door Service", null);
AddDataRow(worksheet, lookup, rowNum++, false, "% Used", "Q6BellDoorService");
AddDataRow(worksheet, lookup, rowNum++, false, "Overall Score", "BellDoorScore");
AddDataRow(worksheet, lookup, rowNum++, false, "Greeting upon arrival", "Q16A");
AddDataRow(worksheet, lookup, rowNum++, false, "Acknowledgement throughout stay", "Q16B");
AddDataRow(worksheet, lookup, rowNum++, false, "Friendliness of bell/ door staff", "Q16C");
AddDataRow(worksheet, lookup, rowNum++, false, "Employee knowledge of the River Rock Casino Resort & Facilities", "Q16D");
AddDataRow(worksheet, lookup, rowNum++, false, "Staff member went out of way to provide excellent service", "Q16E");
AddDataRow(worksheet, lookup, rowNum++, false, "Pleasant departure greeting", "Q16F");
// FOOD & BEVERAGE, CATERING
AddDataRow(worksheet, lookup, rowNum++, true, "FOOD & BEVERAGE, CATERING", null);
AddDataRow(worksheet, lookup, rowNum++, false, "Overall F & B, Catering Score", "RoomScore");
// Tramonto
AddDataRow(worksheet, lookup, rowNum++, true, "Tramonto", null);
AddDataRow(worksheet, lookup, rowNum++, false, "% Used", "Q6Tramonto");
AddDataRow(worksheet, lookup, rowNum++, false, "Overall Score", "TramontoScore");
AddDataRow(worksheet, lookup, rowNum++, false, "Greeting upon arrival", "Q7A");
AddDataRow(worksheet, lookup, rowNum++, false, "Timeliness of seating", "Q7B");
AddDataRow(worksheet, lookup, rowNum++, false, "Attentiveness of server", "Q7C");
AddDataRow(worksheet, lookup, rowNum++, false, "Server's knowledge of menu selections", "Q7D");
AddDataRow(worksheet, lookup, rowNum++, false, "Timeliness of meal delivery", "Q7E");
AddDataRow(worksheet, lookup, rowNum++, false, "Quality and taste of food", "Q7F");
AddDataRow(worksheet, lookup, rowNum++, false, "Presentation of food", "Q7G");
AddDataRow(worksheet, lookup, rowNum++, false, "Quality of beverage", "Q7H");
AddDataRow(worksheet, lookup, rowNum++, false, "Accuracy of bill", "Q7I");
// Buffet
AddDataRow(worksheet, lookup, rowNum++, true, "Buffet", null);
AddDataRow(worksheet, lookup, rowNum++, false, "% Used", "Q6TheBuffet");
AddDataRow(worksheet, lookup, rowNum++, false, "Overall Score", "BuffetScore");
AddDataRow(worksheet, lookup, rowNum++, false, "Greeting upon arrival", "Q8A");
AddDataRow(worksheet, lookup, rowNum++, false, "Attentiveness of server", "Q8B");
AddDataRow(worksheet, lookup, rowNum++, false, "Server's knowledge of menu selections", "Q8C");
AddDataRow(worksheet, lookup, rowNum++, false, "Quality and taste of food", "Q8D");
AddDataRow(worksheet, lookup, rowNum++, false, "Quality of beverage", "Q8E");
AddDataRow(worksheet, lookup, rowNum++, false, "Accuracy of bill", "Q8F");
// Curve
AddDataRow(worksheet, lookup, rowNum++, true, "Curve", null);
AddDataRow(worksheet, lookup, rowNum++, false, "% Used", "Q6Curve");
AddDataRow(worksheet, lookup, rowNum++, false, "Overall Score", "CurveScore");
AddDataRow(worksheet, lookup, rowNum++, false, "Greeting upon arrival", "Q9A");
AddDataRow(worksheet, lookup, rowNum++, false, "Timeliness of seating", "Q9B");
AddDataRow(worksheet, lookup, rowNum++, false, "Attentiveness of server", "Q9C");
AddDataRow(worksheet, lookup, rowNum++, false, "Server's knowledge of menu selections", "Q9D");
AddDataRow(worksheet, lookup, rowNum++, false, "Timeliness of meal delivery", "Q9E");
AddDataRow(worksheet, lookup, rowNum++, false, "Quality and taste of food", "Q9F");
AddDataRow(worksheet, lookup, rowNum++, false, "Presentation of food", "Q9G");
AddDataRow(worksheet, lookup, rowNum++, false, "Quality of beverage", "Q9H");
AddDataRow(worksheet, lookup, rowNum++, false, "Accuracy of bill", "Q9I");
// In-Room Dining
AddDataRow(worksheet, lookup, rowNum++, true, "In-Room Dining", null);
AddDataRow(worksheet, lookup, rowNum++, false, "% Used", "Q6InRoomDining");
AddDataRow(worksheet, lookup, rowNum++, false, "Overall Score", "InRoomScore");
AddDataRow(worksheet, lookup, rowNum++, false, "Phone answered promptly", "Q10A");
AddDataRow(worksheet, lookup, rowNum++, false, "Friendliness of order taker", "Q10B");
AddDataRow(worksheet, lookup, rowNum++, false, "Friendliness of server", "Q10C");
AddDataRow(worksheet, lookup, rowNum++, false, "Order delivered within time period advised", "Q10D");
AddDataRow(worksheet, lookup, rowNum++, false, "Accuracy of order", "Q10E");
AddDataRow(worksheet, lookup, rowNum++, false, "Presentation of food", "Q10F");
AddDataRow(worksheet, lookup, rowNum++, false, "Quality of in-room dining food", "Q10G");
AddDataRow(worksheet, lookup, rowNum++, false, "Delivery staff offered pick-up of empty tray", "Q10H");
// Meeting & Events
AddDataRow(worksheet, lookup, rowNum++, true, "Meeting & Events", null);
AddDataRow(worksheet, lookup, rowNum++, false, "% Used", "Q6Meeting");
AddDataRow(worksheet, lookup, rowNum++, false, "Overall Score", "MeetingScore");
AddDataRow(worksheet, lookup, rowNum++, false, "Condition and cleanliness of meeting/event room", "Q13A");
AddDataRow(worksheet, lookup, rowNum++, false, "Proper meeting/event room temperature", "Q13B");
AddDataRow(worksheet, lookup, rowNum++, false, "Quality of meeting/event food and beverage", "Q13C");
AddDataRow(worksheet, lookup, rowNum++, false, "Friendliness and efficiency of meeting/event staff", "Q13D");
AddDataRow(worksheet, lookup, rowNum++, false, "Quality/condition/support of technical equipment", "Q13E");
AddDataRow(worksheet, lookup, rowNum++, false, "Meeting/event facilities (size, design, amenities)", "Q13F");
AddDataRow(worksheet, lookup, rowNum++, false, "Accuracy of meeting/ event signage", "Q13G");
// SERVICE RECOVERY
AddDataRow(worksheet, lookup, rowNum++, true, "SERVICE RECOVERY", null);
AddDataRow(worksheet, lookup, rowNum++, false, "Experience problem?", "Q23");
AddDataRow(worksheet, lookup, rowNum++, false, "Report problem?", "Q24C");
AddDataRow(worksheet, lookup, rowNum++, false, "Where experienced problem? (% of all problems)", null);
AddDataRow(worksheet, lookup, rowNum++, false, " Arrival", "Q24A_Arrival");
AddDataRow(worksheet, lookup, rowNum++, false, " Staff", "Q24A_Staff");
AddDataRow(worksheet, lookup, rowNum++, false, " Guest Room", "Q24A_GuestRoom");
AddDataRow(worksheet, lookup, rowNum++, false, " Food & Beverage", "Q24A_FoodBeverage");
AddDataRow(worksheet, lookup, rowNum++, false, " Facilities & Service", "Q24A_FacilitiesService");
AddDataRow(worksheet, lookup, rowNum++, false, " Billing/Departure", "Q24A_BillingDeparture");
AddDataRow(worksheet, lookup, rowNum++, false, " Meetings & Events", "Q24A_MeetingsEvents");
AddDataRow(worksheet, lookup, rowNum++, false, " Other", "Q24A_Other");
AddDataRow(worksheet, lookup, rowNum++, false, "", null);
AddDataRow(worksheet, lookup, rowNum++, false, "Overall ability to fix problem (PRS Score)", "Q24D");
AddDataRow(worksheet, lookup, rowNum++, false, "Attribute ratings:", null);
AddDataRow(worksheet, lookup, rowNum++, false, " The length of time taken to resolve your problem", "Q24E_1");
AddDataRow(worksheet, lookup, rowNum++, false, " The effort of employees in resolving your problem", "Q24E_2");
AddDataRow(worksheet, lookup, rowNum++, false, " The courteousness of employees while resolving your problem", "Q24E_3");
AddDataRow(worksheet, lookup, rowNum++, false, " The amount of communication with you from employees while resolving your problem", "Q24E_4");
AddDataRow(worksheet, lookup, rowNum++, false, " The fairness of the outcome in resolving your problem", "Q24E_5");
// Satisfaction Attributes
AddDataRow(worksheet, lookup, rowNum++, true, "Satisfaction Attributes", null);
AddDataRow(worksheet, lookup, rowNum++, false, "Satisfaction with how we made you feel", null);
AddDataRow(worksheet, lookup, rowNum++, false, " Welcome", "Q17A");
AddDataRow(worksheet, lookup, rowNum++, false, " Comfortable", "Q17B");
AddDataRow(worksheet, lookup, rowNum++, false, " Important", "Q17C");
AddDataRow(worksheet, lookup, rowNum++, false, "Satisfaction with Overall Stay", null);
AddDataRow(worksheet, lookup, rowNum++, false, " Overall condition of the River Rock Casino Resort", "Q18A");
AddDataRow(worksheet, lookup, rowNum++, false, " Value for Price", "Q18B");
AddDataRow(worksheet, lookup, rowNum++, false, "Likelihood to Return", "Q19");
AddDataRow(worksheet, lookup, rowNum++, false, "Likelihood to Recommend", "Q20");
AddDataRow(worksheet, lookup, rowNum++, false, "Received \"Exceptional\" Service", "Q21");
AddDataRow(worksheet, lookup, rowNum++, false, "Importance of \"Green\" Initiatives", "Q22");
AddDataRow(worksheet, lookup, rowNum++, false, "Visited River Rock before?", "Q27");
AddDataRow(worksheet, lookup, rowNum++, false, "", null);
AddDataRow(worksheet, lookup, rowNum++, false, "Primary Reason for Choosing (% of all Responses)", null);
AddDataRow(worksheet, lookup, rowNum++, false, " Articles/Advertisements", "Q25_Articles");
AddDataRow(worksheet, lookup, rowNum++, false, " Business meeting/Conference venue", "Q25_Business");
AddDataRow(worksheet, lookup, rowNum++, false, " Facilities/Amenities", "Q25_Facilities");
AddDataRow(worksheet, lookup, rowNum++, false, " Location", "Q25_Location");
AddDataRow(worksheet, lookup, rowNum++, false, " Other, please specify", "Q25_Other");
AddDataRow(worksheet, lookup, rowNum++, false, " Personal recommendation", "Q25_Personal");
AddDataRow(worksheet, lookup, rowNum++, false, " Previous visit", "Q25_Previous");
AddDataRow(worksheet, lookup, rowNum++, false, " Special package/rate", "Q25_Special");
AddDataRow(worksheet, lookup, rowNum++, false, " Travel Agent", "Q25_Travel");
AddDataRow(worksheet, lookup, rowNum++, false, " Website", "Q25_Website");
AddDataRow(worksheet, lookup, rowNum++, false, "", null);
AddDataRow(worksheet, lookup, rowNum++, false, "Reason to Visit (% of all Responses)", null);
AddDataRow(worksheet, lookup, rowNum++, false, " Business", "Q26Business");
AddDataRow(worksheet, lookup, rowNum++, false, " Pleasure", "Q26Pleasure");
AddDataRow(worksheet, lookup, rowNum++, false, " Meeting / Event", "Q26MeetingEvent");
AddDataRow(worksheet, lookup, rowNum++, false, " Other", "Q26Other");
AddDataRow(worksheet, lookup, rowNum++, false, "", null);
AddDataRow(worksheet, lookup, rowNum++, false, "Follow-up on comments requested", "Q29");
// Bottom Line
worksheet.Cells["A" + rowNum].Value = "";
using (ExcelRange r = worksheet.Cells["A" + rowNum + ":L" + rowNum])
{
r.Merge = true;
r.Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
}
rowNum++;
// Bottom Message
worksheet.Cells["A" + rowNum].Value = "All scores are top-2 box scores on a scale of 5, unless otherwise indicated.";
using (ExcelRange r = worksheet.Cells["A" + rowNum + ":L" + rowNum])
{
r.Merge = true;
r.Style.Font.Size = 10;
r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
}
string fileName = sc.ToString() + "-Hotel-Monthly-" + ReportingTools.AdjustAndDisplayDate(DateTime.Now, "yyyy-MM-dd-hh-mm-ss", User) + ".xlsx";
const string lPath = "~/Files/Cache/";
string lOutput = string.Concat(MapPath(lPath), fileName);
FileInfo fi = new FileInfo(lOutput);
p.SaveAs(fi);
hlDownload.Text = "Download File - " + fileName;
hlDownload.NavigateUrl = String.Format("{0}{1}", lPath, fileName);
}
}
protected void btnExport_Click(object sender, EventArgs e)
{
SQLDatabase sql = new SQLDatabase(); sql.CommandTimeout = 120;
if (String.IsNullOrWhiteSpace(ddlQuestion1.SelectedValue) ||
String.IsNullOrWhiteSpace(ddlQuestion1.SelectedValue))
{
TopMessage.ErrorMessage = "Please select two questions to generate cross-tabs for.";
return;
}
else if (ddlQuestion1.SelectedValue == ddlQuestion2.SelectedValue)
{
TopMessage.ErrorMessage = "Please select two <b>separate</b> questions to generate cross-tabs for.";
return;
}
SQLParamList sqlParams = new SQLParamList()
.Add("@Question1", ddlQuestion1.SelectedValue)
.Add("@Question2", ddlQuestion2.SelectedValue);
DataSet ds = sql.QueryDataSet(@"
SELECT [SurveyTypeID]
,[ColumnName]
,[LongLabel]
,[ShortLabel]
,[AnswerTypeID]
,[AvailableOnCrossTab]
,[HasNA]
,[SortOrder]
FROM [tblSurveyQuestions]
WHERE [SurveyTypeID] = 1
AND [ColumnName] IN (@Question1, @Question2);
SELECT [ColumnName]
,[DBValue]
,[Label]
,[SortOrder]
FROM [GCC].[dbo].[tblSurveyQuestions_CustomAnswers]
WHERE [SurveyTypeID] = 1
AND [ColumnName] IN (@Question1, @Question2)
ORDER BY [ColumnName], [SortOrder];", sqlParams);
if (sql.HasError || ds.Tables.Count != 2 || ds.Tables[0].Rows.Count != 2)
{
TopMessage.ErrorMessage = "Unable to load question information from the database.";
return;
}
DataRow drQ1, drQ2;
if (ds.Tables[0].Rows[0]["ColumnName"].Equals(ddlQuestion1.SelectedValue))
{
drQ1 = ds.Tables[0].Rows[0];
drQ2 = ds.Tables[0].Rows[1];
}
else
{
drQ1 = ds.Tables[0].Rows[1];
drQ2 = ds.Tables[0].Rows[0];
}
List <Answer> q1Answers = GetAnswers(drQ1, ds.Tables[1]),
q2Answers = GetAnswers(drQ2, ds.Tables[1]);
StringBuilder sbRowBase = new StringBuilder();
StringBuilder sbGroupCols = new StringBuilder();
bool headerNull = false;
foreach (Answer a in q1Answers)
{
if (a.DBValue != "NULL")
{
sbRowBase.AppendFormat("'{0}',", a.DBValue.Replace("'", "''"));
sbGroupCols.AppendFormat("SUM( CASE WHEN {{0}} = '{0}' THEN 1 ELSE 0 END ) AS '{1}',\n", a.DBValue.Replace("'", "''"), a.Label.Replace("'", "''"));
}
else
{
headerNull = true;
sbGroupCols.AppendFormat("SUM( CASE WHEN {{0}} IS NULL THEN 1 ELSE 0 END ) AS '{1}',\n", a.DBValue.Replace("'", "''"), a.Label.Replace("'", "''"));
}
}
if (sbRowBase.Length > 0)
{
sbRowBase.Remove(sbRowBase.Length - 1, 1);
}
StringBuilder sbRows = new StringBuilder();
bool rowNull = false;
foreach (Answer a in q2Answers)
{
if (a.DBValue != "NULL")
{
sbRows.AppendFormat("UNION ALL SELECT '{0}' [Val], '{1}' [Label], {2} [Sort]\n",
a.DBValue.ToString().Replace("'", "''"),
a.Label.ToString().Replace("'", "''"),
a.Sort);
}
else
{
rowNull = true;
sbRows.AppendFormat("UNION ALL SELECT NULL [Val], '{0}' [Label], {1} [Sort]\n",
a.Label.ToString().Replace("'", "''"),
a.Sort);
}
}
string query = String.Format(@"
;WITH grp AS (
SELECT CONVERT(varchar(1000), {0}) AS [ColVal],
SUM( CASE WHEN {1} IN ( {2} ){5} THEN 1 ELSE 0 END ) AS [RowBase],
{3}
GROUPING( {0} ) AS [IsBase]
FROM [tblSurveyGEI] d
WHERE [DateEntered] BETWEEN @BeginDate AND @EndDate
{6}
GROUP BY {0}
WITH ROLLUP
)
SELECT x.Label, grp.*
FROM (
SELECT 'Base' [Val], 'Base' [Label], -10 [Sort]
{4}
) x
LEFT JOIN grp
ON x.Val = grp.ColVal
OR ( x.Sort = -10 AND grp.IsBase = 1 )
OR ( grp.IsBase = 0 AND x.Val IS NULL AND grp.ColVal IS NULL )
ORDER BY x.Sort
",
"[" + drQ2["ColumnName"].ToString() + "]",
"[" + drQ1["ColumnName"].ToString() + "]",
sbRowBase.ToString(),
String.Format(sbGroupCols.ToString(), "[" + drQ1["ColumnName"].ToString() + "]"),
sbRows.ToString(),
(headerNull ? " OR [" + drQ1["ColumnName"].ToString() + "] IS NULL " : String.Empty),
(!headerNull && !rowNull ? "AND [" + drQ2["ColumnName"].ToString() + "] IS NOT NULL" : String.Empty)
);
sqlParams = new SQLParamList()
.Add("@BeginDate", drDateRange.BeginDate)
.Add("@EndDate", drDateRange.EndDate);
DataTable crossTab = sql.QueryDataTable(query, sqlParams);
if (sql.HasError)
{
TopMessage.ErrorMessage = "There was an error querying the cross tab data from the database.";
return;
}
Data = crossTab;
HeaderRow = drQ1;
HeaderAnswers = q1Answers;
RowRow = drQ2;
RowAnswers = q2Answers;
if (ddlExport.SelectedIndex == 1)
{
using (ExcelPackage p = new ExcelPackage())
{
p.Workbook.Worksheets.Add("CrossTabs");
ExcelWorksheet worksheet = p.Workbook.Worksheets[1];
worksheet.Cells.Style.Font.Size = 11; //Default font size for whole sheet
worksheet.Cells.Style.Font.Name = "Calibri"; //Default Font name for whole sheet
worksheet.Column(1).Width *= 5;
worksheet.Cells[1, 1].Value = "Start Date: " + drDateRange.BeginDate.Value.ToString("yyyy-MM-dd");
worksheet.Cells[2, 1].Value = "End Date: " + drDateRange.EndDate.Value.ToString("yyyy-MM-dd");
//worksheet.Cells[3, 1].Value = "Header Question: " + HeaderRow["LongLabel"].ToString() + ( !HeaderRow["ShortLabel"].Equals( DBNull.Value ) ? " - " + HeaderRow["ShortLabel"].ToString() : String.Empty );
//worksheet.Cells[4, 1].Value = "Row Question: " + RowRow["LongLabel"].ToString() + ( !RowRow["ShortLabel"].Equals( DBNull.Value ) ? " - " + RowRow["ShortLabel"].ToString() : String.Empty );
int rowNum = 4;
using (ExcelRange r = worksheet.Cells[rowNum, 1, rowNum + 1, 1])
{
r.Merge = true;
}
using (ExcelRange r = worksheet.Cells[rowNum, 2, rowNum + 1, 2])
{
r.Value = "Base";
r.Merge = true;
r.Style.Font.Bold = true;
r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
}
using (ExcelRange r = worksheet.Cells[rowNum, 3, rowNum, 2 + HeaderAnswers.Count])
{
r.Value = HeaderRow["LongLabel"].ToString() + (!HeaderRow["ShortLabel"].Equals(DBNull.Value) ? " - " + HeaderRow["ShortLabel"].ToString() : String.Empty);
r.Merge = true;
r.Style.WrapText = true;
r.Style.Font.Bold = true;
r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
}
worksheet.Row(rowNum).Height *= 2;
rowNum++;
for (int i = 0; i < HeaderAnswers.Count; i++)
{
using (ExcelRange r = worksheet.Cells[rowNum, 3 + i])
{
r.Value = HeaderAnswers[i].Label;
r.Style.Font.Bold = true;
r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
}
}
rowNum++;
using (ExcelRange r = worksheet.Cells[rowNum, 1, rowNum + 1, 1])
{
r.Value = "Base";
r.Merge = true;
r.Style.Font.Bold = true;
r.Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Top;
}
double totalCount = Data.Rows[0]["RowBase"].ToString().StringToDbl();
using (ExcelRange r = worksheet.Cells[rowNum, 2, rowNum + 1, 2])
{
r.Value = totalCount;
r.Merge = true;
r.Style.Numberformat.Format = "#,###;-#,###;0";
r.Style.Font.Bold = true;
r.Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Top;
r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
}
DataRow baseRow = Data.Rows[0];
int c = 3;
foreach (Answer a in HeaderAnswers)
{
AddExcelCellValue(worksheet, rowNum, c, totalCount, baseRow[a.Label]);
c++;
}
rowNum += 2;
worksheet.Row(rowNum).Height *= 3;
using (ExcelRange r = worksheet.Cells[rowNum, 1])
{
r.Value = RowRow["LongLabel"].ToString() + (!RowRow["ShortLabel"].Equals(DBNull.Value) ? " - " + RowRow["ShortLabel"].ToString() : String.Empty);
r.Style.Font.Bold = true;
r.Style.WrapText = true;
r.Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
}
using (ExcelRange r = worksheet.Cells[rowNum, 2, rowNum, 2 + HeaderAnswers.Count])
{
r.Merge = true;
}
rowNum++;
for (int i = 1; i < Data.Rows.Count; i++)
{
DataRow dr = Data.Rows[i];
using (ExcelRange r = worksheet.Cells[rowNum, 1])
{
r.Value = dr["Label"];
r.Style.Font.Bold = true;
r.Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
}
AddExcelCellValue(worksheet, rowNum, 2, totalCount, dr["RowBase"]);
c = 3;
foreach (Answer a in HeaderAnswers)
{
AddExcelCellValue(worksheet, rowNum, c, totalCount, dr[a.Label]);
c++;
}
rowNum += 2;
}
worksheet.Cells[5, 3, 5, 2 + HeaderAnswers.Count].AutoFitColumns(9.14f);
string lFileName = string.Format("CrossTabs-{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);
}
}
else
{
hlDownload.Text = String.Empty;
}
}
protected void btnExport_Click(object sender, EventArgs e)
{
SQLDatabase sql = new SQLDatabase(); sql.CommandTimeout = 120;
sql.CommandTimeout = 120;
SQLParamList sqlParams = new SQLParamList()
.Add("@DR1_Begin", drDateRangeFirst.BeginDate)
.Add("@DR1_End", drDateRangeFirst.EndDate)
.Add("@DR2_Begin", drDateRangeSecond.BeginDate)
.Add("@DR2_End", drDateRangeSecond.EndDate);
DataTable dt = sql.ExecStoredProcedureDataTable("spReports_Comparison", sqlParams);
if (sql.HasError)
{
TopMessage.ErrorMessage = "Oops. Something went wrong when exporting the data. Please try again. (ECP100)";
}
else
{
using (ExcelPackage p = new ExcelPackage())
{
p.Workbook.Worksheets.Add("Comparison");
ExcelWorksheet worksheet = p.Workbook.Worksheets[1];
worksheet.Cells.Style.Font.Size = 11; //Default font size for whole sheet
worksheet.Cells.Style.Font.Name = "Calibri"; //Default Font name for whole sheet
worksheet.Column(1).Width = 12.71f;
worksheet.Column(2).Width = 62f;
for (int i = 0; i < _propertySortOrder.Length * 3; i++)
{
worksheet.Column(3 + i).Width = 12.71f;
}
string group1 = String.Format("{0} - {1}", drDateRangeFirst.BeginDate.Value.ToString("yyyy-MM-dd"), drDateRangeFirst.EndDate.Value.ToString("yyyy-MM-dd"));
string group2 = String.Format("{0} - {1}", drDateRangeSecond.BeginDate.Value.ToString("yyyy-MM-dd"), drDateRangeSecond.EndDate.Value.ToString("yyyy-MM-dd"));
worksheet.Cells[1, 1].Value = String.Format("First Date Range: {0}", group1);
worksheet.Cells[2, 1].Value = String.Format("Second Date Range: {0}", group2);
int rowNum = 4;
Action <ExcelRange> titleMerge = r =>
{
r.Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Medium);
r.Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
r.Style.Font.Bold = true;
r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
};
Action <ExcelRange> style = r =>
{
r.Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);
r.Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium;
r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
};
int offset = 0;
for (int i = 0; i < _propertySortOrder.Length; i++)
{
int prop = _propertySortOrder[i];
if (Master.IsPropertyUser &&
prop != 1 &&
prop != ((int)User.PropertyShortCode))
{
offset -= 3;
continue;
}
AddValue(worksheet, rowNum, 3 + offset + i * 3, rowNum, 5 + offset + i * 3, PropertyTools.GetCasinoName(prop), titleMerge);
AddValue(worksheet, rowNum + 1, 3 + offset + i * 3, "First Range", r =>
{
style(r);
r.Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium;
});
AddValue(worksheet, rowNum + 1, 4 + offset + i * 3, "Second Range", style);
AddValue(worksheet, rowNum + 1, 5 + offset + i * 3, "Diff.", r =>
{
style(r);
r.Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium;
});
}
rowNum += 2;
worksheet.View.FreezePanes(rowNum, 3);
AddDataRow(worksheet, dt, rowNum++, true, "SAMPLE SIZE", "SampleCount", CellFormat.Number);
AddDataRow(worksheet, dt, rowNum++, true, "GUEST EXPERIENCE INDEX", null);
AddDataRow(worksheet, dt, rowNum++, false, "GEI", "GEI", CellFormat.Index);
AddDataRow(worksheet, dt, rowNum++, true, "GUEST SERVICE EXPERIENCE INDEX", null);
AddDataRow(worksheet, dt, rowNum++, false, "GSEI", "GSEI");
AddDataRow(worksheet, dt, rowNum++, true, "NET PROMOTER SCORE", null);
AddDataRow(worksheet, dt, rowNum++, false, "NPS", "NPS");
AddDataRow(worksheet, dt, rowNum++, true, "PROBLEM RESOLUTION SCORE", null);
AddDataRow(worksheet, dt, rowNum++, false, "PRS", "PRS");
AddDataRow(worksheet, dt, rowNum++, true, "GUEST LOYALTY", null);
AddDataRow(worksheet, dt, rowNum++, false, "Likely to recommend the casino", "Q6A");
AddDataRow(worksheet, dt, rowNum++, false, "Likely to mostly visit this casino", "Q6B");
AddDataRow(worksheet, dt, rowNum++, false, "Likely to visit this casino for next gaming entertainment opportunity", "Q6C");
AddDataRow(worksheet, dt, rowNum++, false, "Likely to provide personal preferences so casino can serve me better", "Q6D");
AddDataRow(worksheet, dt, rowNum++, true, "CASINO STAFF", null);
AddDataRow(worksheet, dt, rowNum++, false, "Ensuring all of your needs were met", "Q7A");
AddDataRow(worksheet, dt, rowNum++, false, "Making you feel welcome", "Q7B");
AddDataRow(worksheet, dt, rowNum++, false, "Going above & beyond normal service", "Q7C");
AddDataRow(worksheet, dt, rowNum++, false, "Speed of service", "Q7D");
AddDataRow(worksheet, dt, rowNum++, false, "Encouraging you to visit again", "Q7E");
AddDataRow(worksheet, dt, rowNum++, false, "Overall staff availability", "Q7F");
AddDataRow(worksheet, dt, rowNum++, false, "Overall staff", null);
AddDataRow(worksheet, dt, rowNum++, false, "Cashiers", "Q9A", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "Guest Services", "Q9B", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "Slot Attendants", "Q9C", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "Dealers", "Q9D", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "Restaurant Servers", "Q9E", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "Cocktail Servers", "Q9F", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "Coffee Servers", "Q9G", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "Security", "Q9H", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "Managers/Supervisors", "Q9I", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "Hotel Staff", "Q9J", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "Attribute ratings:", null);
AddDataRow(worksheet, dt, rowNum++, false, "Encouraging you to take part in events or promotions", "Q10A", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "Answering questions you had about the property or promotions", "Q10B", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "Being friendly and welcoming", "Q10C", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, true, "CASINO FACILITIES", null);
AddDataRow(worksheet, dt, rowNum++, false, "Overall facilities", "Q12");
AddDataRow(worksheet, dt, rowNum++, false, "Attribute ratings:", null);
AddDataRow(worksheet, dt, rowNum++, false, "Ambiance, mood, atmosphere of the environment", "Q13A", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "Cleanliness of general areas", "Q13B", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "Clear signage", "Q13C", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "Washroom cleanliness", "Q13D", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "Adequate lighting - it is bright enough", "Q13E", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "Safe environment", "Q13F", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "Parking availability", "Q13G", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, true, "GAMING EXPERIENCE", null);
AddDataRow(worksheet, dt, rowNum++, false, "Primary gaming:", null);
AddDataRow(worksheet, dt, rowNum++, false, "Playing Slots", "Count_Slots", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "Playing Tables", "Count_Tables", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "Playing Poker", "Count_Poker", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "Enjoying Food or Beverages", "Count_Food", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "Watching Live Entertainment at a show lounge or theatre", "Count_Entertainment", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "Staying at our Hotel", "Count_Hotel", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "Watching Live Racing", "Count_LiveRacing", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "Watching Racing at our Racebook", "Count_Racebook", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "Playing Bingo", "Count_Bingo", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "Lottery / Pull Tabs", "Count_Lottery", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "None", "Count_None", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "Primary gaming:", "Q14");
AddDataRow(worksheet, dt, rowNum++, false, "Attribute ratings:", null);
AddDataRow(worksheet, dt, rowNum++, false, "Variety of games available", "Q15A", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "Waiting time to play", "Q15B", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "Availability of specific game at your desired denomination", "Q15C", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "Contests & monthly promotions", "Q15D", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "Courtesy & respectfulness of staff", "Q15E", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "Game Knowledge of Staff", "Q15F", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "Rate of earning", "Q16A", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "Redemption value", "Q16B", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "Choice of rewards", "Q16C", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "Slot Free Play", "Q16D", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, true, "FOOD & BEVERAGE", null);
rowNum = AddYesNoRow(worksheet, dt, rowNum++, false, "Purchase food or beverages?", "Q17");
AddDataRow(worksheet, dt, rowNum++, false, "Overall dining experience", "Q19");
AddDataRow(worksheet, dt, rowNum++, false, "Attribute ratings:", null);
AddDataRow(worksheet, dt, rowNum++, false, "Variety of food choices", "Q20A", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "Cleanliness of outlet", "Q20B", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "Courtesy of staff", "Q20C", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "Timely delivery of order", "Q20D", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "Value for the money", "Q20E", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "Pleasant atmosphere", "Q20F", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "Quality of food", "Q20G", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, true, "LOUNGE ENTERTAINMENT", null);
rowNum = AddYesNoRow(worksheet, dt, rowNum++, false, "Attend Lounge entertainment?", "Q21");
AddDataRow(worksheet, dt, rowNum++, false, "Overall entertainment experience", "Q22");
AddDataRow(worksheet, dt, rowNum++, false, "Attribute ratings:", null);
AddDataRow(worksheet, dt, rowNum++, false, "Sound / quality", "Q23A", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "Seating availability", "Q23B", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "Dance floor", "Q23C", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "Fun and enjoyable atmosphere", "Q23D", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, true, "THEATRE", null);
rowNum = AddYesNoRow(worksheet, dt, rowNum++, false, "Attend Theatre?", "Q24");
AddDataRow(worksheet, dt, rowNum++, false, "Overall Theatre experience", "Q25");
AddDataRow(worksheet, dt, rowNum++, false, "Attribute ratings:", null);
AddDataRow(worksheet, dt, rowNum++, false, "The quality of the show", "Q26A", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "The value of the show", "Q26B", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "Seating choices", "Q26C", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "Sound quality", "Q26D", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, true, "SERVICE RECOVERY", null);
rowNum = AddYesNoRow(worksheet, dt, rowNum++, false, "Experience problem?", "Q27");
AddDataRow(worksheet, dt, rowNum++, false, "Where experienced problem?", null);
AddDataRow(worksheet, dt, rowNum++, false, "Arrival and parking", "Q27A_ArrivalAndParking", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "Guest Services", "Q27A_GuestServices", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "Cashiers", "Q27A_Cashiers", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "Manager/Supervisor", "Q27A_ManagerSupervisor", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "Security", "Q27A_Security", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "Slots", "Q27A_Slots", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "Tables", "Q27A_Tables", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "Food & Beverage", "Q27A_FoodAndBeverage", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "Hotel", "Q27A_Hotel", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "Other", "Q27A_Other", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
rowNum = AddYesNoRow(worksheet, dt, rowNum++, false, "Resolve problem?", "Q28");
rowNum = AddYesNoRow(worksheet, dt, rowNum++, false, "Report problem?", "Q29");
AddDataRow(worksheet, dt, rowNum++, false, "Overall ability to fix problem", "Q30");
AddDataRow(worksheet, dt, rowNum++, false, "Attribute ratings:", null);
AddDataRow(worksheet, dt, rowNum++, false, "The length of time taken to resolve your problem", "Q31A", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "The effort of employees in resolving your problem", "Q31B", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "The courteousness of employees while resolving your problem", "Q31C", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
worksheet.Row(rowNum).Height *= 2;
AddDataRow(worksheet, dt, rowNum++, false, "The amount of communication with you from employees while resolving your problem", "Q31D", CellFormat.Percent, r => { r.Style.WrapText = true; r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "The fairness of the outcome in resolving your problem", "Q31E", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, true, "DEMOGRAPHICS", null);
AddDataRow(worksheet, dt, rowNum++, false, "Male", "Q36_Male");
AddDataRow(worksheet, dt, rowNum++, false, "Female", "Q36_Female");
AddDataRow(worksheet, dt, rowNum++, false, String.Empty, null);
AddDataRow(worksheet, dt, rowNum++, false, "19-24", "Q37_19-24");
AddDataRow(worksheet, dt, rowNum++, false, "25-34", "Q37_25-34");
AddDataRow(worksheet, dt, rowNum++, false, "35-44", "Q37_35-44");
AddDataRow(worksheet, dt, rowNum++, false, "45-54", "Q37_45-54");
AddDataRow(worksheet, dt, rowNum++, false, "55-64", "Q37_55-64");
AddDataRow(worksheet, dt, rowNum++, false, "65 or older", "Q37_65 or older");
AddDataRow(worksheet, dt, rowNum++, false, String.Empty, null);
AddDataRow(worksheet, dt, rowNum++, false, "First visit", "Q38_This was my first visit");
AddDataRow(worksheet, dt, rowNum++, false, "2-7 times per week", "Q38_2-7 times per week");
AddDataRow(worksheet, dt, rowNum++, false, "Once per week", "Q38_Once per week");
AddDataRow(worksheet, dt, rowNum++, false, "2-3 times per month", "Q38_2-3 times per month");
AddDataRow(worksheet, dt, rowNum++, false, "Once per month", "Q38_Once per month");
AddDataRow(worksheet, dt, rowNum++, false, "Several times a year", "Q38_Several times a year");
AddDataRow(worksheet, dt, rowNum++, false, "Languages spoken at home (other than English)", null);
AddDataRow(worksheet, dt, rowNum++, false, "Korean", "Q39_1", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "Punjabi", "Q39_2", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "Chinese Mandarin", "Q39_3", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "Other Western European languages", "Q39_4", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "Eastern European languages", "Q39_5", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "Spanish", "Q39_6", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "French", "Q39_7", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "Hindi", "Q39_8", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "Tagalog", "Q39_9", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "Vietnamese", "Q39_10", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "Pakistani", "Q39_11", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "Farsi", "Q39_12", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "Japanese", "Q39_13", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "Arabic / Middle Eastern", "Q39_14", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "Chinese – Cantonese", "Q39_15", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, "Other", "Q39_16", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, dt, rowNum++, false, String.Empty, null);
rowNum = AddYesNoRow(worksheet, dt, rowNum++, false, "Players Club?", "Q4");
rowNum++; //Skip one row
string lFileName = string.Format("DateRangeComparison-{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("{0}{1}", lPath, lFileName);
}
}
}
protected void btnExport_Click(object sender, EventArgs e)
{
SQLDatabase sql = new SQLDatabase(); sql.CommandTimeout = 120;
SQLParamList sqlParams = new SQLParamList()
.Add("@DateRange_Begin", drDateRange.BeginDate)
.Add("@DateRange_End", Convert.ToDateTime(drDateRange.EndDate))
.Add("@SurveyType", ddlSurvey.SelectedValue);
if (ddlProperty.SelectedIndex == 0)
{
sqlParams.Add("@PropertyID", DBNull.Value);
}
else
{
sqlParams.Add("@PropertyID", ddlProperty.SelectedValue);
}
DataSet ds = sql.ExecStoredProcedureDataSet("spData_Export", sqlParams);
if (sql.HasError)
{
TopMessage.ErrorMessage = "Oops. Something went wrong when exporting the data. Please try again. (EDE100)";
}
else
{
string lFileName = string.Format("{0}_{1}", ddlSurvey.SelectedItem.Text, ReportingTools.AdjustAndDisplayDate(DateTime.Now, "yyyy-MM-dd-hh-mm-ss", User));
const string lPath = "~/Files/Cache/";
string lOutput = string.Concat(MapPath(lPath), lFileName);
using (CSVWriter csv = new CSVWriter(lOutput + ".csv"))
{
csv.WriteLine("{0} Data Extract", ddlSurvey.SelectedItem.Text);
csv.WriteLine("Date Range: {0}", drDateRange.BeginDate.HasValue ? String.Format("{0} - {1}", drDateRange.BeginDate.Value.ToString("yyyy-MM-dd"), drDateRange.EndDate.Value.ToString("yyyy-MM-dd")) : "All");
csv.WriteLine("Property: {0}", ddlProperty.SelectedItem.Text);
//CSVRow longLabelRow = new CSVRow();
//CSVRow shortLabelRow = new CSVRow();
CSVRow headerRow = new CSVRow();
foreach (DataColumn dc in ds.Tables[0].Columns)
{
headerRow.Add(dc.ColumnName);
string kv = dc.ColumnName;
//if ( QuestionLookup.ContainsKey( kv ) ) {
// string[] vals = QuestionLookup[kv].Split( '|' );
// longLabelRow.Add( vals[0] );
// if ( vals.Length > 1 ) {
// shortLabelRow.Add( vals[1] );
// } else {
// shortLabelRow.Add( vals[1] );
// }
//} else {
// longLabelRow.Add( "" );
// shortLabelRow.Add( "" );
//}
}
//csv.WriteRow( longLabelRow );
//csv.WriteRow( shortLabelRow );
csv.WriteRow(headerRow);
int colCount = ds.Tables[0].Columns.Count;
foreach (DataRow dr in ds.Tables[0].Rows)
{
CSVRow row = new CSVRow();
for (int i = 0; i < colCount; i++)
{
row.Add(dr[i].ToString());
}
csv.WriteRow(row);
}
csv.Close();
csv.Dispose();
}
//Conversion.DataTableToExcel(lResultSet, ddlQID.SelectedItem.Text, lOutput, true);
hlDownload.Text = "Download File - " + String.Concat(lFileName, ".csv");
hlDownload.NavigateUrl = String.Format("{0}{1}", lPath, String.Concat(lFileName, ".csv"));
}
}
public static string GenerateRespondentDetailsLink(string encoreNumberOrEmail)
{
if (!String.IsNullOrWhiteSpace(encoreNumberOrEmail))
{
if (Regex.IsMatch(encoreNumberOrEmail, @"^\d+$"))
{
return("<a href=\"/GuestDetails/" + encoreNumberOrEmail + "\" title=\"View this Player's Information\">" + ReportingTools.CleanData(encoreNumberOrEmail) + "</a>");
}
else if (Regex.IsMatch(encoreNumberOrEmail, "^.+@.+$"))
{
return("<a href=\"/GuestDetails/?e=" + encoreNumberOrEmail + "\" title=\"View this Player's Information\">" + ReportingTools.CleanData(encoreNumberOrEmail) + "</a>");
}
}
return(String.Empty);
}
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);
}
}
}
public void ProcessRequest(HttpContext context)
{
//Only run locally or in the network
string ip = RequestVars.GetRequestIPv4Address();
if (!ip.Equals("127.0.0.1") && !ip.StartsWith("172.16.") && !ip.StartsWith("192.168.0."))
{
ErrorHandler.WriteLog("GCC_Web_Portal.Jobs", "Job attempted to be run by invalid IP: " + ip, ErrorHandler.ErrorEventID.General);
return;
}
//Get the job ID
string jobID = RequestVars.Get("jobid", String.Empty);
switch (jobID)
{
case "e07db58b-d3a6-4e01-a5ed-ff9875773b3c":
#region Send weekly notification email
DateTime startDate = DateTime.Now.Date.AddDays(-7);
DateTime endDate = DateTime.Now.Date.AddMilliseconds(-1);
//DateTime startDate = new DateTime( 2015, 7, 1 ).Date;
//DateTime endDate = new DateTime( 2015, 8, 1 ).Date.AddMilliseconds( -1 );
SQLDatabase sql = new SQLDatabase(); sql.CommandTimeout = 120;
DataTable dt = sql.ExecStoredProcedureDataTable("spJobs_StatusEmail",
new SQLParamList()
.Add("@DateCreated_Begin", startDate)
.Add("@DateCreated_End", endDate));
StringBuilder sbCurrent = new StringBuilder();
sbCurrent.AppendFormat(@"<h3 style='margin:20px 0'>GEI / GSEI Dashboard for {0} to {1}</h3><table style='border-collapse:collapse;border:1px solid #444;width:100%' cellspacing='0' cellpadding='0'>", startDate.ToString("MMM d, yyyy"), endDate.ToString("MMM d, yyyy"));
sbCurrent.AppendFormat("<thead><tr><th{0}></th><th{0}># Surveys</th><th{0}>GEI</th><th{0}>NPS</th><th{0}>PRS</th><th{0}>GSEI</th><th{0}># Followup</th><th{0}>% Followup</th><th{0}># <24h</th><th{0}>#24-48h</th><th{0}># > 48h</th><th{0}>Avg. Response</th></tr></thead>",
" style='padding:5px;border:1px solid #BBB'");
StringBuilder sbComparison = new StringBuilder("<h3 style='margin:20px 0'>Change from Previous Week</h3><table style='border-collapse:collapse;border:1px solid #444;width:100%' cellspacing='0' cellpadding='0'>");
sbComparison.AppendFormat("<thead><tr><th{0}></th><th{0}># Surveys</th><th{0}>GEI</th><th{0}>NPS</th><th{0}>PRS</th><th{0}>GSEI</th><th{0}># Followup</th><th{0}>% Followup</th><th{0}># <24h</th><th{0}>#24-48h</th><th{0}># > 48h</th><th{0}>Avg. Response</th></tr></thead>",
" style='padding:5px;border:1px solid #BBB'");
Dictionary <string, List <string> > redFlagDetails = new Dictionary <string, List <string> >();
foreach (DataRow dr in dt.Rows)
{
sbCurrent.AppendFormat("<tr><th style='padding:5px;text-align:left;border:1px solid #BBB;'>{0}</th><td{12}>{1:#,###}</td><td{12}>{2}</td><td{12}>{3}</td><td{12}>{4}</td><td{12}>{5}</td><td{12}>{6:#,###}</td><td{12}>{7:#,###}</td><td{12}>{8:#,###}</td><td{12}>{9:#,###}</td><td{12}>{10:#,###}</td><td{12}>{11}</td></tr>",
dr["ShortCode"],
dr["TotalRecords"],
ReportingTools.FormatIndex(dr["GEI"].ToString()),
ReportingTools.FormatPercent(dr["NPS"].ToString()),
ReportingTools.FormatPercent(dr["PRS"].ToString()),
ReportingTools.FormatPercent(dr["GSEI"].ToString()),
dr["FeedbackCount"], //6
ReportingTools.FormatPercent(dr["FeedbackCompletePercent"].ToString()),
dr["FeedbackLessThan24Hrs"],
dr["Feedback24HrsTo48Hrs"],
dr["FeedbackGreaterThan48Hrs"],
ReportingTools.MinutesToNiceTime(dr["AverageFeedbackResponse"].ToString()),
" style='padding:5px;border:1px solid #BBB;text-align:center'"
);
sbComparison.AppendFormat("<tr><th style='padding:5px;text-align:left;border:1px solid #BBB;'>{0}</th>{1}{2}{3}{4}{5}{6}{7}{8}{9}{10}{11}</tr>",
dr["ShortCode"],
GetChangeCell(dr, "TotalRecords_Diff", String.Format("{0:#,###}", dr["TotalRecords"]), redFlagDetails, null),
GetChangeCell(dr, "GEI_Diff", ReportingTools.FormatIndex(dr["GEI_Diff"].ToString()), redFlagDetails, r => { return(r != -1000 && r <= -10); }),
GetChangeCell(dr, "NPS_Diff", ReportingTools.FormatPercent(dr["NPS_Diff"].ToString()), redFlagDetails, r => { return(r != -1000 && r <= -0.1); }),
GetChangeCell(dr, "PRS_Diff", ReportingTools.FormatPercent(dr["PRS_Diff"].ToString()), redFlagDetails, r => { return(r != -1000 && r <= -0.1); }),
GetChangeCell(dr, "GSEI_Diff", ReportingTools.FormatPercent(dr["GSEI_Diff"].ToString()), redFlagDetails, r => { return(r != -1000 && r <= -0.1); }),
GetChangeCell(dr, "FeedbackCount_Diff", String.Format("{0:#,###}", dr["FeedbackCount_Diff"]), redFlagDetails, null), //6
GetChangeCell(dr, "FeedbackCompletePercent_Diff", ReportingTools.FormatPercent(dr["FeedbackCompletePercent_Diff"].ToString()), redFlagDetails, r => { return(r != -1000 && r <= -0.1); }),
GetChangeCell(dr, "FeedbackLessThan24Hrs_Diff", String.Format("{0:#,###}", dr["FeedbackLessThan24Hrs_Diff"]), redFlagDetails, null),
GetChangeCell(dr, "Feedback24HrsTo48Hrs_Diff", String.Format("{0:#,###}", dr["Feedback24HrsTo48Hrs_Diff"]), redFlagDetails, null),
GetChangeCell(dr, "FeedbackGreaterThan48Hrs_Diff", String.Format("{0:#,###}", dr["FeedbackGreaterThan48Hrs_Diff"]), redFlagDetails, null),
GetChangeCell(dr, "AverageFeedbackResponse_Diff", ReportingTools.MinutesToNiceTime(dr["AverageFeedbackResponse_Diff"].ToString()), redFlagDetails, r => { return(r != -1000 && r >= 120); })
);
}
sbCurrent.Append("</table><br /><br /><br />");
sbComparison.Append("</table>");
StringBuilder sbRedFlags = new StringBuilder();
foreach (var kvp in redFlagDetails)
{
if (sbRedFlags.Length == 0)
{
sbRedFlags.Append("<h3 style='margin:20px 0'>Red Flag Summary</h3><ul>");
}
if (kvp.Key.Length <= 4)
{
//Score
sbRedFlags.AppendFormat("<li><b>{0} score</b> has decreased by at least 10% for the following site(s): ", kvp.Key);
}
else if (kvp.Key.Equals("FeedbackCompletePercent"))
{
//Completion percentage
sbRedFlags.Append("<li><b>Feedback Completion</b> has decreased by at least 10% for the following site(s): ");
}
else if (kvp.Key.Equals("AverageFeedbackResponse"))
{
//Feedback response
sbRedFlags.Append("<li><b>Average Feedback Response</b> has increased by at least 2 hours for the following site(s): ");
}
foreach (string shortCode in kvp.Value)
{
sbRedFlags.AppendFormat("{0}, ", shortCode);
}
sbRedFlags.Remove(sbRedFlags.Length - 2, 2)
.Append("</li>");
}
if (sbRedFlags.Length > 0)
{
sbRedFlags.Append("</ul><br />");
sbCurrent.Insert(0, sbRedFlags.ToString());
}
MailMessage msg = null;
try
{
var replacementModel = new
{
DataTables = sbCurrent.ToString() + sbComparison.ToString()
};
string path = context.Server.MapPath("~/Content/notifications/");
msg = EmailManager.CreateEmailFromTemplate(
Path.Combine(path, "WeeklyNotification.htm"),
replacementModel);
msg.IsBodyHtml = true;
msg.BodyEncoding = System.Text.Encoding.UTF8;
msg.From = new MailAddress("*****@*****.**");
msg.Subject = "GCGC Weekly Status Notification - Week Ending " + endDate.ToString("MMM d, yyyy");
bool hasAddress = false;
dt = sql.QueryDataTable(@"
SELECT [SendType], u.[FirstName], u.[LastName], u.[Email]
FROM [tblNotificationUsers] ne
INNER JOIN [tblNotificationPropertySurveyReason] psr
ON ne.[PropertySurveyReasonID] = psr.[PropertySurveyReasonID]
INNER JOIN [tblCOM_Users] u
ON ne.UserID = u.UserID
WHERE psr.PropertyID = @PropertyID
AND psr.SurveyTypeID = @SurveyID
AND psr.ReasonID = @ReasonID
;",
new SQLParamList()
.Add("@PropertyID", (int)GCCPropertyShortCode.GCC)
.Add("@SurveyID", (int)SurveyType.GEI)
.Add("@ReasonID", (int)NotificationReason.WeeklyStatusNotification)
);
if (!sql.HasError && dt.Rows.Count > 0)
{
StringBuilder addrs = new StringBuilder();
foreach (DataRow dr in dt.Rows)
{
switch (dr["SendType"].ToString())
{
case "1":
msg.To.Add(dr["Email"].ToString());
addrs.Append(dr["FirstName"].ToString() + " " + dr["LastName"].ToString() + " <" + dr["Email"].ToString() + ">" + "\n");
hasAddress = true;
break;
case "2":
msg.CC.Add(dr["Email"].ToString());
//Colin requested that CC addresses not show on the call Aug 10,2015
//addrs.Append( dr["FirstName"].ToString() + " " + dr["LastName"].ToString() + " <" + dr["Email"].ToString() + ">" + "\n" );
hasAddress = true;
break;
case "3":
msg.Bcc.Add(dr["Email"].ToString());
hasAddress = true;
break;
}
}
//using ( StreamReader sr = new StreamReader( msg.AlternateViews[0].ContentStream ) ) {
// msg.AlternateViews[0] = AlternateView.CreateAlternateViewFromString( sr.ReadToEnd().Replace( "{Recipients}", context.Server.HtmlEncode( addrs.ToString() ).Replace( "\n", "<br />" ) ), null, MediaTypeNames.Text.Html );
//}
}
if (hasAddress)
{
msg.Send();
}
}
catch (Exception ex)
{
ErrorHandler.WriteLog("GCC_Web_Portal.Jobs", "Error running job.", ErrorHandler.ErrorEventID.General, ex);
}
finally
{
if (msg != null)
{
msg.Dispose();
msg = null;
}
}
return;
#endregion Send weekly notification email
case "506aebb3-dfa2-4b34-94bc-51e81f5f31d3":
#region Send Feedback Reminder Email
sql = new SQLDatabase();
dt = sql.ExecStoredProcedureDataTable("[spJobs_48HrReminder]");
foreach (DataRow dr in dt.Rows)
{
GCCPropertyShortCode sc;
SurveyType st;
NotificationReason nr;
DateTime created = Conversion.XMLDateToDateTime(dr["DateCreated"].ToString());
string feedbackUID = dr["UID"].ToString();
if (Enum.TryParse(dr["PropertyID"].ToString(), out sc) &&
Enum.TryParse(dr["SurveyTypeID"].ToString(), out st) &&
Enum.TryParse(dr["ReasonID"].ToString(), out nr))
{
switch (st)
{
case SurveyType.GEI:
string gagLocation = dr["GEIGAGLocation"].ToString();
if (gagLocation.Length > 0)
{
gagLocation = " - " + gagLocation;
}
string fbLink = GCCPortalUrl + "Admin/Feedback/" + feedbackUID;
SurveyTools.SendNotifications(HttpContext.Current.Server, sc, st, nr, string.Empty,
new
{
Date = created.ToString("yyyy-MM-dd"),
CasinoName = PropertyTools.GetCasinoName((int)sc) + gagLocation,
Problems = (dr["Q27"].Equals(1) ? "Yes" : "No"),
Response = (dr["Q40"].Equals(1) ? "Yes" : "No"),
ProblemDescription = dr["Q27B"].ToString(),
StaffComment = dr["Q11"].ToString(),
GeneralComments = dr["Q34"].ToString(),
MemorableEmployee = dr["Q35"].ToString(),
FeedbackLinkTXT = (String.IsNullOrWhiteSpace(feedbackUID) ? String.Empty : "\n\nRespond to this feedback:\n" + fbLink + "\n"),
FeedbackLinkHTML = (String.IsNullOrWhiteSpace(feedbackUID) ? String.Empty : @"<br /><br />
<p><b>Respond to this feedback:</b></p>
<p>" + fbLink + "</p>"),
SurveyLink = GCCPortalUrl + "Display/GEI/" + dr["RecordID"].ToString()
},
String.Empty,
"Overdue: ");
break;
case SurveyType.Hotel:
SurveyTools.SendNotifications(HttpContext.Current.Server, sc, st, nr, string.Empty,
new
{
CasinoName = PropertyTools.GetCasinoName((int)sc),
FeedbackNoteHTML = "<p><b>This guest has requested a response.</b> You can view and respond to the feedback request by clicking on (or copying and pasting) the following link:</p>\n<p>" + GCCPortalUrl + "Admin/Feedback/" + feedbackUID + "</p>",
FeedbackNoteTXT = "The guest requested feedback. You can view and respond to the feedback request by clicking on (or copying and pasting) the following link:\n" + GCCPortalUrl + "Admin/Feedback/" + feedbackUID + "\n\n",
SurveyLink = GCCPortalUrl + "Display/Hotel/" + dr["RecordID"].ToString()
},
String.Empty,
"Overdue: ");
break;
case SurveyType.Feedback:
gagLocation = dr["FBKGAGLocation"].ToString();
if (gagLocation.Length > 0)
{
gagLocation = " - " + gagLocation;
}
SurveyTools.SendNotifications(HttpContext.Current.Server, sc, st, nr, string.Empty,
new
{
Date = created.ToString("yyyy-MM-dd"),
CasinoName = PropertyTools.GetCasinoName((int)sc) + gagLocation,
FeedbackNoteHTML = "<p><b>This guest has requested a response.</b> You can view and respond to the feedback request by clicking on (or copying and pasting) the following link:</p>\n<p>" + GCCPortalUrl + "Admin/Feedback/" + feedbackUID + "</p>",
FeedbackNoteTXT = "The guest requested feedback. You can view and respond to the feedback request by clicking on (or copying and pasting) the following link:\n" + GCCPortalUrl + "Admin/Feedback/" + feedbackUID + "\n\n",
SurveyLink = GCCPortalUrl + "Display/Feedback/" + dr["RecordID"].ToString()
},
String.Empty,
"Overdue: ");
break;
case SurveyType.Donation:
SurveyTools.SendNotifications(HttpContext.Current.Server, sc, st, nr, string.Empty,
new
{
Date = created.ToString("yyyy-MM-dd"),
CasinoName = PropertyTools.GetCasinoName((int)sc),
FeedbackLink = GCCPortalUrl + "Admin/Feedback/" + feedbackUID,
Link = GCCPortalUrl + "Display/Donation/" + dr["RecordID"].ToString()
},
String.Empty,
"Overdue: ");
break;
}
}
}
return;
#endregion Send Feedback Reminder Email
}
context.Response.ContentType = "text/plain";
context.Response.Write("Invalid Job ID.");
}
protected void btnExport_Click(object sender, EventArgs e)
{
SQLDatabase sql = new SQLDatabase(); sql.CommandTimeout = 120;
sql.CommandTimeout = 90;
DataTable dt = sql.ExecStoredProcedureDataTable("spReports_Quarterly");
if (sql.HasError)
{
TopMessage.ErrorMessage = "Unable to query report data from the database.";
return;
}
using (ExcelPackage p = new ExcelPackage())
{
p.Workbook.Worksheets.Add("Annual Results");
ExcelWorksheet worksheet = p.Workbook.Worksheets[1];
worksheet.Cells.Style.Font.Size = 10; //Default font size for whole sheet
worksheet.Cells.Style.Font.Name = "Calibri"; //Default Font name for whole sheet
worksheet.Cells.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
worksheet.Cells.Style.Fill.BackgroundColor.SetColor(Color.White);
worksheet.Column(1).Width = 12.71f;
worksheet.Column(2).Width = 54.85f;
//Sheet title
worksheet.Cells["A1"].Value = "GCGC Gaming Report";
using (ExcelRange r = worksheet.Cells["A1:B7"])
{
r.Merge = true;
r.Style.Font.Size = 14;
r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.CenterContinuous;
r.Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
r.Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium;
}
DataTable dtTransposed = dt.TransposeDataTable();
Dictionary <string, DataRow> lookup = new Dictionary <string, DataRow>();
foreach (DataRow dr in dtTransposed.Rows)
{
lookup.Add(dr["Quarter"].ToString(), dr);
}
int rowNum = 8;
worksheet.View.FreezePanes(rowNum, 3);
AddDataRow(worksheet, lookup, rowNum++, true, "SAMPLE SIZE", "SampleCount", CellFormat.Number);
AddDataRow(worksheet, lookup, rowNum++, true, "GUEST EXPERIENCE INDEX", null);
AddDataRow(worksheet, lookup, rowNum++, false, "GEI", "GEI", CellFormat.Index);
AddDataRow(worksheet, lookup, rowNum++, true, "GUEST SERVICE EXPERIENCE INDEX", null);
AddDataRow(worksheet, lookup, rowNum++, false, "GSEI", "GSEI");
AddDataRow(worksheet, lookup, rowNum++, true, "NET PROMOTER SCORE", null);
AddDataRow(worksheet, lookup, rowNum++, false, "NPS", "NPS");
AddDataRow(worksheet, lookup, rowNum++, true, "PROBLEM RESOLUTION SCORE", null);
AddDataRow(worksheet, lookup, rowNum++, false, "PRS", "PRS");
AddDataRow(worksheet, lookup, rowNum++, true, "GUEST LOYALTY", null);
AddDataRow(worksheet, lookup, rowNum++, false, "Likely to recommend the casino", "Q6A");
AddDataRow(worksheet, lookup, rowNum++, false, "Likely to mostly visit this casino", "Q6B");
AddDataRow(worksheet, lookup, rowNum++, false, "Likely to visit this casino for next gaming entertainment opportunity", "Q6C");
AddDataRow(worksheet, lookup, rowNum++, false, "Likely to provide personal preferences so casino can serve me better", "Q6D");
AddDataRow(worksheet, lookup, rowNum++, true, "CASINO STAFF", null);
AddDataRow(worksheet, lookup, rowNum++, false, "Ensuring all of your needs were met", "Q7A");
AddDataRow(worksheet, lookup, rowNum++, false, "Making you feel welcome", "Q7B");
AddDataRow(worksheet, lookup, rowNum++, false, "Going above & beyond normal service", "Q7C");
AddDataRow(worksheet, lookup, rowNum++, false, "Speed of service", "Q7D");
AddDataRow(worksheet, lookup, rowNum++, false, "Encouraging you to visit again", "Q7E");
AddDataRow(worksheet, lookup, rowNum++, false, "Overall staff availability", "Q7F");
AddDataRow(worksheet, lookup, rowNum++, false, "Overall staff", null);
AddDataRow(worksheet, lookup, rowNum++, false, "Cashiers", "Q9A", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Guest Services", "Q9B", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Slot Attendants", "Q9C", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Dealers", "Q9D", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Restaurant Servers", "Q9E", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Cocktail Servers", "Q9F", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Coffee Servers", "Q9G", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Security", "Q9H", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Managers/Supervisors", "Q9I", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Hotel Staff", "Q9J", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Attribute ratings:", null);
AddDataRow(worksheet, lookup, rowNum++, false, "Encouraging you to take part in events or promotions", "Q10A", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Answering questions you had about the property or promotions", "Q10B", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Being friendly and welcoming", "Q10C", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, true, "CASINO FACILITIES", null);
AddDataRow(worksheet, lookup, rowNum++, false, "Overall facilities", "Q12");
AddDataRow(worksheet, lookup, rowNum++, false, "Attribute ratings:", null);
AddDataRow(worksheet, lookup, rowNum++, false, "Ambiance, mood, atmosphere of the environment", "Q13A", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Cleanliness of general areas", "Q13B", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Clear signage", "Q13C", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Washroom cleanliness", "Q13D", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Adequate lighting - it is bright enough", "Q13E", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Safe environment", "Q13F", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Parking availability", "Q13G", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, true, "GAMING EXPERIENCE", null);
AddDataRow(worksheet, lookup, rowNum++, false, "Primary gaming:", null);
AddDataRow(worksheet, lookup, rowNum++, false, "Playing Slots", "Count_Slots", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Playing Tables", "Count_Tables", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Playing Poker", "Count_Poker", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Enjoying Food or Beverages", "Count_Food", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Watching Live Entertainment at a show lounge or theatre", "Count_Entertainment", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Staying at our Hotel", "Count_Hotel", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Watching Live Racing", "Count_LiveRacing", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Watching Racing at our Racebook", "Count_Racebook", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Playing Bingo", "Count_Bingo", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Lottery / Pull Tabs", "Count_Lottery", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "None", "Count_None", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Primary gaming:", "Q14");
AddDataRow(worksheet, lookup, rowNum++, false, "Attribute ratings:", null);
AddDataRow(worksheet, lookup, rowNum++, false, "Variety of games available", "Q15A", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Waiting time to play", "Q15B", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Availability of specific game at your desired denomination", "Q15C", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Contests & monthly promotions", "Q15D", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Courtesy & respectfulness of staff", "Q15E", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Game Knowledge of Staff", "Q15F", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Rate of earning", "Q16A", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Redemption value", "Q16B", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Choice of rewards", "Q16C", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Slot Free Play", "Q16D", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, true, "FOOD & BEVERAGE", null);
rowNum = AddYesNoRow(worksheet, lookup, rowNum++, false, "Purchase food or beverages?", "Q17");
AddDataRow(worksheet, lookup, rowNum++, false, "Overall dining experience", "Q19");
AddDataRow(worksheet, lookup, rowNum++, false, "Attribute ratings:", null);
AddDataRow(worksheet, lookup, rowNum++, false, "Variety of food choices", "Q20A", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Cleanliness of outlet", "Q20B", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Courtesy of staff", "Q20C", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Timely delivery of order", "Q20D", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Value for the money", "Q20E", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Pleasant atmosphere", "Q20F", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Quality of food", "Q20G", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, true, "LOUNGE ENTERTAINMENT", null);
rowNum = AddYesNoRow(worksheet, lookup, rowNum++, false, "Attend Lounge entertainment?", "Q21");
AddDataRow(worksheet, lookup, rowNum++, false, "Overall entertainment experience", "Q22");
AddDataRow(worksheet, lookup, rowNum++, false, "Attribute ratings:", null);
AddDataRow(worksheet, lookup, rowNum++, false, "Sound / quality", "Q23A", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Seating availability", "Q23B", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Dance floor", "Q23C", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Fun and enjoyable atmosphere", "Q23D", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, true, "THEATRE", null);
rowNum = AddYesNoRow(worksheet, lookup, rowNum++, false, "Attend Theatre?", "Q24");
AddDataRow(worksheet, lookup, rowNum++, false, "Overall Theatre experience", "Q25");
AddDataRow(worksheet, lookup, rowNum++, false, "Attribute ratings:", null);
AddDataRow(worksheet, lookup, rowNum++, false, "The quality of the show", "Q26A", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "The value of the show", "Q26B", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Seating choices", "Q26C", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Sound quality", "Q26D", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
//AddDataRow( worksheet, lookup, rowNum++, false, "Overall customer service of Theatre staff", "Q26E", CellFormat.Percent, r => { r.Style.Indent = 3; }, null );
AddDataRow(worksheet, lookup, rowNum++, true, "SERVICE RECOVERY", null);
rowNum = AddYesNoRow(worksheet, lookup, rowNum++, false, "Experience problem?", "Q27");
AddDataRow(worksheet, lookup, rowNum++, false, "Where experienced problem?", null);
AddDataRow(worksheet, lookup, rowNum++, false, "Arrival and parking", "Q27A_ArrivalAndParking", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Guest Services", "Q27A_GuestServices", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Cashiers", "Q27A_Cashiers", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Manager/Supervisor", "Q27A_ManagerSupervisor", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Security", "Q27A_Security", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Slots", "Q27A_Slots", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Tables", "Q27A_Tables", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Food & Beverage", "Q27A_FoodAndBeverage", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Hotel", "Q27A_Hotel", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Other", "Q27A_Other", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
rowNum = AddYesNoRow(worksheet, lookup, rowNum++, false, "Resolve problem?", "Q28");
rowNum = AddYesNoRow(worksheet, lookup, rowNum++, false, "Report problem?", "Q29");
AddDataRow(worksheet, lookup, rowNum++, false, "Overall ability to fix problem", "Q30");
AddDataRow(worksheet, lookup, rowNum++, false, "Attribute ratings:", null);
AddDataRow(worksheet, lookup, rowNum++, false, "The length of time taken to resolve your problem", "Q31A", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "The effort of employees in resolving your problem", "Q31B", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "The courteousness of employees while resolving your problem", "Q31C", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
worksheet.Row(rowNum).Height *= 2;
AddDataRow(worksheet, lookup, rowNum++, false, "The amount of communication with you from employees while resolving your problem", "Q31D", CellFormat.Percent, r => { r.Style.WrapText = true; r.Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center; r.Style.Indent = 3; }, r => { r.Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center; });
AddDataRow(worksheet, lookup, rowNum++, false, "The fairness of the outcome in resolving your problem", "Q31E", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, true, "DEMOGRAPHICS", null);
AddDataRow(worksheet, lookup, rowNum++, false, "Male", "Q36_Male");
AddDataRow(worksheet, lookup, rowNum++, false, "Female", "Q36_Female");
AddDataRow(worksheet, lookup, rowNum++, false, String.Empty, null);
AddDataRow(worksheet, lookup, rowNum++, false, "19-24", "Q37_19-24");
AddDataRow(worksheet, lookup, rowNum++, false, "25-34", "Q37_25-34");
AddDataRow(worksheet, lookup, rowNum++, false, "35-44", "Q37_35-44");
AddDataRow(worksheet, lookup, rowNum++, false, "45-54", "Q37_45-54");
AddDataRow(worksheet, lookup, rowNum++, false, "55-64", "Q37_55-64");
AddDataRow(worksheet, lookup, rowNum++, false, "65 or older", "Q37_65 or older");
AddDataRow(worksheet, lookup, rowNum++, false, String.Empty, null);
AddDataRow(worksheet, lookup, rowNum++, false, "First visit", "Q38_This was my first visit");
AddDataRow(worksheet, lookup, rowNum++, false, "2-7 times per week", "Q38_2-7 times per week");
AddDataRow(worksheet, lookup, rowNum++, false, "Once per week", "Q38_Once per week");
AddDataRow(worksheet, lookup, rowNum++, false, "2-3 times per month", "Q38_2-3 times per month");
AddDataRow(worksheet, lookup, rowNum++, false, "Once per month", "Q38_Once per month");
AddDataRow(worksheet, lookup, rowNum++, false, "Several times a year", "Q38_Several times a year");
AddDataRow(worksheet, lookup, rowNum++, false, "Languages spoken at home (other than English)", null);
AddDataRow(worksheet, lookup, rowNum++, false, "Korean", "Q39_1", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Punjabi", "Q39_2", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Chinese Mandarin", "Q39_3", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Other Western European languages", "Q39_4", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Eastern European languages", "Q39_5", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Spanish", "Q39_6", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "French", "Q39_7", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Hindi", "Q39_8", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Tagalog", "Q39_9", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Vietnamese", "Q39_10", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Pakistani", "Q39_11", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Farsi", "Q39_12", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Japanese", "Q39_13", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Arabic / Middle Eastern", "Q39_14", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Chinese – Cantonese", "Q39_15", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Other", "Q39_16", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, String.Empty, null);
rowNum = AddYesNoRow(worksheet, lookup, rowNum++, false, "Players Club?", "Q4");
rowNum++; //Skip one row
//Handle variance rating
foreach (int varCol in _varianceLookup.Keys)
{
double rows = _varianceLookup[varCol].Count;
double i = 1;
//Order each variance value by the amt in descending order
foreach (KeyValuePair <int, double> v in _varianceLookup[varCol].OrderByDescending(x => x.Value))
{
//varCol = column number
//v.Key = row number
//v.Value = variance value
if (i / rows <= 0.25)
{
//Pick the top 25 percent for "High"
using (ExcelRange r = worksheet.Cells[v.Key, varCol])
{
r.Value = "High";
r.Style.Font.Color.SetColor(Color.FromArgb(190, 0, 6));
r.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
r.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(255, 199, 206));
}
}
else if (i / rows > 0.25 && i / rows <= 0.55)
{
//Pick the mid 30 percent for "Medium"
using (ExcelRange r = worksheet.Cells[v.Key, varCol])
{
r.Value = "Medium";
r.Style.Font.Color.SetColor(Color.FromArgb(156, 101, 0));
r.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
r.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(255, 235, 156));
}
}
else
{
//Pick the last 45 percent for "Low"
using (ExcelRange r = worksheet.Cells[v.Key, varCol])
{
r.Value = "Low";
}
}
i++;
}
}
//Add notes/legend info
int noteStartRow = rowNum;
AddMergedCell(worksheet, rowNum, 1, 6, "NOTES:", r =>
{
r.Style.Font.Bold = true;
r.Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
r.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
r.Style.Fill.BackgroundColor.SetColor(Color.LightGray);
});
AddMergedCell(worksheet, rowNum++, 7, 22, "LEGEND", r =>
{
r.Style.Font.Bold = true;
r.Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
r.Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
r.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
r.Style.Fill.BackgroundColor.SetColor(Color.LightGray);
});
AddMergedCell(worksheet, rowNum, 1, 6, "SELECTION PERIOD", r => { r.Style.Font.Bold = true; });
AddMergedCell(worksheet, rowNum++, 7, 22, " - Net Promoter Score (NPS) = difference in percentage of top two box and bottom two box to the 'likelihood to recommend' question.");
AddMergedCell(worksheet, rowNum, 1, 6, "Guests are selected based on their last visit at each site in the past week prior to selection. The interval between surveys must be at least 90 days.", r => { r.Style.WrapText = true; });
AddMergedCell(worksheet, rowNum++, 7, 22, " - Guest Experience Index (GEI) = weighted average of responses to Guest Loyalty questions converted to a 100 pt equal interval scale.");
AddMergedCell(worksheet, rowNum, 1, 6, "SAMPLE SIZE", r => { r.Style.Font.Bold = true; });
AddMergedCell(worksheet, rowNum++, 7, 22, " - Problem Resolution Score (PRS) = of those who had a problem, top two box percentage of \"overall ability to fix problem\" question.");
AddMergedCell(worksheet, rowNum, 1, 6, "Sample Size only takes into account completed surveys. For a statistically significant survey report and margin of error within reasonable confidence, we recommend a minimum sample size of 300 respondents per reporting period.", r => { r.Style.WrapText = true; });
AddMergedCell(worksheet, rowNum++, 7, 22, " - 5 point scales used:");
AddMergedCell(worksheet, rowNum, 1, 6, "REPORTING PERIOD", r => { r.Style.Font.Bold = true; });
AddMergedCell(worksheet, rowNum++, 7, 22, " RATIONAL CONNECTIONS, TOUCHPOINTS, ATTRIBUTES: excellent, very good, good, fair, poor");
AddMergedCell(worksheet, rowNum, 1, 6, "Given the latest results, we will continue to collect and tabulate results on a weekly/monthly basis, but reporting will be grouped quarterly.", r => { r.Style.WrapText = true; });
AddMergedCell(worksheet, rowNum++, 7, 22, " EMOTIONAL CONNECTIONS, BRAND CONNECTIONS: strongly agree,moderately agree, slightly agree,disagree, strongly disagree");
AddMergedCell(worksheet, rowNum, 1, 6, "BENCHMARK", r => { r.Style.Font.Bold = true; });
AddMergedCell(worksheet, rowNum++, 7, 22, " GUEST LOYALTY: definitely would, probably would, possibly would, probably would not, definitely would not");
AddMergedCell(worksheet, rowNum, 1, 6, "The program started on March 19, 2012. Q2 Results will provide the basis for a comparative benchmark, representing the first full quarter of data collection (Apr/September/September).", r => { r.Style.WrapText = true; });
AddMergedCell(worksheet, rowNum++, 7, 22, " - Performance Score %: Top Two Box % = excellent+very good", r => { r.Style.Font.Bold = true; });
AddMergedCell(worksheet, rowNum, 1, 6, String.Empty, r => { r.Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; });
AddMergedCell(worksheet, rowNum++, 7, 22, " - GCGC numbers are total for all properties ", r => { r.Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; });
using (ExcelRange r = worksheet.Cells[noteStartRow + 1, 1, rowNum - 1, 22])
{
r.Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
}
string fileName = "GCGC-QuarterlyReport-" + ReportingTools.AdjustAndDisplayDate(DateTime.Now, "yyyy-MM-dd-hh-mm-ss", User) + ".xlsx";
const string lPath = "~/Files/Cache/";
string lOutput = string.Concat(MapPath(lPath), fileName);
FileInfo fi = new FileInfo(lOutput);
p.SaveAs(fi);
hlDownload.Text = "Download File - " + fileName;
hlDownload.NavigateUrl = String.Format("{0}{1}", lPath, fileName);
}
}
protected void GenerateReport()
{
SQLDatabase sql = new SQLDatabase(); sql.CommandTimeout = 120;
sql.CommandTimeout = 90;
DataSet ds = sql.ExecStoredProcedureDataSet("spReports_FeedbackReport_2",
new SqlParameter("@MonthStart", ddlMonth.SelectedValue + "-01"),
new SqlParameter("@PropertyID", ddlProperty.SelectedValue));
//DataSet ds = sql.ExecStoredProcedureDataSet("spReports_FeedbackReport",
// new SqlParameter("@MonthStart", ddlMonth.SelectedValue + "-01"),
// new SqlParameter("@PropertyID", ddlProperty.SelectedValue));
using (ExcelPackage package = new ExcelPackage())
{
GCCPropertyShortCode sc = (GCCPropertyShortCode)ddlProperty.SelectedValue.StringToInt(0);
string fileName = sc.ToString() + "-FeedbackExport-" + ReportingTools.AdjustAndDisplayDate(DateTime.Now, "yyyy-MM-dd-hh-mm-ss", User);
package.Workbook.Worksheets.Add("GEI Survey");
ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
worksheet.Cells.Style.Font.Size = 10;
worksheet.Cells.Style.Font.Name = "Calibri";
//set column widths
worksheet.Column(1).Width = 23f;
worksheet.Column(2).Width = 11.3f;
worksheet.Column(3).Width = 11.3f;
worksheet.Column(4).Width = 11.3f;
worksheet.Column(5).Width = 11.3f;
worksheet.Column(6).Width = 21.86f;
worksheet.Column(7).Width = 21.86f;
worksheet.Column(8).Width = 9.2f;
worksheet.Column(9).Width = 31.86f;
worksheet.Column(10).Width = 10.5f;
worksheet.Column(11).Width = 21.7f;
worksheet.Column(12).Width = 25f;
worksheet.Column(13).Width = 10.5f;
worksheet.Column(14).Width = 30f;
worksheet.Column(15).Width = 15f;
DataTable DT = ds.Tables[0];
int colNum = 1;
int rowNum = 1;
foreach (DataColumn col in DT.Columns)
{
worksheet.Cells[rowNum, colNum].Value = col.ColumnName;
colNum++;
}
rowNum++;
foreach (DataRow DR in DT.Rows)
{
colNum = 1;
foreach (DataColumn col in DT.Columns)
{
worksheet.Cells[rowNum, colNum].Value = DR[col.ColumnName];
colNum++;
}
rowNum++;
}
#region Feedback Survey Tab
package.Workbook.Worksheets.Add("Feedback Survey");
worksheet = package.Workbook.Worksheets[2];
worksheet.Cells.Style.Font.Size = 10;
worksheet.Cells.Style.Font.Name = "Calibri";
DT = ds.Tables[1];
colNum = 1;
rowNum = 1;
foreach (DataColumn col in DT.Columns)
{
worksheet.Cells[rowNum, colNum].Value = col.ColumnName;
colNum++;
}
rowNum++;
foreach (DataRow DR in DT.Rows)
{
colNum = 1;
foreach (DataColumn col in DT.Columns)
{
worksheet.Cells[rowNum, colNum].Value = DR[col.ColumnName];
colNum++;
}
rowNum++;
}
#endregion Feedback Survey Tab
#region Hotel Survey Tab
package.Workbook.Worksheets.Add("Hotel Survey");
worksheet = package.Workbook.Worksheets[3];
worksheet.Cells.Style.Font.Size = 10;
worksheet.Cells.Style.Font.Name = "Calibri";
DT = ds.Tables[2];
colNum = 1;
rowNum = 1;
foreach (DataColumn col in DT.Columns)
{
worksheet.Cells[rowNum, colNum].Value = col.ColumnName;
colNum++;
}
rowNum++;
foreach (DataRow DR in DT.Rows)
{
colNum = 1;
foreach (DataColumn col in DT.Columns)
{
worksheet.Cells[rowNum, colNum].Value = DR[col.ColumnName];
colNum++;
}
rowNum++;
}
#endregion Hotel Survey Tab
#region Donation Survey Tab
package.Workbook.Worksheets.Add("Donation Survey");
worksheet = package.Workbook.Worksheets[4];
worksheet.Cells.Style.Font.Size = 10;
worksheet.Cells.Style.Font.Name = "Calibri";
DT = ds.Tables[3];
colNum = 1;
rowNum = 1;
foreach (DataColumn col in DT.Columns)
{
worksheet.Cells[rowNum, colNum].Value = col.ColumnName;
colNum++;
}
rowNum++;
foreach (DataRow DR in DT.Rows)
{
colNum = 1;
foreach (DataColumn col in DT.Columns)
{
worksheet.Cells[rowNum, colNum].Value = DR[col.ColumnName];
colNum++;
}
rowNum++;
}
#endregion Donation Survey Tab
Response.Clear();
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", String.Format("attachment; filename={0}.xlsx", fileName));
package.SaveAs(Response.OutputStream);
worksheet.Dispose();
Response.End();
}
}
protected void btnExport_Click(object sender, EventArgs e)
{
SQLDatabase sql = new SQLDatabase(); sql.CommandTimeout = 120;
sql.CommandTimeout = 90;
DataTable dt = sql.ExecStoredProcedureDataTable("spReports_Monthly",
new SqlParameter("@MonthStart", ddlMonth.SelectedValue + "-01"),
new SqlParameter("@PropertyID", ddlProperty.SelectedValue));
if (sql.HasError)
{
TopMessage.ErrorMessage = "Unable to query report data from the database.";
return;
}
using (ExcelPackage p = new ExcelPackage())
{
string[] date = ddlMonth.SelectedValue.Split('-');
DateTime mon = new DateTime(date[0].StringToInt(2017), date[1].StringToInt(1), 1);
GCCPropertyShortCode sc = (GCCPropertyShortCode)ddlProperty.SelectedValue.StringToInt(0);
p.Workbook.Worksheets.Add(PropertyTools.GetCasinoName((int)sc));
ExcelWorksheet worksheet = p.Workbook.Worksheets[1];
worksheet.Cells.Style.Font.Size = 10; //Default font size for whole sheet
worksheet.Cells.Style.Font.Name = "Calibri"; //Default Font name for whole sheet
worksheet.Cells.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
worksheet.Cells.Style.Fill.BackgroundColor.SetColor(Color.White);
worksheet.Column(1).Width = 12.71f;
worksheet.Column(2).Width = 54.85f;
worksheet.Column(3).Width = 10f;
worksheet.Column(4).Width = 15.42f;
worksheet.Column(5).Width = 14.57f;
worksheet.Column(6).Width = 19.28f;
worksheet.Column(7).Width = 4.14f;
worksheet.Column(8).Width = 10.71f;
worksheet.Column(9).Width = 10.71f;
worksheet.Column(10).Width = 10.71f;
worksheet.Column(11).Width = 10.71f;;
//Sheet title
worksheet.Cells["A1"].Value = PropertyTools.GetCasinoName((int)sc).ToUpper() + " GUEST EXPERIENCE REPORT";
using (ExcelRange r = worksheet.Cells["A1:B8"])
{
r.Merge = true;
r.Style.Font.Size = 14;
r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.CenterContinuous;
r.Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Bottom;
}
worksheet.Cells["A9"].Value = String.Format("REPORTING PERIOD: {0}", mon.ToString("MMMM, yyyy"));
using (ExcelRange r = worksheet.Cells["A9:B14"])
{
r.Merge = true;
r.Style.Font.Size = 14;
r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.CenterContinuous;
r.Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Top;
}
//Legend
worksheet.Cells["C1"].Value = "LEGEND";
using (ExcelRange r = worksheet.Cells["C1:K1"])
{
r.Merge = true;
r.Style.Font.Size = 12;
r.Style.Font.Bold = true;
r.Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Top;
r.Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium;
r.Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium;
}
AddMergedCell(worksheet, "C2", "K2", "- Net Promoter Score (NPS) = difference in percentage of top two box and bottom two box to the 'likelihood to recommend' question.", r => { r.Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium; r.Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium; });
AddMergedCell(worksheet, "C3", "K3", "- Guest Experience Index (GEI) = weighted average of responses to Guest Loyalty questions converted to a 100 point equal interval scale.", r => { r.Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium; r.Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium; });
AddMergedCell(worksheet, "C4", "K4", "- Problem Resolution Score (PRS) = of those who had a problem, top two box percentage of \"overall ability to fix problem\" question.", r => { r.Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium; r.Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium; });
AddMergedCell(worksheet, "C5", "K5", "- 5 point scales used:", r => { r.Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium; r.Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium; });
AddMergedCell(worksheet, "C6", "K6", "RATIONAL CONNECTIONS, TOUCHPOINTS, ATTRIBUTES: excellent, very good, good, fair, poor", r => { r.Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium; r.Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium; r.Style.Indent = 2; });
AddMergedCell(worksheet, "C7", "K7", "EMOTIONAL CONNECTIONS, BRAND CONNECTIONS: strongly agree,moderately agree, slightly agree,disagree, strongly disagree", r => { r.Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium; r.Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium; r.Style.Indent = 2; });
AddMergedCell(worksheet, "C8", "K8", "GUEST LOYALTY: definitely would, probably would, possibly would, probably would not, definitely would not", r => { r.Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium; r.Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium; r.Style.Indent = 2; });
AddMergedCell(worksheet, "C9", "K9", "- Performance Score %: Top Two Box % = excellent+very good; strongly agree+moderately agree; definitely would +probably would", r => { r.Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium; r.Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium; });
AddMergedCell(worksheet, "C10", "K10", "- GCGC numbers are total for all properties ", r => { r.Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium; r.Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium; r.Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium; });
AddMergedCell(worksheet, "C12", "D12", "Green: +10% change or more", r => r.Style.Font.Bold = true);
AddMergedCell(worksheet, "E12", "F12", "Red: -10% change or more", r => r.Style.Font.Bold = true);
float titleFontSize = 10f;
AddMergedCell(worksheet, "C14", "C15", "CURRENT PERIOD", r =>
{
r.Style.WrapText = true;
r.Style.Font.Bold = true;
r.Style.Font.Size = titleFontSize;
r.Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
r.Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
r.Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
});
AddMergedCell(worksheet, "D14", "F14", "VARIANCE FROM", r =>
{
r.Style.Font.Bold = true;
r.Style.Font.Size = titleFontSize;
r.Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);
r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
});
AddMergedCell(worksheet, "H14", "K14", "PERFORMANCE " + "2015", r =>
{
r.Style.Font.Bold = true;
r.Style.Font.Size = titleFontSize;
r.Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);
r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
});
AddMergedCell(worksheet, "A15", "B15", "Total Sample:", r =>
{
r.Style.Font.Bold = true;
r.Style.Font.Size = titleFontSize;
r.Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Right;
});
using (ExcelRange r = worksheet.Cells["D15"])
{
r.Value = "PREVIOUS PERIOD";
r.Style.Font.Size = titleFontSize;
r.Style.Font.Bold = true;
r.Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
}
using (ExcelRange r = worksheet.Cells["E15"])
{
r.Value = "YEAR AGO";
r.Style.Font.Size = titleFontSize;
r.Style.Font.Bold = true;
r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
}
using (ExcelRange r = worksheet.Cells["F15"])
{
r.Value = "CURRENT PERIOD GCGC";
r.Style.Font.Size = titleFontSize;
r.Style.Font.Bold = true;
r.Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
}
using (ExcelRange r = worksheet.Cells["H15"])
{
r.Value = "Q1/FY15"; //+mon.ToString("yy");
r.Style.Font.Size = titleFontSize;
r.Style.Font.Bold = true;
r.Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
}
using (ExcelRange r = worksheet.Cells["I15"])
{
r.Value = "Q2/FY15"; //+mon.ToString("yy");
r.Style.Font.Size = titleFontSize;
r.Style.Font.Bold = true;
r.Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
}
using (ExcelRange r = worksheet.Cells["J15"])
{
r.Value = "Q3/FY15"; // + mon.ToString("yy");
r.Style.Font.Size = titleFontSize;
r.Style.Font.Bold = true;
r.Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
}
using (ExcelRange r = worksheet.Cells["K15"])
{
r.Value = "Q4/FY15"; //+mon.ToString("yy");
r.Style.Font.Size = titleFontSize;
r.Style.Font.Bold = true;
r.Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
r.Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
}
//Set up the lookup dictionary for getting rows when they're not all there
Dictionary <int, DataRow> lookup = new Dictionary <int, DataRow>();
foreach (DataRow dr in dt.Rows)
{
int rowid = dr["DateRange"].ToString()[0].ToString().StringToInt();
lookup.Add(rowid, dr);
}
int rowNum = 16;
AddDataRow(worksheet, lookup, rowNum++, true, "SAMPLE SIZE", "SampleCount", CellFormat.Number);
AddDataRow(worksheet, lookup, rowNum++, true, "GUEST EXPERIENCE INDEX", null);
AddDataRow(worksheet, lookup, rowNum++, false, "GEI", "GEI", CellFormat.Index);
AddDataRow(worksheet, lookup, rowNum++, true, "GUEST SERVICE EXPERIENCE INDEX", null);
AddDataRow(worksheet, lookup, rowNum++, false, "GSEI", "GSEI");
AddDataRow(worksheet, lookup, rowNum++, true, "NET PROMOTER SCORE", null);
AddDataRow(worksheet, lookup, rowNum++, false, "NPS", "NPS");
AddDataRow(worksheet, lookup, rowNum++, true, "PROBLEM RESOLUTION SCORE", null);
AddDataRow(worksheet, lookup, rowNum++, false, "PRS", "PRS");
AddDataRow(worksheet, lookup, rowNum++, true, "GUEST LOYALTY", null);
AddDataRow(worksheet, lookup, rowNum++, false, "Likely to recommend the casino", "Q6A");
AddDataRow(worksheet, lookup, rowNum++, false, "Likely to mostly visit this casino", "Q6B");
AddDataRow(worksheet, lookup, rowNum++, false, "Likely to visit this casino for next gaming entertainment opportunity", "Q6C");
AddDataRow(worksheet, lookup, rowNum++, false, "Likely to provide personal preferences so casino can serve me better", "Q6D");
AddDataRow(worksheet, lookup, rowNum++, true, "CASINO STAFF", null);
AddDataRow(worksheet, lookup, rowNum++, false, "Ensuring all of your needs were met", "Q7A");
AddDataRow(worksheet, lookup, rowNum++, false, "Making you feel welcome", "Q7B");
AddDataRow(worksheet, lookup, rowNum++, false, "Going above & beyond normal service", "Q7C");
AddDataRow(worksheet, lookup, rowNum++, false, "Speed of service", "Q7D");
AddDataRow(worksheet, lookup, rowNum++, false, "Encouraging you to visit again", "Q7E");
AddDataRow(worksheet, lookup, rowNum++, false, "Overall staff availability", "Q7F");
AddDataRow(worksheet, lookup, rowNum++, false, "Overall staff", null);
AddDataRow(worksheet, lookup, rowNum++, false, "Cashiers", "Q9A", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Guest Services", "Q9B", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Slot Attendants", "Q9C", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Dealers", "Q9D", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Restaurant Servers", "Q9E", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Cocktail Servers", "Q9F", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Coffee Servers", "Q9G", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Security", "Q9H", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Managers/Supervisors", "Q9I", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Hotel Staff", "Q9J", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Attribute ratings:", null);
AddDataRow(worksheet, lookup, rowNum++, false, "Encouraging you to take part in events or promotions", "Q10A", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Answering questions you had about the property or promotions", "Q10B", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Being friendly and welcoming", "Q10C", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, true, "CASINO FACILITIES", null);
AddDataRow(worksheet, lookup, rowNum++, false, "Overall facilities", "Q12");
AddDataRow(worksheet, lookup, rowNum++, false, "Attribute ratings:", null);
AddDataRow(worksheet, lookup, rowNum++, false, "Ambiance, mood, atmosphere of the environment", "Q13A", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Cleanliness of general areas", "Q13B", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Clear signage", "Q13C", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Washroom cleanliness", "Q13D", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Adequate lighting - it is bright enough", "Q13E", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Safe environment", "Q13F", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Parking availability", "Q13G", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, true, "GAMING EXPERIENCE", null);
AddDataRow(worksheet, lookup, rowNum++, false, "Primary gaming:", null);
AddDataRow(worksheet, lookup, rowNum++, false, "Playing Slots", "Count_Slots", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Playing Tables", "Count_Tables", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Playing Poker", "Count_Poker", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Enjoying Food or Beverages", "Count_Food", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Watching Live Entertainment at a show lounge or theatre", "Count_Entertainment", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Staying at our Hotel", "Count_Hotel", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Watching Live Racing", "Count_LiveRacing", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Watching Racing at our Racebook", "Count_Racebook", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Playing Bingo", "Count_Bingo", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Lottery / Pull Tabs", "Count_Lottery", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "None", "Count_None", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Primary gaming:", "Q14");
AddDataRow(worksheet, lookup, rowNum++, false, "Attribute ratings:", null);
AddDataRow(worksheet, lookup, rowNum++, false, "Variety of games available", "Q15A", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Waiting time to play", "Q15B", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Availability of specific game at your desired denomination", "Q15C", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Contests & monthly promotions", "Q15D", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Courtesy & respectfulness of staff", "Q15E", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Game Knowledge of Staff", "Q15F", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Rate of earning", "Q16A", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Redemption value", "Q16B", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Choice of rewards", "Q16C", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Slot Free Play", "Q16D", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, true, "FOOD & BEVERAGE", null);
rowNum = AddYesNoRow(worksheet, lookup, rowNum++, false, "Purchase food or beverages?", "Q17");
AddDataRow(worksheet, lookup, rowNum++, false, "Overall dining experience", "Q19");
AddDataRow(worksheet, lookup, rowNum++, false, "Attribute ratings:", null);
AddDataRow(worksheet, lookup, rowNum++, false, "Variety of food choices", "Q20A", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Cleanliness of outlet", "Q20B", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Courtesy of staff", "Q20C", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Timely delivery of order", "Q20D", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Value for the money", "Q20E", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Pleasant atmosphere", "Q20F", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Quality of food", "Q20G", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, true, "LOUNGE ENTERTAINMENT", null);
rowNum = AddYesNoRow(worksheet, lookup, rowNum++, false, "Attend Lounge entertainment?", "Q21");
AddDataRow(worksheet, lookup, rowNum++, false, "Overall entertainment experience", "Q22");
AddDataRow(worksheet, lookup, rowNum++, false, "Attribute ratings:", null);
AddDataRow(worksheet, lookup, rowNum++, false, "Sound / quality", "Q23A", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Seating availability", "Q23B", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Dance floor", "Q23C", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Fun and enjoyable atmosphere", "Q23D", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, true, "THEATRE", null);
rowNum = AddYesNoRow(worksheet, lookup, rowNum++, false, "Attend Theatre?", "Q24");
AddDataRow(worksheet, lookup, rowNum++, false, "Overall Theatre experience", "Q25");
AddDataRow(worksheet, lookup, rowNum++, false, "Attribute ratings:", null);
AddDataRow(worksheet, lookup, rowNum++, false, "The quality of the show", "Q26A", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "The value of the show", "Q26B", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Seating choices", "Q26C", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Sound quality", "Q26D", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
//AddDataRow( worksheet, lookup, rowNum++, false, "Overall customer service of Theatre staff", "Q26E", CellFormat.Percent, r => { r.Style.Indent = 3; }, null );
AddDataRow(worksheet, lookup, rowNum++, true, "SERVICE RECOVERY", null);
rowNum = AddYesNoRow(worksheet, lookup, rowNum++, false, "Experience problem?", "Q27");
AddDataRow(worksheet, lookup, rowNum++, false, "Where experienced problem?", null);
AddDataRow(worksheet, lookup, rowNum++, false, "Arrival and parking", "Q27A_ArrivalAndParking", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Guest Services", "Q27A_GuestServices", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Cashiers", "Q27A_Cashiers", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Manager/Supervisor", "Q27A_ManagerSupervisor", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Security", "Q27A_Security", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Slots", "Q27A_Slots", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Tables", "Q27A_Tables", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Food & Beverage", "Q27A_FoodAndBeverage", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Hotel", "Q27A_Hotel", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Other", "Q27A_Other", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
rowNum = AddYesNoRow(worksheet, lookup, rowNum++, false, "Resolve problem?", "Q28");
rowNum = AddYesNoRow(worksheet, lookup, rowNum++, false, "Report problem?", "Q29");
AddDataRow(worksheet, lookup, rowNum++, false, "Overall ability to fix problem", "Q30");
AddDataRow(worksheet, lookup, rowNum++, false, "Attribute ratings:", null);
AddDataRow(worksheet, lookup, rowNum++, false, "The length of time taken to resolve your problem", "Q31A", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "The effort of employees in resolving your problem", "Q31B", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "The courteousness of employees while resolving your problem", "Q31C", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
worksheet.Row(rowNum).Height *= 2;
AddDataRow(worksheet, lookup, rowNum++, false, "The amount of communication with you from employees while resolving your problem", "Q31D", CellFormat.Percent, r => { r.Style.WrapText = true; r.Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center; r.Style.Indent = 3; }, r => { r.Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center; });
AddDataRow(worksheet, lookup, rowNum++, false, "The fairness of the outcome in resolving your problem", "Q31E", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, true, "DEMOGRAPHICS", null);
AddDataRow(worksheet, lookup, rowNum++, false, "Male", "Q36_Male");
AddDataRow(worksheet, lookup, rowNum++, false, "Female", "Q36_Female");
AddDataRow(worksheet, lookup, rowNum++, false, String.Empty, null);
AddDataRow(worksheet, lookup, rowNum++, false, "19-24", "Q37_19-24");
AddDataRow(worksheet, lookup, rowNum++, false, "25-34", "Q37_25-34");
AddDataRow(worksheet, lookup, rowNum++, false, "35-44", "Q37_35-44");
AddDataRow(worksheet, lookup, rowNum++, false, "45-54", "Q37_45-54");
AddDataRow(worksheet, lookup, rowNum++, false, "55-64", "Q37_55-64");
AddDataRow(worksheet, lookup, rowNum++, false, "65 or older", "Q37_65 or older");
AddDataRow(worksheet, lookup, rowNum++, false, String.Empty, null);
AddDataRow(worksheet, lookup, rowNum++, false, "First visit", "Q38_This was my first visit");
AddDataRow(worksheet, lookup, rowNum++, false, "2-7 times per week", "Q38_2-7 times per week");
AddDataRow(worksheet, lookup, rowNum++, false, "Once per week", "Q38_Once per week");
AddDataRow(worksheet, lookup, rowNum++, false, "2-3 times per month", "Q38_2-3 times per month");
AddDataRow(worksheet, lookup, rowNum++, false, "Once per month", "Q38_Once per month");
AddDataRow(worksheet, lookup, rowNum++, false, "Several times a year", "Q38_Several times a year");
AddDataRow(worksheet, lookup, rowNum++, false, "Languages spoken at home (other than English)", null);
AddDataRow(worksheet, lookup, rowNum++, false, "Korean", "Q39_1", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Punjabi", "Q39_2", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Chinese Mandarin", "Q39_3", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Other Western European languages", "Q39_4", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Eastern European languages", "Q39_5", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Spanish", "Q39_6", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "French", "Q39_7", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Hindi", "Q39_8", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Tagalog", "Q39_9", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Vietnamese", "Q39_10", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Pakistani", "Q39_11", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Farsi", "Q39_12", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Japanese", "Q39_13", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Arabic / Middle Eastern", "Q39_14", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Chinese – Cantonese", "Q39_15", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, "Other", "Q39_16", CellFormat.Percent, r => { r.Style.Indent = 3; }, null);
AddDataRow(worksheet, lookup, rowNum++, false, String.Empty, null);
rowNum = AddYesNoRow(worksheet, lookup, rowNum++, false, "Players Club?", "Q4");
string fileName = sc.ToString() + "-Monthly-" + ReportingTools.AdjustAndDisplayDate(DateTime.Now, "yyyy-MM-dd-hh-mm-ss", User) + ".xlsx";
const string lPath = "~/Files/Cache/";
string lOutput = string.Concat(MapPath(lPath), fileName);
FileInfo fi = new FileInfo(lOutput);
p.SaveAs(fi);
hlDownload.Text = "Download File - " + fileName;
hlDownload.NavigateUrl = String.Format("{0}{1}", lPath, fileName);
}
}
