def userSex():
#分性别的注册用户
#男
sql = """
select count(sex) 'num' from user where sex = '1';
"""
data = pysql.dbInfo(sql)
data = data.fillna(0)
male = data.values[0][0]
#女
sql = """
select count(sex) 'num' from user where sex = '2';
"""
data = pysql.dbInfo(sql)
data = data.fillna(0)
female = data.values[0][0]
sql = """ insert into dayAddApi_usersexall(male,female,createDate) values (%s,%s,%s) """
dset = [(male, female, str(datetime.datetime.now())[:10])]
status = pysql.insertData(sql, dset)
log.log('用户性别（总）更新状态-{}！'.format(status), 'info')
#日增数据
timeList = timeScale()
sql = 'select distinct createDate from dayAddApi_usersex'
tmRest = pysql.dbInfoLocal(sql)
tmRest = tmRest.fillna(0)
tmwait = []
if not tmRest.empty:
tmwait = [str(x)[:10] for x in tmRest['createDate']]
for i in range(len(timeList) - 1):
stTime = timeList[i]
edTime = timeList[i + 1]
if stTime in tmwait:
continue
print '性别' + stTime
sql = """
select count(*) from user where date_created > '{}' and date_created < '{}' and sex = '1'
""".format(stTime, edTime)
data = pysql.dbInfo(sql)
data = data.fillna(0)
male = data.values[0][0]
sql = """
select count(*) from user where date_created > '{}' and date_created < '{}' and sex = '2'
""".format(stTime, edTime)
data = pysql.dbInfo(sql)
data = data.fillna(0)
female = data.values[0][0]
sql = """ insert into dayAddApi_usersex(male,female,createDate) values (%s,%s,%s) """
dset = [(male, female, stTime)]
status = pysql.insertData(sql, dset)
log.log('用户性别（日）更新状态-{}！({})'.format(status, stTime), 'info')
def userAge():
#分年龄的注册用户
sql = """
select age,count(age) 'num' from user where age <> 0 group by age;
"""
data = pysql.dbInfo(sql)
age_label = config.age_label
data['age'] = pd.cut(data['age'],
age_label['point'],
labels=age_label['label'])
data = pd.pivot_table(data, index=["age"], values=["num"], aggfunc='sum')
data['age'] = data.index
sql = """ insert into dayAddApi_userageall(age1,age2,age3,age4,age5,createDate) values (%s,%s,%s,%s,%s,%s) """
dset = [(data['num'][0], data['num'][1], data['num'][2], data['num'][3],
data['num'][4], str(datetime.datetime.now())[:10])]
status = pysql.insertData(sql, dset)
log.log('用户年龄（总）更新状态-{}！'.format(status), 'info')
timeList = timeScale()
sql = 'select distinct createDate from dayAddApi_userage'
tmRest = pysql.dbInfoLocal(sql)
tmRest = tmRest.fillna(0)
tmwait = []
if not tmRest.empty:
tmwait = [str(x)[:10] for x in tmRest['createDate']]
for i in range(len(timeList) - 1):
stTime = timeList[i]
edTime = timeList[i + 1]
if stTime in tmwait:
continue
print '年龄' + stTime
sql = """
select age,count(age) 'num' from user where age <> 0 and date_created > '{}' and date_created < '{}' group by age;
""".format(stTime, edTime)
data = pysql.dbInfo(sql)
age_label = config.age_label
data['age'] = pd.cut(data['age'],
age_label['point'],
labels=age_label['label'])
data = pd.pivot_table(data,
index=["age"],
values=["num"],
aggfunc='sum')
data['age'] = data.index
sql = """ insert into dayAddApi_userage(age1,age2,age3,age4,age5,createDate) values (%s,%s,%s,%s,%s,%s) """
dset = [(data['num'][0], data['num'][1], data['num'][2],
data['num'][3], data['num'][4], stTime)]
status = pysql.insertData(sql, dset)
log.log('用户年龄（日）更新状态-{}！({})'.format(status, stTime), 'info')
def dashbook():
dashBook = {}
sql = """select avg(a.repayMoney) 'avgMoney' from loan_repaying a left join loan b
on a.loanId = b.id
where DateDiff(a.createdTime,now())=-1"""
data = pysql.dbInfo(sql)
data = data.fillna(0)
avgMoney = round(data.values[0][0],2)
sql = """
select avg(b.termNum) 'avgTermNum' from loan_repaying a left join loan b
on a.loanId = b.id
where DateDiff(a.createdTime,now())=-1
"""
data = pysql.dbInfo(sql)
data = data.fillna(0)
avgTermNum = round(data.values[0][0],2)
sql = """
select avg(b.repayMoney - b.payMoney) 'avgServiceMoney' from loan_repaying a left join loan b
on a.loanId = b.id
where DateDiff(a.createdTime,now())=-1
"""
data = pysql.dbInfo(sql)
data = data.fillna(0)
avgServiceMoney = round(data.values[0][0],2)
sql = """ insert into dayAddApi_indexdash(avgTermNum,avgMoney,avgServiceMoney,createDate) values (%s,%s,%s,%s) """
dset = [(avgTermNum,avgMoney,avgServiceMoney,str(datetime.datetime.now()-datetime.timedelta(days=1))[:10])]
status = pysql.insertData(sql,dset)
log.log('仪表盘数据更新状态-{}！'.format(status),'info')
def paid():
timeList = timeScale()
sql = 'select distinct createDate from dayAddApi_flowpaidmoney'
tmRest = pysql.dbInfoLocal(sql)
tmRest = tmRest.fillna(0)
tmwait = []
if not tmRest.empty:
tmwait = [str(x)[:10] for x in tmRest['createDate']]
for i in range(len(timeList) - 1):
stTime = timeList[i]
edTime = timeList[i + 1]
if stTime in tmwait:
continue
#还款金额
print '还款金额数据更新：' + stTime + '~' + edTime
sql = """
select sum(repayMoney)
from loan_repaying
where compatibleStatus <> 'CANCEL' and productId != 1001
and repaidTime >= '{}' and repaidTime < '{}';
""".format(stTime, edTime)
data = pysql.dbInfo(sql)
data = data.fillna(0)
money = data.values[0][0]
sql = """ insert into dayAddApi_flowpaidmoney(paidMoney, createDate) values (%s, %s) """
dset = [(money, stTime)]
status = pysql.insertData(sql, dset)
log.log('每日还款金额更新状态-{}! ({})'.format(status, stTime), 'info')
def hopperHead():
sql = """select count(*) from user;"""
data = pysql.dbInfo(sql)
registerNum = data.values[0][0]
sql = """select count(distinct user_id) from ci_cash_apply_info;"""
data = pysql.dbInfo(sql)
applyNum = data.values[0][0]
sql = """select count(DISTINCT user_id) from ci_cash_apply_info where status in ('FA_SUCCESS','SUCCESS');"""
data = pysql.dbInfo(sql)
passNum = data.values[0][0]
sql = """select count(*) from batch_acc_mgnt_account_info;"""
data = pysql.dbInfo(sql)
loanNum = data.values[0][0]
sql = """select count(*) from batch_acc_mgnt_account_info where loan_times >=2;"""
data = pysql.dbInfo(sql)
reloanNum = data.values[0][0]
#数据插入
sql = """ insert into dayAddApi_indexhopper(register,applys,passs,loan,reloan,createDate) values (%s,%s,%s,%s,%s,%s) """
dset = [(registerNum,applyNum,passNum,loanNum,reloanNum,str(datetime.datetime.now()-datetime.timedelta(days=1))[:10])]
status = pysql.insertData(sql,dset)
log.log('漏斗数据更新状态-{}'.format(status),'info')
#基本总量情况
sumUser = registerNum
activeUser = loanNum
sql = """select count(*) from loan where status=6;"""
data = pysql.dbInfo(sql)
tradeNum = data.values[0][0]
sql = """select sum(lendMoney) from loan where status=6;"""
data = pysql.dbInfo(sql)
tradeMoney = int(data.values[0][0])
#数据插入
sql = """ insert into dayAddApi_indexhead(sumUser,activeUser,tradeNum,tradeMoney,createDate) values (%s,%s,%s,%s,%s) """
dset = [(sumUser,activeUser,tradeNum,tradeMoney,str(datetime.datetime.now()-datetime.timedelta(days=1))[:10])]
status = pysql.insertData(sql,dset)
log.log('首页标题数据更新状态-{}！'.format(status),'info')
def userNum():
timeList = timeScale()
sql = 'select distinct createDate from dayAddApi_userincrease'
tmRest = pysql.dbInfoLocal(sql)
tmRest = tmRest.fillna(0)
tmwait = []
if not tmRest.empty:
tmwait = [str(x)[:10] for x in tmRest['createDate']]
for i in range(len(timeList)-1):
stTime = timeList[i]
edTime = timeList[i+1]
if stTime in tmwait:
continue
#人数增长
print '用户增长数据更新：' + stTime + '~' + edTime
#注册
sql = """
select sum(1) from user where date_created > '{}' and date_created < '{}'
""".format(stTime,edTime)
data = pysql.dbInfo(sql)
data = data.fillna(0)
registerNum = data.values[0][0]
#申请（新老）
sql = """
select count(distinct user_id) from ci_cash_apply_info
where create_time > '{}' and create_time < '{}'
and user_id not in (select distinct user_id from ci_cash_apply_info where create_time < '{}');
""".format(stTime,edTime,stTime)
data = pysql.dbInfo(sql)
data = data.fillna(0)
newApplyNum = data.values[0][0]
sql = """
select count(distinct user_id) from ci_cash_apply_info
where create_time > '{}' and create_time < '{}'
and user_id in (select distinct user_id from ci_cash_apply_info where create_time < '{}');
""".format(stTime,edTime,stTime)
data = pysql.dbInfo(sql)
data = data.fillna(0)
oldApplyNum = data.values[0][0]
#授信
sql = """
select count(distinct user_id) from ci_cash_apply_info where audit_date > '{}' and audit_date < '{}' and status in ('SUCCESS')
""".format(stTime,edTime)
data = pysql.dbInfo(sql)
data = data.fillna(0)
allowNum = data.values[0][0]
#数据插入
sql = """ insert into dayAddApi_userincrease(register,allow,newApply,oldApply,createDate) values (%s,%s,%s,%s,%s) """
dset = [(registerNum,allowNum,newApplyNum,oldApplyNum,stTime)]
status = pysql.insertData(sql,dset)
log.log('用户增长数据更新状态{}({})！'.format(status,stTime),'info')
def delayDay():
#逾期情况 not in (3,4,5,6,1001)
sql = """
select DATE_FORMAT(b.termDate,'%Y-%m-%d') 'date',sum(b.repayMoney) 'allMoney' from loan a,loan_repaying b
where a.id=b.loanId and a.status=6 and b.compatibleStatus not in ('CANCEL') and b.productId not in (3,4,5,6,1001)
and b.termDate < DATE_FORMAT(now(),'%Y-%m-%d')
GROUP BY DATE_FORMAT(b.termDate,'%Y-%m-%d');
"""
alldata = pysql.dbInfo(sql)
delayPoint = [0, 3, 7, 10, 20, 30, 60, 90]
pp = []
for day in delayPoint:
sql = """
select DATE_FORMAT(c.termDate,'%Y-%m-%d') 'date',sum(c.repayMoney) 'payMoney' from (
select a.payMoney,b.* from loan a,loan_repaying b
where a.id=b.loanId and a.status=6 and b.compatibleStatus not in ('CANCEL') and b.productId not in (3,4,5,6,1001)
and b.termDate < DATE_FORMAT(now(),'%Y-%m-%d')
HAVING if(b.repaidTime is NULL,TO_DAYS(now()) - TO_DAYS(b.termDate),TO_DAYS(b.repaidTime) - TO_DAYS(b.termDate)) <= {}) c
GROUP BY DATE_FORMAT(c.termDate,'%Y-%m-%d');
""".format(day)
plan = pysql.dbInfo(sql)
repay = pd.merge(alldata, plan)
pp.append(
pd.Series([
round(x * 100, 2)
for x in (repay['allMoney'] - repay['payMoney']) /
repay['allMoney']
],
index=repay['date']))
pt = pd.concat(pp, axis=1, join_axes=[pp[0].index])
pt.columns = [
'首逾率', '逾期率3+', '逾期率7+', '逾期率10+', '逾期率20+', '逾期率M1', '逾期率M2', '逾期率M3'
]
pt = pt.fillna(0)
pt['times'] = list(pt.index)
s0 = list(pt['首逾率'])
s3 = list(pt['逾期率3+'])
s7 = list(pt['逾期率7+'])
s10 = list(pt['逾期率10+'])
s20 = list(pt['逾期率20+'])
sM1 = list(pt['逾期率M1'])
sM2 = list(pt['逾期率M2'])
sM3 = list(pt['逾期率M3'])
stt = list(pt['times'])
sql = "delete from dayAddApi_aeyedelayrate"
status = pysql.deletetData(sql)
log.log(u'逾期数据删除状态-{}！'.format(status), 'info')
sql = """ insert into dayAddApi_aeyedelayrate(delayRate0,delayRate3,delayRate7,delayRate10,delayRate20,delayRateM1,delayRateM2,delayRateM3,createDate) values (%s,%s,%s,%s,%s,%s,%s,%s,%s) """
dset = zip(s0, s3, s7, s10, s20, sM1, sM2, sM3, stt)
status = pysql.insertData(sql, dset)
log.log(u'逾期数据更新状态-{}！'.format(status), 'info')
def loanNO():
timeList = timeScale()
sql = 'select distinct createDate from dayAddApi_flowloanmoneyno'
tmRest = pysql.dbInfoLocal(sql)
tmRest = tmRest.fillna(0)
tmwait = []
if not tmRest.empty:
tmwait = [str(x)[:10] for x in tmRest['createDate']]
for i in range(len(timeList) - 1):
stTime = timeList[i]
edTime = timeList[i + 1]
if stTime in tmwait:
continue
#借贷金额
print '借贷金额(新老)数据更新：' + stTime + '~' + edTime
#old
sql = """
select sum(repayMoney)
from loan_repaying
where compatibleStatus <> 'CANCEL' and productId != 1001
and createdTime >= '{}' and createdTime < '{}'
and userSid in (select distinct userSid from loan_repaying where createdTime < '{}');
""".format(stTime, edTime, stTime)
data = pysql.dbInfo(sql)
data = data.fillna(0)
loanOld = data.values[0][0]
#new
sql = """
select sum(repayMoney)
from loan_repaying
where compatibleStatus <> 'CANCEL' and productId != 1001
and createdTime >= '{}' and createdTime < '{}'
and userSid not in (select distinct userSid from loan_repaying where createdTime < '{}');
""".format(stTime, edTime, stTime)
data = pysql.dbInfo(sql)
data = data.fillna(0)
loanNew = data.values[0][0]
#插入数据
sql = """ insert into dayAddApi_flowloanmoneyno(loanOld,loanNew,createDate) values (%s,%s,%s) """
dset = [(loanOld, loanNew, stTime)]
status = pysql.insertData(sql, dset)
log.log('借贷金额(新老)更新状态-{}！({})！'.format(status, stTime), 'info')
def passRate():
timeList = timeScale()
sql = 'select distinct createDate from dayAddApi_aeyepassrate'
tmRest = pysql.dbInfoLocal(sql)
tmRest = tmRest.fillna(0)
tmwait = []
if not tmRest.empty:
tmwait = [str(x)[:10] for x in tmRest['createDate']]
for i in range(len(timeList) - 1):
stTime = timeList[i]
edTime = timeList[i + 1]
if stTime in tmwait:
continue
print '通过率' + stTime
#每日审核量
sql = """
select count(distinct user_id) from ci_cash_apply_info where audit_date >= '{}' and audit_date < '{}'
""".format(stTime, edTime)
data = pysql.dbInfo(sql)
data = data.fillna(0)
applyNum = data.values[0][0]
#每日通过量
sql = """
select count(distinct user_id) from ci_cash_apply_info where audit_date >= '{}' and audit_date < '{}' and status = 'SUCCESS'
""".format(stTime, edTime)
data = pysql.dbInfo(sql)
data = data.fillna(0)
passNum = data.values[0][0]
#通过率
passRate = round(passNum / float(applyNum) * 100, 2)
sql = """ insert into dayAddApi_aeyepassrate(applyNum,passNum,passRate,createDate) values (%s,%s,%s,%s) """
dset = [(applyNum, passNum, passRate, stTime)]
status = pysql.insertData(sql, dset)
log.log('通过率数据更新状态-{}({})！'.format(status, stTime), 'info')
def actRepayment():
timeList = timeScale()
sql = 'select distinct createDate from dayAddApi_indexacrepay'
tmRest = pysql.dbInfoLocal(sql)
tmRest = tmRest.fillna(0)
tmwait = []
if not tmRest.empty:
tmwait = [str(x)[:10] for x in tmRest['createDate']]
for i in range(len(timeList) - 1):
stTime = timeList[i]
edTime = timeList[i + 1]
if stTime in tmwait:
continue
print '应还实还数据更新：' + stTime
sql = """
select sum(repayMoney) from loan_repaying
where termDate='{}' and compatibleStatus not in ('CANCEL')
""".format(stTime)
data = pysql.dbInfo(sql)
data = data.fillna(0)
allRepayMoney = int(data.values[0][0])
sql = """
select sum(repayMoney) from loan_repaying
where termDate='{}' and compatibleStatus not in ('CANCEL')
and repaidTime is not null and DATE_FORMAT(termDate,'%Y-%m-%d') >= DATE_FORMAT(repaidTime,'%Y-%m-%d')
""".format(stTime)
data = pysql.dbInfo(sql)
data = data.fillna(0)
acRepayMoney = int(data.values[0][0])
repayRate = int(acRepayMoney / float(allRepayMoney) * 100)
sql = """ insert into dayAddApi_indexacrepay(allRepayMoney,acRepayMoney,repayRate,createDate) values (%s,%s,%s,%s) """
dset = [(allRepayMoney, acRepayMoney, repayRate, stTime)]
status = pysql.insertData(sql, dset)
log.log('每日应还实还更新状态-{}！({})！'.format(status, stTime), 'info')
def loanFund():
fundId = config.fundloanId
for fundName in fundId.keys():
ids = fundId[fundName][0]
timeList = timeScale('2017-08-30')
sql = "select distinct createDate from dayAddApi_flowloanfund where fundName='" + fundName + "'"
tmRest = pysql.dbInfoLocal(sql)
tmRest = tmRest.fillna(0)
tmwait = []
if not tmRest.empty:
tmwait = [str(x)[:10] for x in tmRest['createDate']]
for i in range(len(timeList) - 1):
stTime = timeList[i]
edTime = timeList[i + 1]
if stTime in tmwait:
continue
print u'贷款数据' + fundName + ' ' + stTime
sql = """
select sum(lendMoney) from loan
where status=6 and productId not in (3,4,5,6,1001)
and createdTime >= '{}' and createdTime < '{}'
and fundPayAccountId in ({})
""".format(stTime, edTime, ids)
data = pysql.dbInfo(sql)
data = data.fillna(0)
lendMoney = data.values[0][0]
sql = """ insert into dayAddApi_flowloanfund(fundName,sumMoney,createDate) values (%s,%s,%s) """
dset = [(fundName, lendMoney, stTime)]
status = pysql.insertData(sql, dset)
log.log(
u'每日贷款数据更新状态-{}！({})(资金方{})！'.format(status, stTime, fundName),
'info')
def getRate():
timeList = timeScale('2017-05-01')
sql = "select distinct createDate from dayAddApi_aeyegetrate"
tmRest = pysql.dbInfoLocal(sql)
tmRest = tmRest.fillna(0)
tmwait = []
if not tmRest.empty:
tmwait = [str(x)[:10] for x in tmRest['createDate']]
for i in range(len(timeList) - 1):
stTime = timeList[i]
edTime = timeList[i + 1]
if stTime in tmwait:
continue
print '成功贷款率' + stTime
sql = """
select count(distinct userSid) from loan where createdTime >= '{}' and createdTime < '{}'
""".format(stTime, edTime)
data = pysql.dbInfo(sql)
data = data.fillna(0)
tryNum = data.values[0][0]
sql = """
select count(distinct userSid) from loan where createdTime >= '{}' and createdTime < '{}' and status = 6
""".format(stTime, edTime)
data = pysql.dbInfo(sql)
data = data.fillna(0)
sucNum = data.values[0][0]
sucRate = round(sucNum / float(tryNum) * 100, 2)
sql = """ insert into dayAddApi_aeyegetrate(tryNum,sucNum,sucRate,createDate) values (%s,%s,%s,%s) """
dset = [(tryNum, sucNum, sucRate, stTime)]
status = pysql.insertData(sql, dset)
log.log(u'成功贷款数据更新状态-{}！({})！'.format(status, stTime), 'info')
def userPlace():
sql = """
select aes_decrypt(a.id_num,'1zhida**') 'id_num' from _user a,batch_acc_mgnt_account_info b where a.id=b.user_id;
"""
# sql = """
# select aes_decrypt(a.id_num,'1zhida**') 'id_num' from _user a,batch_acc_mgnt_account_info b where a.id=b.user_id and b.membership in (3,4,5);
# """
# sql = """
# select distinct aes_decrypt(a.id_num,'1zhida**') 'id_num'
# from _user a,loan_repaying b
# where a.id=b.userSid and b.compatibleStatus = 'OVERDUE';
# """
data = pysql.dbInfo(sql)
id_init = pd.read_csv(getTheFile('../data/t_id_card_init.csv'))
id_init['code'] = id_init['code'].map(str)
province = id_init[id_init['code'].map(lambda x:str(x)[-4:]=='0000')]
province1 = province[province['name'].map(lambda x: '北京'in x or '上海' in x or '天津'in x or '重庆' in x)]
province2 = province[province['name'].map(lambda x: '市' not in x)]
province = pd.concat([province1,province2])
city = id_init[id_init['code'].map(lambda x:str(x)[-2:]=='00')]
data['province_t'] = data['id_num'].map(lambda x:str(x)[:2]+'0000')
data['city_t'] = data['id_num'].map(lambda x:str(x)[:4]+'00')
data['country_t'] = data['id_num'].map(lambda x:str(x)[:6])
data = pd.merge(data,province,left_on='province_t',right_on='code',how='left')
data['省'] = data['name']
del data['code']
del data['name']
data = pd.merge(data,city,left_on='city_t',right_on='code',how='left')
data['市'] = data['name']
del data['code']
del data['name']
del data['province_t']
del data['city_t']
del data['country_t']
data["人数"] = 1
tp = pd.pivot_table(data,index=['省','市'],values=["人数"],aggfunc='count',fill_value=0)
tp['省'] = tp.index.map(lambda x :x[0])
tp['市'] = tp.index.map(lambda x :x[1])
tp = tp.sort_values(by="人数",ascending=False)
tp = tp.reset_index(drop=True)
# tp.to_csv('C:\Users\Amon\Desktop\ct.csv',index=False,encoding='utf_8_sig')
# exit(0)
#生成city
# gg = {}
# for i in range(len(tp)):
# item = tp.ix[i]
# key = item['省'] + item['市']
# gg[key] = item['市'].replace("地区","").replace("市","")
# if i > 71:
# break
# json.dump(gg, open(getTheFile('data/city.json'), 'w'), default=config.set_default)
city = json.load(open(getTheFile('../data/city.json')))
cityName = []
cityNum = []
for i in range(50,-1,-1):
item = tp.ix[i]
key = item['省'].decode('utf-8') + item['市'].decode('utf-8')
if city.get(key,None) is not None:
cityName.append(city.get(key))
cityNum.append(item['人数'])
ctime = [str(datetime.datetime.now()-datetime.timedelta(days=1))[:10]]*len(cityName)
sql = """ insert into dayAddApi_indexcity(cityName,numInCity,createDate) values (%s,%s,%s) """
cityName = [x.decode("utf-8") for x in cityName]
dset = zip(cityName,cityNum,ctime)
status = pysql.insertData(sql,dset)
log.log('用户地区分布数据更新状态-{}！'.format(status),'info')
def userRest():
timeList = timeScaleMonth()
sql = 'select distinct createDate from dayAddApi_userrest'
tmRest = pysql.dbInfoLocal(sql)
tmRest = tmRest.fillna(0)
tmwait = []
if not tmRest.empty:
tmwait = [str(x)[:7] for x in tmRest['createDate']]
for i in range(len(timeList) - 1):
stTime = timeList[i]
edTime = timeList[i + 1]
if stTime in tmwait:
continue
print u'留存' + stTime
sql = """
select DATE_FORMAT(a.audit_date,'%Y-%m') 'month',count(distinct a.user_id) 'allrest' from (
select c.user_id,c.audit_date from ci_cash_apply_info c left join user u
on c.user_id=u.id
where c.status='SUCCESS' and u.date_created > '{}' and u.date_created < '{}'
) a
where a.audit_date = (
select min(b.audit_date) from ci_cash_apply_info b where a.user_id=b.user_id and b.status='SUCCESS' and b.create_time > '{}'
)
group by DATE_FORMAT(a.audit_date,'%Y-%m')
""".format(stTime + '-01', edTime + '-01', stTime + '-01')
allrest = pysql.dbInfo(sql)
allrest = allrest.fillna(0)
sql = """
select DATE_FORMAT(a.createdTime,'%Y-%m') 'month',count(distinct a.userSid) 'currentactive' from (
select l.userSid,l.createdTime from loan l left join user u
on l.userSid=u.id
where l.status=6 and u.date_created > '{}' and u.date_created < '{}' ) a
group by DATE_FORMAT(a.createdTime,'%Y-%m')
""".format(stTime + '-01', edTime + '-01')
cactive = pysql.dbInfo(sql)
cactive = cactive.fillna(0)
aad = pd.merge(allrest, cactive, how='outer')
aad = aad.fillna(0)
aad['allrest'] = [
sum(aad['allrest'][:(i + 1)]) for i in range(len(aad))
]
aad['rtime'] = stTime
aad['activerate'] = aad['currentactive'] / aad['allrest'] * 100
aad['createdTime'] = str(datetime.datetime.now())[:10]
rtime = list(aad['rtime'])
cmonth = list(aad['month'])
allrest = list(aad['allrest'])
currentactive = list(aad['currentactive'])
currentActiveRate = list(aad['activerate'])
ctime = list(aad['createdTime'])
sql = "delete from dayAddApi_userrest where registerDate='{}'".format(
stTime)
status = pysql.deletetData(sql)
log.log(u'留存数据删除状态-{}！'.format(status), 'info')
sql = """ insert into dayAddApi_userrest(registerDate,currentDate,allPass,currentActive,currentActiveRate,createDate) values (%s,%s,%s,%s,%s,%s) """
dset = zip(rtime, cmonth, allrest, currentactive, currentActiveRate,
ctime)
status = pysql.insertData(sql, dset)
log.log('留存数据更新状态-{}！({})'.format(status, stTime), 'info')
def check():
#催收情况
sql = """
select month, round(D4催回金额/D4逾期金额*100,2) 'day4Rate',
round(D7催回金额/D7逾期金额*100,2) 'day7Rate',
round(D15催回金额/D15逾期金额*100,2) 'day15Rate',
round(D30催回金额/D30逾期金额*100,2) 'day30Rate',
round(D60催回金额/D60逾期金额*100,2) 'day60Rate',
round(D90催回金额/D90逾期金额*100,2) 'day90Rate',
round(D90Plus催回金额/D90逾期金额*100,2) 'day90Ratem'
from (
select date_format(a.lendTime,'%Y-%m') 'month',
sum(case when datediff(now(), b.termDate) >= 4 and b.compatibleStatus in ('OVERDUE','OVERDUE_PAID')
then a.payMoney else 0 end) 'D4逾期金额',
sum(case when datediff(now(), b.termDate) >= 7 and b.compatibleStatus in ('OVERDUE','OVERDUE_PAID')
then a.payMoney else 0 end) 'D7逾期金额',
sum(case when datediff(now(), b.termDate) >= 15 and b.compatibleStatus in ('OVERDUE','OVERDUE_PAID')
then a.payMoney else 0 end) 'D15逾期金额',
sum(case when datediff(now(), b.termDate) >= 30 and b.compatibleStatus in ('OVERDUE','OVERDUE_PAID')
then a.payMoney else 0 end) 'D30逾期金额',
sum(case when datediff(now(), b.termDate) >= 60 and b.compatibleStatus in ('OVERDUE','OVERDUE_PAID')
then a.payMoney else 0 end) 'D60逾期金额',
sum(case when datediff(now(), b.termDate) >= 90 and b.compatibleStatus in ('OVERDUE','OVERDUE_PAID')
then a.payMoney else 0 end) 'D90逾期金额',
sum(case when datediff(now(), b.termDate) >= 4 and b.compatibleStatus = 'OVERDUE_PAID' and b.repaidTime <= adddate(b.termDate, interval+4 day)
then a.payMoney else 0 end) 'D4催回金额',
sum(case when datediff(now(), b.termDate) >= 7 and b.compatibleStatus = 'OVERDUE_PAID' and b.repaidTime <= adddate(b.termDate, interval+7 day)
then a.payMoney else 0 end) 'D7催回金额',
sum(case when datediff(now(), b.termDate) >= 15 and b.compatibleStatus = 'OVERDUE_PAID' and b.repaidTime <= adddate(b.termDate, interval+15 day)
then a.payMoney else 0 end) 'D15催回金额',
sum(case when datediff(now(), b.termDate) >= 30 and b.compatibleStatus = 'OVERDUE_PAID' and b.repaidTime <= adddate(b.termDate, interval+30 day)
then a.payMoney else 0 end) 'D30催回金额',
sum(case when datediff(now(), b.termDate) >= 60 and b.compatibleStatus = 'OVERDUE_PAID' and b.repaidTime <= adddate(b.termDate, interval+60 day)
then a.payMoney else 0 end) 'D60催回金额',
sum(case when datediff(now(), b.termDate) >= 90 and b.compatibleStatus = 'OVERDUE_PAID' and b.repaidTime <= adddate(b.termDate, interval+90 day)
then a.payMoney else 0 end) 'D90催回金额',
sum(case when datediff(now(), b.termDate) >= 90 and b.compatibleStatus = 'OVERDUE_PAID'
then a.payMoney else 0 end) 'D90Plus催回金额'
from loan a, loan_repaying b
where a.id = b.loanId and a.status = 6 and b.termDate < curdate() and a.productId not in (3,4)
#and not exists (select 1 from loan g where g.userSid = a.userSid and g.status = 6 and g.lendTime < a.lendTime)
group by month) a;
"""
data = pysql.dbInfo(sql)
data = data.fillna(0)
month = list(data['month'])
day4Rate = list(data['day4Rate'])
day7Rate = list(data['day7Rate'])
day15Rate = list(data['day15Rate'])
day30Rate = list(data['day30Rate'])
day60Rate = list(data['day60Rate'])
day90Rate = list(data['day90Rate'])
day90Ratem = list(data['day90Ratem'])
updateDate = [str(datetime.datetime.today())[:10]] * len(month)
sql = "delete from dayAddApi_collectrate"
status = pysql.deletetData(sql)
log.log(u'催回率数据删除状态-{}!({})'.format(status,str(datetime.date.today())),'info')
sql = """ insert into dayAddApi_collectrate(month,day4Rate,day7Rate,day15Rate,day30Rate,day60Rate,day90Rate,day90Ratem,createDate) values (%s,%s,%s,%s,%s,%s,%s,%s,%s) """
dset = zip(month,day4Rate,day7Rate,day15Rate,day30Rate,day60Rate,day90Rate,day90Ratem,updateDate)
status = pysql.insertData(sql,dset)
log.log('催回率数据更新状态-{}！({})！'.format(status,str(datetime.date.today())),'info')
def collectNumYesterday():
#每日数据
timeList = timeScale()
sql = 'select distinct createDate from dayAddApi_collectnum'
tmRest = pysql.dbInfoLocal(sql)
tmRest = tmRest.fillna(0)
tmwait = []
if not tmRest.empty:
tmwait = [str(x)[:10] for x in tmRest['createDate']]
for i in range(len(timeList)-1):
stTime = timeList[i]
edTime = timeList[i+1]
if stTime in tmwait:
continue
print '催收每日数据' + stTime
#新增案件数
sql = """
select count(*) from t_loan_case
where create_date like '{}%' and firm_id = 1
""".format(stTime)
data = pysql.dbInfoCollect(sql)
data = data.fillna(0)
yesterdayNew = data.values[0][0]
#催回案件数（30天以上委外 30天以下自催）
sql = """
select count(*) from t_loan_case
where repaid_date like '{}%' and firm_id = 1 and overdue_day >= 30
""".format(stTime)
data = pysql.dbInfoCollect(sql)
data = data.fillna(0)
yesterdayPaidl30 = data.values[0][0]
sql = """
select count(*) from t_loan_case
where repaid_date like '{}%' and firm_id = 1 and overdue_day < 30
""".format(stTime)
data = pysql.dbInfoCollect(sql)
data = data.fillna(0)
yesterdayPaidu30 = data.values[0][0]
#催回 三日前的应催案件数
threeDay = str(datetime.datetime.strptime(stTime, '%Y-%m-%d') - datetime.timedelta(days=3))[:10]
sql = """
select count(*) from t_loan_case
where create_date like '{}%' and firm_id = 1
""".format(threeDay)
data = pysql.dbInfoCollect(sql)
data = data.fillna(0)
threeDayPaing = data.values[0][0]
#催回 三日前的催回案件数
sql = """
select count(*) from t_loan_case
where create_date like '{}%' and repaid_date <= '{}' and firm_id = 1
""".format(threeDay,edTime)
data = pysql.dbInfoCollect(sql)
data = data.fillna(0)
threeDayPaid = data.values[0][0]
#昨日 三日催回率
if threeDayPaing != 0:
NewPaidRate = round(threeDayPaid/float(threeDayPaing)*100,2)
else:
NewPaidRate = 0
sql = """ insert into dayAddApi_collectnum(newAdd,newCollectMl1,newCollectMu1,threeDayCollect,threeDayCollectRate,createDate) values (%s,%s,%s,%s,%s,%s) """
dset = [(yesterdayNew,yesterdayPaidl30,yesterdayPaidu30,threeDayPaing,NewPaidRate,stTime)]
status = pysql.insertData(sql,dset)
log.log('催回基本数据更新状态-{}! ({})'.format(status,stTime),'info')
def collectDisYesterday():
#案件数量情
#每日数据 当前逾期天数的分布
timeList = timeScale()
sql = 'select distinct createDate from dayAddApi_collectdis'
tmRest = pysql.dbInfoLocal(sql)
tmRest = tmRest.fillna(0)
tmwait = []
if not tmRest.empty:
tmwait = [str(x)[:10] for x in tmRest['createDate']]
for i in range(len(timeList)-1):
stTime = timeList[i]
edTime = timeList[i+1]
if stTime in tmwait:
continue
print '案件数量' + stTime
curDisct = {}
#待催收的案件数(未完成的催回+在规定时间外催回的)
sql = """
select count(*) from t_loan_case
where firm_id = 1 and create_date < '{}' and repaid_date > '{}' and loan_status = 'PAID'
""".format(edTime,edTime)
data = pysql.dbInfoCollect(sql)
data = data.fillna(0)
sql = """
select count(*) from t_loan_case
where loan_status = 'OVERDUE' and firm_id = 1 and create_date < '{}'
""".format(edTime)
data1 = pysql.dbInfoCollect(sql)
data1 = data1.fillna(0)
currentNum = data.values[0][0] + data1.values[0][0]
curDisct['currentNum'] = currentNum
#待催收的案件逾期天数分布
sql = """
select DATEDIFF('{}',overdue_date) 'overdue_day' from t_loan_case
where firm_id = 1 and create_date < '{}' and repaid_date > '{}' and loan_status = 'PAID'
""".format(edTime,edTime,edTime)
data = pysql.dbInfoCollect(sql)
sql = """
select DATEDIFF('{}',overdue_date) 'overdue_day' from t_loan_case
where loan_status = 'OVERDUE' and firm_id = 1 and create_date < '{}'
""".format(edTime,edTime)
data1 = pysql.dbInfoCollect(sql)
overdue_day_list = []
for x in data['overdue_day']:
overdue_day_list.append(x)
for x in data1['overdue_day']:
overdue_day_list.append(x)
bins = [0,3,10,20,30,60,90,max(overdue_day_list)+1]
labels = ['1-3','4-10','11-20','21-30','31-60','61-90','90-']
df = pd.cut(overdue_day_list,bins=bins,labels=labels)
df = df.value_counts()
for i in range(len(df)):
curDisct[df.index[i]] = df.values[i]
sql = """ insert into dayAddApi_collectdis(dayto3,dayto10,dayto20,dayto30,dayto60,dayto90,dayover90,currentNum,createDate) values (%s,%s,%s,%s,%s,%s,%s,%s,%s) """
dset = [(curDisct['1-3'],curDisct['4-10'],curDisct['11-20'],curDisct['21-30'],curDisct['31-60'],curDisct['61-90'],curDisct['90-'],curDisct['currentNum'],stTime)]
status = pysql.insertData(sql,dset)
log.log('每日案件逾期天数更新状态-{}! ({})'.format(status,stTime),'info')
def passRateloan():
endTime = str(datetime.date.today() - datetime.timedelta(days=3))
timeList = timeScale('2017-05-01')
sql = 'select distinct createDate from dayAddApi_marketnum'
tmRest = pysql.dbInfoLocal(sql)
tmRest = tmRest.fillna(0)
tmwait = []
if not tmRest.empty:
tmwait = [str(x)[:10] for x in tmRest['createDate']]
for i in range(len(timeList) - 1):
stTime = timeList[i]
edTime1 = timeList[i + 1]
if stTime in tmwait and stTime < endTime:
#当前提现成功率
sql = """
select count(DISTINCT b.userSid) from ci_cash_apply_info a,loan b
where a.user_id=b.userSid and a.product_id = b.productId
and a.audit_date >= '{}' and a.audit_date < '{}'
and b.createdTime > '{}'
and a.status in ('SUCCESS') and b.status=6;
""".format(stTime, edTime1, stTime)
data = pysql.dbInfo(sql)
data = data.fillna(0)
paidNum = data.values[0][0]
sql = """
select applyPass from dayAddApi_marketnum
where createDate >= '{}' and createDate < '{}'
""".format(stTime, edTime1)
data = pysql.dbInfoLocal(sql)
data = data.fillna(0)
applyPass = data.values[0][0]
#当前 申请提现成功率
paidRate = round(paidNum / float(applyPass) *
100, 2) if paidNum > 2 else 0
#存储数据
sql = """
update dayAddApi_marketnum
set paidRate = {} ,paidNum = {}
where createDate >= '{}' and createDate < '{}'
""".format(paidRate, paidNum, stTime, edTime1)
status = pysql.updateData(sql)
log.log('当前申请提现成功率更新状态-{}! ({})'.format(status, stTime), 'info')
continue
print '提现情况' + stTime
#每日数据模板
dayNum = {
'applyPass': 0,
'firstDayT': 0,
'firstDay': 0,
'firstDayRate': 0,
'tryRate': 0,
'secondDay': 0,
'secondDayRate': 0,
'thirdDay': 0,
'thirdDayRate': 0,
'paidNum': 0,
'paidRate': 0,
'auditTime': 0,
'auditTimeWit': 0,
'auditTimeToday': 0
}
#每日审核通过情况
sql = """
select count(distinct user_id) from ci_cash_apply_info
where audit_date > '{}' and audit_date < '{}' and status in ('SUCCESS','FA_SUCCESS')
""".format(stTime, edTime1)
data = pysql.dbInfo(sql)
data = data.fillna(0)
dayNum['applyPass'] = data.values[0][0]
#当日审核 申请提现
sql = """
select count(DISTINCT b.userSid) from ci_cash_apply_info a,loan b
where a.user_id=b.userSid and a.product_id = b.productId
and a.audit_date > '{}' and a.audit_date < '{}'
and b.createdTime > '{}' and b.createdTime < '{}'
and a.status in ('SUCCESS','FA_SUCCESS');
""".format(stTime, edTime1, stTime, edTime1)
data = pysql.dbInfo(sql)
data = data.fillna(0)
dayNum['firstDayT'] = data.values[0][0]
#当日审核 申请提现成功
sql = """
select count(DISTINCT b.userSid) from ci_cash_apply_info a,loan b
where a.user_id=b.userSid and a.product_id = b.productId
and a.audit_date > '{}' and a.audit_date < '{}'
and b.createdTime > '{}' and b.createdTime < '{}'
and a.status in ('SUCCESS','FA_SUCCESS') and b.status=6;
""".format(stTime, edTime1, stTime, edTime1)
data = pysql.dbInfo(sql)
data = data.fillna(0)
dayNum['firstDay'] = data.values[0][0]
#当日审核 申请提现率
dayNum['tryRate'] = round(
dayNum['firstDayT'] / float(dayNum['applyPass']) *
100, 2) if dayNum['applyPass'] > 2 else 0
#当日审核 申请提现成功率
dayNum['firstDayRate'] = round(
dayNum['firstDay'] / float(dayNum['applyPass']) *
100, 2) if dayNum['applyPass'] > 2 else 0
#当前提现成功
sql = """
select count(DISTINCT b.userSid) from ci_cash_apply_info a,loan b
where a.user_id=b.userSid and a.product_id = b.productId
and a.audit_date > '{}' and a.audit_date < '{}'
and b.createdTime > '{}'
and a.status in ('SUCCESS','FA_SUCCESS') and b.status=6;
""".format(stTime, edTime1, stTime)
data = pysql.dbInfo(sql)
data = data.fillna(0)
dayNum['paidNum'] = data.values[0][0]
#当前 申请提现成功率
dayNum['paidRate'] = round(
dayNum['paidNum'] / float(dayNum['applyPass']) *
100, 2) if dayNum['applyPass'] > 2 else 0
#审核时间
sql = """
select DATE_FORMAT(create_time,'%H') 'hour',(UNIX_TIMESTAMP(audit_date) - UNIX_TIMESTAMP(create_time)) 'wait_second'
from ci_cash_apply_info
where audit_date is not null and status in ('SUCCESS','FA_SUCCESS')
and audit_date > '{}' and audit_date < '{}'
""".format(stTime, edTime1)
data = pysql.dbInfo(sql)
data = data.fillna(0)
if len(data) > 10:
lp = data['wait_second']
lp = lp[lp > np.percentile(data['wait_second'], 15)]
lp = lp[lp < np.percentile(data['wait_second'], 85)]
dayNum['auditTime'] = int(np.mean(lp) / 60)
data = data[data['hour'].map(
lambda x: x not in
['22', '23', '00', '01', '02', '03', '04', '05', '06'])]
if not data.empty:
if len(data) > 10:
lp = data['wait_second']
lp = lp[lp > np.percentile(data['wait_second'], 15)]
lp = lp[lp < np.percentile(data['wait_second'], 85)]
dayNum['auditTimeWit'] = int(np.mean(lp) / 60)
#当日的审核情况
sql = """
select DATE_FORMAT(create_time,'%H') 'hour',(UNIX_TIMESTAMP(audit_date) - UNIX_TIMESTAMP(create_time)) 'wait_second'
from ci_cash_apply_info
where audit_date is not null and status in ('SUCCESS','FA_SUCCESS') and DATE_FORMAT(audit_date,'%Y-%m-%d')=DATE_FORMAT(create_time,'%Y-%m-%d')
and audit_date > '{}' and audit_date < '{}'
""".format(stTime, edTime1)
data = pysql.dbInfo(sql)
data = data.fillna(0)
data = data[data['hour'].map(
lambda x: x not in
['22', '23', '00', '01', '02', '03', '04', '05', '06'])]
if len(data) > 10:
lp = data['wait_second']
lp = lp[lp > np.percentile(data['wait_second'], 15)]
lp = lp[lp < np.percentile(data['wait_second'], 85)]
dayNum['auditTimeToday'] = int(np.mean(lp) / 60)
sql = """ insert into dayAddApi_marketnum(applyPass,firstDayT,firstDay,firstDayRate,tryRate,secondDay,secondDayRate,thirdDay,thirdDayRate,paidNum,paidRate,auditTime,auditTimeWit,auditTimeToday,createDate) values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) """
dset = [(dayNum['applyPass'], dayNum['firstDayT'], dayNum['firstDay'],
dayNum['firstDayRate'], dayNum['tryRate'],
dayNum['secondDay'], dayNum['secondDayRate'],
dayNum['thirdDay'], dayNum['thirdDayRate'], dayNum['paidNum'],
dayNum['paidRate'], dayNum['auditTime'],
dayNum['auditTimeWit'], dayNum['auditTimeToday'], stTime)]
status = pysql.insertData(sql, dset)
log.log(u'逾审核时间数据更新更新状态-{}！({})！'.format(status, stTime), 'info')
def loan():
timeList = timeScale()
sql = 'select distinct createDate from dayAddApi_flowloanmoney'
tmRest = pysql.dbInfoLocal(sql)
tmRest = tmRest.fillna(0)
tmwait = []
if not tmRest.empty:
tmwait = [str(x)[:10] for x in tmRest['createDate']]
for i in range(len(timeList) - 1):
stTime = timeList[i]
edTime = timeList[i + 1]
if stTime in tmwait:
continue
#借贷金额
print '借贷金额数据更新：' + stTime + '~' + edTime
sql = """
select productId,repayMoney
from loan_repaying
where compatibleStatus <> 'CANCEL' and productId != 1001
and createdTime >= '{}' and createdTime < '{}';
""".format(stTime, edTime)
data = pysql.dbInfo(sql)
data = data.fillna(0)
product = config.product
for key in product.keys():
tp = data[data['productId'] == int(key)]
if not tp.empty:
money = int(sum(tp['repayMoney']))
else:
money = 0
sql = """ insert into dayAddApi_flowloanmoney(product,money,createDate) values (%s,%s,%s) """
dset = [(product[key], money, stTime)]
status = pysql.insertData(sql, dset)
log.log('每日借贷金额更新状态-{}! ({})'.format(status, stTime), 'info')
allLoan = int(sum(data['repayMoney']))
sql = """ insert into dayAddApi_flowloanmoney(product,money,createDate) values (%s,%s,%s) """
dset = [('All', allLoan, stTime)]
status = pysql.insertData(sql, dset)
log.log('每日借贷金额更新状态-{}! ({})'.format(status, stTime), 'info')
#总金额
sql = """
select productId,sum(repayMoney) 'repayMoney'
from loan_repaying
where compatibleStatus <> 'CANCEL' and productId != 1001
group by productId
"""
data = pysql.dbInfo(sql)
data = data.fillna(0)
product = config.product
for key in product.keys():
proname = product[key]
tp = data[data['productId'] == int(key)]
if not tp.empty:
money = int(sum(tp['repayMoney']))
else:
money = 0
sql = """ insert into dayAddApi_flowloanmoneysum(product,money,createDate) values (%s,%s,%s) """
dset = [(proname, money, str(datetime.datetime.today())[:10])]
status = pysql.insertData(sql, dset)
log.log('借贷总金额更新状态-{}！({})！'.format(status, stTime), 'info')
def c2c():
timeList = timeScale(startTime='2017-12-10')
sql = 'select distinct createDate from dayAddApi_flowc2cfund'
tmRest = pysql.dbInfoLocal(sql)
tmRest = tmRest.fillna(0)
tmwait = []
if not tmRest.empty:
tmwait = set([str(x)[:10] for x in tmRest['createDate']])
for i in range(len(timeList) - 1):
stTime = timeList[i]
edTime = timeList[i + 1]
if stTime in tmwait:
continue
print 'c2c数据更新：' + stTime
c2c_member = config.c2c_member
for name in c2c_member:
ids = c2c_member[name][0]
sql = """
select count(*) 'num', sum(repayMoney) 'summoney' from loan
where status = 6 and productId = 7
and lastUpdated >= '{}' and lastUpdated < '{}'
and loanerId in ({})
""".format(stTime, edTime, ids)
data = pysql.dbInfo(sql)
data = data.fillna(0)
loancount = data['num'].values[0]
loanmoney = data['summoney'].values[0]
sql = """
select count(*) 'num', sum(ll.repayMoney) 'summoney' from loan l,loan_repaying ll
where l.id = ll.loanId
and l.status = 6 and l.productId = 7
and ll.termDate = '{}'
and l.loanerId in ({})
""".format(stTime, ids)
data = pysql.dbInfo(sql)
data = data.fillna(0)
loanCountTerm = data['summoney'].values[0]
sql = """
select count(*) 'num', sum(lt.repayMoney) 'summoney' from (
select ll.loanId, ll.repayMoney, ll.repaidTime, ll.termDate from loan l,loan_repaying ll
where l.id = ll.loanId
and l.status = 6 and l.productId = 7
and ll.termDate = '{}'
and l.loanerId in ({})) lt
where DATE_FORMAT(lt.repaidTime,'%Y-%m-%d') > DATE_FORMAT(lt.termDate,'%Y-%m-%d') or lt.repaidTime is null
""".format(stTime, ids)
data = pysql.dbInfo(sql)
data = data.fillna(0)
loanCountTermNo = data['summoney'].values[0]
delayRate0 = 0 if loanCountTermNo == 0 else round(
loanCountTermNo / float(loanCountTerm) * 100, 2)
sql = """
select count(*) 'num', sum(l.repayMoney) 'summoney' from loan l,loan_repaying ll
where l.id = ll.loanId
and l.status = 6 and l.productId = 7 and ll.termDate < '{}'
and l.loanerId in ({})
""".format(edTime, ids)
data = pysql.dbInfo(sql)
data = data.fillna(0)
allCountTerm = data['summoney'].values[0]
sql = """
select count(*) 'num', sum(ll.repayMoney) 'summoney' from loan l,loan_repaying ll
where l.id = ll.loanId
and l.status = 6 and l.productId = 7
and l.loanerId in ({})
and if(ll.repaidTime is null, DATEDIFF(DATE_FORMAT(now(), "%Y-%m-%d"), DATE_FORMAT(ll.termDate, "%Y-%m-%d")), DATEDIFF(DATE_FORMAT(ll.repaidTime, "%Y-%m-%d"), DATE_FORMAT(ll.termDate, "%Y-%m-%d"))) >= 7
""".format(ids)
data = pysql.dbInfo(sql)
data = data.fillna(0)
countTerm7 = data['summoney'].values[0]
delayRate7 = 0 if countTerm7 == 0 else round(
countTerm7 / float(allCountTerm) * 100, 2)
sql = """ insert into dayAddApi_flowc2cfund(member, loanCount, loanMoney, loanCountTerm, loanCountTermNo, delayRate0, allCountTerm, delayRate7, countTerm7, createDate) values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) """
dset = [
(name, loancount, loanmoney, loanCountTerm, loanCountTermNo,
delayRate0, allCountTerm, delayRate7, countTerm7, stTime)
]
status = pysql.insertData(sql, dset)
log.log('c2c更新状态-{}！({})！'.format(status, stTime), 'info')
def delayDayNO():
timeList = timeScale('2017-08-30')[:-3]
sql = "select distinct createDate from dayAddApi_aeyedelayrateno "
tmRest = pysql.dbInfoLocal(sql)
tmRest = tmRest.fillna(0)
tmwait = []
if not tmRest.empty:
tmwait = [str(x)[:10] for x in tmRest['createDate']]
for i in range(len(timeList) - 1):
stTime = timeList[i]
edTime = timeList[i + 1]
if stTime in tmwait:
continue
print '逾期(新老)3天逾期率' + stTime
#分新老首逾情况
#new
sql = """
select sum(a.repayMoney)
from loan a,loan_repaying b
where a.id=b.loanId and a.status=6 and b.compatibleStatus not in ('CANCEL') and b.productId not in (3,4,5,6,1001)
and b.termDate >= '{}' and b.termDate < '{}'
and a.userSid not in (select distinct userSid from loan_repaying where termDate < '{}')
""".format(stTime, edTime, stTime)
data = pysql.dbInfo(sql)
data = data.fillna(0)
newRepaySum = data.values[0][0]
sql = """
select sum(a.repayMoney)
from loan a,loan_repaying b
where a.id=b.loanId and a.status=6 and b.compatibleStatus not in ('CANCEL') and b.productId not in (3,4,5,6,1001)
and b.termDate >= '{}' and b.termDate < '{}'
and if(b.repaidTime is NULL,TO_DAYS(now()) - TO_DAYS(b.termDate),TO_DAYS(b.repaidTime) - TO_DAYS(b.termDate)) <= 3
and a.userSid not in (
select distinct userSid from loan_repaying where termDate < '{}'
)
""".format(stTime, edTime, stTime)
data = pysql.dbInfo(sql)
data = data.fillna(0)
newPaid = data.values[0][0]
newDelayRate = round((newRepaySum - newPaid) / newRepaySum * 100, 2)
#old
sql = """
select sum(a.repayMoney)
from loan a,loan_repaying b
where a.id=b.loanId and a.status=6 and b.compatibleStatus not in ('CANCEL') and b.productId not in (3,4,5,6,1001)
and b.termDate >= '{}' and b.termDate < '{}'
and a.userSid in (
select distinct userSid from loan_repaying where termDate < '{}'
)
""".format(stTime, edTime, stTime)
data = pysql.dbInfo(sql)
data = data.fillna(0)
oldRepaySum = data.values[0][0]
sql = """
select sum(a.repayMoney)
from loan a,loan_repaying b
where a.id=b.loanId and a.status=6 and b.compatibleStatus not in ('CANCEL') and b.productId not in (3,4,5,6,1001)
and b.termDate >= '{}' and b.termDate < '{}'
and if(b.repaidTime is NULL,TO_DAYS(now()) - TO_DAYS(b.termDate),TO_DAYS(b.repaidTime) - TO_DAYS(b.termDate)) <= 3
and a.userSid in (
select distinct userSid from loan_repaying where termDate < '{}'
)
""".format(stTime, edTime, stTime)
data = pysql.dbInfo(sql)
data = data.fillna(0)
oldPaid = data.values[0][0]
oldDelayRate = round((oldRepaySum - oldPaid) / oldRepaySum * 100, 2)
sql = """ insert into dayAddApi_aeyedelayrateno(newRepaySum,newPaid,newDelayRate3,oldRepaySum,oldPaid,oldDelayRate3,createDate) values (%s,%s,%s,%s,%s,%s,%s) """
dset = [(newRepaySum, newPaid, newDelayRate, oldRepaySum, oldPaid,
oldDelayRate, stTime)]
status = pysql.insertData(sql, dset)
log.log(u'逾期3天(新老)数据更新状态-{}！({})！'.format(status, stTime), 'info')
