/// <summary> /// 添加轮胎数据 /// </summary> /// <param name="model"></param> /// <returns></returns> public static int AddTireActivityPid(TireActivityPIDModel model) { var sql = @"INSERT INTO Activity.[dbo].[TireActivityPID] ([TireActivityID] ,[PID] ,[Is_Deleted] ,[CreateDatetime] ,[LastUpdateDateTime] ,[CreateBy] ,[LastUpdateBy] ) VALUES ( @TireActivityID , @PID, @Is_Deleted, GETDATE() , GETDATE(), @CreateBy, @LastUpdateBy ) SELECT SCOPE_IDENTITY();"; using (var conn = new SqlConnection(ConnectionHelper.GetDecryptConn("Gungnir"))) { var parameters = new[] { new SqlParameter("@TireActivityID", model.TireActivityID), new SqlParameter("@PID", model.PID), new SqlParameter("@Is_Deleted", model.Is_Deleted), new SqlParameter("@CreateBy", model.CreateBy), new SqlParameter("@LastUpdateBy", model.LastUpdateBy) }; return(Convert.ToInt32(SqlHelper.ExecuteScalar(conn, CommandType.Text, sql, parameters))); } }
/// <summary> /// 添加轮胎数据 /// </summary> /// <param name="model"></param> /// <returns></returns> public static int AddTireActivityPid(TireActivityPIDModel model) { try { return(DataAccess.DAO.TireActivity.DalTireActivity.AddTireActivityPid(model)); } catch (Exception ex) { Logger.Error("AddTireActivityPid", ex); throw ex; } }
public ActionResult ImportTireActivityPid(string planName, string planDesc, string beginDateTime, string endDateTime) { var list = new List <TireActivityPIDModel>(); #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文件" })); } var desc = Request["planName"].ToString(); #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的索引 for (int i = cellNum; i < titleRow.LastCellNum; i++) { if (titleRow.GetCell(i) != null) { if (getStringValueFunc(titleRow.GetCell(i)) == "PID") { pidNum = i; } } } var msgs = 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)); if (!string.IsNullOrEmpty(pid)) { var item = new TireActivityPIDModel { PID = pid }; var existsItem = list.FirstOrDefault(x => x.PID == item.PID ); if (existsItem != null) { list.Remove(existsItem); } ; item.CreateBy = User.Identity.Name; item.LastUpdateBy = User.Identity.Name; 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.GetValidTirePid(pidList).Select(x => x.PID).ToList(); //有效pid集合 var validMainPackageList = list.Where(x => validPidList.Contains(x.PID)).ToList(); //有效的轮胎活动PID if (!validMainPackageList.Any()) { return(Json(new { code = 1, status = false, msg = "轮胎PID全部无效!" })); } var tireActivityModel = new TireActivityModel(); tireActivityModel.PlanDesc = planDesc.Trim(); tireActivityModel.PlanName = planName.Trim(); tireActivityModel.BeginDatetime = DateTime.Parse(beginDateTime); tireActivityModel.EndDatetime = DateTime.Parse(endDateTime); tireActivityModel.PIDNum = validMainPackageList.Count; var nowTime = DateTime.Now; if (nowTime < tireActivityModel.BeginDatetime) { tireActivityModel.Status = 0; } if (tireActivityModel.BeginDatetime <= nowTime && nowTime <= tireActivityModel.EndDatetime) { tireActivityModel.Status = 1; } if (nowTime > tireActivityModel.EndDatetime) { tireActivityModel.Status = 2; } tireActivityModel.CreateBy = User.Identity.Name; tireActivityModel.LastUpdateBy = User.Identity.Name; int maxUpdateID = TireActivityManager.GetMaxTireActivityUpdateID(); tireActivityModel.UpdateID = maxUpdateID + 1; tireActivityModel.PlanNumber = "计划" + (maxUpdateID + 2); var notvalidCount = list.Where(x => !validPidList.Contains(x.PID)).ToList().Count; #endregion var repeatList = TireActivityManager.GetRepeatTirePids(validMainPackageList.Select(x => x.PID).ToList().ToList()); return(Json(new { code = 0, activityModel = tireActivityModel, list = validMainPackageList, repeatList = repeatList, notvalidCount = notvalidCount, validCount = validPidList.Count })); }