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