1.工单派发选择工艺路线或选择生产车间时判断绑定条件接口开发 2.组织架构删除update改为delete 3.工艺路线新增else if改为if 4...

yl

2023-03-04 4210fdce014886374193400e485edce170c05f78

 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
                    {
@@ -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,
                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
                            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";
@@ -450,13 +498,25 @@
            {
                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) 
                            values(@materialcode,@materialname,@materialspec,@uomcode,@stocktypecode,@materialtypecode,@warehousecode,@maxstockqty,@minstockqty,@username,@CreateDate)";
                    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);
@@ -480,13 +540,14 @@
                }
                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 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);
@@ -586,7 +647,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,14 +690,14 @@
                          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();

                    //根据工艺路线编码获取关联的工序信息
@@ -665,7 +726,7 @@
        #endregion

        #region[存货档案关联工艺路线提交]
        public static ToMessage SaveInventoryFile(string partcode, string username, List<ObjectData> json)
        public static ToMessage SaveInventoryFile(string partcode, string defaultroute_code, string username, List<ObjectData> json)
        {
            var sql = "";
            List<object> list = new List<object>();
@@ -678,7 +739,7 @@
                    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' where 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
@@ -703,8 +764,8 @@
                        });
                    }
                    //标记物料表关联工艺路线标识
                    sql = @"update TMateriel_Info set proute_id='Y' where partcode=@partcode";
                    list.Add(new { str = sql, parm = new { partcode = partcode } });
                    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)
@@ -735,6 +796,364 @@



        #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)
        {
@@ -744,22 +1163,22 @@
            {
                if (routecode != "" && routecode != null)
                {
                    search += "and code like '%'+@partcode+'%' ";
                    search += "and A.code like '%'+@routecode+'%' ";
                    dynamicParams.Add("@routecode", routecode);
                }
                if (routename != "" && routename != null)
                {
                    search += "and name like '%'+@routename+'%' ";
                    search += "and A.name like '%'+@routename+'%' ";
                    dynamicParams.Add("@routename", routename);
                }
                if (description != "" && description != null)
                {
                    search += "and description=@description ";
                    search += "and A.description like '%'+@description+'%' ";
                    dynamicParams.Add("@description", description);
                }
                if (createuser != "" && createuser != null)
                {
                    search += "and lm_user=@createuser ";
                    search += "and U.username like '%'+@createuser+'%' ";
                    dynamicParams.Add("@createuser", createuser);
                }
                if (search == "")
@@ -768,9 +1187,9 @@
                }
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select code,name,description,enable,lm_user,lm_date  
                            from TFlw_Rout
                            where is_delete<>'1' " + search;
                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 = "查询成功!";
@@ -845,13 +1264,24 @@
            {
                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() } });
                    string is_firststep = "N"; //是否首道工序
                    string is_laststep = "N"; //是否末道工序
                    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";
@@ -861,7 +1291,7 @@
                            is_laststep = "Y";
                        }
                        //新增工艺路线关联工序表
                        sql = @"insert TFlw_Rtdt (rout_code,seq,step_code,first_choke,last_choke,lm_user,lm_date) values()";
                        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);
@@ -900,6 +1330,18 @@
            var dynamicParams = new DynamicParameters();
            try
            {
                //判断工艺路路线是否被存货绑定
                sql = @"select *   from TMateriel_Route where route_code=@routecode";
                dynamicParams.Add("@routecode", routecode);
                var data_0 = DapperHelper.selectdata(sql, dynamicParams);
                if (data_0.Rows.Count > 0)
                {
                    mes.code = "300";
                    mes.count = 0;
                    mes.Message = "存货档案已关联工艺路线，不允许删除!";
                    mes.data = null;
                    return mes;
                }
                //判断工艺路线是否被工单引用(被引用则不能删除)
                sql = @"select *   from TK_Wrk_Man where route_code=@routecode";
                dynamicParams.Add("@routecode", routecode);
@@ -910,8 +1352,9 @@
                    mes.count = 0;
                    mes.Message = "工艺路线已被工单引用，不允许删除!";
                    mes.data = null;
                    return mes;
                }
                else 
                else
                {
                    //判断当前工艺路线是否有设置节拍工价(有设置，提示先删除节拍工价设置)
                    sql = @"select *   from TPrteEqp_Stad where route_code=@routecode";
@@ -923,8 +1366,9 @@
                        mes.count = 0;
                        mes.Message = "工艺路线已设置节拍工价，请先删除设置!";
                        mes.data = null;
                        return mes;
                    }
                    else 
                    else
                    {
                        //删除工艺路线关联工序表
                        sql = @"delete TFlw_Rtdt  where rout_code=@routecode";
@@ -936,22 +1380,52 @@
                        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;
                    }
                }
                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;
            }
            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)
            {
@@ -964,9 +1438,6 @@
        }
        #endregion




        #region[工序查询]
        public static ToMessage StepSearch(string stepcode, string stepname, string enable, string steptypecode, string createuser, int startNum, int endNum, string prop, string order)
        {
@@ -976,27 +1447,27 @@
            {
                if (stepcode != "" && stepcode != null)
                {
                    search += "and stepcode like '%'+@stepcode+'%' ";
                    search += "and A.stepcode like '%'+@stepcode+'%' ";
                    dynamicParams.Add("@stepcode", stepcode);
                }
                if (stepname != "" && stepname != null)
                {
                    search += "and stepname like '%'+@stepname+'%' ";
                    search += "and A.stepname like '%'+@stepname+'%' ";
                    dynamicParams.Add("@stepname", stepname);
                }
                if (enable != "" && enable != null)
                {
                    search += "and enable=@enable ";
                    search += "and A.enable=@enable ";
                    dynamicParams.Add("@enable", enable);
                }
                if (steptypecode != "" && steptypecode != null)
                {
                    search += "and flwtype=@steptypecode ";
                    search += "and A.flwtype=@steptypecode ";
                    dynamicParams.Add("@steptypecode", steptypecode);
                }
                if (createuser != "" && createuser != null)
                {
                    search += "and lm_user=@createuser ";
                    search += "and U.username=@createuser ";
                    dynamicParams.Add("@createuser", createuser);
                }
                if (search == "")
@@ -1005,9 +1476,9 @@
                }
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select stepcode,stepname,flwtype,enable,descr,lm_user,lm_date,is_eqp,is_defect  
                            from TStep
                            where is_delete<>'1' " + search;
                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 = "查询成功!";
@@ -1029,10 +1500,23 @@
        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);
@@ -1060,6 +1544,7 @@
                }
                if (operType == "Update")
                {
                    list.Clear();
                    //如果当前工序使用状态位N(未启用),需判断当前工序对应工艺路线被引用的工单是否关闭
                    if (enable == "N")
                    {
@@ -1077,18 +1562,31 @@
                            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 } });
                        }
                    }

                    var sql = @"update TStep set stepname=@stepname,flwtype=@steptypecode,enable=@enable,descr=@description,

                    sql_1 = @"update TStep set stepname=@stepname,flwtype=@steptypecode,enable=@enable,descr=@description,
                                lm_user=@username,lm_date=@CreateDate where id=@stepid";
                    dynamicParams.Add("@stepid", stepid);
                    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)
                    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;
@@ -1139,16 +1637,16 @@
                }

                //删除设备节拍工价表
                sql = @"delete TPrteEqp_Stad  where stepcode=@stepcode";
                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 = @"update TDefect_Step  where step_code=@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 partcode=@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)
@@ -1199,7 +1697,7 @@
                        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.code=B.eqp_code where T.btype='WX' and T.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++)
@@ -1241,14 +1739,15 @@
                              left join(
                              select distinct A.eqp_code  from TFlw_Rteqp A 
                              inner join TEqpInfo B on A.eqp_code=B.code 
                              where B.wksp_code='W001' and A.is_delete<>'1' and B.is_delete<>'1'
                              ) B on A.code=B.eqp_code and A.is_delete<>'1'";
                              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.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();//关联标识
@@ -1286,7 +1785,7 @@
                    sql = @"delete TFlw_Rteqp where step_code=@stepcode";
                    list.Add(new { str = sql, parm = new { stepcode = stepcode } });
                    //标记工序表关联工作站标识
                    sql = @"update TStep is_eqp='N' where stepcode=@stepcode";
                    sql = @"update TStep set is_eqp='N' where stepcode=@stepcode";
                    list.Add(new { str = sql, parm = new { stepcode = stepcode } });
                }
                else
@@ -1341,5 +1840,537 @@
            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,B.flwtype as flag      
                        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<StepDefect>(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,string steptype, int startNum, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            string search = "",sql="";
            int total = 0;
            try
            {
                // --------------查询指定数据--------------
                if (steptype == "Z")  //自制工序
                {
                    total = 0; //总条数
                    //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' ";

                    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,C.eqp_code,'0' as eqp_value,'0' as stand_value,'0' as cavity_qty,'0' as unprice
                            from TFlw_Rout A
                            inner join TFlw_Rtdt B on A.code=B.rout_code
                            left join TFlw_Rteqp C on B.step_code=C.step_code
                            where A.code=@routecode and B.step_code=@stepcode and style='E' and @partcode+A.code+C.eqp_code+B.step_code 
                            not in(select materiel_code+route_code+eqp_code+step_code  from TPrteEqp_Stad where materiel_code=@partcode and route_code=@routecode and step_code=@stepcode)
                            ) 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);
                }
                else  //外协工序
                {
                    total = 0; //总条数
                    //sql = @"select B.code as eqpcode,B.name as eqpname,'' as wksp_code,'' 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='W' and @partcode+eqp_code+step_code not in(select materiel_code+eqp_code+step_code  from TPrteEqp_Stad)
                    //        ) A
                    //        left join TCustomer B on A.eqp_code=B.code
                    //        where B.is_delete<>'1'";
                    sql = @"select B.code as eqpcode,B.name as eqpname,'' as wksp_code,'' 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,C.eqp_code,'0' as eqp_value,'0' as stand_value,'0' as cavity_qty,'0' as unprice
                            from TFlw_Rout A
                            inner join TFlw_Rtdt B on A.code=B.rout_code
                            left join TFlw_Rteqp C on B.step_code=C.step_code
                            where A.code=@routecode and B.step_code=@stepcode and style='W' and @partcode+A.code+C.eqp_code+B.step_code 
                            not in(select materiel_code+route_code+eqp_code+step_code  from TPrteEqp_Stad where materiel_code=@partcode and route_code=@routecode and step_code=@stepcode)
                            ) A
                            left join TCustomer B on A.eqp_code=B.code
                            where B.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 AA.partcode=@partcode ";
                    dynamicParams.Add("@partcode", partcode);
                }
                if (routecode != "" && routecode != null)
                {
                    search += "and AA.route_code=@routecode ";
                    dynamicParams.Add("@routecode", routecode);
                }
                if (stepcode != "" && stepcode != null)
                {
                    search += "and AA.stepcode=@stepcode ";
                    dynamicParams.Add("@stepcode", stepcode);
                }
                if (eqpcode != "" && eqpcode != null)
                {
                    search += "and AA.eqp_code=@eqpcode";
                    dynamicParams.Add("@eqpcode", eqpcode);
                }
                if (search == "")
                {
                    search = "and 1=1 ";
                }
                search = search.Substring(3);//截取索引2后面的字符
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select *  from (
                            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
                            left join TCustomer S on A.eqp_code=S.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'
                            union all
                            select A.materiel_code as partcode,B.partname,B.partspec,A.route_code,C.name as route_name,
                            D.stepcode,D.stepname,S.code,S.name as eqp_name,'' as wksp_code,'' 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 TCustomer S on A.eqp_code=S.code
                            where B.is_delete<>'1' and C.is_delete<>'1' and D.is_delete<>'1' and S.is_delete<>'1'
                            ) as AA where" + 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
    }
}