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 ClosingDayDAO implements IClosingDayDAO
52 {
53
54 private static final String SQL_QUERY_INSERT = "INSERT INTO appointment_closing_day ( date_of_closing_day, id_form) VALUES (?, ?)";
55 private static final String SQL_QUERY_UPDATE = "UPDATE appointment_closing_day SET date_of_closing_day = ?, id_form = ? WHERE id_closing_day = ?";
56 private static final String SQL_QUERY_DELETE = "DELETE FROM appointment_closing_day WHERE id_closing_day = ?";
57 private static final String SQL_QUERY_SELECT_COLUMNS = "SELECT id_closing_day, date_of_closing_day, id_form FROM appointment_closing_day";
58 private static final String SQL_QUERY_SELECT = SQL_QUERY_SELECT_COLUMNS + " WHERE id_closing_day = ?";
59 private static final String SQL_QUERY_SELECT_BY_ID_FORM = SQL_QUERY_SELECT_COLUMNS + " WHERE id_form = ?";
60 private static final String SQL_QUERY_SELECT_BY_ID_FORM_AND_DATE_OF_CLOSING_DAY = SQL_QUERY_SELECT_BY_ID_FORM + " AND date_of_closing_day = ?";
61 private static final String SQL_QUERY_SELECT_BY_ID_FORM_AND_DATE_RANGE = SQL_QUERY_SELECT_BY_ID_FORM
62 + " AND date_of_closing_day >= ? AND date_of_closing_day <= ?";
63 private static final String SQL_QUERY_DELETE_BY_ID_FORM_AND_DATE_OF_CLOSING_DAY = "DELETE FROM appointment_closing_day WHERE id_form = ? AND date_of_closing_day = ? ";
64 private static final String SQL_QUERY_DELETE_BY_ID_FORM = "DELETE FROM appointment_closing_day WHERE id_form = ? ";
65
66 @Override
67 public void insert( ClosingDay closingDay, Plugin plugin )
68 {
69 try ( DAOUtil daoUtil = buildDaoUtil( SQL_QUERY_INSERT, closingDay, plugin, true ) )
70 {
71 daoUtil.executeUpdate( );
72 if ( daoUtil.nextGeneratedKey( ) )
73 {
74 closingDay.setIdClosingDay( daoUtil.getGeneratedKeyInt( 1 ) );
75 }
76 }
77 }
78
79 @Override
80 public void update( ClosingDay closingDay, Plugin plugin )
81 {
82 try ( DAOUtil daoUtil = buildDaoUtil( SQL_QUERY_UPDATE, closingDay, plugin, false ) )
83 {
84 daoUtil.executeUpdate( );
85 }
86 }
87
88 @Override
89 public void delete( int nIdClosingDay, Plugin plugin )
90 {
91 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin ) )
92 {
93 daoUtil.setInt( 1, nIdClosingDay );
94 daoUtil.executeUpdate( );
95 }
96 }
97
98 @Override
99 public ClosingDay select( int nIdClosingDay, Plugin plugin )
100 {
101 ClosingDay closingDay = null;
102 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT, plugin ) )
103 {
104 daoUtil.setInt( 1, nIdClosingDay );
105 daoUtil.executeQuery( );
106 if ( daoUtil.next( ) )
107 {
108 closingDay = buildClosingDay( daoUtil );
109 }
110 }
111 return closingDay;
112 }
113
114 @Override
115 public ClosingDay findByIdFormAndDateOfClosingDay( int nIdForm, LocalDate dateOfCLosingDay, Plugin plugin )
116 {
117 ClosingDay closingDay = null;
118 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_ID_FORM_AND_DATE_OF_CLOSING_DAY, plugin ) )
119 {
120 daoUtil.setInt( 1, nIdForm );
121 daoUtil.setDate( 2, Date.valueOf( dateOfCLosingDay ) );
122 daoUtil.executeQuery( );
123 if ( daoUtil.next( ) )
124 {
125 closingDay = buildClosingDay( daoUtil );
126 }
127 }
128 return closingDay;
129 }
130
131 @Override
132 public void deleteByIdFormAndDateOfClosingDay( int nIdForm, LocalDate dateOfCLosingDay, Plugin plugin )
133 {
134 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_BY_ID_FORM_AND_DATE_OF_CLOSING_DAY, plugin ) )
135 {
136 daoUtil.setInt( 1, nIdForm );
137 daoUtil.setDate( 2, Date.valueOf( dateOfCLosingDay ) );
138 daoUtil.executeUpdate( );
139 }
140 }
141
142 @Override
143 public void deleteByIdForm( int nIdForm, Plugin plugin )
144 {
145 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_BY_ID_FORM, plugin ) )
146 {
147 daoUtil.setInt( 1, nIdForm );
148 daoUtil.executeUpdate( );
149 }
150 }
151
152 @Override
153 public List<ClosingDay> findByIdForm( int nIdForm, Plugin plugin )
154 {
155 List<ClosingDay> listClosingDay = new ArrayList<>( );
156 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_ID_FORM, plugin ) )
157 {
158 daoUtil.setInt( 1, nIdForm );
159 daoUtil.executeQuery( );
160 while ( daoUtil.next( ) )
161 {
162 listClosingDay.add( buildClosingDay( daoUtil ) );
163 }
164 }
165 return listClosingDay;
166 }
167
168 @Override
169 public List<ClosingDay> findByIdFormAndDateRange( int nIdForm, LocalDate startingDate, LocalDate endingDate, Plugin plugin )
170 {
171 List<ClosingDay> listClosingDay = new ArrayList<>( );
172 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_ID_FORM_AND_DATE_RANGE, plugin ) )
173 {
174 daoUtil.setInt( 1, nIdForm );
175 daoUtil.setDate( 2, Date.valueOf( startingDate ) );
176 daoUtil.setDate( 3, Date.valueOf( endingDate ) );
177 daoUtil.executeQuery( );
178 while ( daoUtil.next( ) )
179 {
180 listClosingDay.add( buildClosingDay( daoUtil ) );
181 }
182 }
183 return listClosingDay;
184 }
185
186
187
188
189
190
191
192
193 private ClosingDay buildClosingDay( DAOUtil daoUtil )
194 {
195 int nIndex = 1;
196 ClosingDayointment/business/planning/ClosingDay.html#ClosingDay">ClosingDay closingDay = new ClosingDay( );
197 closingDay.setIdClosingDay( daoUtil.getInt( nIndex++ ) );
198 closingDay.setSqlDateOfClosingDay( daoUtil.getDate( nIndex++ ) );
199 closingDay.setIdForm( daoUtil.getInt( nIndex ) );
200 return closingDay;
201 }
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217 private DAOUtil buildDaoUtil( String query, ClosingDay closingDay, Plugin plugin, boolean isInsert )
218 {
219 int nIndex = 1;
220 DAOUtil daoUtil = null;
221 if ( isInsert )
222 {
223 daoUtil = new DAOUtil( query, Statement.RETURN_GENERATED_KEYS, plugin );
224 }
225 else
226 {
227 daoUtil = new DAOUtil( query, plugin );
228 }
229 daoUtil.setDate( nIndex++, closingDay.getSqlDateOfClosingDay( ) );
230 daoUtil.setInt( nIndex++, closingDay.getIdForm( ) );
231 if ( !isInsert )
232 {
233 daoUtil.setInt( nIndex, closingDay.getIdClosingDay( ) );
234 }
235 return daoUtil;
236 }
237 }