001: package com.technoetic.xplanner.db;
002:
003: import java.sql.Connection;
004: import java.sql.PreparedStatement;
005: import java.sql.ResultSet;
006: import java.sql.SQLException;
007: import java.util.Date;
008:
009: import net.sf.hibernate.Session;
010: import org.apache.log4j.Logger;
011:
012: import com.technoetic.xplanner.domain.virtual.DailyTimesheetEntry;
013: import com.technoetic.xplanner.domain.virtual.Timesheet;
014: import com.technoetic.xplanner.domain.virtual.TimesheetEntry;
015:
016: // todo These queries should be converted to Hibernate
017:
018: public class PersonTimesheetQuery {
019: private Logger log = Logger.getLogger(getClass());
020: private static String summaryQuery = "SELECT person.name as person_name, "
021: + "project.id as project_id, "
022: + "project.name as project_name, "
023: + "iteration.id as iteration_id, "
024: + "iteration.name as iteration_name, "
025: + "story.id as story_id, "
026: + "story.name as story_name, "
027: + "Sum(time_entry.duration) AS total_duration "
028: + "FROM person, project, iteration, story, task, time_entry "
029: + "WHERE project.id = iteration.project_id "
030: + "AND iteration.id = story.iteration_id "
031: + "AND story.id = task.story_id "
032: + "AND task.id = time_entry.task_id "
033: + "AND (person.id = time_entry.person1_id OR person.id = time_entry.person2_id) "
034: + "AND "
035: + " ((time_entry.report_date >= ? AND time_entry.report_date <= ? AND time_entry.start_time IS NULL) OR "
036: + " (time_entry.start_time >= ? AND time_entry.start_time <= ?)) "
037: + "AND person.id = ? "
038: + "GROUP BY person.id, person.name, project.id, project.name, iteration.id, "
039: + "iteration.name, story.id, story.name "
040: + "ORDER BY person.name, project.name, iteration.name, story.name ";
041:
042: private static String dailyQueryByReportDate = "SELECT "
043: + "time_entry.report_date as report_date, "
044: + "Sum(time_entry.duration) AS total_duration "
045: + "FROM time_entry "
046: + "WHERE ? in (time_entry.person1_id, time_entry.person2_id) "
047: + "AND time_entry.start_time IS NULL "
048: + "AND time_entry.report_date >= ? "
049: + "AND time_entry.report_date <= ? "
050: + "GROUP BY time_entry.report_date "
051: + "ORDER BY time_entry.report_date ";
052:
053: private static String dailyQueryByStartDate = "SELECT "
054: + "time_entry.start_time as report_date, "
055: + "Sum(time_entry.duration) AS total_duration "
056: + "FROM time_entry "
057: + "WHERE ? in (time_entry.person1_id, time_entry.person2_id) "
058: + "AND time_entry.start_time IS NOT NULL "
059: + "AND time_entry.start_time >= ? "
060: + "AND time_entry.start_time <= ? "
061: + "GROUP BY time_entry.start_time "
062: + "ORDER BY time_entry.start_time ";
063:
064: private int personId;
065: private java.util.Date endDate = new Date();
066: private java.util.Date startDate = new Date();
067: private final Session session;
068:
069: public PersonTimesheetQuery(Session session) {
070: this .session = session;
071: }
072:
073: public Timesheet getTimesheet() {
074: Timesheet timesheet = new Timesheet(this .startDate,
075: this .endDate);
076: try {
077: try {
078: Connection conn = session.connection();
079: PreparedStatement stmt = conn
080: .prepareStatement(summaryQuery);
081: stmt.setDate(1, new java.sql.Date(this .startDate
082: .getTime()));
083: stmt.setDate(2, new java.sql.Date(this .endDate
084: .getTime()));
085: stmt.setDate(3, new java.sql.Date(this .startDate
086: .getTime()));
087: stmt.setDate(4, new java.sql.Date(this .endDate
088: .getTime()));
089: stmt.setInt(5, this .personId);
090: ResultSet results = stmt.executeQuery();
091: for (boolean isRow = results.next(); isRow; isRow = results
092: .next()) {
093: TimesheetEntry time = new TimesheetEntry();
094: time
095: .setPersonName(results
096: .getString("person_name"));
097: time.setProjectId(results.getInt("project_id"));
098: time.setProjectName(results
099: .getString("project_name"));
100: time.setIterationId(results.getInt("iteration_id"));
101: time.setIterationName(results
102: .getString("iteration_name"));
103: time.setStoryId(results.getInt("story_id"));
104: time.setStoryName(results.getString("story_name"));
105: time.setTotalDuration(results
106: .getDouble("total_duration"));
107: timesheet.addEntry(time);
108: }
109: stmt.close();
110:
111: doDailyQuery(conn, timesheet, dailyQueryByReportDate);
112: doDailyQuery(conn, timesheet, dailyQueryByStartDate);
113: } catch (Exception ex) {
114: log.error("query error", ex);
115: } finally {
116: session.connection().rollback();
117: }
118: } catch (Exception ex) {
119: log.error("error in PersonTimesheetQuery", ex);
120: }
121: return timesheet;
122: }
123:
124: private void doDailyQuery(Connection conn, Timesheet timesheet,
125: String query) throws SQLException {
126: PreparedStatement stmt = conn.prepareStatement(query);
127: try {
128: stmt.setInt(1, this .personId);
129: stmt
130: .setDate(2, new java.sql.Date(this .startDate
131: .getTime()));
132: stmt.setDate(3, new java.sql.Date(this .endDate.getTime()));
133: ResultSet results = stmt.executeQuery();
134: for (boolean isRow = results.next(); isRow; isRow = results
135: .next()) {
136: DailyTimesheetEntry time = new DailyTimesheetEntry();
137: time.setEntryDate(results.getDate("report_date"));
138: time.setTotalDuration(results
139: .getDouble("total_duration"));
140: timesheet.addDailyEntry(time);
141: }
142: } finally {
143: stmt.close();
144: }
145: }
146:
147: public void setPersonId(int personId) {
148: this .personId = personId;
149: }
150:
151: public int getPersonId() {
152: return personId;
153: }
154:
155: public java.util.Date getStartDate() {
156: return startDate;
157: }
158:
159: public void setStartDate(java.util.Date startDate) {
160: this .startDate = startDate;
161: }
162:
163: public java.util.Date getEndDate() {
164: return endDate;
165: }
166:
167: public void setEndDate(java.util.Date endDate) {
168: this.endDate = endDate;
169: }
170: }
|