Forum OpenACS Q&A: Ecommerce with expired unconfirmed orders

This query:
select
  sum(one_if_within_n_days(confirmed_date,1)) as n_in_last_24_hours,
  sum(one_if_within_n_days(confirmed_date,7)) as n_in_last_7_days
from ec_orders_reportable
in www/admin/ecommerce/index.tcl returns an error: Unable to convert null timestamp to date The confirmed_date field is NULL because the orders were not confirmed. This error appeared after test orders I entered in were never processed. This could happen with a production site unless I am mistaken. Should it work this way?
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?

Collapse
Posted by Don Baccus on
Your solution will work fine in PG7.1 but not PG7.0, which doesn't
handle nulls in function parameter lists correctly.

Just another reason to upgrade to PG7.1, right? :)

Collapse
Posted by Dave Bauer on
Sure. My system is based on a checkout of CVS from November or December. Is there anything I have to change to upgrade to 7.1 in OpenACS besides tweaking the pgdump? I have modified almost every ecommerce file.