1
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
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
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
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
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 }