Index: openacs-4/packages/invoices/lib/invoice-items-list.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/invoices/lib/invoice-items-list.tcl,v
diff -u -N -r1.5 -r1.6
--- openacs-4/packages/invoices/lib/invoice-items-list.tcl	7 Nov 2005 23:10:51 -0000	1.5
+++ openacs-4/packages/invoices/lib/invoice-items-list.tcl	5 Jun 2006 13:09:41 -0000	1.6
@@ -109,30 +109,7 @@
     }
 }
 
-set aggregate_amount ""
-if { [exists_and_not_null groupby] } {
-    append aggregate_amount "<ul><table border=0><tr><td><b>Aggregate Amount:</b></td><td>&nbsp;</td></tr>"
-    foreach cat $categories_filter {
-	set c_name [lindex $cat 0]
-	set c_id   [lindex $cat 1]
-	if { [exists_and_not_null category_id] } {
-	    if { [string equal $c_id $category_id] } {
-		append aggregate_amount "<tr><td><li>$c_name:</td>"
-		set amount [db_string get_amount { }]
-		append aggregate_amount "<td align=right>$amount</td>"
-		append aggregate_amount "</tr>"
-	    }
-	} else {
-	    append aggregate_amount "<tr><td><li>$c_name:</td>"
-	    set amount [db_string get_amount { }]
-	    append aggregate_amount "<td align=right>$amount</td>"
-	    append aggregate_amount "</tr>"
-	}
-    }
-append aggregate_amount "</ul>"
-}
 
-
 lappend elements item_title [list label "[_ invoices.Invoice_Item_title]"] \
     final_amount [list label "[_ invoices.Final_Amount]"] \
     invoice_title [list label "[_ invoices.Invoice_Title]" \
@@ -271,3 +248,26 @@
 	}
     }
 }
+
+set aggregate_amount ""
+if { [exists_and_not_null groupby] } {
+    append aggregate_amount "<ul><table border=0><tr><td><b>Aggregate Amount:</b></td><td>&nbsp;</td></tr>"
+    foreach cat $categories_filter {
+	set c_name [lindex $cat 0]
+	set c_id   [lindex $cat 1]
+	if { [exists_and_not_null category_id] } {
+	    if { [string equal $c_id $category_id] } {
+		append aggregate_amount "<tr><td><li>$c_name:</td>"
+		set amount [db_string get_amount { }]
+		append aggregate_amount "<td align=right>$amount</td>"
+		append aggregate_amount "</tr>"
+	    }
+	} else {
+	    append aggregate_amount "<tr><td><li>$c_name:</td>"
+	    set amount [db_string get_amount { }]
+	    append aggregate_amount "<td align=right>$amount</td>"
+	    append aggregate_amount "</tr>"
+	}
+    }
+append aggregate_amount "</ul>"
+}
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 -N -r1.4 -r1.5
--- openacs-4/packages/invoices/lib/invoice-items-list.xql	7 Nov 2005 23:10:51 -0000	1.4
+++ openacs-4/packages/invoices/lib/invoice-items-list.xql	5 Jun 2006 13:09:41 -0000	1.5
@@ -125,6 +125,7 @@
 		and iv.invoice_id = ii.invoice_id
 		and ii.offer_item_id = com.object_id
 		and com.category_id = :c_id
+		[template::list::filter_where_clauses -and -name "iv_items"]
     </querytext>
 </fullquery>
 
Index: openacs-4/packages/invoices/lib/offer-items-list.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/invoices/lib/offer-items-list.tcl,v
diff -u -N -r1.6 -r1.7
--- openacs-4/packages/invoices/lib/offer-items-list.tcl	31 Dec 2005 07:36:24 -0000	1.6
+++ openacs-4/packages/invoices/lib/offer-items-list.tcl	5 Jun 2006 13:09:41 -0000	1.7
@@ -108,44 +108,6 @@
     }
 }
 
-set aggregate_amount ""
-if { [exists_and_not_null groupby] } {
-    append aggregate_amount "<ul><table border=0><tr><td><b>Aggregate Amount:</b></td><td>&nbsp;</td></tr>"
-    foreach cat $categories_filter {
-        set c_name [lindex $cat 0]
-        set c_id   [lindex $cat 1]
-	set amount_values [db_list_of_lists get_amount_values { }]
-	set total_amount "0.00"
-	foreach val $amount_values {
-	    set ppu [lindex $val 0]
-	    set iu  [lindex $val 1]
-	    set r   [lindex $val 2]
-	    set amount [expr $ppu * $iu]
-	    if { [string equal $r "0.00"] } {
-		set amount [format %.2f $amount]
-	    } else {
-		set amount [format %.2f [expr $amount - [expr [expr $r / 100] * $amount]]]
-	    }
-	    set total_amount [expr $total_amount + $amount]
-	}
-
-        if { [exists_and_not_null category_id] } {
-	    foreach cat_id $category_id {
-		if { [string equal $c_id $cat_id] } {
-		    append aggregate_amount "<tr><td><li>$c_name:</td>"
-		    append aggregate_amount "<td align=right>$total_amount</td>"
-		    append aggregate_amount "</tr>"
-		}
-	    }
-        } else {
-            append aggregate_amount "<tr><td><li>$c_name:</td>"
-            append aggregate_amount "<td align=right>$total_amount</td>"
-            append aggregate_amount "</tr>"
-        }
-    }
-append aggregate_amount "</ul>"
-}
-
 lappend elements item_title [list label "[_ invoices.Offer_Item_Title]"] \
     final_amount [list label "[_ invoices.Final_Amount]"] \
     offer_title [list label "[_ invoices.Offer_Title]" \
@@ -194,12 +156,21 @@
     }
 }
 
-set project_status_p 0
-if { [exists_and_not_null project_status_id] } {
-    set project_status_p 1
+# If the project_status_id filter is set, then
+# Limit it in the pagination query
+if {[exists_and_not_null project_status_id]} {
+    set project_pag_query "and i.item_id in (select object_id_one
+from acs_data_links r, cr_items i 
+where r.object_id_one = i.item_id 
+and i.content_type = 'iv_offer' 
+and object_id_two in (select item_id 
+   from cr_items pi, pm_projects p 
+   where p.status_id = :project_status_id 
+   and pi.latest_revision = p.project_id))"
+} else {
+    set project_pag_query ""
 }
 
-
 set groupby_values {
     { "#invoices.Customer#" { { groupby org_name } { offer_items_orderby org_name,asc } } }
     { "#invoices.Category#" { { groupby cat_name } { offer_items_orderby cat_name,asc } } }    
@@ -226,14 +197,10 @@
 	    orderby_desc { lower(o.title) desc }
 	    orderby_asc { lower(o.title) asc }
 	}
-	org_name {
-	    label { [_ invoices.Customer] }
-	    orderby_asc { org.name asc }
-	}
-	cat_name {
-	    label { [_ invoices.Category] }
-	    orderby_asc { ob.title asc }
-	}
+        cat_name {
+            label { [_ invoices.Category] }
+            orderby_asc { ob.title asc }
+        }
 	month {
 	    label { [_ invoices.Month] }
 	    orderby_asc { to_char(oi.creation_date,'mm') asc }
@@ -271,23 +238,43 @@
 	set tree_id [category::get_tree $cat_id]
 	set tree_$tree_id $cat_name
     }
-    set project_item_id [lindex [application_data_link::get_linked -from_object_id $item_id -to_object_type content_item] 0]
-    if { $project_status_p } {
-	if { [exists_and_not_null project_item_id] } {
-	    switch $project_status_id {
-		"1" {
-		    if { ![pm::project::open_p -project_item_id $project_item_id] } {
-			continue
-		    }
-		}
-		"2" {
-		    if { [pm::project::open_p -project_item_id $project_item_id] } {
-			continue
-		    }
-		}
+}
+
+set aggregate_amount ""
+if { [exists_and_not_null groupby] } {
+    append aggregate_amount "<ul><table border=0><tr><td><b>Aggregate Amount:</b></td><td>&nbsp;</td></tr>"
+    foreach cat $categories_filter {
+        set c_name [lindex $cat 0]
+        set c_id   [lindex $cat 1]
+	set amount_values [db_list_of_lists get_amount_values { }]
+	set total_amount "0.00"
+	foreach val $amount_values {
+	    set ppu [lindex $val 0]
+	    set iu  [lindex $val 1]
+	    set r   [lindex $val 2]
+	    set amount [expr $ppu * $iu]
+	    if { [string equal $r "0.00"] } {
+		set amount [format %.2f $amount]
+	    } else {
+		set amount [format %.2f [expr $amount - [expr [expr $r / 100] * $amount]]]
 	    }
-	} else {
-	    continue
+	    set total_amount [expr $total_amount + $amount]
 	}
+
+        if { [exists_and_not_null category_id] } {
+	    foreach cat_id $category_id {
+		if { [string equal $c_id $cat_id] } {
+		    append aggregate_amount "<tr><td><li>$c_name:</td>"
+		    append aggregate_amount "<td align=right>$total_amount</td>"
+		    append aggregate_amount "</tr>"
+		}
+	    }
+        } else {
+            append aggregate_amount "<tr><td><li>$c_name:</td>"
+            append aggregate_amount "<td align=right>$total_amount</td>"
+            append aggregate_amount "</tr>"
+        }
     }
+append aggregate_amount "</ul>"
 }
+
Index: openacs-4/packages/invoices/lib/offer-items-list.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/invoices/lib/offer-items-list.xql,v
diff -u -N -r1.3 -r1.4
--- openacs-4/packages/invoices/lib/offer-items-list.xql	7 Nov 2005 23:10:51 -0000	1.3
+++ openacs-4/packages/invoices/lib/offer-items-list.xql	5 Jun 2006 13:09:41 -0000	1.4
@@ -6,7 +6,7 @@
 	select
 		oi.offer_item_id,
 		oi.title as item_title,
-		o.title as offer_title,
+		cro.title as offer_title,
 		oi.price_per_unit,
 		oi.rebate,
 		oi.item_units,
@@ -15,22 +15,23 @@
 		org.name as org_name,
 		to_char(oi.creation_date,'yy-mm-dd') as creation_date,
 		to_char(oi.creation_date,'mm') as month,
-		o.item_id, 
+		i.item_id,
 		ob.title as cat_name
 	from
 		iv_offer_itemsx oi,
-		iv_offersx o,
+		iv_offers o,
+		cr_revisions cro,
 		organizations org,
 		category_object_map com,
 		cr_items i,
 		acs_objects ob
 	where
-		i.item_id = o.item_id
-		and o.offer_id = i.latest_revision
+		o.offer_id = i.latest_revision
 		and o.offer_id = oi.offer_id
+		and cro.revision_id = o.offer_id
 		and com.object_id = oi.offer_item_id
 		and org.organization_id = o.organization_id
-		and com.category_id = ob.object_id
+                and com.category_id = ob.object_id
 		$category_filter_clause
 		and [template::list::page_where_clause -name "offer_items"]	
  		[template::list::filter_where_clauses -and -name "offer_items"]
@@ -43,20 +44,19 @@
 	select
 		oi.offer_item_id
 	from
-		iv_offer_itemsx oi,
-		iv_offersx o,
-		organizations org,
-		category_object_map com,
+		iv_offer_items oi,
+		iv_offers o,
 		cr_items i,
-		acs_objects ob
+                category_object_map com,
+                acs_objects ob
 	where
-		i.item_id = o.item_id
-		and o.offer_id = i.latest_revision
+		-- get latest revision from the offer
+		o.offer_id = i.latest_revision
 		and o.offer_id = oi.offer_id
-		and com.object_id = oi.offer_item_id
-		and org.organization_id = o.organization_id
-		and com.category_id = ob.object_id
+                and com.object_id = oi.offer_item_id
+                and com.category_id = ob.object_id
 		$category_filter_clause 
+		$project_pag_query
 		[template::list::filter_where_clauses -and -name "offer_items"]
 		[template::list::orderby_clause -orderby -name "offer_items"]
     </querytext>
@@ -103,17 +103,19 @@
 		item_units,
 		rebate
         from
-		iv_offer_itemsx oi,
+		iv_offer_items oi,
 		category_object_map com,
-		iv_offersx o,
+		iv_offers o,
 		cr_items i
 		
         where
-		i.item_id = o.item_id
-		and o.offer_id = i.latest_revision
+		-- get latest revision from the offer
+		o.offer_id = i.latest_revision
 		and o.offer_id = oi.offer_id
-		and com.object_id = oi.offer_item_id
+                and com.object_id = oi.offer_item_id
+		$project_pag_query
 		and com.category_id = :c_id
+		[template::list::filter_where_clauses -and -name "offer_items"]
     </querytext>
 </fullquery>
 
Index: openacs-4/packages/invoices/lib/report-customer.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/invoices/lib/report-customer.tcl,v
diff -u -N -r1.3 -r1.4
--- openacs-4/packages/invoices/lib/report-customer.tcl	19 Apr 2006 14:22:35 -0000	1.3
+++ openacs-4/packages/invoices/lib/report-customer.tcl	5 Jun 2006 13:09:41 -0000	1.4
@@ -173,5 +173,5 @@
 set contacts_url [apm_package_url_from_key contacts]
 
 db_multirow -extend {customer_url} reports $sql_query_name {} {
-    set customer_url "${contacts_url}$customer_id"
+    set customer_url [export_vars -base "invoice-items" -url {customer_id {groupby "cat_name"} {orderby "cat_name"}}]
 }