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