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.announce.business;
35
36 import java.util.ArrayList;
37 import java.util.Collection;
38 import java.util.Locale;
39
40 import fr.paris.lutece.portal.service.i18n.I18nService;
41 import fr.paris.lutece.portal.service.plugin.Plugin;
42 import fr.paris.lutece.util.ReferenceItem;
43 import fr.paris.lutece.util.ReferenceList;
44 import fr.paris.lutece.util.sql.DAOUtil;
45
46
47
48
49 public final class SectorDAO implements ISectorDAO
50 {
51 private static final String SQL_QUERY_NEWPK = "SELECT max( id_sector ) FROM announce_sector ";
52 private static final String SQL_QUERY_SELECT = "SELECT id_sector, label_sector, description_sector, announces_validation, sector_order, tags FROM announce_sector WHERE id_sector = ? ";
53 private static final String SQL_QUERY_SELECTALL = "SELECT id_sector, label_sector, description_sector, announces_validation, sector_order,tags FROM announce_sector ORDER BY sector_ORDER";
54 private static final String SQL_QUERY_INSERT = "INSERT INTO announce_sector ( id_sector, label_sector, description_sector, announces_validation, sector_order, tags ) VALUES (?,?,?,?,?,?) ";
55 private static final String SQL_QUERY_DELETE = "DELETE FROM announce_sector WHERE id_sector = ? ";
56 private static final String SQL_QUERY_UPDATE = "UPDATE announce_sector SET label_sector = ?, description_sector = ?, announces_validation = ?, tags = ? WHERE id_sector = ? ";
57 private static final String SQL_QUERY_COUNT_CATEGORIES_FOR_FIELD = "SELECT COUNT(*) FROM announce_category WHERE id_sector = ?";
58
59
60 private static final String SQL_QUERY_SELECT_FIELD_ID_BY_ORDER = "SELECT id_sector FROM announce_sector WHERE sector_order = ? LIMIT 1";
61 private static final String SQL_QUERY_SELECT_FIELD_ORDER_BY_ID = "SELECT sector_order FROM announce_sector WHERE id_sector = ? LIMIT 1";
62 private static final String SQL_QUERY_UPDATE_FIELD_ORDER = "UPDATE announce_sector SET sector_order = ? WHERE id_sector = ?";
63 private static final String SQL_QUERY_SELECT_MAX_ORDER = "SELECT max(sector_order) FROM announce_sector";
64
65
66 private static final String PROPERTY_FIELD_REFERENCE_LIST_TOP_LABEL = "announce.sector.referenceListTopLabel";
67
68
69
70
71
72
73
74
75 private int newPrimaryKey( Plugin plugin )
76 {
77 int nKey = 1;
78 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEWPK, plugin ) )
79 {
80 daoUtil.executeQuery( );
81
82 if ( daoUtil.next( ) )
83 {
84 nKey = daoUtil.getInt( 1 ) + 1;
85 }
86 }
87 return nKey;
88 }
89
90
91
92
93 @Override
94 public void insert( Sector sector, Plugin plugin )
95 {
96 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, plugin ) )
97 {
98 sector.setId( newPrimaryKey( plugin ) );
99 daoUtil.setInt( 1, sector.getId( ) );
100 daoUtil.setString( 2, sector.getLabel( ) );
101 daoUtil.setString( 3, sector.getDescription( ) );
102 daoUtil.setBoolean( 4, sector.getAnnouncesValidation( ) );
103 daoUtil.setInt( 5, selectMaxOrder( plugin ) + 1 );
104 daoUtil.setString( 6, sector.getTags( ) );
105
106 daoUtil.executeUpdate( );
107 }
108 }
109
110
111
112
113 @Override
114 public Sector load( int nIdFIeld, Plugin plugin )
115 {
116 Sector sector = null;
117 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT, plugin ) )
118 {
119 daoUtil.setInt( 1, nIdFIeld );
120 daoUtil.executeQuery( );
121
122 if ( daoUtil.next( ) )
123 {
124 sector = new Sector( );
125 sector.setId( daoUtil.getInt( 1 ) );
126 sector.setLabel( daoUtil.getString( 2 ) );
127 sector.setDescription( daoUtil.getString( 3 ) );
128 sector.setAnnouncesValidation( daoUtil.getBoolean( 4 ) );
129 sector.setOrder( daoUtil.getInt( 5 ) );
130 sector.setTags( daoUtil.getString( 6 ) );
131 sector.setNumberCategories( countCategoriesForSector( sector, plugin ) );
132 }
133
134 }
135
136 return sector;
137 }
138
139
140
141
142 @Override
143 public void delete( Sector sector, Plugin plugin )
144 {
145 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin ) )
146 {
147 daoUtil.setInt( 1, sector.getId( ) );
148 daoUtil.executeUpdate( );
149 }
150 }
151
152
153
154
155 @Override
156 public void store( Sector sector, Plugin plugin )
157 {
158 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin ) )
159 {
160
161 daoUtil.setString( 1, sector.getLabel( ) );
162 daoUtil.setString( 2, sector.getDescription( ) );
163 daoUtil.setBoolean( 3, sector.getAnnouncesValidation( ) );
164 daoUtil.setString( 4, sector.getTags( ) );
165
166 daoUtil.setInt( 5, sector.getId( ) );
167
168 daoUtil.executeUpdate( );
169 }
170 }
171
172
173
174
175 @Override
176 public Collection<Sector> selectAll( Plugin plugin )
177 {
178 Collection<Sector> listSectors = new ArrayList<>( );
179 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL, plugin ) )
180 {
181 daoUtil.executeQuery( );
182
183 while ( daoUtil.next( ) )
184 {
185 Sectors/announce/business/Sector.html#Sector">Sector sector = new Sector( );
186 sector.setId( daoUtil.getInt( 1 ) );
187 sector.setLabel( daoUtil.getString( 2 ) );
188 sector.setDescription( daoUtil.getString( 3 ) );
189 sector.setAnnouncesValidation( daoUtil.getBoolean( 4 ) );
190 sector.setOrder( daoUtil.getInt( 5 ) );
191 sector.setTags( daoUtil.getString( 6 ) );
192 sector.setNumberCategories( countCategoriesForSector( sector, plugin ) );
193
194 listSectors.add( sector );
195 }
196
197 }
198
199 return listSectors;
200 }
201
202
203
204
205 @Override
206 public ReferenceList selectReferenceList( Plugin plugin )
207 {
208 ReferenceList listSectors = new ReferenceList( );
209
210 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL, plugin ) )
211 {
212 daoUtil.executeQuery( );
213
214 while ( daoUtil.next( ) )
215 {
216 ReferenceItem item = new ReferenceItem( );
217 item.setCode( daoUtil.getString( 1 ) );
218 item.setName( daoUtil.getString( 2 ) );
219
220 listSectors.add( item );
221 }
222
223 }
224
225 return listSectors;
226 }
227
228
229
230
231 @Override
232 public ReferenceList selectLocaleReferenceList( Plugin plugin, Locale locale )
233 {
234 ReferenceList frontListSectors = new ReferenceList( );
235 frontListSectors.addItem( "0", I18nService.getLocalizedString( PROPERTY_FIELD_REFERENCE_LIST_TOP_LABEL, locale ) );
236
237 ReferenceList listSectors = selectReferenceList( plugin );
238 frontListSectors.addAll( listSectors );
239
240 return frontListSectors;
241 }
242
243
244
245
246
247
248
249
250
251
252 private int countCategoriesForSector( Sector sector, Plugin plugin )
253 {
254 int nNumberCategories = 0;
255 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_COUNT_CATEGORIES_FOR_FIELD, plugin ) )
256 {
257
258 daoUtil.setInt( 1, sector.getId( ) );
259 daoUtil.executeQuery( );
260
261 if ( daoUtil.next( ) )
262 {
263 nNumberCategories = daoUtil.getInt( 1 );
264 }
265 }
266 return nNumberCategories;
267 }
268
269
270
271
272
273
274
275 @Override
276 public void storeOrder( int nNewOrder, int nId, Plugin plugin )
277 {
278 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE_FIELD_ORDER, plugin ) )
279 {
280 daoUtil.setInt( 1, nNewOrder );
281 daoUtil.setInt( 2, nId );
282 daoUtil.executeUpdate( );
283 }
284 }
285
286
287
288
289 @Override
290 public int selectIdByOrder( int nOrder, Plugin plugin )
291 {
292 int nResult = 1;
293 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_FIELD_ID_BY_ORDER, plugin ) )
294 {
295
296 daoUtil.setInt( 1, nOrder );
297 daoUtil.executeQuery( );
298
299 if ( daoUtil.next( ) )
300 {
301 nResult = daoUtil.getInt( 1 );
302 }
303 }
304 return nResult;
305 }
306
307
308
309
310 @Override
311 public int selectOrderById( int nId, Plugin plugin )
312 {
313 int nResult = 1;
314 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_FIELD_ORDER_BY_ID, plugin ) )
315 {
316 daoUtil.setInt( 1, nId );
317 daoUtil.executeQuery( );
318
319 if ( daoUtil.next( ) )
320 {
321 nResult = daoUtil.getInt( 1 );
322 }
323 }
324 return nResult;
325 }
326
327
328
329
330 @Override
331 public int selectMaxOrder( Plugin plugin )
332 {
333 int nOrder = 0;
334 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_MAX_ORDER, plugin ) )
335 {
336 daoUtil.executeQuery( );
337
338 if ( daoUtil.next( ) )
339 {
340 nOrder = daoUtil.getInt( 1 );
341 }
342 }
343 return nOrder;
344 }
345 }