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