CoreService.java
1 package org.weixin.service; 2 3 import java.text.DateFormat; 4 import java.text.SimpleDateFormat; 5 import java.util.Calendar; 6 import java.util.Date; 7 import java.util.HashMap; 8 9 import javax.servlet.http.HttpServletRequest; 10 11 import org.weixin.message.TextMessage; 12 import org.weixin.util.MessageUtil; 13 import org.weixin.util.MySQLUtil; 14 15 public class CoreService { 16 public static String processRequest(HttpServletRequest request){ 17 String respXML = null; 18 TextMessage tm = new TextMessage(); 19 //解析微信服务器发送的请求 20 try { 21 HashMap<String,String>requestMap = MessageUtil.parseXML(request); 22 //用户的OpenID 23 String fromUserName = requestMap.get("FromUserName"); 24 //公众号的原始ID 25 String toUserName = requestMap.get("ToUserName"); 26 //请求消息类型 27 String msgType = requestMap.get("MsgType"); 28 29 //回复 30 tm.setFromUserName(toUserName); 31 tm.setToUserName(fromUserName); 32 tm.setMsgType(MessageUtil.RESP_MESSAGE_TYPE_TEXT); 33 tm.setCreateTime(new Date().getTime()); 34 35 //将文本消息保存到数据库 36 if(msgType.equals(MessageUtil.REQ_MESSAGE_TYPE_TEXT)){ 37 String content = requestMap.get("Content"); 38 boolean result1 = MySQLUtil.isTodaySigned(fromUserName); 39 boolean result2 = MySQLUtil.judgeSign(fromUserName); 40 if(content.equals("签到")){ 41 if(!result1){ 42 MySQLUtil.saveDate(fromUserName); 43 } 44 if(result2){ 45 MySQLUtil.signIn(fromUserName); 46 tm.setContent("签到成功[机智]\n签到时间为：\n"+MySQLUtil.showNow()); 47 }else{ 48 tm.setContent("上次还没签退呢[皱眉]无法签到！"); 49 } 50 } 51 if(content.equals("签退")){ 52 if(!result2){ 53 tm.setContent("签退成功[嘿哈]\n签退时间为：\n"+MySQLUtil.showNow()+"\n本次学习了 "+MySQLUtil.signOut(fromUserName)); 54 }else{ 55 tm.setContent("还没签到就想签退[奸笑]签到去吧孩子！"); 56 } 57 } 58 if(content.equals("查今天")){ 59 tm.setContent("今天已经学习了 "+MySQLUtil.selectDayTime(fromUserName, "today")); 60 } 61 if(content.equals("查昨天")){ 62 tm.setContent("你昨天学习了 "+MySQLUtil.selectDayTime(fromUserName, "yesterday")); 63 } 64 if(content.equals("查本周")){ 65 tm.setContent("你本周学习了 "+MySQLUtil.selectWeekTime(fromUserName)); 66 } 67 if(content.contains("bd")){ 68 tm.setContent("您的微信已绑定学号"+MySQLUtil.linkNumber(fromUserName, content)); 69 } 70 respXML=MessageUtil.messageToXML(tm); 71 } 72 else if(msgType.equals(MessageUtil.REQ_MESSAGE_TYPE_IMAGE)){ 73 tm.setContent("您发送的是图片消息！"); 74 respXML=MessageUtil.messageToXML(tm); 75 } 76 else if(msgType.equals(MessageUtil.REQ_MESSAGE_TYPE_EVENT)){ 77 //事件类型 78 String eventType = requestMap.get("Event"); 79 //关注事件 80 if(eventType.equals(MessageUtil.EVENT_TYPE_SUBSCRIBE)){ 81 tm.setContent("欢迎关注！"); 82 //MySQLUtil.saveWeixinUser(fromUserName); 83 } 84 else if(eventType.equals(MessageUtil.EVENT_TYPE_UNSUBSCRIBE)){ 85 //更新weixin_user表的关注状态 86 } 87 } 88 //respXML = MessageUtil.messageToXML(tm); 89 } catch (Exception e) { 90 e.printStackTrace(); 91 } 92 return respXML; 93 } 94 }
MySQLUtil.java
package org.weixin.util; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; public class MySQLUtil { public Connection getConnection() { Connection conn = null; String url = "jdbc:mysql://127.0.0.1:3306/student?useSSL=true"; String username = "root"; String password = "123456"; try { Class.forName("com.mysql.jdbc.Driver"); conn = DriverManager.getConnection(url, username, password); } catch (Exception e) { e.printStackTrace(); } return conn; } public void releaseResource(Connection conn, PreparedStatement ps, ResultSet rs) { try { if (null != rs) { rs.close(); } if (null != ps) { ps.close(); } if (null != conn) { conn.close(); } } catch (Exception e) { e.printStackTrace(); } } // 绑定学号 public static String linkNumber(String openId, String s) { String number = s.substring(s.length()-10,s.length()); MySQLUtil mysql = new MySQLUtil(); Connection conn = mysql.getConnection(); String sql = "UPDATE user SET open_id = ? WHERE number = ?"; PreparedStatement ps = null; try { ps = conn.prepareStatement(sql); ps.setString(1, openId); ps.setString(2, number); ps.execute(); } catch (SQLException e) { e.printStackTrace(); } finally { mysql.releaseResource(conn, ps, null); } return number; } // 签到 public static void signIn(String openId) { MySQLUtil mysql = new MySQLUtil(); Connection conn = mysql.getConnection(); String sql = "insert into sign_in_out (open_id,date,sign_in) values (?,curdate(),curtime())"; PreparedStatement ps = null; try { ps = conn.prepareStatement(sql); ps.setString(1, openId); ps.execute(); } catch (SQLException e) { e.printStackTrace(); } finally { mysql.releaseResource(conn, ps, null); } } // 存储用户名及日期到day_time public static void saveDate(String openId) { MySQLUtil mysql = new MySQLUtil(); Connection conn = mysql.getConnection(); String sql = "insert into day_time (open_id,date,time_count,weekday) values (?,curdate(),0,WEEKDAY(NOW())+1)"; PreparedStatement ps = null; try { ps = conn.prepareStatement(sql); ps.setString(1, openId); ps.execute(); } catch (SQLException e) { e.printStackTrace(); } finally { mysql.releaseResource(conn, ps, null); } } // 签退 public static String signOut(String openId) { int maxId = 0; String result; MySQLUtil mysql = new MySQLUtil(); Connection conn = mysql.getConnection(); ResultSet rs = null; PreparedStatement ps = null; String sql1 = "SELECT MAX(id) as max FROM sign_in_out WHERE open_id = ? AND date = CURDATE()"; try { ps = conn.prepareStatement(sql1); ps.setString(1, openId); rs = ps.executeQuery(); if(rs.next()){ maxId = rs.getInt("max"); } } catch (SQLException e1) { e1.printStackTrace(); } String sql2 = "UPDATE sign_in_out SET sign_out = curtime() WHERE id = ?"; try { ps = conn.prepareStatement(sql2); ps.setInt(1, maxId); ps.execute(); } catch (SQLException e) { e.printStackTrace(); } finally { mysql.releaseResource(conn, ps, rs); } result = MySQLUtil.countDayTime(openId, maxId); return result; } // 计算当天总时间 public static String countDayTime(String openId, int maxId) { int sec = 0; String result = null; MySQLUtil mysql = new MySQLUtil(); Connection conn = mysql.getConnection(); ResultSet rs = null; PreparedStatement ps = null; String sql1 = "select (TIME_TO_SEC(sign_out) - TIME_TO_SEC(sign_in)) sec from sign_in_out WHERE id = ? "; try { ps = conn.prepareStatement(sql1); ps.setInt(1, maxId); rs = ps.executeQuery(); if(rs.next()){ sec = rs.getInt("sec"); result = sec/3600 + "小时" + sec%3600/60 + "分" + sec%3600%60 +"秒"; } } catch (SQLException e1) { e1.printStackTrace(); } String sql2 = "UPDATE day_time SET time_count = SEC_TO_TIME(TIME_TO_SEC(time_count)+"+sec+") WHERE open_id = ? AND date = CURDATE()"; try { ps = conn.prepareStatement(sql2); ps.setString(1, openId); ps.execute(); } catch (SQLException e) { e.printStackTrace(); } finally { mysql.releaseResource(conn, ps, rs); } return result; } // 判断用户今天是否签到过 public static boolean isTodaySigned(String openId) { boolean result = false; MySQLUtil mysql = new MySQLUtil(); Connection conn = mysql.getConnection(); ResultSet rs = null; PreparedStatement ps = null; String sql = "SELECT count(*) as i FROM sign_in_out WHERE open_id= ? AND date = curdate()"; try { ps = conn.prepareStatement(sql); ps.setString(1, openId); rs = ps.executeQuery(); int signCounts = 0; if(rs.next()){ signCounts = rs.getInt("i"); } if(signCounts>0){ result = true; } } catch (SQLException e) { e.printStackTrace(); } finally { mysql.releaseResource(conn, ps, rs); } return result; } //判断是否签退 没签退不能签到 没签到不能签退 public static boolean judgeSign(String openId) { boolean result = false; MySQLUtil mysql = new MySQLUtil(); Connection conn = mysql.getConnection(); ResultSet rs = null; PreparedStatement ps = null; String sql1 = "SELECT sign_out FROM sign_in_out WHERE open_id = ? ORDER BY id DESC"; try { ps = conn.prepareStatement(sql1); ps.setString(1, openId); rs = ps.executeQuery(); if(rs.next()){ if(rs.getString(1)!=null){ result = true; } }else{ result = true; } } catch (SQLException e1) { e1.printStackTrace(); } finally { mysql.releaseResource(conn, ps, rs); } return result; } //查询每日时间 今天 昨天 public static String selectDayTime(String openId, String day) { String dayTime = "\n查什么吖[捂脸][捂脸][捂脸]你都没来过"; int time = 0; MySQLUtil mysql = new MySQLUtil(); Connection conn = mysql.getConnection(); ResultSet rs = null; PreparedStatement ps = null; String sql = ""; if(day.equals("today")){ sql = "SELECT TIME_TO_SEC(time_count) FROM day_time WHERE open_id = ? AND date = CURDATE()"; }else if(day.equals("yesterday")){ sql = "SELECT TIME_TO_SEC(time_count) FROM day_time WHERE open_id = ? AND date = DATE_SUB(curdate(),INTERVAL 1 DAY)"; } try { ps = conn.prepareStatement(sql); ps.setString(1, openId); rs = ps.executeQuery(); if(rs.next()){ time = rs.getInt(1); dayTime = time/3600 + "小时" + time%3600/60 + "分" + time%3600%60 + "秒[惊讶]"; } } catch (SQLException e1) { e1.printStackTrace(); } finally { mysql.releaseResource(conn, ps, rs); } return dayTime; } //查询本周时间 public static String selectWeekTime(String openId) { String weekTime = "\n查什么吖[捂脸][捂脸][捂脸]你都没来过"; int time = 0; MySQLUtil mysql = new MySQLUtil(); Connection conn = mysql.getConnection(); ResultSet rs = null; PreparedStatement ps = null; String sql = "SELECT TIME_TO_SEC(time_count) FROM day_time WHERE open_id = ? AND date BETWEEN (SELECT DATE_SUB(CURDATE(),INTERVAL (SELECT WEEKDAY(NOW())) DAY)) AND CURDATE()"; try { ps = conn.prepareStatement(sql); ps.setString(1, openId); rs = ps.executeQuery(); while(rs.next()){ time = time + rs.getInt(1); } weekTime = time/3600 + "小时" + time%3600/60 + "分" + time%3600%60 + "秒[惊讶]"; } catch (SQLException e1) { e1.printStackTrace(); } finally { mysql.releaseResource(conn, ps, rs); } return weekTime; } //显示时间 public static String showNow() { String dateTime = null; MySQLUtil mysql = new MySQLUtil(); Connection conn = mysql.getConnection(); ResultSet rs = null; PreparedStatement ps = null; String sql = "SELECT NOW()"; try { ps = conn.prepareStatement(sql); rs = ps.executeQuery(); while(rs.next()){ dateTime = rs.getString(1); } } catch (SQLException e1) { e1.printStackTrace(); } finally { mysql.releaseResource(conn, ps, rs); } return dateTime; } }
用户表 签到签退表 每日时间表