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