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.modules.resource.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.sql.Timestamp;
41 import java.util.ArrayList;
42 import java.util.List;
43
44
45
46
47 public class AppointmentResourceDAO implements IAppointmentResourceDAO
48 {
49
50 private static final String SQL_QUERY_SELECT_ALL = " SELECT id_appointment, id_app_form_res_type, id_resource FROM appointment_resource_app_res ";
51 private static final String SQL_QUERY_SELECT_BY_ID_APPOINTMENT = SQL_QUERY_SELECT_ALL + " WHERE id_appointment = ? ";
52 private static final String SQL_QUERY_SELECT_BY_PRIMARY_KEY = SQL_QUERY_SELECT_ALL + " WHERE id_appointment = ? AND id_app_form_res_type = ? ";
53 private static final String SQL_QUERY_IS_RESOURCE_AVAILABLE = " SELECT ar.id_resource FROM appointment_resource_app_res ar "
54 + " INNER JOIN appointment_resource_form_rt frt ON ar.id_app_form_res_type = frt.id "
55 + " INNER JOIN appointment_appointment a ON ar.id_appointment = a.id_appointment" + " INNER JOIN appointment_slot s ON s.id_slot = a.id_slot"
56 + " WHERE ar.id_resource = ?" + " AND frt.resource_type_name = ?" + " AND a.is_cancelled != ?"
57 + " AND ( ( ( s.starting_date_time ) < ? && ( s.ending_date_time ) > ? )"
58 + " || ( ( s.starting_date_time ) < ? && ( s.ending_date_time ) > ? )"
59 + " || ( ( s.starting_date_time ) > ? && ( s.ending_date_time ) < ? )"
60 + " || ( ( s.starting_date_time ) = ? && ( s.ending_date_time ) = ? )" + " )";
61 private static final String SQL_QUERY_FIND_ID_APPOINTMENT_BY_RESAOURCE_AND_DATE = "SELECT ar.id_appointment FROM appointment_resource_app_res ar INNER JOIN appointment_appointment a ON ar.id_appointment = a.id_appointment INNER JOIN appointment_resource_form_rt frt ON ar.id_app_form_res_type = frt.id "
62 + " INNER JOIN appointment_slot s ON s.id_slot = a.id_slot WHERE ar.id_resource = ? AND frt.resource_type_name = ? AND s.starting_date_time > ? AND s.ending_date_time < ? AND a.is_cancelled != ? ";
63
64
65 private static final String SQL_QUERY_INSERT = " INSERT INTO appointment_resource_app_res (id_appointment,id_app_form_res_type,id_resource) VALUES (?,?,?) ";
66 private static final String SQL_QUERY_UPDATE = " UPDATE appointment_resource_app_res SET id_resource=? WHERE id_appointment = ? AND id_app_form_res_type = ?";
67
68
69 private static final String SQL_QUERY_DELETE_BY_PRIMARY_KEY = " DELETE FROM appointment_resource_app_res WHERE id_appointment = ? AND id_app_form_res_type = ? ";
70 private static final String SQL_QUERY_DELETE_BY_ID_APPOINTMENT = " DELETE FROM appointment_resource_app_res WHERE id_appointment = ? ";
71 private static final String SQL_QUERY_DELETE_BY_ID_APPOINTMENT_FORM_RESOURCE_TYPE = " DELETE FROM appointment_resource_app_res WHERE id_app_form_res_type = ? ";
72
73
74
75
76 @Override
77 public void insert( AppointmentResource resource, Plugin plugin )
78 {
79 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, plugin );
80 int nIndex = 1;
81 daoUtil.setInt( nIndex++, resource.getIdAppointment( ) );
82 daoUtil.setInt( nIndex++, resource.getIdAppointmentFormResourceType( ) );
83 daoUtil.setString( nIndex, resource.getIdResource( ) );
84 daoUtil.executeUpdate( );
85 daoUtil.free( );
86 }
87
88
89
90
91 @Override
92 public AppointmentResource findByPrimaryKey( int nIdAppointment, int nIdAppointmentFormResourceType, Plugin plugin )
93 {
94 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_PRIMARY_KEY, plugin );
95 daoUtil.setInt( 1, nIdAppointment );
96 daoUtil.setInt( 2, nIdAppointmentFormResourceType );
97 daoUtil.executeQuery( );
98
99 AppointmentResource resource = null;
100
101 if ( daoUtil.next( ) )
102 {
103 resource = getFromDAOUtil( daoUtil );
104 }
105
106 daoUtil.free( );
107
108 return resource;
109 }
110
111
112
113
114 @Override
115 public List<AppointmentResource> findByIdAppointment( int nIdAppointment, Plugin plugin )
116 {
117 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_ID_APPOINTMENT, plugin );
118 daoUtil.setInt( 1, nIdAppointment );
119 daoUtil.executeQuery( );
120
121 List<AppointmentResource> listAppointmentResource = new ArrayList<AppointmentResource>( );
122
123 while ( daoUtil.next( ) )
124 {
125 listAppointmentResource.add( getFromDAOUtil( daoUtil ) );
126 }
127
128 daoUtil.free( );
129
130 return listAppointmentResource;
131 }
132
133
134
135
136 @Override
137 public void update( AppointmentResource resource, Plugin plugin )
138 {
139 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin );
140 int nIndex = 1;
141 daoUtil.setString( nIndex++, resource.getIdResource( ) );
142 daoUtil.setInt( nIndex++, resource.getIdAppointment( ) );
143 daoUtil.setInt( nIndex, resource.getIdAppointmentFormResourceType( ) );
144 daoUtil.executeUpdate( );
145 daoUtil.free( );
146 }
147
148
149
150
151 @Override
152 public void delete( int nIdAppointment, int nIdAppointmentFormResourceType, Plugin plugin )
153 {
154 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_BY_PRIMARY_KEY, plugin );
155 daoUtil.setInt( 1, nIdAppointment );
156 daoUtil.setInt( 2, nIdAppointmentFormResourceType );
157 daoUtil.executeUpdate( );
158 daoUtil.free( );
159 }
160
161
162
163
164 @Override
165 public void deleteByIdAppointment( int nIdAppointment, Plugin plugin )
166 {
167 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_BY_ID_APPOINTMENT, plugin );
168 daoUtil.setInt( 1, nIdAppointment );
169 daoUtil.executeUpdate( );
170 daoUtil.free( );
171 }
172
173
174
175
176 @Override
177 public void deleteByIdAppointmentFormResourceType( int nIdAppointmentFormResourceType, Plugin plugin )
178 {
179 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_BY_ID_APPOINTMENT_FORM_RESOURCE_TYPE, plugin );
180 daoUtil.setInt( 1, nIdAppointmentFormResourceType );
181 daoUtil.executeUpdate( );
182 daoUtil.free( );
183 }
184
185
186
187
188 @Override
189 public boolean isResourceAvailable( String strIdResource, String strResourceTypeName, Timestamp nStartingTime, Timestamp nEndingTime, Plugin plugin )
190 {
191
192 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_IS_RESOURCE_AVAILABLE, plugin );
193 int nIndex = 1;
194 daoUtil.setString( nIndex++, strIdResource );
195 daoUtil.setString( nIndex++, strResourceTypeName );
196
197 daoUtil.setBoolean( nIndex++, true );
198 daoUtil.setTimestamp( nIndex++, nStartingTime );
199 daoUtil.setTimestamp( nIndex++, nStartingTime );
200 daoUtil.setTimestamp( nIndex++, nEndingTime );
201 daoUtil.setTimestamp( nIndex++, nEndingTime );
202 daoUtil.setTimestamp( nIndex++, nStartingTime );
203 daoUtil.setTimestamp( nIndex++, nEndingTime );
204 daoUtil.setTimestamp( nIndex++, nStartingTime );
205 daoUtil.setTimestamp( nIndex, nEndingTime );
206
207 daoUtil.executeQuery( );
208
209 boolean bHasResult = true;
210
211 if ( daoUtil.next( ) )
212 {
213 bHasResult = false;
214 }
215
216 daoUtil.free( );
217
218 return bHasResult;
219 }
220
221
222
223
224 @Override
225 public List<Integer> findIdAppointmentsByResourceAndDate( String strIdResource, String strResourceType, Date dateMin, Date dateMax, Plugin plugin )
226 {
227
228 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_ID_APPOINTMENT_BY_RESAOURCE_AND_DATE, plugin );
229 daoUtil.setString( 1, strIdResource );
230 daoUtil.setString( 2, strResourceType );
231 daoUtil.setTimestamp( 3, new Timestamp( dateMin.getTime( ) ) );
232 daoUtil.setTimestamp( 4, new Timestamp( dateMax.getTime( ) ) );
233 daoUtil.setBoolean( 5, true );
234
235 List<Integer> listIdAppointments = new ArrayList<Integer>( );
236 daoUtil.executeQuery( );
237
238 while ( daoUtil.next( ) )
239 {
240 listIdAppointments.add( daoUtil.getInt( 1 ) );
241 }
242
243 daoUtil.free( );
244
245 return listIdAppointments;
246 }
247
248
249
250
251
252
253
254
255 private AppointmentResource getFromDAOUtil( DAOUtil daoUtil )
256 {
257 AppointmentResourcement/modules/resource/business/AppointmentResource.html#AppointmentResource">AppointmentResource resource = new AppointmentResource( );
258 int nIndex = 1;
259 resource.setIdAppointment( daoUtil.getInt( nIndex++ ) );
260 resource.setIdAppointmentFormResourceType( daoUtil.getInt( nIndex++ ) );
261 resource.setIdResource( daoUtil.getString( nIndex ) );
262
263 return resource;
264 }
265 }