Index: openacs-4/packages/ecommerce/www/admin/orders/revenue.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/orders/revenue.adp,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/orders/revenue.adp 17 Aug 2008 22:42:25 -0000 1.1 @@ -0,0 +1,38 @@ + + @title;noquote@ + @context;noquote@ +

@title@

+ + + +@reportable_transactions_select_html;noquote@ + + +@money_select_html;noquote@ + + + +@gift_certificates_sales_html;noquote@ + + + +@gift_certificates_issued_html;noquote@ + + + +@gift_certificates_approved_html;noquote@ +
PeriodRevenue 1
 
PeriodProduct Sales 2
 
PeriodGift Certificate Sales +3
 
PeriodGift Certificates Issued +4
 
ExpiresGift Certificates Outstanding 5
+ +

+1 Revenue: the actual amount of credit card charges minus the amount of credit card refunds. +

+2 Sales: the price charged, the shipping charged, and the tax charged (minus the amounts refunded) for shipped items (most companies recognize revenue when items are shipped so that they don't risk double counting an account receivable and an item in inventory; see the ecommerce chapter of Philip & Alex's Guide to Web Publishing). Note that this is different from revenue because revenue includes sales of gift certificates. Additionally, some products were paid for using gift certificates. +

+3 Gift Certificate Sales: the amount of gift certificates purchased (recognized on date of purchase). +

+4 Gift Certificates Issued: the amount of gift certificates issued to customers free of charge by web site administrators. +

+5 Gift Certificates Outstanding: gift certificates which have not yet been applied to shipped items (therefore they are considered a liability). +

Index: openacs-4/packages/ecommerce/www/admin/orders/revenue.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/orders/revenue.tcl,v diff -u -r1.5 -r1.6 --- openacs-4/packages/ecommerce/www/admin/orders/revenue.tcl 9 Aug 2008 10:33:13 -0000 1.5 +++ openacs-4/packages/ecommerce/www/admin/orders/revenue.tcl 17 Aug 2008 22:42:25 -0000 1.6 @@ -13,36 +13,23 @@ ad_require_permission [ad_conn package_id] admin -doc_body_append "[ad_admin_header "Financial Reports"] +set title "Financial Reports" +set context [list [list index "Orders / Shipments / Refunds"] $title] -

Financial Reports

- -[ad_context_bar [list "../" "Ecommerce([ec_system_name])"] [list "index" "Orders"] "Financial Reports"] - -
- - - -" - set revenue_sum 0 +set reportable_transactions_select_html "" db_foreach reportable_transactions_select "select to_char(inserted_date,'YYYY') as transaction_year, to_char(inserted_date,'Q') as transaction_quarter, sum(decode(transaction_type,'charge',transaction_amount,-1*transaction_amount)) as revenue from ec_fin_transactions_reportable group by to_char(inserted_date,'YYYY'), to_char(inserted_date,'Q') order by to_char(inserted_date,'YYYY') || to_char(inserted_date,'Q')" { set revenue_sum [expr $revenue_sum + $revenue] - doc_body_append "\n" + append reportable_tranactions_select_html "\n" if { $transaction_quarter == "4" } { - doc_body_append "\n" - set revenue_sum 0 + append reportable_transactions_select_html "\n" + set revenue_sum 0 } } -doc_body_append " - - -" - # This slightly strange-looking query says: # Give me the total price, shipping, and tax charged for the items in # each shipment (that's what the view ec_items_money_view gives you). @@ -58,7 +45,7 @@ set price_sum 0 set shipping_sum 0 set tax_sum 0 - +set money_select_html "" db_foreach money_select "select to_char(shipment_date,'YYYY') as shipment_year, to_char(shipment_date,'Q') as shipment_quarter, nvl(sum(bal_price_charged),0) as total_price_charged, @@ -70,62 +57,47 @@ set price_sum [expr $price_sum + $total_price_charged] set shipping_sum [expr $shipping_sum + $total_shipping_charged] set tax_sum [expr $tax_sum + $total_tax_charged] - doc_body_append "\n" + append money_select_html "\n" if { $transaction_quarter == "4" } { - doc_body_append "\n" - set price_sum 0 - set shipping_sum 0 - set tax_sum 0 + append money_select_html "\n" + set price_sum 0 + set shipping_sum 0 + set tax_sum 0 } } -doc_body_append " - - -" - set amount_sum 0 - +set gift_certificates_sales_html "" db_foreach gift_certificates_select "select to_char(issue_date,'YYYY') as issue_year, to_char(issue_date,'Q') as issue_quarter, nvl(sum(amount),0) as amount from ec_gift_certificates where gift_certificate_state = 'authorized' group by to_char(issue_date,'YYYY'), to_char(issue_date,'Q') order by to_char(issue_date,'YYYY') || to_char(issue_date,'Q')" { set amount_sum [expr $amount_sum + $amount] - doc_body_append "\n" + append gift_certificates_sales_html "\n" if { $issue_quarter == "4" } { - doc_body_append "\n" - set amount_sum 0 + append gift_certificates_sales_html "\n" + set amount_sum 0 } } -doc_body_append " - - -" - set amount_sum 0 - +set gift_certificates_issued_html "" db_foreach gift_certificates_select "select to_char(issue_date,'YYYY') as issue_year, to_char(issue_date,'Q') as issue_quarter, nvl(sum(amount),0) as amount from ec_gift_certificates where gift_certificate_state = 'authorized' group by to_char(issue_date,'YYYY'), to_char(issue_date,'Q') order by to_char(issue_date,'YYYY') || to_char(issue_date,'Q')" { set amount_sum [expr $amount_sum + $amount] - doc_body_append "\n" + append gift_certificates_issued_html "\n" if { $issue_quarter == "4" } { - doc_body_append "\n" - set amount_sum 0 + append gift_certificates_issued_html "\n" + set amount_sum 0 } } -doc_body_append " - - -" - # Even if a gift certificate is tied to items that have been ordered, it is still # considered outstanding until it is actually applied to shipped items. @@ -140,34 +112,17 @@ # unable to be shipped) set amount_outstanding_sum 0 - +set gift_certificates_approved_html "" db_foreach gift_certificates_approved_select "select to_char(expires,'YYYY') as expires_year, to_char(expires,'Q') as expires_quarter, nvl(sum(gift_certificate_amount_left(gift_certificate_id)),0) + nvl(sum(ec_gift_cert_unshipped_amount(gift_certificate_id)),0) as amount_outstanding from ec_gift_certificates_approved group by to_char(expires,'YYYY'), to_char(expires,'Q') order by to_char(expires,'YYYY') || to_char(expires,'Q')" { set amount_outstanding_sum [expr $amount_outstanding_sum + $amount_outstanding] - doc_body_append "\n" + append gift_certificates_approved_html "\n" if { $expires_quarter == "4" } { - doc_body_append "\n" - set amount_sum 0 + append gift_certificates_approved_html "\n" + set amount_sum 0 } } - -doc_body_append "
PeriodRevenue 1
$transaction_year Q$transaction_quarter[ec_pretty_pure_price $revenue]
$transaction_year Q$transaction_quarter[ec_pretty_pure_price $revenue]
total for $transaction_year[ec_pretty_pure_price $revenue_sum]
total for $transaction_year[ec_pretty_pure_price $revenue_sum]
 
PeriodProduct Sales 2
$transaction_year Q$transaction_quarterPrice: [ec_pretty_pure_price $total_price_charged] | Shipping: [ec_pretty_pure_price $total_shipping_charged] | Tax [ec_pretty_pure_price $total_tax_charged]
$transaction_year Q$transaction_quarterPrice: [ec_pretty_pure_price $total_price_charged] | Shipping: [ec_pretty_pure_price $total_shipping_charged] | Tax [ec_pretty_pure_price $total_tax_charged]
total for $transaction_yearPrice: [ec_pretty_pure_price $price_sum] | Shipping [ec_pretty_pure_price $shipping_sum] | Tax [ec_pretty_pure_price $tax_sum]
total for $transaction_yearPrice: [ec_pretty_pure_price $price_sum] | Shipping [ec_pretty_pure_price $shipping_sum] | Tax [ec_pretty_pure_price $tax_sum]
 
PeriodGift Certificate Sales 3
$issue_year Q$issue_quarter[ec_pretty_pure_price $amount]
$issue_year Q$issue_quarter[ec_pretty_pure_price $amount]
total for $issue_year[ec_pretty_pure_price $amount_sum]
total for $issue_year[ec_pretty_pure_price $amount_sum]
 
PeriodGift Certificates Issued 4
$issue_year Q$issue_quarter[ec_pretty_pure_price $amount]
$issue_year Q$issue_quarter[ec_pretty_pure_price $amount]
total for $issue_year[ec_pretty_pure_price $amount_sum]
total for $issue_year[ec_pretty_pure_price $amount_sum]
 
ExpiresGift Certificates Outstanding 5
$expires_year Q$expires_quarter[ec_pretty_pure_price $amount_outstanding]
$expires_year Q$expires_quarter[ec_pretty_pure_price $amount_outstanding]
total for $expires_year[ec_pretty_pure_price $amount_outstanding_sum]
total for $expires_year[ec_pretty_pure_price $amount_outstanding_sum]
- -

-
-1 Revenue: the actual amount of credit card charges minus the amount of credit card refunds. -

-2 Sales: the price charged, the shipping charged, and the tax charged (minus the amounts refunded) for shipped items (most companies recognize revenue when items are shipped so that they don't risk double counting an account receivable and an item in inventory; see the ecommerce chapter of Philip & Alex's Guide to Web Publishing). Note that this is different from revenue because revenue includes sales of gift certificates. Additionally, some products were paid for using gift certificates. -

-3 Gift Certificate Sales: the amount of gift certificates purchased (recognized on date of purchase). -

-4 Gift Certificates Issued: the amount of gift certificates issued to customers free of charge by web site administrators. -

-5 Gift Certificates Outstanding: gift certificates which have not yet been applied to shipped items (therefore they are - considered a liability). -[ad_admin_footer] -"