adding some more billing report views
authormiker <miker@dcc99617-32d9-48b4-a31d-7c20da2025e4>
Wed, 30 Apr 2008 04:50:12 +0000 (04:50 +0000)
committermiker <miker@dcc99617-32d9-48b4-a31d-7c20da2025e4>
Wed, 30 Apr 2008 04:50:12 +0000 (04:50 +0000)
git-svn-id: svn://svn.open-ils.org/ILS/trunk@9490 dcc99617-32d9-48b4-a31d-7c20da2025e4

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

index aa31d33..e63437e 100644 (file)
@@ -3647,6 +3647,105 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA
         </links>
     </class>
 
+    <class id="rmocbbol" controller="open-ils.reporter-store" oils_obj:fieldmapper="reporter::money::open_circ_balance_by_owning_lib" oils_persist:tablename="money.open_circ_balance_by_owning_lib" reporter:core="true" reporter:label="Open Circulation Billing by Owning Library">
+        <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="Circulation ID" name="id" oils_obj:array_position="3" oils_persist:virtual="false" reporter:datatype="link"/>
+            <field reporter:label="Owning Library" name="owning_lib" oils_obj:array_position="4" oils_persist:virtual="false" reporter:datatype="link"/>
+            <field reporter:label="Billing Type" name="billing_type" oils_obj:array_position="5" oils_persist:virtual="false" reporter:datatype="text"/>
+            <field reporter:label="Total Billed" name="billed" oils_obj:array_position="6" oils_persist:virtual="false" reporter:datatype="money"/>
+        </fields>
+        <links>
+            <link field="id" reltype="has_a" key="id" map="" class="circ"/>
+            <link field="owning_lib" reltype="has_a" key="id" map="" class="aou"/>
+        </links>
+    </class>
+
+    <class id="rmobbol" controller="open-ils.reporter-store" oils_obj:fieldmapper="reporter::money::open_balance_by_owning_lib" oils_persist:tablename="money.open_balance_by_owning_lib" reporter:core="true" reporter:label="Open Circulation Balance by Owning Library">
+        <fields oils_persist:primary="owning_lib">
+            <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="Owning Library" name="owning_lib" oils_obj:array_position="3" oils_persist:virtual="false" reporter:datatype="link"/>
+            <field reporter:label="Billing Types" name="billing_types" oils_obj:array_position="4" oils_persist:virtual="false" reporter:datatype="text"/>
+            <field reporter:label="Balance" name="balance" oils_obj:array_position="5" oils_persist:virtual="false" reporter:datatype="money"/>
+        </fields>
+        <links>
+            <link field="owning_lib" reltype="has_a" key="id" map="" class="aou"/>
+        </links>
+    </class>
+
+    <class id="rmocbbcol" controller="open-ils.reporter-store" oils_obj:fieldmapper="reporter::money::open_circ_balance_by_circ_and_owning_lib" oils_persist:tablename="money.open_circ_balance_by_circ_and_owning_lib" reporter:core="true" reporter:label="Open Circulation Billing by Circulating Library and Owning Library">
+        <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="Circulation ID" name="id" oils_obj:array_position="3" oils_persist:virtual="false" reporter:datatype="link"/>
+            <field reporter:label="Circulating Library" name="circ_lib" oils_obj:array_position="4" oils_persist:virtual="false" reporter:datatype="link"/>
+            <field reporter:label="Owning Library" name="owning_lib" oils_obj:array_position="5" oils_persist:virtual="false" reporter:datatype="link"/>
+            <field reporter:label="Billing Type" name="billing_type" oils_obj:array_position="6" oils_persist:virtual="false" reporter:datatype="text"/>
+            <field reporter:label="Total Billed" name="billed" oils_obj:array_position="7" oils_persist:virtual="false" reporter:datatype="money"/>
+        </fields>
+        <links>
+            <link field="id" reltype="has_a" key="id" map="" class="circ"/>
+            <link field="owning_lib" reltype="has_a" key="id" map="" class="aou"/>
+            <link field="circ_lib" reltype="has_a" key="id" map="" class="aou"/>
+        </links>
+    </class>
+
+    <class id="rmobbcol" controller="open-ils.reporter-store" oils_obj:fieldmapper="reporter::money::open_balance_by_circ_and_owning_lib" oils_persist:tablename="money.open_balance_by_circ_and_owning_lib" reporter:core="true" reporter:label="Open Circulation Balance by Circulating Library and Owning Library">
+        <fields oils_persist:primary="circ_lib">
+            <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="Circulating Library" name="circ_lib" oils_obj:array_position="3" oils_persist:virtual="false" reporter:datatype="link"/>
+            <field reporter:label="Owning Library" name="owning_lib" oils_obj:array_position="4" oils_persist:virtual="false" reporter:datatype="link"/>
+            <field reporter:label="Billing Types" name="billing_typse" oils_obj:array_position="5" oils_persist:virtual="false" reporter:datatype="text"/>
+            <field reporter:label="Balance" name="balance" oils_obj:array_position="6" oils_persist:virtual="false" reporter:datatype="money"/>
+        </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"/>
+        </links>
+    </class>
+
+    <class id="rmocbbhol" controller="open-ils.reporter-store" oils_obj:fieldmapper="reporter::money::open_circ_balance_by_usr_home_and_owning_lib" oils_persist:tablename="money.open_circ_balance_by_usr_home_and_owning_lib" reporter:core="true" reporter:label="Open Circulation Billing by User Home Library and Owning Library">
+        <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="Circulation ID" name="id" oils_obj:array_position="3" oils_persist:virtual="false" reporter:datatype="link"/>
+            <field reporter:label="User Home Library" name="home_ou" oils_obj:array_position="4" oils_persist:virtual="false" reporter:datatype="link"/>
+            <field reporter:label="Owning Library" name="owning_lib" oils_obj:array_position="5" oils_persist:virtual="false" reporter:datatype="link"/>
+            <field reporter:label="Billing Type" name="billing_type" oils_obj:array_position="6" oils_persist:virtual="false" reporter:datatype="text"/>
+            <field reporter:label="Total Billed" name="billed" oils_obj:array_position="7" oils_persist:virtual="false" reporter:datatype="money"/>
+        </fields>
+        <links>
+            <link field="id" reltype="has_a" key="id" map="" class="circ"/>
+            <link field="owning_lib" reltype="has_a" key="id" map="" class="aou"/>
+            <link field="home_ou" reltype="has_a" key="id" map="" class="aou"/>
+        </links>
+    </class>
+
+    <class id="rmobbhol" controller="open-ils.reporter-store" oils_obj:fieldmapper="reporter::money::open_balance_by_usr_home_and_owning_lib" oils_persist:tablename="money.open_balance_by_usr_home_and_owning_lib" reporter:core="true" reporter:label="Open Circulation Balance by User Home Library and Owning Library">
+        <fields oils_persist:primary="home_ou">
+            <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="User Home Library" name="home_ou" oils_obj:array_position="3" oils_persist:virtual="false" reporter:datatype="link"/>
+            <field reporter:label="Owning Library" name="owning_lib" oils_obj:array_position="4" oils_persist:virtual="false" reporter:datatype="link"/>
+            <field reporter:label="Billing Types" name="billing_typse" oils_obj:array_position="5" oils_persist:virtual="false" reporter:datatype="text"/>
+            <field reporter:label="Balance" name="balance" oils_obj:array_position="6" oils_persist:virtual="false" reporter:datatype="money"/>
+        </fields>
+        <links>
+            <link field="owning_lib" reltype="has_a" key="id" map="" class="aou"/>
+            <link field="home_ou" reltype="has_a" key="id" map="" class="aou"/>
+        </links>
+    </class>
+
+
        <!-- ********************************************************************************************************************* -->
 
 </IDL>
index c359d91..00c79e4 100644 (file)
@@ -224,6 +224,83 @@ SELECT  t.value as title,
     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);
 
+
+CREATE OR REPLACE VIEW money.open_circ_balance_by_owning_lib AS
+       SELECT  circ.id,
+               cn.owning_lib,
+               bill.billing_type,
+               SUM(bill.amount) AS billed
+         FROM  action.circulation circ
+               JOIN money.billing bill ON (circ.id = bill.xact) 
+               JOIN asset.copy cp ON (circ.target_copy = cp.id) 
+               JOIN asset.call_number cn ON (cn.id = cp.call_number) 
+         WHERE circ.xact_finish IS NULL
+               AND NOT bill.voided
+         GROUP BY 1,2,3
+         ORDER BY 1,2,3;
+
+CREATE OR REPLACE VIEW money.open_balance_by_owning_lib AS
+       SELECT  owning_lib,
+               ARRAY_TO_STRING(ARRAY_ACCUM(DISTINCT billing_type), ', ') AS billing_types,
+               SUM(billed) - SUM( COALESCE((SELECT SUM(amount) AS paid FROM money.payment WHERE NOT voided AND xact = x.id), 0::NUMERIC) ) AS balance
+         FROM  money.open_circ_balance_by_owning_lib x
+         GROUP BY 1;
+
+
+
+
+
+CREATE OR REPLACE VIEW money.open_circ_balance_by_circ_and_owning_lib AS
+       SELECT  circ.id,
+               circ.circ_lib,
+               cn.owning_lib,
+               bill.billing_type,
+               SUM(bill.amount) AS billed
+         FROM  action.circulation circ
+               JOIN money.billing bill ON (circ.id = bill.xact) 
+               JOIN asset.copy cp ON (circ.target_copy = cp.id) 
+               JOIN asset.call_number cn ON (cn.id = cp.call_number) 
+         WHERE circ.xact_finish IS NULL
+               AND NOT bill.voided
+         GROUP BY 1,2,3,4
+         ORDER BY 1,2,3,4;
+
+CREATE OR REPLACE VIEW money.open_balance_by_circ_and_owning_lib AS
+       SELECT  circ_lib,
+               owning_lib,
+               ARRAY_TO_STRING(ARRAY_ACCUM(DISTINCT billing_type), ', ') AS billing_types,
+               SUM(billed) - SUM( COALESCE((SELECT SUM(amount) AS paid FROM money.payment WHERE NOT voided AND xact = x.id), 0::NUMERIC) ) AS balance
+         FROM  money.open_circ_balance_by_circ_and_owning_lib x
+         GROUP BY 1,2;
+
+
+
+
+
+CREATE OR REPLACE VIEW money.open_circ_balance_by_usr_home_and_owning_lib AS
+       SELECT  circ.id,
+               usr.home_ou,
+               cn.owning_lib,
+               bill.billing_type,
+               SUM(bill.amount) AS billed
+         FROM  action.circulation circ
+               JOIN money.billing bill ON (circ.id = bill.xact) 
+               JOIN asset.copy cp ON (circ.target_copy = cp.id) 
+               JOIN asset.call_number cn ON (cn.id = cp.call_number) 
+               JOIN actor.usr usr ON (circ.usr = usr.id) 
+         WHERE circ.xact_finish IS NULL
+               AND NOT bill.voided
+         GROUP BY 1,2,3,4
+         ORDER BY 1,2,3,4;
+
+CREATE OR REPLACE VIEW money.open_balance_by_usr_home_and_owning_lib AS
+       SELECT  home_ou,
+               owning_lib,
+               ARRAY_TO_STRING(ARRAY_ACCUM(DISTINCT billing_type), ', ') AS billing_types,
+               SUM(billed) - SUM( COALESCE((SELECT SUM(amount) AS paid FROM money.payment WHERE NOT voided AND xact = x.id), 0::NUMERIC) ) AS balance
+         FROM  money.open_circ_balance_by_usr_home_and_owning_lib x
+         GROUP BY 1,2;
+
 COMMIT;