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.document.business.portlet;
35
36 import fr.paris.lutece.portal.business.portlet.Portlet;
37 import fr.paris.lutece.util.ReferenceItem;
38 import fr.paris.lutece.util.ReferenceList;
39 import fr.paris.lutece.util.sql.DAOUtil;
40
41 import java.util.ArrayList;
42 import java.util.Collection;
43 import java.util.List;
44
45 public class DocumentPortletDAO implements IDocumentPortletDAO
46 {
47 private static final String SQL_QUERY_INSERT = "INSERT INTO document_portlet ( id_portlet , code_document_type ) VALUES ( ? , ? )";
48 private static final String SQL_QUERY_SELECT = "SELECT id_portlet , code_document_type FROM document_portlet WHERE id_portlet = ? ";
49 private static final String SQL_QUERY_UPDATE = "UPDATE document_portlet SET id_portlet = ?, code_document_type = ? WHERE id_portlet = ? ";
50 private static final String SQL_QUERY_DELETE = "DELETE FROM document_portlet WHERE id_portlet= ? ";
51 private static final String SQL_QUERY_DELETE_PUBLISHED_DOCUMENT_PORTLET = " DELETE FROM document_published WHERE id_portlet = ? ";
52 private static final String SQL_QUERY_SELECT_DOCUMENTS_BY_TYPE_AND_CATEGORY = "SELECT DISTINCT b.id_portlet , a.name, a.date_update "
53 + "FROM document_portlet b " + "LEFT JOIN document_published c ON b.id_portlet = c.id_portlet AND c.id_document= ? "
54 + "INNER JOIN core_portlet a ON b.id_portlet = a.id_portlet " + "LEFT OUTER JOIN document_category_portlet d ON b.id_portlet = d.id_portlet "
55 + "INNER JOIN core_page f ON a.id_page = f.id_page "
56 + "WHERE c.id_portlet IS NULL AND b.code_document_type = ? AND (d.id_category IN (SELECT e.id_category "
57 + "FROM document_category_link e WHERE e.id_document = ?) OR d.id_category IS NULL) ";
58 private static final String SQL_QUERY_CHECK_IS_ALIAS = "SELECT id_alias FROM core_portlet_alias WHERE id_alias = ?";
59 private static final String SQL_QUERY_SELECT_PORTLETS_BY_DOCUMENT_ID = "SELECT id_portlet FROM document_published WHERE id_document = ?";
60
61
62 private static final String SQL_QUERY_INSERT_CATEGORY_PORTLET = "INSERT INTO document_category_portlet ( id_portlet , id_category ) VALUES ( ? , ? )";
63 private static final String SQL_QUERY_DELETE_CATEGORY_PORTLET = " DELETE FROM document_category_portlet WHERE id_portlet = ? ";
64 private static final String SQL_QUERY_DELETE_AUTO_PUBLICATION_PORTLET = " DELETE FROM document_auto_publication WHERE id_portlet = ? ";
65 private static final String SQL_QUERY_SELECT_CATEGORY_PORTLET = "SELECT id_category FROM document_category_portlet WHERE id_portlet = ? ";
66 private static final String SQL_QUERY_CASE_AND = " AND ";
67
68
69
70
71
72
73
74
75
76
77 public void insert( Portlet portlet )
78 {
79 DocumentPortlet./../../../../../fr/paris/lutece/plugins/document/business/portlet/DocumentPortlet.html#DocumentPortlet">DocumentPortlet p = (DocumentPortlet) portlet;
80
81 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT );
82 daoUtil.setInt( 1, p.getId( ) );
83 daoUtil.setString( 2, p.getDocumentTypeCode( ) );
84
85 daoUtil.executeUpdate( );
86 daoUtil.free( );
87
88 insertCategory( portlet );
89 }
90
91
92
93
94
95
96
97 private void insertCategory( Portlet portlet )
98 {
99 DocumentPortlet./../../../../../fr/paris/lutece/plugins/document/business/portlet/DocumentPortlet.html#DocumentPortlet">DocumentPortlet p = (DocumentPortlet) portlet;
100
101 if ( p.getIdCategory( ) != null )
102 {
103 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT_CATEGORY_PORTLET );
104
105 for ( int nIdCategory : p.getIdCategory( ) )
106 {
107 daoUtil.setInt( 1, p.getId( ) );
108 daoUtil.setInt( 2, nIdCategory );
109
110 daoUtil.executeUpdate( );
111 }
112
113 daoUtil.free( );
114 }
115 }
116
117
118
119
120
121
122
123 public void delete( int nPortletId )
124 {
125 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE );
126 daoUtil.setInt( 1, nPortletId );
127 daoUtil.executeUpdate( );
128 daoUtil.free( );
129
130 daoUtil = new DAOUtil( SQL_QUERY_DELETE_PUBLISHED_DOCUMENT_PORTLET );
131 daoUtil.setInt( 1, nPortletId );
132 daoUtil.executeUpdate( );
133 daoUtil.free( );
134
135 deleteCategories( nPortletId );
136 deleteAutoPublication( nPortletId );
137 }
138
139
140
141
142
143
144
145 private void deleteCategories( int nPortletId )
146 {
147 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_CATEGORY_PORTLET );
148 daoUtil.setInt( 1, nPortletId );
149 daoUtil.executeUpdate( );
150 daoUtil.free( );
151 }
152
153
154
155
156
157
158
159 private void deleteAutoPublication( int nPortletId )
160 {
161 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_AUTO_PUBLICATION_PORTLET );
162 daoUtil.setInt( 1, nPortletId );
163 daoUtil.executeUpdate( );
164 daoUtil.free( );
165 }
166
167
168
169
170
171
172
173
174 public Portlet load( int nPortletId )
175 {
176 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT );
177 daoUtil.setInt( 1, nPortletId );
178 daoUtil.executeQuery( );
179
180 DocumentPortletent/business/portlet/DocumentPortlet.html#DocumentPortlet">DocumentPortlet portlet = new DocumentPortlet( );
181
182 if ( daoUtil.next( ) )
183 {
184 portlet.setId( daoUtil.getInt( 1 ) );
185 portlet.setDocumentTypeCode( daoUtil.getString( 2 ) );
186 }
187
188 daoUtil.free( );
189
190 portlet.setIdCategory( loadCategories( nPortletId ) );
191
192 return portlet;
193 }
194
195
196
197
198
199
200
201 private int [ ] loadCategories( int nPortletId )
202 {
203 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_CATEGORY_PORTLET );
204 daoUtil.setInt( 1, nPortletId );
205 daoUtil.executeQuery( );
206
207 Collection<Integer> nListIdCategory = new ArrayList<Integer>( );
208
209 while ( daoUtil.next( ) )
210 {
211 nListIdCategory.add( daoUtil.getInt( 1 ) );
212 }
213
214 daoUtil.free( );
215
216 int [ ] nArrayIdCategory = new int [ nListIdCategory.size( )];
217 int i = 0;
218
219 for ( Integer nIdCategory : nListIdCategory )
220 {
221 nArrayIdCategory [i++] = nIdCategory.intValue( );
222 }
223
224 return nArrayIdCategory;
225 }
226
227
228
229
230
231
232
233 public void store( Portlet portlet )
234 {
235 DocumentPortlet./../../../../../fr/paris/lutece/plugins/document/business/portlet/DocumentPortlet.html#DocumentPortlet">DocumentPortlet p = (DocumentPortlet) portlet;
236 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE );
237 daoUtil.setInt( 1, p.getId( ) );
238 daoUtil.setString( 2, p.getDocumentTypeCode( ) );
239 daoUtil.setInt( 3, p.getId( ) );
240
241 daoUtil.executeUpdate( );
242
243 daoUtil.free( );
244
245 deleteCategories( p.getId( ) );
246 insertCategory( p );
247 }
248
249
250
251
252
253
254
255
256
257
258
259
260 public Collection<ReferenceItem> selectByDocumentOdAndDocumentType( int nDocumentId, String strCodeDocumentType, PortletOrder pOrder,
261 PortletFilter pFilter )
262 {
263 StringBuilder strSQl = new StringBuilder( );
264 strSQl.append( SQL_QUERY_SELECT_DOCUMENTS_BY_TYPE_AND_CATEGORY );
265
266 String strFilter = ( pFilter != null ) ? pFilter.getSQLFilter( ) : null;
267
268 if ( strFilter != null )
269 {
270 strSQl.append( SQL_QUERY_CASE_AND );
271 strSQl.append( strFilter );
272 }
273
274 strSQl.append( pOrder.getSQLOrderBy( ) );
275
276 DAOUtil daoUtil = new DAOUtil( strSQl.toString( ) );
277 daoUtil.setInt( 1, nDocumentId );
278 daoUtil.setString( 2, strCodeDocumentType );
279 daoUtil.setInt( 3, nDocumentId );
280
281 if ( strFilter != null )
282 {
283 if ( pFilter.getPortletFilterType( ).equals( PortletFilter.PAGE_NAME ) )
284 {
285 for ( int i = 0; i < pFilter.getPageName( ).length; i++ )
286 {
287 daoUtil.setString( i + 4, "%" + pFilter.getPageName( ) [i] + "%" );
288 }
289 }
290 else
291 if ( pFilter.getPortletFilterType( ).equals( PortletFilter.PORTLET_NAME ) )
292 {
293 for ( int i = 0; i < pFilter.getPortletName( ).length; i++ )
294 {
295 daoUtil.setString( i + 4, "%" + pFilter.getPortletName( ) [i] + "%" );
296 }
297 }
298 else
299 if ( pFilter.getPortletFilterType( ).equals( PortletFilter.PAGE_ID ) )
300 {
301 daoUtil.setInt( 4, pFilter.getIdPage( ) );
302 }
303 }
304
305 daoUtil.executeQuery( );
306
307 ReferenceList list = new ReferenceList( );
308
309 while ( daoUtil.next( ) )
310 {
311 list.addItem( daoUtil.getInt( 1 ), daoUtil.getString( 2 ) );
312 }
313
314 daoUtil.free( );
315
316 return list;
317 }
318
319
320
321
322
323
324
325
326 public boolean checkIsAliasPortlet( int nPortletId )
327 {
328 boolean bIsAlias = false;
329 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_CHECK_IS_ALIAS );
330
331 daoUtil.setInt( 1, nPortletId );
332 daoUtil.executeQuery( );
333
334 if ( daoUtil.next( ) )
335 {
336 bIsAlias = true;
337 }
338
339 daoUtil.free( );
340
341 return bIsAlias;
342 }
343
344
345
346
347
348 public List<Integer> selectPortletsByDocumentId( int nDocumentId )
349 {
350 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_PORTLETS_BY_DOCUMENT_ID );
351 daoUtil.setInt( 1, nDocumentId );
352 daoUtil.executeQuery( );
353
354 List<Integer> list = new ArrayList<Integer>( );
355
356 while ( daoUtil.next( ) )
357 {
358 list.add( daoUtil.getInt( 1 ) );
359 }
360
361 daoUtil.free( );
362
363 return list;
364 }
365 }