asd.sql

create function check_bank_transactions(startdt timestamp without time zone, enddt timestamp without time zone) returns TABLE(order_id integer, order_amount numeric, transaction_amount numeric)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    WITH orders_all_prev AS (
        SELECT o.id AS order_id, 
               o.order_status_id, 
               o.parent_id, 
               CASE
                  WHEN o.order_status_id = 8 THEN 0
                  ELSE o.costall
               END AS costall, 
               o.delivery_from
        FROM orders o
        WHERE o.enabled = 1
              AND
              (o.order_status_id IN (2,6,7,9) OR (o.order_status_id = 8 AND o.is_payed = 1))
              AND
              o.payment_type_id = 2
    ),
    orders_grouped_prev AS (
        SELECT oap.parent_id, 
               SUM(oap.costall) AS costall
        FROM orders_all_prev oap
        GROUP BY oap.parent_id
    ),
    bt_prev AS (
        SELECT b.order_id, MAX(b.order_type) AS ot, SUM(b.transaction_amount) AS sm
        FROM bank_transactions b
        GROUP BY b.order_id
    ),
    final AS (
        SELECT oa.order_id,
               CAST(CASE
                       WHEN b.ot = 1 THEN oa.costall
                       WHEN b.ot = 2 THEN COALESCE(og.costall, oa.costall)
                       WHEN b.order_id IS NULL THEN oa.costall
                    END AS numeric) AS order_amount,
               COALESCE(b.sm, 0) AS transaction_amount
        FROM orders_all_prev oa
             LEFT JOIN bt_prev b
                        ON b.order_id = oa.order_id
             LEFT JOIN orders_grouped_prev og
                        ON og.parent_id = oa.order_id
             LEFT JOIN orders_grouped_prev og_check
                        ON og_check.parent_id = oa.parent_id
        WHERE oa.delivery_from BETWEEN startdt AND enddt
              AND
              ((b.ot = 1 AND oa.order_status_id IN (7,9) AND b.sm <> oa.costall)
               OR
               (b.ot = 2 AND b.sm <> COALESCE(og.costall, oa.costall))
               OR
               b.order_id IS NULL)
              AND
              (oa.order_id = og_check.parent_id OR og_check.parent_id IS NULL)
    )
    SELECT f.order_id, f.order_amount, f.transaction_amount
    FROM final f
    WHERE f.order_amount <> 0
    ORDER BY f.order_id;
END;
$$;