修改单据重复显示问题，增加工单源单id关联

yl

2024-03-29 9c634fd767aec36ef97c3a814bf7a29c67d20ee1

 VueWebApi/DLL/DAL/ProductModelDAL.cs

@@ -421,7 +421,7 @@
        #endregion

        #region[存货档案查询]
        public static ToMessage InventoryFileSelect(string stu_torgcode,string stu_torgtypecode,string partcode, string partname, string partspec, string stocktypecode, string materialtypecode, string storehousecode, int startNum, int endNum, string prop, string order)
        public static ToMessage InventoryFileSelect(string stu_torgcode, string stu_torgtypecode, string partcode, string partname, string partspec, string stocktypecode, string materialtypecode, string storehousecode, int startNum, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            string search0 = "";
@@ -461,7 +461,7 @@
                }
                if (stocktypecode != "" && stocktypecode != null)
                {
                    search0+= " where code = @stocktypecode";
                    search0 += " where code = @stocktypecode";
                    search += " and   CTE.iparent_id<>'' and CTE.dict_type='CHLX'";
                    dynamicParams.Add("@stocktypecode", stocktypecode);
                }
@@ -516,20 +516,20 @@
                               select distinct materiel_code from TMateriel_Route M
                               left join TOrganization F on M.torg_code=F.org_code 
                               left join TOrganization as L on F.parent_id=L.id 
                               where 1=1 " + search1 + ")  M on A.partcode=M.materiel_code  where  A.is_delete<>'1' " + search+") AS Result";
                               where 1=1 " + search1 + ")  M on A.partcode=M.materiel_code  where  A.is_delete<>'1' " + search + ") AS Result";
                var data0 = DapperHelper.selectdata(sql0, dynamicParams);
                if (data0.Rows.Count > 0)
                {
                    total = Convert.ToInt32(data0.Rows[0]["total"].ToString());
                }
                else 
                else
                {
                    total = 0;
                }

                var sql = @"WITH CTE AS (
                        SELECT  code,name,iparent_id,dict_type
                        FROM T_Dict "+search0+" ";
                        FROM T_Dict " + search0 + " ";
                sql += @"UNION ALL
                        SELECT t.code,t.name,t.iparent_id,t.dict_type
                        FROM T_Dict t
@@ -549,7 +549,7 @@
                               select distinct materiel_code from TMateriel_Route M
                               left join TOrganization F on M.torg_code=F.org_code 
                               left join TOrganization as L on F.parent_id=L.id 
                               where 1=1 " + search1+")  M on A.partcode=M.materiel_code  where  A.is_delete<>'1' "+search+ " ) AS Temp) AS Result WHERE RowNum >= "+ startNum + " AND RowNum <="+ endNum + " ORDER BY "+ prop + " "+order+"";
                               where 1=1 " + search1 + ")  M on A.partcode=M.materiel_code  where  A.is_delete<>'1' " + search + " ) AS Temp) AS Result WHERE RowNum >= " + startNum + " AND RowNum <=" + endNum + " ORDER BY " + prop + " " + order + "";
                var data = DapperHelper.selectdata(sql, dynamicParams);
                mes.code = "200";
                mes.Message = "查询成功!";
@@ -568,7 +568,7 @@
        #endregion

        #region[存货档案新增编辑]
        public static ToMessage AddUpdateInventoryFile(string materialid, string materialcode, string materialname, string materialspec, string uomcode, string warehousecode, string stocktypecode, string minstockqty, string maxstockqty,string is_batchno,string is_fifo,string is_incheck,string is_outcheck, string username, string operType)
        public static ToMessage AddUpdateInventoryFile(string materialid, string materialcode, string materialname, string materialspec, string uomcode, string warehousecode, string stocktypecode, string minstockqty, string maxstockqty, string is_batchno, string is_fifo, string is_incheck, string is_outcheck, string username, string operType)
        {
            var dynamicParams = new DynamicParameters();
            try
@@ -775,7 +775,7 @@
        #endregion

        #region[存货档案关联工艺路线查询]
        public static ToMessage InventoryFileAssociationRoute(string stu_torgcode,string stu_torgtypecode,string partcode)
        public static ToMessage InventoryFileAssociationRoute(string stu_torgcode, string stu_torgtypecode, string partcode)
        {
            string sql = "";
            string search = "";
@@ -815,7 +815,7 @@
                    rout.code = data.Rows[i]["ROUTECODE"].ToString();
                    rout.name = data.Rows[i]["ROUTENAME"].ToString();
                    rout.flag = data.Rows[i]["FLAG"].ToString();
                    rout.wkshopcode= data.Rows[i]["ORG_CODE"].ToString();
                    rout.wkshopcode = data.Rows[i]["ORG_CODE"].ToString();
                    rout.wkshopname = data.Rows[i]["ORG_NAME"].ToString();
                    //根据工艺路线编码获取关联的工序信息
                    sql = @"select A.seq,B.stepcode,B.stepname,B.enable from TFlw_Rtdt A
@@ -843,7 +843,7 @@
        #endregion

        #region[存货档案关联工艺路线提交]
        public static ToMessage SaveInventoryFile(string stu_torgcode,string stu_torgtypecode, string partcode, string defaultroute_code, string username, List<PartRout> json)
        public static ToMessage SaveInventoryFile(string stu_torgcode, string stu_torgtypecode, string partcode, string defaultroute_code, string username, List<PartRout> json)
        {
            var sql = "";
            var dynamicParams = new DynamicParameters();
@@ -876,7 +876,7 @@
                        list.Add(new { str = sql, parm = new { partcode = partcode, stu_torgcode = stu_torgcode } });
                        break;
                }
                if (json != null && json.Count >0)
                if (json != null && json.Count > 0)
                {
                    //物料+组织查询节拍工价表中对应的所有工艺路线,然后根据传过来的做对比，再去删除节拍工价表

@@ -1029,7 +1029,7 @@
                    mes.Message = "获取版本号失败!";
                    mes.data = null;
                }
               

            }
            catch (Exception e)
            {
@@ -1069,7 +1069,7 @@
                            version = version,
                            username = username,
                            CreateDate = DateTime.Now.ToString(),
                            startdate= startdate
                            startdate = startdate
                        }
                    });
                    //写入BOM子表
@@ -1286,7 +1286,7 @@


        #region[工艺路线查询]
        public static ToMessage RouteSearch(string stu_torgcode, string stu_torgtypecode, string WorkShop,string routecode, string routename, string description, string createuser, int startNum, int endNum, string prop, string order)
        public static ToMessage RouteSearch(string stu_torgcode, string stu_torgtypecode, string WorkShop, string routecode, string routename, string description, string createuser, int startNum, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
@@ -1337,7 +1337,12 @@
                }
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select A.id,T.org_code,T.org_name,A.code,A.name,A.description,A.enable,U.username as lm_user,A.lm_date  from TFlw_Rout A
                var sql = @"select A.id,T.org_code,T.org_name,A.code,A.name,A.description,A.enable,U.username as lm_user,A.lm_date,
                            ISNULL(STUFF((SELECT ',' + R.materiel_code
                            FROM TMateriel_Route R
                            WHERE CHARINDEX(',' + R.route_code + ',', ',' + A.code + ',') > 0
                            FOR XML PATH('')), 1, 1, ''),'') AS isrolepart 
                            from TFlw_Rout A
                            left join TUser U on A.lm_user=U.usercode
                            left join TOrganization T on A.torg_code=T.org_code 
                            left join TOrganization as L on T.parent_id=L.id 
@@ -1430,7 +1435,7 @@
                    }
                    //新增工艺路线表
                    sql = @"insert into TFlw_Rout(code,name,description,enable,lm_user,lm_date,torg_code) values(@code,@name,@description,@enable,@lm_user,@lm_date,@torg_code)";
                    list.Add(new { str = sql, parm = new { code = json.code, name = json.name, description = json.description, enable = json.enable, lm_user = username, lm_date = DateTime.Now.ToString(), torg_code=json.wkshopcode } });
                    list.Add(new { str = sql, parm = new { code = json.code, name = json.name, description = json.description, enable = json.enable, lm_user = username, lm_date = DateTime.Now.ToString(), torg_code = json.wkshopcode } });
                    for (int i = 0; i < json.Data.Rows.Count; i++)
                    {
                        string is_firststep = "N"; //是否首道工序
@@ -1445,7 +1450,7 @@
                        }
                        //新增工艺路线关联工序表
                        sql = @"insert TFlw_Rtdt (rout_code,seq,step_code,first_choke,last_choke,lm_user,lm_date,torg_code) values(@rout_code,@seq,@step_code,@first_choke,@last_choke,@lm_user,@lm_date,@torg_code)";
                        list.Add(new { str = sql, parm = new { rout_code = json.code, seq = Convert.ToInt32(json.Data.Rows[i]["SEQ"].ToString()), step_code = json.Data.Rows[i]["STEPCODE"].ToString(), first_choke = is_firststep, last_choke = is_laststep, lm_user = username, lm_date = DateTime.Now.ToString(),torg_code=json.wkshopcode } });
                        list.Add(new { str = sql, parm = new { rout_code = json.code, seq = Convert.ToInt32(json.Data.Rows[i]["SEQ"].ToString()), step_code = json.Data.Rows[i]["STEPCODE"].ToString(), first_choke = is_firststep, last_choke = is_laststep, lm_user = username, lm_date = DateTime.Now.ToString(), torg_code = json.wkshopcode } });
                    }
                    bool aa = DapperHelper.DoTransaction(list);
                    if (aa)
@@ -1564,6 +1569,197 @@
        }
        #endregion

        #region[工艺路线关联产品查询]
        public static ToMessage RputeInventorySearch(string stu_torgcode, string stu_torgtypecode, string partcode, string partname, string partspec, string stocktypecode, string materialtypecode, string storehousecode)
        {
            var dynamicParams = new DynamicParameters();
            string search0 = "";
            string search = "";
            string search1 = "";
            try
            {
                switch (stu_torgtypecode)
                {
                    case "":
                        break;
                    case "D":
                        search1 += "and L.org_code=@stu_torgcode ";
                        dynamicParams.Add("@stu_torgcode", stu_torgcode);
                        break;
                    case "W":
                        search1 += "and F.org_code=@stu_torgcode ";
                        dynamicParams.Add("@stu_torgcode", stu_torgcode);
                        break;
                    default:
                        break;
                }
                if (partcode != "" && partcode != null)
                {
                    search += "and A.partcode like '%'+@partcode+'%' ";
                    dynamicParams.Add("@partcode", partcode);
                }
                if (partname != "" && partname != null)
                {
                    search += "and A.partname like '%'+@partname+'%' ";
                    dynamicParams.Add("@partname", partname);
                }
                if (partspec != "" && partspec != null)
                {
                    search += "and A.partspec like '%'+@partspec+'%' ";
                    dynamicParams.Add("@partspec", partspec);
                }
                if (stocktypecode != "" && stocktypecode != null)
                {
                    search0 += " where code = @stocktypecode";
                    search += " and   CTE.iparent_id<>'' and CTE.dict_type='CHLX'";
                    dynamicParams.Add("@stocktypecode", stocktypecode);
                }
                if (materialtypecode != "" && materialtypecode != null)
                {
                    search += "and C.code=@materialtypecode ";
                    dynamicParams.Add("@materialtypecode", materialtypecode);
                }
                if (storehousecode != "" && storehousecode != null)
                {
                    search += "and A.stck_code=@storehousecode ";
                    dynamicParams.Add("@storehousecode", storehousecode);
                }
                if (search == "")
                {
                    search = "and 1=1 ";
                }
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql0 = @"WITH CTE AS (
                        SELECT  code,name,iparent_id,dict_type
                        FROM T_Dict " + search0 + " ";
                sql0 += @"UNION ALL
                        SELECT t.code,t.name,t.iparent_id,t.dict_type
                        FROM T_Dict t
                        INNER JOIN CTE c ON c.code = t.iparent_id)
                        SELECT COUNT(1) as total FROM (
                        SELECT distinct A.id,A.partcode,A.partname,A.partspec,A.uom_code,B.name as uom_name,D.code as stocktypecode,D.name as stocktypename,D.iparent_id,
                            A.stck_code,T.name as stck_name,A.maxqty,A.minqty,U.username as lm_user,
                            A.lm_date,(case when isnull(M.materiel_code,'')='' then 'N' else 'Y' end) as proute_id,A.is_batchno,A.is_fifo,A.is_incheck,A.is_outcheck
                  FROM CTE
                  right join TMateriel_Info A on CTE.code=A.stocktype_code
                  left join TUom B on A.uom_code=B.code
                        left join T_Dict D on A.stocktype_code=D.code 
                        left join T_Sec_Stck T on A.stck_code=T.code 
                        left join TUser U on A.lm_user=U.usercode
                        left join (
                               select distinct materiel_code from TMateriel_Route M
                               left join TOrganization F on M.torg_code=F.org_code 
                               left join TOrganization as L on F.parent_id=L.id 
                               where 1=1 " + search1 + ")  M on A.partcode=M.materiel_code  where  A.is_delete<>'1' " + search + ") AS Result";
                var data0 = DapperHelper.selectdata(sql0, dynamicParams);
                if (data0.Rows.Count > 0)
                {
                    total = Convert.ToInt32(data0.Rows[0]["total"].ToString());
                }
                else
                {
                    total = 0;
                }

                var sql = @"WITH CTE AS (
                        SELECT  code,name,iparent_id,dict_type
                        FROM T_Dict " + search0 + " ";
                sql += @"UNION ALL
                        SELECT t.code,t.name,t.iparent_id,t.dict_type
                        FROM T_Dict t
                        INNER JOIN CTE c ON c.code = t.iparent_id)
                        SELECT * FROM(SELECT *,ROW_NUMBER() OVER(ORDER BY lm_date desc) AS RowNum
                        FROM(
                        SELECT distinct A.id,A.partcode,A.partname,A.partspec,A.uom_code,B.name as uom_name,D.code as stocktypecode,D.name as stocktypename,D.iparent_id,
                            A.stck_code,T.name as stck_name,A.maxqty,A.minqty,U.username as lm_user,
                            A.lm_date,(case when isnull(M.materiel_code,'')='' then 'N' else 'Y' end) as proute_id,A.is_batchno,A.is_fifo,A.is_incheck,A.is_outcheck
                  FROM CTE
                  right join TMateriel_Info A on CTE.code=A.stocktype_code
                  left join TUom B on A.uom_code=B.code
                        left join T_Dict D on A.stocktype_code=D.code 
                        left join T_Sec_Stck T on A.stck_code=T.code 
                        left join TUser U on A.lm_user=U.usercode
                        left join (
                               select distinct materiel_code from TMateriel_Route M
                               left join TOrganization F on M.torg_code=F.org_code 
                               left join TOrganization as L on F.parent_id=L.id 
                               where 1=1 " + search1 + ")  M on A.partcode=M.materiel_code  where  A.is_delete<>'1' " + search + " ) AS Temp) AS Result";
                var data = DapperHelper.selectdata(sql, dynamicParams);
                mes.code = "200";
                mes.Message = "查询成功!";
                mes.count = total;
                mes.data = data;
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[工艺路线批量指定物料保存]
        public static ToMessage RouteBatchPartSave(string stu_torgcode, string stu_torgtypecode, string username, string routecode, string[] rolebatchpartcode)
        {
            var sql = "";
            var dynamicParams = new DynamicParameters();
            List<object> list = new List<object>();
            try
            {
                list.Clear();
                //清除工艺路线关联的物料数据
                sql = @"delete TMateriel_Route where route_code=@route_code";
                list.Add(new { str = sql, parm = new { route_code = routecode } });
                //循环写入工艺路线物料关联表
                for (int i = 0; i < rolebatchpartcode.Length; i++)
                {
                    sql = @"insert into TMateriel_Route(materiel_code,route_code,lm_user,lm_date,torg_code) 
                            values(@materiel_code,@route_code,@lm_user,@lm_date,@torg_code)";
                    list.Add(new
                    {
                        str = sql,
                        parm = new
                        {
                            materiel_code = rolebatchpartcode[i].ToString(),
                            route_code = routecode,
                            lm_user = username,
                            lm_date = DateTime.Now.ToString(),
                            torg_code = stu_torgcode
                        }
                    });
                }

                bool aa = DapperHelper.DoTransaction(list);
                if (aa)
                {
                    mes.code = "200";
                    mes.count = 0;
                    mes.Message = "操作成功!";
                    mes.data = null;
                }
                else
                {
                    mes.code = "300";
                    mes.count = 0;
                    mes.Message = "操作失败!";
                    mes.data = null;
                }
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion




@@ -1595,7 +1791,7 @@
        #endregion

        #region[工序查询]
        public static ToMessage StepSearch(string stu_torgcode,string stu_torgtypecode,string WorkShop, string stepcode, string stepname, string enable, string steptypecode, string createuser, int startNum, int endNum, string prop, string order)
        public static ToMessage StepSearch(string stu_torgcode, string stu_torgtypecode, string WorkShop, string stepcode, string stepname, string enable, string steptypecode, string createuser, int startNum, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
@@ -1674,7 +1870,7 @@
        #endregion

        #region[工序新增编辑]
        public static ToMessage AddUpdateStep(string stepid,string WorkShop, string stepcode, string stepname, string steptypecode, string enable, string description, string username, string operType)
        public static ToMessage AddUpdateStep(string stepid, string WorkShop, string stepcode, string stepname, string steptypecode, string enable, string description, string username, string operType)
        {
            var dynamicParams = new DynamicParameters();
            List<object> list = new List<object>();
@@ -1763,7 +1959,7 @@
                    }
                    sql_1 = @"update TStep set torg_code=@torg_code, stepname=@stepname,flwtype=@steptypecode,enable=@enable,descr=@description,
                                lm_user=@username,lm_date=@CreateDate where id=@stepid";
                    list.Add(new { str = sql_1, parm = new { torg_code=WorkShop, stepid = stepid, stepname = stepname, steptypecode = steptypecode, enable = enable, description = description, username = username, CreateDate = DateTime.Now.ToString() } });
                    list.Add(new { str = sql_1, parm = new { torg_code = WorkShop, stepid = stepid, stepname = stepname, steptypecode = steptypecode, enable = enable, description = description, username = username, CreateDate = DateTime.Now.ToString() } });
                    bool aa = DapperHelper.DoTransaction(list);
                    if (aa)
                    {
@@ -1888,7 +2084,7 @@
                    rout.type = data.Rows[i]["TYPE"].ToString();
                    rout.flag = data.Rows[i]["FLAG"].ToString();
                    rout.children = new List<StepEqpCn>();
                    if (rout.code == "211"|| rout.code == "228") //外协供方
                    if (rout.code == "211" || rout.code == "228") //外协供方
                    {
                        //根据外协供方标识编码查找外协供方信息(包含已关联标识)
                        sql = @"select A.code,A.name,'W' as type,(case when B.eqp_code is null then 'N' else 'Y' end) flag
@@ -1953,7 +2149,7 @@
        #endregion

        #region [工序定义关联工作站提交]
        public static ToMessage SaveStepAssociationEqp(string WorkShop,string stepcode, string username, List<ObjectData> json)
        public static ToMessage SaveStepAssociationEqp(string WorkShop, string stepcode, string username, List<ObjectData> json)
        {
            var sql = "";
            List<object> list = new List<object>();
@@ -1995,7 +2191,7 @@
                                style = json[i].name,
                                lm_user = username,
                                lm_date = DateTime.Now.ToString(),
                                torg_code=WorkShop
                                torg_code = WorkShop
                            }
                        });
                    }
@@ -2031,7 +2227,7 @@
        #endregion

        #region[工序关联缺陷查询]
        public static ToMessage StepAssociationDefect(string WorkShop,string stepcode)
        public static ToMessage StepAssociationDefect(string WorkShop, string stepcode)
        {
            string sql = "";
            var dynamicParams = new DynamicParameters();
@@ -2074,7 +2270,7 @@
        #endregion

        #region[工序关联缺陷提交]
        public static ToMessage SaveStepAssociationDefect(string WorkShop,string stepcode, string username, List<ObjectData> json)
        public static ToMessage SaveStepAssociationDefect(string WorkShop, string stepcode, string username, List<ObjectData> json)
        {
            var sql = "";
            List<object> list = new List<object>();
@@ -2108,7 +2304,7 @@
                                stepcode = stepcode,
                                lm_user = username,
                                lm_date = DateTime.Now.ToString(),
                                torg_code= WorkShop
                                torg_code = WorkShop
                            }
                        });
                    }
@@ -2176,7 +2372,7 @@
        #endregion

        #region[产品编码查找工艺路线下拉框]
        public static ToMessage PartSelectRpute(string stu_torgcode,string stu_torgtypecode,string partcode)
        public static ToMessage PartSelectRpute(string stu_torgcode, string stu_torgtypecode, string partcode)
        {
            string sql = "";
            string search = "";
@@ -2204,7 +2400,7 @@
                        inner join TFlw_Rout B on A.route_code=B.code 
                        left join TOrganization T on A.torg_code=T.org_code 
                        left join TOrganization as L on T.parent_id=L.id
                        where A.materiel_code=@partcode and B.is_delete<>'1' and A.is_delete<>'1' "+search;
                        where A.materiel_code=@partcode and B.is_delete<>'1' and A.is_delete<>'1' " + search;
                dynamicParams.Add("@partcode", partcode);
                var data = DapperHelper.selectdata(sql, dynamicParams);
                mes.code = "200";
@@ -2280,7 +2476,7 @@
        #endregion

        #region[根据工序线编码查找关联设备集合]
        public static ToMessage StepSelectEqpList(string partcode, string routecode, string stepcode,string steptype, int startNum, int endNum, string prop, string order)
        public static ToMessage StepSelectEqpList(string partcode, string routecode, string stepcode, string steptype, int startNum, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            string search = "", sql = "";
@@ -2373,7 +2569,7 @@
        #endregion

        #region[节拍工价查询]
        public static ToMessage BeatRateSearch(string stu_torgcode,string stu_torgtypecode,string partcode, string routecode, string stepcode, string eqpcode, int startNum, int endNum, string prop, string order)
        public static ToMessage BeatRateSearch(string stu_torgcode, string stu_torgtypecode, string partcode, string routecode, string stepcode, string eqpcode, int startNum, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
@@ -2432,7 +2628,7 @@
                            left join TOrganization F on C.torg_code=F.org_code
                            left join TOrganization L on  F.parent_id=L.id
                            where B.is_delete<>'1' and C.is_delete<>'1' and D.is_delete<>'1' and E.is_delete<>'1' and F.is_delete<>'1' " + search1 + search;
                    sql += @"union all 
                sql += @"union all 
                            select A.materiel_code as partcode,B.partname,B.partspec,A.route_code,C.name as route_name,
                            D.stepcode,D.stepname,A.eqp_code,E.name as eqp_name,''as wksp_code,'' as wksp_name,
                            A.eqp_value,A.stand_value,A.cavity_qty,A.unprice
@@ -2443,8 +2639,8 @@
                            left join TCustomer E on A.eqp_code=E.code
                            left join TOrganization F on C.torg_code=F.org_code
                            left join TOrganization L on  F.parent_id=L.id
                            where B.is_delete<>'1' and C.is_delete<>'1' and D.is_delete<>'1' and E.is_delete<>'1' " + search;     
                            
                            where B.is_delete<>'1' and C.is_delete<>'1' and D.is_delete<>'1' and E.is_delete<>'1' " + search;

                var data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total);
                mes.code = "200";
                mes.Message = "查询成功!";
@@ -2524,7 +2720,7 @@
                        }
                    });
                }
               

                bool aa = DapperHelper.DoTransaction(list);
                if (aa)
                {