class YiUserExtend(db.Model, BaseModel):
__bind_key__ = 'xhh_yiyiyuan'
__tablename__ = 'yi_user_extend'
id = db.Column(db.BigInteger, primary_key=True)
user_id = db.Column(db.BigInteger, nullable=False)
uuid = db.Column(db.String(32))
school_valid = db.Column(db.Integer, server_default=db.FetchedValue())
school_id = db.Column(db.Integer, server_default=db.FetchedValue())
school = db.Column(db.String(64))
edu = db.Column(db.String(64))
school_time = db.Column(db.String(64))
industry = db.Column(db.Integer)
company = db.Column(db.String(128))
position = db.Column(db.String(128))
profession = db.Column(db.String(128))
telephone = db.Column(db.String(32))
marriage = db.Column(db.Integer)
email = db.Column(db.String(32))
income = db.Column(db.String(32))
home_area = db.Column(db.Integer)
home_address = db.Column(db.String(128))
company_area = db.Column(db.Integer)
company_address = db.Column(db.String(128))
version = db.Column(db.Integer)
is_new = db.Column(db.Integer, server_default=db.FetchedValue())
is_callback = db.Column(db.Integer, server_default=db.FetchedValue())
reg_ip = db.Column(db.String(16))
last_modify_time = db.Column(db.DateTime)
create_time = db.Column(db.DateTime)
'''
查找用户信息
'''
def getUserIp(self, user_id):
if not user_id:
return False
return db.session.query(YiUserExtend).filter(
YiUserExtend.user_id == user_id).limit(1).first()
'''
计算ip下有多少用户
'''
def getIpCount(self, ip_str):
if not ip_str:
return False
return db.session.query(YiUserExtend).filter(
YiUserExtend.reg_ip == ip_str).count()
"""
通过ip下所有数据
"""
def getIpData(self, reg_ip, offset, limit_num=100):
res = db.session.query(YiUserExtend).filter(
YiUserExtend.reg_ip == reg_ip).order_by(
YiUserExtend.user_id.asc()).offset(offset).limit(
limit_num).all()
return res
class AfAddress(db.Model, Base):
# 指定数据库
__bind_key__ = 'xhh_antifraud'
__tablename__ = 'af_address'
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())
addr_count = db.Column(db.Integer)
addr_parents_count = db.Column(db.Integer)
addr_phones_nodups = db.Column(db.Integer)
addr_phones_dups = db.Column(db.Integer)
addr_collection_count = db.Column(db.Integer)
addr_loan_count = db.Column(db.Integer)
addr_gamble_count = db.Column(db.Integer)
addr_father_count = db.Column(db.Integer)
addr_mother_count = db.Column(db.Integer)
addr_colleague_count = db.Column(db.Integer)
addr_company_count = db.Column(db.Integer)
addr_name_invalids = db.Column(db.Integer)
addr_myphone_count = db.Column(db.Integer)
addr_tel_count = db.Column(db.Integer)
addr_relative_count = db.Column(db.Integer)
addr_contacts_count = db.Column(db.Integer)
create_time = db.Column(db.DateTime, nullable=False)
class AfResult(db.Model, BaseModel):
# 指定数据库
__bind_key__ = 'xhh_antifraud'
__tablename__ = 'af_result'
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())
setting_id = db.Column(db.BigInteger, nullable=False, server_default=db.FetchedValue())
score = db.Column(db.Integer, server_default=db.FetchedValue())
result_status = db.Column(db.Integer, nullable=False, server_default=db.FetchedValue())
result_subject = db.Column(db.Text, nullable=False)
create_time = db.Column(db.DateTime, nullable=False)
def addResult(self, dict_data):
try:
if dict_data is None or len(dict_data) == 0:
return False
self.request_id = dict_data.get('request_id')
self.user_id = dict_data.get('user_id')
self.aid = dict_data.get('aid',1)
self.setting_id = dict_data.get('setting_id', 0)
self.score = dict_data.get('score', 0)
self.result_status = dict_data.get('result_status')
self.result_subject = dict_data.get('result_subject')
self.create_time = datetime.now()
self.add()
db.session.commit()
return True
except Exception as e:
logger.error("AfResult-addOne:%s" % e)
return False
class AppBlackRoom(db.Model, BaseModel):
# 指定数据库
__bind_key__ = 'analysis_repertory'
__tablename__ = 'app_black_room'
# table model
id = db.Column(db.Integer, primary_key=True)
app_package = db.Column(db.String(100),
index=True,
nullable=False,
server_default=db.FetchedValue())
down_from = db.Column(db.SmallInteger,
nullable=False,
server_default=db.FetchedValue())
over_time = db.Column(db.DateTime, index=True)
def getBlackRoom(self, app_package, down_from):
where = and_(
AppBlackRoom.app_package == app_package,
AppBlackRoom.down_from == down_from,
)
return db.session.query(AppBlackRoom).filter(where).order_by(
desc('id')).limit(1).first()
def putBlackRoom(self, app_package, down_from):
oInfo = self.getBlackRoom(app_package, down_from)
if oInfo is None:
data = {}
data['app_package'] = app_package
data['down_from'] = down_from
data['over_time'] = datetime.now() + timedelta(hours=+8)
self.addByDict(data)
else:
oInfo['over_time'] = datetime.now() + timedelta(hours=+8)
db.session.commit()
class YiFriend(db.Model, BaseModel):
__bind_key__ = 'xhh_yiyiyuan'
__tablename__ = 'yi_friends'
id = db.Column(db.BigInteger, primary_key=True)
user_id = db.Column(db.BigInteger, nullable=False, index=True)
fuser_id = db.Column(db.BigInteger, nullable=False, index=True)
type = db.Column(db.Integer,
nullable=False,
server_default=db.FetchedValue())
auth = db.Column(db.Integer,
nullable=False,
server_default=db.FetchedValue())
authed = db.Column(db.Integer,
nullable=False,
server_default=db.FetchedValue())
company = db.Column(db.String(100), nullable=False, index=True)
same_company = db.Column(db.Integer,
nullable=False,
server_default=db.FetchedValue())
school_id = db.Column(db.BigInteger,
nullable=False,
index=True,
server_default=db.FetchedValue())
same_school = db.Column(db.Integer,
nullable=False,
server_default=db.FetchedValue())
invite = db.Column(db.Integer,
nullable=False,
server_default=db.FetchedValue())
like = db.Column(db.Integer,
nullable=False,
server_default=db.FetchedValue())
modify_time = db.Column(db.DateTime, nullable=False)
create_time = db.Column(db.DateTime, nullable=False)
def getByUserId(self, user_id):
'''
获取认证
'''
where = and_(YiFriend.user_id == user_id, YiFriend.type < 3)
t = db.session.query(YiFriend, YiUser).outerjoin(
YiUser, YiFriend.fuser_id == YiUser.user_id)
res = t.filter(where).order_by(YiFriend.id).limit(1000).all()
return res
def blackNum(self, user_id):
'''
一级关系中有黑名单
'''
where = and_(YiFriend.user_id == user_id, YiFriend.type == 1,
YiUser.status == 5)
t = db.session.query(func.count(
YiFriend.id).label("user_count")).outerjoin(
YiUser, YiFriend.fuser_id == YiUser.user_id)
res = t.filter(where).limit(1).first()
return res.user_count
class AppList(db.Model, Base):
# 指定数据库
__bind_key__ = 'analysis_repertory'
__tablename__ = 'app_list'
# table model
id = db.Column(db.BigInteger, primary_key=True)
app_name = db.Column(db.String(100),
nullable=False,
server_default=db.FetchedValue())
app_package = db.Column(db.String(100),
nullable=False,
server_default=db.FetchedValue())
create_time = db.Column(db.DateTime, index=True)
def getAppByPkgList(self, pkgList):
data = {}
if len(pkgList) <= 0:
return data
appList = db.session.query(AppList.id, AppList.app_package).filter(
AppList.app_package.in_(pkgList)).all()
if appList is not None and len(appList) > 0:
for i in appList:
data[i.id] = i.app_package
return data
def batchInsert(self, insertApp, diffAppList):
db.session.execute(self.__table__.insert(), insertApp)
db.session.commit()
return self.getAppByPkgList(diffAppList)
def getAppByAppid(self, appidList):
if len(appidList) <= 0:
return {}
appList = db.session.query(AppList).filter(
AppList.id.in_(appidList)).all()
if appList is None or len(appList) == 0:
return {}
data = {}
for appInfo in appList:
each = {}
each['app_id'] = appInfo.id
each['app_name'] = appInfo.app_name
each['app_package'] = appInfo.app_package
data[appInfo.id] = each
return data
class SyncIdList(db.Model, Base):
__bind_key__ = 'analysis_repertory'
__tablename__ = 'sync_id_list'
# class attr
STATUS_INIT = 0
STATUS_DOING = 1
STATUS_SUCCESS = 2
SYNC_DETAIL = 'sync_detail'
SYNC_APP = 'sync_app'
SYNC_MSG = 'sync_msg'
# table model
id = db.Column(db.BigInteger, primary_key=True)
start_id = db.Column(db.Integer,
nullable=False,
server_default=db.FetchedValue())
end_id = db.Column(db.Integer,
nullable=False,
server_default=db.FetchedValue())
sync_status = db.Column(db.Integer,
index=True,
nullable=False,
server_default=db.FetchedValue())
sync_type = db.Column(db.String(32),
index=True,
nullable=False,
server_default=db.FetchedValue())
create_time = db.Column(db.DateTime, index=True)
modify_time = db.Column(db.DateTime, index=True)
def get(self, user_id):
return self.query.get(user_id)
def getInitSyncId(self, sync_type):
where = and_(
SyncIdList.sync_status == self.STATUS_INIT,
SyncIdList.sync_type == sync_type,
)
db_sync = db.session.query(SyncIdList).filter(where).order_by(
desc('id')).limit(1).first()
return db_sync
def lockStatus(self, status):
self.sync_status = status
self.modify_time = datetime.now()
db.session.commit()
class AfSsReport(db.Model, Base):
# 指定数据库
__bind_key__ = 'xhh_antifraud'
__tablename__ = 'af_ss_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())
score = db.Column(db.Integer,nullable=True)
rain_risk_reason = db.Column(db.Text,nullable=True)
rain_score = db.Column(db.String,nullable=True)
consume_fund_index = db.Column(db.String,nullable=True)
indentity_risk_index = db.Column(db.String,nullable=True)
social_stability_index = db.Column(db.String,nullable=True)
phone_register_month = db.Column(db.Integer,nullable=True)
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 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 ShopLoanUserLoanExtend(db.Model, BaseModel):
__bind_key__ = 'loan_shop'
__tablename__ = 'loan_user_loan_extend'
metadata = MetaData()
id = db.Column(db.BigInteger, primary_key=True)
loan_id = db.Column(db.BigInteger, nullable=False, server_default=db.FetchedValue())
user_id = db.Column(db.BigInteger, nullable=False, server_default=db.FetchedValue())
device_tokens = db.Column(db.String(64))
device_type = db.Column(db.String(20))
is_outmoney = db.Column(db.BigInteger, nullable=False, server_default=db.FetchedValue())
user_ip = db.Column(db.String(16))
loan_total = db.Column(db.BigInteger, nullable=False, server_default=db.FetchedValue())
loan_success = db.Column(db.BigInteger, nullable=False, server_default=db.FetchedValue())
modify_time = db.Column(db.DateTime)
create_time = db.Column(db.DateTime)
version = db.Column(db.BigInteger, nullable=False, server_default=db.FetchedValue())
def getSuccessNum(self, user_id):
if user_id is None:
return 0
where = and_(
ShopLoanUserLoanExtend.user_id == user_id,
)
res = db.session.query(ShopLoanUserLoanExtend).filter(where).order_by(desc(ShopLoanUserLoanExtend.modify_time)).first()
return res
class YiUserLoanFlow(db.Model, BaseModel):
__bind_key__ = 'own_yiyiyuan'
__tablename__ = 'yi_user_loan_flows'
id = db.Column(db.Integer, primary_key=True)
loan_id = db.Column(db.Integer, nullable=False)
admin_id = db.Column(db.Integer, nullable=False)
loan_status = db.Column(db.Integer)
relative = db.Column(db.String(1024))
reason = db.Column(db.String(1024))
create_time = db.Column(db.DateTime)
admin_name = db.Column(db.String(64))
type = db.Column(db.Integer, server_default=db.FetchedValue())
class ShopLoanUser(db.Model, BaseModel):
__bind_key__ = 'loan_shop'
__tablename__ = 'loan_user'
metadata = MetaData()
user_id = db.Column(db.BigInteger, primary_key=True)
user_no = db.Column(db.String(32))
mobile = db.Column(db.String(12))
invite_code = db.Column(db.String(32))
from_code = db.Column(db.String(32))
head_img = db.Column(db.String(128))
realname = db.Column(db.String(32))
identity = db.Column(db.String(20))
sex = db.Column(db.Integer, server_default=db.FetchedValue())
email = db.Column(db.String(64))
marriage = db.Column(db.Integer, server_default=db.FetchedValue())
status = db.Column(db.Integer, server_default=db.FetchedValue())
identity_valid = db.Column(db.Integer, server_default=db.FetchedValue())
come_from = db.Column(db.Integer, server_default=db.FetchedValue())
last_login_time = db.Column(db.DateTime)
last_login_position = db.Column(db.Integer,
server_default=db.FetchedValue())
create_time = db.Column(db.DateTime)
modify_time = db.Column(db.DateTime)
version = db.Column(db.Integer, server_default=db.FetchedValue())
def getUidsByMobiles(self, mobiles):
if len(mobiles) == 0:
return []
oUsers = db.session.query(
ShopLoanUser.user_id.label('user_id')).filter(
ShopLoanUser.mobile.in_(mobiles)).limit(1).all()
if oUsers:
return [i.user_id for i in oUsers]
else:
return []
def getUidCounts(self, mobiles):
if len(mobiles) == 0:
return 0
counts = db.session.query(ShopLoanUser).filter(
ShopLoanUser.mobile.in_(mobiles)).limit(1000).count()
return counts
def getByIdentity(self, identity):
db_user = db.session.query(ShopLoanUser).filter(
ShopLoanUser.identity == identity).first()
return db_user
def getByMobile(self, mobile):
db_user = db.session.query(ShopLoanUser).filter(
ShopLoanUser.mobile == mobile).limit(1).first()
return db_user
class UserAppSnapshot(db.Model, Base):
__bind_key__ = 'analysis_repertory'
__tablename__ = 'user_app_snapshot'
# table model
id = db.Column(db.BigInteger, primary_key=True)
mobile = db.Column(db.String(32),
index=True,
nullable=False,
server_default=db.FetchedValue())
app_list = db.Column(db.Text, nullable=False)
create_time = db.Column(db.DateTime, nullable=False)
def getAppList(self, mobile):
if len(mobile) == 0:
return None
return db.session.query(UserAppSnapshot.app_list).filter(
UserAppSnapshot.mobile == mobile).order_by(
desc('id')).limit(1).first()
def saveAppList(self, mobile, time, app_list):
if len(mobile) == 0 or len(time) == 0 or len(app_list) == 0:
return False
data = {}
data['mobile'] = mobile
data['app_list'] = app_list
data['create_time'] = time
self.addByDict(data)
def getAppListWithTime(self, mobile, time):
if mobile is None or time is None or len(mobile) != 11 or len(
time) != 19:
return []
where = and_(UserAppSnapshot.mobile == str(mobile),
UserAppSnapshot.create_time <= time)
appList = db.session.query(
UserAppSnapshot.app_list).filter(where).order_by(
desc('id')).limit(1).first()
if appList is None or len(appList) < 0:
return []
return appList.app_list
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 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 AppInfo(db.Model, BaseModel):
# 指定数据库
__bind_key__ = 'analysis_repertory'
__tablename__ = 'app_info'
# table model
id = db.Column(db.Integer, primary_key=True)
app_id = db.Column(db.Integer, nullable=False)
app_name = db.Column(db.String(100),
index=True,
nullable=False,
server_default=db.FetchedValue())
app_package = db.Column(db.String(100),
index=True,
nullable=False,
server_default=db.FetchedValue())
first_label = db.Column(db.String(50),
index=True,
nullable=False,
server_default=db.FetchedValue())
second_lable = db.Column(db.String(50),
index=True,
nullable=False,
server_default=db.FetchedValue())
company = db.Column(db.String(100),
nullable=False,
server_default=db.FetchedValue())
app_version = db.Column(db.String(32),
nullable=False,
server_default=db.FetchedValue())
publish_date = db.Column(db.String(32),
nullable=False,
server_default=db.FetchedValue())
down_count = db.Column(db.String(32),
nullable=False,
server_default=db.FetchedValue())
comment_rate = db.Column(db.String(32),
nullable=False,
server_default=db.FetchedValue())
comment_person = db.Column(db.String(32),
nullable=False,
server_default=db.FetchedValue())
app_size = db.Column(db.String(32),
nullable=False,
server_default=db.FetchedValue())
down_from = db.Column(db.SmallInteger,
nullable=False,
server_default=db.FetchedValue())
create_time = db.Column(db.Date, index=True)
def getAppByPkgList(self, pkgList):
data = {}
if len(pkgList) <= 0:
return data
appList = db.session.query(AppInfo.down_from,
AppInfo.app_package).filter(
AppInfo.app_package.in_(pkgList)).all()
if appList is not None and len(appList) > 0:
for i in appList:
each = data[i.app_package] if data.get(
i.app_package, None) is not None else []
each.append(i.down_from)
data[i.app_package] = each
return data
def batchInsert(self, insertApp):
db.session.execute(self.__table__.insert(), insertApp)
db.session.commit()
def getAppByAppid(self, appidList):
if len(appidList) <= 0:
return []
appList = db.session.query(AppInfo).filter(
AppInfo.app_id.in_(appidList)).all()
if appList is None or len(appList) == 0:
return []
data = []
for appInfo in appList:
each = {}
each['id'] = appInfo.id
each['app_id'] = appInfo.app_id
each['app_name'] = appInfo.app_name
each['app_package'] = appInfo.app_package
each['first_label'] = appInfo.first_label
each['second_lable'] = appInfo.second_lable
each['company'] = appInfo.company
each['app_version'] = appInfo.app_version
each['publish_date'] = appInfo.publish_date
each['down_count'] = appInfo.down_count
each['comment_rate'] = appInfo.comment_rate
each['comment_person'] = appInfo.comment_person
each['app_size'] = appInfo.app_size
each['down_from'] = appInfo.down_from
each['create_time'] = appInfo.create_time
data.append(each)
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 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
class YiAntiFraud(db.Model, BaseModel):
__bind_key__ = 'xhh_yiyiyuan'
__tablename__ = 'yi_anti_fraud'
id = db.Column(db.BigInteger, primary_key=True)
user_id = db.Column(db.BigInteger,
nullable=False,
index=True,
server_default=db.FetchedValue())
loan_id = db.Column(db.BigInteger,
nullable=False,
server_default=db.FetchedValue())
type = db.Column(db.Integer,
nullable=False,
server_default=db.FetchedValue())
model_status = db.Column(db.Integer,
nullable=False,
index=True,
server_default=db.FetchedValue())
result_status = db.Column(db.Integer,
nullable=False,
index=True,
server_default=db.FetchedValue())
result_subject = db.Column(db.Text, nullable=False)
result_time = db.Column(db.DateTime, nullable=False)
modify_time = db.Column(db.DateTime, nullable=False)
create_time = db.Column(db.DateTime, nullable=False)
#version = db.Column(db.Integer, nullable=False)
def getById(self, id):
db_afraud = self.query.get(int(id))
return db_afraud
def getByUserId(self, user_id):
'''
获取认证
'''
where = and_(YiAntiFraud.user_id == user_id)
res = db.session.query(YiAntiFraud, YiUser) \
.outerjoin(YiUser, YiAntiFraud.user_id == YiUser.user_id) \
.filter(where) \
.order_by(YiAntiFraud.id) \
.limit(1000).all()
return res
def getData(self, end_time):
'''
获取需要处理的数据, 默认查询一小时内
'''
# 1. 精确到分
end_time = datetime.strptime(end_time, '%Y-%m-%d %H:%M:%S')
end_time = end_time - timedelta(seconds=end_time.second)
# 2. 五分钟内 @todo
#start_time = end_time - timedelta(seconds=3600)
where = and_(
#YiAntiFraud.create_time >= start_time,
#YiAntiFraud.create_time < end_time,
YiAntiFraud.model_status == 1,
YiAntiFraud.result_status == 0)
res = db.session.query(YiAntiFraud, YiUser) \
.outerjoin(YiUser, YiAntiFraud.user_id == YiUser.user_id) \
.filter(where) \
.order_by(YiAntiFraud.id) \
.limit(1000) \
.all()
return res
def lock(self, data, status):
if len(data) == 0:
return False
now = datetime.now()
for o in data:
o.modify_time = now
o.model_status = status # 锁定 | 完成
db.session.commit()
return True
def finished(self, result_status, result_subject):
'''
结束借款流程
'''
self.model_status = 3
self.result_status = 0
self.result_subject = result_subject
self.result_time = self.modify_time = datetime.now()
db.session.commit()
class YiUser(db.Model, BaseModel):
__bind_key__ = 'xhh_yiyiyuan'
__tablename__ = 'yi_user'
user_id = db.Column(db.BigInteger, primary_key=True)
openid = db.Column(db.String(64), index=True)
mobile = db.Column(db.String(20), unique=True)
invite_code = db.Column(db.String(32))
invite_qrcode = db.Column(db.String(32))
from_code = db.Column(db.String(32))
user_type = db.Column(db.Integer, server_default=db.FetchedValue())
status = db.Column(db.Integer,
nullable=False,
server_default=db.FetchedValue())
identity_valid = db.Column(db.Integer,
nullable=False,
server_default=db.FetchedValue())
school_valid = db.Column(db.Integer,
nullable=False,
server_default=db.FetchedValue())
school = db.Column(db.String(64))
school_id = db.Column(db.Integer,
nullable=False,
server_default=db.FetchedValue())
edu = db.Column(db.String(64))
school_time = db.Column(db.String(64))
realname = db.Column(db.String(32))
identity = db.Column(db.String(20))
industry = db.Column(db.Integer, server_default=db.FetchedValue())
company = db.Column(db.String(128))
position = db.Column(db.String(128))
telephone = db.Column(db.String(32))
address = db.Column(db.String(128))
pic_self = db.Column(db.String(128))
pic_identity = db.Column(db.String(128))
pic_type = db.Column(db.Integer,
nullable=False,
server_default=db.FetchedValue())
come_from = db.Column(db.Integer,
nullable=False,
server_default=db.FetchedValue())
serverid = db.Column(db.String(128))
create_time = db.Column(db.DateTime)
pic_up_time = db.Column(db.DateTime)
final_score = db.Column(db.Integer)
birth_year = db.Column(db.Integer)
last_login_time = db.Column(db.DateTime)
last_login_type = db.Column(db.String(16))
verify_time = db.Column(db.DateTime)
is_webunion = db.Column(db.String(8),
nullable=False,
server_default=db.FetchedValue())
webunion_confirm_time = db.Column(db.DateTime)
is_red_packets = db.Column(db.String(4),
nullable=False,
server_default=db.FetchedValue())
def get(self, user_id):
return self.query.get(user_id)
def getByUserId(self, user_id):
db_user = db.session.query(YiUser).filter(
YiUser.user_id == user_id).limit(1).first()
return db_user
def getByMobile(self, mobile):
db_user = db.session.query(YiUser).filter(
YiUser.mobile == mobile).limit(1).first()
return db_user
def isOverdueMobile(self, mobiles):
if len(mobiles) == 0:
return []
mobile_str = '"' + '","'.join(mobiles) + '"'
sql = '''
SELECT
u.mobile,
u.user_id,
l.status
FROM yi_user_loan l
LEFT JOIN yi_user u
ON l.user_id = u.user_id
WHERE u.mobile IN(%s)
AND l.status = 12
LIMIT 0, 50;
''' % mobile_str
users = db.session.execute(sql, bind=self.get_engine()).fetchall()
return users
def getUidsByMobiles(self, mobiles):
if len(mobiles) == 0:
return []
oUsers = db.session.query(YiUser.user_id.label('user_id')).filter(
YiUser.mobile.in_(mobiles)).limit(1000).all()
if oUsers:
return [i.user_id for i in oUsers]
else:
return []
def getUidCounts(self, mobiles):
if len(mobiles) == 0:
return 0
counts = db.session.query(YiUser).filter(
YiUser.mobile.in_(mobiles)).limit(1000).count()
return counts
def getMobileNum(self, start, end):
where = and_(YiUser.user_id >= start, YiUser.user_id <= end,
YiUser.mobile != None)
res = db.session.query(YiUser).filter(where).all()
return res
def get_maxid(self):
maxid = db.session.query(func.max(YiUser.user_id)).scalar()
return maxid
def getByMobileAndIdentity(self, mobile, identity):
where = and_(YiUser.mobile == mobile, YiUser.identity == identity)
db_user = db.session.query(YiUser).filter(where).limit(1).first()
return db_user
def getByIdentity(self, identity):
db_user = db.session.query(YiUser).filter(
YiUser.identity == identity).first()
return db_user
class StrategyRequest(db.Model, BaseModel):
__bind_key__ = 'xhh_strategy'
__tablename__ = 'st_strategy_request'
id = db.Column(db.BigInteger, primary_key=True)
aid = db.Column(db.Integer,
nullable=False,
server_default=db.FetchedValue())
req_id = db.Column(db.BigInteger,
nullable=False,
unique=True,
server_default=db.FetchedValue())
user_id = db.Column(db.BigInteger,
nullable=False,
index=True,
server_default=db.FetchedValue())
loan_id = db.Column(db.BigInteger,
nullable=False,
index=True,
server_default=db.FetchedValue())
status = db.Column(db.Integer,
nullable=False,
server_default=db.FetchedValue())
come_from = db.Column(db.Integer,
nullable=True,
server_default=db.FetchedValue())
callbackurl = db.Column(db.String(255),
nullable=False,
server_default=db.FetchedValue())
create_time = db.Column(db.DateTime, nullable=False)
modify_time = db.Column(db.DateTime, nullable=False)
version = db.Column(db.Integer, nullable=False)
def getById(self, id):
db_strategy = self.query.get(int(id))
return db_strategy
def getByReqId(self, db_base):
if db_base.request_id is None:
return None
if db_base.aid is None:
return None
if db_base.user_id is None:
return None
where = and_(
StrategyRequest.req_id == int(db_base.request_id),
StrategyRequest.aid == int(db_base.aid),
StrategyRequest.user_id == int(db_base.user_id),
)
data = self.query.filter(where).order_by(
StrategyRequest.id.desc()).first()
return data
def getByIdData(self, id):
if id is None:
return None
where = and_(StrategyRequest.id == int(id))
data = self.query.filter(where).first()
return data
def getData(self, end_time):
'''
获取需要处理的数据, 默认查询一小时内
'''
# 1. 精确到分
end_time = datetime.strptime(end_time, '%Y-%m-%d %H:%M:%S')
end_time = end_time + timedelta(seconds=end_time.second - 360)
# 2. 查询一小时内
#start_time = end_time - timedelta(seconds=3600)
where = and_(StrategyRequest.aid.in_([1, 14]),
StrategyRequest.create_time < end_time,
StrategyRequest.status == 100)
res = db.session.query(StrategyRequest) \
.filter(where) \
.order_by(StrategyRequest.id) \
.limit(1000) \
.all()
return res
def lock(self, data, status):
if len(data) == 0:
return False
now = datetime.now()
for o in data:
o.modify_time = now
o.status = status # 锁定 | 完成
db.session.commit()
return True
def finished(self, status):
self.status = status
self.modify_time = datetime.now()
self.version = self.version + 1
db.session.commit()
return True
# 获取待处理数据
def getInitData(self):
now = datetime.now()
start_time = now + timedelta(seconds=now.second - 86400)
where = and_(
StrategyRequest.status == 0,
StrategyRequest.create_time > start_time,
)
res = db.session.query(StrategyRequest).filter(where).order_by(
StrategyRequest.id).limit(1).all()
return res
class AfWsm(db.Model, BaseModel):
# 指定数据库
__bind_key__ = 'xhh_antifraud'
__tablename__ = 'af_wsm'
id = db.Column(db.BigInteger, primary_key=True)
loan_id = db.Column(db.BigInteger,
nullable=False,
index=True,
server_default=db.FetchedValue())
ymonth = db.Column(db.String(20), nullable=False)
mobile = db.Column(db.String(20), nullable=False)
zs_in_times = db.Column(db.Integer, nullable=True)
st_in_times = db.Column(db.Integer, nullable=True)
te_in_times = db.Column(db.Integer, nullable=True)
etf_in_times = db.Column(db.Integer, nullable=True)
zs_out_times = db.Column(db.Integer, nullable=True)
st_out_times = db.Column(db.Integer, nullable=True)
te_out_times = db.Column(db.Integer, nullable=True)
etf_out_times = db.Column(db.Integer, nullable=True)
zs_in_duration = db.Column(db.BigInteger, nullable=True)
st_in_duration = db.Column(db.BigInteger, nullable=True)
te_in_duration = db.Column(db.BigInteger, nullable=True)
etf_in_duration = db.Column(db.BigInteger, nullable=True)
zs_out_duration = db.Column(db.BigInteger, nullable=True)
st_out_duration = db.Column(db.BigInteger, nullable=True)
te_out_duration = db.Column(db.BigInteger, nullable=True)
etf_out_duration = db.Column(db.BigInteger, nullable=True)
zs_call_times = db.Column(db.Integer, nullable=True)
st_call_times = db.Column(db.Integer, nullable=True)
te_call_times = db.Column(db.Integer, nullable=True)
etf_call_times = db.Column(db.Integer, nullable=True)
zs_call_duration = db.Column(db.BigInteger, nullable=True)
st_call_duration = db.Column(db.BigInteger, nullable=True)
te_call_duration = db.Column(db.BigInteger, nullable=True)
etf_call_duration = db.Column(db.BigInteger, nullable=True)
work_call_times = db.Column(db.Integer, nullable=True)
weekend_call_times = db.Column(db.Integer, nullable=True)
total_times = db.Column(db.Integer, nullable=True)
total_duration = db.Column(db.BigInteger, nullable=True)
contacts_times = db.Column(db.Integer, nullable=True)
contacts_duration = db.Column(db.BigInteger, nullable=True)
relatives_times = db.Column(db.Integer, nullable=True)
relatives_duration = db.Column(db.BigInteger, nullable=True)
contacts_often_time_part = db.Column(db.String(64), nullable=True)
relatives_often_time_part = db.Column(db.String(64), nullable=True)
create_time = db.Column(db.DateTime, nullable=False)
def addWsm(self, dict_data, user):
try:
if dict_data is None or len(dict_data) == 0:
return False
self.loan_id = user.loan_id
self.ymonth = dict_data.get('ymonth', '000000')
self.mobile = user.mobile
self.zs_in_times = dict_data.get('zs_in_times', 0)
self.st_in_times = dict_data.get('st_in_times', 0)
self.te_in_times = dict_data.get('te_in_times', 0)
self.etf_in_times = dict_data.get('etf_in_times', 0)
self.zs_out_times = dict_data.get('zs_out_times', 0)
self.st_out_times = dict_data.get('st_out_times', 0)
self.te_out_times = dict_data.get('te_out_times', 0)
self.etf_out_times = dict_data.get('etf_out_times', 0)
self.zs_in_duration = dict_data.get('zs_in_duration', 0)
self.st_in_duration = dict_data.get('st_in_duration', 0)
self.te_in_duration = dict_data.get('te_in_duration', 0)
self.etf_in_duration = dict_data.get('etf_in_duration', 0)
self.zs_out_duration = dict_data.get('zs_out_duration', 0)
self.st_out_duration = dict_data.get('st_out_duration', 0)
self.te_out_duration = dict_data.get('te_out_duration', 0)
self.etf_out_duration = dict_data.get('etf_out_duration', 0)
self.zs_call_times = dict_data.get('zs_call_times', 0)
self.st_call_times = dict_data.get('st_call_times', 0)
self.te_call_times = dict_data.get('te_call_times', 0)
self.etf_call_times = dict_data.get('etf_call_times', 0)
self.zs_call_duration = dict_data.get('zs_call_duration', 0)
self.st_call_duration = dict_data.get('st_call_duration', 0)
self.te_call_duration = dict_data.get('te_call_duration', 0)
self.etf_call_duration = dict_data.get('etf_call_duration', 0)
self.work_call_times = dict_data.get('work_call_times', 0)
self.weekend_call_times = dict_data.get('weekend_call_times', 0)
self.total_times = dict_data.get('total_times', 0)
self.total_duration = dict_data.get('total_duration', 0)
self.contacts_times = dict_data.get('contacts_times', 0)
self.contacts_duration = dict_data.get('contacts_duration', 0)
self.relatives_times = dict_data.get('relatives_times', 0)
self.relatives_duration = dict_data.get('relatives_duration', 0)
self.contacts_often_time_part = dict_data.get(
'contacts_often_time_part', '0')
self.relatives_often_time_part = dict_data.get(
'relatives_often_time_part', '0')
self.create_time = datetime.now()
self.add()
db.session.commit()
return True
except Exception as e:
logger.error("AfWsm-addOne:%s" % e)
return False
class JxlStat(db.Model, BaseModel):
# 指定数据库
__bind_key__ = 'xhh_open'
# 表名
__tablename__ = 'jxl_stat'
id = db.Column(db.Integer, primary_key=True)
aid = db.Column(db.Integer,
nullable=False,
server_default=db.FetchedValue())
requestid = db.Column(db.Integer,
nullable=False,
index=True,
server_default=db.FetchedValue())
name = db.Column(db.String(50), nullable=False)
idcard = db.Column(db.String(20), nullable=False)
phone = db.Column(db.String(20), nullable=False, index=True)
website = db.Column(db.String(50), nullable=False)
create_time = db.Column(db.DateTime, nullable=False)
url = db.Column(db.String(100), nullable=False)
source = db.Column(db.Integer, nullable=False)
def __init__(self):
super(JxlStat, self).__init__()
def getByRequestId(self, requestid):
if requestid is None:
return None
now_time = datetime.now()
end_time = (now_time +
timedelta(days=-120)).strftime('%Y-%m-%d %H:%M:%S')
where = and_(JxlStat.requestid == int(requestid),
JxlStat.create_time >= end_time)
data = self.query.filter(where).order_by(desc(
JxlStat.create_time)).first()
if data is None:
return None
domain = self._getDomain(data.create_time)
res = {}
res['report_url'] = domain + data.url
res['detail_url'] = res['report_url'].replace(".json", "_detail.json")
res['jxlstat_id'] = data.id
res['source'] = data.source
return res
def getByPhone(self, phone):
'''
根据phone获取
@param int phone
@return []
'''
if phone is None:
return None
where = and_(JxlStat.phone == phone,
JxlStat.website.notin_(["jingdong"]))
data = self.query.filter(where).order_by(desc(
JxlStat.create_time)).first()
if data is None:
return None
domain = self._getDomain(data.create_time)
res = {}
res['report_url'] = domain + data.url
#res['report_url'] = "http://182.92.80.211:8091/ofiles/jxl/7309173.json"
res['detail_url'] = res['report_url'].replace(".json", "_detail.json")
res['jxlstat_id'] = data.id
res['source'] = data.source
return res
def getData(self, id):
'''
获取需要处理的数据, 默认查询1个人
'''
if id is None:
return None
now_time = datetime.now()
end_time = (now_time +
timedelta(days=-120)).strftime('%Y-%m-%d %H:%M:%S')
where = and_(JxlStat.id == int(id), JxlStat.create_time >= end_time)
data = self.query.filter(where).order_by(desc(
JxlStat.create_time)).first()
return data
def getMaxId(self):
sql = "select max(id) as max_id from jxl_stat"
max_id = db.session.execute(sql, bind=self.get_engine()).fetchone()
_max_id = max_id[0]
return _max_id
def getById(self, id):
'''
根据id获取
@param int id
@return []
'''
if id is None:
return None
now_time = datetime.now()
end_time = (now_time +
timedelta(days=-120)).strftime('%Y-%m-%d %H:%M:%S')
where = and_(JxlStat.id == int(id), JxlStat.create_time >= end_time)
data = self.query.filter(where).order_by(desc(
JxlStat.create_time)).first()
if data is None:
return None
domain = self._getDomain(data.create_time)
res = {}
res['report_url'] = domain + data.url
#res['report_url'] = "http://182.92.80.211:8091/ofiles/jxl/7309173.json"
res['detail_url'] = res['report_url'].replace(".json", "_detail.json")
res['jxlstat_id'] = data.id
res['source'] = data.source
return res
def _getDomain(self, create_time):
'''
获取域名
@param datetime $create_time 时间格式
@return str 域名
'''
#@todo
from lib.config import get_config
cf = get_config()
if cf.TESTING:
return 'http://182.92.80.211:8091'
now = datetime.now()
ta = now - create_time
if ta.days > 1:
#domain = "http://124.193.149.180:8100"
#domain = "http://123.207.141.180"
domain = "http://10.139.36.194"
else:
domain = "http://open.xianhuahua.com"
return domain
def _getByUrl(self, url):
# 获取url中的内容, 设置超时
html = self._getByUrl2(url)
if html is None:
# 重试
html = self._getByUrl2(url)
if html is None:
raise Exception(1000, 'cant download by ' + url)
return html
def _getByUrl2(self, url):
socket.setdefaulttimeout(25)
try:
response = urllib.request.urlopen(url)
html = response.read()
except Exception as e:
logger.error('url get fail %s' % e)
html = None
return html
def getReport(self, url):
# 获取报告
# return self.getTestReport(url)
strings = self._getByUrl(url)
data = json.loads(strings)
return data
def getDetail(self, url):
# 获取详情
# return self.getTestDetail(url)
strings = self._getByUrl(url)
data = json.loads(strings)
return data
def getTestReport(self):
import os
path = os.getcwd()
fp = open(path + "/tests/15882797956/15882797956.json", "r")
data = json.loads(fp.read())
fp.close()
return data
def getTestDetail(self):
import os
path = os.getcwd()
fp = open(path + "/tests/15882797956/15882797956_detail.json", "r")
data = json.loads(fp.read())
fp.close()
return data
class YiUserLoanExtend(db.Model, BaseModel):
__bind_key__ = 'xhh_yiyiyuan'
__tablename__ = 'yi_user_loan_extend'
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_(YiUserLoanExtend.user_id == user_id,
YiUserLoanExtend.status == "SUCCESS")
res = db.session.query(YiUserLoanExtend).filter(where).count()
return res
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 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
