经信局公文批办查询列表接口修改

houlin · 发表于 2022-6-23 09:21:38

本帖最后由 houlin 于 2022-6-23 09:25 编辑

思路: 将关联表的sql提取出来,先对主表数据进行分页,再把分页的数据和表进行关联查询
因字数限制,这块只放修改后代码
代码如下

/**
* 查询文件批办列表数据
* @param req
* @param response
* @param login
* @param findall
* @param time
* @param cmdType 空--查询列表 export--导出列表
* @return
*/
public static HashMap<String,Object> getList(HttpServletRequest req, HttpServletResponse response, LoginModel login, String findall, String time,String cmdType){
List list = new ArrayList<HashMap<String, String>>();
HashMap<String,Object> map = new HashMap<String,Object>();
JSONObject json = new JSONObject();
try {
DatabaseBo dbo = new DatabaseBo();
String usercode = login.getUserCode();
String roles[] = login.getRoles();
String username = login.getUserName();
List listC14 = Arrays.asList(roles);
//获取页码
String page = req.getParameter("page");
if(page == null || "".equals(page)){
page = "1";
}
//定义每页的长度
int pageSize = 10;
//定义当前页的第一位id
int count = (Integer.parseInt(page)-1)*pageSize+1;
//定义当前页的最后一位id
int endCount = (Integer.parseInt(page)-1)*pageSize+pageSize;
//获取文件类型
String titlelike = req.getParameter("titlelike");
//获取标题、姓名、发文单位
String find = req.getParameter("find");
//获取是否办理或批办状态
String ystatus = req.getParameter("ystatus");
//2020/11/13 田思斌增加按发文单位查询
//获取发文单位
String unit = req.getParameter("unit");
//定义查询条件
String findstr = "";
//定义文件类型查询条件
String titlelikestr = "";
//定义根据批办状态查询条件
String statustr = "";
//定义根据发文单位查询条件
String unitstr = "";
//wangjiajia 2020.9.23
//排序方式
String desctype = req.getParameter("desctype");
System.out.println("获取排序方式=============："+desctype);
String desctypesql = "";
if (!"".equals(desctype) && desctype != null){
if("0".equals(desctype)) //按照发布时间排序
desctypesql += " FASONGTIME desc,";
else //按照办结时间排序
desctypesql += " finishtime1 desc,";;
}
//wangjiajia 2020.9.24
//新增发布时间，办结时间按钮
String fasongstarttime = req.getParameter("fasongstarttime");
String fasongendtime = req.getParameter("fasongendtime");
String fasongsql="";
//发布时间
if(fasongstarttime!=null&&!"".equals(fasongstarttime))
fasongsql+=" and fasongtime>='"+fasongstarttime+"'";
if(fasongendtime!=null&&!"".equals(fasongendtime))
fasongsql+=" and fasongtime<='"+fasongendtime+"'";
//办结时间
String finishsql="";
String finishstarttime = req.getParameter("finishstarttime");
String finishendtime = req.getParameter("finishendtime");
if(finishstarttime!=null&&!"".equals(finishstarttime))
fasongsql+=" and finishtime1>='"+finishstarttime+"'";
if(finishendtime!=null&&!"".equals(finishendtime))
fasongsql+=" and finishtime1<='"+finishendtime+"'";
if(find != null && !"".equals(find)){//根据标题进行搜索 OR a.SPONSORNAME LIKE '%"+find+"%' OR a.TIME LIKE '%"+find+"%'
findstr += "and (a.TITLE LIKE '%"+find +"%')";
}
if(unit != null && !"".equals(unit)){//根据标题进行搜索 OR a.SPONSORNAME LIKE '%"+find+"%' OR a.TIME LIKE '%"+find+"%'
unitstr += "and (a.TIME LIKE '%"+unit +"%')";
}
if(titlelike != null && !"".equals(titlelike)){//根据文件类型进行搜索
titlelikestr += " and a.WENJIAN_TYPE = '"+titlelike+"'";
}
if(ystatus != null && !"".equals(ystatus)){
statustr += " and b.BANJIE = '"+ystatus+"'";
}
//定义分页
String pageStr = "";
if(time == null || "".equals(time)){//WHERE RN BETWEEN "+count+" AND "+endCount
pageStr = "WHERE p.RN BETWEEN "+count+" AND "+endCount;
}
//获取是否为收藏列表
String collect = req.getParameter("collect");
System.out.println("获取是否为收藏列表："+collect);
//定义收藏条件
String collectStr = "";
if(collect != null && !"".equals(collect)){//为收藏列表
collectStr = " and collectwk = '1' ";
}
//导出查询(查询)
/*String updateExcel = "(select wm_concat(REVICENAME) from OA_WORKINFO n where n.wid=a.ID and n.ISREAD='1' and n.ISDELIVERY='1') read,\n" +
" (select wm_concat(REVICENAME) from OA_WORKINFO n where n.wid=a.ID and n.ISREAD='0' and n.ISDELIVERY='1') unread,\n " +
" (select wm_concat(idea) from OA_WORKINFO n where n.wid=a.ID ) opinion,\n" +
" (SELECT DISTINCT\n" +
" CASE\n" +
" WHEN (SELECT COUNT(*) FROM OA_NEWWORK_FINISHTIME WHERE FINISHTIME>=(SELECT TO_CHAR(sysdate,'yyyy-mm-dd') FROM dual) AND rid=a.ID) >0\n" +
" THEN (SELECT min(FINISHTIME) FROM OA_NEWWORK_FINISHTIME WHERE FINISHTIME>=(SELECT TO_CHAR(sysdate,'yyyy-mm-dd') FROM dual) AND rid=a.ID)\n" +
" when (SELECT COUNT(*) FROM OA_NEWWORK_FINISHTIME WHERE FINISHTIME>=(SELECT TO_CHAR(sysdate,'yyyy-mm-dd') FROM dual) AND rid=a.ID) <=0\n" +
" then (SELECT max(FINISHTIME) FROM OA_NEWWORK_FINISHTIME WHERE rid=a.ID)\n" +
" else ''\n" +
" END FINISHTIME\n" +
" FROM OA_NEWWORK_FINISHTIME\n" +
" WHERE rid=a.ID) finishtime1,\n" +
" (select wm_concat(F.FINISHTIME) from OA_NEWWORK_FINISHTIME f where f.rid=a.ID ) FINISHTIME,isjinji WENJIAN_STATUS,";*/
String updateExcel1 = "r.FINISHTIME finishtime1,isjinji WENJIAN_STATUS,";
String updateExcel2 = " m.read,n.unread,o.opinion,q.FINISHTIME ";
String addsql1 = " left join " +
" (" +
" select case when uu.count>0 then vv.FINISHTIME_min when uu.count<=0 then ww.FINISHTIME_max else '' end FINISHTIME,uu.rid " +
" from ( select count(*) count,rid from OA_NEWWORK_FINISHTIME WHERE rid >=0 group by rid ) uu " +
" left join (SELECT MIN(FINISHTIME) FINISHTIME_min,rid FROM OA_NEWWORK_FINISHTIME WHERE rid >=0 group by rid) vv on uu.rid = vv.rid " +
" left join (SELECT MAX(FINISHTIME) FINISHTIME_max,rid FROM OA_NEWWORK_FINISHTIME WHERE rid >=0 group by rid) ww on uu.rid = ww.rid" +
" ) r on a.id = r.rid ";
String addsql2 = " left join (select wm_concat(REVICENAME) read,wid from OA_WORKINFO WHERE ISREAD ='1' AND ISDELIVERY='1' and wid >=0 group by wid ) m on a.aid = m.wid " +
" left join (select wm_concat(REVICENAME) unread,wid from OA_WORKINFO WHERE ISREAD ='0' AND ISDELIVERY='1' and wid >=0 group by wid ) n on a.aid = n.wid " +
" left join (select wm_concat(idea) opinion,wid from OA_WORKINFO WHERE wid >=0 group by wid ) o on a.aid = o.wid " +
" left join (select wm_concat(FINISHTIME) FINISHTIME,rid from OA_NEWWORK_FINISHTIME WHERE rid >=0 group by rid ) q on a.aid = q.rid " ;
//定义列表总数量
String num ="0";
//查询当前登陆人是否属于办公室科员/办公室科长
List<HashMap<String,String>> molist = ModuleGrantUtil.getModuleGrantByUsercode(usercode,"workinform","workadd","getList");
//a03-所有主管领导 molist-办公室所有人 a02-副局长 a01-局长 a42--公文批办特殊发布人员--杨帆
if (listC14.contains("a03") || (null != molist && molist.size() > 0) || listC14.contains("a02") || listC14.contains("a01")|| listC14.contains("a42")) {
//查看所有
String sql = "";
//如果是按办结时间排序，办结时间为空的不显示 desctypesql-排序方式
if(desctypesql.equals(" finishtime1 desc,")){
if ((find != null && !"".equals(find)) && (unit != null && !"".equals(unit))) {//主办单位或者直接搜索
sql = (new StringBuilder(
"select * from (select rownum AS rn,t.* from (select * from(SELECT a.ID aid," + updateExcel1 + "a.FASONGTIME,a.TIME,a.TITLE,a.BIGEVENT,a.SPONSORNAME,APPROVALSTATUS,CASE WHEN A.SPONSORCODE = '" + usercode + "' THEN '0' ELSE '1' END LOGINSTATU,CASE WHEN APPROVALSTATUS = '3' THEN '已终止' WHEN APPROVALSTATUS = '2' THEN '已驳回' ELSE A.STATE END STATE,a.ISSECRETY,a.WENJIAN_TYPE FROM OA_NEWWORK a "+addsql1+" WHERE 1=1 and submit = '1' " + findstr + unitstr))
.append(" ORDER BY " + desctypesql + "ID DESC) c left join (select * from oa_workcollect where cname='")
.append(usercode).append("')z on c.aid=z.zid order by " + desctypesql + " c.aid desc) t where 1=1 and FINISHTIME1 is not null " + fasongsql + " " + finishsql + " " + collectStr + ") p ").toString();
} else if ((find != null && !"".equals(find)) || (titlelike != null && !"".equals(titlelike))) { //根据标题搜索排序
sql = (new StringBuilder(
"select * from (select rownum AS rn,t.* from (select * from(SELECT a.ID aid," + updateExcel1 + "a.FASONGTIME,a.TIME,a.TITLE,a.BIGEVENT,a.SPONSORNAME,APPROVALSTATUS,CASE WHEN A.SPONSORCODE = '" + usercode + "' THEN '0' ELSE '1' END LOGINSTATU,CASE WHEN APPROVALSTATUS = '3' THEN '已终止' WHEN APPROVALSTATUS = '2' THEN '已驳回' ELSE A.STATE END STATE,a.ISSECRETY,a.WENJIAN_TYPE FROM OA_NEWWORK a "+addsql1+" WHERE 1=1 and submit = '1' " + findstr + titlelikestr))
.append(" ORDER BY " + desctypesql + "ID DESC) c left join (select * from oa_workcollect where cname='")
.append(usercode).append("')z on c.aid=z.zid order by " + desctypesql + " c.aid desc) t where 1=1 and FINISHTIME1 is not null " + fasongsql + " " + finishsql + " " + collectStr + ") p ").toString();
} else if ((unit != null && !"".equals(unit)) || (titlelike != null && !"".equals(titlelike))) {//标题或主办单位选择
sql = (new StringBuilder(
"select * from (select rownum AS rn,t.* from (select * from(SELECT a.ID aid," + updateExcel1 + "a.FASONGTIME,a.TIME,a.TITLE,a.BIGEVENT,a.SPONSORNAME,APPROVALSTATUS,CASE WHEN A.SPONSORCODE = '" + usercode + "' THEN '0' ELSE '1' END LOGINSTATU,CASE WHEN APPROVALSTATUS = '3' THEN '已终止' WHEN APPROVALSTATUS = '2' THEN '已驳回' ELSE A.STATE END STATE,a.ISSECRETY,a.WENJIAN_TYPE FROM OA_NEWWORK a "+addsql1+" WHERE 1=1 and submit = '1' " + unitstr + titlelikestr))
.append(" ORDER BY " + desctypesql + "ID DESC) c left join (select * from oa_workcollect where cname='")
.append(usercode).append("')z on c.aid=z.zid order by " + desctypesql + " c.aid desc) t where 1=1 and FINISHTIME1 is not null " + fasongsql + " " + finishsql + " " + collectStr + ") p ").toString();
} else if (!"".equals(findall) && findall != null) { //全选搜索
sql = (new StringBuilder(
"select * from (select rownum AS rn,t.* from (select * from(SELECT ID aid," + updateExcel1 + "FASONGTIME,TIME,TITLE,BIGEVENT,SPONSORNAME,APPROVALSTATUS,CASE WHEN SPONSORCODE = '" + usercode + "' THEN '0' ELSE '1' END LOGINSTATU,CASE WHEN APPROVALSTATUS = '3' THEN '已终止' WHEN APPROVALSTATUS = '2' THEN '已驳回' ELSE STATE END STATE,ISSECRETY FROM OA_NEWWORK a "+addsql1+" where submit = '1' ORDER BY ID DESC) c left join (select * from oa_workcollect where cname='"))
.append(usercode).append("')z on c.aid=z.zid order by " + desctypesql + "c.aid desc) t where 1=1 and FINISHTIME1 is not null " + fasongsql + " " + finishsql + " " + collectStr + ") p ").toString();
} else {
sql = (new StringBuilder(
"select * from (SELECT x.*, ROWNUM RN FROM(select * from(SELECT ID aid," + updateExcel1 + "FASONGTIME,TIME,TITLE,BIGEVENT,SPONSORNAME,APPROVALSTATUS,CASE WHEN SPONSORCODE = '" + usercode + "' THEN '0' ELSE '1' END LOGINSTATU,CASE WHEN APPROVALSTATUS = '3' THEN '已终止' WHEN APPROVALSTATUS = '2' THEN '已驳回' ELSE STATE END STATE,ISSECRETY,WENJIAN_TYPE,DIGEST FROM OA_NEWWORK a "+addsql1+" where submit = '1' ORDER BY " + desctypesql + "ID DESC) c left join (select * from oa_workcollect where cname='"))
.append(usercode).append("')z on c.aid=z.zid order by " + desctypesql + " c.aid desc) x where 1=1 and FINISHTIME1 is not null " + fasongsql + " " + finishsql + " " + collectStr + time + ") p ")
.toString();
}
}else {
if ((find != null && !"".equals(find)) && (unit != null && !"".equals(unit))) { //搜索和主办单位
sql = (new StringBuilder(
"select * from (select rownum AS rn,t.* from (select * from(SELECT a.ID aid," + updateExcel1 + "a.FASONGTIME,a.TIME,a.TITLE,a.BIGEVENT,a.SPONSORNAME,APPROVALSTATUS,CASE WHEN A.SPONSORCODE = '" + usercode + "' THEN '0' ELSE '1' END LOGINSTATU,CASE WHEN APPROVALSTATUS = '3' THEN '已终止' WHEN APPROVALSTATUS = '2' THEN '已驳回' ELSE A.STATE END STATE,a.ISSECRETY,a.WENJIAN_TYPE FROM OA_NEWWORK a "+addsql1+" WHERE 1=1 and submit = '1' " + findstr + unitstr))
.append(" ORDER BY " + desctypesql + "ID DESC) c left join (select * from oa_workcollect where cname='")
.append(usercode).append("')z on c.aid=z.zid order by " + desctypesql + " c.aid desc) t where 1=1 " + fasongsql + " " + finishsql + " " + collectStr + ") p ").toString();
} else if ((find != null && !"".equals(find)) || (titlelike != null && !"".equals(titlelike))) {//搜索或者标题选择
sql = (new StringBuilder(
"select * from (select rownum AS rn,t.* from (select * from(SELECT a.ID aid," + updateExcel1 + "a.FASONGTIME,a.TIME,a.TITLE,a.BIGEVENT,a.SPONSORNAME,APPROVALSTATUS,CASE WHEN A.SPONSORCODE = '" + usercode + "' THEN '0' ELSE '1' END LOGINSTATU,CASE WHEN APPROVALSTATUS = '3' THEN '已终止' WHEN APPROVALSTATUS = '2' THEN '已驳回' ELSE A.STATE END STATE,a.ISSECRETY,a.WENJIAN_TYPE FROM OA_NEWWORK a "+addsql1+" WHERE 1=1 and submit = '1' " + findstr + titlelikestr))
.append(" ORDER BY " + desctypesql + "ID DESC) c left join (select * from oa_workcollect where cname='")
.append(usercode).append("')z on c.aid=z.zid order by " + desctypesql + " c.aid desc) t where 1=1 " + fasongsql + " " + finishsql + " " + collectStr + ") p ").toString();
} else if ((unit != null && !"".equals(unit)) || (titlelike != null && !"".equals(titlelike))) { //根据主办单位和标题选择
sql = (new StringBuilder(
"select * from (select rownum AS rn,t.* from (select * from(SELECT a.ID aid," + updateExcel1 + "a.FASONGTIME,a.TIME,a.TITLE,a.BIGEVENT,a.SPONSORNAME,APPROVALSTATUS,CASE WHEN A.SPONSORCODE = '" + usercode + "' THEN '0' ELSE '1' END LOGINSTATU,CASE WHEN APPROVALSTATUS = '3' THEN '已终止' WHEN APPROVALSTATUS = '2' THEN '已驳回' ELSE A.STATE END STATE,a.ISSECRETY,a.WENJIAN_TYPE FROM OA_NEWWORK a "+addsql1+" WHERE 1=1 and submit = '1' " + unitstr + titlelikestr))
.append(" ORDER BY " + desctypesql + "ID DESC) c left join (select * from oa_workcollect where cname='")
.append(usercode).append("')z on c.aid=z.zid order by " + desctypesql + " c.aid desc) t where 1=1 " + fasongsql + " " + finishsql + " " + collectStr + ") p ").toString();
} else if (!"".equals(findall) && findall != null) { //全选
sql = (new StringBuilder(
"select * from (select rownum AS rn,t.* from (select * from(SELECT ID aid," + updateExcel1 + "FASONGTIME,TIME,TITLE,BIGEVENT,SPONSORNAME,APPROVALSTATUS,CASE WHEN SPONSORCODE = '" + usercode + "' THEN '0' ELSE '1' END LOGINSTATU,CASE WHEN APPROVALSTATUS = '3' THEN '已终止' WHEN APPROVALSTATUS = '2' THEN '已驳回' ELSE STATE END STATE,ISSECRETY FROM OA_NEWWORK a "+addsql1+" where submit = '1' ORDER BY ID DESC) c left join (select * from oa_workcollect where cname='"))
.append(usercode).append("')z on c.aid=z.zid order by " + desctypesql + "c.aid desc) t where 1=1 " + fasongsql + " " + finishsql + " " + collectStr + ") p ").toString();
} else {
sql = (new StringBuilder(
"select * from (SELECT x.*, ROWNUM RN FROM(select * from(SELECT ID aid," + updateExcel1 + "FASONGTIME,TIME,TITLE,BIGEVENT,SPONSORNAME,APPROVALSTATUS,CASE WHEN SPONSORCODE = '" + usercode + "' THEN '0' ELSE '1' END LOGINSTATU,CASE WHEN APPROVALSTATUS = '3' THEN '已终止' WHEN APPROVALSTATUS = '2' THEN '已驳回' ELSE STATE END STATE,ISSECRETY,WENJIAN_TYPE,DIGEST FROM OA_NEWWORK a "+addsql1+" where submit = '1' ORDER BY " + desctypesql + "ID DESC) c left join (select * from oa_workcollect where cname='"))
.append(usercode).append("')z on c.aid=z.zid order by " + desctypesql + " c.aid desc) x where 1=1 " + fasongsql + " " + finishsql + " " + collectStr + time + ") p ")
.toString();
}
}
String count_sql = sql.replaceFirst("\\*", "count(aid) num");
num = dbo.prepareQuery(count_sql,null).get(0).get("NUM");
String finil_sql = "SELECT a.* "+" from ("+sql+pageStr+") a ";
if("export".equals(cmdType)){
finil_sql = "SELECT a.*,"+updateExcel2 +" from ("+sql+pageStr+") a "+ addsql2;
}
list = dbo.prepareQuery(finil_sql, null);
} else {//查看自己相关
System.out.println("排序顺序是：========="+desctypesql);
String selectpishi = "";
System.out.println(desctypesql+"3333");
if(desctypesql.equals(" finishtime1 desc,")){
if ((find != null && !"".equals(find)) && (unit != null && !"".equals(unit))) {
selectpishi = (new StringBuilder(
"select * from (select rownum AS rn,t.* from (select * from(select a.ID aid," + updateExcel1 + "a.FASONGTIME,a.TIME,a.TITLE,a.BIGEVENT,b.BANJIE,a.SPONSORNAME,b.iscollect,APPROVALSTATUS,CASE WHEN SPONSORCODE = '" + usercode + "' THEN '0' ELSE '1' END LOGINSTATU,CASE WHEN APPROVALSTATUS = '3' THEN '已终止' WHEN APPROVALSTATUS = '2' THEN '已驳回' ELSE A.STATE END STATE,a.ISSECRETY,a.WENJIAN_TYPE from oa_newwork a left join oa_workinfo b on a.id=b.wid "+addsql1+" where a.submit = '1' AND (A.APPROVALSTATUS = '1' or A.APPROVALSTATUS = '9' or A.APPROVALSTATUS = '10') " + statustr + findstr + unitstr + " and b.revicecode='"))
.append(usercode)
.append("' order by " + desctypesql + "a.id desc) c left join (select * from oa_workcollect where cname='")
.append(usercode).append("' )z on c.aid=z.zid order by " + desctypesql + " c.aid desc) t where 1=1 and FINISHTIME1 is not null " + fasongsql + " " + finishsql + " " + collectStr + ") p ").toString();
} else if ((find != null && !"".equals(find)) || (titlelike != null && !"".equals(titlelike))) {
selectpishi = (new StringBuilder(
"select * from (select rownum AS rn,t.* from (select * from(select a.ID aid," + updateExcel1 + "a.FASONGTIME,a.TIME,a.TITLE,a.BIGEVENT,b.BANJIE,a.SPONSORNAME,b.iscollect,APPROVALSTATUS,CASE WHEN SPONSORCODE = '" + usercode + "' THEN '0' ELSE '1' END LOGINSTATU,CASE WHEN APPROVALSTATUS = '3' THEN '已终止' WHEN APPROVALSTATUS = '2' THEN '已驳回' ELSE A.STATE END STATE,a.ISSECRETY,a.WENJIAN_TYPE from oa_newwork a left join oa_workinfo b on a.id=b.wid "+addsql1+" where a.submit = '1' AND (A.APPROVALSTATUS = '1' or A.APPROVALSTATUS = '9' or A.APPROVALSTATUS = '10') " + statustr + findstr + titlelikestr + " and b.revicecode='"))
.append(usercode)
.append("' order by " + desctypesql + "a.id desc) c left join (select * from oa_workcollect where cname='")
.append(usercode).append("' )z on c.aid=z.zid order by " + desctypesql + " c.aid desc) t where 1=1 and FINISHTIME1 is not null " + fasongsql + " " + finishsql + " " + collectStr + ") p ").toString();
} else if ((unit != null && !"".equals(unit)) || (titlelike != null && !"".equals(titlelike))) {
selectpishi = (new StringBuilder(
"select * from (select rownum AS rn,t.* from (select * from(select a.ID aid," + updateExcel1 + "a.FASONGTIME,a.TIME,a.TITLE,a.BIGEVENT,b.BANJIE,a.SPONSORNAME,b.iscollect,APPROVALSTATUS,CASE WHEN SPONSORCODE = '" + usercode + "' THEN '0' ELSE '1' END LOGINSTATU,CASE WHEN APPROVALSTATUS = '3' THEN '已终止' WHEN APPROVALSTATUS = '2' THEN '已驳回' ELSE A.STATE END STATE,a.ISSECRETY,a.WENJIAN_TYPE from oa_newwork a left join oa_workinfo b on a.id=b.wid "+addsql1+" where a.submit = '1' AND (A.APPROVALSTATUS = '1' or A.APPROVALSTATUS = '9' or A.APPROVALSTATUS = '10') " + statustr + unitstr + titlelikestr + " and b.revicecode='"))
.append(usercode)
.append("' order by " + desctypesql + "a.id desc) c left join (select * from oa_workcollect where cname='")
.append(usercode).append("' )z on c.aid=z.zid order by " + desctypesql + " c.aid desc) t where 1=1 and FINISHTIME1 is not null " + fasongsql + " " + finishsql + " " + collectStr + ") p ").toString();
} else if (!"".equals(findall) && findall != null) {
selectpishi = (new StringBuilder(
"select * from (select rownum AS rn,t.* from (select * from(select a.ID aid," + updateExcel1 + "a.FASONGTIME,a.TIME,a.TITLE,a.BIGEVENT,b.BANJIE,a.SPONSORNAME,b.iscollect,APPROVALSTATUS,CASE WHEN SPONSORCODE = '" + usercode + "' THEN '0' ELSE '1' END LOGINSTATU,CASE WHEN APPROVALSTATUS = '3' THEN '已终止' WHEN APPROVALSTATUS = '2' THEN '已驳回' ELSE A.STATE END STATE,a.ISSECRETY from oa_newwork a left join oa_workinfo b on a.id=b.wid "+addsql1+" where a.submit = '1' AND (A.APPROVALSTATUS = '1' or A.APPROVALSTATUS = '9' or A.APPROVALSTATUS = '10') +" + statustr + " and b.revicecode='"))
.append(usercode)
.append("' order by " + desctypesql + "a.id desc) c left join (select * from oa_workcollect where cname='")
.append(usercode).append("' )z on c.aid=z.zid order by " + desctypesql + " c.aid desc) t where 1=1 and FINISHTIME1 is not null " + fasongsql + " " + finishsql + " " + collectStr + ") p ").toString();
} else {
selectpishi = (new StringBuilder(
"SELECT * FROM (SELECT x.*, ROWNUM RN FROM(select * from(select a.ID aid," + updateExcel1 + "a.FASONGTIME,a.TIME,a.TITLE,a.BIGEVENT,b.BANJIE,a.SPONSORNAME,b.iscollect,APPROVALSTATUS,CASE WHEN SPONSORCODE = '" + usercode + "' THEN '0' ELSE '1' END LOGINSTATU,CASE WHEN APPROVALSTATUS = '3' THEN '已终止' WHEN APPROVALSTATUS = '2' THEN '已驳回' ELSE A.STATE END STATE,a.ISSECRETY,a.WENJIAN_TYPE,a.DIGEST from oa_newwork a left join oa_workinfo b on a.id=b.wid "+addsql1+" where a.submit = '1' AND (A.APPROVALSTATUS = '1' or A.APPROVALSTATUS = '9' or A.APPROVALSTATUS = '10') " + statustr + " and b.revicecode='"))
.append(usercode)
.append("' order by " + desctypesql + "a.id desc) c left join (select * from oa_workcollect where cname='")
.append(usercode).append("' )z on c.aid=z.zid order by " + desctypesql + " c.aid desc ) x where 1=1 and FINISHTIME1 is not null " + fasongsql + " " + finishsql + " " + collectStr + time + ") p ")
.toString();
}
}
else {
if ((find != null && !"".equals(find)) && (unit != null && !"".equals(unit))) {
selectpishi = (new StringBuilder(
"select * from (select rownum AS rn,t.* from (select * from(select a.ID aid," + updateExcel1 + "a.FASONGTIME,a.TIME,a.TITLE,a.BIGEVENT,b.BANJIE,a.SPONSORNAME,b.iscollect,APPROVALSTATUS,CASE WHEN SPONSORCODE = '" + usercode + "' THEN '0' ELSE '1' END LOGINSTATU,CASE WHEN APPROVALSTATUS = '3' THEN '已终止' WHEN APPROVALSTATUS = '2' THEN '已驳回' ELSE A.STATE END STATE,a.ISSECRETY,a.WENJIAN_TYPE from oa_newwork a left join oa_workinfo b on a.id=b.wid "+addsql1+" where a.submit = '1' AND (A.APPROVALSTATUS = '1' or A.APPROVALSTATUS = '9' or A.APPROVALSTATUS = '10') " + statustr + findstr + unitstr + " and b.revicecode='"))
.append(usercode)
.append("' order by " + desctypesql + "a.id desc) c left join (select * from oa_workcollect where cname='")
.append(usercode).append("' )z on c.aid=z.zid order by " + desctypesql + " c.aid desc) t where 1=1 " + fasongsql + " " + finishsql + " " + collectStr + ") p ").toString();
} else if ((find != null && !"".equals(find)) || (titlelike != null && !"".equals(titlelike))) {
selectpishi = (new StringBuilder(
"select * from (select rownum AS rn,t.* from (select * from(select a.ID aid," + updateExcel1 + "a.FASONGTIME,a.TIME,a.TITLE,a.BIGEVENT,b.BANJIE,a.SPONSORNAME,b.iscollect,APPROVALSTATUS,CASE WHEN SPONSORCODE = '" + usercode + "' THEN '0' ELSE '1' END LOGINSTATU,CASE WHEN APPROVALSTATUS = '3' THEN '已终止' WHEN APPROVALSTATUS = '2' THEN '已驳回' ELSE A.STATE END STATE,a.ISSECRETY,a.WENJIAN_TYPE from oa_newwork a left join oa_workinfo b on a.id=b.wid "+addsql1+" where a.submit = '1' AND (A.APPROVALSTATUS = '1' or A.APPROVALSTATUS = '9' or A.APPROVALSTATUS = '10') " + statustr + findstr + titlelikestr + " and b.revicecode='"))
.append(usercode)
.append("' order by " + desctypesql + "a.id desc) c left join (select * from oa_workcollect where cname='")
.append(usercode).append("' )z on c.aid=z.zid order by " + desctypesql + " c.aid desc) t where 1=1 " + fasongsql + " " + finishsql + " " + collectStr + ") p ").toString();
} else if ((unit != null && !"".equals(unit)) || (titlelike != null && !"".equals(titlelike))) {
selectpishi = (new StringBuilder(
"select * from (select rownum AS rn,t.* from (select * from(select a.ID aid," + updateExcel1 + "a.FASONGTIME,a.TIME,a.TITLE,a.BIGEVENT,b.BANJIE,a.SPONSORNAME,b.iscollect,APPROVALSTATUS,CASE WHEN SPONSORCODE = '" + usercode + "' THEN '0' ELSE '1' END LOGINSTATU,CASE WHEN APPROVALSTATUS = '3' THEN '已终止' WHEN APPROVALSTATUS = '2' THEN '已驳回' ELSE A.STATE END STATE,a.ISSECRETY,a.WENJIAN_TYPE from oa_newwork a left join oa_workinfo b on a.id=b.wid "+addsql1+" where a.submit = '1' AND (A.APPROVALSTATUS = '1' or A.APPROVALSTATUS = '9' or A.APPROVALSTATUS = '10') " + statustr + unitstr + titlelikestr + " and b.revicecode='"))
.append(usercode)
.append("' order by " + desctypesql + "a.id desc) c left join (select * from oa_workcollect where cname='")
.append(usercode).append("' )z on c.aid=z.zid order by " + desctypesql + " c.aid desc) t where 1=1 " + fasongsql + " " + finishsql + " " + collectStr + ") p ").toString();
} else if (!"".equals(findall) && findall != null) {
selectpishi = (new StringBuilder(
"select * from (select rownum AS rn,t.* from (select * from(select a.ID aid," + updateExcel1 + "a.FASONGTIME,a.TIME,a.TITLE,a.BIGEVENT,b.BANJIE,a.SPONSORNAME,b.iscollect,APPROVALSTATUS,CASE WHEN SPONSORCODE = '" + usercode + "' THEN '0' ELSE '1' END LOGINSTATU,CASE WHEN APPROVALSTATUS = '3' THEN '已终止' WHEN APPROVALSTATUS = '2' THEN '已驳回' ELSE A.STATE END STATE,a.ISSECRETY from oa_newwork a left join oa_workinfo b on a.id=b.wid "+addsql1+" where a.submit = '1' AND (A.APPROVALSTATUS = '1' or A.APPROVALSTATUS = '9' or A.APPROVALSTATUS = '10') +" + statustr + " and b.revicecode='"))
.append(usercode)
.append("' order by " + desctypesql + "a.id desc) c left join (select * from oa_workcollect where cname='")
.append(usercode).append("' )z on c.aid=z.zid order by " + desctypesql + " c.aid desc) t where 1=1 " + fasongsql + " " + finishsql + " " + collectStr + ") p ").toString();
} else {
selectpishi = (new StringBuilder(
"SELECT * FROM (SELECT x.*, ROWNUM RN FROM(select * from(select a.ID aid," + updateExcel1 + "a.FASONGTIME,a.TIME,a.TITLE,a.BIGEVENT,b.BANJIE,a.SPONSORNAME,b.iscollect,APPROVALSTATUS,CASE WHEN SPONSORCODE = '" + usercode + "' THEN '0' ELSE '1' END LOGINSTATU,CASE WHEN APPROVALSTATUS = '3' THEN '已终止' WHEN APPROVALSTATUS = '2' THEN '已驳回' ELSE A.STATE END STATE,a.ISSECRETY,a.WENJIAN_TYPE,a.DIGEST from oa_newwork a left join oa_workinfo b on a.id=b.wid "+addsql1+" where a.submit = '1' AND (A.APPROVALSTATUS = '1' or A.APPROVALSTATUS = '9' or A.APPROVALSTATUS = '10') " + statustr + " and b.revicecode='"))
.append(usercode)
.append("' order by " + desctypesql + "a.id desc) c left join (select * from oa_workcollect where cname='")
.append(usercode).append("' )z on c.aid=z.zid order by " + desctypesql + " c.aid desc ) x where 1=1 " + fasongsql + " " + finishsql + " " + collectStr + time + ") p ")
.toString();
}
}
String count_sql = selectpishi.replaceFirst("\\*", "count(aid) num");
num = dbo.prepareQuery(count_sql,null).get(0).get("NUM");
String finil_sql = "SELECT a.* " +" from ("+selectpishi+pageStr+") a ";
if("export".equals(cmdType)){
finil_sql = "SELECT a.*,"+updateExcel2 +" from ("+selectpishi+pageStr+") a "+ addsql2;
}
list = dbo.prepareQuery(finil_sql, null);
}
map.put("num",num);
map.put("list",list);
}catch (Exception e){
e.printStackTrace();
}
return map;
}

复制代码

娄元杰 · 发表于 2022-6-23 09:34:07

此项问题是经信局公文批办列表查询缓慢，未修改前加载需要10多秒，修改后在1秒内完成加载。

朱君君 · 发表于 2022-6-27 15:42:03

真长

		自动登录	找回密码
密码			立即注册

经信局公文批办查询列表接口修改

点评