/// <summary> /// /// </summary> /// <param name="dbModel"></param> /// <param name="Dicols"></param> /// <param name="columnname"></param> /// <param name="uploadcolname"></param> /// <param name="row"></param> /// <param name="rowIndex"></param> /// <param name="sb"></param> /// <param name="flownumber"></param> /// <param name="dtNow"></param> /// <param name="IsEmptyRow"></param> /// <param name="stepList"></param> /// <param name="guid"></param> /// <returns></returns> private bool SetDbModelLadderData(Ppc_StockInfo dbModel, Dictionary <string, int> Dicols, string columnname, string uploadcolname, IRow row, int rowIndex, StringBuilder sb, string flownumber, DateTime dtNow, bool IsEmptyRow, List <Ppc_UploadStockStepqty> stepList, string guid) { //阶梯行阶梯列的检查 if (columnname.Contains("阶梯")) { if (!string.IsNullOrWhiteSpace(uploadcolname) && Dicols.ContainsKey(uploadcolname)) { int rightIndex = Dicols[uploadcolname]; ICell cell = row.GetCell(rightIndex); if (cell != null) { if (!string.IsNullOrWhiteSpace(cell.ToString().Trim())) { int Qty = 0; if (int.TryParse(row.GetCell(rightIndex).ToString().Trim(), out Qty)) { var stepModel = MakeStockStepqty(Qty, dtNow, flownumber, guid); stepList.Add(stepModel); IsEmptyRow = true; } else { sb.Append(string.Format("第{0}行阶梯只能填入数字", rowIndex)); } } else { sb.Append(string.Format("第{0}行阶梯行数量不能为空", rowIndex)); } } } else { sb.Append(string.Format("第{0}行阶梯列数据必须上传", rowIndex)); } } return(IsEmptyRow); }
/// <summary> /// 点击保存按键写入数据库操作 /// </summary> /// <param name="inputDto"></param> /// <returns></returns> public OnlineOrdersResultDto UploadStock(UploadStockInputDto inputDto) { OnlineOrdersResultDto res = new OnlineOrdersResultDto() { StatusCode = 0, Message = "保存数据失败" }; if (string.IsNullOrWhiteSpace(inputDto.FileName)) { res.Message = "Excel文件名系统未找到"; return(res); } string fileSavePath = hostingEnvironment.WebRootPath + @"\upload\stock\"; string filename = fileSavePath + inputDto.FileName; if (!File.Exists(filename)) { res.Message = "系统未找到上传的文件"; return(res); } if (inputDto.UpFileds == null || inputDto.UpFileds.Count == 0) { res.Message = "请下方拖拽要上传的字段"; return(res); } #region 获取表头最新的流水号 var flownumber = string.Empty; string preFixNo = "BJ" + DateTime.Now.Date.ToString("yyyy-MM-dd").Replace("-", ""); var uploadinfo = _iRepository.GetList <Ppc_UploadInfo, Ppc_UploadInfo>(m => m.UploadNO.StartsWith(preFixNo), null, null, m => new Ppc_UploadInfo { UploadNO = Kogel.Dapper.Extension.Function.Max(m.UploadNO) }); if (uploadinfo == null || uploadinfo.Count == 0 || string.IsNullOrWhiteSpace(uploadinfo[0].UploadNO)) { flownumber = preFixNo + "0001"; } else { var dbnumber = uploadinfo.FirstOrDefault().UploadNO; flownumber = preFixNo + Common.GetMaxNo(dbnumber); } if (string.IsNullOrEmpty(flownumber)) { res.Message = "未生成比价流水号"; return(res); } #endregion string extName = Path.GetExtension(filename); List <Ppc_StockInfo> stockList = new List <Ppc_StockInfo>(); //物料信息表 List <Ppc_UploadStockStepqty> stepList = new List <Ppc_UploadStockStepqty>(); //阶梯表 StringBuilder sb = new StringBuilder(); StringBuilder sbTotal = new StringBuilder(); DateTime dtNow = DateTime.Now; //读取Excel数据 using (Stream sm = System.IO.File.OpenRead(filename)) { ISheet sheet = null; IRow firstRow = null; Dictionary <string, int> Dicols = new Dictionary <string, int>(); //数据开始行(排除标题行) int startRow = 0; try { //根据文件流创建excel数据结构,NPOI的工厂类WorkbookFactory会自动识别excel版本,创建出不同的excel数据结构 IWorkbook workbook = extName.ToLower() == ".xls" ? new HSSFWorkbook(sm) : WorkbookFactory.Create(sm); //如果没有指定的sheetName,则尝试获取第一个sheet sheet = workbook.GetSheetAt(0); if (sheet != null) { firstRow = sheet.GetRow(0); //一行最后一个cell的编号 即总的列数 int cellCount = firstRow.LastCellNum; for (int i = firstRow.FirstCellNum; i < cellCount; ++i) { ICell cell = firstRow.GetCell(i); if (cell != null) { string cellValue = cell.StringCellValue; if (!string.IsNullOrWhiteSpace(cellValue) && !Dicols.ContainsKey(cellValue)) { Dicols.Add(cellValue, i); } } } startRow = sheet.FirstRowNum + 1; //最后一列的标号 int rowCount = sheet.LastRowNum; Guid guidRow = Guid.Empty; //关联物料表与价格梯度表; for (int i = startRow; i <= rowCount; i++) { bool xinghaoRowFlag = true;//标记是否为型号行,默认为是型号行 Ppc_StockInfo dbModel = new Ppc_StockInfo(); IRow row = sheet.GetRow(i); #region Excel已经保存在服务器上面,客户端怎么拖拽上传都无法改变行的性质(要么物料行,要么阶梯行) ICell cell = row.GetCell(0); if (cell != null && !string.IsNullOrWhiteSpace(cell.ToString().Trim())) { guidRow = Guid.NewGuid(); xinghaoRowFlag = true; } else if (i == 1) { res.Message = "第一行数据行必须为型号行"; return(res); } else { xinghaoRowFlag = false; } #endregion if (row == null || row.FirstCellNum < 0) { continue; //没有数据的行默认是null } bool IsEmptyRow = false; //是否是空行判断 for (int j = row.FirstCellNum; j < cellCount; j++) { string columnname = firstRow.GetCell(j).StringCellValue; //excel中上传的列名对应数据库中的列名称 string uploadcolname = inputDto.UpFileds.Find(m => m.Key == columnname) != null?inputDto.UpFileds.Find(m => m.Value == columnname).Value : string.Empty; //用户拖拽的实际列名 if (xinghaoRowFlag) { IsEmptyRow = SetDbModelData(dbModel, Dicols, columnname, uploadcolname, row, i, sb, flownumber, dtNow, IsEmptyRow, stepList, guidRow.ToString()); dbModel.Guid = guidRow.ToString(); } else { //针对阶梯行的数据检查 IsEmptyRow = SetDbModelLadderData(dbModel, Dicols, columnname, uploadcolname, row, i, sb, flownumber, dtNow, IsEmptyRow, stepList, guidRow.ToString()); } } if (sb.Length == 0 && xinghaoRowFlag) { stockList.Add(dbModel); } if (!IsEmptyRow) { break; } if (sb.Length > 0 && IsEmptyRow) { sbTotal.Append(sb.ToString()); } } } } catch (Exception ex) { throw ex; } } #region 写入数据库 if (sbTotal != null && sbTotal.Length > 0) { res.Message = sbTotal.ToString(); return(res); } if (stockList != null && stockList.Count > 0) { var rowsCount = _iRepository.InsertEntityList <Ppc_StockInfo>(stockList); if (rowsCount > 0) { Ppc_UploadInfo head = new Ppc_UploadInfo() { UploadNO = flownumber, Des = inputDto.Remark, Status = (int)EnumCenter.Ppc_UploadInfo_Status.InterfaceDocking, CreateTime = dtNow, UpdateTime = dtNow, CreateID = SessionDataResult.LoginUseID, CreateName = SessionDataResult.LoginUseName }; var headCount = _iRepository.Insert <Ppc_UploadInfo>(head); if (headCount > 0) { #region 写入价格阶梯配置表 if (stepList != null && stepList.Count > 0) { var stepCount = _iRepository.InsertEntityList <Ppc_UploadStockStepqty>(stepList); if (stepCount < 0) { res.Message = "数据导入成功但是阶梯数据未导入成功"; return(res); } } #endregion res.StatusCode = 1; res.Message = "导入库存数据成功"; return(res); } else { res.Message = "写入上传信息失败"; return(res); } } else { res.Message = "写入上传物料信息失败"; return(res); } } #endregion return(res); }
/// <summary> /// /// </summary> /// <param name="dbModel"></param> /// <param name="Dicols"></param> /// <param name="columnname"></param> /// <param name="uploadcolname"></param> /// <param name="row"></param> /// <param name="rowIndex"></param> /// <param name="sb"></param> /// <param name="flownumber"></param> /// <param name="dtNow"></param> /// <param name="IsEmptyRow"></param> /// <param name="stepList"></param> /// <param name="guid"></param> /// <returns></returns> private bool SetDbModelData(Ppc_StockInfo dbModel, Dictionary <string, int> Dicols, string columnname, string uploadcolname, IRow row, int rowIndex, StringBuilder sb, string flownumber, DateTime dtNow, bool IsEmptyRow, List <Ppc_UploadStockStepqty> stepList, string guid) { if (Dicols.ContainsKey(uploadcolname)) { int rightIndex = Dicols[uploadcolname]; ICell cell = row.GetCell(rightIndex); if (cell != null) { if (columnname.Contains("型号")) { if (!string.IsNullOrWhiteSpace(cell.ToString().Trim())) { dbModel.Model = row.GetCell(rightIndex).ToString().Trim(); IsEmptyRow = true; } else { sb.Append(string.Format("第{0}行型号不能为空", rowIndex)); } } else if (columnname.Contains("品牌")) { if (!string.IsNullOrWhiteSpace(cell.ToString().Trim())) { dbModel.Brand = row.GetCell(rightIndex).ToString().Trim(); IsEmptyRow = true; } else { sb.Append(string.Format("第{0}行品牌不能为空", rowIndex)); } } else if (columnname.Contains("库存")) { int Qty = 0; if (!string.IsNullOrWhiteSpace(cell.ToString().Trim())) { if (int.TryParse(row.GetCell(rightIndex).ToString().Trim(), out Qty)) { dbModel.InvQty = Qty; IsEmptyRow = true; } else { sb.Append(string.Format("第{0}行库存只能填入数字", rowIndex)); } } else { sb.Append(string.Format("第{0}行库存不能为空", rowIndex)); } } else if (columnname.Contains("SPQ")) { int SPQ = 0; if (!string.IsNullOrWhiteSpace(cell.ToString().Trim())) { if (int.TryParse(row.GetCell(rightIndex).ToString().Trim(), out SPQ)) { dbModel.SPQ = SPQ; IsEmptyRow = true; } else { sb.Append(string.Format("第{0}行SPQ只能填入数字", rowIndex)); } } else { sb.Append(string.Format("第{0}行SPQ不能为空", rowIndex)); } } else if (columnname.Contains("MOQ")) { int MOQ = 0; if (!string.IsNullOrWhiteSpace(cell.ToString().Trim())) { if (int.TryParse(row.GetCell(rightIndex).ToString().Trim(), out MOQ)) { dbModel.MOQ = MOQ; IsEmptyRow = true; } else { sb.Append(string.Format("第{0}行MOQ只能填入数字", rowIndex)); } } else { sb.Append(string.Format("第{0}行MOQ不能为空", rowIndex)); } } else if (columnname.Contains("供应商报价")) { decimal CostPrice = 0; if (!string.IsNullOrWhiteSpace(cell.ToString().Trim())) { if (decimal.TryParse(row.GetCell(rightIndex).ToString().Trim(), out CostPrice)) { dbModel.CostPrice = CostPrice; IsEmptyRow = true; } else { sb.Append(string.Format("第{0}行供应商报价只能填入数字", rowIndex)); } } else { sb.Append(string.Format("第{0}行供应商报价不能为空", rowIndex)); } } else if (columnname.Contains("批次")) { if (!string.IsNullOrWhiteSpace(cell.ToString().Trim())) { dbModel.BatchNo = row.GetCell(rightIndex).ToString().Trim(); IsEmptyRow = true; } } else if (columnname.Contains("封装")) { if (!string.IsNullOrWhiteSpace(cell.ToString().Trim())) { dbModel.Encapsulation = row.GetCell(rightIndex).ToString().Trim(); IsEmptyRow = true; } } else if (columnname.Contains("描述")) { if (!string.IsNullOrWhiteSpace(cell.ToString().Trim())) { dbModel.Remark = row.GetCell(rightIndex).ToString().Trim(); IsEmptyRow = true; } } else if (columnname.Contains("货期(SZ)")) { if (!string.IsNullOrWhiteSpace(cell.ToString().Trim())) { dbModel.SZDelivery = row.GetCell(rightIndex).ToString().Trim();; IsEmptyRow = true; } else { sb.Append(string.Format("第{0}行型号不能为空", rowIndex)); } } else if (columnname.Contains("货期(HK)")) { if (!string.IsNullOrWhiteSpace(cell.ToString().Trim())) { dbModel.HKDelivery = row.GetCell(rightIndex).ToString().Trim(); IsEmptyRow = true; } } else if (columnname.Contains("阶梯")) { if (!string.IsNullOrWhiteSpace(cell.ToString().Trim())) { int Qty = 0; if (int.TryParse(row.GetCell(rightIndex).ToString().Trim(), out Qty)) { var stepModel = MakeStockStepqty(Qty, dtNow, flownumber, guid); stepList.Add(stepModel); IsEmptyRow = true; } else { sb.Append(string.Format("第{0}行阶梯只能填入数字", rowIndex)); } } } } } else { if (columnname.Contains("型号")) { sb.Append(string.Format("第{0}行型号必须拖拽上传", rowIndex)); } else if (columnname.Contains("品牌")) { sb.Append(string.Format("第{0}行品牌必须拖拽上传", rowIndex)); } else if (columnname.Contains("库存")) { sb.Append(string.Format("第{0}行库存必须拖拽上传", rowIndex)); } else if (columnname.Contains("SPQ")) { sb.Append(string.Format("第{0}行SPQ必须拖拽上传", rowIndex)); } else if (columnname.Contains("MOQ")) { sb.Append(string.Format("第{0}行MOQ必须拖拽上传", rowIndex)); } else if (columnname.Contains("供应商报价")) { sb.Append(string.Format("第{0}行供应商报价必须拖拽上传", rowIndex)); } else if (columnname.Contains("货期(SZ)")) { sb.Append(string.Format("第{0}行货期(sz)必须拖拽上传", rowIndex)); } } if (!dbModel.Status.HasValue) { dbModel.Status = (int)EnumCenter.Ppc_StockInfo_Status.InterfaceDocking; } if (!dbModel.CreateTime.HasValue) { dbModel.CreateTime = dtNow; } if (string.IsNullOrEmpty(dbModel.UploadNo)) { dbModel.UploadNo = flownumber; } if (string.IsNullOrWhiteSpace(guid)) { dbModel.Guid = guid; } return(IsEmptyRow); }