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