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.participatorybudget.business.vote;
35
36 import java.text.SimpleDateFormat;
37 import java.util.ArrayList;
38 import java.util.HashMap;
39 import java.util.List;
40 import java.util.Map;
41
42 import fr.paris.lutece.portal.service.plugin.Plugin;
43 import fr.paris.lutece.util.sql.DAOUtil;
44
45
46
47
48 public final class VoteDAO implements IVoteDAO
49 {
50 private static final int CAMPAIGN_CODE_DOCUMENT_ATTR_ID = 165;
51
52
53 private static final String SQL_QUERY_INSERT = "INSERT INTO participatorybudget_votes ( id_user, id_projet, date_vote, arrondissement, age,birth_date,ip_address, title, location, theme, status) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ";
54 private static final String SQL_QUERY_DELETE = "DELETE FROM participatorybudget_votes WHERE id_user = ? AND id_projet = ?";
55 private static final String SQL_QUERY_SELECTALL = "SELECT id_user, id_projet, date_vote, arrondissement, age,birth_date,ip_address, title, location, theme, status FROM participatorybudget_votes";
56 private static final String SQL_QUERY_DELETE_ALL = "DELETE FROM participatorybudget_votes WHERE id_user = ?";
57 private static final String SQl_QUERY_SELECT = SQL_QUERY_SELECTALL + " where id_user= ?";
58 private static final String SQL_QUERY_SELECT_VOTE = SQl_QUERY_SELECT + " and id_projet= ?";
59 private static final String SQl_QUERY_COUNT_VOTE_ARR = "SELECT COUNT(*) FROM participatorybudget_votes where id_user= ? and location = ?";
60 private static final String SQl_QUERY_COUNT_VOTE = "SELECT COUNT(*) FROM participatorybudget_votes where id_user= ? and location <> ?";
61 private static final String SQl_QUERY_COUNT_VOTE_BY_CAMPAIGN = "SELECT dc.text_value, COUNT(*) FROM participatorybudget_votes v JOIN document_content dc ON dc.id_document = v.id_projet AND dc.id_document_attr = "
62 + CAMPAIGN_CODE_DOCUMENT_ATTR_ID + " GROUP BY dc.text_value";
63 private static final String SQl_QUERY_COUNT_VOTE_BY_DATE_BY_CAMPAIGN = "SELECT dc.text_value, CONVERT(v.date_vote, DATE), COUNT(*) FROM participatorybudget_votes v JOIN document_content dc ON dc.id_document = v.id_projet AND dc.id_document_attr = "
64 + CAMPAIGN_CODE_DOCUMENT_ATTR_ID + " GROUP BY dc.text_value, CONVERT(v.date_vote, DATE)";
65 private static final String SQl_QUERY_COUNT_VOTE_BY_THEME = "SELECT v.theme, COUNT(*) FROM participatorybudget_votes v JOIN document_content dc ON dc.id_document = v.id_projet AND dc.id_document_attr = "
66 + CAMPAIGN_CODE_DOCUMENT_ATTR_ID + " JOIN participatorybudget_campaign c ON c.code_campaign = dc.text_value AND c.id_campaign = ? GROUP BY v.theme";
67 private static final String SQl_QUERY_COUNT_VOTE_BY_LOCATION = "SELECT v.location, COUNT(*) FROM participatorybudget_votes v JOIN document_content dc ON dc.id_document = v.id_projet AND dc.id_document_attr = "
68 + CAMPAIGN_CODE_DOCUMENT_ATTR_ID
69 + " JOIN participatorybudget_campaign c ON c.code_campaign = dc.text_value AND c.id_campaign = ? GROUP BY v.location";
70 private static final String SQl_QUERY_COUNT_VOTE_BY_PROJECT_ID = "SELECT v.id_projet, COUNT(*) FROM participatorybudget_votes v JOIN document_content dc ON dc.id_document = v.id_projet AND dc.id_document_attr = "
71 + CAMPAIGN_CODE_DOCUMENT_ATTR_ID
72 + " JOIN participatorybudget_campaign c ON c.code_campaign = dc.text_value AND c.id_campaign = ? GROUP BY v.id_projet";
73 private static final String SQL_QUERY_SELECT_USER = "SELECT DISTINCT id_user FROM participatorybudget_votes";
74 private static final String SQL_QUERY_VALIDATE_VOTE = "UPDATE participatorybudget_votes SET status= ? where id_user=?";
75 private static final String SQL_QUERY_SELECT_VOTE_STATUS = "SELECT id_user, id_projet, date_vote, arrondissement, age,birth_date,ip_address, title, location, theme, status FROM participatorybudget_votes where id_user= ? and status = ?";
76
77 private final SimpleDateFormat sdf = new SimpleDateFormat( "yyyy-MM-dd" );
78
79
80
81
82
83
84
85
86
87 @Override
88 public void insert( Vote vote, Plugin plugin )
89 {
90 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, plugin ) )
91 {
92 daoUtil.setString( 1, vote.getUserId( ) );
93 daoUtil.setInt( 2, vote.getProjetId( ) );
94 daoUtil.setTimestamp( 3, vote.getDateVote( ) );
95 daoUtil.setInt( 4, vote.getArrondissement( ) );
96 daoUtil.setInt( 5, vote.getAge( ) );
97 daoUtil.setString( 6, vote.getBirthDate( ) );
98 daoUtil.setString( 7, vote.getIpAddress( ) );
99 daoUtil.setString( 8, vote.getTitle( ) );
100 daoUtil.setString( 9, vote.getLocation( ) );
101 daoUtil.setString( 10, vote.getTheme( ) );
102 daoUtil.setInt( 11, vote.geStatus( ) );
103
104 daoUtil.executeUpdate( );
105 }
106 }
107
108
109
110
111
112
113
114
115
116
117
118 public void delete( String strUserId, int nProjetId, Plugin plugin )
119 {
120 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin ) )
121 {
122 daoUtil.setString( 1, strUserId );
123 daoUtil.setInt( 2, nProjetId );
124
125 daoUtil.executeUpdate( );
126 }
127 }
128
129
130
131
132
133
134
135
136 @Override
137 public List<Vote> selectVotesList( Plugin plugin )
138 {
139 List<Vote> voteList = new ArrayList<Vote>( );
140
141 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL, plugin ) )
142 {
143 daoUtil.executeQuery( );
144
145 while ( daoUtil.next( ) )
146 {
147 Vote/plugins/participatorybudget/business/vote/Vote.html#Vote">Vote vote = new Vote( );
148
149 vote.setUserId( daoUtil.getString( 1 ) );
150 vote.setProjetId( daoUtil.getInt( 2 ) );
151 vote.setDateVote( daoUtil.getTimestamp( 3 ) );
152 vote.setArrondissement( daoUtil.getInt( 4 ) );
153 vote.setAge( daoUtil.getInt( 5 ) );
154 vote.setBirthDate( daoUtil.getString( 6 ) );
155 vote.setIpAddress( daoUtil.getString( 7 ) );
156 vote.setTitle( daoUtil.getString( 8 ) );
157 vote.setLocation( daoUtil.getString( 9 ) );
158 vote.setTheme( daoUtil.getString( 10 ) );
159 vote.setStatus( daoUtil.getInt( 11 ) );
160
161 voteList.add( vote );
162 }
163 }
164
165 return voteList;
166 }
167
168
169
170
171 @Override
172 public void deleteAll( String strUserId, Plugin plugin )
173 {
174 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_ALL, plugin ) )
175 {
176 daoUtil.setString( 1, strUserId );
177
178 daoUtil.executeUpdate( );
179 }
180 }
181
182
183
184
185
186
187
188
189 @Override
190 public List<Vote> selectVotesUser( String strUserId, Plugin plugin )
191 {
192 List<Vote> voteList = new ArrayList<Vote>( );
193
194 try ( DAOUtil daoUtil = new DAOUtil( SQl_QUERY_SELECT, plugin ) )
195 {
196 daoUtil.setString( 1, strUserId );
197
198 daoUtil.executeQuery( );
199
200 while ( daoUtil.next( ) )
201 {
202 Vote/plugins/participatorybudget/business/vote/Vote.html#Vote">Vote vote = new Vote( );
203
204 vote.setUserId( daoUtil.getString( 1 ) );
205 vote.setProjetId( daoUtil.getInt( 2 ) );
206 vote.setDateVote( daoUtil.getTimestamp( 3 ) );
207 vote.setArrondissement( daoUtil.getInt( 4 ) );
208 vote.setAge( daoUtil.getInt( 5 ) );
209 vote.setBirthDate( daoUtil.getString( 6 ) );
210 vote.setIpAddress( daoUtil.getString( 7 ) );
211 vote.setTitle( daoUtil.getString( 8 ) );
212 vote.setLocation( daoUtil.getString( 9 ) );
213 vote.setTheme( daoUtil.getString( 10 ) );
214 vote.setStatus( daoUtil.getInt( 11 ) );
215
216 voteList.add( vote );
217 }
218 }
219
220 return voteList;
221 }
222
223
224
225
226
227
228
229
230 @Override
231 public int countNbVotesUserArrondissement( String strUserId, int nLocation, Plugin plugin )
232 {
233 int nbrVotes = 0;
234
235 try ( DAOUtil daoUtil = new DAOUtil( SQl_QUERY_COUNT_VOTE_ARR, plugin ) )
236 {
237 daoUtil.setString( 1, strUserId );
238 daoUtil.setInt( 2, nLocation );
239
240 daoUtil.executeQuery( );
241
242 if ( daoUtil.next( ) )
243 {
244 nbrVotes = daoUtil.getInt( 1 );
245 }
246 }
247
248 return nbrVotes;
249 }
250
251
252
253
254
255
256
257
258 @Override
259 public int countNbVotesUser( String strUserId, int nLocation, Plugin plugin )
260 {
261 int nbrVotes = 0;
262
263 try ( DAOUtil daoUtil = new DAOUtil( SQl_QUERY_COUNT_VOTE, plugin ) )
264 {
265 daoUtil.setString( 1, strUserId );
266 daoUtil.setInt( 2, nLocation );
267
268 daoUtil.executeQuery( );
269
270 if ( daoUtil.next( ) )
271 {
272 nbrVotes = daoUtil.getInt( 1 );
273 }
274 }
275
276 return nbrVotes;
277 }
278
279 @Override
280 public Map<String, Integer> countNbVotesByCampaignCode( Plugin plugin )
281 {
282 Map<String, Integer> values = new HashMap<>( );
283
284 try ( DAOUtil daoUtil = new DAOUtil( SQl_QUERY_COUNT_VOTE_BY_CAMPAIGN, plugin ) )
285 {
286 daoUtil.executeQuery( );
287
288 while ( daoUtil.next( ) )
289 {
290 values.put( daoUtil.getString( 1 ), daoUtil.getInt( 2 ) );
291 }
292 }
293
294 return values;
295 }
296
297 @Override
298 public Map<String, Map<String, Integer>> countNbVotesByDateAllCampaigns( Plugin plugin )
299 {
300 Map<String, Map<String, Integer>> values = new HashMap<>( );
301
302 try ( DAOUtil daoUtil = new DAOUtil( SQl_QUERY_COUNT_VOTE_BY_DATE_BY_CAMPAIGN, plugin ) )
303 {
304 daoUtil.executeQuery( );
305
306 while ( daoUtil.next( ) )
307 {
308
309 String campaignCode = daoUtil.getString( 1 );
310 if ( !values.containsKey( campaignCode ) )
311 {
312 values.put( campaignCode, new HashMap<>( ) );
313 }
314 Map<String, Integer> campaignValues = values.get( campaignCode );
315
316
317 String date = sdf.format( daoUtil.getTimestamp( 2 ) );
318 int nbVotes = daoUtil.getInt( 3 );
319 campaignValues.put( date, nbVotes );
320 }
321 }
322
323 return values;
324 }
325
326 @Override
327 public Map<String, Integer> countNbVotesByTheme( int campaignId, Plugin plugin )
328 {
329 Map<String, Integer> values = new HashMap<>( );
330
331 try ( DAOUtil daoUtil = new DAOUtil( SQl_QUERY_COUNT_VOTE_BY_THEME, plugin ) )
332 {
333 daoUtil.setInt( 1, campaignId );
334 daoUtil.executeQuery( );
335
336 while ( daoUtil.next( ) )
337 {
338 values.put( daoUtil.getString( 1 ), daoUtil.getInt( 2 ) );
339 }
340 }
341
342 return values;
343 }
344
345 @Override
346 public Map<String, Integer> countNbVotesByLocation( int campaignId, Plugin plugin )
347 {
348 Map<String, Integer> values = new HashMap<>( );
349
350 try ( DAOUtil daoUtil = new DAOUtil( SQl_QUERY_COUNT_VOTE_BY_LOCATION, plugin ) )
351 {
352 daoUtil.setInt( 1, campaignId );
353 daoUtil.executeQuery( );
354
355 while ( daoUtil.next( ) )
356 {
357 values.put( daoUtil.getString( 1 ), daoUtil.getInt( 2 ) );
358 }
359 }
360
361 return values;
362 }
363
364 @Override
365 public Map<Integer, Integer> countNbVotesByProjectId( int campaignId, Plugin plugin )
366 {
367 Map<Integer, Integer> values = new HashMap<>( );
368
369 try ( DAOUtil daoUtil = new DAOUtil( SQl_QUERY_COUNT_VOTE_BY_PROJECT_ID, plugin ) )
370 {
371 daoUtil.setInt( 1, campaignId );
372 daoUtil.executeQuery( );
373
374 while ( daoUtil.next( ) )
375 {
376 values.put( daoUtil.getInt( 1 ), daoUtil.getInt( 2 ) );
377 }
378 }
379
380 return values;
381 }
382
383
384
385
386 @Override
387 public Vote selectVote( String strUserId, int nIdproject, Plugin plugin )
388 {
389 Vote vote = null;
390
391 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_VOTE, plugin ) )
392 {
393 daoUtil.setString( 1, strUserId );
394 daoUtil.setInt( 2, nIdproject );
395
396 daoUtil.executeQuery( );
397
398 if ( daoUtil.next( ) )
399 {
400 vote = new Vote( );
401
402 vote.setUserId( daoUtil.getString( 1 ) );
403 vote.setProjetId( daoUtil.getInt( 2 ) );
404 vote.setDateVote( daoUtil.getTimestamp( 3 ) );
405 vote.setArrondissement( daoUtil.getInt( 4 ) );
406 vote.setAge( daoUtil.getInt( 5 ) );
407 vote.setBirthDate( daoUtil.getString( 6 ) );
408 vote.setIpAddress( daoUtil.getString( 7 ) );
409 vote.setTitle( daoUtil.getString( 8 ) );
410 vote.setLocation( daoUtil.getString( 9 ) );
411 vote.setTheme( daoUtil.getString( 10 ) );
412 vote.setStatus( daoUtil.getInt( 11 ) );
413 }
414 }
415
416 return vote;
417 }
418
419
420
421
422
423
424 @Override
425 public List<String> selectUser( Plugin plugin )
426 {
427 List<String> userList = new ArrayList<>( );
428
429 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_USER, plugin ) )
430 {
431 daoUtil.executeQuery( );
432
433 while ( daoUtil.next( ) )
434 {
435 userList.add( daoUtil.getString( 1 ) );
436 }
437 }
438
439 return userList;
440 }
441
442 @Override
443 public void validateVote( String strUserId, int statusVote, Plugin plugin )
444 {
445 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_VALIDATE_VOTE, plugin ) )
446 {
447 daoUtil.setInt( 1, statusVote );
448 daoUtil.setString( 2, strUserId );
449
450 daoUtil.executeUpdate( );
451 }
452 }
453
454 @Override
455 public List<Vote> selectVotes( String strUserId, int statusVote, Plugin plugin )
456 {
457 List<Vote> voteList = new ArrayList<>( );
458
459 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_VOTE_STATUS, plugin ) )
460 {
461 daoUtil.setString( 1, strUserId );
462 daoUtil.setInt( 2, statusVote );
463
464 daoUtil.executeQuery( );
465
466 while ( daoUtil.next( ) )
467 {
468 Vote/plugins/participatorybudget/business/vote/Vote.html#Vote">Vote vote = new Vote( );
469
470 vote.setUserId( daoUtil.getString( 1 ) );
471 vote.setProjetId( daoUtil.getInt( 2 ) );
472 vote.setDateVote( daoUtil.getTimestamp( 3 ) );
473 vote.setArrondissement( daoUtil.getInt( 4 ) );
474 vote.setAge( daoUtil.getInt( 5 ) );
475 vote.setBirthDate( daoUtil.getString( 6 ) );
476 vote.setIpAddress( daoUtil.getString( 7 ) );
477 vote.setTitle( daoUtil.getString( 8 ) );
478 vote.setLocation( daoUtil.getString( 9 ) );
479 vote.setTheme( daoUtil.getString( 10 ) );
480 vote.setStatus( daoUtil.getInt( 11 ) );
481
482 voteList.add( vote );
483 }
484 }
485
486 return voteList;
487 }
488
489 }