class UAAmount(db.Model):
"""
用户理财产品金额变动历史
"""
id = db.Column(db.Integer(), primary_key=True)
date = db.Column(db.Date())
userasset_id = db.Column(db.Integer(),
db.ForeignKey('user_asset.id',
ondelete='RESTRICT'),
nullable=False)
amount = db.Column(db.Integer(), default=0)
update_time = db.Column(db.DateTime(timezone=True))
__table_args__ = (
UniqueConstraint('userasset_id', 'date',
name='uix_uaid_date'), # 联合唯一索引
)
def __init__(self, ua_id, amount, now):
self.userasset_id = ua_id
self.amount = amount
self.date = now.date()
self.update_time = now
db.session.add(self)
db.session.flush()
@property
def amount_yuan(self):
return round(self.amount / 100, 2)
@staticmethod
def update(ua_id, amount):
now = _now()
date = now.date()
uaa = UAAmount.query.filter_by(date=date, userasset_id=ua_id).first()
if uaa:
uaa.amount = amount
uaa.update_time = now
else:
uaa = UAAmount(ua_id, amount, now)
return uaa
@staticmethod
def clear_cache(model, operation):
cache.delete('user_asset_summary_{}'.format(model.user_asset.user_id))
@property
def update_time_str(self):
return self.update_time.strftime("%m-%d %H:%M")
class Agent(db.Model):
"""购买处(银行/基金公司/支付宝/微信)"""
id = db.Column(db.Integer(), primary_key=True)
name = db.Column(db.String(16), unique=True)
assets = db.relationship('UserAsset', backref='agent', lazy='dynamic')
def __init__(self, name):
self.name = name
db.session.add(self)
db.session.commit()
@staticmethod
def name_cache():
cache_key = 'agent_name'
if cache.get(cache_key):
return cache.get(cache_key)
name_dict = dict()
for agent in Agent.query.order_by('id').all():
name_dict[agent.id] = agent.name
cache.set(cache_key, name_dict)
return name_dict
@staticmethod
def clear_cache(model, operation):
cache_keys = ['agent_name']
cache.delete_many(*cache_keys)
@staticmethod
def user_agent(user_id):
cache_key = 'agent_user_{}'.format(user_id)
if cache.get(cache_key):
return cache.get(cache_key)
user_dict = dict()
for agent in Agent.query.join(UserAsset).filter(
Agent.id == UserAsset.agent_id,
UserAsset.user_id == user_id,
).distinct().order_by(Agent.id).all():
user_dict[agent.id] = agent.name
cache.set(cache_key, user_dict)
return user_dict
class Family(db.Model):
id = db.Column(db.Integer(), primary_key=True)
name = db.Column(db.String(20))
goal = db.Column(db.Integer(), default=0)
create_time = db.Column(db.DateTime(timezone=True), default=public.now)
update_time = db.Column(db.DateTime(timezone=True), default=public.now)
parent_id = db.Column(db.Integer())
users = db.relationship('User', backref='family', lazy='dynamic')
def __init__(self, parent_id, name, goal):
"""
创建家庭
:param name:
:param goal: 单位:分
"""
self.parent_id = parent_id,
self.name = name
self.goal = goal
self.create_time = public.now()
self.update_time = public.now()
db.session.add(self)
db.session.flush()
@property
def goal_yuan(self):
return int(self.goal / 100)
@property
def parent(self):
user = User.query.filter_by(id=self.parent_id).first()
if user:
return user.username
else:
return ''
class UserAsset(db.Model):
"""
用户投资项目
"""
id = db.Column(db.Integer(), primary_key=True)
agent_id = db.Column(db.Integer(),
db.ForeignKey('agent.id', ondelete='RESTRICT'),
nullable=False)
fp = db.Column(db.Integer(),
db.ForeignKey('financial_product.id', ondelete='RESTRICT'),
nullable=False)
start_time = db.Column(db.DateTime(timezone=True)) # 第一次买入时间
update_time = db.Column(db.DateTime(timezone=True), index=True) # 最后更新时间
user_id = db.Column(db.Integer(),
db.ForeignKey('user.id', ondelete='RESTRICT'),
nullable=False)
is_delete = db.Column(db.Boolean(), default=False)
amounts = db.relationship('UAAmount', backref='user_asset', lazy='dynamic')
__table_args__ = (
UniqueConstraint('fp', 'agent_id', 'user_id',
name='uix_ag_fp_user'), # 联合唯一索引
)
def __init__(self, agent_id, fp, user_id):
self.agent_id = agent_id
self.fp = fp
self.user_id = user_id
db.session.add(self)
db.session.flush()
@property
def fp_name(self):
return FinancialProduct.name_cache().get(self.fp)
@property
def agent_name(self):
return Agent.name_cache().get(self.agent_id)
@property
def last_amount(self):
return self.amounts.order_by(desc('id')).first()
@property
def update_time_str(self):
return self.update_time.strftime("%m-%d %H:%M")
@staticmethod
def clear_cache(model, operation):
if operation == 'insert':
# 新增持仓记录时，刷新用户的渠道缓存
cache.delete('agent_user_{}'.format(model.user_id))
class FPType(db.Model):
"""
理财产品类型
1银行存款 / 2货币基金 / 3债券基金 / 4股票基金 / 5股票 / 6银行理财 / 7保险理财 / 8黄金 / 9白银 / 10现金
"""
id = db.Column(db.Integer(), primary_key=True)
name = db.Column(db.String(8), unique=True)
fps = db.relationship('FinancialProduct', backref='type', lazy='dynamic')
@staticmethod
def dict():
return {
1: '银行存款',
2: '货币基金',
3: '债券基金',
4: '股票基金',
5: '股票',
6: '银行理财',
7: '保险理财',
8: '黄金',
9: '白银',
10: '现金',
}
class FinancialProduct(db.Model):
id = db.Column(db.Integer(), primary_key=True)
name = db.Column(db.String(32), unique=True)
code = db.Column(db.String(6), unique=True) # 基金/股票 代码
type_id = db.Column(db.Integer(),
db.ForeignKey('fp_type.id', ondelete='RESTRICT'),
nullable=False)
url = db.Column(db.Text(), default='{}')
meta = db.Column(db.Text(), default='{}')
update_time = db.Column(db.DateTime(timezone=True)) # 更新时间
assets = db.relationship('FPAsset',
secondary=fp_assets,
backref=db.backref('fps', lazy='dynamic'))
us_assets = db.relationship('UserAsset',
backref='financial_product',
lazy='dynamic')
def __init__(self, name, type_id, code=None):
self.name = name
self.type_id = type_id
self.code = code
db.session.add(self)
db.session.commit()
@staticmethod
def name_cache():
cache_key = 'fp_name'
if cache.get(cache_key):
return cache.get(cache_key)
name_dict = dict()
for fp in FinancialProduct.query.all():
name_dict[fp.id] = fp.name
cache.set(cache_key, name_dict)
return name_dict
@staticmethod
def clear_cache(model, operation):
cache_keys = ['fp_name']
cache.delete_many(*cache_keys)
class FPAsset(db.Model):
"""
金融产品的资产分布枚举： 股票、债券、现金、其他
"""
id = db.Column(db.Integer(), primary_key=True)
name = db.Column(db.String(16), unique=True)
if cache.get(cache_key):
return cache.get(cache_key)
user_dict = dict()
for agent in Agent.query.join(UserAsset).filter(
Agent.id == UserAsset.agent_id,
UserAsset.user_id == user_id,
).distinct().order_by(Agent.id).all():
user_dict[agent.id] = agent.name
cache.set(cache_key, user_dict)
return user_dict
fp_assets = db.Table(
'fp_assets',
db.Column('fp_id',
db.Integer(),
db.ForeignKey('financial_product.id'),
nullable=False),
db.Column('fpa_id',
db.Integer(),
db.ForeignKey('fp_asset.id'),
nullable=False),
UniqueConstraint('fp_id', 'fpa_id',
name='fp_fpa_unique') # 联合唯一索引,name索引的名字
)
class FinancialProduct(db.Model):
id = db.Column(db.Integer(), primary_key=True)
name = db.Column(db.String(32), unique=True)
code = db.Column(db.String(6), unique=True) # 基金/股票 代码
type_id = db.Column(db.Integer(),
class User(db.Model, UserMixin):
id = db.Column(db.Integer(), primary_key=True)
username = db.Column(db.String(20), unique=True)
password = db.Column(db.String(200))
email = db.Column(db.String(32), unique=True)
last_login = db.Column(db.DateTime(timezone=True))
join_date = db.Column(db.DateTime(timezone=True), default=public.now)
goal = db.Column(db.Integer(), default=0)
family_id = db.Column(db.Integer(),
db.ForeignKey('family.id', ondelete='RESTRICT'),
default=None)
is_staff = db.Column(db.Boolean(), default=False)
def __init__(self, username, password, email):
"""
创建用户
:param username:
:param password:
"""
self.username = username
self.password = generate_password_hash(password)
self.email = email
db.session.add(self)
db.session.commit()
def check_password(self, password):
"""
验证密码
:param password: 密码明文
:return: True or False
"""
return check_password_hash(self.password, password)
@property
def avatar(self):
return public.get_avatar(self.email)
def refresh_last_login(self):
self.last_login = public.now()
db.session.commit()
@property
def goal_yuan(self):
if self.goal:
return int(self.goal / 100)
else:
return 0
@property
def asset_summary(self):
"""
用户支持概况
:return:
"""
cache_key = 'user_asset_summary_{}'.format(self.id)
if cache.get(cache_key):
return cache.get(cache_key)
asset_dict = {
'goal': self.goal_yuan, # 金额目标
'fp_count': 0, # 理财产品数量
'total_amount': 0, # 总金额
'last_update': '', # 最后更新
'agent_tuples': list(), # 渠道数据 (名称，金额，理财产品数量)
'fptype_tuples': list(), # 理财类型数据 (名称，金额，理财产品数量)
}
agent_dict = dict()
fptype_dict = dict()
last_update = None
for ua in UserAsset.query.filter_by(user_id=self.id,
is_delete=False).all():
# 获取最后更新时间
if not last_update:
asset_dict['last_update'] = ua.update_time_str
elif ua.update_time > last_update:
asset_dict['last_update'] = ua.update_time_str
amount = ua.last_amount.amount_yuan
asset_dict['total_amount'] += amount
asset_dict['fp_count'] += 1
# 汇总每个渠道的总金额
agent_id = ua.agent_id
if agent_id in agent_dict:
agent_dict[agent_id]['amount'] += amount
agent_dict[agent_id]['count'] += 1
else:
agent_dict[agent_id] = {'amount': amount, 'count': 1}
# 汇总每个理财类型的总金额
fp_type_id = ua.financial_product.type_id
if fp_type_id in fptype_dict:
fptype_dict[fp_type_id]['amount'] += amount
fptype_dict[fp_type_id]['count'] += 1
else:
fptype_dict[fp_type_id] = {'amount': amount, 'count': 1}
agent_tuples = []
for k, v in agent_dict.items():
agent_tuples.append(
(Agent.name_cache().get(k), v.get('amount'), v.get('count')))
agent_tuples.sort(key=lambda x: x[1], reverse=True)
asset_dict['agent_tuples'] = agent_tuples
fptype_tuples = []
for k, v in fptype_dict.items():
fptype_tuples.append(
(FPType.dict().get(k), v.get('amount'), v.get('count')))
fptype_tuples.sort(key=lambda x: x[1], reverse=True)
asset_dict['fptype_tuples'] = fptype_tuples
if self.goal:
goal_rate = round(
asset_dict['total_amount'] / self.goal_yuan * 100, 1)
else:
goal_rate = 0
asset_dict['goal_rate'] = goal_rate
asset_dict['total_amount'] = round(asset_dict.get('total_amount'), 2)
cache.set(cache_key, asset_dict)
return asset_dict
@property
def family_members(self):
"""用户的家庭成员(包括用户本人)，如未加入家庭，则只有用户本人"""
if self.family_id:
return self.family.users.all()
else:
return [self]