优化

王 垚

2022-01-10 a93a49e6415af2187d0f465e453ee613f3eaf178

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

@@ -28,26 +28,29 @@
    public class Pro_ScDayImport : AbstractDynamicFormPlugIn
    {
        private string fileFullPath = string.Empty;

        //public override void OnLoad(EventArgs e)
        //{
        //    base.OnLoad(e);
        //    throw new Exception("1");
        //}
        /// <summary>
        /// 页面初始化 渲染界面 将T0-T60更改为日期显示
        /// </summary>
        /// <param name="e"></param>
        public override void OnInitialize(InitializeEventArgs e)
        {
            base.OnInitialize(e);
            DateTime DateNow = DateTime.Now;
            for (int m = 0; m <= 60; m++)
            {
                this.View.GetControl("FT" + "" + m + "").ControlAppearance.TextColor = "#FFFF00";
                this.View.GetControl("FT" + "" + m + "").Text = DateNow.AddDays(m).ToShortDateString();
            }
            this.View.SendDynamicFormAction(View);
        }
        public override void BarItemClick(BarItemClickEventArgs e)
        {
            //LogHelper.Info("导入功能点击按钮：");
            if (e.BarItemKey.ToUpper().Equals("TBIMPORT"))
            {
                //LogHelper.Info("导入功能点击按钮：");
                ImportData();
                //WriteImportLog();
                //this.View.ShowErrMessage("测试导入功能点击按钮");
            }

            if (e.BarItemKey.ToUpper().Equals("TBWJSC"))
            {
                //LogHelper.Info("文件上传功能点击按钮：");
                ImportDataSC(fileFullPath);
            }
        }
@@ -108,9 +111,14 @@
                        return;
                    }
                    dt.Rows.RemoveAt(0);//去除标题行
                    //DataSet ds = helper.ReadFromFile(fileFullPath, 1);
                    //DataTable dt = ds.Tables[0];
                    //LogHelper.Info("导入功能：dt行数：" + dt.Rows.Count);
                    string FOrderType = this.Model.GetValue("FOrderType").ToString();
                    string FTempName = "T_PRD_MO";
                    string FTempNameEntry = "T_PRD_MOENTRY";
                    if (FOrderType == "委外订单")
                    {
                        FTempName = "T_SUB_REQORDER";
                        FTempNameEntry = "T_SUB_REQORDERENTRY";
                    }
                    DateTime date = Convert.ToDateTime(this.Model.GetValue("F_Paez_Date"));
                    if (dt.Rows.Count > 0)
                    {
@@ -120,19 +128,46 @@
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            error = "";
                            if (dt.Rows[i][dicExcelCol["组织编码"]].ToString() == "")
                                error += "组织编码不能为空;";
                            if (dt.Rows[i][dicExcelCol["生产订单号"]].ToString() == "")
                                error += "生产订单号不能为空;\n";
                                error += "生产订单号不能为空;";
                            if (dt.Rows[i][dicExcelCol["生产订单明细行号"]].ToString() == "")
                                error += "生产订单明细行号不能为空;";
                            if (dt.Rows[i][dicExcelCol["生产车间编码"]].ToString() == "")
                                error += "生产车间编码不能为空;\n";
                            //增加员工
                                error += "生产车间编码不能为空;";
                            if (dt.Rows[i][dicExcelCol["生产资源编码"]].ToString() == "")
                                error += "生产资源编码不能为空;";
                            if (dt.Rows[i][dicExcelCol["物料编码"]].ToString() == "")
                                error += "物料编码不能为空;\n";
                                error += "物料编码不能为空;";
                            if (dt.Rows[i][dicExcelCol["交货日期"]].ToString() == "")
                                error += "交货日期不能为空;\n";
                                error += "交货日期不能为空;";
                            if (dt.Rows[i][dicExcelCol["订单等级"]].ToString() == "")
                                error += "订单等级不能为空;\n";
                                error += "订单等级不能为空;";
                            //验证 生产资源 + 物料 判断 是否有维护产能表 
                            string cnSql = string.Format(@"/*dialect*/ 
SELECT Count(*) row
FROM T_PRD_MATERIALCOMPARE T1 
JOIN T_PRD_MATERIALCOMPAREENTRY T2 ON T1.FID=T2.FID
JOIN T_BD_MATERIAL T3 ON T2.FMATERIALID = T3.FMATERIALID
JOIN T_ORG_ORGANIZATIONS T4 ON T1.FORGID = T4.FORGID
JOIN T_JIT_WORKCENTER T5 ON T2.FSCZY = T5.FID
WHERE T4.FNUMBER = '{0}' AND T3.FNUMBER='{1}' AND T5.FNUMBER ='{2}'",
dt.Rows[i][dicExcelCol["组织编码"]].ToString(), dt.Rows[i][dicExcelCol["物料编码"]].ToString(), dt.Rows[i][dicExcelCol["生产资源编码"]].ToString());
                            int cnFid = DBServiceHelper.ExecuteScalar<int>(Context, cnSql, 0);
                            if (cnFid == 0)
                                error += "没有维护产能表;";
                            cnSql = string.Format(@"/*dialect*/ 
SELECT Count(*) row  FROM {0} T1
LEFT JOIN {1} T2 ON T1.FID = T2.FID
JOIN T_BD_MATERIAL T3 ON T2.FMATERIALID = T3.FMATERIALID
join (  select A.FBILLTYPEID,B.FNAME  from T_BAS_BILLTYPE A 
JOIN T_BAS_BILLTYPE_L B ON A.FBILLTYPEID =B.FBILLTYPEID
where FBILLFORMID in ('PRD_MO','SUB_SUBREQORDER')   AND  b.FLOCALEID = 2052)t4 on t1.FBILLTYPE =t4.FBILLTYPEID
WHERE T1.FBILLNO = '{2}'  AND T3.FNUMBER = '{3}' and FSEQ='{4}' ", FTempName, FTempNameEntry, dt.Rows[i][dicExcelCol["生产订单号"]].ToString(), dt.Rows[i][dicExcelCol["物料编码"]].ToString(), dt.Rows[i][dicExcelCol["生产订单明细行号"]].ToString());
                            cnFid = DBServiceHelper.ExecuteScalar<int>(Context, cnSql, 0);
                            if (cnFid == 0)
                                error += "没有生产订单信息;";
                            if (error.Length > 0)
                                ErrorResult += $"Excel模板存在错误，行数{i + 1}:{error}\r\n";
                        }
@@ -146,13 +181,13 @@
                        {
                            //根据生产订单号和物料查询是否存在明细(2.28+生产订单明细行号验证)
                            string sql = string.Format(@"/*dialect*/ 
SELECT t4.FNAME as FMoFBillType,T1.FBILLNO,T2.*  FROM T_PRD_MO T1
LEFT JOIN T_PRD_MOENTRY T2 ON T1.FID = T2.FID
SELECT t4.FNAME as FMoFBillType,T1.FBILLNO,T2.*  FROM {0} T1
LEFT JOIN {1} T2 ON T1.FID = T2.FID
JOIN T_BD_MATERIAL T3 ON T2.FMATERIALID = T3.FMATERIALID
join (  select A.FBILLTYPEID,B.FNAME  from T_BAS_BILLTYPE A 
JOIN T_BAS_BILLTYPE_L B ON A.FBILLTYPEID =B.FBILLTYPEID
where FBILLFORMID='PRD_MO'  AND FNAME<>'生產訂單')t4 on t1.FBILLTYPE =t4.FBILLTYPEID
WHERE T1.FBILLNO = '{0}'  AND T3.FNUMBER = '{1}' and FSEQ='{2}' ", dt.Rows[i][dicExcelCol["生产订单号"]].ToString(), dt.Rows[i][dicExcelCol["物料编码"]].ToString(), dt.Rows[i][dicExcelCol["生产订单明细行号"]].ToString());
where FBILLFORMID in ('PRD_MO','SUB_SUBREQORDER')   AND  b.FLOCALEID = 2052)t4 on t1.FBILLTYPE =t4.FBILLTYPEID
WHERE T1.FBILLNO = '{2}'  AND T3.FNUMBER = '{3}' and FSEQ='{4}' ", FTempName, FTempNameEntry, dt.Rows[i][dicExcelCol["生产订单号"]].ToString(), dt.Rows[i][dicExcelCol["物料编码"]].ToString(), dt.Rows[i][dicExcelCol["生产订单明细行号"]].ToString());
                            DataTable _dt = new DataTable();
                            _dt = DBServiceHelper.ExecuteDataSet(Context, sql).Tables[0];
                            string FLOT = "";//FLOT  批次
@@ -280,6 +315,18 @@
        {
            try
            {
                string FOrderType = this.Model.GetValue("FOrderType").ToString();
                string FTempName = "T_PRD_MO";
                string FTempNameEntry = "T_PRD_MOENTRY";
                string FTempBomName = "T_PRD_PPBOM";
                string FTempBomNameEntry = "T_PRD_PPBOMENTRY";
                if (FOrderType == "委外订单")
                {
                    FTempName = "T_SUB_REQORDER";
                    FTempNameEntry = "T_SUB_REQORDERENTRY";
                    FTempBomName = "T_SUB_PPBOM";
                    FTempBomNameEntry = "T_SUB_PPBOMENTRY";
                }
                CloudClient cloudClient = new CloudClient("http://localhost//k3cloud/");
                string sql;
                DateTime date = Convert.ToDateTime(this.Model.GetValue("F_Paez_Date"));
@@ -293,9 +340,12 @@
                foreach (DynamicObject current in entityDataObjoct)
                {
                    //根据生产订单号和物料查询是否存在生产订单明细明细 +生产订单明细行号 + 组织(2021-12-10修改)
                    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}' AND T2.FSEQ = '{2}' AND T1.FPRDORGID = '{3}'", Convert.ToString(current["FSCOrderNo"]), Convert.ToString(current["FMatrailId_Id"]), Convert.ToString(current["FSRCBILLENTRYSEQ"]), Convert.ToString(current["FORGID_Id"]));
                    sql = string.Format(@"
/*dialect*/
SELECT T1.FBILLNO,T2.* FROM {4} T1
LEFT JOIN {5} T2 ON T1.FID = T2.FID
WHERE T1.FBILLNO = '{0}'  AND T2.FMATERIALID = '{1}' AND T2.FSEQ = '{2}' AND T1.{6} = '{3}'"
, Convert.ToString(current["FSCOrderNo"]), Convert.ToString(current["FMatrailId_Id"]), Convert.ToString(current["FSRCBILLENTRYSEQ"]), Convert.ToString(current["FORGID_Id"]), FTempName, FTempNameEntry, FOrderType == "委外订单" ? "FSUBORGID" : "FPRDORGID");
                    //LogHelper.Info(sql);
                    DataTable dt = new DataTable();
                    dt = DBServiceHelper.ExecuteDataSet(Context, sql).Tables[0];
@@ -308,16 +358,37 @@
                    return;
                }
                //校验生产用料清单是否审核
                if (FOrderType == "生产订单")
                {
                foreach (DynamicObject current in entityDataObjoct)
                {
                    //根据生产订单号和物料查询是否存在明细 +生产订单明细行号
                    sql = string.Format(@"/*dialect*/SELECT FBILLNO FROM T_PRD_PPBOM T1
WHERE T1.FMOBILLNO = '{0}'  AND T1.FMATERIALID = '{1}' AND T1.FMOENTRYSEQ = '{2}' and FDOCUMENTSTATUS <>'C'                          
", Convert.ToString(current["FSCOrderNo"]), Convert.ToString(current["FMatrailId_Id"]), Convert.ToString(current["FSRCBILLENTRYSEQ"]));
                        sql = string.Format(@"
/*dialect*/
SELECT FBILLNO FROM {0} T1
WHERE T1.FMOBILLNO = '{1}'  AND T1.FMATERIALID = '{2}' AND T1.FMOENTRYSEQ = '{3}' and FDOCUMENTSTATUS <>'C'                          
", FTempBomName, 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"]) + ",生产订单明细行号：" + Convert.ToString(current["FSRCBILLENTRYSEQ"]) + "物料" + (current["FMatrailId"] as DynamicObject)["Number"] + "对应的生产用料清单：" + dt.Rows[0][0] + "未审核\n";
                    }
                }
                else
                {
                    foreach (DynamicObject current in entityDataObjoct)
                    {
                        //根据生产订单号和物料查询是否存在明细 +生产订单明细行号
                        sql = string.Format(@"
/*dialect*/
SELECT FBILLNO FROM {0} T1
WHERE T1.FSUBBILLNO = '{1}'  AND T1.FMATERIALID = '{2}' AND T1.FSUBREQENTRYSEQ = '{3}' and FDOCUMENTSTATUS <>'C'                          
", FTempBomName, 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"]) + ",生产订单明细行号：" + Convert.ToString(current["FSRCBILLENTRYSEQ"]) + "物料" + (current["FMatrailId"] as DynamicObject)["Number"] + "对应的生产用料清单：" + dt.Rows[0][0] + "未审核\n";
                    }
                }
                if (_result != "")
                {
@@ -356,14 +427,14 @@
                    //查询生产订单数据 
                    sql = string.Format(@"
/*dialect*/
SELECT t4.FNAME as FMoFBillType,T1.FBILLNO,T2.*,t3.FNUMBER as FUnitNumber,tBom.FNUMBER as FBomNumber FROM T_PRD_MO T1
 LEFT JOIN T_PRD_MOENTRY T2 ON T1.FID = T2.FID
SELECT t4.FNAME as FMoFBillType,T1.FBILLNO,T2.*,t3.FNUMBER as FUnitNumber,tBom.FNUMBER as FBomNumber FROM {3} T1
 LEFT JOIN {4} T2 ON T1.FID = T2.FID
 LEFT JOIN T_ENG_BOM tBom on t2.FBOMID = tBom.FID
 join T_BD_UNIT t3 on t2.FUNITID=t3.FUNITID 
 join (  select A.FBILLTYPEID,B.FNAME  from T_BAS_BILLTYPE A 
JOIN T_BAS_BILLTYPE_L B ON A.FBILLTYPEID =B.FBILLTYPEID
where FBILLFORMID='PRD_MO'  AND FNAME<>'生產訂單')t4 on t1.FBILLTYPE =t4.FBILLTYPEID
 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"]));
where FBILLFORMID in ('PRD_MO','SUB_SUBREQORDER')   AND b.FLOCALEID = 2052)t4 on t1.FBILLTYPE =t4.FBILLTYPEID
 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"]), FTempName, FTempNameEntry);
                    DataTable dt = new DataTable();
                    dt = DBServiceHelper.ExecuteDataSet(Context, sql).Tables[0];
                    if (dt.Rows.Count == 0)
@@ -401,6 +472,7 @@
                    JObject jsonModel = new JObject();
                    jsonModel.Add("FPreparatDate", FPlanBeginDate);//编制日期(改为计划开始日期)
                    jsonModel.Add("FMoFBillType", FMoFBillType);//
                    jsonModel.Add("FOrderType", FOrderType);//
                    jsonModel.Add("FSRCBILLENTRYSEQ", Convert.ToString(current["FSRCBILLENTRYSEQ"]));//生产订单明细行号
                    jsonModel.Add("FSCOrderNo", Convert.ToString(current["FSCOrderNo"])); //生产订单号FSCOrderNo
                    jsonModel.Add("FHSeOrderBillNo", Convert.ToString(current["FSalOrderNo"]));//销售订单号