1   /**
2    * Copyright (c) 2000-2010 Liferay, Inc. All rights reserved.
3    *
4    * This library is free software; you can redistribute it and/or modify it under
5    * the terms of the GNU Lesser General Public License as published by the Free
6    * Software Foundation; either version 2.1 of the License, or (at your option)
7    * any later version.
8    *
9    * This library is distributed in the hope that it will be useful, but WITHOUT
10   * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
11   * FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more
12   * details.
13   */
14  
15  package com.liferay.portal.service.persistence;
16  
17  import com.liferay.portal.kernel.dao.orm.QueryPos;
18  import com.liferay.portal.kernel.dao.orm.QueryUtil;
19  import com.liferay.portal.kernel.dao.orm.SQLQuery;
20  import com.liferay.portal.kernel.dao.orm.Session;
21  import com.liferay.portal.kernel.dao.orm.Type;
22  import com.liferay.portal.kernel.exception.SystemException;
23  import com.liferay.portal.kernel.util.OrderByComparator;
24  import com.liferay.portal.kernel.util.StringBundler;
25  import com.liferay.portal.kernel.util.StringPool;
26  import com.liferay.portal.kernel.util.StringUtil;
27  import com.liferay.portal.kernel.util.Validator;
28  import com.liferay.portal.model.Organization;
29  import com.liferay.portal.service.persistence.impl.BasePersistenceImpl;
30  import com.liferay.portal.util.PropsValues;
31  import com.liferay.util.dao.orm.CustomSQLUtil;
32  
33  import java.util.ArrayList;
34  import java.util.Iterator;
35  import java.util.LinkedHashMap;
36  import java.util.List;
37  import java.util.Map;
38  
39  /**
40   * <a href="OrganizationFinderImpl.java.html"><b><i>View Source</i></b></a>
41   *
42   * @author Amos Fong
43   * @author Brian Wing Shun Chan
44   * @author Jorge Ferrer
45   */
46  public class OrganizationFinderImpl
47      extends BasePersistenceImpl<Organization> implements OrganizationFinder {
48  
49      public static String COUNT_BY_ORGANIZATION_ID =
50          OrganizationFinder.class.getName() + ".countByOrganizationId";
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_USER_GROUPS =
71          OrganizationFinder.class.getName() + ".joinByOrganizationsUserGroups";
72  
73      public static String JOIN_BY_ORGANIZATIONS_PASSWORD_POLICIES =
74          OrganizationFinder.class.getName() +
75              ".joinByOrganizationsPasswordPolicies";
76  
77      public static String JOIN_BY_ORGANIZATIONS_ROLES =
78          OrganizationFinder.class.getName() + ".joinByOrganizationsRoles";
79  
80      public static String JOIN_BY_ORGANIZATIONS_USERS =
81          OrganizationFinder.class.getName() + ".joinByOrganizationsUsers";
82  
83      public static String JOIN_BY_ORG_GROUP_PERMISSION =
84          OrganizationFinder.class.getName() + ".joinByOrgGroupPermission";
85  
86      public static String JOIN_BY_USERS_ORGS =
87          OrganizationFinder.class.getName() + ".joinByUsersOrgs";
88  
89      public int countByKeywords(
90              long companyId, long parentOrganizationId,
91              String parentOrganizationIdComparator, String keywords,
92              String type, Long regionId, Long countryId,
93              LinkedHashMap<String, Object> params)
94          throws SystemException {
95  
96          String[] names = null;
97          String[] streets = null;
98          String[] cities = null;
99          String[] zips = null;
100         boolean andOperator = false;
101 
102         if (Validator.isNotNull(keywords)) {
103             names = CustomSQLUtil.keywords(keywords);
104             streets = CustomSQLUtil.keywords(keywords);
105             cities = CustomSQLUtil.keywords(keywords);
106             zips = CustomSQLUtil.keywords(keywords);
107         }
108         else {
109             andOperator = true;
110         }
111 
112         return countByC_PO_N_T_S_C_Z_R_C(
113             companyId, parentOrganizationId, parentOrganizationIdComparator,
114             names, type, streets, cities, zips, regionId, countryId, params,
115             andOperator);
116     }
117 
118     public int countByO_U(long organizationId, long userId)
119         throws SystemException {
120 
121         LinkedHashMap<String, Object> params1 =
122             new LinkedHashMap<String, Object>();
123 
124         params1.put("usersOrgs", userId);
125 
126         LinkedHashMap<String, Object> params2 =
127             new LinkedHashMap<String, Object>();
128 
129         params2.put("organizationsUserGroups", userId);
130 
131         Session session = null;
132 
133         try {
134             session = openSession();
135 
136             int count = countByOrganizationId(session, organizationId, params1);
137 
138             if (PropsValues.ORGANIZATIONS_USER_GROUP_MEMBERSHIP_ENABLED) {
139                 count += countByOrganizationId(
140                     session, organizationId, params2);
141             }
142 
143             return count;
144         }
145         catch (Exception e) {
146             throw new SystemException(e);
147         }
148         finally {
149             closeSession(session);
150         }
151     }
152 
153     public int countByC_PO_N_T_S_C_Z_R_C(
154             long companyId, long parentOrganizationId,
155             String parentOrganizationIdComparator, String name, String type,
156             String street, String city, String zip, Long regionId,
157             Long countryId, LinkedHashMap<String, Object> params,
158             boolean andOperator)
159         throws SystemException {
160 
161         return countByC_PO_N_T_S_C_Z_R_C(
162             companyId, parentOrganizationId, parentOrganizationIdComparator,
163             new String[] {name}, type, new String[] {street},
164             new String[] {city}, new String[] {zip}, regionId, countryId,
165             params, andOperator);
166     }
167 
168     public int countByC_PO_N_T_S_C_Z_R_C(
169             long companyId, long parentOrganizationId,
170             String parentOrganizationIdComparator, String[] names,
171             String type, String[] streets, String[] cities, String[] zips,
172             Long regionId, Long countryId, LinkedHashMap<String, Object> params,
173             boolean andOperator)
174         throws SystemException {
175 
176         names = CustomSQLUtil.keywords(names);
177         streets = CustomSQLUtil.keywords(streets);
178         cities = CustomSQLUtil.keywords(cities);
179         zips = CustomSQLUtil.keywords(zips);
180 
181         if (params != null) {
182             Long resourceId = (Long)params.get("permissionsResourceId");
183             Long groupId = (Long)params.get("permissionsGroupId");
184 
185             if (Validator.isNotNull(groupId) &&
186                     Validator.isNotNull(resourceId)) {
187 
188                 return countByPermissions(
189                     companyId, parentOrganizationId,
190                     parentOrganizationIdComparator, names, type, streets,
191                     cities, zips, regionId, countryId, resourceId.longValue(),
192                     groupId.longValue(), andOperator);
193             }
194         }
195 
196         Session session = null;
197 
198         try {
199             session = openSession();
200 
201             String sql = null;
202 
203             if (Validator.isNotNull(type)) {
204                 sql = CustomSQLUtil.get(COUNT_BY_C_PO_N_L_S_C_Z_R_C);
205             }
206             else {
207                 sql = CustomSQLUtil.get(COUNT_BY_C_PO_N_S_C_Z_R_C);
208             }
209 
210             sql = CustomSQLUtil.replaceKeywords(
211                 sql, "lower(Organization_.name)", StringPool.LIKE, false,
212                 names);
213             sql = CustomSQLUtil.replaceKeywords(
214                 sql, "lower(Address.street1)", StringPool.LIKE, true,
215                 streets);
216             sql = CustomSQLUtil.replaceKeywords(
217                 sql, "lower(Address.street2)", StringPool.LIKE, true,
218                 streets);
219             sql = CustomSQLUtil.replaceKeywords(
220                 sql, "lower(Address.street3)", StringPool.LIKE, true,
221                 streets);
222             sql = CustomSQLUtil.replaceKeywords(
223                 sql, "lower(Address.city)", StringPool.LIKE, false,
224                 cities);
225             sql = CustomSQLUtil.replaceKeywords(
226                 sql, "lower(Address.zip)", StringPool.LIKE, true,
227                 zips);
228 
229             if (regionId == null) {
230                 sql = StringUtil.replace(sql, REGION_ID_SQL, StringPool.BLANK);
231             }
232 
233             if (countryId == null) {
234                 sql = StringUtil.replace(sql, COUNTRY_ID_SQL, StringPool.BLANK);
235             }
236 
237             sql = StringUtil.replace(sql, "[$JOIN$]", getJoin(params));
238             sql = StringUtil.replace(sql, "[$WHERE$]", getWhere(params));
239             sql = StringUtil.replace(
240                 sql, "[$PARENT_ORGANIZATION_ID_COMPARATOR$]",
241                 parentOrganizationIdComparator);
242             sql = CustomSQLUtil.replaceAndOperator(sql, andOperator);
243 
244             SQLQuery q = session.createSQLQuery(sql);
245 
246             q.addScalar(COUNT_COLUMN_NAME, Type.LONG);
247 
248             QueryPos qPos = QueryPos.getInstance(q);
249 
250             setJoin(qPos, params);
251             qPos.add(companyId);
252             qPos.add(parentOrganizationId);
253 
254             if (Validator.isNotNull(type)) {
255                 qPos.add(type);
256             }
257 
258             qPos.add(names, 2);
259             qPos.add(streets, 6);
260 
261             if (regionId != null) {
262                 qPos.add(regionId);
263                 qPos.add(regionId);
264             }
265 
266             if (countryId != null) {
267                 qPos.add(countryId);
268                 qPos.add(countryId);
269             }
270 
271             qPos.add(cities, 2);
272             qPos.add(zips, 2);
273 
274             Iterator<Long> itr = q.list().iterator();
275 
276             if (itr.hasNext()) {
277                 Long count = itr.next();
278 
279                 if (count != null) {
280                     return count.intValue();
281                 }
282             }
283 
284             return 0;
285         }
286         catch (Exception e) {
287             throw new SystemException(e);
288         }
289         finally {
290             closeSession(session);
291         }
292     }
293 
294     public List<Organization> findByKeywords(
295             long companyId, long parentOrganizationId,
296             String parentOrganizationIdComparator, String keywords,
297             String type, Long regionId, Long countryId,
298             LinkedHashMap<String, Object> params, int start, int end,
299             OrderByComparator obc)
300         throws SystemException {
301 
302         String[] names = null;
303         String[] streets = null;
304         String[] cities = null;
305         String[] zips = null;
306         boolean andOperator = false;
307 
308         if (Validator.isNotNull(keywords)) {
309             names = CustomSQLUtil.keywords(keywords);
310             streets = CustomSQLUtil.keywords(keywords);
311             cities = CustomSQLUtil.keywords(keywords);
312             zips = CustomSQLUtil.keywords(keywords);
313         }
314         else {
315             andOperator = true;
316         }
317 
318         return findByC_PO_N_T_S_C_Z_R_C(
319             companyId, parentOrganizationId, parentOrganizationIdComparator,
320             names, type, streets, cities, zips, regionId, countryId, params,
321             andOperator, start, end, obc);
322     }
323 
324     public List<Organization> findByC_PO_N_T_S_C_Z_R_C(
325             long companyId, long parentOrganizationId,
326             String parentOrganizationIdComparator, String name, String type,
327             String street, String city, String zip, Long regionId,
328             Long countryId, LinkedHashMap<String, Object> params,
329             boolean andOperator, int start, int end, OrderByComparator obc)
330         throws SystemException {
331 
332         return findByC_PO_N_T_S_C_Z_R_C(
333             companyId, parentOrganizationId, parentOrganizationIdComparator,
334             new String[] {name}, type, new String[] {street},
335             new String[] {city}, new String[] {zip}, regionId, countryId,
336             params, andOperator, start, end, obc);
337     }
338 
339     public List<Organization> findByC_PO_N_T_S_C_Z_R_C(
340             long companyId, long parentOrganizationId,
341             String parentOrganizationIdComparator, String[] names,
342             String type, String[] streets, String[] cities, String[] zips,
343             Long regionId, Long countryId, LinkedHashMap<String, Object> params,
344             boolean andOperator, int start, int end, OrderByComparator obc)
345         throws SystemException {
346 
347         names = CustomSQLUtil.keywords(names);
348         streets = CustomSQLUtil.keywords(streets);
349         cities = CustomSQLUtil.keywords(cities);
350         zips = CustomSQLUtil.keywords(zips);
351 
352         if (params != null) {
353             Long resourceId = (Long)params.get("permissionsResourceId");
354             Long groupId = (Long)params.get("permissionsGroupId");
355 
356             if (Validator.isNotNull(groupId) &&
357                     Validator.isNotNull(resourceId)) {
358 
359                 return findByPermissions(
360                     companyId, parentOrganizationId,
361                     parentOrganizationIdComparator, names, type, streets,
362                     cities, zips, regionId, countryId, resourceId.longValue(),
363                     groupId.longValue(), andOperator, start, end, obc);
364             }
365         }
366         else {
367             params = new LinkedHashMap<String, Object>();
368         }
369 
370         Long userId = null;
371 
372         if (PropsValues.ORGANIZATIONS_USER_GROUP_MEMBERSHIP_ENABLED) {
373             userId = (Long)params.get("usersOrgs");
374         }
375 
376         LinkedHashMap<String, Object> params1 = params;
377 
378         LinkedHashMap<String, Object> params2 =
379             new LinkedHashMap<String, Object>();
380 
381         params2.putAll(params1);
382 
383         if (userId != null) {
384             params2.remove("usersOrgs");
385             params2.put("organizationsUserGroups", userId);
386         }
387 
388         StringBundler sb = new StringBundler();
389 
390         sb.append("(");
391 
392         if (Validator.isNotNull(type)) {
393             sb.append(CustomSQLUtil.get(FIND_BY_C_PO_N_L_S_C_Z_R_C));
394         }
395         else {
396             sb.append(CustomSQLUtil.get(FIND_BY_C_PO_N_S_C_Z_R_C));
397         }
398 
399         String sql = sb.toString();
400 
401         sql = StringUtil.replace(sql, "[$JOIN$]", getJoin(params1));
402         sql = StringUtil.replace(sql, "[$WHERE$]", getWhere(params1));
403 
404         sb.setIndex(0);
405 
406         sb.append(sql);
407 
408         sb.append(")");
409 
410         if (Validator.isNotNull(userId)) {
411             sb.append(" UNION (");
412 
413             if (Validator.isNotNull(type)) {
414                 sb.append(CustomSQLUtil.get(FIND_BY_C_PO_N_L_S_C_Z_R_C));
415             }
416             else {
417                 sb.append(CustomSQLUtil.get(FIND_BY_C_PO_N_S_C_Z_R_C));
418             }
419 
420             sql = sb.toString();
421 
422             sql = StringUtil.replace(sql, "[$JOIN$]", getJoin(params2));
423             sql = StringUtil.replace(sql, "[$WHERE$]", getWhere(params2));
424 
425             sb.setIndex(0);
426 
427             sb.append(sql);
428 
429             sb.append(")");
430         }
431 
432         sql = sb.toString();
433 
434         sql = CustomSQLUtil.replaceKeywords(
435             sql, "lower(Organization_.name)", StringPool.LIKE, false,
436             names);
437         sql = CustomSQLUtil.replaceKeywords(
438             sql, "lower(Address.street1)", StringPool.LIKE, true,
439             streets);
440         sql = CustomSQLUtil.replaceKeywords(
441             sql, "lower(Address.street2)", StringPool.LIKE, true,
442             streets);
443         sql = CustomSQLUtil.replaceKeywords(
444             sql, "lower(Address.street3)", StringPool.LIKE, true,
445             streets);
446         sql = CustomSQLUtil.replaceKeywords(
447             sql, "lower(Address.city)", StringPool.LIKE, false,
448             cities);
449         sql = CustomSQLUtil.replaceKeywords(
450             sql, "lower(Address.zip)", StringPool.LIKE, true,
451             zips);
452         sql = StringUtil.replace(
453             sql, "[$PARENT_ORGANIZATION_ID_COMPARATOR$]",
454             parentOrganizationIdComparator);
455 
456         if (regionId == null) {
457             sql = StringUtil.replace(sql, REGION_ID_SQL, StringPool.BLANK);
458         }
459 
460         if (countryId == null) {
461             sql = StringUtil.replace(sql, COUNTRY_ID_SQL, StringPool.BLANK);
462         }
463 
464         sql = CustomSQLUtil.replaceAndOperator(sql, andOperator);
465         sql = CustomSQLUtil.replaceOrderBy(sql, obc);
466 
467         Session session = null;
468 
469         try {
470             session = openSession();
471 
472             SQLQuery q = session.createSQLQuery(sql);
473 
474             q.addScalar("orgId", Type.LONG);
475 
476             QueryPos qPos = QueryPos.getInstance(q);
477 
478             setJoin(qPos, params1);
479             qPos.add(companyId);
480             qPos.add(parentOrganizationId);
481 
482             if (Validator.isNotNull(type)) {
483                 qPos.add(type);
484             }
485 
486             qPos.add(names, 2);
487             qPos.add(streets, 6);
488 
489             if (regionId != null) {
490                 qPos.add(regionId);
491                 qPos.add(regionId);
492             }
493 
494             if (countryId != null) {
495                 qPos.add(countryId);
496                 qPos.add(countryId);
497             }
498 
499             qPos.add(cities, 2);
500             qPos.add(zips, 2);
501 
502             if (Validator.isNotNull(userId)) {
503                 setJoin(qPos, params2);
504                 qPos.add(companyId);
505                 qPos.add(parentOrganizationId);
506 
507                 if (Validator.isNotNull(type)) {
508                     qPos.add(type);
509                 }
510 
511                 qPos.add(names, 2);
512                 qPos.add(streets, 6);
513 
514                 if (regionId != null) {
515                     qPos.add(regionId);
516                     qPos.add(regionId);
517                 }
518 
519                 if (countryId != null) {
520                     qPos.add(countryId);
521                     qPos.add(countryId);
522                 }
523 
524                 qPos.add(cities, 2);
525                 qPos.add(zips, 2);
526             }
527 
528             List<Organization> organizations = new ArrayList<Organization>();
529 
530             Iterator<Long> itr = (Iterator<Long>)QueryUtil.iterate(
531                 q, getDialect(), start, end);
532 
533             while (itr.hasNext()) {
534                 Long organizationId = itr.next();
535 
536                 Organization organization = OrganizationUtil.findByPrimaryKey(
537                     organizationId.longValue());
538 
539                 organizations.add(organization);
540             }
541 
542             return organizations;
543         }
544         catch (Exception e) {
545             throw new SystemException(e);
546         }
547         finally {
548             closeSession(session);
549         }
550     }
551 
552     protected int countByOrganizationId(
553         Session session, long organizationId,
554         LinkedHashMap<String, Object> params) {
555 
556         String sql = CustomSQLUtil.get(COUNT_BY_ORGANIZATION_ID);
557 
558         sql = StringUtil.replace(sql, "[$JOIN$]", getJoin(params));
559         sql = StringUtil.replace(sql, "[$WHERE$]", getWhere(params));
560 
561         SQLQuery q = session.createSQLQuery(sql);
562 
563         q.addScalar(COUNT_COLUMN_NAME, Type.LONG);
564 
565         QueryPos qPos = QueryPos.getInstance(q);
566 
567         setJoin(qPos, params);
568         qPos.add(organizationId);
569 
570         Iterator<Long> itr = q.list().iterator();
571 
572         if (itr.hasNext()) {
573             Long count = itr.next();
574 
575             if (count != null) {
576                 return count.intValue();
577             }
578         }
579 
580         return 0;
581     }
582 
583     protected int countByPermissions(
584             long companyId, long parentOrganizationId,
585             String parentOrganizationIdComparator, String[] names,
586             String type, String[] streets, String[] cities, String[] zips,
587             Long regionId, Long countryId, long resourceId, long groupId,
588             boolean andOperator)
589         throws SystemException {
590 
591         Session session = null;
592 
593         try {
594             session = openSession();
595 
596             StringBundler sb = new StringBundler();
597 
598             sb.append("(");
599 
600             if (Validator.isNotNull(type)) {
601                 sb.append(CustomSQLUtil.get(COUNT_BY_C_PO_N_L_S_C_Z_R_C));
602             }
603             else {
604                 sb.append(CustomSQLUtil.get(COUNT_BY_C_PO_N_S_C_Z_R_C));
605             }
606 
607             String sql = sb.toString();
608 
609             if (regionId == null) {
610                 sql = StringUtil.replace(sql, REGION_ID_SQL, StringPool.BLANK);
611             }
612 
613             if (countryId == null) {
614                 sql = StringUtil.replace(sql, COUNTRY_ID_SQL, StringPool.BLANK);
615             }
616 
617             sql = StringUtil.replace(
618                 sql, "[$JOIN$]", getJoin("groupsPermissions"));
619             sql = StringUtil.replace(
620                 sql, "[$WHERE$]", getWhere("groupsPermissions"));
621 
622             sb.setIndex(0);
623 
624             sb.append(sql);
625 
626             sb.append(") UNION (");
627 
628             if (Validator.isNotNull(type)) {
629                 sb.append(CustomSQLUtil.get(COUNT_BY_C_PO_N_L_S_C_Z_R_C));
630             }
631             else {
632                 sb.append(CustomSQLUtil.get(COUNT_BY_C_PO_N_S_C_Z_R_C));
633             }
634 
635             sql = sb.toString();
636 
637             if (regionId == null) {
638                 sql = StringUtil.replace(sql, REGION_ID_SQL, StringPool.BLANK);
639             }
640 
641             if (countryId == null) {
642                 sql = StringUtil.replace(sql, COUNTRY_ID_SQL, StringPool.BLANK);
643             }
644 
645             sql = StringUtil.replace(
646                 sql, "[$JOIN$]", getJoin("orgGroupPermission"));
647             sql = StringUtil.replace(
648                 sql, "[$WHERE$]", getWhere("orgGroupPermission"));
649             sql = StringUtil.replace(
650                 sql, "[$PARENT_ORGANIZATION_ID_COMPARATOR$]",
651                 parentOrganizationIdComparator);
652             sql = CustomSQLUtil.replaceAndOperator(sql, andOperator);
653 
654             sb.setIndex(0);
655 
656             sb.append(sql);
657 
658             sb.append(")");
659 
660             sql = sb.toString();
661 
662             sql = CustomSQLUtil.replaceKeywords(
663                 sql, "lower(Organization_.name)", StringPool.LIKE, false,
664                 names);
665             sql = CustomSQLUtil.replaceKeywords(
666                 sql, "lower(Address.street1)", StringPool.LIKE, true,
667                 streets);
668             sql = CustomSQLUtil.replaceKeywords(
669                 sql, "lower(Address.street2)", StringPool.LIKE, true,
670                 streets);
671             sql = CustomSQLUtil.replaceKeywords(
672                 sql, "lower(Address.street3)", StringPool.LIKE, true,
673                 streets);
674             sql = CustomSQLUtil.replaceKeywords(
675                 sql, "lower(Address.city)", StringPool.LIKE, false,
676                 cities);
677             sql = CustomSQLUtil.replaceKeywords(
678                 sql, "lower(Address.zip)", StringPool.LIKE, true,
679                 zips);
680 
681             if (regionId == null) {
682                 sql = StringUtil.replace(sql, REGION_ID_SQL, StringPool.BLANK);
683             }
684 
685             if (countryId == null) {
686                 sql = StringUtil.replace(sql, COUNTRY_ID_SQL, StringPool.BLANK);
687             }
688 
689             SQLQuery q = session.createSQLQuery(sql);
690 
691             q.addScalar(COUNT_COLUMN_NAME, Type.LONG);
692 
693             QueryPos qPos = QueryPos.getInstance(q);
694 
695             for (int i = 0; i < 2; i++) {
696                 qPos.add(resourceId);
697 
698                 if (i == 1) {
699                     qPos.add(groupId);
700                 }
701 
702                 qPos.add(companyId);
703                 qPos.add(parentOrganizationId);
704 
705                 if (Validator.isNotNull(type)) {
706                     qPos.add(type);
707                 }
708 
709                 qPos.add(names, 2);
710                 qPos.add(streets, 6);
711 
712                 if (regionId != null) {
713                     qPos.add(regionId);
714                     qPos.add(regionId);
715                 }
716 
717                 if (countryId != null) {
718                     qPos.add(countryId);
719                     qPos.add(countryId);
720                 }
721 
722                 qPos.add(cities, 2);
723                 qPos.add(zips, 2);
724             }
725 
726             int count = 0;
727 
728             Iterator<Long> itr = q.list().iterator();
729 
730             while (itr.hasNext()) {
731                 Long l = itr.next();
732 
733                 if (l != null) {
734                     count += l.intValue();
735                 }
736             }
737 
738             return count;
739         }
740         catch (Exception e) {
741             throw new SystemException(e);
742         }
743         finally {
744             closeSession(session);
745         }
746     }
747 
748     protected List<Organization> findByPermissions(
749             long companyId, long parentOrganizationId,
750             String parentOrganizationIdComparator, String[] names,
751             String type, String[] streets, String[] cities, String[] zips,
752             Long regionId, Long countryId, long resourceId, long groupId,
753             boolean andOperator, int start, int end, OrderByComparator obc)
754         throws SystemException {
755 
756         Session session = null;
757 
758         try {
759             session = openSession();
760 
761             StringBundler sb = new StringBundler();
762 
763             sb.append("(");
764 
765             if (Validator.isNotNull(type)) {
766                 sb.append(CustomSQLUtil.get(FIND_BY_C_PO_N_L_S_C_Z_R_C));
767             }
768             else {
769                 sb.append(CustomSQLUtil.get(FIND_BY_C_PO_N_S_C_Z_R_C));
770             }
771 
772             String sql = sb.toString();
773 
774             if (regionId == null) {
775                 sql = StringUtil.replace(sql, REGION_ID_SQL, StringPool.BLANK);
776             }
777 
778             if (countryId == null) {
779                 sql = StringUtil.replace(sql, COUNTRY_ID_SQL, StringPool.BLANK);
780             }
781 
782             sql = StringUtil.replace(
783                 sql, "[$JOIN$]", getJoin("groupsPermissions"));
784             sql = StringUtil.replace(
785                 sql, "[$WHERE$]", getWhere("groupsPermissions"));
786 
787             sb.setIndex(0);
788 
789             sb.append(sql);
790 
791             sb.append(") UNION (");
792 
793             if (Validator.isNotNull(type)) {
794                 sb.append(CustomSQLUtil.get(FIND_BY_C_PO_N_L_S_C_Z_R_C));
795             }
796             else {
797                 sb.append(CustomSQLUtil.get(FIND_BY_C_PO_N_S_C_Z_R_C));
798             }
799 
800             sql = sb.toString();
801 
802             if (regionId == null) {
803                 sql = StringUtil.replace(sql, REGION_ID_SQL, StringPool.BLANK);
804             }
805 
806             if (countryId == null) {
807                 sql = StringUtil.replace(sql, COUNTRY_ID_SQL, StringPool.BLANK);
808             }
809 
810             sql = StringUtil.replace(
811                 sql, "[$JOIN$]", getJoin("orgGroupPermission"));
812             sql = StringUtil.replace(
813                 sql, "[$WHERE$]", getWhere("orgGroupPermission"));
814             sql = StringUtil.replace(
815                 sql, "[$PARENT_ORGANIZATION_ID_COMPARATOR$]",
816                 parentOrganizationIdComparator);
817             sql = CustomSQLUtil.replaceAndOperator(sql, andOperator);
818 
819             sb.setIndex(0);
820 
821             sb.append(sql);
822 
823             sb.append(") ");
824 
825             sql = sb.toString();
826 
827             sql = CustomSQLUtil.replaceKeywords(
828                 sql, "lower(Organization_.name)", StringPool.LIKE, false,
829                 names);
830             sql = CustomSQLUtil.replaceKeywords(
831                 sql, "lower(Address.street1)", StringPool.LIKE, true,
832                 streets);
833             sql = CustomSQLUtil.replaceKeywords(
834                 sql, "lower(Address.street2)", StringPool.LIKE, true,
835                 streets);
836             sql = CustomSQLUtil.replaceKeywords(
837                 sql, "lower(Address.street3)", StringPool.LIKE, true,
838                 streets);
839             sql = CustomSQLUtil.replaceKeywords(
840                 sql, "lower(Address.city)", StringPool.LIKE, false,
841                 cities);
842             sql = CustomSQLUtil.replaceKeywords(
843                 sql, "lower(Address.zip)", StringPool.LIKE, true,
844                 zips);
845 
846             if (regionId == null) {
847                 sql = StringUtil.replace(sql, REGION_ID_SQL, StringPool.BLANK);
848             }
849 
850             if (countryId == null) {
851                 sql = StringUtil.replace(sql, COUNTRY_ID_SQL, StringPool.BLANK);
852             }
853 
854             sql = CustomSQLUtil.replaceOrderBy(sql, obc);
855 
856             SQLQuery q = session.createSQLQuery(sql);
857 
858             q.addScalar("orgId", Type.LONG);
859 
860             QueryPos qPos = QueryPos.getInstance(q);
861 
862             for (int i = 0; i < 2; i++) {
863                 qPos.add(resourceId);
864 
865                 if (i == 1) {
866                     qPos.add(groupId);
867                 }
868 
869                 qPos.add(companyId);
870                 qPos.add(parentOrganizationId);
871 
872                 if (Validator.isNotNull(type)) {
873                     qPos.add(type);
874                 }
875 
876                 qPos.add(names, 2);
877                 qPos.add(streets, 6);
878 
879                 if (regionId != null) {
880                     qPos.add(regionId);
881                     qPos.add(regionId);
882                 }
883 
884                 if (countryId != null) {
885                     qPos.add(countryId);
886                     qPos.add(countryId);
887                 }
888 
889                 qPos.add(cities, 2);
890                 qPos.add(zips, 2);
891             }
892 
893             List<Organization> organizations = new ArrayList<Organization>();
894 
895             Iterator<Long> itr = (Iterator<Long>)QueryUtil.iterate(
896                 q, getDialect(), start, end);
897 
898             while (itr.hasNext()) {
899                 Long organizationId = itr.next();
900 
901                 Organization organization = OrganizationUtil.findByPrimaryKey(
902                     organizationId.longValue());
903 
904                 organizations.add(organization);
905             }
906 
907             return organizations;
908         }
909         catch (Exception e) {
910             throw new SystemException(e);
911         }
912         finally {
913             closeSession(session);
914         }
915     }
916 
917     protected String getJoin(LinkedHashMap<String, Object> params) {
918         if ((params == null) || params.isEmpty()) {
919             return StringPool.BLANK;
920         }
921 
922         StringBundler sb = new StringBundler(params.size());
923 
924         Iterator<Map.Entry<String, Object>> itr = params.entrySet().iterator();
925 
926         while (itr.hasNext()) {
927             Map.Entry<String, Object> entry = itr.next();
928 
929             String key = entry.getKey();
930             Object value = entry.getValue();
931 
932             if (Validator.isNotNull(value)) {
933                 sb.append(getJoin(key));
934             }
935         }
936 
937         return sb.toString();
938     }
939 
940     protected String getJoin(String key) {
941         String join = StringPool.BLANK;
942 
943         if (key.equals("groupsPermissions")) {
944             join = CustomSQLUtil.get(JOIN_BY_GROUPS_PERMISSIONS);
945         }
946         else if (key.equals("organizationsGroups")) {
947             join = CustomSQLUtil.get(JOIN_BY_ORGANIZATIONS_GROUPS);
948         }
949         else if (key.equals("organizationsPasswordPolicies")) {
950             join = CustomSQLUtil.get(JOIN_BY_ORGANIZATIONS_PASSWORD_POLICIES);
951         }
952         else if (key.equals("organizationsRoles")) {
953             join = CustomSQLUtil.get(JOIN_BY_ORGANIZATIONS_ROLES);
954         }
955         else if (key.equals("organizationsUserGroups")) {
956             join = CustomSQLUtil.get(JOIN_BY_ORGANIZATIONS_USER_GROUPS);
957         }
958         else if (key.equals("organizationsUsers")) {
959             join = CustomSQLUtil.get(JOIN_BY_ORGANIZATIONS_USERS);
960         }
961         else if (key.equals("orgGroupPermission")) {
962             join = CustomSQLUtil.get(JOIN_BY_ORG_GROUP_PERMISSION);
963         }
964         else if (key.equals("usersOrgs")) {
965             join = CustomSQLUtil.get(JOIN_BY_USERS_ORGS);
966         }
967 
968         if (Validator.isNotNull(join)) {
969             int pos = join.indexOf("WHERE");
970 
971             if (pos != -1) {
972                 join = join.substring(0, pos);
973             }
974         }
975 
976         return join;
977     }
978 
979     protected String getWhere(LinkedHashMap<String, Object> params) {
980         if ((params == null) || params.isEmpty()) {
981             return StringPool.BLANK;
982         }
983 
984         StringBundler sb = new StringBundler(params.size());
985 
986         Iterator<Map.Entry<String, Object>> itr = params.entrySet().iterator();
987 
988         while (itr.hasNext()) {
989             Map.Entry<String, Object> entry = itr.next();
990 
991             String key = entry.getKey();
992             Object value = entry.getValue();
993 
994             if (Validator.isNotNull(value)) {
995                 sb.append(getWhere(key, value));
996             }
997         }
998 
999         return sb.toString();
1000    }
1001
1002    protected String getWhere(String key) {
1003        return getWhere(key, null);
1004    }
1005
1006    protected String getWhere(String key, Object value) {
1007        String join = StringPool.BLANK;
1008
1009        if (key.equals("groupsPermissions")) {
1010            join = CustomSQLUtil.get(JOIN_BY_GROUPS_PERMISSIONS);
1011        }
1012        else if (key.equals("organizations")) {
1013            Long[] organizationIds = (Long[])value;
1014
1015            if (organizationIds.length == 0) {
1016                join = "WHERE ((Organization_.organizationId = -1) )";
1017            }
1018            else {
1019                StringBundler sb = new StringBundler(
1020                    organizationIds.length * 2 + 1);
1021
1022                sb.append("WHERE (");
1023
1024                for (int i = 0; i < organizationIds.length; i++) {
1025                    sb.append("(Organization_.organizationId = ?) ");
1026
1027                    if ((i + 1) < organizationIds.length) {
1028                        sb.append("OR ");
1029                    }
1030                }
1031
1032                sb.append(")");
1033
1034                join = sb.toString();
1035            }
1036        }
1037        else if (key.equals("organizationsGroups")) {
1038            join = CustomSQLUtil.get(JOIN_BY_ORGANIZATIONS_GROUPS);
1039        }
1040        else if (key.equals("organizationsPasswordPolicies")) {
1041            join = CustomSQLUtil.get(JOIN_BY_ORGANIZATIONS_PASSWORD_POLICIES);
1042        }
1043        else if (key.equals("organizationsRoles")) {
1044            join = CustomSQLUtil.get(JOIN_BY_ORGANIZATIONS_ROLES);
1045        }
1046        else if (key.equals("organizationsTree")) {
1047            Long[][] leftAndRightOrganizationIds = (Long[][])value;
1048
1049            if (leftAndRightOrganizationIds.length > 0) {
1050                StringBundler sb = new StringBundler(
1051                    leftAndRightOrganizationIds.length * 2 + 1);
1052
1053                sb.append("WHERE (");
1054
1055                for (int i = 0; i < leftAndRightOrganizationIds.length; i++) {
1056                    sb.append(
1057                        "(Organization_.leftOrganizationId BETWEEN ? AND ?) ");
1058
1059                    if ((i + 1) < leftAndRightOrganizationIds.length) {
1060                        sb.append("OR ");
1061                    }
1062                }
1063
1064                sb.append(")");
1065
1066                join = sb.toString();
1067            }
1068        }
1069        else if (key.equals("organizationsUserGroups")) {
1070            join = CustomSQLUtil.get(JOIN_BY_ORGANIZATIONS_USER_GROUPS);
1071        }
1072        else if (key.equals("organizationsUsers")) {
1073            join = CustomSQLUtil.get(JOIN_BY_ORGANIZATIONS_USERS);
1074        }
1075        else if (key.equals("orgGroupPermission")) {
1076            join = CustomSQLUtil.get(JOIN_BY_ORG_GROUP_PERMISSION);
1077        }
1078        else if (key.equals("usersOrgs")) {
1079            join = CustomSQLUtil.get(JOIN_BY_USERS_ORGS);
1080        }
1081
1082        if (Validator.isNotNull(join)) {
1083            int pos = join.indexOf("WHERE");
1084
1085            if (pos != -1) {
1086                join = join.substring(pos + 5, join.length()).concat(" AND ");
1087            }
1088            else {
1089                join = StringPool.BLANK;
1090            }
1091        }
1092
1093        return join;
1094    }
1095
1096    protected void setJoin(
1097        QueryPos qPos, LinkedHashMap<String, Object> params) {
1098
1099        if (params != null) {
1100            Iterator<Map.Entry<String, Object>> itr =
1101                params.entrySet().iterator();
1102
1103            while (itr.hasNext()) {
1104                Map.Entry<String, Object> entry = itr.next();
1105
1106                Object value = entry.getValue();
1107
1108                if (value instanceof Long) {
1109                    Long valueLong = (Long)value;
1110
1111                    if (Validator.isNotNull(valueLong)) {
1112                        qPos.add(valueLong);
1113                    }
1114                }
1115                else if (value instanceof Long[]) {
1116                    Long[] valueArray = (Long[])value;
1117
1118                    for (int i = 0; i < valueArray.length; i++) {
1119                        if (Validator.isNotNull(valueArray[i])) {
1120                            qPos.add(valueArray[i]);
1121                        }
1122                    }
1123                }
1124                else if (value instanceof Long[][]) {
1125                    Long[][] valueDoubleArray = (Long[][])value;
1126
1127                    for (Long[] valueArray : valueDoubleArray) {
1128                        for (Long valueLong : valueArray) {
1129                            qPos.add(valueLong);
1130                        }
1131                    }
1132                }
1133                else if (value instanceof String) {
1134                    String valueString = (String)value;
1135
1136                    if (Validator.isNotNull(valueString)) {
1137                        qPos.add(valueString);
1138                    }
1139                }
1140            }
1141        }
1142    }
1143
1144    protected static String COUNTRY_ID_SQL =
1145        "((Organization_.countryId = ?) OR (Address.countryId = ?)) " +
1146            "[$AND_OR_CONNECTOR$]";
1147
1148    protected static String REGION_ID_SQL =
1149        "((Organization_.regionId = ?) OR (Address.regionId = ?)) " +
1150            "[$AND_OR_CONNECTOR$]";
1151
1152}