1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17 package org.kuali.rice.kew.actionlist.dao.impl;
18
19 import org.apache.commons.lang.StringUtils;
20 import org.apache.ojb.broker.PersistenceBroker;
21 import org.apache.ojb.broker.accesslayer.LookupException;
22 import org.apache.ojb.broker.query.Criteria;
23 import org.apache.ojb.broker.query.QueryByCriteria;
24 import org.kuali.rice.kew.actionitem.ActionItem;
25 import org.kuali.rice.kew.actionitem.ActionItemActionListExtension;
26 import org.kuali.rice.kew.actionitem.OutboxItemActionListExtension;
27 import org.kuali.rice.kew.actionlist.ActionListFilter;
28 import org.kuali.rice.kew.actionlist.dao.ActionListDAO;
29 import org.kuali.rice.kew.api.WorkflowRuntimeException;
30 import org.kuali.rice.kew.api.action.DelegationType;
31 import org.kuali.rice.kew.doctype.bo.DocumentType;
32 import org.kuali.rice.kew.service.KEWServiceLocator;
33 import org.kuali.rice.kew.util.KEWConstants;
34 import org.kuali.rice.kim.api.services.KimApiServiceLocator;
35 import org.springmodules.orm.ojb.PersistenceBrokerCallback;
36 import org.springmodules.orm.ojb.support.PersistenceBrokerDaoSupport;
37
38 import java.sql.Connection;
39 import java.sql.PreparedStatement;
40 import java.sql.ResultSet;
41 import java.sql.SQLException;
42 import java.sql.Timestamp;
43 import java.util.Calendar;
44 import java.util.Collection;
45 import java.util.Date;
46 import java.util.HashMap;
47 import java.util.Iterator;
48 import java.util.List;
49 import java.util.Map;
50
51
52
53
54
55
56 public class ActionListDAOOjbImpl extends PersistenceBrokerDaoSupport implements ActionListDAO {
57
58 private static final org.apache.log4j.Logger LOG = org.apache.log4j.Logger.getLogger(ActionListDAOOjbImpl.class);
59
60 public Collection<ActionItem> getActionList(String principalId, ActionListFilter filter) {
61 return getActionItemsInActionList(ActionItemActionListExtension.class, principalId, filter);
62
63
64
65
66
67
68
69
70
71
72
73
74 }
75
76 public Collection<ActionItem> getActionListForSingleDocument(String documentId) {
77 LOG.debug("getting action list for document id " + documentId);
78 Criteria crit = new Criteria();
79 crit.addEqualTo("documentId", documentId);
80 Collection<ActionItem> collection = this.getPersistenceBrokerTemplate().getCollectionByQuery(new QueryByCriteria(ActionItemActionListExtension.class, crit));
81 LOG.debug("found " + collection.size() + " action items for document id " + documentId);
82 return createActionListForRouteHeader(collection);
83 }
84
85 private Criteria setUpActionListCriteria(String principalId, ActionListFilter filter) {
86 LOG.debug("setting up Action List criteria");
87 Criteria crit = new Criteria();
88 boolean filterOn = false;
89 String filteredByItems = "";
90
91 if (filter.getActionRequestCd() != null && !"".equals(filter.getActionRequestCd().trim()) && !filter.getActionRequestCd().equals(KEWConstants.ALL_CODE)) {
92 if (filter.isExcludeActionRequestCd()) {
93 crit.addNotEqualTo("actionRequestCd", filter.getActionRequestCd());
94 } else {
95 crit.addEqualTo("actionRequestCd", filter.getActionRequestCd());
96 }
97 filteredByItems += filteredByItems.length() > 0 ? ", " : "";
98 filteredByItems += "Action Requested";
99 }
100
101 if (filter.getCreateDateFrom() != null || filter.getCreateDateTo() != null) {
102 if (filter.isExcludeCreateDate()) {
103 if (filter.getCreateDateFrom() != null && filter.getCreateDateTo() != null) {
104 crit.addNotBetween("routeHeader.createDate", new Timestamp(beginningOfDay(filter.getCreateDateFrom()).getTime()), new Timestamp(endOfDay(filter.getCreateDateTo()).getTime()));
105 } else if (filter.getCreateDateFrom() != null && filter.getCreateDateTo() == null) {
106 crit.addLessOrEqualThan("routeHeader.createDate", new Timestamp(beginningOfDay(filter.getCreateDateFrom()).getTime()));
107 } else if (filter.getCreateDateFrom() == null && filter.getCreateDateTo() != null) {
108 crit.addGreaterOrEqualThan("routeHeader.createDate", new Timestamp(endOfDay(filter.getCreateDateTo()).getTime()));
109 }
110 } else {
111 if (filter.getCreateDateFrom() != null && filter.getCreateDateTo() != null) {
112 crit.addBetween("routeHeader.createDate", new Timestamp(beginningOfDay(filter.getCreateDateFrom()).getTime()), new Timestamp(endOfDay(filter.getCreateDateTo()).getTime()));
113 } else if (filter.getCreateDateFrom() != null && filter.getCreateDateTo() == null) {
114 crit.addGreaterOrEqualThan("routeHeader.createDate", new Timestamp(beginningOfDay(filter.getCreateDateFrom()).getTime()));
115 } else if (filter.getCreateDateFrom() == null && filter.getCreateDateTo() != null) {
116 crit.addLessOrEqualThan("routeHeader.createDate", new Timestamp(endOfDay(filter.getCreateDateTo()).getTime()));
117 }
118 }
119 filteredByItems += filteredByItems.length() > 0 ? ", " : "";
120 filteredByItems += "Date Created";
121 }
122
123 if (filter.getDocRouteStatus() != null && !"".equals(filter.getDocRouteStatus().trim()) && !filter.getDocRouteStatus().equals(KEWConstants.ALL_CODE)) {
124 if (filter.isExcludeRouteStatus()) {
125 crit.addNotEqualTo("routeHeader.docRouteStatus", filter.getDocRouteStatus());
126 } else {
127 crit.addEqualTo("routeHeader.docRouteStatus", filter.getDocRouteStatus());
128 }
129 filteredByItems += filteredByItems.length() > 0 ? ", " : "";
130 filteredByItems += "Document Route Status";
131 }
132
133 if (filter.getDocumentTitle() != null && !"".equals(filter.getDocumentTitle().trim())) {
134 String docTitle = filter.getDocumentTitle();
135 if (docTitle.trim().endsWith("*")) {
136 docTitle = docTitle.substring(0, docTitle.length() - 1);
137 }
138
139 if (filter.isExcludeDocumentTitle()) {
140 crit.addNotLike("docTitle", "%" + docTitle + "%");
141 } else {
142 crit.addLike("docTitle", "%" + docTitle + "%");
143 }
144 filteredByItems += filteredByItems.length() > 0 ? ", " : "";
145 filteredByItems += "Document Title";
146 }
147
148 if (filter.getDocumentType() != null && !"".equals(filter.getDocumentType().trim())) {
149 if (filter.isExcludeDocumentType()) {
150 crit.addNotLike("docName", "%" + filter.getDocumentType() + "%");
151 } else {
152 String documentTypeName = filter.getDocumentType();
153 DocumentType documentType = KEWServiceLocator.getDocumentTypeService().findByName(documentTypeName);
154 if (documentType == null) {
155 crit.addLike("docName", "%" + filter.getDocumentType() + "%");
156 } else {
157
158 Criteria docTypeCrit = new Criteria();
159 constructDocumentTypeCriteria(docTypeCrit, documentType);
160 crit.addAndCriteria(docTypeCrit);
161 }
162 }
163 filteredByItems += filteredByItems.length() > 0 ? ", " : "";
164 filteredByItems += "Document Type";
165 }
166
167 if (filter.getLastAssignedDateFrom() != null || filter.getLastAssignedDateTo() != null) {
168 if (filter.isExcludeLastAssignedDate()) {
169 if (filter.getLastAssignedDateFrom() != null && filter.getLastAssignedDateTo() != null) {
170 crit.addNotBetween("dateAssigned", new Timestamp(beginningOfDay(filter.getLastAssignedDateFrom()).getTime()), new Timestamp(endOfDay(filter.getLastAssignedDateTo()).getTime()));
171 } else if (filter.getLastAssignedDateFrom() != null && filter.getLastAssignedDateTo() == null) {
172 crit.addLessOrEqualThan("dateAssigned", new Timestamp(beginningOfDay(filter.getLastAssignedDateFrom()).getTime()));
173 } else if (filter.getLastAssignedDateFrom() == null && filter.getLastAssignedDateTo() != null) {
174 crit.addGreaterOrEqualThan("dateAssigned", new Timestamp(endOfDay(filter.getLastAssignedDateTo()).getTime()));
175 }
176 } else {
177 if (filter.getLastAssignedDateFrom() != null && filter.getLastAssignedDateTo() != null) {
178 crit.addBetween("dateAssigned", new Timestamp(beginningOfDay(filter.getLastAssignedDateFrom()).getTime()), new Timestamp(endOfDay(filter.getLastAssignedDateTo()).getTime()));
179 } else if (filter.getLastAssignedDateFrom() != null && filter.getLastAssignedDateTo() == null) {
180 crit.addGreaterOrEqualThan("dateAssigned", new Timestamp(beginningOfDay(filter.getLastAssignedDateFrom()).getTime()));
181 } else if (filter.getLastAssignedDateFrom() == null && filter.getLastAssignedDateTo() != null) {
182 crit.addLessOrEqualThan("dateAssigned", new Timestamp(endOfDay(filter.getLastAssignedDateTo()).getTime()));
183 }
184 }
185 filteredByItems += filteredByItems.length() > 0 ? ", " : "";
186 filteredByItems += "Date Last Assigned";
187 }
188
189 filter.setGroupId(null);
190 if (filter.getGroupIdString() != null && !"".equals(filter.getGroupIdString().trim()) && !filter.getGroupIdString().trim().equals(KEWConstants.NO_FILTERING)) {
191
192 filter.setGroupId(filter.getGroupIdString().trim());
193
194 if (filter.isExcludeGroupId()) {
195 Criteria critNotEqual = new Criteria();
196 critNotEqual.addNotEqualTo("groupId", filter.getGroupId());
197 Criteria critNull = new Criteria();
198 critNull.addIsNull("groupId");
199 critNotEqual.addOrCriteria(critNull);
200 crit.addAndCriteria(critNotEqual);
201 } else {
202 crit.addEqualTo("groupId", filter.getGroupId());
203 }
204 filteredByItems += filteredByItems.length() > 0 ? ", " : "";
205 filteredByItems += "Action Request Workgroup";
206 }
207
208 if (filteredByItems.length() > 0) {
209 filterOn = true;
210 }
211
212 boolean addedDelegationCriteria = false;
213 if (StringUtils.isBlank(filter.getDelegationType()) && StringUtils.isBlank(filter.getPrimaryDelegateId()) && StringUtils.isBlank(filter.getDelegatorId())) {
214 crit.addEqualTo("principalId", principalId);
215 addedDelegationCriteria = true;
216 } else if ((StringUtils.isNotBlank(filter.getDelegationType()) && DelegationType.PRIMARY.getCode().equals(filter.getDelegationType()))
217 || StringUtils.isNotBlank(filter.getPrimaryDelegateId())) {
218
219 if ((StringUtils.isBlank(filter.getPrimaryDelegateId())) || (filter.getPrimaryDelegateId().trim().equals(KEWConstants.ALL_CODE))) {
220
221 Criteria userCrit = new Criteria();
222 Criteria groupCrit = new Criteria();
223 Criteria orCrit = new Criteria();
224 userCrit.addEqualTo("delegatorWorkflowId", principalId);
225 List<String> delegatorGroupIds = KimApiServiceLocator.getGroupService().getGroupIdsForPrincipal(principalId);
226 if (delegatorGroupIds != null && !delegatorGroupIds.isEmpty()) {
227 groupCrit.addIn("delegatorGroupId", delegatorGroupIds);
228 }
229 orCrit.addOrCriteria(userCrit);
230 orCrit.addOrCriteria(groupCrit);
231 crit.addAndCriteria(orCrit);
232 crit.addEqualTo("delegationType", DelegationType.PRIMARY.getCode());
233 filter.setDelegationType(DelegationType.PRIMARY.getCode());
234 filter.setExcludeDelegationType(false);
235 addToFilterDescription(filteredByItems, "Primary Delegator Id");
236 addedDelegationCriteria = true;
237 filterOn = true;
238 } else if (!filter.getPrimaryDelegateId().trim().equals(KEWConstants.PRIMARY_DELEGATION_DEFAULT)) {
239
240 crit.addEqualTo("principalId", filter.getPrimaryDelegateId());
241 Criteria userCrit = new Criteria();
242 Criteria groupCrit = new Criteria();
243 Criteria orCrit = new Criteria();
244 userCrit.addEqualTo("delegatorWorkflowId", principalId);
245 List<String> delegatorGroupIds = KimApiServiceLocator.getGroupService().getGroupIdsForPrincipal(principalId);
246 if (delegatorGroupIds != null && !delegatorGroupIds.isEmpty()) {
247 groupCrit.addIn("delegatorGroupId", delegatorGroupIds);
248 }
249 orCrit.addOrCriteria(userCrit);
250 orCrit.addOrCriteria(groupCrit);
251 crit.addAndCriteria(orCrit);
252 crit.addEqualTo("delegationType", DelegationType.PRIMARY.getCode());
253 filter.setDelegationType(DelegationType.PRIMARY.getCode());
254 filter.setExcludeDelegationType(false);
255 addToFilterDescription(filteredByItems, "Primary Delegator Id");
256 addedDelegationCriteria = true;
257 filterOn = true;
258 }
259 }
260 if (!addedDelegationCriteria && ( (StringUtils.isNotBlank(filter.getDelegationType()) && DelegationType.SECONDARY.getCode().equals(filter.getDelegationType()))
261 || StringUtils.isNotBlank(filter.getDelegatorId()) )) {
262
263 crit.addEqualTo("principalId", principalId);
264 if (StringUtils.isBlank(filter.getDelegatorId())) {
265 filter.setDelegationType(DelegationType.SECONDARY.getCode());
266
267 if (!filter.isExcludeDelegationType()) {
268 crit.addEqualTo("delegationType", DelegationType.SECONDARY.getCode());
269 addToFilterDescription(filteredByItems, "Secondary Delegator Id");
270 addedDelegationCriteria = true;
271 filterOn = true;
272 }
273 } else if (filter.getDelegatorId().trim().equals(KEWConstants.ALL_CODE)) {
274
275 crit.addEqualTo("delegationType", DelegationType.SECONDARY.getCode());
276 filter.setDelegationType(DelegationType.SECONDARY.getCode());
277 filter.setExcludeDelegationType(false);
278 addToFilterDescription(filteredByItems, "Secondary Delegator Id");
279 addedDelegationCriteria = true;
280 filterOn = true;
281 } else if (!filter.getDelegatorId().trim().equals(
282 KEWConstants.DELEGATION_DEFAULT)) {
283
284 filter.setDelegationType(DelegationType.SECONDARY.getCode());
285 filter.setExcludeDelegationType(false);
286 Criteria userCrit = new Criteria();
287 Criteria groupCrit = new Criteria();
288 if (filter.isExcludeDelegatorId()) {
289 Criteria userNull = new Criteria();
290 userCrit.addNotEqualTo("delegatorWorkflowId", filter.getDelegatorId());
291 userNull.addIsNull("delegatorWorkflowId");
292 userCrit.addOrCriteria(userNull);
293 Criteria groupNull = new Criteria();
294 groupCrit.addNotEqualTo("delegatorGroupId", filter.getDelegatorId());
295 groupNull.addIsNull("delegatorGroupId");
296 groupCrit.addOrCriteria(groupNull);
297 crit.addAndCriteria(userCrit);
298 crit.addAndCriteria(groupCrit);
299 } else {
300 Criteria orCrit = new Criteria();
301 userCrit.addEqualTo("delegatorWorkflowId", filter.getDelegatorId());
302 groupCrit.addEqualTo("delegatorGroupId", filter.getDelegatorId());
303 orCrit.addOrCriteria(userCrit);
304 orCrit.addOrCriteria(groupCrit);
305 crit.addAndCriteria(orCrit);
306 }
307 addToFilterDescription(filteredByItems, "Secondary Delegator Id");
308 addedDelegationCriteria = true;
309 filterOn = true;
310 }
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348 }
349
350
351 if (!addedDelegationCriteria) {
352 crit.addEqualTo("principalId", principalId);
353 filter.setDelegationType(DelegationType.SECONDARY.getCode());
354 filter.setExcludeDelegationType(true);
355 Criteria critNotEqual = new Criteria();
356 Criteria critNull = new Criteria();
357 critNotEqual.addNotEqualTo("delegationType", DelegationType.SECONDARY.getCode());
358 critNull.addIsNull("delegationType");
359 critNotEqual.addOrCriteria(critNull);
360 crit.addAndCriteria(critNotEqual);
361 }
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525 if (! "".equals(filteredByItems)) {
526 filteredByItems = "Filtered by " + filteredByItems;
527 }
528 filter.setFilterLegend(filteredByItems);
529 filter.setFilterOn(filterOn);
530
531 LOG.debug("returning from Action List criteria");
532 return crit;
533 }
534
535 private void constructDocumentTypeCriteria(Criteria criteria, DocumentType documentType) {
536
537 Criteria docTypeBaseCrit = new Criteria();
538 docTypeBaseCrit.addEqualTo("docName", documentType.getName());
539 criteria.addOrCriteria(docTypeBaseCrit);
540 Collection children = documentType.getChildrenDocTypes();
541 if (children != null) {
542 for (Iterator iterator = children.iterator(); iterator.hasNext();) {
543 DocumentType childDocumentType = (DocumentType) iterator.next();
544 constructDocumentTypeCriteria(criteria, childDocumentType);
545 }
546 }
547 }
548
549 private void addToFilterDescription(String filterDescription, String labelToAdd) {
550 filterDescription += filterDescription.length() > 0 ? ", " : "";
551 filterDescription += labelToAdd;
552 }
553
554 private static final String ACTION_LIST_COUNT_QUERY = "select count(distinct(ai.doc_hdr_id)) from krew_actn_itm_t ai where ai.PRNCPL_ID = ? and (ai.dlgn_typ is null or ai.dlgn_typ = 'P')";
555
556 public int getCount(final String workflowId) {
557 return (Integer)getPersistenceBrokerTemplate().execute(new PersistenceBrokerCallback() {
558 public Object doInPersistenceBroker(PersistenceBroker broker) {
559 PreparedStatement statement = null;
560 ResultSet resultSet = null;
561 try {
562 Connection connection = broker.serviceConnectionManager().getConnection();
563 statement = connection.prepareStatement(ACTION_LIST_COUNT_QUERY);
564 statement.setString(1, workflowId);
565 resultSet = statement.executeQuery();
566 if (!resultSet.next()) {
567 throw new WorkflowRuntimeException("Error determining Action List Count.");
568 }
569 return resultSet.getInt(1);
570 } catch (SQLException e) {
571 throw new WorkflowRuntimeException("Error determining Action List Count.", e);
572 } catch (LookupException e) {
573 throw new WorkflowRuntimeException("Error determining Action List Count.", e);
574 } finally {
575 if (statement != null) {
576 try {
577 statement.close();
578 } catch (SQLException e) {}
579 }
580 if (resultSet != null) {
581 try {
582 resultSet.close();
583 } catch (SQLException e) {}
584 }
585 }
586 }
587 });
588 }
589
590
591
592
593
594
595
596
597 private Collection<ActionItem> createActionListForUser(Collection<ActionItem> actionItems) {
598 Map<String, ActionItem> actionItemMap = new HashMap<String, ActionItem>();
599 ActionListPriorityComparator comparator = new ActionListPriorityComparator();
600 for (ActionItem potentialActionItem: actionItems) {
601 ActionItem existingActionItem = actionItemMap.get(potentialActionItem.getDocumentId());
602 if (existingActionItem == null || comparator.compare(potentialActionItem, existingActionItem) > 0) {
603 actionItemMap.put(potentialActionItem.getDocumentId(), potentialActionItem);
604 }
605 }
606 return actionItemMap.values();
607 }
608
609
610
611
612
613
614
615
616 private Collection<ActionItem> createActionListForRouteHeader(Collection<ActionItem> actionItems) {
617 Map<String, ActionItem> actionItemMap = new HashMap<String, ActionItem>();
618 ActionListPriorityComparator comparator = new ActionListPriorityComparator();
619 for (ActionItem potentialActionItem: actionItems) {
620 ActionItem existingActionItem = actionItemMap.get(potentialActionItem.getPrincipalId());
621 if (existingActionItem == null || comparator.compare(potentialActionItem, existingActionItem) > 0) {
622 actionItemMap.put(potentialActionItem.getPrincipalId(), potentialActionItem);
623 }
624 }
625 return actionItemMap.values();
626 }
627
628 private Collection<ActionItem> getActionItemsInActionList(Class objectsToRetrieve, String principalId, ActionListFilter filter) {
629 LOG.debug("getting action list for user " + principalId);
630 Criteria crit = null;
631 if (filter == null) {
632 crit = new Criteria();
633 crit.addEqualTo("principalId", principalId);
634 } else {
635 crit = setUpActionListCriteria(principalId, filter);
636 }
637 LOG.debug("running query to get action list for criteria " + crit);
638 Collection<ActionItem> collection = this.getPersistenceBrokerTemplate().getCollectionByQuery(new QueryByCriteria(objectsToRetrieve, crit));
639 LOG.debug("found " + collection.size() + " action items for user " + principalId);
640 return createActionListForUser(collection);
641 }
642
643 public Collection<ActionItem> getOutbox(String principalId, ActionListFilter filter) {
644 return getActionItemsInActionList(OutboxItemActionListExtension.class, principalId, filter);
645
646
647
648
649
650
651
652
653
654
655 }
656
657
658
659
660
661
662
663 public void removeOutboxItems(String principalId, List<String> outboxItems) {
664 Criteria crit = new Criteria();
665 crit.addIn("actionItemId", outboxItems);
666 getPersistenceBrokerTemplate().deleteByQuery(new QueryByCriteria(OutboxItemActionListExtension.class, crit));
667 }
668
669
670
671
672
673
674 public void saveOutboxItem(OutboxItemActionListExtension outboxItem) {
675 this.getPersistenceBrokerTemplate().store(outboxItem);
676 }
677
678
679
680
681
682
683 public OutboxItemActionListExtension getOutboxByDocumentId(String documentId) {
684 Criteria crit = new Criteria();
685 crit.addEqualTo("documentId", documentId);
686 return (OutboxItemActionListExtension)getPersistenceBrokerTemplate().getObjectByQuery(new QueryByCriteria(OutboxItemActionListExtension.class, crit));
687 }
688
689
690
691
692
693
694 public OutboxItemActionListExtension getOutboxByDocumentIdUserId(String documentId, String userId) {
695 Criteria crit = new Criteria();
696 crit.addEqualTo("documentId", documentId);
697 crit.addEqualTo("principalId", userId);
698 return (OutboxItemActionListExtension)getPersistenceBrokerTemplate().getObjectByQuery(new QueryByCriteria(OutboxItemActionListExtension.class, crit));
699 }
700
701 private Date beginningOfDay(Date date) {
702 Calendar cal = Calendar.getInstance();
703 cal.setTime(date);
704 cal.set(Calendar.HOUR_OF_DAY, 0);
705 cal.set(Calendar.MINUTE, 0);
706 cal.set(Calendar.SECOND, 0);
707 return cal.getTime();
708 }
709
710 private Date endOfDay(Date date) {
711 Calendar cal = Calendar.getInstance();
712 cal.setTime(date);
713 cal.set(Calendar.HOUR_OF_DAY, 23);
714 cal.set(Calendar.MINUTE, 59);
715 cal.set(Calendar.SECOND, 59);
716 return cal.getTime();
717 }
718
719 }