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