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.publication;
35
36 import fr.paris.lutece.util.sql.DAOUtil;
37
38 import java.sql.Timestamp;
39
40 import java.util.ArrayList;
41 import java.util.Collection;
42 import java.util.Date;
43
44
45
46
47 public class DocumentPublicationDAO implements IDocumentPublicationDAO
48 {
49 private static final String SQL_QUERY_DELETE = " DELETE FROM document_published WHERE id_portlet = ? AND id_document = ? ";
50 private static final String SQL_QUERY_DELETE_FROM_PORTLET_ID = " DELETE FROM document_published WHERE id_portlet = ? ";
51 private static final String SQL_QUERY_DELETE_FROM_DOCUMENT_ID = " DELETE FROM document_published WHERE id_document = ? ";
52 private static final String SQL_QUERY_INSERT = " INSERT INTO document_published ( id_portlet, id_document, document_order, status, date_publishing ) VALUES ( ?, ?, ?, ?, ? )";
53 private static final String SQL_QUERY_UPDATE = " UPDATE document_published SET document_order = ?, status = ?, date_publishing = ? WHERE id_portlet = ? AND id_document = ? ";
54 private static final String SQL_QUERY_SELECT = " SELECT document_order, status, date_publishing FROM document_published WHERE id_portlet = ? AND id_document = ? ORDER BY document_order ASC ";
55 private static final String SQL_QUERY_SELECT_BY_PORTLET_ID = " SELECT id_document, document_order, status, date_publishing FROM document_published WHERE id_portlet = ? ORDER BY document_order ASC ";
56 private static final String SQL_QUERY_SELECT_BY_DOCUMENT_ID = " SELECT id_portlet, document_order, status, date_publishing FROM document_published WHERE id_document = ? ORDER BY document_order ASC ";
57 private static final String SQL_QUERY_SELECT_BY_PORTLET_ID_AND_STATUS = " SELECT id_document, document_order, date_publishing FROM document_published WHERE id_portlet = ? AND status = ? ORDER BY document_order ASC ";
58 private static final String SQL_QUERY_SELECT_BY_DOCUMENT_ID_AND_STATUS = " SELECT id_portlet, document_order, date_publishing FROM document_published WHERE id_document = ? AND status = ? ORDER BY document_order ASC ";
59 private static final String SQL_QUERY_SELECT_BY_DATE_PUBLISHING_AND_STATUS = " SELECT id_portlet, id_document, document_order, date_publishing FROM document_published WHERE date_publishing >= ? AND status = ? ORDER BY document_order ASC ";
60 private static final String SQL_QUERY_MAX_ORDER = "SELECT max(document_order) FROM document_published WHERE id_portlet = ? ";
61 private static final String SQL_QUERY_MODIFY_ORDER_BY_ID = "SELECT id_document FROM document_published WHERE document_order = ? AND id_portlet = ?";
62
63
64
65
66
67
68
69 public void insert( DocumentPublication documentPublication )
70 {
71 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT );
72
73 daoUtil.setInt( 1, documentPublication.getPortletId( ) );
74 daoUtil.setInt( 2, documentPublication.getDocumentId( ) );
75 daoUtil.setInt( 3, documentPublication.getDocumentOrder( ) );
76 daoUtil.setInt( 4, documentPublication.getStatus( ) );
77 daoUtil.setTimestamp( 5, new Timestamp( documentPublication.getDatePublishing( ).getTime( ) ) );
78
79 daoUtil.executeUpdate( );
80 daoUtil.free( );
81 }
82
83
84
85
86
87
88
89 public void store( DocumentPublication documentPublication )
90 {
91 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE );
92 daoUtil.setInt( 1, documentPublication.getDocumentOrder( ) );
93 daoUtil.setInt( 2, documentPublication.getStatus( ) );
94 daoUtil.setTimestamp( 3, new Timestamp( documentPublication.getDatePublishing( ).getTime( ) ) );
95 daoUtil.setInt( 4, documentPublication.getPortletId( ) );
96 daoUtil.setInt( 5, documentPublication.getDocumentId( ) );
97
98 daoUtil.executeUpdate( );
99 daoUtil.free( );
100 }
101
102
103
104
105
106
107
108
109
110 public void delete( int nPortletId, int nDocumentId )
111 {
112 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE );
113 daoUtil.setInt( 1, nPortletId );
114 daoUtil.setInt( 2, nDocumentId );
115 daoUtil.executeUpdate( );
116 daoUtil.free( );
117 }
118
119
120
121
122
123
124
125 public void deleteFromPortletId( int nPortletId )
126 {
127 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_FROM_PORTLET_ID );
128 daoUtil.setInt( 1, nPortletId );
129 daoUtil.executeUpdate( );
130 daoUtil.free( );
131 }
132
133
134
135
136
137
138
139 public void deleteFromDocumentId( int nDocumentId )
140 {
141 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_FROM_DOCUMENT_ID );
142 daoUtil.setInt( 1, nDocumentId );
143 daoUtil.executeUpdate( );
144 daoUtil.free( );
145 }
146
147
148
149
150
151
152
153
154
155
156
157
158
159 public DocumentPublication select( int nPortletId, int nDocumentId )
160 {
161 DocumentPublication documentPublication = null;
162 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT );
163 daoUtil.setInt( 1, nPortletId );
164 daoUtil.setInt( 2, nDocumentId );
165 daoUtil.executeQuery( );
166
167 while ( daoUtil.next( ) )
168 {
169 documentPublication = new DocumentPublication( );
170 documentPublication.setPortletId( nPortletId );
171 documentPublication.setDocumentId( nDocumentId );
172 documentPublication.setDocumentOrder( daoUtil.getInt( 1 ) );
173 documentPublication.setStatus( daoUtil.getInt( 2 ) );
174 documentPublication.setDatePublishing( daoUtil.getTimestamp( 3 ) );
175 }
176
177 daoUtil.free( );
178
179 return documentPublication;
180 }
181
182
183
184
185
186
187
188
189 public Collection<DocumentPublication> selectByPortletId( int nPortletId )
190 {
191 Collection<DocumentPublication> listDocumentPublication = new ArrayList<DocumentPublication>( );
192 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_PORTLET_ID );
193 daoUtil.setInt( 1, nPortletId );
194 daoUtil.executeQuery( );
195
196 while ( daoUtil.next( ) )
197 {
198 DocumentPublicationlication/DocumentPublication.html#DocumentPublication">DocumentPublication documentPublication = new DocumentPublication( );
199 documentPublication.setPortletId( nPortletId );
200 documentPublication.setDocumentId( daoUtil.getInt( 1 ) );
201 documentPublication.setDocumentOrder( daoUtil.getInt( 2 ) );
202 documentPublication.setStatus( daoUtil.getInt( 3 ) );
203 documentPublication.setDatePublishing( daoUtil.getTimestamp( 4 ) );
204 listDocumentPublication.add( documentPublication );
205 }
206
207 daoUtil.free( );
208
209 return listDocumentPublication;
210 }
211
212
213
214
215
216
217
218
219 public Collection<DocumentPublication> selectByDocumentId( int nDocumentId )
220 {
221 Collection<DocumentPublication> listDocumentPublication = new ArrayList<DocumentPublication>( );
222 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_DOCUMENT_ID );
223 daoUtil.setInt( 1, nDocumentId );
224 daoUtil.executeQuery( );
225
226 while ( daoUtil.next( ) )
227 {
228 DocumentPublicationlication/DocumentPublication.html#DocumentPublication">DocumentPublication documentPublication = new DocumentPublication( );
229 documentPublication.setPortletId( daoUtil.getInt( 1 ) );
230 documentPublication.setDocumentId( nDocumentId );
231 documentPublication.setDocumentOrder( daoUtil.getInt( 2 ) );
232 documentPublication.setStatus( daoUtil.getInt( 3 ) );
233 documentPublication.setDatePublishing( daoUtil.getTimestamp( 4 ) );
234 listDocumentPublication.add( documentPublication );
235 }
236
237 daoUtil.free( );
238
239 return listDocumentPublication;
240 }
241
242
243
244
245
246
247
248
249
250
251 public Collection<DocumentPublication> selectByPortletIdAndStatus( int nPortletId, int nStatus )
252 {
253 Collection<DocumentPublication> listDocumentPublication = new ArrayList<DocumentPublication>( );
254 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_PORTLET_ID_AND_STATUS );
255 daoUtil.setInt( 1, nPortletId );
256 daoUtil.setInt( 2, nStatus );
257 daoUtil.executeQuery( );
258
259 while ( daoUtil.next( ) )
260 {
261 DocumentPublicationlication/DocumentPublication.html#DocumentPublication">DocumentPublication documentPublication = new DocumentPublication( );
262 documentPublication.setPortletId( nPortletId );
263 documentPublication.setDocumentId( daoUtil.getInt( 1 ) );
264 documentPublication.setDocumentOrder( daoUtil.getInt( 2 ) );
265 documentPublication.setStatus( nStatus );
266 documentPublication.setDatePublishing( daoUtil.getTimestamp( 3 ) );
267 listDocumentPublication.add( documentPublication );
268 }
269
270 daoUtil.free( );
271
272 return listDocumentPublication;
273 }
274
275
276
277
278
279
280
281
282
283
284 public Collection<DocumentPublication> selectByDocumentIdAndStatus( int nDocumentId, int nStatus )
285 {
286 Collection<DocumentPublication> listDocumentPublication = new ArrayList<DocumentPublication>( );
287 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_DOCUMENT_ID_AND_STATUS );
288 daoUtil.setInt( 1, nDocumentId );
289 daoUtil.setInt( 2, nStatus );
290 daoUtil.executeQuery( );
291
292 while ( daoUtil.next( ) )
293 {
294 DocumentPublicationlication/DocumentPublication.html#DocumentPublication">DocumentPublication documentPublication = new DocumentPublication( );
295 documentPublication.setPortletId( daoUtil.getInt( 1 ) );
296 documentPublication.setDocumentId( nDocumentId );
297 documentPublication.setDocumentOrder( daoUtil.getInt( 2 ) );
298 documentPublication.setStatus( nStatus );
299 documentPublication.setDatePublishing( daoUtil.getTimestamp( 3 ) );
300 listDocumentPublication.add( documentPublication );
301 }
302
303 daoUtil.free( );
304
305 return listDocumentPublication;
306 }
307
308
309
310
311
312
313
314
315
316
317 public Collection<DocumentPublication> selectSinceDatePublishingAndStatus( Date datePublishing, int nStatus )
318 {
319 Collection<DocumentPublication> listDocumentPublication = new ArrayList<DocumentPublication>( );
320 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_DATE_PUBLISHING_AND_STATUS );
321 daoUtil.setTimestamp( 1, new Timestamp( datePublishing.getTime( ) ) );
322 daoUtil.setInt( 2, nStatus );
323 daoUtil.executeQuery( );
324
325 while ( daoUtil.next( ) )
326 {
327 DocumentPublicationlication/DocumentPublication.html#DocumentPublication">DocumentPublication documentPublication = new DocumentPublication( );
328 documentPublication.setPortletId( daoUtil.getInt( 1 ) );
329 documentPublication.setDocumentId( daoUtil.getInt( 2 ) );
330 documentPublication.setDocumentOrder( daoUtil.getInt( 3 ) );
331 documentPublication.setStatus( nStatus );
332 documentPublication.setDatePublishing( daoUtil.getTimestamp( 4 ) );
333 listDocumentPublication.add( documentPublication );
334 }
335
336 daoUtil.free( );
337
338 return listDocumentPublication;
339 }
340
341
342
343
344
345
346
347
348 public int selectMaxDocumentOrder( int nPortletId )
349 {
350 int nOrder = 0;
351 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_MAX_ORDER );
352 daoUtil.setInt( 1, nPortletId );
353 daoUtil.executeQuery( );
354
355 if ( daoUtil.next( ) )
356 {
357 nOrder = daoUtil.getInt( 1 );
358 }
359
360 daoUtil.free( );
361
362 return nOrder;
363 }
364
365
366
367
368
369
370
371
372
373
374 public int selectDocumentIdByOrder( int nDocumentOrder, int nPortletId )
375 {
376
377 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_MODIFY_ORDER_BY_ID );
378 int nResult = nDocumentOrder;
379 daoUtil.setInt( 1, nResult );
380 daoUtil.setInt( 2, nPortletId );
381 daoUtil.executeQuery( );
382
383 if ( !daoUtil.next( ) )
384 {
385
386 nResult = 1;
387 }
388 else
389 {
390 nResult = daoUtil.getInt( 1 );
391 }
392
393 daoUtil.free( );
394
395 return nResult;
396 }
397 }