Index: openacs-4/packages/invoices/invoices.info
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/invoices/invoices.info,v
diff -u -r1.27 -r1.28
--- openacs-4/packages/invoices/invoices.info	5 Jun 2006 14:07:36 -0000	1.27
+++ openacs-4/packages/invoices/invoices.info	18 Jun 2006 11:42:25 -0000	1.28
@@ -7,15 +7,15 @@
     <initial-install-p>f</initial-install-p>
     <singleton-p>t</singleton-p>
     
-    <version name="1.0d9" url="http://openacs.org/repository/download/apm/invoices-1.0d9.apm">
+    <version name="1.0a1" url="http://openacs.org/repository/download/apm/invoices-1.0a1.apm">
         <owner url="mailto:timo@timohentschel.de">Timo Hentschel</owner>
         <summary>Invoices package that will collect costs to invoice items in invoices and store data about received payment.</summary>
         <release-date>2005-11-15</release-date>
         <vendor url="http://www.cognovis.de">Cognovis</vendor>
         <description format="text/plain">Collect costs to invoice items and invoices, store payment information.</description>
         <maturity>0</maturity>
 
-        <provides url="invoices" version="1.0d9"/>
+        <provides url="invoices" version="1.0a1"/>
         <requires url="acs-content-repository" version="5.1.4d3"/>
         <requires url="acs-kernel" version="5.0d13"/>
         <requires url="acs-templating" version="5.1.5d1"/>
Index: openacs-4/packages/invoices/catalog/invoices.de_DE.ISO-8859-1.xml
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/invoices/catalog/invoices.de_DE.ISO-8859-1.xml,v
diff -u -r1.38 -r1.39
--- openacs-4/packages/invoices/catalog/invoices.de_DE.ISO-8859-1.xml	5 Jun 2006 14:07:36 -0000	1.38
+++ openacs-4/packages/invoices/catalog/invoices.de_DE.ISO-8859-1.xml	18 Jun 2006 11:42:25 -0000	1.39
@@ -1,5 +1,5 @@
 <?xml version="1.0" encoding="ISO-8859-1"?>
-<message_catalog package_key="invoices" package_version="1.0d8" locale="de_DE" charset="ISO-8859-1">
+<message_catalog package_key="invoices" package_version="1.0d9" locale="de_DE" charset="ISO-8859-1">
 
   <msg key="accepted">Auftragsbest�tigungen</msg>
   <msg key="Accepted_date">Akzeptiert am</msg>
@@ -51,6 +51,7 @@
   <msg key="Date_Range">Zeitraum:</msg>
   <msg key="day">Tag</msg>
   <msg key="Delete">L�schen</msg>
+  <msg key="delivered_projects">Ausgelieferte Projekte</msg>
   <msg key="Due">F�llig</msg>
   <msg key="Due_date">F�lligkeit</msg>
   <msg key="Edit">�ndern</msg>
@@ -78,6 +79,7 @@
   <msg key="Invoice">Rechnung</msg>
   <msg key="Invoice_Cancel">Stornieren</msg>
   <msg key="Invoice_count">Anzahl Rechnungen</msg>
+  <msg key="invoice_creation_user_help">Benutzer der die Rechnung erstellt hat</msg>
   <msg key="invoice_display">Dokumente zum Drucken anzeigen</msg>
   <msg key="invoice_email">Email</msg>
   <msg key="invoice_for_join">Zum sp�teren Laden markieren</msg>
@@ -146,6 +148,7 @@
   <msg key="iv_invoice_count_billed">berechnete Positonen</msg>
   <msg key="iv_invoice_count_total">Anzahl Positionen</msg>
   <msg key="iv_invoice_creation_date">erstellt am</msg>
+  <msg key="iv_invoice_creation_date_help">Datum an dem die Rechnung angelegt wurde</msg>
   <msg key="iv_invoice_creation_user">erstellt von</msg>
   <msg key="iv_invoice_credit_1">Gutschrift</msg>
   <msg key="iv_invoice_credit_Add">Gutschrift erstellen</msg>
@@ -227,6 +230,7 @@
   <msg key="iv_invoice_url">Invoices</msg>
   <msg key="iv_invoice_url2">Rechnungen, Preislisten, Reports</msg>
   <msg key="iv_invoice_vat">Mehrwertsteuer</msg>
+  <msg key="iv_invoice_vat_help">MwST (Mehrwertsteuer)</msg>
   <msg key="iv_invoice_vat_percent">Mehrwertsteuersatz</msg>
   <msg key="iv_invoice_vat_percent_help">Mehrwertsteuersatz welcher der Rechnung hinzugef�gt wird</msg>
   <msg key="iv_invoice_View">Rechnung ansehen</msg>
@@ -236,6 +240,8 @@
   <msg key="iv_join_invoice">Vorbereitete Rechnungen laden</msg>
   <msg key="iv_journal">Rechnungsbuch</msg>
   <msg key="iv_journal_check">Rechnungsbuch erstellen</msg>
+  <msg key="iv_mass_invoice_New">Stapelrechnung</msg>
+  <msg key="iv_mass_invoice_New2">Anlegen einer einzelnen Rechnung pro project</msg>
   <msg key="iv_of_month_year">Rechnungen von %long_month% %year%:</msg>
   <msg key="iv_of_year">Rechnungen des Jahres %year%:</msg>
   <msg key="iv_offer_1">PA</msg>
@@ -257,6 +263,7 @@
   <msg key="iv_offer_comment">&lt;b&gt;Interner Kommentar&lt;/b&gt;</msg>
   <msg key="iv_offer_comment_help">Interner Kommentar der nicht dem Kunden gezeigt wird.</msg>
   <msg key="iv_offer_count_total">Anzahl Positionen</msg>
+  <msg key="iv_offer_create_New">&lt;font color=green&gt;Vorausgef�llte Projektaufstellung hinzuf�gen&lt;/font&gt;</msg>
   <msg key="iv_offer_creation_date">erstellt am</msg>
   <msg key="iv_offer_creation_date_help">Erstelldatum</msg>
   <msg key="iv_offer_creation_user">Kundenberater</msg>
@@ -350,6 +357,8 @@
   <msg key="iv_offer_send">Angebot schicken</msg>
   <msg key="iv_offer_send_accepted">Auftragsbest�tigung schicken</msg>
   <msg key="iv_offer_send_again">Angebot erneut schicken</msg>
+  <msg key="iv_offer_show_sum_p">Gesamtsumme im Angebot</msg>
+  <msg key="iv_offer_show_sum_p_help">Zeige die gesamte Summe im Angebots PDF</msg>
   <msg key="iv_offer_subproject_finish_date_help">Interne Deadline des Subprojektes</msg>
   <msg key="iv_offer_template">Angebotsvorlage</msg>
   <msg key="iv_offer_Title">Titel</msg>
@@ -359,6 +368,7 @@
   <msg key="iv_offer_vat_percent">Mehrwertsteuersatz</msg>
   <msg key="iv_offer_vat_percent_help">Wie hoch ist die MwSt, die dem Kunden berechnet wird</msg>
   <msg key="iv_offer_View">Projektaufstellung ansehen</msg>
+  <msg key="iv_parent_offer_View">Projektaufstellung des Hauptprojektes</msg>
   <msg key="iv_payment_1">Zahlung</msg>
   <msg key="iv_payment_2">Zahlungen</msg>
   <msg key="iv_payment_Add">Zahlung hinzuf�gen</msg>
@@ -399,6 +409,11 @@
   <msg key="Link_instances">Anwendungen verlinken</msg>
   <msg key="lt_no_such_invoice_no">Leider k�nnen wir die von Ihnen angeforderte Rechnungsnummer nicht in unserem System finden. Bitte �berpr�fen Sie ob die Nummer richtig ist.</msg>
   <msg key="Mapped">Verbunden</msg>
+  <msg key="mass_invoice_email_body">Die Stapelverarbeitung der Rechungen ist abschlossen. &lt;p&gt; %failed_projects_html%</msg>
+  <msg key="mass_invoice_email_subject">Stapelverarbeitung der Rechnungserstellung abgeschlossen.</msg>
+  <msg key="mass_invoice_error">Leider gab es ein paar Probleme. Bitte legen Sie f�r die folgenden Projekte die Rechnung manuell an:</msg>
+  <msg key="missing_offer_list">Fehlende Projektaufstellung</msg>
+  <msg key="missing_offer_list_text">Es existiert noch keine Projektaufstellung zu diesem Projekt. Bitte legen Sie diese an.</msg>
   <msg key="Month">Monat</msg>
   <msg key="month">Monat</msg>
   <msg key="More_than_one_customer">Mehr als ein Kunde</msg>
@@ -422,6 +437,7 @@
   <msg key="offers">Angebote</msg>
   <msg key="Offers_items">Kostenpositionen</msg>
   <msg key="ok">Ok</msg>
+  <msg key="open_projects">Offene Projekte</msg>
   <msg key="Page_count">Seitenyahl</msg>
   <msg key="Parent_invoice_item">Bezug auf Rechnungsposition</msg>
   <msg key="Parent_offer_item">Bezug auf Kostenposition</msg>
Index: openacs-4/packages/invoices/catalog/invoices.en_US.ISO-8859-1.xml
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/invoices/catalog/invoices.en_US.ISO-8859-1.xml,v
diff -u -r1.40 -r1.41
--- openacs-4/packages/invoices/catalog/invoices.en_US.ISO-8859-1.xml	5 Jun 2006 14:07:36 -0000	1.40
+++ openacs-4/packages/invoices/catalog/invoices.en_US.ISO-8859-1.xml	18 Jun 2006 11:42:25 -0000	1.41
@@ -1,5 +1,5 @@
 <?xml version="1.0" encoding="ISO-8859-1"?>
-<message_catalog package_key="invoices" package_version="1.0d8" locale="en_US" charset="ISO-8859-1">
+<message_catalog package_key="invoices" package_version="1.0d9" locale="en_US" charset="ISO-8859-1">
 
   <msg key="accepted">Order confirmation</msg>
   <msg key="Accepted_date">Accepted on</msg>
@@ -51,6 +51,7 @@
   <msg key="Date_Range">Date Range:</msg>
   <msg key="day">Day</msg>
   <msg key="Delete">Delete</msg>
+  <msg key="delivered_projects">Delivered Projects</msg>
   <msg key="Due">Due</msg>
   <msg key="Due_date">Due date</msg>
   <msg key="Edit">Edit</msg>
@@ -78,6 +79,7 @@
   <msg key="Invoice">Invoice</msg>
   <msg key="Invoice_Cancel">Cancel</msg>
   <msg key="Invoice_count">Invoice count</msg>
+  <msg key="invoice_creation_user_help">User who created the invoice</msg>
   <msg key="invoice_display">Display documents</msg>
   <msg key="invoice_email">Email</msg>
   <msg key="invoice_for_join">Mark for later download</msg>
@@ -146,6 +148,7 @@
   <msg key="iv_invoice_count_billed">Billed items</msg>
   <msg key="iv_invoice_count_total">Total items</msg>
   <msg key="iv_invoice_creation_date">Created on</msg>
+  <msg key="iv_invoice_creation_date_help">Date the invoice was created</msg>
   <msg key="iv_invoice_creation_user">Created by</msg>
   <msg key="iv_invoice_credit_1">Credit</msg>
   <msg key="iv_invoice_credit_Add">Add invoice credit</msg>
@@ -227,6 +230,7 @@
   <msg key="iv_invoice_url">Invoices</msg>
   <msg key="iv_invoice_url2">Rechnungen, Preislisten, Reports</msg>
   <msg key="iv_invoice_vat">VAT</msg>
+  <msg key="iv_invoice_vat_help">VAT (value added tax)</msg>
   <msg key="iv_invoice_vat_percent">%VAT</msg>
   <msg key="iv_invoice_vat_percent_help">Percentage of VAT added in the invoice</msg>
   <msg key="iv_invoice_View">View Invoice</msg>
@@ -236,6 +240,8 @@
   <msg key="iv_join_invoice">Load prepared invoices</msg>
   <msg key="iv_journal">Invoice journal</msg>
   <msg key="iv_journal_check">Export Journal</msg>
+  <msg key="iv_mass_invoice_New">Mass invoice</msg>
+  <msg key="iv_mass_invoice_New2">Mass create invoices for the selected projects</msg>
   <msg key="iv_of_month_year">Invoices of %long_month% %year%:</msg>
   <msg key="iv_of_year">Invoices of year %year%:</msg>
   <msg key="iv_offer_1">Cost Sheet</msg>
@@ -256,6 +262,7 @@
   <msg key="iv_offer_comment">Comment</msg>
   <msg key="iv_offer_comment_help">Internal comment not shown to the customer</msg>
   <msg key="iv_offer_count_total">Total items</msg>
+  <msg key="iv_offer_create_New">Create prefilled offer</msg>
   <msg key="iv_offer_creation_date">Created on</msg>
   <msg key="iv_offer_creation_date_help">Creation Date</msg>
   <msg key="iv_offer_creation_user">Created by</msg>
@@ -359,6 +366,7 @@
   <msg key="iv_offer_vat_percent">%VAT</msg>
   <msg key="iv_offer_vat_percent_help">What is the VAT charged for the customer</msg>
   <msg key="iv_offer_View">View Cost Sheet</msg>
+  <msg key="iv_parent_offer_View">View offer from parent project</msg>
   <msg key="iv_payment_1">Payment</msg>
   <msg key="iv_payment_2">Payments</msg>
   <msg key="iv_payment_Add">Add Payment</msg>
@@ -399,6 +407,11 @@
   <msg key="Link_instances">Link package instances</msg>
   <msg key="lt_no_such_invoice_no">We are sorry but the invoice number you are searching for does not exist in our system.</msg>
   <msg key="Mapped">Mapped</msg>
+  <msg key="mass_invoice_email_body">The mass generation of invoices is finished.&lt;p&gt; %failed_projects_html%</msg>
+  <msg key="mass_invoice_email_subject">Invoice mass generation finished.</msg>
+  <msg key="mass_invoice_error">Sadly there have been some errors. Please create invoices for the following projects manually:</msg>
+  <msg key="missing_offer_list">Missing Offer List</msg>
+  <msg key="missing_offer_list_text">You did not provide an offer list. Please create one first.</msg>
   <msg key="Month">Month</msg>
   <msg key="month">Month</msg>
   <msg key="More_than_one_customer">More than one customer</msg>
@@ -422,6 +435,7 @@
   <msg key="offers">Cost Sheets</msg>
   <msg key="Offers_items">Offers Items</msg>
   <msg key="ok">Ok</msg>
+  <msg key="open_projects">Open Projects</msg>
   <msg key="Page_count">Number of pages</msg>
   <msg key="Parent_invoice_item">Parent invoice item</msg>
   <msg key="Parent_offer_item">Parent cost item</msg>
Index: openacs-4/packages/invoices/lib/projects-billable.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/invoices/lib/projects-billable.tcl,v
diff -u -r1.16 -r1.17
--- openacs-4/packages/invoices/lib/projects-billable.tcl	12 Jun 2006 09:11:23 -0000	1.16
+++ openacs-4/packages/invoices/lib/projects-billable.tcl	18 Jun 2006 11:42:26 -0000	1.17
@@ -75,7 +75,8 @@
     set actions ""
     set bulk_id_list ""
 } else {
-    set actions [list "[_ invoices.iv_invoice_New]" "${base_url}invoice-ae" "[_ invoices.iv_invoice_New2]" ]
+    set actions  [list "[_ invoices.iv_invoice_New]" "${base_url}invoice-ae" "[_ invoices.iv_invoice_New2]" "[_ invoices.iv_mass_invoice_New]" "${base_url}mass-invoice" "[_ invoices.iv_mass_invoice_New2]" ]
+
     set bulk_id_list [list organization_id return_url]
     set row_list "checkbox {}\n $row_list"
 }
Index: openacs-4/packages/invoices/www/invoice-ae.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/invoices/www/invoice-ae.tcl,v
diff -u -r1.33 -r1.34
--- openacs-4/packages/invoices/www/invoice-ae.tcl	5 Jun 2006 14:07:36 -0000	1.33
+++ openacs-4/packages/invoices/www/invoice-ae.tcl	18 Jun 2006 11:42:26 -0000	1.34
@@ -314,7 +314,7 @@
 
 	# get all subprojects marked for no invoice to display warning
 	foreach main_project_id $project_id {
-	    set subprojects [pm::project:subprojects -project_id $main_project_id]
+	    set subprojects [pm::project::subprojects -project_item_id $main_project_id]
 
 	    db_foreach not_invoiceable_subprojects {} {
 		set offer_url [export_vars -base offer-ae {offer_id {mode display}}]
Index: openacs-4/packages/invoices/www/mass-invoice-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/invoices/www/mass-invoice-postgresql.xql,v
diff -u
--- /dev/null	1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/invoices/www/mass-invoice-postgresql.xql	18 Jun 2006 11:42:26 -0000	1.1
@@ -0,0 +1,47 @@
+<?xml version="1.0"?>
+<queryset>
+	<rdbms><type>postgresql</type><version>7.3</version></rdbms>
+
+
+<fullquery name="today">
+      <querytext>
+      
+      select to_char(now(),'YYYY-MM-DD') from dual
+    
+      </querytext>
+</fullquery>
+
+<fullquery name="get_open_rebate">
+      <querytext>
+      
+	select sum(o.amount_sum - o.amount_total) as open_rebate
+	from iv_offers o, acs_data_links r, cr_items co
+	where r.object_id_one in ([join $project_id ,])
+	and r.object_id_two = co.item_id
+	and co.latest_revision = o.offer_id
+    
+      </querytext>
+</fullquery>
+
+<fullquery name="get_given_rebate">
+      <querytext>
+      
+	select i.invoice_id, i.amount_sum - i.total_amount as given_rebate
+	from acs_rels r, cr_items co, iv_offer_items oi,
+	     iv_invoice_items ii, iv_invoices i, cr_items ci
+	where i.invoice_id <> :invoice_rev_id
+	and i.parent_invoice_id is null
+	and ci.latest_revision = i.invoice_id
+	and i.invoice_id in (select ii.invoice_id
+	                     from iv_invoice_items ii, iv_offer_items oi,
+	                          cr_items co, acs_data_links r
+	                     where oi.offer_id = co.latest_revision
+	                     and r.object_id_one in ([join $project_id ,])
+	                     and r.object_id_two = co.item_id
+	                     and oi.offer_item_id = ii.offer_item_id)
+
+      </querytext>
+</fullquery>
+
+
+</queryset>
Index: openacs-4/packages/invoices/www/mass-invoice.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/invoices/www/mass-invoice.tcl,v
diff -u
--- /dev/null	1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/invoices/www/mass-invoice.tcl	18 Jun 2006 11:42:26 -0000	1.1
@@ -0,0 +1,342 @@
+ad_page_contract {
+    Page to mass generate inoices, one for each project
+
+    @author Malte Sussdorff (malte.sussdorff@cognovis.de)
+    @creation-date 2006-06-18
+} {
+    project_id:multiple
+    {return_url:optional ""}
+    {__new_p 0}
+    {mode edit}
+    {send:optional}
+} -properties {
+    context:onevalue
+    page_title:onevalue
+}
+
+set current_url "[ad_conn url]?[ad_conn query]"
+set package_id [ad_conn package_id]
+set user_id [auth::require_login]
+set date_format "YYYY-MM-DD"
+set has_submit 0
+set has_edit 0
+set offer_ids [list]
+set files [list]
+set failed_project_ids [list]
+
+if {$__new_p} {
+    set project_id [string trim $project_id "{}"]
+}
+set page_title "[_ invoices.iv_invoice_Add2]"
+set _invoice_id 0
+set invoice_rev_id 0
+set cur_total_amount 0
+
+array set container_objects [iv::util::get_default_objects -package_id $package_id]
+set timestamp_format "$date_format [lc_get formbuilder_time_format]"
+
+set language [lang::conn::language]
+
+foreach project_item_id $project_id {
+
+    # We need to make sure the whole process runs through smoothly for invoice
+    # generation. Therefore we put this in transaction. If the transaction fails
+    # add the project number to a list and notify the user about it later.
+
+    # normal invoice: get recipients from projects
+    db_1row contacts {}
+    
+    # Reset variables
+    
+    set project_title 0
+    set total_amount 0.
+    set total_credit 0.
+    set offer_ids [list]	
+    
+    #####  INSERT INVOICE #################
+    
+    set due_date [db_string today {}]
+    set title "[_ invoices.iv_invoice_1] [organizations::name -organization_id $customer_id] $due_date"
+    
+    db_1row offer_data {}
+    
+    # Get the recipient information and data.
+    if {$recipient_id eq ""} {
+	set rec_organization_id $customer_id
+    } else {
+	if {[person::person_p -party_id $recipient_id]} {
+	    set rec_organization_id [contact::util::get_employee_organization -employee_id $recipient_id]
+	} else {
+	    set rec_organization_id $recipient_id
+	}
+    }
+	
+    set contacts_package_id [lindex [application_link::get_linked -from_package_id $package_id -to_package_key contacts] 0]
+    array set org_data [contacts::get_values \
+			    -group_name "Customers" \
+			    -object_type "organization" \
+			    -party_id $rec_organization_id \
+			    -contacts_package_id $contacts_package_id]
+    
+    if {[info exists org_data(vat_percent)]} {
+	set vat_percent [format "%.1f" $org_data(vat_percent)]
+    } else {
+	set vat_percent [format "%.1f" 0]
+    }
+    
+    set credit_category_id [parameter::get -parameter "CreditCategory"]
+    set description [lang::util::localize [pm::project::name -project_item_id $project_item_id]]	
+    
+    # If an invoice exists with this description 
+    
+    if {[db_string existing_invoice "select 1 from iv_invoicesx where description = :description" -default 0]} {
+	continue
+	}
+    
+    # We are getting the invoice_nr here as we are generating the PDF now.
+    set invoice_nr [db_nextval iv_invoice_seq]
+    
+    set currency [iv::price_list::get_currency -organization_id $rec_organization_id]	
+
+    db_transaction {
+	set new_invoice_rev_id [iv::invoice::new  \
+				    -title $title \
+				    -description $description  \
+				    -contact_id $contact_id \
+				    -recipient_id $recipient_id \
+				    -invoice_nr $invoice_nr \
+				    -organization_id $rec_organization_id \
+				    -total_amount $amount_total \
+				    -amount_sum $amount_sum \
+				    -currency $currency \
+				    -due_date $due_date \
+				    -payment_days $payment_days \
+				    -vat_percent $vat_percent \
+				    -vat $vat]
+	
+	set invoice_id [content::revision::item_id -revision_id $new_invoice_rev_id]
+	if {[exists_and_not_null category_ids]} {
+	    category::map_object -object_id $new_invoice_rev_id $category_ids
+	}
+	
+	
+	###### Prepare invoice items ###########
+	
+	set counter 0
+	
+	
+	# Can't use db_foreach as we are in a transaction
+	set offer_items_list [db_list_of_lists offer_items {}]
+	
+	foreach offer_items $offer_items_list {
+	    
+	    template::util::list_to_array $offer_items offer [list title description offer_item_id item_units offer_id \
+								  price_per_unit item_nr project_id credit_percent \
+								  project_title vat rebate category_id offer_cr_item_id
+							     ]
+	    
+	    
+	    set offer(price_per_unit) [format "%.2f" $offer(price_per_unit)]
+	    set offer(amount_sum) [format "%.2f" [expr $offer(item_units) * $offer(price_per_unit)]]
+	    set offer(amount) [format "%.2f" [expr (1. - ($offer(rebate) / 100.)) * $offer(amount_sum)]]
+	    set offer(rebate) [format "%.1f" $offer(rebate)]
+	    set offer(category) [lang::util::localize [category::get_name $offer(category_id)]]
+	    
+	    # Calculate the credit
+	    if {[empty_string_p $offer(credit_percent)]} {
+		set offer(credit_percent) 0.
+	    }
+	    if {$offer(price_per_unit) > 1.} {
+		set offer(credit) [format "%.1f" [expr $offer(item_units) * (($offer(credit_percent) + 100.) / 100.)]]
+	    } else {
+		# do not add credit to items with price of 1 or less
+		set offer(credit) [format "%.1f" $offer(item_units)]
+	    }
+	    set offer(credit) [format "%.2f" [expr $offer(credit) * $offer(price_per_unit)]]
+	    set offer(credit) [format "%.2f" [expr (1. - ($offer(rebate) / 100.)) * $offer(credit)]]
+	    set offer(credit) [format "%.2f" [expr $offer(credit) - $offer(amount)]]
+	    
+	    set offer_name ""
+	    if {![empty_string_p $offer(category)]} {
+		set offer_name "$offer(category): "
+	    }
+	    append offer_name "$offer(item_units) x $offer(price_per_unit) $currency = $offer(amount_sum) $currency"
+	    if {$offer(rebate) > 0} {
+		append offer_name " - $offer(rebate)% [_ invoices.iv_offer_item_rebate] = $offer(amount) $currency"
+	    }
+	    if {![empty_string_p $offer(description)]} {
+		append offer_name " ($offer(description))"
+	    }
+	    
+	    set total_amount [expr $total_amount + $offer(amount) + $offer(credit)]
+	    set total_credit [expr $total_credit + $offer(credit)]
+	    
+	    # Insert the invoice item
+	    incr counter
+	    set offer(vat) [expr $vat_percent * $offer(vat) / 100.]
+	    
+	    set new_item_rev_id [iv::invoice_item::new \
+				     -invoice_id $new_invoice_rev_id \
+				     -title $offer(title) \
+				     -description $offer(description)  \
+				     -item_nr $offer(item_nr) \
+				     -offer_item_id $offer(offer_item_id) \
+				     -item_units $offer(item_units) \
+				     -price_per_unit $offer(price_per_unit) \
+				     -rebate $offer(rebate) \
+				     -amount_total $offer(amount) \
+				     -sort_order $counter \
+				     -vat $offer(vat) ]
+	    
+	    # Append the offer_id so we can later on decide 
+	    # if the offer/project should be marked as billed.
+	    if {[lsearch $offer_ids $offer(offer_id)] < 0 } {
+		lappend offer_ids $offer(offer_id)
+	    }
+	}
+	
+	# add credit offer entry
+	if {$total_credit > 0.} {
+	    set vat_credit [format "%.2f" [expr $total_credit * $vat_percent / 100.]]
+	    db_1row get_credit_offer {}
+	    
+	    # add new offer item
+	    set offer_item_rev_id [iv::offer_item::new \
+				       -offer_id $credit_offer_rev_id \
+				       -title $title \
+				       -description $description \
+				       -comment "" \
+				       -item_nr $invoice_id \
+				       -item_units -$total_credit \
+				       -price_per_unit 1 \
+				       -rebate 0 \
+				       -sort_order $invoice_id \
+				       -vat $vat_credit]
+	    
+	    category::map_object -object_id $offer_item_rev_id $credit_category_id
+	}
+	
+    } on_error {
+	
+	lappend failed_project_ids $project_item_id
+	continue
+    }
+
+    ############ PDF Generation ################
+    
+    set locale [lang::user::site_wide_locale -user_id $recipient_id]
+    
+    if {$total_amount >= 0} {
+	
+	# send invoice
+	set invoice_title [lang::util::localize "#invoices.file_invoice#_${invoice_nr}.pdf" $locale]
+	set document_type invoice
+	
+    } elseif {[empty_string_p $parent_invoice_id]} {
+	
+	# send credit
+	set invoice_title [lang::util::localize "#invoices.file_invoice_credit#_${invoice_nr}.pdf" $locale]
+	set document_type credit
+    } else {
+	
+	# send cancellation
+	set invoice_title [lang::util::localize "#invoices.file_invoice_cancel#_${invoice_nr}.pdf" $locale]
+	set document_type cancel
+    }
+    
+    # substitute variables in invoice text
+    # and return the content of all necessary document files
+    # (opening, invoice/credit/cancellation, copy)
+    set documents [iv::invoice::parse_data -invoice_id $invoice_id -types $document_type -email_text ""]
+    
+    set file_title $invoice_title
+    
+    set document_file [lreplace $documents 0 0]
+    
+    # Import the PDF
+    if {![empty_string_p $document_file]} {
+	set file_size [file size $document_file]
+	
+	# We need to keep the file in the filesystem so we can later 
+	# join the files into one PDF for printout.
+	# Still the single PDF needs to be stored along with the invoice.
+	
+	util_unlist [contact::oo::import_oo_pdf -oo_file $document_file -printer_name "pdfconv" -title $file_title -parent_id $invoice_id -return_pdf_with_id] file_item_id file_mime_type file_name
+	
+	lappend files $file_name
+	
+	# an invoice has been generated. Now move it to the folder
+	
+	set root_folder_id [lindex [application_data_link::get_linked -from_object_id $customer_id -to_object_type content_folder] 0]
+	set invoice_folder_id [fs::get_folder -name "invoices_${root_folder_id}" -parent_id $root_folder_id]
+	if {[empty_string_p $invoice_folder_id]} {
+	    # use folder of party if no invoice-folder exists
+	    set invoice_folder_id $organization_id
+	}
+	
+	# move files to invoice_folder
+	application_data_link::new -this_object_id $invoice_id -target_object_id $file_item_id
+	db_dml set_publish_status_and_parent {}
+	db_dml set_context_id {}
+	
+	#  Finally mark invoice as "Billed" in the system.
+	
+	iv::invoice::set_status -invoice_id $invoice_id -status "billed"
+    }
+}
+    
+# foreach offer_id: check if there's an item that's not billed -> status new, else status billed
+foreach offer_id $offer_ids {
+    set unbilled_items [db_string check_offer_status {} -default 0]
+    
+    if {$unbilled_items == 0} {
+	# all offer items billed
+	set status billed
+    } else {
+	# there are still unbilled offer items
+	set status new
+    }
+    
+    db_dml set_status {}
+}
+
+# Now it is time to join the pdf together
+
+
+if {[exists_and_not_null files]} {
+    util_unlist [contact::oo::join_pdf -filenames $files -title "[_ invoices.invoices].pdf" -no_import] joined_mime_type joined_pdf
+    set file_p 1
+} else {
+    set file_p 0
+}
+
+# Deal with failed projects
+
+if {[exists_and_not_null failed_project_ids]} {
+    set failed_projects_html "[_ invoices.mass_invoice_error]<ul>"
+    foreach failed_project_id $failed_project_ids {
+	append failed_projects_html "<li><a href=\"[export_vars -base "[apm_package_url_from_id $package_id]/invoice-ae" -url {{project_id $failed_project_id}}]\">[pm::project::name -project_item_id $failed_project_id]</a></li>"
+    }
+    append failed_projects_html "</ul>"
+} else {
+    set failed_projects_html ""
+}
+
+# and send out the e-mail
+
+if {$file_p} {
+    acs_mail_lite::complex_send \
+	-from_addr [party::email -party_id $user_id] \
+	-to_party_ids "$user_id" \
+	-subject [_ invoices.mass_invoice_email_subject] \
+	-body [_ invoices.mass_invoice_email_body] \
+	-files [list [list "[_ invoices.invoices].pdf" "$joined_mime_type" "$joined_pdf"]] \
+	-mime_type "text/html"
+} else {
+    acs_mail_lite::complex_send \
+	-from_addr [party::email -party_id $user_id] \
+	-to_party_ids "$user_id" \
+	-subject [_ invoices.mass_invoice_email_subject] \
+	-body [_ invoices.mass_invoice_email_body] \
+	-mime_type "text/html" 
+}
\ No newline at end of file
Index: openacs-4/packages/invoices/www/mass-invoice.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/invoices/www/mass-invoice.xql,v
diff -u
--- /dev/null	1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/invoices/www/mass-invoice.xql	18 Jun 2006 11:42:26 -0000	1.1
@@ -0,0 +1,285 @@
+<?xml version="1.0"?>
+<queryset>
+
+<fullquery name="contacts">
+      <querytext>
+
+    select contact_id, recipient_id, customer_id
+    from pm_projects pj, cr_items i
+    where i.item_id = :project_item_id
+    and i.latest_revision = pj.project_id
+
+      </querytext>
+</fullquery>
+
+<fullquery name="offer_data">
+      <querytext>
+
+	select payment_days,
+	       vat_percent,
+	       vat,
+	       amount_total,
+	       amount_sum
+	from iv_offers o, cr_items i, acs_data_links r
+	where o.offer_id = i.latest_revision
+	and r.object_id_one = :project_item_id
+	and r.object_id_two = i.item_id
+
+      </querytext>
+</fullquery>
+
+<fullquery name="offer_items">
+      <querytext>
+
+    select cr.title, cr.description, ofi.offer_item_id, ofi.item_units, ofi.offer_id,
+           ofi.price_per_unit, ofi.item_nr, pi.item_id as project_id, of.credit_percent,
+           pr.title as project_title, ofi.vat, ofi.rebate, m.category_id, oi.item_id as offer_cr_item_id
+    from cr_items oi, cr_revisions cr, cr_items pi, cr_revisions pr,
+         acs_objects o, acs_data_links r, iv_offers of, iv_offer_items ofi
+    left outer join category_object_map m on (m.object_id = ofi.offer_item_id)
+    where o.object_id = ofi.offer_id
+    and o.package_id = :package_id
+    and oi.latest_revision = ofi.offer_id
+    and r.object_id_one = pi.item_id
+    and r.object_id_two = oi.item_id
+    and pr.revision_id = pi.latest_revision
+    and pi.item_id = :project_item_id
+    and cr.revision_id = ofi.offer_item_id
+    and of.offer_id = ofi.offer_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')
+    order by pi.item_id, ofi.item_nr
+
+      </querytext>
+</fullquery>
+
+<fullquery name="set_invoice_nr">
+      <querytext>
+
+    update iv_invoices
+    set invoice_nr = :invoice_nr,
+        due_date = now()
+    where invoice_id = (select latest_revision from cr_items where item_id = :invoice_id)
+
+      </querytext>
+</fullquery>
+
+
+<fullquery name="set_publish_status_and_parent">
+      <querytext>
+
+    update cr_items
+    set publish_status = 'live',
+        parent_id = :invoice_folder_id
+    where item_id = :file_item_id
+
+      </querytext>
+</fullquery>
+
+<fullquery name="set_context_id">
+      <querytext>
+
+    update acs_objects
+    set context_id = :invoice_folder_id
+    where object_id = :file_item_id
+
+      </querytext>
+</fullquery>
+
+<fullquery name="check_cancelled_invoice">
+      <querytext>
+
+	select cri.item_id as cancel_id, r.title as cancel_title
+	from iv_invoices ci, cr_items cri, cr_revisions r
+	where cri.latest_revision = ci.invoice_id
+	and ci.invoice_id = :parent_invoice_id
+	and r.revision_id = ci.invoice_id
+
+      </querytext>
+</fullquery>
+
+<fullquery name="check_cancellation">
+      <querytext>
+
+    select ii.item_id as cancel_id, r.title as cancel_title
+    from iv_invoices ci, cr_items cri, iv_invoices i, cr_items ii, cr_revisions r
+    where cri.latest_revision = ci.invoice_id
+    and ci.invoice_id = i.parent_invoice_id
+    and i.invoice_id = ii.latest_revision
+    and cri.item_id = :invoice_id
+    and r.revision_id = i.invoice_id
+
+      </querytext>
+</fullquery>
+
+<fullquery name="not_invoiceable_subprojects">
+      <querytext>
+
+		select io.item_id as offer_id, ro.title as offer_title
+		from iv_offers o, cr_items io, cr_revisions ro, acs_data_links r, cr_items ip, pm_projects p
+		where o.offer_id = ro.revision_id
+		and ro.revision_id = io.latest_revision
+		and r.object_id_one = ip.item_id
+		and r.object_id_two = io.item_id
+		and ip.item_id in ([join $subprojects ,])
+		and p.project_id = ip.latest_revision
+		and p.invoice_p = false
+
+      </querytext>
+</fullquery>
+
+
+<fullquery name="invoice_items">
+      <querytext>
+
+	select ir.title, ir.description, ir.item_id as iv_item_id,
+               i.item_units, i.price_per_unit, i.item_nr, ofi.offer_id,
+               pi.item_id as project_id, pr.title as project_title,
+               i.vat as old_vat, i.rebate, m.category_id, i.offer_item_id,
+               of.credit_percent, oi.item_id as offer_cr_item_id
+	from cr_items oi, iv_invoice_items i, cr_revisions ir, cr_items pi,
+	     cr_revisions pr, iv_offers of, cr_items vi, cr_items ii, acs_data_links r,
+	     pm_projects p, iv_offer_items ofi
+	left outer join category_object_map m on (m.object_id = ofi.offer_item_id)
+	where oi.latest_revision = ofi.offer_id
+	and i.offer_item_id = ofi.offer_item_id
+	and i.iv_item_id = ir.revision_id
+	and ir.revision_id = ii.latest_revision
+	and i.invoice_id = vi.latest_revision
+	and vi.item_id = :invoice_id
+        and r.object_id_one = pi.item_id
+        and r.object_id_two = oi.item_id
+	and pr.revision_id = pi.latest_revision
+	and p.project_id = pr.revision_id
+        and of.offer_id = ofi.offer_id
+	order by pi.item_id, i.item_nr
+
+      </querytext>
+</fullquery>
+
+<fullquery name="project_titles">
+      <querytext>
+
+	select r.title
+	from cr_revisions r, cr_items i
+	where r.revision_id = i.latest_revision
+	and i.item_id in ([join $project_id ,])
+
+      </querytext>
+</fullquery>
+
+<fullquery name="get_data">
+      <querytext>
+
+	select t.invoice_id as invoice_rev_id, r.title, r.description,
+	       t.invoice_nr, t.parent_invoice_id, t.total_amount,
+	       t.paid_amount, t.payment_days, t.vat, t.vat_percent,
+	       to_char(t.due_date, :date_format) as due_date, t.amount_sum,
+	       o.creation_user, p.first_names, p.last_name, t.recipient_id,
+	       to_char(o.creation_date, :timestamp_format) as creation_date,
+	       t.contact_id
+	from iv_invoices t, cr_revisions r, cr_items i, acs_objects o,
+	     persons p
+	where r.revision_id = t.invoice_id
+	and i.latest_revision = r.revision_id
+	and i.item_id = :invoice_id
+	and o.object_id = t.invoice_id
+	and p.person_id = o.creation_user
+
+      </querytext>
+</fullquery>
+
+<fullquery name="check_offer_status">
+      <querytext>
+
+		select count(*) as unbilled_items
+		from iv_offers o, iv_offer_items ofi
+		where o.offer_id = :offer_id
+		and ofi.offer_id = o.offer_id
+		and not exists (select 1
+				from iv_invoice_items ii
+				where ii.offer_item_id = ofi.offer_item_id)
+		group by o.offer_id
+
+      </querytext>
+</fullquery>
+
+<fullquery name="get_credit_offer">
+      <querytext>
+
+		select of.offer_id as credit_offer_rev_id
+		from iv_offers of, cr_items oi, acs_data_links r,
+		     acs_objects o, pm_projects p, cr_items pi
+		where r.object_id_one = pi.item_id
+		and r.object_id_two = oi.item_id
+		and oi.latest_revision = of.offer_id
+		and of.status = 'credit'
+		and o.object_id = of.offer_id
+		and o.package_id = :package_id
+		and pi.latest_revision = p.project_id
+		and p.status_id = 2
+		and p.customer_id = :organization_id
+
+      </querytext>
+</fullquery>
+
+<fullquery name="get_credit_offer_item">
+      <querytext>
+
+		select ofi.offer_id as credit_offer_rev_id,
+                       oi.item_id as credit_offer_item_id
+		from iv_offers of, cr_items i, acs_objects o, acs_data_links r,
+                     iv_offer_items ofi, cr_items oi, pm_projects p, cr_items pi
+		where r.object_id_one = pi.item_id
+		and r.object_id_two = i.item_id
+		and o.object_id = of.offer_id
+		and o.package_id = :package_id
+		and of.offer_id = ofi.offer_id
+		and of.status = 'credit'
+		and i.latest_revision = of.offer_id
+		and oi.latest_revision = ofi.offer_item_id
+		and ofi.item_nr = :invoice_id
+		and pi.latest_revision = p.project_id
+		and p.status_id = 2
+		and p.customer_id = :organization_id
+
+      </querytext>
+</fullquery>
+
+<fullquery name="set_status">
+      <querytext>
+		update iv_offers
+		set status = :status
+		where offer_id = :offer_id
+		and status <> 'credit'
+      </querytext>
+</fullquery>
+
+<fullquery name="iv::invoice::set_status.update_status">
+      <querytext>
+
+	update iv_invoices
+	set status = :status
+	where invoice_id = (select latest_revision
+                          from cr_items
+                          where item_id = :invoice_id)
+
+      </querytext>
+</fullquery>
+
+<fullquery name="get_organizations">
+    <querytext>
+	select 
+		distinct customer_id
+	from 
+		pm_projectsx 
+	where 
+		item_id in ([join $project_id ","])
+    </querytext>
+</fullquery>
+
+</queryset>