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