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[] groupIds = null;
225    
226                    if (params.get("usersGroups") instanceof Long) {
227                            Long groupId = (Long)params.get("usersGroups");
228    
229                            if (groupId > 0) {
230                                    groupIds = new Long[] {groupId};
231                            }
232                    }
233                    else {
234                            groupIds = (Long[])params.get("usersGroups");
235                    }
236    
237                    boolean inherit = GetterUtil.getBoolean(params.get("inherit"));
238    
239                    boolean doUnion = Validator.isNotNull(groupIds) && inherit;
240    
241                    LinkedHashMap<String, Object> params1 = params;
242    
243                    LinkedHashMap<String, Object> params2 = null;
244    
245                    LinkedHashMap<String, Object> params3 = null;
246    
247                    if (doUnion) {
248                            params2 = new LinkedHashMap<String, Object>(params1);
249    
250                            params2.remove("usersGroups");
251    
252                            params3 = new LinkedHashMap<String, Object>(params1);
253    
254                            params3.remove("usersGroups");
255    
256                            List<Long> organizationIds = new ArrayList<Long>();
257                            List<Long> userGroupIds = new ArrayList<Long>();
258    
259                            for (long groupId : groupIds) {
260                                    Group group = GroupLocalServiceUtil.fetchGroup(groupId);
261    
262                                    if ((group != null) && group.isOrganization()) {
263                                            organizationIds.add(group.getOrganizationId());
264                                    }
265    
266                                    List<Organization> organizations = GroupUtil.getOrganizations(
267                                            groupId);
268    
269                                    for (Organization organization : organizations) {
270                                            organizationIds.add(organization.getOrganizationId());
271                                    }
272    
273                                    List<UserGroup> userGroups = GroupUtil.getUserGroups(groupId);
274    
275                                    for (int i = 0; i < userGroups.size(); i++) {
276                                            UserGroup userGroup = userGroups.get(i);
277    
278                                            userGroupIds.add(userGroup.getUserGroupId());
279                                    }
280                            }
281    
282                            params2.put(
283                                    "usersOrgs",
284                                    organizationIds.toArray(new Long[organizationIds.size()]));
285    
286                            params3.put(
287                                    "usersUserGroups",
288                                    userGroupIds.toArray(new Long[userGroupIds.size()]));
289                    }
290    
291                    Session session = null;
292    
293                    try {
294                            session = openSession();
295    
296                            Set<Long> userIds = new HashSet<Long>();
297    
298                            userIds.addAll(
299                                    countByC_FN_MN_LN_SN_EA_S(
300                                            session, companyId, firstNames, middleNames, lastNames,
301                                            screenNames, emailAddresses, status, params1, andOperator));
302    
303                            if (doUnion) {
304                                    userIds.addAll(
305                                            countByC_FN_MN_LN_SN_EA_S(
306                                                    session, companyId, firstNames, middleNames, lastNames,
307                                                    screenNames, emailAddresses, status, params2,
308                                                    andOperator));
309    
310                                    userIds.addAll(
311                                            countByC_FN_MN_LN_SN_EA_S(
312                                                    session, companyId, firstNames, middleNames, lastNames,
313                                                    screenNames, emailAddresses, status, params3,
314                                                    andOperator));
315                            }
316    
317                            return userIds.size();
318                    }
319                    catch (Exception e) {
320                            throw new SystemException(e);
321                    }
322                    finally {
323                            closeSession(session);
324                    }
325            }
326    
327            public List<User> findByKeywords(
328                            long companyId, String keywords, int status,
329                            LinkedHashMap<String, Object> params, int start, int end,
330                            OrderByComparator obc)
331                    throws SystemException {
332    
333                    String[] firstNames = null;
334                    String[] middleNames = null;
335                    String[] lastNames = null;
336                    String[] screenNames = null;
337                    String[] emailAddresses = null;
338                    boolean andOperator = false;
339    
340                    if (Validator.isNotNull(keywords)) {
341                            firstNames = CustomSQLUtil.keywords(keywords);
342                            middleNames = CustomSQLUtil.keywords(keywords);
343                            lastNames = CustomSQLUtil.keywords(keywords);
344                            screenNames = CustomSQLUtil.keywords(keywords);
345                            emailAddresses = CustomSQLUtil.keywords(keywords);
346                    }
347                    else {
348                            andOperator = true;
349                    }
350    
351                    return findByC_FN_MN_LN_SN_EA_S(
352                            companyId, firstNames, middleNames, lastNames, screenNames,
353                            emailAddresses, status, params, andOperator, start, end, obc);
354            }
355    
356            public List<User> findByNoAnnouncementsDeliveries(String type)
357                    throws SystemException {
358    
359                    Session session = null;
360    
361                    try {
362                            session = openSession();
363    
364                            String sql = CustomSQLUtil.get(FIND_BY_NO_ANNOUNCEMENTS_DELIVERIES);
365    
366                            SQLQuery q = session.createSQLQuery(sql);
367    
368                            q.addEntity("User_", UserImpl.class);
369    
370                            QueryPos qPos = QueryPos.getInstance(q);
371    
372                            qPos.add(type);
373    
374                            return q.list(true);
375                    }
376                    catch (Exception e) {
377                            throw new SystemException(e);
378                    }
379                    finally {
380                            closeSession(session);
381                    }
382            }
383    
384            public List<User> findByNoContacts() throws SystemException {
385                    Session session = null;
386    
387                    try {
388                            session = openSession();
389    
390                            String sql = CustomSQLUtil.get(FIND_BY_NO_CONTACTS);
391    
392                            SQLQuery q = session.createSQLQuery(sql);
393    
394                            q.addEntity("User_", UserImpl.class);
395    
396                            return q.list(true);
397                    }
398                    catch (Exception e) {
399                            throw new SystemException(e);
400                    }
401                    finally {
402                            closeSession(session);
403                    }
404            }
405    
406            public List<User> findByNoGroups() throws SystemException {
407                    Session session = null;
408    
409                    try {
410                            session = openSession();
411    
412                            String sql = CustomSQLUtil.get(FIND_BY_NO_GROUPS);
413    
414                            SQLQuery q = session.createSQLQuery(sql);
415    
416                            q.addEntity("User_", UserImpl.class);
417    
418                            return q.list(true);
419                    }
420                    catch (Exception e) {
421                            throw new SystemException(e);
422                    }
423                    finally {
424                            closeSession(session);
425                    }
426            }
427    
428            public List<User> findByC_FN_MN_LN_SN_EA_S(
429                            long companyId, String firstName, String middleName,
430                            String lastName, String screenName, String emailAddress, int status,
431                            LinkedHashMap<String, Object> params, boolean andOperator,
432                            int start, int end, OrderByComparator obc)
433                    throws SystemException {
434    
435                    String[] firstNames = CustomSQLUtil.keywords(firstName);
436                    String[] middleNames = CustomSQLUtil.keywords(middleName);
437                    String[] lastNames = CustomSQLUtil.keywords(lastName);
438                    String[] screenNames = CustomSQLUtil.keywords(screenName);
439                    String[] emailAddresses = CustomSQLUtil.keywords(emailAddress);
440    
441                    return findByC_FN_MN_LN_SN_EA_S(
442                            companyId, firstNames, middleNames, lastNames, screenNames,
443                            emailAddresses, status, params, andOperator, start, end, obc);
444            }
445    
446            public List<User> findByC_FN_MN_LN_SN_EA_S(
447                            long companyId, String[] firstNames, String[] middleNames,
448                            String[] lastNames, String[] screenNames, String[] emailAddresses,
449                            int status, LinkedHashMap<String, Object> params,
450                            boolean andOperator, int start, int end, OrderByComparator obc)
451                    throws SystemException {
452    
453                    firstNames = CustomSQLUtil.keywords(firstNames);
454                    middleNames = CustomSQLUtil.keywords(middleNames);
455                    lastNames = CustomSQLUtil.keywords(lastNames);
456                    screenNames = CustomSQLUtil.keywords(screenNames);
457                    emailAddresses = CustomSQLUtil.keywords(emailAddresses);
458    
459                    if (params == null) {
460                            params = _emptyLinkedHashMap;
461                    }
462    
463                    Long[] groupIds = null;
464    
465                    if (params.get("usersGroups") instanceof Long) {
466                            Long groupId = (Long)params.get("usersGroups");
467    
468                            if (groupId > 0) {
469                                    groupIds = new Long[] {groupId};
470                            }
471                    }
472                    else {
473                            groupIds = (Long[])params.get("usersGroups");
474                    }
475    
476                    boolean inherit = GetterUtil.getBoolean(params.get("inherit"));
477    
478                    boolean doUnion = Validator.isNotNull(groupIds) && inherit;
479    
480                    LinkedHashMap<String, Object> params1 = params;
481    
482                    LinkedHashMap<String, Object> params2 = null;
483    
484                    LinkedHashMap<String, Object> params3 = null;
485    
486                    if (doUnion) {
487                            params2 = new LinkedHashMap<String, Object>(params1);
488    
489                            params2.remove("usersGroups");
490    
491                            params3 = new LinkedHashMap<String, Object>(params1);
492    
493                            params3.remove("usersGroups");
494    
495                            List<Long> organizationIds = new ArrayList<Long>();
496                            List<Long> userGroupIds = new ArrayList<Long>();
497    
498                            for (long groupId : groupIds) {
499                                    Group group = GroupLocalServiceUtil.fetchGroup(groupId);
500    
501                                    if ((group != null) && group.isOrganization()) {
502                                            organizationIds.add(group.getOrganizationId());
503                                    }
504    
505                                    List<Organization> organizations = GroupUtil.getOrganizations(
506                                            groupId);
507    
508                                    for (Organization organization : organizations) {
509                                            organizationIds.add(organization.getOrganizationId());
510                                    }
511    
512                                    List<UserGroup> userGroups = GroupUtil.getUserGroups(groupId);
513    
514                                    for (int i = 0; i < userGroups.size(); i++) {
515                                            UserGroup userGroup = userGroups.get(i);
516    
517                                            userGroupIds.add(userGroup.getUserGroupId());
518                                    }
519                            }
520    
521                            params2.put(
522                                    "usersOrgs",
523                                    organizationIds.toArray(new Long[organizationIds.size()]));
524    
525                            params3.put(
526                                    "usersUserGroups",
527                                    userGroupIds.toArray(new Long[userGroupIds.size()]));
528                    }
529    
530                    Session session = null;
531    
532                    try {
533                            session = openSession();
534    
535                            String sql = CustomSQLUtil.get(FIND_BY_C_FN_MN_LN_SN_EA_S);
536    
537                            sql = CustomSQLUtil.replaceKeywords(
538                                    sql, "lower(User_.firstName)", StringPool.LIKE, false,
539                                    firstNames);
540                            sql = CustomSQLUtil.replaceKeywords(
541                                    sql, "lower(User_.middleName)", StringPool.LIKE, false,
542                                    middleNames);
543                            sql = CustomSQLUtil.replaceKeywords(
544                                    sql, "lower(User_.lastName)", StringPool.LIKE, false,
545                                    lastNames);
546                            sql = CustomSQLUtil.replaceKeywords(
547                                    sql, "lower(User_.screenName)", StringPool.LIKE, false,
548                                    screenNames);
549                            sql = CustomSQLUtil.replaceKeywords(
550                                    sql, "lower(User_.emailAddress)", StringPool.LIKE, true,
551                                    emailAddresses);
552    
553                            if (status == WorkflowConstants.STATUS_ANY) {
554                                    sql = StringUtil.replace(sql, _STATUS_SQL, StringPool.BLANK);
555                            }
556    
557                            StringBundler sb = new StringBundler();
558    
559                            sb.append(StringPool.OPEN_PARENTHESIS);
560                            sb.append(replaceJoinAndWhere(sql, params1));
561                            sb.append(StringPool.CLOSE_PARENTHESIS);
562    
563                            if (doUnion) {
564                                    sb.append(" UNION (");
565                                    sb.append(replaceJoinAndWhere(sql, params2));
566                                    sb.append(") UNION (");
567                                    sb.append(replaceJoinAndWhere(sql, params3));
568                                    sb.append(StringPool.CLOSE_PARENTHESIS);
569                            }
570    
571                            if (obc != null) {
572                                    sb.append(" ORDER BY ");
573                                    sb.append(obc.toString());
574                            }
575    
576                            sql = sb.toString();
577    
578                            sql = CustomSQLUtil.replaceAndOperator(sql, andOperator);
579    
580                            SQLQuery q = session.createSQLQuery(sql);
581    
582                            q.addScalar("userId", Type.LONG);
583    
584                            QueryPos qPos = QueryPos.getInstance(q);
585    
586                            setJoin(qPos, params1);
587    
588                            qPos.add(companyId);
589                            qPos.add(false);
590                            qPos.add(firstNames, 2);
591                            qPos.add(middleNames, 2);
592                            qPos.add(lastNames, 2);
593                            qPos.add(screenNames, 2);
594                            qPos.add(emailAddresses, 2);
595    
596                            if (status != WorkflowConstants.STATUS_ANY) {
597                                    qPos.add(status);
598                            }
599    
600                            if (doUnion) {
601                                    setJoin(qPos, params2);
602    
603                                    qPos.add(companyId);
604                                    qPos.add(false);
605                                    qPos.add(firstNames, 2);
606                                    qPos.add(middleNames, 2);
607                                    qPos.add(lastNames, 2);
608                                    qPos.add(screenNames, 2);
609                                    qPos.add(emailAddresses, 2);
610    
611                                    if (status != WorkflowConstants.STATUS_ANY) {
612                                            qPos.add(status);
613                                    }
614    
615                                    setJoin(qPos, params3);
616    
617                                    qPos.add(companyId);
618                                    qPos.add(false);
619                                    qPos.add(firstNames, 2);
620                                    qPos.add(middleNames, 2);
621                                    qPos.add(lastNames, 2);
622                                    qPos.add(screenNames, 2);
623                                    qPos.add(emailAddresses, 2);
624    
625                                    if (status != WorkflowConstants.STATUS_ANY) {
626                                            qPos.add(status);
627                                    }
628                            }
629    
630                            List<Long> userIds = (List<Long>)QueryUtil.list(
631                                    q, getDialect(), start, end);
632    
633                            List<User> users = new ArrayList<User>(userIds.size());
634    
635                            for (Long userId : userIds) {
636                                    User user = UserUtil.findByPrimaryKey(userId);
637    
638                                    users.add(user);
639                            }
640    
641                            return users;
642                    }
643                    catch (Exception e) {
644                            throw new SystemException(e);
645                    }
646                    finally {
647                            closeSession(session);
648                    }
649            }
650    
651            protected List<Long> countByC_FN_MN_LN_SN_EA_S(
652                    Session session, long companyId, String[] firstNames,
653                    String[] middleNames, String[] lastNames, String[] screenNames,
654                    String[] emailAddresses, int status,
655                    LinkedHashMap<String, Object> params, boolean andOperator) {
656    
657                    String sql = CustomSQLUtil.get(COUNT_BY_C_FN_MN_LN_SN_EA_S);
658    
659                    sql = CustomSQLUtil.replaceKeywords(
660                            sql, "lower(User_.firstName)", StringPool.LIKE, false, firstNames);
661                    sql = CustomSQLUtil.replaceKeywords(
662                            sql, "lower(User_.middleName)", StringPool.LIKE, false,
663                            middleNames);
664                    sql = CustomSQLUtil.replaceKeywords(
665                            sql, "lower(User_.lastName)", StringPool.LIKE, false, lastNames);
666                    sql = CustomSQLUtil.replaceKeywords(
667                            sql, "lower(User_.screenName)", StringPool.LIKE, false,
668                            screenNames);
669                    sql = CustomSQLUtil.replaceKeywords(
670                            sql, "lower(User_.emailAddress)", StringPool.LIKE, true,
671                            emailAddresses);
672    
673                    if (status == WorkflowConstants.STATUS_ANY) {
674                            sql = StringUtil.replace(sql, _STATUS_SQL, StringPool.BLANK);
675                    }
676    
677                    sql = replaceJoinAndWhere(sql, params);
678                    sql = CustomSQLUtil.replaceAndOperator(sql, andOperator);
679    
680                    SQLQuery q = session.createSQLQuery(sql);
681    
682                    q.addScalar("userId", Type.LONG);
683    
684                    QueryPos qPos = QueryPos.getInstance(q);
685    
686                    setJoin(qPos, params);
687    
688                    qPos.add(companyId);
689                    qPos.add(false);
690                    qPos.add(firstNames, 2);
691                    qPos.add(middleNames, 2);
692                    qPos.add(lastNames, 2);
693                    qPos.add(screenNames, 2);
694                    qPos.add(emailAddresses, 2);
695    
696                    if (status != WorkflowConstants.STATUS_ANY) {
697                            qPos.add(status);
698                    }
699    
700                    return q.list(true);
701            }
702    
703            protected String getJoin(LinkedHashMap<String, Object> params) {
704                    if ((params == null) || params.isEmpty()) {
705                            return StringPool.BLANK;
706                    }
707    
708                    StringBundler sb = new StringBundler(params.size());
709    
710                    for (Map.Entry<String, Object> entry : params.entrySet()) {
711                            String key = entry.getKey();
712    
713                            if (key.equals("expandoAttributes")) {
714                                    continue;
715                            }
716    
717                            Object value = entry.getValue();
718    
719                            if (Validator.isNotNull(value)) {
720                                    sb.append(getJoin(key, value));
721                            }
722                    }
723    
724                    return sb.toString();
725            }
726    
727            protected String getJoin(String key, Object value) {
728                    String join = StringPool.BLANK;
729    
730                    if (key.equals("contactTwitterSn")) {
731                            join = CustomSQLUtil.get(JOIN_BY_CONTACT_TWITTER_SN);
732                    }
733                    else if (key.equals("noOrganizations")) {
734                            join = CustomSQLUtil.get(JOIN_BY_NO_ORGANIZATIONS);
735                    }
736                    else if (key.equals("userGroupRole")) {
737                            join = CustomSQLUtil.get(JOIN_BY_USER_GROUP_ROLE);
738                    }
739                    else if (key.equals("usersGroups")) {
740                            join = CustomSQLUtil.get(JOIN_BY_USERS_GROUPS);
741                    }
742                    else if (key.equals("usersOrgs")) {
743                            join = CustomSQLUtil.get(JOIN_BY_USERS_ORGS);
744                    }
745                    else if (key.equals("usersOrgsTree")) {
746                            join = CustomSQLUtil.get(JOIN_BY_USERS_ORGS_TREE);
747                    }
748                    else if (key.equals("usersPasswordPolicies")) {
749                            join = CustomSQLUtil.get(JOIN_BY_USERS_PASSWORD_POLICIES);
750                    }
751                    else if (key.equals("usersRoles")) {
752                            join = CustomSQLUtil.get(JOIN_BY_USERS_ROLES);
753                    }
754                    else if (key.equals("usersTeams")) {
755                            join = CustomSQLUtil.get(JOIN_BY_USERS_TEAMS);
756                    }
757                    else if (key.equals("usersUserGroups")) {
758                            join = CustomSQLUtil.get(JOIN_BY_USERS_USER_GROUPS);
759                    }
760                    else if (key.equals("announcementsDeliveryEmailOrSms")) {
761                            join = CustomSQLUtil.get(
762                                    JOIN_BY_ANNOUNCEMENTS_DELIVERY_EMAIL_OR_SMS);
763                    }
764                    else if (key.equals("socialMutualRelation")) {
765                            join = CustomSQLUtil.get(JOIN_BY_SOCIAL_MUTUAL_RELATION);
766                    }
767                    else if (key.equals("socialMutualRelationType")) {
768                            join = CustomSQLUtil.get(JOIN_BY_SOCIAL_MUTUAL_RELATION_TYPE);
769                    }
770                    else if (key.equals("socialRelation")) {
771                            join = CustomSQLUtil.get(JOIN_BY_SOCIAL_RELATION);
772                    }
773                    else if (key.equals("socialRelationType")) {
774                            join = CustomSQLUtil.get(JOIN_BY_SOCIAL_RELATION_TYPE);
775                    }
776                    else if (value instanceof CustomSQLParam) {
777                            CustomSQLParam customSQLParam = (CustomSQLParam)value;
778    
779                            join = customSQLParam.getSQL();
780                    }
781    
782                    if (Validator.isNotNull(join)) {
783                            int pos = join.indexOf("WHERE");
784    
785                            if (pos != -1) {
786                                    join = join.substring(0, pos);
787                            }
788                    }
789    
790                    return join;
791            }
792    
793            protected String getWhere(LinkedHashMap<String, Object> params) {
794                    if ((params == null) || params.isEmpty()) {
795                            return StringPool.BLANK;
796                    }
797    
798                    StringBundler sb = new StringBundler(params.size());
799    
800                    for (Map.Entry<String, Object> entry : params.entrySet()) {
801                            String key = entry.getKey();
802    
803                            if (key.equals("expandoAttributes")) {
804                                    continue;
805                            }
806    
807                            Object value = entry.getValue();
808    
809                            if (Validator.isNotNull(value)) {
810                                    sb.append(getWhere(key, value));
811                            }
812                    }
813    
814                    return sb.toString();
815            }
816    
817            protected String getWhere(String key, Object value) {
818                    String join = StringPool.BLANK;
819    
820                    if (key.equals("contactTwitterSn")) {
821                            join = CustomSQLUtil.get(JOIN_BY_CONTACT_TWITTER_SN);
822                    }
823                    else if (key.equals("noOrganizations")) {
824                            join = CustomSQLUtil.get(JOIN_BY_NO_ORGANIZATIONS);
825                    }
826                    else if (key.equals("userGroupRole")) {
827                            join = CustomSQLUtil.get(JOIN_BY_USER_GROUP_ROLE);
828                    }
829                    else if (key.equals("usersGroups")) {
830                            if (value instanceof Long) {
831                                    join = CustomSQLUtil.get(JOIN_BY_USERS_GROUPS);
832                            }
833                            else if (value instanceof Long[]) {
834                                    Long[] groupIds = (Long[])value;
835    
836                                    if (groupIds.length == 0) {
837                                            join = "WHERE (Users_Groups.groupId = -1)";
838                                    }
839                                    else {
840                                            StringBundler sb = new StringBundler(
841                                                    groupIds.length * 2 + 1);
842    
843                                            sb.append("WHERE (");
844    
845                                            for (int i = 0; i < groupIds.length; i++) {
846                                                    sb.append("(Users_Groups.groupId = ?) ");
847    
848                                                    if ((i + 1) < groupIds.length) {
849                                                            sb.append("OR ");
850                                                    }
851                                            }
852    
853                                            sb.append(StringPool.CLOSE_PARENTHESIS);
854    
855                                            join = sb.toString();
856                                    }
857                            }
858                    }
859                    else if (key.equals("usersOrgs")) {
860                            if (value instanceof Long) {
861                                    join = CustomSQLUtil.get(JOIN_BY_USERS_ORGS);
862                            }
863                            else if (value instanceof Long[]) {
864                                    Long[] organizationIds = (Long[])value;
865    
866                                    if (organizationIds.length == 0) {
867                                            join = "WHERE (Users_Orgs.organizationId = -1)";
868                                    }
869                                    else {
870                                            StringBundler sb = new StringBundler(
871                                                    organizationIds.length * 2 + 1);
872    
873                                            sb.append("WHERE (");
874    
875                                            for (int i = 0; i < organizationIds.length; i++) {
876                                                    sb.append("(Users_Orgs.organizationId = ?) ");
877    
878                                                    if ((i + 1) < organizationIds.length) {
879                                                            sb.append("OR ");
880                                                    }
881                                            }
882    
883                                            sb.append(StringPool.CLOSE_PARENTHESIS);
884    
885                                            join = sb.toString();
886                                    }
887                            }
888                    }
889                    else if (key.equals("usersOrgsTree")) {
890                            List<Organization> organizationsTree = (List<Organization>)value;
891    
892                            int size = organizationsTree.size();
893    
894                            if (size > 0) {
895                                    StringBundler sb = new StringBundler(size * 2 + 1);
896    
897                                    sb.append("WHERE (");
898    
899                                    for (int i = 0; i < size; i++) {
900                                            sb.append("(Organization_.treePath LIKE ?) ");
901    
902                                            if ((i + 1) < size) {
903                                                    sb.append("OR ");
904                                            }
905                                    }
906    
907                                    sb.append(StringPool.CLOSE_PARENTHESIS);
908    
909                                    join = sb.toString();
910                            }
911                            else {
912                                    join = "WHERE (Organization_.treePath LIKE ?)";
913                            }
914                    }
915                    else if (key.equals("usersPasswordPolicies")) {
916                            join = CustomSQLUtil.get(JOIN_BY_USERS_PASSWORD_POLICIES);
917                    }
918                    else if (key.equals("usersRoles")) {
919                            join = CustomSQLUtil.get(JOIN_BY_USERS_ROLES);
920                    }
921                    else if (key.equals("usersTeams")) {
922                            join = CustomSQLUtil.get(JOIN_BY_USERS_TEAMS);
923                    }
924                    else if (key.equals("usersUserGroups")) {
925                            if (value instanceof Long) {
926                                    join = CustomSQLUtil.get(JOIN_BY_USERS_USER_GROUPS);
927                            }
928                            else if (value instanceof Long[]) {
929                                    Long[] userGroupIds = (Long[])value;
930    
931                                    if (userGroupIds.length == 0) {
932                                            join = "WHERE (Users_UserGroups.userGroupId = -1)";
933                                    }
934                                    else {
935                                            StringBundler sb = new StringBundler(
936                                                    userGroupIds.length * 2 + 1);
937    
938                                            sb.append("WHERE (");
939    
940                                            for (int i = 0; i < userGroupIds.length; i++) {
941                                                    sb.append("(Users_UserGroups.userGroupId = ?) ");
942    
943                                                    if ((i + 1) < userGroupIds.length) {
944                                                            sb.append("OR ");
945                                                    }
946                                            }
947    
948                                            sb.append(StringPool.CLOSE_PARENTHESIS);
949    
950                                            join = sb.toString();
951                                    }
952                            }
953                    }
954                    else if (key.equals("announcementsDeliveryEmailOrSms")) {
955                            join = CustomSQLUtil.get(
956                                    JOIN_BY_ANNOUNCEMENTS_DELIVERY_EMAIL_OR_SMS);
957                    }
958                    else if (key.equals("socialMutualRelation")) {
959                            join = CustomSQLUtil.get(JOIN_BY_SOCIAL_MUTUAL_RELATION);
960                    }
961                    else if (key.equals("socialMutualRelationType")) {
962                            join = CustomSQLUtil.get(JOIN_BY_SOCIAL_MUTUAL_RELATION_TYPE);
963                    }
964                    else if (key.equals("socialRelation")) {
965                            join = CustomSQLUtil.get(JOIN_BY_SOCIAL_RELATION);
966                    }
967                    else if (key.equals("socialRelationType")) {
968                            join = CustomSQLUtil.get(JOIN_BY_SOCIAL_RELATION_TYPE);
969                    }
970                    else if (value instanceof CustomSQLParam) {
971                            CustomSQLParam customSQLParam = (CustomSQLParam)value;
972    
973                            join = customSQLParam.getSQL();
974                    }
975    
976                    if (Validator.isNotNull(join)) {
977                            int pos = join.indexOf("WHERE");
978    
979                            if (pos != -1) {
980                                    join = join.substring(pos + 5, join.length()).concat(" AND ");
981                            }
982                            else {
983                                    join = StringPool.BLANK;
984                            }
985                    }
986    
987                    return join;
988            }
989    
990            protected String replaceJoinAndWhere(
991                    String sql, LinkedHashMap<String, Object> params) {
992    
993                    sql = StringUtil.replace(sql, "[$JOIN$]", getJoin(params));
994                    sql = StringUtil.replace(sql, "[$WHERE$]", getWhere(params));
995    
996                    return sql;
997            }
998    
999            protected void setJoin(
1000                    QueryPos qPos, LinkedHashMap<String, Object> params) {
1001    
1002                    if (params == null) {
1003                            return;
1004                    }
1005    
1006                    for (Map.Entry<String, Object> entry : params.entrySet()) {
1007                            String key = entry.getKey();
1008    
1009                            if (key.equals("expandoAttributes")) {
1010                                    continue;
1011                            }
1012    
1013                            Object value = entry.getValue();
1014    
1015                            if (key.equals("usersOrgsTree")) {
1016                                    List<Organization> organizationsTree =
1017                                            (List<Organization>)value;
1018    
1019                                    if (!organizationsTree.isEmpty()) {
1020                                            for (Organization organization : organizationsTree) {
1021                                                    StringBundler treePath = new StringBundler(5);
1022    
1023                                                    treePath.append(StringPool.PERCENT);
1024                                                    treePath.append(StringPool.SLASH);
1025                                                    treePath.append(organization.getOrganizationId());
1026                                                    treePath.append(StringPool.SLASH);
1027                                                    treePath.append(StringPool.PERCENT);
1028    
1029                                                    qPos.add(treePath.toString());
1030                                            }
1031                                    }
1032                                    else {
1033                                            qPos.add("%/ /%");
1034                                    }
1035                            }
1036                            else if (value instanceof Long) {
1037                                    Long valueLong = (Long)value;
1038    
1039                                    if (Validator.isNotNull(valueLong)) {
1040                                            qPos.add(valueLong);
1041                                    }
1042                            }
1043                            else if (value instanceof Long[]) {
1044                                    Long[] valueArray = (Long[])value;
1045    
1046                                    for (Long element : valueArray) {
1047                                            if (Validator.isNotNull(element)) {
1048                                                    qPos.add(element);
1049                                            }
1050                                    }
1051                            }
1052                            else if (value instanceof Long[][]) {
1053                                    Long[][] valueDoubleArray = (Long[][])value;
1054    
1055                                    for (Long[] valueArray : valueDoubleArray) {
1056                                            for (Long valueLong : valueArray) {
1057                                                    qPos.add(valueLong);
1058                                            }
1059                                    }
1060                            }
1061                            else if (value instanceof String) {
1062                                    String valueString = (String)value;
1063    
1064                                    if (Validator.isNotNull(valueString)) {
1065                                            qPos.add(valueString);
1066                                    }
1067                            }
1068                            else if (value instanceof String[]) {
1069                                    String[] valueArray = (String[])value;
1070    
1071                                    for (String element : valueArray) {
1072                                            if (Validator.isNotNull(element)) {
1073                                                    qPos.add(element);
1074                                            }
1075                                    }
1076                            }
1077                            else if (value instanceof CustomSQLParam) {
1078                                    CustomSQLParam customSQLParam = (CustomSQLParam)value;
1079    
1080                                    customSQLParam.process(qPos);
1081                            }
1082                    }
1083            }
1084    
1085            private static final String _STATUS_SQL = "AND (User_.status = ?)";
1086    
1087            private LinkedHashMap<String, Object> _emptyLinkedHashMap =
1088                    new LinkedHashMap<String, Object>(0);
1089    
1090    }