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.appointment.business.form;
35
36 import java.sql.Statement;
37 import java.util.ArrayList;
38 import java.util.List;
39
40 import fr.paris.lutece.portal.service.plugin.Plugin;
41 import fr.paris.lutece.util.sql.DAOUtil;
42
43
44
45
46
47
48
49 public final class FormDAO implements IFormDAO
50 {
51
52 private static final String SQL_QUERY_INSERT = "INSERT INTO appointment_form ( title, description, reference, id_category, starting_validity_date, ending_validity_date, is_active, id_workflow, workgroup,is_multislot_appointment, nb_consecutive_slots, role_fo, capacity_per_slot, is_anonymizable, anonymization_pattern ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )";
53 private static final String SQL_QUERY_UPDATE = "UPDATE appointment_form SET title = ?, description = ?, reference = ?, id_category = ?, starting_validity_date = ?, ending_validity_date = ?, is_active = ?, id_workflow = ?, workgroup = ?, is_multislot_appointment = ?, nb_consecutive_slots= ?, role_fo = ?, capacity_per_slot= ?, is_anonymizable=?, anonymization_pattern=? WHERE id_form = ?";
54 private static final String SQL_QUERY_DELETE = "DELETE FROM appointment_form WHERE id_form = ? ";
55 private static final String SQL_QUERY_SELECT_COLUMNS = "SELECT form.id_form, form.title, form.description, form.reference, form.id_category, form.starting_validity_date, form.ending_validity_date, form.is_active, form.id_workflow, form.workgroup, form.is_multislot_appointment, form.nb_consecutive_slots, form.role_fo, form.capacity_per_slot, form.is_anonymizable, form.anonymization_pattern FROM appointment_form form";
56 private static final String SQL_QUERY_SELECT_BY_TITLE = SQL_QUERY_SELECT_COLUMNS + " WHERE title = ?";
57 private static final String SQL_QUERY_SELECT_ALL = SQL_QUERY_SELECT_COLUMNS;
58 private static final String SQL_QUERY_SELECT = SQL_QUERY_SELECT_COLUMNS + " WHERE id_form = ?";
59 private static final String SQL_QUERY_SELECT_ACTIVE_FORMS = SQL_QUERY_SELECT_COLUMNS + " WHERE is_active = 1";
60 private static final String SQL_QUERY_SELECT_ACTIVE_AND_DISPLAYED_ON_PORTLET_FORMS = SQL_QUERY_SELECT_COLUMNS
61 + " INNER JOIN appointment_display display ON form.id_form = display.id_form WHERE form.is_active = 1 AND display.is_displayed_on_portlet = 1";
62 private static final String SQL_QUERY_SELECT_BY_CATEGORY = SQL_QUERY_SELECT_COLUMNS + " WHERE id_category = ?";
63
64 @Override
65 public void insert( Form form, Plugin plugin )
66 {
67 try ( DAOUtil daoUtil = buildDaoUtil( SQL_QUERY_INSERT, form, plugin, true ) )
68 {
69 daoUtil.executeUpdate( );
70 if ( daoUtil.nextGeneratedKey( ) )
71 {
72 form.setIdForm( daoUtil.getGeneratedKeyInt( 1 ) );
73 }
74 }
75 }
76
77 @Override
78 public void update( Form form, Plugin plugin )
79 {
80 try ( DAOUtil daoUtil = buildDaoUtil( SQL_QUERY_UPDATE, form, plugin, false ) )
81 {
82 daoUtil.executeUpdate( );
83 }
84 }
85
86 @Override
87 public void delete( int nIdForm, Plugin plugin )
88 {
89 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin ) )
90 {
91 daoUtil.setInt( 1, nIdForm );
92 daoUtil.executeUpdate( );
93 }
94 }
95
96 @Override
97 public Form select( int nIdForm, Plugin plugin )
98 {
99 Form form = null;
100 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT, plugin ) )
101 {
102 daoUtil.setInt( 1, nIdForm );
103 daoUtil.executeQuery( );
104 if ( daoUtil.next( ) )
105 {
106 form = buildForm( daoUtil );
107 }
108 }
109 return form;
110 }
111
112 @Override
113 public List<Form> selectByCategory( int nIdCategory, Plugin plugin )
114 {
115 List<Form> listForms = new ArrayList<>( );
116 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_CATEGORY, plugin ) )
117 {
118 daoUtil.setInt( 1, nIdCategory );
119 daoUtil.executeQuery( );
120 while ( daoUtil.next( ) )
121 {
122 listForms.add( buildForm( daoUtil ) );
123 }
124 }
125 return listForms;
126 }
127
128 @Override
129 public List<Form> findActiveForms( Plugin plugin )
130 {
131 List<Form> listForms = new ArrayList<>( );
132 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ACTIVE_FORMS, plugin ) )
133 {
134 daoUtil.executeQuery( );
135 while ( daoUtil.next( ) )
136 {
137 listForms.add( buildForm( daoUtil ) );
138 }
139 }
140 return listForms;
141 }
142
143 @Override
144 public List<Form> findActiveAndDisplayedOnPortletForms( Plugin plugin )
145 {
146 List<Form> listForms = new ArrayList<>( );
147 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ACTIVE_AND_DISPLAYED_ON_PORTLET_FORMS, plugin ) )
148 {
149 daoUtil.executeQuery( );
150 while ( daoUtil.next( ) )
151 {
152 listForms.add( buildForm( daoUtil ) );
153 }
154 }
155 return listForms;
156 }
157
158 @Override
159 public List<Form> findByTitle( String strTitle, Plugin plugin )
160 {
161 List<Form> listForms = new ArrayList<>( );
162 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_TITLE, plugin ) )
163 {
164 daoUtil.setString( 1, strTitle );
165 daoUtil.executeQuery( );
166 while ( daoUtil.next( ) )
167 {
168 listForms.add( buildForm( daoUtil ) );
169 }
170 }
171 return listForms;
172 }
173
174 @Override
175 public List<Form> findAllForms( Plugin plugin )
176 {
177 List<Form> listForms = new ArrayList<>( );
178 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ALL, plugin ) )
179 {
180 daoUtil.executeQuery( );
181 while ( daoUtil.next( ) )
182 {
183 listForms.add( buildForm( daoUtil ) );
184 }
185 }
186 return listForms;
187 }
188
189
190
191
192
193
194
195
196 private Form buildForm( DAOUtil daoUtil )
197 {
198 int nIndex = 1;
199 Form/plugins/appointment/business/form/Form.html#Form">Form form = new Form( );
200 form.setIdForm( daoUtil.getInt( nIndex++ ) );
201 form.setTitle( daoUtil.getString( nIndex++ ) );
202 form.setDescription( daoUtil.getString( nIndex++ ) );
203 form.setReference( daoUtil.getString( nIndex++ ) );
204 form.setIdCategory( daoUtil.getInt( nIndex++ ) );
205 form.setStartingValiditySqlDate( daoUtil.getDate( nIndex++ ) );
206 form.setEndingValiditySqlDate( daoUtil.getDate( nIndex++ ) );
207 form.setIsActive( daoUtil.getBoolean( nIndex++ ) );
208 form.setIdWorkflow( daoUtil.getInt( nIndex++ ) );
209 form.setWorkgroup( daoUtil.getString( nIndex++ ) );
210 form.setIsMultislotAppointment( daoUtil.getBoolean( nIndex++ ) );
211 form.setNbConsecutiveSlots( daoUtil.getInt( nIndex++ ) );
212 form.setRole( daoUtil.getString( nIndex++ ) );
213 form.setCapacityPerSlot( daoUtil.getInt( nIndex++ ) );
214 form.setAnonymizable( daoUtil.getBoolean( nIndex++ ) );
215 form.setAnonymizationPattern(daoUtil.getString(nIndex));
216
217 return form;
218 }
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234 private DAOUtil buildDaoUtil( String query, Form form, Plugin plugin, boolean isInsert )
235 {
236 int nIndex = 1;
237 DAOUtil daoUtil = null;
238 if ( isInsert )
239 {
240 daoUtil = new DAOUtil( query, Statement.RETURN_GENERATED_KEYS, plugin );
241 }
242 else
243 {
244 daoUtil = new DAOUtil( query, plugin );
245 }
246 daoUtil.setString( nIndex++, form.getTitle( ) );
247 daoUtil.setString( nIndex++, form.getDescription( ) );
248 daoUtil.setString( nIndex++, form.getReference( ) );
249 if ( form.getIdCategory( ) == null || form.getIdCategory( ) == 0 )
250 {
251 daoUtil.setIntNull( nIndex++ );
252 }
253 else
254 {
255 daoUtil.setInt( nIndex++, form.getIdCategory( ) );
256 }
257 daoUtil.setDate( nIndex++, form.getStartingValiditySqlDate( ) );
258 daoUtil.setDate( nIndex++, form.getEndingValiditySqlDate( ) );
259 daoUtil.setBoolean( nIndex++, form.getIsActive( ) );
260 daoUtil.setInt( nIndex++, form.getIdWorkflow( ) );
261 daoUtil.setString( nIndex++, form.getWorkgroup( ) );
262 daoUtil.setBoolean( nIndex++, form.getIsMultislotAppointment( ) );
263 daoUtil.setInt( nIndex++, form.getNbConsecutiveSlots( ) );
264 daoUtil.setString( nIndex++, form.getRole( ) );
265 daoUtil.setInt( nIndex++, form.getCapacityPerSlot( ) );
266 daoUtil.setBoolean( nIndex++, form.isAnonymizable() );
267 daoUtil.setString( nIndex++, form.getAnonymizationPattern( ) );
268
269 if ( !isInsert )
270 {
271 daoUtil.setInt( nIndex, form.getIdForm( ) );
272 }
273
274 return daoUtil;
275 }
276 }