View Javadoc
1   /*
2    * Copyright (c) 2002-2018, 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.appointment.business.form;
35  
36  import java.util.ArrayList;
37  import java.util.List;
38  
39  import fr.paris.lutece.plugins.appointment.business.UtilDAO;
40  import fr.paris.lutece.portal.service.plugin.Plugin;
41  import fr.paris.lutece.util.sql.DAOUtil;
42  
43  /**
44   * This class provides Data Access methods for Form objects
45   * 
46   * @author Laurent Payen
47   *
48   */
49  public final class FormDAO extends UtilDAO implements IFormDAO
50  {
51  
52      private static final String SQL_QUERY_NEW_PK = "SELECT max(id_form) FROM appointment_form";
53      private static final String SQL_QUERY_INSERT = "INSERT INTO appointment_form (id_form, title, description, reference, id_category, starting_validity_date, ending_validity_date, is_active, id_workflow, workgroup) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
54      private static final String SQL_QUERY_UPDATE = "UPDATE appointment_form SET title = ?, description = ?, reference = ?, id_category = ?, starting_validity_date = ?, ending_validity_date = ?, is_active = ?, id_workflow = ?, workgroup = ? WHERE id_form = ?";
55      private static final String SQL_QUERY_DELETE = "DELETE FROM appointment_form WHERE id_form = ? ";
56      private static final String SQL_QUERY_SELECT_COLUMNS = "SELECT form.id_form, form.title, form.description, form.reference, form.id_category, form.starting_validity_date, form.ending_validity_date, form.is_active, form.id_workflow, form.workgroup FROM appointment_form form";
57      private static final String SQL_QUERY_SELECT_BY_TITLE = SQL_QUERY_SELECT_COLUMNS + " WHERE title = ?";
58      private static final String SQL_QUERY_SELECT_ALL = SQL_QUERY_SELECT_COLUMNS;
59      private static final String SQL_QUERY_SELECT = SQL_QUERY_SELECT_COLUMNS + " WHERE id_form = ?";
60      private static final String SQL_QUERY_SELECT_ACTIVE_FORMS = SQL_QUERY_SELECT_COLUMNS + " WHERE is_active = 1";
61      private static final String SQL_QUERY_SELECT_ACTIVE_AND_DISPLAYED_ON_PORTLET_FORMS = SQL_QUERY_SELECT_COLUMNS
62              + " INNER JOIN appointment_display display ON form.id_form = display.id_form WHERE form.is_active = 1 AND display.is_displayed_on_portlet = 1";
63  
64      @Override
65      public synchronized void insert( Form form, Plugin plugin )
66      {
67          form.setIdForm( getNewPrimaryKey( SQL_QUERY_NEW_PK, plugin ) );
68          DAOUtil daoUtil = buildDaoUtil( SQL_QUERY_INSERT, form, plugin, true );
69          executeUpdate( daoUtil );
70      }
71  
72      @Override
73      public void update( Form form, Plugin plugin )
74      {
75          DAOUtil daoUtil = buildDaoUtil( SQL_QUERY_UPDATE, form, plugin, false );
76          executeUpdate( daoUtil );
77      }
78  
79      @Override
80      public void delete( int nIdForm, Plugin plugin )
81      {
82          DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin );
83          daoUtil.setInt( 1, nIdForm );
84          executeUpdate( daoUtil );
85      }
86  
87      @Override
88      public Form select( int nIdForm, Plugin plugin )
89      {
90          DAOUtil daoUtil = null;
91          Form form = null;
92          try
93          {
94              daoUtil = new DAOUtil( SQL_QUERY_SELECT, plugin );
95              daoUtil.setInt( 1, nIdForm );
96              daoUtil.executeQuery( );
97              if ( daoUtil.next( ) )
98              {
99                  form = buildForm( daoUtil );
100             }
101         }
102         finally
103         {
104             if ( daoUtil != null )
105             {
106                 daoUtil.free( );
107             }
108         }
109         return form;
110     }
111 
112     @Override
113     public List<Form> findActiveForms( Plugin plugin )
114     {
115         DAOUtil daoUtil = null;
116         List<Form> listForms = new ArrayList<>( );
117         try
118         {
119             daoUtil = new DAOUtil( SQL_QUERY_SELECT_ACTIVE_FORMS, plugin );
120             daoUtil.executeQuery( );
121             while ( daoUtil.next( ) )
122             {
123                 listForms.add( buildForm( daoUtil ) );
124             }
125         }
126         finally
127         {
128             if ( daoUtil != null )
129             {
130                 daoUtil.free( );
131             }
132         }
133         return listForms;
134     }
135 
136     @Override
137     public List<Form> findActiveAndDisplayedOnPortletForms( Plugin plugin )
138     {
139         DAOUtil daoUtil = null;
140         List<Form> listForms = new ArrayList<>( );
141         try
142         {
143             daoUtil = new DAOUtil( SQL_QUERY_SELECT_ACTIVE_AND_DISPLAYED_ON_PORTLET_FORMS, plugin );
144             daoUtil.executeQuery( );
145             while ( daoUtil.next( ) )
146             {
147                 listForms.add( buildForm( daoUtil ) );
148             }
149         }
150         finally
151         {
152             if ( daoUtil != null )
153             {
154                 daoUtil.free( );
155             }
156         }
157         return listForms;
158     }
159 
160     @Override
161     public List<Form> findByTitle( String strTitle, Plugin plugin )
162     {
163         DAOUtil daoUtil = null;
164         List<Form> listForms = new ArrayList<>( );
165         try
166         {
167             daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_TITLE, plugin );
168             daoUtil.setString( 1, strTitle );
169             daoUtil.executeQuery( );
170             while ( daoUtil.next( ) )
171             {
172                 listForms.add( buildForm( daoUtil ) );
173             }
174         }
175         finally
176         {
177             if ( daoUtil != null )
178             {
179                 daoUtil.free( );
180             }
181         }
182         return listForms;
183     }
184 
185     @Override
186     public List<Form> findAllForms( Plugin plugin )
187     {
188         DAOUtil daoUtil = null;
189         List<Form> listForms = new ArrayList<>( );
190         try
191         {
192             daoUtil = new DAOUtil( SQL_QUERY_SELECT_ALL, plugin );
193             daoUtil.executeQuery( );
194             while ( daoUtil.next( ) )
195             {
196                 listForms.add( buildForm( daoUtil ) );
197             }
198         }
199         finally
200         {
201             if ( daoUtil != null )
202             {
203                 daoUtil.free( );
204             }
205         }
206         return listForms;
207     }
208 
209     /**
210      * Build a Form business object from the resultset
211      * 
212      * @param daoUtil
213      *            the prepare statement util object
214      * @return a new Form with all its attributes assigned
215      */
216     private Form buildForm( DAOUtil daoUtil )
217     {
218         int nIndex = 1;
219         Form form = new Form( );
220         form.setIdForm( daoUtil.getInt( nIndex++ ) );
221         form.setTitle( daoUtil.getString( nIndex++ ) );
222         form.setDescription( daoUtil.getString( nIndex++ ) );
223         form.setReference( daoUtil.getString( nIndex++ ) );
224         form.setIdCategory( daoUtil.getInt( nIndex++ ) );
225         form.setStartingValiditySqlDate( daoUtil.getDate( nIndex++ ) );
226         form.setEndingValiditySqlDate( daoUtil.getDate( nIndex++ ) );
227         form.setIsActive( daoUtil.getBoolean( nIndex++ ) );
228         form.setIdWorkflow( daoUtil.getInt( nIndex++ ) );
229         form.setWorkgroup( daoUtil.getString( nIndex ) );
230         return form;
231     }
232 
233     /**
234      * Build a daoUtil object with the form
235      * 
236      * @param query
237      *            the query
238      * @param form
239      *            the form
240      * @param plugin
241      *            the plugin
242      * @param isInsert
243      *            true if it is an insert query (in this case, need to set the id). If false, it is an update, in this case, there is a where parameter id to
244      *            set
245      * @return a new daoUtil with all its values assigned
246      */
247     private DAOUtil buildDaoUtil( String query, Form form, Plugin plugin, boolean isInsert )
248     {
249         int nIndex = 1;
250         DAOUtil daoUtil = new DAOUtil( query, plugin );
251         if ( isInsert )
252         {
253             daoUtil.setInt( nIndex++, form.getIdForm( ) );
254         }
255         daoUtil.setString( nIndex++, form.getTitle( ) );
256         daoUtil.setString( nIndex++, form.getDescription( ) );
257         daoUtil.setString( nIndex++, form.getReference( ) );
258         if ( form.getIdCategory( ) == null || form.getIdCategory( ) == 0 )
259         {
260             daoUtil.setIntNull( nIndex++ );
261         }
262         else
263         {
264             daoUtil.setInt( nIndex++, form.getIdCategory( ) );
265         }
266         daoUtil.setDate( nIndex++, form.getStartingValiditySqlDate( ) );
267         daoUtil.setDate( nIndex++, form.getEndingValiditySqlDate( ) );
268         daoUtil.setBoolean( nIndex++, form.getIsActive( ) );
269         daoUtil.setInt( nIndex++, form.getIdWorkflow( ) );
270         daoUtil.setString( nIndex++, form.getWorkgroup( ) );
271         if ( !isInsert )
272         {
273             daoUtil.setInt( nIndex, form.getIdForm( ) );
274         }
275         return daoUtil;
276     }
277 
278     /**
279      * Execute a safe update (Free the connection in case of error when execute the query)
280      * 
281      * @param daoUtil
282      *            the daoUtil
283      */
284     private void executeUpdate( DAOUtil daoUtil )
285     {
286         try
287         {
288             daoUtil.executeUpdate( );
289         }
290         finally
291         {
292             if ( daoUtil != null )
293             {
294                 daoUtil.free( );
295             }
296         }
297     }
298 
299 }