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.crm.business.notification;
35
36 import fr.paris.lutece.portal.service.plugin.Plugin;
37 import fr.paris.lutece.util.sql.DAOUtil;
38
39 import java.util.ArrayList;
40 import java.util.List;
41
42
43
44
45
46
47 public class NotificationDAO implements INotificationDAO
48 {
49
50 private static final String SQL_QUERY_NEW_PK = " SELECT max( id_notification ) FROM crm_notification ";
51 private static final String SQL_QUERY_INSERT = " INSERT INTO crm_notification (id_notification, id_demand, is_read, object, message, date_creation, sender) VALUES (?,?,?,?,?,?,?) ";
52 private static final String SQL_QUERY_SELECT = " SELECT id_notification, id_demand, is_read, object, message, date_creation, sender FROM crm_notification WHERE id_notification = ? ";
53 private static final String SQL_QUERY_UPDATE = " UPDATE crm_notification SET is_read = ?, object = ?, message = ?, date_creation = ?, sender = ? WHERE id_notification = ? ";
54 private static final String SQL_QUERY_DELETE = " DELETE FROM crm_notification WHERE id_notification = ? ";
55 private static final String SQL_QUERY_DELETE_BY_ID_DEMAND = " DELETE FROM crm_notification WHERE id_demand = ? ";
56 private static final String SQL_QUERY_SELECT_ALL = " SELECT id_notification, id_demand, is_read, object, message, date_creation, sender FROM crm_notification ";
57
58
59 private static final String SQL_ORDER_BY = " ORDER BY ";
60 private static final String SQL_DESC = " DESC ";
61 private static final String SQL_OR = " OR ";
62 private static final String SQL_AND = " AND ";
63 private static final String SQL_WHERE = " WHERE ";
64 private static final String SQL_DATE_CREATION = " date_creation ";
65 private static final String SQL_FILTER_ID_DEMAND = " id_demand = ? ";
66 private static final String SQL_FILTER_IS_READ = " is_read = ? ";
67
68
69
70
71 public int newPrimaryKey( Plugin plugin )
72 {
73 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_PK, plugin );
74 daoUtil.executeQuery( );
75
76 int nKey = 1;
77
78 if ( daoUtil.next( ) )
79 {
80 nKey = daoUtil.getInt( 1 ) + 1;
81 }
82
83 daoUtil.free( );
84
85 return nKey;
86 }
87
88
89
90
91 public synchronized int insert( Notification notification, Plugin plugin )
92 {
93 int nKey = -1;
94
95 if ( notification != null )
96 {
97 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, plugin );
98
99 int nIndex = 1;
100 notification.setIdNotification( newPrimaryKey( plugin ) );
101
102 daoUtil.setInt( nIndex++, notification.getIdNotification( ) );
103 daoUtil.setInt( nIndex++, notification.getIdDemand( ) );
104 daoUtil.setBoolean( nIndex++, notification.isRead( ) );
105 daoUtil.setString( nIndex++, notification.getObject( ) );
106 daoUtil.setString( nIndex++, notification.getMessage( ) );
107 daoUtil.setTimestamp( nIndex++, notification.getDateCreation( ) );
108 daoUtil.setString( nIndex++, notification.getSender( ) );
109
110 daoUtil.executeUpdate( );
111 daoUtil.free( );
112
113 nKey = notification.getIdNotification( );
114 }
115
116 return nKey;
117 }
118
119
120
121
122 public Notification load( int nIdNotification, Plugin plugin )
123 {
124 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT, plugin );
125 daoUtil.setInt( 1, nIdNotification );
126 daoUtil.executeQuery( );
127
128 Notification notification = null;
129
130 if ( daoUtil.next( ) )
131 {
132 int nIndex = 1;
133 notification = new Notification( );
134 notification.setIdNotification( daoUtil.getInt( nIndex++ ) );
135 notification.setIdDemand( daoUtil.getInt( nIndex++ ) );
136 notification.setIsRead( daoUtil.getBoolean( nIndex++ ) );
137 notification.setObject( daoUtil.getString( nIndex++ ) );
138 notification.setMessage( daoUtil.getString( nIndex++ ) );
139 notification.setDateCreation( daoUtil.getTimestamp( nIndex++ ) );
140 notification.setSender( daoUtil.getString( nIndex++ ) );
141 }
142
143 daoUtil.free( );
144
145 return notification;
146 }
147
148
149
150
151 public void store( Notification notification, Plugin plugin )
152 {
153 if ( notification != null )
154 {
155 int nIndex = 1;
156
157 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin );
158
159 daoUtil.setBoolean( nIndex++, notification.isRead( ) );
160 daoUtil.setString( nIndex++, notification.getObject( ) );
161 daoUtil.setString( nIndex++, notification.getMessage( ) );
162 daoUtil.setTimestamp( nIndex++, notification.getDateCreation( ) );
163 daoUtil.setString( nIndex++, notification.getSender( ) );
164
165 daoUtil.setInt( nIndex++, notification.getIdNotification( ) );
166 daoUtil.executeUpdate( );
167 daoUtil.free( );
168 }
169 }
170
171
172
173
174 public void delete( int nIdNotification, Plugin plugin )
175 {
176 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin );
177 daoUtil.setInt( 1, nIdNotification );
178 daoUtil.executeUpdate( );
179 daoUtil.free( );
180 }
181
182
183
184
185 public void deleteByIdDemand( int nIdDemand, Plugin plugin )
186 {
187 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_BY_ID_DEMAND, plugin );
188 daoUtil.setInt( 1, nIdDemand );
189 daoUtil.executeUpdate( );
190 daoUtil.free( );
191 }
192
193
194
195
196 public List<Notification> selectAll( Plugin plugin )
197 {
198 StringBuilder sbSQL = new StringBuilder( SQL_QUERY_SELECT_ALL );
199 sbSQL.append( SQL_ORDER_BY );
200 sbSQL.append( SQL_DATE_CREATION );
201 sbSQL.append( SQL_DESC );
202
203 List<Notification> listNotifications = new ArrayList<Notification>( );
204 DAOUtil daoUtil = new DAOUtil( sbSQL.toString( ), plugin );
205 daoUtil.executeQuery( );
206
207 while ( daoUtil.next( ) )
208 {
209 int nIndex = 1;
210 Notificationiness/notification/Notification.html#Notification">Notification notification = new Notification( );
211 notification.setIdNotification( daoUtil.getInt( nIndex++ ) );
212 notification.setIdDemand( daoUtil.getInt( nIndex++ ) );
213 notification.setIsRead( daoUtil.getBoolean( nIndex++ ) );
214 notification.setObject( daoUtil.getString( nIndex++ ) );
215 notification.setMessage( daoUtil.getString( nIndex++ ) );
216 notification.setDateCreation( daoUtil.getTimestamp( nIndex++ ) );
217 notification.setSender( daoUtil.getString( nIndex++ ) );
218 listNotifications.add( notification );
219 }
220
221 daoUtil.free( );
222
223 return listNotifications;
224 }
225
226
227
228
229 public List<Notification> selectNotificationsByFilter( NotificationFilter nFilter, Plugin plugin )
230 {
231 List<Notification> listNotifications = new ArrayList<Notification>( );
232 StringBuilder sbSQL = new StringBuilder( buildSQLQuery( nFilter ) );
233 sbSQL.append( SQL_ORDER_BY );
234 sbSQL.append( SQL_DATE_CREATION );
235 sbSQL.append( SQL_DESC );
236
237 DAOUtil daoUtil = new DAOUtil( sbSQL.toString( ), plugin );
238 setFilterValues( nFilter, daoUtil );
239 daoUtil.executeQuery( );
240
241 while ( daoUtil.next( ) )
242 {
243 int nIndex = 1;
244 Notificationiness/notification/Notification.html#Notification">Notification notification = new Notification( );
245 notification.setIdNotification( daoUtil.getInt( nIndex++ ) );
246 notification.setIdDemand( daoUtil.getInt( nIndex++ ) );
247 notification.setIsRead( daoUtil.getBoolean( nIndex++ ) );
248 notification.setObject( daoUtil.getString( nIndex++ ) );
249 notification.setMessage( daoUtil.getString( nIndex++ ) );
250 notification.setDateCreation( daoUtil.getTimestamp( nIndex++ ) );
251 notification.setSender( daoUtil.getString( nIndex++ ) );
252 listNotifications.add( notification );
253 }
254
255 daoUtil.free( );
256
257 return listNotifications;
258 }
259
260
261
262
263
264
265
266
267 private String buildSQLQuery( NotificationFilter nFilter )
268 {
269 StringBuilder sbSQL = new StringBuilder( SQL_QUERY_SELECT_ALL );
270 int nIndex = 1;
271
272 if ( nFilter.containsIdDemand( ) )
273 {
274 nIndex = addSQLWhereOr( nFilter.getIsWideSearch( ), sbSQL, nIndex );
275 sbSQL.append( SQL_FILTER_ID_DEMAND );
276 }
277
278 if ( nFilter.containsIsRead( ) )
279 {
280 addSQLWhereOr( nFilter.getIsWideSearch( ), sbSQL, nIndex );
281 sbSQL.append( SQL_FILTER_IS_READ );
282 }
283
284 return sbSQL.toString( );
285 }
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302 private int addSQLWhereOr( boolean bIsWideSearch, StringBuilder sbSQL, int nIndex )
303 {
304 if ( nIndex == 1 )
305 {
306 sbSQL.append( SQL_WHERE );
307 }
308 else
309 {
310 sbSQL.append( bIsWideSearch ? SQL_OR : SQL_AND );
311 }
312
313 return nIndex + 1;
314 }
315
316
317
318
319
320
321
322
323
324 private void setFilterValues( NotificationFilter nFilter, DAOUtil daoUtil )
325 {
326 int nIndex = 1;
327
328 if ( nFilter.containsIdDemand( ) )
329 {
330 daoUtil.setInt( nIndex++, nFilter.getIdDemand( ) );
331 }
332
333 if ( nFilter.containsIsRead( ) )
334 {
335 daoUtil.setBoolean( nIndex++, nFilter.isRead( ) );
336 }
337 }
338 }