设备点检记录导出接口去掉分页参数，设备保养记录查询、明细查询、导出(接口开发)

yl

2022-09-10 e691f2ecede6a962dfa76bb4a494ac1c80c9a389

 VueWebApi/DLL/DAL/DeviceManagerDAL.cs

@@ -663,27 +663,27 @@
            {
                if (checkitemcode != "" && checkitemcode != null)
                {
                    search += "and code like '%'+@checkitemcode+'%' ";
                    search += "and A.code like '%'+@checkitemcode+'%' ";
                    dynamicParams.Add("@checkitemcode", checkitemcode);
                }
                if (checkitemname != "" && checkitemname != null)
                {
                    search += "and name like '%'+@checkitemname+'%' ";
                    search += "and A.name like '%'+@checkitemname+'%' ";
                    dynamicParams.Add("@checkitemname", checkitemname);
                }
                if (checkdescr != "" && checkdescr != null)
                {
                    search += "and description=@checkdescr ";
                    search += "and A.description like '%'+@checkdescr+'%' ";
                    dynamicParams.Add("@checkdescr", checkdescr);
                }
                if (isqrcode != "" && isqrcode != null)
                {
                    search += "and isscan=@isqrcode ";
                    search += "and A.isscan=@isqrcode ";
                    dynamicParams.Add("@isqrcode", isqrcode);
                }
                if (cycle != "" && cycle != null)
                {
                    search += "and isscan=@cycle ";
                    search += "and A.cycle=@cycle ";
                    dynamicParams.Add("@cycle", cycle);
                }
                if (search == "")
@@ -693,8 +693,10 @@
                //search = search.Substring(3);//截取索引2后面的字符
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select id,code,name,description,isscan,cycle,lm_user,lm_date 
                            from TEqpchk_Item where is_delete<>'1' " + search;
                var sql = @"select A.id,A.code,A.name,A.description,A.isscan,A.cycle,U.username as lm_user,A.lm_date 
                            from TEqpchk_Item 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 = "查询成功!";
@@ -842,7 +844,7 @@


        #region[设备保养项列表查询]
        public static ToMessage DeviceMaiItemSearch(string maiitemcode, string maiitemname, string maidescr, string isqrcode, string cycle, int startNum, int endNum, string prop, string order)
        public static ToMessage DeviceMaiItemSearch(string maiitemcode, string maiitemname, string maidescr, string isqrcode, int startNum, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
@@ -850,28 +852,23 @@
            {
                if (maiitemcode != "" && maiitemcode != null)
                {
                    search += "and code like '%'+@maiitemcode+'%' ";
                    search += "and A.code like '%'+@maiitemcode+'%' ";
                    dynamicParams.Add("@maiitemcode", maiitemcode);
                }
                if (maiitemname != "" && maiitemname != null)
                {
                    search += "and name like '%'+@maiitemname+'%' ";
                    search += "and A.name like '%'+@maiitemname+'%' ";
                    dynamicParams.Add("@maiitemname", maiitemname);
                }
                if (maidescr != "" && maidescr != null)
                {
                    search += "and description=@maidescr ";
                    search += "and A.description like '%'+@maidescr+'%' ";
                    dynamicParams.Add("@maidescr", maidescr);
                }
                if (isqrcode != "" && isqrcode != null)
                {
                    search += "and isscan=@isqrcode ";
                    search += "and A.isscan=@isqrcode ";
                    dynamicParams.Add("@isqrcode", isqrcode);
                }
                if (cycle != "" && cycle != null)
                {
                    search += "and isscan=@cycle ";
                    dynamicParams.Add("@cycle", cycle);
                }
                if (search == "")
                {
@@ -880,8 +877,10 @@
                //search = search.Substring(3);//截取索引2后面的字符
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select id,code,name,description,isscan,cycle,lm_user,lm_date 
                            from TEqpmai_Item where is_delete<>'1' " + search;
                var sql = @"select A.id,A.code,A.name,A.description,A.isscan,U.username as lm_user,A.lm_date 
                            from TEqpmai_Item 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 = "查询成功!";
@@ -900,20 +899,19 @@
        #endregion

        #region[设备保养项新增编辑]
        public static ToMessage AddUpdateDeviceMaiItem(string maiitemid, string maiitemcode, string maiitemname, string maiitemdescr, string cycle, string isqrcode, string usercode, string operType)
        public static ToMessage AddUpdateDeviceMaiItem(string maiitemid, string maiitemcode, string maiitemname, string maiitemdescr, string isqrcode, string usercode, string operType)
        {
            var dynamicParams = new DynamicParameters();
            try
            {
                if (operType == "Add")
                {
                    var sql = @"insert into TEqpmai_Item(code,name,description,isscan,cycle,lm_user,lm_date) 
                              values(@maiitemcode,@maiitemname,@checkitemdescr,@isqrcode,@cycle,@usercode,@CreateDate)";
                    var sql = @"insert into TEqpmai_Item(code,name,description,isscan,lm_user,lm_date) 
                              values(@maiitemcode,@maiitemname,@maiitemdescr,@isqrcode,@usercode,@CreateDate)";
                    dynamicParams.Add("@maiitemcode", maiitemcode);
                    dynamicParams.Add("@maiitemname", maiitemname);
                    dynamicParams.Add("@maiitemdescr", maiitemdescr);
                    dynamicParams.Add("@isqrcode", isqrcode);
                    dynamicParams.Add("@cycle", cycle);
                    dynamicParams.Add("@usercode", usercode);
                    dynamicParams.Add("@CreateDate", DateTime.Now.ToString());
                    int cont = DapperHelper.SQL(sql, dynamicParams);
@@ -934,14 +932,13 @@
                }
                if (operType == "Update")
                {
                    var sql = @"update TEqpmai_Item set name=@maiitemname,description=@maiitemdescr,isscan=@isqrcode,cycle=@cycle,
                    var sql = @"update TEqpmai_Item set name=@maiitemname,description=@maiitemdescr,isscan=@isqrcode,
                                lm_user=@usercode,lm_date=@CreateDate
                                where id=@maiitemid";
                    dynamicParams.Add("@maiitemid", maiitemid);
                    dynamicParams.Add("@maiitemname", maiitemname);
                    dynamicParams.Add("@maiitemdescr", maiitemdescr);
                    dynamicParams.Add("@isqrcode", isqrcode);
                    dynamicParams.Add("@cycle", cycle);
                    dynamicParams.Add("@usercode", usercode);
                    dynamicParams.Add("@CreateDate", DateTime.Now.ToString());
                    int cont = DapperHelper.SQL(sql, dynamicParams);
@@ -1037,17 +1034,17 @@
            {
                if (checkstandcode != "" && checkstandcode != null)
                {
                    search += "and code like '%'+@checkstandcode+'%' ";
                    search += "and A.code like '%'+@checkstandcode+'%' ";
                    dynamicParams.Add("@checkstandcode", checkstandcode);
                }
                if (checkstandname != "" && checkstandname != null)
                {
                    search += "and name like '%'+@checkstandname+'%' ";
                    search += "and A.name like '%'+@checkstandname+'%' ";
                    dynamicParams.Add("@checkstandname", checkstandname);
                }
                if (checkcontr != "" && checkcontr != null)
                {
                    search += "and iscontr=@checkcontr ";
                    search += "and A.iscontr=@checkcontr ";
                    dynamicParams.Add("@checkcontr", checkcontr);
                }
                if (search == "")
@@ -1057,8 +1054,10 @@
                //search = search.Substring(3);//截取索引2后面的字符
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select id,code,name,description,iscontr,is_checkeqp,lm_user,lm_date 
                            from TEqpchk_Main where is_delete<>'1' " + search;
                var sql = @"select A.id,A.code,A.name,A.description,A.iscontr,A.is_checkeqp,U.username as lm_user,A.lm_date 
                            from TEqpchk_Main 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 = "查询成功!";
@@ -1210,7 +1209,7 @@
                    list.Add(new { str = sql, parm = new { checkstaned_code = json.code } });
                    //修改点检标准主表
                    sql = @"update  TEqpchk_Main set name=@checkstaned_name,description=@checkstaned_desc,iscontr=@iscontr where code=@checkstaned_code";
                    list.Add(new { str = sql, parm = new { checkstaned_code = json.code, checkstaned_name = json.name, checkstaned_desc = json.description } });
                    list.Add(new { str = sql, parm = new { checkstaned_code = json.code, checkstaned_name = json.name, checkstaned_desc = json.description,iscontr = json.enable } });
                    //新增点检标准关联点检项目子表
                    for (int i = 0; i < json.Data.Rows.Count; i++)
                    {
@@ -1350,7 +1349,7 @@
                    //        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
                            select AA.code,AA.name,'E' as type,(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
@@ -1473,17 +1472,17 @@
            {
                if (repairstandcode != "" && repairstandcode != null)
                {
                    search += "and code like '%'+@repairstandcode+'%' ";
                    search += "and A.code like '%'+@repairstandcode+'%' ";
                    dynamicParams.Add("@repairstandcode", repairstandcode);
                }
                if (repairstandname != "" && repairstandname != null)
                {
                    search += "and name like '%'+@repairstandname+'%' ";
                    search += "and A.name like '%'+@repairstandname+'%' ";
                    dynamicParams.Add("@repairstandname", repairstandname);
                }
                if (repairstanddescr != "" && repairstanddescr != null)
                {
                    search += "and description like '%'+@repairstanddescr+'%' ";
                    search += "and A.description like '%'+@repairstanddescr+'%' ";
                    dynamicParams.Add("@repairstanddescr", repairstanddescr);
                }
                
@@ -1494,8 +1493,10 @@
                //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 sql = @"select A.id,A.code,A.name,A.description,A.is_repaireqp,A.main_cycle,U.username as lm_user,A.lm_date 
                            from TEqpmai_Main 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 = "查询成功!";
@@ -1545,16 +1546,17 @@
            try
            {
                //获取设备保养标准信息
                sql = @"select code,name,description
                sql = @"select code,name,description,main_cycle
                        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();
                EqpRepair rout = new EqpRepair();
                if (data.Rows.Count > 0)
                {
                    rout.code = data.Rows[0]["CODE"].ToString();
                    rout.name = data.Rows[0]["NAME"].ToString();
                    rout.repaircycle= data.Rows[0]["MAIN_CYCLE"].ToString();
                    rout.description = data.Rows[0]["DESCRIPTION"].ToString();
                }
                else
@@ -1566,7 +1568,7 @@
                    return mes;
                }
                //根据设备保养标准编码获取关联的保养项目信息
                sql = @"select B.code,B.name,A.chkdesc,A.isscan,A.cycle  from TEqpmai_Deta A
                sql = @"select B.code,B.name,A.chk_desc,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);
@@ -1589,7 +1591,7 @@
        #endregion

        #region[设备保养标准新增编辑]
        public static ToMessage AddUpdateDeviceRepairStandArd(string opertype, RoutEdit json, string username)
        public static ToMessage AddUpdateDeviceRepairStandArd(string opertype, EqpRepair json, string username)
        {
            var sql = "";
            var dynamicParams = new DynamicParameters();
@@ -1599,13 +1601,13 @@
                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() } });
                    sql = @"insert into TEqpmai_Main(code,name,description,main_cycle,lm_user,lm_date) values(@code,@name,@descr,@main_cycle,@lm_user,@lm_date)";
                    list.Add(new { str = sql, parm = new { code = json.code, name = json.name, descr = json.description,main_cycle=json.repaircycle, 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)";
                        sql = @"insert TEqpmai_Deta (seq,code,name,eapmai_code,chk_desc,isscan,lm_user,lm_date) 
                                values(@repairitem_seq,@repairitem_code,@repairitem_name,@code,@repairdesc,@isscan,@lm_user,@lm_date)";
                        list.Add(new
                        {
                            str = sql,
@@ -1615,7 +1617,6 @@
                                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,
@@ -1645,13 +1646,13 @@
                    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 } });
                    sql = @"update  TEqpmai_Main set name=@repairstaned_name,description=@repairstaned_desc,main_cycle=@main_cycle where code=@repairstaned_code";
                    list.Add(new { str = sql, parm = new { repairstaned_code = json.code, repairstaned_name = json.name, repairstaned_desc = json.description, main_cycle =json.repaircycle} });
                    //新增点检标准关联点检项目子表
                    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)";
                        sql = @"insert TEqpmai_Deta (seq,code,name,eapmai_code,chk_desc,isscan,lm_user,lm_date) 
                                values(@repairitem_seq,@repairitem_code,@repairitem_name,@code,@repairdesc,@isscan,@lm_user,@lm_date)";
                        list.Add(new
                        {
                            str = sql,
@@ -1661,7 +1662,6 @@
                                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,
@@ -1787,7 +1787,7 @@
                    //        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
                            select AA.code,AA.name,'E' as type,(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
@@ -1898,5 +1898,418 @@
            return mes;
        }
        #endregion



        #region[设备点检记录查询]
        public static ToMessage DeviceCheckTakeSearch(string wkshopcode, string eqpcode, string eqpname, string stanedname, string checkuser, string checkopendate, string checkclosedate, int startNum, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
            try
            {
                if (wkshopcode != "" && wkshopcode != null)
                {
                    search += "and T.org_code=@wkshopcode ";
                    dynamicParams.Add("@wkshopcode", wkshopcode);
                }
                if (eqpcode != "" && eqpcode != null)
                {
                    search += "and A.eqp_code like '%'+@eqpcode+'%' ";
                    dynamicParams.Add("@eqpcode", eqpcode);
                }
                if (eqpname != "" && eqpname != null)
                {
                    search += "and E.name like '%'+@eqpname+'%' ";
                    dynamicParams.Add("@eqpname", eqpname);
                }
                if (stanedname != "" && stanedname != null)
                {
                    search += "and M.name like '%'+@stanedname+'%' ";
                    dynamicParams.Add("@stanedname", stanedname);
                }
                if (checkuser != "" && checkuser != null)
                {
                    search += "and A.chk_user like '%'+@checkuser+'%' ";
                    dynamicParams.Add("@checkuser", checkuser);
                }
                if (checkopendate != "" && checkopendate != null)
                {
                    search += "and A.chk_date between @checkopendate and @checkclosedate ";
                    dynamicParams.Add("@checkopendate", checkopendate + " 00:00:00");
                    dynamicParams.Add("@checkclosedate", checkclosedate + " 23:59:59");
                }
                if (search == "")
                {
                    search = "and 1=1 ";
                }
                //search = search.Substring(3);//截取索引2后面的字符
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select A.djwo,T.org_code,T.org_name,A.eqp_code,E.name as eqp_name,M.code as stanedcode,M.name as stanedname,
                            A.chk_user,A.chk_result,A.chk_date
                            from TEqpchk_Proc_Main A
                            left join TEqpInfo E on A.eqp_code=E.code
                            left join TEqpchk_Main M on A.eqpchkmain_code=M.code
                            left join TOrganization T on E.wksp_code=T.org_code
                            where T.description='W' " + 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 DeviceCheckSubTakeSearch(string djwo)
        {
            string sql = "";
            var dynamicParams = new DynamicParameters();
            try
            {
                sql = @"select A.seq,T.code as itemcode,T.name as itemname,A.result,A.chk_value,A.remark
                        from TEqpchk_Proc_Deta A
                        left join TEqpchk_Item T on A.eqpchkdeta_code=T.code
                        where A.djwo=@djwo";
                dynamicParams.Add("@djwo", djwo);
                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 DeviceCheckTakeOutExcel(string wkshopcode, string eqpcode, string eqpname, string stanedname, string checkuser, string checkopendate, string checkclosedate)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
            try
            {
                if (wkshopcode != "" && wkshopcode != null)
                {
                    search += "and T.org_code=@wkshopcode ";
                    dynamicParams.Add("@wkshopcode", wkshopcode);
                }
                if (eqpcode != "" && eqpcode != null)
                {
                    search += "and A.eqp_code like '%'+@eqpcode+'%' ";
                    dynamicParams.Add("@eqpcode", eqpcode);
                }
                if (eqpname != "" && eqpname != null)
                {
                    search += "and E.name like '%'+@eqpname+'%' ";
                    dynamicParams.Add("@eqpname", eqpname);
                }
                if (stanedname != "" && stanedname != null)
                {
                    search += "and M.name like '%'+@stanedname+'%' ";
                    dynamicParams.Add("@stanedname", stanedname);
                }
                if (checkuser != "" && checkuser != null)
                {
                    search += "and A.chk_user like '%'+@checkuser+'%' ";
                    dynamicParams.Add("@checkuser", checkuser);
                }
                if (checkopendate != "" && checkopendate != null)
                {
                    search += "and A.chk_date between @checkopendate and @checkclosedate ";
                    dynamicParams.Add("@checkopendate", checkopendate + " 00:00:00");
                    dynamicParams.Add("@checkclosedate", checkclosedate + " 23:59:59");
                }
                if (search == "")
                {
                    search = "and 1=1 ";
                }
                search = search.Substring(3);//截取索引2后面的字符
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select 
                            CASE WHEN
                            ROW_NUMBER() OVER (PARTITION BY A.djwo ORDER BY B.eqpchkdeta_code) = 1 THEN A.djwo
                            ELSE ''END AS '点检单号',
                            CASE WHEN
                            ROW_NUMBER() OVER (PARTITION BY E.wksp_code ORDER BY B.eqpchkdeta_code) = 1 THEN T.org_name
                            ELSE ''END AS '生产车间',
                            CASE WHEN
                            ROW_NUMBER() OVER (PARTITION BY A.eqp_code ORDER BY B.eqpchkdeta_code) = 1 THEN A.eqp_code
                            ELSE ''END AS '设备编号',
                            CASE WHEN
                            ROW_NUMBER() OVER (PARTITION BY A.eqp_code ORDER BY B.eqpchkdeta_code) = 1 THEN E.name
                            ELSE ''END AS '设备名称',
                            CASE WHEN
                            ROW_NUMBER() OVER (PARTITION BY A.eqpchkmain_code ORDER BY B.eqpchkdeta_code) = 1 THEN A.eqpchkmain_code
                            ELSE ''END AS '点检标准编码',
                            CASE WHEN
                            ROW_NUMBER() OVER (PARTITION BY A.eqpchkmain_code ORDER BY B.eqpchkdeta_code) = 1 THEN M.name
                            ELSE ''END AS '点检标准名称',
                            CASE WHEN
                            ROW_NUMBER() OVER (PARTITION BY A.chk_user ORDER BY B.eqpchkdeta_code) = 1 THEN A.chk_user
                            ELSE ''END AS '检验人员',
                            CASE WHEN
                            ROW_NUMBER() OVER (PARTITION BY A.chk_result ORDER BY B.eqpchkdeta_code) = 1 THEN A.chk_result
                            ELSE ''END AS '检验结果',
                            CASE WHEN
                            ROW_NUMBER() OVER (PARTITION BY A.chk_date ORDER BY B.eqpchkdeta_code) = 1 THEN A.chk_date
                            END AS '检验时间',
                            B.seq as '点检部位序号',H.code '点检部位编码',H.name '点检部位名称',B.result '点检结果',B.chk_value '数值',B.remark '备注'
                            from TEqpchk_Proc_Main A
                            left join TEqpchk_Proc_Deta B on A.djwo=B.djwo
                            left join TEqpInfo E on A.eqp_code=E.code
                            left join TEqpchk_Main M on A.eqpchkmain_code=M.code
                            left join TOrganization T on E.wksp_code=T.org_code
                            left join TEqpchk_Item H on B.eqpchkdeta_code=H.code
                            where T.description='W' " + search;
                DataTable data = DapperHelper.selectdata(sql, dynamicParams);
                data.TableName = "Table"; //设置DataTable的名称
                string msg = DownLoad.DataTableToExcel(data, "工序检验记录");
                mes.code = "200";
                mes.Message = "查询成功!";
                mes.count = total;
                mes.data = msg;
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion



        #region[设备保养记录查询]
        public static ToMessage DeviceCheckTakeSearch(string wkshopcode, string eqpcode, string eqpname, string stanedname, string repairuser, string repairopendate, string repairclosedate, string repairresult, int startNum, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
            try
            {
                if (wkshopcode != "" && wkshopcode != null)
                {
                    search += "and T.org_code=@wkshopcode ";
                    dynamicParams.Add("@wkshopcode", wkshopcode);
                }
                if (eqpcode != "" && eqpcode != null)
                {
                    search += "and A.eqp_code like '%'+@eqpcode+'%' ";
                    dynamicParams.Add("@eqpcode", eqpcode);
                }
                if (eqpname != "" && eqpname != null)
                {
                    search += "and E.name like '%'+@eqpname+'%' ";
                    dynamicParams.Add("@eqpname", eqpname);
                }
                if (stanedname != "" && stanedname != null)
                {
                    search += "and M.name like '%'+@stanedname+'%' ";
                    dynamicParams.Add("@stanedname", stanedname);
                }
                if (repairuser != "" && repairuser != null)
                {
                    search += "and A.maint_user like '%'+@repairuser+'%' ";
                    dynamicParams.Add("@repairuser", repairuser);
                }
                if (repairopendate != "" && repairopendate != null)
                {
                    search += "and A.maint_date between @repairopendate and @repairclosedate ";
                    dynamicParams.Add("@repairopendate", repairopendate + " 00:00:00");
                    dynamicParams.Add("@repairclosedate", repairclosedate + " 23:59:59");
                }
                if (repairresult != "" && repairresult != null)
                {
                    search += "and A.maint_result=@repairresult ";
                    dynamicParams.Add("@repairresult", repairresult);
                }
                if (search == "")
                {
                    search = "and 1=1 ";
                }
                //search = search.Substring(3);//截取索引2后面的字符
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select A.bywo,T.org_code,T.org_name,A.eqp_code,E.name as eqp_name,M.code as stanedcode,M.name as stanedname,A.maint_cyc,
                            A.maint_user,A.maint_result,A.maint_date
                            from TEqpmaint_Proc_Main A
                            left join TEqpInfo E on A.eqp_code=E.code
                            left join TEqpmai_Main M on A.eqpmaint_code=M.code
                            left join TOrganization T on E.wksp_code=T.org_code
                            where T.description='W' " + 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 DeviceRepairSubTakeSearch(string bywo)
        {
            string sql = "";
            var dynamicParams = new DynamicParameters();
            try
            {
                sql = @"select A.seq,T.code as itemcode,T.name as itemname,A.result,A.maint_value,A.remark
                        from TEqpmaint_Proc_Deta A
                        left join TEqpmai_Item T on A.eqpmaideta_code=T.code
                        where A.bywo=@bywo";
                dynamicParams.Add("@bywo", bywo);
                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 DeviceRepairTakeOutExcel(string wkshopcode, string eqpcode, string eqpname, string stanedname, string repairuser, string repairopendate, string repairclosedate, string repairresult)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
            try
            {
                if (wkshopcode != "" && wkshopcode != null)
                {
                    search += "and T.org_code=@wkshopcode ";
                    dynamicParams.Add("@wkshopcode", wkshopcode);
                }
                if (eqpcode != "" && eqpcode != null)
                {
                    search += "and A.eqp_code like '%'+@eqpcode+'%' ";
                    dynamicParams.Add("@eqpcode", eqpcode);
                }
                if (eqpname != "" && eqpname != null)
                {
                    search += "and E.name like '%'+@eqpname+'%' ";
                    dynamicParams.Add("@eqpname", eqpname);
                }
                if (stanedname != "" && stanedname != null)
                {
                    search += "and M.name like '%'+@stanedname+'%' ";
                    dynamicParams.Add("@stanedname", stanedname);
                }
                if (repairuser != "" && repairuser != null)
                {
                    search += "and A.maint_user like '%'+@repairuser+'%' ";
                    dynamicParams.Add("@repairuser", repairuser);
                }
                if (repairopendate != "" && repairopendate != null)
                {
                    search += "and A.chk_date between @repairopendate and @repairclosedate ";
                    dynamicParams.Add("@repairopendate", repairopendate + " 00:00:00");
                    dynamicParams.Add("@repairclosedate", repairclosedate + " 23:59:59");
                }
                if (repairresult != "" && repairresult != null)
                {
                    search += "and A.maint_result=@repairresult ";
                    dynamicParams.Add("@repairresult", repairresult);
                }
                if (search == "")
                {
                    search = "and 1=1 ";
                }
                search = search.Substring(3);//截取索引2后面的字符
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select 
                            CASE WHEN
                            ROW_NUMBER() OVER (PARTITION BY A.bywo ORDER BY B.eqpmaideta_code) = 1 THEN A.bywo
                            ELSE ''END AS '保养单号',
                            CASE WHEN
                            ROW_NUMBER() OVER (PARTITION BY E.wksp_code ORDER BY B.eqpmaideta_code) = 1 THEN T.org_name
                            ELSE ''END AS '生产车间',
                            CASE WHEN
                            ROW_NUMBER() OVER (PARTITION BY A.eqp_code ORDER BY B.eqpmaideta_code) = 1 THEN A.eqp_code
                            ELSE ''END AS '设备编号',
                            CASE WHEN
                            ROW_NUMBER() OVER (PARTITION BY A.eqp_code ORDER BY B.eqpmaideta_code) = 1 THEN E.name
                            ELSE ''END AS '设备名称',
                            CASE WHEN
                            ROW_NUMBER() OVER (PARTITION BY A.eqpmaint_code ORDER BY B.eqpmaideta_code) = 1 THEN A.eqpmaint_code
                            ELSE ''END AS '保养标准编码',
                            CASE WHEN
                            ROW_NUMBER() OVER (PARTITION BY A.eqpmaint_code ORDER BY B.eqpmaideta_code) = 1 THEN M.name
                            ELSE ''END AS '保养标准名称',
                            CASE WHEN
                            ROW_NUMBER() OVER (PARTITION BY A.maint_cyc ORDER BY B.eqpmaideta_code) = 1 THEN A.maint_cyc
                            ELSE ''END AS '保养周期',
                            CASE WHEN
                            ROW_NUMBER() OVER (PARTITION BY A.maint_user ORDER BY B.eqpmaideta_code) = 1 THEN A.maint_user
                            ELSE ''END AS '保养人员',
                            CASE WHEN
                            ROW_NUMBER() OVER (PARTITION BY A.maint_result ORDER BY B.eqpmaideta_code) = 1 THEN A.maint_result
                            ELSE ''END AS '保养结果',
                            CASE WHEN
                            ROW_NUMBER() OVER (PARTITION BY A.maint_date ORDER BY B.eqpmaideta_code) = 1 THEN A.maint_date
                            END AS '保养时间',
                            B.seq as '保养部位序号',H.code '保养部位编码',H.name '保养部位名称',B.result '保养结果',B.maint_value '数值',B.remark '备注'
                            from TEqpmaint_Proc_Main A
                            left join TEqpmaint_Proc_Deta B on A.bywo=B.bywo
                            left join TEqpInfo E on A.eqp_code=E.code
                            left join TEqpmai_Main M on A.eqpmaint_code=M.code
                            left join TOrganization T on E.wksp_code=T.org_code
                            left join TEqpmai_Item H on B.eqpmaideta_code=H.code
                            where T.description='W' " + search;
                DataTable data = DapperHelper.selectdata(sql, dynamicParams);
                data.TableName = "Table"; //设置DataTable的名称
                string msg = DownLoad.DataTableToExcel(data, "工序检验记录");
                mes.code = "200";
                mes.Message = "查询成功!";
                mes.count = total;
                mes.data = msg;
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion
    }
}