コード例 #1
0
        /// <summary>
        /// 缺件生产订单生成
        /// </summary>
        public void CheckLackProductionOrder()
        {
            ///获取状态⑦为50已发布的缺件表数据,记录开始计算时间⑧并将状态⑦更新为60处理中
            ///数据库执行语句
            StringBuilder sqlText = new StringBuilder();
            ///获取状态⑦为50反馈完成的缺件表
            List <LackOfMaterialInfo> lackOfMaterialInfos = lackOfMaterialBLL.GetListByPage("" +
                                                                                            "[STATUS] = " + (int)LackOfMaterialStatusConstants.Feedbacked + "",
                                                                                            "[ID]", 1, 1, out int dataCnt);

            ///没有需要计算的缺件表
            if (lackOfMaterialInfos.Count == 0)
            {
                return;
            }
            LackOfMaterialInfo lackOfMaterialInfo = lackOfMaterialInfos.FirstOrDefault();

            ///获取状态⑦为50已发布的缺件表数据,记录开始计算时间⑧并将状态⑦更新为60处理中
            lackOfMaterialBLL.UpdateStatus((int)LackOfMaterialStatusConstants.OrderCalculating, lackOfMaterialInfo.Id, "", loginUser);
            ///工厂
            string plantCondition = string.Empty;

            if (!string.IsNullOrEmpty(lackOfMaterialInfo.Plant))
            {
                plantCondition = " and [WERK] = N'" + lackOfMaterialInfo.Plant + "' ";
            }

            ///根据开始日期⑤与结束日期⑥、工厂代码④范围获取生产订单列表,按生产日期+生产顺序从晚到早进行排列
            ///供货计划与生产订单日期相差一天
            List <PullOrdersInfo> pullOrdersInfos = new PullOrdersBLL().GetList("" + plantCondition + " and " +
                                                                                "[ORDER_DATE] between " +
                                                                                "N'" + lackOfMaterialInfo.StartDate.GetValueOrDefault().AddDays(materialRequrieAdvanceDays) + "' and " +
                                                                                "N'" + lackOfMaterialInfo.EndDate.GetValueOrDefault().AddDays(materialRequrieAdvanceDays) + "'",
                                                                                "[ORDER_DATE] desc,[VEHICLE_ORDER] desc");

            ///没有生产订单时计算失败
            if (pullOrdersInfos.Count == 0)
            {
                lackOfMaterialBLL.UpdateStatus((int)LackOfMaterialStatusConstants.OrderCalculateFailed, lackOfMaterialInfo.Id, "没有生产订单时计算失败", loginUser);
                return;
            }

            ///根据缺件表明细中的物料号②、供应商代码③获取该生产订单的订单BOM中的数据
            List <LackOfMaterialDetailInfo> lackOfMaterialDetailInfos = new LackOfMaterialDetailBLL().GetList("" +
                                                                                                              "[LACK_ORDER_FID] = N'" + lackOfMaterialInfo.Fid.GetValueOrDefault() + "' and " +
                                                                                                              "[FEEDBACK_FLAG] = 1", string.Empty);

            if (lackOfMaterialDetailInfos.Count == 0)
            {
                lackOfMaterialBLL.UpdateStatus((int)LackOfMaterialStatusConstants.OrderCalculateFailed, lackOfMaterialInfo.Id, "缺件表中无物料缺件信息", loginUser);
                return;
            }
            ///BOM
            List <PullOrderBomInfo> pullOrderBomInfos = new PullOrderBomBLL().GetList("" +
                                                                                      "[ORDERFID] in ('" + string.Join("','", pullOrdersInfos.Select(d => d.Fid.GetValueOrDefault()).ToArray()) + "') and " +
                                                                                      "[ZCOMNO] in ('" + string.Join("','", lackOfMaterialDetailInfos.Select(d => d.PartNo).ToArray()) + "') and " +
                                                                                      "[SUPPLIER_NUM] in ('" + string.Join("','", lackOfMaterialDetailInfos.Select(d => d.SupplierNum).ToArray()) + "')", string.Empty);

            ///历史缺件生产订单 ORDER_DATE
            List <PorderLackMaterialInfo> lackOfMaterialProductionOrderInfos = new PorderLackMaterialBLL().GetList(
                "[ORDER_DATE] between " +
                "N'" + lackOfMaterialInfo.StartDate.GetValueOrDefault().AddDays(materialRequrieAdvanceDays) + "' and " +
                "N'" + lackOfMaterialInfo.EndDate.GetValueOrDefault().AddDays(materialRequrieAdvanceDays) + "'", string.Empty);
            ///LOG_FID
            Guid logFid = Guid.NewGuid();

            foreach (PullOrdersInfo pullOrdersInfo in pullOrdersInfos)
            {
                ///并将该数据集合中的消耗数量与反馈缺件数量⑦进行对比,当反馈缺件数量⑦ – 消耗数量 >= 0 时
                List <PullOrderBomInfo> pullOrderBoms = pullOrderBomInfos.Where(d => d.Orderfid.GetValueOrDefault() == pullOrdersInfo.Fid.GetValueOrDefault()).ToList();
                if (pullOrderBoms.Count == 0)
                {
                    continue;
                }

                ///此时需要记录对比结果到生产订单缺件明细中,物料号③、供应商代码④、工厂代码⑤、需求数量⑥即为BOM消耗数量
                var pullOrderBomQuery = pullOrderBoms
                                        .GroupBy(b => new { b.Zcomno, b.SupplierNum })
                                        .Select(d => new { PartNo = d.Key.Zcomno, d.Key.SupplierNum, Zqty = d.Sum(x => x.Zqty.GetValueOrDefault()) }).ToList();

                ///缺件数量⑦即当反馈缺件数量⑦ – 消耗数量 >= 0 是为消耗数量,否则为反馈缺件剩余数量⑦
                ///同时记录缺件生产订单,生产订单号③、生产订单外键②、工厂代码④、车间代码⑤、生产线代码⑥从生产订单中继承、缺件表外键①从缺件表继承、缺件标记⑦为True
                var lackDetailInfos = (from l in lackOfMaterialDetailInfos
                                       join p in pullOrderBomQuery
                                       on new { l.PartNo, l.SupplierNum } equals new { p.PartNo, p.SupplierNum }
                                       where l.FeedbackLackQty > 0
                                       select new { l.PartNo, l.SupplierNum, l.Plant, l.PartPurchaser, p.Zqty, l.FeedbackLackQty, l.Fid, l.LackOrderFid }).ToList();

                ///历史生产订单中是否存在此订单号
                PorderLackMaterialInfo lackOfMaterialProductionOrderInfo
                    = lackOfMaterialProductionOrderInfos.FirstOrDefault(d =>
                                                                        d.ProductionOrderFid.GetValueOrDefault() == pullOrdersInfo.Fid.GetValueOrDefault());
                ///当缺件明细中的反馈缺件数量全部被扣除到小于等于零时,不再记录生产订单缺件明细,仅记录缺件生产订单,此时开始缺件标记⑦为False
                Guid lackOfMaterialProductionOrderFid = Guid.NewGuid();

                #region TT_ATP_PORDER_LACK_MATERIAL
                if (lackOfMaterialProductionOrderInfo == null)
                {
                    ///不存在、直接插入
                    sqlText.AppendFormat("insert into [LES].[TT_ATP_PORDER_LACK_MATERIAL] (" +
                                         "[FID],[LACK_ORDER_FID],[PRODUCTION_ORDER_FID],[PRODUCTION_ORDER_NO],[PLANT],[ASSEMBLY_LINE]," +
                                         "[VALID_FLAG],[LACK_FLAG],[CREATE_USER],[CREATE_DATE],[ORDER_DATE]) values (" +
                                         "'{8}','{0}','{1}','{2}','{3}','{4}'," +
                                         "1,{7},'{5}',GETDATE(),'{6}');",
                                         lackOfMaterialInfo.Fid,                       ///LACK_ORDER_FID,0
                                         pullOrdersInfo.Fid,                           ///PRODUCTION_ORDER_FID,1
                                         pullOrdersInfo.OrderNo,                       ///PRODUCTION_ORDER_NO,2
                                         pullOrdersInfo.Werk,                          ///PLANT,3
                                         pullOrdersInfo.AssemblyLine,                  ///ASSEMBLY_LINE,4
                                         loginUser,                                    ///CREATE_USER,5
                                         pullOrdersInfo.OrderDate.GetValueOrDefault(), ///ORDER_DATE,6
                                         (lackDetailInfos.Count == 0 ? 0 : 1),         ///LACK_FLAG,7
                                         lackOfMaterialProductionOrderFid              ///FID,8
                                         );
                }
                else
                {
                    lackOfMaterialProductionOrderFid = lackOfMaterialProductionOrderInfo.Fid.GetValueOrDefault();
                    ///已存在、更新状态
                    sqlText.AppendFormat("update [LES].[TT_ATP_PORDER_LACK_MATERIAL] set " +
                                         "[LACK_FLAG] = {4}," +
                                         "[LACK_ORDER_FID] = N'{0}'," +
                                         "[ORDER_DATE] = N'{3}'," +
                                         "[MODIFY_USER] = N'{1}'," +
                                         "[MODIFY_DATE] = GETDATE() where " +
                                         "[FID] = N'{2}';",
                                         lackOfMaterialInfo.Fid.GetValueOrDefault(),   ///LACK_ORDER_FID,0
                                         loginUser,                                    ///MODIFY_USER,1
                                         lackOfMaterialProductionOrderFid,             ///FID,2
                                         pullOrdersInfo.OrderDate.GetValueOrDefault(), ///ORDER_DATE,3
                                         (lackDetailInfos.Count == 0 ? 0 : 1)          ///LACK_FLAG,4
                                         );
                    ///删除之前计算的生产订单缺件明细
                    sqlText.AppendFormat("delete from [LES].[TT_ATP_PORDER_LACK_MATERIAL_DETAIL] where " +
                                         "[LACK_PORDER_FID] = N'{0}';",
                                         lackOfMaterialProductionOrderFid///LACK_PORDER_FID,0
                                         );
                    ///移除
                    //lackOfMaterialProductionOrderInfos.Remove(lackOfMaterialProductionOrderInfo);
                }
                #endregion

                #region TI_IFM_SAP_PRODUCTION_ORDER_LACK_MATERIAL
                ///插入到SAP中间表
                if (lackMaterialProductionOrdersToSap.ToLower() == "true")
                {
                    sqlText.AppendFormat(" insert into [LES].[TI_IFM_SAP_PRODUCTION_ORDER_LACK_MATERIAL] (" +
                                         "[FID],[ENTERPRISE],[AREA_NO],[DMS_NO],[MATERIAL_CHECK],[SEND_TIME],[VALID_FLAG],[CREATE_USER],[CREATE_DATE],[PROCESS_FLAG],[LOG_FID]) values (" +
                                         "NEWID(),'{0}','{1}','{2}',0,GETDATE(),1,'{3}',GETDATE(),{5},'{4}');",
                                         pullOrdersInfo.Werk,                ///ENTERPRISE,0
                                         pullOrdersInfo.AssemblyLine,        ///AREA_NO,1
                                         pullOrdersInfo.OrderNo,             ///DMS_NO,2
                                         loginUser,                          /// CREATE_USER,3
                                         logFid,                             ///LOG_FID,4
                                         (int)ProcessFlagConstants.Untreated ///PROCESS_FLAG,5
                                         );
                }
                #endregion

                #region TI_IFM_MES_PRODUCTION_ORDER_LACK_MATERIAL
                ///插入到MES中间表
                if (lackMaterialProductionOrdersToMes.ToLower() == "true")
                {
                    sqlText.AppendFormat("insert into [LES].[TI_IFM_MES_PRODUCTION_ORDER_LACK_MATERIAL] (" +
                                         "[FID],[ENTERPRISE],[AREA_NO],[DMS_NO],[MATERIAL_CHECK],[SEND_TIME],[VALID_FLAG],[CREATE_USER],[CREATE_DATE],[PROCESS_FLAG],[LOG_FID]) values (" +
                                         "NEWID(),'{0}','{1}','{2}',0,GETDATE(),1,'{3}',GETDATE(),{5},'{4}');",
                                         pullOrdersInfo.Werk,                ///ENTERPRISE,0
                                         pullOrdersInfo.AssemblyLine,        ///AREA_NO,1
                                         pullOrdersInfo.OrderNo,             ///DMS_NO,2
                                         loginUser,                          /// CREATE_USER,3
                                         logFid,                             ///LOG_FID,4
                                         (int)ProcessFlagConstants.Untreated ///PROCESS_FLAG,5
                                         );
                }
                #endregion

                #region TT_ATP_PORDER_LACK_MATERIAL_DETAIL
                if (lackDetailInfos.Count > 0)
                {
                    ///写入生产订单缺件明细
                    foreach (var lackDetailInfo in lackDetailInfos)
                    {
                        sqlText.AppendFormat("insert into [LES].[TT_ATP_PORDER_LACK_MATERIAL_DETAIL] (" +
                                             "[FID],[LACK_PORDER_FID],[PRODUCTION_ORDER_FID],[LACK_DETAIL_FID],[PART_NO],[SUPPLIER_NUM],[PLANT],[REQUIRE_QTY],[LACK_QTY]," +
                                             "[VALID_FLAG],[CREATE_USER],[CREATE_DATE]," +
                                             "[PART_PURCHASER],[PRODUCTION_ORDER_NO],[ASSEMBLY_LINE],[ORDER_DATE],[LACK_ORDER_FID]) values " +
                                             "(NEWID(),'{0}','{1}','{2}','{3}','{4}','{5}',{6},{7}," +
                                             "1,'{8}',GETDATE(),'{9}','{10}','{11}','{12}','{13}');",
                                             lackOfMaterialProductionOrderFid,                                                                             ///LACK_PORDER_FID,0
                                             pullOrdersInfo.Fid.GetValueOrDefault(),                                                                       ///PRODUCTION_ORDER_FID,1
                                             lackDetailInfo.Fid.GetValueOrDefault(),                                                                       ///LACK_DETAIL_FID,2
                                             lackDetailInfo.PartNo,                                                                                        ///PART_NO,3
                                             lackDetailInfo.SupplierNum,                                                                                   ///SUPPLIER_NUM,4
                                             lackDetailInfo.Plant,                                                                                         ///PLANT,5
                                             lackDetailInfo.Zqty,                                                                                          ///REQUIRE_QTY,6
                                             lackDetailInfo.FeedbackLackQty >= lackDetailInfo.Zqty ? lackDetailInfo.Zqty : lackDetailInfo.FeedbackLackQty, ///LACK_QTY,7
                                             loginUser,                                                                                                    ///CREATE_USER,8
                                             lackDetailInfo.PartPurchaser,                                                                                 ///PART_PURCHASER,9
                                             pullOrdersInfo.OrderNo,                                                                                       ///PRODUCTION_ORDER_NO,10
                                             pullOrdersInfo.AssemblyLine,                                                                                  ///ASSEMBLY_LINE,11
                                             pullOrdersInfo.OrderDate.GetValueOrDefault(),                                                                 ///ORDER_DATE,12
                                             lackDetailInfo.LackOrderFid.GetValueOrDefault()                                                               ///LACK_ORDER_FID,13
                                             );
                    }
                }
                #endregion

                ///将差异值作为缺件数量以用于下一份生产订单的比对
                lackOfMaterialDetailInfos = (from l in lackOfMaterialDetailInfos
                                             join p in pullOrderBomQuery
                                             on new { l.PartNo, l.SupplierNum } equals new { p.PartNo, p.SupplierNum } into temp
                                             from tt in temp.DefaultIfEmpty()
                                             select new LackOfMaterialDetailInfo {
                    PartNo = l.PartNo, SupplierNum = l.SupplierNum, Plant = l.Plant, PartPurchaser = l.PartPurchaser, FeedbackLackQty = l.FeedbackLackQty - (tt == null ? 0 : tt.Zqty), Fid = l.Fid, LackOrderFid = l.LackOrderFid
                }).ToList();
            }
            #region 从检查日期范围内移出的生产订单
            ///移除的历史生产订单
            var lackOfMaterialProductionOrderRemoveInfos = (from l in lackOfMaterialProductionOrderInfos
                                                            where !(from p in pullOrdersInfos where p.OrderNo == l.ProductionOrderNo select p).Any()
                                                            select l).ToList();
            foreach (var lackOfMaterialProductionOrderRemoveInfo in lackOfMaterialProductionOrderRemoveInfos)
            {
                ///更新状态
                sqlText.AppendFormat("update [LES].[TT_ATP_PORDER_LACK_MATERIAL] set " +
                                     "[LACK_FLAG] = {3}," +
                                     "[LACK_ORDER_FID] = N'{0}'," +
                                     "[MODIFY_USER] = N'{1}'," +
                                     "[MODIFY_DATE] = GETDATE() where " +
                                     "[PRODUCTION_ORDER_FID] = N'{2}';",
                                     lackOfMaterialInfo.Fid.GetValueOrDefault(),                                     ///LACK_ORDER_FID,0
                                     loginUser,                                                                      ///MODIFY_USER,1
                                     lackOfMaterialProductionOrderRemoveInfo.ProductionOrderFid.GetValueOrDefault(), ///PRODUCTION_ORDER_FID,2
                                     0                                                                               ///LACK_FLAG,3
                                     );
                ///删除明细
                sqlText.AppendFormat("delete from [LES].[TT_ATP_PORDER_LACK_MATERIAL_DETAIL] where " +
                                     "[PRODUCTION_ORDER_FID] = N'{0}';",
                                     lackOfMaterialProductionOrderRemoveInfo.ProductionOrderFid.GetValueOrDefault()///PRODUCTION_ORDER_FID,0
                                     );
                ///插入到SAP中间表
                if (lackMaterialProductionOrdersToSap.ToLower() == "true")
                {
                    sqlText.AppendFormat(" insert into [LES].[TI_IFM_SAP_PRODUCTION_ORDER_LACK_MATERIAL] (" +
                                         "[FID],[ENTERPRISE],[AREA_NO],[DMS_NO],[MATERIAL_CHECK],[SEND_TIME],[VALID_FLAG],[CREATE_USER],[CREATE_DATE],[PROCESS_FLAG],[LOG_FID]) values (" +
                                         "NEWID(),'{0}','{1}','{2}',{6},GETDATE(),1,'{3}',GETDATE(),{5},'{4}');",
                                         lackOfMaterialProductionOrderRemoveInfo.Plant,             ///ENTERPRISE,0
                                         lackOfMaterialProductionOrderRemoveInfo.AssemblyLine,      ///AREA_NO,1
                                         lackOfMaterialProductionOrderRemoveInfo.ProductionOrderNo, ///DMS_NO,2
                                         loginUser,                                                 /// CREATE_USER,3
                                         logFid,                                                    ///LOG_FID,4
                                         (int)ProcessFlagConstants.Untreated,                       ///PROCESS_FLAG,5
                                         0                                                          ///MATERIAL_CHECK,6
                                         );
                }
                ///插入到MES中间表
                if (lackMaterialProductionOrdersToMes.ToLower() == "true")
                {
                    sqlText.AppendFormat("insert into [LES].[TI_IFM_MES_PRODUCTION_ORDER_LACK_MATERIAL] (" +
                                         "[FID],[ENTERPRISE],[AREA_NO],[DMS_NO],[MATERIAL_CHECK],[SEND_TIME],[VALID_FLAG],[CREATE_USER],[CREATE_DATE],[PROCESS_FLAG],[LOG_FID]) values (" +
                                         "NEWID(),'{0}','{1}','{2}',{6},GETDATE(),1,'{3}',GETDATE(),{5},'{4}');",
                                         lackOfMaterialProductionOrderRemoveInfo.Plant,             ///ENTERPRISE,0
                                         lackOfMaterialProductionOrderRemoveInfo.AssemblyLine,      ///AREA_NO,1
                                         lackOfMaterialProductionOrderRemoveInfo.ProductionOrderNo, ///DMS_NO,2
                                         loginUser,                                                 /// CREATE_USER,3
                                         logFid,                                                    ///LOG_FID,4
                                         (int)ProcessFlagConstants.Untreated,                       ///PROCESS_FLAG,5
                                         0                                                          ///MATERIAL_CHECK,6
                                         );
                }
            }
            #endregion
            ///
            if (sqlText.Length > 0)
            {
                ///调用发送数据
                string keyValue = lackOfMaterialInfo.StartDate.GetValueOrDefault().ToString("yyyyMMdd") + "~" + lackOfMaterialInfo.EndDate.GetValueOrDefault().ToString("yyyyMMdd");
                if (lackMaterialProductionOrdersToSap.ToLower() == "true")
                {
                    sqlText.AppendFormat(BLL.LES.CommonBLL.GetCreateOutboundLogSql("SAP", logFid, "LES-SAP-017", keyValue, loginUser));
                }
                if (lackMaterialProductionOrdersToMes.ToLower() == "true")
                {
                    sqlText.AppendFormat(BLL.LES.CommonBLL.GetCreateOutboundLogSql("MES", logFid, "LES-MES-002", keyValue, loginUser));
                }
                ///计算完成后记录计算结束时间⑨并更新状态⑦为70已完成,同时将缺件生产订单及生产订单缺件明细一次性写入数据库中
                sqlText.AppendFormat("update [LES].[TT_ATP_LACK_OF_MATERIAL] set " +
                                     "[STATUS] = {2}," +
                                     "[EXECUTE_END_TIME] = GETDATE()," +
                                     "[MODIFY_USER] = N'{0}'," +
                                     "[MODIFY_DATE] = GETDATE() where " +
                                     "[ID] = {1};",
                                     loginUser,                                        ///MODIFY_USER,0
                                     lackOfMaterialInfo.Id,                            ///ID,1
                                     (int)LackOfMaterialStatusConstants.OrderCompleted ///STATUS,2
                                     );
                #region 执行
                using (TransactionScope trans = new TransactionScope())
                {
                    BLL.LES.CommonBLL.ExecuteNonQueryBySql(sqlText.ToString());
                    trans.Complete();
                }
                #endregion
            }
        }
コード例 #2
0
 public long InsertInfo(PorderLackMaterialInfo info)
 {
     return(dal.Add(info));
 }