--
-- ec_cert_unshipped_one_order/2
--
create or replace function ec_cert_unshipped_one_order(
  integer,
  integer
) returns numeric as $$

DECLARE
	v_gift_certificate_id	alias for $1;	
	v_order_id		alias for $2;
        total_shipment_cost     numeric;
        earlier_certs           numeric;
        total_tied_amount       numeric;
BEGIN
        select coalesce(sum(coalesce(ec_shipment_price(shipment_id),0) + coalesce(ec_shipment_shipping(shipment_id),0) + coalesce(ec_shipment_tax(shipment_id),0)),0) into total_shipment_cost
        from ec_shipments
        where order_id=v_order_id;

        earlier_certs := ec_earlier_certs_on_one_order(v_gift_certificate_id, v_order_id);

        IF total_shipment_cost <= earlier_certs THEN
                total_tied_amount := ec_one_gift_cert_on_one_order(v_gift_certificate_id, v_order_id);
        ELSE
	    IF total_shipment_cost > earlier_certs + ec_one_gift_cert_on_one_order(v_gift_certificate_id, v_order_id) THEN
                total_tied_amount := 0;
            ELSE
                total_tied_amount := ec_one_gift_cert_on_one_order(v_gift_certificate_id, v_order_id) - (total_shipment_cost - earlier_certs);
	    END IF;
        END IF;

        RETURN total_tied_amount;               
END;$$ language plpgsql;