Phase A1 Implementation Plan — Excel 樣板設計 + 下載

# 確認 MF 怎麼接 system_characteristic
grep -rn "module_frame.*system_characteristic\|system_characteristic.*module_frame" \
  app/ infra/ domain/ 2>/dev/null

# 看 SSP versioning service 怎麼從 MF init system_characteristic
grep -n "system_characteristic" app/oscal/service/ssp_versioning_service.py

# 看 jedi-oscal profile service 有沒有 "list controls of profile" method
find ~/Projects/Jedicogy/module/jedi-python-package/jedi-oscal -name "profile*.py" | head -10
grep -rn "def.*profile.*control\|def.*get.*controls\|def.*list.*controls" \
  ~/Projects/Jedicogy/module/jedi-python-package/jedi-oscal/jedi_oscal/ 2>/dev/null | head -20

# 看 GRC project start flow 怎麼從 profile clone control 到 AP
grep -n "profile.*control\|init_ap_controls" app/project/service/oscal_project_service.py

grep -rn "def.*list.*tenant\|tenant.*users\|users.*tenant" \
  ~/Projects/Jedicogy/module/jedi-python-package/jedi-auth/jedi_auth/domain/ 2>/dev/null | head -10

# MF own (existing) vs tenant pool
ls infra/module_frame/models/module_frame_reference_document.py
grep -rn "reference_document" infra/compliance/ 2>/dev/null | head -10

grep -rn "context_type.*module_frame\|'module_frame'.*context\|MODULE_FRAME" \
  app/ infra/ domain/ ~/Projects/Jedicogy/module/jedi-python-package/jedi-oscal/jedi_oscal/ 2>/dev/null \
  | grep -v __pycache__ | head -15

poetry run python3 -c "
from openpyxl import Workbook
from openpyxl.workbook.defined_name import DefinedName
wb = Workbook()
dn = DefinedName(name='test_range', attr_text='Sheet1!\$A\$1:\$A\$10')
wb.defined_names['test_range'] = dn
print('API works:', wb.defined_names['test_range'].attr_text)
"

# api/module_frame/excel_template_router.py
from flask import Blueprint, send_file, request
from flask_restful import Api, Resource
from dependency_injector.wiring import inject, Provide
from common.middleware.jwt_mw import jwt_required
from di_containers.containers import Container

## ⚠️ T0 Verify 後的命名更新
## 既有 api/module_frame/__init__.py 已有 blueprint (url_prefix='/api/1.0')，
## 不另開 Blueprint — 把 route 加進既有 create_module() 即可。
api = Api(bp)


class ExcelTemplateResource(Resource):
    method_decorators = [jwt_required()]

    @inject
    def get(
        self,
        module_frame_uid: str,
        excel_template_app_service=Provide[Container.module_frame_container.excel_template_app_service],
    ):
        mode = request.args.get("mode", "blank")
        if mode not in ("blank", "filled"):
            raise BadRequestError(GrcErrorCode.GRC_INVALID_PARAM)
        locale = request.args.get("locale")
        bytesio, filename = excel_template_app_service.generate(
            module_frame_uid=module_frame_uid,
            mode=mode,
            locale=locale,
        )
        return send_file(
            bytesio,
            mimetype="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
            as_attachment=True,
            download_name=filename,
        )


api.add_resource(SspImportTemplateResource, "/module-frame/<string:module_frame_uid>/ssp-import-template")

# sheet_definitions.py
from dataclasses import dataclass
from enum import StrEnum

class LookupSource(StrEnum):
    USERS = "users"
    ORG_UNITS = "org_units"
    DEVICES = "devices"
    INFO_SYSTEMS = "info_systems"
    REF_DOCS = "ref_docs"
    ORGS = "orgs"

@dataclass(frozen=True)
class ColumnDef:
    key: str                          # 對應 data row dict key
    header_i18n_key: str              # babel key
    required: bool = False
    enum_values: tuple[str, ...] | None = None   # 直接列舉（< 255 char）
    lookup_source: LookupSource | None = None    # 用 named range
    width: int = 20                   # column width

@dataclass(frozen=True)
class SheetDef:
    sheet_name: str
    description_i18n_key: str
    columns: tuple[ColumnDef, ...]

ALL_SHEETS = (
    SheetDef(
        sheet_name="01_基本資料",
        description_i18n_key="excel_template.sheet.metadata",
        columns=(
            ColumnDef("system_name", "field.system_name", required=True),
            ColumnDef("sensitivity_level", "field.sensitivity_level", required=True,
                      enum_values=("low", "moderate", "high")),
            ...
        ),
    ),
    ...  # 其他 7 個資料 sheet
)

def build_lookup_sheet(wb, source: LookupSource, values: list[str]) -> str:
    """建隱藏 lookup sheet + DefinedName，回傳 defined name 可被 DataValidation 引用"""
    sheet_name = f"_lookup_{source.value}"
    ws = wb.create_sheet(sheet_name)
    ws.sheet_state = "hidden"
    ws.cell(row=1, column=1, value=source.value)  # header
    for i, v in enumerate(values, start=2):
        ws.cell(row=i, column=1, value=v)
    
    defined_name = f"_lookup_{source.value}_range"
    end_row = len(values) + 1
    dn = DefinedName(name=defined_name, attr_text=f"'{sheet_name}'!$A$2:$A${end_row}")
    wb.defined_names[defined_name] = dn  # ⚠️ 依 Task 0.6 verify 結果調整 API
    return defined_name

class ExcelTemplateGenerator:
    def generate(self, data_bundle: TemplateDataBundle) -> BytesIO:
        wb = Workbook()
        wb.remove(wb.active)  # 移除預設 Sheet
        
        # 1. 建 lookup sheets（先建，sheet 內 DataValidation 才能引用）
        lookup_refs = {}
        for source in LookupSource:
            values = data_bundle.lookups.get(source, [])
            if values:
                lookup_refs[source] = build_lookup_sheet(wb, source, values)
        
        # 2. 00_說明 sheet
        self._build_intro_sheet(wb, data_bundle)
        
        # 3. 8 個資料 sheet
        for sheet_def in ALL_SHEETS:
            self._build_data_sheet(wb, sheet_def, data_bundle, lookup_refs)
        
        # 4. 輸出
        bio = BytesIO()
        wb.save(bio)
        bio.seek(0)
        return bio

@dataclass
class TemplateDataBundle:
    mf: ModuleFrameEntity
    profile_info: dict  # framework name / version / profile uid
    locale: str
    mode: str  # 'blank' or 'filled'
    
    # filled mode 才有
    metadata: dict | None = None
    parties_org: list[dict] = field(default_factory=list)
    parties_person: list[dict] = field(default_factory=list)
    devices: list[dict] = field(default_factory=list)
    info_systems: list[dict] = field(default_factory=list)
    leveraged: list[dict] = field(default_factory=list)
    controls_with_aos: list[dict] = field(default_factory=list)
    ref_docs: list[dict] = field(default_factory=list)
    
    # 兩 mode 都有（下拉資料源）
    lookups: dict[LookupSource, list[str]] = field(default_factory=dict)

@transaction
def generate(self, module_frame_uid, mode='blank', locale=None):
    locale = locale or get_user_context().locale
    mf = self._mf_domain_service.get_one(ModuleFrameQueryEntity(uid=module_frame_uid))
    if not mf:
        raise NotFound(GrcErrorCode.GRC_MODULE_FRAME_NOT_FOUND)
    
    bundle = TemplateDataBundle(
        mf=mf,
        profile_info=self._fetch_profile_info(mf.oscal_profile_uid),
        locale=locale,
        mode=mode,
        lookups=self._fetch_lookups(mf),  # 兩 mode 都要
    )
    if mode == 'filled':
        # Task 4 / 5 補
        self._populate_filled_data(bundle, mf)
    
    bio = self._generator.generate(bundle)
    filename = f"ssp_template_{mf.name}_{mode}_{date.today().strftime('%Y%m%d')}.xlsx"
    return bio, filename

def _populate_filled_data(self, bundle: TemplateDataBundle, mf: ModuleFrameEntity):
    # 01_基本資料
    bundle.metadata = self._fetch_mf_metadata(mf)  # ⚠️ 依 Task 0.1 verify 結果調整來源
    
    # 02_單位 / 03_參與人員
    parties = self._party_domain_service.list_by_context(
        context_type='module_frame', context_id=mf.id
    )
    bundle.parties_org = [p for p in parties if p.party_type == 'organization']
    bundle.parties_person = [p for p in parties if p.party_type == 'person']
    
    # 04_設備 / 05_資訊系統 / 06_外部利用服務（A0.1 ssp_system_implementation_items, scope=module_frame）
    main = self._system_implementation_main_domain_service.find_by_scope(
        scope_type='module_frame', scope_id=mf.id
    )
    if main:
        items = self._system_implementation_item_domain_service.list_by_main(main.id)
        bundle.devices = [i for i in items if i.implementation_type == 'hardware']
        bundle.info_systems = [i for i in items 
                                if i.implementation_type in ('component','system','subsystem','service','software')]
        bundle.leveraged = [i for i in items if i.implementation_type == 'leveraged-authorization']
    
    # 08_程序書（依 Task 0.4 verify — 預期是 MF own）
    bundle.ref_docs = self._ref_doc_domain_service.list_by_mf(mf.id)
    
    # 07_控制項與AO 留給 Task 5

def _build_data_sheet(self, wb, sheet_def, bundle, lookup_refs):
    ws = wb.create_sheet(sheet_def.sheet_name)
    
    # Header (Task 2 已建)
    self._write_header(ws, sheet_def, bundle.locale)
    
    # Filled mode: 寫資料 row
    if bundle.mode == 'filled':
        data_rows = self._get_data_for_sheet(sheet_def, bundle)
        for row_idx, row_data in enumerate(data_rows, start=2):
            for col_idx, col_def in enumerate(sheet_def.columns, start=1):
                ws.cell(row=row_idx, column=col_idx, value=row_data.get(col_def.key))
    
    # 預留空白 row 供使用者新增（blank mode 全空，filled mode 在資料 row 之後加 5-10 row）
    self._apply_data_validations(ws, sheet_def, lookup_refs, max_row=...)

def _populate_controls_with_aos(self, bundle, mf):
    # 1. 取 profile 範圍內全部 control（依 Task 0.2 verify 結果）
    profile_controls = self._profile_domain_service.list_controls_by_profile(
        profile_uid=mf.oscal_profile_uid
    )
    # profile_controls: list of ProfileControlEntity, 每個有 catalog_control_id
    
    # 2. 取每 control 的 AO list
    control_ids = [pc.catalog_control_id for pc in profile_controls]
    aos = self._ao_domain_service.list_by_control_ids(control_ids)
    aos_by_control = defaultdict(list)
    for ao in aos:
        aos_by_control[ao.catalog_control_id].append(ao)
    
    # 3. 取 MF 既有的 implementation_statement / ao_statement
    control_defaults = {
        cd.catalog_control_id: cd
        for cd in self._control_default_domain_service.list_by_mf(mf.id)
    }
    ao_defaults = {
        aod.catalog_control_objective_id: aod
        for aod in self._ao_default_domain_service.list_by_mf(mf.id)
    }
    
    # 4. 組 flat row list（父子用 statement_id / control_id 區分）
    rows = []
    for pc in profile_controls:
        # 父 row: control
        cd = control_defaults.get(pc.catalog_control_id)
        rows.append({
            "statement_id": None,  # control 父 row 留空
            "control_id": pc.control_number,  # AC-1
            "control_name": pc.control_title_i18n.get(bundle.locale, pc.control_title),
            "objective_id": None,
            "objective_name": None,
            "impl_status": cd.impl_status if cd else None,
            "statement": cd.implementation_statement if cd else None,
            "reference_doc": cd.reference_doc_name if cd else None,
        })
        # 子 row: AOs
        for ao in aos_by_control.get(pc.catalog_control_id, []):
            aod = ao_defaults.get(ao.id)
            rows.append({
                "statement_id": ao.uid,
                "control_id": pc.control_number,  # 子 row 也填 control_id 方便 A2 parser 識別父子
                "control_name": None,
                "objective_id": ao.objective_number,
                "objective_name": ao.objective_title_i18n.get(bundle.locale),
                "impl_status": aod.impl_status if aod else None,
                "statement": aod.statement if aod else None,
                "reference_doc": aod.reference_doc_name if aod else None,
            })
    
    bundle.controls_with_aos = rows

# generator 內處理 07 sheet 時：
# control row（statement_id=None）淺灰底色，AO row 留白
CONTROL_ROW_FILL = PatternFill(start_color="F2F2F2", fill_type="solid")

for row_idx, row_data in enumerate(rows, start=2):
    is_control_row = row_data.get("statement_id") is None
    for col_idx, col_def in enumerate(sheet_def.columns, start=1):
        cell = ws.cell(row=row_idx, column=col_idx, value=row_data.get(col_def.key))
        if is_control_row:
            cell.fill = CONTROL_ROW_FILL

// ModuleFrameService.js
async downloadExcelTemplate(uid, mode = 'blank') {
  const response = await this.axios.get(
    API.MODULE_FRAME_EXCEL_TEMPLATE.replace(':uid', uid),
    { params: { mode }, responseType: 'blob' }
  );
  // 從 Content-Disposition 取 filename
  const dispositionHeader = response.headers['content-disposition'] || '';
  const filenameMatch = dispositionHeader.match(/filename="?([^"]+)"?/);
  const filename = filenameMatch ? filenameMatch[1] : `template_${mode}.xlsx`;
  
  // 觸發 browser download
  const url = window.URL.createObjectURL(new Blob([response.data]));
  const link = document.createElement('a');
  link.href = url;
  link.setAttribute('download', filename);
  document.body.appendChild(link);
  link.click();
  link.remove();
  window.URL.revokeObjectURL(url);
}

# 起 BE
cd ~/Projects/Billows/Audit-Manager/compliance-manager-be
lsof -ti:8000 | xargs kill -9 2>/dev/null
set -a; source .env; set +a
nohup poetry run python main_app.py > /dev/null 2>&1 &
sleep 3

# 取 dev token (略，使用既有 helper)
TOKEN="..."
MF_UID="..."  # 一個 dev DB 已存在的 MF uid

# Blank mode
curl -H "Authorization: Bearer $TOKEN" \
     -H "X-Tenant-ID: 102" \
     "http://localhost:8000/api/1.0/module-frame/$MF_UID/ssp-import-template?mode=blank" \
     -o /tmp/template_blank.xlsx
ls -la /tmp/template_blank.xlsx

# Filled mode
curl -H "Authorization: Bearer $TOKEN" \
     "http://localhost:8000/api/1.0/module-frame/$MF_UID/ssp-import-template?mode=filled" \
     -o /tmp/template_filled.xlsx

# 用 openpyxl 驗
poetry run python3 -c "
from openpyxl import load_workbook
for fn in ['/tmp/template_blank.xlsx', '/tmp/template_filled.xlsx']:
    wb = load_workbook(fn)
    print(f'{fn}: sheets={len(wb.sheetnames)}, visible={[s for s in wb.sheetnames if not s.startswith(\"_lookup_\")]}')"

---
type: feat
breaking: false
modules: [module-frame, ssp-import-template]
commit: <main commit hash>
---

# feat: 合規資源庫 Excel 樣板下載（A1）

Phase 2 Track A 第一階段。提供 ModuleFrame 詳細頁下載 Excel 樣板（9 sheet 結構，blank / filled 兩模式）。filled 模式以 profile 範圍為基準預填 MF 既有資料（含 parties / devices / info_systems / leveraged / controls + AOs / reference docs）。

[詳細變更見 design-A1.md + implementation-plan-A1.md]