public List<ConsumeData> getAllChinaLifeMemberConsumeDateInZXQZYDIY(Connection appconn)
      throws Exception {
    log.info("Enter ZhongXinQinZiYouDIY getAllChinaLifeMemberConsumeDateInZXQZYDIY");

    boolean createConn = false;
    if (appconn == null) {
      Class.forName("oracle.jdbc.driver.OracleDriver");
      appconn = DbConnectionFactory.getInstance().getConnection("posapp");
      createConn = true;
    }

    List<ConsumeData> list = new ArrayList<ConsumeData>();

    ResultSet rs = null;
    PreparedStatement pstmt = null;

    String sql =
        "select cp.membercardid,cp.shopname,cp.amountcurrency,cp.point,cp.producttypename,cp.transdate,cp.tempmembertxid from clubpoint cp where cp.membercardid like '95519%' and clubid = '00' and shopid = '5381' order by transdate desc";

    log.info("getAllchinalife member in ZXQZYDIY sql is" + sql);

    pstmt = appconn.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);

    rs = pstmt.executeQuery();

    while (rs.next()) {
      ConsumeData data = new ConsumeData();

      String memberCardNo = rs.getString(1);
      String shopName = rs.getString(2);
      float consumeMoney = rs.getFloat(3);
      float point = rs.getFloat(4);
      String consumeType = rs.getString(5);
      Date transDate = rs.getDate(6);
      String memberTxId = rs.getString(7);

      data.setMemberCardNo(memberCardNo);
      data.setConsumeMoney(consumeMoney);
      data.setConsumeType(consumeType);
      data.setPoint(point);
      data.setShopName(shopName);
      data.setTransDate(transDate);
      data.setMemberTxId(memberTxId);

      list.add(data);
    }

    if (rs != null) rs.close();
    if (pstmt != null) pstmt.close();

    if (createConn && (appconn != null)) {
      SqlUtil.close(appconn);
      appconn = null;
    }
    log.info(
        "Exit ZhongXinQinZiYouDIY getAllChinaLifeMemberConsumeDateInZXQZYDIY list size is "
            + list.size());
    return list;
  }
  public List<ConsumeData> getReportData(Connection appconn, LinkedHashSet<String> memberTxIds)
      throws SQLException {
    log.info("Enter ZhongXinQinZiYouDIY getReportData");

    List<ConsumeData> list = new ArrayList<ConsumeData>();

    StringBuffer sqlbuf = new StringBuffer();

    int i = 0;
    int size = memberTxIds.size();

    ResultSet rs = null;
    PreparedStatement pstmt = null;

    sqlbuf
        .append(
            "select cp.id,cp.membercardid,cp.shopid,cp.shopname,cp.amountcurrency,cp.point,cp.producttypename,cp.transdate,cp.tempmembertxid from clubpoint cp where clubid = '00' and shopid = '1362' and ")
        .append(
            "((cp.transdate >= to_date('2010-08-08 00:00:01','yyyy-MM-dd HH24:mi:ss') and cp.transdate < to_date('2010-08-08 23:59:59','yyyy-MM-dd HH24:mi:ss')) or ")
        .append(
            "(cp.transdate >= to_date('2010-08-14 00:00:01','yyyy-MM-dd HH24:mi:ss') and cp.transdate < to_date('2010-08-14 23:59:59','yyyy-MM-dd HH24:mi:ss')) or ")
        .append(
            "(cp.transdate >= to_date('2010-08-15 00:00:01','yyyy-MM-dd HH24:mi:ss') and cp.transdate < to_date('2010-08-15 23:59:59','yyyy-MM-dd HH24:mi:ss'))) and cp.tempmembertxid in (");

    for (String txId : memberTxIds) {
      ++i;
      sqlbuf.append("'").append(txId).append("'");
      if (i < size) {
        sqlbuf.append(",");
      }
    }
    sqlbuf.append(" ) order by membercardid,shopid,transdate desc");

    log.info("ZhongXinQinZiYouDIY getReportData sql is " + sqlbuf.toString());

    pstmt =
        appconn.prepareStatement(
            sqlbuf.toString(), ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);

    rs = pstmt.executeQuery();

    while (rs.next()) {
      ConsumeData data = new ConsumeData();
      String clubpointId = rs.getString(1);
      String memberCardNo = rs.getString(2);
      String shopName = rs.getString(3);
      float consumeMoney = rs.getFloat(4);
      float point = rs.getFloat(5);
      String consumeType = rs.getString(6);
      Date transDate = rs.getDate(7);
      String memberTxId = rs.getString(8);

      data.setClubpointId(clubpointId);
      data.setMemberCardNo(memberCardNo);
      data.setConsumeMoney(consumeMoney);
      data.setConsumeType(consumeType);
      data.setPoint(point);
      data.setShopName(shopName);
      data.setTransDate(transDate);
      data.setMemberTxId(memberTxId);

      list.add(data);
    }

    if (rs != null) rs.close();
    if (pstmt != null) pstmt.close();

    log.info("Exit ZhongXinQinZiYouDIY getReportData list size is " + list.size());

    return list;
  }