采购到货：获取采购订单信息 采购到货：根据采购订单获取物料信息 采购到货：生成标签

yl

2023-01-31 f21eadc526d728d65a46e0b7d172c21307cc4d18

 VueWebApi/DLL/DAL/ProductModelDAL.cs

@@ -27,7 +27,7 @@
            try
            {
                //获取设备类型数据
                sql = @"select code,name from T_Dict where dict_group='CHLX' ";
                sql = @"select code,name from T_Dict where dict_type='CHLX' ";
                var data = DapperHelper.selecttable(sql);
                mes.code = "200";
                mes.Message = "查询成功!";
@@ -72,9 +72,10 @@
                }
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select A.id,A.code,A.name,A.dict_code as stockcode,B.name as stockname,A.lm_user,A.lm_date  
                var sql = @"select A.id,A.code,A.name,A.dict_code as stockcode,B.name as stockname,U.username as lm_user,A.lm_date  
                            from TMateriel_Type A
                            left join T_Dict B on A.dict_code=B.code and B.dict_group='CHLX'
                            left join T_Dict B on A.dict_code=B.code and B.dict_type='CHLX'
                            left join TUser U on A.lm_user=U.usercode
                            where A.is_delete<>'1' " + search;
                var data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total);
                mes.code = "200";
@@ -101,6 +102,17 @@
            {
                if (operType == "Add")
                {
                    var sql0 = @"select *  from TMateriel_Type where code=@materialtypecode";
                    dynamicParams.Add("@materialtypecode", materialtypecode);
                    var data = DapperHelper.selectdata(sql0, dynamicParams);
                    if (data.Rows.Count > 0)
                    {
                        mes.code = "300";
                        mes.count = 0;
                        mes.Message = "操作失败,编码重复!";
                        mes.data = null;
                        return mes;
                    }
                    var sql = @"insert into TMateriel_Type(dict_code,code,name,lm_user,lm_date) 
                            values(@stocktypecode,@materialtypecode,@materialtypename,@username,@CreateDate)";
                    dynamicParams.Add("@stocktypecode", stocktypecode);
@@ -257,6 +269,17 @@
                //循环写入单位表
                for (int i = 0; i < json.Count; i++)
                {
                    var sql0 = @"select *  from TUom where code=@code";
                    dynamicParams.Add("@code", json[i].code);
                    var data = DapperHelper.selectdata(sql0, dynamicParams);
                    if (data.Rows.Count > 0)
                    {
                        mes.code = "300";
                        mes.count = 0;
                        mes.Message = "操作失败,编码重复!";
                        mes.data = null;
                        return mes;
                    }
                    sql = @"insert into TUom(code,name,digdect,r_ule,lm_user,lm_date) values(@code,@name,@digdect,@r_ule,@username,@CreateDate)";
                    list.Add(new
                    {
@@ -265,10 +288,10 @@
                        {
                            code = json[i].code,
                            name = json[i].name,
                            digdect=json[i].digdect,
                            r_ule=json[i].r_ule,
                            username= username,
                            CreateDate= DateTime.Now.ToString()
                            digdect = json[i].digdect,
                            r_ule = json[i].r_ule,
                            username = username,
                            CreateDate = DateTime.Now.ToString()
                        }
                    });
                }
@@ -373,6 +396,30 @@
        }
        #endregion

        #region[单位下拉列表查询]
        public static ToMessage UomSelect()
        {
            string sql = "";
            try
            {
                //获取工艺路线数据
                sql = @"select code,name from TUom where is_delete<>'1' ";
                var data = DapperHelper.selecttable(sql);
                mes.code = "200";
                mes.Message = "查询成功!";
                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 InventoryFileSelect(string partcode, string partname, string partspec, string stocktypecode, string materialtypecode, string storehousecode, int startNum, int endNum, string prop, string order)
        {
@@ -416,14 +463,15 @@
                }
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select A.partcode,A.partname,A.partspec,A.uom_code,B.name as uom_name,D.code as stocktypecode,D.name as stocktypename,
                            C.code as materialtypecode,C.name as materialtypename,A.stck_code,T.name as stck_name,A.maxqty,A.minqty,A.lm_user,
                            A.lm_date,A.proute_id
                var sql = @"select A.id,A.partcode,A.partname,A.partspec,A.uom_code,B.name as uom_name,D.code as stocktypecode,D.name as stocktypename,
                            C.code as materialtypecode,C.name as materialtypename,A.stck_code,T.name as stck_name,A.maxqty,A.minqty,U.username as lm_user,A.default_route,
                            A.lm_date,A.proute_id,A.is_batchno,A.is_fifo,A.is_incheck,A.is_outcheck
                            from TMateriel_Info A
                            left join TUom B on A.uom_code=B.code
                            left join TMateriel_Type C on A.materieltype_code=C.code
                            left join T_Dict D on C.dict_code=D.code and D.dict_type='CHLX'
                            left join T_Dict D on A.stocktype_code=D.code and D.dict_type='CHLX'
                            left join T_Sec_Stck T on A.stck_code=T.code 
                            left join TUser U on A.lm_user=U.usercode
                            where A.is_delete<>'1' " + search;
                var data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total);
                mes.code = "200";
@@ -443,25 +491,41 @@
        #endregion

        #region[存货档案新增编辑]
        public static ToMessage AddUpdateInventoryFile(string materialid, string materialcode, string materialname, string materialspec, string uomcode, string warehousecode, string stocktypecode, string materialtypecode, string minstockqty, string maxstockqty, string username, string operType)
        public static ToMessage AddUpdateInventoryFile(string materialid, string materialcode, string materialname, string materialspec, string uomcode, string warehousecode, string stocktypecode, string materialtypecode, 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
            {
                if (operType == "Add")
                {
                    var sql = @"insert into TMateriel_Info(partcode,partname,partspec,uom_code,materialtypecode,stck_code,proute_id,maxqty,minqty,lm_user,lm_date) 
                            values(@stocktypecode,@materialtypecode,@materialtypename,@username,@CreateDate)";
                    var sql0 = @"select *  from TMateriel_Info where partcode=@materialcode";
                    dynamicParams.Add("@materialcode", materialcode);
                    var data = DapperHelper.selectdata(sql0, dynamicParams);
                    if (data.Rows.Count > 0)
                    {
                        mes.code = "300";
                        mes.count = 0;
                        mes.Message = "操作失败,编码重复!";
                        mes.data = null;
                        return mes;
                    }
                    var sql = @"insert into TMateriel_Info(partcode,partname,partspec,uom_code,stocktype_code,materieltype_code,stck_code,maxqty,minqty,lm_user,lm_date,is_batchno,is_fifo,is_incheck,is_outcheck) 
                            values(@materialcode,@materialname,@materialspec,@uomcode,@stocktypecode,@materialtypecode,@warehousecode,@maxstockqty,@minstockqty,@username,@CreateDate,@is_batchno,@is_fifo,@is_incheck,@is_outcheck)";
                    dynamicParams.Add("@materialcode", materialcode);
                    dynamicParams.Add("@materialname", materialname);
                    dynamicParams.Add("@materialspec", materialspec);
                    dynamicParams.Add("@uomcode", uomcode);
                    dynamicParams.Add("@warehousecode", warehousecode);
                    dynamicParams.Add("@stocktypecode", stocktypecode);
                    dynamicParams.Add("@materialtypecode", materialtypecode);
                    dynamicParams.Add("@minstockqty", minstockqty);
                    dynamicParams.Add("@maxstockqty", maxstockqty);
                    dynamicParams.Add("@username", username);
                    dynamicParams.Add("@CreateDate", DateTime.Now.ToString());
                    dynamicParams.Add("@is_batchno", is_batchno);
                    dynamicParams.Add("@is_fifo", is_fifo);
                    dynamicParams.Add("@is_incheck", is_incheck);
                    dynamicParams.Add("@is_outcheck", is_outcheck);
                    int cont = DapperHelper.SQL(sql, dynamicParams);
                    if (cont > 0)
                    {
@@ -480,18 +544,24 @@
                }
                if (operType == "Update")
                {
                    var sql = @"update TMateriel_Type set partname=@materialname,partspec=@materialspec,uom_code=@uomcode,materialtypecode=@materialtypecode,stck_code=@warehousecode,
                                maxqty=@maxstockqty,minqty=@minstockqty lm_user=@username,lm_date=@CreateDate where id=@materialid";
                    var sql = @"update TMateriel_Info set partname=@materialname,partspec=@materialspec,uom_code=@uomcode,stocktype_code=@stocktypecode,materieltype_code=@materialtypecode,stck_code=@warehousecode,
                                maxqty=@maxstockqty,minqty=@minstockqty,lm_user=@username,lm_date=@CreateDate,is_batchno=@is_batchno,is_fifo=@is_fifo,
                                is_incheck=@is_incheck,is_outcheck=@is_outcheck where id=@materialid";
                    dynamicParams.Add("@materialid", materialid);
                    dynamicParams.Add("@materialname", materialname);
                    dynamicParams.Add("@materialspec", materialspec);
                    dynamicParams.Add("@uomcode", uomcode);
                    dynamicParams.Add("@warehousecode", warehousecode);
                    dynamicParams.Add("@stocktypecode", stocktypecode);
                    dynamicParams.Add("@materialtypecode", materialtypecode);
                    dynamicParams.Add("@minstockqty", minstockqty);
                    dynamicParams.Add("@maxstockqty", maxstockqty);
                    dynamicParams.Add("@username", username);
                    dynamicParams.Add("@CreateDate", DateTime.Now.ToString());
                    dynamicParams.Add("@is_batchno", is_batchno);
                    dynamicParams.Add("@is_fifo", is_fifo);
                    dynamicParams.Add("@is_incheck", is_incheck);
                    dynamicParams.Add("@is_outcheck", is_outcheck);
                    int cont = DapperHelper.SQL(sql, dynamicParams);
                    if (cont > 0)
                    {
@@ -586,7 +656,7 @@
                //有没有被工单引用

                //删除物料
                sql = @"update TMateriel_Info set is_delete='1' where partcode=@materialcode";
                sql = @"delete TMateriel_Info  where partcode=@materialcode";
                list.Add(new { str = sql, parm = new { materialcode = materialcode } });
                bool aa = DapperHelper.DoTransaction(list);
                if (aa)
@@ -629,16 +699,16 @@
                          select distinct route_code  from TMateriel_Route where  materiel_code=@partcode and is_delete<>'1'
                        ) B
                        on A.code=B.route_code 
                        where  A.is_delete='0'";
                        where  A.is_delete='0' and A.enable='Y'";
                dynamicParams.Add("@partcode", partcode);
                var data = DapperHelper.selectdata(sql, dynamicParams);
                for (int i = 0; i < data.Rows.Count; i++)
                {
                    MaterialRout rout = new MaterialRout();
                    rout.code = data.Rows[i]["CODE"].ToString();
                    rout.name = data.Rows[i]["NAME"].ToString();
                    rout.code = data.Rows[i]["ROUTECODE"].ToString();
                    rout.name = data.Rows[i]["ROUTENAME"].ToString();
                    rout.flag = data.Rows[i]["FLAG"].ToString();
                   

                    //根据工艺路线编码获取关联的工序信息
                    sql = @"select A.seq,B.stepcode,B.stepname,B.enable from TFlw_Rtdt A
                            inner join TStep B on A.step_code=B.stepcode
@@ -664,5 +734,1573 @@
        }
        #endregion

        #region[存货档案关联工艺路线提交]
        public static ToMessage SaveInventoryFile(string partcode, string defaultroute_code, string username, List<ObjectData> json)
        {
            var sql = "";
            List<object> list = new List<object>();
            try
            {
                list.Clear();
                if (json == null || json.Count <= 0)
                {
                    //清除物料关联工艺路线表数据
                    sql = @"delete TMateriel_Route where materiel_code=@partcode";
                    list.Add(new { str = sql, parm = new { partcode = partcode } });
                    //标记物料表关联工艺路线标识
                    sql = @"update TMateriel_Info set proute_id='N',default_route='' where partcode=@partcode";
                    list.Add(new { str = sql, parm = new { partcode = partcode } });
                }
                else
                {
                    //清除用户关联角色表数据
                    sql = @"delete TMateriel_Route where materiel_code=@partcode";
                    list.Add(new { str = sql, parm = new { partcode = partcode } });
                    //循环写入用户关联角色表
                    for (int i = 0; i < json.Count; i++)
                    {
                        sql = @"insert into TMateriel_Route(materiel_code,route_code,lm_user,lm_date) values(@materiel_code,@route_code,@lm_user,@lm_date)";
                        list.Add(new
                        {
                            str = sql,
                            parm = new
                            {
                                materiel_code = partcode,
                                route_code = json[i].code,
                                lm_user = username,
                                lm_date = DateTime.Now.ToString()
                            }
                        });
                    }
                    //标记物料表关联工艺路线标识
                    sql = @"update TMateriel_Info set proute_id='Y',default_route=@defaultroute_code where partcode=@partcode";
                    list.Add(new { str = sql, parm = new { partcode = partcode, defaultroute_code = defaultroute_code } });
                }
                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



        #region[物料清单主列表查询]
        public static ToMessage BoIventorySelect(string parentpartcode, string parentpartname, string parentpartspec, string parttype, string status, string version, string createusername, string createopendate, string createclosedate, int startNum, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
            try
            {
                if (parentpartcode != "" && parentpartcode != null)
                {
                    search += "and M.partcode like '%'+@parentpartcode+'%' ";
                    dynamicParams.Add("@parentpartcode", parentpartcode);
                }
                if (parentpartname != "" && parentpartname != null)
                {
                    search += "and M.partname like '%'+@parentpartname+'%' ";
                    dynamicParams.Add("@parentpartname", parentpartname);
                }
                if (parentpartspec != "" && parentpartspec != null)
                {
                    search += "and M.partspec like '%'+@parentpartspec+'%' ";
                    dynamicParams.Add("@parentpartspec", parentpartspec);
                }
                if (parttype != "" && parttype != null)
                {
                    search += "and M.stocktype_code=@parttype ";
                    dynamicParams.Add("@parttype", parttype);
                }
                if (status != "" && status != null)
                {
                    search += "and A.status=@status ";
                    dynamicParams.Add("@status", status);
                }
                if (version != "" && version != null)
                {
                    search += "and A.version like '%'+@version+'%' ";
                    dynamicParams.Add("@version", version);
                }
                if (createusername != "" && createusername != null)
                {
                    search += "and U.username like '%'+@createusername+'%' ";
                    dynamicParams.Add("@createusername", createusername);
                }
                if (createopendate != "" && createopendate != null)
                {
                    search += "and A.lm_date between @createopendate and @createclosedate ";
                    dynamicParams.Add("@createopendate", createopendate + " 00:00:00");
                    dynamicParams.Add("@createclosedate", createclosedate + " 23:59:59");
                }
                if (search == "")
                {
                    search = "and 1=1 ";
                }
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select A.id, M.partcode,M.partname,M.partspec,M.stocktype_code,D.name as stocktype_name,M.uom_code,T.name as uom_name,
                            A.quantity,A.status,A.startdate,A.version,U.username,A.lm_date  
                            from TBom_Main A
                            left join  TMateriel_Info M on A.materiel_code=M.partcode
                            left join  TUom T on M.uom_code=T.code
                            left join  TUser U on A.lm_user=U.usercode
                            left join  T_Dict D on M.stocktype_code=D.code 
                            where A.is_delete<>'1' " + search;
                var data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total);
                mes.code = "200";
                mes.Message = "查询成功!";
                mes.count = total;
                mes.data = data.ToList();
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[物料清单新增时获取最大版本号]
        public static ToMessage MaterielDetailedVsion(string parentpartcode)
        {
            string sql = "";
            var dynamicParams = new DynamicParameters();
            try
            {
                sql = @"select isnull(max(substring(version,charindex('V',version)+1,len(version)-charindex('V',version))),0)+1 as version    
                        from TBom_Main where materiel_code=@parentpartcode";
                dynamicParams.Add("@parentpartcode", parentpartcode);
                var data = DapperHelper.selectdata(sql, dynamicParams);
                if (data.Rows.Count > 0)
                {
                    mes.code = "200";
                    mes.Message = "获取版本号成功!";
                    mes.data = "V" + data.Rows[0]["version"].ToString();
                }
                else
                {
                    mes.code = "300";
                    mes.Message = "获取版本号失败!";
                    mes.data = null;
                }
               
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[物料清单新增/编辑提交]
        public static ToMessage AddUpdateBoIventory(string bomid, string parentpartcode, string parentpartname, string parentpartspec, string status, string uomcode, string quantity, string startdate, string version, string username, string opertype, List<BomSub> objs)
        {
            string sql = "";
            List<object> list = new List<object>();
            var dynamicParams = new DynamicParameters();
            try
            {
                if (opertype == "Add")
                {
                    //获取主表最大ID
                    string sql0 = @"select ISNULL(IDENT_CURRENT('TBom_Main')+1,1) as id";
                    var dt = DapperHelper.selecttable(sql0);
                    //写入BOM主表
                    sql = @"insert into TBom_Main(materiel_code,quantity,status,version,lm_user,lm_date,startdate) 
                            values(@materiel_code,@quantity,@status,@version,@username,@CreateDate,@startdate)";
                    list.Add(new
                    {
                        str = sql,
                        parm = new
                        {
                            materiel_code = parentpartcode,
                            quantity = quantity,
                            status = status,
                            version = version,
                            username = username,
                            CreateDate = DateTime.Now.ToString(),
                            startdate= startdate
                        }
                    });
                    //写入BOM子表
                    for (int i = 0; i < objs.Count; i++)
                    {
                        sql = @"insert into TBom_Deta(m_id,seq,smateriel_code,base_quantity,loss_quantity,total_quantity,pn_type) 
                            values(@m_id,@seq,@smateriel_code,@base_quantity,@loss_quantity,@total_quantity,@pn_type)";
                        list.Add(new
                        {
                            str = sql,
                            parm = new
                            {
                                m_id = dt.Rows[0]["id"].ToString(),
                                seq = objs[i].seq.ToString(),
                                smateriel_code = objs[i].smaterirl_code.ToString(),
                                base_quantity = objs[i].base_quantity.ToString(),
                                loss_quantity = objs[i].loss_quantity.ToString(),
                                total_quantity = objs[i].total_quantity.ToString(),
                                pn_type = objs[i].pn_type.ToString()
                            }
                        });
                    }
                    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;
                    }
                }
                if (opertype == "Update")
                {
                    //修改BOM主表基本用量、启用状态
                    sql = @"update  TBom_Main set quantity=@quantity,status=@status,lm_user=@lm_user,lm_date=@lm_date where materiel_code=@materiel_code and id=@bomid";
                    list.Add(new
                    {
                        str = sql,
                        parm = new
                        {
                            bomid = bomid,
                            materiel_code = parentpartcode,
                            quantity = quantity,
                            status = status,
                            lm_user = username,
                            lm_date = DateTime.Now.ToString()
                        }
                    });
                    //删除BOM子表
                    sql = @"delete  TBom_Deta  where m_id=@bomid";
                    list.Add(new
                    {
                        str = sql,
                        parm = new
                        {
                            bomid = bomid
                        }
                    });
                    //写入BOM子表
                    for (int i = 0; i < objs.Count; i++)
                    {
                        sql = @"insert into TBom_Deta(m_id,seq,smateriel_code,base_quantity,loss_quantity,total_quantity,pn_type) 
                            values(@m_id,@seq,@smateriel_code,@base_quantity,@loss_quantity,@total_quantity,@pn_type)";
                        list.Add(new
                        {
                            str = sql,
                            parm = new
                            {
                                m_id = bomid,
                                seq = objs[i].seq.ToString(),
                                smateriel_code = objs[i].smaterirl_code.ToString(),
                                base_quantity = objs[i].base_quantity.ToString(),
                                loss_quantity = objs[i].loss_quantity.ToString(),
                                total_quantity = objs[i].total_quantity.ToString(),
                                pn_type = objs[i].pn_type.ToString()
                            }
                        });
                    }
                    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

        #region[物料清单编辑显示及预览]
        public static ToMessage BoIventorySelectView(string bomid)
        {
            string sql = "";
            var dynamicParams = new DynamicParameters();
            try
            {
                //判断物料类型是否有关联物料
                sql = @"select materiel_code from TK_Wrk_Man   where materiel_code in (select materiel_code from TBom_Main  where id=@bomid ) and bom_id=@bomid";
                dynamicParams.Add("@bomid", bomid);
                var data0 = DapperHelper.selectdata(sql, dynamicParams);
                if (data0.Rows.Count > 0)
                {
                    mes.code = "300";
                    mes.count = 0;
                    mes.Message = "当前物料清单已被工单关联使用,不允许修改!";
                    mes.data = null;
                    return mes;
                }
                //获取Bom子表数据
                sql = @"select A.seq,B.partcode,B.partname,B.partspec,B.uom_code,T.name as uom_name,
                         A.base_quantity,A.loss_quantity,A.total_quantity,A.pn_type   
                         from TBom_Deta A
                         left join TMateriel_Info B on A.smateriel_code=B.partcode
                         left join  TUom T on  B.uom_code=T.code
                         where A.m_id=@bomid";
                dynamicParams.Add("@bomid", bomid);
                var data = DapperHelper.selectdata(sql, dynamicParams);
                mes.code = "200";
                mes.Message = "查询成功!";
                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 DeleteBoIventory(string bomid)
        {
            var sql = "";
            List<object> list = new List<object>();
            var dynamicParams = new DynamicParameters();
            try
            {
                list.Clear();
                //判断物料类型是否有关联物料
                sql = @"select materiel_code from TK_Wrk_Man   where materiel_code in (select materiel_code from TBom_Main  where id=@bomid ) and bom_id=@bomid";
                dynamicParams.Add("@bomid", bomid);
                var data = DapperHelper.selectdata(sql, dynamicParams);
                if (data.Rows.Count > 0)
                {
                    mes.code = "300";
                    mes.count = 0;
                    mes.Message = "当前物料清单已被工单关联使用,不允许删除!";
                    mes.data = null;
                }
                else
                {
                    //删除物料清单子表
                    sql = @"delete TBom_Deta where m_id=@bomid";
                    list.Add(new { str = sql, parm = new { bomid = bomid } });
                    //删除物料清单主表
                    sql = @"delete TBom_Main where id=@bomid";
                    list.Add(new { str = sql, parm = new { bomid = bomid } });
                    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




        #region[工艺路线查询]
        public static ToMessage RouteSearch(string routecode, string routename, string description, string createuser, int startNum, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
            try
            {
                if (routecode != "" && routecode != null)
                {
                    search += "and A.code like '%'+@routecode+'%' ";
                    dynamicParams.Add("@routecode", routecode);
                }
                if (routename != "" && routename != null)
                {
                    search += "and A.name like '%'+@routename+'%' ";
                    dynamicParams.Add("@routename", routename);
                }
                if (description != "" && description != null)
                {
                    search += "and A.description like '%'+@description+'%' ";
                    dynamicParams.Add("@description", description);
                }
                if (createuser != "" && createuser != null)
                {
                    search += "and U.username like '%'+@createuser+'%' ";
                    dynamicParams.Add("@createuser", createuser);
                }
                if (search == "")
                {
                    search = "and 1=1 ";
                }
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select A.id,A.code,A.name,A.description,A.enable,U.username as lm_user,A.lm_date  from TFlw_Rout A
                            left join TUser U on A.lm_user=U.usercode
                            where A.is_delete<>'1' " + search;
                var data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total);
                mes.code = "200";
                mes.Message = "查询成功!";
                mes.count = total;
                mes.data = data.ToList();
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[工艺路线预览]
        public static ToMessage ViewRoute(string routecode)
        {
            string sql = "";
            var dynamicParams = new DynamicParameters();
            List<RoutEdit> list = new List<RoutEdit>();
            try
            {
                //获取工艺路线信息
                sql = @"select code,name,description,enable 
                        from TFlw_Rout
                        where code=@routecode and is_delete<>'1'";
                dynamicParams.Add("@routecode", routecode);
                var data = DapperHelper.selectdata(sql, dynamicParams);
                for (int i = 0; i < data.Rows.Count; i++)
                {
                    RoutEdit rout = new RoutEdit();
                    rout.code = data.Rows[i]["CODE"].ToString();
                    rout.name = data.Rows[i]["NAME"].ToString();
                    rout.enable = data.Rows[i]["ENABLE"].ToString();
                    rout.description = data.Rows[i]["DESCRIPTION"].ToString();

                    //根据工艺路线编码获取关联的工序信息
                    sql = @"select A.seq,B.stepcode,B.stepname,B.enable from TFlw_Rtdt A
                            inner join TStep B on A.step_code=B.stepcode
                            where A.rout_code=@route_code and B.is_delete<>'1' order by A.seq asc";
                    dynamicParams.Add("@route_code", rout.code);
                    var data0 = DapperHelper.selectdata(sql, dynamicParams);
                    rout.Data = data0;
                    list.Add(rout);

                }
                mes.code = "200";
                mes.Message = "查询成功!";
                mes.data = list;
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[工艺路线新增]
        public static ToMessage AddUpdateRoute(string id, string opertype, string username, RoutEdit json)
        {
            var sql = "";
            var dynamicParams = new DynamicParameters();
            List<object> list = new List<object>();
            try
            {
                if (opertype == "Add")
                {
                    var sql0 = @"select *  from TFlw_Rout where code=@code";
                    dynamicParams.Add("@code", json.code);
                    var data = DapperHelper.selectdata(sql0, dynamicParams);
                    if (data.Rows.Count > 0)
                    {
                        mes.code = "300";
                        mes.count = 0;
                        mes.Message = "操作失败,编码重复!";
                        mes.data = null;
                        return mes;
                    }
                    //新增工艺路线表
                    sql = @"insert into TFlw_Rout(code,name,description,enable,lm_user,lm_date) values(@code,@name,@description,@enable,@lm_user,@lm_date)";
                    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() } });
                    for (int i = 0; i < json.Data.Rows.Count; i++)
                    {
                        string is_firststep = "N"; //是否首道工序
                        string is_laststep = "N"; //是否末道工序
                        if (json.Data.Rows[i]["SEQ"].ToString() == "1")  //是否首道工序
                        {
                            is_firststep = "Y";
                        }
                        else if (Convert.ToInt32(json.Data.Rows[i]["SEQ"].ToString()) == json.Data.Rows.Count) //是否末道工序
                        {
                            is_laststep = "Y";
                        }
                        //新增工艺路线关联工序表
                        sql = @"insert TFlw_Rtdt (rout_code,seq,step_code,first_choke,last_choke,lm_user,lm_date) values(@rout_code,@seq,@step_code,@first_choke,@last_choke,@lm_user,@lm_date)";
                        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() } });
                    }
                    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

        #region[工艺路线删除]
        public static ToMessage DeleteRoute(string routecode)
        {
            var sql = "";
            List<object> list = new List<object>();
            var dynamicParams = new DynamicParameters();
            try
            {
                //判断工艺路线是否被工单引用(被引用则不能删除)
                sql = @"select *   from TK_Wrk_Man where route_code=@routecode";
                dynamicParams.Add("@routecode", routecode);
                var data = DapperHelper.selectdata(sql, dynamicParams);
                if (data.Rows.Count > 0)
                {
                    mes.code = "300";
                    mes.count = 0;
                    mes.Message = "工艺路线已被工单引用，不允许删除!";
                    mes.data = null;
                    return mes;
                }
                else
                {
                    //判断当前工艺路线是否有设置节拍工价(有设置，提示先删除节拍工价设置)
                    sql = @"select *   from TPrteEqp_Stad where route_code=@routecode";
                    dynamicParams.Add("@routecode", routecode);
                    var data0 = DapperHelper.selectdata(sql, dynamicParams);
                    if (data0.Rows.Count > 0)
                    {
                        mes.code = "300";
                        mes.count = 0;
                        mes.Message = "工艺路线已设置节拍工价，请先删除设置!";
                        mes.data = null;
                        return mes;
                    }
                    else
                    {
                        //删除工艺路线关联工序表
                        sql = @"delete TFlw_Rtdt  where rout_code=@routecode";
                        list.Add(new { str = sql, parm = new { routecode = routecode } });
                        //删除工艺路线表
                        sql = @"delete TFlw_Rout  where code=@routecode";
                        list.Add(new { str = sql, parm = new { routecode = routecode } });
                        //删除物料关联工艺路线表
                        sql = @"delete TMateriel_Route  where route_code=@routecode";
                        list.Add(new { str = sql, parm = new { routecode = routecode } });
                    }
                    bool aa = DapperHelper.DoTransaction(list);
                    if (aa)
                    {
                        mes.code = "200";
                        mes.count = 0;
                        mes.Message = "删除成功!";
                        mes.data = null;
                        return mes;
                    }
                    else
                    {
                        mes.code = "300";
                        mes.count = 0;
                        mes.Message = "删除失败!";
                        mes.data = null;
                        return mes;
                    }
                }
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
                return mes;
            }
            return mes;
        }
        #endregion




        #region[工序下拉查询接口]
        public static ToMessage StepSelect()
        {
            string sql = "";
            try
            {
                //获取工序数据
                sql = @"select stepcode,stepname from TStep where is_delete<>'1' and enable='Y'";
                var data = DapperHelper.selecttable(sql);
                mes.code = "200";
                mes.Message = "查询成功!";
                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 StepSearch(string stepcode, string stepname, string enable, string steptypecode, string createuser, int startNum, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
            try
            {
                if (stepcode != "" && stepcode != null)
                {
                    search += "and A.stepcode like '%'+@stepcode+'%' ";
                    dynamicParams.Add("@stepcode", stepcode);
                }
                if (stepname != "" && stepname != null)
                {
                    search += "and A.stepname like '%'+@stepname+'%' ";
                    dynamicParams.Add("@stepname", stepname);
                }
                if (enable != "" && enable != null)
                {
                    search += "and A.enable=@enable ";
                    dynamicParams.Add("@enable", enable);
                }
                if (steptypecode != "" && steptypecode != null)
                {
                    search += "and A.flwtype=@steptypecode ";
                    dynamicParams.Add("@steptypecode", steptypecode);
                }
                if (createuser != "" && createuser != null)
                {
                    search += "and U.username=@createuser ";
                    dynamicParams.Add("@createuser", createuser);
                }
                if (search == "")
                {
                    search = "and 1=1 ";
                }
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select A.id,A.stepcode,A.stepname,A.flwtype,A.enable,A.descr,U.username as lm_user,A.lm_date,A.is_eqp,A.is_defect  from TStep A
                            left join TUser U on A.lm_user=U.usercode
                            where A.is_delete<>'1' " + search;
                var data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total);
                mes.code = "200";
                mes.Message = "查询成功!";
                mes.count = total;
                mes.data = data.ToList();
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[工序新增编辑]
        public static ToMessage AddUpdateStep(string stepid, string stepcode, string stepname, string steptypecode, string enable, string description, string username, string operType)
        {
            var dynamicParams = new DynamicParameters();
            List<object> list = new List<object>();
            var sql_1 = "";
            try
            {
                if (operType == "Add")
                {
                    var sql0 = @"select *  from TStep where stepcode=@stepcode";
                    dynamicParams.Add("@stepcode", stepcode);
                    var data = DapperHelper.selectdata(sql0, dynamicParams);
                    if (data.Rows.Count > 0)
                    {
                        mes.code = "300";
                        mes.count = 0;
                        mes.Message = "操作失败,编码重复!";
                        mes.data = null;
                        return mes;
                    }
                    var sql = @"insert into TStep(stepcode,stepname,flwtype,enable,descr,lm_user,lm_date) 
                            values(@stepcode,@stepname,@steptypecode,@enable,@description,@username,@CreateDate)";
                    dynamicParams.Add("@stepcode", stepcode);
                    dynamicParams.Add("@stepname", stepname);
                    dynamicParams.Add("@steptypecode", steptypecode);
                    dynamicParams.Add("@enable", enable);
                    dynamicParams.Add("@description", description);
                    dynamicParams.Add("@username", username);
                    dynamicParams.Add("@CreateDate", DateTime.Now.ToString());
                    int cont = DapperHelper.SQL(sql, dynamicParams);
                    if (cont > 0)
                    {
                        mes.code = "200";
                        mes.count = 0;
                        mes.Message = "新增操作成功!";
                        mes.data = null;
                    }
                    else
                    {
                        mes.code = "300";
                        mes.count = 0;
                        mes.Message = "新增操作失败!";
                        mes.data = null;
                    }
                }
                if (operType == "Update")
                {
                    list.Clear();
                    //如果当前工序使用状态位N(未启用),需判断当前工序对应工艺路线被引用的工单是否关闭
                    if (enable == "N")
                    {
                        var sql0 = @"select *  from TFlw_Rtdt A
                                 inner join TK_Wrk_Man B on A.rout_code=B.route_code
                                 where A.step_code=@stepcode and B.status<>'CLOSED'";
                        dynamicParams.Add("@stepcode", stepcode);
                        var data = DapperHelper.selectdata(sql0, dynamicParams);
                        if (data.Rows.Count > 0)
                        {
                            mes.code = "300";
                            mes.count = 0;
                            mes.Message = "工序有为执行完的工单,工序状态不能为未启用!";
                            mes.data = null;
                            return mes;
                        }
                    }
                    //当前工序类型与关联工作站中的类型是否匹配,不匹配则清除关联工作站数据，同时清理工序标(关联工作站标识字段为N)
                    var sql_10 = @"select S.flwtype  from TFlw_Rteqp  A
                                   inner join TStep S on A.step_code=S.stepcode
                                   where A.step_code=@stepcode";
                    dynamicParams.Add("@stepcode", stepcode);
                    var data_10 = DapperHelper.selectdata(sql_10, dynamicParams);
                    if (data_10.Rows.Count > 0)
                    {
                        if (data_10.Rows[0]["FLWTYPE"].ToString() != steptypecode)
                        {
                            //删除工序关联工作站表
                            sql_1 = @"delete TFlw_Rteqp  where step_code=@stepcode";
                            list.Add(new { str = sql_1, parm = new { stepcode = stepcode } });
                            //修改工序表关联工作站标识
                            sql_1 = @"update TStep set is_eqp='N' where stepcode=@stepcode";
                            list.Add(new { str = sql_1, parm = new { stepcode = stepcode } });
                        }
                    }


                    sql_1 = @"update TStep set 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 { stepid = stepid, stepname = stepname, steptypecode = steptypecode, enable = enable, description = description, username = username, CreateDate = DateTime.Now.ToString() } });
                    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

        #region[工序删除]
        public static ToMessage DeleteStep(string stepcode)
        {
            var sql = "";
            List<object> list = new List<object>();
            var dynamicParams = new DynamicParameters();
            try
            {
                list.Clear();
                //当前工序是否关联工艺路线
                sql = @"select *  from TFlw_Rtdt 
                        where step_code=@stepcode";
                dynamicParams.Add("@stepcode", stepcode);
                var data = DapperHelper.selectdata(sql, dynamicParams);
                if (data.Rows.Count > 0)
                {
                    mes.code = "300";
                    mes.count = 0;
                    mes.Message = "工序已关联工艺路线,不允许删除!";
                    mes.data = null;
                    return mes;
                }

                //删除设备节拍工价表
                sql = @"delete TPrteEqp_Stad  where step_code=@stepcode";
                list.Add(new { str = sql, parm = new { stepcode = stepcode } });
                //删除工序关联工作站表
                sql = @"delete TFlw_Rteqp   where step_code=@stepcode";
                list.Add(new { str = sql, parm = new { stepcode = stepcode } });
                //删除缺陷关联工序表
                sql = @"delete TDefect_Step  where step_code=@stepcode";
                list.Add(new { str = sql, parm = new { stepcode = stepcode } });
                //删除工序
                sql = @"update TStep set is_delete='1' where stepcode=@stepcode";
                list.Add(new { str = sql, parm = new { stepcode = stepcode } });
                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

        #region[工序定义关联工作站查询]
        public static ToMessage StepAssociationEqp(string stepcode)
        {
            string sql = "";
            var dynamicParams = new DynamicParameters();
            List<StepEqp> list = new List<StepEqp>();
            try
            {
                //获取工作站集合(车间、外协供应商,包含已绑定工作站标识)
                sql = @"select distinct T.org_code as wksp_code,T.org_name as wksp_name,'E' as type,(case when B.eqp_code is null then 'N' else 'Y' end) flag from TOrganization T
                        left join(
                        select distinct A.eqp_code,B.wksp_code  from TFlw_Rteqp A 
                        inner join TEqpInfo B on A.eqp_code=B.code 
                        where A.step_code=@stepcode and A.is_delete<>'1' and B.is_delete<>'1'
                        ) B on T.org_code=B.wksp_code where T.description='W' and is_delete<>'1'
                        UNION ALL
                        select distinct T.btype as wksp_code,(case T.btype  when 'WX' then '外协供方' end ) as wksp_name,'W' as type,(case when B.btype is null then 'N' else 'Y' end) flag 
                        from TCustomer T
                        left join(
                        select distinct A.eqp_code,B.btype  from TFlw_Rteqp A 
                        inner join TCustomer B on A.eqp_code=B.code 
                        where A.step_code=@stepcode and A.is_delete<>'1' and B.is_delete<>'1'
                        ) B on T.btype=B.btype where T.btype='WX' and T.is_delete<>'1'";
                dynamicParams.Add("@stepcode", stepcode);
                var data = DapperHelper.selectdata(sql, dynamicParams);
                for (int i = 0; i < data.Rows.Count; i++)
                {
                    StepEqp rout = new StepEqp();
                    rout.code = data.Rows[i]["WKSP_CODE"].ToString();
                    rout.name = data.Rows[i]["WKSP_NAME"].ToString();
                    rout.type = data.Rows[i]["TYPE"].ToString();
                    rout.flag = data.Rows[i]["FLAG"].ToString();
                    rout.children = new List<StepEqpCn>();
                    if (rout.code == "WX") //外协供方
                    {
                        //根据外协供方标识编码查找外协供方信息(包含已关联标识)
                        sql = @"select A.code,A.name,'W' as type,(case when B.eqp_code is null then 'N' else 'Y' end) flag
                                from TCustomer A
                                left join(
                                select distinct A.eqp_code  from TFlw_Rteqp A 
                                inner join TCustomer B on A.eqp_code=B.code 
                                where B.btype=@wxcode and A.is_delete<>'1' and B.is_delete<>'1'
                                ) B on A.code=B.eqp_code where A.btype=@wxcode and A.is_delete<>'1'";
                        dynamicParams.Add("@wxcode", rout.code);
                        var data0 = DapperHelper.selectdata(sql, dynamicParams);
                        for (int k = 0; k < data0.Rows.Count; k++)
                        {
                            StepEqpCn cn = new StepEqpCn();
                            cn.code = data0.Rows[k]["CODE"].ToString();//外协供方编码
                            cn.name = data0.Rows[k]["NAME"].ToString();//外协供方名称
                            cn.type = data0.Rows[k]["TYPE"].ToString();//工作站类型(E:设备 W:外协供方)
                            cn.flag = data0.Rows[k]["FLAG"].ToString();//关联标识
                            rout.children.Add(cn);
                        }
                        list.Add(rout);
                    }
                    else
                    {
                        //根据车间编码查找设备(包含已关联标识)
                        sql = @"select A.code,A.name,'E' as type,(case when B.eqp_code is null then 'N' else 'Y' end) flag
                              from TEqpInfo A
                              left join(
                              select distinct A.eqp_code  from TFlw_Rteqp A 
                              inner join TEqpInfo B on A.eqp_code=B.code 
                              where A.step_code=@stepcode and A.is_delete<>'1' and B.is_delete<>'1'
                              ) B on A.code=B.eqp_code where A.wksp_code=@wkspcode and A.is_delete<>'1'";
                        dynamicParams.Add("@stepcode", stepcode);
                        dynamicParams.Add("@wkspcode", data.Rows[i]["WKSP_CODE"].ToString());
                        var data0 = DapperHelper.selectdata(sql, dynamicParams);
                        for (int j = 0; j < data0.Rows.Count; j++)
                        {
                            StepEqpCn cn = new StepEqpCn();
                            cn.code = data0.Rows[j]["CODE"].ToString();//设备编码
                            cn.name = data0.Rows[j]["NAME"].ToString();//设备名称
                            cn.type = data0.Rows[j]["TYPE"].ToString();//工作站类型(E:设备 W:外协供方)
                            cn.flag = data0.Rows[j]["FLAG"].ToString();//关联标识
                            rout.children.Add(cn);
                        }
                        list.Add(rout);
                    }
                }
                mes.code = "200";
                mes.Message = "查询成功!";
                mes.data = list;
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region [工序定义关联工作站提交]
        public static ToMessage SaveStepAssociationEqp(string stepcode, string username, List<ObjectData> json)
        {
            var sql = "";
            List<object> list = new List<object>();
            try
            {
                list.Clear();
                if (json == null || json.Count <= 0)
                {
                    //清除工序关联工作站表数据
                    sql = @"delete TFlw_Rteqp where step_code=@stepcode";
                    list.Add(new { str = sql, parm = new { stepcode = stepcode } });
                    //标记工序表关联工作站标识
                    sql = @"update TStep set is_eqp='N' where stepcode=@stepcode";
                    list.Add(new { str = sql, parm = new { stepcode = stepcode } });
                }
                else
                {
                    //清除工序关联工作站表数据
                    sql = @"delete TFlw_Rteqp where step_code=@stepcode";
                    list.Add(new { str = sql, parm = new { stepcode = stepcode } });
                    //循环写入用户关联角色表
                    for (int i = 0; i < json.Count; i++)
                    {
                        sql = @"insert into TFlw_Rteqp(eqp_code,step_code,style,lm_user,lm_date) values(@eqp_code,@stepcode,@style,@lm_user,@lm_date)";
                        list.Add(new
                        {
                            str = sql,
                            parm = new
                            {
                                eqp_code = json[i].code,
                                stepcode = stepcode,
                                style = json[i].name,
                                lm_user = username,
                                lm_date = DateTime.Now.ToString()
                            }
                        });
                    }
                    //标记物料表关联工艺路线标识
                    sql = @"update TStep set is_eqp='Y' where stepcode=@stepcode";
                    list.Add(new { str = sql, parm = new { stepcode = stepcode } });
                }
                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

        #region[工序关联缺陷查询]
        public static ToMessage StepAssociationDefect(string stepcode)
        {
            string sql = "";
            var dynamicParams = new DynamicParameters();
            List<StepDefect> list = new List<StepDefect>();
            try
            {
                //获取工序关联缺陷数据(包含关联标识)
                sql = @"select A.code,A.name,(case when B.defect_code is null then 'N' else 'Y' end) flag from TDefect A
                        left join(
                        select distinct defect_code  from TDefect_Step where step_code=@stepcode and is_delete<>'1'
                        ) B on A.code=B.defect_code";
                dynamicParams.Add("@stepcode", stepcode);
                var data = DapperHelper.selectdata(sql, dynamicParams);
                for (int i = 0; i < data.Rows.Count; i++)
                {
                    StepDefect dft = new StepDefect();
                    dft.code = data.Rows[i]["CODE"].ToString();
                    dft.name = data.Rows[i]["NAME"].ToString();
                    dft.flag = data.Rows[i]["FLAG"].ToString();
                    list.Add(dft);
                }
                mes.code = "200";
                mes.Message = "查询成功!";
                mes.data = list;
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[工序关联缺陷提交]
        public static ToMessage SaveStepAssociationDefect(string stepcode, string username, List<ObjectData> json)
        {
            var sql = "";
            List<object> list = new List<object>();
            try
            {
                list.Clear();
                if (json == null || json.Count <= 0)
                {
                    //清除工序关联缺陷表数据
                    sql = @"delete TDefect_Step where step_code=@stepcode";
                    list.Add(new { str = sql, parm = new { stepcode = stepcode } });
                    //标记工序表关联工作站标识
                    sql = @"update TStep set is_defect='N' where stepcode=@stepcode";
                    list.Add(new { str = sql, parm = new { stepcode = stepcode } });
                }
                else
                {
                    //清除工序关联缺陷表数据
                    sql = @"delete TDefect_Step where step_code=@stepcode";
                    list.Add(new { str = sql, parm = new { stepcode = stepcode } });
                    //循环写入用户关联角色表
                    for (int i = 0; i < json.Count; i++)
                    {
                        sql = @"insert into TDefect_Step(defect_code,step_code,lm_user,lm_date) values(@defect_code,@stepcode,@lm_user,@lm_date)";
                        list.Add(new
                        {
                            str = sql,
                            parm = new
                            {
                                defect_code = json[i].code,
                                stepcode = stepcode,
                                lm_user = username,
                                lm_date = DateTime.Now.ToString()
                            }
                        });
                    }
                    //标记物料表关联工艺路线标识
                    sql = @"update TStep set is_defect='Y' where stepcode=@stepcode";
                    list.Add(new { str = sql, parm = new { stepcode = stepcode } });
                }
                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



        #region[产品信息下拉框查询]
        public static ToMessage PartSelect()
        {
            string sql = "";
            try
            {
                //获取物料数据
                sql = @"select M.partcode,M.partname,M.partspec,M.uom_code,T.name as uom_name,
                        M.stocktype_code,D.name as stocktype_name,M.stck_code,S.name as stck_name
                        from TMateriel_Info M
                        left join  TUom T on M.uom_code=T.code
                        left join  T_Dict D on M.stocktype_code=D.code
                        left join  T_Sec_Stck S on M.stck_code=S.code
                        where M.is_delete<>'1' ";
                var data = DapperHelper.selecttable(sql);
                mes.code = "200";
                mes.Message = "查询成功!";
                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 PartSelectRpute(string partcode)
        {
            string sql = "";
            var dynamicParams = new DynamicParameters();
            try
            {
                //通过产品编码查找关联的工艺路线信息
                sql = @"select A.route_code,B.name as route_name   
                        from TMateriel_Route A
                        inner join TFlw_Rout B on A.route_code=B.code 
                        where A.materiel_code=@partcode and B.is_delete<>'1' and A.is_delete<>'1'";
                dynamicParams.Add("@partcode", partcode);
                var data = DapperHelper.selectdata(sql, dynamicParams);
                mes.code = "200";
                mes.Message = "查询成功!";
                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 RouteSelectStep(string routecode)
        {
            string sql = "";
            var dynamicParams = new DynamicParameters();
            try
            {
                //通过产品编码查找关联的工艺路线信息
                sql = @"select B.stepcode as code,B.stepname as name   
                        from TFlw_Rtdt A
                        inner join TStep B on A.step_code=B.stepcode
                        where A.rout_code=@routecode and B.is_delete<>'1' and A.is_delete<>'1'";
                dynamicParams.Add("@routecode", routecode);
                var data = DapperHelper.select<ObjectData>(sql, dynamicParams);
                mes.code = "200";
                mes.Message = "查询成功!";
                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 StepSelectEqp(string stepcode)
        {
            string sql = "";
            var dynamicParams = new DynamicParameters();
            try
            {
                //通过产品编码查找关联的工艺路线信息
                sql = @"select B.code,B.name  from TFlw_Rteqp A 
                        left join TEqpInfo B on A.eqp_code=B.code
                        where A.step_code=@stepcode and A.style='E' and A.is_delete<>'1' and B.is_delete<>'1'";
                dynamicParams.Add("@stepcode", stepcode);
                var data = DapperHelper.select<ObjectData>(sql, dynamicParams);
                mes.code = "200";
                mes.Message = "查询成功!";
                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 StepSelectEqpList(string partcode, string routecode, string stepcode, int startNum, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
            try
            {
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select B.code as eqpcode,B.name as eqpname,B.wksp_code,T.org_name as wksp_name,A.eqp_value,A.stand_value,A.cavity_qty,A.unprice  
                            from (
                            select materiel_code, eqp_code,eqp_value,stand_value,cavity_qty,unprice   from TPrteEqp_Stad
                            where materiel_code=@partcode  and route_code=@routecode and step_code=@stepcode 
                            union all
                            select @partcode as materiel_code,eqp_code,'0' as eqp_value,'0' as stand_value,'0' as cavity_qty,'0' as unprice
                            from TFlw_Rteqp where step_code=@stepcode and style='E' and @partcode+eqp_code+step_code not in(select materiel_code+eqp_code+step_code  from TPrteEqp_Stad)
                            ) A
                            left join TEqpInfo B on A.eqp_code=B.code 
                            left join TOrganization T on B.wksp_code=T.org_code 
                            left join TMateriel_Info C on A.materiel_code=C.partcode
                            where B.is_delete<>'1' and  T.is_delete<>'1' and C.is_delete<>'1' ";
                dynamicParams.Add("@partcode", partcode);
                dynamicParams.Add("@routecode", routecode);
                dynamicParams.Add("@stepcode", stepcode);
                var data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total);
                mes.code = "200";
                mes.Message = "查询成功!";
                mes.count = total;
                mes.data = data.ToList();
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[节拍工价查询]
        public static ToMessage BeatRateSearch(string partcode, string routecode, string stepcode, string eqpcode, int startNum, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
            try
            {
                if (partcode != "" && partcode != null)
                {
                    search += "and A.materiel_code=@partcode ";
                    dynamicParams.Add("@partcode", partcode);
                }
                if (routecode != "" && routecode != null)
                {
                    search += "and A.route_code=@routecode ";
                    dynamicParams.Add("@routecode", routecode);
                }
                if (stepcode != "" && stepcode != null)
                {
                    search += "and A.step_code=@stepcode ";
                    dynamicParams.Add("@stepcode", stepcode);
                }
                if (eqpcode != "" && eqpcode != null)
                {
                    search += "and A.eqp_code=@eqpcode";
                    dynamicParams.Add("@eqpcode", eqpcode);
                }
                if (search == "")
                {
                    search = "and 1=1 ";
                }
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"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,E.wksp_code,F.org_name as wksp_name,
                            A.eqp_value,A.stand_value,A.cavity_qty,A.unprice
                            from TPrteEqp_Stad A
                            left join TMateriel_Info B on A.materiel_code=B.partcode
                            left join TFlw_Rout C on A.route_code=C.code
                            left join TStep D on A.step_code=D.stepcode
                            left join TEqpInfo E on A.eqp_code=E.code
                            left join TOrganization F on E.wksp_code=F.org_code
                            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' " + search;
                var data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total);
                mes.code = "200";
                mes.Message = "查询成功!";
                mes.count = total;
                mes.data = data.ToList();
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[节拍工价提交]
        public static ToMessage SaveBeatRate(string partcode, string routecode, string stepcode, string eqpcode, string wkshopcode, string eqp_value, string stand_value, string cavity_qty, string unprice, string username)
        {
            var sql = "";
            List<object> list = new List<object>();
            var dynamicParams = new DynamicParameters();
            try
            {
                list.Clear();
                //当前工序是否关联工艺路线
                sql = @"select *  from TPrteEqp_Stad
                        where materiel_code=@partcode and route_code=@routecode and step_code=@stepcode and eqp_code=@eqpcode";
                dynamicParams.Add("@partcode", partcode);
                dynamicParams.Add("@routecode", routecode);
                dynamicParams.Add("@stepcode", stepcode);
                dynamicParams.Add("@eqpcode", eqpcode);
                var data = DapperHelper.selectdata(sql, dynamicParams);
                if (data.Rows.Count > 0)  //被设置关联过就修改
                {
                    sql = @"update TPrteEqp_Stad set stand_value=@stand_value,eqp_value=@eqp_value,cavity_qty=@cavity_qty,unprice=@unprice,lm_user=@username,lm_date=@userdate
                            where materiel_code=@partcode and route_code=@routecode and step_code=@stepcode and eqp_code=@eqpcode ";
                    list.Add(new
                    {
                        str = sql,
                        parm = new
                        {
                            partcode = partcode,
                            routecode = routecode,
                            stepcode = stepcode,
                            eqpcode = eqpcode,
                            stand_value = stand_value,
                            eqp_value = eqp_value,
                            cavity_qty = cavity_qty,
                            unprice = unprice,
                            username = username,
                            userdate = DateTime.Now.ToString()
                        }
                    });
                }
                else
                {
                    sql = @"insert into TPrteEqp_Stad(materiel_code,eqp_code,stand_value,route_code,unprice,eqp_value,cavity_qty,wkspcode,lm_user,lm_date,step_code) 
                    values(@partcode,@eqpcode,@stand_value,@routecode,@unprice,@eqp_value,@cavity_qty,@wkspcode,@username,@userdate,@stepcode)";
                    list.Add(new
                    {
                        str = sql,
                        parm = new
                        {
                            partcode = partcode,
                            eqpcode = eqpcode,
                            stand_value = stand_value,
                            routecode = routecode,
                            unprice = unprice,
                            eqp_value = eqp_value,
                            cavity_qty = cavity_qty,
                            wkspcode = wkshopcode,
                            username = username,
                            userdate = DateTime.Now.ToString(),
                            stepcode = stepcode
                        }
                    });
                }
                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

        #region[节拍工价删除]
        public static ToMessage SaveBeatRate(string partcode, string routecode, string stepcode, string eqpcode)
        {
            var sql = "";
            List<object> list = new List<object>();
            var dynamicParams = new DynamicParameters();
            try
            {

                //删除节拍工价表
                sql = @"delete TPrteEqp_Stad  where materiel_code=@partcode and route_code=@routecode and step_code=@stepcode and eqp_code=@eqpcode";
                list.Add(new
                {
                    str = sql,
                    parm = new
                    {
                        partcode = partcode,
                        routecode = routecode,
                        stepcode = stepcode,
                        eqpcode = eqpcode
                    }
                });

                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
    }
}