001    /**
002     * Copyright (c) 2000-2012 Liferay, Inc. All rights reserved.
003     *
004     * This library is free software; you can redistribute it and/or modify it under
005     * the terms of the GNU Lesser General Public License as published by the Free
006     * Software Foundation; either version 2.1 of the License, or (at your option)
007     * any later version.
008     *
009     * This library is distributed in the hope that it will be useful, but WITHOUT
010     * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
011     * FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more
012     * details.
013     */
014    
015    package com.liferay.portlet.asset.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.CalendarUtil;
024    import com.liferay.portal.kernel.util.StringBundler;
025    import com.liferay.portal.kernel.util.StringPool;
026    import com.liferay.portal.kernel.util.Validator;
027    import com.liferay.portal.model.Layout;
028    import com.liferay.portal.service.persistence.impl.BasePersistenceImpl;
029    import com.liferay.portal.util.PropsValues;
030    import com.liferay.portlet.asset.model.AssetEntry;
031    import com.liferay.portlet.asset.model.impl.AssetEntryImpl;
032    import com.liferay.util.dao.orm.CustomSQLUtil;
033    
034    import java.sql.Timestamp;
035    
036    import java.util.Date;
037    import java.util.Iterator;
038    import java.util.List;
039    
040    /**
041     * @author Brian Wing Shun Chan
042     * @author Jorge Ferrer
043     */
044    public class AssetEntryFinderImpl
045            extends BasePersistenceImpl<AssetEntry> implements AssetEntryFinder {
046    
047            public static final String FIND_BY_AND_CATEGORY_IDS =
048                    AssetEntryFinder.class.getName() + ".findByAndCategoryIds";
049    
050            public static final String FIND_BY_AND_CATEGORY_IDS_TREE =
051                    AssetEntryFinder.class.getName() + ".findByAndCategoryIdsTree";
052    
053            public static final String FIND_BY_AND_TAG_IDS =
054                    AssetEntryFinder.class.getName() + ".findByAndTagIds";
055    
056            public int countEntries(AssetEntryQuery entryQuery) throws SystemException {
057                    Session session = null;
058    
059                    try {
060                            session = openSession();
061    
062                            SQLQuery q = buildAssetQuerySQL(entryQuery, true, session);
063    
064                            Iterator<Long> itr = q.iterate();
065    
066                            if (itr.hasNext()) {
067                                    Long count = itr.next();
068    
069                                    if (count != null) {
070                                            return count.intValue();
071                                    }
072                            }
073    
074                            return 0;
075                    }
076                    catch (Exception e) {
077                            throw new SystemException(e);
078                    }
079                    finally {
080                            closeSession(session);
081                    }
082            }
083    
084            public List<AssetEntry> findEntries(AssetEntryQuery entryQuery)
085                    throws SystemException {
086    
087                    Session session = null;
088    
089                    try {
090                            session = openSession();
091    
092                            SQLQuery q = buildAssetQuerySQL(entryQuery, false, session);
093    
094                            return (List<AssetEntry>)QueryUtil.list(
095                                    q, getDialect(), entryQuery.getStart(), entryQuery.getEnd());
096                    }
097                    catch (Exception e) {
098                            throw new SystemException(e);
099                    }
100                    finally {
101                            closeSession(session);
102                    }
103            }
104    
105            protected void buildAllCategoriesSQL(
106                    String sqlId, long[] categoryIds, StringBundler sb) {
107    
108                    sb.append(" AND AssetEntry.entryId IN (");
109    
110                    for (int i = 0; i < categoryIds.length; i++) {
111                            sb.append(CustomSQLUtil.get(sqlId));
112    
113                            if ((i + 1) < categoryIds.length) {
114                                    sb.append(" AND AssetEntry.entryId IN (");
115                            }
116                    }
117    
118                    for (int i = 0; i < categoryIds.length; i++) {
119                            if ((i + 1) < categoryIds.length) {
120                                    sb.append(StringPool.CLOSE_PARENTHESIS);
121                            }
122                    }
123    
124                    sb.append(StringPool.CLOSE_PARENTHESIS);
125            }
126    
127            protected void buildAllTagsSQL(long[] tagIds, StringBundler sb) {
128                    sb.append(" AND AssetEntry.entryId IN (");
129    
130                    for (int i = 0; i < tagIds.length; i++) {
131                            sb.append(CustomSQLUtil.get(FIND_BY_AND_TAG_IDS));
132    
133                            if ((i + 1) < tagIds.length) {
134                                    sb.append(" AND AssetEntry.entryId IN (");
135                            }
136                    }
137    
138                    for (int i = 0; i < tagIds.length; i++) {
139                            if ((i + 1) < tagIds.length) {
140                                    sb.append(StringPool.CLOSE_PARENTHESIS);
141                            }
142                    }
143    
144                    sb.append(StringPool.CLOSE_PARENTHESIS);
145            }
146    
147            protected SQLQuery buildAssetQuerySQL(
148                    AssetEntryQuery entryQuery, boolean count, Session session) {
149    
150                    StringBundler sb = new StringBundler();
151    
152                    if (count) {
153                            sb.append(
154                                    "SELECT COUNT(DISTINCT AssetEntry.entryId) AS COUNT_VALUE ");
155                    }
156                    else {
157                            sb.append("SELECT DISTINCT {AssetEntry.*} ");
158    
159                            String orderByCol1 = entryQuery.getOrderByCol1();
160                            String orderByCol2 = entryQuery.getOrderByCol2();
161    
162                            if (orderByCol1.equals("ratings") ||
163                                    orderByCol2.equals("ratings")) {
164    
165                                    sb.append(", RatingsEntry.score ");
166                            }
167                    }
168    
169                    sb.append("FROM AssetEntry ");
170    
171                    if (entryQuery.getAnyTagIds().length > 0) {
172                            sb.append("INNER JOIN ");
173                            sb.append("AssetEntries_AssetTags ON ");
174                            sb.append("(AssetEntries_AssetTags.entryId = ");
175                            sb.append("AssetEntry.entryId) ");
176                            sb.append("INNER JOIN ");
177                            sb.append("AssetTag ON ");
178                            sb.append("(AssetTag.tagId = AssetEntries_AssetTags.tagId) ");
179                    }
180    
181                    if (entryQuery.getAnyCategoryIds().length > 0) {
182                            sb.append("INNER JOIN ");
183                            sb.append("AssetEntries_AssetCategories ON ");
184                            sb.append("(AssetEntries_AssetCategories.entryId = ");
185                            sb.append("AssetEntry.entryId) ");
186                            sb.append("INNER JOIN ");
187                            sb.append("AssetCategory ON ");
188                            sb.append("(AssetCategory.categoryId = ");
189                            sb.append("AssetEntries_AssetCategories.categoryId) ");
190                    }
191    
192                    if (entryQuery.getLinkedAssetEntryId() > 0) {
193                            sb.append("INNER JOIN ");
194                            sb.append("AssetLink ON ");
195                            sb.append("(AssetEntry.entryId = AssetLink.entryId1) ");
196                            sb.append("OR (AssetEntry.entryId = AssetLink.entryId2)");
197                    }
198    
199                    if (entryQuery.getOrderByCol1().equals("ratings") ||
200                            entryQuery.getOrderByCol2().equals("ratings")) {
201    
202                            sb.append(" LEFT JOIN ");
203                            sb.append("RatingsEntry ON ");
204                            sb.append("(RatingsEntry.classNameId = ");
205                            sb.append("AssetEntry.classNameId) AND ");
206                            sb.append("(RatingsEntry.classPK = AssetEntry.classPK)");
207                    }
208    
209                    sb.append("WHERE ");
210    
211                    int whereIndex = sb.index();
212    
213                    if (entryQuery.getLinkedAssetEntryId() > 0) {
214                            sb.append(" AND ((AssetLink.entryId1 = ?) OR ");
215                            sb.append("(AssetLink.entryId2 = ?))");
216                            sb.append(" AND (AssetEntry.entryId != ?)");
217                    }
218    
219                    if (entryQuery.isVisible() != null) {
220                            sb.append(" AND (visible = ?)");
221                    }
222    
223                    if (entryQuery.isExcludeZeroViewCount()) {
224                            sb.append(" AND (AssetEntry.viewCount > 0)");
225                    }
226    
227                    // Layout
228    
229                    Layout layout = entryQuery.getLayout();
230    
231                    if (layout != null) {
232                            sb.append(" AND (AssetEntry.layoutUuid = ?)");
233                    }
234    
235                    // Category conditions
236    
237                    if (entryQuery.getAllCategoryIds().length > 0) {
238                            if (PropsValues.ASSET_CATEGORIES_SEARCH_HIERARCHICAL) {
239                                    buildAllCategoriesSQL(
240                                            FIND_BY_AND_CATEGORY_IDS_TREE,
241                                            entryQuery.getAllCategoryIds(), sb);
242                            }
243                            else {
244                                    buildAllCategoriesSQL(
245                                            FIND_BY_AND_CATEGORY_IDS, entryQuery.getAllCategoryIds(),
246                                            sb);
247                            }
248                    }
249    
250                    if (entryQuery.getAnyCategoryIds().length > 0) {
251                            if (PropsValues.ASSET_CATEGORIES_SEARCH_HIERARCHICAL) {
252                                    sb.append(
253                                            getCategoryIds(
254                                                    FIND_BY_AND_CATEGORY_IDS_TREE,
255                                                    entryQuery.getAnyCategoryIds()));
256                            }
257                            else {
258                                    sb.append(
259                                            getCategoryIds(
260                                                    FIND_BY_AND_CATEGORY_IDS,
261                                                    entryQuery.getAnyCategoryIds()));
262                            }
263                    }
264    
265                    if (entryQuery.getNotAllCategoryIds().length > 0) {
266                            if (PropsValues.ASSET_CATEGORIES_SEARCH_HIERARCHICAL) {
267                                    buildNotAnyCategoriesSQL(
268                                            FIND_BY_AND_CATEGORY_IDS_TREE,
269                                            entryQuery.getNotAllCategoryIds(), sb);
270                            }
271                            else {
272                                    buildNotAnyCategoriesSQL(
273                                            FIND_BY_AND_CATEGORY_IDS, entryQuery.getNotAllCategoryIds(),
274                                            sb);
275                            }
276                    }
277    
278                    if (entryQuery.getNotAnyCategoryIds().length > 0) {
279                            sb.append(" AND (");
280    
281                            if (PropsValues.ASSET_CATEGORIES_SEARCH_HIERARCHICAL) {
282                                    sb.append(
283                                            getNotCategoryIds(
284                                                    FIND_BY_AND_CATEGORY_IDS_TREE,
285                                                    entryQuery.getNotAnyCategoryIds()));
286                            }
287                            else {
288                                    sb.append(
289                                            getNotCategoryIds(
290                                                    FIND_BY_AND_CATEGORY_IDS,
291                                                    entryQuery.getNotAnyCategoryIds()));
292                            }
293    
294                            sb.append(") ");
295                    }
296    
297                    // Asset entry subtypes
298    
299                    if (entryQuery.getClassTypeIds().length > 0) {
300                            buildClassTypeIdsSQL(entryQuery.getClassTypeIds(), sb);
301                    }
302    
303                    // Tag conditions
304    
305                    if (entryQuery.getAllTagIds().length > 0) {
306                            buildAllTagsSQL(entryQuery.getAllTagIds(), sb);
307                    }
308    
309                    if (entryQuery.getAnyTagIds().length > 0) {
310                            sb.append(" AND (");
311                            sb.append(getTagIds(entryQuery.getAnyTagIds(), StringPool.EQUAL));
312                            sb.append(") ");
313                    }
314    
315                    if (entryQuery.getNotAllTagIds().length > 0) {
316                            buildNotAnyTagsSQL(entryQuery.getNotAllTagIds(), sb);
317                    }
318    
319                    if (entryQuery.getNotAnyTagIds().length > 0) {
320                            sb.append(" AND (");
321                            sb.append(getNotTagIds(entryQuery.getNotAnyTagIds()));
322                            sb.append(") ");
323                    }
324    
325                    // Other conditions
326    
327                    sb.append(
328                            getDates(
329                                    entryQuery.getPublishDate(), entryQuery.getExpirationDate()));
330                    sb.append(getGroupIds(entryQuery.getGroupIds()));
331                    sb.append(getClassNameIds(entryQuery.getClassNameIds()));
332    
333                    if (!count) {
334                            sb.append(" ORDER BY ");
335    
336                            if (entryQuery.getOrderByCol1().equals("ratings")) {
337                                    sb.append("RatingsEntry.score");
338                            }
339                            else {
340                                    sb.append("AssetEntry.");
341                                    sb.append(entryQuery.getOrderByCol1());
342                            }
343    
344                            sb.append(StringPool.SPACE);
345                            sb.append(entryQuery.getOrderByType1());
346    
347                            if (Validator.isNotNull(entryQuery.getOrderByCol2()) &&
348                                    !entryQuery.getOrderByCol1().equals(
349                                            entryQuery.getOrderByCol2())) {
350    
351                                    if (entryQuery.getOrderByCol2().equals("ratings")) {
352                                            sb.append(", RatingsEntry.score");
353                                    }
354                                    else {
355                                            sb.append(", AssetEntry.");
356                                            sb.append(entryQuery.getOrderByCol2());
357                                    }
358    
359                                    sb.append(StringPool.SPACE);
360                                    sb.append(entryQuery.getOrderByType2());
361                            }
362                    }
363    
364                    if (sb.index() > whereIndex) {
365                            String where = sb.stringAt(whereIndex);
366    
367                            if (where.startsWith(" AND")) {
368                                    sb.setStringAt(where.substring(4), whereIndex);
369                            }
370                    }
371    
372                    String sql = sb.toString();
373    
374                    SQLQuery q = session.createSQLQuery(sql);
375    
376                    if (count) {
377                            q.addScalar(COUNT_COLUMN_NAME, Type.LONG);
378                    }
379                    else {
380                            q.addEntity("AssetEntry", AssetEntryImpl.class);
381                    }
382    
383                    QueryPos qPos = QueryPos.getInstance(q);
384    
385                    if (entryQuery.getLinkedAssetEntryId() > 0) {
386                            qPos.add(entryQuery.getLinkedAssetEntryId());
387                            qPos.add(entryQuery.getLinkedAssetEntryId());
388                            qPos.add(entryQuery.getLinkedAssetEntryId());
389                    }
390    
391                    if (entryQuery.isVisible() != null) {
392                            qPos.add(entryQuery.isVisible());
393                    }
394    
395                    if (layout != null) {
396                            qPos.add(layout.getUuid());
397                    }
398    
399                    if (PropsValues.ASSET_CATEGORIES_SEARCH_HIERARCHICAL) {
400                            qPos.add(entryQuery.getAllLeftAndRightCategoryIds());
401                            qPos.add(entryQuery.getAnyLeftAndRightCategoryIds());
402                            qPos.add(entryQuery.getNotAllLeftAndRightCategoryIds());
403                            qPos.add(entryQuery.getNotAnyLeftAndRightCategoryIds());
404                    }
405                    else {
406                            qPos.add(entryQuery.getAllCategoryIds());
407                            qPos.add(entryQuery.getAnyCategoryIds());
408                            qPos.add(entryQuery.getNotAllCategoryIds());
409                            qPos.add(entryQuery.getNotAnyCategoryIds());
410                    }
411    
412                    qPos.add(entryQuery.getAllTagIds());
413                    qPos.add(entryQuery.getAnyTagIds());
414                    qPos.add(entryQuery.getNotAllTagIds());
415                    qPos.add(entryQuery.getNotAnyTagIds());
416    
417                    setDates(
418                            qPos, entryQuery.getPublishDate(), entryQuery.getExpirationDate());
419    
420                    qPos.add(entryQuery.getGroupIds());
421                    qPos.add(entryQuery.getClassNameIds());
422    
423                    return q;
424            }
425    
426            protected void buildClassTypeIdsSQL(long[] classTypeIds, StringBundler sb) {
427                    sb.append(" AND (");
428    
429                    for (int i = 0; i < classTypeIds.length; i++) {
430                            sb.append(" AssetEntry.classTypeId = ");
431                            sb.append(classTypeIds[i]);
432    
433                            if ((i + 1) < classTypeIds.length) {
434                                    sb.append(" OR ");
435                            }
436                            else {
437                                    sb.append(StringPool.CLOSE_PARENTHESIS);
438                            }
439                    }
440            }
441    
442            protected void buildNotAnyCategoriesSQL(
443                    String sqlId, long[] categoryIds, StringBundler sb) {
444    
445                    sb.append(" AND (");
446    
447                    for (int i = 0; i < categoryIds.length; i++) {
448                            sb.append("AssetEntry.entryId NOT IN (");
449                            sb.append(CustomSQLUtil.get(sqlId));
450                            sb.append(StringPool.CLOSE_PARENTHESIS);
451    
452                            if ((i + 1) < categoryIds.length) {
453                                    sb.append(" OR ");
454                            }
455                    }
456    
457                    sb.append(StringPool.CLOSE_PARENTHESIS);
458            }
459    
460            protected void buildNotAnyTagsSQL(long[] tagIds, StringBundler sb) {
461                    sb.append(" AND (");
462    
463                    for (int i = 0; i < tagIds.length; i++) {
464                            sb.append("AssetEntry.entryId NOT IN (");
465                            sb.append(CustomSQLUtil.get(FIND_BY_AND_TAG_IDS));
466                            sb.append(StringPool.CLOSE_PARENTHESIS);
467    
468                            if ((i + 1) < tagIds.length) {
469                                    sb.append(" OR ");
470                            }
471                    }
472    
473                    sb.append(StringPool.CLOSE_PARENTHESIS);
474            }
475    
476            protected String getCategoryIds(String sqlId, long[] categoryIds) {
477                    StringBundler sb = new StringBundler();
478    
479                    sb.append(" AND (");
480    
481                    for (int i = 0; i < categoryIds.length; i++) {
482                            sb.append("AssetEntry.entryId IN (");
483                            sb.append(CustomSQLUtil.get(sqlId));
484                            sb.append(StringPool.CLOSE_PARENTHESIS);
485    
486                            if ((i + 1) < categoryIds.length) {
487                                    sb.append(" OR ");
488                            }
489                    }
490    
491                    sb.append(StringPool.CLOSE_PARENTHESIS);
492    
493                    return sb.toString();
494            }
495    
496            protected String getClassNameIds(long[] classNameIds) {
497                    if (classNameIds.length == 0) {
498                            return StringPool.BLANK;
499                    }
500    
501                    StringBundler sb = new StringBundler(classNameIds.length + 2);
502    
503                    sb.append(" AND (AssetEntry.classNameId = ?");
504    
505                    for (int i = 1; i < classNameIds.length; i++) {
506                            sb.append(" OR AssetEntry.classNameId = ? ");
507                    }
508    
509                    sb.append(") ");
510    
511                    return sb.toString();
512            }
513    
514            protected String getDates(Date publishDate, Date expirationDate) {
515                    StringBundler sb = new StringBundler(4);
516    
517                    if (publishDate != null) {
518                            sb.append(" AND (AssetEntry.publishDate IS NULL OR ");
519                            sb.append("AssetEntry.publishDate < ?)");
520                    }
521    
522                    if (expirationDate != null) {
523                            sb.append(" AND (AssetEntry.expirationDate IS NULL OR ");
524                            sb.append("AssetEntry.expirationDate > ?)");
525                    }
526    
527                    return sb.toString();
528            }
529    
530            protected String getGroupIds(long[] groupIds) {
531                    if (groupIds.length == 0) {
532                            return StringPool.BLANK;
533                    }
534    
535                    StringBundler sb = new StringBundler(groupIds.length + 2);
536    
537                    sb.append(" AND (AssetEntry.groupId = ? ");
538    
539                    for (int i = 1; i < groupIds.length; i++) {
540                            sb.append(" OR AssetEntry.groupId = ? ");
541                    }
542    
543                    sb.append(")");
544    
545                    return sb.toString();
546            }
547    
548            protected String getNotCategoryIds(String sqlId, long[] notCategoryIds) {
549                    if (notCategoryIds.length == 0) {
550                            return StringPool.BLANK;
551                    }
552    
553                    StringBundler sb = new StringBundler(notCategoryIds.length * 4 - 1);
554    
555                    for (int i = 0; i < notCategoryIds.length; i++) {
556                            sb.append("AssetEntry.entryId NOT IN (");
557                            sb.append(CustomSQLUtil.get(sqlId));
558                            sb.append(StringPool.CLOSE_PARENTHESIS);
559    
560                            if ((i + 1) < notCategoryIds.length) {
561                                    sb.append(" AND ");
562                            }
563                    }
564    
565                    return sb.toString();
566            }
567    
568            protected String getNotTagIds(long[] notTagIds) {
569                    if (notTagIds.length == 0) {
570                            return StringPool.BLANK;
571                    }
572    
573                    StringBundler sb = new StringBundler(notTagIds.length * 4 - 1);
574    
575                    for (int i = 0; i < notTagIds.length; i++) {
576                            sb.append("AssetEntry.entryId NOT IN (");
577                            sb.append(CustomSQLUtil.get(FIND_BY_AND_TAG_IDS));
578                            sb.append(StringPool.CLOSE_PARENTHESIS);
579    
580                            if ((i + 1) < notTagIds.length) {
581                                    sb.append(" AND ");
582                            }
583                    }
584    
585                    return sb.toString();
586            }
587    
588            protected String getTagIds(long[] tagIds, String operator) {
589                    StringBundler sb = new StringBundler(tagIds.length * 4 - 1);
590    
591                    for (int i = 0; i < tagIds.length; i++) {
592                            sb.append("AssetTag.tagId ");
593                            sb.append(operator);
594                            sb.append(" ? ");
595    
596                            if ((i + 1) != tagIds.length) {
597                                    sb.append("OR ");
598                            }
599                    }
600    
601                    return sb.toString();
602            }
603    
604            protected void setDates(
605                    QueryPos qPos, Date publishDate, Date expirationDate) {
606    
607                    if (publishDate != null) {
608                            Timestamp publishDate_TS = CalendarUtil.getTimestamp(publishDate);
609    
610                            qPos.add(publishDate_TS);
611                    }
612    
613                    if (expirationDate != null) {
614                            Timestamp expirationDate_TS = CalendarUtil.getTimestamp(
615                                    expirationDate);
616    
617                            qPos.add(expirationDate_TS);
618                    }
619            }
620    
621    }