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