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.dbpage.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.Collection;
42  import java.util.List;
43  
44  
45  /**
46   * This class provides Data Access methods for DbPageDatabaseSection objects
47   */
48  public final class DbPageDatabaseSectionDAO implements IDbPageDatabaseSectionDAO
49  {
50      // Constants
51      private static final String SQL_QUERY_NEW_PK = " SELECT max( id_section ) FROM dbpage_section ";
52      private static final String SQL_QUERY_SELECT = " SELECT id_section, id_page, id_type, template, desc_column, desc_sql, pool, title , id_order, role FROM dbpage_section WHERE id_section = ?  ";
53      private static final String SQL_QUERY_INSERT = " INSERT INTO dbpage_section ( id_section, id_page, id_type, template, desc_column, desc_sql, pool, title , id_order, role ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ? , ?, ? ) ";
54      private static final String SQL_QUERY_DELETE = " DELETE FROM dbpage_section WHERE id_section = ?  ";
55      private static final String SQL_QUERY_UPDATE = " UPDATE dbpage_section SET id_section = ?, id_page = ?, id_type = ?, template = ?, desc_column = ?, desc_sql = ?, pool = ?, title = ? , id_order = ?, role = ? WHERE id_section = ?  ";
56      private static final String SQL_QUERY_SELECT_ALL = " SELECT id_section, id_page, id_type, template, desc_column, desc_sql, pool, title , id_order, role FROM dbpage_section ORDER BY id_order ";
57      private static final String SQL_QUERY_COUNT_SECTIONS = " SELECT count(*) FROM dbpage_section WHERE id_page= ? ";
58      private static final String SQL_QUERY_SECTIONS_BY_PAGE = "SELECT id_section, id_page, id_type, template, desc_column, desc_sql, pool, title , id_order, role FROM dbpage_section WHERE id_page = ? ORDER BY id_order ";
59      private static final String SQL_QUERY_SECTIONS_BY_ROLE = "SELECT id_section, id_page, id_type, template, desc_column, desc_sql, pool, title , id_order, role FROM dbpage_section WHERE role = ? ORDER BY id_order ";
60      private static final String SQL_QUERY_SELECT_COMBO = "SELECT id_order , id_order FROM dbpage_section WHERE id_page=? ";
61      private static final String SQL_QUERY_MODIFY_ORDER = "UPDATE dbpage_section SET id_order = ?  WHERE id_section = ? and id_page = ? ";
62      private static final String SQL_QUERY_SELECT_ORDER_BY_ID = "SELECT id_section FROM dbpage_section  WHERE id_order = ? and id_page = ? ";
63      private static final String SQL_QUERY_MAX_ORDER_BY_PAGE = "SELECT MAX(id_order) FROM dbpage_section  WHERE id_page= ?  ";
64      private static final String SQL_QUERY_REORDER_SECTION = " UPDATE dbpage_section SET id_order = ? WHERE id_section = ? ";
65      private static final String SQL_QUERY_COUNT_SECTION_IN_PAGE = " select count(id_section) from dbpage_section where id_page = (select distinct id_page from dbpage_page where param_name= ? ) and title= ? ";
66      /**
67       * Generates a new primary key
68       * @return The new primary key
69       */
70      private int newPrimaryKey( Plugin plugin )
71      {
72          DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_PK, plugin );
73          daoUtil.executeQuery(  );
74  
75          int nKey;
76  
77          if ( !daoUtil.next(  ) )
78          {
79              // if the table is empty
80              nKey = 1;
81          }
82  
83          nKey = daoUtil.getInt( 1 ) + 1;
84  
85          daoUtil.free(  );
86  
87          return nKey;
88      }
89  
90      /**
91       * Insert a new record in the table.
92       * @param dbPageDatabaseSection The dbPageDatabaseSection object
93       * @param plugin The plugin
94       */
95      public void insert( DbPageDatabaseSection dbPageDatabaseSection, Plugin plugin )
96      {
97          DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, plugin );
98          dbPageDatabaseSection.setId( newPrimaryKey( plugin ) );
99          daoUtil.setInt( 1, dbPageDatabaseSection.getId(  ) );
100         daoUtil.setInt( 2, dbPageDatabaseSection.getIdPage(  ) );
101         daoUtil.setString( 3, dbPageDatabaseSection.getIdType(  ) );
102         daoUtil.setString( 4, dbPageDatabaseSection.getTemplatePath(  ) );
103         daoUtil.setString( 5, dbPageDatabaseSection.getColumn(  ) );
104         daoUtil.setString( 6, dbPageDatabaseSection.getSql(  ) );
105         daoUtil.setString( 7, dbPageDatabaseSection.getPool(  ) );
106         daoUtil.setString( 8, dbPageDatabaseSection.getTitle(  ) );
107         daoUtil.setInt( 9, dbPageDatabaseSection.getOrder(  ) );
108         daoUtil.setString( 10, dbPageDatabaseSection.getRole(  ) );
109 
110         daoUtil.executeUpdate(  );
111         daoUtil.free(  );
112     }
113 
114     /**
115      * Load the data of DbPageDatabaseSection from the table
116      * @return the instance of the DbPageDatabaseSection
117      * @param plugin The plugin
118      * @param nDbPageDatabaseSectionId The identifier of DbPageDatabaseSection
119      */
120     public DbPageDatabaseSection load( int nDbPageDatabaseSectionId, Plugin plugin )
121     {
122         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT, plugin );
123         daoUtil.setInt( 1, nDbPageDatabaseSectionId );
124         daoUtil.executeQuery(  );
125 
126         DbPageDatabaseSection dbPageDatabaseSection = null;
127 
128         if ( daoUtil.next(  ) )
129         {
130             dbPageDatabaseSection = new DbPageDatabaseSection(  );
131             dbPageDatabaseSection.setId( daoUtil.getInt( 1 ) );
132             dbPageDatabaseSection.setIdPage( daoUtil.getInt( 2 ) );
133             dbPageDatabaseSection.setIdType( daoUtil.getString( 3 ) );
134             dbPageDatabaseSection.setTemplatePath( daoUtil.getString( 4 ) );
135             dbPageDatabaseSection.setColumn( daoUtil.getString( 5 ) );
136             dbPageDatabaseSection.setSql( daoUtil.getString( 6 ) );
137             dbPageDatabaseSection.setPool( daoUtil.getString( 7 ) );
138             dbPageDatabaseSection.setTitle( daoUtil.getString( 8 ) );
139             dbPageDatabaseSection.setOrder( daoUtil.getInt( 9 ) );
140             dbPageDatabaseSection.setRole( daoUtil.getString( 10 ) );
141         }
142 
143         daoUtil.free(  );
144 
145         return dbPageDatabaseSection;
146     }
147 
148     /**
149      * Load the sections related to a page
150      * @return the A List of the DbPageDatabaseSections
151      * @param plugin The plugin
152      * @param nDbPageId The identifier of DbPageDatabase
153      */
154     public List<DbPageDatabaseSection> loadSectionsByPageId( int nDbPageId, Plugin plugin )
155     {
156         List<DbPageDatabaseSection> listDbPageDatabaseSections = new ArrayList<DbPageDatabaseSection>(  );
157         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SECTIONS_BY_PAGE, plugin );
158         daoUtil.setInt( 1, nDbPageId );
159         daoUtil.executeQuery(  );
160 
161         DbPageDatabaseSection dbPageDatabaseSection = null;
162 
163         while ( daoUtil.next(  ) )
164         {
165             dbPageDatabaseSection = new DbPageDatabaseSection(  );
166             dbPageDatabaseSection.setId( daoUtil.getInt( 1 ) );
167             dbPageDatabaseSection.setIdPage( daoUtil.getInt( 2 ) );
168             dbPageDatabaseSection.setIdType( daoUtil.getString( 3 ) );
169             dbPageDatabaseSection.setTemplatePath( daoUtil.getString( 4 ) );
170             dbPageDatabaseSection.setColumn( daoUtil.getString( 5 ) );
171             dbPageDatabaseSection.setSql( daoUtil.getString( 6 ) );
172             dbPageDatabaseSection.setPool( daoUtil.getString( 7 ) );
173             dbPageDatabaseSection.setTitle( daoUtil.getString( 8 ) );
174             dbPageDatabaseSection.setOrder( daoUtil.getInt( 9 ) );
175             dbPageDatabaseSection.setRole( daoUtil.getString( 10 ) );
176 
177             listDbPageDatabaseSections.add( dbPageDatabaseSection );
178         }
179 
180         daoUtil.free(  );
181 
182         return listDbPageDatabaseSections;
183     }
184 
185     /**
186      * This method is used to find the sections of a dbpage
187      * @param strRoleKey The role key
188      * @param plugin The plugin object
189      * @return Collection of DbPageDatabaseSection
190      */
191     public Collection<DbPageDatabaseSection> loadSectionsByRoleKey( String strRoleKey, Plugin plugin )
192     {
193         List<DbPageDatabaseSection> listDbPageDatabaseSections = new ArrayList<DbPageDatabaseSection>(  );
194         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SECTIONS_BY_ROLE, plugin );
195         daoUtil.setString( 1, strRoleKey );
196         daoUtil.executeQuery(  );
197 
198         DbPageDatabaseSection dbPageDatabaseSection = null;
199 
200         while ( daoUtil.next(  ) )
201         {
202             dbPageDatabaseSection = new DbPageDatabaseSection(  );
203             dbPageDatabaseSection.setId( daoUtil.getInt( 1 ) );
204             dbPageDatabaseSection.setIdPage( daoUtil.getInt( 2 ) );
205             dbPageDatabaseSection.setIdType( daoUtil.getString( 3 ) );
206             dbPageDatabaseSection.setTemplatePath( daoUtil.getString( 4 ) );
207             dbPageDatabaseSection.setColumn( daoUtil.getString( 5 ) );
208             dbPageDatabaseSection.setSql( daoUtil.getString( 6 ) );
209             dbPageDatabaseSection.setPool( daoUtil.getString( 7 ) );
210             dbPageDatabaseSection.setTitle( daoUtil.getString( 8 ) );
211             dbPageDatabaseSection.setOrder( daoUtil.getInt( 9 ) );
212             dbPageDatabaseSection.setRole( daoUtil.getString( 10 ) );
213 
214             listDbPageDatabaseSections.add( dbPageDatabaseSection );
215         }
216 
217         daoUtil.free(  );
218 
219         return listDbPageDatabaseSections;
220     }
221 
222     /**
223      * Delete a record from the table
224      * @param plugin The plugin
225      * @param nDbPageDatabaseSectionId The DbPageDatabaseSection Id
226      */
227     public void delete( int nDbPageDatabaseSectionId, Plugin plugin )
228     {
229         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin );
230         daoUtil.setInt( 1, nDbPageDatabaseSectionId );
231         daoUtil.executeUpdate(  );
232         daoUtil.free(  );
233     }
234 
235     /**
236      * Update the record in the table
237      * @param plugin The plugin
238      * @param dbPageDatabaseSection The reference of dbPageDatabaseSection
239      */
240     public void store( DbPageDatabaseSection dbPageDatabaseSection, Plugin plugin )
241     {
242         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin );
243         daoUtil.setInt( 1, dbPageDatabaseSection.getId(  ) );
244         daoUtil.setInt( 2, dbPageDatabaseSection.getIdPage(  ) );
245         daoUtil.setString( 3, dbPageDatabaseSection.getIdType(  ) );
246         daoUtil.setString( 4, dbPageDatabaseSection.getTemplatePath(  ) );
247         daoUtil.setString( 5, dbPageDatabaseSection.getColumn(  ) );
248         daoUtil.setString( 6, dbPageDatabaseSection.getSql(  ) );
249         daoUtil.setString( 7, dbPageDatabaseSection.getPool(  ) );
250         daoUtil.setString( 8, dbPageDatabaseSection.getTitle(  ) );
251         daoUtil.setInt( 9, dbPageDatabaseSection.getOrder(  ) );
252         daoUtil.setString( 10, dbPageDatabaseSection.getRole(  ) );
253 
254         daoUtil.setInt( 11, dbPageDatabaseSection.getId(  ) );
255 
256         daoUtil.executeUpdate(  );
257         daoUtil.free(  );
258     }
259 
260     /**
261      * Load the list of dbPageDatabaseSections by page
262      * @param plugin The plugin
263      * @return The List of the DbPageDatabaseSections
264      */
265     public List<DbPageDatabaseSection> selectDbPageDatabaseSectionList( Plugin plugin )
266     {
267         List<DbPageDatabaseSection> listDbPageDatabaseSections = new ArrayList<DbPageDatabaseSection>(  );
268         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ALL, plugin );
269         daoUtil.executeQuery(  );
270 
271         while ( daoUtil.next(  ) )
272         {
273             DbPageDatabaseSection dbPageDatabaseSection = new DbPageDatabaseSection(  );
274             dbPageDatabaseSection.setId( daoUtil.getInt( 1 ) );
275             dbPageDatabaseSection.setIdPage( daoUtil.getInt( 2 ) );
276             dbPageDatabaseSection.setIdType( daoUtil.getString( 3 ) );
277             dbPageDatabaseSection.setTemplatePath( daoUtil.getString( 4 ) );
278             dbPageDatabaseSection.setColumn( daoUtil.getString( 5 ) );
279             dbPageDatabaseSection.setSql( daoUtil.getString( 6 ) );
280             dbPageDatabaseSection.setPool( daoUtil.getString( 7 ) );
281             dbPageDatabaseSection.setTitle( daoUtil.getString( 8 ) );
282             dbPageDatabaseSection.setOrder( daoUtil.getInt( 9 ) );
283             dbPageDatabaseSection.setRole( daoUtil.getString( 10 ) );
284 
285             listDbPageDatabaseSections.add( dbPageDatabaseSection );
286         }
287 
288         daoUtil.free(  );
289 
290         return listDbPageDatabaseSections;
291     }
292 
293     /**
294      * Calculates the number of sections in a page
295      * @param nDbPageId The id of the page
296      * @param plugin The plugin
297      * @return The number of sections
298      */
299     public int countSections( int nDbPageId, Plugin plugin )
300     {
301         int nCount = 0;
302         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_COUNT_SECTIONS, plugin );
303         daoUtil.setInt( 1, nDbPageId );
304         daoUtil.executeQuery(  );
305 
306         if ( daoUtil.next(  ) )
307         {
308             nCount = daoUtil.getInt( 1 );
309         }
310 
311         daoUtil.free(  );
312 
313         return nCount;
314     }
315     
316     /**
317      * Calculates the number of section having the same title
318      * @param nDbPageId The id of the page
319      * @param plugin The plugin
320      * @return The number of sections
321      */
322     public boolean isSectionInPage( String strPageName,String strSectionTitle, Plugin plugin )
323     {
324     	boolean bValue = false;
325         int nCount = 0;
326         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_COUNT_SECTION_IN_PAGE, plugin );
327         daoUtil.setString( 1, strPageName );
328         daoUtil.setString( 2, strSectionTitle );
329         daoUtil.executeQuery(  );
330 
331         if ( daoUtil.next(  ) )
332         {
333             nCount = daoUtil.getInt( 1 );
334         }
335 
336         daoUtil.free(  );
337         if(nCount>0)bValue=true;
338         return bValue;
339     }
340 
341     /**
342      * Load the list of orders
343      * @param plugin The plugin
344      * @param nPageId The identifier of the page
345      * @return The ReferenceList of the Orders
346      */
347     public ReferenceList selectOrderList( int nPageId, Plugin plugin )
348     {
349         ReferenceList orderList = new ReferenceList(  );
350         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_COMBO, plugin );
351         daoUtil.setInt( 1, nPageId );
352         daoUtil.executeQuery(  );
353 
354         while ( daoUtil.next(  ) )
355         {
356             orderList.addItem( daoUtil.getInt( 1 ), daoUtil.getString( 2 ) );
357         }
358 
359         daoUtil.free(  );
360 
361         return orderList;
362     }
363 
364     /**
365      * Modify the order of a section
366      *
367      * @param nPageId The Page identifier
368      * @param nNewOrder The order number
369      * @param nIdSection The Section identifier
370      * @param plugin The plugin
371      */
372     public void getModifySectionOrder( int nPageId, int nNewOrder, int nIdSection, Plugin plugin )
373     {
374         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_MODIFY_ORDER, plugin );
375         daoUtil.setInt( 1, nNewOrder );
376         daoUtil.setInt( 2, nIdSection );
377         daoUtil.setInt( 3, nPageId );
378         daoUtil.executeUpdate(  );
379         daoUtil.free(  );
380     }
381 
382     /**
383      * Returns the identifier of a section in a distinct order
384      * @return The order of the Section
385      * @param nPageId The identifier of the page
386      * @param nSectionOrder The order number
387      * @param plugin The plugin
388      */
389     public int selectIdByOrder( int nPageId, int nSectionOrder, Plugin plugin )
390     {
391         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ORDER_BY_ID, plugin );
392         int nResult = nSectionOrder;
393         daoUtil.setInt( 1, nSectionOrder );
394         daoUtil.setInt( 2, nPageId );
395         daoUtil.executeQuery(  );
396 
397         if ( !daoUtil.next(  ) )
398         {
399             // If number order doesn't exist
400             nResult = 1;
401         }
402 
403         nResult = daoUtil.getInt( 1 );
404         daoUtil.free(  );
405 
406         return nResult;
407     }
408 
409     /**
410      * Returns the highest order on a page
411      * @return The order of the Section
412      * @param nPageId The identifier of the page
413      * @param plugin The plugin
414      */
415     public int selectMaxIdOrder( int nPageId, Plugin plugin )
416     {
417         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_MAX_ORDER_BY_PAGE, plugin );
418         int nResult = nPageId;
419         daoUtil.setInt( 1, nPageId );
420         daoUtil.executeQuery(  );
421 
422         if ( !daoUtil.next(  ) )
423         {
424             // If number order doesn't exist
425             nResult = 1;
426         }
427 
428         nResult = daoUtil.getInt( 1 );
429         daoUtil.free(  );
430 
431         return nResult;
432     }
433 
434     /**
435      *
436      * @param nIdSection1 the section order
437      * @param nOrderSection1 the section order
438      * @param nIdSection2 the section order
439      * @param nOrderSection2 the section order
440      * @param plugin The plugin
441      */
442     public void reorderSections( int nIdSection1, int nOrderSection1, int nIdSection2, int nOrderSection2, Plugin plugin )
443     {
444         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_REORDER_SECTION, plugin );
445         daoUtil.setInt( 1, nOrderSection1 );
446         daoUtil.setInt( 2, nIdSection1 );
447         daoUtil.executeUpdate(  );
448         daoUtil.setInt( 1, nOrderSection2 );
449         daoUtil.setInt( 2, nIdSection2 );
450         daoUtil.executeUpdate(  );
451         daoUtil.free(  );
452     }
453 }