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