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