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