GetCoworkListSql.jsp
9.79 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
<%@ page language="java" contentType="text/html; charset=UTF-8" %>
<%@ page import="weaver.general.Util,weaver.docs.docs.CustomFieldManager" %>
<%@ page import="java.util.*" %>
<%@ page import="weaver.general.GCONST" %>
<%@ page import="weaver.general.IsGovProj" %>
<%@ page import="weaver.general.BaseBean" %>
<%@page import="weaver.cowork.CoworkItemMarkOperation"%>
<%@page import="weaver.cowork.CoworkLabelVO"%>
<%@page import="java.net.URLDecoder"%>
<jsp:useBean id="recordSet" class="weaver.conn.RecordSet" scope="page" />
<jsp:useBean id="CoworkBaseService" class="com.api.cowork.service.CoworkBaseService" scope="page" />
<jsp:useBean id="coworkShareService" class="com.engine.cowork.util.CoworkShareService" scope="page" />
<%@ taglib uri="/browserTag" prefix="brow"%>
<%@ include file="/systeminfo/init_wev8.jsp" %>
<%
new BaseBean().writeLog("into===");
String sruserid = Util.null2String(request.getParameter("sruserid"));//要查询的人
String from = Util.null2String(request.getParameter("from")); // 来自哪里 from=cowork 来自协作
String type = Util.null2String(request.getParameter("type"));// 查看类型(全部:all;未读:unread;重要:important;隐藏:hidden;板块标签:typePlate;自定义标签:label;)
String labelid = Util.null2String(request.getParameter("labelid"));// 标签ID(全部:allTab;未读:1;重要:2;隐藏:3;)
String orderType = Util.null2String(request.getParameter("orderType"), "important"); // 排序方式(未读:unread;重要:important;回复:replyNum;查看:readNum;)
int layout = Util.getIntValue(Util.null2String(request.getParameter("layout"), "1")); // 1为数图模式;2为列表模式
String mainid = Util.null2String(request.getParameter("mainid"));// 协作类别ID
// 前台参数==查询条件
String name = Util.null2String(request.getParameter("name")); //主题
String typeid = Util.null2String(request.getParameter("typeid"));//协作板块ID
String status = Util.null2String(request.getParameter("status")); //状态
String jointype = Util.null2String(request.getParameter("jointype")); //参与类型//(全部主题:（0或空） ;仅参与的:6;仅负责的:3;负责或参与的:1;我创建的:4;待审批的:5;其他关注的工作:2;)
String principal = Util.null2String(request.getParameter("principal"));//负责人
String creater = Util.null2String(request.getParameter("creater")); //创建者
String viewType = Util.null2String(request.getParameter("viewType"));// 门户查看类型 （2超期1未读 "0" 全部）
int pagesize = Util.getIntValue(request.getParameter("pagesize"),200); //每一次取多少
int pageindex = Util.getIntValue(request.getParameter("pageNum"), 1);
String fromPage = Util.null2String(request.getParameter("fromPage"));//来自哪个菜单点击
int index=Util.getIntValue(request.getParameter("index")); //下标
String searchStr = " 1=1 ";
if (!name.equals("")) {
searchStr += " and name like '%" + name + "%' ";
}
if (!typeid.equals("")&&!typeid.equals("0")) {
searchStr += " and typeid in(" + typeid + ") ";
}
if (!mainid.equals("")) {
searchStr += " and typeid in(select id from cowork_types where departmentid in (" + mainid + ")) ";
}
if (!status.equals("")) {//空为全部
if (!status.equals("0")) {
searchStr += " and status =" + status + " ";
}
} else {
//searchStr += " and status=1 ";
}
if (jointype.equals("0")||"".equals(jointype)) { // 全部主题
searchStr += " and jointype is not null ";
} else if (jointype.equals("1")) { // 负责或参与的
searchStr += " and jointype=1 ";
} else if (jointype.equals("2")) { // 其他关注的工作
searchStr += " and jointype=0 ";
} else if (jointype.equals("3")) { // 仅负责的
searchStr += " and principal=" + sruserid + " ";
} else if (jointype.equals("4")) { // 我创建的
searchStr += " and creater=" + sruserid + " ";
} else if (jointype.equals("5")&&!("itemApproval".equals(fromPage))) { //（交流列表） 审批 approvalAtatus=0 表示需要审批但还未审批，协作管理员才有审批权限（主题审批只有版块负责人才有）
searchStr += " and (isApproval=1 and approvalAtatus=1 and (creater=" + sruserid + " or principal=" + sruserid + " or cotypeid is not null)) ";
}else if (jointype.equals("5")&&("itemApproval".equals(fromPage))) { //(待审批列表) 审批 approvalAtatus=0 表示需要审批但还未审批，协作管理员才有审批权限（主题审批只有版块负责人才有）
searchStr += " and (isApproval=1 and approvalAtatus=1 and cotypeid is not null) ";
}else if (jointype.equals("6")){//仅参与的
searchStr += " and jointype=1 and principal !=" + sruserid + " ";
}
if (!creater.equals("")) {
searchStr += " and creater='" + creater + "' ";
}
if (!principal.equals("")) {
searchStr += " and principal='" + principal + "' ";
}
if ("unread".equals(type)||"1".equals(viewType)) {
searchStr = searchStr + " and isnew=1 and ishidden<>1 ";
} else if ("important".equals(type)) {
searchStr = searchStr + " and important=1 and ishidden<>1 ";
} else if ("hidden".equals(type)) {
searchStr = searchStr + " and ishidden=1 ";
} else if ("all".equals(type)) {
searchStr = searchStr + " and ishidden<>1 ";
} else if ("label".equals(type)) {
searchStr = searchStr + " and ishidden<>1 and islabel=1 ";
} else if ("typePlate".equals(type)) {
searchStr = searchStr + " and ishidden<>1 and typeid = (select name from cowork_label where id = " + labelid + ") ";
}
if("2".equals(viewType)&&"oracle".equals(recordSet.getDBType())){
searchStr = searchStr + " and ((enddate || ' ' || (case when (endtime is null or endtime ='') then '00:00' else endtime end)) < '"+CoworkBaseService.getCurrentTimeString()+"' and enddate is not null) ";
}else if("2".equals(viewType)&&"sqlserver".equals(recordSet.getDBType())){
searchStr = searchStr + " and ((enddate + ' ' + (case when (endtime is null or endtime ='') then '00:00' else endtime end)) < '"+CoworkBaseService.getCurrentTimeString()+"' and enddate is not null and enddate != '') ";
}else if(("2".equals(viewType)&&"mysql".equals(recordSet.getDBType()))){
searchStr = searchStr + " and (CONCAT(enddate ,' ' , (case when (endtime is null or endtime ='') then '00:00' else endtime end)) < '"+CoworkBaseService.getCurrentTimeString()+"' and enddate is not null and enddate != '') ";
}
//这边有点绕，要做区分（交流列表主题创建人和负责人和版块负责人都能看到待审批的，但是审批列表只有版块负责人能看到待审批的，因为审批列表能审批，而主题审批只有版块负责人有权限）
if(!jointype.equals("5")&&!("itemApproval".equals(fromPage))){//待审批的不需要再走这边了，上面已经控制了
searchStr = searchStr + " and (approvalAtatus=0 or (approvalAtatus=1 and (creater=" + sruserid + " or principal=" + sruserid + " or cotypeid is not null))) " ;
}else if(!jointype.equals("5")&&("itemApproval".equals(fromPage))){
searchStr = searchStr + " and (approvalAtatus=0 or (approvalAtatus=1 and cotypeid is not null)) " ;
}
searchStr = searchStr + " and id not in (select itemid from cowork_quiter where userid='" + sruserid + "') ";
String sqlStr = "(" +
" select t1.id,t1.name,t1.status,t1.typeid,t1.creater,t1.principal,t1.begindate,t1.enddate,t1.endtime,t1.replyNum,t1.readNum,t1.lastdiscussant,t1.lastupdatedate,t1.lastupdatetime,t1.isApproval,t1.approvalAtatus,t1.isTop,t2.cotypeid," +
" case when t3.sourceid is not null then 1 when t2.cotypeid is not null then 0 end as jointype," +
" case when t4.coworkid is not null then 0 else 1 end as isnew," +
" case when t5.coworkid is not null then 1 else 0 end as important," +
" case when t6.coworkid is not null then 1 else 0 end as ishidden" +
(type.equals("label") ? " ,case when t7.coworkid is not null then 1 else 0 end as islabel" : "") +
" from cowork_items t1 left join " +
//关注的协作
" ( " + coworkShareService.getManagerShareSql(sruserid) + " ) t2 on t1.typeid=t2.cotypeid left join " +
//直接参与的协作
" (" + coworkShareService.getPartnerShareSql(sruserid) + ") t3 on t3.sourceid=t1.id" +
//阅读|重要|隐藏
" left join (select distinct coworkid,userid from cowork_read where userid=" + sruserid + ") t4 on t1.id=t4.coworkid" + //阅读状态
" left join (select distinct coworkid,userid from cowork_important where userid=" + sruserid + " ) t5 on t1.id=t5.coworkid" + //重要性
" left join (select distinct coworkid,userid from cowork_hidden where userid=" + sruserid + " ) t6 on t1.id=t6.coworkid" + //是否隐藏
(type.equals("label") ? " left join (select distinct coworkid from cowork_item_label where labelid=" + labelid + ") t7 on t1.id=t7.coworkid" : "") +
" ) t ";
String backfields = " id,name,status,typeid,creater,principal,begindate,enddate,endtime,jointype,isnew,important,ishidden,replyNum,readNum,lastdiscussant,lastupdatedate,lastupdatetime,isApproval,approvalAtatus,isTop,cotypeid ";
String fromSql = sqlStr;
String sqlWhere = searchStr;
String orderby = " jointype desc,isTop desc,isnew desc,important desc";
if (orderType.equals("important")) {
orderby = " jointype desc,isTop desc,important desc,isnew desc";
} else if (orderType.equals("replyNum")) {
orderby = " jointype desc,isTop desc,replyNum desc,isnew desc";
} else if (orderType.equals("readNum")) {
orderby = " jointype desc,isTop desc,readNum desc,isnew desc";
} else if (orderType.equals("unread")) {
orderby = "isTop desc,isnew desc,important desc";
}
String selectsql = "select "+backfields+" from "+sqlStr+" where "+sqlWhere+" order by "+orderby;
new BaseBean().writeLog("selectsql==="+selectsql);
out.print(selectsql);
%>