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