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.demand;
35
36 import fr.paris.lutece.plugins.crm.util.constants.CRMConstants;
37 import fr.paris.lutece.portal.service.plugin.Plugin;
38 import fr.paris.lutece.util.sql.DAOUtil;
39
40 import org.apache.commons.lang3.StringUtils;
41
42 import java.text.SimpleDateFormat;
43
44 import java.util.ArrayList;
45 import java.util.List;
46
47
48
49
50
51
52 public class DemandDAO implements IDemandDAO
53 {
54 private static final String SQL_QUERY_NEW_PK = " SELECT max( id_demand ) FROM crm_demand ";
55 private static final String SQL_QUERY_INSERT = " INSERT INTO crm_demand (id_demand, id_demand_type, id_crm_user, status_text, id_status_crm, data, date_modification, remote_id ) VALUES (?,?,?,?,?,?,?,?) ";
56 private static final String SQL_QUERY_SELECT = " SELECT id_demand, id_demand_type, id_crm_user, status_text, id_status_crm, data, date_modification, remote_id FROM crm_demand WHERE id_demand = ? ";
57 private static final String SQL_QUERY_SELECT_BY_REMOTE_KEY = " SELECT id_demand, id_demand_type, id_crm_user, status_text, id_status_crm, data, date_modification, remote_id FROM crm_demand WHERE remote_id = ? AND id_demand_type = ? ";
58 private static final String SQL_QUERY_UPDATE = " UPDATE crm_demand SET id_demand_type = ?, id_crm_user = ?, status_text = ?, id_status_crm = ?, data = ?, date_modification = ? ,remote_id= ? WHERE id_demand = ? ";
59 private static final String SQL_QUERY_DELETE = " DELETE FROM crm_demand WHERE id_demand = ? ";
60 private static final String SQL_QUERY_SELECT_ALL = " SELECT id_demand, id_demand_type, id_crm_user, status_text, id_status_crm, data, date_modification,remote_id, (SELECT count(*) FROM crm_notification WHERE is_read = 0 AND id_demand = demand.id_demand) AS nb_unread_notif FROM crm_demand demand ";
61 private static final String SQL_QUERY_SELECT_ALL_WITH_NOTIFICATION = " SELECT demand.id_demand, id_demand_type, id_crm_user, status_text, id_status_crm, data, date_modification,remote_id, (SELECT count(*) FROM crm_notification WHERE is_read = 0 AND id_demand = demand.id_demand) AS nb_unread_notif FROM crm_demand AS demand ";
62 private static final String SQL_QUERY_COUNT = " SELECT count(*) FROM ";
63
64
65 private static final String SQL_PERCENT = "%";
66 private static final String SQL_ORDER_BY = " ORDER BY ";
67 private static final String SQL_DESC = " DESC ";
68 private static final String SQL_ASC = " ASC ";
69 private static final String SQL_OR = " OR ";
70 private static final String SQL_AND = " AND ";
71 private static final String SQL_WHERE = " WHERE ";
72 private static final String SQL_DATE_MODIFICATION_ORDER = " date_modification ";
73 private static final String SQL_FILTER_ID_CRM_USER = " id_crm_user = ? ";
74 private static final String SQL_FILTER_ID_DEMAND_TYPE = " id_demand_type = ? ";
75 private static final String SQL_FILTER_DATE_MODIFICATION = " date_modification LIKE ? ";
76 private static final String SQL_FILTER_ID_STATUS_CRM = " id_status_crm = ? ";
77 private static final String SQL_NB_UNREAD_NOTIFICATION = " nb_unread_notif ";
78 private static final String SQL_FILTER_NOTIFICATION = " EXISTS (SELECT id_notification FROM crm_notification notif WHERE (notif.object LIKE ? OR notif.message LIKE ?) AND notif.id_demand = demand.id_demand )";
79
80
81
82
83 public int newPrimaryKey( Plugin plugin )
84 {
85 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_PK, plugin ) )
86 {
87 daoUtil.executeQuery( );
88
89 int nKey = 1;
90
91 if ( daoUtil.next( ) )
92 {
93 nKey = daoUtil.getInt( 1 ) + 1;
94 }
95
96 return nKey;
97 }
98 }
99
100
101
102
103 public synchronized int insert( Demand demand, Plugin plugin )
104 {
105 int nIdDemand = -1;
106
107 if ( demand != null )
108 {
109 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, plugin ) )
110 {
111 int nIndex = 1;
112 demand.setIdDemand( newPrimaryKey( plugin ) );
113 daoUtil.setInt( nIndex++, demand.getIdDemand( ) );
114 daoUtil.setInt( nIndex++, demand.getIdDemandType( ) );
115 daoUtil.setInt( nIndex++, demand.getIdCRMUser( ) );
116 daoUtil.setString( nIndex++, demand.getStatusText( ) );
117 daoUtil.setInt( nIndex++, demand.getIdStatusCRM( ) );
118 daoUtil.setString( nIndex++, demand.getData( ) );
119 daoUtil.setTimestamp( nIndex++, demand.getDateModification( ) );
120 daoUtil.setString( nIndex++, demand.getRemoteId( ) );
121
122 daoUtil.executeUpdate( );
123 }
124
125 nIdDemand = demand.getIdDemand( );
126 }
127
128 return nIdDemand;
129 }
130
131
132
133
134 public Demand load( int nIdDemand, Plugin plugin )
135 {
136 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT, plugin ) )
137 {
138 daoUtil.setInt( 1, nIdDemand );
139 daoUtil.executeQuery( );
140
141 Demand demand = null;
142
143 if ( daoUtil.next( ) )
144 {
145 int nIndex = 1;
146 demand = new Demand( );
147 demand.setIdDemand( daoUtil.getInt( nIndex++ ) );
148 demand.setIdDemandType( daoUtil.getInt( nIndex++ ) );
149 demand.setIdCRMUser( daoUtil.getInt( nIndex++ ) );
150 demand.setStatusText( daoUtil.getString( nIndex++ ) );
151 demand.setIdStatusCRM( daoUtil.getInt( nIndex++ ) );
152 demand.setData( daoUtil.getString( nIndex++ ) );
153 demand.setDateModification( daoUtil.getTimestamp( nIndex++ ) );
154 demand.setRemoteId( daoUtil.getString( nIndex++ ) );
155 }
156
157 return demand;
158 }
159 }
160
161
162
163
164 @Override
165 public Demand loadByRemoteKey( String strRemoteId, int nIdDemandType, Plugin plugin )
166 {
167 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_REMOTE_KEY, plugin ) )
168 {
169 daoUtil.setString( 1, strRemoteId );
170 daoUtil.setInt( 2, nIdDemandType );
171
172 daoUtil.executeQuery( );
173
174 Demand demand = null;
175
176 if ( daoUtil.next( ) )
177 {
178 int nIndex = 1;
179 demand = new Demand( );
180 demand.setIdDemand( daoUtil.getInt( nIndex++ ) );
181 demand.setIdDemandType( daoUtil.getInt( nIndex++ ) );
182 demand.setIdCRMUser( daoUtil.getInt( nIndex++ ) );
183 demand.setStatusText( daoUtil.getString( nIndex++ ) );
184 demand.setIdStatusCRM( daoUtil.getInt( nIndex++ ) );
185 demand.setData( daoUtil.getString( nIndex++ ) );
186 demand.setDateModification( daoUtil.getTimestamp( nIndex++ ) );
187 demand.setRemoteId( daoUtil.getString( nIndex++ ) );
188 }
189
190 return demand;
191 }
192 }
193
194
195
196
197 public void store( Demand demand, Plugin plugin )
198 {
199 if ( demand != null )
200 {
201 int nIndex = 1;
202
203 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin ) )
204 {
205 daoUtil.setInt( nIndex++, demand.getIdDemandType( ) );
206 daoUtil.setInt( nIndex++, demand.getIdCRMUser( ) );
207 daoUtil.setString( nIndex++, demand.getStatusText( ) );
208 daoUtil.setInt( nIndex++, demand.getIdStatusCRM( ) );
209 daoUtil.setString( nIndex++, demand.getData( ) );
210 daoUtil.setTimestamp( nIndex++, demand.getDateModification( ) );
211 daoUtil.setString( nIndex++, demand.getRemoteId( ) );
212
213 daoUtil.setInt( nIndex++, demand.getIdDemand( ) );
214
215 daoUtil.executeUpdate( );
216 }
217 }
218 }
219
220
221
222
223 public void delete( int nIdDemand, Plugin plugin )
224 {
225 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin ) )
226 {
227 daoUtil.setInt( 1, nIdDemand );
228 daoUtil.executeUpdate( );
229 }
230 }
231
232
233
234
235 public List<Demand> selectAll( Plugin plugin )
236 {
237 List<Demand> listDemands = new ArrayList<Demand>( );
238 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ALL, plugin ) )
239 {
240 daoUtil.executeQuery( );
241
242 while ( daoUtil.next( ) )
243 {
244 int nIndex = 1;
245 Demandgins/crm/business/demand/Demand.html#Demand">Demand demand = new Demand( );
246 demand.setIdDemand( daoUtil.getInt( nIndex++ ) );
247 demand.setIdDemandType( daoUtil.getInt( nIndex++ ) );
248 demand.setIdCRMUser( daoUtil.getInt( nIndex++ ) );
249 demand.setStatusText( daoUtil.getString( nIndex++ ) );
250 demand.setIdStatusCRM( daoUtil.getInt( nIndex++ ) );
251 demand.setData( daoUtil.getString( nIndex++ ) );
252 demand.setDateModification( daoUtil.getTimestamp( nIndex++ ) );
253 demand.setRemoteId( daoUtil.getString( nIndex++ ) );
254
255 listDemands.add( demand );
256 }
257 }
258 return listDemands;
259 }
260
261
262
263
264 public List<Demand> selectByFilter( DemandFilter dFilter, IPaginationProperties paginationProperties, Plugin plugin )
265 {
266 List<Demand> listDemands = new ArrayList<Demand>( );
267 StringBuilder sbSQL = new StringBuilder( buildSQLQuery( dFilter ) );
268
269 if ( paginationProperties != null )
270 {
271 sbSQL.append( " LIMIT " + paginationProperties.getItemsPerPage( ) );
272 sbSQL.append( " OFFSET " + ( ( paginationProperties.getPageIndex( ) - 1 ) * paginationProperties.getItemsPerPage( ) ) );
273 }
274
275 try ( DAOUtil daoUtil = new DAOUtil( sbSQL.toString( ), plugin ) )
276 {
277 setFilterValues( dFilter, daoUtil );
278 daoUtil.executeQuery( );
279
280 while ( daoUtil.next( ) )
281 {
282 int nIndex = 1;
283 Demandgins/crm/business/demand/Demand.html#Demand">Demand demand = new Demand( );
284 demand.setIdDemand( daoUtil.getInt( nIndex++ ) );
285 demand.setIdDemandType( daoUtil.getInt( nIndex++ ) );
286 demand.setIdCRMUser( daoUtil.getInt( nIndex++ ) );
287 demand.setStatusText( daoUtil.getString( nIndex++ ) );
288 demand.setIdStatusCRM( daoUtil.getInt( nIndex++ ) );
289 demand.setData( daoUtil.getString( nIndex++ ) );
290 demand.setDateModification( daoUtil.getTimestamp( nIndex++ ) );
291 demand.setRemoteId( daoUtil.getString( nIndex++ ) );
292
293 listDemands.add( demand );
294 }
295
296 return listDemands;
297 }
298 }
299
300
301
302
303
304
305
306
307 private String buildSQLQuery( DemandFilter dFilter )
308 {
309 StringBuilder sbSQL = new StringBuilder( );
310
311 if ( StringUtils.isNotBlank( dFilter.getNotification( ) ) )
312 {
313 sbSQL.append( SQL_QUERY_SELECT_ALL_WITH_NOTIFICATION );
314 }
315 else
316 {
317 sbSQL.append( SQL_QUERY_SELECT_ALL );
318 }
319
320 int nIndex = 1;
321
322 if ( dFilter.containsIdCRMUser( ) )
323 {
324 nIndex = addSQLWhereOr( dFilter.getIsWideSearch( ), sbSQL, nIndex );
325 sbSQL.append( SQL_FILTER_ID_CRM_USER );
326 }
327
328 if ( dFilter.containsIdDemandType( ) )
329 {
330 nIndex = addSQLWhereOr( dFilter.getIsWideSearch( ), sbSQL, nIndex );
331 sbSQL.append( SQL_FILTER_ID_DEMAND_TYPE );
332 }
333
334 if ( dFilter.containsDateModification( ) )
335 {
336 nIndex = addSQLWhereOr( dFilter.getIsWideSearch( ), sbSQL, nIndex );
337 sbSQL.append( SQL_FILTER_DATE_MODIFICATION );
338 }
339
340 if ( dFilter.containsIdStatusCRM( ) )
341 {
342 nIndex = addSQLWhereOr( dFilter.getIsWideSearch( ), sbSQL, nIndex );
343 sbSQL.append( SQL_FILTER_ID_STATUS_CRM );
344 }
345
346 if ( StringUtils.isNotBlank( dFilter.getNotification( ) ) )
347 {
348 nIndex = addSQLWhereOr( dFilter.getIsWideSearch( ), sbSQL, nIndex );
349 sbSQL.append( SQL_FILTER_NOTIFICATION );
350 }
351
352
353 sbSQL.append( SQL_ORDER_BY );
354
355 List<DemandSort> listDemandSort = dFilter.getListDemandSort( );
356
357 if ( ( listDemandSort == null ) || listDemandSort.isEmpty( ) )
358 {
359
360 sbSQL.append( SQL_DATE_MODIFICATION_ORDER );
361 sbSQL.append( SQL_DESC );
362 }
363 else
364 {
365 int nSize = listDemandSort.size( );
366
367 for ( int i = 0; i < nSize; i++ )
368 {
369 if ( i != 0 )
370 {
371 sbSQL.append( " , " );
372 }
373
374 DemandSort demandSort = listDemandSort.get( i );
375
376 if ( CRMConstants.SORT_DATE_MODIFICATION.equals( demandSort.getField( ) ) )
377 {
378 sbSQL.append( SQL_DATE_MODIFICATION_ORDER );
379 }
380 else
381 if ( CRMConstants.SORT_NB_UNREAD_NOTIFICATION.equals( demandSort.getField( ) ) )
382 {
383 sbSQL.append( SQL_NB_UNREAD_NOTIFICATION );
384 }
385
386 if ( demandSort.isAsc( ) )
387 {
388 sbSQL.append( SQL_ASC );
389 }
390 else
391 {
392 sbSQL.append( SQL_DESC );
393 }
394 }
395 }
396
397 return sbSQL.toString( );
398 }
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415 private int addSQLWhereOr( boolean bIsWideSearch, StringBuilder sbSQL, int nIndex )
416 {
417 if ( nIndex == 1 )
418 {
419 sbSQL.append( SQL_WHERE );
420 }
421 else
422 {
423 sbSQL.append( bIsWideSearch ? SQL_OR : SQL_AND );
424 }
425
426 return nIndex + 1;
427 }
428
429
430
431
432
433
434
435
436
437 private void setFilterValues( DemandFilter dFilter, DAOUtil daoUtil )
438 {
439 int nIndex = 1;
440
441 if ( dFilter.containsIdCRMUser( ) )
442 {
443 daoUtil.setInt( nIndex++, dFilter.getIdCRMUser( ) );
444 }
445
446 if ( dFilter.containsIdDemandType( ) )
447 {
448 daoUtil.setInt( nIndex++, dFilter.getIdDemandType( ) );
449 }
450 if ( dFilter.containsDateModification( ) )
451 {
452 SimpleDateFormat sdfSQL = new SimpleDateFormat( "yyyy-MM-dd" );
453 String strDateModification = sdfSQL.format( dFilter.getDateModification( ) );
454 StringBuilder strNotificationBuilder = new StringBuilder( );
455 strNotificationBuilder.append( strDateModification );
456 strNotificationBuilder.append( SQL_PERCENT );
457
458 daoUtil.setString( nIndex++, strNotificationBuilder.toString( ) );
459 }
460
461 if ( dFilter.containsIdStatusCRM( ) )
462 {
463 daoUtil.setInt( nIndex++, dFilter.getIdStatusCRM( ) );
464 }
465 if ( StringUtils.isNotBlank( dFilter.getNotification( ) ) )
466 {
467 StringBuilder strNotificationBuilder = new StringBuilder( );
468 strNotificationBuilder.append( SQL_PERCENT );
469 strNotificationBuilder.append( dFilter.getNotification( ) );
470 strNotificationBuilder.append( SQL_PERCENT );
471
472 daoUtil.setString( nIndex++, strNotificationBuilder.toString( ) );
473 daoUtil.setString( nIndex++, strNotificationBuilder.toString( ) );
474 }
475 }
476
477
478
479
480 public int countByFilter( DemandFilter dFilter, Plugin plugin )
481 {
482 StringBuilder sbSQL = new StringBuilder( SQL_QUERY_COUNT );
483 sbSQL.append( " ( " );
484 sbSQL.append( buildSQLQuery( dFilter ) );
485 sbSQL.append( " ) AS results" );
486
487 int nTotalResult = 0;
488
489 try ( DAOUtil daoUtil = new DAOUtil( sbSQL.toString( ), plugin ) )
490 {
491 setFilterValues( dFilter, daoUtil );
492 daoUtil.executeQuery( );
493
494 while ( daoUtil.next( ) )
495 {
496 nTotalResult = daoUtil.getInt( 1 );
497 }
498 }
499
500 return nTotalResult;
501 }
502 }