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