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 fr.paris.lutece.portal.service.plugin.Plugin;
37 import fr.paris.lutece.util.ReferenceList;
38 import fr.paris.lutece.util.sql.DAOUtil;
39
40 import java.util.ArrayList;
41 import java.util.List;
42
43
44
45
46 public final class CategoryDAO implements ICategoryDAO
47 {
48 private static final String SQL_QUERY_NEWPK = "SELECT max( id_category ) FROM announce_category ";
49 private static final String SQL_QUERY_SELECT = "SELECT id_category, id_sector, label_category, display_price, price_mandatory, announces_validation, id_mailing_list, id_workflow, display_captcha FROM announce_category WHERE id_category = ? ";
50 private static final String SQL_QUERY_SELECTALL = "SELECT a.id_category, a.id_sector, a.label_category, b.label_sector FROM announce_category a, announce_sector b WHERE a.id_sector = b.id_sector ORDER BY a.id_sector, a.label_category";
51 private static final String SQL_QUERY_INSERT = "INSERT INTO announce_category ( id_category, id_sector, label_category, display_price, price_mandatory, announces_validation, id_mailing_list, id_workflow, display_captcha ) VALUES (?,?,?,?,?,?,?,?,?) ";
52 private static final String SQL_QUERY_DELETE = "DELETE FROM announce_category WHERE id_category = ? ";
53 private static final String SQL_QUERY_UPDATE = "UPDATE announce_category SET id_sector = ?, label_category = ?, display_price = ?, price_mandatory = ?, announces_validation = ?, id_mailing_list = ?, id_workflow = ?, display_captcha = ? WHERE id_category = ? ";
54 private static final String SQL_QUERY_COUNT_ANNOUNCES_FOR_CATEORY = "SELECT COUNT(*) FROM announce_announce WHERE id_category = ?";
55 private static final String SQL_QUERY_COUNT_PUBLISHED_ANNOUNCES_FOR_CATEORY = "SELECT COUNT(*) FROM announce_announce WHERE id_category = ? AND published = 1 AND suspended = 0 AND suspended_by_user = 0 ";
56 private static final String SQL_QUERY_COUNT_ENTRIES_FOR_CATEGORY = "SELECT COUNT(*) FROM announce_announce WHERE id_category = ?";
57 private static final String SQL_QUERY_SELECT_CATEGORIES_FOR_FIELD = "SELECT id_category, id_sector, label_category FROM announce_category WHERE id_sector = ? ORDER BY label_category";
58 private static final String SQL_QUERY_SELECT_CATEGORIES_REFERENCELIST = "SELECT id_category, label_category FROM announce_category";
59
60
61
62
63
64
65
66
67 public int newPrimaryKey( Plugin plugin )
68 {
69 int nKey = 1;
70 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEWPK, plugin ) )
71 {
72 daoUtil.executeQuery( );
73
74 if ( daoUtil.next( ) )
75 {
76 nKey = daoUtil.getInt( 1 ) + 1;
77 }
78 }
79 return nKey;
80 }
81
82
83
84
85 @Override
86 public void insert( Category category, Plugin plugin )
87 {
88 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, plugin ) )
89 {
90 category.setId( newPrimaryKey( plugin ) );
91
92 int nIndex = 1;
93 daoUtil.setInt( nIndex++, category.getId( ) );
94 daoUtil.setInt( nIndex++, category.getIdSector( ) );
95 daoUtil.setString( nIndex++, category.getLabel( ) );
96 daoUtil.setBoolean( nIndex++, category.getDisplayPrice( ) );
97 daoUtil.setBoolean( nIndex++, category.getPriceMandatory( ) );
98 daoUtil.setInt( nIndex++, category.getAnnouncesValidation( ) );
99 daoUtil.setInt( nIndex++, category.getIdMailingList( ) );
100 daoUtil.setBoolean( nIndex++, category.getDisplayCaptcha( ) );
101 daoUtil.setInt( nIndex, category.getIdWorkflow( ) );
102 daoUtil.executeUpdate( );
103 }
104 }
105
106 @Override
107 public int copyCategory( Category category, Plugin plugin )
108 {
109 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, plugin ) )
110 {
111 category.setId( newPrimaryKey( plugin ) );
112
113 int nIndex = 1;
114 daoUtil.setInt( nIndex++, category.getId( ) );
115 daoUtil.setInt( nIndex++, category.getIdSector( ) );
116 daoUtil.setString( nIndex++, category.getLabel( ) );
117 daoUtil.setBoolean( nIndex++, category.getDisplayPrice( ) );
118 daoUtil.setBoolean( nIndex++, category.getPriceMandatory( ) );
119 daoUtil.setInt( nIndex++, category.getAnnouncesValidation( ) );
120 daoUtil.setInt( nIndex++, category.getIdMailingList( ) );
121 daoUtil.setBoolean( nIndex++, category.getDisplayCaptcha( ) );
122 daoUtil.setInt( nIndex, category.getIdWorkflow( ) );
123 daoUtil.executeUpdate( );
124 }
125 return category.getId( );
126 }
127
128
129
130
131 @Override
132 public Category load( int nCategoryId, Plugin plugin )
133 {
134 Category category = null;
135 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT, plugin ) )
136 {
137 daoUtil.setInt( 1, nCategoryId );
138 daoUtil.executeQuery( );
139
140 if ( daoUtil.next( ) )
141 {
142 category = new Category( );
143
144 int nIndex = 1;
145 category.setId( daoUtil.getInt( nIndex++ ) );
146 category.setIdSector( daoUtil.getInt( nIndex++ ) );
147 category.setLabel( daoUtil.getString( nIndex++ ) );
148 category.setDisplayPrice( daoUtil.getBoolean( nIndex++ ) );
149 category.setPriceMandatory( daoUtil.getBoolean( nIndex++ ) );
150 category.setAnnouncesValidation( daoUtil.getInt( nIndex++ ) );
151 category.setIdMailingList( daoUtil.getInt( nIndex++ ) );
152 category.setIdWorkflow( daoUtil.getInt( nIndex++ ) );
153 category.setDisplayCaptcha( daoUtil.getBoolean( nIndex++ ) );
154 category.setNumberAnnounces( countAnnouncesForCategory( category, plugin ) );
155 }
156 }
157 return category;
158 }
159
160
161
162
163 @Override
164 public void delete( Category category, Plugin plugin )
165 {
166 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin ) )
167 {
168 daoUtil.setInt( 1, category.getId( ) );
169 daoUtil.executeUpdate( );
170 }
171 }
172
173
174
175
176 @Override
177 public void store( Category category, Plugin plugin )
178 {
179 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin ) )
180 {
181 int nIndex = 1;
182 daoUtil.setInt( nIndex++, category.getIdSector( ) );
183 daoUtil.setString( nIndex++, category.getLabel( ) );
184 daoUtil.setBoolean( nIndex++, category.getDisplayPrice( ) );
185 daoUtil.setBoolean( nIndex++, category.getPriceMandatory( ) );
186 daoUtil.setInt( nIndex++, category.getAnnouncesValidation( ) );
187 daoUtil.setInt( nIndex++, category.getIdMailingList( ) );
188 daoUtil.setInt( nIndex++, category.getIdWorkflow( ) );
189 daoUtil.setBoolean( nIndex++, category.getDisplayCaptcha( ) );
190 daoUtil.setInt( nIndex, category.getId( ) );
191 daoUtil.executeUpdate( );
192 }
193 }
194
195
196
197
198 @Override
199 public List<Category> selectAll( Plugin plugin )
200 {
201 List<Category> listCategories = new ArrayList<>( );
202 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL, plugin ) )
203 {
204 daoUtil.executeQuery( );
205
206 while ( daoUtil.next( ) )
207 {
208 Categorynounce/business/Category.html#Category">Category category = new Category( );
209 category.setId( daoUtil.getInt( 1 ) );
210 category.setIdSector( daoUtil.getInt( 2 ) );
211 category.setLabel( daoUtil.getString( 3 ) );
212 category.setLabelSector( daoUtil.getString( 4 ) );
213 category.setNumberAnnounces( countAnnouncesForCategory( category, plugin ) );
214
215 listCategories.add( category );
216 }
217 }
218 return listCategories;
219 }
220
221
222
223
224
225
226
227
228
229
230 private int countAnnouncesForCategory( Category category, Plugin plugin )
231 {
232 int nNumberAnnounces = 0;
233 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_COUNT_ANNOUNCES_FOR_CATEORY, plugin ) )
234 {
235 daoUtil.setInt( 1, category.getId( ) );
236 daoUtil.executeQuery( );
237
238 if ( daoUtil.next( ) )
239 {
240 nNumberAnnounces = daoUtil.getInt( 1 );
241 }
242 }
243 return nNumberAnnounces;
244 }
245
246
247
248
249 @Override
250 public int countPublishedAnnouncesForCategory( Category category, Plugin plugin )
251 {
252 int nNumberAnnounces = 0;
253 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_COUNT_PUBLISHED_ANNOUNCES_FOR_CATEORY, plugin ) )
254 {
255 daoUtil.setInt( 1, category.getId( ) );
256 daoUtil.executeQuery( );
257
258 if ( daoUtil.next( ) )
259 {
260 nNumberAnnounces = daoUtil.getInt( 1 );
261 }
262 }
263 return nNumberAnnounces;
264 }
265
266
267
268
269 @Override
270 public int countEntriesForCategory( Category category, Plugin plugin )
271 {
272 int nNumberEntries = 0;
273 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_COUNT_ENTRIES_FOR_CATEGORY, plugin ) )
274 {
275 daoUtil.setInt( 1, category.getId( ) );
276 daoUtil.executeQuery( );
277
278 if ( daoUtil.next( ) )
279 {
280 nNumberEntries = daoUtil.getInt( 1 );
281 }
282 }
283 return nNumberEntries;
284 }
285
286
287
288
289 @Override
290 public List<Category> selectCategoriesForSector( Sector sector, Plugin plugin )
291 {
292 List<Category> listCategories = new ArrayList<>( );
293 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_CATEGORIES_FOR_FIELD, plugin ) )
294 {
295 daoUtil.setInt( 1, sector.getId( ) );
296 daoUtil.executeQuery( );
297
298 while ( daoUtil.next( ) )
299 {
300 Categorynounce/business/Category.html#Category">Category category = new Category( );
301 category.setId( daoUtil.getInt( 1 ) );
302 category.setIdSector( daoUtil.getInt( 2 ) );
303 category.setLabel( daoUtil.getString( 3 ) );
304 category.setNumberAnnounces( countPublishedAnnouncesForCategory( category, plugin ) );
305
306 listCategories.add( category );
307 }
308 }
309 return listCategories;
310 }
311
312
313
314
315 @Override
316 public ReferenceList selectCategoriesReferenceList( Plugin plugin )
317 {
318 ReferenceList listCategories = new ReferenceList( );
319 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_CATEGORIES_REFERENCELIST, plugin ) )
320 {
321 daoUtil.executeQuery( );
322
323 while ( daoUtil.next( ) )
324 {
325 listCategories.addItem( daoUtil.getInt( 1 ), daoUtil.getString( 2 ) );
326 }
327 }
328 return listCategories;
329 }
330 }