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