Index: openacs-4/packages/dotlrn-ecommerce/www/manage/add-course-admin.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/add-course-admin.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/add-course-admin.tcl 2 Sep 2008 21:54:47 -0000 1.1 @@ -0,0 +1,66 @@ +ad_page_contract { + Add a user to the admin relational segement +} { + object_id + userkey:multiple + authority_id + return_url +} + +set course_id $object_id +set userkey_list $userkey +unset userkey +foreach userkey $userkey_list { + + set username [lindex $userkey 0] + set authority_id [lindex $userkey 1] + set user_id [db_string get_user_id "select user_id from cc_users where authority_id = :authority_id and username = :username"] + + permission::grant \ + -object_id $course_id \ + -privilege "admin" \ + -party_id $user_id + db_1row get_course_community "select dc.community_id, dca.package_id from dotlrn_catalog dc, dotlrn_communities_all dca where course_id = (select latest_revision from cr_items where item_id = :course_id) and dc.community_id = dca.community_id" + set extra_vars [ns_set create] + ns_set put $extra_vars user_id $user_id + ns_set put $extra_vars community_id $community_id + relation_add -member_state "approved" -extra_vars $extra_vars dotlrn_admin_rel $community_id $user_id + + permission::grant \ + -object_id $community_id \ + -privilege "admin" \ + -party_id $user_id + set calendar_package_id [dotlrn_ecommerce::community_calendar_package_id -community_id $community_id] + permission::grant \ + -object_id $calendar_package_id \ + -party_id $user_id \ + -privilege "admin" + #permission::grant \ + -party_id $user_id \ + -privilege "admin" \ + -object_id $package_id + + db_foreach get_communities "select c.community_id, c.package_id from dotlrn_ecommerce_section s, dotlrn_communities_all c where s.course_id = :course_id and s.community_id = c.community_id" { + set extra_vars [ns_set create] + ns_set put $extra_vars user_id $user_id + ns_set put $extra_vars community_id $community_id + relation_add -member_state "approved" -extra_vars $extra_vars dotlrn_admin_rel $community_id $user_id + + permission::grant \ + -party_id $user_id \ + -privilege "admin" \ + -object_id $community_id + set calendar_package_id [dotlrn_ecommerce::community_calendar_package_id -community_id $community_id] + permission::grant \ + -object_id $calendar_package_id \ + -party_id $user_id \ + -privilege "admin" + + # permission::grant \ + -party_id $user_id \ + -privilege "admin" \ + -object_id $package_id + + } +} +ad_returnredirect -message "Administrator Added" $return_url \ No newline at end of file Index: openacs-4/packages/dotlrn-ecommerce/www/manage/application-approve.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/application-approve.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/application-approve.tcl 2 Sep 2008 21:54:47 -0000 1.1 @@ -0,0 +1,21 @@ +# packages/dotlrn-ecommerce/www/admin/application-approve.tcl + +ad_page_contract { + + + + @author Roel Canicula (roelmc@pldtdsl.net) + @creation-date 2005-06-23 + @arch-tag: 93f47ba6-c04e-419a-bcd6-60bb95553236 + @cvs-id $Id: application-approve.tcl,v 1.1 2008/09/02 21:54:47 daveb Exp $ +} { + community_id:integer,notnull + user_id:integer,notnull +} -properties { +} -validate { +} -errors { +} + +dotlrn_community::membership_approve -community_id $community_id -user_id $user_id + +ad_returnredirect ../applications Index: openacs-4/packages/dotlrn-ecommerce/www/manage/application-reject.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/application-reject.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/application-reject.tcl 2 Sep 2008 21:54:47 -0000 1.1 @@ -0,0 +1,21 @@ +# packages/dotlrn-ecommerce/www/admin/application-reject.tcl + +ad_page_contract { + + + + @author Roel Canicula (roelmc@pldtdsl.net) + @creation-date 2005-07-01 + @arch-tag: 93f47ba6-c04e-419a-bcd6-60bb95553236 + @cvs-id $Id: application-reject.tcl,v 1.1 2008/09/02 21:54:47 daveb Exp $ +} { + community_id:integer,notnull + user_id:integer,notnull +} -properties { +} -validate { +} -errors { +} + +dotlrn_community::membership_reject -community_id $community_id -user_id $user_id + +ad_returnredirect ../applications Index: openacs-4/packages/dotlrn-ecommerce/www/manage/application-view.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/application-view.adp,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/application-view.adp 2 Sep 2008 21:54:47 -0000 1.1 @@ -0,0 +1,7 @@ + + One Application + {../applications "#dotlrn-ecommerce.lt_Waiting_List_and_Prer#"} "One Application" + + + + \ No newline at end of file Index: openacs-4/packages/dotlrn-ecommerce/www/manage/application-view.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/application-view.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/application-view.tcl 2 Sep 2008 21:54:47 -0000 1.1 @@ -0,0 +1,16 @@ +# packages/dotlrn-ecommerce/www/admin/application-reject.tcl + +ad_page_contract { + + @author Tracy Adams (teadams@alum.mit.edu) + @creation-date 2005-08-022 + @arch-tag: + @cvs-id $Id: application-view.tcl,v 1.1 2008/09/02 21:54:47 daveb Exp $ +} { + session_id:integer,notnull + +} -properties { +} -validate { +} -errors { +} + Index: openacs-4/packages/dotlrn-ecommerce/www/manage/applications.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/applications.adp,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/applications.adp 2 Sep 2008 21:54:47 -0000 1.1 @@ -0,0 +1,5 @@ + + Pending Applications + {Pending Applications} + + \ No newline at end of file Index: openacs-4/packages/dotlrn-ecommerce/www/manage/applications.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/applications.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/applications.tcl 2 Sep 2008 21:54:47 -0000 1.1 @@ -0,0 +1,91 @@ +# packages/dotlrn-ecommerce/www/admin/applications.tcl + +ad_page_contract { + + + + @author Roel Canicula (roelmc@pldtdsl.net) + @creation-date 2005-06-23 + @arch-tag: 47e50b22-750a-4337-98ed-747058310624 + @cvs-id $Id: applications.tcl,v 1.1 2008/09/02 21:54:47 daveb Exp $ +} { + +} -properties { +} -validate { +} -errors { +} + +template::list::create \ + -name "applications" \ + -multirow "applications" \ + -no_data "No pending applications" \ + -page_flush_p 1 \ + -elements { + community_name { + label "Section" + } + person_name { + label "Participant" + } + member_state { + label "Member State" + } + assessment_result { + label "Application" + display_template { + + View + + + N/A + + } + html { align center } + } + actions { + label "" + display_template { + Approve + Reject + } + html { align center } + } + } + +db_multirow -extend { approve_url reject_url asm_url } applications applications { + select pretty_name as community_name, person__name(user_id) as person_name, member_state, c.community_id, user_id + from dotlrn_member_rels_full r, dotlrn_communities_all c + where r.community_id = c.community_id + and member_state = 'needs approval' +} { + set approve_url [export_vars -base application-approve { community_id user_id }] + set reject_url [export_vars -base application-reject { community_id user_id }] + + # Get associated assessment + if { [db_0or1row assessment { + select ss.session_id + + from dotlrn_ecommerce_section s, + (select c.* + from dotlrn_catalogi c, + cr_items i + where c.course_id = i.live_revision) c, + (select a.* + from as_assessmentsi a, + cr_items i + where a.assessment_id = i.latest_revision) a, + as_sessions ss + + where s.community_id = :community_id + and s.course_id = c.item_id + and c.assessment_id = a.item_id + and a.assessment_id = ss.assessment_id + and ss.subject_id = :user_id + + order by creation_datetime desc + + limit 1 + }] } { + set asm_url [export_vars -base /assessment/asm-admin/results-session { session_id }] + } +} \ No newline at end of file Index: openacs-4/packages/dotlrn-ecommerce/www/manage/course-add-edit.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/course-add-edit.adp,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/course-add-edit.adp 2 Sep 2008 21:54:47 -0000 1.1 @@ -0,0 +1,6 @@ + +@page_title@ +@context;noquote@ + + + Index: openacs-4/packages/dotlrn-ecommerce/www/manage/course-add-edit.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/course-add-edit.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/course-add-edit.tcl 2 Sep 2008 21:54:47 -0000 1.1 @@ -0,0 +1,237 @@ +ad_page_contract { + Displays a form to add a course or add a new revision of a course (edit) + + @author Miguel Marin (miguelmarin@viaro.net) + @author Viaro Networks www.viaro.net + @creation-date 27-01-2005 + +} { + course_id:optional + mode:optional + { return_url "" } + { index "" } +} + +set page_title "" +set context "" + +set user_id [ad_conn user_id] +set package_id [ad_conn package_id] +set cc_package_id [apm_package_id_from_key "dotlrn-catalog"] +if {[info exists course_id] && [db_string exists "select count(*) from acs_objects where object_id = :course_id" -default 0]} { + set revision_id $course_id + permission::require_permission -party_id $user_id -object_id $course_id -privilege "admin" +} else { + permission::require_permission -party_id $user_id -object_id $package_id -privilege "write" + set revision_id -1 +} + +set enable_applications_p [parameter::get -package_id [ad_conn package_id] -parameter EnableCourseApplicationsP -default 1] + +set mode_p edit + +# Get assessments +set assessment_package_id [parameter::get -package_id $package_id -parameter AssessmentPackage -default ""] +set asm_list [list [list "[_ dotlrn-catalog.not_associate]" "-1"]] +set assessment_query "assessment" + +set asm_list [db_list_of_lists assessment { }] + +# Get a list of all the attributes asociated to dotlrn_catalog +set attribute_list [package_object_attribute_list -start_with dotlrn_catalog dotlrn_catalog] +set elements "" + +# Creates the elements to show with ad_form +set count 1 +foreach attribute $attribute_list { + switch [lindex $attribute 2] { + display_p - + community_id { continue } + assessment_id { + if { ! $enable_applications_p } { + continue + } + } + } + set element_mode "" + set aditional_type "" + set aditional_elements "" + set help_text "" + + switch [lindex $attribute 4] { + string { + if { [string equal [lindex $attribute 2] "assessment_id"]} { + set aditional_type "(select)" + set aditional_elements [list options $asm_list] + } else { + if { [string equal [lindex $attribute 2] "course_key"]} { + set element_mode [list mode $mode_p] + set help_text [list help_text "Short name used in URL"] + } + } + } + text { + set aditional_type "(textarea)" + # html element needs to be a list not in curly braces + set aditional_elements [list html {rows 10 cols 55}] + } + integer { + if { [string equal [lindex $attribute 2] "assessment_id"]} { + set aditional_type "(select)" + set aditional_elements [list options $asm_list] + set help_text [list help_text "[_ dotlrn-ecommerce.as_widget_help]"] + } + } + boolean { + set aditional_type "(radio)" + set aditional_elements [list options {{Yes t} {No f}}] + } + } + if { $count > 3 } { + append aditional_type ",optional" + } + set element [list [lindex $attribute 2]:text${aditional_type} [list label [lindex $attribute 3]] $aditional_elements $element_mode $help_text] + lappend elements $element + + incr count +} + +# Create the form +ad_form -name add_course -export {mode $mode} -form { + course_id:key + {return_url:text(hidden),optional} +} + + +ad_form -extend -name add_course -form $elements + + + +ad_form -extend -name add_course -form { + {category_ids:text(category),multiple,optional + {label "[_ dotlrn-catalog.categories]"} + {html {size 4}} + {value "$revision_id $cc_package_id"} + {help_text "Hold the <Ctrl> key to select multiple categories"} + } + {display_p:boolean(radio) + {label "[_ dotlrn-ecommerce.Display_Course]"} + {options {{Yes t} {No f}}} + } +} + +ad_form -extend -name add_course -on_submit { + if { $category_ids == [list [list $revision_id $cc_package_id]] } { + set category_ids "" + } +} -new_data { + # New item and revision in the CR + set folder_id [dotlrn_catalog::get_folder_id] + set attribute_list [package_object_attribute_list -start_with dotlrn_catalog dotlrn_catalog] + set form_attributes [list] + + # Create master community + set community_id [dotlrn_club::new -pretty_name "[string_truncate -len 75 $course_name] Section Template"] + # add the calendar item type "session" + set calendar_id [dotlrn_calendar::get_group_calendar_id -community_id $community_id] + set item_type_id [calendar::item_type_new -calendar_id $calendar_id -type "Session"] + + # HAM : let's now add a "Section Administration" for the master community + set admin_portal_id [dotlrn_community::get_admin_portal_id -community_id $community_id] + set element_id [dotlrn_ecommerce_admin_portlet::add_self_to_page -portal_id $admin_portal_id -package_id [db_string "getpackage_id" "select package_id from dotlrn_communities where community_id=:community_id"]] + ns_log Notice "DEBUG : Added Admin Portal $element_id" + # we want the section admin portlet to be at the top + db_dml "bring_portlet_to_top" "update portal_element_map set sort_key=0, region=1 where element_id=:element_id" + + + foreach attribute $attribute_list { + set attr_name [lindex $attribute 2] + if { [info exists $attr_name] } { + lappend form_attributes [list $attr_name [set $attr_name]] + } + } + if { [dotlrn_catalog::check_name -name $course_key] } { + set item_id [content::item::new -name $course_key -parent_id $folder_id \ + -content_type "dotlrn_catalog" -creation_user $user_id \ + -attributes $form_attributes -is_live t -title $course_key] + } else { + ad_return_complaint 1 "\#dotlrn-catalog.name_already\#" + ad_script_abort + } + # Grant admin privileges to the user over the item in the CR + permission::grant -party_id $user_id -object_id $item_id -privilege "admin" + permission::grant -party_id $user_id -object_id $community_id -privilege "admin" + permission::grant -party_id [dotlrn_ecommerce::admins_rel_segment -package_id [ad_conn package_id]] -object_id $community_id -privilege "admin" + + set revision_id [db_string get_revision_id { } -default "-1"] + if { ![string equal $category_ids "-1"] } { + category::map_object -object_id $revision_id $category_ids + } + + # add email template defaults + # we will fall back to the site wide defaults instead + + if { [exists_and_not_null assessment_id] } { + # Make sure assessment permissions are properly set + set registered_users [acs_magic_object registered_users] + permission::grant -party_id $registered_users -object_id $assessment_id -privilege "read" + permission::grant -party_id $registered_users -object_id $assessment_id -privilege "write" + permission::grant -party_id $registered_users -object_id $assessment_id -privilege "create" + } + +} -edit_data { + # New revision in the CR + catch { + db_1row template_community { + select community_id + from dotlrn_catalogi + where course_id = :course_id + } + } + + set folder_id [dotlrn_catalog::get_folder_id] + set item_id [dotlrn_catalog::get_item_id -revision_id $course_id] + set attribute_list [package_object_attribute_list -start_with dotlrn_catalog dotlrn_catalog] + set form_attributes [list] + foreach attribute $attribute_list { + set attr_name [lindex $attribute 2] + if { [info exists $attr_name] } { + lappend form_attributes [list $attr_name [set $attr_name]] + } + } + + set revision_id [content::revision::new -item_id $item_id -attributes $form_attributes -content_type "dotlrn_catalog"] + + # Set the new revision live + dotlrn_catalog::set_live -revision_id $revision_id + if { ![string equal $category_ids "-1"] } { + category::map_object -object_id $revision_id $category_ids + } + + if { [exists_and_not_null assessment_id] } { + # Make sure assessment permissions are properly set + set registered_users [acs_magic_object registered_users] + permission::grant -party_id $registered_users -object_id $assessment_id -privilege "read" + permission::grant -party_id $registered_users -object_id $assessment_id -privilege "write" + permission::grant -party_id $registered_users -object_id $assessment_id -privilege "create" + } + +} -new_request { + set context [list [list course-list "[_ dotlrn-catalog.course_list]"] "[_ dotlrn-catalog.new_course]"] + set page_title "[_ dotlrn-catalog.new_course]" + set revision_id "-1" + set display_p t + set auto_register_p f +} -edit_request { + set context [list [list course-list "[_ dotlrn-catalog.course_list]"] "[_ dotlrn-catalog.edit_course]"] + set page_title "[_ dotlrn-catalog.edit_course]" + db_1row get_course_info { } + db_string get_course_assessment { } -default "[_ dotlrn-catalog.not_associated]" + set auto_register_p [ad_decode $auto_register_p "" f $auto_register_p] +} -after_submit { + if { $return_url == "" } { + # set return_url [export_vars -base course-info {course_id course_name course_key} ] + set return_url "course-info?course_id=$revision_id&course_name=$course_name&course_key=$course_key" + } + ad_returnredirect "$return_url" +} \ No newline at end of file Index: openacs-4/packages/dotlrn-ecommerce/www/manage/course-add-edit.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/course-add-edit.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/course-add-edit.xql 2 Sep 2008 21:54:47 -0000 1.1 @@ -0,0 +1,35 @@ + + + + + + select cr.title ,ci.item_id as assessment_id from + cr_items ci, cr_revisions cr, as_assessments a, cr_folders cf + where cr.revision_id = ci.latest_revision and a.assessment_id = cr.revision_id and cf.folder_id = ci.parent_id and cf.package_id = :assessment_package_id + order by lower(cr.title) + + + + + + select * from dotlrn_catalog where course_id = :course_id + + + + + + select cr.title from + cr_folders cf, cr_items ci, cr_revisions cr, as_assessments a + where cr.revision_id = ci.latest_revision and a.assessment_id = cr.revision_id and + ci.parent_id = cf.folder_id and ci.item_id = :assessment_id order by cr.title + + + + + + select revision_id from cr_revisions where item_id = :item_id + + + + + Index: openacs-4/packages/dotlrn-ecommerce/www/manage/course-admins.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/course-admins.adp,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/course-admins.adp 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,14 @@ + +@page_title@ +@context@ + + + \ No newline at end of file Index: openacs-4/packages/dotlrn-ecommerce/www/manage/course-admins.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/course-admins.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/course-admins.tcl 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,31 @@ +ad_page_contract { + Manage who can administer this course +} { + object_id:integer,notnull +} + +permission::require_permission \ + -party_id [ad_conn user_id] \ + -object_id $object_id \ + -privilege "admin" + +set page_title "Course Administrators" +set context [list $page_title] + +set return_url [ad_return_url] + +set course_id $object_id +template::list::create \ + -name admins \ + -multirow admins \ + -key user_id \ + -bulk_action_export_vars {return_url course_id} \ + -filters {return_url {}} \ + -elements { + first_names {} + last_name {} + username {} + email {} + } -bulk_actions {Remove remove-admin "Remove Admin"} + +db_multirow admins admins "select first_names,last_name,username,email,user_id from acs_users_all u, acs_permissions p where object_id = :object_id and p.grantee_id = u.user_id and p.privilege = 'admin'" \ No newline at end of file Index: openacs-4/packages/dotlrn-ecommerce/www/manage/course-attribute-add.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/course-attribute-add.adp,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/course-attribute-add.adp 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,5 @@ + + Add Attribute + {course-attributes {Course Attributes}} {Add Attribute} + + \ No newline at end of file Index: openacs-4/packages/dotlrn-ecommerce/www/manage/course-attribute-add.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/course-attribute-add.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/course-attribute-add.tcl 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,48 @@ +# packages/dotlrn-ecommerce/www/admin/course-attribute-add.tcl + +ad_page_contract { + + + + @author Roel Canicula (roelmc@pldtdsl.net) + @creation-date 2005-05-24 + @arch-tag: 95d276e0-dd4f-4ab1-bf84-02369541f50d + @cvs-id $Id: course-attribute-add.tcl,v 1.1 2008/09/02 21:54:48 daveb Exp $ +} { + +} -properties { +} -validate { +} -errors { +} + +set attribute_list [package_object_attribute_list -start_with dotlrn_catalog dotlrn_catalog] +set sort_order [expr [llength $attribute_list] + 1] + +ad_form -name attribute -export {sort_order} -form { + {name:text {label "Attribute Name"} + {help_text "Unique key to identify the attribute"} + } + {title:text {label "Title"}} + {widget:text(select) {label "Widget"} {options { + {"String (textfield)" string} \ + {"Text (textbox)" text} \ + {Integer integer} \ + {Boolean boolean} + }}} +} -on_submit { + array set spec [list string text text text integer integer boolean boolean] + + content::type::attribute::new \ + -content_type "dotlrn_catalog" \ + -attribute_name $name \ + -datatype $widget \ + -pretty_name $title \ + -sort_order $sort_order \ + -column_spec $spec($widget) + + util_memoize_flush [list package_object_attribute_list_cached -start_with dotlrn_catalog -include_storage_types type_specific dotlrn_catalog] + util_memoize_flush [list package_object_attribute_list_cached -start_with acs_object -include_storage_types type_specific dotlrn_catalog] + + ad_returnredirect course-attributes + ad_script_abort +} \ No newline at end of file Index: openacs-4/packages/dotlrn-ecommerce/www/manage/course-attributes-delete.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/course-attributes-delete.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/course-attributes-delete.tcl 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,27 @@ +# packages/dotlrn-ecommerce/www/admin/course-attributes-delete.tcl + +ad_page_contract { + + + + @author Roel Canicula (roelmc@pldtdsl.net) + @creation-date 2005-05-24 + @arch-tag: 3531c32e-3d16-4e9c-a58b-0b4c6651f82f + @cvs-id $Id: course-attributes-delete.tcl,v 1.1 2008/09/02 21:54:48 daveb Exp $ +} { + name:multiple +} -properties { +} -validate { +} -errors { +} + +foreach attribute $name { + content::type::attribute::delete \ + -content_type dotlrn_catalog \ + -attribute_name $attribute \ + -drop_column 1 +} + +util_memoize_flush [list package_object_attribute_list_cached -start_with dotlrn_catalog -include_storage_types type_specific dotlrn_catalog] + +ad_returnredirect course-attributes \ No newline at end of file Index: openacs-4/packages/dotlrn-ecommerce/www/manage/course-attributes.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/course-attributes.adp,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/course-attributes.adp 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,5 @@ + + Course Attributes + {Course Attributes} + + \ No newline at end of file Index: openacs-4/packages/dotlrn-ecommerce/www/manage/course-attributes.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/course-attributes.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/course-attributes.tcl 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,50 @@ +# packages/dotlrn-ecommerce/www/admin/course-attributes.tcl + +ad_page_contract { + + + + @author Roel Canicula (roelmc@pldtdsl.net) + @creation-date 2005-05-24 + @arch-tag: 787e3cbd-2eb2-4e8e-8ba6-8fde9a2643de + @cvs-id $Id: course-attributes.tcl,v 1.1 2008/09/02 21:54:48 daveb Exp $ +} { + +} -properties { +} -validate { +} -errors { +} + +set attribute_list [package_object_attribute_list -start_with dotlrn_catalog dotlrn_catalog] + +template::multirow create attributes attribute_id name title sort_order widget + +set count 1 +foreach attribute $attribute_list { + template::multirow append attributes \ + [lindex $attribute 0] \ + [lindex $attribute 2] \ + [lindex $attribute 3] \ + $count \ + [lindex $attribute 4] + + incr count +} + +template::list::create \ + -name attributes \ + -key name \ + -elements { + name { + label Name + } + title { + label Title + } + widget { + label Datatype + } + } \ + -multirow attributes \ + -actions {Add course-attribute-add Add} \ + -bulk_actions {Delete course-attributes-delete Delete} \ No newline at end of file Index: openacs-4/packages/dotlrn-ecommerce/www/manage/course-delete.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/course-delete.adp,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/course-delete.adp 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,33 @@ + +@page_title@ +@context;noquote@ +
+#dotlrn-catalog.this_course# @rev_num@ + + #dotlrn-catalog.version# + + + #dotlrn-catalog.versions# + + + + #dotlrn-catalog.has_no# + + + + #dotlrn-catalog.and_has_one# + + + #dotlrn-catalog.and_has# @assoc_num@ #dotlrn-catalog.to_dotlrn# + + +
+ +#dotlrn-catalog.do_you_still# +

+ + + + There are @sections@ section(s) under this course + and it cannot be deleted. + \ No newline at end of file Index: openacs-4/packages/dotlrn-ecommerce/www/manage/course-delete.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/course-delete.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/course-delete.tcl 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,44 @@ +ad_page_contract { + Displays a delete confirmation message and deletes the course + @author Miguel Marin (miguelmarin@viaro.net) + @author Viaro Networks www.viaro.net + @creation-date 09-02-2005 +} { + object_id:notnull + course_key:notnull + creation_user:notnull +} + +set page_title "[_ dotlrn-catalog.confirm_delete] $course_key" +set context [list [list course-list "[_ dotlrn-catalog.course_list]"] "[_ dotlrn-catalog.delete_course]"] +set course_id [db_string "live_rev" "select latest_revision from cr_items where item_id=:object_id"] + +# Check for create permissions over dotlrn-catalog +set user_id [ad_conn user_id] +set cc_package_id [apm_package_id_from_key "dotlrn-catalog"] +permission::require_permission -party_id $user_id -object_id $object_id -privilege "delete" + + +set rev_assoc [dotlrn_catalog::check_rev_assoc -item_id $object_id] +set rev_num [lindex $rev_assoc 0] +set assoc_num [lindex $rev_assoc 1] + +db_1row sections { + select count(*) as sections + from dotlrn_ecommerce_section + where course_id = :object_id +} + +if { ! $sections } { + + ad_form -name delete_course -export {course_key $course_key creation_user $creation_user } -cancel_url "course-info?course_id=$course_id" -form { + {object_id:text(hidden) + { value $object_id } + } + } -on_submit { + dotlrn_catalog::course_delete -item_id $object_id + } -after_submit { + ad_returnredirect "course-list" + } + +} \ No newline at end of file Index: openacs-4/packages/dotlrn-ecommerce/www/manage/course-info-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/course-info-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/course-info-oracle.xql 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,23 @@ + + + + + oracle + 8.1.6 + + + + + + select item_type_id from cal_item_types where type='Session' and calendar_id = :template_calendar_id and rownum = 1 + + + + + + select item_type_id from cal_item_types where type='Session' and calendar_id = :calendar_id and rownum = 1 + + + + + \ No newline at end of file Index: openacs-4/packages/dotlrn-ecommerce/www/manage/course-info-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/course-info-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/course-info-postgresql.xql 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,20 @@ + + + + postgresql + 7.1 + + + + + select item_type_id from cal_item_types where type='Session' and calendar_id = :template_calendar_id limit 1 + + + + + + select item_type_id from cal_item_types where type='Session' and calendar_id = :calendar_id limit 1 + + + + \ No newline at end of file Index: openacs-4/packages/dotlrn-ecommerce/www/manage/course-info.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/course-info.adp,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/course-info.adp 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,129 @@ + +@page_title@ +@context;noquote@ + + + + +
+ +
+ + +

#dotlrn-catalog.info#:

+ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + +
+ +
+ +
+ + 		+ #dotlrn-catalog.course_key# + + + @course_key@ +
#dotlrn-catalog.course_name#@name@
#dotlrn-catalog.course_info#@info;noquote@
+ #dotlrn-catalog.dotlrn#: + + + #dotlrn-catalog.no# + + + (#dotlrn-catalog.associate#) + + + + + #dotlrn-catalog.yes# (#dotlrn-catalog.watch#) + + + #dotlrn-catalog.yes# (#dotlrn-catalog.watch#) + + +
#dotlrn-catalog.asm#:@asm@
+ #dotlrn-catalog.categorize#: + #dotlrn-catalog.yes# (@category_names@)
+ + + + #dotlrn-catalog.enroll# + + +
+ #dotlrn-catalog.enroll_not# + + + + + + + + + + + + + Add a section + Edit + #dotlrn-catalog.delete# + + #dotlrn-catalog.categorize# + +
Template Community:User Admin Add Session
Manage Course Administrators
+ +

Sections

+ + + +

Email Templates

+Administer Email Templates + \ No newline at end of file Index: openacs-4/packages/dotlrn-ecommerce/www/manage/course-info.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/course-info.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/course-info.tcl 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,314 @@ +ad_page_contract { + Displays information of one course + @author Miguel Marin (miguelmarin@viaro.net) + @author Viaro Networks www.viaro.net + @creation-date 15-02-2005 +} { + course_id:notnull + { index "" } + { return_url "" } +} -validate { + course_exists { + # check if the course exists and set course_name and course_key + if {![db_0or1row get_course "select course_name, course_key from dotlrn_catalog where course_id=:course_id"]} { + ad_return notfound + } + } +} + +# TODO DaveB is there a Tcl proc for course info? + +if { [string equal $index ""] } { + set context [list [list "course-list" "Course List"] "$course_name"] +} else { + set context [list [list "../course-info?course_id=$course_id" "[_ dotlrn-catalog.one_course_info]"] "$course_name"] + set return_url "${return_url}&index=yes" +} + +# HAM : do we show template community buttons +set show_template_community_admin [parameter::get -parameter ShowTemplateCommunityAdminP -default 0] + +# Check permission over course_id +permission::require_permission -object_id $course_id -privilege "admin" +set package_admin_p [permission::permission_p -object_id [ad_conn package_id] -privilege "admin" -party_id [ad_conn user_id]] + +db_1row get_course_info { } +set page_title "$course_name" + +set asm_name [db_string get_asm_name { } -default "[_ dotlrn-catalog.not_associated]"] +set asm_url "/assessment/asm-admin/one-a?assessment_id=$assessment_id" +set item_id [dotlrn_catalog::get_item_id -revision_id $course_id] +set creation_user [dotlrn_catalog::get_creation_user -object_id $item_id] +set rel [dotlrn_catalog::has_relation -course_id $course_id] + + +set dotlrn_url [dotlrn::get_url] +if { ![info exists index] } { + set index "" +} + +if { ![info exists to_index] } { + set to_index "" +} + +if { [info exist return_url] } { + set return_url $return_url +} else { + set return_url "course-info?course_id=$course_id&course_name=$name&course_key=$course_key" +} + +if { ![info exists asmid] } { + set asmid "-1" +} + +if { ![info exists revision] } { + set revision "no" +} + +set category_p [db_string get_category { } -default -1] + +set info $course_info +set name $course_name +set edit "yes" +set asm $asm_name +set info [ad_html_text_convert -from text/enhanced -to text/plain $info] + +set cc_package_id [apm_package_id_from_key "dotlrn-catalog"] + +# Get the category name +set category_names [join [category::get_names [category::get_mapped_categories $course_id]] ", "] + +# Check if user has admin permission over course_id +set admin_p 0 +if { [permission::permission_p -object_id $cc_package_id -privilege "create"] } { + set item_id [dotlrn_catalog::get_item_id -revision_id $course_id] + if { [permission::permission_p -object_id $course_id -privilege "admin"] } { + set admin_p 1 + } else { + set admin_p 0 + } +} + + +set obj_n 0 +set dotlrn_class "(" +set dotlrn_com "(" + +# For dotlrn associations +db_multirow -extend { obj_n admin_p } relations relation { } { + set obj_n 1 + if { [string equal $type "dotlrn_catalog_class_rel" ]} { + append dotlrn_class "'$object_id'" + append dotlrn_class "," + } else { + append dotlrn_com "'$object_id'" + append dotlrn_com "," + } +} +append dotlrn_class "0)" +append dotlrn_com "0)" + +db_multirow classes_list get_dotlrn_classes { } + +template::list::create \ + -name dotlrn_classes \ + -multirow classes_list \ + -key object_id \ + -row_pretty_plural "[_ dotlrn-catalog.dotlrn_classes]" \ + -elements { + class { + label "[_ dotlrn-catalog.class_name]" + display_template { + + @classes_list.pretty_name@ + + + @classes_list.pretty_name@ + + } + } + dep_name { + label "[_ dotlrn-catalog.dep_name]" + display_template { + @classes_list.department_name@ + } + } + term_name { + label "[_ dotlrn-catalog.term_name]" + display_template { + @classes_list.term_name@ + } + } + subject { + label "[_ dotlrn-catalog.subject_name]" + display_template { + @classes_list.class_name@ + } + } + } + + +db_multirow com_list get_dotlrn_communities { } + + +template::list::create \ + -name dotlrn_communities \ + -multirow com_list \ + -key object_id \ + -row_pretty_plural "[_ dotlrn-catalog.dotlrn_com]" \ + -elements { + community { + label "[_ dotlrn-catalog.com_name]" + display_template { + + @com_list.pretty_name@ + + + @com_list.pretty_name@ + + + } + } + } + + +set return_url [ns_urlencode "course-info?course_id=$course_id&course_key=$course_key&course_name=$course_name"] + + +set community_url "" + +db_multirow -extend {community_url calendar_url num_sessions attendees available_slots sessions} section_list section_list { + select s.section_id, s.section_name, s.product_id, s.community_id, v.maxparticipants + from dotlrn_ecommerce_section s, dotlrn_catalogi c, ec_custom_product_field_values v + where s.course_id = c.item_id + and c.course_id = :course_id + and s.product_id = v.product_id + + order by lower(s.section_name) +} { + ns_log notice "DEBUG:: $section_name, $community_id" + + set community_url [dotlrn_community::get_community_url $community_id] + + set calendar_id [dotlrn_calendar::get_group_calendar_id -community_id $community_id] + + set calendar_url [calendar_portlet_display::get_url_stub $calendar_id] + + set item_type_id [db_string item_type_id { } -default 0] + + set num_sessions [db_string num_sessions {} ] + + set attendees [dotlrn_ecommerce::section::attendees $section_id] + set available_slots [dotlrn_ecommerce::section::available_slots $section_id] + set sessions [util_memoize [list dotlrn_ecommerce::section::sessions $calendar_id]] +} + +template::list::create \ + -name section_list \ + -multirow section_list \ + -key section_id \ + -bulk_action_method post \ + -elements { + name { + label "Name" + display_template { + @section_list.section_name@ + } + } + dates { + label "Dates" + display_template { + @section_list.sessions;noquote@ + } + } + registration { + label "Registration" + display_template { + Registrants
+ @section_list.attendees@ participants,
@section_list.available_slots@ available + } + } + members { + label "Purchases" + html { align center } + display_template { + Process Purchase + } + } + actions { + label "Actions" + display_template { Remove } + + } + } + +# ad_form -name session -export { course_id return_url } -form { +# {session_name:text {label "Session Name"}} +# {session_description:text(textarea) {label "Section Description"} {html {cols 50 rows 5}}} +# {typical_start_time:date {label "Typical Start Time"} {format {[lc_get formbuilder_time_format]}}} +# {typical_end_time:date {label "Typical End Time"} {format {[lc_get formbuilder_time_format]}}} +# original_url:text(hidden) +# } -on_request { +# set original_url [ad_return_url] +# } -on_submit { + +# set typical_start_time [lindex $typical_start_time 3]:[lindex $typical_start_time 4] +# set typical_end_time [lindex $typical_end_time 3]:[lindex $typical_end_time 4] + +# db_dml new_session { +# insert into dotlrn_ecommerce_predefined_sessions +# (course_item_id, session_name, session_description, typical_start_time, typical_end_time) +# values +# (:item_id, :session_name, :session_description, :typical_start_time, :typical_end_time) +# } + +# } -after_submit { +# ad_returnredirect "$original_url#Sessions" +# ad_script_abort +# } + +# template::list::create \ +# -name sessions \ +# -multirow sessions \ +# -key session_id \ +# -bulk_actions {Delete predefined-session-delete Delete} \ +# -bulk_action_export_vars { {return_url "[ad_return_url]#Sessions"} } \ +# -elements { +# session_name { +# label "Session Name" +# } +# session_description { +# label "Session Description" +# } +# typical_start_time { +# label "Typical Start Time" +# } +# typical_end_time { +# label "Typical End Time" +# } +# } + +# db_multirow sessions sessions { +# select session_id, session_name, session_description, to_char(typical_start_time, 'HH:MIAM') as typical_start_time, to_char(typical_end_time, 'HH:MIAM') as typical_end_time +# from dotlrn_ecommerce_predefined_sessions +# where course_item_id = :item_id +# } + +catch { + db_1row template_community { + select c.community_id as template_community_id + from dotlrn_catalogi c + where c.course_id = :course_id + } + + if { [empty_string_p $template_community_id] } { + unset template_community_id + } else { + set template_community_url [dotlrn_community::get_community_url $template_community_id] + set template_calendar_id [dotlrn_calendar::get_group_calendar_id -community_id $template_community_id] + set template_item_type_id [db_string get_item_type_id { } -default 0] + set template_calendar_url [export_vars -base ${template_community_url}calendar/cal-item-new { {item_type_id $template_item_type_id} {calendar_id $template_calendar_id} {view day} }] + } +} +set permissions_url [export_vars -base course-admins {{object_id $item_id}}] +set return_url [ad_return_url] Index: openacs-4/packages/dotlrn-ecommerce/www/manage/course-info.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/course-info.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/course-info.xql 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,72 @@ + + + + + + select dc.course_info, dc.assessment_id, cr.item_id + from dotlrn_catalog dc, cr_revisions cr + where cr.revision_id = :course_id and dc.course_id = :course_id + + + + + + select cr.title from + cr_folders cf, cr_items ci, cr_revisions cr, as_assessments a + where cr.revision_id = ci.latest_revision and a.assessment_id = cr.revision_id and + ci.parent_id = cf.folder_id and ci.item_id = :assessment_id order by cr.title + + + + + + + select count(object_id) from category_object_map where object_id = :course_id + + + + + + select tree_id from category_tree_map where object_id = :cc_package_id + + + + + + select object_id_two as object_id, rel_type as type from acs_rels + where object_id_one = :course_id order by type + + + + + + select class_instance_id as object_id, department_name, term_name, class_name, pretty_name, url + from dotlrn_class_instances_full where class_instance_id in $dotlrn_class + order by department_name, term_name, class_name, pretty_name + + + + + + select community_id as object_id, pretty_name, url from dotlrn_clubs_full + where community_id in $dotlrn_com order by pretty_name + + + + + + select count(cal_item_id) from cal_items where on_which_calendar = :calendar_id and item_type_id = :item_type_id + + + + + + select count(*) as attendees + from dotlrn_member_rels_approved + where community_id = :community_id + and (rel_type = 'dotlrn_member_rel' + or rel_type = 'dc_student_rel') + + + + \ No newline at end of file Index: openacs-4/packages/dotlrn-ecommerce/www/manage/course-list.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/course-list.adp,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/course-list.adp 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,28 @@ + +@page_title@ +@context;noquote@ + + +

+ +

+ #dotlrn-catalog.search_courses# + + +
+
+#dotlrn-catalog.new_course# +

+ + +
+#dotlrn-catalog.new_course# +

+ + Index: openacs-4/packages/dotlrn-ecommerce/www/manage/course-list.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/course-list.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/course-list.tcl 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,126 @@ +ad_page_contract { + Displays a list of all courses + @author Miguel Marin (miguelmarin@viaro.net) + @author Viaro Networks www.viaro.net + @creation-date 28-01-2005 +} { + page:optional + keyword:optional +} + + +set return_url [ad_return_url] +set user_id [ad_conn user_id] +set context [list "Course List"] +set page_title "[_ dotlrn-catalog.course_list]" + +set cc_package_id [apm_package_id_from_key "dotlrn-catalog"] + +permission::require_permission -party_id $user_id -object_id $cc_package_id -privilege "create" + +if {[permission::permission_p -party_id $user_id -object_id $cc_package_id -privilege "admin"]} { + set admin_p 1 +} else { + set admin_p 0 +} + +# The tree id from categories +set tree_list [category_tree::get_mapped_trees $cc_package_id] +if { [string equal [lindex [lindex $tree_list 0] 1] "dotlrn-course-catalog"] } { + set tree_id [lindex [lindex $tree_list 0] 0] +} else { + set tree_id "" +} + +if { [acs_user::site_wide_admin_p] } { + if { [info exist keyword] } { + set query get_course_info_site_wide_keyword + set paginator_query site_wide_paginator_keyword + } else { + set query get_course_info_site_wide + set paginator_query site_wide_paginator + } +} else { + if { [info exist keyword] } { + set query get_course_info_keyword + set paginator_query site_wide_paginator_keyword + } else { + set query get_course_info + set paginator_query paginator + } +} + +template::list::create \ + -name course_list \ + -multirow course_list \ + -key course_id \ + -page_groupsize 5 \ + -page_flush_p t \ + -page_query_name $query \ + -bulk_action_method post \ + -bulk_action_export_vars { + }\ + -row_pretty_plural "[_ dotlrn-catalog.courses]" \ + -elements { + name { + label "Course Name (Key)" + display_template { +

+ @course_list.course_name@ (@course_list.course_key@) +
+ } + } + category { + label "Categories" + display_template { +
+ + @course_list.category_name@ + + + #dotlrn-catalog.uncat# + +
+ } + } + sections { + label Sections + html { align center } + display_template { + + @course_list.sections@ + + } + } + actions { + + label "[_ dotlrn-catalog.admin] [_ dotlrn-catalog.Actions]" + display_template { +
+ + +
+ } + } + } + +db_multirow -extend { asm_name rel item_id creation_user category_name sections } course_list $query {} { + set asm_name [db_string get_asm_name { } -default "[_ dotlrn-catalog.not_associated]"] + set item_id [dotlrn_catalog::get_item_id -revision_id $course_id] + set creation_user [dotlrn_catalog::get_creation_user -object_id $item_id] + set rel [dotlrn_catalog::has_relation -course_id $course_id] + set category_name "" + set mapped [category::get_mapped_categories $course_id] + foreach element $mapped { + append category_name "[category::get_name $element], " + } + set category_name [string range $category_name 0 [expr [string length $category_name] - 3]] + + db_1row sections { + select count(*) as sections + from dotlrn_ecommerce_section + where course_id = :item_id + } +} + Index: openacs-4/packages/dotlrn-ecommerce/www/manage/course-list.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/course-list.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/course-list.xql 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,98 @@ + + + + + + select cc.course_id, cc.course_key, cc.course_name, cc.course_info, cc.assessment_id, ci.item_id + from dotlrn_catalog cc, cr_items ci + where cc.course_id = ci.live_revision and + ci.item_id in ( + select object_id from acs_permissions where grantee_id = :user_id and + privilege = 'admin') + + + + + + select cc.course_id, cc.course_key, cc.course_name, cc.course_info, cc.assessment_id, ci.item_id + from dotlrn_catalog cc, cr_items ci + where cc.course_id = ci.live_revision + order by lower(cc.course_name) + + + + + + select cc.course_id, cc.course_key, cc.course_name, cc.course_info, cc.assessment_id, ci.item_id + from dotlrn_catalog cc, cr_items ci + where cc.course_id = ci.live_revision and + (lower(cc.course_name) like lower('%$keyword%') or + lower(cc.course_key) like lower('%$keyword%')) and + ci.item_id in ( + select object_id from acs_permissions where grantee_id = :user_id and + privilege = 'admin') + + + + + + select cc.course_id, cc.course_key, cc.course_name, cc.course_info, cc.assessment_id, ci.item_id + from dotlrn_catalog cc, cr_items ci + where cc.course_id = ci.live_revision and + (lower(cc.course_name) like lower('%$keyword%') or + lower(cc.course_key) like lower('%$keyword%')) + + + + + + select cr.title from + cr_folders cf, cr_items ci, cr_revisions cr, as_assessments a + where cr.revision_id = ci.latest_revision and a.assessment_id = cr.revision_id and + ci.parent_id = cf.folder_id and ci.item_id = :assessment_id order by cr.title + + + + + + select cc.course_id, cc.course_key, cc.course_name, cc.course_info, cc.assessment_id, ci.item_id + from dotlrn_catalog cc, cr_items ci + where cc.course_id = ci.live_revision and + ci.item_id in ( + select object_id from acs_permissions where grantee_id = :user_id and + privilege = 'admin') + + + + + + select cc.course_id, cc.course_key, cc.course_name, cc.course_info, cc.assessment_id, ci.item_id + from dotlrn_catalog cc, cr_items ci + where cc.course_id = ci.live_revision + + + + + + select cc.course_id, cc.course_key, cc.course_name, cc.course_info, cc.assessment_id, ci.item_id + from dotlrn_catalog cc, cr_items ci + where cc.course_id = ci.live_revision + (lower(cc.course_name) like lower('%$keyword%') or + lower(cc.course_key) like lower('%$keyword%')) and + ci.item_id in ( + select object_id from acs_permissions where grantee_id = :user_id and + privilege = 'admin') + + + + + + select cc.course_id, cc.course_key, cc.course_name, cc.course_info, cc.assessment_id, ci.item_id + from dotlrn_catalog cc, cr_items ci + where cc.course_id = ci.live_revision + (lower(cc.course_name) like lower('%$keyword%') or + lower(cc.course_key) like lower('%$keyword%')) and + + + + Index: openacs-4/packages/dotlrn-ecommerce/www/manage/email-template-delete.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/email-template-delete.adp,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/email-template-delete.adp 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,2 @@ + + \ No newline at end of file Index: openacs-4/packages/dotlrn-ecommerce/www/manage/email-template-delete.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/email-template-delete.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/email-template-delete.tcl 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,69 @@ +# packages/dotlrn-ecommerce/www/admin/email-template.tcl + +ad_page_contract { + + add/edit an email template + + @author Deds Castillo (deds@i-manila.com.ph) + @creation-date 2005-07-20 + @arch-tag: cb528bf6-f4e5-4c87-bbe9-e987780a6709 + @cvs-id $Id: email-template-delete.tcl,v 1.1 2008/09/02 21:54:48 daveb Exp $ +} { + {section_id ""} + {community_id ""} + {action ""} + {return_url ""} +} -properties { +} -validate { +} -errors { +} + +if {[empty_string_p $return_url]} { + set return_url [export_vars -base "one-section" {section_id}] +} + +set title "Add/edit email template" +if {![exists_and_not_null community_id]} { + set community_id [db_string get_community_id { + select community_id + from dotlrn_ecommerce_section + where section_id = :section_id + }] +} + +switch -exact $action { + "submit_app" { + set type "application sent" + } + "approve_app" { + set type "on approval" + } + "on join" - + "application sent" - + "on approval" - + "waitinglist approved" - + "prereq approval" - + "prereq reject" { + set type $action + } + default { + set type "" + } +} + +if {![db_0or1row get_email "select * from dotlrn_member_emails where community_id=:community_id and type=:type" -column_array email]} { + ad_returnredirect $return_url + ad_script_abort +} + +set info "Are you sure you want to delete '$email(subject)' for [dotlrn_ecommerce::email_type_pretty -type $type]?" +ad_form -name delete -export {section_id community_id action return_url type} -cancel_url $return_url -form { + {info:text(inform) {label "$info"}} + } -on_submit { + db_dml delete_email "delete from dotlrn_member_emails where community_id=:community_id and type=:type" + ad_returnredirect $return_url + ad_script_abort + } + +set page_title "Delete email" +ad_return_template \ No newline at end of file Index: openacs-4/packages/dotlrn-ecommerce/www/manage/email-template.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/email-template.adp,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/email-template.adp 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,7 @@ + + @title@ +@template@
+#dotlrn-ecommerce.Sent_when#
+ + + Index: openacs-4/packages/dotlrn-ecommerce/www/manage/email-template.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/email-template.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/email-template.tcl 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,68 @@ +# packages/dotlrn-ecommerce/www/admin/email-template.tcl + +ad_page_contract { + + add/edit an email template + + @author Deds Castillo (deds@i-manila.com.ph) + @creation-date 2005-07-20 + @arch-tag: cb528bf6-f4e5-4c87-bbe9-e987780a6709 + @cvs-id $Id: email-template.tcl,v 1.1 2008/09/02 21:54:48 daveb Exp $ +} { + {section_id ""} + {community_id ""} + {action ""} + {return_url ""} +} -properties { +} -validate { +} -errors { +} + +if {[empty_string_p $return_url]} { + set return_url [export_vars -base "one-section" {section_id}] +} + +switch -exact $action { + "submit_app" { + set type "application sent" + } + "approve_app" { + set type "on approval" + } + "on join" - + "application sent" - + "on approval" - + "waitinglist approved" - + "prereq approval" - + "needs approval" - + "request approval" - + "prereq reject" { + set type $action + } + default { + set type "" + } +} + +if {[empty_string_p $type]} { + ad_returnredirect $return_url +} + + +set title "Add/edit default email template" +set section_id [db_string get_section_id { + select section_id + from dotlrn_ecommerce_section + where community_id=:community_id +} -default ""] + +if {$community_id ne ""} { + set title "Add/edit Course default email template" +} +if {$section_id ne ""} { + set title "Add/edit Section email template" +} + +set template [dotlrn_ecommerce::email_type_pretty -type $type] +set sent_when [dotlrn_ecommerce::email_type_sent_when -type $type] +set extra_vars [list [list action $action] [list section_id $section_id]] \ No newline at end of file Index: openacs-4/packages/dotlrn-ecommerce/www/manage/email-templates.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/email-templates.adp,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/email-templates.adp 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,4 @@ + +@title@ +@context@ + \ No newline at end of file Index: openacs-4/packages/dotlrn-ecommerce/www/manage/email-templates.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/email-templates.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/email-templates.tcl 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,26 @@ +ad_page_contract { + Show email templates list +} { + {community_id:integer ""} +} +if {$community_id ne ""} { + if {[db_0or1row get_section_info "select ds.section_id, ('Section:' || ds.section_name || case when ds.section_name = dc.course_name then '' else '(' || dc.course_name || ')' end ) as course_name from dotlrn_ecommerce_section ds, dotlrn_catalogx dc where ds.community_id=:community_id and ds.course_id=dc.item_id limit 1"]} { + set community_url [export_vars -base one-section {section_id}] + } elseif {[db_0or1row get_course_info "select course_id, 'Course:' || course_name as course_name from dotlrn_catalogx, cr_items where live_revision=revision_id and community_id=:community_id"]} { + set community_url [export_vars -base course-info {course_id}] + } else { + ns_returnnotfound + } + +} else { + set course_name "" +} + +set return_url [ad_return_url] +set title "Email Templates" +if {[info exists community_url]} { + set context [list [list $community_url $course_name] $title] +} else { + set context [list $title] +} +ad_return_template Index: openacs-4/packages/dotlrn-ecommerce/www/manage/email-test.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/email-test.adp,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/email-test.adp 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,9 @@ + +@page_title@ +@context@ +

+#dotlrn-ecommerce.lt_Test_email_sent_to_user# +

+

+#dotlrn-ecommerce.Return_to_where# +

\ No newline at end of file Index: openacs-4/packages/dotlrn-ecommerce/www/manage/email-test.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/email-test.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/email-test.tcl 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,15 @@ +ad_page_contract { + + Send a test email + +} { + community_id + type + return_url +} +acs_user::get -user_id [ad_conn user_id] -array user + +set email_data [dotlrn_community::send_member_email -community_id $community_id -type $type -to_user $user(user_id)] + +set page_title "[_ dotlrn-ecommerce.Test_email_sent]" +set context [list $page_title] Index: openacs-4/packages/dotlrn-ecommerce/www/manage/index.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/index.adp,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/index.adp 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,17 @@ + +@doc_title@ +@context@ + +

@doc_title@

+ +
+ #dotlrn-catalog.search_courses# + + +
+
+#dotlrn-catalog.new_course# +
 +
+ + Index: openacs-4/packages/dotlrn-ecommerce/www/manage/index.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/index.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/index.tcl 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,85 @@ +ad_page_contract { + Course management section +} -query { + keyword:optional + {groupby "course_name"} +} + +set user_id [ad_conn user_id] +set package_id [ad_conn package_id] + +set admin_p [permission::permission_p -object_id $package_id -privilege "admin"] +if {!$admin_p} { + + set admin_section_ids [dotlrn_ecommerce::section::admin_section_ids -user_id $user_id] + if {![llength $admin_section_ids]} { + ad_return_complaint 1 "You don't have permission to administer." + ad_script_abort + } + set admin_section_id_clause "and s.section_id in ([template::util::tcl_to_sql_list $admin_section_ids])" +} else { + set admin_section_id_clause "" +} + +# show a list of courses/sections they can see + +template::list::create \ + -name courses \ + -multirow courses \ + -key section_id \ + -elements { + course_name {label "Course" link_url_col course_url hide_p 1} + section_name {label "Section" link_url_col section_url} + category_name {label "Categories"} + } \ + -groupby { label "Group By" values {course_name} } \ + -filters { keyword { where_clause "lower(course_name) like '%' || lower(:keyword) || '%'" }} + +db_multirow -extend {section_url course_url category_name} courses courses " select c.course_id, c.course_name, s.section_name, s.section_id + from cr_items i, dotlrn_catalogi c + left join ( select + * from + dotlrn_ecommerce_section des + where exists ( + select 1 from acs_object_party_privilege_map m + where m.party_id = :user_id + and m.object_id = des.community_id + and m.privilege = 'admin' + ) + ) s on + c.item_id = s.course_id + where + c.course_id = i.live_revision + and exists ( + select 1 from acs_object_party_privilege_map m + where m.party_id = :user_id + and m.object_id = c.course_id + and m.privilege = 'admin' + ) + [template::list::filter_where_clauses -name courses -and] + order by lower(c.course_name), lower(s.section_name) " { + set section_url [export_vars -base one-section {section_id}] + set course_url [export_vars -base course-info {course_id}] + +# set asm_name [db_string get_asm_name { } -default "[_ dotlrn-catalog.not_associated]"] + set item_id [dotlrn_catalog::get_item_id -revision_id $course_id] + set creation_user [dotlrn_catalog::get_creation_user -object_id $item_id] + set rel [dotlrn_catalog::has_relation -course_id $course_id] + set category_name "" + set mapped [category::get_mapped_categories $course_id] + foreach element $mapped { + append category_name "[category::get_name $element], " + } + set category_name [string range $category_name 0 [expr [string length $category_name] - 3]] + + } + +set doc_title "[_ dotlrn-ecommerce.Course_Management]" +set context [list $doc_title] + +if {![info exists keyword]} { + set keyword_value [_ dotlrn-catalog.please_type] +} else { + set keyword_value $keyword +} +ad_return_template Index: openacs-4/packages/dotlrn-ecommerce/www/manage/membership-add.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/membership-add.adp,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/membership-add.adp 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,21 @@ + + @title@ + @context;noquote@ + + +

+

Users related to @user.first_names@ @user.last_name@ (@user.email@)

+ +

+ + +

+

Select a patron for @user.first_names@ @user.last_name@ (@user.email@)

+ + +

+

Select a patron for users @group_name@

+ + +

+ \ No newline at end of file Index: openacs-4/packages/dotlrn-ecommerce/www/manage/membership-add.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/membership-add.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/membership-add.tcl 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,255 @@ +# packages/dotlrn-ecommerce/www/admin/membership-add.tcl + +ad_page_contract { + + + + @author Roel Canicula (roelmc@pldtdsl.net) + @creation-date 2005-05-19 + @arch-tag: 147cb277-9537-4e94-aac5-c2c2e8c26adf + @cvs-id $Id: membership-add.tcl,v 1.1 2008/09/02 21:54:48 daveb Exp $ +} { + user_id:integer,notnull + community_id:integer,notnull + section_id:integer,notnull + + {patron ""} + {patron_id 0} + + referer:notnull + + participant_id:optional + {cancel ""} + + user_ids:integer,multiple,optional +} -properties { +} -validate { +} -errors { +} + +if { ! [empty_string_p $cancel] } { + ad_returnredirect $referer + ad_script_abort +} + +dotlrn_ecommerce::check_user -user_id $user_id + +if { [info exists participant_id] } { + set patron_id $user_id + set user_id $participant_id +} + +set next_url [export_vars -base membership-add { section_id community_id referer patron patron_id {participant_id $user_id} }] +db_1row get_section_info "select c.course_id, s.section_name, s.product_id + from dotlrn_ecommerce_section s, dotlrn_catalogi c, cr_items ci + where s.course_id = c.item_id + and ci.live_revision=c.revision_id + and s.section_id = :section_id" + +set context [list [list [export_vars -base course-info { course_id }] $section_name] "Process Purchase"] + +set title "Select Patron" +db_1row section_name { + select section_name + from dotlrn_ecommerce_section + where section_id = :section_id +} + +acs_user::get -user_id $user_id -array user + +set confirm_url [export_vars -base membership-add { user_id {confirmed_p 1} community_id section_id referer }] + +set patron_list [linsert [db_list_of_lists patrons { + select first_names||' '||last_name||' ('||email||')', user_id + from dotlrn_users + where user_id != :user_id + and (case when :patron = '' + then true + else lower(first_names||' '||last_name||' '||email) like '%'||lower(:patron)||'%' end) + and not user_id + in (select object_id_two + from acs_rels + where object_id_one = :user_id + and rel_type = 'patron_rel') + and not user_id + in (select object_id_one + from acs_rels + where object_id_two = :user_id + and rel_type = 'patron_rel') +}] 0 {{} 0}] + +if { [llength $patron_list] == 1 } { + set form [rp_getform] + ns_set delkey $form __refreshing_p + ns_set put $form __refreshing_p 0 +} + +set validate [list] + +if { ( [empty_string_p $patron] || [llength $patron_list] == 1 ) && ! $patron_id } { + ad_form -name "patron" -export { {patron_id 0} } -form { + {patron:text,optional {label "Search Patron"} {html {onchange "if (this.value != '') { this.form.__refreshing_p.value = 1; } else { this.form.__refreshing_p.value = 0 ; }"}} + {help_text "Enter a string to search names and email addresses.
Or Create an account and return to this form"} + } + } + + lappend validate {patron + { ! [empty_string_p $patron] } + "Please enter a search string" + } + lappend validate {patron + { [llength $patron_list] > 1 } + "No users found. Please try again" + } +} elseif { $patron_id } { + acs_user::get -user_id $patron_id -array patron_user + + ad_form -name "patron" -export { patron patron_id } -form { + {patron_name:text(inform) {label "Patron"} {value "$patron_user(first_names) $patron_user(last_name) ($patron_user(email))"}} + } + + lappend validate {relationship + { ![empty_string_p $relationship] || ![empty_string_p [template::element::get_value patron relationship_new]] } + "Please select a relationship or enter a new one" + } +} else { + ad_form -name "patron" -export { patron } -form { + {patron_id:integer(select),optional {label "Patron"} {options {$patron_list}} + {help_text "Select a patron from the list. Can't find the patron?
Create an account and return to this form"} + } + } + + lappend validate {patron_id + { $patron_id } + "Please select a patron from the list" + } + + lappend validate {relationship + { ![empty_string_p $relationship] || ![empty_string_p [template::element::get_value patron relationship_new]] } + "Please select a relationship or enter a new one" + } +} + +ad_form -extend -name "patron" -export { user_id community_id section_id referer user_ids:multiple } \ + -validate $validate \ + -form { + {relationship:text(category),multiple,optional {label "Relationship"} {category_application_id $community_id} {html {size 4}} + {help_text "Please select one or enter one below if not in the list"} + {assign_single_p t} + } + {relationship_new:text,optional {label "Other Relationship"} + {help_text "This field is ignored if a relationship is selected from the list above"} + } + {proceed:text(submit) {label "Select Patron"}} + {cancel:text(submit) {label "Cancel"}} + } -on_request { + } -on_submit { + set rel_id [relation::get_id -object_id_one $user_id -object_id_two $patron_id -rel_type "patron_rel"] + + if { [empty_string_p $rel_id] } { + # Create patron relationship + set rel_id [db_exec_plsql relate_patron { + select acs_rel__new (null, + 'patron_rel', + :user_id, + :patron_id, + null, + null, + null) + }] + ns_log notice "DEBUG:: Created relationship $rel_id: $user_id - $patron_id" + } else { + ns_log notice "DEBUG:: Existing relationship $rel_id: $user_id - $patron_id" + } + + # Check if no categories were selected + if { [empty_string_p $relationship] } { + set relationship "" + + # See if user entered a new relationship and add that + if { ! [empty_string_p $relationship_new] } { + set tree_id [parameter::get -package_id [ad_conn package_id] -parameter PatronRelationshipCategoryTree -default 0] + set relationship [list [category::add -name $relationship_new -tree_id $tree_id -parent_id ""]] + } + } + + # Set relationships from categories + ns_log notice "DEBUG:: Categories $relationship rel_id $rel_id" + category::map_object -remove_old -object_id $rel_id $relationship + +# set community_url [dotlrn_community::get_community_url $community_id] +# set add_member_url [export_vars -base ${community_url}member-add-3 { user_id { rel_type dotlrn_member_rel } referer }] + +# ad_returnredirect $add_member_url + +# ad_returnredirect [export_vars -base membership-add { user_id { confirmed_p 1 } community_id section_id referer }] + + if { ! [empty_string_p [template::element get_value patron proceed]] } { + ad_returnredirect [export_vars -base "ecommerce/shopping-cart-add" { product_id { user_id $patron_id } { participant_id $user_id } }] + } else { + ad_returnredirect $referer + } + ad_script_abort + } + +if { ! [info exists user_ids] } { + +template::list::create \ + -name "patrons" \ + -multirow "patrons" \ + -no_data "No relationships listed for this user" \ + -elements { + patron { + label Patron + } + relationship { + label Relationship + display_template { + @patrons.relationship;noquote@ + } + } + actions { + label "" + display_template { + Select as Patron + } + } + } + +db_multirow -extend { relationship patron_url } patrons patrons { + select r.rel_id, u.user_id as patron_id, u.first_names||' '||u.last_name||' ('||u.email||')' as patron, 1 as direction + from acs_rels r, dotlrn_users u + where r.object_id_two = u.user_id + and r.rel_type = 'patron_rel' + and r.object_id_one = :user_id + + union + + select r.rel_id, u.user_id as patron_id, u.first_names||' '||u.last_name||' ('||u.email||')' as patron, 2 as direction + from acs_rels r, dotlrn_users u + where r.object_id_one = u.user_id + and r.rel_type = 'patron_rel' + and r.object_id_two = :user_id + and not r.object_id_one in (select object_id_two + from acs_rels r + where rel_type = 'patron_rel' + and object_id_one = :user_id) +} { + foreach category [category::get_mapped_categories $rel_id] { + lappend relationship [category::get_name $category] + } + + set relationship [join $relationship ", "] + + if { $direction == 1 } { + set relationship "« $relationship" + } else { + set relationship "$relationship »" + } + + set patron_url [export_vars -base "ecommerce/shopping-cart-add" { product_id { user_id $patron_id } { participant_id $user_id } }] +} + +} else { +set group_name "[person::name -person_id [lindex $user_ids 0]] to [person::name -person_id [lindex $user_ids [expr [llength $user_ids]-1]]]" +} \ No newline at end of file Index: openacs-4/packages/dotlrn-ecommerce/www/manage/membership-remove.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/membership-remove.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/membership-remove.tcl 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,24 @@ +# packages/dotlrn-ecommerce/www/admin/remove-membership.tcl + +ad_page_contract { + + + + @author Roel Canicula (roelmc@pldtdsl.net) + @creation-date 2005-05-19 + @arch-tag: fe075f24-ed9a-4701-8a6d-1f90676ff5b1 + @cvs-id $Id: membership-remove.tcl,v 1.1 2008/09/02 21:54:48 daveb Exp $ +} { + {user_id:integer,multiple {}} + community_id:integer,notnull + return_url +} -properties { +} -validate { +} -errors { +} + +foreach one_user_id $user_id { + catch {dotlrn_community::remove_user $community_id $one_user_id} +} + +ad_returnredirect $return_url \ No newline at end of file Index: openacs-4/packages/dotlrn-ecommerce/www/manage/one-section.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/one-section.adp,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/one-section.adp 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,75 @@ + +@title@ +@context@ + + + + +
+

#dotlrn-ecommerce.Section_Information#

+ + + +#dotlrn-ecommerce.lt_Edit_Section_Informat# + +		 +

#dotlrn-ecommerce.Quick_Links#

+#dotlrn-ecommerce.Course_Catalog#
+#dotlrn-ecommerce.lt_eCommerce_Administrat#
+#dotlrn-ecommerce.Course_Page# @course_name@
+

+ +#dotlrn-ecommerce.lt_Process_Purchase_for_# + +

#dotlrn-ecommerce.Registration#

+ +#dotlrn-ecommerce.lt_num_attendees_partici#
+#dotlrn-ecommerce.List_Registrants#
+#dotlrn-ecommerce.Related_Users#
+#dotlrn-ecommerce.lt_Waiting_List_and_Prer#
+ + + + +#dotlrn-ecommerce.lt_Application_assessmen#
+#dotlrn-ecommerce.Select_1# +#dotlrn-ecommerce.Edit_Application# + +#dotlrn-ecommerce.Preview_Application# +Results Summary + + + +

+

#dotlrn-ecommerce.Public_Pages#

+ + + + +
+

#dotlrn-ecommerce.lt_Sessions_and_Attendan#

+ + + + + +

#dotlrn-ecommerce.Expense_Tracking#

+ + + +

Email Templates

+Administer Email Templates + +

+

#dotlrn-ecommerce.Related_Items#

+#dotlrn-ecommerce.Product#
+#dotlrn-ecommerce.Community_User_Pages#
+#dotlrn-ecommerce.lt_Community_Admin_Pages# + + +
Index: openacs-4/packages/dotlrn-ecommerce/www/manage/one-section.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/one-section.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/one-section.tcl 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,89 @@ +# packages/dotlrn-ecommerce/www/admin/one-section + +ad_page_contract { + + @author Tracy Adams (teadams@alum.mit.edu) + @creation-date 2005-06-14 + @arch-tag: + @cvs-id $Id: one-section.tcl,v 1.1 2008/09/02 21:54:48 daveb Exp $ +} { + section_id:integer,notnull +} -properties { +} -validate { +} -errors { +} + +set return_url "section?section_id=$section_id" + +# course id is the revision_id +db_1row get_section { } +permission::require_permission \ + -object_id $community_id \ + -party_id [ad_conn user_id] \ + -privilege "admin" + +set package_admin_p [permission::permission_p -object_id [ad_conn package_id] -privilege "admin" -party_id [ad_conn user_id]] + +set community_url [dotlrn_community::get_community_url $community_id] +dotlrn_catalog::get_course_data -course_id $course_id +set item_id [dotlrn_catalog::get_item_id -revision_id $course_id] + +template::util::array_to_vars course_data +set course_name "$name" + +set title "$course_name: Section $section_name" + +set community_package_id [dotlrn_community::get_package_id $community_id] + +set context [list [list "course-list" "Course List"] [list "course-info?course_id=$course_id" "$course_name"] $section_name ] + +set public_pages_url "../pages/${section_id}/" +set section_folder_id [dotlrn_ecommerce::section::get_public_folder_id $section_id] +set public_pages_admin_url [export_vars -base ${community_url}/file-storage/ {{folder_id $section_folder_id}}] +set num_attendees [db_string num_attendees { }] + +set attendance_show_p [apm_package_installed_p "attendance"] +set expensetracking_show_p [apm_package_installed_p "expenses"] +set show_public_pages_p [parameter::get -parameter SupportPublicPagesP -default 0] + +# Get application assessment +set assessment_id [db_string get_assessment { + select c.assessment_id, c.auto_register_p + + from dotlrn_ecommerce_section s, + dotlrn_catalogi c, + cr_items i, + as_assessmentsi a + + where s.course_id = c.item_id + and c.item_id = i.item_id + and i.live_revision = c.course_id + and c.assessment_id = a.item_id + and s.section_id = :section_id + + limit 1 +} -default ""] + +if { ! [empty_string_p $assessment_id] } { + as::assessment::data -assessment_id $assessment_id + set assessment_package_id [parameter::get -parameter AssessmentPackage] + set assessment_package_url [site_node::get_url_from_object_id -object_id $assessment_package_id] + set assessment_select_url [export_vars -base [apm_package_url_from_key dotlrn-ecommerce]admin/course-add-edit { course_id }] + set assessment_view_url [export_vars -base ${assessment_package_url}assessment { assessment_id }] + set assessment_edit_url [export_vars -base ${assessment_package_url}asm-admin/one-a { assessment_id }] + set assessment_results_url [export_vars -base ${assessment_package_url}asm-admin/item-stats {{return_url [ad_return_url]} {catalog_section_id $section_id} {assessment_id} }] +} + +# Flush cache for this section +# Shouldn't have much effect on performance and will keep the data +# more up to date +dotlrn_ecommerce::section::flush_cache $section_id + +# setup waiting list URLs +set applications_url [ad_conn package_url]applications +set section_key [db_string get_section_key "select dca.community_key from dotlrn_communities dca, dotlrn_ecommerce_section s where s.section_id = :section_id and s.community_id=dca.community_id" -default ""] +set waiting_and_prereq_url [export_vars -base /acs-templating/list-add-filter {{list_name applications} {filter_name section_key} {filter_value $section_key} {return_url $applications_url}}] +set needs_approval_url "[export_vars -base /acs-templating/list-add-filter {{list_name applications} {filter_names "section_key application_status"} {filter_values "[list $section_key {needs approval}]"} {return_url $applications_url}}]" +set waitinglist_approved_url "[export_vars -base /acs-templating/list-add-filter {{list_name applications} {filter_names "section_key application_status"} {filter_values "[list $section_key {waitinglist approved}]"} {return_url $applications_url}}]" +set approval_url "[export_vars -base /acs-templating/list-add-filter {{list_name applications} {filter_names "section_key application_status"} {filter_values "[list $section_key {request approval}]"} {return_url $applications_url}}]" +set request_approved_url "[export_vars -base /acs-templating/list-add-filter {{list_name applications} {filter_names "section_key application_status"} {filter_values "[list $section_key {request approved}]"} {return_url $applications_url}}]" \ No newline at end of file Index: openacs-4/packages/dotlrn-ecommerce/www/manage/one-section.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/one-section.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/one-section.xql 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,19 @@ + + + + + + select live_revision as course_id, community_id, product_id, section_name from cr_items, dotlrn_ecommerce_section where section_id = :section_id and cr_items.item_id = dotlrn_ecommerce_section.course_id + + + + + + select count(*) as attendees + from dotlrn_member_rels_approved + where community_id = :community_id + and (rel_type = 'dotlrn_member_rel' or rel_type = 'dc_student_rel') + + + + \ No newline at end of file Index: openacs-4/packages/dotlrn-ecommerce/www/manage/one-user.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/one-user.adp,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/one-user.adp 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,45 @@ + + @page_title@ + @context;noquote@ + user_info.first_names + @user_id@ + + + + + + +
+ +

#acs-subsite.Basic_Information#

+ + + + 		+ +

#dotlrn-ecommerce.Order_History#

+ + +

#dotlrn-ecommerce.Applications#

+ + +
\ No newline at end of file Index: openacs-4/packages/dotlrn-ecommerce/www/manage/one-user.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/one-user.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/one-user.tcl 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,95 @@ +# /pvt/home.tcl + +ad_page_contract { + user's workspace page + @cvs-id $Id: one-user.tcl,v 1.1 2008/09/02 21:54:48 daveb Exp $ +} { + user_id:integer,notnull + {cancel ""} +} -properties { + system_name:onevalue + context:onevalue + full_name:onevalue + email:onevalue + url:onevalue + screen_name:onevalue + bio:onevalue + portrait_state:onevalue + portrait_publish_date:onevalue + portrait_title:onevalue + export_user_id:onevalue + ad_url:onevalue + member_link:onevalue + pvt_home_url:onevalue +} + +acs_user::get -array user -include_bio -user_id $user_id + +set account_status [ad_conn account_status] +set login_url [ad_get_login_url] +set subsite_url [ad_conn vhost_subsite_url] + +set page_title "[_ dotlrn-ecommerce.User_information_for] [person::name -person_id $user_id]" + +set pvt_home_url [ad_pvt_home] + +set context [list $page_title] + +set ad_url [ad_url] + +set return_url [ad_return_url] + +db_multirow -extend { order_url } orders get_orders { + select o.order_id, confirmed_date + from ec_orders o, dotlrn_ecommerce_transactions t, ec_items i, dotlrn_ecommerce_section s + where o.order_id = t.order_id + and o.order_id = i.order_id + and i.product_id = s.product_id + and user_id=:user_id + and order_state not in ('in_basket','void','expired') + group by o.order_id, confirmed_date + order by o.order_id +} { + set order_url [export_vars -base ecommerce/one { order_id }] + set confirmed_date [util_AnsiDatetoPrettyDate $confirmed_date] +} + +set sessions_with_applications 0 +db_multirow -extend { asm_url edit_asm_url } sessions sessions { + select c.community_id, c.pretty_name + from dotlrn_member_rels_full r, dotlrn_communities c + where r.community_id = c.community_id + and r.member_state = 'application sent' + and r.user_id = :user_id +} { + if { [db_0or1row assessment { + select ss.session_id, c.assessment_id + + from dotlrn_ecommerce_section s, + (select c.* + from dotlrn_catalogi c, + cr_items i + where c.course_id = i.live_revision) c, + (select a.* + from as_assessmentsi a, + cr_items i + where a.assessment_id = i.latest_revision) a, + as_sessions ss + + where s.community_id = :community_id + and s.course_id = c.item_id + and c.assessment_id = a.item_id + and a.assessment_id = ss.assessment_id + and ss.subject_id = :user_id + + order by creation_datetime desc + + limit 1 + }] } { + set asm_url [export_vars -base /assessment/session { session_id }] + set edit_asm_url [export_vars -base /assessment/assessment { assessment_id }] + incr sessions_with_applications + } +} + +set catalog_url [ad_conn package_url] \ No newline at end of file Index: openacs-4/packages/dotlrn-ecommerce/www/manage/participant-add.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/participant-add.adp,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/participant-add.adp 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,11 @@ + + @title@ + @context@ + + \ No newline at end of file Index: openacs-4/packages/dotlrn-ecommerce/www/manage/participant-add.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/participant-add.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/participant-add.tcl 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,39 @@ +ad_page_contract { + packages/dotlrn-ecommerce/www/admin/participant-add.tcl + + + @author Roel Canicula (roelmc@pldtdsl.net) + @creation-date 2005-06-01 + @arch-tag: 0ee8915f-9e76-4a92-9d43-89c2c757276d + @cvs-id $Id: participant-add.tcl,v 1.1 2008/09/02 21:54:48 daveb Exp $ +} { + user_id:integer + {return_url ""} + section_id:integer + {cancel ""} + {add_url ""} +} -properties { +} -validate { +} -errors { +} + + +if { ! [empty_string_p $cancel] } { + ns_log notice "DEBUG:: CANCEL - $cancel - $return_url" + + ad_returnredirect $return_url + ad_script_abort +} + +db_1row get_section_info "select c.course_id, c.course_name, s.section_name, s.product_id + from dotlrn_ecommerce_section s, dotlrn_catalogi c, cr_items ci + where s.course_id = c.item_id + and ci.live_revision=c.revision_id + and s.section_id = :section_id" + +set title "Participant Info for $course_name: $section_name" +set context [list [list [export_vars -base course-info { course_id }] $section_name] "Participants and Patrons"] +if { [empty_string_p $add_url] } { + set add_url [export_vars -base "[ad_conn package_url]ecommerce/shopping-cart-add" { product_id user_id }] +} +#set addpatron_url [export_vars -base "[apm_package_url_from_key dotlrn-ecommerce]admin/membership-add" { user_id section_id community_id {referer $return_url} }] Index: openacs-4/packages/dotlrn-ecommerce/www/manage/participant-create.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/participant-create.adp,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/participant-create.adp 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,6 @@ + + #dotlrn-ecommerce.lt_Create_a_Participant_# + {#dotlrn-ecommerce.lt_Create_a_Participant_#} + + \ No newline at end of file Index: openacs-4/packages/dotlrn-ecommerce/www/manage/participant-create.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/participant-create.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/participant-create.tcl 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,17 @@ +# packages/dotlrn-ecommerce/www/admin/patron-create.tcl + +ad_page_contract { + + + + @author Roel Canicula (roelmc@pldtdsl.net) + @creation-date 2005-05-28 + @arch-tag: 2671f04d-3d9b-4e86-aa34-2dbf0dea6c35 + @cvs-id $Id: participant-create.tcl,v 1.1 2008/09/02 21:54:48 daveb Exp $ +} { + next_url:trim,notnull +} -properties { +} -validate { +} -errors { +} + Index: openacs-4/packages/dotlrn-ecommerce/www/manage/permissions-user-add.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/permissions-user-add.adp,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/permissions-user-add.adp 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,5 @@ + + @page_title@ + @context@ + + Index: openacs-4/packages/dotlrn-ecommerce/www/manage/permissions-user-add.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/permissions-user-add.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/permissions-user-add.tcl 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,12 @@ +ad_page_contract { + Redirect page for adding users to the permissions list. + + @author Lars Pind (lars@collaboraid.biz) + @creation-date 2003-06-13 + @cvs-id $Id: permissions-user-add.tcl,v 1.1 2008/09/02 21:54:48 daveb Exp $ +} + +set page_title "Add User" + +set context [list [list "permissions" "Permissions"] $page_title] + Index: openacs-4/packages/dotlrn-ecommerce/www/manage/permissions.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/permissions.adp,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/permissions.adp 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,4 @@ + + @page_title@ + @context@ + \ No newline at end of file Index: openacs-4/packages/dotlrn-ecommerce/www/manage/permissions.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/permissions.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/permissions.tcl 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,8 @@ +ad_page_contract { + +} { + object_id:integer,notnull +} + +set page_title [_ acs-subsite.Permissions] +set context [list $page_title] \ No newline at end of file Index: openacs-4/packages/dotlrn-ecommerce/www/manage/predefined-session-delete.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/predefined-session-delete.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/predefined-session-delete.tcl 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,26 @@ +# packages/dotlrn-ecommerce/www/admin/predefined-session-delete.tcl + +ad_page_contract { + + + + @author Roel Canicula (roelmc@pldtdsl.net) + @creation-date 2005-05-25 + @arch-tag: d8338236-fc0a-4804-8fc8-39648e9554e6 + @cvs-id $Id: predefined-session-delete.tcl,v 1.1 2008/09/02 21:54:48 daveb Exp $ +} { + {session_id:integer,multiple {}} + return_url +} -properties { +} -validate { +} -errors { +} + +if { [llength $session_id] } { + db_dml delete_sesions [subst { + delete from dotlrn_ecommerce_predefined_sessions + where session_id in ([join $session_id ,]) + }] +} + +ad_returnredirect $return_url \ No newline at end of file Index: openacs-4/packages/dotlrn-ecommerce/www/manage/remove-admin.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/remove-admin.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/remove-admin.tcl 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,50 @@ +ad_page_contract { + Remove course administrator +} { + course_id:integer,notnull + user_id:integer,notnull,multiple + return_url +} + +permission::require_permission \ + -object_id $course_id \ + -party_id [ad_conn user_id] \ + -privilege "admin" + + + +db_1row get_course_community "select dc.community_id, dca.package_id from dotlrn_catalog dc, dotlrn_communities_all dca where course_id = (select latest_revision from cr_items where item_id = :course_id) and dc.community_id = dca.community_id" + +set user_id_list $user_id +unset user_id + +foreach user_id $user_id_list { + group::remove_member -group_id $community_id -user_id $user_id + permission::revoke \ + -object_id $course_id \ + -privilege "admin" \ + -party_id $user_id + permission::revoke \ + -object_id $community_id \ + -privilege "admin" \ + -party_id $user_id + permission::revoke \ + -party_id $user_id \ + -privilege "admin" \ + -object_id $package_id + + db_foreach get_communities "select c.community_id, c.package_id from dotlrn_ecommerce_section s, dotlrn_communities_all c where s.course_id = :course_id and s.community_id = c.community_id" { + group::remove_member -group_id $community_id -user_id $user_id + + permission::revoke \ + -party_id $user_id \ + -privilege "admin" \ + -object_id $community_id + permission::revoke \ + -party_id $user_id \ + -privilege "admin" \ + -object_id $package_id + } + +} +ad_returnredirect -message "Administrator Removed" $return_url \ No newline at end of file Index: openacs-4/packages/dotlrn-ecommerce/www/manage/section-add-edit.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/section-add-edit.adp,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/section-add-edit.adp 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,15 @@ + +@page_title@ +@context@ +add_class_instance.term + + + + + + + + Index: openacs-4/packages/dotlrn-ecommerce/www/manage/section-add-edit.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/section-add-edit.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/section-add-edit.tcl 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,30 @@ +ad_page_contract { + + @author Deds Castillo + @creation-date 2004-05-01 + @version $Id: section-add-edit.tcl,v 1.1 2008/09/02 21:54:48 daveb Exp $ +} { + course_id:notnull + {section_id:optional ""} + {return_url "" } + + {sessions:integer,multiple {}} + section_name:optional +} + +dotlrn_catalog::get_course_data -course_id $course_id +set item_id [dotlrn_catalog::get_item_id -revision_id $course_id] +permission::require_permission -object_id $course_id -party_id [ad_conn user_id] -privilege write +if { [ad_form_new_p -key section_id] } { + set page_title "Add Section" +} else { + set page_title "Edit Section" +} + +set context [list $page_title] + +if { [info exists section_name] } { + set submitted_p 1 +} else { + set submitted_p 0 +} \ No newline at end of file Index: openacs-4/packages/dotlrn-ecommerce/www/manage/section-add-sessions.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/section-add-sessions.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/section-add-sessions.tcl 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,21 @@ +ad_page_contract { + Redirects to the calendar to add sessions + @author Caroline@meekshome.com +} { + community_id + {return_url "" } +} + +#Expected usage. you have just created a dotlrn_community and now you want to add sessions. + + + +set calendar_id [dotlrn_calendar::get_group_calendar_id -community_id $community_id] + +set url [calendar_portlet_display::get_url_stub $calendar_id] + +set item_type_id [db_string item_type_id "select item_type_id from cal_item_types where type = 'Session' and calendar_id = :calendar_id"] + +set num_sessions [db_string num_sessions "select count(cal_item_id) from cal_items where on_which_calendar = :calendar_id and item_type_id = :item_type_id"] + +ns_write "$num_sessions Sessions Scheduled. Add Sessions" Index: openacs-4/packages/dotlrn-ecommerce/www/manage/section-add.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/section-add.adp,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/section-add.adp 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,6 @@ + + + + + + Index: openacs-4/packages/dotlrn-ecommerce/www/manage/section-add.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/section-add.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/section-add.tcl 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,67 @@ +ad_page_contract { + Displays information of one course + @author Tracy Adams (teadams@alum.mit.edu) +} { + course_id:notnull + section_id:optional + {return_url "" } +} + + + +ad_form -name add_section -form { + section_id:key + {return_url:text(hidden) {value $return_url}} + {course_id:text(hidden) {value $course_id}} + {section_name:text} + +} -new_data { + + db_transaction { + + set community_id [dotlrn_club::new -pretty_name $section_name] + + + db_dml add_section { + insert into dotlrn_ecommerce_section(section_id, course_id, section_name, community_id) values + (:section_id, :course_id, :section_name, :community_id) + } + + + } + + ad_returnredirect $return_url + + +} -edit_data { + + +} -new_request { + +} -edit_request { + +} + + +#create table dotlrn_ecommerce_section ( +# section_id integer primary key, +# community_id integer references dotlrn_communities_all(community_id), +# section_name varchar(2000), +# age varchar(2000), +# date_time_start timestamptz, +# date_time_end timestamptz, +# daily_p char(1) check (daily_p in ('t','f')), +# weekly_p char(1) check (weekly_p in ('t','f')), +# qualified_age_low integer, +# qualified_age_high integer, +# account_code_revenue varchar(100), +# account_code_expense varchar(100), +# max_participants integer, +# waiting_list_p char(1) check (daily_p in ('t','f')), +# notify_waiting_number integer, +# member_price_number numeric, +# non_member_price numeric +#); + + + Index: openacs-4/packages/dotlrn-ecommerce/www/manage/section-delete.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/section-delete.adp,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/section-delete.adp 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,14 @@ + +@page_title@ +@context@ + +@content;noquote@ + +

+ + + + + + Back + \ No newline at end of file Index: openacs-4/packages/dotlrn-ecommerce/www/manage/section-delete.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/section-delete.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/section-delete.tcl 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,49 @@ +ad_page_contract { + + Delete a section + + @author Hamilton Chua + @creation-date 2004-05-01 + @version $Id: section-delete.tcl,v 1.1 2008/09/02 21:54:48 daveb Exp $ +} { + section_id:notnull + {return_url "" } +} + +set page_title "Delete Section" +set context $page_title + +set attendees [dotlrn_ecommerce::section::attendees $section_id] + +# Don't allow deletion of sections with attendees +if { $attendees <= 0 } { + # create confirmation form + set content "Are you sure you want to remove this section ? " + + ad_form -name delete_section \ + -cancel_url $return_url \ + -form { + {return_url:text(hidden) { value $return_url } } + {section_id:text(hidden) { value $section_id } } + } -on_submit { + + # HAM : + # for now just delete from dotlrn_ecommerce_sections and then archive + # discuss what else needs to be done for delete + + set community_id [db_string "getcomid" "select community_id from dotlrn_ecommerce_section where section_id=:section_id"] + + # delete from section table + db_dml "delete section" "delete from dotlrn_ecommerce_section where section_id=:section_id " + + # archive community + dotlrn_community::archive -community_id $community_id + + + + } -after_submit { + ad_returnredirect $return_url + } +} else { + set content "This section currently has participants and cannot be removed." +} \ No newline at end of file Index: openacs-4/packages/dotlrn-ecommerce/www/manage/section-edit.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/section-edit.adp,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/section-edit.adp 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,5 @@ + +@page_title@ +@context@ + + \ No newline at end of file Index: openacs-4/packages/dotlrn-ecommerce/www/manage/section-edit.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/section-edit.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/section-edit.tcl 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,144 @@ +# packages/dotlrn-ecommerce/www/admin/section-edit.tcl + +ad_page_contract { + + Edit a section + + @author Dave Bauer (dave@solutiongrove.com) + @creation-date 2005-05-16 + +} -query { + section_id:integer,notnull + {return_url ""} +} -properties { + page_title + context +} + +set package_id [ad_conn package_id] +set user_id [ad_conn user_id] + +#permission::require_permission \ + -party_id $user_id \ + -object_id $section_id \ + -privilege "write" + +ad_form -name section-edit \ + -export { return_url } \ + -form { + section_id:key + {product_id:text(hidden)} + {course_id:text(hidden)} + {community_id:text(hidden)} + {section_name:text {label "Section Name"}} + {price:text(text) {label "Regular Price"} {html {size 6}} {section "Product Info"}} + {terms:integer(category),multiple,optional + {label "Terms"} + {html {size 4}} + } +} +# ecommerce stuff + +set exclude_list [list "'classid'"] + +db_foreach custom_fields_select " + select field_identifier, + field_name, + default_value, + column_type + from ec_custom_product_fields + where active_p='t' + and field_identifier not in ([join $exclude_list ", "]) + order by creation_date" { + # date + if {[string equal $column_type date] || [string equal $column_type timestamp]} { + if {[string equal $field_identifier enddate]} { + ad_form -extend -name section-edit -form [list \ + [list "${field_identifier}:date,optional" {label $field_name} {value $default_value} {help_text "Not required for Distance Learning Courses"}]] + } else { + ad_form -extend -name section-edit -form [list \ + [list "${field_identifier}:date,optional" {label $field_name} {value $default_value}] \ + ] + } + } elseif {[string equal $column_type integer] || [string equal $column_type number]} { + ad_form -extend -name section-edit -form [list \ + [list "${field_identifier}:text(text),optional" {label $field_name} {value $default_value} {html {size 5}}] \ + ] + } elseif {[string equal $column_type "varchar(200)"]} { + ad_form -extend -name section-edit -form [list \ + [list "${field_identifier}:text(text),optional" {label $field_name} {value $default_value} {html {size 50 maxlength 200}}] \ + ] + } elseif {[string equal $column_type "varchar(4000)"]} { + ad_form -extend -name section-edit -form [list \ + [list "${field_identifier}:text(textarea),optional" {label $field_name} {value $default_value} {html {rows 4 cols 60}}] \ + ] + } else { + ad_form -extend -name section-edit -form [list \ + [list "${field_identifier}:text(radio),optional" {label $field_name} {value $default_value} {options {{t t} {f f}}}] \ + ] + } +} + + +ad_form -extend -name section-edit \ + -edit_request { + # fill in the form + db_1row get_section "select * from dotlrn_ecommerce_section where section_id=:section_id" + # get price from product + db_1row get_product "select price from ec_products where product_id=:product_id" + # get custom fields + db_1row get_custom_values "select * from ec_custom_product_field_values where product_id=:product_id" + # get categoriees + element set_properties section-edit terms -category_object_id $community_id + } -edit_data { + db_transaction { + set bind_set [ns_set create] + ns_set put $bind_set product_id $product_id + ns_set put $bind_set user_id $user_id + ns_set put $bind_set peeraddr [ad_conn peeraddr] + + db_foreach custom_columns_select { + select field_identifier, column_type + from ec_custom_product_fields + where active_p='t' + } { + if {[info exists $field_identifier] } { + lappend custom_columns "${field_identifier}=:${field_identifier}" + if {[string equal $column_type date] || [string equal $column_type timestamp]} { + set one_date [template::util::date::get_property linear_date_no_time [subst $$field_identifier]] + ns_set put $bind_set $field_identifier $one_date + } else { + ns_set put $bind_set $field_identifier [subst $$field_identifier] + } + } + } + db_dml custom_fields_update " + update ec_custom_product_field_values set [join $custom_columns ","] where product_id=:product_id + " -bind $bind_set + + + db_dml update_product { + update ec_products set price=:price where product_id=:product_id + } + db_dml update_section { + update dotlrn_ecommerce_section set section_name=:section_name where section_id=:section_id + } + # attach categories to the community_id or product_id, we have plenty of objects to work with! + # it works, but I can't get the terms values to display as selected. + # where is the options for the terms form element set!? DAVEB + if { ![string equal $terms "-1"] } { + category::map_object -remove_old -object_id $community_id $terms + } + + } + } -after_submit { + if {[string equal "" $return_url]} { + set return_url [export_vars -base "course-info" {course_id}] + } + ad_returnredirect $return_url + } + +set page_title "Edit Section" +set context [list $page_title] + +ad_return_template \ No newline at end of file Index: openacs-4/packages/dotlrn-ecommerce/www/manage/session.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/session.adp,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/session.adp 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,5 @@ + +@page_title +@context@ + + \ No newline at end of file Index: openacs-4/packages/dotlrn-ecommerce/www/manage/session.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/session.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/session.tcl 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,30 @@ +ad_page_contract { + We want to see the assessment session even though we don't have admin + over the assessment package. + If we have admin over the community, we can see the application + assessment session. + session_id:integer,notnull +} { + +} + +# get mapping to make sure this is an applicaiton for dotlrn-ecommerce +# section +set form [ns_getform] +set session_id [ns_set iget $form session_id] +set community_id [db_string get_community_id "select r.object_id_one from acs_rels r, dotlrn_ecommerce_application_assessment_map m where m.rel_id = r.rel_id and m.session_id = :session_id" -default ""] +if {$community_id eq ""} { + ad_return_complaint 1 "Invalid session" + ad_script_abort +} + +permission::require_permission \ + -object_id $community_id \ + -party_id [ad_conn user_id] \ + -privilege "admin" + +# once we know we have permission just include the session results page +# from assessment. + +set page_title "Application" +set context [list $page_title] Index: openacs-4/packages/dotlrn-ecommerce/www/manage/toggle-display.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/toggle-display.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/toggle-display.tcl 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,10 @@ +ad_page_contract { + Toggle display_section_p +} { + section_id:integer,notnull + {return_url ".."} +} + +db_dml toggle_display "" + +ad_returnredirect $return_url Index: openacs-4/packages/dotlrn-ecommerce/www/manage/toggle-display.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/toggle-display.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/toggle-display.xql 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,9 @@ + + + + + + update ec_custom_product_field_values set display_section_p = not display_section_p where product_id = (select product_id from dotlrn_ecommerce_section where section_id=:section_id) + + + Index: openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/address-add-2.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/address-add-2.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/address-add-2.tcl 2 Sep 2008 21:54:48 -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"] + +

Confirm Shipping Address

+ + [ad_context_bar [list "../" "Ecommerce([ec_system_name])"] [list "index" "Orders"] [list "one?[export_url_vars order_id]" "One Order"] "Confirm Shipping Address"] + +
+

Please confirm new address:

+
" + +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]] +
+
+ [export_entire_form] +
+ +
+
+ [ad_admin_footer]" Index: openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/address-add-3.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/address-add-3.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/address-add-3.tcl 2 Sep 2008 21:54:48 -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/manage/ecommerce/address-add-3.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/address-add-3.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/address-add-3.xql 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,37 @@ + + + + + + select user_id + from ec_orders + where order_id = :order_id + + + + + + 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) + + + + + + update ec_orders + set shipping_address = :address_id + where order_id = :order_id + + + + + + update ec_creditcards + set billing_address = :address_id + where creditcard_id = :creditcard_id + + + + Index: openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/address-add.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/address-add.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/address-add.tcl 2 Sep 2008 21:54:48 -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"] + +

New Shipping Address

+ + [ad_context_bar [list "../" "Ecommerce([ec_system_name])"] [list "index" "Orders"] [list "one?[export_url_vars order_id]" "One Order"] "New Shipping Address"] + +
+

Please enter a new domestic address or a new international address. All future shipments for this order will go to this address.

+ +

New domestic address:

" + +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 " +
+
+ [export_form_vars order_id creditcard_id] + + + + + + + + + + + + + + + + + + + + + + + + + +
Name
Address
2nd line (optional)
City  State [state_widget]
Zip
Phone day     + evening
+
+ +
+
+
+ +

New international address:

+ +
+
+ [export_form_vars order_id creditcard_id] + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + +
Name
Address
2nd line (optional)
City
Province or Region
Postal Code
Country[ec_country_widget]
Phone day     + evening
+
+ +
+
+
+ + [ad_admin_footer]" + + Index: openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/address-add.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/address-add.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/address-add.xql 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,11 @@ + + + + + + select first_names || ' ' || last_name from cc_users, ec_orders where ec_orders.user_id=cc_users.user_id and order_id=:order_id + + + + + Index: openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/comments-edit.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/comments-edit.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/comments-edit.tcl 2 Sep 2008 21:54:48 -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 2008/09/02 21:54:48 daveb 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/manage/ecommerce/comments-edit.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/comments-edit.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/comments-edit.xql 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,11 @@ + + + + + + update ec_orders set cs_comments=:cs_comments where order_id=:order_id + + + + + Index: openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/comments.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/comments.adp,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/comments.adp 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,5 @@ + + Comments + + + @doc_body;noquote@ Index: openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/comments.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/comments.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/comments.tcl 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,35 @@ +# 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 2008/09/02 21:54:48 daveb Exp $ + @author ported by Jerry Asher (jerry@theashergroup.com) +} { + order_id:integer,notnull +} + +ad_require_permission [ad_conn package_id] admin + +set doc_body "" + +append doc_body "
+[export_form_vars order_id] + +Please add or edit comments below: + +
+ +
+ +
+ +

+

+ +
+ +
" + +set context [list [list index Orders] [list one?order_id=$order_id "One Order"] "Comments"] \ No newline at end of file Index: openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/comments.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/comments.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/comments.xql 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,11 @@ + + + + + + select cs_comments from ec_orders where order_id=:order_id + + + + + Index: openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/creditcard-add-2.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/creditcard-add-2.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/creditcard-add-2.tcl 2 Sep 2008 21:54:48 -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 "
  • Your credit card number contains invalid characters.
    • " +} + +if { ![info exists creditcard_type] || [empty_string_p $creditcard_type] } { + incr exception_count + append exception_text "
  • You forgot to enter your credit card type.
    • " +} + +# 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 "
  • Please enter your full credit card expiration date (month and year).
    • " +} + +if { $exception_count > 0 } { + ad_return_complaint $exception_count $exception_text + return +} + +doc_body_append " + [ad_admin_header "Confirm Credit Card"] + +

    Confirm Credit Card

    + + [ad_context_bar [list "../" "Ecommerce([ec_system_name])"] [list "index" "Orders"] [list "one?[export_url_vars order_id]" "One Order"] "Confirm Credit Card"] + +
    + +

    Please confirm that this is correct:

    + +
    + 
    +[ec_pretty_creditcard_type $creditcard_type]
+$creditcard_number
+exp: $creditcard_expire_1/$creditcard_expire_2
+
    +
    + +
    + [export_entire_form] +
    + +
    +
    + [ad_admin_footer]" Index: openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/creditcard-add-3.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/creditcard-add-3.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/creditcard-add-3.tcl 2 Sep 2008 21:54:48 -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/manage/ecommerce/creditcard-add-3.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/creditcard-add-3.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/creditcard-add-3.xql 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,29 @@ + + + + + + select user_id + from ec_orders + where order_id = :order_id + + + + + + 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) + + + + + + update ec_orders + set creditcard_id = :creditcard_id + where order_id = :order_id + + + + Index: openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/creditcard-add.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/creditcard-add.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/creditcard-add.tcl 2 Sep 2008 21:54:48 -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"] + +

    New Credit Card

    + + [ad_context_bar [list "../" "Ecommerce([ec_system_name])"] [list "index" "Orders"] [list "one?[export_url_vars order_id]" "One Order"] "New Credit Card"] + +
    +

    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).

    " + +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 " + + + + + + + +
    + [ec_display_as_html [ec_pretty_mailing_address_from_ec_addresses $billing_address]] + " +doc_body_append " + + +
    + + [export_form_vars order_id] + + + + + + + + + + + + + + +
    Credit card number:
    Type:[ec_creditcard_widget] +
    + +
    +
    Expires:[ec_creditcard_expire_1_widget] [ec_creditcard_expire_2_widget]
    +
    +
    + [ad_admin_footer]" Index: openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/creditcard-add.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/creditcard-add.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/creditcard-add.xql 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,15 @@ + + + + + + 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 + + + + Index: openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/financial-transactions.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/financial-transactions.adp,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/financial-transactions.adp 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,7 @@ + + #dotlrn-ecommerce.lt_Financial_Transaction# + @context@ + + +

    + #dotlrn-ecommerce.lt_Order_Details_for_ord# \ No newline at end of file Index: openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/financial-transactions.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/financial-transactions.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/financial-transactions.tcl 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,18 @@ +# packages/dotlrn-ecommerce/www/admin/ecommerce/financial-transactions.tcl + +ad_page_contract { + + Financial transactions page + + @author Roel Canicula (roelmc@pldtdsl.net) + @creation-date 2005-08-17 + @arch-tag: e3a4648c-6ae1-4a93-ac7f-f1fd97f04d2f + @cvs-id $Id: financial-transactions.tcl,v 1.1 2008/09/02 21:54:48 daveb Exp $ +} { + order_id:integer,notnull +} -properties { +} -validate { +} -errors { +} + +set context [list [list index Orders] [list one?order_id=$order_id "One Order"] "Financial Transactions"] \ No newline at end of file Index: openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/index.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/index.adp,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/index.adp 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,11 @@ + + #dotlrn-ecommerce.Order_Summary# + @context@ + + +

    + +

    + + #dotlrn-ecommerce.Display_All_Orders# + Index: openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/index.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/index.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/index.tcl 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,286 @@ +# 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 2008/09/02 21:54:48 daveb Exp $ +} { + section_id:integer,optional + user_id:integer,optional + start:optional + type:optional + payment_method:optional + orderby:optional +} -properties { +} -validate { +} -errors { +} + +if { [empty_string_p [set available_payment_methods [parameter::get -parameter PaymentMethods]]] } { + lappend available_payment_methods cc +} + +set method_filters [list] + +foreach available_payment_method [split $available_payment_methods] { + set _payment_method [split $available_payment_method :] + if { [llength $_payment_method] == 2 } { + set _payment_method [lindex $_payment_method 0] + } + + lappend method_filters [list [ad_decode $_payment_method \ + cc "[_ dotlrn-ecommerce.Credit_Card]" \ + check "[_ dotlrn-ecommerce.Check]" \ + internal_account "[_ dotlrn-ecommerce.Internal_Account]" \ + cash "[_ dotlrn-ecommerce.Cash]" \ + invoice "[_ dotlrn-ecommerce.Invoice]" \ + scholarship "[_ dotlrn-ecommerce.Scholarship]" \ + lockbox "[_ dotlrn-ecommerce.Lock_Box]" \ + "[_ dotlrn-ecommerce.Credit_Card]" + ] \ + $_payment_method] +} + +template::list::create \ + -name "orders" \ + -multirow "orders" \ + -page_flush_p 1 \ + -no_data "[_ dotlrn-ecommerce.No_orders]" \ + -elements { + order_id { + label "[_ dotlrn-ecommerce.Order_ID]" + link_url_col order_url + html { align center } + } + confirmed_date { + label "[_ dotlrn-ecommerce.Date]" + } + _section_name { + label "[_ dotlrn-ecommerce.Item]" + link_url_col section_url + } + purchaser { + label "[_ dotlrn-ecommerce.Purchaser]" + link_url_col person_url + } + participant_name { + label "[_ dotlrn-ecommerce.Participant]" + display_template { + + @orders.participant_name@ + + + @orders.participant_name@ + + } + } + method { + label "[_ dotlrn-ecommerce.Payment_Method]" + display_template { + + @orders.method@, #dotlrn-ecommerce.Scholarship# + + + @orders.method@ + + } + } + total_price { + label "[_ dotlrn-ecommerce.Total_Amount]" + html { align right } + display_template { + @orders.pretty_actual_total;noquote@ + } + aggregate sum + aggregate_label "[_ dotlrn-ecommerce.Total_1]:" + } + refund_price { + label "[_ dotlrn-ecommerce.Total_Refunds]" + html { align right } + display_template { + @orders.pretty_refund;noquote@ + } + aggregate sum + aggregate_label "[_ dotlrn-ecommerce.Total_1]:" + } + price_to_display { + label "[_ dotlrn-ecommerce.lt_Total_Amount_Received]" + html { align right } + display_template { + @orders.pretty_total;noquote@ + } + aggregate sum + aggregate_label "[_ dotlrn-ecommerce.Total_1]:" + } + balance { + label "[_ dotlrn-ecommerce.Fully_Paid]" + html { align right } + display_template { + + [_ dotlrn-ecommerce.No] + + + [_ dotlrn-ecommerce.Yes] + + } + } + checked_out_by { + label "[_ dotlrn-ecommerce.Registered_By_Admin]" + display_template { + + [_ dotlrn-ecommerce.No] + + + [_ dotlrn-ecommerce.Yes] + + } + html { align center } + } + refund { + display_template { + + Refund + + } + } + transactions { + display_template { + Transactions + } + } + } -filters { + section_id { + where_clause { _section_id = :section_id } + } + user_id { + where_clause { purchasing_user_id = :user_id } + } + start { + label "[_ dotlrn-ecommerce.In_the_last]" + values { + {"24 hours" "24 hours"} + {"7 days" "7 days"} + {"Month" "1 month"} + } + where_clause { authorized_date >= current_timestamp - :start::interval } + } + type { + label "[_ dotlrn-ecommerce.Orders_with]" + values { + {"[_ dotlrn-ecommerce.Outstanding_blance]" balance} + } + where_clause { + (case when method = 'invoice' then + ec_total_price(order_id) - ec_order_gift_cert_amount(order_id) - + (select coalesce(sum(amount), 0) + from dotlrn_ecommerce_transaction_invoice_payments + where order_id = order_id) + else 0 end) > 0 + } + } + payment_method { + label "[_ dotlrn-ecommerce.Payment_method]" + values { $method_filters } + where_clause { + (method = :payment_method or + (:payment_method = 'scholarship' and + coalesce((select true + 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))), false))) + } + } + } -orderby { + order_id { + label "[_ dotlrn-ecommerce.Order_ID]" + orderby order_id + } + confirmed_date { + label "[_ dotlrn-ecommerce.Date]" + orderby confirmed_date_date_column + } + _section_name { + label "[_ dotlrn-ecommerce.Section_Name]" + orderby _section_name + } + purchaser { + label "[_ dotlrn-ecommerce.Purchaser]" + orderby "lower(first_names||' '||last_name)" + } + participant_name { + label "[_ dotlrn-ecommerce.Participant]" + orderby "lower(case when object_type = 'group' then acs_group__name(participant_id) else person__name(participant_id) end)" + } + method { + label "[_ dotlrn-ecommerce.Payment_Method]" + orderby method + } + total_price { + label "[_ dotlrn-ecommerce.Total_Amount]" + orderby total_price + } + refund_price { + label "[_ dotlrn-ecommerce.Total_Refunds]" + orderby refund_price + } + price_to_display { + label "[_ dotlrn-ecommerce.lt_Total_Amount_Received]" + orderby price_to_display + } + balance { + label "[_ dotlrn-ecommerce.Balance]" + orderby balance + } + checked_out_by { + label "[_ dotlrn-ecommerce.Regsitered_By_Admin]" + orderby checked_out_by_admin_p + } + } + +db_multirow -extend { order_url section_url pretty_total pretty_balance person_url pretty_refund pretty_actual_total refund_url participant_url participant_type } orders orders [subst { + + select * from dlec_view_orders + where 1=1 + [template::list::filter_where_clauses -and -name orders] + +-- 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, section_name, s.section_id, s.course_id, o.authorized_date, balance, refund_price, refund_date, purchaser + + [template::list::orderby_clause -name orders -orderby] +}] { + set order_url [export_vars -base one { order_id }] + set pretty_total [ec_pretty_price $price_to_display] + set pretty_balance [ec_pretty_price $balance] + set method [ad_decode $method \ + cc "[_ dotlrn-ecommerce.Credit_Card]" \ + check "[_ dotlrn-ecommerce.Check]" \ + internal_account "[_ dotlrn-ecommerce.Internal_Account]" \ + cash "[_ dotlrn-ecommerce.Cash]" \ + invoice "[_ dotlrn-ecommerce.Invoice]" \ + scholarship "[_ dotlrn-ecommerce.Scholarship]" \ + lockbox "[_ dotlrn-ecommerce.Lock_Box]" \ + "[_ dotlrn-ecommerce.Credit_Card]" + ] + set order_state [string totitle $order_state] + set section_url [export_vars -base ../one-section { {section_id $_section_id} }] + set person_url [export_vars -base ../one-user { {user_id $purchasing_user_id} }] + set participant_url [export_vars -base ../one-user { {user_id $participant_id} }] + set pretty_refund [ec_pretty_price $refund_price] + set pretty_actual_total [ec_pretty_price $total_price] + + set refund_url [export_vars -base "items-return-2" { order_id item_id }] + + set participant_type [acs_object_type $participant_id] +} + +if { [info exists section_id] } { + set context [list [list [export_vars -base ../one-section { section_id }] [db_string section_name { select section_name from dotlrn_ecommerce_section where section_id = :section_id }]] "[_ dotlrn-ecommerce.Order_Summary]"] +} else { + set context [list "[_ dotlrn-ecommerce.Order_Summary]"] +} \ No newline at end of file Index: openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/invoice-payment.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/invoice-payment.adp,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/invoice-payment.adp 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,5 @@ + + Invoice Payment + @context@ + + \ No newline at end of file Index: openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/invoice-payment.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/invoice-payment.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/invoice-payment.tcl 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,355 @@ +# 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 2008/09/02 21:54:48 daveb 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 "[_ dotlrn-ecommerce.lt_Internal_account_numb]" internal_account] + lappend validate {internal_account + { [exists_and_not_null internal_account] || [template::element::get_value checkout method] != "internal_account" } + "[_ dotlrn-ecommerce.lt_Please_enter_an_inter]" + } + } + check { + lappend method_options [list "[_ dotlrn-ecommerce.Check]" check] + } + cc { + lappend method_options [list "[_ dotlrn-ecommerce.Credit_Card]" cc] + lappend validate {creditcard_number + { [template::element::get_value checkout method] != "cc" || [exists_and_not_null creditcard_number] } + "[_ dotlrn-ecommerce.lt_Please_enter_a_credit]" + } + lappend validate {creditcard_type + { [template::element::get_value checkout method] != "cc" || [exists_and_not_null creditcard_type] } + "[_ dotlrn-ecommerce.lt_Please_select_a_credi]" + } + 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]) } + "[_ dotlrn-ecommerce.lt_A_full_credit_card_ex]" + } + } + cash { + lappend method_options [list "[_ dotlrn-ecommerce.Cash]" cash] + } + lockbox { + lappend method_options [list "[_ dotlrn-ecommerce.Lock_Box]" lockbox] + } + } + incr method_count +} +set payment_methods $new_payment_methods + +# Build the form +ad_form -name checkout -export { order_id } -form { + {-section "[_ dotlrn-ecommerce.Amount_to_be_Paid]"} + {amount:float {label "[_ dotlrn-ecommerce.Amount_to_be_Paid]"} {html {size 10}}} +} + +if { $method_count > 1 } { + ad_form -extend -name checkout -form { + {-section "[_ dotlrn-ecommerce.Payment_Information]"} + {method:text(radio) {label "[_ dotlrn-ecommerce.lt_Select_a_payment_meth]"} {options {$method_options}}} + } + + if { [exists_and_equal internal_account_p 1] } { + ad_form -extend -name checkout -form { + {internal_account:text,optional {label "[_ dotlrn-ecommerce.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 "[_ dotlrn-ecommerce.lt_Credit_card_informati]"} + {creditcard_number:text {label "[_ dotlrn-ecommerce.Credit_card_number]"}} + {creditcard_type:text(select) {label Type} {options {{"[_ dotlrn-ecommerce.Please_select_one]" ""} {VISA v} {MasterCard m} {"American Express" a}}}} + {creditcard_expires:text(inform) {label "[_ dotlrn-ecommerce.Expires] *"} {value $ec_expires_widget}} + } + } else { + ad_form -extend -name checkout -form { + {-section "[_ dotlrn-ecommerce.lt_Credit_card_informati]"} + {creditcard_number:text,optional {label "[_ dotlrn-ecommerce.Credit_card_number]"}} + {creditcard_type:text(select),optional {label Type} {options {{"[_ dotlrn-ecommerce.Please_select_one]" ""} {VISA v} {MasterCard m} {"American Express" a}}}} + {creditcard_expires:text(inform),optional {label "[_ dotlrn-ecommerce.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) } + "[_ dotlrn-ecommerce.lt_You_may_only_enter_up] [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" + } + + 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 financial-transactions { order_id }] + ad_script_abort +} + +# doc_body_append " +# [ad_admin_header "Invoice Payment"] + +#

    Invoice Payment

    + +# [ad_context_bar [list "../" "Ecommerce([ec_system_name])"] [list "index" "Orders"] "One Order"] + +#
    + +#

    Invoice Payment

    + +# [eval [template::adp_compile -string [subst { +# +# }]]] +# [ad_admin_footer]" + +set context [list [list index Orders] [list one?order_id=$order_id "One Order"] "Invoice Payment"] \ No newline at end of file Index: openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-add-2.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-add-2.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-add-2.tcl 2 Sep 2008 21:54:48 -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."] + +

    Add Items, Cont.

    + + [ad_context_bar [list "../" "Ecommerce([ec_system_name])"] [list "index" "Orders"] [list "one?order_id=$order_id" "One Order"] "Add Items, Cont."] + +
    " + +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 " +

    Here are the product(s) that match your search.

    +

    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.

    +
      " + } + incr product_counter + doc_body_append " +

    • $product_name

      + [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 " +

      No matching products were found.

      " +} else { + doc_body_append "
    " +} + +doc_body_append " + [ad_admin_footer]" Index: openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-add-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-add-2.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-add-2.xql 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,11 @@ + + + + + + select product_id, product_name from ec_products where $additional_query_part + + + + + Index: openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-add-3.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-add-3.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-add-3.tcl 2 Sep 2008 21:54:48 -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."] + +

    Add Items, Cont.

    + + [ad_context_bar [list "../" "Ecommerce([ec_system_name])"] [list "index" "Orders"] [list "one?order_id=$order_id" "One Order"] "Add Items, Cont."] + +
    " + +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 " +
    + [export_form_vars order_id product_id color_choice size_choice style_choice item_id] +
    +

    This is the price that this user would normally receive for this product. Make modifications as needed:

    +
    + + ([ad_parameter -package_id [ec_id] Currency ecommerce]) +
    +
    +
    + +
    +
    + [ad_admin_footer]" Index: openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-add-3.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-add-3.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-add-3.xql 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,11 @@ + + + + + + select user_id from ec_orders where order_id=:order_id + + + + + Index: openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-add-4-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-add-4-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-add-4-oracle.xql 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,17 @@ + + + + oracle8.1.6 + + + + 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) + + + + + + Index: openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-add-4-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-add-4-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-add-4-postgresql.xql 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,17 @@ + + + + postgresql7.1 + + + + 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) + + + + + + Index: openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-add-4.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-add-4.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-add-4.tcl 2 Sep 2008 21:54:48 -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." " +

    This order does not have an associated credit card, so new items cannot be added.

    +

    Please create a new order instead.

    " + 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/manage/ecommerce/items-add-4.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-add-4.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-add-4.xql 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,32 @@ + + + + + + select count(*) from ec_items where item_id=:item_id + + + + + + + select creditcard_id from ec_orders where order_id=:order_id + + + + + + + select shipping_method from ec_orders where order_id=:order_id + + + + + + + update ec_items set shipping_charged=:shipping_price where item_id=:item_id + + + + + Index: openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-add.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-add.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-add.tcl 2 Sep 2008 21:54:48 -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"] + +

    Add Items

    + + [ad_context_bar [list "../" "Ecommerce([ec_system_name])"] [list "index" "Orders"] [list "one?order_id=$order_id" "One Order"] "Add Items"] + +
    +
    +

    Search for a product to add:

    +
    + [export_form_vars order_id] +
      +
    • By Name:
      • +
    +
    +
    + [export_form_vars order_id] +
      +
    • By SKU:
      • +
    +
    +
    + [ad_admin_footer]" + Index: openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-return-2-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-return-2-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-return-2-oracle.xql 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,34 @@ + + + + oracle8.1.6 + + + + select nvl(shipping_charged,0) - nvl(shipping_refunded,0) + from ec_orders + where order_id=:order_id + + + + + + 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') + + + + + + 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') + + + + Index: openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-return-2-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-return-2-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-return-2-postgresql.xql 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,34 @@ + + + + postgresql7.1 + + + + select coalesce(shipping_charged,0) - coalesce(shipping_refunded,0) + from ec_orders + where order_id=:order_id + + + + + + 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') + + + + + + 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') + + + + Index: openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-return-2.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-return-2.adp,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-return-2.adp 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,5 @@ + + Refund + @context@ + + @doc_body;noquote@ \ No newline at end of file Index: openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-return-2.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-return-2.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-return-2.tcl 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,166 @@ +ad_page_contract { + + @cvs-id $Id: items-return-2.tcl,v 1.1 2008/09/02 21:54:48 daveb 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,optional + order_id:notnull,naturalnum + {reason_for_return ""} + all_items_p:optional + item_id:optional,multiple + received_back_date:date,array,optional + received_back_time:time,array,optional +} + +ad_require_permission [ad_conn package_id] admin + +if { ! [info exists refund_id] } { + set refund_id [db_nextval refund_id_sequence] +} + +if { [array exists received_back_date] && [array exists received_back_time] } { + 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" + } +} else { + set received_back_datetime [clock format [clock seconds] -format "%Y-%m-%d %H:%M:%S"] +} + +# 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 " +
  • This refund has already been inserted into the database. Are you using an old form? Return to the order." + 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 "
  • 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 "
  • 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 +} + +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] +} + +# See if a credit card was used for this purchase +set method [db_string method { + select method + from dotlrn_ecommerce_transactions + where order_id = :order_id +} -default cc] + +if { $method == "invoice" } { + if { [db_0or1row cc_transaction_in_invoice { + select 1 + where exists (select * + from dotlrn_ecommerce_transaction_invoice_payments + where order_id = :order_id + and method = 'cc') + }] } { + set method cc + } +} + +set items_to_print "" +db_foreach get_return_item_list $sql { + + if { [info exists all_items_p] } { + lappend item_id_list $item_id + } + + if { $method == "cc" } { + append items_to_print " + + $product_name + (by credit card out of [ec_pretty_price $price_charged]); (manually out of [ec_pretty_price $price_charged]) + + " + } else { + append items_to_print " + + $product_name + (manually out of [ec_pretty_price $price_charged]) + + " + } +} + +append doc_body " +
    + [export_form_vars refund_id order_id item_id_list received_back_datetime reason_for_return] +
    + + + + + $items_to_print +
    ItemPrice to Refund
    " + +# 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 " + +
    + +
    " + +#doc_return 200 text/html $doc_body +set context [list [list index Orders] [list one?order_id=$order_id "One Order"] "Refund"] Index: openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-return-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-return-2.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-return-2.xql 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,12 @@ + + + + + + select count(*) + from ec_refunds + where refund_id=:refund_id + + + + Index: openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-return-3-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-return-3-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-return-3-oracle.xql 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,52 @@ + + + + oracle8.1.6 + + + + 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 ", "]) + + + + + + select nvl(ec_tax(:tax_price_to_refund, 0, :order_id),0) + from dual + + + + + + select nvl(ec_tax(0, $shipping_to_refund($item_id), :order_id),0) + from dual + + + + + + 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 + + + + + + select nvl(ec_tax(0, :base_shipping, :order_id),0) + from dual + + + + + + select nvl(ec_cash_amount_to_refund(:total_amount_to_refund, :order_id),0) + from dual + + + + Index: openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-return-3-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-return-3-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-return-3-postgresql.xql 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,48 @@ + + + + postgresql7.1 + + + + 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 ", "]) + + + + + + select coalesce(ec_tax(:tax_price_to_refund, 0, :order_id),0) + + + + + + select coalesce(ec_tax(0, $shipping_to_refund($item_id), :order_id),0) + + + + + + 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 + + + + + + select coalesce(ec_tax(0, :base_shipping, :order_id),0) + + + + + + select coalesce(ec_cash_amount_to_refund((:total_amount_to_refund::float + :total_amount_to_refund_manually::float)::numeric, :order_id::integer),0) + + + + Index: openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-return-3.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-return-3.adp,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-return-3.adp 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,5 @@ + + Refund + @context@ + + @doc_body;noquote@ \ No newline at end of file Index: openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-return-3.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-return-3.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-return-3.tcl 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,302 @@ +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,optional + price_to_refund_manually: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 " +
  • This refund has already been inserted into the database.Are you using an old form? Return to the order." + 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_price_to_refund_manually 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 { ! [array exists price_to_refund] } { + foreach _item [array names price_to_refund_manually] { + set price_to_refund($_item) 0 + } + } + + if { [empty_string_p price_to_refund($item_id)] || [empty_string_p price_to_refund_manually($item_id)] } { + incr exception_count + append exception_text "
  • Please enter a price to refund for $product_name." + } elseif {[regexp {[^0-9\.]} $price_to_refund($item_id)] || [regexp {[^0-9\.]} $price_to_refund_manually($item_id)]} { + incr exception_count + append exception_text "
  • Please enter a purely numeric price to refund for $product_name (no letters or special characters)." + } elseif { ($price_to_refund($item_id) + $price_to_refund_manually($item_id)) > $price_charged } { + incr exception_count + append exception_text "
  • 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)] + set total_price_to_refund_manually [expr $total_price_to_refund_manually + $price_to_refund_manually($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 "
  • 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 "
  • 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 "
  • 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 "
  • Please enter a base shipping amount to refund." +} elseif {[regexp {[^0-9\.]} $base_shipping_to_refund]} { + incr exception_count + append exception_text "
  • 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 "
  • 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] +set total_amount_to_refund_manually [expr $total_price_to_refund_manually + $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 how much to refund to the credit card and how much to +# refund manually +if { $total_amount_to_refund >= $cash_amount_to_refund } { + # Requested amount to refund to credit card is greater than what + # is available for refund (the rest probably paid via gift + # certs/scholarship), give priority to credit card refunds than + # manual refunds + set cash_amount_to_refund_cc $cash_amount_to_refund + set cash_amount_to_refund_manually 0 +} else { + # Give priority to credit card refunds, the rest refund manually + set cash_amount_to_refund_cc $total_amount_to_refund + set cash_amount_to_refund_manually [expr $cash_amount_to_refund - $total_amount_to_refund] +} + +# Calculate gift certificate amount and tax to refund + +set certificate_amount_to_reinstate [expr ($total_amount_to_refund + $total_amount_to_refund_manually) - $cash_amount_to_refund] +if { $certificate_amount_to_reinstate < 0.01 } { + + # 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 "" +} + +set method [db_string method { + select method + from dotlrn_ecommerce_transactions + where order_id = :order_id +} -default cc] + +if { $method == "invoice" } { + if { [db_0or1row cc_transaction_in_invoice { + select 1 + where exists (select * + from dotlrn_ecommerce_transaction_invoice_payments + where order_id = :order_id + and method = 'cc') + }] } { + set method cc + } +} + +append doc_body " + + [export_entire_form] + [export_form_vars cash_amount_to_refund certificate_amount_to_reinstate cash_amount_to_refund_cc cash_amount_to_refund_manually] +
    +

    Total refund amount: [ec_pretty_price [expr $total_amount_to_refund + $total_amount_to_refund_manually]] (price: [ec_pretty_price [expr $total_price_to_refund + $total_price_to_refund_manually]], shipping: [ec_pretty_price $total_shipping_to_refund], tax: [ec_pretty_price $total_tax_to_refund])

    +
      " + +if { $certificate_amount_to_reinstate > 0 } { + if { $method == "scholarship" } { + append doc_body "
    • [ec_pretty_price $certificate_amount_to_reinstate] will be reinstated in the scholarship fund.
      " + } else { + append doc_body "
    • [ec_pretty_price $certificate_amount_to_reinstate] will be reinstated in gift certificates.
      " + } +} + +if { $method == "cc" } { + append doc_body " +
    • [ec_pretty_price $cash_amount_to_refund_cc] will be refunded to the customer's credit card.
      " +} + +append doc_body " +
    • [ec_pretty_price $cash_amount_to_refund_manually] will be refunded to the customer manually.
      " + +append doc_body " +
    " + +# Request the credit card number to be re-entered if it is no longer +# on file, yet there is money to refund. + +# Only ask for credit card info if credit card was used in purchase + +if { [empty_string_p $creditcard_number] && $cash_amount_to_refund > 0 && $method == "cc" } { + append doc_body " +

    Please re-enter the credit card number of the card used for this order:

    + + + + +
    + + + + + + + + + + + + + + + +
    + [ec_creditcard_widget $creditcard_type disabled]
    Ending in:xxxxxxxxxxxx$creditcard_last_four
    Expires:$card_expiration
    Billing address:$billing_street
    + $billing_city, $billing_state $billing_zip
    + $billing_country
    +
    " +} else { + append doc_body " + [export_form_vars creditcard_id creditcard_number]" +} +append doc_body " + [export_form_vars creditcard_type]" + +append doc_body " +
    +
    + [export_form_vars creditcard_last_four] +
    + +
    +
    • " + +#doc_return 200 text/html $doc_body +set context [list [list index Orders] [list one?order_id=$order_id "One Order"] "Refund"] \ No newline at end of file Index: openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-return-3.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-return-3.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-return-3.xql 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,26 @@ + + + + + + select count(*) + from ec_refunds + where refund_id=:refund_id + + + + + + 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 + + + + Index: openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-return-4-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-return-4-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-return-4-oracle.xql 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,201 @@ + + + + + oracle + 8.1.6 + + + + + 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) + + + + + + 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 + + + + + + select ec_tax(:price_bind_variable,0,:order_id) + from dual + + + + + + select ec_tax(0,:shipping_bind_variable,:order_id) + from dual + + + + + + select nvl(shipping_tax_charged,0) + from ec_orders + where order_id=:order_id + + + + + + select ec_tax(0,:base_shipping_to_refund,:order_id) + from dual + + + + + + 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) + + + + + + select ec_order_gift_cert_amount(:order_id) + from dual + + + + + + insert into ec_problems_log + (problem_id, problem_date, problem_details, order_id) + values + (ec_problem_id_sequence.nextval, sysdate, :errorstring, :order_id) + + + + + + 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 + + + + + + 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) + + + + + + insert into ec_problems_log + (problem_id, problem_date, problem_details, order_id) + values + (ec_problem_id_sequence.nextval, sysdate, :errorstring, :order_id) + + + + + + update ec_financial_transactions + set refunded_date=sysdate + where transaction_id=:pgw_transaction_id + + + + + + update ec_financial_transactions + set to_be_captured_date = sysdate + where transaction_id = :transaction_id + + + + + + 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 + + + + + + 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 + + + + + + update ec_financial_transactions + set refunded_amount = nvl(refunded_amount, 0) + :refund_amount + where transaction_id = :charged_transaction_id + + + + + + update ec_financial_transactions + set refunded_amount = coalesce(refunded_amount, 0) + :unrefunded_amount + where transaction_id = :charged_transaction_id + + + + + + 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')) + + + + + + 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')) + + + + + + update scholarship_fund_grants + set grant_amount = grant_amount - least(to_number(grant_amount), least(to_number(:certificate_amount_to_reinstate), to_number(:reinstateable_amount))) + where grant_id = :grant_id + + + + Index: openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-return-4-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-return-4-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-return-4-postgresql.xql 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,246 @@ + + + + postgresql7.1 + + + + 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) + + + + + + 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 + + + + + + select ec_tax(:price_bind_variable, 0, :order_id) + + + + + + select coalesce(shipping_tax_charged,0) + from ec_orders + where order_id=:order_id + + + + + + select ec_tax(0,:shipping_bind_variable,:order_id) + + + + + + select ec_tax(0,:base_shipping_to_refund,:order_id) + + + + + + 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 + + + + + + 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) + + + + + + update ec_financial_transactions + set refunded_amount = coalesce(refunded_amount, 0) + :refund_amount + where transaction_id = :charged_transaction_id + + + + + + select transaction_id as charged_transaction_id, (transaction_amount - coalesce(refunded_amount, 0)) as unrefunded_amount, 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 - 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 + + + + + + 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) + + + + + + update ec_financial_transactions + set refunded_amount = coalesce(refunded_amount, 0) + :unrefunded_amount + where transaction_id = :charged_transaction_id + + + + + + select ec_order_gift_cert_amount(:order_id) + + + + + + insert into ec_problems_log + (problem_id, problem_date, problem_details, order_id) + values + (ec_problem_id_sequence.nextval, current_timestamp, :errorstring, :order_id) + + + + + + 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 + + + + + + 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) + + + + + + insert into ec_problems_log + (problem_id, problem_date, problem_details, order_id) + values + (ec_problem_id_sequence.nextval, current_timestamp, :errorstring, :order_id) + + + + + + update ec_financial_transactions + set refunded_date = current_timestamp + where transaction_id=:pgw_transaction_id + + + + + + update ec_financial_transactions + set to_be_captured_date = current_timestamp + where transaction_id = :transaction_id + + + + + + 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 + + + + + + select transaction_id as charged_transaction_id, (transaction_amount - coalesce(refunded_amount, 0)) as unrefunded_amount, 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 - 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 + + + + + + update ec_financial_transactions + set refunded_amount = coalesce(refunded_amount, 0) + :refund_amount + where transaction_id = :charged_transaction_id + + + + + + update ec_financial_transactions + set refunded_amount = coalesce(refunded_amount, 0) + :unrefunded_amount + where transaction_id = :charged_transaction_id + + + + + + 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) + + + + + + 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) + + + + + + update scholarship_fund_grants + set grant_amount = grant_amount - least(grant_amount::numeric, least(:certificate_amount_to_reinstate, :reinstateable_amount)) + where grant_id = :grant_id + + + + Index: openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-return-4.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-return-4.adp,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-return-4.adp 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,5 @@ + + Refund + @context@ + + @doc_body;noquote@ \ No newline at end of file Index: openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-return-4.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-return-4.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-return-4.tcl 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,588 @@ +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 2008/09/02 21:54:48 daveb 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,optional + price_to_refund_manually:array + shipping_to_refund:array + base_shipping_to_refund + cash_amount_to_refund:optional + cash_amount_to_refund_cc:optional + cash_amount_to_refund_manually:optional + certificate_amount_to_reinstate + + creditcard_id:optional + creditcard_type:optional + creditcard_number:optional + creditcard_last_four:optional +} + +if { ! [array exists price_to_refund] } { + foreach _item [array names price_to_refund_manually] { + set price_to_refund($_item) 0 + } +} + +set method [db_string method { + select method + from dotlrn_ecommerce_transactions + where order_id = :order_id +} -default cc] + +if { $method == "invoice" } { + if { [db_0or1row cc_transaction_in_invoice { + select 1 + where exists (select * + from dotlrn_ecommerce_transaction_invoice_payments + where order_id = :order_id + and method = 'cc') + }] } { + set method cc + } +} + +# 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 " +
  • This refund has already been inserted into the database. Are you using an old form? Return to the order.
    • " + return +} + +# Check if money needs to be refunded and if the credit card number is +# still on file. + +if { $cash_amount_to_refund > 0 && $method == "cc" } { + + # 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 " +
  • + You forgot to provide the creditcard that was used to purchase the items to be returned. +
    • " + } + if {![info exists creditcard_type] || ([info exists creditcard_type] && [empty_string_p $creditcard_type])} { + incr exception_count + append exception_text " +
  • + You forgot to provide type of the creditcard that was used to purchase the items to be returned. +
    • " + } + if {![info exists creditcard_number] || ([info exists creditcard_number] && [empty_string_p $creditcard_number])} { + incr exception_count + append exception_text " +
  • + You forgot to provide card number of the creditcard that was used to purchase the items to be returned. +
    • " + } + if {![info exists creditcard_last_four] || ([info exists creditcard_last_four] && [empty_string_p $creditcard_last_four])} { + incr exception_count + append exception_text " +
  • + You forgot to provide card number of the creditcard that was used to purchase the items to be returned. +
    • " + } + + # 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 "
  • The credit card number contains invalid characters.
    • " + } + + if {[string length $creditcard_number] > 4 && ![string match *$creditcard_last_four $creditcard_number]} { + incr exception_count + append exception_text "
  • The last for digits of the credit card number do not match the digits on file.
    + Make sure to enter the card number of the credit card that was used to pay for the order.
    • " + } + + 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 + +if { $method == "cc" } { + 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 [expr $price_to_refund($item_id) + $price_to_refund_manually($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" + + # Remove the participant from the community + if { [db_0or1row community { + select s.community_id, o.participant_id, s.section_id + from dotlrn_ecommerce_section s, ec_items i, dotlrn_ecommerce_orders o + where s.product_id = i.product_id + and i.item_id = o.item_id + and i.item_id = :item_id + }] } { + if { [acs_object_type $participant_id] == "group" } { + # Remove one enrolled user from the group + if { [db_0or1row one_user { + select user_id as one_participant_id + from dotlrn_member_rels_approved + where community_id = :community_id + and user_id in (select member_id + from group_member_map + where group_id = :participant_id) + + limit 1 + }] } { + dotlrn_community::remove_user $community_id $one_participant_id + } + } else { + if { [dotlrn_community::member_p $community_id $participant_id] } { + dotlrn_community::remove_user $community_id $participant_id + } + } + + dotlrn_ecommerce::section::approve_next_in_waiting_list $community_id + dotlrn_ecommerce::section::flush_cache -user_id $participant_id $section_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_cc +while { $refund_amount > 0 && $method == "cc" } { + + # 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 " +

    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.

    +

    This transaction has been aborted, i.e. no refund has been given to the customer. This has been logged in the problems log.

    " + 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} { + # Return the amount to the scholarship fund + if { [db_0or1row scholarship_fund { + select grant_id + from scholarship_fund_grants + where gift_certificate_id = :gift_certificate_id + }] } { + db_dml return_to_scholarship_fund { } + } else { + # Don't reinstate gift certificate given via scholarship + 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 && $method == "cc" } { + 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 "

    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.

    " + } 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 "

    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.

    " + } 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 "

    Refund transaction $pgw_transaction_id for [ec_pretty_price $transaction_amount] is complete!

    ";# + if { $cash_amount_to_refund_manually > 0 } { + append results_explanation "

    The amount [ec_pretty_price $cash_amount_to_refund_manually] is to be refunded manually

    ";# + } + } + } 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 "

    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

    " + + } + } 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.

    " + } +} elseif { $cash_amount_to_refund_manually > 0 } { + set page_title "Refund results" + append results_explanation "

    The amount [ec_pretty_price $cash_amount_to_refund_manually] is to be refunded manually

    ";# +} 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 " +
    + $results_explanation + Back to Order Summary  Back to Order $order_id +
    " + +#doc_return 200 text/html $doc_body +set context [list [list index Orders] [list one?order_id=$order_id "One Order"] "Refund"] Index: openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-return-4.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-return-4.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-return-4.xql 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,107 @@ + + + + + + select user_id + from ec_orders + where order_id=:order_id + + + + + + select count(*) + from ec_refunds + where refund_id=:refund_id + + + + + + select creditcard_number + from ec_orders o, ec_creditcards c + where o.creditcard_id=c.creditcard_id + and o.order_id=:order_id + + + + + + select creditcard_id + from ec_orders + where order_id=:order_id + + + + + + 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) + + + + + + select creditcard_id + from ec_orders + where order_id=:order_id + + + + + + update ec_creditcards + set creditcard_number=:creditcard_number + where creditcard_id=:creditcard_id + + + + + + 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) + + + + + + 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 + + + + + + update ec_orders + set shipping_refunded=:base_shipping_to_refund, shipping_tax_refunded=:base_shipping_tax_to_refund + where order_id=:order_id + + + + + + select transaction_id, transaction_amount, refunded_transaction_id, to_char(to_be_captured_date, 'yyyy-mm-dd hh24:mi:ss') as 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' + + + + Index: openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-return.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-return.adp,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-return.adp 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,5 @@ + + Refund + @context@ + + @doc_body;noquote@ \ No newline at end of file Index: openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-return.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-return.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-return.tcl 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,85 @@ +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 " +
  • This order doesn't contain any refundable items; perhaps you are using an old form. Return to the order." + return +} + +set doc_body "" + +# Generate the new refund_id to prevent reusing this form. + +set refund_id [db_nextval refund_id_sequence] + +append doc_body " +
    + [export_form_vars order_id refund_id] + +
    +

    Date received back: [ad_dateentrywidget received_back_date] [ec_timeentrywidget received_back_time]

    + +

    Please check off the items that were received back:

    +
    " + +db_foreach refund_items { + select i.item_id, i.price_charged, i.price_name, + case when not c.course_name is null then c.course_name||': '||s.section_name else p.product_name end as product_name, person__name(o.participant_id) as participant_name + + from ec_items_refundable i left join dotlrn_ecommerce_orders o + on (i.item_id = o.item_id), + + ec_products p left join dotlrn_ecommerce_section s + on (p.product_id = s.product_id) + left join (select c.course_id, c.item_id, c.course_name + from dotlrn_catalogi c, cr_items i + where c.course_id = i.live_revision) c + on (s.course_id = c.item_id) + + where i.order_id = :order_id + and i.product_id = p.product_id +} { + if { [empty_string_p $participant_name] } { + append doc_body [subst { + $product_name ; $price_name: [ec_pretty_price $price_charged]
    + }] + } else { + append doc_body [subst { + Participant: $participant_name ; $product_name ; $price_name: [ec_pretty_price $price_charged]
    + }] + } +} + +append doc_body " +
    + +

    Reason for refund (if known):

    +
    + +
    +
    + +
    +
    " + +set context [list [list index Orders] [list one?order_id=$order_id "One Order"] "Refund"] \ No newline at end of file Index: openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-return.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-return.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-return.xql 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,11 @@ + + + + + + select count(*) from ec_items_refundable where order_id=:order_id + + + + + Index: openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-void-2-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-void-2-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-void-2-oracle.xql 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,70 @@ + + + + oracle8.1.6 + + + + select ec_order_gift_cert_amount(:order_id) from dual + + + + + + + update ec_items set item_state='void', voided_date=sysdate, voided_by=:customer_service_rep where item_id in ([join $item_id_list ", "]) + + + + + + + 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 + + + + + + + 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 + + + + + + + select ec_order_gift_cert_amount(:order_id) from dual + + + + + + + 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) + + + + + + + + select ec_one_gift_cert_on_one_order(:cert,:order_id) from dual + + + + + + + insert into ec_gift_certificate_usage + (gift_certificate_id, order_id, amount_reinstated, reinstated_date) + values + (:cert, :order_id, :iteration_reinstate_amount, sysdate) + + + + + + Index: openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-void-2-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-void-2-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-void-2-postgresql.xql 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,63 @@ + + + + postgresql7.1 + + + + select ec_order_gift_cert_amount(:order_id) + + + + + + + update ec_items set item_state='void', voided_date=current_timestamp, voided_by=:customer_service_rep where item_id in ([join $item_id_list ", "]) + + + + + + + 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 + + + + + + + select ec_order_gift_cert_amount(:order_id) + + + + + + + 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) + + + + + + + + select ec_one_gift_cert_on_one_order(:cert,:order_id) + + + + + + + insert into ec_gift_certificate_usage + (gift_certificate_id, order_id, amount_reinstated, reinstated_date) + values + (:cert, :order_id, :iteration_reinstate_amount, current_timestamp) + + + + + + Index: openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-void-2.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-void-2.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-void-2.tcl 2 Sep 2008 21:54:48 -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 "
  • You didn't check off any items.
    • " + 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" " +

    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. This has been logged in the problems log.

    " + 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/manage/ecommerce/items-void-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-void-2.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-void-2.xql 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,29 @@ + + + + + + select count(*) from ec_items where order_id=:order_id and product_id=:product_id + + + + + + + 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 + + + + + + + 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 + + + + + Index: openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-void.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-void.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-void.tcl 2 Sep 2008 21:54:48 -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 " +
  • These items are already void; perhaps you are using an old form. Return to the order.
    • " + 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"] + +

    Void $item_or_items

    + + [ad_context_bar [list "../" "Ecommerce([ec_system_name])"] [list "index" "Orders"] [list "one?[export_url_vars order_id]" "One Order"] "$item_or_items"] + +
    +
    +
    + [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 " +

    Warning: 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\").

    " + } + + 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 " +

    Please confirm that you want to void $product_name; $price_name: [ec_pretty_price $price_charged] $item_state

    " + } +} 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 " +

    Warning: 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\").

    " + } + doc_body_append " +

    Please check off the item(s) you wish to void.

    + + + + + " + + 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 " + + + + " + } + doc_body_append "
    Void ItemProductItem State
    " + if { $item_state == "void" } { + doc_body_append " (already void) " + } else { + doc_body_append "" + } + doc_body_append " + $product_name; $price_name: [ec_pretty_price $price_charged]$item_state
    " +} + +doc_body_append " +
    +
    + +
    + + [ad_admin_footer]" Index: openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-void.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-void.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/items-void.xql 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,43 @@ + + + + + + select count(*) from ec_items where order_id=:order_id and product_id=:product_id and item_state <> 'void' + + + + + + + select count(*) from ec_items where order_id=:order_id and product_id=:product_id + + + + + + + select item_state from ec_items where order_id=:order_id and product_id=:product_id + + + + + + + select count(*) from ec_items where order_id=:order_id and product_id=:product_id and item_state in ('shipped','arrived','received_back') + + + + + + + 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 + + + + + Index: openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/one-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/one-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/one-oracle.xql 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,22 @@ + + + + + oracle + 8.1.6 + + + + + 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(+) + + + + Index: openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/one-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/one-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/one-postgresql.xql 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,17 @@ + + + + + + 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 + + + + Index: openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/one.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/one.adp,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/one.adp 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,5 @@ + + One Order + @context@ + + @doc_body;noquote@ \ No newline at end of file Index: openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/one.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/one.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/one.tcl 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,181 @@ +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(+)" + +set doc_body "" + +append doc_body " + [ec_decode $order_state "void" "" "
    "] + + + + + + + + + + + + + + + + + +
    Order ID$order_id[ec_decode $order_state "void" "" "
    [ec_formatted_price_shipping_gift_certificate_and_tax_in_an_order $order_id]
    "]
    Ordered by$first_names $last_name
    Confirmed date[ec_formatted_full_date $confirmed_date]
    Order state[ec_decode $order_state "void" "void" $order_state]
    " + +if { $order_state == "void" } { + append doc_body " +

    Details of Void

    + +
    + Voided by: [db_string voided_by_name_select " + select first_names || ' ' || last_name from cc_users where user_id = :voided_by" -default ""]
    + Date: [ec_formatted_full_date $voided_date]
    + [ec_decode $reason_for_void "" "" "Reason: [ec_display_as_html $reason_for_void]"] +
    " +} + +append doc_body " + [ec_decode $cs_comments "" "" "

    Comments

    \n
    [ec_display_as_html $cs_comments]
    "] + + + +

    Items

    +
      " + +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 " +
    • + 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 " +
      + [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] +
      • " + 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 " + $product_name; + [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 " +
    • + 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 " +
      + [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] +
      • " +} + +append doc_body "$items_ul" + +if { $order_state == "authorized" || $order_state == "partially_fulfilled" } { + append doc_body " +
    • Record a Shipment
      • +
    • Add Items
      • " +} +if { $order_state == "fulfilled" || $order_state == "partially_fulfilled" } { + append doc_body " +
    • Refund
      • " +} + +append doc_body " +
    + " + +set refunds [template::adp_compile -string {}] +# Hack to not display ds stuff even if it's enabled, demo purposes +regsub -all {\[::ds_show_p\]} $refunds 0 refunds + +append doc_body "[eval $refunds]" + +set context [list [list index Orders] [list one?order_id=$order_id "One Order"] "One Order"] \ No newline at end of file Index: openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/one.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/one.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/one.xql 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,61 @@ + + + + + + select first_names || ' ' || last_name + from cc_users + where user_id=:voided_by + + + + + + 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 + + + + + + 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 + + + + + + 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 + + + + + + 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 + + + + Index: openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/search-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/search-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/search-oracle.xql 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,98 @@ + + + + oracle8.1.6 + + + + + 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 + + + + + + + + + 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 + + + + + + + + + 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 + + + + + + + + + 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 + + + + + + + Index: openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/search-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/search-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/search-postgresql.xql 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,98 @@ + + + + postgresql7.1 + + + + + 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 + + + + + + + + + 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 + + + + + + + + + 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 + + + + + + + + + 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 + + + + + + + Index: openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/track-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/track-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/track-oracle.xql 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,17 @@ + + + + oracle8.1.6 + + + + +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 + + + + + + Index: openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/track-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/track-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/track-postgresql.xql 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,17 @@ + + + + postgresql7.1 + + + + +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 + + + + + + Index: openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/track.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/track.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/track.tcl 2 Sep 2008 21:54:48 -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 2008/09/02 21:54:48 daveb 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"] + +

    Track Shipment

    + +[ad_context_bar [list "../" "Ecommerce([ec_system_name])"] [list "index" "Orders"] [list "one?[export_url_vars order_id]" "One Order"] "Track Shipment"] + +
    +" + + +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 {().*?(
    .*?
    )} $page_from_fedex match detailed_info scan_activity + # Remove links + regsub -all -nocase {} $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 {(]*>Tracking Number:.*).*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 {} $ups_info "" ups_info + set carrier_info "$ups_info" + } + } { + set carrier_info "Unable to retrieve data from UPS." + } + +} + +doc_body_append "
      +
    • Shipping Date: $pretty_ship_date +
    • Carrier: $carrier +
    • Tracking Number: $tracking_number +
    + +

    Information from [ec_decode $carrier "" "Carrier" $carrier]

    + +
    +[ec_decode $carrier_info "" "None Available" $carrier_info] +
    + +[ad_admin_footer] +" Index: openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/void-2-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/void-2-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/void-2-oracle.xql 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,27 @@ + + + + oracle8.1.6 + + + + + 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 + + + + + + + + declare begin ec_reinst_gift_cert_on_order(:order_id); end; + + + + + Index: openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/void-2-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/void-2-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/void-2-postgresql.xql 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,27 @@ + + + + postgresql7.1 + + + + + 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 + + + + + + + + select ec_reinst_gift_cert_on_order(:order_id) + + + + + Index: openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/void-2.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/void-2.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/void-2.tcl 2 Sep 2008 21:54:48 -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 2008/09/02 21:54:48 daveb 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/manage/ecommerce/void-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/void-2.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/void-2.xql 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,16 @@ + + + + + + + update ec_items + set item_state='void', + voided_by=:customer_service_rep + where order_id=:order_id + + + + + + Index: openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/void.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/void.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/void.tcl 2 Sep 2008 21:54:48 -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 2008/09/02 21:54:48 daveb 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"] + +

    Void Order

    + +[ad_context_bar [list "../" "Ecommerce([ec_system_name])"] [list "index" "Orders"] [list "one?[export_url_vars order_id]" "One Order"] "Void"] + +
    +" + +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 "Warning: 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\"). +

    + Voiding is usually done if the customer cancels their order before it ships. +

    + " +} + +doc_body_append "Note: this will cause all individual items in this order +to be marked 'void'. +

    " + +doc_body_append " +
    +[export_form_vars order_id] + +Please explain why you are voiding this order: + +
    + +

    + +
    + +

    +

    + +
    + + + +[ad_admin_footer] +" Index: openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/void.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/void.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-ecommerce/www/manage/ecommerce/void.xql 2 Sep 2008 21:54:48 -0000 1.1 @@ -0,0 +1,11 @@ + + + + + + select count(*) from ec_items where order_id=:order_id and item_state in ('shipped', 'arrived', 'received_back') + + + + +