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