SubcpyDeptUtil.jsp
7.77 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
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
<%@ page import="weaver.general.Util"%>
<%@ page import="java.util.Map.Entry"%>
<%@ page import="java.util.*"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"%>
<%!
/**
* 得到分部树查询sql
*/
private String getSubCpyTree(boolean isSqlServer){
String sql = null;
weaver.conn.RecordSet rs = new weaver.conn.RecordSet();
if(isSqlServer){
sql = "select * from GetSubCpyTree() order by level desc,showorder,id";
}else if("mysql".equals(rs.getDBType())){
sql = "SELECT t.id,t.subcompanyname,t.supsubcomid,CASE WHEN t.supsubcomid =0 THEN t.id ELSE CONCAT(t.supsubcomid,'_',t.id) END code,CONVERT(tl.lv,SIGNED) level "
+" FROM (SELECT @id idlist, @lv:=@lv+1 lv,"
+"(SELECT @id:=GROUP_CONCAT(id SEPARATOR ',') FROM HrmSubCompany d WHERE (d.canceled IS NULL OR d.canceled!=1) AND FIND_IN_SET(supsubcomid,@id)) sub"
+" FROM HrmSubCompany,(SELECT @id:='0',@lv:=-1) vars "
+" WHERE @id IS NOT NULL AND (canceled IS NULL OR canceled!=1)) tl,HrmSubCompany t"
+" WHERE FIND_IN_SET(t.id,tl.idlist) AND (canceled IS NULL OR canceled!=1) ORDER BY tl.lv DESC,t.showorder ASC, t.id ASC";
}else{
sql=" select id, "+
" subcompanyname, "+
" supsubcomid, "+
" substr(code, 2) code, "+
" (length(lv) / 2) \"level\" "+
" from (select id, "+
" subcompanyname, "+
" supsubcomid, "+
" showorder, "+
" SYS_CONNECT_BY_PATH(cpy.id, '_') code, "+
" SYS_CONNECT_BY_PATH('a', '_') as lv "+
" FROM HrmSubCompany cpy "+
" START WITH cpy.supsubcomid = 0 "+
" CONNECT BY PRIOR cpy.id = cpy.supsubcomid) t "+
" order by \"level\" desc,showorder asc, id asc ";
}
return sql;
}
/**
* 得到部门树查询sql
*/
private String getDeptTree(boolean isSqlServer){
String sql = null;
weaver.conn.RecordSet rs = new weaver.conn.RecordSet();
if(isSqlServer){
sql = "select * from getdepttree() order by level desc,showorder,id";
}else if("mysql".equals(rs.getDBType())){
sql = "SELECT t.id,t.departmentname,t.supdepid,t.subcompanyid1,CASE WHEN t.supdepid =0 THEN concat('dept_',t.id) ELSE CONCAT('dept_',t.supdepid,'_',t.id) END code,CONVERT(tl.lv,SIGNED) level "
+" FROM (SELECT @id idlist, @lv:=@lv+1 lv,"
+"(SELECT @id:=GROUP_CONCAT(id SEPARATOR ',') FROM HrmDepartment d WHERE (d.canceled IS NULL OR d.canceled!=1) AND FIND_IN_SET(supdepid,@id)) sub"
+" FROM HrmDepartment,(SELECT @id:='0',@lv:=-1) vars "
+" WHERE @id IS NOT NULL AND (canceled IS NULL OR canceled!=1)) tl,HrmDepartment t"
+" WHERE FIND_IN_SET(t.id,tl.idlist) AND (canceled IS NULL OR canceled!=1) order by tl.lv DESC,showorder asc, id asc ";
}else{
sql = " select id, "+
" departmentname, "+
" supdepid, "+
" subcompanyid1, "+
" 'dept_' || substr(code, 2) code, "+
" (length(lv) / 2) \"level\" "+
" from (select id, "+
" departmentname, "+
" supdepid, "+
" subcompanyid1, "+
" showorder, "+
" SYS_CONNECT_BY_PATH(dept.id, '_') code, "+
" SYS_CONNECT_BY_PATH('a', '_') as lv "+
" FROM HrmDepartment dept "+
" START WITH dept.supdepid = 0 "+
" CONNECT BY PRIOR dept.id = dept.supdepid) t "+
" order by \"level\" desc,showorder asc, id asc ";
}
return sql;
}
/**
* 设置否包含下级部门
*/
private void setIsContainSub(
LinkedHashMap<String, LinkedHashMap<String, String>> map) {
weaver.conn.RecordSet rs = new weaver.conn.RecordSet();
rs.execute("SELECT distinct subcompanyid1 FROM HrmDepartment WHERE canceled IS NULL OR canceled!=1");
List<String> subIdsList = new ArrayList<String>();
while (rs.next()) {
subIdsList.add(rs.getString("subcompanyid1"));
}
LinkedHashMap<String, String> tempMap = null;
for (int i = 0; i < subIdsList.size(); i++) {
tempMap = map.get(subIdsList.get(i));
if (tempMap == null) {
continue;
} else {
if ("no".equals(tempMap.get("hasSub"))
|| tempMap.get("hasSub") == null) {
tempMap.put("hasSub", "yes");
}
}
}
}
/**
* 把所有分部组合成的树放到集合中
* @return
*/
private LinkedHashMap<String,LinkedHashMap<String, String>> getTreeMapResult(weaver.conn.RecordSet rs) {
LinkedHashMap<String,LinkedHashMap<String, String>> map = new LinkedHashMap<String,LinkedHashMap<String, String>>();
String id = null;
String supsubcomid = null;
String code = null;
String level = null;
String s3 = null;
LinkedHashMap<String, String> curValues = null;
while(rs.next()){
id = null2String(rs.getString("id"));
supsubcomid = null2String(rs.getString("supsubcomid"));
code = null2String(rs.getString("code"));
level = null2String(rs.getString("level"));
curValues = new LinkedHashMap<String, String>();
curValues.put("id",id);
curValues.put("supsubcomid",supsubcomid);
curValues.put("code",code);
curValues.put("level",level);
map.put(id,curValues);
}
return map;
}
/**
* 把所有部门组合成的树放到集合中
* @return
*/
private LinkedHashMap<String,LinkedHashMap<String, String>> getDeptTreeMapResult(weaver.conn.RecordSet rs) {
LinkedHashMap<String,LinkedHashMap<String, String>> map = new LinkedHashMap<String,LinkedHashMap<String, String>>();
String id = null;
String superid = null;
String subcompanyid1 = null;
String code = null;
String level = null;
String s3 = null;
LinkedHashMap<String, String> curValues = null;
while(rs.next()){
id = null2String(rs.getString("id"));
superid = null2String(rs.getString("supdepid"));
subcompanyid1 = null2String(rs.getString("subcompanyid1"));
code = null2String(rs.getString("code"));
level = null2String(rs.getString("level"));
curValues = new LinkedHashMap<String, String>();
curValues.put("id",id);
curValues.put("superid",superid);
curValues.put("subcompanyid1",subcompanyid1);
curValues.put("code",code);
curValues.put("level",level);
map.put(id,curValues);
}
return map;
}
/**
* null转空字符串
* @param str
* @return
*/
public static String null2String(String str) {
return null2String(str, "");
}
/**
* null转换为指定字符串
* @param str 检测字符串
* @param nullValue 替换字符串
* @return
*/
public static String null2String(String str, String nullValue) {
return (str == null || "".equals(str)) ? nullValue : str;
}
/**
* 字符串是否为空
* @param str
* @return
*/
public static boolean isEmpty(String str){
return str==null || "".equals(str.trim());
}
%>