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