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