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