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