| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111 |
- # app/models/settlement.py
- from datetime import datetime
- from sqlalchemy import Column, Integer, String, DateTime, JSON, Enum, Text
- from app.core.database import Base
- class VasSettlementPeriod(Base):
- __tablename__ = "vas_settlement_period"
- __ddl__ = """
- CREATE TABLE IF NOT EXISTS `vas_settlement_period` (
- `id` VARCHAR(32) NOT NULL COMMENT '账期ID如 2026-10',
- `name` VARCHAR(128) NOT NULL,
- `start_date` DATETIME NOT NULL,
- `end_date` DATETIME NOT NULL,
- `total_orders` INT NOT NULL DEFAULT 0,
- `total_revenue` INT NOT NULL DEFAULT 0,
- `total_cost` INT NOT NULL DEFAULT 0,
- `avg_cost` INT NOT NULL DEFAULT 0,
- `net_profit` INT NOT NULL DEFAULT 0,
- `status` ENUM('draft', 'confirmed', 'paid') NOT NULL DEFAULT 'draft',
- `summary_data` JSON NULL,
- `confirmed_by` VARCHAR(128) NULL,
- `confirmed_at` DATETIME NULL,
- `paid_at` DATETIME NULL,
- `notes` TEXT NULL,
- `created_at` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
- `updated_at` DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
- PRIMARY KEY (`id`)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='结算账期总表';
- """
- id = Column(String(32), primary_key=True) # 账期ID,如 2026-10 或 2026-10-Q1
- name = Column(String(128), nullable=False) # 账期显示名称
- start_date = Column(DateTime, nullable=False)
- end_date = Column(DateTime, nullable=False)
-
- total_orders = Column(Integer, default=0, nullable=False) # 本账期履约完成订单总数
- total_revenue = Column(Integer, default=0, nullable=False) # 本期实收总额 (分)
- total_cost = Column(Integer, default=0, nullable=False) # 本期耗材总成本 (分)
- avg_cost = Column(Integer, default=0, nullable=False) # 单均摊薄耗材 (分)
- net_profit = Column(Integer, default=0, nullable=False) # 本期总净毛利 (分)
- status = Column(
- Enum('draft', 'confirmed', 'paid', name='settlement_period_status'),
- default='draft',
- nullable=False
- )
- # 包含甲乙分红、各对接人提成总计、开发者版权总计、过失扣除明细的完整汇总 JSON
- summary_data = Column(JSON, nullable=True)
- confirmed_by = Column(String(128), nullable=True)
- confirmed_at = Column(DateTime, nullable=True)
- paid_at = Column(DateTime, nullable=True)
- notes = Column(Text, nullable=True)
- created_at = Column(DateTime, default=datetime.utcnow)
- updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
- class VasSettlementDetail(Base):
- __tablename__ = "vas_settlement_detail"
- __ddl__ = """
- CREATE TABLE IF NOT EXISTS `vas_settlement_detail` (
- `id` INT NOT NULL AUTO_INCREMENT,
- `period_id` VARCHAR(32) NOT NULL,
- `order_id` VARCHAR(128) NOT NULL,
- `channel_type` VARCHAR(32) NOT NULL,
- `agency_id` VARCHAR(64) NULL,
- `handler_id` VARCHAR(128) NULL,
- `developer_id` VARCHAR(128) NULL,
- `order_amount` INT NOT NULL,
- `avg_cost` INT NOT NULL,
- `net_profit` INT NOT NULL,
- `tech_fee` INT NOT NULL DEFAULT 0,
- `sales_fee` INT NOT NULL DEFAULT 0,
- `pool_fee` INT NOT NULL DEFAULT 0,
- `partner_a_amount` INT NOT NULL DEFAULT 0,
- `partner_b_amount` INT NOT NULL DEFAULT 0,
- `snapshot` JSON NULL,
- `created_at` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
- PRIMARY KEY (`id`),
- INDEX `idx_vas_settle_det_period` (`period_id`),
- INDEX `idx_vas_settle_det_order` (`order_id`)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='单笔订单分润快照表';
- """
- id = Column(Integer, primary_key=True, autoincrement=True)
- period_id = Column(String(32), nullable=False, index=True)
- order_id = Column(String(128), nullable=False, index=True)
-
- channel_type = Column(String(32), nullable=False)
- agency_id = Column(String(64), nullable=True)
- handler_id = Column(String(128), nullable=True) # 40%提成归属人
- developer_id = Column(String(128), nullable=True) # 20%技术版权归属人
- order_amount = Column(Integer, nullable=False) # 订单实收
- avg_cost = Column(Integer, nullable=False) # 均摊耗材
- net_profit = Column(Integer, nullable=False) # 单单毛利
- tech_fee = Column(Integer, default=0, nullable=False) # 20% 技术版权费
- sales_fee = Column(Integer, default=0, nullable=False) # 40% 销售包干提成
- pool_fee = Column(Integer, default=0, nullable=False) # 40% 合伙分红池
- partner_a_amount = Column(Integer, default=0, nullable=False) # 甲应得金额
- partner_b_amount = Column(Integer, default=0, nullable=False) # 乙应得金额
- # 包含该单完整计算快照
- snapshot = Column(JSON, nullable=True)
- created_at = Column(DateTime, default=datetime.utcnow)
|