def get_loanafterdata():
init_app()
today = datetime.date(datetime.today())
origin_day = today - dt.timedelta(days=59)
today_s = "'" + str(today) + "'"
origin_day_s = "'" + str(origin_day) + "'"
sql = '''
select xx.*
from
(
select date_format(applytime,'%Y-%m-%d') days,
count(distinct case when repaymode=0 and b.hasrepayamt>0 then a.applyinfoid end) deal_num,
count(distinct case when repaymode=0 and b.hasrepayamt>0
and (a.applystatus='O' or hasrepayamt-payedamt<0 ) then a.applyinfoid end) repaied_num,
sum(case when repaymode=0 and b.hasrepayamt>0 then hasrepayamt end) deal_amt,
sum(case when repaymode=0 and b.hasrepayamt>0
and (a.applystatus='O' or hasrepayamt-payedamt<0 ) then hasrepayamt end) repaied_amt,
count(distinct case when repaymode=0 and hasrepayamt>0
and payedamt<hasrepayamt and applystatus<>'O' then a.applyinfoid end ) nopay_num,
sum(case when repaymode=0 and hasrepayamt>0 and payedamt<hasrepayamt
and applystatus<>'O' then hasrepayamt-payedamt end) nopay_amt,
count(distinct case when repaymode=0 and b.status in ('L','LS') then a.applyinfoid end) turnloan_num,
count(distinct case when repaymode=0 and b.status in ('LS') then a.applyinfoid end) loanback_num,
sum(case when repaymode=0 and b.status in ('L','LS') then hasrepayamt-payedamt end) turnloan_amt,
sum(case when repaymode=0 and b.status in ('LS') then hasrepayamt-payedamt end) loanback_amt,
count(distinct case when repaymode=0 and b.status in ('L') then a.applyinfoid end) nowloan_num,
sum(case when repaymode=0 and b.status in ('L') then hasrepayamt-payedamt end) nowloan_amt
from ac_bts_db.ApplyInfo a
left join ac_bts_db.InsteadRepayTxnCtrl b
on a.applyinfoid=b.applyinfoid
where applytime<''' + today_s + ''' and applytime>=''' + origin_day_s + '''
group by date_format(applytime,'%Y-%m-%d')
) xx
'''
afterloan_row = sql_util.select_rows_by_sql(
sql_text=sql,
sql_paras={},
ns_server_id='/db/mysql/ac_bts_db',
max_size=-1)
afterloan_list = []
for row in afterloan_row:
afterloan_list.append(list(row))
afterloan_df = pd.DataFrame(afterloan_list,
columns=[
'日期', '应还笔数', '已还笔数', '应还金额', '已还金额',
'未还笔数', '未还金额', '转贷笔数', '转贷催回笔数', '转贷金额',
'转贷催回金额', '当前逾期笔数', '当前逾期金额'
])
afterloan_df = afterloan_df.sort_values(by='日期', ascending=0)
afterloan_df = afterloan_df.fillna(0)
afterloan_df = afterloan_df[afterloan_df['日期'] >= '2018-01-15']
return afterloan_df
def get_cif_loantime():
init_app()
logger = logging.getLogger(__name__)
starttime = time.time()
logger.info('to get cif_loantime begin')
sql = '''
select partyid,max(loantime) from dev_db.f_loanagreement
where loanstatus in ('D','O','R','E')
group by partyid
'''
sql_row = sql_util.select_rows_by_sql(sql_text=sql,
sql_paras={},
ns_server_id='/db/oracle/dev_dw_db')
loantime_list = []
for row in sql_row:
loantime_list.append(list(row))
loantime_df = pd.DataFrame(loantime_list, columns=['partyid', 'loantime'])
endtime = time.time()
logger.info('end of query data fromTime=[%s], toTime=[%s].' %
(starttime, endtime))
return loantime_df
def get_checkdetail():
init_app()
today = datetime.date(datetime.today())
org_day = today - dt.timedelta(days=1)
today_s = "'" + str(today) + "'"
org_day_s = "'" + str(org_day) + "'"
sql = ''' select a.applytime,a.applyinfoid,a.partyid,
case when a.applystatus='T' then '申请提交成功'
when a.applystatus='M' then '人工审核状态'
when a.applystatus='S' then '申请审核通过'
when a.applystatus='R' then '审核拒绝(最终状态)'
when a.applystatus='C' then '申请被撤销(最终状态)'
when a.applystatus='O' then '代还交易完成(最终状态)' end applystatus,
json_extract(a.applydata,'$.issuerName'),a.applyamt,
case when c.scheduletype='FT' then '手续费交易'
when c.scheduletype='RT' then '还款交易'
when c.scheduletype='PT' then '扣款交易' end trade_status,
case when c.status='W' then '等待执行'
when c.status='S' then '执行成功'
when c.status='F' then '执行失败' end repaystatus,
c.amt,
case when b.completetime is null then '未完成'
else TIMESTAMPDIFF(MINUTE,a.applytime,b.completetime) end alltime
from ac_bts_db.ApplyInfo a
left join ac_bts_db.InsteadRepayTxnCtrl b
on a.applyinfoid=b.applyinfoid
left join ac_bts_db.InsteadRepaySchedule c
on b.insteadrepaytxnctrlid=c.insteadrepaytxnctrlid
left join
(
select insteadrepaytxnctrlid,exestarttime
from ac_bts_db.InsteadRepaySchedule
where serialno=1
) f
on c.insteadrepaytxnctrlid=f.insteadrepaytxnctrlid
where a.applystatus not in ('R','C') and a.repaymode=0
and a.applytime>=''' + org_day_s + ''' and a.applytime<''' + today_s + '''
'''
sql_row = sql_util.select_rows_by_sql(sql_text=sql,
sql_paras={},
ns_server_id='/db/mysql/ac_bts_db',
max_size=-1)
data_list = []
for row in sql_row:
data_list.append(list(row))
repay_df = pd.DataFrame(data_list,
columns=[
'申请时间', '申请id', 'partyid', '状态', '银行卡', '申请额度',
'操作类型', '操作状态', '操作金额', '代扣用时(分)'
])
repay_df = repay_df.sort_values(by='申请时间', ascending=[0])
return repay_df
def get_mobile_phone(partyId):
init_app()
db = DatabaseOperator('/python/db/ac_cif_db')
sql = '''
select ao.corporateRepresentUserName phoneNumber from ac_cif_db.OrgParty ao
where ao.partyId = :partyId
'''
#db = sql_util.select_rows_by_sql(sql_text=sql,sql_paras={},ns_server_id='/python/db/ac_cif_db', max_size=-1)
param = dict()
param['partyId'] = partyId
row_list = db.query_record(sql, params=param)
phone_num = None
for row in row_list:
phone_num = row['phoneNumber']
if phone_num is None:
return 0
service_group = 'ac-ums.admin-srv'
service_id = 'me.andpay.ac.ums.api.UserManagementService'
user = TiLnkClient.call_lnk_srv(service_group,
service_id,
'getUserByUserName',
phone_num,
ns_server_id=None)
return user['userName']
def get_cdss_txntime():
init_app()
logger = logging.getLogger(__name__)
starttime = time.time()
logger.info('to get cdss_txntime begin')
sql = ''' select distinct txnpartyid,min(txntime),max(txntime)
from dev_dw.f_txnlist
where txnflag='S' and salesamt>0
group by txnpartyid
'''
sql_row = sql_util.select_rows_by_sql(sql_text=sql,
sql_paras={},
ns_server_id='/db/oracle/dev_dw_db')
time_list = []
for row in sql_row:
logger.info('loop ing')
time_list.append(list(row))
logger.info('loop end')
txntime_df = pd.DataFrame(time_list,
columns=['partyid', 'firsttime', 'lasttime'])
endtime = time.time()
logger.info('end of query data fromTime=[%s], toTime=[%s].' %
(starttime, endtime))
return txntime_df
def get_cif_M2():
init_app()
logger = logging.getLogger(__name__)
starttime = time.time()
sql = ''' select distinct partyid,
case when y.pid is not null then 'M2' else 'NM' end categroy
from dev_dw.f_loanagreement x
left join
(
select distinct a.partyid pid from dev_dw.f_loanagreement a
left join dev_dw.f_loanrepayschedule b
on a.id=b.idloanagreement
where b.repaytime-b.duedate>33 or (b.repaytime is null and sysdate-b.duedate>33)
) y
on x.partyid=y.pid
where loantime<to_date('2017-09-01','yyyy-mm-dd') '''
sql_row = sql_util.select_rows_by_sql(sql_text=sql,
sql_paras={},
ns_server_id='/db/oracle/dev_dw_db')
partyid_list = []
for row in sql_row:
partyid_list.append(list(row))
endtime = time.time()
logger.info('end of query data fromTime=[%s], toTime=[%s].' %
(starttime, endtime))
partyid_df = pd.DataFrame(partyid_list, columns=['partyid', 'status'])
return partyid_df
def nodeal_user():
init_app()
today = datetime.date(datetime.today())
today_s = "'" + str(today) + "'"
week_day = get_week_day(today)
week_list = ['星期二', '星期三', '星期四', '星期五']
if week_day in week_list:
time = today - dt.timedelta(days=1)
elif week_day == '星期一':
time = today - dt.timedelta(days=3)
else:
time = today
time_s = "'" + str(time) + "'"
sql = ''' select * from
(
select pid,ids,repaymode,
case when applystatus='O' and cancelflag=TRUE then '用户终止结清'
when applystatus='O' and fallback=1 then '扣款失败结清'
when applystatus='O' then '正常结清'
when applystatus='C' and cancelflag=TRUE then '用户终止撤销'
when applystatus='C' then '扣款失败撤销'
else '未结清' end deal_status
from
(
select a.applyinfoid ids,a.partyid pid,a.applystatus,a.repaymode,
json_extract(a.applydata,'$.issuerName') bank,
json_extract(txndata,'$.customLine') ctline,
json_extract(txndata,'$.userCancelFlag') cancelflag,
b.fallback,b.status,b.repayamt,b.payedamt,b.hasrepayamt
from ac_bts_db.ApplyInfo a
left join ac_bts_db.InsteadRepayTxnCtrl b
on a.applyinfoid=b.applyinfoid
where applytime>=''' + time_s + ''' and applytime<''' + today_s + '''
) x
) y
where deal_status in ('用户终止结清','用户终止撤销','扣款失败撤销')
'''
sql_row = sql_util.select_rows_by_sql(sql_text=sql,
sql_paras={},
ns_server_id='/db/mysql/ac_bts_db',
max_size=-1)
partyid_list = []
for row in sql_row:
partyid_list.append(list(row))
partyid_df = pd.DataFrame(
partyid_list, columns=['partyid', 'applyid', 'repaymode', 'status'])
return partyid_df
def get_cif_partyadditioninfo():
init_app()
mongodb_path_cif = bootstrap.ti_config_service.get_value(
'get_user_info.mongodb_path_cif',
default='/Users/andpay/PycharmProjects/score_card_end/ac_cif_db')
mongodb_name_cif = bootstrap.ti_config_service.get_value(
'get_user_info.mongodb_name_cif', default='ad_cif_db')
db = MongoTemplate.get_database(mongodb_path_cif, mongodb_name_cif)
#print(db.collection_names(include_system_collections=False)) 查询数据库中的表
collection = db.get_collection("PartyAdditionInfo")
return collection
def get_contact():
init_app()
psns_contact = get_psns_phonecontact()
contact_list = []
for item in psns_contact.find():
partyid = item['partyId']
num = len(item['items'])
contact_list.append([partyid, num])
contact_df = pd.DataFrame(contact_list, columns=['partyid', 'phone_num'])
return contact_df
def out_put_run():
init_app()
logger = logging.getLogger(__name__)
startime=time.strftime("%Y-%m-%d %H:%M:%S",time.localtime())
#logger.info('to get m2_df begin')
#m2_df=get_cif_M2()
mongo_lrds=get_lrds_maindoc()
key_name=data_center({},'name')
count=0
data_soure=[]
result_list = []
#for item in mongo_lrds.find({'crtTime':{'$gte':datetime(2017,1,1)}}):
#for item in collection.find({'loanApplyInfo.data.partyId':{'$in':partyid_list}}):
#for item in mongo_lrds.find().sort('crtTime',-1).limit(10):
for item in mongo_lrds.find(no_cursor_timeout=True).batch_size(500):
data_soure.append(item)
count=count+1
if count==500:
#print(time.strftime("%Y-%m-%d %H:%M:%S",time.localtime()))
for item in data_soure:
key_value=data_center(item,'value')
if key_value is None:
continue
else:
print(key_value)
result_list.append(key_value)
data_soure=[]
count=0
all_info_df=pd.DataFrame(result_list,columns=key_name)
#选取最大applyID
#applyid_df=all_info_df.groupby(all_info_df['partyid']).agg({'applyid':'max'}).reset_index()
#applyid_serise=applyid_df['applyid']
#按最大applyid进行过滤
#end_all_info_df=all_info_df[all_info_df['applyid'].isin(applyid_serise)]
#与逾期数据融合
#end_all_info_df=pd.merge(m2_df,end_all_info_df,on='partyid',how='left')
endtime=time.strftime("%Y-%m-%d %H:%M:%S",time.localtime())
logger.info('data handle, fromTime=[%s], toTime=[%s].' % (startime, endtime))
return all_info_df
def get_lrds_maindoc():
init_app()
mongodb_path_lrds = bootstrap.ti_config_service.get_value(
'get_user_info.mongodb_path_lrds', default='/db/mongodb/ac_lrds_db')
mongodb_name_lrds = bootstrap.ti_config_service.get_value(
'get_user_info.mongodb_name_lrds', default='ac_lrds_db')
db = MongoTemplate.get_database(mongodb_path_lrds, mongodb_name_lrds)
#print(db.collection_names(include_system_collections=False)) 查询数据库中的表
#['mybankLoanMainDoc', 'mainDoc', '_asyncDataHandlerRegisters'] 所有表
collection = db.get_collection("mainDoc")
return collection
def get_psns_phonecontact():
init_app()
mongodb_path_psns = bootstrap.ti_config_service.get_value(
'get_user_info.mongodb_path_psns',
default='/Users/andpay/PycharmProjects/score_card_end/ac_psns_db')
mongodb_name_psns = bootstrap.ti_config_service.get_value(
'get_user_info.mongodb_name_psns', default='ac_psns_db')
db = MongoTemplate.get_database(mongodb_path_psns, mongodb_name_psns)
#print(db.collection_names(include_system_collections=False)) 查询数据库中的表
collection = db.get_collection("phoneContacts")
for i in collection.find(no_cursor_timeout=True):
print(i)
return collection
def email_task():
init_app()
score_df = get_contact()
excel_writer = pd.ExcelWriter('/home/andpay/data/excel/phone.xlsx',
engine='xlsxwriter')
score_df.to_excel(excel_writer, index=False)
excel_writer.save()
subject = 'relative_phone'
to_addrs = ['*****@*****.**']
body_text = 'relative_phone'
attachment_file = "/home/andpay/data/excel/phone.xlsx"
EmailSend.send_email(subject,
to_addrs,
body_text,
attachment_files=[attachment_file])
def get_all_value(id_list):
init_app()
table = get_lrds_maindoc()
all_list = []
for item in table.find({'applyId': {'$in': id_list}}):
applyid = item['applyId']
phone = get_phone(item)
reportid = get_reportid(item)
print([applyid, phone, reportid])
all_list.append([applyid, phone, reportid])
result_df = pd.DataFrame(all_list,
columns=['APPLYID', 'PHONE', 'REPORTID'])
return result_df
def get_failreason():
init_app()
today = datetime.date(datetime.today())
org_day = today - dt.timedelta(days=1)
today_s = "'" + str(today) + "'"
org_day_s = "'" + str(org_day) + "'"
sql = '''
select date_format(a.applytime,'%Y-%m-%d'),
json_extract(applyData,'$.issuerName'),
errorcode,errormsg,
count(distinct case when a.applystatus<>'O' and c.status='F' then a.applyinfoid end),
count(distinct a.applyinfoid)
from ac_bts_db.ApplyInfo a
left join ac_bts_db.InsteadRepayTxnCtrl b
on a.applyinfoid=b.applyinfoid
left join ac_bts_db.InsteadRepaySchedule c
on b.insteadrepaytxnctrlid=c.insteadrepaytxnctrlid
where c.scheduletype='PT'
and a.applytime<date_format(''' + today_s + ''','%Y-%m-%d')
group by date_format(a.applytime,'%Y-%m-%d'),
json_extract(applyData,'$.issuerName'),
errorcode,errormsg
'''
sql_row = sql_util.select_rows_by_sql(sql_text=sql,
sql_paras={},
ns_server_id='/db/mysql/ac_bts_db',
max_size=-1)
fail_list = []
for row in sql_row:
fail_list.append(list(row))
fail_df = pd.DataFrame(
fail_list, columns=['日期', '银行', '失败code', '失败原因', '失败笔数', '总笔数'])
fail_df = fail_df.sort_values(by=['日期', '失败code', '失败笔数'],
ascending=[0, 1, 0])
fail_df = fail_df[(fail_df['失败笔数'] != 0) & (fail_df['失败原因'].notnull())]
return fail_df
def nodeal_user():
init_app()
sql = ''' select * from
(
select pid,ids,repaymode,
case when applystatus='O' and cancelflag=TRUE then '用户终止结清'
when applystatus='O' and fallback=1 then '扣款失败结清'
when applystatus='O' then '正常结清'
when applystatus='C' and cancelflag=TRUE then '用户终止撤销'
when applystatus='C' then '扣款失败撤销'
else '未结清' end deal_status
from
(
select a.applyinfoid ids,a.partyid pid,a.applystatus,a.repaymode,
json_extract(a.applydata,'$.issuerName') bank,
json_extract(txndata,'$.customLine') ctline,
json_extract(txndata,'$.userCancelFlag') cancelflag,
b.fallback,b.status,b.repayamt,b.payedamt,b.hasrepayamt
from ac_bts_db.ApplyInfo a
left join ac_bts_db.InsteadRepayTxnCtrl b
on a.applyinfoid=b.applyinfoid
where applytime>='2018-01-22' and applytime<'2018-01-24'
) x
) y
where deal_status in ('用户终止结清','用户终止撤销','扣款失败撤销')
'''
sql_row = sql_util.select_rows_by_sql(sql_text=sql,
sql_paras={},
ns_server_id='/db/mysql/ac_bts_db',
max_size=-1)
partyid_list = []
for row in sql_row:
partyid_list.append(list(row))
partyid_df = pd.DataFrame(
partyid_list, columns=['partyid', 'applyid', 'repaymode', 'status'])
return partyid_df
def get_relative_phone():
init_app()
table = get_cif_partyadditioninfo()
phone_list = []
for item in table.find({'partyId': {'$in': party_list}}):
partyid = item['partyId']
relative_mobile = get_relative(item)
colleague_moblie = get_colleague(item)
ls = [partyid, relative_mobile, colleague_moblie]
phone_list.append(ls)
phone_df = pd.DataFrame(
phone_list, columns=['partyid', 'relative_mobile', 'colleague_mobile'])
return phone_df
def get_cif_loantime():
init_app()
sql = '''
select partyid,max(loantime) from dev_db.f_loanagreement
where loanstatus in ('D','O','R','E')
group by partyid
'''
sql_row = sql_util.select_rows_by_zsql(sql_text=sql,
sql_paras={},
ns_server_id='/db/oracle/dev_dw_db')
loantime_list = []
for row in sql_row:
print(row)
loantime_list.append(list(row))
loantime_df = pd.DataFrame(loantime_list, columns=['partyid', 'loantime'])
return loantime_df
def nodeal_user():
init_app()
sql_1 = '''select distinct partyid
from ac_bts_db.ApplyInfo
'''
user_row = sql_util.select_rows_by_sql(sql_text=sql_1,
sql_paras={},
ns_server_id='/db/mysql/ac_bts_db',
max_size=-1)
user_list = []
for user in user_row:
user_list.append(user[0])
user_list = str(tuple(user_list))
sql_2 = '''select distinct b.merchantCustomerId
from ac_agw_db.AuthBindCard a
left join ac_agw_db.MerchantUser b
on a.merchantUserId=b.merchantUserId
where a.authNetId in ('08470009-00', '08470010-00') and a.status='1' and a.crttime<'2018-01-30'
and b.merchantCustomerId not in''' + user_list
result_row = sql_util.select_rows_by_sql(
sql_text=sql_2,
sql_paras={},
ns_server_id='/db/mysql/ac_bts_db',
max_size=-1)
result_list = []
for row in result_row:
result_list.append(row[0])
result_list1 = result_list[:700]
result_list2 = result_list[700:]
result_list1 = str(tuple(result_list1))
result_list2 = str(tuple(result_list2))
sql_3 = '''select a.partyid,c.creditline,c.lineused,
d.loantimes,d.loanamt,e.txntimes,e.txnamt
from dev_dw.dim_txnparty a
left join dev_dw.bts_applyinfo b
on a.partyid=b.partyid
left join
( select x.partyid pid ,x.totalcreditline creditline,x.totalcreditlineused lineused
from dev_dw.f_pcrbasicinfo x
join
(
select partyid,max(id) mid from dev_dw.f_pcrbasicinfo
where reporttime>=to_date('2018-01-01','yyyy-mm-dd')
group by partyid
) y
on x.id=y.mid
) c
on a.partyid=c.pid
left join
(
select partyid,count(distinct id) loantimes,sum(loanamt) loanamt from dev_dw.f_loanagreement
where loantime >=to_date('2017-11-01','yyyy-mm-dd') and loanstatus in ('D','O','R','E')
group by partyid
) d
on a.partyid=d.partyid
left join
(
select txnpartyid,count(distinct txnid) txntimes,sum(salesamt) txnamt from dev_dw.f_txnlist
where txntime>=to_date('2017-11-01','yyyy-mm-dd') and txnflag='S' and salesamt>0
group by txnpartyid
) e
on a.partyid=e.txnpartyid
where a.partyid in ''' + result_list1 + ''' or a.partyid in ''' + result_list2
oracle_row = sql_util.select_rows_by_sql(
sql_text=sql_3,
sql_paras={},
ns_server_id='/db/oracle/dev_dw_db',
max_size=-1)
user_info = []
for row in oracle_row:
user_info.append(list(row))
col = [
'partyid', 'creditline', 'lineused', 'loantime_3m', 'loanamt_3m',
'txntime_3m', 'txnamt_3m'
]
user_df = pd.DataFrame(user_info, columns=col)
return user_df, col
def __init__(self, ns_config):
init_app()
self.ns_server_id = ns_config
#!/usr/bin/python
# encoding=utf-8
from ti_config.bootstrap import init_ti_srv_cfg
from ti_daf.sql_context import SqlContext, session_scope, iselect_rows_by_sql
from ti_daf import SqlTemplate, sql_util
import json
import pandas as pd
import numpy as np
from datetime import datetime
import datetime as dt
from get_user_info.data_merge.send_email import EmailSend
import os
from get_user_info.config import init_app
init_app()
def get_customer_df():
today = datetime.date(datetime.today())
yesterday = today - dt.timedelta(days=1)
afterday = today + dt.timedelta(days=2)
sql = '''
select to_char(b.crttime,'yyyy-mm-dd') days,b.servicestaff,a.source,
count(distinct b.customerid) call_num,
count(distinct case when b.connected=1 then b.customerid end ) connect_num,
count(distinct case when b.valid=1 then b.customerid end) valid_num,
count(distinct case when c.partyid is not null then b.customerid end) login_num,
count(distinct case when d.partyid is not null then b.customerid end) loan_num
def get_basicdata():
init_app()
today = datetime.date(datetime.today())
origin_day = today - dt.timedelta(days=59)
today_s = "'" + str(today) + "'"
origin_day_s = "'" + str(origin_day) + "'"
sql = '''
select uu.register_count,xx.*,yy.loanamt,yy.new_loanamt,yy.old_loanamt,zz.backamt from
(
select date_format(applytime,'%Y-%m-%d') days,count(distinct a.applyinfoid) apply_num,
count(distinct case when repaymode=0 then a.applyinfoid end) help_num,
count(distinct case when repaymode=1 then a.applyinfoid end) circle_num,
count(distinct case when e.deal_status='用户终止撤销' then a.applyinfoid end) usercancel_num,
count(distinct case when e.deal_status='首笔失败撤销' then a.applyinfoid end) firstcancel_num,
count(distinct case when e.deal_status='用户终止结清' then a.applyinfoid end) usersettle_num,
count(distinct case when e.deal_status='中途失败结清' then a.applyinfoid end) midfailsettle_num,
count(distinct case when e.deal_status='全额结清' then a.applyinfoid end) fullamountsettle_num,
count(distinct case when e.deal_status='其他' then a.applyinfoid end) other_num,
count(distinct case when e.deal_status='逾期中' then a.applyinfoid end ) overdue_num
from ac_bts_db.ApplyInfo a
left join ac_bts_db.InsteadRepayTxnCtrl b
on a.applyinfoid=b.applyinfoid
left join ac_bts_db.InsteadRepaySchedule c
on b.insteadrepaytxnctrlid=c.insteadrepaytxnctrlid
left join ac_lms_db.LoanApplyInfo d
on c.exttxnid=d.id and c.scheduletype='RT'
left join
(
select a.applyinfoid ids,
case when applystatus='O' and json_extract(txndata,'$.userCancelFlag')=TRUE then '用户终止结清'
when applystatus='O' and hasrepayamt<repayamt then '中途失败结清'
when applystatus='O' then '全额结清'
when applystatus='C' and json_extract(txndata,'$.userCancelFlag')=TRUE then '用户终止撤销'
when applystatus='C' then '首笔失败撤销'
when hasrepayamt>0 and payedamt<hasrepayamt and applystatus<>'O' then '逾期中'
else '其他' end deal_status
from ac_bts_db.ApplyInfo a
left join ac_bts_db.InsteadRepayTxnCtrl b
on a.applyinfoid=b.applyinfoid
) e
on e.ids=a.applyinfoid
where applytime<''' + today_s + ''' and applytime>=''' + origin_day_s + '''
group by date_format(applytime,'%Y-%m-%d')
) xx
left join
(
select date_format(exestarttime,'%Y-%m-%d') days,sum(a.amt) loanamt,
sum(case when TIMESTAMPDIFF(day,c.crttime,a.prestarttime)<=30 then a.amt end) new_loanamt,
sum(case when TIMESTAMPDIFF(day,c.crttime,a.prestarttime)>30 then a.amt end) old_loanamt
from ac_bts_db.InsteadRepaySchedule a
left join ac_bts_db.InsteadRepayTxnCtrl b
on a.insteadrepaytxnctrlid=b.insteadrepaytxnctrlid
left join ac_cif_db.Party c
on b.partyid=c.partyid
where scheduletype='RT' and a.status='S'
and a.prestarttime<''' + today_s + ''' and a.prestarttime>=''' + origin_day_s + '''
group by date_format(exestarttime,'%Y-%m-%d')
) yy
on xx.days=yy.days
left join
(
select date_format(exestarttime,'%Y-%m-%d') days,sum(a.amt) backamt
from ac_bts_db.InsteadRepaySchedule a
where scheduletype in ('PT','FT') and a.status='S'
and exestarttime<''' + today_s + ''' and exestarttime>=''' + origin_day_s + '''
group by date_format(exestarttime,'%Y-%m-%d')
) zz
on xx.days=zz.days
left join
(
select date_format(crttime,'%Y-%m-%d') days,count(distinct partyid) register_count from ac_cif_db.Party
where crttime<''' + today_s + ''' and crttime>=''' + origin_day_s + '''
group by date_format(crttime,'%Y-%m-%d')
) uu
on xx.days=uu.days
'''
day_row = sql_util.select_rows_by_sql(sql_text=sql,
sql_paras={},
ns_server_id='/db/mysql/ac_bts_db',
max_size=-1)
day_list = []
for row in day_row:
day_list.append(list(row))
day_df = pd.DataFrame(day_list,
columns=[
'register', 'day', 'apply_num', 'help_num',
'circle_num', 'usercancel_num',
'firstcancel_num', 'usersettle_num',
'midfailsettle_num', 'fullamountsettle_num',
'other_num', 'overdue_num', 'loan_amt',
'new_loanamt', 'old_loanamt', 'return_amt'
])
# ---------------------------------------------------------
# today=datetime.date(datetime.today())
# origin_day=datetime.date(datetime.strptime('2017-12-21','%Y-%m-%d'))
# num=(today-origin_day).days
card_list = []
for i in range(-1, 60):
af_day = today - dt.timedelta(days=i)
bf_day = today - dt.timedelta(days=i + 1)
af_day_s = "'" + str(af_day) + "'"
bf_day_s = "'" + str(bf_day) + "'"
sql_1 = '''
select date_format(x.ctime,'%Y-%m-%d'),count(distinct x.mcid),count(distinct x.cid)
from
(
select a.crttime ctime,b.merchantCustomerId mcid,c.cardno cid
from ac_agw_db.AuthBindCard a
left join ac_agw_db.MerchantUser b
on a.merchantUserId=b.merchantUserId
left join ac_agw_db.MerchantUserCard c
on a.merchantusercardid=c.merchantusercardid
where a.authNetId like '08470010-00%' and a.status='1'
and a.crttime<''' + af_day_s + ''' and a.crttime>=''' + bf_day_s + '''
) x
left join
(
select
distinct c.cardno cid
from ac_agw_db.AuthBindCard a
left join ac_agw_db.MerchantUser b
on a.merchantUserId=b.merchantUserId
left join ac_agw_db.MerchantUserCard c
on a.merchantusercardid=c.merchantusercardid
where a.authNetId like '08470010-00%' and a.status='1'
and a.crttime<''' + bf_day_s + '''
) y
on x.cid=y.cid
where y.cid is null
group by date_format(x.ctime,'%Y-%m-%d')
'''
card_row = sql_util.select_rows_by_sql(
sql_text=sql_1,
sql_paras={},
ns_server_id='/db/mysql/ac_bts_db',
max_size=-1)
for row in card_row:
card_list.append(list(row))
card_df = pd.DataFrame(card_list,
columns=['day', 'person_num', 'card_num'])
end_day_df = pd.merge(card_df, day_df, on='day', how='right')
end_day_df = end_day_df.sort_values(by='day', ascending=[0])
col = list(end_day_df.columns)
col.remove('day')
end_day_df.loc['row_sum'] = end_day_df[col].apply(lambda x: x.sum(),
axis=0)
end_day_df.loc[end_day_df.index == 'row_sum', 'day'] = '总计'
pass_rate = end_day_df['help_num'] / end_day_df['apply_num']
end_day_df.insert(7, 'pass_rate', pass_rate)
end_day_df.columns = [
'日期', '绑卡人数', '绑卡张数', '新增注册人数', '提交笔数', '垫付笔数', '循环笔数', '垫付占比',
'用户终止撤销笔数', '首笔失败撤销笔数', '用户终止结清笔数', '中途失败结清笔数', '全额结清笔数', '其他笔数',
'逾期笔数', '放款金额', '新户放款额', '旧户放款额', '回款金额'
]
end_day_df = end_day_df.fillna(0)
usercancel_proportion = end_day_df['用户终止撤销笔数'] / end_day_df['提交笔数']
userfail_proportion = end_day_df['首笔失败撤销笔数'] / end_day_df['提交笔数']
partsettle_proportion = end_day_df['用户终止结清笔数'] / end_day_df['提交笔数']
midfailsettle_proportion = end_day_df['中途失败结清笔数'] / end_day_df['提交笔数']
allsettle_proportion = end_day_df['全额结清笔数'] / end_day_df['提交笔数']
end_day_df.insert(9, '用户终止撤销占比', usercancel_proportion)
end_day_df.insert(11, '首笔失败撤销占比', userfail_proportion)
end_day_df.insert(13, '用户终止结清占比', partsettle_proportion)
end_day_df.insert(15, '中途失败结清占比', midfailsettle_proportion)
end_day_df.insert(17, '全额结清占比', allsettle_proportion)
return end_day_df
def get_funneldata():
init_app()
today = datetime.date(datetime.today())
today_s = "'" + str(today) + "'"
yesterday = today - dt.timedelta(days=1)
yesterday_s = "'" + str(yesterday) + "'"
sql = '''
select
case when sysdate-registertime<30 and HASCLEARREPAY=0 then 'new_u' else 'old_u' end cate,
count(distinct case when hasloginapp=1 then x.partyid end ) allcount,
count(distinct case when hasloginapp=1 and ENTRYREPAYHOME=1 then x.partyid end) enterhome,
count(distinct case when BINDCARDONCE=1 and hasloginapp=1
and ENTRYREPAYHOME=1 then x.partyid end) hasbindcard,
count(distinct case when BINDCARDNOW=1 and hasloginapp=1
and ENTRYREPAYHOME=1 then x.partyid end) nowbindcard,
count(distinct case when CLICKAPPLYONAPP=1 and BINDCARDONCE=1
and hasloginapp=1 and ENTRYREPAYHOME=1 and BINDCARDONCE=1
and lastbindcardtime>=to_date(''' + yesterday_s + ''','yyyy-mm-dd')
and lastbindcardtime<to_date(''' + today_s + ''','yyyy-mm-dd') then x.partyid end ) hitapply,
count(distinct case when HASAPPLYREPAY=1 and CLICKAPPLYONAPP=1
and BINDCARDONCE=1 and hasloginapp=1 and ENTRYREPAYHOME=1 and BINDCARDONCE=1
and lastapplyrepaytime>=to_date(''' + yesterday_s + ''','yyyy-mm-dd')
and lastapplyrepaytime<to_date(''' + today_s + ''','yyyy-mm-dd') then x.partyid end ) hasapply,
count(distinct case when HASCLEARREPAY=1 and HASAPPLYREPAY=1
and CLICKAPPLYONAPP=1 and BINDCARDONCE=1 and hasloginapp=1 and ENTRYREPAYHOME=1 and BINDCARDONCE=1
and lastclearrepaytime>=to_date(''' + yesterday_s + ''','yyyy-mm-dd')
and lastclearrepaytime<to_date(''' + today_s + ''','yyyy-mm-dd') then x.partyid end ) settle
from dev_dw.f_repaytags x
where lastlogintime<''' + today_s + ''' and lastlogintime>=''' + yesterday_s + '''
group by case when sysdate-registertime<30 and HASCLEARREPAY=0 then 'new_u' else 'old_u' end '''
card_row = sql_util.select_rows_by_sql(sql_text=sql,
sql_paras={},
ns_server_id='/db/oracle/dev_dw_db',
max_size=-1)
card_list = []
for row in card_row:
card_list.append(list(row))
card_df = pd.DataFrame(card_list,
columns=[
'类别', '登入过app', '进入帮还首页', '绑过银行卡', '当前有绑卡',
'点击申请按钮', '有申请记录', '有结清记录'
])
new_u = card_df[card_df['类别'] == 'new_u'].ix[:, 1:].values[0]
old_u = card_df[card_df['类别'] == 'old_u'].ix[:, 1:].values[0]
# num_list=list(card_row[0])
# print(num_list)
name_list = [
'登入过app', '进入帮还首页', '绑过银行卡', '当前有绑卡', '点击申请按钮', '有申请记录', '有结清记录'
]
sum_num_new = new_u[0]
sum_num_old = old_u[0]
result_list = []
for i in range(len(name_list)):
cate = name_list[i]
num_new = new_u[i]
num_old = old_u[i]
proportion_new = new_u[i] / sum_num_new
proportion_old = old_u[i] / sum_num_old
if i == 0:
propor_descend_new = propor_descend_old = 1
else:
propor_descend_new = new_u[i] / new_u[i - 1]
propor_descend_old = old_u[i] / old_u[i - 1]
result_list.append(
['新商户', cate, num_new, propor_descend_new, proportion_new])
result_list.append(
['旧商户', cate, num_old, propor_descend_old, proportion_old])
funnel_df = pd.DataFrame(result_list,
columns=['商户类型', '过程', '人数', '上一步转化率', '整体转化率'])
funnel_df = funnel_df.sort_values(by='商户类型', ascending=0)
return funnel_df
def get_rulecategroy():
init_app()
today = datetime.date(datetime.today())
bf_day = today - dt.timedelta(days=10)
yesterday = today - dt.timedelta(days=1)
today_s = "'" + str(today) + "'"
bf_day_s = "'" + str(bf_day) + "'"
sql = '''
select x.*,y.zmpass,y.zmrefuse,y.pcrpass,y.pcrrefuse from
(
select to_char(applydate,'yyyy-mm-dd') days, count(distinct b.businesskey) all_count,
count(distinct case when conclusion='A' then b.businesskey end ) pass,
count(distinct case when conclusion='D' then b.businesskey end ) refuse
from dev_dw.f_loanapplyinfo a
join dev_dw.f_ruletaskexeclog b
on to_char(a.id)=b.businesskey
where a.applytype='repayCredit'
and rulefullfuncname like '%genFinalDecision_RC%'
and applydate>=to_date(''' + bf_day_s + ''','yyyy-mm-dd')
and applydate<to_date(''' + today_s + ''','yyyy-mm-dd')
group by to_char(applydate,'yyyy-mm-dd')
) x
left join
(
select to_char(applydate,'yyyy-mm-dd') days,
count(distinct case when conclusion='A' and json_value(ruledatainjson,'$.checkPcrDataResult')='D' then b.businesskey end ) zmpass,
count(distinct case when conclusion='D' and json_value(ruledatainjson,'$.checkPcrDataResult')='D' then b.businesskey end ) zmrefuse,
count(distinct case when conclusion='A' and json_value(ruledatainjson,'$.checkPcrDataResult')='A' then b.businesskey end ) pcrpass,
count(distinct case when conclusion='D' and json_value(ruledatainjson,'$.checkPcrDataResult')='A' then b.businesskey end ) pcrrefuse
from dev_dw.f_loanapplyinfo a
join dev_dw.f_ruletaskexeclog b
on to_char(a.id)=b.businesskey
where a.applytype='repayCredit'
and rulefullfuncname like '%checkRulesDataExec_RC%'
and applydate>=to_date(''' + bf_day_s + ''','yyyy-mm-dd') and applydate<to_date(''' + today_s + ''','yyyy-mm-dd')
group by to_char(applydate,'yyyy-mm-dd')
) y
on x.days=y.days
'''
categroy_row = sql_util.select_rows_by_sql(
sql_text=sql,
sql_paras={},
ns_server_id='/db/oracle/dev_dw_db',
max_size=-1)
categroy_list = []
for row in categroy_row:
categroy_list.append(list(row))
rulecategroy_df = pd.DataFrame(categroy_list,
columns=[
'day', 'applytimes', 'passtimes',
'refusetimes', 'zmpass', 'zmrefuse',
'pcrpass', 'pcrrefuse'
])
pass_rate = rulecategroy_df['passtimes'] / rulecategroy_df['applytimes']
zmpass_rate = rulecategroy_df['zmpass'] / (rulecategroy_df['zmpass'] +
rulecategroy_df['zmrefuse'])
pcrpass_rate = rulecategroy_df['pcrpass'] / (rulecategroy_df['pcrpass'] +
rulecategroy_df['pcrrefuse'])
zmproportion = (rulecategroy_df['zmpass'] + rulecategroy_df['zmrefuse']
) / rulecategroy_df['applytimes']
pcrproportion = (rulecategroy_df['pcrpass'] + rulecategroy_df['pcrrefuse']
) / rulecategroy_df['applytimes']
zmall = rulecategroy_df['zmpass'] + rulecategroy_df['zmrefuse']
pcrall = rulecategroy_df['pcrpass'] + rulecategroy_df['pcrrefuse']
rulecategroy_df.insert(4, 'pass_rate', pass_rate)
rulecategroy_df.insert(5, 'zmall', zmall)
rulecategroy_df.insert(6, 'zmproportion', zmproportion)
rulecategroy_df.insert(7, 'pcrall', pcrall)
rulecategroy_df.insert(8, 'pcrproportion', pcrproportion)
rulecategroy_df.insert(11, 'zmpass_rate', zmpass_rate)
rulecategroy_df.insert(14, 'pcrpass_rate', pcrpass_rate)
rulecategroy_df = rulecategroy_df.fillna(0)
rulecategroy_df.columns = [
'日期', '运行笔数', '通过笔数', '拒绝笔数', '通过率', '芝麻运行笔数', '芝麻占比', '人行运行笔数',
'人行占比', '芝麻通过笔数', '芝麻拒绝笔数', '芝麻通过率', '人行通过笔数', '人行拒绝笔数', '人行通过率'
]
return rulecategroy_df
def get_circledata():
init_app()
today = datetime.date(datetime.today())
origin_day = today - dt.timedelta(days=59)
today_s = "'" + str(today) + "'"
origin_day_s = "'" + str(origin_day) + "'"
sql = '''
select xx.*,ww.loanamt,round(zz.debittime/zz.complete_num,1)
from
(
select date_format(applytime,'%Y-%m-%d') days,
count(distinct case when repaymode=1 then a.applyinfoid end) circle_num,
count(distinct case when repaymode=1 and e.deal_status='用户终止撤销' then a.applyinfoid end) usercancel_num,
count(distinct case when repaymode=1 and e.deal_status='首笔失败撤销' then a.applyinfoid end) firstcancel_num,
count(distinct case when repaymode=1 and e.deal_status='用户终止结清' then a.applyinfoid end) usersettle_num,
count(distinct case when repaymode=1 and e.deal_status='中途失败结清' then a.applyinfoid end) midfailsettle_num,
count(distinct case when repaymode=1 and e.deal_status='全额结清' then a.applyinfoid end) fullamountsettle_num,
count(distinct case when repaymode=1 and e.deal_status='其他' then a.applyinfoid end) other_num
from ac_bts_db.ApplyInfo a
left join ac_bts_db.InsteadRepayTxnCtrl b
on a.applyinfoid=b.applyinfoid
left join ac_bts_db.InsteadRepaySchedule c
on b.insteadrepaytxnctrlid=c.insteadrepaytxnctrlid
left join ac_lms_db.LoanApplyInfo d
on c.exttxnid=d.id and c.scheduletype='RT'
left join
(
select a.applyinfoid ids,
case when applystatus='O' and json_extract(txndata,'$.userCancelFlag')=TRUE then '用户终止结清'
when applystatus='O' and hasrepayamt<repayamt then '中途失败结清'
when applystatus='O' then '全额结清'
when applystatus='C' and json_extract(txndata,'$.userCancelFlag')=TRUE then '用户终止撤销'
when applystatus='C' then '首笔失败撤销'
else '其他' end deal_status
from ac_bts_db.ApplyInfo a
left join ac_bts_db.InsteadRepayTxnCtrl b
on a.applyinfoid=b.applyinfoid
) e
on e.ids=a.applyinfoid
where applytime<''' + today_s + ''' and applytime>=''' + origin_day_s + '''
group by date_format(applytime,'%Y-%m-%d')
) xx
left join
(
select date_format(exestarttime,'%Y-%m-%d') days,sum(a.amt) loanamt
from ac_bts_db.InsteadRepaySchedule a
left join ac_bts_db.InsteadRepayTxnCtrl b
on b.insteadrepaytxnctrlid=a.insteadrepaytxnctrlid
left join ac_bts_db.ApplyInfo c
on c.applyinfoid=b.applyinfoid
where scheduletype='RT' and a.status='S' and c.repaymode=1
group by date_format(exestarttime,'%Y-%m-%d')
) ww
on xx.days=ww.days
left join
(
select date_format(applytime,'%Y-%m-%d') days,
count(distinct case when b.completetime is not null then a.applyinfoid end) complete_num,
sum(case when b.completetime is not null then
TIMESTAMPDIFF(MINUTE,a.applytime,b.completetime) end) debittime
from ac_bts_db.ApplyInfo a
left join ac_bts_db.InsteadRepayTxnCtrl b
on a.applyinfoid=b.applyinfoid
where repaymode=1
group by date_format(applytime,'%Y-%m-%d')
) zz
on xx.days=zz.days
'''
circle_row = sql_util.select_rows_by_sql(
sql_text=sql,
sql_paras={},
ns_server_id='/db/mysql/ac_bts_db',
max_size=-1)
circle_list = []
for row in circle_row:
circle_list.append(list(row))
circle_df = pd.DataFrame(circle_list,
columns=[
'日期', '循环笔数', '用户终止撤销笔数', '首笔失败撤销笔数',
'用户终止结清笔数', '中途失败结清笔数', '全额结清笔数', '其他笔数',
'循环交易额', '平均扣款时长(分)'
])
circle_df = circle_df.sort_values(by='日期', ascending=0)
circle_df = circle_df[circle_df['日期'] >= '2018-01-15']
avgamt = circle_df['循环交易额'] / (circle_df['用户终止结清笔数'] +
circle_df['中途失败结清笔数'] + circle_df['全额结清笔数'])
avgamt = avgamt.apply(lambda x: round(x, 2))
circle_df.insert(9, '循环笔均金额', avgamt)
circle_df = circle_df.fillna(0)
usercancel_proportion = circle_df['用户终止撤销笔数'] / circle_df['循环笔数']
userfail_proportion = circle_df['首笔失败撤销笔数'] / circle_df['循环笔数']
partsettle_proportion = circle_df['用户终止结清笔数'] / circle_df['循环笔数']
midfailsettle_proportion = circle_df['中途失败结清笔数'] / circle_df['循环笔数']
allsettle_proportion = circle_df['全额结清笔数'] / circle_df['循环笔数']
circle_df.insert(3, '用户终止撤销占比', usercancel_proportion)
circle_df.insert(5, '首笔失败撤销占比', userfail_proportion)
circle_df.insert(7, '用户终止结清占比', partsettle_proportion)
circle_df.insert(9, '中途失败结清占比', midfailsettle_proportion)
circle_df.insert(11, '全额结清占比', allsettle_proportion)
return circle_df
def get_allrule_hit():
init_app()
today = datetime.date(datetime.today())
bf_day = today - dt.timedelta(days=10)
yesterday = today - dt.timedelta(days=1)
today_s = "'" + str(today) + "'"
bf_day_s = "'" + str(bf_day) + "'"
sql = '''
select day,conclusion,
case when rulename='B_ZMF_R001_RC' and y.keys is not null then 'B_ZMF_R001_RC_01'
when rulename='B_ZMF_R001_RC' and z.keys is not null then 'B_ZMF_R001_RC_02'
else rulename end rulenames,count(distinct x.keys)
from
(
select a.partyid pid,b.businesskey keys,to_char(startexectime,'yyyy-mm-dd') day,conclusion,
substr(rulefullfuncname,instr(rulefullfuncname,'.',1,8)+1,
length(rulefullfuncname)-instr(rulefullfuncname,'.',1,8)) rulename
from dev_dw.f_loanapplyinfo a
join dev_dw.f_ruletaskexeclog b
on to_char(a.id)=b.businesskey
where applytype='repayCredit'
and b.startexectime>=to_date(''' + bf_day_s + ''','yyyy-mm-dd')
and b.startexectime<to_date(''' + today_s + ''','yyyy-mm-dd')
and conclusion in ('A','D') and ruledatainjson not like '%trialRun%'
and substr(rulefullfuncname,instr(rulefullfuncname,'.',1,8)+1,
length(rulefullfuncname)-instr(rulefullfuncname,'.',1,8))
not like 'flow%'
) x
left join
(
select distinct businesskey keys from dev_dw.f_ruletaskexeclog
where json_value(ruledatainjson,'$.checkPcrDataResult')='A'
) y
on x.keys=y.keys
left join
(
select distinct businesskey keys from dev_dw.f_ruletaskexeclog
where json_value(ruledatainjson,'$.checkPcrDataResult')='D'
) z
on x.keys=z.keys
group by day,conclusion,
case when rulename='B_ZMF_R001_RC' and y.keys is not null then 'B_ZMF_R001_RC_01'
when rulename='B_ZMF_R001_RC' and z.keys is not null then 'B_ZMF_R001_RC_02'
else rulename end
'''
rule_row = sql_util.select_rows_by_sql(sql_text=sql,
sql_paras={},
ns_server_id='/db/oracle/dev_dw_db',
max_size=-1)
rule_list = []
for row in rule_row:
rule_list.append(list(row))
rule_df = pd.DataFrame(
rule_list, columns=['day', 'conclusion', 'rule_code', 'hit_num'])
# rule_df=rule_df.sort_values(by=['day','conclusion','hit_num'],ascending=[0,0,0])
rule_df = pd.pivot_table(rule_df,
index=['rule_code', 'conclusion'],
columns='day',
values='hit_num').reset_index().fillna(0)
sql_rule = '''select rule_name,rule_code from helprepay_rule '''
# db= SqlContext('/python/db/scratch')
map_row = sql_util.select_rows_by_sql(sql_text=sql_rule,
sql_paras={},
ns_server_id='/db/oracle/scratch_db',
max_size=-1)
map_list = []
for row in map_row:
map_list.append(list(row))
map_df = pd.DataFrame(map_list, columns=['rule_name', 'rule_code'])
rule_df = pd.merge(map_df, rule_df, on='rule_code', how='right')
# rule_df=pd.pivot_table(rule_df,index='day',columns='rule_code',values='hit_num').reset_index().fillna(0)
rule_df1 = rule_df[rule_df['conclusion'] == 'D']
rule_df2 = rule_df[rule_df['conclusion'] == 'A']
# rule_df1=pd.pivot_table(rule_df1,index=['rule_code','conclusion'],columns='day',values='hit_num').reset_index().fillna(0)
rule_df1 = rule_df1.sort_values(by=str(yesterday), ascending=0)
# rule_df2=pd.pivot_table(rule_df2,index=['rule_code','conclusion'],columns='day',values='hit_num').reset_index().fillna(0)
rule_df2 = rule_df2.sort_values(by=str(yesterday), ascending=0)
rule_df = pd.concat([rule_df1, rule_df2], axis=0)
return rule_df
