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