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