CarSetDataOperation2.jsp
13 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
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
<%@ page language="java" contentType="text/html; charset=UTF-8" %>
<%@ page import="weaver.general.Util"%>
<%@ page import="java.util.*" %>
<%@ page import="net.sf.json.JSONArray"%>
<%@ page import="net.sf.json.JSONObject"%>
<jsp:useBean id="RecordSet" class="weaver.conn.RecordSet" scope="page"/>
<jsp:useBean id="rs2" class="weaver.conn.RecordSet" scope="page"/>
<jsp:useBean id="FormManager" class="weaver.workflow.form.FormManager" scope="page" />
<jsp:useBean id="CarDateTimeUtil" class="weaver.car.CarDateTimeUtil" scope="page" />
<%@page import="weaver.hrm.HrmUserVarify"%>
<%@page import="weaver.hrm.User"%>
<%
User user = HrmUserVarify.getUser (request , response) ;
String action = Util.null2String(request.getParameter("action"));
if("getData".equals(action)){
int workflowid = Util.getIntValue(request.getParameter("workflowid"),-1);
String sql = "select isremind,remindtype from mode_carremindset";
RecordSet.executeSql(sql);
int isremind = 0;
int remindtype = 0;
if(RecordSet.next()){
isremind = RecordSet.getInt("isremind");
remindtype = RecordSet.getInt("remindtype");
}
JSONArray array = new JSONArray();
JSONObject object = new JSONObject();
if(isremind <= 0){
object.put("iscontinue","no");
object.put("remindtype",remindtype);
}else{
sql = "select id from carbasic where workflowid ='"+workflowid+"' and isuse=1";
RecordSet.executeSql(sql);
int id = 0;
if(RecordSet.next()){
id = RecordSet.getInt("id");
}
sql = "select carfieldid,modefieldid from mode_carrelatemode where mainid="+id;
RecordSet.executeSql(sql);
while(RecordSet.next()){
int carfieldid = RecordSet.getInt("carfieldid");
int modefieldid = RecordSet.getInt("modefieldid");
switch(carfieldid)
{
case 627:
object.put("carid",modefieldid); //车辆
break;
case 628:
object.put("driver",modefieldid); //司机
break;
case 629:
object.put("caruserid",modefieldid); //用车人
break;
case 634:
object.put("startDate",modefieldid); //开始日期
break;
case 635:
object.put("startTime",modefieldid); //开始时间
break;
case 636:
object.put("endDate",modefieldid); //结束日期
break;
case 637:
object.put("endTime",modefieldid); //结束时间
break;
case 639:
object.put("chexiao",modefieldid); //撤销
break;
default:
break;
}
}
object.put("iscontinue","yes");
object.put("remindtype",remindtype);
}
array.add(object);
response.getWriter().print(array.toString());
// out.print(array.toString());
// out.flush();
// out.close();
}else if("getDataSys".equals(action)){ // 128307
int workflowid = Util.getIntValue(request.getParameter("workflowid"),-1);//流程id
int formId = Util.getIntValue(request.getParameter("formId"),-1);//表单id
String sql = "select isremind,remindtype from mode_carremindset";
RecordSet.executeSql(sql);
int isremind = 0;
int remindtype = 0;
if(RecordSet.next()){
isremind = RecordSet.getInt("isremind");
remindtype = RecordSet.getInt("remindtype");
}
JSONArray array = new JSONArray();
JSONObject object = new JSONObject();
String requirednode="";
if(formId==163){
object.put("carid","627"); //车辆
object.put("driver","628"); //司机
object.put("caruserid","629"); //用车人
object.put("startDate","634"); //开始日期
object.put("startTime","635"); //开始时间
object.put("endDate","636"); //结束日期
object.put("endTime","637"); //结束时间
object.put("iscontinue","yes");
object.put("remindtype",remindtype);
}else{
sql = "select t.*,info.labelname from( ";
sql += "select a.* from mode_carrelatemode a,carbasic b where a.mainid=b.id and b.workflowid="+workflowid;
sql +=")t left join WORKFLOW_BILLFIELD bill on t.MODEFIELDID=BILL.id left join HTMLLABELINFO info on info.indexid=bill.fieldlabel and languageid="+(user==null?7:user.getLanguage());
RecordSet.executeSql(sql);
if(RecordSet.getCounts()>=7){
while(RecordSet.next()){
String carfieldid = RecordSet.getString("carfieldid");
String modefieldid = RecordSet.getString("modefieldid");
String labelname = RecordSet.getString("labelname");
requirednode = Util.null2String(RecordSet.getString("requirednode"));
String key = "";
if(carfieldid.equals("627")){//车辆
key = "carid";
}else if(carfieldid.equals("628")){//司机
key = "driver";
}else if(carfieldid.equals("629")){//用车人
key = "caruserid";
}else if(carfieldid.equals("634")){//开始日期
key = "startDate";
}else if(carfieldid.equals("635")){//开始时间
key = "startTime";
}else if(carfieldid.equals("636")){//结束日期
key = "endDate";
}else if(carfieldid.equals("637")){//结束时间
key = "endTime";
}
object.put(key,modefieldid); //车辆
object.put(key+"name",labelname); //车辆
}
object.put("iscontinue","yes");
object.put("remindtype",remindtype);
}else{
object.put("iscontinue","no");
object.put("remindtype",remindtype);
}
}
object.put("requirednode",requirednode); //必填节点
if(isremind <= 0){
object.put("iscontinue","no");
object.put("remindtype",remindtype);
}
array.add(object);
response.getWriter().print(array.toString());
// out.print(array.toString());
// out.flush();
// out.close();
}else if("checkData".equals(action)){
JSONArray array = new JSONArray();
JSONObject object = new JSONObject();
int requestid = Util.getIntValue(Util.null2String(request.getParameter("requestid"),"0"),0);
String workflowid = Util.null2String(request.getParameter("workflowid"));
String carid = Util.null2String(request.getParameter("carid")); //车辆
String startDate = Util.null2String(request.getParameter("startDate")); //开始日期
String startTime = Util.null2String(request.getParameter("startTime")); //开始时间
String endDate = Util.null2String(request.getParameter("endDate")); //结束日期
String endTime = Util.null2String(request.getParameter("endTime")); //结束时间
startDate = CarDateTimeUtil.getServerDate(startDate,startTime);//客户端时间转换成服务器时间
startTime = CarDateTimeUtil.getServerTime(startDate,startTime);
if(!"".equals(endDate)){
endDate = CarDateTimeUtil.getServerDate(endDate,endTime);
}
if(!"".equals(endTime)){
endTime = CarDateTimeUtil.getServerTime(endDate,endTime);
}
/*begin 查询条件拼装*/
String returnStr = " and c1.id='"+carid+"' ";
if((RecordSet.getDBType()).equals("oracle")){
returnStr += " and ((c2.startDate ||' '||c2.startTime >= '"+startDate+" "+startTime+"' AND c2.startDate ||' '||c2.startTime <= '"+endDate+" "+endTime+"') OR"+
" (c2.startDate ||' '||c2.startTime <= '"+startDate+" "+startTime+"' AND c2.endDate||' '||c2.endTime >= '"+endDate+" "+endTime+"') OR"+
//" (c2.startDate||' '||c2.startTime >= '"+startDate+" "+startTime+"' AND c2.endDate||' '||c2.endTime >= '"+endDate+" "+endTime+"') OR"+
" (c2.endDate||' '||c2.endTime >= '"+startDate+" "+startTime+"' AND c2.endDate||' '||c2.endTime <= '"+endDate+" "+endTime+"')) ";
}else if((RecordSet.getDBType()).equals("mysql")){
returnStr += "and ((concat(c2.startDate,'',c2.startTime) >= concat('"+startDate+"',' ','"+startTime+"') AND concat(c2.startDate,'',c2.startTime) <= concat('"+endDate+"',' ','"+endTime+"')) OR "+
"(concat(c2.startDate ,' ',c2.startTime) <= concat('"+startDate+"',' ','"+startTime+"') AND concat(c2.endDate,' ',c2.endTime) >= concat('"+endDate+"',' ','"+endTime+"')) OR"+
//" (c2.startDate||' '||c2.startTime >= '"+startDate+" "+startTime+"' AND c2.endDate||' '||c2.endTime >= '"+endDate+" "+endTime+"') OR"+
" (concat(c2.endDate,' ',c2.endTime) >= concat('"+startDate+"',' ','"+startTime+"') AND concat(c2.endDate,' ',c2.endTime) <= concat('"+endDate+"',' ','"+endTime+"')))";
}else{
returnStr += " and ((c2.startDate +' '+c2.startTime >= '"+startDate+" "+startTime+"' AND c2.startDate +' '+c2.startTime <= '"+endDate+" "+endTime+"') OR"+
" (c2.startDate +' '+c2.startTime <= '"+startDate+" "+startTime+"' AND c2.endDate+' '+c2.endTime >= '"+endDate+" "+endTime+"') OR"+
//" (c2.startDate+' '+c2.startTime >= '"+startDate+" "+startTime+"' AND c2.endDate+' '+c2.endTime >= '"+endDate+" "+endTime+"') OR"+
" (c2.endDate+' '+c2.endTime >= '"+startDate+" "+startTime+"' AND c2.endDate+' '+c2.endTime <= '"+endDate+" "+endTime+"')) ";
}
returnStr += " and(c2.enddate is not null and c2.endtime is not null) and (c2.cancel =0 or c2.cancel is null) and c3.requestid != '"+requestid+"' ";
if ((RecordSet.getDBType()).equals("oracle")) {
returnStr = Util.StringReplace(returnStr,"SUBSTRING","substr");
}
/*end 查询条件拼装*/
String C2 = "";
if ((RecordSet.getDBType()).equals("oracle")) {
C2 += "(select id,requestid,to_number(carId) as carId,to_number(driver) as driver,to_number(userid) as userid,startdate,starttime,enddate,endtime,cancel,'CarUseApprove' as tablename,'cancel' as fieldname from CarUseApprove";
}else if ((RecordSet.getDBType()).equals("mysql")){
C2 += "(select id,requestid,carId,driver,userid,startdate,starttime,enddate,endtime,cancel,'CarUseApprove' as tablename,'cancel' as fieldname from CarUseApprove";
} else {
C2 += "(select id,requestid,carId,driver,userid,startdate,starttime,enddate,endtime,cancel,'CarUseApprove' as tablename,'cancel' as fieldname from CarUseApprove";
}
RecordSet.executeSql("select id,formid from carbasic where formid!=163 and isuse = 1");
while (RecordSet.next()) {
String mainid = RecordSet.getString("id");
String _formid = RecordSet.getString("formid");
String _tablename = FormManager.getTablename(_formid);
if(_tablename==null||"".equals(_tablename))continue;
C2 += " union all select id,requestid,";
Map _map = new HashMap();
rs2.executeSql("select carfieldid,modefieldid,fieldname from mode_carrelatemode c,workflow_billfield b where c.modefieldid=b.id and mainid="+mainid);
while (rs2.next()) {
String carfieldid = rs2.getString("carfieldid");
String modefieldid = rs2.getString("modefieldid");
String fieldname = rs2.getString("fieldname");
_map.put(carfieldid,fieldname);
}
if ((RecordSet.getDBType()).equals("oracle")) {
C2 += "to_number("+Util.null2s(Util.null2String(_map.get("627")),"0") +") as carId,";
C2 += "to_number("+Util.null2s(Util.null2String(_map.get("628")),"0") +") as driver,";
C2 += "to_number("+Util.null2s(Util.null2String(_map.get("629")),"0") +") as userid,";
}else if ((RecordSet.getDBType()).equals("mysql")){
C2 += Util.null2s(Util.null2String(_map.get("627")),"0") +" as carId,";
C2 += Util.null2s(Util.null2String(_map.get("628")),"0") +" as driver,";
C2 += Util.null2s(Util.null2String(_map.get("629")),"0") +" as userid,";
}else {
C2 += Util.null2s(Util.null2String(_map.get("627")),"0") +" as carId,";
C2 += Util.null2s(Util.null2String(_map.get("628")),"0") +" as driver,";
C2 += Util.null2s(Util.null2String(_map.get("629")),"0") +" as userid,";
}
C2 += Util.null2s(Util.null2String(_map.get("634")),"''") +" as startDate,";
C2 += Util.null2s(Util.null2String(_map.get("635")),"''") +" as startTime,";
C2 += Util.null2s(Util.null2String(_map.get("636")),"''") +" as endDate,";
C2 += Util.null2s(Util.null2String(_map.get("637")),"''") +" as endTime,";
C2 += Util.null2s(Util.null2String(_map.get("639")),"'0'") +" as cancel,";
C2 += "'"+_tablename +"' as tablename,";
C2 += "'" + Util.null2String(_map.get("639")) +"' as fieldname";
C2 += " from " + _tablename;
}
C2 += ")";
String backfields = "c1.id,c2.id aid,c1.carNo,c2.driver,c2.userid,c2.startdate,c2.starttime,c2.enddate,c2.endtime,c3.requestid,c3.requestname,c.id as tid, c.name as typename,c3.currentnodetype,c2.cancel,c2.tablename,c2.fieldname ";
String fromSql = " Carinfo c1 left join "+C2+" c2 on c2.carId = c1.id left join workflow_requestbase c3 on c2.requestid=c3.requestid left join CarType c on c1.cartype = c.id ";
String whereSql = " where c3.currentnodetype<>0 and c3.workflowid not in (select workflowid from carbasic where isuse=0)" + returnStr;
String orderby = " c2.startdate ,c2.starttime , c1.id" ;
if("".equals(endDate)&&"".equals(endTime)){//结束日期和结束时间为空不进行冲突校验
whereSql+= " and 1=2";
}
//System.out.println("select "+backfields+" from "+fromSql+" "+whereSql);
RecordSet.executeSql("select "+backfields+" from "+fromSql+" "+whereSql);
if(RecordSet.next()){
object.put("iscontinue","no");
}else{
object.put("iscontinue","yes");
}
array.add(object);
response.getWriter().print(array.toString());
// out.print(array.toString());
// out.flush();
// out.close();
}
%>