public void EDI2Plan()
{
try
{
string flowCode = this.systemMgr.GetEntityPreferenceValue(EntityPreference.CodeEnum.FordFlow);
var flowMaster = this.genericMgr.FindById<FlowMaster>(flowCode);
var shippingScheduleMasters = this.genericMgr.FindAll<ShippingScheduleMaster>
(" from ShippingScheduleMaster order by Id desc", 0, 1);
var errorItems = new List<string>();
if(shippingScheduleMasters != null && shippingScheduleMasters.Count > 0 && !shippingScheduleMasters.First().IsImported)
{
var shippingScheduleMaster = shippingScheduleMasters.First();
shippingScheduleMaster.IsImported = true;
this.genericMgr.Update(shippingScheduleMaster);
var shippingScheduleDetails = this.genericMgr.FindAll<ShippingScheduleDetail>
(" from ShippingScheduleDetail where ShippingId =? ", new object[] { shippingScheduleMaster.Id });
var itemDic = this.itemMgr.GetRefItemCode(flowCode, shippingScheduleDetails.Select(p => p.Item).Distinct().ToList());
var mrpPlanLogList = new List<MrpPlanLog>();
foreach(var p in shippingScheduleDetails)
{
if(!itemDic.ContainsKey(p.Item))
{
errorItems.Add(p.Item);
continue;
}
var mrpPlanLog = new MrpPlanLog();
mrpPlanLog.Flow = flowCode;
var item = itemDic[p.Item];
mrpPlanLog.Item = item.Code;
mrpPlanLog.UnitQty = 1;
mrpPlanLog.ItemDescription = item.Deriction;
mrpPlanLog.ItemReference = item.ReferenceCode;
mrpPlanLog.Location = flowMaster.LocationFrom;
mrpPlanLog.OrderType = flowMaster.Type;
mrpPlanLog.Party = flowMaster.PartyTo;
mrpPlanLog.PlanDate = DateTime.Parse(p.ForecastDate.Substring(0, 4) + "-" + p.ForecastDate.Substring(4, 2) + "-" + p.ForecastDate.Substring(6, 2));
mrpPlanLog.Qty = double.Parse(p.Qty);
mrpPlanLog.Uom = item.Uom;
mrpPlanLogList.Add(mrpPlanLog);
}
planMgr.CreateMrpPlan(flowCode, mrpPlanLogList);
}
var planningScheduleMasters = this.genericMgr.FindAll<PlanningScheduleMaster>
(" from PlanningScheduleMaster order by Id desc", 0, 1);
if(planningScheduleMasters != null && planningScheduleMasters.Count > 0 && !planningScheduleMasters.Last().IsImported)
{
var planningScheduleMaster = planningScheduleMasters.First();
planningScheduleMaster.IsImported = true;
this.genericMgr.Update(planningScheduleMaster);
var planningScheduleDetails = this.genericMgr.FindAll<PlanningScheduleDetail>
(" from PlanningScheduleDetail where PlanningId =? ", new object[] { planningScheduleMaster.Id });
var itemDic = this.itemMgr.GetRefItemCode(flowCode, planningScheduleDetails.Select(p => p.Item).Distinct().ToList());
var mrpPlanLogList = new List<MrpPlanLog>();
var rccpPlanLogList = new List<RccpPlanLog>();
var currentItem = string.Empty;
var forcastDateFrom = DateTime.Now;
foreach(var plan in planningScheduleDetails)
{
if(!itemDic.ContainsKey(plan.Item))
{
errorItems.Add(plan.Item);
continue;
}
var item = itemDic[plan.Item];
double qty = double.Parse(plan.Qty);
if(plan.ScheduleTiming == "W")
{
var days = 6;
DateTime planDate = DateTime.Parse(plan.ScheduleWhen);
#region MrpPlanLog 拆分周到天
if(planDate > DateTime.Now.AddDays(14) && planDate < DateTime.Now.AddDays(30))
{
double dayQty = Math.Round(qty / days);
DateTime currentDate = planDate;
for(int j = 0; j < days; j++)
{
MrpPlanLog mrpPlanLog = new MrpPlanLog();
mrpPlanLog.UnitQty = 1;
mrpPlanLog.ItemDescription = item.Description;
mrpPlanLog.ItemReference = item.ReferenceCode;
mrpPlanLog.PlanDate = currentDate;
mrpPlanLog.Item = item.Code;
mrpPlanLog.Party = flowMaster.PartyTo;
mrpPlanLog.OrderType = flowMaster.Type;
mrpPlanLog.Flow = flowMaster.Code;
mrpPlanLog.Location = flowMaster.LocationFrom;
mrpPlanLog.Uom = item.Uom;
if(j == days - 1)
{
mrpPlanLog.Qty = qty - dayQty * (days - 1);
}
else
{
mrpPlanLog.Qty = dayQty;
}
currentDate = currentDate.AddDays(1);
mrpPlanLogList.Add(mrpPlanLog);
}
}
#endregion
#region RccpPlanLog 周转粗能力计划单
RccpPlanLog rccpPlanLog = new RccpPlanLog();
rccpPlanLog.Flow = flowCode;
rccpPlanLog.DateIndexTo = DateTimeHelper.GetWeekOfYear(planDate);
rccpPlanLog.Item = item.Code;
rccpPlanLog.Uom = item.Uom;
rccpPlanLog.DateType = CodeMaster.TimeUnit.Week;
rccpPlanLog.Qty = qty;
rccpPlanLog.UnitQty = 1;
rccpPlanLog.ItemDescription = item.Description;
rccpPlanLog.ItemReference = item.ReferenceCode;
rccpPlanLogList.Add(rccpPlanLog);
#endregion
}
else if(plan.ScheduleTiming == "F")
{
#region RccpPlanLog 把月度的拆分到周 首月不拆分
DateTime forcastDateTo = DateTime.Parse(plan.ScheduleWhen.Split('~')[1]).AddDays(1);
if(currentItem == plan.Item)
{
var weeks = Math.Round((forcastDateTo - forcastDateFrom).TotalDays / 7);
double weekQty = Math.Round(qty / weeks);
for(int i = 0; i < weeks; i++)
{
RccpPlanLog rccpPlanLog = new RccpPlanLog();
rccpPlanLog.Flow = flowCode;
rccpPlanLog.DateIndexTo = DateTimeHelper.GetWeekOfYear(forcastDateFrom);
rccpPlanLog.Item = item.Code;
rccpPlanLog.Uom = item.Uom;
rccpPlanLog.DateType = CodeMaster.TimeUnit.Week;
if(i == weeks - 1)
{
rccpPlanLog.Qty = qty - weekQty * (weeks - 1);
}
else
{
rccpPlanLog.Qty = weekQty;
}
rccpPlanLog.UnitQty = 1;
rccpPlanLog.ItemDescription = item.Description;
rccpPlanLog.ItemReference = item.ReferenceCode;
forcastDateFrom = forcastDateFrom.AddDays(7);
rccpPlanLogList.Add(rccpPlanLog);
}
}
currentItem = plan.Item;
forcastDateFrom = forcastDateTo;
#endregion
}
}
planMgr.CreateMrpPlan(flowCode, mrpPlanLogList);
//planMgr.CreateRccpPlan(CodeMaster.TimeUnit.Week, rccpPlanLogList);
}
if(errorItems.Count > 0)
{
string errorMessage = string.Format("参考物料号{0}不存在", string.Join(",", errorItems.Distinct()));
var ex = new Exception(errorMessage);
log.Error(ex);
SendErrorEmail("Edi转Plan失败", ex);
}
}
catch(Exception ex)
{
log.Error(ex);
SendErrorEmail("数据由EDI转Plan失败", ex);
}
}
public void ReadRccpPlanFromXls(Stream inputStream, DateTime startDate, DateTime endDate, bool isItemRef, com.Sconit.CodeMaster.TimeUnit dateType)
{
#region 判断
if (startDate > endDate)
{
throw new BusinessException("开始日期必须小于结束日期");
}
#endregion 判断
#region Import
if (inputStream.Length == 0)
{
throw new BusinessException("Import.Stream.Empty");
}
HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
ISheet sheet = workbook.GetSheetAt(0);
IEnumerator rows = sheet.GetRowEnumerator();
IRow dateRow = sheet.GetRow(5);
ImportHelper.JumpRows(rows, 6);
var rccpPlanLogList = new List<RccpPlanLog>();
#region 列定义
int colFlow = 0;//路线
int colItemCode = 1;//物料代码或参考物料号
int colUom = 3;//单位
#endregion
BusinessException businessException = new BusinessException();
while (rows.MoveNext())
{
Item item = null;
string uomCode = null;
string itemReference = null;
string flowCode = null;
HSSFRow row = (HSSFRow)rows.Current;
if (!ImportHelper.CheckValidDataRow(row, 0, 3))
{
break;//边界
}
string rowIndex = (row.RowNum + 1).ToString();
#region 读取路线代码
flowCode = ImportHelper.GetCellStringValue(row.GetCell(colFlow));
#endregion
#region 读取物料代码
try
{
string itemCode = ImportHelper.GetCellStringValue(row.GetCell(colItemCode));
if (itemCode == null)
{
businessException.AddMessage(string.Format("物料不能为空,第{0}行", rowIndex));
continue;
}
if (isItemRef)
{
item = this.genericMgr.FindAll<Item>("from Item as i where i.ReferenceCode = ? ", new object[] { itemCode }, 0, 1).First();
itemReference = itemCode;
}
else
{
item = this.itemMgr.GetCacheItem(itemCode);
itemReference = item.ReferenceCode;
}
if (item == null)
{
businessException.AddMessage(string.Format("物料号{0}不存在,第{1}行.", itemCode, rowIndex));
continue;
}
if (item.ItemCategory != "MODEL")
{
businessException.AddMessage(string.Format("物料号{0}不是车型,第{1}行.", itemCode, rowIndex));
continue;
}
}
catch
{
businessException.AddMessage(string.Format("读取物料时出错,第{0}行.", rowIndex));
continue;
}
#endregion
#region 读取单位
try
{
string uomCell = ImportHelper.GetCellStringValue(row.GetCell(colUom));
uomCode = this.genericMgr.FindById<Uom>(uomCell).Code;
}
catch (Exception ex)
{
businessException.AddMessage(string.Format("读取单位出错,第{0}行." + ex.Message, rowIndex));
continue;
}
#endregion
#region 读取数量
try
{
for (int i = 4; ; i++)
{
ICell dateCell = dateRow.GetCell(i);
string dateIndex = null;
#region 读取计划日期
if (dateCell != null)
{
if (dateCell.CellType == CellType.STRING)
{
dateIndex = dateCell.StringCellValue;
}
else
{
if (dateType == CodeMaster.TimeUnit.Month)
{
if (dateCell.CellType == CellType.NUMERIC)
{
dateIndex = dateCell.DateCellValue.ToString("yyyy-MM");
}
else
{
throw new BusinessException("月的时间索引必须为文本或日期格式");
}
}
else if (dateType == CodeMaster.TimeUnit.Day)
{
if (dateCell.CellType == CellType.NUMERIC)
{
dateIndex = dateCell.DateCellValue.ToString("yyyy-MM-dd");
}
else
{
throw new BusinessException("天的时间索引必须为文本或日期格式");
}
}
else
{
throw new BusinessException("周的时间索引必须为文本格式");
}
}
if (string.IsNullOrWhiteSpace(dateIndex))
{
break;
}
DateTime currentDateTime = DateTime.Now;
if (dateType == CodeMaster.TimeUnit.Week)
{
currentDateTime = DateTimeHelper.GetWeekIndexDateFrom(dateIndex);
}
else if (dateType == CodeMaster.TimeUnit.Month)
{
if (!DateTime.TryParse(dateIndex + "-01", out currentDateTime))
{
businessException.AddMessage("日期{0}格式无效", dateIndex);
continue;
}
}
else if (dateType == CodeMaster.TimeUnit.Day)
{
if (!DateTime.TryParse(dateIndex, out currentDateTime))
{
businessException.AddMessage("日期{0}格式无效", dateIndex);
continue;
}
}
if (currentDateTime.CompareTo(startDate) < 0)
{
continue;
}
if (currentDateTime.CompareTo(endDate) > 0)
{
break;
}
}
else
{
break;
}
#endregion
double qty = 0;
if (row.GetCell(i) != null)
{
if (row.GetCell(i).CellType == CellType.NUMERIC)
{
qty = row.GetCell(i).NumericCellValue;
}
else
{
string qtyValue = ImportHelper.GetCellStringValue(row.GetCell(i));
if (qtyValue != null)
{
qty = Convert.ToDouble(qtyValue);
}
}
}
else
{
continue;
}
if (qty < 0)
{
businessException.AddMessage(string.Format("数量需大于0,第{0}行", rowIndex));
continue;
}
else
{
decimal unitQty = itemMgr.ConvertItemUomQty(item.Code, uomCode, 1, item.Uom);
RccpPlanLog rccpPlanLog = new RccpPlanLog();
rccpPlanLog.Flow = flowCode;
rccpPlanLog.DateIndexTo = dateIndex;
rccpPlanLog.DateIndex = dateIndex;
rccpPlanLog.Item = item.Code;
rccpPlanLog.Uom = uomCode;
rccpPlanLog.DateType = dateType;
rccpPlanLog.Qty = qty;
rccpPlanLog.UnitQty = unitQty;
rccpPlanLog.ItemDescription = item.CodeDescription;
rccpPlanLog.ItemReference = item.ReferenceCode;
rccpPlanLogList.Add(rccpPlanLog);
}
}
}
catch (Exception ex)
{
businessException.AddMessage(ex.Message);
}
#endregion
}
if (businessException.HasMessage)
{
throw businessException;
}
#endregion
var flowMasters = this.genericMgr.FindAll<FlowMaster>
(@"from FlowMaster as m where m.IsActive = ? and Type=? ",
new object[] { true, CodeMaster.OrderType.Distribution });
#region Day
if (dateType == CodeMaster.TimeUnit.Day)
{
var flowDetails = new List<FlowDetail>();
foreach (var flow in flowMasters)
{
foreach (var flowDetail in flowMgr.GetFlowDetailList(flow, false, true))
{
if (flowDetail.MrpWeight > 0)
{
//flowDetail.DefaultLocationFrom = string.IsNullOrWhiteSpace(flowDetail.LocationFrom) ? flowDetail.CurrentFlowMaster.LocationFrom : flowDetail.LocationFrom;
//flowDetail.DefaultLocationTo = string.IsNullOrWhiteSpace(flowDetail.LocationTo) ? flowDetail.CurrentFlowMaster.LocationTo : flowDetail.LocationTo;
flowDetails.Add(flowDetail);
}
}
}
var flowDetailDic = flowDetails.GroupBy(p => new { Flow = p.Flow, Item = p.Item }).ToDictionary(d => d.Key, d => d.First());
var mrpPlanLogs = new List<MrpPlanLog>();
foreach (var rccpPlanLog in rccpPlanLogList)
{
try
{
var bomDetails = bomMgr.GetFlatBomDetail(rccpPlanLog.Item, DateTime.Parse(rccpPlanLog.DateIndex), true);
var bomMaster = this.bomMgr.GetCacheBomMaster(rccpPlanLog.Item);
var qty = itemMgr.ConvertItemUomQty(rccpPlanLog.Item, rccpPlanLog.Uom, (decimal)rccpPlanLog.Qty, bomMaster.Uom);
foreach (var bomDetail in bomDetails)
{
var flowDetail = flowDetailDic.ValueOrDefault(new { Flow = rccpPlanLog.Flow, Item = bomDetail.Item });
if (flowDetail == null)
{
businessException.AddMessage("销售路线{0}中不存在此物料{1}", rccpPlanLog.Flow, bomDetail.Item);
}
else
{
var mrpPlanLog = new MrpPlanLog();
mrpPlanLog.Flow = rccpPlanLog.Flow;
mrpPlanLog.Item = bomDetail.Item;
Item bomItem = this.itemMgr.GetCacheItem(bomDetail.Item);
mrpPlanLog.ItemDescription = bomItem.Description;
mrpPlanLog.ItemReference = bomItem.ReferenceCode;
mrpPlanLog.Location = flowDetail.DefaultLocationFrom;
mrpPlanLog.OrderType = flowDetail.CurrentFlowMaster.Type;
mrpPlanLog.Party = flowDetail.CurrentFlowMaster.PartyTo;
mrpPlanLog.PlanDate = DateTime.Parse(rccpPlanLog.DateIndex);
var item = this.itemMgr.GetCacheItem(mrpPlanLog.Item);
mrpPlanLog.Qty = (double)itemMgr.ConvertItemUomQty(rccpPlanLog.Item, bomDetail.Uom, qty * bomDetail.CalculatedQty, item.Uom);
decimal unitQty = itemMgr.ConvertItemUomQty(rccpPlanLog.Item, flowDetail.Uom, 1, item.Uom);
mrpPlanLog.UnitQty = unitQty;
mrpPlanLog.Uom = flowDetail.Uom;
mrpPlanLogs.Add(mrpPlanLog);
}
}
}
catch (Exception)
{
businessException.AddMessage(string.Format("分解bom{0}出错", rccpPlanLog.Item));
}
}
if (businessException.HasMessage)
{
throw businessException;
}
var groupMrpPlanLogs = mrpPlanLogs.GroupBy(p => p.Flow, (k, g) =>
new
{
k,
List = from q in g
group q by new
{
q.PlanDate,
q.Item,
q.PlanVersion,
q.Flow,
q.Location
} into result
select new MrpPlanLog
{
PlanDate = result.Key.PlanDate,
Item = result.Key.Item,
PlanVersion = result.Key.PlanVersion,
Flow = result.Key.Flow,
Location = result.Key.Location,
Qty = result.Sum(r => r.Qty),
Uom = result.First().Uom,
UnitQty = result.First().UnitQty,
ItemDescription = result.First().ItemDescription,
ItemReference = result.First().ItemReference,
Party = result.First().Party,
OrderType = result.First().OrderType
}
});
foreach (var groupMrpPlanLog in groupMrpPlanLogs)
{
this.CreateMrpPlan(groupMrpPlanLog.k, groupMrpPlanLog.List.ToList());
}
}
#endregion
CreateRccpPlan(dateType, rccpPlanLogList);
}
public void ReadDailyMrpPlanFromXls(Stream inputStream, DateTime? startDate, DateTime? endDate, string flowCode, bool isItemRef)
{
#region 判断
if (startDate.HasValue)
{
if (startDate.Value.Date < DateTime.Now.Date)
{
throw new BusinessException("开始日期必须大于当期日期");
}
}
else
{
startDate = DateTime.Now.Date;
}
if (endDate.HasValue)
{
if (endDate.Value.Date <= DateTime.Now.Date)
{
throw new BusinessException("结束日期必须大于当期日期");
}
}
else
{
endDate = DateTime.MaxValue.Date;
}
if (startDate.Value > endDate.Value)
{
throw new BusinessException("开始日期必须小于结束日期");
}
if (inputStream.Length == 0)
{
throw new BusinessException("Import.Stream.Empty");
}
#endregion 判断
HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
ISheet sheet = workbook.GetSheetAt(0);
IEnumerator rows = sheet.GetRowEnumerator();
IRow dateRow = sheet.GetRow(5);
ImportHelper.JumpRows(rows, 6);
#region 列定义
int colFlow = 0;
//int colLocation = 0;//库位
int colItemCode = 1;//物料代码或参考物料号
//int colItemDescription = 2;//物料描述
int colUom = 3;//单位
#endregion
List<FlowDetail> flowDetailList = new List<FlowDetail>();
var uomDic = this.genericMgr.FindAll<Uom>().ToDictionary(d => d.Code, d => d);
if (string.IsNullOrWhiteSpace(flowCode))
{
var flowMasters = this.genericMgr.FindAll<FlowMaster>(" from FlowMaster where Type=?",
CodeMaster.OrderType.Distribution).ToList();
foreach (var flowMaster in flowMasters)
{
flowDetailList.AddRange(GetFlowDetails(flowMaster));
}
}
else
{
flowDetailList = GetFlowDetails(flowCode).ToList();
}
var flowDetailDic = flowDetailList.GroupBy(p => p.Flow, (k, g) => new { k, g }).ToDictionary(d => d.k, d => d.g);
List<MrpPlanLog> mrpPlanLogList = new List<MrpPlanLog>();
BusinessException businessException = new BusinessException();
while (rows.MoveNext())
{
Item item = null;
string uomCode = null;
string itemReference = null;
string flow = null;
HSSFRow row = (HSSFRow)rows.Current;
if (!ImportHelper.CheckValidDataRow(row, 0, 3))
{
break;//边界
}
string rowIndex = (row.RowNum + 1).ToString();
#region 读取路线
try
{
string flowCell = ImportHelper.GetCellStringValue(row.GetCell(colFlow));
if (flowCell != null && flowDetailDic.ContainsKey(flowCell))
{
flow = flowCell;
}
else
{
businessException.AddMessage(string.Format("读取路线出错,第{0}行.", rowIndex));
continue;
}
}
catch (Exception ex)
{
businessException.AddMessage(string.Format("读取路线出错,第{0}行." + ex.Message, rowIndex));
continue;
}
var flowDetails = flowDetailDic.ValueOrDefault(flow);
#endregion
#region 读取物料代码
try
{
string itemCode = ImportHelper.GetCellStringValue(row.GetCell(colItemCode));
if (itemCode == null)
{
businessException.AddMessage(string.Format("物料号不能为空,第{0}行", rowIndex));
continue;
}
if (isItemRef)
{
item = this.genericMgr.FindAll<Item>("from Item as i where i.ReferenceCode = ? ", new object[] { itemCode }, 0, 1).First();
itemReference = itemCode;
}
else
{
item = this.itemMgr.GetCacheItem(itemCode);
itemReference = item.ReferenceCode;
}
if (item == null)
{
businessException.AddMessage(string.Format("物料号{0}不存在,第{1}行.", itemCode, rowIndex));
continue;
}
}
catch (Exception ex)
{
businessException.AddMessage(ex.Message + ". 第" + rowIndex + "行.");
continue;
}
#endregion
#region 读取单位
try
{
string uomCell = ImportHelper.GetCellStringValue(row.GetCell(colUom));
uomCode = uomDic.ValueOrDefault(uomCell).Code;
}
catch (Exception ex)
{
businessException.AddMessage(ex.Message + ". 第" + rowIndex + "行.");
continue;
}
#endregion
#region 使用flowDetails过滤
var q = flowDetails.Where(f => f.Item == item.Code && f.Uom == uomCode);
string locationCode = string.Empty;
if (q.Count() > 0)
{
locationCode = !string.IsNullOrWhiteSpace(q.FirstOrDefault().LocationFrom) ? q.FirstOrDefault().LocationFrom : q.FirstOrDefault().CurrentFlowMaster.LocationFrom;
}
else
{
if (flowDetails.First().CurrentFlowMaster.IsManualCreateDetail)
{
uomCode = uomCode == null ? item.Uom : uomCode;
locationCode = flowDetails.First().CurrentFlowMaster.LocationFrom;
}
else
{
businessException.AddMessage(string.Format("没有找到匹配的路线明细,第{0}行.物料{1},单位{2}", rowIndex, item.Code, uomCode));
continue;
}
}
#endregion
#region 读取数量
try
{
for (int i = 4; ; i++)
{
ICell dateCell = dateRow.GetCell(i);
#region 读取计划日期
DateTime currentDate = DateTime.Now;
string weekOfYear = string.Empty;
if (dateCell != null)
{
if (dateCell.CellType == CellType.NUMERIC)
{
currentDate = dateCell.DateCellValue;
}
else if (dateCell.CellType == CellType.STRING)
{
currentDate = DateTime.Parse(dateCell.StringCellValue);
}
}
else
{
break;
}
if (startDate.HasValue && currentDate.Date < startDate.Value.Date)
{
continue;
}
if (endDate.HasValue && currentDate.Date > endDate.Value.Date)
{
break;
}
#endregion
double qty = 0;
if (row.GetCell(i) != null)
{
if (row.GetCell(i).CellType == CellType.NUMERIC)
{
qty = row.GetCell(i).NumericCellValue;
}
else
{
string qtyValue = ImportHelper.GetCellStringValue(row.GetCell(i));
if (qtyValue != null)
{
qty = Convert.ToDouble(qtyValue);
}
}
}
else
{
continue;
}
if (qty < 0)
{
businessException.AddMessage(string.Format("数量必须大于0,第{0}行", rowIndex));
continue;
}
else
{
MrpPlanLog mrpPlanLog = new MrpPlanLog();
decimal unitQty = itemMgr.ConvertItemUomQty(item.Code, uomCode, 1, item.Uom);
mrpPlanLog.PlanDate = currentDate;
mrpPlanLog.Item = item.Code;
//dailyPlanLog.PlanVersion = dailyPlan.PlanVersion;
mrpPlanLog.Flow = flowDetails.First().CurrentFlowMaster.Code;
mrpPlanLog.Location = locationCode;
mrpPlanLog.Party = flowDetails.First().CurrentFlowMaster.PartyTo;
mrpPlanLog.OrderType = flowDetails.First().CurrentFlowMaster.Type;
mrpPlanLog.Uom = uomCode;
mrpPlanLog.Qty = qty;
mrpPlanLog.UnitQty = unitQty;
mrpPlanLog.ItemDescription = item.CodeDescription;
mrpPlanLog.ItemReference = item.ReferenceCode;
//dailyPlanLog.CreateDate = System.DateTime.Now;
//dailyPlanLog.CreateUserId = user.Id;
//dailyPlanLog.CreateUserName = user.FullName;
mrpPlanLogList.Add(mrpPlanLog);
}
}
}
catch (Exception ex)
{
businessException.AddMessage(string.Format("读取数量出错,第{0}行." + ex.Message, rowIndex));
}
#endregion
}
var mrpPlanLogGroups = mrpPlanLogList.GroupBy(p => p.Flow, (k, g) => new { k, g });
foreach (var mrpPlanLogGroup in mrpPlanLogGroups)
{
CreateMrpPlan(mrpPlanLogGroup.k, mrpPlanLogGroup.g.ToList());
}
if (businessException.HasMessage)
{
throw businessException;
}
}
public void ReadWeeklyMrpPlanFromXls(Stream inputStream, string startWeek, string endWeek, string flowCode, bool isItemRef)
{
#region 判断
DateTime? startDate = null;
DateTime? endDate = null;
if (!string.IsNullOrEmpty(startWeek))
{
startDate = DateTimeHelper.GetWeekIndexDateFrom(startWeek);
}
if (!string.IsNullOrEmpty(endWeek))
{
endDate = DateTimeHelper.GetWeekIndexDateFrom(endWeek);
}
if (startDate.HasValue)
{
if (startDate.Value.Date < DateTime.Now.Date)
{
throw new BusinessException("开始日期必须大于当期日期");
}
}
else
{
startDate = DateTime.Now.Date;
}
if (endDate.HasValue)
{
if (endDate.Value.Date <= DateTime.Now.Date)
{
throw new BusinessException("结束日期必须大于当期日期");
}
}
else
{
endDate = DateTime.MaxValue.Date;
}
if (startDate.Value > endDate.Value)
{
throw new BusinessException("开始日期必须小于结束日期");
}
if (inputStream.Length == 0)
{
throw new BusinessException("Import.Stream.Empty");
}
#endregion 判断
//var flowDetails = GetFlowDetails(flowCode);
List<FlowDetail> flowDetailList = new List<FlowDetail>();
var uomDic = this.genericMgr.FindAll<Uom>().ToDictionary(d => d.Code, d => d);
if (string.IsNullOrWhiteSpace(flowCode))
{
var flowMasters = this.genericMgr.FindAll<FlowMaster>(" from FlowMaster where Type=?",
CodeMaster.OrderType.Distribution).ToList();
foreach (var flowMaster in flowMasters)
{
flowDetailList.AddRange(GetFlowDetails(flowMaster));
}
}
else
{
flowDetailList = GetFlowDetails(flowCode).ToList();
}
var flowDetailDic = flowDetailList.GroupBy(p => p.CurrentFlowMaster.Code, (k, g) => new { k, g }).ToDictionary(d => d.k, d => d.g);
HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
ISheet sheet = workbook.GetSheetAt(0);
IEnumerator rows = sheet.GetRowEnumerator();
IRow dateRow = sheet.GetRow(5);
ImportHelper.JumpRows(rows, 6);
var mrpPlanLogList = new List<MrpPlanLog>();
int days = 7;
try
{
days = (int)sheet.GetRow(4).GetCell(1).NumericCellValue;
}
catch (Exception)
{ }
#region 列定义
//int colFlow = 0;//
int colFlow = 0;//
int colItemCode = 1;//物料代码或参考物料号
//int colItemDescription = 2;//物料描述
int colUom = 3;//单位
#endregion
BusinessException businessException = new BusinessException();
while (rows.MoveNext())
{
string flow = null;
Item item = null;
string uomCode = null;
string itemReference = null;
HSSFRow row = (HSSFRow)rows.Current;
if (!ImportHelper.CheckValidDataRow(row, 0, 3))
{
break;//边界
}
string rowIndex = (row.RowNum + 1).ToString();
#region 读取路线
try
{
string flowCell = ImportHelper.GetCellStringValue(row.GetCell(colFlow));
if (flowCell != null && flowDetailDic.ContainsKey(flowCell))
{
flow = flowCell;
}
}
catch (Exception ex)
{
businessException.AddMessage(string.Format("读取库位出错,第{0}行." + ex.Message, rowIndex));
continue;
}
var flowDetails = flowDetailDic.ValueOrDefault(flow);
#endregion
#region 读取物料代码
try
{
string itemCode = ImportHelper.GetCellStringValue(row.GetCell(colItemCode));
if (itemCode == null)
{
businessException.AddMessage(string.Format("物料号不存在,第{0}行", rowIndex));
continue;
}
if (isItemRef)
{
item = this.genericMgr.FindAll<Item>("from Item as i where i.ReferenceCode = ? ", new object[] { itemCode }, 0, 1).First();
itemReference = itemCode;
}
else
{
item = this.genericMgr.FindById<Item>(itemCode);
itemReference = item.ReferenceCode;
}
if (item == null)
{
businessException.AddMessage(string.Format("物料号{0}不存在,第{1}行", itemCode, rowIndex));
continue;
}
}
catch (Exception ex)
{
businessException.AddMessage(string.Format("读取物料号出错,第{0}行" + ex.Message, rowIndex));
continue;
}
#endregion
#region 读取单位
try
{
string uomCell = ImportHelper.GetCellStringValue(row.GetCell(colUom));
uomCode = uomDic.ValueOrDefault(uomCell).Code;
}
catch (Exception ex)
{
businessException.AddMessage(string.Format("读取单位出错,第{0}行," + ex.Message, rowIndex));
continue;
}
#endregion
#region 使用flowDetails过滤
string locationCode = string.Empty;
var q = flowDetails.Where(f => f.Item == item.Code && f.Uom == uomCode);
if (q.Count() > 0)
{
locationCode = !string.IsNullOrWhiteSpace(q.FirstOrDefault().LocationFrom) ? q.FirstOrDefault().LocationFrom : q.FirstOrDefault().CurrentFlowMaster.LocationFrom;
}
else
{
if (flowDetails.First().CurrentFlowMaster.IsManualCreateDetail)
{
uomCode = uomCode == null ? item.Uom : uomCode;
locationCode = flowDetails.First().CurrentFlowMaster.LocationFrom;
}
else
{
businessException.AddMessage("没有找到匹配的路线明细,第{0}行.物料号{1},单位{2},库位{3}", rowIndex, item.Code, uomCode, flow);
continue;
}
}
#endregion
#region 读取数量
try
{
for (int i = 4; ; i++)
{
ICell dateCell = dateRow.GetCell(i);
string weekOfYear = null;
#region 读取计划日期
if (dateCell != null)
{
weekOfYear = ImportHelper.GetCellStringValue(dateCell);
if (string.IsNullOrWhiteSpace(weekOfYear))
{
break;
}
if (weekOfYear.CompareTo(startWeek) < 0)
{
continue;
}
if (weekOfYear.CompareTo(endWeek) > 0)
{
break;
}
}
else
{
break;
}
#endregion
double qty = 0;
if (row.GetCell(i) != null)
{
if (row.GetCell(i).CellType == CellType.NUMERIC)
{
qty = row.GetCell(i).NumericCellValue;
}
else
{
string qtyValue = ImportHelper.GetCellStringValue(row.GetCell(i));
if (qtyValue != null)
{
qty = Convert.ToDouble(qtyValue);
}
}
}
else
{
continue;
}
if (qty < 0)
{
businessException.AddMessage("导入数量不能小于0,第{0}行", rowIndex);
continue;
}
else
{
double dayQty = Math.Round(qty / days);
DateTime currentDate = DateTimeHelper.GetWeekIndexDateFrom(weekOfYear);
for (int j = 0; j < days; j++)
{
MrpPlanLog mrpPlanLog = new MrpPlanLog();
decimal unitQty = itemMgr.ConvertItemUomQty(item.Code, uomCode, 1, item.Uom);
mrpPlanLog.UnitQty = unitQty;
mrpPlanLog.ItemDescription = item.CodeDescription;
mrpPlanLog.ItemReference = item.ReferenceCode;
mrpPlanLog.PlanDate = currentDate;
mrpPlanLog.Item = item.Code;
mrpPlanLog.Party = flowDetails.First().CurrentFlowMaster.PartyTo;
mrpPlanLog.OrderType = flowDetails.First().CurrentFlowMaster.Type;
//dailyPlanLog.PlanVersion = dailyPlan.PlanVersion;
mrpPlanLog.Flow = flowDetails.First().CurrentFlowMaster.Code;
mrpPlanLog.Location = locationCode;
mrpPlanLog.Uom = uomCode;
if (j == days - 1)
{
mrpPlanLog.Qty = qty - dayQty * (days - 1);
}
else
{
mrpPlanLog.Qty = dayQty;
}
currentDate = currentDate.AddDays(1);
mrpPlanLogList.Add(mrpPlanLog);
}
}
}
}
catch (Exception ex)
{
businessException.AddMessage(ex.Message);
}
#endregion
}
if (businessException.HasMessage)
{
throw businessException;
}
var mrpPlanLogGroups = mrpPlanLogList.GroupBy(p => p.Flow, (k, g) => new { k, g });
foreach (var mrpPlanLogGroup in mrpPlanLogGroups)
{
CreateMrpPlan(mrpPlanLogGroup.k, mrpPlanLogGroup.g.ToList());
}
}