001 /*****************************************************************************
002 * MODULE DESCRIPTION
003 ******************************************************************************
004 *
005 * NAME: KiuruReservation.java
006 * LANGUAGE: Java 2
007 * DATE: 13.11.2002
008 * AUTHOR: Jussi Mäkinen, Jyväskylä University (JYU)
009 * PURPOSE: This module includes routines to check and make reservations
010 * that are not handled by any third party reservation systems.
011 *
012 ******************************************************************************
013 * COPYRIGHT (C) Kiuru group
014 * Limited rights granted. Please refer to license
015 *****************************************************************************/
016 package kiurubeans;
017
018 import kotkabeans.*;
019 import java.sql.*;
020 import java.util.ArrayList;
021
022 /** This class encapsulates actual making of reservations and getting information
023 * about already made reservations.
024 */
025 public class KiuruReservation {
026
027 /** Reservation request */
028 public final static int REQUEST_STATE_REQUEST = 1;
029 /** Reservation is being processed. Reserved for future use. */
030 public final static int REQUEST_STATE_PROCESSING = 2;
031 /** Reservation is made and confirmed. This is the final state. */
032 public final static int REQUEST_STATE_CONFIRMED = 3;
033 /** Reservation was made and then cancelled */
034 public final static int REQUEST_STATE_CANCELLED = 4;
035 /** Somebody removed reservation. */
036 public final static int REQUEST_STATE_REMOVED = 5;
037
038 /** Default constructor */
039 public KiuruReservation() {
040
041 }
042
043 /** Assigns persons to a reservation
044 * @param cPerson personID of the contact person for the reservation
045 * @param resMaker personID of the person who actually made the reservation
046 * @param reservations Array of <CODE>Integers</CODE> containing <CODE>reservationID</CODE>s.
047 * @return <CODE>true</CODE> if no exceptions were thrown.
048 */
049 public static boolean assignPersonsToReservation(int cPerson, int resMaker,
050 Integer[] reservations) {
051 boolean ok = false;
052 PreparedDB pdb = new PreparedDB();
053 try {
054 pdb.connect("INSERT INTO reservationperson (deleted,personid,reservationid,reservationroleid) values ('false',?,?,?);",ResultSet.CONCUR_READ_ONLY);
055 for (int i=0;i<reservations.length;i++) {
056 if (reservations[i].intValue()>0) {
057 pdb.setInt(2,reservations[i].intValue());
058 pdb.setInt(1,cPerson);
059 pdb.setInt(3,3); //Remember to delete magic numbers
060 pdb.executeUpdate();
061 pdb.setInt(1,resMaker);
062 pdb.setInt(3,1);
063 pdb.executeUpdate();
064 }
065 }
066 ok = true;
067 }
068 catch (Exception e) { ok = false; }
069 finally {
070 pdb.disconnect();
071 }
072 return ok;
073 }
074
075 /** Checks if given space is reserved or free.
076 * @param spaceId Id of space
077 * @param eventId Time interval to be checked
078 * @return Returns false if given space is free during given event
079 * @throws java.lang.Exception If something went wrong in query
080 */
081 public static boolean isReserved(int spaceId,int eventId) throws Exception{
082 boolean reserved=false;
083 DB db = new DB("isReserved");
084 try {
085 db.connect();
086 ResultSet rs = db.executeQuery("SELECT e1.eventid from reservation,event as e1, "+
087 "event as e2 where (e1.begintime,e1.endtime) overlaps "+
088 "(e2.begintime,e2.endtime) and reservation.eventid=e1.eventid and "+
089 "e2.eventid="+eventId+" and reservation.deleted=false and "+
090 "reservation.spaceid="+spaceId+" and reservation.requeststateid=3 and "+
091 "e1.deleted=false and e2.deleted=false;");
092 if (rs.next()) {reserved=true;}
093 }
094 finally {
095 db.disconnect();
096 }
097 return reserved;
098 }
099
100 /** Makes reservation to given space for given event
101 * @param spaceId Space that is going to be reserved.
102 * @param eventId Event that is going to be held.
103 * @param organisationId Organisation that is arranging the event.
104 * @param requestState If the reservation is final or proposition.
105 * @param reservationInfo Additional info
106 * @return id of made reservation or -1 if reservation was not successful
107 * @param user Who is making the reservation
108 * @throws Exception If something went wrong in database update
109 */
110 public static int makeReservation(int spaceId, int eventId, int organisationId,int requestState,String reservationInfo, User user) throws Exception{
111 int resId=-1;
112 if (eventId<1) return resId;
113 if (spaceId<1) return resId;
114 DB db = new DB("Add reservation");
115 try {
116 if (!Event.checkUsersEventRights(eventId, user)) return resId;
117 if (isReserved(spaceId,eventId) && requestState > REQUEST_STATE_REQUEST) return resId;
118 if (requestState == REQUEST_STATE_REQUEST && !KiuruSpace.checkReservationRequestRight(spaceId,user))
119 return resId;
120 if (requestState == REQUEST_STATE_CONFIRMED && !KiuruSpace.checkUserRights(spaceId,user))
121 return resId;
122 db.connect();
123 if (requestState==REQUEST_STATE_CONFIRMED) {
124 ResultSet rs = db.executeQuery("SELECT reservationid from reservation,event as e1, event as e2 where spaceid="+spaceId+" and reservation.eventid=e1.eventid and e2.eventid="+eventId+" and (e1.begintime,e1.endtime) overlaps (e2.begintime,e2.endtime) and reservation.requeststateid=3 and reservation.deleted='false' and e1.deleted='false' and e2.deleted='false';");
125 if (rs.next()) { return -1;}
126 }
127 db.executeUpdate("UPDATE reservation SET deleted='true' where eventid="+eventId+" and deleted='false';");
128 resId=AutoNumber.getNumber("Reservation");
129 db.executeUpdate("INSERT INTO reservation (deleted,reservationid,spaceid,eventid,resorganisationid,payorganisationid,requeststateid,updatetime,description) values ('false',"+resId+","+spaceId+","+eventId+","+organisationId+","+organisationId+","+requestState+",NOW(),'"+kotkabeans.Encoder.SQLEncode(reservationInfo)+"');");
130 db.executeUpdate("UPDATE eventspace set spaceid="+spaceId+" WHERE eventid="+eventId+" and deleted='false'");
131 }
132 catch (Exception e) {
133 resId=-1;
134 }
135 finally {
136 db.disconnect();
137 }
138 return resId;
139 }
140
141
142 /** Makes reservation to given space for given event
143 * @param spaceId Space that is going to be reserved.
144 * @param eventId Event that is going to be held.
145 * @param organisationId Organisation that is arranging the event.
146 * @param reservationInfo Additional info
147 * @return id of made reservation or -1 if reservation was not successful
148 * @param user Who is making the reservation
149 * @throws Exception If something went wrong in database update
150 */
151 public static int makeReservation(int spaceId, int eventId, int organisationId,String reservationInfo,User user) throws Exception {
152 return makeReservation(spaceId,eventId,organisationId,1, reservationInfo,user);
153 }
154
155 /** Makes reservations to given space for given event
156 * @return id of made reservation or -1 if reservation was not successful
157 * @param spaceId Space that is going to be reserved.
158 * @param eventIds Events that are going to be held.
159 * @param organisationId Organisation that is arranging the event.
160 * @param reservationInfo Additional info
161 * @param user Who is making the reservation
162 * @param requestState If the reservation is final or proposition.
163 * @param payOrganisationId Organisation that is paying the event.
164 * @throws Exception If something went wrong in database update
165 */
166 public static Integer[] makeReservation(int spaceId, Integer[] eventIds, int organisationId, int payOrganisationId, int requestState, String reservationInfo,User user) throws Exception{
167
168 Integer[] resIds=new Integer[eventIds.length];
169 for (int i=0;i<eventIds.length;i++)
170 resIds[i]=new Integer(makeReservation(spaceId,eventIds[i].intValue(),organisationId,requestState,reservationInfo,user));
171 return resIds;
172 }
173
174 /** Adds person as a contact person into a reservation
175 * @param personID Id of the contact person
176 * @param reservationID Id of the reservation
177 * @throws Exception If something went wrong during database update
178 */
179 public static void addPersonToReservation(int personID,int reservationID) throws Exception {
180 DB db = new DB("addPersonToReservation");
181 try {
182 db.connect();
183 db.executeUpdate("INSERT INTO reservationperson (deleted,personid,reservationid,reservationroleid) values ('false',"+personID+","+reservationID+",1);");
184 }
185 finally {
186 db.disconnect();
187 }
188 }
189
190 /** Returns state for current reservation, or empty string if there is
191 * no reservation.
192 * @param spaceId Id of space where reservation is made to
193 * @param eventId Id of the event that deals with reservation
194 * @return RequestState as a String.
195 */
196 public static String getReservationState(int eventId, int spaceId) {
197 Log.log("START GETRESERVATIONSTATE");
198 StringBuffer sb = new StringBuffer(20);
199 sb.append("SELECT rs.name as name FROM requeststate as rs, reservation as r ");
200 sb.append("WHERE r.eventid="+eventId+" and r.deleted=false ");
201 sb.append("AND r.requeststateid = rs.requeststateid ");
202 if (spaceId!=0) {
203 sb.append("AND r.spaceid ="+spaceId);
204 }
205 RS2 rs = null;
206 try {
207 Log.log("IN GETRESERVATIONSTATE");
208 rs = SimpleDb.simpleQuery("Kiurureservation.getReservationState", sb.toString());
209 if (!SimpleDb.isEmpty(rs)) {
210 Log.log("RS2 NONEMPTY");
211 rs.next();
212 return rs.getString("name");
213 }
214 else {
215 return "Ei varausta";
216 }
217 }
218 catch (Exception e) {
219 return "VIRHE!";
220 }
221 }
222
223 /** Determines if given event is reserved (+confirmed) at given space. We use
224 * this method in order to avoid double reservations.
225 * @return true if reservation is confirmed
226 * @param eventId Id of event that is checked
227 * @param spaceId Id of space where event should occur.
228 */
229 public static boolean isReservationOk(int eventId,int spaceId) {
230 boolean isOk=false;
231 DB db = new DB("isReservationOk");
232 try { // 3 == confirmed
233 String query="SELECT reservationid from reservation where eventid="+eventId+" and deleted='false' and requeststateid=3";
234 if (spaceId!=0) {query=query+" and spaceid="+spaceId;}
235 db.connect();
236
237 ResultSet rs = db.executeQuery(query);
238 if (rs.next()) {isOk=true;}
239 }
240 catch (Exception e) {
241 isOk=false;
242 }
243 finally {
244 db.disconnect();
245 }
246 return isOk;
247 }
248
249 /** Determines if there is reservation for given event.
250 * @param eventId Id of given event
251 * @return true if reservation is made and confirmed
252 */
253 public static boolean isReservationOk(int eventId) {
254 return isReservationOk(eventId,0);
255 }
256
257 /** Returns contact person of given reservation
258 * @param reservationId Id of reservation as String
259 * @throws Exception If something went wrong in database query
260 * @return PersonID of the contact person
261 */
262 public static int getReservationPerson(String reservationId) throws Exception {
263 int personId=-1;
264 try {
265 personId=getReservationPerson(Integer.parseInt(reservationId));
266 }
267 catch (NumberFormatException nfe) {personId=-1;}
268 return personId;
269 }
270
271 /** Returns contact person of given reservation
272 * @return PersonID of contact person.
273 * @param reservationId ReservationId
274 * @throws Exception If something went wrong in database query
275 */
276
277 public static int getReservationPerson(int reservationId) throws Exception{
278 DB db = new DB("getReservationPerson");
279 int person=-1;
280 try {
281 String query="select personid from reservationperson as rp "+
282 "where rp.reservationid="+reservationId+
283 " and rp.reservationroleid=3 "+
284 "and rp.deleted='f' "+
285 "limit 1 ";
286 db.connect();
287 ResultSet rs = db.executeQuery(query);
288 if (rs.next()) {
289 person=rs.getInt("personid");
290 }
291 }
292 finally {
293 db.disconnect();
294 }
295 return person;
296 }
297
298 /** Returns eventid of given reservation
299 * @param reservationId Id of the given reservation
300 * @return eventID
301 */
302 public static int getEventId(String reservationId) {
303 int eventId=-1;
304 try {
305 eventId=getEventId(Integer.parseInt(reservationId));
306 }
307 catch (NumberFormatException nfe) {eventId=-1;}
308 return eventId;
309 }
310
311 /** Returns eventid of given reservation
312 * @return eventId
313 * @param reservationId Id of the given reservation
314 */
315
316 public static int getEventId(int reservationId) {
317 DB db = new DB("getEventId");
318 int event=-1;
319 try {
320 String query="select eventid from reservation as r "+
321 "where r.reservationid="+reservationId+
322 " and r.deleted='f'";
323 db.connect();
324 ResultSet rs = db.executeQuery(query);
325 if (rs.next()) {
326 event=rs.getInt("eventid");
327 }
328 }
329 catch (Exception e) {
330 event=-1;
331 }
332 finally {
333 db.disconnect();
334 }
335 return event;
336 }
337
338 /** Returns EventID and EventGroupID of given reservation in int array.
339 * @return int array where array[0]=eventId and array[1]=eventGroupId
340 * @param reservationId Id of reservation as String
341 * @throws Exception If something went wrong in database query
342 */
343 public static int[] getEventAndEventGroupId(String reservationId) throws Exception {
344 int results[];
345 try {
346 results=getEventAndEventGroupId(Integer.parseInt(reservationId));
347 }
348 catch (NumberFormatException nfe) {results=null;}
349 return results;
350 }
351
352 /** Returns EventID and EventGroupID of given reservation in int array.
353 * @param reservationId Id of reservation
354 * @throws Exception If something went wrong in database query
355 * @return int array where array[0]=eventId and array[1]=eventGroupId
356 */
357 public static int[] getEventAndEventGroupId(int reservationId) throws Exception {
358 DB db = new DB("getEventId");
359 int event=-1;
360 int eventGroup=-1;
361 try {
362 String query="select event.eventid, eventgroupid from reservation as r,event "+
363 "where r.eventid=event.eventid and r.reservationid="+reservationId+
364 " and r.deleted='f' and event.eventid=reservation.eventid and "+
365 "event.deleted='f'";
366 db.connect();
367 ResultSet rs = db.executeQuery(query);
368 if (rs.next()) {
369 event=rs.getInt("eventid");
370 eventGroup=rs.getInt("eventgroupid");
371 }
372 }
373 finally {
374 db.disconnect();
375 }
376 int[] results = new int[2];
377 results[0]=event;
378 results[1]=eventGroup;
379 return results;
380 }
381 /* public static int getReservationOverlappingCount(int reservationid) {
382
383 select count(*) from reservation where
384 reservationid in
385 (SELECT distinct r.reservationid FROM event AS e1, event AS e2,eventspace AS es1, eventspace AS es2, reservation as r WHERE (e1.begintime,e1.endtime) OVERLAPS (e2.begintime,e2.endtime) AND e1.begintime>NOW() AND es2.eventid=e2.eventid AND es1.eventid=e1.eventid AND es2.spaceid=es1.spaceid AND e1.eventid<>e2.eventid AND es1.spaceid<>0 AND e1.deleted='false' AND e2.deleted='false' AND es1.deleted='false' AND es2.deleted='false' and es1.spaceid=r.spaceid and e1.eventid=r.eventid
386 and r.requeststateid<4
387 and r.spaceid in (
388 select spaceid from reservation
389 where reservationid=493
390 )
391 and r.reservationid<>493
392 )
393 }*/
394
395 /** Checks if given user has access to modify reservation (or anything administrial)
396 * @param reservationId Id of reservation to be checked
397 * @param user User who tries to modify reservation
398 * @return true if user is allowed to modify reservation
399 */
400 public static boolean checkUsersReservationRights(int reservationId, User user) {
401 int eventId = getEventId(reservationId);
402 if (kotkabeans.Event.checkUsersEventRights(eventId, user)) return true;
403 DB db = new DB("checkUsersReservationRights");
404 try {
405 db.connect();
406 ResultSet rs = db.executeQuery("SELECT reservationroleid from"+
407 " reservationperson where deleted='f' and personid="+user.getPersonID()+
408 " and reservationid="+reservationId);
409 if (rs.next()) {
410 return true;
411 }
412 }
413 catch (Exception e) {
414 return false;
415 }
416 finally {
417 db.disconnect();
418 }
419
420 return false;
421 }
422
423 /** Changes states of specified reservation requests into reservations
424 * @return Returns reservation requests that couldn't be accepted. The main reason for this
425 * is the space is not free.
426 * @param reservations Array of Strings containing <CODE>reservationID</CODE>s we are accepting.
427 */
428 public static ArrayList acceptReservationRequests(String[] reservations) {
429 ArrayList failure = new ArrayList(10);
430 if (reservations==null || reservations.length<1) return failure;
431 DB db = new DB();
432 int i=0;
433 try {
434 db.connect();
435 for (i=0;i<reservations.length;i++) {
436 ResultSet rs = db.executeQuery("SELECT e2.eventid as eventid FROM eventgroup AS eg1, event AS e1, "
437 +"event AS e2,reservation AS es1, reservation AS es2 WHERE (e1.begintime,e1.endtime) "
438 +"OVERLAPS (e2.begintime,e2.endtime) AND e1.begintime>NOW() AND es2.eventid=e2.eventid "
439 +"AND es1.eventid=e1.eventid AND es2.spaceid=es1.spaceid AND e1.eventid<>e2.eventid AND "
440 +"es1.spaceid<>0 AND e1.deleted='false' AND e2.deleted='false' AND es1.deleted='false' "
441 +"AND es2.deleted='false' AND e1.eventgroupid=eg1.eventgroupid AND es1.reservationid="+reservations[i]+" "
442 +"AND es2.requeststateid="+REQUEST_STATE_CONFIRMED+" ORDER BY e1.begintime;");
443 if (rs.next()) {failure.add(reservations[i]);}
444 if (!failure.contains(reservations[i])) {
445 int ok = db.executeUpdate("UPDATE reservation SET requeststateid="+REQUEST_STATE_CONFIRMED+" where requeststateid = "+REQUEST_STATE_REQUEST+" and reservationid="+reservations[i]+";");
446 if (ok!=1) {failure.add(reservations[i]);}
447 }
448 }
449 }
450 catch (Exception e) {
451 for (int j=i;j<reservations.length;j++) {
452 if (!failure.contains(reservations[j]))
453 failure.add(reservations[j]);
454 }
455 }
456 finally {
457 db.disconnect();
458 }
459 return failure;
460 }
461 }