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.8 -r1.9
--- openacs-4/packages/invoices/lib/projects-billable.xql	2 Apr 2006 22:08:02 -0000	1.8
+++ openacs-4/packages/invoices/lib/projects-billable.xql	2 May 2006 12:07:11 -0000	1.9
@@ -90,4 +90,136 @@
       </querytext>
 </fullquery>
 
+<fullquery name="projects_to_bill2">
+    <querytext>
+	select 
+		sub.project_id, 
+		sub.title, 
+		sub.description, 
+		sub.amount_open,
+           	sub.creation_date,  
+		total.count_total, 
+		billed.count_billed, 
+		sub.name, 
+		sub.recipient_id,
+		sub.customer_id as org_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, 
+			p.recipient_id,
+			oz.name
+	    	from 
+			cr_items pi, 
+			cr_revisions pr, 
+			pm_projects p,
+         		acs_objects o, 
+			acs_data_links r, 
+			iv_offer_items ofi,
+			cr_items oi, 
+			organizations oz
+    		where 
+			pi.latest_revision = pr.revision_id
+    			and p.project_id = pr.revision_id
+    			and o.object_id = p.project_id
+			and r.object_id_one = pi.item_id
+    			and r.object_id_two = oi.item_id
+	 	  	and p.status_id = :p_closed_id
+	 	  	and p.invoice_p = true
+    			and ofi.offer_id = oi.latest_revision
+    			and p.customer_id = oz.organization_id
+    		group by 
+			oi.item_id, pi.item_id, pr.title, pr.description, 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
+    	where 
+		total.item_id = sub.offer_id
+    		and billed.item_id = sub.offer_id
+    		[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>
+
+<fullquery name="projects_to_bill_paginated">
+    <querytext>
+	select 
+		sub.project_id
+    	from (
+    		select 
+                        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, 
+			p.recipient_id,
+			oz.name
+	    	from 
+			cr_items pi, 
+			cr_revisions pr, 
+			pm_projects p,
+         		acs_objects o, 
+			acs_data_links r, 
+			iv_offer_items ofi,
+         		acs_objects oo, 
+			cr_items oi, 
+			organizations oz
+    		where 
+			pi.latest_revision = pr.revision_id
+    			and p.project_id = pr.revision_id
+    			and o.object_id = p.project_id
+			and r.object_id_one = pi.item_id
+    			and r.object_id_two = oi.item_id
+	 	  	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')
+    		group by 
+			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::orderby_clause -name projects -orderby]
+      </querytext>
+</fullquery>
+
 </queryset>