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.32 -r1.33
--- openacs-4/packages/invoices/catalog/invoices.en_US.ISO-8859-1.xml 10 Apr 2006 06:21:57 -0000 1.32
+++ openacs-4/packages/invoices/catalog/invoices.en_US.ISO-8859-1.xml 10 Apr 2006 15:59:49 -0000 1.33
@@ -65,6 +65,9 @@
<msg key="file_offer_accepted">Order confirmation</msg>
<msg key="Final_Amount">Gross amount</msg>
<msg key="Finish_date">Deadline</msg>
+ <msg key="first_order_Amount_total">First order amount</msg>
+ <msg key="first_order_Creation_Date">First order at</msg>
+ <msg key="First_Order_Reports">Reports for first orders</msg>
<msg key="Fixed_cost">Fixed cost?</msg>
<msg key="folder_iv_accepted">Order confirmation</msg>
<msg key="folder_iv_invoice">Invoices</msg>
Index: openacs-4/packages/invoices/lib/report-first-order.adp
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/invoices/lib/report-first-order.adp,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/invoices/lib/report-first-order.adp 10 Apr 2006 15:58:36 -0000 1.1
@@ -0,0 +1,4 @@
+@date_filter;noquote@
+<br><br>
+
+<listtemplate name="reports"></listtemplate>
Index: openacs-4/packages/invoices/lib/report-first-order.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/invoices/lib/report-first-order.tcl,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/invoices/lib/report-first-order.tcl 10 Apr 2006 15:58:36 -0000 1.1
@@ -0,0 +1,93 @@
+set optional_param_list [list]
+set optional_unset_list [list category_f]
+
+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 {}
+ }
+}
+
+
+# Procedure that manages the date filter
+set date_filter [iv::invoice::year_month_day_filter \
+ -base $base_url \
+ -year $year \
+ -month $month \
+ -day $day \
+ -last_years $last_years \
+ -extra_vars ""]
+
+set return_url [ad_return_url]
+set extra_query ""
+
+if { [exists_and_not_null year] } {
+ # We get the projects for this year
+ append extra_query " and to_char(ao.creation_date, 'YYYY') = :year"
+}
+
+if { [exists_and_not_null month] } {
+ # We get the projects for this specific month
+ append extra_query " and to_char(ao.creation_date, 'MM') = :month"
+}
+
+if { [exists_and_not_null day] } {
+ # We get the projects for this specific day
+ append extra_query " and to_char(ao.creation_date, 'DD') = :day"
+}
+
+
+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]:"
+ }
+ creation_date {
+ label "[_ invoices.first_order_Creation_Date]"
+ }
+ amount_total {
+ label "[_ invoices.first_order_Amount_total]"
+ aggregate sum
+ aggregate_label "[_ invoices.Total]:"
+ }
+ } -orderby {
+ default_value creation_date
+ customer_name {
+ label {[_ invoices.Customer]}
+ orderby {lower(oo.name)}
+ default_direction asc
+ }
+ creation_date {
+ label "[_ invoices.first_order_Creation_Date]"
+ orderby {ao.creation_date}
+ default_direction desc
+ }
+ amount_total {
+ label "[_ invoices.first_order_Amount_total]"
+ orderby {o.amount_total}
+ default_direction desc
+ }
+ }
+
+
+set contacts_url [apm_package_url_from_key contacts]
+
+db_multirow -extend {customer_url} reports new_customer_with_orders {} {
+ set customer_url "${contacts_url}$customer_id"
+}
Index: openacs-4/packages/invoices/lib/report-first-order.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/invoices/lib/report-first-order.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/invoices/lib/report-first-order.xql 10 Apr 2006 15:58:36 -0000 1.1
@@ -0,0 +1,25 @@
+<?xml version="1.0"?>
+<queryset>
+
+<fullquery name="new_customer_with_orders">
+ <querytext>
+ select oo.organization_id as customer_id, oo.name as customer_name, o.amount_total,
+ to_char(ao.creation_date, 'YYYY-MM-DD') as creation_date
+ from organizations oo, iv_offers o, cr_items i, acs_objects ao,
+ (select min(o2.offer_id) as offer_id, o2.organization_id
+ from iv_offers o2, cr_items i2
+ where o2.offer_id = i2.latest_revision
+ and o2.accepted_date is not null
+ and o2.amount_total > 1
+ group by o2.organization_id) sub
+ where o.organization_id = oo.organization_id
+ and ao.object_id = i.item_id
+ and o.offer_id = i.latest_revision
+ and o.organization_id = sub.organization_id
+ and o.offer_id = sub.offer_id
+ $extra_query
+ [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.5 -r1.6
--- openacs-4/packages/invoices/www/index.adp 2 Apr 2006 22:08:02 -0000 1.5
+++ openacs-4/packages/invoices/www/index.adp 10 Apr 2006 15:58:36 -0000 1.6
@@ -16,5 +16,6 @@
<li><a href="offer-items">#invoices.Offers_items#</a></li>
<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="@fs_folder_url@">#invoices.iv_journal#</a></li>
</ul>
Index: openacs-4/packages/invoices/www/offer-first-reports.adp
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/invoices/www/offer-first-reports.adp,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/invoices/www/offer-first-reports.adp 10 Apr 2006 15:58:36 -0000 1.1
@@ -0,0 +1,14 @@
+<master>
+<property name="title">@page_title;noquote@</property>
+<property name="context">@context;noquote@</property>
+
+
+<include src="/packages/invoices/lib/report-first-order"
+ portlet_title="@page_title@"
+ base_url="@base_url@"
+ year="@year@"
+ month="@month@"
+ day="@day@"
+ last_years="@last_years@"
+ orderby="@orderby@"
+/>
Index: openacs-4/packages/invoices/www/offer-first-reports.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/invoices/www/offer-first-reports.tcl,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/invoices/www/offer-first-reports.tcl 10 Apr 2006 15:58:36 -0000 1.1
@@ -0,0 +1,19 @@
+ad_page_contract {
+ Generates reports about the customers who first ordered
+ by year, month and day
+
+ @author Timo Hentschel (timo@timohentschel.de)
+} {
+ {year ""}
+ {month ""}
+ {day ""}
+ {orderby ""}
+ {last_years "5"}
+}
+
+set page_title "[_ invoices.First_Order_Reports]"
+set context [list $page_title]
+
+set base_url [ad_conn url]
+
+ad_return_template