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.33 -r1.34
--- openacs-4/packages/invoices/catalog/invoices.en_US.ISO-8859-1.xml	10 Apr 2006 15:59:49 -0000	1.33
+++ openacs-4/packages/invoices/catalog/invoices.en_US.ISO-8859-1.xml	13 Apr 2006 12:15:30 -0000	1.34
@@ -47,6 +47,7 @@
   <msg key="Currency">Currency</msg>
   <msg key="Currency_paid">Currency paid</msg>
   <msg key="Customer">Customer</msg>
+  <msg key="Customer_Invoices_Reports">Customer invoices report</msg>
   <msg key="Date_Range">Date Range:</msg>
   <msg key="day">Day</msg>
   <msg key="Delete">Delete</msg>
@@ -75,6 +76,7 @@
   <msg key="Group_by">Group By</msg>
   <msg key="Invoice">Invoice</msg>
   <msg key="Invoice_Cancel">Cancel</msg>
+  <msg key="Invoice_count">Invoice count</msg>
   <msg key="Invoice_Item">Invoice Item</msg>
   <msg key="Invoice_item_number">Invoice item number</msg>
   <msg key="Invoice_Item_title">Invoice Item Title</msg>
Index: openacs-4/packages/invoices/lib/report-customer.adp
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/invoices/lib/report-customer.adp,v
diff -u
--- /dev/null	1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/invoices/lib/report-customer.adp	13 Apr 2006 12:15:30 -0000	1.1
@@ -0,0 +1,2 @@
+<listfilters name="reports" style="select-menu"></listfilters>
+<listtemplate name="reports"></listtemplate>
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
--- /dev/null	1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/invoices/lib/report-customer.tcl	13 Apr 2006 12:15:30 -0000	1.1
@@ -0,0 +1,102 @@
+set optional_param_list [list]
+set optional_unset_list [list country_code]
+
+foreach optional_unset $optional_unset_list {
+    if {[info exists $optional_unset]} {
+        if {[empty_string_p [set $optional_unset]]} {
+            unset $optional_unset
+        }
+    }
+}
+
+foreach optional_param $optional_param_list {
+    if {![info exists $optional_param]} {
+	set $optional_param {}
+    }
+}
+
+
+set return_url [ad_return_url]
+set postal_attribute_id [attribute::id -object_type "organization" -attribute_name "company_address"]
+
+set start_date_sql ""
+if { $start_date != "YYYY-MM-DD" } {
+    # Get all customer invoices starting with start_date
+    set start_date_sql "ao.creation_date > to_timestamp(:start_date, 'YYYY-MM-DD')"
+}
+
+set end_date_sql ""
+if { $end_date != "YYYY-MM-DD" } {
+    # Get all customer invoices up to and including end_date
+    set end_date_sql "ao.creation_date < to_timestamp(:end_date, 'YYYY-MM-DD') + interval '1 day'"
+}
+
+set country_where_clause ""
+set sql_query_name all_customer_orders
+if { [exists_and_not_null country_code] } {
+    set country_where_clause "p.country_code in ('[join $country_code "', '"]')"
+    set sql_query_name all_customer_orders_of_country
+}
+
+set country_options [util::address::country_options]
+
+template::list::create \
+    -name reports \
+    -multirow reports \
+    -filters {
+	year {}
+	month {}
+	day {}
+    } -elements {
+	customer_name {
+	    label {[_ invoices.Customer]}
+	    link_url_col customer_url
+	    aggregate count
+	    aggregate_label "[_ invoices.Total]:"
+	}
+	amount_total {
+	    label "[_ invoices.Amount_total]"
+	    aggregate sum
+	    aggregate_label "[_ invoices.Total]:"
+	}
+	invoice_count {
+	    label "[_ invoices.Invoice_count]"
+	}
+    } -orderby {
+	default_value amount_total
+        customer_name {
+	    label {[_ invoices.Customer]}
+	    orderby {lower(oo.name)}
+	    default_direction asc
+        }
+        amount_total {
+	    label "[_ invoices.Amount_total]"
+	    orderby {amount_total}
+	    default_direction desc
+        }
+        invoice_count {
+	    label "[_ invoices.Invoice_count]"
+	    orderby {invoice_count}
+	    default_direction desc
+        }
+    } -filters {
+	country_code {
+	    label "[_ ams.country]"
+	    type multival
+	    values $country_options
+	    where_clause $country_where_clause
+	}
+	start_date {
+	    where_clause $start_date_sql
+	}
+	end_date {
+	    where_clause $end_date_sql
+	}
+    }
+
+
+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"
+}
Index: openacs-4/packages/invoices/lib/report-customer.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/invoices/lib/report-customer.xql,v
diff -u
--- /dev/null	1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/invoices/lib/report-customer.xql	13 Apr 2006 12:15:30 -0000	1.1
@@ -0,0 +1,39 @@
+<?xml version="1.0"?>
+<queryset>
+
+<fullquery name="all_customer_orders">
+    <querytext>
+    select oo.organization_id as customer_id, oo.name as customer_name, sum(i.total_amount) as amount_total,
+           count(*) as invoice_count
+    from organizations oo, iv_invoices i, cr_items ci, acs_objects ao
+    where i.organization_id = oo.organization_id
+    and ao.object_id = ci.item_id
+    and i.invoice_id = ci.latest_revision
+    and i.cancelled_p = 'f'
+    [template::list::filter_where_clauses -and -name "reports"]
+    group by oo.organization_id, oo.name
+    [template::list::orderby_clause -name reports -orderby]
+    </querytext>
+</fullquery>
+
+<fullquery name="all_customer_orders_of_country">
+    <querytext>
+    select oo.organization_id as customer_id, oo.name as customer_name, sum(i.total_amount) as amount_total,
+           count(*) as invoice_count
+    from organizations oo, iv_invoices i, cr_items ci, acs_objects ao, postal_addresses p,
+         ams_attribute_values av, cr_items ooi
+    where i.organization_id = oo.organization_id
+    and ao.object_id = ci.item_id
+    and i.invoice_id = ci.latest_revision
+    and i.cancelled_p = 'f'
+    and ooi.item_id = oo.organization_id
+    and av.object_id = ooi.latest_revision
+    and av.attribute_id = :postal_attribute_id
+    and p.address_id = av.value_id
+    [template::list::filter_where_clauses -and -name "reports"]
+    group by oo.organization_id, oo.name
+    [template::list::orderby_clause -name reports -orderby]
+    </querytext>
+</fullquery>
+
+</queryset>
Index: openacs-4/packages/invoices/www/index.adp
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/invoices/www/index.adp,v
diff -u -r1.6 -r1.7
--- openacs-4/packages/invoices/www/index.adp	10 Apr 2006 15:58:36 -0000	1.6
+++ openacs-4/packages/invoices/www/index.adp	13 Apr 2006 12:15:30 -0000	1.7
@@ -17,5 +17,6 @@
 <li><a href="offer-items-reports">#invoices.Offer_Items_Reports#</a></li>
 <li><a href="invoice-items-reports">#invoices.Invoice_Items_Reports#</a></li>
 <li><a href="offer-first-reports">#invoices.First_Order_Reports#</a></li>
+<li><a href="invoice-customer-reports">#invoices.Customer_Invoices_Reports#</a></li>
 <li><a href="@fs_folder_url@">#invoices.iv_journal#</a></li>
 </ul>
Index: openacs-4/packages/invoices/www/invoice-customer-reports.adp
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/invoices/www/invoice-customer-reports.adp,v
diff -u
--- /dev/null	1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/invoices/www/invoice-customer-reports.adp	13 Apr 2006 12:15:30 -0000	1.1
@@ -0,0 +1,32 @@
+<master>
+<property name="title">@page_title;noquote@</property>
+<property name="context">@context;noquote@</property>
+
+<table width="100%">
+<tr>
+<td>
+<form action="@current_url@">
+  @export_vars;noquote@
+  <table>
+    <tr>
+      <td>#invoices.iv_invoice_start_date# <input type=text name="start_date" size=12 value="@start_date@"></td>
+      <td>#invoices.iv_invoice_end_date# <input type=text name="end_date" size=12 value="@end_date@"</td>
+      <td><input type=submit name="submit" value="#invoices.ok#"></td>
+    </tr>
+    <if @clear_p@ eq 1>
+      <tr>
+        <td><a href="@clear_link@">#invoices.clear#</a></td>
+      </tr>
+    </if>
+  </table>
+</form>
+</td>
+
+<include src="/packages/invoices/lib/report-customer"
+	portlet_title="@page_title@"
+	base_url="@base_url@"
+	start_date="@start_date@"
+	end_date="@end_date@"
+        orderby="@orderby@"
+        country_code="@country_code@"
+/>
Index: openacs-4/packages/invoices/www/invoice-customer-reports.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/invoices/www/invoice-customer-reports.tcl,v
diff -u
--- /dev/null	1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/invoices/www/invoice-customer-reports.tcl	13 Apr 2006 12:15:30 -0000	1.1
@@ -0,0 +1,26 @@
+ad_page_contract {
+    Generates reports about the customers and their billed amount
+    by year, month and day
+
+    @author Timo Hentschel (timo@timohentschel.de)
+} {
+    {start_date "YYYY-MM-DD"}
+    {end_date "YYYY-MM-DD"}
+    {orderby ""}
+    {country_code:multiple ""}
+}
+
+set page_title "[_ invoices.Customer_Invoices_Reports]"
+set context [list $page_title]
+
+set base_url [ad_conn url]
+set current_url [ad_conn url]
+set clear_link [export_vars -base $current_url {page orderby organization_id}]
+set export_vars [export_vars -form {page orderby organization_id}]
+
+set clear_p 1
+if {$start_date == "YYYY-MM-DD" && $end_date == "YYYY-MM-DD"} {
+    set clear_p 0
+}
+
+ad_return_template