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