Forum OpenACS Q&A: Response to Ecommerce with expired unconfirmed orders

Collapse
Posted by Gaizka Villate on
I think you're right, and that can happen with a production site.
<p>
I've tried a workaround. I think i've read somewhere that a plpgsql should return null when one of its arguments is null (is it right?), so i redefined that function as follows:

<pre>
create function one_if_within_n_days (datetime, integer)
returns integer as '
declare
        query_date alias for $1;
        n_days alias for $2;
begin
  IF query_date is null
      THEN return null;
  END if;
  IF current_timestamp::date - query_date::date <= n_days THEN
    return 1;
  ELSE
    return 0;
  END IF;
end;
' language 'plpgsql';
</pre>

It doesn't raise an error now:
<pre>
test2=# select one_if_within_n_days(null,1);
one_if_within_n_days
-------------------------

(1 row)
</pre>
And then, in <code>/admin/ecommerce/index.tcl</code> you can use:
<pre>
select
  sum(coalesce(one_if_within_n_days(confirmed_date,1),0) as n_in_last_24_hours,
  sum(coalesce(one_if_within_n_days(confirmed_date,7),0) as n_in_last_7_days
from ec_orders_reportable
</pre>
<p>
<p>
I think this is the correct behaviour. Im i wrong?