def data_refresh():
try:
day_row = sql_util.select_rows_by_sql(
sql_text=sql,
sql_paras={},
ns_server_id='/python/db/ac_bts_db',
max_size=-1)
card_row = sql_util.select_rows_by_sql(
sql_text=sql_1,
sql_paras={},
ns_server_id='/python/db/ac_agw_db',
max_size=-1)
except:
return 'error'
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_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_creditrate_df():
today = datetime.date(datetime.today())
origin_date = datetime.date(datetime.today()) - dt.timedelta(days=7)
ytd_s = "'" + str(today) + "'"
ord_s = "'" + str(origin_date) + "'"
sql = '''
select
a.creditrating,
count(distinct a.id) apply_num,
count(distinct case when a.status like '%A%' then a.id end) pass_num,
count(distinct case when b.id is not null and b.loanstatus in ('D','O','R','E') then a.id end) loan_num
from dev_dw.f_loanapplyinfo a
left join dev_dw.f_loanagreement b
on a.applyno=b.applyid
where a.idproduct<>21
and a.applydate>=to_date(''' + ord_s + ''','yyyy-mm-dd')
and a.applydate<to_date(''' + ytd_s + ''','yyyy-mm-dd')
group by a.creditrating
'''
sql_row = sql_util.select_rows_by_sql(sql_text=sql,
sql_paras={},
ns_server_id='/python/db/dev_dw_db',
max_size=-1)
sql_list = []
for row in sql_row:
sql_list.append(list(row))
creditrate_df = pd.DataFrame(
sql_list, columns=['partyid', 'apply_num', 'pass_num', 'loan_num'])
return creditrate_df
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_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_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_way_df():
yesterday = datetime.date(datetime.today()) - dt.timedelta(days=1)
origin_date = yesterday - dt.timedelta(days=6)
today = datetime.date(datetime.today())
ytd_s = "'" + str(yesterday) + "'"
ord_s = "'" + str(origin_date) + "'"
week_list = []
fail_list = []
for i in range(4):
time_after_s = today - dt.timedelta(days=i * 7)
time_before_s = today - dt.timedelta(days=(i + 1) * 7)
time_after_turn = yesterday - dt.timedelta(days=i * 7)
b_str = datetime.strftime(time_before_s, '%m-%d')
a_str = datetime.strftime(time_after_turn, '%m-%d')
string = b_str + "&" + a_str
time_before = "'" + str(time_before_s) + "'"
time_after = "'" + str(time_after_s) + "'"
# 每个通道数据
sql = '''
select a.paychannelid,case when fee=0 then 'companypay' else 'userpay' end cate,
count(distinct a.idinvoice),
sum(amt)
from dev_dw.f_payment a
left join dev_dw.f_repayloantxnlist b
on a.idinvoice=b.idinvoice
where a.status='S'
and a.paytime>=to_date(''' + time_before + ''','yyyy-mm-dd')
and a.paytime<to_date(''' + time_after + ''','yyyy-mm-dd')
and b.applytype<>'repayCredit'
group by a.paychannelid,
case when fee=0 then 'companypay' else 'userpay' end
'''
way_row = sql_util.select_rows_by_sql(
sql_text=sql,
sql_paras={},
ns_server_id='/python/db/dev_dw_db',
max_size=-1)
for row in way_row:
week_list.append([string] + list(row))
sql_fail = '''
select
case when failcode='IPS.009' then '通道不支持'
when failcode='IPS.027' then '查询支付结果失败'
when failcode='IPS.028' then '外部代扣编号不存在'
when failcode='IPS.029' then '交易记录不存在'
when failcode='IPS.030' then '支付通道银行不存在'
when failcode='IPS.031' then '支付路由异常'
when failcode='IPS.032' then '查询对账文件失败'
when failcode='IPS.033' then '支付同步次数达到最大限制'
when failcode='IPS.034' then '余额不足'
when failcode='IPS.035' then '不匹配的支付金额'
when failcode='IPS.036' then '不匹配的交易状态'
when failcode='IPS.037' then '不匹配的票据状态'
when failcode='IPS.038' then '支付失败(因为再次支付)' end pay_status,
count(distinct idinvoice) counts
from dev_dw.f_payment
where paychannelid in ('DDP.QTO.PAY','DDP.KFT.PAY','DDP.KQ.FAST.PAY')
and paytime>=to_date(''' + time_before + ''','yyyy-mm-dd') and paytime<to_date(''' + time_after + ''','yyyy-mm-dd')
and status='F'
group by
case when failcode='IPS.009' then '通道不支持'
when failcode='IPS.027' then '查询支付结果失败'
when failcode='IPS.028' then '外部代扣编号不存在'
when failcode='IPS.029' then '交易记录不存在'
when failcode='IPS.030' then '支付通道银行不存在'
when failcode='IPS.031' then '支付路由异常'
when failcode='IPS.032' then '查询对账文件失败'
when failcode='IPS.033' then '支付同步次数达到最大限制'
when failcode='IPS.034' then '余额不足'
when failcode='IPS.035' then '不匹配的支付金额'
when failcode='IPS.036' then '不匹配的交易状态'
when failcode='IPS.037' then '不匹配的票据状态'
when failcode='IPS.038' then '支付失败(因为再次支付)' end
'''
fail_row = sql_util.select_rows_by_sql(
sql_text=sql_fail,
sql_paras={},
ns_server_id='/python/db/dev_dw_db',
max_size=-1)
for row in fail_row:
fail_list.append([string] + list(row))
week_way_df = pd.DataFrame(
week_list,
columns=['day', 'channel', 'cate', 'successnum', 'successamt'])
week_way_df[['successnum',
'successamt']] = week_way_df[['successnum',
'successamt']].astype(int)
week_way_df = pd.pivot_table(week_way_df,
index='day',
columns=['cate', 'channel'],
values=['successnum',
'successamt']).reset_index()
fail_way_df = pd.DataFrame(fail_list,
columns=['day', 'fail_reason', 'fail_num'])
fail_way_df = pd.pivot_table(fail_way_df,
index='day',
columns='fail_reason',
values='fail_num')
return week_way_df, fail_way_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
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_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_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
from dev_dw.f_customer a
left join dev_dw.f_callinfo b
on a.id=b.customerid
left join
(
select distinct partyid from dev_dw.f_loginhistory
where logintime>=to_date(''' + "'" + str(
yesterday) + "'" + ''','yyyy-mm-dd')
and logintime<to_date(''' + "'" + str(today) + "'" + ''','yyyy-mm-dd')
) c
on a.partyid=c.partyid
left join
(
select distinct partyid from dev_dw.f_loanagreement
where idproduct<>21 and loanstatus in ('D','O','R','E')
and loantime>=to_date(''' + "'" + str(
yesterday) + "'" + ''','yyyy-mm-dd')
and loantime<to_date(''' + "'" + str(
afterday) + "'" + ''','yyyy-mm-dd')
) d
on a.partyid=d.partyid
left join
(
select distinct partyid from dev_dw.f_loanapplyinfo
where idproduct<>21
and applydate>=to_date(''' + "'" + str(
yesterday) + "'" + ''','yyyy-mm-dd')
and applydate<to_date(''' + "'" + str(
afterday) + "'" + ''','yyyy-mm-dd')
) e
on a.partyid=e.partyid
where a.source in ('未续借信用通商户','未提现信用通商户')
and b.crttime>=to_date(''' + "'" + str(yesterday) + "'" + ''','yyyy-mm-dd')
and b.crttime<to_date(''' + "'" + str(afterday) + "'" + ''','yyyy-mm-dd')
group by to_char(b.crttime,'yyyy-mm-dd'),b.servicestaff,a.source
'''
sql_row = sql_util.select_rows_by_sql(sql_text=sql,
sql_paras={},
ns_server_id='/python/db/dev_dw_db',
max_size=-1)
data_list = []
for row in sql_row:
data_list.append(list(row))
data_df = pd.DataFrame(data_list,
columns=[
'day', 'services', 'source', 'call_num',
'connect_num', 'valid_num', 'login_num',
'loan_num'
])
data_df = data_df.sort_values(by=['day', 'services'], ascending=[0, 1])
no_withdraw_df = data_df[data_df['source'] == '未提现信用通商户']
no_renew_df = data_df[data_df['source'] == '未续借信用通商户']
no_withdraw_df = no_withdraw_df.drop(['source'], axis=1)
no_renew_df = no_renew_df.drop(['source'], axis=1)
login_rate_withdraw = no_withdraw_df['login_num'] / no_withdraw_df[
'valid_num']
login_rate_renew = no_renew_df['login_num'] / no_renew_df['valid_num']
no_withdraw_df.insert(6, 'login_rate', login_rate_withdraw)
no_renew_df.insert(6, 'login_rate', login_rate_renew)
no_withdraw_df.columns = [
'触达日期', '触达经理', '触达人数', '接通人数', '有效人数', '登入人数', '登入率', '提现人数'
]
no_renew_df.columns = [
'触达日期', '触达经理', '触达人数', '接通人数', '有效人数', '登入人数', '登入率', '提现人数'
]
return no_withdraw_df, no_renew_df
def get_repay_df():
today = datetime.date(datetime.today())
yesterday = today - dt.timedelta(days=1)
day_list = []
for i in range(4):
after = today - dt.timedelta(days=i * 7)
before = today - dt.timedelta(days=(i + 1) * 7)
mid = yesterday - dt.timedelta(days=i * 7)
after_s = "'" + str(after) + "'"
before_s = "'" + str(before) + "'"
time_bl = datetime.strftime(before, '%m-%d')
time_al = datetime.strftime(mid, '%m-%d')
time_str = time_bl + "&" + time_al
sql = '''
select sum(apply_num),sum(help_num),sum(circle_num),sum(firstcredit_num),sum(helpsettle_num),
sum(circlesettle_num),sum(help_amt),sum(circle_amt),sum(firstcredit_amt),avg(avgamt),sum(help_amt_all),sum(circle_amt_all)
from
(
select date_format(applytime,'%Y-%m-%d') days,count(distinct a.applyinfoid) apply_num,
count(distinct case when repaymode=0
and (json_extract(b.txndata,'$.supportFirstCredit')=False or json_extract(b.txndata,'$.supportFirstCredit') is null)
then a.applyinfoid end) help_num,
count(distinct case when repaymode=1
and (json_extract(b.txndata,'$.supportFirstCredit')=False or json_extract(b.txndata,'$.supportFirstCredit') is null)
then a.applyinfoid end) circle_num,
count(distinct case when json_extract(txndata,'$.supportFirstCredit')=True then a.applyinfoid end) firstcredit_num,
count(distinct case when repaymode=0 and e.deal_status in ('用户终止结清','中途失败结清','全额结清')
and (json_extract(b.txndata,'$.supportFirstCredit')=False or json_extract(b.txndata,'$.supportFirstCredit') is null)
then a.applyinfoid end) helpsettle_num,
count(distinct case when repaymode=1 and e.deal_status in ('用户终止结清','中途失败结清','全额结清')
and (json_extract(b.txndata,'$.supportFirstCredit')=False or json_extract(b.txndata,'$.supportFirstCredit') is null)
then a.applyinfoid end) circlesettle_num,
sum(case when repaymode=0 then b.repayamt end) help_amt_all,
sum(case when repaymode=1 then b.repayamt end) circle_amt_all
from ac_bts_db.ApplyInfo a
left join ac_bts_db.InsteadRepayTxnCtrl b
on a.applyinfoid=b.applyinfoid
left join
(
select distinct 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 a.applytime>=''' + before_s + ''' and a.applytime<''' + after_s + '''
group by date_format(applytime,'%Y-%m-%d')
) xx
left join
(
select date_format(exestarttime,'%Y-%m-%d') days,
sum(case when c.repaymode=0 and (json_extract(b.txndata,'$.supportFirstCredit')=False or json_extract(b.txndata,'$.supportFirstCredit') is null)
then a.amt end) help_amt,
sum(case when c.repaymode=1 and (json_extract(b.txndata,'$.supportFirstCredit')=False or json_extract(b.txndata,'$.supportFirstCredit') is null)
then a.amt end) circle_amt,
sum(case when json_extract(b.txndata,'$.supportFirstCredit') =True then a.amt end) firstcredit_amt
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 a.exestarttime>=''' + before_s + ''' and a.exestarttime<''' + after_s + '''
group by date_format(exestarttime,'%Y-%m-%d')
) yy
on xx.days=yy.days
left join
(
select date_format(applytime,'%Y-%m-%d') days,
avg(json_extract(txndata,'$.evaluateOpenAmt')) avgamt
from ac_bts_db.ApplyInfo a
left join ac_bts_db.InsteadRepayTxnCtrl b
on a.applyinfoid=b.applyinfoid
where repaymode=0 and json_extract(txndata,'$.evaluateOpenAmt') is not null
and applytime>=''' + before_s + ''' and applytime<''' + after_s + '''
group by date_format(applytime,'%Y-%m-%d')
) zz
on xx.days=zz.days
'''
day_row = sql_util.select_rows_by_sql(
sql_text=sql,
sql_paras={},
ns_server_id='/python/db/ac_cif_db',
max_size=-1)
for row in day_row:
day_list.append([time_str] + list(row))
day_df = pd.DataFrame(day_list,
columns=[
'日期', '提交笔数', '垫付笔数', '循环笔数', '首笔授信笔数', '垫付结清笔数',
'循环结清笔数', '垫付金额', '循环金额', '首笔授信金额', '平均授信',
'垫付申请金额', '循环申请金额'
])
day_df = day_df.sort_values(by='日期', ascending=[1])
return day_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 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