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 DISTINCT {AssetEntry.*} ");
230    
231                            String orderByCol1 = entryQuery.getOrderByCol1();
232                            String orderByCol2 = entryQuery.getOrderByCol2();
233    
234                            if (orderByCol1.equals("ratings") ||
235                                    orderByCol2.equals("ratings")) {
236    
237                                    sb.append(", RatingsStats.averageScore ");
238                            }
239                    }
240    
241                    sb.append("FROM AssetEntry ");
242    
243                    if (entryQuery.getAnyTagIds().length > 0) {
244                            sb.append("INNER JOIN ");
245                            sb.append("AssetEntries_AssetTags ON ");
246                            sb.append("(AssetEntries_AssetTags.entryId = ");
247                            sb.append("AssetEntry.entryId) ");
248                            sb.append("INNER JOIN ");
249                            sb.append("AssetTag ON ");
250                            sb.append("(AssetTag.tagId = AssetEntries_AssetTags.tagId) ");
251                    }
252    
253                    if (entryQuery.getLinkedAssetEntryId() > 0) {
254                            sb.append("INNER JOIN ");
255                            sb.append("AssetLink ON ");
256                            sb.append("(AssetEntry.entryId = AssetLink.entryId1) ");
257                            sb.append("OR (AssetEntry.entryId = AssetLink.entryId2)");
258                    }
259    
260                    if (entryQuery.getOrderByCol1().equals("ratings") ||
261                            entryQuery.getOrderByCol2().equals("ratings")) {
262    
263                            sb.append(" LEFT JOIN ");
264                            sb.append("RatingsStats ON ");
265                            sb.append("(RatingsStats.classNameId = ");
266                            sb.append("AssetEntry.classNameId) AND ");
267                            sb.append("(RatingsStats.classPK = AssetEntry.classPK)");
268                    }
269    
270                    sb.append("WHERE ");
271    
272                    int whereIndex = sb.index();
273    
274                    if (entryQuery.getLinkedAssetEntryId() > 0) {
275                            sb.append(" AND ((AssetLink.entryId1 = ?) OR ");
276                            sb.append("(AssetLink.entryId2 = ?))");
277                            sb.append(" AND (AssetEntry.entryId != ?)");
278                    }
279    
280                    if (entryQuery.isVisible() != null) {
281                            sb.append(" AND (visible = ?)");
282                    }
283    
284                    if (entryQuery.isExcludeZeroViewCount()) {
285                            sb.append(" AND (AssetEntry.viewCount > 0)");
286                    }
287    
288                    // Keywords
289    
290                    if (Validator.isNotNull(entryQuery.getKeywords())) {
291                            sb.append(" AND ((AssetEntry.title LIKE ?) OR");
292                            sb.append(" (AssetEntry.description LIKE ?))");
293                    }
294                    else {
295                            if (Validator.isNotNull(entryQuery.getTitle())) {
296                                    sb.append(" AND (AssetEntry.title LIKE ?)");
297                            }
298    
299                            if (Validator.isNotNull(entryQuery.getDescription())) {
300                                    sb.append(" AND (AssetEntry.description LIKE ?)");
301                            }
302                    }
303    
304                    // Layout
305    
306                    Layout layout = entryQuery.getLayout();
307    
308                    if (layout != null) {
309                            sb.append(" AND (AssetEntry.layoutUuid = ?)");
310                    }
311    
312                    // Category conditions
313    
314                    if (entryQuery.getAllCategoryIds().length > 0) {
315                            buildAllCategoriesSQL(entryQuery.getAllCategoryIds(), sb);
316                    }
317    
318                    if (entryQuery.getAnyCategoryIds().length > 0) {
319                            buildAnyCategoriesSQL(entryQuery.getAnyCategoryIds(), sb);
320                    }
321    
322                    if (entryQuery.getNotAllCategoryIds().length > 0) {
323                            buildNotAllCategoriesSQL(entryQuery.getNotAllCategoryIds(), sb);
324                    }
325    
326                    if (entryQuery.getNotAnyCategoryIds().length > 0) {
327                            buildNotAnyCategoriesSQL(entryQuery.getNotAnyCategoryIds(), sb);
328                    }
329    
330                    // Asset entry subtypes
331    
332                    if (entryQuery.getClassTypeIds().length > 0) {
333                            buildClassTypeIdsSQL(entryQuery.getClassTypeIds(), sb);
334                    }
335    
336                    // Tag conditions
337    
338                    if (entryQuery.getAllTagIds().length > 0) {
339                            buildAllTagsSQL(entryQuery.getAllTagIdsArray(), sb);
340                    }
341    
342                    if (entryQuery.getAnyTagIds().length > 0) {
343                            buildAnyTagsSQL(entryQuery.getAnyTagIds(), sb);
344                    }
345    
346                    if (entryQuery.getNotAllTagIds().length > 0) {
347                            buildNotAllTagsSQL(entryQuery.getNotAllTagIdsArray(), sb);
348                    }
349    
350                    if (entryQuery.getNotAnyTagIds().length > 0) {
351                            buildNotAnyTagsSQL(entryQuery.getNotAnyTagIds(), sb);
352                    }
353    
354                    // Other conditions
355    
356                    sb.append(
357                            getDates(
358                                    entryQuery.getPublishDate(), entryQuery.getExpirationDate()));
359                    sb.append(getGroupIds(entryQuery.getGroupIds()));
360                    sb.append(getClassNameIds(entryQuery.getClassNameIds()));
361    
362                    if (!count) {
363                            sb.append(" ORDER BY ");
364    
365                            if (entryQuery.getOrderByCol1().equals("ratings")) {
366                                    sb.append("RatingsStats.averageScore");
367                            }
368                            else {
369                                    sb.append("AssetEntry.");
370                                    sb.append(entryQuery.getOrderByCol1());
371                            }
372    
373                            sb.append(StringPool.SPACE);
374                            sb.append(entryQuery.getOrderByType1());
375    
376                            if (Validator.isNotNull(entryQuery.getOrderByCol2()) &&
377                                    !entryQuery.getOrderByCol1().equals(
378                                            entryQuery.getOrderByCol2())) {
379    
380                                    if (entryQuery.getOrderByCol2().equals("ratings")) {
381                                            sb.append(", RatingsStats.averageScore");
382                                    }
383                                    else {
384                                            sb.append(", AssetEntry.");
385                                            sb.append(entryQuery.getOrderByCol2());
386                                    }
387    
388                                    sb.append(StringPool.SPACE);
389                                    sb.append(entryQuery.getOrderByType2());
390                            }
391                    }
392    
393                    if (sb.index() > whereIndex) {
394                            String where = sb.stringAt(whereIndex);
395    
396                            if (where.startsWith(" AND")) {
397                                    sb.setStringAt(where.substring(4), whereIndex);
398                            }
399                    }
400    
401                    String sql = sb.toString();
402    
403                    SQLQuery q = session.createSQLQuery(sql);
404    
405                    if (count) {
406                            q.addScalar(COUNT_COLUMN_NAME, Type.LONG);
407                    }
408                    else {
409                            q.addEntity("AssetEntry", AssetEntryImpl.class);
410                    }
411    
412                    QueryPos qPos = QueryPos.getInstance(q);
413    
414                    if (entryQuery.getLinkedAssetEntryId() > 0) {
415                            qPos.add(entryQuery.getLinkedAssetEntryId());
416                            qPos.add(entryQuery.getLinkedAssetEntryId());
417                            qPos.add(entryQuery.getLinkedAssetEntryId());
418                    }
419    
420                    if (entryQuery.isVisible() != null) {
421                            qPos.add(entryQuery.isVisible());
422                    }
423    
424                    if (Validator.isNotNull(entryQuery.getKeywords())) {
425                            qPos.add(entryQuery.getKeywords() + CharPool.PERCENT);
426                            qPos.add(entryQuery.getKeywords() + CharPool.PERCENT);
427                    }
428                    else {
429                            if (Validator.isNotNull(entryQuery.getTitle())) {
430                                    qPos.add(entryQuery.getTitle() + CharPool.PERCENT);
431                            }
432    
433                            if (Validator.isNotNull(entryQuery.getDescription())) {
434                                    qPos.add(entryQuery.getDescription() + CharPool.PERCENT);
435                            }
436                    }
437    
438                    if (layout != null) {
439                            qPos.add(layout.getUuid());
440                    }
441    
442                    setDates(
443                            qPos, entryQuery.getPublishDate(), entryQuery.getExpirationDate());
444    
445                    qPos.add(entryQuery.getGroupIds());
446                    qPos.add(entryQuery.getClassNameIds());
447    
448                    return q;
449            }
450    
451            protected void buildClassTypeIdsSQL(long[] classTypeIds, StringBundler sb) {
452                    sb.append(" AND (");
453    
454                    for (int i = 0; i < classTypeIds.length; i++) {
455                            sb.append(" AssetEntry.classTypeId = ");
456                            sb.append(classTypeIds[i]);
457    
458                            if ((i + 1) < classTypeIds.length) {
459                                    sb.append(" OR ");
460                            }
461                            else {
462                                    sb.append(StringPool.CLOSE_PARENTHESIS);
463                            }
464                    }
465            }
466    
467            protected void buildNotAllCategoriesSQL(
468                            long[] categoryIds, StringBundler sb)
469                    throws SystemException {
470    
471                    String findByAndCategoryIdsSQL = CustomSQLUtil.get(
472                            FIND_BY_AND_CATEGORY_IDS);
473    
474                    sb.append(" AND (");
475    
476                    for (int i = 0; i < categoryIds.length; i++) {
477                            sb.append("NOT ");
478    
479                            String sql = null;
480    
481                            if (PropsValues.ASSET_CATEGORIES_SEARCH_HIERARCHICAL) {
482                                    List<Long> treeCategoryIds = AssetCategoryFinderUtil.findByG_L(
483                                            categoryIds[i]);
484    
485                                    if (treeCategoryIds.size() > 1) {
486                                            sql = StringUtil.replace(
487                                                    findByAndCategoryIdsSQL, "[$CATEGORY_ID$]",
488                                                    StringUtil.merge(treeCategoryIds));
489                                    }
490                            }
491    
492                            if (sql == null) {
493                                    sql = StringUtil.replace(
494                                            findByAndCategoryIdsSQL, " IN ([$CATEGORY_ID$])",
495                                            " = " + categoryIds[i]);
496                            }
497    
498                            sb.append(sql);
499    
500                            if ((i + 1) < categoryIds.length) {
501                                    sb.append(" OR ");
502                            }
503                    }
504    
505                    sb.append(StringPool.CLOSE_PARENTHESIS);
506            }
507    
508            protected void buildNotAllTagsSQL(long[][] tagIds, StringBundler sb) {
509                    sb.append(" AND (");
510    
511                    for (int i = 0; i < tagIds.length; i++) {
512                            sb.append("AssetEntry.entryId NOT IN (");
513    
514                            String sql = CustomSQLUtil.get(FIND_BY_AND_TAG_IDS);
515    
516                            sql = StringUtil.replace(sql, "[$TAG_ID$]", getTagIds(tagIds[i]));
517    
518                            sb.append(sql);
519                            sb.append(StringPool.CLOSE_PARENTHESIS);
520    
521                            if (((i + 1) < tagIds.length) && (tagIds[i + 1].length > 0)) {
522                                    sb.append(" OR ");
523                            }
524                    }
525    
526                    sb.append(StringPool.CLOSE_PARENTHESIS);
527            }
528    
529            protected void buildNotAnyCategoriesSQL(
530                            long[] notCategoryIds, StringBundler sb)
531                    throws SystemException {
532    
533                    sb.append(" AND (NOT ");
534    
535                    String sql = CustomSQLUtil.get(FIND_BY_AND_CATEGORY_IDS);
536    
537                    String notCategoryIdsString = null;
538    
539                    if (PropsValues.ASSET_CATEGORIES_SEARCH_HIERARCHICAL) {
540                            List<Long> notCategoryIdsList = new ArrayList<Long>();
541    
542                            for (long notCategoryId : notCategoryIds) {
543                                    notCategoryIdsList.addAll(
544                                            AssetCategoryFinderUtil.findByG_L(notCategoryId));
545                            }
546    
547                            notCategoryIdsString = StringUtil.merge(notCategoryIdsList);
548                    }
549                    else {
550                            notCategoryIdsString = StringUtil.merge(notCategoryIds);
551                    }
552    
553                    sb.append(
554                            StringUtil.replace(sql, "[$CATEGORY_ID$]", notCategoryIdsString));
555                    sb.append(StringPool.CLOSE_PARENTHESIS);
556            }
557    
558            protected String buildNotAnyTagsSQL(long[] notTagIds, StringBundler sb) {
559                    sb.append(" AND (");
560    
561                    for (int i = 0; i < notTagIds.length; i++) {
562                            sb.append("AssetEntry.entryId NOT IN (");
563    
564                            String sql = CustomSQLUtil.get(FIND_BY_AND_TAG_IDS);
565    
566                            sql = StringUtil.replace(sql, "[$TAG_ID$]", getTagIds(notTagIds));
567    
568                            sb.append(sql);
569                            sb.append(StringPool.CLOSE_PARENTHESIS);
570    
571                            if ((i + 1) < notTagIds.length) {
572                                    sb.append(" AND ");
573                            }
574                    }
575    
576                    sb.append(StringPool.CLOSE_PARENTHESIS);
577    
578                    return sb.toString();
579            }
580    
581            protected String getClassNameIds(long[] classNameIds) {
582                    if (classNameIds.length == 0) {
583                            return StringPool.BLANK;
584                    }
585    
586                    StringBundler sb = new StringBundler(classNameIds.length + 1);
587    
588                    sb.append(" AND (AssetEntry.classNameId = ?");
589    
590                    for (int i = 0; i < (classNameIds.length - 1); i++) {
591                            sb.append(" OR AssetEntry.classNameId = ?");
592                    }
593    
594                    sb.append(StringPool.CLOSE_PARENTHESIS);
595    
596                    return sb.toString();
597            }
598    
599            protected String getDates(Date publishDate, Date expirationDate) {
600                    StringBundler sb = new StringBundler(4);
601    
602                    if (publishDate != null) {
603                            sb.append(" AND (AssetEntry.publishDate IS NULL OR ");
604                            sb.append("AssetEntry.publishDate < ?)");
605                    }
606    
607                    if (expirationDate != null) {
608                            sb.append(" AND (AssetEntry.expirationDate IS NULL OR ");
609                            sb.append("AssetEntry.expirationDate > ?)");
610                    }
611    
612                    return sb.toString();
613            }
614    
615            protected String getGroupIds(long[] groupIds) {
616                    if (groupIds.length == 0) {
617                            return StringPool.BLANK;
618                    }
619    
620                    StringBundler sb = new StringBundler(groupIds.length + 1);
621    
622                    sb.append(" AND (AssetEntry.groupId = ?");
623    
624                    for (int i = 0; i < (groupIds.length - 1); i++) {
625                            sb.append(" OR AssetEntry.groupId = ?");
626                    }
627    
628                    sb.append(StringPool.CLOSE_PARENTHESIS);
629    
630                    return sb.toString();
631            }
632    
633            protected String getTagIds(long[] tagIds) {
634                    StringBundler sb = new StringBundler((tagIds.length * 3) - 1);
635    
636                    for (int i = 0; i < tagIds.length; i++) {
637                            sb.append("tagId = ");
638                            sb.append(tagIds[i]);
639    
640                            if ((i + 1) != tagIds.length) {
641                                    sb.append(" OR ");
642                            }
643                    }
644    
645                    return sb.toString();
646            }
647    
648            protected void setDates(
649                    QueryPos qPos, Date publishDate, Date expirationDate) {
650    
651                    if (publishDate != null) {
652                            Timestamp publishDate_TS = CalendarUtil.getTimestamp(publishDate);
653    
654                            qPos.add(publishDate_TS);
655                    }
656    
657                    if (expirationDate != null) {
658                            Timestamp expirationDate_TS = CalendarUtil.getTimestamp(
659                                    expirationDate);
660    
661                            qPos.add(expirationDate_TS);
662                    }
663            }
664    
665    }