public async Task <ResponseResult> Handle(Command request, CancellationToken cancellationToken)
{
request.UserId = string.IsNullOrWhiteSpace(request.UserId) ? null : request.UserId;
var req = System.Net.WebRequest.Create(request.Url);
if (req != null)
{
using (Stream requstStream = req.GetResponse().GetResponseStream())
{
var copyStream = new MemoryStream();
requstStream.CopyTo(copyStream);
using (copyStream)
{
var context = new ExcelPackage(copyStream);
var worksheet = context.Workbook.Worksheets[1];
int colStart = worksheet.Dimension.Start.Column; // starts from 1
int colEnd = worksheet.Dimension.End.Column;
int rowStart = worksheet.Dimension.Start.Row; // starts from 1
int rowEnd = worksheet.Dimension.End.Row;
// id
var typeId = worksheet.Cells[1, 1].Text.Trim();
var index = typeId?.IndexOf(':');
if ((index ?? 0) > 0)
{
typeId = typeId.Substring(index.Value + 1);
}
else
{
return(ResponseResult.Error("物料类型Id不能为空。"));
}
// typecode
var typeCode = worksheet.Cells[1, 3].Text.Trim();
var code = typeCode?.Substring(5);
if (code.StartsWith(_tradCode) && !code.Equals(request.TypeCode))
{
return(ResponseResult.Error("导入模板错误"));
}
// upload datetime
var templateDateTime = worksheet.Cells[1, 4].Text.Trim();
index = templateDateTime?.IndexOf(':');
if ((index ?? 0) > 0)
{
templateDateTime = templateDateTime.Substring(index.Value + 1);
}
else
{
return(ResponseResult.Error("模板导出时间不能为空。"));
}
var type = new QueryStack.Models.MaterialType();
using (var scope = _databaseScopeFactory.CreateReadOnly())
{
if (!await _repository.IsUploadDateTimeLatestAsync(typeId, templateDateTime))
{
return(ResponseResult.Error("导入模板不是最新的，请重新下载模板。"));
}
type = await _materialTypeRepository.FindAsync(typeId);
if (type == null)
{
return(ResponseResult.Error("物料类型不正确，请重新下载模板。"));
}
}
StringBuilder errorMessage = new StringBuilder();
for (int i = rowStart + 2; i <= rowEnd; i++)
{
newrow:
if (i > rowEnd)
{
break;
}
try
{
var material = new QueryStack.Models.Material();
// 物料类型
material.MateTypeId = typeId;
material.MateTypeCode = type.TypeCode;
material.MateTypeName = type.TypeName;
// 物料名称
var text = worksheet.Cells[i, 1].Text.Trim();
if (string.IsNullOrWhiteSpace(text) || text.Length > 40)
{
errorMessage.Append($"{i.ToString()},");
continue;
}
material.MateName = text;
// 物料规格
text = worksheet.Cells[i, 2].Text.Trim();
if (string.IsNullOrWhiteSpace(text))
{
errorMessage.Append($"{i.ToString()},");
continue;
}
else
{
index = text.IndexOf(':');
if ((index ?? 0) > 0)
{
var spec = text.Split(':');
material.MaterialSpecId = spec[0];
material.MaterialSpec = spec[1];
}
else
{
errorMessage.Append($"{i.ToString()},");
continue;
}
}
// 物料单位
text = worksheet.Cells[i, 3].Text.Trim();
if (string.IsNullOrWhiteSpace(text))
{
errorMessage.Append($"{i.ToString()},");
continue;
}
else
{
index = text.IndexOf(':');
if ((index ?? 0) > 0)
{
var unit = text.Split(':');
material.MaterialUnitId = unit[0];
material.MaterialUnits = unit[1];
}
else
{
errorMessage.Append($"{i.ToString()},");
continue;
}
}
// 是否有独立编号
text = worksheet.Cells[i, 5].Text.Trim();
if (string.IsNullOrWhiteSpace(text))
{
errorMessage.Append($"{i.ToString()},");
continue;
}
else
{
index = text.IndexOf(':');
if ((index ?? 0) > 0)
{
var idCodeSingle = text.Split(':');
if (idCodeSingle[0].Equals("0"))
{
material.IdCodeSingle = false;
}
else if (idCodeSingle[0].Equals("1"))
{
material.IdCodeSingle = true;
}
else
{
errorMessage.Append($"{i.ToString()},");
continue;
}
}
else
{
errorMessage.Append($"{i.ToString()},");
continue;
}
}
// 是否为生产物料
text = worksheet.Cells[i, 4].Text.Trim();
if (string.IsNullOrWhiteSpace(text))
{
errorMessage.Append($"{i.ToString()},");
continue;
}
else
{
index = text.IndexOf(':');
if ((index ?? 0) > 0)
{
var isSelfBuild = text.Split(':');
if (isSelfBuild[0].Equals("0"))
{
material.IsSelfBuild = false;
}
else if (isSelfBuild[0].Equals("1"))
{
material.IsSelfBuild = true;
material.IdCodeSingle = false; // 【是否为生产物料】选择为“是”时，【是否有独立编码】默认为“否”且不可修改。
}
else
{
errorMessage.Append($"{i.ToString()},");
continue;
}
}
else
{
errorMessage.Append($"{i.ToString()},");
continue;
}
}
// 建议采购价
text = worksheet.Cells[i, 7].Text.Trim();
if (!string.IsNullOrWhiteSpace(text))
{
decimal price = 0;
try
{
price = decimal.Round(decimal.Parse(text), 2);
material.MaterialPrice = price;
}
catch (Exception)
{
errorMessage.Append($"{i.ToString()},");
continue;
}
}
// 采购主体
text = worksheet.Cells[i, 6].Text.Trim();
if (!string.IsNullOrEmpty(request.TypeCode) && request.TypeCode.StartsWith(_tradCode))
{
if (string.IsNullOrWhiteSpace(text))
{
errorMessage.Append($"{i.ToString()},");
i++;
continue;
}
}
var tradeList = text.Split(',');
if (tradeList != null)
{
foreach (var item in tradeList)
{
if (!string.IsNullOrWhiteSpace(item.Trim()))
{
index = item.IndexOf(':');
if ((index ?? 0) > 0)
{
var trade = new QueryStack.Models.Trade();
var tradeSplit = item.Split(':');
trade.TradeId = tradeSplit[0];
trade.TradeName = tradeSplit[1];
material.Trades.Add(trade);
}
else
{
errorMessage.Append($"{i.ToString()},");
i++;
goto newrow;
}
}
}
}
// 自定义属性， 从第8列开始
for (int m = 8; m <= colEnd; m++)
{
var property = new MaterialProperty();
// 属性
text = worksheet.Cells[2, m].Text.Trim();
if (string.IsNullOrWhiteSpace(text))
{
errorMessage.Append($"{i.ToString()},");
i++;
goto newrow;
}
else
{
index = text.IndexOf(':');
if ((index ?? 0) > 0)
{
var propertySplit = text.Split(':');
property.PropId = propertySplit[0];
var name = propertySplit[1];
property.PropName = (name.EndsWith('*') == true) ? name.Remove(name.LastIndexOf('*')) : name;
}
else
{
errorMessage.Append($"{i.ToString()},");
i++;
goto newrow;
}
}
// 选项
text = worksheet.Cells[i, m].Text.Trim();
// 自定义属性是否必填
if (worksheet.Cells[2, m].IsRichText)
{
if (string.IsNullOrWhiteSpace(text))
{
errorMessage.Append($"{i.ToString()},");
i++;
goto newrow;
}
index = text.IndexOf(':');
if ((index ?? 0) > 0)
{
var optionSplit = text.Split(':');
property.OptionId = optionSplit[0];
}
else
{
errorMessage.Append($"{i.ToString()},");
i++;
goto newrow;
}
material.Properties.Add(property);
}
else if (!worksheet.Cells[2, m].IsRichText && !string.IsNullOrWhiteSpace(text))
{
index = text.IndexOf(':');
if ((index ?? 0) > 0)
{
var optionSplit = text.Split(':');
property.OptionId = optionSplit[0];
}
else
{
errorMessage.Append($"{i.ToString()},");
i++;
goto newrow;
}
material.Properties.Add(property);
}
}
// 物料有效性验证
if (!MaterialValidation(material))
{
errorMessage.Append($"{i.ToString()},");
continue;
}
using (var scope = _databaseScopeFactory.CreateWithTransaction())
{
// INSERT INTO tb_materialinfo
var result = await _repository.GetIdAndMaxCodeAsync();
material.Id = result.Id;
var c = 1;
if (result.Data != null)
{
c = int.Parse(result.Data.MateCode.Substring(2)) + 1;
}
material.MateCode = "WL" + c.ToString().PadLeft(8, '0');
await _repository.AddAsync(material, request.UserId);
// INSERT INTO tb_materialproperties
if (material.Properties?.Any() ?? false)
{
foreach (var item in material.Properties)
{
item.MaterialId = material.Id;
}
await _repository.AddPropertiesAsync(material.Properties, request.UserId);
}
// INSERT INTO tb_tradeinfo
if (material.Trades?.Any() ?? false)
{
foreach (var item in material.Trades)
{
item.ProdMaterId = material.Id;
}
await _repository.AddTradeAsync(material.Trades, request.UserId);
}
scope.SaveChanges();
}
}
catch (Exception)
{
errorMessage.Append($"{i.ToString()},");
continue;
}
}
if (errorMessage?.Length > 0)
{
string message = errorMessage.ToString();
message = (message.EndsWith(',') == true) ? message.Remove(message.LastIndexOf(',')) : message;
return(ResponseResult.Error($"第{message}行导入出错。"));
}
}
}
return(ResponseResult.Ok());
}
return(ResponseResult.Error($"文件导入出错，请重新导入"));
}
public async Task <ResponseResult> Handle(Command request, CancellationToken cancellationToken)
{
request.UserId = string.IsNullOrWhiteSpace(request.UserId) ? null : request.UserId;
var material = new QueryStack.Models.Material()
{
MateName = request.MateName,
MateTypeId = request.MateTypeId,
MateTypeCode = string.IsNullOrWhiteSpace(request.MateTypeCode) ? null : request.MateTypeCode,
MateTypeName = string.IsNullOrWhiteSpace(request.MateTypeName) ? null : request.MateTypeName,
MaterialSpecId = string.IsNullOrWhiteSpace(request.MaterialSpecId) ? null : request.MaterialSpecId,
MaterialSpec = string.IsNullOrWhiteSpace(request.MaterialSpec) ? null : request.MaterialSpec,
MaterialUnitId = request.MaterialUnitId,
MaterialUnits = string.IsNullOrWhiteSpace(request.MaterialUnits) ? null : request.MaterialUnits,
MaterialPrice = request.MaterialPrice,
IdCodeSingle = request.IdCodeSingle,
IsSelfBuild = request.IsSelfBuild,
};
using (var scope = _databaseScopeFactory.CreateWithTransaction())
{
var result = await _materialRepository.GetIdAndMaxCodeAsync();
material.Id = result.Id;
var c = 1;
if (result.Data != null)
{
c = int.Parse(result.Data.MateCode.Substring(2)) + 1;
}
material.MateCode = "WL" + c.ToString().PadLeft(8, '0');
await _materialRepository.AddAsync(material, request.UserId);
request.Properties = request.Properties
.Where(x => (!string.IsNullOrWhiteSpace(x.PropId) && !string.IsNullOrWhiteSpace(x.OptionId)))
.Distinct().ToList();
if (request.Properties.Count > 0)
{
var pops = new List <QueryStack.Models.MaterialProperty>();
foreach (var one in request.Properties)
{
pops.Add(new QueryStack.Models.MaterialProperty()
{
MaterialId = material.Id,
PropId = one.PropId,
PropName = one.PropName,
OptionId = one.OptionId,
});
}
await _materialRepository.AddPropertiesAsync(pops, request.UserId);
}
request.Trades = request.Trades.Where(x => !string.IsNullOrWhiteSpace(x.TradeId)).Distinct().ToList();
if (request.Trades.Count > 0)
{
var trade = new List <QueryStack.Models.Trade>();
foreach (var one in request.Trades)
{
trade.Add(new QueryStack.Models.Trade()
{
ProdMaterId = material.Id,
TradeId = one.TradeId,
TradeName = one.TradeName,
});
}
await _materialRepository.AddTradeAsync(trade, request.UserId);
}
scope.SaveChanges();
}
return(ResponseResult.Ok());
}
public async Task <ResponseResult> Handle(Command request, CancellationToken cancellationToken)
{
if (string.IsNullOrWhiteSpace(request.Id))
{
return(ResponseResult.Error("物料 Id 不能为空"));
}
using (var scope = _databaseScopeFactory.CreateWithTransaction())
{
var material = await _materialRepository.FindAsync(request.Id);
if (material == null)
{
return(ResponseResult.Error($"未找到 Id = {request.Id} 的符合要求的物料"));
}
if (material.MaterialPrice != request.MaterialPrice)
{
await _materialRepository.UpdateAsync(
request.Id,
request.MaterialPrice,
string.IsNullOrWhiteSpace(request.UserId)?null : request.UserId);
}
var ids = material.Trades.Select(x => x.TradeId).ToList();
request.Trades = request.Trades
.Where(x => (!string.IsNullOrWhiteSpace(x.TradeId)) && (!ids.Contains(x.TradeId))).ToList();
bool hasTradesValues = false;
if (request.Trades.Count > 0)
{
hasTradesValues = true;
var trade = new List <QueryStack.Models.Trade>();
foreach (var one in request.Trades)
{
trade.Add(new QueryStack.Models.Trade()
{
ProdMaterId = material.Id,
TradeId = one.TradeId,
TradeName = one.TradeName,
});
}
await _materialRepository.AddTradeAsync(
trade,
string.IsNullOrWhiteSpace(request.UserId)?null : request.UserId);
}
if (!string.IsNullOrEmpty(request.MateTypeCode) && request.MateTypeCode.StartsWith(_tradCode))
{
if (!hasTradesValues)
{
return(ResponseResult.Error("采购主体不能为空"));
}
}
scope.SaveChanges();
}
return(ResponseResult.Ok());
}
public async Task <ResponseResult> Handle(Command request, CancellationToken cancellationToken)
{
using (var scope = _databaseScopeFactory.CreateWithTransaction())
{
var material = await _materialRepository.FindAsync(request.Id);
if (material == null)
{
return(ResponseResult.Error($"未找到 Id={request.Id} 的符合要求的物料数据"));
}
request.UserId = string.IsNullOrWhiteSpace(request.UserId) ? null : request.UserId;
material.MateName = request.MateName;
material.MateTypeId = request.MateTypeId;
material.MateTypeCode = string.IsNullOrWhiteSpace(request.MateTypeCode) ? null : request.MateTypeCode;
material.MateTypeName = string.IsNullOrWhiteSpace(request.MateTypeName) ? null : request.MateTypeName;
material.MaterialSpecId = string.IsNullOrWhiteSpace(request.MaterialSpecId) ? null : request.MaterialSpecId;
material.MaterialSpec = string.IsNullOrWhiteSpace(request.MaterialSpec) ? null : request.MaterialSpec;
material.MaterialUnitId = request.MaterialUnitId;
material.MaterialUnits = string.IsNullOrWhiteSpace(request.MaterialUnits) ? null : request.MaterialUnits;
material.MaterialPrice = request.MaterialPrice;
material.IdCodeSingle = request.IdCodeSingle;
material.IsSelfBuild = request.IsSelfBuild;
request.Properties = request.Properties.Where(x => (!string.IsNullOrWhiteSpace(x.PropId)) &&
(!string.IsNullOrWhiteSpace(x.OptionId))).Distinct().ToList();
if (request.Properties.Count > 0)
{
// 添加
var add = request.Properties.Where(x => !material.Properties.Any(y => y.PropId == x.PropId)).ToList();
if (add.Count > 0)
{
var pops = new List <QueryStack.Models.MaterialProperty>();
foreach (var one in add)
{
pops.Add(new QueryStack.Models.MaterialProperty()
{
MaterialId = material.Id,
PropId = one.PropId,
PropName = one.PropName,
OptionId = one.OptionId,
});
}
await _materialRepository.AddPropertiesAsync(pops, request.UserId);
}
// 编辑
var edit = request.Properties.Where(x => material.Properties.Any(y => y.PropId == x.PropId && y.OptionId != x.OptionId)).ToList();
if (edit.Count > 0)
{
var pops_2 = new List <QueryStack.Models.MaterialProperty>();
foreach (var one in edit)
{
var mp = material.Properties.FirstOrDefault(x => x.PropId == one.PropId);
pops_2.Add(new QueryStack.Models.MaterialProperty()
{
Id = mp.Id,
MaterialId = material.Id,
PropId = one.PropId,
PropName = one.PropName,
OptionId = one.OptionId,
});
}
await _materialRepository.UpdatePropertiesAsync(pops_2, request.UserId);
}
// 删除
var delete = material.Properties.Where(x => !request.Properties.Any(y => y.PropId == x.PropId)).ToList();
if (delete.Count > 0)
{
await _materialRepository.DeletePropertiesAsync(delete.Select(x => x.Id).ToArray());
}
}
else
{
if (material.Properties.Count > 0)
{
await _materialRepository.DeletePropertiesAsync(material.Properties.Select(x => x.Id).ToArray());
}
}
request.Trades = request.Trades.Where(x => !string.IsNullOrWhiteSpace(x.TradeId)).Distinct().ToList();
if (request.Trades.Count > 0)
{
// 添加
var add = request.Trades.Where(x => !material.Trades.Any(y => y.TradeId == x.TradeId)).ToList();
if (add.Count > 0)
{
var trade = new List <QueryStack.Models.Trade>();
foreach (var one in add)
{
trade.Add(new QueryStack.Models.Trade()
{
ProdMaterId = material.Id,
TradeId = one.TradeId,
TradeName = one.TradeName,
});
}
await _materialRepository.AddTradeAsync(trade, request.UserId);
}
// 删除
var delete = material.Trades.Where(x => !request.Trades.Any(y => y.TradeId == x.TradeId)).ToList();
if (delete.Count > 0)
{
await _materialRepository.DeleteTradeAsync(delete.Select(x => x.Id).ToArray());
}
}
else
{
await _materialRepository.DeleteTradeAsync(material.Trades.Select(x => x.Id).ToArray());
}
await _materialRepository.UpdateAsync(material, request.UserId);
scope.SaveChanges();
}
return(ResponseResult.Ok());
}
