adding circ_item_list example reporter view; installing staged-search stuff
authormiker <miker@dcc99617-32d9-48b4-a31d-7c20da2025e4>
Fri, 7 Mar 2008 18:10:23 +0000 (18:10 +0000)
committermiker <miker@dcc99617-32d9-48b4-a31d-7c20da2025e4>
Fri, 7 Mar 2008 18:10:23 +0000 (18:10 +0000)
git-svn-id: svn://svn.open-ils.org/ILS/trunk@8904 dcc99617-32d9-48b4-a31d-7c20da2025e4

Open-ILS/examples/fm_IDL.xml
Open-ILS/src/sql/Pg/build-db.sh
Open-ILS/src/sql/Pg/example.reporter-extension.sql

index b26d9ce..e690e45 100644 (file)
                </links>
        </class>
 
+    <class id="rocit" controller="open-ils.reporter-store" oils_obj:fieldmapper="reporter::classic_item_list" oils_persist:tablename="reporter.classic_item_list" reporter:core="true" reporter:label="Classic Item List">
+        <fields oils_persist:primary="id">
+            <field name="isnew" oils_obj:array_position="0" oils_persist:virtual="true" />
+            <field name="ischanged" oils_obj:array_position="1" oils_persist:virtual="true" />
+            <field name="isdeleted" oils_obj:array_position="2" oils_persist:virtual="true" />
+            <field reporter:label="Title" name="title" oils_obj:array_position="3" oils_persist:virtual="false" reporter:datatype="text"/>
+            <field reporter:label="Author" name="author" oils_obj:array_position="4" oils_persist:virtual="false" reporter:datatype="text"/>
+            <field reporter:label="Pubdate" name="pubdate" oils_obj:array_position="5" oils_persist:virtual="false" reporter:datatype="text"/>
+            <field reporter:label="Copy ID" name="id" oils_obj:array_position="6" oils_persist:virtual="false" reporter:datatype="id"/>
+            <field reporter:label="Price" name="price" oils_obj:array_position="7" oils_persist:virtual="false" reporter:datatype="money"/>
+            <field reporter:label="Barcode" name="barcode" oils_obj:array_position="8" oils_persist:virtual="false" reporter:datatype="text"/>
+            <field reporter:label="Callnumber Label" name="call_number_label" oils_obj:array_position="9" oils_persist:virtual="false" reporter:datatype="text"/>
+            <field reporter:label="Dewy Tens" name="dewey_block_tens" oils_obj:array_position="10" oils_persist:virtual="false" reporter:datatype="text"/>
+            <field reporter:label="Dewy Hundreds" name="dewey_block_hundreds" oils_obj:array_position="11" oils_persist:virtual="false" reporter:datatype="text"/>
+            <field reporter:label="Use Count" name="use_count" oils_obj:array_position="12" oils_persist:virtual="false" reporter:datatype="int"/>
+            <field reporter:label="Circ Modifier" name="circ_modifier" oils_obj:array_position="13" oils_persist:virtual="false" reporter:datatype="text"/>
+            <field reporter:label="Shelving Location Name" name="shelving_location" oils_obj:array_position="14" oils_persist:virtual="false" reporter:datatype="text"/>
+            <field reporter:label="Legacy Stat Cat 1" name="stat_cat_1" oils_obj:array_position="15" oils_persist:virtual="false" reporter:datatype="text"/>
+            <field reporter:label="Legacy Stat Cat 2" name="stat_cat_2" oils_obj:array_position="16" oils_persist:virtual="false" reporter:datatype="text"/>
+            <field reporter:label="Legacy Stat Cat 1 Value" name="stat_cat_1_value" oils_obj:array_position="17" oils_persist:virtual="false" reporter:datatype="text"/>
+            <field reporter:label="Legacy Stat Cat 2 Value" name="stat_cat_2_value" oils_obj:array_position="18" oils_persist:virtual="false" reporter:datatype="text"/>
+            <field reporter:label="Edit Date" name="edit_date" oils_obj:array_position="19" oils_persist:virtual="false" reporter:datatype="timestamp"/>
+            <field reporter:label="Create Date" name="create_date" oils_obj:array_position="20" oils_persist:virtual="false" reporter:datatype="timestamp"/>
+            <field reporter:label="Owning Lib Name" name="owning_lib_name" oils_obj:array_position="21" oils_persist:virtual="false" reporter:datatype="text"/>
+            <field reporter:label="Circ Lib Name" name="circ_lib_name" oils_obj:array_position="22" oils_persist:virtual="false" reporter:datatype="text"/>
+            <field reporter:label="Owning Lib" name="owning_lib" oils_obj:array_position="23" oils_persist:virtual="false" reporter:datatype="link"/>
+            <field reporter:label="Circ Lib" name="circ_lib" oils_obj:array_position="24" oils_persist:virtual="false" reporter:datatype="link"/>
+            <field reporter:label="Creator" name="creator" oils_obj:array_position="25" oils_persist:virtual="false" reporter:datatype="link"/>
+            <field reporter:label="Age Protection" name="age_protect" oils_obj:array_position="26" oils_persist:virtual="false" reporter:datatype="link"/>
+            <field reporter:label="OPAC Visible" name="opac_visible" oils_obj:array_position="27" oils_persist:virtual="false" reporter:datatype="bool"/>
+            <field reporter:label="Reference" name="ref" oils_obj:array_position="28" oils_persist:virtual="false" reporter:datatype="bool"/>
+            <field reporter:label="Deposit Amount" name="deposit_amount" oils_obj:array_position="29" oils_persist:virtual="false" reporter:datatype="text"/>
+            <field reporter:label="Deleted" name="deleted" oils_obj:array_position="30" oils_persist:virtual="false" reporter:datatype="bool"/>
+            <field reporter:label="TCN" name="tcn_value" oils_obj:array_position="31" oils_persist:virtual="false" reporter:datatype="text"/>
+            <field reporter:label="Status" name="status" oils_obj:array_position="32" oils_persist:virtual="false" reporter:datatype="link"/>
+            <field reporter:label="Stop Fines Reason" name="stop_fines" oils_obj:array_position="33" oils_persist:virtual="false" reporter:datatype="text"/>
+            <field reporter:label="Due Date" name="due_date" oils_obj:array_position="34" oils_persist:virtual="false" reporter:datatype="timestamp"/>
+            <field reporter:label="Patron Barcode" name="patron_barcode" oils_obj:array_position="35" oils_persist:virtual="false" reporter:datatype="text"/>
+            <field reporter:label="Patron Name" name="patron_name" oils_obj:array_position="36" oils_persist:virtual="false" reporter:datatype="text"/>
+        </fields>
+        <links>
+            <link field="owning_lib" reltype="has_a" key="id" map="" class="aou"/>
+            <link field="circ_lib" reltype="has_a" key="id" map="" class="aou"/>
+            <link field="creator" reltype="has_a" key="id" map="" class="au"/>
+            <link field="age_protect" reltype="has_a" key="id" map="" class="crahp"/>
+            <link field="status" reltype="has_a" key="id" map="" class="ccs"/>
+        </links>
+    </class>
 
        <!-- ********************************************************************************************************************* -->
 
index 3ff444c..29dce75 100755 (executable)
@@ -21,6 +21,8 @@ PGPASSWORD=$5 PGUSER=$4 PGHOST=$1 PGPORT=$2 PGDATABASE=$3 psql -f 090.schema.act
 PGPASSWORD=$5 PGUSER=$4 PGHOST=$1 PGPORT=$2 PGDATABASE=$3 psql -f 100.circ_matrix.sql
 PGPASSWORD=$5 PGUSER=$4 PGHOST=$1 PGPORT=$2 PGDATABASE=$3 psql -f 110.hold_matrix.sql
 
+PGPASSWORD=$5 PGUSER=$4 PGHOST=$1 PGPORT=$2 PGDATABASE=$3 psql -f 300.schema.staged_search.sql
+
 PGPASSWORD=$5 PGUSER=$4 PGHOST=$1 PGPORT=$2 PGDATABASE=$3 psql -f 500.view.cross-schema.sql
 
 PGPASSWORD=$5 PGUSER=$4 PGHOST=$1 PGPORT=$2 PGDATABASE=$3 psql -f 800.fkeys.sql
index 88d310e..c359d91 100644 (file)
@@ -154,6 +154,75 @@ SELECT     x.id AS id,
        JOIN reporter.demographic dem ON (dem.id = u.id)
        JOIN actor.usr_address paddr ON (paddr.id = u.billing_address);
 
+CREATE OR REPLACE VIEW reporter.classic_item_list AS
+SELECT  t.value as title,
+    a.value as author,
+    p.value as pubdate,
+    cp.id,
+    cp.price,
+    cp.barcode,
+    cn.label as call_number_label,
+    CASE
+        WHEN call_number_dewey(cn.label) ~  E'^[0-9.]+$'
+            THEN
+                btrim(
+                    to_char(
+                        10 * floor((call_number_dewey(cn.label)::float) / 10), '000'
+                    )
+                )
+        ELSE NULL
+    END AS dewey_block_tens,
+    CASE
+        WHEN call_number_dewey(cn.label) ~  E'^[0-9.]+$'
+            THEN
+                btrim(
+                    to_char(
+                        100 * floor((call_number_dewey(cn.label)::float) / 100), '000'
+                    )
+                )
+        ELSE NULL
+    END AS dewey_block_hundreds,
+    (SELECT COUNT(*) FROM action.circulation WHERE target_copy = cp.id) as use_count,
+    cp.circ_modifier,
+    sl.name AS shelving_location,
+    sc1.stat_cat_entry AS stat_cat_1,
+    sc2.stat_cat_entry AS stat_cat_2,
+    sce1.value AS stat_cat_1_value,
+    sce2.value AS stat_cat_2_value,
+    cp.edit_date,
+    cp.create_date,
+    ol.shortname AS owning_lib_name,
+    cn.owning_lib,
+    cl.shortname AS circ_lib_name,
+    cl.id AS circ_lib,
+    cp.creator,
+    cp.age_protect,
+    cp.opac_visible,
+    cp.ref,
+    cp.deposit_amount,
+    cp.deleted,
+    b.tcn_value,
+    cp.status,
+    circ.stop_fines,
+    circ.due_date,
+    circ_card.barcode as patron_barcode,
+    circ_u.first_given_name || ' ' || circ_u.family_name as patron_name
+  FROM  asset.copy cp
+    JOIN asset.copy_location sl ON (cp.location = sl.id)
+    JOIN asset.call_number cn ON (cp.call_number = cn.id)
+    JOIN biblio.record_entry b ON (cn.record = b.id)
+    JOIN actor.org_unit ol ON (cn.owning_lib = ol.id)
+    JOIN actor.org_unit cl ON (cp.circ_lib = cl.id)
+    LEFT JOIN metabib.full_rec t ON (cn.record = t.record AND t.tag = '245' and t.subfield = 'a')
+    LEFT JOIN metabib.full_rec a ON (cn.record = a.record AND a.tag = '100' and a.subfield = 'a')
+    LEFT JOIN metabib.full_rec p ON (cn.record = p.record AND p.tag = '260' and p.subfield = 'c')
+    LEFT JOIN action.circulation circ ON (circ.target_copy = cp .id AND circ.checkin_time IS NULL)
+    LEFT JOIN actor.usr circ_u ON (circ_u.id = circ.usr)
+    LEFT JOIN actor.card circ_card ON (circ_u.id = circ_card.usr)
+    LEFT JOIN asset.stat_cat_entry_copy_map sc1 ON (sc1.owning_copy = cp.id AND sc1.stat_cat = 1)
+    LEFT JOIN asset.stat_cat_entry sce1 ON (sce1.id = sc1.stat_cat_entry)
+    LEFT JOIN asset.stat_cat_entry_copy_map sc2 ON (sc2.owning_copy = cp.id AND sc2.stat_cat = 2)
+    LEFT JOIN asset.stat_cat_entry sce2 ON (sce2.id = sc2.stat_cat_entry);
 
 COMMIT;