优化

王 垚

2021-12-10 742d7030e86066437d8005007337821302bd7e61

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

@@ -5,71 +5,49 @@
using Kingdee.BOS.Orm.DataEntity;
using Kingdee.BOS.ServiceHelper.Excel;
using Kingdee.BOS.Util;
using Kingdee.BOS.ServiceHelper;

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using Newtonsoft.Json.Linq;
using Kingdee.BOS.WebApi.Client;
using ZD.Cloud.WebApi;
using Kingdee.BOS.Core;
using Kingdee.BOS.Core.DynamicForm;
using Kingdee.BOS.KDThread;
using Kingdee.BOS.ServiceHelper;
using Demo.TimedTaskPlugIn.PRD;
using ZD.Cloud.Logger;

using Demo.Model.Model;
using System.Linq;
using ZD.Share.Common;
using ZD.Cloud.WebApi;
using Demo.Utility;
using ZD.Cloud.Logger;
using System.Text;

namespace Demo.BillView.PRD
{

    [Description("生产日计划导入")]
    [HotUpdate]
    public class Pro_ScDayImport : AbstractDynamicFormPlugIn
    {
        CloudClient cloudClient = new CloudClient("http://localhost/K3Cloud/");
        private string fileFullPath = string.Empty;
        //public override void BarItemClick(Kingdee.BOS.Core.DynamicForm.PlugIn.Args.BarItemClickEventArgs e)

        //public override void OnLoad(EventArgs e)
        //{
        //    base.BarItemClick(e);
        //    if (e.BarItemKey.ToUpper().Equals("TBIMPORT"))
        //    {
        //    }

        //    if (e.BarItemKey.ToUpper().Equals("TBWJSC"))
        //    {
        //        //Control updateControl = this.View.GetControl("fileFullPath");
        //        //// 上传地址
        //        //string uploadUrl = "FileUpLoadServices/FileService.svc/upload2attachment/";
        //        //updateControl.SetCustomPropertyValue("ExtandUrl", uploadUrl);
        //        //// 下载地址
        //        //string downloadUrl = "FileUpLoadServices/download.aspx";
        //        //updateControl.SetCustomPropertyValue("ExDownLoadUrl", downloadUrl);

        //        ImportData(fileFullPath);
        //    }

        //    base.OnLoad(e);
        //    throw new Exception("1");
        //}
        public override void AfterBarItemClick(AfterBarItemClickEventArgs e)
        public override void BarItemClick(BarItemClickEventArgs e)
        {
            base.AfterBarItemClick(e);
            //LogHelper.Info("导入功能点击按钮：");
            if (e.BarItemKey.ToUpper().Equals("TBIMPORT"))
            {
                LogService.WriteAsync("导入功能：");
                //LogHelper.Info("导入功能点击按钮：");
                ImportData();
                //WriteImportLog();
                //this.View.ShowErrMessage("测试导入功能点击按钮");
            }

            if (e.BarItemKey.ToUpper().Equals("TBWJSC"))
            {
                //Control updateControl = this.View.GetControl("fileFullPath");
                //// 上传地址
                //string uploadUrl = "FileUpLoadServices/FileService.svc/upload2attachment/";
                //updateControl.SetCustomPropertyValue("ExtandUrl", uploadUrl);
                //// 下载地址
                //string downloadUrl = "FileUpLoadServices/download.aspx";
                //updateControl.SetCustomPropertyValue("ExDownLoadUrl", downloadUrl);
                LogHelper.Info("222222");
                LogService.WriteAsync("上传功能：");
                //LogHelper.Info("文件上传功能点击按钮：");
                ImportDataSC(fileFullPath);
            }
        }
@@ -86,6 +64,16 @@
                    {
                        //获取上传的文件名
                        string text = (jSONArray[0] as Dictionary<string, object>)["ServerFileName"].ToString();
                        if (Convert.ToDecimal((jSONArray[0] as Dictionary<string, object>)["FileBytesLength"]) > 30 * 1024)
                        {
                            this.View.ShowErrMessage("文件过大，重新选择。限制上传30M以上文件");
                            return;
                        }
                        if (!text.Contains(".xlsx") || !text.Contains(".xls"))
                        {
                            this.View.ShowErrMessage("文件类型错误，请上传Excel文件");
                            return;
                        }
                        //文件上传到服务端的临时目录
                        string directory = "FileUpLoadServices\\UploadFiles";
                        //文件的完整路径
@@ -105,9 +93,24 @@
            {
                if (!string.IsNullOrEmpty(fileFullPath))
                {
                    DataSet ds = helper.ReadFromFile(fileFullPath, 1);
                    NpoiHelper np = new NpoiHelper();
                    DataSet ds = np.ReadExcel(fileFullPath, 1);
                    DataTable dt = ds.Tables[0];
                    LogService.WriteAsync("导入功能：dt行数：" + dt.Rows.Count);
                    //去除空行
                    RemoveEmpty(dt);
                    //中文抬头和excel抬头对应
                    Dictionary<string, string> dicExcelCol = new Dictionary<string, string>();
                    string fileTitle = "组织编码,销售订单号,生产订单号,生产订单明细行号,生产车间编码,生产资源编码,员工编码,物料编码,物料名称,规格型号,计量单位,批次号,销售订单数量,生产订单数量,总齐套数量,交货日期,订单等级";
                    string checkTitleErrInfo = CheckUploadFileTitle(dt, fileTitle.Split(','), out dicExcelCol);
                    if (checkTitleErrInfo != "")
                    {
                        this.View.ShowMessage("导入模板错误:\n" + checkTitleErrInfo);
                        return;
                    }
                    dt.Rows.RemoveAt(0);//去除标题行
                    //DataSet ds = helper.ReadFromFile(fileFullPath, 1);
                    //DataTable dt = ds.Tables[0];
                    //LogHelper.Info("导入功能：dt行数：" + dt.Rows.Count);
                    DateTime date = Convert.ToDateTime(this.Model.GetValue("F_Paez_Date"));
                    if (dt.Rows.Count > 0)
                    {
@@ -117,19 +120,19 @@
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            error = "";
                            if (dt.Rows[i][1].ToString() == "")
                                error += "生产订单号不能为空;";
                            if (dt.Rows[i][2].ToString() == "")
                                error += "生产组织不能为空;";
                            if (dt.Rows[i][3].ToString() == "")
                                error += "生产车间不能为空;";
                            if (dt.Rows[i][5].ToString() == "")
                                error += "产品代码不能为空;";
                            if (dt.Rows[i][13].ToString() == "")
                                error += "交货日期不能为空;";
                            if (dt.Rows[i][14].ToString() == "")
                                error += "订单等级不能为空;";

                            if (dt.Rows[i][dicExcelCol["生产订单号"]].ToString() == "")
                                error += "生产订单号不能为空;\n";
                            if (dt.Rows[i][dicExcelCol["生产订单明细行号"]].ToString() == "")
                                error += "生产订单明细行号不能为空;";
                            if (dt.Rows[i][dicExcelCol["生产车间编码"]].ToString() == "")
                                error += "生产车间编码不能为空;\n";
                            //增加员工
                            if (dt.Rows[i][dicExcelCol["物料编码"]].ToString() == "")
                                error += "物料编码不能为空;\n";
                            if (dt.Rows[i][dicExcelCol["交货日期"]].ToString() == "")
                                error += "交货日期不能为空;\n";
                            if (dt.Rows[i][dicExcelCol["订单等级"]].ToString() == "")
                                error += "订单等级不能为空;\n";
                            if (error.Length > 0)
                                ErrorResult += $"Excel模板存在错误，行数{i + 1}:{error}\r\n";
                        }
@@ -141,55 +144,83 @@
                        this.Model.DeleteEntryData("FEntity");
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            //根据生产订单号和物料查询是否存在明细
                            string sql = string.Format(@"/*dialect*/ SELECT T1.FBILLNO,T2.* FROM T_PRD_MO T1
                            //根据生产订单号和物料查询是否存在明细(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
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());
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());
                            DataTable _dt = new DataTable();
                            _dt = DBServiceHelper.ExecuteDataSet(Context, sql).Tables[0];
                            string FLOT = "";//FLOT  批次
                            string FQTY = "";//FQTY 生产订单数量
                            string FBILLNO = "";//FBILLNO 原单号 
                            string FMoFBillType = "";
                            if (_dt.Rows.Count > 0)
                            {
                                FLOT = _dt.Rows[0]["FLOT"].ToString();
                                FQTY = _dt.Rows[0]["FQTY"].ToString();
                                FBILLNO = _dt.Rows[0]["FBILLNO"].ToString();
                                FMoFBillType = _dt.Rows[0]["FMoFBillType"].ToString();
                            }
                            //物料编码 + 生产订单号 + 生产资源 去判断是否导入过 带出日计划工单ID及编号
                            //物料编码 + 生产订单号 + 生产资源 + 生产订单行号 去判断是否导入过 带出日计划工单ID及编号 和 关闭数量（6.18更）
                            //9.2 关闭的日计划需要排除
                            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());
                            string fCloseCount = "";
                            string fQtyLS = "";
                            sql = string.Format(@"/*dialect*/
SELECT A.FID,FBILLNO,FSCORDERNO,FHMATERID,FHSOURCEID,A.FCLOSECOUNT,ISNULL((SELECT SUM(ISNULL(FHQTY,0)) FROM SC_WORKBILLSORTBILLSUB T2 WHERE T2.FID = A.FID AND DATEDIFF(D,FHMASTERDATE,GETDATE())>0),0)FQTYLS  
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 A.FDOCUMENTSTATUS NOT IN ('E') AND 
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][dicExcelCol["生产订单号"]].ToString(), dt.Rows[i][dicExcelCol["物料编码"]].ToString(), dt.Rows[i][dicExcelCol["生产资源编码"]].ToString(), dt.Rows[i][dicExcelCol["生产订单明细行号"]].ToString());
                            ////LogHelper.Error(sql);
                            DataTable dayPlanDt = DBServiceHelper.ExecuteDataSet(Context, sql).Tables[0];
                            if (dayPlanDt.Rows.Count > 0)
                            {
                                fDayPlanID = dayPlanDt.Rows[0]["FID"].ToString();
                                fDayPlanBillNo = dayPlanDt.Rows[0]["FBILLNO"].ToString();
                                fCloseCount = dayPlanDt.Rows[0]["FCloseCount"].ToString();
                                fQtyLS = dayPlanDt.Rows[0]["FQTYLS"].ToString();
                            }
                            //DynamicObject row = new DynamicObject(entity.DynamicObjectType);
                            this.Model.CreateNewEntryRow("FEntity");
                            this.Model.SetValue("FSEQ", i + 1, i);
                            this.Model.SetValue("FSalOrderNo", dt.Rows[i][0].ToString(), i);
                            this.Model.SetValue("FSCOrderNo", dt.Rows[i][1].ToString(), i);
                            this.Model.SetItemValueByNumber("FORGID", dt.Rows[i][2].ToString(), i);
                            this.Model.SetItemValueByNumber("FProductWorkShopId", dt.Rows[i][3].ToString(), i);
                            this.Model.SetItemValueByNumber("FResourcesId", dt.Rows[i][4].ToString(), i);
                            this.Model.SetValue("FMoFBillType", FMoFBillType, i);
                            this.Model.SetValue("FSalOrderNo", dt.Rows[i][dicExcelCol["销售订单号"]].ToString(), i);
                            this.Model.SetValue("FSCOrderNo", dt.Rows[i][dicExcelCol["生产订单号"]].ToString(), i);
                            if (dt.Rows[i][dicExcelCol["组织编码"]].ToString() == "")
                            {
                                this.Model.SetValue("FORGID", Context.CurrentOrganizationInfo.ID, i);
                            }
                            else
                            {
                                this.Model.SetItemValueByNumber("FORGID", dt.Rows[i][dicExcelCol["组织编码"]].ToString(), i);
                            }
                            this.Model.SetItemValueByNumber("FProductWorkShopId", dt.Rows[i][dicExcelCol["生产车间编码"]].ToString(), i);
                            this.Model.SetItemValueByNumber("FResourcesId", dt.Rows[i][dicExcelCol["生产资源编码"]].ToString(), i);
                            this.Model.SetItemValueByNumber("FEmpID", dt.Rows[i][dicExcelCol["员工编码"]].ToString(), i);
                            //  this.Model.SetValue("FMatrailId", dt.Rows[i][5].ToString(), i);
                            this.Model.SetItemValueByNumber("FMatrailId", dt.Rows[i][5].ToString(), i);
                            this.Model.SetValue("FBatch", dt.Rows[i][9].ToString(), i);
                            this.Model.SetValue("FSalOrderCount", dt.Rows[i][10].ToString(), i);
                            this.Model.SetValue("FCompleteSetCount", dt.Rows[i][12].ToString(), i);
                            this.Model.SetValue("FDeliveryDate", ToDateTimeValue(dt.Rows[i][13].ToString()), i);
                            this.Model.SetValue("FOrderLevel", OrderType(dt.Rows[i][14].ToString()), i);
                            this.Model.SetItemValueByNumber("FMatrailId", dt.Rows[i][dicExcelCol["物料编码"]].ToString(), i);
                            this.Model.SetValue("FBatch", dt.Rows[i][dicExcelCol["批次号"]].ToString(), i);
                            this.Model.SetValue("FSalOrderCount", dt.Rows[i][dicExcelCol["销售订单数量"]].ToString(), i);
                            this.Model.SetValue("FCompleteSetCount", dt.Rows[i][dicExcelCol["总齐套数量"]].ToString(), i);//总齐套数量
                            this.Model.SetValue("FDeliveryDate", dt.Rows[i][dicExcelCol["交货日期"]].ToString(), i);
                            this.Model.SetValue("FOrderLevel", OrderType(dt.Rows[i][dicExcelCol["订单等级"]].ToString()), i);
                            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][dicExcelCol["生产订单明细行号"]].ToString(), i);//生产订单明细行号
                            this.Model.SetValue("FCloseCount", fCloseCount, i);//关闭数量
                            this.Model.SetValue("FQTYLS", fQtyLS, i);//历史遗留

                            for (int j = 0; j <= 60; j++)
                            {
                                this.Model.SetValue("FT" + "" + j + "", dt.Rows[i][15 + j].ToString(), i);
                                this.Model.SetValue("FT" + "" + j + "", dt.Rows[i][16 + j].ToString(), i);
                            }
                            //调用数量值更新
                            this.View.InvokeFieldUpdateService("FT0", i);
@@ -204,8 +235,6 @@
                }

            }


        }
        /// <summary>
        /// 订单状态等级
@@ -251,13 +280,61 @@
        {
            try
            {
                LogService.WriteAsync("测试");
                CloudClient cloudClient = new CloudClient("http://localhost//k3cloud/");
                string sql;
                DateTime date = Convert.ToDateTime(this.Model.GetValue("F_Paez_Date"));
                //获取单据体信息
                Entity entity = this.View.BillBusinessInfo.GetEntity("FEntity");
                //单据体信息转换为列表集合
                DynamicObjectCollection entityDataObjoct = this.View.Model.GetEntityDataObject(entity);
                //LogHelper.Info("导入功能列表行数：" + entityDataObjoct.Count);
                string _result = "";
                //验证是否有生产订单明细行
                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"]));
                    //LogHelper.Info(sql);
                    DataTable dt = new DataTable();
                    dt = DBServiceHelper.ExecuteDataSet(Context, sql).Tables[0];
                    if (dt.Rows.Count == 0)
                        _result += "序号：" + Convert.ToString(current["SEQ"]) + ",组织：" + (current["FORGID"] as DynamicObject)["Number"] + ",生产订单号：" + Convert.ToString(current["FSCOrderNo"]) + ",生产订单明细行号：" + Convert.ToString(current["FSRCBILLENTRYSEQ"]) + "对应的物料" + (current["FMatrailId"] as DynamicObject)["Number"] + "不存在\n";
                }
                if (_result != "")
                {
                    this.View.ShowErrMessage(_result);
                    return;
                }
                //校验生产用料清单是否审核
                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"]));
                    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 != "")
                {
                    this.View.ShowErrMessage(_result);
                    return;
                }
                foreach (DynamicObject current in entityDataObjoct)
                {
                    //未排数量= 总数 - 日计划    超出日计划为负 - 历史数量 + 关闭数量 < 0 表示超出了总计划数量
                    if (Convert.ToInt32(current["FNoScheduled"]) - Convert.ToInt32(current["FQTYLS"]) + Convert.ToInt32(current["FCloseCount"]) < 0)
                        _result += "序号：" + Convert.ToString(current["SEQ"]) + ",日计划总数量超出生产订单数量\n";
                }
                if (_result != "")
                {
                    this.View.ShowErrMessage(_result);
                    return;
                }
                List<string> sqlList = new List<string>();
                foreach (DynamicObject current in entityDataObjoct)
                {
@@ -272,42 +349,21 @@
                    }
                }
                int res = DBServiceHelper.ExecuteBatch(Context, sqlList);
                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"]));
                    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"] + "不存在";

                }
                if (_result != "")
                {
                    this.View.ShowErrMessage(_result);
                    return;
                }
                foreach (DynamicObject current in entityDataObjoct)
                {
                    if (Convert.ToInt32(current["FNoScheduled"]) < 0)
                        _result += "序号：" + Convert.ToString(current["SEQ"]) + ",日计划总数量超出生产订单数量";
                }
                if (_result != "")
                {
                    this.View.ShowErrMessage(_result);
                    return;
                }
                foreach (DynamicObject current in entityDataObjoct)
                {
                    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"]));
                    //查询生产订单数据 
                    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
 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"]));
                    DataTable dt = new DataTable();
                    dt = DBServiceHelper.ExecuteDataSet(Context, sql).Tables[0];
                    if (dt.Rows.Count == 0)
@@ -317,6 +373,11 @@
                    string FLOT = "";//FLOT  批次
                    string FQTY = "";//FQTY 生产订单数量
                    string FBILLNO = "";//FBILLNO 原单号 
                    string FHUnitID = "";//单位
                    string FPlanBeginDate = "";//开始日期
                    string FPlanEndDate = "";//结束日期
                    string FMoFBillType = "";//
                    string FBomNumber = "";//Bom 版本
                    if (dt.Rows.Count > 0)
                    {
                        FID = dt.Rows[0]["FID"].ToString();
@@ -324,6 +385,11 @@
                        FLOT = dt.Rows[0]["FLOT"].ToString();
                        FQTY = dt.Rows[0]["FQTY"].ToString();
                        FBILLNO = dt.Rows[0]["FBILLNO"].ToString();
                        FHUnitID = dt.Rows[0]["FUnitNumber"].ToString();
                        FPlanBeginDate = dt.Rows[0]["FPlanStartDate"].ToString();
                        FPlanEndDate = dt.Rows[0]["FPlanFinishDate"].ToString();
                        FMoFBillType = dt.Rows[0]["FMoFBillType"].ToString();
                        FBomNumber = dt.Rows[0]["FBomNumber"].ToString();
                    }
                    JObject jsonRoot = new JObject();
                    jsonRoot.Add("Creator", "");
@@ -333,21 +399,29 @@
                    jsonRoot.Add("SubSystemId", "");
                    jsonRoot.Add("IsVerifyBaseDataField", "");
                    JObject jsonModel = new JObject();
                    jsonModel.Add("FPreparatDate", date.ToString());//编制日期
                    jsonModel.Add("FSCOrderNo", current["FSCOrderNo"].ToString()); //生产订单号FSCOrderNo
                    jsonModel.Add("FPreparatDate", FPlanBeginDate);//编制日期(改为计划开始日期)
                    jsonModel.Add("FMoFBillType", FMoFBillType);//
                    jsonModel.Add("FSRCBILLENTRYSEQ", Convert.ToString(current["FSRCBILLENTRYSEQ"]));//生产订单明细行号
                    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", current["FDeliveryDate"].ToString());//交货期HOrderCommitDate
                                                                                            //jsonModel.Add("FBatch", current["FBatch"].ToString());//批次
                    jsonModel.Add("FHOrderCommitDate", Convert.ToString(current["FDeliveryDate"]));//交货期HOrderCommitDate
                    jsonModel.Add("FHUnitID", new JObject() { ["Fnumber"] = FHUnitID });//单位
                    jsonModel.Add("FBOMID", new JObject() { ["Fnumber"] = FBomNumber });//BOM版本
                    jsonModel.Add("FHPlanBeginDate", FPlanBeginDate);//计划开始日期
                    jsonModel.Add("FHPlanEndDate", FPlanEndDate);//计划完工日期
                    //jsonModel.Add("FBatch", current["FBatch"].ToString());//批次
                    jsonModel.Add("FBatch", FLOT);//批次
                    jsonModel.Add("FHOrderNeedQty", FQTY);// 订单需求数量
                    jsonModel.Add("FPrdBillNo", FBILLNO); ;//源单编号
                    jsonModel.Add("FPRDMOMAINID", FID); ;//源单主内码
                    jsonModel.Add("FPRDMOENTYID", FENTRYID); ;//源单子内码
                    jsonModel.Add("FOrderQuantity", current["FOrderQuantity"].ToString()); ;//生产订单数量
                    jsonModel.Add("FDayPlanQuantity", current["FDayPlanQuantity"].ToString()); ;//日计划数量
                    jsonModel.Add("FNoScheduled", current["FNoScheduled"].ToString()); ;//未排数量
                    jsonModel.Add("FMoFID", FID); ;//源单主内码
                    jsonModel.Add("FMoFentryId", FENTRYID); ;//源单子内码
                    jsonModel.Add("FOrderQuantity", Convert.ToString(current["FOrderQuantity"])); ;//生产订单数量
                    jsonModel.Add("FDayPlanQuantity", Convert.ToString(current["FDayPlanQuantity"])); ;//日计划数量
                    jsonModel.Add("FNoScheduled", Convert.ToString(current["FNoScheduled"])); ;//未排数量
                    JObject jsonFHMaterID = new JObject();
                    jsonFHMaterID.Add("FNumber", (current["FMatrailId"] as DynamicObject)["Number"].ToString());
                    jsonModel.Add("FHMaterID", jsonFHMaterID);//FHMaterID 物料
@@ -370,28 +444,39 @@
                        jsonFResourcesId.Add("FNumber", (current["FProductWorkShopId"] as DynamicObject)["Number"].ToString());
                        jsonModel.Add("FHWorkShopID", jsonFResourcesId);//FHWorkShopID  生产资源
                    }
                    if (Convert.ToString(current["FEmpID_Id"]) != "0")
                    {
                        JObject jsonFEmpID = new JObject();
                        jsonFEmpID.Add("FSTAFFNUMBER", (current["FEmpID"] as DynamicObject)["FStaffNumber"].ToString());
                        jsonModel.Add("FEmpID", jsonFEmpID);//FHWorkShopID  人员
                    }
                    JArray Entry = new JArray();
                    if (Convert.ToString(current["FDayPlanID"]) != "0")
                    {
                        sql = $"select max(FHMASTERDATE)FHMASTERDATE from SC_WORKBILLSORTBILLSub where FID={Convert.ToString(current["FDayPlanID"])} ";
                        DateTime EndDate = DBServiceHelper.ExecuteScalar<DateTime>(Context, sql, DateTime.Now);
                        sql = $" /*dialect*/ select FEntryID, FHMASTERDATE from SC_WORKBILLSORTBILLSub  where FID={Convert.ToString(current["FDayPlanID"])} ";
                        List<DayPlanModel> dayPlanModel = new List<DayPlanModel>();
                        dayPlanModel = DBServiceHelper.ExecuteDataSet(Context, sql).ToModelList<DayPlanModel>();
                        for (int i = 0; i <= 60; i++)
                        {
                            if (EndDate >= date.AddDays(i))
                            if (Convert.ToString(current["FT" + "" + i + ""]) == "" || Convert.ToString(current["FT" + "" + i + ""]) == "0")
                                continue;
                            JObject jsonFPOOrderEntry = new JObject();
                            jsonFPOOrderEntry.Add("FHMasterDate", date.AddDays(i).ToShortDateString()); ;//主日期
                            jsonFPOOrderEntry.Add("FHQty ", Convert.ToString(current["FT" + "" + i + ""])); ;//日计划数量
                            jsonFPOOrderEntry.Add("FColumn", "FT" + "" + i + ""); ;//对应的列头
                            List<DayPlanModel> _dayPlanModel = dayPlanModel.Where(x => x.FHMASTERDATE == date.AddDays(i)).ToList();
                            if (_dayPlanModel.Count > 0)
                            {
                                jsonFPOOrderEntry.Add("FEntryID", _dayPlanModel.FirstOrDefault().FEntryID); ;//序号
                            }
                            Entry.Add(jsonFPOOrderEntry);
                        }
                        jsonModel.Add("FEntity", Entry);
                        jsonModel.Add("FID", Convert.ToString(current["FDayPlanID"])); ;//源单编号
                        jsonModel.Add("FID", Convert.ToString(current["FDayPlanID"]));
                        jsonRoot.Add("Model", jsonModel);
                        var result = cloudClient.Save("Paez_Sc_WorkBillAutoSortBill", jsonRoot.ToString());
                        JObject saveObj = JObject.Parse(result);
                        string saveIsSuc = saveObj["Result"]["ResponseStatus"]["IsSuccess"].ToString().ToUpper();
                        LogService.WriteAsync(jsonRoot);
                        if (saveIsSuc == "TRUE")
                        {
                            string FDayPlanBillNo = saveObj["Result"]["Number"].ToString();
@@ -400,15 +485,16 @@
                        }
                        else
                        {
                            LogService.WriteAsync(jsonRoot);
                            LogHelper.Error(jsonRoot.ToString());
                            this.View.ShowErrMessage(jsonRoot.ToString());
                            //LogHelper.Error("生产日计划导入 新增异常：" + saveObj.ToString());
                            this.View.ShowMessage(saveObj["Result"]["ResponseStatus"]["Errors"][0]["Message"].ToString());
                        }
                    }
                    else
                    {
                        for (int i = 0; i <= 60; i++)
                        {
                            if (Convert.ToString(current["FT" + "" + i + ""]) == "" || Convert.ToString(current["FT" + "" + i + ""]) == "0")
                                continue;
                            JObject jsonFPOOrderEntry = new JObject();
                            jsonFPOOrderEntry.Add("FHMasterDate", date.AddDays(i).ToShortDateString()); ;//主日期
                            jsonFPOOrderEntry.Add("FHQty ", Convert.ToString(current["FT" + "" + i + ""])); ;//日计划数量
@@ -420,7 +506,6 @@
                        var result = cloudClient.Save("Paez_Sc_WorkBillAutoSortBill", jsonRoot.ToString());
                        JObject saveObj = JObject.Parse(result);
                        string saveIsSuc = saveObj["Result"]["ResponseStatus"]["IsSuccess"].ToString().ToUpper();
                        LogService.WriteAsync(jsonRoot);
                        if (saveIsSuc == "TRUE")
                        {
                            string FDayPlanBillNo = saveObj["Result"]["Number"].ToString();
@@ -431,9 +516,8 @@
                        }
                        else
                        {
                            LogService.WriteAsync(jsonRoot);
                            LogHelper.Error(jsonRoot.ToString());
                            this.View.ShowErrMessage(jsonRoot.ToString());
                            //LogHelper.Error("生产日计划导入 新增异常：" + saveObj.ToString());
                            this.View.ShowMessage(saveObj["Result"]["ResponseStatus"]["Errors"][0]["Message"].ToString());
                        }
                    }
                    this.View.UpdateView();
@@ -441,6 +525,7 @@
            }
            catch (Exception ex)
            {
                //LogHelper.Error(ex.ToString());
                this.View.ShowErrMessage(ex.ToString());
            }

@@ -492,6 +577,210 @@
            }
            return string.Empty;
        }
    }
        public void WriteImportLog()
        {
            try
            {
                CloudClient cloudClient = new CloudClient("http://localhost//k3cloud/");
                string sql;
                DateTime date = Convert.ToDateTime(this.Model.GetValue("F_Paez_Date"));
                //获取单据体信息
                Entity entity = this.View.BillBusinessInfo.GetEntity("FEntity");
                //单据体信息转换为列表集合
                DynamicObjectCollection entityDataObjoct = this.View.Model.GetEntityDataObject(entity);
                foreach (DynamicObject current in entityDataObjoct)
                {
                    if (string.IsNullOrEmpty(Convert.ToString(current["FSCOrderNo"])))
                        continue;
                    //根据生产订单号和物料查询是否存在明细 +生产订单明细行号
                    sql = string.Format(@"
/*dialect*/
SELECT T1.FBILLNO,T2.*,t3.FNUMBER as FUnitNumber FROM T_PRD_MO T1
 LEFT JOIN T_PRD_MOENTRY T2 ON T1.FID = T2.FID
 join T_BD_UNIT t3 on t2.FUNITID=t3.FUNITID 
 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)
                        continue;
                    string FID = "";//FID 源单主内码
                    string FENTRYID = "";//FENTRYID 源单子内码
                    string FLOT = "";//FLOT  批次
                    string FQTY = "";//FQTY 生产订单数量
                    string FBILLNO = "";//FBILLNO 原单号 
                    string FHUnitID = "";//单位
                    string FPlanBeginDate = "";//开始日期
                    string FPlanEndDate = "";//结束日期
                    if (dt.Rows.Count > 0)
                    {
                        FID = dt.Rows[0]["FID"].ToString();
                        FENTRYID = dt.Rows[0]["FENTRYID"].ToString();
                        FLOT = dt.Rows[0]["FLOT"].ToString();
                        FQTY = dt.Rows[0]["FQTY"].ToString();
                        FBILLNO = dt.Rows[0]["FBILLNO"].ToString();
                        FHUnitID = dt.Rows[0]["FUnitNumber"].ToString();
                        FPlanBeginDate = dt.Rows[0]["FPlanStartDate"].ToString();
                        FPlanEndDate = dt.Rows[0]["FPlanFinishDate"].ToString();
                    }
                    JObject jsonRoot = new JObject();
                    jsonRoot.Add("Creator", "");
                    jsonRoot.Add("NeedUpDateFields", new JArray());
                    jsonRoot.Add("NeedReturnFields", new JArray());
                    jsonRoot.Add("IsDeleteEntry", "false");
                    jsonRoot.Add("SubSystemId", "");
                    jsonRoot.Add("IsVerifyBaseDataField", "");
                    JObject jsonModel = new JObject();
                    jsonModel.Add("FPreparatDate", date.ToString());//编制日期
                    jsonModel.Add("FSRCBILLENTRYSEQ", Convert.ToString(current["FSRCBILLENTRYSEQ"]));//生产订单明细行号
                    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("FHUnitID", new JObject() { ["Fnumber"] = FHUnitID });//单位
                    jsonModel.Add("FHPlanBeginDate", FPlanBeginDate);//计划开始日期
                    jsonModel.Add("FHPlanEndDate", FPlanEndDate);//计划完工日期
                    //jsonModel.Add("FBatch", current["FBatch"].ToString());//批次
                    jsonModel.Add("FBatch", FLOT);//批次
                    jsonModel.Add("FHOrderNeedQty", FQTY);// 订单需求数量
                    jsonModel.Add("FPrdBillNo", FBILLNO); ;//源单编号
                    jsonModel.Add("FPRDMOMAINID", FID); ;//源单主内码
                    jsonModel.Add("FPRDMOENTYID", FENTRYID); ;//源单子内码
                    jsonModel.Add("FMoFID", FID); ;//源单主内码
                    jsonModel.Add("FMoFentryId", FENTRYID); ;//源单子内码
                    jsonModel.Add("FOrderQuantity", Convert.ToString(current["FOrderQuantity"])); ;//生产订单数量
                    jsonModel.Add("FDayPlanQuantity", Convert.ToString(current["FDayPlanQuantity"])); ;//日计划数量
                    jsonModel.Add("FNoScheduled", Convert.ToString(current["FNoScheduled"])); ;//未排数量
                    JObject jsonFHMaterID = new JObject();
                    jsonFHMaterID.Add("FNumber", (current["FMatrailId"] as DynamicObject)["Number"].ToString());
                    jsonModel.Add("FHMaterID", jsonFHMaterID);//FHMaterID 物料
                    if (Convert.ToString(current["FORGID_Id"]) != "0")
                    {
                        JObject jsonFHPRDORGID = new JObject();
                        jsonFHPRDORGID.Add("FNumber", (current["FORGID"] as DynamicObject)["Number"].ToString());
                        jsonModel.Add("FHPRDORGID", jsonFHPRDORGID);//FHMaterID 生产组织

                    }
                    if (Convert.ToString(current["FResourcesId_Id"]) != "0")
                    {
                        JObject jsonFProductWorkShopId = new JObject();
                        jsonFProductWorkShopId.Add("FNumber", (current["FResourcesId"] as DynamicObject)["Number"].ToString());
                        jsonModel.Add("FHSourceID", jsonFProductWorkShopId);//FHSourceID  生产车间
                    }
                    if (Convert.ToString(current["FProductWorkShopId_Id"]) != "0")
                    {
                        JObject jsonFResourcesId = new JObject();
                        jsonFResourcesId.Add("FNumber", (current["FProductWorkShopId"] as DynamicObject)["Number"].ToString());
                        jsonModel.Add("FHWorkShopID", jsonFResourcesId);//FHWorkShopID  生产资源
                    }
                    JArray Entry = new JArray();

                    for (int i = 0; i <= 60; i++)
                    {
                        if (Convert.ToString(current["FT" + "" + i + ""]) == "" || Convert.ToString(current["FT" + "" + i + ""]) == "0")
                            continue;
                        JObject jsonFPOOrderEntry = new JObject();
                        jsonFPOOrderEntry.Add("FHMasterDate", date.AddDays(i).ToShortDateString()); ;//主日期
                        jsonFPOOrderEntry.Add("FHQty ", Convert.ToString(current["FT" + "" + i + ""])); ;//日计划数量
                        jsonFPOOrderEntry.Add("FColumn", "FT" + "" + i + ""); ;//对应的列头
                        Entry.Add(jsonFPOOrderEntry);
                    }
                    jsonModel.Add("FEntity", Entry);
                    jsonRoot.Add("Model", jsonModel);
                    var result = cloudClient.Save("paez_Sc_WorkBillAutoSortBill_Log", jsonRoot.ToString());
                    JObject saveObj = JObject.Parse(result);
                    string saveIsSuc = saveObj["Result"]["ResponseStatus"]["IsSuccess"].ToString().ToUpper();
                    if (saveIsSuc != "TRUE")
                    {
                        //LogHelper.Error("生产日计划导入记录日志，新增异常json：" + jsonRoot.ToString());
                    }

                }
            }
            catch (Exception ex)
            {
                //LogHelper.Error("生产日计划导入记录日志跳出:" + ex.Message.ToString());
            }
        }

        /// <summary>
        /// dt去空行
        /// </summary>
        /// <param name="dt"></param>
        public void RemoveEmpty(DataTable dt)
        {
            List<DataRow> removelist = new List<DataRow>();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                bool IsNull = true;
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    if (!string.IsNullOrEmpty(dt.Rows[i][j].ToString().Trim()))
                    {
                        IsNull = false;
                    }
                }
                if (IsNull)
                {
                    removelist.Add(dt.Rows[i]);
                }
            }
            for (int i = 0; i < removelist.Count; i++)
            {
                dt.Rows.Remove(removelist[i]);
            }
        }


        /// <summary>
        /// 检查上传文件模板表头
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="fileTitle"></param>
        /// <returns></returns>
        public string CheckUploadFileTitle(DataTable dt, string[] fileTitle, out Dictionary<string, string> dicCol, int titleRow = 0, string[] arrFileTitle = null)
        {
            dicCol = new Dictionary<string, string>();
            try
            {
                StringBuilder checkTitleInfo = new StringBuilder();
                if (fileTitle != null && fileTitle.Length > 0)
                {
                    foreach (var item in fileTitle)
                    {
                        if (item == "") { continue; }

                        bool isHasCol = false;
                        for (int i = 0; i < dt.Columns.Count; i++)
                        {
                            //string title = Regex.Replace(dt.Rows[titleRow][i].ToString(), @"[/n/r]", "");
                            string title = dt.Rows[titleRow][i].ToString().Replace("\n", ""); //去除换行
                            if (title.Contains(item))
                            {
                                isHasCol = true;
                                if (!dicCol.ContainsKey(item))
                                    dicCol.Add(item, "F" + (i + 1).ToString());

                                //过滤非法字符单引号
                                DataRow[] drs = dt.Select("F" + (i + 1).ToString() + " like'%''%'");
                                if (drs != null && drs.Length > 0)
                                    return "列[" + item + "]中存在不允许字符(半角单引号):',请改成全角单引号或其他";
                            }
                        }
                        if (!isHasCol)
                        {
                            checkTitleInfo.AppendLine("模板中不存在[" + item + "]");
                        }

                    }
                }
                return checkTitleInfo.ToString();
            }
            catch
            {
                return "模板文件错误，请检查.";
            }
        }

    }
}