001
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.Validator;
027 import com.liferay.portal.service.persistence.impl.BasePersistenceImpl;
028 import com.liferay.portal.util.PropsValues;
029 import com.liferay.portlet.asset.model.AssetEntry;
030 import com.liferay.portlet.asset.model.impl.AssetEntryImpl;
031 import com.liferay.util.dao.orm.CustomSQLUtil;
032
033 import java.sql.Timestamp;
034
035 import java.util.Date;
036 import java.util.Iterator;
037 import java.util.List;
038
039
043 public class AssetEntryFinderImpl
044 extends BasePersistenceImpl<AssetEntry> implements AssetEntryFinder {
045
046 public static String FIND_BY_AND_CATEGORY_IDS =
047 AssetEntryFinder.class.getName() + ".findByAndCategoryIds";
048
049 public static String FIND_BY_AND_CATEGORY_IDS_TREE =
050 AssetEntryFinder.class.getName() + ".findByAndCategoryIdsTree";
051
052 public static String FIND_BY_AND_TAG_IDS =
053 AssetEntryFinder.class.getName() + ".findByAndTagIds";
054
055 public int countEntries(AssetEntryQuery entryQuery) throws SystemException {
056 Session session = null;
057
058 try {
059 session = openSession();
060
061 SQLQuery q = buildAssetQuerySQL(entryQuery, true, session);
062
063 Iterator<Long> itr = q.list().iterator();
064
065 if (itr.hasNext()) {
066 Long count = itr.next();
067
068 if (count != null) {
069 return count.intValue();
070 }
071 }
072
073 return 0;
074 }
075 catch (Exception e) {
076 throw new SystemException(e);
077 }
078 finally {
079 closeSession(session);
080 }
081 }
082
083 public List<AssetEntry> findEntries(AssetEntryQuery entryQuery)
084 throws SystemException {
085
086 Session session = null;
087
088 try {
089 session = openSession();
090
091 SQLQuery q = buildAssetQuerySQL(entryQuery, false, session);
092
093 return (List<AssetEntry>)QueryUtil.list(
094 q, getDialect(), entryQuery.getStart(), entryQuery.getEnd());
095 }
096 catch (Exception e) {
097 throw new SystemException(e);
098 }
099 finally {
100 closeSession(session);
101 }
102 }
103
104 protected void buildAllCategoriesSQL(
105 String sqlId, long[] categoryIds, StringBundler sb) {
106
107 sb.append(" AND AssetEntry.entryId IN (");
108
109 for (int i = 0; i < categoryIds.length; i++) {
110 sb.append(CustomSQLUtil.get(sqlId));
111
112 if ((i + 1) < categoryIds.length) {
113 sb.append(" AND AssetEntry.entryId IN (");
114 }
115 }
116
117 for (int i = 0; i < categoryIds.length; i++) {
118 if ((i + 1) < categoryIds.length) {
119 sb.append(StringPool.CLOSE_PARENTHESIS);
120 }
121 }
122
123 sb.append(StringPool.CLOSE_PARENTHESIS);
124 }
125
126 protected void buildAllTagsSQL(long[] tagIds, StringBundler sb) {
127 sb.append(" AND AssetEntry.entryId IN (");
128
129 for (int i = 0; i < tagIds.length; i++) {
130 sb.append(CustomSQLUtil.get(FIND_BY_AND_TAG_IDS));
131
132 if ((i + 1) < tagIds.length) {
133 sb.append(" AND AssetEntry.entryId IN (");
134 }
135 }
136
137 for (int i = 0; i < tagIds.length; i++) {
138 if ((i + 1) < tagIds.length) {
139 sb.append(StringPool.CLOSE_PARENTHESIS);
140 }
141 }
142
143 sb.append(StringPool.CLOSE_PARENTHESIS);
144 }
145
146 protected SQLQuery buildAssetQuerySQL(
147 AssetEntryQuery entryQuery, boolean count, Session session) {
148
149 StringBundler sb = new StringBundler();
150
151 if (count) {
152 sb.append("SELECT COUNT(AssetEntry.entryId) AS COUNT_VALUE ");
153 }
154 else {
155 sb.append("SELECT DISTINCT {AssetEntry.*} ");
156 }
157
158 sb.append("FROM AssetEntry ");
159
160 if (entryQuery.getAnyTagIds().length > 0) {
161 sb.append("INNER JOIN ");
162 sb.append("AssetEntries_AssetTags ON ");
163 sb.append("(AssetEntries_AssetTags.entryId = ");
164 sb.append("AssetEntry.entryId) ");
165 sb.append("INNER JOIN ");
166 sb.append("AssetTag ON ");
167 sb.append("(AssetTag.tagId = AssetEntries_AssetTags.tagId) ");
168 }
169
170 if (entryQuery.getAnyCategoryIds().length > 0) {
171 sb.append("INNER JOIN ");
172 sb.append("AssetEntries_AssetCategories ON ");
173 sb.append("(AssetEntries_AssetCategories.entryId = ");
174 sb.append("AssetEntry.entryId) ");
175 sb.append("INNER JOIN ");
176 sb.append("AssetCategory ON ");
177 sb.append("(AssetCategory.categoryId = ");
178 sb.append("AssetEntries_AssetCategories.categoryId) ");
179 }
180
181 sb.append("WHERE ");
182
183 int whereIndex = sb.index();
184
185 if (entryQuery.isVisible() != null) {
186 sb.append(" AND (visible = ?)");
187 }
188
189 if (entryQuery.isExcludeZeroViewCount()) {
190 sb.append(" AND (AssetEntry.viewCount > 0)");
191 }
192
193
194
195 if (entryQuery.getAllCategoryIds().length > 0) {
196 if (PropsValues.ASSET_CATEGORIES_SEARCH_HIERARCHICAL) {
197 buildAllCategoriesSQL(
198 FIND_BY_AND_CATEGORY_IDS_TREE,
199 entryQuery.getAllCategoryIds(), sb);
200 }
201 else {
202 buildAllCategoriesSQL(
203 FIND_BY_AND_CATEGORY_IDS, entryQuery.getAllCategoryIds(),
204 sb);
205 }
206 }
207
208 if (entryQuery.getAnyCategoryIds().length > 0) {
209 sb.append(" AND (");
210 sb.append(getCategoryIds(
211 entryQuery.getAnyCategoryIds(), StringPool.EQUAL));
212 sb.append(") ");
213 }
214
215 if (entryQuery.getNotAllCategoryIds().length > 0) {
216 if (PropsValues.ASSET_CATEGORIES_SEARCH_HIERARCHICAL) {
217 buildNotAnyCategoriesSQL(
218 FIND_BY_AND_CATEGORY_IDS_TREE,
219 entryQuery.getNotAllCategoryIds(), sb);
220 }
221 else {
222 buildNotAnyCategoriesSQL(
223 FIND_BY_AND_CATEGORY_IDS, entryQuery.getNotAllCategoryIds(),
224 sb);
225 }
226 }
227
228 if (entryQuery.getNotAnyCategoryIds().length > 0) {
229 sb.append(" AND (");
230
231 if (PropsValues.ASSET_CATEGORIES_SEARCH_HIERARCHICAL) {
232 sb.append(
233 getNotCategoryIds(
234 FIND_BY_AND_CATEGORY_IDS_TREE,
235 entryQuery.getNotAnyCategoryIds()));
236 }
237 else {
238 sb.append(
239 getNotCategoryIds(
240 FIND_BY_AND_CATEGORY_IDS,
241 entryQuery.getNotAnyCategoryIds()));
242 }
243
244 sb.append(") ");
245 }
246
247
248
249 if (entryQuery.getAllTagIds().length > 0) {
250 buildAllTagsSQL(entryQuery.getAllTagIds(), sb);
251 }
252
253 if (entryQuery.getAnyTagIds().length > 0) {
254 sb.append(" AND (");
255 sb.append(getTagIds(entryQuery.getAnyTagIds(), StringPool.EQUAL));
256 sb.append(") ");
257 }
258
259 if (entryQuery.getNotAllTagIds().length > 0) {
260 buildNotAnyTagsSQL(entryQuery.getNotAllTagIds(), sb);
261 }
262
263 if (entryQuery.getNotAnyTagIds().length > 0) {
264 sb.append(" AND (");
265 sb.append(getNotTagIds(entryQuery.getNotAnyTagIds()));
266 sb.append(") ");
267 }
268
269
270
271 int datesIndex = sb.index();
272
273 sb.append("[$DATES$]");
274 sb.append(getGroupIds(entryQuery.getGroupIds()));
275 sb.append(getClassNameIds(entryQuery.getClassNameIds()));
276
277 if (!count) {
278 sb.append(" ORDER BY AssetEntry.");
279 sb.append(entryQuery.getOrderByCol1());
280 sb.append(StringPool.SPACE);
281 sb.append(entryQuery.getOrderByType1());
282
283 if (Validator.isNotNull(entryQuery.getOrderByCol2()) &&
284 !entryQuery.getOrderByCol1().equals(
285 entryQuery.getOrderByCol2())) {
286
287 sb.append(", AssetEntry.");
288 sb.append(entryQuery.getOrderByCol2());
289 sb.append(StringPool.SPACE);
290 sb.append(entryQuery.getOrderByType2());
291 }
292 }
293
294 sb.setStringAt(
295 getDates(
296 entryQuery.getPublishDate(), entryQuery.getExpirationDate()),
297 datesIndex);
298
299 if (sb.index() > whereIndex) {
300 String where = sb.stringAt(whereIndex);
301
302 if (where.startsWith(" AND")) {
303 sb.setStringAt(where.substring(4), whereIndex);
304 }
305 }
306
307 String sql = sb.toString();
308
309 SQLQuery q = session.createSQLQuery(sql);
310
311 if (count) {
312 q.addScalar(COUNT_COLUMN_NAME, Type.LONG);
313 }
314 else {
315 q.addEntity("AssetEntry", AssetEntryImpl.class);
316 }
317
318 QueryPos qPos = QueryPos.getInstance(q);
319
320 if (entryQuery.isVisible() != null) {
321 qPos.add(entryQuery.isVisible().booleanValue());
322 }
323
324 if (PropsValues.ASSET_CATEGORIES_SEARCH_HIERARCHICAL) {
325 qPos.add(entryQuery.getAllLeftAndRightCategoryIds());
326 qPos.add(entryQuery.getAnyLeftAndRightCategoryIds());
327 qPos.add(entryQuery.getNotAllLeftAndRightCategoryIds());
328 qPos.add(entryQuery.getNotAnyLeftAndRightCategoryIds());
329 }
330 else {
331 qPos.add(entryQuery.getAllCategoryIds());
332 qPos.add(entryQuery.getAnyCategoryIds());
333 qPos.add(entryQuery.getNotAllCategoryIds());
334 qPos.add(entryQuery.getNotAnyCategoryIds());
335 }
336
337 qPos.add(entryQuery.getAllTagIds());
338 qPos.add(entryQuery.getAnyTagIds());
339 qPos.add(entryQuery.getNotAllTagIds());
340 qPos.add(entryQuery.getNotAnyTagIds());
341
342 setDates(
343 qPos, entryQuery.getPublishDate(),
344 entryQuery.getExpirationDate());
345
346 qPos.add(entryQuery.getGroupIds());
347 qPos.add(entryQuery.getClassNameIds());
348
349 return q;
350 }
351
352 protected void buildNotAnyCategoriesSQL(
353 String sqlId, long[] categoryIds, StringBundler sb) {
354
355 sb.append(" AND (");
356
357 for (int i = 0; i < categoryIds.length; i++) {
358 sb.append("AssetEntry.entryId NOT IN (");
359 sb.append(CustomSQLUtil.get(sqlId));
360 sb.append(StringPool.CLOSE_PARENTHESIS);
361
362 if ((i + 1) < categoryIds.length) {
363 sb.append(" OR ");
364 }
365 }
366
367 sb.append(StringPool.CLOSE_PARENTHESIS);
368 }
369
370 protected void buildNotAnyTagsSQL(long[] tagIds, StringBundler sb) {
371 sb.append(" AND (");
372
373 for (int i = 0; i < tagIds.length; i++) {
374 sb.append("AssetEntry.entryId NOT IN (");
375 sb.append(CustomSQLUtil.get(FIND_BY_AND_TAG_IDS));
376 sb.append(StringPool.CLOSE_PARENTHESIS);
377
378 if ((i + 1) < tagIds.length) {
379 sb.append(" OR ");
380 }
381 }
382
383 sb.append(StringPool.CLOSE_PARENTHESIS);
384 }
385
386 protected String getCategoryIds(long[] categoryIds, String operator) {
387 StringBundler sb = new StringBundler();
388
389 for (int i = 0; i < categoryIds.length; i++) {
390 if (PropsValues.ASSET_CATEGORIES_SEARCH_HIERARCHICAL) {
391 sb.append("AssetCategory.leftCategoryId BETWEEN ? AND ?");
392 }
393 else {
394 sb.append("AssetCategory.categoryId ");
395 sb.append(operator);
396 sb.append(" ?");
397 }
398
399 if ((i + 1) != categoryIds.length) {
400 sb.append(" OR ");
401 }
402 }
403
404 return sb.toString();
405 }
406
407 protected String getClassNameIds(long[] classNameIds) {
408 if (classNameIds.length == 0) {
409 return StringPool.BLANK;
410 }
411
412 StringBundler sb = new StringBundler(classNameIds.length + 2);
413
414 sb.append(" AND (classNameId = ?");
415
416 for (int i = 1; i < classNameIds.length; i++) {
417 sb.append(" OR classNameId = ? ");
418 }
419
420 sb.append(") ");
421
422 return sb.toString();
423 }
424
425 protected String getDates(Date publishDate, Date expirationDate) {
426 StringBundler sb = new StringBundler(2);
427
428 if (publishDate != null) {
429 sb.append(" AND (publishDate IS NULL OR publishDate < ?)");
430 }
431
432 if (expirationDate != null) {
433 sb.append(" AND (expirationDate IS NULL OR expirationDate > ?)");
434 }
435
436 return sb.toString();
437 }
438
439 protected String getGroupIds(long[] groupIds) {
440 if (groupIds.length == 0) {
441 return StringPool.BLANK;
442 }
443
444 StringBundler sb = new StringBundler(groupIds.length + 2);
445
446 sb.append(" AND (AssetEntry.groupId = ? ");
447
448 for (int i = 1; i < groupIds.length; i++) {
449 sb.append(" OR AssetEntry.groupId = ? ");
450 }
451
452 sb.append(")");
453
454 return sb.toString();
455 }
456
457 protected String getNotCategoryIds(String sqlId, long[] notCategoryIds) {
458 if (notCategoryIds.length == 0) {
459 return StringPool.BLANK;
460 }
461
462 StringBundler sb = new StringBundler(notCategoryIds.length * 4 - 1);
463
464 for (int i = 0; i < notCategoryIds.length; i++) {
465 sb.append("AssetEntry.entryId NOT IN (");
466 sb.append(CustomSQLUtil.get(sqlId));
467 sb.append(StringPool.CLOSE_PARENTHESIS);
468
469 if ((i + 1) < notCategoryIds.length) {
470 sb.append(" AND ");
471 }
472 }
473
474 return sb.toString();
475 }
476
477 protected String getNotTagIds(long[] notTagIds) {
478 if (notTagIds.length == 0) {
479 return StringPool.BLANK;
480 }
481
482 StringBundler sb = new StringBundler(notTagIds.length * 4 - 1);
483
484 for (int i = 0; i < notTagIds.length; i++) {
485 sb.append("AssetEntry.entryId NOT IN (");
486 sb.append(CustomSQLUtil.get(FIND_BY_AND_TAG_IDS));
487 sb.append(StringPool.CLOSE_PARENTHESIS);
488
489 if ((i + 1) < notTagIds.length) {
490 sb.append(" AND ");
491 }
492 }
493
494 return sb.toString();
495 }
496
497 protected String getTagIds(long[] tagIds, String operator) {
498 StringBundler sb = new StringBundler(tagIds.length * 4 - 1);
499
500 for (int i = 0; i < tagIds.length; i++) {
501 sb.append("AssetTag.tagId ");
502 sb.append(operator);
503 sb.append(" ? ");
504
505 if ((i + 1) != tagIds.length) {
506 sb.append("OR ");
507 }
508 }
509
510 return sb.toString();
511 }
512
513 protected void setDates(
514 QueryPos qPos, Date publishDate, Date expirationDate) {
515
516 if (publishDate != null) {
517 Timestamp publishDate_TS = CalendarUtil.getTimestamp(publishDate);
518
519 qPos.add(publishDate_TS);
520 }
521
522 if (expirationDate != null) {
523 Timestamp expirationDate_TS =
524 CalendarUtil.getTimestamp(expirationDate);
525
526 qPos.add(expirationDate_TS);
527 }
528 }
529
530 }