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