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