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