Script to generate a Custom Order Fees report

generate-custom-fees-report.php

<?php

/** @noinspection ALL */

use Magento\Framework\App\Bootstrap;
use Magento\Framework\App\Http;
use Magento\Framework\App\ResourceConnection;
use Magento\Framework\App\State;

require_once '/var/www/html/app/bootstrap.php';

$bootstrap = Bootstrap::create(BP, $_SERVER);
$objectManager = $bootstrap->getObjectManager();
$state = $objectManager->get(State::class);

$state->setAreaCode('adminhtml');

$resource = $objectManager->get(ResourceConnection::class);
$connection = $resource->getConnection();

$version = $connection->query('SELECT VERSION();')->fetchColumn();

preg_match('/^[\d.]+/', $version, $matches);

$isCompatible = version_compare($matches[0], '8.0.0', '>=') || version_compare($matches[0], '10.6.0', '>=');

if (!$isCompatible) {
    die('MySQL 8.x or MariaDB 10.6.x is required.');
}

$customOrderFeesTable = $resource->getTableName('custom_order_fees');
$orderTable = $resource->getTableName('sales_order');
$query = <<<SQL
SELECT
    DATE(so.created_at) AS 'Order Created At',
    fee.title AS 'Fee Name',
    SUM(fee.base_value) AS 'Base Total Amount',
    SUM(fee.value) AS 'Total Amount'
FROM {$customOrderFeesTable} AS cof
JOIN JSON_TABLE(
    cof.custom_fees,
    '$' COLUMNS (
        NESTED PATH '$.*' COLUMNS (
            title VARCHAR(255) PATH '$.title',
            value DECIMAL(10, 4) PATH '$.value',
            base_value DECIMAL(10, 4) PATH '$.base_value'
        )
    )
) AS fee
LEFT JOIN {$orderTable} AS so ON so.entity_id = cof.order_entity_id
GROUP BY DATE(so.created_at), fee.title;
SQL;
$result = $connection->query($query)->fetchAll();
$file = fopen('php://stdout', 'w');

fputcsv($file, array_keys($result[0]));

array_walk(
    $result,
    static function (array $row) use ($file): void {
        fputcsv($file, $row);
    }
);

fclose($file);

Example output:

"Order Created At","Fee Name","Base Total Amount","Total Amount"
2025-03-10,"Environmental Fee",5.0000,3.5335
2025-03-10,"Processing Fee",2.5000,1.7668
2025-03-21,"Environmental Fee",5.0000,5.0000
2025-03-21,"Processing Fee",2.5000,2.5000
2025-04-15,"Environmental Fee",10.0000,10.0000
2025-04-15,"Processing Fee",5.0000,5.0000
2025-04-18,"Environmental Fee",5.0000,5.0000
2025-04-18,"Processing Fee",2.5000,2.5000