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