MES工单新选源单、选单据类型(标准工单、报废补单) 开发工序检验报表(查询、导出)

yl

2022-08-26 437f8436bfd174b34a8ec4e48babf25ceb533ef3

 VueWebApi/DLL/DAL/DeviceManagerDAL.cs

@@ -614,8 +614,15 @@
                    mes.data = null;
                    return mes;
                }

                //删除设备
                sql = @"update TEqpInfo set is_delete='1' where code=@devicecode";
                list.Add(new { str = sql, parm = new { devicecode = devicecode } });
                //删除设备关联点检标准表
                sql = @"delete TEqpchk_Eqp  where eqp_code=@devicecode";
                list.Add(new { str = sql, parm = new { devicecode = devicecode } });
                //删除设备绑定保养标准表
                sql = @"delete TEqpmai_Eqp  where eqp_code=@devicecode";
                list.Add(new { str = sql, parm = new { devicecode = devicecode } });
                bool aa = DapperHelper.DoTransaction(list);
                if (aa)
@@ -1156,7 +1163,7 @@
                if (opertype == "Add")
                {
                    //新增设备点检标准主表
                    sql = @"insert into TEqpchk_Main(code,name,description,iscontr,lm_user,lm_date) values(@code,@name,@descr,@lm_user,@lm_date)";
                    sql = @"insert into TEqpchk_Main(code,name,description,iscontr,lm_user,lm_date) values(@code,@name,@descr,@iscontr,@lm_user,@lm_date)";
                    list.Add(new { str = sql, parm = new { code = json.code, name = json.name, descr = json.description, iscontr = json.enable, lm_user = username, lm_date = DateTime.Now.ToString() } });
                    for (int i = 0; i < json.Data.Rows.Count; i++)
                    {
@@ -1307,5 +1314,589 @@
            return mes;
        }
        #endregion

        #region[设备点检标准关联工作站查询]
        public static ToMessage DeviceCheckStanedAssociationEqp(string checkstand_code)
        {
            string sql = "";
            var dynamicParams = new DynamicParameters();
            List<StandEqp> list = new List<StandEqp>();
            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 TEqpchk_Eqp A 
                        inner join TEqpInfo B on A.eqp_code=B.code 
                        where A.eqpchkmain_code=@checkstand_code 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'";
                dynamicParams.Add("@checkstand_code", checkstand_code);
                var data = DapperHelper.selectdata(sql, dynamicParams);
                for (int i = 0; i < data.Rows.Count; i++)
                {
                    StandEqp rout = new StandEqp();
                    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<StandEqpCn>();
                    //根据车间编码查找设备(包含已关联标识)
                    //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 TEqpchk_Eqp A 
                    //        inner join TEqpInfo B on A.eqp_code=B.code 
                    //        where A.eqpchkmain_code=@checkstand_code 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'";
                    sql = @"select *  from(
                            select AA.code,AA.name,(case when BB.eqpchkmain_code is null then 'N' else 'Y' end) flag,(case when AA.eqpchkmain_code  is null then 'N' else 'Y' end) flage1
                            from(
                            select A.code,A.name,B.eqpchkmain_code
                            from TEqpInfo A
                            left join TEqpchk_Eqp B on A.code=B.eqp_code
                            where A.wksp_code=@wkspcode and A.is_delete<>'1' 
                            ) as AA
                            left join (
                             select A.code,A.name,B.eqpchkmain_code
                             from TEqpInfo A
                             inner join TEqpchk_Eqp B on A.code=B.eqp_code
                             where A.wksp_code=@wkspcode and B.eqpchkmain_code=@checkstand_code and A.is_delete<>'1'
                            ) as BB on AA.code=BB.code
                            ) as CC where case when flag ='N' and flage1 ='Y' then 0 else 1 end=1";
                    dynamicParams.Add("@checkstand_code", checkstand_code);
                    dynamicParams.Add("@wkspcode", data.Rows[i]["WKSP_CODE"].ToString());
                    var data0 = DapperHelper.selectdata(sql, dynamicParams);
                    for (int j = 0; j < data0.Rows.Count; j++)
                    {
                        StandEqpCn cn = new StandEqpCn();
                        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 SaveDeviceCheckStanedAssociationEqp(string checkstand_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 TEqpchk_Eqp where eqpchkmain_code=@checkstand_code";
                    list.Add(new { str = sql, parm = new { checkstand_code = checkstand_code } });
                    //标记设备点检标准表关联工作站标识
                    sql = @"update TEqpchk_Main set is_checkeqp='N' where code=@checkstand_code";
                    list.Add(new { str = sql, parm = new { checkstand_code = checkstand_code } });
                }
                else
                {
                    //清除设备点检标准关联设备表数据
                    sql = @"delete TEqpchk_Eqp where eqpchkmain_code=@checkstand_code";
                    list.Add(new { str = sql, parm = new { checkstand_code = checkstand_code } });
                    //循环写入设备点检标准关联设备表
                    for (int i = 0; i < json.Count; i++)
                    {
                        sql = @"insert into TEqpchk_Eqp(eqpchkmain_code,eqp_code,lm_user,lm_date) values(@checkstand_code,@eqp_code,@lm_user,@lm_date)";
                        list.Add(new
                        {
                            str = sql,
                            parm = new
                            {
                                eqp_code = json[i].code,
                                checkstand_code = checkstand_code,
                                lm_user = username,
                                lm_date = DateTime.Now.ToString()
                            }
                        });
                    }
                    //标记设备点检标准表关联工作站标识
                    sql = @"update TEqpchk_Main set is_checkeqp='Y' where code=@checkstand_code";
                    list.Add(new { str = sql, parm = new { checkstand_code = checkstand_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 DeviceRepairStandArdSearch(string repairstandcode, string repairstandname, string repairstanddescr, int startNum, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
            try
            {
                if (repairstandcode != "" && repairstandcode != null)
                {
                    search += "and code like '%'+@repairstandcode+'%' ";
                    dynamicParams.Add("@repairstandcode", repairstandcode);
                }
                if (repairstandname != "" && repairstandname != null)
                {
                    search += "and name like '%'+@repairstandname+'%' ";
                    dynamicParams.Add("@repairstandname", repairstandname);
                }
                if (repairstanddescr != "" && repairstanddescr != null)
                {
                    search += "and description like '%'+@repairstanddescr+'%' ";
                    dynamicParams.Add("@repairstanddescr", repairstanddescr);
                }
                
                if (search == "")
                {
                    search = "and 1=1 ";
                }
                //search = search.Substring(3);//截取索引2后面的字符
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select id,code,name,description,is_repaireqp,lm_user,lm_date 
                            from TEqpmai_Main where 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 DeviceRepairItemSelect()
        {
            string sql = "";
            try
            {
                //获取设备保养项目数据
                sql = @"select code,name,description from TEqpmai_Item 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 ViewDeviceRepairStanedSearch(string repairstand_code)
        {
            string sql = "";
            var dynamicParams = new DynamicParameters();
            try
            {
                //获取设备保养标准信息
                sql = @"select code,name,description
                        from TEqpmai_Main
                        where code=@repairstand_code and is_delete<>'1'";
                dynamicParams.Add("@repairstand_code", repairstand_code);
                var data = DapperHelper.selectdata(sql, dynamicParams);
                RoutEdit rout = new RoutEdit();
                if (data.Rows.Count > 0)
                {
                    rout.code = data.Rows[0]["CODE"].ToString();
                    rout.name = data.Rows[0]["NAME"].ToString();
                    rout.description = data.Rows[0]["DESCRIPTION"].ToString();
                }
                else
                {
                    mes.code = "300";
                    mes.count = 0;
                    mes.Message = "无保养标准信息!";
                    mes.data = null;
                    return mes;
                }
                //根据设备保养标准编码获取关联的保养项目信息
                sql = @"select B.code,B.name,A.chkdesc,A.isscan,A.cycle  from TEqpmai_Deta A
                        left join TEqpmai_Item  B on A.code=B.code
                        where  A.eapmai_code=@repairstand_code order by A.seq asc";
                dynamicParams.Add("@repairstand_code", rout.code);
                var data0 = DapperHelper.selectdata(sql, dynamicParams);
                rout.Data = data0;

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

        #region[设备保养标准新增编辑]
        public static ToMessage AddUpdateDeviceRepairStandArd(string opertype, RoutEdit json, string username)
        {
            var sql = "";
            var dynamicParams = new DynamicParameters();
            List<object> list = new List<object>();
            try
            {
                if (opertype == "Add")
                {
                    //新增设备保养标准主表
                    sql = @"insert into TEqpmai_Main(code,name,description,lm_user,lm_date) values(@code,@name,@descr,@lm_user,@lm_date)";
                    list.Add(new { str = sql, parm = new { code = json.code, name = json.name, descr = json.description, lm_user = username, lm_date = DateTime.Now.ToString() } });
                    for (int i = 0; i < json.Data.Rows.Count; i++)
                    {
                        //新增设备保养标准关联保养项子表
                        sql = @"insert TEqpmai_Deta (seq,code,name,eapmai_code,cycle,chk_desc,isscan,lm_user,lm_date) 
                                values(@repairitem_seq,@repairitem_code,@repairitem_name,@code,@cycle,@repairdesc,@isscan,@lm_user,@lm_date)";
                        list.Add(new
                        {
                            str = sql,
                            parm = new
                            {
                                repairitem_seq = Convert.ToInt32(json.Data.Rows[i]["REPAIRITEM_SEQ"].ToString()),
                                repairitem_code = json.Data.Rows[i]["REPAIRITEM_CODE"].ToString(),
                                repairitem_name = json.Data.Rows[i]["REPAIRITEM_NAME"].ToString(),
                                code = json.code,
                                cycle = json.Data.Rows[i]["CYCLE"].ToString(),
                                repairdesc = json.Data.Rows[i]["REPAIRITEM_DESCR"].ToString(),
                                isscan = json.Data.Rows[i]["ISSCAN"].ToString(),
                                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;
                    }
                }
                if (opertype == "Update")
                {
                    //删除保养标准关联点检项目子表
                    sql = @"delete from TEqpmai_Deta where eapmai_code=@repairstaned_code";
                    list.Add(new { str = sql, parm = new { repairstaned_code = json.code } });
                    //修改保养标准主表
                    sql = @"update  TEqpmai_Main set name=@repairstaned_name,description=@repairstaned_desc where code=@repairstaned_code";
                    list.Add(new { str = sql, parm = new { repairstaned_code = json.code, repairstaned_name = json.name, repairstaned_desc = json.description } });
                    //新增点检标准关联点检项目子表
                    for (int i = 0; i < json.Data.Rows.Count; i++)
                    {
                        sql = @"insert TEqpmai_Deta (seq,code,name,eapmai_code,cycle,chk_desc,isscan,lm_user,lm_date) 
                                values(@repairitem_seq,@repairitem_code,@repairitem_name,@code,@cycle,@repairdesc,@isscan,@lm_user,@lm_date)";
                        list.Add(new
                        {
                            str = sql,
                            parm = new
                            {
                                repairitem_seq = Convert.ToInt32(json.Data.Rows[i]["REPAIRITEM_SEQ"].ToString()),
                                repairitem_code = json.Data.Rows[i]["REPAIRITEM_CODE"].ToString(),
                                repairitem_name = json.Data.Rows[i]["REPAIRITEM_NAME"].ToString(),
                                code = json.code,
                                cycle = json.Data.Rows[i]["CYCLE"].ToString(),
                                repairdesc = json.Data.Rows[i]["REPAIRITEM_DESCR"].ToString(),
                                isscan = json.Data.Rows[i]["ISSCAN"].ToString(),
                                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 DeleteDeviceRepairStaned(string repairstand_code)
        {
            var sql = "";
            List<object> list = new List<object>();
            var dynamicParams = new DynamicParameters();
            try
            {
                sql = @"select * from  TEqpmaint_Proc_Main  where eqpmaint_code=@repairstand_code";
                dynamicParams.Add("@repairstand_code", repairstand_code);
                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 TEqpmai_Main  where code=@repairstand_code";
                list.Add(new { str = sql, parm = new { repairstand_code = repairstand_code } });
                //删除设备保养标准关联保养项目子表
                sql = @"delete TEqpmai_Deta  where eapmai_code=@repairstand_code";
                list.Add(new { str = sql, parm = new { repairstand_code = repairstand_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 DeviceRepairStanedAssociationEqp(string repairstand_code)
        {
            string sql = "";
            var dynamicParams = new DynamicParameters();
            List<StandEqp> list = new List<StandEqp>();
            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 TEqpmai_Eqp A 
                        inner join TEqpInfo B on A.eqp_code=B.code 
                        where A.eapmai_code=@repairstand_code 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'";

                dynamicParams.Add("@repairstand_code", repairstand_code);
                var data = DapperHelper.selectdata(sql, dynamicParams);
                for (int i = 0; i < data.Rows.Count; i++)
                {
                    StandEqp rout = new StandEqp();
                    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<StandEqpCn>();
                    //根据车间编码查找设备(包含已关联标识)
                    //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 TEqpmai_Eqp A 
                    //        inner join TEqpInfo B on A.eqp_code=B.code 
                    //        where A.eapmai_code=@repairstand_code 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'";
                    sql = @"select *  from(
                            select AA.code,AA.name,(case when BB.eapmai_code is null then 'N' else 'Y' end) flag,(case when AA.eapmai_code  is null then 'N' else 'Y' end) flage1
                            from(
                            select A.code,A.name,B.eapmai_code
                            from TEqpInfo A
                            left join TEqpmai_Eqp B on A.code=B.eqp_code
                            where A.wksp_code=@wkspcode and A.is_delete<>'1' 
                            ) as AA
                            left join (
                             select A.code,A.name,B.eapmai_code
                             from TEqpInfo A
                             inner join TEqpmai_Eqp B on A.code=B.eqp_code
                             where A.wksp_code=@wkspcode and B.eapmai_code=@repairstand_code and A.is_delete<>'1'
                            ) as BB on AA.code=BB.code
                            ) as CC where case when flag ='N' and flage1 ='Y' then 0 else 1 end=1";
                    dynamicParams.Add("@repairstand_code", repairstand_code);
                    dynamicParams.Add("@wkspcode", data.Rows[i]["WKSP_CODE"].ToString());
                    var data0 = DapperHelper.selectdata(sql, dynamicParams);
                    for (int j = 0; j < data0.Rows.Count; j++)
                    {
                        StandEqpCn cn = new StandEqpCn();
                        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 SaveDeviceRepairStanedAssociationEqp(string repairstand_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 TEqpmai_Eqp where eapmai_code=@repairstand_code";
                    list.Add(new { str = sql, parm = new { repairstand_code = repairstand_code } });
                    //标记设备保养标准表关联工作站标识
                    sql = @"update TEqpmai_Main set is_repaireqp='N' where code=@repairstand_code";
                    list.Add(new { str = sql, parm = new { repairstand_code = repairstand_code } });
                }
                else
                {
                    //清除设备保养标准关联设备表数据
                    sql = @"delete TEqpmai_Eqp where eapmai_code=@repairstand_code";
                    list.Add(new { str = sql, parm = new { repairstand_code = repairstand_code } });
                    //循环写入设备保养标准关联设备表
                    for (int i = 0; i < json.Count; i++)
                    {
                        sql = @"insert into TEqpmai_Eqp(eapmai_code,eqp_code,lm_user,lm_date) values(@repairstand_code,@eqp_code,@lm_user,@lm_date)";
                        list.Add(new
                        {
                            str = sql,
                            parm = new
                            {
                                eqp_code = json[i].code,
                                repairstand_code = repairstand_code,
                                lm_user = username,
                                lm_date = DateTime.Now.ToString()
                            }
                        });
                    }
                    //标记设备保养标准表关联工作站标识
                    sql = @"update TEqpmai_Main set is_repaireqp='Y' where code=@repairstand_code";
                    list.Add(new { str = sql, parm = new { repairstand_code = repairstand_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
    }
}