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 String FIND_BY_AND_CATEGORY_IDS =
048                    AssetEntryFinder.class.getName() + ".findByAndCategoryIds";
049    
050            public static String FIND_BY_AND_CATEGORY_IDS_TREE =
051                    AssetEntryFinder.class.getName() + ".findByAndCategoryIdsTree";
052    
053            public static 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(),
419                            entryQuery.getExpirationDate());
420    
421                    qPos.add(entryQuery.getGroupIds());
422                    qPos.add(entryQuery.getClassNameIds());
423    
424                    return q;
425            }
426    
427            protected void buildClassTypeIdsSQL(long[] classTypeIds, StringBundler sb) {
428                    sb.append(" AND (");
429    
430                    for (int i = 0; i < classTypeIds.length; i++) {
431                            sb.append(" AssetEntry.classTypeId = ");
432                            sb.append(classTypeIds[i]);
433    
434                            if ((i + 1) < classTypeIds.length) {
435                                    sb.append(" OR ");
436                            }
437                            else {
438                                    sb.append(StringPool.CLOSE_PARENTHESIS);
439                            }
440                    }
441            }
442    
443            protected void buildNotAnyCategoriesSQL(
444                    String sqlId, long[] categoryIds, StringBundler sb) {
445    
446                    sb.append(" AND (");
447    
448                    for (int i = 0; i < categoryIds.length; i++) {
449                            sb.append("AssetEntry.entryId NOT IN (");
450                            sb.append(CustomSQLUtil.get(sqlId));
451                            sb.append(StringPool.CLOSE_PARENTHESIS);
452    
453                            if ((i + 1) < categoryIds.length) {
454                                    sb.append(" OR ");
455                            }
456                    }
457    
458                    sb.append(StringPool.CLOSE_PARENTHESIS);
459            }
460    
461            protected void buildNotAnyTagsSQL(long[] tagIds, StringBundler sb) {
462                    sb.append(" AND (");
463    
464                    for (int i = 0; i < tagIds.length; i++) {
465                            sb.append("AssetEntry.entryId NOT IN (");
466                            sb.append(CustomSQLUtil.get(FIND_BY_AND_TAG_IDS));
467                            sb.append(StringPool.CLOSE_PARENTHESIS);
468    
469                            if ((i + 1) < tagIds.length) {
470                                    sb.append(" OR ");
471                            }
472                    }
473    
474                    sb.append(StringPool.CLOSE_PARENTHESIS);
475            }
476    
477            protected String getCategoryIds(String sqlId, long[] categoryIds) {
478                    StringBundler sb = new StringBundler();
479    
480                    sb.append(" AND (");
481    
482                    for (int i = 0; i < categoryIds.length; i++) {
483                            sb.append("AssetEntry.entryId IN (");
484                            sb.append(CustomSQLUtil.get(sqlId));
485                            sb.append(StringPool.CLOSE_PARENTHESIS);
486    
487                            if ((i + 1) < categoryIds.length) {
488                                    sb.append(" OR ");
489                            }
490                    }
491    
492                    sb.append(StringPool.CLOSE_PARENTHESIS);
493    
494                    return sb.toString();
495            }
496    
497            protected String getClassNameIds(long[] classNameIds) {
498                    if (classNameIds.length == 0) {
499                            return StringPool.BLANK;
500                    }
501    
502                    StringBundler sb = new StringBundler(classNameIds.length + 2);
503    
504                    sb.append(" AND (AssetEntry.classNameId = ?");
505    
506                    for (int i = 1; i < classNameIds.length; i++) {
507                            sb.append(" OR AssetEntry.classNameId = ? ");
508                    }
509    
510                    sb.append(") ");
511    
512                    return sb.toString();
513            }
514    
515            protected String getDates(Date publishDate, Date expirationDate) {
516                    StringBundler sb = new StringBundler(4);
517    
518                    if (publishDate != null) {
519                            sb.append(" AND (AssetEntry.publishDate IS NULL OR ");
520                            sb.append("AssetEntry.publishDate < ?)");
521                    }
522    
523                    if (expirationDate != null) {
524                            sb.append(" AND (AssetEntry.expirationDate IS NULL OR ");
525                            sb.append("AssetEntry.expirationDate > ?)");
526                    }
527    
528                    return sb.toString();
529            }
530    
531            protected String getGroupIds(long[] groupIds) {
532                    if (groupIds.length == 0) {
533                            return StringPool.BLANK;
534                    }
535    
536                    StringBundler sb = new StringBundler(groupIds.length + 2);
537    
538                    sb.append(" AND (AssetEntry.groupId = ? ");
539    
540                    for (int i = 1; i < groupIds.length; i++) {
541                            sb.append(" OR AssetEntry.groupId = ? ");
542                    }
543    
544                    sb.append(")");
545    
546                    return sb.toString();
547            }
548    
549            protected String getNotCategoryIds(String sqlId, long[] notCategoryIds) {
550                    if (notCategoryIds.length == 0) {
551                            return StringPool.BLANK;
552                    }
553    
554                    StringBundler sb = new StringBundler(notCategoryIds.length * 4 - 1);
555    
556                    for (int i = 0; i < notCategoryIds.length; i++) {
557                            sb.append("AssetEntry.entryId NOT IN (");
558                            sb.append(CustomSQLUtil.get(sqlId));
559                            sb.append(StringPool.CLOSE_PARENTHESIS);
560    
561                            if ((i + 1) < notCategoryIds.length) {
562                                    sb.append(" AND ");
563                            }
564                    }
565    
566                    return sb.toString();
567            }
568    
569            protected String getNotTagIds(long[] notTagIds) {
570                    if (notTagIds.length == 0) {
571                            return StringPool.BLANK;
572                    }
573    
574                    StringBundler sb = new StringBundler(notTagIds.length * 4 - 1);
575    
576                    for (int i = 0; i < notTagIds.length; i++) {
577                            sb.append("AssetEntry.entryId NOT IN (");
578                            sb.append(CustomSQLUtil.get(FIND_BY_AND_TAG_IDS));
579                            sb.append(StringPool.CLOSE_PARENTHESIS);
580    
581                            if ((i + 1) < notTagIds.length) {
582                                    sb.append(" AND ");
583                            }
584                    }
585    
586                    return sb.toString();
587            }
588    
589            protected String getTagIds(long[] tagIds, String operator) {
590                    StringBundler sb = new StringBundler(tagIds.length * 4 - 1);
591    
592                    for (int i = 0; i < tagIds.length; i++) {
593                            sb.append("AssetTag.tagId ");
594                            sb.append(operator);
595                            sb.append(" ? ");
596    
597                            if ((i + 1) != tagIds.length) {
598                                    sb.append("OR ");
599                            }
600                    }
601    
602                    return sb.toString();
603            }
604    
605            protected void setDates(
606                    QueryPos qPos, Date publishDate, Date expirationDate) {
607    
608                    if (publishDate != null) {
609                            Timestamp publishDate_TS = CalendarUtil.getTimestamp(publishDate);
610    
611                            qPos.add(publishDate_TS);
612                    }
613    
614                    if (expirationDate != null) {
615                            Timestamp expirationDate_TS =
616                                    CalendarUtil.getTimestamp(expirationDate);
617    
618                            qPos.add(expirationDate_TS);
619                    }
620            }
621    
622    }