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