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, scope 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, scope ) 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 = ?, scope = ? WHERE id_campagne = ?";
59      private static final String SQL_QUERY_SELECTALL = "SELECT id_campagne, code_campagne, title, description, active, code_moderation_type, moderation_duration, scope 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, scope "
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         daoUtil.setString( 8, campagne.getScope( ) );
137 
138         daoUtil.executeUpdate( );
139         daoUtil.free( );
140     }
141 
142     /**
143      * @param campagne
144      *            Campagne courante
145      * @param plugin
146      *            Plugin courant
147      */
148     public void insertImages( Campagne campagne, Plugin plugin )
149     {
150         for ( File image : campagne.getImgs( ) )
151         {
152             DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT_IMAGE, plugin );
153 
154             daoUtil.setInt( 1, newPrimaryKeyImages( plugin ) );
155             daoUtil.setString( 2, campagne.getCode( ) );
156             daoUtil.setInt( 3, image.getIdFile( ) );
157 
158             daoUtil.executeUpdate( );
159             daoUtil.free( );
160         }
161     }
162 
163     /**
164      * {@inheritDoc }
165      */
166     @Override
167     public Campagne load( int nKey, Plugin plugin )
168     {
169         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT, plugin );
170         daoUtil.setInt( 1, nKey );
171         daoUtil.executeQuery( );
172 
173         Campagne campagne = null;
174 
175         if ( daoUtil.next( ) )
176         {
177             campagne = getRow( daoUtil );
178         }
179 
180         daoUtil.free( );
181         loadImagesIds( campagne, plugin );
182         return campagne;
183     }
184 
185     private void loadImagesIds( Campagne campagne, Plugin plugin )
186     {
187         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_IMAGES, plugin );
188         daoUtil.setString( 1, campagne.getCode( ) );
189         daoUtil.executeQuery( );
190         List<File> listImgs = new ArrayList<File>( );
191         while ( daoUtil.next( ) )
192         {
193             listImgs.add( FileHome.findByPrimaryKey( daoUtil.getInt( 3 ) ) );
194         }
195         daoUtil.free( );
196         campagne.setImgs( listImgs );
197     }
198 
199     /**
200      * {@inheritDoc }
201      */
202     @Override
203     public void delete( int nKey, Plugin plugin )
204     {
205         deleteImages( nKey, plugin );
206         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin );
207         daoUtil.setInt( 1, nKey );
208         daoUtil.executeUpdate( );
209         daoUtil.free( );
210     }
211 
212     private void deleteImages( int nKey, Plugin plugin )
213     {
214         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_IMAGES, plugin );
215         daoUtil.setInt( 1, nKey );
216         daoUtil.executeUpdate( );
217         daoUtil.free( );
218     }
219 
220     /**
221      * {@inheritDoc }
222      */
223     @Override
224     public void store( Campagne campagne, Plugin plugin )
225     {
226         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin );
227 
228         daoUtil.setInt( 1, campagne.getId( ) );
229         daoUtil.setString( 2, campagne.getCode( ) );
230         daoUtil.setString( 3, campagne.getTitle( ) );
231         daoUtil.setString( 4, campagne.getDescription( ) );
232         daoUtil.setBoolean( 5, campagne.getActive( ) );
233         daoUtil.setString( 6, campagne.getCodeModerationType( ) );
234         daoUtil.setInt( 7, campagne.getModerationDuration( ) );
235         daoUtil.setString( 8, campagne.getScope( ) );
236         daoUtil.setInt( 9, campagne.getId( ) );
237 
238         daoUtil.executeUpdate( );
239         daoUtil.free( );
240 
241         deleteImages( campagne.getId( ), plugin );
242         insertImages( campagne, plugin );
243     }
244 
245     /**
246      * Returns the campain the code of which is the SQL 'max' (Ex : if 6 campains 'B0' - 'C' - 'D' - 'G0' - 'GA' - 'G', returns campain 'GA').
247      * 
248      * {@inheritDoc }
249      */
250     @Override
251     public Campagne selectLastCampagne( Plugin plugin )
252     {
253         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_LAST_CAMPAGNE, plugin );
254         daoUtil.executeQuery( );
255 
256         Campagne campagne = null;
257 
258         if ( daoUtil.next( ) )
259         {
260             campagne = getRow( daoUtil );
261         }
262 
263         daoUtil.free( );
264         loadImagesIds( campagne, plugin );
265         return campagne;
266     }
267 
268     /**
269      * {@inheritDoc }
270      */
271     @Override
272     public Collection<Campagne> selectCampagnesList( Plugin plugin )
273     {
274         HashMap<String, Campagne> campagneMap = new HashMap<String, Campagne>( );
275         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL, plugin );
276         daoUtil.executeQuery( );
277 
278         while ( daoUtil.next( ) )
279         {
280             Campagne campagne = getRow( daoUtil );
281             campagneMap.put( campagne.getCode( ), campagne );
282             campagne.setImgs( new ArrayList<File>( ) );
283         }
284 
285         daoUtil.free( );
286 
287         daoUtil = new DAOUtil( SQL_QUERY_SELECTALL_IMAGES, plugin );
288         daoUtil.executeQuery( );
289         while ( daoUtil.next( ) )
290         {
291             String campagneCode = daoUtil.getString( 2 );
292             int fileId = daoUtil.getInt( 3 );
293             Campagne campagne = campagneMap.get( campagneCode );
294             if ( campagne != null )
295             {
296                 campagne.getImgs( ).add( FileHome.findByPrimaryKey( fileId ) );
297             }
298             else
299             {
300                 AppLogService.info( "Ideation, ideation_campagne_images orphaned file: " + campagneCode + "," + fileId );
301             }
302         }
303         ArrayList<Campagne> result = new ArrayList<Campagne>( campagneMap.values( ) );
304         daoUtil.free( );
305         return result;
306     }
307 
308     /**
309      * {@inheritDoc }
310      */
311     @Override
312     public Collection<Integer> selectIdCampagnesList( Plugin plugin )
313     {
314         Collection<Integer> campagneList = new ArrayList<Integer>( );
315         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL_ID, plugin );
316         daoUtil.executeQuery( );
317 
318         while ( daoUtil.next( ) )
319         {
320             campagneList.add( daoUtil.getInt( 1 ) );
321         }
322 
323         daoUtil.free( );
324         return campagneList;
325     }
326 
327     private Campagne getRow( DAOUtil daoUtil )
328     {
329         int nCpt = 1;
330         Campagnempagnebp/business/Campagne.html#Campagne">Campagne campagne = new Campagne( );
331         campagne.setId( daoUtil.getInt( nCpt++ ) );
332         campagne.setCode( daoUtil.getString( nCpt++ ) );
333         campagne.setTitle( daoUtil.getString( nCpt++ ) );
334         campagne.setDescription( daoUtil.getString( nCpt++ ) );
335         campagne.setActive( daoUtil.getBoolean( nCpt++ ) );
336         campagne.setCodeModerationType( daoUtil.getString( nCpt++ ) );
337         campagne.setModerationDuration( daoUtil.getInt( nCpt++ ) );
338         campagne.setScope( daoUtil.getString( nCpt++ ) );
339         return campagne;
340     }
341 
342 }