-->
-<IDL xmlns="http://opensrf.org/spec/IDL/base/v1" xmlns:idl="http://opensrf.org/spec/IDL/base/v1" xmlns:oils_persist="http://open-ils.org/spec/opensrf/IDL/persistence/v1" xmlns:oils_obj="http://open-ils.org/spec/opensrf/IDL/objects/v1" xmlns:reporter="http://open-ils.org/spec/opensrf/IDL/reporter/v1" xmlns:permacrud="http://open-ils.org/spec/opensrf/IDL/permacrud/v1">
+<IDL xmlns="http://opensrf.org/spec/IDL/base/v1" xmlns:idl="http://opensrf.org/spec/IDL/base/v1" xmlns:oils_persist="http://open-ils.org/spec/opensrf/IDL/persistence/v1" xmlns:oils_obj="http://open-ils.org/spec/opensrf/IDL/objects/v1" xmlns:reporter="http://open-ils.org/spec/opensrf/IDL/reporter/v1" xmlns:sr="http://open-ils.org/spec/opensrf/IDL/simple-reporter/v1" xmlns:permacrud="http://open-ils.org/spec/opensrf/IDL/permacrud/v1">
<!-- Virtual classes -->
<class id="mups" controller="open-ils.cstore" oils_obj:fieldmapper="money::user_payment_summary" oils_persist:virtual="true" reporter:label="User Payment Summary">
</class>
- <class id="rof" controller="open-ils.reporter-store" oils_obj:fieldmapper="reporter::output_folder" oils_persist:tablename="reporter.output_folder" reporter:label="Output Folder">
+ <class id="rof" controller="open-ils.reporter-store open-ils.cstore open-ils.pcrud" oils_obj:fieldmapper="reporter::output_folder" oils_persist:tablename="reporter.output_folder" reporter:label="Output Folder">
<fields oils_persist:primary="id" oils_persist:sequence="reporter.output_folder_id_seq">
<field name="id" reporter:datatype="id" />
<field name="parent" reporter:datatype="link"/>
<field name="name" reporter:datatype="text"/>
<field name="shared" reporter:datatype="bool"/>
<field name="share_with" reporter:datatype="link"/>
+ <field name="simple_reporter" reporter:datatype="bool"/>
<field name="children" oils_persist:virtual="true" reporter:datatype="link"/>
<field name="outputs" oils_persist:virtual="true" reporter:datatype="link"/>
</fields>
<link field="share_with" reltype="has_a" key="id" map="" class="aou"/>
<link field="outputs" reltype="has_many" key="folder" map="" class="rs"/>
</links>
+ <permacrud xmlns="http://open-ils.org/spec/opensrf/IDL/permacrud/v1">
+ <actions>
+ <create permission="RUN_REPORTS RUN_SIMPLE_REPORTS">
+ <context link="owner" field="home_ou"/>
+ </create>
+ <retrieve permission="RUN_REPORTS RUN_SIMPLE_REPORTS">
+ <context link="owner" field="home_ou"/>
+ </retrieve>
+ <update permission="RUN_REPORTS RUN_SIMPLE_REPORTS">
+ <context link="owner" field="home_ou"/>
+ </update>
+ <delete permission="RUN_REPORTS RUN_SIMPLE_REPORTS">
+ <context link="owner" field="home_ou"/>
+ </delete>
+ </actions>
+ </permacrud>
</class>
- <class id="rtf" controller="open-ils.reporter-store" oils_obj:fieldmapper="reporter::template_folder" oils_persist:tablename="reporter.template_folder" reporter:label="Template Folder">
+ <class id="rtf" controller="open-ils.reporter-store open-ils.cstore open-ils.pcrud" oils_obj:fieldmapper="reporter::template_folder" oils_persist:tablename="reporter.template_folder" reporter:label="Template Folder">
<fields oils_persist:primary="id" oils_persist:sequence="reporter.template_folder_id_seq">
<field name="id" reporter:datatype="id" />
<field name="parent" reporter:datatype="link"/>
<field name="name" reporter:datatype="text"/>
<field name="shared" reporter:datatype="bool"/>
<field name="share_with" reporter:datatype="link"/>
+ <field name="simple_reporter" reporter:datatype="bool"/>
<field name="children" oils_persist:virtual="true" reporter:datatype="link"/>
<field name="templates" oils_persist:virtual="true" reporter:datatype="link"/>
</fields>
<link field="share_with" reltype="has_a" key="id" map="" class="aou"/>
<link field="templates" reltype="has_many" key="folder" map="" class="rt"/>
</links>
+ <permacrud xmlns="http://open-ils.org/spec/opensrf/IDL/permacrud/v1">
+ <actions>
+ <create permission="RUN_REPORTS RUN_SIMPLE_REPORTS">
+ <context link="owner" field="home_ou"/>
+ </create>
+ <retrieve permission="RUN_REPORTS RUN_SIMPLE_REPORTS">
+ <context link="owner" field="home_ou"/>
+ </retrieve>
+ <update permission="RUN_REPORTS RUN_SIMPLE_REPORTS">
+ <context link="owner" field="home_ou"/>
+ </update>
+ <delete permission="RUN_REPORTS RUN_SIMPLE_REPORTS">
+ <context link="owner" field="home_ou"/>
+ </delete>
+ </actions>
+ </permacrud>
</class>
- <class id="rrf" controller="open-ils.reporter-store" oils_obj:fieldmapper="reporter::report_folder" oils_persist:tablename="reporter.report_folder" reporter:label="Report Folder">
+ <class id="rrf" controller="open-ils.reporter-store open-ils.cstore open-ils.pcrud" oils_obj:fieldmapper="reporter::report_folder" oils_persist:tablename="reporter.report_folder" reporter:label="Report Folder">
<fields oils_persist:primary="id" oils_persist:sequence="reporter.report_folder_id_seq">
<field name="id" reporter:datatype="id" />
<field name="parent" reporter:datatype="link"/>
<field name="name" reporter:datatype="text"/>
<field name="shared" reporter:datatype="bool"/>
<field name="share_with" reporter:datatype="link"/>
+ <field name="simple_reporter" reporter:datatype="bool"/>
<field name="children" oils_persist:virtual="true" reporter:datatype="link"/>
<field name="reports" oils_persist:virtual="true" reporter:datatype="link"/>
</fields>
<link field="share_with" reltype="has_a" key="id" map="" class="aou"/>
<link field="reports" reltype="has_many" key="folder" map="" class="rr"/>
</links>
+ <permacrud xmlns="http://open-ils.org/spec/opensrf/IDL/permacrud/v1">
+ <actions>
+ <create permission="RUN_REPORTS RUN_SIMPLE_REPORTS">
+ <context link="owner" field="home_ou"/>
+ </create>
+ <retrieve permission="RUN_REPORTS RUN_SIMPLE_REPORTS">
+ <context link="owner" field="home_ou"/>
+ </retrieve>
+ <update permission="RUN_REPORTS RUN_SIMPLE_REPORTS">
+ <context link="owner" field="home_ou"/>
+ </update>
+ <delete permission="RUN_REPORTS RUN_SIMPLE_REPORTS">
+ <context link="owner" field="home_ou"/>
+ </delete>
+ </actions>
+ </permacrud>
</class>
<class id="rt" controller="open-ils.reporter-store open-ils.cstore open-ils.pcrud" oils_obj:fieldmapper="reporter::template" oils_persist:tablename="reporter.template" reporter:label="Template">
<fields oils_persist:primary="id" oils_persist:sequence="reporter.template_id_seq">
</links>
<permacrud xmlns="http://open-ils.org/spec/opensrf/IDL/permacrud/v1">
<actions>
- <create permission="RUN_REPORTS" owning_user="owner" global_required="true"/>
- <retrieve permission="RUN_REPORTS" owning_user="owner" global_required="true"/>
- <update permission="RUN_REPORTS" owning_user="owner" global_required="true"/>
- <delete permission="RUN_REPORTS" owning_user="owner" global_required="true"/>
+ <create permission="RUN_REPORTS RUN_SIMPLE_REPORTS">
+ <context link="owner" field="home_ou"/>
+ </create>
+ <retrieve permission="RUN_REPORTS RUN_SIMPLE_REPORTS">
+ <context link="owner" field="home_ou"/>
+ </retrieve>
+ <update permission="RUN_REPORTS RUN_SIMPLE_REPORTS">
+ <context link="owner" field="home_ou"/>
+ </update>
+ <delete permission="RUN_REPORTS RUN_SIMPLE_REPORTS">
+ <context link="owner" field="home_ou"/>
+ </delete>
</actions>
</permacrud>
</class>
- <class id="rr" controller="open-ils.reporter-store" oils_obj:fieldmapper="reporter::report" oils_persist:tablename="reporter.report" reporter:label="Report">
+ <class id="rr" controller="open-ils.reporter-store open-ils.cstore open-ils.pcrud" oils_obj:fieldmapper="reporter::report" oils_persist:tablename="reporter.report" reporter:label="Report">
<fields oils_persist:primary="id" oils_persist:sequence="reporter.report_id_seq">
<field name="id" reporter:datatype="id" />
<field name="owner" reporter:datatype="link"/>
<link field="folder" reltype="has_a" key="id" map="" class="rrf"/>
<link field="runs" reltype="has_many" key="report" map="" class="rs"/>
</links>
+ <permacrud xmlns="http://open-ils.org/spec/opensrf/IDL/permacrud/v1">
+ <actions>
+ <create permission="RUN_REPORTS RUN_SIMPLE_REPORTS">
+ <context link="owner" field="home_ou"/>
+ </create>
+ <retrieve permission="RUN_REPORTS RUN_SIMPLE_REPORTS">
+ <context link="owner" field="home_ou"/>
+ </retrieve>
+ <update permission="RUN_REPORTS RUN_SIMPLE_REPORTS">
+ <context link="owner" field="home_ou"/>
+ </update>
+ <delete permission="RUN_REPORTS RUN_SIMPLE_REPORTS">
+ <context link="owner" field="home_ou"/>
+ </delete>
+ </actions>
+ </permacrud>
</class>
- <class id="rs" controller="open-ils.reporter-store" oils_obj:fieldmapper="reporter::schedule" oils_persist:tablename="reporter.schedule" reporter:label="Schedule">
+ <class id="rs" controller="open-ils.reporter-store open-ils.cstore open-ils.pcrud" oils_obj:fieldmapper="reporter::schedule" oils_persist:tablename="reporter.schedule" reporter:label="Schedule">
<fields oils_persist:primary="id" oils_persist:sequence="reporter.schedule_id_seq">
- <field name="id" reporter:datatype="id" />
- <field name="runner" reporter:datatype="link"/>
- <field name="start_time" reporter:datatype="timestamp"/>
- <field name="complete_time" reporter:datatype="timestamp"/>
- <field name="run_time" reporter:datatype="timestamp"/>
- <field name="email" reporter:datatype="text"/>
- <field name="excel_format" reporter:datatype="bool"/>
- <field name="csv_format" reporter:datatype="bool"/>
- <field name="html_format" reporter:datatype="bool"/>
- <field name="error_code" reporter:datatype="int"/>
- <field name="error_text" reporter:datatype="text"/>
- <field name="report" reporter:datatype="link"/>
- <field name="folder" reporter:datatype="link"/>
- <field name="chart_pie" reporter:datatype="bool"/>
- <field name="chart_bar" reporter:datatype="bool"/>
- <field name="chart_line" reporter:datatype="bool"/>
+ <field reporter:label="Id" name="id" reporter:datatype="id" />
+ <field reporter:label="Runner" name="runner" reporter:datatype="link"/>
+ <field reporter:label="Start Time" name="start_time" reporter:datatype="timestamp"/>
+ <field reporter:label="Finish Time" name="complete_time" reporter:datatype="timestamp"/>
+ <field reporter:label="Run Time" name="run_time" reporter:datatype="timestamp"/>
+ <field reporter:label="Email" name="email" reporter:datatype="text"/>
+ <field reporter:label="Excel" name="excel_format" reporter:datatype="bool"/>
+ <field reporter:label="CSV" name="csv_format" reporter:datatype="bool"/>
+ <field reporter:label="HTML" name="html_format" reporter:datatype="bool"/>
+ <field reporter:label="Error Code" name="error_code" reporter:datatype="int"/>
+ <field reporter:label="Error Text" name="error_text" reporter:datatype="text"/>
+ <field reporter:label="Report" name="report" reporter:datatype="link"/>
+ <field reporter:label="Folder" name="folder" reporter:datatype="link"/>
+ <field reporter:label="Pie Chart" name="chart_pie" reporter:datatype="bool"/>
+ <field reporter:label="Bar Chart" name="chart_bar" reporter:datatype="bool"/>
+ <field reporter:label="Line Chart" name="chart_line" reporter:datatype="bool"/>
</fields>
<links>
<link field="runner" reltype="has_a" key="id" map="" class="au"/>
<link field="report" reltype="has_a" key="id" map="" class="rr"/>
<link field="folder" reltype="has_a" key="id" map="" class="rof"/>
</links>
+ <permacrud xmlns="http://open-ils.org/spec/opensrf/IDL/permacrud/v1">
+ <actions>
+ <create permission="RUN_REPORTS RUN_SIMPLE_REPORTS">
+ <context link="runner" field="home_ou"/>
+ </create>
+ <retrieve permission="RUN_REPORTS RUN_SIMPLE_REPORTS">
+ <context link="runner" field="home_ou"/>
+ </retrieve>
+ <update permission="RUN_REPORTS RUN_SIMPLE_REPORTS">
+ <context link="runner" field="home_ou"/>
+ </update>
+ <delete permission="RUN_REPORTS RUN_SIMPLE_REPORTS">
+ <context link="runner" field="home_ou"/>
+ </delete>
+ </actions>
+ </permacrud>
</class>
+ <class id="rcr" controller="open-ils.reporter-store open-ils.cstore open-ils.pcrud"
+ oils_obj:fieldmapper="reporter::completed_reports" oils_persist:tablename="reporter.completed_reports"
+ reporter:label="Completed Report Runs" oils_persist:readonly="true">
+ <fields oils_persist:primary="run">
+ <field reporter:label="Run" name="run" reporter:datatype="link"/>
+ <field reporter:label="Report" name="report" reporter:datatype="link"/>
+ <field reporter:label="Template" name="template" reporter:datatype="link"/>
+ <field reporter:label="Template Owner" name="template_owner" reporter:datatype="link"/>
+ <field reporter:label="Report Owner" name="report_owner" reporter:datatype="link"/>
+ <field reporter:label="Runner" name="runner" reporter:datatype="link"/>
+ <field reporter:label="Template Folder" name="template_folder" reporter:datatype="link"/>
+ <field reporter:label="Report Folder" name="report_folder" reporter:datatype="link"/>
+ <field reporter:label="Output Folder" name="output_folder" reporter:datatype="link"/>
+ <field reporter:label="Report Name" name="report_name" reporter:datatype="text"/>
+ <field reporter:label="Template Name" name="template_name" reporter:datatype="text"/>
+ <field reporter:label="Start Time" name="start_time" reporter:datatype="text"/>
+ <field reporter:label="Run Time" name="run_time" reporter:datatype="text"/>
+ <field reporter:label="Finish Time" name="complete_time" reporter:datatype="text"/>
+ <field reporter:label="Error Code" name="error_code" reporter:datatype="text"/>
+ <field reporter:label="Error Text" name="error_text" reporter:datatype="text"/>
+ </fields>
+ <links>
+ <link field="run" reltype="has_a" key="id" map="" class="rs"/>
+ <link field="report" reltype="has_a" key="id" map="" class="rr"/>
+ <link field="template" reltype="has_a" key="id" map="" class="rt"/>
+ <link field="template_owner" reltype="has_a" key="id" map="" class="au"/>
+ <link field="report_owner" reltype="has_a" key="id" map="" class="au"/>
+ <link field="runner" reltype="has_a" key="id" map="" class="au"/>
+ <link field="template_folder" reltype="has_a" key="id" map="" class="rtf"/>
+ <link field="report_folder" reltype="has_a" key="id" map="" class="rrf"/>
+ <link field="output_folder" reltype="has_a" key="id" map="" class="rof"/>
+ </links>
+ <permacrud xmlns="http://open-ils.org/spec/opensrf/IDL/permacrud/v1">
+ <actions>
+ <retrieve permission="RUN_REPORTS RUN_SIMPLE_REPORTS">
+ <context link="runner" field="home_ou"/>
+ </retrieve>
+ </actions>
+ </permacrud>
+ </class>
<class id="rmsr" controller="open-ils.reporter-store open-ils.cstore open-ils.pcrud" oils_obj:fieldmapper="reporter::materialized_simple_record" oils_persist:tablename="reporter.materialized_simple_record" reporter:label="Fast Simple Record Extracts">
<fields oils_persist:primary="id">
<field reporter:label="Record ID" name="id" reporter:datatype="id" />
</permacrud>
</class>
+ <!-- Simple Reporter classes are currently used only by the SR UI -->
+ <class id="srcirc" controller="simple-reporter.ui" oils_persist:virtual="true" reporter:label="Simple Reporter Circulation">
+ <oils_persist:source_definition><![CDATA[
+ SELECT
+ -- Circ id; just for counting
+ combcirc.id AS circ_id,
+
+ -- User info, currently limiting to what's available from action.all_circulation
+ combcirc.usr_post_code AS usr_post_code,
+ combcirc.usr_birth_year AS usr_bith_year,
+ combcirc.usr_profile AS usr_profile_id,
+ pgtu.name AS usr_profile,
+ combcirc.usr_home_ou AS usr_home_ou_id,
+ aouuh.shortname AS usr_home_ou_shortname,
+ aouuh.name AS usr_home_ou_name,
+ (actor.org_unit_ancestor_at_depth(combcirc.usr_home_ou, 1)).id AS usr_sys_id,
+ aouus.shortname AS usr_sys_shortname,
+ aouus.name AS usr_sys_name,
+
+ -- Circ info
+ -- Checkout
+ combcirc.xact_start AS xact_start,
+ CAST(TO_CHAR(combcirc.xact_start, 'YYYY') AS INTEGER) AS xact_start_year,
+ TO_CHAR(combcirc.xact_start, 'YYYY-MM') AS xact_start_year_mon,
+ TO_CHAR(combcirc.xact_start, 'YYYY-MM-DD') AS xact_start_date,
+ combcirc.circ_lib AS circ_lib_id,
+ aouco.shortname AS circ_lib_shortname,
+ aouco.name AS circ_lib_name,
+ (actor.org_unit_ancestor_at_depth(combcirc.circ_lib, 1)).id AS circ_sys_id,
+ aoucos.shortname AS circ_sys_shortname,
+ aoucos.name AS circ_sys_name,
+ auco.usrname AS circ_staff,
+ awso.name AS checkout_workstation,
+
+ combcirc.due_date AS due_date,
+ CAST(TO_CHAR(combcirc.due_date, 'YYYY') AS INTEGER) AS due_date_year,
+ TO_CHAR(combcirc.due_date, 'YYYY-MM') AS due_date_year_mon,
+ TO_CHAR(combcirc.due_date, 'YYYY-MM-DD') AS due_date_date,
+
+ -- Checkin
+ combcirc.xact_finish AS xact_finish,
+ CAST(TO_CHAR(combcirc.xact_finish, 'YYYY') AS INTEGER) AS xact_finish_year,
+ TO_CHAR(combcirc.xact_finish, 'YYYY-MM') AS xact_finish_year_mon,
+ TO_CHAR(combcirc.xact_finish, 'YYYY-MM-DD') AS xact_finish_date,
+ aouci.shortname AS checkin_lib_shortname,
+ aouci.name AS checkin_lib_name,
+ auci.usrname AS checkin_staff,
+ awsi.name AS checkin_workstation,
+ aoucis.shortname AS checkin_sys_shortname,
+ aoucis.name AS checkin_sys_name,
+
+ combcirc.checkin_time AS checkin_time,
+
+ -- Misc circ info
+ combcirc.renewal_remaining AS renewal_remaining,
+ combcirc.auto_renewal_remaining AS auto_renewal_remaining,
+ combcirc.grace_period AS grace_period,
+
+ combcirc.stop_fines AS stop_fines,
+ combcirc.stop_fines_time AS stop_fines_time,
+
+ combcirc.duration AS duration,
+ combcirc.fine_interval AS fine_interval,
+ combcirc.recurring_fine AS recurring_fine,
+ combcirc.max_fine AS max_fine,
+ combcirc.duration_rule AS duration_rule,
+ combcirc.recurring_fine_rule AS recurring_fine_rule,
+ combcirc.max_fine_rule AS max_fine_rule,
+ CASE WHEN combcirc.parent_circ IS NULL THEN 'circ' ELSE 'renew' END AS circ_renew,
+ CASE WHEN phone_renewal THEN 'phone' WHEN desk_renewal THEN 'desk' WHEN opac_renewal THEN 'opac' WHEN auto_renewal THEN 'auto' ELSE null END AS renewal_type,
+ CASE WHEN COALESCE(combcirc.checkin_time, now()) > combcirc.due_date THEN true ELSE false END AS is_overdue,
+
+ -- Copy info
+ acp.barcode AS copy_barcode,
+ acp.circ_modifier AS copy_circ_modifier_code,
+ ccm.name AS copy_circ_modifier_name,
+ acp.circ_lib AS copy_circ_lib_id,
+ acp.location AS location_id,
+ acpl.name AS location_name,
+ aoucp.shortname AS copy_circ_lib_shortname,
+ aoucp.name AS copy_circ_lib_name,
+ (actor.org_unit_ancestor_at_depth(copy_circ_lib, 1)).id AS copy_sys_id,
+ aoucps.shortname AS copy_sys_shortname,
+ aoucps.name AS copy_sys_name,
+ combcirc.copy_owning_lib AS copy_owning_lib_id,
+ aoucpo.shortname AS copy_owning_lib_shortname,
+ aoucpo.name AS copy_owning_lib_name,
+ TRIM(BOTH ' ' FROM concat_ws(' ', acnp.label, acn.label, acns.label)) AS copy_call_number_full,
+ acnp.label AS copy_call_number_prefix,
+ acn.label AS copy_call_number_label,
+ acns.label AS copy_call_number_suffix,
+ TRIM(BOTH ' ' FROM concat_ws(' ', acnp.label_sortkey, acn.label_sortkey, acns.label_sortkey)) AS copy_call_number_sortkey_full,
+ acnp.label_sortkey AS copy_call_number_prefix_sortkey,
+ acn.label_sortkey AS copy_call_number_label_sortkey,
+ acns.label_sortkey AS copy_call_number_suffix_sortkey,
+ COALESCE(rmsr.title, acp.dummy_title) AS title,
+ COALESCE(rmsr.author, acp.dummy_author) AS author,
+ rmsr.publisher AS publisher,
+ public.approximate_date(rmsr.pubdate, '0') AS pubdate,
+ array_to_string(rmsr.isbn, ', ') AS isbn,
+ array_to_string(rmsr.issn, ', ') AS issn,
+ part.label AS part_label,
+ part.label_sortkey AS part_label_sortkey,
+ acn.record AS bib_id,
+ rmsr.tcn_value AS tcn_value
+
+ FROM
+ action.all_circulation combcirc
+
+ -- User
+ INNER JOIN actor.org_unit aouuh ON (combcirc.usr_home_ou = aouuh.id)
+ LEFT JOIN actor.org_unit aouus ON ((actor.org_unit_ancestor_at_depth(combcirc.usr_home_ou, 1)).id = aouus.id)
+ LEFT JOIN permission.grp_tree pgtu ON (combcirc.usr_profile = pgtu.id)
+
+ -- Circ
+ LEFT JOIN actor.workstation awso ON (combcirc.workstation = awso.id)
+ LEFT JOIN actor.workstation awsi ON (combcirc.checkin_workstation = awsi.id)
+ INNER JOIN actor.org_unit aouco ON (combcirc.circ_lib = aouco.id)
+ LEFT JOIN actor.org_unit aouci ON (combcirc.checkin_lib = aouci.id)
+ LEFT JOIN actor.org_unit aoucos ON ((actor.org_unit_ancestor_at_depth(combcirc.circ_lib, 1)).id = aoucos.id)
+ LEFT JOIN actor.org_unit aoucis ON ((actor.org_unit_ancestor_at_depth(combcirc.checkin_lib, 1)).id = aouci.id)
+ LEFT JOIN actor.usr auco ON (combcirc.circ_staff = auco.id)
+ LEFT JOIN actor.usr auci ON (combcirc.checkin_staff = auci.id)
+
+ -- Copy / call / title
+ INNER JOIN asset.copy acp ON (combcirc.target_copy = acp.id)
+ INNER JOIN asset.copy_location acpl ON (acp.location = acpl.id)
+ INNER JOIN actor.org_unit aoucp ON (acp.circ_lib = aoucp.id)
+ LEFT JOIN actor.org_unit aoucps ON ((actor.org_unit_ancestor_at_depth(combcirc.copy_circ_lib, 1)).id = aoucps.id)
+ LEFT JOIN actor.org_unit aoucpo ON (combcirc.copy_owning_lib = aoucpo.id)
+ LEFT JOIN actor.org_unit aoucpos ON ((actor.org_unit_ancestor_at_depth(combcirc.copy_owning_lib, 1)).id = aoucpos.id)
+ LEFT JOIN config.circ_modifier ccm ON (acp.circ_modifier = ccm.code)
+ INNER JOIN asset.call_number acn ON (acp.call_number = acn.id)
+ INNER JOIN asset.call_number_prefix acnp ON (acn.prefix = acnp.id)
+ INNER JOIN asset.call_number_suffix acns ON (acn.suffix = acns.id)
+ LEFT JOIN reporter.materialized_simple_record rmsr ON (acn.record = rmsr.id)
+ LEFT JOIN (
+ SELECT bmp.record, bmp.label, bmp.label_sortkey, acmp.target_copy
+ FROM biblio.monograph_part bmp
+ INNER JOIN asset.copy_part_map acmp ON (acmp.part = bmp.id)
+ WHERE NOT bmp.deleted
+ ) part ON (part.record = acn.record AND part.target_copy = acp.id)
+ ]]></oils_persist:source_definition>
+ <field_groups>
+ <group name="common" reporter:label="Common Fields"/>
+ <group name="item" reporter:label="Item Fields"/>
+ <group name="user" reporter:label="Patron Fields"/>
+ <group name="title" reporter:label="Call Number and Title Fields"/>
+ <group name="org" reporter:label="Libraries"/>
+ </field_groups>
+ <fields oils_persist:primary="circ_id">
+ <field reporter:label="Circulation ID" name="circ_id" reporter:datatype="int" sr:suggest_transform="count_distinct" field_groups="common"/>
+ <field reporter:label="Patron Post Code" name="usr_post_code" reporter:datatype="text" field_groups="user"/>
+ <field reporter:label="Patron Birth Year" name="usr_bith_year" reporter:datatype="int" field_groups="user"/>
+ <field reporter:label="Patron Profile Group" name="usr_profile_id" sr:hide_from="display" reporter:datatype="link" field_groups="user,common" sr:suggest_filter="true"/>
+ <field reporter:label="Patron Profile Group" name="usr_profile" sr:hide_from="filter" reporter:datatype="text" field_groups="user,common"/>
+ <field reporter:label="Patron Home Library" name="usr_home_ou_id" sr:hide_from="display" reporter:datatype="org_unit" field_groups="org,user,common" sr:suggest_filter="true"/>
+ <field reporter:label="Patron Home Library Short (Policy) Name" sr:hide_from="filter" name="usr_home_ou_shortname" reporter:datatype="text" field_groups="org,user,common"/>
+ <field reporter:label="Patron Home Library Name" name="usr_home_ou_name" sr:hide_from="filter" reporter:datatype="text" field_groups="org,user,common"/>
+ <field reporter:label="Patron System Library" name="usr_sys_id" sr:hide_from="display" reporter:datatype="org_unit" field_groups="org,user,common" sr:suggest_filter="true"/>
+ <field reporter:label="Patron System Library Short (Policy) Name" name="usr_sys_shortname" sr:hide_from="filter" reporter:datatype="text" field_groups="org,user"/>
+ <field reporter:label="Patron System Library Name" name="usr_sys_name" sr:hide_from="filter" reporter:datatype="text" field_groups="org,user"/>
+ <field reporter:label="Circulation Start Date/Time" name="xact_start" reporter:datatype="timestamp" sr:suggest_filter="true" field_groups="common"/>
+ <field reporter:label="Circulation Start Year" name="xact_start_year" reporter:datatype="int"/>
+ <field reporter:label="Circulation Start Year and Month" name="xact_start_year_mon" reporter:datatype="text" field_groups="common"/>
+ <field reporter:label="Circulation Start Date" name="xact_start_date" reporter:datatype="timestamp"/>
+ <field reporter:label="Checkout Library" name="circ_lib_id" sr:hide_from="display" reporter:datatype="org_unit" field_groups="org,common" sr:suggest_filter="true"/>
+ <field reporter:label="Checkout Library Short (Policy) Name" name="circ_lib_shortname" sr:hide_from="filter" reporter:datatype="text" field_groups="org,common"/>
+ <field reporter:label="Checkout Library Name" name="circ_lib_name" sr:hide_from="filter" reporter:datatype="text" field_groups="org,common"/>
+ <field reporter:label="Checkout System" name="circ_sys_id" sr:hide_from="display" reporter:datatype="org_unit" field_groups="org" sr:suggest_filter="true"/>
+ <field reporter:label="Checkout System Short (Policy) Name" name="circ_sys_shortname" sr:hide_from="filter" reporter:datatype="text" field_groups="org"/>
+ <field reporter:label="Checkout System Name" name="circ_sys_name" sr:hide_from="filter" reporter:datatype="text" field_groups="org"/>
+ <field reporter:label="Checkout Staff" name="circ_staff" reporter:datatype="text"/>
+ <field reporter:label="Checkout Workstation" name="checkout_workstation" reporter:datatype="text"/>
+ <field reporter:label="Due Date/Time" name="due_date" reporter:datatype="timestamp" field_groups="common" sr:suggest_filter="true"/>
+ <field reporter:label="Due Date Year" name="due_date_year" reporter:datatype="int"/>
+ <field reporter:label="Due Date Year and Month" name="due_date_year_mon" reporter:datatype="text" field_groups="common"/>
+ <field reporter:label="Due Date" name="due_date_date" reporter:datatype="text"/>
+ <field reporter:label="Circulation Finish Date/Time" name="xact_finish" reporter:datatype="timestamp"/>
+ <field reporter:label="Circulation Finish Year" name="xact_finish_year" reporter:datatype="int"/>
+ <field reporter:label="Circulation Finish Year and Month" name="xact_finish_year_mon" reporter:datatype="text"/>
+ <field reporter:label="Circulation Finish Date" name="xact_finish_date" reporter:datatype="text"/>
+ <field reporter:label="Checkin Library Short (Policy) Name" name="checkin_lib_shortname" sr:hide_from="filter" reporter:datatype="text" field_groups="org"/>
+ <field reporter:label="Checkin Library Name" name="checkin_lib_name" sr:hide_from="filter" reporter:datatype="text" field_groups="org"/>
+ <field reporter:label="Checkin Staff" name="checkin_staff" reporter:datatype="text"/>
+ <field reporter:label="Checkin Workstation" name="checkin_workstation" reporter:datatype="text"/>
+ <field reporter:label="Checkin System Short (Policy) Name" name="checkin_sys_shortname" sr:hide_from="filter" reporter:datatype="text" field_groups="org"/>
+ <field reporter:label="Checkin System Name" name="checkin_sys_name" sr:hide_from="filter" reporter:datatype="text" field_groups="org"/>
+ <field reporter:label="Checkin Date/Time" name="checkin_time" reporter:datatype="timestamp"/>
+ <field reporter:label="Remaining Renewals" name="renewal_remaining" reporter:datatype="int" sr:suggest_transform="sum" field_groups="common"/>
+ <field reporter:label="Remaining Autorenewals" name="auto_renewal_remaining" reporter:datatype="int" sr:suggest_transform="sum"/>
+ <field reporter:label="Grace Period" name="grace_period" reporter:datatype="interval"/>
+ <field reporter:label="Stop Fines Reason" name="stop_fines" reporter:datatype="text"/>
+ <field reporter:label="Stop Fines Date/Time" name="stop_fines_time" reporter:datatype="timestamp"/>
+ <field reporter:label="Circulation Duration" name="duration" reporter:datatype="interval"/>
+ <field reporter:label="Fine Interval" name="fine_interval" reporter:datatype="interval"/>
+ <field reporter:label="Recurring Fine Amount" name="recurring_fine" reporter:datatype="money"/>
+ <field reporter:label="Max Fine Amount" name="max_fine" reporter:datatype="money"/>
+ <field reporter:label="Circulation Duration Rule" name="duration_rule" reporter:datatype="text"/>
+ <field reporter:label="Recurring Fine Rule" name="recurring_fine_rule" reporter:datatype="text"/>
+ <field reporter:label="Max Fine Rule" name="max_fine_rule" reporter:datatype="text"/>
+ <field reporter:label="Circulation or Renewal" name="circ_renew" reporter:datatype="text" field_groups="common" sr:suggest_filter="true"/>
+ <field reporter:label="Renewal Type" name="renewal_type" reporter:datatype="text" field_groups="common" sr:suggest_filter="true"/>
+ <field reporter:label="Overdue?" name="is_overdue" reporter:datatype="bool" field_groups="common" sr:suggest_filter="true"/>
+ <field reporter:label="Item Barcode" name="copy_barcode" reporter:datatype="text" field_groups="item"/>
+ <field reporter:label="Shelving Location" name="location_id" sr:hide_from="display" reporter:datatype="link" field_groups="item,common" sr:suggest_filter="true"/>
+ <field reporter:label="Shelving Location" name="location_name" sr:hide_from="filter" reporter:datatype="text" field_groups="item,common"/>
+ <field reporter:label="Item Circulation Modifier Code" name="copy_circ_modifier_code" reporter:datatype="link" field_groups="item,common" sr:suggest_filter="true"/>
+ <field reporter:label="Item Circulation Modifier Name" name="copy_circ_modifier_name" reporter:datatype="text" field_groups="item"/>
+ <field reporter:label="Item Circulating Library" name="copy_circ_lib_id" sr:hide_from="display" reporter:datatype="org_unit" field_groups="org,item"/>
+ <field reporter:label="Item Circulating Library Short (Policy) Name" name="copy_circ_lib_shortname" sr:hide_from="filter" reporter:datatype="text" field_groups="org,item"/>
+ <field reporter:label="Item Circulation Library Name" name="copy_circ_lib_name" sr:hide_from="filter" reporter:datatype="text" field_groups="org,item"/>
+ <field reporter:label="Item System Library" name="copy_sys_id" sr:hide_from="display" reporter:datatype="org_unit" field_groups="org,item"/>
+ <field reporter:label="Item System Short (Policy) Name" name="copy_sys_shortname" sr:hide_from="filter" reporter:datatype="text" field_groups="org,item"/>
+ <field reporter:label="Item System Name" name="copy_sys_name" sr:hide_from="filter" reporter:datatype="text" field_groups="org,item"/>
+ <field reporter:label="Item Owning Library" name="copy_owning_lib_id" sr:hide_from="display" reporter:datatype="org_unit" field_groups="org,item" sr:suggest_filter="true"/>
+ <field reporter:label="Item Owning Library Short (Policy) Name" name="copy_owning_lib_shortname" sr:hide_from="filter" reporter:datatype="text" field_groups="org,item,common"/>
+ <field reporter:label="Item Owning Library Name" name="copy_owning_lib_name" sr:hide_from="filter" reporter:datatype="text" field_groups="org,item,common"/>
+ <field reporter:label="Full Call Number" name="copy_call_number_full" reporter:datatype="text" field_groups="title"/>
+ <field reporter:label="Call Number Prefix" name="copy_call_number_prefix" reporter:datatype="text" field_groups="title"/>
+ <field reporter:label="Call Number Label" name="copy_call_number_label" reporter:datatype="text" field_groups="title"/>
+ <field reporter:label="Call Number Suffix" name="copy_call_number_suffix" reporter:datatype="text" field_groups="title"/>
+ <field reporter:label="Full Call Number Sortkey" name="copy_call_number_sortkey_full" reporter:datatype="text" field_groups="title"/>
+ <field reporter:label="Call Number Prefix Sortkey" name="copy_call_number_prefix_sortkey" reporter:datatype="text"/>
+ <field reporter:label="Call Number Label Sortkey" name="copy_call_number_label_sortkey" reporter:datatype="text"/>
+ <field reporter:label="Call Number Suffix Sortkey" name="copy_call_number_suffix_sortkey" reporter:datatype="text"/>
+ <field reporter:label="Call Number Dewey" name="copy_call_number_dewey" reporter:datatype="text" field_groups="title"/>
+ <field reporter:label="Call Number Dewey Block Tens" name="copy_call_number_dewey_block_tens" reporter:datatype="text" field_groups="title,common" sr:suggest_filter="true"/>
+ <field reporter:label="Call Number Dewey Block Hundreds" name="copy_call_number_dewey_block_hundreds" reporter:datatype="text" field_groups="title,common" sr:suggest_filter="true"/>
+ <field reporter:label="Call Number Dewey Range Tens" name="copy_call_number_dewey_range_tens" reporter:datatype="text" field_groups="title,common" sr:suggest_filter="true"/>
+ <field reporter:label="Call Number Dewey Range Hundreds" name="copy_call_number_dewey_range_hundreds" reporter:datatype="text" field_groups="title,common" sr:suggest_filter="true"/>
+ <field reporter:label="Monographic Part" name="part_label" reporter:datatype="text" field_groups="item,title"/>
+ <field reporter:label="Monographic Part Sortkey" name="part_label_sortkey" reporter:datatype="text" field_groups="item,title"/>
+ <field reporter:label="Bibliographic Record ID" name="bib_id" reporter:datatype="int" field_groups="title"/>
+ <field reporter:label="TCN" name="tcn_value" reporter:datatype="text" field_groups="title"/>
+ <field reporter:label="Title" name="title" reporter:datatype="text" field_groups="title"/>
+ <field reporter:label="Author" name="author" reporter:datatype="text" field_groups="title"/>
+ <field reporter:label="Publisher" name="publisher" reporter:datatype="text" field_groups="title"/>
+ <field reporter:label="Publication Date" name="pubdate" reporter:datatype="int" field_groups="title"/>
+ <field reporter:label="ISBN" name="isbn" reporter:datatype="text" field_groups="title"/>
+ <field reporter:label="ISSN" name="issn" reporter:datatype="text" field_groups="title"/>
+ </fields>
+ <links>
+ <link field="usr_profile_id" reltype="has_a" key="id" map="" class="pgt"/>
+ <link sr:org_filter_field="owning_lib" field="location_id" reltype="has_a" key="id" map="" class="acpl"/>
+ <link field="copy_circ_modifier_code" reltype="might_have" key="code" map="" class="ccm"/>
+ </links>
+ </class>
+
+ <class id="srusr" controller="simple-reporter.ui" oils_persist:virtual="true" reporter:label="Simple Reporter Patrons">
+ <oils_persist:source_definition><![CDATA[
+ SELECT
+ au.home_ou AS home_ou_filter,
+ au.id AS user_id,
+ cardp.barcode AS barcode_primary,
+ au.usrname AS usrname,
+ au.email AS email,
+ CONCAT_WS(' ', au.prefix, au.first_given_name, au.second_given_name, au.family_name, au.suffix) AS full_name,
+ au.prefix AS prefix,
+ au.first_given_name AS first_given_name,
+ au.second_given_name AS second_given_name,
+ au.family_name AS family_name,
+ au.suffix AS suffix,
+ CONCAT_WS(' ', COALESCE(au.pref_prefix, au.prefix), COALESCE(au.pref_first_given_name, au.first_given_name),
+ COALESCE(au.pref_second_given_name, au.second_given_name), COALESCE(au.pref_family_name, au.family_name),
+ COALESCE(au.pref_suffix, au.suffix)) AS pref_full_name,
+ COALESCE(au.pref_prefix, au.prefix) AS pref_prefix,
+ COALESCE(au.pref_first_given_name, au.first_given_name) AS pref_first_given_name,
+ COALESCE(au.pref_second_given_name, au.second_given_name) AS pref_second_given_name,
+ COALESCE(au.pref_family_name, au.family_name) AS pref_family_name,
+ COALESCE(au.pref_suffix, au.suffix) AS pref_suffix,
+ au.profile AS profile_id,
+ pgt.name AS profile,
+ au.alias AS alias,
+ CONCAT_WS(', ', au.day_phone, au.evening_phone, au.other_phone) AS all_phone,
+ au.day_phone AS day_phone,
+ au.evening_phone AS evening_phone,
+ au.other_phone AS other_phone,
+ au.dob AS dob,
+ CAST(TO_CHAR(au.dob, 'YYYY') AS INTEGER) AS dob_year,
+ TO_CHAR(au.dob, 'YYYY-MM') AS dob_year_month,
+ EXTRACT('year' FROM DATE_TRUNC('year', AGE(au.dob))) AS dob_age_years,
+ au.active AS active,
+ au.barred AS barred,
+ au.deleted AS deleted,
+ au.juvenile AS juvenile,
+ au.claims_returned_count AS claims_returned_count,
+ au.credit_forward_balance AS credit_forward_balance,
+ au.create_date AS create_date,
+ CAST(TO_CHAR(au.create_date, 'YYYY') AS INTEGER) AS create_date_year,
+ TO_CHAR(au.create_date, 'YYYY-MM') AS create_date_year_mon,
+ TO_CHAR(au.create_date, 'YYYY-MM-DD') AS create_date_date,
+ au.expire_date AS expire_date,
+ CAST(TO_CHAR(au.expire_date, 'YYYY') AS INTEGER) AS expire_date_year,
+ TO_CHAR(au.expire_date, 'YYYY-MM') AS expire_date_year_mon,
+ TO_CHAR(au.expire_date, 'YYYY-MM-DD') AS expire_date_date,
+ au.claims_never_checked_out_count AS claims_never_checked_out_count,
+ au.last_update_time AS last_update_time,
+ au.home_ou AS home_ou_id,
+ aouh.shortname AS home_ou_shortname,
+ aouh.name AS home_ou_name,
+ (actor.org_unit_ancestor_at_depth(au.home_ou, 1)).id AS home_sys_id,
+ aous.shortname AS home_sys_shortname,
+ aous.name AS home_sys_name,
+ auam.valid AS mailing_valid,
+ auam.address_type AS mailing_address_type,
+ auam.street1 AS mailing_street1,
+ auam.street2 AS mailing_street2,
+ auam.city AS mailing_city,
+ auam.state AS mailing_state,
+ auam.country AS mailing_country,
+ auam.post_code AS mailing_post_code,
+ auam.county AS mailing_county,
+ auam.within_city_limits AS mailing_within_city_limits,
+ auam.pending AS mailing_pending,
+ CONCAT_WS(' ', CONCAT_WS(', ', auam.street1, auam.street2), CONCAT_WS(', ', auam.city, auam.state), auam.post_code) AS mailing_full,
+ auap.valid AS physical_valid,
+ auap.address_type AS physical_address_type,
+ auap.street1 AS physical_street1,
+ auap.street2 AS physical_street2,
+ auap.city AS physical_city,
+ auap.state AS physical_state,
+ auap.country AS physical_country,
+ auap.post_code AS physical_post_code,
+ auap.county AS physical_county,
+ auap.within_city_limits AS physical_within_city_limits,
+ auap.pending AS physical_pending,
+ CONCAT_WS(' ', CONCAT_WS(', ', auap.street1, auap.street2), CONCAT_WS(', ', auap.city, auap.state), auap.post_code) AS physical_full,
+ CASE WHEN aus_hold.value ~ 'phone' THEN TRUE ELSE FALSE END AS phone_notify,
+ CASE WHEN aus_hold.value ~ 'sms' THEN TRUE ELSE FALSE END AS sms_notify,
+ CASE WHEN aus_hold.value ~ 'email' THEN TRUE ELSE FALSE END AS email_notify,
+ CASE WHEN aus_coll.value ILIKE 'true' THEN TRUE ELSE FALSE END AS collections_exempt,
+ CASE WHEN aus_noti.value ILIKE 'true' THEN TRUE ELSE FALSE END AS notice_optin,
+ asceum1.stat_cat_entry AS legacy_stat_cat1,
+ asceum2.stat_cat_entry AS legacy_stat_cat2,
+
+ -- Pile up aggregates at the bottom to make the GROUP BY more manageable.
+ STRING_AGG(DISTINCT carda.barcode, ', ') AS barcode_active,
+ STRING_AGG(DISTINCT cards.barcode, ', ') AS barcode_all,
+ STRING_AGG(DISTINCT pgts.name, ', ') AS secondary_groups,
+ COUNT(DISTINCT CASE WHEN circ.checkin_time IS NULL AND circ.xact_finish IS NULL AND now() > circ.due_date THEN circ.id ELSE NULL END) AS circs_overdue,
+ COUNT(DISTINCT CASE WHEN circ.checkin_time IS NULL AND circ.xact_finish IS NULL THEN circ.id ELSE NULL END) AS circs_open
+
+ FROM
+ actor.usr au
+ INNER JOIN actor.org_unit aouh ON (au.home_ou = aouh.id)
+ INNER JOIN actor.org_unit aous ON ((actor.org_unit_ancestor_at_depth(au.home_ou, 1)).id = aous.id)
+ LEFT JOIN actor.card cardp ON (au.card = cardp.id)
+ LEFT JOIN actor.card carda ON (au.id = carda.usr AND carda.active)
+ LEFT JOIN actor.card cards ON (au.id = cards.usr)
+ INNER JOIN permission.grp_tree pgt ON (au.profile = pgt.id)
+ LEFT JOIN permission.usr_grp_map pugm ON (au.id = pugm.usr)
+ LEFT JOIN permission.grp_tree pgts ON (pugm.grp = pgts.id)
+ LEFT JOIN actor.usr_address auam ON (au.mailing_address = auam.id)
+ LEFT JOIN actor.usr_address auap ON (au.billing_address = auap.id)
+ LEFT JOIN action.circulation circ ON (au.id = circ.usr)
+ LEFT JOIN actor.usr_setting aus_hold ON (au.id = aus_hold.usr AND aus_hold.name = 'opac.hold_notify')
+ LEFT JOIN actor.usr_setting aus_coll ON (au.id = aus_coll.usr AND aus_coll.name = 'circ.collections.exempt')
+ LEFT JOIN actor.usr_setting aus_noti ON (au.id = aus_noti.usr AND aus_noti.name = 'circ.default_overdue_notices_enabled')
+ LEFT JOIN actor.stat_cat_entry_usr_map asceum1 ON (au.id = asceum1.target_usr AND asceum1.stat_cat = 1)
+ LEFT JOIN actor.stat_cat_entry_usr_map asceum2 ON (au.id = asceum2.target_usr AND asceum2.stat_cat = 2)
+
+ GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,
+ 16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,
+ 31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,
+ 46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,
+ 61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,
+ 76,77,78,79,80,81
+ ]]></oils_persist:source_definition>
+ <field_groups>
+ <group name="common" reporter:label="Common Fields"/>
+ <group name="name" reporter:label="Name Fields"/>
+ <group name="contact" reporter:label="Contact Fields"/>
+ <group name="address" reporter:label="Address Related Fields"/>
+ <group name="org" reporter:label="Libraries"/>
+ </field_groups>
+ <fields oils_persist:primary="user_id">
+ <field reporter:label="VIEW_USER Permission Verification" name="home_ou_filter" reporter:datatype="int"
+ sr:hide_from="display,filter" sr:force_transform="reporter.intersect_user_perm_ou,SR__USER_ID,VIEW_USER" sr:force_filter="true" sr:force_filtervalues="t"/>
+ <field reporter:label="User ID" name="user_id" reporter:datatype="int" sr:suggest_transform="count_distinct" field_groups="common"/>
+ <field reporter:label="Primary Barcode" name="barcode_primary" reporter:datatype="text" field_groups="common"/>
+ <field reporter:label="OPAC/Staff Client User Name" name="usrname" reporter:datatype="text" field_groups="common"/>
+ <field reporter:label="Email" name="email" reporter:datatype="text" field_groups="contact,common"/>
+ <field reporter:label="Full Name" name="full_name" reporter:datatype="text" field_groups="name,common"/>
+ <field reporter:label="Prefix" name="prefix" reporter:datatype="text" field_groups="name"/>
+ <field reporter:label="First Name" name="first_given_name" reporter:datatype="text" field_groups="name,common"/>
+ <field reporter:label="Middle Name" name="second_given_name" reporter:datatype="text" field_groups="name"/>
+ <field reporter:label="Last Name" name="family_name" reporter:datatype="text" field_groups="name,common"/>
+ <field reporter:label="Suffix" name="suffix" reporter:datatype="text" field_groups="name"/>
+ <field reporter:label="Full Preferred Name" name="pref_full_name" reporter:datatype="text" field_groups="name,common"/>
+ <field reporter:label="Preferred Prefix" name="pref_prefix" reporter:datatype="text" field_groups="name"/>
+ <field reporter:label="Preferred First Name" name="pref_first_given_name" reporter:datatype="text" field_groups="name,common"/>
+ <field reporter:label="Preferred Middle Name" name="pref_second_given_name" reporter:datatype="text" field_groups="name"/>
+ <field reporter:label="Preferred Last Name" name="pref_family_name" reporter:datatype="text" field_groups="name,common"/>
+ <field reporter:label="Preferred Suffix" name="pref_suffix" reporter:datatype="text" field_groups="name"/>
+ <field reporter:label="Main (Profile) Permission Group" name="profile_id" reporter:datatype="link" sr:hide_from="display" sr:suggest_filter="true"/>
+ <field reporter:label="Main (Profile) Permission Group" name="profile" reporter:datatype="text" sr:hide_from="filter" field_groups="common"/>
+ <field reporter:label="OPAC/Staff Client Holds Alias" name="alias" reporter:datatype="text" field_groups="common,name"/>
+ <field reporter:label="All Phone Numbers" name="all_phone" reporter:datatype="text" field_groups="contact"/>
+ <field reporter:label="Daytime Phone" name="day_phone" reporter:datatype="text" field_groups="contact,common"/>
+ <field reporter:label="Evening Phone" name="evening_phone" reporter:datatype="text" field_groups="contact"/>
+ <field reporter:label="Other Phone" name="other_phone" reporter:datatype="text" field_groups="contact"/>
+ <field reporter:label="Date of Birth" name="dob" reporter:datatype="timestamp"/>
+ <field reporter:label="Birth Year" name="dob_year" reporter:datatype="int"/>
+ <field reporter:label="Birth Year and Month" name="dob_year_month" reporter:datatype="text"/>
+ <field reporter:label="Age" name="dob_age_years" reporter:datatype="int"/>
+ <field reporter:label="Active?" name="active" reporter:datatype="bool" sr:suggest_filter="true" field_groups="common"/>
+ <field reporter:label="Barred?" name="barred" reporter:datatype="bool" sr:suggest_filter="true" field_groups="common"/>
+ <field reporter:label="Deleted?" name="deleted" reporter:datatype="bool" sr:suggest_filter="true" field_groups="common"/>
+ <field reporter:label="Juvenile?" name="juvenile" reporter:datatype="bool" sr:suggest_filter="true" field_groups="common"/>
+ <field reporter:label="Claims-returned Count" name="claims_returned_count" reporter:datatype="int" sr:suggest_transform="sum"/>
+ <field reporter:label="User Credit Balance" name="credit_forward_balance" reporter:datatype="money"/>
+ <field reporter:label="Record Creation Date/Time" name="create_date" reporter:datatype="timestamp"/>
+ <field reporter:label="Record Creation Year" name="create_date_year" reporter:datatype="int"/>
+ <field reporter:label="Record Creation Year and Month" name="create_date_year_mon" reporter:datatype="text"/>
+ <field reporter:label="Record Creation Date" name="create_date_date" reporter:datatype="text"/>
+ <field reporter:label="Privilege Expiration Date/Time" name="expire_date" reporter:datatype="timestamp" field_groups="common" sr:suggest_filter="true"/>
+ <field reporter:label="Privilege Expiration Year" name="expire_date_year" reporter:datatype="int"/>
+ <field reporter:label="Privilege Expiration Year and Month" name="expire_date_year_mon" sr:suggest_filter="true" reporter:datatype="text"/>
+ <field reporter:label="Privilege Expiration Date" name="expire_date_date" reporter:datatype="text"/>
+ <field reporter:label="Claims Never Checked Out Count" name="claims_never_checked_out_count" reporter:datatype="int" sr:suggest_transform="sum"/>
+ <field reporter:label="Record Last Update Time" name="last_update_time" reporter:datatype="timestamp"/>
+ <field reporter:label="Home Library" name="home_ou_id" reporter:datatype="org_unit" sr:suggest_filter="true" sr:hide_from="display" field_groups="org,common"/>
+ <field reporter:label="Home Library Short (Policy) Name" name="home_ou_shortname" reporter:datatype="text" sr:hide_from="filter" field_groups="org,common"/>
+ <field reporter:label="Home Library Name" name="home_ou_name" reporter:datatype="text" sr:hide_from="filter" field_groups="org,common"/>
+ <field reporter:label="Home System" name="home_sys_id" reporter:datatype="org_unit" sr:suggest_filter="true" sr:hide_from="display" field_groups="org,common"/>
+ <field reporter:label="Home System Short (Policy) Name" name="home_sys_shortname" reporter:datatype="text" sr:hide_from="filter" field_groups="org,common"/>
+ <field reporter:label="Home System Name" name="home_sys_name" reporter:datatype="text" sr:hide_from="filter" field_groups="org,common"/>
+ <field reporter:label="Mailing Address Valid?" name="mailing_valid" reporter:datatype="bool" field_groups="address"/>
+ <field reporter:label="Mailing Address Type" name="mailing_address_type" reporter:datatype="text" field_groups="address"/>
+ <field reporter:label="Mailing Address Street1" name="mailing_street1" reporter:datatype="text" field_groups="address"/>
+ <field reporter:label="Mailing Address Street2" name="mailing_street2" reporter:datatype="text" field_groups="address"/>
+ <field reporter:label="Mailing Address City" name="mailing_city" reporter:datatype="text" field_groups="address"/>
+ <field reporter:label="Mailing Address State" name="mailing_state" reporter:datatype="text" field_groups="address"/>
+ <field reporter:label="Mailing Address Country" name="mailing_country" reporter:datatype="text" field_groups="address"/>
+ <field reporter:label="Mailing Address Postal Code" name="mailing_post_code" reporter:datatype="text" field_groups="address"/>
+ <field reporter:label="Mailing Address County" name="mailing_county" reporter:datatype="text" field_groups="address"/>
+ <field reporter:label="Mailing Address Within City Limits?" name="mailing_within_city_limits" reporter:datatype="bool" field_groups="address"/>
+ <field reporter:label="Mailing Address Pending?" name="mailing_pending" reporter:datatype="bool" field_groups="address"/>
+ <field reporter:label="Full Mailing Address" name="mailing_full" reporter:datatype="text" field_groups="address,common"/>
+ <field reporter:label="Physical Address Valid?" name="physical_valid" reporter:datatype="bool" field_groups="address"/>
+ <field reporter:label="Physical Address Type" name="physical_address_type" reporter:datatype="text" field_groups="address"/>
+ <field reporter:label="Physical Address Street1" name="physical_street1" reporter:datatype="text" field_groups="address"/>
+ <field reporter:label="Physical Address Street2" name="physical_street2" reporter:datatype="text" field_groups="address"/>
+ <field reporter:label="Physical Address City" name="physical_city" reporter:datatype="text" field_groups="address"/>
+ <field reporter:label="Physical Address State" name="physical_state" reporter:datatype="text" field_groups="address"/>
+ <field reporter:label="Physical Address Country" name="physical_country" reporter:datatype="text" field_groups="address"/>
+ <field reporter:label="Physical Address Postal Code" name="physical_post_code" reporter:datatype="text" field_groups="address"/>
+ <field reporter:label="Physical Address Country" name="physical_county" reporter:datatype="text" field_groups="address"/>
+ <field reporter:label="Physical Address Within City Limits?" name="physical_within_city_limits" reporter:datatype="bool" field_groups="address"/>
+ <field reporter:label="Physical Address Pending?" name="physical_pending" reporter:datatype="bool" field_groups="address"/>
+ <field reporter:label="Full Physical Address" name="physical_full" reporter:datatype="text" field_groups="address,common"/>
+ <field reporter:label="Hold Notifications: Phone?" name="phone_notify" reporter:datatype="bool" field_groups="contact"/>
+ <field reporter:label="Hold Notifications: SMS?" name="sms_notify" reporter:datatype="bool" field_groups="contact"/>
+ <field reporter:label="Hold Notifications: Email?" name="email_notify" reporter:datatype="bool" field_groups="contact"/>
+ <field reporter:label="Collections Exempt?" name="collections_exempt" reporter:datatype="bool"/>
+ <field reporter:label="Email Notice Opt-in?" name="notice_optin" reporter:datatype="bool"/>
+ <field reporter:label="Legacy Stat Cat 1" name="legacy_stat_cat1" reporter:datatype="text"/>
+ <field reporter:label="Legacy Stat Cat 2" name="legacy_stat_cat2" reporter:datatype="text"/>
+ <field reporter:label="All Active Barcodes" name="barcode_active" reporter:datatype="text" sr:suggest_operator="contains"/>
+ <field reporter:label="All Barcodes" name="barcode_all" reporter:datatype="text" sr:suggest_operator="contains"/>
+ <field reporter:label="Secondary Permission Groups" name="secondary_groups" reporter:datatype="text" sr:suggest_operator="contains"/>
+ <field reporter:label="Overdue Circulations" name="circs_overdue" reporter:datatype="int" sr:suggest_transform="sum" sr:suggest_filter="true" field_groups="common"/>
+ <field reporter:label="Open Circulations" name="circs_open" reporter:datatype="int" sr:suggest_transform="sum" sr:suggest_filter="true" field_groups="common"/>
+ </fields>
+ <links>
+ <link field="profile_id" reltype="has_a" key="id" map="" class="pgt"/>
+ </links>
+ </class>
+
+ <class id="srcp" controller="simple-reporter.ui" oils_persist:virtual="true" reporter:label="Simple Reporter Collections">
+ <oils_persist:source_definition><![CDATA[
+ SELECT
+ -- ids
+ acp.id AS copy_id,
+ acp.floating AS floating_id,
+ acn.id AS acn_id,
+ acp.circ_lib AS circ_lib_id,
+ acp.status AS status_id,
+ acp.location AS location_id,
+ acp.age_protect AS age_protect_id,
+ aous.id AS circ_sys_id,
+ acn.owning_lib AS owning_lib_id,
+ aouos.id AS owning_sys_id,
+ awsi.id AS inv_workstation_id,
+
+ -- OUs
+ aou.shortname AS circ_lib_shortname,
+ aou.name AS circ_lib_name,
+ aous.shortname AS circ_sys_shortname,
+ aous.name AS circ_sys_name,
+ aouo.shortname AS owning_lib_shortname,
+ aouo.name AS owning_lib_name,
+ aouos.shortname AS owning_sys_shortname,
+ aouos.name AS owning_sys_name,
+
+ -- dates
+ acp.create_date AS create_date_time,
+ CAST(TO_CHAR(acp.create_date, 'YYYY') AS INTEGER) AS create_date_year,
+ TO_CHAR(acp.create_date, 'YYYY-MM') AS create_date_year_mon,
+ TO_CHAR(acp.create_date, 'YYYY-MM-DD') AS create_date_date,
+ acp.active_date AS active_date_time,
+ CAST(TO_CHAR(acp.active_date, 'YYYY') AS INTEGER) AS active_date_year,
+ TO_CHAR(acp.active_date, 'YYYY-MM') AS active_date_year_mon,
+ TO_CHAR(acp.active_date, 'YYYY-MM-DD') AS active_date_date,
+ acp.edit_date AS edit_date_time,
+ CAST(TO_CHAR(acp.edit_date, 'YYYY') AS INTEGER) AS edit_date_year,
+ TO_CHAR(acp.edit_date, 'YYYY-MM') AS edit_date_year_mon,
+ TO_CHAR(acp.edit_date, 'YYYY-MM-DD') AS edit_date_date,
+ acp.status_changed_time AS status_changed_time_time,
+ CAST(TO_CHAR(acp.status_changed_time, 'YYYY') AS INTEGER) AS status_changed_year,
+ TO_CHAR(acp.status_changed_time, 'YYYY-MM') AS status_changed_year_mon,
+ TO_CHAR(acp.status_changed_time, 'YYYY-MM-DD') AS status_changed_date,
+ ali.inventory_date AS latest_inv_date,
+ CAST(TO_CHAR(ali.inventory_date, 'YYYY') AS INTEGER) AS latest_inv_date_year,
+ TO_CHAR(ali.inventory_date, 'YYYY-MM') AS latest_inv_date_year_mon,
+ TO_CHAR(ali.inventory_date, 'YYYY-MM-DD') AS latest_inv_date_date,
+
+ -- acp
+ acp.barcode AS barcode,
+ ccs.name AS status,
+ acpl.name AS location_name,
+ acp.circ_modifier AS copy_circ_modifier_code,
+ ccm.name AS copy_circ_modifier_name,
+ acp.price AS price,
+ acp.cost AS acq_price,
+ acp.deleted AS copy_deleted,
+ acp.ref AS reference,
+ acp.circulate AS copy_circulate,
+ (acp.circulate AND acpl.circulate) AS circulate,
+ acp.holdable AS copy_holdable,
+ acp.deposit AS deposit,
+ acp.deposit_amount AS deposit_amount,
+ acp.alert_message AS alert_message,
+ acp.opac_visible AS copy_opac_visible,
+ (acp.opac_visible AND ccs.opac_visible) AS opac_visible,
+ crahp.name AS age_protect,
+ CASE WHEN acp.age_protect IS NULL THEN FALSE ELSE (now() - crahp.age) < acp.active_date END AS under_age_protection,
+ (acp.holdable AND ccs.holdable AND acpl.holdable) AS holdable,
+ acp.copy_number AS copy_number,
+ acp.circ_as_type AS circ_as_type,
+ acp.loan_duration AS loan_duration_int,
+ CASE acp.loan_duration WHEN 1 THEN 'short' WHEN 2 THEN 'medium' ELSE 'long' END AS loan_duration,
+ acp.fine_level AS fine_level_int,
+ CASE acp.fine_level WHEN 1 THEN 'low' WHEN 2 THEN 'medium' ELSE 'high' END AS fine_level,
+
+ -- Call Number
+ TRIM(BOTH ' ' FROM CONCAT_WS(' ', acnp.label, acn.label, acns.label)) AS copy_call_number_full,
+ acnp.label AS copy_call_number_prefix,
+ acn.label AS copy_call_number_label,
+ acns.label AS copy_call_number_suffix,
+ TRIM(BOTH ' ' FROM concat_ws(' ', acnp.label_sortkey, acn.label_sortkey, acns.label_sortkey)) AS copy_call_number_sortkey_full,
+ acnp.label_sortkey AS copy_call_number_prefix_sortkey,
+ acn.label_sortkey AS copy_call_number_label_sortkey,
+ acns.label_sortkey AS copy_call_number_suffix_sortkey,
+ racnd.dewey AS call_number_dewey,
+ racnd.dewey_block_tens AS call_number_dewey_block_tens,
+ racnd.dewey_block_hundreds AS call_number_dewey_block_hundreds,
+ racnd.dewey_range_tens AS call_number_dewey_range_tens,
+ racnd.dewey_range_hundreds AS call_number_dewey_range_hundreds,
+
+ -- Misc
+ COALESCE(rmsr.title, acp.dummy_title) AS title,
+ COALESCE(rmsr.author, acp.dummy_author) AS author,
+ rmsr.publisher AS publisher,
+ public.approximate_date(rmsr.pubdate, '0') AS pubdate,
+ COALESCE(array_to_string(rmsr.isbn, ', '), acp.dummy_isbn) AS isbn,
+ array_to_string(rmsr.issn, ', ') AS issn,
+ part.label AS part_label,
+ part.label_sortkey AS part_label_sortkey,
+ acn.record AS bib_id,
+ rmsr.tcn_value AS tcn_value,
+ acn.deleted AS call_deleted,
+ bre.deleted AS bib_deleted,
+ ccs.holdable AS status_holdable,
+ acpl.holdable AS location_holdable,
+ acp.circulate AS location_circulate,
+ awsi.name AS inventory_workstation,
+ aucr.usrname AS creating_user,
+ aued.usrname AS editing_user,
+
+ -- Aggregates
+ -- may decide these price games aren't worth it, but estimated collection value is useful and missing prices are not.
+ COALESCE(acp.cost, acp.price, (select first(value::numeric(8,2)) from actor.org_unit_ancestor_setting('cat.default_item_price', acp.circ_lib)), 5.00) AS est_price,
+ COUNT(DISTINCT combcirc.id) AS circ_total,
+ STRING_AGG(mfde_subj.value, ', ') AS bib_subjects
+
+ FROM
+ asset.copy acp
+ LEFT JOIN config.circ_modifier ccm ON (acp.circ_modifier = ccm.code)
+ INNER JOIN asset.call_number acn ON (acp.call_number = acn.id)
+ LEFT JOIN reporter.asset_call_number_dewey racnd ON (acn.id = racnd.call_number)
+ INNER JOIN config.copy_status ccs ON (acp.status = ccs.id)
+ LEFT JOIN config.rule_age_hold_protect crahp ON (acp.age_protect = crahp.id)
+ INNER JOIN asset.copy_location acpl ON (acp.location = acpl.id)
+ INNER JOIN asset.call_number_prefix acnp ON (acn.prefix = acnp.id)
+ INNER JOIN asset.call_number_suffix acns ON (acn.suffix = acns.id)
+ LEFT JOIN asset.latest_inventory ali ON (acp.id = ali.copy)
+ LEFT JOIN actor.workstation awsi ON (ali.inventory_workstation = awsi.id)
+ LEFT JOIN reporter.materialized_simple_record rmsr ON (acn.record = rmsr.id)
+ INNER JOIN actor.org_unit aou ON (acp.circ_lib = aou.id)
+ LEFT JOIN actor.org_unit aous ON ((actor.org_unit_ancestor_at_depth(acp.circ_lib, 1)).id = aous.id)
+ INNER JOIN actor.org_unit aouo ON (acn.owning_lib = aouo.id)
+ LEFT JOIN actor.org_unit aouos ON ((actor.org_unit_ancestor_at_depth(acn.owning_lib, 1)).id = aouos.id)
+ INNER JOIN biblio.record_entry bre ON (acn.record = bre.id)
+ LEFT JOIN metabib.flat_display_entry mfde_subj ON (acn.record = mfde_subj.source AND mfde_subj.name = 'subject')
+ LEFT JOIN action.all_circulation combcirc ON (acp.id = combcirc.target_copy)
+ INNER JOIN actor.usr aucr ON (acp.creator = aucr.id)
+ INNER JOIN actor.usr aued ON (acp.editor = aued.id)
+ LEFT JOIN (
+ SELECT bmp.record, bmp.label, bmp.label_sortkey, acmp.target_copy
+ FROM biblio.monograph_part bmp
+ INNER JOIN asset.copy_part_map acmp ON (acmp.part = bmp.id)
+ WHERE NOT bmp.deleted
+ ) part ON (part.record = acn.record AND part.target_copy = acp.id)
+
+ GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,
+ 16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,
+ 31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,
+ 46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,
+ 61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,
+ 76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,
+ 91,92,93,94,95,96
+
+ ]]></oils_persist:source_definition>
+ <field_groups>
+ <group name="common" reporter:label="Common Fields"/>
+ <group name="dates" reporter:label="Date Fields"/>
+ <group name="title" reporter:label="Call Number and Title Fields"/>
+ <group name="org" reporter:label="Libraries"/>
+ </field_groups>
+ <fields oils_persist:primary="copy_id">
+ <field reporter:label="Item ID" name="copy_id" reporter:datatype="int" sr:suggest_transform="count_distinct"/>
+ <field reporter:label="Floating Group" name="floating_id" reporter:datatype="link" sr:hide_from="display"/>
+ <field reporter:label="Call Number" name="acn_id" reporter:datatype="int" sr:hide_from="display"/>
+ <field reporter:label="Circulating Library" name="circ_lib_id" reporter:datatype="org_unit" sr:hide_from="display" field_groups="org" sr:suggest_filter="true"/>
+ <field reporter:label="Item Status" name="status_id" reporter:datatype="link" sr:hide_from="display" field_groups="common" sr:suggest_filter="true"/>
+ <field reporter:label="Shelving Location" name="location_id" reporter:datatype="link" sr:hide_from="display" field_groups="common" sr:suggest_filter="true"/>
+ <field reporter:label="Age Protection" name="age_protect_id" reporter:datatype="link" sr:hide_from="display"/>
+ <field reporter:label="Circulating System" name="circ_sys_id" reporter:datatype="org_unit" sr:hide_from="display" field_groups="org"/>
+ <field reporter:label="Owning Library" name="owning_lib_id" reporter:datatype="org_unit" sr:hide_from="display" field_groups="org,common" sr:suggest_filter="true"/>
+ <field reporter:label="Owning System" name="owning_sys_id" reporter:datatype="org_unit" sr:hide_from="display" field_groups="org,common" sr:suggest_filter="true"/>
+ <field reporter:label="Inventory Workstation" name="inv_workstation_id" reporter:datatype="link" sr:hide_from="display"/>
+ <field reporter:label="Circulating Library Short (Policy) Name" name="circ_lib_shortname" reporter:datatype="text" sr:hide_from="filter" field_groups="org,common"/>
+ <field reporter:label="Circulating Library Name" name="circ_lib_name" reporter:datatype="text" sr:hide_from="filter" field_groups="org,common"/>
+ <field reporter:label="Circulating System Short (Policy) Name" name="circ_sys_shortname" reporter:datatype="text" sr:hide_from="filter" field_groups="org"/>
+ <field reporter:label="Circulating System Name" name="circ_sys_name" reporter:datatype="text" sr:hide_from="filter" field_groups="org"/>
+ <field reporter:label="Owning Library Short (Policy) Name" name="owning_lib_shortname" reporter:datatype="text" sr:hide_from="filter" field_groups="org,common"/>
+ <field reporter:label="Owning Library Name" name="owning_lib_name" reporter:datatype="text" sr:hide_from="filter" field_groups="org,common"/>
+ <field reporter:label="Owning System Short (Policy) Name" name="owning_sys_shortname" reporter:datatype="text" sr:hide_from="filter" field_groups="org"/>
+ <field reporter:label="Owning System Name" name="owning_sys_name" reporter:datatype="text" sr:hide_from="filter" field_groups="org"/>
+ <field reporter:label="Item Create Date/Time" name="create_date_time" reporter:datatype="timestamp" field_groups="dates"/>
+ <field reporter:label="Item Create Year" name="create_date_year" reporter:datatype="int" field_groups="dates"/>
+ <field reporter:label="Item Create Year and Month" name="create_date_year_mon" reporter:datatype="text" field_groups="dates"/>
+ <field reporter:label="Item Create Date" name="create_date_date" reporter:datatype="text" field_groups="dates"/>
+ <field reporter:label="Item Active Date/Time" name="active_date_time" reporter:datatype="timestamp" field_groups="dates"/>
+ <field reporter:label="Item Active Year" name="active_date_year" reporter:datatype="int" field_groups="dates"/>
+ <field reporter:label="Item Active Year and Month" name="active_date_year_mon" reporter:datatype="text" field_groups="dates"/>
+ <field reporter:label="Item Active Date" name="active_date_date" reporter:datatype="text" field_groups="dates"/>
+ <field reporter:label="Item Edit Date/Time" name="edit_date_time" reporter:datatype="timestamp" field_groups="dates"/>
+ <field reporter:label="Item Edit Year" name="edit_date_year" reporter:datatype="int" field_groups="dates"/>
+ <field reporter:label="Item Edit Year and Month" name="edit_date_year_mon" reporter:datatype="text" field_groups="dates"/>
+ <field reporter:label="Item Edit Date" name="edit_date_date" reporter:datatype="text" field_groups="dates"/>
+ <field reporter:label="Item Status Changed Date/Time" name="status_changed_time_time" reporter:datatype="timestamp" field_groups="dates"/>
+ <field reporter:label="Item Status Changed Year" name="status_changed_year" reporter:datatype="int" field_groups="dates"/>
+ <field reporter:label="Item Status Changed Year and Month" name="status_changed_year_mon" reporter:datatype="text" field_groups="dates"/>
+ <field reporter:label="Item Status Changed Date" name="status_changed_date" reporter:datatype="text" field_groups="dates"/>
+ <field reporter:label="Item Latest Inventory Date/Time" name="latest_inv_date" reporter:datatype="timestamp" field_groups="dates"/>
+ <field reporter:label="Item Latest Inventory Year" name="latest_inv_date_year" reporter:datatype="int" field_groups="dates"/>
+ <field reporter:label="Item Latest Inventory Year and Month" name="latest_inv_date_year_mon" reporter:datatype="text" field_groups="dates"/>
+ <field reporter:label="Item Latest Inventory Date" name="latest_inv_date_date" reporter:datatype="text" field_groups="dates"/>
+ <field reporter:label="Barcode" name="barcode" reporter:datatype="text" field_groups="common"/>
+ <field reporter:label="Item Status" name="status" reporter:datatype="text" sr:hide_from="filter" field_groups="common"/>
+ <field reporter:label="Shelving Location" name="location_name" reporter:datatype="text" sr:hide_from="filter" field_groups="common"/>
+ <field reporter:label="Circulation Modifier Code" name="copy_circ_modifier_code" reporter:datatype="link" field_groups="common" sr:suggest_filter="true"/>
+ <field reporter:label="Circulation Modifier Name" name="copy_circ_modifier_name" reporter:datatype="text" sr:hide_from="filter" field_groups="common"/>
+ <field reporter:label="Price" name="price" reporter:datatype="money" sr:suggest_transform="sum"/>
+ <field reporter:label="Acquisition Cost" name="acq_price" reporter:datatype="money" sr:suggest_transform="sum"/>
+ <field reporter:label="Item Deleted?" name="copy_deleted" reporter:datatype="bool" sr:suggest_filter="true"/>
+ <field reporter:label="Reference?" name="reference" reporter:datatype="bool"/>
+ <field reporter:label="Item Circulate?" name="copy_circulate" reporter:datatype="bool"/>
+ <field reporter:label="Circulate?" name="circulate" reporter:datatype="bool"/>
+ <field reporter:label="Item Holdable?" name="copy_holdable" reporter:datatype="bool"/>
+ <field reporter:label="Deposit?" name="deposit" reporter:datatype="bool"/>
+ <field reporter:label="Deposit Amount" name="deposit_amount" reporter:datatype="money" sr:suggest_transform="sum"/>
+ <field reporter:label="Alert Message" name="alert_message" reporter:datatype="text"/>
+ <field reporter:label="Item OPAC Visible?" name="copy_opac_visible" reporter:datatype="bool"/>
+ <field reporter:label="OPAC Visible?" name="opac_visible" reporter:datatype="bool"/>
+ <field reporter:label="Age Protection" name="age_protect" reporter:datatype="text" sr:hide_from="filter"/>
+ <field reporter:label="Currently Age Protected?" name="under_age_protection" reporter:datatype="bool" field_groups="common"/>
+ <field reporter:label="Holdable?" name="holdable" reporter:datatype="bool" field_groups="common"/>
+ <field reporter:label="Item Number" name="copy_number" reporter:datatype="int"/>
+ <field reporter:label="Circ as Type" name="circ_as_type" reporter:datatype="text"/>
+ <field reporter:label="Loan Duration Value" name="loan_duration_int" reporter:datatype="int"/>
+ <field reporter:label="Loan Duration" name="loan_duration" reporter:datatype="text"/>
+ <field reporter:label="Fine Level Value" name="fine_level_int" reporter:datatype="int"/>
+ <field reporter:label="Fine Level" name="fine_level" reporter:datatype="text"/>
+ <field reporter:label="Creating User" name="creating_user" reporter:datatype="text"/>
+ <field reporter:label="Editing User" name="editing_user" reporter:datatype="text"/>
+ <field reporter:label="Full Call Number" name="copy_call_number_full" reporter:datatype="text" field_groups="title,common"/>
+ <field reporter:label="Call Number Prefix" name="copy_call_number_prefix" reporter:datatype="text" field_groups="title"/>
+ <field reporter:label="Call Number Label" name="copy_call_number_label" reporter:datatype="text" field_groups="title"/>
+ <field reporter:label="Call Number Suffix" name="copy_call_number_suffix" reporter:datatype="text" field_groups="title"/>
+ <field reporter:label="Full Call Number Sortkey" name="copy_call_number_sortkey_full" reporter:datatype="text" field_groups="title"/>
+ <field reporter:label="Call Number Prefix Sortkey" name="copy_call_number_prefix_sortkey" reporter:datatype="text"/>
+ <field reporter:label="Call Number Label Sortkey" name="copy_call_number_label_sortkey" reporter:datatype="text"/>
+ <field reporter:label="Call Number Suffix Sortkey" name="copy_call_number_suffix_sortkey" reporter:datatype="text"/>
+ <field reporter:label="Title" name="title" reporter:datatype="text" field_groups="title,common"/>
+ <field reporter:label="Author" name="author" reporter:datatype="text" field_groups="title,common"/>
+ <field reporter:label="Publisher" name="publisher" reporter:datatype="text" field_groups="title"/>
+ <field reporter:label="Publication Year" name="pubdate" reporter:datatype="int" field_groups="title"/>
+ <field reporter:label="ISBN" name="isbn" reporter:datatype="text" field_groups="title"/>
+ <field reporter:label="ISSN" name="issn" reporter:datatype="text" field_groups="title"/>
+ <field reporter:label="Monographic Part" name="part_label" reporter:datatype="text" field_groups="item,title"/>
+ <field reporter:label="Monographic Part Sortkey" name="part_label_sortkey" reporter:datatype="text" field_groups="item,title"/>
+ <field reporter:label="Bibliographic Record ID" name="bib_id" reporter:datatype="int" field_groups="title,common"/>
+ <field reporter:label="TCN" name="tcn_value" reporter:datatype="text" field_groups="title,common"/>
+ <field reporter:label="Call Number Deleted?" name="call_deleted" reporter:datatype="bool" sr:suggest_filter="true"/>
+ <field reporter:label="Bibliographic Record Deleted?" name="bib_deleted" reporter:datatype="bool" sr:suggest_filter="true"/>
+ <field reporter:label="Item Status Holdable?" name="status_holdable" reporter:datatype="bool"/>
+ <field reporter:label="Shelving Location Holdable?" name="location_holdable" reporter:datatype="bool"/>
+ <field reporter:label="Shelving Location Circulate?" name="location_circulate" reporter:datatype="bool"/>
+ <field reporter:label="Latest Inventory Workstation" name="inventory_workstation" reporter:datatype="text"/>
+ <field reporter:label="Estimated Price" name="est_price" reporter:datatype="money" sr:suggest_transform="sum"/>
+ <field reporter:label="Total Circulations" name="circ_total" reporter:datatype="int" sr:suggest_transform="sum" field_groups="common"/>
+ <field reporter:label="All Bibliographic Record Subjects" name="bib_subjects" reporter:datatype="text" sr:suggest_operator="contains"/>
+ </fields>
+ <links>
+ <link field="floating_id" reltype="might_have" key="id" map="" class="cfg"/>
+ <link field="status_id" reltype="has_a" key="id" map="" class="ccs"/>
+ <link sr:org_filter_field="owning_lib" field="location_id" reltype="has_a" key="id" map="" class="acpl"/>
+ <link field="copy_circ_modifier_code" reltype="might_have" key="code" map="" class="ccm"/>
+ <link field="age_protect_id" reltype="might_have" key="id" map="" class="crahp"/>
+ <link field="inv_workstation_id" reltype="might_have" key="id" map="" class="aws"/>
+ </links>
+ </class>
+
+ <class id="srwd" controller="simple-reporter.ui" oils_persist:virtual="true" reporter:label="Simple Reporter Weeding">
+ <oils_persist:source_definition><![CDATA[
+
+ SELECT
+ -- ids
+ acp.id AS copy_id,
+ acn.id AS acn_id,
+ acp.circ_lib AS circ_lib_id,
+ acp.status AS status_id,
+ acp.location AS location_id,
+ acp.age_protect AS age_protect_id,
+ aous.id AS circ_sys_id,
+ acn.owning_lib AS owning_lib_id,
+ aouos.id AS owning_sys_id,
+ awsi.id AS inv_workstation_id,
+
+ -- OUs
+ aou.shortname AS circ_lib_shortname,
+ aou.name AS circ_lib_name,
+ aous.shortname AS circ_sys_shortname,
+ aous.name AS circ_sys_name,
+ aouo.shortname AS owning_lib_shortname,
+ aouo.name AS owning_lib_name,
+ aouos.shortname AS owning_sys_shortname,
+ aouos.name AS owning_sys_name,
+
+ -- dates
+ acp.create_date AS create_date_time,
+ (EXTRACT(EPOCH FROM AGE(acp.create_date)) / 86400) AS item_age_days,
+ (EXTRACT(YEAR FROM AGE(acp.create_date)) * 12) + EXTRACT(MONTH FROM AGE(acp.create_date)) AS item_age_months,
+ EXTRACT(YEAR FROM AGE(acp.create_date)) AS item_age_years,
+ CAST(TO_CHAR(acp.create_date, 'YYYY') AS INTEGER) AS create_date_year,
+ TO_CHAR(acp.create_date, 'YYYY-MM') AS create_date_year_mon,
+ TO_CHAR(acp.create_date, 'YYYY-MM-DD') AS create_date_date,
+ acp.active_date AS active_date_time,
+ (EXTRACT(EPOCH FROM AGE(acp.active_date)) / 86400) AS item_active_days,
+ EXTRACT(YEAR FROM AGE(acp.active_date)) * 12 + EXTRACT(MONTH FROM AGE(acp.active_date)) AS item_active_months,
+ EXTRACT(YEAR FROM AGE(acp.active_date)) AS item_active_years,
+ CAST(TO_CHAR(acp.active_date, 'YYYY') AS INTEGER) AS active_date_year,
+ TO_CHAR(acp.active_date, 'YYYY-MM') AS active_date_year_mon,
+ TO_CHAR(acp.active_date, 'YYYY-MM-DD') AS active_date_date,
+ acp.edit_date AS edit_date_time,
+ (EXTRACT(EPOCH FROM AGE(acp.edit_date)) / 86400) AS item_edit_age_days,
+ (EXTRACT(YEAR FROM AGE(acp.edit_date)) * 12) + EXTRACT(MONTH FROM AGE(acp.edit_date)) AS item_edit_age_months,
+ EXTRACT(YEAR FROM AGE(acp.edit_date)) AS item_edit_age_years,
+ CAST(TO_CHAR(acp.edit_date, 'YYYY') AS INTEGER) AS edit_date_year,
+ TO_CHAR(acp.edit_date, 'YYYY-MM') AS edit_date_year_mon,
+ TO_CHAR(acp.edit_date, 'YYYY-MM-DD') AS edit_date_date,
+ acp.status_changed_time AS status_changed_time_time,
+ CAST(TO_CHAR(acp.status_changed_time, 'YYYY') AS INTEGER) AS status_changed_year,
+ TO_CHAR(acp.status_changed_time, 'YYYY-MM') AS status_changed_year_mon,
+ TO_CHAR(acp.status_changed_time, 'YYYY-MM-DD') AS status_changed_date,
+ ali.inventory_date AS latest_inv_date,
+ (EXTRACT(EPOCH FROM AGE(ali.inventory_date)) / 86400) AS inventory_age_days,
+ EXTRACT(YEAR FROM AGE(ali.inventory_date)) * 12 + EXTRACT(MONTH FROM AGE(ali.inventory_date)) AS inventory_age_months,
+ EXTRACT(YEAR FROM AGE(ali.inventory_date)) AS inventory_age_years,
+ CAST(TO_CHAR(ali.inventory_date, 'YYYY') AS INTEGER) AS inv_date_year,
+ TO_CHAR(ali.inventory_date, 'YYYY-MM') AS inv_date_year_mon,
+ TO_CHAR(ali.inventory_date, 'YYYY-MM-DD') AS inv_date_date,
+
+ -- acp
+ acp.barcode AS barcode,
+ ccs.name AS status,
+ acpl.name AS location_name,
+ acp.circ_modifier AS circ_modifier_code,
+ ccm.name AS circ_modifier_name,
+ acp.price AS price,
+ acp.cost AS acq_price,
+ acp.deleted AS copy_deleted,
+ acp.ref AS reference,
+ acp.circulate AS copy_circulate,
+ (acp.circulate AND acpl.circulate) AS circulate,
+ acp.holdable AS copy_holdable,
+ acp.deposit AS deposit,
+ acp.deposit_amount AS deposit_amount,
+ acp.alert_message AS alert_message,
+ acp.opac_visible AS copy_opac_visible,
+ (acp.opac_visible AND ccs.opac_visible) AS opac_visible,
+ crahp.name AS age_protect,
+ CASE WHEN acp.age_protect IS NULL THEN FALSE ELSE (now() - crahp.age) < acp.active_date END AS under_age_protection,
+ (acp.holdable AND ccs.holdable AND acpl.holdable) AS holdable,
+ acp.copy_number AS copy_number,
+ acp.circ_as_type AS circ_as_type,
+ acp.loan_duration AS loan_duration_int,
+ CASE acp.loan_duration WHEN 1 THEN 'short' WHEN 2 THEN 'medium' ELSE 'long' END AS loan_duration,
+ acp.fine_level AS fine_level_int,
+ CASE acp.fine_level WHEN 1 THEN 'low' WHEN 2 THEN 'medium' ELSE 'high' END AS fine_level,
+
+ -- Call Number
+ TRIM(BOTH ' ' FROM CONCAT_WS(' ', acnp.label, acn.label, acns.label)) AS copy_call_number_full,
+ acnp.label AS copy_call_number_prefix,
+ acn.label AS copy_call_number_label,
+ acns.label AS copy_call_number_suffix,
+ TRIM(BOTH ' ' FROM concat_ws(' ', acnp.label_sortkey, acn.label_sortkey, acns.label_sortkey)) AS copy_call_number_sortkey_full,
+ acnp.label_sortkey AS copy_call_number_prefix_sortkey,
+ acn.label_sortkey AS copy_call_number_label_sortkey,
+ acns.label_sortkey AS copy_call_number_suffix_sortkey,
+ racnd.dewey AS call_number_dewey,
+ racnd.dewey_block_tens AS call_number_dewey_block_tens,
+ racnd.dewey_block_hundreds AS call_number_dewey_block_hundreds,
+ racnd.dewey_range_tens AS call_number_dewey_range_tens,
+ racnd.dewey_range_hundreds AS call_number_dewey_range_hundreds,
+
+ -- Misc
+ COALESCE(rmsr.title, acp.dummy_title) AS title,
+ COALESCE(rmsr.author, acp.dummy_author) AS author,
+ rmsr.publisher AS publisher,
+ public.approximate_date(rmsr.pubdate, '0') AS pubdate,
+ COALESCE(array_to_string(rmsr.isbn, ', '), acp.dummy_isbn) AS isbn,
+ array_to_string(rmsr.issn, ', ') AS issn,
+ part.label AS part_label,
+ part.label_sortkey AS part_label_sortkey,
+ acn.record AS bib_id,
+ rmsr.tcn_value AS tcn_value,
+ acn.deleted AS call_deleted,
+ bre.deleted AS bib_deleted,
+ ccs.holdable AS status_holdable,
+ acpl.holdable AS location_holdable,
+ acp.circulate AS location_circulate,
+ awsi.name AS inventory_workstation,
+
+ -- Aggregates
+ -- may decide these price games aren't worth it, but estimated collection value is useful
+ COALESCE(acp.cost, acp.price, (select first(value::numeric(8,2)) from actor.org_unit_ancestor_setting('cat.default_item_price', acp.circ_lib)), 5.00) AS est_price,
+ STRING_AGG(mfde_subj.value, ', ') AS bib_subjects,
+ MAX(combcirc.xact_start) AS last_circ_datetime,
+ (EXTRACT(EPOCH FROM AGE(MAX(combcirc.xact_start))) / 86400) AS last_circ_age_days,
+ (EXTRACT(YEAR FROM AGE(MAX(combcirc.xact_start))) * 12) + EXTRACT(MONTH FROM AGE(MAX(combcirc.xact_start))) AS last_circ_age_months,
+ EXTRACT(YEAR FROM AGE(MAX(combcirc.xact_start))) AS last_circ_age_years,
+ CAST(TO_CHAR(MAX(combcirc.xact_start), 'YYYY') AS INTEGER) AS last_circ_year,
+ TO_CHAR(MAX(combcirc.xact_start), 'YYYY-MM') AS last_circ_year_mon,
+ TO_CHAR(MAX(combcirc.xact_start), 'YYYY-MM-DD') AS last_circ_date,
+ COALESCE(MAX(combcirc.xact_start), acp.create_date) AS last_circ_create_datetime,
+ (EXTRACT(EPOCH FROM AGE(COALESCE(MAX(combcirc.xact_start), acp.create_date))) / 86400) AS last_circ_create_age_days,
+ (EXTRACT(YEAR FROM AGE(COALESCE(MAX(combcirc.xact_start), acp.create_date))) * 12) + EXTRACT(MONTH FROM AGE(COALESCE(MAX(combcirc.xact_start), acp.create_date))) AS last_circ_create_age_months,
+ EXTRACT(YEAR FROM AGE(COALESCE(MAX(combcirc.xact_start), acp.create_date))) AS last_circ_create_age_years,
+ CAST(TO_CHAR(COALESCE(MAX(combcirc.xact_start), acp.create_date), 'YYYY') AS INTEGER) AS last_circ_create_year,
+ TO_CHAR(COALESCE(MAX(combcirc.xact_start), acp.create_date), 'YYYY-MM') AS last_circ_create_year_mon,
+ TO_CHAR(COALESCE(MAX(combcirc.xact_start), acp.create_date), 'YYYY-MM-DD') AS last_circ_create_date,
+ COUNT(DISTINCT combcirc.id) AS circ_total, -- good for finding low performers to lose or ragged copies to replace
+ COUNT(DISTINCT CASE WHEN date_part('year', now()) = date_part('year', combcirc.xact_start) THEN combcirc.id ELSE NULL END) AS circ_ytd,
+ COUNT(DISTINCT CASE WHEN date_part('year', now() - '1 year'::interval) = date_part('year', combcirc.xact_start) THEN combcirc.id ELSE NULL END) AS circ_last_year,
+ COUNT(DISTINCT CASE WHEN date_part('year', now() - '2 year'::interval) = date_part('year', combcirc.xact_start) THEN combcirc.id ELSE NULL END) AS circ_two_years_ago,
+ COUNT(DISTINCT CASE WHEN date_part('year', now() - '3 year'::interval) = date_part('year', combcirc.xact_start) THEN combcirc.id ELSE NULL END) AS circ_three_years_ago,
+ COUNT(DISTINCT CASE WHEN date_part('year', now() - '5 year'::interval) <= date_part('year', combcirc.xact_start) THEN combcirc.id ELSE NULL END) AS circ_last_five_years
+
+ FROM
+ asset.copy acp
+ LEFT JOIN config.circ_modifier ccm ON (acp.circ_modifier = ccm.code)
+ INNER JOIN asset.call_number acn ON (acp.call_number = acn.id)
+ LEFT JOIN reporter.asset_call_number_dewey racnd ON (acn.id = racnd.call_number)
+ INNER JOIN config.copy_status ccs ON (acp.status = ccs.id)
+ LEFT JOIN config.rule_age_hold_protect crahp ON (acp.age_protect = crahp.id)
+ INNER JOIN asset.copy_location acpl ON (acp.location = acpl.id)
+ INNER JOIN asset.call_number_prefix acnp ON (acn.prefix = acnp.id)
+ INNER JOIN asset.call_number_suffix acns ON (acn.suffix = acns.id)
+ LEFT JOIN asset.latest_inventory ali ON (acp.id = ali.copy)
+ LEFT JOIN actor.workstation awsi ON (ali.inventory_workstation = awsi.id)
+ LEFT JOIN reporter.materialized_simple_record rmsr ON (acn.record = rmsr.id)
+ INNER JOIN actor.org_unit aou ON (acp.circ_lib = aou.id)
+ LEFT JOIN actor.org_unit aous ON ((actor.org_unit_ancestor_at_depth(acp.circ_lib, 1)).id = aous.id)
+ INNER JOIN actor.org_unit aouo ON (acn.owning_lib = aouo.id)
+ LEFT JOIN actor.org_unit aouos ON ((actor.org_unit_ancestor_at_depth(acn.owning_lib, 1)).id = aouos.id)
+ INNER JOIN biblio.record_entry bre ON (acn.record = bre.id)
+ LEFT JOIN metabib.flat_display_entry mfde_subj ON (acn.record = mfde_subj.source AND mfde_subj.name = 'subject')
+ LEFT JOIN action.all_circulation combcirc ON (acp.id = combcirc.target_copy)
+ LEFT JOIN (
+ SELECT bmp.record, bmp.label, bmp.label_sortkey, acmp.target_copy
+ FROM biblio.monograph_part bmp
+ INNER JOIN asset.copy_part_map acmp ON (acmp.part = bmp.id)
+ WHERE NOT bmp.deleted
+ ) part ON (part.record = acn.record AND part.target_copy = acp.id)
+
+ GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,
+ 16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,
+ 31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,
+ 46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,
+ 61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,
+ 76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,
+ 91,92,93,94,95,96,97,98,99,100,101,102,103,104,105
+
+ ]]></oils_persist:source_definition>
+ <field_groups>
+ <group name="common" reporter:label="Common Fields"/>
+ <group name="dates" reporter:label="Date Fields"/>
+ <group name="inv" reporter:label="Inventory Fields"/>
+ <group name="title" reporter:label="Call Number and Title Fields"/>
+ <group name="org" reporter:label="Libraries"/>
+ </field_groups>
+ <fields oils_persist:primary="copy_id">
+ <field reporter:label="Item ID" name="copy_id" reporter:datatype="int" sr:suggest_transform="count_distinct" field_groups="common"/>
+ <field reporter:label="Call Number ID" name="acn_id" reporter:datatype="int" sr:hide_from="display"/>
+ <field reporter:label="Circulating Library" name="circ_lib_id" reporter:datatype="org_unit" sr:hide_from="display" field_groups="org,common" sr:suggest_filter="true"/>
+ <field reporter:label="Item Status" name="status_id" reporter:datatype="link" sr:hide_from="display" sr:suggest_filter="true"/>
+ <field reporter:label="Shelving Location" name="location_id" reporter:datatype="link" sr:hide_from="display" field_groups="common" sr:suggest_filter="true"/>
+ <field reporter:label="Age Protection" name="age_protect_id" reporter:datatype="link" sr:hide_from="display"/>
+ <field reporter:label="Circulating System" name="circ_sys_id" reporter:datatype="org_unit" sr:hide_from="display" field_groups="org,common"/>
+ <field reporter:label="Owning Library" name="owning_lib_id" reporter:datatype="org_unit" sr:hide_from="display" field_groups="org,common" sr:suggest_filter="true"/>
+ <field reporter:label="Owning System" name="owning_sys_id" reporter:datatype="org_unit" sr:hide_from="display" field_groups="org,common"/>
+ <field reporter:label="Last Inventory Workstation" name="inv_workstation_id" reporter:datatype="link" sr:hide_from="display" field_groups="inv"/>
+ <field reporter:label="Circulating Library Short (Policy) Name" name="circ_lib_shortname" reporter:datatype="text" sr:hide_from="filter" field_groups="org,common"/>
+ <field reporter:label="Circulating Library Name" name="circ_lib_name" reporter:datatype="text" sr:hide_from="filter" field_groups="org"/>
+ <field reporter:label="Circulating System Short (Policy) Name" name="circ_sys_shortname" reporter:datatype="text" sr:hide_from="filter" field_groups="org,common"/>
+ <field reporter:label="Circulating System Name" name="circ_sys_name" reporter:datatype="text" sr:hide_from="filter" field_groups="org"/>
+ <field reporter:label="Owning Library Short (Policy) Name" name="owning_lib_shortname" reporter:datatype="text" sr:hide_from="filter" field_groups="org,common"/>
+ <field reporter:label="Owning Library Name" name="owning_lib_name" reporter:datatype="text" sr:hide_from="filter" field_groups="org,common"/>
+ <field reporter:label="Owning System Short (Policy) Name" name="owning_sys_shortname" reporter:datatype="text" sr:hide_from="filter" field_groups="org"/>
+ <field reporter:label="Owning System Name" name="owning_sys_name" reporter:datatype="text" sr:hide_from="filter" field_groups="org"/>
+ <field reporter:label="Last Circulated Date / Time" name="last_circ_datetime" reporter:datatype="timestamp" field_groups="dates,common" sr:suggest_filter="true"/>
+ <field reporter:label="Last Circulated Days Ago" name="last_circ_age_days" reporter:datatype="int" field_groups="dates"/>
+ <field reporter:label="Last Circluated Months Ago" name="last_circ_age_months" reporter:datatype="int" field_groups="dates"/>
+ <field reporter:label="Last Circulated Years Ago" name="last_circ_age_years" reporter:datatype="int" field_groups="dates"/>
+ <field reporter:label="Last Circulated Year" name="last_circ_year" reporter:datatype="int" field_groups="dates"/>
+ <field reporter:label="Last Circulated Year and Month" name="last_circ_year_mon" reporter:datatype="text" field_groups="dates"/>
+ <field reporter:label="Last Circulated Date" name="last_circ_date" reporter:datatype="text" field_groups="dates"/>
+ <field reporter:label="Last Circulated or Item Created Date / Time" name="last_circ_create_datetime" reporter:datatype="timestamp" field_groups="dates,common" sr:suggest_filter="true"/>
+ <field reporter:label="Last Circulated or Item Created Days Ago" name="last_circ_create_age_days" reporter:datatype="int" field_groups="dates"/>
+ <field reporter:label="Last Circulated or Item Created Months Ago" name="last_circ_create_age_months" reporter:datatype="int" field_groups="dates"/>
+ <field reporter:label="Last Circulated or Item Created Years Ago" name="last_circ_create_age_years" reporter:datatype="int" field_groups="dates"/>
+ <field reporter:label="Last Circulated or Item Created Year" name="last_circ_create_year" reporter:datatype="int" field_groups="dates"/>
+ <field reporter:label="Last Circulated or Item Created Year and Month" name="last_circ_create_year_mon" reporter:datatype="text" field_groups="dates"/>
+ <field reporter:label="Last Circulated or Item Created Date" name="last_circ_create_date" reporter:datatype="text" field_groups="dates"/>
+ <field reporter:label="Create Date / Time" name="create_date_time" reporter:datatype="timestamp" field_groups="dates"/>
+ <field reporter:label="Item Age (Days)" name="item_age_days" reporter:datatype="int" field_groups="dates"/>
+ <field reporter:label="Item Age (Months)" name="item_age_months" reporter:datatype="int" field_groups="dates"/>
+ <field reporter:label="Item Age (Years)" name="item_age_years" reporter:datatype="int" field_groups="dates"/>
+ <field reporter:label="Create Date Year" name="create_date_year" reporter:datatype="int" field_groups="dates"/>
+ <field reporter:label="Create Date Year and Month" name="create_date_year_mon" reporter:datatype="text" field_groups="dates"/>
+ <field reporter:label="Create Date" name="create_date_date" reporter:datatype="text" field_groups="dates"/>
+ <field reporter:label="Active Date / Time" name="active_date_time" reporter:datatype="timestamp" field_groups="dates"/>
+ <field reporter:label="Active For (Days)" name="item_active_days" reporter:datatype="int" field_groups="dates"/>
+ <field reporter:label="Active For (Months)" name="item_active_months" reporter:datatype="int" field_groups="dates"/>
+ <field reporter:label="Active For (Years)" name="item_active_years" reporter:datatype="int" field_groups="dates"/>
+ <field reporter:label="Active Date Year" name="active_date_year" reporter:datatype="int" field_groups="dates"/>
+ <field reporter:label="Active Date Year and Month" name="active_date_year_mon" reporter:datatype="text" field_groups="dates"/>
+ <field reporter:label="Active Date" name="active_date_date" reporter:datatype="text" field_groups="dates"/>
+ <field reporter:label="Last Edit Date / Time" name="edit_date_time" reporter:datatype="timestamp" field_groups="dates"/>
+ <field reporter:label="Last Edited Days Ago" name="item_edit_age_days" reporter:datatype="int" field_groups="dates"/>
+ <field reporter:label="Last Edited Months Ago" name="item_edit_age_months" reporter:datatype="int" field_groups="dates"/>
+ <field reporter:label="Last Edited Years Ago" name="item_edit_age_years" reporter:datatype="int" field_groups="dates"/>
+ <field reporter:label="Last Edit Date Year" name="edit_date_year" reporter:datatype="int" field_groups="dates"/>
+ <field reporter:label="Last Edit Date Year and Month" name="edit_date_year_mon" reporter:datatype="text" field_groups="dates"/>
+ <field reporter:label="Last Edit Date" name="edit_date_date" reporter:datatype="text" field_groups="dates"/>
+ <field reporter:label="Item Status Changed Date / Time" name="status_changed_time_time" reporter:datatype="timestamp" field_groups="dates"/>
+ <field reporter:label="Item Status Changed Year" name="status_changed_year" reporter:datatype="int" field_groups="dates"/>
+ <field reporter:label="Item Status Changed Year and Month" name="status_changed_year_mon" reporter:datatype="text" field_groups="dates"/>
+ <field reporter:label="Item Status Changed Date" name="status_changed_date" reporter:datatype="text" field_groups="dates"/>
+ <field reporter:label="Last Inventoried Date / Time" name="latest_inv_date" reporter:datatype="timestamp" field_groups="inv,dates"/>
+ <field reporter:label="Last Inventoried Days Ago" name="inventory_age_days" reporter:datatype="int" field_groups="inv,dates"/>
+ <field reporter:label="Last Inventoried Months Ago" name="inventory_age_months" reporter:datatype="int" field_groups="inv,dates"/>
+ <field reporter:label="Last Inventoried Years Ago" name="inventory_age_years" reporter:datatype="int" field_groups="inv,dates"/>
+ <field reporter:label="Last Inventoried Year" name="inv_date_year" reporter:datatype="int" field_groups="inv,dates"/>
+ <field reporter:label="Last Inventoried Year and Month" name="inv_date_year_mon" reporter:datatype="text" field_groups="inv,dates"/>
+ <field reporter:label="Last Inventoried Date" name="inv_date_date" reporter:datatype="text" field_groups="inv,dates"/>
+ <field reporter:label="Barcode" name="barcode" reporter:datatype="text" field_groups="common"/>
+ <field reporter:label="Item Status" name="status" reporter:datatype="text" field_groups="common"/>
+ <field reporter:label="Shelving Location" name="location_name" reporter:datatype="text" field_groups="common"/>
+ <field reporter:label="Circulation Modifier Code" name="circ_modifier_code" reporter:datatype="text" field_groups="common" sr:suggest_filter="true"/>
+ <field reporter:label="Circulation Modifier Name" name="circ_modifier_name" reporter:datatype="text" sr:hide_from="filter" field_groups="common"/>
+ <field reporter:label="Price" name="price" reporter:datatype="money" sr:suggest_transform="sum"/>
+ <field reporter:label="Acquisition Cost" name="acq_price" reporter:datatype="money" sr:suggest_transform="sum"/>
+ <field reporter:label="Item Deleted?" name="copy_deleted" reporter:datatype="bool" sr:suggest_filter="true"/>
+ <field reporter:label="Reference?" name="reference" reporter:datatype="bool"/>
+ <field reporter:label="Item Circulate?" name="copy_circulate" reporter:datatype="bool"/>
+ <field reporter:label="Circulate?" name="circulate" reporter:datatype="bool"/>
+ <field reporter:label="Item Holdable?" name="copy_holdable" reporter:datatype="bool"/>
+ <field reporter:label="Deposit?" name="deposit" reporter:datatype="bool"/>
+ <field reporter:label="Deposit Amount" name="deposit_amount" reporter:datatype="money" sr:suggest_transform="sum"/>
+ <field reporter:label="Alert Messge" name="alert_message" reporter:datatype="text"/>
+ <field reporter:label="Item OPAC Visible?" name="copy_opac_visible" reporter:datatype="bool"/>
+ <field reporter:label="OPAC Visible?" name="opac_visible" reporter:datatype="bool"/>
+ <field reporter:label="Age Protection Rule" name="age_protect" reporter:datatype="text" sr:hide_from="filter"/>
+ <field reporter:label="Under Age Protection?" name="under_age_protection" reporter:datatype="bool"/>
+ <field reporter:label="Holdable?" name="holdable" reporter:datatype="bool"/>
+ <field reporter:label="Item Number" name="copy_number" reporter:datatype="int"/>
+ <field reporter:label="Item Circ As Type" name="circ_as_type" reporter:datatype="text"/>
+ <field reporter:label="Loan Duration (Int)" name="loan_duration_int" reporter:datatype="int"/>
+ <field reporter:label="Loan Duration" name="loan_duration" reporter:datatype="text"/>
+ <field reporter:label="Fine Level (Int)" name="fine_level_int" reporter:datatype="int"/>
+ <field reporter:label="Fine Level" name="fine_level" reporter:datatype="text"/>
+ <field reporter:label="Full Call Number" name="copy_call_number_full" reporter:datatype="text" field_groups="title,common"/>
+ <field reporter:label="Call Number Prefix" name="copy_call_number_prefix" reporter:datatype="text" field_groups="title"/>
+ <field reporter:label="Call Number Label" name="copy_call_number_label" reporter:datatype="text" field_groups="title,common"/>
+ <field reporter:label="Call Number Suffix" name="copy_call_number_suffix" reporter:datatype="text" field_groups="title"/>
+ <field reporter:label="Call Number Dewey" name="call_number_dewey" reporter:datatype="text" field_groups="title"/>
+ <field reporter:label="Full Call Number Sortkey" name="copy_call_number_sortkey_full" reporter:datatype="text" field_groups="title"/>
+ <field reporter:label="Call Number Prefix Sortkey" name="copy_call_number_prefix_sortkey" reporter:datatype="text"/>
+ <field reporter:label="Call Number Label Sortkey" name="copy_call_number_label_sortkey" reporter:datatype="text"/>
+ <field reporter:label="Call Number Suffix Sortkey" name="copy_call_number_suffix_sortkey" reporter:datatype="text"/>
+ <field reporter:label="Call Number Dewey Block Tens" name="call_number_dewey_block_tens" reporter:datatype="text" field_groups="title,common"/>
+ <field reporter:label="Call Number Dewey Block Hundreds" name="call_number_dewey_block_hundreds" reporter:datatype="text" field_groups="title,common"/>
+ <field reporter:label="Call Number Dewey Range Tens" name="call_number_dewey_range_tens" reporter:datatype="text" field_groups="title"/>
+ <field reporter:label="Call Number Dewey Range Hundreds" name="call_number_dewey_range_hundreds" reporter:datatype="text" field_groups="title"/>
+ <field reporter:label="Title" name="title" reporter:datatype="text" field_groups="title,common"/>
+ <field reporter:label="Author" name="author" reporter:datatype="text" field_groups="title,common"/>
+ <field reporter:label="Publisher" name="publisher" reporter:datatype="text" field_groups="title"/>
+ <field reporter:label="Publication Year" name="pubdate" reporter:datatype="int" field_groups="title"/>
+ <field reporter:label="ISBN" name="isbn" reporter:datatype="text" field_groups="title"/>
+ <field reporter:label="ISSN" name="issn" reporter:datatype="text" field_groups="title"/>
+ <field reporter:label="Monographic Part" name="part_label" reporter:datatype="text" field_groups="item,title"/>
+ <field reporter:label="Monographic Part Sortkey" name="part_label_sortkey" reporter:datatype="text" field_groups="item,title"/>
+ <field reporter:label="Bibliographic Record ID" name="bib_id" reporter:datatype="int" field_groups="title,common"/>
+ <field reporter:label="TCN" name="tcn_value" reporter:datatype="text" field_groups="title,common"/>
+ <field reporter:label="Call Number Deleted?" name="call_deleted" reporter:datatype="bool"/>
+ <field reporter:label="Bibliographic Record Deleted?" name="bib_deleted" reporter:datatype="bool"/>
+ <field reporter:label="Item Status Holdable?" name="status_holdable" reporter:datatype="bool"/>
+ <field reporter:label="Shelving Location Holdable?" name="location_holdable" reporter:datatype="bool"/>
+ <field reporter:label="Shelving Location Circulate?" name="location_circulate" reporter:datatype="bool"/>
+ <field reporter:label="Latest Inventory Workstation" name="inventory_workstation" reporter:datatype="text" field_groups="inv"/>
+ <field reporter:label="Estimated Price" name="est_price" reporter:datatype="money" sr:suggest_transform="sum"/>
+ <field reporter:label="All Subjects" name="bib_subjects" reporter:datatype="text" sr:suggest_operator="contains"/>
+ <field reporter:label="Circulation Total" name="circ_total" reporter:datatype="int" sr:suggest_transform="sum" sr:suggest_filter="true" field_groups="common"/>
+ <field reporter:label="Circulation Year To Date" name="circ_ytd" reporter:datatype="int" sr:suggest_transform="sum" sr:suggest_filter="true" field_groups="common"/>
+ <field reporter:label="Circulation Last Year" name="circ_last_year" reporter:datatype="int" sr:suggest_transform="sum" sr:suggest_filter="true" field_groups="common"/>
+ <field reporter:label="Circulation Two Years Ago" name="circ_two_years_ago" reporter:datatype="int" sr:suggest_transform="sum" sr:suggest_filter="true" field_groups="common"/>
+ <field reporter:label="Circulation Three Years Ago" name="circ_three_years_ago" reporter:datatype="int" sr:suggest_transform="sum" sr:suggest_filter="true" field_groups="common"/>
+ <field reporter:label="Circulation Total Last Five Years" name="circ_last_five_years" reporter:datatype="int" sr:suggest_transform="sum" sr:suggest_filter="true" field_groups="common"/>
+ </fields>
+ <links>
+ <link field="status_id" reltype="has_a" key="id" map="" class="ccs"/>
+ <link sr:org_filter_field="owning_lib" field="location_id" reltype="has_a" key="id" map="" class="acpl"/>
+ <link field="circ_modifier_code" reltype="might_have" key="code" map="" class="ccm"/>
+ <link field="age_protect_id" reltype="might_have" key="id" map="" class="crahp"/>
+ <link field="inv_workstation_id" reltype="might_have" key="id" map="" class="aws"/>
+ </links>
+ </class>
+
+ <class id="srbps" controller="simple-reporter.ui" oils_persist:virtual="true" reporter:label="Simple Reporter Billing and Payments Transaction Summary">
+ <oils_persist:source_definition><![CDATA[
+
+ SELECT
+ -- ids
+ xact.id AS xact_id,
+ COALESCE(circ.circ_lib, mg.billing_location) AS billing_lib_id,
+ COALESCE(aoubs.id, aougbs.id) AS billing_sys_id,
+
+ -- OUs
+ COALESCE(aoub.shortname, aougb.shortname) AS billing_lib_shortname,
+ COALESCE(aoub.name, aougb.shortname) AS billing_lib_name,
+ COALESCE(aoubs.shortname, aougbs.shortname) AS billing_sys_shortname,
+ COALESCE(aoubs.name, aougbs.name) AS billing_sys_name,
+
+ -- billings
+ billings.total_amount AS billing_grand_total,
+ billings.total_unvoided_amount AS billing_total,
+
+ billings.voided_billing_count AS billing_voided_count,
+ billings.all_billing_types AS all_billing_types,
+ billings.billing_types AS billing_types,
+
+ billings.total_voided_amount AS billing_total_voided,
+ billings.total_overdue_amount AS billing_overdue_amount,
+ billings.total_lost_amount AS billing_lost_amount,
+ billings.total_long_od_amount AS billing_long_od_amount,
+ billings.total_damaged_amount AS billing_damaged_amount,
+ billings.total_deposit_amount AS billing_deposit_amount,
+ billings.total_rental_amount AS billing_rental_amount,
+
+ -- payments
+ payments.total_amount AS payment_grand_total,
+ payments.total_unvoided_amount AS payment_total,
+
+ payments.voided_payment_count AS payment_voided_count,
+ payments.all_payment_types AS all_payment_types,
+ payments.payment_types AS payment_types,
+
+ payments.total_voided_amount AS payment_total_voided,
+ payments.total_account_adjustment AS payment_account_adjustment_amount,
+ payments.total_cash_payment AS payment_cash_amount,
+ payments.total_check_payment AS payment_check_amount,
+ payments.total_credit_card_payment AS payment_credit_card_amount,
+ payments.total_debit_card_payment AS payment_debit_card_amount,
+ payments.total_credit_payment AS payment_credit_amount,
+ payments.total_forgive_payment AS payment_forgive_amount,
+ payments.total_work_payment AS payment_work_amount,
+ payments.total_goods_payment AS payment_goods_amount,
+ payments.total_currency_payment AS payment_currency_amount,
+ payments.total_non_currency_payment AS payment_non_currency_amount,
+
+ -- dates
+ xact.xact_start AS xact_start,
+ CAST(TO_CHAR(xact.xact_start, 'YYYY') AS INTEGER) AS xact_start_year,
+ TO_CHAR(xact.xact_start, 'YYYY-MM') AS xact_start_year_mon,
+ TO_CHAR(xact.xact_start, 'YYYY-MM-DD') AS xact_start_date,
+ xact.xact_finish AS xact_finish,
+ CAST(TO_CHAR(xact.xact_finish, 'YYYY') AS INTEGER) AS xact_finish_year,
+ TO_CHAR(xact.xact_finish, 'YYYY-MM') AS xact_finish_year_mon,
+ TO_CHAR(xact.xact_finish, 'YYYY-MM-DD') AS xact_finish_date,
+ billings.earliest_billing_ts AS earliest_billing_ts,
+ CAST(TO_CHAR(billings.earliest_billing_ts, 'YYYY') AS INTEGER) AS earliest_billing_year,
+ TO_CHAR(billings.earliest_billing_ts, 'YYYY-MM') AS earliest_billing_year_mon,
+ TO_CHAR(billings.earliest_billing_ts, 'YYYY-MM-DD') AS earliest_billing_date,
+ billings.latest_billing_ts AS latest_billing_ts,
+ CAST(TO_CHAR(billings.latest_billing_ts, 'YYYY') AS INTEGER) AS latest_billing_year,
+ TO_CHAR(billings.latest_billing_ts, 'YYYY-MM') AS latest_billing_year_mon,
+ TO_CHAR(billings.latest_billing_ts, 'YYYY-MM-DD') AS latest_billing_date,
+ payments.earliest_payment_ts AS earliest_payment_ts,
+ CAST(TO_CHAR(payments.earliest_payment_ts, 'YYYY') AS INTEGER) AS earliest_payment_year,
+ TO_CHAR(payments.earliest_payment_ts, 'YYYY-MM') AS earliest_payment_year_mon,
+ TO_CHAR(payments.earliest_payment_ts, 'YYYY-MM-DD') AS earliest_payment_date,
+ payments.latest_payment_ts AS latest_payment_ts,
+ CAST(TO_CHAR(payments.latest_payment_ts, 'YYYY') AS INTEGER) AS latest_payment_year,
+ TO_CHAR(payments.latest_payment_ts, 'YYYY-MM') AS latest_payment_year_mon,
+ TO_CHAR(payments.latest_payment_ts, 'YYYY-MM-DD') AS latest_payment_date,
+
+ -- Misc
+ CASE WHEN circ.id IS NOT NULL THEN 'circulation' WHEN mg.id IS NOT NULL THEN 'grocery' ELSE 'aged' END AS xact_type,
+ (xact.xact_finish IS NOT NULL) AS xact_closed
+
+ FROM
+ money.billable_xact xact
+ LEFT JOIN action.circulation circ ON (xact.id = circ.id)
+ LEFT JOIN money.grocery mg ON (xact.id = mg.id)
+ LEFT JOIN actor.org_unit aoub ON (circ.circ_lib = aoub.id)
+ LEFT JOIN actor.org_unit aougb ON (mg.billing_location = aougb.id)
+ LEFT JOIN actor.org_unit aoubs ON ((actor.org_unit_ancestor_at_depth(circ.circ_lib, 1)).id = aoubs.id)
+ LEFT JOIN actor.org_unit aougbs ON ((actor.org_unit_ancestor_at_depth(mg.billing_location, 1)).id = aougbs.id)
+
+ -- oohh nooo.... If not done this way you encounter situations
+ -- like multiplying billing totals x number of payments and
+ -- payment totals x number of billings and other fabulous fiduciary fantasies.
+ LEFT JOIN (
+ SELECT
+ xact,
+ MIN(billing_ts) AS earliest_billing_ts,
+ MAX(billing_ts) AS latest_billing_ts,
+
+ STRING_AGG(DISTINCT billing_type, ', ') AS all_billing_types,
+ STRING_AGG(DISTINCT CASE WHEN NOT voided THEN billing_type ELSE NULL END, ', ') AS billing_types,
+ COUNT(DISTINCT CASE WHEN voided THEN id ELSE NULL END) AS voided_billing_count,
+
+ SUM(amount) AS total_amount,
+ SUM(CASE WHEN NOT voided THEN amount ELSE NULL END) AS total_unvoided_amount,
+ SUM(CASE WHEN voided THEN amount ELSE NULL END) AS total_voided_amount,
+ SUM(CASE WHEN NOT voided AND btype = 1 THEN amount ELSE NULL END) AS total_overdue_amount,
+ SUM(CASE WHEN NOT voided AND btype IN (3,4) THEN amount ELSE NULL END) AS total_lost_amount,
+ SUM(CASE WHEN NOT voided AND btype IN (10,11) THEN amount ELSE NULL END) AS total_long_od_amount,
+ SUM(CASE WHEN NOT voided AND btype IN (7,8) THEN amount ELSE NULL END) AS total_damaged_amount, -- Likely incomplete because damaged billing interface is whack.
+ SUM(CASE WHEN NOT voided AND btype = 5 THEN amount ELSE NULL END) AS total_deposit_amount,
+ SUM(CASE WHEN NOT voided AND btype = 6 THEN amount ELSE NULL END) AS total_rental_amount
+
+ FROM
+ money.all_billings
+ GROUP BY 1
+ ) billings ON (xact.id = billings.xact)
+ -- Can't include accepting user or cash_drawer because that will "duplicate"
+ -- rows if multiple staff accept payments on a single xact...
+ LEFT JOIN (
+ SELECT
+ xact,
+ MIN(payment_ts) AS earliest_payment_ts,
+ MAX(payment_ts) AS latest_payment_ts,
+
+ STRING_AGG(DISTINCT payment_type, ', ') AS all_payment_types,
+ STRING_AGG(DISTINCT CASE WHEN NOT voided THEN payment_type ELSE NULL END, ', ') AS payment_types,
+ COUNT(DISTINCT CASE WHEN voided THEN id ELSE NULL END) AS voided_payment_count,
+
+ SUM(amount) AS total_amount,
+ SUM(CASE WHEN NOT voided THEN amount ELSE NULL END) AS total_unvoided_amount,
+ SUM(CASE WHEN voided THEN amount ELSE NULL END) AS total_voided_amount, -- almost certainly 0 in every database, but...
+ SUM(CASE WHEN NOT voided AND payment_type = 'account_adjustment' THEN amount ELSE NULL END) AS total_account_adjustment,
+ SUM(CASE WHEN NOT voided AND payment_type = 'cash_payment' THEN amount ELSE NULL END) AS total_cash_payment,
+ SUM(CASE WHEN NOT voided AND payment_type = 'check_payment' THEN amount ELSE NULL END) AS total_check_payment,
+ SUM(CASE WHEN NOT voided AND payment_type = 'credit_card_payment' THEN amount ELSE NULL END) AS total_credit_card_payment,
+ SUM(CASE WHEN NOT voided AND payment_type = 'debit_card_payment' THEN amount ELSE NULL END) AS total_debit_card_payment,
+ SUM(CASE WHEN NOT voided AND payment_type = 'credit_payment_payment' THEN amount ELSE NULL END) AS total_credit_payment,
+ SUM(CASE WHEN NOT voided AND payment_type = 'forgive_payment' THEN amount ELSE NULL END) AS total_forgive_payment,
+ SUM(CASE WHEN NOT voided AND payment_type = 'work_payment' THEN amount ELSE NULL END) AS total_work_payment,
+ SUM(CASE WHEN NOT voided AND payment_type = 'goods_payment' THEN amount ELSE NULL END) AS total_goods_payment,
+ SUM(CASE WHEN NOT voided AND payment_type IN ('cash_payment', 'check_payment', 'credit_card_payment', 'debit_card_payment', 'credit_payment') THEN amount ELSE NULL END) AS total_currency_payment,
+ SUM(CASE WHEN NOT voided AND payment_type NOT IN ('cash_payment', 'check_payment', 'credit_card_payment', 'debit_card_payment', 'credit_payment') THEN amount ELSE NULL END) AS total_non_currency_payment
+
+ FROM
+ money.all_payments
+ GROUP BY 1
+ ) payments ON (xact.id = payments.xact)
+
+ ]]></oils_persist:source_definition>
+ <field_groups>
+ <group name="common" reporter:label="Common Fields"/>
+ <group name="bill" reporter:label="Billing Fields"/>
+ <group name="pay" reporter:label="Payment Fields"/>
+ <group name="dates" reporter:label="Date Fields"/>
+ <group name="org" reporter:label="Libraries"/>
+ </field_groups>
+ <fields oils_persist:primary="xact_id">
+ <field reporter:label="Transaction ID" name="xact_id" reporter:datatype="text" sr:suggest_transform="count_distinct" field_groups="common"/>
+ <field reporter:label="Billing Library" name="billing_lib_id" reporter:datatype="org_unit" sr:hide_from="display" sr:suggest_filter="true" field_groups="org,common"/>
+ <field reporter:label="Billing System" name="billing_sys_id" reporter:datatype="org_unit" sr:hide_from="display" sr:suggest_filter="true" field_groups="org,common"/>
+ <field reporter:label="Billing Library Short (Policy) Name" name="billing_lib_shortname" reporter:datatype="text" sr:hide_from="filter" field_groups="org,common"/>
+ <field reporter:label="Billing Library Name" name="billing_lib_name" reporter:datatype="text" sr:hide_from="filter" field_groups="org,common"/>
+ <field reporter:label="Billing System Short (Policy) Name" name="billing_sys_shortname" reporter:datatype="text" sr:hide_from="filter" field_groups="org,common"/>
+ <field reporter:label="Billing System Name" name="billing_sys_name" reporter:datatype="text" sr:hide_from="filter" field_groups="org"/>
+ <field reporter:label="Billing Grand Total (Includes Voids)" name="billing_grand_total" reporter:datatype="money" field_groups="bill" sr:suggest_transform="sum"/>
+ <field reporter:label="Billing Total" name="billing_total" reporter:datatype="money" sr:suggest_filter="true" field_groups="bill,common" sr:suggest_transform="sum"/>
+ <field reporter:label="Voided Billings" name="billing_voided_count" reporter:datatype="text" field_groups="bill,common"/>
+ <field reporter:label="Billing Types (Include Voids)" name="all_billing_types" reporter:datatype="text" field_groups="bill" sr:suggest_operator="contains"/>
+ <field reporter:label="Billing Types" name="billing_types" reporter:datatype="text" field_groups="bill,common" sr:suggest_operator="contains"/>
+ <field reporter:label="Billing Amount Voided" name="billing_total_voided" reporter:datatype="money" field_groups="bill" sr:suggest_transform="sum"/>
+ <field reporter:label="Billing Total (Overdue)" name="billing_overdue_amount" reporter:datatype="money" field_groups="bill" sr:suggest_transform="sum"/>
+ <field reporter:label="Billing Total (Lost)" name="billing_lost_amount" reporter:datatype="money" field_groups="bill" sr:suggest_transform="sum"/>
+ <field reporter:label="Billing Total (Long Overdue)" name="billing_long_od_amount" reporter:datatype="money" field_groups="bill" sr:suggest_transform="sum"/>
+ <field reporter:label="Billing Total (Damaged)" name="billing_damaged_amount" reporter:datatype="money" field_groups="bill" sr:suggest_transform="sum"/>
+ <field reporter:label="Billing Total (Deposit)" name="billing_deposit_amount" reporter:datatype="money" field_groups="bill" sr:suggest_transform="sum"/>
+ <field reporter:label="Billing Total (Rental)" name="billing_rental_amount" reporter:datatype="money" field_groups="bill" sr:suggest_transform="sum"/>
+ <field reporter:label="Payment Grand Total (Includes Voids)" name="payment_grand_total" reporter:datatype="money" field_groups="pay" sr:suggest_transform="sum"/>
+ <field reporter:label="Payment Total" name="payment_total" reporter:datatype="money" field_groups="pay,common" sr:suggest_transform="sum"/>
+ <field reporter:label="Voided Payments" name="payment_voided_count" reporter:datatype="text" field_groups="pay,common"/>
+ <field reporter:label="All Payment Types (Includes Voids)" name="all_payment_types" reporter:datatype="text" field_groups="pay" sr:suggest_operator="contains"/>
+ <field reporter:label="Payment Types" name="payment_types" reporter:datatype="text" field_groups="pay,common" sr:suggest_operator="contains"/>
+ <field reporter:label="Payment Amount Voided" name="payment_total_voided" reporter:datatype="money" field_groups="pay" sr:suggest_transform="sum"/>
+ <field reporter:label="Payment Total (Account Adjustment)" name="payment_account_adjustment_amount" reporter:datatype="money" field_groups="pay" sr:suggest_transform="sum"/>
+ <field reporter:label="Payment Total (Cash)" name="payment_cash_amount" reporter:datatype="money" field_groups="pay" sr:suggest_transform="sum"/>
+ <field reporter:label="Payment Total (Check)" name="payment_check_amount" reporter:datatype="money" field_groups="pay" sr:suggest_transform="sum"/>
+ <field reporter:label="Payment Total (Credit Card)" name="payment_credit_card_amount" reporter:datatype="money" field_groups="pay" sr:suggest_transform="sum"/>
+ <field reporter:label="Payment Total (Debit Card)" name="payment_debit_card_amount" reporter:datatype="money" field_groups="pay" sr:suggest_transform="sum"/>
+ <field reporter:label="Payment Total (Patron Credit)" name="payment_credit_amount" reporter:datatype="money" field_groups="pay" sr:suggest_transform="sum"/>
+ <field reporter:label="Payment Total (Forgiven)" name="payment_forgive_amount" reporter:datatype="money" field_groups="pay" sr:suggest_transform="sum"/>
+ <field reporter:label="Payment Total (Work)" name="payment_work_amount" reporter:datatype="money" field_groups="pay" sr:suggest_transform="sum"/>
+ <field reporter:label="Payment Total (Goods)" name="payment_goods_amount" reporter:datatype="money" field_groups="pay" sr:suggest_transform="sum"/>
+ <field reporter:label="Payment Total (Currency)" name="payment_currency_amount" reporter:datatype="money" field_groups="pay" sr:suggest_transform="sum"/>
+ <field reporter:label="Paymnt Total (Non-Currency)" name="payment_non_currency_amount" reporter:datatype="money" field_groups="pay" sr:suggest_transform="sum"/>
+ <field reporter:label="Transaction Start Date / Time" name="xact_start" reporter:datatype="timestamp" sr:suggest_filter="true" field_groups="dates,common"/>
+ <field reporter:label="Transaction Start Year" name="xact_start_year" reporter:datatype="int" field_groups="dates"/>
+ <field reporter:label="Transaction Start Year and Month" name="xact_start_year_mon" reporter:datatype="text" field_groups="dates,common"/>
+ <field reporter:label="Transaction Start Date" name="xact_start_date" reporter:datatype="text" field_groups="dates"/>
+ <field reporter:label="Transaction Finish Date / Time" name="xact_finish" reporter:datatype="timestamp" field_groups="dates"/>
+ <field reporter:label="Transaction Finish Year" name="xact_finish_year" reporter:datatype="int" field_groups="dates"/>
+ <field reporter:label="Transaction Finish Year and Month" name="xact_finish_year_mon" reporter:datatype="text" field_groups="dates"/>
+ <field reporter:label="Transaction Finish Date" name="xact_finish_date" reporter:datatype="text" field_groups="dates"/>
+ <field reporter:label="First Billing Date / Time" name="earliest_billing_ts" reporter:datatype="timestamp" sr:suggest_filter="true" field_groups="bill,dates,common"/>
+ <field reporter:label="First Billing Year" name="earliest_billing_year" reporter:datatype="int" field_groups="bill,dates"/>
+ <field reporter:label="First Billing Year and Month" name="earliest_billing_year_mon" reporter:datatype="text" field_groups="bill,dates"/>
+ <field reporter:label="First Billing Date" name="earliest_billing_date" reporter:datatype="text" field_groups="bill,dates"/>
+ <field reporter:label="Last Billing Date / Time" name="latest_billing_ts" reporter:datatype="timestamp" field_groups="bill,dates,common"/>
+ <field reporter:label="Last Billing Year" name="latest_billing_year" reporter:datatype="int" field_groups="bill,dates"/>
+ <field reporter:label="Last Billing Year and Month" name="latest_billing_year_mon" reporter:datatype="text" field_groups="bill,dates"/>
+ <field reporter:label="Last Billing Date" name="latest_billing_date" reporter:datatype="text" field_groups="bill,dates"/>
+ <field reporter:label="First Payment Date / Time" name="earliest_payment_ts" reporter:datatype="timestamp" sr:suggest_filter="true" field_groups="pay,dates,common"/>
+ <field reporter:label="First Payment Year" name="earliest_payment_year" reporter:datatype="int" field_groups="dates"/>
+ <field reporter:label="First Payment Year and Month" name="earliest_payment_year_mon" reporter:datatype="text" field_groups="pay,dates,common"/>
+ <field reporter:label="First Payment Date" name="earliest_payment_date" reporter:datatype="text" field_groups="pay,dates"/>
+ <field reporter:label="Last Payment Date / Time" name="latest_payment_ts" reporter:datatype="timestamp" field_groups="pay,dates,common"/>
+ <field reporter:label="Last Payment Year" name="latest_payment_year" reporter:datatype="int" field_groups="pay,dates"/>
+ <field reporter:label="Last Payment Year and Month" name="latest_payment_year_mon" reporter:datatype="text" field_groups="pay,dates,common"/>
+ <field reporter:label="Last Payment Date" name="latest_payment_date" reporter:datatype="text" field_groups="pay,dates"/>
+ <field reporter:label="Transaction Type" name="xact_type" reporter:datatype="text" sr:suggest_filter="true" field_groups="common"/>
+ <field reporter:label="Transaction Closed?" name="xact_closed" sr:suggest_filter="true" reporter:datatype="bool"/>
+ </fields>
+ </class>
+
<!-- ********************************************************************************************************************* -->
</IDL>
projects / evergreen-equinox.git / commitdiff