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