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