1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17 package org.exoplatform.calendar.service;
18
19 import javax.jcr.query.Query;
20
21 import org.exoplatform.calendar.service.impl.NewUserListener;
22 import org.exoplatform.commons.utils.ISO8601;
23 import org.exoplatform.commons.utils.XPathUtils;
24
25
26
27
28
29
30
31
32
33 @Deprecated
34 public class EventQuery {
35 private String nodeType = "exo:calendarEvent";
36
37 private String eventType;
38
39 private String text = null;
40
41 private String[] categoryIds = null;
42
43 private String[] calendarIds = null;
44
45 private String[] filterCalendarIds = null;
46
47 private java.util.Calendar fromDate = null;
48
49 private java.util.Calendar toDate = null;
50
51 private String calendarPath;
52
53 private String priority;
54
55 private String state;
56
57 private String[] orderBy;
58
59 private String[] participants;
60
61 private Boolean excludeRepeatEvent = false;
62
63 private String orderType = Utils.ASCENDING;
64
65 private String queryType = Query.XPATH;
66
67 private long limitedItems = 0;
68
69 public EventQuery() {}
70
71 public EventQuery(EventQuery eventQuery) {
72 this.setCalendarId(eventQuery.getCalendarId());
73 this.setCalendarPath(eventQuery.getCalendarPath());
74 this.setCategoryId(eventQuery.getCategoryId());
75 this.setEventType(eventQuery.getEventType());
76 this.setExcludeRepeatEvent(eventQuery.getExcludeRepeatEvent());
77 this.setFilterCalendarIds(eventQuery.getFilterCalendarIds());
78 this.setFromDate(eventQuery.getFromDate());
79 this.setLimitedItems(eventQuery.getLimitedItems());
80 this.setNodeType(eventQuery.getNodeType());
81 this.setOrderBy(eventQuery.getOrderBy());
82 this.setOrderType(eventQuery.getOrderType());
83 this.setParticipants(eventQuery.getParticipants());
84 this.setPriority(eventQuery.getPriority());
85 this.setQueryType(eventQuery.getQueryType());
86 this.setState(eventQuery.getState());
87 this.setText(eventQuery.getText());
88 this.setToDate(eventQuery.getToDate());
89 }
90
91 public String getNodeType() {
92 return nodeType;
93 }
94
95 public void setNodeType(String nt) {
96 this.nodeType = nt;
97 }
98
99 public String getEventType() {
100 return eventType;
101 }
102
103 public void setEventType(String eventType) {
104 this.eventType = eventType;
105 }
106
107 public void setText(String fullTextSearch) {
108 this.text = fullTextSearch;
109 }
110
111 public String getText() {
112 return text;
113 }
114
115 public String[] getCategoryId() {
116 return categoryIds;
117 }
118
119 public boolean isSearchInAllCategories() {
120 if(categoryIds == null || categoryIds.length == 0) {
121 return true;
122 }
123
124 for(String id : categoryIds) {
125 if(CalendarService.DEFAULT_EVENTCATEGORY_ID_ALL.equals(id)) {
126 return true;
127 }
128 }
129
130 return false;
131 }
132
133 public void setCategoryId(String[] categoryIds) {
134 this.categoryIds = categoryIds;
135 }
136
137 public String[] getCalendarId() {
138 return calendarIds;
139 }
140
141 public void setCalendarId(String[] calendarIds) {
142 this.calendarIds = calendarIds;
143 }
144
145 public void setFilterCalendarIds(String[] filterCalendarIds) {
146 this.filterCalendarIds = filterCalendarIds;
147 }
148
149 public String[] getFilterCalendarIds() {
150 return filterCalendarIds;
151 }
152
153 public java.util.Calendar getFromDate() {
154 return fromDate;
155 }
156
157 public void setFromDate(java.util.Calendar fromDate) {
158 this.fromDate = fromDate;
159 }
160
161 public java.util.Calendar getToDate() {
162 return toDate;
163 }
164
165 public void setToDate(java.util.Calendar toDate) {
166 this.toDate = toDate;
167 }
168
169 public String getCalendarPath() {
170 return calendarPath;
171 }
172
173 public void setCalendarPath(String calendarPath) {
174 this.calendarPath = calendarPath;
175 }
176
177 public String getPriority() {
178 return priority;
179 }
180
181 public void setPriority(String priority) {
182 this.priority = priority;
183 }
184
185 public String getState() {
186 return state;
187 }
188
189 public void setState(String st) {
190 this.state = st;
191 }
192
193 public String[] getOrderBy() {
194 return orderBy;
195 }
196
197 public void setOrderBy(String[] order) {
198 this.orderBy = order;
199 }
200
201 public String[] getParticipants() {
202 return participants;
203 }
204
205 public void setParticipants(String[] par) {
206 this.participants = par;
207 }
208
209 public String getOrderType() {
210 return orderType;
211 }
212
213 public void setOrderType(String type) {
214 this.orderType = type;
215 }
216
217 public String getQueryStatement() throws Exception {
218 StringBuilder queryString = null;
219 if (queryType.equals(Query.SQL)) {
220 if (!Utils.isEmpty(calendarPath))
221 queryString = new StringBuilder(" select * from ").append(nodeType).append(" where jcr:path like '").append(calendarPath).append("/%'");
222 else
223 queryString = new StringBuilder(" select * from ").append(nodeType).append(" ");
224 if (!Utils.isEmpty(text)) {
225 String val = escapeLikeQuery(text);
226 queryString.append(" and (").append(Utils.EXO_SUMMARY).append(" like '%").append(val).append("%'");
227 queryString.append(" ESCAPE '\\'");
228 queryString.append(" or ").append(Utils.EXO_DESCRIPTION).append(" like '%").append(val).append("%'");
229 queryString.append(" ESCAPE '\\'");
230 queryString.append(" or ").append(Utils.EXO_LOCATION).append(" like '%").append(val).append("%'");
231 queryString.append(" ESCAPE '\\'");
232 queryString.append(" or ").append(Utils.EXO_PARTICIPANT).append(" like '%").append(val).append("%'");
233 queryString.append(" ESCAPE '\\'");
234 queryString.append(" or ").append(Utils.EXO_INVITATION).append(" like '%").append(val).append("%'");
235 queryString.append(" ESCAPE '\\'");
236
237 queryString.append(")");
238 }
239 if (!Utils.isEmpty(eventType)) {
240 queryString.append(" and ").append(Utils.EXO_EVENT_TYPE).append(" = '").append(eventType).append("'");
241 }
242 if (!Utils.isEmpty(priority)) {
243 queryString.append(" and ").append(Utils.EXO_PRIORITY).append(" = '").append(priority).append("'");
244 }
245 if (!Utils.isEmpty(state)) {
246 queryString.append(" and ").append(Utils.EXO_EVENT_STATE).append(" = '").append(state).append("'");
247 }
248 if (!isSearchInAllCategories()) {
249 queryString.append(" and (");
250 String[] categoryIds = getCategoryId();
251 for (int i = 0; i < categoryIds.length; i++) {
252 if(i > 0) {
253 queryString.append(" or ");
254 }
255 queryString.append(Utils.EXO_EVENT_CATEGORYID).append(" = '").append(categoryIds[i]).append("'");
256 }
257 queryString.append(" )");
258 }
259 if (calendarIds != null && calendarIds.length > 0) {
260 for (String calendarId : calendarIds) {
261 queryString.append(" and ").append(Utils.EXO_CALENDAR_ID).append(" = '").append(calendarId).append("'");
262 }
263 }
264 return queryString.toString();
265 } else {
266 if (calendarPath != null)
267 queryString = new StringBuilder("/jcr:root").append(XPathUtils.escapeIllegalXPathName(calendarPath)).append("//element(*,").append(nodeType).append(")");
268 else
269 queryString = new StringBuilder("/jcr:root//element(*,").append(nodeType).append(")");
270 boolean hasConjuntion = false;
271 StringBuilder stringBuffer = new StringBuilder("[");
272
273 if (text != null && text.length() > 0) {
274 String val = escapeContainsQuery(text);
275 stringBuffer.append("(jcr:contains(@").append(Utils.EXO_SUMMARY).append(", '").append(val).append("')")
276 .append(" or jcr:contains(@").append(Utils.EXO_DESCRIPTION).append(", '").append(val).append("')")
277 .append(" or jcr:contains(@").append(Utils.EXO_LOCATION).append(", '").append(val).append("')")
278 .append(" or jcr:contains(@").append(Utils.EXO_PARTICIPANT).append(", '").append(val).append("')")
279 .append(" or jcr:contains(@").append(Utils.EXO_INVITATION).append(", '").append(val).append("'))");
280 hasConjuntion = true;
281 }
282
283 if (eventType != null && eventType.length() > 0) {
284 if (hasConjuntion)
285 stringBuffer.append(" and (");
286 else
287 stringBuffer.append("(");
288 stringBuffer.append("@exo:eventType='").append(eventType).append("'");
289 stringBuffer.append(")");
290 hasConjuntion = true;
291 }
292
293 if (priority != null && priority.length() > 0) {
294 if (hasConjuntion)
295 stringBuffer.append(" and (");
296 else
297 stringBuffer.append("(");
298 stringBuffer.append("@exo:priority='").append(priority).append("'");
299 stringBuffer.append(")");
300 hasConjuntion = true;
301 }
302
303 if (state != null && state.length() > 0) {
304 if (hasConjuntion)
305 stringBuffer.append(" and (");
306 else
307 stringBuffer.append("(");
308 stringBuffer.append("@exo:eventState='").append(state).append("'");
309 stringBuffer.append(")");
310 hasConjuntion = true;
311 }
312
313 if (!isSearchInAllCategories()) {
314 if (hasConjuntion)
315 stringBuffer.append(" and (");
316 else
317 stringBuffer.append("(");
318 for (int i = 0; i < categoryIds.length; i++) {
319 if (i == 0)
320 stringBuffer.append("@exo:eventCategoryId='").append(categoryIds[i]).append("'");
321 else
322 stringBuffer.append(" or @exo:eventCategoryId='").append(categoryIds[i]).append("'");
323 }
324 stringBuffer.append(")");
325 hasConjuntion = true;
326 }
327
328 if (calendarIds != null && calendarIds.length > 0) {
329 if (hasConjuntion)
330 stringBuffer.append(" and (");
331 else
332 stringBuffer.append("(");
333 for (int i = 0; i < calendarIds.length; i++) {
334 if (i == 0)
335 stringBuffer.append("@exo:calendarId='").append(calendarIds[i]).append("'");
336 else
337 stringBuffer.append(" or @exo:calendarId='").append(calendarIds[i]).append("'");
338 }
339 stringBuffer.append(")");
340 hasConjuntion = true;
341 }
342 if (filterCalendarIds != null && filterCalendarIds.length > 0) {
343 if (hasConjuntion)
344 stringBuffer.append(" and (");
345 else
346 stringBuffer.append("(");
347 for (int i = 0; i < filterCalendarIds.length; i++) {
348 if (i == 0)
349 stringBuffer.append("@exo:calendarId !='").append(filterCalendarIds[i]).append("'");
350 else
351 stringBuffer.append(" and @exo:calendarId !='").append(filterCalendarIds[i]).append("'");
352 }
353 stringBuffer.append(")");
354 hasConjuntion = true;
355 }
356
357 if (participants != null && participants.length > 0) {
358 if (hasConjuntion)
359 stringBuffer.append(" and (");
360 else
361 stringBuffer.append("(");
362 for (int i = 0; i < participants.length; i++) {
363 if (i == 0)
364 stringBuffer.append("@exo:participant='").append(participants[i]).append("'");
365 else
366 stringBuffer.append(" or @exo:participant='").append(participants[i]).append("'");
367 }
368 stringBuffer.append(")");
369 hasConjuntion = true;
370 }
371
372
373 if (fromDate != null && toDate != null) {
374 if (hasConjuntion)
375 stringBuffer.append(" and (");
376 else
377 stringBuffer.append("(");
378 stringBuffer.append("(");
379
380 stringBuffer.append("@exo:fromDateTime <= xs:dateTime('").append(ISO8601.format(fromDate)).append("') and ");
381 stringBuffer.append("@exo:toDateTime >= xs:dateTime('").append(ISO8601.format(toDate)).append("')");
382 stringBuffer.append(") or (");
383
384
385 stringBuffer.append("@exo:fromDateTime >= xs:dateTime('").append(ISO8601.format(fromDate)).append("') and ");
386 stringBuffer.append("@exo:fromDateTime <= xs:dateTime('").append(ISO8601.format(toDate)).append("')");
387 stringBuffer.append(") or (");
388
389
390 stringBuffer.append("@exo:toDateTime >= xs:dateTime('").append(ISO8601.format(fromDate)).append("') and ");
391 stringBuffer.append("@exo:toDateTime <= xs:dateTime('").append(ISO8601.format(toDate)).append("')");
392 stringBuffer.append(") or (");
393 stringBuffer.append("(not(@exo:repeatUntil) or @exo:repeatUntil >= xs:dateTime('" + ISO8601.format(fromDate) + "'))");
394 stringBuffer.append(" and (not(@exo:repeatFinishDate) or @exo:repeatFinishDate >= xs:dateTime('" + ISO8601.format(fromDate) + "'))");
395 stringBuffer.append(" and @exo:repeat != 'norepeat'");
396 stringBuffer.append(")");
397
398 stringBuffer.append(")");
399 hasConjuntion = true;
400 } else if (fromDate != null) {
401 if (hasConjuntion)
402 stringBuffer.append(" and (");
403 else
404 stringBuffer.append("(");
405
406
407
408
409 stringBuffer.append("@exo:fromDateTime >= xs:dateTime('").append(ISO8601.format(fromDate)).append("')");
410
411 stringBuffer.append(" or (");
412 stringBuffer.append("(not(@exo:repeatUntil) or @exo:repeatUntil >= xs:dateTime('" + ISO8601.format(fromDate) + "'))");
413 stringBuffer.append(" and (not(@exo:repeatFinishDate) or @exo:repeatFinishDate >= xs:dateTime('" + ISO8601.format(fromDate) + "'))");
414 stringBuffer.append(" and @exo:repeat != 'norepeat'");
415 stringBuffer.append(")");
416
417 stringBuffer.append(")");
418 hasConjuntion = true;
419 } else if (toDate != null) {
420 if (hasConjuntion)
421 stringBuffer.append(" and (");
422 else
423 stringBuffer.append("(");
424
425
426
427
428
429
430 stringBuffer.append("@exo:toDateTime <= xs:dateTime('").append(ISO8601.format(toDate)).append("')");
431 stringBuffer.append(")");
432 hasConjuntion = true;
433 }
434
435 if (excludeRepeatEvent != null && excludeRepeatEvent) {
436 if (hasConjuntion) {
437 stringBuffer.append(" and ");
438 }
439 stringBuffer.append(" not(@jcr:mixinTypes='exo:repeatCalendarEvent' and @exo:repeat!='").append(CalendarEvent.RP_NOREPEAT).append("' and @exo:recurrenceId='')");
440 hasConjuntion = true;
441 }
442 stringBuffer.append("]");
443
444 if (orderBy != null && orderBy.length > 0 && orderType != null && orderType.length() > 0) {
445 for (int i = 0; i < orderBy.length; i++) {
446 if (i == 0)
447 stringBuffer.append(" order by @").append(orderBy[i].trim()).append(" ").append(orderType);
448 else
449 stringBuffer.append(", order by @").append(orderBy[i].trim()).append(" ").append(orderType);
450 }
451 hasConjuntion = true;
452 }
453 if (hasConjuntion)
454 queryString.append(stringBuffer.toString());
455 return queryString.toString();
456 }
457 }
458
459 public void setQueryType(String queryType) {
460 this.queryType = queryType;
461 }
462
463 public String getQueryType() {
464 return queryType;
465 }
466
467 public void setLimitedItems(int limitedItems) {
468 this.limitedItems = limitedItems;
469 }
470
471 public void setLimitedItems(long limitedItems) {
472 this.limitedItems = limitedItems;
473 }
474
475 public long getLimitedItems() {
476 return limitedItems;
477 }
478
479 public void setExcludeRepeatEvent(Boolean excludeRepeatEvent) {
480 this.excludeRepeatEvent = excludeRepeatEvent;
481 }
482
483 public Boolean getExcludeRepeatEvent() {
484 return excludeRepeatEvent;
485 }
486
487 protected String escapeContainsQuery(String s) {
488 StringBuilder buffer = new StringBuilder();
489 for (int i = 0; i < s.length(); i++) {
490 char ch = s.charAt(i);
491 if(ch == '~') {
492 if(i < s.length() - 1 && s.charAt(i + 1) == '~') {
493
494
495 } else {
496 String subString = s.substring(i + 1);
497 try {
498 float f = Float.parseFloat(subString);
499 if(f >= 0.0f && f <= 1.0f) {
500 buffer.append(ch);
501 } else {
502 buffer.append('\\').append(ch);
503 }
504 } catch (Exception ex) {
505 buffer.append('\\').append(ch);
506 }
507 }
508 } else if (ch == '"' || ch == '-' || ch == '\\'
509 || ch == '{' || ch == '}'
510 || ch == '(' || ch == ')'
511 || ch == '[' || ch == ']'
512 || ch == ':' || ch == '^' || ch == '!') {
513 buffer.append('\\').append(ch);
514 } else if (ch == '\'') {
515 buffer.append("''");
516 } else {
517 buffer.append(ch);
518 }
519 }
520 return buffer.toString();
521 }
522
523 protected String escapeLikeQuery(String s) {
524 StringBuilder buffer = new StringBuilder();
525 for (int i = 0; i < s.length(); i++) {
526 char ch = s.charAt(i);
527 if (ch == '%' || ch == '_' || ch == '\\') {
528 buffer.append('\\').append(ch);
529 } else if (ch == '\'') {
530 buffer.append("''");
531 } else {
532 buffer.append(ch);
533 }
534 }
535 return buffer.toString();
536 }
537 }