001    /**
002     * Copyright (c) 2000-present Liferay, Inc. All rights reserved.
003     *
004     * This library is free software; you can redistribute it and/or modify it under
005     * the terms of the GNU Lesser General Public License as published by the Free
006     * Software Foundation; either version 2.1 of the License, or (at your option)
007     * any later version.
008     *
009     * This library is distributed in the hope that it will be useful, but WITHOUT
010     * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
011     * FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more
012     * details.
013     */
014    
015    package com.liferay.portlet.asset.service.persistence.impl;
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.CharPool;
025    import com.liferay.portal.kernel.util.ListUtil;
026    import com.liferay.portal.kernel.util.StringBundler;
027    import com.liferay.portal.kernel.util.StringPool;
028    import com.liferay.portal.kernel.util.StringUtil;
029    import com.liferay.portal.kernel.util.Validator;
030    import com.liferay.portal.model.Layout;
031    import com.liferay.portal.service.persistence.impl.BasePersistenceImpl;
032    import com.liferay.portal.util.PropsValues;
033    import com.liferay.portlet.asset.model.AssetCategory;
034    import com.liferay.portlet.asset.model.AssetEntry;
035    import com.liferay.portlet.asset.model.impl.AssetEntryImpl;
036    import com.liferay.portlet.asset.service.persistence.AssetCategoryUtil;
037    import com.liferay.portlet.asset.service.persistence.AssetEntryFinder;
038    import com.liferay.portlet.asset.service.persistence.AssetEntryQuery;
039    import com.liferay.util.dao.orm.CustomSQLUtil;
040    
041    import java.sql.Timestamp;
042    
043    import java.util.ArrayList;
044    import java.util.Collections;
045    import java.util.Date;
046    import java.util.Iterator;
047    import java.util.List;
048    
049    /**
050     * @author Brian Wing Shun Chan
051     * @author Jorge Ferrer
052     * @author Shuyang Zhou
053     */
054    public class AssetEntryFinderImpl
055            extends BasePersistenceImpl<AssetEntry> implements AssetEntryFinder {
056    
057            public static final String FIND_BY_AND_CATEGORY_IDS =
058                    AssetEntryFinder.class.getName() + ".findByAndCategoryIds";
059    
060            public static final String FIND_BY_AND_TAG_IDS =
061                    AssetEntryFinder.class.getName() + ".findByAndTagIds";
062    
063            @Override
064            public int countEntries(AssetEntryQuery entryQuery) {
065                    Session session = null;
066    
067                    try {
068                            session = openSession();
069    
070                            SQLQuery q = buildAssetQuerySQL(entryQuery, true, session);
071    
072                            Iterator<Long> itr = q.iterate();
073    
074                            if (itr.hasNext()) {
075                                    Long count = itr.next();
076    
077                                    if (count != null) {
078                                            return count.intValue();
079                                    }
080                            }
081    
082                            return 0;
083                    }
084                    catch (Exception e) {
085                            throw new SystemException(e);
086                    }
087                    finally {
088                            closeSession(session);
089                    }
090            }
091    
092            @Override
093            public List<AssetEntry> findEntries(AssetEntryQuery entryQuery) {
094                    Session session = null;
095    
096                    try {
097                            session = openSession();
098    
099                            SQLQuery q = buildAssetQuerySQL(entryQuery, false, session);
100    
101                            return (List<AssetEntry>)QueryUtil.list(
102                                    q, getDialect(), entryQuery.getStart(), entryQuery.getEnd());
103                    }
104                    catch (Exception e) {
105                            throw new SystemException(e);
106                    }
107                    finally {
108                            closeSession(session);
109                    }
110            }
111    
112            protected void buildAllCategoriesSQL(long[] categoryIds, StringBundler sb) {
113                    String findByAndCategoryIdsSQL = CustomSQLUtil.get(
114                            FIND_BY_AND_CATEGORY_IDS);
115    
116                    sb.append(" AND (");
117    
118                    for (int i = 0; i < categoryIds.length; i++) {
119                            String sql = null;
120    
121                            if (PropsValues.ASSET_CATEGORIES_SEARCH_HIERARCHICAL) {
122                                    List<Long> treeCategoryIds = getSubcategoryIds(categoryIds[i]);
123    
124                                    if (treeCategoryIds.size() > 1) {
125                                            sql = StringUtil.replace(
126                                                    findByAndCategoryIdsSQL, "[$CATEGORY_ID$]",
127                                                    StringUtil.merge(treeCategoryIds));
128                                    }
129                            }
130    
131                            if (sql == null) {
132                                    sql = StringUtil.replace(
133                                            findByAndCategoryIdsSQL, " IN ([$CATEGORY_ID$])",
134                                            " = " + categoryIds[i]);
135                            }
136    
137                            sb.append(sql);
138    
139                            if ((i + 1) < categoryIds.length) {
140                                    sb.append(" AND ");
141                            }
142                    }
143    
144                    sb.append(StringPool.CLOSE_PARENTHESIS);
145            }
146    
147            protected void buildAllTagsSQL(long[][] tagIds, StringBundler sb) {
148                    sb.append(" AND AssetEntry.entryId IN (");
149    
150                    for (int i = 0; i < tagIds.length; i++) {
151                            String sql = CustomSQLUtil.get(FIND_BY_AND_TAG_IDS);
152    
153                            sql = StringUtil.replace(sql, "[$TAG_ID$]", getTagIds(tagIds[i]));
154    
155                            sb.append(sql);
156    
157                            if ((i + 1) < tagIds.length) {
158                                    sb.append(" AND AssetEntry.entryId IN (");
159                            }
160                    }
161    
162                    for (int i = 0; i < tagIds.length; i++) {
163                            if ((i + 1) < tagIds.length) {
164                                    sb.append(StringPool.CLOSE_PARENTHESIS);
165                            }
166                    }
167    
168                    sb.append(StringPool.CLOSE_PARENTHESIS);
169            }
170    
171            protected void buildAnyCategoriesSQL(long[] categoryIds, StringBundler sb) {
172                    String sql = CustomSQLUtil.get(FIND_BY_AND_CATEGORY_IDS);
173    
174                    String categoryIdsString = null;
175    
176                    if (PropsValues.ASSET_CATEGORIES_SEARCH_HIERARCHICAL) {
177                            List<Long> categoryIdsList = new ArrayList<Long>();
178    
179                            for (long categoryId : categoryIds) {
180                                    categoryIdsList.addAll(getSubcategoryIds(categoryId));
181                            }
182    
183                            if (categoryIdsList.isEmpty()) {
184                                    return;
185                            }
186    
187                            categoryIdsString = StringUtil.merge(categoryIdsList);
188                    }
189                    else {
190                            categoryIdsString = StringUtil.merge(categoryIds);
191                    }
192    
193                    sb.append(" AND (");
194                    sb.append(
195                            StringUtil.replace(sql, "[$CATEGORY_ID$]", categoryIdsString));
196                    sb.append(StringPool.CLOSE_PARENTHESIS);
197            }
198    
199            protected String buildAnyTagsSQL(long[] tagIds, StringBundler sb) {
200                    sb.append(" AND (");
201    
202                    for (int i = 0; i < tagIds.length; i++) {
203                            sb.append("AssetTag.tagId = ");
204                            sb.append(tagIds[i]);
205    
206                            if ((i + 1) != tagIds.length) {
207                                    sb.append(" OR ");
208                            }
209                    }
210    
211                    sb.append(StringPool.CLOSE_PARENTHESIS);
212    
213                    return sb.toString();
214            }
215    
216            protected SQLQuery buildAssetQuerySQL(
217                    AssetEntryQuery entryQuery, boolean count, Session session) {
218    
219                    StringBundler sb = new StringBundler(64);
220    
221                    if (count) {
222                            sb.append(
223                                    "SELECT COUNT(DISTINCT AssetEntry.entryId) AS COUNT_VALUE ");
224                    }
225                    else {
226                            sb.append("SELECT {AssetEntry.*} ");
227    
228                            boolean selectRatings = false;
229    
230                            String orderByCol1 = entryQuery.getOrderByCol1();
231                            String orderByCol2 = entryQuery.getOrderByCol2();
232    
233                            if (orderByCol1.equals("ratings") ||
234                                    orderByCol2.equals("ratings")) {
235    
236                                    selectRatings = true;
237    
238                                    sb.append(", TEMP_TABLE.averageScore ");
239                            }
240    
241                            sb.append("FROM (SELECT DISTINCT AssetEntry.entryId ");
242    
243                            if (selectRatings) {
244                                    sb.append(", RatingsStats.averageScore ");
245                            }
246                    }
247    
248                    sb.append("FROM AssetEntry ");
249    
250                    if (entryQuery.getAnyTagIds().length > 0) {
251                            sb.append("INNER JOIN ");
252                            sb.append("AssetEntries_AssetTags ON ");
253                            sb.append("(AssetEntries_AssetTags.entryId = ");
254                            sb.append("AssetEntry.entryId) ");
255                            sb.append("INNER JOIN ");
256                            sb.append("AssetTag ON ");
257                            sb.append("(AssetTag.tagId = AssetEntries_AssetTags.tagId) ");
258                    }
259    
260                    if (entryQuery.getLinkedAssetEntryId() > 0) {
261                            sb.append("INNER JOIN ");
262                            sb.append("AssetLink ON ");
263                            sb.append("(AssetEntry.entryId = AssetLink.entryId1) ");
264                            sb.append("OR (AssetEntry.entryId = AssetLink.entryId2)");
265                    }
266    
267                    if (entryQuery.getOrderByCol1().equals("ratings") ||
268                            entryQuery.getOrderByCol2().equals("ratings")) {
269    
270                            sb.append(" LEFT JOIN ");
271                            sb.append("RatingsStats ON ");
272                            sb.append("(RatingsStats.classNameId = ");
273                            sb.append("AssetEntry.classNameId) AND ");
274                            sb.append("(RatingsStats.classPK = AssetEntry.classPK)");
275                    }
276    
277                    sb.append("WHERE ");
278    
279                    int whereIndex = sb.index();
280    
281                    if (entryQuery.getLinkedAssetEntryId() > 0) {
282                            sb.append(" AND ((AssetLink.entryId1 = ?) OR ");
283                            sb.append("(AssetLink.entryId2 = ?))");
284                            sb.append(" AND (AssetEntry.entryId != ?)");
285                    }
286    
287                    if (entryQuery.isVisible() != null) {
288                            sb.append(" AND (visible = ?)");
289                    }
290    
291                    if (entryQuery.isExcludeZeroViewCount()) {
292                            sb.append(" AND (AssetEntry.viewCount > 0)");
293                    }
294    
295                    // Keywords
296    
297                    if (Validator.isNotNull(entryQuery.getKeywords())) {
298                            sb.append(" AND ((AssetEntry.title LIKE ?) OR");
299                            sb.append(" (AssetEntry.description LIKE ?))");
300                    }
301                    else {
302                            if (Validator.isNotNull(entryQuery.getTitle())) {
303                                    sb.append(" AND (AssetEntry.title LIKE ?)");
304                            }
305    
306                            if (Validator.isNotNull(entryQuery.getDescription())) {
307                                    sb.append(" AND (AssetEntry.description LIKE ?)");
308                            }
309                    }
310    
311                    // Layout
312    
313                    Layout layout = entryQuery.getLayout();
314    
315                    if (layout != null) {
316                            sb.append(" AND (AssetEntry.layoutUuid = ?)");
317                    }
318    
319                    // Category conditions
320    
321                    if (entryQuery.getAllCategoryIds().length > 0) {
322                            buildAllCategoriesSQL(entryQuery.getAllCategoryIds(), sb);
323                    }
324    
325                    if (entryQuery.getAnyCategoryIds().length > 0) {
326                            buildAnyCategoriesSQL(entryQuery.getAnyCategoryIds(), sb);
327                    }
328    
329                    if (entryQuery.getNotAllCategoryIds().length > 0) {
330                            buildNotAllCategoriesSQL(entryQuery.getNotAllCategoryIds(), sb);
331                    }
332    
333                    if (entryQuery.getNotAnyCategoryIds().length > 0) {
334                            buildNotAnyCategoriesSQL(entryQuery.getNotAnyCategoryIds(), sb);
335                    }
336    
337                    // Asset entry subtypes
338    
339                    if (entryQuery.getClassTypeIds().length > 0) {
340                            buildClassTypeIdsSQL(entryQuery.getClassTypeIds(), sb);
341                    }
342    
343                    // Tag conditions
344    
345                    if (entryQuery.getAllTagIds().length > 0) {
346                            buildAllTagsSQL(entryQuery.getAllTagIdsArray(), sb);
347                    }
348    
349                    if (entryQuery.getAnyTagIds().length > 0) {
350                            buildAnyTagsSQL(entryQuery.getAnyTagIds(), sb);
351                    }
352    
353                    if (entryQuery.getNotAllTagIds().length > 0) {
354                            buildNotAllTagsSQL(entryQuery.getNotAllTagIdsArray(), sb);
355                    }
356    
357                    if (entryQuery.getNotAnyTagIds().length > 0) {
358                            buildNotAnyTagsSQL(entryQuery.getNotAnyTagIds(), sb);
359                    }
360    
361                    // Other conditions
362    
363                    sb.append(
364                            getDates(
365                                    entryQuery.getPublishDate(), entryQuery.getExpirationDate()));
366                    sb.append(getGroupIds(entryQuery.getGroupIds()));
367                    sb.append(getClassNameIds(entryQuery.getClassNameIds()));
368    
369                    if (!count) {
370                            sb.append(") TEMP_TABLE ");
371                            sb.append("INNER JOIN ");
372                            sb.append("AssetEntry AssetEntry ON ");
373                            sb.append("TEMP_TABLE.entryId = AssetEntry.entryId");
374    
375                            sb.append(" ORDER BY ");
376    
377                            if (entryQuery.getOrderByCol1().equals("ratings")) {
378                                    sb.append("TEMP_TABLE.averageScore");
379                            }
380                            else {
381                                    sb.append("AssetEntry.");
382                                    sb.append(entryQuery.getOrderByCol1());
383                            }
384    
385                            sb.append(StringPool.SPACE);
386                            sb.append(entryQuery.getOrderByType1());
387    
388                            if (Validator.isNotNull(entryQuery.getOrderByCol2()) &&
389                                    !entryQuery.getOrderByCol1().equals(
390                                            entryQuery.getOrderByCol2())) {
391    
392                                    if (entryQuery.getOrderByCol2().equals("ratings")) {
393                                            sb.append(", TEMP_TABLE.averageScore");
394                                    }
395                                    else {
396                                            sb.append(", AssetEntry.");
397                                            sb.append(entryQuery.getOrderByCol2());
398                                    }
399    
400                                    sb.append(StringPool.SPACE);
401                                    sb.append(entryQuery.getOrderByType2());
402                            }
403                    }
404    
405                    if (sb.index() > whereIndex) {
406                            String where = sb.stringAt(whereIndex);
407    
408                            if (where.startsWith(" AND")) {
409                                    sb.setStringAt(where.substring(4), whereIndex);
410                            }
411                    }
412    
413                    String sql = sb.toString();
414    
415                    SQLQuery q = session.createSynchronizedSQLQuery(sql);
416    
417                    if (count) {
418                            q.addScalar(COUNT_COLUMN_NAME, Type.LONG);
419                    }
420                    else {
421                            q.addEntity("AssetEntry", AssetEntryImpl.class);
422                    }
423    
424                    QueryPos qPos = QueryPos.getInstance(q);
425    
426                    if (entryQuery.getLinkedAssetEntryId() > 0) {
427                            qPos.add(entryQuery.getLinkedAssetEntryId());
428                            qPos.add(entryQuery.getLinkedAssetEntryId());
429                            qPos.add(entryQuery.getLinkedAssetEntryId());
430                    }
431    
432                    if (entryQuery.isVisible() != null) {
433                            qPos.add(entryQuery.isVisible());
434                    }
435    
436                    if (Validator.isNotNull(entryQuery.getKeywords())) {
437                            qPos.add(entryQuery.getKeywords() + CharPool.PERCENT);
438                            qPos.add(entryQuery.getKeywords() + CharPool.PERCENT);
439                    }
440                    else {
441                            if (Validator.isNotNull(entryQuery.getTitle())) {
442                                    qPos.add(entryQuery.getTitle() + CharPool.PERCENT);
443                            }
444    
445                            if (Validator.isNotNull(entryQuery.getDescription())) {
446                                    qPos.add(entryQuery.getDescription() + CharPool.PERCENT);
447                            }
448                    }
449    
450                    if (layout != null) {
451                            qPos.add(layout.getUuid());
452                    }
453    
454                    setDates(
455                            qPos, entryQuery.getPublishDate(), entryQuery.getExpirationDate());
456    
457                    qPos.add(entryQuery.getGroupIds());
458                    qPos.add(entryQuery.getClassNameIds());
459    
460                    return q;
461            }
462    
463            protected void buildClassTypeIdsSQL(long[] classTypeIds, StringBundler sb) {
464                    sb.append(" AND (");
465    
466                    for (int i = 0; i < classTypeIds.length; i++) {
467                            sb.append(" AssetEntry.classTypeId = ");
468                            sb.append(classTypeIds[i]);
469    
470                            if ((i + 1) < classTypeIds.length) {
471                                    sb.append(" OR ");
472                            }
473                            else {
474                                    sb.append(StringPool.CLOSE_PARENTHESIS);
475                            }
476                    }
477            }
478    
479            protected void buildNotAllCategoriesSQL(
480                    long[] categoryIds, StringBundler sb) {
481    
482                    String findByAndCategoryIdsSQL = CustomSQLUtil.get(
483                            FIND_BY_AND_CATEGORY_IDS);
484    
485                    sb.append(" AND (");
486    
487                    for (int i = 0; i < categoryIds.length; i++) {
488                            sb.append("NOT ");
489    
490                            String sql = null;
491    
492                            if (PropsValues.ASSET_CATEGORIES_SEARCH_HIERARCHICAL) {
493                                    List<Long> treeCategoryIds = getSubcategoryIds(categoryIds[i]);
494    
495                                    if (treeCategoryIds.size() > 1) {
496                                            sql = StringUtil.replace(
497                                                    findByAndCategoryIdsSQL, "[$CATEGORY_ID$]",
498                                                    StringUtil.merge(treeCategoryIds));
499                                    }
500                            }
501    
502                            if (sql == null) {
503                                    sql = StringUtil.replace(
504                                            findByAndCategoryIdsSQL, " IN ([$CATEGORY_ID$])",
505                                            " = " + categoryIds[i]);
506                            }
507    
508                            sb.append(sql);
509    
510                            if ((i + 1) < categoryIds.length) {
511                                    sb.append(" OR ");
512                            }
513                    }
514    
515                    sb.append(StringPool.CLOSE_PARENTHESIS);
516            }
517    
518            protected void buildNotAllTagsSQL(long[][] tagIds, StringBundler sb) {
519                    sb.append(" AND (");
520    
521                    for (int i = 0; i < tagIds.length; i++) {
522                            sb.append("AssetEntry.entryId NOT IN (");
523    
524                            String sql = CustomSQLUtil.get(FIND_BY_AND_TAG_IDS);
525    
526                            sql = StringUtil.replace(sql, "[$TAG_ID$]", getTagIds(tagIds[i]));
527    
528                            sb.append(sql);
529                            sb.append(StringPool.CLOSE_PARENTHESIS);
530    
531                            if (((i + 1) < tagIds.length) && (tagIds[i + 1].length > 0)) {
532                                    sb.append(" OR ");
533                            }
534                    }
535    
536                    sb.append(StringPool.CLOSE_PARENTHESIS);
537            }
538    
539            protected void buildNotAnyCategoriesSQL(
540                    long[] notCategoryIds, StringBundler sb) {
541    
542                    sb.append(" AND (NOT ");
543    
544                    String sql = CustomSQLUtil.get(FIND_BY_AND_CATEGORY_IDS);
545    
546                    String notCategoryIdsString = null;
547    
548                    if (PropsValues.ASSET_CATEGORIES_SEARCH_HIERARCHICAL) {
549                            List<Long> notCategoryIdsList = new ArrayList<Long>();
550    
551                            for (long notCategoryId : notCategoryIds) {
552                                    notCategoryIdsList.addAll(getSubcategoryIds(notCategoryId));
553                            }
554    
555                            notCategoryIdsString = StringUtil.merge(notCategoryIdsList);
556                    }
557                    else {
558                            notCategoryIdsString = StringUtil.merge(notCategoryIds);
559                    }
560    
561                    sb.append(
562                            StringUtil.replace(sql, "[$CATEGORY_ID$]", notCategoryIdsString));
563                    sb.append(StringPool.CLOSE_PARENTHESIS);
564            }
565    
566            protected String buildNotAnyTagsSQL(long[] notTagIds, StringBundler sb) {
567                    sb.append(" AND (");
568    
569                    for (int i = 0; i < notTagIds.length; i++) {
570                            sb.append("AssetEntry.entryId NOT IN (");
571    
572                            String sql = CustomSQLUtil.get(FIND_BY_AND_TAG_IDS);
573    
574                            sql = StringUtil.replace(sql, "[$TAG_ID$]", getTagIds(notTagIds));
575    
576                            sb.append(sql);
577                            sb.append(StringPool.CLOSE_PARENTHESIS);
578    
579                            if ((i + 1) < notTagIds.length) {
580                                    sb.append(" AND ");
581                            }
582                    }
583    
584                    sb.append(StringPool.CLOSE_PARENTHESIS);
585    
586                    return sb.toString();
587            }
588    
589            protected String getClassNameIds(long[] classNameIds) {
590                    if (classNameIds.length == 0) {
591                            return StringPool.BLANK;
592                    }
593    
594                    StringBundler sb = new StringBundler(classNameIds.length + 1);
595    
596                    sb.append(" AND (AssetEntry.classNameId = ?");
597    
598                    for (int i = 0; i < (classNameIds.length - 1); i++) {
599                            sb.append(" OR AssetEntry.classNameId = ?");
600                    }
601    
602                    sb.append(StringPool.CLOSE_PARENTHESIS);
603    
604                    return sb.toString();
605            }
606    
607            protected String getDates(Date publishDate, Date expirationDate) {
608                    StringBundler sb = new StringBundler(4);
609    
610                    if (publishDate != null) {
611                            sb.append(" AND (AssetEntry.publishDate IS NULL OR ");
612                            sb.append("AssetEntry.publishDate < ?)");
613                    }
614    
615                    if (expirationDate != null) {
616                            sb.append(" AND (AssetEntry.expirationDate IS NULL OR ");
617                            sb.append("AssetEntry.expirationDate > ?)");
618                    }
619    
620                    return sb.toString();
621            }
622    
623            protected String getGroupIds(long[] groupIds) {
624                    if (groupIds.length == 0) {
625                            return StringPool.BLANK;
626                    }
627    
628                    StringBundler sb = new StringBundler(groupIds.length + 1);
629    
630                    sb.append(" AND (AssetEntry.groupId = ?");
631    
632                    for (int i = 0; i < (groupIds.length - 1); i++) {
633                            sb.append(" OR AssetEntry.groupId = ?");
634                    }
635    
636                    sb.append(StringPool.CLOSE_PARENTHESIS);
637    
638                    return sb.toString();
639            }
640    
641            protected List<Long> getSubcategoryIds(long parentCategoryId) {
642                    AssetCategory parentAssetCategory = AssetCategoryUtil.fetchByPrimaryKey(
643                            parentCategoryId);
644    
645                    if (parentAssetCategory == null) {
646                            return Collections.emptyList();
647                    }
648    
649                    return ListUtil.toList(
650                            AssetCategoryUtil.getDescendants(parentAssetCategory),
651                            AssetCategory.CATEGORY_ID_ACCESSOR);
652            }
653    
654            protected String getTagIds(long[] tagIds) {
655                    StringBundler sb = new StringBundler((tagIds.length * 3) - 1);
656    
657                    for (int i = 0; i < tagIds.length; i++) {
658                            sb.append("tagId = ");
659                            sb.append(tagIds[i]);
660    
661                            if ((i + 1) != tagIds.length) {
662                                    sb.append(" OR ");
663                            }
664                    }
665    
666                    return sb.toString();
667            }
668    
669            protected void setDates(
670                    QueryPos qPos, Date publishDate, Date expirationDate) {
671    
672                    if (publishDate != null) {
673                            Timestamp publishDate_TS = CalendarUtil.getTimestamp(publishDate);
674    
675                            qPos.add(publishDate_TS);
676                    }
677    
678                    if (expirationDate != null) {
679                            Timestamp expirationDate_TS = CalendarUtil.getTimestamp(
680                                    expirationDate);
681    
682                            qPos.add(expirationDate_TS);
683                    }
684            }
685    
686    }