GetCoworkTreeSql.jsp
6.88 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
<%@ 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"%>
<%@ page import="weaver.cowork.CoworkService" %>
<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 countType = Util.null2s(Util.null2String(request.getParameter("countType")),"sub");//
CoworkService coworkService=new CoworkService();
int userid=user.getUID();
int departmentid=user.getUserDepartment(); //用户所属部门
int subCompanyid=user.getUserSubCompany1(); //用户所属分部
String seclevel=user.getSeclevel(); //用于安全等级
String sqlStr=" from ("+
" select t1.id,t1.name,t1.status,t1.typeid,t9.typename,t9.mainid,t9.mainname,"+
" case when t3.sourceid is not null then 1 when t2.cotypeid is not null then 0 end as jointype,"+
" case when t6.coworkid is not null then 1 else 0 end as ishidden"+
" from cowork_items t1 left join "+
//关注的协作
" ("+coworkService.getManagerShareSql(""+userid)+") t2 on t1.typeid=t2.cotypeid left join "+
//直接参与的协作
" ("+coworkService.getPartnerShareSql(""+userid)+") t3 on t3.sourceid=t1.id"+
" left join (select t7.id,t7.typename,t8.id as mainid,t8.typename as mainname from cowork_types t7 left join cowork_maintypes t8 on t7.departmentid=t8.id) t9 on t1.typeid=t9.id"+
//阅读|重要|隐藏
" left join (select distinct coworkid,userid from cowork_hidden where userid="+userid+" ) t6 on t1.id=t6.coworkid"+ //是否隐藏
" ) t where 1=1 and jointype is not null and ishidden<>1 and status=1";
if(countType.equals("main")){
sqlStr="select mainid as id,count(mainid) as total "+sqlStr+" GROUP BY mainid ";
}
else if(countType.equals("sub")){
sqlStr="select typeid as id,count(typeid) as total "+sqlStr+" GROUP BY typeid ";
}
else if(countType.equals("themeApprovalMain")){//协作主题审批
sqlStr = sqlStr.replace("t1.id,t1.name,t1.status,t1.typeid", "t1.id,t1.name,t1.status,t1.typeid,t1.isApproval,t1.approvalAtatus,t2.cotypeid");
sqlStr = sqlStr.replace("jointype is not null", "(isApproval=1 and approvalAtatus=1 and cotypeid is not null)");
sqlStr = "select mainid as id,count(mainid) as total "+sqlStr+" GROUP BY mainid ";
}
else if(countType.equals("themeApprovalSub")){//协作主题审批
sqlStr = sqlStr.replace("t1.id,t1.name,t1.status,t1.typeid", "t1.id,t1.name,t1.status,t1.typeid,t1.isApproval,t1.approvalAtatus,t2.cotypeid");
sqlStr = sqlStr.replace("jointype is not null", "(isApproval=1 and approvalAtatus=1 and cotypeid is not null)");
sqlStr = "select typeid as id,count(typeid) as total "+sqlStr+" GROUP BY typeid ";
}
else if(countType.equals("contentApproveMain")){//协作内容审批
sqlStr = sqlStr.replace("and ishidden<>1 and status=1", "");
sqlStr = "select t11.mainid as id , count(mainid) as total from cowork_discuss t10 join (select id , mainid "+sqlStr+") t11 "+
" on t10.coworkid = t11.id where t10.approvalAtatus = 1 group by mainid";
}
else if(countType.equals("contentApproveSub")){//协作内容审批
sqlStr = sqlStr.replace("and ishidden<>1 and status=1", "");
sqlStr = "select t11.typeid as id , count(typeid) as total from cowork_discuss t10 join (select id , typeid "+sqlStr+") t11 "+
" on t10.coworkid = t11.id where t10.approvalAtatus = 1 group by typeid";
}
else if(countType.equals("nameMonitorMain")){//主题监控
sqlStr = "select t4.mainid as id , count(mainid) as total from cowork_items t1 left join "+
"(select t2.id as typeid, t3.id as mainid from cowork_types t2 left join cowork_maintypes t3 on t2.departmentid=t3.id) t4"+
" on t1.typeid = t4.typeid group by mainid";
}
else if(countType.equals("nameMonitorSub")){//主题监控
sqlStr = "select typeid as id , count(typeid) as total from cowork_items t1 group by typeid";
}
else if(countType.equals("discussMonitorMain")){//内容监控
sqlStr = "select t.mainid as id , count(t.mainid) as total from cowork_discuss t0 left join "+
"( select t1.id as coworid,t4.id as typeid ,t4.mainid as mainid from cowork_items t1 left join "+
"(select t2.id , t3.id as mainid from cowork_types t2 left join cowork_maintypes t3 on t2.departmentid=t3.id) t4"+
" on t1.typeid = t4.id) t on t0.coworkid = t.coworid and t0.topdiscussid = 0 group by mainid";
}
else if(countType.equals("discussMonitorSub")){//内容监控
sqlStr = "select t.typeid as id , count(t.typeid) as total from cowork_discuss t0 left join "+
"( select t1.id as coworid,t4.id as typeid ,t4.mainid as mainid from cowork_items t1 left join "+
"(select t2.id , t3.id as mainid from cowork_types t2 left join cowork_maintypes t3 on t2.departmentid=t3.id) t4"+
" on t1.typeid = t4.id) t on t0.coworkid = t.coworid and t0.topdiscussid = 0 group by typeid";
}
else if(countType.equals("commentMonitorMain")){//评论监控
sqlStr = "select t.mainid as id , count(t.mainid) as total from cowork_discuss t0 left join "+
"( select t1.id as coworid,t4.id as typeid ,t4.mainid as mainid from cowork_items t1 left join "+
"(select t2.id , t3.id as mainid from cowork_types t2 left join cowork_maintypes t3 on t2.departmentid=t3.id) t4"+
" on t1.typeid = t4.id) t on t0.coworkid = t.coworid and t0.topdiscussid <> 0 group by mainid";
}
else if(countType.equals("commentMonitorSub")){//评论监控
sqlStr = "select t.typeid as id , count(t.typeid) as total from cowork_discuss t0 left join "+
"( select t1.id as coworid,t4.id as typeid ,t4.mainid as mainid from cowork_items t1 left join "+
"(select t2.id , t3.id as mainid from cowork_types t2 left join cowork_maintypes t3 on t2.departmentid=t3.id) t4"+
" on t1.typeid = t4.id) t on t0.coworkid = t.coworid and t0.topdiscussid <> 0 group by typeid";
}
new BaseBean().writeLog("selectsql==="+sqlStr);
out.print(sqlStr);
%>