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