# Architecture ## Pipeline Flow `epr:process` runs as an infinite polling loop. Each cycle: ``` while(true) SourceRepository.fetchPendingRecords() ← SELECT TOP 1 ├─ DB error → log error (n/10) + sleep(8s) + retry │ └─ 10 consecutive failures → log critical + exit(1) └─ success → sleep(8s) always ├─ no record → log + next cycle └─ record found: validateRequiredFields() ← BusinessSerialNumber, NameEng, EprRegInfoId, LegalPersonFullNamePinYin, RegAddressEng, CompanyAddressLine1En, CompanyAddressPostcode, CityEngName, RegNumber, CountryTwoCode (all); AreaName (CN only); RegisteredCapital + RegisteredCapitalCurrency (FR only); VATNumber (EU only) ├─ HK + empty AreaName → defaults to '香港' after validation ├─ non-CN, non-HK + empty AreaName → defaults to strtoupper(CountryTwoCode) after validation ├─ empty required field → SourceStatusUpdater.markAsFailed(id, error) [PushTaxBureauStatus=7, Remarks=error] │ → WeChatNotificationService.sendNotification(bsn, name, error, mobile) │ → next cycle └─ all required: validateFieldLengths() ← NameEng (≤50 chars), CompanyAddressLine1En (≤100 chars) ├─ field exceeds limit → SourceStatusUpdater.markAsFailed(id, error) [PushTaxBureauStatus=7, Remarks=error] │ → WeChatNotificationService.sendNotification(bsn, name, error, mobile) │ → next cycle └─ all within limits: InseeApiClient.fetchCompanyData() ← FR only, skip if non-FR or SIREN invalid ├─ failure → SourceStatusUpdater.markAsFailed(id, "INSEE API query failed: {error}") │ → WeChatNotificationService.sendNotification(bsn, name, error, mobile) │ → next cycle └─ success: SignatureGenerator.generate() ← TTF font PNG, random from 7 fonts DocxGenerator.generate() ← includes inline signature image XlsxGenerator.generate() OssUploader.upload() × 2 TargetRepository.getLatestAttachmentIds() ← fetch prev F_Ids for epr_reg_info_id TargetRepository.createRecord() ← returns new target row id SourceStatusUpdater.markAsProcessed(id) ← PushTaxBureauStatus → 6 SourceAttachmentRepository.insertAttachmentRecords() ← returns {docx_f_id, xlsx_f_id} TargetRepository.updateAttachmentIds() ← write F_Ids back to target row SourceAttachmentRepository.deleteByFIds() ← delete prev Base_AnnexesFile rows (only if prev F_Ids exist) cleanupTempFiles() WeChatNotificationService.sendNotification(bsn, name, "File generation succeeded", mobile) → next cycle (on any exception in the above block:) SourceStatusUpdater.markAsFailed(id, "File generation failed: {error}") WeChatNotificationService.sendNotification(bsn, name, error, mobile) → next cycle ``` ## Data Model ### Source DB Tables (read-only) | Table | Key Fields | Role | |-------|-----------|------| | EPRBusinessRecord | Id, BusinessSerialNumber, CompanyId, Country | Main record | | Base_Customer_Company | NameEng, RegNumber, LegalPerson*, CompanyAddress* | Company details | | EPRRegInfo | Id, EPRBusinessRecordId, PushTaxBureauStatus, PushType, Remarks | Processing status (PushTaxBureauStatus: 5=pending, 6=files generated, 3=推送成功, 7=failure; Remarks stores error message) | | GeneralTemplateEPR | Id, VATNumber | VAT data | | Country | CountryName, CountryTwoCode | Country codes | | Base_Area | F_AreaId, F_AreaName | Area names | | Base_User | F_UserId, F_Mobile | Business counselor info (F_Mobile for WeChat @mention) | | Base_AnnexesFile | F_Id, InfoId, F_FileName, FileUrl, FileGroup, FileCategoryId | Attachment records (written and deleted by this service) | ### Target DB Table: fr_epr_mail Stores parsed Stripe/Léko invoice emails. Created by migration `2026_05_12_100000`. ### Target DB Table: fr_epr_citeo Stores parsed CITEO invoice emails. Created by migration `2026_05_14_100000`. Key columns: citeo_client_number (N° Client), invoice_number (N° FACTURE), total_due (TOTAL T.T.C.), date_due (avant le deadline), invoice_description, company_name_from_email, company_name_from_pdf, currency (EUR). Source DB lookup fields (business_serial_number, register_id, epr_reg_info_id, business_code, name_cn, counselor_mobile) populated from `lookupSourceRecord()` by `comp.NameEng`. `agency_bills_status`: 0=not executed, 1=failed, 2=success, 3=confirmed (human review via CiteoManageController). ### Source DB Table: AgencyBills Both Léko and CITEO monitors **always** insert/update a record into `AgencyBills` on `sqlsrv_source` (regardless of source record match result). Before insert, dedup check: query by `BillUser` + `PaymentEndTime` + `EmailTime` + `InstitutionName` — if existing record found, update it instead of inserting a new one. Field mapping: | AgencyBills Column | Léko Value | CITEO Value | |---|---|---| | BusinessType | 'EPR' (fixed) | 'EPR' (fixed) | | BusinessRecordID | sourceRecord.RegisterID (null if no match) | sourceRecord.RegisterID (null if no match) | | BusinessSerialNumber | sourceRecord.BusinessSerialNumber (null if no match) | sourceRecord.BusinessSerialNumber (null if no match) | | SubOrderNo | sourceRecord.SubOrderID (null if no match) | sourceRecord.SubOrderID (null if no match) | | CompanyId | sourceRecord.CompanyId (null if no match) | sourceRecord.CompanyId (null if no match) | | BillCompanyEng | companyName (from merged email/PDF) | companyName (from merged email/PDF) | | BillNumber | invoiceNumber (from PDF) | invoiceNumber (from PDF) | | BillAmount | totalDue (decimal 18,4) | totalDue (decimal 18,4) | | CurrencyCode | 'EUR' (fixed) | 'EUR' (fixed) | | InstitutionName | 'LEKO' (fixed) | 'CITEO' (fixed) | | BillType | `'法国包装法LEKO（英语版）'` or `'法国包装法LEKO（法语版）'` (detected from email subject) | '法国包装法CITEO（法语）' (fixed) | | BillUser | membership_id (from PDF OCR+AI) | citeoClientNumber (from PDF) | | BillContent | description (from PDF) | description (from PDF) | | PaymentEndTime | dateDue parsed from yyyy.mm.dd to datetime | dateDue parsed from yyyy.mm.dd to datetime | | Email | 'info@seamew.de' (fixed) | 'info@seamew.de' (fixed) | | EmailTime | mailDate (datetime from email) | mailDate (datetime from email) | | BillFile | PDF COS URL (uploaded to `leko_invoice/` directory) | PDF COS URL (uploaded to `citeo_invoice/` directory) | | EmailTitle | email subject | email subject | | state | 0=unique source match, 1=no match or multiple matches | 0=unique source match, 1=no match or multiple matches | | Remarks | null | null | | Creation_Id | 'system' (fixed) | 'system' (fixed) | | CreationName | 'system' (fixed) | 'system' (fixed) | | CreationDate | now() (datetime) | now() (datetime) | If source record lookup fails (0 or >1 matches), AgencyBills is still inserted with state=1 and source-related fields as null. Dedup: same BillUser + PaymentEndTime + EmailTime + InstitutionName → update existing record instead of insert. ### Target DB Table: fr_epr_reg | Column | Type | Source | |--------|------|--------| | id | auto | Primary key | | source_id | string(36) | vb.Id (GUID) | | epr_reg_info_id | string(36) | eri.Id (GUID) | | business_serial_number | string nullable | vb.BusinessSerialNumber | | name_eng | string nullable | bc.NameEng | | reg_address_eng | string nullable | bc.RegAddressEng | | city_eng_name | string nullable | bc.CityEngName | | legal_person_full_name_pinyin | string nullable | bc.LegalPersonFullNamePinYin | | company_address_line1_en | string nullable | bc.CompanyAddressLine1En | | company_address_line2_en | string nullable | bc.CompanyAddressLine2En | | company_address_postcode | string nullable | bc.CompanyAddressPostcode | | country_two_code | string(10) nullable | c.CountryTwoCode | | area_name | string nullable | ba.F_AreaName | | company_country | string nullable | bc.Country | | reg_number | string nullable | bc.RegNumber | | registered_capital | decimal(18,2) nullable | bc.RegisteredCapital | | registered_capital_currency | string(10) nullable | bc.RegisteredCapitalCurrency | | legal_person_gender | string(10) nullable | bc.LegalPersonGender | | legal_person_email | string nullable | bc.LegalPersonEmail | | legal_person_phone | string nullable | bc.LegalPersonPhone | | vat_number | string nullable | gt.VATNumber | | insee_legal_form | string nullable | INSEE API | | insee_naf_code | string nullable | INSEE API | | insee_siret | string nullable | INSEE API | | pdf_oss_url | string nullable | OSS upload result | | xlsx_oss_url | string nullable | OSS upload result | | pdf_file_name | string nullable | Generated filename | | xlsx_file_name | string nullable | Generated filename | | pdf_attachment_id | string(36) nullable | F_Id of PDF row in Base_AnnexesFile | | xlsx_attachment_id | string(36) nullable | F_Id of XLSX row in Base_AnnexesFile | | status | tinyint default(0) | 0=pending, 1=processing, 2=success, 3=failed | | error_message | text nullable | Failure reason | | reg_code | string nullable | 注册码 (from migration 2026_05_07) | | cert_oss_url | string nullable | 证书OSS路径 (from migration 2026_05_07) | | created_at, updated_at | timestamps | Laravel standard | ## INSEE SIRENE API Integration ### Request - URL: `https://api.insee.fr/api-sirene/3.11/siren/{siren}` - Header: `X-INSEE-Api-Key-Integration: {key}` - SIREN = first 9 digits of bc.RegNumber ### Response fields extracted 1. **categorieJuridiqueUniteLegale** → mapped via FORMES_JURIDIQUES (~170 entries) → xlsx column J 2. **activitePrincipaleUniteLegale** → dots removed → xlsx column L 3. **nicSiegeUniteLegale** → combined with siren as siret_siege → xlsx column O ### Fallback - If `CountryTwoCode` is not `FR`, INSEE is skipped entirely — all three fields default to null. - If RegNumber is missing or shorter than 9 digits, INSEE fields default to null. ## PDF Conversion Pipeline DOCX → LibreOffice → PDF → PdfNormalizer (A4 resize) → final PDF. - **LibreOfficeConverter** — wraps LibreOffice `--headless --convert-to pdf`; uses independent `UserInstallation` dir per conversion; limits concurrent instances via `flock` slot files (default 2, `LIBREOFFICE_MAX_CONCURRENT` env var) - **PdfNormalizer** — resizes to A4 portrait using Ghostscript (primary) or `storage/python/normalize_pdf.py` (PyMuPDF fallback); failure is non-fatal - **PdfConverter** — orchestrator: calls converter → normalizer with `firstPageOnly=true` (ensures single-page output even if content overflows A4) - **SoftwarePathResolver** — auto-detects LibreOffice/Ghostscript paths on first run, caches to `storage/cache/software_paths.json`; supports Windows glob patterns and PATH fallback - LibreOffice/Ghostscript paths: auto-detected by SoftwarePathResolver or set via `LIBREOFFICE_PATH`/`GHOSTSCRIPT_PATH` env vars (forward slashes for Windows .env, e.g. `"C:/Program Files/LibreOffice/program/soffice.exe"`) **Template structure**: The DOCX template must have a single body-level `` only — paragraph-level section breaks cause LibreOffice to produce multi-page PDFs with content loss. All paragraph-level `` elements were removed from the template; only the body-level one remains. Custom ZipArchive-based processor (not PHPWord TemplateProcessor). Handles: - Simple `{{placeholder}}` replacement in XML - Cross-run node splitting: `normalizeSplitPlaceholders()` collapses any `{{...}}` span that Word split across multiple `` runs (with interleaved tags/bookmarks) into a single text node before replacement; plus 4 fallback strategies when direct match fails - 4 fallback strategies when direct match fails - **Inline image embedding**: `imageReplacements` parameter maps placeholders to image file paths; adds PNG to `word/media/`, creates OOXML `` drawing element referencing a new Relationship in `word/_rels/document.xml.rels` - Returns structured result: `{success: bool, message: string, replacements: array}` ## Signature Generation (SignatureGenerator) Generates a PNG signature image using PHP GD library and TTF fonts: - **Font selection**: deterministically based on first character (A-Z → font index via LETTER_FONT_MAP; Chinese → Unicode code point mod 7) - **Style**: Normal — white background, black text, centered; dynamic canvas width (text width + HORIZONTAL_PADDING=40 on each side) × 200px, font size 60. DOCX display size fixed at 3.1cm × 1.2cm (scaled to table cell width by Word). - **Integration**: The `{{LegalPersonFullNamePinYin}}` placeholder in the DOCX template is replaced with an inline signature image (not plain text). The image is embedded directly in the "Signature" cell of the bottom table. - **Cleanup**: Temp PNG file is deleted after DOCX generation completes ## XLSX Generation PhpSpreadsheet loads template, fills from row 14. **Memory management**: XlsxGenerator temporarily raises `memory_limit` to 512M during generation (PhpSpreadsheet's SimpleCache3 can exceed 128MB with 128M default), then restores original limit and calls `disconnectWorksheets()` to release memory. Column formatting rules: - **D (City)**: Chinese characters → pinyin via `overtrue/pinyin` (杭州 → hang zhou); English → unchanged - **E (Country)**: country two-letter code - **F (Province/State)**: AreaName required only for CN; Chinese AreaName → as-is (中国省份、香港、澳门); non-Chinese non-empty AreaName → as-is; non-CN non-HK empty AreaName → strtoupper(CountryTwoCode); HK empty AreaName → '香港' - **I (Turnover)**: `< €20M` - **N (Capital)**: France → `{capital} {currency}` with French decimal comma (2.5 → 2,5); non-France → empty - **U/AA (Phone)**: hyphen replaced with space (86-18727624967 → 86 18727624967) - **AG (Household waste forecast)**: random integer 4000–6000 - **AJ (0-5t tonnage)**: random float 0.1–5.0 with one decimal, French comma format, no "t" suffix Fixed values: ProducerType=PMH marketplace seller, Sector=Other, Position=CEO, Language=EN, CN_CODE=95, FR_COUNTRY_CODE=39, FullPOA=Yes, PreviousAffiliation=No ## Mail Invoice Monitor Pipeline `epr:leko-monitor` runs as an infinite polling loop, same pattern as `epr:process`: ``` while(true) connectImap() ← imap.qiye.aliyun.com:993/ssl ├─ connection fail → log error + sleep(8s) + retry └─ success: imap_search(UNSEEN FROM stripe) ← invoice+statements+acct_1Jb54xFUqOeYBTlr@stripe.com ├─ no unread emails → log + close + sleep(8s) └─ for each unread email: parseSubject() ← EN: #F2026-72508 / FR: nº F2026-70764 parseBody() ← TO/À, Invoice#/Facture nº, Total due/Montant total dû, Membership ID, description extractPdfAttachment() ← BaiduOcrClient → OCR text → ArkModelClient::extractInvoiceFields() → upload PDF to COS (leko_invoice/) ↳ retry up to 3 times (3s interval) on OCR/AI failure lookupSourceRecord() ← query source DB by comp.NameEng (LIKE wildcard, SupplierName='LEKO') saveAgencyBill() ← always insert/update into AgencyBills on sqlsrv_source (regardless of match result), InstitutionName='LEKO', BillType detected by email language updateEprRegInfo() ← InvoiceId + RegReceiptNumber (only on unique match) saveMailRecord() ← insert into fr_epr_mail markEmailRead() ← imap_setflag_full(\Seen) DB error → log error (n/10) + sleep(8s) 10 consecutive DB errors → log critical + exit(1) ``` ### Services - **MailInvoiceParser** — parses email subject (EN/FR regex) and body (strip HTML, extract fields via regex) - **MailInvoiceMonitor** — IMAP connection, search, fetch, parse, PDF OCR+AI, COS upload, fuzzy source lookup, AgencyBills insert, DB update, save, mark read - **BaiduOcrClient** — PDF OCR via Baidu general_basic API (client_id+client_secret → access_token → pdf_file base64). Returns raw OCR text from PDF attachment. - **ArkModelClient** — extracts invoice fields from OCR text via Volcano Doubao model (doubao-seed-1-6-251015). Returns membership_id, date_due, currency, and cross-checks other parsed fields. - **OssUploader** — shared service, uploads Léko PDF to COS `leko_invoice/` directory (AgencyBills.BillFile) ### OCR + AI Pipeline For each email with a PDF attachment: ``` PDF attachment → BaiduOcrClient.recognizePdf() → raw OCR text → ArkModelClient.extractInvoiceFields(ocrText) → {membership_id, date_due, currency} → merge with email-parsed fields → complete record ``` **Retry**: OCR + AI extraction retries up to 3 times with 3-second intervals on failure. Each attempt is logged (attempt N/3). After all retries exhausted, logs error and falls back to partial data. If PDF download/OCR/AI fails after all retries, email-parsed fields are still saved (date_due and membership_id will be null). Email is still marked as read. ### Email Parsing Patterns | Field | English | French | |-------|---------|--------| | Subject | `New invoice from Léko #F2026-72508` | `Nouvelle facture nº F2026-70764 de Léko` | | Company | `TO: Company Name` | `À : Company Name` | | Invoice# | `Invoice#: F2026-72508` | `Facture nº : F2026-72508` | | Total due | `Total due: €150.00` | `Montant total dû : 150,00 €` | | Description | `2026 Annual Contribution Provision - Flat Rate` | `Provision pour la Contribution annuelle 2026 - Forfait` | | Membership | `Membership ID: LK-12345` | `Membership ID: LK-12345` | ### Source DB Lookup ```sql SELECT br.SubOrderID, br.BusinessSerialNumber, br.ID AS RegisterID, reg.ID AS EPRRegInfoID, br.BusinessCode, comp.NameEng, comp.NameCN, usr.F_Mobile, br.CompanyId FROM EPRBusinessRecord br LEFT JOIN EPRRegInfo reg ON reg.EPRBusinessRecordId = br.ID LEFT JOIN Base_Customer_Company comp ON comp.ID = br.CompanyId LEFT JOIN RecyclingType recyclingType ON br.RecyclingTypeId = recyclingType.ID LEFT JOIN SupplierInformation recycleMerchant ON br.OfficialFeeRecycleMerchant = recycleMerchant.ID LEFT JOIN ServiceItems svc ON svc.ID = br.ServiceItemId LEFT JOIN Base_User usr ON usr.F_UserId = br.BusinessCounselorId WHERE br.Country='FR' AND comp.NameEng LIKE :companyEng AND recyclingType.TypeName='包装法' AND recycleMerchant.SupplierName='LEKO' AND svc.ServiceItemName='包装法注册' AND br.Status !=5 ``` ## CITEO Invoice Mail Monitor Pipeline `epr:citeo-monitor` can run as a separate daemon or as part of the **combined** `epr:mail-monitor` daemon (recommended). CITEO emails sit in the `法国包装法CITEO账单` folder (auto-archived by mailbox rules). When run standalone, each daemon creates its own IMAP connection. The combined daemon connects once per cycle and processes both folders sequentially. ``` while(true) connectImap() ← same imap.qiye.aliyun.com:993/ssl ├─ connection fail → log error + sleep(8s) + retry └─ success: imap_search(UNSEEN folder) ← 法国包装法CITEO账单 folder PHP filter: sender = adv.emballages@espaceclients.citeo.com + subject contains "facture" + "Citeo" ├─ no matching emails → log + close + sleep(8s) └─ for each filtered email: dedupCheck(content_md5) ← fr_epr_citeo.content_md5 CiteoInvoiceParser.parseBody() ← extract company_name, invoice_number, client_number from email extractPdfAttachment() ← BaiduOcrClient (first page only) → OCR text → upload PDF to COS (citeo_invoice/) ↳ retry up to 3 times (3s interval) on OCR/AI failure ArkModelClient.extractCiteoInvoiceFields() ← CITEO-specific AI prompt → company_name, citeo_client_number, invoice_number, total_due, date_due, description, currency mergeFields() ← prefer PDF/AI fields over email-parsed fields crossCheck() ← compare company_name_from_email vs company_name_from_pdf lookupSourceRecord() ← query source DB by comp.NameEng (+ br.SubOrderID, br.CompanyId) saveAgencyBill() ← always insert/update into AgencyBills on sqlsrv_source (regardless of match result), with EmailTitle + state fields; dedup check by BillUser + PaymentEndTime + EmailTime saveCiteoRecord() ← insert into fr_epr_citeo (with source fields + agency_bills_status) markEmailRead() ← imap_setflag_full(\Seen) DB error → log error (n/10) + sleep(8s) 10 consecutive DB errors → log critical + exit(1) ``` ### CITEO Invoice PDF Fields | Field | Label in PDF | Example | |-------|-------------|---------| | Client number | N° Client | 50053662 | | Invoice number | N° FACTURE | 91044977 | | Company name | (after N° TVA Intra-com line) | TRIPLE EIGHT DISTRIBUTION LLC | | Total due | TOTAL T.T.C. | 110.00 | | Date due | avant le dd.mm.yyyy | 30.06.2026 → stored as 2026.06.30 | | Description | Détail de la facturation | Contribution annuelle 2026 au titre des emballages ménagers | | Currency | (implicit, always EUR for France) | EUR | ### CITEO Services - **CiteoMailMonitor** — orchestrator (IMAP, filter, parse, OCR, AI, source lookup, AgencyBills insert, COS upload, save, mark read) - **CiteoInvoiceParser** — email body parsing (N° Client, N° FACTURE, company name regex) - **BaiduOcrClient** — shared service, same as Léko monitor (first page only, pdf_file_num=1) - **ArkModelClient::extractCiteoInvoiceFields()** — CITEO-specific AI prompt for field extraction - **OssUploader** — shared service, uploads CITEO PDF to COS `citeo_invoice/` directory ## Combined Mail Monitor (epr:mail-monitor) `epr:mail-monitor` is the **recommended** way to run both mail monitors. It uses a single IMAP connection per cycle to avoid the Windows SSL issues that occur when two separate PHP processes connect to the same IMAP server simultaneously. ``` CombinedMailMonitor::runOneCycle() → BaseMailMonitor::connectImap() ← one IMAP connection (TCP→SSL→banner pre-check) → MailInvoiceMonitor::processFolder($client) ← 法国包装法Léko账单 (independent try/catch) → CiteoMailMonitor::processFolder($client) ← 法国包装法CITEO账单 (independent try/catch) → BaseMailMonitor::disconnectImap($client) ← disconnect + gc + environment restore ``` **Class hierarchy**: `BaseMailMonitor` (abstract) → `MailInvoiceMonitor` (Léko) / `CiteoMailMonitor` (CITEO). `CombinedMailMonitor` is a standalone orchestrator that injects both sub-monitors and calls their `processFolder()` with a shared client. Each sub-monitor handles its own filtering and per-email processing. **IMAP protocol layer**: `BaseMailMonitor` uses `SafeImapClient` (extends webklex `Client`) which instantiates `SafeLegacyProtocol` (extends webklex `LegacyProtocol`) when the `legacy-imap` protocol is configured. `SafeLegacyProtocol::examineFolder()` guards against partial IMAP STATUS responses (some servers omit RECENT/MESSAGES) by defaulting missing properties to 0 via null coalescence, preventing crashes during folder queries. **Non-ASCII folder lookup**: `BaseMailMonitor::findFolder()` compensates for a legacy-imap UTF7-IMAP double-decode bug: PHP's `imap_getmailboxes()` already returns UTF-8 decoded names, but webklex's `LegacyProtocol::decodeFolderName()` applies a spurious `mb_convert_encoding(UTF7-IMAP→UTF-8)` pass, corrupting non-ASCII characters. `findFolder()` matches by applying the same corruption to the lookup name, then restores the raw Modified UTF-7 path from `imap_getmailboxes()` via `buildRawPathMap()`. **Error isolation**: Each folder's `processFolder()` runs in an independent try/catch block — a Léko processing failure does not prevent CITEO from processing on the same connection. ## CITEO Management UI Human-review endpoints for CITEO invoice records (the `fr_epr_citeo` table), served under `internal.network` (172.16.x.x). Distinct from the automation APIs: `index` returns an HTML view, not the `{code,msg,data}` JSON envelope. - `GET /fr_epr_reg/api/epr/citeo?status=0|3` — paginated HTML view (`epr_reg.citeo_manage`) of CITEO records filtered by `agency_bills_status` (`0`=not executed, `3`=confirmed; omit/other shows both 0 and 3). Used by operations to review parsed CITEO invoices before confirming them. - `POST /fr_epr_reg/api/epr/citeo/confirm` — marks a single `fr_epr_citeo` record as confirmed (`agency_bills_status` -> 3). Body: `{id}`. Returns `{success: bool, msg}`. Idempotent: re-confirming returns `{success: false, msg: "already confirmed"}`. Controller: `CiteoManageController`. These are the only non-JSON endpoints in the `/api/epr/*` group. ## French LEKO/CITEO Unified File Generation API POST `/fr_epr_reg/api/epr/fr/file-generation` — unified synchronous LEKO/CITEO file generation for the SaaS relay (endpoint spec: [docs/french-file-generation-api.md](french-file-generation-api.md); full contract: `E:\ou\meiou-app\app_withdrawn\docs\UNIFIED_API_DESIGN.md` §4.4.21（LEKO）/ §4.4.7（CITEO）). The **API_Flow** consumes `Data`/`bizParam` directly and performs **zero reads/writes on the source DB** (no `SourceRepository`/`SourceStatusUpdater`/`SourceAttachmentRepository`/`TargetRepository`); the existing polled **Source_Flow** (`EprRegProcessor`) is unchanged. - **Middleware**: `fr.api.transport` (POST + `application/json` only, body size `FR_FILE_API_MAX_BODY_BYTES`; 405/400), `fr.api.bearer` (optional Bearer via `FR_FILE_API_AUTH_ENABLED`/`FR_FILE_API_AUTH_TOKEN`; 401), `fr.api.rate` (per-IP limit `FR_FILE_API_RATE_LIMIT_PER_MINUTE`; 429). Not `internal.network` (the SaaS relay calls from its own host). - **Envelope**: `{PushType, Country, Data, bizParam}`; PushType whitelist `FR_EPR_REGISTER_LEKO_FILE` / `FR_EPR_REGISTER_CITEO_FILE` (exact dispatch, no dynamic method calls); `Country` must match the `FR` prefix. - **Validation** (`FrenchFileGenerationValidator`): LEKO fixed 12 Data fields (name + surname pinyin split) + conditional `AreaName`(CN) / `RegisteredCapital`+`RegisteredCapitalCurrency`(FR) / `VATNumber`(EU) + `bizParam.BusinessSerialNumber` + 50/100 char limits; CITEO fixed 6 fields + `VATNumber`(EU except FR) or `RegNumber`(others). `InseeLegalForm`/`InseeNafCode`/`InseeSiret`/`CompanyAddressLine2En` are not part of the contract. All errors aggregated in one 400. PushType whitelist is restricted to file-generation types (`FrenchPushType::isFileGeneration()`), so `FR_EPR_REGISTER_LEKO_FILE_MERGE`/`FR_EPR_REGISTER_LEKO` are rejected here (they belong to the merge/registration endpoints). - **Mapping** (`FrenchFileGenerationMapper`): trim, uppercase `CountryTwoCode`, HK→`香港`, non-CN/HK empty `AreaName`→uppercase country code, array values comma-joined; drops the three Insee* fields and `CompanyAddressLine2En`; `bizParam` kept verbatim for passthrough. - **LEKO branch** (`FrenchFileGenerationService` → `LekoInseeResolver` → `LekoApiFileGenerator`): France-company only — SIREN = first 9 digits of `RegNumber`; exactly 9 → `InseeApiClient` (SIRENE 3.11, 5 attempts / 6 s, same decision + mapping + defaults as `EprRegProcessor::fetchInseeData`); final failure → `INSEE_QUERY_FAILED` 400, no files, no source fallback. Non-FR / invalid SIREN → empty INSEE data (XLSX J/L/O defaults `Autre / Other`/`''`/`''`). Files: `Leko_Template.xlsx` → XLSX (J/L/O read only server-side `_insee`) and `Leko_Template.docx` → POA (signature PNG) → PDF (`firstPageOnly=true`) → OSS **API bucket** `{OSS_API_PREFIX}{year}/{OSS_API_MODULE_DIR}/leko/{BusinessSerialNumber}/` (`POA-{sanitized NameEng}.pdf` + `Leko_Template list of companies_v3.11 2026.xlsx`). - **CITEO branch** (`FrenchFileGenerationService` → `CiteoPoaGenerator`): no INSEE. Minimal fields + license no. branch (EU except FR → `VATNumber`, else `RegNumber`) → `Citeo_Template.docx` → PDF (`firstPageOnly=false`, all pages) → OSS **API bucket** `{OSS_API_PREFIX}{year}/{OSS_API_MODULE_DIR}/citeo/{segment}/POA-{sanitized NameEng}.pdf` (`segment` = `bizParam.BusinessSerialNumber`, falling back to `{sanitized NameEng}-{uniqid}`). The legacy `CiteoPoaService` (Id-based) now delegates to `CiteoPoaGenerator`; its route/response unchanged. The `usaeu` API bucket is **private** — uploads keep objects private (`visibility=private`; no `ACL:public-read`), consumers sign on access (same posture as es_haiya_epr). - **Response**: `{code:200, msg:success, ProcessMode:sync, data:{files:[{url,name,type[,date]}]}, bizParam}` (UNIFIED_API_DESIGN §5.1) — LEKO files `[XLSX (EPR业务申请表), POA (授权书)]`, CITEO `[POA (授权书)]`; `url` is an **OSS relative path** (no domain - the SaaS side prepends it); an absolute URL in `url` is rejected as an internal error (400). Empty list is `[]`, never `null`. Errors: validation/INSEE/template/DOCX/XLSX/PDF/OSS → categorized 400 (`INSEE_QUERY_FAILED` / `TEMPLATE_MISSING` / `DOCX_GENERATION_FAILED` / `XLSX_GENERATION_FAILED` / `PDF_CONVERSION_FAILED` / `OSS_UPLOAD_FAILED`), unclassified → fixed `Internal error` 500. - **Logging**: `api_french` channel; `SensitiveDataRedactor` masks bearer/INSEE key/RegNumber/SIREN/SIRET/VAT/phone/email/address/URL in logs. Shared components' logs (INSEE siren/siret/address, DOCX replacements) are redacted too. - **Test data samples**: `法国LEKO注册文件.json`, `法国CITEO注册文件.json` in `E:\ou\meiou-app\app_withdrawn\docs\` (unified with the new-system docs; identical to the UNIFIED_API_DESIGN.md §4.4.21/§4.4.7 examples; both pass the production validator - see `FrenchApiDocContractTest`). - **Full spec**: `E:\ou\meiou-app\app_withdrawn\docs\UNIFIED_API_DESIGN.md` §4.4.21（LEKO）/ §4.4.7（CITEO）; endpoint-level spec: [docs/french-file-generation-api.md](french-file-generation-api.md). ## French LEKO Merge XLSX API (new system) POST `/fr_epr_reg/api/epr/fr/merge-xlsx` — merges 2–50 generated LEKO company XLSX into one company list for the SaaS relay (endpoint spec: [docs/french-merge-xlsx-api.md](french-merge-xlsx-api.md); contract §4.4.22). **API_Flow: zero DB access.** - **Middleware**: same three guards as file-generation (`fr.api.transport` / `fr.api.bearer` / `fr.api.rate`; the three `/api/epr/fr/*` endpoints **share one per-IP quota**). Not `internal.network`. - **Envelope**: `{PushType: FR_EPR_REGISTER_LEKO_FILE_MERGE, Country: FR, Data:{files:[{url,name,type}]}, bizParam}`; `Data.files` = 2–50 API-bucket OSS **relative paths** (pipe the `data.files[].url` from file-generation straight back in); `type` optional but must be `EPR业务申请表` when present; `bizParam` optional (echoed verbatim). - **OSS key validation** (`FrenchApiOssKeyValidator`): relative path only (rejects absolute URL / leading `/` / `\` / control chars), no `.`/`..` segments (incl. `%2e%2e`), must match `{OSS_API_PREFIX}{4-digit year}/{OSS_API_MODULE_DIR}/`, must not point to the reserved `merged/` directory (merged outputs feed registration-mail, not re-merging), object must exist; per-file cap `FR_FILE_API_OSS_MAX_FILE_BYTES` (20 MB), total cap `FR_FILE_API_OSS_MAX_TOTAL_BYTES` (200 MB). Key-format violations surface as aggregated validation errors (400 `Validation failed: …`); `OSS_KEY_NOT_FOUND` / `OSS_FILE_TOO_LARGE` / `OSS_DOWNLOAD_FAILED` remain categorized 400. - **Flow** (`FrenchMergeService`): validate → download each key to a temp file via `readStream()` → `XlsxRowMerger::merge()` (same row semantics as the legacy API: first file as base keeping rows 1–13, row 14 of each next file appended, data rows set to `FORMAT_TEXT`) → upload to API bucket `.../merged/merged_{Ymd_His}_{8 random}.xlsx` → single-entry `data.files` response; temp files cleaned in `finally`. - **Response**: `{code:200, msg:success, ProcessMode:sync, data:{files:[{url,name,type}]}, bizParam}` (`url` = relative path to the merged file). Errors: categorized 400 (`XLSX_MERGE_FAILED`, `OSS_UPLOAD_FAILED`, …), unclassified → fixed `Internal error` 500. - **Logging**: `api_french` channel. - **Full spec**: `E:\ou\meiou-app\app_withdrawn\docs\UNIFIED_API_DESIGN.md` §4.4.22; endpoint-level spec: [docs/french-merge-xlsx-api.md](french-merge-xlsx-api.md). ## French LEKO Registration Mail API (new system) POST `/fr_epr_reg/api/epr/fr/registration-mail` — takes the merged XLSX + POA PDFs by OSS key, builds `POA.zip`, verifies row/PDF counts, then emails the registration dossier to Léko (endpoint spec: [docs/french-registration-mail-api.md](french-registration-mail-api.md); contract §4.4.23). **API_Flow: zero source-DB access (no source status write); after the mail is sent it persists one LEKO UIN task row per company into the target DB (`fr_epr_reg`, `data_source='api'`, idempotent by company BSN parsed from the POA key `leko/{BSN}/`).** - **Middleware**: same three guards as above (shared per-IP quota). - **Envelope**: `{PushType: FR_EPR_REGISTER_LEKO, Country: FR, Data:{files:[…]}, bizParam}`; `Data.files` = exactly one `EPR业务申请表` (the merged XLSX) + 1–100 `授权书` (POA PDFs); `type` required; all keys validated as above. - **Flow** (`FrenchRegistrationMailService`): validate → download XLSX + PDFs (streamed to temp files) → count non-empty XLSX rows from row 14 (`LekoRegistrationMailer::countXlsxDataRows`) and require equality with the POA count (else 400 `COUNT_MISMATCH`, **no mail**) → build `POA.zip` (entry names = `FileNameSanitizer::sanitize(basename(name))` + forced `.pdf`, duplicates suffixed `-{index}`) → `LekoRegistrationMailer::send()` (fixed attachments `Leko_Template list of companies_v3.11 {year}.xlsx` + `POA.zip`, recipient `SMTP_MAIL_TO`). Temp files cleaned in `finally`. - **Response**: `{code:200, msg:success, ProcessMode:sync, data:{files:[], sent_to, client_count}, bizParam}`. Errors: categorized 400 (`COUNT_MISMATCH`, `MAIL_SEND_FAILED`, `OSS_*`), unclassified → fixed `Internal error` 500. - **Out of scope**: the UIN (下号) step after registration is a separate RPA flow — API-flow tasks are consumed via the `leko-rpa` endpoints below. - **Logging**: `api_french` channel. - **Full spec**: `E:\ou\meiou-app\app_withdrawn\docs\UNIFIED_API_DESIGN.md` §4.4.23; endpoint-level spec: [docs/french-registration-mail-api.md](french-registration-mail-api.md). ## Léko UIN RPA API (new system, API flow only) `POST /fr_epr_reg/api/epr/leko-rpa/claim` + `/result` — internal (`internal.network`) endpoints for the **API-flow-specific RPA** (endpoint spec: [docs/french-rpa-leko-api.md](french-rpa-leko-api.md)). The source-flow RPA is untouched: it still claims `EPRRegInfo.PushTaxBureauStatus=3` from the source DB and writes `BaseAnnexesFile` + `RegBackNumber` + status 4 itself. - **任务来源**: registration-mail persists task rows (`data_source='api' AND status=2`); claim only ever returns those rows — **the two queues never overlap**. - **State machine** (`fr_epr_reg.rpa_status`): 0 pending → 1 claimed (atomic single-statement `UPDATE TOP(1) … OUTPUT`, `rpa_attempts+1`, `rpa_claimed_at` lease; stale claims re-claimable after `FR_RPA_CLAIM_STALE_MINUTES`) → 2 done (`reg_code`/`cert_oss_url`) / 3 failed (result `failed`, or attempts exhausted → failure notification). - **claim response**: source-flow-aligned fields (`BusinessSerialNumber`, `NameEng`) plus `xlsx`/`pdf` entries carrying the OSS key + name + a **server-signed temporary download URL** (10 min, GET-only — RPA needs no COS credentials) and the echoed `bizParam`. - **result**: `{task_id, status, reg_code?, cert_key?, cert_name?, error_message?}`; `cert_key` must sit under this task's `leko/{BusinessSerialNumber}/` directory; idempotent for completed tasks (no duplicate notify). - **SaaS notification** (§13 `delivery/rpa/callback`, sent by this service, not the RPA): URL = `bizParam.callback_url`/`callbackUrl` → `FR_RPA_DELIVERY_CALLBACK_URL`; retries ≤3 with 2s/6s backoff (2xx ok, plain 4xx no-retry); success payload `receiptType=ISSUED_INFO`, `uin`, `files[{url,name,type=UIN_CERTIFICATE_FILE}]`; failure `code=500, data=null`. Delivery recorded in `notify_*` columns. - **RPA scripts**: `python/rpa_leko_api_step{1_get,2_upload_oss,3_save}.py` (self-contained, mirroring the source-flow three steps; step2 uploads the certificate to `usaeu` under `{OSS_API_PREFIX}{year}/fr_epr_reg/leko/{BSN}/` and returns the relative key). - **Logging**: `api_leko_rpa` channel. ## Léko RPA Scripts (`python/`) — 下号 / 证书 `python/` 存放 Léko 注册之后的 RPA 辅助脚本（Python；不属本服务运行时，由 RPA 流程/调度按需调用）。 | 脚本 | 作用 | |---|---| | `rpa_leko_step1_get.py` | 取一条待下号任务（`EPRRegInfo.PushTaxBureauStatus=3 AND PushType='301' AND RecyclingType.TypeName='包装法' AND ServiceItemName='包装法注册' AND SupplierInformation.SupplierName='LEKO' AND EPRBusinessRecord.Status=3`，按 `ModificationDate` 升序 TOP 1），并立即把 `ModificationDate` 更新为当前时间（租约，防并发重复处理） | | `rpa_leko_step1_search_error_sendmsg.py` | 「查询到多家公司」异常时发企业微信群消息（webhook 地址为脚本内常量） | | `rpa_leko_step1_search_error_update_reginfo.py` | 错单回写：更新 `EPRRegInfo.PushTaxBureauStatus` + `Remarks` | | `rpa_leko_step2_upload_oss.py` | 门户下号产生的证书 PDF 上传腾讯云 COS（SecretId/SecretKey/region/桶/key 前缀全部由调用方传入，脚本内无凭证） | | `rpa_leko_step3_save.py` | 下号成功落库：向 `Base_AnnexesFile` 插入证书附件记录，并更新 `EPRRegInfo.RegBackNumber`（注册码）+ `PushTaxBureauStatus=4` | | `rpa_leko_step3_save_sucess_sendmsg.py` | 下号成功后的企业微信通知 | | `rpa_leko_api_step1_get.py` | **API 流程专用**：调 `/api/epr/leko-rpa/claim` 认领任务（只取 API 流程注册成功的数据） | | `rpa_leko_api_step1_search_error_sendmsg.py` | **API 流程专用**：门户查询检测到多家公司时企业微信告警（与 source 流程同文案） | | `rpa_leko_api_step1_search_error_update_task.py` | **API 流程专用**：查询异常回传（`/result` failed → 服务端置 `rpa_status=3` + SaaS 失败通知） | | `rpa_leko_api_step2_upload_oss.py` | **API 流程专用**：证书上传 `usaeu` 桶 `{prefix}{年}/fr_epr_reg/leko/{BSN}/`，返回相对 key（服务端签名取件，RPA 不持 COS 凭证） | | `rpa_leko_api_step3_save.py` | **API 流程专用**：调 `/api/epr/leko-rpa/result` 回执（服务端落库并调用 SaaS delivery 异步通知） | - **状态语义补充**：source 侧 `PushTaxBureauStatus=4` = **下号完成**（RPA 回写 `RegBackNumber` 之后）。 - **与 API 流程的关系**：`/api/epr/fr/*` 家族不回写 `PushTaxBureauStatus`（状态由 SaaS 侧维护），上述 `rpa_leko_step*.py` 只服务 source 流程（按 `PushTaxBureauStatus=3` 取任务）。**API 流程使用独立的 `rpa_leko_api_step*.py` + `leko-rpa` 接口**（见上节），两套 RPA 零交集、互不影响。 - 依赖：`pymssql`、`cos-python-sdk-v5`、`requests`；数据库连接参数（host/user/password/database/port）均为函数入参，脚本内默认值仅为占位（`localhost` / `sa` / `password`），实际由调用方注入。 - 注：`rpa_leko_step1_search_error_sendmsg.py` 内置企业微信 webhook key（按用户决定原样入库）；如判定有泄露风险应轮换该 key。 ## Merge XLSX API POST `/fr_epr_reg/api/epr/merge-xlsx` — merges multiple XLSX attachments into one file. - **Middleware**: `internal.network` (172.16.x.x subnet only) - **Controller**: `XlsxMergeController` → `XlsxMergeService` - **Request validation**: `MergeXlsxRequest` — requires `AttachmentIDs` array with at least 2 UUID strings (max 36 chars each) - **Flow**: fetch `Base_AnnexesFile` metadata by F_Id → validate all exist and are xlsx type → download from COS → first file as base (keep rows 1-13) → extract row 14 (A-AJ) from each subsequent file → write merged file → upload to COS → return oss_url. Row merging runs in the shared `XlsxRowMerger` (also used by the new-system `/api/epr/fr/merge-xlsx`). - **Response**: `{code: 200, msg: "Merge succeeded", data: {oss_url: "..."}}` or `{code: 400, msg: error, data: null}` - **Full API spec**: see `docs/merge-xlsx-api.md` ## Send Registration Mail API POST `/fr_epr_reg/api/epr/send-registration-mail` — sends merged XLSX + PDF ZIP to Léko via SMTP, updates source DB PushTaxBureauStatus to 3 (推送成功). - **Middleware**: `internal.network` (172.16.x.x subnet) + `parse.large.json` (handles Base64 line breaks in large JSON) - **Controller**: `RegistrationMailController` → `RegistrationMailService` - **Request validation**: `SendRegistrationMailRequest` — requires `xlsx_url` (valid URL), `pdf_zip_base64` (string, max 67MB), `epr_reg_info_ids` (1–100 UUID strings) - **Validation**: XLSX data row count = PDF file count = `epr_reg_info_ids` count (three-way consistency check) - **Flow**: download XLSX → decode ZIP → count XLSX rows + PDF files + IDs → validate counts match → send email via PHPMailer (Aliyun SMTP SSL) → update source DB PushTaxBureauStatus=3 → cleanup. SMTP send + XLSX row counting are delegated to the shared `LekoRegistrationMailer` (also used by the new-system `/api/epr/fr/registration-mail`, which does **not** write source status). - **Email**: Subject: `Register with Léko {year} for {count} clients--Sea&Mew Consulting GmbH--{date}`; Body: HTML with paragraph formatting; Attachments: XLSX + ZIP - **Response**: `{code: 200, msg: "Registration mail sent successfully", data: {sent_to, client_count}}` or `{code: 400, msg: error}` - **Full API spec**: see `docs/send-registration-mail-api.md` ## CITEO POA Generation API POST `/fr_epr_reg/api/epr/generate-citeo-poa` - generates a CITEO power-of-attorney PDF on demand from an EPRRegInfo ID (unlike LEKO POA, which the `epr:process` polling loop generates). - **Middleware**: `internal.network` (172.16.x.x subnet only); no `parse.large.json` (small request body) - **Controller**: `CiteoPoaController` -> `CiteoPoaService` - **Request validation**: `GenerateCiteoPoaRequest` - requires `Id` (EPRRegInfo UUID, max 36 chars). All company data is fetched server-side from the source DB by this ID. - **Flow**: `SourceRepository::fetchByEprRegInfoId()` -> validate required fields + business license no. (EU-except-France -> VATNumber, else -> RegNumber) -> `WordTemplateProcessor` fills `storage/Citeo_Template.docx` (7 placeholders / 9 usages; signature as inline PNG via SignatureGenerator) -> `PdfConverter::convertDocxToPdf($path, false)` (`firstPageOnly=false`; CITEO POA is 2 pages, signature on page 2) -> `OssUploader` uploads to COS `citeo_poa/{sanitized NameEng}/{uniqid}/POA-{sanitized NameEng}.pdf` -> return `{pdf_url}` - **Filename**: `POA-{FileNameSanitizer::sanitize(NameEng)}.pdf` — company name sanitized like LEKO (see Conventions); returned `pdf_url` has its path decoded by `OssUploader`, so `&`, spaces and non-ASCII appear readable in the URL - **Response**: `{code: 200, msg: "CITEO POA generated successfully", data: {pdf_url}}` or `{code: 400, msg: error, data: null}` - **Full API spec**: see `docs/citeo-poa-api.md` ## Refashion POA Generation API POST `/fr_epr_reg/api/epr/generate-refashion-poa` - generates a Refashion (French textile law / ECO TLC) power-of-attorney PDF on demand from an EPRRegInfo ID. Same on-demand pattern as CITEO POA (not polled). - **Middleware**: `internal.network` (172.16.x.x subnet only); no `parse.large.json` (small request body) - **Controller**: `RefashionPoaController` -> `RefashionPoaService` - **Request validation**: `GenerateRefashionPoaRequest` - requires `Id` (EPRRegInfo UUID, max 36 chars). All company data is fetched server-side from the source DB by this ID. - **ECO TLC scoping**: `SourceRepository::fetchEcoTlcRecordByEprRegInfoId()` extends `fetchByEprRegInfoId()` with `LEFT JOIN SupplierInformation` and `WHERE si.SupplierName = 'ECO TLC'`, so only textile-law records can generate a Refashion POA (prevents packaging LEKO/CITEO records being mis-processed). Record not found / not ECO TLC -> 400 (single null result, no leak of which). The same query also `LEFT JOIN`s `Base_Customer` (on `vb.CustomerId`) -> `Base_User` (on `SaleUserID`) to return `CustomerManagerName` (`F_RealName`) and `CustomerManagerEmail` (`F_Email`) for the post-generation notification (currently disabled; fields retained but unused); these joins exist only in this Refashion-scoped query (CITEO/LEKO queries unchanged). - **Flow**: `fetchEcoTlcRecordByEprRegInfoId()` -> validate required fields (NameEng, CountryTwoCode, CityEngName, RegAddressEng, LegalPersonFullNamePinYin, **LegalPersonPhone, LegalPersonEmail** - last two are Refashion-specific) + business license no. (EU-except-France -> VATNumber, else -> RegNumber) -> `WordTemplateProcessor` fills `storage/Refashion_Template.docx` (8 placeholders / 13 usages, **text-only, no signature image**; `{{LegalPersonPhone}}`/`{{LegalPersonEmail}}` are split across XML runs and collapsed by `WordTemplateProcessor::normalizeSplitPlaceholders()` into a single text node before direct replacement) -> `PdfConverter::convertDocxToPdf($path, false)` (`firstPageOnly=false`; Refashion POA is multi-page) -> `OssUploader` uploads to COS `refashion_poa/{sanitized NameEng}/{uniqid}/POA-{sanitized NameEng}.pdf` -> return `{pdf_url}` (post-generation customer-manager notification is currently disabled; the `RefashionPoaNotifier::notify()` call is commented out in `RefashionPoaService::generate()`) - **No signature image**: the Signature and Stamp cells are left empty in the template (manual wet-signing / 盖章 later); no `SignatureGenerator` dependency. The floating Sea&Mew logo (`image1.png`) in the Date row stays unchanged. - **Filename**: `POA-{FileNameSanitizer::sanitize(NameEng)}.pdf` — sanitized like CITEO/LEKO (see Conventions); returned `pdf_url` path is decoded readable - **Post-generation notification**: currently **disabled** - `RefashionPoaService::generate()` no longer calls `RefashionPoaNotifier` (the call is commented out), so the API returns only `{pdf_url}` (no `notification` field). The `RefashionPoaNotifier` class is retained for re-enablement; when active it best-effort emails the customer manager a PDF attachment (or WeChat @18381322048 fallback on empty/failed email) via `REFASHION_SMTP_*` (`fr-epr@seamew.de`), never throwing. - **Response**: `{code: 200, msg: "Refashion POA generated successfully", data: {pdf_url}}` or `{code: 400, msg: error, data: null}` - **Full API spec**: see `docs/refashion-poa-api.md` ## Refashion UIN Certificate Generation API POST `/fr_epr_reg/api/epr/generate-refashion-uin-certificate` - generates a Refashion (French textile law) UIN certificate PDF on demand from **company data passed directly in the request** (unlike CITEO/Refashion POA, no source DB query). On-demand only (not polled). - **Middleware**: `internal.network` (172.16.x.x subnet only); no `parse.large.json` (small request body) - **Controller**: `RefashionUinCertificateController` -> `RefashionUinCertificateService` - **Request validation**: `GenerateRefashionUinCertificateRequest` - requires 5 non-empty strings: `NameEng` (company English name, `{{NameEng}}` used twice in template), `CountryReg` (registration country), `BusinessLicenseNo` (business license no.), `UIN` (UIN number), `CompanyCnName` (company Chinese name - used only for the filename). The issuance date (`{{CurrentDay}}`) is NOT passed in - the service uses `date('Y.m.d')` (e.g. `2026.04.24`) server-side. Empty/missing params -> 400. - **Flow**: validated request data -> `WordTemplateProcessor` fills `storage/Refashion_UIN_Template.docx` (5 placeholders / 6 usages, text-only, no signature image) -> `PdfConverter::convertDocxToPdf($path, false)` (keep all pages, same as Refashion POA) -> `OssUploader` uploads to COS `refashion_uin/{sanitized NameEng}/{uniqid}/` -> return `{pdf_url}`. COS config is shared with the other APIs (`tencent_oss` disk, `OSS_*` env vars); only the `refashion_uin/` directory prefix is specific to this feature. - **Filename**: `法国纺织UIN证书-{FileNameSanitizer::sanitize(CompanyCnName)}.pdf` (Chinese filename; company CN/EN names sanitized like the POA services); returned `pdf_url` path is decoded, so the Chinese filename appears readable in the URL - **OSS failure handling**: any upload exception is wrapped in a `RuntimeException` by the service, so upload problems return 400 (not 500) - same as validation/business errors. - **Response**: `{code: 200, msg: "Refashion UIN certificate generated successfully", data: {pdf_url}}` or `{code: 400, msg: error, data: null}` - **Full API spec**: see `docs/refashion-uin-certificate-api.md` ## Send Refashion Mail API POST `/fr_epr_reg/api/epr/send-refashion-mail` - sends a Refashion (ECO TLC) POA ZIP to Refashion via SMTP, updates source DB PushTaxBureauStatus to 3 (推送成功). Mirrors `send-registration-mail` minus `xlsx_url` (Refashion sends only the POA ZIP, no merged XLSX). - **Middleware**: `internal.network` (172.16.x.x subnet) + `parse.large.json` (handles Base64 line breaks in large JSON) - **Controller**: `RefashionMailController` -> `RefashionMailService` - **Request validation**: `SendRefashionMailRequest` - requires `pdf_zip_base64` (string, max 67MB) + `epr_reg_info_ids` (1–100 unique UUID strings) - **Validation**: all IDs must be ECO TLC records (`SupplierName='ECO TLC'`, same scope as `generate-refashion-poa`); PDF count in ZIP = `epr_reg_info_ids` count (two-way consistency - no XLSX count, unlike `send-registration-mail`'s three-way check) - **Flow**: decode ZIP to temp file -> count PDFs -> validate PDF count = ID count + all IDs are ECO TLC -> send email via PHPMailer (independent Aliyun SMTP account `fr-epr@seamew.de` via `REFASHION_SMTP_*`, distinct from Léko's `info@seamew.de` / `SMTP_MAIL_*`) -> `SourceStatusUpdater::bulkMarkAsPushSucceeded(ids)` (PushTaxBureauStatus=3) -> cleanup - **Email**: French subject/body hardcoded as class constants in `RefashionMailService` (`Demande de l'enregistrement avec Refashion {year} pour {count} clients--Sea&Mew Consulting GmbH--{Y.m.d}`); recipient = `REFASHION_REG_MAIL_TO` (default `hotline@refashion.fr`); single attachment `POA.zip` - **Response**: `{code: 200, msg: "Refashion registration mail sent successfully", data: {sent_to, client_count}}` or `{code: 400, msg: error, data: null}` - **Full API spec**: see `docs/refashion-mail-api.md` ## WeChat Notification After every record (success or failure), a WeChat webhook notification is sent: - **Webhook URL**: `WECHAT_WEBHOOK_URL` env variable → `config('services.wechat.webhook_url')` - **Message format**: `{BusinessSerialNumber} {NameEng} {file generation succeeded / failure reason}` - **@mention**: `F_Mobile` from `Base_User` (joined via `BusinessCounselorId`) → `mentioned_mobile_list` parameter - **Failure notification**: error message includes missing required fields, INSEE API error, or file generation error - **Service**: `WeChatNotificationService::sendNotification(bsn, nameEng, message, mobile)` - **Graceful**: webhook URL not configured → log warning and skip; HTTP error → log error but don't throw ## Retry Mechanisms Transient failures are automatically retried before WeChat failure notifications: | Component | File | Retries | Interval | Scope / Notes | |-----------|------|---------|----------|---------------| | Source DB query | EprRegProcessor.php | 10 | 8s | `fetchPendingRecords()`, `exit(1)` on exhaustion (supervisor restarts) | | INSEE API | EprRegProcessor.php | 5 | 6s | `fetchInseeData()`, WeChat after exhaustion | | DOCX generation | DocxGenerator.php | 3 | 3s | `WordTemplateProcessor::process()`, WeChat after exhaustion | | ZipArchive::open() | WordTemplateProcessor.php | 3 | 100-300ms jitter | Open only; close/rename errors propagate to DOCX retry | | LibreOffice conversion | LibreOfficeConverter.php | 3 | 2s | DOCX->PDF | | OCR + AI (mail) | MailInvoiceMonitor, CiteoMailMonitor | 3 | 3s | Baidu OCR + Volcano Doubao; partial-data fallback after exhaustion | ## Logging Channels | Channel | Path | Retention env (default 30) | |---------|------|----------------------------| | `epr_daily` | `storage/logs/epr/epr-YYYY-MM-DD.log` | `LOG_EPR_DAYS` | | `mail_invoice` | `storage/logs/mail/mail-invoice-YYYY-MM-DD.log` | `LOG_MAIL_DAYS` | | `citeo_invoice` | `storage/logs/citeo/citeo-invoice-YYYY-MM-DD.log` | `LOG_CITEO_DAYS` | | `api_merge_xlsx` | `storage/logs/api/merge-xlsx-YYYY-MM-DD.log` | `LOG_API_DAYS` | | `api_reg_mail` | `storage/logs/api/reg-mail-YYYY-MM-DD.log` | `LOG_API_DAYS` | | `api_citeo_poa` | `storage/logs/api/citeo-poa-YYYY-MM-DD.log` | `LOG_API_DAYS` | | `api_refashion_poa` | `storage/logs/api/refashion-poa-YYYY-MM-DD.log` | `LOG_API_DAYS` | | `api_refashion_mail` | `storage/logs/api/refashion-mail-YYYY-MM-DD.log` | `LOG_API_DAYS` | | `api_refashion_uin` | `storage/logs/api/refashion-uin-YYYY-MM-DD.log` | `LOG_API_DAYS` | | `daily` (default) | `storage/logs/laravel-YYYY-MM-DD.log` | - | Large base64 fields (>10KB) in API requests are auto-saved to `storage/logs/api/raw/{field}_{timestamp}_{hash}.txt` by `ParseLargeJsonBody` middleware. Test runs must never write into these files: retention (`days`) ages files out by mtime, so fresh test noise keeps them alive indefinitely. `phpunit.xml` sets `LOG_PATH_SUFFIX=/testing`, which shifts the whole log tree to `storage/logs/testing/...` for `vendor/bin/phpunit` (`$logRoot` in `config/logging.php`; `ParseLargeJsonBody` reads it via `logging.path_root`). The paths in the table above are the resolved values when the suffix is unset — i.e. every non-test run. ## Conventions - `declare(strict_types=1)` in all app code - Numeric status values (0/1/2/3), not strings - Gender mapping: '1' -> MR, '2' -> MRS - Name splitting: first word = first name, rest = last name - XLSX data starts at row 14; DOCX date format: `Y.m.d` - French decimal comma (2.5 -> 2,5); phone hyphen replaced with space - Province (AreaName): required only for CN; non-CN non-HK empty -> `strtoupper(CountryTwoCode)`; HK empty -> `'香港'` - Signature font: deterministically selected from 7 TTF fonts in `storage/fonts/` by first letter (A-Z -> one of 7 via `LETTER_FONT_MAP`) - GD extension required for signature image generation - All API response `msg` fields must be English (encoding issues on Windows) - POA/certificate filenames: company names entering any filename or OSS key go through `FileNameSanitizer::sanitize()` — strips `\ / : * ? " ' < > | # %`, control chars -> space, trims, collapses double spaces; `&` is kept (legal on Windows and in URL paths). Applies to LEKO POA (`epr:process`) and the CITEO/Refashion POA + UIN certificate APIs alike. - COS URL filenames: `OssUploader::upload()` decodes the returned URL **path** (`rawurldecode` + single-pass `strtr` re-encode of `# ? % " ' * : < > | \`), so `&`, spaces and non-ASCII appear readable; the query string is never decoded. Callers must NOT `urldecode`/`decodeURI` the URL again — that would turn `+` into a space.