日计划导入增加生产订单明细行号功能

王 垚

2021-03-01 4b0ac821502f956d7b0acd73f80a384ee41f6a6a

日计划导入增加生产订单明细行号功能

 src/BLL/Demo.BillView/PRD/Pro_ScDayImport.cs

@@ -119,7 +119,7 @@
                            if (dt.Rows[i][1].ToString() == "")
                                error += "生产订单号不能为空;";
                            if (dt.Rows[i][2].ToString() == "")
                                error += "组织不能为空;";
                                error += "生产订单明细行号不能为空;";
                            if (dt.Rows[i][3].ToString() == "")
                                error += "生产车间不能为空;";
                            if (dt.Rows[i][5].ToString() == "")
@@ -140,11 +140,11 @@
                        this.Model.DeleteEntryData("FEntity");
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            //根据生产订单号和物料查询是否存在明细
                            //根据生产订单号和物料查询是否存在明细(2.28+生产订单明细行号验证)
                            string sql = string.Format(@"/*dialect*/ SELECT T1.FBILLNO,T2.* FROM T_PRD_MO T1
LEFT JOIN T_PRD_MOENTRY T2 ON T1.FID = T2.FID
JOIN T_BD_MATERIAL T3 ON T2.FMATERIALID = T3.FMATERIALID
WHERE T1.FBILLNO = '{0}'  AND T3.FNUMBER = '{1}' ", dt.Rows[i][1].ToString(), dt.Rows[i][5].ToString());
WHERE T1.FBILLNO = '{0}'  AND T3.FNUMBER = '{1}' and FSEQ='{2}' ", dt.Rows[i][1].ToString(), dt.Rows[i][5].ToString() ,dt.Rows[i][2].ToString());
                            DataTable _dt = new DataTable();
                            _dt = DBServiceHelper.ExecuteDataSet(Context, sql).Tables[0];
                            string FLOT = "";//FLOT  批次
@@ -156,12 +156,12 @@
                                FQTY = _dt.Rows[0]["FQTY"].ToString();
                                FBILLNO = _dt.Rows[0]["FBILLNO"].ToString();
                            }
                            //物料编码 + 生产订单号 + 生产资源 去判断是否导入过 带出日计划工单ID及编号
                            //物料编码 + 生产订单号 + 生产资源 + 生产订单行号 去判断是否导入过 带出日计划工单ID及编号
                            string fDayPlanID = "";
                            string fDayPlanBillNo = "";
                            sql = string.Format(@"select a.FID,FBILLNO,FSCOrderNo,FHMaterID,FHSourceID from Sc_WorkBillSortBillMain a JOIN T_BD_MATERIAL b ON a.FHMaterID = b.FMATERIALID 
left join T_ENG_WORKCENTER c on a.FHSourceID=c.fid 
 where FSCOrderNo ='{0}' and b.FNUMBER ='{1}' and (isnull(c.FNUMBER,'') ='{2}' or (select count(*) from T_ENG_WORKCENTER where isnull(c.FNUMBER,'') ='{2}')=0)", dt.Rows[i][1].ToString(), dt.Rows[i][5].ToString(), dt.Rows[i][4].ToString());
 where FSCOrderNo ='{0}' and b.FNUMBER ='{1}' and a.FSRCBILLENTRYSEQ ='{3}' and (isnull(c.FNUMBER,'') ='{2}' or (select count(*) from T_ENG_WORKCENTER where isnull(c.FNUMBER,'') ='{2}')=0)", dt.Rows[i][1].ToString(), dt.Rows[i][5].ToString(), dt.Rows[i][4].ToString(), dt.Rows[i][2].ToString());
                            DataTable dayPlanDt = DBServiceHelper.ExecuteDataSet(Context, sql).Tables[0];
                            if (dayPlanDt.Rows.Count > 0)
                            {
@@ -186,6 +186,7 @@
                            this.Model.SetValue("FOrderQuantity", FQTY, i);
                            this.Model.SetValue("FDayPlanID", fDayPlanID, i);//日计划工单ID
                            this.Model.SetValue("FDayPlanBillNo", fDayPlanBillNo, i);//日工单编号
                            this.Model.SetValue("FSRCBILLENTRYSEQ", dt.Rows[i][2].ToString(),  i);//生产订单明细行号
                            for (int j = 0; j <= 60; j++)
                            {
                                this.Model.SetValue("FT" + "" + j + "", dt.Rows[i][15 + j].ToString(), i);
@@ -203,8 +204,6 @@
                }

            }


        }
        /// <summary>
        /// 订单状态等级
@@ -274,15 +273,14 @@
                string _result = "";
                foreach (DynamicObject current in entityDataObjoct)
                {
                    //根据生产订单号和物料查询是否存在明细
                    //根据生产订单号和物料查询是否存在明细 +生产订单明细行号
                    sql = string.Format(@"/*dialect*/SELECT T1.FBILLNO,T2.* FROM T_PRD_MO T1
                                LEFT JOIN T_PRD_MOENTRY T2 ON T1.FID = T2.FID
                                WHERE T1.FBILLNO = '{0}'  AND T2.FMATERIALID = '{1}'", Convert.ToString(current["FSCOrderNo"]), Convert.ToString(current["FMatrailId_Id"]));
                                WHERE T1.FBILLNO = '{0}'  AND T2.FMATERIALID = '{1}' AND T2.FSEQ = '{2}'", Convert.ToString(current["FSCOrderNo"]), Convert.ToString(current["FMatrailId_Id"]), Convert.ToString(current["FSRCBILLENTRYSEQ"]));
                    DataTable dt = new DataTable();
                    dt = DBServiceHelper.ExecuteDataSet(Context, sql).Tables[0];
                    if (dt.Rows.Count == 0)
                        _result += "序号：" + Convert.ToString(current["SEQ"]) + ",生产订单号：" + Convert.ToString(current["FSCOrderNo"]) + "对应的物料" + (current["FMatrailId"] as DynamicObject)["Number"] + "不存在";

                        _result += "序号：" + Convert.ToString(current["SEQ"]) + ",生产订单号：" + Convert.ToString(current["FSCOrderNo"])+ ",生产订单明细行号：" + Convert.ToString(current["FSRCBILLENTRYSEQ"]) + "对应的物料" + (current["FMatrailId"] as DynamicObject)["Number"] + "不存在";
                }
                if (_result != "")
                {
@@ -303,10 +301,10 @@
                {
                    if (string.IsNullOrEmpty(Convert.ToString(current["FSCOrderNo"])))
                        continue;
                    //根据生产订单号和物料查询是否存在明细
                    //根据生产订单号和物料查询是否存在明细 +生产订单明细行号
                    sql = string.Format(@"/*dialect*/SELECT T1.FBILLNO,T2.* FROM T_PRD_MO T1
                                LEFT JOIN T_PRD_MOENTRY T2 ON T1.FID = T2.FID
                                WHERE T1.FBILLNO = '{0}'  AND T2.FMATERIALID = '{1}'", Convert.ToString(current["FSCOrderNo"]), Convert.ToString(current["FMatrailId_Id"]));
                                WHERE T1.FBILLNO = '{0}'  AND T2.FMATERIALID = '{1}' AND T2.FSEQ = '{2}'", Convert.ToString(current["FSCOrderNo"]), Convert.ToString(current["FMatrailId_Id"]), Convert.ToString(current["FSRCBILLENTRYSEQ"]));
                    DataTable dt = new DataTable();
                    dt = DBServiceHelper.ExecuteDataSet(Context, sql).Tables[0];
                    if (dt.Rows.Count == 0)
@@ -333,12 +331,13 @@
                    jsonRoot.Add("IsVerifyBaseDataField", "");
                    JObject jsonModel = new JObject();
                    jsonModel.Add("FPreparatDate", date.ToString());//编制日期
                    jsonModel.Add("FSRCBILLENTRYSEQ", date.ToString());//生产订单明细行号
                    jsonModel.Add("FSCOrderNo", Convert.ToString(current["FSCOrderNo"])); //生产订单号FSCOrderNo
                    jsonModel.Add("FHSeOrderBillNo", Convert.ToString(current["FSalOrderNo"]));//销售订单号
                    jsonModel.Add("FHOrderLev", current["FOrderLevel"].ToString());//订单等级
                    jsonModel.Add("FHOrderQty", Convert.ToString(current["FSalOrderCount"]));//销售订单数量
                    jsonModel.Add("FHOrderCommitDate", Convert.ToString(current["FDeliveryDate"]));//交货期HOrderCommitDate
                                                                                            //jsonModel.Add("FBatch", current["FBatch"].ToString());//批次
                                                                                                   //jsonModel.Add("FBatch", current["FBatch"].ToString());//批次
                    jsonModel.Add("FBatch", FLOT);//批次
                    jsonModel.Add("FHOrderNeedQty", FQTY);// 订单需求数量
                    jsonModel.Add("FPrdBillNo", FBILLNO); ;//源单编号
@@ -374,6 +373,7 @@
                    {
                        sql = $"select max(FHMASTERDATE)FHMASTERDATE from SC_WORKBILLSORTBILLSub where FID={Convert.ToString(current["FDayPlanID"])} ";
                        DateTime EndDate = DBServiceHelper.ExecuteScalar<DateTime>(Context, sql, DateTime.Now);
                        int fseq = 1;
                        for (int i = 0; i <= 60; i++)
                        {
                            if (Convert.ToString(current["FT" + "" + i + ""]) == "" || Convert.ToString(current["FT" + "" + i + ""]) == "0")
@@ -384,7 +384,9 @@
                            jsonFPOOrderEntry.Add("FHMasterDate", date.AddDays(i).ToShortDateString()); ;//主日期
                            jsonFPOOrderEntry.Add("FHQty ", Convert.ToString(current["FT" + "" + i + ""])); ;//日计划数量
                            jsonFPOOrderEntry.Add("FColumn", "FT" + "" + i + ""); ;//对应的列头
                            jsonFPOOrderEntry.Add("FSEQ", fseq); ;//序号
                            Entry.Add(jsonFPOOrderEntry);
                            fseq++;
                        }
                        jsonModel.Add("FEntity", Entry);
                        jsonModel.Add("FID", Convert.ToString(current["FDayPlanID"])); ;//源单编号
@@ -407,22 +409,18 @@
                    }
                    else
                    {
                        int fseq = 1;
                        for (int i = 0; i <= 60; i++)
                        {
                            LogHelper.Error("测试跳出");


                            if (Convert.ToString(current["FT" + "" + i + ""]) == "" || Convert.ToString(current["FT" + "" + i + ""]) == "0")
                                continue;

                            LogHelper.Error("测试跳出结束");


                            JObject jsonFPOOrderEntry = new JObject();
                            jsonFPOOrderEntry.Add("FHMasterDate", date.AddDays(i).ToShortDateString()); ;//主日期
                            jsonFPOOrderEntry.Add("FHQty ", Convert.ToString(current["FT" + "" + i + ""])); ;//日计划数量
                            jsonFPOOrderEntry.Add("FColumn", "FT" + "" + i + ""); ;//对应的列头
                            jsonFPOOrderEntry.Add("FSEQ", fseq); ;//序号
                            Entry.Add(jsonFPOOrderEntry);
                            fseq++;
                        }
                        jsonModel.Add("FEntity", Entry);
                        jsonRoot.Add("Model", jsonModel);

 src/BLL/Demo.BillView/PRD/Pro_WorkBillPlatform.cs

@@ -164,7 +164,7 @@
        /// </summary>
        public void ExportExcel()
        {
            List<string> Title = new List<string> {"销售订单号", "生产订单号*","生产组织*", "生产车间*", "生产资源", "产品代码*", "产品名称", "规格型号", "计量单位",
            List<string> Title = new List<string> {"销售订单号", "生产订单号*","生产订单明细行号*", "生产车间*", "生产资源", "产品代码*", "产品名称", "规格型号", "计量单位",
                "批次号","销售订单数量","生产订单数量","总齐套数量","交货日期*","订单等级*"};
            /*
             ,"T0","T1","T2","T3","T4","T5","T6","T7",
@@ -365,13 +365,14 @@
            }

            int result = DBServiceHelper.ExecuteBatch(Context, sqlList);
            if (result > 0)
            {
                this.View.ShowMessage("操作成功");
                SearchList();
            }
            else
                this.View.ShowMessage("操作失败，联系管理员！");

            this.View.ShowMessage("操作成功");
            SearchList();
            //if (result > 0)
            //{
            //}
            //else
            //    this.View.ShowMessage("操作失败，联系管理员！");

        }
        /// <summary>
@@ -953,7 +954,7 @@
LEFT JOIN T_STK_Inventory a on T1.FStockOrgId = a.FStockOrgId and T1.FMATERIALID =a.FMATERIALID 
LEFT JOIN JIT_MOMaterReadysBill B ON T1.FStockOrgId = b.FHSTOCKORGID and T1.FMATERIALID =b.FHMATERID and a.FOwnerId = b.FOwnerId 
LEFT join T_ORG_Organizations c on a.FOWNERID = c.FORGID
--join t_BD_Stock d on a.FSTOCKID =d.FSTOCKID
join t_BD_Stock d on a.FSTOCKID =d.FSTOCKID
where d.FAvailableComplete<>0
--where T1.FMATERIALID in (105773)
");
@@ -972,8 +973,8 @@
) T1 
LEFT JOIN T_STK_Inventory a on T1.FStockOrgId = a.FStockOrgId and T1.FMATERIALID =a.FMATERIALID 
LEFT JOIN JIT_MOMaterReadysBill B ON T1.FStockOrgId = b.FHSTOCKORGID and T1.FMATERIALID =b.FHMATERID
--join t_BD_Stock d on a.FSTOCKID =d.FSTOCKID
--where d.FAvailableComplete<>0
join t_BD_Stock d on a.FSTOCKID =d.FSTOCKID
where d.FAvailableComplete<>0
--where T1.FMATERIALID in (105773)
group by T1.FStockOrgId,T1.FMATERIALID
");