财务核算模块设计——多币种、多主体的挑战

// 订单信息
// 销售价格：€100
// 销售时汇率：1 EUR = 7.8 CNY
// 确认收入：€100 × 7.8 = ¥780

// 两周后Amazon结算
// 结算时汇率：1 EUR = 7.6 CNY
// 实际收款：€100 × 7.6 = ¥760

// 汇兑损失：¥780 - ¥760 = ¥20
// 毛利率影响：假设原毛利30%（¥234），汇兑损失占毛利的8.5%

/**
 * 汇率管理服务
 *
 * 支持多种汇率类型、历史汇率查询、汇率换算
 */
@Service
public class ExchangeRateService {

    @Autowired
    private ExchangeRateRepository exchangeRateRepository;

    @Autowired
    private ExchangeRateApiClient apiClient;

    /**
     * 获取指定日期的汇率
     *
     * @param fromCurrency 源币种
     * @param toCurrency 目标币种
     * @param date 日期
     * @param rateType 汇率类型
     * @return 汇率
     */
    public BigDecimal getExchangeRate(String fromCurrency, String toCurrency,
                                       LocalDate date, RateType rateType) {
        // 同币种直接返回1
        if (fromCurrency.equals(toCurrency)) {
            return BigDecimal.ONE;
        }

        // 查询数据库
        ExchangeRate rate = exchangeRateRepository.findByDateAndType(
            fromCurrency, toCurrency, date, rateType);

        if (rate != null) {
            return rate.getRate();
        }

        // 尝试反向汇率
        ExchangeRate reverseRate = exchangeRateRepository.findByDateAndType(
            toCurrency, fromCurrency, date, rateType);

        if (reverseRate != null && reverseRate.getRate().compareTo(BigDecimal.ZERO) > 0) {
            return BigDecimal.ONE.divide(reverseRate.getRate(), 6, RoundingMode.HALF_UP);
        }

        // 通过CNY中转计算
        if (!fromCurrency.equals("CNY") && !toCurrency.equals("CNY")) {
            BigDecimal fromToCny = getExchangeRate(fromCurrency, "CNY", date, rateType);
            BigDecimal cnyToTarget = getExchangeRate("CNY", toCurrency, date, rateType);
            return fromToCny.multiply(cnyToTarget).setScale(6, RoundingMode.HALF_UP);
        }

        throw new BusinessException("未找到汇率: " + fromCurrency + "/" + toCurrency);
    }

    /**
     * 金额换算
     */
    public BigDecimal convert(BigDecimal amount, String fromCurrency,
                              String toCurrency, LocalDate date) {
        BigDecimal rate = getExchangeRate(fromCurrency, toCurrency, date, RateType.SPOT);
        return amount.multiply(rate).setScale(2, RoundingMode.HALF_UP);
    }

    /**
     * 自动更新汇率（每日定时任务）
     */
    @Scheduled(cron = "0 30 9 * * ?")  // 每天9:30更新
    public void updateDailyRates() {
        List<String> currencies = Arrays.asList("USD", "EUR", "GBP", "JPY", "HKD");
        LocalDate today = LocalDate.now();

        for (String currency : currencies) {
            try {
                BigDecimal rate = apiClient.fetchRate(currency, "CNY");
                saveRate(currency, "CNY", today, rate, RateType.SPOT);
                log.info("更新汇率成功: {}/CNY = {}", currency, rate);
            } catch (Exception e) {
                log.error("更新汇率失败: {}", currency, e);
            }
        }
    }
}

-- 汇率表
CREATE TABLE t_exchange_rate (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    from_currency VARCHAR(8) NOT NULL COMMENT '源币种',
    to_currency VARCHAR(8) NOT NULL COMMENT '目标币种',
    rate_date DATE NOT NULL COMMENT '汇率日期',
    rate_type VARCHAR(16) NOT NULL COMMENT '汇率类型: SPOT/PERIOD_END/BUDGET',
    rate DECIMAL(12,6) NOT NULL COMMENT '汇率',
    source VARCHAR(32) COMMENT '数据来源',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_rate (from_currency, to_currency, rate_date, rate_type),
    INDEX idx_date (rate_date)
) COMMENT '汇率表';

-- 汇率历史表（用于审计）
CREATE TABLE t_exchange_rate_history (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    rate_id BIGINT NOT NULL,
    old_rate DECIMAL(12,6),
    new_rate DECIMAL(12,6),
    changed_by VARCHAR(64),
    changed_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    change_reason VARCHAR(256)
) COMMENT '汇率变更历史';

/**
 * 应收管理服务
 */
@Service
public class AccountReceivableService {

    @Autowired
    private ReceivableRepository receivableRepository;

    @Autowired
    private ExchangeRateService exchangeRateService;

    @Autowired
    private VoucherService voucherService;

    /**
     * 创建应收单（订单发货时调用）
     */
    @Transactional
    public Receivable createReceivable(Order order) {
        Receivable receivable = new Receivable();
        receivable.setReceivableNo(generateReceivableNo());
        receivable.setOrderNo(order.getOrderNo());
        receivable.setCustomerCode(order.getChannel());  // 平台作为客户
        receivable.setEntityCode(order.getEntityCode()); // 法人主体

        // 原币金额
        receivable.setCurrency(order.getCurrency());
        receivable.setOriginalAmount(order.getTotalAmount());

        // 本位币金额（按交易日汇率）
        LocalDate transDate = order.getShipDate();
        BigDecimal rate = exchangeRateService.getExchangeRate(
            order.getCurrency(), "CNY", transDate, RateType.SPOT);
        receivable.setLocalAmount(order.getTotalAmount().multiply(rate)
            .setScale(2, RoundingMode.HALF_UP));
        receivable.setExchangeRate(rate);

        receivable.setTransDate(transDate);
        receivable.setDueDate(transDate.plusDays(14));  // 预计14天后结算
        receivable.setStatus(ReceivableStatus.PENDING);

        receivableRepository.save(receivable);

        // 生成收入确认凭证
        generateRevenueVoucher(receivable);

        return receivable;
    }

    /**
     * 平台结算核销
     */
    @Transactional
    public void settleReceivable(String receivableNo, SettlementDTO settlement) {
        Receivable receivable = receivableRepository.findByNo(receivableNo);

        // 记录实际收款
        receivable.setSettledCurrency(settlement.getCurrency());
        receivable.setSettledAmount(settlement.getAmount());
        receivable.setSettleDate(settlement.getSettleDate());

        // 计算本位币实际收款
        BigDecimal settleRate = exchangeRateService.getExchangeRate(
            settlement.getCurrency(), "CNY", settlement.getSettleDate(), RateType.SPOT);
        BigDecimal settledLocalAmount = settlement.getAmount().multiply(settleRate)
            .setScale(2, RoundingMode.HALF_UP);
        receivable.setSettledLocalAmount(settledLocalAmount);

        // 计算汇兑损益
        BigDecimal exchangeGainLoss = settledLocalAmount.subtract(receivable.getLocalAmount());
        receivable.setExchangeGainLoss(exchangeGainLoss);

        receivable.setStatus(ReceivableStatus.SETTLED);
        receivableRepository.save(receivable);

        // 生成收款凭证（含汇兑损益）
        generateSettlementVoucher(receivable);
    }

    /**
     * 生成收入确认凭证
     *
     * 借：应收账款-平台（外币）
     * 贷：主营业务收入
     */
    private void generateRevenueVoucher(Receivable receivable) {
        VoucherDTO voucher = new VoucherDTO();
        voucher.setVoucherDate(receivable.getTransDate());
        voucher.setDescription("销售收入确认-" + receivable.getOrderNo());

        // 借方：应收账款
        VoucherItemDTO debit = new VoucherItemDTO();
        debit.setAccountCode("1122");  // 应收账款
        debit.setCurrency(receivable.getCurrency());
        debit.setOriginalAmount(receivable.getOriginalAmount());
        debit.setLocalAmount(receivable.getLocalAmount());
        debit.setDebitCredit(DebitCredit.DEBIT);
        voucher.addItem(debit);

        // 贷方：主营业务收入
        VoucherItemDTO credit = new VoucherItemDTO();
        credit.setAccountCode("6001");  // 主营业务收入
        credit.setLocalAmount(receivable.getLocalAmount());
        credit.setDebitCredit(DebitCredit.CREDIT);
        voucher.addItem(credit);

        voucherService.createVoucher(voucher);
    }
}

/**
 * 应付管理服务
 */
@Service
public class AccountPayableService {

    @Autowired
    private PayableRepository payableRepository;

    @Autowired
    private ExchangeRateService exchangeRateService;

    @Autowired
    private VoucherService voucherService;

    /**
     * 创建应付单（采购入库时调用）
     */
    @Transactional
    public Payable createPayable(PurchaseReceipt receipt) {
        Payable payable = new Payable();
        payable.setPayableNo(generatePayableNo());
        payable.setPurchaseOrderNo(receipt.getPurchaseOrderNo());
        payable.setSupplierCode(receipt.getSupplierCode());
        payable.setEntityCode(receipt.getEntityCode());

        // 原币金额
        payable.setCurrency(receipt.getCurrency());
        payable.setOriginalAmount(receipt.getTotalAmount());

        // 本位币金额（按入库日汇率）
        LocalDate receiptDate = receipt.getReceiptDate();
        BigDecimal rate = exchangeRateService.getExchangeRate(
            receipt.getCurrency(), "CNY", receiptDate, RateType.SPOT);
        payable.setLocalAmount(receipt.getTotalAmount().multiply(rate)
            .setScale(2, RoundingMode.HALF_UP));
        payable.setExchangeRate(rate);

        payable.setTransDate(receiptDate);
        payable.setDueDate(calculateDueDate(receipt));  // 根据付款条款计算
        payable.setStatus(PayableStatus.PENDING);
        payable.setInvoiceStatus(InvoiceStatus.NOT_RECEIVED);

        payableRepository.save(payable);

        // 生成暂估入库凭证
        generateEstimatedVoucher(payable);

        return payable;
    }

    /**
     * 发票登记
     */
    @Transactional
    public void registerInvoice(String payableNo, InvoiceDTO invoice) {
        Payable payable = payableRepository.findByNo(payableNo);

        // 三单匹配：采购单、入库单、发票
        validateThreeWayMatch(payable, invoice);

        payable.setInvoiceNo(invoice.getInvoiceNo());
        payable.setInvoiceDate(invoice.getInvoiceDate());
        payable.setInvoiceAmount(invoice.getAmount());
        payable.setTaxAmount(invoice.getTaxAmount());
        payable.setInvoiceStatus(InvoiceStatus.RECEIVED);

        // 如果发票金额与暂估金额有差异，生成调整凭证
        BigDecimal diff = invoice.getAmount().subtract(payable.getOriginalAmount());
        if (diff.abs().compareTo(new BigDecimal("0.01")) > 0) {
            generateAdjustmentVoucher(payable, diff);
        }

        payableRepository.save(payable);
    }

    /**
     * 付款处理
     */
    @Transactional
    public void processPayment(String payableNo, PaymentDTO payment) {
        Payable payable = payableRepository.findByNo(payableNo);

        // 记录实际付款
        payable.setPaidCurrency(payment.getCurrency());
        payable.setPaidAmount(payment.getAmount());
        payable.setPaymentDate(payment.getPaymentDate());

        // 计算本位币实际付款
        BigDecimal payRate = exchangeRateService.getExchangeRate(
            payment.getCurrency(), "CNY", payment.getPaymentDate(), RateType.SPOT);
        BigDecimal paidLocalAmount = payment.getAmount().multiply(payRate)
            .setScale(2, RoundingMode.HALF_UP);
        payable.setPaidLocalAmount(paidLocalAmount);

        // 计算汇兑损益
        BigDecimal exchangeGainLoss = payable.getLocalAmount().subtract(paidLocalAmount);
        payable.setExchangeGainLoss(exchangeGainLoss);

        payable.setStatus(PayableStatus.PAID);
        payableRepository.save(payable);

        // 生成付款凭证（含汇兑损益）
        generatePaymentVoucher(payable);
    }

    /**
     * 三单匹配验证
     */
    private void validateThreeWayMatch(Payable payable, InvoiceDTO invoice) {
        // 1. 数量匹配
        // 2. 单价匹配（允许一定误差）
        // 3. 金额匹配
        BigDecimal tolerance = new BigDecimal("0.02");  // 2%误差容忍度

        BigDecimal diff = invoice.getAmount().subtract(payable.getOriginalAmount())
            .abs().divide(payable.getOriginalAmount(), 4, RoundingMode.HALF_UP);

        if (diff.compareTo(tolerance) > 0) {
            throw new BusinessException("发票金额与采购金额差异超过2%，请核实");
        }
    }
}

/**
 * 汇兑损益计算服务
 */
@Service
public class ExchangeGainLossService {

    @Autowired
    private ExchangeRateService exchangeRateService;

    @Autowired
    private ReceivableRepository receivableRepository;

    @Autowired
    private PayableRepository payableRepository;

    @Autowired
    private BankAccountRepository bankAccountRepository;

    @Autowired
    private VoucherService voucherService;

    /**
     * 期末汇兑损益计算（月结时调用）
     */
    @Transactional
    public ExchangeGainLossResult calculatePeriodEndGainLoss(String period) {
        LocalDate periodEndDate = getPeriodEndDate(period);
        ExchangeGainLossResult result = new ExchangeGainLossResult();

        // 1. 计算应收账款汇兑损益
        BigDecimal receivableGainLoss = calculateReceivableGainLoss(periodEndDate);
        result.setReceivableGainLoss(receivableGainLoss);

        // 2. 计算应付账款汇兑损益
        BigDecimal payableGainLoss = calculatePayableGainLoss(periodEndDate);
        result.setPayableGainLoss(payableGainLoss);

        // 3. 计算外币银行存款汇兑损益
        BigDecimal bankGainLoss = calculateBankGainLoss(periodEndDate);
        result.setBankGainLoss(bankGainLoss);

        // 4. 汇总
        BigDecimal totalGainLoss = receivableGainLoss
            .add(payableGainLoss)
            .add(bankGainLoss);
        result.setTotalGainLoss(totalGainLoss);

        // 5. 生成汇兑损益凭证
        if (totalGainLoss.abs().compareTo(new BigDecimal("0.01")) > 0) {
            generateGainLossVoucher(result, periodEndDate);
        }

        return result;
    }

    /**
     * 计算应收账款汇兑损益
     */
    private BigDecimal calculateReceivableGainLoss(LocalDate periodEndDate) {
        List<Receivable> pendingReceivables = receivableRepository
            .findByStatusAndCurrencyNot(ReceivableStatus.PENDING, "CNY");

        BigDecimal totalGainLoss = BigDecimal.ZERO;

        for (Receivable receivable : pendingReceivables) {
            // 获取期末汇率
            BigDecimal periodEndRate = exchangeRateService.getExchangeRate(
                receivable.getCurrency(), "CNY", periodEndDate, RateType.PERIOD_END);

            // 计算期末本位币金额
            BigDecimal periodEndLocalAmount = receivable.getOriginalAmount()
                .multiply(periodEndRate).setScale(2, RoundingMode.HALF_UP);

            // 汇兑损益 = 期末金额 - 账面金额
            BigDecimal gainLoss = periodEndLocalAmount.subtract(receivable.getLocalAmount());

            // 更新账面金额
            receivable.setLocalAmount(periodEndLocalAmount);
            receivable.setExchangeRate(periodEndRate);
            receivableRepository.save(receivable);

            totalGainLoss = totalGainLoss.add(gainLoss);
        }

        return totalGainLoss;
    }

    /**
     * 生成汇兑损益凭证
     */
    private void generateGainLossVoucher(ExchangeGainLossResult result, LocalDate date) {
        VoucherDTO voucher = new VoucherDTO();
        voucher.setVoucherDate(date);
        voucher.setDescription("期末汇兑损益调整");

        BigDecimal totalGainLoss = result.getTotalGainLoss();

        if (totalGainLoss.compareTo(BigDecimal.ZERO) > 0) {
            // 汇兑收益
            // 借：应收账款/银行存款（调增）
            // 贷：财务费用-汇兑损益
            VoucherItemDTO credit = new VoucherItemDTO();
            credit.setAccountCode("6603.02");  // 财务费用-汇兑损益
            credit.setLocalAmount(totalGainLoss);
            credit.setDebitCredit(DebitCredit.CREDIT);
            voucher.addItem(credit);
        } else {
            // 汇兑损失
            // 借：财务费用-汇兑损益
            // 贷：应收账款/银行存款（调减）
            VoucherItemDTO debit = new VoucherItemDTO();
            debit.setAccountCode("6603.02");
            debit.setLocalAmount(totalGainLoss.abs());
            debit.setDebitCredit(DebitCredit.DEBIT);
            voucher.addItem(debit);
        }

        voucherService.createVoucher(voucher);
    }
}

-- 会计科目表
CREATE TABLE t_account (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    account_code VARCHAR(32) NOT NULL COMMENT '科目编码',
    account_name VARCHAR(128) NOT NULL COMMENT '科目名称',
    account_type VARCHAR(16) NOT NULL COMMENT '科目类型: ASSET/LIABILITY/EQUITY/INCOME/EXPENSE',
    parent_code VARCHAR(32) COMMENT '上级科目编码',
    level INT NOT NULL COMMENT '科目级次',
    is_leaf TINYINT DEFAULT 1 COMMENT '是否末级科目',
    is_foreign_currency TINYINT DEFAULT 0 COMMENT '是否外币核算',
    is_auxiliary TINYINT DEFAULT 0 COMMENT '是否辅助核算',
    auxiliary_type VARCHAR(64) COMMENT '辅助核算类型: CUSTOMER/SUPPLIER/PROJECT',
    balance_direction VARCHAR(8) COMMENT '余额方向: DEBIT/CREDIT',
    status VARCHAR(16) DEFAULT 'ACTIVE' COMMENT '状态',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_account_code (account_code),
    INDEX idx_parent (parent_code)
) COMMENT '会计科目表';

-- 初始化跨境电商常用科目
INSERT INTO t_account (account_code, account_name, account_type, level, is_foreign_currency, auxiliary_type) VALUES
-- 资产类
('1001', '库存现金', 'ASSET', 1, 0, NULL),
('1002', '银行存款', 'ASSET', 1, 1, NULL),
('1002.01', '银行存款-CNY', 'ASSET', 2, 0, NULL),
('1002.02', '银行存款-USD', 'ASSET', 2, 1, NULL),
('1002.03', '银行存款-EUR', 'ASSET', 2, 1, NULL),
('1002.04', '银行存款-HKD', 'ASSET', 2, 1, NULL),
('1122', '应收账款', 'ASSET', 1, 1, 'CUSTOMER'),
('1122.01', '应收账款-Amazon', 'ASSET', 2, 1, 'CUSTOMER'),
('1122.02', '应收账款-eBay', 'ASSET', 2, 1, 'CUSTOMER'),
('1122.03', '应收账款-独立站', 'ASSET', 2, 1, 'CUSTOMER'),
('1405', '库存商品', 'ASSET', 1, 0, NULL),
('1408', '委托加工物资', 'ASSET', 1, 0, NULL),
-- 负债类
('2202', '应付账款', 'LIABILITY', 1, 1, 'SUPPLIER'),
('2202.01', '应付账款-国内供应商', 'LIABILITY', 2, 0, 'SUPPLIER'),
('2202.02', '应付账款-海外供应商', 'LIABILITY', 2, 1, 'SUPPLIER'),
('2211', '应付职工薪酬', 'LIABILITY', 1, 0, NULL),
('2221', '应交税费', 'LIABILITY', 1, 0, NULL),
-- 收入类
('6001', '主营业务收入', 'INCOME', 1, 0, NULL),
('6001.01', '主营业务收入-Amazon', 'INCOME', 2, 0, NULL),
('6001.02', '主营业务收入-eBay', 'INCOME', 2, 0, NULL),
('6001.03', '主营业务收入-独立站', 'INCOME', 2, 0, NULL),
-- 成本类
('6401', '主营业务成本', 'EXPENSE', 1, 0, NULL),
('6401.01', '主营业务成本-商品成本', 'EXPENSE', 2, 0, NULL),
('6401.02', '主营业务成本-物流成本', 'EXPENSE', 2, 0, NULL),
('6401.03', '主营业务成本-平台费用', 'EXPENSE', 2, 0, NULL),
-- 费用类
('6601', '销售费用', 'EXPENSE', 1, 0, NULL),
('6601.01', '销售费用-广告费', 'EXPENSE', 2, 0, NULL),
('6601.02', '销售费用-推广费', 'EXPENSE', 2, 0, NULL),
('6602', '管理费用', 'EXPENSE', 1, 0, NULL),
('6603', '财务费用', 'EXPENSE', 1, 0, NULL),
('6603.01', '财务费用-利息支出', 'EXPENSE', 2, 0, NULL),
('6603.02', '财务费用-汇兑损益', 'EXPENSE', 2, 0, NULL),
('6603.03', '财务费用-手续费', 'EXPENSE', 2, 0, NULL);

-- 会计凭证主表
CREATE TABLE t_voucher (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    voucher_no VARCHAR(32) NOT NULL COMMENT '凭证号',
    entity_code VARCHAR(32) NOT NULL COMMENT '法人主体编码',
    voucher_date DATE NOT NULL COMMENT '凭证日期',
    period VARCHAR(8) NOT NULL COMMENT '会计期间: 202601',
    voucher_type VARCHAR(16) COMMENT '凭证类型: 收/付/转',
    description VARCHAR(512) COMMENT '摘要',
    total_debit DECIMAL(16,2) COMMENT '借方合计',
    total_credit DECIMAL(16,2) COMMENT '贷方合计',
    attachment_count INT DEFAULT 0 COMMENT '附件数',
    status VARCHAR(16) DEFAULT 'DRAFT' COMMENT '状态: DRAFT/SUBMITTED/APPROVED/POSTED',
    source_type VARCHAR(32) COMMENT '来源类型: MANUAL/AUTO/IMPORT',
    source_no VARCHAR(64) COMMENT '来源单号',
    created_by VARCHAR(64) COMMENT '制单人',
    approved_by VARCHAR(64) COMMENT '审核人',
    posted_by VARCHAR(64) COMMENT '记账人',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    approved_at DATETIME,
    posted_at DATETIME,
    UNIQUE KEY uk_voucher_no (entity_code, voucher_no),
    INDEX idx_period (period),
    INDEX idx_date (voucher_date),
    INDEX idx_source (source_type, source_no)
) COMMENT '会计凭证主表';

-- 会计凭证明细表
CREATE TABLE t_voucher_item (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    voucher_id BIGINT NOT NULL COMMENT '凭证ID',
    voucher_no VARCHAR(32) NOT NULL COMMENT '凭证号',
    seq INT NOT NULL COMMENT '行号',
    account_code VARCHAR(32) NOT NULL COMMENT '科目编码',
    account_name VARCHAR(128) COMMENT '科目名称',
    description VARCHAR(256) COMMENT '摘要',
    currency VARCHAR(8) DEFAULT 'CNY' COMMENT '币种',
    exchange_rate DECIMAL(12,6) DEFAULT 1 COMMENT '汇率',
    original_debit DECIMAL(16,2) DEFAULT 0 COMMENT '原币借方',
    original_credit DECIMAL(16,2) DEFAULT 0 COMMENT '原币贷方',
    local_debit DECIMAL(16,2) DEFAULT 0 COMMENT '本币借方',
    local_credit DECIMAL(16,2) DEFAULT 0 COMMENT '本币贷方',
    auxiliary_type VARCHAR(32) COMMENT '辅助核算类型',
    auxiliary_code VARCHAR(64) COMMENT '辅助核算编码',
    auxiliary_name VARCHAR(128) COMMENT '辅助核算名称',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_voucher (voucher_id),
    INDEX idx_account (account_code),
    INDEX idx_auxiliary (auxiliary_type, auxiliary_code)
) COMMENT '会计凭证明细表';

-- 应收账款表
CREATE TABLE t_receivable (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    receivable_no VARCHAR(32) NOT NULL COMMENT '应收单号',
    entity_code VARCHAR(32) NOT NULL COMMENT '法人主体',
    customer_code VARCHAR(32) NOT NULL COMMENT '客户编码（平台）',
    customer_name VARCHAR(128) COMMENT '客户名称',
    order_no VARCHAR(64) COMMENT '关联订单号',
    trans_date DATE NOT NULL COMMENT '交易日期',
    due_date DATE COMMENT '到期日期',
    currency VARCHAR(8) NOT NULL COMMENT '原币种',
    original_amount DECIMAL(16,2) NOT NULL COMMENT '原币金额',
    exchange_rate DECIMAL(12,6) NOT NULL COMMENT '入账汇率',
    local_amount DECIMAL(16,2) NOT NULL COMMENT '本币金额',
    settled_currency VARCHAR(8) COMMENT '结算币种',
    settled_amount DECIMAL(16,2) DEFAULT 0 COMMENT '已结算原币金额',
    settled_local_amount DECIMAL(16,2) DEFAULT 0 COMMENT '已结算本币金额',
    settle_date DATE COMMENT '结算日期',
    exchange_gain_loss DECIMAL(16,2) DEFAULT 0 COMMENT '汇兑损益',
    status VARCHAR(16) DEFAULT 'PENDING' COMMENT '状态: PENDING/PARTIAL/SETTLED/CANCELLED',
    remark VARCHAR(512) COMMENT '备注',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_receivable_no (receivable_no),
    INDEX idx_customer (customer_code),
    INDEX idx_order (order_no),
    INDEX idx_status (status),
    INDEX idx_due_date (due_date)
) COMMENT '应收账款表';

-- 应付账款表
CREATE TABLE t_payable (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    payable_no VARCHAR(32) NOT NULL COMMENT '应付单号',
    entity_code VARCHAR(32) NOT NULL COMMENT '法人主体',
    supplier_code VARCHAR(32) NOT NULL COMMENT '供应商编码',
    supplier_name VARCHAR(128) COMMENT '供应商名称',
    purchase_order_no VARCHAR(64) COMMENT '关联采购单号',
    trans_date DATE NOT NULL COMMENT '交易日期',
    due_date DATE COMMENT '到期日期',
    currency VARCHAR(8) NOT NULL COMMENT '原币种',
    original_amount DECIMAL(16,2) NOT NULL COMMENT '原币金额',
    exchange_rate DECIMAL(12,6) NOT NULL COMMENT '入账汇率',
    local_amount DECIMAL(16,2) NOT NULL COMMENT '本币金额',
    invoice_no VARCHAR(64) COMMENT '发票号',
    invoice_date DATE COMMENT '发票日期',
    invoice_amount DECIMAL(16,2) COMMENT '发票金额',
    tax_amount DECIMAL(16,2) COMMENT '税额',
    invoice_status VARCHAR(16) DEFAULT 'NOT_RECEIVED' COMMENT '发票状态',
    paid_currency VARCHAR(8) COMMENT '付款币种',
    paid_amount DECIMAL(16,2) DEFAULT 0 COMMENT '已付原币金额',
    paid_local_amount DECIMAL(16,2) DEFAULT 0 COMMENT '已付本币金额',
    payment_date DATE COMMENT '付款日期',
    exchange_gain_loss DECIMAL(16,2) DEFAULT 0 COMMENT '汇兑损益',
    status VARCHAR(16) DEFAULT 'PENDING' COMMENT '状态: PENDING/PARTIAL/PAID/CANCELLED',
    remark VARCHAR(512) COMMENT '备注',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_payable_no (payable_no),
    INDEX idx_supplier (supplier_code),
    INDEX idx_purchase (purchase_order_no),
    INDEX idx_status (status),
    INDEX idx_due_date (due_date)
) COMMENT '应付账款表';

/**
 * 金蝶云星空凭证对接服务
 */
@Service
public class KingdeeVoucherService {

    @Value("${kingdee.api.url}")
    private String apiUrl;

    @Value("${kingdee.api.appId}")
    private String appId;

    @Value("${kingdee.api.appSecret}")
    private String appSecret;

    private RestTemplate restTemplate = new RestTemplate();

    /**
     * 推送凭证到金蝶
     */
    public KingdeeResult pushVoucher(Voucher voucher) {
        // 1. 获取访问令牌
        String token = getAccessToken();

        // 2. 转换凭证格式
        KingdeeVoucherDTO kingdeeVoucher = convertToKingdeeFormat(voucher);

        // 3. 调用金蝶API
        String url = apiUrl + "/k3cloud/Kingdee.BOS.WebApi.ServicesStub.DynamicFormService.Save.common.kdsvc";

        HttpHeaders headers = new HttpHeaders();
        headers.setContentType(MediaType.APPLICATION_JSON);
        headers.set("Authorization", "Bearer " + token);

        Map<String, Object> requestBody = new HashMap<>();
        requestBody.put("formid", "GL_VOUCHER");  // 凭证表单ID
        requestBody.put("data", buildVoucherData(kingdeeVoucher));

        HttpEntity<Map<String, Object>> request = new HttpEntity<>(requestBody, headers);

        try {
            ResponseEntity<KingdeeResult> response = restTemplate.postForEntity(
                url, request, KingdeeResult.class);
            return response.getBody();
        } catch (Exception e) {
            log.error("推送凭证到金蝶失败: {}", voucher.getVoucherNo(), e);
            throw new BusinessException("凭证推送失败: " + e.getMessage());
        }
    }

    /**
     * 转换为金蝶凭证格式
     */
    private KingdeeVoucherDTO convertToKingdeeFormat(Voucher voucher) {
        KingdeeVoucherDTO dto = new KingdeeVoucherDTO();

        // 凭证头
        dto.setFDate(voucher.getVoucherDate().format(DateTimeFormatter.ISO_DATE));
        dto.setFVoucherGroupId("PRE01");  // 凭证字：记
        dto.setFYear(voucher.getVoucherDate().getYear());
        dto.setFPeriod(voucher.getVoucherDate().getMonthValue());
        dto.setFExplanation(voucher.getDescription());
        dto.setFAttachments(voucher.getAttachmentCount());

        // 凭证分录
        List<KingdeeVoucherEntryDTO> entries = new ArrayList<>();
        for (VoucherItem item : voucher.getItems()) {
            KingdeeVoucherEntryDTO entry = new KingdeeVoucherEntryDTO();
            entry.setFAccountId(mapAccountCode(item.getAccountCode()));
            entry.setFExplanation(item.getDescription());
            entry.setFCurrencyId(item.getCurrency());
            entry.setFExchangeRate(item.getExchangeRate());
            entry.setFOriginalAmountFor(item.getOriginalDebit().subtract(item.getOriginalCredit()));
            entry.setFDebit(item.getLocalDebit());
            entry.setFCredit(item.getLocalCredit());

            // 辅助核算
            if (item.getAuxiliaryType() != null) {
                entry.setFDetailId(mapAuxiliaryCode(item.getAuxiliaryType(), item.getAuxiliaryCode()));
            }

            entries.add(entry);
        }
        dto.setFEntity(entries);

        return dto;
    }

    /**
     * 科目编码映射（业务系统 -> 金蝶）
     */
    private String mapAccountCode(String bizAccountCode) {
        // 从映射表获取金蝶科目编码
        AccountMapping mapping = accountMappingRepository.findByBizCode(bizAccountCode);
        if (mapping == null) {
            throw new BusinessException("未找到科目映射: " + bizAccountCode);
        }
        return mapping.getKingdeeCode();
    }
}

-- 科目映射表
CREATE TABLE t_account_mapping (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    biz_account_code VARCHAR(32) NOT NULL COMMENT '业务系统科目编码',
    biz_account_name VARCHAR(128) COMMENT '业务系统科目名称',
    finance_system VARCHAR(16) NOT NULL COMMENT '财务系统: KINGDEE/YONYOU',
    finance_account_code VARCHAR(32) NOT NULL COMMENT '财务系统科目编码',
    finance_account_name VARCHAR(128) COMMENT '财务系统科目名称',
    status VARCHAR(16) DEFAULT 'ACTIVE',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_mapping (biz_account_code, finance_system)
) COMMENT '科目映射表';

/**
 * 用友U8+凭证对接服务
 */
@Service
public class YonyouVoucherService {

    @Value("${yonyou.api.url}")
    private String apiUrl;

    @Autowired
    private YonyouTokenService tokenService;

    /**
     * 推送凭证到用友
     */
    public YonyouResult pushVoucher(Voucher voucher) {
        // 1. 获取令牌
        String token = tokenService.getToken();

        // 2. 构建用友凭证XML
        String voucherXml = buildVoucherXml(voucher);

        // 3. 调用用友WebService
        String url = apiUrl + "/U8API/Voucher/Add";

        HttpHeaders headers = new HttpHeaders();
        headers.setContentType(MediaType.APPLICATION_XML);
        headers.set("token", token);

        HttpEntity<String> request = new HttpEntity<>(voucherXml, headers);

        try {
            ResponseEntity<String> response = restTemplate.postForEntity(
                url, request, String.class);
            return parseYonyouResponse(response.getBody());
        } catch (Exception e) {
            log.error("推送凭证到用友失败: {}", voucher.getVoucherNo(), e);
            throw new BusinessException("凭证推送失败: " + e.getMessage());
        }
    }

    /**
     * 构建用友凭证XML
     */
    private String buildVoucherXml(Voucher voucher) {
        StringBuilder xml = new StringBuilder();
        xml.append("<?xml version=\"1.0\" encoding=\"utf-8\"?>");
        xml.append("<ufinterface roession=\"1\" sender=\"ERP\">");
        xml.append("<voucher>");

        // 凭证头
        xml.append("<voucher_head>");
        xml.append("<voucher_type>记</voucher_type>");
        xml.append("<fiscal_year>").append(voucher.getVoucherDate().getYear()).append("</fiscal_year>");
        xml.append("<accounting_period>").append(voucher.getVoucherDate().getMonthValue()).append("</accounting_period>");
        xml.append("<voucher_date>").append(voucher.getVoucherDate()).append("</voucher_date>");
        xml.append("<attachment_number>").append(voucher.getAttachmentCount()).append("</attachment_number>");
        xml.append("</voucher_head>");

        // 凭证分录
        xml.append("<voucher_body>");
        int seq = 1;
        for (VoucherItem item : voucher.getItems()) {
            xml.append("<entry>");
            xml.append("<entry_id>").append(seq++).append("</entry_id>");
            xml.append("<account_code>").append(mapToYonyouAccount(item.getAccountCode())).append("</account_code>");
            xml.append("<abstract>").append(escapeXml(item.getDescription())).append("</abstract>");
            xml.append("<currency>").append(item.getCurrency()).append("</currency>");
            xml.append("<exchange_rate>").append(item.getExchangeRate()).append("</exchange_rate>");
            xml.append("<debit_amount>").append(item.getLocalDebit()).append("</debit_amount>");
            xml.append("<credit_amount>").append(item.getLocalCredit()).append("</credit_amount>");
            xml.append("</entry>");
        }
        xml.append("</voucher_body>");

        xml.append("</voucher>");
        xml.append("</ufinterface>");

        return xml.toString();
    }
}

/**
 * 凭证同步调度服务
 */
@Service
public class VoucherSyncScheduler {

    @Autowired
    private VoucherRepository voucherRepository;

    @Autowired
    private KingdeeVoucherService kingdeeService;

    @Autowired
    private VoucherSyncLogRepository syncLogRepository;

    /**
     * 定时同步凭证（每小时执行）
     */
    @Scheduled(cron = "0 0 * * * ?")
    public void syncVouchersToFinance() {
        // 1. 查询待同步凭证
        List<Voucher> pendingVouchers = voucherRepository.findBySyncStatus(SyncStatus.PENDING);

        log.info("开始同步凭证，待同步数量: {}", pendingVouchers.size());

        int successCount = 0;
        int failCount = 0;

        for (Voucher voucher : pendingVouchers) {
            try {
                // 2. 推送到财务系统
                KingdeeResult result = kingdeeService.pushVoucher(voucher);

                if (result.isSuccess()) {
                    // 3. 更新同步状态
                    voucher.setSyncStatus(SyncStatus.SUCCESS);
                    voucher.setFinanceVoucherNo(result.getVoucherNo());
                    voucher.setSyncTime(LocalDateTime.now());
                    voucherRepository.save(voucher);

                    successCount++;
                } else {
                    handleSyncFailure(voucher, result.getMessage());
                    failCount++;
                }
            } catch (Exception e) {
                handleSyncFailure(voucher, e.getMessage());
                failCount++;
            }
        }

        log.info("凭证同步完成，成功: {}，失败: {}", successCount, failCount);
    }

    /**
     * 处理同步失败
     */
    private void handleSyncFailure(Voucher voucher, String errorMsg) {
        // 记录失败日志
        VoucherSyncLog log = new VoucherSyncLog();
        log.setVoucherNo(voucher.getVoucherNo());
        log.setErrorMessage(errorMsg);
        log.setRetryCount(voucher.getRetryCount() + 1);
        log.setCreatedAt(LocalDateTime.now());
        syncLogRepository.save(log);

        // 更新重试次数
        voucher.setRetryCount(voucher.getRetryCount() + 1);

        // 超过3次标记为失败
        if (voucher.getRetryCount() >= 3) {
            voucher.setSyncStatus(SyncStatus.FAILED);
        }

        voucherRepository.save(voucher);
    }

    /**
     * 重试失败凭证（每天凌晨执行）
     */
    @Scheduled(cron = "0 0 2 * * ?")
    public void retryFailedVouchers() {
        List<Voucher> failedVouchers = voucherRepository.findBySyncStatusAndRetryCountLessThan(
            SyncStatus.FAILED, 5);

        for (Voucher voucher : failedVouchers) {
            voucher.setSyncStatus(SyncStatus.PENDING);
            voucherRepository.save(voucher);
        }

        log.info("重置失败凭证数量: {}", failedVouchers.size());
    }
}

/**
 * 利润表生成服务
 */
@Service
public class ProfitStatementService {

    @Autowired
    private VoucherItemRepository voucherItemRepository;

    /**
     * 生成利润表
     */
    public ProfitStatement generateProfitStatement(String entityCode, String period) {
        ProfitStatement statement = new ProfitStatement();
        statement.setEntityCode(entityCode);
        statement.setPeriod(period);

        // 1. 营业收入
        BigDecimal amazonRevenue = getAccountBalance("6001.01", period);
        BigDecimal ebayRevenue = getAccountBalance("6001.02", period);
        BigDecimal shopifyRevenue = getAccountBalance("6001.03", period);
        BigDecimal otherRevenue = getAccountBalance("6001.99", period);

        statement.setAmazonRevenue(amazonRevenue);
        statement.setEbayRevenue(ebayRevenue);
        statement.setShopifyRevenue(shopifyRevenue);
        statement.setOtherRevenue(otherRevenue);
        statement.setTotalRevenue(amazonRevenue.add(ebayRevenue)
            .add(shopifyRevenue).add(otherRevenue));

        // 2. 营业成本
        BigDecimal productCost = getAccountBalance("6401.01", period);
        BigDecimal logisticsCost = getAccountBalance("6401.02", period);
        BigDecimal platformFee = getAccountBalance("6401.03", period);

        statement.setProductCost(productCost);
        statement.setLogisticsCost(logisticsCost);
        statement.setPlatformFee(platformFee);
        statement.setTotalCost(productCost.add(logisticsCost).add(platformFee));

        // 3. 毛利
        statement.setGrossProfit(statement.getTotalRevenue().subtract(statement.getTotalCost()));

        // 4. 期间费用
        BigDecimal sellingExpense = getAccountBalance("6601", period);
        BigDecimal adminExpense = getAccountBalance("6602", period);
        BigDecimal financeExpense = getAccountBalance("6603", period);
        BigDecimal exchangeGainLoss = getAccountBalance("6603.02", period);

        statement.setSellingExpense(sellingExpense);
        statement.setAdminExpense(adminExpense);
        statement.setFinanceExpense(financeExpense);
        statement.setExchangeGainLoss(exchangeGainLoss);
        statement.setTotalExpense(sellingExpense.add(adminExpense).add(financeExpense));

        // 5. 营业利润
        statement.setOperatingProfit(statement.getGrossProfit().subtract(statement.getTotalExpense()));

        // 6. 净利润（简化处理，实际需考虑所得税）
        BigDecimal taxRate = new BigDecimal("0.25");
        BigDecimal incomeTax = statement.getOperatingProfit().multiply(taxRate);
        statement.setIncomeTax(incomeTax);
        statement.setNetProfit(statement.getOperatingProfit().subtract(incomeTax));

        return statement;
    }

    /**
     * 获取科目期间余额
     */
    private BigDecimal getAccountBalance(String accountCode, String period) {
        return voucherItemRepository.sumByAccountAndPeriod(accountCode, period);
    }
}

/**
 * 渠道利润分析服务
 */
@Service
public class ChannelProfitAnalysisService {

    /**
     * 生成渠道利润分析报表
     */
    public List<ChannelProfitDTO> analyzeChannelProfit(String period) {
        List<ChannelProfitDTO> results = new ArrayList<>();

        List<String> channels = Arrays.asList("AMAZON_US", "AMAZON_EU", "EBAY", "SHOPIFY");

        for (String channel : channels) {
            ChannelProfitDTO dto = new ChannelProfitDTO();
            dto.setChannel(channel);
            dto.setPeriod(period);

            // 收入
            BigDecimal revenue = orderRepository.sumRevenueByChannelAndPeriod(channel, period);
            dto.setRevenue(revenue);

            // 商品成本
            BigDecimal productCost = orderRepository.sumProductCostByChannelAndPeriod(channel, period);
            dto.setProductCost(productCost);

            // 物流成本
            BigDecimal logisticsCost = orderRepository.sumLogisticsCostByChannelAndPeriod(channel, period);
            dto.setLogisticsCost(logisticsCost);

            // 平台费用
            BigDecimal platformFee = orderRepository.sumPlatformFeeByChannelAndPeriod(channel, period);
            dto.setPlatformFee(platformFee);

            // 广告费用
            BigDecimal adCost = adRepository.sumCostByChannelAndPeriod(channel, period);
            dto.setAdCost(adCost);

            // 计算毛利和净利
            BigDecimal grossProfit = revenue.subtract(productCost).subtract(logisticsCost).subtract(platformFee);
            dto.setGrossProfit(grossProfit);
            dto.setGrossMargin(grossProfit.divide(revenue, 4, RoundingMode.HALF_UP));

            BigDecimal netProfit = grossProfit.subtract(adCost);
            dto.setNetProfit(netProfit);
            dto.setNetMargin(netProfit.divide(revenue, 4, RoundingMode.HALF_UP));

            results.add(dto);
        }

        return results;
    }
}

/**
 * 汇兑损益分析报表
 */
@Service
public class ExchangeGainLossReportService {

    /**
     * 生成汇兑损益分析报表
     */
    public ExchangeGainLossReport generateReport(String period) {
        ExchangeGainLossReport report = new ExchangeGainLossReport();
        report.setPeriod(period);

        // 1. 按币种统计
        List<CurrencyGainLossDTO> byCurrency = new ArrayList<>();
        List<String> currencies = Arrays.asList("USD", "EUR", "GBP", "JPY");

        for (String currency : currencies) {
            CurrencyGainLossDTO dto = new CurrencyGainLossDTO();
            dto.setCurrency(currency);

            // 应收汇兑损益
            BigDecimal receivableGainLoss = receivableRepository
                .sumExchangeGainLossByCurrencyAndPeriod(currency, period);
            dto.setReceivableGainLoss(receivableGainLoss);

            // 应付汇兑损益
            BigDecimal payableGainLoss = payableRepository
                .sumExchangeGainLossByCurrencyAndPeriod(currency, period);
            dto.setPayableGainLoss(payableGainLoss);

            // 银行存款汇兑损益
            BigDecimal bankGainLoss = bankAccountRepository
                .sumExchangeGainLossByCurrencyAndPeriod(currency, period);
            dto.setBankGainLoss(bankGainLoss);

            dto.setTotalGainLoss(receivableGainLoss.add(payableGainLoss).add(bankGainLoss));

            byCurrency.add(dto);
        }

        report.setByCurrency(byCurrency);

        // 2. 汇总
        BigDecimal totalGainLoss = byCurrency.stream()
            .map(CurrencyGainLossDTO::getTotalGainLoss)
            .reduce(BigDecimal.ZERO, BigDecimal::add);
        report.setTotalGainLoss(totalGainLoss);

        return report;
    }
}