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 ) 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.executeUpdate( );
117 }
118
119 }
120 }
121 }
122
123
124
125
126
127
128
129 private void insertBlogsPublicationOnUpdate( Portlet portlet )
130 {
131 BlogListPortlet./../../../../../fr/paris/lutece/plugins/blog/business/portlet/BlogListPortlet.html#BlogListPortlet">BlogListPortlet p = (BlogListPortlet) portlet;
132
133 if ( !p.getArrayBlogs( ).isEmpty( ) )
134 {
135 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT_BLOGS_PORTLET_ON_UPDATE ) )
136 {
137
138 for ( BlogPublication docPub : p.getArrayBlogs( ) )
139 {
140 daoUtil.setInt( 1, p.getId( ) );
141 daoUtil.setInt( 2, docPub.getIdBlog( ) );
142 daoUtil.setInt( 3, 1 );
143 daoUtil.setInt( 4, docPub.getBlogOrder( ) );
144 daoUtil.setDate( 5, docPub.getDateBeginPublishing( ) );
145 daoUtil.setDate( 6, docPub.getDateEndPublishing( ) );
146 daoUtil.executeUpdate( );
147 }
148
149 }
150 }
151 }
152
153
154
155
156 @Override
157 public void delete( int nPortletId )
158 {
159 deleteHtmlsDocsId( nPortletId );
160 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE ) )
161 {
162 daoUtil.setInt( 1, nPortletId );
163 daoUtil.executeUpdate( );
164 }
165
166 }
167
168
169
170
171
172
173
174 private void deleteHtmlsDocsId( int nPortletId )
175 {
176 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_BLOGS_PORTLET ) )
177 {
178 daoUtil.setInt( 1, nPortletId );
179 daoUtil.executeUpdate( );
180 }
181 }
182
183
184
185
186 @Override
187 public Portlet load( int nPortletId )
188 {
189 BlogListPortletbusiness/portlet/BlogListPortlet.html#BlogListPortlet">BlogListPortlet portlet = new BlogListPortlet( );
190 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT ) )
191 {
192 daoUtil.setInt( 1, nPortletId );
193 daoUtil.executeQuery( );
194
195 if ( daoUtil.next( ) )
196 {
197 portlet.setId( daoUtil.getInt( 1 ) );
198 portlet.setPageTemplateDocument( daoUtil.getInt( 2 ) );
199 }
200 }
201
202 portlet.setArrayBlogs( loadBlogsId( nPortletId ) );
203 return portlet;
204 }
205
206
207
208
209 @Override
210 public Map<Integer, String> loadPages( String strPortletType )
211 {
212 Map<Integer, String> page = new HashMap<>( );
213 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_PAGE_PORTLET ) )
214 {
215 daoUtil.setString( 1, strPortletType );
216 daoUtil.executeQuery( );
217
218 while ( daoUtil.next( ) )
219 {
220 page.put( daoUtil.getInt( 1 ), daoUtil.getString( 2 ) );
221 }
222 }
223 return page;
224 }
225
226
227
228
229
230
231
232 private List<BlogPublication> loadBlogsId( int nPortletId )
233 {
234 List<BlogPublication> listDocPublication = new ArrayList<>( );
235 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_CATEGORY_PORTLET ) )
236 {
237 daoUtil.setInt( 1, nPortletId );
238 daoUtil.executeQuery( );
239
240 while ( daoUtil.next( ) )
241 {
242 BlogPublication/business/portlet/BlogPublication.html#BlogPublication">BlogPublication docPub = new BlogPublication( );
243 docPub.setIdBlog( daoUtil.getInt( 1 ) );
244 docPub.setBlogOrder( daoUtil.getInt( 2 ) );
245 docPub.setDateBeginPublishing( daoUtil.getDate( 3 ) );
246 docPub.setDateEndPublishing( daoUtil.getDate( 4 ) );
247 docPub.setStatus( daoUtil.getInt( 5 ) );
248 listDocPublication.add( docPub );
249
250 }
251
252 }
253 return listDocPublication;
254 }
255
256
257
258
259 @Override
260 public void store( Portlet portlet )
261 {
262 BlogListPortlet./../../../../../fr/paris/lutece/plugins/blog/business/portlet/BlogListPortlet.html#BlogListPortlet">BlogListPortlet p = (BlogListPortlet) portlet;
263 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE ) )
264 {
265 daoUtil.setInt( 1, p.getId( ) );
266 daoUtil.setInt( 2, p.getPageTemplateDocument( ) );
267 daoUtil.setInt( 3, p.getId( ) );
268
269 daoUtil.executeUpdate( );
270 }
271
272 deleteHtmlsDocsId( p.getId( ) );
273 insertBlogsPublicationOnUpdate( p );
274 }
275
276
277
278
279 @Override
280 public boolean checkIsAliasPortlet( int nPortletId )
281 {
282 boolean bIsAlias = false;
283 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_CHECK_IS_ALIAS ) )
284 {
285 daoUtil.setInt( 1, nPortletId );
286 daoUtil.executeQuery( );
287
288 if ( daoUtil.next( ) )
289 {
290 bIsAlias = true;
291 }
292 }
293 return bIsAlias;
294 }
295
296
297
298
299 @Override
300 public ReferenceList selectBlogListPortletReferenceList( Plugin plugin )
301 {
302 ReferenceList blogPortletList = new ReferenceList( );
303 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL, plugin ) )
304 {
305 daoUtil.executeQuery( );
306
307 while ( daoUtil.next( ) )
308 {
309 blogPortletList.addItem( daoUtil.getInt( 1 ), daoUtil.getString( 2 ) );
310 }
311
312 }
313 return blogPortletList;
314 }
315
316
317
318
319 @Override
320 public Collection<ReferenceItem> selectPortletByType( int nDocumentId, PortletOrder pOrder, PortletFilter pFilter )
321 {
322 StringBuilder strSQl = new StringBuilder( );
323 strSQl.append( SQL_QUERY_SELECT_PORTLET_BY_TYPE );
324
325 String strFilter = ( pFilter != null ) ? pFilter.getSQLFilter( ) : null;
326
327 if ( strFilter != null )
328 {
329 strSQl.append( "AND" );
330 strSQl.append( strFilter );
331 }
332
333 strSQl.append( pOrder.getSQLOrderBy( ) );
334
335 ReferenceList list = new ReferenceList( );
336 try ( DAOUtil daoUtil = new DAOUtil( strSQl.toString( ) ) )
337 {
338
339 daoUtil.setInt( 1, nDocumentId );
340
341 if ( strFilter != null )
342 {
343 if ( pFilter.getPortletFilterType( ).equals( PortletFilter.PAGE_NAME ) )
344 {
345 for ( int i = 0; i < pFilter.getPageName( ).length; i++ )
346 {
347 daoUtil.setString( i + 2, "%" + pFilter.getPageName( ) [i] + "%" );
348 }
349 }
350 else
351 if ( pFilter.getPortletFilterType( ).equals( PortletFilter.PORTLET_NAME ) )
352 {
353 for ( int i = 0; i < pFilter.getPortletName( ).length; i++ )
354 {
355 daoUtil.setString( i + 2, "%" + pFilter.getPortletName( ) [i] + "%" );
356 }
357 }
358 else
359 if ( pFilter.getPortletFilterType( ).equals( PortletFilter.PAGE_ID ) )
360 {
361 daoUtil.setInt( 2, pFilter.getIdPage( ) );
362 }
363 }
364
365 daoUtil.executeQuery( );
366
367 while ( daoUtil.next( ) )
368 {
369 list.addItem( daoUtil.getInt( 1 ), daoUtil.getString( 2 ) );
370 }
371 }
372
373 return list;
374 }
375
376
377
378
379 @Override
380 public int selectMinDocumentBlogOrder( )
381 {
382 int nKey = 1;
383 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_MIN_DOC_ORDER ) )
384 {
385 daoUtil.executeQuery( );
386
387 if ( daoUtil.next( ) )
388 {
389 nKey = daoUtil.getInt( 1 );
390 }
391 }
392 return nKey;
393 }
394
395 }