Exemple #1
0
  /**
   * プロジェクトの進捗情報を取得します。
   *
   * @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;
    }
  }
Exemple #2
0
  @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;
  }