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