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