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