001
014
015 package com.liferay.portal.service.persistence.impl;
016
017 import com.liferay.portal.kernel.dao.orm.CustomSQLParam;
018 import com.liferay.portal.kernel.dao.orm.QueryPos;
019 import com.liferay.portal.kernel.dao.orm.QueryUtil;
020 import com.liferay.portal.kernel.dao.orm.SQLQuery;
021 import com.liferay.portal.kernel.dao.orm.Session;
022 import com.liferay.portal.kernel.dao.orm.Type;
023 import com.liferay.portal.kernel.dao.orm.WildcardMode;
024 import com.liferay.portal.kernel.exception.SystemException;
025 import com.liferay.portal.kernel.util.ArrayUtil;
026 import com.liferay.portal.kernel.util.GetterUtil;
027 import com.liferay.portal.kernel.util.OrderByComparator;
028 import com.liferay.portal.kernel.util.StringBundler;
029 import com.liferay.portal.kernel.util.StringPool;
030 import com.liferay.portal.kernel.util.StringUtil;
031 import com.liferay.portal.kernel.util.Validator;
032 import com.liferay.portal.kernel.workflow.WorkflowConstants;
033 import com.liferay.portal.model.Group;
034 import com.liferay.portal.model.Organization;
035 import com.liferay.portal.model.User;
036 import com.liferay.portal.model.impl.UserImpl;
037 import com.liferay.portal.service.GroupLocalServiceUtil;
038 import com.liferay.portal.service.persistence.OrganizationUtil;
039 import com.liferay.portal.service.persistence.RoleUtil;
040 import com.liferay.portal.service.persistence.UserFinder;
041 import com.liferay.portal.service.persistence.UserUtil;
042 import com.liferay.portal.util.PropsValues;
043 import com.liferay.util.dao.orm.CustomSQLUtil;
044
045 import java.io.Serializable;
046
047 import java.util.ArrayList;
048 import java.util.HashSet;
049 import java.util.Iterator;
050 import java.util.LinkedHashMap;
051 import java.util.List;
052 import java.util.Map;
053
054
061 public class UserFinderImpl
062 extends BasePersistenceImpl<User> implements UserFinder {
063
064 public static final String COUNT_BY_SOCIAL_USERS =
065 UserFinder.class.getName() + ".countBySocialUsers";
066
067 public static final String COUNT_BY_USER =
068 UserFinder.class.getName() + ".countByUser";
069
070 public static final String FIND_BY_NO_ANNOUNCEMENTS_DELIVERIES =
071 UserFinder.class.getName() + ".findByNoAnnouncementsDeliveries";
072
073 public static final String FIND_BY_NO_CONTACTS =
074 UserFinder.class.getName() + ".findByNoContacts";
075
076 public static final String FIND_BY_NO_GROUPS =
077 UserFinder.class.getName() + ".findByNoGroups";
078
079 public static final String FIND_BY_SOCIAL_USERS =
080 UserFinder.class.getName() + ".findBySocialUsers";
081
082 public static final String FIND_BY_C_FN_MN_LN_SN_EA_S =
083 UserFinder.class.getName() + ".findByC_FN_MN_LN_SN_EA_S";
084
085 public static final String JOIN_BY_CONTACT_TWITTER_SN =
086 UserFinder.class.getName() + ".joinByContactTwitterSN";
087
088 public static final String JOIN_BY_GROUPS_ORGS =
089 UserFinder.class.getName() + ".joinByGroupsOrgs";
090
091 public static final String JOIN_BY_GROUPS_USER_GROUPS =
092 UserFinder.class.getName() + ".joinByGroupsUserGroups";
093
094 public static final String JOIN_BY_NO_ORGANIZATIONS =
095 UserFinder.class.getName() + ".joinByNoOrganizations";
096
097 public static final String JOIN_BY_USER_GROUP_ROLE =
098 UserFinder.class.getName() + ".joinByUserGroupRole";
099
100 public static final String JOIN_BY_USERS_GROUPS =
101 UserFinder.class.getName() + ".joinByUsersGroups";
102
103 public static final String JOIN_BY_USERS_ORGS =
104 UserFinder.class.getName() + ".joinByUsersOrgs";
105
106 public static final String JOIN_BY_USERS_ORGS_TREE =
107 UserFinder.class.getName() + ".joinByUsersOrgsTree";
108
109 public static final String JOIN_BY_USERS_PASSWORD_POLICIES =
110 UserFinder.class.getName() + ".joinByUsersPasswordPolicies";
111
112 public static final String JOIN_BY_USERS_ROLES =
113 UserFinder.class.getName() + ".joinByUsersRoles";
114
115 public static final String JOIN_BY_USERS_TEAMS =
116 UserFinder.class.getName() + ".joinByUsersTeams";
117
118 public static final String JOIN_BY_USERS_USER_GROUPS =
119 UserFinder.class.getName() + ".joinByUsersUserGroups";
120
121 public static final String JOIN_BY_ANNOUNCEMENTS_DELIVERY_EMAIL_OR_SMS =
122 UserFinder.class.getName() + ".joinByAnnouncementsDeliveryEmailOrSms";
123
124 public static final String JOIN_BY_SOCIAL_MUTUAL_RELATION =
125 UserFinder.class.getName() + ".joinBySocialMutualRelation";
126
127 public static final String JOIN_BY_SOCIAL_MUTUAL_RELATION_TYPE =
128 UserFinder.class.getName() + ".joinBySocialMutualRelationType";
129
130 public static final String JOIN_BY_SOCIAL_RELATION =
131 UserFinder.class.getName() + ".joinBySocialRelation";
132
133 public static final String JOIN_BY_SOCIAL_RELATION_TYPE =
134 UserFinder.class.getName() + ".joinBySocialRelationType";
135
136 @Override
137 public int countBySocialUsers(
138 long companyId, long userId, int socialRelationType,
139 String socialRelationTypeComparator, int status) {
140
141 Session session = null;
142
143 try {
144 session = openSession();
145
146 String sql = CustomSQLUtil.get(COUNT_BY_SOCIAL_USERS);
147
148 sql = StringUtil.replace(
149 sql, "[$SOCIAL_RELATION_TYPE_COMPARATOR$]",
150 socialRelationTypeComparator.equals(StringPool.EQUAL) ?
151 StringPool.EQUAL : StringPool.NOT_EQUAL);
152
153 SQLQuery q = session.createSynchronizedSQLQuery(sql);
154
155 q.addScalar(COUNT_COLUMN_NAME, Type.LONG);
156
157 QueryPos qPos = QueryPos.getInstance(q);
158
159 qPos.add(userId);
160 qPos.add(socialRelationType);
161 qPos.add(companyId);
162 qPos.add(Boolean.FALSE);
163 qPos.add(status);
164
165 Iterator<Long> itr = q.iterate();
166
167 if (itr.hasNext()) {
168 Long count = itr.next();
169
170 if (count != null) {
171 return count.intValue();
172 }
173 }
174
175 return 0;
176 }
177 catch (Exception e) {
178 throw new SystemException(e);
179 }
180 finally {
181 closeSession(session);
182 }
183 }
184
185 @Override
186 public int countByUser(long userId, LinkedHashMap<String, Object> params) {
187 Session session = null;
188
189 try {
190 session = openSession();
191
192 String sql = CustomSQLUtil.get(COUNT_BY_USER);
193
194 sql = replaceJoinAndWhere(sql, params);
195
196 SQLQuery q = session.createSynchronizedSQLQuery(sql);
197
198 q.addScalar(COUNT_COLUMN_NAME, Type.LONG);
199
200 QueryPos qPos = QueryPos.getInstance(q);
201
202 setJoin(qPos, params);
203
204 qPos.add(userId);
205
206 Iterator<Long> itr = q.iterate();
207
208 if (itr.hasNext()) {
209 Long count = itr.next();
210
211 if (count != null) {
212 return count.intValue();
213 }
214 }
215
216 return 0;
217 }
218 catch (Exception e) {
219 throw new SystemException(e);
220 }
221 finally {
222 closeSession(session);
223 }
224 }
225
226 @Override
227 public int countByKeywords(
228 long companyId, String keywords, int status,
229 LinkedHashMap<String, Object> params) {
230
231 String[] firstNames = null;
232 String[] middleNames = null;
233 String[] lastNames = null;
234 String[] screenNames = null;
235 String[] emailAddresses = null;
236 boolean andOperator = false;
237
238 if (Validator.isNotNull(keywords)) {
239 firstNames = CustomSQLUtil.keywords(keywords);
240 middleNames = CustomSQLUtil.keywords(keywords);
241 lastNames = CustomSQLUtil.keywords(keywords);
242 screenNames = CustomSQLUtil.keywords(keywords);
243 emailAddresses = CustomSQLUtil.keywords(keywords);
244 }
245 else {
246 andOperator = true;
247 }
248
249 return countByC_FN_MN_LN_SN_EA_S(
250 companyId, firstNames, middleNames, lastNames, screenNames,
251 emailAddresses, status, params, andOperator);
252 }
253
254 @Override
255 public int countByC_FN_MN_LN_SN_EA_S(
256 long companyId, String firstName, String middleName, String lastName,
257 String screenName, String emailAddress, int status,
258 LinkedHashMap<String, Object> params, boolean andOperator) {
259
260 String[] firstNames = CustomSQLUtil.keywords(firstName);
261 String[] middleNames = CustomSQLUtil.keywords(middleName);
262 String[] lastNames = CustomSQLUtil.keywords(lastName);
263 String[] screenNames = CustomSQLUtil.keywords(screenName);
264 String[] emailAddresses = CustomSQLUtil.keywords(emailAddress);
265
266 return countByC_FN_MN_LN_SN_EA_S(
267 companyId, firstNames, middleNames, lastNames, screenNames,
268 emailAddresses, status, params, andOperator);
269 }
270
271 @Override
272 public int countByC_FN_MN_LN_SN_EA_S(
273 long companyId, String[] firstNames, String[] middleNames,
274 String[] lastNames, String[] screenNames, String[] emailAddresses,
275 int status, LinkedHashMap<String, Object> params, boolean andOperator) {
276
277 List<Long> userIds = doFindByC_FN_MN_LN_SN_EA_S(
278 companyId, firstNames, middleNames, lastNames, screenNames,
279 emailAddresses, status, params, andOperator, QueryUtil.ALL_POS,
280 QueryUtil.ALL_POS, null);
281
282 return userIds.size();
283 }
284
285 @Override
286 public List<User> findByKeywords(
287 long companyId, String keywords, int status,
288 LinkedHashMap<String, Object> params, int start, int end,
289 OrderByComparator<User> obc) {
290
291 String[] firstNames = null;
292 String[] middleNames = null;
293 String[] lastNames = null;
294 String[] screenNames = null;
295 String[] emailAddresses = null;
296 boolean andOperator = false;
297
298 if (params == null) {
299 params = _emptyLinkedHashMap;
300 }
301
302 if (Validator.isNotNull(keywords)) {
303 WildcardMode wildcardMode = (WildcardMode)GetterUtil.getObject(
304 params.get("wildcardMode"), WildcardMode.SURROUND);
305
306 firstNames = CustomSQLUtil.keywords(keywords, wildcardMode);
307 middleNames = CustomSQLUtil.keywords(keywords, wildcardMode);
308 lastNames = CustomSQLUtil.keywords(keywords, wildcardMode);
309 screenNames = CustomSQLUtil.keywords(keywords, wildcardMode);
310 emailAddresses = CustomSQLUtil.keywords(keywords, wildcardMode);
311 }
312 else {
313 andOperator = true;
314 }
315
316 return findByC_FN_MN_LN_SN_EA_S(
317 companyId, firstNames, middleNames, lastNames, screenNames,
318 emailAddresses, status, params, andOperator, start, end, obc);
319 }
320
321 @Override
322 public List<User> findByNoAnnouncementsDeliveries(String type) {
323 Session session = null;
324
325 try {
326 session = openSession();
327
328 String sql = CustomSQLUtil.get(FIND_BY_NO_ANNOUNCEMENTS_DELIVERIES);
329
330 SQLQuery q = session.createSynchronizedSQLQuery(sql);
331
332 q.addEntity("User_", UserImpl.class);
333
334 QueryPos qPos = QueryPos.getInstance(q);
335
336 qPos.add(type);
337
338 return q.list(true);
339 }
340 catch (Exception e) {
341 throw new SystemException(e);
342 }
343 finally {
344 closeSession(session);
345 }
346 }
347
348 @Override
349 public List<User> findByNoContacts() {
350 Session session = null;
351
352 try {
353 session = openSession();
354
355 String sql = CustomSQLUtil.get(FIND_BY_NO_CONTACTS);
356
357 SQLQuery q = session.createSynchronizedSQLQuery(sql);
358
359 q.addEntity("User_", UserImpl.class);
360
361 return q.list(true);
362 }
363 catch (Exception e) {
364 throw new SystemException(e);
365 }
366 finally {
367 closeSession(session);
368 }
369 }
370
371 @Override
372 public List<User> findByNoGroups() {
373 Session session = null;
374
375 try {
376 session = openSession();
377
378 String sql = CustomSQLUtil.get(FIND_BY_NO_GROUPS);
379
380 SQLQuery q = session.createSynchronizedSQLQuery(sql);
381
382 q.addEntity("User_", UserImpl.class);
383
384 return q.list(true);
385 }
386 catch (Exception e) {
387 throw new SystemException(e);
388 }
389 finally {
390 closeSession(session);
391 }
392 }
393
394 @Override
395 public List<User> findBySocialUsers(
396 long companyId, long userId, int socialRelationType,
397 String socialRelationTypeComparator, int status, int start, int end,
398 OrderByComparator<User> obc) {
399
400 Session session = null;
401
402 try {
403 session = openSession();
404
405 String sql = CustomSQLUtil.get(FIND_BY_SOCIAL_USERS);
406
407 sql = StringUtil.replace(
408 sql, "[$SOCIAL_RELATION_TYPE_COMPARATOR$]",
409 socialRelationTypeComparator.equals(StringPool.EQUAL) ?
410 StringPool.EQUAL : StringPool.NOT_EQUAL);
411
412 sql = CustomSQLUtil.replaceOrderBy(sql, obc);
413
414 SQLQuery q = session.createSynchronizedSQLQuery(sql);
415
416 q.addEntity("User_", UserImpl.class);
417
418 QueryPos qPos = QueryPos.getInstance(q);
419
420 qPos.add(userId);
421 qPos.add(socialRelationType);
422 qPos.add(companyId);
423 qPos.add(Boolean.FALSE);
424 qPos.add(status);
425
426 return (List<User>)QueryUtil.list(q, getDialect(), start, end);
427 }
428 catch (Exception e) {
429 throw new SystemException(e);
430 }
431 finally {
432 closeSession(session);
433 }
434 }
435
436 @Override
437 public List<User> findByC_FN_MN_LN_SN_EA_S(
438 long companyId, String firstName, String middleName, String lastName,
439 String screenName, String emailAddress, int status,
440 LinkedHashMap<String, Object> params, boolean andOperator, int start,
441 int end, OrderByComparator<User> obc) {
442
443 String[] firstNames = CustomSQLUtil.keywords(firstName);
444 String[] middleNames = CustomSQLUtil.keywords(middleName);
445 String[] lastNames = CustomSQLUtil.keywords(lastName);
446 String[] screenNames = CustomSQLUtil.keywords(screenName);
447 String[] emailAddresses = CustomSQLUtil.keywords(emailAddress);
448
449 return findByC_FN_MN_LN_SN_EA_S(
450 companyId, firstNames, middleNames, lastNames, screenNames,
451 emailAddresses, status, params, andOperator, start, end, obc);
452 }
453
454 @Override
455 public List<User> findByC_FN_MN_LN_SN_EA_S(
456 long companyId, String[] firstNames, String[] middleNames,
457 String[] lastNames, String[] screenNames, String[] emailAddresses,
458 int status, LinkedHashMap<String, Object> params, boolean andOperator,
459 int start, int end, OrderByComparator<User> obc) {
460
461 try {
462 List<Long> userIds = doFindByC_FN_MN_LN_SN_EA_S(
463 companyId, firstNames, middleNames, lastNames, screenNames,
464 emailAddresses, status, params, andOperator, start, end, obc);
465
466 List<User> users = new ArrayList<User>(userIds.size());
467
468 for (Long userId : userIds) {
469 User user = UserUtil.findByPrimaryKey(userId);
470
471 users.add(user);
472 }
473
474 return users;
475 }
476 catch (Exception e) {
477 throw new SystemException(e);
478 }
479 }
480
481 protected List<Long> doFindByC_FN_MN_LN_SN_EA_S(
482 long companyId, String[] firstNames, String[] middleNames,
483 String[] lastNames, String[] screenNames, String[] emailAddresses,
484 int status, LinkedHashMap<String, Object> params, boolean andOperator,
485 int start, int end, OrderByComparator<User> obc) {
486
487 firstNames = CustomSQLUtil.keywords(firstNames);
488 middleNames = CustomSQLUtil.keywords(middleNames);
489 lastNames = CustomSQLUtil.keywords(lastNames);
490 screenNames = CustomSQLUtil.keywords(screenNames);
491 emailAddresses = CustomSQLUtil.keywords(emailAddresses);
492
493 if (params == null) {
494 params = _emptyLinkedHashMap;
495 }
496
497 LinkedHashMap<String, Object> params1 = params;
498
499 LinkedHashMap<String, Object> params2 = null;
500
501 LinkedHashMap<String, Object> params3 = null;
502
503 LinkedHashMap<String, Object> params4 = null;
504
505 LinkedHashMap<String, Object> params5 = null;
506
507 LinkedHashMap<String, Object> params6 = null;
508
509 Long[] groupIds = null;
510
511 if (params.get("usersGroups") instanceof Long) {
512 Long groupId = (Long)params.get("usersGroups");
513
514 if (groupId > 0) {
515 groupIds = new Long[] {groupId};
516 }
517 }
518 else {
519 groupIds = (Long[])params.get("usersGroups");
520 }
521
522 Long[] roleIds = null;
523
524 if (params.get("usersRoles") instanceof Long) {
525 Long roleId = (Long)params.get("usersRoles");
526
527 if (roleId > 0) {
528 roleIds = new Long[] {roleId};
529 }
530 }
531 else {
532 roleIds = (Long[])params.get("usersRoles");
533 }
534
535 boolean inherit = GetterUtil.getBoolean(params.get("inherit"));
536 boolean socialRelationTypeUnionUserGroups = GetterUtil.getBoolean(
537 params.get("socialRelationTypeUnionUserGroups"));
538
539 if (ArrayUtil.isNotEmpty(groupIds) && inherit &&
540 !socialRelationTypeUnionUserGroups) {
541
542 List<Long> organizationIds = new ArrayList<Long>();
543 List<Long> siteGroupIds = new ArrayList<Long>();
544 List<Long> userGroupIds = new ArrayList<Long>();
545
546 for (long groupId : groupIds) {
547 Group group = GroupLocalServiceUtil.fetchGroup(groupId);
548
549 if (group == null) {
550 continue;
551 }
552
553 if (group.isOrganization()) {
554 organizationIds.add(group.getOrganizationId());
555 }
556 else if (group.isUserGroup()) {
557 userGroupIds.add(group.getClassPK());
558 }
559 else {
560 siteGroupIds.add(groupId);
561 }
562 }
563
564 if (!organizationIds.isEmpty()) {
565 params2 = new LinkedHashMap<String, Object>(params1);
566
567 params2.remove("usersGroups");
568
569 if (PropsValues.ORGANIZATIONS_MEMBERSHIP_STRICT) {
570 params2.put("usersOrgs", organizationIds);
571 }
572 else {
573 Map<Serializable, Organization> organizations =
574 OrganizationUtil.fetchByPrimaryKeys(
575 new HashSet<Serializable>(organizationIds));
576
577 params2.put(
578 "usersOrgsTree",
579 new ArrayList<Organization>(organizations.values()));
580 }
581 }
582
583 if (!siteGroupIds.isEmpty()) {
584 Long[] siteGroupIdsArray = siteGroupIds.toArray(
585 new Long[siteGroupIds.size()]);
586
587 params3 = new LinkedHashMap<String, Object>(params1);
588
589 params3.remove("usersGroups");
590
591 params3.put("groupsOrgs", siteGroupIdsArray);
592
593 params4 = new LinkedHashMap<String, Object>(params1);
594
595 params4.remove("usersGroups");
596
597 params4.put("groupsUserGroups", siteGroupIdsArray);
598 }
599
600 if (!userGroupIds.isEmpty()) {
601 params5 = new LinkedHashMap<String, Object>(params1);
602
603 params5.remove("usersGroups");
604
605 params5.put(
606 "usersUserGroups",
607 userGroupIds.toArray(new Long[userGroupIds.size()]));
608 }
609 }
610
611 if (ArrayUtil.isNotEmpty(roleIds) && inherit &&
612 !socialRelationTypeUnionUserGroups) {
613
614 List<Long> organizationIds = new ArrayList<Long>();
615 List<Long> siteGroupIds = new ArrayList<Long>();
616 List<Long> userGroupIds = new ArrayList<Long>();
617
618 for (long roleId : roleIds) {
619 List<Group> groups = RoleUtil.getGroups(roleId);
620
621 for (Group group : groups) {
622 if (group.isOrganization()) {
623 organizationIds.add(group.getOrganizationId());
624 }
625 else if (group.isUserGroup()) {
626 userGroupIds.add(group.getClassPK());
627 }
628 else {
629 siteGroupIds.add(group.getGroupId());
630 }
631 }
632 }
633
634 if (!organizationIds.isEmpty()) {
635 params2 = new LinkedHashMap<String, Object>(params1);
636
637 params2.remove("usersRoles");
638
639 if (PropsValues.ORGANIZATIONS_MEMBERSHIP_STRICT) {
640 params2.put(
641 "usersOrgs",
642 organizationIds.toArray(
643 new Long[organizationIds.size()]));
644 }
645 else {
646 Map<Serializable, Organization> organizations =
647 OrganizationUtil.fetchByPrimaryKeys(
648 new HashSet<Serializable>(organizationIds));
649
650 params2.put(
651 "usersOrgsTree",
652 new ArrayList<Organization>(organizations.values()));
653 }
654 }
655
656 if (!siteGroupIds.isEmpty()) {
657 Long[] siteGroupIdsArray = siteGroupIds.toArray(
658 new Long[siteGroupIds.size()]);
659
660 params3 = new LinkedHashMap<String, Object>(params1);
661
662 params3.remove("usersRoles");
663
664 params3.put("usersGroups", siteGroupIdsArray);
665
666 params4 = new LinkedHashMap<String, Object>(params1);
667
668 params4.remove("usersRoles");
669
670 params4.put("groupsOrgs", siteGroupIdsArray);
671
672 params5 = new LinkedHashMap<String, Object>(params1);
673
674 params5.remove("usersRoles");
675
676 params5.put("groupsUserGroups", siteGroupIdsArray);
677 }
678
679 if (!userGroupIds.isEmpty()) {
680 params6 = new LinkedHashMap<String, Object>(params1);
681
682 params6.remove("usersRoles");
683
684 params6.put(
685 "usersUserGroups",
686 userGroupIds.toArray(new Long[userGroupIds.size()]));
687 }
688 }
689
690 if (socialRelationTypeUnionUserGroups) {
691 boolean hasSocialRelationTypes = Validator.isNotNull(
692 params.get("socialRelationType"));
693
694 if (hasSocialRelationTypes && ArrayUtil.isNotEmpty(groupIds)) {
695 params2 = new LinkedHashMap<String, Object>(params1);
696
697 params1.remove("socialRelationType");
698
699 params2.remove("usersGroups");
700 }
701 }
702
703 Session session = null;
704
705 try {
706 session = openSession();
707
708 String sql = CustomSQLUtil.get(FIND_BY_C_FN_MN_LN_SN_EA_S);
709
710 sql = CustomSQLUtil.replaceKeywords(
711 sql, "lower(User_.firstName)", StringPool.LIKE, false,
712 firstNames);
713 sql = CustomSQLUtil.replaceKeywords(
714 sql, "lower(User_.middleName)", StringPool.LIKE, false,
715 middleNames);
716 sql = CustomSQLUtil.replaceKeywords(
717 sql, "lower(User_.lastName)", StringPool.LIKE, false,
718 lastNames);
719 sql = CustomSQLUtil.replaceKeywords(
720 sql, "lower(User_.screenName)", StringPool.LIKE, false,
721 screenNames);
722 sql = CustomSQLUtil.replaceKeywords(
723 sql, "lower(User_.emailAddress)", StringPool.LIKE, true,
724 emailAddresses);
725
726 if (status == WorkflowConstants.STATUS_ANY) {
727 sql = StringUtil.replace(sql, _STATUS_SQL, StringPool.BLANK);
728 }
729
730 StringBundler sb = new StringBundler(14);
731
732 sb.append(StringPool.OPEN_PARENTHESIS);
733 sb.append(replaceJoinAndWhere(sql, params1));
734 sb.append(StringPool.CLOSE_PARENTHESIS);
735
736 if (params2 != null) {
737 sb.append(" UNION (");
738 sb.append(replaceJoinAndWhere(sql, params2));
739 sb.append(StringPool.CLOSE_PARENTHESIS);
740 }
741
742 if (params3 != null) {
743 sb.append(" UNION (");
744 sb.append(replaceJoinAndWhere(sql, params3));
745 sb.append(StringPool.CLOSE_PARENTHESIS);
746 }
747
748 if (params4 != null) {
749 sb.append(" UNION (");
750 sb.append(replaceJoinAndWhere(sql, params4));
751 sb.append(StringPool.CLOSE_PARENTHESIS);
752 }
753
754 if (params5 != null) {
755 sb.append(" UNION (");
756 sb.append(replaceJoinAndWhere(sql, params5));
757 sb.append(StringPool.CLOSE_PARENTHESIS);
758 }
759
760 if (params6 != null) {
761 sb.append(" UNION (");
762 sb.append(replaceJoinAndWhere(sql, params6));
763 sb.append(StringPool.CLOSE_PARENTHESIS);
764 }
765
766 if (obc != null) {
767 sb.append(" ORDER BY ");
768 sb.append(obc.toString());
769 }
770
771 sql = sb.toString();
772
773 sql = CustomSQLUtil.replaceAndOperator(sql, andOperator);
774
775 SQLQuery q = session.createSynchronizedSQLQuery(sql);
776
777 q.addScalar("userId", Type.LONG);
778
779 QueryPos qPos = QueryPos.getInstance(q);
780
781 setJoin(qPos, params1);
782
783 qPos.add(companyId);
784 qPos.add(false);
785 qPos.add(firstNames, 2);
786 qPos.add(middleNames, 2);
787 qPos.add(lastNames, 2);
788 qPos.add(screenNames, 2);
789 qPos.add(emailAddresses, 2);
790
791 if (status != WorkflowConstants.STATUS_ANY) {
792 qPos.add(status);
793 }
794
795 if (params2 != null) {
796 setJoin(qPos, params2);
797
798 qPos.add(companyId);
799 qPos.add(false);
800 qPos.add(firstNames, 2);
801 qPos.add(middleNames, 2);
802 qPos.add(lastNames, 2);
803 qPos.add(screenNames, 2);
804 qPos.add(emailAddresses, 2);
805
806 if (status != WorkflowConstants.STATUS_ANY) {
807 qPos.add(status);
808 }
809 }
810
811 if (params3 != null) {
812 setJoin(qPos, params3);
813
814 qPos.add(companyId);
815 qPos.add(false);
816 qPos.add(firstNames, 2);
817 qPos.add(middleNames, 2);
818 qPos.add(lastNames, 2);
819 qPos.add(screenNames, 2);
820 qPos.add(emailAddresses, 2);
821
822 if (status != WorkflowConstants.STATUS_ANY) {
823 qPos.add(status);
824 }
825 }
826
827 if (params4 != null) {
828 setJoin(qPos, params4);
829
830 qPos.add(companyId);
831 qPos.add(false);
832 qPos.add(firstNames, 2);
833 qPos.add(middleNames, 2);
834 qPos.add(lastNames, 2);
835 qPos.add(screenNames, 2);
836 qPos.add(emailAddresses, 2);
837
838 if (status != WorkflowConstants.STATUS_ANY) {
839 qPos.add(status);
840 }
841 }
842
843 if (params5 != null) {
844 setJoin(qPos, params5);
845
846 qPos.add(companyId);
847 qPos.add(false);
848 qPos.add(firstNames, 2);
849 qPos.add(middleNames, 2);
850 qPos.add(lastNames, 2);
851 qPos.add(screenNames, 2);
852 qPos.add(emailAddresses, 2);
853
854 if (status != WorkflowConstants.STATUS_ANY) {
855 qPos.add(status);
856 }
857 }
858
859 if (params6 != null) {
860 setJoin(qPos, params6);
861
862 qPos.add(companyId);
863 qPos.add(false);
864 qPos.add(firstNames, 2);
865 qPos.add(middleNames, 2);
866 qPos.add(lastNames, 2);
867 qPos.add(screenNames, 2);
868 qPos.add(emailAddresses, 2);
869
870 if (status != WorkflowConstants.STATUS_ANY) {
871 qPos.add(status);
872 }
873 }
874
875 return (List<Long>)QueryUtil.list(q, getDialect(), start, end);
876 }
877 catch (Exception e) {
878 throw new SystemException(e);
879 }
880 finally {
881 closeSession(session);
882 }
883 }
884
885 protected String getJoin(LinkedHashMap<String, Object> params) {
886 if ((params == null) || params.isEmpty()) {
887 return StringPool.BLANK;
888 }
889
890 StringBundler sb = new StringBundler(params.size());
891
892 for (Map.Entry<String, Object> entry : params.entrySet()) {
893 String key = entry.getKey();
894
895 if (key.equals("expandoAttributes")) {
896 continue;
897 }
898
899 Object value = entry.getValue();
900
901 if (Validator.isNotNull(value)) {
902 sb.append(getJoin(key, value));
903 }
904 }
905
906 return sb.toString();
907 }
908
909 protected String getJoin(String key, Object value) {
910 String join = StringPool.BLANK;
911
912 if (key.equals("contactTwitterSn")) {
913 join = CustomSQLUtil.get(JOIN_BY_CONTACT_TWITTER_SN);
914 }
915 else if (key.equals("groupsOrgs")) {
916 join = CustomSQLUtil.get(JOIN_BY_GROUPS_ORGS);
917 }
918 else if (key.equals("groupsUserGroups")) {
919 join = CustomSQLUtil.get(JOIN_BY_GROUPS_USER_GROUPS);
920 }
921 else if (key.equals("noOrganizations")) {
922 join = CustomSQLUtil.get(JOIN_BY_NO_ORGANIZATIONS);
923 }
924 else if (key.equals("userGroupRole")) {
925 join = CustomSQLUtil.get(JOIN_BY_USER_GROUP_ROLE);
926 }
927 else if (key.equals("usersGroups")) {
928 join = CustomSQLUtil.get(JOIN_BY_USERS_GROUPS);
929 }
930 else if (key.equals("usersOrgs")) {
931 join = CustomSQLUtil.get(JOIN_BY_USERS_ORGS);
932 }
933 else if (key.equals("usersOrgsTree")) {
934 join = CustomSQLUtil.get(JOIN_BY_USERS_ORGS_TREE);
935 }
936 else if (key.equals("usersPasswordPolicies")) {
937 join = CustomSQLUtil.get(JOIN_BY_USERS_PASSWORD_POLICIES);
938 }
939 else if (key.equals("usersRoles")) {
940 join = CustomSQLUtil.get(JOIN_BY_USERS_ROLES);
941 }
942 else if (key.equals("usersTeams")) {
943 join = CustomSQLUtil.get(JOIN_BY_USERS_TEAMS);
944 }
945 else if (key.equals("usersUserGroups")) {
946 join = CustomSQLUtil.get(JOIN_BY_USERS_USER_GROUPS);
947 }
948 else if (key.equals("announcementsDeliveryEmailOrSms")) {
949 join = CustomSQLUtil.get(
950 JOIN_BY_ANNOUNCEMENTS_DELIVERY_EMAIL_OR_SMS);
951 }
952 else if (key.equals("socialMutualRelation")) {
953 join = CustomSQLUtil.get(JOIN_BY_SOCIAL_MUTUAL_RELATION);
954 }
955 else if (key.equals("socialMutualRelationType")) {
956 join = CustomSQLUtil.get(JOIN_BY_SOCIAL_MUTUAL_RELATION_TYPE);
957 }
958 else if (key.equals("socialRelation")) {
959 join = CustomSQLUtil.get(JOIN_BY_SOCIAL_RELATION);
960 }
961 else if (key.equals("socialRelationType")) {
962 join = CustomSQLUtil.get(JOIN_BY_SOCIAL_RELATION_TYPE);
963 }
964 else if (value instanceof CustomSQLParam) {
965 CustomSQLParam customSQLParam = (CustomSQLParam)value;
966
967 join = customSQLParam.getSQL();
968 }
969
970 if (Validator.isNotNull(join)) {
971 int pos = join.indexOf("WHERE");
972
973 if (pos != -1) {
974 join = join.substring(0, pos);
975 }
976 }
977
978 return join;
979 }
980
981 protected String getWhere(LinkedHashMap<String, Object> params) {
982 if ((params == null) || params.isEmpty()) {
983 return StringPool.BLANK;
984 }
985
986 StringBundler sb = new StringBundler(params.size());
987
988 for (Map.Entry<String, Object> entry : params.entrySet()) {
989 String key = entry.getKey();
990
991 if (key.equals("expandoAttributes")) {
992 continue;
993 }
994
995 Object value = entry.getValue();
996
997 if (Validator.isNotNull(value)) {
998 sb.append(getWhere(key, value));
999 }
1000 }
1001
1002 return sb.toString();
1003 }
1004
1005 protected String getWhere(String key, Object value) {
1006 String join = StringPool.BLANK;
1007
1008 if (key.equals("contactTwitterSn")) {
1009 join = CustomSQLUtil.get(JOIN_BY_CONTACT_TWITTER_SN);
1010 }
1011 else if (key.equals("groupsOrgs")) {
1012 Long[] groupIds = (Long[])value;
1013
1014 join = CustomSQLUtil.get(JOIN_BY_GROUPS_ORGS);
1015
1016 if (groupIds.length > 1) {
1017 StringBundler sb = new StringBundler(groupIds.length * 2 + 1);
1018
1019 sb.append("Groups_Orgs.groupId IN (");
1020
1021 for (long groupId : groupIds) {
1022 sb.append(groupId);
1023 sb.append(StringPool.COMMA);
1024 }
1025
1026 sb.setIndex(sb.index() - 1);
1027
1028 sb.append(StringPool.CLOSE_PARENTHESIS);
1029
1030 join = StringUtil.replace(
1031 join, "Groups_Orgs.groupId = ?", sb.toString());
1032 }
1033 else {
1034 join = StringUtil.replace(
1035 join, "Groups_Orgs.groupId = ?",
1036 "Groups_Orgs.groupId = " + groupIds[0]);
1037 }
1038 }
1039 else if (key.equals("groupsUserGroups")) {
1040 Long[] groupIds = (Long[])value;
1041
1042 join = CustomSQLUtil.get(JOIN_BY_GROUPS_USER_GROUPS);
1043
1044 if (groupIds.length > 1) {
1045 StringBundler sb = new StringBundler(groupIds.length * 2 + 1);
1046
1047 sb.append("Groups_UserGroups.groupId IN (");
1048
1049 for (long groupId : groupIds) {
1050 sb.append(groupId);
1051 sb.append(StringPool.COMMA);
1052 }
1053
1054 sb.setIndex(sb.index() - 1);
1055
1056 sb.append(StringPool.CLOSE_PARENTHESIS);
1057
1058 join = StringUtil.replace(
1059 join, "Groups_UserGroups.groupId = ?", sb.toString());
1060 }
1061 else {
1062 join = StringUtil.replace(
1063 join, "Groups_UserGroups.groupId = ?",
1064 "Groups_UserGroups.groupId = " + groupIds[0]);
1065 }
1066 }
1067 else if (key.equals("noOrganizations")) {
1068 join = CustomSQLUtil.get(JOIN_BY_NO_ORGANIZATIONS);
1069 }
1070 else if (key.equals("userGroupRole")) {
1071 join = CustomSQLUtil.get(JOIN_BY_USER_GROUP_ROLE);
1072
1073 Long[] valueArray = (Long[])value;
1074
1075 Long groupId = valueArray[0];
1076
1077 if (Validator.isNull(groupId)) {
1078 join = StringUtil.replace(
1079 join, "(UserGroupRole.groupId = ?) AND", StringPool.BLANK);
1080 }
1081 }
1082 else if (key.equals("usersGroups")) {
1083 if (value instanceof Long) {
1084 join = CustomSQLUtil.get(JOIN_BY_USERS_GROUPS);
1085 }
1086 else if (value instanceof Long[]) {
1087 Long[] groupIds = (Long[])value;
1088
1089 if (groupIds.length > 1) {
1090 StringBundler sb = new StringBundler(
1091 groupIds.length * 2 + 1);
1092
1093 sb.append("WHERE (Users_Groups.groupId IN (");
1094
1095 for (long groupId : groupIds) {
1096 sb.append(groupId);
1097 sb.append(StringPool.COMMA);
1098 }
1099
1100 sb.setIndex(sb.index() - 1);
1101
1102 sb.append("))");
1103
1104 join = sb.toString();
1105 }
1106 else {
1107 join = "WHERE (Users_Groups.groupId = " + groupIds[0] + ")";
1108 }
1109 }
1110 }
1111 else if (key.equals("usersOrgs")) {
1112 if (value instanceof Long) {
1113 join = CustomSQLUtil.get(JOIN_BY_USERS_ORGS);
1114 }
1115 else if (value instanceof Long[]) {
1116 Long[] organizationIds = (Long[])value;
1117
1118 if (organizationIds.length > 1) {
1119 StringBundler sb = new StringBundler(
1120 organizationIds.length * 2 + 1);
1121
1122 sb.append("WHERE (Users_Orgs.organizationId IN (");
1123
1124 for (long organizationId : organizationIds) {
1125 sb.append(organizationId);
1126 sb.append(StringPool.COMMA);
1127 }
1128
1129 sb.setIndex(sb.index() - 1);
1130
1131 sb.append("))");
1132
1133 join = sb.toString();
1134 }
1135 else {
1136 join =
1137 "WHERE (Users_Orgs.organizationId = " +
1138 organizationIds[0] + ")";
1139 }
1140 }
1141 }
1142 else if (key.equals("usersOrgsTree")) {
1143 List<Organization> organizationsTree = (List<Organization>)value;
1144
1145 int size = organizationsTree.size();
1146
1147 if (size > 0) {
1148 StringBundler sb = new StringBundler(size * 4 + 1);
1149
1150 sb.append("WHERE (");
1151
1152 for (Organization organization : organizationsTree) {
1153 sb.append("(Organization_.treePath LIKE '%/");
1154 sb.append(organization.getOrganizationId());
1155 sb.append("/%')");
1156 sb.append(" OR ");
1157 }
1158
1159 sb.setIndex(sb.index() - 1);
1160
1161 sb.append(StringPool.CLOSE_PARENTHESIS);
1162
1163 join = sb.toString();
1164 }
1165 else {
1166 join = "WHERE (Organization_.treePath LIKE '%/ /%')";
1167 }
1168 }
1169 else if (key.equals("usersPasswordPolicies")) {
1170 join = CustomSQLUtil.get(JOIN_BY_USERS_PASSWORD_POLICIES);
1171 }
1172 else if (key.equals("usersRoles")) {
1173 join = CustomSQLUtil.get(JOIN_BY_USERS_ROLES);
1174 }
1175 else if (key.equals("usersTeams")) {
1176 join = CustomSQLUtil.get(JOIN_BY_USERS_TEAMS);
1177 }
1178 else if (key.equals("usersUserGroups")) {
1179 if (value instanceof Long) {
1180 join = CustomSQLUtil.get(JOIN_BY_USERS_USER_GROUPS);
1181 }
1182 else if (value instanceof Long[]) {
1183 Long[] userGroupIds = (Long[])value;
1184
1185 if (userGroupIds.length > 1) {
1186 StringBundler sb = new StringBundler(
1187 userGroupIds.length * 2 + 1);
1188
1189 sb.append("WHERE (Users_UserGroups.userGroupId IN (");
1190
1191 for (long userGroupId : userGroupIds) {
1192 sb.append(userGroupId);
1193 sb.append(StringPool.COMMA);
1194 }
1195
1196 sb.setIndex(sb.index() - 1);
1197
1198 sb.append("))");
1199
1200 join = sb.toString();
1201 }
1202 else {
1203 join =
1204 "WHERE (Users_UserGroups.userGroupId = " +
1205 userGroupIds[0] + ")";
1206 }
1207 }
1208 }
1209 else if (key.equals("announcementsDeliveryEmailOrSms")) {
1210 join = CustomSQLUtil.get(
1211 JOIN_BY_ANNOUNCEMENTS_DELIVERY_EMAIL_OR_SMS);
1212 }
1213 else if (key.equals("socialMutualRelation")) {
1214 join = CustomSQLUtil.get(JOIN_BY_SOCIAL_MUTUAL_RELATION);
1215 }
1216 else if (key.equals("socialMutualRelationType")) {
1217 join = CustomSQLUtil.get(JOIN_BY_SOCIAL_MUTUAL_RELATION_TYPE);
1218 }
1219 else if (key.equals("socialRelation")) {
1220 join = CustomSQLUtil.get(JOIN_BY_SOCIAL_RELATION);
1221 }
1222 else if (key.equals("socialRelationType")) {
1223 if (value instanceof Long[]) {
1224 join = CustomSQLUtil.get(JOIN_BY_SOCIAL_RELATION_TYPE);
1225 }
1226 else if (value instanceof Long[][]) {
1227 StringBundler sb = new StringBundler();
1228
1229 sb.append("WHERE (SocialRelation.userId1 = ?) AND ");
1230 sb.append("(SocialRelation.type_ IN (");
1231
1232 Long[][] valueDoubleArray = (Long[][])value;
1233
1234 Long[] socialRelationTypes = valueDoubleArray[1];
1235
1236 for (int i = 0; i < socialRelationTypes.length; i++) {
1237 sb.append(StringPool.QUESTION);
1238
1239 if ((i + 1) < socialRelationTypes.length) {
1240 sb.append(StringPool.COMMA);
1241 }
1242 }
1243
1244 sb.append("))");
1245
1246 join = sb.toString();
1247 }
1248 }
1249 else if (value instanceof CustomSQLParam) {
1250 CustomSQLParam customSQLParam = (CustomSQLParam)value;
1251
1252 join = customSQLParam.getSQL();
1253 }
1254
1255 if (Validator.isNotNull(join)) {
1256 int pos = join.indexOf("WHERE");
1257
1258 if (pos != -1) {
1259 join = join.substring(pos + 5, join.length()).concat(" AND ");
1260 }
1261 else {
1262 join = StringPool.BLANK;
1263 }
1264 }
1265
1266 return join;
1267 }
1268
1269 protected String replaceJoinAndWhere(
1270 String sql, LinkedHashMap<String, Object> params) {
1271
1272 sql = StringUtil.replace(sql, "[$JOIN$]", getJoin(params));
1273 sql = StringUtil.replace(sql, "[$WHERE$]", getWhere(params));
1274
1275 return sql;
1276 }
1277
1278 protected void setJoin(
1279 QueryPos qPos, LinkedHashMap<String, Object> params) {
1280
1281 if (params == null) {
1282 return;
1283 }
1284
1285 for (Map.Entry<String, Object> entry : params.entrySet()) {
1286 String key = entry.getKey();
1287
1288 if (key.equals("expandoAttributes")) {
1289 continue;
1290 }
1291
1292 Object value = entry.getValue();
1293
1294 if (value instanceof Long) {
1295 Long valueLong = (Long)value;
1296
1297 if (Validator.isNotNull(valueLong)) {
1298 qPos.add(valueLong);
1299 }
1300 }
1301 else if (value instanceof Long[]) {
1302 if (key.equals("groupsOrgs") ||
1303 key.equals("groupsUserGroups") ||
1304 key.equals("usersGroups") || key.equals("usersOrgs") ||
1305 key.equals("usersUserGroups")) {
1306
1307 continue;
1308 }
1309
1310 Long[] valueArray = (Long[])value;
1311
1312 for (Long element : valueArray) {
1313 if (Validator.isNotNull(element)) {
1314 qPos.add(element);
1315 }
1316 }
1317 }
1318 else if (value instanceof Long[][]) {
1319 Long[][] valueDoubleArray = (Long[][])value;
1320
1321 for (Long[] valueArray : valueDoubleArray) {
1322 for (Long valueLong : valueArray) {
1323 qPos.add(valueLong);
1324 }
1325 }
1326 }
1327 else if (value instanceof String) {
1328 String valueString = (String)value;
1329
1330 if (Validator.isNotNull(valueString)) {
1331 qPos.add(valueString);
1332 }
1333 }
1334 else if (value instanceof String[]) {
1335 String[] valueArray = (String[])value;
1336
1337 for (String element : valueArray) {
1338 if (Validator.isNotNull(element)) {
1339 qPos.add(element);
1340 }
1341 }
1342 }
1343 else if (value instanceof CustomSQLParam) {
1344 CustomSQLParam customSQLParam = (CustomSQLParam)value;
1345
1346 customSQLParam.process(qPos);
1347 }
1348 }
1349 }
1350
1351 private static final String _STATUS_SQL = "AND (User_.status = ?)";
1352
1353 private LinkedHashMap<String, Object> _emptyLinkedHashMap =
1354 new LinkedHashMap<String, Object>(0);
1355
1356 }