View Javadoc
1   /*
2    * Copyright (c) 2002-2020, City of Paris
3    * All rights reserved.
4    *
5    * Redistribution and use in source and binary forms, with or without
6    * modification, are permitted provided that the following conditions
7    * are met:
8    *
9    *  1. Redistributions of source code must retain the above copyright notice
10   *     and the following disclaimer.
11   *
12   *  2. Redistributions in binary form must reproduce the above copyright notice
13   *     and the following disclaimer in the documentation and/or other materials
14   *     provided with the distribution.
15   *
16   *  3. Neither the name of 'Mairie de Paris' nor 'Lutece' nor the names of its
17   *     contributors may be used to endorse or promote products derived from
18   *     this software without specific prior written permission.
19   *
20   * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
21   * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
22   * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
23   * ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDERS OR CONTRIBUTORS BE
24   * LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
25   * CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
26   * SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
27   * INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
28   * CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
29   * ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
30   * POSSIBILITY OF SUCH DAMAGE.
31   *
32   * License 1.0
33   */
34  package fr.paris.lutece.plugins.blog.business.portlet;
35  
36  import java.util.Collection;
37  
38  import fr.paris.lutece.portal.business.portlet.Portlet;
39  import fr.paris.lutece.portal.service.plugin.Plugin;
40  import fr.paris.lutece.util.ReferenceItem;
41  import fr.paris.lutece.util.ReferenceList;
42  import fr.paris.lutece.util.sql.DAOUtil;
43  
44  /**
45   * this class provides Data Access methods for BlogsPortlet objects
46   */
47  public final class BlogPortletDAO implements IBlogPortletDAO
48  {
49      // Constants
50  
51      private static final String SQL_QUERY_SELECTALL = "SELECT id_portlet, name, content_id, id_page_template_document FROM blog_portlet";
52      private static final String SQL_QUERY_SELECT = "SELECT id_portlet, name, content_id,id_page_template_document  FROM blog_portlet WHERE id_portlet = ? ";
53      private static final String SQL_QUERY_INSERT = "INSERT INTO blog_portlet ( id_portlet, name, content_id, id_page_template_document ) VALUES ( ?, ?, ?, ? )";
54      private static final String SQL_QUERY_DELETE = "DELETE FROM blog_portlet WHERE id_portlet = ? ";
55      private static final String SQL_QUERY_UPDATE = "UPDATE blog_portlet SET id_portlet = ?, name = ?, content_id = ?, id_page_template_document=? WHERE id_portlet = ? ";
56      private static final String SQL_QUERY_INSERT_BLOGS_PORTLET = "INSERT INTO blog_list_portlet_htmldocs ( id_portlet , id_blog, status, document_order ) VALUES ( ? , ?, ?, ? )";
57      private static final String SQL_QUERY_INSERT_BLOGS_PORTLET_ON_UPDATE = "INSERT INTO blog_list_portlet_htmldocs ( id_portlet , id_blog, status, document_order, date_begin_publishing, date_end_publishing) VALUES ( ? , ?, ?, ?, ?, ? )";
58      private static final String SQL_QUERY_SELECT_PORTLET_BY_TYPE = "SELECT DISTINCT b.id_portlet , a.name, a.date_update " + "FROM blog_portlet b "
59              + "LEFT JOIN blog_list_portlet_htmldocs c ON b.id_portlet = c.id_portlet " + "INNER JOIN core_portlet a ON b.id_portlet = a.id_portlet "
60              + "INNER JOIN core_page f ON a.id_page = f.id_page WHERE c.id_portlet IS NULL ";
61  
62      /**
63       * Insert a new record in the table.
64       *
65       * @param portlet
66       *            The Instance of the Portlet
67       */
68      @Override
69      public void insert( Portlet portlet )
70      {
71          BlogPortlet../../../../../../../fr/paris/lutece/plugins/blog/business/portlet/BlogPortlet.html#BlogPortlet">BlogPortlet p = (BlogPortlet) portlet;
72          try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT ) )
73          {
74              daoUtil.setInt( 1, p.getId( ) );
75              daoUtil.setString( 2, p.getPortletName( ) );
76              daoUtil.setInt( 3, p.getContentId( ) );
77              daoUtil.setInt( 4, p.getPageTemplateDocument( ) );
78              daoUtil.executeUpdate( );
79          }
80          insertBlogPublication( p );
81  
82      }
83  
84      /**
85       * Delete record from table
86       *
87       * @param nPortletId
88       *            The indentifier of the Portlet
89       */
90      @Override
91      public void delete( int nPortletId )
92      {
93          try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE ) )
94          {
95              daoUtil.setInt( 1, nPortletId );
96              daoUtil.executeUpdate( );
97          }
98      }
99  
100     /**
101      * Update the record in the table
102      *
103      * @param portlet
104      *            The reference of the portlet
105      */
106     @Override
107     public void store( Portlet portlet )
108     {
109         BlogPortlet../../../../../../../fr/paris/lutece/plugins/blog/business/portlet/BlogPortlet.html#BlogPortlet">BlogPortlet p = (BlogPortlet) portlet;
110         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE ) )
111         {
112             daoUtil.setInt( 1, p.getId( ) );
113             daoUtil.setString( 2, p.getPortletName( ) );
114             daoUtil.setInt( 3, p.getContentId( ) );
115             daoUtil.setInt( 4, p.getPageTemplateDocument( ) );
116             daoUtil.setInt( 5, p.getId( ) );
117 
118             daoUtil.executeUpdate( );
119         }
120 
121         BlogPublicationHome.removeByIdPortlet( p.getId( ) );
122         insertBlogPublicationOnUpdate( p );
123 
124     }
125 
126     /**
127      * load the data of dbpagePortlet from the table
128      * 
129      * @return portlet The instance of the object portlet
130      * @param nIdPortlet
131      *            The identifier of the portlet
132      */
133     @Override
134     public Portlet load( int nIdPortlet )
135     {
136         BlogPortletlog/business/portlet/BlogPortlet.html#BlogPortlet">BlogPortlet portlet = new BlogPortlet( );
137         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT ) )
138         {
139             daoUtil.setInt( 1, nIdPortlet );
140 
141             daoUtil.executeQuery( );
142 
143             if ( daoUtil.next( ) )
144             {
145                 portlet.setId( daoUtil.getInt( 1 ) );
146                 portlet.setPortletName( daoUtil.getString( 2 ) );
147                 portlet.setContentId( daoUtil.getInt( 3 ) );
148                 portlet.setPageTemplateDocument( daoUtil.getInt( 4 ) );
149             }
150         }
151         return portlet;
152     }
153 
154     /**
155      * Insert a docPublication for a specified portlet
156      * 
157      * @param portlet
158      *            the BlogsPortlet to insert
159      */
160     private void insertBlogPublication( BlogPortlet p )
161     {
162         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT_BLOGS_PORTLET ) )
163         {
164             daoUtil.setInt( 1, p.getId( ) );
165             daoUtil.setInt( 2, p.getContentId( ) );
166             daoUtil.setInt( 3, 1 );
167             daoUtil.setInt( 4, 0 );
168             daoUtil.executeUpdate( );
169         }
170     }
171 
172     /**
173      * {@inheritDoc }
174      */
175     @Override
176     public ReferenceList selectBlogPortletReferenceList( Plugin plugin )
177     {
178         ReferenceList blogPortletList = new ReferenceList( );
179         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL, plugin ) )
180         {
181             daoUtil.executeQuery( );
182 
183             while ( daoUtil.next( ) )
184             {
185                 blogPortletList.addItem( daoUtil.getInt( 1 ), daoUtil.getString( 2 ) );
186             }
187         }
188         return blogPortletList;
189     }
190 
191     /**
192      * {@inheritDoc }
193      */
194     @Override
195     public Collection<ReferenceItem> selectPortletByType( int nDocumentId, PortletOrder pOrder, PortletFilter pFilter )
196     {
197         StringBuilder strSQl = new StringBuilder( );
198         strSQl.append( SQL_QUERY_SELECT_PORTLET_BY_TYPE );
199 
200         String strFilter = ( pFilter != null ) ? pFilter.getSQLFilter( ) : null;
201 
202         if ( strFilter != null )
203         {
204             strSQl.append( "AND" );
205             strSQl.append( strFilter );
206         }
207 
208         strSQl.append( pOrder.getSQLOrderBy( ) );
209 
210         ReferenceList list = new ReferenceList( );
211         try ( DAOUtil daoUtil = new DAOUtil( strSQl.toString( ) ) )
212         {
213             if ( strFilter != null )
214             {
215                 if ( pFilter.getPortletFilterType( ).equals( PortletFilter.PAGE_NAME ) )
216                 {
217                     for ( int i = 0; i < pFilter.getPageName( ).length; i++ )
218                     {
219                         daoUtil.setString( i + 1, "%" + pFilter.getPageName( ) [i] + "%" );
220                     }
221                 }
222                 else
223                     if ( pFilter.getPortletFilterType( ).equals( PortletFilter.PORTLET_NAME ) )
224                     {
225                         for ( int i = 0; i < pFilter.getPortletName( ).length; i++ )
226                         {
227                             daoUtil.setString( i + 1, "%" + pFilter.getPortletName( ) [i] + "%" );
228                         }
229                     }
230                     else
231                         if ( pFilter.getPortletFilterType( ).equals( PortletFilter.PAGE_ID ) )
232                         {
233                             daoUtil.setInt( 1, pFilter.getIdPage( ) );
234                         }
235             }
236 
237             daoUtil.executeQuery( );
238             while ( daoUtil.next( ) )
239             {
240                 list.addItem( daoUtil.getInt( 1 ), daoUtil.getString( 2 ) );
241             }
242         }
243 
244         return list;
245     }
246 
247     /**
248      * Insert a blog publication for a specified portlet
249      * 
250      * @param portlet
251      *            the BlogPortlet to insert
252      */
253     private void insertBlogPublicationOnUpdate( BlogPortlet portlet )
254     {
255         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT_BLOGS_PORTLET_ON_UPDATE ) )
256         {
257             daoUtil.setInt( 1, portlet.getId( ) );
258             daoUtil.setInt( 2, portlet.getContentId( ) );
259             daoUtil.setInt( 3, 1 );
260             daoUtil.setInt( 4, 0 );
261             daoUtil.setDate( 5, portlet.getBlogPublication( ).getDateBeginPublishing( ) );
262             daoUtil.setDate( 6, portlet.getBlogPublication( ).getDateEndPublishing( ) );
263 
264             daoUtil.executeUpdate( );
265         }
266     }
267 }