/// <summary> /// 执行生成二维码包 /// </summary> /// <param name="obj"></param> /// <returns></returns> public bool ExcGenerate(IntegralGoodsQrcPackage obj) { try { //压缩包存储路径 var basePath = System.AppDomain.CurrentDomain.SetupInformation.ApplicationBase; var qrCodePath = Convert.ToString(ConfigurationManager.AppSettings["QrCodeStorePath"]); var fileName = string.Format("{0}.xls", Guid.NewGuid().ToString()); var serverPath = qrCodePath + fileName; var filePath = basePath + serverPath; //查询商品 IntegralGoods goods = (from g in db.IntegralGoods where g.GoodsId == obj.GoodsId select g).FirstOrDefault <IntegralGoods>(); //var goods = _integralGoodsRepository.Find(obj.GoodsId); obj.Name = goods.GoodsName; obj.GoodsCode = goods.GoodsNo; obj.State = 2; obj.CreateDate = DateTime.Now; obj.Url = Convert.ToString(ConfigurationManager.AppSettings["QrCodeUrl"]) + "/"; obj.DownLoadUrl = serverPath; //添加一条压缩包记录 db.IntegralGoodsQrcPackage.Add(obj); db.SaveChanges(); //安全码的模板 //{规格}{商品类别}{工厂}{月份}{流水号} var snTmpl = ((int)obj.Range).ToString() + obj.GoodsCode + ((int)obj.Source).ToString() + ((int)obj.March).ToString().PadLeft(2, '0'); List <string> qrCodeList = new List <string>(); //生成二维码 for (int i = 1; i <= obj.Num; i++) { IntegralGoodsQrc qrc = new IntegralGoodsQrc(); qrc.State = 1; qrc.PackageId = obj.PackageId; qrc.CreateDate = DateTime.Now; qrc.QrcUrl = ""; qrc.Code = ""; //添加一条二维码记录 db.IntegralGoodsQrc.Add(qrc); db.SaveChanges(); //安全码 qrc.Code = snTmpl + qrc.QrcId.ToString().PadLeft(10, '0'); //安全码需混淆 //{域名}?p={本批次ID}&sn={安全码} qrc.QrcUrl = string.Format("{0}?sn={1}", obj.Url, EncryptHelper.Confusion(qrc.Code)); //更改二维码的值 SetQrcCode(qrc.QrcId, qrc.QrcUrl, qrc.Code); //生成一个二维码 var code = QrCodeHelper.ToBytes(qrc.QrcUrl); qrCodeList.Add(qrc.QrcUrl); } //生成Excel ExcelExtend.SaveExcel <string>(qrCodeList, qrCodePath, fileName); obj.State = 1; //_repository.Update(obj); EntityState statebefore = db.Entry(obj).State; db.Entry(obj).State = EntityState.Modified; db.SaveChanges(); //将生成的所有二维码打包 //CompressHelper.ToZip(qrCodeList, filePath, "PNG"); return(true); } catch (Exception ex) { string a = ex.Message; return(false); } }
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 }); } }