Index: openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/address-add-2.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/address-add-2.tcl,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/address-add-2.tcl 3 Aug 2005 22:45:46 -0000 1.1
@@ -0,0 +1,48 @@
+ad_page_contract {
+
+ Confirm shipping address.
+
+ @author Eve Andersson (eveander@arsdigita.com)
+ @creation-date Summer 1999
+ @author ported by Jerry Asher (jerry@theashergroup.com)
+ @author revised by Bart Teeuwisse (bart.teeuwisse@thecodemill.biz)
+ @revision-date April 2002
+
+} {
+ order_id:integer,notnull
+ creditcard_id:integer,optional
+ attn
+ line1
+ line2
+ city
+ {usps_abbrev ""}
+ {full_state_name ""}
+ zip_code
+ {country_code "US"}
+ phone
+ phone_time
+}
+
+ad_require_permission [ad_conn package_id] admin
+
+doc_body_append "
+ [ad_admin_header "Confirm Shipping Address"]
+
+ <h2>Confirm Shipping Address</h2>
+
+ [ad_context_bar [list "../" "Ecommerce([ec_system_name])"] [list "index" "Orders"] [list "one?[export_url_vars order_id]" "One Order"] "Confirm Shipping Address"]
+
+ <hr>
+ <p>Please confirm new address:</p>
+ <blockquote>"
+
+doc_body_append "
+ [ec_display_as_html [ec_pretty_mailing_address_from_args $line1 $line2 $city $usps_abbrev $zip_code $country_code $full_state_name $attn $phone $phone_time]]
+ </blockquote>
+ <form method=post action=address-add-3>
+ [export_entire_form]
+ <center>
+ <input type=submit value=\"Confirm\">
+ </center>
+ </form>
+ [ad_admin_footer]"
Index: openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/address-add-3.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/address-add-3.tcl,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/address-add-3.tcl 3 Aug 2005 22:45:46 -0000 1.1
@@ -0,0 +1,76 @@
+ad_page_contract {
+
+ Insert the address.
+
+ @author Eve Andersson (eveander@arsdigita.com)
+ @creation-date Summer 1999
+ @author ported by Jerry Asher (jerry@theashergroup.com)
+ @author revised by Bart Teeuwisse (bart.teeuwisse@thecodemill.biz)
+ @revision-date April 2002
+
+} {
+ order_id:integer,notnull
+ {creditcard_id:integer ""}
+ attn
+ line1
+ line2
+ city
+ {usps_abbrev ""}
+ {full_state_name ""}
+ zip_code
+ {country_code "US"}
+ phone
+ phone_time
+}
+
+ad_require_permission [ad_conn package_id] admin
+
+if {[empty_string_p $creditcard_id]} {
+
+ # Insert the address into ec_addresses, update the address in
+ # ec_orders
+
+ db_transaction {
+ set address_id [db_nextval ec_address_id_sequence]
+ set user_id [db_string user_id_select "
+ select user_id
+ from ec_orders
+ where order_id = :order_id"]
+
+ db_dml address_insert "
+ insert into ec_addresses
+ (address_id, user_id, address_type, attn, line1, line2, city, usps_abbrev, full_state_name, zip_code, country_code, phone, phone_time)
+ values
+ (:address_id, :user_id, 'shipping', :attn, :line1, :line2, :city, :usps_abbrev, :full_state_name, :zip_code, :country_code, :phone, :phone_time)"
+
+ db_dml ec_orders_update "
+ update ec_orders
+ set shipping_address = :address_id
+ where order_id = :order_id"
+ }
+} else {
+
+ # Insert the address into ec_addresses, update the address in
+ # ec_creditcards
+
+ db_transaction {
+ set address_id [db_nextval ec_address_id_sequence]
+ set user_id [db_string user_id_select "
+ select user_id
+ from ec_orders
+ where order_id = :order_id"]
+
+ db_dml address_insert "
+ insert into ec_addresses
+ (address_id, user_id, address_type, attn, line1, line2, city, usps_abbrev, full_state_name, zip_code, country_code, phone, phone_time)
+ values
+ (:address_id, :user_id, 'shipping', :attn, :line1, :line2, :city, :usps_abbrev, :full_state_name, :zip_code, :country_code, :phone, :phone_time)"
+
+ db_dml ec_creditcards_update "
+ update ec_creditcards
+ set billing_address = :address_id
+ where creditcard_id = :creditcard_id"
+ }
+}
+
+ad_returnredirect "one?[export_url_vars order_id]"
Index: openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/address-add-3.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/address-add-3.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/address-add-3.xql 3 Aug 2005 22:45:46 -0000 1.1
@@ -0,0 +1,37 @@
+<?xml version="1.0"?>
+<queryset>
+
+ <fullquery name="user_id_select">
+ <querytext>
+ select user_id
+ from ec_orders
+ where order_id = :order_id
+ </querytext>
+ </fullquery>
+
+ <fullquery name="address_insert">
+ <querytext>
+ insert into ec_addresses
+ (address_id, user_id, address_type, attn, line1, line2, city, usps_abbrev, full_state_name, zip_code, country_code, phone, phone_time)
+ values
+ (:address_id, :user_id, 'shipping', :attn, :line1, :line2, :city, :usps_abbrev, :full_state_name, :zip_code, :country_code, :phone, :phone_time)
+ </querytext>
+ </fullquery>
+
+ <fullquery name="ec_orders_update">
+ <querytext>
+ update ec_orders
+ set shipping_address = :address_id
+ where order_id = :order_id
+ </querytext>
+ </fullquery>
+
+ <fullquery name="ec_orders_update">
+ <querytext>
+ update ec_creditcards
+ set billing_address = :address_id
+ where creditcard_id = :creditcard_id
+ </querytext>
+ </fullquery>
+
+</queryset>
Index: openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/address-add.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/address-add.tcl,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/address-add.tcl 3 Aug 2005 22:45:46 -0000 1.1
@@ -0,0 +1,120 @@
+ad_page_contract {
+ New shipping address.
+
+ @author Eve Andersson (eveander@arsdigita.com)
+ @creation-date Summer 1999
+ @author ported by Jerry Asher (jerry@theashergroup.com)
+ @author revised by Bart Teeuwisse (bart.teeuwisse@thecodemill.biz)
+ @revision-date April 2002
+
+} {
+ order_id:integer,notnull
+ creditcard_id:integer,optional
+}
+
+ad_require_permission [ad_conn package_id] admin
+
+doc_body_append "
+ [ad_admin_header "New Shipping Address"]
+
+ <h2>New Shipping Address</h2>
+
+ [ad_context_bar [list "../" "Ecommerce([ec_system_name])"] [list "index" "Orders"] [list "one?[export_url_vars order_id]" "One Order"] "New Shipping Address"]
+
+ <hr>
+ <p>Please enter a new domestic address or a new international address. All future shipments for this order will go to this address.</p>
+
+ <p>New domestic address:</p>"
+
+set user_name [db_string user_name_select "
+ select first_names || ' ' || last_name
+ from cc_users, ec_orders
+ where ec_orders.user_id=cc_users.user_id
+ and order_id=:order_id" -default ""]
+
+doc_body_append "
+ <blockquote>
+ <form method=post action=address-add-2>
+ [export_form_vars order_id creditcard_id]
+ <table>
+ <tr>
+ <td>Name</td>
+ <td><input type=text name=attn size=30 value=\"[ad_quotehtml $user_name]\"></td>
+ </tr>
+ <tr>
+ <td>Address</td>
+ <td><input type=text name=line1 size=40></td>
+ </tr>
+ <tr>
+ <td>2nd line (optional)</td>
+ <td><input type=text name=line2 size=40></td>
+ </tr>
+ <tr>
+ <td>City</font></td>
+ <td><input type=text name=city size=20> State [state_widget]</td>
+ </tr>
+ <tr>
+ <td>Zip</td>
+ <td><input type=text maxlength=5 name=zip_code size=5></td>
+ </tr>
+ <tr>
+ <td>Phone</td>
+ <td><input type=text name=phone size=20 maxlength=20> <input type=radio name=phone_time value=d CHECKED> day
+ <input type=radio name=phone_time value=e> evening</td>
+ </tr>
+ </table>
+ <center>
+ <input type=submit value=\"Continue\">
+ </center>
+ </form>
+ </blockquote>
+
+ <p>New international address:</p>
+
+ <blockquote>
+ <form method=post action=address-add-2>
+ [export_form_vars order_id creditcard_id]
+ <table>
+ <tr>
+ <td>Name</td>
+ <td><input type=text name=attn size=30 value=\"[ad_quotehtml $user_name]\"></td>
+ </tr>
+ <tr>
+ <td>Address</td>
+ <td><input type=text name=line1 size=50></td>
+ </tr>
+ <tr>
+ <td>2nd line (optional)</td>
+ <td><input type=text name=line2 size=50></td>
+ </tr>
+ <tr>
+ <td>City</font></td>
+ <td><input type=text name=city size=20></td>
+ </tr>
+ <tr>
+ <td>Province or Region</td>
+ <td><input type=text name=full_state_name size=15></td>
+ </tr>
+ <tr>
+ <td>Postal Code</td>
+ <td><input type=text maxlength=10 name=zip_code size=10></td>
+ </tr>
+ <tr>
+ <td>Country</td>
+ <td>[ec_country_widget]</td>
+ </tr>
+ <tr>
+ <td>Phone</td>
+ <td><input type=text name=phone size=20 maxlength=20> <input type=radio name=phone_time value=d CHECKED> day
+ <input type=radio name=phone_time value=e> evening</td>
+ </tr>
+ </table>
+ <center>
+ <input type=submit value=\"Continue\">
+ </center>
+ </form>
+ </blockquote>
+
+ [ad_admin_footer]"
+
+
Index: openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/address-add.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/address-add.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/address-add.xql 3 Aug 2005 22:45:46 -0000 1.1
@@ -0,0 +1,11 @@
+<?xml version="1.0"?>
+<queryset>
+
+<fullquery name="user_name_select">
+ <querytext>
+ select first_names || ' ' || last_name from cc_users, ec_orders where ec_orders.user_id=cc_users.user_id and order_id=:order_id
+ </querytext>
+</fullquery>
+
+
+</queryset>
Index: openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/comments-edit.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/comments-edit.tcl,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/comments-edit.tcl 3 Aug 2005 22:45:46 -0000 1.1
@@ -0,0 +1,19 @@
+# /www/[ec_url_concat [ec_url] /admin]/orders/comments-edit.tcl
+ad_page_contract {
+
+ Update the comments field of ec_orders.
+
+ @author Eve Andersson (eveander@arsdigita.com)
+ @creation-date Summer 1999
+ @cvs-id $Id: comments-edit.tcl,v 1.1 2005/08/03 22:45:46 roelc Exp $
+ @author ported by Jerry Asher (jerry@theashergroup.com)
+} {
+ order_id:integer,notnull
+ cs_comments
+}
+
+ad_require_permission [ad_conn package_id] admin
+
+db_dml cs_comments_update "update ec_orders set cs_comments=:cs_comments where order_id=:order_id"
+
+ad_returnredirect "one?[export_url_vars order_id]"
Index: openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/comments-edit.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/comments-edit.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/comments-edit.xql 3 Aug 2005 22:45:46 -0000 1.1
@@ -0,0 +1,11 @@
+<?xml version="1.0"?>
+<queryset>
+
+<fullquery name="cs_comments_update">
+ <querytext>
+ update ec_orders set cs_comments=:cs_comments where order_id=:order_id
+ </querytext>
+</fullquery>
+
+
+</queryset>
Index: openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/comments.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/comments.tcl,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/comments.tcl 3 Aug 2005 22:45:46 -0000 1.1
@@ -0,0 +1,42 @@
+# www/[ec_url_concat [ec_url] /admin]/orders/comments.tcl
+ad_page_contract {
+ Add and edit comments for an order.
+
+ @author Eve Andersson (eveander@arsdigita.com)
+ @creation-date Summer 1999
+ @cvs-id $Id: comments.tcl,v 1.1 2005/08/03 22:45:46 roelc Exp $
+ @author ported by Jerry Asher (jerry@theashergroup.com)
+} {
+ order_id:integer,notnull
+}
+
+ad_require_permission [ad_conn package_id] admin
+
+doc_body_append "[ad_admin_header "Comments"]
+
+<h2>Comments</h2>
+
+[ad_context_bar [list "../" "Ecommerce([ec_system_name])"] [list "index" "Orders"] [list "one?[export_url_vars order_id]" "One Order"] "Comments"]
+
+<hr>
+
+<form method=post action=comments-edit>
+[export_form_vars order_id]
+
+Please add or edit comments below:
+
+<br>
+
+<blockquote>
+<textarea name=cs_comments rows=15 cols=50 wrap>[db_string comments_select "select cs_comments from ec_orders where order_id=:order_id"]</textarea>
+</blockquote>
+
+<p>
+<center>
+<input type=submit value=\"Submit\">
+</center>
+
+</form>
+
+[ad_admin_footer]
+"
Index: openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/comments.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/comments.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/comments.xql 3 Aug 2005 22:45:46 -0000 1.1
@@ -0,0 +1,11 @@
+<?xml version="1.0"?>
+<queryset>
+
+<fullquery name="comments_select">
+ <querytext>
+ select cs_comments from ec_orders where order_id=:order_id
+ </querytext>
+</fullquery>
+
+
+</queryset>
Index: openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/creditcard-add-2.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/creditcard-add-2.tcl,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/creditcard-add-2.tcl 3 Aug 2005 22:45:46 -0000 1.1
@@ -0,0 +1,89 @@
+ad_page_contract {
+
+ Credit card confirm.
+
+ @author Eve Andersson (eveander@arsdigita.com)
+ @creation-date Summer 1999
+ @author ported by Jerry Asher (jerry@theashergroup.com)
+ @author revised by Bart Teeuwisse (bart.teeuwisse@thecodemill.biz)
+ @revision-date April 2002
+
+} {
+
+ address_id:notnull
+ order_id:integer,notnull
+ creditcard_number
+ creditcard_type
+ creditcard_expire_1
+ creditcard_expire_2
+}
+
+ad_require_permission [ad_conn package_id] admin
+
+# Get rid of spaces and dashes
+
+regsub -all -- "-" $creditcard_number "" creditcard_number
+regsub -all -- " " $creditcard_number "" creditcard_number
+
+# Error checking
+
+set exception_count 0
+set exception_text ""
+
+if { [regexp {[^0-9]} $creditcard_number] } {
+
+ # I've already removed spaces and dashes, so only numbers should
+ # remain
+
+ incr exception_count
+ append exception_text "<li> Your credit card number contains invalid characters.</li>"
+}
+
+if { ![info exists creditcard_type] || [empty_string_p $creditcard_type] } {
+ incr exception_count
+ append exception_text "<li> You forgot to enter your credit card type.</li>"
+}
+
+# Make sure the credit card type is right & that it has the right
+# number of digits
+
+set additional_count_and_text [ec_creditcard_precheck $creditcard_number $creditcard_type]
+set exception_count [expr $exception_count + [lindex $additional_count_and_text 0]]
+append exception_text [lindex $additional_count_and_text 1]
+
+if { ![info exists creditcard_expire_1] || [empty_string_p $creditcard_expire_1] || ![info exists creditcard_expire_2] || [empty_string_p $creditcard_expire_2] } {
+ incr exception_count
+ append exception_text "<li> Please enter your full credit card expiration date (month and year).</li>"
+}
+
+if { $exception_count > 0 } {
+ ad_return_complaint $exception_count $exception_text
+ return
+}
+
+doc_body_append "
+ [ad_admin_header "Confirm Credit Card"]
+
+ <h2>Confirm Credit Card</h2>
+
+ [ad_context_bar [list "../" "Ecommerce([ec_system_name])"] [list "index" "Orders"] [list "one?[export_url_vars order_id]" "One Order"] "Confirm Credit Card"]
+
+ <hr>
+
+ <p>Please confirm that this is correct:</p>
+
+ <blockquote>
+ <pre>
+[ec_pretty_creditcard_type $creditcard_type]
+$creditcard_number
+exp: $creditcard_expire_1/$creditcard_expire_2
+ </pre>
+ </blockquote>
+
+ <form method=\"post\" action=\"creditcard-add-3\">
+ [export_entire_form]
+ <center>
+ <input type=submit value=\"Confirm\">
+ </center>
+ </form>
+ [ad_admin_footer]"
Index: openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/creditcard-add-3.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/creditcard-add-3.tcl,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/creditcard-add-3.tcl 3 Aug 2005 22:45:46 -0000 1.1
@@ -0,0 +1,38 @@
+ad_page_contract {
+
+ @author Eve Andersson (eveander@arsdigita.com)
+ @creation-date Summer 1999
+ @author ported by Jerry Asher (jerry@theashergroup.com)
+ @author revised by Bart Teeuwisse (bart.teeuwisse@thecodemill.biz)
+ @revision-date April 2002
+
+} {
+ address_id
+ order_id:notnull
+ creditcard_number:notnull
+ creditcard_type:notnull
+ creditcard_expire_1
+ creditcard_expire_2
+}
+
+ad_require_permission [ad_conn package_id] admin
+
+db_transaction {
+ set user_id [db_string user_id_select "
+ select user_id
+ from ec_orders
+ where order_id = :order_id"]
+ set creditcard_id [db_nextval ec_creditcard_id_sequence]
+ set creditcard_last_four [string range $creditcard_number [expr [string length $creditcard_number] -4] [expr [string length $creditcard_number] -1]]
+ set creditcard_expire "$creditcard_expire_1/$creditcard_expire_2"
+ db_dml creditcard_insert_select "
+ insert into ec_creditcards
+ (creditcard_id, user_id, creditcard_number, creditcard_last_four, creditcard_type, creditcard_expire, billing_address)
+ values
+ (:creditcard_id, :user_id, :creditcard_number, :creditcard_last_four, :creditcard_type, :creditcard_expire, :address_id)"
+ db_dml ec_orders_update "
+ update ec_orders
+ set creditcard_id = :creditcard_id
+ where order_id = :order_id"
+}
+ad_returnredirect "one?[export_url_vars order_id]"
Index: openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/creditcard-add-3.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/creditcard-add-3.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/creditcard-add-3.xql 3 Aug 2005 22:45:46 -0000 1.1
@@ -0,0 +1,29 @@
+<?xml version="1.0"?>
+<queryset>
+
+ <fullquery name="user_id_select">
+ <querytext>
+ select user_id
+ from ec_orders
+ where order_id = :order_id
+ </querytext>
+ </fullquery>
+
+ <fullquery name="creditcard_insert_select">
+ <querytext>
+ insert into ec_creditcards
+ (creditcard_id, user_id, creditcard_number, creditcard_last_four, creditcard_type, creditcard_expire, billing_address)
+ values
+ (:creditcard_id, :user_id, :creditcard_number, :creditcard_last_four, :creditcard_type, :creditcard_expire, :address_id)
+ </querytext>
+ </fullquery>
+
+ <fullquery name="ec_orders_update">
+ <querytext>
+ update ec_orders
+ set creditcard_id = :creditcard_id
+ where order_id = :order_id
+ </querytext>
+ </fullquery>
+
+</queryset>
Index: openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/creditcard-add.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/creditcard-add.tcl,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/creditcard-add.tcl 3 Aug 2005 22:45:46 -0000 1.1
@@ -0,0 +1,78 @@
+ad_page_contract {
+
+ Add a creditcard.
+
+ @author Eve Andersson (eveander@arsdigita.com)
+ @creation-date Summer 1999
+ @author ported by Jerry Asher (jerry@theashergroup.com)
+ @author revised by Bart Teeuwisse (bart.teeuwisse@thecodemill.biz)
+ @revision-date April 2002
+
+} {
+ order_id:integer,notnull
+}
+
+ad_require_permission [ad_conn package_id] admin
+
+doc_body_append "
+ [ad_admin_header "New Credit Card"]
+
+ <h2>New Credit Card</h2>
+
+ [ad_context_bar [list "../" "Ecommerce([ec_system_name])"] [list "index" "Orders"] [list "one?[export_url_vars order_id]" "One Order"] "New Credit Card"]
+
+ <hr>
+ <p>Entering a new credit card will cause all future transactions
+ involving this order to use this credit card. However, it will
+ not have any effect on transactions that are currently
+ underway (e.g., if a transaction has already been authorized
+ with a different credit card, that credit card will be used to
+ complete the transaction). </p>"
+
+db_0or1row select_billing_address "
+ select c.billing_address, a.country_code
+ from ec_creditcards c, ec_orders o, ec_addresses a
+ where o.creditcard_id = c.creditcard_id
+ and a.address_id = c.billing_address
+ and o.order_id = :order_id
+ limit 1"
+
+doc_body_append "
+ <table>
+ <tr>
+ <td>
+ [ec_display_as_html [ec_pretty_mailing_address_from_ec_addresses $billing_address]]
+ </td>
+ <td>"
+doc_body_append "
+ </td>
+ <td width=\"20%\">
+ </td>
+ <td valign=\"top\">
+ <form method=\"post\" action=\"creditcard-add-2\">
+ <input type=\"hidden\" name=\"address_id\" value=\"$billing_address\"></input>
+ [export_form_vars order_id]
+ <table>
+ <tr>
+ <td>Credit card number:</td>
+ <td><input type=\"text\" name=\"creditcard_number\" size=\"21\"></td>
+ </tr>
+ <tr>
+ <td>Type:</td>
+ <td>[ec_creditcard_widget]</td>
+ <td>
+ <center>
+ <input type=\"submit\" value=\"Submit\">
+ </center>
+ </td>
+ </tr>
+ <tr>
+ <td>Expires:</td>
+ <td>[ec_creditcard_expire_1_widget] [ec_creditcard_expire_2_widget]</td>
+ </tr>
+ </table>
+ </form>
+ </td>
+ </tr>
+ </table>
+ [ad_admin_footer]"
Index: openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/creditcard-add.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/creditcard-add.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/creditcard-add.xql 3 Aug 2005 22:45:46 -0000 1.1
@@ -0,0 +1,15 @@
+<?xml version="1.0"?>
+<queryset>
+
+ <fullquery name="select_billing_address">
+ <querytext>
+ select c.billing_address, a.country_code
+ from ec_creditcards c, ec_orders o, ec_addresses a
+ where o.creditcard_id = c.creditcard_id
+ and a.address_id = c.billing_address
+ and o.order_id = :order_id
+ limit 1
+ </querytext>
+ </fullquery>
+
+</queryset>
Index: openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/index.adp
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/index.adp,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/index.adp 3 Aug 2005 22:45:46 -0000 1.1
@@ -0,0 +1,4 @@
+<master>
+ <property name="title">Order Summary</property>
+
+ <listtemplate name="orders"></listtemplate>
\ No newline at end of file
Index: openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/index.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/index.tcl,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/index.tcl 3 Aug 2005 22:45:46 -0000 1.1
@@ -0,0 +1,69 @@
+# packages/dotlrn-ecommerce/www/admin/ecommerce/index.tcl
+
+ad_page_contract {
+
+ Pretty list of orders
+
+ @author Roel Canicula (roelmc@pldtdsl.net)
+ @creation-date 2005-08-04
+ @arch-tag: 275de49f-3457-4b4a-bef3-b86b79964217
+ @cvs-id $Id: index.tcl,v 1.1 2005/08/03 22:45:46 roelc Exp $
+} {
+
+} -properties {
+} -validate {
+} -errors {
+}
+
+template::list::create \
+ -name "orders" \
+ -multirow "orders" \
+ -elements {
+ order_id {
+ label "Order ID"
+ link_url_col order_url
+ html { align center }
+ }
+ order_state {
+ label "Order State"
+ }
+ price_to_display {
+ label "Total Amount"
+ html { align right }
+ }
+ person__name {
+ label "Purchaser"
+ }
+ method {
+ label "Payment Method"
+ }
+ balance {
+ label "Balance"
+ html { align right }
+ }
+ }
+
+db_multirow -extend { balance order_url } orders orders {
+ select o.order_id, o.confirmed_date, o.order_state, ec_total_price(o.order_id) as price_to_display, o.user_id, u.first_names, u.last_name, count(*) as n_items, person__name(o.user_id), t.method
+ from ec_orders o
+ join ec_items i using (order_id)
+ left join cc_users u on (o.user_id=u.user_id)
+ join dotlrn_ecommerce_transactions t using (order_id)
+ group by o.order_id, o.confirmed_date, o.order_state, ec_total_price(o.order_id), o.user_id, u.first_names, u.last_name, o.in_basket_date, t.method
+ order by o.in_basket_date desc
+} {
+ if { $method == "invoice" } {
+ set balance [ec_pretty_price [expr $price_to_display - [db_string invoice_payments_sum {
+ select coalesce(sum(amount), 0)
+ from dotlrn_ecommerce_transaction_invoice_payments
+ where order_id = :order_id
+ } -default 0]]]
+ } else {
+ set balance "Paid in full"
+ }
+
+ set order_url [export_vars -base one { order_id }]
+ set price_to_display [ec_pretty_price $price_to_display]
+ set method [string totitle $method]
+ set order_state [string totitle $order_state]
+}
\ No newline at end of file
Index: openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/invoice-payment.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/invoice-payment.tcl,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/invoice-payment.tcl 3 Aug 2005 22:45:46 -0000 1.1
@@ -0,0 +1,352 @@
+# packages/dotlr-ecommerce/www/admin/ecommerce/invoice-payment.tcl
+
+ad_page_contract {
+
+ Invoice payment
+
+ @author Roel Canicula (roelmc@pldtdsl.net)
+ @creation-date 2005-08-04
+ @arch-tag: fe435374-28f2-43a5-ba91-dce6b868304f
+ @cvs-id $Id: invoice-payment.tcl,v 1.1 2005/08/03 22:45:46 roelc Exp $
+} {
+ order_id:integer,notnull
+
+ {creditcard_expire_1 ""}
+ {creditcard_expire_2 ""}
+} -properties {
+} -validate {
+} -errors {
+}
+
+set admin_p [permission::permission_p -object_id [ad_conn package_id] -privilege "admin"]
+set user_id [db_string order_owner {
+ select user_id
+ from ec_orders
+ where order_id = :order_id
+}]
+
+set form [rp_getform]
+ns_set delkey $form creditcard_expires
+
+set validate [list]
+
+set billing_address_id [db_list get_billing_address_id "
+ select address_id
+ from ec_addresses
+ where user_id=:user_id
+ and address_type = 'billing'
+ order by address_id limit 1"]
+
+if { [info exists billing_address_id] } {
+ set billing_address_exists [db_0or1row select_address "
+ select attn, line1, line2, city, usps_abbrev, zip_code, phone, country_code, full_state_name, phone_time
+ from ec_addresses
+ where address_id=:billing_address_id"]
+}
+
+set ec_expires_widget "[ec_creditcard_expire_1_widget $creditcard_expire_1] [ec_creditcard_expire_2_widget $creditcard_expire_2]"
+
+if { [empty_string_p [set payment_methods [parameter::get -parameter PaymentMethods]]] } {
+ lappend payment_methods cc
+}
+
+set method_count 0
+set new_payment_methods [list]
+foreach payment_method [split $payment_methods] {
+ set _payment_method [split $payment_method :]
+ if { [llength $_payment_method] == 2 } {
+ lappend new_payment_methods [set payment_method [lindex $_payment_method 0]]
+
+ switch [lindex $_payment_method 1] {
+ admin {
+ if { $admin_p } {
+ set ${payment_method}_p 1
+ } else {
+ continue
+ }
+ }
+ }
+ } else {
+ set ${payment_method}_p 1
+ lappend new_payment_methods $payment_method
+ }
+
+ switch $payment_method {
+ internal_account {
+ lappend method_options [list "Internal account number" internal_account]
+ lappend validate {internal_account
+ { [exists_and_not_null internal_account] || [template::element::get_value checkout method] != "internal_account" }
+ "Please enter an internal account code"
+ }
+ }
+ check {
+ lappend method_options [list "User sends in a check" check]
+ }
+ cc {
+ lappend method_options [list "Pay via credit card" cc]
+ lappend validate {creditcard_number
+ { [template::element::get_value checkout method] != "cc" || [exists_and_not_null creditcard_number] }
+ "Please enter a credit card number"
+ }
+ lappend validate {creditcard_type
+ { [template::element::get_value checkout method] != "cc" || [exists_and_not_null creditcard_type] }
+ "Please select a credit card type"
+ }
+ lappend validate {creditcard_expires
+ { [template::element::get_value checkout method] != "cc" || ([exists_and_not_null creditcard_expire_1] && [exists_and_not_null creditcard_expire_2]) }
+ "A full credit card expiration date (month and year) is required"
+ }
+ }
+ cash {
+ lappend method_options [list "User pays cash" cash]
+ }
+ }
+ incr method_count
+}
+set payment_methods $new_payment_methods
+
+# Build the form
+ad_form -name checkout -export { order_id } -form {
+ {-section "Amount to be Paid"}
+ {amount:float {label "Amount to be Paid"} {html {size 10}}}
+}
+
+if { $method_count > 1 } {
+ ad_form -extend -name checkout -form {
+ {-section "Payment Information"}
+ {method:text(radio) {label "Select a payment method"} {options {$method_options}}}
+ }
+
+ if { [exists_and_equal internal_account_p 1] } {
+ ad_form -extend -name checkout -form {
+ {internal_account:text,optional {label "Internal Account"}}
+ }
+ }
+} elseif { $method_count == 1 } {
+ ad_form -extend -name checkout -export { {method "[lindex [split $payment_methods] 0]"} } -form {}
+} else {
+ ad_form -extend -name checkout -export { {method cc} } -form {}
+}
+
+if { [info exists cc_p] } {
+ set country_options [linsert [db_list_of_lists countries {
+ select default_name, iso from countries order by default_name
+ }] 0 {"Select a country" ""}]
+
+ set state_options [linsert [db_list_of_lists states {
+ select state_name, abbrev from us_states order by state_name
+ }] 0 {"Select a state" ""}]
+
+ ad_form -extend -name checkout -form {
+ {-section "Billing Information"}
+ {bill_to_first_names:text {label "First name(s)"} {html {size 40}}}
+ {bill_to_last_name:text {label "Last Name"} {html {size 40}}}
+ {bill_to_phone:text {label "Telephone"} {html {size 40}}}
+ {bill_to_phone_time:text(radio) {label "Best time to call"} {options {{day d} {evening e}}}}
+ {bill_to_line1:text {label Address} {html {size 40}}}
+ {bill_to_line2:text,optional {label "Address line 2"} {html {size 40}}}
+ {bill_to_city:text {label City} {html {size 40}}}
+ {bill_to_usps_abbrev:text(select) {label "State/Province"} {options {$state_options}}}
+ {bill_to_zip_code:text {label "ZIP/Postal code"}}
+ {bill_to_country_code:text(select) {label "Country"} {options {$country_options}}}
+ {bill_to_full_state_name:text(hidden),optional}
+ }
+
+ if { $method_count == 1 } {
+ # The creditcard_expires field is a hack, improve it
+ # retrieve a saved address
+ ad_form -extend -name checkout -form {
+ {-section "Credit card information"}
+ {creditcard_number:text {label "Credit card number"}}
+ {creditcard_type:text(select) {label Type} {options {{"Please select one" ""} {VISA v} {MasterCard m} {"American Express" a}}}}
+ {creditcard_expires:text(inform) {label "Expires <span class=\"form-required-mark\">*</span>"} {value $ec_expires_widget}}
+ }
+ } else {
+ ad_form -extend -name checkout -form {
+ {-section "Credit card information"}
+ {creditcard_number:text,optional {label "Credit card number"}}
+ {creditcard_type:text(select),optional {label Type} {options {{"Please select one" ""} {VISA v} {MasterCard m} {"American Express" a}}}}
+ {creditcard_expires:text(inform),optional {label "Expires"} {value $ec_expires_widget}}
+ }
+ }
+}
+
+set price [ec_price_shipping_gift_certificate_and_tax_in_an_order $order_id]
+set total_price [expr [lindex $price 0]-[lindex $price 1]-[lindex $price 2][lindex $price 3]]
+
+set invoice_payments_sum [db_string invoice_payments_sum {
+ select coalesce(sum(amount), 0)
+ from dotlrn_ecommerce_transaction_invoice_payments
+ where order_id = :order_id
+} -default 0]
+
+lappend validate {amount
+ { $amount > 0 && $amount <= ($total_price - $invoice_payments_sum) }
+ "You may only enter up to the amount [ec_pretty_price [expr $total_price - $invoice_payments_sum]]"
+}
+
+ad_form -extend -name checkout -validate $validate -form {} -on_request {
+ set amount [expr $total_price - $invoice_payments_sum]
+ set method cc
+
+ if {$billing_address_exists == 1} {
+ set bill_to_attn $attn
+ # split attn for separate first_names, last_name processing, delimiter is triple space
+ # separate first_names, last_name is required for some payment gateway validation systems (such as EZIC)
+ set name_delim [string first " " $attn]
+ if {$name_delim < 0 } {
+ set name_delim 0
+ }
+ set bill_to_first_names [string trim [string range $attn 0 $name_delim]]
+ set bill_to_last_name [string range $attn [expr $name_delim + 3 ] end]
+
+ set bill_to_line1 $line1
+ set bill_to_line2 $line2
+ set bill_to_city $city
+ set bill_to_usps_abbrev $usps_abbrev
+ set bill_to_zip_code $zip_code
+ set bill_to_phone $phone
+ set bill_to_country_code $country_code
+ set bill_to_full_state_name $full_state_name
+ set bill_to_phone_time $phone_time
+ set bill_to_state_widget $usps_abbrev
+ } else {
+ set billing_address_id 0
+ # no previous billing address, set defaults
+ set bill_to_first_names [value_if_exists first_names]
+ set bill_to_last_name [value_if_exists last_name]
+
+ set bill_to_line1 ""
+ set bill_to_line2 ""
+ set bill_to_city ""
+ set bill_to_usps_abbrev ""
+ set bill_to_zip_code ""
+ set bill_to_phone ""
+ set bill_to_country_code US
+ set bill_to_full_state_name ""
+ set bill_to_phone_time "d"
+ set bill_to_state_widget ""
+ }
+
+} -on_submit {
+# db_transaction {
+ if { $method != "internal_account" } {
+ set internal_account ""
+ }
+
+ # Record payment
+ db_dml insert_invoice_payment {
+ insert into dotlrn_ecommerce_transaction_invoice_payments
+ (order_id, method, internal_account, amount)
+ values
+ (:order_id, :method, :internal_account, :amount)
+ }
+
+ if { $method == "cc" } {
+ # If paid via credit card, initiate a cc transaction
+ set creditcard_id [db_nextval ec_creditcard_id_sequence]
+ set cc_no [string range $creditcard_number [expr [string length $creditcard_number] -4] [expr [string length $creditcard_number] -1]]
+ set expiry "$creditcard_expire_1/$creditcard_expire_2"
+ db_dml insert_new_cc "
+ insert into ec_creditcards
+ (creditcard_id, user_id, creditcard_number, creditcard_last_four, creditcard_type, creditcard_expire, billing_address)
+ values
+ (:creditcard_id, :user_id, :creditcard_number, :cc_no , :creditcard_type, :expiry, :billing_address_id)"
+ db_dml update_order_set_cc "
+ update ec_orders
+ set creditcard_id=:creditcard_id
+ where order_id=:order_id"
+
+ set transaction_id [db_nextval ec_transaction_id_sequence]
+
+ if { ![empty_string_p $creditcard_id] } {
+ db_dml insert_financial_transaction "
+ insert into ec_financial_transactions
+ (creditcard_id, transaction_id, order_id, transaction_amount, transaction_type, inserted_date)
+ values
+ (:creditcard_id, :transaction_id, :order_id, :amount, 'charge', current_timestamp)"
+
+ ec_update_state_to_confirmed $order_id
+ array set response [ec_creditcard_authorization $order_id $transaction_id]
+ set result $response(response_code)
+ set transaction_id $response(transaction_id)
+ if { [string equal $result "authorized"] } {
+ ec_email_new_order $order_id
+
+ # Change the order state from 'confirmed' to
+ # 'authorized'.
+
+ ec_update_state_to_authorized $order_id
+
+ # Record the date & time of the authorization.
+
+ db_dml update_authorized_date "
+ update ec_financial_transactions
+ set authorized_date = current_timestamp
+ where transaction_id = :transaction_id"
+ }
+
+ ns_log notice "DEBUG:: $result"
+
+ if { [string equal $result "authorized"] || [string equal $result "no_recommendation"] } {
+ ad_returnredirect [export_vars -base one { order_id }]
+ ad_script_abort
+ } elseif { [string equal $result "failed_authorization"] } {
+
+ # If the gateway returns no recommendation then
+ # possibility remains that the card is invalid and
+ # that soft goods have been 'shipped' because the
+ # gateway was down and could not verify the soft goods
+ # transaction. The store owner then depends on the
+ # honesty of visitor to obtain a new valid credit card
+ # for the 'shipped' products.
+
+ if {[string equal $result "no_recommendation"] } {
+
+ # Therefor reject the transaction and ask for (a
+ # new credit card and ask) the visitor to
+ # retry. Most credit card gateways have uptimes
+ # close to 99% so this scenario should not happen
+ # often. Another reason for rejecting transactions
+ # without recommendation is that the scheduled
+ # procedures can't authorize soft goods
+ # transactions properly.
+
+ db_dml set_transaction_failed "
+ update ec_financial_transactions
+ set failed_p = 't'
+ where transaction_id = :transaction_id"
+
+ }
+
+ # Updates everything that needs to be updated if a
+ # confirmed order fails
+
+ ec_update_state_to_in_basket $order_id
+
+ # log this just in case this is a symptom of an extended gateway downtime
+ ns_log Notice "invoice-payment.tcl, ref(671): creditcard check failed for order_id $order_id. Redirecting to credit-card-correction"
+
+ }
+ }
+ }
+# }
+ ad_returnredirect [export_vars -base one { order_id }]
+ ad_script_abort
+}
+
+doc_body_append "
+[ad_admin_header "Invoice Payment"]
+
+<h2>Invoice Payment</h2>
+
+[ad_context_bar [list "../" "Ecommerce([ec_system_name])"] [list "index" "Orders"] "One Order"]
+
+<hr>
+
+<h3>Invoice Payment</h3>
+
+[eval [template::adp_compile -string [subst {
+ <formtemplate id=\"checkout\"></formtemplate>
+}]]]
+[ad_admin_footer]"
Index: openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-add-2.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-add-2.tcl,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-add-2.tcl 3 Aug 2005 22:45:46 -0000 1.1
@@ -0,0 +1,60 @@
+ad_page_contract {
+
+ @author Eve Andersson (eveander@arsdigita.com)
+ @creation-date Summer 1999
+ @author ported by Jerry Asher (jerry@theashergroup.com)
+ @author revised by Bart Teeuwisse (bart.teeuwisse@thecodemill.biz)
+ @revision-date April 2002
+
+} {
+ order_id:integer,notnull
+ sku:optional
+ product_name:optional
+}
+
+ad_require_permission [ad_conn package_id] admin
+
+doc_body_append "
+ [ad_admin_header "Add Items, Cont."]
+
+ <h2>Add Items, Cont.</h2>
+
+ [ad_context_bar [list "../" "Ecommerce([ec_system_name])"] [list "index" "Orders"] [list "one?order_id=$order_id" "One Order"] "Add Items, Cont."]
+
+ <hr>"
+
+if { [exists_and_not_null sku] } {
+ set additional_query_part "sku=:sku"
+} else {
+ set additional_query_part "upper(product_name) like '%' || upper(:product_name) || '%'"
+}
+
+set product_counter 0
+db_foreach products_select "
+ select product_id, product_name
+ from ec_products
+ where $additional_query_part" {
+ if { $product_counter == 0 } {
+ doc_body_append "
+ <h3>Here are the product(s) that match your search.</h3>
+ <p> Note: the customer's credit card is not going to be reauthorized when you add this item to the order
+ (their card was already found to be valid when they placed the intial order).
+ They will, as usual, be automatically billed for this item when it ships.
+ If the customer's credit limit is in question, just make a test authorization offline.</p>
+ <ul>"
+ }
+ incr product_counter
+ doc_body_append "
+ <li><p><a href=\"[ec_url_concat [ec_url] /admin]/products/one?[export_url_vars product_id]\">$product_name</a></p>
+ [ec_add_to_cart_link $product_id "Add to Order" "Add to Order" "items-add-3" $order_id]"
+}
+
+if { $product_counter == 0 } {
+ doc_body_append "
+ <p>No matching products were found.</p>"
+} else {
+ doc_body_append "</ul>"
+}
+
+doc_body_append "
+ [ad_admin_footer]"
Index: openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-add-2.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-add-2.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-add-2.xql 3 Aug 2005 22:45:46 -0000 1.1
@@ -0,0 +1,11 @@
+<?xml version="1.0"?>
+<queryset>
+
+<fullquery name="products_select">
+ <querytext>
+ select product_id, product_name from ec_products where $additional_query_part
+ </querytext>
+</fullquery>
+
+
+</queryset>
Index: openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-add-3.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-add-3.tcl,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-add-3.tcl 3 Aug 2005 22:45:46 -0000 1.1
@@ -0,0 +1,51 @@
+ad_page_contract {
+
+ Add items, Cont.
+
+ @author Eve Andersson (eveander@arsdigita.com)
+ @creation-date Summer 1999
+ @author ported by Jerry Asher (jerry@theashergroup.com)
+ @author revised by Bart Teeuwisse (bart.teeuwisse@thecodemill.biz)
+ @revision-date April 2002
+
+} {
+ order_id:integer,notnull
+ product_id:integer,notnull
+ color_choice
+ size_choice
+ style_choice
+}
+
+ad_require_permission [ad_conn package_id] admin
+
+doc_body_append "
+ [ad_admin_header "Add Items, Cont."]
+
+ <h2>Add Items, Cont.</h2>
+
+ [ad_context_bar [list "../" "Ecommerce([ec_system_name])"] [list "index" "Orders"] [list "one?order_id=$order_id" "One Order"] "Add Items, Cont."]
+
+ <hr>"
+
+set item_id [db_nextval ec_item_id_sequence]
+set user_id [db_string user_id_select "
+ select user_id
+ from ec_orders
+ where order_id=:order_id"]
+set lowest_price_and_price_name [ec_lowest_price_and_price_name_for_an_item $product_id $user_id ""]
+
+doc_body_append "
+ <form method=post action=items-add-4>
+ [export_form_vars order_id product_id color_choice size_choice style_choice item_id]
+ <blockquote>
+ <p>This is the price that this user would normally receive for this product. Make modifications as needed:</p>
+ <blockquote>
+ <input type=text name=price_name value=\"[ad_quotehtml [lindex $lowest_price_and_price_name 1]]\" size=15>
+ <input type=text name=price_charged value=\"[format "%0.2f" [lindex $lowest_price_and_price_name 0]]\" size=4> ([ad_parameter -package_id [ec_id] Currency ecommerce])
+ </blockquote>
+ </blockquote>
+ <center>
+ <input type=submit value=\"Add the Item\">
+ </center>
+ </form>
+ [ad_admin_footer]"
Index: openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-add-3.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-add-3.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-add-3.xql 3 Aug 2005 22:45:46 -0000 1.1
@@ -0,0 +1,11 @@
+<?xml version="1.0"?>
+<queryset>
+
+<fullquery name="user_id_select">
+ <querytext>
+ select user_id from ec_orders where order_id=:order_id
+ </querytext>
+</fullquery>
+
+
+</queryset>
Index: openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-add-4-oracle.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-add-4-oracle.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-add-4-oracle.xql 3 Aug 2005 22:45:46 -0000 1.1
@@ -0,0 +1,17 @@
+<?xml version="1.0"?>
+
+<queryset>
+ <rdbms><type>oracle</type><version>8.1.6</version></rdbms>
+
+<fullquery name="ec_items_insert">
+ <querytext>
+ insert into ec_items
+ (item_id, product_id, color_choice, size_choice, style_choice, order_id, in_cart_date, item_state, price_charged, price_name)
+ values
+ (:item_id, :product_id, :color_choice, :size_choice, :style_choice, :order_id, sysdate, 'to_be_shipped', :price_charged, :price_name)
+
+ </querytext>
+</fullquery>
+
+
+</queryset>
Index: openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-add-4-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-add-4-postgresql.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-add-4-postgresql.xql 3 Aug 2005 22:45:46 -0000 1.1
@@ -0,0 +1,17 @@
+<?xml version="1.0"?>
+
+<queryset>
+ <rdbms><type>postgresql</type><version>7.1</version></rdbms>
+
+<fullquery name="ec_items_insert">
+ <querytext>
+ insert into ec_items
+ (item_id, product_id, color_choice, size_choice, style_choice, order_id, in_cart_date, item_state, price_charged, price_name)
+ values
+ (:item_id, :product_id, :color_choice, :size_choice, :style_choice, :order_id, current_timestamp, 'to_be_shipped', :price_charged, :price_name)
+
+ </querytext>
+</fullquery>
+
+
+</queryset>
Index: openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-add-4.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-add-4.tcl,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-add-4.tcl 3 Aug 2005 22:45:46 -0000 1.1
@@ -0,0 +1,139 @@
+ad_page_contract {
+
+ Actually add the items.
+
+ @author Eve Andersson (eveander@arsdigita.com)
+ @creation-date Summer 1999
+ @author ported by Jerry Asher (jerry@theashergroup.com)
+ @author revised by Bart Teeuwisse (bart.teeuwisse@thecodemill.biz)
+ @revision-date April 2002
+
+} {
+ item_id:integer,notnull
+ order_id:integer,notnull
+ product_id:integer,notnull
+ color_choice
+ size_choice
+ style_choice
+ price_charged
+ price_name
+}
+
+ad_require_permission [ad_conn package_id] admin
+
+# Double-click protection
+
+if { [db_string doublclick_select "
+ select count(*)
+ from ec_items
+ where item_id = :item_id"] > 0 } {
+ ad_returnredirect "one?[export_url_vars order_id]"
+ ad_script_abort
+}
+
+# Must have associated credit card
+
+if {[empty_string_p [db_string creditcard_id_select "
+ select creditcard_id
+ from ec_orders
+ where order_id = :order_id"]]} {
+ ad_return_error "Unable to add items to this order." "
+ <p>This order does not have an associated credit card, so new items cannot be added.</p>
+ <p>Please create a new order instead.</p>"
+ ad_script_abort
+}
+
+set shipping_method [db_string shipping_method_select "
+ select shipping_method
+ from ec_orders
+ where order_id = :order_id"]
+
+db_transaction {
+ db_dml ec_items_insert "
+ insert into ec_items
+ (item_id, product_id, color_choice, size_choice, style_choice, order_id, in_cart_date, item_state, price_charged, price_name)
+ values
+ (:item_id, :product_id, :color_choice, :size_choice, :style_choice, :order_id, sysdate, 'to_be_shipped', :price_charged, :price_name)"
+
+ # Check if a shipping gateway has been selected.
+
+ set shipping_gateway [ad_parameter ShippingGateway]
+ if {[acs_sc_binding_exists_p ShippingGateway $shipping_gateway]} {
+
+ # Replace the default ecommerce shipping calculations with the
+ # charges from the shipping gateway. Contact the shipping
+ # gateway to recalculate the total shipping charges.
+
+ db_1row select_shipping_address "
+ select country_code, zip_code
+ from ec_addresses a, ec_orders o
+ where address_id = o.shipping_address
+ and o.order_id = :order_id"
+
+ # Calculate the total value of the shipment.
+
+ set shipment_value [db_string select_shipment_value "
+ select sum(coalesce(i.price_charged, 0))
+ from ec_products p, ec_items i
+ where i.product_id = p.product_id
+ and p.no_shipping_avail_p = 'f'
+ and i.item_state not in ('void', 'received_back', 'expired')
+ and i.order_id = :order_id"]
+ set value_currency_code [ad_parameter Currency ecommerce]
+ set weight_unit_of_measure [ad_parameter WeightUnits ecommerce]
+
+ # Get the list of services and their charges.
+
+ set rates_and_services [acs_sc_call "ShippingGateway" "RatesAndServicesSelection" \
+ [list "" "" "$country_code" "$zip_code" "$shipment_value" "$value_currency_code" "" "$weight_unit_of_measure"] \
+ "$shipping_gateway"]
+
+ # Find the charges for the selected service for the order.
+
+ foreach service $rates_and_services {
+ array set rate_and_service $service
+ set order_shipping_cost $rate_and_service(total_charges)
+ set service_code $rate_and_service(service_code)
+ set service_description [acs_sc_call "ShippingGateway" "ServiceDescription" \
+ "$service_code" \
+ "$shipping_gateway"]
+
+ # Unfortunately checking on the description of the
+ # shipping service is required as only the description is
+ # stored with the order as the shipping method.
+
+ if {[string equal $service_description $shipping_method]} {
+ break
+ }
+ }
+
+ # Calculate the tax on shipping and update the shipping cost
+ # of the order.
+
+ set tax_on_order_shipping_cost [db_string get_shipping_tax "
+ select ec_tax(0, :order_shipping_cost, :order_id)"]
+
+ db_dml set_shipping_charges "
+ update ec_orders
+ set shipping_charged = round(:order_shipping_cost, 2), shipping_tax_charged = round(:tax_on_order_shipping_cost, 2)
+ where order_id=:order_id"
+
+ } else {
+
+ # I calculate the shipping after it's inserted because this
+ # procedure goes and checks whether this is the first instance
+ # of this product in this order. I know it's non-ideal
+ # efficiency-wise, but this procedure (written for the user
+ # pages) # is already written and it works.
+
+ set shipping_price [ec_shipping_price_for_one_item $item_id $product_id $order_id $shipping_method]
+
+ db_dml ec_items_update "
+ update ec_items
+ set shipping_charged = :shipping_price
+ where item_id = :item_id"
+
+ }
+}
+
+ad_returnredirect "one?[export_url_vars order_id]"
Index: openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-add-4.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-add-4.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-add-4.xql 3 Aug 2005 22:45:46 -0000 1.1
@@ -0,0 +1,32 @@
+<?xml version="1.0"?>
+<queryset>
+
+<fullquery name="doublclick_select">
+ <querytext>
+ select count(*) from ec_items where item_id=:item_id
+ </querytext>
+</fullquery>
+
+
+<fullquery name="creditcard_id_select">
+ <querytext>
+ select creditcard_id from ec_orders where order_id=:order_id
+ </querytext>
+</fullquery>
+
+
+<fullquery name="shipping_method_select">
+ <querytext>
+ select shipping_method from ec_orders where order_id=:order_id
+ </querytext>
+</fullquery>
+
+
+<fullquery name="ec_items_update">
+ <querytext>
+ update ec_items set shipping_charged=:shipping_price where item_id=:item_id
+ </querytext>
+</fullquery>
+
+
+</queryset>
Index: openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-add.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-add.tcl,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-add.tcl 3 Aug 2005 22:45:46 -0000 1.1
@@ -0,0 +1,41 @@
+ad_page_contract {
+
+ Add an item to an order.
+
+ @author Eve Andersson (eveander@arsdigita.com)
+ @creation-date Summer 1999
+ @author ported by Jerry Asher (jerry@theashergroup.com)
+ @author revised by Bart Teeuwisse (bart.teeuwisse@thecodemill.biz)
+ @revision-date April 2002
+
+} {
+ order_id:integer,notnull
+}
+
+ad_require_permission [ad_conn package_id] admin
+
+doc_body_append "
+ [ad_admin_header "Add Items"]
+
+ <h2>Add Items</h2>
+
+ [ad_context_bar [list "../" "Ecommerce([ec_system_name])"] [list "index" "Orders"] [list "one?order_id=$order_id" "One Order"] "Add Items"]
+
+ <hr>
+ <blockquote>
+ <p>Search for a product to add:</p>
+ <form method=post action=items-add-2>
+ [export_form_vars order_id]
+ <ul>
+ <li>By Name: <input type=text name=product_name size=20> <input type=submit value=\"Search\"></li>
+ </ul>
+ </form>
+ <form method=post action=items-add-2>
+ [export_form_vars order_id]
+ <ul>
+ <li>By SKU: <input type=text name=sku size=3> <input type=submit value=\"Search\"></li>
+ </ul>
+ </form>
+ </blockquote>
+ [ad_admin_footer]"
+
Index: openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-return-2-oracle.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-return-2-oracle.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-return-2-oracle.xql 3 Aug 2005 22:45:46 -0000 1.1
@@ -0,0 +1,34 @@
+<?xml version="1.0"?>
+
+<queryset>
+ <rdbms><type>oracle</type><version>8.1.6</version></rdbms>
+
+ <fullquery name="base_shipping_select">
+ <querytext>
+ select nvl(shipping_charged,0) - nvl(shipping_refunded,0)
+ from ec_orders
+ where order_id=:order_id
+ </querytext>
+ </fullquery>
+
+ <fullquery name="all_items_select">
+ <querytext>
+ select i.item_id, p.product_name, i.price_charged, nvl(i.shipping_charged,0) as shipping_charged
+ from ec_items i, ec_products p
+ where i.product_id=p.product_id
+ and i.item_id in ([join $item_id_list ", "])
+ and i.item_state in ('shipped','arrived')
+ </querytext>
+ </fullquery>
+
+ <fullquery name="selected_items_select">
+ <querytext>
+ select i.item_id, p.product_name, i.price_charged, nvl(i.shipping_charged,0) as shipping_charged
+ from ec_items i, ec_products p
+ where i.product_id=p.product_id
+ and i.order_id=:order_id
+ and i.item_state in ('shipped','arrived')
+ </querytext>
+ </fullquery>
+
+</queryset>
Index: openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-return-2-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-return-2-postgresql.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-return-2-postgresql.xql 3 Aug 2005 22:45:46 -0000 1.1
@@ -0,0 +1,34 @@
+<?xml version="1.0"?>
+
+<queryset>
+ <rdbms><type>postgresql</type><version>7.1</version></rdbms>
+
+ <fullquery name="base_shipping_select">
+ <querytext>
+ select coalesce(shipping_charged,0) - coalesce(shipping_refunded,0)
+ from ec_orders
+ where order_id=:order_id
+ </querytext>
+ </fullquery>
+
+ <fullquery name="all_items_select">
+ <querytext>
+ select i.item_id, p.product_name, i.price_charged, coalesce(i.shipping_charged,0) as shipping_charged
+ from ec_items i, ec_products p
+ where i.product_id=p.product_id
+ and i.item_id in ([join $item_id_list ", "])
+ and i.item_state in ('shipped','arrived')
+ </querytext>
+ </fullquery>
+
+ <fullquery name="selected_items_select">
+ <querytext>
+ select i.item_id, p.product_name, i.price_charged, coalesce(i.shipping_charged,0) as shipping_charged
+ from ec_items i, ec_products p
+ where i.product_id=p.product_id
+ and i.order_id=:order_id
+ and i.item_state in ('shipped','arrived')
+ </querytext>
+ </fullquery>
+
+</queryset>
Index: openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-return-2.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-return-2.tcl,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-return-2.tcl 3 Aug 2005 22:45:46 -0000 1.1
@@ -0,0 +1,141 @@
+ad_page_contract {
+
+ @cvs-id $Id: items-return-2.tcl,v 1.1 2005/08/03 22:45:46 roelc Exp $
+ @author ported by Jerry Asher (jerry@theashergroup.com)
+ @author revised by Bart Teeuwisse (bart.teeuwisse@thecodemill.biz)
+ @revision-date April 2002
+
+} {
+
+ refund_id:notnull
+ order_id:notnull,naturalnum
+ reason_for_return
+ all_items_p:optional
+ item_id:optional,multiple
+ received_back_date:date,array
+ received_back_time:time,array
+}
+
+ad_require_permission [ad_conn package_id] admin
+
+set received_back_datetime $received_back_date(date)
+if { [exists_and_not_null received_back_time(time)] } {
+ append received_back_datetime " [ec_timeentrywidget_time_check \"$received_back_time(time)\"]$received_back_time(ampm)"
+} else {
+ append received_back_datetime " 12:00:00AM"
+}
+
+# The customer service rep must be logged on
+
+set customer_service_rep [ad_get_user_id]
+if {$customer_service_rep == 0} {
+ set return_url "[ad_conn url]?[export_entire_form_as_url_vars]"
+ ad_returnredirect "/register?[export_url_vars return_url]"
+ ad_script_abort
+}
+
+# Make sure they haven't already inserted this refund
+
+if { [db_string get_refund_count "
+ select count(*)
+ from ec_refunds
+ where refund_id=:refund_id"] > 0 } {
+ ad_return_complaint 1 "
+ <li>This refund has already been inserted into the database. Are you using an old form? <a href=\"one?[export_url_vars order_id]\">Return to the order.</a>"
+ ad_script_abort
+}
+
+set exception_count 0
+set exception_text ""
+
+# They must have either checked "All items" and none of the rest, or
+# at least one of the rest and not "All items". They also need to have
+# shipment_date filled in
+
+if { [info exists all_items_p] && [info exists item_id] } {
+ incr exception_count
+ append exception_text "<li>Please either check off \"All items\" or check off some of the items, but not both."
+}
+if { ![info exists all_items_p] && ![info exists item_id] } {
+ incr exception_count
+ append exception_text "<li>Please either check off \"All items\" or check off some of the items."
+}
+
+if { $exception_count > 0 } {
+ ad_return_complaint 1 $exception_text
+ ad_script_abort
+}
+
+append doc_body "
+ [ad_admin_header "Specify refund amount"]
+
+ <h2>Specify refund amount</h2>
+
+ [ad_context_bar [list "../" "Ecommerce([ec_system_name])"] [list "index" "Orders"] [list "one?[export_url_vars order_id]" "One"] "Mark Items Returned"]
+ <hr>"
+
+set shipping_refund_percent [ad_parameter -package_id [ec_id] ShippingRefundPercent ecommerce]
+
+if { ![info exists all_items_p] } {
+ set item_id_list $item_id
+ set sql [db_map all_items_select]
+} else {
+ set sql [db_map selected_items_select]
+}
+
+# Generate a list of the items if they selected "All items" because,
+# regardless of what happens elsewhere on the site (e.g. an item is
+# added to the order, thereby causing the query for all items to
+# return one more item), only the items that they confirm here should
+# be recorded as part of this return.
+
+if { [info exists all_items_p] } {
+ set item_id_list [list]
+}
+
+set items_to_print ""
+db_foreach get_return_item_list $sql {
+
+ if { [info exists all_items_p] } {
+ lappend item_id_list $item_id
+ }
+ append items_to_print "
+ <tr>
+ <td>$product_name</td>
+ <td><input type=text name=\"price_to_refund.${item_id}\" value=\"[format "%0.2f" $price_charged]\" size=\"5\"> (out of [ec_pretty_price $price_charged])</td>
+ <td><input type=text name=\"shipping_to_refund.${item_id}\" value=\"[format "%0.2f" [expr $shipping_charged * $shipping_refund_percent]]\" size=\"5\">
+ (out of [ec_pretty_price $shipping_charged])</td>
+ </tr>"
+}
+
+append doc_body "
+ <form method=post action=items-return-3>
+ [export_form_vars refund_id order_id item_id_list received_back_datetime reason_for_return]
+ <blockquote>
+ <table border=0 cellspacing=0 cellpadding=10>
+ <tr>
+ <th>Item</th><th>Price to Refund</th><th>Shipping to Refund</th>
+ </tr>
+ $items_to_print
+ </table>"
+
+# Although only one refund may be done on an item, multiple refunds
+# may be done on the base shipping cost, so show shipping_charged -
+# shipping_refunded.
+
+set base_shipping [db_string base_shipping_select "
+ select nvl(shipping_charged,0) - nvl(shipping_refunded,0)
+ from ec_orders
+ where order_id=:order_id"]
+
+append doc_body "
+ <p>Base shipping charge to refund:
+ <input type=text name=base_shipping_to_refund value=\"[format "%0.2f" [expr $base_shipping * $shipping_refund_percent]]\" size=\"5\">
+ (out of [ec_pretty_price $base_shipping])</p>
+ </blockquote>
+
+ <center><input type=submit value=\"Continue\"></center>
+
+ [ad_admin_footer]"
+
+doc_return 200 text/html $doc_body
Index: openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-return-2.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-return-2.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-return-2.xql 3 Aug 2005 22:45:46 -0000 1.1
@@ -0,0 +1,12 @@
+<?xml version="1.0"?>
+<queryset>
+
+ <fullquery name="get_refund_count">
+ <querytext>
+ select count(*)
+ from ec_refunds
+ where refund_id=:refund_id
+ </querytext>
+ </fullquery>
+
+</queryset>
Index: openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-return-3-oracle.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-return-3-oracle.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-return-3-oracle.xql 3 Aug 2005 22:45:46 -0000 1.1
@@ -0,0 +1,52 @@
+<?xml version="1.0"?>
+
+<queryset>
+ <rdbms><type>oracle</type><version>8.1.6</version></rdbms>
+
+ <fullquery name="get_items_for_return">
+ <querytext>
+ select i.item_id, p.product_name, nvl(i.price_charged,0) as price_charged, nvl(i.shipping_charged,0) as shipping_charged,
+ nvl(i.price_tax_charged,0) as price_tax_charged, nvl(i.shipping_tax_charged,0) as shipping_tax_charged
+ from ec_items i, ec_products p
+ where i.product_id=p.product_id
+ and i.item_id in ([join $item_id_list ", "])
+ </querytext>
+ </fullquery>
+
+ <fullquery name="get_ec_tax">
+ <querytext>
+ select nvl(ec_tax(:tax_price_to_refund, 0, :order_id),0)
+ from dual
+ </querytext>
+ </fullquery>
+
+ <fullquery name="get_it_shipping_tax_refund">
+ <querytext>
+ select nvl(ec_tax(0, $shipping_to_refund($item_id), :order_id),0)
+ from dual
+ </querytext>
+ </fullquery>
+
+ <fullquery name="get_shipping_charged_values">
+ <querytext>
+ select nvl(shipping_charged,0) - nvl(shipping_refunded,0) as base_shipping, nvl(shipping_tax_charged,0) - nvl(shipping_tax_refunded,0) as base_shipping_tax
+ from ec_orders
+ where order_id = :order_id
+ </querytext>
+ </fullquery>
+
+ <fullquery name="get_base_shipping_it_refund">
+ <querytext>
+ select nvl(ec_tax(0, :base_shipping, :order_id),0)
+ from dual
+ </querytext>
+ </fullquery>
+
+ <fullquery name="get_cash_refunded">
+ <querytext>
+ select nvl(ec_cash_amount_to_refund(:total_amount_to_refund, :order_id),0)
+ from dual
+ </querytext>
+ </fullquery>
+
+</queryset>
Index: openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-return-3-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-return-3-postgresql.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-return-3-postgresql.xql 3 Aug 2005 22:45:46 -0000 1.1
@@ -0,0 +1,48 @@
+<?xml version="1.0"?>
+
+<queryset>
+ <rdbms><type>postgresql</type><version>7.1</version></rdbms>
+
+ <fullquery name="get_items_for_return">
+ <querytext>
+ select i.item_id, p.product_name, coalesce(i.price_charged,0) as price_charged, coalesce(i.shipping_charged,0) as shipping_charged, coalesce(i.price_tax_charged,0) as price_tax_charged,
+ coalesce(i.shipping_tax_charged,0) as shipping_tax_charged
+ from ec_items i, ec_products p
+ where i.product_id=p.product_id
+ and i.item_id in ([join $item_id_list ", "])
+ </querytext>
+ </fullquery>
+
+ <fullquery name="get_ec_tax">
+ <querytext>
+ select coalesce(ec_tax(:tax_price_to_refund, 0, :order_id),0)
+ </querytext>
+ </fullquery>
+
+ <fullquery name="get_it_shipping_tax_refund">
+ <querytext>
+ select coalesce(ec_tax(0, $shipping_to_refund($item_id), :order_id),0)
+ </querytext>
+ </fullquery>
+
+ <fullquery name="get_shipping_charged_values">
+ <querytext>
+ select coalesce(shipping_charged,0) - coalesce(shipping_refunded,0) as base_shipping, coalesce(shipping_tax_charged,0) - coalesce(shipping_tax_refunded,0) as base_shipping_tax
+ from ec_orders
+ where order_id = :order_id
+ </querytext>
+ </fullquery>
+
+ <fullquery name="get_base_shipping_it_refund">
+ <querytext>
+ select coalesce(ec_tax(0, :base_shipping, :order_id),0)
+ </querytext>
+ </fullquery>
+
+ <fullquery name="get_cash_refunded">
+ <querytext>
+ select coalesce(ec_cash_amount_to_refund(:total_amount_to_refund, :order_id),0)
+ </querytext>
+ </fullquery>
+
+</queryset>
Index: openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-return-3.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-return-3.tcl,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-return-3.tcl 3 Aug 2005 22:45:46 -0000 1.1
@@ -0,0 +1,248 @@
+ad_page_contract {
+
+ @param refund_id
+ @param order_id
+ @param received_back_date
+ @param reason_for_return
+ @param item_id_list
+ @param price_to_refund(item_id) for each item_id,
+ @param shipping_to_refund(item_id) for each item_id,
+ @param base_shipping_to_refund
+
+ @author
+ @creation-date
+ @author ported by Jerry Asher (jerry@theashergroup.com)
+ @author revised by Bart Teeuwisse (bart.teeuwisse@thecodemill.biz)
+ @revision-date April 2002
+
+} {
+ refund_id:notnull,naturalnum
+ order_id:notnull,naturalnum
+ received_back_datetime
+ reason_for_return
+ item_id_list
+ price_to_refund:array
+ shipping_to_refund:array
+ base_shipping_to_refund
+}
+
+# The customer service rep must be logged on
+
+ad_require_permission [ad_conn package_id] admin
+set customer_service_rep [ad_get_user_id]
+
+# Error checking: Make sure price_to_refund($item_id) is <=
+# price_charged for that item same with shipping. Make sure
+# base_shipping_to_refund is <= base shipping charged - refunded
+# Make sure they haven't already inserted this refund
+
+if { [db_string get_count_refunds "
+ select count(*)
+ from ec_refunds
+ where refund_id=:refund_id"] > 0 } {
+ ad_return_complaint 1 "
+ <li>This refund has already been inserted into the database.Are you using an old form? <a href=\"one?[export_url_vars order_id]\">Return to the order.</a>"
+ return
+}
+
+set exception_count 0
+set exception_text ""
+
+# Add up the items' price/shipping/tax to refund as we go
+
+set total_price_to_refund 0
+set total_shipping_to_refund 0
+set total_price_tax_to_refund 0
+set total_shipping_tax_to_refund 0
+
+db_foreach get_items_for_return "
+ select i.item_id, p.product_name, nvl(i.price_charged,0) as price_charged, nvl(i.shipping_charged,0) as shipping_charged,
+ nvl(i.price_tax_charged,0) as price_tax_charged, nvl(i.shipping_tax_charged,0) as shipping_tax_charged
+ from ec_items i, ec_products p
+ where i.product_id=p.product_id
+ and i.item_id in ([join $item_id_list ", "])" {
+
+ if { [empty_string_p $price_to_refund($item_id)] } {
+ incr exception_count
+ append exception_text "<li>Please enter a price to refund for $product_name."
+ } elseif {[regexp {[^0-9\.]} $price_to_refund($item_id)]} {
+ incr exception_count
+ append exception_text "<li>Please enter a purely numeric price to refund for $product_name (no letters or special characters)."
+ } elseif { $price_to_refund($item_id) > $price_charged } {
+ incr exception_count
+ append exception_text "<li>Please enter a price to refund for $product_name that is less than or equal to [ec_pretty_price $price_charged]."
+ } else {
+ set total_price_to_refund [expr $total_price_to_refund + $price_to_refund($item_id)]
+
+ # Tax will be the minimum of the tax actually charged and the
+ # tax that would have been charged on the price to refund (tax
+ # rates may have changed in the meantime and we don't want to
+ # refund more than they paid)
+
+ set tax_price_to_refund $price_to_refund($item_id)
+ set iteration_price_tax_to_refund [ec_min $price_tax_charged [db_string get_ec_tax "
+ select coalesce(ec_tax(:tax_price_to_refund,0,:order_id),0)
+ from dual"]]
+ set total_price_tax_to_refund [expr $total_price_tax_to_refund + $iteration_price_tax_to_refund]
+ }
+
+ if { [empty_string_p $shipping_to_refund($item_id)] } {
+ incr exception_count
+ append exception_text "<li>Please enter a shipping amount to refund for $product_name."
+ } elseif {[regexp {[^0-9\.]} $shipping_to_refund($item_id)]} {
+ incr exception_count
+ append exception_text "<li>Please enter a purely numeric shipping amount to refund for $product_name (no letters or special characters)."
+ } elseif { $shipping_to_refund($item_id) > $shipping_charged } {
+ incr exception_count
+ append exception_text "<li>Please enter a shipping amount to refund for $product_name that is less than or equal to [ec_pretty_price $shipping_charged]."
+ } else {
+ set total_shipping_to_refund [expr $total_shipping_to_refund + $shipping_to_refund($item_id)]
+
+ set iteration_shipping_tax_to_refund [ec_min $shipping_tax_charged [db_string get_it_shipping_tax_refund "
+ select coalesce(ec_tax(0,$shipping_to_refund($item_id), $order_id),0)
+ from dual"]]
+ set total_shipping_tax_to_refund [expr $total_shipping_tax_to_refund + $iteration_shipping_tax_to_refund]
+ }
+}
+
+db_1row get_shipping_charged_values "
+ select nvl(shipping_charged,0) - nvl(shipping_refunded,0) as base_shipping,
+ nvl(shipping_tax_charged,0) - nvl(shipping_tax_refunded,0) as base_shipping_tax
+ from ec_orders
+ where order_id=:order_id"
+
+if { [empty_string_p $base_shipping_to_refund] } {
+ incr exception_count
+ append exception_text "<li>Please enter a base shipping amount to refund."
+} elseif {[regexp {[^0-9\.]} $base_shipping_to_refund]} {
+ incr exception_count
+ append exception_text "<li>Please enter a purely numeric base shipping amount to refund (no letters or special characters)."
+} elseif { $base_shipping_to_refund > $base_shipping } {
+ incr exception_count
+ append exception_text "<li>Please enter a base shipping amount to refund that is less than or equal to [ec_pretty_price $base_shipping]."
+} else {
+ set total_shipping_to_refund [expr $total_shipping_to_refund + $base_shipping_to_refund]
+ set iteration_shipping_tax_to_refund [ec_min $base_shipping_tax [db_string get_base_shipping_it_refund "
+ select coalesce(ec_tax(0,:base_shipping,:order_id),0)
+ from dual"]]
+ set total_shipping_tax_to_refund [expr $total_shipping_tax_to_refund + $iteration_shipping_tax_to_refund]
+}
+
+if { $exception_count > 0 } {
+ ad_return_complaint $exception_count $exception_text
+ return
+}
+
+set total_tax_to_refund [expr $total_price_tax_to_refund + $total_shipping_tax_to_refund]
+set total_amount_to_refund [expr $total_price_to_refund + $total_shipping_to_refund + $total_tax_to_refund]
+
+# Determine how much of this will be refunded in cash
+
+set cash_amount_to_refund [db_string get_cash_refunded "
+ select nvl(ec_cash_amount_to_refund(:total_amount_to_refund,:order_id),0)
+ from dual"]
+
+# Calculate gift certificate amount and tax to refund
+
+set certificate_amount_to_reinstate [expr $total_amount_to_refund - $cash_amount_to_refund]
+if { $certificate_amount_to_reinstate < 0 } {
+
+ # Because of rounding
+
+ set certificate_amount_to_reinstate 0
+}
+
+# See if the credit card data is still in the database. If not the
+# credit card number has to be re-entered.
+
+if {![db_0or1row get_billing_info "
+ select c.creditcard_id, c.creditcard_type, c.creditcard_last_four,
+ substring(creditcard_expire for 2) as card_exp_month, substring(creditcard_expire from 4 for 2) as card_exp_year,
+ p.first_names || ' ' || p.last_name as card_name,
+ a.line1 as billing_street, a.city as billing_city, a.usps_abbrev as billing_state, a.zip_code as billing_zip, a.country_code as billing_country
+ from ec_orders o, ec_creditcards c, persons p, ec_addresses a
+ where o.creditcard_id = c.creditcard_id
+ and c.billing_address = a.address_id
+ and c.user_id = p.person_id
+ and o.order_id=:order_id"]} {
+
+ set creditcard_number ""
+ set creditcard_id ""
+ set creditcard_type ""
+ set creditcard_last_four ""
+ set card_expiration ""
+ set card_name ""
+ set billing_street ""
+ set billing_state ""
+ set billing_zip ""
+ set billing_country ""
+ set billing_city ""
+}
+
+append doc_body "
+ [ad_admin_header "Refund Totals"]
+
+ <h2>Refund Totals</h2>
+
+ [ad_context_bar [list "../" "Ecommerce([ec_system_name])"] [list "index" "Orders"] [list "one?[export_url_vars order_id]" "One"] "Refund Totals"]
+
+ <hr>
+ <form method=post action=items-return-4>
+ [export_entire_form]
+ [export_form_vars cash_amount_to_refund certificate_amount_to_reinstate]
+ <blockquote>
+ <p>Total refund amount: [ec_pretty_price $total_amount_to_refund] (price: [ec_pretty_price $total_price_to_refund], shipping: [ec_pretty_price $total_shipping_to_refund], tax: [ec_pretty_price $total_tax_to_refund])</p>
+ <ul>
+ <li>[ec_pretty_price $certificate_amount_to_reinstate] will be reinstated in gift certificates.<br>
+ <li>[ec_pretty_price $cash_amount_to_refund] will be refunded to the customer's credit card.<br>
+ </ul>"
+
+# Request the credit card number to be re-entered if it is no longer
+# on file, yet there is money to refund.
+
+if { [empty_string_p $creditcard_number] && $cash_amount_to_refund > 0 } {
+ append doc_body "
+ <p>Please re-enter the credit card number of the card used for this order:</p>
+ <table border=0 cellspacing=0 cellpadding=10>
+ <tr>
+ <td>
+ <table>
+ <tr>
+ <td><input type=\"hidden\" name=\"creditcard_id\" value=\"$creditcard_id\">
+ [ec_creditcard_widget $creditcard_type disabled]</td>
+ <td><input type=\"text\" name=\"creditcard_number\" size=\"21\" maxlength=\"17\"></td>
+ </tr>
+ <tr>
+ <td>Ending in:</td>
+ <td>xxxxxxxxxxxx$creditcard_last_four</td>
+ <tr>
+ <td>Expires:</td>
+ <td>$card_expiration</td>
+ <tr>
+ <td valign=\"top\">Billing address:</td>
+ <td>$billing_street<br>
+ $billing_city, $billing_state $billing_zip<br>
+ $billing_country</td>
+ </tr>
+ </table>
+ </td>
+ </tr>
+ </table>"
+} else {
+ append doc_body "
+ [export_form_vars creditcard_id creditcard_number]"
+}
+append doc_body "
+ [export_form_vars creditcard_type]"
+
+append doc_body "
+ <br>
+ </blockquote>
+ [export_form_vars creditcard_last_four]
+ <center>
+ <input type=submit value=\"Complete the Refund\">
+ </center>
+</form>
+[ad_admin_footer]"
+
+doc_return 200 text/html $doc_body
Index: openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-return-3.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-return-3.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-return-3.xql 3 Aug 2005 22:45:46 -0000 1.1
@@ -0,0 +1,26 @@
+<?xml version="1.0"?>
+<queryset>
+
+ <fullquery name="get_count_refunds">
+ <querytext>
+ select count(*)
+ from ec_refunds
+ where refund_id=:refund_id
+ </querytext>
+ </fullquery>
+
+ <fullquery name="get_billing_info">
+ <querytext>
+ select c.creditcard_id, c.creditcard_type, c.creditcard_number, c.creditcard_last_four,
+ c.creditcard_expire as card_expiration,
+ p.first_names || ' ' || p.last_name as card_name,
+ a.line1 as billing_street, a.city as billing_city, a.usps_abbrev as billing_state, a.zip_code as billing_zip, a.country_code as billing_country
+ from ec_orders o, ec_creditcards c, persons p, ec_addresses a
+ where o.creditcard_id = c.creditcard_id
+ and c.billing_address=a.address_id
+ and c.user_id = p.person_id
+ and o.order_id=:order_id
+ </querytext>
+ </fullquery>
+
+</queryset>
Index: openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-return-4-oracle.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-return-4-oracle.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-return-4-oracle.xql 3 Aug 2005 22:45:46 -0000 1.1
@@ -0,0 +1,193 @@
+<?xml version="1.0"?>
+
+<queryset>
+ <rdbms>
+ <type>oracle</type>
+ <version>8.1.6</version>
+ </rdbms>
+
+ <fullquery name="insert_new_ec_refund">
+ <querytext>
+ insert into ec_refunds
+ (refund_id, order_id, refund_amount, refund_date, refunded_by, refund_reasons)
+ values
+ (:refund_id, :order_id, :cash_amount_to_refund, sysdate, :customer_service_rep,:reason_for_return)
+ </querytext>
+ </fullquery>
+
+ <fullquery name="get_tax_charged_on_item">
+ <querytext>
+ select nvl(price_tax_charged,0) as price_tax_charged,
+ nvl(shipping_tax_charged,0) as shipping_tax_charged
+ from ec_items
+ where item_id=:item_id
+ </querytext>
+ </fullquery>
+
+ <fullquery name="get_tax_charged">
+ <querytext>
+ select ec_tax(:price_bind_variable,0,:order_id)
+ from dual
+ </querytext>
+ </fullquery>
+
+ <fullquery name="get_tax_shipping_to_refund">
+ <querytext>
+ select ec_tax(0,:shipping_bind_variable,:order_id)
+ from dual
+ </querytext>
+ </fullquery>
+
+ <fullquery name="get_base_shipping_tax">
+ <querytext>
+ select nvl(shipping_tax_charged,0)
+ from ec_orders
+ where order_id=:order_id
+ </querytext>
+ </fullquery>
+
+ <fullquery name="get_base_tax_to_refund">
+ <querytext>
+ select ec_tax(0,:base_shipping_to_refund,:order_id)
+ from dual
+ </querytext>
+ </fullquery>
+
+ <fullquery name="insert_new_financial_trans">
+ <querytext>
+ insert into ec_financial_transactions
+ (transaction_id, order_id, refund_id, creditcard_id, transaction_amount, transaction_type, inserted_date)
+ values
+ (:transaction_id, :order_id, :refund_id, :creditcard_id, :cash_amount_to_refund, 'refund', sysdate)
+ </querytext>
+ </fullquery>
+
+ <fullquery name="get_gc_amount_used">
+ <querytext>
+ select ec_order_gift_cert_amount(:order_id)
+ from dual
+ </querytext>
+ </fullquery>
+
+ <fullquery name="record_reinstate_problem">
+ <querytext>
+ insert into ec_problems_log
+ (problem_id, problem_date, problem_details, order_id)
+ values
+ (ec_problem_id_sequence.nextval, sysdate, :errorstring, :order_id)
+ </querytext>
+ </fullquery>
+
+ <fullquery name="reinstateable_gift_certificates">
+ <querytext>
+ select u.gift_certificate_id, nvl(sum(u.amount_used),0) - nvl(sum(u.amount_reinstated),0) as reinstateable_amount
+ from ec_gift_certificate_usage u, ec_gift_certificates c
+ where u.gift_certificate_id = c.gift_certificate_id
+ and u.order_id = :order_id
+ group by u.gift_certificate_id, c.expires
+ order by expires desc, gift_certificate_id desc
+ </querytext>
+ </fullquery>
+
+ <fullquery name="reinstate_gift_certificate">
+ <querytext>
+ insert into ec_gift_certificate_usage
+ (gift_certificate_id, order_id, amount_reinstated, reinstated_date)
+ values
+ (:gift_certificate_id, :order_id, least(to_number(:certificate_amount_to_reinstate), to_number(:reinstateable_amount)), sysdate)
+ </querytext>
+ </fullquery>
+
+ <fullquery name="insert_cc_refund_problem">
+ <querytext>
+ insert into ec_problems_log
+ (problem_id, problem_date, problem_details, order_id)
+ values
+ (ec_problem_id_sequence.nextval, sysdate, :errorstring, :order_id)
+ </querytext>
+ </fullquery>
+
+ <fullquery name="update_ft_set_success">
+ <querytext>
+ update ec_financial_transactions
+ set refunded_date=sysdate
+ where transaction_id=:pgw_transaction_id
+ </querytext>
+ </fullquery>
+
+ <fullquery name="reschedule_refund">
+ <querytext>
+ update ec_financial_transactions
+ set to_be_captured_date = sysdate
+ where transaction_id = :transaction_id
+ </querytext>
+ </fullquery>
+
+ <fullquery name="select_matching_charge_transaction">
+ <querytext>
+ select * from (
+ select transaction_id as charged_transaction_id, marked_date
+ from ec_financial_transactions
+ where order_id = :order_id
+ and transaction_type = 'charge'
+ and (transaction_amount - :refund_amount) < 0.01
+ and (transaction_amount - :refund_amount) > 0
+ and refunded_amount is null
+ and marked_date is not null
+ and failed_p = 'f'
+ order by transaction_id)
+ where rownum=1
+ </querytext>
+ </fullquery>
+
+ <fullquery name="select_unrefunded_charge_transaction">
+ <querytext>
+ select * from (
+ select transaction_id as charged_transaction_id, (transaction_amount - nvl(refunded_amount, 0)) as unrefunded_amount, marked_date
+ from ec_financial_transactions
+ where order_id = :order_id
+ and transaction_type = 'charge'
+ and (transaction_amount - nvl(refunded_amount, 0)) > 0.01
+ and marked_date is not null
+ and failed_p = 'f'
+ order by (transaction_amount - nvl(refunded_amount, 0)) desc
+ )
+ where rownum=1
+ </querytext>
+ </fullquery>
+
+ <fullquery name="record_refunded_amount">
+ <querytext>
+ update ec_financial_transactions
+ set refunded_amount = nvl(refunded_amount, 0) + :refund_amount
+ where transaction_id = :charged_transaction_id
+ </querytext>
+ </fullquery>
+
+ <fullquery name="record_unrefunded_amount">
+ <querytext>
+ update ec_financial_transactions
+ set refunded_amount = coalesce(refunded_amount, 0) + :unrefunded_amount
+ where transaction_id = :charged_transaction_id
+ </querytext>
+ </fullquery>
+
+ <fullquery name="insert_refund_transaction">
+ <querytext>
+ insert into ec_financial_transactions
+ (transaction_id, refunded_transaction_id, order_id, refund_id, creditcard_id, transaction_amount, transaction_type, inserted_date, to_be_captured_date)
+ values
+ (:refund_transaction_id, :charged_transaction_id, :order_id, :refund_id, :creditcard_id, :refund_amount, 'refund', sysdate, to_date(:scheduled_hour,'YYYY-MM-DD HH12:MI:SSAM'))
+ </querytext>
+ </fullquery>
+
+ <fullquery name="insert_unrefund_transaction">
+ <querytext>
+ insert into ec_financial_transactions
+ (transaction_id, refunded_transaction_id, order_id, refund_id, creditcard_id, transaction_amount, transaction_type, inserted_date, to_be_captured_date)
+ values
+ (:refund_transaction_id, :charged_transaction_id, :order_id, :refund_id, :creditcard_id, :unrefunded_amount, 'refund', sysdate, to_date(:scheduled_hour,'YYYY-MM-DD HH12:MI:SSAM'))
+ </querytext>
+ </fullquery>
+
+</queryset>
Index: openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-return-4-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-return-4-postgresql.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-return-4-postgresql.xql 3 Aug 2005 22:45:46 -0000 1.1
@@ -0,0 +1,238 @@
+<?xml version="1.0"?>
+
+<queryset>
+ <rdbms><type>postgresql</type><version>7.1</version></rdbms>
+
+ <fullquery name="insert_new_ec_refund">
+ <querytext>
+ insert into ec_refunds
+ (refund_id, order_id, refund_amount, refund_date, refunded_by, refund_reasons)
+ values
+ (:refund_id, :order_id, :cash_amount_to_refund, current_timestamp, :customer_service_rep,:reason_for_return)
+ </querytext>
+ </fullquery>
+
+ <fullquery name="get_tax_charged_on_item">
+ <querytext>
+ select coalesce(price_tax_charged,0) as price_tax_charged,
+ coalesce(shipping_tax_charged,0) as shipping_tax_charged
+ from ec_items
+ where item_id=:item_id
+ </querytext>
+ </fullquery>
+
+ <fullquery name="get_tax_charged">
+ <querytext>
+ select ec_tax(:price_bind_variable, 0, :order_id)
+ </querytext>
+ </fullquery>
+
+ <fullquery name="get_base_shipping_tax">
+ <querytext>
+ select coalesce(shipping_tax_charged,0)
+ from ec_orders
+ where order_id=:order_id
+ </querytext>
+ </fullquery>
+
+ <fullquery name="get_tax_shipping_to_refund">
+ <querytext>
+ select ec_tax(0,:shipping_bind_variable,:order_id)
+ </querytext>
+ </fullquery>
+
+ <fullquery name="get_base_tax_to_refund">
+ <querytext>
+ select ec_tax(0,:base_shipping_to_refund,:order_id)
+ </querytext>
+ </fullquery>
+
+ <fullquery name="select_matching_charge_transaction">
+ <querytext>
+ select transaction_id as charged_transaction_id, to_char(marked_date, 'yyyy-mm-dd hh24:mi::ss') as marked_date
+ from ec_financial_transactions
+ where order_id = :order_id
+ and transaction_type = 'charge'
+ and (transaction_amount - :refund_amount) < 0.01::numeric
+ and (transaction_amount - :refund_amount) > 0::numeric
+ and refunded_amount is null
+ and marked_date is not null
+ and failed_p = 'f'
+ order by transaction_id
+ limit 1
+ </querytext>
+ </fullquery>
+
+ <fullquery name="insert_refund_transaction">
+ <querytext>
+ insert into ec_financial_transactions
+ (transaction_id, refunded_transaction_id, order_id, refund_id, creditcard_id, transaction_amount, transaction_type, inserted_date, to_be_captured_date)
+ values
+ (:refund_transaction_id, :charged_transaction_id, :order_id, :refund_id, :creditcard_id, :refund_amount, 'refund', current_timestamp, :scheduled_hour)
+ </querytext>
+ </fullquery>
+
+ <fullquery name="record_refunded_amount">
+ <querytext>
+ update ec_financial_transactions
+ set refunded_amount = coalesce(refunded_amount, 0) + :refund_amount
+ where transaction_id = :charged_transaction_id
+ </querytext>
+ </fullquery>
+
+ <fullquery name="select_unrefunded_charge_transaction">
+ <querytext>
+ select transaction_id as charged_transaction_id, (transaction_amount - coalesce(refunded_amount, 0)) as unrefunded_amount, marked_date
+ from ec_financial_transactions
+ where order_id = :order_id
+ and transaction_type = 'charge'
+ and (transaction_amount - coalesce(refunded_amount, 0)) > 0.01::numeric
+ and marked_date is not null
+ and failed_p = 'f'
+ order by (transaction_amount - coalesce(refunded_amount, 0)) desc
+ limit 1
+ </querytext>
+ </fullquery>
+
+ <fullquery name="insert_unrefund_transaction">
+ <querytext>
+ insert into ec_financial_transactions
+ (transaction_id, refunded_transaction_id, order_id, refund_id, creditcard_id, transaction_amount, transaction_type, inserted_date, to_be_captured_date)
+ values
+ (:refund_transaction_id, :charged_transaction_id, :order_id, :refund_id, :creditcard_id, :unrefunded_amount, 'refund', current_timestamp, :scheduled_hour)
+ </querytext>
+ </fullquery>
+
+ <fullquery name="record_unrefunded_amount">
+ <querytext>
+ update ec_financial_transactions
+ set refunded_amount = coalesce(refunded_amount, 0) + :unrefunded_amount
+ where transaction_id = :charged_transaction_id
+ </querytext>
+ </fullquery>
+
+ <fullquery name="get_gc_amount_used">
+ <querytext>
+ select ec_order_gift_cert_amount(:order_id)
+ </querytext>
+ </fullquery>
+
+ <fullquery name="record_reinstate_problem">
+ <querytext>
+ insert into ec_problems_log
+ (problem_id, problem_date, problem_details, order_id)
+ values
+ (ec_problem_id_sequence.nextval, current_timestamp, :errorstring, :order_id)
+ </querytext>
+ </fullquery>
+
+ <fullquery name="reinstateable_gift_certificates">
+ <querytext>
+ select u.gift_certificate_id, coalesce(sum(u.amount_used),0) - coalesce(sum(u.amount_reinstated),0) as reinstateable_amount
+ from ec_gift_certificate_usage u, ec_gift_certificates c
+ where u.gift_certificate_id = c.gift_certificate_id
+ and u.order_id = :order_id
+ group by u.gift_certificate_id, c.expires
+ order by expires desc, gift_certificate_id desc
+ </querytext>
+ </fullquery>
+
+ <fullquery name="reinstate_gift_certificate">
+ <querytext>
+ insert into ec_gift_certificate_usage
+ (gift_certificate_id, order_id, amount_reinstated, reinstated_date)
+ values
+ (:gift_certificate_id, :order_id, least(:certificate_amount_to_reinstate, :reinstateable_amount), current_timestamp)
+ </querytext>
+ </fullquery>
+
+ <fullquery name="insert_cc_refund_problem">
+ <querytext>
+ insert into ec_problems_log
+ (problem_id, problem_date, problem_details, order_id)
+ values
+ (ec_problem_id_sequence.nextval, current_timestamp, :errorstring, :order_id)
+ </querytext>
+ </fullquery>
+
+ <fullquery name="update_ft_set_success">
+ <querytext>
+ update ec_financial_transactions
+ set refunded_date = current_timestamp
+ where transaction_id=:pgw_transaction_id
+ </querytext>
+ </fullquery>
+
+ <fullquery name="reschedule_refund">
+ <querytext>
+ update ec_financial_transactions
+ set to_be_captured_date = current_timestamp
+ where transaction_id = :transaction_id
+ </querytext>
+ </fullquery>
+
+ <fullquery name="select_matching_charge_transaction">
+ <querytext>
+ select transaction_id as charged_transaction_id, marked_date
+ from ec_financial_transactions
+ where order_id = :order_id
+ and transaction_type = 'charge'
+ and (transaction_amount - :refund_amount) < 0.01::numeric
+ and (transaction_amount - :refund_amount) > 0::numeric
+ and refunded_amount is null
+ and marked_date is not null
+ and failed_p = 'f'
+ order by transaction_id
+ limit 1
+ </querytext>
+ </fullquery>
+
+ <fullquery name="select_unrefunded_charge_transaction">
+ <querytext>
+ select transaction_id as charged_transaction_id, (transaction_amount - coalesce(refunded_amount, 0)) as unrefunded_amount, marked_date
+ from ec_financial_transactions
+ where order_id = :order_id
+ and transaction_type = 'charge'
+ and (transaction_amount - coalesce(refunded_amount, 0)) > 0.01::numeric
+ and marked_date is not null
+ and failed_p = 'f'
+ order by (transaction_amount - coalesce(refunded_amount, 0)) desc
+ limit 1
+ </querytext>
+ </fullquery>
+
+ <fullquery name="record_refunded_amount">
+ <querytext>
+ update ec_financial_transactions
+ set refunded_amount = coalesce(refunded_amount, 0) + :refund_amount
+ where transaction_id = :charged_transaction_id
+ </querytext>
+ </fullquery>
+
+ <fullquery name="record_unrefunded_amount">
+ <querytext>
+ update ec_financial_transactions
+ set refunded_amount = coalesce(refunded_amount, 0) + :unrefunded_amount
+ where transaction_id = :charged_transaction_id
+ </querytext>
+ </fullquery>
+
+ <fullquery name="insert_refund_transaction">
+ <querytext>
+ insert into ec_financial_transactions
+ (transaction_id, refunded_transaction_id, order_id, refund_id, creditcard_id, transaction_amount, transaction_type, inserted_date, to_be_captured_date)
+ values
+ (:refund_transaction_id, :charged_transaction_id, :order_id, :refund_id, :creditcard_id, :refund_amount, 'refund', current_timestamp, :scheduled_hour)
+ </querytext>
+ </fullquery>
+
+ <fullquery name="insert_unrefund_transaction">
+ <querytext>
+ insert into ec_financial_transactions
+ (transaction_id, refunded_transaction_id, order_id, refund_id, creditcard_id, transaction_amount, transaction_type, inserted_date, to_be_captured_date)
+ values
+ (:refund_transaction_id, :charged_transaction_id, :order_id, :refund_id, :creditcard_id, :unrefunded_amount, 'refund', current_timestamp, :scheduled_hour)
+ </querytext>
+ </fullquery>
+
+</queryset>
Index: openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-return-4.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-return-4.tcl,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-return-4.tcl 3 Aug 2005 22:45:46 -0000 1.1
@@ -0,0 +1,517 @@
+ad_page_contract {
+
+ This script does the following:
+ 1. tries to get credit card number (insert it if new)
+ 2. puts records into ec_refunds, individual items, the order, and
+ ec_financial transactions
+ 3. does the gift certificate reinstatements
+ 4. tries to do refund
+
+ @param refund_id
+ @param order_id
+ @param received_back_datetime
+ @param reason_for_return
+ @param item_id_list
+ @param price_to_refund
+ @param shipping_to_refund
+ @param base_shipping_to_refund
+ @param cash_amount_to_refund
+ @param certificate_amount_to_reinstate
+
+ @param creditcard_id
+ @param creditcard_type
+ @param creditcard_number
+
+ @author Eve Andersson (eveander@arsdigita.com)
+ @creation-date July 22, 1999
+ @cvs-id $Id: items-return-4.tcl,v 1.1 2005/08/03 22:45:46 roelc Exp $
+ @author ported by Jerry Asher (jerry@theashergroup.com)
+} {
+ refund_id:naturalnum,notnull
+ order_id:naturalnum,notnull
+ received_back_datetime
+ reason_for_return
+ item_id_list:notnull
+ price_to_refund:array
+ shipping_to_refund:array
+ base_shipping_to_refund
+ cash_amount_to_refund:optional
+ certificate_amount_to_reinstate
+
+ creditcard_id:optional
+ creditcard_type:optional
+ creditcard_number:optional
+ creditcard_last_four:optional
+}
+
+# The customer service rep must be logged on and have admin
+# privileges.
+
+ad_require_permission [ad_conn package_id] admin
+set customer_service_rep [ad_get_user_id]
+
+# Get rid of spaces and dashes
+
+regsub -all -- "-" $creditcard_number "" creditcard_number
+regsub -all -- " " $creditcard_number "" creditcard_number
+
+# Error checking: unless the credit card number is in the database or
+# if the total amount to refund is $0.00 the credit card number needs
+# to be re-entered.
+
+set exception_count 0
+set exception_text ""
+
+# Make sure that this refund hasn't been processed before. (Double
+# click prevention.)
+
+if { [db_string get_refund_id_check "
+ select count(*)
+ from ec_refunds
+ where refund_id=:refund_id"] > 0 } {
+ ad_return_complaint 1 "
+ <li>This refund has already been inserted into the database. Are you using an old form? <a href=\"one?[export_url_vars order_id]\">Return to the order.</a></li>"
+ return
+}
+
+# Check if money needs to be refunded and if the credit card number is
+# still on file.
+
+if { [expr $cash_amount_to_refund] > 0 } {
+
+ # Make sure that all the credit card information is there.
+
+ if {![info exists creditcard_id] || ([info exists creditcard_id] && [empty_string_p $creditcard_id])} {
+ incr exception_count
+ append exception_text "
+ <li>
+ You forgot to provide the creditcard that was used to purchase the items to be returned.
+ </li>"
+ }
+ if {![info exists creditcard_type] || ([info exists creditcard_type] && [empty_string_p $creditcard_type])} {
+ incr exception_count
+ append exception_text "
+ <li>
+ You forgot to provide type of the creditcard that was used to purchase the items to be returned.
+ </li>"
+ }
+ if {![info exists creditcard_number] || ([info exists creditcard_number] && [empty_string_p $creditcard_number])} {
+ incr exception_count
+ append exception_text "
+ <li>
+ You forgot to provide card number of the creditcard that was used to purchase the items to be returned.
+ </li>"
+ }
+ if {![info exists creditcard_last_four] || ([info exists creditcard_last_four] && [empty_string_p $creditcard_last_four])} {
+ incr exception_count
+ append exception_text "
+ <li>
+ You forgot to provide card number of the creditcard that was used to purchase the items to be returned.
+ </li>"
+ }
+
+ # The credit card has been re-entered. Check that the number is
+ # correct.
+
+ if { [regexp {[^0-9]} $creditcard_number] } {
+ incr exception_count
+ append exception_text "<li>The credit card number contains invalid characters.</li>"
+ }
+
+ if {[string length $creditcard_number] > 4 && ![string match *$creditcard_last_four $creditcard_number]} {
+ incr exception_count
+ append exception_text "<li>The last for digits of the credit card number do not match the digits on file.<br>
+ Make sure to enter the card number of the credit card that was used to pay for the order.</li>"
+ }
+
+ if {[info exists creditcard_type]} {
+
+ # Make sure the credit card number matches the credit card
+ # type # and that the number has the right number of digits.
+
+ set additional_count_and_text [ec_creditcard_precheck $creditcard_number $creditcard_type]
+
+ set exception_count [expr $exception_count + [lindex $additional_count_and_text 0]]
+ append exception_text [lindex $additional_count_and_text 1]
+ }
+}
+
+if { $exception_count > 0 } {
+ ad_return_complaint $exception_count $exception_text
+ return
+}
+
+# Done with error checking
+
+# 2. Put records into ec_refunds, individual items, the order, and
+# ec_financial_transactions
+
+db_dml update_cc_number_incctable "
+ update ec_creditcards
+ set creditcard_number=:creditcard_number
+ where creditcard_id=:creditcard_id"
+db_dml insert_new_ec_refund "
+ insert into ec_refunds
+ (refund_id, order_id, refund_amount, refund_date, refunded_by, refund_reasons)
+ values
+ (:refund_id, :order_id, :cash_amount_to_refund, sysdate, :customer_service_rep,:reason_for_return)"
+
+foreach item_id $item_id_list {
+
+ # This is annoying (doing these selects before each insert),
+ # but that's how it goes because we don't want to refund more
+ # tax than was actually paid even if the tax rates changed
+
+ set price_bind_variable $price_to_refund($item_id)
+ set shipping_bind_variable $shipping_to_refund($item_id)
+
+ db_1row get_tax_charged_on_item "
+ select nvl(price_tax_charged,0) as price_tax_charged, nvl(shipping_tax_charged,0) as shipping_tax_charged
+ from ec_items
+ where item_id=:item_id"
+
+# torben diagnostics note: following calls for ec_tax, but ec_tax does not exist.
+ set price_tax_to_refund [ec_min $price_tax_charged [db_string get_tax_charged "
+ select ec_tax(:price_bind_variable,0,:order_id)
+ from dual"]]
+
+ set shipping_tax_to_refund [ec_min $shipping_tax_charged [db_string get_tax_shipping_to_refund "
+ select ec_tax(0,:shipping_bind_variable,:order_id)
+ from dual"]]
+
+ db_dml update_item_return "
+ update ec_items
+ set item_state='received_back', received_back_date=to_date(:received_back_datetime,'YYYY-MM-DD HH12:MI:SSAM'), price_refunded=:price_bind_variable,
+ shipping_refunded=:shipping_bind_variable, price_tax_refunded=:price_tax_to_refund, shipping_tax_refunded=:shipping_tax_to_refund, refund_id=:refund_id
+ where item_id=:item_id"
+}
+
+set base_shipping_tax_charged [db_string get_base_shipping_tax "
+ select nvl(shipping_tax_charged,0)
+ from ec_orders
+ where order_id=:order_id"]
+set base_shipping_tax_to_refund [ec_min $base_shipping_tax_charged [db_string get_base_tax_to_refund "
+ select ec_tax(0,:base_shipping_to_refund,:order_id)
+ from dual"]]
+db_dml update_ec_order_set_shipping_refunds "
+ update ec_orders
+ set shipping_refunded=:base_shipping_to_refund, shipping_tax_refunded=:base_shipping_tax_to_refund
+ where order_id=:order_id"
+
+# Match the refund up with prior charge transactions. Some payment
+# gateways such Authorize.net require that each refund is linked
+# to a prior charge transaction and that the refund amount does
+# not exceed the amount of the charge transaction. The refund
+# amount can exceed the charge amount when the order was shipped
+# in parts and the customer returned items from various shipments.
+
+set refund_amount $cash_amount_to_refund
+while { $refund_amount > 0 } {
+
+ # See if the refund matches a single charge transaction. The
+ # test < 0.01 is needed for reasons of rounding errors.
+
+ if {[db_0or1row select_matching_charge_transaction "
+ select transaction_id as charged_transaction_id, marked_date
+ from ec_financial_transactions
+ where order_id = :order_id
+ and transaction_type = 'charge'
+ and (transaction_amount - :refund_amount) < 0.01::numeric
+ and (transaction_amount - :refund_amount) > 0::numeric
+ and refunded_amount is null
+ and marked_date is not null
+ and failed_p = 'f'
+ order by transaction_id
+ limit 1"]} {
+
+ # Create a single refund financial transaction.
+
+ set refund_transaction_id [db_nextval ec_transaction_id_sequence]
+
+ # Authorize.net is an example of a payment gateway that requires
+ # the original transaction to be settled before it accepts refunds
+ # for the transaction. Unfortunately there is no automated way to
+ # find out if the transaction has been settled.
+
+ # However, transactions are settled once a day (by all gateways)
+ # thus it is safe to assume that transactions are settled within
+ # 24 hours after they have been marked for settlement.
+
+ set 24hr [expr 24 * 60 * 60]
+ set time_since_marking [expr [clock seconds] - [clock scan $marked_date]]
+ if { $time_since_marking > $24hr } {
+ set scheduled_hour [clock format [clock scan $marked_date] -format "%Y-%m-%d %H:%M:%S" -gmt true]
+ } else {
+
+ # It is too early to perform the refund now. First the
+ # original transaction needs to be settled by the payment
+ # gateway. Schedule the refund for 24 hours after the original
+ # transaction was marked for settlement. The procedure
+ # ec_unrefunded_transactions will then perform the shortly
+ # after the scheduled hour.
+
+ set scheduled_hour [clock format [expr [clock scan $marked_date] + $24hr] -format "%Y-%m-%d %H:%M:%S" -gmt true]
+ }
+# torben diagnostics note: following insert does not make it into ec_financial_transactions.
+ db_dml insert_refund_transaction "
+ insert into ec_financial_transactions
+ (transaction_id, refunded_transaction_id, order_id, refund_id, creditcard_id, transaction_amount, transaction_type, inserted_date, to_be_captured_date)
+ values
+ (:refund_transaction_id, :charged_transaction_id, :order_id, :refund_id, :creditcard_id, :refund_amount, 'refund', sysdate, :scheduled_hour)"
+
+ # Record the amount that was refunded of the charge transaction.
+
+ db_dml record_refunded_amount "
+ update ec_financial_transactions
+ set refunded_amount = coalesce(refunded_amount, 0) + :refund_amount
+ where transaction_id = :charged_transaction_id"
+
+ # Set the amount to be refunded to zero to indicate that
+ # no more refunds are needed.
+
+ set refund_amount 0
+ } elseif {[db_0or1row select_unrefunded_charge_transaction "
+ select transaction_id as charged_transaction_id, (transaction_amount - coalesce(refunded_amount, 0)) as unrefunded_amount, marked_date
+ from ec_financial_transactions
+ where order_id = :order_id
+ and transaction_type = 'charge'
+ and (transaction_amount - coalesce(refunded_amount, 0)) > 0.01::numeric
+ and marked_date is not null
+ and failed_p = 'f'
+ order by (transaction_amount - coalesce(refunded_amount, 0)) desc
+ limit 1"]} {
+
+ if { $unrefunded_amount >= $refund_amount } {
+
+ # Create refund financial transaction for the refund
+ # amount.
+
+ set refund_transaction_id [db_nextval ec_transaction_id_sequence]
+
+ # Authorize.net is an example of a payment gateway that requires
+ # the original transaction to be settled before it accepts refunds
+ # for the transaction. Unfortunately there is no automated way to
+ # find out if the transaction has been settled.
+
+ # However, transactions are settled once a day (by all gateways)
+ # thus it is safe to assume that transactions are settled within
+ # 24 hours after they have been marked for settlement.
+
+ set 24hr [expr 24 * 60 * 60]
+ set time_since_marking [expr [clock seconds] - [clock scan $marked_date]]
+ if { $time_since_marking > $24hr } {
+ set scheduled_hour [clock format [clock scan $marked_date] -format "%Y-%m-%d %H:%M:%S" -gmt true]
+ } else {
+
+ # It is too early to perform the refund now. First the
+ # original transaction needs to be settled by the payment
+ # gateway. Schedule the refund for 24 hours after the original
+ # transaction was marked for settlement. The procedure
+ # ec_unrefunded_transactions will then perform the shortly
+ # after the scheduled hour.
+
+ set scheduled_hour [clock format [expr [clock scan $marked_date] + $24hr] -format "%Y-%m-%d %H:%M:%S" -gmt true]
+ }
+
+ db_dml insert_refund_transaction "
+ insert into ec_financial_transactions
+ (transaction_id, refunded_transaction_id, order_id, refund_id, creditcard_id, transaction_amount, transaction_type, inserted_date, to_be_captured_date)
+ values
+ (:refund_transaction_id, :charged_transaction_id, :order_id, :refund_id, :creditcard_id, :refund_amount, 'refund', current_timestamp, :scheduled_hour)"
+
+ # Record the amount that was refunded of the charge transaction.
+
+ db_dml record_refunded_amount "
+ update ec_financial_transactions
+ set refunded_amount = coalesce(refunded_amount, 0) + :refund_amount
+ where transaction_id = :charged_transaction_id"
+
+ # Set the amount to be refunded to zero to indicate that
+ # no more refunds are needed.
+
+ set refund_amount 0
+ } else {
+
+ # Create refund financial transaction for the unrefunded
+ # amount of the charge transaction.
+
+ set refund_transaction_id [db_nextval ec_transaction_id_sequence]
+
+ # Authorize.net is an example of a payment gateway that requires
+ # the original transaction to be settled before it accepts refunds
+ # for the transaction. Unfortunately there is no automated way to
+ # find out if the transaction has been settled.
+
+ # However, transactions are settled once a day (by all gateways)
+ # thus it is safe to assume that transactions are settled within
+ # 24 hours after they have been marked for settlement.
+
+ set 24hr [expr 24 * 60 * 60]
+ set time_since_marking [expr [clock seconds] - [clock scan $marked_date]]
+ if { $time_since_marking > $24hr } {
+ set scheduled_hour [clock format [clock scan $marked_date] -format "%Y-%m-%d %H:%M:%S" -gmt true]
+ } else {
+
+ # It is too early to perform the refund now. First the
+ # original transaction needs to be settled by the payment
+ # gateway. Schedule the refund for 24 hours after the original
+ # transaction was marked for settlement. The procedure
+ # ec_unrefunded_transactions will then perform the shortly
+ # after the scheduled hour.
+
+ set scheduled_hour [clock format [expr [clock scan $marked_date] + $24hr] -format "%Y-%m-%d %H:%M:%S" -gmt true]
+ }
+
+ db_dml insert_unrefund_transaction "
+ insert into ec_financial_transactions
+ (transaction_id, refunded_transaction_id, order_id, refund_id, creditcard_id, transaction_amount, transaction_type, inserted_date, to_be_captured_date)
+ values
+ (:refund_transaction_id, :charged_transaction_id, :order_id, :refund_id, :creditcard_id, :unrefunded_amount, 'refund', sysdate, :scheduled_hour)"
+
+ # Record the amount that was refunded of the charge transaction.
+
+ db_dml record_unrefunded_amount "
+ update ec_financial_transactions
+ set refunded_amount = coalesce(refunded_amount, 0) + :unrefunded_amount
+ where transaction_id = :charged_transaction_id"
+
+ # Subtract the amount of the new refund transaction
+ # from the total amount to be refunded.
+
+ set refund_amount [expr $refund_amount - $unrefunded_amount]
+ }
+ }
+}
+
+# 3. do the gift certificate reinstatements (start with ones that
+# expire furthest in to future)
+
+if { $certificate_amount_to_reinstate > 0 } {
+
+ set certificate_amount_used [db_string get_gc_amount_used "
+ select ec_order_gift_cert_amount(:order_id)
+ from dual"]
+
+ if { $certificate_amount_used < $certificate_amount_to_reinstate } {
+ set errorstring "
+ We were unable to reinstate the customer's gift certificate balance because the amount to be reinstated was
+ larger than the original amount used. This shouldn't have happened unless there was a programming error or unless the
+ database was incorrectly updated manually. This transaction was aborted (refund_id $refund_id), i.e. no refund was
+ given to the customer."
+ db_dml record_reinstate_problem "
+ insert into ec_problems_log
+ (problem_id, problem_date, problem_details, order_id)
+ values
+ (ec_problem_id_sequence.nextval, sysdate, :errorstring, :order_id)"
+ ad_return_error "
+ <p>Gift Certificate Error" "We were unable to reinstate the customer's gift certificate balance because the amount
+ to be reinstated was larger than the original amount used. This shouldn't have happened unless there was a programming error
+ or unless the database was incorrectly updated manually.</p>
+ <p>This transaction has been aborted, i.e. no refund has been given to the customer. This has been logged in the problems log.</p>"
+ return
+ }
+
+ # Go through and reinstate certificates in order; it's not so bad
+ # to loop through all of them because there won't be many.
+
+ db_foreach reinstateable_gift_certificates "
+ select u.gift_certificate_id, coalesce(sum(u.amount_used),0) - coalesce(sum(u.amount_reinstated),0) as reinstateable_amount
+ from ec_gift_certificate_usage u, ec_gift_certificates c
+ where u.gift_certificate_id = c.gift_certificate_id
+ and u.order_id = :order_id
+ group by u.gift_certificate_id, c.expires
+ order by expires desc, gift_certificate_id desc" {
+
+ if {$certificate_amount_to_reinstate > 0} {
+ db_dml reinstate_gift_certificate "
+ insert into ec_gift_certificate_usage
+ (gift_certificate_id, order_id, amount_reinstated, reinstated_date)
+ values
+ (:gift_certificate_id, :order_id, least(to_number(:certificate_amount_to_reinstate), to_number(:reinstateable_amount)) , sysdate)"
+ set $certificate_amount_to_reinstate [expr $certificate_amount_to_reinstate - \
+ [expr ($certificate_amount_to_reinstate > $reinstateable_amount) ? $reinstateable_amount : $certificate_amount_to_reinstate]]
+ }
+ }
+}
+
+# 4. Try to do the refund(s)
+
+if {$cash_amount_to_refund > 0} {
+ set page_title "Refund results"
+ set results_explanation ""
+ db_foreach select_unrefund_transactions "
+ select transaction_id, transaction_amount, refunded_transaction_id, to_be_captured_date
+ from ec_financial_transactions
+ where order_id = :order_id
+ and transaction_type = 'refund'
+ and refunded_date is null
+ and failed_p = 'f'" {
+
+ set now [clock format [clock seconds] -format "%Y-%m-%d %H:%M:%S" -gmt true]
+ if { [dt_interval_check $to_be_captured_date $now] > 0} {
+
+ array set response [ec_creditcard_return $transaction_id]
+ set refund_status $response(response_code)
+ set pgw_transaction_id $response(transaction_id)
+ if { $refund_status == "failure" || $refund_status == "invalid_input" } {
+ set errorstring "Refund transaction $transaction_id for [ec_pretty_price $transaction_amount] of refund $refund_id at [ad_conn url], resulted in: $refund_status"
+ db_dml insert_cc_refund_problem "
+ insert into ec_problems_log
+ (problem_id, problem_date, problem_details, order_id)
+ values
+ (ec_problem_id_sequence.nextval, sysdate, :errorstring, :order_id)"
+ append results_explanation "<p>Refund transaction $transaction_id for [ec_pretty_price $transaction_amount] did not occur.
+ We have made a record of this in the problems log so that the situation can be corrected manually.</p>"
+ } elseif { $refund_status == "inconclusive" } {
+
+ # Set the to_be_captured_date so that the scheduled
+ # procedure ec_unrefunded_transactions will retry the
+ # transaction.
+
+ append results_explanation "<p>The results of refund transaction $transaction_id for [ec_pretty_price $transaction_amount] were inconclusive
+ (perhaps due to a communications failure between us and the payment gateway).
+ A program will keep trying to complete this refund transaction and the problems log will be updated if it the refund transaction cannot be completed.</p>"
+ } else {
+
+ # Refund successful
+
+ db_dml update_ft_set_success "
+ update ec_financial_transactions
+ set refunded_date=sysdate
+ where transaction_id=:pgw_transaction_id"
+ append results_explanation "<p>Refund transaction $pgw_transaction_id for [ec_pretty_price $transaction_amount] is complete!</p>";#
+ }
+ } else {
+
+ # It is too early to perform the refund now. First the
+ # original transaction needs to be settled by the payment
+ # gateway.
+
+ append results_explanation "<p>Refund transaction $transaction_id for [ec_pretty_price $transaction_amount] is scheduled for a later time.
+ Refunds can not be processed before the transaction charging the credit card has been completed by the gateway.
+ Transactions are completed with 24 hours after marking. Therefore the refund transaction has been scheduled for $to_be_captured_date</p>"
+
+ }
+ } if_no_rows {
+ set page_title "No credit card refund needed."
+ set results_explanation "No credit card refund was necessary because the entire amount was refunded to the gift certificates the customer used when purchasing the order."
+ }
+} else {
+ set page_title "No credit card refund needed."
+ set results_explanation "No credit card refund was necessary because the entire amount was refunded to the gift certificates the customer used when purchasing the order."
+}
+
+append doc_body "
+ [ad_admin_header $page_title]
+
+ <h2>$page_title</h2>
+
+ [ad_context_bar [list "../" "Ecommerce([ec_system_name])"] [list "index" "Orders"] [list "one?[export_url_vars order_id]" "One"] "Refund Complete"]
+ <hr>
+ <blockquote>
+ $results_explanation
+ <a href=\"one?[export_url_vars order_id]\">Back to Order $order_id</a>
+ </blockquote>
+ [ad_admin_footer]"
+
+doc_return 200 text/html $doc_body
Index: openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-return-4.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-return-4.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-return-4.xql 3 Aug 2005 22:45:46 -0000 1.1
@@ -0,0 +1,107 @@
+<?xml version="1.0"?>
+<queryset>
+
+ <fullquery name="get_user_id">
+ <querytext>
+ select user_id
+ from ec_orders
+ where order_id=:order_id
+ </querytext>
+ </fullquery>
+
+ <fullquery name="get_refund_id_check">
+ <querytext>
+ select count(*)
+ from ec_refunds
+ where refund_id=:refund_id
+ </querytext>
+ </fullquery>
+
+ <fullquery name="get_credit_card_id">
+ <querytext>
+ select creditcard_number
+ from ec_orders o, ec_creditcards c
+ where o.creditcard_id=c.creditcard_id
+ and o.order_id=:order_id
+ </querytext>
+ </fullquery>
+
+ <fullquery name="get_creditcard_id">
+ <querytext>
+ select creditcard_id
+ from ec_orders
+ where order_id=:order_id
+ </querytext>
+ </fullquery>
+
+ <fullquery name="get_transaction_to_query">
+ <querytext>
+ select max(transaction_id)
+ from ec_financial_transactions
+ where creditcard_id=:creditcard_id
+ and (authorized_date is not null or 0=(select count(*)
+ from ec_financial_transactions
+ where creditcard_id=:creditcard_id
+ and authorized_date is not null)
+ </querytext>
+ </fullquery>
+
+ <fullquery name="get_creditcard_id">
+ <querytext>
+ select creditcard_id
+ from ec_orders
+ where order_id=:order_id
+ </querytext>
+ </fullquery>
+
+ <fullquery name="update_cc_number_incctable">
+ <querytext>
+ update ec_creditcards
+ set creditcard_number=:creditcard_number
+ where creditcard_id=:creditcard_id
+ </querytext>
+ </fullquery>
+
+ <fullquery name="insert_new_cc">
+ <querytext>
+ insert into ec_creditcards
+ (creditcard_id, user_id, creditcard_number, creditcard_last_four, creditcard_type, creditcard_expire, billing_address)
+ values
+ (:creditcard_id, :user_id, :creditcard_number, :cc_thing, :creditcard_type, :expires, :address_id)
+ </querytext>
+ </fullquery>
+
+ <fullquery name="update_item_return">
+ <querytext>
+ update ec_items
+ set item_state='received_back',
+ received_back_date=to_date(:received_back_datetime,'YYYY-MM-DD HH12:MI:SSAM'),
+ price_refunded=:price_bind_variable,
+ shipping_refunded=:shipping_bind_variable,
+ price_tax_refunded=:price_tax_to_refund,
+ shipping_tax_refunded=:shipping_tax_to_refund,
+ refund_id=:refund_id
+ where item_id=:item_id
+ </querytext>
+ </fullquery>
+
+ <fullquery name="update_ec_order_set_shipping_refunds">
+ <querytext>
+ update ec_orders
+ set shipping_refunded=:base_shipping_to_refund, shipping_tax_refunded=:base_shipping_tax_to_refund
+ where order_id=:order_id
+ </querytext>
+ </fullquery>
+
+ <fullquery name="select_unrefund_transactions">
+ <querytext>
+ select transaction_id, transaction_amount, refunded_transaction_id, to_be_captured_date
+ from ec_financial_transactions
+ where order_id = :order_id
+ and transaction_type = 'refund'
+ and refunded_date is null
+ and failed_p = 'f'
+ </querytext>
+ </fullquery>
+
+</queryset>
Index: openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-return.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-return.tcl,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-return.tcl 3 Aug 2005 22:45:46 -0000 1.1
@@ -0,0 +1,62 @@
+ad_page_contract {
+
+ Return items.
+
+ @author Eve Andersson (eveander@arsdigita.com)
+ @creation-date Summer 1999
+ @author ported by Jerry Asher (jerry@theashergroup.com)
+ @author revised by Bart Teeuwisse (bart.teeuwisse@thecodemill.biz)
+ @revision-date April 2002
+
+} {
+ order_id:integer,notnull
+}
+
+ad_require_permission [ad_conn package_id] admin
+
+# In case they reload this page after completing the refund process:
+
+if { [db_string doubleclick_select "
+ select count(*)
+ from ec_items_refundable
+ where order_id=:order_id"] == 0 } {
+
+ ad_return_complaint 1 "
+ <li>This order doesn't contain any refundable items; perhaps you are using an old form. <a href=\"one?[export_url_vars order_id]\">Return to the order.</a>"
+ return
+}
+
+doc_body_append "
+ [ad_admin_header "Mark Items Returned"]
+
+ <h2>Mark Items Returned</h2>
+
+ [ad_context_bar [list "../" "Ecommerce([ec_system_name])"] [list "index" "Orders"] [list "one?order_id=$order_id" "One Order"] "Mark Items Returned"]
+ <hr>"
+
+# Generate the new refund_id to prevent reusing this form.
+
+set refund_id [db_nextval refund_id_sequence]
+
+doc_body_append "
+ <form method=post action=items-return-2>
+ [export_form_vars order_id refund_id]
+
+ <blockquote>
+ <p>Date received back: [ad_dateentrywidget received_back_date] [ec_timeentrywidget received_back_time]</p>
+
+ <p>Please check off the items that were received back:</p>
+ <blockquote>
+ [ec_items_for_fulfillment_or_return $order_id "f"]
+ </blockquote>
+
+ <p>Reason for return (if known):</p>
+ <blockquote>
+ <textarea name=reason_for_return rows=5 cols=50 wrap></textarea>
+ </blockquote>
+ </blockquote>
+
+ <center><input type=submit value=\"Continue\"></center>
+ </form>
+
+ [ad_admin_footer]"
Index: openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-return.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-return.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-return.xql 3 Aug 2005 22:45:46 -0000 1.1
@@ -0,0 +1,11 @@
+<?xml version="1.0"?>
+<queryset>
+
+<fullquery name="doubleclick_select">
+ <querytext>
+ select count(*) from ec_items_refundable where order_id=:order_id
+ </querytext>
+</fullquery>
+
+
+</queryset>
Index: openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-void-2-oracle.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-void-2-oracle.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-void-2-oracle.xql 3 Aug 2005 22:45:46 -0000 1.1
@@ -0,0 +1,70 @@
+<?xml version="1.0"?>
+
+<queryset>
+ <rdbms><type>oracle</type><version>8.1.6</version></rdbms>
+
+<fullquery name="gift_certificate_amount_select">
+ <querytext>
+ select ec_order_gift_cert_amount(:order_id) from dual
+ </querytext>
+</fullquery>
+
+
+<fullquery name="item_state_update">
+ <querytext>
+ update ec_items set item_state='void', voided_date=sysdate, voided_by=:customer_service_rep where item_id in ([join $item_id_list ", "])
+ </querytext>
+</fullquery>
+
+
+<fullquery name="item_state_update2">
+ <querytext>
+ update ec_items set item_state='void', voided_date=sysdate, voided_by=:customer_service_rep where order_id=:order_id and product_id=:product_id
+ </querytext>
+</fullquery>
+
+
+<fullquery name="amount_charged_minus_refunded_for_nonvoid_items_select">
+ <querytext>
+ select nvl(sum(nvl(price_charged,0)) + sum(nvl(shipping_charged,0)) + sum(nvl(price_tax_charged,0)) + sum(nvl(shipping_tax_charged,0)) - sum(nvl(price_refunded,0)) - sum(nvl(shipping_refunded,0)) + sum(nvl(price_tax_refunded,0)) - sum(nvl(shipping_tax_refunded,0)),0) from ec_items where item_state <> 'void' and order_id=:order_id
+ </querytext>
+</fullquery>
+
+
+<fullquery name="certificate_amount_used_select">
+ <querytext>
+ select ec_order_gift_cert_amount(:order_id) from dual
+ </querytext>
+</fullquery>
+
+
+<fullquery name="problems_log_insert">
+ <querytext>
+ insert into ec_problems_log
+ (problem_id, problem_date, problem_details, order_id)
+ values
+ (ec_problem_id_sequence.nextval, sysdate, 'We were unable to reinstate the customer''s gift certificate balance because the amount to be reinstated was larger than the original amount used. This shouldn''t have happened unless there was a programming error or unless the database was incorrectly updated manually. The voiding of this order has been aborted.', :order_id)
+
+ </querytext>
+</fullquery>
+
+
+<fullquery name="reinstatable_amount_select">
+ <querytext>
+ select ec_one_gift_cert_on_one_order(:cert,:order_id) from dual
+ </querytext>
+</fullquery>
+
+
+<fullquery name="reinstate_gift_certificate_insert">
+ <querytext>
+ insert into ec_gift_certificate_usage
+ (gift_certificate_id, order_id, amount_reinstated, reinstated_date)
+ values
+ (:cert, :order_id, :iteration_reinstate_amount, sysdate)
+
+ </querytext>
+</fullquery>
+
+
+</queryset>
Index: openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-void-2-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-void-2-postgresql.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-void-2-postgresql.xql 3 Aug 2005 22:45:46 -0000 1.1
@@ -0,0 +1,63 @@
+<?xml version="1.0"?>
+
+<queryset>
+ <rdbms><type>postgresql</type><version>7.1</version></rdbms>
+
+<fullquery name="gift_certificate_amount_select">
+ <querytext>
+ select ec_order_gift_cert_amount(:order_id)
+ </querytext>
+</fullquery>
+
+
+<fullquery name="item_state_update">
+ <querytext>
+ update ec_items set item_state='void', voided_date=current_timestamp, voided_by=:customer_service_rep where item_id in ([join $item_id_list ", "])
+ </querytext>
+</fullquery>
+
+
+<fullquery name="item_state_update2">
+ <querytext>
+ update ec_items set item_state='void', voided_date=current_timestamp, voided_by=:customer_service_rep where order_id=:order_id and product_id=:product_id
+ </querytext>
+</fullquery>
+
+
+<fullquery name="certificate_amount_used_select">
+ <querytext>
+ select ec_order_gift_cert_amount(:order_id)
+ </querytext>
+</fullquery>
+
+
+<fullquery name="problems_log_insert">
+ <querytext>
+ insert into ec_problems_log
+ (problem_id, problem_date, problem_details, order_id)
+ values
+ (ec_problem_id_sequence.nextval, current_timestamp, 'We were unable to reinstate the customer''s gift certificate balance because the amount to be reinstated was larger than the original amount used. This shouldn''t have happened unless there was a programming error or unless the database was incorrectly updated manually. The voiding of this order has been aborted.', :order_id)
+
+ </querytext>
+</fullquery>
+
+
+<fullquery name="reinstatable_amount_select">
+ <querytext>
+ select ec_one_gift_cert_on_one_order(:cert,:order_id)
+ </querytext>
+</fullquery>
+
+
+<fullquery name="reinstate_gift_certificate_insert">
+ <querytext>
+ insert into ec_gift_certificate_usage
+ (gift_certificate_id, order_id, amount_reinstated, reinstated_date)
+ values
+ (:cert, :order_id, :iteration_reinstate_amount, current_timestamp)
+
+ </querytext>
+</fullquery>
+
+
+</queryset>
Index: openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-void-2.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-void-2.tcl,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-void-2.tcl 3 Aug 2005 22:45:46 -0000 1.1
@@ -0,0 +1,217 @@
+ad_page_contract {
+
+ Void items.
+
+ @author Eve Andersson (eveander@arsdigita.com)
+ @creation-date Summer 1999
+ @author ported by Jerry Asher (jerry@theashergroup.com)
+ @author revised by Bart Teeuwisse (bart.teeuwisse@thecodemill.biz)
+ @revision-date April 2002
+
+} {
+ order_id:integer,notnull
+ product_id:integer,notnull
+ item_id:multiple,optional
+}
+
+ad_require_permission [ad_conn package_id] admin
+
+set customer_service_rep [ad_get_user_id]
+
+# See if there's a gift certificate amount applied to this order
+# that's being tied up by unshipped items, in which case we may need
+# to reinstate some or all of it.
+
+# The equations are:
+# (tied up g.c. amount) = (g.c. bal applied to order) - (amount paid for shipped items)
+# + (amount refunded for shipped items)
+# (amount to be reinstated for to-be-voided items) = (tied up g.c. amount)
+# - (total cost of unshipped items)
+# + (cost of to-be-voided items)
+#
+# So, (amount to be reinstated) = (g.c. bal applied to order) - (amount paid for shipped items)
+# + (amount refunded for shipped items) - (total cost of unshipped items) + cost of to-be-voided items)
+# = (g.c. bal applied to order) - (total amount for all nonvoid items in the order, incl the ones that are about to be voided)
+# + (total amount refunded on nonvoid items)
+# + (cost of to-be-voided items)
+# = (g.c. bal applied to order) - (total amount for all nonvoid items in the order after these are voided)
+# + total amount refunded on nonvoid items
+
+# This equation is now officially simple to solve. G.c. balance
+# should be calculated first, then things should be voided, then final
+# calculation should be made and g.c.'s should be reinstated.
+
+db_transaction {
+
+ set gift_certificate_amount [db_string gift_certificate_amount_select "
+ select ec_order_gift_cert_amount(:order_id)
+ from dual"]
+
+ # See if there's more than one item in this order with that
+ # order_id & product_id
+
+ set n_items [db_string num_items_select "
+ select count(*)
+ from ec_items
+ where order_id = :order_id
+ and product_id = :product_id"]
+
+ if { $n_items > 1 } {
+
+ # Make sure they checked at least one checkbox
+
+ set item_id_list $item_id
+ if { [llength $item_id_list] == 1 && [lindex $item_id_list 0] == 0 } {
+ ad_return_complaint 1 "<li>You didn't check off any items.</li>"
+ return
+ }
+ db_dml item_state_update "
+ update ec_items
+ set item_state = 'void', voided_date = sysdate, voided_by = :customer_service_rep
+ where item_id in ([join $item_id_list ", "])"
+ } else {
+ db_dml item_state_update2 "
+ update ec_items
+ set item_state = 'void', voided_date = sysdate, voided_by = :customer_service_rep
+ where order_id = :order_id
+ and product_id = :product_id"
+ }
+
+ set amount_charged_minus_refunded_for_nonvoid_items [db_string amount_charged_minus_refunded_for_nonvoid_items_select "
+ select nvl(sum(nvl(price_charged,0)) + sum(nvl(shipping_charged,0)) + sum(nvl(price_tax_charged,0)) + sum(nvl(shipping_tax_charged,0)) - sum(nvl(price_refunded,0)) -
+ sum(nvl(shipping_refunded,0)) + sum(nvl(price_tax_refunded,0)) - sum(nvl(shipping_tax_refunded,0)),0)
+ from ec_items
+ where item_state <> 'void'
+ and order_id = :order_id"]
+
+ set certificate_amount_to_reinstate [expr $gift_certificate_amount - $amount_charged_minus_refunded_for_nonvoid_items]
+
+ if { $certificate_amount_to_reinstate > 0 } {
+ set certs_to_reinstate_list [list]
+ set certs_to_reinstate_list [db_list certs_to_reinstate_list_select "
+ select u.gift_certificate_id
+ from ec_gift_certificate_usage u, ec_gift_certificates c
+ where u.gift_certificate_id = c.gift_certificate_id
+ and u.order_id = :order_id
+ order by expires desc"]
+
+ # The amount used on that order
+
+ set certificate_amount_used [db_string certificate_amount_used_select "
+ select ec_order_gift_cert_amount(:order_id)
+ from dual"]
+
+ if { $certificate_amount_used < $certificate_amount_to_reinstate } {
+ db_dml problems_log_insert "
+ insert into ec_problems_log
+ (problem_id, problem_date, problem_details, order_id)
+ values
+ (ec_problem_id_sequence.nextval, sysdate, 'We were unable to reinstate the customer''s gift certificate balance because the amount to be reinstated was larger than the original amount used. This shouldn''t have happened unless there was a programming error or unless the database was incorrectly updated manually. The voiding of this order has been aborted.', :order_id)"
+ ad_return_error "Gift Certificate Error" "
+ <p>We were unable to reinstate the customer's gift certificate balance because the amount to be reinstated was larger than the original amount used.
+ This shouldn't have happened unless there was a programming error or unless the database was incorrectly updated manually.</p>
+ <p>The voiding of this order has been aborted. This has been logged in the problems log.</p>"
+ return
+ } else {
+
+ # Go through and reinstate certificates in order; it's not
+ # so bad to loop through all of them because I don't
+ # expect there to be many
+
+ set amount_to_reinstate $certificate_amount_to_reinstate
+ foreach cert $certs_to_reinstate_list {
+ if { $amount_to_reinstate > 0 } {
+
+ # Any amount up to the original amount used on
+ # this order can be reinstated
+
+ set reinstatable_amount [db_string reinstatable_amount_select "
+ select ec_one_gift_cert_on_one_order(:cert,:order_id)
+ from dual"]
+
+ if { $reinstatable_amount > 0 } {
+ set iteration_reinstate_amount [ec_min $reinstatable_amount $amount_to_reinstate]
+ db_dml reinstate_gift_certificate_insert "
+ insert into ec_gift_certificate_usage
+ (gift_certificate_id, order_id, amount_reinstated, reinstated_date)
+ values
+ (:cert, :order_id, :iteration_reinstate_amount, sysdate)"
+ set amount_to_reinstate [expr $amount_to_reinstate - $iteration_reinstate_amount]
+ }
+ }
+ }
+ }
+ }
+}
+
+# Check if a shipping gateway has been selected.
+
+set shipping_gateway [ad_parameter ShippingGateway]
+if {[acs_sc_binding_exists_p ShippingGateway $shipping_gateway]} {
+
+ # Replace the default ecommerce shipping calculations with the
+ # charges from the shipping gateway. Contact the shipping
+ # gateway to recalculate the total shipping charges.
+
+ db_1row select_shipping_address "
+ select country_code, zip_code
+ from ec_addresses a, ec_orders o
+ where address_id = o.shipping_address
+ and o.order_id = :order_id"
+
+ # Calculate the total value of the shipment.
+
+ set shipment_value [db_string select_shipment_value "
+ select sum(coalesce(i.price_charged, 0))
+ from ec_products p, ec_items i
+ where i.product_id = p.product_id
+ and p.no_shipping_avail_p = 'f'
+ and i.item_state not in ('void', 'received_back', 'expired')
+ and i.order_id = :order_id"]
+ set value_currency_code [ad_parameter Currency]
+ set weight_unit_of_measure [ad_parameter WeightUnits]
+
+ # Get the list of services and their charges.
+
+ set rates_and_services [acs_sc_call "ShippingGateway" "RatesAndServicesSelection" \
+ [list "" "" "$country_code" "$zip_code" "$shipment_value" "$value_currency_code" "" "$weight_unit_of_measure"] \
+ "$shipping_gateway"]
+
+ # Find the charges for the selected service for the order.
+
+ set shipping_method [db_string shipping_method_select "
+ select shipping_method
+ from ec_orders
+ where order_id = :order_id"]
+
+ foreach service $rates_and_services {
+ array set rate_and_service $service
+ set order_shipping_cost $rate_and_service(total_charges)
+ set service_code $rate_and_service(service_code)
+ set service_description [acs_sc_call "ShippingGateway" "ServiceDescription" \
+ "$service_code" \
+ "$shipping_gateway"]
+
+ # Unfortunately checking on the description of the
+ # shipping service is required as only the description is
+ # stored with the order as the shipping method.
+
+ if {[string equal $service_description $shipping_method]} {
+ break
+ }
+ }
+
+ # Calculate the tax on shipping and update the shipping cost
+ # of the order.
+
+ set tax_on_order_shipping_cost [db_string get_shipping_tax "
+ select ec_tax(0, :order_shipping_cost, :order_id)"]
+
+ db_dml set_shipping_charges "
+ update ec_orders
+ set shipping_charged = round(:order_shipping_cost, 2), shipping_tax_charged = round(:tax_on_order_shipping_cost, 2)
+ where order_id=:order_id"
+
+}
+
+ad_returnredirect "one?[export_url_vars order_id]"
Index: openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-void-2.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-void-2.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-void-2.xql 3 Aug 2005 22:45:46 -0000 1.1
@@ -0,0 +1,29 @@
+<?xml version="1.0"?>
+<queryset>
+
+<fullquery name="num_items_select">
+ <querytext>
+ select count(*) from ec_items where order_id=:order_id and product_id=:product_id
+ </querytext>
+</fullquery>
+
+
+<fullquery name="amount_charged_minus_refunded_for_nonvoid_items_select">
+ <querytext>
+ select coalesce(sum(coalesce(price_charged,0)) + sum(coalesce(shipping_charged,0)) + sum(coalesce(price_tax_charged,0)) + sum(coalesce(shipping_tax_charged,0)) - sum(coalesce(price_refunded,0)) - sum(coalesce(shipping_refunded,0)) + sum(coalesce(price_tax_refunded,0)) - sum(coalesce(shipping_tax_refunded,0)),0) from ec_items where item_state <> 'void' and order_id=:order_id
+ </querytext>
+</fullquery>
+
+
+<fullquery name="certs_to_reinstate_list_select">
+ <querytext>
+ select u.gift_certificate_id
+ from ec_gift_certificate_usage u, ec_gift_certificates c
+ where u.gift_certificate_id = c.gift_certificate_id
+ and u.order_id = :order_id
+ order by expires desc
+ </querytext>
+</fullquery>
+
+
+</queryset>
Index: openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-void.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-void.tcl,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-void.tcl 3 Aug 2005 22:45:46 -0000 1.1
@@ -0,0 +1,151 @@
+ad_page_contract {
+
+ @author Eve Andersson (eveander@arsdigita.com)
+ @creation-date Summer 1999
+ @author ported by Jerry Asher (jerry@theashergroup.com)
+ @author revised by Bart Teeuwisse (bart.teeuwisse@thecodemill.biz)
+ @revision-date April 2002
+
+} {
+ order_id:integer,notnull
+ product_id:integer,notnull
+}
+
+ad_require_permission [ad_conn package_id] admin
+
+# In case they reload this page after completing the void process:
+
+if { [db_string num_non_void_items_select "
+ select count(*)
+ from ec_items
+ where order_id = :order_id
+ and product_id = :product_id
+ and item_state <> 'void'"] == 0 } {
+ ad_return_complaint 1 "
+ <li>These items are already void; perhaps you are using an old form. <a href=\"one?[export_url_vars order_id]\">Return to the order.</a></li>"
+ return
+}
+
+set n_items [db_string num_items_select "
+ select count(*)
+ from ec_items
+ where order_id = :order_id
+ and product_id = :product_id"]
+
+if { $n_items > 1 } {
+ set item_or_items "Items"
+} else {
+ set item_or_items "Item"
+}
+
+doc_body_append "
+ [ad_admin_header "Void $item_or_items"]
+
+ <h2>Void $item_or_items</h2>
+
+ [ad_context_bar [list "../" "Ecommerce([ec_system_name])"] [list "index" "Orders"] [list "one?[export_url_vars order_id]" "One Order"] "$item_or_items"]
+
+ <hr>
+ <blockquote>
+ <form method=post action=items-void-2>
+ [export_form_vars order_id product_id]"
+
+# We have to take care of some cases (hopefully #1, the simplest, will
+# be most prevalent) different cases get different wording and cases
+# 1-2 are functionally different than cases 3-4
+# 1. There's only one item in this order with this product_id and it
+# hasn't shipped yet
+# 2. There's only one item in this order with this product_id and it's
+# already shipped
+# 3. More than one item in this order with this product_id and no
+# non-void items have already shipped
+# 4. More than one item in this order with this product_id and at
+# least one non-void item has already shipped
+
+if { $n_items == 1 } {
+
+ # Cases 1 & 2 (only differ by a warning message) we assume it's
+ # not void, otherwise they wouldn't have been given the link to
+ # this page
+
+ set item_state [db_string item_state_select "
+ select item_state
+ from ec_items
+ where order_id = :order_id
+ and product_id = :product_id"]
+
+ if { $item_state == "shipped" || $item_state == "arrived" || $item_state == "received_back" } {
+ doc_body_append "
+ <p><font color=red>Warning:</font> our records show that this item has already
+ shipped, which means that the customer has already been charged for this
+ item. Voiding an item will not cause the customer's credit card to be
+ refunded (you can only do that by marking it \"received back\").</p>"
+ }
+
+ db_foreach order_products_select "
+ select i.item_id, i.item_state, p.product_name, i.price_name, i.price_charged
+ from ec_items i, ec_products p
+ where i.product_id = p.product_id
+ and i.order_id = :order_id
+ and i.product_id = :product_id" {
+
+ doc_body_append "
+ <p>Please confirm that you want to void $product_name; $price_name: [ec_pretty_price $price_charged] $item_state</p>"
+ }
+} else {
+
+ # Cases 3 & 4 (only differ by a warning message)
+
+ set n_shipped_items [db_string num_shipped_items_select "
+ select count(*)
+ from ec_items
+ where order_id = :order_id
+ and product_id=:product_id
+ and item_state in ('shipped','arrived','received_back')"]
+
+ if { $n_shipped_items > 0 } {
+ doc_body_append "
+ <p><font>Warning:</font> our records show that at least one of these
+ items has already shipped, which means that the customer has already
+ been charged (for shipped items only). Voiding an item will not cause
+ the customer's credit card to be refunded
+ (you can only do that by marking it \"received back\").</p>"
+ }
+ doc_body_append "
+ <p>Please check off the item(s) you wish to void.</p>
+ <table>
+ <tr>
+ <th>Void Item</th>
+ <th>Product</th>
+ <th>Item State</th>"
+
+ db_foreach order_products_select "
+ select i.item_id, i.item_state, p.product_name, i.price_name, i.price_charged
+ from ec_items i, ec_products p
+ where i.product_id = p.product_id
+ and i.order_id = :order_id
+ and i.product_id = :product_id" {
+
+ doc_body_append "
+ <tr>
+ <td align=center>"
+ if { $item_state == "void" } {
+ doc_body_append " (already void) "
+ } else {
+ doc_body_append "<input type=\"checkbox\" name=\"item_id\" value=\"$item_id\">"
+ }
+ doc_body_append "
+ </td>
+ <td>$product_name; $price_name: [ec_pretty_price $price_charged]</td><td>$item_state</td>
+ </tr>"
+ }
+ doc_body_append "</table>"
+}
+
+doc_body_append "
+ </blockquote>
+ <center>
+ <input type=submit value=\"Confirm\">
+ </center>
+ </form>
+ [ad_admin_footer]"
Index: openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-void.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-void.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/items-void.xql 3 Aug 2005 22:45:46 -0000 1.1
@@ -0,0 +1,43 @@
+<?xml version="1.0"?>
+<queryset>
+
+<fullquery name="num_non_void_items_select">
+ <querytext>
+ select count(*) from ec_items where order_id=:order_id and product_id=:product_id and item_state <> 'void'
+ </querytext>
+</fullquery>
+
+
+<fullquery name="num_items_select">
+ <querytext>
+ select count(*) from ec_items where order_id=:order_id and product_id=:product_id
+ </querytext>
+</fullquery>
+
+
+<fullquery name="item_state_select">
+ <querytext>
+ select item_state from ec_items where order_id=:order_id and product_id=:product_id
+ </querytext>
+</fullquery>
+
+
+<fullquery name="num_shipped_items_select">
+ <querytext>
+ select count(*) from ec_items where order_id=:order_id and product_id=:product_id and item_state in ('shipped','arrived','received_back')
+ </querytext>
+</fullquery>
+
+
+<fullquery name="order_products_select">
+ <querytext>
+ select i.item_id, i.item_state, p.product_name, i.price_name, i.price_charged
+ from ec_items i, ec_products p
+ where i.product_id=p.product_id
+ and i.order_id=:order_id
+ and i.product_id=:product_id
+ </querytext>
+</fullquery>
+
+
+</queryset>
Index: openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/one-oracle.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/one-oracle.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/one-oracle.xql 3 Aug 2005 22:45:46 -0000 1.1
@@ -0,0 +1,22 @@
+<?xml version="1.0"?>
+
+<queryset>
+ <rdbms>
+ <type>oracle</type>
+ <version>8.1.6</version>
+ </rdbms>
+
+ <fullquery name="order_select">
+ <querytext>
+ select o.order_state, o.creditcard_id, o.confirmed_date, o.cs_comments,
+ o.shipping_method, o.shipping_address, o.in_basket_date,
+ o.authorized_date, o.shipping_charged, o.voided_by, o.voided_date,
+ o.reason_for_void, u.user_id, u.first_names, u.last_name, c.billing_address
+ from ec_orders o, cc_users u, ec_creditcards c
+ where order_id=:order_id
+ and o.user_id = u.user_id(+)
+ and o.creditcard_id = c.creditcard_id(+)
+ </querytext>
+ </fullquery>
+
+</queryset>
Index: openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/one-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/one-postgresql.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/one-postgresql.xql 3 Aug 2005 22:45:46 -0000 1.1
@@ -0,0 +1,17 @@
+<?xml version="1.0"?>
+<queryset>
+
+ <fullquery name="order_select">
+ <querytext>
+ select o.order_state, o.creditcard_id, o.confirmed_date, o.cs_comments,
+ o.shipping_method, o.shipping_address, o.in_basket_date,
+ o.authorized_date, o.shipping_charged, o.voided_by, o.voided_date,
+ o.reason_for_void, u.user_id, u.first_names, u.last_name, c.billing_address
+ from ec_orders o
+ left join cc_users u using (user_id)
+ left join ec_creditcards c using (creditcard_id)
+ where order_id=:order_id
+ </querytext>
+ </fullquery>
+
+</queryset>
Index: openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/one.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/one.tcl,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/one.tcl 3 Aug 2005 22:45:46 -0000 1.1
@@ -0,0 +1,476 @@
+ad_page_contract {
+
+ Display one order.
+
+ @author Eve Andersson (eveander@arsdigita.com)
+ @creation-date Summer 1999
+ @author ported by Jerry Asher (jerry@theashergroup.com)
+ @author revised by Bart Teeuwisse (bart.teeuwisse@thecodemill.biz)
+ @revision-date April 2002
+
+} {
+ order_id:integer,notnull
+}
+
+ad_require_permission [ad_conn package_id] admin
+
+db_1row order_select "
+ select o.order_state, o.creditcard_id, o.confirmed_date, o.cs_comments,
+ o.shipping_method, o.shipping_address, o.in_basket_date,
+ o.authorized_date, o.shipping_charged, o.voided_by, o.voided_date,
+ o.reason_for_void, u.user_id, u.first_names, u.last_name, c.billing_address
+ from ec_orders o, cc_users u, ec_creditcards
+ where order_id=:order_id
+ and o.user_id = u.user_id(+)
+ and o.creditcard_id = c.creditcard_id(+)"
+
+doc_body_append "
+ [ad_admin_header "One Order"]
+
+ <h2>One Order</h2>
+
+ [ad_context_bar [list "../" "Ecommerce([ec_system_name])"] [list "index" "Orders"] "One Order"]
+
+ <hr>
+
+ <h3>Overview</h3>
+
+ [ec_decode $order_state "void" "<table>" "<table width=90%>"]
+ <tr>
+ <td align=right><b>Order ID</td>
+ <td>$order_id</td>
+ <td rowspan=4 align=right valign=top>[ec_decode $order_state "void" "" "<pre>[ec_formatted_price_shipping_gift_certificate_and_tax_in_an_order $order_id]</pre>"]</td>
+ </tr>
+ <tr>
+ <td align=right><b>Ordered by</td>
+ <td><a href=\"[ec_acs_admin_url]users/one?user_id=$user_id\">$first_names $last_name</a></td>
+ </tr>
+ <tr>
+ <td align=right><b>Confirmed date</td>
+ <td>[ec_formatted_full_date $confirmed_date]</td>
+ </tr>
+ <tr>
+ <td align=right><b>Order state</td>
+ <td>[ec_decode $order_state "void" "<font color=red>void</font>" $order_state]</td>
+ </tr>
+ </table>"
+
+if { $order_state == "void" } {
+ doc_body_append "
+ <h3>Details of Void</h3>
+
+ <blockquote>
+ Voided by: <a href=\"[ec_acs_admin_url]users/one?user_id=$voided_by\">[db_string voided_by_name_select "
+ select first_names || ' ' || last_name from cc_users where user_id = :voided_by" -default ""]</a><br>
+ Date: [ec_formatted_full_date $voided_date]<br>
+ [ec_decode $reason_for_void "" "" "Reason: [ec_display_as_html $reason_for_void]"]
+ </blockquote>"
+}
+
+doc_body_append "
+ [ec_decode $cs_comments "" "" "<h3>Comments</h3>\n<blockquote>[ec_display_as_html $cs_comments]</blockquote>"]
+
+ <ul>
+ <li><a href=\"comments?[export_url_vars order_id]\">Add/Edit Comments</a></li>
+ </ul>
+
+ <h3>Items</h3>
+ <ul>"
+
+set items_ul ""
+
+# We want to display these by item (with all order states in parentheses), like:
+# Quantity 3: 2 Standard Pencils; Our Price: $0.99 (2 shipped, 1 to_be_shipped).
+# This UI will break if the customer has more than one of the same product with
+# different prices in the same order (the shipment summary is by product_id).
+
+set old_product_color_size_style_price_price_name [list]
+set item_quantity 0
+set state_list [list]
+
+db_foreach products_select "
+ select p.product_name, p.product_id, i.price_name, i.price_charged, count(*) as quantity, i.item_state, i.color_choice, i.size_choice, i.style_choice
+ from ec_items i, ec_products p
+ where i.product_id=p.product_id
+ and i.order_id=:order_id
+ group by p.product_name, p.product_id, i.price_name, i.price_charged, i.item_state, i.color_choice, i.size_choice, i.style_choice" {
+
+ set product_color_size_style_price_price_name [list $product_id $color_choice $size_choice $style_choice $price_charged $price_name]
+
+ set option_list [list]
+ if { ![empty_string_p $color_choice] } {
+ lappend option_list "Color: $color_choice"
+ }
+ if { ![empty_string_p $size_choice] } {
+ lappend option_list "Size: $size_choice"
+ }
+ if { ![empty_string_p $style_choice] } {
+ lappend option_list "Style: $style_choice"
+ }
+ set options [join $option_list ", "]
+
+ # It's OK to compare tcl lists with != because lists are really
+ # strings in tcl
+
+ if { $product_color_size_style_price_price_name != $old_product_color_size_style_price_price_name && [llength $old_product_color_size_style_price_price_name] != 0 } {
+ append items_ul "
+ <li>
+ Quantity $item_quantity: $item_description ([join $item_state_list ", "])"
+ if { [llength $item_state_list] != 1 || [lindex [split [lindex $item_state_list 0] " "] 1] != "void" } {
+
+ # i.e., if the items of this product_id are not all void
+ # (I know that "if" statement could be written more compactly,
+ # but I didn't want to offend Philip by relying on Tcl's internal
+ # representation of a list)
+
+ # EVE: have to make items-void.tcl take more than just product_id
+
+ append items_ul "
+ <font size=-1>
+ (<a href=\"items-void?[export_url_vars order_id]&product_id=[lindex $old_product_color_size_style_price_price_name 0]\">remove</a>)
+ </font>"
+ }
+ append items_ul "
+ <br>
+ [ec_shipment_summary_sub [lindex $old_product_color_size_style_price_price_name 0] [lindex $old_product_color_size_style_price_price_name 1] [lindex $old_product_color_size_style_price_price_name 2] [lindex $old_product_color_size_style_price_price_name 3] [lindex $old_product_color_size_style_price_price_name 4] [lindex $old_product_color_size_style_price_price_name 5] $order_id]
+ </li>"
+ set item_state_list [list]
+ set item_quantity 0
+ }
+
+ lappend item_state_list "$quantity $item_state"
+ set item_quantity [expr $item_quantity + $quantity]
+ set item_description "
+ <a href=\"[ec_url_concat [ec_url] /admin]/products/one?product_id=$product_id\">$product_name</a>;
+ [ec_decode $options "" "" "$options; "]$price_name: [ec_pretty_price $price_charged]"
+ set old_product_color_size_style_price_price_name [list $product_id $color_choice $size_choice $style_choice $price_charged $price_name]
+}
+
+if { [llength $old_product_color_size_style_price_price_name] != 0 } {
+
+ # append the last line
+
+ append items_ul "
+ <li>
+ Quantity $item_quantity: $item_description ([join $item_state_list ", "])"
+ if { [llength $item_state_list] != 1 || [lindex [split [lindex $item_state_list 0] " "] 1] != "void" } {
+
+ # I.e., if the items of this product_id are not all void
+
+ append items_ul "
+ <font size=-1>
+ (<a href=\"items-void?[export_url_vars order_id]&product_id=[lindex $old_product_color_size_style_price_price_name 0]\">remove</a>)
+ </font>"
+ }
+ append items_ul "
+ <br>
+ [ec_shipment_summary_sub [lindex $old_product_color_size_style_price_price_name 0] [lindex $old_product_color_size_style_price_price_name 1] [lindex $old_product_color_size_style_price_price_name 2] [lindex $old_product_color_size_style_price_price_name 3] [lindex $old_product_color_size_style_price_price_name 4] [lindex $old_product_color_size_style_price_price_name 5] $order_id]
+ </li>"
+}
+
+doc_body_append "$items_ul"
+
+if { $order_state == "authorized" || $order_state == "partially_fulfilled" } {
+ doc_body_append "
+ <li><a href=\"fulfill?[export_url_vars order_id]\">Record a Shipment</a></li>
+ <li><a href=\"items-add?[export_url_vars order_id]\">Add Items</a></li>"
+}
+if { $order_state == "fulfilled" || $order_state == "partially_fulfilled" } {
+ doc_body_append "
+ <li><a href=\"items-return?[export_url_vars order_id]\">Mark Items Returned</a></li>"
+}
+
+doc_body_append "
+ </ul>
+
+ <h3>Details</h3>
+
+ <table>
+ <tr>
+ <td align=right valign=top><b>[ec_decode $shipping_method "pickup" "Address" "no shipping" "Address" "Ship to"]</b></td>
+ <td>[ec_display_as_html [ec_pretty_mailing_address_from_ec_addresses $shipping_address]]<br>"
+
+if { $order_state == "confirmed" || $order_state == "authorized" || $order_state == "partially_fulfilled" } {
+ doc_body_append "
+ (<a href=\"address-add?[export_url_vars order_id]\">modify</a>)"
+}
+
+doc_body_append "
+ </td>
+ </tr>"
+
+if { ![empty_string_p $creditcard_id] } {
+ doc_body_append "
+ <tr>
+ <td align=right valign=top><b>Bill to</b></td>
+ <td>[ec_display_as_html [ec_pretty_mailing_address_from_ec_addresses $billing_address]]<br>
+ (<a href=\"address-add?[export_url_vars order_id creditcard_id]\">modify</a>)</td>
+ <td align=right valign=top><b>Credit card</b></td>
+ <td valign=top>[ec_display_as_html [ec_creditcard_summary $creditcard_id] ]<br>
+ (<a href=\"creditcard-add?[export_url_vars order_id]\">modify</a>)</td>
+ </tr>"
+}
+
+doc_body_append "
+ <tr>
+ <td align=right><b>In basket date</b></td>
+ <td>[ec_formatted_full_date $in_basket_date]</td>
+ </tr>
+ <tr>
+ <td align=right><b>Confirmed date</b></td>
+ <td>[ec_formatted_full_date $confirmed_date]</td>
+ </tr>
+ <tr>
+ <td align=right><b>Authorized date</b></td>
+ <td>[ec_formatted_full_date $authorized_date]</td>
+ </tr>
+ <tr>
+ <td align=right><b>Base shipping charged</b></td>
+ <td>[ec_pretty_price $shipping_charged] [ec_decode $shipping_method "pickup" "(Pickup)" "no shipping" "(No Shipping)" ""]</td>
+ </tr>
+ </table>
+
+ <h3>Financial Transactions</h3>"
+
+set table_header "
+ <table border>
+ <tr>
+ <th>ID</th>
+ <th>Date</th>
+ <th>Creditcard Last 4</th>
+ <th>Amount</th>
+ <th>Type</th>
+ <th>To Be Captured</th>
+ <th>Auth Date</th>
+ <th>Mark Date</th>
+ <th>Refund Date</th>
+ <th>Failed</th>
+ </tr>"
+
+set transaction_counter 0
+
+# Check for payment methods
+set method [db_string method {
+ select method, internal_account
+ from dotlrn_ecommerce_transactions
+ where order_id = :order_id
+} -default cc]
+
+if { [db_0or1row scholarship {
+ select 1
+ where exists (select *
+ from ec_gift_certificate_usage
+ where order_id = :order_id
+ and exists (select *
+ from scholarship_fund_grants
+ where ec_gift_certificate_usage.gift_certificate_id = gift_certificate_id))
+}] } {
+ set method scholarship
+}
+
+set total_price [db_string total_price {select ec_total_price(:order_id)} -default 0]
+
+switch $method {
+
+ "invoice" {
+ # List invoice payments
+ doc_body_append "<blockquote>This order was paid by <b>invoice</b>."
+
+ set invoice_payment_sum 0
+ doc_body_append "<ul>"
+ db_foreach invoice_payments {
+ select amount, to_char(payment_date, 'Month dd, yyyy hh:miam') as pretty_payment_date, method as invoice_method
+ from dotlrn_ecommerce_transaction_invoice_payments
+ where order_id = :order_id
+ order by payment_date
+ } {
+ doc_body_append "<li>Date: $pretty_payment_date, Amount: $amount, Via: [ad_decode $invoice_method cc "Credit Card" internal_account "Internal Account" check "Check" cash "Cash" "Credit Card"]</li>"
+ set invoice_payment_sum [expr $invoice_payment_sum + $amount]
+ }
+
+ if { $invoice_payment_sum == 0 } {
+ doc_body_append "<li>No payments have been made</li>"
+ }
+
+ if { $invoice_payment_sum < $total_price } {
+ doc_body_append "<li><a href=\"invoice-payment?order_id=$order_id\">Add Payment</a></li>"
+ }
+
+ doc_body_append [subst {</ul>
+
+ TOTAL: [ec_pretty_price $total_price]
+ <br />
+ Balance: [ec_pretty_price [expr $total_price - $invoice_payment_sum]]
+ </blockquote>}]
+ }
+ "scholarship" {
+ set gc_amount [db_string gc_amount {select ec_order_gift_cert_amount(:order_id)} -default 0]
+
+ if { $gc_amount == $total_price } {
+ doc_body_append "<blockquote>This order was <b>fully</b> paid by <b>scholarship</b>."
+ } else {
+ doc_body_append "<blockquote>This order was <b>partially</b> paid by <b>scholarship</b>."
+ }
+
+ doc_body_append "<ul>"
+ db_foreach funds {
+ select f.title, u.amount_used, g.grant_amount, to_char(g.grant_date, 'Month dd, yyyy hh:miam') as grant_date
+ from ec_gift_certificate_usage u, scholarship_fund_grants g, scholarship_fundi f
+ where u.gift_certificate_id = g.gift_certificate_id
+ and g.fund_id = f.fund_id
+ and u.order_id = :order_id
+
+ order by g.grant_date
+ } {
+ doc_body_append "<li>Date: $grant_date, Fund: $title, Amount Granted: [ec_pretty_price $grant_amount], Amount Used: [ec_pretty_price $amount_used]</li>"
+ }
+ doc_body_append "</ul>"
+
+ doc_body_append "</blockquote>"
+ }
+}
+
+db_foreach financial_transactions_select "
+ select t.transaction_id, t.inserted_date, t.transaction_amount, t.transaction_type, t.to_be_captured_p, t.authorized_date,
+ t.marked_date, t.refunded_date, t.failed_p, c.creditcard_last_four
+ from ec_financial_transactions t, ec_creditcards c
+ where t.creditcard_id=c.creditcard_id
+ and t.order_id=:order_id
+ order by transaction_id" {
+
+ if { $transaction_counter == 0 } {
+ doc_body_append $table_header
+ }
+ doc_body_append "
+ <tr>
+ <td>$transaction_id</td>
+ <td>[ec_nbsp_if_null [ec_formatted_full_date $inserted_date]]</td>
+ <td>$creditcard_last_four</td>
+ <td>[ec_pretty_price $transaction_amount]</td>
+ <td>[ec_decode $transaction_type "charge" "authorization to charge" "intent to refund"]</td>
+ <td>[ec_nbsp_if_null [ec_decode $transaction_type "refund" "Yes" [ec_decode $to_be_captured_p "t" "Yes" "f" "No" ""]]]</td>
+ <td>[ec_nbsp_if_null [ec_formatted_full_date $authorized_date]]</td>
+ <td>[ec_nbsp_if_null [ec_formatted_full_date $marked_date]]</td>
+ <td>[ec_nbsp_if_null [ec_formatted_full_date $refunded_date]]</td>
+ <td>[ec_nbsp_if_null [ec_decode $failed_p "t" "Yes" "f" "No" ""]]</td>
+ </tr>"
+ incr transaction_counter
+}
+
+if { $transaction_counter != 0 } {
+ doc_body_append "</table>"
+} else {
+
+ # Check if this was payed via another method
+ switch $method {
+ cash -
+ check {
+ doc_body_append "<blockquote>This order was <b>fully</b> paid by <b>${method}</b>.</blockquote>"
+ }
+ cc {
+ doc_body_append "<blockquote>No credit card transactions</blockquote>"
+ }
+ invoice -
+ scholarship {}
+ }
+}
+
+doc_body_append "
+ <h3>Shipments</h3>
+ <blockquote>"
+
+set old_shipment_id 0
+
+db_foreach shipments_items_products_select "
+ select s.shipment_id, s.address_id, s.shipment_date, s.expected_arrival_date, s.carrier, s.tracking_number, s.actual_arrival_date, s.actual_arrival_detail,
+ p.product_name, p.product_id, i.price_name, i.price_charged, count(*) as quantity
+ from ec_shipments s, ec_items i, ec_products p
+ where i.shipment_id=s.shipment_id
+ and i.product_id=p.product_id
+ and s.order_id=:order_id
+ group by s.shipment_id, s.address_id, s.shipment_date, s.expected_arrival_date, s.carrier, s.tracking_number, s.actual_arrival_date, s.actual_arrival_detail,
+ p.product_name, p.product_id, i.price_name, i.price_charged
+ order by s.shipment_id" {
+ if { $shipment_id != $old_shipment_id } {
+ if { $old_shipment_id != 0 } {
+ doc_body_append "</ul>"
+ }
+ doc_body_append "
+ <table width=90%>
+ <tr>
+ <td width=50% valign=top>
+ Shipment ID: $shipment_id<br>
+ Date: [util_AnsiDatetoPrettyDate $shipment_date]<br>
+ [ec_decode $expected_arrival_date "" "" "Expected Arrival: [util_AnsiDatetoPrettyDate $expected_arrival_date]<br>"]
+ [ec_decode $carrier "" "" "Carrier: $carrier<br>"]
+ [ec_decode $tracking_number "" "" "Tracking #: $tracking_number<br>"]
+ [ec_decode $actual_arrival_date "" "" "Actual Arrival Date: [util_AnsiDatetoPrettyDate $actual_arrival_date]<br>"]
+ [ec_decode $actual_arrival_detail "" "" "Actual Arrival Detail: $actual_arrival_detail<br>"]
+ (<a href=\"track?shipment_id=$shipment_id\">track</a>)
+ </td>
+ <td valign=top width=50%>
+ [ec_display_as_html [ec_pretty_mailing_address_from_ec_addresses $address_id]]
+ </td>
+ </tr>
+ </table>
+ <ul>"
+ }
+ doc_body_append "<li>Quantity $quantity: $product_name</li>"
+ set old_shipment_id $shipment_id
+}
+
+if { $old_shipment_id == 0 } {
+ doc_body_append "No Shipments Have Been Made"
+} else {
+ doc_body_append "</ul>"
+}
+
+doc_body_append "
+ </blockquote>
+
+ <h3>Returns</h3>
+
+ <blockquote>"
+
+set old_refund_id 0
+
+db_foreach refunds_select "
+ select r.refund_id, r.refund_date, r.refunded_by, r.refund_reasons, r.refund_amount, u.first_names, u.last_name, p.product_name, p.product_id, i.price_name, i.price_charged, count(*) as quantity
+ from ec_refunds r, cc_users u, ec_items i, ec_products p
+ where r.order_id=:order_id
+ and r.refunded_by=u.user_id
+ and i.refund_id=r.refund_id
+ and p.product_id=i.product_id
+ group by r.refund_id, r.refund_date, r.refunded_by, r.refund_reasons, r.refund_amount, u.first_names, u.last_name, p.product_name, p.product_id, i.price_name, i.price_charged" {
+ if { $refund_id != $old_refund_id } {
+ if { $old_refund_id != 0 } {
+ doc_body_append "</ul>"
+ }
+ doc_body_append "
+ Refund ID: $refund_id<br>
+ Date: [ec_formatted_full_date $refund_date]<br>
+ Amount: [ec_pretty_price $refund_amount]<br>
+ Refunded by: <a href=\"[ec_acs_admin_url]users/one?user_id=$refunded_by\">$first_names $last_name</a><br>
+ Reason: $refund_reasons
+ <ul>"
+ }
+ doc_body_append "<li>Quantity $quantity: $product_name</li>"
+ set old_refund_id $refund_id
+}
+
+if { $old_refund_id == 0 } {
+ doc_body_append "No Returns Have Been Made"
+} else {
+ doc_body_append "</ul>"
+}
+
+doc_body_append "</blockquote>"
+
+if { $order_state != "void" } {
+ doc_body_append "
+ <h3>Actions</h3>
+ <ul>
+ <li><a href=\"void?[export_url_vars order_id]\">Void Order</a></li>"
+}
+doc_body_append "</ul>
+[ad_admin_footer]"
Index: openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/one.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/one.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/one.xql 3 Aug 2005 22:45:46 -0000 1.1
@@ -0,0 +1,61 @@
+<?xml version="1.0"?>
+<queryset>
+
+ <fullquery name="voided_by_name_select">
+ <querytext>
+ select first_names || ' ' || last_name
+ from cc_users
+ where user_id=:voided_by
+ </querytext>
+ </fullquery>
+
+ <fullquery name="products_select">
+ <querytext>
+ select p.product_name, p.product_id, i.price_name, i.price_charged, count(*) as quantity, i.item_state, i.color_choice, i.size_choice, i.style_choice
+ from ec_items i, ec_products p
+ where i.product_id=p.product_id
+ and i.order_id=:order_id
+ group by p.product_name, p.product_id, i.price_name, i.price_charged, i.item_state, i.color_choice, i.size_choice, i.style_choice
+ </querytext>
+ </fullquery>
+
+ <fullquery name="financial_transactions_select">
+ <querytext>
+ select t.transaction_id, t.inserted_date, t.transaction_amount, t.transaction_type, t.to_be_captured_p,
+ t.authorized_date, t.marked_date, t.refunded_date, t.failed_p, c.creditcard_last_four
+ from ec_financial_transactions t, ec_creditcards c
+ where t.creditcard_id=c.creditcard_id
+ and t.order_id=:order_id
+ order by transaction_id
+ </querytext>
+ </fullquery>
+
+ <fullquery name="shipments_items_products_select">
+ <querytext>
+ select s.shipment_id, s.address_id, s.shipment_date, s.expected_arrival_date, s.carrier, s.tracking_number,
+ s.actual_arrival_date, s.actual_arrival_detail, p.product_name, p.product_id, i.price_name, i.price_charged, count(*) as quantity
+ from ec_shipments s, ec_items i, ec_products p
+ where i.shipment_id=s.shipment_id
+ and i.product_id=p.product_id
+ and s.order_id=:order_id
+ group by s.shipment_id, s.address_id, s.shipment_date, s.expected_arrival_date, s.carrier, s.tracking_number,
+ s.actual_arrival_date, s.actual_arrival_detail, p.product_name, p.product_id, i.price_name, i.price_charged
+ order by s.shipment_id
+ </querytext>
+ </fullquery>
+
+ <fullquery name="refunds_select">
+ <querytext>
+ select r.refund_id, r.refund_date, r.refunded_by, r.refund_reasons, r.refund_amount, u.first_names, u.last_name,
+ p.product_name, p.product_id, i.price_name, i.price_charged, count(*) as quantity
+ from ec_refunds r, cc_users u, ec_items i, ec_products p
+ where r.order_id=:order_id
+ and r.refunded_by=u.user_id
+ and i.refund_id=r.refund_id
+ and p.product_id=i.product_id
+ group by r.refund_id, r.refund_date, r.refunded_by, r.refund_reasons, r.refund_amount, u.first_names, u.last_name,
+ p.product_name, p.product_id, i.price_name, i.price_charged
+ </querytext>
+ </fullquery>
+
+</queryset>
Index: openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/search-oracle.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/search-oracle.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/search-oracle.xql 3 Aug 2005 22:45:46 -0000 1.1
@@ -0,0 +1,98 @@
+<?xml version="1.0"?>
+
+<queryset>
+ <rdbms><type>oracle</type><version>8.1.6</version></rdbms>
+
+<fullquery name="order_id_query_string_sql">
+ <querytext>
+
+ select o.order_id, o.confirmed_date, o.order_state,
+ ec_total_price(o.order_id) as price_to_display,
+ o.user_id,
+ u.first_names, u.last_name,
+ count(*) as n_items
+ from ec_orders o, cc_users u, ec_items i
+ where o.order_id like :order_id_query_string
+ and o.user_id=u.user_id(+)
+ and o.order_id=i.order_id
+ group by o.order_id, o.confirmed_date, o.order_state,
+ ec_total_price(o.order_id), o.user_id,
+ u.first_names, u.last_name
+ order by order_id
+
+ </querytext>
+</fullquery>
+
+
+<fullquery name="product_sku_query_string_sql">
+ <querytext>
+
+ select o.order_id, o.confirmed_date, o.order_state,
+ ec_total_price(o.order_id) as price_to_display,
+ o.user_id,
+ u.first_names, u.last_name,
+ p.product_name,
+ count(*) as n_items
+ from ec_orders o, cc_users u, ec_items i, ec_products p
+ where upper(p.sku) like upper(:product_sku_query_string)
+ and i.product_id=p.product_id
+ and o.user_id=u.user_id(+)
+ and o.order_id=i.order_id
+ group by o.order_id, o.confirmed_date, o.order_state,
+ ec_total_price(o.order_id),
+ o.user_id,
+ u.first_names, u.last_name, p.product_name
+ order by order_id
+
+ </querytext>
+</fullquery>
+
+
+<fullquery name="product_name_query_string_sql">
+ <querytext>
+
+ select o.order_id, o.confirmed_date, o.order_state,
+ ec_total_price(o.order_id) as price_to_display,
+ o.user_id,
+ u.first_names, u.last_name,
+ p.product_name,
+ count(*) as n_items
+ from ec_orders o, cc_users u, ec_items i, ec_products p
+ where upper(p.product_name) like upper(:product_name_query_string)
+ and i.product_id=p.product_id
+ and o.user_id=u.user_id(+)
+ and o.order_id=i.order_id
+ group by o.order_id, o.confirmed_date, o.order_state,
+ ec_total_price(o.order_id),
+ o.user_id,
+ u.first_names, u.last_name, p.product_name
+ order by order_id
+
+ </querytext>
+</fullquery>
+
+
+<fullquery name="default_sql">
+ <querytext>
+
+ select o.order_id, o.confirmed_date, o.order_state,
+ ec_total_price(o.order_id) as price_to_display,
+ o.user_id,
+ u.first_names, u.last_name,
+ count(*) as n_items
+ from ec_orders o, cc_users u, ec_items i
+ where upper(u.last_name) like upper(:cust_last_name_query_string)
+ and o.user_id=u.user_id(+)
+ and o.order_id=i.order_id
+ group by o.order_id, o.confirmed_date, o.order_state,
+ ec_total_price(o.order_id),
+ o.user_id,
+ u.first_names, u.last_name
+ order by order_id
+
+ </querytext>
+</fullquery>
+
+
+
+</queryset>
Index: openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/search-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/search-postgresql.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/search-postgresql.xql 3 Aug 2005 22:45:46 -0000 1.1
@@ -0,0 +1,98 @@
+<?xml version="1.0"?>
+
+<queryset>
+ <rdbms><type>postgresql</type><version>7.1</version></rdbms>
+
+<fullquery name="order_id_query_string_sql">
+ <querytext>
+
+ select o.order_id, o.confirmed_date, o.order_state,
+ ec_total_price(o.order_id) as price_to_display,
+ o.user_id,
+ u.first_names, u.last_name,
+ count(*) as n_items
+ from ec_orders o
+ LEFT JOIN cc_users u on (o.user_id=u.user_id)
+ JOIN ec_items i on (o.order_id=i.order_id)
+ where o.order_id like :order_id_query_string
+ group by o.order_id, o.confirmed_date, o.order_state,
+ ec_total_price(o.order_id), o.user_id,
+ u.first_names, u.last_name
+ order by order_id
+
+ </querytext>
+</fullquery>
+
+
+<fullquery name="product_sku_query_string_sql">
+ <querytext>
+
+ select o.order_id, o.confirmed_date, o.order_state,
+ ec_total_price(o.order_id) as price_to_display,
+ o.user_id,
+ u.first_names, u.last_name,
+ p.product_name,
+ count(*) as n_items
+ from ec_orders o
+ JOIN ec_items i on (o.order_id=i.order_id)
+ JOIN ec_products p on (i.product_id=p.product_id)
+ LEFT JOIN cc_users u on (o.user_id=u.user_id)
+ where upper(p.sku) like upper(:product_sku_query_string)
+ group by o.order_id, o.confirmed_date, o.order_state,
+ ec_total_price(o.order_id),
+ o.user_id,
+ u.first_names, u.last_name, p.product_name
+ order by order_id
+
+ </querytext>
+</fullquery>
+
+
+<fullquery name="product_name_query_string_sql">
+ <querytext>
+
+ select o.order_id, o.confirmed_date, o.order_state,
+ ec_total_price(o.order_id) as price_to_display,
+ o.user_id,
+ u.first_names, u.last_name,
+ p.product_name,
+ count(*) as n_items
+ from ec_orders o
+ JOIN ec_items i on (o.order_id=i.order_id)
+ JOIN ec_products p on (i.product_id=p.product_id)
+ LEFT JOIN cc_users u on (o.user_id=u.user_id)
+ where upper(p.product_name) like upper(:product_name_query_string)
+ group by o.order_id, o.confirmed_date, o.order_state,
+ ec_total_price(o.order_id),
+ o.user_id,
+ u.first_names, u.last_name, p.product_name
+ order by order_id
+
+ </querytext>
+</fullquery>
+
+
+<fullquery name="default_sql">
+ <querytext>
+
+ select o.order_id, o.confirmed_date, o.order_state,
+ ec_total_price(o.order_id) as price_to_display,
+ o.user_id,
+ u.first_names, u.last_name,
+ count(*) as n_items
+ from ec_orders o
+ LEFT JOIN cc_users u on (o.user_id=u.user_id)
+ JOIN ec_items i on (o.order_id=i.order_id)
+ where upper(u.last_name) like upper(:cust_last_name_query_string)
+ group by o.order_id, o.confirmed_date, o.order_state,
+ ec_total_price(o.order_id),
+ o.user_id,
+ u.first_names, u.last_name
+ order by order_id
+
+ </querytext>
+</fullquery>
+
+
+
+</queryset>
Index: openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/track-oracle.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/track-oracle.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/track-oracle.xql 3 Aug 2005 22:45:46 -0000 1.1
@@ -0,0 +1,17 @@
+<?xml version="1.0"?>
+
+<queryset>
+ <rdbms><type>oracle</type><version>8.1.6</version></rdbms>
+
+<fullquery name="shipment_select">
+ <querytext>
+
+select to_char(shipment_date, 'MMDDYY') as ship_date_for_fedex, to_char(shipment_date, 'MM/DD/YYYY') as pretty_ship_date, carrier, tracking_number
+from ec_shipments
+where shipment_id = :shipment_id
+
+ </querytext>
+</fullquery>
+
+
+</queryset>
Index: openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/track-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/track-postgresql.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/track-postgresql.xql 3 Aug 2005 22:45:46 -0000 1.1
@@ -0,0 +1,17 @@
+<?xml version="1.0"?>
+
+<queryset>
+ <rdbms><type>postgresql</type><version>7.1</version></rdbms>
+
+<fullquery name="shipment_select">
+ <querytext>
+
+select to_char(shipment_date, 'MMDDYY') as ship_date_for_fedex, to_char(shipment_date, 'MM/DD/YYYY') as pretty_ship_date, carrier, tracking_number
+from ec_shipments
+where shipment_id = :shipment_id
+
+ </querytext>
+</fullquery>
+
+
+</queryset>
Index: openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/track.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/track.tcl,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/track.tcl 3 Aug 2005 22:45:46 -0000 1.1
@@ -0,0 +1,74 @@
+# /www/[ec_url_concat [ec_url] /admin]/orders/track.tcl
+ad_page_contract {
+ Track a shipment.
+
+ @author Eve Andersson (eveander@arsdigita.com)
+ @creation-date Summer 1999
+ @cvs-id $Id: track.tcl,v 1.1 2005/08/03 22:45:46 roelc Exp $
+ @author ported by Jerry Asher (jerry@theashergroup.com)
+} {
+ shipment_id:integer,notnull
+}
+
+ad_require_permission [ad_conn package_id] admin
+
+doc_body_append "[ad_admin_header "Track Shipment"]
+
+<h2>Track Shipment</h2>
+
+[ad_context_bar [list "../" "Ecommerce([ec_system_name])"] [list "index" "Orders"] [list "one?[export_url_vars order_id]" "One Order"] "Track Shipment"]
+
+<hr>
+"
+
+
+db_1row shipment_select "
+select to_char(shipment_date, 'MMDDYY') as ship_date_for_fedex, to_char(shipment_date, 'MM/DD/YYYY') as pretty_ship_date, carrier, tracking_number
+from ec_shipments
+where shipment_id = :shipment_id
+"
+
+set carrier_info ""
+
+if { $carrier == "FedEx" } {
+ set fedex_url "http://www.fedex.com/cgi-bin/tracking?tracknumbers=$tracking_number&action=track&language=english&cntry_code=us"
+ with_catch errmsg {
+ set page_from_fedex [ns_httpget $fedex_url]
+ regexp {(<TABLE WIDTH="290".*?</TABLE>).*?(<TABLE WIDTH="460" BORDER="0" CELLPADDING="2" CELLSPACING="1">.*?</TABLE>)} $page_from_fedex match detailed_info scan_activity
+ # Remove links
+ regsub -all -nocase {</*?a.*?>} $scan_activity "" scan_activity
+ set carrier_info "$detailed_info $scan_activity"
+ } {
+ set carrier_info "Unable to retrieve data from FedEx."
+ }
+} elseif { [string match "UPS*" $carrier] } {
+ set ups_url "http://wwwapps.ups.com/etracking/tracking.cgi?submit=Track&InquiryNumber1=$tracking_number&TypeOfInquiryNumber=T&build_detail=yes"
+ with_catch errmsg {
+ set ups_page [ns_httpget $ups_url]
+ if { ![regexp {(<TR><TD[^>]*>Tracking Number:.*</TABLE>).*Tracking results provided by UPS} $ups_page match ups_info] } {
+ set carrier_info "Unable to parse detail data from UPS."
+ } else {
+ # Remove spacer images
+ regsub -all -nocase {<img.*?>} $ups_info "" ups_info
+ set carrier_info "<table noborder>$ups_info"
+ }
+ } {
+ set carrier_info "Unable to retrieve data from UPS."
+ }
+
+}
+
+doc_body_append "<ul>
+<li>Shipping Date: $pretty_ship_date
+<li>Carrier: $carrier
+<li>Tracking Number: $tracking_number
+</ul>
+
+<h4>Information from [ec_decode $carrier "" "Carrier" $carrier]</h4>
+
+<blockquote>
+[ec_decode $carrier_info "" "None Available" $carrier_info]
+</blockquote>
+
+[ad_admin_footer]
+"
Index: openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/void-2-oracle.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/void-2-oracle.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/void-2-oracle.xql 3 Aug 2005 22:45:46 -0000 1.1
@@ -0,0 +1,27 @@
+<?xml version="1.0"?>
+
+<queryset>
+ <rdbms><type>oracle</type><version>8.1.6</version></rdbms>
+
+<fullquery name="order_update">
+ <querytext>
+
+ update ec_orders
+ set order_state='void',
+ reason_for_void=:reason_for_void,
+ voided_by=:customer_service_rep,
+ voided_date=sysdate
+ where order_id=:order_id
+
+ </querytext>
+</fullquery>
+
+
+<fullquery name="gift_certificates_reinst">
+ <querytext>
+ declare begin ec_reinst_gift_cert_on_order(:order_id); end;
+ </querytext>
+</fullquery>
+
+
+</queryset>
Index: openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/void-2-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/void-2-postgresql.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/void-2-postgresql.xql 3 Aug 2005 22:45:46 -0000 1.1
@@ -0,0 +1,27 @@
+<?xml version="1.0"?>
+
+<queryset>
+ <rdbms><type>postgresql</type><version>7.1</version></rdbms>
+
+<fullquery name="order_update">
+ <querytext>
+
+ update ec_orders
+ set order_state='void',
+ reason_for_void=:reason_for_void,
+ voided_by=:customer_service_rep,
+ voided_date=current_timestamp
+ where order_id=:order_id
+
+ </querytext>
+</fullquery>
+
+
+<fullquery name="gift_certificates_reinst">
+ <querytext>
+ select ec_reinst_gift_cert_on_order(:order_id)
+ </querytext>
+</fullquery>
+
+
+</queryset>
Index: openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/void-2.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/void-2.tcl,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/void-2.tcl 3 Aug 2005 22:45:46 -0000 1.1
@@ -0,0 +1,39 @@
+# /www/[ec_url_concat [ec_url] /admin]/orders/void-2.tcl
+ad_page_contract {
+
+ @author Eve Andersson (eveander@arsdigita.com)
+ @creation-date Summer 1999
+ @cvs-id $Id: void-2.tcl,v 1.1 2005/08/03 22:45:46 roelc Exp $
+ @author ported by Jerry Asher (jerry@theashergroup.com)
+} {
+ order_id:integer,notnull
+ reason_for_void
+}
+
+ad_require_permission [ad_conn package_id] admin
+
+ad_maybe_redirect_for_registration
+set customer_service_rep [ad_get_user_id]
+
+db_transaction {
+ db_dml order_update "
+ update ec_orders
+ set order_state='void',
+ reason_for_void=:reason_for_void,
+ voided_by=:customer_service_rep,
+ voided_date=sysdate
+ where order_id=:order_id
+ "
+
+ db_dml items_update "
+ update ec_items
+ set item_state='void',
+ voided_by=:customer_service_rep
+ where order_id=:order_id
+ "
+
+ # Reinstate gift certificates.
+ db_exec_plsql gift_certificates_reinst "declare begin ec_reinst_gift_cert_on_order(:order_id); end;"
+}
+
+ad_returnredirect "one?[export_url_vars order_id]"
Index: openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/void-2.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/void-2.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/void-2.xql 3 Aug 2005 22:45:46 -0000 1.1
@@ -0,0 +1,16 @@
+<?xml version="1.0"?>
+<queryset>
+
+<fullquery name="items_update">
+ <querytext>
+
+ update ec_items
+ set item_state='void',
+ voided_by=:customer_service_rep
+ where order_id=:order_id
+
+ </querytext>
+</fullquery>
+
+
+</queryset>
Index: openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/void.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/void.tcl,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/void.tcl 3 Aug 2005 22:45:46 -0000 1.1
@@ -0,0 +1,62 @@
+# /www/[ec_url_concat [ec_url] /admin]/orders/void.tcl
+ad_page_contract {
+
+ @author Eve Andersson (eveander@arsdigita.com)
+ @creation-date Summer 1999
+ @cvs-id $Id: void.tcl,v 1.1 2005/08/03 22:45:46 roelc Exp $
+ @author ported by Jerry Asher (jerry@theashergroup.com)
+} {
+ order_id:integer,notnull
+}
+
+ad_require_permission [ad_conn package_id] admin
+
+doc_body_append "[ad_admin_header "Void Order"]
+
+<h2>Void Order</h2>
+
+[ad_context_bar [list "../" "Ecommerce([ec_system_name])"] [list "index" "Orders"] [list "one?[export_url_vars order_id]" "One Order"] "Void"]
+
+<hr>
+"
+
+set n_shipped_items [db_string shipped_items_count "select count(*) from ec_items where order_id=:order_id and item_state in ('shipped', 'arrived', 'received_back')"]
+
+if { $n_shipped_items > 0 } {
+ doc_body_append "<font color=red>Warning:</font> our records show that at least one item in this
+ order has already shipped, which means that the customer has already been charged
+ (for shipped items only). Voiding an order will not cause
+ the customer's credit card to be refunded (you can only do that by marking
+ individual items \"received back\").
+ <p>
+ Voiding is usually done if the customer cancels their order before it ships.
+ <p>
+ "
+}
+
+doc_body_append "Note: this will cause all individual items in this order
+to be marked 'void'.
+<p>"
+
+doc_body_append "
+<form method=post action=void-2>
+[export_form_vars order_id]
+
+Please explain why you are voiding this order:
+
+<br>
+
+<blockquote>
+<textarea name=reason_for_void rows=5 cols=50 wrap>
+</textarea>
+</blockquote>
+
+<p>
+<center>
+<input type=submit value=\"Void It!\">
+</center>
+
+</form>
+
+[ad_admin_footer]
+"
Index: openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/void.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/void.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/dotlrn-ecommerce/www/admin/ecommerce/void.xql 3 Aug 2005 22:45:46 -0000 1.1
@@ -0,0 +1,11 @@
+<?xml version="1.0"?>
+<queryset>
+
+<fullquery name="shipped_items_count">
+ <querytext>
+ select count(*) from ec_items where order_id=:order_id and item_state in ('shipped', 'arrived', 'received_back')
+ </querytext>
+</fullquery>
+
+
+</queryset>