001 /***************************************************************************************************
002 * MODULE DESCRIPTION
003 ****************************************************************************************************
004 *
005 * NAME: ReservationSearch.java
006 * LANGUAGE: Java2
007 * DATE: 18.2.2003
008 * AUTHOR: Miika Nurminen, Jyväskylän yliopisto
009 *
010 ****************************************************************************************************
011 * COPYRIGHT (C) KIURU -PROJECT GROUP
012 * Limited rights granted. Please refer to license.
013 ****************************************************************************************************
014 *
015 ****************************************************************************************************
016 * UPDATES
017 ****************************************************************************************************
018 *
019 * 18.2.2003 Initial release
020 *
021 ****************************************************************************************************/
022 package kiurubeans;
023 import kotkabeans.*;
024
025 /**
026 * Search class for Reservations
027 */
028 public class ReservationSearch extends SearchHandler {
029 /** reservation id used in queries */
030 int reservationId = 0;
031 /** if true, also old reservations are searched */
032 boolean oldReservations = false;
033
034
035 /** Creates a new instance of ReservationSearch */
036 public ReservationSearch() {
037 setVisibleFields(new Field[] {
038 // UPPER cannot be used with order by in distinct clauses
039 new Field("code",new HiddenFieldOrderScheme("ord_code")),
040 new Field("begintime"),
041 new Field("endtime"),
042 new Field("kurssikoodi"),
043 new Field("overlappings"),
044 new Field("personname"), // note! UPPER(alias) cannot be used. actual fieldname must be applied
045 new Field("callname"), // note! UPPER(alias) cannot be used. actual fieldname must be applied
046 new Field("state"),
047 new Field("organisation"),
048 new Field("eventname",new HiddenFieldOrderScheme("ord_eventname"))
049 });
050 assignDefaultOrder();
051 }
052
053
054
055 /**
056 * Setter for attribute reservationId
057 * @param i new id
058 */
059 public void setReservationId(int i) {
060 this.reservationId=i;
061 }
062
063 /**
064 * Getter for attribute reservationId
065 * @return current id
066 */
067 public int getReservationId() {
068 return this.reservationId;
069 }
070
071
072 /**
073 * Gets sql string for retrieving reservations
074 * @return reservation sql
075 */
076 public String getReservationSql() {
077 if (getReservationId()==0)
078 return getReservationSql(false,0);
079 else
080 return getReservationSql(true,getReservationId());
081 }
082
083 /**
084 * Gets sql string for retrieving a single reservation data
085 * @param resId input reservation id
086 * @return reservation sql
087 */
088 public String getReservationSql(int resId) {
089 return getReservationSql(true,resId);
090 }
091
092 /**
093 * Setter for attribute oldReservations
094 * @param b new value
095 */
096 public void setOldReservations(boolean b) {
097 this.oldReservations=b;
098 }
099
100 /**
101 * Getter for attribute oldReservations
102 * @return current value
103 */
104 public boolean getOldReservations() {
105 return this.oldReservations;
106 }
107
108
109 /**
110 * Returns SQL clause for all resevations
111 * @param getOverlappingList if true, returns list for overlappings with reservation of id
112 * @param id used if getOverlappingList==true
113 */
114 private String getReservationSql(boolean getOverlappingList, int id) {
115 StringBuffer q = new StringBuffer(200);
116 q.append(" SELECT UPPER(space.code) as ord_code, requeststate.name AS state, organisationtranslation.name ");
117 q.append(" AS organisation,begintime,endtime, CASE WHEN course.code is not NULL ");
118 q.append(" THEN course.code ELSE '-' END as kurssikoodi,r1.reservationid, ");
119 q.append("(CASE WHEN (e1.name NOT LIKE '' AND e1.name IS NOT NULL) THEN e1.name ");
120 q.append("WHEN (eventgroup.name NOT LIKE '' AND eventgroup.name IS NOT NULL) THEN eventgroup.name ");
121 q.append("ELSE '' END) AS eventname, ");
122 q.append("UPPER(CASE WHEN (e1.name NOT LIKE '' AND e1.name IS NOT NULL) THEN e1.name ");
123 q.append("WHEN (eventgroup.name NOT LIKE '' AND eventgroup.name IS NOT NULL) THEN eventgroup.name ");
124 q.append("ELSE '' END) AS ord_eventname, ");
125 q.append("e1.eventid,space.code as code, ");
126 q.append(" ( SELECT count(*) ");
127 q.append(" FROM reservation as r2, event as e2 ");
128 q.append(" WHERE r1.reservationid<>r2.reservationid ");
129 q.append(" AND r1.spaceid = r2.spaceid ");
130 q.append(" AND e2.eventid = r2.eventid ");
131 q.append(" AND r1.inconsistent =0 ");
132 q.append(" AND r2.inconsistent =0 ");
133 q.append(" AND r1.requeststateid<4 ");
134 q.append(" AND r2.requeststateid<4 ");
135 q.append(" AND r2.deleted=false ");
136 // q.append(" AND e2.begintime>NOW() "); // also past reservations may overlap
137 q.append(" AND (e1.begintime,e1.endtime) OVERLAPS (e2.begintime,e2.endtime) ");
138 q.append(" ) as overlappings, ");
139 q.append(" space.spaceid as spaceid, ");
140 q.append("CASE WHEN p.personid IS NOT NULL THEN p.lastname ELSE p.lastname END as personname, ");
141 q.append("CASE WHEN p.personid IS NOT NULL THEN p.callname ELSE p.callname END as callname ");
142 /* q.append(" ( select (p.lastname || ' ' || p.callname) as personname from ");
143 q.append("reservationperson as rp join person as p on rp.personid=p.personid ");
144 q.append(" where r1.reservationid=rp.reservationid ");
145 q.append(" and rp.reservationroleid=3 ");
146 q.append(" and rp.deleted='f' ");
147 q.append("limit 1 ");
148 q.append(" ) as personname ");*/
149
150 q.append(" FROM space, ");
151 q.append(" reservation as r1,event as e1,organisation LEFT OUTER JOIN organisationtranslation ");
152 q.append(" ON organisation.organisationid=organisationtranslation.organisationid, ");
153 q.append(" (eventgroup left outer join courseinstance on ");
154 q.append(" eventgroup.courseinstanceid=courseinstance.courseinstanceid LEFT OUTER ");
155 q.append(" JOIN course on courseinstance.courseid=course.courseid),requeststate ");
156
157 // for person names
158 q.append(" ,reservationperson as rp join person as p on rp.personid=p.personid ");
159
160 q.append(" WHERE r1.requeststateid=requeststate.requeststateid AND ");
161
162 // for person names
163 q.append(" r1.reservationid=rp.reservationid ");
164 q.append(" and rp.reservationroleid=3 ");
165 q.append(" and rp.deleted='f' and ");
166
167 q.append(" r1.eventid=e1.eventid and e1.eventgroupid= ");
168 q.append(" eventgroup.eventgroupid and requeststate.requeststateid<");
169 if (getOverlappingList) q.append("4"); else q.append("3 ");
170 q.append(" and r1.spaceid=space.spaceid and ");
171 q.append(" r1.resorganisationid=organisation.organisationid and ");
172 q.append(" organisationtranslation.languageid=2 and space.deleted=false and ");
173 q.append(" r1.deleted=false and e1.deleted=false and ");
174 q.append(" organisation.deleted=false ");
175 if (getOverlappingList) {
176 q.append(" and r1.reservationid in ( SELECT r1.reservationid ");
177 q.append(" FROM reservation as r2, event as e2 ");
178 q.append(" WHERE r1.spaceid = r2.spaceid ");
179 q.append(" AND r2.reservationid ="+id);
180 q.append(" AND e2.eventid = r2.eventid ");
181 q.append(" AND r1.inconsistent =0 ");
182 q.append(" AND r2.inconsistent =0 ");
183 q.append(" AND r1.requeststateid<4 ");
184 q.append(" AND r2.requeststateid<4 ");
185 q.append(" AND r2.deleted=false ");
186 // q.append(" AND e2.begintime>NOW() "); // also past reservations may overlap
187 q.append(" AND (e1.begintime,e1.endtime) OVERLAPS (e2.begintime,e2.endtime) ) ");
188 }
189 q.append(getOrderClause());
190 return q.toString();
191 }
192
193 /** Returns all active reservations active since now.
194 * @param db open db connection
195 * @throws Exception if problems with db connections
196 * @return reservation info
197 */
198 public RS2 getReservations(DB db) throws Exception {
199 return new RS2(db.executeQuery(getReservationSql()));
200 }
201
202 /**
203 * returns person's reservations
204 * @param db open db connection
205 * @param personId person whose reservations we are searching
206 * @throws Exception if problems with db connections
207 * @return reservation info
208 */
209 public RS2 getReservations(DB db,int personId) throws Exception {
210 StringBuffer q = new StringBuffer(100);
211 q.append("SELECT DISTINCT UPPER(space.code) as ord_code, r1.reservationid as reservation_id,requeststate.name as state, CASE WHEN course.code IS NOT NULL THEN course.code ELSE '-' END as kurssikoodi, ");
212 q.append("(CASE WHEN (event.name NOT LIKE '' AND event.name IS NOT NULL) THEN event.name ");
213 q.append("WHEN (eventgroup.name NOT LIKE '' AND eventgroup.name IS NOT NULL) THEN eventgroup.name ");
214 q.append("ELSE '' END) AS eventname,");
215 q.append("UPPER(CASE WHEN (event.name NOT LIKE '' AND event.name IS NOT NULL) THEN event.name ");
216 q.append("WHEN (eventgroup.name NOT LIKE '' AND eventgroup.name IS NOT NULL) THEN eventgroup.name ");
217 q.append("ELSE '' END) AS ord_eventname,");
218 q.append("CASE WHEN rp1.personid IS NOT NULL THEN p1.lastname ELSE p2.lastname END as personname, ");
219 q.append("CASE WHEN rp1.personid IS NOT NULL THEN p1.callname ELSE p2.callname END as callname, ");
220 q.append("space.code as code ,begintime,endtime, space.spaceid as spaceid,event.eventid as eventid, 0 as organisation, 0 as overlappings FROM space, reservation as r1,");
221 q.append("reservation as r2, event left outer join eventgroup on ");
222 q.append("event.eventgroupid=eventgroup.eventgroupid left outer join courseinstance ");
223 q.append("ON eventgroup.courseinstanceid = courseinstance.courseinstanceid ");
224 q.append("left outer join course on courseinstance.courseid=course.courseid,");
225 q.append("(reservationperson as rp2 left outer join reservationperson as rp1 on ");
226 q.append("(rp1.reservationid=rp2.reservationid and rp1.reservationroleid=3) left outer join person as p1 on ");
227 q.append("(rp1.personid=p1.personid)),person as p2 where r1.spaceid=space.spaceid and ");
228 q.append("r1.requeststateid=requeststate.requeststateid and r1.eventid=event.eventid and ");
229 q.append("r1.reservationid=r2.reservationid and ");
230 q.append("r2.reservationid=rp2.reservationid and rp2.personid="+personId+" and r1.deleted='false' and r2.deleted='false' ");
231 q.append("and rp2.deleted='false' and rp2.personid=p2.personid");
232 if (!getOldReservations()) q.append(" and event.begintime>NOW()");
233 q.append(getOrderClause());
234 return new RS2(db.executeQuery(q.toString()));
235 }
236
237
238 /** Default implementation of clearing action state. Descendant classes may
239 * override this. No implementation in this class.
240 * @see SearchHandler#doClearActionState(HttpServletRequest)
241 * @param request The request-object of the JSP.
242 */
243 protected void doClearActionState(javax.servlet.http.HttpServletRequest request) {
244 }
245
246 /** Ensures empty request parameters are clearer when entity is posted.
247 * <p>
248 * If bean is used in a JSP, this should be called in the beginning of page.
249 * If form content is "" or null it is not sent via HTTP, so those fields
250 * must be cleared manually. No action yet.
251 *
252 * @param request HTTP request with parameters
253 * @see SearchHandler#clearEmptyParameters(HttpServletRequest)
254 */
255 protected void doClearEmptyParameters(javax.servlet.http.HttpServletRequest request) {
256 }
257
258 /**
259 * No implementation yet.
260 */
261 protected void defaultAction() {
262 }
263
264 /**
265 * No implementation yet.
266 */
267 public void submitSearch() {
268 }
269
270 /**
271 * No implementation yet.
272 */
273 public void resetSearch() {
274 }
275 }
276 /***************************************************************************************************
277 * COPYRIGHT (C) KIURU -PROJECT GROUP
278 * Limited rights granted. Please refer to license.
279 **************************************************************************************************/