1   /**
2    * Copyright (c) 2000-2008 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.model.OrganizationConstants;
33  import com.liferay.portal.spring.hibernate.CustomSQLUtil;
34  import com.liferay.portal.spring.hibernate.HibernateUtil;
35  import com.liferay.util.dao.hibernate.QueryPos;
36  import com.liferay.util.dao.hibernate.QueryUtil;
37  
38  import java.util.ArrayList;
39  import java.util.Iterator;
40  import java.util.LinkedHashMap;
41  import java.util.List;
42  import java.util.Map;
43  
44  import org.hibernate.Hibernate;
45  import org.hibernate.SQLQuery;
46  import org.hibernate.Session;
47  
48  /**
49   * <a href="OrganizationFinderImpl.java.html"><b><i>View Source</i></b></a>
50   *
51   * @author Brian Wing Shun Chan
52   * @author Jorge Ferrer
53   *
54   */
55  public class OrganizationFinderImpl implements OrganizationFinder {
56  
57      public static String COUNT_BY_C_PO_N_S_C_Z_R_C =
58          OrganizationFinder.class.getName() + ".countByC_PO_N_S_C_Z_R_C";
59  
60      public static String COUNT_BY_C_PO_N_L_S_C_Z_R_C =
61          OrganizationFinder.class.getName() + ".countByC_PO_N_L_S_C_Z_R_C";
62  
63      public static String FIND_BY_C_PO_N_S_C_Z_R_C =
64          OrganizationFinder.class.getName() + ".findByC_PO_N_S_C_Z_R_C";
65  
66      public static String FIND_BY_C_PO_N_L_S_C_Z_R_C =
67          OrganizationFinder.class.getName() + ".findByC_PO_N_L_S_C_Z_R_C";
68  
69      public static String JOIN_BY_GROUPS_PERMISSIONS =
70          OrganizationFinder.class.getName() + ".joinByGroupsPermissions";
71  
72      public static String JOIN_BY_ORGANIZATIONS_GROUPS =
73          OrganizationFinder.class.getName() + ".joinByOrganizationsGroups";
74  
75      public static String JOIN_BY_ORGANIZATIONS_PASSWORD_POLICIES =
76          OrganizationFinder.class.getName() +
77              ".joinByOrganizationsPasswordPolicies";
78  
79      public static String JOIN_BY_ORGANIZATIONS_ROLES =
80          OrganizationFinder.class.getName() + ".joinByOrganizationsRoles";
81  
82      public static String JOIN_BY_ORGANIZATIONS_USERS =
83          OrganizationFinder.class.getName() + ".joinByOrganizationsUsers";
84  
85      public static String JOIN_BY_ORG_GROUP_PERMISSION =
86          OrganizationFinder.class.getName() + ".joinByOrgGroupPermission";
87  
88      public int countByKeywords(
89              long companyId, long parentOrganizationId,
90              String parentOrganizationComparator, String keywords,
91              int type, Long regionId, Long countryId,
92              LinkedHashMap<String, Object> params)
93          throws SystemException {
94  
95          String[] names = null;
96          String[] streets = null;
97          String[] cities = null;
98          String[] zips = null;
99          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, parentOrganizationComparator,
113             names, type, streets, cities, zips, regionId, countryId, params,
114             andOperator);
115     }
116 
117     public int countByC_PO_N_T_S_C_Z_R_C(
118             long companyId, long parentOrganizationId,
119             String parentOrganizationComparator, String name, int type,
120             String street, String city, String zip, Long regionId,
121             Long countryId, LinkedHashMap<String, Object> params,
122             boolean andOperator)
123         throws SystemException {
124 
125         return countByC_PO_N_T_S_C_Z_R_C(
126             companyId, parentOrganizationId, parentOrganizationComparator,
127             new String[] {name}, type, new String[] {street},
128             new String[] {city}, new String[] {zip}, regionId, countryId,
129             params, andOperator);
130     }
131 
132     public int countByC_PO_N_T_S_C_Z_R_C(
133             long companyId, long parentOrganizationId,
134             String parentOrganizationComparator, String[] names,
135             int type, String[] streets, String[] cities, String[] zips,
136             Long regionId, Long countryId, LinkedHashMap<String, Object> params,
137             boolean andOperator)
138         throws SystemException {
139 
140         names = CustomSQLUtil.keywords(names);
141         streets = CustomSQLUtil.keywords(streets);
142         cities = CustomSQLUtil.keywords(cities);
143         zips = CustomSQLUtil.keywords(zips);
144 
145         if (params != null) {
146             Long resourceId = (Long)params.get("permissionsResourceId");
147             Long groupId = (Long)params.get("permissionsGroupId");
148 
149             if (Validator.isNotNull(groupId) &&
150                     Validator.isNotNull(resourceId)) {
151 
152                 return countByPermissions(
153                     companyId, parentOrganizationId,
154                     parentOrganizationComparator, names, type, streets,
155                     cities, zips, regionId, countryId, resourceId.longValue(),
156                     groupId.longValue(), andOperator);
157             }
158         }
159 
160         Session session = null;
161 
162         try {
163             session = HibernateUtil.openSession();
164 
165             String sql = null;
166 
167             if (type == OrganizationConstants.TYPE_LOCATION ||
168                 type == OrganizationConstants.TYPE_REGULAR) {
169 
170                 sql = CustomSQLUtil.get(COUNT_BY_C_PO_N_L_S_C_Z_R_C);
171             }
172             else {
173                 sql = CustomSQLUtil.get(COUNT_BY_C_PO_N_S_C_Z_R_C);
174             }
175 
176             sql = CustomSQLUtil.replaceKeywords(
177                 sql, "lower(Organization_.name)", StringPool.LIKE, false,
178                 names);
179             sql = CustomSQLUtil.replaceKeywords(
180                 sql, "lower(Address.street1)", StringPool.LIKE, true,
181                 streets);
182             sql = CustomSQLUtil.replaceKeywords(
183                 sql, "lower(Address.street2)", StringPool.LIKE, true,
184                 streets);
185             sql = CustomSQLUtil.replaceKeywords(
186                 sql, "lower(Address.street3)", StringPool.LIKE, true,
187                 streets);
188             sql = CustomSQLUtil.replaceKeywords(
189                 sql, "lower(Address.city)", StringPool.LIKE, false,
190                 cities);
191             sql = CustomSQLUtil.replaceKeywords(
192                 sql, "lower(Address.zip)", StringPool.LIKE, true,
193                 zips);
194 
195             if (regionId == null) {
196                 sql = StringUtil.replace(sql, REGION_ID_SQL, StringPool.BLANK);
197             }
198 
199             if (countryId == null) {
200                 sql = StringUtil.replace(sql, COUNTRY_ID_SQL, StringPool.BLANK);
201             }
202 
203             sql = StringUtil.replace(sql, "[$JOIN$]", getJoin(params));
204             sql = StringUtil.replace(sql, "[$WHERE$]", getWhere(params));
205             sql = StringUtil.replace(
206                 sql, "[$PARENT_ORGANIZATION_ID_COMPARATOR$]",
207                 parentOrganizationComparator);
208             sql = CustomSQLUtil.replaceAndOperator(sql, andOperator);
209 
210             SQLQuery q = session.createSQLQuery(sql);
211 
212             q.addScalar(HibernateUtil.getCountColumnName(), Hibernate.LONG);
213 
214             QueryPos qPos = QueryPos.getInstance(q);
215 
216             setJoin(qPos, params);
217             qPos.add(companyId);
218             qPos.add(parentOrganizationId);
219 
220             if (type == OrganizationConstants.TYPE_LOCATION) {
221                 qPos.add(true);
222             }
223             else if (type == OrganizationConstants.TYPE_REGULAR) {
224                 qPos.add(false);
225             }
226 
227             qPos.add(names, 2);
228             qPos.add(streets, 6);
229 
230             if (regionId != null) {
231                 qPos.add(regionId);
232                 qPos.add(regionId);
233             }
234 
235             if (countryId != null) {
236                 qPos.add(countryId);
237                 qPos.add(countryId);
238             }
239 
240             qPos.add(cities, 2);
241             qPos.add(zips, 2);
242 
243             Iterator<Long> itr = q.list().iterator();
244 
245             if (itr.hasNext()) {
246                 Long count = itr.next();
247 
248                 if (count != null) {
249                     return count.intValue();
250                 }
251             }
252 
253             return 0;
254         }
255         catch (Exception e) {
256             throw new SystemException(e);
257         }
258         finally {
259             HibernateUtil.closeSession(session);
260         }
261     }
262 
263     public List<Organization> findByKeywords(
264             long companyId, long parentOrganizationId,
265             String parentOrganizationComparator, String keywords,
266             int type, Long regionId, Long countryId,
267             LinkedHashMap<String, Object> params, int begin, int end,
268             OrderByComparator obc)
269         throws SystemException {
270 
271         String[] names = null;
272         String[] streets = null;
273         String[] cities = null;
274         String[] zips = null;
275         boolean andOperator = false;
276 
277         if (Validator.isNotNull(keywords)) {
278             names = CustomSQLUtil.keywords(keywords);
279             streets = CustomSQLUtil.keywords(keywords);
280             cities = CustomSQLUtil.keywords(keywords);
281             zips = CustomSQLUtil.keywords(keywords);
282         }
283         else {
284             andOperator = true;
285         }
286 
287         return findByC_PO_N_T_S_C_Z_R_C(
288             companyId, parentOrganizationId, parentOrganizationComparator,
289             names, type, streets, cities, zips, regionId, countryId, params,
290             andOperator, begin, end, obc);
291     }
292 
293     public List<Organization> findByC_PO_N_T_S_C_Z_R_C(
294             long companyId, long parentOrganizationId,
295             String parentOrganizationComparator, String name, int type,
296             String street, String city, String zip, Long regionId,
297             Long countryId, LinkedHashMap<String, Object> params,
298             boolean andOperator, int begin, int end, OrderByComparator obc)
299         throws SystemException {
300 
301         return findByC_PO_N_T_S_C_Z_R_C(
302             companyId, parentOrganizationId, parentOrganizationComparator,
303             new String[] {name}, type, new String[] {street},
304             new String[] {city}, new String[] {zip}, regionId, countryId,
305             params, andOperator, begin, end, obc);
306     }
307 
308     public List<Organization> findByC_PO_N_T_S_C_Z_R_C(
309             long companyId, long parentOrganizationId,
310             String parentOrganizationComparator, String[] names,
311             int type, String[] streets, String[] cities, String[] zips,
312             Long regionId, Long countryId, LinkedHashMap<String, Object> params,
313             boolean andOperator, int begin, int end, OrderByComparator obc)
314         throws SystemException {
315 
316         names = CustomSQLUtil.keywords(names);
317         streets = CustomSQLUtil.keywords(streets);
318         cities = CustomSQLUtil.keywords(cities);
319         zips = CustomSQLUtil.keywords(zips);
320 
321         if (params != null) {
322             Long resourceId = (Long)params.get("permissionsResourceId");
323             Long groupId = (Long)params.get("permissionsGroupId");
324 
325             if (Validator.isNotNull(groupId) &&
326                     Validator.isNotNull(resourceId)) {
327 
328                 return findByPermissions(
329                     companyId, parentOrganizationId,
330                     parentOrganizationComparator, names, type, streets,
331                     cities, zips, regionId, countryId, resourceId.longValue(),
332                     groupId.longValue(), andOperator, begin, end, obc);
333             }
334         }
335 
336         Session session = null;
337 
338         try {
339             session = HibernateUtil.openSession();
340 
341             String sql = null;
342 
343             if (type == OrganizationConstants.TYPE_LOCATION ||
344                 type == OrganizationConstants.TYPE_REGULAR) {
345 
346                 sql = CustomSQLUtil.get(FIND_BY_C_PO_N_L_S_C_Z_R_C);
347             }
348             else {
349                 sql = CustomSQLUtil.get(FIND_BY_C_PO_N_S_C_Z_R_C);
350             }
351 
352             sql = CustomSQLUtil.replaceKeywords(
353                 sql, "lower(Organization_.name)", StringPool.LIKE, false,
354                 names);
355             sql = CustomSQLUtil.replaceKeywords(
356                 sql, "lower(Address.street1)", StringPool.LIKE, true,
357                 streets);
358             sql = CustomSQLUtil.replaceKeywords(
359                 sql, "lower(Address.street2)", StringPool.LIKE, true,
360                 streets);
361             sql = CustomSQLUtil.replaceKeywords(
362                 sql, "lower(Address.street3)", StringPool.LIKE, true,
363                 streets);
364             sql = CustomSQLUtil.replaceKeywords(
365                 sql, "lower(Address.city)", StringPool.LIKE, false,
366                 cities);
367             sql = CustomSQLUtil.replaceKeywords(
368                 sql, "lower(Address.zip)", StringPool.LIKE, true,
369                 zips);
370 
371             if (regionId == null) {
372                 sql = StringUtil.replace(sql, REGION_ID_SQL, StringPool.BLANK);
373             }
374 
375             if (countryId == null) {
376                 sql = StringUtil.replace(sql, COUNTRY_ID_SQL, StringPool.BLANK);
377             }
378 
379             sql = StringUtil.replace(sql, "[$JOIN$]", getJoin(params));
380             sql = StringUtil.replace(sql, "[$WHERE$]", getWhere(params));
381             sql = StringUtil.replace(
382                 sql, "[$PARENT_ORGANIZATION_ID_COMPARATOR$]",
383                 parentOrganizationComparator);
384             sql = CustomSQLUtil.replaceAndOperator(sql, andOperator);
385             sql = CustomSQLUtil.replaceOrderBy(sql, obc);
386 
387             SQLQuery q = session.createSQLQuery(sql);
388 
389             q.addScalar("orgId", Hibernate.LONG);
390 
391             QueryPos qPos = QueryPos.getInstance(q);
392 
393             setJoin(qPos, params);
394             qPos.add(companyId);
395             qPos.add(parentOrganizationId);
396 
397             if (type == OrganizationConstants.TYPE_LOCATION) {
398                 qPos.add(true);
399             }
400             else if (type == OrganizationConstants.TYPE_REGULAR) {
401                 qPos.add(false);
402             }
403 
404             qPos.add(names, 2);
405             qPos.add(streets, 6);
406 
407             if (regionId != null) {
408                 qPos.add(regionId);
409                 qPos.add(regionId);
410             }
411 
412             if (countryId != null) {
413                 qPos.add(countryId);
414                 qPos.add(countryId);
415             }
416 
417             qPos.add(cities, 2);
418             qPos.add(zips, 2);
419 
420             List<Organization> organizations = new ArrayList<Organization>();
421 
422             Iterator<Long> itr = (Iterator<Long>)QueryUtil.iterate(
423                 q, HibernateUtil.getDialect(), begin, end);
424 
425             while (itr.hasNext()) {
426                 Long organizationId = itr.next();
427 
428                 Organization organization = OrganizationUtil.findByPrimaryKey(
429                     organizationId.longValue());
430 
431                 organizations.add(organization);
432             }
433 
434             return organizations;
435         }
436         catch (Exception e) {
437             throw new SystemException(e);
438         }
439         finally {
440             HibernateUtil.closeSession(session);
441         }
442     }
443 
444     protected int countByPermissions(
445             long companyId, long parentOrganizationId,
446             String parentOrganizationComparator, String[] names,
447             int type, String[] streets, String[] cities, String[] zips,
448             Long regionId, Long countryId, long resourceId, long groupId,
449             boolean andOperator)
450         throws SystemException {
451 
452         Session session = null;
453 
454         try {
455             session = HibernateUtil.openSession();
456 
457             StringMaker sm = new StringMaker();
458 
459             sm.append("(");
460 
461             if (type == OrganizationConstants.TYPE_LOCATION ||
462                 type == OrganizationConstants.TYPE_REGULAR) {
463 
464                 sm.append(CustomSQLUtil.get(COUNT_BY_C_PO_N_L_S_C_Z_R_C));
465             }
466             else {
467                 sm.append(CustomSQLUtil.get(COUNT_BY_C_PO_N_S_C_Z_R_C));
468             }
469 
470             String sql = sm.toString();
471 
472             if (regionId == null) {
473                 sql = StringUtil.replace(sql, REGION_ID_SQL, StringPool.BLANK);
474             }
475 
476             if (countryId == null) {
477                 sql = StringUtil.replace(sql, COUNTRY_ID_SQL, StringPool.BLANK);
478             }
479 
480             sql = StringUtil.replace(
481                 sql, "[$JOIN$]", getJoin("groupsPermissions"));
482             sql = StringUtil.replace(
483                 sql, "[$WHERE$]", getWhere("groupsPermissions"));
484 
485             sm = new StringMaker();
486 
487             sm.append(sql);
488 
489             sm.append(") UNION (");
490 
491             if (type == OrganizationConstants.TYPE_LOCATION ||
492                 type == OrganizationConstants.TYPE_REGULAR) {
493 
494                 sm.append(CustomSQLUtil.get(COUNT_BY_C_PO_N_L_S_C_Z_R_C));
495             }
496             else {
497                 sm.append(CustomSQLUtil.get(COUNT_BY_C_PO_N_S_C_Z_R_C));
498             }
499 
500             sql = sm.toString();
501 
502             if (regionId == null) {
503                 sql = StringUtil.replace(sql, REGION_ID_SQL, StringPool.BLANK);
504             }
505 
506             if (countryId == null) {
507                 sql = StringUtil.replace(sql, COUNTRY_ID_SQL, StringPool.BLANK);
508             }
509 
510             sql = StringUtil.replace(
511                 sql, "[$JOIN$]", getJoin("orgGroupPermission"));
512             sql = StringUtil.replace(
513                 sql, "[$WHERE$]", getWhere("orgGroupPermission"));
514             sql = StringUtil.replace(
515                 sql, "[$PARENT_ORGANIZATION_ID_COMPARATOR$]",
516                 parentOrganizationComparator);
517             sql = CustomSQLUtil.replaceAndOperator(sql, andOperator);
518 
519             sm = new StringMaker();
520 
521             sm.append(sql);
522 
523             sm.append(")");
524 
525             sql = sm.toString();
526 
527             sql = CustomSQLUtil.replaceKeywords(
528                 sql, "lower(Organization_.name)", StringPool.LIKE, false,
529                 names);
530             sql = CustomSQLUtil.replaceKeywords(
531                 sql, "lower(Address.street1)", StringPool.LIKE, true,
532                 streets);
533             sql = CustomSQLUtil.replaceKeywords(
534                 sql, "lower(Address.street2)", StringPool.LIKE, true,
535                 streets);
536             sql = CustomSQLUtil.replaceKeywords(
537                 sql, "lower(Address.street3)", StringPool.LIKE, true,
538                 streets);
539             sql = CustomSQLUtil.replaceKeywords(
540                 sql, "lower(Address.city)", StringPool.LIKE, false,
541                 cities);
542             sql = CustomSQLUtil.replaceKeywords(
543                 sql, "lower(Address.zip)", StringPool.LIKE, true,
544                 zips);
545 
546             if (regionId == null) {
547                 sql = StringUtil.replace(sql, REGION_ID_SQL, StringPool.BLANK);
548             }
549 
550             if (countryId == null) {
551                 sql = StringUtil.replace(sql, COUNTRY_ID_SQL, StringPool.BLANK);
552             }
553 
554             SQLQuery q = session.createSQLQuery(sql);
555 
556             q.addScalar(HibernateUtil.getCountColumnName(), Hibernate.LONG);
557 
558             QueryPos qPos = QueryPos.getInstance(q);
559 
560             for (int i = 0; i < 2; i++) {
561                 qPos.add(resourceId);
562 
563                 if (i == 1) {
564                     qPos.add(groupId);
565                 }
566 
567                 qPos.add(companyId);
568                 qPos.add(parentOrganizationId);
569 
570                 if (type == OrganizationConstants.TYPE_LOCATION) {
571                     qPos.add(true);
572                 }
573                 else if (type == OrganizationConstants.TYPE_REGULAR) {
574                     qPos.add(false);
575                 }
576 
577                 qPos.add(names, 2);
578                 qPos.add(streets, 6);
579 
580                 if (regionId != null) {
581                     qPos.add(regionId);
582                     qPos.add(regionId);
583                 }
584 
585                 if (countryId != null) {
586                     qPos.add(countryId);
587                     qPos.add(countryId);
588                 }
589 
590                 qPos.add(cities, 2);
591                 qPos.add(zips, 2);
592             }
593 
594             int count = 0;
595 
596             Iterator<Long> itr = q.list().iterator();
597 
598             while (itr.hasNext()) {
599                 Long l = itr.next();
600 
601                 if (l != null) {
602                     count += l.intValue();
603                 }
604             }
605 
606             return count;
607         }
608         catch (Exception e) {
609             throw new SystemException(e);
610         }
611         finally {
612             HibernateUtil.closeSession(session);
613         }
614     }
615 
616     protected List<Organization> findByPermissions(
617             long companyId, long parentOrganizationId,
618             String parentOrganizationComparator, String[] names,
619             int type, String[] streets, String[] cities, String[] zips,
620             Long regionId, Long countryId, long resourceId, long groupId,
621             boolean andOperator, int begin, int end, OrderByComparator obc)
622         throws SystemException {
623 
624         Session session = null;
625 
626         try {
627             session = HibernateUtil.openSession();
628 
629             StringMaker sm = new StringMaker();
630 
631             sm.append("(");
632 
633             if (type == OrganizationConstants.TYPE_LOCATION ||
634                 type == OrganizationConstants.TYPE_REGULAR) {
635 
636                 sm.append(CustomSQLUtil.get(FIND_BY_C_PO_N_L_S_C_Z_R_C));
637             }
638             else {
639                 sm.append(CustomSQLUtil.get(FIND_BY_C_PO_N_S_C_Z_R_C));
640             }
641 
642             String sql = sm.toString();
643 
644             if (regionId == null) {
645                 sql = StringUtil.replace(sql, REGION_ID_SQL, StringPool.BLANK);
646             }
647 
648             if (countryId == null) {
649                 sql = StringUtil.replace(sql, COUNTRY_ID_SQL, StringPool.BLANK);
650             }
651 
652             sql = StringUtil.replace(
653                 sql, "[$JOIN$]", getJoin("groupsPermissions"));
654             sql = StringUtil.replace(
655                 sql, "[$WHERE$]", getWhere("groupsPermissions"));
656 
657             sm = new StringMaker();
658 
659             sm.append(sql);
660 
661             sm.append(") UNION (");
662 
663             if (type == OrganizationConstants.TYPE_LOCATION ||
664                 type == OrganizationConstants.TYPE_REGULAR) {
665 
666                 sm.append(CustomSQLUtil.get(FIND_BY_C_PO_N_L_S_C_Z_R_C));
667             }
668             else {
669                 sm.append(CustomSQLUtil.get(FIND_BY_C_PO_N_S_C_Z_R_C));
670             }
671 
672             sql = sm.toString();
673 
674             if (regionId == null) {
675                 sql = StringUtil.replace(sql, REGION_ID_SQL, StringPool.BLANK);
676             }
677 
678             if (countryId == null) {
679                 sql = StringUtil.replace(sql, COUNTRY_ID_SQL, StringPool.BLANK);
680             }
681 
682             sql = StringUtil.replace(
683                 sql, "[$JOIN$]", getJoin("orgGroupPermission"));
684             sql = StringUtil.replace(
685                 sql, "[$WHERE$]", getWhere("orgGroupPermission"));
686             sql = StringUtil.replace(
687                 sql, "[$PARENT_ORGANIZATION_ID_COMPARATOR$]",
688                 parentOrganizationComparator);
689             sql = CustomSQLUtil.replaceAndOperator(sql, andOperator);
690 
691             sm = new StringMaker();
692 
693             sm.append(sql);
694 
695             sm.append(") ");
696 
697             sql = sm.toString();
698 
699             sql = CustomSQLUtil.replaceKeywords(
700                 sql, "lower(Organization_.name)", StringPool.LIKE, false,
701                 names);
702             sql = CustomSQLUtil.replaceKeywords(
703                 sql, "lower(Address.street1)", StringPool.LIKE, true,
704                 streets);
705             sql = CustomSQLUtil.replaceKeywords(
706                 sql, "lower(Address.street2)", StringPool.LIKE, true,
707                 streets);
708             sql = CustomSQLUtil.replaceKeywords(
709                 sql, "lower(Address.street3)", StringPool.LIKE, true,
710                 streets);
711             sql = CustomSQLUtil.replaceKeywords(
712                 sql, "lower(Address.city)", StringPool.LIKE, false,
713                 cities);
714             sql = CustomSQLUtil.replaceKeywords(
715                 sql, "lower(Address.zip)", StringPool.LIKE, true,
716                 zips);
717 
718             if (regionId == null) {
719                 sql = StringUtil.replace(sql, REGION_ID_SQL, StringPool.BLANK);
720             }
721 
722             if (countryId == null) {
723                 sql = StringUtil.replace(sql, COUNTRY_ID_SQL, StringPool.BLANK);
724             }
725 
726             sql = CustomSQLUtil.replaceOrderBy(sql, obc);
727 
728             SQLQuery q = session.createSQLQuery(sql);
729 
730             q.addScalar("orgId", Hibernate.LONG);
731 
732             QueryPos qPos = QueryPos.getInstance(q);
733 
734             for (int i = 0; i < 2; i++) {
735                 qPos.add(resourceId);
736 
737                 if (i == 1) {
738                     qPos.add(groupId);
739                 }
740 
741                 qPos.add(companyId);
742                 qPos.add(parentOrganizationId);
743 
744                 if (type == OrganizationConstants.TYPE_LOCATION) {
745                     qPos.add(true);
746                 }
747                 else if (type == OrganizationConstants.TYPE_REGULAR) {
748                     qPos.add(false);
749                 }
750 
751                 qPos.add(names, 2);
752                 qPos.add(streets, 6);
753 
754                 if (regionId != null) {
755                     qPos.add(regionId);
756                     qPos.add(regionId);
757                 }
758 
759                 if (countryId != null) {
760                     qPos.add(countryId);
761                     qPos.add(countryId);
762                 }
763 
764                 qPos.add(cities, 2);
765                 qPos.add(zips, 2);
766             }
767 
768             List<Organization> organizations = new ArrayList<Organization>();
769 
770             Iterator<Long> itr = (Iterator<Long>)QueryUtil.iterate(
771                 q, HibernateUtil.getDialect(), begin, end);
772 
773             while (itr.hasNext()) {
774                 Long organizationId = itr.next();
775 
776                 Organization organization = OrganizationUtil.findByPrimaryKey(
777                     organizationId.longValue());
778 
779                 organizations.add(organization);
780             }
781 
782             return organizations;
783         }
784         catch (Exception e) {
785             throw new SystemException(e);
786         }
787         finally {
788             HibernateUtil.closeSession(session);
789         }
790     }
791 
792     protected String getJoin(LinkedHashMap<String, Object> params) {
793         if (params == null) {
794             return StringPool.BLANK;
795         }
796 
797         StringMaker sm = new StringMaker();
798 
799         Iterator<Map.Entry<String, Object>> itr = params.entrySet().iterator();
800 
801         while (itr.hasNext()) {
802             Map.Entry<String, Object> entry = itr.next();
803 
804             String key = entry.getKey();
805             Object value = entry.getValue();
806 
807             if (Validator.isNotNull(value)) {
808                 sm.append(getJoin(key));
809             }
810         }
811 
812         return sm.toString();
813     }
814 
815     protected String getJoin(String key) {
816         String join = StringPool.BLANK;
817 
818         if (key.equals("groupsPermissions")) {
819             join = CustomSQLUtil.get(JOIN_BY_GROUPS_PERMISSIONS);
820         }
821         else if (key.equals("organizationsGroups")) {
822             join = CustomSQLUtil.get(JOIN_BY_ORGANIZATIONS_GROUPS);
823         }
824         else if (key.equals("organizationsPasswordPolicies")) {
825             join = CustomSQLUtil.get(JOIN_BY_ORGANIZATIONS_PASSWORD_POLICIES);
826         }
827         else if (key.equals("organizationsRoles")) {
828             join = CustomSQLUtil.get(JOIN_BY_ORGANIZATIONS_ROLES);
829         }
830         else if (key.equals("organizationsUsers")) {
831             join = CustomSQLUtil.get(JOIN_BY_ORGANIZATIONS_USERS);
832         }
833         else if (key.equals("orgGroupPermission")) {
834             join = CustomSQLUtil.get(JOIN_BY_ORG_GROUP_PERMISSION);
835         }
836 
837         if (Validator.isNotNull(join)) {
838             int pos = join.indexOf("WHERE");
839 
840             if (pos != -1) {
841                 join = join.substring(0, pos);
842             }
843         }
844 
845         return join;
846     }
847 
848     protected String getWhere(LinkedHashMap<String, Object> params) {
849         if (params == null) {
850             return StringPool.BLANK;
851         }
852 
853         StringMaker sm = new StringMaker();
854 
855         Iterator<Map.Entry<String, Object>> itr = params.entrySet().iterator();
856 
857         while (itr.hasNext()) {
858             Map.Entry<String, Object> entry = itr.next();
859 
860             String key = entry.getKey();
861             Object value = entry.getValue();
862 
863             if (Validator.isNotNull(value)) {
864                 sm.append(getWhere(key, value));
865             }
866         }
867 
868         return sm.toString();
869     }
870 
871     protected String getWhere(String key) {
872         return getWhere(key, null);
873     }
874 
875     protected String getWhere(String key, Object value) {
876         String join = StringPool.BLANK;
877 
878         if (key.equals("groupsPermissions")) {
879             join = CustomSQLUtil.get(JOIN_BY_GROUPS_PERMISSIONS);
880         }
881         else if (key.equals("organizations")) {
882             Long[] organizationIds = (Long[])value;
883 
884             StringMaker sm = new StringMaker();
885 
886             sm.append("WHERE (");
887 
888             for (int i = 0; i < organizationIds.length; i++) {
889                 sm.append("(Organization_.organizationId = ?) ");
890 
891                 if ((i + 1) < organizationIds.length) {
892                     sm.append("OR ");
893                 }
894             }
895 
896             sm.append(")");
897 
898             join = sm.toString();
899         }
900         else if (key.equals("organizationsGroups")) {
901             join = CustomSQLUtil.get(JOIN_BY_ORGANIZATIONS_GROUPS);
902         }
903         else if (key.equals("organizationsPasswordPolicies")) {
904             join = CustomSQLUtil.get(JOIN_BY_ORGANIZATIONS_PASSWORD_POLICIES);
905         }
906         else if (key.equals("organizationsRoles")) {
907             join = CustomSQLUtil.get(JOIN_BY_ORGANIZATIONS_ROLES);
908         }
909         else if (key.equals("organizationsUsers")) {
910             join = CustomSQLUtil.get(JOIN_BY_ORGANIZATIONS_USERS);
911         }
912         else if (key.equals("orgGroupPermission")) {
913             join = CustomSQLUtil.get(JOIN_BY_ORG_GROUP_PERMISSION);
914         }
915 
916         if (Validator.isNotNull(join)) {
917             int pos = join.indexOf("WHERE");
918 
919             if (pos != -1) {
920                 StringMaker sm = new StringMaker();
921 
922                 sm.append(join.substring(pos + 5, join.length()));
923                 sm.append(" AND ");
924 
925                 join = sm.toString();
926             }
927         }
928 
929         return join;
930     }
931 
932     protected void setJoin(
933         QueryPos qPos, LinkedHashMap<String, Object> params) {
934 
935         if (params != null) {
936             Iterator<Map.Entry<String, Object>> itr =
937                 params.entrySet().iterator();
938 
939             while (itr.hasNext()) {
940                 Map.Entry<String, Object> entry = itr.next();
941 
942                 Object value = entry.getValue();
943 
944                 if (value instanceof Long) {
945                     Long valueLong = (Long)value;
946 
947                     if (Validator.isNotNull(valueLong)) {
948                         qPos.add(valueLong);
949                     }
950                 }
951                 else if (value instanceof Long[]) {
952                     Long[] valueArray = (Long[])value;
953 
954                     for (int i = 0; i < valueArray.length; i++) {
955                         if (Validator.isNotNull(valueArray[i])) {
956                             qPos.add(valueArray[i]);
957                         }
958                     }
959                 }
960                 else if (value instanceof String) {
961                     String valueString = (String)value;
962 
963                     if (Validator.isNotNull(valueString)) {
964                         qPos.add(valueString);
965                     }
966                 }
967             }
968         }
969     }
970 
971     protected static String COUNTRY_ID_SQL =
972         "((Organization_.countryId = ?) OR (Address.countryId = ?)) " +
973             "[$AND_OR_CONNECTOR$]";
974 
975     protected static String REGION_ID_SQL =
976         "((Organization_.regionId = ?) OR (Address.regionId = ?)) " +
977             "[$AND_OR_CONNECTOR$]";
978 
979 }