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