def db_insert(request):
company = request.POST.get('company')
name = request.POST.get('name')
alias = request.POST.get('alias')
ip = request.POST.get('ip')
user = request.POST.get('user')
password = request.POST.get('password')
db = request.POST.get('db')
port = request.POST.get('port')
db_type = request.POST.get('db_type')
charset = request.POST.get('charset')
note = request.POST.get('note')
if db_type == 'mysql':
connection_string = f'mysql+mysqldb://{user}:{password}@{ip}:{port}/{db}?charset={charset}'
elif db_type == 'oracle':
connection_string = f'oracle://{user}:{password}@{ip}:{port}/?service_name={db}'
elif db_type == 'sqlserver':
connection_string = f'mssql+pymssql://{user}:{password}@{ip}:{port}/{db}?charset={charset}'
elif db_type == 'postgresql':
connection_string = f'postgresql://{user}:{password}@{ip}:{port}/{db}'
try:
conn = db_config.mysql_connect()
# with conn.cursor() as curs:
# sql = f"""insert into source_db_info(company,name,alias,connection_string,ip,user,passwd,db,port,db_type,note)
# values('{company}','{name}','{alias}','{connection_string}','{ip}','{user}','{password}','{db}',{port},'{db_type}','{note}')"""
# curs.execute(sql)
# conn.commit()
return JsonResponse({'data': '新增成功', 'code': 1000})
except Exception as e:
conn.rollback()
return HttpResponseBadRequest(content=e)
finally:
conn.close()
def list_subcompany(request):
company = request.GET.get('company')
conn = db_config.mysql_connect()
curs = conn.cursor()
try:
sql = f"""select rownum,
company,
item_name,
demand_name,
demand_created,
group_concat(quarter,status order by quarter asc separator'|')
from source_system_demand
where id in ( select max(id) from source_system_demand where company='{company}' group by company,item_name,quarter )
group by rownum,company"""
curs.execute(sql)
result = curs.fetchall()
result_list = []
for i in result:
result_list_tmp = [i[0], i[1], i[2], i[3], i[4]]
for t in i[5].split('|'):
result_list_tmp.append(t)
result_list.append(result_list_tmp)
return JsonResponse(result_list, safe=False)
except:
return HttpResponse('error', status=500)
finally:
curs.close()
conn.close()
def calc_result(self, version):
"""根据检核结果明细计算问题占比
1. 填充空值的问题占比
2. 计算正常的问题占比
:param version: 要进行计算的版本号
:return: 检核成功返回True，失败返回False
"""
company = self.company
try:
conn = db_config.mysql_connect()
curs = conn.cursor()
curs.execute('set autocommit=0')
# 计算问题占比
# 处理item_count和problem_count都是null或=0的行
sql = f"""update check_result_{company}
set problem_per=100
where (item_count is null or item_count=0)
and check_version={version}"""
curs.execute(sql)
# 计算正常的问题占比
sql = f"""update check_result_{company} set problem_per=problem_count/item_count*100\
where problem_per is null
and check_version={version}"""
curs.execute(sql)
conn.commit()
return True
except Exception as e:
conn.rollback()
return False
finally:
curs.close()
conn.close()
def query_check_progress(request):
"""
查询正在运行的检核任务执行进度
:param request:
:return:
"""
company = request.GET.get('company')
try:
conn = db_config.mysql_connect()
curs = conn.cursor()
sql = f"""select count(*) from check_result_{company} a,(select max(check_version) check_version from check_result_{company}) b
where a.check_sql is not null
and a.check_sql != ''
and a.check_version=b.check_version"""
curs.execute(sql)
result = curs.fetchone()
to_be_check_cnt = result[0]
sql = f"""select count(*) from check_result_{company} a,(select max(check_version) check_version from check_result_{company}) b
where a.check_sql is not null
and a.check_sql != ''
and a.check_version=b.check_version
and a.update_flag='Y'"""
curs.execute(sql)
result = curs.fetchone()
checked_cnt = result[0]
value = round(checked_cnt / to_be_check_cnt * 100, 2)
return JsonResponse({"data": value})
except Exception as e:
return JsonResponse({"msg": "查询失败", "reason": e})
finally:
curs.close()
conn.close()
def rule_status_modify(request):
"""修改检核规则状态，禁用/启用 规则的状态
"""
id = request.POST.get('id')
post_status = request.POST.get('status')
company = request.POST.get('company')
conn = db_config.mysql_connect()
curs = conn.cursor()
curs.execute('set autocommit=0')
# 修改状态
try:
if post_status == '已启用':
sql = f"update check_result_template set status='已停用' where id={id} and source_system='{company}'"
rr = curs.execute(sql)
conn.commit()
return JsonResponse({'msg': '修改成功', 'code': 1000})
else:
sql = f"update check_result_template set status='已启用' where id={id} and source_system='{company}'"
rr = curs.execute(sql)
conn.commit()
return JsonResponse({'msg': '修改成功', 'code': 1000})
except:
return HttpResponse('error', status=500)
finally:
curs.close()
conn.close()
def data_overview_company_trend(request):
"""
统计风险集市相关 各公司 检核数据量、问题数据量、问题数据占比
:param request:
:return:
"""
year = request.GET.get('year')
month = request.GET.get('month')
day = request.GET.get('day')
company = request.GET.get('company')
try:
conn = db_config.mysql_connect()
curs = conn.cursor()
sql = f"""select round(sum(problem_count)/sum(item_count)*100,2),check_version
from check_result_{company}
where risk_market_item='是'
and check_date < date_add('{year}-{month}-{day}',interval 1 day)
group by check_version
order by check_version asc"""
curs.execute(sql)
result = curs.fetchall()
result = [r[0] for r in result]
return JsonResponse(result, safe=False)
except Exception as e:
print(e)
return JsonResponse({'msg': str(e)})
finally:
curs.close()
conn.close()
def query_data_day(year, quarter, month):
try:
conn = db_config.mysql_connect()
curs = conn.cursor()
sql = f"""select distinct date_format(d,'%d') from (
select date_format(a.execute_date,'%Y%m%d') d,count(distinct company) from check_execute_log a,dim_date b
where DATE_FORMAT(execute_date,'%Y%m%d') = b.day_id
and a.status='success'
and b.year={year}
and b.quarter={quarter}
and b.month={month}
group by date_format(a.execute_date,'%Y%m%d')
having count(distinct company)>=7
) a
order by 1 desc"""
curs.execute(sql)
day = curs.fetchall()
day = [d[0] for d in day]
return day
except Exception as e:
print('获取天错误:', e)
return False
finally:
curs.close()
conn.close()
def rule_add(request):
"""
新增检核规则
"""
source_system = request.POST.get('source_system')
check_item = request.POST.get('check_item')
target_table = request.POST.get('target_table')
risk_market = request.POST.get('risk_market')
problem_type = request.POST.get('problem_type')
db = request.POST.get('db')
check_sql = request.POST.get('check_sql')
note = request.POST.get('note')
status = request.POST.get('status')
# 处理检核SQL中含有''的情况
check_sql = MySQLdb.escape_string(check_sql).decode('utf-8')
# print(check_sql)
try:
# 连接数据库
conn = db_config.mysql_connect()
curs = conn.cursor()
curs.execute('set autocommit=0')
sql = "select max(id)+1 from check_result_template where source_system in ('" + source_system + "')"
curs.execute(sql)
result = curs.fetchone()
new_id = str(result[0]) # 获取新增的id
sql = f"""insert into check_result_template(id,
source_system,
check_item,
target_table,
risk_market_item,
problem_type,
db,
check_sql,
note,
status)
values({new_id},
'{source_system}',
'{check_item}',
'{target_table}',
'{risk_market}',
'{problem_type}',
'{db}',
'{check_sql}',
'{note}',
'{status}')"""
# print(sql)
curs.execute(sql)
conn.commit()
return JsonResponse({'msg': '修改成功', 'code': 1000})
except Exception as e:
return HttpResponse(e, status=500)
finally:
curs.close()
conn.close()
def init_table(self):
"""
类实例化所需参数
:param company: 公司名
:param source_system: 源系统名称
:return:
初始化检核表
如果check_result_{0}表存在，则从check_result_template表中插入对应公司检核项和逻辑
如果check_result_{0}表不存在，则使用check_result_template表作为模板新建
"""
company = self.company
logging.info('*' * 50)
logging.info(f'开始初始化检核结果表...check_result_{company}')
conn = db_config.mysql_connect()
curs = conn.cursor()
curs.execute('set autocommit=0')
sql = f"select table_name from information_schema.tables where table_schema='data_quality' and table_name='check_result_{company}'"
table_count = curs.execute(sql)
try:
if table_count == 0: # 表不存在则新建
sql = f"""create table check_result_{company} as select * from check_result_template
where company='{company}' order by id,source_system"""
curs.execute(sql)
else: # 表存在则插入
# 获取检核版本号
curs.execute(
f"select count(*) from check_execute_log where company='{company}'"
)
version = curs.fetchone()[0] + 1
# 可能存在了初始化完检核表，但是检核失败导致事务回滚，检核表check_version={version}数据项为空的情况，因此需要处理这种情况
for sql in (
f"delete from check_result_{company} where check_version={version}",
f"insert into check_result_{company} select * from check_result_template where company='{company}' order by id,source_system",
f"update check_result_{company} set check_version={version} where check_version is null",
):
curs.execute(sql)
conn.commit()
logging.info('*' * 50, f"初始化 check_result_{company}表 ...完成",
'*' * 50)
return True
except Exception as e:
conn.rollback()
logging.error('!' * 50,
f'初始化 check_result_{company}表 ...失败,错误信息：{str(e)}',
'!' * 50)
return False
finally:
curs.close()
conn.close()
def rule_config(request):
"""
单条检核规则页面
:param request:
:return:
"""
username = request.session['username']
company = request.GET.get('company')
id = request.GET.get('id')
if id != 'null': # POST传过来的id非空，进行“检核规则编辑”
# 连接数据库
conn = db_config.mysql_connect()
curs = conn.cursor()
curs.execute('set autocommit=0')
sql = f"""select id,check_item,target_table,risk_market_item,problem_type,db,check_sql,note,status
from check_result_template
where source_system in ('{company}') and id={id}"""
curs.execute(sql)
result = curs.fetchall()
for i in result:
id = i[0]
check_item = i[1]
target_table = i[2]
risk_market_item = i[3]
problem_type = i[4]
db = i[5]
check_sql = i[6]
note = i[7]
status = i[8]
curs.close()
conn.close()
return render(
request, "check/rule_config.html", {
"username": username,
"id": id,
"source_system": company,
"check_item": check_item,
"target_table": target_table,
"risk_market_item": risk_market_item,
"problem_type": problem_type,
"db": db,
"check_sql": check_sql,
"note": note,
"status": status,
})
else:
# POST传过来的id为空，进行“检核规则新增”
return render(request, "check/rule_add.html", {
"username": username,
"source_system": company
})
def subcompany_problem_count(request):
"""
子公司仪表盘-问题数据项统计
:param request:
:return:
"""
year = request.GET.get('year')
quarter = request.GET.get('quarter')
month = request.GET.get('month')
day = request.GET.get('day')
company = request.GET.get('company')
try:
conn = db_config.mysql_connect()
curs = conn.cursor()
# 问题占比 | 问题数据总量 | 问题数据项
sql = f"""select sum(a.item_count),sum(a.problem_count),a.check_item from (
select a.check_item,a.item_count,a.problem_count
from check_result_{company} a,
(
select max(a.check_version) check_version
from check_result_{company} a,dim_date b where DATE_FORMAT(a.check_date,'%Y%m%d') = b.day_id
and b.year={year}
and b.quarter={quarter}
and b.month={month}
and b.day={day}
) b
where a.problem_count is not null
and a.problem_count !=0
and a.risk_market_item='是'
and a.check_version=b.check_version
) a
group by a.check_item
order by 2 desc"""
curs.execute(sql)
result = curs.fetchall()
result_list = [['问题占比', '问题数据总量', '问题数据项'], ]
for i in result:
problem_per = (int(i[1]) / int(i[0]))
problem_per = round(problem_per * 100, 2)
problem_total = int(i[1])
item = i[2]
result_list.append([problem_per, problem_total, item])
return JsonResponse(result_list, safe=False)
except Exception as e:
print(e)
return JsonResponse({'msg': str(e)})
finally:
curs.close()
conn.close()
def query_update_history(request):
std_name = request.GET.get('std_name')
if std_name is None:
return JsonResponse({'msg': '请求参数缺失', 'code': 3000})
conn = db_config.mysql_connect()
curs = conn.cursor()
sql = f"select username,update_time from data_standard_update_log where std_name='{std_name}' order by update_time desc limit 1"
if curs.execute(sql) == 1:
result = curs.fetchone()
return JsonResponse({'username': result[0], 'last_update_time': str(result[1])})
else:
return JsonResponse({'username': None, 'last_update_time': None})
def data_overview_total(request):
"""
统计风险集市相关 总数据量、总问题数据量、总问题占比
:param request:
:return:
"""
year = request.GET.get('year')
quarter = request.GET.get('quarter')
month = request.GET.get('month')
day = request.GET.get('day')
all_cnt = 0
problem_cnt = 0
try:
conn = db_config.mysql_connect()
curs = conn.cursor()
for company in ('xt', 'zc', 'db', 'jk', 'jj1', 'jj2', 'jz'):
sql = f"""select sum(a.item_count),sum(a.problem_count) from check_result_{company} a,
(
select max(a.check_version) check_version
from check_result_{company} a,dim_date b where DATE_FORMAT(a.check_date,'%Y%m%d') = b.day_id
and b.year={year}
and b.quarter={quarter}
and b.month={month}
and b.day={day}
) b
where a.check_version=b.check_version
and a.risk_market_item='是'"""
curs.execute(sql)
result = curs.fetchone()
if result[0] is None:
continue
else:
all_cnt = all_cnt + result[0]
problem_cnt = problem_cnt + result[1]
response = {
'all_cnt': all_cnt,
'problem_cnt': problem_cnt,
'problem_per': round(problem_cnt / all_cnt * 100, 2)
}
return JsonResponse(response)
except Exception as e:
print(e)
return JsonResponse({'msg': str(e)})
finally:
curs.close()
conn.close()
def report_detail(request):
"""
查询给定日期，各公司的检核明细结果，返回最新版本数据
"""
company = request.GET.get('company')
year = request.GET.get('year')
quarter = request.GET.get('quarter')
month = request.GET.get('month')
day = request.GET.get('day')
try:
conn = db_config.mysql_connect()
curs = conn.cursor()
sql = f"""select a.check_item,a.problem_type,a.problem_count,a.item_count,concat(a.problem_per,'%') problem_per
from check_result_{company} a,
(
select max(a.check_version) check_version
from check_result_{company} a,dim_date b where DATE_FORMAT(a.check_date,'%Y%m%d') = b.day_id
and b.year={year}
and b.quarter={quarter}
and b.month={month}
and b.day={day}
) b
where a.risk_market_item='是'
and a.check_version=b.check_version
and (a.problem_count<>0 or a.problem_count is null)
order by a.problem_type,a.problem_count desc"""
curs.execute(sql)
result = curs.fetchall()
result_list = []
for i in result:
result_dict = {
"check_item": i[0],
"problem_type": i[1],
"problem_count": i[2],
"item_count": i[3],
"problem_per": i[4],
}
result_list.append(result_dict)
return JsonResponse({'data': result_list})
except Exception as e:
print(e)
return JsonResponse({'msg': str(e)})
finally:
curs.close()
conn.close()
def show_crontab(request):
"""
自动检核配置页面
:param request:
:return:
"""
conn = db_config.mysql_connect()
with conn.cursor() as curs:
# 查询各个公司检核规则配置的数据库、上次检核任务的运行情况
sql = """select distinct b.name,
a.company,
a.db,
CAST(c.execute_date as char),
c.status
from check_result_template a,
source_db_info b,
(select db,company,execute_date,status from check_execute_log where id in
(
select id from (select max(id) id,company,db from check_execute_log where db is not null group by company,db) a
)
) c
where a.db=b.alias
and a.db=c.db
and a.company=c.company
order by 1,2,3"""
curs.execute(sql)
jobs = curs.fetchall()
# 根据数据源中的公司和数据库信息匹配crontab定时任务
cron = CronTab(user=True)
data = []
for i in jobs:
job = list(cron.find_comment(f'autocheck-{i[1]}-{i[2]}'))
t = list(i)
if len(job) > 0:
enable = job[0].is_enabled() # 获取crontab启用状态
job_time = job[0].description(use_24hour_time_format=True, locale_code='zh_CN') # 获取crontab的调度周期
t.extend([enable, job_time])
else:
t.append(None)
data.append(t)
return render(request, "check/crontab.html", {"jobs": data})
def avg_problem_percentage(request):
"""
各公司平均问题占比
:param request:
:return:
"""
# 接口返回值列表
data = []
data_quarter = ['quarter']
data_company = []
# 获取所有年所有季度
year = f.query_data_year()
quarter = []
for y in year:
q = f.query_data_quarter(y)
quarter.extend([(y, i) for i in q])
[data_quarter.append(str(y)+'Q'+str(i)) for i in q]
data.append(data_quarter)
# 查询各公司每季度数据
try:
conn = db_config.mysql_connect()
curs = conn.cursor()
for company in ('xt', 'zc', 'db', 'jk', 'jj1', 'jj2', 'jz'):
data_company = [company]
sql = f"""select round(sum(a.problem_count)/sum(a.item_count)*100,2)
from check_result_{company} a,dim_date b
where a.RISK_MARKET_ITEM='是'
and DATE_FORMAT(a.check_date,'%Y%m%d') = b.day_id
and (b.year,b.quarter) in {tuple(quarter)}
group by b.year,b.quarter"""
curs.execute(sql)
result = curs.fetchall()
[data_company.append(str(r[0])) for r in result]
data.append(data_company)
return JsonResponse(data, safe=False)
except Exception as e:
print(e)
return JsonResponse({'msg': str(e)})
finally:
curs.close()
conn.close()
def query_data_year():
try:
conn = db_config.mysql_connect()
curs = conn.cursor()
sql = """select date_format(execute_date,'%Y'),count(distinct company) as cnt from check_execute_log
where status='success'
group by date_format(execute_date,'%Y')
having count(distinct company)>=7
order by 1 desc"""
curs.execute(sql)
year = curs.fetchall()
year = [y[0] for y in year]
return year
except Exception as e:
print('获取年份错误:', e)
return False
finally:
curs.close()
conn.close()
def query_check_progressbar(company, quarter):
"""查询当前检核进度
"""
conn = db_config.mysql_connect()
curs = conn.cursor()
try:
sql = f"select count(*) from check_result_{company}_{quarter} where check_sql is not null and check_sql != '' "
curs.execute(sql)
to_be_check_cnt = curs.fetchone()[0]
sql = f"select count(*) from check_result_{company}_{quarter} where check_sql is not null and check_sql != '' and update_flag='Y'"
curs.execute(sql)
checked_cnt = curs.fetchone()[0]
return round(checked_cnt / to_be_check_cnt * 100, 2)
except Exception:
return 0
finally:
curs.close()
conn.close()
def query_data_quarter(year):
try:
conn = db_config.mysql_connect()
curs = conn.cursor()
sql = f"""select b.quarter,count(distinct company),b.year from check_execute_log a,dim_date b
where DATE_FORMAT(execute_date,'%Y%m%d') = b.day_id
and b.year={year}
group by b.year,b.quarter
having count(distinct company)>=7
order by 1 asc"""
curs.execute(sql)
quarter = curs.fetchall()
quarter = [q[0] for q in quarter]
return quarter
except Exception as e:
print('获取季度错误:', e)
return False
finally:
curs.close()
conn.close()
def rule(request):
"""
根据公司名查询所有检核规则详情
"""
company = request.GET.get('name')
filter = request.GET.get('risk_market_filter')
conn = db_config.mysql_connect()
curs = conn.cursor()
curs.execute('set autocommit=0')
try:
sql = f"""select id,check_item,target_table,risk_market_item,problem_type,db,check_sql,note,status,source_system
from check_result_template
where source_system in ('{company}')
and risk_market_item like '%{filter}%'
order by id"""
curs.execute(sql)
result = curs.fetchall()
# 构造json
result_list = []
for i in result:
result_dict = {
"id": i[0],
"check_item": i[1],
"target_table": i[2],
"risk_market_item": i[3],
"problem_type": i[4],
"db": i[5],
"check_sql": i[6],
"note": i[7],
"status": i[8],
"source_system": i[9]
}
result_list.append(result_dict)
json_data = {'data': result_list}
return JsonResponse(json_data)
except:
return HttpResponse('error', status=500)
finally:
curs.close()
conn.close()
def query_data_month(year, quarter):
try:
conn = db_config.mysql_connect()
curs = conn.cursor()
sql = f"""select distinct b.month,count(distinct company),b.year,b.day from check_execute_log a,dim_date b
where DATE_FORMAT(execute_date,'%Y%m%d') = b.day_id
and b.year={year}
and b.quarter={quarter}
group by b.year,b.month,b.day
having count(distinct company)>=7
order by 1 asc"""
curs.execute(sql)
month = curs.fetchall()
month = [m[0] for m in month]
return month
except Exception as e:
print('获取月份错误:', e)
return False
finally:
curs.close()
conn.close()
def query_index(request):
conn = db_config.mysql_connect()
curs = conn.cursor()
sql = "select idx_id, idx_pid,idx_name,is_open from data_standard_index"
curs.execute(sql)
result = curs.fetchall()
data = []
for i in result:
data.append({
'id': i[0],
'pId': i[1],
'name': i[2],
't': i[2],
'open': i[3]
})
curs.close()
conn.close()
return JsonResponse(data, safe=False)
def rule_update(request):
"""
执行修改检核规则
"""
id = request.POST.get('id')
source_system = request.POST.get('source_system')
check_item = request.POST.get('check_item')
target_table = request.POST.get('target_table')
risk_market = request.POST.get('risk_market')
problem_type = request.POST.get('problem_type')
db = request.POST.get('db')
check_sql = request.POST.get('check_sql')
note = request.POST.get('note')
status = request.POST.get('status')
# 把"转义为'，再把'转义为''
check_sql = MySQLdb.escape_string(check_sql).decode('utf-8')
# print(check_sql)
try:
conn = db_config.mysql_connect()
curs = conn.cursor()
curs.execute('set autocommit=0')
sql = f"""update check_result_template set check_item='{check_item}',
target_table='{target_table}',
risk_market_item='{risk_market}',
problem_type='{problem_type}',
db='{db}',
check_sql='{check_sql}',
note='{note}',
status='{status}'
where id={id} and source_system='{source_system}'"""
# print(sql)
curs.execute(sql)
conn.commit()
return JsonResponse({'msg': '修改成功', 'code': 1000})
except Exception as e:
return HttpResponse(e, status=500)
finally:
curs.close()
conn.close()
def total_trend(request):
"""
显示集团总问题占比走势
:param request:
:return:
"""
value = []
try:
conn = db_config.mysql_connect()
curs = conn.cursor()
sql = f"""select DATE_FORMAT(a.check_date,'%Y-%m-%d'),
round(sum(a.problem_count)/sum(a.item_count)*100,2),
count(distinct company) from
(
select 'xt' company,item_count,problem_count,check_date from check_result_xt where risk_market_item='是'
union
select 'zc' company,item_count,problem_count,check_date from check_result_zc where risk_market_item='是'
union
select 'db' company,item_count,problem_count,check_date from check_result_db where risk_market_item='是'
union
select 'jk' company,item_count,problem_count,check_date from check_result_jk where risk_market_item='是'
union
select 'jj1' company,item_count,problem_count,check_date from check_result_jj1 where risk_market_item='是'
union
select 'jj2' company,item_count,problem_count,check_date from check_result_jj2 where risk_market_item='是'
union
select 'jz' company,item_count,problem_count,check_date from check_result_jz where risk_market_item='是'
) a
group by DATE_FORMAT(a.check_date,'%Y-%m-%d')
having count(distinct company)=7
order by 1 asc"""
curs.execute(sql)
result = curs.fetchall()
return JsonResponse({'datatime': [r[0] for r in result], 'value': [r[1] for r in result]}, safe=False)
except Exception as e:
print(e)
return JsonResponse({'msg': str(e)})
finally:
curs.close()
conn.close()
def subcompany_data_percentage(request):
"""
各公司数据量占比
:param request:
:return:
"""
year = request.GET.get('year')
quarter = request.GET.get('quarter')
month = request.GET.get('month')
day = request.GET.get('day')
data = []
try:
conn = db_config.mysql_connect()
curs = conn.cursor()
for company in ('xt', 'zc', 'db', 'jk', 'jj1', 'jj2', 'jz'):
sql = f"""select sum(distinct item_count) from check_result_{company} a,
(
select max(a.check_version) check_version
from check_result_{company} a,dim_date b where DATE_FORMAT(a.check_date,'%Y%m%d') = b.day_id
and b.year={year}
and b.quarter={quarter}
and b.month={month}
and b.day={day}
) b
where a.check_version=b.check_version
and a.risk_market_item='是'"""
curs.execute(sql)
result = curs.fetchone()
if result[0] is None:
data.append({'name': company, 'value': 0})
else:
data.append({'name': company, 'value': float(str(result[0]))})
return JsonResponse(data, safe=False)
except Exception as e:
print(e)
return JsonResponse({'msg': str(e)})
finally:
curs.close()
conn.close()
def data_overview_company(request):
"""
统计风险集市相关 各公司 检核数据量、问题数据量、问题数据占比
:param request:
:return:
"""
year = request.GET.get('year')
quarter = request.GET.get('quarter')
month = request.GET.get('month')
day = request.GET.get('day')
data = []
try:
conn = db_config.mysql_connect()
curs = conn.cursor()
for company in ('xt', 'zc', 'db', 'jk', 'jj1', 'jj2', 'jz'):
sql = f"""select sum(a.item_count),sum(a.problem_count),round(sum(a.problem_count)/sum(a.item_count)*100,2)
from check_result_{company} a,
(
select max(a.check_version) check_version
from check_result_{company} a,dim_date b where DATE_FORMAT(a.check_date,'%Y%m%d') = b.day_id
and b.year={year}
and b.quarter={quarter}
and b.month={month}
and b.day={day}
) b
where a.check_version=b.check_version
and a.risk_market_item='是'"""
curs.execute (sql)
result = curs.fetchone()
data.append([company, result[0], result[1], result[2]])
return JsonResponse(data, safe=False)
except Exception as e:
print(e)
return JsonResponse({'msg': str(e)})
finally:
curs.close()
conn.close()
def query_mapping(request):
table_name = request.GET.get('table_name')
sql = f"""select distinct subject_area,
mapping_name,
source,
target,
case when locate('_ts_', mapping_name)>0 then 1
when locate('_ti_', mapping_name)>0 then 2
when locate('_ods_', mapping_name)>0 then 3
else 99
end level
from datacenter_mapping
where (
lower(source) like '%{table_name.lower()}%'
or lower(target) like '%{table_name.lower()}%'
or lower(mapping_name) like '%{table_name.lower()}%'
)
and source<>target
order by level asc,1,2,3
"""
try:
conn = db_config.mysql_connect()
with conn.cursor() as curs:
curs.execute(sql)
r = curs.fetchall()
return JsonResponse({
'subject_area': [i[0] for i in r],
'mapping_name': [i[1] for i in r],
'source': [i[2] for i in r],
'target': [i[3] for i in r],
'level': [i[4] for i in r]
})
except Exception as e:
return HttpResponseBadRequest(e)
finally:
conn.close()
def run_check(self, db):
"""
执行检核
类实例化所需参数
:param company: 公司简称
:param db: 检核的数据库
:return:
"""
company = self.company
logging.info('-' * 50)
logging.info("正在检核" + company + "数据...")
try:
conn = db_config.mysql_connect()
curs = conn.cursor()
curs.execute('set autocommit=0')
# 从规则库表中取出检核项和检核sql，只运行“已启用”状态的SQL
sql = f"""select id,check_sql from check_result_template
where company='{company}'
and check_sql is not null
and check_sql != ''
and db='{db}'
and status='已启用'
order by id"""
curs.execute(sql)
check_list = curs.fetchall()
# 连接源系统数据库
# loc = locals()
# if company in ('xt', 'zc', 'db', 'jk', 'jj1', 'jj2', 'jz'):
# exec(f'conn_source = {company}_{db}()')
# else:
conn_source = mysql_db()
# 获取检核版本号
curs.execute(f"select count(*) from check_execute_log where company='{company}'")
version = curs.fetchone()[0] + 1
# with loc['conn_source'].cursor() as curs_source:
with conn_source.cursor() as curs_source:
# 执行检核
for i in check_list:
id = i[0]
check_sql = i[1]
logging.info(f'{company}, db={db}, id={i[0]} >>>开始检核')
curs_source.execute(check_sql)
check_result = curs_source.fetchall() # 检核结果
for t in check_result:
item_count = t[0]
problem_count = t[1]
archive_sql = f"""update check_result_{company}
set item_count={item_count},
problem_count={problem_count},
update_flag='Y',
check_date=current_timestamp
where id={id}
and check_version={version}"""
curs.execute(archive_sql)
conn.commit()
logging.info(f'{company}, db={db}, id={i[0]} <<<完成')
# loc['conn_source'].close()
conn_source.close()
# 根据检核结果明细计算问题占比
self.calc_result(version)
logging.info("-" * 25, f'{company}, db={db} 检核完成', "-" * 25)
return True
except Exception as e:
conn.rollback()
logging.error("!" * 25, f'{company}, db={db}, id={id} 检核出错,错误信息：{str(e)}', "!" * 25)
return False
finally:
curs.close()
conn.close()
def rule_execute(request):
"""执行检核
"""
company = request.POST.get('company')
username = request.POST.get('username')
quarter = request.POST.get('quarter')
source_system = company
if company == 'xt':
check = Check()
if check.init_table(company, source_system, quarter):
# 初始化3个线程
thread1 = MyThread(func=check.run_check,
args=(company, source_system, quarter,
'oracle'))
thread2 = MyThread(func=check.run_check,
args=(company, source_system, quarter,
'sqlserver'))
thread3 = MyThread(func=check.xt_spec, args=(quarter, ))
thread4 = MyThread(func=check.run_check,
args=(company, source_system, quarter, 'mysql'))
# 开启3个线程
thread1.start()
thread2.start()
thread3.start()
thread4.start()
# 等待运行结束
thread1.join()
thread2.join()
thread3.join()
thread4.join()
if thread1.get_result() is True:
if thread2.get_result() is True:
if thread3.get_result() is True:
if thread4.get_result() is True:
run = True
else:
return JsonResponse({
"status":
"检核过程发生错误：" + str(thread4.get_result())
})
else:
return JsonResponse({
"status":
"检核过程发生错误：" + str(thread3.get_result())
})
else:
return JsonResponse(
{"status": "检核过程发生错误：" + str(thread2.get_result())})
else:
return JsonResponse(
{"status": "检核过程发生错误：" + str(thread1.get_result())})
else:
return JsonResponse({"status": "初始化检核表发生错误"})
elif company == 'zc':
source_system = '资产'
check = Check()
if check.init_table(company, source_system, quarter):
run = check.run_check(company, source_system, quarter, None)
if run is not True:
return JsonResponse({"status": "检核过程发生错误：" + str(run)})
else:
return JsonResponse({"status": "初始化检核表发生错误"})
elif company == 'db':
source_system = '担保'
check = Check()
if check.init_table(company, source_system, quarter):
run = check.run_check(company, source_system, quarter, None)
if run is not True:
return JsonResponse({"status": "检核过程发生错误：" + str(run)})
else:
return JsonResponse({"status": "初始化检核表发生错误"})
elif company == 'jk':
source_system = '金科'
check = Check()
if check.init_table(company, source_system, quarter):
run = check.run_check(company, source_system, quarter, None)
if run is not True:
return JsonResponse({"status": "检核过程发生错误：" + str(run)})
else:
return JsonResponse({"status": "初始化检核表发生错误"})
elif company == 'jj1':
source_system = '基金1'
check = Check()
if check.init_table(company, source_system, quarter):
run = check.run_check(company, source_system, quarter, None)
if run is not True:
return JsonResponse({"status": "检核过程发生错误：" + str(run)})
else:
return JsonResponse({"status": "初始化检核表发生错误"})
elif company == 'jj2':
source_system = '基金2'
check = Check()
if check.init_table(company, source_system, quarter):
run = check.run_check(company, source_system, quarter, None)
if run is not True:
return JsonResponse({"status": "检核过程发生错误：" + str(run)})
else:
return JsonResponse({"status": "初始化检核表发生错误"})
elif company == 'jz':
source_system = '金租'
check = Check()
if check.init_table(company, source_system, quarter):
run = check.run_check(company, source_system, quarter, None)
if run is not True:
return JsonResponse({"status": "检核过程发生错误：" + str(run)})
else:
return JsonResponse({"status": "初始化检核表发生错误"})
if run is True:
conn = db_config.mysql_connect()
curs = conn.cursor()
curs.execute('set autocommit=0')
sql = "insert into check_execute_log values(null,'{0}','{1}',now(),'{2}')".format(
quarter, company, username)
print(sql)
curs.execute(sql)
conn.commit()
curs.close()
conn.close()
return JsonResponse({
"status": "success",
"msg": source_system + "公司检核成功！"
})
def query_check_progress(request):
"""
查询正在运行的检核任务执行进度
:param request:
:return:
"""
company = request.GET.get('company')
db = request.GET.get('db')
data = {}
try:
conn = db_config.mysql_connect()
for company in ('xt', 'zc', 'db', 'jk', 'jj1', 'jj2', 'jz'):
data[company] = {}
with conn.cursor() as curs:
# 已检核指标总数
sql = f"""select a.db,count(*)
from check_result_{company} a,
(
select max(check_version) check_version,db from check_result_{company}
where db in (select distinct alias from source_db_info where company='{company}')
group by db
) b
where a.check_sql is not null
and a.check_sql != ''
and a.check_version=b.check_version
and a.db=b.db
and a.update_flag='Y'
group by a.db"""
curs.execute(sql)
result = curs.fetchall()
for i in result:
data[company][i[0]] = i[1]
# 待检核指标总数
sql = f"""select a.db,count(*)
from check_result_{company} a,
(
select max(check_version) check_version,db from check_result_{company}
where db in (select distinct alias from source_db_info where company='{company}')
group by db
) b
where a.check_sql is not null
and a.check_sql != ''
and a.check_version=b.check_version
and a.db=b.db
group by a.db"""
curs.execute(sql)
result = curs.fetchall()
for i in result:
if i[1] == 0:
data[company][i[0]] = 0
else:
data[company][i[0]] = round(
data[company][i[0]] / i[1] * 100, 2)
return JsonResponse(data)
except Exception as e:
return HttpResponseBadRequest(e)
finally:
conn.close()
