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