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