tamp_diagnose_app.py

# 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
        if self.file:
            self.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:
                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
        if self.file:
            self.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:
                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
        if self.file:
            self.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:
                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='订单支付时间')