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