1   /**
2    * Copyright (c) 2000-2010 Liferay, Inc. All rights reserved.
3    *
4    * This library is free software; you can redistribute it and/or modify it under
5    * the terms of the GNU Lesser General Public License as published by the Free
6    * Software Foundation; either version 2.1 of the License, or (at your option)
7    * any later version.
8    *
9    * This library is distributed in the hope that it will be useful, but WITHOUT
10   * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
11   * FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more
12   * details.
13   */
14  
15  package com.liferay.portlet.journal.service.persistence;
16  
17  import com.liferay.portal.kernel.dao.orm.QueryPos;
18  import com.liferay.portal.kernel.dao.orm.SQLQuery;
19  import com.liferay.portal.kernel.dao.orm.Session;
20  import com.liferay.portal.kernel.dao.orm.Type;
21  import com.liferay.portal.kernel.exception.SystemException;
22  import com.liferay.portal.kernel.util.CalendarUtil;
23  import com.liferay.portal.kernel.util.OrderByComparator;
24  import com.liferay.portal.kernel.util.StringBundler;
25  import com.liferay.portal.kernel.util.StringPool;
26  import com.liferay.portal.kernel.util.StringUtil;
27  import com.liferay.portal.kernel.util.Validator;
28  import com.liferay.portal.kernel.workflow.WorkflowConstants;
29  import com.liferay.portal.service.persistence.impl.BasePersistenceImpl;
30  import com.liferay.portlet.journal.NoSuchArticleException;
31  import com.liferay.portlet.journal.model.JournalArticle;
32  import com.liferay.portlet.journal.model.impl.JournalArticleImpl;
33  import com.liferay.util.dao.orm.CustomSQLUtil;
34  
35  import java.sql.Timestamp;
36  
37  import java.util.ArrayList;
38  import java.util.Date;
39  import java.util.Iterator;
40  import java.util.List;
41  
42  /**
43   * <a href="JournalArticleFinderImpl.java.html"><b><i>View Source</i></b></a>
44   *
45   * @author Brian Wing Shun Chan
46   * @author Raymond Augé
47   */
48  public class JournalArticleFinderImpl
49      extends BasePersistenceImpl<JournalArticle>
50      implements JournalArticleFinder {
51  
52      public static String COUNT_BY_C_G_A_V_T_D_C_T_S_T_D_S_R =
53          JournalArticleFinder.class.getName() +
54              ".countByC_G_A_V_T_D_C_T_S_T_D_S_R";
55  
56      public static String FIND_BY_EXPIRATION_DATE =
57          JournalArticleFinder.class.getName() + ".findByExpirationDate";
58  
59      public static String FIND_BY_REVIEW_DATE =
60          JournalArticleFinder.class.getName() + ".findByReviewDate";
61  
62      public static String FIND_BY_R_D =
63          JournalArticleFinder.class.getName() + ".findByR_D";
64  
65      public static String FIND_BY_C_G_A_V_T_D_C_T_S_T_D_S_R =
66          JournalArticleFinder.class.getName() +
67              ".findByC_G_A_V_T_D_C_T_S_T_D_S_R";
68  
69      public int countByKeywords(
70              long companyId, long groupId, String keywords, Double version,
71              String type, String structureId, String templateId,
72              Date displayDateGT, Date displayDateLT, int status, Date reviewDate)
73          throws SystemException {
74  
75          String[] articleIds = null;
76          String[] titles = null;
77          String[] descriptions = null;
78          String[] contents = null;
79          boolean andOperator = false;
80  
81          if (Validator.isNotNull(keywords)) {
82              articleIds = CustomSQLUtil.keywords(keywords, false);
83              titles = CustomSQLUtil.keywords(keywords);
84              descriptions = CustomSQLUtil.keywords(keywords, false);
85              contents = CustomSQLUtil.keywords(keywords, false);
86          }
87          else {
88              andOperator = true;
89          }
90  
91          return countByC_G_A_V_T_D_C_T_S_T_D_S_R(
92              companyId, groupId, articleIds, version, titles, descriptions,
93              contents, type, new String[] {structureId},
94              new String[] {templateId}, displayDateGT, displayDateLT, status,
95              reviewDate, andOperator);
96      }
97  
98      public int countByC_G_A_V_T_D_C_T_S_T_D_S_R(
99              long companyId, long groupId, String articleId, Double version,
100             String title, String description, String content, String type,
101             String structureId, String templateId, Date displayDateGT,
102             Date displayDateLT, int status, Date reviewDate,
103             boolean andOperator)
104         throws SystemException {
105 
106         return countByC_G_A_V_T_D_C_T_S_T_D_S_R(
107             companyId, groupId, articleId, version, title, description,
108             content, type, new String[] {structureId},
109             new String[] {templateId}, displayDateGT, displayDateLT, status,
110             reviewDate, andOperator);
111     }
112 
113     public int countByC_G_A_V_T_D_C_T_S_T_D_S_R(
114             long companyId, long groupId, String articleId, Double version,
115             String title, String description, String content, String type,
116             String[] structureIds, String[] templateIds, Date displayDateGT,
117             Date displayDateLT, int status, Date reviewDate,
118             boolean andOperator)
119         throws SystemException {
120 
121         return countByC_G_A_V_T_D_C_T_S_T_D_S_R(
122             companyId, groupId, new String[] {articleId}, version,
123             new String[] {title}, new String[] {description},
124             new String[] {content}, type, structureIds, templateIds,
125             displayDateGT, displayDateLT, status, reviewDate, andOperator);
126     }
127 
128     public int countByC_G_A_V_T_D_C_T_S_T_D_S_R(
129             long companyId, long groupId, String[] articleIds, Double version,
130             String[] titles, String[] descriptions, String[] contents,
131             String type, String[] structureIds, String[] templateIds,
132             Date displayDateGT, Date displayDateLT, int status, Date reviewDate,
133             boolean andOperator)
134         throws SystemException {
135 
136         articleIds = CustomSQLUtil.keywords(articleIds, false);
137         titles = CustomSQLUtil.keywords(titles);
138         descriptions = CustomSQLUtil.keywords(descriptions, false);
139         contents = CustomSQLUtil.keywords(contents, false);
140         structureIds = CustomSQLUtil.keywords(structureIds, false);
141         templateIds = CustomSQLUtil.keywords(templateIds, false);
142         Timestamp displayDateGT_TS = CalendarUtil.getTimestamp(displayDateGT);
143         Timestamp displayDateLT_TS = CalendarUtil.getTimestamp(displayDateLT);
144         Timestamp reviewDate_TS = CalendarUtil.getTimestamp(reviewDate);
145 
146         Session session = null;
147 
148         try {
149             session = openSession();
150 
151             String sql = CustomSQLUtil.get(COUNT_BY_C_G_A_V_T_D_C_T_S_T_D_S_R);
152 
153             if (groupId <= 0) {
154                 sql = StringUtil.replace(sql, "(groupId = ?) AND", "");
155             }
156 
157             sql = CustomSQLUtil.replaceKeywords(
158                 sql, "articleId", StringPool.LIKE, false, articleIds);
159 
160             if (version == null) {
161                 sql = StringUtil.replace(
162                     sql, "(version = ?) [$AND_OR_CONNECTOR$]", "");
163             }
164             else if (version <= 0) {
165                 sql = StringUtil.replace(
166                     sql, "COUNT(*", "COUNT(DISTINCT articleId");
167 
168                 sql = StringUtil.replace(
169                     sql, "(version = ?) [$AND_OR_CONNECTOR$]", "");
170             }
171 
172             sql = CustomSQLUtil.replaceKeywords(
173                 sql, "lower(title)", StringPool.LIKE, false, titles);
174             sql = CustomSQLUtil.replaceKeywords(
175                 sql, "description", StringPool.LIKE, false, descriptions);
176             sql = CustomSQLUtil.replaceKeywords(
177                 sql, "content", StringPool.LIKE, false, contents);
178             sql = CustomSQLUtil.replaceKeywords(
179                 sql, "structureId", StringPool.EQUAL, false, structureIds);
180             sql = CustomSQLUtil.replaceKeywords(
181                 sql, "templateId", StringPool.EQUAL, false, templateIds);
182 
183             if (status == WorkflowConstants.STATUS_ANY) {
184                 sql = StringUtil.replace(sql, "(status = ?) AND", "");
185             }
186 
187             sql = CustomSQLUtil.replaceAndOperator(sql, andOperator);
188 
189             SQLQuery q = session.createSQLQuery(sql);
190 
191             q.addScalar(COUNT_COLUMN_NAME, Type.LONG);
192 
193             QueryPos qPos = QueryPos.getInstance(q);
194 
195             qPos.add(companyId);
196 
197             if (groupId > 0) {
198                 qPos.add(groupId);
199             }
200 
201             qPos.add(articleIds, 2);
202 
203             if ((version != null) && (version > 0)) {
204                 qPos.add(version);
205             }
206 
207             qPos.add(titles, 2);
208             qPos.add(descriptions, 2);
209             qPos.add(contents, 2);
210             qPos.add(type);
211             qPos.add(type);
212             qPos.add(structureIds, 2);
213             qPos.add(templateIds, 2);
214             qPos.add(displayDateGT_TS);
215             qPos.add(displayDateGT_TS);
216             qPos.add(displayDateLT_TS);
217             qPos.add(displayDateLT_TS);
218 
219             if (status != WorkflowConstants.STATUS_ANY) {
220                 qPos.add(status);
221             }
222 
223             qPos.add(reviewDate_TS);
224             qPos.add(reviewDate_TS);
225 
226             Iterator<Long> itr = q.list().iterator();
227 
228             if (itr.hasNext()) {
229                 Long count = itr.next();
230 
231                 if (count != null) {
232                     return count.intValue();
233                 }
234             }
235 
236             return 0;
237         }
238         catch (Exception e) {
239             throw new SystemException(e);
240         }
241         finally {
242             closeSession(session);
243         }
244     }
245 
246     public List<JournalArticle> findByExpirationDate(
247             int status, Date expirationDateLT, Date expirationDateGT)
248         throws SystemException {
249 
250         Timestamp expirationDateLT_TS = CalendarUtil.getTimestamp(
251             expirationDateLT);
252         Timestamp expirationDateGT_TS = CalendarUtil.getTimestamp(
253             expirationDateGT);
254 
255         Session session = null;
256 
257         try {
258             session = openSession();
259 
260             String sql = CustomSQLUtil.get(FIND_BY_EXPIRATION_DATE);
261 
262             if (status == WorkflowConstants.STATUS_ANY) {
263                 sql = StringUtil.replace(sql, "(status = ?) AND", "");
264             }
265 
266             SQLQuery q = session.createSQLQuery(sql);
267 
268             q.addEntity("JournalArticle", JournalArticleImpl.class);
269 
270             QueryPos qPos = QueryPos.getInstance(q);
271 
272             if (status != WorkflowConstants.STATUS_ANY) {
273                 qPos.add(status);
274             }
275 
276             qPos.add(expirationDateGT_TS);
277             qPos.add(expirationDateLT_TS);
278 
279             return q.list();
280         }
281         catch (Exception e) {
282             throw new SystemException(e);
283         }
284         finally {
285             closeSession(session);
286         }
287     }
288 
289     public List<JournalArticle> findByKeywords(
290             long companyId, long groupId, String keywords, Double version,
291             String type, String structureId, String templateId,
292             Date displayDateGT, Date displayDateLT, int status, Date reviewDate,
293             int start, int end, OrderByComparator orderByComparator)
294         throws SystemException {
295 
296         String[] articleIds = null;
297         String[] titles = null;
298         String[] descriptions = null;
299         String[] contents = null;
300         boolean andOperator = false;
301 
302         if (Validator.isNotNull(keywords)) {
303             articleIds = CustomSQLUtil.keywords(keywords, false);
304             titles = CustomSQLUtil.keywords(keywords);
305             descriptions = CustomSQLUtil.keywords(keywords, false);
306             contents = CustomSQLUtil.keywords(keywords, false);
307         }
308         else {
309             andOperator = true;
310         }
311 
312         return findByC_G_A_V_T_D_C_T_S_T_D_S_R(
313             companyId, groupId, articleIds, version, titles, descriptions,
314             contents, type, new String[] {structureId},
315             new String[] {templateId}, displayDateGT, displayDateLT, status,
316             reviewDate, andOperator, start, end, orderByComparator);
317     }
318 
319     public List<JournalArticle> findByReviewDate(
320             Date reviewDateLT, Date reviewDateGT)
321         throws SystemException {
322 
323         Timestamp reviewDateLT_TS = CalendarUtil.getTimestamp(reviewDateLT);
324         Timestamp reviewDateGT_TS = CalendarUtil.getTimestamp(reviewDateGT);
325 
326         Session session = null;
327         try {
328             session = openSession();
329 
330             String sql = CustomSQLUtil.get(FIND_BY_REVIEW_DATE);
331 
332             SQLQuery q = session.createSQLQuery(sql);
333 
334             q.addEntity("JournalArticle", JournalArticleImpl.class);
335 
336             QueryPos qPos = QueryPos.getInstance(q);
337 
338             qPos.add(reviewDateGT_TS);
339             qPos.add(reviewDateLT_TS);
340 
341             return q.list();
342         }
343         catch (Exception e) {
344             throw new SystemException(e);
345         }
346         finally {
347             closeSession(session);
348         }
349     }
350 
351     public JournalArticle findByR_D(long resourcePrimKey, Date displayDate)
352         throws NoSuchArticleException, SystemException {
353 
354         Timestamp displayDate_TS = CalendarUtil.getTimestamp(displayDate);
355 
356         Session session = null;
357 
358         try {
359             session = openSession();
360 
361             String sql = CustomSQLUtil.get(FIND_BY_R_D);
362 
363             SQLQuery q = session.createSQLQuery(sql);
364 
365             q.addEntity("JournalArticle", JournalArticleImpl.class);
366 
367             QueryPos qPos = QueryPos.getInstance(q);
368 
369             qPos.add(resourcePrimKey);
370             qPos.add(displayDate_TS);
371 
372             List<JournalArticle> list = q.list();
373 
374             if (list.size() == 0) {
375                 StringBundler sb = new StringBundler(6);
376 
377                 sb.append("No JournalArticle exists with the key ");
378                 sb.append("{resourcePrimKey=");
379                 sb.append(resourcePrimKey);
380                 sb.append(", displayDate=");
381                 sb.append(displayDate);
382                 sb.append("}");
383 
384                 throw new NoSuchArticleException(sb.toString());
385             }
386             else {
387                 return list.get(0);
388             }
389         }
390         catch (NoSuchArticleException nsae) {
391             throw nsae;
392         }
393         catch (Exception e) {
394             throw new SystemException(e);
395         }
396         finally {
397             closeSession(session);
398         }
399     }
400 
401     public List<JournalArticle> findByC_G_A_V_T_D_C_T_S_T_D_S_R(
402             long companyId, long groupId, String articleId, Double version,
403             String title, String description, String content, String type,
404             String structureId, String templateId, Date displayDateGT,
405             Date displayDateLT, int status, Date reviewDate,
406             boolean andOperator, int start, int end,
407             OrderByComparator orderByComparator)
408         throws SystemException {
409 
410         return findByC_G_A_V_T_D_C_T_S_T_D_S_R(
411             companyId, groupId, articleId, version, title, description,
412             content, type, new String[] {structureId},
413             new String[] {templateId}, displayDateGT, displayDateLT, status,
414             reviewDate, andOperator, start, end, orderByComparator);
415     }
416 
417     public List<JournalArticle> findByC_G_A_V_T_D_C_T_S_T_D_S_R(
418             long companyId, long groupId, String articleId, Double version,
419             String title, String description, String content, String type,
420             String[] structureIds, String[] templateIds, Date displayDateGT,
421             Date displayDateLT, int status, Date reviewDate,
422             boolean andOperator, int start, int end,
423             OrderByComparator orderByComparator)
424         throws SystemException {
425 
426         return findByC_G_A_V_T_D_C_T_S_T_D_S_R(
427             companyId, groupId, new String[] {articleId}, version,
428             new String[] {title}, new String[] {description},
429             new String[] {content}, type, structureIds, templateIds,
430             displayDateGT, displayDateLT, status, reviewDate, andOperator,
431             start, end, orderByComparator);
432     }
433 
434     public List<JournalArticle> findByC_G_A_V_T_D_C_T_S_T_D_S_R(
435             long companyId, long groupId, String[] articleIds, Double version,
436             String[] titles, String[] descriptions, String[] contents,
437             String type, String[] structureIds, String[] templateIds,
438             Date displayDateGT, Date displayDateLT, int status,
439             Date reviewDate, boolean andOperator, int start, int end,
440             OrderByComparator orderByComparator)
441         throws SystemException {
442 
443         articleIds = CustomSQLUtil.keywords(articleIds, false);
444         titles = CustomSQLUtil.keywords(titles);
445         descriptions = CustomSQLUtil.keywords(descriptions, false);
446         contents = CustomSQLUtil.keywords(contents, false);
447         structureIds = CustomSQLUtil.keywords(structureIds, false);
448         templateIds = CustomSQLUtil.keywords(templateIds, false);
449         Timestamp displayDateGT_TS = CalendarUtil.getTimestamp(displayDateGT);
450         Timestamp displayDateLT_TS = CalendarUtil.getTimestamp(displayDateLT);
451         Timestamp reviewDate_TS = CalendarUtil.getTimestamp(reviewDate);
452 
453         Session session = null;
454 
455         try {
456             session = openSession();
457 
458             String sql = CustomSQLUtil.get(FIND_BY_C_G_A_V_T_D_C_T_S_T_D_S_R);
459 
460             if (groupId <= 0) {
461                 sql = StringUtil.replace(sql, "(groupId = ?) AND", "");
462             }
463 
464             sql = CustomSQLUtil.replaceKeywords(
465                 sql, "articleId", StringPool.LIKE, false, articleIds);
466 
467             if (version == null) {
468                 sql = StringUtil.replace(
469                     sql, "(version = ?) [$AND_OR_CONNECTOR$]", "");
470             }
471             else if (version <= 0) {
472                 StringBundler sb = new StringBundler(
473                     "articleId, MAX(version) as version");
474 
475                 for (String field : orderByComparator.getOrderByFields()) {
476                     if (field.equals("articleId") || field.equals("version")) {
477                         continue;
478                     }
479 
480                     sb.append(", ");
481                     sb.append(field);
482                 }
483 
484                 sql = StringUtil.replace(
485                     sql, "id_ AS id", sb.toString());
486 
487                 sql = StringUtil.replace(
488                     sql, "(version = ?) [$AND_OR_CONNECTOR$]", "");
489             }
490 
491             sql = CustomSQLUtil.replaceKeywords(
492                 sql, "lower(title)", StringPool.LIKE, false, titles);
493             sql = CustomSQLUtil.replaceKeywords(
494                 sql, "description", StringPool.LIKE, false, descriptions);
495             sql = CustomSQLUtil.replaceKeywords(
496                 sql, "content", StringPool.LIKE, false, contents);
497             sql = CustomSQLUtil.replaceKeywords(
498                 sql, "structureId", StringPool.EQUAL, false, structureIds);
499             sql = CustomSQLUtil.replaceKeywords(
500                 sql, "templateId", StringPool.EQUAL, false, templateIds);
501 
502             if (status == WorkflowConstants.STATUS_ANY) {
503                 sql = StringUtil.replace(sql, "(status = ?) AND", "");
504             }
505 
506             sql = CustomSQLUtil.replaceAndOperator(sql, andOperator);
507 
508             if ((version != null) && (version <= 0)) {
509                 sql = CustomSQLUtil.replaceGroupBy(sql, "articleId");
510             }
511 
512             sql = CustomSQLUtil.replaceOrderBy(sql, orderByComparator);
513 
514             SQLQuery q = session.createSQLQuery(sql);
515 
516             if ((version != null) && (version <= 0)) {
517                 q.addScalar("articleId", Type.STRING);
518             }
519             else {
520                 q.addScalar("id", Type.LONG);
521             }
522 
523             QueryPos qPos = QueryPos.getInstance(q);
524 
525             qPos.add(companyId);
526 
527             if (groupId > 0) {
528                 qPos.add(groupId);
529             }
530 
531             qPos.add(articleIds, 2);
532 
533             if ((version != null) && (version > 0)) {
534                 qPos.add(version);
535             }
536 
537             qPos.add(titles, 2);
538             qPos.add(descriptions, 2);
539             qPos.add(contents, 2);
540             qPos.add(type);
541             qPos.add(type);
542             qPos.add(structureIds, 2);
543             qPos.add(templateIds, 2);
544             qPos.add(displayDateGT_TS);
545             qPos.add(displayDateGT_TS);
546             qPos.add(displayDateLT_TS);
547             qPos.add(displayDateLT_TS);
548 
549             if (status != WorkflowConstants.STATUS_ANY) {
550                 qPos.add(status);
551             }
552 
553             qPos.add(reviewDate_TS);
554             qPos.add(reviewDate_TS);
555 
556             List<JournalArticle> articles = new ArrayList<JournalArticle>();
557 
558             Iterator<Object[]> itr = q.list().iterator();
559 
560             while (itr.hasNext()) {
561                 Object value = itr.next();
562 
563                 JournalArticle article = null;
564 
565                 if ((version != null) && (version <= 0)) {
566                     String articleId = (String)value;
567 
568                     article = getLatestArticle(groupId, articleId, status);
569                 }
570                 else {
571                     long id = (Long)value;
572 
573                     article = JournalArticleUtil.findByPrimaryKey(id);
574                 }
575 
576                 if (article != null) {
577                     articles.add(article);
578                 }
579             }
580 
581             return articles;
582         }
583         catch (Exception e) {
584             throw new SystemException(e);
585         }
586         finally {
587             closeSession(session);
588         }
589     }
590 
591     protected JournalArticle getLatestArticle(
592             long groupId, String articleId, int status)
593         throws SystemException {
594 
595         List<JournalArticle> articles = null;
596 
597         if (status == WorkflowConstants.STATUS_ANY) {
598             articles = JournalArticleUtil.findByG_A(groupId, articleId, 0, 1);
599         }
600         else {
601             articles = JournalArticleUtil.findByG_A_S(
602                 groupId, articleId, status, 0, 1);
603         }
604 
605         if (articles.isEmpty()) {
606             return null;
607         }
608 
609         return articles.get(0);
610     }
611 
612 }