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