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