1 32 33 package com.knowgate.projtrack; 34 35 import java.util.Stack ; 36 import java.util.HashMap ; 37 38 import com.knowgate.debug.DebugFile; 39 40 import com.knowgate.dataobjs.DB; 41 import com.knowgate.dataobjs.DBBind; 42 import com.knowgate.dataobjs.DBPersist; 43 import com.knowgate.dataobjs.DBSubset; 44 45 import com.knowgate.misc.Gadgets; 46 import com.knowgate.jdc.JDCConnection; 47 48 import java.sql.Connection ; 49 import java.sql.SQLException ; 50 import java.sql.CallableStatement ; 51 import java.sql.Statement ; 52 import java.sql.PreparedStatement ; 53 import java.sql.ResultSet ; 54 55 60 public class Project extends DBPersist { 61 62 65 public Project() { 66 super(DB.k_projects, "Project"); 67 } 68 69 74 public Project(String sPrjId) { 75 super(DB.k_projects, "Project"); 76 77 put(DB.gu_project, sPrjId); 78 } 79 80 public Project(JDCConnection oConn, String sPrjId) throws SQLException { 81 super(DB.k_projects, "Project"); 82 83 put (DB.gu_project, sPrjId); 84 85 load (oConn, new Object []{sPrjId}); 86 } 87 88 90 97 public String topParent(JDCConnection oConn) throws SQLException { 98 String sCurrent; 99 String sParent; 100 PreparedStatement oStmt; 101 ResultSet oRSet; 102 103 if (DebugFile.trace) { 104 DebugFile.writeln("Begin Project.topParent([Connection])"); 105 DebugFile.incIdent(); 106 DebugFile.writeln("Connection.prepareStatement(SELECT " + DB.id_parent + " FROM " + DB.k_projects + " WHERE " + DB.gu_project + "='" + getStringNull(DB.gu_project, "null") + "')"); 107 } 108 109 oStmt = oConn.prepareStatement("SELECT " + DB.id_parent + " FROM " + DB.k_projects + " WHERE " + DB.gu_project + "=?"); 110 111 sParent = getString(DB.gu_project); 112 do { 113 sCurrent = sParent; 114 115 if (DebugFile.trace) DebugFile.writeln("PreparedStatement.setString(1, " + sCurrent + ")"); 116 117 oStmt.setString(1, sCurrent); 118 oRSet = oStmt.executeQuery(); 119 if (oRSet.next()) 120 sParent = oRSet.getString(1); 121 else 122 sParent = null; 123 oRSet.close(); 124 } while (sParent!=null); 125 126 oStmt.close(); 127 128 if (DebugFile.trace) { 129 DebugFile.decIdent(); 130 DebugFile.writeln("End Project.topParent() : " + sCurrent); 131 } 132 133 return sCurrent; 134 } 136 138 146 public String clone (JDCConnection oConn) 147 throws SQLException , IllegalAccessException { 148 149 if (DebugFile.trace) { 150 DebugFile.writeln("Begin Project.clone([Connection])"); 151 DebugFile.incIdent(); 152 } 153 154 Project oProj; 155 Duty oDuty = new Duty(); 156 Bug oBug = new Bug(); 157 HashMap oSubProjMap = new HashMap (); 158 Object [] aChild = new Object []{getString(DB.gu_project)}; 159 160 oProj = new Project(oConn, getString(DB.gu_project)); 161 oProj.replace(DB.gu_project, Gadgets.generateUUID()); 162 oProj.store(oConn); 163 164 oSubProjMap.put(aChild[0], oProj.get(DB.gu_project)); 165 166 DBSubset oChilds = new DBSubset(DB.k_projects, DB.gu_project, DB.id_parent + "=?", 10); 167 DBSubset oDuties = new DBSubset (DB.k_duties, oDuty.getTable(oConn).getColumnsStr(), DB.gu_project + "=?", 10); 168 DBSubset oBugs = new DBSubset (DB.k_bugs, oBug.getTable(oConn).getColumnsStr(), DB.gu_project + "=?", 10); 169 170 Stack oPending = new Stack (); 171 172 int iChilds = oChilds.load(oConn, aChild); 173 174 if (DebugFile.trace) DebugFile.writeln(String.valueOf(iChilds) + " childs loaded for " + getString(DB.gu_project)); 175 176 for (int c=0; c<iChilds;c++) oPending.push(oChilds.get(0,c)); 177 178 while (!oPending.empty()) { 179 aChild[0] = oPending.pop(); 180 181 iChilds = oChilds.load(oConn, aChild); 182 183 if (DebugFile.trace) DebugFile.writeln(String.valueOf(iChilds) + " childs loaded for " + aChild[0]); 184 185 oProj = new Project(oConn, (String ) aChild[0]); 186 oProj.replace(DB.gu_project, Gadgets.generateUUID()); 187 if (oSubProjMap.containsKey(oProj.get(DB.id_parent))) 188 oProj.replace(DB.id_parent, oSubProjMap.get(oProj.get(DB.id_parent))); 189 oProj.store(oConn); 190 191 int iDuties = oDuties.load (oConn, new Object []{oProj.get(DB.gu_project)}); 192 193 if (DebugFile.trace) DebugFile.writeln(String.valueOf(iDuties) + " duties loaded for " + oProj.getString(DB.gu_project)); 194 195 for (int d=0; d<iDuties; d++) { 196 oDuties.setElementAt(Gadgets.generateUUID(), 0, d); 197 oDuties.setElementAt(oProj.get(DB.gu_project), 2, d); 198 } 199 200 try { 201 oDuties.store(oConn, oDuty.getClass(), true); 202 } 203 catch (java.lang.InstantiationException ignore) { } 204 205 int iBugs = oBugs.load (oConn, new Object []{oProj.get(DB.gu_project)}); 206 207 if (DebugFile.trace) DebugFile.writeln(String.valueOf(iBugs) + " bugs loaded for " + oProj.getString(DB.gu_project)); 208 209 for (int b=0; b<iBugs; b++) { 210 oBugs.setElementAt(Gadgets.generateUUID(), 0, b); 211 oBugs.setElementAt(oProj.get(DB.gu_project), 3, b); 212 } 213 214 try { 215 oBugs.store(oConn, oBug.getClass(), true); 216 } 217 catch (java.lang.InstantiationException ignore) { } 218 219 oSubProjMap.put (aChild[0], oProj.getString(DB.gu_project)); 220 221 for (int c=0; c<iChilds;c++) 222 oPending.push(oChilds.get(0,c)); 223 } 225 oProj = new Project((String ) oSubProjMap.get(get(DB.gu_project))); 227 String sTopParent = oProj.topParent(oConn); 228 229 if (DebugFile.trace) DebugFile.writeln("topparent=" + (null!=sTopParent ? sTopParent : "null")); 230 231 if (null!=sTopParent) 232 oProj = new Project(sTopParent); 233 234 oProj.expand(oConn); 235 236 if (DebugFile.trace) { 237 DebugFile.decIdent(); 238 DebugFile.writeln("End Project.topParent() : " + (String ) oSubProjMap.get(get(DB.gu_project))); 239 } 240 241 return (String ) oSubProjMap.get(get(DB.gu_project)); 242 } 244 246 257 public boolean load(JDCConnection oConn, Object [] PKVals) throws SQLException { 258 boolean bRetVal = super.load(oConn, PKVals); 259 PreparedStatement oStmt; 260 ResultSet oRSet; 261 262 if (DebugFile.trace) { 263 DebugFile.writeln("Begin Project.load([Connection], Object[])"); 264 DebugFile.incIdent(); 265 } 266 267 if (bRetVal) { 268 if (!isNull(DB.gu_company) && DBBind.exists(oConn, DB.k_companies, "U")) { 269 if (DebugFile.trace) DebugFile.writeln("Connection.prepareStatement(SELECT " + DB.nm_legal + " FROM " + DB.k_companies + " WHERE " + DB.gu_company + "='" + getStringNull(DB.gu_company, "null") + "'"); 270 271 oStmt = oConn.prepareStatement("SELECT " + DB.nm_legal + " FROM " + DB.k_companies + " WHERE " + DB.gu_company + "=?", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); 272 oStmt.setString(1, getString(DB.gu_company)); 273 oRSet = oStmt.executeQuery(); 274 if (oRSet.next()) 275 replace(DB.tx_company, oRSet.getString(1)); 276 else if (AllVals.containsKey(DB.tx_company)) 277 remove(DB.tx_company); 278 oRSet.close(); 279 oStmt.close(); 280 } else if (AllVals.containsKey(DB.tx_company)) 282 remove(DB.tx_company); 283 284 if (!isNull(DB.gu_contact) && DBBind.exists(oConn, DB.k_contacts, "U")) { 285 if (DebugFile.trace) DebugFile.writeln("Connection.prepareStatement(SELECT " + DBBind.Functions.ISNULL + "(" + DB.tx_name + ",'')" + DBBind.Functions.CONCAT + "' '" + DBBind.Functions.CONCAT + DBBind.Functions.ISNULL + "(" + DB.tx_surname + ",'') FROM " + DB.k_contacts + " WHERE " + DB.gu_contact + "='" + getStringNull(DB.gu_contact,"null") + "'"); 286 287 oStmt = oConn.prepareStatement("SELECT " + DBBind.Functions.ISNULL + "(" + DB.tx_name + ",'')" + DBBind.Functions.CONCAT + "' '" + DBBind.Functions.CONCAT + DBBind.Functions.ISNULL + "(" + DB.tx_surname + ",'') FROM " + DB.k_contacts + " WHERE " + DB.gu_contact + "=?", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); 288 oStmt.setString(1, getString(DB.gu_contact)); 289 oRSet = oStmt.executeQuery(); 290 if (oRSet.next()) 291 replace(DB.tx_contact, oRSet.getString(1)); 292 else if (AllVals.containsKey(DB.tx_contact)) 293 remove(DB.tx_contact); 294 oRSet.close(); 295 oStmt.close(); 296 } else if (AllVals.containsKey(DB.tx_contact)) 298 remove(DB.tx_contact); 299 300 } 302 if (DebugFile.trace) { 303 DebugFile.decIdent(); 304 DebugFile.writeln("End Project.load() : " + String.valueOf(bRetVal)); 305 } 306 307 return bRetVal; 308 } 310 312 319 public boolean delete(JDCConnection oConn) throws SQLException { 320 return Project.delete(oConn, getString(DB.gu_project)); 321 } 322 323 325 332 public boolean store(JDCConnection oConn) throws SQLException { 333 boolean bRetVal; 334 String sTopParent; 335 Project oTopParent; 336 java.sql.Timestamp dtNow = new java.sql.Timestamp (DBBind.getTime()); 337 338 if (DebugFile.trace) { 339 DebugFile.writeln("Begin Project.store([Connection])"); 340 DebugFile.incIdent(); 341 } 342 343 if (!AllVals.containsKey(DB.gu_project)) 344 put(DB.gu_project, Gadgets.generateUUID()); 345 346 bRetVal = super.store(oConn); 347 348 sTopParent = topParent(oConn); 350 351 if (DebugFile.trace) DebugFile.writeln("topparent=" + (null!=sTopParent ? sTopParent : "null")); 352 353 if (null!=sTopParent) { 354 oTopParent = new Project(sTopParent); 355 oTopParent.expand(oConn); 356 oTopParent = null; 357 } 359 if (DebugFile.trace) { 360 DebugFile.decIdent(); 361 DebugFile.writeln("End Project.store() : " + String.valueOf(bRetVal)); 362 } 363 364 return bRetVal; 365 } 367 369 378 public float cost (JDCConnection oConn) throws SQLException , NumberFormatException { 379 float fCost = 0; 380 Object oCost; 381 Statement oStmt; 382 ResultSet oRSet; 383 String sSQL; 384 385 if (DebugFile.trace) { 386 DebugFile.writeln("Begin Project.cost([Connection])"); 387 DebugFile.incIdent(); 388 } 389 390 switch (oConn.getDataBaseProduct()) { 391 case JDCConnection.DBMS_ORACLE: 392 sSQL = "SELECT k_sp_prj_cost ('" + getString(DB.gu_project) + "') FROM DUAL"; 393 break; 394 case JDCConnection.DBMS_MSSQL: 395 String sSchema = oConn.getSchemaName(); 396 if (null!=sSchema) { 397 if (sSchema.indexOf('\\')>0) 398 sSQL = "SELECT k_sp_prj_cost ('" + getString(DB.gu_project) + "')"; 399 else 400 sSQL = "SELECT " + sSchema + ".k_sp_prj_cost ('" + getString(DB.gu_project) + "')"; 401 } else { 402 sSQL = "SELECT k_sp_prj_cost ('" + getString(DB.gu_project) + "')"; 403 } 404 break; 405 default: 406 sSQL = "SELECT k_sp_prj_cost ('" + getString(DB.gu_project) + "')"; 407 } 408 409 oStmt = oConn.createStatement(); 410 411 if (DebugFile.trace) 412 DebugFile.writeln("Statement.executeQuery(" + sSQL + ")"); 413 414 oRSet = oStmt.executeQuery (sSQL); 415 416 oRSet.next(); 417 418 oCost = oRSet.getObject(1); 419 420 oRSet.close(); 421 422 oStmt.close(); 423 424 fCost = Float.parseFloat(oCost.toString()); 425 426 if (DebugFile.trace) { 427 DebugFile.decIdent(); 428 DebugFile.writeln("End Project.store() : " + String.valueOf(fCost)); 429 } 430 431 return fCost; 432 } 434 436 446 public DBSubset getAllChilds(JDCConnection oConn) throws SQLException { 447 if (DebugFile.trace) { 448 DebugFile.writeln("Begin Project.getAllChilds([Connection])"); 449 DebugFile.incIdent(); 450 } 451 452 DBSubset oTree = new DBSubset(DB.k_project_expand, DB.gu_project + "," + DB.nm_project + "," + DB.od_level + "," + DB.od_walk + "," + DB.gu_parent, DB.gu_rootprj + "='" + getString(DB.gu_project) + "' ORDER BY " + DB.od_walk, 50); 453 454 int iChildCount = oTree.load(oConn); 455 456 if (DebugFile.trace) { 457 for (int c=0; c<iChildCount; c++) 458 DebugFile.writeln(String.valueOf(oTree.getInt(3,c))+" lv="+String.valueOf(oTree.getInt(2,c))+",gu="+oTree.getString(0,c)+",nm="+oTree.getString(1,c)); 459 DebugFile.decIdent(); 460 DebugFile.writeln("End Project.getAllChilds() : "+String.valueOf(iChildCount)); 461 } 462 463 return oTree; 464 } 465 466 468 475 public void expand(JDCConnection oConn) throws SQLException { 476 CallableStatement oCall; 477 Statement oStmt; 478 479 if (DebugFile.trace) { 480 DebugFile.writeln("Begin Project.expand([Connection])"); 481 DebugFile.incIdent(); 482 } 483 484 if (oConn.getDataBaseProduct()==JDCConnection.DBMS_POSTGRESQL) { 485 if (DebugFile.trace) DebugFile.writeln("Connection.executeQuery(SELECT k_sp_prj_expand ('" + getStringNull(DB.gu_project,"null") + "')"); 486 oStmt = oConn.createStatement(); 487 oStmt.executeQuery("SELECT k_sp_prj_expand ('" + getString(DB.gu_project) + "')"); 488 oStmt.close(); 489 } else { 490 if (DebugFile.trace) DebugFile.writeln("Connection.prepareCall({ call k_sp_prj_expand ('" + getStringNull(DB.gu_project,"null") + "')}"); 491 oCall = oConn.prepareCall("{ call k_sp_prj_expand ('" + getString(DB.gu_project) + "') }"); 492 oCall.execute(); 493 oCall.close(); 494 } 495 496 if (DebugFile.trace) { 497 DebugFile.decIdent(); 498 DebugFile.writeln("End Project.expand()"); 499 } 500 } 502 504 507 516 public static boolean delete(JDCConnection oConn, String sProjectGUID) 517 throws SQLException ,NullPointerException { 518 boolean bRetVal; 519 520 if (null==sProjectGUID) 521 throw new NullPointerException ("Project.delete() GUID of project to be deleted may not be null"); 522 523 if (DebugFile.trace) { 524 DebugFile.writeln("Begin Project.delete([Connection], " + sProjectGUID + ")"); 525 DebugFile.incIdent(); 526 } 527 528 String sTopParent = new Project(sProjectGUID).topParent(oConn); 529 530 if (oConn.getDataBaseProduct()==JDCConnection.DBMS_POSTGRESQL) { 531 if (DebugFile.trace) DebugFile.writeln("Connection.executeQuery(SELECT k_sp_del_project ('" + sProjectGUID + "'))"); 532 Statement oStmt = oConn.createStatement(); 533 ResultSet oRSet = oStmt.executeQuery("SELECT k_sp_del_project ('" + sProjectGUID + "')"); 534 oRSet.close(); 535 oStmt.close(); 536 bRetVal = true; 537 } 538 else { 539 if (DebugFile.trace) DebugFile.writeln("Connection.prepareCall({ call k_sp_del_project ('" + sProjectGUID + "')})"); 540 CallableStatement oCall = oConn.prepareCall("{ call k_sp_del_project ('" + sProjectGUID + "')}"); 541 bRetVal = oCall.execute(); 542 oCall.close(); 543 } 544 545 if (DebugFile.trace) DebugFile.writeln("sTopParent="+sTopParent); 546 547 if (!sProjectGUID.equals(sTopParent)) new Project(sTopParent).expand(oConn); 548 549 if (DebugFile.trace) { 550 DebugFile.decIdent(); 551 DebugFile.writeln("End Project.delete() : " + String.valueOf(bRetVal)); 552 } 553 554 return bRetVal; 555 } 557 560 public static final short ClassId = 80; 561 562 } 563 | Popular Tags |