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