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;
}
