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