View Javadoc
1   /*
2    * Copyright (c) 2002-2022, City of 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.unittree.business.unit;
35  
36  import java.util.ArrayList;
37  import java.util.List;
38  
39  import fr.paris.lutece.plugins.unittree.business.unit.Unit.TypeUnit;
40  import fr.paris.lutece.portal.service.plugin.Plugin;
41  import fr.paris.lutece.util.sql.DAOUtil;
42  
43  /**
44   *
45   * UnitDAO
46   *
47   */
48  public class UnitDAO implements IUnitDAO
49  {
50      private static final String SQL_WHERE = " WHERE ";
51      private static final String SQL_AND = " AND ";
52      private static final String SQL_OR = " OR ";
53      private static final String SQL_ORDER_BY_LABEL_ASC = " ORDER BY label ASC ";
54      private static final String SQL_FILTER_ID_PARENT = " id_parent = ? ";
55      private static final String SQL_FILTER_LABEL = " label = ? ";
56      private static final String SQL_FILTER_DESCRIPTION = " description = ? ";
57  
58      // Table unittree_unit
59      private static final String SQL_QUERY_NEW_PK = " SELECT max( id_unit ) FROM unittree_unit ";
60      private static final String SQL_QUERY_INSERT = " INSERT INTO unittree_unit ( id_unit, id_parent, label, description, sirensiret, numero, voie, codepostal, ville, pays, typeunit ) VALUES ( ?, ?, ?, ?,?, ?, ?, ?, ?, ?, ?) ";
61      private static final String SQL_QUERY_SELECT = " SELECT id_unit, id_parent, label, description,sirensiret, numero, voie, codepostal, ville, pays, typeunit FROM unittree_unit WHERE id_unit = ? ";
62      private static final String SQL_QUERY_SELECT_ALL = " SELECT id_unit, id_parent, label, description, typeunit FROM unittree_unit ";
63      private static final String SQL_QUERY_SELECT_BY_ID_USER = " SELECT u.id_unit, u.id_parent, u.label, u.description,u.sirensiret, u.numero, u.voie, u.codepostal, u.ville, u.pays, u.typeunit "
64              + " FROM unittree_unit u INNER JOIN unittree_unit_user uu ON u.id_unit = uu.id_unit" + " WHERE uu.id_user = ? ";
65      private static final String SQL_QUERY_DELETE = " DELETE FROM unittree_unit WHERE id_unit = ? ";
66      private static final String SQL_QUERY_UPDATE = " UPDATE unittree_unit SET label = ?, description = ?, sirensiret = ?, numero = ?, voie = ?, codepostal = ?, ville = ?, pays = ? WHERE id_unit = ? ";
67      private static final String SQL_QUERY_HAS_SUB_UNIT = " SELECT id_unit FROM unittree_unit WHERE id_parent = ? ";
68      private static final String SQL_QUERY_SELECT_BY_SECTOR = " SELECT uu.id_unit, uu.id_parent, uu.label, uu.description "
69              + " FROM unittree_unit_sector uus INNER JOIN unittree_unit uu ON uus.id_unit = uu.id_unit"
70              + " INNER JOIN unittree_sector us ON us.id_sector = uus.id_sector WHERE us.id_sector = ? ";
71      private static final String SQL_QUERY_SELECT_NO_CHILDREN = " SELECT id_unit, id_parent, label, description "
72              + " FROM unittree_unit WHERE id_unit NOT IN(SELECT id_parent FROM unittree_unit) ";
73  
74      private static final String SQL_QUERY_SELECTED_UNIT_WITHOUT_CHILDREN = "AND uu.id_unit NOT IN (SELECT  uu.id_parent FROM unittree_unit_sector uus INNER JOIN unittree_unit uu ON uus.id_unit = uu.id_unit INNER JOIN unittree_sector us ON us.id_sector = uus.id_sector WHERE us.id_sector = ?)";
75      // Table unittree_unit_user
76      private static final String SQL_QUERY_ADD_USER_TO_UNIT = " INSERT INTO unittree_unit_user ( id_unit, id_user ) VALUES ( ?, ? ) ";
77      private static final String SQL_QUERY_SELECT_IDS_USER = " SELECT id_user FROM unittree_unit_user WHERE id_unit = ? ";
78      private static final String SQL_QUERY_SELECT_ALL_IDS_USER = " SELECT id_user FROM unittree_unit_user ";
79      private static final String SQL_QUERY_REMOVE_USER_FROM_UNIT = " DELETE FROM unittree_unit_user WHERE id_user = ? AND id_unit = ? ";
80      private static final String SQL_QUERY_REMOVE_USERS_FROM_UNIT = " DELETE FROM unittree_unit_user WHERE id_unit = ? ";
81      private static final String SQL_QUERY_CHECK_USER = " SELECT id_unit FROM unittree_unit_user WHERE id_user = ? AND id_unit = ? ";
82      private static final String SQL_QUERY_UPDATE_UNIT_PARENT = " UPDATE unittree_unit SET id_parent = ? WHERE id_unit = ? ";
83  
84      // Table unittree_sector
85  
86      private static final String SQL_QUERY_SELECT_ID_NAME = " SELECT id_sector, name FROM unittree_sector order by name ";
87  
88      /**
89       * {@inheritDoc}
90       */
91      @Override
92      public int newPrimaryKey( Plugin plugin )
93      {
94          DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_PK, plugin );
95          daoUtil.executeQuery( );
96  
97          int nKey = 1;
98  
99          if ( daoUtil.next( ) )
100         {
101             nKey = daoUtil.getInt( 1 ) + 1;
102         }
103 
104         daoUtil.free( );
105 
106         return nKey;
107     }
108 
109     /**
110      * {@inheritDoc}
111      */
112     @Override
113     public synchronized int insert( Unit unit, Plugin plugin )
114     {
115         int nIndex = 1;
116         int nIdUnit = newPrimaryKey( plugin );
117         unit.setIdUnit( nIdUnit );
118 
119         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, plugin );
120         daoUtil.setInt( nIndex++, unit.getIdUnit( ) );
121         daoUtil.setInt( nIndex++, unit.getIdParent( ) );
122         daoUtil.setString( nIndex++, unit.getLabel( ) );
123         daoUtil.setString( nIndex++, unit.getDescription( ) );
124         daoUtil.setString( nIndex++, unit.getSirensiret( ) );
125         daoUtil.setString( nIndex++, unit.getNumero( ) );
126         daoUtil.setString( nIndex++, unit.getVoie( ) );
127         daoUtil.setString( nIndex++, unit.getCodepostal( ) );
128         daoUtil.setString( nIndex++, unit.getVille( ) );
129         daoUtil.setString( nIndex++, unit.getPays( ) );
130         daoUtil.setString( nIndex, unit.getTypeunit( ).name( ) );
131 
132         daoUtil.executeUpdate( );
133         daoUtil.free( );
134 
135         return nIdUnit;
136     }
137 
138     /**
139      * {@inheritDoc}
140      */
141     @Override
142     public Unit load( int nIdUnit, Plugin plugin )
143     {
144         Unit unit = null;
145         int nIndex = 1;
146         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT, plugin );
147         daoUtil.setInt( nIndex, nIdUnit );
148         daoUtil.executeQuery( );
149 
150         if ( daoUtil.next( ) )
151         {
152             nIndex = 1;
153 
154             unit = new Unit( );
155             unit.setIdUnit( daoUtil.getInt( nIndex++ ) );
156             unit.setIdParent( daoUtil.getInt( nIndex++ ) );
157             unit.setLabel( daoUtil.getString( nIndex++ ) );
158             unit.setDescription( daoUtil.getString( nIndex++ ) );
159             unit.setSirensiret( daoUtil.getString( nIndex++ ) );
160             unit.setNumero( daoUtil.getString( nIndex++ ) );
161             unit.setVoie( daoUtil.getString( nIndex++ ) );
162             unit.setCodepostal( daoUtil.getString( nIndex++ ) );
163             unit.setVille( daoUtil.getString( nIndex++ ) );
164             unit.setPays( daoUtil.getString( nIndex++ ) );
165             String type = daoUtil.getString( nIndex );
166             if ( type != null && !"".equals( type.trim( ) ) )
167             {
168                 unit.setTypeunit( TypeUnit.valueOf( type ) );
169             }
170         }
171 
172         daoUtil.free( );
173 
174         return unit;
175     }
176 
177     /**
178      * {@inheritDoc}
179      */
180     @Override
181     public List<Unit> selectByIdUser( int nIdUser, Plugin plugin )
182     {
183         List<Unit> listUnits = new ArrayList<Unit>( );
184         int nIndex = 1;
185         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_ID_USER, plugin );
186         daoUtil.setInt( nIndex, nIdUser );
187         daoUtil.executeQuery( );
188 
189         while ( daoUtil.next( ) )
190         {
191             nIndex = 1;
192 
193             Unit/plugins/unittree/business/unit/Unit.html#Unit">Unit unit = new Unit( );
194             unit.setIdUnit( daoUtil.getInt( nIndex++ ) );
195             unit.setIdParent( daoUtil.getInt( nIndex++ ) );
196             unit.setLabel( daoUtil.getString( nIndex++ ) );
197             unit.setDescription( daoUtil.getString( nIndex++ ) );
198             unit.setSirensiret( daoUtil.getString( nIndex++ ) );
199             unit.setNumero( daoUtil.getString( nIndex++ ) );
200             unit.setVoie( daoUtil.getString( nIndex++ ) );
201             unit.setCodepostal( daoUtil.getString( nIndex++ ) );
202             unit.setVille( daoUtil.getString( nIndex++ ) );
203             unit.setPays( daoUtil.getString( nIndex++ ) );
204             String type = daoUtil.getString( nIndex );
205             if ( type != null && !"".equals( type.trim( ) ) )
206             {
207                 unit.setTypeunit( TypeUnit.valueOf( type ) );
208             }
209             listUnits.add( unit );
210         }
211 
212         daoUtil.free( );
213 
214         return listUnits;
215     }
216 
217     /**
218      * {@inheritDoc}
219      */
220     @Override
221     public List<Unit> selectAll( Plugin plugin )
222     {
223         List<Unit> listUnits = new ArrayList<Unit>( );
224         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ALL + SQL_ORDER_BY_LABEL_ASC, plugin );
225         daoUtil.executeQuery( );
226 
227         while ( daoUtil.next( ) )
228         {
229             int nIndex = 1;
230 
231             Unit/plugins/unittree/business/unit/Unit.html#Unit">Unit unit = new Unit( );
232             unit.setIdUnit( daoUtil.getInt( nIndex++ ) );
233             unit.setIdParent( daoUtil.getInt( nIndex++ ) );
234             unit.setLabel( daoUtil.getString( nIndex++ ) );
235             unit.setDescription( daoUtil.getString( nIndex++ ) );
236             unit.setTypeunit( TypeUnit.valueOf( daoUtil.getString( nIndex ) ) );
237             listUnits.add( unit );
238         }
239 
240         daoUtil.free( );
241 
242         return listUnits;
243     }
244 
245     /**
246      * {@inheritDoc}
247      */
248     @Override
249     public void remove( int nIdUnit, Plugin plugin )
250     {
251         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin );
252         daoUtil.setInt( 1, nIdUnit );
253         daoUtil.executeUpdate( );
254         daoUtil.free( );
255     }
256 
257     /**
258      * {@inheritDoc}
259      */
260     @Override
261     public boolean hasSubUnits( int nIdUnit, Plugin plugin )
262     {
263         boolean bHasSubUnits = false;
264         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_HAS_SUB_UNIT, plugin );
265         daoUtil.setInt( 1, nIdUnit );
266         daoUtil.executeQuery( );
267 
268         if ( daoUtil.next( ) )
269         {
270             bHasSubUnits = true;
271         }
272 
273         daoUtil.free( );
274 
275         return bHasSubUnits;
276     }
277 
278     /**
279      * {@inheritDoc}
280      */
281     @Override
282     public void removeUserFromUnit( int nIdUser, int nIdUnit, Plugin plugin )
283     {
284         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_REMOVE_USER_FROM_UNIT, plugin );
285         daoUtil.setInt( 1, nIdUser );
286         daoUtil.setInt( 2, nIdUnit );
287         daoUtil.executeUpdate( );
288         daoUtil.free( );
289     }
290 
291     /**
292      * {@inheritDoc}
293      */
294     @Override
295     public void removeUsersFromUnit( int nIdUnit, Plugin plugin )
296     {
297         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_REMOVE_USERS_FROM_UNIT, plugin );
298         daoUtil.setInt( 1, nIdUnit );
299         daoUtil.executeUpdate( );
300         daoUtil.free( );
301     }
302 
303     /**
304      * {@inheritDoc}
305      */
306     @Override
307     public void update( Unit unit, Plugin plugin )
308     {
309         int nIndex = 1;
310         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin );
311         daoUtil.setString( nIndex++, unit.getLabel( ) );
312         daoUtil.setString( nIndex++, unit.getDescription( ) );
313         daoUtil.setString( nIndex++, unit.getSirensiret( ) );
314         daoUtil.setString( nIndex++, unit.getNumero( ) );
315         daoUtil.setString( nIndex++, unit.getVoie( ) );
316         daoUtil.setString( nIndex++, unit.getCodepostal( ) );
317         daoUtil.setString( nIndex++, unit.getVille( ) );
318         daoUtil.setString( nIndex++, unit.getPays( ) );
319 
320         daoUtil.setInt( nIndex, unit.getIdUnit( ) );
321 
322         daoUtil.executeUpdate( );
323         daoUtil.free( );
324     }
325 
326     /**
327      * {@inheritDoc}
328      */
329     @Override
330     public List<Unit> selectByFilter( UnitFilter cmFilter, Plugin plugin )
331     {
332         List<Unit> listUnits = new ArrayList<Unit>( );
333         StringBuilder sbSQL = new StringBuilder( buildSQLQuery( cmFilter ) );
334 
335         DAOUtil daoUtil = new DAOUtil( sbSQL.toString( ), plugin );
336         setFilterValues( cmFilter, daoUtil );
337         daoUtil.executeQuery( );
338 
339         while ( daoUtil.next( ) )
340         {
341             int nIndex = 1;
342             Unit/plugins/unittree/business/unit/Unit.html#Unit">Unit unit = new Unit( );
343             unit.setIdUnit( daoUtil.getInt( nIndex++ ) );
344             unit.setIdParent( daoUtil.getInt( nIndex++ ) );
345             unit.setLabel( daoUtil.getString( nIndex++ ) );
346             unit.setDescription( daoUtil.getString( nIndex ) );
347 
348             listUnits.add( unit );
349         }
350 
351         daoUtil.free( );
352 
353         return listUnits;
354     }
355 
356     /**
357      * {@inheritDoc}
358      */
359     @Override
360     public List<Integer> selectAllIdsUser( Plugin plugin )
361     {
362         List<Integer> listIdUsers = new ArrayList<Integer>( );
363         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ALL_IDS_USER, plugin );
364         daoUtil.executeQuery( );
365 
366         while ( daoUtil.next( ) )
367         {
368             listIdUsers.add( daoUtil.getInt( 1 ) );
369         }
370 
371         daoUtil.free( );
372 
373         return listIdUsers;
374     }
375 
376     /**
377      * {@inheritDoc}
378      */
379     @Override
380     public List<Integer> selectIdsUser( int nIdUnit, Plugin plugin )
381     {
382         List<Integer> listIdUsers = new ArrayList<Integer>( );
383         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_IDS_USER, plugin );
384         daoUtil.setInt( 1, nIdUnit );
385         daoUtil.executeQuery( );
386 
387         while ( daoUtil.next( ) )
388         {
389             listIdUsers.add( daoUtil.getInt( 1 ) );
390         }
391 
392         daoUtil.free( );
393 
394         return listIdUsers;
395     }
396 
397     /**
398      * {@inheritDoc}
399      */
400     @Override
401     public synchronized void addUserToUnit( int nIdUnit, int nIdUser, Plugin plugin )
402     {
403         int nIndex = 1;
404 
405         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_ADD_USER_TO_UNIT, plugin );
406         daoUtil.setInt( nIndex++, nIdUnit );
407         daoUtil.setInt( nIndex, nIdUser );
408 
409         daoUtil.executeUpdate( );
410         daoUtil.free( );
411     }
412 
413     /**
414      * {@inheritDoc}
415      */
416     @Override
417     public boolean isUserInUnit( int nIdUser, int nIdUnit, Plugin plugin )
418     {
419         boolean bIsUserInAnUnit = false;
420         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_CHECK_USER, plugin );
421         daoUtil.setInt( 1, nIdUser );
422         daoUtil.setInt( 2, nIdUnit );
423         daoUtil.executeQuery( );
424 
425         if ( daoUtil.next( ) )
426         {
427             bIsUserInAnUnit = true;
428         }
429 
430         daoUtil.free( );
431 
432         return bIsUserInAnUnit;
433     }
434 
435     /**
436      * {@inheritDoc}
437      */
438     @Override
439     public List<Unit> findBySectorId( int nIdSector, Plugin plugin )
440     {
441         List<Unit> listUnits = new ArrayList<Unit>( );
442         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_SECTOR + SQL_ORDER_BY_LABEL_ASC, plugin );
443         daoUtil.setInt( 1, nIdSector );
444         daoUtil.executeQuery( );
445 
446         while ( daoUtil.next( ) )
447         {
448             int nIndex = 1;
449             Unit/plugins/unittree/business/unit/Unit.html#Unit">Unit unit = new Unit( );
450             unit.setIdUnit( daoUtil.getInt( nIndex++ ) );
451             unit.setIdParent( daoUtil.getInt( nIndex++ ) );
452             unit.setLabel( daoUtil.getString( nIndex++ ) );
453             unit.setDescription( daoUtil.getString( nIndex++ ) );
454             listUnits.add( unit );
455         }
456 
457         daoUtil.free( );
458 
459         return listUnits;
460     }
461 
462     @Override
463     public List<Unit> findBySectorIdWithoutChildren( int nIdSector, Plugin plugin )
464     {
465         List<Unit> listUnits = new ArrayList<Unit>( );
466         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_SECTOR + SQL_QUERY_SELECTED_UNIT_WITHOUT_CHILDREN, plugin );
467         daoUtil.setInt( 1, nIdSector );
468         daoUtil.setInt( 2, nIdSector );
469         daoUtil.executeQuery( );
470 
471         while ( daoUtil.next( ) )
472         {
473             int nIndex = 1;
474             Unit/plugins/unittree/business/unit/Unit.html#Unit">Unit unit = new Unit( );
475             unit.setIdUnit( daoUtil.getInt( nIndex++ ) );
476             unit.setIdParent( daoUtil.getInt( nIndex++ ) );
477             unit.setLabel( daoUtil.getString( nIndex++ ) );
478             unit.setDescription( daoUtil.getString( nIndex++ ) );
479             listUnits.add( unit );
480         }
481 
482         daoUtil.free( );
483 
484         return listUnits;
485     }
486 
487     /**
488      * {@inheritDoc}
489      */
490     @Override
491     public List<Unit> getUnitWithNoChildren( Plugin plugin )
492     {
493         List<Unit> listUnits = new ArrayList<Unit>( );
494         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_NO_CHILDREN + SQL_ORDER_BY_LABEL_ASC, plugin );
495         daoUtil.executeQuery( );
496 
497         while ( daoUtil.next( ) )
498         {
499             int nIndex = 1;
500             Unit/plugins/unittree/business/unit/Unit.html#Unit">Unit unit = new Unit( );
501             unit.setIdUnit( daoUtil.getInt( nIndex++ ) );
502             unit.setLabel( daoUtil.getString( nIndex++ ) );
503             unit.setIdParent( daoUtil.getInt( nIndex++ ) );
504             unit.setDescription( daoUtil.getString( nIndex++ ) );
505             listUnits.add( unit );
506         }
507 
508         daoUtil.free( );
509 
510         return listUnits;
511     }
512 
513     /**
514      * {@inheritDoc}
515      */
516     @Override
517     public void updateParent( int nIdUnitToMove, int nIdNewParent, Plugin plugin )
518     {
519         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE_UNIT_PARENT, plugin );
520         daoUtil.setInt( 1, nIdNewParent );
521         daoUtil.setInt( 2, nIdUnitToMove );
522         daoUtil.executeUpdate( );
523         daoUtil.free( );
524     }
525 
526     // PRIVATE METHODS
527 
528     /**
529      * Build the SQL query with filter
530      * 
531      * @param uFilter
532      *            the filter
533      * @return a SQL query
534      */
535     private String buildSQLQuery( UnitFilter uFilter )
536     {
537         StringBuilder sbSQL = new StringBuilder( SQL_QUERY_SELECT_ALL );
538         int nIndex = 1;
539 
540         if ( uFilter.containsIdParent( ) )
541         {
542             nIndex = addSQLWhereOr( uFilter.isWideSearch( ), sbSQL, nIndex );
543             sbSQL.append( SQL_FILTER_ID_PARENT );
544         }
545 
546         if ( uFilter.containsLabel( ) )
547         {
548             nIndex = addSQLWhereOr( uFilter.isWideSearch( ), sbSQL, nIndex );
549             sbSQL.append( SQL_FILTER_LABEL );
550         }
551 
552         if ( uFilter.containsDescription( ) )
553         {
554             addSQLWhereOr( uFilter.isWideSearch( ), sbSQL, nIndex );
555             sbSQL.append( SQL_FILTER_DESCRIPTION );
556         }
557 
558         sbSQL.append( SQL_ORDER_BY_LABEL_ASC );
559 
560         return sbSQL.toString( );
561     }
562 
563     /**
564      * Add a <b>WHERE</b> or a <b>OR</b> depending of the index. <br/>
565      * <ul>
566      * <li>if <code>nIndex</code> == 1, then we add a <b>WHERE</b></li>
567      * <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>
568      * </ul>
569      * 
570      * @param bIsWideSearch
571      *            true if it is a wide search, false otherwise
572      * @param sbSQL
573      *            the SQL query
574      * @param nIndex
575      *            the index
576      * @return the new index
577      */
578     private int addSQLWhereOr( boolean bIsWideSearch, StringBuilder sbSQL, int nIndex )
579     {
580         if ( nIndex == 1 )
581         {
582             sbSQL.append( SQL_WHERE );
583         }
584         else
585         {
586             sbSQL.append( bIsWideSearch ? SQL_OR : SQL_AND );
587         }
588 
589         return nIndex + 1;
590     }
591 
592     /**
593      * Set the filter values on the DAOUtil
594      * 
595      * @param uFilter
596      *            the filter
597      * @param daoUtil
598      *            the DAOUtil
599      */
600     private void setFilterValues( UnitFilter uFilter, DAOUtil daoUtil )
601     {
602         int nIndex = 1;
603 
604         if ( uFilter.containsIdParent( ) )
605         {
606             daoUtil.setInt( nIndex++, uFilter.getIdParent( ) );
607         }
608 
609         if ( uFilter.containsLabel( ) )
610         {
611             daoUtil.setString( nIndex++, uFilter.getLabel( ) );
612         }
613 
614         if ( uFilter.containsDescription( ) )
615         {
616             daoUtil.setString( nIndex, uFilter.getDescription( ) );
617         }
618     }
619 
620     @Override
621     public List<Sector> selectAllSectors( Plugin plugin )
622     {
623         List<Sector> listSector = new ArrayList<Sector>( );
624         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ID_NAME, plugin );
625         daoUtil.executeQuery( );
626 
627         while ( daoUtil.next( ) )
628         {
629             int nIndex = 1;
630 
631             Sectorgins/unittree/business/unit/Sector.html#Sector">Sector sector = new Sector( );
632             sector.setIdSector( daoUtil.getInt( nIndex++ ) );
633             sector.setName( daoUtil.getString( nIndex++ ) );
634 
635             listSector.add( sector );
636         }
637 
638         daoUtil.free( );
639 
640         return listSector;
641     }
642 
643     @Override
644     public List<String> selectAllSectorsGeo( Plugin plugin )
645     {
646         List<String> listSectorGeo = new ArrayList<String>( );
647         DAOUtil daoUtil = new DAOUtil( "SELECT ST_AsGeoJSON(geom) FROM unittree_sector", plugin );
648         daoUtil.executeQuery( );
649 
650         while ( daoUtil.next( ) )
651         {
652             int nIndex = 1;
653             String geometry = daoUtil.getString( nIndex++ );
654             listSectorGeo.add( "{'type':'Feature','geometry': " + geometry.replace( '"', '\'' ) + "}" );
655         }
656 
657         daoUtil.free( );
658 
659         return listSectorGeo;
660     }
661 
662 }