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