}
// todo - review why this is not using the hibernate query language
// The current implementation will break if the Hibernate mappings change
public Timesheet getTimesheet() {
Timesheet timesheet = new Timesheet(this.startDate, this.endDate);
try {
try {
query =
"SELECT project.id as project_id, " +
"project.name as project_name, " +
"iteration.id as iteration_id, " +
"iteration.name as iteration_name, " +
"story.id as story_id, " +
"story.name as story_name, " +
"Sum(time_entry.duration) AS total_duration " +
"FROM person, project, iteration, story, task, time_entry " +
"WHERE project.id = iteration.project_id " +
"AND iteration.id = story.iteration_id " +
"AND story.id = task.story_id " +
"AND task.id = time_entry.task_id " +
"AND (person.id = time_entry.person1_id OR person.id = time_entry.person2_id) " +
"AND time_entry.report_date >= ? " +
"AND time_entry.report_date <= ? " +
IN_CLAUSE_REPLACEMENT + " " +
"GROUP BY project.id, project.name, iteration.id, " +
"iteration.name, story.id, story.name " +
"ORDER BY project.name, iteration.name, story.name ";
if (this.personIds != null && this.personIds.length > 0) {
// Set the in clause using String Manipulation
StringBuffer inClause = new StringBuffer(IN_CLAUSE);
for (int i = 0; i < this.personIds.length; i++) {
if (i > 0) {
inClause.append(",");
}
inClause.append(this.personIds[i]);
}
inClause.append(")");
query = query.replaceAll(IN_CLAUSE_REPLACEMENT, inClause.toString());
}
Connection conn = session.connection();
PreparedStatement stmt = conn.prepareStatement(query);
stmt.setDate(1, new java.sql.Date(this.startDate.getTime()));
stmt.setDate(2, new java.sql.Date(this.endDate.getTime()));
ResultSet results = stmt.executeQuery();
for (boolean isRow = results.next(); isRow; isRow = results.next()) {
final int remoteUserId = SecurityHelper.getRemoteUserId(ThreadServletRequest.get());
if (SystemAuthorizer.get().hasPermission(results.getInt("project_id"),
remoteUserId, "system.project", results.getInt("project_id"), "read")) {
TimesheetEntry time = new TimesheetEntry();
time.setProjectId(results.getInt("project_id"));
time.setProjectName(results.getString("project_name"));
time.setIterationId(results.getInt("iteration_id"));
time.setIterationName(results.getString("iteration_name"));
time.setStoryId(results.getInt("story_id"));
time.setStoryName(results.getString("story_name"));
time.setTotalDuration(results.getDouble("total_duration"));
timesheet.addEntry(time);
}
}
stmt.close();
} catch (Exception ex) {
log.error("query error", ex);