1   /**
2    * Copyright (c) 2000-2010 Liferay, Inc. All rights reserved.
3    *
4    * This library is free software; you can redistribute it and/or modify it under
5    * the terms of the GNU Lesser General Public License as published by the Free
6    * Software Foundation; either version 2.1 of the License, or (at your option)
7    * any later version.
8    *
9    * This library is distributed in the hope that it will be useful, but WITHOUT
10   * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
11   * FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more
12   * details.
13   */
14  
15  package com.liferay.portlet.asset.service.persistence;
16  
17  import com.liferay.portal.kernel.dao.orm.QueryPos;
18  import com.liferay.portal.kernel.dao.orm.QueryUtil;
19  import com.liferay.portal.kernel.dao.orm.SQLQuery;
20  import com.liferay.portal.kernel.dao.orm.Session;
21  import com.liferay.portal.kernel.dao.orm.Type;
22  import com.liferay.portal.kernel.exception.SystemException;
23  import com.liferay.portal.kernel.util.CalendarUtil;
24  import com.liferay.portal.kernel.util.StringBundler;
25  import com.liferay.portal.kernel.util.StringPool;
26  import com.liferay.portal.kernel.util.Validator;
27  import com.liferay.portal.service.persistence.impl.BasePersistenceImpl;
28  import com.liferay.portal.util.PropsValues;
29  import com.liferay.portlet.asset.model.AssetEntry;
30  import com.liferay.portlet.asset.model.impl.AssetEntryImpl;
31  import com.liferay.util.dao.orm.CustomSQLUtil;
32  
33  import java.sql.Timestamp;
34  
35  import java.util.Date;
36  import java.util.Iterator;
37  import java.util.List;
38  
39  /**
40   * <a href="AssetEntryFinderImpl.java.html"><b><i>View Source</i></b></a>
41   *
42   * @author Brian Wing Shun Chan
43   * @author Jorge Ferrer
44   */
45  public class AssetEntryFinderImpl
46      extends BasePersistenceImpl<AssetEntry> implements AssetEntryFinder {
47  
48      public static String FIND_BY_AND_CATEGORY_IDS =
49          AssetEntryFinder.class.getName() + ".findByAndCategoryIds";
50  
51      public static String FIND_BY_AND_CATEGORY_IDS_TREE =
52          AssetEntryFinder.class.getName() + ".findByAndCategoryIdsTree";
53  
54      public static String FIND_BY_AND_TAG_IDS =
55          AssetEntryFinder.class.getName() + ".findByAndTagIds";
56  
57      public int countEntries(AssetEntryQuery entryQuery) throws SystemException {
58          Session session = null;
59  
60          try {
61              session = openSession();
62  
63              SQLQuery q = buildAssetQuerySQL(entryQuery, true, session);
64  
65              Iterator<Long> itr = q.list().iterator();
66  
67              if (itr.hasNext()) {
68                  Long count = itr.next();
69  
70                  if (count != null) {
71                      return count.intValue();
72                  }
73              }
74  
75              return 0;
76          }
77          catch (Exception e) {
78              throw new SystemException(e);
79          }
80          finally {
81              closeSession(session);
82          }
83      }
84  
85      public List<AssetEntry> findEntries(AssetEntryQuery entryQuery)
86          throws SystemException {
87  
88          Session session = null;
89  
90          try {
91              session = openSession();
92  
93              SQLQuery q = buildAssetQuerySQL(entryQuery, false, session);
94  
95              return (List<AssetEntry>)QueryUtil.list(
96                  q, getDialect(), entryQuery.getStart(), entryQuery.getEnd());
97          }
98          catch (Exception e) {
99              throw new SystemException(e);
100         }
101         finally {
102             closeSession(session);
103         }
104     }
105 
106     protected void buildAllCategoriesSQL(
107         String sqlId, long[] categoryIds, StringBundler sb) {
108 
109         sb.append(" AND AssetEntry.entryId IN (");
110 
111         for (int i = 0; i < categoryIds.length; i++) {
112             sb.append(CustomSQLUtil.get(sqlId));
113 
114             if ((i + 1) < categoryIds.length) {
115                 sb.append(" AND AssetEntry.entryId IN (");
116             }
117         }
118 
119         for (int i = 0; i < categoryIds.length; i++) {
120             if ((i + 1) < categoryIds.length) {
121                 sb.append(StringPool.CLOSE_PARENTHESIS);
122             }
123         }
124 
125         sb.append(StringPool.CLOSE_PARENTHESIS);
126     }
127 
128     protected void buildAllTagsSQL(long[] tagIds, StringBundler sb) {
129         sb.append(" AND AssetEntry.entryId IN (");
130 
131         for (int i = 0; i < tagIds.length; i++) {
132             sb.append(CustomSQLUtil.get(FIND_BY_AND_TAG_IDS));
133 
134             if ((i + 1) < tagIds.length) {
135                 sb.append(" AND AssetEntry.entryId IN (");
136             }
137         }
138 
139         for (int i = 0; i < tagIds.length; i++) {
140             if ((i + 1) < tagIds.length) {
141                 sb.append(StringPool.CLOSE_PARENTHESIS);
142             }
143         }
144 
145         sb.append(StringPool.CLOSE_PARENTHESIS);
146     }
147 
148     protected SQLQuery buildAssetQuerySQL(
149         AssetEntryQuery entryQuery, boolean count, Session session) {
150 
151         StringBundler sb = new StringBundler();
152 
153         if (count) {
154             sb.append("SELECT COUNT(AssetEntry.entryId) AS COUNT_VALUE ");
155         }
156         else {
157             sb.append("SELECT DISTINCT {AssetEntry.*} ");
158         }
159 
160         sb.append("FROM AssetEntry ");
161 
162         if (entryQuery.getAnyTagIds().length > 0) {
163             sb.append("INNER JOIN ");
164             sb.append("AssetEntries_AssetTags ON ");
165             sb.append("(AssetEntries_AssetTags.entryId = ");
166             sb.append("AssetEntry.entryId) ");
167             sb.append("INNER JOIN ");
168             sb.append("AssetTag ON ");
169             sb.append("(AssetTag.tagId = AssetEntries_AssetTags.tagId) ");
170         }
171 
172         if (entryQuery.getAnyCategoryIds().length > 0) {
173             sb.append("INNER JOIN ");
174             sb.append("AssetEntries_AssetCategories ON ");
175             sb.append("(AssetEntries_AssetCategories.entryId = ");
176             sb.append("AssetEntry.entryId) ");
177             sb.append("INNER JOIN ");
178             sb.append("AssetCategory ON ");
179             sb.append("(AssetCategory.categoryId = ");
180             sb.append("AssetEntries_AssetCategories.categoryId) ");
181         }
182 
183         sb.append("WHERE ");
184 
185         int whereIndex = sb.index();
186 
187         if (entryQuery.isVisible() != null) {
188             sb.append(" AND (visible = ?)");
189         }
190 
191         if (entryQuery.isExcludeZeroViewCount()) {
192             sb.append(" AND (AssetEntry.viewCount > 0)");
193         }
194 
195         // Category conditions
196 
197         if (entryQuery.getAllCategoryIds().length > 0) {
198             if (PropsValues.ASSET_CATEGORIES_SEARCH_HIERARCHICAL) {
199                 buildAllCategoriesSQL(
200                     FIND_BY_AND_CATEGORY_IDS_TREE,
201                     entryQuery.getAllCategoryIds(), sb);
202             }
203             else {
204                 buildAllCategoriesSQL(
205                     FIND_BY_AND_CATEGORY_IDS, entryQuery.getAllCategoryIds(),
206                     sb);
207             }
208         }
209 
210         if (entryQuery.getAnyCategoryIds().length > 0) {
211             sb.append(" AND (");
212             sb.append(getCategoryIds(
213                 entryQuery.getAnyCategoryIds(), StringPool.EQUAL));
214             sb.append(") ");
215         }
216 
217         if (entryQuery.getNotAllCategoryIds().length > 0) {
218             if (PropsValues.ASSET_CATEGORIES_SEARCH_HIERARCHICAL) {
219                 buildNotAnyCategoriesSQL(
220                     FIND_BY_AND_CATEGORY_IDS_TREE,
221                     entryQuery.getNotAllCategoryIds(), sb);
222             }
223             else {
224                 buildNotAnyCategoriesSQL(
225                     FIND_BY_AND_CATEGORY_IDS, entryQuery.getNotAllCategoryIds(),
226                     sb);
227             }
228         }
229 
230         if (entryQuery.getNotAnyCategoryIds().length > 0) {
231             sb.append(" AND (");
232 
233             if (PropsValues.ASSET_CATEGORIES_SEARCH_HIERARCHICAL) {
234                 sb.append(
235                     getNotCategoryIds(
236                         FIND_BY_AND_CATEGORY_IDS_TREE,
237                         entryQuery.getNotAnyCategoryIds()));
238             }
239             else {
240                 sb.append(
241                     getNotCategoryIds(
242                         FIND_BY_AND_CATEGORY_IDS,
243                         entryQuery.getNotAnyCategoryIds()));
244             }
245 
246             sb.append(") ");
247         }
248 
249         // Tag conditions
250 
251         if (entryQuery.getAllTagIds().length > 0) {
252             buildAllTagsSQL(entryQuery.getAllTagIds(), sb);
253         }
254 
255         if (entryQuery.getAnyTagIds().length > 0) {
256             sb.append(" AND (");
257             sb.append(getTagIds(entryQuery.getAnyTagIds(), StringPool.EQUAL));
258             sb.append(") ");
259         }
260 
261         if (entryQuery.getNotAllTagIds().length > 0) {
262             buildNotAnyTagsSQL(entryQuery.getNotAllTagIds(), sb);
263         }
264 
265         if (entryQuery.getNotAnyTagIds().length > 0) {
266             sb.append(" AND (");
267             sb.append(getNotTagIds(entryQuery.getNotAnyTagIds()));
268             sb.append(") ");
269         }
270 
271         // Other conditions
272 
273         int datesIndex = sb.index();
274 
275         sb.append("[$DATES$]");
276         sb.append(getGroupIds(entryQuery.getGroupIds()));
277         sb.append(getClassNameIds(entryQuery.getClassNameIds()));
278 
279         if (!count) {
280             sb.append(" ORDER BY AssetEntry.");
281             sb.append(entryQuery.getOrderByCol1());
282             sb.append(StringPool.SPACE);
283             sb.append(entryQuery.getOrderByType1());
284 
285             if (Validator.isNotNull(entryQuery.getOrderByCol2()) &&
286                 !entryQuery.getOrderByCol1().equals(
287                     entryQuery.getOrderByCol2())) {
288 
289                 sb.append(", AssetEntry.");
290                 sb.append(entryQuery.getOrderByCol2());
291                 sb.append(StringPool.SPACE);
292                 sb.append(entryQuery.getOrderByType2());
293             }
294         }
295 
296         sb.setStringAt(
297             getDates(
298                 entryQuery.getPublishDate(), entryQuery.getExpirationDate()),
299             datesIndex);
300 
301         if (sb.index() > whereIndex) {
302             String where = sb.stringAt(whereIndex);
303 
304             if (where.startsWith(" AND")) {
305                 sb.setStringAt(where.substring(4), whereIndex);
306             }
307         }
308 
309         String sql = sb.toString();
310 
311         SQLQuery q = session.createSQLQuery(sql);
312 
313         if (count) {
314             q.addScalar(COUNT_COLUMN_NAME, Type.LONG);
315         }
316         else {
317             q.addEntity("AssetEntry", AssetEntryImpl.class);
318         }
319 
320         QueryPos qPos = QueryPos.getInstance(q);
321 
322         if (entryQuery.isVisible() != null) {
323             qPos.add(entryQuery.isVisible().booleanValue());
324         }
325 
326         if (PropsValues.ASSET_CATEGORIES_SEARCH_HIERARCHICAL) {
327             qPos.add(entryQuery.getAllLeftAndRightCategoryIds());
328             qPos.add(entryQuery.getAnyLeftAndRightCategoryIds());
329             qPos.add(entryQuery.getNotAllLeftAndRightCategoryIds());
330             qPos.add(entryQuery.getNotAnyLeftAndRightCategoryIds());
331         }
332         else {
333             qPos.add(entryQuery.getAllCategoryIds());
334             qPos.add(entryQuery.getAnyCategoryIds());
335             qPos.add(entryQuery.getNotAllCategoryIds());
336             qPos.add(entryQuery.getNotAnyCategoryIds());
337         }
338 
339         qPos.add(entryQuery.getAllTagIds());
340         qPos.add(entryQuery.getAnyTagIds());
341         qPos.add(entryQuery.getNotAllTagIds());
342         qPos.add(entryQuery.getNotAnyTagIds());
343 
344         setDates(
345             qPos, entryQuery.getPublishDate(),
346             entryQuery.getExpirationDate());
347 
348         qPos.add(entryQuery.getGroupIds());
349         qPos.add(entryQuery.getClassNameIds());
350 
351         return q;
352     }
353 
354     protected void buildNotAnyCategoriesSQL(
355         String sqlId, long[] categoryIds, StringBundler sb) {
356 
357         sb.append(" AND (");
358 
359         for (int i = 0; i < categoryIds.length; i++) {
360             sb.append("AssetEntry.entryId NOT IN (");
361             sb.append(CustomSQLUtil.get(sqlId));
362             sb.append(StringPool.CLOSE_PARENTHESIS);
363 
364             if ((i + 1) < categoryIds.length) {
365                 sb.append(" OR ");
366             }
367         }
368 
369         sb.append(StringPool.CLOSE_PARENTHESIS);
370     }
371 
372     protected void buildNotAnyTagsSQL(long[] tagIds, StringBundler sb) {
373         sb.append(" AND (");
374 
375         for (int i = 0; i < tagIds.length; i++) {
376             sb.append("AssetEntry.entryId NOT IN (");
377             sb.append(CustomSQLUtil.get(FIND_BY_AND_TAG_IDS));
378             sb.append(StringPool.CLOSE_PARENTHESIS);
379 
380             if ((i + 1) < tagIds.length) {
381                 sb.append(" OR ");
382             }
383         }
384 
385         sb.append(StringPool.CLOSE_PARENTHESIS);
386     }
387 
388     protected String getCategoryIds(long[] categoryIds, String operator) {
389         StringBundler sb = new StringBundler();
390 
391         for (int i = 0; i < categoryIds.length; i++) {
392             if (PropsValues.ASSET_CATEGORIES_SEARCH_HIERARCHICAL) {
393                 sb.append("AssetCategory.leftCategoryId BETWEEN ? AND ?");
394             }
395             else {
396                 sb.append("AssetCategory.categoryId ");
397                 sb.append(operator);
398                 sb.append(" ? ");
399             }
400 
401             if ((i + 1) != categoryIds.length) {
402                 sb.append("OR ");
403             }
404         }
405 
406         return sb.toString();
407     }
408 
409     protected String getClassNameIds(long[] classNameIds) {
410         if (classNameIds.length == 0) {
411             return StringPool.BLANK;
412         }
413 
414         StringBundler sb = new StringBundler(classNameIds.length + 2);
415 
416         sb.append(" AND (classNameId = ?");
417 
418         for (int i = 1; i < classNameIds.length; i++) {
419             sb.append(" OR classNameId = ? ");
420         }
421 
422         sb.append(") ");
423 
424         return sb.toString();
425     }
426 
427     protected String getDates(Date publishDate, Date expirationDate) {
428         StringBundler sb = new StringBundler(2);
429 
430         if (publishDate != null) {
431             sb.append(" AND (publishDate IS NULL OR publishDate < ?)");
432         }
433 
434         if (expirationDate != null) {
435             sb.append(" AND (expirationDate IS NULL OR expirationDate > ?)");
436         }
437 
438         return sb.toString();
439     }
440 
441     protected String getGroupIds(long[] groupIds) {
442         if (groupIds.length == 0) {
443             return StringPool.BLANK;
444         }
445 
446         StringBundler sb = new StringBundler(groupIds.length + 2);
447 
448         sb.append(" AND (AssetEntry.groupId = ? ");
449 
450         for (int i = 1; i < groupIds.length; i++) {
451             sb.append(" OR AssetEntry.groupId = ? ");
452         }
453 
454         sb.append(")");
455 
456         return sb.toString();
457     }
458 
459     protected String getNotCategoryIds(String sqlId, long[] notCategoryIds) {
460         if (notCategoryIds.length == 0) {
461             return StringPool.BLANK;
462         }
463 
464         StringBundler sb = new StringBundler(notCategoryIds.length * 4 - 1);
465 
466         for (int i = 0; i < notCategoryIds.length; i++) {
467             sb.append("AssetEntry.entryId NOT IN (");
468             sb.append(CustomSQLUtil.get(sqlId));
469             sb.append(StringPool.CLOSE_PARENTHESIS);
470 
471             if ((i + 1) < notCategoryIds.length) {
472                 sb.append(" AND ");
473             }
474         }
475 
476         return sb.toString();
477     }
478 
479     protected String getNotTagIds(long[] notTagIds) {
480         if (notTagIds.length == 0) {
481             return StringPool.BLANK;
482         }
483 
484         StringBundler sb = new StringBundler(notTagIds.length * 4 - 1);
485 
486         for (int i = 0; i < notTagIds.length; i++) {
487             sb.append("AssetEntry.entryId NOT IN (");
488             sb.append(CustomSQLUtil.get(FIND_BY_AND_TAG_IDS));
489             sb.append(StringPool.CLOSE_PARENTHESIS);
490 
491             if ((i + 1) < notTagIds.length) {
492                 sb.append(" AND ");
493             }
494         }
495 
496         return sb.toString();
497     }
498 
499     protected String getTagIds(long[] tagIds, String operator) {
500         StringBundler sb = new StringBundler(tagIds.length * 4 - 1);
501 
502         for (int i = 0; i < tagIds.length; i++) {
503             sb.append("AssetTag.tagId ");
504             sb.append(operator);
505             sb.append(" ? ");
506 
507             if ((i + 1) != tagIds.length) {
508                 sb.append("OR ");
509             }
510         }
511 
512         return sb.toString();
513     }
514 
515     protected void setDates(
516         QueryPos qPos, Date publishDate, Date expirationDate) {
517 
518         if (publishDate != null) {
519             Timestamp publishDate_TS = CalendarUtil.getTimestamp(publishDate);
520 
521             qPos.add(publishDate_TS);
522         }
523 
524         if (expirationDate != null) {
525             Timestamp expirationDate_TS =
526                 CalendarUtil.getTimestamp(expirationDate);
527 
528             qPos.add(expirationDate_TS);
529         }
530     }
531 
532 }