public OnlineOrdersResultDto UploadStock(UploadStockInputDto inputDto)
{
OnlineOrdersResultDto result = new OnlineOrdersResultDto()
{
StatusCode = 0, Message = "保存数据失败"
};
inputDto.FileName = "d9d7556c-6283-437c-8b4e-1ac9bb921a47.xlsx";
inputDto.Remark = "测试";
inputDto.UpFileds = new List <UpLoadHead>()
{
new UpLoadHead()
{
Key = "型号", Value = "物料型号"
},
new UpLoadHead()
{
Key = "品牌", Value = "物料型号"
},
new UpLoadHead()
{
Key = "库存", Value = ""
},
new UpLoadHead()
{
Key = "SPQ", Value = "SPQ"
},
new UpLoadHead()
{
Key = "MOQ", Value = "MOQ"
},
new UpLoadHead()
{
Key = "供应商报价", Value = "供应商报价"
},
new UpLoadHead()
{
Key = "批次", Value = "批次"
},
new UpLoadHead()
{
Key = "封装", Value = "封装"
},
new UpLoadHead()
{
Key = "描述", Value = "描述"
},
new UpLoadHead()
{
Key = "货期(SZ)", Value = "货期(SZ)"
},
new UpLoadHead()
{
Key = "货期(HK)", Value = "货期(HK)"
},
new UpLoadHead()
{
Key = "阶梯", Value = "阶梯"
},
};
result = _service.UploadStock(inputDto);
return(result);
}
/// <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);
}