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.quiz.business;
35
36 import fr.paris.lutece.portal.service.plugin.Plugin;
37 import fr.paris.lutece.util.sql.DAOUtil;
38
39 import java.sql.Date;
40 import java.util.ArrayList;
41 import java.util.Collection;
42
43
44
45
46
47 public class QuizDAO implements IQuizDAO
48 {
49
50 private static final String SQL_QUERY_NEW_PK = " SELECT max( id_quiz ) FROM quiz_quiz ";
51 private static final String SQL_QUERY_SELECTALL_ID = "SELECT id_quiz FROM quiz_quiz";
52 private static final String SQL_QUERY_DELETE = " DELETE FROM quiz_quiz WHERE id_quiz= ? ";
53 private static final String SQL_QUERY_INSERT = " INSERT INTO quiz_quiz ( id_quiz, label_quiz, introduction, conclusion, status_quiz, activate_captcha, activate_requirement, date_begin_disponibility, date_end_disponibility, date_creation, cgu, type_quiz, display_step_by_step, results_at_the_end ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )";
54 private static final String SQL_QUERY_SELECT = " SELECT id_quiz, label_quiz, introduction, conclusion, status_quiz, activate_captcha, activate_requirement, date_begin_disponibility, date_end_disponibility, date_creation, cgu, type_quiz, display_step_by_step, results_at_the_end FROM quiz_quiz WHERE id_quiz = ? ";
55 private static final String SQL_QUERY_SELECT_LAST_QUIZ = " SELECT id_quiz, label_quiz, introduction, conclusion, activate_captcha, activate_requirement, status_quiz, date_begin_disponibility, date_end_disponibility, date_creation, cgu, type_quiz, display_step_by_step, results_at_the_end FROM quiz_quiz WHERE id_quiz = ( SELECT max( id_quiz ) FROM quiz_quiz ) ";
56 private static final String SQL_QUERY_UPDATE = " UPDATE quiz_quiz SET label_quiz = ?, introduction = ?, conclusion = ?, status_quiz = ?, activate_captcha = ?, activate_requirement = ?, date_begin_disponibility = ?, date_end_disponibility = ?, cgu = ?, display_step_by_step = ?, results_at_the_end = ? WHERE id_quiz = ? ";
57 private static final String SQL_QUERY_SELECTALL_ENABLED = "SELECT id_quiz FROM quiz_quiz WHERE status_quiz = 1";
58
59
60
61
62
63
64
65 int newPrimaryKey( Plugin plugin )
66 {
67 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_PK, plugin );
68 daoUtil.executeQuery( );
69
70 int nKey;
71
72 if ( !daoUtil.next( ) )
73 {
74
75 nKey = 1;
76 }
77
78 nKey = daoUtil.getInt( 1 ) + 1;
79 daoUtil.free( );
80
81 return nKey;
82 }
83
84
85
86
87
88
89
90 public void insert( Quiz quiz, Plugin plugin )
91 {
92 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, plugin );
93 quiz.setIdQuiz( newPrimaryKey( plugin ) );
94
95 daoUtil.setInt( 1, quiz.getIdQuiz( ) );
96 daoUtil.setString( 2, quiz.getName( ) );
97 daoUtil.setString( 3, quiz.getIntroduction( ) );
98 daoUtil.setString( 4, quiz.getConclusion( ) );
99 daoUtil.setBoolean( 5, false );
100 daoUtil.setInt( 6, quiz.getActiveCaptcha( ) );
101 daoUtil.setInt( 7, quiz.getActiveRequirement( ) );
102 daoUtil.setDate( 8, ( quiz.getDateBeginDisponibility( ) != null ) ? new Date( quiz.getDateBeginDisponibility( )
103 .getTime( ) ) : null );
104 daoUtil.setDate( 9, ( quiz.getDateEndDisponibility( ) != null ) ? new Date( quiz.getDateEndDisponibility( )
105 .getTime( ) ) : null );
106 daoUtil.setTimestamp( 10, quiz.getDateCreation( ) );
107 daoUtil.setString( 11, quiz.getCgu( ) );
108 daoUtil.setString( 12, quiz.getTypeQuiz( ) );
109 daoUtil.setBoolean( 13, quiz.getDisplayStepByStep( ) );
110 daoUtil.setBoolean( 14, quiz.getDisplayResultAfterEachStep( ) );
111
112 daoUtil.executeUpdate( );
113 daoUtil.free( );
114 }
115
116
117
118
119
120
121 public Collection<Quiz> selectQuizList( Plugin plugin )
122 {
123 Collection<Quiz> quizList = new ArrayList<Quiz>( );
124 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL_ID, plugin );
125 daoUtil.executeQuery( );
126
127 while ( daoUtil.next( ) )
128 {
129 quizList.add( load( daoUtil.getInt( 1 ), plugin ) );
130 }
131
132 daoUtil.free( );
133
134 return quizList;
135 }
136
137
138
139
140
141
142 public Collection<Quiz> selectQuizEnabledList( Plugin plugin )
143 {
144 Collection<Quiz> quizEnabledList = new ArrayList<Quiz>( );
145 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL_ENABLED, plugin );
146 daoUtil.executeQuery( );
147
148 while ( daoUtil.next( ) )
149 {
150 quizEnabledList.add( load( daoUtil.getInt( 1 ), plugin ) );
151 }
152
153 daoUtil.free( );
154
155 return quizEnabledList;
156 }
157
158
159
160
161
162
163
164 public void delete( int nIdQuiz, Plugin plugin )
165 {
166 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin );
167 daoUtil.setInt( 1, nIdQuiz );
168 daoUtil.executeUpdate( );
169 daoUtil.free( );
170 }
171
172
173
174
175
176
177
178
179 public Quiz load( int nIdQuiz, Plugin plugin )
180 {
181 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT, plugin );
182 daoUtil.setInt( 1, nIdQuiz );
183 daoUtil.executeQuery( );
184
185 Quiz quiz = null;
186
187 if ( daoUtil.next( ) )
188 {
189 quiz = new Quiz( );
190 quiz.setIdQuiz( daoUtil.getInt( 1 ) );
191 quiz.setName( daoUtil.getString( 2 ) );
192 quiz.setIntroduction( daoUtil.getString( 3 ) );
193 quiz.setConclusion( daoUtil.getString( 4 ) );
194 quiz.setStatus( daoUtil.getInt( 5 ) );
195 quiz.setActiveCaptcha( daoUtil.getInt( 6 ) );
196 quiz.setActiveRequirement( daoUtil.getInt( 7 ) );
197 quiz.setDateBeginDisponibility( daoUtil.getDate( 8 ) );
198 quiz.setDateEndDisponibility( daoUtil.getDate( 9 ) );
199 quiz.setDateCreation( daoUtil.getTimestamp( 10 ) );
200 quiz.setCgu( daoUtil.getString( 11 ) );
201 quiz.setTypeQuiz( daoUtil.getString( 12 ) );
202 quiz.setDisplayStepByStep( daoUtil.getBoolean( 13 ) );
203 quiz.setDisplayResultAfterEachStep( daoUtil.getBoolean( 14 ) );
204 }
205
206 daoUtil.free( );
207
208 return quiz;
209 }
210
211
212
213
214
215
216
217 public Quiz loadLastQuiz( Plugin plugin )
218 {
219 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_LAST_QUIZ, plugin );
220 daoUtil.executeQuery( );
221
222 Quiz quiz = null;
223
224 if ( daoUtil.next( ) )
225 {
226 quiz = new Quiz( );
227 quiz.setIdQuiz( daoUtil.getInt( 1 ) );
228 quiz.setName( daoUtil.getString( 2 ) );
229 quiz.setIntroduction( daoUtil.getString( 3 ) );
230 quiz.setConclusion( daoUtil.getString( 4 ) );
231 quiz.setStatus( daoUtil.getInt( 5 ) );
232 quiz.setActiveCaptcha( daoUtil.getInt( 6 ) );
233 quiz.setActiveRequirement( daoUtil.getInt( 7 ) );
234 quiz.setDateBeginDisponibility( daoUtil.getDate( 8 ) );
235 quiz.setDateEndDisponibility( daoUtil.getDate( 9 ) );
236 quiz.setDateCreation( daoUtil.getTimestamp( 10 ) );
237 quiz.setCgu( daoUtil.getString( 11 ) );
238 quiz.setTypeQuiz( daoUtil.getString( 12 ) );
239 quiz.setDisplayStepByStep( daoUtil.getBoolean( 13 ) );
240 quiz.setDisplayResultAfterEachStep( daoUtil.getBoolean( 14 ) );
241 }
242
243 daoUtil.free( );
244
245 return quiz;
246 }
247
248
249
250
251
252
253
254 public void store( Quiz quiz, Plugin plugin )
255 {
256 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin );
257 daoUtil.setString( 1, quiz.getName( ) );
258 daoUtil.setString( 2, quiz.getIntroduction( ) );
259 daoUtil.setString( 3, quiz.getConclusion( ) );
260 daoUtil.setBoolean( 4, quiz.isEnabled( ) );
261 daoUtil.setInt( 5, quiz.getActiveCaptcha( ) );
262 daoUtil.setInt( 6, quiz.getActiveRequirement( ) );
263 daoUtil.setDate( 7, ( quiz.getDateBeginDisponibility( ) != null ) ? new Date( quiz.getDateBeginDisponibility( )
264 .getTime( ) ) : null );
265 daoUtil.setDate( 8, ( quiz.getDateEndDisponibility( ) != null ) ? new Date( quiz.getDateEndDisponibility( )
266 .getTime( ) ) : null );
267 daoUtil.setString( 9, quiz.getCgu( ) );
268 daoUtil.setBoolean( 10, quiz.getDisplayStepByStep( ) );
269 daoUtil.setBoolean( 11, quiz.getDisplayResultAfterEachStep( ) );
270 daoUtil.setInt( 12, quiz.getIdQuiz( ) );
271
272 daoUtil.executeUpdate( );
273
274 daoUtil.free( );
275 }
276 }