1 package fr.paris.lutece.plugins.module.announce.subscribe.business;
2
3 import fr.paris.lutece.plugins.subscribe.business.SubscriptionFilter;
4 import fr.paris.lutece.portal.service.plugin.Plugin;
5 import fr.paris.lutece.util.sql.DAOUtil;
6 import org.apache.commons.lang.StringUtils;
7
8 import java.util.ArrayList;
9 import java.util.Collection;
10 import java.util.List;
11
12 public class AnnounceSubscribtionDAO implements IAnnounceSubscribtionDAO {
13
14
15 private static final String SQL_QUERY_NEW_PK = "SELECT max( id_subscription ) FROM subscribe_subscription";
16 private static final String SQL_QUERY_SELECT = " SELECT id_subscription, id_user, subscription_provider, subscription_key, id_subscribed_resource, email_subscribes FROM subscribe_subscription ";
17 private static final String SQL_QUERY_SELECT_DISTINCT = " SELECT DISTINCT id_user FROM subscribe_subscription ";
18 private static final String SQL_QUERY_SELECT_FROM_SUBSCRIPTION_ID = SQL_QUERY_SELECT
19 + " WHERE id_subscription = ? ";
20 private static final String SQL_QUERY_INSERT = "INSERT INTO subscribe_subscription ( id_subscription, id_user, subscription_provider, subscription_key, id_subscribed_resource, email_subscribes ) VALUES ( ?, ?, ?, ?, ?, ? ) ";
21 private static final String SQL_QUERY_DELETE = "DELETE FROM subscribe_subscription WHERE id_subscription = ? ";
22 private static final String SQL_QUERY_UPDATE = "UPDATE subscribe_subscription SET id_user = ?, subscription_provider = ?, subscription_key = ?, id_subscribed_resource = ?, email_subscribes = ? WHERE id_subscription = ?";
23 private static final String SQL_QUERY_SELECTALL = "SELECT id_subscription, id_user, subscription_provider, subscription_key, id_subscribed_resource, email_subscribes FROM subscribe_subscription";
24
25 private static final String SQL_FILTER_ID_USER = " id_user = ? ";
26 private static final String SQL_FILTER_PROVIDER = " subscription_provider = ? ";
27 private static final String SQL_FILTER_SUBSCRIPTION_KEY = " subscription_key = ? ";
28 private static final String SQL_FILTER_ID_SUBSCRIBED_RESOURCE = " id_subscribed_resource = ? ";
29 private static final String SQL_FILTER_ID_USER_ORDERED = " id_user";
30 private static final String SQL_FILTER_EMAIL_SUBSCRIBES = " email_subscribes != ?";
31 private static final String CONSTANT_WHERE = " WHERE ";
32 private static final String CONSTANT_AND = " AND ";
33 private static final String CONSTANT_OR = " OR ";
34 private static final String CONSTANT_COMMA = " , ";
35 private static final String CONSTANT_GROUP_BY = " GROUP BY ";
36 private static final String CONSTANT_ORDER_BY = " ORDER BY ";
37 private static final String SQL_FILTER_ID_SUBSCRIPTION = " id_subscription ";
38 private static final String CONSTANT_LIMIT = " LIMIT ";
39
40
41
42
43
44
45 private int newPrimaryKey( Plugin plugin )
46 {
47 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_PK, plugin );
48 daoUtil.executeQuery( );
49
50 int nKey = 1;
51 if ( daoUtil.next( ) )
52 {
53 nKey = daoUtil.getInt( 1 ) + 1;
54 }
55
56 daoUtil.free( );
57 return nKey;
58 }
59
60
61
62
63
64 @Override
65 public void insert(AnnounceSubscribtionDTO subscription, Plugin plugin)
66 {
67 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, plugin );
68
69 subscription.setIdSubscription( newPrimaryKey( plugin ) );
70
71 daoUtil.setInt( 1, subscription.getIdSubscription( ) );
72 daoUtil.setString( 2, subscription.getUserId( ) );
73 daoUtil.setString( 3, subscription.getSubscriptionProvider( ) );
74 daoUtil.setString( 4, subscription.getSubscriptionKey( ) );
75 daoUtil.setString( 5, subscription.getIdSubscribedResource( ) );
76 daoUtil.setString( 6, subscription.getEmailSubscribes( ) );
77
78 daoUtil.executeUpdate( );
79 daoUtil.free( );
80 }
81
82 @Override
83 public void store(AnnounceSubscribtionDTO subscription, Plugin plugin) {
84 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin );
85
86 daoUtil.setString( 1, subscription.getUserId( ) );
87 daoUtil.setString( 2, subscription.getSubscriptionProvider( ) );
88 daoUtil.setString( 3, subscription.getSubscriptionKey( ) );
89 daoUtil.setString( 4, subscription.getIdSubscribedResource( ) );
90 daoUtil.setInt( 5, subscription.getIdSubscription( ) );
91 daoUtil.setString( 6, subscription.getEmailSubscribes( ) );
92
93 daoUtil.executeUpdate( );
94 daoUtil.free( );
95 }
96
97
98
99
100 @Override
101 public void delete( int nSubscriptionId, Plugin plugin )
102 {
103 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin );
104 daoUtil.setInt( 1, nSubscriptionId );
105 daoUtil.executeUpdate( );
106 daoUtil.free( );
107 }
108
109
110
111
112
113 @Override
114 public AnnounceSubscribtionDTO load(int nId, Plugin plugin) {
115 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_FROM_SUBSCRIPTION_ID, plugin );
116 daoUtil.setInt( 1, nId );
117 daoUtil.executeQuery( );
118
119 AnnounceSubscribtionDTO subscription = null;
120
121 if ( daoUtil.next( ) )
122 {
123 subscription = new AnnounceSubscribtionDTO( );
124 subscription.setIdSubscription( daoUtil.getInt( 1 ) );
125 subscription.setUserId( daoUtil.getString( 2 ) );
126 subscription.setSubscriptionProvider( daoUtil.getString( 3 ) );
127 subscription.setSubscriptionKey( daoUtil.getString( 4 ) );
128 subscription.setIdSubscribedResource( daoUtil.getString( 5 ) );
129 subscription.setEmailSubscribes( daoUtil.getString( 6 ) );
130 }
131
132 daoUtil.free( );
133 return subscription;
134 }
135
136
137
138 @Override
139 public Collection<AnnounceSubscribtionDTO> selectSubscriptionsList(Plugin plugin) {
140 Collection<AnnounceSubscribtionDTO> subscriptionList = new ArrayList<AnnounceSubscribtionDTO>( );
141 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL, plugin );
142 daoUtil.executeQuery( );
143
144 while ( daoUtil.next( ) )
145 {
146 AnnounceSubscribtionDTO subscription = new AnnounceSubscribtionDTO( );
147
148 subscription.setIdSubscription( daoUtil.getInt( 1 ) );
149 subscription.setUserId( daoUtil.getString( 2 ) );
150 subscription.setSubscriptionProvider( daoUtil.getString( 3 ) );
151 subscription.setSubscriptionKey( daoUtil.getString( 4 ) );
152 subscription.setIdSubscribedResource( daoUtil.getString( 5 ) );
153 subscription.setEmailSubscribes( daoUtil.getString( 6 ) );
154
155 subscriptionList.add( subscription );
156 }
157
158 daoUtil.free( );
159 return subscriptionList;
160 }
161
162 @Override
163 public List<AnnounceSubscribtionDTO> findByFilter(SubscriptionFilter filter) {
164 List<AnnounceSubscribtionDTO> listSubscription = new ArrayList<AnnounceSubscribtionDTO>( );
165 boolean bHasFilter = false;
166 StringBuilder sbSql = new StringBuilder( SQL_QUERY_SELECT );
167 if ( StringUtils.isNotEmpty( filter.getUserId( ) ) )
168 {
169 sbSql.append( CONSTANT_WHERE );
170 sbSql.append( SQL_FILTER_ID_USER );
171 bHasFilter = true;
172 }
173 if ( filter.getSubscriptionProvider( ) != null )
174 {
175 if ( bHasFilter )
176 {
177 sbSql.append( CONSTANT_AND );
178 }
179 else
180 {
181 sbSql.append( CONSTANT_WHERE );
182 bHasFilter = true;
183 }
184 sbSql.append( SQL_FILTER_PROVIDER );
185 }
186 if ( filter.getSubscriptionKey( ) != null )
187 {
188 if ( bHasFilter )
189 {
190 sbSql.append( CONSTANT_AND );
191 }
192 else
193 {
194 sbSql.append( CONSTANT_WHERE );
195 bHasFilter = true;
196 }
197 sbSql.append( SQL_FILTER_SUBSCRIPTION_KEY );
198 }
199 if ( filter.getIdSubscribedResource( ) != null )
200 {
201 if ( bHasFilter )
202 {
203 sbSql.append( CONSTANT_AND );
204 }
205 else
206 {
207 sbSql.append( CONSTANT_WHERE );
208 bHasFilter = true;
209 }
210 sbSql.append( SQL_FILTER_ID_SUBSCRIBED_RESOURCE );
211 }
212
213 int nIndex = 1;
214 DAOUtil daoUtil = new DAOUtil( sbSql.toString( ) );
215 if ( StringUtils.isNotEmpty( filter.getUserId( ) ) )
216 {
217 daoUtil.setString( nIndex++, filter.getUserId( ) );
218 }
219 if ( filter.getSubscriptionProvider( ) != null )
220 {
221 daoUtil.setString( nIndex++, filter.getSubscriptionProvider( ) );
222 }
223 if ( filter.getSubscriptionKey( ) != null )
224 {
225 daoUtil.setString( nIndex++, filter.getSubscriptionKey( ) );
226 }
227 if ( filter.getIdSubscribedResource( ) != null )
228 {
229
230 daoUtil.setString( nIndex, filter.getIdSubscribedResource( ) );
231 }
232 daoUtil.executeQuery( );
233
234 while ( daoUtil.next( ) )
235 {
236 AnnounceSubscribtionDTO subscription = new AnnounceSubscribtionDTO( );
237 subscription.setIdSubscription( daoUtil.getInt( 1 ) );
238 subscription.setUserId( daoUtil.getString( 2 ) );
239 subscription.setSubscriptionProvider( daoUtil.getString( 3 ) );
240 subscription.setSubscriptionKey( daoUtil.getString( 4 ) );
241 subscription.setIdSubscribedResource( daoUtil.getString( 5 ) );
242 subscription.setEmailSubscribes( daoUtil.getString( 6 ) );
243 listSubscription.add( subscription );
244 }
245
246 daoUtil.free( );
247
248 return listSubscription;
249 }
250
251 @Override
252 public List<AnnounceSubscribtionDTO> findByFilterOr(String userId, String categoryId, String limitRows) {
253 List<AnnounceSubscribtionDTO> listSubscription = new ArrayList<AnnounceSubscribtionDTO>( );
254 boolean bHasFilter = false;
255 StringBuilder sbSql = new StringBuilder( SQL_QUERY_SELECT );
256 sbSql.append( CONSTANT_WHERE );
257
258 if ( StringUtils.isNotEmpty( userId ) )
259 {
260
261 sbSql.append( SQL_FILTER_ID_SUBSCRIBED_RESOURCE );
262 bHasFilter = true;
263 }
264 if ( StringUtils.isNotEmpty( categoryId ) )
265 {
266 if ( bHasFilter )
267 {
268 sbSql.append( CONSTANT_OR );
269 }
270 else
271 {
272 bHasFilter = true;
273 }
274 sbSql.append( SQL_FILTER_ID_SUBSCRIBED_RESOURCE );
275 }
276 if ( bHasFilter ) {
277 sbSql.append( CONSTANT_AND);
278 sbSql.append(SQL_FILTER_EMAIL_SUBSCRIBES);
279 }else
280 {
281 sbSql.append(SQL_FILTER_EMAIL_SUBSCRIBES);
282 bHasFilter = true;
283 }
284
285 sbSql.append(CONSTANT_GROUP_BY+SQL_FILTER_ID_USER_ORDERED);
286 sbSql.append(CONSTANT_ORDER_BY+SQL_FILTER_ID_SUBSCRIPTION);
287 sbSql.append(CONSTANT_LIMIT+limitRows);
288
289 int nIndex = 1;
290 DAOUtil daoUtil = new DAOUtil( sbSql.toString( ) );
291 if ( StringUtils.isNotEmpty( userId ) )
292 {
293 daoUtil.setString( nIndex++, userId);
294 }
295 if ( StringUtils.isNotEmpty( categoryId ) )
296 {
297 daoUtil.setString( nIndex++, categoryId );
298 }
299 daoUtil.setString( nIndex, "NULL" );
300 daoUtil.executeQuery( );
301
302 while ( daoUtil.next( ) )
303 {
304 AnnounceSubscribtionDTO subscription = new AnnounceSubscribtionDTO( );
305 subscription.setIdSubscription( daoUtil.getInt( 1 ) );
306 subscription.setUserId( daoUtil.getString( 2 ) );
307 subscription.setSubscriptionProvider( daoUtil.getString( 3 ) );
308 subscription.setSubscriptionKey( daoUtil.getString( 4 ) );
309 subscription.setIdSubscribedResource( daoUtil.getString( 5 ) );
310 subscription.setEmailSubscribes( daoUtil.getString( 6 ) );
311 listSubscription.add( subscription );
312 }
313
314 daoUtil.free( );
315
316 return listSubscription;
317 }
318 }