update_old_data.sql

# 刷新应收款基准币种
UPDATE ecw_receivable er
SET base_currency_id = (SELECT er.import_currency5
                        FROM ecw_region er
                        WHERE er.id = (SELECT eoo.objective_country_id
                                       FROM ecw_order_objective eoo
                                       WHERE eoo.order_id = er.order_id))
WHERE base_currency_id IS NULL;

# 刷新银行明细流水号
UPDATE ecw_receipt_item AS t1
    JOIN (
        SELECT
            id,
            CONCAT(
                    'YHMX',
                    DATE_FORMAT(create_time, '%Y%m%d'),
                    LPAD(ROW_NUMBER() OVER (PARTITION BY DATE(create_time) ORDER BY create_time), 5, '0')
            ) AS new_serial_number
        FROM ecw_receipt_item
    ) AS t2
    ON t1.id = t2.id
SET t1.serial_number = t2.new_serial_number
WHERE t1.serial_number IS NULL;

# 刷新银行明细审核通过时间
UPDATE ecw_receipt_item
SET approval_time = update_time
WHERE approval_time IS NULL AND `status` = 1;

# 刷新银行明细汇率，银行收款明细-币种≠应收所在自编号对应的核算币种，但是跟收款单-应收费用的币种相同
UPDATE ecw_receipt_item eri
SET eri.rate = (
    SELECT era.write_off_rate
    FROM ecw_receipt_account era
    WHERE eri.receipt_id = era.receipt_id
      AND era.currency_id = eri.currency_id
      AND era.deleted = 0
      AND era.write_off_rate IS NOT NULL
)
WHERE eri.`status` = 1
  AND eri.deleted = 0
  AND EXISTS (
    SELECT 1
    FROM ecw_receipt_account era
    WHERE eri.receipt_id = era.receipt_id
      AND era.currency_id = eri.currency_id
      AND era.deleted = 0
      AND era.write_off_rate IS NOT NULL
);
# 查询银行收款明细-币种≠应收所在自编号对应的核算币种，跟收款单-应收费用的币种也无法匹配
SELECT
    eri.id,
    eri.serial_number,
    eri.receipt_id,
    era.id aid,
    eri.currency_id ic,
    era.currency_id ac,
    era.write_off_rate
FROM
    ecw_receipt_item eri
        LEFT JOIN ecw_receipt_account era ON eri.receipt_id = era.receipt_id
        AND eri.currency_id = era.currency_id
        AND era.deleted = 0
WHERE
    eri.`status` = 1
  AND eri.deleted = 0
  AND era.id IS NULL;
# 刷新银行收款明细-币种≠应收所在自编号对应的核算币种，跟收款单-应收费用的币种也无法匹配
UPDATE ecw_receipt_item eri
    JOIN (
        SELECT
            eri.id
        FROM
            ecw_receipt_item eri
                LEFT JOIN ecw_receipt_account era
                          ON eri.receipt_id = era.receipt_id
                              AND eri.currency_id = era.currency_id
                              AND era.deleted = 0
        WHERE
            eri.`status` = 1
          AND eri.deleted = 0
          AND era.id IS NULL
    ) AS to_update ON eri.id = to_update.id
SET eri.rate = IFNULL((
                          SELECT eer.currency_rate
                          FROM ecw_exchange_rate eer
                          WHERE eer.source_currency_id = eri.currency_id
                            AND eer.target_currency_id = eri.write_off_currency_id
                      ),1);

# 刷新银行明细审核核销币种
UPDATE ecw_receipt_item SET write_off_currency_id = 1 WHERE write_off_currency_id IS NULL;

UPDATE ecw_receipt SET generate_path = 0 WHERE generate_path IS NULL;

# 刷新应收款优惠金额
UPDATE ecw_receivable er
SET er.discount_total = 0 WHERE er.discount_total IS NULL;

# 刷新应收款汇率

# 应收明细币种=基准币种
UPDATE ecw_receivable er
SET er.exchange_rate = 1 WHERE er.exchange_rate IS NULL AND er.receipt_id IS NOT NULL AND er.currency_id = er.base_currency_id AND er.deleted = 0;
# 应收明细基准币种是美元
UPDATE ecw_receivable er
SET er.exchange_rate = ( SELECT era.write_off_rate FROM ecw_receipt_account era WHERE era.receipt_id = er.receipt_id AND era.currency_id = er.currency_id AND era.deleted = 0)
WHERE
    er.receipt_id IS NOT NULL AND er.base_currency_id = 1 AND er.deleted = 0;
# 应收明细基准币种不是美元
UPDATE ecw_receivable er
SET er.exchange_rate = (SELECT eer.currency_rate FROM ecw_exchange_rate eer WHERE eer.source_currency_id = er.currency_id AND eer.target_currency_id = er.base_currency_id )
WHERE er.exchange_rate IS NULL AND er.receipt_id IS NOT NULL AND er.base_currency_id != 1 AND er.deleted = 0;


# 刷新应收款基准金额
UPDATE ecw_receivable er
SET er.base_amount = er.exchange_rate * (er.tax_amount - er.discount_total)
WHERE er.base_amount IS NULL AND er.receipt_id IS NOT NULL AND er.exchange_rate IS NOT NULL;

# 修改收款单已核销待开票状态
UPDATE `ecw_receipt` SET `state` = 4 WHERE `state` = 5;

# 刷新应收明细账户ID
UPDATE ecw_receipt_item eri SET eri.account_id = (SELECT eba.id FROM ecw_bank_account eba WHERE eba.ba_account_name = eri.account_name AND eba.ba_bank_name = eri.account_bank_name AND eba.ba_account_num = eri.account_no AND eba.deleted = 0) WHERE eri.deleted = 0;


# 刷新收款明细-应收明细-收入归属
UPDATE ecw_receivable_write_off_record erwor SET erwor.income_belong =
                                                     (SELECT eba.ba_income_belong FROM ecw_receipt_item eri LEFT JOIN ecw_bank_account eba ON eri.account_id = eba.id WHERE erwor.receipt_item_id = eri.id);