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