1   /**
2    * Copyright (c) 2000-2009 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.journal.service.persistence;
24  
25  import com.liferay.portal.SystemException;
26  import com.liferay.portal.kernel.dao.orm.QueryPos;
27  import com.liferay.portal.kernel.dao.orm.QueryUtil;
28  import com.liferay.portal.kernel.dao.orm.SQLQuery;
29  import com.liferay.portal.kernel.dao.orm.Session;
30  import com.liferay.portal.kernel.dao.orm.Type;
31  import com.liferay.portal.kernel.util.CalendarUtil;
32  import com.liferay.portal.kernel.util.OrderByComparator;
33  import com.liferay.portal.kernel.util.StringPool;
34  import com.liferay.portal.kernel.util.StringUtil;
35  import com.liferay.portal.kernel.util.Validator;
36  import com.liferay.portal.service.persistence.impl.BasePersistenceImpl;
37  import com.liferay.portlet.journal.NoSuchArticleException;
38  import com.liferay.portlet.journal.model.JournalArticle;
39  import com.liferay.portlet.journal.model.impl.JournalArticleImpl;
40  import com.liferay.util.dao.orm.CustomSQLUtil;
41  
42  import java.sql.Timestamp;
43  
44  import java.util.Date;
45  import java.util.Iterator;
46  import java.util.List;
47  
48  /**
49   * <a href="JournalArticleFinderImpl.java.html"><b><i>View Source</i></b></a>
50   *
51   * @author Brian Wing Shun Chan
52   * @author Raymond Augé
53   *
54   */
55  public class JournalArticleFinderImpl
56      extends BasePersistenceImpl implements JournalArticleFinder {
57  
58      public static String COUNT_BY_C_G_A_V_T_D_C_T_S_T_D_A_E_R =
59          JournalArticleFinder.class.getName() +
60              ".countByC_G_A_V_T_D_C_T_S_T_D_A_E_R";
61  
62      public static String FIND_BY_EXPIRATION_DATE =
63          JournalArticleFinder.class.getName() + ".findByExpirationDate";
64  
65      public static String FIND_BY_REVIEW_DATE =
66          JournalArticleFinder.class.getName() + ".findByReviewDate";
67  
68      public static String FIND_BY_R_D =
69          JournalArticleFinder.class.getName() + ".findByR_D";
70  
71      public static String FIND_BY_C_G_A_V_T_D_C_T_S_T_D_A_E_R =
72          JournalArticleFinder.class.getName() +
73              ".findByC_G_A_V_T_D_C_T_S_T_D_A_E_R";
74  
75      public int countByKeywords(
76              long companyId, long groupId, String keywords, Double version,
77              String type, String structureId, String templateId,
78              Date displayDateGT, Date displayDateLT, Boolean approved,
79              Boolean expired, Date reviewDate)
80          throws SystemException {
81  
82          String[] articleIds = null;
83          String[] titles = null;
84          String[] descriptions = null;
85          String[] contents = null;
86          boolean andOperator = false;
87  
88          if (Validator.isNotNull(keywords)) {
89              articleIds = CustomSQLUtil.keywords(keywords, false);
90              titles = CustomSQLUtil.keywords(keywords);
91              descriptions = CustomSQLUtil.keywords(keywords, false);
92              contents = CustomSQLUtil.keywords(keywords, false);
93          }
94          else {
95              andOperator = true;
96          }
97  
98          return countByC_G_A_V_T_D_C_T_S_T_D_A_E_R(
99              companyId, groupId, articleIds, version, titles, descriptions,
100             contents, type, new String[] {structureId},
101             new String[] {templateId}, displayDateGT, displayDateLT, approved,
102             expired, reviewDate, andOperator);
103     }
104 
105     public int countByC_G_A_V_T_D_C_T_S_T_D_A_E_R(
106             long companyId, long groupId, String articleId, Double version,
107             String title, String description, String content, String type,
108             String structureId, String templateId, Date displayDateGT,
109             Date displayDateLT, Boolean approved, Boolean expired,
110             Date reviewDate, boolean andOperator)
111         throws SystemException {
112 
113         return countByC_G_A_V_T_D_C_T_S_T_D_A_E_R(
114             companyId, groupId, articleId, version, title, description,
115             content, type, new String[] {structureId},
116             new String[] {templateId}, displayDateGT, displayDateLT, approved,
117             expired, reviewDate, andOperator);
118     }
119 
120     public int countByC_G_A_V_T_D_C_T_S_T_D_A_E_R(
121             long companyId, long groupId, String articleId, Double version,
122             String title, String description, String content, String type,
123             String[] structureIds, String[] templateIds, Date displayDateGT,
124             Date displayDateLT, Boolean approved, Boolean expired,
125             Date reviewDate, boolean andOperator)
126         throws SystemException {
127 
128         return countByC_G_A_V_T_D_C_T_S_T_D_A_E_R(
129             companyId, groupId, new String[] {articleId}, version,
130             new String[] {title}, new String[] {description},
131             new String[] {content}, type, structureIds, templateIds,
132             displayDateGT, displayDateLT, approved, expired, reviewDate,
133             andOperator);
134     }
135 
136     public int countByC_G_A_V_T_D_C_T_S_T_D_A_E_R(
137             long companyId, long groupId, String[] articleIds, Double version,
138             String[] titles, String[] descriptions, String[] contents,
139             String type, String[] structureIds, String[] templateIds,
140             Date displayDateGT, Date displayDateLT, Boolean approved,
141             Boolean expired, Date reviewDate, boolean andOperator)
142         throws SystemException {
143 
144         articleIds = CustomSQLUtil.keywords(articleIds, false);
145         titles = CustomSQLUtil.keywords(titles);
146         descriptions = CustomSQLUtil.keywords(descriptions, false);
147         contents = CustomSQLUtil.keywords(contents, false);
148         structureIds = CustomSQLUtil.keywords(structureIds, false);
149         templateIds = CustomSQLUtil.keywords(templateIds, false);
150         Timestamp displayDateGT_TS = CalendarUtil.getTimestamp(displayDateGT);
151         Timestamp displayDateLT_TS = CalendarUtil.getTimestamp(displayDateLT);
152         Timestamp reviewDate_TS = CalendarUtil.getTimestamp(reviewDate);
153 
154         Session session = null;
155 
156         try {
157             session = openSession();
158 
159             String sql = CustomSQLUtil.get(
160                 COUNT_BY_C_G_A_V_T_D_C_T_S_T_D_A_E_R);
161 
162             if (groupId <= 0) {
163                 sql = StringUtil.replace(sql, "(groupId = ?) AND", "");
164             }
165 
166             sql = CustomSQLUtil.replaceKeywords(
167                 sql, "articleId", StringPool.LIKE, false, articleIds);
168 
169             if (version == null) {
170                 sql = StringUtil.replace(
171                     sql, "(version = ?) [$AND_OR_CONNECTOR$]", "");
172             }
173 
174             sql = CustomSQLUtil.replaceKeywords(
175                 sql, "lower(title)", StringPool.LIKE, false, titles);
176             sql = CustomSQLUtil.replaceKeywords(
177                 sql, "description", StringPool.LIKE, false, descriptions);
178             sql = CustomSQLUtil.replaceKeywords(
179                 sql, "content", StringPool.LIKE, false, contents);
180             sql = CustomSQLUtil.replaceKeywords(
181                 sql, "structureId", StringPool.EQUAL, false, structureIds);
182             sql = CustomSQLUtil.replaceKeywords(
183                 sql, "templateId", StringPool.EQUAL, false, templateIds);
184 
185             if (approved == null) {
186                 sql = StringUtil.replace(sql, "(approved = ?) AND", "");
187             }
188 
189             if (expired == null) {
190                 sql = StringUtil.replace(sql, "(expired = ?) AND", "");
191             }
192 
193             sql = CustomSQLUtil.replaceAndOperator(sql, andOperator);
194 
195             SQLQuery q = session.createSQLQuery(sql);
196 
197             q.addScalar(COUNT_COLUMN_NAME, Type.LONG);
198 
199             QueryPos qPos = QueryPos.getInstance(q);
200 
201             qPos.add(companyId);
202 
203             if (groupId > 0) {
204                 qPos.add(groupId);
205             }
206 
207             qPos.add(articleIds, 2);
208 
209             if (version != null) {
210                 qPos.add(version);
211             }
212 
213             qPos.add(titles, 2);
214             qPos.add(descriptions, 2);
215             qPos.add(contents, 2);
216             qPos.add(type);
217             qPos.add(type);
218             qPos.add(structureIds, 2);
219             qPos.add(templateIds, 2);
220             qPos.add(displayDateGT_TS);
221             qPos.add(displayDateGT_TS);
222             qPos.add(displayDateLT_TS);
223             qPos.add(displayDateLT_TS);
224 
225             if (approved != null) {
226                 qPos.add(approved);
227             }
228 
229             if (expired != null) {
230                 qPos.add(expired);
231             }
232 
233             qPos.add(reviewDate_TS);
234             qPos.add(reviewDate_TS);
235 
236             Iterator<Long> itr = q.list().iterator();
237 
238             if (itr.hasNext()) {
239                 Long count = itr.next();
240 
241                 if (count != null) {
242                     return count.intValue();
243                 }
244             }
245 
246             return 0;
247         }
248         catch (Exception e) {
249             throw new SystemException(e);
250         }
251         finally {
252             closeSession(session);
253         }
254     }
255 
256     public List<JournalArticle> findByExpirationDate(
257             Boolean expired, Date expirationDateLT, Date expirationDateGT)
258         throws SystemException {
259 
260         Timestamp expirationDateLT_TS = CalendarUtil.getTimestamp(
261             expirationDateLT);
262         Timestamp expirationDateGT_TS = CalendarUtil.getTimestamp(
263             expirationDateGT);
264 
265         Session session = null;
266         try {
267             session = openSession();
268 
269             String sql = CustomSQLUtil.get(FIND_BY_EXPIRATION_DATE);
270 
271             if (expired == null) {
272                 sql = StringUtil.replace(sql, "(expired = ?) AND", "");
273             }
274 
275             SQLQuery q = session.createSQLQuery(sql);
276 
277             q.addEntity("JournalArticle", JournalArticleImpl.class);
278 
279             QueryPos qPos = QueryPos.getInstance(q);
280 
281             if (expired != null) {
282                 qPos.add(expired);
283             }
284 
285             qPos.add(expirationDateGT_TS);
286             qPos.add(expirationDateLT_TS);
287 
288             return q.list();
289         }
290         catch (Exception e) {
291             throw new SystemException(e);
292         }
293         finally {
294             closeSession(session);
295         }
296     }
297 
298     public List<JournalArticle> findByKeywords(
299             long companyId, long groupId, String keywords, Double version,
300             String type, String structureId, String templateId,
301             Date displayDateGT, Date displayDateLT, Boolean approved,
302             Boolean expired, Date reviewDate, int start, int end,
303             OrderByComparator obc)
304         throws SystemException {
305 
306         String[] articleIds = null;
307         String[] titles = null;
308         String[] descriptions = null;
309         String[] contents = null;
310         boolean andOperator = false;
311 
312         if (Validator.isNotNull(keywords)) {
313             articleIds = CustomSQLUtil.keywords(keywords, false);
314             titles = CustomSQLUtil.keywords(keywords);
315             descriptions = CustomSQLUtil.keywords(keywords, false);
316             contents = CustomSQLUtil.keywords(keywords, false);
317         }
318         else {
319             andOperator = true;
320         }
321 
322         return findByC_G_A_V_T_D_C_T_S_T_D_A_E_R(
323             companyId, groupId, articleIds, version, titles, descriptions,
324             contents, type, new String[] {structureId},
325             new String[] {templateId}, displayDateGT, displayDateLT, approved,
326             expired, reviewDate, andOperator, start, end, obc);
327     }
328 
329     public List<JournalArticle> findByReviewDate(
330             Date reviewDateLT, Date reviewDateGT)
331         throws SystemException {
332 
333         Timestamp reviewDateLT_TS = CalendarUtil.getTimestamp(reviewDateLT);
334         Timestamp reviewDateGT_TS = CalendarUtil.getTimestamp(reviewDateGT);
335 
336         Session session = null;
337         try {
338             session = openSession();
339 
340             String sql = CustomSQLUtil.get(FIND_BY_REVIEW_DATE);
341 
342             SQLQuery q = session.createSQLQuery(sql);
343 
344             q.addEntity("JournalArticle", JournalArticleImpl.class);
345 
346             QueryPos qPos = QueryPos.getInstance(q);
347 
348             qPos.add(reviewDateGT_TS);
349             qPos.add(reviewDateLT_TS);
350 
351             return q.list();
352         }
353         catch (Exception e) {
354             throw new SystemException(e);
355         }
356         finally {
357             closeSession(session);
358         }
359     }
360 
361     public JournalArticle findByR_D(long resourcePrimKey, Date displayDate)
362         throws NoSuchArticleException, SystemException {
363 
364         Timestamp displayDate_TS = CalendarUtil.getTimestamp(displayDate);
365 
366         Session session = null;
367 
368         try {
369             session = openSession();
370 
371             String sql = CustomSQLUtil.get(FIND_BY_R_D);
372 
373             SQLQuery q = session.createSQLQuery(sql);
374 
375             q.addEntity("JournalArticle", JournalArticleImpl.class);
376 
377             QueryPos qPos = QueryPos.getInstance(q);
378 
379             qPos.add(resourcePrimKey);
380             qPos.add(displayDate_TS);
381 
382             List<JournalArticle> list = q.list();
383 
384             if (list.size() == 0) {
385                 StringBuilder sb = new StringBuilder();
386 
387                 sb.append("No JournalArticle exists with the key ");
388                 sb.append("{resourcePrimKey=");
389                 sb.append(resourcePrimKey);
390                 sb.append(", displayDate=");
391                 sb.append(displayDate);
392                 sb.append("}");
393 
394                 throw new NoSuchArticleException(sb.toString());
395             }
396             else {
397                 return list.get(0);
398             }
399         }
400         catch (NoSuchArticleException nsae) {
401             throw nsae;
402         }
403         catch (Exception e) {
404             throw new SystemException(e);
405         }
406         finally {
407             closeSession(session);
408         }
409     }
410 
411     public List<JournalArticle> findByC_G_A_V_T_D_C_T_S_T_D_A_E_R(
412             long companyId, long groupId, String articleId, Double version,
413             String title, String description, String content, String type,
414             String structureId, String templateId, Date displayDateGT,
415             Date displayDateLT, Boolean approved, Boolean expired,
416             Date reviewDate, boolean andOperator, int start, int end,
417             OrderByComparator obc)
418         throws SystemException {
419 
420         return findByC_G_A_V_T_D_C_T_S_T_D_A_E_R(
421             companyId, groupId, articleId, version, title, description,
422             content, type, new String[] {structureId},
423             new String[] {templateId}, displayDateGT, displayDateLT, approved,
424             expired, reviewDate, andOperator, start, end, obc);
425     }
426 
427     public List<JournalArticle> findByC_G_A_V_T_D_C_T_S_T_D_A_E_R(
428             long companyId, long groupId, String articleId, Double version,
429             String title, String description, String content, String type,
430             String[] structureIds, String[] templateIds, Date displayDateGT,
431             Date displayDateLT, Boolean approved, Boolean expired,
432             Date reviewDate, boolean andOperator, int start, int end,
433             OrderByComparator obc)
434         throws SystemException {
435 
436         return findByC_G_A_V_T_D_C_T_S_T_D_A_E_R(
437             companyId, groupId, new String[] {articleId}, version,
438             new String[] {title}, new String[] {description},
439             new String[] {content}, type, structureIds, templateIds,
440             displayDateGT, displayDateLT, approved, expired, reviewDate,
441             andOperator, start, end, obc);
442     }
443 
444     public List<JournalArticle> findByC_G_A_V_T_D_C_T_S_T_D_A_E_R(
445             long companyId, long groupId, String[] articleIds, Double version,
446             String[] titles, String[] descriptions, String[] contents,
447             String type, String[] structureIds, String[] templateIds,
448             Date displayDateGT, Date displayDateLT, Boolean approved,
449             Boolean expired, Date reviewDate, boolean andOperator, int start,
450             int end, OrderByComparator obc)
451         throws SystemException {
452 
453         articleIds = CustomSQLUtil.keywords(articleIds, false);
454         titles = CustomSQLUtil.keywords(titles);
455         descriptions = CustomSQLUtil.keywords(descriptions, false);
456         contents = CustomSQLUtil.keywords(contents, false);
457         structureIds = CustomSQLUtil.keywords(structureIds, false);
458         templateIds = CustomSQLUtil.keywords(templateIds, false);
459         Timestamp displayDateGT_TS = CalendarUtil.getTimestamp(displayDateGT);
460         Timestamp displayDateLT_TS = CalendarUtil.getTimestamp(displayDateLT);
461         Timestamp reviewDate_TS = CalendarUtil.getTimestamp(reviewDate);
462 
463         Session session = null;
464 
465         try {
466             session = openSession();
467 
468             String sql = CustomSQLUtil.get(FIND_BY_C_G_A_V_T_D_C_T_S_T_D_A_E_R);
469 
470             if (groupId <= 0) {
471                 sql = StringUtil.replace(sql, "(groupId = ?) AND", "");
472             }
473 
474             sql = CustomSQLUtil.replaceKeywords(
475                 sql, "articleId", StringPool.LIKE, false, articleIds);
476 
477             if (version == null) {
478                 sql = StringUtil.replace(
479                     sql, "(version = ?) [$AND_OR_CONNECTOR$]", "");
480             }
481 
482             sql = CustomSQLUtil.replaceKeywords(
483                 sql, "lower(title)", StringPool.LIKE, false, titles);
484             sql = CustomSQLUtil.replaceKeywords(
485                 sql, "description", StringPool.LIKE, false, descriptions);
486             sql = CustomSQLUtil.replaceKeywords(
487                 sql, "content", StringPool.LIKE, false, contents);
488             sql = CustomSQLUtil.replaceKeywords(
489                 sql, "structureId", StringPool.EQUAL, false, structureIds);
490             sql = CustomSQLUtil.replaceKeywords(
491                 sql, "templateId", StringPool.EQUAL, false, templateIds);
492 
493             if (approved == null) {
494                 sql = StringUtil.replace(sql, "(approved = ?) AND", "");
495             }
496 
497             if (expired == null) {
498                 sql = StringUtil.replace(sql, "(expired = ?) AND", "");
499             }
500 
501             sql = CustomSQLUtil.replaceAndOperator(sql, andOperator);
502             sql = CustomSQLUtil.replaceOrderBy(sql, obc);
503 
504             SQLQuery q = session.createSQLQuery(sql);
505 
506             q.addEntity("JournalArticle", JournalArticleImpl.class);
507 
508             QueryPos qPos = QueryPos.getInstance(q);
509 
510             qPos.add(companyId);
511 
512             if (groupId > 0) {
513                 qPos.add(groupId);
514             }
515 
516             qPos.add(articleIds, 2);
517 
518             if (version != null) {
519                 qPos.add(version);
520             }
521 
522             qPos.add(titles, 2);
523             qPos.add(descriptions, 2);
524             qPos.add(contents, 2);
525             qPos.add(type);
526             qPos.add(type);
527             qPos.add(structureIds, 2);
528             qPos.add(templateIds, 2);
529             qPos.add(displayDateGT_TS);
530             qPos.add(displayDateGT_TS);
531             qPos.add(displayDateLT_TS);
532             qPos.add(displayDateLT_TS);
533 
534             if (approved != null) {
535                 qPos.add(approved);
536             }
537 
538             if (expired != null) {
539                 qPos.add(expired);
540             }
541 
542             qPos.add(reviewDate_TS);
543             qPos.add(reviewDate_TS);
544 
545             return (List<JournalArticle>)QueryUtil.list(
546                 q, getDialect(), start, end);
547         }
548         catch (Exception e) {
549             throw new SystemException(e);
550         }
551         finally {
552             closeSession(session);
553         }
554     }
555 
556 }