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