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