class ApiDetailOther(db.Model, Base): __bind_key__ = 'xhh_antifraud' __tablename__ = 'api_detail_other' id = db.Column(db.BigInteger, primary_key=True) base_id = db.Column(db.BigInteger, index=True) last3_answer = db.Column(db.Integer) last3_all = db.Column(db.Integer) last6_answer = db.Column(db.Integer) last6_all = db.Column(db.Integer) same_phone_num = db.Column(db.Integer) phone_register_month = db.Column(db.Integer) total_duration = db.Column(db.BigInteger) tot_phone_num = db.Column(db.Integer) last3_not_mobile_count = db.Column(db.Integer) last6_not_mobile_count = db.Column(db.Integer) shutdown_duration_count = db.Column(db.Integer) shutdown_sum_days = db.Column(db.Integer) shutdown_max_days = db.Column(db.Integer) shutdown_min_days = db.Column(db.Integer) shutdown_median_days = db.Column(db.Integer) shutdown_mode_days = db.Column(db.Integer) retain_ratio = db.Column(db.String(32)) last_3mth_Oth_ratio = db.Column(db.String(32)) last_3mth_oth_incr = db.Column(db.String(32)) create_time = db.Column(db.DateTime, nullable=False)
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 AddressList(db.Model, BaseModel): __bind_key__ = 'analysis_repertory' __tablename__ = 'address_list' id = db.Column(db.BigInteger, primary_key=True) aid = db.Column(db.Integer) user_id = db.Column(db.BigInteger) user_phone = db.Column(db.String(20), index=True) phone = db.Column(db.String(20)) name = db.Column(db.String(32)) modify_time = db.Column(db.DateTime) create_time = db.Column(db.DateTime) def getByUserPhone(self, user_phone): db_phones = db.session.query(AddressList.phone).filter(AddressList.user_phone == user_phone).limit(2000).all() phones = [] # print(db_phones) for i in db_phones: phone = {'phone':str(i[0])} # phones.append(self.row2dict(i)) phones.append(phone) return phones def getByUserPhones(self, phones): if len(phones) == 0: return [] oUserPhones = db.session.query(AddressList.phone,AddressList.user_phone).filter(AddressList.user_phone.in_(phones)).limit(1000000).all() if oUserPhones: return oUserPhones else : return []
class ReverseAddressList(db.Model, BaseModel): __bind_key__ = 'analysis_repertory' __tablename__ = 'reverse_address_list' id = db.Column(db.BigInteger, primary_key=True) aid = db.Column(db.Integer) user_id = db.Column(db.BigInteger) user_phone = db.Column(db.String(20), index=True) phone = db.Column(db.String(20)) name = db.Column(db.String(32)) modify_time = db.Column(db.DateTime) create_time = db.Column(db.DateTime) def getAddrByPhones(self, phones): if len(phones) == 0: return [] oUserPhones = db.session.query(ReverseAddressList.user_phone).filter( ReverseAddressList.phone.in_(phones)).limit(10000).all() if oUserPhones: return [i.user_phone for i in oUserPhones] else: return [] def getAddrByPhone(self, phones, loan_time): if len(phones) == 0: return [] oUserPhones = db.session.query(ReverseAddressList.user_phone).filter( ReverseAddressList.phone == phones).filter( ReverseAddressList.create_time <= loan_time).limit( 10000).all() if oUserPhones: return [i.user_phone for i in oUserPhones] else: return []
class YiAddressList(db.Model, BaseModel): __bind_key__ = 'own_yiyiyuan' __tablename__ = 'yi_address_list' id = db.Column(db.BigInteger, primary_key=True) user_id = db.Column(db.BigInteger, nullable=False) phone = db.Column(db.String(20), nullable=False) name = db.Column(db.String(20), nullable=False) modify_time = db.Column(db.DateTime, nullable=False) create_time = db.Column(db.DateTime, nullable=False) def getByUserid(self, user_id): if not user_id: return None #@todo 这里会不会有问题 dbAddrLists = self.query.filter( YiAddressList.user_id == user_id).limit(10000).all() addrLists = [] for i in dbAddrLists: addrLists.append(self.row2dict(i)) return addrLists def blackNum(self, user_id): ''' 通讯录中有黑名单 ''' return 0
class DetailList(db.Model, BaseModel): __bind_key__ = 'analysis_repertory' __tablename__ = 'detail_list' id = db.Column(db.BigInteger, primary_key=True) aid = db.Column(db.Integer) user_id = db.Column(db.BigInteger, index=True) mobile = db.Column(db.String(20), index=True) phone = db.Column(db.String(20), index=True) call_times = db.Column(db.Integer) use_time = db.Column(db.Integer) min_time = db.Column(db.DateTime) max_time = db.Column(db.DateTime) create_time = db.Column(db.DateTime) def addDetailList(self, dict_data): try: if dict_data is None or len(dict_data) == 0: return False self.aid = dict_data.aid self.user_id = dict_data.user_id self.mobile = dict_data.mobile self.phone = dict_data.phone self.call_times = dict_data.call_times self.create_time = datetime.now() self.add() db.session.commit() return True except Exception as e: logger.error("addDetailList:%s" % e) return False def getByUserPhone(self, mobile): db_phones = db.session.query(DetailList).filter(DetailList.mobile == mobile).limit(1000).all() phones = [] for i in db_phones: phones.append(self.row2dict(i)) return phones def saveDetailList(self, dict_res, data): createTime = data.create_time if data is not None else '0000-00-00 00:00:00' mobile = str(data.phone) if data is not None else '' aid = int(data.aid) if data is not None else 0 user = YiUser().getByMobile(mobile) user_id = int(user.user_id) if user is not None else 0 insertSql = [] for key, value in dict_res.items(): phone = str(key) min_time = str(value.get('min_time','0000-00-00 00:00:00')) max_time = str(value.get('max_time','0000-00-00 00:00:00')) use_time = int(value.get('use_time',0)) call_times = int(value.get('call_times',0)) data_dict = {'aid': aid, 'user_id': user_id, 'mobile' : mobile, 'call_times' : call_times, 'min_time' : min_time, 'max_time' : max_time, 'use_time' : use_time, 'phone' : phone, 'create_time' : createTime} insertSql.append(data_dict) db.session.execute(self.__table__.insert(),insertSql) db.session.commit()
class YgyUserLoanExtend(db.Model, BaseModel): __bind_key__ = 'xhh_yigeyi' __tablename__ = 'yi_user_loan_extend' metadata = MetaData() id = db.Column(db.BigInteger, primary_key=True) user_id = db.Column(db.BigInteger, nullable=False, server_default=db.FetchedValue()) loan_id = db.Column(db.BigInteger, nullable=False, server_default=db.FetchedValue()) uuid = db.Column(db.String(55)) outmoney = db.Column(db.BigInteger, nullable=False, server_default=db.FetchedValue()) payment_channel = db.Column(db.BigInteger, nullable=False, server_default=db.FetchedValue()) userIp = db.Column(db.String(64)) extend_type = db.Column(db.BigInteger, nullable=False, server_default=db.FetchedValue()) success_num = db.Column(db.BigInteger, nullable=False, server_default=db.FetchedValue()) last_modify_time = db.Column(db.DateTime) create_time = db.Column(db.DateTime) fund = db.Column(db.BigInteger, nullable=False, server_default=db.FetchedValue()) status = db.Column(db.BigInteger, nullable=False, server_default=db.FetchedValue()) version = db.Column(db.BigInteger, nullable=False, server_default=db.FetchedValue()) loan_total = db.Column(db.BigInteger, nullable=False, server_default=db.FetchedValue()) loan_success = db.Column(db.BigInteger, nullable=False, server_default=db.FetchedValue()) loan_quota = db.Column(db.Numeric(12, 2), nullable=False) def getSuccessNum(self, user_id): if user_id is None: return 0 where = and_(YgyUserLoanExtend.user_id == user_id, YgyUserLoanExtend.status == "SUCCESS") res = db.session.query(YgyUserLoanExtend).filter(where).count() return res
class ReverseDetailList(db.Model, BaseModel): __bind_key__ = 'analysis_repertory' __tablename__ = 'reverse_detail_list' id = db.Column(db.BigInteger, primary_key=True) aid = db.Column(db.Integer) user_id = db.Column(db.BigInteger, index=True) mobile = db.Column(db.String(20), index=True) phone = db.Column(db.String(20), index=True) call_times = db.Column(db.Integer) use_time = db.Column(db.Integer) min_time = db.Column(db.DateTime) max_time = db.Column(db.DateTime) create_time = db.Column(db.DateTime) def getDetailByPhones(self, phones): if len(phones) == 0: return [] oUserPhones = db.session.query(ReverseDetailList.mobile).filter( ReverseDetailList.phone.in_(phones)).limit(10000).all() if oUserPhones: return [i.mobile for i in oUserPhones] else: return [] def saveDetailList(self, dict_res, data): createTime = data.create_time if data is not None else '0000-00-00 00:00:00' mobile = str(data.phone) if data is not None else '' aid = int(data.aid) if data is not None else 0 user = YiUser().getByMobile(mobile) user_id = int(user.user_id) if user is not None else 0 insertSql = [] for key, value in dict_res.items(): phone = str(key) min_time = str(value.get('min_time', '0000-00-00 00:00:00')) max_time = str(value.get('max_time', '0000-00-00 00:00:00')) use_time = int(value.get('use_time', 0)) call_times = int(value.get('call_times', 0)) data_dict = { 'aid': aid, 'user_id': user_id, 'mobile': mobile, 'call_times': call_times, 'min_time': min_time, 'max_time': max_time, 'use_time': use_time, 'phone': phone, 'create_time': createTime } insertSql.append(data_dict) db.session.execute(self.__table__.insert(), insertSql) db.session.commit()
class LoanUserLoanFlows(db.Model, BaseModel): __bind_key__ = 'spark' __tablename__ = 'loan_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, nullable=False) relative = db.Column(db.String(1024)) reason = db.Column(db.String(1024)) create_time = db.Column(db.DateTime) modify_time = db.Column(db.DateTime) version = db.Column(db.Integer, nullable=False)
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 YiFavoriteContact(db.Model, BaseModel): __bind_key__ = 'xhh_yiyiyuan' __tablename__ = 'yi_favorite_contacts' id = db.Column(db.BigInteger, primary_key=True) user_id = db.Column(db.BigInteger, nullable=False) contacts_name = db.Column(db.String(20), nullable=False) mobile = db.Column(db.String(20), nullable=False) relatives_name = db.Column(db.String(20), nullable=False) phone = db.Column(db.String(20), nullable=False) last_modify_time = db.Column(db.DateTime, nullable=False) create_time = db.Column(db.DateTime) def getByUserId(self, user_id): return self.query.filter_by(user_id=user_id).limit(1).first() def contactDue(self, dbContact): # 亲属联系人是否逾期 if dbContact is None: return {} mobiles = [] #亲属 if 'phone' in dbContact.keys(): mobiles.append(str(dbContact['phone'])) #常用 if 'mobile' in dbContact.keys(): mobiles.append(str(dbContact['mobile'])) if len(mobiles) == 0: return {} oUser = YiUser() overdue_users = oUser.isOverdueMobile(mobiles) overdue_mobiles = [user[0] for user in overdue_users] # # 判断是否逾期 contract_due_data = {} if 'phone' in dbContact.keys() and dbContact['phone'] is not None: if dbContact['phone'] in overdue_mobiles: contract_due_data['com_r_overdue'] = 1 else: contract_due_data['com_r_overdue'] = 0 if 'mobile' in dbContact.keys() and dbContact['mobile'] is not None: if dbContact['mobile'] in overdue_mobiles: contract_due_data['com_c_overdue'] = 1 else: contract_due_data['com_c_overdue'] = 0 return contract_due_data
class TagInfo(db.Model, Base): # 指定数据库 __bind_key__ = 'xhh_antifraud' __tablename__ = 'tag_info_list' id = db.Column(db.BigInteger, primary_key=True) phone = db.Column(db.String(20), unique=True) source = db.Column(db.Integer) tag_type = db.Column(db.String(255)) status = db.Column(db.SmallInteger, default=0) type = db.Column(db.SmallInteger, nullable=False) modify_time = db.Column(db.DateTime, nullable=False) create_time = db.Column(db.DateTime, nullable=False) def addResult(self, dict_data, source=100): try: if not dict_data: return False self.phone = dict_data.get('phone') self.source = source self.tag_type = dict_data.get('tag_type') self.status = dict_data.get('status', 0) self.type = 1 self.modify_time = datetime.now() self.create_time = datetime.now() res = db.session.query(TagInfo).filter( TagInfo.phone == self.phone).first() tags = dict_data['tag_type'] for i in tags: if i == '其他' or i == '其它': tags.remove(i) if len(tags): if res is not None: res.type = 2 tag_list = res.tag_type.split(',') # 列表 for i in tags: if i not in tag_list: tag_list.append(i) res.tag_type = ','.join(tag_list) res.modify_time = datetime.now() db.session.commit() else: self.tag_type = ','.join(tags) self.add() db.session.commit() logger.info('存储到数据库的手机号%s的标识是%s' % (self.phone, self.tag_type)) except Exception as e: logger.error("TagInfo-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 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 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 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 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 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 Mobile(db.Model, BaseModel): __bind_key__ = 'sparrow' __tablename__ = 'mobile' id = db.Column(db.BigInteger, primary_key=True) aid = db.Column(db.Integer) user_id = db.Column(db.BigInteger, index=True) mobile = db.Column(db.String(20), index=True) realname = db.Column(db.String(32)) identity = db.Column(db.String(20)) create_time = db.Column(db.DateTime) def getUidsByMobiles(self,mobiles): if len(mobiles) == 0: return [] oUsers = db.session.query(Mobile.mobile).filter(Mobile.mobile.in_(mobiles)).limit(1000).all() if oUsers : return [i.mobile for i in oUsers] else : return []
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 YiAddressList(db.Model, BaseModel): __bind_key__ = 'own_yiyiyuan' __tablename__ = 'yi_address_list' id = db.Column(db.BigInteger, primary_key=True) user_id = db.Column(db.BigInteger, nullable=False) phone = db.Column(db.String(20), nullable=False) name = db.Column(db.String(20), nullable=False) modify_time = db.Column(db.DateTime, nullable=False) create_time = db.Column(db.DateTime, nullable=False) def getByUserid(self, user_id): if not user_id: return None #@todo 这里会不会有问题 dbAddrLists = self.query.filter( YiAddressList.user_id == user_id).limit(10000).all() addrLists = [] for i in dbAddrLists: addrLists.append(self.row2dict(i)) return addrLists def blackNum(self, user_id): ''' 通讯录中有黑名单 ''' return 0 #where = and_(YiAddressList.user_id == user_id, YiUser.status == 5) #t = db.session.query(func.count(distinct(YiAddressList.id)).label("user_count")).outerjoin( # YiUser, YiAddressList.phone == YiUser.mobile) #res = t.filter(where).limit(1).first() #return res.user_count def getMessageList(self, mobiles): if len(mobiles) == 0: return [] counts = db.session.query(YiAddressList).filter( YiAddressList.phone.in_(mobiles)).limit(1000).all() return counts
class PhoneTagList(db.Model, BaseModel): LIMIT = 500 __bind_key__ = 'analysis_repertory' __tablename__ = 'phone_tag_list' id = db.Column(db.BigInteger, primary_key=True) phone = db.Column(db.String(20), nullable=False) source = db.Column(db.BigInteger, nullable=False) tag_type = db.Column(db.String(255), nullable=False) modify_time = db.Column(db.DateTime, nullable=False) create_time = db.Column(db.DateTime, nullable=False) other_info = db.Column(db.Text) ''' 通过手机号获取标签条数 ''' def getLabelAll(self, phone_tuple): phone_tuple = list(set(phone_tuple)) chunk_phone_list = self.chunk(phone_tuple) all_list = [] for phone_list in chunk_phone_list: son_list = db.session.query(PhoneTagList). \ filter(PhoneTagList.phone.in_(phone_list)).\ limit(self.LIMIT).\ all() if len(son_list) > 0: all_list.extend(son_list) return all_list def chunk(self, biglist): #大列表分割成小列表 limit = self.LIMIT mlen = len(biglist) mc = math.ceil(mlen / limit) lst = [] for i in range(0, int(mc)): lst.append(biglist[i * limit:(i + 1) * limit]) return lst
class ApiReport(db.Model, Base): # 指定数据库 __bind_key__ = 'xhh_antifraud' __tablename__ = 'api_report' id = db.Column(db.BigInteger, primary_key=True) base_id = db.Column(db.BigInteger) report_aomen = db.Column(db.Integer) report_110 = db.Column(db.Integer) report_120 = db.Column(db.Integer) report_lawyer = db.Column(db.Integer) report_court = db.Column(db.Integer) report_use_time = db.Column(db.Integer) report_shutdown = db.Column(db.Integer) report_name_match = db.Column(db.Integer) report_fcblack_idcard = db.Column(db.Integer) report_fcblack_phone = db.Column(db.Integer) report_fcblack = db.Column(db.Integer) report_operator_name = db.Column(db.String(20)) report_reliability = db.Column(db.Integer) report_night_percent = db.Column(db.Numeric(10, 2)) report_loan_connect = db.Column(db.String(50)) create_time = db.Column(db.DateTime, nullable=False)
class ApiOtherData(db.Model, Base): __bind_key__ = 'xhh_antifraud' __tablename__ = 'api_other_data' id = db.Column(db.BigInteger, primary_key=True) base_id = db.Column(db.BigInteger, nullable=False, server_default='0') learning_letter = db.Column(db.Integer, nullable=False, server_default='0') learning_letter_contrast = db.Column(db.String(32), nullable=False, server_default='') ocial_security = db.Column(db.Integer, nullable=False, server_default='0') ocial_security_contrast = db.Column(db.String(32), nullable=False, server_default='') accumulation_fund = db.Column(db.Integer, nullable=False, server_default='0') accumulation_fund_contrast = db.Column(db.String(32), nullable=False, server_default='') max_account_detail_balance = db.Column(db.String(32), nullable=False, server_default='') create_time = db.Column(db.DateTime, nullable=False)
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 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 YiUserRemitList(db.Model, BaseModel): __bind_key__ = 'xhh_yiyiyuan' __tablename__ = 'yi_user_remit_list' user_id = db.Column(db.BigInteger, primary_key=True) loan_id = db.Column(db.BigInteger, nullable=False, index=True) remit_status = db.Column(db.String(12)) fund = db.Column(db.Integer, nullable=False) create_time = db.Column(db.DateTime) def getMobiles(self, start_time, end_time): ''' 获取需要处理的数据, 默认查询1000条 ''' start_time = start_time end_time = end_time # 借款资方数据 sql = "SELECT u.mobile,u.user_id,l.loan_id FROM yi_user_remit_list l LEFT JOIN yi_user u ON l.user_id = u.user_id WHERE l.remit_status = 'SUCCESS' AND l.fund = 6 AND l.create_time >= '" + start_time + "' AND l.create_time <= '" + end_time + "' order by l.id DESC LIMIT 1000;" users = db.session.execute(sql, bind=self.get_engine()).fetchall() if len(users) == 0: return None return users
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 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