001    /**
002     * Copyright (c) 2000-2012 Liferay, Inc. All rights reserved.
003     *
004     * The contents of this file are subject to the terms of the Liferay Enterprise
005     * Subscription License ("License"). You may not use this file except in
006     * compliance with the License. You can obtain a copy of the License by
007     * contacting Liferay, Inc. See the License for the specific language governing
008     * permissions and limitations under the License, including but not limited to
009     * distribution rights of the Software.
010     *
011     *
012     *
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 sql = CustomSQLUtil.get(FIND_BY_AND_CATEGORY_IDS);
109    
110                    sb.append(" AND (");
111    
112                    for (int i = 0; i < categoryIds.length; i++) {
113                            if (PropsValues.ASSET_CATEGORIES_SEARCH_HIERARCHICAL) {
114                                    List<Long> treeCategoryIds = AssetCategoryFinderUtil.findByG_L(
115                                            categoryIds[i]);
116    
117                                    if (treeCategoryIds.size() > 1) {
118                                            sb.append(
119                                                    StringUtil.replace(
120                                                            sql, "[$CATEGORY_ID$]",
121                                                            StringUtil.merge(treeCategoryIds)));
122    
123                                            continue;
124                                    }
125                            }
126    
127                            sb.append(
128                                    StringUtil.replace(
129                                            sql, " IN ([$CATEGORY_ID$])", " = " + categoryIds[i]));
130    
131                            if ((i + 1) < categoryIds.length) {
132                                    sb.append(" AND ");
133                            }
134                    }
135    
136                    sb.append(StringPool.CLOSE_PARENTHESIS);
137            }
138    
139            protected void buildAllTagsSQL(long[][] tagIds, StringBundler sb) {
140                    sb.append(" AND AssetEntry.entryId IN (");
141    
142                    for (int i = 0; i < tagIds.length; i++) {
143                            String sql = CustomSQLUtil.get(FIND_BY_AND_TAG_IDS);
144    
145                            sql = StringUtil.replace(
146                                    sql, "[$TAG_ID]", getTagIds(tagIds[i], StringPool.EQUAL));
147    
148                            sb.append(sql);
149    
150                            if ((i + 1) < tagIds.length) {
151                                    sb.append(" AND AssetEntry.entryId IN (");
152                            }
153                    }
154    
155                    for (int i = 0; i < tagIds.length; i++) {
156                            if ((i + 1) < tagIds.length) {
157                                    sb.append(StringPool.CLOSE_PARENTHESIS);
158                            }
159                    }
160    
161                    sb.append(StringPool.CLOSE_PARENTHESIS);
162            }
163    
164            protected void buildAnyCategoriesSQL(long[] categoryIds, StringBundler sb)
165                    throws SystemException {
166    
167                    sb.append(" AND (");
168    
169                    String sql = CustomSQLUtil.get(FIND_BY_AND_CATEGORY_IDS);
170    
171                    String categoryIdsString = null;
172    
173                    if (PropsValues.ASSET_CATEGORIES_SEARCH_HIERARCHICAL) {
174                            List<Long> categoryIdsList = new ArrayList<Long>();
175    
176                            for (long categoryId : categoryIds) {
177                                    categoryIdsList.addAll(
178                                            AssetCategoryFinderUtil.findByG_L(categoryId));
179                            }
180    
181                            categoryIdsString = StringUtil.merge(categoryIdsList);
182                    }
183                    else {
184                            categoryIdsString = StringUtil.merge(categoryIds);
185                    }
186    
187                    sb.append(
188                            StringUtil.replace(sql, "[$CATEGORY_ID$]", categoryIdsString));
189                    sb.append(StringPool.CLOSE_PARENTHESIS);
190            }
191    
192            protected SQLQuery buildAssetQuerySQL(
193                            AssetEntryQuery entryQuery, boolean count, Session session)
194                    throws SystemException {
195    
196                    StringBundler sb = new StringBundler();
197    
198                    if (count) {
199                            sb.append(
200                                    "SELECT COUNT(DISTINCT AssetEntry.entryId) AS COUNT_VALUE ");
201                    }
202                    else {
203                            sb.append("SELECT DISTINCT {AssetEntry.*} ");
204    
205                            String orderByCol1 = entryQuery.getOrderByCol1();
206                            String orderByCol2 = entryQuery.getOrderByCol2();
207    
208                            if (orderByCol1.equals("ratings") ||
209                                    orderByCol2.equals("ratings")) {
210    
211                                    sb.append(", RatingsStats.averageScore ");
212                            }
213                    }
214    
215                    sb.append("FROM AssetEntry ");
216    
217                    if (entryQuery.getAnyTagIds().length > 0) {
218                            sb.append("INNER JOIN ");
219                            sb.append("AssetEntries_AssetTags ON ");
220                            sb.append("(AssetEntries_AssetTags.entryId = ");
221                            sb.append("AssetEntry.entryId) ");
222                            sb.append("INNER JOIN ");
223                            sb.append("AssetTag ON ");
224                            sb.append("(AssetTag.tagId = AssetEntries_AssetTags.tagId) ");
225                    }
226    
227                    if (entryQuery.getLinkedAssetEntryId() > 0) {
228                            sb.append("INNER JOIN ");
229                            sb.append("AssetLink ON ");
230                            sb.append("(AssetEntry.entryId = AssetLink.entryId1) ");
231                            sb.append("OR (AssetEntry.entryId = AssetLink.entryId2)");
232                    }
233    
234                    if (entryQuery.getOrderByCol1().equals("ratings") ||
235                            entryQuery.getOrderByCol2().equals("ratings")) {
236    
237                            sb.append(" LEFT JOIN ");
238                            sb.append("RatingsStats ON ");
239                            sb.append("(RatingsStats.classNameId = ");
240                            sb.append("AssetEntry.classNameId) AND ");
241                            sb.append("(RatingsStats.classPK = AssetEntry.classPK)");
242                    }
243    
244                    sb.append("WHERE ");
245    
246                    int whereIndex = sb.index();
247    
248                    if (entryQuery.getLinkedAssetEntryId() > 0) {
249                            sb.append(" AND ((AssetLink.entryId1 = ?) OR ");
250                            sb.append("(AssetLink.entryId2 = ?))");
251                            sb.append(" AND (AssetEntry.entryId != ?)");
252                    }
253    
254                    if (entryQuery.isVisible() != null) {
255                            sb.append(" AND (visible = ?)");
256                    }
257    
258                    if (entryQuery.isExcludeZeroViewCount()) {
259                            sb.append(" AND (AssetEntry.viewCount > 0)");
260                    }
261    
262                    // Layout
263    
264                    Layout layout = entryQuery.getLayout();
265    
266                    if (layout != null) {
267                            sb.append(" AND (AssetEntry.layoutUuid = ?)");
268                    }
269    
270                    // Category conditions
271    
272                    if (entryQuery.getAllCategoryIds().length > 0) {
273                            buildAllCategoriesSQL(entryQuery.getAllCategoryIds(), sb);
274                    }
275    
276                    if (entryQuery.getAnyCategoryIds().length > 0) {
277                            buildAnyCategoriesSQL(entryQuery.getAnyCategoryIds(), sb);
278                    }
279    
280                    if (entryQuery.getNotAllCategoryIds().length > 0) {
281                            buildNotAllCategoriesSQL(entryQuery.getNotAllCategoryIds(), sb);
282                    }
283    
284                    if (entryQuery.getNotAnyCategoryIds().length > 0) {
285                            buildNotAnyCategoriesSQL(entryQuery.getNotAnyCategoryIds(), sb);
286                    }
287    
288                    // Asset entry subtypes
289    
290                    if (entryQuery.getClassTypeIds().length > 0) {
291                            buildClassTypeIdsSQL(entryQuery.getClassTypeIds(), sb);
292                    }
293    
294                    // Tag conditions
295    
296                    if (entryQuery.getAllTagIds().length > 0) {
297                            buildAllTagsSQL(entryQuery.getAllTagIdsArray(), sb);
298                    }
299    
300                    if (entryQuery.getAnyTagIds().length > 0) {
301                            sb.append(" AND (");
302                            sb.append(
303                                    getAnyTagIds(entryQuery.getAnyTagIds(), StringPool.EQUAL));
304                            sb.append(") ");
305                    }
306    
307                    if (entryQuery.getNotAllTagIds().length > 0) {
308                            buildNotAnyTagsSQL(entryQuery.getNotAllTagIdsArray(), sb);
309                    }
310    
311                    if (entryQuery.getNotAnyTagIds().length > 0) {
312                            sb.append(" AND (");
313                            sb.append(getNotTagIds(entryQuery.getNotAnyTagIds()));
314                            sb.append(") ");
315                    }
316    
317                    // Other conditions
318    
319                    sb.append(
320                            getDates(
321                                    entryQuery.getPublishDate(), entryQuery.getExpirationDate()));
322                    sb.append(getGroupIds(entryQuery.getGroupIds()));
323                    sb.append(getClassNameIds(entryQuery.getClassNameIds()));
324    
325                    if (!count) {
326                            sb.append(" ORDER BY ");
327    
328                            if (entryQuery.getOrderByCol1().equals("ratings")) {
329                                    sb.append("RatingsStats.averageScore");
330                            }
331                            else {
332                                    sb.append("AssetEntry.");
333                                    sb.append(entryQuery.getOrderByCol1());
334                            }
335    
336                            sb.append(StringPool.SPACE);
337                            sb.append(entryQuery.getOrderByType1());
338    
339                            if (Validator.isNotNull(entryQuery.getOrderByCol2()) &&
340                                    !entryQuery.getOrderByCol1().equals(
341                                            entryQuery.getOrderByCol2())) {
342    
343                                    if (entryQuery.getOrderByCol2().equals("ratings")) {
344                                            sb.append(", RatingsStats.averageScore");
345                                    }
346                                    else {
347                                            sb.append(", AssetEntry.");
348                                            sb.append(entryQuery.getOrderByCol2());
349                                    }
350    
351                                    sb.append(StringPool.SPACE);
352                                    sb.append(entryQuery.getOrderByType2());
353                            }
354                    }
355    
356                    if (sb.index() > whereIndex) {
357                            String where = sb.stringAt(whereIndex);
358    
359                            if (where.startsWith(" AND")) {
360                                    sb.setStringAt(where.substring(4), whereIndex);
361                            }
362                    }
363    
364                    String sql = sb.toString();
365    
366                    SQLQuery q = session.createSQLQuery(sql);
367    
368                    if (count) {
369                            q.addScalar(COUNT_COLUMN_NAME, Type.LONG);
370                    }
371                    else {
372                            q.addEntity("AssetEntry", AssetEntryImpl.class);
373                    }
374    
375                    QueryPos qPos = QueryPos.getInstance(q);
376    
377                    if (entryQuery.getLinkedAssetEntryId() > 0) {
378                            qPos.add(entryQuery.getLinkedAssetEntryId());
379                            qPos.add(entryQuery.getLinkedAssetEntryId());
380                            qPos.add(entryQuery.getLinkedAssetEntryId());
381                    }
382    
383                    if (entryQuery.isVisible() != null) {
384                            qPos.add(entryQuery.isVisible());
385                    }
386    
387                    if (layout != null) {
388                            qPos.add(layout.getUuid());
389                    }
390    
391                    qPos.add(entryQuery.getAllTagIds());
392                    qPos.add(entryQuery.getAnyTagIds());
393                    qPos.add(entryQuery.getNotAllTagIds());
394                    qPos.add(entryQuery.getNotAnyTagIds());
395    
396                    setDates(
397                            qPos, entryQuery.getPublishDate(), entryQuery.getExpirationDate());
398    
399                    qPos.add(entryQuery.getGroupIds());
400                    qPos.add(entryQuery.getClassNameIds());
401    
402                    return q;
403            }
404    
405            protected void buildClassTypeIdsSQL(long[] classTypeIds, StringBundler sb) {
406                    sb.append(" AND (");
407    
408                    for (int i = 0; i < classTypeIds.length; i++) {
409                            sb.append(" AssetEntry.classTypeId = ");
410                            sb.append(classTypeIds[i]);
411    
412                            if ((i + 1) < classTypeIds.length) {
413                                    sb.append(" OR ");
414                            }
415                            else {
416                                    sb.append(StringPool.CLOSE_PARENTHESIS);
417                            }
418                    }
419            }
420    
421            protected void buildNotAllCategoriesSQL(
422                            long[] categoryIds, StringBundler sb)
423                    throws SystemException {
424    
425                    String sql = CustomSQLUtil.get(FIND_BY_AND_CATEGORY_IDS);
426    
427                    sb.append(" AND (");
428    
429                    for (int i = 0; i < categoryIds.length; i++) {
430                            sb.append("NOT ");
431    
432                            if (PropsValues.ASSET_CATEGORIES_SEARCH_HIERARCHICAL) {
433                                    List<Long> treeCategoryIds = AssetCategoryFinderUtil.findByG_L(
434                                            categoryIds[i]);
435    
436                                    if (treeCategoryIds.size() > 1) {
437                                            sb.append(
438                                                    StringUtil.replace(
439                                                            sql, "[$CATEGORY_ID$]",
440                                                            StringUtil.merge(treeCategoryIds)));
441    
442                                            continue;
443                                    }
444                            }
445    
446                            sb.append(
447                                    StringUtil.replace(
448                                            sql, " IN ([$CATEGORY_ID$])", " = " + categoryIds[i]));
449    
450                            if ((i + 1) < categoryIds.length) {
451                                    sb.append(" OR ");
452                            }
453                    }
454    
455                    sb.append(StringPool.CLOSE_PARENTHESIS);
456            }
457    
458            protected void buildNotAnyCategoriesSQL(
459                            long[] notCategoryIds, StringBundler sb)
460                    throws SystemException {
461    
462                    if (notCategoryIds.length == 0) {
463                            return;
464                    }
465    
466                    sb.append(" AND (NOT ");
467    
468                    String sql = CustomSQLUtil.get(FIND_BY_AND_CATEGORY_IDS);
469    
470                    String notCategoryIdsString = null;
471    
472                    if (PropsValues.ASSET_CATEGORIES_SEARCH_HIERARCHICAL) {
473                            List<Long> notCategoryIdsList = new ArrayList<Long>();
474    
475                            for (long notCategoryId : notCategoryIds) {
476                                    notCategoryIdsList.addAll(
477                                            AssetCategoryFinderUtil.findByG_L(notCategoryId));
478                            }
479    
480                            notCategoryIdsString = StringUtil.merge(notCategoryIdsList);
481                    }
482                    else {
483                            notCategoryIdsString = StringUtil.merge(notCategoryIds);
484                    }
485    
486                    sb.append(
487                            StringUtil.replace(sql, "[$CATEGORY_ID$]", notCategoryIdsString));
488                    sb.append(StringPool.CLOSE_PARENTHESIS);
489            }
490    
491            protected void buildNotAnyTagsSQL(long[][] tagIds, StringBundler sb) {
492                    sb.append(" AND (");
493    
494                    for (int i = 0; i < tagIds.length; i++) {
495                            sb.append("AssetEntry.entryId NOT IN (");
496    
497                            String sql = CustomSQLUtil.get(FIND_BY_AND_TAG_IDS);
498    
499                            sql = StringUtil.replace(
500                                    sql, "[$TAG_ID]", getTagIds(tagIds[i], StringPool.EQUAL));
501    
502                            sb.append(sql);
503    
504                            sb.append(StringPool.CLOSE_PARENTHESIS);
505    
506                            if (((i + 1) < tagIds.length) && (tagIds[i + 1].length > 0)) {
507                                    sb.append(" OR ");
508                            }
509                    }
510    
511                    sb.append(StringPool.CLOSE_PARENTHESIS);
512            }
513    
514            protected String getAnyTagIds(long[] tagIds, String operator) {
515                    StringBundler sb = new StringBundler(tagIds.length * 4 - 1);
516    
517                    for (int i = 0; i < tagIds.length; i++) {
518                            sb.append("AssetTag.tagId ");
519                            sb.append(operator);
520                            sb.append(" ? ");
521    
522                            if ((i + 1) != tagIds.length) {
523                                    sb.append("OR ");
524                            }
525                    }
526    
527                    return sb.toString();
528            }
529    
530            protected String getClassNameIds(long[] classNameIds) {
531                    if (classNameIds.length == 0) {
532                            return StringPool.BLANK;
533                    }
534    
535                    StringBundler sb = new StringBundler(classNameIds.length + 2);
536    
537                    sb.append(" AND (AssetEntry.classNameId = ?");
538    
539                    for (int i = 1; i < classNameIds.length; i++) {
540                            sb.append(" OR AssetEntry.classNameId = ? ");
541                    }
542    
543                    sb.append(") ");
544    
545                    return sb.toString();
546            }
547    
548            protected String getDates(Date publishDate, Date expirationDate) {
549                    StringBundler sb = new StringBundler(4);
550    
551                    if (publishDate != null) {
552                            sb.append(" AND (AssetEntry.publishDate IS NULL OR ");
553                            sb.append("AssetEntry.publishDate < ?)");
554                    }
555    
556                    if (expirationDate != null) {
557                            sb.append(" AND (AssetEntry.expirationDate IS NULL OR ");
558                            sb.append("AssetEntry.expirationDate > ?)");
559                    }
560    
561                    return sb.toString();
562            }
563    
564            protected String getGroupIds(long[] groupIds) {
565                    if (groupIds.length == 0) {
566                            return StringPool.BLANK;
567                    }
568    
569                    StringBundler sb = new StringBundler(groupIds.length + 2);
570    
571                    sb.append(" AND (AssetEntry.groupId = ? ");
572    
573                    for (int i = 1; i < groupIds.length; i++) {
574                            sb.append(" OR AssetEntry.groupId = ? ");
575                    }
576    
577                    sb.append(")");
578    
579                    return sb.toString();
580            }
581    
582            protected String getNotTagIds(long[] notTagIds) {
583                    if (notTagIds.length == 0) {
584                            return StringPool.BLANK;
585                    }
586    
587                    StringBundler sb = new StringBundler(notTagIds.length * 4 - 1);
588    
589                    for (int i = 0; i < notTagIds.length; i++) {
590                            sb.append("AssetEntry.entryId NOT IN (");
591                            sb.append(CustomSQLUtil.get(FIND_BY_AND_TAG_IDS));
592                            sb.append(StringPool.CLOSE_PARENTHESIS);
593    
594                            if ((i + 1) < notTagIds.length) {
595                                    sb.append(" AND ");
596                            }
597                    }
598    
599                    return sb.toString();
600            }
601    
602            protected String getTagIds(long[] tagIds, String operator) {
603                    StringBundler sb = new StringBundler(tagIds.length * 4 - 1);
604    
605                    for (int i = 0; i < tagIds.length; i++) {
606                            sb.append("tagId ");
607                            sb.append(operator);
608                            sb.append(" ? ");
609    
610                            if ((i + 1) != tagIds.length) {
611                                    sb.append("OR ");
612                            }
613                    }
614    
615                    return sb.toString();
616            }
617    
618            protected void setDates(
619                    QueryPos qPos, Date publishDate, Date expirationDate) {
620    
621                    if (publishDate != null) {
622                            Timestamp publishDate_TS = CalendarUtil.getTimestamp(publishDate);
623    
624                            qPos.add(publishDate_TS);
625                    }
626    
627                    if (expirationDate != null) {
628                            Timestamp expirationDate_TS = CalendarUtil.getTimestamp(
629                                    expirationDate);
630    
631                            qPos.add(expirationDate_TS);
632                    }
633            }
634    
635    }