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