[webco-beta] Data structure in 3.1

[David Cramton]

--Apple-Mail-3-975174406
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=ISO-8859-1;
	format=flowed

Hello.

As you may or may not know, at Evergreen we bill differently than is=20
allowed by WCO.  To that end I have built a script that accesses the=20
data in WCO's tables and gives us useful output.  I understand that the=20=

data changes in 3.1 such that I may not be able to use my existing=20
script to bill.  Can someone please tell me, based on the attached view=20=

creation statement, if this view, and the script and billing process=20
built on it, can be used with 3.1, and if not, could you perhaps=20
suggest fixes or alternatives?  Changing our billing/business=20
methodology is NOT currently an available alternative.

View description & text:

The view returns a single record per allocation (checkout session) with=20=

the maximum number of days late for any item in that allocation only=20
for allocations where no item has a status of "WCOF:CHECKOUT" (items=20
not yet returned);

IOW, it only returns a record when all items have been returned. Only=20
then it returns the number of days the most over-due item was returned.

The view relies on the following base tables and assumptions:
=A0=A0=A0=A0=A0=A0=A0 In table person

A person is uniquly identified by person_oid

In table allocation

An allocation is uniquly identified by allocation_oid
All allocations have a valid person (patron)

In table resource_temporal

All resource_temporal records have a valid allocation
Until a resource is returned, the value of event_sym is 'WCOF:CHECKOUT'=20=

for an item that has been checked out.
After an item is checked in, event_etime is the date/time the item was=20=

returned.

The postgresql function age returns a interval in the requested units=20
that is the difference between two dates.
The postgresql function EPOCH returns the number of seconds in an=20
interval.
There are 86400 seconds in one day.

If the view is dropped by an upgrade or accidentally it can be=20
recreated with the DDL statements that follow.
The view contains no actual data and can be dropped and recreated at=20
will.

James


Object DDL
-----------------------------------------------------------------
drop view tesc_late_days_by_allocation;


-----------------------------------------------------------------
create view tesc_late_days_by_allocation
as
select p.userid, p.first_name, p.last_name, a.allocation_oid,
=A0 trunc(extract(
=A0=A0=A0 EPOCH FROM
=A0=A0=A0 age(
=A0=A0=A0=A0=A0 date_trunc('day', rtm.event_etime),
=A0=A0=A0=A0=A0 date_trunc('day', a.end_time)
=A0=A0=A0 )
=A0 ) / 86400) as latedays
from
allocation a inner join person p
on a.patron_oid =3D p.person_oid
inner join(
=A0 select rt.ptr_oid, max(rt.event_etime) as event_etime
=A0 from resource_temporal rt
=A0 where not exists (
=A0=A0=A0 select * from resource_temporal rtx
=A0=A0=A0 where rt.ptr_oid =3D rtx.ptr_oid
=A0=A0=A0 and event_sym =3D 'WCOF:CHECKOUT')
=A0 group by rt.ptr_oid
) rtm
on a.allocation_oid =3D rtm.ptr_oid
where date_trunc('day', rtm.event_etime) >
date_trunc('day', a.end_time);


-----------------------------------------------------------------
grant select on tesc_late_days_by_allocation to odbc;




--Apple-Mail-3-975174406
Content-Transfer-Encoding: quoted-printable
Content-Type: text/enriched;
	charset=ISO-8859-1

Hello.


As you may or may not know, at Evergreen we bill differently than is
allowed by WCO.  To that end I have built a script that accesses the
data in WCO's tables and gives us useful output.  I understand that
the data changes in 3.1 such that I may not be able to use my existing
script to bill.  Can someone please tell me, based on the attached
view creation statement, if this view, and the script and billing
process built on it, can be used with 3.1, and if not, could you
perhaps suggest fixes or alternatives?  Changing our billing/business
methodology is NOT currently an available alternative.


View description & text:


<fontfamily><param>Arial</param><smaller>The view returns a single
record per allocation (checkout session) with the maximum number of
days late for any item in that allocation only for allocations where
no item has a status of "WCOF:CHECKOUT" (items not yet =
returned);</smaller></fontfamily>


<fontfamily><param>Arial</param><smaller>IOW, it only returns a record
when all items have been returned.</smaller></fontfamily>
<fontfamily><param>Arial</param><smaller>Only then it returns the
number of days the most over-due item was =
returned.</smaller></fontfamily>


<fontfamily><param>Arial</param><smaller>The view relies on the
following base tables and assumptions:

</smaller></fontfamily>=A0=A0=A0=A0=A0=A0=A0
<fontfamily><param>Arial</param><smaller>In table =
person</smaller></fontfamily>


<fontfamily><param>Arial</param><smaller>A person is uniquly
identified by person_oid</smaller></fontfamily>


<fontfamily><param>Arial</param><smaller>In table =
allocation</smaller></fontfamily>


<fontfamily><param>Arial</param><smaller>An allocation is uniquly
identified by allocation_oid

All allocations have a valid person (patron)</smaller></fontfamily>


<fontfamily><param>Arial</param><smaller>In table =
resource_temporal</smaller></fontfamily>


<fontfamily><param>Arial</param><smaller>All resource_temporal records
have a valid allocation

Until a resource is returned, the value of event_sym is
'WCOF:CHECKOUT' for an item that has been checked out.

After an item is checked in, event_etime is the date/time the item was
returned.</smaller></fontfamily>


<fontfamily><param>Arial</param><smaller>The postgresql function age
returns a interval in the requested units that is the difference
between two dates.

The postgresql function EPOCH returns the number of seconds in an
interval.

There are 86400 seconds in one day.</smaller></fontfamily>


<fontfamily><param>Arial</param><smaller>If the view is dropped by an
upgrade or accidentally it can be recreated with the DDL statements
that follow.

The view contains no actual data and can be dropped and recreated at
will.</smaller></fontfamily>


<fontfamily><param>Arial</param><smaller>James</smaller></fontfamily>



<fontfamily><param>Arial</param><smaller>Object DDL

</smaller></fontfamily><fixed><fontfamily><param>Courier =
New</param><smaller>------------------------------------------------------=
-----------

drop view tesc_late_days_by_allocation;</smaller></fontfamily></fixed>



<fixed><fontfamily><param>Courier =
New</param><smaller>------------------------------------------------------=
-----------

create view tesc_late_days_by_allocation

as

select p.userid, p.first_name, p.last_name, a.allocation_oid,

=A0 trunc(extract(

=A0=A0=A0 EPOCH FROM

=A0=A0=A0 age(

=A0=A0=A0=A0=A0 date_trunc('day', rtm.event_etime),

=A0=A0=A0=A0=A0 date_trunc('day', a.end_time)

=A0=A0=A0 )

=A0 ) / 86400) as latedays

from

allocation a inner join person p

on a.patron_oid =3D p.person_oid

inner join(

=A0 select rt.ptr_oid, max(rt.event_etime) as event_etime

=A0 from resource_temporal rt

=A0 where not exists (

=A0=A0=A0 select * from resource_temporal rtx

=A0=A0=A0 where rt.ptr_oid =3D rtx.ptr_oid

=A0=A0=A0 and event_sym =3D 'WCOF:CHECKOUT')

=A0 group by rt.ptr_oid

) rtm

on a.allocation_oid =3D rtm.ptr_oid

where date_trunc('day', rtm.event_etime) >

date_trunc('day', a.end_time);</smaller></fontfamily></fixed>



<fixed><fontfamily><param>Courier =
New</param><smaller>------------------------------------------------------=
-----------

grant select on tesc_late_days_by_allocation to =
odbc;</smaller></fontfamily></fixed>





--Apple-Mail-3-975174406--