1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16 package org.kuali.rice.kew.actionlist.dao.impl;
17
18 import org.apache.commons.lang.StringUtils;
19 import org.apache.ojb.broker.PersistenceBroker;
20 import org.apache.ojb.broker.accesslayer.LookupException;
21 import org.apache.ojb.broker.query.Criteria;
22 import org.apache.ojb.broker.query.QueryByCriteria;
23 import org.kuali.rice.core.api.delegation.DelegationType;
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.doctype.bo.DocumentType;
31 import org.kuali.rice.kew.service.KEWServiceLocator;
32 import org.kuali.rice.kew.api.KewApiConstants;
33 import org.kuali.rice.kim.api.services.KimApiServiceLocator;
34 import org.springmodules.orm.ojb.PersistenceBrokerCallback;
35 import org.springmodules.orm.ojb.support.PersistenceBrokerDaoSupport;
36
37 import java.sql.Connection;
38 import java.sql.PreparedStatement;
39 import java.sql.ResultSet;
40 import java.sql.SQLException;
41 import java.sql.Timestamp;
42 import java.util.Calendar;
43 import java.util.Collection;
44 import java.util.Date;
45 import java.util.HashMap;
46 import java.util.Iterator;
47 import java.util.List;
48 import java.util.Map;
49
50
51
52
53
54
55 public class ActionListDAOOjbImpl extends PersistenceBrokerDaoSupport implements ActionListDAO {
56
57 private static final org.apache.log4j.Logger LOG = org.apache.log4j.Logger.getLogger(ActionListDAOOjbImpl.class);
58
59 public Collection<ActionItem> getActionList(String principalId, ActionListFilter filter) {
60 return getActionItemsInActionList(ActionItemActionListExtension.class, principalId, filter);
61
62
63
64
65
66
67
68
69
70
71
72
73 }
74
75 public Collection<ActionItem> getActionListForSingleDocument(String documentId) {
76 LOG.debug("getting action list for document id " + documentId);
77 Criteria crit = new Criteria();
78 crit.addEqualTo("documentId", documentId);
79 Collection<ActionItem> collection = this.getPersistenceBrokerTemplate().getCollectionByQuery(new QueryByCriteria(ActionItemActionListExtension.class, crit));
80 LOG.debug("found " + collection.size() + " action items for document id " + documentId);
81 return createActionListForRouteHeader(collection);
82 }
83
84 private Criteria setUpActionListCriteria(String principalId, ActionListFilter filter) {
85 LOG.debug("setting up Action List criteria");
86 Criteria crit = new Criteria();
87 boolean filterOn = false;
88 String filteredByItems = "";
89
90 if (filter.getActionRequestCd() != null && !"".equals(filter.getActionRequestCd().trim()) && !filter.getActionRequestCd().equals(KewApiConstants.ALL_CODE)) {
91 if (filter.isExcludeActionRequestCd()) {
92 crit.addNotEqualTo("actionRequestCd", filter.getActionRequestCd());
93 } else {
94 crit.addEqualTo("actionRequestCd", filter.getActionRequestCd());
95 }
96 filteredByItems += filteredByItems.length() > 0 ? ", " : "";
97 filteredByItems += "Action Requested";
98 }
99
100 if (filter.getCreateDateFrom() != null || filter.getCreateDateTo() != null) {
101 if (filter.isExcludeCreateDate()) {
102 if (filter.getCreateDateFrom() != null && filter.getCreateDateTo() != null) {
103 crit.addNotBetween("routeHeader.createDate", new Timestamp(beginningOfDay(filter.getCreateDateFrom()).getTime()), new Timestamp(endOfDay(filter.getCreateDateTo()).getTime()));
104 } else if (filter.getCreateDateFrom() != null && filter.getCreateDateTo() == null) {
105 crit.addLessOrEqualThan("routeHeader.createDate", new Timestamp(beginningOfDay(filter.getCreateDateFrom()).getTime()));
106 } else if (filter.getCreateDateFrom() == null && filter.getCreateDateTo() != null) {
107 crit.addGreaterOrEqualThan("routeHeader.createDate", new Timestamp(endOfDay(filter.getCreateDateTo()).getTime()));
108 }
109 } else {
110 if (filter.getCreateDateFrom() != null && filter.getCreateDateTo() != null) {
111 crit.addBetween("routeHeader.createDate", new Timestamp(beginningOfDay(filter.getCreateDateFrom()).getTime()), new Timestamp(endOfDay(filter.getCreateDateTo()).getTime()));
112 } else if (filter.getCreateDateFrom() != null && filter.getCreateDateTo() == null) {
113 crit.addGreaterOrEqualThan("routeHeader.createDate", new Timestamp(beginningOfDay(filter.getCreateDateFrom()).getTime()));
114 } else if (filter.getCreateDateFrom() == null && filter.getCreateDateTo() != null) {
115 crit.addLessOrEqualThan("routeHeader.createDate", new Timestamp(endOfDay(filter.getCreateDateTo()).getTime()));
116 }
117 }
118 filteredByItems += filteredByItems.length() > 0 ? ", " : "";
119 filteredByItems += "Date Created";
120 }
121
122 if (filter.getDocRouteStatus() != null && !"".equals(filter.getDocRouteStatus().trim()) && !filter.getDocRouteStatus().equals(KewApiConstants.ALL_CODE)) {
123 if (filter.isExcludeRouteStatus()) {
124 crit.addNotEqualTo("routeHeader.docRouteStatus", filter.getDocRouteStatus());
125 } else {
126 crit.addEqualTo("routeHeader.docRouteStatus", filter.getDocRouteStatus());
127 }
128 filteredByItems += filteredByItems.length() > 0 ? ", " : "";
129 filteredByItems += "Document Route Status";
130 }
131
132 if (filter.getDocumentTitle() != null && !"".equals(filter.getDocumentTitle().trim())) {
133 String docTitle = filter.getDocumentTitle();
134 if (docTitle.trim().endsWith("*")) {
135 docTitle = docTitle.substring(0, docTitle.length() - 1);
136 }
137
138 if (filter.isExcludeDocumentTitle()) {
139 crit.addNotLike("docTitle", "%" + docTitle + "%");
140 } else {
141 crit.addLike("docTitle", "%" + docTitle + "%");
142 }
143 filteredByItems += filteredByItems.length() > 0 ? ", " : "";
144 filteredByItems += "Document Title";
145 }
146
147 if (filter.getDocumentType() != null && !"".equals(filter.getDocumentType().trim())) {
148 if (filter.isExcludeDocumentType()) {
149 crit.addNotLike("docName", "%" + filter.getDocumentType() + "%");
150 } else {
151 String documentTypeName = filter.getDocumentType();
152 DocumentType documentType = KEWServiceLocator.getDocumentTypeService().findByName(documentTypeName);
153 if (documentType == null) {
154 crit.addLike("docName", "%" + filter.getDocumentType() + "%");
155 } else {
156
157 Criteria docTypeCrit = new Criteria();
158 constructDocumentTypeCriteria(docTypeCrit, documentType);
159 crit.addAndCriteria(docTypeCrit);
160 }
161 }
162 filteredByItems += filteredByItems.length() > 0 ? ", " : "";
163 filteredByItems += "Document Type";
164 }
165
166 if (filter.getLastAssignedDateFrom() != null || filter.getLastAssignedDateTo() != null) {
167 if (filter.isExcludeLastAssignedDate()) {
168 if (filter.getLastAssignedDateFrom() != null && filter.getLastAssignedDateTo() != null) {
169 crit.addNotBetween("dateAssigned", new Timestamp(beginningOfDay(filter.getLastAssignedDateFrom()).getTime()), new Timestamp(endOfDay(filter.getLastAssignedDateTo()).getTime()));
170 } else if (filter.getLastAssignedDateFrom() != null && filter.getLastAssignedDateTo() == null) {
171 crit.addLessOrEqualThan("dateAssigned", new Timestamp(beginningOfDay(filter.getLastAssignedDateFrom()).getTime()));
172 } else if (filter.getLastAssignedDateFrom() == null && filter.getLastAssignedDateTo() != null) {
173 crit.addGreaterOrEqualThan("dateAssigned", new Timestamp(endOfDay(filter.getLastAssignedDateTo()).getTime()));
174 }
175 } else {
176 if (filter.getLastAssignedDateFrom() != null && filter.getLastAssignedDateTo() != null) {
177 crit.addBetween("dateAssigned", new Timestamp(beginningOfDay(filter.getLastAssignedDateFrom()).getTime()), new Timestamp(endOfDay(filter.getLastAssignedDateTo()).getTime()));
178 } else if (filter.getLastAssignedDateFrom() != null && filter.getLastAssignedDateTo() == null) {
179 crit.addGreaterOrEqualThan("dateAssigned", new Timestamp(beginningOfDay(filter.getLastAssignedDateFrom()).getTime()));
180 } else if (filter.getLastAssignedDateFrom() == null && filter.getLastAssignedDateTo() != null) {
181 crit.addLessOrEqualThan("dateAssigned", new Timestamp(endOfDay(filter.getLastAssignedDateTo()).getTime()));
182 }
183 }
184 filteredByItems += filteredByItems.length() > 0 ? ", " : "";
185 filteredByItems += "Date Last Assigned";
186 }
187
188 filter.setGroupId(null);
189 if (filter.getGroupIdString() != null && !"".equals(filter.getGroupIdString().trim()) && !filter.getGroupIdString().trim().equals(KewApiConstants.NO_FILTERING)) {
190
191 filter.setGroupId(filter.getGroupIdString().trim());
192
193 if (filter.isExcludeGroupId()) {
194 Criteria critNotEqual = new Criteria();
195 critNotEqual.addNotEqualTo("groupId", filter.getGroupId());
196 Criteria critNull = new Criteria();
197 critNull.addIsNull("groupId");
198 critNotEqual.addOrCriteria(critNull);
199 crit.addAndCriteria(critNotEqual);
200 } else {
201 crit.addEqualTo("groupId", filter.getGroupId());
202 }
203 filteredByItems += filteredByItems.length() > 0 ? ", " : "";
204 filteredByItems += "Action Request Workgroup";
205 }
206
207 if (filteredByItems.length() > 0) {
208 filterOn = true;
209 }
210
211 boolean addedDelegationCriteria = false;
212 if (StringUtils.isBlank(filter.getDelegationType()) && StringUtils.isBlank(filter.getPrimaryDelegateId()) && StringUtils.isBlank(filter.getDelegatorId())) {
213 crit.addEqualTo("principalId", principalId);
214 addedDelegationCriteria = true;
215 } else if ((StringUtils.isNotBlank(filter.getDelegationType()) && DelegationType.PRIMARY.getCode().equals(filter.getDelegationType()))
216 || StringUtils.isNotBlank(filter.getPrimaryDelegateId())) {
217
218 if ((StringUtils.isBlank(filter.getPrimaryDelegateId())) || (filter.getPrimaryDelegateId().trim().equals(KewApiConstants.ALL_CODE))) {
219
220 Criteria userCrit = new Criteria();
221 Criteria groupCrit = new Criteria();
222 Criteria orCrit = new Criteria();
223 userCrit.addEqualTo("delegatorPrincipalId", principalId);
224 List<String> delegatorGroupIds = KimApiServiceLocator.getGroupService().getGroupIdsByPrincipalId(
225 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(KewApiConstants.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("delegatorPrincipalId", principalId);
245 List<String> delegatorGroupIds = KimApiServiceLocator.getGroupService().getGroupIdsByPrincipalId(
246 principalId);
247 if (delegatorGroupIds != null && !delegatorGroupIds.isEmpty()) {
248 groupCrit.addIn("delegatorGroupId", delegatorGroupIds);
249 }
250 orCrit.addOrCriteria(userCrit);
251 orCrit.addOrCriteria(groupCrit);
252 crit.addAndCriteria(orCrit);
253 crit.addEqualTo("delegationType", DelegationType.PRIMARY.getCode());
254 filter.setDelegationType(DelegationType.PRIMARY.getCode());
255 filter.setExcludeDelegationType(false);
256 addToFilterDescription(filteredByItems, "Primary Delegator Id");
257 addedDelegationCriteria = true;
258 filterOn = true;
259 }
260 }
261 if (!addedDelegationCriteria && ( (StringUtils.isNotBlank(filter.getDelegationType()) && DelegationType.SECONDARY.getCode().equals(filter.getDelegationType()))
262 || StringUtils.isNotBlank(filter.getDelegatorId()) )) {
263
264 crit.addEqualTo("principalId", principalId);
265 if (StringUtils.isBlank(filter.getDelegatorId())) {
266 filter.setDelegationType(DelegationType.SECONDARY.getCode());
267
268 if (!filter.isExcludeDelegationType()) {
269 crit.addEqualTo("delegationType", DelegationType.SECONDARY.getCode());
270 addToFilterDescription(filteredByItems, "Secondary Delegator Id");
271 addedDelegationCriteria = true;
272 filterOn = true;
273 }
274 } else if (filter.getDelegatorId().trim().equals(KewApiConstants.ALL_CODE)) {
275
276 crit.addEqualTo("delegationType", DelegationType.SECONDARY.getCode());
277 filter.setDelegationType(DelegationType.SECONDARY.getCode());
278 filter.setExcludeDelegationType(false);
279 addToFilterDescription(filteredByItems, "Secondary Delegator Id");
280 addedDelegationCriteria = true;
281 filterOn = true;
282 } else if (!filter.getDelegatorId().trim().equals(
283 KewApiConstants.DELEGATION_DEFAULT)) {
284
285 filter.setDelegationType(DelegationType.SECONDARY.getCode());
286 filter.setExcludeDelegationType(false);
287 Criteria userCrit = new Criteria();
288 Criteria groupCrit = new Criteria();
289 if (filter.isExcludeDelegatorId()) {
290 Criteria userNull = new Criteria();
291 userCrit.addNotEqualTo("delegatorPrincipalId", filter.getDelegatorId());
292 userNull.addIsNull("delegatorPrincipalId");
293 userCrit.addOrCriteria(userNull);
294 Criteria groupNull = new Criteria();
295 groupCrit.addNotEqualTo("delegatorGroupId", filter.getDelegatorId());
296 groupNull.addIsNull("delegatorGroupId");
297 groupCrit.addOrCriteria(groupNull);
298 crit.addAndCriteria(userCrit);
299 crit.addAndCriteria(groupCrit);
300 } else {
301 Criteria orCrit = new Criteria();
302 userCrit.addEqualTo("delegatorPrincipalId", filter.getDelegatorId());
303 groupCrit.addEqualTo("delegatorGroupId", filter.getDelegatorId());
304 orCrit.addOrCriteria(userCrit);
305 orCrit.addOrCriteria(groupCrit);
306 crit.addAndCriteria(orCrit);
307 }
308 addToFilterDescription(filteredByItems, "Secondary Delegator Id");
309 addedDelegationCriteria = true;
310 filterOn = true;
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
352 if (!addedDelegationCriteria) {
353 crit.addEqualTo("principalId", principalId);
354 filter.setDelegationType(DelegationType.SECONDARY.getCode());
355 filter.setExcludeDelegationType(true);
356 Criteria critNotEqual = new Criteria();
357 Criteria critNull = new Criteria();
358 critNotEqual.addNotEqualTo("delegationType", DelegationType.SECONDARY.getCode());
359 critNull.addIsNull("delegationType");
360 critNotEqual.addOrCriteria(critNull);
361 crit.addAndCriteria(critNotEqual);
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
526 if (! "".equals(filteredByItems)) {
527 filteredByItems = "Filtered by " + filteredByItems;
528 }
529 filter.setFilterLegend(filteredByItems);
530 filter.setFilterOn(filterOn);
531
532 LOG.debug("returning from Action List criteria");
533 return crit;
534 }
535
536 private void constructDocumentTypeCriteria(Criteria criteria, DocumentType documentType) {
537
538 Criteria docTypeBaseCrit = new Criteria();
539 docTypeBaseCrit.addEqualTo("docName", documentType.getName());
540 criteria.addOrCriteria(docTypeBaseCrit);
541 Collection children = documentType.getChildrenDocTypes();
542 if (children != null) {
543 for (Iterator iterator = children.iterator(); iterator.hasNext();) {
544 DocumentType childDocumentType = (DocumentType) iterator.next();
545 constructDocumentTypeCriteria(criteria, childDocumentType);
546 }
547 }
548 }
549
550 private void addToFilterDescription(String filterDescription, String labelToAdd) {
551 filterDescription += filterDescription.length() > 0 ? ", " : "";
552 filterDescription += labelToAdd;
553 }
554
555 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')";
556
557 public int getCount(final String workflowId) {
558 return (Integer)getPersistenceBrokerTemplate().execute(new PersistenceBrokerCallback() {
559 public Object doInPersistenceBroker(PersistenceBroker broker) {
560 PreparedStatement statement = null;
561 ResultSet resultSet = null;
562 try {
563 Connection connection = broker.serviceConnectionManager().getConnection();
564 statement = connection.prepareStatement(ACTION_LIST_COUNT_QUERY);
565 statement.setString(1, workflowId);
566 resultSet = statement.executeQuery();
567 if (!resultSet.next()) {
568 throw new WorkflowRuntimeException("Error determining Action List Count.");
569 }
570 return resultSet.getInt(1);
571 } catch (SQLException e) {
572 throw new WorkflowRuntimeException("Error determining Action List Count.", e);
573 } catch (LookupException e) {
574 throw new WorkflowRuntimeException("Error determining Action List Count.", e);
575 } finally {
576 if (statement != null) {
577 try {
578 statement.close();
579 } catch (SQLException e) {}
580 }
581 if (resultSet != null) {
582 try {
583 resultSet.close();
584 } catch (SQLException e) {}
585 }
586 }
587 }
588 });
589 }
590
591
592
593
594
595
596
597
598 private Collection<ActionItem> createActionListForUser(Collection<ActionItem> actionItems) {
599 Map<String, ActionItem> actionItemMap = new HashMap<String, ActionItem>();
600 ActionListPriorityComparator comparator = new ActionListPriorityComparator();
601 for (ActionItem potentialActionItem: actionItems) {
602 ActionItem existingActionItem = actionItemMap.get(potentialActionItem.getDocumentId());
603 if (existingActionItem == null || comparator.compare(potentialActionItem, existingActionItem) > 0) {
604 actionItemMap.put(potentialActionItem.getDocumentId(), potentialActionItem);
605 }
606 }
607 return actionItemMap.values();
608 }
609
610
611
612
613
614
615
616
617 private Collection<ActionItem> createActionListForRouteHeader(Collection<ActionItem> actionItems) {
618 Map<String, ActionItem> actionItemMap = new HashMap<String, ActionItem>();
619 ActionListPriorityComparator comparator = new ActionListPriorityComparator();
620 for (ActionItem potentialActionItem: actionItems) {
621 ActionItem existingActionItem = actionItemMap.get(potentialActionItem.getPrincipalId());
622 if (existingActionItem == null || comparator.compare(potentialActionItem, existingActionItem) > 0) {
623 actionItemMap.put(potentialActionItem.getPrincipalId(), potentialActionItem);
624 }
625 }
626 return actionItemMap.values();
627 }
628
629 private Collection<ActionItem> getActionItemsInActionList(Class objectsToRetrieve, String principalId, ActionListFilter filter) {
630 LOG.debug("getting action list for user " + principalId);
631 Criteria crit = null;
632 if (filter == null) {
633 crit = new Criteria();
634 crit.addEqualTo("principalId", principalId);
635 } else {
636 crit = setUpActionListCriteria(principalId, filter);
637 }
638 LOG.debug("running query to get action list for criteria " + crit);
639 Collection<ActionItem> collection = this.getPersistenceBrokerTemplate().getCollectionByQuery(new QueryByCriteria(objectsToRetrieve, crit));
640 LOG.debug("found " + collection.size() + " action items for user " + principalId);
641 return createActionListForUser(collection);
642 }
643
644 public Collection<ActionItem> getOutbox(String principalId, ActionListFilter filter) {
645 return getActionItemsInActionList(OutboxItemActionListExtension.class, principalId, filter);
646
647
648
649
650
651
652
653
654
655
656 }
657
658
659
660
661
662
663
664 public void removeOutboxItems(String principalId, List<String> outboxItems) {
665 Criteria crit = new Criteria();
666 crit.addIn("id", outboxItems);
667 getPersistenceBrokerTemplate().deleteByQuery(new QueryByCriteria(OutboxItemActionListExtension.class, crit));
668 }
669
670
671
672
673
674
675 public void saveOutboxItem(OutboxItemActionListExtension outboxItem) {
676 this.getPersistenceBrokerTemplate().store(outboxItem);
677 }
678
679
680
681
682
683
684 public OutboxItemActionListExtension getOutboxByDocumentId(String documentId) {
685 Criteria crit = new Criteria();
686 crit.addEqualTo("documentId", documentId);
687 return (OutboxItemActionListExtension)getPersistenceBrokerTemplate().getObjectByQuery(new QueryByCriteria(OutboxItemActionListExtension.class, crit));
688 }
689
690
691
692
693
694
695 public OutboxItemActionListExtension getOutboxByDocumentIdUserId(String documentId, String userId) {
696 Criteria crit = new Criteria();
697 crit.addEqualTo("documentId", documentId);
698 crit.addEqualTo("principalId", userId);
699 return (OutboxItemActionListExtension)getPersistenceBrokerTemplate().getObjectByQuery(new QueryByCriteria(OutboxItemActionListExtension.class, crit));
700 }
701
702 private Date beginningOfDay(Date date) {
703 Calendar cal = Calendar.getInstance();
704 cal.setTime(date);
705 cal.set(Calendar.HOUR_OF_DAY, 0);
706 cal.set(Calendar.MINUTE, 0);
707 cal.set(Calendar.SECOND, 0);
708 return cal.getTime();
709 }
710
711 private Date endOfDay(Date date) {
712 Calendar cal = Calendar.getInstance();
713 cal.setTime(date);
714 cal.set(Calendar.HOUR_OF_DAY, 23);
715 cal.set(Calendar.MINUTE, 59);
716 cal.set(Calendar.SECOND, 59);
717 return cal.getTime();
718 }
719
720 }