/// <summary> /// 添加小保养套餐优惠价格数据 /// </summary> /// <param name="model"></param> /// <param name="maxUpdateID"></param> /// <returns></returns> public static int AddMaintenancePackageOnSale(MaintenancePackageOnSaleModel model, int maxUpdateID) { try { return(DataAccess.DAO.TireActivity.DalTireActivity.AddMaintenancePackageOnSale(model, maxUpdateID)); } catch (Exception ex) { Logger.Error("AddMaintenancePackageOnSale", ex); throw ex; } }
/// <summary> /// 添加小保养套餐优惠价格数据 /// </summary> /// <param name="model"></param> /// <param name="maxUpdateID"></param> /// <returns></returns> public static int AddMaintenancePackageOnSale(MaintenancePackageOnSaleModel model, int maxUpdateID) { var sql = @"INSERT INTO Activity.[dbo].[MaintenancePackageOnSale] ([PID] ,[Price] ,[OnetirePrice] ,[TwotirePrice] ,[ThreetirePrice] ,[FourtirePrice] ,[UpdateID] ,[Status] ,[CreateBy] ,[LastUpdateBy] ,[CreateDatetime] ,[LastUpdateDateTime] ) VALUES ( @PID , @Price, @OnetirePrice, @TwotirePrice, @ThreetirePrice, @FourtirePrice, @UpdateID, @Status, @CreateBy, @LastUpdateBy, GETDATE() , GETDATE() ) SELECT SCOPE_IDENTITY();"; using (var conn = new SqlConnection(ConnectionHelper.GetDecryptConn("Gungnir"))) { var parameters = new[] { new SqlParameter("@PID", model.PID), new SqlParameter("@Price", model.Price), new SqlParameter("@OnetirePrice", model.OnetirePrice), new SqlParameter("@TwotirePrice", model.TwotirePrice), new SqlParameter("@ThreetirePrice", model.ThreetirePrice), new SqlParameter("@FourtirePrice", model.FourtirePrice), new SqlParameter("@UpdateID", maxUpdateID), new SqlParameter("@Status", model.Status), new SqlParameter("@CreateBy", model.CreateBy), new SqlParameter("@LastUpdateBy", model.LastUpdateBy) }; return(Convert.ToInt32(SqlHelper.ExecuteScalar(conn, CommandType.Text, sql, parameters))); } }
public ActionResult ImportMaintenancePackage() { var list = new List <MaintenancePackageOnSaleModel>(); #region 验证文件 var files = Request.Files; if (files == null || files.Count <= 0) { return(Json(new { code = 1, status = false, msg = "请先选择文件上传" })); } var file = files[0]; if (file.ContentType != "application/vnd.ms-excel" && file.ContentType != "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet") { return(Json(new { code = 1, status = false, msg = "文件格式不正确, 请上传Excel文件" })); } #endregion var stream = file.InputStream; var buffer = new byte[stream.Length]; stream.Read(buffer, 0, buffer.Length); var workBook = new XSSFWorkbook(new MemoryStream(buffer)); var sheet = workBook.GetSheetAt(0); #region 读取验证excel信息 Func <ICell, string> getStringValueFunc = cell => { if (cell != null) { if (cell.CellType == CellType.Numeric) { return(DateUtil.IsCellDateFormatted(cell) ? cell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss.fff") : cell.NumericCellValue.ToString()); } return(cell.StringCellValue?.Trim()); } return(null); }; var titleRow = sheet.GetRow(sheet.FirstRowNum); var cellNum = titleRow.FirstCellNum; int pidNum = 0; //小保养套餐PID的索引 int priceNum = 0; //原价索引 int oneTireNum = 0; //一条轮胎优惠价索引 int twoTireNum = 0; //二条轮胎优惠价索引 int threeTireNum = 0; //三条轮胎优惠价索引 int fourTireNum = 0; //四条轮胎优惠价索引 for (int i = cellNum; i < titleRow.LastCellNum; i++) { if (getStringValueFunc(titleRow.GetCell(i)) == "小保养套餐PID") { pidNum = i; } if (getStringValueFunc(titleRow.GetCell(i)) == "原价") { priceNum = i; } if (getStringValueFunc(titleRow.GetCell(i)) == "一条轮胎优惠价") { oneTireNum = i; } if (getStringValueFunc(titleRow.GetCell(i)) == "二条轮胎优惠价") { twoTireNum = i; } if (getStringValueFunc(titleRow.GetCell(i)) == "三条轮胎优惠价") { threeTireNum = i; } if (getStringValueFunc(titleRow.GetCell(i)) == "四条轮胎优惠价") { fourTireNum = i; } } int invalidPriceCount = 0; var msgs = new List <string>(); var allPidList = new List <string>(); for (var rowIndex = sheet.FirstRowNum + 1; rowIndex <= sheet.LastRowNum; rowIndex++) { var row = sheet.GetRow(rowIndex); if (row != null) { var cellIndex = row.FirstCellNum; var pid = getStringValueFunc(row.GetCell(pidNum)); allPidList.Add(pid); if (string.IsNullOrWhiteSpace(pid)) { continue; } Regex decimalPattern = new Regex("^[0-9]+(.[0-9]*)?$"); string priceString = getStringValueFunc(row.GetCell(priceNum)); string onePriceString = getStringValueFunc(row.GetCell(oneTireNum)); string twoPriceString = getStringValueFunc(row.GetCell(twoTireNum)); string threePriceString = getStringValueFunc(row.GetCell(threeTireNum)); string fourPriceString = getStringValueFunc(row.GetCell(fourTireNum)); if ((!string.IsNullOrWhiteSpace(priceString) && !decimalPattern.IsMatch(priceString)) || (!string.IsNullOrWhiteSpace(onePriceString) && !decimalPattern.IsMatch(onePriceString)) || (!string.IsNullOrWhiteSpace(twoPriceString) && !decimalPattern.IsMatch(twoPriceString)) || (!string.IsNullOrWhiteSpace(threePriceString) && !decimalPattern.IsMatch(threePriceString)) || (!string.IsNullOrWhiteSpace(fourPriceString) && !decimalPattern.IsMatch(fourPriceString))) { invalidPriceCount++; continue; } decimal price = 0; decimal oneTirePrice = 0; decimal twoTirePrice = 0; decimal threeTirePrice = 0; decimal fourTirePrice = 0; if (!string.IsNullOrWhiteSpace(priceString)) { price = Convert.ToDecimal(priceString); } if (!string.IsNullOrWhiteSpace(onePriceString)) { oneTirePrice = Convert.ToDecimal(onePriceString); } if (!string.IsNullOrWhiteSpace(twoPriceString)) { twoTirePrice = Convert.ToDecimal(twoPriceString); } if (!string.IsNullOrWhiteSpace(threePriceString)) { threeTirePrice = Convert.ToDecimal(threePriceString); } if (!string.IsNullOrWhiteSpace(fourPriceString)) { fourTirePrice = Convert.ToDecimal(fourPriceString); } if (!string.IsNullOrEmpty(pid)) { var item = new MaintenancePackageOnSaleModel { PID = pid, Price = price, OnetirePrice = oneTirePrice, TwotirePrice = twoTirePrice, ThreetirePrice = threeTirePrice, FourtirePrice = fourTirePrice }; int sameIndex = list.FindIndex(x => x.PID == item.PID); if (sameIndex >= 0) { list[sameIndex].Status = 0; } item.CreateBy = User.Identity.Name; item.LastUpdateBy = User.Identity.Name; item.Status = 1; list.Add(item); } } } if (!list.Any()) { return(Json(new { code = 1, status = false, msg = "导入数据不能为空!" })); } #endregion #region 小保养套餐PID验证 var pidList = list.Select(x => x.PID).ToList(); //excel中所有的pid集合 var validPidList = TireActivityManager.GetMaintenancePackagePidList(pidList).Select(x => x.PID).ToList(); //有效pid集合 var validMainPackageList = list.Where(x => validPidList.Contains(x.PID)).ToList(); //有效的小保养套餐数据 var allValidList = TireActivityManager.GetMaintenancePackagePidList(allPidList).Select(x => x.PID).ToList(); var invalidcount = allPidList.Where(x => !allValidList.Contains(x)).ToList().Count;//无效PID数量 if (!validMainPackageList.Any()) { return(Json(new { code = 1, status = false, msg = "小保养套餐PID全部无效!" })); } #endregion if (invalidcount == 0 && invalidPriceCount == 0) { return(AddMaintenancePackageOnSaleList(validMainPackageList)); } else { return(Json(new { code = 0, data = validMainPackageList, invalidCount = invalidcount, invalidPriceCount = invalidPriceCount })); } }