/// <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);
}
