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