001    /**
002     * Copyright (c) 2000-2010 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.journal.service.persistence;
016    
017    import com.liferay.portal.kernel.dao.orm.QueryPos;
018    import com.liferay.portal.kernel.dao.orm.QueryUtil;
019    import com.liferay.portal.kernel.dao.orm.SQLQuery;
020    import com.liferay.portal.kernel.dao.orm.Session;
021    import com.liferay.portal.kernel.dao.orm.Type;
022    import com.liferay.portal.kernel.exception.SystemException;
023    import com.liferay.portal.kernel.util.CalendarUtil;
024    import com.liferay.portal.kernel.util.OrderByComparator;
025    import com.liferay.portal.kernel.util.StringBundler;
026    import com.liferay.portal.kernel.util.StringPool;
027    import com.liferay.portal.kernel.util.StringUtil;
028    import com.liferay.portal.kernel.util.Validator;
029    import com.liferay.portal.kernel.workflow.WorkflowConstants;
030    import com.liferay.portal.service.persistence.impl.BasePersistenceImpl;
031    import com.liferay.portlet.journal.NoSuchArticleException;
032    import com.liferay.portlet.journal.model.JournalArticle;
033    import com.liferay.portlet.journal.model.impl.JournalArticleImpl;
034    import com.liferay.util.dao.orm.CustomSQLUtil;
035    
036    import java.sql.Timestamp;
037    
038    import java.util.ArrayList;
039    import java.util.Date;
040    import java.util.Iterator;
041    import java.util.List;
042    
043    /**
044     * @author Brian Wing Shun Chan
045     * @author Raymond Augé
046     */
047    public class JournalArticleFinderImpl
048            extends BasePersistenceImpl<JournalArticle>
049            implements JournalArticleFinder {
050    
051            public static String COUNT_BY_C_G_A_V_T_D_C_T_S_T_D_S_R =
052                    JournalArticleFinder.class.getName() +
053                            ".countByC_G_A_V_T_D_C_T_S_T_D_S_R";
054    
055            public static String FIND_BY_EXPIRATION_DATE =
056                    JournalArticleFinder.class.getName() + ".findByExpirationDate";
057    
058            public static String FIND_BY_REVIEW_DATE =
059                    JournalArticleFinder.class.getName() + ".findByReviewDate";
060    
061            public static String FIND_BY_R_D =
062                    JournalArticleFinder.class.getName() + ".findByR_D";
063    
064            public static String FIND_BY_C_G_A_V_T_D_C_T_S_T_D_S_R =
065                    JournalArticleFinder.class.getName() +
066                            ".findByC_G_A_V_T_D_C_T_S_T_D_S_R";
067    
068            public int countByKeywords(
069                            long companyId, long groupId, String keywords, Double version,
070                            String type, String structureId, String templateId,
071                            Date displayDateGT, Date displayDateLT, int status, Date reviewDate)
072                    throws SystemException {
073    
074                    String[] articleIds = null;
075                    String[] titles = null;
076                    String[] descriptions = null;
077                    String[] contents = null;
078                    boolean andOperator = false;
079    
080                    if (Validator.isNotNull(keywords)) {
081                            articleIds = CustomSQLUtil.keywords(keywords, false);
082                            titles = CustomSQLUtil.keywords(keywords);
083                            descriptions = CustomSQLUtil.keywords(keywords, false);
084                            contents = CustomSQLUtil.keywords(keywords, false);
085                    }
086                    else {
087                            andOperator = true;
088                    }
089    
090                    return countByC_G_A_V_T_D_C_T_S_T_D_S_R(
091                            companyId, groupId, articleIds, version, titles, descriptions,
092                            contents, type, new String[] {structureId},
093                            new String[] {templateId}, displayDateGT, displayDateLT, status,
094                            reviewDate, andOperator);
095            }
096    
097            public int countByC_G_A_V_T_D_C_T_S_T_D_S_R(
098                            long companyId, long groupId, String articleId, Double version,
099                            String title, String description, String content, String type,
100                            String structureId, String templateId, Date displayDateGT,
101                            Date displayDateLT, int status, Date reviewDate,
102                            boolean andOperator)
103                    throws SystemException {
104    
105                    return countByC_G_A_V_T_D_C_T_S_T_D_S_R(
106                            companyId, groupId, articleId, version, title, description,
107                            content, type, new String[] {structureId},
108                            new String[] {templateId}, displayDateGT, displayDateLT, status,
109                            reviewDate, andOperator);
110            }
111    
112            public int countByC_G_A_V_T_D_C_T_S_T_D_S_R(
113                            long companyId, long groupId, String articleId, Double version,
114                            String title, String description, String content, String type,
115                            String[] structureIds, String[] templateIds, Date displayDateGT,
116                            Date displayDateLT, int status, Date reviewDate,
117                            boolean andOperator)
118                    throws SystemException {
119    
120                    return countByC_G_A_V_T_D_C_T_S_T_D_S_R(
121                            companyId, groupId, new String[] {articleId}, version,
122                            new String[] {title}, new String[] {description},
123                            new String[] {content}, type, structureIds, templateIds,
124                            displayDateGT, displayDateLT, status, reviewDate, andOperator);
125            }
126    
127            public int countByC_G_A_V_T_D_C_T_S_T_D_S_R(
128                            long companyId, long groupId, String[] articleIds, Double version,
129                            String[] titles, String[] descriptions, String[] contents,
130                            String type, String[] structureIds, String[] templateIds,
131                            Date displayDateGT, Date displayDateLT, int status, Date reviewDate,
132                            boolean andOperator)
133                    throws SystemException {
134    
135                    articleIds = CustomSQLUtil.keywords(articleIds, false);
136                    titles = CustomSQLUtil.keywords(titles);
137                    descriptions = CustomSQLUtil.keywords(descriptions, false);
138                    contents = CustomSQLUtil.keywords(contents, false);
139                    structureIds = CustomSQLUtil.keywords(structureIds, false);
140                    templateIds = CustomSQLUtil.keywords(templateIds, false);
141                    Timestamp displayDateGT_TS = CalendarUtil.getTimestamp(displayDateGT);
142                    Timestamp displayDateLT_TS = CalendarUtil.getTimestamp(displayDateLT);
143                    Timestamp reviewDate_TS = CalendarUtil.getTimestamp(reviewDate);
144    
145                    Session session = null;
146    
147                    try {
148                            session = openSession();
149    
150                            String sql = CustomSQLUtil.get(COUNT_BY_C_G_A_V_T_D_C_T_S_T_D_S_R);
151    
152                            if (groupId <= 0) {
153                                    sql = StringUtil.replace(sql, "(groupId = ?) AND", "");
154                            }
155    
156                            sql = CustomSQLUtil.replaceKeywords(
157                                    sql, "articleId", StringPool.LIKE, false, articleIds);
158    
159                            if (version == null) {
160                                    sql = StringUtil.replace(
161                                            sql, "(version = ?) [$AND_OR_CONNECTOR$]", "");
162                            }
163                            else if (version <= 0) {
164                                    sql = StringUtil.replace(
165                                            sql, "COUNT(*", "COUNT(DISTINCT articleId");
166    
167                                    sql = StringUtil.replace(
168                                            sql, "(version = ?) [$AND_OR_CONNECTOR$]", "");
169                            }
170    
171                            sql = CustomSQLUtil.replaceKeywords(
172                                    sql, "lower(title)", StringPool.LIKE, false, titles);
173                            sql = CustomSQLUtil.replaceKeywords(
174                                    sql, "description", StringPool.LIKE, false, descriptions);
175                            sql = CustomSQLUtil.replaceKeywords(
176                                    sql, "content", StringPool.LIKE, false, contents);
177                            sql = CustomSQLUtil.replaceKeywords(
178                                    sql, "structureId", StringPool.EQUAL, false, structureIds);
179                            sql = CustomSQLUtil.replaceKeywords(
180                                    sql, "templateId", StringPool.EQUAL, false, templateIds);
181    
182                            if (status == WorkflowConstants.STATUS_ANY) {
183                                    sql = StringUtil.replace(sql, "(status = ?) AND", "");
184                            }
185    
186                            sql = CustomSQLUtil.replaceAndOperator(sql, andOperator);
187    
188                            SQLQuery q = session.createSQLQuery(sql);
189    
190                            q.addScalar(COUNT_COLUMN_NAME, Type.LONG);
191    
192                            QueryPos qPos = QueryPos.getInstance(q);
193    
194                            qPos.add(companyId);
195    
196                            if (groupId > 0) {
197                                    qPos.add(groupId);
198                            }
199    
200                            qPos.add(articleIds, 2);
201    
202                            if ((version != null) && (version > 0)) {
203                                    qPos.add(version);
204                            }
205    
206                            qPos.add(titles, 2);
207                            qPos.add(descriptions, 2);
208                            qPos.add(contents, 2);
209                            qPos.add(type);
210                            qPos.add(type);
211                            qPos.add(structureIds, 2);
212                            qPos.add(templateIds, 2);
213                            qPos.add(displayDateGT_TS);
214                            qPos.add(displayDateGT_TS);
215                            qPos.add(displayDateLT_TS);
216                            qPos.add(displayDateLT_TS);
217    
218                            if (status != WorkflowConstants.STATUS_ANY) {
219                                    qPos.add(status);
220                            }
221    
222                            qPos.add(reviewDate_TS);
223                            qPos.add(reviewDate_TS);
224    
225                            Iterator<Long> itr = q.list().iterator();
226    
227                            if (itr.hasNext()) {
228                                    Long count = itr.next();
229    
230                                    if (count != null) {
231                                            return count.intValue();
232                                    }
233                            }
234    
235                            return 0;
236                    }
237                    catch (Exception e) {
238                            throw new SystemException(e);
239                    }
240                    finally {
241                            closeSession(session);
242                    }
243            }
244    
245            public List<JournalArticle> findByExpirationDate(
246                            int status, Date expirationDateLT, Date expirationDateGT)
247                    throws SystemException {
248    
249                    Timestamp expirationDateLT_TS = CalendarUtil.getTimestamp(
250                            expirationDateLT);
251                    Timestamp expirationDateGT_TS = CalendarUtil.getTimestamp(
252                            expirationDateGT);
253    
254                    Session session = null;
255    
256                    try {
257                            session = openSession();
258    
259                            String sql = CustomSQLUtil.get(FIND_BY_EXPIRATION_DATE);
260    
261                            if (status == WorkflowConstants.STATUS_ANY) {
262                                    sql = StringUtil.replace(sql, "(status = ?) AND", "");
263                            }
264    
265                            SQLQuery q = session.createSQLQuery(sql);
266    
267                            q.addEntity("JournalArticle", JournalArticleImpl.class);
268    
269                            QueryPos qPos = QueryPos.getInstance(q);
270    
271                            if (status != WorkflowConstants.STATUS_ANY) {
272                                    qPos.add(status);
273                            }
274    
275                            qPos.add(expirationDateGT_TS);
276                            qPos.add(expirationDateLT_TS);
277    
278                            return q.list();
279                    }
280                    catch (Exception e) {
281                            throw new SystemException(e);
282                    }
283                    finally {
284                            closeSession(session);
285                    }
286            }
287    
288            public List<JournalArticle> findByKeywords(
289                            long companyId, long groupId, String keywords, Double version,
290                            String type, String structureId, String templateId,
291                            Date displayDateGT, Date displayDateLT, int status, Date reviewDate,
292                            int start, int end, OrderByComparator orderByComparator)
293                    throws SystemException {
294    
295                    String[] articleIds = null;
296                    String[] titles = null;
297                    String[] descriptions = null;
298                    String[] contents = null;
299                    boolean andOperator = false;
300    
301                    if (Validator.isNotNull(keywords)) {
302                            articleIds = CustomSQLUtil.keywords(keywords, false);
303                            titles = CustomSQLUtil.keywords(keywords);
304                            descriptions = CustomSQLUtil.keywords(keywords, false);
305                            contents = CustomSQLUtil.keywords(keywords, false);
306                    }
307                    else {
308                            andOperator = true;
309                    }
310    
311                    return findByC_G_A_V_T_D_C_T_S_T_D_S_R(
312                            companyId, groupId, articleIds, version, titles, descriptions,
313                            contents, type, new String[] {structureId},
314                            new String[] {templateId}, displayDateGT, displayDateLT, status,
315                            reviewDate, andOperator, start, end, orderByComparator);
316            }
317    
318            public List<JournalArticle> findByReviewDate(
319                            Date reviewDateLT, Date reviewDateGT)
320                    throws SystemException {
321    
322                    Timestamp reviewDateLT_TS = CalendarUtil.getTimestamp(reviewDateLT);
323                    Timestamp reviewDateGT_TS = CalendarUtil.getTimestamp(reviewDateGT);
324    
325                    Session session = null;
326                    try {
327                            session = openSession();
328    
329                            String sql = CustomSQLUtil.get(FIND_BY_REVIEW_DATE);
330    
331                            SQLQuery q = session.createSQLQuery(sql);
332    
333                            q.addEntity("JournalArticle", JournalArticleImpl.class);
334    
335                            QueryPos qPos = QueryPos.getInstance(q);
336    
337                            qPos.add(reviewDateGT_TS);
338                            qPos.add(reviewDateLT_TS);
339    
340                            return q.list();
341                    }
342                    catch (Exception e) {
343                            throw new SystemException(e);
344                    }
345                    finally {
346                            closeSession(session);
347                    }
348            }
349    
350            public JournalArticle findByR_D(long resourcePrimKey, Date displayDate)
351                    throws NoSuchArticleException, SystemException {
352    
353                    Timestamp displayDate_TS = CalendarUtil.getTimestamp(displayDate);
354    
355                    Session session = null;
356    
357                    try {
358                            session = openSession();
359    
360                            String sql = CustomSQLUtil.get(FIND_BY_R_D);
361    
362                            SQLQuery q = session.createSQLQuery(sql);
363    
364                            q.addEntity("JournalArticle", JournalArticleImpl.class);
365    
366                            QueryPos qPos = QueryPos.getInstance(q);
367    
368                            qPos.add(resourcePrimKey);
369                            qPos.add(displayDate_TS);
370    
371                            List<JournalArticle> list = q.list();
372    
373                            if (list.size() == 0) {
374                                    StringBundler sb = new StringBundler(6);
375    
376                                    sb.append("No JournalArticle exists with the key ");
377                                    sb.append("{resourcePrimKey=");
378                                    sb.append(resourcePrimKey);
379                                    sb.append(", displayDate=");
380                                    sb.append(displayDate);
381                                    sb.append("}");
382    
383                                    throw new NoSuchArticleException(sb.toString());
384                            }
385                            else {
386                                    return list.get(0);
387                            }
388                    }
389                    catch (NoSuchArticleException nsae) {
390                            throw nsae;
391                    }
392                    catch (Exception e) {
393                            throw new SystemException(e);
394                    }
395                    finally {
396                            closeSession(session);
397                    }
398            }
399    
400            public List<JournalArticle> findByC_G_A_V_T_D_C_T_S_T_D_S_R(
401                            long companyId, long groupId, String articleId, Double version,
402                            String title, String description, String content, String type,
403                            String structureId, String templateId, Date displayDateGT,
404                            Date displayDateLT, int status, Date reviewDate,
405                            boolean andOperator, int start, int end,
406                            OrderByComparator orderByComparator)
407                    throws SystemException {
408    
409                    return findByC_G_A_V_T_D_C_T_S_T_D_S_R(
410                            companyId, groupId, articleId, version, title, description,
411                            content, type, new String[] {structureId},
412                            new String[] {templateId}, displayDateGT, displayDateLT, status,
413                            reviewDate, andOperator, start, end, orderByComparator);
414            }
415    
416            public List<JournalArticle> findByC_G_A_V_T_D_C_T_S_T_D_S_R(
417                            long companyId, long groupId, String articleId, Double version,
418                            String title, String description, String content, String type,
419                            String[] structureIds, String[] templateIds, Date displayDateGT,
420                            Date displayDateLT, int status, Date reviewDate,
421                            boolean andOperator, int start, int end,
422                            OrderByComparator orderByComparator)
423                    throws SystemException {
424    
425                    return findByC_G_A_V_T_D_C_T_S_T_D_S_R(
426                            companyId, groupId, new String[] {articleId}, version,
427                            new String[] {title}, new String[] {description},
428                            new String[] {content}, type, structureIds, templateIds,
429                            displayDateGT, displayDateLT, status, reviewDate, andOperator,
430                            start, end, orderByComparator);
431            }
432    
433            public List<JournalArticle> findByC_G_A_V_T_D_C_T_S_T_D_S_R(
434                            long companyId, long groupId, String[] articleIds, Double version,
435                            String[] titles, String[] descriptions, String[] contents,
436                            String type, String[] structureIds, String[] templateIds,
437                            Date displayDateGT, Date displayDateLT, int status,
438                            Date reviewDate, boolean andOperator, int start, int end,
439                            OrderByComparator orderByComparator)
440                    throws SystemException {
441    
442                    articleIds = CustomSQLUtil.keywords(articleIds, false);
443                    titles = CustomSQLUtil.keywords(titles);
444                    descriptions = CustomSQLUtil.keywords(descriptions, false);
445                    contents = CustomSQLUtil.keywords(contents, false);
446                    structureIds = CustomSQLUtil.keywords(structureIds, false);
447                    templateIds = CustomSQLUtil.keywords(templateIds, false);
448                    Timestamp displayDateGT_TS = CalendarUtil.getTimestamp(displayDateGT);
449                    Timestamp displayDateLT_TS = CalendarUtil.getTimestamp(displayDateLT);
450                    Timestamp reviewDate_TS = CalendarUtil.getTimestamp(reviewDate);
451    
452                    Session session = null;
453    
454                    try {
455                            session = openSession();
456    
457                            String sql = CustomSQLUtil.get(FIND_BY_C_G_A_V_T_D_C_T_S_T_D_S_R);
458    
459                            if (groupId <= 0) {
460                                    sql = StringUtil.replace(sql, "(groupId = ?) AND", "");
461                            }
462    
463                            sql = CustomSQLUtil.replaceKeywords(
464                                    sql, "articleId", StringPool.LIKE, false, articleIds);
465    
466                            String groupBy = "articleId";
467    
468                            if (version == null) {
469                                    sql = StringUtil.replace(
470                                            sql, "(version = ?) [$AND_OR_CONNECTOR$]", "");
471                            }
472                            else if (version <= 0) {
473                                    StringBundler sb = new StringBundler(groupBy);
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                                    groupBy = sb.toString();
485    
486                                    sb.append(", MAX(version) as version");
487    
488                                    sql = StringUtil.replace(sql, "id_ AS id", sb.toString());
489    
490                                    sql = StringUtil.replace(
491                                            sql, "(version = ?) [$AND_OR_CONNECTOR$]", "");
492                            }
493    
494                            sql = CustomSQLUtil.replaceKeywords(
495                                    sql, "lower(title)", StringPool.LIKE, false, titles);
496                            sql = CustomSQLUtil.replaceKeywords(
497                                    sql, "description", StringPool.LIKE, false, descriptions);
498                            sql = CustomSQLUtil.replaceKeywords(
499                                    sql, "content", StringPool.LIKE, false, contents);
500                            sql = CustomSQLUtil.replaceKeywords(
501                                    sql, "structureId", StringPool.EQUAL, false, structureIds);
502                            sql = CustomSQLUtil.replaceKeywords(
503                                    sql, "templateId", StringPool.EQUAL, false, templateIds);
504    
505                            if (status == WorkflowConstants.STATUS_ANY) {
506                                    sql = StringUtil.replace(sql, "(status = ?) AND", "");
507                            }
508    
509                            sql = CustomSQLUtil.replaceAndOperator(sql, andOperator);
510    
511                            if ((version != null) && (version <= 0)) {
512                                    sql = CustomSQLUtil.replaceGroupBy(sql, groupBy);
513                            }
514    
515                            sql = CustomSQLUtil.replaceOrderBy(sql, orderByComparator);
516    
517                            SQLQuery q = session.createSQLQuery(sql);
518    
519                            if ((version != null) && (version <= 0)) {
520                                    q.addScalar("articleId", Type.STRING);
521                            }
522                            else {
523                                    q.addScalar("id", Type.LONG);
524                            }
525    
526                            QueryPos qPos = QueryPos.getInstance(q);
527    
528                            qPos.add(companyId);
529    
530                            if (groupId > 0) {
531                                    qPos.add(groupId);
532                            }
533    
534                            qPos.add(articleIds, 2);
535    
536                            if ((version != null) && (version > 0)) {
537                                    qPos.add(version);
538                            }
539    
540                            qPos.add(titles, 2);
541                            qPos.add(descriptions, 2);
542                            qPos.add(contents, 2);
543                            qPos.add(type);
544                            qPos.add(type);
545                            qPos.add(structureIds, 2);
546                            qPos.add(templateIds, 2);
547                            qPos.add(displayDateGT_TS);
548                            qPos.add(displayDateGT_TS);
549                            qPos.add(displayDateLT_TS);
550                            qPos.add(displayDateLT_TS);
551    
552                            if (status != WorkflowConstants.STATUS_ANY) {
553                                    qPos.add(status);
554                            }
555    
556                            qPos.add(reviewDate_TS);
557                            qPos.add(reviewDate_TS);
558    
559                            List<JournalArticle> articles = new ArrayList<JournalArticle>();
560    
561                            Iterator<Object[]> itr = (Iterator<Object[]>)QueryUtil.iterate(
562                                    q, getDialect(), start, end);
563    
564                            while (itr.hasNext()) {
565                                    Object value = itr.next();
566    
567                                    JournalArticle article = null;
568    
569                                    if ((version != null) && (version <= 0)) {
570                                            String articleId = (String)value;
571    
572                                            article = getLatestArticle(groupId, articleId, status);
573                                    }
574                                    else {
575                                            long id = (Long)value;
576    
577                                            article = JournalArticleUtil.findByPrimaryKey(id);
578                                    }
579    
580                                    if (article != null) {
581                                            articles.add(article);
582                                    }
583                            }
584    
585                            return articles;
586                    }
587                    catch (Exception e) {
588                            throw new SystemException(e);
589                    }
590                    finally {
591                            closeSession(session);
592                    }
593            }
594    
595            protected JournalArticle getLatestArticle(
596                            long groupId, String articleId, int status)
597                    throws SystemException {
598    
599                    List<JournalArticle> articles = null;
600    
601                    if (status == WorkflowConstants.STATUS_ANY) {
602                            articles = JournalArticleUtil.findByG_A(groupId, articleId, 0, 1);
603                    }
604                    else {
605                            articles = JournalArticleUtil.findByG_A_ST(
606                                    groupId, articleId, status, 0, 1);
607                    }
608    
609                    if (articles.isEmpty()) {
610                            return null;
611                    }
612    
613                    return articles.get(0);
614            }
615    
616    }