001: /*
002: Copyright (C) 2003 Know Gate S.L. All rights reserved.
003: C/Oña, 107 1º2 28050 Madrid (Spain)
004:
005: Redistribution and use in source and binary forms, with or without
006: modification, are permitted provided that the following conditions
007: are met:
008:
009: 1. Redistributions of source code must retain the above copyright
010: notice, this list of conditions and the following disclaimer.
011:
012: 2. The end-user documentation included with the redistribution,
013: if any, must include the following acknowledgment:
014: "This product includes software parts from hipergate
015: (http://www.hipergate.org/)."
016: Alternately, this acknowledgment may appear in the software itself,
017: if and wherever such third-party acknowledgments normally appear.
018:
019: 3. The name hipergate must not be used to endorse or promote products
020: derived from this software without prior written permission.
021: Products derived from this software may not be called hipergate,
022: nor may hipergate appear in their name, without prior written
023: permission.
024:
025: This library is distributed in the hope that it will be useful,
026: but WITHOUT ANY WARRANTY; without even the implied warranty of
027: MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
028:
029: You should have received a copy of hipergate License with this code;
030: if not, visit http://www.hipergate.org or mail to info@hipergate.org
031: */
032:
033: package com.knowgate.addrbook;
034:
035: import java.util.Date;
036:
037: import java.sql.SQLException;
038: import java.sql.CallableStatement;
039: import java.sql.PreparedStatement;
040: import java.sql.Statement;
041: import java.sql.ResultSet;
042: import java.sql.Timestamp;
043:
044: import com.knowgate.debug.DebugFile;
045: import com.knowgate.misc.Gadgets;
046: import com.knowgate.jdc.JDCConnection;
047: import com.knowgate.dataobjs.DB;
048: import com.knowgate.dataobjs.DBBind;
049: import com.knowgate.dataobjs.DBSubset;
050: import com.knowgate.dataobjs.DBPersist;
051:
052: /**
053: * <p>Meeting for Fellows</p>
054: * @author Sergio Montoro Ten
055: * @version 3.0
056: */
057:
058: public class Meeting extends DBPersist {
059:
060: public Meeting() {
061: super (DB.k_meetings, "Meeting");
062: }
063:
064: public Meeting(String sMeetingId) {
065: super (DB.k_meetings, "Meeting");
066:
067: put(DB.gu_meeting, sMeetingId);
068: }
069:
070: public Meeting(JDCConnection oConn, String sMeetingId)
071: throws SQLException {
072: super (DB.k_meetings, "Meeting");
073:
074: load(oConn, new Object[] { sMeetingId });
075: }
076:
077: // ----------------------------------------------------------
078:
079: public Date lastModified() {
080: Date dtLastMod = isNull(DB.dt_modified) ? getDate(DB.dt_created)
081: : getDate(DB.dt_modified);
082: if (null == dtLastMod)
083: dtLastMod = getDate(DB.dt_start);
084: return dtLastMod;
085: }
086:
087: // ----------------------------------------------------------
088:
089: public boolean delete(JDCConnection oConn) throws SQLException {
090: return Meeting.delete(oConn, getString(DB.gu_meeting));
091: }
092:
093: // ----------------------------------------------------------
094:
095: /**
096: * <p>Store meeting</p>
097: * gu_meeting is automatically assigned if not set for new meetings<br>
098: * dt_modified is automatically updated for existing meetings<br>
099: * bo_private is set to zero if value for it is not specified
100: * @param oConn JDCConnection
101: * @return boolean
102: * @throws SQLException
103: */
104: public boolean store(JDCConnection oConn) throws SQLException {
105:
106: // Si no se especificó un identificador para el encuentro, entonces añadirlo automáticamente
107: if (!AllVals.containsKey(DB.gu_meeting))
108: put(DB.gu_meeting, Gadgets.generateUUID());
109: else
110: replace(DB.dt_modified, new Timestamp(new Date().getTime()));
111:
112: if (!AllVals.containsKey(DB.bo_private))
113: put(DB.bo_private, (short) 0);
114:
115: return super .store(oConn);
116: } // store
117:
118: // ----------------------------------------------------------
119:
120: /**
121: * <p>Get Fellows attending to this Meeting</p>
122: * <p>Retrieves Fellows from k_x_meeting_fellow table.</p>
123: * @param oConn Database Connection
124: * @return {@link DBSubset} with 3 columns {gu_fellow,tx_name,tx_surname}
125: * @throws SQLException
126: * @see {@link Fellow}
127: */
128: public DBSubset getFellows(JDCConnection oConn) throws SQLException {
129:
130: DBSubset oFellows = new DBSubset(DB.k_fellows + " f,"
131: + DB.k_x_meeting_fellow + " x", " f." + DB.gu_fellow
132: + ",f." + DB.tx_name + ",f." + DB.tx_surname, "f."
133: + DB.gu_fellow + "=x." + DB.gu_fellow + " AND "
134: + DB.gu_meeting + "=?", 4);
135:
136: oFellows.load(oConn, new Object[] { getString(DB.gu_meeting) });
137:
138: return oFellows;
139:
140: } // getFellows
141:
142: // ----------------------------------------------------------
143:
144: /**
145: * <p>External Enterprise Contacts Attending to this meeting</p>
146: * <p>Retrieves Contacts from k_x_meeting_contact table.</p>
147: * @param oConn Database Connection
148: * @return {@link DBSubset} with 3 columns {gu_contact,tx_name,tx_surname}
149: * @throws SQLException
150: * @see {@link Contact}
151: */
152:
153: public DBSubset getContacts(JDCConnection oConn)
154: throws SQLException {
155:
156: DBSubset oContacts = new DBSubset(DB.k_contacts + " c,"
157: + DB.k_x_meeting_contact + " x", " c." + DB.gu_contact
158: + ",c." + DB.tx_name + ",c." + DB.tx_surname, "c."
159: + DB.gu_contact + "=x." + DB.gu_contact + " AND x."
160: + DB.gu_meeting + "=?", 4);
161:
162: oContacts
163: .load(oConn, new Object[] { getString(DB.gu_meeting) });
164:
165: return oContacts;
166:
167: } // getContacts
168:
169: // ----------------------------------------------------------
170:
171: /**
172: * <p>Get Rooms and other Resources reserved for thsi meeting<.</p>
173: * <p>Retrieves Rooms from k_x_meeting_room table.</p>
174: * @param oConn Database Connection
175: * @return {@link DBSubset} with 5 columns {nm_room,tp_room,tx_company,tx_location,tx_comments}
176: * @throws SQLException
177: */
178:
179: public DBSubset getRooms(JDCConnection oConn) throws SQLException {
180:
181: DBSubset oRooms = new DBSubset(DB.k_rooms + " r,"
182: + DB.k_x_meeting_room + " x", " r." + DB.nm_room
183: + ",r." + DB.tp_room + ",r." + DB.tx_company + ",r."
184: + DB.tx_location + ",r." + DB.tx_comments, "r."
185: + DB.nm_room + "=x." + DB.nm_room + " AND x."
186: + DB.gu_meeting + "=?", 1);
187:
188: oRooms.load(oConn, new Object[] { getString(DB.gu_meeting) });
189:
190: return oRooms;
191: } // getRooms
192:
193: // ----------------------------------------------------------
194:
195: /**
196: * @return Meeting starting hour
197: */
198:
199: public String getHour() {
200: return String.valueOf(getDate(DB.dt_start).getHours());
201: } // getHour
202:
203: // ----------------------------------------------------------
204:
205: /**
206: * @return Meeting starting minute
207: */
208: public String getMinute() {
209:
210: String sMins = String
211: .valueOf(getDate(DB.dt_start).getMinutes());
212: return sMins.length() == 1 ? "0" + sMins : sMins;
213: } // getMinute
214:
215: // ----------------------------------------------------------
216:
217: /**
218: * @return Meeting ending hour
219: */
220: public String getHourEnd() {
221: return String.valueOf(getDate(DB.dt_end).getHours());
222: } // getHourEnd
223:
224: // ----------------------------------------------------------
225:
226: /**
227: * @return Meeting ending minute
228: */
229:
230: public String getMinuteEnd() {
231:
232: String sMins = String.valueOf(getDate(DB.dt_end).getMinutes());
233:
234: return sMins.length() == 1 ? "0" + sMins : sMins;
235: } // getMinuteEnd
236:
237: // ----------------------------------------------------------
238:
239: /**
240: * <p>Remove all attendants to Meeting</p>
241: * <p>Delete registers from k_x_meeting_fellow and k_x_meeting_contact tables</p>
242: * @param oConn Database Connection
243: * @throws SQLException
244: */
245:
246: public void clearAttendants(JDCConnection oConn)
247: throws SQLException {
248: Statement oStmt = oConn.createStatement();
249: oStmt.executeUpdate("DELETE FROM " + DB.k_x_meeting_fellow
250: + " WHERE " + DB.gu_meeting + "='"
251: + getString(DB.gu_meeting) + "'");
252: oStmt.executeUpdate("DELETE FROM " + DB.k_x_meeting_contact
253: + " WHERE " + DB.gu_meeting + "='"
254: + getString(DB.gu_meeting) + "'");
255: oStmt.executeUpdate("UPDATE " + DB.k_meetings + " SET "
256: + DB.dt_modified + "=" + DBBind.Functions.GETDATE);
257: oStmt.close();
258:
259: } // clearAttendants
260:
261: // ----------------------------------------------------------
262:
263: /**
264: * <p>Remove all Rooms and other booked Resources for Meeting</p>
265: * Delete registers from k_x_meeting_room
266: * @param oConn Database Connection
267: * @throws SQLException
268: */
269:
270: public void clearRooms(JDCConnection oConn) throws SQLException {
271: Statement oStmt = oConn.createStatement();
272: oStmt.executeUpdate("DELETE FROM " + DB.k_x_meeting_room
273: + " WHERE " + DB.gu_meeting + "='"
274: + getString(DB.gu_meeting) + "'");
275: oStmt.executeUpdate("UPDATE " + DB.k_meetings + " SET "
276: + DB.dt_modified + "=" + DBBind.Functions.GETDATE);
277: oStmt.close();
278: } // clearRooms
279:
280: // ----------------------------------------------------------
281:
282: /**
283: * <p>Assign a Room or Resource to a Meeting</p>
284: * <p>If Room was already assigned, booking dates (dt_start and dt_end at k_x_meeting_room)
285: * are updated and no error is raised.</p>
286: * @param oConn Database Connection
287: * @param sRoomNm Room Name
288: * @throws SQLException
289: */
290:
291: public void setRoom(JDCConnection oConn, String sRoomNm)
292: throws SQLException {
293: boolean bBooked;
294: PreparedStatement oStmt;
295: Statement oExec;
296: ResultSet oRSet;
297: String sSQL;
298:
299: if (DebugFile.trace) {
300: DebugFile.writeln("Begin Meeting.setRoom([Connection], "
301: + sRoomNm + ")");
302: DebugFile.incIdent();
303: }
304:
305: oStmt = oConn
306: .prepareStatement(
307: "SELECT NULL FROM " + DB.k_x_meeting_room
308: + " WHERE " + DB.gu_meeting + "=? AND "
309: + DB.nm_room + "=?",
310: ResultSet.TYPE_FORWARD_ONLY,
311: ResultSet.CONCUR_READ_ONLY);
312: oStmt.setString(1, getString(DB.gu_meeting));
313: oStmt.setString(2, sRoomNm);
314: oRSet = oStmt.executeQuery();
315: bBooked = oRSet.next();
316: oRSet.close();
317: oStmt.close();
318:
319: if (bBooked) {
320: sSQL = "UPDATE " + DB.k_x_meeting_room + " SET "
321: + DB.dt_start + "="
322: + DBBind.escape(getDate(DB.dt_start), "ts") + ","
323: + DB.dt_end + "="
324: + DBBind.escape(getDate(DB.dt_end), "ts")
325: + " WHERE " + DB.gu_meeting + "='"
326: + getString(DB.gu_meeting) + "' AND " + DB.nm_room
327: + "='" + sRoomNm + "'";
328: oExec = oConn.createStatement();
329: if (DebugFile.trace)
330: DebugFile.writeln("Statement.execute(" + sSQL + ")");
331: oExec.execute(sSQL);
332: oExec.close();
333: } else {
334: sSQL = "INSERT INTO " + DB.k_x_meeting_room + "("
335: + DB.gu_meeting + "," + DB.nm_room + ","
336: + DB.dt_start + "," + DB.dt_end + ") VALUES ('"
337: + getString(DB.gu_meeting) + "','" + sRoomNm + "',"
338: + DBBind.escape(getDate(DB.dt_start), "ts") + ","
339: + DBBind.escape(getDate(DB.dt_end), "ts") + ")";
340: oExec = oConn.createStatement();
341: if (DebugFile.trace)
342: DebugFile.writeln("Statement.execute(" + sSQL + ")");
343: oExec.execute(sSQL);
344: oExec.close();
345: }
346:
347: Statement oUpdt = oConn.createStatement();
348: oUpdt.executeUpdate("UPDATE " + DB.k_meetings + " SET "
349: + DB.dt_modified + "=" + DBBind.Functions.GETDATE);
350: oUpdt.close();
351:
352: if (DebugFile.trace) {
353: DebugFile.decIdent();
354: DebugFile.writeln("End Meeting.setRoom()");
355: }
356: } // setRoom
357:
358: // ----------------------------------------------------------
359:
360: /**
361: * <p>Assign Attendant to Meeting</p>
362: * <p>Attendants may be Fellows or Contacts</p>
363: * <p>If Attendant was already assigned, meeting dates are updated and no error is raised.</p>
364: * @param oConn Database Connection
365: * @param sAttendantId {@link Fellow} or {@link Contact} Unique Identifier
366: * @throws SQLException
367: */
368:
369: public void setAttendant(JDCConnection oConn, String sAttendantId)
370: throws SQLException {
371: boolean bAttends;
372: boolean bExists;
373: PreparedStatement oStmt;
374: Statement oExec;
375: ResultSet oRSet;
376: String sSQL;
377:
378: if (DebugFile.trace) {
379: DebugFile
380: .writeln("Begin Meeting.setAttendant([Connection], "
381: + sAttendantId + ")");
382: DebugFile.incIdent();
383: }
384:
385: oStmt = oConn
386: .prepareStatement("SELECT NULL FROM "
387: + DB.k_x_meeting_fellow + " WHERE "
388: + DB.gu_meeting + "=? AND " + DB.gu_fellow
389: + "=?", ResultSet.TYPE_FORWARD_ONLY,
390: ResultSet.CONCUR_READ_ONLY);
391: oStmt.setString(1, getString(DB.gu_meeting));
392: oStmt.setString(2, sAttendantId);
393: oRSet = oStmt.executeQuery();
394: bAttends = oRSet.next();
395: oRSet.close();
396: oStmt.close();
397:
398: if (bAttends) {
399: sSQL = "UPDATE " + DB.k_x_meeting_fellow + " SET "
400: + DB.dt_start + "="
401: + DBBind.escape(getTimestamp(DB.dt_start), "ts")
402: + "," + DB.dt_end + "="
403: + DBBind.escape(getTimestamp(DB.dt_end), "ts")
404: + " WHERE " + DB.gu_meeting + "='"
405: + getString(DB.gu_meeting) + "' AND "
406: + DB.gu_fellow + "='" + sAttendantId + "'";
407: oExec = oConn.createStatement();
408:
409: if (DebugFile.trace)
410: DebugFile.writeln("Statement.execute(" + sSQL + ")");
411: oExec.execute(sSQL);
412: oExec.close();
413: } else {
414: sSQL = "SELECT NULL FROM " + DB.k_x_meeting_contact
415: + " WHERE " + DB.gu_meeting + "=? AND "
416: + DB.gu_contact + "=?";
417: if (DebugFile.trace)
418: DebugFile.writeln("Connection.prepareStatement( "
419: + sSQL + ")");
420: oStmt = oConn.prepareStatement(sSQL,
421: ResultSet.TYPE_FORWARD_ONLY,
422: ResultSet.CONCUR_READ_ONLY);
423: oStmt.setString(1, getString(DB.gu_meeting));
424: oStmt.setString(2, sAttendantId);
425: oRSet = oStmt.executeQuery();
426: bAttends = oRSet.next();
427: oRSet.close();
428: oStmt.close();
429:
430: if (bAttends) {
431: sSQL = "UPDATE "
432: + DB.k_x_meeting_contact
433: + " SET "
434: + DB.dt_start
435: + "="
436: + DBBind
437: .escape(getTimestamp(DB.dt_start), "ts")
438: + "," + DB.dt_end + "="
439: + DBBind.escape(getTimestamp(DB.dt_end), "ts")
440: + " WHERE " + DB.gu_meeting + "='"
441: + getString(DB.gu_meeting) + "' AND "
442: + DB.gu_contact + "='" + sAttendantId + "'";
443: oExec = oConn.createStatement();
444: if (DebugFile.trace)
445: DebugFile
446: .writeln("Statement.execute(" + sSQL + ")");
447: oExec.execute(sSQL);
448: oExec.close();
449: } else {
450: sSQL = "SELECT NULL FROM " + DB.k_fellows + " WHERE "
451: + DB.gu_fellow + "=?";
452: if (DebugFile.trace)
453: DebugFile.writeln("Connection.prepareStatement( "
454: + sSQL + ")");
455: oStmt = oConn.prepareStatement(sSQL,
456: ResultSet.TYPE_FORWARD_ONLY,
457: ResultSet.CONCUR_READ_ONLY);
458: oStmt.setString(1, sAttendantId);
459: oRSet = oStmt.executeQuery();
460: bExists = oRSet.next();
461: oRSet.close();
462: oStmt.close();
463:
464: if (bExists) {
465: sSQL = "INSERT INTO "
466: + DB.k_x_meeting_fellow
467: + "("
468: + DB.gu_meeting
469: + ","
470: + DB.gu_fellow
471: + ","
472: + DB.dt_start
473: + ","
474: + DB.dt_end
475: + ") VALUES ('"
476: + getString(DB.gu_meeting)
477: + "','"
478: + sAttendantId
479: + "',"
480: + DBBind.escape(getTimestamp(DB.dt_start),
481: "ts")
482: + ","
483: + DBBind.escape(getTimestamp(DB.dt_end),
484: "ts") + ")";
485: oExec = oConn.createStatement();
486: if (DebugFile.trace)
487: DebugFile.writeln("Statement.execute(" + sSQL
488: + ")");
489: oExec.execute(sSQL);
490: oExec.close();
491: } else {
492: sSQL = "SELECT NULL FROM " + DB.k_contacts
493: + " WHERE " + DB.gu_contact + "=?";
494: if (DebugFile.trace)
495: DebugFile
496: .writeln("Connection.prepareStatement( "
497: + sSQL + ")");
498: oStmt = oConn.prepareStatement(sSQL,
499: ResultSet.TYPE_FORWARD_ONLY,
500: ResultSet.CONCUR_READ_ONLY);
501: oStmt.setString(1, sAttendantId);
502: oRSet = oStmt.executeQuery();
503: bExists = oRSet.next();
504: oRSet.close();
505: oStmt.close();
506:
507: if (bExists) {
508: sSQL = "INSERT INTO "
509: + DB.k_x_meeting_contact
510: + "("
511: + DB.gu_meeting
512: + ","
513: + DB.gu_contact
514: + ","
515: + DB.dt_start
516: + ","
517: + DB.dt_end
518: + ") VALUES ('"
519: + getString(DB.gu_meeting)
520: + "','"
521: + sAttendantId
522: + "',"
523: + DBBind
524: .escape(
525: getTimestamp(DB.dt_start),
526: "ts")
527: + ","
528: + DBBind.escape(
529: getTimestamp(DB.dt_end), "ts")
530: + ")";
531: oExec = oConn.createStatement();
532: if (DebugFile.trace)
533: DebugFile.writeln("Statement.execute("
534: + sSQL + ")");
535: oExec.execute(sSQL);
536: oExec.close();
537: }
538: }
539: }
540: } // fi (bAttends)
541:
542: Statement oUpdt = oConn.createStatement();
543: oUpdt.executeUpdate("UPDATE " + DB.k_meetings + " SET "
544: + DB.dt_modified + "=" + DBBind.Functions.GETDATE);
545: oUpdt.close();
546:
547: if (DebugFile.trace) {
548: DebugFile.decIdent();
549: DebugFile.writeln("End Meeting.setAttendant()");
550: }
551: } // setAttendant
552:
553: /**
554: * Make copies of this meeting
555: * @param oConn JDCConnection
556: * @param nDaysGap int Frecuency in days. One means that the meeting is repeated
557: * daily for the number of specified times. Seven means that the meeting is repeated weekly.
558: * Twenty eight is approximately once per month (every 4 weeks).
559: * @param nTimes int Number of times to repeat the meeting
560: * @throws SQLException
561: * @throws IllegalArgumentException if nDaysGap<=0 or nTimes<0
562: */
563: public void repeat(JDCConnection oConn, int nDaysGap, int nTimes,
564: boolean bSkipHolidays) throws SQLException,
565: IllegalArgumentException {
566: if (DebugFile.trace) {
567: DebugFile.writeln("Begin Meeting.repeat([Connection], "
568: + String.valueOf(nDaysGap) + ","
569: + String.valueOf(nTimes) + ")");
570: DebugFile.incIdent();
571: }
572: if (nDaysGap <= 0)
573: throw new IllegalArgumentException(
574: "Meeting.repeat() days gap must be an integer greater than zero");
575: if (nTimes < 0)
576: throw new IllegalArgumentException(
577: "Meeting.repeat() days gap must be an integer greater than or equal to zero");
578: final long lDaysGap = ((long) nDaysGap) * 86400000l;
579: Date dtStart, dtEnd;
580: Meeting oClone = new Meeting();
581: oClone.clone(this );
582: DBSubset oConts = getContacts(oConn);
583: DBSubset oFells = getFellows(oConn);
584: DBSubset oRooms = getRooms(oConn);
585: long lStart = getDate(DB.dt_start).getTime() + lDaysGap;
586: long lEnd = getDate(DB.dt_end).getTime() + lDaysGap;
587: for (int t = 0; t < nTimes; t++) {
588: dtStart = new Date(lStart);
589: dtEnd = new Date(lEnd);
590: oClone.replace(DB.gu_meeting, Gadgets.generateUUID());
591: oClone.replace(DB.dt_start, dtStart);
592: oClone.replace(DB.dt_end, dtEnd);
593: // Skip Saturday and Sunday
594: if (!bSkipHolidays
595: || (dtStart.getDay() != 0 && dtStart.getDay() != 6)) {
596: oClone.store(oConn);
597: for (int c = 0; c < oConts.getRowCount(); c++)
598: oClone.setAttendant(oConn, oConts.getString(0, c));
599: for (int f = 0; f < oFells.getRowCount(); f++)
600: oClone.setAttendant(oConn, oFells.getString(0, f));
601: for (int r = 0; r < oRooms.getRowCount(); r++)
602: oClone.setRoom(oConn, oRooms.getString(0, r));
603: } // fi (!bSkipHolidays || (getDay()!=0 && getDay()!=6))
604: lStart += lDaysGap;
605: lEnd += lDaysGap;
606: } // next
607: if (DebugFile.trace) {
608: DebugFile.decIdent();
609: DebugFile.writeln("End Meeting.repeat()");
610: }
611: } // repeat
612:
613: // **********************************************************
614: // Static Methods
615:
616: /**
617: * <p>Delete Meeting</p>
618: * Calls k_sp_del_meeting stored procedure
619: * @param oConn Database Connection
620: * @param sMeetingGUID Meeting Unique Identifier
621: * @throws SQLException
622: */
623:
624: public static boolean delete(JDCConnection oConn,
625: String sMeetingGUID) throws SQLException {
626: boolean bRetVal;
627:
628: CallableStatement oCall = oConn
629: .prepareCall("{call k_sp_del_meeting ('" + sMeetingGUID
630: + "')}");
631: bRetVal = oCall.execute();
632: oCall.close();
633:
634: return bRetVal;
635: } // delete
636:
637: // **********************************************************
638: // Public Constants
639:
640: public static final short ClassId = 21;
641: }
|