1
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
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}