点检记录导出接口报错优化、不良明细查询接口优化 安灯系统PC端接口开发

yl

2022-09-22 59d429fd2caa1ecb879abfd21e9be17d42a39ee0

 VueWebApi/DLL/DAL/DeviceManagerDAL.cs

@@ -673,7 +673,7 @@
                }
                if (checkdescr != "" && checkdescr != null)
                {
                    search += "and A.description=@checkdescr ";
                    search += "and A.description like '%'+@checkdescr+'%' ";
                    dynamicParams.Add("@checkdescr", checkdescr);
                }
                if (isqrcode != "" && isqrcode != null)
@@ -862,7 +862,7 @@
                }
                if (maidescr != "" && maidescr != null)
                {
                    search += "and A.description=@maidescr ";
                    search += "and A.description like '%'+@maidescr+'%' ";
                    dynamicParams.Add("@maidescr", maidescr);
                }
                if (isqrcode != "" && isqrcode != null)
@@ -907,7 +907,7 @@
                if (operType == "Add")
                {
                    var sql = @"insert into TEqpmai_Item(code,name,description,isscan,lm_user,lm_date) 
                              values(@maiitemcode,@maiitemname,@checkitemdescr,@isqrcode,@usercode,@CreateDate)";
                              values(@maiitemcode,@maiitemname,@maiitemdescr,@isqrcode,@usercode,@CreateDate)";
                    dynamicParams.Add("@maiitemcode", maiitemcode);
                    dynamicParams.Add("@maiitemname", maiitemname);
                    dynamicParams.Add("@maiitemdescr", maiitemdescr);
@@ -1034,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 == "")
@@ -1054,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 = "查询成功!";
@@ -1470,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);
                }
                
@@ -1491,8 +1493,10 @@
                //search = search.Substring(3);//截取索引2后面的字符
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select id,code,name,description,is_repaireqp,main_cycle,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 = "查询成功!";
@@ -1542,7 +1546,7 @@
            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);
@@ -1564,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);
@@ -1597,7 +1601,7 @@
                if (opertype == "Add")
                {
                    //新增设备保养标准主表
                    sql = @"insert into TEqpmai_Main(code,name,description,main_cycle,lm_user,lm_date) values(@code,@name,@descr,@lm_user,@lm_date)";
                    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++)
                    {
@@ -1894,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
    }
}