1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34 package fr.paris.lutece.plugins.blog.business.portlet;
35
36 import fr.paris.lutece.portal.business.portlet.Portlet;
37 import fr.paris.lutece.portal.service.plugin.Plugin;
38 import fr.paris.lutece.util.ReferenceItem;
39 import fr.paris.lutece.util.ReferenceList;
40 import fr.paris.lutece.util.sql.DAOUtil;
41
42 import java.util.ArrayList;
43 import java.util.Collection;
44 import java.util.HashMap;
45 import java.util.List;
46 import java.util.Map;
47
48
49
50
51 public final class BlogListPortletDAO implements IBlogListPortletDAO
52 {
53
54 private static final String SQL_QUERY_SELECTALL = "SELECT id_portlet , id_page_template_document FROM blog_list_portlet ";
55 private static final String SQL_QUERY_INSERT = "INSERT INTO blog_list_portlet ( id_portlet , id_page_template_document ) VALUES ( ? , ? )";
56 private static final String SQL_QUERY_SELECT = "SELECT id_portlet , id_page_template_document FROM blog_list_portlet WHERE id_portlet = ? ";
57 private static final String SQL_QUERY_UPDATE = "UPDATE blog_list_portlet SET id_portlet = ?, id_page_template_document = ? WHERE id_portlet = ? ";
58 private static final String SQL_QUERY_DELETE = "DELETE FROM blog_list_portlet WHERE id_portlet= ? ";
59 private static final String SQL_QUERY_CHECK_IS_ALIAS = "SELECT id_alias FROM core_portlet_alias WHERE id_alias = ?";
60
61 private static final String SQL_QUERY_SELECT_PORTLET_BY_TYPE = "SELECT DISTINCT b.id_portlet , a.name, a.date_update " + "FROM blog_list_portlet b "
62 + "LEFT JOIN blog_list_portlet_htmldocs c ON b.id_portlet = c.id_portlet AND c.id_blog= ? "
63 + "INNER JOIN core_portlet a ON b.id_portlet = a.id_portlet " + "INNER JOIN core_page f ON a.id_page = f.id_page WHERE c.id_portlet IS NULL ";
64
65 private static final String SQL_QUERY_INSERT_BLOGS_PORTLET = "INSERT INTO blog_list_portlet_htmldocs ( id_portlet , id_blog, status, document_order, date_end_publishing ) VALUES ( ? , ?, ?, ?, ? )";
66 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 ( ? , ?, ?, ?, ?, ? )";
67 private static final String SQL_QUERY_DELETE_BLOGS_PORTLET = " DELETE FROM blog_list_portlet_htmldocs WHERE id_portlet = ? ";
68 private static final String SQL_QUERY_SELECT_CATEGORY_PORTLET = "SELECT id_blog, document_order, date_begin_publishing, date_end_publishing, status FROM blog_list_portlet_htmldocs WHERE id_portlet = ? order by document_order ";
69 private static final String SQL_QUERY_SELECT_PAGE_PORTLET = "SELECT id_page_template_document,description from blog_page_template where portlet_type= ?";
70
71 private static final String SQL_QUERY_SELECT_MIN_DOC_ORDER = "SELECT MIN( document_order ) FROM blog_list_portlet_htmldocs ";
72
73
74
75
76
77
78
79 @Override
80 public void insert( Portlet portlet )
81 {
82 BlogListPortlet./../../../../../fr/paris/lutece/plugins/blog/business/portlet/BlogListPortlet.html#BlogListPortlet">BlogListPortlet p = (BlogListPortlet) portlet;
83
84 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT ) )
85 {
86 daoUtil.setInt( 1, p.getId( ) );
87 daoUtil.setInt( 2, p.getPageTemplateDocument( ) );
88
89 daoUtil.executeUpdate( );
90 }
91
92 insertBlogsId( portlet );
93 }
94
95
96
97
98
99
100
101 private void insertBlogsId( Portlet portlet )
102 {
103 BlogListPortlet./../../../../../fr/paris/lutece/plugins/blog/business/portlet/BlogListPortlet.html#BlogListPortlet">BlogListPortlet p = (BlogListPortlet) portlet;
104
105 if ( !p.getArrayBlogs( ).isEmpty( ) )
106 {
107 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT_BLOGS_PORTLET ) )
108 {
109
110 for ( BlogPublication docPub : p.getArrayBlogs( ) )
111 {
112 daoUtil.setInt( 1, p.getId( ) );
113 daoUtil.setInt( 2, docPub.getIdBlog( ) );
114 daoUtil.setInt( 3, 1 );
115 daoUtil.setInt( 4, docPub.getBlogOrder( ) );
116 daoUtil.setDate( 5, docPub.getDateEndPublishing( ) );
117 daoUtil.executeUpdate( );
118 }
119
120 }
121 }
122 }
123
124
125
126
127
128
129
130 private void insertBlogsPublicationOnUpdate( Portlet portlet )
131 {
132 BlogListPortlet./../../../../../fr/paris/lutece/plugins/blog/business/portlet/BlogListPortlet.html#BlogListPortlet">BlogListPortlet p = (BlogListPortlet) portlet;
133
134 if ( !p.getArrayBlogs( ).isEmpty( ) )
135 {
136 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT_BLOGS_PORTLET_ON_UPDATE ) )
137 {
138
139 for ( BlogPublication docPub : p.getArrayBlogs( ) )
140 {
141 daoUtil.setInt( 1, p.getId( ) );
142 daoUtil.setInt( 2, docPub.getIdBlog( ) );
143 daoUtil.setInt( 3, 1 );
144 daoUtil.setInt( 4, docPub.getBlogOrder( ) );
145 daoUtil.setDate( 5, docPub.getDateBeginPublishing( ) );
146 daoUtil.setDate( 6, docPub.getDateEndPublishing( ) );
147 daoUtil.executeUpdate( );
148 }
149
150 }
151 }
152 }
153
154
155
156
157 @Override
158 public void delete( int nPortletId )
159 {
160 deleteHtmlsDocsId( nPortletId );
161 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE ) )
162 {
163 daoUtil.setInt( 1, nPortletId );
164 daoUtil.executeUpdate( );
165 }
166
167 }
168
169
170
171
172
173
174
175 private void deleteHtmlsDocsId( int nPortletId )
176 {
177 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_BLOGS_PORTLET ) )
178 {
179 daoUtil.setInt( 1, nPortletId );
180 daoUtil.executeUpdate( );
181 }
182 }
183
184
185
186
187 @Override
188 public Portlet load( int nPortletId )
189 {
190 BlogListPortletbusiness/portlet/BlogListPortlet.html#BlogListPortlet">BlogListPortlet portlet = new BlogListPortlet( );
191 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT ) )
192 {
193 daoUtil.setInt( 1, nPortletId );
194 daoUtil.executeQuery( );
195
196 if ( daoUtil.next( ) )
197 {
198 portlet.setId( daoUtil.getInt( 1 ) );
199 portlet.setPageTemplateDocument( daoUtil.getInt( 2 ) );
200 }
201 }
202
203 portlet.setArrayBlogs( loadBlogsId( nPortletId ) );
204 return portlet;
205 }
206
207
208
209
210 @Override
211 public Map<Integer, String> loadPages( String strPortletType )
212 {
213 Map<Integer, String> page = new HashMap<>( );
214 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_PAGE_PORTLET ) )
215 {
216 daoUtil.setString( 1, strPortletType );
217 daoUtil.executeQuery( );
218
219 while ( daoUtil.next( ) )
220 {
221 page.put( daoUtil.getInt( 1 ), daoUtil.getString( 2 ) );
222 }
223 }
224 return page;
225 }
226
227
228
229
230
231
232
233 private List<BlogPublication> loadBlogsId( int nPortletId )
234 {
235 List<BlogPublication> listDocPublication = new ArrayList<>( );
236 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_CATEGORY_PORTLET ) )
237 {
238 daoUtil.setInt( 1, nPortletId );
239 daoUtil.executeQuery( );
240
241 while ( daoUtil.next( ) )
242 {
243 BlogPublication/business/portlet/BlogPublication.html#BlogPublication">BlogPublication docPub = new BlogPublication( );
244 docPub.setIdBlog( daoUtil.getInt( 1 ) );
245 docPub.setBlogOrder( daoUtil.getInt( 2 ) );
246 docPub.setDateBeginPublishing( daoUtil.getDate( 3 ) );
247 docPub.setDateEndPublishing( daoUtil.getDate( 4 ) );
248 docPub.setStatus( daoUtil.getInt( 5 ) );
249 listDocPublication.add( docPub );
250
251 }
252
253 }
254 return listDocPublication;
255 }
256
257
258
259
260 @Override
261 public void store( Portlet portlet )
262 {
263 BlogListPortlet./../../../../../fr/paris/lutece/plugins/blog/business/portlet/BlogListPortlet.html#BlogListPortlet">BlogListPortlet p = (BlogListPortlet) portlet;
264 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE ) )
265 {
266 daoUtil.setInt( 1, p.getId( ) );
267 daoUtil.setInt( 2, p.getPageTemplateDocument( ) );
268 daoUtil.setInt( 3, p.getId( ) );
269
270 daoUtil.executeUpdate( );
271 }
272
273 deleteHtmlsDocsId( p.getId( ) );
274 insertBlogsPublicationOnUpdate( p );
275 }
276
277
278
279
280 @Override
281 public boolean checkIsAliasPortlet( int nPortletId )
282 {
283 boolean bIsAlias = false;
284 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_CHECK_IS_ALIAS ) )
285 {
286 daoUtil.setInt( 1, nPortletId );
287 daoUtil.executeQuery( );
288
289 if ( daoUtil.next( ) )
290 {
291 bIsAlias = true;
292 }
293 }
294 return bIsAlias;
295 }
296
297
298
299
300 @Override
301 public ReferenceList selectBlogListPortletReferenceList( Plugin plugin )
302 {
303 ReferenceList blogPortletList = new ReferenceList( );
304 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL, plugin ) )
305 {
306 daoUtil.executeQuery( );
307
308 while ( daoUtil.next( ) )
309 {
310 blogPortletList.addItem( daoUtil.getInt( 1 ), daoUtil.getString( 2 ) );
311 }
312
313 }
314 return blogPortletList;
315 }
316
317
318
319
320 @Override
321 public Collection<ReferenceItem> selectPortletByType( int nDocumentId, PortletOrder pOrder, PortletFilter pFilter )
322 {
323 StringBuilder strSQl = new StringBuilder( );
324 strSQl.append( SQL_QUERY_SELECT_PORTLET_BY_TYPE );
325
326 String strFilter = ( pFilter != null ) ? pFilter.getSQLFilter( ) : null;
327
328 if ( strFilter != null )
329 {
330 strSQl.append( "AND" );
331 strSQl.append( strFilter );
332 }
333
334 strSQl.append( pOrder.getSQLOrderBy( ) );
335
336 ReferenceList list = new ReferenceList( );
337 try ( DAOUtil daoUtil = new DAOUtil( strSQl.toString( ) ) )
338 {
339
340 daoUtil.setInt( 1, nDocumentId );
341
342 if ( strFilter != null )
343 {
344 if ( pFilter.getPortletFilterType( ).equals( PortletFilter.PAGE_NAME ) )
345 {
346 for ( int i = 0; i < pFilter.getPageName( ).length; i++ )
347 {
348 daoUtil.setString( i + 2, "%" + pFilter.getPageName( ) [i] + "%" );
349 }
350 }
351 else
352 if ( pFilter.getPortletFilterType( ).equals( PortletFilter.PORTLET_NAME ) )
353 {
354 for ( int i = 0; i < pFilter.getPortletName( ).length; i++ )
355 {
356 daoUtil.setString( i + 2, "%" + pFilter.getPortletName( ) [i] + "%" );
357 }
358 }
359 else
360 if ( pFilter.getPortletFilterType( ).equals( PortletFilter.PAGE_ID ) )
361 {
362 daoUtil.setInt( 2, pFilter.getIdPage( ) );
363 }
364 }
365
366 daoUtil.executeQuery( );
367
368 while ( daoUtil.next( ) )
369 {
370 list.addItem( daoUtil.getInt( 1 ), daoUtil.getString( 2 ) );
371 }
372 }
373
374 return list;
375 }
376
377
378
379
380 @Override
381 public int selectMinDocumentBlogOrder( )
382 {
383 int nKey = 1;
384 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_MIN_DOC_ORDER ) )
385 {
386 daoUtil.executeQuery( );
387
388 if ( daoUtil.next( ) )
389 {
390 nKey = daoUtil.getInt( 1 );
391 }
392 }
393 return nKey;
394 }
395
396 }