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