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