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