public static void CitizenMReport(DateTime hotelTime, string startTime, string endTime, string SendTo, string SendReport) { var attachmentsList = new List <EmailExtend.FileAttachment>(); var SendReportArr = SendReport.Split(';'); foreach (var str in SendReportArr) { if (str != "") { switch (str) { case "RoomScoreReport": attachmentsList.Add(RoomScoreReport(startTime, endTime)); break; case "OptInDailyReport": attachmentsList.Add(OptInDailyReport(startTime, endTime)); break; case "SupervisorReport": attachmentsList.Add(SupervisorReport(startTime, endTime)); break; } } } EmailExtend.SendEmail(SendTo, string.Format("[{0}] Daily Report {1} ", SystemConfig.HotelName, ExcelExtend.DatetTimeFormate(hotelTime)), " Daily Report " + hotelTime, attachmentsList); }
public static void SendIncidentReport(string startTime, string endTime, double TimeZone, string SendTo) { double cutTimeZone = 0 - TimeZone; var StartDateTime = Convert.ToDateTime(startTime).AddHours(cutTimeZone); var EndDateTime = Convert.ToDateTime(endTime).AddHours(cutTimeZone); startTime = StartDateTime.ToString("yyyy-MM-dd HH:mm"); endTime = EndDateTime.ToString("yyyy-MM-dd HH:mm"); string TemplatePath = EmailExtend.MapPath("/Views/Home/IncidentReportTemplate.cshtml"); var index = File.ReadAllText(TemplatePath, System.Text.Encoding.GetEncoding("GBK")); var ModelList = DbFunction.IncidentHistoryEmail(startTime, endTime, TimeZone); var OpenCount = ModelList.Count(x => x.GuestMood == "--"); var SatisfiedCount = ModelList.Count(x => x.GuestMood == "Staisfied"); var AcceptedCount = ModelList.Count(x => x.GuestMood == "Accepted"); var AngryCount = ModelList.Count(x => x.GuestMood == "Angry"); var ComplaintCount = ModelList.Count(x => x.GuestMood == "Complaint"); var ImpatientCount = ModelList.Count(x => x.GuestMood == "Impatient"); var result = Razor.Parse(index, new { OpenCount = ModelList.Count(x => x.GuestMood == "--"), SatisfiedCount = ModelList.Count(x => x.GuestMood == "Staisfied"), AcceptedCount = ModelList.Count(x => x.GuestMood == "Accepted"), AngryCount = ModelList.Count(x => x.GuestMood == "Angry"), ComplaintCount = ModelList.Count(x => x.GuestMood == "Complaint"), ImpatientCount = ModelList.Count(x => x.GuestMood == "Impatient"), ModelList }); EmailExtend.SendEmail(SendTo, string.Format("Incident Report {0} to {1}", StartDateTime.ToString("MM/dd/yyyy"), EndDateTime.ToString("MM/dd/yyyy")), result); }
public static EmailExtend.FileAttachment OptInDailyReport(string startTime, string endTime) { using (FileStream fs = new FileStream(EmailExtend.MapPath("/ReportMuban/optindailyemail.xls"), FileMode.Open, FileAccess.Read)) { HSSFWorkbook hssfworkbook = new HSSFWorkbook(fs); var dailyInfo = DbFunction.OptInDailyReportEmail(startTime, endTime); var datenow = Convert.ToDateTime(startTime); HSSFSheet sheetDaily = (HSSFSheet)hssfworkbook.GetSheetAt(0); hssfworkbook.SetSheetName(0, datenow.ToString("MMddyyyy")); sheetDaily.GetRow(0).GetCell(0).SetCellValue(SystemConfig.HotelName); sheetDaily.GetRow(0).GetCell(9).SetCellValue(datenow.Year); sheetDaily.GetRow(1).GetCell(7).SetCellValue(ExcelExtend.DatetTimeFormate(datenow)); if (dailyInfo != null) { sheetDaily.GetRow(4).GetCell(9).SetCellValue(dailyInfo.Daily.Start_DOD); sheetDaily.GetRow(5).GetCell(9).SetCellValue(dailyInfo.Daily.Start_Stayover); sheetDaily.GetRow(6).GetCell(12).SetCellValue(dailyInfo.Daily.Start_NSS); sheetDaily.GetRow(7).GetCell(12).SetCellValue(dailyInfo.Daily.Start_MSS); sheetDaily.GetRow(8).GetCell(12).SetCellValue(dailyInfo.Daily.Start_OCI_OptOut); sheetDaily.GetRow(9).GetCell(9).SetCellValue(dailyInfo.Daily.StartOfTheDayTotal); sheetDaily.GetRow(11).GetCell(9).SetCellValue(dailyInfo.Daily.Start_Discrepancies); sheetDaily.GetRow(14).GetCell(9).SetCellValue(dailyInfo.Daily.End_CheckOut); sheetDaily.GetRow(15).GetCell(9).SetCellValue(dailyInfo.Daily.End_Stayover); sheetDaily.GetRow(16).GetCell(9).SetCellValue(dailyInfo.Daily.End_MandatoryClean); sheetDaily.GetRow(18).GetCell(9).SetCellValue(dailyInfo.Daily.EndOfTheDayTotal); sheetDaily.GetRow(21).GetCell(9).SetCellValue(dailyInfo.Daily.ExtraRoomsCount); sheetDaily.GetRow(39).GetCell(9).SetCellValue(dailyInfo.Daily.ChangesStayoverProgramCount); var copyRow = 2; var startRow = 3; HSSFSheet sheetDetails = (HSSFSheet)hssfworkbook.GetSheetAt(1); var DetailsList = dailyInfo.RoomList.Where(x => x.TaskStatus == "S").OrderBy(x => x.NodeName).ToList(); DetailsList.ForEach(d => { var EndOfDay = dailyInfo.RoomList.Where(x => x.CreateTime == d.CreateTime && x.NodeName == d.NodeName && x.TaskStatus == "E").FirstOrDefault(); sheetDetails.CopyRow(copyRow, startRow); HSSFRow hRow = (HSSFRow)sheetDetails.GetRow(startRow); hRow.GetCell(0).SetCellValue(d.CreateTime.ToString("dd/MM/yyyy")); hRow.GetCell(1).SetCellValue(d.NodeName); hRow.GetCell(2).SetCellValue(d.RoomStatus); hRow.GetCell(3).SetCellValue(d.ReservStatus); hRow.GetCell(4).SetCellValue(d.IsOptIn == 1 ? "Yes" : "No"); hRow.GetCell(5).SetCellValue(d.IsGreenProgram == 1 ? "Yes" : "No"); if (EndOfDay != null) { hRow.GetCell(6).SetCellValue(EndOfDay.RoomStatus); hRow.GetCell(7).SetCellValue(EndOfDay.ReservStatus); hRow.GetCell(8).SetCellValue(EndOfDay.IsOptIn == 1 ? "Yes" : "No"); hRow.GetCell(9).SetCellValue(EndOfDay.IsGreenProgram == 1 ? "Yes" : "No"); } startRow++; }); sheetDetails.RemoveRowAt(copyRow); } var fileName = SystemConfig.HotelName + " Opt In Daily Report_" + ExcelExtend.DatetTimeFormate(datenow) + ".xls"; MemoryStream ms = new MemoryStream(); hssfworkbook.Write(ms); return(new EmailExtend.FileAttachment() { FileContent = ms.ToArray(), FileName = fileName }); //var attachmentsList = new List<EmailExtend.FileAttachment>() {new EmailExtend.FileAttachment //{ // FileContent = ms.ToArray(), // FileName = fileName //} }; //EmailExtend.SendEmail(SendTo, string.Format("Opt In Daily Report {0} ", ExcelExtend.DatetTimeFormate(datenow)), "Test Opt In Daily Report", attachmentsList); } }
public static EmailExtend.FileAttachment RoomScoreReport(string startTime, string endTime) { using (FileStream fs = new FileStream(EmailExtend.MapPath("/ReportMuban/cleanscore_citizenM.xls"), FileMode.Open, FileAccess.Read)) { HSSFWorkbook hssfworkbook = new HSSFWorkbook(fs); var ModelList = DbFunction.RoomScoreReport_citizenM(startTime, endTime); var sheetUser = (HSSFSheet)hssfworkbook.GetSheet("pm"); var summarySheet = (HSSFSheet)hssfworkbook.GetSheet("Summary"); sheetUser.GetRow(0).GetCell(18).SetCellValue(Convert.ToDateTime(startTime).ToString("dd/MM/yyyy")); summarySheet.GetRow(0).GetCell(1).SetCellValue(Convert.ToDateTime(startTime).ToString("dd/MM/yyyy")); int row = 2; var tabIndex = 2; var userList = ModelList.Select(x => new { x.UserID, x.UserName }).Distinct().ToList(); userList.ForEach(user => { var dataRow = 3; int copyRow = 2; sheetUser = (HSSFSheet)hssfworkbook.CloneSheet(1); string sheetName = user.UserName.Replace('/', ' ').Replace('\\', ' ').Replace('?', ' ').Replace('*', ' ').Replace('[', ' ').Replace(']', ' '); hssfworkbook.SetSheetName(tabIndex, sheetName); var RoomList = ModelList.AsParallel().Where(x => x.UserID == user.UserID).OrderBy(x => x.NodeName).ToList(); HSSFRow summaryTimeRow = (HSSFRow)summarySheet.GetRow(0); summarySheet.CopyRow(copyRow, row + 1); HSSFRow sRow = (HSSFRow)summarySheet.GetRow(row + 1); sRow.GetCell(0).SetCellValue(user.UserName); sRow.GetCell(1).SetCellValue(RoomList.Count()); sRow.GetCell(2).SetCellValue(RoomList.Sum(x => x.Credit)); RoomList.ForEach(item => { var cleaningType = ""; switch (item.CleaningType) { case 0: cleaningType = "Checkout"; break; case 1: cleaningType = "Stayover"; break; case 2: cleaningType = "Mandatory Stayover"; break; } HSSFRow userRow = (HSSFRow)sheetUser.CopyRow(copyRow, dataRow); userRow.GetCell(0).SetCellValue(item.NodeName); userRow.GetCell(1).SetCellValue(item.StartRoomStauts); userRow.GetCell(2).SetCellValue(item.EndRoomStauts); userRow.GetCell(3).SetCellValue(cleaningType); userRow.GetCell(4).SetCellValue(item.CreatorUserName); userRow.GetCell(5).SetCellValue(item.UserName); userRow.GetCell(6).SetCellValue(item.ToDoTime); userRow.GetCell(7).SetCellValue(item.DoingTime); userRow.GetCell(8).SetCellValue(item.PauseTime); userRow.GetCell(9).SetCellValue(item.PauseReason); userRow.GetCell(10).SetCellValue(item.ResumeTime); userRow.GetCell(11).SetCellValue(item.DoneTime); userRow.GetCell(12).SetCellValue(item.CleanTime); userRow.GetCell(13).SetCellValue(item.Credit); userRow.GetCell(14).SetCellValue(item.AdditionalTaskName); userRow.GetCell(15).SetCellValue(item.AdditionalTaskCredit); userRow.GetCell(16).SetCellValue(item.TouchUpStartTime); userRow.GetCell(17).SetCellValue(item.TouchUpDoneTime); userRow.GetCell(18).SetCellValue(item.TouchUpMinutes); userRow.GetCell(19).SetCellValue(item.RoomNote); dataRow++; }); HSSFRow sheetLast = (HSSFRow)sheetUser.CopyRow(copyRow, dataRow); sheetLast.GetCell(11).SetCellValue("Total"); sheetLast.GetCell(12).SetCellValue(RoomList.Sum(x => x.Credit)); sheetUser.RemoveRowAt(2); row++; tabIndex++; }); hssfworkbook.RemoveSheetAt(1); summarySheet.RemoveRowAt(2); var fileName = SystemConfig.HotelName + " Clean Score Report_" + ExcelExtend.DatetTimeFormate(Convert.ToDateTime(startTime)) + ".xls"; var ms = new MemoryStream(); hssfworkbook.Write(ms); return(new EmailExtend.FileAttachment() { FileContent = ms.ToArray(), FileName = fileName }); } }
public static EmailExtend.FileAttachment SupervisorReport(string startTime, string endTime) { using (FileStream fs = new FileStream(EmailExtend.MapPath("/ReportMuban/supervisorsheet_citizenM.xls"), FileMode.Open, FileAccess.Read)) { HSSFWorkbook hssfworkbook = new HSSFWorkbook(fs); List <SupervisorSheetModel_citizenM> ModelList = DbFunction.SupervisorListReport_citizenM(startTime, endTime); var UserList = ModelList.Select(x => x.InspectBy).Distinct().ToList(); var datenow = Convert.ToDateTime(startTime); //得到 Excel 模板的名称 为 Room 的表格 var sheet0 = (HSSFSheet)hssfworkbook.GetSheetAt(0); sheet0.GetRow(6).GetCell(3).SetCellValue(ExcelExtend.DatetTimeFormate(Convert.ToDateTime(startTime), 2) + "-" + ExcelExtend.DatetTimeFormate(Convert.ToDateTime(endTime), 2)); var tabIndex = 1; for (int i = 0; i < UserList.Count; i++) { var row = 9; sheet0 = (HSSFSheet)hssfworkbook.CloneSheet(0); string sheetName = UserList[i].ToString().Replace('/', ' ').Replace('\\', ' ').Replace('?', ' ').Replace('*', ' ').Replace('[', ' ').Replace(']', ' '); hssfworkbook.SetSheetName(tabIndex, sheetName); var NodeList = ModelList.Where(x => x.InspectBy == sheetName).OrderBy(x => Convert.ToInt32(x.NodeName)).ThenBy(x => Convert.ToInt32(x.NodeName)).ToList(); for (int j = 0; j < NodeList.Count; j++) { var cleaningType = ""; switch (NodeList[j].CleaningType) { case 0: cleaningType = "Checkout"; break; case 1: cleaningType = "Stayover"; break; case 2: cleaningType = "Mandatory Stayover"; break; } sheet0.CopyRow(8, row); HSSFRow fsRow = (HSSFRow)sheet0.GetRow(row); fsRow.GetCell(0).SetCellValue(NodeList[j].NodeName); fsRow.GetCell(1).SetCellValue(string.IsNullOrEmpty(NodeList[j].RoomAssnDoingTime.ToString()) ? "" : Convert.ToDateTime(NodeList[j].RoomAssnDoingTime).ToString("dd/MM HH:mm")); fsRow.GetCell(2).SetCellValue(string.IsNullOrEmpty(NodeList[j].RoomAssnDoneTime.ToString()) ? "" : Convert.ToDateTime(NodeList[j].RoomAssnDoneTime).ToString("dd/MM HH:mm")); fsRow.GetCell(3).SetCellValue(NodeList[j].StartStatusRoom); fsRow.GetCell(4).SetCellValue(NodeList[j].EndStatusRoom); fsRow.GetCell(5).SetCellValue(cleaningType); fsRow.GetCell(6).SetCellValue(string.IsNullOrEmpty(NodeList[j].RoomAssnTouchUpDoingTime.ToString()) ? "" : Convert.ToDateTime(NodeList[j].RoomAssnTouchUpDoingTime).ToString("dd/MM HH:mm")); fsRow.GetCell(7).SetCellValue(string.IsNullOrEmpty(NodeList[j].RoomAssnTouchUpDoneTime.ToString()) ? "" : Convert.ToDateTime(NodeList[j].RoomAssnTouchUpDoneTime).ToString("dd/MM HH:mm")); fsRow.GetCell(8).SetCellValue(NodeList[j].DoneBy); fsRow.GetCell(9).SetCellValue(string.IsNullOrEmpty(NodeList[j].DoingTime.ToString()) ? "" : Convert.ToDateTime(NodeList[j].DoingTime).ToString("dd/MM HH:mm")); fsRow.GetCell(10).SetCellValue(string.IsNullOrEmpty(NodeList[j].DoneTime.ToString()) ? "" : Convert.ToDateTime(NodeList[j].DoneTime).ToString("dd/MM HH:mm")); fsRow.GetCell(11).SetCellValue(string.IsNullOrEmpty(NodeList[j].ReDoTime.ToString()) ? "" : Convert.ToDateTime(NodeList[j].ReDoTime).ToString("dd/MM HH:mm")); fsRow.GetCell(12).SetCellValue(string.IsNullOrEmpty(NodeList[j].ReDoneTime.ToString()) ? "" : Convert.ToDateTime(NodeList[j].ReDoneTime).ToString("dd/MM HH:mm")); fsRow.GetCell(13).SetCellValue(NodeList[j].InspectBy); fsRow.GetCell(14).SetCellValue(NodeList[j].InspectRemark); fsRow.GetCell(15).SetCellValue(NodeList[j].InspectTouchUpRemark); fsRow.GetCell(16).SetCellValue(NodeList[j].RoomNote); fsRow.GetCell(17).SetCellValue(NodeList[j].IsSkipped == 1 ? "√" : ""); row++; } sheet0.RemoveRowAt(8); tabIndex++; } if (UserList.Count > 0) { hssfworkbook.RemoveSheetAt(0); } var fileName = SystemConfig.HotelName + " Supervisor Report_" + ExcelExtend.DatetTimeFormate(datenow) + ".xls"; var ms = new MemoryStream(); hssfworkbook.Write(ms); return(new EmailExtend.FileAttachment() { FileContent = ms.ToArray(), FileName = fileName }); } }