View Javadoc
1   /*
2    * Copyright (c) 2002-2014, Mairie de Paris
3    * All rights reserved.
4    *
5    * Redistribution and use in source and binary forms, with or without
6    * modification, are permitted provided that the following conditions
7    * are met:
8    *
9    *  1. Redistributions of source code must retain the above copyright notice
10   *     and the following disclaimer.
11   *
12   *  2. Redistributions in binary form must reproduce the above copyright notice
13   *     and the following disclaimer in the documentation and/or other materials
14   *     provided with the distribution.
15   *
16   *  3. Neither the name of 'Mairie de Paris' nor 'Lutece' nor the names of its
17   *     contributors may be used to endorse or promote products derived from
18   *     this software without specific prior written permission.
19   *
20   * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
21   * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
22   * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
23   * ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDERS OR CONTRIBUTORS BE
24   * LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
25   * CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
26   * SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
27   * INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
28   * CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
29   * ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
30   * POSSIBILITY OF SUCH DAMAGE.
31   *
32   * License 1.0
33   */
34  package fr.paris.lutece.plugins.crm.business.demand;
35  
36  import fr.paris.lutece.plugins.crm.util.TargetEnum;
37  import fr.paris.lutece.portal.service.plugin.Plugin;
38  import fr.paris.lutece.util.sql.DAOUtil;
39  
40  import java.sql.Date;
41  
42  import java.util.ArrayList;
43  import java.util.List;
44  
45  /**
46   *
47   * DemandTypeDAO
48   *
49   */
50  public class DemandTypeDAO implements IDemandTypeDAO
51  {
52      // SQL QUERIES
53      private static final String SQL_QUERY_NEW_PK = " SELECT max( id_demand_type ) FROM crm_demand_type ";
54      private static final String SQL_QUERY_INSERT = " INSERT INTO crm_demand_type (id_demand_type, label, url_resource, url_info, url_contact, demand_type_order, id_category, date_begin, date_end, workgroup_key, role_key, target, url_delete, is_include_id_user,is_need_authentication ,  is_need_validation) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ";
55      private static final String SQL_QUERY_SELECT = " SELECT id_demand_type, label, url_resource, url_info, url_contact, demand_type_order, id_category, date_begin, date_end, workgroup_key, role_key, target, url_delete, is_include_id_user,is_need_authentication ,  is_need_validation FROM crm_demand_type WHERE id_demand_type = ? ";
56      private static final String SQL_QUERY_UPDATE = " UPDATE crm_demand_type SET label = ?, url_resource = ?, url_info = ?, url_contact = ?, demand_type_order = ?, id_category = ?, date_begin = ?, date_end = ?, workgroup_key = ?, role_key = ?, target = ?, url_delete = ?,is_include_id_user=?, is_need_authentication =? ,is_need_validation=? WHERE id_demand_type = ? ";
57      private static final String SQL_QUERY_DELETE = " DELETE FROM crm_demand_type WHERE id_demand_type = ? ";
58      private static final String SQL_QUERY_SELECT_ALL = " SELECT id_demand_type, label, url_resource, url_info, url_contact, demand_type_order, id_category, date_begin, date_end, workgroup_key, role_key, target, url_delete, is_include_id_user, is_need_authentication, is_need_validation FROM crm_demand_type ";
59      private static final String SQL_QUERY_SELECT_MAX_ORDER = " SELECT max( demand_type_order ) FROM crm_demand_type ";
60      private static final String SQL_QUERY_SELECT_BY_DEMAND_TYPE_ORDER = " SELECT id_demand_type, label, url_resource, url_info, url_contact, demand_type_order, id_category, date_begin, date_end, workgroup_key, role_key, target, url_delete, is_include_id_user, is_need_authentication, is_need_validation FROM crm_demand_type WHERE demand_type_order = ? ";
61      private static final String SQL_QUERY_SELECT_BY_ID_CATEGORY_AND_DATE = " SELECT id_demand_type, label, url_resource, url_info, url_contact, demand_type_order, id_category, date_begin, date_end, workgroup_key, role_key, target, url_delete, is_include_id_user, is_need_authentication, is_need_validation "
62              + " FROM crm_demand_type WHERE id_category = ? AND ( date_begin IS NULL OR date_begin <= ? ) AND ( date_end IS NULL OR date_end > ? ) ";
63      private static final String SQL_QUERY_SELECT_NO_DATE_END_DEMAND_TYPES = " SELECT id_demand_type, label, url_resource, url_info, url_contact, demand_type_order, id_category, date_begin, date_end, workgroup_key, role_key, target, url_delete, is_include_id_user, is_need_authentication, is_need_validation FROM crm_demand_type WHERE date_end IS NULL ";
64  
65      // FILTERS
66      private static final String SQL_ORDER_BY = " ORDER BY ";
67      private static final String SQL_ASC = " ASC ";
68      private static final String SQL_OR = " OR ";
69      private static final String SQL_AND = " AND ";
70      private static final String SQL_WHERE = " WHERE ";
71      private static final String SQL_FILTER_DEMAND_TYPE_ORDER = " demand_type_order ";
72      private static final String SQL_FILTER_LABEL = " label LIKE ? ";
73      private static final String SQL_FILTER_URL_RESOURCE = " url_resource LIKE ? ";
74      private static final String SQL_FILTER_ID_CATEGORY = " id_category = ? ";
75      private static final String SQL_FILTER_DATE_BEGIN = " date_begin ";
76      private static final String SQL_FILTER_DATE_END = " date_end ";
77      private static final String SQL_FILTER_WORKGROUP_KEY = " workgroup_key = ? ";
78      private static final String SQL_FILTER_ROLE_KEY = " role_key = ? ";
79      private static final String SQL_FILTER_URL_DELETE = " url_delete LIKE ? ";
80      private static final String PERCENT = "%";
81      private static final String QUESTION_MARK = " ? ";
82  
83      /**
84       * {@inheritDoc}
85       */
86      public int newPrimaryKey( Plugin plugin )
87      {
88          DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_PK, plugin );
89          daoUtil.executeQuery( );
90  
91          int nKey = 1;
92  
93          if ( daoUtil.next( ) )
94          {
95              nKey = daoUtil.getInt( 1 ) + 1;
96          }
97  
98          daoUtil.free( );
99  
100         return nKey;
101     }
102 
103     /**
104      * {@inheritDoc}
105      */
106     public synchronized int insert( DemandType demandType, Plugin plugin )
107     {
108         int nKey = -1;
109 
110         if ( demandType != null )
111         {
112             DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, plugin );
113 
114             int nIndex = 1;
115             demandType.setIdDemandType( newPrimaryKey( plugin ) );
116 
117             daoUtil.setInt( nIndex++, demandType.getIdDemandType( ) );
118             daoUtil.setString( nIndex++, demandType.getLabel( ) );
119             daoUtil.setString( nIndex++, demandType.getUrlResource( ) );
120             daoUtil.setString( nIndex++, demandType.getUrlInfo( ) );
121             daoUtil.setString( nIndex++, demandType.getUrlContact( ) );
122             daoUtil.setInt( nIndex++, demandType.getOrder( ) );
123             daoUtil.setInt( nIndex++, demandType.getIdCategory( ) );
124 
125             Date dateBegin = null;
126 
127             if ( demandType.getDateBegin( ) != null )
128             {
129                 dateBegin = new Date( demandType.getDateBegin( ).getTime( ) );
130             }
131 
132             Date dateEnd = null;
133 
134             if ( demandType.getDateEnd( ) != null )
135             {
136                 dateEnd = new Date( demandType.getDateEnd( ).getTime( ) );
137             }
138 
139             daoUtil.setDate( nIndex++, dateBegin );
140             daoUtil.setDate( nIndex++, dateEnd );
141             daoUtil.setString( nIndex++, demandType.getWorkgroup( ) );
142             daoUtil.setString( nIndex++, demandType.getRole( ) );
143             daoUtil.setInt( nIndex++, demandType.getTarget( ).getId( ) );
144             daoUtil.setString( nIndex++, demandType.getUrlDelete( ) );
145             daoUtil.setBoolean( nIndex++, demandType.isIncludeIdCrmUser( ) );
146             daoUtil.setBoolean( nIndex++, demandType.isNeedAuthentication( ) );
147             daoUtil.setBoolean( nIndex++, demandType.isNeedValidation( ) );
148 
149             daoUtil.executeUpdate( );
150             daoUtil.free( );
151 
152             nKey = demandType.getIdDemandType( );
153         }
154 
155         return nKey;
156     }
157 
158     /**
159      * {@inheritDoc}
160      */
161     public DemandType load( int nIdDemandType, Plugin plugin )
162     {
163         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT, plugin );
164         daoUtil.setInt( 1, nIdDemandType );
165         daoUtil.executeQuery( );
166 
167         DemandType demandType = null;
168 
169         if ( daoUtil.next( ) )
170         {
171             int nIndex = 1;
172             demandType = new DemandType( );
173             demandType.setIdDemandType( daoUtil.getInt( nIndex++ ) );
174             demandType.setLabel( daoUtil.getString( nIndex++ ) );
175             demandType.setUrlResource( daoUtil.getString( nIndex++ ) );
176             demandType.setUrlInfo( daoUtil.getString( nIndex++ ) );
177             demandType.setUrlContact( daoUtil.getString( nIndex++ ) );
178             demandType.setOrder( daoUtil.getInt( nIndex++ ) );
179             demandType.setIdCategory( daoUtil.getInt( nIndex++ ) );
180             demandType.setDateBegin( daoUtil.getDate( nIndex++ ) );
181             demandType.setDateEnd( daoUtil.getDate( nIndex++ ) );
182             demandType.setWorkgroup( daoUtil.getString( nIndex++ ) );
183             demandType.setRole( daoUtil.getString( nIndex++ ) );
184             demandType.setTarget( TargetEnum.getTarget( daoUtil.getInt( nIndex++ ) ) );
185             demandType.setUrlDelete( daoUtil.getString( nIndex++ ) );
186             demandType.setIncludeIdCrmUser( daoUtil.getBoolean( nIndex++ ) );
187             demandType.setNeedAuthentication( daoUtil.getBoolean( nIndex++ ) );
188             demandType.setNeedValidation( daoUtil.getBoolean( nIndex++ ) );
189         }
190 
191         daoUtil.free( );
192 
193         return demandType;
194     }
195 
196     /**
197      * {@inheritDoc}
198      */
199     public void store( DemandType demandType, Plugin plugin )
200     {
201         if ( demandType != null )
202         {
203             int nIndex = 1;
204 
205             DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin );
206 
207             daoUtil.setString( nIndex++, demandType.getLabel( ) );
208             daoUtil.setString( nIndex++, demandType.getUrlResource( ) );
209             daoUtil.setString( nIndex++, demandType.getUrlInfo( ) );
210             daoUtil.setString( nIndex++, demandType.getUrlContact( ) );
211             daoUtil.setInt( nIndex++, demandType.getOrder( ) );
212             daoUtil.setInt( nIndex++, demandType.getIdCategory( ) );
213 
214             Date dateBegin = null;
215 
216             if ( demandType.getDateBegin( ) != null )
217             {
218                 dateBegin = new Date( demandType.getDateBegin( ).getTime( ) );
219             }
220 
221             Date dateEnd = null;
222 
223             if ( demandType.getDateEnd( ) != null )
224             {
225                 dateEnd = new Date( demandType.getDateEnd( ).getTime( ) );
226             }
227 
228             daoUtil.setDate( nIndex++, dateBegin );
229             daoUtil.setDate( nIndex++, dateEnd );
230             daoUtil.setString( nIndex++, demandType.getWorkgroup( ) );
231             daoUtil.setString( nIndex++, demandType.getRole( ) );
232             daoUtil.setInt( nIndex++, demandType.getTarget( ).getId( ) );
233             daoUtil.setString( nIndex++, demandType.getUrlDelete( ) );
234             daoUtil.setBoolean( nIndex++, demandType.isIncludeIdCrmUser( ) );
235             daoUtil.setBoolean( nIndex++, demandType.isNeedAuthentication( ) );
236             daoUtil.setBoolean( nIndex++, demandType.isNeedValidation( ) );
237 
238             daoUtil.setInt( nIndex++, demandType.getIdDemandType( ) );
239 
240             daoUtil.executeUpdate( );
241             daoUtil.free( );
242         }
243     }
244 
245     /**
246      * {@inheritDoc}
247      */
248     public void delete( int nIdDemandType, Plugin plugin )
249     {
250         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin );
251         daoUtil.setInt( 1, nIdDemandType );
252         daoUtil.executeUpdate( );
253         daoUtil.free( );
254     }
255 
256     /**
257      * {@inheritDoc}
258      */
259     public List<DemandType> selectAll( Plugin plugin )
260     {
261         List<DemandType> listDemandTypes = new ArrayList<DemandType>( );
262         StringBuilder sbSQL = new StringBuilder( SQL_QUERY_SELECT_ALL );
263         sbSQL.append( SQL_ORDER_BY );
264         sbSQL.append( SQL_FILTER_DEMAND_TYPE_ORDER );
265         sbSQL.append( SQL_ASC );
266 
267         DAOUtil daoUtil = new DAOUtil( sbSQL.toString( ), plugin );
268         daoUtil.executeQuery( );
269 
270         while ( daoUtil.next( ) )
271         {
272             int nIndex = 1;
273             DemandType/business/demand/DemandType.html#DemandType">DemandType demandType = new DemandType( );
274             demandType.setIdDemandType( daoUtil.getInt( nIndex++ ) );
275             demandType.setLabel( daoUtil.getString( nIndex++ ) );
276             demandType.setUrlResource( daoUtil.getString( nIndex++ ) );
277             demandType.setUrlInfo( daoUtil.getString( nIndex++ ) );
278             demandType.setUrlContact( daoUtil.getString( nIndex++ ) );
279             demandType.setOrder( daoUtil.getInt( nIndex++ ) );
280             demandType.setIdCategory( daoUtil.getInt( nIndex++ ) );
281             demandType.setDateBegin( daoUtil.getDate( nIndex++ ) );
282             demandType.setDateEnd( daoUtil.getDate( nIndex++ ) );
283             demandType.setWorkgroup( daoUtil.getString( nIndex++ ) );
284             demandType.setRole( daoUtil.getString( nIndex++ ) );
285             demandType.setTarget( TargetEnum.getTarget( daoUtil.getInt( nIndex++ ) ) );
286             demandType.setUrlDelete( daoUtil.getString( nIndex++ ) );
287             demandType.setIncludeIdCrmUser( daoUtil.getBoolean( nIndex++ ) );
288             demandType.setNeedAuthentication( daoUtil.getBoolean( nIndex++ ) );
289             demandType.setNeedValidation( daoUtil.getBoolean( nIndex++ ) );
290 
291             listDemandTypes.add( demandType );
292         }
293 
294         daoUtil.free( );
295 
296         return listDemandTypes;
297     }
298 
299     /**
300      * {@inheritDoc}
301      */
302     public List<DemandType> selectByIdCategoryAndDate( int nIdCategory, java.util.Date dateToday, Plugin plugin )
303     {
304         List<DemandType> listDemandTypes = new ArrayList<DemandType>( );
305         StringBuilder sbSQL = new StringBuilder( SQL_QUERY_SELECT_BY_ID_CATEGORY_AND_DATE );
306         sbSQL.append( SQL_ORDER_BY );
307         sbSQL.append( SQL_FILTER_DEMAND_TYPE_ORDER );
308         sbSQL.append( SQL_ASC );
309 
310         int nIndex = 1;
311         Date date = new Date( dateToday.getTime( ) );
312         DAOUtil daoUtil = new DAOUtil( sbSQL.toString( ), plugin );
313         daoUtil.setInt( nIndex++, nIdCategory );
314         daoUtil.setDate( nIndex++, date );
315         daoUtil.setDate( nIndex++, date );
316         daoUtil.executeQuery( );
317 
318         while ( daoUtil.next( ) )
319         {
320             nIndex = 1;
321 
322             DemandType/business/demand/DemandType.html#DemandType">DemandType demandType = new DemandType( );
323             demandType.setIdDemandType( daoUtil.getInt( nIndex++ ) );
324             demandType.setLabel( daoUtil.getString( nIndex++ ) );
325             demandType.setUrlResource( daoUtil.getString( nIndex++ ) );
326             demandType.setUrlInfo( daoUtil.getString( nIndex++ ) );
327             demandType.setUrlContact( daoUtil.getString( nIndex++ ) );
328             demandType.setOrder( daoUtil.getInt( nIndex++ ) );
329             demandType.setIdCategory( daoUtil.getInt( nIndex++ ) );
330             demandType.setDateBegin( daoUtil.getDate( nIndex++ ) );
331             demandType.setDateEnd( daoUtil.getDate( nIndex++ ) );
332             demandType.setWorkgroup( daoUtil.getString( nIndex++ ) );
333             demandType.setRole( daoUtil.getString( nIndex++ ) );
334             demandType.setTarget( TargetEnum.getTarget( daoUtil.getInt( nIndex++ ) ) );
335             demandType.setUrlDelete( daoUtil.getString( nIndex++ ) );
336             demandType.setIncludeIdCrmUser( daoUtil.getBoolean( nIndex++ ) );
337             demandType.setNeedAuthentication( daoUtil.getBoolean( nIndex++ ) );
338             demandType.setNeedValidation( daoUtil.getBoolean( nIndex++ ) );
339 
340             listDemandTypes.add( demandType );
341         }
342 
343         daoUtil.free( );
344 
345         return listDemandTypes;
346     }
347 
348     /**
349      * {@inheritDoc}
350      */
351     public DemandType selectByOrder( int nOrder, Plugin plugin )
352     {
353         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_DEMAND_TYPE_ORDER, plugin );
354         daoUtil.setInt( 1, nOrder );
355         daoUtil.executeQuery( );
356 
357         DemandType demandType = null;
358 
359         if ( daoUtil.next( ) )
360         {
361             int nIndex = 1;
362             demandType = new DemandType( );
363             demandType.setIdDemandType( daoUtil.getInt( nIndex++ ) );
364             demandType.setLabel( daoUtil.getString( nIndex++ ) );
365             demandType.setUrlResource( daoUtil.getString( nIndex++ ) );
366             demandType.setUrlInfo( daoUtil.getString( nIndex++ ) );
367             demandType.setUrlContact( daoUtil.getString( nIndex++ ) );
368             demandType.setOrder( daoUtil.getInt( nIndex++ ) );
369             demandType.setIdCategory( daoUtil.getInt( nIndex++ ) );
370             demandType.setDateBegin( daoUtil.getDate( nIndex++ ) );
371             demandType.setDateEnd( daoUtil.getDate( nIndex++ ) );
372             demandType.setWorkgroup( daoUtil.getString( nIndex++ ) );
373             demandType.setRole( daoUtil.getString( nIndex++ ) );
374             demandType.setTarget( TargetEnum.getTarget( daoUtil.getInt( nIndex++ ) ) );
375             demandType.setUrlDelete( daoUtil.getString( nIndex++ ) );
376             demandType.setIncludeIdCrmUser( daoUtil.getBoolean( nIndex++ ) );
377             demandType.setNeedAuthentication( daoUtil.getBoolean( nIndex++ ) );
378             demandType.setNeedValidation( daoUtil.getBoolean( nIndex++ ) );
379 
380         }
381 
382         daoUtil.free( );
383 
384         return demandType;
385     }
386 
387     /**
388      * {@inheritDoc}
389      */
390     public int selectMaxOrder( Plugin plugin )
391     {
392         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_MAX_ORDER, plugin );
393 
394         int nMaxOrder = 1;
395 
396         daoUtil.executeQuery( );
397 
398         if ( daoUtil.next( ) )
399         {
400             nMaxOrder = daoUtil.getInt( 1 );
401         }
402 
403         daoUtil.free( );
404 
405         return nMaxOrder;
406     }
407 
408     /**
409      * {@inheritDoc}
410      */
411     public List<DemandType> selectDemandTypesByFilter( DemandTypeFilter dtFilter, Plugin plugin )
412     {
413         List<DemandType> listDemandTypes = new ArrayList<DemandType>( );
414         StringBuilder sbSQL = new StringBuilder( buildSQLQuery( dtFilter ) );
415         sbSQL.append( SQL_ORDER_BY );
416         sbSQL.append( SQL_FILTER_DEMAND_TYPE_ORDER );
417         sbSQL.append( SQL_ASC );
418 
419         DAOUtil daoUtil = new DAOUtil( sbSQL.toString( ), plugin );
420         setFilterValues( dtFilter, daoUtil );
421         daoUtil.executeQuery( );
422 
423         while ( daoUtil.next( ) )
424         {
425             int nIndex = 1;
426             DemandType/business/demand/DemandType.html#DemandType">DemandType demandType = new DemandType( );
427             demandType.setIdDemandType( daoUtil.getInt( nIndex++ ) );
428             demandType.setLabel( daoUtil.getString( nIndex++ ) );
429             demandType.setUrlResource( daoUtil.getString( nIndex++ ) );
430             demandType.setUrlInfo( daoUtil.getString( nIndex++ ) );
431             demandType.setUrlContact( daoUtil.getString( nIndex++ ) );
432             demandType.setOrder( daoUtil.getInt( nIndex++ ) );
433             demandType.setIdCategory( daoUtil.getInt( nIndex++ ) );
434             demandType.setDateBegin( daoUtil.getDate( nIndex++ ) );
435             demandType.setDateEnd( daoUtil.getDate( nIndex++ ) );
436             demandType.setWorkgroup( daoUtil.getString( nIndex++ ) );
437             demandType.setRole( daoUtil.getString( nIndex++ ) );
438             demandType.setTarget( TargetEnum.getTarget( daoUtil.getInt( nIndex++ ) ) );
439             demandType.setUrlDelete( daoUtil.getString( nIndex++ ) );
440             demandType.setIncludeIdCrmUser( daoUtil.getBoolean( nIndex++ ) );
441             demandType.setNeedAuthentication( daoUtil.getBoolean( nIndex++ ) );
442             demandType.setNeedValidation( daoUtil.getBoolean( nIndex++ ) );
443 
444             listDemandTypes.add( demandType );
445         }
446 
447         daoUtil.free( );
448 
449         return listDemandTypes;
450     }
451 
452     /**
453      * {@inheritDoc}
454      */
455     public List<DemandType> selectNoDateEndDemandTypes( Plugin plugin )
456     {
457         List<DemandType> listDemandTypes = new ArrayList<DemandType>( );
458 
459         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_NO_DATE_END_DEMAND_TYPES, plugin );
460         daoUtil.executeQuery( );
461 
462         while ( daoUtil.next( ) )
463         {
464             int nIndex = 1;
465             DemandType/business/demand/DemandType.html#DemandType">DemandType demandType = new DemandType( );
466             demandType.setIdDemandType( daoUtil.getInt( nIndex++ ) );
467             demandType.setLabel( daoUtil.getString( nIndex++ ) );
468             demandType.setUrlResource( daoUtil.getString( nIndex++ ) );
469             demandType.setUrlInfo( daoUtil.getString( nIndex++ ) );
470             demandType.setUrlContact( daoUtil.getString( nIndex++ ) );
471             demandType.setOrder( daoUtil.getInt( nIndex++ ) );
472             demandType.setIdCategory( daoUtil.getInt( nIndex++ ) );
473             demandType.setDateBegin( daoUtil.getDate( nIndex++ ) );
474             demandType.setDateEnd( daoUtil.getDate( nIndex++ ) );
475             demandType.setWorkgroup( daoUtil.getString( nIndex++ ) );
476             demandType.setRole( daoUtil.getString( nIndex++ ) );
477             demandType.setTarget( TargetEnum.getTarget( daoUtil.getInt( nIndex++ ) ) );
478             demandType.setUrlDelete( daoUtil.getString( nIndex++ ) );
479             demandType.setIncludeIdCrmUser( daoUtil.getBoolean( nIndex++ ) );
480             demandType.setNeedAuthentication( daoUtil.getBoolean( nIndex++ ) );
481             demandType.setNeedValidation( daoUtil.getBoolean( nIndex++ ) );
482 
483             listDemandTypes.add( demandType );
484         }
485 
486         daoUtil.free( );
487 
488         return listDemandTypes;
489     }
490 
491     /**
492      * Build the SQL query with filter
493      * 
494      * @param dtFilter
495      *            the filter
496      * @return a SQL query
497      */
498     private String buildSQLQuery( DemandTypeFilter dtFilter )
499     {
500         StringBuilder sbSQL = new StringBuilder( SQL_QUERY_SELECT_ALL );
501         int nIndex = 1;
502 
503         if ( dtFilter.containsLabel( ) )
504         {
505             nIndex = addSQLWhereOr( dtFilter.getIsWideSearch( ), sbSQL, nIndex );
506             sbSQL.append( SQL_FILTER_LABEL );
507         }
508 
509         if ( dtFilter.containsUrlResource( ) )
510         {
511             nIndex = addSQLWhereOr( dtFilter.getIsWideSearch( ), sbSQL, nIndex );
512             sbSQL.append( SQL_FILTER_URL_RESOURCE );
513         }
514 
515         if ( dtFilter.containsIdCategory( ) )
516         {
517             nIndex = addSQLWhereOr( dtFilter.getIsWideSearch( ), sbSQL, nIndex );
518             sbSQL.append( SQL_FILTER_ID_CATEGORY );
519         }
520 
521         if ( dtFilter.containsDateBegin( ) )
522         {
523             nIndex = addSQLWhereOr( dtFilter.getIsWideSearch( ), sbSQL, nIndex );
524             sbSQL.append( SQL_FILTER_DATE_BEGIN );
525             sbSQL.append( dtFilter.getOperatorDateBegin( ) );
526             sbSQL.append( QUESTION_MARK );
527         }
528 
529         if ( dtFilter.containsDateEnd( ) )
530         {
531             nIndex = addSQLWhereOr( dtFilter.getIsWideSearch( ), sbSQL, nIndex );
532             sbSQL.append( SQL_FILTER_DATE_END );
533             sbSQL.append( dtFilter.getOperatorDateEnd( ) );
534             sbSQL.append( QUESTION_MARK );
535         }
536 
537         if ( dtFilter.containsWorkgroup( ) )
538         {
539             nIndex = addSQLWhereOr( dtFilter.getIsWideSearch( ), sbSQL, nIndex );
540             sbSQL.append( SQL_FILTER_WORKGROUP_KEY );
541         }
542 
543         if ( dtFilter.containsRole( ) )
544         {
545             nIndex = addSQLWhereOr( dtFilter.getIsWideSearch( ), sbSQL, nIndex );
546             sbSQL.append( SQL_FILTER_ROLE_KEY );
547         }
548 
549         if ( dtFilter.containsOrder( ) )
550         {
551             nIndex = addSQLWhereOr( dtFilter.getIsWideSearch( ), sbSQL, nIndex );
552             sbSQL.append( SQL_FILTER_DEMAND_TYPE_ORDER );
553             sbSQL.append( dtFilter.getOperatorOrder( ) );
554             sbSQL.append( QUESTION_MARK );
555         }
556 
557         if ( dtFilter.containsUrlDelete( ) )
558         {
559             nIndex = addSQLWhereOr( dtFilter.getIsWideSearch( ), sbSQL, nIndex );
560             sbSQL.append( SQL_FILTER_URL_DELETE );
561         }
562 
563         return sbSQL.toString( );
564     }
565 
566     /**
567      * Add a <b>WHERE</b> or a <b>OR</b> depending of the index. <br/>
568      * <ul>
569      * <li>if <code>nIndex</code> == 1, then we add a <b>WHERE</b></li>
570      * <li>if <code>nIndex</code> != 1, then we add a <b>OR</b> or a <b>AND</b> depending of the wide search characteristic</li>
571      * </ul>
572      * 
573      * @param bIsWideSearch
574      *            true if it is a wide search, false otherwise
575      * @param sbSQL
576      *            the SQL query
577      * @param nIndex
578      *            the index
579      * @return the new index
580      */
581     private int addSQLWhereOr( boolean bIsWideSearch, StringBuilder sbSQL, int nIndex )
582     {
583         if ( nIndex == 1 )
584         {
585             sbSQL.append( SQL_WHERE );
586         }
587         else
588         {
589             sbSQL.append( bIsWideSearch ? SQL_OR : SQL_AND );
590         }
591 
592         return nIndex + 1;
593     }
594 
595     /**
596      * Set the filter values on the DAOUtil
597      * 
598      * @param dtFilter
599      *            the filter
600      * @param daoUtil
601      *            the DAOUtil
602      */
603     private void setFilterValues( DemandTypeFilter dtFilter, DAOUtil daoUtil )
604     {
605         int nIndex = 1;
606 
607         if ( dtFilter.containsLabel( ) )
608         {
609             daoUtil.setString( nIndex++, PERCENT + dtFilter.getLabel( ) + PERCENT );
610         }
611 
612         if ( dtFilter.containsUrlResource( ) )
613         {
614             daoUtil.setString( nIndex++, PERCENT + dtFilter.getUrlResource( ) + PERCENT );
615         }
616 
617         if ( dtFilter.containsIdCategory( ) )
618         {
619             daoUtil.setInt( nIndex++, dtFilter.getIdCategory( ) );
620         }
621 
622         if ( dtFilter.containsDateBegin( ) )
623         {
624             daoUtil.setDate( nIndex++, new Date( dtFilter.getDateBegin( ).getTime( ) ) );
625         }
626 
627         if ( dtFilter.containsDateEnd( ) )
628         {
629             daoUtil.setDate( nIndex++, new Date( dtFilter.getDateEnd( ).getTime( ) ) );
630         }
631 
632         if ( dtFilter.containsWorkgroup( ) )
633         {
634             // No '%' because the workgroup key is unique
635             daoUtil.setString( nIndex++, dtFilter.getWorkgroup( ) );
636         }
637 
638         if ( dtFilter.containsRole( ) )
639         {
640             // No '%' because the role key is unique
641             daoUtil.setString( nIndex++, dtFilter.getRole( ) );
642         }
643 
644         if ( dtFilter.containsOrder( ) )
645         {
646             daoUtil.setInt( nIndex++, dtFilter.getOrder( ) );
647         }
648 
649         if ( dtFilter.containsUrlDelete( ) )
650         {
651             daoUtil.setString( nIndex++, PERCENT + dtFilter.getUrlDelete( ) + PERCENT );
652         }
653     }
654 }