1 32 33 package com.knowgate.addrbook; 34 35 import java.util.Date ; 36 37 import java.sql.SQLException ; 38 import java.sql.CallableStatement ; 39 import java.sql.PreparedStatement ; 40 import java.sql.Statement ; 41 import java.sql.ResultSet ; 42 import java.sql.Timestamp ; 43 44 import com.knowgate.debug.DebugFile; 45 import com.knowgate.misc.Gadgets; 46 import com.knowgate.jdc.JDCConnection; 47 import com.knowgate.dataobjs.DB; 48 import com.knowgate.dataobjs.DBBind; 49 import com.knowgate.dataobjs.DBSubset; 50 import com.knowgate.dataobjs.DBPersist; 51 52 57 58 public class Meeting extends DBPersist { 59 60 public Meeting() { 61 super(DB.k_meetings, "Meeting"); 62 } 63 64 public Meeting(String sMeetingId) { 65 super(DB.k_meetings, "Meeting"); 66 67 put(DB.gu_meeting, sMeetingId); 68 } 69 70 public Meeting(JDCConnection oConn, String sMeetingId) throws SQLException { 71 super(DB.k_meetings, "Meeting"); 72 73 load(oConn, new Object [] {sMeetingId}); 74 } 75 76 78 public Date lastModified() { 79 Date dtLastMod = isNull(DB.dt_modified) ? getDate(DB.dt_created) : getDate(DB.dt_modified); 80 if (null==dtLastMod) dtLastMod = getDate(DB.dt_start); 81 return dtLastMod; 82 } 83 84 86 public boolean delete(JDCConnection oConn) throws SQLException { 87 return Meeting.delete(oConn, getString(DB.gu_meeting)); 88 } 89 90 92 101 public boolean store(JDCConnection oConn) throws SQLException { 102 103 if (!AllVals.containsKey(DB.gu_meeting)) 105 put(DB.gu_meeting, Gadgets.generateUUID()); 106 else 107 replace(DB.dt_modified, new Timestamp (new Date ().getTime())); 108 109 if (!AllVals.containsKey(DB.bo_private)) 110 put(DB.bo_private, (short)0); 111 112 return super.store(oConn); 113 } 115 117 125 public DBSubset getFellows(JDCConnection oConn) throws SQLException { 126 127 DBSubset oFellows = new DBSubset(DB.k_fellows + " f," + DB.k_x_meeting_fellow + " x", 128 " f." + DB.gu_fellow + ",f." + DB.tx_name + ",f." + DB.tx_surname, 129 "f." + DB.gu_fellow + "=x." + DB.gu_fellow + " AND " + DB.gu_meeting + "=?", 4); 130 131 oFellows.load(oConn, new Object [] { getString(DB.gu_meeting) }); 132 133 return oFellows; 134 135 } 137 139 147 148 public DBSubset getContacts(JDCConnection oConn) throws SQLException { 149 150 DBSubset oContacts = new DBSubset(DB.k_contacts + " c," + DB.k_x_meeting_contact + " x", 151 " c." + DB.gu_contact + ",c." + DB.tx_name + ",c." + DB.tx_surname, 152 "c." + DB.gu_contact + "=x." + DB.gu_contact + " AND x." + DB.gu_meeting + "=?", 4); 153 154 oContacts.load(oConn, new Object [] { getString(DB.gu_meeting) }); 155 156 return oContacts; 157 158 } 160 162 169 170 public DBSubset getRooms(JDCConnection oConn) throws SQLException { 171 172 DBSubset oRooms = new DBSubset(DB.k_rooms + " r," + DB.k_x_meeting_room + " x", 173 " r." + DB.nm_room + ",r." + DB.tp_room + ",r." + DB.tx_company + ",r." + DB.tx_location + ",r." + DB.tx_comments, 174 "r."+ DB.nm_room + "=x." + DB.nm_room + " AND x." + DB.gu_meeting + "=?", 1); 175 176 oRooms.load(oConn, new Object [] { getString(DB.gu_meeting) }); 177 178 return oRooms; 179 } 181 183 186 187 public String getHour() { 188 return String.valueOf(getDate(DB.dt_start).getHours()); 189 } 191 193 196 public String getMinute() { 197 198 String sMins = String.valueOf(getDate(DB.dt_start).getMinutes()); 199 return sMins.length()==1 ? "0" + sMins : sMins; 200 } 202 204 207 public String getHourEnd() { 208 return String.valueOf(getDate(DB.dt_end).getHours()); 209 } 211 213 216 217 public String getMinuteEnd() { 218 219 String sMins = String.valueOf(getDate(DB.dt_end).getMinutes()); 220 221 return sMins.length()==1 ? "0" + sMins : sMins; 222 } 224 226 232 233 public void clearAttendants(JDCConnection oConn) throws SQLException { 234 Statement oStmt = oConn.createStatement(); 235 oStmt.executeUpdate("DELETE FROM " + DB.k_x_meeting_fellow + " WHERE " + DB.gu_meeting + "='" + getString(DB.gu_meeting) + "'"); 236 oStmt.executeUpdate("DELETE FROM " + DB.k_x_meeting_contact + " WHERE " + DB.gu_meeting + "='" + getString(DB.gu_meeting) + "'"); 237 oStmt.executeUpdate("UPDATE " + DB.k_meetings + " SET " + DB.dt_modified + "=" + DBBind.Functions.GETDATE); 238 oStmt.close(); 239 240 } 242 244 250 251 public void clearRooms(JDCConnection oConn) throws SQLException { 252 Statement oStmt = oConn.createStatement(); 253 oStmt.executeUpdate("DELETE FROM " + DB.k_x_meeting_room + " WHERE " + DB.gu_meeting + "='" + getString(DB.gu_meeting) + "'"); 254 oStmt.executeUpdate("UPDATE " + DB.k_meetings + " SET " + DB.dt_modified + "=" + DBBind.Functions.GETDATE); 255 oStmt.close(); 256 } 258 260 268 269 public void setRoom(JDCConnection oConn, String sRoomNm) throws SQLException { 270 boolean bBooked; 271 PreparedStatement oStmt; 272 Statement oExec; 273 ResultSet oRSet; 274 String sSQL; 275 276 if (DebugFile.trace) { 277 DebugFile.writeln("Begin Meeting.setRoom([Connection], " + sRoomNm + ")"); 278 DebugFile.incIdent(); 279 } 280 281 oStmt = oConn.prepareStatement("SELECT NULL FROM " + DB.k_x_meeting_room + " WHERE " + DB.gu_meeting + "=? AND " + DB.nm_room + "=?", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); 282 oStmt.setString(1,getString(DB.gu_meeting)); 283 oStmt.setString(2,sRoomNm); 284 oRSet = oStmt.executeQuery(); 285 bBooked = oRSet.next(); 286 oRSet.close(); 287 oStmt.close(); 288 289 if (bBooked) { 290 sSQL = "UPDATE " + DB.k_x_meeting_room + " SET " + DB.dt_start + "=" + DBBind.escape(getDate(DB.dt_start), "ts") + "," + DB.dt_end + "=" + DBBind.escape(getDate(DB.dt_end), "ts") + " WHERE " + DB.gu_meeting + "='" + getString(DB.gu_meeting) + "' AND " + DB.nm_room + "='" + sRoomNm + "'"; 291 oExec = oConn.createStatement(); 292 if (DebugFile.trace) DebugFile.writeln("Statement.execute(" + sSQL + ")"); 293 oExec.execute(sSQL); 294 oExec.close(); 295 } 296 else { 297 sSQL = "INSERT INTO " + DB.k_x_meeting_room + "(" + DB.gu_meeting + "," + DB.nm_room + "," + DB.dt_start + "," + DB.dt_end + ") VALUES ('" + getString(DB.gu_meeting) + "','" + sRoomNm + "'," + DBBind.escape(getDate(DB.dt_start), "ts") + "," + DBBind.escape(getDate(DB.dt_end), "ts") + ")"; 298 oExec = oConn.createStatement(); 299 if (DebugFile.trace) DebugFile.writeln("Statement.execute(" + sSQL + ")"); 300 oExec.execute(sSQL); 301 oExec.close(); 302 } 303 304 Statement oUpdt = oConn.createStatement(); 305 oUpdt.executeUpdate("UPDATE " + DB.k_meetings + " SET " + DB.dt_modified + "=" + DBBind.Functions.GETDATE); 306 oUpdt.close(); 307 308 if (DebugFile.trace) { 309 DebugFile.decIdent(); 310 DebugFile.writeln("End Meeting.setRoom()"); 311 } 312 } 314 316 324 325 public void setAttendant(JDCConnection oConn, String sAttendantId) throws SQLException { 326 boolean bAttends; 327 boolean bExists; 328 PreparedStatement oStmt; 329 Statement oExec; 330 ResultSet oRSet; 331 String sSQL; 332 333 if (DebugFile.trace) { 334 DebugFile.writeln("Begin Meeting.setAttendant([Connection], " + sAttendantId + ")"); 335 DebugFile.incIdent(); 336 } 337 338 oStmt = oConn.prepareStatement("SELECT NULL FROM " + DB.k_x_meeting_fellow + " WHERE " + DB.gu_meeting + "=? AND " + DB.gu_fellow + "=?", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); 339 oStmt.setString(1,getString(DB.gu_meeting)); 340 oStmt.setString(2,sAttendantId); 341 oRSet = oStmt.executeQuery(); 342 bAttends = oRSet.next(); 343 oRSet.close(); 344 oStmt.close(); 345 346 if (bAttends) { 347 sSQL = "UPDATE " + DB.k_x_meeting_fellow + " SET " + DB.dt_start + "=" + DBBind.escape(getTimestamp(DB.dt_start), "ts") + "," + DB.dt_end + "=" + DBBind.escape(getTimestamp(DB.dt_end), "ts") + " WHERE " + DB.gu_meeting + "='" + getString(DB.gu_meeting) + "' AND " + DB.gu_fellow + "='" + sAttendantId + "'"; 348 oExec = oConn.createStatement(); 349 350 if (DebugFile.trace) DebugFile.writeln("Statement.execute(" + sSQL + ")"); 351 oExec.execute(sSQL); 352 oExec.close(); 353 } 354 else { 355 sSQL = "SELECT NULL FROM " + DB.k_x_meeting_contact + " WHERE " + DB.gu_meeting + "=? AND " + DB.gu_contact + "=?"; 356 if (DebugFile.trace) DebugFile.writeln("Connection.prepareStatement( " + sSQL + ")"); 357 oStmt = oConn.prepareStatement(sSQL, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); 358 oStmt.setString(1,getString(DB.gu_meeting)); 359 oStmt.setString(2,sAttendantId); 360 oRSet = oStmt.executeQuery(); 361 bAttends = oRSet.next(); 362 oRSet.close(); 363 oStmt.close(); 364 365 if (bAttends) { 366 sSQL = "UPDATE " + DB.k_x_meeting_contact + " SET " + DB.dt_start + "=" + DBBind.escape(getTimestamp(DB.dt_start), "ts") + "," + DB.dt_end + "=" + DBBind.escape(getTimestamp(DB.dt_end), "ts") + " WHERE " + DB.gu_meeting + "='" + getString(DB.gu_meeting) + "' AND " + DB.gu_contact + "='" + sAttendantId + "'"; 367 oExec = oConn.createStatement(); 368 if (DebugFile.trace) DebugFile.writeln("Statement.execute(" + sSQL + ")"); 369 oExec.execute(sSQL); 370 oExec.close(); 371 } 372 else { 373 sSQL = "SELECT NULL FROM " + DB.k_fellows + " WHERE " + DB.gu_fellow + "=?"; 374 if (DebugFile.trace) DebugFile.writeln("Connection.prepareStatement( " + sSQL + ")"); 375 oStmt = oConn.prepareStatement(sSQL, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); 376 oStmt.setString(1,sAttendantId); 377 oRSet = oStmt.executeQuery(); 378 bExists = oRSet.next(); 379 oRSet.close(); 380 oStmt.close(); 381 382 if (bExists) { 383 sSQL = "INSERT INTO " + DB.k_x_meeting_fellow + "(" + DB.gu_meeting + "," + DB.gu_fellow + "," + DB.dt_start + "," + DB.dt_end + ") VALUES ('" + getString(DB.gu_meeting) + "','" + sAttendantId + "'," + DBBind.escape(getTimestamp(DB.dt_start), "ts") + "," + DBBind.escape(getTimestamp(DB.dt_end), "ts") + ")"; 384 oExec = oConn.createStatement(); 385 if (DebugFile.trace) DebugFile.writeln("Statement.execute(" + sSQL + ")"); 386 oExec.execute(sSQL); 387 oExec.close(); 388 } 389 else { 390 sSQL = "SELECT NULL FROM " + DB.k_contacts + " WHERE " + DB.gu_contact + "=?"; 391 if (DebugFile.trace) DebugFile.writeln("Connection.prepareStatement( " + sSQL + ")"); 392 oStmt = oConn.prepareStatement(sSQL, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); 393 oStmt.setString(1,sAttendantId); 394 oRSet = oStmt.executeQuery(); 395 bExists = oRSet.next(); 396 oRSet.close(); 397 oStmt.close(); 398 399 if (bExists) { 400 sSQL = "INSERT INTO " + DB.k_x_meeting_contact + "(" + DB.gu_meeting + "," + DB.gu_contact + "," + DB.dt_start + "," + DB.dt_end + ") VALUES ('" + getString(DB.gu_meeting) + "','" + sAttendantId + "'," + DBBind.escape(getTimestamp(DB.dt_start), "ts") + "," + DBBind.escape(getTimestamp(DB.dt_end), "ts") + ")"; 401 oExec = oConn.createStatement(); 402 if (DebugFile.trace) DebugFile.writeln("Statement.execute(" + sSQL + ")"); 403 oExec.execute(sSQL); 404 oExec.close(); 405 } 406 } 407 } 408 } 410 Statement oUpdt = oConn.createStatement(); 411 oUpdt.executeUpdate("UPDATE " + DB.k_meetings + " SET " + DB.dt_modified + "=" + DBBind.Functions.GETDATE); 412 oUpdt.close(); 413 414 if (DebugFile.trace) { 415 DebugFile.decIdent(); 416 DebugFile.writeln("End Meeting.setAttendant()"); 417 } 418 } 420 430 public void repeat (JDCConnection oConn, int nDaysGap, int nTimes, boolean bSkipHolidays) 431 throws SQLException ,IllegalArgumentException { 432 if (DebugFile.trace) { 433 DebugFile.writeln("Begin Meeting.repeat([Connection], " + String.valueOf(nDaysGap) + "," + String.valueOf(nTimes) + ")"); 434 DebugFile.incIdent(); 435 } 436 if (nDaysGap<=0) throw new IllegalArgumentException ("Meeting.repeat() days gap must be an integer greater than zero"); 437 if (nTimes<0) throw new IllegalArgumentException ("Meeting.repeat() days gap must be an integer greater than or equal to zero"); 438 final long lDaysGap = ((long) nDaysGap)*86400000l; 439 Date dtStart, dtEnd; 440 Meeting oClone = new Meeting(); 441 oClone.clone(this); 442 DBSubset oConts = getContacts(oConn); 443 DBSubset oFells = getFellows(oConn); 444 DBSubset oRooms = getRooms(oConn); 445 long lStart = getDate(DB.dt_start).getTime()+lDaysGap; 446 long lEnd = getDate(DB.dt_end).getTime()+lDaysGap; 447 for (int t=0; t<nTimes; t++) { 448 dtStart = new Date (lStart); 449 dtEnd = new Date (lEnd); 450 oClone.replace(DB.gu_meeting, Gadgets.generateUUID()); 451 oClone.replace(DB.dt_start, dtStart); 452 oClone.replace(DB.dt_end, dtEnd); 453 if (!bSkipHolidays || (dtStart.getDay()!=0 && dtStart.getDay()!=6)) { 455 oClone.store(oConn); 456 for (int c=0; c<oConts.getRowCount(); c++) 457 oClone.setAttendant(oConn, oConts.getString(0,c)); 458 for (int f=0; f<oFells.getRowCount(); f++) 459 oClone.setAttendant(oConn, oFells.getString(0,f)); 460 for (int r=0; r<oRooms.getRowCount(); r++) 461 oClone.setRoom(oConn, oRooms.getString(0,r)); 462 } lStart += lDaysGap; 464 lEnd += lDaysGap; 465 } if (DebugFile.trace) { 467 DebugFile.decIdent(); 468 DebugFile.writeln("End Meeting.repeat()"); 469 } 470 } 472 475 482 483 public static boolean delete(JDCConnection oConn, String sMeetingGUID) throws SQLException { 484 boolean bRetVal; 485 486 CallableStatement oCall = oConn.prepareCall("{call k_sp_del_meeting ('" + sMeetingGUID + "')}"); 487 bRetVal = oCall.execute(); 488 oCall.close(); 489 490 return bRetVal; 491 } 493 496 public static final short ClassId = 21; 497 } 498
| Popular Tags
|