/**
* 保存会员收支明细
*
* @param conn 数据库连接对象，此方法中不处理事务
* @param detail 会员收支明细
* @throws SQLException
*/
public void saveMemberAccountDetail(Connection conn, MemberAccountDetail detail)
throws SQLException {
String sql =
"insert member_account_detail(member_id,type,order_id,income,pay,balance,create_time) values(?,?,?,?,?,?,?)";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, detail.getMemberId());
ps.setInt(2, detail.getType());
ps.setInt(3, detail.getOrderId());
ps.setDouble(4, detail.getIncome());
ps.setDouble(5, detail.getPay());
ps.setDouble(6, detail.getBalance());
ps.setTimestamp(7, new Timestamp(new Date().getTime()));
ps.executeUpdate();
ps.close();
}
/**
* 分页获取收支明细列表信息
*
* @param page 分页信息
* @param param [startTime:开始时间；endTime:结束时间；memberId:会员id；type:类型；moneyFlow:资金流向]
* @return
*/
public Page<MemberAccountDetail> getMemberAccountDetailPage(
Page<MemberAccountDetail> page, Map<String, Object> param) {
Connection conn = DbUtil.getConnection();
PreparedStatement ps = null;
ResultSet rs = null;
try {
// 查询条件
StringBuilder condSql = new StringBuilder();
Timestamp startTime = (Timestamp) param.get("startTime");
Timestamp endTime = (Timestamp) param.get("endTime");
String memberId = (String) param.get("memberId");
Integer type = (Integer) param.get("type");
String moneyFlow = (String) param.get("moneyFlow");
if (StringUtils.isNotBlank(memberId)) {
condSql.append(" and o.member_id = ? ");
}
if ("income".equals(moneyFlow)) {
condSql.append(" and o.income!=0 ");
}
if ("pay".equals(moneyFlow)) {
condSql.append(" and o.pay!=0 ");
}
if (type != 0) {
condSql.append(" and o.type=? ");
}
if (startTime != null) {
condSql.append(" and o.create_time>=? ");
}
if (endTime != null) {
condSql.append(" and o.create_time<=? ");
}
// 查询总记录数
int index = 1;
ps =
conn.prepareStatement(
"select count(o.id) from member_account_detail o where 1=1 " + condSql);
if (StringUtils.isNotBlank(memberId)) {
ps.setString(index++, memberId);
}
if (type != 0) {
ps.setInt(index++, type);
}
if (startTime != null) {
ps.setTimestamp(index++, startTime);
}
if (endTime != null) {
ps.setTimestamp(index++, endTime);
}
rs = ps.executeQuery();
if (rs.next()) {
page.setTotalRecords(rs.getInt(1));
}
// 获取列表数据
if (page.getTotalRecords() > 0) {
List<MemberAccountDetail> list = new ArrayList<MemberAccountDetail>();
MemberAccountDetail o;
StringBuilder sql = new StringBuilder(50);
sql.append(
"select o.id,o.member_id,o.type,o.order_id,o.income,o.pay,o.balance,o.create_time from member_account_detail o");
sql.append(" where 1=1 ").append(condSql);
sql.append(" order by o.id desc limit ");
sql.append(page.getFirstResult()).append(",").append(page.getPageCount());
ps = conn.prepareStatement(sql.toString());
index = 1;
if (StringUtils.isNotBlank(memberId)) {
ps.setString(index++, memberId);
}
if (type != 0) {
ps.setInt(index++, type);
}
if (startTime != null) {
ps.setTimestamp(index++, startTime);
}
if (endTime != null) {
ps.setTimestamp(index++, endTime);
}
rs = ps.executeQuery();
while (rs.next()) {
o = new MemberAccountDetail();
o.setId(rs.getInt("id"));
o.setMemberId(rs.getString("member_id"));
o.setType(rs.getInt("type"));
o.setOrderId(rs.getInt("order_id"));
o.setIncome(rs.getDouble("income"));
o.setPay(rs.getDouble("pay"));
o.setBalance(rs.getDouble("balance"));
o.setCreateTime(rs.getTimestamp("create_time"));
list.add(o);
}
page.setList(list);
}
} catch (Exception e) {
log.error("分页获取收支明细列表信息时出现异常：", e);
} finally {
DbUtil.closeConnection(rs, ps, conn);
}
return page;
}