001    /**
002     * Copyright (c) 2000-2011 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.OrderByComparator;
025    import com.liferay.portal.kernel.util.StringBundler;
026    import com.liferay.portal.kernel.util.StringPool;
027    import com.liferay.portal.kernel.util.StringUtil;
028    import com.liferay.portal.kernel.util.Validator;
029    import com.liferay.portal.kernel.workflow.WorkflowConstants;
030    import com.liferay.portal.model.Organization;
031    import com.liferay.portal.model.User;
032    import com.liferay.portal.model.impl.UserImpl;
033    import com.liferay.portal.service.persistence.impl.BasePersistenceImpl;
034    import com.liferay.util.dao.orm.CustomSQLUtil;
035    
036    import java.util.Iterator;
037    import java.util.LinkedHashMap;
038    import java.util.List;
039    import java.util.Map;
040    
041    /**
042     * @author Brian Wing Shun Chan
043     * @author Jon Steer
044     * @author Raymond Augé
045     * @author Connor McKay
046     */
047    public class UserFinderImpl
048            extends BasePersistenceImpl<User> implements UserFinder {
049    
050            public static String COUNT_BY_USER =
051                    UserFinder.class.getName() + ".countByUser";
052    
053            public static String COUNT_BY_C_FN_MN_LN_SN_EA_S =
054                    UserFinder.class.getName() + ".countByC_FN_MN_LN_SN_EA_S";
055    
056            public static String FIND_BY_NO_ANNOUNCEMENTS_DELIVERIES =
057                    UserFinder.class.getName() + ".findByNoAnnouncementsDeliveries";
058    
059            public static String FIND_BY_NO_CONTACTS =
060                    UserFinder.class.getName() + ".findByNoContacts";
061    
062            public static String FIND_BY_NO_GROUPS =
063                    UserFinder.class.getName() + ".findByNoGroups";
064    
065            public static String FIND_BY_C_FN_MN_LN_SN_EA_S =
066                    UserFinder.class.getName() + ".findByC_FN_MN_LN_SN_EA_S";
067    
068            public static String JOIN_BY_CONTACT_TWITTER_SN =
069                    UserFinder.class.getName() + ".joinByContactTwitterSN";
070    
071            public static String JOIN_BY_NO_ORGANIZATIONS =
072                    UserFinder.class.getName() + ".joinByNoOrganizations";
073    
074            public static String JOIN_BY_PERMISSION =
075                    UserFinder.class.getName() + ".joinByPermission";
076    
077            public static String JOIN_BY_USER_GROUP_ROLE =
078                    UserFinder.class.getName() + ".joinByUserGroupRole";
079    
080            public static String JOIN_BY_USERS_GROUPS =
081                    UserFinder.class.getName() + ".joinByUsersGroups";
082    
083            public static String JOIN_BY_USERS_ORGS =
084                    UserFinder.class.getName() + ".joinByUsersOrgs";
085    
086            public static String JOIN_BY_USERS_ORGS_TREE =
087                    UserFinder.class.getName() + ".joinByUsersOrgsTree";
088    
089            public static String JOIN_BY_USERS_PASSWORD_POLICIES =
090                    UserFinder.class.getName() + ".joinByUsersPasswordPolicies";
091    
092            public static String JOIN_BY_USERS_ROLES =
093                    UserFinder.class.getName() + ".joinByUsersRoles";
094    
095            public static String JOIN_BY_USERS_TEAMS =
096                    UserFinder.class.getName() + ".joinByUsersTeams";
097    
098            public static String JOIN_BY_USERS_USER_GROUPS =
099                    UserFinder.class.getName() + ".joinByUsersUserGroups";
100    
101            public static String JOIN_BY_ANNOUNCEMENTS_DELIVERY_EMAIL_OR_SMS =
102                    UserFinder.class.getName() + ".joinByAnnouncementsDeliveryEmailOrSms";
103    
104            public static String JOIN_BY_SOCIAL_MUTUAL_RELATION =
105                    UserFinder.class.getName() + ".joinBySocialMutualRelation";
106    
107            public static String JOIN_BY_SOCIAL_MUTUAL_RELATION_TYPE =
108                    UserFinder.class.getName() + ".joinBySocialMutualRelationType";
109    
110            public static String JOIN_BY_SOCIAL_RELATION =
111                    UserFinder.class.getName() + ".joinBySocialRelation";
112    
113            public static String JOIN_BY_SOCIAL_RELATION_TYPE =
114                    UserFinder.class.getName() + ".joinBySocialRelationType";
115    
116            public int countByUser(long userId, LinkedHashMap<String, Object> params)
117                    throws SystemException {
118    
119                    Session session = null;
120    
121                    try {
122                            session = openSession();
123    
124                            String sql = CustomSQLUtil.get(COUNT_BY_USER);
125    
126                            sql = StringUtil.replace(sql, "[$JOIN$]", getJoin(params));
127                            sql = StringUtil.replace(sql, "[$WHERE$]", getWhere(params));
128    
129                            SQLQuery q = session.createSQLQuery(sql);
130    
131                            q.addScalar(COUNT_COLUMN_NAME, Type.LONG);
132    
133                            QueryPos qPos = QueryPos.getInstance(q);
134    
135                            setJoin(qPos, params);
136    
137                            qPos.add(userId);
138    
139                            Iterator<Long> itr = q.iterate();
140    
141                            if (itr.hasNext()) {
142                                    Long count = itr.next();
143    
144                                    if (count != null) {
145                                            return count.intValue();
146                                    }
147                            }
148    
149                            return 0;
150                    }
151                    catch (Exception e) {
152                            throw new SystemException(e);
153                    }
154                    finally {
155                            closeSession(session);
156                    }
157            }
158    
159            public int countByKeywords(
160                            long companyId, String keywords, int status,
161                            LinkedHashMap<String, Object> params)
162                    throws SystemException {
163    
164                    String[] firstNames = null;
165                    String[] middleNames = null;
166                    String[] lastNames = null;
167                    String[] screenNames = null;
168                    String[] emailAddresses = null;
169                    boolean andOperator = false;
170    
171                    if (Validator.isNotNull(keywords)) {
172                            firstNames = CustomSQLUtil.keywords(keywords);
173                            middleNames = CustomSQLUtil.keywords(keywords);
174                            lastNames = CustomSQLUtil.keywords(keywords);
175                            screenNames = CustomSQLUtil.keywords(keywords);
176                            emailAddresses = CustomSQLUtil.keywords(keywords);
177                    }
178                    else {
179                            andOperator = true;
180                    }
181    
182                    return countByC_FN_MN_LN_SN_EA_S(
183                            companyId, firstNames, middleNames, lastNames, screenNames,
184                            emailAddresses, status, params, andOperator);
185            }
186    
187            public int countByC_FN_MN_LN_SN_EA_S(
188                            long companyId, String firstName, String middleName,
189                            String lastName, String screenName, String emailAddress,
190                            int status, LinkedHashMap<String, Object> params,
191                            boolean andOperator)
192                    throws SystemException {
193    
194                    String[] firstNames = CustomSQLUtil.keywords(firstName);
195                    String[] middleNames = CustomSQLUtil.keywords(middleName);
196                    String[] lastNames = CustomSQLUtil.keywords(lastName);
197                    String[] screenNames = CustomSQLUtil.keywords(lastName);
198                    String[] emailAddresses = CustomSQLUtil.keywords(emailAddress);
199    
200                    return countByC_FN_MN_LN_SN_EA_S(
201                            companyId, firstNames, middleNames, lastNames, screenNames,
202                            emailAddresses, status, params, andOperator);
203            }
204    
205            public int countByC_FN_MN_LN_SN_EA_S(
206                            long companyId, String[] firstNames, String[] middleNames,
207                            String[] lastNames, String[] screenNames, String[] emailAddresses,
208                            int status, LinkedHashMap<String, Object> params,
209                            boolean andOperator)
210                    throws SystemException {
211    
212                    firstNames = CustomSQLUtil.keywords(firstNames);
213                    middleNames = CustomSQLUtil.keywords(middleNames);
214                    lastNames = CustomSQLUtil.keywords(lastNames);
215                    screenNames = CustomSQLUtil.keywords(screenNames);
216                    emailAddresses = CustomSQLUtil.keywords(emailAddresses);
217    
218                    Session session = null;
219    
220                    try {
221                            session = openSession();
222    
223                            String sql = CustomSQLUtil.get(COUNT_BY_C_FN_MN_LN_SN_EA_S);
224    
225                            sql = CustomSQLUtil.replaceKeywords(
226                                    sql, "lower(User_.firstName)", StringPool.LIKE, false,
227                                    firstNames);
228                            sql = CustomSQLUtil.replaceKeywords(
229                                    sql, "lower(User_.middleName)", StringPool.LIKE, false,
230                                    middleNames);
231                            sql = CustomSQLUtil.replaceKeywords(
232                                    sql, "lower(User_.lastName)", StringPool.LIKE, false,
233                                    lastNames);
234                            sql = CustomSQLUtil.replaceKeywords(
235                                    sql, "lower(User_.screenName)", StringPool.LIKE, false,
236                                    screenNames);
237                            sql = CustomSQLUtil.replaceKeywords(
238                                    sql, "lower(User_.emailAddress)", StringPool.LIKE, true,
239                                    emailAddresses);
240    
241                            if (status == WorkflowConstants.STATUS_ANY) {
242                                    sql = StringUtil.replace(sql, STATUS_SQL, StringPool.BLANK);
243                            }
244    
245                            sql = StringUtil.replace(sql, "[$JOIN$]", getJoin(params));
246                            sql = StringUtil.replace(sql, "[$WHERE$]", getWhere(params));
247                            sql = CustomSQLUtil.replaceAndOperator(sql, andOperator);
248    
249                            SQLQuery q = session.createSQLQuery(sql);
250    
251                            q.addScalar(COUNT_COLUMN_NAME, Type.LONG);
252    
253                            QueryPos qPos = QueryPos.getInstance(q);
254    
255                            setJoin(qPos, params);
256    
257                            qPos.add(companyId);
258                            qPos.add(false);
259                            qPos.add(firstNames, 2);
260                            qPos.add(middleNames, 2);
261                            qPos.add(lastNames, 2);
262                            qPos.add(screenNames, 2);
263                            qPos.add(emailAddresses, 2);
264    
265                            if (status != WorkflowConstants.STATUS_ANY) {
266                                    qPos.add(status);
267                            }
268    
269                            Iterator<Long> itr = q.iterate();
270    
271                            if (itr.hasNext()) {
272                                    Long count = itr.next();
273    
274                                    if (count != null) {
275                                            return count.intValue();
276                                    }
277                            }
278    
279                            return 0;
280                    }
281                    catch (Exception e) {
282                            throw new SystemException(e);
283                    }
284                    finally {
285                            closeSession(session);
286                    }
287            }
288    
289            public List<User> findByKeywords(
290                            long companyId, String keywords, int status,
291                            LinkedHashMap<String, Object> params, int start, int end,
292                            OrderByComparator obc)
293                    throws SystemException {
294    
295                    String[] firstNames = null;
296                    String[] middleNames = null;
297                    String[] lastNames = null;
298                    String[] screenNames = null;
299                    String[] emailAddresses = null;
300                    boolean andOperator = false;
301    
302                    if (Validator.isNotNull(keywords)) {
303                            firstNames = CustomSQLUtil.keywords(keywords);
304                            middleNames = CustomSQLUtil.keywords(keywords);
305                            lastNames = CustomSQLUtil.keywords(keywords);
306                            screenNames = CustomSQLUtil.keywords(keywords);
307                            emailAddresses = CustomSQLUtil.keywords(keywords);
308                    }
309                    else {
310                            andOperator = true;
311                    }
312    
313                    return findByC_FN_MN_LN_SN_EA_S(
314                            companyId, firstNames, middleNames, lastNames, screenNames,
315                            emailAddresses, status, params, andOperator, start, end, obc);
316            }
317    
318            public List<User> findByNoAnnouncementsDeliveries(String type)
319                    throws SystemException {
320    
321                    Session session = null;
322    
323                    try {
324                            session = openSession();
325    
326                            String sql = CustomSQLUtil.get(FIND_BY_NO_ANNOUNCEMENTS_DELIVERIES);
327    
328                            SQLQuery q = session.createSQLQuery(sql);
329    
330                            q.addEntity("User_", UserImpl.class);
331    
332                            QueryPos qPos = QueryPos.getInstance(q);
333    
334                            qPos.add(type);
335    
336                            return q.list(true);
337                    }
338                    catch (Exception e) {
339                            throw new SystemException(e);
340                    }
341                    finally {
342                            closeSession(session);
343                    }
344            }
345    
346            public List<User> findByNoContacts() throws SystemException {
347                    Session session = null;
348    
349                    try {
350                            session = openSession();
351    
352                            String sql = CustomSQLUtil.get(FIND_BY_NO_CONTACTS);
353    
354                            SQLQuery q = session.createSQLQuery(sql);
355    
356                            q.addEntity("User_", UserImpl.class);
357    
358                            return q.list(true);
359                    }
360                    catch (Exception e) {
361                            throw new SystemException(e);
362                    }
363                    finally {
364                            closeSession(session);
365                    }
366            }
367    
368            public List<User> findByNoGroups() throws SystemException {
369                    Session session = null;
370    
371                    try {
372                            session = openSession();
373    
374                            String sql = CustomSQLUtil.get(FIND_BY_NO_GROUPS);
375    
376                            SQLQuery q = session.createSQLQuery(sql);
377    
378                            q.addEntity("User_", UserImpl.class);
379    
380                            return q.list(true);
381                    }
382                    catch (Exception e) {
383                            throw new SystemException(e);
384                    }
385                    finally {
386                            closeSession(session);
387                    }
388            }
389    
390            public List<User> findByC_FN_MN_LN_SN_EA_S(
391                            long companyId, String firstName, String middleName,
392                            String lastName, String screenName, String emailAddress,
393                            int status, LinkedHashMap<String, Object> params,
394                            boolean andOperator, int start, int end, OrderByComparator obc)
395                    throws SystemException {
396    
397                    String[] firstNames = CustomSQLUtil.keywords(firstName);
398                    String[] middleNames = CustomSQLUtil.keywords(middleName);
399                    String[] lastNames = CustomSQLUtil.keywords(lastName);
400                    String[] screenNames = CustomSQLUtil.keywords(screenName);
401                    String[] emailAddresses = CustomSQLUtil.keywords(emailAddress);
402    
403                    return findByC_FN_MN_LN_SN_EA_S(
404                            companyId, firstNames, middleNames, lastNames, screenNames,
405                            emailAddresses, status, params, andOperator, start, end, obc);
406            }
407    
408            public List<User> findByC_FN_MN_LN_SN_EA_S(
409                            long companyId, String[] firstNames, String[] middleNames,
410                            String[] lastNames, String[] screenNames, String[] emailAddresses,
411                            int status, LinkedHashMap<String, Object> params,
412                            boolean andOperator, int start, int end, OrderByComparator obc)
413                    throws SystemException {
414    
415                    firstNames = CustomSQLUtil.keywords(firstNames);
416                    middleNames = CustomSQLUtil.keywords(middleNames);
417                    lastNames = CustomSQLUtil.keywords(lastNames);
418                    screenNames = CustomSQLUtil.keywords(screenNames);
419                    emailAddresses = CustomSQLUtil.keywords(emailAddresses);
420    
421                    Session session = null;
422    
423                    try {
424                            session = openSession();
425    
426                            String sql = CustomSQLUtil.get(FIND_BY_C_FN_MN_LN_SN_EA_S);
427    
428                            sql = CustomSQLUtil.replaceKeywords(
429                                    sql, "lower(User_.firstName)", StringPool.LIKE, false,
430                                    firstNames);
431                            sql = CustomSQLUtil.replaceKeywords(
432                                    sql, "lower(User_.middleName)", StringPool.LIKE, false,
433                                    middleNames);
434                            sql = CustomSQLUtil.replaceKeywords(
435                                    sql, "lower(User_.lastName)", StringPool.LIKE, false,
436                                    lastNames);
437                            sql = CustomSQLUtil.replaceKeywords(
438                                    sql, "lower(User_.screenName)", StringPool.LIKE, false,
439                                    screenNames);
440                            sql = CustomSQLUtil.replaceKeywords(
441                                    sql, "lower(User_.emailAddress)", StringPool.LIKE, true,
442                                    emailAddresses);
443    
444                            if (status == WorkflowConstants.STATUS_ANY) {
445                                    sql = StringUtil.replace(sql, STATUS_SQL, StringPool.BLANK);
446                            }
447    
448                            sql = StringUtil.replace(sql, "[$JOIN$]", getJoin(params));
449                            sql = StringUtil.replace(sql, "[$WHERE$]", getWhere(params));
450                            sql = CustomSQLUtil.replaceAndOperator(sql, andOperator);
451                            sql = CustomSQLUtil.replaceOrderBy(sql, obc);
452    
453                            SQLQuery q = session.createSQLQuery(sql);
454    
455                            q.addEntity("User_", UserImpl.class);
456    
457                            QueryPos qPos = QueryPos.getInstance(q);
458    
459                            setJoin(qPos, params);
460    
461                            qPos.add(companyId);
462                            qPos.add(false);
463                            qPos.add(firstNames, 2);
464                            qPos.add(middleNames, 2);
465                            qPos.add(lastNames, 2);
466                            qPos.add(screenNames, 2);
467                            qPos.add(emailAddresses, 2);
468    
469                            if (status != WorkflowConstants.STATUS_ANY) {
470                                    qPos.add(status);
471                            }
472    
473                            return (List<User>)QueryUtil.list(q, getDialect(), start, end);
474                    }
475                    catch (Exception e) {
476                            throw new SystemException(e);
477                    }
478                    finally {
479                            closeSession(session);
480                    }
481            }
482    
483            protected String getJoin(LinkedHashMap<String, Object> params) {
484                    if ((params == null) || params.isEmpty()) {
485                            return StringPool.BLANK;
486                    }
487    
488                    StringBundler sb = new StringBundler(params.size());
489    
490                    Iterator<Map.Entry<String, Object>> itr = params.entrySet().iterator();
491    
492                    while (itr.hasNext()) {
493                            Map.Entry<String, Object> entry = itr.next();
494    
495                            String key = entry.getKey();
496    
497                            if (key.equals("expandoAttributes")) {
498                                    continue;
499                            }
500    
501                            Object value = entry.getValue();
502    
503                            if (Validator.isNotNull(value)) {
504                                    sb.append(getJoin(key, value));
505                            }
506                    }
507    
508                    return sb.toString();
509            }
510    
511            protected String getJoin(String key, Object value) {
512                    String join = StringPool.BLANK;
513    
514                    if (key.equals("contactTwitterSn")) {
515                            join = CustomSQLUtil.get(JOIN_BY_CONTACT_TWITTER_SN);
516                    }
517                    else if (key.equals("noOrganizations")) {
518                            join = CustomSQLUtil.get(JOIN_BY_NO_ORGANIZATIONS);
519                    }
520                    else if (key.equals("permission")) {
521                            join = CustomSQLUtil.get(JOIN_BY_PERMISSION);
522                    }
523                    else if (key.equals("userGroupRole")) {
524                            join = CustomSQLUtil.get(JOIN_BY_USER_GROUP_ROLE);
525                    }
526                    else if (key.equals("usersGroups")) {
527                            join = CustomSQLUtil.get(JOIN_BY_USERS_GROUPS);
528                    }
529                    else if (key.equals("usersOrgs")) {
530                            join = CustomSQLUtil.get(JOIN_BY_USERS_ORGS);
531                    }
532                    else if (key.equals("usersOrgsTree")) {
533                            join = CustomSQLUtil.get(JOIN_BY_USERS_ORGS_TREE);
534                    }
535                    else if (key.equals("usersPasswordPolicies")) {
536                            join = CustomSQLUtil.get(JOIN_BY_USERS_PASSWORD_POLICIES);
537                    }
538                    else if (key.equals("usersRoles")) {
539                            join = CustomSQLUtil.get(JOIN_BY_USERS_ROLES);
540                    }
541                    else if (key.equals("usersTeams")) {
542                            join = CustomSQLUtil.get(JOIN_BY_USERS_TEAMS);
543                    }
544                    else if (key.equals("usersUserGroups")) {
545                            join = CustomSQLUtil.get(JOIN_BY_USERS_USER_GROUPS);
546                    }
547                    else if (key.equals("announcementsDeliveryEmailOrSms")) {
548                            join = CustomSQLUtil.get(
549                                    JOIN_BY_ANNOUNCEMENTS_DELIVERY_EMAIL_OR_SMS);
550                    }
551                    else if (key.equals("socialMutualRelation")) {
552                            join = CustomSQLUtil.get(JOIN_BY_SOCIAL_MUTUAL_RELATION);
553                    }
554                    else if (key.equals("socialMutualRelationType")) {
555                            join = CustomSQLUtil.get(JOIN_BY_SOCIAL_MUTUAL_RELATION_TYPE);
556                    }
557                    else if (key.equals("socialRelation")) {
558                            join = CustomSQLUtil.get(JOIN_BY_SOCIAL_RELATION);
559                    }
560                    else if (key.equals("socialRelationType")) {
561                            join = CustomSQLUtil.get(JOIN_BY_SOCIAL_RELATION_TYPE);
562                    }
563                    else if (value instanceof CustomSQLParam) {
564                            CustomSQLParam customSQLParam = (CustomSQLParam)value;
565    
566                            join = customSQLParam.getSQL();
567                    }
568    
569                    if (Validator.isNotNull(join)) {
570                            int pos = join.indexOf("WHERE");
571    
572                            if (pos != -1) {
573                                    join = join.substring(0, pos);
574                            }
575                    }
576    
577                    return join;
578            }
579    
580            protected String getWhere(LinkedHashMap<String, Object> params) {
581                    if ((params == null) || params.isEmpty()) {
582                            return StringPool.BLANK;
583                    }
584    
585                    StringBundler sb = new StringBundler(params.size());
586    
587                    Iterator<Map.Entry<String, Object>> itr = params.entrySet().iterator();
588    
589                    while (itr.hasNext()) {
590                            Map.Entry<String, Object> entry = itr.next();
591    
592                            String key = entry.getKey();
593    
594                            if (key.equals("expandoAttributes")) {
595                                    continue;
596                            }
597    
598                            Object value = entry.getValue();
599    
600                            if (Validator.isNotNull(value)) {
601                                    sb.append(getWhere(key, value));
602                            }
603                    }
604    
605                    return sb.toString();
606            }
607    
608            protected String getWhere(String key, Object value) {
609                    String join = StringPool.BLANK;
610    
611                    if (key.equals("contactTwitterSn")) {
612                            join = CustomSQLUtil.get(JOIN_BY_CONTACT_TWITTER_SN);
613                    }
614                    else if (key.equals("noOrganizations")) {
615                            join = CustomSQLUtil.get(JOIN_BY_NO_ORGANIZATIONS);
616                    }
617                    else if (key.equals("permission")) {
618                            join = CustomSQLUtil.get(JOIN_BY_PERMISSION);
619                    }
620                    else if (key.equals("userGroupRole")) {
621                            join = CustomSQLUtil.get(JOIN_BY_USER_GROUP_ROLE);
622                    }
623                    else if (key.equals("usersGroups")) {
624                            join = CustomSQLUtil.get(JOIN_BY_USERS_GROUPS);
625                    }
626                    else if (key.equals("usersOrgs")) {
627                            if (value instanceof Long) {
628                                    join = CustomSQLUtil.get(JOIN_BY_USERS_ORGS);
629                            }
630                            else if (value instanceof Long[]) {
631                                    Long[] organizationIds = (Long[])value;
632    
633                                    if (organizationIds.length == 0) {
634                                            join = "WHERE ((Users_Orgs.organizationId = -1) ))";
635                                    }
636                                    else {
637                                            StringBundler sb = new StringBundler(
638                                                    organizationIds.length * 2 + 1);
639    
640                                            sb.append("WHERE (");
641    
642                                            for (int i = 0; i < organizationIds.length; i++) {
643                                                    sb.append("(Users_Orgs.organizationId = ?) ");
644    
645                                                    if ((i + 1) < organizationIds.length) {
646                                                            sb.append("OR ");
647                                                    }
648                                            }
649    
650                                            sb.append(")");
651    
652                                            join = sb.toString();
653                                    }
654                            }
655                    }
656                    else if (key.equals("usersOrgsTree")) {
657                            List<Organization> organizationsTree = (List<Organization>)value;
658    
659                            int size = organizationsTree.size();
660    
661                            if (size > 0) {
662                                    StringBundler sb = new StringBundler(size * 2 + 1);
663    
664                                    sb.append("WHERE (");
665    
666                                    for (int i = 0; i < size; i++) {
667                                            sb.append("(Organization_.treePath LIKE ?) ");
668    
669                                            if ((i + 1) < size) {
670                                                    sb.append("OR ");
671                                            }
672                                    }
673    
674                                    sb.append(")");
675    
676                                    join = sb.toString();
677                            }
678                    }
679                    else if (key.equals("usersPasswordPolicies")) {
680                            join = CustomSQLUtil.get(JOIN_BY_USERS_PASSWORD_POLICIES);
681                    }
682                    else if (key.equals("usersRoles")) {
683                            join = CustomSQLUtil.get(JOIN_BY_USERS_ROLES);
684                    }
685                    else if (key.equals("usersTeams")) {
686                            join = CustomSQLUtil.get(JOIN_BY_USERS_TEAMS);
687                    }
688                    else if (key.equals("usersUserGroups")) {
689                            join = CustomSQLUtil.get(JOIN_BY_USERS_USER_GROUPS);
690                    }
691                    else if (key.equals("announcementsDeliveryEmailOrSms")) {
692                            join = CustomSQLUtil.get(
693                                    JOIN_BY_ANNOUNCEMENTS_DELIVERY_EMAIL_OR_SMS);
694                    }
695                    else if (key.equals("socialMutualRelation")) {
696                            join = CustomSQLUtil.get(JOIN_BY_SOCIAL_MUTUAL_RELATION);
697                    }
698                    else if (key.equals("socialMutualRelationType")) {
699                            join = CustomSQLUtil.get(JOIN_BY_SOCIAL_MUTUAL_RELATION_TYPE);
700                    }
701                    else if (key.equals("socialRelation")) {
702                            join = CustomSQLUtil.get(JOIN_BY_SOCIAL_RELATION);
703                    }
704                    else if (key.equals("socialRelationType")) {
705                            join = CustomSQLUtil.get(JOIN_BY_SOCIAL_RELATION_TYPE);
706                    }
707                    else if (value instanceof CustomSQLParam) {
708                            CustomSQLParam customSQLParam = (CustomSQLParam)value;
709    
710                            join = customSQLParam.getSQL();
711                    }
712    
713                    if (Validator.isNotNull(join)) {
714                            int pos = join.indexOf("WHERE");
715    
716                            if (pos != -1) {
717                                    join = join.substring(pos + 5, join.length()).concat(" AND ");
718                            }
719                            else {
720                                    join = StringPool.BLANK;
721                            }
722                    }
723    
724                    return join;
725            }
726    
727            protected void setJoin(
728                    QueryPos qPos, LinkedHashMap<String, Object> params) {
729    
730                    if (params == null) {
731                            return;
732                    }
733    
734                    for (Map.Entry<String, Object> entry : params.entrySet()) {
735                            String key = entry.getKey();
736    
737                            if (key.equals("expandoAttributes")) {
738                                    continue;
739                            }
740    
741                            Object value = entry.getValue();
742    
743                            if (key.equals("usersOrgsTree")) {
744                                    List<Organization> organizationsTree =
745                                            (List<Organization>)value;
746    
747                                    if (!organizationsTree.isEmpty()) {
748                                            for (Organization organization : organizationsTree) {
749                                                    StringBundler treePath = new StringBundler(5);
750    
751                                                    treePath.append(StringPool.PERCENT);
752                                                    treePath.append(StringPool.SLASH);
753                                                    treePath.append(organization.getOrganizationId());
754                                                    treePath.append(StringPool.SLASH);
755                                                    treePath.append(StringPool.PERCENT);
756    
757                                                    qPos.add(treePath.toString());
758                                            }
759                                    }
760                            }
761                            else if (value instanceof Long) {
762                                    Long valueLong = (Long)value;
763    
764                                    if (Validator.isNotNull(valueLong)) {
765                                            qPos.add(valueLong);
766                                    }
767                            }
768                            else if (value instanceof Long[]) {
769                                    Long[] valueArray = (Long[])value;
770    
771                                    for (Long element : valueArray) {
772                                            if (Validator.isNotNull(element)) {
773                                                    qPos.add(element);
774                                            }
775                                    }
776                            }
777                            else if (value instanceof Long[][]) {
778                                    Long[][] valueDoubleArray = (Long[][])value;
779    
780                                    for (Long[] valueArray : valueDoubleArray) {
781                                            for (Long valueLong : valueArray) {
782                                                    qPos.add(valueLong);
783                                            }
784                                    }
785                            }
786                            else if (value instanceof String) {
787                                    String valueString = (String)value;
788    
789                                    if (Validator.isNotNull(valueString)) {
790                                            qPos.add(valueString);
791                                    }
792                            }
793                            else if (value instanceof String[]) {
794                                    String[] valueArray = (String[])value;
795    
796                                    for (String element : valueArray) {
797                                            if (Validator.isNotNull(element)) {
798                                                    qPos.add(element);
799                                            }
800                                    }
801                            }
802                            else if (value instanceof CustomSQLParam) {
803                                    CustomSQLParam customSQLParam = (CustomSQLParam)value;
804    
805                                    customSQLParam.process(qPos);
806                            }
807                    }
808            }
809    
810            protected static String STATUS_SQL = "AND (User_.status = ?)";
811    
812    }