001: package com.technoetic.xplanner.db;
002:
003: import com.technoetic.xplanner.domain.virtual.Timesheet;
004: import com.technoetic.xplanner.domain.virtual.TimesheetEntry;
005: import com.technoetic.xplanner.filters.ThreadServletRequest;
006: import com.technoetic.xplanner.security.SecurityHelper;
007: import com.technoetic.xplanner.security.auth.SystemAuthorizer;
008: import net.sf.hibernate.*;
009: import net.sf.hibernate.type.Type;
010: import org.apache.log4j.Logger;
011:
012: import java.sql.Connection;
013: import java.sql.PreparedStatement;
014: import java.sql.ResultSet;
015: import java.util.Date;
016: import java.util.Iterator;
017:
018: public class AggregateTimesheetQuery {
019: private Logger log = Logger.getLogger(getClass());
020: private static String query;
021: private String[] personIds;
022: private java.util.Date endDate = new Date();
023: private java.util.Date startDate = new Date();
024: private static final String IN_CLAUSE = "AND person.id IN (";
025: private static final String IN_CLAUSE_REPLACEMENT = "AND 1=1";
026: private final Session session;
027:
028: public AggregateTimesheetQuery(Session session) {
029: this .session = session;
030: }
031:
032: // todo - review why this is not using the hibernate query language
033: // The current implementation will break if the Hibernate mappings change
034: public Timesheet getTimesheet() {
035: Timesheet timesheet = new Timesheet(this .startDate,
036: this .endDate);
037: try {
038: try {
039: query = "SELECT project.id, project.name, iteration.id, iteration.name, story.id, story.name, "
040: + "Sum(time_entry.duration) "
041: + "FROM Person as person, Project as project,elements(project.iterations) as iteration, "
042: + "elements(iteration.userStories) as story, elements(story.tasks) as task, elements(task.timeEntries) as time_entry "
043: + "WHERE (person.id = time_entry.person1Id OR person.id = time_entry.person2Id) "
044: + "AND time_entry.reportDate >= ? "
045: + "AND time_entry.reportDate <= ? "
046: + IN_CLAUSE_REPLACEMENT
047: + " "
048: + "GROUP BY project.id, project.name, iteration.id, "
049: + "iteration.name, story.id, story.name "
050: + "ORDER BY project.name, iteration.name, story.name ";
051:
052: if (this .personIds != null && this .personIds.length > 0) {
053: // Set the in clause using String Manipulation
054: StringBuffer inClause = new StringBuffer(IN_CLAUSE);
055: for (int i = 0; i < this .personIds.length; i++) {
056: if (i > 0) {
057: inClause.append(",");
058: }
059: inClause.append(this .personIds[i]);
060: }
061: inClause.append(")");
062: query = query.replaceAll(IN_CLAUSE_REPLACEMENT,
063: inClause.toString());
064: }
065: Iterator iterator = session.iterate(query,
066: new Object[] { this .startDate, this .endDate },
067: new Type[] { Hibernate.DATE, Hibernate.DATE });
068: while (iterator.hasNext()) {
069: final int remoteUserId = SecurityHelper
070: .getRemoteUserId(ThreadServletRequest.get());
071: Object[] row = (Object[]) iterator.next();
072: int projectId = ((Integer) row[0]).intValue();
073: String projectName = (String) row[1];
074: int iterationId = ((Integer) row[2]).intValue();
075: String iterationName = (String) row[3];
076: int storyId = ((Integer) row[4]).intValue();
077: String storyName = (String) row[5];
078: double totalDuration = ((Double) row[6])
079: .doubleValue();
080:
081: if (SystemAuthorizer.get().hasPermission(projectId,
082: remoteUserId, "system.project", projectId,
083: "read")) {
084: TimesheetEntry time = new TimesheetEntry();
085: time.setProjectId(projectId);
086: time.setProjectName(projectName);
087: time.setIterationId(iterationId);
088: time.setIterationName(iterationName);
089: time.setStoryId(storyId);
090: time.setStoryName(storyName);
091: time.setTotalDuration(totalDuration);
092: timesheet.addEntry(time);
093: }
094: }
095: } catch (Exception ex) {
096: log.error("query error", ex);
097: }
098: } catch (Exception ex) {
099: log.error("error in AggregateTimesheetQuery", ex);
100: }
101: return timesheet;
102: }
103:
104: public void setPersonIds(String[] personIds) {
105: this .personIds = personIds;
106: }
107:
108: public String[] getPersonId() {
109: return personIds;
110: }
111:
112: public java.util.Date getStartDate() {
113: return startDate;
114: }
115:
116: public void setStartDate(java.util.Date startDate) {
117: this .startDate = startDate;
118: }
119:
120: public java.util.Date getEndDate() {
121: return endDate;
122: }
123:
124: public void setEndDate(java.util.Date endDate) {
125: this.endDate = endDate;
126: }
127: }
|