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