1   /**
2    * Copyright (c) 2000-2007 Liferay, Inc. All rights reserved.
3    *
4    * Permission is hereby granted, free of charge, to any person obtaining a copy
5    * of this software and associated documentation files (the "Software"), to deal
6    * in the Software without restriction, including without limitation the rights
7    * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
8    * copies of the Software, and to permit persons to whom the Software is
9    * furnished to do so, subject to the following conditions:
10   *
11   * The above copyright notice and this permission notice shall be included in
12   * all copies or substantial portions of the Software.
13   *
14   * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
15   * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
16   * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
17   * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
18   * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
19   * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
20   * SOFTWARE.
21   */
22  
23  package com.liferay.portal.service.persistence;
24  
25  import com.liferay.portal.SystemException;
26  import com.liferay.portal.kernel.util.OrderByComparator;
27  import com.liferay.portal.kernel.util.StringMaker;
28  import com.liferay.portal.kernel.util.StringPool;
29  import com.liferay.portal.kernel.util.StringUtil;
30  import com.liferay.portal.kernel.util.Validator;
31  import com.liferay.portal.model.Organization;
32  import com.liferay.portal.spring.hibernate.CustomSQLUtil;
33  import com.liferay.portal.spring.hibernate.HibernateUtil;
34  import com.liferay.util.dao.hibernate.QueryPos;
35  import com.liferay.util.dao.hibernate.QueryUtil;
36  
37  import java.util.ArrayList;
38  import java.util.Iterator;
39  import java.util.LinkedHashMap;
40  import java.util.List;
41  import java.util.Map;
42  
43  import org.hibernate.Hibernate;
44  import org.hibernate.SQLQuery;
45  import org.hibernate.Session;
46  
47  /**
48   * <a href="OrganizationFinder.java.html"><b><i>View Source</i></b></a>
49   *
50   * @author Brian Wing Shun Chan
51   *
52   */
53  public class OrganizationFinder {
54  
55      public static String COUNT_BY_C_PO_N_L_S_C_Z_R_C =
56          OrganizationFinder.class.getName() + ".countByC_PO_N_L_S_C_Z_R_C";
57  
58      public static String FIND_BY_C_PO_N_L_S_C_Z_R_C =
59          OrganizationFinder.class.getName() + ".findByC_PO_N_L_S_C_Z_R_C";
60  
61      public static String JOIN_BY_GROUPS_PERMISSIONS =
62          OrganizationFinder.class.getName() + ".joinByGroupsPermissions";
63  
64      public static String JOIN_BY_ORG_GROUP_PERMISSION =
65          OrganizationFinder.class.getName() + ".joinByOrgGroupPermission";
66  
67      public static String JOIN_BY_ORGANIZATIONS_GROUPS =
68          OrganizationFinder.class.getName() + ".joinByOrganizationsGroups";
69  
70      public static String JOIN_BY_ORGANIZATIONS_PASSWORD_POLICIES =
71          OrganizationFinder.class.getName() +
72              ".joinByOrganizationsPasswordPolicies";
73  
74      public static String JOIN_BY_ORGANIZATIONS_ROLES =
75          OrganizationFinder.class.getName() + ".joinByOrganizationsRoles";
76  
77      public static int countByKeywords(
78              long companyId, long parentOrganizationId,
79              String parentOrganizationComparator, String keywords,
80              boolean location, Long regionId, Long countryId,
81              LinkedHashMap params)
82          throws SystemException {
83  
84          String[] names = null;
85          String[] streets = null;
86          String[] cities = null;
87          String[] zips = null;
88          boolean andOperator = false;
89  
90          if (Validator.isNotNull(keywords)) {
91              names = CustomSQLUtil.keywords(keywords);
92              streets = CustomSQLUtil.keywords(keywords);
93              cities = CustomSQLUtil.keywords(keywords);
94              zips = CustomSQLUtil.keywords(keywords);
95          }
96          else {
97              andOperator = true;
98          }
99  
100         return countByC_PO_N_L_S_C_Z_R_C(
101             companyId, parentOrganizationId, parentOrganizationComparator,
102             names, location, streets, cities, zips, regionId, countryId, params,
103             andOperator);
104     }
105 
106     public static int countByC_PO_N_L_S_C_Z_R_C(
107             long companyId, long parentOrganizationId,
108             String parentOrganizationComparator, String name, boolean location,
109             String street, String city, String zip, Long regionId,
110             Long countryId, LinkedHashMap params, boolean andOperator)
111         throws SystemException {
112 
113         return countByC_PO_N_L_S_C_Z_R_C(
114             companyId, parentOrganizationId, parentOrganizationComparator,
115             new String[] {name}, location, new String[] {street},
116             new String[] {city}, new String[] {zip}, regionId, countryId,
117             params, andOperator);
118     }
119 
120     public static int countByC_PO_N_L_S_C_Z_R_C(
121             long companyId, long parentOrganizationId,
122             String parentOrganizationComparator, String[] names,
123             boolean location, String[] streets, String[] cities, String[] zips,
124             Long regionId, Long countryId, LinkedHashMap params,
125             boolean andOperator)
126         throws SystemException {
127 
128         names = CustomSQLUtil.keywords(names);
129         streets = CustomSQLUtil.keywords(streets);
130         cities = CustomSQLUtil.keywords(cities);
131         zips = CustomSQLUtil.keywords(zips);
132 
133         if (params != null) {
134             Long resourceId = (Long)params.get("permissionsResourceId");
135             Long groupId = (Long)params.get("permissionsGroupId");
136 
137             if (Validator.isNotNull(groupId) &&
138                     Validator.isNotNull(resourceId)) {
139 
140                 return _countByPermissions(
141                     companyId, parentOrganizationId,
142                     parentOrganizationComparator, names, location, streets,
143                     cities, zips, regionId, countryId, resourceId.longValue(),
144                     groupId.longValue(), andOperator);
145             }
146         }
147 
148         Session session = null;
149 
150         try {
151             session = HibernateUtil.openSession();
152 
153             String sql = CustomSQLUtil.get(COUNT_BY_C_PO_N_L_S_C_Z_R_C);
154 
155             sql = CustomSQLUtil.replaceKeywords(
156                 sql, "lower(Organization_.name)", StringPool.LIKE, false,
157                 names);
158             sql = CustomSQLUtil.replaceKeywords(
159                 sql, "lower(Address.street1)", StringPool.LIKE, true,
160                 streets);
161             sql = CustomSQLUtil.replaceKeywords(
162                 sql, "lower(Address.street2)", StringPool.LIKE, true,
163                 streets);
164             sql = CustomSQLUtil.replaceKeywords(
165                 sql, "lower(Address.street3)", StringPool.LIKE, true,
166                 streets);
167             sql = CustomSQLUtil.replaceKeywords(
168                 sql, "lower(Address.city)", StringPool.LIKE, false,
169                 cities);
170             sql = CustomSQLUtil.replaceKeywords(
171                 sql, "lower(Address.zip)", StringPool.LIKE, true,
172                 zips);
173 
174             if (regionId == null) {
175                 sql = StringUtil.replace(sql, _REGION_ID_SQL, StringPool.BLANK);
176             }
177 
178             if (countryId == null) {
179                 sql = StringUtil.replace(
180                     sql, _COUNTRY_ID_SQL, StringPool.BLANK);
181             }
182 
183             sql = StringUtil.replace(sql, "[$JOIN$]", _getJoin(params));
184             sql = StringUtil.replace(sql, "[$WHERE$]", _getWhere(params));
185             sql = StringUtil.replace(
186                 sql, "[$PARENT_ORGANIZATION_ID_COMPARATOR$]",
187                 parentOrganizationComparator);
188             sql = CustomSQLUtil.replaceAndOperator(sql, andOperator);
189 
190             SQLQuery q = session.createSQLQuery(sql);
191 
192             q.addScalar(HibernateUtil.getCountColumnName(), Hibernate.LONG);
193 
194             QueryPos qPos = QueryPos.getInstance(q);
195 
196             _setJoin(qPos, params);
197             qPos.add(companyId);
198             qPos.add(parentOrganizationId);
199             qPos.add(location);
200             qPos.add(names, 2);
201             qPos.add(streets, 6);
202 
203             if (regionId != null) {
204                 qPos.add(regionId);
205                 qPos.add(regionId);
206             }
207 
208             if (countryId != null) {
209                 qPos.add(countryId);
210                 qPos.add(countryId);
211             }
212 
213             qPos.add(cities, 2);
214             qPos.add(zips, 2);
215 
216             Iterator itr = q.list().iterator();
217 
218             if (itr.hasNext()) {
219                 Long count = (Long)itr.next();
220 
221                 if (count != null) {
222                     return count.intValue();
223                 }
224             }
225 
226             return 0;
227         }
228         catch (Exception e) {
229             throw new SystemException(e);
230         }
231         finally {
232             HibernateUtil.closeSession(session);
233         }
234     }
235 
236     public static List findByKeywords(
237             long companyId, long parentOrganizationId,
238             String parentOrganizationComparator, String keywords,
239             boolean location, Long regionId, Long countryId,
240             LinkedHashMap params, int begin, int end, OrderByComparator obc)
241         throws SystemException {
242 
243         String[] names = null;
244         String[] streets = null;
245         String[] cities = null;
246         String[] zips = null;
247         boolean andOperator = false;
248 
249         if (Validator.isNotNull(keywords)) {
250             names = CustomSQLUtil.keywords(keywords);
251             streets = CustomSQLUtil.keywords(keywords);
252             cities = CustomSQLUtil.keywords(keywords);
253             zips = CustomSQLUtil.keywords(keywords);
254         }
255         else {
256             andOperator = true;
257         }
258 
259         return findByC_PO_N_L_S_C_Z_R_C(
260             companyId, parentOrganizationId, parentOrganizationComparator,
261             names, location, streets, cities, zips, regionId, countryId, params,
262             andOperator, begin, end, obc);
263     }
264 
265     public static List findByC_PO_N_L_S_C_Z_R_C(
266             long companyId, long parentOrganizationId,
267             String parentOrganizationComparator, String name, boolean location,
268             String street, String city, String zip, Long regionId,
269             Long countryId, LinkedHashMap params, boolean andOperator,
270             int begin, int end, OrderByComparator obc)
271         throws SystemException {
272 
273         return findByC_PO_N_L_S_C_Z_R_C(
274             companyId, parentOrganizationId, parentOrganizationComparator,
275             new String[] {name}, location, new String[] {street},
276             new String[] {city}, new String[] {zip}, regionId, countryId,
277             params, andOperator, begin, end, obc);
278     }
279 
280     public static List findByC_PO_N_L_S_C_Z_R_C(
281             long companyId, long parentOrganizationId,
282             String parentOrganizationComparator, String[] names,
283             boolean location, String[] streets, String[] cities, String[] zips,
284             Long regionId, Long countryId, LinkedHashMap params,
285             boolean andOperator, int begin, int end, OrderByComparator obc)
286         throws SystemException {
287 
288         names = CustomSQLUtil.keywords(names);
289         streets = CustomSQLUtil.keywords(streets);
290         cities = CustomSQLUtil.keywords(cities);
291         zips = CustomSQLUtil.keywords(zips);
292 
293         if (params != null) {
294             Long resourceId = (Long)params.get("permissionsResourceId");
295             Long groupId = (Long)params.get("permissionsGroupId");
296 
297             if (Validator.isNotNull(groupId) &&
298                     Validator.isNotNull(resourceId)) {
299 
300                 return _findByPermissions(
301                     companyId, parentOrganizationId,
302                     parentOrganizationComparator, names, location, streets,
303                     cities, zips, regionId, countryId, resourceId.longValue(),
304                     groupId.longValue(), andOperator, begin, end, obc);
305             }
306         }
307 
308         Session session = null;
309 
310         try {
311             session = HibernateUtil.openSession();
312 
313             String sql = CustomSQLUtil.get(FIND_BY_C_PO_N_L_S_C_Z_R_C);
314 
315             sql = CustomSQLUtil.replaceKeywords(
316                 sql, "lower(Organization_.name)", StringPool.LIKE, false,
317                 names);
318             sql = CustomSQLUtil.replaceKeywords(
319                 sql, "lower(Address.street1)", StringPool.LIKE, true,
320                 streets);
321             sql = CustomSQLUtil.replaceKeywords(
322                 sql, "lower(Address.street2)", StringPool.LIKE, true,
323                 streets);
324             sql = CustomSQLUtil.replaceKeywords(
325                 sql, "lower(Address.street3)", StringPool.LIKE, true,
326                 streets);
327             sql = CustomSQLUtil.replaceKeywords(
328                 sql, "lower(Address.city)", StringPool.LIKE, false,
329                 cities);
330             sql = CustomSQLUtil.replaceKeywords(
331                 sql, "lower(Address.zip)", StringPool.LIKE, true,
332                 zips);
333 
334             if (regionId == null) {
335                 sql = StringUtil.replace(sql, _REGION_ID_SQL, StringPool.BLANK);
336             }
337 
338             if (countryId == null) {
339                 sql = StringUtil.replace(
340                     sql, _COUNTRY_ID_SQL, StringPool.BLANK);
341             }
342 
343             sql = StringUtil.replace(sql, "[$JOIN$]", _getJoin(params));
344             sql = StringUtil.replace(sql, "[$WHERE$]", _getWhere(params));
345             sql = StringUtil.replace(
346                 sql, "[$PARENT_ORGANIZATION_ID_COMPARATOR$]",
347                 parentOrganizationComparator);
348             sql = CustomSQLUtil.replaceAndOperator(sql, andOperator);
349             sql = CustomSQLUtil.replaceOrderBy(sql, obc);
350 
351             SQLQuery q = session.createSQLQuery(sql);
352 
353             q.addScalar("orgId", Hibernate.LONG);
354 
355             QueryPos qPos = QueryPos.getInstance(q);
356 
357             _setJoin(qPos, params);
358             qPos.add(companyId);
359             qPos.add(parentOrganizationId);
360             qPos.add(location);
361             qPos.add(names, 2);
362             qPos.add(streets, 6);
363 
364             if (regionId != null) {
365                 qPos.add(regionId);
366                 qPos.add(regionId);
367             }
368 
369             if (countryId != null) {
370                 qPos.add(countryId);
371                 qPos.add(countryId);
372             }
373 
374             qPos.add(cities, 2);
375             qPos.add(zips, 2);
376 
377             List list = new ArrayList();
378 
379             Iterator itr = QueryUtil.iterate(
380                 q, HibernateUtil.getDialect(), begin, end);
381 
382             while (itr.hasNext()) {
383                 Long organizationId = (Long)itr.next();
384 
385                 Organization organization = OrganizationUtil.findByPrimaryKey(
386                     organizationId.longValue());
387 
388                 list.add(organization);
389             }
390 
391             return list;
392         }
393         catch (Exception e) {
394             throw new SystemException(e);
395         }
396         finally {
397             HibernateUtil.closeSession(session);
398         }
399     }
400 
401     private static int _countByPermissions(
402             long companyId, long parentOrganizationId,
403             String parentOrganizationComparator, String[] names,
404             boolean location, String[] streets, String[] cities, String[] zips,
405             Long regionId, Long countryId, long resourceId, long groupId,
406             boolean andOperator)
407         throws SystemException {
408 
409         Session session = null;
410 
411         try {
412             session = HibernateUtil.openSession();
413 
414             StringMaker sm = new StringMaker();
415 
416             sm.append("(");
417 
418             sm.append(CustomSQLUtil.get(COUNT_BY_C_PO_N_L_S_C_Z_R_C));
419 
420             String sql = sm.toString();
421 
422             if (regionId == null) {
423                 sql = StringUtil.replace(sql, _REGION_ID_SQL, StringPool.BLANK);
424             }
425 
426             if (countryId == null) {
427                 sql = StringUtil.replace(
428                     sql, _COUNTRY_ID_SQL, StringPool.BLANK);
429             }
430 
431             sql = StringUtil.replace(
432                 sql, "[$JOIN$]", _getJoin("groupsPermissions"));
433             sql = StringUtil.replace(
434                 sql, "[$WHERE$]", _getWhere("groupsPermissions"));
435 
436             sm = new StringMaker();
437 
438             sm.append(sql);
439 
440             sm.append(") UNION (");
441 
442             sm.append(CustomSQLUtil.get(COUNT_BY_C_PO_N_L_S_C_Z_R_C));
443 
444             sql = sm.toString();
445 
446             if (regionId == null) {
447                 sql = StringUtil.replace(sql, _REGION_ID_SQL, StringPool.BLANK);
448             }
449 
450             if (countryId == null) {
451                 sql = StringUtil.replace(
452                     sql, _COUNTRY_ID_SQL, StringPool.BLANK);
453             }
454 
455             sql = StringUtil.replace(
456                 sql, "[$JOIN$]", _getJoin("orgGroupPermission"));
457             sql = StringUtil.replace(
458                 sql, "[$WHERE$]", _getWhere("orgGroupPermission"));
459             sql = StringUtil.replace(
460                 sql, "[$PARENT_ORGANIZATION_ID_COMPARATOR$]",
461                 parentOrganizationComparator);
462             sql = CustomSQLUtil.replaceAndOperator(sql, andOperator);
463 
464             sm = new StringMaker();
465 
466             sm.append(sql);
467 
468             sm.append(")");
469 
470             sql = sm.toString();
471 
472             sql = CustomSQLUtil.replaceKeywords(
473                 sql, "lower(Organization_.name)", StringPool.LIKE, false,
474                 names);
475             sql = CustomSQLUtil.replaceKeywords(
476                 sql, "lower(Address.street1)", StringPool.LIKE, true,
477                 streets);
478             sql = CustomSQLUtil.replaceKeywords(
479                 sql, "lower(Address.street2)", StringPool.LIKE, true,
480                 streets);
481             sql = CustomSQLUtil.replaceKeywords(
482                 sql, "lower(Address.street3)", StringPool.LIKE, true,
483                 streets);
484             sql = CustomSQLUtil.replaceKeywords(
485                 sql, "lower(Address.city)", StringPool.LIKE, false,
486                 cities);
487             sql = CustomSQLUtil.replaceKeywords(
488                 sql, "lower(Address.zip)", StringPool.LIKE, true,
489                 zips);
490 
491             if (regionId == null) {
492                 sql = StringUtil.replace(sql, _REGION_ID_SQL, StringPool.BLANK);
493             }
494 
495             if (countryId == null) {
496                 sql = StringUtil.replace(
497                     sql, _COUNTRY_ID_SQL, StringPool.BLANK);
498             }
499 
500             SQLQuery q = session.createSQLQuery(sql);
501 
502             q.addScalar(HibernateUtil.getCountColumnName(), Hibernate.LONG);
503 
504             QueryPos qPos = QueryPos.getInstance(q);
505 
506             for (int i = 0; i < 2; i++) {
507                 qPos.add(resourceId);
508 
509                 if (i == 1) {
510                     qPos.add(groupId);
511                 }
512 
513                 qPos.add(companyId);
514                 qPos.add(parentOrganizationId);
515                 qPos.add(location);
516                 qPos.add(names, 2);
517                 qPos.add(streets, 6);
518 
519                 if (regionId != null) {
520                     qPos.add(regionId);
521                     qPos.add(regionId);
522                 }
523 
524                 if (countryId != null) {
525                     qPos.add(countryId);
526                     qPos.add(countryId);
527                 }
528 
529                 qPos.add(cities, 2);
530                 qPos.add(zips, 2);
531             }
532 
533             int count = 0;
534 
535             Iterator itr = q.list().iterator();
536 
537             while (itr.hasNext()) {
538                 Long l = (Long)itr.next();
539 
540                 if (l != null) {
541                     count += l.intValue();
542                 }
543             }
544 
545             return count;
546         }
547         catch (Exception e) {
548             throw new SystemException(e);
549         }
550         finally {
551             HibernateUtil.closeSession(session);
552         }
553     }
554 
555     private static List _findByPermissions(
556             long companyId, long parentOrganizationId,
557             String parentOrganizationComparator, String[] names,
558             boolean location, String[] streets, String[] cities, String[] zips,
559             Long regionId, Long countryId, long resourceId, long groupId,
560             boolean andOperator, int begin, int end, OrderByComparator obc)
561         throws SystemException {
562 
563         Session session = null;
564 
565         try {
566             session = HibernateUtil.openSession();
567 
568             StringMaker sm = new StringMaker();
569 
570             sm.append("(");
571 
572             sm.append(CustomSQLUtil.get(FIND_BY_C_PO_N_L_S_C_Z_R_C));
573 
574             String sql = sm.toString();
575 
576             if (regionId == null) {
577                 sql = StringUtil.replace(sql, _REGION_ID_SQL, StringPool.BLANK);
578             }
579 
580             if (countryId == null) {
581                 sql = StringUtil.replace(
582                     sql, _COUNTRY_ID_SQL, StringPool.BLANK);
583             }
584 
585             sql = StringUtil.replace(
586                 sql, "[$JOIN$]", _getJoin("groupsPermissions"));
587             sql = StringUtil.replace(
588                 sql, "[$WHERE$]", _getWhere("groupsPermissions"));
589 
590             sm = new StringMaker();
591 
592             sm.append(sql);
593 
594             sm.append(") UNION (");
595 
596             sm.append(CustomSQLUtil.get(FIND_BY_C_PO_N_L_S_C_Z_R_C));
597 
598             sql = sm.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(
606                     sql, _COUNTRY_ID_SQL, StringPool.BLANK);
607             }
608 
609             sql = StringUtil.replace(
610                 sql, "[$JOIN$]", _getJoin("orgGroupPermission"));
611             sql = StringUtil.replace(
612                 sql, "[$WHERE$]", _getWhere("orgGroupPermission"));
613             sql = StringUtil.replace(
614                 sql, "[$PARENT_ORGANIZATION_ID_COMPARATOR$]",
615                 parentOrganizationComparator);
616             sql = CustomSQLUtil.replaceAndOperator(sql, andOperator);
617 
618             sm = new StringMaker();
619 
620             sm.append(sql);
621 
622             sm.append(") ");
623 
624             sql = sm.toString();
625 
626             sql = CustomSQLUtil.replaceKeywords(
627                 sql, "lower(Organization_.name)", StringPool.LIKE, false,
628                 names);
629             sql = CustomSQLUtil.replaceKeywords(
630                 sql, "lower(Address.street1)", StringPool.LIKE, true,
631                 streets);
632             sql = CustomSQLUtil.replaceKeywords(
633                 sql, "lower(Address.street2)", StringPool.LIKE, true,
634                 streets);
635             sql = CustomSQLUtil.replaceKeywords(
636                 sql, "lower(Address.street3)", StringPool.LIKE, true,
637                 streets);
638             sql = CustomSQLUtil.replaceKeywords(
639                 sql, "lower(Address.city)", StringPool.LIKE, false,
640                 cities);
641             sql = CustomSQLUtil.replaceKeywords(
642                 sql, "lower(Address.zip)", StringPool.LIKE, true,
643                 zips);
644 
645             if (regionId == null) {
646                 sql = StringUtil.replace(sql, _REGION_ID_SQL, StringPool.BLANK);
647             }
648 
649             if (countryId == null) {
650                 sql = StringUtil.replace(
651                     sql, _COUNTRY_ID_SQL, StringPool.BLANK);
652             }
653 
654             sql = CustomSQLUtil.replaceOrderBy(sql, obc);
655 
656             SQLQuery q = session.createSQLQuery(sql);
657 
658             q.addScalar("orgId", Hibernate.LONG);
659 
660             QueryPos qPos = QueryPos.getInstance(q);
661 
662             for (int i = 0; i < 2; i++) {
663                 qPos.add(resourceId);
664 
665                 if (i == 1) {
666                     qPos.add(groupId);
667                 }
668 
669                 qPos.add(companyId);
670                 qPos.add(parentOrganizationId);
671                 qPos.add(location);
672                 qPos.add(names, 2);
673                 qPos.add(streets, 6);
674 
675                 if (regionId != null) {
676                     qPos.add(regionId);
677                     qPos.add(regionId);
678                 }
679 
680                 if (countryId != null) {
681                     qPos.add(countryId);
682                     qPos.add(countryId);
683                 }
684 
685                 qPos.add(cities, 2);
686                 qPos.add(zips, 2);
687             }
688 
689             List list = new ArrayList();
690 
691             Iterator itr = QueryUtil.iterate(
692                 q, HibernateUtil.getDialect(), begin, end);
693 
694             while (itr.hasNext()) {
695                 Long organizationId = (Long)itr.next();
696 
697                 Organization organization = OrganizationUtil.findByPrimaryKey(
698                     organizationId.longValue());
699 
700                 list.add(organization);
701             }
702 
703             return list;
704         }
705         catch (Exception e) {
706             throw new SystemException(e);
707         }
708         finally {
709             HibernateUtil.closeSession(session);
710         }
711     }
712 
713     private static String _getJoin(LinkedHashMap params) {
714         if (params == null) {
715             return StringPool.BLANK;
716         }
717 
718         StringMaker sm = new StringMaker();
719 
720         Iterator itr = params.entrySet().iterator();
721 
722         while (itr.hasNext()) {
723             Map.Entry entry = (Map.Entry)itr.next();
724 
725             String key = (String)entry.getKey();
726             Object value = entry.getValue();
727 
728             if (Validator.isNotNull(value)) {
729                 sm.append(_getJoin(key));
730             }
731         }
732 
733         return sm.toString();
734     }
735 
736     private static String _getJoin(String key) {
737         String join = StringPool.BLANK;
738 
739         if (key.equals("groupsPermissions")) {
740             join = CustomSQLUtil.get(JOIN_BY_GROUPS_PERMISSIONS);
741         }
742         else if (key.equals("organizationsGroups")) {
743             join = CustomSQLUtil.get(JOIN_BY_ORGANIZATIONS_GROUPS);
744         }
745         else if (key.equals("organizationsPasswordPolicies")) {
746             join = CustomSQLUtil.get(JOIN_BY_ORGANIZATIONS_PASSWORD_POLICIES);
747         }
748         else if (key.equals("organizationsRoles")) {
749             join = CustomSQLUtil.get(JOIN_BY_ORGANIZATIONS_ROLES);
750         }
751         else if (key.equals("orgGroupPermission")) {
752             join = CustomSQLUtil.get(JOIN_BY_ORG_GROUP_PERMISSION);
753         }
754 
755         if (Validator.isNotNull(join)) {
756             int pos = join.indexOf("WHERE");
757 
758             if (pos != -1) {
759                 join = join.substring(0, pos);
760             }
761         }
762 
763         return join;
764     }
765 
766     private static String _getWhere(LinkedHashMap params) {
767         if (params == null) {
768             return StringPool.BLANK;
769         }
770 
771         StringMaker sm = new StringMaker();
772 
773         Iterator itr = params.entrySet().iterator();
774 
775         while (itr.hasNext()) {
776             Map.Entry entry = (Map.Entry)itr.next();
777 
778             String key = (String)entry.getKey();
779             Object value = entry.getValue();
780 
781             if (Validator.isNotNull(value)) {
782                 sm.append(_getWhere(key));
783             }
784         }
785 
786         return sm.toString();
787     }
788 
789     private static String _getWhere(String key) {
790         String join = StringPool.BLANK;
791 
792         if (key.equals("groupsPermissions")) {
793             join = CustomSQLUtil.get(JOIN_BY_GROUPS_PERMISSIONS);
794         }
795         else if (key.equals("organizationsGroups")) {
796             join = CustomSQLUtil.get(JOIN_BY_ORGANIZATIONS_GROUPS);
797         }
798         else if (key.equals("organizationsPasswordPolicies")) {
799             join = CustomSQLUtil.get(JOIN_BY_ORGANIZATIONS_PASSWORD_POLICIES);
800         }
801         else if (key.equals("organizationsRoles")) {
802             join = CustomSQLUtil.get(JOIN_BY_ORGANIZATIONS_ROLES);
803         }
804         else if (key.equals("orgGroupPermission")) {
805             join = CustomSQLUtil.get(JOIN_BY_ORG_GROUP_PERMISSION);
806         }
807 
808         if (Validator.isNotNull(join)) {
809             int pos = join.indexOf("WHERE");
810 
811             if (pos != -1) {
812                 StringMaker sm = new StringMaker();
813 
814                 sm.append(join.substring(pos + 5, join.length()));
815                 sm.append(" AND ");
816 
817                 join = sm.toString();
818             }
819         }
820 
821         return join;
822     }
823 
824     private static void _setJoin(QueryPos qPos, LinkedHashMap params) {
825         if (params != null) {
826             Iterator itr = params.entrySet().iterator();
827 
828             while (itr.hasNext()) {
829                 Map.Entry entry = (Map.Entry)itr.next();
830 
831                 Object value = entry.getValue();
832 
833                 if (value instanceof Long) {
834                     Long valueLong = (Long)value;
835 
836                     if (Validator.isNotNull(valueLong)) {
837                         qPos.add(valueLong);
838                     }
839                 }
840                 else if (value instanceof String) {
841                     String valueString = (String)value;
842 
843                     if (Validator.isNotNull(valueString)) {
844                         qPos.add(valueString);
845                     }
846                 }
847             }
848         }
849     }
850 
851     private static String _COUNTRY_ID_SQL =
852         "((Organization_.countryId = ?) OR (Address.countryId = ?)) " +
853             "[$AND_OR_CONNECTOR$]";
854 
855     private static String _REGION_ID_SQL =
856         "((Organization_.regionId = ?) OR (Address.regionId = ?)) " +
857             "[$AND_OR_CONNECTOR$]";
858 
859 }