--
-- ec_total_tax/1
--
create or replace function ec_total_tax(
  integer
) returns numeric as $$

DECLARE
	v_order_id			alias for $1;
        order_tax               	numeric;
        item_price_tax          	numeric;
        item_shipping_tax       	numeric;
BEGIN
        select into order_tax
        coalesce(shipping_tax_charged,0) - coalesce(shipping_tax_refunded,0)
        FROM ec_orders
        WHERE order_id=v_order_id;

        select into item_price_tax
        coalesce(sum(price_tax_charged),0) - coalesce(sum(price_tax_refunded),0)
        FROM ec_items
        WHERE order_id=v_order_id
        and item_state <> 'void';

        select into item_shipping_tax
        coalesce(sum(shipping_tax_charged),0) - coalesce(sum(shipping_tax_refunded),0)
        FROM ec_items
        WHERE order_id=v_order_id;

        return order_tax + item_price_tax + item_shipping_tax;
END;$$ language plpgsql;