Changeset 9489

Show
Ignore:
Timestamp:
04/30/08 00:47:34 (2 months ago)
Author:
miker
Message:

adding some more billing report views

Location:
branches/rel_1_2/Open-ILS
Files:
2 modified

Legend:

Unmodified
Added
Removed

  • branches/rel_1_2/Open-ILS/examples/fm_IDL.xml

    r9457 r9489  
    27522752    </class> 
    27532753 
     2754    <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"> 
     2755        <fields oils_persist:primary="id"> 
     2756            <field name="isnew" oils_obj:array_position="0" oils_persist:virtual="true" /> 
     2757            <field name="ischanged" oils_obj:array_position="1" oils_persist:virtual="true" /> 
     2758            <field name="isdeleted" oils_obj:array_position="2" oils_persist:virtual="true" /> 
     2759            <field reporter:label="Circulation ID" name="id" oils_obj:array_position="3" oils_persist:virtual="false" reporter:datatype="link"/> 
     2760            <field reporter:label="Owning Library" name="owning_lib" oils_obj:array_position="4" oils_persist:virtual="false" reporter:datatype="link"/> 
     2761            <field reporter:label="Billing Type" name="billing_type" oils_obj:array_position="5" oils_persist:virtual="false" reporter:datatype="text"/> 
     2762            <field reporter:label="Total Billed" name="billed" oils_obj:array_position="6" oils_persist:virtual="false" reporter:datatype="money"/> 
     2763        </fields> 
     2764        <links> 
     2765            <link field="id" reltype="has_a" key="id" map="" class="circ"/> 
     2766            <link field="owning_lib" reltype="has_a" key="id" map="" class="aou"/> 
     2767        </links> 
     2768    </class> 
     2769 
     2770    <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"> 
     2771        <fields oils_persist:primary="owning_lib"> 
     2772            <field name="isnew" oils_obj:array_position="0" oils_persist:virtual="true" /> 
     2773            <field name="ischanged" oils_obj:array_position="1" oils_persist:virtual="true" /> 
     2774            <field name="isdeleted" oils_obj:array_position="2" oils_persist:virtual="true" /> 
     2775            <field reporter:label="Owning Library" name="owning_lib" oils_obj:array_position="3" oils_persist:virtual="false" reporter:datatype="link"/> 
     2776            <field reporter:label="Billing Types" name="billing_types" oils_obj:array_position="4" oils_persist:virtual="false" reporter:datatype="text"/> 
     2777            <field reporter:label="Balance" name="balance" oils_obj:array_position="5" oils_persist:virtual="false" reporter:datatype="money"/> 
     2778        </fields> 
     2779        <links> 
     2780            <link field="owning_lib" reltype="has_a" key="id" map="" class="aou"/> 
     2781        </links> 
     2782    </class> 
     2783 
     2784    <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"> 
     2785        <fields oils_persist:primary="id"> 
     2786            <field name="isnew" oils_obj:array_position="0" oils_persist:virtual="true" /> 
     2787            <field name="ischanged" oils_obj:array_position="1" oils_persist:virtual="true" /> 
     2788            <field name="isdeleted" oils_obj:array_position="2" oils_persist:virtual="true" /> 
     2789            <field reporter:label="Circulation ID" name="id" oils_obj:array_position="3" oils_persist:virtual="false" reporter:datatype="link"/> 
     2790            <field reporter:label="Circulating Library" name="circ_lib" oils_obj:array_position="4" oils_persist:virtual="false" reporter:datatype="link"/> 
     2791            <field reporter:label="Owning Library" name="owning_lib" oils_obj:array_position="5" oils_persist:virtual="false" reporter:datatype="link"/> 
     2792            <field reporter:label="Billing Type" name="billing_type" oils_obj:array_position="6" oils_persist:virtual="false" reporter:datatype="text"/> 
     2793            <field reporter:label="Total Billed" name="billed" oils_obj:array_position="7" oils_persist:virtual="false" reporter:datatype="money"/> 
     2794        </fields> 
     2795        <links> 
     2796            <link field="id" reltype="has_a" key="id" map="" class="circ"/> 
     2797            <link field="owning_lib" reltype="has_a" key="id" map="" class="aou"/> 
     2798            <link field="circ_lib" reltype="has_a" key="id" map="" class="aou"/> 
     2799        </links> 
     2800    </class> 
     2801 
     2802    <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"> 
     2803        <fields oils_persist:primary="circ_lib"> 
     2804            <field name="isnew" oils_obj:array_position="0" oils_persist:virtual="true" /> 
     2805            <field name="ischanged" oils_obj:array_position="1" oils_persist:virtual="true" /> 
     2806            <field name="isdeleted" oils_obj:array_position="2" oils_persist:virtual="true" /> 
     2807            <field reporter:label="Circulating Library" name="circ_lib" oils_obj:array_position="3" oils_persist:virtual="false" reporter:datatype="link"/> 
     2808            <field reporter:label="Owning Library" name="owning_lib" oils_obj:array_position="4" oils_persist:virtual="false" reporter:datatype="link"/> 
     2809            <field reporter:label="Billing Types" name="billing_typse" oils_obj:array_position="5" oils_persist:virtual="false" reporter:datatype="text"/> 
     2810            <field reporter:label="Balance" name="balance" oils_obj:array_position="6" oils_persist:virtual="false" reporter:datatype="money"/> 
     2811        </fields> 
     2812        <links> 
     2813            <link field="owning_lib" reltype="has_a" key="id" map="" class="aou"/> 
     2814            <link field="circ_lib" reltype="has_a" key="id" map="" class="aou"/> 
     2815        </links> 
     2816    </class> 
     2817 
     2818    <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"> 
     2819        <fields oils_persist:primary="id"> 
     2820            <field name="isnew" oils_obj:array_position="0" oils_persist:virtual="true" /> 
     2821            <field name="ischanged" oils_obj:array_position="1" oils_persist:virtual="true" /> 
     2822            <field name="isdeleted" oils_obj:array_position="2" oils_persist:virtual="true" /> 
     2823            <field reporter:label="Circulation ID" name="id" oils_obj:array_position="3" oils_persist:virtual="false" reporter:datatype="link"/> 
     2824            <field reporter:label="User Home Library" name="home_ou" oils_obj:array_position="4" oils_persist:virtual="false" reporter:datatype="link"/> 
     2825            <field reporter:label="Owning Library" name="owning_lib" oils_obj:array_position="5" oils_persist:virtual="false" reporter:datatype="link"/> 
     2826            <field reporter:label="Billing Type" name="billing_type" oils_obj:array_position="6" oils_persist:virtual="false" reporter:datatype="text"/> 
     2827            <field reporter:label="Total Billed" name="billed" oils_obj:array_position="7" oils_persist:virtual="false" reporter:datatype="money"/> 
     2828        </fields> 
     2829        <links> 
     2830            <link field="id" reltype="has_a" key="id" map="" class="circ"/> 
     2831            <link field="owning_lib" reltype="has_a" key="id" map="" class="aou"/> 
     2832            <link field="home_ou" reltype="has_a" key="id" map="" class="aou"/> 
     2833        </links> 
     2834    </class> 
     2835 
     2836    <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"> 
     2837        <fields oils_persist:primary="home_ou"> 
     2838            <field name="isnew" oils_obj:array_position="0" oils_persist:virtual="true" /> 
     2839            <field name="ischanged" oils_obj:array_position="1" oils_persist:virtual="true" /> 
     2840            <field name="isdeleted" oils_obj:array_position="2" oils_persist:virtual="true" /> 
     2841            <field reporter:label="User Home Library" name="home_ou" oils_obj:array_position="3" oils_persist:virtual="false" reporter:datatype="link"/> 
     2842            <field reporter:label="Owning Library" name="owning_lib" oils_obj:array_position="4" oils_persist:virtual="false" reporter:datatype="link"/> 
     2843            <field reporter:label="Billing Types" name="billing_typse" oils_obj:array_position="5" oils_persist:virtual="false" reporter:datatype="text"/> 
     2844            <field reporter:label="Balance" name="balance" oils_obj:array_position="6" oils_persist:virtual="false" reporter:datatype="money"/> 
     2845        </fields> 
     2846        <links> 
     2847            <link field="owning_lib" reltype="has_a" key="id" map="" class="aou"/> 
     2848            <link field="home_ou" reltype="has_a" key="id" map="" class="aou"/> 
     2849        </links> 
     2850    </class> 
     2851 
     2852 
    27542853        <!-- ********************************************************************************************************************* --> 
    27552854 

  • branches/rel_1_2/Open-ILS/src/sql/Pg/example.reporter-extension.sql

    r8905 r9489  
    225225    LEFT JOIN asset.stat_cat_entry sce2 ON (sce2.id = sc2.stat_cat_entry); 
    226226 
     227 
     228CREATE OR REPLACE VIEW money.open_circ_balance_by_owning_lib AS 
     229        SELECT  circ.id, 
     230                cn.owning_lib, 
     231                bill.billing_type, 
     232                SUM(bill.amount) AS billed 
     233          FROM  action.circulation circ 
     234                JOIN money.billing bill ON (circ.id = bill.xact)  
     235                JOIN asset.copy cp ON (circ.target_copy = cp.id)  
     236                JOIN asset.call_number cn ON (cn.id = cp.call_number)  
     237          WHERE circ.xact_finish IS NULL 
     238                AND NOT bill.voided 
     239          GROUP BY 1,2,3 
     240          ORDER BY 1,2,3; 
     241 
     242CREATE OR REPLACE VIEW money.open_balance_by_owning_lib AS 
     243        SELECT  owning_lib, 
     244                ARRAY_TO_STRING(ARRAY_ACCUM(DISTINCT billing_type), ', ') AS billing_types, 
     245                SUM(billed) - SUM( COALESCE((SELECT SUM(amount) AS paid FROM money.payment WHERE NOT voided AND xact = x.id), 0::NUMERIC) ) AS balance 
     246          FROM  money.open_circ_balance_by_owning_lib x 
     247          GROUP BY 1; 
     248 
     249 
     250 
     251 
     252 
     253CREATE OR REPLACE VIEW money.open_circ_balance_by_circ_and_owning_lib AS 
     254        SELECT  circ.id, 
     255                circ.circ_lib, 
     256                cn.owning_lib, 
     257                bill.billing_type, 
     258                SUM(bill.amount) AS billed 
     259          FROM  action.circulation circ 
     260                JOIN money.billing bill ON (circ.id = bill.xact)  
     261                JOIN asset.copy cp ON (circ.target_copy = cp.id)  
     262                JOIN asset.call_number cn ON (cn.id = cp.call_number)  
     263          WHERE circ.xact_finish IS NULL 
     264                AND NOT bill.voided 
     265          GROUP BY 1,2,3,4 
     266          ORDER BY 1,2,3,4; 
     267 
     268CREATE OR REPLACE VIEW money.open_balance_by_circ_and_owning_lib AS 
     269        SELECT  circ_lib, 
     270                owning_lib, 
     271                ARRAY_TO_STRING(ARRAY_ACCUM(DISTINCT billing_type), ', ') AS billing_types, 
     272                SUM(billed) - SUM( COALESCE((SELECT SUM(amount) AS paid FROM money.payment WHERE NOT voided AND xact = x.id), 0::NUMERIC) ) AS balance 
     273          FROM  money.open_circ_balance_by_circ_and_owning_lib x 
     274          GROUP BY 1,2; 
     275 
     276 
     277 
     278 
     279 
     280CREATE OR REPLACE VIEW money.open_circ_balance_by_usr_home_and_owning_lib AS 
     281        SELECT  circ.id, 
     282                usr.home_ou, 
     283                cn.owning_lib, 
     284                bill.billing_type, 
     285                SUM(bill.amount) AS billed 
     286          FROM  action.circulation circ 
     287                JOIN money.billing bill ON (circ.id = bill.xact)  
     288                JOIN asset.copy cp ON (circ.target_copy = cp.id)  
     289                JOIN asset.call_number cn ON (cn.id = cp.call_number)  
     290                JOIN actor.usr usr ON (circ.usr = usr.id)  
     291          WHERE circ.xact_finish IS NULL 
     292                AND NOT bill.voided 
     293          GROUP BY 1,2,3,4 
     294          ORDER BY 1,2,3,4; 
     295 
     296CREATE OR REPLACE VIEW money.open_balance_by_usr_home_and_owning_lib AS 
     297        SELECT  home_ou, 
     298                owning_lib, 
     299                ARRAY_TO_STRING(ARRAY_ACCUM(DISTINCT billing_type), ', ') AS billing_types, 
     300                SUM(billed) - SUM( COALESCE((SELECT SUM(amount) AS paid FROM money.payment WHERE NOT voided AND xact = x.id), 0::NUMERIC) ) AS balance 
     301          FROM  money.open_circ_balance_by_usr_home_and_owning_lib x 
     302          GROUP BY 1,2; 
     303 
    227304COMMIT; 
    228305