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