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.directory.modules.pdfproducerarchive.business.zipbasket;
35
36 import fr.paris.lutece.plugins.directory.modules.pdfproducerarchive.utils.StatusZipEnum;
37 import fr.paris.lutece.portal.service.plugin.Plugin;
38 import fr.paris.lutece.util.sql.DAOUtil;
39
40 import java.sql.Timestamp;
41 import java.util.ArrayList;
42 import java.util.Date;
43 import java.util.GregorianCalendar;
44 import java.util.List;
45
46
47
48
49
50
51 public class ZipBasketDAO implements IZipBasketDAO
52 {
53 private static final String SQL_QUERY_INSERT = "INSERT INTO directory_zip_basket (id_zip_basket,name,zip_state,id_user,id_directory,id_record,archive_item_key) VALUES ( ? , ? , ? , ? , ? , ? , ? );";
54 private static final String SQL_QUERY_SELECT_MAX_ID = "SELECT max(id_zip_basket) FROM directory_zip_basket";
55 private static final String SQL_QUERY_CHECK = "SELECT id_zip_basket, name, url, zip_state, id_user, id_directory, id_record, archive_item_key, date_creation FROM directory_zip_basket WHERE id_user = ? AND id_directory = ? AND id_record = ? ;";
56 private static final String SQL_QUERY_SELECT_ALL = "SELECT id_zip_basket, name, url, zip_state, id_user, id_directory, id_record, archive_item_key, date_creation FROM directory_zip_basket;";
57 private static final String SQL_QUERY_SELECT_ALL_BY_ADMIN_USER = "SELECT id_zip_basket, name, url, zip_state, id_user, id_directory, id_record, archive_item_key, date_creation FROM directory_zip_basket WHERE id_user = ? AND id_directory = ? ;";
58 private static final String SQL_QUERY_SELECT_ALL_BY_ADMIN_USER_ORDER = "SELECT id_zip_basket, name, url, zip_state, id_user, id_directory, id_record, archive_item_key, date_creation FROM directory_zip_basket WHERE id_user = ? AND id_directory = ? ORDER BY id_record ;";
59 private static final String SQL_QUERY_DELETE_ELEMENT = "DELETE FROM directory_zip_basket WHERE id_zip_basket = ? ";
60 private static final String SQL_QUERY_UPDATE_STATUS = "UPDATE directory_zip_basket SET zip_state = ? , date_creation = ? WHERE id_zip_basket = ? ";
61 private static final String SQL_QUERY_UPDATE_URL = "UPDATE directory_zip_basket SET url = ? , date_creation = ? WHERE id_zip_basket = ? ";
62 private static final String SQL_QUERY_SELECT = "SELECT id_zip_basket, name, url, zip_state, id_user, id_directory, id_record, archive_item_key, date_creation FROM directory_zip_basket WHERE id_zip_basket = ? ";
63 private static final String SQL_QUERY_SELECT_BY_DATE = " SELECT id_zip_basket, name, url, zip_state, id_user, id_directory, id_record, archive_item_key, date_creation FROM directory_zip_basket WHERE date_creation <= ? ";
64
65
66
67
68
69
70 private int newPrimaryKey( Plugin plugin )
71 {
72 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_MAX_ID, plugin );
73 daoUtil.executeQuery( );
74
75 int nIdZipBasket = 1;
76
77 if ( daoUtil.next( ) )
78 {
79 nIdZipBasket = daoUtil.getInt( 1 ) + 1;
80 }
81
82 daoUtil.free( );
83
84 return nIdZipBasket;
85 }
86
87
88
89
90 public synchronized void addZipBasket( String strName, int nIdAdminUser, Plugin plugin, int nIdDirectory,
91 int nIdRecord, int nArchiveItemKey )
92 {
93 int nIdZipBasket = newPrimaryKey( plugin );
94
95 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, plugin );
96 daoUtil.setInt( 1, nIdZipBasket );
97 daoUtil.setString( 2, strName );
98 daoUtil.setString( 3, StatusZipEnum.PENDING.getId( ) );
99 daoUtil.setInt( 4, nIdAdminUser );
100 daoUtil.setInt( 5, nIdDirectory );
101 daoUtil.setInt( 6, nIdRecord );
102 daoUtil.setInt( 7, nArchiveItemKey );
103 daoUtil.executeUpdate( );
104 daoUtil.free( );
105 }
106
107
108
109
110 public boolean existsZipBasket( int nIdAdminUser, Plugin plugin, int nIdDirectory, int nIdRecord )
111 {
112 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_CHECK, plugin );
113 daoUtil.setInt( 1, nIdAdminUser );
114 daoUtil.setInt( 2, nIdDirectory );
115 daoUtil.setInt( 3, nIdRecord );
116 daoUtil.executeQuery( );
117
118 boolean bExistsZipBasket = daoUtil.next( );
119 daoUtil.free( );
120
121 return bExistsZipBasket;
122 }
123
124
125
126
127 public List<ZipBasket> loadAllZipBasket( Plugin plugin )
128 {
129 List<ZipBasket> lisZipBasket = new ArrayList<ZipBasket>( );
130
131 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ALL, plugin );
132 daoUtil.executeQuery( );
133
134 while ( daoUtil.next( ) )
135 {
136 ZipBasket zipBasket = new ZipBasket( );
137 zipBasket.setIdZip( daoUtil.getInt( 1 ) );
138 zipBasket.setZipName( daoUtil.getString( 2 ) );
139 zipBasket.setZipUrl( daoUtil.getString( 3 ) );
140 zipBasket.setZipStatus( daoUtil.getString( 4 ) );
141 zipBasket.setIdAdminUser( daoUtil.getInt( 5 ) );
142 zipBasket.setIdDirectory( daoUtil.getInt( 6 ) );
143 zipBasket.setIdRecord( daoUtil.getInt( 7 ) );
144 zipBasket.setArchiveItemKey( daoUtil.getInt( 8 ) );
145 zipBasket.setDateZipAdded( daoUtil.getTimestamp( 9 ) );
146
147 if ( zipBasket != null )
148 {
149 lisZipBasket.add( zipBasket );
150 }
151 }
152
153 daoUtil.free( );
154
155 return lisZipBasket;
156 }
157
158
159
160
161 public List<ZipBasket> loadAllZipBasketByAdminUser( Plugin plugin, int nIdAdminUser, int nIdDirectory )
162 {
163 List<ZipBasket> lisZipBasket = new ArrayList<ZipBasket>( );
164
165 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ALL_BY_ADMIN_USER, plugin );
166 daoUtil.setInt( 1, nIdAdminUser );
167 daoUtil.setInt( 2, nIdDirectory );
168 daoUtil.executeQuery( );
169
170 while ( daoUtil.next( ) )
171 {
172 ZipBasket zipBasket = new ZipBasket( );
173 zipBasket.setIdZip( daoUtil.getInt( 1 ) );
174 zipBasket.setZipName( daoUtil.getString( 2 ) );
175 zipBasket.setZipUrl( daoUtil.getString( 3 ) );
176 zipBasket.setZipStatus( daoUtil.getString( 4 ) );
177 zipBasket.setIdAdminUser( daoUtil.getInt( 5 ) );
178 zipBasket.setIdDirectory( daoUtil.getInt( 6 ) );
179 zipBasket.setIdRecord( daoUtil.getInt( 7 ) );
180 zipBasket.setArchiveItemKey( daoUtil.getInt( 8 ) );
181 zipBasket.setDateZipAdded( daoUtil.getTimestamp( 9 ) );
182
183 if ( zipBasket != null )
184 {
185 lisZipBasket.add( zipBasket );
186 }
187 }
188
189 daoUtil.free( );
190
191 return lisZipBasket;
192 }
193
194
195
196
197 public void deleteZipBasket( Plugin plugin, int nIdZipBasket )
198 {
199 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_ELEMENT, plugin );
200 daoUtil.setInt( 1, nIdZipBasket );
201 daoUtil.executeUpdate( );
202 daoUtil.free( );
203 }
204
205
206
207
208 public void deleteMultiZipBasket( Plugin plugin, List<Integer> listIdZipBasket )
209 {
210 for ( Integer idZipBasket : listIdZipBasket )
211 {
212 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_ELEMENT, plugin );
213 daoUtil.setInt( 1, idZipBasket.intValue( ) );
214 daoUtil.executeUpdate( );
215 daoUtil.free( );
216 }
217 }
218
219
220
221
222 public void changeZipBasketStatus( Plugin plugin, int nIdZipBasket, String strStatus )
223 {
224 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE_STATUS, plugin );
225 daoUtil.setString( 1, strStatus );
226 daoUtil.setTimestamp( 2, new Timestamp( GregorianCalendar.getInstance( ).getTimeInMillis( ) ) );
227 daoUtil.setInt( 3, nIdZipBasket );
228 daoUtil.executeUpdate( );
229 daoUtil.free( );
230 }
231
232
233
234
235 public void changeZipBasketUrl( Plugin plugin, int nIdZipBasket, String strUrl )
236 {
237 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE_URL, plugin );
238 daoUtil.setString( 1, strUrl );
239 daoUtil.setTimestamp( 2, new Timestamp( GregorianCalendar.getInstance( ).getTimeInMillis( ) ) );
240 daoUtil.setInt( 3, nIdZipBasket );
241 daoUtil.executeUpdate( );
242 daoUtil.free( );
243 }
244
245
246
247
248 public ZipBasket loadZipBasket( Plugin plugin, int nIdZipBasket )
249 {
250 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT, plugin );
251 daoUtil.setInt( 1, nIdZipBasket );
252 daoUtil.executeQuery( );
253
254 while ( daoUtil.next( ) )
255 {
256 ZipBasket zipBasket = new ZipBasket( );
257 zipBasket.setIdZip( daoUtil.getInt( 1 ) );
258 zipBasket.setZipName( daoUtil.getString( 2 ) );
259 zipBasket.setZipUrl( daoUtil.getString( 3 ) );
260 zipBasket.setZipStatus( daoUtil.getString( 4 ) );
261 zipBasket.setIdAdminUser( daoUtil.getInt( 5 ) );
262 zipBasket.setIdDirectory( daoUtil.getInt( 6 ) );
263 zipBasket.setIdRecord( daoUtil.getInt( 7 ) );
264 zipBasket.setArchiveItemKey( daoUtil.getInt( 8 ) );
265 zipBasket.setDateZipAdded( daoUtil.getTimestamp( 9 ) );
266 daoUtil.free( );
267
268 return zipBasket;
269 }
270
271 daoUtil.free( );
272
273 return null;
274 }
275
276
277
278
279 public List<ZipBasket> loadZipBasketByDate( Plugin plugin, Date dateExpiry )
280 {
281 List<ZipBasket> lisZipBasket = new ArrayList<ZipBasket>( );
282
283 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_DATE, plugin );
284 daoUtil.setDate( 1, new java.sql.Date( dateExpiry.getTime( ) ) );
285 daoUtil.executeQuery( );
286
287 while ( daoUtil.next( ) )
288 {
289 int nIndex = 1;
290 ZipBasket zipBasket = new ZipBasket( );
291 zipBasket.setIdZip( daoUtil.getInt( nIndex++ ) );
292 zipBasket.setZipName( daoUtil.getString( nIndex++ ) );
293 zipBasket.setZipUrl( daoUtil.getString( nIndex++ ) );
294 zipBasket.setZipStatus( daoUtil.getString( nIndex++ ) );
295 zipBasket.setIdAdminUser( daoUtil.getInt( nIndex++ ) );
296 zipBasket.setIdDirectory( daoUtil.getInt( nIndex++ ) );
297 zipBasket.setIdRecord( daoUtil.getInt( nIndex++ ) );
298 zipBasket.setArchiveItemKey( daoUtil.getInt( nIndex++ ) );
299 zipBasket.setDateZipAdded( daoUtil.getTimestamp( nIndex++ ) );
300
301 lisZipBasket.add( zipBasket );
302 }
303
304 daoUtil.free( );
305
306 return lisZipBasket;
307 }
308
309
310
311
312 public List<ZipBasket> loadAllZipBasketByAdminUserOrder( Plugin plugin, int nIdAdminUser, int nIdDirectory )
313 {
314 List<ZipBasket> lisZipBasket = new ArrayList<ZipBasket>( );
315
316 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ALL_BY_ADMIN_USER_ORDER, plugin );
317 daoUtil.setInt( 1, nIdAdminUser );
318 daoUtil.setInt( 2, nIdDirectory );
319 daoUtil.executeQuery( );
320
321 while ( daoUtil.next( ) )
322 {
323 ZipBasket zipBasket = new ZipBasket( );
324 zipBasket.setIdZip( daoUtil.getInt( 1 ) );
325 zipBasket.setZipName( daoUtil.getString( 2 ) );
326 zipBasket.setZipUrl( daoUtil.getString( 3 ) );
327 zipBasket.setZipStatus( daoUtil.getString( 4 ) );
328 zipBasket.setIdAdminUser( daoUtil.getInt( 5 ) );
329 zipBasket.setIdDirectory( daoUtil.getInt( 6 ) );
330 zipBasket.setIdRecord( daoUtil.getInt( 7 ) );
331 zipBasket.setArchiveItemKey( daoUtil.getInt( 8 ) );
332 zipBasket.setDateZipAdded( daoUtil.getTimestamp( 9 ) );
333
334 if ( zipBasket != null )
335 {
336 lisZipBasket.add( zipBasket );
337 }
338 }
339
340 daoUtil.free( );
341
342 return lisZipBasket;
343 }
344 }