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.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 Time Slot objects
45   * 
46   * @author Laurent Payen
47   *
48   */
49  public final class TimeSlotDAO extends UtilDAO implements ITimeSlotDAO
50  {
51  
52      private static final String SQL_QUERY_NEW_PK = "SELECT max(id_time_slot) FROM appointment_time_slot";
53      private static final String SQL_QUERY_INSERT = "INSERT INTO appointment_time_slot (id_time_slot, starting_time, ending_time, is_open, max_capacity, id_working_day) VALUES (?, ?, ?, ?, ?, ?)";
54      private static final String SQL_QUERY_UPDATE = "UPDATE appointment_time_slot SET starting_time = ?, ending_time = ?, is_open = ?, max_capacity = ?, id_working_day = ? WHERE id_time_slot = ?";
55      private static final String SQL_QUERY_DELETE = "DELETE FROM appointment_time_slot WHERE id_time_slot = ?";
56      private static final String SQL_QUERY_SELECT_COLUMNS = "SELECT id_time_slot, starting_time, ending_time, is_open, max_capacity, id_working_day FROM appointment_time_slot";
57      private static final String SQL_QUERY_SELECT = SQL_QUERY_SELECT_COLUMNS + " WHERE id_time_slot = ?";
58      private static final String SQL_QUERY_SELECT_BY_ID_WORKING_DAY = SQL_QUERY_SELECT_COLUMNS + " WHERE id_working_day = ?";
59  
60      @Override
61      public synchronized void insert( TimeSlot timeSlot, Plugin plugin )
62      {
63          timeSlot.setIdTimeSlot( getNewPrimaryKey( SQL_QUERY_NEW_PK, plugin ) );
64          DAOUtil daoUtil = buildDaoUtil( SQL_QUERY_INSERT, timeSlot, plugin, true );
65          executeUpdate( daoUtil );
66      }
67  
68      @Override
69      public void update( TimeSlot timeSlot, Plugin plugin )
70      {
71          DAOUtil daoUtil = buildDaoUtil( SQL_QUERY_UPDATE, timeSlot, plugin, false );
72          executeUpdate( daoUtil );
73      }
74  
75      @Override
76      public void delete( int nIdTimeSlot, Plugin plugin )
77      {
78          DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin );
79          daoUtil.setInt( 1, nIdTimeSlot );
80          executeUpdate( daoUtil );
81      }
82  
83      @Override
84      public TimeSlot select( int nIdTimeSlot, Plugin plugin )
85      {
86          DAOUtil daoUtil = null;
87          TimeSlot timeSlot = null;
88          try
89          {
90              daoUtil = new DAOUtil( SQL_QUERY_SELECT, plugin );
91              daoUtil.setInt( 1, nIdTimeSlot );
92              daoUtil.executeQuery( );
93              if ( daoUtil.next( ) )
94              {
95                  timeSlot = buildTimeSlot( daoUtil );
96              }
97          }
98          finally
99          {
100             if ( daoUtil != null )
101             {
102                 daoUtil.free( );
103             }
104         }
105         return timeSlot;
106     }
107 
108     @Override
109     public List<TimeSlot> findByIdWorkingDay( int nIdWorkingDay, Plugin plugin )
110     {
111         DAOUtil daoUtil = null;
112         List<TimeSlot> listTimeSLots = new ArrayList<>( );
113         try
114         {
115             daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_ID_WORKING_DAY, plugin );
116             daoUtil.setInt( 1, nIdWorkingDay );
117             daoUtil.executeQuery( );
118             while ( daoUtil.next( ) )
119             {
120                 listTimeSLots.add( buildTimeSlot( daoUtil ) );
121             }
122         }
123         finally
124         {
125             if ( daoUtil != null )
126             {
127                 daoUtil.free( );
128             }
129         }
130         return listTimeSLots;
131     }
132 
133     /**
134      * Build a time slot business object from the resultset
135      * 
136      * @param daoUtil
137      *            the prepare statement util object
138      * @return a new time slot with all its attributes assigned
139      */
140     private TimeSlot buildTimeSlot( DAOUtil daoUtil )
141     {
142         int nIndex = 1;
143         TimeSlot timeSlot = new TimeSlot( );
144         timeSlot.setIdTimeSlot( daoUtil.getInt( nIndex++ ) );
145         timeSlot.setSqlStartingTime( daoUtil.getTime( nIndex++ ) );
146         timeSlot.setSqlEndingTime( daoUtil.getTime( nIndex++ ) );
147         timeSlot.setIsOpen( daoUtil.getBoolean( nIndex++ ) );
148         timeSlot.setMaxCapacity( daoUtil.getInt( nIndex++ ) );
149         timeSlot.setIdWorkingDay( daoUtil.getInt( nIndex ) );
150         return timeSlot;
151     }
152 
153     /**
154      * Build a daoUtil object with time slot business object
155      * 
156      * @param query
157      *            the query
158      * @param timeSlot
159      *            the time slot
160      * @param plugin
161      *            the plugin
162      * @param isInsert
163      *            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
164      *            set
165      * @return a new daoUtil with all its values assigned
166      */
167     private DAOUtil buildDaoUtil( String query, TimeSlot timeSlot, Plugin plugin, boolean isInsert )
168     {
169         int nIndex = 1;
170         DAOUtil daoUtil = new DAOUtil( query, plugin );
171         if ( isInsert )
172         {
173             daoUtil.setInt( nIndex++, timeSlot.getIdTimeSlot( ) );
174         }
175         daoUtil.setTime( nIndex++, timeSlot.getStartingTimeSqlTime( ) );
176         daoUtil.setTime( nIndex++, timeSlot.getEndingTimeSqlTime( ) );
177         daoUtil.setBoolean( nIndex++, timeSlot.getIsOpen( ) );
178         daoUtil.setInt( nIndex++, timeSlot.getMaxCapacity( ) );
179         daoUtil.setInt( nIndex++, timeSlot.getIdWorkingDay( ) );
180         if ( !isInsert )
181         {
182             daoUtil.setInt( nIndex, timeSlot.getIdTimeSlot( ) );
183         }
184         return daoUtil;
185     }
186 
187     /**
188      * Execute a safe update (Free the connection in case of error when execute the query)
189      * 
190      * @param daoUtil
191      *            the daoUtil
192      */
193     private void executeUpdate( DAOUtil daoUtil )
194     {
195         try
196         {
197             daoUtil.executeUpdate( );
198         }
199         finally
200         {
201             if ( daoUtil != null )
202             {
203                 daoUtil.free( );
204             }
205         }
206     }
207 
208 }