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