package com.example.myapplication.dao;
import android.util.Log;
import com.example.myapplication.entity.User;
import com.example.myapplication.utils.JDBCUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.Calendar;
import java.util.Date;
public class UserDao {
private static final String TAG = "mysql-party-UserDao";
/**
* function: 登录
*/
public static int login(String userAccount, String userPassword) {
Connection connection = null;
boolean success = false;
try {
connection = JDBCUtils.getConn();
if (connection != null) {
String query = "SELECT * FROM user WHERE UserID = ? AND password = ?";
try (PreparedStatement pstmt = connection.prepareStatement(query)) {
pstmt.setString(1, userAccount);
pstmt.setString(2, userPassword);
try (ResultSet rs = pstmt.executeQuery()) {
if (rs.next()) {
String a1 = rs.getString("UserID");
if(a1.length() == 10) {
Log.d("MysqlHelp_denglu", "用户名和密码匹配成功！");
success = true;
return 1;
}else if (a1.length() < 10){
Log.d("MysqlHelp_denglu", "用户名和密码匹配成功！");
success = true;
return 2;
}
} else {
Log.d("MysqlHelp_denglu", "用户名和密码不匹配。");
success = false;
}
}
}
} else {
Log.d("MysqlHelp_denglu", "无法获取数据库连接。");
}
} catch (Exception ex) {
Log.e("MysqlHelp_denglu", "验证登录时发生异常", ex);
success = false;
}
return 0;
}
/**
* function: 注册
*/
public static boolean register(User user) {
// 根据数据库名称，建立连接
Connection connection = JDBCUtils.getConn();
try {
String sql = "insert into k1(userAccount,userName,userPhone,userClass,userPassword) values (?,?,?,?,?)";
if (connection != null) {// connection不为null表示与数据库建立了连接
PreparedStatement ps = connection.prepareStatement(sql);
if (ps != null) {
//将数据插入数据库
ps.setString(1, user.getUserAccount());
ps.setString(2, user.getUserName());
ps.setString(3, user.getUserPhone());
ps.setString(4, user.getUserClass());
ps.setString(5, Integer.toString(123456));
// 执行sql查询语句并返回结果集
int rs = ps.executeUpdate();
if (rs > 0)
return true;
else
return false;
} else {
return false;
}
} else {
return false;
}
} catch (Exception e) {
e.printStackTrace();
Log.e(TAG, "异常register：" + e.getMessage());
return false;
}
}
/**
* function: 打卡
*/
// 在方法中获取当前系统日期并插入数据库
public static String daka(String time_s, String time_e, String text) {
int text1 = Integer.parseInt(text);
StringBuilder resultBuilder = new StringBuilder();
Connection connection = JDBCUtils.getConn();
try {
if (connection != null) {
System.out.println("11234");
}
if (connection != null) {
String sql1 = "SELECT * FROM room WHERE MeetroomId = ? AND Capacity <= ?";
PreparedStatement ps1 = connection.prepareStatement(sql1);
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
String currentDate = dateFormat.format(new Date());
ps1.setString(1, time_s);
// ps1.setString(3, time_e);
ps1.setInt(2, text1);
ResultSet resultSet = ps1.executeQuery();
while (resultSet.next()) {
String date = resultSet.getString("MeetroomId");
String resultAccount = resultSet.getString("MeetroomName");
String times = resultSet.getString("Address");
String timee = resultSet.getString("ReadyState");
String text2 = resultSet.getString("Capacity");
// 将查询结果拼接为字符串
resultBuilder.append("会议室编号：").append(date).append("\n").append("会议室名称: ").append(resultAccount).append("\n")
.append("会议室地址: ").append(times).append("\n")
.append("可用状态: ").append(timee).append("\n")
.append("容纳人数: ").append(text).append("\n\n");
}
} else {
return "0"; // 连接数据库失败，返回"0"
}
} catch (Exception e) {
e.printStackTrace();
System.out.println("发生异常：" + e.getMessage()); // 输出异常信息
return "0"; // 发生异常，返回"0"
} finally {
// 可以在这里关闭连接等资源
}
return resultBuilder.toString();
}
/**
* function: 打卡记录
*/
public static String records(String account) throws SQLException {
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
StringBuilder resultBuilder = new StringBuilder();
try {
connection = JDBCUtils.getConn();
String sql = "SELECT * FROM room";
statement = connection.prepareStatement(sql);
resultSet = statement.executeQuery();
while (resultSet.next()) {
String date = resultSet.getString("MeetroomId");
String resultAccount = resultSet.getString("MeetroomName");
String times = resultSet.getString("Address");
String timee = resultSet.getString("ReadyState");
String text = resultSet.getString("Capacity");
// 将查询结果拼接为字符串
resultBuilder.append("会议室编号：").append(date).append("\n").append("会议室名称: ").append(resultAccount).append("\n")
.append("会议室地址: ").append(times).append("\n")
.append("可用状态: ").append(timee).append("\n")
.append("容纳人数: ").append(text).append("\n\n");
}
} catch (SQLException e) {
e.printStackTrace();
}
return resultBuilder.toString();
}
/**
* function: 设置学习目标
*/
public static boolean tar(String title1,String main11,String t_s1,String t_e1,String p_n1,String p_p1,String room1,String account) {
// 根据数据库名称，建立连接
Connection connection = JDBCUtils.getConn();
try {
String sql = "insert into meeting (MeetingName, MeetingContent,MeetingBegin,MeetingEnd,MeetingNum,Participants ,MeetroomId ,UserID) values (?,?,?,?,?,?, ?, ?)";
if (connection != null) { // connection 不为null表示与数据库建立了连接
PreparedStatement ps = connection.prepareStatement(sql);
if (ps != null) {
System.out.println("12345678");
ps.setString(1, title1);
ps.setString(2, main11);
ps.setString(3, t_s1);
ps.setString(4, t_e1);
ps.setString(5, p_n1);
ps.setString(6, p_p1);
ps.setString(7, room1);
ps.setString(8, account);
// 执行sql查询语句并返回结果集
int rs = ps.executeUpdate();
if (rs > 0)
return true;
else
return false;
} else {
return false;
}
} else {
return false;
}
} catch (Exception e) {
e.printStackTrace();
return false;
}
}
/**
* function: 完成度分析
*/
public static boolean ana(String account) {
//
//
//
// // 格式化日期为 "yyyy-MM-dd" 格式的字符串
//
// try {
//
// // 构建更新语句，更新符合账户和前四天日期的 analysis 数据
// String updateSql = "SELETE ";
// PreparedStatement updatePs = connection.prepareStatement(updateSql);
//
// updatePs.setString(1, user.getAnalysis());
// updatePs.setString(2, user.getBuzu());
// updatePs.setString(3, formattedDate);
// updatePs.setString(4, account);
//
// int result = updatePs.executeUpdate(); // 执行更新操作
//
//
// return result > 0; // 返回更新是否成功的结果
// } catch (SQLException e) {
// e.printStackTrace();
return false; // 更新过程中出现异常，返回更新失败
}
/**
* function: 统计打卡次数
*/
public static String count(String account) throws SQLException {
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
StringBuilder resultBuilder = new StringBuilder();
try {
connection = JDBCUtils.getConn();
if (connection != null) {
// 获取当前系统日期
Calendar calendar = Calendar.getInstance();
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM");
String formattedDate = dateFormat.format(calendar.getTime());
String query = "SELECT COUNT(*) AS count FROM k2 WHERE Account = ? AND SUBSTRING(date, 1, 7) = ?";
statement = connection.prepareStatement(query);
statement.setString(1, account);
statement.setString(2, formattedDate);
resultSet = statement.executeQuery();
if (resultSet.next()) {
int count = resultSet.getInt("count");
System.out.println("统计结果: " + count);
resultBuilder.append("打卡次数：").append(count).append("\n");
}
}
} finally {
if (resultSet != null) {
resultSet.close();
}
if (statement != null) {
statement.close();
}
if (connection != null) {
connection.close();
}
}
return resultBuilder.toString();
}
/**
* function: 统计目标次数
* 及统计平均达成度
*/
public static String count2(String account) throws SQLException {
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
StringBuilder resultBuilder = new StringBuilder();
int count = 0;
int sumAnalysis = 0;
String countString = "0"; // 默认值为 "0"
try {
connection = JDBCUtils.getConn();
if (connection != null) {
// 获取当前系统日期的年月部分
Calendar calendar = Calendar.getInstance();
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM");
String formattedDate = dateFormat.format(calendar.getTime());
String queryCount = "SELECT COUNT(*) AS count FROM k3 WHERE Account = ? AND SUBSTRING(date1, 1, 7) = ?";
statement = connection.prepareStatement(queryCount);
statement.setString(1, account);
statement.setString(2, formattedDate);
resultSet = statement.executeQuery();
if (resultSet.next()) {
count = resultSet.getInt("count");
countString = String.valueOf(count); // 将整数转换为字符串
resultBuilder.append("本月学习目标：").append(count).append("\n");
System.out.println("统计结果: " + countString);
}
// 查询并计算对应月份的 analysis 数据总和
String querySum = "SELECT SUM(analysis) AS sumAnalysis FROM k3 WHERE Account = ? AND SUBSTRING(date1, 1, 7) = ?";
statement = connection.prepareStatement(querySum);
statement.setString(1, account);
statement.setString(2, formattedDate);
resultSet = statement.executeQuery();
if (resultSet.next()) {
sumAnalysis = resultSet.getInt("sumAnalysis");
if(count != 0){
int anaaverage = sumAnalysis / count;
resultBuilder.append("本月平均达成度：").append(anaaverage).append("\n");
System.out.println("本月平均达成度: " + sumAnalysis);
}
else{
resultBuilder.append("本月平均达成度：").append("无学习目标").append("\n");
}
}
}
} finally {
if (resultSet != null) {
resultSet.close();
}
if (statement != null) {
statement.close();
}
if (connection != null) {
connection.close();
}
}
return resultBuilder.toString();
}
/**
* function: 统计所有学生打卡
*/
public static String Clock_in_Summary(String account) throws SQLException {
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
StringBuilder resultBuilder = new StringBuilder();
try {
connection = JDBCUtils.getConn();
String sql = "SELECT * FROM k2 ";
statement = connection.prepareStatement(sql);
resultSet = statement.executeQuery();
while (resultSet.next()) {
String date = resultSet.getString("date");
String resultAccount = resultSet.getString("Account");
String times = resultSet.getString("time_s");
String timee = resultSet.getString("time_e");
String text = resultSet.getString("text");
// 将查询结果拼接为字符串
resultBuilder.append("日期：").append(date).append("\n").append("学号: ").append(resultAccount).append("\n")
.append("开始时间: ").append(times).append("\n")
.append("结束时间: ").append(timee).append("\n")
.append("学习内容: ").append(text).append("\n\n");
}
} catch (SQLException e) {
e.printStackTrace();
}
return resultBuilder.toString();
}
/**
* function: 统计所有学生每日学习目标
*
*/
public static boolean sumana(User user, String account) {
Connection connection = JDBCUtils.getConn();
// 获取当前系统日期
Calendar calendar = Calendar.getInstance();
// 往前推四天
calendar.add(Calendar.DAY_OF_MONTH, -4);
// 格式化日期为 "yyyy-MM-dd" 格式的字符串
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
String formattedDate = dateFormat.format(calendar.getTime());
try {
// 构建更新语句，更新符合账户和前四天日期的 analysis 数据
String updateSql = "UPDATE k3 SET analysis = ?, buzu = ? WHERE date1 = ? AND account = ?";
PreparedStatement updatePs = connection.prepareStatement(updateSql);
updatePs.setString(1, user.getAnalysis());
updatePs.setString(2, user.getBuzu());
updatePs.setString(3, formattedDate);
updatePs.setString(4, account);
int result = updatePs.executeUpdate(); // 执行更新操作
return result > 0; // 返回更新是否成功的结果
} catch (SQLException e) {
e.printStackTrace();
return false; // 更新过程中出现异常，返回更新失败
}
}
}
浙公网安备 33010602011771号