34047007@qq.com
e937fc930c
init 后首次落盘，累计工作： - 数据侧：simulate_breeding_data real-scale 3.5万树重建 + 分层观测 - 引擎侧：ABLUP 稀疏生产档 EM-REML（blup 1.6.0/1.7.0）；性能倒挂修复—— 系谱闭包收口为 BFS 可达祖先 + N_EXACT 3000 对齐 N_SUBSAMPLE（选中 200~3000 树 不再顶进数小时精确迹尾），A/B 数值等价实证 + 守卫探针 - 前端：观测过滤 + 表单/HTTP 工具链完善 - 文档：业务链/观测梳理 + 引擎实施记录 + 规划对照总表 - gitignore：排除 Temp/调试脚本/一次性验证输出
1545 lines
90 KiB
Python
1545 lines
90 KiB
Python
#!/usr/bin/env python
|
||
# -*- coding: utf-8 -*-
|
||
"""桃育种压测级模拟数据生成器（2026-08-06，自洽式）。
|
||
|
||
目标：在 dev 库（Postgres）上生成 20 年时间线的真实感育种数据——
|
||
- ~5 万棵单株（F1 杂交圃 / F2 自交分离 / BC1 回交）
|
||
- 百万级性状观测（童期 juvenile + 成株 evaluation，含数值/文本两型）
|
||
- 完整选择晋级链（初选 sp → 复选 ap → 品系 line → 区试 regional → 审定 released），
|
||
晋级走真实服务层 SelectionResultService（创建 clone/germplasm/pedigree、回写树阶段与状态）
|
||
- 单株评价、农事操作（采收同步产量）、定植批次、区域试验（trial/study/entry）
|
||
|
||
设计要点：
|
||
- 参考数据（性状/人员/基地/地块/育种目标/规则/砧木/基础种质池）若缺失则补齐，不清除既有参考数据。
|
||
- 业务数据（bre_* 表）按 FK 序全量清空重建（--no-wipe 跳过）。
|
||
- 数值性状用简化无穷小模型：BV（父母均值+孟德尔抽样）+ 年度效应 + 误差，按性状 h² 截断到有效区间；
|
||
选择依据 = 各性状 BV 标准化加权指数（升序性状权重取负），保证入选单株表型确实更优（选择信号真实）。
|
||
- 过程内直调服务层（AuthSchema 超管身份，Permission 不过滤，免登录免验证码），
|
||
批量晋级复用 create_batch（单事务逐棵 create，失败不拖垮整批）。
|
||
- 淘汰记录（is_selected=eliminated）与淘汰树状态均在各轮正向晋级**全部完成之后**批量回写，
|
||
避免正向晋级校验（_assert_stage_forward）先看到 eliminated 结论而误判“结论回退”。
|
||
|
||
运行：
|
||
ENVIRONMENT=dev python backend/scripts/simulate_breeding_data.py --dry-run # 小规模验证
|
||
ENVIRONMENT=dev python backend/scripts/simulate_breeding_data.py --verify # 全量 + 自检
|
||
"""
|
||
from __future__ import annotations
|
||
|
||
import argparse
|
||
import asyncio
|
||
import json
|
||
import random
|
||
import sys
|
||
from datetime import UTC, date, datetime
|
||
from pathlib import Path
|
||
|
||
import numpy as np
|
||
|
||
BACKEND_DIR = Path(__file__).resolve().parents[1]
|
||
sys.path.insert(0, str(BACKEND_DIR))
|
||
|
||
# Windows 控制台 GBK 下中文 print 会乱码，统一以 UTF-8 输出
|
||
try:
|
||
sys.stdout.reconfigure(encoding="utf-8", line_buffering=True)
|
||
sys.stderr.reconfigure(encoding="utf-8", line_buffering=True)
|
||
except (AttributeError, ValueError):
|
||
pass
|
||
|
||
from sqlalchemy import func, insert, select, text # noqa: E402
|
||
from sqlalchemy.dialects.postgresql import insert as pg_insert # noqa: E402
|
||
from sqlalchemy.ext.asyncio import async_sessionmaker, create_async_engine # noqa: E402
|
||
|
||
from app.config.setting import settings # noqa: E402
|
||
from app.core.base_schema import AuthSchema, CoreUserSchema # noqa: E402
|
||
from app.core.bre_audit_ctx import bre_audit_suppress # noqa: E402
|
||
from app.utils.common_util import uuid4_str # noqa: E402
|
||
|
||
from app.api.v1.module_bre.cross_combination.model import CrossCombinationModel # noqa: E402
|
||
from app.api.v1.module_bre.tree.model import TreeModel # noqa: E402
|
||
from app.api.v1.module_bre.planting.model import PlantingModel # noqa: E402
|
||
from app.api.v1.module_bre.tree_evaluation.model import TreeEvaluationModel # noqa: E402
|
||
from app.api.v1.module_bre.trait_observation.model import TraitObservationModel # noqa: E402
|
||
from app.api.v1.module_bre.selection_result.model import SelectionResultModel # noqa: E402
|
||
from app.api.v1.module_bre.field_operation.model import FieldOperationModel # noqa: E402
|
||
from app.api.v1.module_bre.trial.model import TrialModel # noqa: E402
|
||
from app.api.v1.module_bre.trial_study.model import TrialStudyModel # noqa: E402
|
||
from app.api.v1.module_bre.trial_study_entry.model import TrialStudyEntryModel # noqa: E402
|
||
from app.api.v1.module_bre.trait.model import TraitModel # noqa: E402
|
||
from app.api.v1.module_bre.germplasm.model import BreedingGermplasmModel # noqa: E402
|
||
from app.api.v1.module_bre.target.model import TargetModel # noqa: E402
|
||
from app.api.v1.module_bre.plan.model import PlanModel # noqa: E402
|
||
from app.api.v1.module_bre.personnel.model import PersonnelModel # noqa: E402
|
||
from app.api.v1.module_bre.site.model import BreedingSiteModel, BreedingPlotModel # noqa: E402
|
||
from app.api.v1.module_bre.rootstock.model import RootstockModel # noqa: E402
|
||
from app.api.v1.module_bre.selection_rule.model import SelectionRuleModel # noqa: E402
|
||
from app.api.v1.module_bre.selection_result.service import SelectionResultService # noqa: E402
|
||
from app.api.v1.module_bre.selection_result.schema import SelectionResultBatchCreateSchema # noqa: E402
|
||
from app.api.v1.module_bre.pollination.model import PollinationModel # noqa: E402
|
||
from app.api.v1.module_bre.pollen.model import PollenModel # noqa: E402
|
||
from app.api.v1.module_bre.seed_lot.model import SeedLotModel # noqa: E402
|
||
from app.api.v1.module_bre.seed_treatment.model import SeedTreatmentModel # noqa: E402
|
||
from app.api.v1.module_bre.seedling.model import SeedlingModel # noqa: E402
|
||
from app.api.v1.module_bre.propagation.model import PropagationModel # noqa: E402
|
||
from app.api.v1.module_bre.environment_condition.model import EnvironmentConditionModel # noqa: E402
|
||
from app.api.v1.module_bre.observation.model import ObservationModel # noqa: E402
|
||
from app.api.v1.module_bre.treatment.model import TreatmentModel # noqa: E402
|
||
from app.api.v1.module_bre.clone.model import CloneModel # noqa: E402
|
||
# UserModel 必须显式导入：多个 module_bre 模型的 relationship 以字符串引用 UserModel，
|
||
# 未注册进 mapper registry 会在首次配置映射时抛 InvalidRequestError
|
||
from app.api.v1.module_system.user.model import UserModel # noqa: E402,F401
|
||
|
||
# ---------------------------------------------------------------------------
|
||
# 常量与参数
|
||
# ---------------------------------------------------------------------------
|
||
WINDOW_END = 2026 # 数据“今天”年份（2026-08-06）
|
||
F1_YEARS = range(2007, 2024) # F1 杂交组合年份 2007..2023
|
||
F1_TREES = 60 # 10 人团队真实规模：F1 = 17年×12×60 = 12,240
|
||
F2_COMBOS = 120
|
||
F2_TREES = 30 # F2 = 120×30 = 3,600
|
||
BC_COMBOS = 40
|
||
BC_TREES = 20 # BC = 40×20 = 800（全圃活体合计 ≈ 3.4 万，含 ramet）
|
||
K_RAMETS = 4 # 每入选克隆的无性系苗（ramet）数：保证 ABLUP G1 每 clone ≥2 样本
|
||
RAMET_EVAL_YEARS = 2 # 无性系苗成株/童期观测年数（自初选年起的连续年份）
|
||
|
||
# 晋级轮次：名称 / 选育结论 / 入选比例 / 起始阶段 / 晋级阶段 / 距定植年偏移
|
||
ROUNDS = [
|
||
("sp", "selected", 0.35, "seedling", "sp", 3),
|
||
("ap", "primary", 0.50, "sp", "ap", 5),
|
||
("line", "key", 0.40, "ap", "line", 6),
|
||
("regional", "preserved", 0.50, "line", "regional_trial", 7),
|
||
("released", "preserved", 0.30, "regional_trial", "released", 8),
|
||
]
|
||
|
||
# 数值性状：code, name, unit, category, vmin, vmax, stage, direction, into_ebv, mu, sd, h2, weight
|
||
NUMERIC_TRAITS = [
|
||
("single_tree_yield", "单株产量", "kg", "产量", 0, 60, "evaluation", "desc", "1", 18.0, 8.0, 0.35, 0.25),
|
||
("fruit_number", "结果个数", "个", "产量", 0, 600, "evaluation", "desc", "1", 220.0, 80.0, 0.30, 0.03),
|
||
("avg_fruit_weight", "平均单果重", "g", "果形", 50, 400, "evaluation", "desc", "1", 210.0, 50.0, 0.40, 0.20),
|
||
("marketable_rate", "好果率", "%", "品质", 0, 100, "evaluation", "desc", "1", 82.0, 12.0, 0.30, 0.15),
|
||
("ssc", "可溶性固形物", "%", "品质", 5, 22, "evaluation", "desc", "1", 12.5, 2.2, 0.55, 0.20),
|
||
("firmness_value", "果肉硬度", "kg/cm²", "品质", 0, 20, "evaluation", "desc", "1", 8.5, 2.5, 0.40, 0.05),
|
||
("acidity", "可滴定酸", "%", "品质", 0, 3.5, "evaluation", "asc", "1", 0.7, 0.25, 0.40, -0.05),
|
||
("red_blush", "着色度", "%", "果形", 0, 100, "evaluation", "desc", "1", 55.0, 22.0, 0.50, 0.05),
|
||
("chilling_requirement", "需冷量", "h", "物候", 0, 1200, "evaluation", "asc", "1", 750.0, 180.0, 0.60, -0.05),
|
||
("stone_weight", "核重", "g", "果形", 0, 30, "evaluation", "asc", "1", 8.0, 3.0, 0.30, -0.02),
|
||
("fruit_length", "果实纵径", "mm", "果形", 40, 110, "evaluation", "desc", "1", 68.0, 10.0, 0.35, 0.02),
|
||
("fruit_diameter", "果实横径", "mm", "果形", 40, 120, "evaluation", "desc", "1", 72.0, 12.0, 0.35, 0.02),
|
||
("uniformity", "果实整齐度", "%", "品质", 0, 100, "evaluation", "desc", "1", 78.0, 10.0, 0.25, 0.02),
|
||
("seedling_height", "苗高", "cm", "营养", 0, 400, "juvenile", "desc", "0", 180.0, 50.0, 0.30, 0.0),
|
||
("trunk_diameter", "地径", "cm", "营养", 0, 20, "juvenile", "desc", "0", 6.0, 1.8, 0.30, 0.0),
|
||
("shoot_growth", "新梢生长量", "cm", "营养", 0, 250, "juvenile", "desc", "0", 90.0, 35.0, 0.25, 0.0),
|
||
("leaf_disease_severity", "叶部病害程度", "%", "抗性", 0, 100, "juvenile", "asc", "0", 15.0, 12.0, 0.20, 0.0),
|
||
]
|
||
|
||
# 文本性状：code, name, category, stage, options, probs
|
||
TEXT_TRAITS = [
|
||
("disease_resistance", "抗病性", "抗性", "evaluation", ["高抗", "抗", "中抗", "感"], [0.12, 0.35, 0.35, 0.18]),
|
||
("flesh_color", "果肉颜色", "品质", "evaluation", ["白色", "黄白", "黄色", "红色"], [0.35, 0.25, 0.30, 0.10]),
|
||
("fruit_shape", "果实形状", "果形", "evaluation", ["圆形", "扁圆", "蟠桃形", "椭圆形"], [0.40, 0.20, 0.15, 0.25]),
|
||
]
|
||
|
||
# 描述性状（单株鉴定表单 6 标签页官方字典，权威来源：育种管理系统操作手册）。
|
||
# 结构：(code, name, category, data_type, options_or_None, mu_or_None, sd_or_None, vmin_or_None, vmax_or_None, reuse_stats_code_or_None)
|
||
# data_type: categorical(分级,选项入 scale_json) / date / numeric
|
||
# reuse_stats_code: 值同源于某 stats 观测（如纵径横径复用 stats fruit_length/fruit_diameter），不独立造值
|
||
# 归并策略：avg_fruit_weight/ssc/flesh_color/fruit_shape 与 stats 撞 code → 描述侧定义覆盖升级（category/data_type/scale_json）；
|
||
# 纵径/横径（longitudinal_dia/transverse_dia）与 stats fruit_length/fruit_diameter 语义重复 → 造值时取同源值。
|
||
DESCRIPTIVE_TRAITS = [
|
||
# ① 基本鉴定
|
||
("growth_vigor", "生长势", "基本鉴定", "categorical", ["强", "中", "弱"], None, None, None, None, None),
|
||
("growth_type", "生长型", "基本鉴定", "categorical", ["普通", "半矮", "矮"], None, None, None, None, None),
|
||
("first_flower_date", "始花日期", "基本鉴定", "date", None, None, None, None, None, None),
|
||
("flower_type", "花型", "基本鉴定", "categorical", ["蔷薇型", "铃型"], None, None, None, None, None),
|
||
("pollen", "花粉", "基本鉴定", "categorical", ["无", "少", "中", "多"], None, None, None, None, None),
|
||
("load_amount", "负载量", "基本鉴定", "categorical", ["高", "中", "低"], None, None, None, None, None),
|
||
("maturity_uniformity", "成熟一致性", "基本鉴定", "categorical", ["一致", "较一致", "不一致"], None, None, None, None, None),
|
||
# ② 果实外观
|
||
("fruit_shape", "果形", "果实外观", "categorical", ["扁平", "扁圆", "圆", "椭圆", "卵圆"], None, None, None, None, None),
|
||
("apex_shape", "果顶", "果实外观", "categorical", ["突出", "稍突出", "圆平", "稍凹陷", "凹陷"], None, None, None, None, None),
|
||
("stem_cavity_depth", "梗洼深度", "果实外观", "categorical", ["浅", "中", "深"], None, None, None, None, None),
|
||
("stem_cavity_width", "梗洼宽度", "果实外观", "categorical", ["窄", "中", "宽"], None, None, None, None, None),
|
||
("fruit_base", "果基", "果实外观", "categorical", ["正", "稍偏", "偏"], None, None, None, None, None),
|
||
("suture_line", "缝合线", "果实外观", "categorical", ["浅", "中", "深"], None, None, None, None, None),
|
||
("symmetry", "对称性", "果实外观", "categorical", ["对称", "较对称", "不对称"], None, None, None, None, None),
|
||
# ③ 果皮
|
||
("pubescence", "茸毛", "果皮", "categorical", ["无", "少", "中", "多"], None, None, None, None, None),
|
||
("skin_base_color", "底色", "果皮", "categorical", ["淡绿", "绿白", "白", "浅黄", "深黄"], None, None, None, None, None),
|
||
("coloration_area", "着色面积", "果皮", "categorical", ["无", "少", "中", "多", "全"], None, None, None, None, None),
|
||
("coloration_depth", "着色深度", "果皮", "categorical", ["浅", "中", "深"], None, None, None, None, None),
|
||
("coloration_brightness", "着色亮度", "果皮", "categorical", ["暗", "中", "亮"], None, None, None, None, None),
|
||
("coloration_pattern", "着色形态", "果皮", "categorical", ["晕", "条", "斑"], None, None, None, None, None),
|
||
("skin_spot", "果面斑点", "果皮", "categorical", ["少", "中", "多"], None, None, None, None, None),
|
||
("skin_peelability", "果皮剥离度", "果皮", "categorical", ["不能", "难", "易"], None, None, None, None, None),
|
||
# ④ 果实大小（数值 core）
|
||
("max_fruit_weight", "最大果重", "果实大小", "numeric", None, 300.0, 70.0, 100, 600, None),
|
||
("avg_fruit_weight", "平均果重", "果实大小", "numeric", None, 210.0, 50.0, 50, 400, None),
|
||
("longitudinal_dia", "纵径", "果实大小", "numeric", None, None, None, None, None, "fruit_length"),
|
||
("transverse_dia", "横径", "果实大小", "numeric", None, None, None, None, None, "fruit_diameter"),
|
||
("lateral_dia", "侧径", "果实大小", "numeric", None, 70.0, 11.0, 40, 115, None),
|
||
# ⑤ 果肉
|
||
("flesh_thickness", "果肉厚度", "果肉", "numeric", None, 18.0, 3.0, 5, 35, None),
|
||
("flesh_color", "肉色", "果肉", "categorical", ["淡绿", "白", "黄白", "黄", "橙黄", "红", "紫红"], None, None, None, None, None),
|
||
("flesh_firmness", "硬度", "果肉", "categorical", ["很软", "软", "中", "硬", "很硬"], None, None, None, None, None),
|
||
("subepidermal_anthocyanin", "皮下花色苷", "果肉", "categorical", ["无", "少", "中", "多"], None, None, None, None, None),
|
||
("flesh_anthocyanin", "果肉花色苷", "果肉", "categorical", ["无", "少", "中", "多"], None, None, None, None, None),
|
||
("stone_cavity_anthocyanin", "核窝花色苷", "果肉", "categorical", ["无", "少", "中", "多"], None, None, None, None, None),
|
||
("ssc", "SSC", "果肉", "numeric", None, 12.5, 2.2, 5, 22, None),
|
||
("fiber", "纤维", "果肉", "categorical", ["少", "中", "多"], None, None, None, None, None),
|
||
("juice", "汁液", "果肉", "categorical", ["少", "中", "多"], None, None, None, None, None),
|
||
("sweet_acidity", "甜酸度", "果肉", "categorical", ["淡甜", "甜", "浓甜", "酸甜", "甜酸适中", "甜酸", "酸"], None, None, None, None, None),
|
||
("aroma", "香气", "果肉", "categorical", ["淡", "中", "多"], None, None, None, None, None),
|
||
("quality_overall", "品质综合", "果肉", "categorical", ["上", "中", "下"], None, None, None, None, None),
|
||
# ⑥ 果核
|
||
("stone_adherence", "粘离性", "果核", "categorical", ["粘核", "半离", "离核"], None, None, None, None, None),
|
||
("stone_size", "核大小", "果核", "categorical", ["小", "中", "大"], None, None, None, None, None),
|
||
("stone_shape", "核形", "果核", "categorical", ["扁平", "近圆", "椭圆", "倒卵圆", "卵圆"], None, None, None, None, None),
|
||
("stone_cracking", "裂核", "果核", "categorical", ["无", "少", "中", "多"], None, None, None, None, None),
|
||
]
|
||
# 描述性状中与 stats 撞 code、由描述侧覆盖升级现有行的 code 集
|
||
DESCRIPTIVE_OVERRIDE_CODES = {"avg_fruit_weight", "ssc", "flesh_color", "fruit_shape"}
|
||
# 描述性状中值同源复用 stats 观测的映射 {描述code: stats code}
|
||
DESCRIPTIVE_REUSE_STATS = {t[0]: t[9] for t in DESCRIPTIVE_TRAITS if t[9]}
|
||
# 描述性状 numeric core 的单位与先验遗传力（抄 bre_trait_seed.sql 官方值）
|
||
DESCRIPTIVE_UNITS = {"max_fruit_weight": "g", "avg_fruit_weight": "g", "longitudinal_dia": "mm",
|
||
"transverse_dia": "mm", "lateral_dia": "mm", "flesh_thickness": "mm", "ssc": "%"}
|
||
DESCRIPTIVE_H2 = {"max_fruit_weight": 0.5, "avg_fruit_weight": 0.5, "longitudinal_dia": 0.4,
|
||
"transverse_dia": 0.4, "lateral_dia": 0.4, "flesh_thickness": 0.3, "ssc": 0.5}
|
||
# 描述 numeric core 的遗传代理（不在 bv 矩阵列中）：{描述code: (stats code, 系数)}，
|
||
# 借同源 stats 性状的 BV 保持遗传相关（最大果重↔平均果重、侧径/肉厚↔横径）
|
||
DESCRIPTIVE_BV_PROXY = {
|
||
"max_fruit_weight": ("avg_fruit_weight", 1.3),
|
||
"lateral_dia": ("fruit_diameter", 0.9),
|
||
"flesh_thickness": ("fruit_diameter", 0.3),
|
||
}
|
||
|
||
# 基础种质池（F1 亲本）：name, variety_type, maturity, firmness, s_alleles, avg_fruit_weight, ssc, chilling
|
||
FOUNDERS = [
|
||
("春美", "普通桃", "早", "硬溶质", "S1/S2", 150, 11.5, 650),
|
||
("春雪", "普通桃", "早", "硬溶质", "S1/S3", 180, 12.0, 700),
|
||
("白凤", "普通桃", "中", "软溶质", "S2/S3", 220, 13.0, 780),
|
||
("湖景蜜露", "普通桃", "中", "软溶质", "S3/S4", 240, 12.5, 800),
|
||
("京玉", "普通桃", "中", "硬溶质", "S1/S4", 260, 12.0, 750),
|
||
("大久保", "普通桃", "中", "软溶质", "S2/S4", 280, 11.0, 820),
|
||
("仓方早生", "普通桃", "早", "硬溶质", "S1/S9", 190, 10.5, 700),
|
||
("冈山白", "普通桃", "中", "软溶质", "S2/S9", 300, 12.0, 850),
|
||
("早香玉", "普通桃", "早", "硬溶质", "S3/S9", 160, 11.0, 680),
|
||
("雨花露", "普通桃", "早", "软溶质", "S1/S7", 170, 10.0, 690),
|
||
("霞晖1号", "普通桃", "早", "硬溶质", "S2/S7", 175, 11.5, 670),
|
||
("砂子早生", "普通桃", "早", "硬溶质", "S3/S7", 165, 10.8, 660),
|
||
("中油桃4号", "油桃", "中", "硬溶质", "S1/S3", 210, 12.8, 760),
|
||
("中油桃8号", "油桃", "中", "硬溶质", "S2/S4", 235, 13.5, 780),
|
||
("瑞光18", "油桃", "中", "硬溶质", "S1/S4", 220, 12.2, 770),
|
||
("瑞光28", "油桃", "晚", "硬溶质", "S3/S9", 245, 13.0, 820),
|
||
("瑞蟠4号", "蟠桃", "中", "软溶质", "S2/S3", 210, 12.5, 800),
|
||
("瑞蟠21", "蟠桃", "中", "硬溶质", "S1/S2", 230, 12.8, 790),
|
||
("晚白桃", "普通桃", "晚", "硬溶质", "S7/S9", 270, 12.5, 900),
|
||
("京红", "普通桃", "中", "软溶质", "S1/S9", 250, 11.8, 790),
|
||
("燕红", "普通桃", "中", "硬溶质", "S2/S9", 265, 12.2, 810),
|
||
("新川中岛", "普通桃", "中", "硬溶质", "S3/S4", 280, 13.2, 830),
|
||
("川中岛白桃", "普通桃", "晚", "硬溶质", "S1/S7", 290, 13.5, 860),
|
||
("锦绣黄桃", "黄肉", "中", "硬溶质", "S2/S7", 260, 14.0, 800),
|
||
("锦园", "黄肉", "中", "硬溶质", "S1/S4", 275, 13.8, 820),
|
||
("金童5号", "黄肉", "中", "硬溶质", "S3/S7", 240, 13.0, 790),
|
||
("金童8号", "黄肉", "中", "硬溶质", "S2/S9", 250, 13.2, 800),
|
||
("早黄金", "黄肉", "早", "硬溶质", "S1/S3", 200, 12.8, 720),
|
||
("中蟠桃1号", "蟠桃", "中", "硬溶质", "S2/S4", 225, 12.0, 790),
|
||
("中蟠桃11", "蟠桃", "晚", "硬溶质", "S1/S9", 235, 12.6, 830),
|
||
("中油蟠1号", "油蟠桃", "中", "硬溶质", "S3/S4", 230, 12.8, 810),
|
||
("中华寿桃", "普通桃", "晚", "硬溶质", "S2/S9", 320, 13.0, 950),
|
||
("大五星", "普通桃", "中", "硬溶质", "S1/S4", 300, 12.2, 840),
|
||
("布目早生", "普通桃", "早", "软溶质", "S3/S9", 195, 10.8, 710),
|
||
("冬桃", "普通桃", "晚", "硬溶质", "S7/S9", 310, 13.5, 1100),
|
||
("血桃", "普通桃", "中", "硬溶质", "S1/S7", 200, 11.0, 780),
|
||
("脆桃", "普通桃", "早", "硬溶质", "S2/S3", 175, 10.5, 690),
|
||
("黄肉晚熟", "黄肉", "晚", "硬溶质", "S1/S2", 280, 14.2, 950),
|
||
("白桃(地方)", "普通桃", "中", "软溶质", "S2/S7", 260, 11.5, 850),
|
||
("油桃晚熟", "油桃", "晚", "硬溶质", "S3/S7", 255, 13.8, 880),
|
||
]
|
||
|
||
PERSONNEL = [
|
||
("王育桃", "男", "育种家", "主持杂交组合与选择晋级"),
|
||
("李农艺", "女", "农艺师", "负责定植与田间评价"),
|
||
("张田间", "男", "技术员", "负责农事操作与采收记录"),
|
||
]
|
||
SITES = [("主育种基地", "河南郑州", "华北", 35.6, 113.6, 150, "核心育种圃与品比圃"),
|
||
("试验示范站", "山东泰安", "华北", 36.2, 117.1, 250, "区试与示范栽培点")]
|
||
PLOTS = [(0, "P01", "南北行", "选种圃"),
|
||
(0, "P02", "南北行", "选种圃"),
|
||
(0, "P03", "南北行", "复选圃"),
|
||
(0, "P04", "东西行", "品比圃"),
|
||
(0, "P05", "东西行", "育种圃"),
|
||
(0, "P06", "南北行", "杂种圃"),
|
||
(1, "T01", "南北行", "区试圃"),
|
||
(1, "T02", "东西行", "区试圃"),
|
||
(1, "T03", "南北行", "区试圃")]
|
||
PLANS = [("桃-2020-1", "桃树品种选育计划（2020-2030）", "早熟优质大果硬肉耐贮综合育种")]
|
||
TARGETS = [("早熟优质", "早熟", "早熟、优质、丰产，树势开张"),
|
||
("大果硬肉", "硬肉", "大果型、硬溶质、耐贮运"),
|
||
("抗病耐贮", "抗病", "抗细菌性穿孔病、耐贮运"),
|
||
("低需冷量", "冷量", "低需冷量、适南岭以南促早栽培")]
|
||
RULES = [("初选", "sp", "综合评价得分与指数筛选"),
|
||
("复选", "ap", "连续两年结果表现复核"),
|
||
("品系比较", "line", "品系比较试验表现"),
|
||
("区域试验", "regional_trial", "区域试验产量与品质表现")]
|
||
ROOTSTOCKS = [("毛桃", "乔化", "强"), ("山桃", "乔化", "中"), ("GF677", "半矮化", "强")]
|
||
|
||
|
||
def _now() -> datetime:
|
||
return datetime.now(UTC)
|
||
|
||
|
||
def _base() -> dict:
|
||
return {"uuid": uuid4_str(), "created_time": _now(), "updated_time": _now(), "is_deleted": False}
|
||
|
||
|
||
# ---------------------------------------------------------------------------
|
||
# 工具
|
||
# ---------------------------------------------------------------------------
|
||
def chunked(seq: list, size: int):
|
||
for i in range(0, len(seq), size):
|
||
yield seq[i:i + size]
|
||
|
||
|
||
def clip_round(v, vmin: float, vmax: float):
|
||
return float(np.clip(np.round(v, 1), vmin, vmax))
|
||
|
||
|
||
async def insert_chunked(session, model, rows: list[dict], size: int = 20000) -> int:
|
||
total = 0
|
||
for chunk in chunked(rows, size):
|
||
await session.execute(insert(model), chunk)
|
||
total += len(chunk)
|
||
return total
|
||
|
||
|
||
# ---------------------------------------------------------------------------
|
||
# 参考数据补齐
|
||
# ---------------------------------------------------------------------------
|
||
# 官方字典种子（产量/品质/物候/生长量/描述 + 抗病/需冷量 + DUS TG/53 描述符）以
|
||
# backend/sql/*.sql 为单一事实源。模拟脚本只为其自有性状造观测，不建这些字典行；
|
||
# wipe 重建若不同步回填，回归断言（e2e_busflow 19 码 / e2e_refine DUS）必缺。
|
||
# 幂等：各文件均为单个 INSERT ... ON CONFLICT（DO UPDATE / DO NOTHING）。
|
||
_REFERENCE_SQL_SEEDS = [
|
||
"bre_yield_phenology_traits.sql",
|
||
"bre_disease_chilling_traits.sql",
|
||
"bre_dus_seed.sql",
|
||
]
|
||
|
||
|
||
async def _apply_reference_sql_seeds(session) -> None:
|
||
sql_dir = Path(__file__).resolve().parent.parent / "sql"
|
||
for fname in _REFERENCE_SQL_SEEDS:
|
||
content = (sql_dir / fname).read_text(encoding="utf-8")
|
||
for stmt in content.split(";"):
|
||
body = "\n".join(ln for ln in stmt.splitlines()
|
||
if not ln.strip().startswith("--")).strip()
|
||
if body:
|
||
await session.execute(text(body))
|
||
await session.commit()
|
||
|
||
|
||
async def ensure_reference(session) -> dict:
|
||
"""补齐参考数据（缺失才插入），返回供后续使用的 id 映射。"""
|
||
ref = {}
|
||
|
||
# 性状（trait_code 幂等）
|
||
existing_codes = {r[0] for r in await session.execute(select(TraitModel.trait_code))}
|
||
trait_upserts = []
|
||
for code, name, unit, cat, vmin, vmax, stage, direction, into_ebv, *_ in NUMERIC_TRAITS:
|
||
if code not in existing_codes:
|
||
trait_upserts.append({"trait_code": code, "trait_name": name, "category": cat, "data_type": "numeric",
|
||
"unit": unit, "is_core": "1", "valid_min": vmin, "valid_max": vmax,
|
||
"stage": stage, "direction": direction, "into_ebv": into_ebv,
|
||
"default_h2": next(t[11] for t in NUMERIC_TRAITS if t[0] == code)})
|
||
for code, name, cat, stage, options, probs in TEXT_TRAITS:
|
||
if code not in existing_codes:
|
||
trait_upserts.append({"trait_code": code, "trait_name": name, "category": cat, "data_type": "text",
|
||
"unit": None, "is_core": "1", "valid_min": None, "valid_max": None,
|
||
"stage": stage, "direction": "desc", "into_ebv": "0",
|
||
"scale_json": json.dumps(list(zip(options, probs)), ensure_ascii=False),
|
||
"default_h2": 0.2})
|
||
if trait_upserts:
|
||
await session.execute(insert(TraitModel), [{**_base(), **r} for r in trait_upserts])
|
||
await session.commit()
|
||
# 描述性状（43 条官方字典）：撞 code 的覆盖升级（category/data_type/scale_json 对齐描述侧），
|
||
# 其余幂等插入。desc 覆盖不建重复行（avg_fruit_weight/ssc/flesh_color/fruit_shape 与 stats 同归口）。
|
||
desc_upserts = []
|
||
for code, name, cat, dtype, options, mu, sd, vmin, vmax, _reuse in DESCRIPTIVE_TRAITS:
|
||
if dtype == "numeric":
|
||
desc_upserts.append({"trait_code": code, "trait_name": name, "category": cat, "data_type": "numeric",
|
||
"unit": DESCRIPTIVE_UNITS.get(code), "is_core": "1",
|
||
"valid_min": vmin, "valid_max": vmax, "stage": "evaluation",
|
||
"direction": "desc", "into_ebv": "1",
|
||
"scale_json": None, "default_h2": DESCRIPTIVE_H2.get(code)})
|
||
elif dtype == "categorical":
|
||
desc_upserts.append({"trait_code": code, "trait_name": name, "category": cat, "data_type": "categorical",
|
||
"unit": None, "is_core": "0", "valid_min": None, "valid_max": None,
|
||
"stage": "evaluation", "direction": "desc", "into_ebv": "0",
|
||
"scale_json": json.dumps(options, ensure_ascii=False), "default_h2": None})
|
||
else: # date
|
||
desc_upserts.append({"trait_code": code, "trait_name": name, "category": cat, "data_type": "date",
|
||
"unit": None, "is_core": "0", "valid_min": None, "valid_max": None,
|
||
"stage": "evaluation", "direction": "desc", "into_ebv": "0",
|
||
"scale_json": None, "default_h2": None})
|
||
for r in desc_upserts:
|
||
await session.execute(
|
||
pg_insert(TraitModel)
|
||
.values({**_base(), **r})
|
||
.on_conflict_do_update(
|
||
index_elements=["trait_code"],
|
||
# is_core 不入 set_：撞 code 的 stats 行（flesh_color/fruit_shape 等）保留原 is_core，
|
||
# 仅对齐 category/data_type/scale_json 等描述侧定义
|
||
set_={"trait_name": r["trait_name"], "category": r["category"], "data_type": r["data_type"],
|
||
"unit": r["unit"], "valid_min": r["valid_min"],
|
||
"valid_max": r["valid_max"], "scale_json": r["scale_json"],
|
||
"direction": r["direction"], "into_ebv": r["into_ebv"], "default_h2": r["default_h2"],
|
||
"updated_time": func.now()}))
|
||
await session.commit()
|
||
await _apply_reference_sql_seeds(session)
|
||
rows = await session.execute(select(TraitModel.id, TraitModel.trait_code))
|
||
ref["trait_id_by_code"] = {code: tid for tid, code in rows.all()}
|
||
|
||
# 人员
|
||
if (await session.scalar(select(func.count(PersonnelModel.id))) or 0) == 0:
|
||
await session.execute(insert(PersonnelModel), [
|
||
{**_base(), "name": n, "gender": g, "role": r, "remark": rm}
|
||
for n, g, r, rm in PERSONNEL
|
||
])
|
||
await session.commit()
|
||
ref["personnel_ids"] = [r[0] for r in await session.execute(
|
||
select(PersonnelModel.id).where(PersonnelModel.is_deleted == False))] # noqa: E712
|
||
|
||
# 基地/地块
|
||
if (await session.scalar(select(func.count(BreedingSiteModel.id))) or 0) == 0:
|
||
await session.execute(insert(BreedingSiteModel), [
|
||
{**_base(), "site_name": n, "address": a, "eco_type": e, "latitude": la, "longitude": lo,
|
||
"elevation": el, "remark": rm}
|
||
for n, a, e, la, lo, el, rm in SITES
|
||
])
|
||
await session.commit()
|
||
site_ids = [r[0] for r in await session.execute(select(BreedingSiteModel.id).order_by(BreedingSiteModel.id))]
|
||
if (await session.scalar(select(func.count(BreedingPlotModel.id))) or 0) == 0:
|
||
await session.execute(insert(BreedingPlotModel), [
|
||
{**_base(), "site_id": site_ids[si], "plot_code": code, "row_orientation": ro, "grid_note": note,
|
||
"row_count": 40, "col_count": 30, "area": 5.0}
|
||
for si, code, ro, note in PLOTS
|
||
])
|
||
await session.commit()
|
||
plot_rows = await session.execute(
|
||
select(BreedingPlotModel.id, BreedingPlotModel.site_id).order_by(BreedingPlotModel.id)
|
||
.where(BreedingPlotModel.is_deleted == False)) # noqa: E712
|
||
ref["plots"] = [{"id": pid, "site_id": sid} for pid, sid in plot_rows.all()]
|
||
|
||
# 计划/目标/规则
|
||
if (await session.scalar(select(func.count(PlanModel.id))) or 0) == 0:
|
||
await session.execute(insert(PlanModel), [
|
||
{**_base(), "plan_code": c, "plan_name": n, "objective": o}
|
||
for c, n, o in PLANS
|
||
])
|
||
await session.commit()
|
||
plan_ids = [r[0] for r in await session.execute(
|
||
select(PlanModel.id).where(PlanModel.is_deleted == False))] # noqa: E712
|
||
ref["plan_ids"] = plan_ids
|
||
if (await session.scalar(select(func.count(TargetModel.id))) or 0) == 0:
|
||
await session.execute(insert(TargetModel), [
|
||
{**_base(), "target_name": n, "series": s, "description": d, "is_preset": "1",
|
||
"plan_id": plan_ids[0] if plan_ids else None}
|
||
for n, s, d in TARGETS
|
||
])
|
||
await session.commit()
|
||
target_rows = await session.execute(
|
||
select(TargetModel.id).order_by(TargetModel.id).where(TargetModel.is_deleted == False)) # noqa: E712
|
||
ref["target_ids"] = [r[0] for r in target_rows.all()]
|
||
if (await session.scalar(select(func.count(SelectionRuleModel.id))) or 0) == 0:
|
||
target0 = ref["target_ids"][0] if ref["target_ids"] else None
|
||
await session.execute(insert(SelectionRuleModel), [
|
||
{**_base(), "rule_name": n, "stage": st, "target_id": target0, "conditions_json": "[]",
|
||
"logic": "and", "action": "promote", "priority": 1, "enabled": "1", "remark": rm}
|
||
for n, st, rm in RULES
|
||
])
|
||
await session.commit()
|
||
rule_rows = await session.execute(
|
||
select(SelectionRuleModel.id).order_by(SelectionRuleModel.id)
|
||
.where(SelectionRuleModel.is_deleted == False)) # noqa: E712
|
||
ref["rule_ids"] = [r[0] for r in rule_rows.all()]
|
||
|
||
# 砧木
|
||
if (await session.scalar(select(func.count(RootstockModel.id))) or 0) == 0:
|
||
await session.execute(insert(RootstockModel), [
|
||
{**_base(), "rootstock_name": n, "dwarf_class": d, "compatibility": c}
|
||
for n, d, c in ROOTSTOCKS
|
||
])
|
||
await session.commit()
|
||
root_rows = await session.execute(
|
||
select(RootstockModel.id).order_by(RootstockModel.id)
|
||
.where(RootstockModel.is_deleted == False)) # noqa: E712
|
||
ref["rootstock_ids"] = [r[0] for r in root_rows.all()]
|
||
|
||
# 基础种质池（按名称幂等补齐）
|
||
germ_rows = await session.execute(select(BreedingGermplasmModel.id, BreedingGermplasmModel.cultivar_name))
|
||
germ_by_name = {name: gid for gid, name in germ_rows.all()}
|
||
founder_missing = [FOUNDERS[i] for i, (name, *_rest) in enumerate(FOUNDERS) if name not in germ_by_name]
|
||
if founder_missing:
|
||
await session.execute(insert(BreedingGermplasmModel), [
|
||
{**_base(), "cultivar_name": n, "variety_type": vt, "maturity_period": m, "firmness": f,
|
||
"s_alleles": s, "avg_fruit_weight": w, "ssc": ssc, "chilling_requirement": c,
|
||
"can_be_female": True, "can_be_male": True, "stage": "germplasm", "generation": "F0",
|
||
"storage_type": "田间", "is_rootstock": False, "biological_status": "landrace",
|
||
"breeding_program": "桃育种项目"}
|
||
for n, vt, m, f, s, w, ssc, c in founder_missing
|
||
])
|
||
await session.commit()
|
||
germ_rows = await session.execute(select(BreedingGermplasmModel.id, BreedingGermplasmModel.cultivar_name))
|
||
germ_by_name = {name: gid for gid, name in germ_rows.all()}
|
||
ref["founder_ids"] = [germ_by_name[f[0]] for f in FOUNDERS]
|
||
return ref
|
||
|
||
|
||
# ---------------------------------------------------------------------------
|
||
# 清表
|
||
# ---------------------------------------------------------------------------
|
||
async def wipe_business(session) -> None:
|
||
"""清空全部 bre_* 业务表（含参考数据与历史测试残留），保留字典(sys_dict_*)。
|
||
|
||
动态取当前库中所有 bre_ 前缀表，TRUNCATE ... RESTART IDENTITY CASCADE 一次清掉
|
||
整个外键依赖闭包（已确认无非 bre_ 表外键指向 bre_ 表），确保最终库中
|
||
只保留字典 + 本脚本重新生成的数据。
|
||
"""
|
||
tbls = [r[0] for r in await session.execute(text(
|
||
"SELECT tablename FROM pg_tables WHERE schemaname='public' AND tablename LIKE 'bre_%'"))]
|
||
if tbls:
|
||
await session.execute(text(f"TRUNCATE TABLE {', '.join(tbls)} RESTART IDENTITY CASCADE"))
|
||
await session.commit()
|
||
|
||
|
||
# ---------------------------------------------------------------------------
|
||
# 遗传模拟与选择
|
||
# ---------------------------------------------------------------------------
|
||
def build_gen_params() -> tuple[list[str], dict, np.ndarray, np.ndarray]:
|
||
"""返回 (trait_codes, param_map, sigmaA, w)。"""
|
||
codes = [t[0] for t in NUMERIC_TRAITS]
|
||
params = {}
|
||
sigmaA = np.zeros(len(codes))
|
||
w = np.zeros(len(codes))
|
||
for i, t in enumerate(NUMERIC_TRAITS):
|
||
code, _name, _unit, _cat, vmin, vmax, _stage, direction, into_ebv, mu, sd, h2, weight = t
|
||
params[code] = {"mu": mu, "sd": sd, "h2": h2, "vmin": vmin, "vmax": vmax,
|
||
"direction": direction, "into_ebv": into_ebv, "stage": _stage, "idx": i}
|
||
sigmaA[i] = sd * np.sqrt(h2)
|
||
w[i] = weight
|
||
wsum = np.abs(w).sum()
|
||
if wsum > 0:
|
||
w = w / wsum
|
||
return codes, params, sigmaA, w
|
||
|
||
|
||
def idx_score(bv: np.ndarray, sd: np.ndarray, w: np.ndarray) -> np.ndarray:
|
||
"""加权标准化 BV 指数（高 = 优）。"""
|
||
return (bv / sd) @ w
|
||
|
||
|
||
def sample_bv_parents(rng: np.random.Generator, dam_bv: np.ndarray, sire_bv: np.ndarray, sigmaA: np.ndarray) -> np.ndarray:
|
||
"""后代 BV = 中亲 + 孟德尔抽样（sd = sigmaA/sqrt(2)）。"""
|
||
return 0.5 * (dam_bv + sire_bv) + rng.normal(0.0, 1.0, size=sigmaA.shape) * (sigmaA * 0.7071)
|
||
|
||
|
||
def select_pool(pool: np.ndarray, idx: np.ndarray, frac: float, rng: np.random.Generator) -> tuple[np.ndarray, np.ndarray]:
|
||
"""在候选池内按指数取前 frac（含小随机扰动保持多样性），返回 (选中, 淘汰)。"""
|
||
n = len(pool)
|
||
k = max(1, int(round(n * frac)))
|
||
if k >= n:
|
||
return pool, np.array([], dtype=int)
|
||
jitter = rng.normal(0.0, 0.05, size=n)
|
||
rank = np.argsort(-(idx[pool] + jitter))
|
||
return pool[rank[:k]], pool[rank[k:]]
|
||
|
||
|
||
async def apply_elim_status(session) -> None:
|
||
"""正向晋级全部完成后，将存在淘汰记录的树置为终止态（只进不退，安全）。"""
|
||
await session.execute(text(
|
||
"UPDATE bre_tree t SET status='eliminated' "
|
||
"WHERE EXISTS (SELECT 1 FROM bre_selection_result s "
|
||
"WHERE s.tree_id = t.id AND s.is_deleted = false AND s.is_selected = 'eliminated')"))
|
||
|
||
|
||
# ---------------------------------------------------------------------------
|
||
# 主流程
|
||
# ---------------------------------------------------------------------------
|
||
async def main() -> None:
|
||
parser = argparse.ArgumentParser(description="桃育种压测级模拟数据生成器")
|
||
parser.add_argument("--dry-run", action="store_true", help="小规模验证（精简组合/株数）")
|
||
parser.add_argument("--seed", type=int, default=42, help="随机种子")
|
||
parser.add_argument("--no-wipe", action="store_true", help="不清空既有业务数据")
|
||
parser.add_argument("--verify", action="store_true", help="生成后运行自检")
|
||
parser.add_argument("--batch-size", type=int, default=50, help="批量晋级每批单株数")
|
||
parser.add_argument("--end-year", type=int, default=WINDOW_END, help="数据终止年份")
|
||
args = parser.parse_args()
|
||
|
||
if settings.DATABASE_TYPE != "postgres":
|
||
raise SystemExit("本脚本仅支持 Postgres（dev 环境）。请以 ENVIRONMENT=dev 运行。")
|
||
|
||
dry = args.dry_run
|
||
f1_years = list(F1_YEARS) if not dry else [2007, 2008, 2009, 2010]
|
||
f1_per_year = 3 if dry else 12
|
||
f1_trees = 24 if dry else F1_TREES
|
||
f2_combos = 3 if dry else F2_COMBOS
|
||
f2_trees = 16 if dry else F2_TREES
|
||
bc_combos = 2 if dry else BC_COMBOS
|
||
bc_trees = 12 if dry else BC_TREES
|
||
end_year = args.end_year
|
||
|
||
rng = np.random.default_rng(args.seed)
|
||
pyrand = random.Random(args.seed)
|
||
codes, params, sigmaA, w = build_gen_params()
|
||
nT = len(codes)
|
||
sd = np.array([params[c]["sd"] for c in codes])
|
||
|
||
engine = create_async_engine(settings.ASYNC_DB_URI, pool_size=8, max_overflow=12, pool_pre_ping=True)
|
||
Session = async_sessionmaker(engine, expire_on_commit=False)
|
||
auth = AuthSchema(user=CoreUserSchema(id=1, username="admin", name="管理员", is_superuser=True))
|
||
t0 = datetime.now(UTC)
|
||
counts = {"trees": 0, "evals": 0, "obs": 0, "field_ops": 0, "selections": 0,
|
||
"combos": 0, "plantings": 0, "clones": 0, "germplasm": 0, "pedigree": 0,
|
||
"trials": 0, "studies": 0, "entries": 0,
|
||
"pollination": 0, "pollen": 0, "seed_lots": 0, "seed_treatments": 0, "seedlings": 0,
|
||
"propagations": 0, "env_conds": 0, "observations": 0, "treatments": 0}
|
||
print(f"[sim] ENVIRONMENT={settings.ENVIRONMENT} DB={settings.DATABASE_TYPE}@{settings.DATABASE_NAME} dry_run={dry}")
|
||
|
||
async with Session() as session:
|
||
if not args.no_wipe:
|
||
print("[sim] 1/8 清空全部 bre_* 业务表（字典保留）...")
|
||
await wipe_business(session)
|
||
print("[sim] 2/8 补齐参考数据（性状/人员/基地/地块/目标/规则/砧木/基础种质池）...")
|
||
ref = await ensure_reference(session)
|
||
trait_id = ref["trait_id_by_code"]
|
||
personnel_ids = ref["personnel_ids"]
|
||
plots = ref["plots"]
|
||
founder_ids = ref["founder_ids"]
|
||
nf = len(founder_ids)
|
||
rule_ids = ref["rule_ids"]
|
||
rootstock_ids = ref["rootstock_ids"]
|
||
target_ids = ref["target_ids"]
|
||
plan_ids = ref["plan_ids"]
|
||
|
||
# 基础种质池遗传值（标准正态 × 加性标准差）
|
||
founder_bv = rng.normal(0.0, 1.0, (nf, nT)) * sigmaA[None, :]
|
||
|
||
combo_info: list[dict] = [] # 每个组合的生成信息（供 F2/BC 亲本 BV、系谱/区试映射）
|
||
promo_queue: list[dict] = [] # 正向晋级队列（按组合内轮次顺序，最终走服务层）
|
||
elim_accum: list[dict] = [] # 淘汰记录（全部晋级完成后统一批量回写）
|
||
|
||
# ---------- F1 ----------
|
||
f1_combos = [(y, seq) for y in f1_years for seq in range(1, f1_per_year + 1)]
|
||
print(f"[sim] 3/8 生成 F1 杂交组合 {len(f1_years)} 年度 × {f1_per_year} 组合/年 = {len(f1_combos)} 个 → 单株 ...")
|
||
combo_rows = []
|
||
for y, seq in f1_combos:
|
||
dam_i = (y * 7 + seq * 3) % nf
|
||
sire_i = (dam_i + 1 + seq) % nf
|
||
while sire_i == dam_i:
|
||
sire_i = (sire_i + 1) % nf
|
||
combo_rows.append({
|
||
"combination_code": f"桃{y}-{seq:03d}", "cross_year": y,
|
||
"bre_target_id": target_ids[(y + seq) % len(target_ids)],
|
||
"female_parent_id": founder_ids[dam_i], "male_parent_id": founder_ids[sire_i],
|
||
"cross_method": "人工授粉", "cross_type": "杂交", "design_type": "full_diallel",
|
||
"stage": "seedling", "cross_date": f"{y}-04-{10 + seq % 10:02d}",
|
||
"seed_count": pyrand.randint(300, 800),
|
||
"reason": "目标亲本组配", "remark": "F1 杂种实生苗圃",
|
||
})
|
||
res = await session.execute(
|
||
insert(CrossCombinationModel).values([{**_base(), **r} for r in combo_rows]).returning(CrossCombinationModel.id))
|
||
f1_combo_ids = list(res.scalars())
|
||
counts["combos"] += len(f1_combo_ids)
|
||
|
||
combo_defs = []
|
||
for (y, seq), cid in zip(f1_combos, f1_combo_ids):
|
||
code = f"桃{y}-{seq:03d}"
|
||
dam_i = (y * 7 + seq * 3) % nf
|
||
sire_i = (dam_i + 1 + seq) % nf
|
||
while sire_i == dam_i:
|
||
sire_i = (sire_i + 1) % nf
|
||
combo_defs.append({"code": code, "id": cid, "generation": "F1",
|
||
"dam_i": dam_i, "sire_i": sire_i,
|
||
"dam_id": founder_ids[dam_i], "sire_id": founder_ids[sire_i],
|
||
"planted": y + 2, "cross_year": y, "n": f1_trees})
|
||
|
||
for c in combo_defs:
|
||
await _gen_combo(session, counts, combo_info, promo_queue, elim_accum, c,
|
||
founder_bv, params, sigmaA, sd, w, codes, rng, pyrand,
|
||
personnel_ids, plots, rule_ids, rootstock_ids, end_year, trait_id)
|
||
await session.commit()
|
||
print(f"[sim] 累计: 组合={counts['combos']} 单株={counts['trees']} 评价={counts['evals']} "
|
||
f"观测={counts['obs']} 农事={counts['field_ops']} 淘汰={len(elim_accum)}")
|
||
|
||
# ---------- F1 选择晋级（真实服务层 create_batch） ----------
|
||
print("[sim] 4/8 运行 F1 选择晋级（服务层批量，sp/ap/line/regional/released）...")
|
||
ok, fail = await run_promotions(session, auth, promo_queue, args.batch_size)
|
||
counts["selections"] += ok
|
||
promo_queue.clear()
|
||
print(f"[sim] F1 晋级成功 {ok} / 失败 {fail}")
|
||
|
||
# 收集 line 及以上种质（id / BV），供 F2/BC 亲本与区试
|
||
line_germ = await collect_line_germplasms(session, combo_info)
|
||
print(f"[sim] line 及以上种质 {len(line_germ)} 个")
|
||
|
||
# ---------- F2 / BC ----------
|
||
print("[sim] 5/8 生成 F2 自交 / BC1 回交组合与单株 ...")
|
||
d2c = await _gen_derived_combos(
|
||
session, counts, combo_info, promo_queue, elim_accum, line_germ, founder_bv, founder_ids,
|
||
target_ids, f2_combos, f2_trees, bc_combos, bc_trees,
|
||
params, sigmaA, sd, w, codes, rng, pyrand, personnel_ids, plots,
|
||
rule_ids, rootstock_ids, end_year, trait_id)
|
||
await session.commit()
|
||
print(f"[sim] 派生组合 {d2c} 个，累计单株 {counts['trees']} 观测 {counts['obs']}")
|
||
|
||
print("[sim] 6/8 运行 F2/BC 选择晋级 ...")
|
||
ok2, fail2 = await run_promotions(session, auth, promo_queue, args.batch_size)
|
||
counts["selections"] += ok2
|
||
promo_queue.clear()
|
||
print(f"[sim] F2/BC 晋级成功 {ok2} / 失败 {fail2}")
|
||
|
||
# 晋级全部完成后：批量回写淘汰记录 + 淘汰树状态
|
||
if elim_accum:
|
||
await insert_chunked(session, SelectionResultModel, [{**_base(), **r} for r in elim_accum])
|
||
counts["selections"] += len(elim_accum)
|
||
await apply_elim_status(session)
|
||
await session.commit()
|
||
print(f"[sim] 淘汰记录 {len(elim_accum)} 条已回写，淘汰树状态已置位")
|
||
|
||
# ---------- 区域试验 ----------
|
||
print("[sim] 7/8 生成区域试验（trial / study / entry）...")
|
||
await gen_trials(session, counts, combo_info, plots, target_ids, plan_ids)
|
||
await session.commit()
|
||
|
||
# ---------- 业务流空表补齐 ----------
|
||
print("[sim] 8/8 补齐业务流空表（授粉/花粉/种子批/种子处理/育苗/克隆扩繁/环境气象/通用观测/试验处理）...")
|
||
await gen_support_data(session, counts, combo_info, personnel_ids, plots,
|
||
rootstock_ids, trait_id, params, codes, end_year, pyrand, rng)
|
||
await session.commit()
|
||
|
||
# ---------- 自检 ----------
|
||
elapsed = (datetime.now(UTC) - t0).total_seconds()
|
||
print(f"[sim] 生成完成，耗时 {elapsed:.1f}s。")
|
||
await report_counts(session, counts)
|
||
if args.verify:
|
||
await verify(session, counts, dry)
|
||
|
||
await engine.dispose()
|
||
|
||
|
||
# ---------------------------------------------------------------------------
|
||
# 单个组合生成（组合 → 定植 → 单株 → 选择排程 → 淘汰记录/评价/观测/农事 + 晋级入队）
|
||
# ---------------------------------------------------------------------------
|
||
async def _gen_combo(session, counts, combo_info, promo_queue, elim_accum, c,
|
||
founder_bv, params, sigmaA, sd, w, codes, rng, pyrand,
|
||
personnel_ids, plots, rule_ids, rootstock_ids, end_year, trait_id) -> None:
|
||
code = c["code"]
|
||
cid = c["id"]
|
||
n = c["n"]
|
||
planted = c["planted"]
|
||
generation = c["generation"]
|
||
|
||
# 父本 BV
|
||
if generation == "F1":
|
||
dam_bv, sire_bv = founder_bv[c["dam_i"]], founder_bv[c["sire_i"]]
|
||
else:
|
||
dam_bv, sire_bv = c["dam_bv"], c["sire_bv"]
|
||
|
||
# 单株 BV 与指数
|
||
bv = np.zeros((n, len(codes)))
|
||
for i in range(n):
|
||
bv[i] = sample_bv_parents(rng, dam_bv, sire_bv, sigmaA)
|
||
idx = idx_score(bv, sd, w)
|
||
|
||
plot = plots[c["cross_year"] % len(plots)]
|
||
root = rootstock_ids[(c["cross_year"] * 3) % len(rootstock_ids)]
|
||
persona = personnel_ids[c["cross_year"] % len(personnel_ids)]
|
||
|
||
# 定植批次
|
||
plant_row = {**_base(),
|
||
"combination_id": cid, "plot_id": plot["id"], "planting_date": f"{planted}-03-15",
|
||
"tree_count": n, "row_no": 1, "col_no": 1, "bre_personnel_id": persona,
|
||
"rootstock_id": root, "block_no": 1, "remark": f"{code} 定植"}
|
||
pres = await session.execute(insert(PlantingModel).values([plant_row]).returning(PlantingModel.id))
|
||
planting_id = list(pres.scalars())[0]
|
||
counts["plantings"] += 1
|
||
|
||
# 单株
|
||
tree_rows = [{
|
||
"combination_id": cid, "dam_id": c["dam_id"], "sire_id": c["sire_id"],
|
||
"tree_no": f"{code}-{i + 1:03d}", "plot_id": plot["id"], "planting_id": planting_id,
|
||
"row_no": i // 20 + 1, "col_no": i % 20 + 1, "rootstock_id": root,
|
||
"planted_date": f"{planted}-03-15", "bre_personnel_id": persona,
|
||
"status": "alive", "stage": "seedling", "generation": generation,
|
||
"remark": f"{code} 实生苗",
|
||
} for i in range(n)]
|
||
res = await session.execute(insert(TreeModel).values([{**_base(), **r} for r in tree_rows]).returning(TreeModel.id))
|
||
tree_ids = list(res.scalars())
|
||
counts["trees"] += n
|
||
|
||
info = {"code": code, "combo_id": cid, "generation": generation, "planted": planted,
|
||
"cross_year": c["cross_year"], "n": n,
|
||
"tree_ids": tree_ids, "bv": bv, "rounds": {}}
|
||
combo_info.append(info)
|
||
|
||
# 童期观测（定植年 ~ sp 前一年 / 窗口末）
|
||
sp_year = planted + 3
|
||
await gen_juvenile_obs(session, counts, cid, tree_ids, bv, trait_id, params, sd, w,
|
||
planted, min(sp_year - 1, end_year), rng, code)
|
||
|
||
# 选择排程
|
||
alive_pool = np.arange(n)
|
||
current_sel = None
|
||
for ri, (rname, is_selected, frac, from_stage, to_stage, off) in enumerate(ROUNDS):
|
||
ryear = planted + off
|
||
pool = alive_pool if rname == "sp" else current_sel
|
||
if pool is None or ryear > end_year:
|
||
info["rounds"][rname] = {"year": ryear, "active": False}
|
||
continue
|
||
sel, elim = select_pool(pool, idx, frac, rng)
|
||
# 淘汰记录（暂存，晋级全部完成后统一回写）
|
||
for ei in elim:
|
||
elim_accum.append({
|
||
"combination_id": cid, "tree_id": tree_ids[ei], "tree_no": f"{code}-{ei + 1:03d}",
|
||
"selection_year": ryear, "is_selected": "eliminated",
|
||
"from_stage": from_stage, "to_stage": from_stage,
|
||
"reason": f"{rname} 轮未达入选标准", "remark": "模拟数据"})
|
||
info["rounds"][rname] = {"year": ryear, "active": True, "sel": sel, "elim": elim}
|
||
if rname == "sp":
|
||
info["sp_order"] = [int(i) for i in sel] # 晋级 clone 序由此决定
|
||
# 入队正向晋级（选中树）
|
||
if len(sel):
|
||
promo_queue.append({
|
||
"combination_id": cid, "selection_year": ryear, "is_selected": is_selected,
|
||
"from_stage": from_stage, "to_stage": to_stage,
|
||
"rule_id": rule_ids[min(ri, len(rule_ids) - 1)] if rule_ids else None,
|
||
"approved_by": personnel_ids[0],
|
||
"reason": f"{rname} 轮入选（指数前 {len(sel)} 名）", "remark": "模拟数据",
|
||
"tree_ids": [tree_ids[int(i)] for i in sel],
|
||
})
|
||
current_sel = sel
|
||
|
||
# 成株评价与观测：各轮选中树，自 sp_year 至淘汰年前一年/窗口末
|
||
await gen_eval_obs(session, counts, info, cid, tree_ids, bv, trait_id, params, sd, w,
|
||
sp_year, end_year, rng, personnel_ids, plot, code, pyrand)
|
||
|
||
# 小区级农事（每年）
|
||
await gen_plot_ops(session, counts, plot["id"], planted, end_year, personnel_ids, pyrand, code)
|
||
|
||
|
||
def _gen_desc_obs_rows(base, tree_id, cid, y, trait_id, code, bvec, idx_of,
|
||
pyrand, rng, year_eff=None, reuse_vals=None,
|
||
evaluation_id=None, stage="evaluation") -> list[dict]:
|
||
"""为单棵树单年份生成 43 条描述性状观测（仅入选候选树 / ramet 分层使用）。
|
||
|
||
categorical → value_text（scale_json 选项抽样）；date → value_date（花期 3-4 月）；
|
||
numeric core → value_numeric（遗传代理 + 年度 + 误差）；纵/横径复用 stats 同源观测值。
|
||
"""
|
||
rows = []
|
||
year_eff = year_eff or {}
|
||
reuse_vals = reuse_vals or {}
|
||
for dcode, _name, _cat, dtype, options, mu, sd, vmin, vmax, reuse in DESCRIPTIVE_TRAITS:
|
||
# avg_fruit_weight/ssc 与 stats 撞 code 且同为 numeric：其观测由既有数值循环写入
|
||
# （同一 (evaluation_id, trait_id) 唯一约束下不可双写），描述侧不再造值。
|
||
if dcode in ("avg_fruit_weight", "ssc"):
|
||
continue
|
||
row = {**_base(),
|
||
"tree_id": tree_id, "combination_id": cid,
|
||
"trait_id": trait_id[dcode], "evaluate_year": y,
|
||
"stage": stage, "remark": f"{code} 描述观测"}
|
||
if evaluation_id is not None:
|
||
row["evaluation_id"] = evaluation_id
|
||
if dtype == "categorical":
|
||
row["value_text"] = str(pyrand.choice(options))
|
||
elif dtype == "date":
|
||
row["value_date"] = f"{y}-{pyrand.randint(3, 4):02d}-{pyrand.randint(1, 28):02d}"
|
||
else:
|
||
if reuse is not None:
|
||
v = reuse_vals.get(reuse)
|
||
if v is None:
|
||
continue
|
||
row["value_numeric"] = float(v)
|
||
else:
|
||
proxy, scale = DESCRIPTIVE_BV_PROXY.get(dcode, (None, 1.0))
|
||
g = scale * bvec[idx_of[proxy]] if proxy is not None and proxy in idx_of else 0.0
|
||
row["value_numeric"] = clip_round(mu + g + year_eff.get(dcode, 0.0)
|
||
+ rng.normal(0, sd * 0.6), vmin, vmax)
|
||
rows.append(row)
|
||
return rows
|
||
|
||
|
||
async def gen_juvenile_obs(session, counts, cid, tree_ids, bv, trait_id, params, sd, w,
|
||
start_year, end_year, rng, code) -> None:
|
||
if end_year < start_year:
|
||
return
|
||
juv = [c for c in params if params[c]["stage"] == "juvenile"]
|
||
rows = []
|
||
for y in range(start_year, end_year + 1):
|
||
year_eff = {c: rng.normal(0, params[c]["sd"] * 0.10) for c in juv}
|
||
for i in range(len(tree_ids)):
|
||
for c in juv:
|
||
p = params[c]
|
||
val = clip_round(p["mu"] + bv[i, p["idx"]] + year_eff[c] + rng.normal(0, p["sd"] * 0.6), p["vmin"], p["vmax"])
|
||
rows.append({**_base(),
|
||
"tree_id": tree_ids[i], "combination_id": cid,
|
||
"trait_id": trait_id[c], "evaluate_year": y,
|
||
"value_numeric": val, "stage": "juvenile",
|
||
"remark": f"{code} 童期观测"})
|
||
if rows:
|
||
counts["obs"] += await insert_chunked(session, TraitObservationModel, rows)
|
||
|
||
|
||
async def gen_eval_obs(session, counts, info, cid, tree_ids, bv, trait_id, params, sd, w,
|
||
sp_year, end_year, rng, personnel_ids, plot, code, pyrand) -> None:
|
||
eval_num = [c for c in params if params[c]["stage"] == "evaluation"]
|
||
text_specs = {t[0]: t for t in TEXT_TRAITS}
|
||
idx_of = {c: params[c]["idx"] for c in params}
|
||
eval_tree_idx = None
|
||
for rname in ("sp", "ap", "line", "regional", "released"):
|
||
r = info["rounds"].get(rname)
|
||
if r and r.get("active"):
|
||
eval_tree_idx = r["sel"]
|
||
break
|
||
if eval_tree_idx is None:
|
||
return
|
||
# 各树淘汰年前一年为止（无淘汰则评估至窗口末）
|
||
elim_year = {}
|
||
for rname in ("ap", "line", "regional", "released"):
|
||
r = info["rounds"].get(rname)
|
||
if r and r.get("active") and r.get("elim") is not None:
|
||
for ei in r["elim"]:
|
||
elim_year[ei] = r["year"] - 1
|
||
for y in range(sp_year, end_year + 1):
|
||
active = [i for i in eval_tree_idx if elim_year.get(i, end_year + 1) >= y]
|
||
if not active:
|
||
continue
|
||
year_eff = {c: rng.normal(0, params[c]["sd"] * 0.10) for c in eval_num}
|
||
# 单株评价（先插，取 id 供观测外键）
|
||
e_rows, eval_trees = [], []
|
||
for i in active:
|
||
z = idx_score(bv[i:i + 1], sd, w)[0]
|
||
score = float(np.clip(55 + 25 * (z / 1.5) + rng.normal(0, 3), 0, 100))
|
||
m = pyrand.randint(6, 8)
|
||
d = pyrand.randint(10, 28)
|
||
e_rows.append({**_base(),
|
||
"combination_id": cid, "tree_id": tree_ids[i], "evaluate_year": y,
|
||
"evaluate_date": f"{y}-{m:02d}-{d:02d}",
|
||
"bre_personnel_id": personnel_ids[y % len(personnel_ids)],
|
||
"overall_score": round(score, 1),
|
||
"crop_load": round(pyrand.uniform(0.3, 1.0), 2),
|
||
"remark": f"{code} 成株评价"})
|
||
eval_trees.append((i, m, d))
|
||
res = await session.execute(insert(TreeEvaluationModel).values(e_rows).returning(TreeEvaluationModel.id))
|
||
eval_ids = list(res.scalars())
|
||
counts["evals"] += len(e_rows)
|
||
eval_map = {i: eid for (i, _m, _d), eid in zip(eval_trees, eval_ids)}
|
||
date_map = {i: (m, d) for i, m, d in eval_trees}
|
||
# 观测（数值 + 文本）与采收（同源表型，保证一致）
|
||
o_rows, h_rows = [], []
|
||
for i in active:
|
||
eid = eval_map[i]
|
||
vals = {}
|
||
for c in eval_num:
|
||
p = params[c]
|
||
val = clip_round(p["mu"] + bv[i, p["idx"]] + year_eff[c] + rng.normal(0, p["sd"] * 0.6), p["vmin"], p["vmax"])
|
||
vals[c] = val
|
||
o_rows.append({**_base(),
|
||
"evaluation_id": eid, "tree_id": tree_ids[i], "combination_id": cid,
|
||
"trait_id": trait_id[c], "evaluate_year": y,
|
||
"value_numeric": val, "stage": "evaluation",
|
||
"crop_load": round(pyrand.uniform(0.3, 1.0), 2),
|
||
"remark": f"{code} 成株观测"})
|
||
for tcode, _name, _cat, _st, opts, probs in text_specs.values():
|
||
if tcode in ("flesh_color", "fruit_shape"):
|
||
continue # 已由描述性状 categorical 覆盖（同一 (evaluation_id, trait_id) 不双写）
|
||
o_rows.append({**_base(),
|
||
"evaluation_id": eid, "tree_id": tree_ids[i], "combination_id": cid,
|
||
"trait_id": trait_id[tcode], "evaluate_year": y,
|
||
"value_text": str(pyrand.choices(opts, weights=probs, k=1)[0]),
|
||
"stage": "evaluation", "remark": f"{code} 成株观测"})
|
||
# 描述性状：候选树全量 43 条，挂同一评价头（纵/横径复用本次 stats 观测）
|
||
o_rows.extend(_gen_desc_obs_rows(_base(), tree_ids[i], cid, y, trait_id, code,
|
||
bv[i], idx_of, pyrand, rng,
|
||
year_eff=year_eff, reuse_vals=vals,
|
||
evaluation_id=eid, stage="evaluation"))
|
||
m, d = date_map[i]
|
||
h_rows.append({**_base(),
|
||
"tree_id": tree_ids[i], "plot_id": plot["id"], "op_type": "harvest",
|
||
"op_date": date(y, m, d), "op_detail": "采收并记录单株产量",
|
||
"yield_kg": vals.get("single_tree_yield"),
|
||
"fruit_count": int(vals.get("fruit_number") or 0),
|
||
"avg_fruit_weight": vals.get("avg_fruit_weight"),
|
||
"marketable_rate": vals.get("marketable_rate"),
|
||
"operator_id": personnel_ids[y % len(personnel_ids)], "status": 1,
|
||
"remark": f"{code} 采收"})
|
||
if o_rows:
|
||
counts["obs"] += await insert_chunked(session, TraitObservationModel, o_rows)
|
||
if h_rows:
|
||
counts["field_ops"] += await insert_chunked(session, FieldOperationModel, h_rows)
|
||
|
||
|
||
async def gen_plot_ops(session, counts, plot_id, start_year, end_year, personnel_ids, pyrand, code) -> None:
|
||
ops = [("fertilize", 3), ("spray", 5), ("prune", 2), ("irrigate", 6)]
|
||
rows = []
|
||
for y in range(start_year, end_year + 1):
|
||
for op, mo in ops:
|
||
rows.append({**_base(), "plot_id": plot_id, "op_type": op,
|
||
"op_date": date(y, mo, pyrand.randint(1, 28)),
|
||
"op_detail": f"{code} 地块 {op}", "operator_id": personnel_ids[y % len(personnel_ids)],
|
||
"status": 1, "remark": "小区例行农事"})
|
||
if rows:
|
||
counts["field_ops"] += await insert_chunked(session, FieldOperationModel, rows)
|
||
|
||
|
||
# ---------------------------------------------------------------------------
|
||
# 批量晋级（真实服务层 create_batch，事务化 + 失败单株回退重试）
|
||
# ---------------------------------------------------------------------------
|
||
async def run_promotions(session, auth, queue, batch_size) -> tuple[int, int]:
|
||
ok = fail = 0
|
||
with bre_audit_suppress():
|
||
for item in queue:
|
||
base = {k: item[k] for k in
|
||
("selection_year", "is_selected", "rule_id", "approved_by",
|
||
"from_stage", "to_stage", "reason", "remark")}
|
||
for chunk in chunked(item["tree_ids"], batch_size):
|
||
data = SelectionResultBatchCreateSchema(combination_id=item["combination_id"], tree_id=chunk, **base)
|
||
try:
|
||
svc = SelectionResultService(auth, session)
|
||
res = await svc.create_batch(data)
|
||
await session.commit()
|
||
ok += res.success_count
|
||
fail += res.fail_count
|
||
for fd in res.fail_details:
|
||
print(f"[sim] !! 晋级失败: {fd}")
|
||
except Exception as e: # 整批失败：逐棵回退重试
|
||
await session.rollback()
|
||
for tid in chunk:
|
||
single = SelectionResultBatchCreateSchema(combination_id=item["combination_id"], tree_id=[tid], **base)
|
||
try:
|
||
svc = SelectionResultService(auth, session)
|
||
r = await svc.create_batch(single)
|
||
await session.commit()
|
||
ok += r.success_count
|
||
fail += r.fail_count
|
||
except Exception as e2:
|
||
await session.rollback()
|
||
fail += 1
|
||
print(f"[sim] !! 单棵晋级失败 tree_id={tid}: {e2}")
|
||
return ok, fail
|
||
|
||
|
||
# ---------------------------------------------------------------------------
|
||
# 派生组合（F2 / BC）
|
||
# ---------------------------------------------------------------------------
|
||
async def collect_line_germplasms(session, combo_info) -> list[dict]:
|
||
"""返回 line 及以上选中树的种质信息 [{code, seq, bv, germplasm_id, combo_id, year, clone_code}]。"""
|
||
names = []
|
||
for info in combo_info:
|
||
r = info["rounds"].get("line")
|
||
if not r or not r.get("active") or "sel" not in r:
|
||
continue
|
||
sp_order = info.get("sp_order", [])
|
||
for ei in r["sel"]:
|
||
ei = int(ei)
|
||
if ei not in sp_order:
|
||
continue
|
||
seq = sp_order.index(ei) + 1
|
||
names.append({"code": info["code"], "seq": seq, "bv": info["bv"][ei].copy(),
|
||
"combo_id": info["combo_id"], "year": r["year"], "clone_code": f"{info['code']}-{seq:04d}"})
|
||
if not names:
|
||
return []
|
||
rows = await session.execute(
|
||
text("SELECT id, cultivar_name FROM bre_germplasm WHERE cultivar_name = ANY(:codes)"),
|
||
{"codes": [n["clone_code"] for n in names]})
|
||
gid_by_name = {name: gid for gid, name in rows.all()}
|
||
for n in names:
|
||
n["germplasm_id"] = gid_by_name.get(n["clone_code"])
|
||
return [n for n in names if n["germplasm_id"]]
|
||
|
||
|
||
async def _gen_derived_combos(session, counts, combo_info, promo_queue, elim_accum, line_germ,
|
||
founder_bv, founder_ids, target_ids, f2_combos, f2_trees,
|
||
bc_combos, bc_trees,
|
||
params, sigmaA, sd, w, codes, rng, pyrand, personnel_ids, plots,
|
||
rule_ids, rootstock_ids, end_year, trait_id) -> int:
|
||
if not line_germ:
|
||
return 0
|
||
candidates = [g for g in line_germ if g["year"] <= end_year - 3]
|
||
if not candidates:
|
||
return 0
|
||
nfg = len(founder_ids)
|
||
ncan = len(candidates)
|
||
defs = []
|
||
for k in range(f2_combos):
|
||
a = candidates[k % ncan]
|
||
b_idx = (k * 7 + 3) % ncan
|
||
b = candidates[b_idx]
|
||
# 候选池中同一组合的多个 line 种质（同 combo_id）连续成块，
|
||
# 若固定 index 取到同块元素会无限循环，故循环体内 index 递增并加防死循环上限
|
||
guard = 0
|
||
while b["combo_id"] == a["combo_id"] and guard < ncan:
|
||
b_idx = (b_idx + 1) % ncan
|
||
b = candidates[b_idx]
|
||
guard += 1
|
||
if b["combo_id"] == a["combo_id"]:
|
||
continue
|
||
cy = max(a["year"], b["year"]) + 1
|
||
if cy > end_year - 1:
|
||
continue
|
||
defs.append({"generation": "F2", "dam": a, "sire": b, "cross_year": cy, "n": f2_trees, "kind": "F2"})
|
||
for k in range(bc_combos):
|
||
a = candidates[k % len(candidates)]
|
||
fi = (k * 5 + 1) % nfg
|
||
cy = a["year"] + 1
|
||
if cy > end_year - 1:
|
||
continue
|
||
defs.append({"generation": "BC1", "dam": a,
|
||
"sire": {"bv": founder_bv[fi], "germplasm_id": founder_ids[fi]},
|
||
"cross_year": cy, "n": bc_trees, "kind": "BC"})
|
||
if not defs:
|
||
return 0
|
||
combo_rows = []
|
||
for k, d in enumerate(defs):
|
||
code = f"桃{d['cross_year']}{d['kind']}-{k + 1:03d}"
|
||
combo_rows.append({
|
||
"combination_code": code, "cross_year": d["cross_year"],
|
||
"bre_target_id": target_ids[k % len(target_ids)],
|
||
"female_parent_id": d["dam"].get("germplasm_id"),
|
||
"male_parent_id": d["sire"].get("germplasm_id"),
|
||
"cross_method": "人工授粉", "cross_type": "杂交" if d["kind"] == "F2" else "回交",
|
||
"design_type": "partial_diallel", "stage": "seedling",
|
||
"cross_date": f"{d['cross_year']}-04-{10 + k % 10:02d}", "seed_count": pyrand.randint(200, 500),
|
||
"reason": "世代推进", "remark": f"{d['kind']} 分离世代",
|
||
})
|
||
res = await session.execute(
|
||
insert(CrossCombinationModel).values([{**_base(), **r} for r in combo_rows]).returning(CrossCombinationModel.id))
|
||
ids = list(res.scalars())
|
||
counts["combos"] += len(ids)
|
||
nd = len(defs)
|
||
for k, (d, cid) in enumerate(zip(defs, ids)):
|
||
if k % 20 == 0 or k == nd - 1:
|
||
print(f"[sim] F2/BC 组合 {k + 1}/{nd}（{d['kind']} {d['cross_year']}）...")
|
||
c = {"code": f"桃{d['cross_year']}{d['kind']}-{k + 1:03d}", "id": cid,
|
||
"generation": d["generation"], "planted": d["cross_year"] + 1,
|
||
"dam_bv": d["dam"]["bv"], "sire_bv": d["sire"]["bv"],
|
||
"dam_id": d["dam"].get("germplasm_id"), "sire_id": d["sire"].get("germplasm_id"),
|
||
"cross_year": d["cross_year"], "n": d["n"]}
|
||
await _gen_combo(session, counts, combo_info, promo_queue, elim_accum, c,
|
||
founder_bv, params, sigmaA, sd, w, codes, rng, pyrand,
|
||
personnel_ids, plots, rule_ids, rootstock_ids, end_year, trait_id)
|
||
return len(ids)
|
||
|
||
|
||
# ---------------------------------------------------------------------------
|
||
# 区域试验
|
||
# ---------------------------------------------------------------------------
|
||
async def gen_trials(session, counts, combo_info, plots, target_ids, plan_ids) -> None:
|
||
regional = [] # (combo_id, year, clone_code)
|
||
for info in combo_info:
|
||
r = info["rounds"].get("regional")
|
||
if not r or not r.get("active") or "sel" not in r:
|
||
continue
|
||
sp_order = info.get("sp_order", [])
|
||
for ei in r["sel"]:
|
||
ei = int(ei)
|
||
if ei not in sp_order:
|
||
continue
|
||
seq = sp_order.index(ei) + 1
|
||
regional.append((info["combo_id"], r["year"], f"{info['code']}-{seq:04d}"))
|
||
if not regional:
|
||
return
|
||
rows = await session.execute(
|
||
text("SELECT id, cultivar_name FROM bre_germplasm WHERE cultivar_name = ANY(:codes)"),
|
||
{"codes": [name for _, _, name in regional]})
|
||
gid_by_name = {name: gid for gid, name in rows.all()}
|
||
by_year: dict[int, list] = {}
|
||
for cid, y, name in regional:
|
||
gid = gid_by_name.get(name)
|
||
if gid:
|
||
by_year.setdefault(y, []).append((cid, gid))
|
||
for y, items in sorted(by_year.items()):
|
||
trial = {**_base(), "trial_name": f"桃区试-{y}", "plan_id": plan_ids[0] if plan_ids else None,
|
||
"target_id": target_ids[y % len(target_ids)] if target_ids else None,
|
||
"trial_type": "regional", "design_type": "randomized_block",
|
||
"start_year": y, "end_year": y + 2, "objective": f"{y} 年区域试验", "remark": "模拟数据"}
|
||
tres = await session.execute(insert(TrialModel).values([trial]).returning(TrialModel.id))
|
||
trial_id = list(tres.scalars())[0]
|
||
counts["trials"] += 1
|
||
for site in {p["site_id"] for p in plots}:
|
||
study = {**_base(), "trial_id": trial_id, "study_name": f"区试-{y}-site{site}", "site_id": site,
|
||
"year": y, "block_count": 3, "season": "夏", "design_type": "randomized_block",
|
||
"remark": "模拟数据"}
|
||
sres = await session.execute(insert(TrialStudyModel).values([study]).returning(TrialStudyModel.id))
|
||
study_id = list(sres.scalars())[0]
|
||
counts["studies"] += 1
|
||
entry_rows = [{**_base(), "trial_study_id": study_id, "entry_number": eno,
|
||
"germplasm_id": gid, "combination_id": cid, "block_no": eno % 3 + 1,
|
||
"remark": "模拟数据"}
|
||
for eno, (cid, gid) in enumerate(items, start=1)]
|
||
await insert_chunked(session, TrialStudyEntryModel, entry_rows)
|
||
counts["entries"] += len(entry_rows)
|
||
|
||
|
||
# ---------------------------------------------------------------------------
|
||
# 业务流空表补齐（授粉→花粉→种子批→种子处理→育苗、克隆扩繁、环境气象、通用观测、试验处理）
|
||
# ---------------------------------------------------------------------------
|
||
async def gen_support_data(session, counts, combo_info, personnel_ids, plots, rootstock_ids,
|
||
trait_id, params, codes, end_year, pyrand, rng) -> None:
|
||
"""为流程侧空表生成模拟数据（纯批量 insert，不走服务层）。
|
||
|
||
授粉链按组合逐条生成（F1/F2/BC 全含）；扩繁只对晋级到 line 的树（其 clone 已由
|
||
选择晋级服务层创建）；气象按 site×year 唯一；通用观测给 line 树补 EAV 记录。
|
||
"""
|
||
# 组合基础信息（seed_count / 父本种质）
|
||
combo_meta = {cid: {"seed_count": sc, "male_parent_id": mpid}
|
||
for cid, sc, mpid in (await session.execute(
|
||
text("SELECT id, seed_count, male_parent_id FROM bre_cross_combination"))).all()}
|
||
name_by_gid = {gid: name for gid, name in (await session.execute(
|
||
select(BreedingGermplasmModel.id, BreedingGermplasmModel.cultivar_name))).all()}
|
||
|
||
# 1) 每组合一条授粉链：pollen → pollination → seed_lot → seed_treatment → seedling
|
||
pollen_rows, polli_rows, seed_rows, treat_rows, seedl_rows = [], [], [], [], []
|
||
for info in combo_info:
|
||
code, cid = info["code"], info["combo_id"]
|
||
cy = info["cross_year"]
|
||
meta = combo_meta.get(cid, {"seed_count": pyrand.randint(300, 800), "male_parent_id": None})
|
||
seed_count = meta["seed_count"] or pyrand.randint(300, 800)
|
||
mpid = meta.get("male_parent_id")
|
||
plot = plots[cy % len(plots)]
|
||
persona = personnel_ids[cy % len(personnel_ids)]
|
||
# 花粉批次（父本混合花粉，母/父本为种质无树）
|
||
pollen_rows.append({**_base(),
|
||
"lot_code": f"PL-{code}", "male_tree_id": None, "source_type": "mixed",
|
||
"source_desc": f"父本混合花粉（{name_by_gid.get(mpid, '亲本')}）",
|
||
"collect_date": date(cy, 3, 15), "collect_method": "采花取粉",
|
||
"quantity": f"{pyrand.randint(20, 80)}g", "storage_method": "minus80",
|
||
"viability_method": "ttc", "viability_pct": round(pyrand.uniform(70, 92), 1),
|
||
"viability_test_date": date(cy, 3, 20), "expiry_date": date(cy, 5, 15),
|
||
"remark": "模拟数据"})
|
||
# 授粉
|
||
polli_rows.append({**_base(),
|
||
"combination_id": cid, "plot_id": plot["id"],
|
||
"pollination_date": f"{cy}-04-{10 + cy % 10:02d}",
|
||
"flower_count": pyrand.randint(800, 3000), "effective_count": pyrand.randint(200, 800),
|
||
"bre_personnel_id": persona, "pollinator": None,
|
||
"emasculation_date": date(cy, 4, 5), "bagging_date": date(cy, 4, 25),
|
||
"female_tree_id": None, "male_tree_id": None,
|
||
"pollination_method": "人工点授", "pollen_lot_id": None, "remark": "模拟数据"})
|
||
# 育苗出苗数（衔接定植株数 n），种子批 used_count 同步
|
||
emerg = max(int(info["n"] * pyrand.uniform(1.2, 2.0)), 1)
|
||
strong = int(emerg * pyrand.uniform(0.85, 1.0))
|
||
seed_rows.append({**_base(),
|
||
"combination_id": cid, "pollination_id": None, "lot_code": f"SL-{code}",
|
||
"harvest_year": cy, "seed_count": seed_count, "used_count": emerg,
|
||
"germination_rate": round(pyrand.uniform(55, 85), 2),
|
||
"storage_type": "种子库", "storage_location": f"库A-{cy % 8 + 1}架",
|
||
"test_date": date(cy, 8, 15), "remark": "模拟数据"})
|
||
# 种子处理（冬季低温层积）
|
||
treat_rows.append({**_base(),
|
||
"combination_id": cid, "seed_lot_id": None, "treatment_method": "低温层积催芽",
|
||
"treatment_start": f"{cy}-10-01", "treatment_end": f"{cy + 1}-02-15",
|
||
"germination_rate": round(pyrand.uniform(55, 85), 1),
|
||
"bre_personnel_id": persona, "remark": "模拟数据"})
|
||
# 育苗（stage 3 壮苗，衔接定植）
|
||
sowing = f"{cy + 1}-03-05"
|
||
seedl_rows.append({**_base(),
|
||
"combination_id": cid, "treatment_id": None, "seed_lot_id": None,
|
||
"batch_no": f"YP-{cid}-{sowing.replace('-', '')}-1",
|
||
"sowing_date": sowing, "tray_no": f"T-{cy % 40 + 1:03d}", "nursery": "育苗圃",
|
||
"seedling_count": emerg, "emergence_date": f"{cy + 1}-04-01",
|
||
"strong_seedling_date": f"{cy + 1}-05-10", "strong_seedling_count": strong,
|
||
"stage": "3", "bre_personnel_id": persona, "remark": "模拟数据"})
|
||
|
||
if pollen_rows:
|
||
pollen_ids = list((await session.execute(
|
||
insert(PollenModel).values(pollen_rows).returning(PollenModel.id))).scalars())
|
||
for r, pid in zip(polli_rows, pollen_ids):
|
||
r["pollen_lot_id"] = pid
|
||
polli_ids = list((await session.execute(
|
||
insert(PollinationModel).values(polli_rows).returning(PollinationModel.id))).scalars())
|
||
for r, pid in zip(seed_rows, polli_ids):
|
||
r["pollination_id"] = pid
|
||
seed_ids = list((await session.execute(
|
||
insert(SeedLotModel).values(seed_rows).returning(SeedLotModel.id))).scalars())
|
||
for r, sid in zip(treat_rows, seed_ids):
|
||
r["seed_lot_id"] = sid
|
||
treat_ids = list((await session.execute(
|
||
insert(SeedTreatmentModel).values(treat_rows).returning(SeedTreatmentModel.id))).scalars())
|
||
for r, tid, sid in zip(seedl_rows, treat_ids, seed_ids):
|
||
r["treatment_id"], r["seed_lot_id"] = tid, sid
|
||
await insert_chunked(session, SeedlingModel, seedl_rows)
|
||
counts.update(pollen=len(pollen_ids), pollination=len(polli_ids),
|
||
seed_lots=len(seed_ids), seed_treatments=len(treat_ids),
|
||
seedlings=len(seedl_rows))
|
||
await session.commit()
|
||
|
||
# 2) 克隆扩繁闭环：为每个入选克隆补 K 棵共享 clone_id 的无性系苗（ramet）树 + 扩繁批次
|
||
# + 童期/成株观测。ABLUP G1 门禁按 c{clone}（回退 f{组合}）分组且要求每组 ≥ min_clone_n，
|
||
# 服务层晋级为每入选树各建唯一克隆（1:1），缺 ramet 即每组 n=1 无法建模，故按真实
|
||
# "入选株→克隆→扩繁→新树"闭环补齐；ramet 共享克隆 BV，观测仅加年度与误差噪声。
|
||
clone_id_by_code = {cc: cid for cid, cc in (await session.execute(
|
||
select(CloneModel.id, CloneModel.clone_code))).all()}
|
||
orig_by_clone: dict[int, tuple] = {}
|
||
for tid, cl, cid_, gen, dam, sire, plot_id, root, persona, stg in (await session.execute(
|
||
text("SELECT id, clone_id, combination_id, generation, dam_id, sire_id, plot_id, "
|
||
"rootstock_id, bre_personnel_id, stage FROM bre_tree "
|
||
"WHERE clone_id IS NOT NULL AND is_deleted = false"))).all():
|
||
orig_by_clone[cl] = (tid, cid_, gen, dam, sire, plot_id, root, persona, stg)
|
||
eval_num = [c for c in params if params[c]["stage"] == "evaluation"]
|
||
juv_num = [c for c in params if params[c]["stage"] == "juvenile"]
|
||
idx_of = {c: params[c]["idx"] for c in params}
|
||
prop_rows = []
|
||
for info in combo_info:
|
||
sp_order = info.get("sp_order", [])
|
||
if not sp_order:
|
||
continue
|
||
code, cy = info["code"], info["cross_year"]
|
||
sp_year = info["planted"] + 3
|
||
ev_years = [y for y in range(sp_year, min(sp_year + RAMET_EVAL_YEARS, end_year + 1))]
|
||
if not ev_years:
|
||
continue
|
||
bv = info["bv"]
|
||
combo_id = info["combo_id"]
|
||
gen = info["generation"]
|
||
plot = plots[cy % len(plots)]
|
||
local_ramets, local_meta = [], []
|
||
for seq, ei in enumerate(sp_order, 1):
|
||
clone_code = f"{code}-{seq:04d}"
|
||
cid_ = clone_id_by_code.get(clone_code)
|
||
orig = orig_by_clone.get(cid_) if cid_ else None
|
||
if not orig:
|
||
continue
|
||
_tid, _cid, _gen, dam, sire, plot_id, root, persona, stg = orig
|
||
rs_id = root or rootstock_ids[(cy + seq) % len(rootstock_ids)]
|
||
op_id = persona or personnel_ids[(cy + seq) % len(personnel_ids)]
|
||
grafted = pyrand.randint(40, 90)
|
||
prop_rows.append({**_base(),
|
||
"batch_code": f"PR-{clone_code}", "scion_source_type": "tree",
|
||
"scion_source_id": _tid, "produced_clone_id": cid_,
|
||
"rootstock_id": rs_id, "method": "嫁接", "graft_date": date(sp_year, 3, 5),
|
||
"nursery_site_id": plot["site_id"], "operator_id": op_id,
|
||
"scion_count": grafted, "grafted_count": grafted,
|
||
"survival_count": int(grafted * pyrand.uniform(0.75, 0.95)),
|
||
"destination": "定植", "remark": "模拟数据"})
|
||
for k in range(K_RAMETS):
|
||
local_ramets.append({**_base(),
|
||
"combination_id": combo_id, "dam_id": dam, "sire_id": sire,
|
||
"clone_id": cid_, "plot_id": plot_id or plot["id"],
|
||
"tree_no": f"{code}-{seq:04d}R{k + 1:02d}",
|
||
"row_no": k + 1, "col_no": seq % 20 + 1, "block_no": seq % 3 + 1,
|
||
"rootstock_id": rs_id, "planted_date": f"{sp_year}-03-10",
|
||
"bre_personnel_id": op_id, "status": "alive", "stage": stg,
|
||
"generation": gen, "remark": f"{clone_code} 克隆无性系苗"})
|
||
local_meta.append((bv[ei], ev_years, code, combo_id))
|
||
if prop_rows:
|
||
await insert_chunked(session, PropagationModel, prop_rows)
|
||
counts["propagations"] += len(prop_rows)
|
||
prop_rows = []
|
||
if local_ramets:
|
||
rid_rows = list((await session.execute(
|
||
insert(TreeModel).values(local_ramets).returning(TreeModel.id))).scalars())
|
||
counts["trees"] += len(rid_rows)
|
||
obs = []
|
||
for tid, (bvec, years, code_, cid_) in zip(rid_rows, local_meta):
|
||
for y in years:
|
||
year_eff = {c: rng.normal(0, params[c]["sd"] * 0.10)
|
||
for c in eval_num + juv_num}
|
||
reuse_vals = {}
|
||
for c in eval_num + juv_num:
|
||
p = params[c]
|
||
val = clip_round(p["mu"] + bvec[p["idx"]] + year_eff[c]
|
||
+ rng.normal(0, p["sd"] * 0.6), p["vmin"], p["vmax"])
|
||
if c in ("fruit_length", "fruit_diameter"):
|
||
reuse_vals[c] = val
|
||
obs.append({**_base(),
|
||
"tree_id": tid, "combination_id": cid_, "trait_id": trait_id[c],
|
||
"evaluate_year": y, "value_numeric": val,
|
||
"stage": params[c]["stage"], "remark": f"{code_} 克隆观测"})
|
||
# 描述性状：克隆苗全量 43 条，无评价头（tree_id 直挂），纵/横径复用本次观测
|
||
obs.extend(_gen_desc_obs_rows(_base(), tid, cid_, y, trait_id, code_,
|
||
bvec, idx_of, pyrand, rng,
|
||
year_eff=year_eff, reuse_vals=reuse_vals,
|
||
stage="evaluation"))
|
||
counts["obs"] += await insert_chunked(session, TraitObservationModel, obs, 20000)
|
||
|
||
# 3) 环境气象：site × year（2007..end_year，site×year 唯一）
|
||
env_rows = []
|
||
for sid in sorted({p["site_id"] for p in plots}):
|
||
for y in range(2007, end_year + 1):
|
||
env_rows.append({**_base(),
|
||
"site_id": sid, "year": y,
|
||
"chilling_hours": round(750 + sid * 40 + pyrand.uniform(-120, 120), 1),
|
||
"growing_degree_days": round(3200 + pyrand.uniform(-300, 300), 1),
|
||
"rainfall_mm": round(620 + sid * 80 + pyrand.uniform(-120, 120), 1),
|
||
"temp_avg": round(14.5 + sid * 0.8 + pyrand.uniform(-1.5, 1.5), 1),
|
||
"soil_moisture": round(0.55 + pyrand.uniform(-0.1, 0.1), 2),
|
||
"source": "气象站插值", "remark": "模拟数据"})
|
||
if env_rows:
|
||
await insert_chunked(session, EnvironmentConditionModel, env_rows)
|
||
counts["env_conds"] += len(env_rows)
|
||
|
||
# 4) 通用观测（EAV）：line 晋级树补 ssc(数值) / 果肉颜色(文本) / 果实形状(文本)
|
||
text_specs = {t[0]: t for t in TEXT_TRAITS}
|
||
ssc_p = params["ssc"]
|
||
obs_rows = []
|
||
for info in combo_info:
|
||
r = info["rounds"].get("line")
|
||
if not r or not r.get("active") or "sel" not in r:
|
||
continue
|
||
obs_year = r["year"]
|
||
op = personnel_ids[obs_year % len(personnel_ids)]
|
||
bv = info["bv"]
|
||
for ei in r["sel"]:
|
||
ei = int(ei)
|
||
tid = info["tree_ids"][ei]
|
||
ssc_val = clip_round(ssc_p["mu"] + bv[ei, ssc_p["idx"]] + pyrand.gauss(0, ssc_p["sd"] * 0.4),
|
||
ssc_p["vmin"], ssc_p["vmax"])
|
||
obs_rows.append({**_base(),
|
||
"tree_id": tid, "trait_id": trait_id.get("ssc"), "obs_date": date(obs_year, 7, 20),
|
||
"obs_year": obs_year, "obs_value": f"{ssc_val:.1f}", "obs_type": "numeric",
|
||
"operator_id": op, "status": 1, "remark": "模拟数据"})
|
||
for tcode, t_tid in (("flesh_color", trait_id.get("flesh_color")),
|
||
("fruit_shape", trait_id.get("fruit_shape"))):
|
||
val = pyrand.choices(text_specs[tcode][4], weights=text_specs[tcode][5], k=1)[0]
|
||
obs_rows.append({**_base(),
|
||
"tree_id": tid, "trait_id": t_tid, "obs_date": date(obs_year, 7, 20),
|
||
"obs_year": obs_year, "obs_value": val, "obs_type": "text",
|
||
"operator_id": op, "status": 1, "remark": "模拟数据"})
|
||
if obs_rows:
|
||
counts["observations"] += await insert_chunked(session, ObservationModel, obs_rows)
|
||
await session.commit()
|
||
|
||
# 5) 试验处理：每个研究点 2-3 条
|
||
study_ids = [r[0] for r in (await session.execute(select(TrialStudyModel.id))).all()]
|
||
treat_rows = []
|
||
for sid in study_ids:
|
||
for tf, lv, desc in (("栽培管理", "对照", "常规栽培"), ("栽培管理", "提质", "控产提质"),
|
||
("施肥", "常规", "常规施肥"), ("施肥", "减量", "减量增效")):
|
||
if pyrand.random() < 0.65:
|
||
treat_rows.append({**_base(), "trial_study_id": sid, "factor": tf,
|
||
"level": lv, "description": desc, "remark": "模拟数据"})
|
||
if treat_rows:
|
||
await insert_chunked(session, TreatmentModel, treat_rows)
|
||
counts["treatments"] += len(treat_rows)
|
||
|
||
|
||
# ---------------------------------------------------------------------------
|
||
# 汇总与自检
|
||
# ---------------------------------------------------------------------------
|
||
async def report_counts(session, counts) -> None:
|
||
print("[sim] 生成统计：")
|
||
print(f" 杂交组合 : {counts['combos']}")
|
||
print(f" 单株 : {counts['trees']}")
|
||
print(f" 单株评价 : {counts['evals']}")
|
||
print(f" 性状观测 : {counts['obs']}")
|
||
print(f" 农事操作 : {counts['field_ops']}")
|
||
print(f" 选育结果(晋级+淘汰) : {counts['selections']}")
|
||
print(f" 定植批次 : {counts['plantings']}")
|
||
print(f" 区域试验/研究点/参试: {counts['trials']}/{counts['studies']}/{counts['entries']}")
|
||
for t, key in (("bre_clone", "clones"), ("bre_germplasm", "germplasm"), ("bre_pedigree", "pedigree")):
|
||
n = (await session.execute(text(f"SELECT count(*) FROM {t}"))).scalar()
|
||
counts[key] = n
|
||
print(f" {t:<16} : {n}")
|
||
for t, key in (("bre_pollination", "pollination"), ("bre_pollen", "pollen"),
|
||
("bre_seed_lot", "seed_lots"), ("bre_seed_treatment", "seed_treatments"),
|
||
("bre_seedling", "seedlings"), ("bre_propagation", "propagations"),
|
||
("bre_environment_condition", "env_conds"), ("bre_observation", "observations"),
|
||
("bre_treatment", "treatments")):
|
||
print(f" {t:<20} : {counts[key]}")
|
||
|
||
|
||
async def verify(session, counts, dry: bool = False) -> None:
|
||
print("[sim] 自检 ...")
|
||
problems = []
|
||
min_obs = 8000 if dry else 300000
|
||
|
||
def chk(name, cond):
|
||
ok_ = bool(cond)
|
||
print(f" [{'OK ' if ok_ else 'BAD'}] {name}")
|
||
if not ok_:
|
||
problems.append(name)
|
||
|
||
n_tree = (await session.execute(text("SELECT count(*) FROM bre_tree"))).scalar()
|
||
n_obs = (await session.execute(text("SELECT count(*) FROM bre_trait_observation"))).scalar()
|
||
n_obs_orphan = (await session.execute(text(
|
||
"SELECT count(*) FROM bre_trait_observation o LEFT JOIN bre_tree t ON t.id=o.tree_id "
|
||
"WHERE o.tree_id IS NOT NULL AND t.id IS NULL"))).scalar()
|
||
n_eval_orphan = (await session.execute(text(
|
||
"SELECT count(*) FROM bre_tree_evaluation e LEFT JOIN bre_tree t ON t.id=e.tree_id "
|
||
"WHERE t.id IS NULL"))).scalar()
|
||
n_sel_orphan = (await session.execute(text(
|
||
"SELECT count(*) FROM bre_selection_result s LEFT JOIN bre_tree t ON t.id=s.tree_id "
|
||
"WHERE t.id IS NULL"))).scalar()
|
||
n_dup_tree = (await session.execute(text(
|
||
"SELECT count(*) FROM (SELECT tree_no FROM bre_tree GROUP BY tree_no HAVING count(*)>1) x"))).scalar()
|
||
n_dup_clone = (await session.execute(text(
|
||
"SELECT count(*) FROM (SELECT clone_code FROM bre_clone GROUP BY clone_code HAVING count(*)>1) x"))).scalar()
|
||
n_germ_dup = (await session.execute(text(
|
||
"SELECT count(*) FROM (SELECT cultivar_name FROM bre_germplasm "
|
||
"WHERE cultivar_name ~ '\\-\\d{4}$' GROUP BY cultivar_name HAVING count(*)>1) x"))).scalar()
|
||
n_sp = (await session.execute(text("SELECT count(*) FROM bre_tree WHERE stage='sp'"))).scalar()
|
||
n_ap = (await session.execute(text("SELECT count(*) FROM bre_tree WHERE stage='ap'"))).scalar()
|
||
n_line = (await session.execute(text("SELECT count(*) FROM bre_tree WHERE stage='line'"))).scalar()
|
||
n_elim = (await session.execute(text("SELECT count(*) FROM bre_tree WHERE status='eliminated'"))).scalar()
|
||
n_sel_tree = (await session.execute(text(
|
||
"SELECT count(*) FROM bre_tree WHERE status IN ('selected','primary','key','preserved')"))).scalar()
|
||
n_line_germ = (await session.execute(text(
|
||
"SELECT count(*) FROM bre_germplasm WHERE cultivar_name ~ '\\-\\d{4}$'"))).scalar()
|
||
n_sel_total = (await session.execute(text("SELECT count(*) FROM bre_selection_result"))).scalar()
|
||
n_polli = (await session.execute(text("SELECT count(*) FROM bre_pollination"))).scalar()
|
||
n_pollen = (await session.execute(text("SELECT count(*) FROM bre_pollen"))).scalar()
|
||
n_lot = (await session.execute(text("SELECT count(*) FROM bre_seed_lot"))).scalar()
|
||
n_treat = (await session.execute(text("SELECT count(*) FROM bre_seed_treatment"))).scalar()
|
||
n_seedl = (await session.execute(text("SELECT count(*) FROM bre_seedling"))).scalar()
|
||
n_prop = (await session.execute(text("SELECT count(*) FROM bre_propagation"))).scalar()
|
||
n_env = (await session.execute(text("SELECT count(*) FROM bre_environment_condition"))).scalar()
|
||
n_obs_g = (await session.execute(text("SELECT count(*) FROM bre_observation"))).scalar()
|
||
n_trt = (await session.execute(text("SELECT count(*) FROM bre_treatment"))).scalar()
|
||
n_seedl_orphan = (await session.execute(text(
|
||
"SELECT count(*) FROM bre_seedling s LEFT JOIN bre_seed_lot l ON l.id = s.seed_lot_id "
|
||
"WHERE s.seed_lot_id IS NOT NULL AND l.id IS NULL"))).scalar()
|
||
n_prop_orphan = (await session.execute(text(
|
||
"SELECT count(*) FROM bre_propagation p LEFT JOIN bre_clone c ON c.id = p.produced_clone_id "
|
||
"WHERE p.produced_clone_id IS NOT NULL AND c.id IS NULL"))).scalar()
|
||
n_obs_tree_orphan = (await session.execute(text(
|
||
"SELECT count(*) FROM bre_observation o LEFT JOIN bre_tree t ON t.id = o.tree_id "
|
||
"WHERE o.tree_id IS NOT NULL AND t.id IS NULL"))).scalar()
|
||
n_clone_single = (await session.execute(text(
|
||
"SELECT count(*) FROM (SELECT clone_id FROM bre_tree "
|
||
"WHERE clone_id IS NOT NULL AND is_deleted = false "
|
||
"GROUP BY clone_id HAVING count(*) < 2) x"))).scalar()
|
||
n_ramet_orphan = (await session.execute(text(
|
||
"SELECT count(*) FROM bre_tree t LEFT JOIN bre_clone c ON c.id = t.clone_id "
|
||
"WHERE t.clone_id IS NOT NULL AND c.id IS NULL"))).scalar()
|
||
|
||
chk("单株数 > 0", n_tree > 0)
|
||
chk(f"观测数 ≥ {min_obs / 10000:g}万", n_obs >= min_obs)
|
||
chk("观测无孤儿 tree_id", n_obs_orphan == 0)
|
||
chk("评价无孤儿 tree_id", n_eval_orphan == 0)
|
||
chk("选育结果无孤儿 tree_id", n_sel_orphan == 0)
|
||
chk("单株编号无重复", n_dup_tree == 0)
|
||
chk("克隆编号无重复", n_dup_clone == 0)
|
||
chk("晋级种质无重名", n_germ_dup == 0)
|
||
chk("晋级阶段分布合理", n_sp >= n_ap >= n_line and n_sp > 0)
|
||
chk("有淘汰树且选中树存活", n_elim > 0 and n_sel_tree > 0)
|
||
chk("晋级种质（line+）> 0", n_line_germ > 0)
|
||
chk("选育结果 = 晋级 + 淘汰", n_sel_total == counts["selections"])
|
||
chk("授粉/花粉/种子链有数据", n_polli > 0 and n_pollen > 0 and n_lot > 0 and n_treat > 0 and n_seedl > 0)
|
||
chk("扩繁/气象/通用观测/处理有数据", n_prop > 0 and n_env > 0 and n_obs_g > 0 and n_trt > 0)
|
||
chk("育苗无孤儿 seed_lot_id", n_seedl_orphan == 0)
|
||
chk("扩繁无孤儿 clone_id", n_prop_orphan == 0)
|
||
chk("通用观测无孤儿 tree_id", n_obs_tree_orphan == 0)
|
||
chk("每个克隆 ≥ 2 棵树（ABLUP G1 可建模）", n_clone_single == 0)
|
||
chk("克隆树无孤儿 clone_id", n_ramet_orphan == 0)
|
||
|
||
print(f" [信息] stage 分布: seedling/sp/ap/line = "
|
||
f"{n_tree - n_sp - n_ap - n_line}/{n_sp}/{n_ap}/{n_line}")
|
||
if problems:
|
||
print(f"[sim] 自检失败 {len(problems)} 项: {problems}")
|
||
raise SystemExit(1)
|
||
print("[sim] 自检全部通过 ✓")
|
||
|
||
|
||
if __name__ == "__main__":
|
||
asyncio.run(main())
|