File-based Vendor 说明

本文介绍 file-based integrations 的格式规范、常见问题和故障排查。Vendor 的文件交付方式不等于 Retail API 的处理方式：已迁移 vendor 经过 file-based-integration-lambda 和 integrations/elt，格式处理在 Lambda/ELT 中完成；legacy vendor 才由 Retail API parser 直接读取文件。

状态来源：不要继续使用下方的 “All 19” 作为迁移状态。当前 Retail API 以 IntegrationType::isMigratedToElt() 标记 12 个已完成 cutover 的 vendor：Altruist、Apex、Betterment、Flourish、Folio Investing、LPL、My529、Pacific Life、Raymond James、RBC、Schwab、Trust America。integrations/elt/declarations/ 中有定义但尚未被该方法标记的 vendor，仍按 legacy 或迁移中处理。

历史 File-based Vendor 清单

Vendor	文件格式	Host Type	Developer
Allianz	CSV/Excel	Self-hosted	Yan Hu
Altruist	CSV	Self-hosted	Yan Hu
Apex	CSV	Vendor-hosted	Tingsong Xu
Betterment	CSV	Self-hosted	Kewei Yan
Fidelity	Fixed-width (NAM, POS, TAX)	Self-hosted	Qianwei Hao
First Clearing	CSV	Vendor-hosted	Qianwei Hao
Flourish	CSV	Self-hosted	Tingsong Xu
Folio Investing	CSV	Self-hosted	Qianwei Hao
Interactive Brokers	CSV	Self-hosted	Winston Li
Jackson	CSV/Excel	Self-hosted	Winston Li
LPL	CSV	Vendor-hosted	Qianwei Hao
My529	CSV	Vendor-hosted	Kewei Yan
Pacific Life	CSV	Self-hosted	Winston Li
Pershing	Fixed-width (ACCT, GCUS, ECMB, FUND, ISCA)	Self-hosted	Kewei Yan
Raymond James	CSV	Self-hosted	Winston Li
RBC	CSV	Self-hosted	Yan Hu
Schwab (File)	CSV	Self-hosted	Yan Hu
SEI	CSV	Self-hosted	Winston Li
Trust America	CSV	Vendor-hosted	Kewei Yan

常见文件格式问题

1. 编码问题

问题	症状	解决方案
UTF-8 BOM	文件开头出现额外字符	解析前移除 BOM
Latin-1 / ISO-8859-1	特殊字符损坏	转换为 UTF-8
Windows-1252	智能引号显示异常	转换为 UTF-8

// Handle encoding detection and conversion
$content = file_get_contents($filePath);

// Detect encoding
$encoding = mb_detect_encoding($content, ['UTF-8', 'ISO-8859-1', 'Windows-1252']);

// Convert to UTF-8
if ($encoding !== 'UTF-8') {
    $content = mb_convert_encoding($content, 'UTF-8', $encoding);
}

// Strip BOM if present
$content = preg_replace('/^\xEF\xBB\xBF/', '', $content);

2. 日期格式差异

Vendor	日期格式	示例
Fidelity	MMDDYYYY (no separator, in POS header offset 62)	12312024
Pershing	YYYY-MM-DD (in fixed-width files)	2024-12-31
Schwab	M/D/YYYY	1/5/2024
LPL	DD-MMM-YYYY	31-Dec-2024

// Flexible date parsing
private function parseDate(string $value): ?Carbon
{
    $formats = [
        'm/d/Y',      // 12/31/2024
        'n/j/Y',      // 1/5/2024
        'Y-m-d',      // 2024-12-31
        'd-M-Y',      // 31-Dec-2024
        'm-d-Y',      // 12-31-2024
    ];

    foreach ($formats as $format) {
        try {
            return Carbon::createFromFormat($format, trim($value));
        } catch (Exception) {
            continue;
        }
    }

    return null;
}

3. 金额和货币格式

问题	示例	清理后的值
货币符号	$1,234.56	1234.56
千位分隔符	1,234,567.89	1234567.89
括号表示负数	(1,234.56)	-1234.56
空格分隔符	1 234 567.89	1234567.89

private function parseAmount(string $value): float
{
    $cleaned = $value;

    // Handle parentheses for negative
    if (preg_match('/^\((.*)\)$/', $cleaned, $matches)) {
        $cleaned = '-' . $matches[1];
    }

    // Remove currency symbols and separators
    $cleaned = preg_replace('/[^0-9.\-]/', '', $cleaned);

    return (float) $cleaned;
}

4. Null/空值处理

Vendor	空值表示
Fidelity	Space-padded fixed-width fields; closed accounts marked by `&` prefix in NAM short name
Pershing	`NULL` string
Schwab	`N/A` string
LPL	`-` dash

private function normalizeNull(mixed $value): mixed
{
    if (is_string($value)) {
        $normalized = strtoupper(trim($value));

        if (in_array($normalized, ['', 'NULL', 'N/A', 'NA', '-', '--'])) {
            return null;
        }
    }

    return $value;
}

主要 Vendor 格式说明

Fidelity

文件类型：Fixed-width text，交付格式为 .DAT.ZIP 编码：UTF-8 日期格式：MMDDYYYY（位于 POS header offset 62） IBD Code：570（DB reference 保存为 570:{client_id}）

逻辑文件：

FileType	文件名模式	用途
`NAM`	`NABASE`	Account names, types, owner names
`POS`	`POSITD`	Positions: CUSIP, quantity, price, market value
`TAX`	`_TLAOPENDELTA_`	Cost basis (aggregated per CUSIP)

行前缀：H = file header，D = detail row，CH = client header（client_id 位于 offset 3，长度 20），CT = client tailer。

Account Reference：从 NAM offset 8、长度 9 取 BRANCH(3) + ACCOUNT_NUMBER(6)（例如 X46629715），保存时脱敏为 BRANCH + XXX + LAST(3)。

常见问题：

CH/CT 边界不匹配会抛出异常，通常表示上传文件被截断。
已关闭账户的 NAM short name 以 & 开头，会被过滤。
Fidelity 自己的文档错误地写成 MMDDYY；实际格式是 MMDDYYYY。

完整字段 offset 表和代码位置请参阅 Fidelity vendor 文档。

Pershing

文件类型：Fixed-width text files 编码：UTF-8 日期格式：YYYY-MM-DD

逻辑文件：

文件	用途
`ACCT`	Account information (number, name, type, status)
`GCUS`	Securities positions (CUSIP, quantity, market value)
`ECMB`	Electronic trading funds — cash balances (new format)
`FUND`	Money market funds — fund balances (legacy format)
`ISCA`	Security information (symbol, CUSIP, ISIN, price)

过滤：每条 detail row 都在该 file type 的固定 offset 携带 IBD（3 字符）和 IP（3–4 字符）。Reader 根据 integration reference {transmission_id}:{IBD}[:{IP_list}] 中的 IBD/IP 过滤行。

常见问题：

不同 file type 的 IBD/IP offset 不同（offset 表见 vendor 文档）。
ISCA 没有 row-level filtering，security catalog 对所有记录生效。
空值用字面量字符串 NULL 表示。

IBD/IP offset 表、transmission setup SOP 以及完整 account/holding type mapping 请参阅 Pershing vendor 文档。

Schwab（File）

文件类型：CSV 编码：带 BOM 的 UTF-8 日期格式：M/D/YYYY（不补前导零）

必需列：

列名	说明	示例
Account Number	Account ID	XXXX-1234
Account Type	Classification	Brokerage
Balance	Total value	50000.00
Date	As of date	1/5/2024

常见问题：

文件开头的 BOM 需要移除。
Account number 可能已脱敏（XXXX-1234）。
同一 vendor 还有 API 版本（Schwab API），不要混淆。

LPL

文件类型：CSV 编码：Windows-1252 日期格式：DD-MMM-YYYY

必需列：

列名	说明	示例
RepCode	Advisor rep code	ABC123
AccountNumber	Account ID	12345-67890
ClientName	Client name	John Smith
AccountValue	Total value	100,000.00
AsOfDate	Data date	31-Dec-2024

常见问题：

使用 Windows-1252 编码，而不是 UTF-8。
大文件可能需要分块处理。
对拥有多个 Rep 的 advisor 必须按 Rep code 过滤。

Interactive Brokers

File Type: CSV (Activity Statement export) Encoding: UTF-8 Date Format: YYYY-MM-DD

Structure:

Multiple sections in single file
Section headers indicate data type
Need to parse section by section

Statement,Header,Account,Date
Statement,Data,U1234567,2024-12-31

Positions,Header,Symbol,Quantity,Price,Value
Positions,Data,AAPL,100,150.00,15000.00
Positions,Data,GOOGL,50,140.00,7000.00

Trades,Header,Date,Symbol,Quantity,Price
Trades,Data,2024-12-15,AAPL,10,148.00

Common Issues:

Multi-section format requires custom parser
Currency conversion for multi-currency accounts
Options/futures positions need special handling

Raymond James

File Type: CSV Encoding: UTF-8 Date Format: MM/DD/YYYY

Common Issues:

Advisor hierarchy in export
Branch code filtering may be needed

SEI

File Type: CSV Encoding: UTF-8 Date Format: MM/DD/YYYY

Common Issues:

Trust account structures
Multi-custody accounts

新增 File-based Integration

检查清单

Obtain Sample Files
- Get at least 3 sample files with real data structure
- Document file format (CSV/Excel)
- Document encoding
- Document date/amount formats

Create Directory Structure

app/Integrations/[NewVendor]/
├── Integrator.php
├── Parser.php
├── Mapper.php
└── Models/
    ├── Account.php
    └── Holding.php

Implement Parser
- Handle encoding conversion
- Handle header detection
- Handle multi-sheet (if Excel)
- Handle empty/summary rows
Implement Mapper
- Document column mapping
- Handle date parsing
- Handle amount parsing
- Handle null values
- Map account types
Implement Integrator
- Extend FileBasedIntegrator
- Wire up Parser and Mapper
- Implement sync logic
Add to IntegrationType Enum
- Add new enum case
- Implement isFileBased() return true
- Set getFileBasedHostType()
Testing
- Unit test Parser with sample files
- Unit test Mapper with edge cases
- Integration test full sync flow
- Test with malformed/edge case files
Documentation
- Add to this vendor-specifics document
- Update architecture-overview vendor list
- Add developer ownership

模板：新的 Vendor Integrator

<?php

namespace App\Integrations\NewVendor;

use App\Integrations\Support\FileBasedIntegrator;
use RightCapital\Core\Enums\Integration\IntegrationType;

class Integrator extends FileBasedIntegrator
{
    protected function getParser(): Parser
    {
        return new Parser();
    }

    protected function getMapper(): Mapper
    {
        return new Mapper();
    }

    protected static function getVendor(): IntegrationType
    {
        return IntegrationType::NEW_VENDOR;
    }
}

开发负责人

Developer	Integrations
Qianwei Hao	Fidelity, First Clearing, Folio Investing, LPL
Kewei Yan	Betterment, My529, Pershing, Trust America
Tingsong Xu	Apex, Flourish
Yan Hu	Allianz, Altruist, RBC, Schwab (file)
Winston Li	Interactive Brokers, Jackson, Pacific Life, Raymond James, SEI

调试技巧

查看 Raw 文件内容

# Check encoding
file -I /path/to/uploaded/file.csv

# View first few lines
head -20 /path/to/uploaded/file.csv

# Check for BOM
hexdump -C /path/to/uploaded/file.csv | head -1

常见解析错误

Error	Likely Cause	Solution
“Column X not found”	Wrong column name or encoding	Check actual headers in file
“Invalid date format”	Unexpected date format	Add format to parser
“Number format exception”	Non-numeric characters	Improve amount cleaning
“File too large”	Exceeds memory limit	Implement chunked reading

本地测试文件

// Quick test in tinker (current Reader/Extractor pattern)
use RightCapital\Integrations\FileBased\Fidelity\{Extractor, FileType, Reader};

$reader    = Reader::create('570:CLIENT_ID', FileType::NAM);
$extractor = new Extractor($reader);
dd($extractor->getEntitiesByReference());

Note: legacy file-based integrations used App\Integrations\<Vendor>\Parser. New code lives in the RightCapital\Integrations\FileBased\<Vendor> package and uses the Reader + Extractor + Integrator pattern. See patterns doc.

File-based Vendor 说明

历史 File-based Vendor 清单

常见文件格式问题

1. 编码问题

2. 日期格式差异

3. 金额和货币格式

4. Null/空值处理

主要 Vendor 格式说明

Fidelity

Pershing

Schwab（File）

LPL

Interactive Brokers

Raymond James

SEI

新增 File-based Integration

检查清单

模板：新的 Vendor Integrator

开发负责人

调试技巧

查看 Raw 文件内容

常见解析错误

本地测试文件

相关文档