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