class ApiLoan(db.Model, Base):
__bind_key__ = 'xhh_antifraud'
__tablename__ = 'api_loan'
id = db.Column(db.BigInteger, primary_key=True)
base_id = db.Column(db.BigInteger, nullable=False, index=True)
contain_id = db.Column(db.Integer)
user_total = db.Column(db.Integer)
loan_all = db.Column(db.Integer)
overdue_norepay = db.Column(db.Integer)
overdue_repay = db.Column(db.Integer)
overdue7_norepay = db.Column(db.Integer)
overdue7_repay = db.Column(db.Integer)
loan_total = db.Column(db.Integer)
last_loan_day = db.Column(db.DateTime)
normal_repay = db.Column(db.Integer)
realadl_tot_reject_num = db.Column(db.Integer)
realadl_tot_freject_num = db.Column(db.Integer)
realadl_tot_sreject_num = db.Column(db.Integer)
realadl_tot_dlq14_num = db.Column(db.Integer)
realadl_dlq14_ratio = db.Column(db.Numeric(12, 2))
history_bad_status = db.Column(db.Integer)
com_c_user = db.Column(db.Integer)
success_num = db.Column(db.Integer)
last_end_date = db.Column(db.DateTime)
last_repay_time = db.Column(db.DateTime)
last_success_loan_days = db.Column(db.Integer)
mth3_dlq_num = db.Column(db.Integer)
mth3_dlq7_num = db.Column(db.Integer)
mth3_wst_sys = db.Column(db.Integer)
mth6_dlq_ratio = db.Column(db.Numeric(12, 2))
wst_dlq_sts = db.Column(db.Integer)
user_loan_total = db.Column(db.Integer)
cd_amount = db.Column(db.Integer)
create_time = db.Column(db.DateTime)
class AfAddrloan(db.Model, Base):
__bind_key__ = 'xhh_antifraud'
__tablename__ = 'af_addr_loan'
id = db.Column(db.BigInteger, primary_key=True)
request_id = db.Column(db.BigInteger, nullable=False, index=True)
aid = db.Column(db.Integer)
contain_id = db.Column(db.Integer)
user_id = db.Column(db.BigInteger, nullable=False, index=True)
user_total = db.Column(db.Integer, nullable=False)
loan_all = db.Column(db.Integer, nullable=False)
overdue_norepay = db.Column(db.Integer, nullable=False)
overdue_repay = db.Column(db.Integer, nullable=False)
overdue7_norepay = db.Column(db.Integer, nullable=False)
overdue7_repay = db.Column(db.Integer, nullable=False)
loan_total = db.Column(db.Integer, nullable=False)
last_loan_day = db.Column(db.String, nullable=False)
normal_repay = db.Column(db.Integer, nullable=False)
realadl_tot_reject_num = db.Column(db.Integer, nullable=True)
realadl_tot_freject_num = db.Column(db.Integer, nullable=True)
realadl_tot_sreject_num = db.Column(db.Integer, nullable=True)
realadl_tot_dlq14_num = db.Column(db.Integer, nullable=True)
realadl_dlq14_ratio = db.Column(db.Numeric(12, 2), nullable=True)
history_bad_status = db.Column(db.Integer, nullable=True)
create_time = db.Column(db.DateTime, nullable=False)
class YiUserInvest(db.Model, BaseModel):
__bind_key__ = 'xhh_yiyiyuan'
__tablename__ = 'yi_user_invest'
__table_args__ = (
db.Index('userinvest', 'user_id', 'loan_id', 'loan_user_id'),
)
invest_id = db.Column(db.BigInteger, primary_key=True)
user_id = db.Column(db.BigInteger, nullable=False, server_default=db.FetchedValue())
loan_user_id = db.Column(db.BigInteger, nullable=False, server_default=db.FetchedValue())
loan_id = db.Column(db.BigInteger, nullable=False, server_default=db.FetchedValue())
amount = db.Column(db.Numeric(10, 4), nullable=False)
_yield = db.Column('yield', db.Numeric(10, 4), nullable=False, server_default=db.FetchedValue())
start_date = db.Column(db.DateTime)
end_date = db.Column(db.DateTime)
status = db.Column(db.Integer, nullable=False, server_default=db.FetchedValue())
contract = db.Column(db.String(20))
contract_url = db.Column(db.String(128))
create_time = db.Column(db.DateTime)
version = db.Column(db.Integer)
def getInvestMe(self, loan_user_id):
'''
投资我的人
'''
fields = [YiUserInvest.loan_user_id.label('my_user_id'), YiUserInvest.user_id.label(
'i_user_id'), YiUser.mobile.label('mobile')]
t = db.session.query(*fields).outerjoin(YiUser, YiUser.user_id == YiUserInvest.user_id)
t.filter(YiUserInvest.loan_user_id == loan_user_id)
res = t.limit(1000).all()
return res
def getMyInvest(self, user_id):
'''
我投资的人
'''
fields = [YiUserInvest.loan_user_id.label('i_user_id'), YiUserInvest.user_id.label('my_user_id'), YiUser.mobile.label(
'mobile')]
t = db.session.query(*fields).outerjoin(YiUser, YiUser.user_id == YiUserInvest.loan_user_id)
res = t.filter(YiUserInvest.user_id == user_id).limit(1000).all()
return res
class ShopLoanRepay(db.Model, BaseModel):
__bind_key__ = 'xhh_yigeyi'
__tablename__ = 'loan_repay'
metadata = MetaData()
id = db.Column(db.BigInteger, primary_key=True)
order_id = db.Column(db.String(64))
bank_id = db.Column(db.Integer, nullable=False, server_default=db.FetchedValue())
user_id = db.Column(db.Integer, nullable=False, server_default=db.FetchedValue())
loan_id = db.Column(db.Integer, nullable=False, server_default=db.FetchedValue())
status = db.Column(db.Integer, nullable=False, server_default=db.FetchedValue())
error_code = db.Column(db.String(32))
error_msg = db.Column(db.String(50))
money = db.Column(db.Numeric(10, 2), nullable=False, server_default=db.FetchedValue())
actual_money = db.Column(db.Numeric(10, 2), nullable=False, server_default=db.FetchedValue())
paybill = db.Column(db.String(64))
platform = db.Column(db.Integer, nullable=False, server_default=db.FetchedValue())
come_from = db.Column(db.Integer, nullable=False, server_default=db.FetchedValue())
coupon_amount = db.Column(db.Numeric(10, 2), nullable=False, server_default=db.FetchedValue())
create_time = db.Column(db.DateTime)
modify_time = db.Column(db.DateTime)
version = db.Column(db.Integer, nullable=False, server_default=db.FetchedValue())
def advanceRepay(self,user_ids):
'''通讯录借款提前/正常还款 '''
if len(user_ids) == 0:
return 0
userid_str = '"' + '","'.join(str(val) for val in user_ids) + '"'
sql = "select count(DISTINCT(repay.loan_id)) from loan_repay as repay left join loan_user_loan as loan on(repay.loan_id=loan.loan_id) where repay.user_id in(%s) and repay.status=6 and repay.modify_time < loan.end_date" % userid_str
count = db.session.execute(sql, bind=self.get_engine()).fetchone()
all_overdue = count[0]
return all_overdue
def getSuccessNum(self, user_id):
if not user_id:
return 0
sql = 'select count(1) as success_num from loan_repay lr inner join loan_user_loan ul on ul.loan_id = lr.loan_id where lr.user_id = "%s" and lr.status =6 and ul.number= 0 ' %(user_id)
count = db.session.execute(sql, bind=self.get_engine()).fetchone()
return count[0]
class YgyUserLoanExtend(db.Model, BaseModel):
__bind_key__ = 'xhh_yigeyi'
__tablename__ = 'yi_user_loan_extend'
metadata = MetaData()
id = db.Column(db.BigInteger, primary_key=True)
user_id = db.Column(db.BigInteger,
nullable=False,
server_default=db.FetchedValue())
loan_id = db.Column(db.BigInteger,
nullable=False,
server_default=db.FetchedValue())
uuid = db.Column(db.String(55))
outmoney = db.Column(db.BigInteger,
nullable=False,
server_default=db.FetchedValue())
payment_channel = db.Column(db.BigInteger,
nullable=False,
server_default=db.FetchedValue())
userIp = db.Column(db.String(64))
extend_type = db.Column(db.BigInteger,
nullable=False,
server_default=db.FetchedValue())
success_num = db.Column(db.BigInteger,
nullable=False,
server_default=db.FetchedValue())
last_modify_time = db.Column(db.DateTime)
create_time = db.Column(db.DateTime)
fund = db.Column(db.BigInteger,
nullable=False,
server_default=db.FetchedValue())
status = db.Column(db.BigInteger,
nullable=False,
server_default=db.FetchedValue())
version = db.Column(db.BigInteger,
nullable=False,
server_default=db.FetchedValue())
loan_total = db.Column(db.BigInteger,
nullable=False,
server_default=db.FetchedValue())
loan_success = db.Column(db.BigInteger,
nullable=False,
server_default=db.FetchedValue())
loan_quota = db.Column(db.Numeric(12, 2), nullable=False)
def getSuccessNum(self, user_id):
if user_id is None:
return 0
where = and_(YgyUserLoanExtend.user_id == user_id,
YgyUserLoanExtend.status == "SUCCESS")
res = db.session.query(YgyUserLoanExtend).filter(where).count()
return res
class ApiContact(db.Model, Base):
# 指定数据库
__bind_key__ = 'xhh_antifraud'
__tablename__ = 'api_contact'
id = db.Column(db.BigInteger, primary_key=True)
base_id = db.Column(db.BigInteger, nullable=False, index=True)
com_r_total = db.Column(db.Integer)
com_r_rank = db.Column(db.Integer)
com_r_total_mavg = db.Column(db.Numeric(12, 2))
com_r_duration = db.Column(db.Integer)
com_r_duration_rank = db.Column(db.Integer)
com_r_duration_mavg = db.Column(db.Numeric(12, 2))
com_c_total = db.Column(db.Integer)
com_c_rank = db.Column(db.Integer)
com_c_total_mavg = db.Column(db.Numeric(12, 2))
com_c_duration = db.Column(db.Integer)
com_c_duration_rank = db.Column(db.Integer)
com_c_duration_mavg = db.Column(db.Numeric(12, 2))
com_r_overdue = db.Column(db.Integer)
com_c_overdue = db.Column(db.Integer)
create_time = db.Column(db.DateTime, nullable=False)
class AfRelationMatch(db.Model, Base):
__bind_key__ = 'xhh_antifraud'
__tablename__ = 'af_relation_match'
id = db.Column(db.BigInteger, primary_key=True)
request_id = db.Column(db.BigInteger, nullable=False, index=True)
aid = db.Column(db.Integer, nullable=False)
user_id = db.Column(db.BigInteger, nullable=False, index=True)
avg_jaccard_phone_no4 = db.Column(db.Numeric(12, 4))
p_num_android = db.Column(db.Numeric(12, 4))
yiqi_7_p = db.Column(db.Numeric(12, 4))
ref_model_people_pt = db.Column(db.Numeric(12, 4))
report_shutdown_1_no4 = db.Column(db.Numeric(12, 4))
jaccard_all_more1_no4 = db.Column(db.Numeric(12, 4))
jaccard_all_max_no4 = db.Column(db.Numeric(12, 4))
num_android_no4 = db.Column(db.Integer)
vs_connect_match_1_no4 = db.Column(db.Numeric(12, 4))
create_time = db.Column(db.DateTime, nullable=False)
class AfReport(db.Model, Base):
# 指定数据库
__bind_key__ = 'xhh_antifraud'
__tablename__ = 'af_report'
id = db.Column(db.BigInteger, primary_key=True)
request_id = db.Column(db.BigInteger, nullable=False, index=True, server_default=db.FetchedValue())
aid = db.Column(db.Integer)
user_id = db.Column(db.BigInteger, nullable=False, index=True, server_default=db.FetchedValue())
report_aomen = db.Column(db.Integer)
report_110 = db.Column(db.Integer)
report_120 = db.Column(db.Integer)
report_lawyer = db.Column(db.Integer)
report_court = db.Column(db.Integer)
report_use_time = db.Column(db.Integer)
report_shutdown = db.Column(db.Integer)
report_name_match = db.Column(db.Integer)
report_fcblack_idcard = db.Column(db.Integer)
report_fcblack_phone = db.Column(db.Integer)
report_fcblack = db.Column(db.Integer)
report_operator_name = db.Column(db.String(20))
report_reliability = db.Column(db.Integer)
report_night_percent = db.Column(db.Numeric(10, 2))
report_loan_connect = db.Column(db.String(50))
create_time = db.Column(db.DateTime, nullable=False)
def getReportShutdownByUserIds(self, user_ids):
'''
获取af_detail
'''
if len(user_ids) == 0:
return []
userid_str = '"' + '","'.join(str(val) for val in user_ids) + '"'
sql = '''SELECT report_shutdown from (select * from af_report WHERE user_id in(%s) ORDER BY id DESC ) tmp GROUP BY user_id ''' % userid_str
res = db.session.execute(sql, bind=self.get_engine()).fetchall()
report_shutdown= []
if len(res) > 0:
for i in res:
if i[0] is None:
report_shutdown.append(0)
else:
report_shutdown.append(i[0])
return report_shutdown
class YiUserPassword(db.Model, BaseModel):
__bind_key__ = 'xhh_yiyiyuan'
__tablename__ = 'yi_user_password'
id = db.Column(db.BigInteger, primary_key=True)
user_id = db.Column(db.BigInteger,
nullable=False,
index=True,
server_default=db.FetchedValue())
login_password = db.Column(db.String(64))
pay_password = db.Column(db.String(64))
device_tokens = db.Column(db.String(64))
device_type = db.Column(db.String(10))
iden_address = db.Column(db.String(64))
nation = db.Column(db.String(32))
pic_url = db.Column(db.String(64))
iden_url = db.Column(db.String(64))
score = db.Column(db.Numeric(12, 4))
create_time = db.Column(db.DateTime, nullable=False)
last_modify_time = db.Column(db.DateTime, nullable=False)
version = db.Column(db.BigInteger, nullable=False)
def getAndroidcount(self, user_ids):
'''
获取已存在关系(relation)的数据
'''
if len(user_ids) == 0:
return 0
where = and_(
YiUserPassword.user_id.in_(user_ids),
YiUserPassword.device_type == 'android',
)
counts = db.session.query(YiUserPassword) \
.filter(where) \
.order_by(YiUserPassword.id.desc()) \
.limit(1000) \
.count()
return counts
class ApiReport(db.Model, Base):
# 指定数据库
__bind_key__ = 'xhh_antifraud'
__tablename__ = 'api_report'
id = db.Column(db.BigInteger, primary_key=True)
base_id = db.Column(db.BigInteger)
report_aomen = db.Column(db.Integer)
report_110 = db.Column(db.Integer)
report_120 = db.Column(db.Integer)
report_lawyer = db.Column(db.Integer)
report_court = db.Column(db.Integer)
report_use_time = db.Column(db.Integer)
report_shutdown = db.Column(db.Integer)
report_name_match = db.Column(db.Integer)
report_fcblack_idcard = db.Column(db.Integer)
report_fcblack_phone = db.Column(db.Integer)
report_fcblack = db.Column(db.Integer)
report_operator_name = db.Column(db.String(20))
report_reliability = db.Column(db.Integer)
report_night_percent = db.Column(db.Numeric(10, 2))
report_loan_connect = db.Column(db.String(50))
create_time = db.Column(db.DateTime, nullable=False)
class ShopLoanUserLoan(db.Model, BaseModel):
__bind_key__ = 'loan_shop'
__tablename__ = 'loan_user_loan'
metadata = MetaData()
loan_id = db.Column(db.BigInteger, primary_key=True)
parent_loan_id = db.Column(db.Integer,
nullable=False,
server_default=db.FetchedValue())
number = db.Column(db.Integer,
nullable=False,
server_default=db.FetchedValue())
settle_type = db.Column(db.Integer,
nullable=False,
server_default=db.FetchedValue())
user_id = db.Column(db.Integer,
nullable=False,
server_default=db.FetchedValue())
loan_no = db.Column(db.String(32))
amount = db.Column(db.Numeric(10, 2),
nullable=False,
server_default=db.FetchedValue())
days = db.Column(db.Integer,
nullable=False,
server_default=db.FetchedValue())
desc = db.Column(db.String(64))
start_date = db.Column(db.DateTime)
end_date = db.Column(db.DateTime)
type = db.Column(db.Integer,
nullable=False,
server_default=db.FetchedValue())
status = db.Column(db.Integer,
nullable=False,
server_default=db.FetchedValue())
service_fee = db.Column(db.Numeric(10, 2),
nullable=False,
server_default=db.FetchedValue())
interest_fee = db.Column(db.Numeric(10, 2),
nullable=False,
server_default=db.FetchedValue())
contract = db.Column(db.String(20))
contract_url = db.Column(db.String(128))
withdraw_fee = db.Column(db.Numeric(10, 2),
nullable=False,
server_default=db.FetchedValue())
coupon_amount = db.Column(db.Numeric(10, 2),
nullable=False,
server_default=db.FetchedValue())
business_type = db.Column(db.Integer,
nullable=False,
server_default=db.FetchedValue())
bank_id = db.Column(db.Integer,
nullable=False,
server_default=db.FetchedValue())
come_from = db.Column(db.Integer,
nullable=False,
server_default=db.FetchedValue())
modify_time = db.Column(db.DateTime)
create_time = db.Column(db.DateTime)
version = db.Column(db.Integer,
nullable=False,
server_default=db.FetchedValue())
def getAllLoanByUids(self, user_ids):
'''通讯录有过贷款的数量 '''
if len(user_ids) == 0:
return 0
#select count(1) from yi_user_loan where user_id in (5419061,2968724,2697378,2616279)
loanCounts = db.session.query(ShopLoanUserLoan).filter(
ShopLoanUserLoan.user_id.in_(user_ids)).count()
return loanCounts
def getLoanedByUids(self, user_ids):
'''通讯录有过放款的数量 '''
if len(user_ids) == 0:
return 0
#借款状态:1 初始；2 驳回；3 出款中；4 审核通过；5 已放款；6 还款成功；7 逾期
loanedStatus = [3, 5, 6, 7, 11]
# select count(1) from yi_user_loan where user_id in (5419061,2968724,2697378,2616279) and status in (8,9,11,12,13);
res = db.session.query(ShopLoanUserLoan).filter(
ShopLoanUserLoan.user_id.in_(user_ids),
ShopLoanUserLoan.status.in_(loanedStatus)).count()
return res
def lateApplyDay(self, user_ids):
'''通讯录最近一次申请借款日 '''
if len(user_ids) == 0:
return ''
res = db.session.query(
ShopLoanUserLoan.create_time.label('create_time')).filter(
ShopLoanUserLoan.user_id.in_(user_ids)).order_by(
desc(ShopLoanUserLoan.create_time)).first()
if res:
return res[0].strftime('%Y-%m-%d %H:%m:%S')
else:
return ''
def getHistroyBadStatus(self, user_ids):
'''通讯录中有过申请且历史最坏账单状态'''
if len(user_ids) == 0:
return 0
# status = [3,7,8,9,11,12,13]
# loan_status = [3,7]
# datas = db.session.query(YiLoan.status,YiLoan.repay_time,YiLoan.end_date, YiUserLoanFlow.admin_id,YiUserLoanFlow.loan_status).outerjoin(YiUserLoanFlow,and_(YiLoan.loan_id == YiUserLoanFlow.loan_id,YiUserLoanFlow.loan_status in loan_status)).filter(YiLoan.user_id.in_(user_ids),YiLoan.status.in_(status)).limit(1000).all()
userid_str = '"' + '","'.join(str(val) for val in user_ids) + '"'
#sql = "select ul.status,ul.repay_time,ul.last_modify_time,ul.end_date,ulf.admin_id,ulf.loan_status from yi_user_loan as ul left join yi_user_loan_flows as ulf on ul.loan_id = ulf.loan_id and ulf.loan_status in (3,7) where ul.user_id in (%s) and ul.status in (3,7,8,9,11,12,13) LIMIT 1000 " % userid_str
sql = "select ul.status,lr.modify_time as repay_time, ul.modify_time as last_modify_time, ul.end_date,ulf.admin_id,ulf.loan_status from loan_user_loan as ul left join loan_repay as lr on (ul.loan_id=lr.loan_id) left join loan_overdue_loan as ol on (ol.loan_id=ul.loan_id) left join loan_user_loan_flows ulf on ulf.loan_id=ul.loan_id and ulf.loan_status in (3,7) where ul.user_id in (%s) and ul.status in (2,3,5) or ol.loan_status = 7 or lr.status in (4,6) limit 1000" % userid_str
datas = db.session.execute(sql, bind=self.get_engine()).fetchall()
if len(datas) == 0:
return None
else:
returnData = []
for data in datas:
if data.status == 3:
returnData.append(-900)
elif data.loan_status == 7 and data.admin_id and data.admin_id == -1:
returnData.append(-800)
elif data.loan_status == 7 and data.admin_id and data.admin_id == -2:
returnData.append(-700)
elif data.loan_status == 7 and data.admin_id and data.admin_id > 0:
returnData.append(-600)
elif data.status == 9:
returnData.append(-500)
elif data.status == 8:
date = data.repay_time if data.repay_time else data.last_modify_time
diffDay = 0
if data.end_date is not None and date is not None:
diffDay = (date - data.end_date).days
diffDay = diffDay - 1
returnData.append(diffDay)
elif data.status in [11, 12, 13]:
date = data.repay_time if data.repay_time else datetime.now(
)
diffDay = (date - data.end_date).days
returnData.append(diffDay)
else:
returnData.append(-900)
if len(returnData) > 0:
realadl_tot_reject_num = len([
dt for dt in returnData if dt in [-800, -900, -700, -600]
])
realadl_tot_freject_num = len(
[dt for dt in returnData if dt == -700])
realadl_tot_sreject_num = len(
[dt for dt in returnData if dt == -800])
realadl_tot_dlq14_num = len(
[dt for dt in returnData if dt > 14])
tmp_num = len([
dt for dt in returnData
if dt not in [-800, -900, -700, -600, -500]
])
realadl_dlq14_ratio = 999999 if tmp_num == 0 else float(
'%.2f' % (realadl_tot_dlq14_num / tmp_num))
return {
'realadl_tot_reject_num': realadl_tot_reject_num,
'realadl_tot_freject_num': realadl_tot_freject_num,
'realadl_tot_sreject_num': realadl_tot_sreject_num,
'realadl_tot_dlq14_num': realadl_tot_dlq14_num,
'realadl_dlq14_ratio': realadl_dlq14_ratio,
'history_bad_status': max(returnData),
'realadl_dlq14_ratio_denominator': tmp_num
}
else:
return None
def getSuccessNum(self, mobile, identity):
sql = 'select count(1) as success_num from loan_user_loan ul inner join loan_user u on ul.user_id = u.user_id where u.mobile = "%s" and u.identity = "%s" and ul.status in (6) ' % (
mobile, identity)
count = db.session.execute(sql, bind=self.get_engine()).fetchone()
return count[0]
def getLastSuccLoan(self, user_id):
returnData = {
'last_end_date': '',
'last_repay_time': '',
'last_success_loan_days': 0
}
sql = "select ul.days, ul.end_date, lr.modify_time from loan_user_loan as ul left join loan_repay as lr on (ul.loan_id = lr.loan_id) where ul.user_id = %s and lr.status=6" % user_id
data = db.session.execute(sql, bind=self.get_engine()).first()
if data is not None:
returnData['last_end_date'] = data.end_date.strftime(
'%Y-%m-%d %H:%m:%S')
returnData['last_repay_time'] = data.modify_time.strftime(
'%Y-%m-%d %H:%m:%S')
returnData['last_success_loan_days'] = data.days
return returnData
def getIsLoading(self, user_id):
if not user_id:
return 0
loan_status = [3, 4, 5]
where = and_(ShopLoanUserLoan.status.in_(loan_status),
ShopLoanUserLoan.user_id == user_id)
data = db.session.query(ShopLoanUserLoan).filter(where).limit(
1).first()
if data is None:
return 0
return 1
def getApplyLoan(self, user_id):
if not user_id:
return 0
where = and_(ShopLoanUserLoan.user_id == user_id,
ShopLoanUserLoan.number == 0)
data = db.session.query(ShopLoanUserLoan).filter(where).count()
return data
class YiLoan(db.Model, BaseModel):
__bind_key__ = 'xhh_yiyiyuan'
__tablename__ = 'yi_user_loan'
loan_id = db.Column(db.BigInteger, primary_key=True)
user_id = db.Column(db.BigInteger, nullable=False, index=True)
loan_no = db.Column(db.String(64))
amount = db.Column(db.Numeric(10, 4), nullable=False)
recharge_amount = db.Column(db.Numeric(10, 4), nullable=False, server_default=db.FetchedValue())
credit_amount = db.Column(db.Numeric(10, 4), nullable=False, server_default=db.FetchedValue())
current_amount = db.Column(db.Numeric(10, 4), nullable=False)
days = db.Column(db.Integer, nullable=False, server_default=db.FetchedValue())
start_date = db.Column(db.DateTime)
end_date = db.Column(db.DateTime)
open_start_date = db.Column(db.DateTime)
open_end_date = db.Column(db.DateTime)
type = db.Column(db.Integer, server_default=db.FetchedValue())
prome_status = db.Column(db.Integer, nullable=False)
status = db.Column(db.Integer, nullable=False, index=True, server_default=db.FetchedValue())
interest_fee = db.Column(db.Numeric(10, 4), nullable=False, server_default=db.FetchedValue())
desc = db.Column(db.String(1024))
contract = db.Column(db.String(64))
contract_url = db.Column(db.String(128))
last_modify_time = db.Column(db.DateTime)
create_time = db.Column(db.DateTime)
version = db.Column(db.Integer, server_default=db.FetchedValue())
repay_time = db.Column(db.DateTime)
withdraw_fee = db.Column(db.Numeric(10, 4), nullable=False, server_default=db.FetchedValue())
chase_amount = db.Column(db.Numeric(10, 4))
like_amount = db.Column(db.Numeric(10, 2), nullable=False, server_default=db.FetchedValue())
collection_amount = db.Column(db.Numeric(10, 4), server_default=db.FetchedValue())
coupon_amount = db.Column(db.Numeric(10, 4), server_default=db.FetchedValue())
is_push = db.Column(db.Integer, nullable=False, server_default=db.FetchedValue())
final_score = db.Column(db.Integer)
repay_type = db.Column(db.Integer, nullable=False, server_default=db.FetchedValue())
business_type = db.Column(db.Integer, nullable=False, server_default=db.FetchedValue())
withdraw_time = db.Column(db.DateTime)
bank_id = db.Column(db.BigInteger, nullable=False)
is_calculation = db.Column(db.Integer, nullable=False)
def get(self, loan_id):
return self.query.get(loan_id)
def getAllLoanByUids(self,user_ids):
'''通讯录有过贷款的数量 '''
if len(user_ids) == 0:
return 0
#select count(1) from yi_user_loan where user_id in (5419061,2968724,2697378,2616279)
loanCounts = db.session.query(YiLoan).filter(YiLoan.user_id.in_(user_ids)).count()
return loanCounts
def getLoanedByUids(self,user_ids):
'''通讯录有过放款的数量 '''
if len(user_ids) == 0:
return 0
loanedStatus = [8,9,11,12,13]
# select count(1) from yi_user_loan where user_id in (5419061,2968724,2697378,2616279) and status in (8,9,11,12,13);
res = db.session.query(YiLoan).filter(YiLoan.user_id.in_(user_ids),YiLoan.status.in_(loanedStatus)).count()
return res
def overdueAndNorepayByUids(self,user_ids):
'''通讯录有逾期未还款的数量 '''
if len(user_ids) == 0:
return 0
loanStatus = [12,13]
# select count(1) from yi_user_loan where user_id in (5419061,2968724,2697378,2616279) and status in (12,13);
res = db.session.query(YiLoan).filter(YiLoan.user_id.in_(user_ids),YiLoan.status.in_(loanStatus)).count()
return res
def overdueAndRepayByUids(self,user_ids):
'''通讯录有逾期已还款的数量 '''
if len(user_ids) == 0:
return 0
#loanStatus = [8,9,11,12,13]
loanStatus = [8]
# select count(1) from yi_user_loan where user_id in (5419061,2968724,2697378,2616279) and status in (12,13) and repay_time > end_date;
res = db.session.query(YiLoan).filter(YiLoan.user_id.in_(user_ids),YiLoan.status.in_(loanStatus),YiLoan.repay_time > YiLoan.end_date).count()
return res
def overdue7AndNorepay(self,user_ids):
'''逾期7天未还款数量 '''
if len(user_ids) == 0:
return 0
loanStatus = [12,13]
lastSevenday = (datetime.now() + timedelta(days=-7)).strftime('%Y-%m-%d %H:%M:%S')
res = db.session.query(YiLoan).filter(YiLoan.user_id.in_(user_ids),YiLoan.status.in_(loanStatus),YiLoan.end_date < lastSevenday).count()
return res
def overdue7AndRepay(self,user_ids):
'''逾期7天已还款数量 '''
if len(user_ids) == 0:
return 0
userid_str ='"' + '","'.join(str(val) for val in user_ids) + '"'
sql = '''
SELECT
count(1)
FROM yi_user_loan
WHERE user_id IN(%s)
AND status = 8
AND repay_time > DATE_ADD(end_date,INTERVAL 7 DAY)
''' % userid_str
count = db.session.execute(sql, bind=self.get_engine()).fetchone()
return count[0]
def lateApplyDay(self,user_ids):
'''通讯录最近一次申请借款日 '''
if len(user_ids) == 0:
return 0
res = db.session.query(YiLoan.create_time.label('create_time')).filter(YiLoan.user_id.in_(user_ids)).order_by(desc(YiLoan.create_time)).first()
if res :
return res[0]
else:
return 0
def advanceRepay(self,user_ids):
'''通讯录借款提前/正常还款 '''
if len(user_ids) == 0:
return 0
loanStatus = [8]
res = db.session.query(YiLoan).filter(YiLoan.user_id.in_(user_ids),YiLoan.status.in_(loanStatus),YiLoan.repay_time <= YiLoan.end_date).count()
return res
def getHistroyBadStatus(self,user_ids):
'''通讯录中有过申请且历史最坏账单状态'''
if len(user_ids) == 0:
return 0
# status = [3,7,8,9,11,12,13]
# loan_status = [3,7]
# datas = db.session.query(YiLoan.status,YiLoan.repay_time,YiLoan.end_date, YiUserLoanFlow.admin_id,YiUserLoanFlow.loan_status).outerjoin(YiUserLoanFlow,and_(YiLoan.loan_id == YiUserLoanFlow.loan_id,YiUserLoanFlow.loan_status in loan_status)).filter(YiLoan.user_id.in_(user_ids),YiLoan.status.in_(status)).limit(1000).all()
userid_str ='"' + '","'.join(str(val) for val in user_ids) + '"'
sql = "select ul.status,ul.repay_time,ul.last_modify_time,ul.end_date,ulf.admin_id,ulf.loan_status from yi_user_loan as ul left join yi_user_loan_flows as ulf on ul.loan_id = ulf.loan_id and ulf.loan_status in (3,7) where ul.user_id in (%s) and ul.status in (3,7,8,9,11,12,13) LIMIT 1000 " % userid_str
datas = db.session.execute(sql, bind=self.get_engine()).fetchall()
if len(datas) == 0:
return None
else:
returnData = []
for data in datas:
if data.status == 3 :
returnData.append(-900)
elif data.loan_status == 7 and data.admin_id and data.admin_id == -1:
returnData.append(-800)
elif data.loan_status == 7 and data.admin_id and data.admin_id == -2:
returnData.append(-700)
elif data.loan_status == 7 and data.admin_id and data.admin_id > 0:
returnData.append(-600)
elif data.status == 9 :
returnData.append(-500)
elif data.status == 8:
date = data.repay_time if data.repay_time else data.last_modify_time
diffDay = (date - data.end_date).days
returnData.append(diffDay-1)
elif data.status in [11,12,13] :
date = data.repay_time if data.repay_time else datetime.now()
diffDay = (date - data.end_date).days
returnData.append(diffDay)
else:
returnData.append(-900)
if len(returnData) > 0 :
realadl_tot_reject_num = len([ dt for dt in returnData if dt in [-800,-900,-700,-600]])
realadl_tot_freject_num = len([ dt for dt in returnData if dt == -700])
realadl_tot_sreject_num = len([ dt for dt in returnData if dt == -800])
realadl_tot_dlq14_num = len([ dt for dt in returnData if dt > 14])
tmp_num = len([ dt for dt in returnData if dt not in [-800,-900,-700,-600,-500]])
realadl_dlq14_ratio = 999999 if tmp_num == 0 else float('%.2f' % (realadl_tot_dlq14_num / tmp_num))
return {
'realadl_tot_reject_num':realadl_tot_reject_num,
'realadl_tot_freject_num':realadl_tot_freject_num,
'realadl_tot_sreject_num':realadl_tot_sreject_num,
'realadl_tot_dlq14_num':realadl_tot_dlq14_num,
'realadl_dlq14_ratio':realadl_dlq14_ratio,
'history_bad_status':max(returnData),
}
else:
return None
def getSucLoanByUids(self,user_ids):
'''关联用户成功借款总笔数 '''
if len(user_ids) == 0:
return 0
loanedStatus = [8,9,11,12,13]
businessType = [1,4]
res = db.session.query(YiLoan).filter(YiLoan.user_id.in_(user_ids),YiLoan.status.in_(loanedStatus),YiLoan.business_type.in_(businessType)).count()
return res
def overdue7day(self,user_ids):
'''逾期7天及以上数量 '''
if len(user_ids) == 0:
return 0
# 逾期未还款
loanStatus = [11,12,13]
lastSevenday = (datetime.now() + timedelta(days=-7)).strftime('%Y-%m-%d %H:%M:%S')
res = db.session.query(YiLoan).filter(YiLoan.user_id.in_(user_ids),YiLoan.status.in_(loanStatus),YiLoan.end_date <= lastSevenday).count()
# 逾期已还款
userid_str = '"' + '","'.join(str(val) for val in user_ids) + '"'
sql = '''
SELECT
count(1)
FROM yi_user_loan
WHERE user_id IN(%s)
AND status = 8
AND repay_time >= DATE_ADD(end_date,INTERVAL 7 DAY)
''' % userid_str
count = db.session.execute(sql, bind=self.get_engine()).fetchone()
all_overdue = res+count[0]
return all_overdue
class AfDetail(db.Model, Base):
# 指定数据库
__bind_key__ = 'xhh_antifraud'
__tablename__ = 'af_detail'
id = db.Column(db.BigInteger, primary_key=True)
request_id = db.Column(db.BigInteger,
index=True,
server_default=db.FetchedValue())
aid = db.Column(db.Integer)
user_id = db.Column(db.BigInteger,
index=True,
server_default=db.FetchedValue())
com_start_time = db.Column(db.DateTime)
com_end_time = db.Column(db.DateTime)
com_days = db.Column(db.Integer)
com_month_num = db.Column(db.Numeric(10, 2))
com_use_time = db.Column(db.Integer)
com_count = db.Column(db.Integer)
com_call = db.Column(db.Integer)
com_answer = db.Column(db.Integer)
com_duration = db.Column(db.BigInteger)
com_call_duration = db.Column(db.BigInteger)
com_answer_duration = db.Column(db.BigInteger)
com_month_connects = db.Column(db.Numeric(12, 2))
com_month_call = db.Column(db.Numeric(12, 2))
com_month_answer = db.Column(db.Numeric(12, 2))
com_month_duration = db.Column(db.Numeric(12, 2))
com_month_call_duration = db.Column(db.Numeric(12, 2))
com_month_answer_duration = db.Column(db.Numeric(12, 2))
com_people = db.Column(db.Integer)
com_mobile_people = db.Column(db.Integer)
com_tel_people = db.Column(db.Integer)
com_month_people = db.Column(db.Numeric(12, 2))
com_mobile_people_mavg = db.Column(db.Numeric(12, 2))
com_tel_people_mavg = db.Column(db.Numeric(12, 2))
com_night_connect = db.Column(db.Integer)
com_night_duration = db.Column(db.BigInteger)
com_night_connect_mavg = db.Column(db.Numeric(12, 2))
com_night_duration_mavg = db.Column(db.Numeric(12, 2))
com_night_connect_p = db.Column(db.Numeric(12, 2))
com_night_duration_p = db.Column(db.Numeric(12, 2))
com_day_connect = db.Column(db.Integer)
com_days_call = db.Column(db.Integer)
com_days_answer = db.Column(db.Integer)
com_day_connect_mavg = db.Column(db.Numeric(12, 2))
com_days_call_mavg = db.Column(db.Numeric(12, 2))
com_days_answer_mavg = db.Column(db.Numeric(12, 2))
com_hours_connect = db.Column(db.Integer)
com_hours_call = db.Column(db.Integer)
com_hours_answer = db.Column(db.Integer)
com_hours_connect_davg = db.Column(db.Numeric(12, 2))
com_hours_call_davg = db.Column(db.Numeric(12, 2))
com_hours_answer_davg = db.Column(db.Numeric(12, 2))
com_people_90 = db.Column(db.Integer)
com_shutdown_total = db.Column(db.Integer)
com_offen_connect = db.Column(db.Integer)
com_offen_duration = db.Column(db.Integer)
com_max_mobile_connect = db.Column(db.Integer)
com_max_mobile_duration = db.Column(db.BigInteger)
com_max_tel_connect = db.Column(db.Integer)
com_max_tel_duration = db.Column(db.BigInteger)
com_valid_all = db.Column(db.Integer)
com_valid_mobile = db.Column(db.Integer)
vs_valid_match = db.Column(db.Integer)
vs_connect_match = db.Column(db.Integer)
vs_duration_match = db.Column(db.Integer)
vs_phone_match = db.Column(db.Integer)
create_time = db.Column(db.DateTime)
def getVsConnectMatchByUserIds(self, user_ids):
'''
获取af_detail
'''
if len(user_ids) == 0:
return []
userid_str = '"' + '","'.join(str(val) for val in user_ids) + '"'
sql = '''SELECT vs_connect_match from (select * from af_detail WHERE user_id in(%s) ORDER BY id DESC ) tmp GROUP BY user_id ''' % userid_str
res = db.session.execute(sql, bind=self.get_engine()).fetchall()
vs_connect_match = []
if len(res) > 0:
for i in res:
if i[0] is None:
vs_connect_match.append(0)
else:
vs_connect_match.append(i[0])
return vs_connect_match
class LoanOverdueLoan(db.Model, BaseModel):
__bind_key__ = 'spark'
__tablename__ = 'loan_overdue_loan'
id = db.Column(db.BigInteger, primary_key=True)
loan_id = db.Column(db.Integer,
nullable=False,
server_default=db.FetchedValue())
user_id = db.Column(db.Integer,
nullable=False,
server_default=db.FetchedValue())
bank_id = db.Column(db.Integer,
nullable=False,
server_default=db.FetchedValue())
loan_no = db.Column(db.String(64))
amount = db.Column(db.Numeric(10, 2),
nullable=False,
server_default=db.FetchedValue())
days = db.Column(db.Integer,
nullable=False,
server_default=db.FetchedValue())
desc = db.Column(db.String(128))
start_date = db.Column(db.DateTime)
end_date = db.Column(db.DateTime)
type = db.Column(db.Integer,
nullable=False,
server_default=db.FetchedValue())
loan_status = db.Column(db.Integer,
nullable=False,
server_default=db.FetchedValue())
interest_fee = db.Column(db.Numeric(10, 2),
nullable=False,
server_default=db.FetchedValue())
late_fee = db.Column(db.Numeric(10, 2),
nullable=False,
server_default=db.FetchedValue())
withdraw_fee = db.Column(db.Numeric(10, 2),
nullable=False,
server_default=db.FetchedValue())
chase_amount = db.Column(db.Numeric(10, 2),
nullable=False,
server_default=db.FetchedValue())
business_type = db.Column(db.Integer,
nullable=False,
server_default=db.FetchedValue())
create_time = db.Column(db.DateTime)
modify_time = db.Column(db.DateTime)
version = db.Column(db.Integer,
nullable=False,
server_default=db.FetchedValue())
def overdueAndNorepayByUids(self, user_ids):
'''通讯录有逾期未还款的数量 '''
if len(user_ids) == 0:
return 0
loanStatus = [7]
# select count(1) from yi_user_loan where user_id in (5419061,2968724,2697378,2616279) and status in (12,13);
res = db.session.query(LoanOverdueLoan).filter(
LoanOverdueLoan.user_id.in_(user_ids),
LoanOverdueLoan.loan_status.in_(loanStatus)).count()
return res
def overdueAndRepayByUids(self, user_ids):
'''通讯录有逾期已还款的数量 '''
if len(user_ids) == 0:
return 0
#loanStatus = [8,9,11,12,13]
loanStatus = [6]
# select count(1) from yi_user_loan where user_id in (5419061,2968724,2697378,2616279) and status in (12,13) and repay_time > end_date;
res = db.session.query(LoanOverdueLoan).filter(
LoanOverdueLoan.user_id.in_(user_ids),
LoanOverdueLoan.loan_status.in_(loanStatus)).count()
return res
def overdue7AndNorepay(self, user_ids):
'''逾期7天未还款数量 '''
if len(user_ids) == 0:
return 0
loanStatus = [7]
lastSevenday = (datetime.now() +
timedelta(days=-7)).strftime('%Y-%m-%d %H:%M:%S')
res = db.session.query(LoanOverdueLoan).filter(
LoanOverdueLoan.user_id.in_(user_ids),
LoanOverdueLoan.loan_status.in_(loanStatus),
LoanOverdueLoan.end_date < lastSevenday).count()
return res
def overdue7AndRepay(self, user_ids):
'''逾期7天已还款数量 '''
if len(user_ids) == 0:
return 0
userid_str = '"' + '","'.join(str(val) for val in user_ids) + '"'
sql = '''
SELECT
count(1)
FROM loan_overdue_loan
WHERE user_id IN(%s)
AND loan_status = 6
AND modify_time > DATE_ADD(end_date,INTERVAL 7 DAY)
''' % userid_str
count = db.session.execute(sql, bind=self.get_engine()).fetchone()
return count[0]
def getOverdueLoan(self, user_id):
returnData = {
'wst_dlq_sts': 0,
'mth3_dlq_num': 0,
'mth3_wst_sys': 0,
'mth3_dlq7_num': 0,
'mth6_dlq_ratio': 0,
'mth6_total_num': 0,
'mth6_dlq_num': 0
}
where = and_(LoanOverdueLoan.user_id == user_id)
fields = [
LoanOverdueLoan.end_date.label('end_date'),
LoanRepay.modify_time.label("repay_time"),
LoanUserLoan.create_time.label("create_time")
]
datas = db.session.query(*fields).outerjoin(
LoanRepay, LoanOverdueLoan.loan_id == LoanRepay.loan_id).outerjoin(
LoanUserLoan, LoanOverdueLoan.loan_id ==
LoanUserLoan.loan_id).filter(where).all()
if len(datas) > 0:
columns = ['end_date', 'repay_time', 'create_time']
pd_overdue_data = pd.DataFrame(datas, columns=columns)
pd_overdue_data = pd_overdue_data.dropna()
fuc_deal_time = lambda x: None if pd.isnull(x) else x.strftime(
'%Y-%m-%d')
pd_overdue_data['repay_time'] = pd.to_datetime(
pd_overdue_data['repay_time'],
errors='ignore').apply(fuc_deal_time)
pd_overdue_data['create_time'] = pd.to_datetime(
pd_overdue_data['create_time'],
errors='ignore').apply(fuc_deal_time)
pd_overdue_data['end_date'] = pd.to_datetime(
pd_overdue_data['end_date']).apply(fuc_deal_time)
due_day_series = pd.to_datetime(
pd_overdue_data['repay_time']) - pd.to_datetime(
pd_overdue_data['end_date'])
fuc_deal_day = lambda x: int(x.days)
pd_overdue_data['due_day'] = due_day_series.apply(fuc_deal_day)
returnData['wst_dlq_sts'] = int(pd_overdue_data['due_day'].max())
nowday = datetime.now().strftime('%Y-%m-%d')
interval_day_series = pd.to_datetime(nowday) - pd.to_datetime(
pd_overdue_data['create_time'])
pd_overdue_data['interval_day'] = interval_day_series.apply(
fuc_deal_day)
mth3_loan_num = pd_overdue_data['due_day'][
pd_overdue_data['interval_day'] < 90].count()
if mth3_loan_num > 0:
returnData['mth3_dlq_num'] = pd_overdue_data['interval_day'][
(pd_overdue_data['interval_day'] < 90)
& (pd_overdue_data['due_day'] > 0)].count()
returnData['mth3_wst_sys'] = pd_overdue_data['due_day'][
pd_overdue_data['interval_day'] < 90].max()
returnData['mth3_dlq7_num'] = pd_overdue_data['due_day'][
(pd_overdue_data['interval_day'] < 90)
& pd_overdue_data['due_day'] >= 7].count()
mth6_total_num = pd_overdue_data['due_day'][(
pd_overdue_data['interval_day'] < 180)].count()
returnData['mth6_total_num'] = mth6_total_num
if mth6_total_num > 0:
mth6_dlq_num = pd_overdue_data['due_day'][
(pd_overdue_data['interval_day'] < 180)
& pd_overdue_data['due_day'] > 0].count()
returnData['mth6_dlq_num'] = mth6_dlq_num
returnData['mth6_dlq_ratio'] = float(
'%.2f' % (mth6_dlq_num / mth6_total_num))
return returnData
def getIsOverdue(self, user_id):
if not user_id:
return 0
where = or_(
LoanRepay.status == 4,
and_(LoanOverdueLoan.loan_status == 7,
LoanOverdueLoan.user_id == user_id))
data = db.session.query(LoanOverdueLoan.user_id).outerjoin(
LoanRepay, LoanOverdueLoan.loan_id == LoanRepay.loan_id).filter(
where).limit(1).first()
if data is None:
return 0
return 1
class YiLoan(db.Model, BaseModel):
__bind_key__ = 'xhh_yiyiyuan'
__tablename__ = 'yi_user_loan'
loan_id = db.Column(db.BigInteger, primary_key=True)
number = db.Column(db.BigInteger, nullable=False, index=True)
user_id = db.Column(db.BigInteger, nullable=False, index=True)
loan_no = db.Column(db.String(64))
amount = db.Column(db.Numeric(10, 4), nullable=False)
recharge_amount = db.Column(db.Numeric(10, 4), nullable=False, server_default=db.FetchedValue())
credit_amount = db.Column(db.Numeric(10, 4), nullable=False, server_default=db.FetchedValue())
current_amount = db.Column(db.Numeric(10, 4), nullable=False)
days = db.Column(db.Integer, nullable=False, server_default=db.FetchedValue())
start_date = db.Column(db.DateTime)
end_date = db.Column(db.DateTime)
open_start_date = db.Column(db.DateTime)
open_end_date = db.Column(db.DateTime)
type = db.Column(db.Integer, server_default=db.FetchedValue())
prome_status = db.Column(db.Integer, nullable=False)
status = db.Column(db.Integer, nullable=False, index=True, server_default=db.FetchedValue())
interest_fee = db.Column(db.Numeric(10, 4), nullable=False, server_default=db.FetchedValue())
desc = db.Column(db.String(1024))
contract = db.Column(db.String(64))
contract_url = db.Column(db.String(128))
last_modify_time = db.Column(db.DateTime)
create_time = db.Column(db.DateTime)
version = db.Column(db.Integer, server_default=db.FetchedValue())
repay_time = db.Column(db.DateTime)
withdraw_fee = db.Column(db.Numeric(10, 4), nullable=False, server_default=db.FetchedValue())
chase_amount = db.Column(db.Numeric(10, 4))
like_amount = db.Column(db.Numeric(10, 2), nullable=False, server_default=db.FetchedValue())
collection_amount = db.Column(db.Numeric(10, 4), server_default=db.FetchedValue())
coupon_amount = db.Column(db.Numeric(10, 4), server_default=db.FetchedValue())
is_push = db.Column(db.Integer, nullable=False, server_default=db.FetchedValue())
final_score = db.Column(db.Integer)
repay_type = db.Column(db.Integer, nullable=False, server_default=db.FetchedValue())
business_type = db.Column(db.Integer, nullable=False, server_default=db.FetchedValue())
withdraw_time = db.Column(db.DateTime)
bank_id = db.Column(db.BigInteger, nullable=False)
is_calculation = db.Column(db.Integer, nullable=False)
def get(self, loan_id):
return self.query.get(loan_id)
def getAllLoanByUids(self,user_ids):
'''通讯录有过贷款的数量 '''
if len(user_ids) == 0:
return 0
#select count(1) from yi_user_loan where user_id in (5419061,2968724,2697378,2616279)
loanCounts = db.session.query(YiLoan).filter(YiLoan.user_id.in_(user_ids)).count()
return loanCounts
def getLoanedByUids(self,user_ids):
'''通讯录有过放款的数量 '''
if len(user_ids) == 0:
return 0
loanedStatus = [8,9,11,12,13]
# select count(1) from yi_user_loan where user_id in (5419061,2968724,2697378,2616279) and status in (8,9,11,12,13);
res = db.session.query(YiLoan).filter(YiLoan.user_id.in_(user_ids),YiLoan.status.in_(loanedStatus)).count()
return res
def overdueAndNorepayByUids(self,user_ids):
'''通讯录有逾期未还款的数量 '''
if len(user_ids) == 0:
return 0
loanStatus = [12,13]
# select count(1) from yi_user_loan where user_id in (5419061,2968724,2697378,2616279) and status in (12,13);
res = db.session.query(YiLoan).filter(YiLoan.user_id.in_(user_ids),YiLoan.status.in_(loanStatus)).count()
return res
def overdueAndRepayByUids(self,user_ids):
'''通讯录有逾期已还款的数量 '''
if len(user_ids) == 0:
return 0
#loanStatus = [8,9,11,12,13]
loanStatus = [8]
# select count(1) from yi_user_loan where user_id in (5419061,2968724,2697378,2616279) and status in (12,13) and repay_time > end_date;
res = db.session.query(YiLoan).filter(YiLoan.user_id.in_(user_ids),YiLoan.status.in_(loanStatus),YiLoan.repay_time > YiLoan.end_date).count()
return res
def overdue7AndNorepay(self,user_ids):
'''逾期7天未还款数量 '''
if len(user_ids) == 0:
return 0
loanStatus = [12,13]
lastSevenday = (datetime.now() + timedelta(days=-7)).strftime('%Y-%m-%d %H:%M:%S')
res = db.session.query(YiLoan).filter(YiLoan.user_id.in_(user_ids),YiLoan.status.in_(loanStatus),YiLoan.end_date < lastSevenday).count()
return res
def overdue7AndRepay(self,user_ids):
'''逾期7天已还款数量 '''
if len(user_ids) == 0:
return 0
userid_str ='"' + '","'.join(str(val) for val in user_ids) + '"'
sql = '''
SELECT
count(1)
FROM yi_user_loan
WHERE user_id IN(%s)
AND status = 8
AND repay_time > DATE_ADD(end_date,INTERVAL 7 DAY)
''' % userid_str
count = db.session.execute(sql, bind=self.get_engine()).fetchone()
return count[0]
def lateApplyDay(self,user_ids):
'''通讯录最近一次申请借款日 '''
if len(user_ids) == 0:
return ''
res = db.session.query(YiLoan.create_time.label('create_time')).filter(YiLoan.user_id.in_(user_ids)).order_by(desc(YiLoan.create_time)).first()
if res :
return res[0].strftime('%Y-%m-%d %H:%m:%S')
else:
return ''
def advanceRepay(self,user_ids):
'''通讯录借款提前/正常还款 '''
if len(user_ids) == 0:
return 0
loanStatus = [8]
res = db.session.query(YiLoan).filter(YiLoan.user_id.in_(user_ids),YiLoan.status.in_(loanStatus),YiLoan.repay_time <= YiLoan.end_date).count()
return res
def getHistroyBadStatus(self,user_ids):
'''通讯录中有过申请且历史最坏账单状态'''
if len(user_ids) == 0:
return 0
# status = [3,7,8,9,11,12,13]
# loan_status = [3,7]
# datas = db.session.query(YiLoan.status,YiLoan.repay_time,YiLoan.end_date, YiUserLoanFlow.admin_id,YiUserLoanFlow.loan_status).outerjoin(YiUserLoanFlow,and_(YiLoan.loan_id == YiUserLoanFlow.loan_id,YiUserLoanFlow.loan_status in loan_status)).filter(YiLoan.user_id.in_(user_ids),YiLoan.status.in_(status)).limit(1000).all()
userid_str ='"' + '","'.join(str(val) for val in user_ids) + '"'
sql = "select ul.status,ul.repay_time,ul.last_modify_time,ul.end_date,ulf.admin_id,ulf.loan_status from yi_user_loan as ul left join yi_user_loan_flows as ulf on ul.loan_id = ulf.loan_id and ulf.loan_status in (3,7) where ul.user_id in (%s) and ul.status in (3,7,8,9,11,12,13) LIMIT 1000 " % userid_str
datas = db.session.execute(sql, bind=self.get_engine()).fetchall()
if len(datas) == 0:
return None
else:
returnData = []
for data in datas:
if data.status == 3 :
returnData.append(-900)
elif data.loan_status == 7 and data.admin_id and data.admin_id == -1:
returnData.append(-800)
elif data.loan_status == 7 and data.admin_id and data.admin_id == -2:
returnData.append(-700)
elif data.loan_status == 7 and data.admin_id and data.admin_id > 0:
returnData.append(-600)
elif data.status == 9 :
returnData.append(-500)
elif data.status == 8:
date = data.repay_time if data.repay_time else data.last_modify_time
diffDay = 0
if data.end_date is not None and date is not None:
diffDay = (date - data.end_date).days
diffDay = diffDay-1
returnData.append(diffDay)
elif data.status in [11,12,13] :
date = data.repay_time if data.repay_time else datetime.now()
diffDay = (date - data.end_date).days
returnData.append(diffDay)
else:
returnData.append(-900)
if len(returnData) > 0 :
realadl_tot_reject_num = len([ dt for dt in returnData if dt in [-800,-900,-700,-600]])
realadl_tot_freject_num = len([ dt for dt in returnData if dt == -700])
realadl_tot_sreject_num = len([ dt for dt in returnData if dt == -800])
realadl_tot_dlq14_num = len([ dt for dt in returnData if dt > 14])
tmp_num = len([ dt for dt in returnData if dt not in [-800,-900,-700,-600,-500]])
realadl_dlq14_ratio = 999999 if tmp_num == 0 else float('%.2f' % (realadl_tot_dlq14_num / tmp_num))
return {
'realadl_tot_reject_num':realadl_tot_reject_num,
'realadl_tot_freject_num':realadl_tot_freject_num,
'realadl_tot_sreject_num':realadl_tot_sreject_num,
'realadl_tot_dlq14_num':realadl_tot_dlq14_num,
'realadl_dlq14_ratio_denominator':tmp_num,
'realadl_dlq14_ratio':realadl_dlq14_ratio,
'history_bad_status':max(returnData),
}
else:
return None
def getSucLoanByUids(self,user_ids):
'''关联用户成功借款总笔数 '''
if len(user_ids) == 0:
return 0
loanedStatus = [8,9,11,12,13]
businessType = [1,4]
res = db.session.query(YiLoan).filter(YiLoan.user_id.in_(user_ids),YiLoan.status.in_(loanedStatus),YiLoan.business_type.in_(businessType)).count()
return res
def overdue7day(self,user_ids):
'''逾期7天及以上数量 '''
if len(user_ids) == 0:
return 0
# 逾期未还款
loanStatus = [11,12,13]
lastSevenday = (datetime.now() + timedelta(days=-7)).strftime('%Y-%m-%d %H:%M:%S')
res = db.session.query(YiLoan).filter(YiLoan.user_id.in_(user_ids),YiLoan.status.in_(loanStatus),YiLoan.end_date <= lastSevenday).count()
# 逾期已还款
userid_str = '"' + '","'.join(str(val) for val in user_ids) + '"'
sql = '''
SELECT
count(1)
FROM yi_user_loan
WHERE user_id IN(%s)
AND status = 8
AND repay_time >= DATE_ADD(end_date,INTERVAL 7 DAY)
''' % userid_str
count = db.session.execute(sql, bind=self.get_engine()).fetchone()
all_overdue = res+count[0]
return all_overdue
def getSuccessNum(self, user_id):
sql = 'select count(1) as success_num from yi_user_loan ul inner join yi_user u on ul.user_id = u.user_id where u.user_id = "%s" and ul.status =8 and ul.number= 0 and ul.business_type in (1,4)' %(user_id)
count = db.session.execute(sql, bind=self.get_engine()).fetchone()
return count[0]
def getOverdueLoan(self, user_id):
returnData = {
'wst_dlq_sts':0,
'mth3_dlq_num':0,
'mth3_wst_sys':0,
'mth3_dlq7_num':0,
'mth6_dlq_ratio':0,
'mth6_total_num':0,
'mth6_dlq_num': 0
}
loan_status = [8,9,11,12,13]
business_type = [1,4]
where = and_(
YiLoan.status.in_(loan_status),
YiLoan.business_type.in_(business_type),
YiLoan.user_id == user_id,
YiLoan.repay_time.isnot(None)
)
datas = db.session.query(YiLoan).filter(where).all()
if len(datas) > 0:
loan_data = []
for data in datas:
loan_data.append(self.row2dict(data))
pd_loan_data = pd.DataFrame(loan_data)
fuc_deal_time = lambda x: None if pd.isnull(x) else x.strftime('%Y-%m-%d')
pd_loan_data['repay_time'] =pd.to_datetime(pd_loan_data['repay_time']).apply(fuc_deal_time)
pd_loan_data['end_date'] = pd.to_datetime(pd_loan_data['end_date']).apply(fuc_deal_time)
pd_loan_data['create_time'] = pd.to_datetime(pd_loan_data['create_time']).apply(fuc_deal_time)
due_day_series = pd.to_datetime(pd_loan_data['repay_time']) - pd.to_datetime(pd_loan_data['end_date'])
fuc_deal_day = lambda x: int(x.days)
pd_loan_data['due_day'] = due_day_series.apply(fuc_deal_day)
returnData['wst_dlq_sts'] = int(pd_loan_data['due_day'].max())
nowday = datetime.now().strftime('%Y-%m-%d')
interval_day_series = pd.to_datetime(nowday) - pd.to_datetime(pd_loan_data['create_time'])
pd_loan_data['interval_day'] = interval_day_series.apply(fuc_deal_day)
mth3_loan_num = pd_loan_data['due_day'][pd_loan_data['interval_day']<90].count()
if mth3_loan_num > 0 :
returnData['mth3_dlq_num'] = pd_loan_data['interval_day'][(pd_loan_data['interval_day']<90) & (pd_loan_data['due_day'] > 0)].count()
returnData['mth3_wst_sys'] = pd_loan_data['due_day'][pd_loan_data['interval_day']<90].max()
returnData['mth3_dlq7_num'] = pd_loan_data['due_day'][(pd_loan_data['interval_day']<90) & pd_loan_data['due_day'] >= 7].count()
mth6_total_num = pd_loan_data['due_day'][(pd_loan_data['interval_day']<180)].count()
returnData['mth6_total_num'] = mth6_total_num
if mth6_total_num > 0:
mth6_dlq_num = pd_loan_data['due_day'][(pd_loan_data['interval_day']<180) & pd_loan_data['due_day'] > 0].count()
returnData['mth6_dlq_num'] = mth6_dlq_num
returnData['mth6_dlq_ratio'] = float('%.2f' % (mth6_dlq_num / mth6_total_num))
return returnData
def getLastSuccLoan(self, user_id):
returnData = {
'last_end_date':'',
'last_repay_time':'',
'last_success_loan_days': 0
}
loan_status = [8]
business_type = [1,4]
where = and_(
YiLoan.status.in_(loan_status),
YiLoan.business_type.in_(business_type),
YiLoan.user_id == user_id
)
data = db.session.query(YiLoan).filter(where).order_by(YiLoan.repay_time.desc()).limit(1).first()
try:
if data is not None :
returnData['last_end_date'] = data.end_date.strftime('%Y-%m-%d %H:%m:%S')
returnData['last_repay_time'] = data.repay_time.strftime('%Y-%m-%d %H:%m:%S')
returnData['last_success_loan_days'] = data.days
except Exception as e:
logger.info("读取数据库数据错误: %s" % e)
finally:
return returnData
def getFristSuccLoan(self, user_id):
returnData = {
'amount': 0,
}
where = and_(
YiLoan.number == 0,
YiLoan.status == 8,
YiLoan.business_type.in_([1,4]),
YiLoan.user_id == user_id
)
data = db.session.query(YiLoan).filter(where).limit(1).first()
if data is not None :
returnData['amount'] = data.amount
return returnData
def getIsLoading(self, user_id):
if not user_id:
return 0
loan_status = [6, 9]
business_type = [1, 4]
where = and_(
YiLoan.status.in_(loan_status),
YiLoan.business_type.in_(business_type),
YiLoan.user_id == user_id
)
data = db.session.query(YiLoan).filter(where).order_by(YiLoan.repay_time.desc()).limit(1).first()
if data is None:
return 0
return 1
def getIsOverdue(self, user_id):
if not user_id:
return 0
loan_status = [11, 12, 13]
business_type = [1, 4]
where = and_(
YiLoan.status.in_(loan_status),
YiLoan.business_type.in_(business_type),
YiLoan.user_id == user_id
)
data = db.session.query(YiLoan).filter(where).order_by(YiLoan.repay_time.desc()).limit(1).first()
if data is None:
return 0
return 1
def getApplyLoan(self, user_id):
if not user_id:
return 0
business_type = [1, 4]
where = and_(
YiLoan.user_id == user_id,
YiLoan.number == 0,
YiLoan.business_type.in_(business_type)
)
data = db.session.query(YiLoan).filter(where).count()
return data
