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