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.projtrack;
034:
035: import java.util.Stack;
036: import java.util.HashMap;
037:
038: import com.knowgate.debug.DebugFile;
039:
040: import com.knowgate.dataobjs.DB;
041: import com.knowgate.dataobjs.DBBind;
042: import com.knowgate.dataobjs.DBPersist;
043: import com.knowgate.dataobjs.DBSubset;
044:
045: import com.knowgate.misc.Gadgets;
046: import com.knowgate.jdc.JDCConnection;
047:
048: import java.sql.Connection;
049: import java.sql.SQLException;
050: import java.sql.CallableStatement;
051: import java.sql.Statement;
052: import java.sql.PreparedStatement;
053: import java.sql.ResultSet;
054:
055: /**
056: * <p>Project</p>
057: * @author Sergio Montoro Ten
058: * @version 3.0
059: */
060: public class Project extends DBPersist {
061:
062: /**
063: * Create empty an Project
064: */
065: public Project() {
066: super (DB.k_projects, "Project");
067: }
068:
069: /**
070: * Create empty an Project and set gu_project.
071: * No data is loaded from database.
072: * @param sPrjId Project Unique Identifier.
073: */
074: public Project(String sPrjId) {
075: super (DB.k_projects, "Project");
076:
077: put(DB.gu_project, sPrjId);
078: }
079:
080: public Project(JDCConnection oConn, String sPrjId)
081: throws SQLException {
082: super (DB.k_projects, "Project");
083:
084: put(DB.gu_project, sPrjId);
085:
086: load(oConn, new Object[] { sPrjId });
087: }
088:
089: // ----------------------------------------------------------
090:
091: /**
092: * Get Project Top Parent.
093: * Browse recursively k_projects table until finding the top most parent for Project.
094: * @param oConn Database Connection
095: * @return GUID of top most parent Project or <b>null</b> if this is a top Project.
096: * @throws SQLException
097: */
098: public String topParent(JDCConnection oConn) throws SQLException {
099: String sCurrent;
100: String sParent;
101: PreparedStatement oStmt;
102: ResultSet oRSet;
103:
104: if (DebugFile.trace) {
105: DebugFile.writeln("Begin Project.topParent([Connection])");
106: DebugFile.incIdent();
107: DebugFile.writeln("Connection.prepareStatement(SELECT "
108: + DB.id_parent + " FROM " + DB.k_projects
109: + " WHERE " + DB.gu_project + "='"
110: + getStringNull(DB.gu_project, "null") + "')");
111: }
112:
113: oStmt = oConn.prepareStatement("SELECT " + DB.id_parent
114: + " FROM " + DB.k_projects + " WHERE " + DB.gu_project
115: + "=?");
116:
117: sParent = getString(DB.gu_project);
118: do {
119: sCurrent = sParent;
120:
121: if (DebugFile.trace)
122: DebugFile.writeln("PreparedStatement.setString(1, "
123: + sCurrent + ")");
124:
125: oStmt.setString(1, sCurrent);
126: oRSet = oStmt.executeQuery();
127: if (oRSet.next())
128: sParent = oRSet.getString(1);
129: else
130: sParent = null;
131: oRSet.close();
132: } while (sParent != null);
133:
134: oStmt.close();
135:
136: if (DebugFile.trace) {
137: DebugFile.decIdent();
138: DebugFile.writeln("End Project.topParent() : " + sCurrent);
139: }
140:
141: return sCurrent;
142: } // topParent();
143:
144: // ----------------------------------------------------------
145:
146: /**
147: * <P>Clone Project</P>
148: * When a project is cloned all its subprojects, duites and bugs are also cloned.
149: * @param oConn Database Connection
150: * @return GUID of new cloned Project
151: * @throws SQLException
152: * @throws IllegalAccessException
153: */
154: public String clone(JDCConnection oConn) throws SQLException,
155: IllegalAccessException {
156:
157: if (DebugFile.trace) {
158: DebugFile.writeln("Begin Project.clone([Connection])");
159: DebugFile.incIdent();
160: }
161:
162: Project oProj;
163: Duty oDuty = new Duty();
164: Bug oBug = new Bug();
165: HashMap oSubProjMap = new HashMap();
166: Object[] aChild = new Object[] { getString(DB.gu_project) };
167:
168: oProj = new Project(oConn, getString(DB.gu_project));
169: oProj.replace(DB.gu_project, Gadgets.generateUUID());
170: oProj.store(oConn);
171:
172: oSubProjMap.put(aChild[0], oProj.get(DB.gu_project));
173:
174: DBSubset oChilds = new DBSubset(DB.k_projects, DB.gu_project,
175: DB.id_parent + "=?", 10);
176: DBSubset oDuties = new DBSubset(DB.k_duties, oDuty.getTable(
177: oConn).getColumnsStr(), DB.gu_project + "=?", 10);
178: DBSubset oBugs = new DBSubset(DB.k_bugs, oBug.getTable(oConn)
179: .getColumnsStr(), DB.gu_project + "=?", 10);
180:
181: Stack oPending = new Stack();
182:
183: int iChilds = oChilds.load(oConn, aChild);
184:
185: if (DebugFile.trace)
186: DebugFile.writeln(String.valueOf(iChilds)
187: + " childs loaded for " + getString(DB.gu_project));
188:
189: for (int c = 0; c < iChilds; c++)
190: oPending.push(oChilds.get(0, c));
191:
192: while (!oPending.empty()) {
193: aChild[0] = oPending.pop();
194:
195: iChilds = oChilds.load(oConn, aChild);
196:
197: if (DebugFile.trace)
198: DebugFile.writeln(String.valueOf(iChilds)
199: + " childs loaded for " + aChild[0]);
200:
201: oProj = new Project(oConn, (String) aChild[0]);
202: oProj.replace(DB.gu_project, Gadgets.generateUUID());
203: if (oSubProjMap.containsKey(oProj.get(DB.id_parent)))
204: oProj.replace(DB.id_parent, oSubProjMap.get(oProj
205: .get(DB.id_parent)));
206: oProj.store(oConn);
207:
208: int iDuties = oDuties.load(oConn, new Object[] { oProj
209: .get(DB.gu_project) });
210:
211: if (DebugFile.trace)
212: DebugFile.writeln(String.valueOf(iDuties)
213: + " duties loaded for "
214: + oProj.getString(DB.gu_project));
215:
216: for (int d = 0; d < iDuties; d++) {
217: oDuties.setElementAt(Gadgets.generateUUID(), 0, d);
218: oDuties.setElementAt(oProj.get(DB.gu_project), 2, d);
219: }
220:
221: try {
222: oDuties.store(oConn, oDuty.getClass(), true);
223: } catch (java.lang.InstantiationException ignore) { /* never thrown*/
224: }
225:
226: int iBugs = oBugs.load(oConn, new Object[] { oProj
227: .get(DB.gu_project) });
228:
229: if (DebugFile.trace)
230: DebugFile.writeln(String.valueOf(iBugs)
231: + " bugs loaded for "
232: + oProj.getString(DB.gu_project));
233:
234: for (int b = 0; b < iBugs; b++) {
235: oBugs.setElementAt(Gadgets.generateUUID(), 0, b);
236: oBugs.setElementAt(oProj.get(DB.gu_project), 3, b);
237: }
238:
239: try {
240: oBugs.store(oConn, oBug.getClass(), true);
241: } catch (java.lang.InstantiationException ignore) { /* never thrown*/
242: }
243:
244: oSubProjMap.put(aChild[0], oProj.getString(DB.gu_project));
245:
246: for (int c = 0; c < iChilds; c++)
247: oPending.push(oChilds.get(0, c));
248: } // wend
249:
250: // Re-expandir todos los hijos del padre absoluto del clon
251: oProj = new Project((String) oSubProjMap
252: .get(get(DB.gu_project)));
253: String sTopParent = oProj.topParent(oConn);
254:
255: if (DebugFile.trace)
256: DebugFile.writeln("topparent="
257: + (null != sTopParent ? sTopParent : "null"));
258:
259: if (null != sTopParent)
260: oProj = new Project(sTopParent);
261:
262: oProj.expand(oConn);
263:
264: if (DebugFile.trace) {
265: DebugFile.decIdent();
266: DebugFile.writeln("End Project.topParent() : "
267: + (String) oSubProjMap.get(get(DB.gu_project)));
268: }
269:
270: return (String) oSubProjMap.get(get(DB.gu_project));
271: } // clone
272:
273: // ----------------------------------------------------------
274:
275: /**
276: * Load Project.
277: * If Project is assigned to a Company then Company Legal Name
278: * (k_companies.nm_legal) is loaded into property DB.tx_company.
279: * If Project is assigned to a Contact then Contact Full Name
280: * (tx_name+tx_surname) is loaded into property DB.tx_contact.
281: * @param oConn
282: * @param PKVals
283: * @return
284: * @throws SQLException
285: */
286: public boolean load(JDCConnection oConn, Object[] PKVals)
287: throws SQLException {
288: boolean bRetVal = super .load(oConn, PKVals);
289: PreparedStatement oStmt;
290: ResultSet oRSet;
291:
292: if (DebugFile.trace) {
293: DebugFile
294: .writeln("Begin Project.load([Connection], Object[])");
295: DebugFile.incIdent();
296: }
297:
298: if (bRetVal) {
299: if (!isNull(DB.gu_company)
300: && DBBind.exists(oConn, DB.k_companies, "U")) {
301: if (DebugFile.trace)
302: DebugFile
303: .writeln("Connection.prepareStatement(SELECT "
304: + DB.nm_legal
305: + " FROM "
306: + DB.k_companies
307: + " WHERE "
308: + DB.gu_company
309: + "='"
310: + getStringNull(DB.gu_company,
311: "null") + "'");
312:
313: oStmt = oConn.prepareStatement("SELECT " + DB.nm_legal
314: + " FROM " + DB.k_companies + " WHERE "
315: + DB.gu_company + "=?",
316: ResultSet.TYPE_FORWARD_ONLY,
317: ResultSet.CONCUR_READ_ONLY);
318: oStmt.setString(1, getString(DB.gu_company));
319: oRSet = oStmt.executeQuery();
320: if (oRSet.next())
321: replace(DB.tx_company, oRSet.getString(1));
322: else if (AllVals.containsKey(DB.tx_company))
323: remove(DB.tx_company);
324: oRSet.close();
325: oStmt.close();
326: } // fi (exists(k_companies))
327: else if (AllVals.containsKey(DB.tx_company))
328: remove(DB.tx_company);
329:
330: if (!isNull(DB.gu_contact)
331: && DBBind.exists(oConn, DB.k_contacts, "U")) {
332: if (DebugFile.trace)
333: DebugFile
334: .writeln("Connection.prepareStatement(SELECT "
335: + DBBind.Functions.ISNULL
336: + "("
337: + DB.tx_name
338: + ",'')"
339: + DBBind.Functions.CONCAT
340: + "' '"
341: + DBBind.Functions.CONCAT
342: + DBBind.Functions.ISNULL
343: + "("
344: + DB.tx_surname
345: + ",'') FROM "
346: + DB.k_contacts
347: + " WHERE "
348: + DB.gu_contact
349: + "='"
350: + getStringNull(DB.gu_contact,
351: "null") + "'");
352:
353: oStmt = oConn.prepareStatement("SELECT "
354: + DBBind.Functions.ISNULL + "(" + DB.tx_name
355: + ",'')" + DBBind.Functions.CONCAT + "' '"
356: + DBBind.Functions.CONCAT
357: + DBBind.Functions.ISNULL + "(" + DB.tx_surname
358: + ",'') FROM " + DB.k_contacts + " WHERE "
359: + DB.gu_contact + "=?",
360: ResultSet.TYPE_FORWARD_ONLY,
361: ResultSet.CONCUR_READ_ONLY);
362: oStmt.setString(1, getString(DB.gu_contact));
363: oRSet = oStmt.executeQuery();
364: if (oRSet.next())
365: replace(DB.tx_contact, oRSet.getString(1));
366: else if (AllVals.containsKey(DB.tx_contact))
367: remove(DB.tx_contact);
368: oRSet.close();
369: oStmt.close();
370: } // fi (exists(k_contacts))
371: else if (AllVals.containsKey(DB.tx_contact))
372: remove(DB.tx_contact);
373:
374: } // fi (super.load)
375:
376: if (DebugFile.trace) {
377: DebugFile.decIdent();
378: DebugFile.writeln("End Project.load() : "
379: + String.valueOf(bRetVal));
380: }
381:
382: return bRetVal;
383: } // load
384:
385: // ----------------------------------------------------------
386:
387: /**
388: * <p>Delete Project.</p>
389: * Calls k_sp_del_project stored procedure.<br>
390: * Deletion includes Project Childs, Duties and Bugs.
391: * @param oConn Database Connection
392: * @throws SQLException
393: */
394: public boolean delete(JDCConnection oConn) throws SQLException {
395: return Project.delete(oConn, getString(DB.gu_project));
396: }
397:
398: // ----------------------------------------------------------
399:
400: /**
401: * Store Project
402: * If gu_project is null a new GUID is automatically assigned.<br>
403: * Calls internally to expand() method for re-expanding k_project_expand table.<br>
404: * @param oConn Database Connection
405: * @throws SQLException
406: */
407: public boolean store(JDCConnection oConn) throws SQLException {
408: boolean bRetVal;
409: String sTopParent;
410: Project oTopParent;
411: java.sql.Timestamp dtNow = new java.sql.Timestamp(DBBind
412: .getTime());
413:
414: if (DebugFile.trace) {
415: DebugFile.writeln("Begin Project.store([Connection])");
416: DebugFile.incIdent();
417: }
418:
419: if (!AllVals.containsKey(DB.gu_project))
420: put(DB.gu_project, Gadgets.generateUUID());
421:
422: bRetVal = super .store(oConn);
423:
424: // Re-expandir todos los hijos del padre absoluto de este proyecto
425: sTopParent = topParent(oConn);
426:
427: if (DebugFile.trace)
428: DebugFile.writeln("topparent="
429: + (null != sTopParent ? sTopParent : "null"));
430:
431: if (null != sTopParent) {
432: oTopParent = new Project(sTopParent);
433: oTopParent.expand(oConn);
434: oTopParent = null;
435: } // fi (sTopParent)
436:
437: if (DebugFile.trace) {
438: DebugFile.decIdent();
439: DebugFile.writeln("End Project.store() : "
440: + String.valueOf(bRetVal));
441: }
442:
443: return bRetVal;
444: } // store()
445:
446: // ----------------------------------------------------------
447:
448: /**
449: * <p>Compute total project cost</p>
450: * Total project cost is the sum of costs of all duties from the project<br>
451: * This method call stored procedure k_sp_prj_cost
452: * @param oConn Database Connection
453: * @return Sum of all duty costs for project
454: * @throws SQLException
455: * @throws NumberFormatException
456: */
457: public float cost(JDCConnection oConn) throws SQLException,
458: NumberFormatException {
459: float fCost = 0;
460: Object oCost;
461: Statement oStmt;
462: ResultSet oRSet;
463: String sSQL;
464:
465: if (DebugFile.trace) {
466: DebugFile.writeln("Begin Project.cost([Connection])");
467: DebugFile.incIdent();
468: }
469:
470: switch (oConn.getDataBaseProduct()) {
471: case JDCConnection.DBMS_ORACLE:
472: sSQL = "SELECT k_sp_prj_cost ('" + getString(DB.gu_project)
473: + "') FROM DUAL";
474: break;
475: case JDCConnection.DBMS_MSSQL:
476: String sSchema = oConn.getSchemaName();
477: if (null != sSchema) {
478: if (sSchema.indexOf('\\') > 0)
479: sSQL = "SELECT k_sp_prj_cost ('"
480: + getString(DB.gu_project) + "')";
481: else
482: sSQL = "SELECT " + sSchema + ".k_sp_prj_cost ('"
483: + getString(DB.gu_project) + "')";
484: } else {
485: sSQL = "SELECT k_sp_prj_cost ('"
486: + getString(DB.gu_project) + "')";
487: }
488: break;
489: default:
490: sSQL = "SELECT k_sp_prj_cost ('" + getString(DB.gu_project)
491: + "')";
492: }
493:
494: oStmt = oConn.createStatement();
495:
496: if (DebugFile.trace)
497: DebugFile.writeln("Statement.executeQuery(" + sSQL + ")");
498:
499: oRSet = oStmt.executeQuery(sSQL);
500:
501: oRSet.next();
502:
503: oCost = oRSet.getObject(1);
504:
505: oRSet.close();
506:
507: oStmt.close();
508:
509: fCost = Float.parseFloat(oCost.toString());
510:
511: if (DebugFile.trace) {
512: DebugFile.decIdent();
513: DebugFile.writeln("End Project.store() : "
514: + String.valueOf(fCost));
515: }
516:
517: return fCost;
518: } // cost
519:
520: // ----------------------------------------------------------
521:
522: /**
523: * <p>Get all Project Childs as a DBSubset.</p>
524: * @param oConn Database Connection
525: * @return DBSubset with the following structure:<br>
526: * <table border=1 cellpadding=4>
527: * <tr><td><b>gu_project</b></td><td><b>nm_project</b></td><td><b>od_level</b></td><td><b>od_walk</b></td><td><b>id_parent</b></td></tr>
528: * <tr><td>Project GUID</td><td>Project Name</td><td>Depth Level</td><td>Walk order within level</td><td>Inmediate Parent</td></tr>
529: * </table>
530: * @throws SQLException
531: */
532: public DBSubset getAllChilds(JDCConnection oConn)
533: throws SQLException {
534: if (DebugFile.trace) {
535: DebugFile
536: .writeln("Begin Project.getAllChilds([Connection])");
537: DebugFile.incIdent();
538: }
539:
540: DBSubset oTree = new DBSubset(DB.k_project_expand,
541: DB.gu_project + "," + DB.nm_project + "," + DB.od_level
542: + "," + DB.od_walk + "," + DB.gu_parent,
543: DB.gu_rootprj + "='" + getString(DB.gu_project)
544: + "' ORDER BY " + DB.od_walk, 50);
545:
546: int iChildCount = oTree.load(oConn);
547:
548: if (DebugFile.trace) {
549: for (int c = 0; c < iChildCount; c++)
550: DebugFile.writeln(String.valueOf(oTree.getInt(3, c))
551: + " lv=" + String.valueOf(oTree.getInt(2, c))
552: + ",gu=" + oTree.getString(0, c) + ",nm="
553: + oTree.getString(1, c));
554: DebugFile.decIdent();
555: DebugFile.writeln("End Project.getAllChilds() : "
556: + String.valueOf(iChildCount));
557: }
558:
559: return oTree;
560: }
561:
562: // ----------------------------------------------------------
563:
564: /**
565: * <p>Expand Project childs.</p>
566: * Calls k_sp_prj_expand stored procedure.<br>
567: * Expansion is stored at k_project_expand table.
568: * @param oConn Database Connection
569: * @throws SQLException
570: */
571: public void expand(JDCConnection oConn) throws SQLException {
572: CallableStatement oCall;
573: Statement oStmt;
574:
575: if (DebugFile.trace) {
576: DebugFile.writeln("Begin Project.expand([Connection])");
577: DebugFile.incIdent();
578: }
579:
580: if (oConn.getDataBaseProduct() == JDCConnection.DBMS_POSTGRESQL) {
581: if (DebugFile.trace)
582: DebugFile
583: .writeln("Connection.executeQuery(SELECT k_sp_prj_expand ('"
584: + getStringNull(DB.gu_project, "null")
585: + "')");
586: oStmt = oConn.createStatement();
587: oStmt.executeQuery("SELECT k_sp_prj_expand ('"
588: + getString(DB.gu_project) + "')");
589: oStmt.close();
590: } else {
591: if (DebugFile.trace)
592: DebugFile
593: .writeln("Connection.prepareCall({ call k_sp_prj_expand ('"
594: + getStringNull(DB.gu_project, "null")
595: + "')}");
596: oCall = oConn.prepareCall("{ call k_sp_prj_expand ('"
597: + getString(DB.gu_project) + "') }");
598: oCall.execute();
599: oCall.close();
600: }
601:
602: if (DebugFile.trace) {
603: DebugFile.decIdent();
604: DebugFile.writeln("End Project.expand()");
605: }
606: } // expand()
607:
608: // ----------------------------------------------------------
609:
610: // **********************************************************
611: // Static Methods
612:
613: /**
614: * <p>Delete Project.</p>
615: * Calls k_sp_del_project stored procedure.<br>
616: * Deletion includes Project Childs, Duties and Bugs.
617: * @param oConn Database Connection
618: * @param sProjectGUID GUID of project to be deleted.
619: * @throws NullPointerException if sProjectGUID is null
620: * @throws SQLException
621: */
622: public static boolean delete(JDCConnection oConn,
623: String sProjectGUID) throws SQLException,
624: NullPointerException {
625: boolean bRetVal;
626:
627: if (null == sProjectGUID)
628: throw new NullPointerException(
629: "Project.delete() GUID of project to be deleted may not be null");
630:
631: if (DebugFile.trace) {
632: DebugFile.writeln("Begin Project.delete([Connection], "
633: + sProjectGUID + ")");
634: DebugFile.incIdent();
635: }
636:
637: String sTopParent = new Project(sProjectGUID).topParent(oConn);
638:
639: if (oConn.getDataBaseProduct() == JDCConnection.DBMS_POSTGRESQL) {
640: if (DebugFile.trace)
641: DebugFile
642: .writeln("Connection.executeQuery(SELECT k_sp_del_project ('"
643: + sProjectGUID + "'))");
644: Statement oStmt = oConn.createStatement();
645: ResultSet oRSet = oStmt
646: .executeQuery("SELECT k_sp_del_project ('"
647: + sProjectGUID + "')");
648: oRSet.close();
649: oStmt.close();
650: bRetVal = true;
651: } else {
652: if (DebugFile.trace)
653: DebugFile
654: .writeln("Connection.prepareCall({ call k_sp_del_project ('"
655: + sProjectGUID + "')})");
656: CallableStatement oCall = oConn
657: .prepareCall("{ call k_sp_del_project ('"
658: + sProjectGUID + "')}");
659: bRetVal = oCall.execute();
660: oCall.close();
661: }
662:
663: if (DebugFile.trace)
664: DebugFile.writeln("sTopParent=" + sTopParent);
665:
666: if (!sProjectGUID.equals(sTopParent))
667: new Project(sTopParent).expand(oConn);
668:
669: if (DebugFile.trace) {
670: DebugFile.decIdent();
671: DebugFile.writeln("End Project.delete() : "
672: + String.valueOf(bRetVal));
673: }
674:
675: return bRetVal;
676: } // delete()
677:
678: // **********************************************************
679: // Public Constants
680:
681: public static final short ClassId = 80;
682:
683: }
|