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