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