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.planning;
35
36 import java.sql.Date;
37 import java.sql.Statement;
38 import java.time.LocalDate;
39 import java.util.ArrayList;
40 import java.util.List;
41
42 import fr.paris.lutece.portal.service.plugin.Plugin;
43 import fr.paris.lutece.util.sql.DAOUtil;
44
45
46
47
48
49
50
51 public final class WeekDefinitionDAO implements IWeekDefinitionDAO
52 {
53
54 private static final String SQL_QUERY_INSERT = "INSERT INTO appointment_week_definition ( date_of_apply, ending_date_of_apply, id_reservation_rule) VALUES ( ?, ?, ?)";
55 private static final String SQL_QUERY_UPDATE = "UPDATE appointment_week_definition SET date_of_apply = ?, ending_date_of_apply = ?, id_reservation_rule = ? WHERE id_week_definition = ?";
56 private static final String SQL_QUERY_DELETE = "DELETE FROM appointment_week_definition WHERE id_week_definition = ?";
57 private static final String SQL_QUERY_DELETE_BY_ID_RESERVATION_RULE = "DELETE FROM appointment_week_definition WHERE id_reservation_rule = ?";
58 private static final String SQL_QUERY_SELECT_COLUMNS = "SELECT id_week_definition, date_of_apply, ending_date_of_apply, id_reservation_rule FROM appointment_week_definition ";
59 private static final String SQL_QUERY_SELECT = SQL_QUERY_SELECT_COLUMNS + " WHERE id_week_definition = ?";
60 private static final String SQL_QUERY_SELECT_BY_ID_FORM = " SELECT appw.id_week_definition, appw.date_of_apply, appw.ending_date_of_apply, appw.id_reservation_rule FROM appointment_week_definition appw INNER JOIN appointment_reservation_rule rule on ( rule.id_reservation_rule = appw.id_reservation_rule ) where rule.id_form = ? ";
61 private static final String SQL_QUERY_SELECT_BY_ID_FORM_AND_DATE_OF_APPLY = SQL_QUERY_SELECT_BY_ID_FORM + " AND appw.date_of_apply = ? ";
62 private static final String SQL_QUERY_SELECT_BY_ID_RESERVATION_RULE_AND_DATE_OF_APPLY = SQL_QUERY_SELECT_COLUMNS
63 + " where id_reservation_rule = ? AND date_of_apply = ? ";
64 private static final String SQL_SELECT_BY_RULE = SQL_QUERY_SELECT_COLUMNS + " WHERE id_reservation_rule = ?";
65
66 @Override
67 public void insert( WeekDefinition weekDefinition, Plugin plugin )
68 {
69 try ( DAOUtil daoUtil = buildDaoUtil( SQL_QUERY_INSERT, weekDefinition, plugin, true ) )
70 {
71 daoUtil.executeUpdate( );
72 if ( daoUtil.nextGeneratedKey( ) )
73 {
74 weekDefinition.setIdWeekDefinition( daoUtil.getGeneratedKeyInt( 1 ) );
75 }
76 }
77 }
78
79 @Override
80 public void update( WeekDefinition weekDefinition, Plugin plugin )
81 {
82 try ( DAOUtil daoUtil = buildDaoUtil( SQL_QUERY_UPDATE, weekDefinition, plugin, false ) )
83 {
84 daoUtil.executeUpdate( );
85 }
86 }
87
88 @Override
89 public void delete( int nIdWeekDefinition, Plugin plugin )
90 {
91 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin ) )
92 {
93 daoUtil.setInt( 1, nIdWeekDefinition );
94 daoUtil.executeUpdate( );
95 }
96 }
97
98 @Override
99 public void deleteByIdReservationRule( int nIdReservationRule, Plugin plugin )
100 {
101 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_BY_ID_RESERVATION_RULE, plugin ) )
102 {
103 daoUtil.setInt( 1, nIdReservationRule );
104 daoUtil.executeUpdate( );
105 }
106 }
107
108 @Override
109 public WeekDefinition select( int nIdWeekDefinition, Plugin plugin )
110 {
111 WeekDefinition weekDefinition = null;
112 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT, plugin ) )
113 {
114 daoUtil.setInt( 1, nIdWeekDefinition );
115 daoUtil.executeQuery( );
116 if ( daoUtil.next( ) )
117 {
118 weekDefinition = buildWeekDefinition( daoUtil );
119 }
120 }
121 return weekDefinition;
122 }
123
124 @Override
125 public List<WeekDefinition> findByIdForm( int nIdForm, Plugin plugin )
126 {
127 List<WeekDefinition> listWeekDefinition = new ArrayList<>( );
128 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_ID_FORM, plugin ) )
129 {
130 daoUtil.setInt( 1, nIdForm );
131 daoUtil.executeQuery( );
132 while ( daoUtil.next( ) )
133 {
134 listWeekDefinition.add( buildWeekDefinition( daoUtil ) );
135 }
136 }
137 return listWeekDefinition;
138 }
139
140 @Override
141 public List<WeekDefinition> findByReservationRule( int nIdReservationRule, Plugin plugin )
142 {
143 List<WeekDefinition> listWeekDefinition = new ArrayList<>( );
144 try ( DAOUtil daoUtil = new DAOUtil( SQL_SELECT_BY_RULE, plugin ) )
145 {
146 daoUtil.setInt( 1, nIdReservationRule );
147 daoUtil.executeQuery( );
148 while ( daoUtil.next( ) )
149 {
150 listWeekDefinition.add( buildWeekDefinition( daoUtil ) );
151 }
152 }
153 return listWeekDefinition;
154 }
155
156 @Override
157 public WeekDefinition findByIdFormAndDateOfApply( int nIdForm, LocalDate dateOfApply, Plugin plugin )
158 {
159 WeekDefinition weekDefinition = null;
160 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_ID_FORM_AND_DATE_OF_APPLY, plugin ) )
161 {
162 daoUtil.setInt( 1, nIdForm );
163 daoUtil.setDate( 2, Date.valueOf( dateOfApply ) );
164 daoUtil.executeQuery( );
165 if ( daoUtil.next( ) )
166 {
167 weekDefinition = buildWeekDefinition( daoUtil );
168 }
169 }
170 return weekDefinition;
171 }
172
173 @Override
174 public WeekDefinition findByIdReservationRuleAndDateOfApply( int nIdReservationRule, LocalDate dateOfApply, Plugin plugin )
175 {
176
177 WeekDefinition weekDefinition = null;
178 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_ID_RESERVATION_RULE_AND_DATE_OF_APPLY, plugin ) )
179 {
180 daoUtil.setInt( 1, nIdReservationRule );
181 daoUtil.setDate( 2, Date.valueOf( dateOfApply ) );
182 daoUtil.executeQuery( );
183 if ( daoUtil.next( ) )
184 {
185 weekDefinition = buildWeekDefinition( daoUtil );
186 }
187 }
188 return weekDefinition;
189
190 }
191
192
193
194
195
196
197
198
199 private WeekDefinition buildWeekDefinition( DAOUtil daoUtil )
200 {
201 int nIndex = 1;
202 WeekDefinition/business/planning/WeekDefinition.html#WeekDefinition">WeekDefinition weekDefinition = new WeekDefinition( );
203 weekDefinition.setIdWeekDefinition( daoUtil.getInt( nIndex++ ) );
204 weekDefinition.setSqlDateOfApply( daoUtil.getDate( nIndex++ ) );
205 weekDefinition.setSqlEndingDateOfApply( daoUtil.getDate( nIndex++ ) );
206 weekDefinition.setIdReservationRule( daoUtil.getInt( nIndex ) );
207 return weekDefinition;
208 }
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224 private DAOUtil buildDaoUtil( String query, WeekDefinition weekDefinition, Plugin plugin, boolean isInsert )
225 {
226 int nIndex = 1;
227 DAOUtil daoUtil = null;
228 if ( isInsert )
229 {
230 daoUtil = new DAOUtil( query, Statement.RETURN_GENERATED_KEYS, plugin );
231 }
232 else
233 {
234 daoUtil = new DAOUtil( query, plugin );
235 }
236 daoUtil.setDate( nIndex++, weekDefinition.getSqlDateOfApply( ) );
237 daoUtil.setDate( nIndex++, weekDefinition.getSqlEndingDateOfApply( ) );
238 daoUtil.setInt( nIndex++, weekDefinition.getIdReservationRule( ) );
239 if ( !isInsert )
240 {
241 daoUtil.setInt( nIndex, weekDefinition.getIdWeekDefinition( ) );
242 }
243 return daoUtil;
244 }
245 }