Index: openacs-4/packages/invoices/invoices.info
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/invoices/invoices.info,v
diff -u -r1.12 -r1.13
--- openacs-4/packages/invoices/invoices.info 7 Nov 2005 15:54:30 -0000 1.12
+++ openacs-4/packages/invoices/invoices.info 7 Nov 2005 23:10:50 -0000 1.13
@@ -7,15 +7,15 @@
<initial-install-p>f</initial-install-p>
<singleton-p>t</singleton-p>
- <version name="0.01d16" url="http://openacs.org/repository/download/apm/invoices-0.01d16.apm">
+ <version name="0.01d17" url="http://openacs.org/repository/download/apm/invoices-0.01d17.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-07</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="0.01d16"/>
+ <provides url="invoices" version="0.01d17"/>
<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.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.14 -r1.15
--- openacs-4/packages/invoices/catalog/invoices.en_US.ISO-8859-1.xml 7 Nov 2005 15:53:19 -0000 1.14
+++ openacs-4/packages/invoices/catalog/invoices.en_US.ISO-8859-1.xml 7 Nov 2005 23:10:51 -0000 1.15
@@ -1,5 +1,5 @@
<?xml version="1.0" encoding="ISO-8859-1"?>
-<message_catalog package_key="invoices" package_version="0.01d15" locale="en_US" charset="ISO-8859-1">
+<message_catalog package_key="invoices" package_version="0.01d16" locale="en_US" charset="ISO-8859-1">
<msg key="Accepted_date">Accepted on</msg>
<msg key="Add_offer_project">Add offer project</msg>
@@ -10,15 +10,19 @@
<msg key="Amount_paid">Amount paid</msg>
<msg key="Amount_sum">Amount sum</msg>
<msg key="Amount_total">Total Amount</msg>
+ <msg key="and">and</msg>
<msg key="Apply_VAT">Apply VAT?</msg>
<msg key="back">Back</msg>
<msg key="back_to_project">Back to project</msg>
<msg key="back_to_year">Back to %year%</msg>
<msg key="back_to_years">Back to Years</msg>
+ <msg key="Between">Between</msg>
<msg key="Billable_Projects">Billable Projects</msg>
<msg key="Cancel">Cancel</msg>
<msg key="cancel_and_return">Cancel and Return</msg>
<msg key="Cancelled">Cancelled?</msg>
+ <msg key="Category">Category</msg>
+ <msg key="clear">clear</msg>
<msg key="Client_Account_Man">Client Account Manager</msg>
<msg key="continue_with_accept">Continue with Offer Acceptance</msg>
<msg key="continue_with_delete">Continue with Delete</msg>
@@ -28,25 +32,42 @@
<msg key="Cost_number">Cost number</msg>
<msg key="Costs">Costs</msg>
<msg key="count">Count</msg>
+ <msg key="Creation_Date">Creation Date</msg>
+ <msg key="credit_offer_desc">Credit awarded by projects</msg>
+ <msg key="credit_offer_title">Credit</msg>
+ <msg key="credit_project_desc">Empty project to collect credit from other projects</msg>
+ <msg key="credit_project_title">Credit</msg>
<msg key="CSV">CSV</msg>
<msg key="Currency">Currency</msg>
<msg key="Currency_paid">Currency paid</msg>
<msg key="Customer">Customer</msg>
+ <msg key="Date_Range">Date Range:</msg>
<msg key="day">Day</msg>
<msg key="Delete">Delete</msg>
<msg key="Due_date">Due date</msg>
<msg key="Edit">Edit</msg>
+ <msg key="email_pdf_template">Template</msg>
<msg key="File_count">Number of files</msg>
+ <msg key="Final_Amount">Final Amount</msg>
<msg key="Finish_date">Finish date</msg>
<msg key="Fixed_cost">Fixed cost?</msg>
+ <msg key="folder_iv_accepted">Accepted Offers</msg>
+ <msg key="folder_iv_invoice">Invoices</msg>
+ <msg key="folder_iv_offer">Offers</msg>
+ <msg key="Group_by">Group By</msg>
<msg key="Invoice">Invoice</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>
<msg key="Invoice_Items">Invoice Items</msg>
+ <msg key="Invoice_Items_Reports">Invoice Items Reports</msg>
<msg key="Invoice_number">Invoice number</msg>
<msg key="Invoice_reference">Invoice reference</msg>
+ <msg key="Invoice_Title">Invoice Title</msg>
<msg key="Invoices">Invoices</msg>
<msg key="invoices">Invoices</msg>
+ <msg key="Invoices_items">Invoices Items</msg>
+ <msg key="Item_Id">Item ID</msg>
<msg key="iv_cost_1">Cost</msg>
<msg key="iv_cost_2">Costs</msg>
<msg key="iv_cost_Add">Add Cost</msg>
@@ -162,6 +183,8 @@
<msg key="iv_offer_count_total">Total items</msg>
<msg key="iv_offer_creation_date">Created on</msg>
<msg key="iv_offer_creation_user">Created by</msg>
+ <msg key="iv_offer_credit_percent">Credit percent</msg>
+ <msg key="iv_offer_credit_sum">Credit sum</msg>
<msg key="iv_offer_currency">Currency</msg>
<msg key="iv_offer_date_comment">Date Comment:</msg>
<msg key="iv_offer_date_comment_help">Internal Notes explaining the deadline communicated to the client</msg>
@@ -260,6 +283,7 @@
<msg key="iv_price_list_1">Price List</msg>
<msg key="iv_price_list_2">Price Lists</msg>
<msg key="iv_price_list_Add">Add Price List</msg>
+ <msg key="iv_price_list_credit_percent">Credit percent</msg>
<msg key="iv_price_list_currency">Currency</msg>
<msg key="iv_price_list_Delete">Delete Price List</msg>
<msg key="iv_price_list_Description">Description</msg>
@@ -273,6 +297,8 @@
<msg key="iv_reports">Invoice Reports</msg>
<msg key="last_years">Last Years</msg>
<msg key="Link_instances">Link package instances</msg>
+ <msg key="Mapped">Mapped</msg>
+ <msg key="Month">Month</msg>
<msg key="month">Month</msg>
<msg key="New">New</msg>
<msg key="New_clients">New Clients</msg>
@@ -281,11 +307,15 @@
<msg key="Number_of_units">Number of units</msg>
<msg key="Offer">Cost Sheet</msg>
<msg key="Offer_Item">Offer Item</msg>
+ <msg key="Offer_Item_Id">Offer Item ID</msg>
<msg key="Offer_item_number">Offer item number</msg>
+ <msg key="Offer_Item_Title">Offer Item Title</msg>
<msg key="Offer_Items">Offer Items</msg>
<msg key="Offer_number">Offer number</msg>
+ <msg key="Offer_Title">Offer Title</msg>
<msg key="Offers">Cost Sheets</msg>
<msg key="offers">Cost Sheets</msg>
+ <msg key="Offers_items">Offers Items</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>
@@ -306,12 +336,16 @@
<msg key="Sort_order">Sort order</msg>
<msg key="Status">Status</msg>
<msg key="Table">Table</msg>
+ <msg key="template_invoice">Invoice</msg>
+ <msg key="template_invoice_cancel">Invoice cancellation</msg>
+ <msg key="template_invoice_credit">Credit</msg>
+ <msg key="template_offer">Offer</msg>
+ <msg key="template_offer_accepted">Accepted Offer</msg>
<msg key="Total_amount">Total amount</msg>
<msg key="units">h</msg>
<msg key="Unmounted">Unmounted</msg>
<msg key="VAT">%VAT</msg>
<msg key="VAT_amount">VAT amount</msg>
<msg key="year">Year</msg>
<msg key="yes">Yes</msg>
- <msg key="You_must_suplly_project_id">You must suplly at least one project id</msg>
</message_catalog>
Index: openacs-4/packages/invoices/lib/invoice-items-list-portlet.adp
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/invoices/lib/invoice-items-list-portlet.adp,v
diff -u -r1.2 -r1.3
--- openacs-4/packages/invoices/lib/invoice-items-list-portlet.adp 3 Nov 2005 22:49:12 -0000 1.2
+++ openacs-4/packages/invoices/lib/invoice-items-list-portlet.adp 7 Nov 2005 23:10:51 -0000 1.3
@@ -15,7 +15,8 @@
category_id="@category_id@"
filter_package_id="@filter_package_id@"
customer_id="@customer_id@"
- date_range="@date_range@"
+ date_range_start="@date_range_start@"
+ date_range_end="@date_range_end@"
project_status_id="@project_status_id@"
groupby="@groupby@"
/>
Index: openacs-4/packages/invoices/lib/invoice-items-list.adp
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/invoices/lib/invoice-items-list.adp,v
diff -u -r1.2 -r1.3
--- openacs-4/packages/invoices/lib/invoice-items-list.adp 3 Nov 2005 22:49:12 -0000 1.2
+++ openacs-4/packages/invoices/lib/invoice-items-list.adp 7 Nov 2005 23:10:51 -0000 1.3
@@ -5,14 +5,22 @@
<form>
<table>
<tr>
- <td valign="middle">
- <b>#invoices.Date_Range#</b>
- </td>
- <td>
- <input type="text" id="sel1" name="date_range" size="10">
- <a href="" onclick="return showCalendar('sel1', 'yyyy-mm-dd')">
+ <td valign="top">
+ <b>#invoices.Between#:</b>
+ </td>
+ <td>
+ <input type="text" id="sel1" name="date_range_start" size="10" value="@date_range_start@">
+ <a href="" onclick="return showCalendar('sel1', 'yy-mm-dd')">
+ <img src="resources/calendar.gif" border="0"></a><br>
+ <small>[ yyyy-mm-dd ]</small>
+ </td>
+ <td valign="top">
+ <b>#invoices.and#</b>
+ </td>
+ <td>
+ <input type="text" id="sel2" name="date_range_end" size="10" value="@date_range_end@">
+ <a href="" onclick="return showCalendar('sel2', 'yy-mm-dd')">
<img src="resources/calendar.gif" border="0"></a>
-
<input type="submit" value="ok"><br>
<small>[ yyyy-mm-dd ]</small>
</td>
@@ -30,3 +38,5 @@
</if>
<listtemplate name="iv_items"></listtemplate>
+
+@aggregate_amount;noquote@
\ No newline at end of file
Index: openacs-4/packages/invoices/lib/invoice-items-list.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/invoices/lib/invoice-items-list.tcl,v
diff -u -r1.4 -r1.5
--- openacs-4/packages/invoices/lib/invoice-items-list.tcl 4 Nov 2005 15:15:05 -0000 1.4
+++ openacs-4/packages/invoices/lib/invoice-items-list.tcl 7 Nov 2005 23:10:51 -0000 1.5
@@ -1,6 +1,6 @@
-set optional_param_list [list elements category_filter_clause]
+set optional_param_list [list elements category_filter_clause date_range_start date_range_end]
set optional_unset_list [list iv_items_orderby category_id \
- customer_id filter_package_id date_range \
+ customer_id filter_package_id \
project_status_id groupby]
foreach optional_unset $optional_unset_list {
@@ -17,10 +17,25 @@
}
}
-if { [exists_and_not_null date_range] } {
- catch { set date_range [lc_time_fmt $date_range %y-%m-%d] } errMsg
+set date_range_clause ""
+
+if { [exists_and_not_null date_range_start] } {
+ set date_range 1
+ catch { set date_range_start [lc_time_fmt $date_range_start %y-%m-%d] } errMsg
+ append date_range_clause "to_char(oi.creation_date,'yy-mm-dd') >= :date_range_start"
}
+if { [exists_and_not_null date_range_end] } {
+ set date_range 1
+ catch { set date_range_end [lc_time_fmt $date_range_end %y-%m-%d] } errMsg
+
+ if { [exists_and_not_null date_range_start] } {
+ append date_range_clause " and to_char(oi.creation_date,'yy-mm-dd') <= :date_range_end"
+ } else {
+ append date_range_clause "to_char(oi.creation_date,'yy-mm-dd') <= :date_range_end"
+ }
+}
+
if {![info exist filters_p] } {
set filters_p 1
}
@@ -45,10 +60,6 @@
set return_url [ad_return_url]
}
-if { [exists_and_not_null category_id] } {
- set category_filter_clause "and com.category_id = $category_id"
-}
-
set categories_p 0
# Elements to construnct row_lists
@@ -69,36 +80,64 @@
# Create the elements for the list template
set elements [list]
+set categories_filter [list]
-# We are going to create the elements for each mapped categories
+# We are going to create the elements for each mapped category tree
if { $categories_p } {
- set categories [db_list_of_lists get_categories { }]
+ set categories_trees [db_list_of_lists get_category_trees { }]
set mapped_objects [list]
set multirow_extend [list]
+ set tree_ids [list]
- foreach category $categories {
- set cat_name [lindex $category 0]
- set cat_id [lindex $category 1]
-
- lappend multirow_extend category_$cat_id
+ foreach tree $categories_trees {
+ set tree_name [lindex $tree 0]
+ set tree_id [lindex $tree 1]
+
+ lappend tree_ids $tree_id
+ lappend multirow_extend tree_$tree_id
+
+ set label "$tree_name"
+
+ lappend elements tree_$tree_id [list label $label]
+ lappend row_list tree_$tree_id
+ lappend row_list [list]
+ }
+ set categories [db_list_of_lists get_categories " "]
+ foreach cat $categories {
+ lappend categories_filter [list [lang::util::localize [lindex $cat 0]] [lindex $cat 1]]
+ }
+}
+
+set aggregate_amount ""
+if { [exists_and_not_null groupby] } {
+ append aggregate_amount "<ul><table border=0><tr><td><b>Aggregate Amount:</b></td><td> </td></tr>"
+ foreach cat $categories_filter {
+ set c_name [lindex $cat 0]
+ set c_id [lindex $cat 1]
if { [exists_and_not_null category_id] } {
- set label "<a href=\"invoice-items?category_id=$cat_id\">$cat_name</a>"
- append label " <small>(<a href=\"invoice-items\">[_ invoices.clear]</a>)</small>"
+ if { [string equal $c_id $category_id] } {
+ append aggregate_amount "<tr><td><li>$c_name:</td>"
+ set amount [db_string get_amount { }]
+ append aggregate_amount "<td align=right>$amount</td>"
+ append aggregate_amount "</tr>"
+ }
} else {
- set label "<a href=\"invoice-items?category_id=$cat_id\">$cat_name</a>"
+ append aggregate_amount "<tr><td><li>$c_name:</td>"
+ set amount [db_string get_amount { }]
+ append aggregate_amount "<td align=right>$amount</td>"
+ append aggregate_amount "</tr>"
}
- lappend elements category_$cat_id [list label $label]
- lappend row_list category_$cat_id
- lappend row_list [list]
}
+append aggregate_amount "</ul>"
}
+
lappend elements item_title [list label "[_ invoices.Invoice_Item_title]"] \
final_amount [list label "[_ invoices.Final_Amount]"] \
invoice_title [list label "[_ invoices.Invoice_Title]" \
display_template {
- <a href=\"invoice-ae?mode=display&invoice_id=@iv_items.item_id@&organization_id=@iv_items.organization_id@\">@iv_items.invoice_title@</a>
+ <a href=\"invoice-ae?mode=display&invoice_id=@iv_items.item_id@&organization_id=@iv_items.organization_id@&project_id=@iv_items.project_item_id@\">@iv_items.invoice_title@</a>
} ] \
rebate [list label "[_ invoices.Rebate]" \
display_template {
@@ -111,16 +150,24 @@
month [list label ""]
-set filters [list category_id { } \
+set filters [list \
+ category_id {
+ label "[_ invoices.Category]"
+ values $categories_filter
+ where_clause { com.category_id = :category_id }
+ } \
filter_package_id {
where_clause { ii.object_package_id = :filter_package_id }
} \
customer_id {
where_clause { iv.organization_id = :customer_id}
} \
date_range {
- where_clause { to_char(ii.creation_date,'yy-mm-dd') > :date_range }
- }]
+ where_clause "$date_range_clause"
+ } \
+ date_range_start { } \
+ date_range_end { } \
+ ]
if { [apm_package_installed_p "project-manager"] } {
lappend filters project_status_id {
@@ -136,9 +183,9 @@
set groupby_values {
- { "#invoices.Customer#" { { groupby organization_id } { orderby organization_id,desc } } }
- { "#invoices.Category#" { { groupby category_id } { orderby category_id,desc } } }
- { "#invoices.Month#" { { groupby month } { orderby time_stamp,desc } } }
+ { "#invoices.Customer#" { { groupby org_name } { iv_items_orderby org_name,asc } } }
+ { "#invoices.Category#" { { groupby cat_name } { iv_items_orderby cat_name,asc } } }
+ { "#invoices.Month#" { { groupby month } { iv_items_orderby month,asc } } }
}
@@ -161,6 +208,18 @@
orderby_desc { lower(iv.title) desc }
orderby_asc { lower(iv.title) asc }
}
+ org_name {
+ label { [_ invoices.Customer] }
+ orderby_asc { org.name asc }
+ }
+ cat_name {
+ label { [_ invoices.Category] }
+ orderby_asc { ob.title asc }
+ }
+ month {
+ label { [_ invoices.Month] }
+ orderby_asc { to_char(ii.creation_date,'mm') asc }
+ }
} \
-html {width 100%} \
-page_size $page_size \
@@ -182,12 +241,12 @@
}
# Elements to extend the multirow
-lappend multirow_extend final_amount
+lappend multirow_extend project_item_id
db_multirow -extend $multirow_extend iv_items iv_items { } {
- set final_amount [expr [expr $price_per_unit * $item_units] - [expr $rebate * $price_per_unit * $item_units]]
- if { $categories_p && [exists_and_not_null category_id]} {
- set category_$category_id "[_ invoices.Mapped]"
+ if { $categories_p && [exists_and_not_null cat_id]} {
+ set tree_id [category::get_tree $cat_id]
+ set tree_$tree_id "$cat_name"
}
set off_item_id [db_string get_offer_item_id { }]
Index: openacs-4/packages/invoices/lib/invoice-items-list.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/invoices/lib/invoice-items-list.xql,v
diff -u -r1.3 -r1.4
--- openacs-4/packages/invoices/lib/invoice-items-list.xql 4 Nov 2005 14:40:06 -0000 1.3
+++ openacs-4/packages/invoices/lib/invoice-items-list.xql 7 Nov 2005 23:10:51 -0000 1.4
@@ -7,25 +7,31 @@
ii.iv_item_id,
ii.title as item_title,
iv.title as invoice_title,
- ii.price_per_unit,
ii.rebate,
- ii.item_units,
- com.category_id,
+ ii.amount_total as final_amount,
+ com.category_id as cat_id,
iv.organization_id,
to_char(ii.creation_date,'yy-mm-dd') as creation_date,
to_char(ii.creation_date,'mm') as month,
ii.offer_item_id,
+ iv.organization_id,
+ org.name as org_name,
+ ob.title as cat_name,
i.item_id
from
iv_invoice_itemsx ii,
iv_invoicesx iv,
category_object_map com,
- cr_items i
+ cr_items i,
+ organizations org,
+ acs_objects ob
where
iv.item_id = i.item_id
and ii.invoice_id = i.latest_revision
and iv.invoice_id = ii.invoice_id
and com.object_id = ii.offer_item_id
+ and org.organization_id = iv.organization_id
+ and ob.object_id = com.category_id
$category_filter_clause
and [template::list::page_where_clause -name "iv_items"]
[template::list::filter_where_clauses -and -name "iv_items"]
@@ -36,32 +42,58 @@
<fullquery name="iv_items_paginated">
<querytext>
select
- iv_item_id,
- title as item_title,
- price_per_unit,
- rebate,
- item_units
+ ii.iv_item_id
from
- iv_invoice_itemsx
+ iv_invoice_itemsx ii,
+ iv_invoicesx iv,
+ category_object_map com,
+ cr_items i,
+ organizations org,
+ acs_objects ob
+ where
+ iv.item_id = i.item_id
+ and ii.invoice_id = i.latest_revision
+ and iv.invoice_id = ii.invoice_id
+ and com.object_id = ii.offer_item_id
+ and org.organization_id = iv.organization_id
+ and ob.object_id = com.category_id
+ $category_filter_clause
+ [template::list::filter_where_clauses -and -name "iv_items"]
+ [template::list::orderby_clause -orderby -name "iv_items"]
</querytext>
</fullquery>
+<fullquery name="get_category_trees">
+ <querytext>
+ select
+ distinct
+ o.title,
+ c.tree_id
+ from
+ category_object_map com,
+ iv_offer_items io,
+ acs_objects o,
+ categories c
+ where
+ com.object_id = io.offer_item_id
+ and com.category_id = c.category_id
+ and c.tree_id = o.object_id
+ order by
+ o.title asc
+ </querytext>
+</fullquery>
+
<fullquery name="get_categories">
<querytext>
- select
- distinct
- o.title,
- com.category_id
- from
- category_object_map com,
- iv_invoice_items ii,
- acs_objects o
- where
- com.object_id = ii.offer_item_id
- and com.category_id = o.object_id
- $category_filter_clause
- order by
- o.title asc
+ select
+ o.title,
+ c.category_id
+ from
+ categories c,
+ acs_objects o
+ where
+ o.object_id = c.category_id
+ and c.tree_id in ([template::util::tcl_to_sql_list $tree_ids])
</querytext>
</fullquery>
@@ -78,5 +110,24 @@
</querytext>
</fullquery>
+<fullquery name="get_amount">
+ <querytext>
+ select
+ sum(ii.amount_total)
+ from
+ iv_invoicesx iv,
+ iv_invoice_itemsx ii,
+ cr_items i,
+ category_object_map com
+ where
+ iv.item_id = i.item_id
+ and ii.invoice_id = i.latest_revision
+ and iv.invoice_id = ii.invoice_id
+ and ii.offer_item_id = com.object_id
+ and com.category_id = :c_id
+ </querytext>
+</fullquery>
+
+
</queryset>
Index: openacs-4/packages/invoices/lib/offer-items-list-portlet.adp
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/invoices/lib/offer-items-list-portlet.adp,v
diff -u -r1.2 -r1.3
--- openacs-4/packages/invoices/lib/offer-items-list-portlet.adp 3 Nov 2005 21:09:24 -0000 1.2
+++ openacs-4/packages/invoices/lib/offer-items-list-portlet.adp 7 Nov 2005 23:10:51 -0000 1.3
@@ -15,7 +15,8 @@
category_id="@category_id@"
filter_package_id="@filter_package_id@"
customer_id="@customer_id@"
- date_range="@date_range@"
+ date_range_start="@date_range_start@"
+ date_range_end="@date_range_end@"
project_status_id="@project_status_id@"
groupby="@groupby@"
/>
Index: openacs-4/packages/invoices/lib/offer-items-list.adp
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/invoices/lib/offer-items-list.adp,v
diff -u -r1.3 -r1.4
--- openacs-4/packages/invoices/lib/offer-items-list.adp 3 Nov 2005 22:49:12 -0000 1.3
+++ openacs-4/packages/invoices/lib/offer-items-list.adp 7 Nov 2005 23:10:51 -0000 1.4
@@ -3,16 +3,24 @@
<tr>
<td>
<form>
- <table>
+ <table border="0">
<tr>
- <td valign="middle">
- <b>#invoices.Date_Range#</b>
- </td>
- <td>
- <input type="text" id="sel1" name="date_range" size="10">
- <a href="" onclick="return showCalendar('sel1', 'yyyy-mm-dd')">
+ <td valign="top">
+ <b>#invoices.Between#:</b>
+ </td>
+ <td>
+ <input type="text" id="sel1" name="date_range_start" size="10" value="@date_range_start@">
+ <a href="" onclick="return showCalendar('sel1', 'yy-mm-dd')">
+ <img src="resources/calendar.gif" border="0"></a><br>
+ <small>[ yyyy-mm-dd ]</small>
+ </td>
+ <td valign="top">
+ <b>#invoices.and#</b>
+ </td>
+ <td>
+ <input type="text" id="sel2" name="date_range_end" size="10" value="@date_range_end@">
+ <a href="" onclick="return showCalendar('sel2', 'yy-mm-dd')">
<img src="resources/calendar.gif" border="0"></a>
-
<input type="submit" value="ok"><br>
<small>[ yyyy-mm-dd ]</small>
</td>
@@ -30,3 +38,5 @@
</if>
<listtemplate name="offer_items"></listtemplate>
+
+@aggregate_amount;noquote@
Index: openacs-4/packages/invoices/lib/offer-items-list.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/invoices/lib/offer-items-list.tcl,v
diff -u -r1.3 -r1.4
--- openacs-4/packages/invoices/lib/offer-items-list.tcl 3 Nov 2005 22:49:12 -0000 1.3
+++ openacs-4/packages/invoices/lib/offer-items-list.tcl 7 Nov 2005 23:10:51 -0000 1.4
@@ -1,6 +1,6 @@
-set optional_param_list [list elements category_filter_clause]
+set optional_param_list [list elements category_filter_clause date_range_start date_range_end]
set optional_unset_list [list offer_items_orderby category_id \
- customer_id filter_package_id date_range \
+ customer_id filter_package_id \
project_status_id groupby]
foreach optional_unset $optional_unset_list {
@@ -17,10 +17,25 @@
}
}
-if { [exists_and_not_null date_range] } {
- catch { set date_range [lc_time_fmt $date_range %y-%m-%d] } errMsg
+set date_range_clause ""
+
+if { [exists_and_not_null date_range_start] } {
+ set date_range 1
+ catch { set date_range_start [lc_time_fmt $date_range_start %y-%m-%d] } errMsg
+ append date_range_clause "to_char(oi.creation_date,'yy-mm-dd') >= :date_range_start"
}
+if { [exists_and_not_null date_range_end] } {
+ set date_range 1
+ catch { set date_range_end [lc_time_fmt $date_range_end %y-%m-%d] } errMsg
+
+ if { [exists_and_not_null date_range_start] } {
+ append date_range_clause " and to_char(oi.creation_date,'yy-mm-dd') <= :date_range_end"
+ } else {
+ append date_range_clause "to_char(oi.creation_date,'yy-mm-dd') <= :date_range_end"
+ }
+}
+
if {![info exist filters_p] } {
set filters_p 1
}
@@ -45,10 +60,6 @@
set return_url [ad_return_url]
}
-if { [exists_and_not_null category_id] } {
- set category_filter_clause "and com.category_id = $category_id"
-}
-
set categories_p 0
# Elements to construnct row_lists
@@ -68,31 +79,71 @@
# Create the elements for the list template
set elements [list]
+set categories_filter [list]
-# We are going to create the elements for each mapped categories
+# We are going to create the elements for each mapped category tree
if { $categories_p } {
- set categories [db_list_of_lists get_categories { }]
+ set categories_trees [db_list_of_lists get_category_trees { }]
set mapped_objects [list]
set multirow_extend [list]
-
- foreach category $categories {
- set cat_name [lindex $category 0]
- set cat_id [lindex $category 1]
+ set tree_ids [list]
+
+ foreach tree $categories_trees {
+ set tree_name [lindex $tree 0]
+ set tree_id [lindex $tree 1]
+
+ lappend tree_ids $tree_id
+ lappend multirow_extend tree_$tree_id
+
+ set label "$tree_name"
- lappend multirow_extend category_$cat_id
- if { [exists_and_not_null category_id] } {
- set label "<a href=\"offer-items?category_id=$cat_id\">$cat_name</a>"
- append label " <small>(<a href=\"offer-items\">[_ invoices.clear]</a>)</small>"
- } else {
- set label "<a href=\"offer-items?category_id=$cat_id\">$cat_name</a>"
- }
- lappend elements category_$cat_id [list label $label]
- lappend row_list category_$cat_id
+ lappend elements tree_$tree_id [list label $label]
+ lappend row_list tree_$tree_id
lappend row_list [list]
}
+ set categories [db_list_of_lists get_categories " "]
+ foreach cat $categories {
+ lappend categories_filter [list [lang::util::localize [lindex $cat 0]] [lindex $cat 1]]
+ }
}
+set aggregate_amount ""
+if { [exists_and_not_null groupby] } {
+ append aggregate_amount "<ul><table border=0><tr><td><b>Aggregate Amount:</b></td><td> </td></tr>"
+ foreach cat $categories_filter {
+ set c_name [lindex $cat 0]
+ set c_id [lindex $cat 1]
+ set amount_values [db_list_of_lists get_amount_values { }]
+ set total_amount "0.00"
+ foreach val $amount_values {
+ set ppu [lindex $val 0]
+ set iu [lindex $val 1]
+ set r [lindex $val 2]
+ set amount [expr $ppu * $iu]
+ if { [string equal $r "0.00"] } {
+ set amount [format %.2f $amount]
+ } else {
+ set amount [format %.2f [expr $amount - [expr [expr $r / 100] * $amount]]]
+ }
+ set total_amount [expr $total_amount + $amount]
+ }
+
+ if { [exists_and_not_null category_id] } {
+ if { [string equal $c_id $category_id] } {
+ append aggregate_amount "<tr><td><li>$c_name:</td>"
+ append aggregate_amount "<td align=right>$total_amount</td>"
+ append aggregate_amount "</tr>"
+ }
+ } else {
+ append aggregate_amount "<tr><td><li>$c_name:</td>"
+ append aggregate_amount "<td align=right>$total_amount</td>"
+ append aggregate_amount "</tr>"
+ }
+ }
+append aggregate_amount "</ul>"
+}
+
lappend elements item_title [list label "[_ invoices.Offer_Item_Title]"] \
final_amount [list label "[_ invoices.Final_Amount]"] \
offer_title [list label "[_ invoices.Offer_Title]" \
@@ -109,17 +160,24 @@
creation_date [list label "[_ invoices.Creation_Date]"] \
month [list label ""]
-
-set filters [list category_id { } \
+set filters [list \
+ category_id {
+ label "[_ invoices.Category]"
+ values $categories_filter
+ where_clause { com.category_id = :category_id }
+ } \
filter_package_id {
where_clause { oi.object_package_id = :filter_package_id }
} \
customer_id {
where_clause { o.organization_id = :customer_id}
} \
date_range {
- where_clause { to_char(oi.creation_date,'yy-mm-dd') > :date_range }
- }]
+ where_clause "$date_range_clause"
+ } \
+ date_range_start { } \
+ date_range_end { } \
+ ]
if { [apm_package_installed_p "project-manager"] } {
lappend filters project_status_id {
@@ -135,9 +193,9 @@
set groupby_values {
- { "#invoices.Customer#" { { groupby organization_id } { orderby organization_id,desc } } }
- { "#invoices.Category#" { { groupby category_id } { orderby cateogory_id,desc } } }
- { "#invoices.Month#" { { groupby month } { orderby time_stamp,desc } } }
+ { "#invoices.Customer#" { { groupby org_name } { offer_items_orderby org_name,asc } } }
+ { "#invoices.Category#" { { groupby cat_name } { offer_items_orderby cat_name,asc } } }
+ { "#invoices.Month#" { { groupby month } { offer_items_orderby month,asc } } }
}
@@ -160,6 +218,18 @@
orderby_desc { lower(o.title) desc }
orderby_asc { lower(o.title) asc }
}
+ org_name {
+ label { [_ invoices.Customer] }
+ orderby_asc { org.name asc }
+ }
+ cat_name {
+ label { [_ invoices.Category] }
+ orderby_asc { ob.title asc }
+ }
+ month {
+ label { [_ invoices.Month] }
+ orderby_asc { to_char(oi.creation_date,'mm') asc }
+ }
} \
-html {width 100%} \
-page_size $page_size \
@@ -170,8 +240,7 @@
label "[_ invoices.Group_by]:"
type multivar
values $groupby_values
- } \
- -filters $filters \
+ } -filters $filters \
-formats {
normal {
label "[_ invoices.Table]"
@@ -184,10 +253,16 @@
lappend multirow_extend final_amount
db_multirow -extend $multirow_extend offer_items offer_items { } {
- set final_amount [expr [expr $price_per_unit * $item_units] - [expr $rebate * $price_per_unit * $item_units]]
- if { $categories_p } {
- set category_$category_id "[_ invoices.Mapped]"
+ set amount [expr $price_per_unit * $item_units]
+ if { [string equal $rebate "0.00"] } {
+ set final_amount [format %.2f $amount]
+ } else {
+ set final_amount [format %.2f [expr $amount - [expr [expr $rebate / 100] * $amount]]]
}
+ if { $categories_p && [exists_and_not_null cat_id]} {
+ set tree_id [category::get_tree $cat_id]
+ set tree_$tree_id $cat_name
+ }
set project_item_id [lindex [application_data_link::get_linked -from_object_id $item_id -to_object_type content_item] 0]
if { $project_status_p } {
if { [exists_and_not_null project_item_id] } {
Index: openacs-4/packages/invoices/lib/offer-items-list.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/invoices/lib/offer-items-list.xql,v
diff -u -r1.2 -r1.3
--- openacs-4/packages/invoices/lib/offer-items-list.xql 3 Nov 2005 21:09:24 -0000 1.2
+++ openacs-4/packages/invoices/lib/offer-items-list.xql 7 Nov 2005 23:10:51 -0000 1.3
@@ -10,60 +10,112 @@
oi.price_per_unit,
oi.rebate,
oi.item_units,
- com.category_id,
+ com.category_id as cat_id,
o.organization_id,
+ org.name as org_name,
to_char(oi.creation_date,'yy-mm-dd') as creation_date,
to_char(oi.creation_date,'mm') as month,
- o.item_id
+ o.item_id,
+ ob.title as cat_name
from
iv_offer_itemsx oi,
iv_offersx o,
+ organizations org,
category_object_map com,
- cr_items i
+ cr_items i,
+ acs_objects ob
where
i.item_id = o.item_id
and o.offer_id = i.latest_revision
and o.offer_id = oi.offer_id
and com.object_id = oi.offer_item_id
+ and org.organization_id = o.organization_id
+ and com.category_id = ob.object_id
$category_filter_clause
and [template::list::page_where_clause -name "offer_items"]
- [template::list::filter_where_clauses -and -name "offer_items"]
+ [template::list::filter_where_clauses -and -name "offer_items"]
[template::list::orderby_clause -orderby -name "offer_items"]
</querytext>
</fullquery>
<fullquery name="offer_items_paginated">
<querytext>
select
- offer_item_id,
- title as item_title,
- revision_id as offer_title,
- price_per_unit,
- rebate,
- item_units
+ oi.offer_item_id
from
- iv_offer_itemsx
+ iv_offer_itemsx oi,
+ iv_offersx o,
+ organizations org,
+ category_object_map com,
+ cr_items i,
+ acs_objects ob
+ where
+ i.item_id = o.item_id
+ and o.offer_id = i.latest_revision
+ and o.offer_id = oi.offer_id
+ and com.object_id = oi.offer_item_id
+ and org.organization_id = o.organization_id
+ and com.category_id = ob.object_id
+ $category_filter_clause
+ [template::list::filter_where_clauses -and -name "offer_items"]
+ [template::list::orderby_clause -orderby -name "offer_items"]
</querytext>
</fullquery>
-<fullquery name="get_categories">
+<fullquery name="get_category_trees">
<querytext>
select
distinct
o.title,
- com.category_id
+ c.tree_id
from
category_object_map com,
iv_offer_items io,
- acs_objects o
+ acs_objects o,
+ categories c
where
com.object_id = io.offer_item_id
- and com.category_id = o.object_id
- $category_filter_clause
+ and com.category_id = c.category_id
+ and c.tree_id = o.object_id
order by
o.title asc
</querytext>
</fullquery>
+<fullquery name="get_categories">
+ <querytext>
+ select
+ o.title,
+ c.category_id
+ from
+ categories c,
+ acs_objects o
+ where
+ o.object_id = c.category_id
+ and c.tree_id in ([template::util::tcl_to_sql_list $tree_ids])
+ </querytext>
+</fullquery>
+
+<fullquery name="get_amount_values">
+ <querytext>
+ select
+ price_per_unit,
+ item_units,
+ rebate
+ from
+ iv_offer_itemsx oi,
+ category_object_map com,
+ iv_offersx o,
+ cr_items i
+
+ where
+ i.item_id = o.item_id
+ and o.offer_id = i.latest_revision
+ and o.offer_id = oi.offer_id
+ and com.object_id = oi.offer_item_id
+ and com.category_id = :c_id
+ </querytext>
+</fullquery>
+
</queryset>
Index: openacs-4/packages/invoices/tcl/invoice-procs.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/invoices/tcl/invoice-procs.tcl,v
diff -u -r1.7 -r1.8
--- openacs-4/packages/invoices/tcl/invoice-procs.tcl 14 Oct 2005 06:25:03 -0000 1.7
+++ openacs-4/packages/invoices/tcl/invoice-procs.tcl 7 Nov 2005 23:10:51 -0000 1.8
@@ -320,3 +320,154 @@
return $mail_text
}
+
+
+ad_proc -public iv::invoice::year_month_day_filter {
+ {-year ""}
+ {-month ""}
+ {-day ""}
+ {-last_years "5"}
+ {-extra_vars ""}
+ -base:required
+} {
+ Returns and html filter to use in any adp page for sort data according to date. Return
+ the variables year, month, day and any extra variable you recieved in extra_vars to the base
+ page (url).
+
+ @author Miguel Marin (miguelmarin@viaro.net)
+ @author Viaro Networks www.viaro.net
+
+ @param year The year to show.
+ @param month The month to show (depending on the month and year number of days shown are 31,30,29 and 28).
+ @param day The day to show.
+ @param last_years How many past years will be shown from the actual year.
+ @param extra_vars A list of extra vars to include in the links. They have to be of the form
+ [list [list var_name value] [list var_name value] ...].
+ @param base The page to redirect in the links.
+
+} {
+ set actual_year [string range [dt_sysdate] 0 3]
+ set html "<center><table><tr><td>"
+
+ for { set i $last_years } { $i > 0 } { set i [expr $i - 1] } {
+ set myear [expr $actual_year - $i]
+ set send_vars [list [list year $myear] month day last_years]
+ foreach var $extra_vars {
+ lappend send_vars $var
+ }
+ set url [export_vars -base $base $send_vars]
+ if { [string equal $year $myear] } {
+ append html "<b><a href=\"$url\">$myear</a></b>"
+ } else {
+ append html "<a href=\"$url\">$myear</a>"
+ }
+ append html " "
+ }
+
+ # We always look for 5 years from actual year
+ for { set i $actual_year } { $i < [expr $actual_year + 6] } { incr i} {
+ set send_vars [list [list year $i] month day last_years]
+ foreach var $extra_vars {
+ lappend send_vars $var
+ }
+ set url [export_vars -base $base $send_vars]
+ if { [string equal $year $i] } {
+ append html "<b><a href=\"$url\">$i</a></b>"
+ } else {
+ append html "<a href=\"$url\">$i</a>"
+ }
+ append html " "
+ }
+
+ if { [exists_and_not_null year] } {
+ set send_vars [list month day last_years]
+ foreach var $extra_vars {
+ lappend send_vars $var
+ }
+ set url [export_vars -base $base $send_vars]
+ append html "<small>(<a href=\"$url\">Clear</a>)</small>"
+ }
+
+ append html "</td></tr><tr><td>"
+
+ for { set i 1 } { $i < 13 } { incr i } {
+ set short_month [template::util::date::monthName $i short]
+ # Dates format has a 0 before the number for months that
+ # are lower than 10
+ if { $i < 10 } {
+ set m "0$i"
+ } else {
+ set m $i
+ }
+ set send_vars [list year [list month $m] day last_years]
+ foreach var $extra_vars {
+ lappend send_vars $var
+ }
+ set url [export_vars -base $base $send_vars]
+ if { [string equal $month $m] } {
+ append html "<b><a href=\"$url\">$short_month</a></b>"
+ } else {
+ append html "<a href=\"$url\">$short_month</a>"
+ }
+ append html " "
+ }
+
+ if { [exists_and_not_null month] } {
+ set send_vars [list year day last_years]
+ foreach var $extra_vars {
+ lappend send_vars $var
+ }
+ set url [export_vars -base $base $send_vars]
+ append html "<small>(<a href=\"$url\">Clear</a>)</small>"
+ }
+
+ append html "</td></tr><tr><td>"
+
+ # We figure out how many days we are going to show according to the month
+ set month_days 31
+ if { [exists_and_not_null month] } {
+ if { [string equal $month "04"] || [string equal $month "06"] || [string equal $month "09"] ||
+ [string equal $month "11"] } {
+ set month_days 30
+ } elseif {[string equal $month "02"] } {
+ if { [exists_and_not_null year] && [string equal [expr $year % 4] "0"] } {
+ set month_days 29
+ } else {
+ set month_days 28
+ }
+ }
+ }
+
+ for { set i 1 } { $i <= $month_days } { incr i } {
+ # Dates format has a 0 before the number for days that
+ # are lower than 10
+ if { $i < 10 } {
+ set d "0$i"
+ } else {
+ set d $i
+ }
+ set send_vars [list year month [list day $d] last_years]
+ foreach var $extra_vars {
+ lappend send_vars $var
+ }
+ set url [export_vars -base $base $send_vars]
+ if { [string equal $day $d] } {
+ append html "<b><a href=\"$url\">$d</a></b>"
+ } else {
+ append html "<a href=\"$url\">$d</a>"
+ }
+ append html " "
+ }
+
+ if { [exists_and_not_null day] } {
+ set send_vars [list year month last_years]
+ foreach var $extra_vars {
+ lappend send_vars $var
+ }
+ set url [export_vars -base $base $send_vars]
+ append html "<small>(<a href=\"$url\">Clear</a>)</small>"
+ }
+
+ append html "</td></tr></table></center>"
+ return $html
+}
\ No newline at end of file
Index: openacs-4/packages/invoices/www/invoice-items.adp
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/invoices/www/invoice-items.adp,v
diff -u -r1.2 -r1.3
--- openacs-4/packages/invoices/www/invoice-items.adp 3 Nov 2005 22:49:12 -0000 1.2
+++ openacs-4/packages/invoices/www/invoice-items.adp 7 Nov 2005 23:10:51 -0000 1.3
@@ -11,7 +11,8 @@
category_id="@category_id@"
filter_package_id="@filter_package_id@"
customer_id="@customer_id@"
- date_range="@date_range@"
+ date_range_start="@date_range_start@"
+ date_range_end="@date_range_end@"
project_status_id="@project_status_id@"
groupby="@groupby@"
/>
Index: openacs-4/packages/invoices/www/invoice-items.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/invoices/www/invoice-items.tcl,v
diff -u -r1.2 -r1.3
--- openacs-4/packages/invoices/www/invoice-items.tcl 3 Nov 2005 22:49:12 -0000 1.2
+++ openacs-4/packages/invoices/www/invoice-items.tcl 7 Nov 2005 23:10:51 -0000 1.3
@@ -11,7 +11,8 @@
{category_id ""}
{filter_package_id ""}
{customer_id ""}
- {date_range ""}
+ {date_range_start ""}
+ {date_range_end ""}
{project_status_id ""}
{groupby ""}
}
Index: openacs-4/packages/invoices/www/offer-items.adp
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/invoices/www/offer-items.adp,v
diff -u -r1.2 -r1.3
--- openacs-4/packages/invoices/www/offer-items.adp 3 Nov 2005 21:09:24 -0000 1.2
+++ openacs-4/packages/invoices/www/offer-items.adp 7 Nov 2005 23:10:51 -0000 1.3
@@ -11,7 +11,8 @@
category_id="@category_id@"
filter_package_id="@filter_package_id@"
customer_id="@customer_id@"
- date_range="@date_range@"
+ date_range_start="@date_range_start@"
+ date_range_end="@date_range_end@"
project_status_id="@project_status_id@"
groupby="@groupby@"
/>
Index: openacs-4/packages/invoices/www/offer-items.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/invoices/www/offer-items.tcl,v
diff -u -r1.2 -r1.3
--- openacs-4/packages/invoices/www/offer-items.tcl 3 Nov 2005 21:09:24 -0000 1.2
+++ openacs-4/packages/invoices/www/offer-items.tcl 7 Nov 2005 23:10:51 -0000 1.3
@@ -11,7 +11,8 @@
{category_id ""}
{filter_package_id ""}
{customer_id ""}
- {date_range ""}
+ {date_range_start ""}
+ {date_range_end ""}
{project_status_id ""}
{groupby ""}
}