Index: openacs-4/packages/invoices/lib/invoice-items-list.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/invoices/lib/invoice-items-list.xql,v diff -u -r1.3 -r1.4 --- openacs-4/packages/invoices/lib/invoice-items-list.xql 4 Nov 2005 14:40:06 -0000 1.3 +++ openacs-4/packages/invoices/lib/invoice-items-list.xql 7 Nov 2005 23:10:51 -0000 1.4 @@ -7,25 +7,31 @@ ii.iv_item_id, ii.title as item_title, iv.title as invoice_title, - ii.price_per_unit, ii.rebate, - ii.item_units, - com.category_id, + ii.amount_total as final_amount, + com.category_id as cat_id, iv.organization_id, to_char(ii.creation_date,'yy-mm-dd') as creation_date, to_char(ii.creation_date,'mm') as month, ii.offer_item_id, + iv.organization_id, + org.name as org_name, + ob.title as cat_name, i.item_id from iv_invoice_itemsx ii, iv_invoicesx iv, category_object_map com, - cr_items i + cr_items i, + organizations org, + acs_objects ob where iv.item_id = i.item_id and ii.invoice_id = i.latest_revision and iv.invoice_id = ii.invoice_id and com.object_id = ii.offer_item_id + and org.organization_id = iv.organization_id + and ob.object_id = com.category_id $category_filter_clause and [template::list::page_where_clause -name "iv_items"] [template::list::filter_where_clauses -and -name "iv_items"] @@ -36,32 +42,58 @@ select - iv_item_id, - title as item_title, - price_per_unit, - rebate, - item_units + ii.iv_item_id from - iv_invoice_itemsx + iv_invoice_itemsx ii, + iv_invoicesx iv, + category_object_map com, + cr_items i, + organizations org, + acs_objects ob + where + iv.item_id = i.item_id + and ii.invoice_id = i.latest_revision + and iv.invoice_id = ii.invoice_id + and com.object_id = ii.offer_item_id + and org.organization_id = iv.organization_id + and ob.object_id = com.category_id + $category_filter_clause + [template::list::filter_where_clauses -and -name "iv_items"] + [template::list::orderby_clause -orderby -name "iv_items"] + + + select + distinct + o.title, + c.tree_id + from + category_object_map com, + iv_offer_items io, + acs_objects o, + categories c + where + com.object_id = io.offer_item_id + and com.category_id = c.category_id + and c.tree_id = o.object_id + order by + o.title asc + + + - select - distinct - o.title, - com.category_id - from - category_object_map com, - iv_invoice_items ii, - acs_objects o - where - com.object_id = ii.offer_item_id - and com.category_id = o.object_id - $category_filter_clause - order by - o.title asc + select + o.title, + c.category_id + from + categories c, + acs_objects o + where + o.object_id = c.category_id + and c.tree_id in ([template::util::tcl_to_sql_list $tree_ids]) @@ -78,5 +110,24 @@ + + + select + sum(ii.amount_total) + from + iv_invoicesx iv, + iv_invoice_itemsx ii, + cr_items i, + category_object_map com + where + iv.item_id = i.item_id + and ii.invoice_id = i.latest_revision + and iv.invoice_id = ii.invoice_id + and ii.offer_item_id = com.object_id + and com.category_id = :c_id + + + +