GC promo report config example

gc_promo_report.yaml

gc_promo_report:
    reports:
        entries:
            name: 'Entries report'
            description: 'This report shows all promotion entries.'
            sql: "SELECT * FROM gc_promo_entry ORDER BY id ASC;"
        bank-transfer-payment-report:
            name: 'Bank transfer payment report'
            description: 'This report shows all approved cash prize claims.'
            sql: |
                "SELECT
                    @rownum := @rownum + 1 as ac_code,
                    CONCAT(e.first_name, ' ', e.last_name) AS  customer_name,
                    REPLACE(UPPER(e.bank_account_sort_code), ' ', '') AS BIC,
                    REPLACE(UPPER(e.bank_account_number), ' ', '') AS IBAN,
                    e.payment_price AS customer_amount,
                    e.id AS entry_id
                    FROM gc_promo_entry e
                    cross join (select @rownum := 0) r
                    WHERE e.status = 'Approved'
                    AND e.payment_preference = 'BankTransfer'
                    ORDER BY e.status_approved_at DESC;"