# coding: utf-8 import datetime from sqlalchemy import Column, DECIMAL, Date, DateTime, Index, String, Table, Text, text from sqlalchemy.dialects.mysql import BIGINT, INTEGER from sqlalchemy.ext.declarative import declarative_base from app.api.engine import TAMP_SQL, tamp_user_engine, tamp_diagnose_app_engine, pdf_folder from app.model.base import Base from app.model.base import BaseModel class Customer(Base, BaseModel): __tablename__ = 'customer' id = Column(String(64), primary_key=True, comment='id') customer_name = Column(String(64), index=True, comment='客户姓名') name = Column(String(64)) valueSex = Column(INTEGER(11), comment='性别 -- 1.男 2.女') valueBirth = Column(DateTime, comment='生日') phone = Column(String(20), comment='手机') email = Column(String(120), comment='邮箱') landline = Column(String(20), comment='座机') address = Column(String(120), comment='家庭地址') fax = Column(String(20), comment='传真') qq = Column(String(20)) wechat = Column(String(30), comment='微信号') wechatNickName = Column(String(30), comment='微信昵称') weibo = Column(String(30), comment='微博') work = Column(String(50), comment='工作') staff = Column(String(50), comment='职务') school = Column(String(50), comment='毕业学校') nativeplace = Column(String(30), comment='籍贯') valueMarry = Column(INTEGER(11), comment='婚姻状况1.未婚 2.已婚 3.离异') familyList = Column(String(500), comment='家庭成员') favor = Column(String(100), comment='兴趣爱好') concatTime = Column(String(30), comment='方便联系时间') wealth = Column(String(30), comment='财产') extra = Column(String(120)) total_assets = Column(DECIMAL(22, 6)) floating_earnings = Column(DECIMAL(22, 6)) create_by = Column(String(64)) create_time = Column(DateTime) update_by = Column(String(64)) update_time = Column(DateTime) delete_tag = Column(INTEGER(11)) member_since = Column(DateTime) last_seen = Column(DateTime) remark = Column(Text, comment='备注') group_id = Column(INTEGER(11), index=True, comment='组id') company_address = Column(String(200), comment='单位地址') other_address = Column(String(200), comment='其他地址') class CustomerAsset(Base, BaseModel): __tablename__ = 'customer_assets' id = Column(String(64), primary_key=True, nullable=False, comment='客户id') total_market = Column(DECIMAL(22, 6), comment='总市值') total_principal = Column(DECIMAL(22, 6), comment='总本金') accumulated_earnings = Column(DECIMAL(22, 6), comment='累计收益') floating_earnings_proportion = Column(DECIMAL(22, 6), comment='累计收益率') delete_tag = Column(INTEGER(11)) ifa_id = Column(String(64), primary_key=True, nullable=False, comment='理财师id') seven_profit_rate = Column(DECIMAL(22, 6), comment='最近7天收益率') thirty_profit_rate = Column(DECIMAL(22, 6), comment='最近30天收益率') ninety_profit_rate = Column(DECIMAL(22, 6), comment='最近90天收益率') half_year_profit_rate = Column(DECIMAL(22, 6), comment='最近半年收益率') year_profit_rate = Column(DECIMAL(22, 6), comment='今年收益率') class FundReportManange(Base, BaseModel): __tablename__ = 'fund_report_manange' id = Column(String(64), primary_key=True, comment='id') ifa_id = Column(String(64), comment='理财师id') default_template = Column(Text, comment='默认模板') custom_template = Column(Text, comment='自定义模版') create_by = Column(String(64)) create_time = Column(DateTime) update_by = Column(String(64)) update_time = Column(DateTime) delete_tag = Column(INTEGER(2), server_default=text("'0'")) name = Column(String(300), comment='模版名称') type = Column(INTEGER(2), comment='模版类型1持仓报告2诊断报告') default = Column(INTEGER(2), server_default=text("'0'")) def to_dict(self, allow_field=None): all_field = [r.name for r in self.__table__.columns] if allow_field: allow_field = set(allow_field) & set(allow_field) else: allow_field = all_field data = {c: int(getattr(self, c).timestamp()) if isinstance(getattr(self, c), datetime.datetime) else getattr(self, c) for c in allow_field} with TAMP_SQL(tamp_user_engine) as tamp_user: tamp_user_session = tamp_user.session sql = "select ui_username_mp from user_info where id = '{}'".format(self.ifa_id) res = tamp_user_session.execute(sql) if res: data['author_name'] = res.fetchone()[0] else: data['author_name'] = '' if self.default_template: data['sys_default'] = 1 else: data['sys_default'] = 0 return data class Group(Base, BaseModel): __tablename__ = 'group' __table_args__ = ( Index('describe', 'describe', 'ifauser_id', unique=True), ) id = Column(INTEGER(11), primary_key=True) describe = Column(String(50)) createtime = Column(DateTime) ifauser_id = Column(String(64)) class HoldDiagnoseReport(Base, BaseModel): __tablename__ = 'hold_diagnose_report' id = Column(INTEGER(11), primary_key=True) customer_id = Column(String(64)) ifa_id = Column(String(64)) update_time = Column(DateTime) update_status = Column(INTEGER(11)) file = Column(String(128)) be_viewed = Column(INTEGER(2), comment='0未查看1已查看') report_data = Column(Text) name = Column(String(100)) def to_dict(self, allow_field=None): all_field = [r.name for r in self.__table__.columns] if allow_field: allow_field = set(allow_field) & set(allow_field) else: allow_field = all_field file = '' if self.file: file = pdf_folder + self.file data = {c: int(getattr(self, c).timestamp()) if isinstance(getattr(self, c), datetime.datetime) else getattr(self, c) for c in allow_field} with TAMP_SQL(tamp_user_engine) as tamp_user, TAMP_SQL(tamp_diagnose_app_engine) as tamp_diagnose_app: tamp_user_session = tamp_user.session tamp_diagnose_app_session = tamp_diagnose_app.session sql = "select ui_username_mp from user_info where id = '{}'".format(self.ifa_id) res = tamp_user_session.execute(sql) sql2 = "select customer_name from tamp_diagnose_app.customer_view where id = '{}'".format(self.customer_id) res2 = tamp_diagnose_app_session.execute(sql2) res = res.fetchone() res2 = res2.fetchone() if data: data['file'] = file if res: data['author_name'] = res[0] else: data['author_name'] = '' if res2: data['customer_name'] = res2[0] else: data['customer_name'] = '' data['type'] = 2 return data class HoldReport(Base, BaseModel): __tablename__ = 'hold_report' id = Column(INTEGER(11), primary_key=True) customer_id = Column(String(64)) ifa_id = Column(String(64)) update_time = Column(DateTime) update_status = Column(INTEGER(11)) file = Column(String(128)) be_viewed = Column(INTEGER(2), comment='0未查看1已查看') report_data = Column(Text) name = Column(String(100)) def to_dict(self, allow_field=None): all_field = [r.name for r in self.__table__.columns] if allow_field: allow_field = set(allow_field) & set(allow_field) else: allow_field = all_field file = '' if self.file: file = pdf_folder + self.file data = {c: int(getattr(self, c).timestamp()) if isinstance(getattr(self, c), datetime.datetime) else getattr(self, c) for c in allow_field} with TAMP_SQL(tamp_user_engine) as tamp_user, TAMP_SQL(tamp_diagnose_app_engine) as tamp_diagnose_app: tamp_user_session = tamp_user.session tamp_diagnose_app_session = tamp_diagnose_app.session sql = "select ui_username_mp from user_info where id = '{}'".format(self.ifa_id) res = tamp_user_session.execute(sql) sql2 = "select customer_name from tamp_diagnose_app.customer_view where id = '{}'".format(self.customer_id) res2 = tamp_diagnose_app_session.execute(sql2) res = res.fetchone() res2 = res2.fetchone() if data: data['file'] = file if res: data['author_name'] = res[0] else: data['author_name'] = '' if res2: data['customer_name'] = res2[0] else: data['customer_name'] = '' data['type'] = 1 return data class PeriodicReport(Base, BaseModel): __tablename__ = 'periodic_report' id = Column(INTEGER(11), primary_key=True) customer_id = Column(String(64)) ifa_id = Column(String(64)) update_time = Column(DateTime) update_status = Column(INTEGER(11)) file = Column(String(128)) be_viewed = Column(INTEGER(2), comment='0未查看1已查看') report_data = Column(Text) name = Column(String(100)) def to_dict(self, allow_field=None): all_field = [r.name for r in self.__table__.columns] if allow_field: allow_field = set(allow_field) & set(allow_field) else: allow_field = all_field file = '' if self.file: file = pdf_folder + self.file data = {c: int(getattr(self, c).timestamp()) if isinstance(getattr(self, c), datetime.datetime) else getattr(self, c) for c in allow_field} with TAMP_SQL(tamp_user_engine) as tamp_user, TAMP_SQL(tamp_diagnose_app_engine) as tamp_diagnose_app: tamp_user_session = tamp_user.session tamp_diagnose_app_session = tamp_diagnose_app.session sql = "select ui_username_mp from user_info where id = '{}'".format(self.ifa_id) res = tamp_user_session.execute(sql) sql2 = "select customer_name from tamp_diagnose_app.customer_view where id = '{}'".format(self.customer_id) res2 = tamp_diagnose_app_session.execute(sql2) res = res.fetchone() res2 = res2.fetchone() if data: data['file'] = file if res: data['author_name'] = res[0] else: data['author_name'] = '' if res2: data['customer_name'] = res2[0] else: data['customer_name'] = '' data['type'] = 3 return data class IfaCustomer(Base, BaseModel): __tablename__ = 'ifa_customer' id = Column(String(64), primary_key=True) customer_id = Column(String(64)) ifa_id = Column(String(64)) fund_count = Column(INTEGER(11)) revision = Column(INTEGER(11)) create_by = Column(String(64)) create_time = Column(DateTime) update_by = Column(String(64)) update_time = Column(DateTime) delete_tag = Column(INTEGER(11)) class IfaFundCollection(Base, BaseModel): __tablename__ = 'ifa_fund_collection' __table_args__ = ( Index('unique', 'fund_id', 'ifa_id', 'delete_tag'), ) id = Column(INTEGER(11), primary_key=True, comment='id') fund_id = Column(String(64)) ifa_id = Column(String(64)) create_time = Column(DateTime) create_by = Column(String(255)) update_time = Column(DateTime) update_by = Column(DateTime) delete_tag = Column(INTEGER(4), server_default=text("'0'")) type = Column(INTEGER(4)) class IfauserFund(Base, BaseModel): __tablename__ = 'ifauser_fund' id = Column(INTEGER(11), primary_key=True) type = Column(INTEGER(11), index=True) fund_id = Column(String(30), index=True) fund_name = Column(String(30)) ifauser_id = Column(String(64)) customer_count = Column(INTEGER(11)) sum = Column(DECIMAL(22, 6)) class Operation(Base, BaseModel): __tablename__ = 'operations' id = Column(INTEGER(11), primary_key=True) operator_id = Column(String(64), index=True) describe = Column(Text) timestamp = Column(DateTime, index=True) ip = Column(Text) class RelocateSuggestion(Base, BaseModel): __tablename__ = 'relocate_suggestion' id = Column(String(64), primary_key=True) fund_id = Column(String(64)) substrategy = Column(INTEGER(11)) fund_name = Column(String(255)) percent = Column(DECIMAL(22, 6)) group_id = Column(String(255)) delete_tag = Column(INTEGER(11)) type = Column(INTEGER(11)) register_number = Column(String(20)) class RelocateSuggestionGroup(Base, BaseModel): __tablename__ = 'relocate_suggestion_group' id = Column(String(64), primary_key=True) group_name = Column(String(255)) ifa_id = Column(String(255)) customer_id = Column(String(255)) create_time = Column(DateTime) create_by = Column(String(255)) update_time = Column(DateTime) update_by = Column(String(255)) delete_tag = Column(INTEGER(11)) class UserHoldFundAsset(Base, BaseModel): __tablename__ = 'user_hold_fund_assets' id = Column(String(64), primary_key=True) reference_market = Column(DECIMAL(22, 6), comment='市值') holding_net = Column(DECIMAL(22, 6), comment='累计收益率') profit_or_loss = Column(DECIMAL(22, 6), comment='累计收益') delete_tag = Column(INTEGER(11)) annual_profit_rate = Column(DECIMAL(22, 6), comment='年化收益率') seven_profit = Column(DECIMAL(22, 6), comment='最近7天收益') seven_profit_rate = Column(DECIMAL(22, 6), comment='最近7天收益率') seven_annual_profit_rate = Column(DECIMAL(22, 6), comment='最近7天年化收益率') thirty_profit = Column(DECIMAL(22, 6), comment='最近30天收益') thirty_profit_rate = Column(DECIMAL(22, 6), comment='最近30天收益率') thirty_annual_profit_rate = Column(DECIMAL(22, 6), comment='最近30天年化收益率') ninety_profit = Column(DECIMAL(22, 6), comment='最近90天收益') ninety_profit_rate = Column(DECIMAL(22, 6), comment='最近90天收益率') ninety_annual_profit_rate = Column(DECIMAL(22, 6), comment='最近90天年化收益率') half_year_profit = Column(DECIMAL(22, 6), comment='最近半年收益') half_year_profit_rate = Column(DECIMAL(22, 6), comment='最近半年收益率') half_year_annual_profit_rate = Column(DECIMAL(22, 6), comment='最近半年年化收益率') year_profit = Column(DECIMAL(22, 6), comment='今年收益') year_profit_rate = Column(DECIMAL(22, 6), comment='今年收益率') year_annual_profit_rate = Column(DECIMAL(22, 6), comment='今年年化收益率') class UserHoldFundFromApp(Base, BaseModel): __tablename__ = 'user_hold_fund_from_app' id = Column(String(64), primary_key=True) type = Column(INTEGER(11), index=True, comment='0 公募 1 私募 2 优选') fund_id = Column(String(64), index=True) fund_name = Column(String(30)) customer_id = Column(String(64), index=True, comment='customer id') subscription_fee = Column(DECIMAL(22, 6), comment='申购费') nav = Column(DECIMAL(22, 6), comment='购买时候的净值') create_by = Column(String(64)) create_time = Column(DateTime) update_by = Column(String(64)) update_time = Column(DateTime) delete_tag = Column(INTEGER(11)) confirm_amount = Column(DECIMAL(22, 6), comment='确认金额') confirm_share = Column(DECIMAL(22, 6), comment='确认份额') order_source = Column(INTEGER(11)) order_type = Column(INTEGER(1), comment='1、申购或追加 2、赎回 4 暂时其他') remark = Column(String(255), comment='订单备注') user_id = Column(String(64), index=True, server_default=text("''"), comment='ifa的id') folio_name = Column(String(64), comment='组合名称，默认是default') confirm_share_date = Column(Date, comment='确认份额日期') pay_date = Column(Date, comment='订单支付时间')