Exemple #1
0
 public static String getIncompleteSQL(String column) {
   StringBuilder build = new StringBuilder();
   build.append(" ").append(column).append(" IN (");
   Iterator<String> it = incompleteStatus.iterator();
   while (it.hasNext()) {
     String status = it.next();
     build.append("'").append(status).append("'");
     if (it.hasNext()) {
       build.append(",");
     }
   }
   build.append(") ");
   return build.toString();
 }
Exemple #2
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;
    }
  }