Index: openacs-4/packages/invoices/lib/projects-billable.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/invoices/lib/projects-billable.xql,v
diff -u -r1.9 -r1.10
--- openacs-4/packages/invoices/lib/projects-billable.xql	2 May 2006 12:07:11 -0000	1.9
+++ openacs-4/packages/invoices/lib/projects-billable.xql	18 May 2006 19:33:59 -0000	1.10
@@ -4,20 +4,20 @@
 <fullquery name="projects_to_bill">
     <querytext>
 	select 
-		r.item_id as project_id, 
-		r.title, 
-		r.description, 
+		sub.project_id, 
+		sub.title, 
+		sub.description, 
 		sub.amount_open,
            	sub.creation_date,  
-		total.count_total, 
-		billed.count_billed, 
-		name, 
+		sub.name,
+		lower(name) as name2, 
 		sub.recipient_id,
 		sub.customer_id as org_id
-    	from (
-    		select 
-			oi.item_id as offer_id, 
-			pr.revision_id, 
+	from (select 
+			oi.item_id as offer_id,
+                        pi.item_id as project_id,
+                        pr.title,
+                        pr.description,
 			o.creation_date,
 	   		sum(ofi.item_units * ofi.price_per_unit * (1-(ofi.rebate/100))) as amount_open,
            		p.customer_id, 
@@ -30,7 +30,6 @@
          		acs_objects o, 
 			acs_data_links r, 
 			iv_offer_items ofi,
-         		acs_objects oo, 
 			cr_items oi, 
 			organizations oz
     		where 
@@ -42,50 +41,11 @@
 	 	  	and p.status_id = :p_closed_id
 	 	  	and p.invoice_p = true
     			and ofi.offer_id = oi.latest_revision
-    			and oo.object_id = oi.item_id
-    			and oo.package_id = :package_id
-    			and p.customer_id = oz.organization_id		
-    			and not exists (select 1
-                    			from iv_invoice_items ii, iv_invoices i, cr_items ci
-                    			where ii.offer_item_id = ofi.offer_item_id
-                    			and i.invoice_id = ii.invoice_id
-                    			and ci.latest_revision = i.invoice_id
-                    			and i.cancelled_p = 'f')
+    			and p.customer_id = oz.organization_id
     		group by 
-			oi.item_id, pr.revision_id, o.creation_date, p.customer_id, oz.name, p.recipient_id
-	    ) sub, 
-	    (
-    		select 
-			count(*) as count_total, oi.item_id
-		from 	
-			cr_items oi, iv_offer_items ofi
-    		where 
-			ofi.offer_id = oi.latest_revision
-    		group by 
-			oi.item_id
-    	    ) total, 
-	    (
-    		select 
-			count(ci.item_id) as count_billed, oi.item_id
-    		from 
-			cr_items oi, iv_offer_items ofi
-    			left outer join iv_invoice_items ii
-    			on (ii.offer_item_id = ofi.offer_item_id)
-    			left outer join iv_invoices i
-    			on (ii.invoice_id = i.invoice_id and i.cancelled_p = 'f')
-    			left outer join cr_items ci
-    			on (ci.latest_revision = i.invoice_id)
-    		where 
-			ofi.offer_id = oi.latest_revision
-    		group by
-			oi.item_id
-    	    ) billed, 
-		cr_revisions r
-    	where 
-		r.revision_id = sub.revision_id
-		and total.item_id = sub.offer_id
-    		and billed.item_id = sub.offer_id
-    		[template::list::filter_where_clauses -and -name projects]	
+			oi.item_id, pi.item_id, pr.title, pr.description, o.creation_date, p.customer_id, oz.name, p.recipient_id) sub
+		where	1=1 [template::list::filter_where_clauses -and -name projects]	
+    			[template::list::page_where_clause -and -name projects -key sub.project_id]
     		[template::list::orderby_clause -name projects -orderby]
       </querytext>
 </fullquery>
@@ -100,7 +60,8 @@
            	sub.creation_date,  
 		total.count_total, 
 		billed.count_billed, 
-		sub.name, 
+		sub.name,
+		lower(name) as name2, 
 		sub.recipient_id,
 		sub.customer_id as org_id
     	from (