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