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