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.planning;
35  
36  import java.sql.Date;
37  import java.time.LocalDate;
38  import java.util.ArrayList;
39  import java.util.List;
40  
41  import fr.paris.lutece.plugins.appointment.business.UtilDAO;
42  import fr.paris.lutece.portal.service.plugin.Plugin;
43  import fr.paris.lutece.util.sql.DAOUtil;
44  
45  /**
46   * This class provides Data Access methods for Closing Day objects
47   * 
48   * @author Laurent Payen
49   *
50   */
51  public final class ClosingDayDAO extends UtilDAO implements IClosingDayDAO
52  {
53  
54      private static final String SQL_QUERY_NEW_PK = "SELECT max(id_closing_day) FROM appointment_closing_day";
55      private static final String SQL_QUERY_INSERT = "INSERT INTO appointment_closing_day (id_closing_day, date_of_closing_day, id_form) VALUES ( ?, ?, ?)";
56      private static final String SQL_QUERY_UPDATE = "UPDATE appointment_closing_day SET date_of_closing_day = ?, id_form = ? WHERE id_closing_day = ?";
57      private static final String SQL_QUERY_DELETE = "DELETE FROM appointment_closing_day WHERE id_closing_day = ?";
58      private static final String SQL_QUERY_SELECT_COLUMNS = "SELECT id_closing_day, date_of_closing_day, id_form FROM appointment_closing_day";
59      private static final String SQL_QUERY_SELECT = SQL_QUERY_SELECT_COLUMNS + " WHERE id_closing_day = ?";
60      private static final String SQL_QUERY_SELECT_BY_ID_FORM = SQL_QUERY_SELECT_COLUMNS + " WHERE id_form = ?";
61      private static final String SQL_QUERY_SELECT_BY_ID_FORM_AND_DATE_OF_CLOSING_DAY = SQL_QUERY_SELECT_BY_ID_FORM + " AND date_of_closing_day = ?";
62      private static final String SQL_QUERY_SELECT_BY_ID_FORM_AND_DATE_RANGE = SQL_QUERY_SELECT_BY_ID_FORM
63              + " AND date_of_closing_day >= ? AND date_of_closing_day <= ?";
64  
65      @Override
66      public synchronized void insert( ClosingDay closingDay, Plugin plugin )
67      {
68          closingDay.setIdClosingDay( getNewPrimaryKey( SQL_QUERY_NEW_PK, plugin ) );
69          DAOUtil daoUtil = buildDaoUtil( SQL_QUERY_INSERT, closingDay, plugin, true );
70          executeUpdate( daoUtil );
71      }
72  
73      @Override
74      public void update( ClosingDay closingDay, Plugin plugin )
75      {
76          DAOUtil daoUtil = buildDaoUtil( SQL_QUERY_UPDATE, closingDay, plugin, false );
77          executeUpdate( daoUtil );
78      }
79  
80      @Override
81      public void delete( int nIdClosingDay, Plugin plugin )
82      {
83          DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin );
84          daoUtil.setInt( 1, nIdClosingDay );
85          executeUpdate( daoUtil );
86      }
87  
88      @Override
89      public ClosingDay select( int nIdClosingDay, Plugin plugin )
90      {
91          DAOUtil daoUtil = null;
92          ClosingDay closingDay = null;
93          try
94          {
95              daoUtil = new DAOUtil( SQL_QUERY_SELECT, plugin );
96              daoUtil.setInt( 1, nIdClosingDay );
97              daoUtil.executeQuery( );
98              if ( daoUtil.next( ) )
99              {
100                 closingDay = buildClosingDay( daoUtil );
101             }
102         }
103         finally
104         {
105             if ( daoUtil != null )
106             {
107                 daoUtil.free( );
108             }
109         }
110         return closingDay;
111     }
112 
113     @Override
114     public ClosingDay findByIdFormAndDateOfClosingDay( int nIdForm, LocalDate dateOfCLosingDay, Plugin plugin )
115     {
116         DAOUtil daoUtil = null;
117         ClosingDay closingDay = null;
118         try
119         {
120             daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_ID_FORM_AND_DATE_OF_CLOSING_DAY, plugin );
121             daoUtil.setInt( 1, nIdForm );
122             daoUtil.setDate( 2, Date.valueOf( dateOfCLosingDay ) );
123             daoUtil.executeQuery( );
124             if ( daoUtil.next( ) )
125             {
126                 closingDay = buildClosingDay( daoUtil );
127             }
128         }
129         finally
130         {
131             if ( daoUtil != null )
132             {
133                 daoUtil.free( );
134             }
135         }
136         return closingDay;
137     }
138 
139     @Override
140     public List<ClosingDay> findByIdForm( int nIdForm, Plugin plugin )
141     {
142         DAOUtil daoUtil = null;
143         List<ClosingDay> listClosingDay = new ArrayList<>( );
144         try
145         {
146             daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_ID_FORM, plugin );
147             daoUtil.setInt( 1, nIdForm );
148             daoUtil.executeQuery( );
149             while ( daoUtil.next( ) )
150             {
151                 listClosingDay.add( buildClosingDay( daoUtil ) );
152             }
153         }
154         finally
155         {
156             if ( daoUtil != null )
157             {
158                 daoUtil.free( );
159             }
160         }
161         return listClosingDay;
162     }
163 
164     @Override
165     public List<ClosingDay> findByIdFormAndDateRange( int nIdForm, LocalDate startingDate, LocalDate endingDate, Plugin plugin )
166     {
167         DAOUtil daoUtil = null;
168         List<ClosingDay> listClosingDay = new ArrayList<>( );
169         try
170         {
171             daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_ID_FORM_AND_DATE_RANGE, plugin );
172             daoUtil.setInt( 1, nIdForm );
173             daoUtil.setDate( 2, Date.valueOf( startingDate ) );
174             daoUtil.setDate( 3, Date.valueOf( endingDate ) );
175             daoUtil.executeQuery( );
176             while ( daoUtil.next( ) )
177             {
178                 listClosingDay.add( buildClosingDay( daoUtil ) );
179             }
180         }
181         finally
182         {
183             if ( daoUtil != null )
184             {
185                 daoUtil.free( );
186             }
187         }
188         return listClosingDay;
189     }
190 
191     /**
192      * Build a Closing Day business object from the resultset
193      * 
194      * @param daoUtil
195      *            the prepare statement util object
196      * @return a new Closing Day with all its attributes assigned
197      */
198     private ClosingDay buildClosingDay( DAOUtil daoUtil )
199     {
200         int nIndex = 1;
201         ClosingDay closingDay = new ClosingDay( );
202         closingDay.setIdClosingDay( daoUtil.getInt( nIndex++ ) );
203         closingDay.setSqlDateOfClosingDay( daoUtil.getDate( nIndex++ ) );
204         closingDay.setIdForm( daoUtil.getInt( nIndex ) );
205         return closingDay;
206     }
207 
208     /**
209      * Build a daoUtil object with the CLosingDay business object
210      * 
211      * @param query
212      *            the query
213      * @param closingDay
214      *            the closingDay
215      * @param plugin
216      *            the plugin
217      * @param isInsert
218      *            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
219      *            set
220      * @return a new daoUtil with all its values assigned
221      */
222     private DAOUtil buildDaoUtil( String query, ClosingDay closingDay, Plugin plugin, boolean isInsert )
223     {
224         int nIndex = 1;
225         DAOUtil daoUtil = new DAOUtil( query, plugin );
226         if ( isInsert )
227         {
228             daoUtil.setInt( nIndex++, closingDay.getIdClosingDay( ) );
229         }
230         daoUtil.setDate( nIndex++, closingDay.getSqlDateOfClosingDay( ) );
231         daoUtil.setInt( nIndex++, closingDay.getIdForm( ) );
232         if ( !isInsert )
233         {
234             daoUtil.setInt( nIndex, closingDay.getIdClosingDay( ) );
235         }
236         return daoUtil;
237     }
238 
239     /**
240      * Execute a safe update (Free the connection in case of error when execute the query)
241      * 
242      * @param daoUtil
243      *            the daoUtil
244      */
245     private void executeUpdate( DAOUtil daoUtil )
246     {
247         try
248         {
249             daoUtil.executeUpdate( );
250         }
251         finally
252         {
253             if ( daoUtil != null )
254             {
255                 daoUtil.free( );
256             }
257         }
258     }
259 
260 }