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