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_GROUPS_PERMISSIONS =
067                    OrganizationFinder.class.getName() + ".joinByGroupsPermissions";
068    
069            public static final String JOIN_BY_ORGANIZATIONS_GROUPS =
070                    OrganizationFinder.class.getName() + ".joinByOrganizationsGroups";
071    
072            public static final String JOIN_BY_ORGANIZATIONS_PASSWORD_POLICIES =
073                    OrganizationFinder.class.getName() +
074                            ".joinByOrganizationsPasswordPolicies";
075    
076            public static final String JOIN_BY_ORGANIZATIONS_ROLES =
077                    OrganizationFinder.class.getName() + ".joinByOrganizationsRoles";
078    
079            public static final String JOIN_BY_ORGANIZATIONS_USERS =
080                    OrganizationFinder.class.getName() + ".joinByOrganizationsUsers";
081    
082            public static final String JOIN_BY_ORG_GROUP_PERMISSION =
083                    OrganizationFinder.class.getName() + ".joinByOrgGroupPermission";
084    
085            public static final String JOIN_BY_USERS_ORGS =
086                    OrganizationFinder.class.getName() + ".joinByUsersOrgs";
087    
088            public int countByKeywords(
089                            long companyId, long parentOrganizationId,
090                            String parentOrganizationIdComparator, String keywords, String type,
091                            Long regionId, Long countryId,
092                            LinkedHashMap<String, Object> params)
093                    throws SystemException {
094    
095                    String[] names = null;
096                    String[] streets = null;
097                    String[] cities = null;
098                    String[] zips = null;
099                    boolean andOperator = false;
100    
101                    if (Validator.isNotNull(keywords)) {
102                            names = CustomSQLUtil.keywords(keywords);
103                            streets = CustomSQLUtil.keywords(keywords);
104                            cities = CustomSQLUtil.keywords(keywords);
105                            zips = CustomSQLUtil.keywords(keywords);
106                    }
107                    else {
108                            andOperator = true;
109                    }
110    
111                    return countByC_PO_N_T_S_C_Z_R_C(
112                            companyId, parentOrganizationId, parentOrganizationIdComparator,
113                            names, type, streets, cities, zips, regionId, countryId, params,
114                            andOperator);
115            }
116    
117            public int countByO_U(long organizationId, long userId)
118                    throws SystemException {
119    
120                    LinkedHashMap<String, Object> params1 =
121                            new LinkedHashMap<String, Object>();
122    
123                    params1.put("usersOrgs", userId);
124    
125                    Session session = null;
126    
127                    try {
128                            session = openSession();
129    
130                            int count = countByOrganizationId(session, organizationId, params1);
131    
132                            return count;
133                    }
134                    catch (Exception e) {
135                            throw new SystemException(e);
136                    }
137                    finally {
138                            closeSession(session);
139                    }
140            }
141    
142            public int countByC_PO_N_T_S_C_Z_R_C(
143                            long companyId, long parentOrganizationId,
144                            String parentOrganizationIdComparator, String name, String type,
145                            String street, String city, String zip, Long regionId,
146                            Long countryId, LinkedHashMap<String, Object> params,
147                            boolean andOperator)
148                    throws SystemException {
149    
150                    String[] names = CustomSQLUtil.keywords(name);
151                    String[] streets = CustomSQLUtil.keywords(street);
152                    String[] cities = CustomSQLUtil.keywords(city);
153                    String[] zips = CustomSQLUtil.keywords(zip);
154    
155                    return countByC_PO_N_T_S_C_Z_R_C(
156                            companyId, parentOrganizationId, parentOrganizationIdComparator,
157                            names, type, streets, cities, zips, regionId, countryId, params,
158                            andOperator);
159            }
160    
161            public int countByC_PO_N_T_S_C_Z_R_C(
162                            long companyId, long parentOrganizationId,
163                            String parentOrganizationIdComparator, String[] names, String type,
164                            String[] streets, String[] cities, String[] zips, Long regionId,
165                            Long countryId, LinkedHashMap<String, Object> params,
166                            boolean andOperator)
167                    throws SystemException {
168    
169                    names = CustomSQLUtil.keywords(names);
170                    streets = CustomSQLUtil.keywords(streets);
171                    cities = CustomSQLUtil.keywords(cities);
172                    zips = CustomSQLUtil.keywords(zips);
173    
174                    if (params != null) {
175                            Long resourceId = (Long)params.get("permissionsResourceId");
176                            Long groupId = (Long)params.get("permissionsGroupId");
177    
178                            if (Validator.isNotNull(groupId) &&
179                                    Validator.isNotNull(resourceId)) {
180    
181                                    return countByPermissions(
182                                            companyId, parentOrganizationId,
183                                            parentOrganizationIdComparator, names, type, streets,
184                                            cities, zips, regionId, countryId, resourceId.longValue(),
185                                            groupId.longValue(), andOperator);
186                            }
187                    }
188    
189                    Session session = null;
190    
191                    try {
192                            session = openSession();
193    
194                            String sql = null;
195    
196                            if (Validator.isNotNull(type)) {
197                                    sql = CustomSQLUtil.get(COUNT_BY_C_PO_N_L_S_C_Z_R_C);
198                            }
199                            else {
200                                    sql = CustomSQLUtil.get(COUNT_BY_C_PO_N_S_C_Z_R_C);
201                            }
202    
203                            sql = CustomSQLUtil.replaceKeywords(
204                                    sql, "lower(Organization_.name)", StringPool.LIKE, false,
205                                    names);
206                            sql = CustomSQLUtil.replaceKeywords(
207                                    sql, "lower(Address.street1)", StringPool.LIKE, true, streets);
208                            sql = CustomSQLUtil.replaceKeywords(
209                                    sql, "lower(Address.street2)", StringPool.LIKE, true, streets);
210                            sql = CustomSQLUtil.replaceKeywords(
211                                    sql, "lower(Address.street3)", StringPool.LIKE, true, streets);
212                            sql = CustomSQLUtil.replaceKeywords(
213                                    sql, "lower(Address.city)", StringPool.LIKE, false, cities);
214                            sql = CustomSQLUtil.replaceKeywords(
215                                    sql, "lower(Address.zip)", StringPool.LIKE, true, zips);
216    
217                            if (regionId == null) {
218                                    sql = StringUtil.replace(sql, REGION_ID_SQL, StringPool.BLANK);
219                            }
220    
221                            if (countryId == null) {
222                                    sql = StringUtil.replace(sql, COUNTRY_ID_SQL, StringPool.BLANK);
223                            }
224    
225                            sql = StringUtil.replace(sql, "[$JOIN$]", getJoin(params));
226                            sql = StringUtil.replace(sql, "[$WHERE$]", getWhere(params));
227                            sql = StringUtil.replace(
228                                    sql, "[$PARENT_ORGANIZATION_ID_COMPARATOR$]",
229                                    parentOrganizationIdComparator);
230                            sql = CustomSQLUtil.replaceAndOperator(sql, andOperator);
231    
232                            SQLQuery q = session.createSQLQuery(sql);
233    
234                            q.addScalar(COUNT_COLUMN_NAME, Type.LONG);
235    
236                            QueryPos qPos = QueryPos.getInstance(q);
237    
238                            setJoin(qPos, params);
239    
240                            qPos.add(companyId);
241                            qPos.add(parentOrganizationId);
242    
243                            if (Validator.isNotNull(type)) {
244                                    qPos.add(type);
245                            }
246    
247                            qPos.add(names, 2);
248                            qPos.add(streets, 6);
249    
250                            if (regionId != null) {
251                                    qPos.add(regionId);
252                                    qPos.add(regionId);
253                            }
254    
255                            if (countryId != null) {
256                                    qPos.add(countryId);
257                                    qPos.add(countryId);
258                            }
259    
260                            qPos.add(cities, 2);
261                            qPos.add(zips, 2);
262    
263                            Iterator<Long> itr = q.iterate();
264    
265                            if (itr.hasNext()) {
266                                    Long count = itr.next();
267    
268                                    if (count != null) {
269                                            return count.intValue();
270                                    }
271                            }
272    
273                            return 0;
274                    }
275                    catch (Exception e) {
276                            throw new SystemException(e);
277                    }
278                    finally {
279                            closeSession(session);
280                    }
281            }
282    
283            public List<Organization> findByCompanyId(
284                            long companyId, LinkedHashMap<String, Object> params, int start,
285                            int end, OrderByComparator obc)
286                    throws SystemException {
287    
288                    if (params == null) {
289                            params = new LinkedHashMap<String, Object>();
290                    }
291    
292                    StringBundler sb = new StringBundler();
293    
294                    sb.append("(");
295    
296                    String sql = CustomSQLUtil.get(FIND_BY_COMPANY_ID);
297    
298                    sql = StringUtil.replace(sql, "[$JOIN$]", getJoin(params));
299                    sql = StringUtil.replace(sql, "[$WHERE$]", getWhere(params));
300    
301                    sb.append(sql);
302                    sb.append(")");
303    
304                    sql = sb.toString();
305    
306                    sql = CustomSQLUtil.replaceAndOperator(sql, true);
307                    sql = CustomSQLUtil.replaceOrderBy(sql, obc);
308    
309                    Session session = null;
310    
311                    try {
312                            session = openSession();
313    
314                            SQLQuery q = session.createSQLQuery(sql);
315    
316                            q.addScalar("orgId", Type.LONG);
317    
318                            QueryPos qPos = QueryPos.getInstance(q);
319    
320                            setJoin(qPos, params);
321    
322                            qPos.add(companyId);
323    
324                            List<Organization> organizations = new ArrayList<Organization>();
325    
326                            Iterator<Long> itr = (Iterator<Long>)QueryUtil.iterate(
327                                    q, getDialect(), start, end);
328    
329                            while (itr.hasNext()) {
330                                    Long organizationId = itr.next();
331    
332                                    Organization organization = OrganizationUtil.findByPrimaryKey(
333                                            organizationId.longValue());
334    
335                                    organizations.add(organization);
336                            }
337    
338                            return organizations;
339                    }
340                    catch (Exception e) {
341                            throw new SystemException(e);
342                    }
343                    finally {
344                            closeSession(session);
345                    }
346            }
347    
348            public List<Organization> findByKeywords(
349                            long companyId, long parentOrganizationId,
350                            String parentOrganizationIdComparator, String keywords, String type,
351                            Long regionId, Long countryId,
352                            LinkedHashMap<String, Object> params, int start, int end,
353                            OrderByComparator obc)
354                    throws SystemException {
355    
356                    String[] names = null;
357                    String[] streets = null;
358                    String[] cities = null;
359                    String[] zips = null;
360                    boolean andOperator = false;
361    
362                    if (Validator.isNotNull(keywords)) {
363                            names = CustomSQLUtil.keywords(keywords);
364                            streets = CustomSQLUtil.keywords(keywords);
365                            cities = CustomSQLUtil.keywords(keywords);
366                            zips = CustomSQLUtil.keywords(keywords);
367                    }
368                    else {
369                            andOperator = true;
370                    }
371    
372                    return findByC_PO_N_T_S_C_Z_R_C(
373                            companyId, parentOrganizationId, parentOrganizationIdComparator,
374                            names, type, streets, cities, zips, regionId, countryId, params,
375                            andOperator, start, end, obc);
376            }
377    
378            public List<Organization> findByC_PO_N_T_S_C_Z_R_C(
379                            long companyId, long parentOrganizationId,
380                            String parentOrganizationIdComparator, String name, String type,
381                            String street, String city, String zip, Long regionId,
382                            Long countryId, LinkedHashMap<String, Object> params,
383                            boolean andOperator, int start, int end, OrderByComparator obc)
384                    throws SystemException {
385    
386                    String[] names = CustomSQLUtil.keywords(name);
387                    String[] streets = CustomSQLUtil.keywords(street);
388                    String[] cities = CustomSQLUtil.keywords(city);
389                    String[] zips = CustomSQLUtil.keywords(zip);
390    
391                    return findByC_PO_N_T_S_C_Z_R_C(
392                            companyId, parentOrganizationId, parentOrganizationIdComparator,
393                            names, type, streets, cities, zips, regionId, countryId, params,
394                            andOperator, start, end, obc);
395            }
396    
397            public List<Organization> findByC_PO_N_T_S_C_Z_R_C(
398                            long companyId, long parentOrganizationId,
399                            String parentOrganizationIdComparator, String[] names, String type,
400                            String[] streets, String[] cities, String[] zips, Long regionId,
401                            Long countryId, LinkedHashMap<String, Object> params,
402                            boolean andOperator, int start, int end, OrderByComparator obc)
403                    throws SystemException {
404    
405                    names = CustomSQLUtil.keywords(names);
406                    streets = CustomSQLUtil.keywords(streets);
407                    cities = CustomSQLUtil.keywords(cities);
408                    zips = CustomSQLUtil.keywords(zips);
409    
410                    if (params != null) {
411                            Long resourceId = (Long)params.get("permissionsResourceId");
412                            Long groupId = (Long)params.get("permissionsGroupId");
413    
414                            if (Validator.isNotNull(groupId) &&
415                                    Validator.isNotNull(resourceId)) {
416    
417                                    return findByPermissions(
418                                            companyId, parentOrganizationId,
419                                            parentOrganizationIdComparator, names, type, streets,
420                                            cities, zips, regionId, countryId, resourceId.longValue(),
421                                            groupId.longValue(), andOperator, start, end, obc);
422                            }
423                    }
424                    else {
425                            params = new LinkedHashMap<String, Object>();
426                    }
427    
428                    StringBundler sb = new StringBundler();
429    
430                    sb.append("(");
431    
432                    if (Validator.isNotNull(type)) {
433                            sb.append(CustomSQLUtil.get(FIND_BY_C_PO_N_L_S_C_Z_R_C));
434                    }
435                    else {
436                            sb.append(CustomSQLUtil.get(FIND_BY_C_PO_N_S_C_Z_R_C));
437                    }
438    
439                    String sql = sb.toString();
440    
441                    sql = StringUtil.replace(sql, "[$JOIN$]", getJoin(params));
442                    sql = StringUtil.replace(sql, "[$WHERE$]", getWhere(params));
443                    sql = sql.concat(StringPool.CLOSE_PARENTHESIS);
444                    sql = CustomSQLUtil.replaceKeywords(
445                            sql, "lower(Organization_.name)", StringPool.LIKE, false, names);
446                    sql = CustomSQLUtil.replaceKeywords(
447                            sql, "lower(Address.street1)", StringPool.LIKE, true, streets);
448                    sql = CustomSQLUtil.replaceKeywords(
449                            sql, "lower(Address.street2)", StringPool.LIKE, true, streets);
450                    sql = CustomSQLUtil.replaceKeywords(
451                            sql, "lower(Address.street3)", StringPool.LIKE, true, streets);
452                    sql = CustomSQLUtil.replaceKeywords(
453                            sql, "lower(Address.city)", StringPool.LIKE, false, cities);
454                    sql = CustomSQLUtil.replaceKeywords(
455                            sql, "lower(Address.zip)", StringPool.LIKE, true, zips);
456                    sql = StringUtil.replace(
457                            sql, "[$PARENT_ORGANIZATION_ID_COMPARATOR$]",
458                            parentOrganizationIdComparator);
459    
460                    if (regionId == null) {
461                            sql = StringUtil.replace(sql, REGION_ID_SQL, StringPool.BLANK);
462                    }
463    
464                    if (countryId == null) {
465                            sql = StringUtil.replace(sql, COUNTRY_ID_SQL, StringPool.BLANK);
466                    }
467    
468                    sql = CustomSQLUtil.replaceAndOperator(sql, andOperator);
469                    sql = CustomSQLUtil.replaceOrderBy(sql, obc);
470    
471                    Session session = null;
472    
473                    try {
474                            session = openSession();
475    
476                            SQLQuery q = session.createSQLQuery(sql);
477    
478                            q.addScalar("orgId", Type.LONG);
479    
480                            QueryPos qPos = QueryPos.getInstance(q);
481    
482                            setJoin(qPos, params);
483    
484                            qPos.add(companyId);
485                            qPos.add(parentOrganizationId);
486    
487                            if (Validator.isNotNull(type)) {
488                                    qPos.add(type);
489                            }
490    
491                            qPos.add(names, 2);
492                            qPos.add(streets, 6);
493    
494                            if (regionId != null) {
495                                    qPos.add(regionId);
496                                    qPos.add(regionId);
497                            }
498    
499                            if (countryId != null) {
500                                    qPos.add(countryId);
501                                    qPos.add(countryId);
502                            }
503    
504                            qPos.add(cities, 2);
505                            qPos.add(zips, 2);
506    
507                            List<Organization> organizations = new ArrayList<Organization>();
508    
509                            Iterator<Long> itr = (Iterator<Long>)QueryUtil.iterate(
510                                    q, getDialect(), start, end);
511    
512                            while (itr.hasNext()) {
513                                    Long organizationId = itr.next();
514    
515                                    Organization organization = OrganizationUtil.findByPrimaryKey(
516                                            organizationId.longValue());
517    
518                                    organizations.add(organization);
519                            }
520    
521                            return organizations;
522                    }
523                    catch (Exception e) {
524                            throw new SystemException(e);
525                    }
526                    finally {
527                            closeSession(session);
528                    }
529            }
530    
531            protected int countByOrganizationId(
532                    Session session, long organizationId,
533                    LinkedHashMap<String, Object> params) {
534    
535                    String sql = CustomSQLUtil.get(COUNT_BY_ORGANIZATION_ID);
536    
537                    sql = StringUtil.replace(sql, "[$JOIN$]", getJoin(params));
538                    sql = StringUtil.replace(sql, "[$WHERE$]", getWhere(params));
539    
540                    SQLQuery q = session.createSQLQuery(sql);
541    
542                    q.addScalar(COUNT_COLUMN_NAME, Type.LONG);
543    
544                    QueryPos qPos = QueryPos.getInstance(q);
545    
546                    setJoin(qPos, params);
547    
548                    qPos.add(organizationId);
549    
550                    Iterator<Long> itr = q.iterate();
551    
552                    if (itr.hasNext()) {
553                            Long count = itr.next();
554    
555                            if (count != null) {
556                                    return count.intValue();
557                            }
558                    }
559    
560                    return 0;
561            }
562    
563            protected int countByPermissions(
564                            long companyId, long parentOrganizationId,
565                            String parentOrganizationIdComparator, String[] names, String type,
566                            String[] streets, String[] cities, String[] zips, Long regionId,
567                            Long countryId, long resourceId, long groupId, boolean andOperator)
568                    throws SystemException {
569    
570                    Session session = null;
571    
572                    try {
573                            session = openSession();
574    
575                            StringBundler sb = new StringBundler();
576    
577                            sb.append("(");
578    
579                            if (Validator.isNotNull(type)) {
580                                    sb.append(CustomSQLUtil.get(COUNT_BY_C_PO_N_L_S_C_Z_R_C));
581                            }
582                            else {
583                                    sb.append(CustomSQLUtil.get(COUNT_BY_C_PO_N_S_C_Z_R_C));
584                            }
585    
586                            String sql = sb.toString();
587    
588                            if (regionId == null) {
589                                    sql = StringUtil.replace(sql, REGION_ID_SQL, StringPool.BLANK);
590                            }
591    
592                            if (countryId == null) {
593                                    sql = StringUtil.replace(sql, COUNTRY_ID_SQL, StringPool.BLANK);
594                            }
595    
596                            sql = StringUtil.replace(
597                                    sql, "[$JOIN$]", getJoin("groupsPermissions"));
598                            sql = StringUtil.replace(
599                                    sql, "[$WHERE$]", getWhere("groupsPermissions"));
600    
601                            sb.setIndex(0);
602    
603                            sb.append(sql);
604                            sb.append(") UNION (");
605    
606                            if (Validator.isNotNull(type)) {
607                                    sb.append(CustomSQLUtil.get(COUNT_BY_C_PO_N_L_S_C_Z_R_C));
608                            }
609                            else {
610                                    sb.append(CustomSQLUtil.get(COUNT_BY_C_PO_N_S_C_Z_R_C));
611                            }
612    
613                            sql = sb.toString();
614    
615                            if (regionId == null) {
616                                    sql = StringUtil.replace(sql, REGION_ID_SQL, StringPool.BLANK);
617                            }
618    
619                            if (countryId == null) {
620                                    sql = StringUtil.replace(sql, COUNTRY_ID_SQL, StringPool.BLANK);
621                            }
622    
623                            sql = StringUtil.replace(
624                                    sql, "[$JOIN$]", getJoin("orgGroupPermission"));
625                            sql = StringUtil.replace(
626                                    sql, "[$WHERE$]", getWhere("orgGroupPermission"));
627                            sql = StringUtil.replace(
628                                    sql, "[$PARENT_ORGANIZATION_ID_COMPARATOR$]",
629                                    parentOrganizationIdComparator);
630                            sql = CustomSQLUtil.replaceAndOperator(sql, andOperator);
631    
632                            sb.setIndex(0);
633    
634                            sb.append(sql);
635                            sb.append(")");
636    
637                            sql = sb.toString();
638    
639                            sql = CustomSQLUtil.replaceKeywords(
640                                    sql, "lower(Organization_.name)", StringPool.LIKE, false,
641                                    names);
642                            sql = CustomSQLUtil.replaceKeywords(
643                                    sql, "lower(Address.street1)", StringPool.LIKE, true, streets);
644                            sql = CustomSQLUtil.replaceKeywords(
645                                    sql, "lower(Address.street2)", StringPool.LIKE, true, streets);
646                            sql = CustomSQLUtil.replaceKeywords(
647                                    sql, "lower(Address.street3)", StringPool.LIKE, true, streets);
648                            sql = CustomSQLUtil.replaceKeywords(
649                                    sql, "lower(Address.city)", StringPool.LIKE, false, cities);
650                            sql = CustomSQLUtil.replaceKeywords(
651                                    sql, "lower(Address.zip)", StringPool.LIKE, true, zips);
652    
653                            if (regionId == null) {
654                                    sql = StringUtil.replace(sql, REGION_ID_SQL, StringPool.BLANK);
655                            }
656    
657                            if (countryId == null) {
658                                    sql = StringUtil.replace(sql, COUNTRY_ID_SQL, StringPool.BLANK);
659                            }
660    
661                            SQLQuery q = session.createSQLQuery(sql);
662    
663                            q.addScalar(COUNT_COLUMN_NAME, Type.LONG);
664    
665                            QueryPos qPos = QueryPos.getInstance(q);
666    
667                            for (int i = 0; i < 2; i++) {
668                                    qPos.add(resourceId);
669    
670                                    if (i == 1) {
671                                            qPos.add(groupId);
672                                    }
673    
674                                    qPos.add(companyId);
675                                    qPos.add(parentOrganizationId);
676    
677                                    if (Validator.isNotNull(type)) {
678                                            qPos.add(type);
679                                    }
680    
681                                    qPos.add(names, 2);
682                                    qPos.add(streets, 6);
683    
684                                    if (regionId != null) {
685                                            qPos.add(regionId);
686                                            qPos.add(regionId);
687                                    }
688    
689                                    if (countryId != null) {
690                                            qPos.add(countryId);
691                                            qPos.add(countryId);
692                                    }
693    
694                                    qPos.add(cities, 2);
695                                    qPos.add(zips, 2);
696                            }
697    
698                            int count = 0;
699    
700                            Iterator<Long> itr = q.iterate();
701    
702                            while (itr.hasNext()) {
703                                    Long l = itr.next();
704    
705                                    if (l != null) {
706                                            count += l.intValue();
707                                    }
708                            }
709    
710                            return count;
711                    }
712                    catch (Exception e) {
713                            throw new SystemException(e);
714                    }
715                    finally {
716                            closeSession(session);
717                    }
718            }
719    
720            protected List<Organization> findByPermissions(
721                            long companyId, long parentOrganizationId,
722                            String parentOrganizationIdComparator, String[] names, String type,
723                            String[] streets, String[] cities, String[] zips, Long regionId,
724                            Long countryId, long resourceId, long groupId, boolean andOperator,
725                            int start, int end, OrderByComparator obc)
726                    throws SystemException {
727    
728                    Session session = null;
729    
730                    try {
731                            session = openSession();
732    
733                            StringBundler sb = new StringBundler();
734    
735                            sb.append("(");
736    
737                            if (Validator.isNotNull(type)) {
738                                    sb.append(CustomSQLUtil.get(FIND_BY_C_PO_N_L_S_C_Z_R_C));
739                            }
740                            else {
741                                    sb.append(CustomSQLUtil.get(FIND_BY_C_PO_N_S_C_Z_R_C));
742                            }
743    
744                            String sql = sb.toString();
745    
746                            if (regionId == null) {
747                                    sql = StringUtil.replace(sql, REGION_ID_SQL, StringPool.BLANK);
748                            }
749    
750                            if (countryId == null) {
751                                    sql = StringUtil.replace(sql, COUNTRY_ID_SQL, StringPool.BLANK);
752                            }
753    
754                            sql = StringUtil.replace(
755                                    sql, "[$JOIN$]", getJoin("groupsPermissions"));
756                            sql = StringUtil.replace(
757                                    sql, "[$WHERE$]", getWhere("groupsPermissions"));
758    
759                            sb.setIndex(0);
760    
761                            sb.append(sql);
762                            sb.append(") UNION (");
763    
764                            if (Validator.isNotNull(type)) {
765                                    sb.append(CustomSQLUtil.get(FIND_BY_C_PO_N_L_S_C_Z_R_C));
766                            }
767                            else {
768                                    sb.append(CustomSQLUtil.get(FIND_BY_C_PO_N_S_C_Z_R_C));
769                            }
770    
771                            sql = sb.toString();
772    
773                            if (regionId == null) {
774                                    sql = StringUtil.replace(sql, REGION_ID_SQL, StringPool.BLANK);
775                            }
776    
777                            if (countryId == null) {
778                                    sql = StringUtil.replace(sql, COUNTRY_ID_SQL, StringPool.BLANK);
779                            }
780    
781                            sql = StringUtil.replace(
782                                    sql, "[$JOIN$]", getJoin("orgGroupPermission"));
783                            sql = StringUtil.replace(
784                                    sql, "[$WHERE$]", getWhere("orgGroupPermission"));
785                            sql = StringUtil.replace(
786                                    sql, "[$PARENT_ORGANIZATION_ID_COMPARATOR$]",
787                                    parentOrganizationIdComparator);
788                            sql = CustomSQLUtil.replaceAndOperator(sql, andOperator);
789    
790                            sb.setIndex(0);
791    
792                            sb.append(sql);
793    
794                            sb.append(") ");
795    
796                            sql = sb.toString();
797    
798                            sql = CustomSQLUtil.replaceKeywords(
799                                    sql, "lower(Organization_.name)", StringPool.LIKE, false,
800                                    names);
801                            sql = CustomSQLUtil.replaceKeywords(
802                                    sql, "lower(Address.street1)", StringPool.LIKE, true, streets);
803                            sql = CustomSQLUtil.replaceKeywords(
804                                    sql, "lower(Address.street2)", StringPool.LIKE, true, streets);
805                            sql = CustomSQLUtil.replaceKeywords(
806                                    sql, "lower(Address.street3)", StringPool.LIKE, true, streets);
807                            sql = CustomSQLUtil.replaceKeywords(
808                                    sql, "lower(Address.city)", StringPool.LIKE, false, cities);
809                            sql = CustomSQLUtil.replaceKeywords(
810                                    sql, "lower(Address.zip)", StringPool.LIKE, true, zips);
811    
812                            if (regionId == null) {
813                                    sql = StringUtil.replace(sql, REGION_ID_SQL, StringPool.BLANK);
814                            }
815    
816                            if (countryId == null) {
817                                    sql = StringUtil.replace(sql, COUNTRY_ID_SQL, StringPool.BLANK);
818                            }
819    
820                            sql = CustomSQLUtil.replaceOrderBy(sql, obc);
821    
822                            SQLQuery q = session.createSQLQuery(sql);
823    
824                            q.addScalar("orgId", Type.LONG);
825    
826                            QueryPos qPos = QueryPos.getInstance(q);
827    
828                            for (int i = 0; i < 2; i++) {
829                                    qPos.add(resourceId);
830    
831                                    if (i == 1) {
832                                            qPos.add(groupId);
833                                    }
834    
835                                    qPos.add(companyId);
836                                    qPos.add(parentOrganizationId);
837    
838                                    if (Validator.isNotNull(type)) {
839                                            qPos.add(type);
840                                    }
841    
842                                    qPos.add(names, 2);
843                                    qPos.add(streets, 6);
844    
845                                    if (regionId != null) {
846                                            qPos.add(regionId);
847                                            qPos.add(regionId);
848                                    }
849    
850                                    if (countryId != null) {
851                                            qPos.add(countryId);
852                                            qPos.add(countryId);
853                                    }
854    
855                                    qPos.add(cities, 2);
856                                    qPos.add(zips, 2);
857                            }
858    
859                            List<Organization> organizations = new ArrayList<Organization>();
860    
861                            Iterator<Long> itr = (Iterator<Long>)QueryUtil.iterate(
862                                    q, getDialect(), start, end);
863    
864                            while (itr.hasNext()) {
865                                    Long organizationId = itr.next();
866    
867                                    Organization organization = OrganizationUtil.findByPrimaryKey(
868                                            organizationId.longValue());
869    
870                                    organizations.add(organization);
871                            }
872    
873                            return organizations;
874                    }
875                    catch (Exception e) {
876                            throw new SystemException(e);
877                    }
878                    finally {
879                            closeSession(session);
880                    }
881            }
882    
883            protected String getJoin(LinkedHashMap<String, Object> params) {
884                    if ((params == null) || params.isEmpty()) {
885                            return StringPool.BLANK;
886                    }
887    
888                    StringBundler sb = new StringBundler(params.size());
889    
890                    Iterator<Map.Entry<String, Object>> itr = params.entrySet().iterator();
891    
892                    while (itr.hasNext()) {
893                            Map.Entry<String, Object> entry = itr.next();
894    
895                            String key = entry.getKey();
896    
897                            if (key.equals("expandoAttributes")) {
898                                    continue;
899                            }
900    
901                            Object value = entry.getValue();
902    
903                            if (Validator.isNotNull(value)) {
904                                    sb.append(getJoin(key));
905                            }
906                    }
907    
908                    return sb.toString();
909            }
910    
911            protected String getJoin(String key) {
912                    String join = StringPool.BLANK;
913    
914                    if (key.equals("groupsPermissions")) {
915                            join = CustomSQLUtil.get(JOIN_BY_GROUPS_PERMISSIONS);
916                    }
917                    else if (key.equals("organizationsGroups")) {
918                            join = CustomSQLUtil.get(JOIN_BY_ORGANIZATIONS_GROUPS);
919                    }
920                    else if (key.equals("organizationsPasswordPolicies")) {
921                            join = CustomSQLUtil.get(JOIN_BY_ORGANIZATIONS_PASSWORD_POLICIES);
922                    }
923                    else if (key.equals("organizationsRoles")) {
924                            join = CustomSQLUtil.get(JOIN_BY_ORGANIZATIONS_ROLES);
925                    }
926                    else if (key.equals("organizationsUsers")) {
927                            join = CustomSQLUtil.get(JOIN_BY_ORGANIZATIONS_USERS);
928                    }
929                    else if (key.equals("orgGroupPermission")) {
930                            join = CustomSQLUtil.get(JOIN_BY_ORG_GROUP_PERMISSION);
931                    }
932                    else if (key.equals("usersOrgs")) {
933                            join = CustomSQLUtil.get(JOIN_BY_USERS_ORGS);
934                    }
935    
936                    if (Validator.isNotNull(join)) {
937                            int pos = join.indexOf("WHERE");
938    
939                            if (pos != -1) {
940                                    join = join.substring(0, pos);
941                            }
942                    }
943    
944                    return join;
945            }
946    
947            protected String getWhere(LinkedHashMap<String, Object> params) {
948                    if ((params == null) || params.isEmpty()) {
949                            return StringPool.BLANK;
950                    }
951    
952                    StringBundler sb = new StringBundler(params.size());
953    
954                    Iterator<Map.Entry<String, Object>> itr = params.entrySet().iterator();
955    
956                    while (itr.hasNext()) {
957                            Map.Entry<String, Object> entry = itr.next();
958    
959                            String key = entry.getKey();
960    
961                            if (key.equals("expandoAttributes")) {
962                                    continue;
963                            }
964    
965                            Object value = entry.getValue();
966    
967                            if (Validator.isNotNull(value)) {
968                                    sb.append(getWhere(key, value));
969                            }
970                    }
971    
972                    return sb.toString();
973            }
974    
975            protected String getWhere(String key) {
976                    return getWhere(key, null);
977            }
978    
979            protected String getWhere(String key, Object value) {
980                    String join = StringPool.BLANK;
981    
982                    if (key.equals("groupsPermissions")) {
983                            join = CustomSQLUtil.get(JOIN_BY_GROUPS_PERMISSIONS);
984                    }
985                    else if (key.equals("organizations")) {
986                            Long[] organizationIds = (Long[])value;
987    
988                            if (organizationIds.length == 0) {
989                                    join = "WHERE ((Organization_.organizationId = -1) )";
990                            }
991                            else {
992                                    StringBundler sb = new StringBundler(
993                                            organizationIds.length * 2 + 1);
994    
995                                    sb.append("WHERE (");
996    
997                                    for (int i = 0; i < organizationIds.length; i++) {
998                                            sb.append("(Organization_.organizationId = ?) ");
999    
1000                                            if ((i + 1) < organizationIds.length) {
1001                                                    sb.append("OR ");
1002                                            }
1003                                    }
1004    
1005                                    sb.append(")");
1006    
1007                                    join = sb.toString();
1008                            }
1009                    }
1010                    else if (key.equals("organizationsGroups")) {
1011                            join = CustomSQLUtil.get(JOIN_BY_ORGANIZATIONS_GROUPS);
1012                    }
1013                    else if (key.equals("organizationsPasswordPolicies")) {
1014                            join = CustomSQLUtil.get(JOIN_BY_ORGANIZATIONS_PASSWORD_POLICIES);
1015                    }
1016                    else if (key.equals("organizationsRoles")) {
1017                            join = CustomSQLUtil.get(JOIN_BY_ORGANIZATIONS_ROLES);
1018                    }
1019                    else if (key.equals("organizationsTree")) {
1020                            List<Organization> organizationsTree = (List<Organization>)value;
1021    
1022                            int size = organizationsTree.size();
1023    
1024                            if (!organizationsTree.isEmpty()) {
1025                                    StringBundler sb = new StringBundler(size * 2 + 1);
1026    
1027                                    sb.append("WHERE (");
1028    
1029                                    for (int i = 0; i < size; i++) {
1030                                            sb.append("(Organization_.treePath LIKE ?) ");
1031    
1032                                            if ((i + 1) < size) {
1033                                                    sb.append("OR ");
1034                                            }
1035                                    }
1036    
1037                                    sb.append(")");
1038    
1039                                    join = sb.toString();
1040                            }
1041                    }
1042                    else if (key.equals("organizationsUsers")) {
1043                            join = CustomSQLUtil.get(JOIN_BY_ORGANIZATIONS_USERS);
1044                    }
1045                    else if (key.equals("orgGroupPermission")) {
1046                            join = CustomSQLUtil.get(JOIN_BY_ORG_GROUP_PERMISSION);
1047                    }
1048                    else if (key.equals("usersOrgs")) {
1049                            join = CustomSQLUtil.get(JOIN_BY_USERS_ORGS);
1050                    }
1051    
1052                    if (Validator.isNotNull(join)) {
1053                            int pos = join.indexOf("WHERE");
1054    
1055                            if (pos != -1) {
1056                                    join = join.substring(pos + 5, join.length()).concat(" AND ");
1057                            }
1058                            else {
1059                                    join = StringPool.BLANK;
1060                            }
1061                    }
1062    
1063                    return join;
1064            }
1065    
1066            protected void setJoin(
1067                    QueryPos qPos, LinkedHashMap<String, Object> params) {
1068    
1069                    if (params == null) {
1070                            return;
1071                    }
1072    
1073                    for (Map.Entry<String, Object> entry : params.entrySet()) {
1074                            String key = entry.getKey();
1075    
1076                            if (key.equals("expandoAttributes")) {
1077                                    continue;
1078                            }
1079    
1080                            Object value = entry.getValue();
1081    
1082                            if (key.equals("organizationsTree")) {
1083                                    List<Organization> organizationsTree =
1084                                            (List<Organization>)value;
1085    
1086                                    if (!organizationsTree.isEmpty()) {
1087                                            for (Organization organization : organizationsTree) {
1088                                                    StringBundler sb = new StringBundler(5);
1089    
1090                                                    sb.append(StringPool.PERCENT);
1091                                                    sb.append(StringPool.SLASH);
1092                                                    sb.append(organization.getOrganizationId());
1093                                                    sb.append(StringPool.SLASH);
1094                                                    sb.append(StringPool.PERCENT);
1095    
1096                                                    qPos.add(sb.toString());
1097                                            }
1098                                    }
1099                            }
1100                            else if (value instanceof Long) {
1101                                    Long valueLong = (Long)value;
1102    
1103                                    if (Validator.isNotNull(valueLong)) {
1104                                            qPos.add(valueLong);
1105                                    }
1106                            }
1107                            else if (value instanceof Long[]) {
1108                                    Long[] valueArray = (Long[])value;
1109    
1110                                    for (Long element : valueArray) {
1111                                            if (Validator.isNotNull(element)) {
1112                                                    qPos.add(element);
1113                                            }
1114                                    }
1115                            }
1116                            else if (value instanceof Long[][]) {
1117                                    Long[][] valueDoubleArray = (Long[][])value;
1118    
1119                                    for (Long[] valueArray : valueDoubleArray) {
1120                                            for (Long valueLong : valueArray) {
1121                                                    qPos.add(valueLong);
1122                                            }
1123                                    }
1124                            }
1125                            else if (value instanceof String) {
1126                                    String valueString = (String)value;
1127    
1128                                    if (Validator.isNotNull(valueString)) {
1129                                            qPos.add(valueString);
1130                                    }
1131                            }
1132                    }
1133            }
1134    
1135            protected static final String COUNTRY_ID_SQL =
1136                    "((Organization_.countryId = ?) OR (Address.countryId = ?)) " +
1137                            "[$AND_OR_CONNECTOR$]";
1138    
1139            protected static final String REGION_ID_SQL =
1140                    "((Organization_.regionId = ?) OR (Address.regionId = ?)) " +
1141                            "[$AND_OR_CONNECTOR$]";
1142    
1143    }