Пример #1
0
  /**
   * Update PTL_PORTLET_IMAGE Data Bindding JavaBean
   *
   * @param bean PTL_PORTLET_IMAGE Data Bindding JavaBean
   * @return 更新件数
   * @throws SQLException SQL実行例外
   */
  public int update(PtlPortletImageModel bean) throws SQLException {

    PreparedStatement pstmt = null;
    int count = 0;
    Connection con = null;
    con = getCon();

    try {
      // SQL文
      SqlBuffer sql = new SqlBuffer();
      sql.addSql(" update");
      sql.addSql("   PTL_PORTLET_IMAGE");
      sql.addSql(" set ");
      sql.addSql("   BIN_SID=?,");
      sql.addSql("   PLI_NAME=?");
      sql.addSql(" where ");
      sql.addSql("   PLT_SID=?");
      sql.addSql(" and");
      sql.addSql("   PLI_SID=?");

      pstmt = con.prepareStatement(sql.toSqlString());
      sql.addLongValue(bean.getBinSid());
      sql.addStrValue(bean.getPliName());
      // where
      sql.addIntValue(bean.getPltSid());
      sql.addLongValue(bean.getPliSid());

      log__.info(sql.toLogString());
      sql.setParameter(pstmt);
      count = pstmt.executeUpdate();
    } catch (SQLException e) {
      throw e;
    } finally {
      JDBCUtil.closeStatement(pstmt);
    }
    return count;
  }
Пример #2
0
  /**
   * <br>
   * [機 能] 指定したアンケートの対象人数を取得する <br>
   * [解 説] <br>
   * [備 考]
   *
   * @param emnSid アンケートSID
   * @return 対象者件数
   * @throws SQLException SQL実行時例外
   */
  public int[] getAnswerCount(long emnSid) throws SQLException {

    PreparedStatement pstmt = null;
    ResultSet rs = null;
    Connection con = null;
    int[] result = new int[3];
    con = getCon();

    try {
      // SQL文
      SqlBuffer sql = new SqlBuffer();
      sql.addSql(" select ");
      sql.addSql("   count(EMN_SID) as ALLCNT,");
      sql.addSql("   count(case when EAM_STS_KBN = ? then 1 else null end) as ANSCNT,");
      sql.addSql("   count(case when EAM_STS_KBN = ? then 1 else null end) as NOANSCNT");
      sql.addSql(" from");
      sql.addSql("   ENQ_ANS_MAIN");
      sql.addSql(" where");
      sql.addSql("   ENQ_ANS_MAIN.EMN_SID = ?");
      sql.addIntValue(GSConstEnquete.EAM_STS_KBN_YES);
      sql.addIntValue(GSConstEnquete.EAM_STS_KBN_NO);
      sql.addLongValue(emnSid);

      log__.info(sql.toLogString());

      pstmt = con.prepareStatement(sql.toSqlString());
      sql.setParameter(pstmt);
      rs = pstmt.executeQuery();
      if (rs.next()) {
        result[0] = rs.getInt("ALLCNT");
        result[1] = rs.getInt("ANSCNT");
        result[2] = rs.getInt("NOANSCNT");
      }
    } catch (SQLException e) {
      throw e;
    } finally {
      JDBCUtil.closeResultSet(rs);
      JDBCUtil.closeStatement(pstmt);
    }

    return result;
  }
Пример #3
0
  /**
   * Select PTL_PORTLET_IMAGE
   *
   * @param pltSid PLT_SID
   * @param pliSid PLI_SID
   * @return PTL_PORTLET_IMAGEModel
   * @throws SQLException SQL実行例外
   */
  public PtlPortletImageModel select(int pltSid, long pliSid) throws SQLException {

    PreparedStatement pstmt = null;
    ResultSet rs = null;
    Connection con = null;
    PtlPortletImageModel ret = null;
    con = getCon();

    try {
      // SQL文
      SqlBuffer sql = new SqlBuffer();
      sql.addSql(" select");
      sql.addSql("   PLT_SID,");
      sql.addSql("   PLI_SID,");
      sql.addSql("   BIN_SID,");
      sql.addSql("   PLI_NAME");
      sql.addSql(" from");
      sql.addSql("   PTL_PORTLET_IMAGE");
      sql.addSql(" where ");
      sql.addSql("   PLT_SID=?");
      sql.addSql(" and");
      sql.addSql("   PLI_SID=?");

      pstmt = con.prepareStatement(sql.toSqlString());
      sql.addIntValue(pltSid);
      sql.addLongValue(pliSid);

      log__.info(sql.toLogString());
      sql.setParameter(pstmt);
      rs = pstmt.executeQuery();
      if (rs.next()) {
        ret = __getPtlPortletImageFromRs(rs);
      }
    } catch (SQLException e) {
      throw e;
    } finally {
      JDBCUtil.closeResultSet(rs);
      JDBCUtil.closeStatement(pstmt);
    }
    return ret;
  }
Пример #4
0
  /**
   * Insert PTL_PORTLET_IMAGE Data Bindding JavaBean
   *
   * @param bean PTL_PORTLET_IMAGE Data Bindding JavaBean
   * @throws SQLException SQL実行例外
   */
  public void insert(PtlPortletImageModel bean) throws SQLException {

    PreparedStatement pstmt = null;
    Connection con = null;
    con = getCon();

    try {
      // SQL文
      SqlBuffer sql = new SqlBuffer();
      sql.addSql(" insert ");
      sql.addSql(" into ");
      sql.addSql(" PTL_PORTLET_IMAGE(");
      sql.addSql("   PLT_SID,");
      sql.addSql("   PLI_SID,");
      sql.addSql("   BIN_SID,");
      sql.addSql("   PLI_NAME");
      sql.addSql(" )");
      sql.addSql(" values");
      sql.addSql(" (");
      sql.addSql("   ?,");
      sql.addSql("   ?,");
      sql.addSql("   ?,");
      sql.addSql("   ?");
      sql.addSql(" )");

      pstmt = con.prepareStatement(sql.toSqlString());
      sql.addIntValue(bean.getPltSid());
      sql.addLongValue(bean.getPliSid());
      sql.addLongValue(bean.getBinSid());
      sql.addStrValue(bean.getPliName());
      log__.info(sql.toLogString());
      sql.setParameter(pstmt);
      pstmt.executeUpdate();
    } catch (SQLException e) {
      throw e;
    } finally {
      JDBCUtil.closeStatement(pstmt);
    }
  }
Пример #5
0
  /**
   * <br>
   * [機 能] 指定したアンケートの回答情報を取得する <br>
   * [解 説] <br>
   * [備 考]
   *
   * @param reqMdl リクエストモデル
   * @param emnSid アンケートSID
   * @param eqmSeq 設問連番
   * @param queKbn 設問種類
   * @return 回答情報
   * @throws SQLException SQL実行時例外
   */
  public List<Enq310QuestionSubModel> getAnswerSubList(
      RequestModel reqMdl, long emnSid, int eqmSeq, int queKbn) throws SQLException {

    PreparedStatement pstmt = null;
    ResultSet rs = null;
    Connection con = null;
    List<Enq310QuestionSubModel> ansSubList = new ArrayList<Enq310QuestionSubModel>();
    con = getCon();

    try {
      // SQL文
      SqlBuffer sql = new SqlBuffer();
      sql.addSql(" select ");
      sql.addSql("   ENQ_QUE_SUB.EMN_SID as EMN_SID,");
      sql.addSql("   ENQ_QUE_SUB.EQM_SEQ as EQM_SEQ,");
      sql.addSql("   ENQ_QUE_SUB.EQS_SEQ as EQS_SEQ,");
      sql.addSql("   ENQ_QUE_SUB.EQS_DSP_NAME as EQS_DSP_NAME,");
      sql.addSql("   ANS_MAIN.CNT as ALLCNT,");
      sql.addSql("   ANS_MAIN.ANS_CNT as ANS_CNT,");
      sql.addSql("   ANS_SUB.ANS_SUB_CNT as ANS_SUB_CNT");
      sql.addSql(" from");
      sql.addSql("   (");
      sql.addSql("     select");
      sql.addSql("       EMN_SID,");
      sql.addSql("       count(EMN_SID) as CNT,");
      sql.addSql("       count(case when EAM_STS_KBN = ? then 1 else null end) as ANS_CNT");
      sql.addSql("     from");
      sql.addSql("       ENQ_ANS_MAIN");
      sql.addSql("     where");
      sql.addSql("       ENQ_ANS_MAIN.EMN_SID = ?");
      sql.addSql("     group by");
      sql.addSql("       ENQ_ANS_MAIN.EMN_SID");
      sql.addSql("   ) ANS_MAIN,");
      sql.addSql("   ENQ_QUE_SUB");
      sql.addSql("   left join");
      sql.addSql("     (");
      sql.addSql("       select");
      sql.addSql("         ENQ_ANS_SUB.EMN_SID as EMN_SID,");
      sql.addSql("         ENQ_ANS_SUB.EQM_SEQ as EQM_SEQ,");
      sql.addSql("         ENQ_ANS_SUB.EQS_SEQ as EQS_SEQ,");
      sql.addSql("         count(ENQ_ANS_SUB.EAS_ANS) as ANS_SUB_CNT");
      sql.addSql("       from");
      sql.addSql("         ENQ_ANS_SUB");
      sql.addSql("       where");
      sql.addSql("         ENQ_ANS_SUB.EMN_SID = ?");
      sql.addSql("       and");
      sql.addSql("         ENQ_ANS_SUB.EQM_SEQ = ?");
      sql.addSql("       group by");
      sql.addSql("         ENQ_ANS_SUB.EMN_SID,");
      sql.addSql("         ENQ_ANS_SUB.EQM_SEQ,");
      sql.addSql("         ENQ_ANS_SUB.EQS_SEQ");
      sql.addSql("     ) ANS_SUB");
      sql.addSql("   on");
      sql.addSql("     ENQ_QUE_SUB.EMN_SID = ANS_SUB.EMN_SID");
      sql.addSql("     and ENQ_QUE_SUB.EQM_SEQ = ANS_SUB.EQM_SEQ");
      sql.addSql("     and ENQ_QUE_SUB.EQS_SEQ = ANS_SUB.EQS_SEQ");
      sql.addSql(" where");
      sql.addSql("   ENQ_QUE_SUB.EMN_SID = ?");
      sql.addSql(" and");
      sql.addSql("   ENQ_QUE_SUB.EQM_SEQ = ?");
      sql.addSql(" and");
      sql.addSql("   ENQ_QUE_SUB.EMN_SID = ANS_MAIN.EMN_SID ");
      sql.addSql(" order by");
      sql.addSql("   ENQ_QUE_SUB.EQS_DSP_SEC");
      sql.addIntValue(GSConstEnquete.EAM_STS_KBN_YES);
      sql.addLongValue(emnSid);
      sql.addLongValue(emnSid);
      sql.addLongValue(eqmSeq);
      sql.addLongValue(emnSid);
      sql.addLongValue(eqmSeq);

      log__.info(sql.toLogString());

      pstmt = con.prepareStatement(sql.toSqlString());
      sql.setParameter(pstmt);
      rs = pstmt.executeQuery();
      Enq310Biz biz310 = new Enq310Biz();
      while (rs.next()) {
        Enq310QuestionSubModel ansSubData = new Enq310QuestionSubModel();
        // 設問SID
        ansSubData.setEmnSid(emnSid);
        // 設問連番
        ansSubData.setQueSeq(eqmSeq);
        // 設問サブ連番
        ansSubData.setQueSubSeq(rs.getInt("EQS_SEQ"));
        // 表示名
        if (rs.getInt("EQS_SEQ") == GSConstEnquete.CHOICE_KBN_OTHER) {
          GsMessage gsMsg = new GsMessage(reqMdl);
          ansSubData.setDspName(gsMsg.getMessage("cmn.other"));
        } else {
          ansSubData.setDspName(rs.getString("EQS_DSP_NAME"));
        }
        // 対象人数
        int ansSubCnt = rs.getInt("ANS_SUB_CNT");
        ansSubData.setAnswer(StringUtil.toCommaFormat(String.valueOf(ansSubCnt)));
        ansSubData.setAnswerNum(String.valueOf(ansSubCnt));
        // 対象人数 割合 全体
        int allCnt = rs.getInt("ALLCNT");
        int arPer = biz310.getRatio(allCnt, ansSubCnt);
        ansSubData.setAnswerAllPer(String.valueOf(arPer));
        // 対象人数 割合 回答人数
        int ansCnt = rs.getInt("ANS_CNT");
        int ansPer = biz310.getRatio(ansCnt, ansSubCnt);
        ansSubData.setAnswerArPer(String.valueOf(ansPer));

        ansSubList.add(ansSubData);
      }
    } catch (SQLException e) {
      throw e;
    } finally {
      JDBCUtil.closeResultSet(rs);
      JDBCUtil.closeStatement(pstmt);
    }

    return ansSubList;
  }
Пример #6
0
  /**
   * <br>
   * [機 能] 指定したアンケートの対象人数を取得する <br>
   * [解 説] <br>
   * [備 考]
   *
   * @param emnSid アンケートSID
   * @param eqmSeq 設問連番
   * @return 対象者件数
   * @throws SQLException SQL実行時例外
   */
  public int[] getAnswerCount(long emnSid, int eqmSeq) throws SQLException {

    PreparedStatement pstmt = null;
    ResultSet rs = null;
    Connection con = null;
    int[] result = new int[3];
    con = getCon();

    try {
      // SQL文
      SqlBuffer sql = new SqlBuffer();
      sql.addSql(" select ");
      sql.addSql("   count(EMN_SID) as CNT");
      sql.addSql(" from");
      sql.addSql("   ENQ_ANS_MAIN");
      sql.addSql(" where");
      sql.addSql("   EMN_SID = ?");
      sql.addLongValue(emnSid);

      log__.info(sql.toLogString());

      pstmt = con.prepareStatement(sql.toSqlString());
      sql.setParameter(pstmt);
      rs = pstmt.executeQuery();
      if (rs.next()) {
        result[0] = rs.getInt("CNT");
      }

      JDBCUtil.closeResultSet(rs);
      JDBCUtil.closePreparedStatement(pstmt);

      sql = new SqlBuffer();
      sql.addSql(" select");
      sql.addSql("   count(distinct USR_SID) as ANSCNT");
      sql.addSql("  from");
      sql.addSql("    ENQ_ANS_SUB");
      sql.addSql("  where");
      sql.addSql("    EMN_SID = ?");
      sql.addSql("  and");
      sql.addSql("    EQM_SEQ = ?");
      sql.addSql("  and");
      sql.addSql("    EAS_ANS is not null ");
      sql.addSql("  and");
      sql.addSql("    EAS_ANS <> ''");
      sql.addLongValue(emnSid);
      sql.addLongValue(eqmSeq);

      log__.info(sql.toLogString());

      pstmt = con.prepareStatement(sql.toSqlString());
      sql.setParameter(pstmt);
      rs = pstmt.executeQuery();
      if (rs.next()) {
        result[1] = rs.getInt("ANSCNT");
        if (result[0] > 0) {
          result[2] = result[0] - result[1];
        }
      }

    } catch (SQLException e) {
      throw e;
    } finally {
      JDBCUtil.closeResultSet(rs);
      JDBCUtil.closeStatement(pstmt);
    }

    return result;
  }