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()