package com.muyu.system.service.impl;

import com.muyu.common.system.domain.SysUser;
import com.muyu.system.service.DataBaseSelect;
import org.springframework.stereotype.Service;

import java.sql.*;
import java.util.List;

/**
 * 从数据源查询信息业务实现层
 * @Author：weiran
 * @Package：com.muyu.system.service.impl
 * @Project：cloud-server-8
 * @name：DataBaseSelectImpl
 * @Date：2024/9/30 20:09
 */
@Service
public class DataBaseSelectImpl implements DataBaseSelect {



    /**
     * 查询这个数据源中的登录账号和密码
     * @param username
     * @param password
     * @param databaseName
     */
    @Override
    public SysUser selectuser(String username, String password, String databaseName) {
        String URL="jdbc:mysql://159.75.188.178:3306/"+databaseName+"?useSSL=false&serverTimezone=UTC";
        String USER="root";
        String PASS="bwie-8666";
        Connection conn=null;
        PreparedStatement preparedStatement=null;
        ResultSet rs =null;
        SysUser sysUser=null;
        try{
            //注册JDBC驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //打开连接
            conn= DriverManager.getConnection(URL,USER,PASS);
            //创建Sql查询语句
            String sql= "select u.user_id, "+
                        "u.dept_id, "+
                        "u.user_name, "+
                        "u.nick_name, "+
                        "u.email, "+
                        "u.avatar, "+
                        "u.phonenumber, "+
                        "u.password, "+
                        "u.sex, "+
                        "u.status, "+
                        "u.del_flag, "+
                        "u.login_ip, "+
                        "u.login_date, "+
                        "u.firm_id, "+
                        "u.create_by, "+
                        "u.create_time, "+
                        "u.remark, "+
                        "ur.role_id, "+
                        "cr.firm_name, "+
                        "cr.database_name "+
                    "from " + databaseName + ".sys_user u " +
                    "left join " + databaseName + ".sys_user_role ur on u.user_id = ur.user_id " +
                    "left join eight.firm cr on u.firm_id = cr.firm_id " +
                    "where u.user_name = ?";
            preparedStatement=conn.prepareStatement(sql);
            preparedStatement.setString(1, username);
            //执行查询
            rs=preparedStatement.executeQuery();
            //处理结果集
            while (rs.next()){
                sysUser=new SysUser();
                sysUser.setUserId(rs.getLong("user_id"));
                sysUser.setDeptId(rs.getLong("dept_id"));
                sysUser.setUserName(rs.getString("user_name"));
                sysUser.setNickName(rs.getString("nick_name"));
                sysUser.setEmail(rs.getString("email"));
                sysUser.setPhonenumber(rs.getString("phonenumber"));
                sysUser.setSex(rs.getString("sex"));
                sysUser.setAvatar(rs.getString("avatar"));
                sysUser.setPassword(rs.getString("password"));
                sysUser.setStatus(rs.getString("status"));
                sysUser.setDelFlag(rs.getString("del_flag"));
                sysUser.setLoginIp(rs.getString("login_ip"));
                sysUser.setLoginDate(rs.getDate("login_date"));
                sysUser.setFirmId(rs.getInt("firm_id"));
                sysUser.setCreateBy(rs.getString("create_by"));
                sysUser.setCreateTime(rs.getDate("create_time"));
                sysUser.setRemark(rs.getString("remark"));
//                sysUser.setRoleIds(rs.getObject("role_id", Long[].class));
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            //关闭资源
            try {
                if (rs!=null){
                    rs.close();
                }
                if (preparedStatement!=null){
                    preparedStatement.close();
                }
                if (conn!=null){
                    conn.close();
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        return sysUser;
    }
}
