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 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 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 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 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 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 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 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')
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')
