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.announce.business;
35  
36  import fr.paris.lutece.portal.service.plugin.Plugin;
37  import fr.paris.lutece.util.ReferenceList;
38  import fr.paris.lutece.util.sql.DAOUtil;
39  
40  import java.util.ArrayList;
41  import java.util.List;
42  
43  /**
44   * the DAO class for category
45   */
46  public final class CategoryDAO implements ICategoryDAO
47  {
48      private static final String SQL_QUERY_NEWPK = "SELECT max( id_category ) FROM announce_category ";
49      private static final String SQL_QUERY_SELECT = "SELECT id_category, id_sector, label_category, display_price, price_mandatory, announces_validation, id_mailing_list, id_workflow, display_captcha FROM announce_category WHERE id_category = ? ";
50      private static final String SQL_QUERY_SELECTALL = "SELECT a.id_category, a.id_sector, a.label_category, b.label_sector FROM announce_category a, announce_sector b WHERE a.id_sector = b.id_sector ORDER BY a.id_sector, a.label_category";
51      private static final String SQL_QUERY_INSERT = "INSERT INTO announce_category ( id_category, id_sector, label_category, display_price, price_mandatory, announces_validation, id_mailing_list, id_workflow, display_captcha )  VALUES (?,?,?,?,?,?,?,?,?) ";
52      private static final String SQL_QUERY_DELETE = "DELETE FROM announce_category WHERE id_category = ? ";
53      private static final String SQL_QUERY_UPDATE = "UPDATE announce_category SET id_sector = ?, label_category = ?, display_price = ?, price_mandatory = ?, announces_validation = ?, id_mailing_list = ?, id_workflow = ?, display_captcha = ? WHERE id_category = ? ";
54      private static final String SQL_QUERY_COUNT_ANNOUNCES_FOR_CATEORY = "SELECT COUNT(*) FROM announce_announce WHERE id_category = ?";
55      private static final String SQL_QUERY_COUNT_PUBLISHED_ANNOUNCES_FOR_CATEORY = "SELECT COUNT(*) FROM announce_announce WHERE id_category = ? AND published = 1 AND suspended = 0 AND suspended_by_user = 0 ";
56      private static final String SQL_QUERY_COUNT_ENTRIES_FOR_CATEGORY = "SELECT COUNT(*) FROM announce_announce WHERE id_category = ?";
57      private static final String SQL_QUERY_SELECT_CATEGORIES_FOR_FIELD = "SELECT id_category, id_sector, label_category FROM announce_category WHERE id_sector = ? ORDER BY label_category";
58      private static final String SQL_QUERY_SELECT_CATEGORIES_REFERENCELIST = "SELECT id_category, label_category FROM announce_category";
59  
60      /**
61       * Generates a new primary key
62       * 
63       * @param plugin
64       *            The plugin
65       * @return The new primary key
66       */
67      public int newPrimaryKey( Plugin plugin )
68      {
69          int nKey = 1;
70          try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEWPK, plugin ) )
71          {
72              daoUtil.executeQuery( );
73  
74              if ( daoUtil.next( ) )
75              {
76                  nKey = daoUtil.getInt( 1 ) + 1;
77              }
78          }
79          return nKey;
80      }
81  
82      /**
83       * {@inheritDoc}
84       */
85      @Override
86      public void insert( Category category, Plugin plugin )
87      {
88          try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, plugin ) )
89          {
90              category.setId( newPrimaryKey( plugin ) );
91  
92              int nIndex = 1;
93              daoUtil.setInt( nIndex++, category.getId( ) );
94              daoUtil.setInt( nIndex++, category.getIdSector( ) );
95              daoUtil.setString( nIndex++, category.getLabel( ) );
96              daoUtil.setBoolean( nIndex++, category.getDisplayPrice( ) );
97              daoUtil.setBoolean( nIndex++, category.getPriceMandatory( ) );
98              daoUtil.setInt( nIndex++, category.getAnnouncesValidation( ) );
99              daoUtil.setInt( nIndex++, category.getIdMailingList( ) );
100             daoUtil.setBoolean( nIndex++, category.getDisplayCaptcha( ) );
101             daoUtil.setInt( nIndex, category.getIdWorkflow( ) );
102             daoUtil.executeUpdate( );
103         }
104     }
105 
106     @Override
107     public int copyCategory( Category category, Plugin plugin )
108     {
109         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, plugin ) )
110         {
111             category.setId( newPrimaryKey( plugin ) );
112 
113             int nIndex = 1;
114             daoUtil.setInt( nIndex++, category.getId( ) );
115             daoUtil.setInt( nIndex++, category.getIdSector( ) );
116             daoUtil.setString( nIndex++, category.getLabel( ) );
117             daoUtil.setBoolean( nIndex++, category.getDisplayPrice( ) );
118             daoUtil.setBoolean( nIndex++, category.getPriceMandatory( ) );
119             daoUtil.setInt( nIndex++, category.getAnnouncesValidation( ) );
120             daoUtil.setInt( nIndex++, category.getIdMailingList( ) );
121             daoUtil.setBoolean( nIndex++, category.getDisplayCaptcha( ) );
122             daoUtil.setInt( nIndex, category.getIdWorkflow( ) );
123             daoUtil.executeUpdate( );
124         }
125         return category.getId( );
126     }
127 
128     /**
129      * {@inheritDoc}
130      */
131     @Override
132     public Category load( int nCategoryId, Plugin plugin )
133     {
134         Category category = null;
135         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT, plugin ) )
136         {
137             daoUtil.setInt( 1, nCategoryId );
138             daoUtil.executeQuery( );
139 
140             if ( daoUtil.next( ) )
141             {
142                 category = new Category( );
143 
144                 int nIndex = 1;
145                 category.setId( daoUtil.getInt( nIndex++ ) );
146                 category.setIdSector( daoUtil.getInt( nIndex++ ) );
147                 category.setLabel( daoUtil.getString( nIndex++ ) );
148                 category.setDisplayPrice( daoUtil.getBoolean( nIndex++ ) );
149                 category.setPriceMandatory( daoUtil.getBoolean( nIndex++ ) );
150                 category.setAnnouncesValidation( daoUtil.getInt( nIndex++ ) );
151                 category.setIdMailingList( daoUtil.getInt( nIndex++ ) );
152                 category.setIdWorkflow( daoUtil.getInt( nIndex++ ) );
153                 category.setDisplayCaptcha( daoUtil.getBoolean( nIndex++ ) );
154                 category.setNumberAnnounces( countAnnouncesForCategory( category, plugin ) );
155             }
156         }
157         return category;
158     }
159 
160     /**
161      * {@inheritDoc}
162      */
163     @Override
164     public void delete( Category category, Plugin plugin )
165     {
166         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin ) )
167         {
168             daoUtil.setInt( 1, category.getId( ) );
169             daoUtil.executeUpdate( );
170         }
171     }
172 
173     /**
174      * {@inheritDoc}
175      */
176     @Override
177     public void store( Category category, Plugin plugin )
178     {
179         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin ) )
180         {
181             int nIndex = 1;
182             daoUtil.setInt( nIndex++, category.getIdSector( ) );
183             daoUtil.setString( nIndex++, category.getLabel( ) );
184             daoUtil.setBoolean( nIndex++, category.getDisplayPrice( ) );
185             daoUtil.setBoolean( nIndex++, category.getPriceMandatory( ) );
186             daoUtil.setInt( nIndex++, category.getAnnouncesValidation( ) );
187             daoUtil.setInt( nIndex++, category.getIdMailingList( ) );
188             daoUtil.setInt( nIndex++, category.getIdWorkflow( ) );
189             daoUtil.setBoolean( nIndex++, category.getDisplayCaptcha( ) );
190             daoUtil.setInt( nIndex, category.getId( ) );
191             daoUtil.executeUpdate( );
192         }
193     }
194 
195     /**
196      * {@inheritDoc}
197      */
198     @Override
199     public List<Category> selectAll( Plugin plugin )
200     {
201         List<Category> listCategories = new ArrayList<>( );
202         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL, plugin ) )
203         {
204             daoUtil.executeQuery( );
205 
206             while ( daoUtil.next( ) )
207             {
208                 Categorynounce/business/Category.html#Category">Category category = new Category( );
209                 category.setId( daoUtil.getInt( 1 ) );
210                 category.setIdSector( daoUtil.getInt( 2 ) );
211                 category.setLabel( daoUtil.getString( 3 ) );
212                 category.setLabelSector( daoUtil.getString( 4 ) );
213                 category.setNumberAnnounces( countAnnouncesForCategory( category, plugin ) );
214 
215                 listCategories.add( category );
216             }
217         }
218         return listCategories;
219     }
220 
221     /**
222      * Counts the number of annouces for a specified category
223      *
224      * @param plugin
225      *            The plugin
226      * @param category
227      *            The specified category
228      * @return The Number of announces
229      */
230     private int countAnnouncesForCategory( Category category, Plugin plugin )
231     {
232         int nNumberAnnounces = 0;
233         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_COUNT_ANNOUNCES_FOR_CATEORY, plugin ) )
234         {
235             daoUtil.setInt( 1, category.getId( ) );
236             daoUtil.executeQuery( );
237 
238             if ( daoUtil.next( ) )
239             {
240                 nNumberAnnounces = daoUtil.getInt( 1 );
241             }
242         }
243         return nNumberAnnounces;
244     }
245 
246     /**
247      * {@inheritDoc}
248      */
249     @Override
250     public int countPublishedAnnouncesForCategory( Category category, Plugin plugin )
251     {
252         int nNumberAnnounces = 0;
253         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_COUNT_PUBLISHED_ANNOUNCES_FOR_CATEORY, plugin ) )
254         {
255             daoUtil.setInt( 1, category.getId( ) );
256             daoUtil.executeQuery( );
257 
258             if ( daoUtil.next( ) )
259             {
260                 nNumberAnnounces = daoUtil.getInt( 1 );
261             }
262         }
263         return nNumberAnnounces;
264     }
265 
266     /**
267      * {@inheritDoc}
268      */
269     @Override
270     public int countEntriesForCategory( Category category, Plugin plugin )
271     {
272         int nNumberEntries = 0;
273         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_COUNT_ENTRIES_FOR_CATEGORY, plugin ) )
274         {
275             daoUtil.setInt( 1, category.getId( ) );
276             daoUtil.executeQuery( );
277 
278             if ( daoUtil.next( ) )
279             {
280                 nNumberEntries = daoUtil.getInt( 1 );
281             }
282         }
283         return nNumberEntries;
284     }
285 
286     /**
287      * {@inheritDoc}
288      */
289     @Override
290     public List<Category> selectCategoriesForSector( Sector sector, Plugin plugin )
291     {
292         List<Category> listCategories = new ArrayList<>( );
293         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_CATEGORIES_FOR_FIELD, plugin ) )
294         {
295             daoUtil.setInt( 1, sector.getId( ) );
296             daoUtil.executeQuery( );
297 
298             while ( daoUtil.next( ) )
299             {
300                 Categorynounce/business/Category.html#Category">Category category = new Category( );
301                 category.setId( daoUtil.getInt( 1 ) );
302                 category.setIdSector( daoUtil.getInt( 2 ) );
303                 category.setLabel( daoUtil.getString( 3 ) );
304                 category.setNumberAnnounces( countPublishedAnnouncesForCategory( category, plugin ) );
305 
306                 listCategories.add( category );
307             }
308         }
309         return listCategories;
310     }
311 
312     /**
313      * {@inheritDoc}
314      */
315     @Override
316     public ReferenceList selectCategoriesReferenceList( Plugin plugin )
317     {
318         ReferenceList listCategories = new ReferenceList( );
319         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_CATEGORIES_REFERENCELIST, plugin ) )
320         {
321             daoUtil.executeQuery( );
322 
323             while ( daoUtil.next( ) )
324             {
325                 listCategories.addItem( daoUtil.getInt( 1 ), daoUtil.getString( 2 ) );
326             }
327         }
328         return listCategories;
329     }
330 }