1   /**
2    * Copyright (c) 2000-2009 Liferay, Inc. All rights reserved.
3    *
4    *
5    *
6    *
7    * The contents of this file are subject to the terms of the Liferay Enterprise
8    * Subscription License ("License"). You may not use this file except in
9    * compliance with the License. You can obtain a copy of the License by
10   * contacting Liferay, Inc. See the License for the specific language governing
11   * permissions and limitations under the License, including but not limited to
12   * distribution rights of the Software.
13   *
14   * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
15   * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
16   * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
17   * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
18   * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
19   * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
20   * SOFTWARE.
21   */
22  
23  package com.liferay.portal.service.persistence;
24  
25  import com.liferay.portal.SystemException;
26  import com.liferay.portal.kernel.dao.orm.QueryPos;
27  import com.liferay.portal.kernel.dao.orm.QueryUtil;
28  import com.liferay.portal.kernel.dao.orm.SQLQuery;
29  import com.liferay.portal.kernel.dao.orm.Session;
30  import com.liferay.portal.kernel.dao.orm.Type;
31  import com.liferay.portal.kernel.util.OrderByComparator;
32  import com.liferay.portal.kernel.util.StringPool;
33  import com.liferay.portal.kernel.util.StringUtil;
34  import com.liferay.portal.kernel.util.Validator;
35  import com.liferay.portal.model.Organization;
36  import com.liferay.portal.model.OrganizationConstants;
37  import com.liferay.portal.service.persistence.impl.BasePersistenceImpl;
38  import com.liferay.portal.util.PropsValues;
39  import com.liferay.util.dao.orm.CustomSQLUtil;
40  
41  import java.util.ArrayList;
42  import java.util.Iterator;
43  import java.util.LinkedHashMap;
44  import java.util.List;
45  import java.util.Map;
46  
47  /**
48   * <a href="OrganizationFinderImpl.java.html"><b><i>View Source</i></b></a>
49   *
50   * @author Amos Fong
51   * @author Brian Wing Shun Chan
52   * @author Jorge Ferrer
53   */
54  public class OrganizationFinderImpl
55      extends BasePersistenceImpl implements OrganizationFinder {
56  
57      public static String COUNT_BY_ORGANIZATION_ID =
58          OrganizationFinder.class.getName() + ".countByOrganizationId";
59  
60      public static String COUNT_BY_C_PO_N_S_C_Z_R_C =
61          OrganizationFinder.class.getName() + ".countByC_PO_N_S_C_Z_R_C";
62  
63      public static String COUNT_BY_C_PO_N_L_S_C_Z_R_C =
64          OrganizationFinder.class.getName() + ".countByC_PO_N_L_S_C_Z_R_C";
65  
66      public static String FIND_BY_C_PO_N_S_C_Z_R_C =
67          OrganizationFinder.class.getName() + ".findByC_PO_N_S_C_Z_R_C";
68  
69      public static String FIND_BY_C_PO_N_L_S_C_Z_R_C =
70          OrganizationFinder.class.getName() + ".findByC_PO_N_L_S_C_Z_R_C";
71  
72      public static String JOIN_BY_GROUPS_PERMISSIONS =
73          OrganizationFinder.class.getName() + ".joinByGroupsPermissions";
74  
75      public static String JOIN_BY_ORGANIZATIONS_GROUPS =
76          OrganizationFinder.class.getName() + ".joinByOrganizationsGroups";
77  
78      public static String JOIN_BY_ORGANIZATIONS_USER_GROUPS =
79          OrganizationFinder.class.getName() + ".joinByOrganizationsUserGroups";
80  
81      public static String JOIN_BY_ORGANIZATIONS_PASSWORD_POLICIES =
82          OrganizationFinder.class.getName() +
83              ".joinByOrganizationsPasswordPolicies";
84  
85      public static String JOIN_BY_ORGANIZATIONS_ROLES =
86          OrganizationFinder.class.getName() + ".joinByOrganizationsRoles";
87  
88      public static String JOIN_BY_ORGANIZATIONS_USERS =
89          OrganizationFinder.class.getName() + ".joinByOrganizationsUsers";
90  
91      public static String JOIN_BY_ORG_GROUP_PERMISSION =
92          OrganizationFinder.class.getName() + ".joinByOrgGroupPermission";
93  
94      public static String JOIN_BY_USERS_ORGS =
95          OrganizationFinder.class.getName() + ".joinByUsersOrgs";
96  
97      public int countByKeywords(
98              long companyId, long parentOrganizationId,
99              String parentOrganizationIdComparator, String keywords,
100             int type, Long regionId, Long countryId,
101             LinkedHashMap<String, Object> params)
102         throws SystemException {
103 
104         String[] names = null;
105         String[] streets = null;
106         String[] cities = null;
107         String[] zips = null;
108         boolean andOperator = false;
109 
110         if (Validator.isNotNull(keywords)) {
111             names = CustomSQLUtil.keywords(keywords);
112             streets = CustomSQLUtil.keywords(keywords);
113             cities = CustomSQLUtil.keywords(keywords);
114             zips = CustomSQLUtil.keywords(keywords);
115         }
116         else {
117             andOperator = true;
118         }
119 
120         return countByC_PO_N_T_S_C_Z_R_C(
121             companyId, parentOrganizationId, parentOrganizationIdComparator,
122             names, type, streets, cities, zips, regionId, countryId, params,
123             andOperator);
124     }
125 
126     public int countByO_U(long organizationId, long userId)
127         throws SystemException {
128 
129         LinkedHashMap<String, Object> params1 =
130             new LinkedHashMap<String, Object>();
131 
132         params1.put("usersOrgs", userId);
133 
134         LinkedHashMap<String, Object> params2 =
135             new LinkedHashMap<String, Object>();
136 
137         params2.put("organizationsUserGroups", userId);
138 
139         Session session = null;
140 
141         try {
142             session = openSession();
143 
144             int count = countByOrganizationId(session, organizationId, params1);
145 
146             if (PropsValues.ORGANIZATIONS_USER_GROUP_MEMBERSHIP_ENABLED) {
147                 count += countByOrganizationId(
148                     session, organizationId, params2);
149             }
150 
151             return count;
152         }
153         catch (Exception e) {
154             throw new SystemException(e);
155         }
156         finally {
157             closeSession(session);
158         }
159     }
160 
161     public int countByC_PO_N_T_S_C_Z_R_C(
162             long companyId, long parentOrganizationId,
163             String parentOrganizationIdComparator, String name, int type,
164             String street, String city, String zip, Long regionId,
165             Long countryId, LinkedHashMap<String, Object> params,
166             boolean andOperator)
167         throws SystemException {
168 
169         return countByC_PO_N_T_S_C_Z_R_C(
170             companyId, parentOrganizationId, parentOrganizationIdComparator,
171             new String[] {name}, type, new String[] {street},
172             new String[] {city}, new String[] {zip}, regionId, countryId,
173             params, andOperator);
174     }
175 
176     public int countByC_PO_N_T_S_C_Z_R_C(
177             long companyId, long parentOrganizationId,
178             String parentOrganizationIdComparator, String[] names,
179             int type, String[] streets, String[] cities, String[] zips,
180             Long regionId, Long countryId, LinkedHashMap<String, Object> params,
181             boolean andOperator)
182         throws SystemException {
183 
184         names = CustomSQLUtil.keywords(names);
185         streets = CustomSQLUtil.keywords(streets);
186         cities = CustomSQLUtil.keywords(cities);
187         zips = CustomSQLUtil.keywords(zips);
188 
189         if (params != null) {
190             Long resourceId = (Long)params.get("permissionsResourceId");
191             Long groupId = (Long)params.get("permissionsGroupId");
192 
193             if (Validator.isNotNull(groupId) &&
194                     Validator.isNotNull(resourceId)) {
195 
196                 return countByPermissions(
197                     companyId, parentOrganizationId,
198                     parentOrganizationIdComparator, names, type, streets,
199                     cities, zips, regionId, countryId, resourceId.longValue(),
200                     groupId.longValue(), andOperator);
201             }
202         }
203 
204         Session session = null;
205 
206         try {
207             session = openSession();
208 
209             String sql = null;
210 
211             if (type == OrganizationConstants.TYPE_LOCATION ||
212                 type == OrganizationConstants.TYPE_REGULAR) {
213 
214                 sql = CustomSQLUtil.get(COUNT_BY_C_PO_N_L_S_C_Z_R_C);
215             }
216             else {
217                 sql = CustomSQLUtil.get(COUNT_BY_C_PO_N_S_C_Z_R_C);
218             }
219 
220             sql = CustomSQLUtil.replaceKeywords(
221                 sql, "lower(Organization_.name)", StringPool.LIKE, false,
222                 names);
223             sql = CustomSQLUtil.replaceKeywords(
224                 sql, "lower(Address.street1)", StringPool.LIKE, true,
225                 streets);
226             sql = CustomSQLUtil.replaceKeywords(
227                 sql, "lower(Address.street2)", StringPool.LIKE, true,
228                 streets);
229             sql = CustomSQLUtil.replaceKeywords(
230                 sql, "lower(Address.street3)", StringPool.LIKE, true,
231                 streets);
232             sql = CustomSQLUtil.replaceKeywords(
233                 sql, "lower(Address.city)", StringPool.LIKE, false,
234                 cities);
235             sql = CustomSQLUtil.replaceKeywords(
236                 sql, "lower(Address.zip)", StringPool.LIKE, true,
237                 zips);
238 
239             if (regionId == null) {
240                 sql = StringUtil.replace(sql, REGION_ID_SQL, StringPool.BLANK);
241             }
242 
243             if (countryId == null) {
244                 sql = StringUtil.replace(sql, COUNTRY_ID_SQL, StringPool.BLANK);
245             }
246 
247             sql = StringUtil.replace(sql, "[$JOIN$]", getJoin(params));
248             sql = StringUtil.replace(sql, "[$WHERE$]", getWhere(params));
249             sql = StringUtil.replace(
250                 sql, "[$PARENT_ORGANIZATION_ID_COMPARATOR$]",
251                 parentOrganizationIdComparator);
252             sql = CustomSQLUtil.replaceAndOperator(sql, andOperator);
253 
254             SQLQuery q = session.createSQLQuery(sql);
255 
256             q.addScalar(COUNT_COLUMN_NAME, Type.LONG);
257 
258             QueryPos qPos = QueryPos.getInstance(q);
259 
260             setJoin(qPos, params);
261             qPos.add(companyId);
262             qPos.add(parentOrganizationId);
263 
264             if (type == OrganizationConstants.TYPE_LOCATION) {
265                 qPos.add(true);
266             }
267             else if (type == OrganizationConstants.TYPE_REGULAR) {
268                 qPos.add(false);
269             }
270 
271             qPos.add(names, 2);
272             qPos.add(streets, 6);
273 
274             if (regionId != null) {
275                 qPos.add(regionId);
276                 qPos.add(regionId);
277             }
278 
279             if (countryId != null) {
280                 qPos.add(countryId);
281                 qPos.add(countryId);
282             }
283 
284             qPos.add(cities, 2);
285             qPos.add(zips, 2);
286 
287             Iterator<Long> itr = q.list().iterator();
288 
289             if (itr.hasNext()) {
290                 Long count = itr.next();
291 
292                 if (count != null) {
293                     return count.intValue();
294                 }
295             }
296 
297             return 0;
298         }
299         catch (Exception e) {
300             throw new SystemException(e);
301         }
302         finally {
303             closeSession(session);
304         }
305     }
306 
307     public List<Organization> findByKeywords(
308             long companyId, long parentOrganizationId,
309             String parentOrganizationIdComparator, String keywords,
310             int type, Long regionId, Long countryId,
311             LinkedHashMap<String, Object> params, int start, int end,
312             OrderByComparator obc)
313         throws SystemException {
314 
315         String[] names = null;
316         String[] streets = null;
317         String[] cities = null;
318         String[] zips = null;
319         boolean andOperator = false;
320 
321         if (Validator.isNotNull(keywords)) {
322             names = CustomSQLUtil.keywords(keywords);
323             streets = CustomSQLUtil.keywords(keywords);
324             cities = CustomSQLUtil.keywords(keywords);
325             zips = CustomSQLUtil.keywords(keywords);
326         }
327         else {
328             andOperator = true;
329         }
330 
331         return findByC_PO_N_T_S_C_Z_R_C(
332             companyId, parentOrganizationId, parentOrganizationIdComparator,
333             names, type, streets, cities, zips, regionId, countryId, params,
334             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 name, int type,
340             String street, String city, String zip, Long regionId,
341             Long countryId, LinkedHashMap<String, Object> params,
342             boolean andOperator, int start, int end, OrderByComparator obc)
343         throws SystemException {
344 
345         return findByC_PO_N_T_S_C_Z_R_C(
346             companyId, parentOrganizationId, parentOrganizationIdComparator,
347             new String[] {name}, type, new String[] {street},
348             new String[] {city}, new String[] {zip}, regionId, countryId,
349             params, andOperator, start, end, obc);
350     }
351 
352     public List<Organization> findByC_PO_N_T_S_C_Z_R_C(
353             long companyId, long parentOrganizationId,
354             String parentOrganizationIdComparator, String[] names,
355             int type, String[] streets, String[] cities, String[] zips,
356             Long regionId, Long countryId, LinkedHashMap<String, Object> params,
357             boolean andOperator, int start, int end, OrderByComparator obc)
358         throws SystemException {
359 
360         names = CustomSQLUtil.keywords(names);
361         streets = CustomSQLUtil.keywords(streets);
362         cities = CustomSQLUtil.keywords(cities);
363         zips = CustomSQLUtil.keywords(zips);
364 
365         if (params != null) {
366             Long resourceId = (Long)params.get("permissionsResourceId");
367             Long groupId = (Long)params.get("permissionsGroupId");
368 
369             if (Validator.isNotNull(groupId) &&
370                     Validator.isNotNull(resourceId)) {
371 
372                 return findByPermissions(
373                     companyId, parentOrganizationId,
374                     parentOrganizationIdComparator, names, type, streets,
375                     cities, zips, regionId, countryId, resourceId.longValue(),
376                     groupId.longValue(), andOperator, start, end, obc);
377             }
378         }
379         else {
380             params = new LinkedHashMap<String, Object>();
381         }
382 
383         Long userId = null;
384 
385         if (PropsValues.ORGANIZATIONS_USER_GROUP_MEMBERSHIP_ENABLED) {
386             userId = (Long)params.get("usersOrgs");
387         }
388 
389         LinkedHashMap<String, Object> params1 = params;
390 
391         LinkedHashMap<String, Object> params2 =
392             new LinkedHashMap<String, Object>();
393 
394         params2.putAll(params1);
395 
396         if (userId != null) {
397             params2.remove("usersOrgs");
398             params2.put("organizationsUserGroups", userId);
399         }
400 
401         StringBuilder sb = new StringBuilder();
402 
403         sb.append("(");
404 
405         if ((type == OrganizationConstants.TYPE_LOCATION) ||
406             (type == OrganizationConstants.TYPE_REGULAR)) {
407 
408             sb.append(CustomSQLUtil.get(FIND_BY_C_PO_N_L_S_C_Z_R_C));
409         }
410         else {
411             sb.append(CustomSQLUtil.get(FIND_BY_C_PO_N_S_C_Z_R_C));
412         }
413 
414         String sql = sb.toString();
415 
416         sql = StringUtil.replace(sql, "[$JOIN$]", getJoin(params1));
417         sql = StringUtil.replace(sql, "[$WHERE$]", getWhere(params1));
418 
419         sb = new StringBuilder();
420 
421         sb.append(sql);
422 
423         sb.append(")");
424 
425         if (Validator.isNotNull(userId)) {
426             sb.append(" UNION (");
427 
428             if (type == OrganizationConstants.TYPE_LOCATION ||
429                 type == OrganizationConstants.TYPE_REGULAR) {
430 
431                 sb.append(CustomSQLUtil.get(FIND_BY_C_PO_N_L_S_C_Z_R_C));
432             }
433             else {
434                 sb.append(CustomSQLUtil.get(FIND_BY_C_PO_N_S_C_Z_R_C));
435             }
436 
437             sql = sb.toString();
438 
439             sql = StringUtil.replace(sql, "[$JOIN$]", getJoin(params2));
440             sql = StringUtil.replace(sql, "[$WHERE$]", getWhere(params2));
441 
442             sb = new StringBuilder();
443 
444             sb.append(sql);
445 
446             sb.append(")");
447         }
448 
449         sql = sb.toString();
450 
451         sql = CustomSQLUtil.replaceKeywords(
452             sql, "lower(Organization_.name)", StringPool.LIKE, false,
453             names);
454         sql = CustomSQLUtil.replaceKeywords(
455             sql, "lower(Address.street1)", StringPool.LIKE, true,
456             streets);
457         sql = CustomSQLUtil.replaceKeywords(
458             sql, "lower(Address.street2)", StringPool.LIKE, true,
459             streets);
460         sql = CustomSQLUtil.replaceKeywords(
461             sql, "lower(Address.street3)", StringPool.LIKE, true,
462             streets);
463         sql = CustomSQLUtil.replaceKeywords(
464             sql, "lower(Address.city)", StringPool.LIKE, false,
465             cities);
466         sql = CustomSQLUtil.replaceKeywords(
467             sql, "lower(Address.zip)", StringPool.LIKE, true,
468             zips);
469         sql = StringUtil.replace(
470             sql, "[$PARENT_ORGANIZATION_ID_COMPARATOR$]",
471             parentOrganizationIdComparator);
472 
473         if (regionId == null) {
474             sql = StringUtil.replace(sql, REGION_ID_SQL, StringPool.BLANK);
475         }
476 
477         if (countryId == null) {
478             sql = StringUtil.replace(sql, COUNTRY_ID_SQL, StringPool.BLANK);
479         }
480 
481         sql = CustomSQLUtil.replaceAndOperator(sql, andOperator);
482         sql = CustomSQLUtil.replaceOrderBy(sql, obc);
483 
484         Session session = null;
485 
486         try {
487             session = openSession();
488 
489             SQLQuery q = session.createSQLQuery(sql);
490 
491             q.addScalar("orgId", Type.LONG);
492 
493             QueryPos qPos = QueryPos.getInstance(q);
494 
495             setJoin(qPos, params1);
496             qPos.add(companyId);
497             qPos.add(parentOrganizationId);
498 
499             if (type == OrganizationConstants.TYPE_LOCATION) {
500                 qPos.add(true);
501             }
502             else if (type == OrganizationConstants.TYPE_REGULAR) {
503                 qPos.add(false);
504             }
505 
506             qPos.add(names, 2);
507             qPos.add(streets, 6);
508 
509             if (regionId != null) {
510                 qPos.add(regionId);
511                 qPos.add(regionId);
512             }
513 
514             if (countryId != null) {
515                 qPos.add(countryId);
516                 qPos.add(countryId);
517             }
518 
519             qPos.add(cities, 2);
520             qPos.add(zips, 2);
521 
522             if (Validator.isNotNull(userId)) {
523                 setJoin(qPos, params2);
524                 qPos.add(companyId);
525                 qPos.add(parentOrganizationId);
526 
527                 if (type == OrganizationConstants.TYPE_LOCATION) {
528                     qPos.add(true);
529                 }
530                 else if (type == OrganizationConstants.TYPE_REGULAR) {
531                     qPos.add(false);
532                 }
533 
534                 qPos.add(names, 2);
535                 qPos.add(streets, 6);
536 
537                 if (regionId != null) {
538                     qPos.add(regionId);
539                     qPos.add(regionId);
540                 }
541 
542                 if (countryId != null) {
543                     qPos.add(countryId);
544                     qPos.add(countryId);
545                 }
546 
547                 qPos.add(cities, 2);
548                 qPos.add(zips, 2);
549             }
550 
551             List<Organization> organizations = new ArrayList<Organization>();
552 
553             Iterator<Long> itr = (Iterator<Long>)QueryUtil.iterate(
554                 q, getDialect(), start, end);
555 
556             while (itr.hasNext()) {
557                 Long organizationId = itr.next();
558 
559                 Organization organization = OrganizationUtil.findByPrimaryKey(
560                     organizationId.longValue());
561 
562                 organizations.add(organization);
563             }
564 
565             return organizations;
566         }
567         catch (Exception e) {
568             throw new SystemException(e);
569         }
570         finally {
571             closeSession(session);
572         }
573     }
574 
575     protected int countByOrganizationId(
576         Session session, long organizationId,
577         LinkedHashMap<String, Object> params) {
578 
579         String sql = CustomSQLUtil.get(COUNT_BY_ORGANIZATION_ID);
580 
581         sql = StringUtil.replace(sql, "[$JOIN$]", getJoin(params));
582         sql = StringUtil.replace(sql, "[$WHERE$]", getWhere(params));
583 
584         SQLQuery q = session.createSQLQuery(sql);
585 
586         q.addScalar(COUNT_COLUMN_NAME, Type.LONG);
587 
588         QueryPos qPos = QueryPos.getInstance(q);
589 
590         setJoin(qPos, params);
591         qPos.add(organizationId);
592 
593         Iterator<Long> itr = q.list().iterator();
594 
595         if (itr.hasNext()) {
596             Long count = itr.next();
597 
598             if (count != null) {
599                 return count.intValue();
600             }
601         }
602 
603         return 0;
604     }
605 
606     protected int countByPermissions(
607             long companyId, long parentOrganizationId,
608             String parentOrganizationIdComparator, String[] names,
609             int type, String[] streets, String[] cities, String[] zips,
610             Long regionId, Long countryId, long resourceId, long groupId,
611             boolean andOperator)
612         throws SystemException {
613 
614         Session session = null;
615 
616         try {
617             session = openSession();
618 
619             StringBuilder sb = new StringBuilder();
620 
621             sb.append("(");
622 
623             if (type == OrganizationConstants.TYPE_LOCATION ||
624                 type == OrganizationConstants.TYPE_REGULAR) {
625 
626                 sb.append(CustomSQLUtil.get(COUNT_BY_C_PO_N_L_S_C_Z_R_C));
627             }
628             else {
629                 sb.append(CustomSQLUtil.get(COUNT_BY_C_PO_N_S_C_Z_R_C));
630             }
631 
632             String sql = sb.toString();
633 
634             if (regionId == null) {
635                 sql = StringUtil.replace(sql, REGION_ID_SQL, StringPool.BLANK);
636             }
637 
638             if (countryId == null) {
639                 sql = StringUtil.replace(sql, COUNTRY_ID_SQL, StringPool.BLANK);
640             }
641 
642             sql = StringUtil.replace(
643                 sql, "[$JOIN$]", getJoin("groupsPermissions"));
644             sql = StringUtil.replace(
645                 sql, "[$WHERE$]", getWhere("groupsPermissions"));
646 
647             sb = new StringBuilder();
648 
649             sb.append(sql);
650 
651             sb.append(") UNION (");
652 
653             if (type == OrganizationConstants.TYPE_LOCATION ||
654                 type == OrganizationConstants.TYPE_REGULAR) {
655 
656                 sb.append(CustomSQLUtil.get(COUNT_BY_C_PO_N_L_S_C_Z_R_C));
657             }
658             else {
659                 sb.append(CustomSQLUtil.get(COUNT_BY_C_PO_N_S_C_Z_R_C));
660             }
661 
662             sql = sb.toString();
663 
664             if (regionId == null) {
665                 sql = StringUtil.replace(sql, REGION_ID_SQL, StringPool.BLANK);
666             }
667 
668             if (countryId == null) {
669                 sql = StringUtil.replace(sql, COUNTRY_ID_SQL, StringPool.BLANK);
670             }
671 
672             sql = StringUtil.replace(
673                 sql, "[$JOIN$]", getJoin("orgGroupPermission"));
674             sql = StringUtil.replace(
675                 sql, "[$WHERE$]", getWhere("orgGroupPermission"));
676             sql = StringUtil.replace(
677                 sql, "[$PARENT_ORGANIZATION_ID_COMPARATOR$]",
678                 parentOrganizationIdComparator);
679             sql = CustomSQLUtil.replaceAndOperator(sql, andOperator);
680 
681             sb = new StringBuilder();
682 
683             sb.append(sql);
684 
685             sb.append(")");
686 
687             sql = sb.toString();
688 
689             sql = CustomSQLUtil.replaceKeywords(
690                 sql, "lower(Organization_.name)", StringPool.LIKE, false,
691                 names);
692             sql = CustomSQLUtil.replaceKeywords(
693                 sql, "lower(Address.street1)", StringPool.LIKE, true,
694                 streets);
695             sql = CustomSQLUtil.replaceKeywords(
696                 sql, "lower(Address.street2)", StringPool.LIKE, true,
697                 streets);
698             sql = CustomSQLUtil.replaceKeywords(
699                 sql, "lower(Address.street3)", StringPool.LIKE, true,
700                 streets);
701             sql = CustomSQLUtil.replaceKeywords(
702                 sql, "lower(Address.city)", StringPool.LIKE, false,
703                 cities);
704             sql = CustomSQLUtil.replaceKeywords(
705                 sql, "lower(Address.zip)", StringPool.LIKE, true,
706                 zips);
707 
708             if (regionId == null) {
709                 sql = StringUtil.replace(sql, REGION_ID_SQL, StringPool.BLANK);
710             }
711 
712             if (countryId == null) {
713                 sql = StringUtil.replace(sql, COUNTRY_ID_SQL, StringPool.BLANK);
714             }
715 
716             SQLQuery q = session.createSQLQuery(sql);
717 
718             q.addScalar(COUNT_COLUMN_NAME, Type.LONG);
719 
720             QueryPos qPos = QueryPos.getInstance(q);
721 
722             for (int i = 0; i < 2; i++) {
723                 qPos.add(resourceId);
724 
725                 if (i == 1) {
726                     qPos.add(groupId);
727                 }
728 
729                 qPos.add(companyId);
730                 qPos.add(parentOrganizationId);
731 
732                 if (type == OrganizationConstants.TYPE_LOCATION) {
733                     qPos.add(true);
734                 }
735                 else if (type == OrganizationConstants.TYPE_REGULAR) {
736                     qPos.add(false);
737                 }
738 
739                 qPos.add(names, 2);
740                 qPos.add(streets, 6);
741 
742                 if (regionId != null) {
743                     qPos.add(regionId);
744                     qPos.add(regionId);
745                 }
746 
747                 if (countryId != null) {
748                     qPos.add(countryId);
749                     qPos.add(countryId);
750                 }
751 
752                 qPos.add(cities, 2);
753                 qPos.add(zips, 2);
754             }
755 
756             int count = 0;
757 
758             Iterator<Long> itr = q.list().iterator();
759 
760             while (itr.hasNext()) {
761                 Long l = itr.next();
762 
763                 if (l != null) {
764                     count += l.intValue();
765                 }
766             }
767 
768             return count;
769         }
770         catch (Exception e) {
771             throw new SystemException(e);
772         }
773         finally {
774             closeSession(session);
775         }
776     }
777 
778     protected List<Organization> findByPermissions(
779             long companyId, long parentOrganizationId,
780             String parentOrganizationIdComparator, String[] names,
781             int type, String[] streets, String[] cities, String[] zips,
782             Long regionId, Long countryId, long resourceId, long groupId,
783             boolean andOperator, int start, int end, OrderByComparator obc)
784         throws SystemException {
785 
786         Session session = null;
787 
788         try {
789             session = openSession();
790 
791             StringBuilder sb = new StringBuilder();
792 
793             sb.append("(");
794 
795             if (type == OrganizationConstants.TYPE_LOCATION ||
796                 type == OrganizationConstants.TYPE_REGULAR) {
797 
798                 sb.append(CustomSQLUtil.get(FIND_BY_C_PO_N_L_S_C_Z_R_C));
799             }
800             else {
801                 sb.append(CustomSQLUtil.get(FIND_BY_C_PO_N_S_C_Z_R_C));
802             }
803 
804             String sql = sb.toString();
805 
806             if (regionId == null) {
807                 sql = StringUtil.replace(sql, REGION_ID_SQL, StringPool.BLANK);
808             }
809 
810             if (countryId == null) {
811                 sql = StringUtil.replace(sql, COUNTRY_ID_SQL, StringPool.BLANK);
812             }
813 
814             sql = StringUtil.replace(
815                 sql, "[$JOIN$]", getJoin("groupsPermissions"));
816             sql = StringUtil.replace(
817                 sql, "[$WHERE$]", getWhere("groupsPermissions"));
818 
819             sb = new StringBuilder();
820 
821             sb.append(sql);
822 
823             sb.append(") UNION (");
824 
825             if (type == OrganizationConstants.TYPE_LOCATION ||
826                 type == OrganizationConstants.TYPE_REGULAR) {
827 
828                 sb.append(CustomSQLUtil.get(FIND_BY_C_PO_N_L_S_C_Z_R_C));
829             }
830             else {
831                 sb.append(CustomSQLUtil.get(FIND_BY_C_PO_N_S_C_Z_R_C));
832             }
833 
834             sql = sb.toString();
835 
836             if (regionId == null) {
837                 sql = StringUtil.replace(sql, REGION_ID_SQL, StringPool.BLANK);
838             }
839 
840             if (countryId == null) {
841                 sql = StringUtil.replace(sql, COUNTRY_ID_SQL, StringPool.BLANK);
842             }
843 
844             sql = StringUtil.replace(
845                 sql, "[$JOIN$]", getJoin("orgGroupPermission"));
846             sql = StringUtil.replace(
847                 sql, "[$WHERE$]", getWhere("orgGroupPermission"));
848             sql = StringUtil.replace(
849                 sql, "[$PARENT_ORGANIZATION_ID_COMPARATOR$]",
850                 parentOrganizationIdComparator);
851             sql = CustomSQLUtil.replaceAndOperator(sql, andOperator);
852 
853             sb = new StringBuilder();
854 
855             sb.append(sql);
856 
857             sb.append(") ");
858 
859             sql = sb.toString();
860 
861             sql = CustomSQLUtil.replaceKeywords(
862                 sql, "lower(Organization_.name)", StringPool.LIKE, false,
863                 names);
864             sql = CustomSQLUtil.replaceKeywords(
865                 sql, "lower(Address.street1)", StringPool.LIKE, true,
866                 streets);
867             sql = CustomSQLUtil.replaceKeywords(
868                 sql, "lower(Address.street2)", StringPool.LIKE, true,
869                 streets);
870             sql = CustomSQLUtil.replaceKeywords(
871                 sql, "lower(Address.street3)", StringPool.LIKE, true,
872                 streets);
873             sql = CustomSQLUtil.replaceKeywords(
874                 sql, "lower(Address.city)", StringPool.LIKE, false,
875                 cities);
876             sql = CustomSQLUtil.replaceKeywords(
877                 sql, "lower(Address.zip)", StringPool.LIKE, true,
878                 zips);
879 
880             if (regionId == null) {
881                 sql = StringUtil.replace(sql, REGION_ID_SQL, StringPool.BLANK);
882             }
883 
884             if (countryId == null) {
885                 sql = StringUtil.replace(sql, COUNTRY_ID_SQL, StringPool.BLANK);
886             }
887 
888             sql = CustomSQLUtil.replaceOrderBy(sql, obc);
889 
890             SQLQuery q = session.createSQLQuery(sql);
891 
892             q.addScalar("orgId", Type.LONG);
893 
894             QueryPos qPos = QueryPos.getInstance(q);
895 
896             for (int i = 0; i < 2; i++) {
897                 qPos.add(resourceId);
898 
899                 if (i == 1) {
900                     qPos.add(groupId);
901                 }
902 
903                 qPos.add(companyId);
904                 qPos.add(parentOrganizationId);
905 
906                 if (type == OrganizationConstants.TYPE_LOCATION) {
907                     qPos.add(true);
908                 }
909                 else if (type == OrganizationConstants.TYPE_REGULAR) {
910                     qPos.add(false);
911                 }
912 
913                 qPos.add(names, 2);
914                 qPos.add(streets, 6);
915 
916                 if (regionId != null) {
917                     qPos.add(regionId);
918                     qPos.add(regionId);
919                 }
920 
921                 if (countryId != null) {
922                     qPos.add(countryId);
923                     qPos.add(countryId);
924                 }
925 
926                 qPos.add(cities, 2);
927                 qPos.add(zips, 2);
928             }
929 
930             List<Organization> organizations = new ArrayList<Organization>();
931 
932             Iterator<Long> itr = (Iterator<Long>)QueryUtil.iterate(
933                 q, getDialect(), start, end);
934 
935             while (itr.hasNext()) {
936                 Long organizationId = itr.next();
937 
938                 Organization organization = OrganizationUtil.findByPrimaryKey(
939                     organizationId.longValue());
940 
941                 organizations.add(organization);
942             }
943 
944             return organizations;
945         }
946         catch (Exception e) {
947             throw new SystemException(e);
948         }
949         finally {
950             closeSession(session);
951         }
952     }
953 
954     protected String getJoin(LinkedHashMap<String, Object> params) {
955         if (params == null) {
956             return StringPool.BLANK;
957         }
958 
959         StringBuilder sb = new StringBuilder();
960 
961         Iterator<Map.Entry<String, Object>> itr = params.entrySet().iterator();
962 
963         while (itr.hasNext()) {
964             Map.Entry<String, Object> entry = itr.next();
965 
966             String key = entry.getKey();
967             Object value = entry.getValue();
968 
969             if (Validator.isNotNull(value)) {
970                 sb.append(getJoin(key));
971             }
972         }
973 
974         return sb.toString();
975     }
976 
977     protected String getJoin(String key) {
978         String join = StringPool.BLANK;
979 
980         if (key.equals("groupsPermissions")) {
981             join = CustomSQLUtil.get(JOIN_BY_GROUPS_PERMISSIONS);
982         }
983         else if (key.equals("organizationsGroups")) {
984             join = CustomSQLUtil.get(JOIN_BY_ORGANIZATIONS_GROUPS);
985         }
986         else if (key.equals("organizationsPasswordPolicies")) {
987             join = CustomSQLUtil.get(JOIN_BY_ORGANIZATIONS_PASSWORD_POLICIES);
988         }
989         else if (key.equals("organizationsRoles")) {
990             join = CustomSQLUtil.get(JOIN_BY_ORGANIZATIONS_ROLES);
991         }
992         else if (key.equals("organizationsUserGroups")) {
993             join = CustomSQLUtil.get(JOIN_BY_ORGANIZATIONS_USER_GROUPS);
994         }
995         else if (key.equals("organizationsUsers")) {
996             join = CustomSQLUtil.get(JOIN_BY_ORGANIZATIONS_USERS);
997         }
998         else if (key.equals("orgGroupPermission")) {
999             join = CustomSQLUtil.get(JOIN_BY_ORG_GROUP_PERMISSION);
1000        }
1001        else if (key.equals("usersOrgs")) {
1002            join = CustomSQLUtil.get(JOIN_BY_USERS_ORGS);
1003        }
1004
1005        if (Validator.isNotNull(join)) {
1006            int pos = join.indexOf("WHERE");
1007
1008            if (pos != -1) {
1009                join = join.substring(0, pos);
1010            }
1011        }
1012
1013        return join;
1014    }
1015
1016    protected String getWhere(LinkedHashMap<String, Object> params) {
1017        if (params == null) {
1018            return StringPool.BLANK;
1019        }
1020
1021        StringBuilder sb = new StringBuilder();
1022
1023        Iterator<Map.Entry<String, Object>> itr = params.entrySet().iterator();
1024
1025        while (itr.hasNext()) {
1026            Map.Entry<String, Object> entry = itr.next();
1027
1028            String key = entry.getKey();
1029            Object value = entry.getValue();
1030
1031            if (Validator.isNotNull(value)) {
1032                sb.append(getWhere(key, value));
1033            }
1034        }
1035
1036        return sb.toString();
1037    }
1038
1039    protected String getWhere(String key) {
1040        return getWhere(key, null);
1041    }
1042
1043    protected String getWhere(String key, Object value) {
1044        String join = StringPool.BLANK;
1045
1046        if (key.equals("groupsPermissions")) {
1047            join = CustomSQLUtil.get(JOIN_BY_GROUPS_PERMISSIONS);
1048        }
1049        else if (key.equals("organizations")) {
1050            Long[] organizationIds = (Long[])value;
1051
1052            if (organizationIds.length > 0) {
1053                StringBuilder sb = new StringBuilder();
1054
1055                sb.append("WHERE (");
1056
1057                for (int i = 0; i < organizationIds.length; i++) {
1058                    sb.append("(Organization_.organizationId = ?) ");
1059
1060                    if ((i + 1) < organizationIds.length) {
1061                        sb.append("OR ");
1062                    }
1063                }
1064
1065                if (organizationIds.length == 0) {
1066                    sb.append("(Organization_.organizationId = -1) ");
1067                }
1068
1069                sb.append(")");
1070
1071                join = sb.toString();
1072            }
1073        }
1074        else if (key.equals("organizationsGroups")) {
1075            join = CustomSQLUtil.get(JOIN_BY_ORGANIZATIONS_GROUPS);
1076        }
1077        else if (key.equals("organizationsPasswordPolicies")) {
1078            join = CustomSQLUtil.get(JOIN_BY_ORGANIZATIONS_PASSWORD_POLICIES);
1079        }
1080        else if (key.equals("organizationsRoles")) {
1081            join = CustomSQLUtil.get(JOIN_BY_ORGANIZATIONS_ROLES);
1082        }
1083        else if (key.equals("organizationsTree")) {
1084            Long[][] leftAndRightOrganizationIds = (Long[][])value;
1085
1086            if (leftAndRightOrganizationIds.length > 0) {
1087                StringBuilder sb = new StringBuilder();
1088
1089                sb.append("WHERE (");
1090
1091                for (int i = 0; i < leftAndRightOrganizationIds.length; i++) {
1092                    sb.append(
1093                        "(Organization_.leftOrganizationId BETWEEN ? AND ?) ");
1094
1095                    if ((i + 1) < leftAndRightOrganizationIds.length) {
1096                        sb.append("OR ");
1097                    }
1098                }
1099
1100                sb.append(")");
1101
1102                join = sb.toString();
1103            }
1104        }
1105        else if (key.equals("organizationsUserGroups")) {
1106            join = CustomSQLUtil.get(JOIN_BY_ORGANIZATIONS_USER_GROUPS);
1107        }
1108        else if (key.equals("organizationsUsers")) {
1109            join = CustomSQLUtil.get(JOIN_BY_ORGANIZATIONS_USERS);
1110        }
1111        else if (key.equals("orgGroupPermission")) {
1112            join = CustomSQLUtil.get(JOIN_BY_ORG_GROUP_PERMISSION);
1113        }
1114        else if (key.equals("usersOrgs")) {
1115            join = CustomSQLUtil.get(JOIN_BY_USERS_ORGS);
1116        }
1117
1118        if (Validator.isNotNull(join)) {
1119            int pos = join.indexOf("WHERE");
1120
1121            if (pos != -1) {
1122                StringBuilder sb = new StringBuilder();
1123
1124                sb.append(join.substring(pos + 5, join.length()));
1125                sb.append(" AND ");
1126
1127                join = sb.toString();
1128            }
1129            else {
1130                join = StringPool.BLANK;
1131            }
1132        }
1133
1134        return join;
1135    }
1136
1137    protected void setJoin(
1138        QueryPos qPos, LinkedHashMap<String, Object> params) {
1139
1140        if (params != null) {
1141            Iterator<Map.Entry<String, Object>> itr =
1142                params.entrySet().iterator();
1143
1144            while (itr.hasNext()) {
1145                Map.Entry<String, Object> entry = itr.next();
1146
1147                Object value = entry.getValue();
1148
1149                if (value instanceof Long) {
1150                    Long valueLong = (Long)value;
1151
1152                    if (Validator.isNotNull(valueLong)) {
1153                        qPos.add(valueLong);
1154                    }
1155                }
1156                else if (value instanceof Long[]) {
1157                    Long[] valueArray = (Long[])value;
1158
1159                    for (int i = 0; i < valueArray.length; i++) {
1160                        if (Validator.isNotNull(valueArray[i])) {
1161                            qPos.add(valueArray[i]);
1162                        }
1163                    }
1164                }
1165                else if (value instanceof Long[][]) {
1166                    Long[][] valueDoubleArray = (Long[][])value;
1167
1168                    for (Long[] valueArray : valueDoubleArray) {
1169                        for (Long valueLong : valueArray) {
1170                            qPos.add(valueLong);
1171                        }
1172                    }
1173                }
1174                else if (value instanceof String) {
1175                    String valueString = (String)value;
1176
1177                    if (Validator.isNotNull(valueString)) {
1178                        qPos.add(valueString);
1179                    }
1180                }
1181            }
1182        }
1183    }
1184
1185    protected static String COUNTRY_ID_SQL =
1186        "((Organization_.countryId = ?) OR (Address.countryId = ?)) " +
1187            "[$AND_OR_CONNECTOR$]";
1188
1189    protected static String REGION_ID_SQL =
1190        "((Organization_.regionId = ?) OR (Address.regionId = ?)) " +
1191            "[$AND_OR_CONNECTOR$]";
1192
1193}