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