def get_search_result_by_sql(self, user_account, key):
connect_sql = ConnectSql()
connect = connect_sql.connect_risk_sql()
cursor = connect.cursor()
sql_01 = "SELECT orgId FROM `system_user_info` WHERE loginUser = '******';" % user_account
cursor.execute(sql_01)
orgId = cursor.fetchall()[0][0]
sql_02 = "SELECT orgName,orgSupId,orgTel FROM system_org_info WHERE (id = '" + str(orgId) + "' OR orgSupId = '" + str(orgId) + "') AND " \
"orgName LIKE '%" + key + "%';"
cursor.execute(sql_02)
data_02 = cursor.fetchall()[0]
print(data_02)
orgName = data_02[0]
orgSupId = data_02[1]
orgTel = data_02[2]
sql_03 = "SELECT orgName FROM `system_org_info` WHERE id = '%s';" % orgSupId
cursor.execute(sql_03)
orgSupName = cursor.fetchall()[0][0]
org_info = []
org_info.append(orgName)
org_info.append(orgSupName)
org_info.append(orgTel)
print(org_info)
cursor.close()
connect.close()
return org_info
def get_user_sup_org_by_sql(self, user_account):
connect_sql = ConnectSql()
connect = connect_sql.connect_risk_sql()
cursor = connect.cursor()
sql = "SELECT o.orgName FROM `system_user_info` u INNER JOIN `system_org_info` o on u.orgId = o.id WHERE u.loginUser = '******';" % user_account
cursor.execute(sql)
data = cursor.fetchall()[0][0]
print(data)
cursor.close()
connect.close()
return data
def get_add_role_by_sql(self, user_account):
connect_sql = ConnectSql()
connect = connect_sql.connect_risk_sql()
cursor = connect.cursor()
sql = "SELECT r.roleName FROM `system_user_info` u INNER JOIN `system_role_info` r ON u.id = r.createUserId " \
"WHERE u.loginUser = '******' ORDER BY r.createTime DESC;" % user_account
cursor.execute(sql)
data = cursor.fetchall()[0][0]
print(data)
cursor.close()
connect.close()
return data
def get_current_org_info_by_sql(self, user_account):
connect_sql = ConnectSql()
connect = connect_sql.connect_risk_sql()
cursor = connect.cursor()
sql = "SELECT o.orgName,o.orgTel FROM `system_user_info` u INNER JOIN system_org_info o ON u.orgId = o.id WHERE u.loginUser = '******';" % user_account
cursor.execute(sql)
data = cursor.fetchall()[0]
current_org_info = []
current_org_info.append(data[0])
current_org_info.append(data[1])
print(current_org_info)
cursor.close()
connect.close()
return current_org_info
def get_belong_to_superior_org_by_sql(self, user_account):
connect_sql = ConnectSql()
connect = connect_sql.connect_risk_sql()
cursor = connect.cursor()
sql_01 = "SELECT o.orgSupId FROM `system_user_info` u INNER JOIN `system_org_info` o on u.orgId = o.id WHERE u.loginUser = '******';" % user_account
cursor.execute(sql_01)
orgSupId = cursor.fetchall()[0][0]
sql_02 = "SELECT orgName FROM `system_org_info` WHERE id = '%s';" % orgSupId
cursor.execute(sql_02)
sup_org_name = cursor.fetchall()[0][0]
print(sup_org_name)
cursor.close()
connect.close()
return sup_org_name
def get_add_org_by_sql(self, user_account):
connect_sql = ConnectSql()
connect = connect_sql.connect_risk_sql()
cursor = connect.cursor()
sql = "SELECT o.orgName FROM `system_user_info` u INNER JOIN `system_org_info` o ON u.id = o.createUserId WHERE " \
"u.loginUser = '******' ORDER BY o.createTime DESC;" % user_account
cursor.execute(sql)
data = cursor.fetchall()[0]
print(data)
data_01 = data[0]
print(data_01)
cursor.close()
connect.close()
return data_01
def get_search_result_rolename_by_sql(self, user_account, key):
connect_sql = ConnectSql()
connect = connect_sql.connect_risk_sql()
cursor = connect.cursor()
sql_01 = "SELECT r.roleName FROM `system_user_info` u INNER JOIN `system_role_info` r ON u.id = r.createUserId " \
"WHERE u.loginUser = '******' AND r.roleName LIKE '%" + key + "%';"
cursor.execute(sql_01)
data = cursor.fetchall()
roleName = []
for i in range(len(data)):
roleName.append(data[i][0])
print(roleName)
cursor.close()
connect.close()
return roleName
def get_account_info_by_sql(self, user_account):
# 获取当前登录账号的客户信息
connect_sql = ConnectSql()
connect = connect_sql.connect_risk_sql()
cursor = connect.cursor()
sql = "SELECT loginUser,userName,userTel FROM `system_user_info` WHERE loginUser = '******';" % user_account
cursor.execute(sql)
data = cursor.fetchall()
print(data)
data_list = []
data_list.append(data[0][0])
data_list.append(data[0][1])
data_list.append(data[0][2])
cursor.close()
connect.close()
return data_list
def get_org_log_search_result_by_sql(self,user_account,start_time,end_time):
connect_sql = ConnectSql()
connect = connect_sql.connect_risk_sql()
cursor = connect.cursor()
sql_01 = "SELECT id FROM `system_user_info` WHERE loginUser = '******';" % user_account
cursor.execute(sql_01)
userid = cursor.fetchall()[0][0]
print(userid)
sql_02 = "SELECT * FROM `org_operation_log` WHERE createUserId = '" + str(id) + "' AND " \
"createTime >= '" + start_time + "' AND createTime <= '" + end_time + "';"
cursor.execute(sql_02)
data = cursor.fetchall()
num = len(data)
cursor.close()
connect.close()
return num
def get_search_result_by_sql(self, user_account, key):
connect_sql = ConnectSql()
connect = connect_sql.connect_risk_sql()
cursor = connect.cursor()
sql_01 = "SELECT r.roleName,r.roleRemark FROM `system_user_info` u INNER JOIN `system_role_info` r ON " \
"u.id = r.createUserId WHERE u.loginUser = '******' AND r.roleName LIKE '%" + key + "%';"
cursor.execute(sql_01)
data = cursor.fetchall()[0]
print(data)
roleName = data[0]
roleRemark = data[1]
role_info = []
role_info.append(roleName)
role_info.append(roleRemark)
print(role_info)
cursor.close()
connect.close()
return role_info
def get_feedback_info_by_sql(self, user_account):
# 获取当前登录账号提交的反馈信息
connect_sql = ConnectSql()
connect = connect_sql.connect_risk_sql()
cursor = connect.cursor()
sql = "SELECT loginUser,type,content,linkman,phone FROM system_user_info u INNER JOIN user_feedback f on" \
" u.id = f.createUserId WHERE u.loginUser = '******' ORDER BY f.createTime DESC;" % user_account
cursor.execute(sql)
data = cursor.fetchall()
print(data)
data_list = []
data_list.append(data[0][0])
data_list.append(data[0][1])
data_list.append(data[0][2])
data_list.append(data[0][3])
data_list.append(data[0][4])
cursor.close()
connect.close()
return data_list
def get_search_result_orgname_by_sql(self, user_account, key):
connect_sql = ConnectSql()
connect = connect_sql.connect_risk_sql()
cursor = connect.cursor()
sql_01 = "SELECT orgId FROM `system_user_info` WHERE loginUser = '******';" % user_account
cursor.execute(sql_01)
orgId = cursor.fetchall()[0][0]
sql_02 = "SELECT orgName FROM system_org_info WHERE (id = '" + str(
orgId) + "' OR orgSupId = '" + str(
orgId) + "') AND orgName LIKE '%" + key + "%';"
cursor.execute(sql_02)
data_02 = cursor.fetchall()
orgName = []
for i in range(len(data_02)):
orgName.append(data_02[i][0])
print(orgName)
cursor.close()
connect.close()
return orgName
def get_search_result_by_sql(self, user_account, key):
connect_sql = ConnectSql()
connect = connect_sql.connect_risk_sql()
cursor = connect.cursor()
sql_01 = "SELECT id FROM `system_user_info` WHERE loginUser = '******';" % user_account
cursor.execute(sql_01)
id = cursor.fetchall()[0][0]
sql_02 = "SELECT userName,userTel FROM `system_user_info` WHERE userState = '1' and createUserId = '" + str(id) +"' AND " \
"(loginUser LIKE '%" + key + "%' OR userName LIKE '%" + key + "%' OR userTel LIKE '%" + key + "%') " \
"ORDER BY createTime DESC;"
cursor.execute(sql_02)
data = cursor.fetchall()[0]
user_info = []
user_info.append(data[0])
user_info.append(data[1])
print(user_info)
cursor.close()
connect.close()
return user_info
def get_add_user_by_sql(self, user_account):
connect_sql = ConnectSql()
connect = connect_sql.connect_risk_sql()
cursor = connect.cursor()
sql_01 = "SELECT id FROM `system_user_info` WHERE loginUser = '******';" % user_account
cursor.execute(sql_01)
id = cursor.fetchall()[0][0]
sql_02 = "SELECT loginUser FROM `system_user_info` WHERE userState = '1' and createUserId = '%s' ORDER BY " \
"createTime DESC;" % id
cursor.execute(sql_02)
data = cursor.fetchall()
'''add_user_account = []
for i in range(len(data)):
add_user_account.append(data[i][0])'''
add_user_account = data[0][0]
print(add_user_account)
cursor.close()
connect.close()
return add_user_account
def get_search_result_username_by_sql(self, user_account, key):
connect_sql = ConnectSql()
connect = connect_sql.connect_risk_sql()
cursor = connect.cursor()
sql_01 = "SELECT id,userName FROM `system_user_info` WHERE loginUser = '******';" % user_account
cursor.execute(sql_01)
data_01 = cursor.fetchall()
print(data_01)
id = data_01[0][0]
userName = data_01[0][1]
sql_02 = "SELECT userName FROM `system_user_info` WHERE userState = '1' and createUserId = '" + str(
id) + "' AND " \
"(loginUser LIKE '%" + key + "%' OR userName LIKE '%" + key + "%' OR userTel LIKE '%" + key + "%') " \
"ORDER BY createTime DESC;"
cursor.execute(sql_02)
data = cursor.fetchall()
user_name = [userName]
for i in range(len(data)):
user_name.append(data[i][0])
print(user_name)
cursor.close()
connect.close()
return user_name
def get_loan_search_result_by_sql(self, user_account):
connect_sql = ConnectSql()
connect = connect_sql.connect_risk_sql()
cursor = connect.cursor()
sql_01 = "SELECT * FROM `system_user_info` WHERE loginUser = '******';" % user_account
cursor.execute(sql_01)
userid = cursor.fetchall()[0][0]
print(userid)
sql_02 = "SELECT imeis,carPlateNumber,carFrameNumber,carEngineNumber,homeAddress,workAddress FROM " \
"`customer_car` WHERE carStatus = 'OVERDUE' AND createUserId = '" + str(userid) + "';"
cursor.execute(sql_02)
data = cursor.fetchall()
num = len(data)
cursor.close()
connect.close()
if num == 0:
print("搜索结果为空")
result_info = []
return result_info
else:
result_info = []
for i in range(num):
imeis = data[i][0]
carPlateNumber = data[i][1]
carFrameNumber = data[i][2]
carEngineNumber = data[i][3]
homeAddress = data[i][4]
workAddress = data[i][5]
result_info.append(imeis)
result_info.append(carPlateNumber)
result_info.append(carFrameNumber)
result_info.append(carEngineNumber)
result_info.append(homeAddress)
result_info.append(workAddress)
print(result_info)
return result_info
