--
-- ec_tax/3
--
create or replace function ec_tax(
  numeric,
  numeric,
  integer
) returns numeric as $$

DECLARE
	v_price			alias for $1;
	v_shipping		alias for $2;
	v_order_id		alias for $3;
        taxes                   ec_sales_tax_by_state%ROWTYPE;
        tax_exempt_p            ec_orders.tax_exempt_p%TYPE;
BEGIN
        SELECT into tax_exempt_p tax_exempt_p 
        FROM ec_orders
        WHERE order_id = v_order_id;

        IF tax_exempt_p = 't' THEN
                return 0;
        END IF; 
        
        --SELECT t.* into taxes
        --FROM ec_orders o, ec_addresses a, ec_sales_tax_by_state t
        --WHERE o.shipping_address=a.address_id
        --AND a.usps_abbrev=t.usps_abbrev(+)
        --AND o.order_id=v_order_id;

        SELECT into taxes t.* 
	FROM ec_orders o
	    JOIN 
	ec_addresses a on (o.shipping_address=a.address_id)
	    LEFT JOIN
	ec_sales_tax_by_state t using (usps_abbrev)
	WHERE o.order_id=v_order_id;
	

        IF coalesce(taxes.shipping_p,'f') = 'f' THEN
                return coalesce(taxes.tax_rate,0) * v_price;
        ELSE
                return coalesce(taxes.tax_rate,0) * (v_price + v_shipping);
        END IF;
END;$$ language plpgsql;