1   /**
2    * Copyright (c) 2000-2008 Liferay, Inc. All rights reserved.
3    *
4    * Permission is hereby granted, free of charge, to any person obtaining a copy
5    * of this software and associated documentation files (the "Software"), to deal
6    * in the Software without restriction, including without limitation the rights
7    * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
8    * copies of the Software, and to permit persons to whom the Software is
9    * furnished to do so, subject to the following conditions:
10   *
11   * The above copyright notice and this permission notice shall be included in
12   * all copies or substantial portions of the Software.
13   *
14   * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
15   * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
16   * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
17   * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
18   * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
19   * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
20   * SOFTWARE.
21   */
22  
23  package com.liferay.portlet.tags.service.persistence;
24  
25  import com.liferay.portal.SystemException;
26  import com.liferay.portal.kernel.util.StringMaker;
27  import com.liferay.portal.kernel.util.StringPool;
28  import com.liferay.portal.kernel.util.StringUtil;
29  import com.liferay.portal.kernel.util.Validator;
30  import com.liferay.portal.spring.hibernate.CustomSQLUtil;
31  import com.liferay.portal.spring.hibernate.HibernateUtil;
32  import com.liferay.portlet.tags.model.impl.TagsAssetImpl;
33  import com.liferay.util.cal.CalendarUtil;
34  import com.liferay.util.dao.hibernate.QueryPos;
35  import com.liferay.util.dao.hibernate.QueryUtil;
36  
37  import java.sql.Timestamp;
38  
39  import java.util.ArrayList;
40  import java.util.Date;
41  import java.util.Iterator;
42  import java.util.List;
43  
44  import org.hibernate.Hibernate;
45  import org.hibernate.SQLQuery;
46  import org.hibernate.Session;
47  
48  /**
49   * <a href="TagsAssetFinderImpl.java.html"><b><i>View Source</i></b></a>
50   *
51   * @author Brian Wing Shun Chan
52   *
53   */
54  public class TagsAssetFinderImpl implements TagsAssetFinder {
55  
56      public static String COUNT_BY_AND_ENTRY_IDS =
57          TagsAssetFinder.class.getName() + ".countByAndEntryIds";
58  
59      public static String COUNT_BY_OR_ENTRY_IDS =
60          TagsAssetFinder.class.getName() + ".countByOrEntryIds";
61  
62      public static String FIND_BY_AND_ENTRY_IDS =
63          TagsAssetFinder.class.getName() + ".findByAndEntryIds";
64  
65      public static String FIND_BY_OR_ENTRY_IDS =
66          TagsAssetFinder.class.getName() + ".findByOrEntryIds";
67  
68      public static String FIND_BY_VIEW_COUNT =
69          TagsAssetFinder.class.getName() + ".findByViewCount";
70  
71      public static String[] ORDER_BY_COLUMNS = new String[] {
72          "title", "createDate", "modifiedDate", "publishDate", "expirationDate",
73          "priority", "viewCount"
74      };
75  
76      public static String[] ORDER_BY_TYPE = new String[] {
77          "ASC", "DESC"
78      };
79  
80      public int countByAndEntryIds(
81              long groupId, long[] classNameIds, long[] entryIds,
82              long[] notEntryIds, boolean excludeZeroViewCount, Date publishDate,
83              Date expirationDate)
84          throws SystemException {
85  
86          if (entryIds.length == 0) {
87              return 0;
88          }
89  
90          Session session = null;
91  
92          try {
93              session = HibernateUtil.openSession();
94  
95              StringMaker sm = new StringMaker();
96  
97              sm.append("SELECT COUNT(DISTINCT assetId) AS COUNT_VALUE ");
98              sm.append("FROM TagsAsset WHERE TagsAsset.assetId IN (");
99  
100             for (int i = 0; i < entryIds.length; i++) {
101                 sm.append(CustomSQLUtil.get(FIND_BY_AND_ENTRY_IDS));
102 
103                 if ((i + 1) < entryIds.length) {
104                     sm.append(" AND TagsAsset.assetId IN (");
105                 }
106             }
107 
108             for (int i = 0; i < entryIds.length; i++) {
109                 if ((i + 1) < entryIds.length) {
110                     sm.append(StringPool.CLOSE_PARENTHESIS);
111                 }
112             }
113 
114             if (excludeZeroViewCount) {
115                 sm.append(" AND (TagsAsset.viewCount > 0)");
116             }
117 
118             sm.append(StringPool.CLOSE_PARENTHESIS);
119 
120             if (notEntryIds.length > 0) {
121                 sm.append(" AND (");
122 
123                 for (int i = 0; i < notEntryIds.length; i++) {
124                     sm.append("TagsAsset.assetId NOT IN (");
125                     sm.append(CustomSQLUtil.get(FIND_BY_AND_ENTRY_IDS));
126                     sm.append(StringPool.CLOSE_PARENTHESIS);
127 
128                     if ((i + 1) < notEntryIds.length) {
129                         sm.append(" OR ");
130                     }
131                 }
132 
133                 sm.append(StringPool.CLOSE_PARENTHESIS);
134             }
135 
136             sm.append("[$DATES$]");
137 
138             if (groupId > 0) {
139                 sm.append(" AND (TagsAsset.groupId = ?)");
140             }
141 
142             sm.append(getClassNameIds(classNameIds));
143 
144             String sql = sm.toString();
145 
146             sql = getDates(sql, publishDate, expirationDate);
147 
148             SQLQuery q = session.createSQLQuery(sql);
149 
150             q.addScalar(HibernateUtil.getCountColumnName(), Hibernate.LONG);
151 
152             QueryPos qPos = QueryPos.getInstance(q);
153 
154             setEntryIds(qPos, entryIds);
155             setEntryIds(qPos, notEntryIds);
156             setDates(qPos, publishDate, expirationDate);
157 
158             if (groupId > 0) {
159                 setGroupId(qPos, groupId);
160             }
161 
162             setClassNamedIds(qPos, classNameIds);
163 
164             Iterator itr = q.list().iterator();
165 
166             if (itr.hasNext()) {
167                 Long count = (Long)itr.next();
168 
169                 if (count != null) {
170                     return count.intValue();
171                 }
172             }
173 
174             return 0;
175         }
176         catch (Exception e) {
177             throw new SystemException(e);
178         }
179         finally {
180             HibernateUtil.closeSession(session);
181         }
182     }
183 
184     public int countByOrEntryIds(
185             long groupId, long[] classNameIds, long[] entryIds,
186             long[] notEntryIds, boolean excludeZeroViewCount, Date publishDate,
187             Date expirationDate)
188         throws SystemException {
189 
190         if (entryIds.length == 0) {
191             return 0;
192         }
193 
194         Session session = null;
195 
196         try {
197             session = HibernateUtil.openSession();
198 
199             String sql = CustomSQLUtil.get(COUNT_BY_OR_ENTRY_IDS);
200 
201             sql = StringUtil.replace(
202                 sql, "[$ENTRY_ID$]", getEntryIds(entryIds, StringPool.EQUAL));
203 
204             if (notEntryIds.length > 0) {
205                 StringMaker sm = new StringMaker();
206 
207                 sm.append(" AND (");
208 
209                 for (int i = 0; i < notEntryIds.length; i++) {
210                     sm.append("TagsAsset.assetId NOT IN (");
211                     sm.append(CustomSQLUtil.get(FIND_BY_AND_ENTRY_IDS));
212                     sm.append(StringPool.CLOSE_PARENTHESIS);
213 
214                     if ((i + 1) < notEntryIds.length) {
215                         sm.append(" AND ");
216                     }
217                 }
218 
219                 sm.append(StringPool.CLOSE_PARENTHESIS);
220 
221                 sql = StringUtil.replace(
222                     sql, "[$NOT_ENTRY_ID$]", sm.toString());
223             }
224             else {
225                 sql = StringUtil.replace(
226                     sql, "[$NOT_ENTRY_ID$]", StringPool.BLANK);
227             }
228 
229             sql = getDates(sql, publishDate, expirationDate);
230 
231             if (groupId > 0) {
232                 sql += " AND (TagsAsset.groupId = ?)";
233             }
234 
235             sql += getClassNameIds(classNameIds);
236 
237             if (excludeZeroViewCount) {
238                 sql += " AND (TagsAsset.viewCount > 0)";
239             }
240 
241             SQLQuery q = session.createSQLQuery(sql);
242 
243             q.addScalar(HibernateUtil.getCountColumnName(), Hibernate.LONG);
244 
245             QueryPos qPos = QueryPos.getInstance(q);
246 
247             setEntryIds(qPos, entryIds);
248             setEntryIds(qPos, notEntryIds);
249             setDates(qPos, publishDate, expirationDate);
250 
251             if (groupId > 0) {
252                 setGroupId(qPos, groupId);
253             }
254 
255             setClassNamedIds(qPos, classNameIds);
256 
257             Iterator itr = q.list().iterator();
258 
259             if (itr.hasNext()) {
260                 Long count = (Long)itr.next();
261 
262                 if (count != null) {
263                     return count.intValue();
264                 }
265             }
266 
267             return 0;
268         }
269         catch (Exception e) {
270             throw new SystemException(e);
271         }
272         finally {
273             HibernateUtil.closeSession(session);
274         }
275     }
276 
277     public List findByAndEntryIds(
278             long groupId, long[] classNameIds, long[] entryIds,
279             long[] notEntryIds, String orderByCol1, String orderByCol2,
280             String orderByType1, String orderByType2,
281             boolean excludeZeroViewCount, Date publishDate, Date expirationDate,
282             int begin, int end)
283         throws SystemException {
284 
285         if (entryIds.length == 0) {
286             return new ArrayList();
287         }
288 
289         orderByCol1 = checkOrderByCol(orderByCol1);
290         orderByCol2 = checkOrderByCol(orderByCol2);
291         orderByType1 = checkOrderByType(orderByType1);
292         orderByType2 = checkOrderByType(orderByType2);
293 
294         Session session = null;
295 
296         try {
297             session = HibernateUtil.openSession();
298 
299             StringMaker sm = new StringMaker();
300 
301             sm.append("SELECT DISTINCT {TagsAsset.*} ");
302             sm.append("FROM TagsAsset WHERE TagsAsset.assetId IN (");
303 
304             for (int i = 0; i < entryIds.length; i++) {
305                 sm.append(CustomSQLUtil.get(FIND_BY_AND_ENTRY_IDS));
306 
307                 if ((i + 1) < entryIds.length) {
308                     sm.append(" AND TagsAsset.assetId IN (");
309                 }
310             }
311 
312             for (int i = 0; i < entryIds.length; i++) {
313                 if ((i + 1) < entryIds.length) {
314                     sm.append(StringPool.CLOSE_PARENTHESIS);
315                 }
316             }
317 
318             if (excludeZeroViewCount) {
319                 sm.append(" AND (TagsAsset.viewCount > 0)");
320             }
321 
322             sm.append(StringPool.CLOSE_PARENTHESIS);
323 
324             if (notEntryIds.length > 0) {
325                 sm.append(" AND (");
326 
327                 for (int i = 0; i < notEntryIds.length; i++) {
328                     sm.append("TagsAsset.assetId NOT IN (");
329                     sm.append(CustomSQLUtil.get(FIND_BY_AND_ENTRY_IDS));
330                     sm.append(StringPool.CLOSE_PARENTHESIS);
331 
332                     if ((i + 1) < notEntryIds.length) {
333                         sm.append(" OR ");
334                     }
335                 }
336 
337                 sm.append(StringPool.CLOSE_PARENTHESIS);
338             }
339 
340             sm.append("[$DATES$]");
341 
342             if (groupId > 0) {
343                 sm.append(" AND (TagsAsset.groupId = ?)");
344             }
345 
346             sm.append(getClassNameIds(classNameIds));
347 
348             sm.append(" ORDER BY TagsAsset.");
349             sm.append(orderByCol1);
350             sm.append(StringPool.SPACE);
351             sm.append(orderByType1);
352 
353             if (Validator.isNotNull(orderByCol2) &&
354                 !orderByCol1.equals(orderByCol2)) {
355 
356                 sm.append(", TagsAsset.");
357                 sm.append(orderByCol2);
358                 sm.append(StringPool.SPACE);
359                 sm.append(orderByType2);
360             }
361 
362             String sql = sm.toString();
363 
364             sql = getDates(sql, publishDate, expirationDate);
365 
366             SQLQuery q = session.createSQLQuery(sql);
367 
368             q.addEntity("TagsAsset", TagsAssetImpl.class);
369 
370             QueryPos qPos = QueryPos.getInstance(q);
371 
372             setEntryIds(qPos, entryIds);
373             setEntryIds(qPos, notEntryIds);
374             setDates(qPos, publishDate, expirationDate);
375 
376             if (groupId > 0) {
377                 setGroupId(qPos, groupId);
378             }
379 
380             setClassNamedIds(qPos, classNameIds);
381 
382             return QueryUtil.list(q, HibernateUtil.getDialect(), begin, end);
383         }
384         catch (Exception e) {
385             throw new SystemException(e);
386         }
387         finally {
388             HibernateUtil.closeSession(session);
389         }
390     }
391 
392     public List findByOrEntryIds(
393             long groupId, long[] classNameIds, long[] entryIds,
394             long[] notEntryIds, Date publishDate, Date expirationDate )
395         throws SystemException {
396 
397         return findByOrEntryIds(
398             groupId, classNameIds, entryIds, notEntryIds, null, null, null,
399             null, false, publishDate, expirationDate, QueryUtil.ALL_POS,
400             QueryUtil.ALL_POS);
401     }
402 
403     public List findByOrEntryIds(
404             long groupId, long[] classNameIds, long[] entryIds,
405             long[] notEntryIds, String orderByCol1, String orderByCol2,
406             String orderByType1, String orderByType2,
407             boolean excludeZeroViewCount, Date publishDate, Date expirationDate,
408             int begin, int end)
409         throws SystemException {
410 
411         if (entryIds.length == 0) {
412             return new ArrayList();
413         }
414 
415         orderByCol1 = checkOrderByCol(orderByCol1);
416         orderByCol2 = checkOrderByCol(orderByCol2);
417         orderByType1 = checkOrderByType(orderByType1);
418         orderByType2 = checkOrderByType(orderByType2);
419 
420         Session session = null;
421 
422         try {
423             session = HibernateUtil.openSession();
424 
425             String sql = CustomSQLUtil.get(FIND_BY_OR_ENTRY_IDS);
426 
427             sql = StringUtil.replace(
428                 sql, "[$ENTRY_ID$]", getEntryIds(entryIds, StringPool.EQUAL));
429 
430             if (notEntryIds.length > 0) {
431                 StringMaker sm = new StringMaker();
432 
433                 sm.append(" AND (");
434 
435                 for (int i = 0; i < notEntryIds.length; i++) {
436                     sm.append("TagsAsset.assetId NOT IN (");
437                     sm.append(CustomSQLUtil.get(FIND_BY_AND_ENTRY_IDS));
438                     sm.append(StringPool.CLOSE_PARENTHESIS);
439 
440                     if ((i + 1) < notEntryIds.length) {
441                         sm.append(" AND ");
442                     }
443                 }
444 
445                 sm.append(StringPool.CLOSE_PARENTHESIS);
446 
447                 sql = StringUtil.replace(
448                     sql, "[$NOT_ENTRY_ID$]", sm.toString());
449             }
450             else {
451                 sql = StringUtil.replace(
452                     sql, "[$NOT_ENTRY_ID$]", StringPool.BLANK);
453             }
454 
455             sql = getDates(sql, publishDate, expirationDate);
456 
457             if (groupId > 0) {
458                 sql += " AND (TagsAsset.groupId = ?)";
459             }
460 
461             sql += getClassNameIds(classNameIds);
462 
463             if (excludeZeroViewCount) {
464                 sql += " AND (TagsAsset.viewCount > 0)";
465             }
466 
467             StringMaker sm = new StringMaker();
468 
469             sm.append(" ORDER BY TagsAsset.");
470             sm.append(orderByCol1);
471             sm.append(StringPool.SPACE);
472             sm.append(orderByType1);
473 
474             if (Validator.isNotNull(orderByCol2) &&
475                 !orderByCol1.equals(orderByCol2)) {
476 
477                 sm.append(", TagsAsset.");
478                 sm.append(orderByCol2);
479                 sm.append(StringPool.SPACE);
480                 sm.append(orderByType2);
481             }
482 
483             sql += sm.toString();
484 
485             SQLQuery q = session.createSQLQuery(sql);
486 
487             q.addEntity("TagsAsset", TagsAssetImpl.class);
488 
489             QueryPos qPos = QueryPos.getInstance(q);
490 
491             setEntryIds(qPos, entryIds);
492             setEntryIds(qPos, notEntryIds);
493             setDates(qPos, publishDate, expirationDate);
494 
495             if (groupId > 0) {
496                 setGroupId(qPos, groupId);
497             }
498 
499             setClassNamedIds(qPos, classNameIds);
500 
501             return QueryUtil.list(q, HibernateUtil.getDialect(), begin, end);
502         }
503         catch (Exception e) {
504             throw new SystemException(e);
505         }
506         finally {
507             HibernateUtil.closeSession(session);
508         }
509     }
510 
511     public List findByViewCount(
512             long[] classNameId, boolean asc, int begin, int end)
513         throws SystemException {
514 
515         Session session = null;
516 
517         try {
518             session = HibernateUtil.openSession();
519 
520             String sql = CustomSQLUtil.get(FIND_BY_VIEW_COUNT);
521 
522             StringMaker sm = new StringMaker();
523 
524             for (int i = 0; i < classNameId.length; i++) {
525                 sm.append("(TagsAsset.classNameId = ?)");
526 
527                 if ((i+1) < classNameId.length) {
528                     sm.append(" OR ");
529                 }
530             }
531 
532             sql = StringUtil.replace(
533                 sql, "(TagsAsset.classNameId = ?)", sm.toString());
534 
535             sm = new StringMaker();
536 
537             sm.append(" ORDER BY TagsAsset.viewCount");
538 
539             if (asc) {
540                 sm.append(" ASC");
541             }
542             else {
543                 sm.append(" DESC");
544             }
545 
546             sql += sm.toString();
547 
548             SQLQuery q = session.createSQLQuery(sql);
549 
550             q.addEntity("TagsAsset", TagsAssetImpl.class);
551 
552             QueryPos qPos = QueryPos.getInstance(q);
553 
554             for (int i = 0; i < classNameId.length; i++) {
555                 qPos.add(classNameId[i]);
556             }
557 
558             return QueryUtil.list(q, HibernateUtil.getDialect(), begin, end);
559         }
560         catch (Exception e) {
561             throw new SystemException(e);
562         }
563         finally {
564             HibernateUtil.closeSession(session);
565         }
566     }
567 
568     protected String checkOrderByCol(String orderByCol) {
569         if (orderByCol == null) {
570             return "modifiedDate";
571         }
572 
573         for (int i = 0; i < ORDER_BY_COLUMNS.length; i++) {
574             if (orderByCol.equals(ORDER_BY_COLUMNS[i])) {
575                 return orderByCol;
576             }
577         }
578 
579         return "modifiedDate";
580     }
581 
582     protected String checkOrderByType(String orderByType) {
583         if (orderByType == null) {
584             return "DESC";
585         }
586 
587         for (int i = 0; i < ORDER_BY_TYPE.length; i++) {
588             if (orderByType.equals(ORDER_BY_TYPE[i])) {
589                 return orderByType;
590             }
591         }
592 
593         return "DESC";
594     }
595 
596     protected String getClassNameIds(long[] classNameIds) {
597         StringMaker sm = new StringMaker();
598 
599         if (classNameIds.length > 0) {
600             sm.append(" AND (");
601 
602             for (int i = 0; i < classNameIds.length; i++) {
603                 sm.append("classNameId = ?");
604 
605                 if (i > 0) {
606                     sm.append(" AND ");
607                 }
608             }
609 
610             sm.append(") ");
611         }
612 
613         return sm.toString();
614     }
615 
616     protected String getDates(
617         String sql, Date publishDate, Date expirationDate) {
618 
619         StringMaker sm = new StringMaker();
620 
621         if (publishDate != null) {
622             sm.append(" AND (publishDate IS NULL OR publishDate < ?)");
623         }
624 
625         if (expirationDate != null) {
626             sm.append(" AND (expirationDate IS NULL OR expirationDate > ?)");
627         }
628 
629         sql = StringUtil.replace(sql, "[$DATES$]", sm.toString());
630 
631         return sql;
632     }
633 
634     protected String getEntryIds(long[] entryIds, String operator) {
635         StringMaker sm = new StringMaker();
636 
637         for (int i = 0; i < entryIds.length; i++) {
638             sm.append("TagsEntry.entryId ");
639             sm.append(operator);
640             sm.append(" ? ");
641 
642             if ((i + 1) != entryIds.length) {
643                 sm.append("OR ");
644             }
645         }
646 
647         return sm.toString();
648     }
649 
650     protected void setClassNamedIds(QueryPos qPos, long[] classNameIds) {
651         for (int i = 0; i < classNameIds.length; i++) {
652             qPos.add(classNameIds[i]);
653         }
654     }
655 
656     protected void setDates(
657         QueryPos qPos, Date publishDate, Date expirationDate) {
658 
659         if (publishDate != null) {
660             Timestamp publishDate_TS = CalendarUtil.getTimestamp(publishDate);
661 
662             qPos.add(publishDate_TS);
663         }
664 
665         if (expirationDate != null) {
666             Timestamp expirationDate_TS =
667                 CalendarUtil.getTimestamp(expirationDate);
668 
669             qPos.add(expirationDate_TS);
670         }
671     }
672 
673     protected void setGroupId(QueryPos qPos, long groupId) {
674         qPos.add(groupId);
675     }
676 
677     protected void setEntryIds(QueryPos qPos, long[] entryIds) {
678         for (int i = 0; i < entryIds.length; i++) {
679             qPos.add(entryIds[i]);
680         }
681     }
682 
683 }