/**
* プロジェクトの進捗情報を取得します。
*
* @param projectId プロジェクトID
* @return 進捗情報
*/
public static List<DataRow> getProjectProgress(Integer projectId) {
/** CURRENT_DATE in SQL depend on Database locale, so unify the time in java */
if (Database.isJdbcMySQL()) {
SimpleDateFormat sdfSrc = new SimpleDateFormat("yyyy/MM/dd");
Date date = null;
try {
date = sdfSrc.parse(EMPTY_DATE);
} catch (ParseException e) {
logger.error("getProjectProgress", e);
throw new RuntimeException(e);
}
SimpleDateFormat sdfDest = new SimpleDateFormat("yyyy-MM-dd");
String formatedEmptyDate = sdfDest.format(date);
StringBuilder sb = new StringBuilder();
sb.append(
"SELECT task.progress_rate, task.start_plan_date, task.end_plan_date, task.plan_workload, member.workload, ");
sb.append(" CASE WHEN ");
sb.append(" ").append(getCurrentDateWithCast()).append(" < task.end_plan_date ");
sb.append(" THEN");
sb.append(" CASE WHEN ");
sb.append(" ")
.append(getCurrentDateWithCast())
.append(" - task.start_plan_date + 1 < 0 THEN 0 ");
sb.append(" ELSE");
sb.append(" ").append(getCurrentDateWithCast()).append(" - task.start_plan_date + 1 ");
sb.append(" END");
sb.append(" ELSE");
sb.append(" task.end_plan_date - task.start_plan_date + 1 ");
sb.append(" END");
sb.append(" AS lapsed_days, ");
sb.append(
" task.end_plan_date - task.start_plan_date + 1 AS task_days, task.update_date AS task_update_date FROM eip_t_project_task AS task ");
sb.append(" JOIN");
sb.append(" (");
sb.append(
" SELECT task_id, SUM(workload) AS workload FROM eip_t_project_task_member GROUP BY task_id");
sb.append(" ) AS member ON member.task_id = task.task_id ");
sb.append(" WHERE task.project_id = #bind($project_id) AND NOT EXISTS");
sb.append(" (");
sb.append(
" SELECT 0 FROM eip_t_project_task AS sub WHERE sub.parent_task_id = task.task_id");
sb.append(" )");
sb.append(
" AND start_plan_date <> #bind($empty_date) AND end_plan_date <> #bind($empty_date)");
String subQuery = sb.toString();
StringBuilder main = new StringBuilder();
main.append(
"SELECT COUNT(0) AS cnt, SUM(lapsed_days) AS lapsed_days, SUM(task_days) AS task_days, CONVERT(SUM(task_days * progress_rate) / SUM(task_days), SIGNED) AS result_per, CONVERT(SUM(lapsed_days) * 100 / SUM(task_days), SIGNED) AS plan_per, CONVERT(SUM(plan_workload), SIGNED) AS plan_workload, CONVERT(SUM(workload), SIGNED) AS workload, CONVERT(SUM(task_days * progress_rate) / SUM(task_days), SIGNED) AS result_per, MAX(task_update_date) AS task_update_date FROM");
main.append("(").append(subQuery).append(") AS base");
String query = main.toString();
SQLTemplate<EipTProjectTask> sqltemp = Database.sql(EipTProjectTask.class, query);
sqltemp.param("project_id", projectId);
sqltemp.param("empty_date", formatedEmptyDate);
List<DataRow> result = sqltemp.fetchListAsDataRow();
return result;
} else {
StringBuilder sb = new StringBuilder();
sb.append("WITH base AS (");
sb.append(" SELECT");
sb.append(" task.progress_rate"); // 進捗率
sb.append(" , task.start_plan_date"); // 開始予定日
sb.append(" , task.end_plan_date"); // 完了予定日
sb.append(" , task.plan_workload"); // 計画工数
sb.append(" , member.workload"); // 工数
sb.append(" , CASE");
sb.append(" WHEN ").append(getCurrentDateWithCast()).append(" < task.end_plan_date");
sb.append(" THEN");
sb.append(" CASE WHEN ")
.append(getCurrentDateWithCast())
.append(" - task.start_plan_date + 1 < 0");
sb.append(" THEN 0");
sb.append(" ELSE ")
.append(getCurrentDateWithCast())
.append(" - task.start_plan_date + 1");
sb.append(" END");
sb.append(" ELSE");
sb.append(" task.end_plan_date - task.start_plan_date + 1");
sb.append(" END AS lapsed_days"); // 基準日までのタスク経過日数
sb.append(" , task.end_plan_date - task.start_plan_date + 1 AS task_days"); // タスク経過日数
sb.append(" , task.update_date AS task_update_date"); // 更新日
sb.append(" FROM");
sb.append(" eip_t_project_task AS task");
sb.append(" JOIN ( ");
sb.append(" SELECT");
sb.append(" task_id");
sb.append(" , SUM(workload) AS workload");
sb.append(" FROM");
sb.append(" eip_t_project_task_member");
sb.append(" GROUP BY");
sb.append(" task_id");
sb.append(" ) AS member ");
sb.append(" ON member.task_id = task.task_id");
sb.append(" WHERE");
sb.append(" task.project_id = #bind($project_id)");
sb.append(" AND NOT EXISTS(");
sb.append(" SELECT 0");
sb.append(" FROM eip_t_project_task AS sub");
sb.append(" WHERE");
sb.append(" sub.parent_task_id = task.task_id");
sb.append(" )");
sb.append(" AND start_plan_date <> TO_DATE(#bind($empty_date), #bind($date_format))");
sb.append(" AND end_plan_date <> TO_DATE(#bind($empty_date), #bind($date_format))");
sb.append(")");
sb.append("SELECT");
sb.append(" #result('COUNT(0)' 'int' 'cnt')");
sb.append(" , #result('SUM(lapsed_days)' 'int' 'lapsed_days')");
sb.append(" , #result('SUM(task_days)' 'int' 'task_days')");
sb.append(
" , #result('SUM(task_days * progress_rate) / SUM(task_days)' 'int' 'result_per')");
sb.append(
" , #result('SUM(lapsed_days) * 100 / SUM(task_days)' 'int' 'plan_per')"); // 予定進捗
sb.append(
" , #result('SUM(plan_workload)' 'java.math.BigDecimal' 'plan_workload')"); // 計画工数
sb.append(" , #result('SUM(workload)' 'java.math.BigDecimal' 'workload')"); // 実績工数
sb.append(
" , #result('SUM(task_days * progress_rate) / SUM(task_days)' 'int' 'result_per')");
sb.append(" , #result('MAX(task_update_date)' 'java.util.Date' 'task_update_date')");
sb.append(" FROM");
sb.append(" base");
SQLTemplate<EipTProjectTask> sqltemp =
Database.sql(EipTProjectTask.class, String.valueOf(sb));
sqltemp.param("project_id", projectId);
sqltemp.param("date_format", DB_DATE_FORMAT);
sqltemp.param("empty_date", EMPTY_DATE);
List<DataRow> result = sqltemp.fetchListAsDataRow();
return result;
}
}
@SuppressWarnings("unchecked")
public Map<Integer, ALEipUser> getUsers(List<Integer> users) {
Map<Integer, ALEipUser> results = new HashMap<Integer, ALEipUser>(users.size());
Map<Integer, ALEipUser> map = new HashMap<Integer, ALEipUser>(users.size());
List<Integer> fetchUsers = new ArrayList<Integer>(users.size());
HttpServletRequest request = HttpServletRequestLocator.get();
if (request != null) {
map = (Map<Integer, ALEipUser>) request.getAttribute(USERS_KEY);
if (map != null) {
for (Integer userId : users) {
ALEipUser user = map.get(userId);
if (user != null) {
results.put(userId, user);
} else {
fetchUsers.add(userId);
}
}
} else {
map = new HashMap<Integer, ALEipUser>(users.size());
fetchUsers.addAll(users);
}
} else {
fetchUsers.addAll(users);
}
if (fetchUsers.size() > 0) {
StringBuilder select = new StringBuilder();
select.append("SELECT");
select.append(" turbine_user.user_id,");
select.append(" turbine_user.last_name,");
select.append(" turbine_user.first_name,");
select.append(" turbine_user.login_name,");
select.append(" turbine_user.has_photo,");
select.append(" turbine_user.photo_modified");
StringBuilder body = new StringBuilder();
body.append(" FROM turbine_user WHERE ");
body.append(" turbine_user.user_id IN (");
boolean isFirst = true;
for (Integer num : fetchUsers) {
if (!isFirst) {
body.append(",");
}
body.append(num.intValue());
isFirst = false;
}
body.append(")");
SQLTemplate<VTurbineUserLite> query =
Database.sql(VTurbineUserLite.class, select.toString() + body.toString());
List<VTurbineUserLite> list = query.fetchList();
for (VTurbineUserLite model : list) {
ALEipUser eipUser = new ALEipUser();
eipUser.initField();
eipUser.setAliasName(model.getFirstName(), model.getLastName());
eipUser.setName(model.getLoginName());
eipUser.setUserId(model.getUserId());
eipUser.setHasPhoto("T".equals(model.getHasPhoto()) || "N".equals(model.getHasPhoto()));
eipUser.setPhotoModified(
model.getPhotoModified() != null ? model.getPhotoModified().getTime() : 0);
results.put(model.getUserId(), eipUser);
map.put(model.getUserId(), eipUser);
}
}
// requestに登録
if (request != null) {
request.setAttribute(USERS_KEY, map);
}
return results;
}