199 lines
8.7 KiB
Python
199 lines
8.7 KiB
Python
from sqlalchemy import Column, Integer, String, DateTime, Date, DECIMAL, Text, ForeignKey, CheckConstraint
|
||
from sqlalchemy.orm import relationship
|
||
from sqlalchemy.sql import func
|
||
from app.database import Base
|
||
|
||
|
||
class User(Base):
|
||
__tablename__ = "users"
|
||
|
||
id = Column(Integer, primary_key=True, index=True, autoincrement=True)
|
||
username = Column(String(50), unique=True, nullable=False, index=True)
|
||
password_hash = Column(String(255), nullable=False)
|
||
role = Column(String(20), nullable=False, default="employee") # admin/employee/agent
|
||
name = Column(String(50), nullable=False)
|
||
phone = Column(String(20))
|
||
email = Column(String(100))
|
||
status = Column(Integer, nullable=False, default=1) # 0-禁用，1-启用
|
||
last_login_at = Column(DateTime)
|
||
created_at = Column(DateTime, server_default=func.now())
|
||
updated_at = Column(DateTime, server_default=func.now(), onupdate=func.now())
|
||
|
||
# 关联关系
|
||
employee = relationship("Employee", back_populates="user", uselist=False)
|
||
agent = relationship("SecondaryAgent", back_populates="user", uselist=False)
|
||
|
||
__table_args__ = (
|
||
CheckConstraint(role.in_(['admin', 'employee', 'agent'])),
|
||
)
|
||
|
||
|
||
class Employee(Base):
|
||
__tablename__ = "employees"
|
||
|
||
id = Column(Integer, primary_key=True, index=True, autoincrement=True)
|
||
user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), nullable=False)
|
||
base_salary = Column(DECIMAL(10, 2), nullable=False, default=4000.00)
|
||
monthly_target = Column(DECIMAL(15, 2), nullable=False, default=0.00)
|
||
quarterly_target = Column(DECIMAL(15, 2), nullable=False, default=0.00)
|
||
half_year_target = Column(DECIMAL(15, 2), nullable=False, default=0.00)
|
||
yearly_target = Column(DECIMAL(15, 2), nullable=False, default=0.00)
|
||
hire_date = Column(Date)
|
||
department = Column(String(50))
|
||
position = Column(String(50))
|
||
created_at = Column(DateTime, server_default=func.now())
|
||
updated_at = Column(DateTime, server_default=func.now(), onupdate=func.now())
|
||
|
||
# 关联关系
|
||
user = relationship("User", back_populates="employee")
|
||
agents = relationship("SecondaryAgent", back_populates="employee")
|
||
performance_records = relationship("PerformanceRecord", back_populates="employee")
|
||
calculation_results = relationship("CalculationResult", back_populates="employee")
|
||
|
||
|
||
class SecondaryAgent(Base):
|
||
__tablename__ = "secondary_agents"
|
||
|
||
id = Column(Integer, primary_key=True, index=True, autoincrement=True)
|
||
user_id = Column(Integer, ForeignKey("users.id", ondelete="SET NULL"))
|
||
employee_id = Column(Integer, ForeignKey("employees.id", ondelete="CASCADE"), nullable=False)
|
||
company_name = Column(String(100), nullable=False)
|
||
contact_name = Column(String(50))
|
||
contact_phone = Column(String(20))
|
||
profit_share_rate = Column(DECIMAL(5, 2), nullable=False, default=0.60)
|
||
address = Column(String(255))
|
||
remark = Column(Text)
|
||
status = Column(Integer, nullable=False, default=1)
|
||
created_at = Column(DateTime, server_default=func.now())
|
||
updated_at = Column(DateTime, server_default=func.now(), onupdate=func.now())
|
||
|
||
# 关联关系
|
||
user = relationship("User", back_populates="agent")
|
||
employee = relationship("Employee", back_populates="agents")
|
||
performance_records = relationship("PerformanceRecord", back_populates="agent")
|
||
|
||
|
||
class Setting(Base):
|
||
__tablename__ = "settings"
|
||
|
||
id = Column(Integer, primary_key=True, index=True, autoincrement=True)
|
||
setting_key = Column(String(50), unique=True, nullable=False)
|
||
setting_value = Column(Text, nullable=False)
|
||
description = Column(String(255))
|
||
group_name = Column(String(50), default="general")
|
||
sort_order = Column(Integer, default=0)
|
||
updated_at = Column(DateTime, server_default=func.now(), onupdate=func.now())
|
||
|
||
|
||
class ProductCategory(Base):
|
||
__tablename__ = "product_categories"
|
||
|
||
id = Column(Integer, primary_key=True, index=True, autoincrement=True)
|
||
name = Column(String(50), nullable=False)
|
||
code = Column(String(30), unique=True)
|
||
parent_id = Column(Integer, ForeignKey("product_categories.id", ondelete="SET NULL"))
|
||
commission_rate = Column(DECIMAL(5, 2), nullable=False, default=0.03)
|
||
monthly_rebate = Column(DECIMAL(5, 2), nullable=False, default=0.10)
|
||
quarterly_rebate = Column(DECIMAL(5, 2))
|
||
is_main_product = Column(Integer, nullable=False, default=0)
|
||
sort_order = Column(Integer, default=0)
|
||
status = Column(Integer, nullable=False, default=1) # 0-下线/禁用，1-启用
|
||
# 火山引擎导入相关
|
||
source_file = Column(String(100))
|
||
import_batch = Column(String(50))
|
||
last_import_at = Column(DateTime)
|
||
created_at = Column(DateTime, server_default=func.now())
|
||
updated_at = Column(DateTime, server_default=func.now(), onupdate=func.now())
|
||
|
||
# 关联关系
|
||
parent = relationship("ProductCategory", remote_side=[id], backref="children")
|
||
performance_records = relationship("PerformanceRecord", back_populates="category")
|
||
|
||
|
||
class PerformanceRecord(Base):
|
||
__tablename__ = "performance_records"
|
||
|
||
id = Column(Integer, primary_key=True, index=True, autoincrement=True)
|
||
record_type = Column(String(20), nullable=False) # employee/agent
|
||
employee_id = Column(Integer, ForeignKey("employees.id", ondelete="SET NULL"))
|
||
agent_id = Column(Integer, ForeignKey("secondary_agents.id", ondelete="SET NULL"))
|
||
category_id = Column(Integer, ForeignKey("product_categories.id", ondelete="RESTRICT"), nullable=False)
|
||
amount = Column(DECIMAL(15, 2), nullable=False, default=0.00)
|
||
record_date = Column(Date, nullable=False)
|
||
customer_name = Column(String(100))
|
||
order_no = Column(String(50))
|
||
remark = Column(Text)
|
||
created_by = Column(Integer, ForeignKey("users.id", ondelete="SET NULL"))
|
||
created_at = Column(DateTime, server_default=func.now())
|
||
updated_at = Column(DateTime, server_default=func.now(), onupdate=func.now())
|
||
|
||
# 关联关系
|
||
employee = relationship("Employee", back_populates="performance_records")
|
||
agent = relationship("SecondaryAgent", back_populates="performance_records")
|
||
category = relationship("ProductCategory", back_populates="performance_records")
|
||
|
||
__table_args__ = (
|
||
CheckConstraint(record_type.in_(['employee', 'agent'])),
|
||
)
|
||
|
||
|
||
class CalculationResult(Base):
|
||
__tablename__ = "calculation_results"
|
||
|
||
id = Column(Integer, primary_key=True, index=True, autoincrement=True)
|
||
employee_id = Column(Integer, ForeignKey("employees.id", ondelete="CASCADE"), nullable=False)
|
||
calc_period = Column(String(20), nullable=False) # monthly/quarterly/half_yearly/yearly
|
||
calc_year = Column(Integer, nullable=False)
|
||
calc_month = Column(Integer)
|
||
calc_quarter = Column(Integer)
|
||
period_start_date = Column(Date, nullable=False)
|
||
period_end_date = Column(Date, nullable=False)
|
||
|
||
# 业绩数据
|
||
total_performance = Column(DECIMAL(15, 2), nullable=False, default=0.00)
|
||
target_amount = Column(DECIMAL(15, 2), nullable=False, default=0.00)
|
||
completion_rate = Column(DECIMAL(5, 2), nullable=False, default=0.00)
|
||
|
||
# 员工收益
|
||
base_salary = Column(DECIMAL(10, 2), nullable=False, default=0.00)
|
||
performance_bonus = Column(DECIMAL(10, 2), nullable=False, default=0.00)
|
||
personal_commission = Column(DECIMAL(15, 2), nullable=False, default=0.00)
|
||
agent_commission = Column(DECIMAL(15, 2), nullable=False, default=0.00)
|
||
total_income = Column(DECIMAL(15, 2), nullable=False, default=0.00)
|
||
|
||
# 公司收益
|
||
company_rebate = Column(DECIMAL(15, 2), nullable=False, default=0.00)
|
||
company_cost = Column(DECIMAL(15, 2), nullable=False, default=0.00)
|
||
company_profit = Column(DECIMAL(15, 2), nullable=False, default=0.00)
|
||
|
||
# 二级代理收益
|
||
agent_performance = Column(DECIMAL(15, 2), nullable=False, default=0.00)
|
||
agent_share_amount = Column(DECIMAL(15, 2), nullable=False, default=0.00)
|
||
|
||
# 计算详情（JSON格式）
|
||
detail_json = Column(Text)
|
||
|
||
created_at = Column(DateTime, server_default=func.now())
|
||
|
||
# 关联关系
|
||
employee = relationship("Employee", back_populates="calculation_results")
|
||
|
||
__table_args__ = (
|
||
CheckConstraint(calc_period.in_(['monthly', 'quarterly', 'half_yearly', 'yearly'])),
|
||
)
|
||
|
||
|
||
class OperationLog(Base):
|
||
__tablename__ = "operation_logs"
|
||
|
||
id = Column(Integer, primary_key=True, index=True, autoincrement=True)
|
||
user_id = Column(Integer, ForeignKey("users.id", ondelete="SET NULL"))
|
||
action = Column(String(50), nullable=False)
|
||
target_type = Column(String(50))
|
||
target_id = Column(Integer)
|
||
old_value = Column(Text)
|
||
new_value = Column(Text)
|
||
ip_address = Column(String(50))
|
||
user_agent = Column(String(255))
|
||
created_at = Column(DateTime, server_default=func.now())
|