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.notificationstore.business;
35
36 import fr.paris.lutece.plugins.grubusiness.business.notification.EnumNotificationType;
37 import fr.paris.lutece.portal.service.plugin.Plugin;
38 import fr.paris.lutece.util.sql.DAOUtil;
39
40 import java.util.ArrayList;
41 import java.util.List;
42 import java.util.stream.Collectors;
43
44 import org.apache.commons.collections.CollectionUtils;
45
46 import java.sql.Statement;
47
48
49
50
51 public final class NotificationContentDAO implements INotificationContentDAO
52 {
53
54 private static final String SQL_QUERY_SELECT = "SELECT id_notification_content, notification_id, notification_type, id_temporary_status, status_id, file_key, file_store FROM notificationstore_notification_content WHERE id_notification_content = ?";
55 private static final String SQL_QUERY_INSERT = "INSERT INTO notificationstore_notification_content ( notification_id, notification_type, id_temporary_status, status_id, file_key, file_store ) VALUES ( ?, ?, ?, ?, ?, ? ) ";
56 private static final String SQL_QUERY_DELETE = "DELETE FROM notificationstore_notification_content WHERE id_notification_content = ? ";
57 private static final String SQL_QUERY_UPDATE = "UPDATE notificationstore_notification_content SET notification_type = ?, id_temporary_status = ?, status_id = ?, file_key = ?, file_store = ? WHERE id_notification_content = ?";
58 private static final String SQL_QUERY_SELECTALL = "SELECT id_notification_content, notification_id, notification_type, id_temporary_status, status_id, file_key, file_store FROM notificationstore_notification_content";
59 private static final String SQL_QUERY_SELECT_BY_ID_NOTIF = "SELECT id_notification_content, notification_id, notification_type, id_temporary_status, status_id, file_key, file_store FROM notificationstore_notification_content WHERE notification_id = ?";
60 private static final String SQL_PARAM_QUERY_TYPE_NOTIF = " AND notification_type IN (";
61 private static final String SQL_QUERY_UPDATE_STATUS = "UPDATE notificationstore_notification_content SET id_temporary_status = -1, status_id = ? WHERE id_temporary_status = ?";
62
63
64
65
66 @Override
67 public void insert( NotificationContent notificationContent, Plugin plugin )
68 {
69 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, Statement.RETURN_GENERATED_KEYS, plugin ) )
70 {
71 int nIndex = 0;
72 daoUtil.setInt( ++nIndex, notificationContent.getIdNotification( ) );
73 daoUtil.setString( ++nIndex, notificationContent.getNotificationType( ) );
74 daoUtil.setInt( ++nIndex, notificationContent.getIdTemporaryStatus( ) != null ? notificationContent.getIdTemporaryStatus( ) : -1 );
75 daoUtil.setInt( ++nIndex, notificationContent.getStatusId( ) != null ? notificationContent.getStatusId( ) : -1 );
76 daoUtil.setString( ++nIndex, notificationContent.getFileKey( ) );
77 daoUtil.setString( ++nIndex, notificationContent.getFileStore( ) );
78
79 daoUtil.executeUpdate( );
80 if ( daoUtil.nextGeneratedKey( ) )
81 {
82 notificationContent.setId( daoUtil.getGeneratedKeyInt( 1 ) );
83 }
84 }
85 }
86
87
88
89
90 @Override
91 public NotificationContent load( int nId, Plugin plugin )
92 {
93 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT, plugin ) )
94 {
95 daoUtil.setInt( 1, nId );
96 daoUtil.executeQuery( );
97
98 NotificationContent notificationContent = null;
99
100 if ( daoUtil.next( ) )
101 {
102 notificationContent = loadNotificationContent( daoUtil );
103 }
104
105 return notificationContent;
106 }
107 }
108
109
110
111
112 @Override
113 public void delete( int nNotificationContentId, Plugin plugin )
114 {
115 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin ) )
116 {
117 daoUtil.setInt( 1, nNotificationContentId );
118 daoUtil.executeUpdate( );
119 }
120 }
121
122
123
124
125 @Override
126 public void store( NotificationContent notificationContent, Plugin plugin )
127 {
128 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin ) )
129 {
130 int nIndex = 0;
131 daoUtil.setString( ++nIndex, notificationContent.getNotificationType( ) );
132 daoUtil.setInt( ++nIndex, notificationContent.getIdTemporaryStatus( ) );
133 daoUtil.setInt( ++nIndex, notificationContent.getStatusId( ) );
134 daoUtil.setString( ++nIndex, notificationContent.getFileKey( ) );
135 daoUtil.setString( ++nIndex, notificationContent.getFileStore( ) );
136 daoUtil.setInt( ++nIndex, notificationContent.getId( ) );
137
138 daoUtil.executeUpdate( );
139 }
140 }
141
142
143
144
145 @Override
146 public List<NotificationContent> selectNotificationContentsList( Plugin plugin )
147 {
148 List<NotificationContent> listNotificationContents = new ArrayList<>( );
149 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL, plugin ) )
150 {
151 daoUtil.executeQuery( );
152
153 while ( daoUtil.next( ) )
154 {
155 listNotificationContents.add( loadNotificationContent( daoUtil ) );
156 }
157
158 return listNotificationContents;
159 }
160 }
161
162 @Override
163 public List<NotificationContent> selectNotificationContentsByIdNotification( int nIdNotification, Plugin plugin )
164 {
165 List<NotificationContent> listNotificationContents = new ArrayList<>( );
166
167 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_ID_NOTIF, plugin ) )
168 {
169 daoUtil.setInt( 1, nIdNotification );
170
171 daoUtil.executeQuery( );
172
173 while ( daoUtil.next( ) )
174 {
175 listNotificationContents.add( loadNotificationContent( daoUtil ) );
176 }
177 }
178
179 return listNotificationContents;
180 }
181
182 @Override
183 public List<NotificationContent> selectNotificationContentsByIdAndTypeNotification( int nIdNotification, List<EnumNotificationType> listNotificationType,
184 Plugin plugin )
185 {
186 String strQuery = SQL_QUERY_SELECT_BY_ID_NOTIF;
187 if ( CollectionUtils.isNotEmpty( listNotificationType ) )
188 {
189 strQuery += SQL_PARAM_QUERY_TYPE_NOTIF + listNotificationType.stream( ).map( i -> "?" ).collect( Collectors.joining( "," ) ) + " ) ";
190 }
191
192 List<NotificationContent> listNotificationContents = new ArrayList<>( );
193
194 try ( DAOUtil daoUtil = new DAOUtil( strQuery, plugin ) )
195 {
196 int nIndexIn = 1;
197
198 daoUtil.setInt( nIndexIn++, nIdNotification );
199 if ( CollectionUtils.isNotEmpty( listNotificationType ) )
200 {
201 for ( EnumNotificationType notificationType : listNotificationType )
202 {
203 daoUtil.setString( nIndexIn, notificationType.name( ) );
204 nIndexIn++;
205 }
206 }
207
208 daoUtil.executeQuery( );
209
210 while ( daoUtil.next( ) )
211 {
212 listNotificationContents.add( loadNotificationContent( daoUtil ) );
213 }
214 }
215
216 return listNotificationContents;
217 }
218
219
220
221
222
223
224
225 private NotificationContent loadNotificationContent( DAOUtil daoUtil )
226 {
227 NotificationContentss/NotificationContent.html#NotificationContent">NotificationContent notificationContent = new NotificationContent( );
228 notificationContent.setId( daoUtil.getInt( "id_notification_content" ) );
229 notificationContent.setIdNotification( daoUtil.getInt( "notification_id" ) );
230 notificationContent.setNotificationType( daoUtil.getString( "notification_type" ) );
231 notificationContent.setIdTemporaryStatus( daoUtil.getInt( "id_temporary_status" ) );
232 notificationContent.setStatusId( daoUtil.getInt( "status_id" ) );
233 notificationContent.setFileKey( daoUtil.getString( "file_key" ) );
234 notificationContent.setFileStore( daoUtil.getString( "file_store" ) );
235
236 return notificationContent;
237 }
238
239 @Override
240 public void updateStatusId( int nNewStatusId, int nTemporaryStatusId, Plugin plugin )
241 {
242 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE_STATUS, plugin ) )
243 {
244 daoUtil.setInt( 1, nNewStatusId );
245 daoUtil.setInt( 2, nTemporaryStatusId );
246
247 daoUtil.executeUpdate( );
248 }
249 }
250
251 }