def get_operation_df():
today = datetime.date(datetime.today())
yesterday = today - dt.timedelta(days=1)
days = today.day
num = today.month
this_mon = pd.Period(today, freq='M')
first_mon = this_mon - 5
first_mon_s = "'" + str(first_mon) + "'"
result_list = []
# for i in range(5):
# mon=this_mon-i
# day_begin="'"+str(mon)+"-01"+"'"
# day_end="'"+str(mon)+"-"+str(days)+"'"
sql = ''' select a.mon mon,a.apply_times apply_times,b.acess_times access_times,c.loan_times loan_times,
round(b.acess_times/a.apply_times,4) access_rate,round(c.loan_times/b.acess_times,4) withdraw_rate
from
(
select to_char(applydate,'yyyy-mm') mon,count(distinct id) apply_times
from dev_dw.f_loanapplyinfo
where applytype not in ('repayCredit')
group by to_char(applydate,'yyyy-mm')
) a
left join
(
select to_char(applydate,'yyyy-mm') mon,count(distinct id) acess_times
from dev_dw.f_loanapplyinfo
where status like '%A%' and applytype not in ('repayCredit')
group by to_char(applydate,'yyyy-mm')
) b
on a.mon=b.mon
left join
(
select to_char(loantime,'yyyy-mm') mon,count(distinct id) loan_times
from dev_dw.f_loanagreement
where loanstatus in ('D','O','R','E') and idproduct<>21
group by to_char(loantime,'yyyy-mm')
) c
on a.mon=c.mon
where a.mon>=''' + first_mon_s
with session_scope(db_1) as session:
for row in iselect_rows_by_sql(session, sql, []):
result_list.append(list(row))
thams_df = pd.DataFrame(result_list,
columns=[
'mon', 'apply_times', 'access_times',
'loan_times', 'access_rate', 'withdraw_rate'
])
thams_df = thams_df.sort_values(by=['mon'], ascending=[1])
return thams_df
def get_noRenew_user():
db = SqlContext('/python/db/dev_dw_db')
today = datetime.date(datetime.today())
time = today - dt.timedelta(days=2)
time_s = "'" + str(time) + "'"
sql = '''
select c.corporaterepresentname name,a.partyid,a.repaytime ,d.loannum
from
(
select distinct a.partyid,b.repaytime from dev_dw.f_loanagreement a
left join dev_dw.f_loanrepayschedule b
on a.id=b.idloanagreement
where loanstatus in ('D','O','R','E') and a.idproduct<>21
and to_char(b.repaytime,'yyyy-mm-dd')=''' + time_s + '''
and b.repaytime-b.duedate<15
and a.repaytime is not null
) a
left join dev_dw.f_loanapplyinfo b
on a.partyid=b.partyid and b.applytype not in ('repayCredit')
and b.applydate>=to_date(''' + time_s + '''，'yyyy-mm-dd')
left join dev_dw.dim_txnparty c
on a.partyid=c.partyid
left join
(
select partyid,count(distinct id) loannum from dev_dw.f_loanagreement
where loanstatus in ('D','O','R','E') and idproduct<>21
group by partyid
) d
on a.partyid=d.partyid
where b.partyid is null
'''
user_list = []
with session_scope(db) as session:
for row in iselect_rows_by_sql(session, sql, []):
phone_name = get_mobile_phone(row['partyid'])
row_list = list(row)
row_list[1] = phone_name
user_list.append(row_list)
user_df = pd.DataFrame(
user_list, columns=['username', 'phone', 'repaytime', 'loannum'])
user_df = user_df.rename(
columns={
'username': '******',
'phone': '用户电话',
'repaytime': '还款时间',
'loannum': '历史借款次数'
})
return user_df
def get_noWithdraw_user():
init_ti_srv_cfg('ti-daf')
db = SqlContext('/python/db/ac_cif_db')
today = datetime.date(datetime.today())
time = today - dt.timedelta(days=1)
time_s = "'" + str(time) + "'"
sql = '''
select a.personname,a.partyid,a.applydate,a.auditline,
case when c.partyid is null then '新用户' else '老用户' end as cate
from ac_lms_db.LoanApplyInfo a
left join ac_cif_db.LoanAgreement b
on a.applyno=b.applyid
left join
(
select distinct partyid from ac_cif_db.LoanAgreement
where loantime <''' + time_s + ''' and loanstatus in ('D','O','R','E') and idproduct<>21
) c
on a.partyid=c.partyid
where b.id is null and date_format(a.applydate, '%Y-%m-%d')=''' + time_s + ''' and a.status like '%A%'
and a.applytype<>'repayCredit'
'''
user_list = []
with session_scope(db) as session:
for row in iselect_rows_by_sql(session, sql, []):
phone_name = get_mobile_phone(row['partyid'])
row_list = list(row)
row_list[1] = phone_name
user_list.append(row_list)
user_df = pd.DataFrame(
user_list,
columns=['username', 'phone', 'applytime', 'auditline', 'usercate'])
user_df = user_df.rename(
columns={
'username': '******',
'phone': '用户电话',
'applytime': '申请时间',
'auditline': '审批额度',
'usercate': '用户类型'
})
return user_df
def get_daily_df():
yesterday = datetime.date(datetime.today()) - dt.timedelta(days=1)
origin_date = yesterday - dt.timedelta(days=6)
ytd_s = "'" + str(yesterday) + "'"
ord_s = "'" + str(origin_date) + "'"
sql_1 = '''
select a.mon mon,a.apply_times apply_times,b.acess_times access_times,round(b.acess_times/a.apply_times,4) access_rate,
c.loan_times loan_times,round(c.loan_times/b.acess_times,4) withdraw_rate ,c.amt loanamt
from
(
select to_char(applydate,'yyyy-mm-dd') mon,count(distinct id) apply_times
from dev_dw.f_loanapplyinfo
where applytype not in ('repayCredit')
group by to_char(applydate,'yyyy-mm-dd')
) a
left join
(
select to_char(applydate,'yyyy-mm-dd') mon,count(distinct id) acess_times
from dev_dw.f_loanapplyinfo
where status like '%A%' and applytype not in ('repayCredit')
group by to_char(applydate,'yyyy-mm-dd')
) b
on a.mon=b.mon
left join
(
select to_char(loantime,'yyyy-mm-dd') mon,count(distinct id) loan_times,sum(loanamt) amt
from dev_dw.f_loanagreement
where loanstatus in ('D','O','R','E') and idproduct<>21
group by to_char(loantime,'yyyy-mm-dd')
) c
on a.mon=c.mon
where a.mon >=''' + ord_s + ''' and a.mon<=''' + ytd_s + ''' order by mon'''
with session_scope(db_1) as session:
re_list = []
for row in iselect_rows_by_sql(session, sql_1, []):
re_list.append(list(row))
daily_df = pd.DataFrame(
re_list, columns=['日期', '申请笔数', '通过笔数', '通过率', '提现笔数', '提现率', '放款额'])
return daily_df
def get_collect_df():
today = datetime.date(datetime.today())
collection_list = []
for i in range(6):
time_b = today - dt.timedelta(days=(i + 1) * 7)
time_a = today - dt.timedelta(days=7 * i)
time_bs = "'" + str(time_b) + "'"
time_as = "'" + str(time_a) + "'"
time_bl = datetime.strftime(time_b, '%m-%d')
time_al = datetime.strftime(today - dt.timedelta(days=7 * i + 1),
'%m-%d')
time_str = time_bl + "&" + time_al
sql = '''
select yqfl,sum(je),count(distinct id) from
(
select id,duedate,REPAYTIME,bj_amt+lixi je,
case WHEN (overduedate BETWEEN 1 AND 3) THEN '1-3D'
WHEN (overduedate BETWEEN 4 AND 15) THEN '4-15D'
WHEN (overduedate BETWEEN 16 AND 30 ) THEN '16-30D'
WHEN (overduedate BETWEEN 31 AND 60) THEN 'M2'
WHEN (overduedate > 60) THEN 'M3+'
ELSE 'no_kwon' end yqfl
from
(
select a.id,
c.duedate,
c.REPAYTIME,
-(c.REPAIDPRINCIPAL) bj_amt,
-(c.REPAIDINTEREST) AS lixi,
trunc(c.REPAYTIME) - trunc(c.duedate) AS overduedate
FROM
dev_dw.F_LOANAGREEMENT a
left join dev_dw.f_loanrepayschedule c
on a.id=c.idloanagreement
where a.idproduct<>21
and c.REPAYTIME > trunc(c.duedate) + 1
ORDER BY c.REPAYTIME
)
)
where REPAYTIME>=to_date(''' + time_bs + ''','yyyy-mm-dd') and REPAYTIME<to_date(''' + time_as + ''','yyyy-mm-dd') group by yqfl'''
with session_scope(db_1) as session:
for row in iselect_rows_by_sql(session, sql, []):
collection_list.append([time_str] + list(row))
collection_df = pd.DataFrame(
collection_list,
columns=['timecircle', 'categroy', 'collectamt', 'collecttimes'])
day_list = collection_df['timecircle'].unique()
middle_df = pd.DataFrame(
{
'day': day_list,
'1-3D_times': np.nan,
'4-15D_times': np.nan,
'16-30D_times': np.nan,
'M2_times': np.nan,
'M3+_times': np.nan,
'1-3D_amt': np.nan,
'4-15D_amt': np.nan,
'16-30D_amt': np.nan,
'M2_amt': np.nan,
'M3+_amt': np.nan
},
columns=[
'day', '1-3D_times', '4-15D_times', '16-30D_times', 'M2_times',
'M3+_times', '1-3D_amt', '4-15D_amt', '16-30D_amt', 'M2_amt',
'M3+_amt'
])
for day, cate in zip(collection_df['timecircle'],
collection_df['categroy']):
collect_times = collection_df[(collection_df['timecircle'] == day) & (
collection_df['categroy'] == cate)]['collecttimes'].values[0]
collect_amt = \
collection_df[(collection_df['timecircle'] == day) & (collection_df['categroy'] == cate)]['collectamt'].values[
0]
middle_df.ix[middle_df['day'] == day, cate + '_times'] = collect_times
middle_df.ix[middle_df['day'] == day, cate + '_amt'] = collect_amt
middle_df = middle_df.fillna(0)
col = middle_df.columns[1:11]
days = middle_df['day']
end_collect_df = middle_df[col].astype(float)
end_collect_df.insert(0, 'day', days)
all_times = end_collect_df['1-3D_times'] + end_collect_df['4-15D_times'] + end_collect_df['16-30D_times'] + \
end_collect_df['M2_times'] + end_collect_df['M3+_times']
all_amt = end_collect_df['1-3D_amt'] + end_collect_df[
'4-15D_amt'] + end_collect_df['16-30D_amt'] + end_collect_df[
'M2_amt'] + end_collect_df['M3+_amt']
end_collect_df.insert(6, 'all_times', all_times)
end_collect_df.insert(12, 'all_amt', all_amt)
end_collect_df = end_collect_df.sort_values(by='day', ascending=1)
return end_collect_df
def get_sameterm_df():
today = datetime.date(datetime.today())
riqi = today.day
this_mon = pd.Period(today, freq='M')
# 获取当月最后一天数据
lastday = getFirstDay_LastDay(year=this_mon.year, month=this_mon.month)
overdue_mx = []
loan_amt = []
for i in range(12):
month_time = this_mon - i
last_month = this_mon - i - 1
last_month_first_s = "'" + str(last_month) + "-01" + "'"
# print (last_month_first_s)
last_month_last_s = "'" + str(month_time) + "-01" + "'"
# print (last_month_last_s)
# 判断今天是否是当月最后一天及当年是否是闰年
if lastday.day == riqi:
month_time_s = "'" + str(
getFirstDay_LastDay(year=month_time.year,
month=month_time.month)) + "'"
elif isYear(month_time.year
) == 'yes' and month_time.month == 2 and riqi > 29:
month_time_s = "'" + str(month_time) + "-" + '29' + "'"
elif isYear(month_time.year
) == 'no' and month_time.month == 2 and riqi > 28:
month_time_s = "'" + str(month_time) + "-" + '28' + "'"
else:
month_time_s = "'" + str(month_time) + "-" + str(riqi) + "'"
sql_1 = '''
select to_char(to_date(''' + last_month_first_s + ''','yyyy-mm-dd'),'yyyy-mm') as time,
sum(b.dueprincipal) from dev_dw.f_loanagreement a
left join dev_dw.f_loanrepayschedule b
on a.id=b.idloanagreement
where a.loanstatus in ('D','E','R','O')
and a.idproduct in (6,12)
and a.loantime>=to_date(''' + last_month_first_s + ''','yyyy-mm-dd')
and a.loantime<to_date(''' + last_month_last_s + ''','yyyy-mm-dd')
and a.duedate<to_date(''' + month_time_s + ''','yyyy-mm-dd')
and (
(a.repaytime is null and to_date(''' + month_time_s + ''','yyyy-mm-dd')-a.duedate>=4)
or
(a.repaytime <to_date(''' + month_time_s + ''','yyyy-mm-dd') and a.repaytime-a.duedate>=4)
or
(a.repaytime >to_date(''' + month_time_s + ''','yyyy-mm-dd') and to_date(''' + month_time_s + ''','yyyy-mm-dd')-a.duedate>=4)
)
'''
with session_scope(db_1) as session:
for row in iselect_rows_by_sql(session, sql_1, []):
overdue_mx.append(row)
sql_2 = '''
select to_char(to_date(''' + last_month_first_s + ''','yyyy-mm-dd'),'yyyy-mm') as time,
sum(loanamt)
from dev_dw.f_loanagreement
where loanstatus in ('D','E','R','O')
and idproduct in (6,12)
and loantime>=to_date(''' + last_month_first_s + ''','yyyy-mm-dd')
and loantime<to_date(''' + last_month_last_s + ''','yyyy-mm-dd')
'''
with session_scope(db_1) as session:
for row1 in iselect_rows_by_sql(session, sql_2, []):
loan_amt.append(row1)
overdue_mx = pd.DataFrame(overdue_mx, columns=['日期', 'D4逾期金额'])
loan_amt = pd.DataFrame(loan_amt, columns=['日期', '放款金额'])
final_df = pd.merge(overdue_mx, loan_amt, on='日期', how='outer')
return final_df
def get_loan_df():
yesterday = datetime.date(datetime.today()) - dt.timedelta(days=1)
yue_list = []
for i in range(5):
time = yesterday - dt.timedelta(days=i * 7)
time_s = "'" + str(time) + "'"
'''
this_mon=str(pd.Period(today,freq='M')-i)
next_mon="'"+str(pd.Period(today,freq='M')+1-i)+'-01'+"'"
'''
sql_1 = '''
select case when a.idproduct in (10,13) then '3q'
when a.idproduct in (11,14) then '6q'
when a.idproduct in (17,18) then '12q'
else 'dq' end categroy ,sum(b.dueprincipal)
from dev_dw.f_loanagreement a
left join dev_dw.f_loanrepayschedule b
on a.id=b.idloanagreement
where loantime-1<=to_date(''' + time_s + ''' ,'yyyy-mm-dd') and
(b.repaytime-1>to_date(''' + time_s + ''','yyyy-mm-dd') or b.repaytime is null) and a.idproduct<>21
and ( b.repaytime-b.duedate<33 or (b.repaytime is null and to_date(''' + time_s + ''','yyyy-mm-dd')-b.duedate<33))
group by
case when a.idproduct in (10,13) then '3q'
when a.idproduct in (11,14) then '6q'
when a.idproduct in (17,18) then '12q'
else 'dq' end
'''
with session_scope(db_1) as session:
for row in iselect_rows_by_sql(session, sql_1, []):
yue_list.append([time, row[0], row[1]])
yue_df = pd.DataFrame(yue_list, columns=['mon', 'categroy', 'yue'])
yue_df = pd.pivot_table(yue_df,
values='yue',
index='categroy',
columns='mon',
aggfunc=np.sum).reset_index()
# print(yue_df)
loan_list = []
for i in range(5):
time_b = yesterday - dt.timedelta(days=(i + 1) * 7)
time_a = yesterday - dt.timedelta(days=i * 7)
time_b_s = datetime.strftime(time_b, '%m-%d')
time_a_s = datetime.strftime(yesterday - dt.timedelta(days=i * 7 + 1),
'%m-%d')
time_circle = time_b_s + '&' + time_a_s
time_bs = "'" + str(time_b) + "'"
time_as = "'" + str(time_a) + "'"
sql_2 = '''
select case when a.idproduct in (10,13) then '3q'
when a.idproduct in (11,14) then '6q'
when a.idproduct in (17,18) then '12q'
else 'dq' end categroy ,sum(loanamt) amt
from dev_dw.f_loanagreement a
where loanstatus in ('D','O','R','E') and loantime-1<to_date(''' + time_as + ''','yyyy-mm-dd')
and loantime-1>=to_date(''' + time_bs + ''','yyyy-mm-dd') and a.idproduct<>21
group by
case when a.idproduct in (10,13) then '3q'
when a.idproduct in (11,14) then '6q'
when a.idproduct in (17,18) then '12q'
else 'dq' end
'''
with session_scope(db_1) as session:
for row in iselect_rows_by_sql(session, sql_2, []):
ls = [time_circle] + list(row)
loan_list.append(ls)
loan_df = pd.DataFrame(loan_list, columns=['day', 'categroy', 'amt'])
loan_df = pd.pivot_table(loan_df,
values='amt',
index='categroy',
columns='day',
aggfunc=np.sum).reset_index()
return yue_df, loan_df
def get_end_over_df():
today = datetime.date(datetime.today())
num = 11
this_mon = pd.Period(today, freq='M')
first_mon = this_mon - num
first_mon_s = "'" + str(first_mon) + '-01' + "'"
sql_1 = '''
select aa.mon,round(aa.amt/ee.amt,4) D4,round(bb.amt/ee.amt,4) D15,
round(cc.amt/ee.amt,4) M2,round(dd.amt/ee.amt,4) M2p,
round(ff.amt/ee.amt,4) M2_pred
from
(
select to_char(loantime,'yyyy-mm') mon,sum(dueprincipal) amt
from dev_dw.f_loanagreement a
left join dev_dw.f_loanrepayschedule b
on a.id=b.idloanagreement
where loanstatus in ('D','O','R','E') and loantime>=to_date(''' + first_mon_s + ''','yyyy-mm-dd')
and( b.repaytime-b.duedate >4 or (b.repaytime is null and sysdate-b.duedate>4)) and a.idproduct<>21
group by to_char(loantime,'yyyy-mm')
) aa
left join
(
select to_char(loantime,'yyyy-mm') mon,sum(dueprincipal) amt from dev_dw.f_loanagreement a
left join dev_dw.f_loanrepayschedule b
on a.id=b.idloanagreement
where loanstatus in ('D','O','R','E') and loantime>=to_date(''' + first_mon_s + ''','yyyy-mm-dd')
and( b.repaytime-b.duedate>15 or (b.repaytime is null and sysdate-b.duedate>15)) and a.idproduct<>21
group by to_char(loantime,'yyyy-mm')
) bb
on aa.mon=bb.mon
left join
(
select to_char(loantime,'yyyy-mm') mon,sum(dueprincipal) amt from dev_dw.f_loanagreement a
left join dev_dw.f_loanrepayschedule b
on a.id=b.idloanagreement
where loanstatus in ('D','O','R','E') and loantime>=to_date(''' + first_mon_s + ''','yyyy-mm-dd')
and( b.repaytime-b.duedate>33 or (b.repaytime is null and sysdate-b.duedate>33)) and a.idproduct<>21
group by to_char(loantime,'yyyy-mm')
) cc
on aa.mon=cc.mon
left join
(
select to_char(loantime,'yyyy-mm') mon,sum(dueprincipal) amt from dev_dw.f_loanagreement a
left join dev_dw.f_loanrepayschedule b
on a.id=b.idloanagreement
where loanstatus in ('D','O','R','E') and loantime>=to_date(''' + first_mon_s + ''','yyyy-mm-dd')
and( b.repaytime-b.duedate>60 or (b.repaytime is null and sysdate-b.duedate>60)) and a.idproduct<>21
group by to_char(loantime,'yyyy-mm')
) dd
on aa.mon=dd.mon
left join
(
select to_char(loantime,'yyyy-mm') mon,sum(loanamt) amt
from dev_dw.f_loanagreement
where idproduct<>21
and loanstatus in ('D','O','R','E')
group by to_char(loantime,'yyyy-mm')
) ee
on aa.mon=ee.mon
left join
(
select to_char(loantime,'yyyy-mm') mon,sum(dueprincipal) amt from dev_dw.f_loanagreement a
left join dev_dw.f_loanrepayschedule b
on a.id=b.idloanagreement
where loanstatus in ('D','O','R','E') and loantime>=to_date(''' + first_mon_s + ''','yyyy-mm-dd')
and( b.repaytime-b.duedate>33 or (b.repaytime is null and sysdate-b.duedate>15)) and a.idproduct<>21
group by to_char(loantime,'yyyy-mm')
) ff
on aa.mon=ff.mon
order by aa.mon
'''
with session_scope(db_1) as session:
over_list = []
for row in iselect_rows_by_sql(session, sql_1, []):
over_list.append(list(row))
over_df_a = pd.DataFrame(
over_list, columns=['mon', 'D4', 'D15', 'M2', 'M2+', 'M2_pred'])
over_df_a = over_df_a.fillna(0)
# -----------------------------------------
'''
over_table=pd.pivot_table(over_df,values='loanamt',index=['mon'],columns=['categroy'],aggfunc=np.sum,margins=1,fill_value=0)
over_table=over_table.reset_index()
columns_list=['D4','D15','M2','M2+']
for item in columns_list:
over_table[item+'_rate']=over_table[item]/over_table['All']
over_table_num=len(over_table['mon'])
end_over_df=over_table.ix[:over_table_num-2,['mon','D4_rate','D15_rate','M2_rate','M2+_rate']]
sum_amt=over_df['loanamt'].groupby(over_df['mon']).sum()
sum_df=sum_amt.reset_index()
'''
sql_2 = '''
select to_char(loantime,'yyyy-mm') mon,sum(loanamt) amt,count(distinct id) times from dev_dw.f_loanagreement
where loanstatus in ('D','O','R','E') and loantime>=to_date(''' + first_mon_s + ''','yyyy-mm-dd') and idproduct<>21
group by to_char(loantime,'yyyy-mm')
'''
with session_scope(db_1) as session:
loan_list = []
for row in iselect_rows_by_sql(session, sql_2, []):
loan_list.append(list(row))
loan_df_a = pd.DataFrame(loan_list,
columns=['mon', 'loanamt', 'loantimes'])
loan_df_a['loanamt'] = loan_df_a['loanamt'] / 10000
# ----------------------------------------------
yue_list_a = []
for i in range(num + 1):
if i == 0:
this_mon = str(pd.Period(today, freq='M') - i)
next_mon = "'" + str(today) + "'"
else:
this_mon = str(pd.Period(today, freq='M') - i)
next_mon = "'" + str(pd.Period(today, freq='M') + 1 -
i) + '-01' + "'"
sql_3 = '''
select sum(b.dueprincipal) from dev_dw.f_loanagreement a
left join dev_dw.f_loanrepayschedule b
on a.id=b.idloanagreement
where loantime<to_date(''' + next_mon + ''' ,'yyyy-mm-dd') and (b.repaytime>=to_date(''' + next_mon + ''','yyyy-mm-dd') or b.repaytime is null)
and a.idproduct<>21 and ( b.repaytime-b.duedate<33 or (b.repaytime is null and to_date(''' + next_mon + ''','yyyy-mm-dd')-b.duedate<33+1))
'''
with session_scope(db_1) as session:
for row in iselect_rows_by_sql(session, sql_3, []):
yue_list_a.append([this_mon, row[0]])
yue_df_a = pd.DataFrame(yue_list_a, columns=['mon', 'yue'])
yue_df_a['yue'] = yue_df_a['yue'] / 10000
all_result_df_a = pd.merge(loan_df_a, yue_df_a, on='mon')
all_result_df_a = pd.merge(all_result_df_a,
over_df_a,
on='mon',
how='outer')
end_over_df_a = all_result_df_a.sort_values(by=['mon'], ascending=[1])
end_over_df_a = end_over_df_a.fillna(0)
return end_over_df_a
def get_end_week_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) + "'"
re_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_1 = '''
select sum(apply_times) apt,sum(access_times) pt,round(sum(access_times)/sum(apply_times),4) pr,
sum(loan_times) lt,round(sum(loan_times)/sum(access_times),4) wr,sum(loanamt) amt from
(
select a.mon mon,a.apply_times apply_times,b.acess_times access_times,
c.loan_times loan_times,c.amt loanamt
from
(
select to_char(applydate,'yyyy-mm-dd') mon,count(distinct id) apply_times
from dev_dw.f_loanapplyinfo
where applytype not in ('repayCredit')
group by to_char(applydate,'yyyy-mm-dd')
) a
left join
(
select to_char(applydate,'yyyy-mm-dd') mon,count(distinct id) acess_times
from dev_dw.f_loanapplyinfo
where status like '%A%' and applytype not in ('repayCredit')
group by to_char(applydate,'yyyy-mm-dd')
) b
on a.mon=b.mon
left join
(
select to_char(loantime,'yyyy-mm-dd') mon,count(distinct id) loan_times,sum(loanamt) amt
from dev_dw.f_loanagreement
where loanstatus in ('D','O','R','E') and idproduct<>21
group by to_char(loantime,'yyyy-mm-dd')
) c
on a.mon=c.mon
where a.mon >=''' + time_before + ''' and a.mon<''' + time_after + '''
order by mon
)'''
with session_scope(db_1) as session:
for row in iselect_rows_by_sql(session, sql_1, []):
turn_list = [string] + list(row)
re_list.append(turn_list)
end_week_df = pd.DataFrame(
re_list, columns=['周期', '申请笔数', '通过笔数', '通过率', '提现笔数', '提现率', '放款额'])
end_week_df = end_week_df.sort_values(by=['周期'], ascending=[1])
return end_week_df
def get_end_collect_df():
today = datetime.date(datetime.today())
yesterday = datetime.date(datetime.today()) - dt.timedelta(days=1)
collection_list = []
for i in range(6):
time_b = today - dt.timedelta(days=(i + 1) * 7)
time_a = today - dt.timedelta(days=7 * i)
time_bs = "'" + str(time_b) + "'"
time_as = "'" + str(time_a) + "'"
time_a_turn = yesterday - dt.timedelta(days=7 * i)
time_bl = datetime.strftime(time_b, '%m-%d')
time_al = datetime.strftime(time_a_turn, '%m-%d')
time_str = time_bl + "&" + time_al
sql = '''
select yqfl,sum(je),count(*) from
(
select duedate,rcrq,hkrq,amt+lixi+faxi je,agent,
case WHEN (overduedate BETWEEN 4 AND 15) THEN '4-15D'
WHEN (overduedate BETWEEN 16 AND 30 ) THEN '16-30D'
WHEN (overduedate BETWEEN 31 AND 60) THEN 'M2'
WHEN (overduedate > 60) THEN 'M3+'
ELSE 'no_kwon' end yqfl
from
(
select c.duedate,
c.duedate + 4 rcrq,
c.REPAYTIME AS hkrq,
c.dueprincipal amt,
-(INTEREST + PAYFEE) AS lixi,
OVERDUEFEE + PENALTY AS faxi,
CASE
WHEN (agentpartyid = '1014816000030982') THEN 'A1'
WHEN (agentpartyid = '1014816000005443') THEN 'A2'
WHEN (agentpartyid = '1014816000004520') THEN 'A3'
ELSE 'A0'
END AS agent,
CASE
WHEN (c.REPAYTIME IS NULL) THEN trunc(sysdate) - 1 - trunc(c.duedate)
ELSE trunc(c.REPAYTIME) - trunc(c.duedate)
END AS overduedate
FROM
dev_dw.F_LOANAGREEMENT a
LEFT JOIN dev_dw.F_LOANACCOUNT b
ON a.id = b.idloanagreement
left join dev_dw.f_loanrepayschedule c
on a.id=c.idloanagreement
where a.idproduct<>21
ORDER BY c.REPAYTIME
)
where overduedate>=4
)
where hkrq>=to_date(''' + time_bs + ''','yyyy-mm-dd') and hkrq<to_date(''' + time_as + ''','yyyy-mm-dd') group by yqfl'''
with session_scope(db_1) as session:
for row in iselect_rows_by_sql(session, sql, []):
collection_list.append([time_str] + list(row))
collection_df = pd.DataFrame(
collection_list,
columns=['timecircle', 'categroy', 'collectamt', 'collecttimes'])
day_list = collection_df['timecircle'].unique()
middle_df = pd.DataFrame(
{
'day': day_list,
'4-15D_times': np.nan,
'16-30D_times': np.nan,
'M2_times': np.nan,
'M3+_times': np.nan,
'4-15D_amt': np.nan,
'16-30D_amt': np.nan,
'M2_amt': np.nan,
'M3+_amt': np.nan
},
columns=[
'day', '4-15D_times', '16-30D_times', 'M2_times', 'M3+_times',
'4-15D_amt', '16-30D_amt', 'M2_amt', 'M3+_amt'
])
for day, cate in zip(collection_df['timecircle'],
collection_df['categroy']):
collect_times = collection_df[(collection_df['timecircle'] == day) & (
collection_df['categroy'] == cate)]['collecttimes'].values[0]
collect_amt = \
collection_df[(collection_df['timecircle'] == day) & (collection_df['categroy'] == cate)]['collectamt'].values[
0]
middle_df.ix[middle_df['day'] == day, cate + '_times'] = collect_times
middle_df.ix[middle_df['day'] == day, cate + '_amt'] = collect_amt
middle_df = middle_df.fillna(0)
col = middle_df.columns[1:9]
days = middle_df['day']
end_collect_df = middle_df[col].astype(float)
end_collect_df.insert(0, 'day', days)
all_times = end_collect_df['4-15D_times'] + end_collect_df['16-30D_times'] + end_collect_df['M2_times'] + \
end_collect_df['M3+_times']
all_amt = end_collect_df['4-15D_amt'] + end_collect_df[
'16-30D_amt'] + end_collect_df['M2_amt'] + end_collect_df['M3+_amt']
end_collect_df.insert(5, 'all_times', all_times)
end_collect_df.insert(10, 'all_amt', all_amt)
end_collect_df = end_collect_df.sort_values(by='day', ascending=1)
return end_collect_df
def get_sameterm_df():
# 获取月份的最后一天
def getFirstDay_LastDay(year=None, month=None):
"""
:param year: 年份，默认是本年，可传int或str类型
:param month: 月份，默认是本月，可传int或str类型
:return: firstDay: 当月的第一天，datetime.date类型
lastDay: 当月的最后一天，datetime.date类型
"""
if year:
year = int(year)
else:
year = datetime.date(datetime.today()).year
if month:
month = int(month)
else:
month = datetime.date(datetime.today()).month
# 获取当月第一天的星期和当月的总天数
firstDayWeekDay, monthRange = calendar.monthrange(year, month)
# 获取当月的第一天
firstDay = dt.date(year=year, month=month, day=1)
lastDay = dt.date(year=year, month=month, day=monthRange)
return lastDay
# 判断是否是闰年
def isYear(year):
if (year % 4 == 0) & (year % 100 != 0):
return 'yes'
elif year % 400 == 0:
return 'yes'
else:
return 'no'
today = datetime.date(datetime.today())
riqi = today.day
this_mon = pd.Period(today, freq='M')
# 获取当月最后一天数据
lastday = getFirstDay_LastDay(year=this_mon.year, month=this_mon.month)
overdue_mx = []
loan_amt = []
for i in range(12):
month_time = this_mon - i
last_month = this_mon - i - 1
last_month_first_s = "'" + str(last_month) + "-01" + "'"
# print (last_month_first_s)
last_month_last_s = "'" + str(month_time) + "-01" + "'"
# print (last_month_last_s)
# 判断今天是否是当月最后一天
if lastday.day == riqi:
month_time_s = "'" + str(
getFirstDay_LastDay(year=month_time.year,
month=month_time.month)) + "'"
elif isYear(month_time.year
) == 'yes' and month_time.month == 2 and riqi > 29:
month_time_s = "'" + str(month_time) + "-" + '29' + "'"
elif isYear(month_time.year
) == 'no' and month_time.month == 2 and riqi > 28:
month_time_s = "'" + str(month_time) + "-" + '28' + "'"
else:
month_time_s = "'" + str(month_time) + "-" + str(riqi) + "'"
sql_1 = '''
select to_char(to_date(''' + last_month_first_s + ''','yyyy-mm-dd'),'yyyy-mm') as time,
sum(b.dueprincipal) from dev_dw.f_loanagreement a
left join dev_dw.f_loanrepayschedule b
on a.id=b.idloanagreement
where a.loanstatus in ('D','E','R','O')
and a.idproduct in (6,12)
and a.loantime>=to_date(''' + last_month_first_s + ''','yyyy-mm-dd')
and a.loantime<to_date(''' + last_month_last_s + ''','yyyy-mm-dd')
and a.duedate<to_date(''' + month_time_s + ''','yyyy-mm-dd')
and (
(a.repaytime is null and to_date(''' + month_time_s + ''','yyyy-mm-dd')-a.duedate>=4)
or
(a.repaytime <to_date(''' + month_time_s + ''','yyyy-mm-dd') and a.repaytime-a.duedate>=4)
or
(a.repaytime >to_date(''' + month_time_s + ''','yyyy-mm-dd') and to_date(''' + month_time_s + ''','yyyy-mm-dd')-a.duedate>=4)
)
'''
with session_scope(db_1) as session:
for row in iselect_rows_by_sql(session, sql_1, []):
overdue_mx.append(row)
sql_2 = '''
select to_char(to_date(''' + last_month_first_s + ''','yyyy-mm-dd'),'yyyy-mm') as time,
sum(loanamt)
from dev_dw.f_loanagreement
where loanstatus in ('D','E','R','O')
and idproduct in (6,12)
and loantime>=to_date(''' + last_month_first_s + ''','yyyy-mm-dd')
and loantime<to_date(''' + last_month_last_s + ''','yyyy-mm-dd')
'''
with session_scope(db_1) as session:
for row1 in iselect_rows_by_sql(session, sql_2, []):
loan_amt.append(row1)
overdue_mx = pd.DataFrame(overdue_mx, columns=['日期', 'D4逾期金额'])
loan_amt = pd.DataFrame(loan_amt, columns=['日期', '放款金额'])
final_df = pd.merge(overdue_mx, loan_amt, on='日期', how='outer')
return final_df