BudgetPeriodBrowserTreeAjax.jsp
5.17 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
<%@page import="weaver.fna.general.FnaCommon"%>
<%@page import="weaver.conn.RecordSet"%>
<%@page import="weaver.systeminfo.SystemEnv"%>
<%@page import="weaver.general.BaseBean"%>
<%@page import="org.apache.commons.lang.StringEscapeUtils"%>
<%@page import="org.json.JSONObject"%>
<%@ page language="java" contentType="text/html; charset=UTF-8" %>
<%@ page import="weaver.general.Util" %>
<%@ page import="java.util.*,java.sql.Timestamp" %>
<%@ page import="weaver.general.GCONST" %>
<%@page import="weaver.hrm.HrmUserVarify"%>
<%@page import="weaver.hrm.User"%>
<jsp:useBean id="rs" class="weaver.conn.RecordSet" scope="page" />
<jsp:useBean id="rs1" class="weaver.conn.RecordSet" scope="page" />
<jsp:useBean id="rs2" class="weaver.conn.RecordSet" scope="page" />
<%
StringBuffer result = new StringBuffer();
User user = HrmUserVarify.getUser (request , response) ;
if(user != null){
int userId = user.getUID();
String qryType = Util.null2String(request.getParameter("qryType"));
String sqlwhere = Util.null2String(request.getParameter("sqlwhere"));
String tableName = Util.null2String(request.getParameter("tableName"));
String periodId = Util.null2String(request.getParameter("periodId"));
String number = tableName.split("_")[1];
if("1".equals(qryType)){//模糊查询
String qryName = Util.null2String(request.getParameter("qryName"));
//上级不能为其下级，递归查询所有下级
StringBuffer sql = new StringBuffer();
if("oracle".equalsIgnoreCase(rs.getDBType())){
sql.append(" select id,periodName,supId from FnaPeriodSetting_"+number+" \n");
sql.append(" start with id in ('"+periodId+"') \n");
sql.append(" connect by prior id = supsubject ");
}else if("mysql".equalsIgnoreCase(rs.getDBType())){
sql.append(" select DISTINCT t.id,t.periodName,t.supId from (\n");
sql.append(" select @id idlist, @lv:=@lv+1 lv,\n");
sql.append(" (select @id:=group_concat(id separator ',') from FnaPeriodSetting_"+number+" where find_in_set(supId,@id)) sub \n");
sql.append(" from FnaPeriodSetting_"+number+" ,(select @id:='"+periodId+"' ,@lv:=0) vars \n");
sql.append(" where @id is not null) tl, FnaPeriodSetting_"+number+" t \n");
sql.append(" where find_in_set(t.id,tl.idlist) \n");
sql.append(" order by lv asc");
}else{
sql.append("WITH allsub(id,periodName,supId) \n");
sql.append(" as ( \n");
sql.append(" SELECT id,periodName,supId FROM FnaPeriodSetting_"+number+" where id in ('"+periodId+"') \n");
sql.append(" UNION ALL SELECT a.id,a.periodName,a.supId FROM FnaPeriodSetting_"+number+" a,allsub b where a.supId = b.id \n");
sql.append(" ) select * from allsub \n");
}
rs.executeQuery(sql.toString());
List<String> budgetBearerList = new ArrayList<String>();
while(rs.next()){
String _id = Util.null2String(rs.getString("id"));
if(!budgetBearerList.contains(_id)){
budgetBearerList.add(_id);
}
}
result.append("{\"dataArray\":[");
int idx = 0;
String sql1 = " select id, periodName from " + tableName + " where 1=1 and (periodName like ? or codeName like ? ) ";
rs1.executeQuery(sql1, "%"+ qryName +"%", "%"+ qryName +"%");
while(rs1.next()){
String id = rs1.getString("id");
String name = rs1.getString("periodName");
if(budgetBearerList.contains(id)){
continue;
}
if(idx>0){
result.append(",");
}
result.append("{"+
"\"id\":"+JSONObject.quote(id)+","+
"\"name\":"+JSONObject.quote(name)+""+
"}");
idx++;
}
result.append("]}");
}else if("2".equals(qryType)){//显示科目树
String supIdFull = Util.null2String(request.getParameter("id"));
result.append("[");
int idx = 0;
String sql1 = " select id, periodName from " + tableName + " where 1=1 and supId = ? and id != ? ";
rs1.executeQuery(sql1, supIdFull,periodId);
while(rs1.next()){
String id = rs1.getString("id");
String name = rs1.getString("periodName");
if(idx>0){
result.append(",");
}
String isParent = "true";
String sql2 = "select count(*) cnt from "+ tableName +" a where a.supId = ? ";
rs2.executeQuery(sql2, id);
if(rs2.next() && rs2.getInt("cnt") > 0){
isParent = "true";
}else{
isParent = "false";
}
result.append("{"+
"\"id\":"+JSONObject.quote(id)+","+
"\"name\":"+JSONObject.quote(name)+","+
"\"isParent\":"+isParent+","+
"\"canSelect\":"+1+""+
"}");
idx++;
}
result.append("]");
}
}
%><%=result.toString() %>