View Javadoc
1   /*
2    * Copyright (c) 2002-2020, 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.campagnebp.business;
35  
36  import fr.paris.lutece.portal.business.file.File;
37  import fr.paris.lutece.portal.business.file.FileHome;
38  import fr.paris.lutece.portal.service.plugin.Plugin;
39  import fr.paris.lutece.portal.service.util.AppLogService;
40  import fr.paris.lutece.util.sql.DAOUtil;
41  
42  import java.util.ArrayList;
43  import java.util.Collection;
44  import java.util.HashMap;
45  import java.util.List;
46  
47  /**
48   * This class provides Data Access methods for Campagne objects
49   */
50  
51  public final class CampagneDAO implements ICampagneDAO
52  {
53      // Constants
54      private static final String SQL_QUERY_NEW_PK = "SELECT max( id_campagne ) FROM ideation_campagnes";
55      private static final String SQL_QUERY_SELECT = "SELECT id_campagne, code_campagne, title, description, active, code_moderation_type, moderation_duration FROM ideation_campagnes WHERE id_campagne = ?";
56      private static final String SQL_QUERY_INSERT = "INSERT INTO ideation_campagnes ( id_campagne, code_campagne, title, description, active, code_moderation_type, moderation_duration ) VALUES ( ?, ?, ?, ?, ?, ?, ? ) ";
57      private static final String SQL_QUERY_DELETE = "DELETE FROM ideation_campagnes WHERE id_campagne = ? ";
58      private static final String SQL_QUERY_UPDATE = "UPDATE ideation_campagnes SET id_campagne = ?, code_campagne = ?, title = ?, description = ?, active = ?, code_moderation_type = ?, moderation_duration = ? WHERE id_campagne = ?";
59      private static final String SQL_QUERY_SELECTALL = "SELECT id_campagne, code_campagne, title, description, active, code_moderation_type, moderation_duration FROM ideation_campagnes";
60      private static final String SQL_QUERY_SELECTALL_ID = "SELECT id_campagne FROM ideation_campagnes";
61      private static final String SQL_QUERY_SELECT_LAST_CAMPAGNE = "SELECT id_campagne, code_campagne, title, description, active, code_moderation_type, moderation_duration "
62              + "FROM ideation_campagnes WHERE code_campagne = ( SELECT MAX( code_campagne ) FROM ideation_campagnes );";
63  
64      // Queries for image list
65      private static final String SQL_QUERY_NEW_PK_IMAGE = "SELECT max( id_campagne_image ) FROM ideation_campagnes_images";
66      private static final String SQL_QUERY_INSERT_IMAGE = "INSERT INTO ideation_campagnes_images (id_campagne_image, code_campagne, id_file ) values ( ?,  ? , ? )";
67      private static final String SQL_QUERY_SELECT_IMAGES = "SELECT id_campagne_image, code_campagne, id_file from ideation_campagnes_images WHERE code_campagne = ?";
68      private static final String SQL_QUERY_DELETE_IMAGES = "DELETE FROM ideation_campagnes_images WHERE code_campagne = ?";
69      private static final String SQL_QUERY_SELECTALL_IMAGES = "SELECT id_campagne_image, code_campagne, id_file type FROM ideation_campagnes_images";
70  
71      /**
72       * Generates a new primary key
73       *
74       * @param plugin
75       *            The Plugin
76       * @return The new primary key
77       */
78      public int newPrimaryKey( Plugin plugin )
79      {
80          DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_PK, plugin );
81          daoUtil.executeQuery( );
82  
83          int nKey = 1;
84  
85          if ( daoUtil.next( ) )
86          {
87              nKey = daoUtil.getInt( 1 ) + 1;
88          }
89  
90          daoUtil.free( );
91  
92          return nKey;
93      }
94  
95      /**
96       * Generates a new primary key for CampagneImages
97       *
98       * @param plugin
99       *            The Plugin
100      * @return The new primary key
101      */
102     private int newPrimaryKeyImages( Plugin plugin )
103     {
104         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_PK_IMAGE, plugin );
105         daoUtil.executeQuery( );
106 
107         int nKey = 1;
108 
109         if ( daoUtil.next( ) )
110         {
111             nKey = daoUtil.getInt( 1 ) + 1;
112         }
113 
114         daoUtil.free( );
115 
116         return nKey;
117     }
118 
119     /**
120      * {@inheritDoc }
121      */
122     @Override
123     public void insert( Campagne campagne, Plugin plugin )
124     {
125         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, plugin );
126 
127         campagne.setId( newPrimaryKey( plugin ) );
128 
129         daoUtil.setInt( 1, campagne.getId( ) );
130         daoUtil.setString( 2, campagne.getCode( ) );
131         daoUtil.setString( 3, campagne.getTitle( ) );
132         daoUtil.setString( 4, campagne.getDescription( ) );
133         daoUtil.setBoolean( 5, campagne.getActive( ) );
134         daoUtil.setString( 6, campagne.getCodeModerationType( ) );
135         daoUtil.setInt( 7, campagne.getModerationDuration( ) );
136 
137         daoUtil.executeUpdate( );
138         daoUtil.free( );
139     }
140 
141     /**
142      * @param campagne
143      *            Campagne courante
144      * @param plugin
145      *            Plugin courant
146      */
147     public void insertImages( Campagne campagne, Plugin plugin )
148     {
149         for ( File image : campagne.getImgs( ) )
150         {
151             DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT_IMAGE, plugin );
152 
153             daoUtil.setInt( 1, newPrimaryKeyImages( plugin ) );
154             daoUtil.setString( 2, campagne.getCode( ) );
155             daoUtil.setInt( 3, image.getIdFile( ) );
156 
157             daoUtil.executeUpdate( );
158             daoUtil.free( );
159         }
160     }
161 
162     /**
163      * {@inheritDoc }
164      */
165     @Override
166     public Campagne load( int nKey, Plugin plugin )
167     {
168         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT, plugin );
169         daoUtil.setInt( 1, nKey );
170         daoUtil.executeQuery( );
171 
172         Campagne campagne = null;
173 
174         if ( daoUtil.next( ) )
175         {
176             campagne = getRow( daoUtil );
177         }
178 
179         daoUtil.free( );
180         loadImagesIds( campagne, plugin );
181         return campagne;
182     }
183 
184     private void loadImagesIds( Campagne campagne, Plugin plugin )
185     {
186         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_IMAGES, plugin );
187         daoUtil.setString( 1, campagne.getCode( ) );
188         daoUtil.executeQuery( );
189         List<File> listImgs = new ArrayList<File>( );
190         while ( daoUtil.next( ) )
191         {
192             listImgs.add( FileHome.findByPrimaryKey( daoUtil.getInt( 3 ) ) );
193         }
194         daoUtil.free( );
195         campagne.setImgs( listImgs );
196     }
197 
198     /**
199      * {@inheritDoc }
200      */
201     @Override
202     public void delete( int nKey, Plugin plugin )
203     {
204         deleteImages( nKey, plugin );
205         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin );
206         daoUtil.setInt( 1, nKey );
207         daoUtil.executeUpdate( );
208         daoUtil.free( );
209     }
210 
211     private void deleteImages( int nKey, Plugin plugin )
212     {
213         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_IMAGES, plugin );
214         daoUtil.setInt( 1, nKey );
215         daoUtil.executeUpdate( );
216         daoUtil.free( );
217     }
218 
219     /**
220      * {@inheritDoc }
221      */
222     @Override
223     public void store( Campagne campagne, Plugin plugin )
224     {
225         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin );
226 
227         daoUtil.setInt( 1, campagne.getId( ) );
228         daoUtil.setString( 2, campagne.getCode( ) );
229         daoUtil.setString( 3, campagne.getTitle( ) );
230         daoUtil.setString( 4, campagne.getDescription( ) );
231         daoUtil.setBoolean( 5, campagne.getActive( ) );
232         daoUtil.setString( 6, campagne.getCodeModerationType( ) );
233         daoUtil.setInt( 7, campagne.getModerationDuration( ) );
234         daoUtil.setInt( 8, campagne.getId( ) );
235 
236         daoUtil.executeUpdate( );
237         daoUtil.free( );
238 
239         deleteImages( campagne.getId( ), plugin );
240         insertImages( campagne, plugin );
241     }
242 
243     /**
244      * Returns the campain the code of which is the SQL 'max' (Ex : if 6 campains 'B0' - 'C' - 'D' - 'G0' - 'GA' - 'G', returns campain 'GA').
245      *
246      * {@inheritDoc }
247      */
248     @Override
249     public Campagne selectLastCampagne( Plugin plugin )
250     {
251         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_LAST_CAMPAGNE, plugin );
252         daoUtil.executeQuery( );
253 
254         Campagne campagne = null;
255 
256         if ( daoUtil.next( ) )
257         {
258             campagne = getRow( daoUtil );
259         }
260 
261         daoUtil.free( );
262         loadImagesIds( campagne, plugin );
263         return campagne;
264     }
265 
266     /**
267      * {@inheritDoc }
268      */
269     @Override
270     public Collection<Campagne> selectCampagnesList( Plugin plugin )
271     {
272         HashMap<String, Campagne> campagneMap = new HashMap<String, Campagne>( );
273         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL, plugin );
274         daoUtil.executeQuery( );
275 
276         while ( daoUtil.next( ) )
277         {
278             Campagne campagne = getRow( daoUtil );
279             campagneMap.put( campagne.getCode( ), campagne );
280             campagne.setImgs( new ArrayList<File>( ) );
281         }
282 
283         daoUtil.free( );
284 
285         daoUtil = new DAOUtil( SQL_QUERY_SELECTALL_IMAGES, plugin );
286         daoUtil.executeQuery( );
287         while ( daoUtil.next( ) )
288         {
289             String campagneCode = daoUtil.getString( 2 );
290             int fileId = daoUtil.getInt( 3 );
291             Campagne campagne = campagneMap.get( campagneCode );
292             if ( campagne != null )
293             {
294                 campagne.getImgs( ).add( FileHome.findByPrimaryKey( fileId ) );
295             }
296             else
297             {
298                 AppLogService.info( "Ideation, ideation_campagne_images orphaned file: " + campagneCode + "," + fileId );
299             }
300         }
301         ArrayList<Campagne> result = new ArrayList<Campagne>( campagneMap.values( ) );
302         daoUtil.free( );
303         return result;
304     }
305 
306     /**
307      * {@inheritDoc }
308      */
309     @Override
310     public Collection<Integer> selectIdCampagnesList( Plugin plugin )
311     {
312         Collection<Integer> campagneList = new ArrayList<Integer>( );
313         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL_ID, plugin );
314         daoUtil.executeQuery( );
315 
316         while ( daoUtil.next( ) )
317         {
318             campagneList.add( daoUtil.getInt( 1 ) );
319         }
320 
321         daoUtil.free( );
322         return campagneList;
323     }
324 
325     private Campagne getRow( DAOUtil daoUtil )
326     {
327         int nCpt = 1;
328         Campagnempagnebp/business/Campagne.html#Campagne">Campagne campagne = new Campagne( );
329         campagne.setId( daoUtil.getInt( nCpt++ ) );
330         campagne.setCode( daoUtil.getString( nCpt++ ) );
331         campagne.setTitle( daoUtil.getString( nCpt++ ) );
332         campagne.setDescription( daoUtil.getString( nCpt++ ) );
333         campagne.setActive( daoUtil.getBoolean( nCpt++ ) );
334         campagne.setCodeModerationType( daoUtil.getString( nCpt++ ) );
335         campagne.setModerationDuration( daoUtil.getInt( nCpt++ ) );
336         return campagne;
337     }
338 
339 }