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