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.util.ArrayList;
37 import java.util.List;
38
39 import fr.paris.lutece.portal.service.plugin.Plugin;
40 import fr.paris.lutece.util.sql.DAOUtil;
41
42
43
44
45 public final class VoteHistoryDAO implements IVoteHistoryDAO
46 {
47
48 private static final String SQL_QUERY_NEW_PK = "SELECT max( id ) FROM participatorybudget_votes_history";
49 private static final String SQL_QUERY_INSERT = "INSERT INTO participatorybudget_votes_history ( id_user, id_projet, date_vote, arrondissement, age,birth_date,ip_address, title, location, theme, status, id, status_export_stats) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ";
50 private static final String SQL_QUERY_DELETE = "DELETE FROM participatorybudget_votes_history WHERE id_user = ? AND id_projet = ?";
51 private static final String SQL_QUERY_SELECTALL = "SELECT id_user, id_projet, date_vote, arrondissement, age,birth_date,ip_address, title, location, theme, status, id, status_export_stats FROM participatorybudget_votes_history";
52 private static final String SQL_QUERY_DELETE_ALL = "DELETE FROM participatorybudget_votes_history WHERE id_user = ?";
53 private static final String SQL_QUERY_UPDATE_TAG = "UPDATE participatorybudget_votes_history SET status_export_stats= ?, date_vote= ? where id = ?";
54 private static final String SQl_QUERY_SELECT = SQL_QUERY_SELECTALL + " where id_user= ?";
55 private static final String SQl_QUERY_SELECT_By_PORJECT = "SELECT id_user, id_projet, date_vote, arrondissement, age,birth_date,ip_address, title, location, theme, status, status_export_stats FROM participatorybudget_votes_history where id_user = ? and id_projet= ?";
56 private static final String SQl_QUERY_COUNT_VOTE_ARR = "SELECT COUNT(*) FROM participatorybudget_votes_history where id_user= ? and location = ?";
57 private static final String SQl_QUERY_COUNT_VOTE = "SELECT COUNT(*) FROM participatorybudget_votes_history where id_user= ? and location <> ?";
58 private static final String SQl_BY_EXPORTED_STATUS = " WHERE status_export_stats = ?";
59
60
61
62
63
64
65
66
67 public int newPrimaryKey( Plugin plugin )
68 {
69 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_PK, plugin );
70 daoUtil.executeQuery( );
71
72 int nKey = 1;
73
74 if ( daoUtil.next( ) )
75 {
76 nKey = daoUtil.getInt( 1 ) + 1;
77 }
78
79 daoUtil.free( );
80
81 return nKey;
82 }
83
84
85
86
87
88
89
90
91
92 @Override
93 public void insert( Vote vote, Plugin plugin )
94 {
95 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, plugin );
96
97 vote.setId( newPrimaryKey( plugin ) );
98
99 daoUtil.setString( 1, vote.getUserId( ) );
100 daoUtil.setInt( 2, vote.getProjetId( ) );
101 daoUtil.setTimestamp( 3, vote.getDateVote( ) );
102 daoUtil.setInt( 4, vote.getArrondissement( ) );
103 daoUtil.setInt( 5, vote.getAge( ) );
104 daoUtil.setString( 6, vote.getBirthDate( ) );
105 daoUtil.setString( 7, vote.getIpAddress( ) );
106 daoUtil.setString( 8, vote.getTitle( ) );
107 daoUtil.setString( 9, vote.getLocation( ) );
108 daoUtil.setString( 10, vote.getTheme( ) );
109 daoUtil.setInt( 11, vote.geStatus( ) );
110 daoUtil.setInt( 12, vote.getId( ) );
111 daoUtil.setInt( 13, vote.getStatusExportStats( ) );
112
113 daoUtil.executeUpdate( );
114 daoUtil.free( );
115 }
116
117
118
119
120
121
122
123
124
125
126
127 public void delete( String strUserId, int nProjetId, Plugin plugin )
128 {
129 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin );
130 daoUtil.setString( 1, strUserId );
131 daoUtil.setInt( 2, nProjetId );
132
133 daoUtil.executeUpdate( );
134 daoUtil.free( );
135 }
136
137
138
139
140 @Override
141 public void updateTagStats( Vote vote, int nTag, Plugin plugin )
142 {
143 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE_TAG, plugin );
144
145 daoUtil.setInt( 1, nTag );
146 daoUtil.setTimestamp( 2, vote.getDateVote( ) );
147 daoUtil.setInt( 3, vote.getId( ) );
148 daoUtil.executeUpdate( );
149 daoUtil.free( );
150 }
151
152
153
154
155
156
157
158
159 @Override
160 public List<Vote> selectVotesList( Plugin plugin )
161 {
162 List<Vote> voteList = new ArrayList<Vote>( );
163 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL, plugin );
164 daoUtil.executeQuery( );
165
166 while ( daoUtil.next( ) )
167 {
168 Vote/plugins/participatorybudget/business/vote/Vote.html#Vote">Vote vote = new Vote( );
169
170 vote.setUserId( daoUtil.getString( 1 ) );
171 vote.setProjetId( daoUtil.getInt( 2 ) );
172 vote.setDateVote( daoUtil.getTimestamp( 3 ) );
173 vote.setArrondissement( daoUtil.getInt( 4 ) );
174 vote.setAge( daoUtil.getInt( 5 ) );
175 vote.setBirthDate( daoUtil.getString( 6 ) );
176 vote.setIpAddress( daoUtil.getString( 7 ) );
177 vote.setTitle( daoUtil.getString( 8 ) );
178 vote.setLocation( daoUtil.getString( 9 ) );
179 vote.setTheme( daoUtil.getString( 10 ) );
180 vote.setStatus( daoUtil.getInt( 11 ) );
181 vote.setId( daoUtil.getInt( 12 ) );
182 vote.setStatusExportStats( daoUtil.getInt( 13 ) );
183
184 voteList.add( vote );
185 }
186
187 daoUtil.free( );
188
189 return voteList;
190 }
191
192
193
194
195
196
197
198
199 @Override
200 public List<Vote> selectVotesListByExportStatus( Plugin plugin, int status )
201 {
202 List<Vote> voteList = new ArrayList<Vote>( );
203 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL + SQl_BY_EXPORTED_STATUS, plugin );
204 daoUtil.setInt( 1, status );
205 daoUtil.executeQuery( );
206
207 while ( daoUtil.next( ) )
208 {
209 Vote/plugins/participatorybudget/business/vote/Vote.html#Vote">Vote vote = new Vote( );
210
211 vote.setUserId( daoUtil.getString( 1 ) );
212 vote.setProjetId( daoUtil.getInt( 2 ) );
213 vote.setDateVote( daoUtil.getTimestamp( 3 ) );
214 vote.setArrondissement( daoUtil.getInt( 4 ) );
215 vote.setAge( daoUtil.getInt( 5 ) );
216 vote.setBirthDate( daoUtil.getString( 6 ) );
217 vote.setIpAddress( daoUtil.getString( 7 ) );
218 vote.setTitle( daoUtil.getString( 8 ) );
219 vote.setLocation( daoUtil.getString( 9 ) );
220 vote.setTheme( daoUtil.getString( 10 ) );
221 vote.setStatus( daoUtil.getInt( 11 ) );
222 vote.setId( daoUtil.getInt( 12 ) );
223 vote.setStatusExportStats( daoUtil.getInt( 13 ) );
224
225 voteList.add( vote );
226 }
227
228 daoUtil.free( );
229
230 return voteList;
231 }
232
233
234
235
236 @Override
237 public void deleteAll( String strUserId, Plugin plugin )
238 {
239
240 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_ALL, plugin );
241 daoUtil.setString( 1, strUserId );
242 daoUtil.executeUpdate( );
243 daoUtil.free( );
244
245 }
246
247
248
249
250
251
252
253
254 @Override
255 public List<Vote> selectVotesUser( String strUserId, Plugin plugin )
256 {
257 List<Vote> voteList = new ArrayList<Vote>( );
258 DAOUtil daoUtil = new DAOUtil( SQl_QUERY_SELECT, plugin );
259 daoUtil.setString( 1, strUserId );
260 daoUtil.executeQuery( );
261
262 while ( daoUtil.next( ) )
263 {
264 Vote/plugins/participatorybudget/business/vote/Vote.html#Vote">Vote vote = new Vote( );
265
266 vote.setUserId( daoUtil.getString( 1 ) );
267 vote.setProjetId( daoUtil.getInt( 2 ) );
268 vote.setDateVote( daoUtil.getTimestamp( 3 ) );
269 vote.setArrondissement( daoUtil.getInt( 4 ) );
270 vote.setAge( daoUtil.getInt( 5 ) );
271 vote.setBirthDate( daoUtil.getString( 6 ) );
272 vote.setIpAddress( daoUtil.getString( 7 ) );
273 vote.setTitle( daoUtil.getString( 8 ) );
274 vote.setLocation( daoUtil.getString( 9 ) );
275 vote.setTheme( daoUtil.getString( 10 ) );
276 vote.setStatus( daoUtil.getInt( 11 ) );
277 vote.setStatusExportStats( daoUtil.getInt( 12 ) );
278
279 voteList.add( vote );
280 }
281
282 daoUtil.free( );
283
284 return voteList;
285 }
286
287
288
289
290
291
292
293
294 @Override
295 public int countNbVotesUserArrondissement( String strUserId, int nLocation, Plugin plugin )
296 {
297 int nbrVotes = 0;
298 DAOUtil daoUtil = new DAOUtil( SQl_QUERY_COUNT_VOTE_ARR, plugin );
299 daoUtil.setString( 1, strUserId );
300 daoUtil.setInt( 2, nLocation );
301 daoUtil.executeQuery( );
302
303 if ( daoUtil.next( ) )
304 {
305 nbrVotes = daoUtil.getInt( 1 );
306 }
307
308 daoUtil.free( );
309
310 return nbrVotes;
311 }
312
313
314
315
316
317
318
319
320 @Override
321 public int countNbVotesUser( String strUserId, int nLocation, Plugin plugin )
322 {
323 int nbrVotes = 0;
324 DAOUtil daoUtil = new DAOUtil( SQl_QUERY_COUNT_VOTE, plugin );
325 daoUtil.setString( 1, strUserId );
326 daoUtil.setInt( 2, nLocation );
327 daoUtil.executeQuery( );
328
329 if ( daoUtil.next( ) )
330 {
331 nbrVotes = daoUtil.getInt( 1 );
332 }
333
334 daoUtil.free( );
335
336 return nbrVotes;
337 }
338
339 @Override
340 public Vote selectVoteUser( String strUserId, int nIdproject, Plugin plugin )
341 {
342
343 Vote vote = null;
344 DAOUtil daoUtil = new DAOUtil( SQl_QUERY_SELECT_By_PORJECT, plugin );
345 daoUtil.setString( 1, strUserId );
346 daoUtil.setInt( 2, nIdproject );
347 daoUtil.executeQuery( );
348
349 if ( daoUtil.next( ) )
350 {
351 vote = new Vote( );
352 vote.setUserId( daoUtil.getString( 1 ) );
353 vote.setProjetId( daoUtil.getInt( 2 ) );
354 vote.setDateVote( daoUtil.getTimestamp( 3 ) );
355 vote.setArrondissement( daoUtil.getInt( 4 ) );
356 vote.setAge( daoUtil.getInt( 5 ) );
357 vote.setBirthDate( daoUtil.getString( 6 ) );
358 vote.setIpAddress( daoUtil.getString( 7 ) );
359 vote.setTitle( daoUtil.getString( 8 ) );
360 vote.setLocation( daoUtil.getString( 9 ) );
361 vote.setTheme( daoUtil.getString( 10 ) );
362 vote.setStatus( daoUtil.getInt( 11 ) );
363 vote.setStatusExportStats( daoUtil.getInt( 12 ) );
364
365 }
366
367 daoUtil.free( );
368 return vote;
369 }
370 }