Changeset 9490

Show
Ignore:
Timestamp:
04/30/08 00:50:12 (2 weeks ago)
Author:
miker
Message:

adding some more billing report views

Location:
trunk/Open-ILS
Files:
2 modified

Legend:

Unmodified
Added
Removed

  • trunk/Open-ILS/examples/fm_IDL.xml

    r9458 r9490  
    36483648    </class> 
    36493649 
     3650    <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"> 
     3651        <fields oils_persist:primary="id"> 
     3652            <field name="isnew" oils_obj:array_position="0" oils_persist:virtual="true" /> 
     3653            <field name="ischanged" oils_obj:array_position="1" oils_persist:virtual="true" /> 
     3654            <field name="isdeleted" oils_obj:array_position="2" oils_persist:virtual="true" /> 
     3655            <field reporter:label="Circulation ID" name="id" oils_obj:array_position="3" oils_persist:virtual="false" reporter:datatype="link"/> 
     3656            <field reporter:label="Owning Library" name="owning_lib" oils_obj:array_position="4" oils_persist:virtual="false" reporter:datatype="link"/> 
     3657            <field reporter:label="Billing Type" name="billing_type" oils_obj:array_position="5" oils_persist:virtual="false" reporter:datatype="text"/> 
     3658            <field reporter:label="Total Billed" name="billed" oils_obj:array_position="6" oils_persist:virtual="false" reporter:datatype="money"/> 
     3659        </fields> 
     3660        <links> 
     3661            <link field="id" reltype="has_a" key="id" map="" class="circ"/> 
     3662            <link field="owning_lib" reltype="has_a" key="id" map="" class="aou"/> 
     3663        </links> 
     3664    </class> 
     3665 
     3666    <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"> 
     3667        <fields oils_persist:primary="owning_lib"> 
     3668            <field name="isnew" oils_obj:array_position="0" oils_persist:virtual="true" /> 
     3669            <field name="ischanged" oils_obj:array_position="1" oils_persist:virtual="true" /> 
     3670            <field name="isdeleted" oils_obj:array_position="2" oils_persist:virtual="true" /> 
     3671            <field reporter:label="Owning Library" name="owning_lib" oils_obj:array_position="3" oils_persist:virtual="false" reporter:datatype="link"/> 
     3672            <field reporter:label="Billing Types" name="billing_types" oils_obj:array_position="4" oils_persist:virtual="false" reporter:datatype="text"/> 
     3673            <field reporter:label="Balance" name="balance" oils_obj:array_position="5" oils_persist:virtual="false" reporter:datatype="money"/> 
     3674        </fields> 
     3675        <links> 
     3676            <link field="owning_lib" reltype="has_a" key="id" map="" class="aou"/> 
     3677        </links> 
     3678    </class> 
     3679 
     3680    <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"> 
     3681        <fields oils_persist:primary="id"> 
     3682            <field name="isnew" oils_obj:array_position="0" oils_persist:virtual="true" /> 
     3683            <field name="ischanged" oils_obj:array_position="1" oils_persist:virtual="true" /> 
     3684            <field name="isdeleted" oils_obj:array_position="2" oils_persist:virtual="true" /> 
     3685            <field reporter:label="Circulation ID" name="id" oils_obj:array_position="3" oils_persist:virtual="false" reporter:datatype="link"/> 
     3686            <field reporter:label="Circulating Library" name="circ_lib" oils_obj:array_position="4" oils_persist:virtual="false" reporter:datatype="link"/> 
     3687            <field reporter:label="Owning Library" name="owning_lib" oils_obj:array_position="5" oils_persist:virtual="false" reporter:datatype="link"/> 
     3688            <field reporter:label="Billing Type" name="billing_type" oils_obj:array_position="6" oils_persist:virtual="false" reporter:datatype="text"/> 
     3689            <field reporter:label="Total Billed" name="billed" oils_obj:array_position="7" oils_persist:virtual="false" reporter:datatype="money"/> 
     3690        </fields> 
     3691        <links> 
     3692            <link field="id" reltype="has_a" key="id" map="" class="circ"/> 
     3693            <link field="owning_lib" reltype="has_a" key="id" map="" class="aou"/> 
     3694            <link field="circ_lib" reltype="has_a" key="id" map="" class="aou"/> 
     3695        </links> 
     3696    </class> 
     3697 
     3698    <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"> 
     3699        <fields oils_persist:primary="circ_lib"> 
     3700            <field name="isnew" oils_obj:array_position="0" oils_persist:virtual="true" /> 
     3701            <field name="ischanged" oils_obj:array_position="1" oils_persist:virtual="true" /> 
     3702            <field name="isdeleted" oils_obj:array_position="2" oils_persist:virtual="true" /> 
     3703            <field reporter:label="Circulating Library" name="circ_lib" oils_obj:array_position="3" oils_persist:virtual="false" reporter:datatype="link"/> 
     3704            <field reporter:label="Owning Library" name="owning_lib" oils_obj:array_position="4" oils_persist:virtual="false" reporter:datatype="link"/> 
     3705            <field reporter:label="Billing Types" name="billing_typse" oils_obj:array_position="5" oils_persist:virtual="false" reporter:datatype="text"/> 
     3706            <field reporter:label="Balance" name="balance" oils_obj:array_position="6" oils_persist:virtual="false" reporter:datatype="money"/> 
     3707        </fields> 
     3708        <links> 
     3709            <link field="owning_lib" reltype="has_a" key="id" map="" class="aou"/> 
     3710            <link field="circ_lib" reltype="has_a" key="id" map="" class="aou"/> 
     3711        </links> 
     3712    </class> 
     3713 
     3714    <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"> 
     3715        <fields oils_persist:primary="id"> 
     3716            <field name="isnew" oils_obj:array_position="0" oils_persist:virtual="true" /> 
     3717            <field name="ischanged" oils_obj:array_position="1" oils_persist:virtual="true" /> 
     3718            <field name="isdeleted" oils_obj:array_position="2" oils_persist:virtual="true" /> 
     3719            <field reporter:label="Circulation ID" name="id" oils_obj:array_position="3" oils_persist:virtual="false" reporter:datatype="link"/> 
     3720            <field reporter:label="User Home Library" name="home_ou" oils_obj:array_position="4" oils_persist:virtual="false" reporter:datatype="link"/> 
     3721            <field reporter:label="Owning Library" name="owning_lib" oils_obj:array_position="5" oils_persist:virtual="false" reporter:datatype="link"/> 
     3722            <field reporter:label="Billing Type" name="billing_type" oils_obj:array_position="6" oils_persist:virtual="false" reporter:datatype="text"/> 
     3723            <field reporter:label="Total Billed" name="billed" oils_obj:array_position="7" oils_persist:virtual="false" reporter:datatype="money"/> 
     3724        </fields> 
     3725        <links> 
     3726            <link field="id" reltype="has_a" key="id" map="" class="circ"/> 
     3727            <link field="owning_lib" reltype="has_a" key="id" map="" class="aou"/> 
     3728            <link field="home_ou" reltype="has_a" key="id" map="" class="aou"/> 
     3729        </links> 
     3730    </class> 
     3731 
     3732    <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"> 
     3733        <fields oils_persist:primary="home_ou"> 
     3734            <field name="isnew" oils_obj:array_position="0" oils_persist:virtual="true" /> 
     3735            <field name="ischanged" oils_obj:array_position="1" oils_persist:virtual="true" /> 
     3736            <field name="isdeleted" oils_obj:array_position="2" oils_persist:virtual="true" /> 
     3737            <field reporter:label="User Home Library" name="home_ou" oils_obj:array_position="3" oils_persist:virtual="false" reporter:datatype="link"/> 
     3738            <field reporter:label="Owning Library" name="owning_lib" oils_obj:array_position="4" oils_persist:virtual="false" reporter:datatype="link"/> 
     3739            <field reporter:label="Billing Types" name="billing_typse" oils_obj:array_position="5" oils_persist:virtual="false" reporter:datatype="text"/> 
     3740            <field reporter:label="Balance" name="balance" oils_obj:array_position="6" oils_persist:virtual="false" reporter:datatype="money"/> 
     3741        </fields> 
     3742        <links> 
     3743            <link field="owning_lib" reltype="has_a" key="id" map="" class="aou"/> 
     3744            <link field="home_ou" reltype="has_a" key="id" map="" class="aou"/> 
     3745        </links> 
     3746    </class> 
     3747 
     3748 
    36503749        <!-- ********************************************************************************************************************* --> 
    36513750 

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

    r8904 r9490  
    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