private boolean isExsit(String activityId, String uniqueId) {
boolean flag = false;
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = DBUtil.getConnection();
String sql =
"select id from youhui_cn_fanli.tyh_activity_join where activity_id =? and `activity_unique_id` = ?;";
ps = conn.prepareStatement(sql);
ps.setString(1, activityId);
ps.setString(2, uniqueId);
rs = ps.executeQuery();
if (rs.next()) {
flag = true;
}
} catch (SQLException e) {
logger.error("error stack", e);
} catch (Exception e) {
logger.error("error stack", e);
} finally {
DBUtil.close(rs, ps, conn);
}
return flag;
}
public long getLastJoinTime(String uid, String activityId) {
long lastTime = 0;
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = DBUtil.getConnection();
String sql =
"select * from `ssyh_main`.`activity_join` where `uid` = ? and `activity_id`=? order by `create_time` desc;";
ps = conn.prepareStatement(sql);
ps.setString(1, uid);
ps.setString(2, activityId);
rs = ps.executeQuery();
if (rs.next()) {
lastTime = StringUtil.timestamp2long(rs.getTimestamp("create_time"));
}
} catch (SQLException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
} finally {
DBUtil.close(rs, ps, conn);
}
return lastTime;
}
public boolean insertShiftJF(String uid, int jf_num) {
boolean flag = false;
Connection conn = null;
PreparedStatement ps = null;
try {
conn = DBUtil.getConnection();
String sql =
"insert into `ssyh_main`.`trade_mx_"
+ uid.substring(0, 1)
+ "` (`trade_id`,`uid`,`jf_num`,`type`,`status`) values(?,?,?,?,?) ";
ps = conn.prepareStatement(sql);
ps.setString(1, StringUtil.generateTradeId());
ps.setString(2, uid);
ps.setInt(3, jf_num);
ps.setInt(4, Value.trade_mx_type_shift);
ps.setInt(5, Value.trade_mx_status_success);
ps.executeUpdate();
flag = true;
} catch (SQLException e) {
logger.error("error stack", e);
} finally {
DBUtil.close(ps, conn);
}
return flag;
}
public int getJFAddMXNum(String uid) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
int count = 0;
try {
conn = DBUtil.getConnection();
String sql =
"select count(*) from `ssyh_main`.`trade_mx_"
+ uid.substring(0, 1)
+ "` where `uid`=? and type in (1,2,3)";
ps = conn.prepareStatement(sql);
ps.setString(1, uid);
rs = ps.executeQuery();
if (rs.next()) {
count = (rs.getInt(1));
}
} catch (SQLException e) {
logger.error("error stack", e);
} finally {
DBUtil.close(rs, ps, conn);
}
return count;
}
/**
* 新增或更新用户月度活跃时长统计，返回执行结果
*
* @param bean 用户月度活跃时长统计Bean
* @return 执行结果
*/
public boolean addOrUpdateMAUStats(MAUStatsBean bean) {
boolean result = false;
Connection conn = null;
PreparedStatement ps = null;
String sql =
"INSERT INTO `ssyh_main`.`mau_stats`"
+ "(`month_str`,`uid`,`stay_seconds`) VALUES(?,?,?) "
+ "ON DUPLICATE KEY UPDATE `stay_seconds`=`stay_seconds` + ?;";
try {
conn = DBUtil.getConnection();
ps = conn.prepareStatement(sql);
// insert参数
ps.setString(1, bean.getMonthStr());
ps.setString(2, bean.getUid());
ps.setInt(3, bean.getStaySeconds());
// update参数
ps.setInt(4, bean.getStaySeconds());
ps.executeUpdate();
result = true;
} catch (SQLException e) {
logger.error("addOrUpdateMAUStats exception:" + e.getMessage(), e);
} finally {
DBUtil.close(null, ps, conn);
}
return result;
}
public int join(
Activity activity, String uid, int runningDay, int status, String activityUniqueId) {
int ret = 0;
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = DBUtil.getConnection();
if (activity == null
|| activity.getId() == null
|| "".equals(activity.getId())
|| uid == null
|| "".equals(uid)) {
return ret;
}
if (isExsit(activity.getId(), activityUniqueId)) {
ret = 2;
return ret;
}
String sql =
"insert into `ssyh_main`.`activity_join`(`id`,`uid`,`date`,`running_days`,`create_time`,`jf_num`,`activity_id`,`activity_name`,`status`,`activity_unique_id`) values(?,?,?,?,?,?,?,?,?,?)";
ps = conn.prepareStatement(sql);
String today = ft.format(new Date());
int n = -1;
int j = 0;
String tradeId = null;
while (n < 0 && j++ < 10) {
tradeId = getTradeId(uid);
ps.setString(1, tradeId);
ps.setString(2, uid);
ps.setString(3, today);
ps.setInt(4, runningDay);
ps.setTimestamp(5, StringUtil.long2timestamp(System.currentTimeMillis()));
ps.setInt(6, activity.getJfNum());
ps.setString(7, activity.getId());
ps.setString(8, activity.getName());
ps.setInt(9, status);
ps.setString(10, activityUniqueId);
n = ps.executeUpdate();
if (n > 0) {
ret = 1;
}
}
} catch (SQLException e) {
logger.error("error stack", e);
} catch (Exception e) {
logger.error("error stack", e);
} finally {
DBUtil.close(rs, ps, conn);
}
return ret;
}
/**
* 新增或更新用户月度活跃时长统计，返回执行结果
*
* @param uidList 月度活跃uid分页清单
* @param millisList 对应用户在线时长list
* @param monthStr 统计月度，yyyyMM
* @return 执行结果
*/
public boolean batchAddOrUpdateMAUStats(
List<String> uidList, List<String> millisList, String monthStr) {
if (uidList == null || millisList == null || uidList.isEmpty() || millisList.isEmpty()) {
return false;
}
if (uidList.size() != millisList.size()) {
return false;
}
int listSize = uidList.size();
boolean result = false;
Connection conn = null;
PreparedStatement ps = null;
String sql =
"INSERT INTO `ssyh_main`.`mau_stats`"
+ "(`month_str`,`uid`,`stay_seconds`,`stay_minutes`,`stay_hours`) VALUES(?,?,?,?,?) "
+ "ON DUPLICATE KEY UPDATE `stay_seconds` = ?,`stay_minutes`=?,`stay_hours`=?;";
try {
conn = DBUtil.getConnection();
// 关闭事务自动提交
conn.setAutoCommit(false);
ps = conn.prepareStatement(sql);
for (int i = 0; i < listSize; i++) {
String uid = uidList.get(i);
String millisStr = millisList.get(i);
long stayMillis = NumberUtils.isNumber(millisStr) ? Long.parseLong(millisStr) : 0L;
MAUStatsBean bean = new MAUStatsBean(monthStr, uid, stayMillis);
// insert参数
ps.setString(1, bean.getMonthStr());
ps.setString(2, bean.getUid());
ps.setInt(3, bean.getStaySeconds());
ps.setInt(4, bean.getStayMinutes());
ps.setDouble(5, bean.getStayHours());
// update参数
ps.setInt(6, bean.getStaySeconds());
ps.setInt(7, bean.getStayMinutes());
ps.setDouble(8, bean.getStayHours());
ps.addBatch();
}
ps.executeBatch();
// 关闭事务自动提交
conn.setAutoCommit(true);
result = true;
} catch (SQLException e) {
result = false;
logger.error("addOrUpdateMAUStats exception:" + e.getMessage(), e);
} finally {
DBUtil.close(null, ps, conn);
}
return result;
}
/**
* 删除指定月份之前的 所有月度活跃时长统计数据
*
* @param yearMonthInt 指定月份，整形
* @return 删除结果
*/
public boolean deleteBeforeMonth(int yearMonthInt) {
boolean deleteResult = false; // 删除结果
Connection conn = null;
PreparedStatement ps = null;
String sql = "DELETE FROM `ssyh_main`.`mau_stats` WHERE `month_str` < ?;";
try {
conn = DBUtil.getConnection();
ps = conn.prepareStatement(sql);
ps.setInt(1, yearMonthInt);
deleteResult = ps.executeUpdate() > 0 ? true : false;
} catch (SQLException e) {
logger.error("deleteBeforeMonth exception:" + e.getMessage(), e);
} finally {
DBUtil.close(ps, conn);
}
return deleteResult;
}
/**
* 新增或更新用户月度活跃时长统计，返回执行结果
*
* @param bean 用户月度活跃时长统计Bean
* @return 执行结果
*/
public boolean batchAddOrUpdateMAUStats(List<MAUStatsBean> beans) {
boolean result = false;
Connection conn = null;
PreparedStatement ps = null;
String sql =
"INSERT INTO `ssyh_main`.`mau_stats`"
+ "(`month_str`,`uid`,`stay_seconds`,`stay_minutes`,`stay_hours`) VALUES(?,?,?,?,?) "
+ "ON DUPLICATE KEY UPDATE `stay_seconds` = ?,`stay_minutes`=?,`stay_hours`=?;";
try {
conn = DBUtil.getConnection();
// 关闭事务自动提交
conn.setAutoCommit(false);
ps = conn.prepareStatement(sql);
for (MAUStatsBean bean : beans) {
// insert参数
ps.setString(1, bean.getMonthStr());
ps.setString(2, bean.getUid());
ps.setInt(3, bean.getStaySeconds());
ps.setInt(4, bean.getStayMinutes());
ps.setDouble(5, bean.getStayHours());
// update参数
ps.setInt(6, bean.getStaySeconds());
ps.setInt(7, bean.getStayMinutes());
ps.setDouble(8, bean.getStayHours());
ps.addBatch();
}
ps.executeBatch();
// 关闭事务自动提交
conn.setAutoCommit(true);
result = true;
} catch (SQLException e) {
logger.error("addOrUpdateMAUStats exception:" + e.getMessage(), e);
} finally {
DBUtil.close(null, ps, conn);
}
return result;
}
public List<TradeMx> getJFAddMX(String uid, int page, int pageSize) {
List<TradeMx> tradeMxList = new ArrayList<TradeMx>();
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = DBUtil.getConnection();
String sql =
"select * from `ssyh_main`.`trade_mx_"
+ uid.substring(0, 1)
+ "` where `uid`=? and type in (1,2,3) limit ?,?";
ps = conn.prepareStatement(sql);
ps.setString(1, uid);
ps.setInt(2, (pageSize * (page - 1)));
ps.setInt(3, pageSize);
rs = ps.executeQuery();
while (rs.next()) {
TradeMx tradeMx = new TradeMx();
tradeMx.setDescription(rs.getString("description"));
tradeMx.setId(rs.getInt("id"));
tradeMx.setInsert_time(StringUtil.timestamp2datetime(rs.getTimestamp("insert_time")));
tradeMx.setJf_num(rs.getInt("jf_num"));
tradeMx.setStatus(rs.getInt("status"));
tradeMx.setTrade_id(rs.getString("trade_id"));
tradeMx.setType(rs.getInt("type"));
tradeMx.setUid(uid);
tradeMx.setIcon(
"http://gi2.md.alicdn.com/bao/uploaded/i2/TB1ypNbHXXXXXc1XpXXXXXXXXXX_!!0-item_pic.jpg_430x430q90.jpg");
tradeMxList.add(tradeMx);
}
} catch (SQLException e) {
logger.error("error stack", e);
} finally {
DBUtil.close(rs, ps, conn);
}
return tradeMxList;
}