package org.springblade.modules.FTP;
|
|
|
import org.json.JSONArray;
|
import org.json.JSONObject;
|
import org.springblade.common.utils.InvestigateUtil;
|
import org.springblade.modules.system.entity.User;
|
import org.springblade.modules.system.service.IUserService;
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.stereotype.Component;
|
|
import javax.annotation.PostConstruct;
|
import java.io.File;
|
import java.sql.Connection;
|
import java.sql.DriverManager;
|
import java.sql.PreparedStatement;
|
import java.sql.SQLException;
|
import java.util.Date;
|
|
import static org.springblade.common.config.FtpConfig.localPath;
|
import static org.springblade.common.config.FtpConfig.sqlConnect;
|
|
@Component
|
public class MysqlCenlint {
|
|
@Autowired
|
private IUserService userService;
|
|
//声明对象
|
private static MysqlCenlint mysqlCenlint;
|
|
/**
|
* 初始化
|
*/
|
@PostConstruct
|
public void init(){
|
mysqlCenlint = this;
|
mysqlCenlint.userService = this.userService;
|
}
|
|
/**
|
* sql 连接
|
* @param sql
|
*/
|
public static void sqlConnect(String sql,Integer type){
|
String driver = "com.mysql.cj.jdbc.Driver";
|
String url = sqlConnect; //换成要连接的数据库信息
|
String user = "root";
|
String password = "zhba0728";
|
Connection conn = null;
|
PreparedStatement ps = null;
|
try {
|
Class.forName ( driver );
|
conn = (Connection) DriverManager.getConnection ( url, user, password );
|
if (!conn.isClosed ()) {
|
System.out.println ( "数据库连接成功：" );
|
String sqls = sql;
|
ps = conn.prepareStatement ( sqls );
|
//判断是否为修改，删除
|
if (type==1){
|
//修改删除
|
ps.executeUpdate();
|
}else {
|
//新增
|
ps.execute();
|
}
|
}
|
} catch (ClassNotFoundException e) {
|
e.printStackTrace();
|
} catch (SQLException e) {
|
e.printStackTrace ();
|
}finally {
|
try {
|
ps.close();
|
conn.close();
|
} catch (SQLException throwables) {
|
throwables.printStackTrace();
|
}
|
}
|
}
|
|
|
/**
|
* 连接mysql数据库 新增
|
* @param sql
|
*/
|
public static void inster(String sql) {
|
sqlConnect(sql,2);
|
try {
|
Thread.sleep(300);
|
//对比 sql 字符串，如果是是往用户表里插入保安员数据，则调用公安审查接口
|
String insertUserSql = "insert into blade_user";
|
if (sql.contains(insertUserSql)){
|
//如果包含，调用公安接口进行保安员审查
|
getMxaminationSecurityInfo(sql);
|
}
|
} catch (InterruptedException e) {
|
e.printStackTrace();
|
}
|
}
|
|
/**
|
* 审查保安员
|
* @param sqls 新增的sql 语句
|
*/
|
private static void getMxaminationSecurityInfo(String sqls) {
|
//解析sql，读取数据，取出角色，如果是保安员，则审查，如果不是就不审查
|
//获取第一个 （ ) 的位置
|
int a = sqls.indexOf("(");
|
int b = sqls.indexOf(")");
|
//获取第二个 ( )的位置
|
a = sqls.indexOf("(",a+1);
|
b = sqls.indexOf(")",b+1);
|
//截取字符串
|
String subSql = sqls.substring(a, b);
|
String[] splitSql = subSql.split(",");
|
//读取角色
|
String roleId = splitSql[10];
|
//去除单引号
|
String subRoleId = roleId.substring(1, roleId.length() - 1);
|
//如果是保安员
|
if (subRoleId.equals("1412226235153731586")){
|
//读取身份证号
|
String cardid = splitSql[12];
|
//去除单引号
|
String subCardid = cardid.substring(1, cardid.length() - 1);
|
User user = new User();
|
String userId = splitSql[0].substring(2, splitSql[0].length() - 1);
|
user.setId(Long.parseLong(userId));
|
System.out.println("身份证："+subCardid);
|
//远程调用接口
|
String body = InvestigateUtil.httpGetOne(subCardid);
|
JSONObject jsonObject = new JSONObject(body);
|
Object data = jsonObject.get("data");
|
JSONObject jsonData = new JSONObject(data.toString());
|
JSONArray res = jsonData.getJSONArray("res");
|
//设置审核时间
|
user.setAuditTime(new Date());
|
if (res.length() == 0) {
|
//没有数据正常
|
user.setExaminationType("0");
|
} else {
|
int count = 0;
|
user.setExaminationType("1");
|
for (int i = 0; i < res.length(); i++) {
|
if (res.getJSONObject(i).get("zdrylbjh").toString() != null
|
&& res.getJSONObject(i).get("zdrylbjh").toString() != ""
|
&& res.getJSONObject(i).get("zdrylbjh").toString() != "null"
|
) {
|
user.setExaminationMx(res.getJSONObject(i).get("zdrylbjh").toString());
|
//更新用户数据
|
mysqlCenlint.userService.updateById(user);
|
break;
|
}
|
if (res.getJSONObject(i).get("zdryxlmc").toString() != null
|
&& res.getJSONObject(i).get("zdryxlmc").toString() != ""
|
&& res.getJSONObject(i).get("zdryxlmc").toString() != "null"
|
) {
|
user.setExaminationMx(res.getJSONObject(i).get("zdryxlmc").toString());
|
//更新用户数据
|
mysqlCenlint.userService.updateById(user);
|
break;
|
}
|
if (res.getJSONObject(i).get("ztrylx").toString() != null
|
&& res.getJSONObject(i).get("ztrylx").toString() != ""
|
&& res.getJSONObject(i).get("ztrylx").toString() != "null"
|
) {
|
user.setExaminationMx(res.getJSONObject(i).get("ztrylx").toString());
|
//更新用户数据
|
mysqlCenlint.userService.updateById(user);
|
break;
|
}
|
// if (res.getJSONObject(i).get("ajlbmc").toString() != null
|
// && res.getJSONObject(i).get("ajlbmc").toString() != "") {
|
// user.setExaminationMx(res.getJSONObject(i).get("ajlbmc").toString());
|
// //更新用户数据
|
// userService.updateById(user);
|
// break;
|
// }
|
count++;
|
//条件中的数据都为空，则审查为正常
|
if (count==res.length()){
|
//更新用户数据,设为正常
|
user.setExaminationType("0");
|
mysqlCenlint.userService.updateById(user);
|
}
|
}
|
}
|
//更新用户数据
|
mysqlCenlint.userService.updateById(user);
|
}
|
}
|
|
/**
|
* 连接mysql数据库 修改
|
* @param sql
|
*/
|
public static void update(String sql) {
|
sqlConnect(sql,1);
|
// try {
|
// int ColumnCount;
|
// //int RowCount;
|
// String driver = "com.mysql.cj.jdbc.Driver";
|
// String url = sqlConnect; //换成要连接的数据库信息
|
// String user = "root";
|
// String password = "zhba0728";
|
// Class.forName ( driver );
|
// Connection conn = (Connection) DriverManager.getConnection ( url, user, password );
|
// if (!conn.isClosed ()) {
|
// System.out.println ( "数据库连接成功：" );
|
// String sqls = sql; //sql
|
// PreparedStatement ps = conn.prepareStatement ( sqls );
|
// ps.executeUpdate();
|
// ps.close ();
|
// conn.close ();
|
// }
|
// } catch (ClassNotFoundException e) {
|
// e.printStackTrace ();
|
// } catch (SQLException e) {
|
// e.printStackTrace ();
|
// }
|
}
|
|
/**
|
* 连接mysql数据库 删除
|
* @param sql
|
*/
|
public static void delete(String sql) {
|
sqlConnect(sql,1);
|
// try {
|
// int ColumnCount;
|
// //int RowCount;
|
// String driver = "com.mysql.cj.jdbc.Driver";
|
// String url = sqlConnect; //换成要连接的数据库信息
|
// String user = "root";
|
// String password = "zhba0728";
|
// Class.forName ( driver );
|
// Connection conn = (Connection) DriverManager.getConnection ( url, user, password );
|
// if (!conn.isClosed ()) {
|
// System.out.println ( "数据库连接成功：" );
|
// String sqls = sql; //sql
|
// PreparedStatement ps = conn.prepareStatement ( sqls );
|
// ps.executeUpdate();
|
// ps.close ();
|
// conn.close ();
|
// }
|
// } catch (ClassNotFoundException e) {
|
// e.printStackTrace ();
|
// } catch (SQLException e) {
|
// e.printStackTrace ();
|
// }
|
}
|
|
/**
|
* 删除本地文件
|
*/
|
public static void deletes(String fileName){
|
File file = new File("D:\\"+fileName);
|
if (file.isFile() && file.exists()) {
|
file.delete();
|
}
|
}
|
/**
|
* 删除本地文件
|
*/
|
public static void deletess(String fileName){
|
File file = new File(localPath+fileName);
|
if (file.isFile() && file.exists()) {
|
file.delete();
|
}
|
}
|
}
|
