Post-Race Reports Functional Requirements

SELECT p.id                                         AS product_id,
       p.name                                       AS product,
       oli.price + COALESCE(oli.discount, 0)        AS unit_price,
       COUNT(*)                                     AS qty,
       SUM(oli.price + COALESCE(oli.discount, 0))   AS price,
       SUM(COALESCE(oli.discount, 0))               AS discount,
       SUM(COALESCE(oli.net, oli.price))            AS sub_total
FROM order_line_item oli
JOIN sales_order so       ON so.id = oli.order_id
JOIN event_participant ep ON ep.id = oli.event_participant_id
JOIN product p            ON p.id = oli.product_id
WHERE ep.event_id = :eventId
  AND so.status = 'P'
GROUP BY p.id, p.name, oli.price + COALESCE(oli.discount, 0)
ORDER BY p.name, unit_price;

SELECT so.id                         AS order_id,
       so.number                     AS order_number,
       so.payment_date_time          AS paid_on,
       (SELECT SUM(COALESCE(l.net, l.price))
          FROM order_line_item l WHERE l.order_id = so.id) AS order_lines_total,
       so.discount                   AS order_discount,
       (SELECT SUM(COALESCE(l.discount, 0))
          FROM order_line_item l WHERE l.order_id = so.id) AS on_lines,
       GREATEST(0, so.discount - (SELECT SUM(COALESCE(l.discount, 0))
                                    FROM order_line_item l WHERE l.order_id = so.id)) AS order_expense,
       EXISTS (SELECT 1
                 FROM order_line_item l
                 LEFT JOIN event_participant ep ON ep.id = l.event_participant_id
                WHERE l.order_id = so.id
                  AND (ep.event_id IS NULL OR ep.event_id <> :eventId)) AS spans_other_events
FROM sales_order so
WHERE so.status = 'P'
  AND COALESCE(so.discount, 0) <> 0
  AND EXISTS (SELECT 1
                FROM order_line_item l
                JOIN event_participant ep ON ep.id = l.event_participant_id
               WHERE l.order_id = so.id AND ep.event_id = :eventId)
ORDER BY so.payment_date_time, so.id;

SELECT li.paid_orders,
       li.line_items,
       li.price                                  AS line_price,
       li.line_discount,
       li.line_net                               AS line_sub_total,
       od.order_discount,
       od.refunds,
       li.line_net - od.order_discount - od.refunds AS net_income
FROM (SELECT COUNT(DISTINCT so.id)                                   AS paid_orders,
             COUNT(*)                                                AS line_items,
             COALESCE(SUM(oli.price + COALESCE(oli.discount, 0)), 0) AS price,
             COALESCE(SUM(COALESCE(oli.discount, 0)), 0)             AS line_discount,
             COALESCE(SUM(COALESCE(oli.net, oli.price)), 0)          AS line_net
        FROM order_line_item oli
        JOIN sales_order so       ON so.id = oli.order_id
        JOIN event_participant ep ON ep.id = oli.event_participant_id
       WHERE ep.event_id = :eventId AND so.status = 'P') li
CROSS JOIN
     (SELECT COALESCE(SUM(GREATEST(0, so.discount - (SELECT SUM(COALESCE(l.discount, 0))
                                                        FROM order_line_item l
                                                       WHERE l.order_id = so.id))), 0) AS order_discount,
             COALESCE(SUM(so.refund_amount), 0) AS refunds
        FROM sales_order so
       WHERE so.status = 'P'
         AND EXISTS (SELECT 1
                       FROM order_line_item l
                       JOIN event_participant ep ON ep.id = l.event_participant_id
                      WHERE l.order_id = so.id AND ep.event_id = :eventId)) od;

SELECT COUNT(*)                                     AS qty,
       COALESCE(SUM(COALESCE(oli.net, oli.price)), 0) AS amount
FROM order_line_item oli
JOIN sales_order so       ON so.id = oli.order_id
JOIN event_participant ep ON ep.id = oli.event_participant_id
JOIN event e              ON e.id = ep.event_id
LEFT JOIN series s        ON s.id = e.series_id
WHERE ep.event_id = :eventId
  AND so.status = 'P'
  AND oli.product_id = COALESCE(e.product_day_license_id, s.product_day_license_id);

SELECT ep.id                                          AS event_participant_id,
       ep.first_name,
       ep.last_name,
       NULLIF(ep.identity_number, '')                 AS id_number,
       COALESCE(NULLIF(u.csa_id, ''), csa.meta_value) AS csa_id,
       so.number                                      AS order_number,
       COALESCE(oli.net, oli.price)                   AS amount_paid
FROM order_line_item oli
JOIN sales_order so       ON so.id = oli.order_id
JOIN event_participant ep ON ep.id = oli.event_participant_id
JOIN event e              ON e.id = ep.event_id
LEFT JOIN series s        ON s.id = e.series_id
LEFT JOIN wp_users u      ON u.ID = ep.person_id
LEFT JOIN wp_usermeta csa ON csa.user_id = ep.person_id
                         AND csa.meta_key = 'csa_id'
                         AND csa.meta_value <> ''
WHERE ep.event_id = :eventId
  AND so.status = 'P'
  AND oli.product_id = COALESCE(e.product_day_license_id, s.product_day_license_id)
  AND EXISTS (SELECT 1
                FROM race_result rr
                JOIN result_set rs ON rs.id = rr.result_set_id
               WHERE rs.event_id = ep.event_id
                 AND rs.active = 'Y'
                 AND rr.event_participant_id = ep.id
                 AND (rr.position IS NOT NULL OR rr.status IS NOT NULL)
                 AND COALESCE(rr.status, 'FIN') <> 'DNS')
ORDER BY ep.last_name, ep.first_name;