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