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