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