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