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