Index: openacs-4/packages/ecommerce/www/admin/audit-table-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/audit-table-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/audit-table-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,17 @@ + + + + oracle8.1.6 + + + + + select 1 + from dual + where to_date('$start_date(date)','YYYY-MM-DD HH24:MI:SS') > to_date('$end_date(date)', 'YYYY-MM-DD HH24:MI:SS') + + + + + + Index: openacs-4/packages/ecommerce/www/admin/audit-table-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/audit-table-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/audit-table-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,17 @@ + + + + postgresql7.1 + + + + + select 1 + + where to_date('$start_date(date)','YYYY-MM-DD HH24:MI:SS') > to_date('$end_date(date)', 'YYYY-MM-DD HH24:MI:SS') + + + + + + Index: openacs-4/packages/ecommerce/www/admin/index.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/index.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/index.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,50 @@ + + + + + + select count(*) from ec_problems_log where resolved_date is null + + + + + + + +select + sum(one_if_within_n_days(confirmed_date,1)) as n_in_last_24_hours, + sum(one_if_within_n_days(confirmed_date,7)) as n_in_last_7_days +from ec_orders_reportable + + + + + + + select count(*) as n_products, round(avg(price),2) as avg_price from ec_products_displayable + + + + + + + select count(*) from ec_customer_service_issues where close_date is null + + + + + + + select count(*) from ec_product_comments where approved_p is null + + + + + + + select count(*) from ec_user_class_user_map where user_class_approved_p is null or user_class_approved_p='f' + + + + + Index: openacs-4/packages/ecommerce/www/admin/restore-one-id-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/restore-one-id-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/restore-one-id-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,13 @@ + + + + oracle8.1.6 + + + + select * from $audit_table_name where rowid = :rowid + + + + + Index: openacs-4/packages/ecommerce/www/admin/restore-one-id-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/restore-one-id-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/restore-one-id-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,13 @@ + + + + postgresql7.1 + + + + select * from $audit_table_name where oid as rowid = :rowid + + + + + Index: openacs-4/packages/ecommerce/www/admin/cat/category-add-0.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/cat/category-add-0.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/cat/category-add-0.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,13 @@ + + + + + + select count(*) +from ec_categories +where sort_key = (:prev_sort_key + :next_sort_key)/2 + + + + + Index: openacs-4/packages/ecommerce/www/admin/cat/category-add-2-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/cat/category-add-2-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/cat/category-add-2-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,16 @@ + + + + oracle8.1.6 + + + + insert into ec_categories +(category_id, category_name, sort_key, last_modified, last_modifying_user, modified_ip_address) +values +(:category_id, :category_name, (:prev_sort_key + :next_sort_key)/2, sysdate, :user_id, :peeraddr) + + + + + Index: openacs-4/packages/ecommerce/www/admin/cat/category-add-2-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/cat/category-add-2-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/cat/category-add-2-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,16 @@ + + + + postgresql7.1 + + + + insert into ec_categories +(category_id, category_name, sort_key, last_modified, last_modifying_user, modified_ip_address) +values +(:category_id, :category_name, (:prev_sort_key + :next_sort_key)/2, current_timestamp, :user_id, :peeraddr) + + + + + Index: openacs-4/packages/ecommerce/www/admin/cat/category-add-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/cat/category-add-2.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/cat/category-add-2.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,21 @@ + + + + + + select category_id from ec_categories +where category_id=:category_id + + + + + + + select count(*) +from ec_categories +where sort_key = (:prev_sort_key + :next_sort_key)/2 + + + + + Index: openacs-4/packages/ecommerce/www/admin/cat/category-add-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/cat/Attic/category-add-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/cat/category-add-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,13 @@ + + + + oracle8.1.6 + + + + select ec_category_id_sequence.nextval from dual + + + + + Index: openacs-4/packages/ecommerce/www/admin/cat/category-add-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/cat/Attic/category-add-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/cat/category-add-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,13 @@ + + + + postgresql7.1 + + + + select ec_category_id_sequence.nextval + + + + + Index: openacs-4/packages/ecommerce/www/admin/cat/category-add.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/cat/category-add.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/cat/category-add.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,13 @@ + + + + + + select count(*) +from ec_categories +where sort_key = (:prev_sort_key + :next_sort_key)/2 + + + + + Index: openacs-4/packages/ecommerce/www/admin/cat/category-delete-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/cat/category-delete-2.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/cat/category-delete-2.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,76 @@ + + + + + + select subsubcategory_id from ec_subsubcategories where subcategory_id in (select subcategory_id from ec_subcategories where category_id=:category_id) + + + + + + + select subcategory_id from ec_subcategories where category_id=:category_id + + + + + + + delete from ec_subsubcategory_product_map +where subsubcategory_id in (select subsubcategory_id from ec_subsubcategories where subcategory_id in (select subcategory_id from ec_subcategories where category_id=:category_id)) + + + + + + + delete from ec_subsubcategories where subcategory_id in (select subcategory_id from ec_subcategories where category_id=:category_id) + + + + + + + delete from ec_subcategory_product_map +where subcategory_id in (select subcategory_id from ec_subcategories where category_id=:category_id) + + + + + + + delete from ec_subsubcategories where subcategory_id in (select subcategory_id from ec_subcategories where category_id=:category_id) + + + + + + + delete from ec_category_product_map where category_id=:category_id + + + + + + + delete from ec_category_template_map where category_id=:category_id + + + + + + + delete from ec_user_session_info where category_id=:category_id + + + + + + + delete from ec_categories where category_id=:category_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/cat/category-edit-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/cat/category-edit-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/cat/category-edit-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,18 @@ + + + + oracle8.1.6 + + + + update ec_categories +set category_name=:category_name, +last_modified=sysdate, +last_modifying_user=:user_id, +modified_ip_address=:address +where category_id=:category_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/cat/category-edit-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/cat/category-edit-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/cat/category-edit-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,18 @@ + + + + postgresql7.1 + + + + update ec_categories +set category_name=:category_name, +last_modified=current_timestamp, +last_modifying_user=:user_id, +modified_ip_address=:address +where category_id=:category_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/cat/category-swap.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/cat/category-swap.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/cat/category-swap.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,32 @@ + + + + + + select count(*) from ec_categories where category_id=:category_id and sort_key=:sort_key + + + + + + + select count(*) from ec_categories where category_id=:next_category_id and sort_key=:next_sort_key + + + + + + + update ec_categories set sort_key=:next_sort_key where category_id=:category_id + + + + + + + update ec_categories set sort_key=:sort_key where category_id=:next_category_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/cat/category.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/cat/category.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/cat/category.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,11 @@ + + + + + + select subcategory_id, sort_key, subcategory_name from ec_subcategories where category_id=:category_id order by sort_key + + + + + Index: openacs-4/packages/ecommerce/www/admin/cat/index.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/cat/index.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/cat/index.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,11 @@ + + + + + + select category_id, sort_key, category_name from ec_categories order by sort_key + + + + + Index: openacs-4/packages/ecommerce/www/admin/cat/subcategory-add-0.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/cat/subcategory-add-0.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/cat/subcategory-add-0.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,14 @@ + + + + + + select count(*) +from ec_subcategories +where category_id=:category_id +and sort_key = (:prev_sort_key + :next_sort_key)/2 + + + + + Index: openacs-4/packages/ecommerce/www/admin/cat/subcategory-add-2-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/cat/subcategory-add-2-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/cat/subcategory-add-2-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,16 @@ + + + + oracle8.1.6 + + + + insert into ec_subcategories + (category_id, subcategory_id, subcategory_name, sort_key, last_modified, last_modifying_user, modified_ip_address) + values + (:category_id, :subcategory_id, :subcategory_name, (:prev_sort_key + :next_sort_key)/2, sysdate, :user_id, :address) + + + + + Index: openacs-4/packages/ecommerce/www/admin/cat/subcategory-add-2-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/cat/subcategory-add-2-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/cat/subcategory-add-2-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,16 @@ + + + + postgresql7.1 + + + + insert into ec_subcategories + (category_id, subcategory_id, subcategory_name, sort_key, last_modified, last_modifying_user, modified_ip_address) + values + (:category_id, :subcategory_id, :subcategory_name, (:prev_sort_key + :next_sort_key)/2, current_timestamp, :user_id, :address) + + + + + Index: openacs-4/packages/ecommerce/www/admin/cat/subcategory-add-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/cat/subcategory-add-2.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/cat/subcategory-add-2.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,22 @@ + + + + + + select subcategory_id from ec_subcategories +where subcategory_id=:subcategory_id + + + + + + + select count(*) +from ec_subcategories +where category_id=:category_id +and sort_key = (:prev_sort_key + :next_sort_key)/2 + + + + + Index: openacs-4/packages/ecommerce/www/admin/cat/subcategory-add-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/cat/Attic/subcategory-add-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/cat/subcategory-add-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,13 @@ + + + + oracle8.1.6 + + + + select ec_subcategory_id_sequence.nextval from dual + + + + + Index: openacs-4/packages/ecommerce/www/admin/cat/subcategory-add-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/cat/Attic/subcategory-add-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/cat/subcategory-add-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,13 @@ + + + + postgresql7.1 + + + + select ec_subcategory_id_sequence.nextval + + + + + Index: openacs-4/packages/ecommerce/www/admin/cat/subcategory-add.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/cat/subcategory-add.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/cat/subcategory-add.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,14 @@ + + + + + + select count(*) +from ec_subcategories +where category_id=:category_id +and sort_key = (:prev_sort_key + :next_sort_key)/2 + + + + + Index: openacs-4/packages/ecommerce/www/admin/cat/subcategory-delete-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/cat/subcategory-delete-2.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/cat/subcategory-delete-2.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,40 @@ + + + + + + select subsubcategory_id from ec_subsubcategories where subcategory_id=:subcategory_id + + + + + + + delete from ec_subsubcategory_product_map + where subsubcategory_id in (select subsubcategory_id from ec_subsubcategories where subcategory_id=:subcategory_id) + + + + + + + delete from ec_subsubcategories where subcategory_id=:subcategory_id + + + + + + + delete from ec_subcategory_product_map where subcategory_id=:subcategory_id + + + + + + + delete from ec_subcategories where subcategory_id=:subcategory_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/cat/subcategory-edit-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/cat/subcategory-edit-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/cat/subcategory-edit-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,18 @@ + + + + oracle8.1.6 + + + + update ec_subcategories +set subcategory_name=:subcategory_name, +last_modified=sysdate, +last_modifying_user=:user_id, +modified_ip_address=:address +where subcategory_id=:subcategory_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/cat/subcategory-edit-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/cat/subcategory-edit-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/cat/subcategory-edit-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,18 @@ + + + + postgresql7.1 + + + + update ec_subcategories +set subcategory_name=:subcategory_name, +last_modified=current_timestamp, +last_modifying_user=:user_id, +modified_ip_address=:address +where subcategory_id=:subcategory_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/cat/subcategory-swap.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/cat/subcategory-swap.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/cat/subcategory-swap.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,32 @@ + + + + + + select count(*) from ec_subcategories where subcategory_id=:subcategory_id and sort_key=:sort_key + + + + + + + select count(*) from ec_subcategories where subcategory_id=:next_subcategory_id and sort_key=:next_sort_key + + + + + + + update ec_subcategories set sort_key=:next_sort_key where subcategory_id=:subcategory_id + + + + + + + update ec_subcategories set sort_key=:sort_key where subcategory_id=:next_subcategory_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/cat/subcategory.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/cat/subcategory.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/cat/subcategory.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,11 @@ + + + + + + select subsubcategory_id, sort_key, subsubcategory_name from ec_subsubcategories where subcategory_id=:subcategory_id order by sort_key + + + + + Index: openacs-4/packages/ecommerce/www/admin/cat/subsubcategory-add-0.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/cat/subsubcategory-add-0.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/cat/subsubcategory-add-0.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,14 @@ + + + + + + select count(*) +from ec_subsubcategories +where subcategory_id=:subcategory_id +and sort_key = (:prev_sort_key + :next_sort_key)/2 + + + + + Index: openacs-4/packages/ecommerce/www/admin/cat/subsubcategory-add-2-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/cat/subsubcategory-add-2-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/cat/subsubcategory-add-2-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,16 @@ + + + + oracle8.1.6 + + + + insert into ec_subsubcategories +(subcategory_id, subsubcategory_id, subsubcategory_name, sort_key, last_modified, last_modifying_user, modified_ip_address) +values +(:subcategory_id, :subsubcategory_id, :subsubcategory_name, (:prev_sort_key + :next_sort_key)/2, sysdate, :user_id,:address) + + + + + Index: openacs-4/packages/ecommerce/www/admin/cat/subsubcategory-add-2-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/cat/subsubcategory-add-2-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/cat/subsubcategory-add-2-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,16 @@ + + + + postgresql7.1 + + + + insert into ec_subsubcategories +(subcategory_id, subsubcategory_id, subsubcategory_name, sort_key, last_modified, last_modifying_user, modified_ip_address) +values +(:subcategory_id, :subsubcategory_id, :subsubcategory_name, (:prev_sort_key + :next_sort_key)/2, current_timestamp, :user_id,:address) + + + + + Index: openacs-4/packages/ecommerce/www/admin/cat/subsubcategory-add-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/cat/subsubcategory-add-2.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/cat/subsubcategory-add-2.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,22 @@ + + + + + + select subsubcategory_id from ec_subsubcategories +where subsubcategory_id=:subsubcategory_id + + + + + + + select count(*) +from ec_subsubcategories +where subcategory_id=:subcategory_id +and sort_key = (:prev_sort_key + :next_sort_key)/2 + + + + + Index: openacs-4/packages/ecommerce/www/admin/cat/subsubcategory-add-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/cat/Attic/subsubcategory-add-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/cat/subsubcategory-add-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,13 @@ + + + + oracle8.1.6 + + + + select ec_subsubcategory_id_sequence.nextval from dual + + + + + Index: openacs-4/packages/ecommerce/www/admin/cat/subsubcategory-add-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/cat/Attic/subsubcategory-add-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/cat/subsubcategory-add-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,13 @@ + + + + postgresql7.1 + + + + select ec_subsubcategory_id_sequence.nextval + + + + + Index: openacs-4/packages/ecommerce/www/admin/cat/subsubcategory-add.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/cat/subsubcategory-add.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/cat/subsubcategory-add.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,14 @@ + + + + + + select count(*) +from ec_subsubcategories +where subcategory_id=:subcategory_id +and sort_key = (:prev_sort_key + :next_sort_key)/2 + + + + + Index: openacs-4/packages/ecommerce/www/admin/cat/subsubcategory-delete-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/cat/subsubcategory-delete-2.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/cat/subsubcategory-delete-2.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,18 @@ + + + + + + delete from ec_subsubcategory_product_map where subsubcategory_id=:subsubcategory_id + + + + + + + delete from ec_subsubcategories where subsubcategory_id = :subsubcategory_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/cat/subsubcategory-edit-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/cat/subsubcategory-edit-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/cat/subsubcategory-edit-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,18 @@ + + + + oracle8.1.6 + + + + update ec_subsubcategories +set subsubcategory_name=:subsubcategory_name, +last_modified=sysdate, +last_modifying_user=:user_id, +modified_ip_address=:address +where subsubcategory_id=:subsubcategory_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/cat/subsubcategory-edit-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/cat/subsubcategory-edit-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/cat/subsubcategory-edit-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,18 @@ + + + + postgresql7.1 + + + + update ec_subsubcategories +set subsubcategory_name=:subsubcategory_name, +last_modified=current_timestamp, +last_modifying_user=:user_id, +modified_ip_address=:address +where subsubcategory_id=:subsubcategory_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/cat/subsubcategory-swap.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/cat/subsubcategory-swap.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/cat/subsubcategory-swap.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,32 @@ + + + + + + select count(*) from ec_subsubcategories where subsubcategory_id=:subsubcategory_id and sort_key=:sort_key + + + + + + + select count(*) from ec_subsubcategories where subsubcategory_id=:next_subsubcategory_id and sort_key=:next_sort_key + + + + + + + update ec_subsubcategories set sort_key=:next_sort_key where subsubcategory_id=:subsubcategory_id + + + + + + + update ec_subsubcategories set sort_key=:sort_key where subsubcategory_id=:next_subsubcategory_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/customer-reviews/approval-change-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-reviews/approval-change-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/customer-reviews/approval-change-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,18 @@ + + + + oracle8.1.6 + + + + update ec_product_comments set +approved_p=:approved_p, +last_modified = sysdate, +last_modifying_user = :user_id, +modified_ip_address = '[ns_conn peeraddr]' +where comment_id=:comment_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/customer-reviews/approval-change-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-reviews/approval-change-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/customer-reviews/approval-change-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,18 @@ + + + + postgresql7.1 + + + + update ec_product_comments set +approved_p=:approved_p, +last_modified = current_timestamp, +last_modifying_user = :user_id, +modified_ip_address = '[ns_conn peeraddr]' +where comment_id=:comment_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/customer-reviews/one.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-reviews/one.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/customer-reviews/one.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,15 @@ + + + + + + select c.product_id, c.user_id, c.user_comment, c.one_line_summary, c.rating, p.product_name, u.email, c.comment_date, c.approved_p +from ec_product_comments c, ec_products p, cc_users u +where c.product_id = p.product_id +and c. user_id = u.user_id +and c.comment_id=:comment_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/customer-service/actions.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/actions.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/customer-service/actions.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,16 @@ + + + + + + + select picklist_item + from ec_picklist_items + where picklist_name='info_used' +order by sort_key + + + + + + Index: openacs-4/packages/ecommerce/www/admin/customer-service/active-toggle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/active-toggle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/customer-service/active-toggle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,13 @@ + + + + + + update $table_name +set active_p=:active_p +where $primary_key_name=:primary_key_value + + + + + Index: openacs-4/packages/ecommerce/www/admin/customer-service/canned-response-add-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/canned-response-add-2.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/customer-service/canned-response-add-2.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,19 @@ + + + + + + select response_id from ec_canned_responses where one_line = :one_line + + + + + + + insert into ec_canned_responses (response_id, one_line, response_text) +values (ec_canned_response_id_sequence.nextval, :one_line, :response_text) + + + + + Index: openacs-4/packages/ecommerce/www/admin/customer-service/canned-response-delete-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/canned-response-delete-2.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/customer-service/canned-response-delete-2.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,11 @@ + + + + + + delete from ec_canned_responses where response_id = :response_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/customer-service/canned-response-delete.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/canned-response-delete.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/customer-service/canned-response-delete.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,13 @@ + + + + + + select one_line, response_text +from ec_canned_responses +where response_id = :response_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/customer-service/canned-response-edit-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/canned-response-edit-2.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/customer-service/canned-response-edit-2.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,13 @@ + + + + + + update ec_canned_responses +set one_line = :one_line, response_text = :response_text +where response_id = :response_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/customer-service/canned-response-edit.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/canned-response-edit.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/customer-service/canned-response-edit.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,13 @@ + + + + + + select one_line, response_text +from ec_canned_responses +where response_id = :response_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/customer-service/email-send-2-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/email-send-2-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/customer-service/email-send-2-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,24 @@ + + + + oracle8.1.6 + + + + select ec_interaction_id_sequence.nextval from dual + + + + + + + insert into ec_customer_serv_interactions +(interaction_id, customer_service_rep, user_identification_id, interaction_date, interaction_originator, interaction_type) +values +(:interaction_id, :customer_service_rep, :user_identification_id, sysdate, 'rep', 'email') + + + + + + Index: openacs-4/packages/ecommerce/www/admin/customer-service/email-send-2-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/email-send-2-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/customer-service/email-send-2-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,24 @@ + + + + postgresql7.1 + + + + select ec_interaction_id_sequence.nextval + + + + + + + insert into ec_customer_serv_interactions +(interaction_id, customer_service_rep, user_identification_id, interaction_date, interaction_originator, interaction_type) +values +(:interaction_id, :customer_service_rep, :user_identification_id, current_timestamp, 'rep', 'email') + + + + + + Index: openacs-4/packages/ecommerce/www/admin/customer-service/email-send-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/email-send-2.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/customer-service/email-send-2.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,22 @@ + + + + + + select count(*) from ec_customer_service_actions where action_id=:action_id + + + + + + + insert into ec_customer_service_actions +(action_id, issue_id, interaction_id, action_details) +values +(:action_id, :issue_id, :interaction_id, :action_details) + + + + + + Index: openacs-4/packages/ecommerce/www/admin/customer-service/email-send-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/email-send-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/customer-service/email-send-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,25 @@ + + + + oracle8.1.6 + + + + +select u.email as user_email, id.email as id_email + from cc_users u, ec_user_identification id + where id.user_id = u.user_id(+) + and id.user_identification_id=:user_identification_id + + + + + + + + select ec_action_id_sequence.nextval from dual + + + + + Index: openacs-4/packages/ecommerce/www/admin/customer-service/email-send-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/email-send-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/customer-service/email-send-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,13 @@ + + + + postgresql7.1 + + + + select ec_action_id_sequence.nextval + + + + + Index: openacs-4/packages/ecommerce/www/admin/customer-service/email-send.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/email-send.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/customer-service/email-send.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,23 @@ + + + + + + +select u.email as user_email, id.email as id_email + from ec_user_identification id + left join cc_users u using (user_id) + where id.user_identification_id=:user_identification_id + + + + + + + + select first_names || ' ' || last_name from cc_users where user_id=:customer_service_rep + + + + + Index: openacs-4/packages/ecommerce/www/admin/customer-service/gift-certificate-add-2-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/gift-certificate-add-2-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/customer-service/gift-certificate-add-2-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,17 @@ + + + + oracle8.1.6 + + + + insert into ec_gift_certificates +(gift_certificate_id, user_id, amount, expires, issue_date, issued_by, gift_certificate_state, last_modified, last_modifying_user, modified_ip_address) +values +(ec_gift_cert_id_sequence.nextval, :user_id, :amount, :expires_to_insert, sysdate, :customer_service_rep, 'authorized', sysdate, :customer_service_rep, address) + + + + + + Index: openacs-4/packages/ecommerce/www/admin/customer-service/gift-certificate-add-2-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/gift-certificate-add-2-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/customer-service/gift-certificate-add-2-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,17 @@ + + + + postgresql7.1 + + + + insert into ec_gift_certificates +(gift_certificate_id, user_id, amount, expires, issue_date, issued_by, gift_certificate_state, last_modified, last_modifying_user, modified_ip_address) +values +(ec_gift_cert_id_sequence.nextval, :user_id, :amount, :expires_to_insert, current_timestamp, :customer_service_rep, 'authorized', current_timestamp, :customer_service_rep, address) + + + + + + Index: openacs-4/packages/ecommerce/www/admin/customer-service/gift-certificate-add-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/gift-certificate-add-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/customer-service/gift-certificate-add-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,13 @@ + + + + oracle8.1.6 + + + + select [ec_decode $expires "" "null" $expires] from dual + + + + + Index: openacs-4/packages/ecommerce/www/admin/customer-service/gift-certificate-add-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/gift-certificate-add-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/customer-service/gift-certificate-add-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,13 @@ + + + + postgresql7.1 + + + + select [ec_decode $expires "" "null" $expires] + + + + + Index: openacs-4/packages/ecommerce/www/admin/customer-service/gift-certificate-add.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/gift-certificate-add.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/customer-service/gift-certificate-add.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,11 @@ + + + + + + select first_names || ' ' || last_name from cc_users where user_id=:user_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/customer-service/gift-certificate-edit-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/gift-certificate-edit-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/customer-service/gift-certificate-edit-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,16 @@ + + + + oracle8.1.6 + + + + update ec_gift_certificates +set expires=sysdate, last_modified=sysdate, last_modifying_user=:customer_service_rep, +modified_ip_address= :address +where gift_certificate_id=:gift_certificate_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/customer-service/gift-certificate-edit-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/gift-certificate-edit-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/customer-service/gift-certificate-edit-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,16 @@ + + + + postgresql7.1 + + + + update ec_gift_certificates +set expires=current_timestamp, last_modified=current_timestamp, last_modifying_user=:customer_service_rep, +modified_ip_address= :address +where gift_certificate_id=:gift_certificate_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/customer-service/gift-certificate-void-2-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/gift-certificate-void-2-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/customer-service/gift-certificate-void-2-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,13 @@ + + + + oracle8.1.6 + + + + update ec_gift_certificates set gift_certificate_state='void', voided_date=sysdate, voided_by=:customer_service_rep, reason_for_void=:reason_for_void where gift_certificate_id=:gift_certificate_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/customer-service/gift-certificate-void-2-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/gift-certificate-void-2-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/customer-service/gift-certificate-void-2-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,13 @@ + + + + postgresql7.1 + + + + update ec_gift_certificates set gift_certificate_state='void', voided_date=current_timestamp, voided_by=:customer_service_rep, reason_for_void=:reason_for_void where gift_certificate_id=:gift_certificate_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/customer-service/gift-certificate-void-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/gift-certificate-void-2.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/customer-service/gift-certificate-void-2.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,11 @@ + + + + + + select user_id from ec_gift_certificates where gift_certificate_id=:gift_certificate_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/customer-service/gift-certificates-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/gift-certificates-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/customer-service/gift-certificates-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,13 @@ + + + + oracle8.1.6 + + + + select ec_gift_certificate_balance(:user_id) from dual + + + + + Index: openacs-4/packages/ecommerce/www/admin/customer-service/gift-certificates-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/gift-certificates-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/customer-service/gift-certificates-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,13 @@ + + + + postgresql7.1 + + + + select ec_gift_certificate_balance(:user_id) + + + + + Index: openacs-4/packages/ecommerce/www/admin/customer-service/gift-certificates.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/gift-certificates.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/customer-service/gift-certificates.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,11 @@ + + + + + + select first_names || ' ' || last_name from cc_users where user_id=:user_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/customer-service/index.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/index.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/customer-service/index.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,47 @@ + + + + + + select count(*) +from ec_customer_service_issues issues, ec_user_identification id +where issues.user_identification_id = id.user_identification_id +and close_date is NULL +and deleted_p = 'f' +and 0 = (select count(*) from ec_cs_issue_type_map map where map.issue_id=issues.issue_id) + + + + + + + select picklist_item from ec_picklist_items where picklist_name='issue_type' order by sort_key + + + + + + + select count(*) +from ec_customer_service_issues issues, ec_user_identification id +where issues.user_identification_id = id.user_identification_id +and close_date is NULL +and deleted_p = 'f' +and 1 <= (select count(*) from ec_cs_issue_type_map map where map.issue_id=issues.issue_id and map.issue_type=:issue_type) + + + + + + + select count(*) +from ec_customer_service_issues issues, ec_user_identification id +where issues.user_identification_id = id.user_identification_id +and close_date is NULL +and deleted_p = 'f' +$last_bit_of_query + + + + + Index: openacs-4/packages/ecommerce/www/admin/customer-service/interaction-add-2-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/interaction-add-2-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/customer-service/interaction-add-2-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,20 @@ + + + + oracle8.1.6 + + + + select to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') from dual + + + + + + + select ec_action_id_sequence.nextval from dual + + + + + Index: openacs-4/packages/ecommerce/www/admin/customer-service/interaction-add-2-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/interaction-add-2-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/customer-service/interaction-add-2-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,20 @@ + + + + postgresql7.1 + + + + select to_char(current_timestamp, 'YYYY-MM-DD HH24:MI:SS') + + + + + + + select ec_action_id_sequence.nextval + + + + + Index: openacs-4/packages/ecommerce/www/admin/customer-service/interaction-add-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/interaction-add-2.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/customer-service/interaction-add-2.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,25 @@ + + + + + + select order_id from ec_customer_service_issues where issue_id=:issue_id + + + + + + + select issue_type from ec_cs_issue_type_map where issue_id=:issue_id + + + + + + + select first_names as d_first_names, last_name as d_last_name, user_id as d_user_id from cc_users where upper(email) =:email + + + + + Index: openacs-4/packages/ecommerce/www/admin/customer-service/interaction-add-3-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/interaction-add-3-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/customer-service/interaction-add-3-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,34 @@ + + + + oracle8.1.6 + + + + select to_char(interaction_date, 'YYYY-MM-DD HH24:MI:SS') as open_date_str from ec_customer_serv_interactions where interaction_id=:interaction_id + + + + + + + select ec_interaction_id_sequence.nextval from dual + + + + + + + select ec_interaction_id_sequence.nextval from dual + + + + + + + select ec_user_ident_id_sequence.nextval from dual + + + + + Index: openacs-4/packages/ecommerce/www/admin/customer-service/interaction-add-3-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/interaction-add-3-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/customer-service/interaction-add-3-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,34 @@ + + + + postgresql7.1 + + + + select to_char(interaction_date, 'YYYY-MM-DD HH24:MI:SS') as open_date_str from ec_customer_serv_interactions where interaction_id=:interaction_id + + + + + + + select ec_interaction_id_sequence.nextval + + + + + + + select ec_interaction_id_sequence.nextval + + + + + + + select ec_user_ident_id_sequence.nextval + + + + + Index: openacs-4/packages/ecommerce/www/admin/customer-service/interaction-add-3.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/interaction-add-3.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/customer-service/interaction-add-3.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,146 @@ + + + + + + select count(*) from ec_customer_service_actions where action_id=:action_id + + + + + + + select i.user_identification_id as c_user_identification_id, a.interaction_id + from ec_customer_service_actions a, ec_customer_serv_interactions i + where i.interaction_id=a.interaction_id + and a.action_id=:action_id + + + + + + + select u.user_id as issue_user_id, u.user_identification_id as issue_user_identification_id + from ec_user_identification u, ec_customer_service_issues i + where u.user_identification_id = i.user_identification_id + and i.issue_id=:issue_id + + + + + + + select first_names || ' ' || last_name from cc_users where user_id=:issue_user_id + + + + + + + select user_id from ec_user_identification where user_identification_id=:c_user_identification_id + + + + + + + select first_names || ' ' || last_name from cc_users where user_id=:issue_user_id + + + + + + + select user_id as order_user_id from ec_orders where order_id=:order_id + + + + + + + select first_names || ' ' || last_name from cc_users where user_id=:order_user_id + + + + + + + select user_id from ec_user_identification where user_identification_id=:c_user_identification_id + + + + + + + select user_identification_id as uiid_to_insert from ec_user_identification where user_id=:d_user_id + + + + + + + insert into ec_user_identification + (user_identification_id, user_id, email, first_names, last_name, postal_code, other_id_info) + values + (:uiid_to_insert, :user_id_to_insert, :email,:first_names,:last_name,:postal_code,:other_id_info) + + + + + + + + insert into ec_customer_serv_interactions + (interaction_id, customer_service_rep, user_identification_id, interaction_date, interaction_originator, interaction_type) + values + (:interaction_id, :customer_service_rep, :uiid_to_insert, $date_string, :interaction_originator, [ec_decode $interaction_type "other" ":interaction_type_other" ":interaction_type"]) + + + + + + + + insert into ec_customer_service_issues + (issue_id, user_identification_id, order_id, open_date, close_date, closed_by) + values + (:issue_id, :uiid_to_insert, :order_id, $date_string, [ec_decode $close_issue_p "t" ":date_string" "''"], [ec_decode $close_issue_p "t" ":customer_service_rep" "''"]) + + + + + + + + insert into ec_cs_issue_type_map + (issue_id, issue_type) + values + (:issue_id, :issue_type) + + + + + + + + insert into ec_customer_service_actions +(action_id, issue_id, interaction_id, action_details, follow_up_required) +values +(:action_id, :issue_id, :interaction_id, :action_details,:follow_up_required) + + + + + + + + insert into ec_cs_action_info_used_map + (action_id, info_used) + values + (:action_id, :info_used) + + + + + + Index: openacs-4/packages/ecommerce/www/admin/customer-service/interaction-add.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/interaction-add.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/customer-service/interaction-add.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,11 @@ + + + + + + select first_names || ' ' || last_name from cc_users where user_id=:customer_service_rep + + + + + Index: openacs-4/packages/ecommerce/www/admin/customer-service/interaction-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/interaction-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/customer-service/interaction-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,13 @@ + + + + oracle8.1.6 + + + + select user_identification_id, customer_service_rep, to_char(interaction_date,'YYYY-MM-DD HH24:MI:SS') as full_interaction_date, interaction_originator, interaction_type, interaction_headers from ec_customer_serv_interactions where interaction_id=:interaction_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/customer-service/interaction-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/interaction-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/customer-service/interaction-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,13 @@ + + + + postgresql7.1 + + + + select user_identification_id, customer_service_rep, to_char(interaction_date,'YYYY-MM-DD HH24:MI:SS') as full_interaction_date, interaction_originator, interaction_type, interaction_headers from ec_customer_serv_interactions where interaction_id=:interaction_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/customer-service/interaction-summary.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/interaction-summary.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/customer-service/interaction-summary.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,11 @@ + + + + + + select first_names || ' ' || last_name from cc_users where user_id=:user_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/customer-service/interactions.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/interactions.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/customer-service/interactions.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,18 @@ + + + + + + select unique interaction_originator from ec_customer_serv_interactions + + + + + + + select picklist_item from ec_picklist_items where picklist_name='interaction_type' order by sort_key + + + + + Index: openacs-4/packages/ecommerce/www/admin/customer-service/issue-edit-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/issue-edit-2.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/customer-service/issue-edit-2.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,18 @@ + + + + + + delete from ec_cs_issue_type_map where issue_id=:issue_id + + + + + + + insert into ec_cs_issue_type_map (issue_id, issue_type) values (:issue_id,:issue_type) + + + + + Index: openacs-4/packages/ecommerce/www/admin/customer-service/issue-edit.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/issue-edit.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/customer-service/issue-edit.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,18 @@ + + + + + + select issue_type from ec_cs_issue_type_map where issue_id=:issue_id + + + + + + + select first_names || ' ' || last_name from cc_users where user_id=:customer_service_rep + + + + + Index: openacs-4/packages/ecommerce/www/admin/customer-service/issue-open-or-close-2-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/issue-open-or-close-2-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/customer-service/issue-open-or-close-2-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,13 @@ + + + + oracle8.1.6 + + + + update ec_customer_service_issues set close_date=sysdate, closed_by=:customer_service_rep where issue_id=:issue_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/customer-service/issue-open-or-close-2-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/issue-open-or-close-2-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/customer-service/issue-open-or-close-2-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,13 @@ + + + + postgresql7.1 + + + + update ec_customer_service_issues set close_date=current_timestamp, closed_by=:customer_service_rep where issue_id=:issue_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/customer-service/issue-open-or-close-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/issue-open-or-close-2.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/customer-service/issue-open-or-close-2.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,11 @@ + + + + + + update ec_customer_service_issues set close_date=null where issue_id=:issue_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/customer-service/issue-open-or-close.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/issue-open-or-close.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/customer-service/issue-open-or-close.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,11 @@ + + + + + + select first_names || ' ' || last_name from cc_users where user_id=:customer_service_rep + + + + + Index: openacs-4/packages/ecommerce/www/admin/customer-service/issue-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/issue-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/customer-service/issue-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,16 @@ + + + + oracle8.1.6 + + + + select i.user_identification_id, i.order_id, i.closed_by, i.deleted_p, i.open_date, i.close_date, to_char(i.open_date,'YYYY-MM-DD HH24:MI:SS') as full_open_date, to_char(i.close_date,'YYYY-MM-DD HH24:MI:SS') as full_close_date, u.first_names || ' ' || u.last_name as closed_rep_name +from ec_customer_service_issues i, cc_users u +where i.closed_by=u.user_id(+) +and issue_id=:issue_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/customer-service/issue-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/issue-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/customer-service/issue-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,16 @@ + + + + postgresql7.1 + + + +select i.user_identification_id, i.order_id, i.closed_by, i.deleted_p, i.open_date, i.close_date, to_char(i.open_date,'YYYY-MM-DD HH24:MI:SS') as full_open_date, to_char(i.close_date,'YYYY-MM-DD HH24:MI:SS') as full_close_date, u.first_names || ' ' || u.last_name as closed_rep_name +from ec_customer_service_issues i + left join cc_users u on (i.closed_by=u.user_id) +where issue_id=:issue_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/customer-service/issue.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/issue.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/customer-service/issue.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,11 @@ + + + + + + select issue_type from ec_cs_issue_type_map where issue_id=:issue_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/customer-service/issues.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/issues.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/customer-service/issues.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,11 @@ + + + + + + select picklist_item from ec_picklist_items where picklist_name='issue_type' order by sort_key + + + + + Index: openacs-4/packages/ecommerce/www/admin/customer-service/picklist-item-add-2-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/picklist-item-add-2-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/customer-service/picklist-item-add-2-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,16 @@ + + + + oracle8.1.6 + + + + insert into ec_picklist_items +(picklist_item_id, picklist_item, picklist_name, sort_key, last_modified, last_modifying_user, modified_ip_address) +values +(:picklist_item_id, :picklist_item, :picklist_name, (:prev_sort_key + :next_sort_key)/2, sysdate, :user_id,:address) + + + + + Index: openacs-4/packages/ecommerce/www/admin/customer-service/picklist-item-add-2-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/picklist-item-add-2-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/customer-service/picklist-item-add-2-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,16 @@ + + + + postgresql7.1 + + + + insert into ec_picklist_items +(picklist_item_id, picklist_item, picklist_name, sort_key, last_modified, last_modifying_user, modified_ip_address) +values +(:picklist_item_id, :picklist_item, :picklist_name, (:prev_sort_key + :next_sort_key)/2, current_timestamp, :user_id,:address) + + + + + Index: openacs-4/packages/ecommerce/www/admin/customer-service/picklist-item-add-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/picklist-item-add-2.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/customer-service/picklist-item-add-2.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,22 @@ + + + + + + select picklist_item_id from ec_picklist_items +where picklist_item_id=:picklist_item_id + + + + + + + select count(*) +from ec_picklist_items +where picklist_name=:picklist_name +and sort_key = (:prev_sort_key + :next_sort_key)/2 + + + + + Index: openacs-4/packages/ecommerce/www/admin/customer-service/picklist-item-add-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/Attic/picklist-item-add-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/customer-service/picklist-item-add-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,13 @@ + + + + oracle8.1.6 + + + + select ec_picklist_item_id_sequence.nextval from dual + + + + + Index: openacs-4/packages/ecommerce/www/admin/customer-service/picklist-item-add-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/Attic/picklist-item-add-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/customer-service/picklist-item-add-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,13 @@ + + + + postgresql7.1 + + + + select ec_picklist_item_id_sequence.nextval + + + + + Index: openacs-4/packages/ecommerce/www/admin/customer-service/picklist-item-add.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/picklist-item-add.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/customer-service/picklist-item-add.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,14 @@ + + + + + + select count(*) +from ec_picklist_items +where picklist_name=:picklist_name +and sort_key = (:prev_sort_key + :next_sort_key)/2 + + + + + Index: openacs-4/packages/ecommerce/www/admin/customer-service/picklist-item-delete-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/picklist-item-delete-2.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/customer-service/picklist-item-delete-2.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,11 @@ + + + + + + delete from ec_picklist_items where picklist_item_id=:picklist_item_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/customer-service/picklist-item-swap.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/picklist-item-swap.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/customer-service/picklist-item-swap.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,32 @@ + + + + + + select count(*) from ec_picklist_items where picklist_item_id=:picklist_item_id and sort_key=:sort_key + + + + + + + select count(*) from ec_picklist_items where picklist_item_id=:next_picklist_item_id and sort_key=:next_sort_key + + + + + + + update ec_picklist_items set sort_key=:next_sort_key where picklist_item_id=:picklist_item_id + + + + + + + update ec_picklist_items set sort_key=:sort_key where picklist_item_id=:next_picklist_item_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/customer-service/picklist-row-delete-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/picklist-row-delete-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/customer-service/picklist-row-delete-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,13 @@ + + + + oracle8.1.6 + + + + delete from $table_name where rowid=:rowid + + + + + Index: openacs-4/packages/ecommerce/www/admin/customer-service/picklist-row-delete-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/picklist-row-delete-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/customer-service/picklist-row-delete-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,13 @@ + + + + postgresql7.1 + + + + delete from $table_name where oid as rowid=:rowid + + + + + Index: openacs-4/packages/ecommerce/www/admin/customer-service/picklist-value-add.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/picklist-value-add.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/customer-service/picklist-value-add.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,15 @@ + + + + + + insert into $table_name +($col_to_insert) +values +(:val_to_insert) + + + + + + Index: openacs-4/packages/ecommerce/www/admin/customer-service/spam-2-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/spam-2-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/customer-service/spam-2-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,16 @@ + + + + oracle8.1.6 + + + + +select ec_spam_id_sequence.nextval + from dual + + + + + + Index: openacs-4/packages/ecommerce/www/admin/customer-service/spam-2-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/spam-2-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/customer-service/spam-2-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,16 @@ + + + + postgresql7.1 + + + + +select ec_spam_id_sequence.nextval + + + + + + + Index: openacs-4/packages/ecommerce/www/admin/customer-service/spam-3-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/spam-3-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/customer-service/spam-3-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,24 @@ + + + + oracle8.1.6 + + + + + insert into ec_gift_certificates + (gift_certificate_id, user_id, amount, + expires, + issue_date, issued_by, gift_certificate_state, + last_modified, last_modifying_user, modified_ip_address) + values + (ec_gift_cert_id_sequence.nextval, :user_id, :amount, + $expires_to_insert, + sysdate, :customer_service_rep, 'authorized', + sysdate, :customer_service_rep, '[ns_conn peeraddr]') + + + + + + Index: openacs-4/packages/ecommerce/www/admin/customer-service/spam-3-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/spam-3-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/customer-service/spam-3-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,24 @@ + + + + postgresql7.1 + + + + + insert into ec_gift_certificates + (gift_certificate_id, user_id, amount, + expires, + issue_date, issued_by, gift_certificate_state, + last_modified, last_modifying_user, modified_ip_address) + values + (ec_gift_cert_id_sequence.nextval, :user_id, :amount, + $expires_to_insert, + current_timestamp, :customer_service_rep, 'authorized', + current_timestamp, :customer_service_rep, '[ns_conn peeraddr]') + + + + + + Index: openacs-4/packages/ecommerce/www/admin/customer-service/spam-3.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/spam-3.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/customer-service/spam-3.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,30 @@ + + + + + + select count(*) from ec_spam_log where spam_id=:spam_id + + + + + + + + insert into ec_spam_log + (spam_id, spam_text, mailing_list_category_id, + mailing_list_subcategory_id, mailing_list_subsubcategory_id, + user_class_id, product_id, + last_visit_start_date, last_visit_end_date) + values + (:spam_id, :message, :mailing_list_category_id, + :mailing_list_subcategory_id, :mailing_list_subsubcategory_id, + :user_class_id, :product_id, + to_date(:start_date,'YYYY-MM-DD HH24:MI:SS'), + to_date(:end_date,'YYYY-MM-DD HH24:MI:SS')) + + + + + + Index: openacs-4/packages/ecommerce/www/admin/customer-service/spam-log.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/spam-log.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/customer-service/spam-log.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,18 @@ + + + + + + select user_class_name from ec_user_classes where user_class_id=:user_class_id + + + + + + + select product_name from ec_products where product_id=:product_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/customer-service/statistics.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/statistics.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/customer-service/statistics.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,18 @@ + + + + + + select picklist_item from ec_picklist_items where picklist_name='issue_type' order by sort_key + + + + + + + select picklist_item from ec_picklist_items where picklist_name='info_used' order by sort_key + + + + + Index: openacs-4/packages/ecommerce/www/admin/customer-service/user-identification-edit.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/user-identification-edit.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/customer-service/user-identification-edit.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,17 @@ + + + + + + update ec_user_identification +set first_names=:first_names, +last_name=:last_name, +email=:email, +postal_code=:postal_code, +other_id_info=:other_id_info +where user_identification_id=:user_identification_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/customer-service/user-identification-match-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/user-identification-match-2.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/customer-service/user-identification-match-2.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,11 @@ + + + + + + update ec_user_identification set user_id=:d_user_id where user_identification_id=:user_identification_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/customer-service/user-identification.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/user-identification.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/customer-service/user-identification.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,18 @@ + + + + + + select * from ec_user_identification where user_identification_id=:user_identification_id + + + + + + + select first_names as d_first_names, last_name as d_last_name, user_id as d_user_id from cc_users where upper(email) = :email + + + + + Index: openacs-4/packages/ecommerce/www/admin/email-templates/add-2-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/email-templates/add-2-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/email-templates/add-2-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,16 @@ + + + + oracle8.1.6 + + + + insert into ec_email_templates +(email_template_id, title, subject, message, variables, when_sent, issue_type_list, last_modified, last_modifying_user, modified_ip_address) +values +(ec_email_template_id_sequence.nextval, :title, :subject, :message, :variables, :when_sent, :issue_type, sysdate, :user_id, '[DoubleApos [ns_conn peeraddr]]') + + + + + Index: openacs-4/packages/ecommerce/www/admin/email-templates/add-2-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/email-templates/add-2-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/email-templates/add-2-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,16 @@ + + + + postgresql7.1 + + + + insert into ec_email_templates +(email_template_id, title, subject, message, variables, when_sent, issue_type_list, last_modified, last_modifying_user, modified_ip_address) +values +(ec_email_template_id_sequence.nextval, :title, :subject, :message, :variables, :when_sent, :issue_type, current_timestamp, :user_id, '[DoubleApos [ns_conn peeraddr]]') + + + + + Index: openacs-4/packages/ecommerce/www/admin/email-templates/edit-2-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/email-templates/edit-2-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/email-templates/edit-2-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,24 @@ + + + + oracle8.1.6 + + + + + update ec_email_templates + set title=:title, + subject=:subject, + message=:message, + variables=:variables, + when_sent=:when_sent, + issue_type_list=:issue_type, + last_modified=sysdate, + last_modifying_user=:user_id, + modified_ip_address='[DoubleApos [ns_conn peeraddr]]' + where email_template_id=:email_template_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/email-templates/edit-2-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/email-templates/edit-2-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/email-templates/edit-2-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,24 @@ + + + + postgresql7.1 + + + + + update ec_email_templates + set title=:title, + subject=:subject, + message=:message, + variables=:variables, + when_sent=:when_sent, + issue_type_list=:issue_type, + last_modified=current_timestamp, + last_modifying_user=:user_id, + modified_ip_address='[DoubleApos [ns_conn peeraddr]]' + where email_template_id=:email_template_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/email-templates/edit.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/email-templates/edit.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/email-templates/edit.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,11 @@ + + + + + + select * from ec_email_templates where email_template_id=:email_template_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/orders/address-add-3-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/orders/Attic/address-add-3-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/orders/address-add-3-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,13 @@ + + + + oracle8.1.6 + + + + select ec_address_id_sequence.nextval from dual + + + + + Index: openacs-4/packages/ecommerce/www/admin/orders/address-add-3-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/orders/Attic/address-add-3-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/orders/address-add-3-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,13 @@ + + + + postgresql7.1 + + + + select ec_address_id_sequence.nextval + + + + + Index: openacs-4/packages/ecommerce/www/admin/orders/address-add-3.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/orders/address-add-3.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/orders/address-add-3.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,29 @@ + + + + + + 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 + + + + + Index: openacs-4/packages/ecommerce/www/admin/orders/address-add.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/orders/address-add.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/orders/address-add.xql 10 Jul 2001 20:33:53 -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/ecommerce/www/admin/orders/by-order-state-and-time-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/orders/by-order-state-and-time-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/orders/by-order-state-and-time-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,20 @@ + + + + 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.user_id=u.user_id(+) +and o.order_id=i.order_id +$confirmed_query_bit $order_state_query_bit +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_by_clause + + + + + + Index: openacs-4/packages/ecommerce/www/admin/orders/by-order-state-and-time.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/orders/Attic/by-order-state-and-time.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/orders/by-order-state-and-time.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,18 @@ + + + + + +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 + JOIN ec_items i using (order_id) + LEFT JOIN cc_users u on (o.user_id=u.user_id) +$confirmed_query_bit $order_state_query_bit +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_by_clause + + + + + + Index: openacs-4/packages/ecommerce/www/admin/orders/comments-edit.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/orders/comments-edit.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/orders/comments-edit.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,11 @@ + + + + + + update ec_orders set cs_comments=:cs_comments where order_id=:order_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/orders/comments.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/orders/comments.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/orders/comments.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,11 @@ + + + + + + select cs_comments from ec_orders where order_id=:order_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/orders/creditcard-add-3-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/orders/Attic/creditcard-add-3-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/orders/creditcard-add-3-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,13 @@ + + + + oracle8.1.6 + + + + select ec_creditcard_id_sequence.nextval from dual + + + + + Index: openacs-4/packages/ecommerce/www/admin/orders/creditcard-add-3-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/orders/Attic/creditcard-add-3-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/orders/creditcard-add-3-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,13 @@ + + + + postgresql7.1 + + + + select ec_creditcard_id_sequence.nextval + + + + + Index: openacs-4/packages/ecommerce/www/admin/orders/creditcard-add-3.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/orders/creditcard-add-3.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/orders/creditcard-add-3.xql 10 Jul 2001 20:33:53 -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_zip_code) + values + (:creditcard_id, :user_id, :creditcard_number, :creditcard_last_four, :creditcard_type, :creditcard_expire, :billing_zip_code) + + + + + + + + update ec_orders set creditcard_id=:creditcard_id where order_id=:order_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/orders/creditcard-add.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/orders/creditcard-add.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/orders/creditcard-add.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,11 @@ + + + + + + select zip_code from ec_addresses a, ec_orders o where a.address_id=o.shipping_address and order_id=:order_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/orders/fulfill-2-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/orders/Attic/fulfill-2-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/orders/fulfill-2-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,13 @@ + + + + oracle8.1.6 + + + + select ec_shipment_id_sequence.nextval from dual + + + + + Index: openacs-4/packages/ecommerce/www/admin/orders/fulfill-2-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/orders/Attic/fulfill-2-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/orders/fulfill-2-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,13 @@ + + + + postgresql7.1 + + + + select ec_shipment_id_sequence.nextval + + + + + Index: openacs-4/packages/ecommerce/www/admin/orders/fulfill-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/orders/fulfill-2.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/orders/fulfill-2.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,22 @@ + + + + + + +select shipping_method + from ec_orders + where order_id=:order_id + + + + + + + + select shipping_address from ec_orders where order_id=:order_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/orders/fulfill-3-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/orders/fulfill-3-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/orders/fulfill-3-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,125 @@ + + + + oracle8.1.6 + + + + select nvl(sum(price_charged),0) from ec_items where item_id in ([join $item_id_vars ", "]) + + + + + + + select nvl(sum(shipping_charged),0) from ec_items where item_id in ([join $item_id_vars ", "]) + + + + + + + select ec_tax(:total_price_of_items, :total_shipping_of_items, :order_id) from dual + + + + + + + insert into ec_shipments + (shipment_id, order_id, shipment_date, expected_arrival_date, carrier, tracking_number, shippable_p, last_modified, last_modifying_user, modified_ip_address) + values + (:shipment_id, :order_id, to_date(:shipment_date, 'YYYY-MM-DD HH12:MI:SSAM'), to_date(:expected_arrival_date, 'YYYY-MM-DD HH12:MI:SSAM'), :carrier, :tracking_number, :shippable_p_tf, sysdate, :customer_service_rep, :peeraddr) + + + + + + + + select ec_shipment_cost(:shipment_id) from dual + + + + + + + select ec_order_cost(:order_id) from dual + + + + + + + update ec_financial_transactions set shipment_id=:shipment_id, to_be_captured_p='t', to_be_captured_date=sysdate where transaction_id=:transaction_id + + + + + + + insert into ec_problems_log + (problem_id, problem_date, problem_details, order_id) + values + (ec_problem_id_sequence.nextval, sysdate, :problem_details, :order_id) + + + + + + + + update ec_financial_transactions set marked_date=sysdate where transaction_id=:transaction_id + + + + + + + insert into ec_financial_transactions + (transaction_id, order_id, shipment_id, transaction_amount, transaction_type, to_be_captured_p, inserted_date, to_be_captured_date) + values + (:transaction_id, :order_id, :shipment_id, :shipment_cost, 'charge','t',sysdate,sysdate) + + + + + + + + + insert into ec_problems_log + (problem_id, problem_date, problem_details, order_id) + values + (ec_problem_id_sequence.nextval, sysdate, :problem_details, :order_id) + + + + + + + + update ec_financial_transactions set authorized_date=sysdate where transaction_id=:transaction_id + + + + + + + insert into ec_problems_log + (problem_id, problem_date, problem_details, order_id) + values + (ec_problem_id_sequence.nextval, sysdate, :problem_details, :order_id) + + + + + + + + update ec_financial_transactions set marked_date=sysdate where transaction_id=:transaction_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/orders/fulfill-3-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/orders/fulfill-3-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/orders/fulfill-3-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,111 @@ + + + + postgresql7.1 + + + + select ec_tax(:total_price_of_items, :total_shipping_of_items, :order_id) + + + + + + + insert into ec_shipments + (shipment_id, order_id, shipment_date, expected_arrival_date, carrier, tracking_number, shippable_p, last_modified, last_modifying_user, modified_ip_address) + values + (:shipment_id, :order_id, to_date(:shipment_date, 'YYYY-MM-DD HH12:MI:SSAM'), to_date(:expected_arrival_date, 'YYYY-MM-DD HH12:MI:SSAM'), :carrier, :tracking_number, :shippable_p_tf, current_timestamp, :customer_service_rep, :peeraddr) + + + + + + + + select ec_shipment_cost(:shipment_id) + + + + + + + select ec_order_cost(:order_id) + + + + + + + update ec_financial_transactions set shipment_id=:shipment_id, to_be_captured_p='t', to_be_captured_date=current_timestamp where transaction_id=:transaction_id + + + + + + + insert into ec_problems_log + (problem_id, problem_date, problem_details, order_id) + values + (ec_problem_id_sequence.nextval, current_timestamp, :problem_details, :order_id) + + + + + + + + update ec_financial_transactions set marked_date=current_timestamp where transaction_id=:transaction_id + + + + + + + insert into ec_financial_transactions + (transaction_id, order_id, shipment_id, transaction_amount, transaction_type, to_be_captured_p, inserted_date, to_be_captured_date) + values + (:transaction_id, :order_id, :shipment_id, :shipment_cost, 'charge','t',current_timestamp,current_timestamp) + + + + + + + + + insert into ec_problems_log + (problem_id, problem_date, problem_details, order_id) + values + (ec_problem_id_sequence.nextval, current_timestamp, :problem_details, :order_id) + + + + + + + + update ec_financial_transactions set authorized_date=current_timestamp where transaction_id=:transaction_id + + + + + + + insert into ec_problems_log + (problem_id, problem_date, problem_details, order_id) + values + (ec_problem_id_sequence.nextval, current_timestamp, :problem_details, :order_id) + + + + + + + + update ec_financial_transactions set marked_date=current_timestamp where transaction_id=:transaction_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/orders/fulfill-3.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/orders/fulfill-3.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/orders/fulfill-3.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,92 @@ + + + + + + select count(*) from ec_shipments where shipment_id=:shipment_id + + + + + + + select shipping_method from ec_orders where order_id=:order_id + + + + + + + select shipping_method from ec_orders where order_id=:order_id + + + + + + + select coalesce(sum(price_charged),0) from ec_items where item_id in ([join $item_id_vars ", "]) + + + + + + + select count(*) from ec_shipments where order_id=:order_id + + + + + + + select coalesce(sum(shipping_charged),0) from ec_items where item_id in ([join $item_id_vars ", "]) + + + + + + + select $shipping_of_items + shipping_charged from ec_orders where order_id=:order_id + + + + + + + + update ec_items + set item_state='shipped', shipment_id=:shipment_id + where item_id in ([join $item_id_vars ", "]) + + + + + + + + select count(*) from ec_financial_transactions where order_id=:order_id and to_be_captured_p='t' + + + + + + + select max(transaction_id) from ec_financial_transactions where order_id=:order_id + + + + + + + select max(transaction_id) from ec_financial_transactions where order_id=:order_id + + + + + + + update ec_financial_transactions set failed_p='t' where transaction_id=:transaction_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/orders/fulfill.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/orders/fulfill.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/orders/fulfill.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,25 @@ + + + + + + +select user_id + from ec_orders + where order_id=:order_id + + + + + + + +select shipping_method + from ec_orders + where order_id=:order_id + + + + + + Index: openacs-4/packages/ecommerce/www/admin/orders/fulfillment-items-needed.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/orders/fulfillment-items-needed.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/orders/fulfillment-items-needed.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,20 @@ + + + + + + + select p.product_id, p.product_name, + i.color_choice, i.size_choice, i.style_choice, + count(*) as quantity + from ec_products p, ec_items_shippable i + where p.product_id=i.product_id + group by p.product_id, p.product_name, + i.color_choice, i.size_choice, i.style_choice + order by quantity desc + + + + + + Index: openacs-4/packages/ecommerce/www/admin/orders/fulfillment.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/orders/fulfillment.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/orders/fulfillment.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,17 @@ + + + + + + + select o.order_id, o.confirmed_date, o.order_state, o.shipping_method, + u.first_names, u.last_name, u.user_id + from ec_orders_shippable o, cc_users u + where o.user_id=u.user_id + order by o.shipping_method, o.order_state, o.order_id + + + + + + Index: openacs-4/packages/ecommerce/www/admin/orders/gift-certificate-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/orders/gift-certificate-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/orders/gift-certificate-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,21 @@ + + + + oracle8.1.6 + + + + +select c.*, i.first_names || ' ' || i.last_name as issuer, i.user_id as issuer_user_id, p.first_names || ' ' || p.last_name as purchaser, p.user_id as purchaser_user_id, gift_certificate_amount_left(c.gift_certificate_id) as amount_left, decode(sign(sysdate-expires),1,'t',0,'t','f') as expired_p, v.first_names as voided_by_first_names, v.last_name as voided_by_last_name, o.first_names || ' ' || o.last_name as owned_by +from ec_gift_certificates c, cc_users i, cc_users p, cc_users v, cc_users o +where c.issued_by=i.user_id(+) +and c.purchased_by=p.user_id(+) +and c.voided_by=v.user_id(+) +and c.user_id=o.user_id(+) +and c.gift_certificate_id=:gift_certificate_id + + + + + + Index: openacs-4/packages/ecommerce/www/admin/orders/gift-certificate-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/orders/gift-certificate-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/orders/gift-certificate-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,21 @@ + + + + postgresql7.1 + + + + +select c.*, i.first_names || ' ' || i.last_name as issuer, i.user_id as issuer_user_id, p.first_names || ' ' || p.last_name as purchaser, p.user_id as purchaser_user_id, gift_certificate_amount_left(c.gift_certificate_id) as amount_left, case when current_timestamp-expires >= 0 then 't' else 'f' end as expired_p, v.first_names as voided_by_first_names, v.last_name as voided_by_last_name, o.first_names || ' ' || o.last_name as owned_by +from ec_gift_certificates c + LEFT JOIN cc_users i on (c.issued_by=i.user_id) + LEFT JOIN cc_users p on (c.purchased_by=p.user_id) + LEFT JOIN cc_users v on (c.voided_by=v.user_id) + LEFT JOIN cc_users o on (c.user_id=o.user_id) +where c.gift_certificate_id=:gift_certificate_id + + + + + + Index: openacs-4/packages/ecommerce/www/admin/orders/gift-certificate-void-2-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/orders/gift-certificate-void-2-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/orders/gift-certificate-void-2-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,20 @@ + + + + oracle8.1.6 + + + + +update ec_gift_certificates +set gift_certificate_state='void', + voided_date=sysdate, + voided_by=:customer_service_rep, + reason_for_void=:reason_for_void +where gift_certificate_id=:gift_certificate_id + + + + + + Index: openacs-4/packages/ecommerce/www/admin/orders/gift-certificate-void-2-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/orders/gift-certificate-void-2-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/orders/gift-certificate-void-2-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,20 @@ + + + + postgresql7.1 + + + + +update ec_gift_certificates +set gift_certificate_state='void', + voided_date=current_timestamp, + voided_by=:customer_service_rep, + reason_for_void=:reason_for_void +where gift_certificate_id=:gift_certificate_id + + + + + + Index: openacs-4/packages/ecommerce/www/admin/orders/gift-certificates-issued.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/orders/gift-certificates-issued.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/orders/gift-certificates-issued.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,30 @@ + + + + + + +SELECT user_id as rep, first_names as rep_first_names, last_name as rep_last_name +FROM ec_customer_service_reps +ORDER BY last_name, first_names + + + + + + + + +SELECT g.gift_certificate_id, g.issue_date, g.amount, + g.issued_by, u.first_names, u.last_name, + g.user_id as issued_to, r.first_names as issued_to_first_names, r.last_name as issued_to_last_name +from ec_gift_certificates_issued g, cc_users u, cc_users r +where g.issued_by=u.user_id and g.user_id=r.user_id +$issue_date_query_bit $rep_query_bit +order by $order_by_clause + + + + + + Index: openacs-4/packages/ecommerce/www/admin/orders/gift-certificates.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/orders/gift-certificates.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/orders/gift-certificates.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,16 @@ + + + + + + select g.gift_certificate_id, g.issue_date, g.gift_certificate_state, g.recipient_email, g.purchased_by, g.amount, u.first_names, u.last_name +from ec_gift_certificates g, cc_users u +where g.purchased_by=u.user_id +$issue_date_query_bit $gift_certificate_state_query_bit +order by $order_by_clause + + + + + + Index: openacs-4/packages/ecommerce/www/admin/orders/index.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/orders/index.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/orders/index.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,78 @@ + + + + + + +select + sum(one_if_within_n_days(confirmed_date,1)) as n_o_in_last_24_hours, + sum(one_if_within_n_days(confirmed_date,7)) as n_o_in_last_7_days +from ec_orders_reportable + + + + + + + + +select + sum(one_if_within_n_days(issue_date,1)) as n_g_in_last_24_hours, + sum(one_if_within_n_days(issue_date,7)) as n_g_in_last_7_days +from ec_gift_certificates_purchased + + + + + + + + +select + sum(one_if_within_n_days(issue_date,1)) as n_gi_in_last_24_hours, + sum(one_if_within_n_days(issue_date,7)) as n_gi_in_last_7_days +from ec_gift_certificates_issued + + + + + + + + +select + sum(one_if_within_n_days(shipment_date,1)) as n_s_in_last_24_hours, + sum(one_if_within_n_days(shipment_date,7)) as n_s_in_last_7_days +from ec_shipments + + + + + + + + +select + sum(one_if_within_n_days(refund_date,1)) as n_r_in_last_24_hours, + sum(one_if_within_n_days(refund_date,7)) as n_r_in_last_7_days +from ec_refunds + + + + + + + + select count(*) from ec_orders_shippable where shipping_method='standard' + + + + + + + select count(*) from ec_orders_shippable where shipping_method='standard' + + + + + Index: openacs-4/packages/ecommerce/www/admin/orders/items-add-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/orders/items-add-2.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/orders/items-add-2.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,11 @@ + + + + + + select product_id, product_name from ec_products where $additional_query_part + + + + + Index: openacs-4/packages/ecommerce/www/admin/orders/items-add-3-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/orders/Attic/items-add-3-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/orders/items-add-3-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,13 @@ + + + + oracle8.1.6 + + + + select ec_item_id_sequence.nextval from dual + + + + + Index: openacs-4/packages/ecommerce/www/admin/orders/items-add-3-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/orders/Attic/items-add-3-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/orders/items-add-3-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,13 @@ + + + + postgresql7.1 + + + + select ec_item_id_sequence.nextval + + + + + Index: openacs-4/packages/ecommerce/www/admin/orders/items-add-3.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/orders/items-add-3.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/orders/items-add-3.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,11 @@ + + + + + + select user_id from ec_orders where order_id=:order_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/orders/items-add-4-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/orders/items-add-4-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/orders/items-add-4-oracle.xql 10 Jul 2001 20:33:53 -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/ecommerce/www/admin/orders/items-add-4-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/orders/items-add-4-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/orders/items-add-4-postgresql.xql 10 Jul 2001 20:33:53 -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/ecommerce/www/admin/orders/items-add-4.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/orders/items-add-4.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/orders/items-add-4.xql 10 Jul 2001 20:33:53 -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/ecommerce/www/admin/orders/items-return-2-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/orders/items-return-2-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/orders/items-return-2-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,13 @@ + + + + oracle8.1.6 + + + + select nvl(shipping_charged,0) - nvl(shipping_refunded,0) from ec_orders where order_id=:order_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/orders/items-return-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/orders/items-return-2.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/orders/items-return-2.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,18 @@ + + + + + + select count(*) from ec_refunds where refund_id=:refund_id + + + + + + + select coalesce(shipping_charged,0) - coalesce(shipping_refunded,0) from ec_orders where order_id=:order_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/orders/items-return-3-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/orders/items-return-3-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/orders/items-return-3-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,41 @@ + + + + oracle8.1.6 + + + + select ec_tax(:tax_price_to_refund,0,:order_id) from dual + + + + + + + select ec_tax(0,$shipping_to_refund($item_id),$order_id) 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 ec_tax(0,:base_shipping,:order_id) from dual + + + + + + + select ec_cash_amount_to_refund(:total_amount_to_refund,:order_id) from dual + + + + + Index: openacs-4/packages/ecommerce/www/admin/orders/items-return-3-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/orders/items-return-3-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/orders/items-return-3-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,34 @@ + + + + postgresql7.1 + + + + select ec_tax(:tax_price_to_refund,0,:order_id) + + + + + + + select ec_tax(0,$shipping_to_refund($item_id),$order_id) + + + + + + + select ec_tax(0,:base_shipping,:order_id) + + + + + + + select ec_cash_amount_to_refund(:total_amount_to_refund,:order_id) + + + + + Index: openacs-4/packages/ecommerce/www/admin/orders/items-return-3.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/orders/items-return-3.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/orders/items-return-3.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,32 @@ + + + + + + select count(*) from ec_refunds where refund_id=:refund_id + + + + + + + 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 creditcard_number from ec_orders o, ec_creditcards c where o.creditcard_id=c.creditcard_id and o.order_id=:order_id + + + + + + + select zip_code from ec_orders o, ec_addresses a where o.shipping_address=a.address_id and o.order_id=:order_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/orders/items-return-4-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/orders/items-return-4-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/orders/items-return-4-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,131 @@ + + + + oracle8.1.6 + + + + select creditcard_id_sequence.nextval from dual + + + + + + + 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 + + + + + + + select ec_transaction_id_sequence.nextval 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 gift_certificate_amount_left(:cert) 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) + + + + + + + + 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=:transaction_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/orders/items-return-4-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/orders/items-return-4-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/orders/items-return-4-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,117 @@ + + + + postgresql7.1 + + + + select creditcard_id_sequence.nextval + + + + + + + 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 ec_tax(:price_bind_variable,0,:order_id) + + + + + + + select ec_tax(0,:shipping_bind_variable,:order_id) + + + + + + + select ec_tax(0,:base_shipping_to_refund,:order_id) + + + + + + + select ec_transaction_id_sequence.nextval + + + + + + + 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', current_timestamp) + + + + + + + + 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 gift_certificate_amount_left(:cert) + + + + + + + insert into ec_gift_certificate_usage + (gift_certificate_id, order_id, amount_reinstated, reinstated_date) + values + (:cert, :order_id, :iteration_reinstate_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=:transaction_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/orders/items-return-4.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/orders/items-return-4.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/orders/items-return-4.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,105 @@ + + + + + + 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_zip_code) + values + (:creditcard_id, :user_id, :creditcard_number, :cc_thing, :creditcard_type,:expires,:billing_zip_code) + + + + + + + + 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 + + + + + + + + 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 + + + + + + + select coalesce(shipping_tax_charged,0) from ec_orders where order_id=:order_id + + + + + + + update ec_orders set shipping_refunded=:base_shipping_to_refund, shipping_tax_refunded=:base_shipping_tax_to_refund where order_id=:order_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/orders/items-return-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/orders/Attic/items-return-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/orders/items-return-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,13 @@ + + + + oracle8.1.6 + + + + select refund_id_sequence.nextval from dual + + + + + Index: openacs-4/packages/ecommerce/www/admin/orders/items-return-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/orders/Attic/items-return-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/orders/items-return-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,13 @@ + + + + postgresql7.1 + + + + select refund_id_sequence.nextval + + + + + Index: openacs-4/packages/ecommerce/www/admin/orders/items-return.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/orders/items-return.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/orders/items-return.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,11 @@ + + + + + + select count(*) from ec_items_refundable where order_id=:order_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/orders/items-void-2-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/orders/items-void-2-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/orders/items-void-2-oracle.xql 10 Jul 2001 20:33:53 -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/ecommerce/www/admin/orders/items-void-2-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/orders/items-void-2-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/orders/items-void-2-postgresql.xql 10 Jul 2001 20:33:53 -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/ecommerce/www/admin/orders/items-void-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/orders/items-void-2.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/orders/items-void-2.xql 10 Jul 2001 20:33:53 -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/ecommerce/www/admin/orders/items-void.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/orders/items-void.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/orders/items-void.xql 10 Jul 2001 20:33:53 -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/ecommerce/www/admin/orders/one-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/orders/one-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/orders/one-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,21 @@ + + + + oracle8.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 +from ec_orders o, cc_users u +where order_id=:order_id +and o.user_id = u.user_id(+) + + + + + + Index: openacs-4/packages/ecommerce/www/admin/orders/one.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/orders/one.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/orders/one.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,74 @@ + + + + + + +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 +from ec_orders o + LEFT JOIN cc_users u using (user_id) +where order_id=:order_id + + + + + + + + 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.settled_date, t.refunded_date, t.refund_settled_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/ecommerce/www/admin/orders/refunds.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/orders/refunds.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/orders/refunds.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,17 @@ + + + + + + select r.refund_id, r.refund_date, r.order_id, r.refund_amount, r.refunded_by, u.first_names, u.last_name, count(*) as n_items +from ec_refunds r, cc_users u, ec_items i +where r.refunded_by=u.user_id +and i.refund_id=r.refund_id +$refund_date_query_bit +group by r.refund_id, r.refund_date, r.order_id, r.refund_amount, r.refunded_by, u.first_names, u.last_name +order by $order_by_clause + + + + + Index: openacs-4/packages/ecommerce/www/admin/orders/revenue-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/orders/revenue-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/orders/revenue-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,66 @@ + + + + oracle8.1.6 + + + + select to_char(inserted_date,'YYYY') as transaction_year, to_char(inserted_date,'Q') as transaction_quarter, sum(case when transaction_type = 'charge' then transaction_amount else -1*transaction_amount end) as revenue +from ec_fin_transactions_reportable +group by to_char(inserted_date,'YYYY'), to_char(inserted_date,'Q') +order by to_char(inserted_date,'YYYY') || to_char(inserted_date,'Q') + + + + + + + select to_char(shipment_date,'YYYY') as shipment_year, +to_char(shipment_date,'Q') as shipment_quarter, +nvl(sum(bal_price_charged),0) as total_price_charged, +nvl(sum(bal_shipping_charged + case when mv.shipment_id = (select min(s2.shipment_id) from ec_shipments s2 where s2.order_id=mv.order_id) then (select nvl(o.shipping_charged else 0 end-nvl(o.shipping_refunded,0) from ec_orders o where o.order_id=mv.order_id),0)),0) as total_shipping_charged, +nvl(sum(bal_tax_charged + case when mv.shipment_id = (select min(s2.shipment_id) from ec_shipments s2 where s2.order_id=mv.order_id) then (select nvl(o.shipping_tax_charged else 0 end-nvl(o.shipping_tax_refunded,0) from ec_orders o where o.order_id=mv.order_id),0)),0) as total_tax_charged +from ec_items_money_view mv +group by to_char(shipment_date,'YYYY'), to_char(shipment_date,'Q') +order by to_char(shipment_date,'YYYY') || to_char(shipment_date,'Q') + + + + + + + select to_char(issue_date,'YYYY') as issue_year, +to_char(issue_date,'Q') as issue_quarter, +nvl(sum(amount),0) as amount +from ec_gift_certificates where gift_certificate_state in ('authorized_plus_avs','authorized_minus_avs') +group by to_char(issue_date,'YYYY'), to_char(issue_date,'Q') +order by to_char(issue_date,'YYYY') || to_char(issue_date,'Q') + + + + + + + select to_char(issue_date,'YYYY') as issue_year, +to_char(issue_date,'Q') as issue_quarter, +nvl(sum(amount),0) as amount +from ec_gift_certificates where gift_certificate_state in ('authorized_plus_avs','authorized_minus_avs') +group by to_char(issue_date,'YYYY'), to_char(issue_date,'Q') +order by to_char(issue_date,'YYYY') || to_char(issue_date,'Q') + + + + + + + select to_char(expires,'YYYY') as expires_year, +to_char(expires,'Q') as expires_quarter, +nvl(sum(gift_certificate_amount_left(gift_certificate_id)),0) + nvl(sum(ec_gift_cert_unshipped_amount(gift_certificate_id)),0) as amount_outstanding +from ec_gift_certificates_approved +group by to_char(expires,'YYYY'), to_char(expires,'Q') +order by to_char(expires,'YYYY') || to_char(expires,'Q') + + + + + Index: openacs-4/packages/ecommerce/www/admin/orders/revenue-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/orders/revenue-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/orders/revenue-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,66 @@ + + + + postgresql7.1 + + + + select to_char(inserted_date,'YYYY') as transaction_year, to_char(inserted_date,'Q') as transaction_quarter, sum(case when transaction_type = 'charge' then transaction_amount else -1*transaction_amount end) as revenue +from ec_fin_transactions_reportable +group by to_char(inserted_date,'YYYY'), to_char(inserted_date,'Q') +order by to_char(inserted_date,'YYYY') || to_char(inserted_date,'Q') + + + + + + + select to_char(shipment_date,'YYYY') as shipment_year, +to_char(shipment_date,'Q') as shipment_quarter, +coalesce(sum(bal_price_charged),0) as total_price_charged, +coalesce(sum(bal_shipping_charged + case when mv.shipment_id = (select min(s2.shipment_id) from ec_shipments s2 where s2.order_id=mv.order_id) then (select coalesce(o.shipping_charged else 0 end-coalesce(o.shipping_refunded,0) from ec_orders o where o.order_id=mv.order_id),0)),0) as total_shipping_charged, +coalesce(sum(bal_tax_charged + case when mv.shipment_id = (select min(s2.shipment_id) from ec_shipments s2 where s2.order_id=mv.order_id) then (select coalesce(o.shipping_tax_charged else 0 end-coalesce(o.shipping_tax_refunded,0) from ec_orders o where o.order_id=mv.order_id),0)),0) as total_tax_charged +from ec_items_money_view mv +group by to_char(shipment_date,'YYYY'), to_char(shipment_date,'Q') +order by to_char(shipment_date,'YYYY') || to_char(shipment_date,'Q') + + + + + + + select to_char(issue_date,'YYYY') as issue_year, +to_char(issue_date,'Q') as issue_quarter, +coalesce(sum(amount),0) as amount +from ec_gift_certificates where gift_certificate_state in ('authorized_plus_avs','authorized_minus_avs') +group by to_char(issue_date,'YYYY'), to_char(issue_date,'Q') +order by to_char(issue_date,'YYYY') || to_char(issue_date,'Q') + + + + + + + select to_char(issue_date,'YYYY') as issue_year, +to_char(issue_date,'Q') as issue_quarter, +coalesce(sum(amount),0) as amount +from ec_gift_certificates where gift_certificate_state in ('authorized_plus_avs','authorized_minus_avs') +group by to_char(issue_date,'YYYY'), to_char(issue_date,'Q') +order by to_char(issue_date,'YYYY') || to_char(issue_date,'Q') + + + + + + + select to_char(expires,'YYYY') as expires_year, +to_char(expires,'Q') as expires_quarter, +coalesce(sum(gift_certificate_amount_left(gift_certificate_id)),0) + coalesce(sum(ec_gift_cert_unshipped_amount(gift_certificate_id)),0) as amount_outstanding +from ec_gift_certificates_approved +group by to_char(expires,'YYYY'), to_char(expires,'Q') +order by to_char(expires,'YYYY') || to_char(expires,'Q') + + + + + Index: openacs-4/packages/ecommerce/www/admin/orders/shipments-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/orders/shipments-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/orders/shipments-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,22 @@ + + + + oracle8.1.6 + + + + +select s.shipment_id, + s.shipment_date, + s.order_id, + s.carrier, + case when nvl((select count(*) from ec_items where order_id=s.order_id) = 0) then nvl((select count(*) from ec_items where shipment_id=s.shipment_id) else 0 end,'Full','Partial') as full_or_partial, + nvl((select count(*) from ec_items where shipment_id=s.shipment_id),0) as n_items +from ec_shipments s +$where_clause +order by $order_by_clause + + + + + Index: openacs-4/packages/ecommerce/www/admin/orders/shipments.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/orders/Attic/shipments.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/orders/shipments.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,27 @@ + + + + + + select unique carrier from ec_shipments where carrier is not null order by carrier + + + + + + + +select s.shipment_id, + s.shipment_date, + s.order_id, + s.carrier, + case when coalesce((select count(*) from ec_items where order_id=s.order_id) = 0) then coalesce((select count(*) from ec_items where shipment_id=s.shipment_id) else 0 end,'Full','Partial') as full_or_partial, + coalesce((select count(*) from ec_items where shipment_id=s.shipment_id),0) as n_items +from ec_shipments s +$where_clause +order by $order_by_clause + + + + + Index: openacs-4/packages/ecommerce/www/admin/orders/track-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/orders/track-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/orders/track-oracle.xql 10 Jul 2001 20:33:53 -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/ecommerce/www/admin/orders/track-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/orders/track-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/orders/track-postgresql.xql 10 Jul 2001 20:33:53 -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/ecommerce/www/admin/orders/void-2-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/orders/void-2-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/orders/void-2-oracle.xql 10 Jul 2001 20:33:53 -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/ecommerce/www/admin/orders/void-2-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/orders/void-2-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/orders/void-2-postgresql.xql 10 Jul 2001 20:33:53 -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/ecommerce/www/admin/orders/void-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/orders/void-2.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/orders/void-2.xql 10 Jul 2001 20:33:53 -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/ecommerce/www/admin/orders/void.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/orders/void.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/orders/void.xql 10 Jul 2001 20:33:53 -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') + + + + + Index: openacs-4/packages/ecommerce/www/admin/problems/index-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/problems/index-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/problems/index-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,21 @@ + + + + oracle8.1.6 + + + + + select + l.*, + u.first_names || ' ' || u.last_name as user_name + from ec_problems_log l, + cc_users u + where l.resolved_by = u.user_id(+) + $sql_clause + order by problem_date asc + + + + + Index: openacs-4/packages/ecommerce/www/admin/problems/index.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/problems/index.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/problems/index.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,33 @@ + + + + + + select count(*) from ec_problems_log + + + + + + + select count(*) from ec_problems_log where resolved_date is null + + + + + + + + select + l.*, + u.first_names || ' ' || u.last_name as user_name + from ec_problems_log l + LEFT JOIN cc_users u on (l.resolved_by = u.user_id) + $sql_clause + order by problem_date asc + + + + + + Index: openacs-4/packages/ecommerce/www/admin/problems/resolve-2-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/problems/resolve-2-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/problems/resolve-2-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,13 @@ + + + + oracle8.1.6 + + + + update ec_problems_log set resolved_by=:user_id, resolved_date=sysdate where problem_id = :problem_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/problems/resolve-2-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/problems/resolve-2-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/problems/resolve-2-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,13 @@ + + + + postgresql7.1 + + + + update ec_problems_log set resolved_by=:user_id, resolved_date=current_timestamp where problem_id = :problem_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/problems/resolve.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/problems/resolve.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/problems/resolve.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,11 @@ + + + + + + select problem_details, problem_date from ec_problems_log where problem_id = :problem_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/add-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/add-2.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/add-2.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,30 @@ + + + + + + + select column_type, field_identifier, field_name + from ec_custom_product_fields + where column_type in ('date','integer') + and active_p='t' + + + + + + + + select field_identifier from ec_custom_product_fields where active_p='t' + + + + + + + select user_class_id from ec_user_classes + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/add-3.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/add-3.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/add-3.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,47 @@ + + + + + + select user_class_id, user_class_name from ec_user_classes order by user_class_name + + + + + + + + select field_identifier, field_name, column_type + from ec_custom_product_fields + where active_p = 't' + + + + + + + + select template_name from ec_templates where template_id=:template_id + + + + + + + + select field_identifier, field_name, column_type + from ec_custom_product_fields + where active_p = 't' + + + + + + + + select user_class_id from ec_user_classes + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/add-4-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/add-4-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/add-4-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,44 @@ + + + + oracle8.1.6 + + + + + begin + :1 := ec_product.new(product_id => :product_id, + object_type => 'ec_product', + creation_date => sysdate, + creation_user => :user_id, + creation_ip => :peeraddr, + context_id => :context_id, + product_name => :product_name, + price => :price, + sku => :sku, + one_line_description => :one_line_description, + detailed_description => :detailed_description, + search_keywords => :search_keywords, + present_p => :present_p, + stock_status => :stock_status, + dirname => :dirname, + available_date => to_date(:available_date, 'YYYY-MM-DD'), + color_list => :color_list, + size_list => :size_list, + style_list => :style_list, + email_on_purchase_list => :email_on_purchase_list, + url => :url, + no_shipping_avail_p => :no_shipping_avail_p, + shipping => :shipping, + shipping_additional => :shipping_additional, + weight => :weight, + active_p => 't', + template_id => :template_id + ); + end; + + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/add-4-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/add-4-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/add-4-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,46 @@ + + + + postgresql7.1 + + + + FIX ME PLSQL +FIX ME PLSQL + + begin + :1 := ec_product__new(product_id => :product_id, + object_type => 'ec_product', + creation_date => current_timestamp, + creation_user => :user_id, + creation_ip => :peeraddr, + context_id => :context_id, + product_name => :product_name, + price => :price, + sku => :sku, + one_line_description => :one_line_description, + detailed_description => :detailed_description, + search_keywords => :search_keywords, + present_p => :present_p, + stock_status => :stock_status, + dirname => :dirname, + available_date => to_date(:available_date, 'YYYY-MM-DD'), + color_list => :color_list, + size_list => :size_list, + style_list => :style_list, + email_on_purchase_list => :email_on_purchase_list, + url => :url, + no_shipping_avail_p => :no_shipping_avail_p, + shipping => :shipping, + shipping_additional => :shipping_additional, + weight => :weight, + active_p => 't', + template_id => :template_id + ); + end; + + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/add-4.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/add-4.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/add-4.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,82 @@ + + + + + + select count(*) from ec_products where product_id=:product_id + + + + + + + select user_class_id from ec_user_classes + + + + + + + + select field_identifier + from ec_custom_product_fields + where active_p='t' + + + + + + + + + insert into ec_custom_product_field_values + ([join $custom_columns_to_insert ", "], $audit_fields) + values + ([join $custom_column_values_to_insert ","], $audit_info) + + + + + + + + + insert into ec_category_product_map (product_id, category_id, $audit_fields) + values + (:product_id, :category_id, $audit_info) + + + + + + + + + insert into ec_subcategory_product_map ( + product_id, subcategory_id, $audit_fields) values ( + :product_id, :subcategory_id, $audit_info) + + + + + + + + insert into ec_subsubcategory_product_map ( + product_id, subsubcategory_id, $audit_fields) values ( + :product_id, :subsubcategory_id, $audit_info) + + + + + + + + insert into ec_product_user_class_prices ( + product_id, user_class_id, price, $audit_fields) values ( + :product_id, :user_class_id, :uc_price, $audit_info) + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/add.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/add.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/add.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,42 @@ + + + + + + select count(*) from ec_user_classes + + + + + + + + select user_class_id, + user_class_name + from ec_user_classes + order by user_class_name + + + + + + + select count(*) from ec_custom_product_fields where active_p='t' + + + + + + + + select field_identifier, + field_name, + default_value, + column_type + from ec_custom_product_fields + where active_p='t' order by creation_date + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/by-category-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/by-category-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/by-category-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,23 @@ + + + + oracle8.1.6 + + + + +select cats.category_id, cats.sort_key, cats.category_name, count(cat_view.product_id) as n_products, sum(cat_view.n_sold) as total_sold_in_category +from + ec_categories cats, + (select map.product_id, map.category_id, count(i.item_id) as n_sold + from ec_category_product_map map, ec_items_reportable i + where map.product_id = i.product_id(+) + group by map.product_id, map.category_id) cat_view +where cats.category_id = cat_view.category_id(+) +group by cats.category_id, cats.sort_key, cats.category_name +order by cats.sort_key + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/by-category.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/by-category.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/by-category.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,23 @@ + + + + + + +select cats.category_id, cats.sort_key, cats.category_name, count(cat_view.product_id) as n_products, sum(cat_view.n_sold) as total_sold_in_category +from + ec_categories cats + LEFT JOIN + (select map.product_id, map.category_id, count(i.item_id) as n_sold + from ec_category_product_map map + LEFT JOIN ec_items_reportable i using (product_id) + group by map.product_id, map.category_id) cat_view + using (category_id) +group by cats.category_id, cats.sort_key, cats.category_name +order by cats.sort_key + + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/categories-upload-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/categories-upload-2.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/categories-upload-2.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,22 @@ + + + + + + + select c.category_id, c.category_name, s.subcategory_id, + s.subcategory_name from ec_subcategories s, ec_categories c + where c.category_id = s.category_id + and upper(:category) like upper(subcategory_name) || '%' + + + + + + + select category_id, category_name from ec_categories where upper(:category) like upper(category_name) || '%' + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/custom-field-add-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/custom-field-add-2.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/custom-field-add-2.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,18 @@ + + + + + + select count(*) from ec_custom_product_fields where field_identifier=:field_identifier + + + + + + + select count(*) from user_tab_columns where column_name=upper(:field_identifier) and table_name='EC_PRODUCTS' + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/custom-field-add-3.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/custom-field-add-3.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/custom-field-add-3.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,32 @@ + + + + + + select count(*) from ec_custom_product_fields where field_identifier=:field_identifier + + + + + + + delete from ec_custom_product_fields where field_identifier=:field_identifier + + + + + + + delete from ec_custom_product_fields where field_identifier=:field_identifier + + + + + + + alter table ec_custom_product_field_values drop column $field_identifier + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/custom-field-edit-2-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/custom-field-edit-2-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/custom-field-edit-2-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,22 @@ + + + + oracle8.1.6 + + + + + update ec_custom_product_fields + set field_name = :field_name, + default_value = :default_value, + column_type = :column_type, + last_modified = sysdate, + last_modifying_user = :user_id, + modified_ip_address = :peeraddr + where field_identifier = :field_identifier + + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/custom-field-edit-2-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/custom-field-edit-2-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/custom-field-edit-2-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,22 @@ + + + + postgresql7.1 + + + + + update ec_custom_product_fields + set field_name = :field_name, + default_value = :default_value, + column_type = :column_type, + last_modified = current_timestamp, + last_modifying_user = :user_id, + modified_ip_address = :peeraddr + where field_identifier = :field_identifier + + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/custom-field-edit-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/custom-field-edit-2.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/custom-field-edit-2.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,29 @@ + + + + + + alter table ec_custom_product_field_values drop constraint ${field_identifier}_constraint + + + + + + + + alter table ec_custom_product_field_values + modify ($field_identifier $column_type) + + + + + + + + alter table ec_custom_p_field_values_audit + modify ($field_identifier $column_type) + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/custom-field-edit.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/custom-field-edit.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/custom-field-edit.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,15 @@ + + + + + + +select field_name, default_value, column_type, active_p +from ec_custom_product_fields +where field_identifier=:field_identifier + + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/custom-field-status-change-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/custom-field-status-change-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/custom-field-status-change-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,20 @@ + + + + oracle8.1.6 + + + + +update ec_custom_product_fields +set active_p = :active_p, + last_modified = sysdate, + last_modifying_user = :user_id, + modified_ip_address = :peeraddr +where field_identifier = :field_identifier + + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/custom-field-status-change-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/custom-field-status-change-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/custom-field-status-change-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,20 @@ + + + + postgresql7.1 + + + + +update ec_custom_product_fields +set active_p = :active_p, + last_modified = current_timestamp, + last_modifying_user = :user_id, + modified_ip_address = :peeraddr +where field_identifier = :field_identifier + + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/custom-field.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/custom-field.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/custom-field.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,15 @@ + + + + + + +select field_name, default_value, column_type, active_p +from ec_custom_product_fields +where field_identifier=:field_identifier + + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/custom-fields.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/custom-fields.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/custom-fields.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,15 @@ + + + + + + +select field_identifier, field_name, active_p +from ec_custom_product_fields +order by active_p desc, field_name + + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/delete-2-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/delete-2-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/delete-2-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,17 @@ + + + + oracle8.1.6 + + + + + begin + ec_product.delete(:product_id); + end; + + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/delete-2-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/delete-2-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/delete-2-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,15 @@ + + + + postgresql7.1 + + + + + select ec_product__delete(:product_id) + + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/delete-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/delete-2.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/delete-2.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,172 @@ + + + + + + select count(*) from ec_items where product_id=:product_id + + + + + + + select offer_id from ec_offers where product_id=:product_id + + + + + + + delete from ec_offers where product_id=:product_id + + + + + + + delete from ec_custom_product_field_values where product_id=:product_id + + + + + + + select subsubcategory_id from ec_subsubcategory_product_map where product_id=:product_id + + + + + + + delete from ec_subsubcategory_product_map where product_id=:product_id + + + + + + + select subcategory_id from ec_subcategory_product_map where product_id=:product_id + + + + + + + delete from ec_subcategory_product_map where product_id=:product_id + + + + + + + select category_id from ec_category_product_map where product_id=:product_id + + + + + + + delete from ec_category_product_map where product_id=:product_id + + + + + + + select review_id from ec_product_reviews where product_id=:product_id + + + + + + + delete from ec_product_reviews where product_id=:product_id + + + + + + + delete from ec_product_comments where product_id=:product_id + + + + + + + select product_a from ec_product_links where product_b=:product_id + + + + + + + select product_b from ec_product_links where product_a=:product_id + + + + + + + delete from ec_product_links where product_a=:product_id or product_b=:product_id + + + + + + + select user_class_id, price from ec_product_user_class_prices where product_id=:product_id + + + + + + + delete from ec_user_session_info where product_id=:product_id + + + + + + + delete from ec_product_user_class_prices where product_id=:product_id + + + + + + + select series_id from ec_product_series_map where component_id=:product_id + + + + + + + select component_id from ec_product_series_map where series_id=:product_id + + + + + + + delete from ec_product_series_map where series_id=:product_id or component_id=:product_id + + + + + + + select sale_price_id from ec_sale_prices where product_id=:product_id + + + + + + + delete from ec_sale_prices where product_id=:product_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/edit-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/edit-2.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/edit-2.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,62 @@ + + + + + + + select column_type, field_identifier, field_name + from ec_custom_product_fields + where column_type in ('date','integer') + and active_p='t' + + + + + + + + select dirname from ec_products where product_id=:product_id + + + + + + + select user_class_id, user_class_name from ec_user_classes order by user_class_name + + + + + + + + select field_identifier, field_name, column_type + from ec_custom_product_fields + where active_p = 't' + + + + + + + + select template_name from ec_templates where template_id=:template_id + + + + + + + select field_identifier from ec_custom_product_fields where active_p='t' + + + + + + + select user_class_id from ec_user_classes + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/edit-3.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/edit-3.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/edit-3.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,165 @@ + + + + + + + update ec_products + set product_name=:product_name, + sku=:sku, + one_line_description=:one_line_description, + detailed_description=:detailed_description, + color_list=:color_list, + size_list=:size_list, + style_list=:style_list, + email_on_purchase_list=:email_on_purchase_list, + search_keywords=:search_keywords, + url=:url, + price=:price, + no_shipping_avail_p=:no_shipping_avail_p, + present_p=:present_p, + available_date=:available_date, + shipping=:shipping, + shipping_additional=:shipping_additional, + weight=:weight, + template_id=:template_id, + stock_status=:stock_status, + $audit_update + where product_id=:product_id + + + + + + + + select field_identifier from ec_custom_product_fields where active_p='t' + + + + + + + select count(*) from ec_custom_product_field_values where product_id=:product_id + + + + + + + + insert into ec_custom_product_field_values + ([join $custom_columns_to_insert ", "], $audit_fields) + values + ([join $custom_column_values_to_insert ","], $audit_info) + + + + + + + + update ec_custom_product_field_values set [join $update_list ", "], $audit_update where product_id=:product_id + + + + + + + select category_id from ec_category_product_map where product_id=:product_id + + + + + + + select subcategory_id from ec_subcategory_product_map where product_id=:product_id + + + + + + + select subsubcategory_id from ec_subsubcategory_product_map where product_id=:product_id + + + + + + + delete from ec_subsubcategory_product_map where product_id=$product_id and subsubcategory_id=:old_subsubcategory_id + + + + + + + delete from ec_subcategory_product_map where product_id=:product_id and subcategory_id=:old_subcategory_id + + + + + + + delete from ec_category_product_map where product_id=:product_id and category_id=:old_category_id + + + + + + + insert into ec_category_product_map (product_id, category_id, $audit_fields) values (:product_id, :new_category_id, $audit_info) + + + + + + + insert into ec_subcategory_product_map (product_id, subcategory_id, $audit_fields) values (:product_id, :new_subcategory_id, $audit_info) + + + + + + + insert into ec_subsubcategory_product_map (product_id, subsubcategory_id, $audit_fields) values (:product_id, :new_subsubcategory_id, $audit_info) + + + + + + + select user_class_id from ec_user_classes + + + + + + + select user_class_id, price from ec_product_user_class_prices where product_id=$product_id + + + + + + + delete from ec_product_user_class_prices where user_class_id = :user_class_id + + + + + + + insert into ec_product_user_class_prices (product_id, user_class_id, price, $audit_fields) values (:product_id, :user_class_id, :user_class_price, $audit_info) + + + + + + + update ec_product_user_class_prices set price=:user_class_price, $audit_update where user_class_id = :user_class_id and product_id = :product_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/edit.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/edit.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/edit.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,74 @@ + + + + + + select * from ec_products where product_id=:product_id + + + + + + + select category_id from ec_category_product_map where product_id=:product_id + + + + + + + select subcategory_id from ec_subcategory_product_map where product_id=:product_id + + + + + + + select subsubcategory_id from ec_subsubcategory_product_map where product_id=:product_id + + + + + + + select count(*) from ec_user_classes + + + + + + + select user_class_id, user_class_name from ec_user_classes order by user_class_name + + + + + + + select price from ec_product_user_class_prices where product_id=:product_id and user_class_id=:user_class_id + + + + + + + select count(*) from ec_custom_product_fields where active_p='t' + + + + + + + select field_identifier, field_name, default_value, column_type from ec_custom_product_fields where active_p='t' order by creation_date + + + + + + + select $field_identifier from ec_custom_product_field_values where product_id=:product_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/index.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/index.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/index.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,11 @@ + + + + + + select count(*) as n_products, round(avg(price),2) as avg_price from ec_products_displayable + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/link-add-3-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/link-add-3-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/link-add-3-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,28 @@ + + + + oracle8.1.6 + + + + insert into ec_product_links + (product_a, product_b, last_modified, last_modifying_user, modified_ip_address) + values + (:link_product_id, :product_id, sysdate, :user_id, :peeraddr) + + + + + + + + insert into ec_product_links + (product_a, product_b, last_modified, last_modifying_user, modified_ip_address) + values + (:product_id, :link_product_id, sysdate, :user_id, :peeraddr) + + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/link-add-3-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/link-add-3-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/link-add-3-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,28 @@ + + + + postgresql7.1 + + + + insert into ec_product_links + (product_a, product_b, last_modified, last_modifying_user, modified_ip_address) + values + (:link_product_id, :product_id, current_timestamp, :user_id, :peeraddr) + + + + + + + + insert into ec_product_links + (product_a, product_b, last_modified, last_modifying_user, modified_ip_address) + values + (:product_id, :link_product_id, current_timestamp, :user_id, :peeraddr) + + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/link-add-3.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/link-add-3.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/link-add-3.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,18 @@ + + + + + + select count(*) from ec_product_links where product_a=:link_product_id and product_b=:product_id + + + + + + + select count(*) from ec_product_links where product_a=:product_id and product_b=:link_product_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/link-add.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/link-add.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/link-add.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,11 @@ + + + + + + select product_id as link_product_id, product_name as link_product_name from ec_products where $additional_query_part + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/link-delete-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/link-delete-2.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/link-delete-2.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,11 @@ + + + + + + delete from ec_product_links where product_a=:product_a and product_b=:product_b + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/link.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/link.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/link.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,28 @@ + + + + + + +select product_b, product_name as product_b_name +from ec_product_links, ec_products +where product_a=:product_id +and product_b=ec_products.product_id + + + + + + + + +select product_a, product_name as product_a_name +from ec_product_links, ec_products +where product_b=:product_id +and ec_product_links.product_a=ec_products.product_id + + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/list-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/list-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/list-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,18 @@ + + + + oracle8.1.6 + + + + select ep.product_id, ep.product_name, ep.available_date, count(distinct eir.item_id) as n_items_ordered, count(distinct epc.comment_id) as n_comments +from ec_products ep, ec_items_reportable eir, ec_product_comments epc +where ep.product_id = eir.product_id(+) +and ep.product_id = epc.product_id(+) $category_exclusion_clause +group by ep.product_id, ep.product_name, ep.available_date +$order_by_clause + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/list.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/list.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/list.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,26 @@ + + + + + + select category_name from ec_categories where category_id = :category_id + + + + + + + +select ep.product_id, ep.product_name, ep.available_date, count(distinct eir.item_id) as n_items_ordered, count(distinct epc.comment_id) as n_comments +from ec_products ep + LEFT JOIN ec_items_reportable eir using (product_id) + LEFT JOIN ec_product_comments epc on (ep.product_id = epc.product_id) + $category_exclusion_clause +group by ep.product_id, ep.product_name, ep.available_date +$order_by_clause + + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/offer-add-2-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/offer-add-2-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/offer-add-2-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,25 @@ + + + + oracle8.1.6 + + + + +insert into ec_offers +(offer_id, product_id, retailer_id, price, shipping, stock_status, + special_offer_p, special_offer_html, offer_begins, + offer_ends $additional_column, last_modified, last_modifying_user, + modified_ip_address) +values +(:offer_id, :product_id, :retailer_id, :price, :shipping, :stock_status, + :special_offer_p, :special_offer_html, + to_date(:offer_begins, 'YYYY-MM-DD HH24:MI:SS'), + to_date(:offer_ends,'YYYY-MM-DD HH24:MI:SS') $additional_value, sysdate, + :user_id, :peeraddr) + + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/offer-add-2-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/offer-add-2-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/offer-add-2-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,25 @@ + + + + postgresql7.1 + + + + +insert into ec_offers +(offer_id, product_id, retailer_id, price, shipping, stock_status, + special_offer_p, special_offer_html, offer_begins, + offer_ends $additional_column, last_modified, last_modifying_user, + modified_ip_address) +values +(:offer_id, :product_id, :retailer_id, :price, :shipping, :stock_status, + :special_offer_p, :special_offer_html, + to_date(:offer_begins, 'YYYY-MM-DD HH24:MI:SS'), + to_date(:offer_ends,'YYYY-MM-DD HH24:MI:SS') $additional_value, current_timestamp, + :user_id, :peeraddr) + + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/offer-add-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/offer-add-2.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/offer-add-2.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,11 @@ + + + + + + select count(*) from ec_offers where offer_id=:offer_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/offer-add-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/Attic/offer-add-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/offer-add-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,13 @@ + + + + oracle8.1.6 + + + + select ec_offer_sequence.nextval from dual + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/offer-add-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/Attic/offer-add-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/offer-add-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,13 @@ + + + + postgresql7.1 + + + + select ec_offer_sequence.nextval + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/offer-add.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/offer-add.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/offer-add.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,26 @@ + + + + + + +select count(*) from ec_offers +where product_id=:product_id +and retailer_id=:retailer_id +and deleted_p='f' +and (($to_date_offer_begins >= offer_begins and $to_date_offer_begins <= offer_ends) or ($to_date_offer_ends >= offer_begins and $to_date_offer_ends <= offer_ends) or ($to_date_offer_begins <= offer_ends and $to_date_offer_ends >= offer_ends)) + + + + + + + + +select retailer_name || ' (' || case when reach = 'web' then url else city || ', ' || usps_abbrev end || ')' from ec_retailers where retailer_id=:retailer_id + + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/offer-delete-2-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/offer-delete-2-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/offer-delete-2-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,21 @@ + + + + oracle8.1.6 + + + + +update ec_offers +set deleted_p=:deleted_p, + last_modified=sysdate, + last_modifying_user=:user_id, + modified_ip_address=:peeraddr +where product_id=:product_id +and retailer_id=:retailer_id + + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/offer-delete-2-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/offer-delete-2-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/offer-delete-2-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,21 @@ + + + + postgresql7.1 + + + + +update ec_offers +set deleted_p=:deleted_p, + last_modified=current_timestamp, + last_modifying_user=:user_id, + modified_ip_address=:peeraddr +where product_id=:product_id +and retailer_id=:retailer_id + + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/offer-edit-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/offer-edit-2.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/offer-edit-2.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,24 @@ + + + + + + select count(*) from ec_offers +where product_id=:product_id +and retailer_id=:retailer_id +and offer_id != :offer_id +and deleted_p='f' +and (($to_date_offer_begins >= offer_begins and $to_date_offer_begins <= offer_ends) or ($to_date_offer_ends >= offer_begins and $to_date_offer_ends <= offer_ends) or ($to_date_offer_begins <= offer_ends and $to_date_offer_ends >= offer_ends)) + + + + + + + + select retailer_name || ' (' || city || ', ' || usps_abbrev || ')' as retailer_name_to_print from ec_retailers where retailer_id=:retailer_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/offer-edit-3-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/offer-edit-3-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/offer-edit-3-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,27 @@ + + + + oracle8.1.6 + + + + +update ec_offers +set retailer_id = :retailer_id, + price = :price, + shipping = :shipping, + stock_status = :stock_status, + special_offer_p = :special_offer_p, + special_offer_html = :special_offer_html, + offer_begins = to_date(:offer_begins, 'YYYY-MM-DD HH24:MI:SS'), + offer_ends = to_date(:offer_ends, 'YYYY-MM-DD HH24:MI:SS') $additional_thing_to_insert, + last_modified = sysdate, + last_modifying_user = :user_id, + modified_ip_address = :peeraddr +where offer_id = :offer_id + + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/offer-edit-3-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/offer-edit-3-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/offer-edit-3-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,27 @@ + + + + postgresql7.1 + + + + +update ec_offers +set retailer_id = :retailer_id, + price = :price, + shipping = :shipping, + stock_status = :stock_status, + special_offer_p = :special_offer_p, + special_offer_html = :special_offer_html, + offer_begins = to_date(:offer_begins, 'YYYY-MM-DD HH24:MI:SS'), + offer_ends = to_date(:offer_ends, 'YYYY-MM-DD HH24:MI:SS') $additional_thing_to_insert, + last_modified = current_timestamp, + last_modifying_user = :user_id, + modified_ip_address = :peeraddr +where offer_id = :offer_id + + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/offer-edit.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/offer-edit.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/offer-edit.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,26 @@ + + + + + + +select retailer_name, retailer_id, city, usps_abbrev +from ec_retailers +order by retailer_name + + + + + + + + +select price, shipping, stock_status, shipping_unavailable_p, offer_begins, + offer_ends, special_offer_p, special_offer_html from ec_offers +where offer_id=:offer_id + + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/offers-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/offers-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/offers-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,40 @@ + + + + oracle8.1.6 + + + + +select o.offer_id, o.retailer_id, r.retailer_name, price, shipping, + stock_status, special_offer_p, special_offer_html, + shipping_unavailable_p, offer_begins, offer_ends, o.deleted_p, + case when sign(sysdate-offer_begins) = 1 then 1 else 0 end as offer_begun_p, + case when sign(sysdate-offer_ends) = 1 then 1 else 0 end as offer_expired_p +from ec_offers_current o, ec_retailers r +where o.retailer_id=r.retailer_id +and o.product_id=:product_id +order by o.last_modified desc + + + + + + + + +select o.offer_id, o.retailer_id, retailer_name, price, shipping, + stock_status, special_offer_p, special_offer_html, + shipping_unavailable_p, offer_begins, offer_ends, o.deleted_p, + case when sign(sysdate-offer_begins) = 1 then 1 else 0 end as offer_begun_p, + case when sign(sysdate-offer_ends) = 1 then 1 else 0 end as offer_expired_p +from ec_offers o, ec_retailers r +where o.retailer_id=r.retailer_id + and o.product_id=:product_id + and (o.deleted_p='t' or o.offer_begins - sysdate > 0 or o.offer_ends - sysdate < 0) +order by o.last_modified desc + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/offers-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/offers-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/offers-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,40 @@ + + + + postgresql7.1 + + + + +select o.offer_id, o.retailer_id, r.retailer_name, price, shipping, + stock_status, special_offer_p, special_offer_html, + shipping_unavailable_p, offer_begins, offer_ends, o.deleted_p, + case when sign(current_timestamp-offer_begins) = 1 then 1 else 0 end as offer_begun_p, + case when sign(current_timestamp-offer_ends) = 1 then 1 else 0 end as offer_expired_p +from ec_offers_current o, ec_retailers r +where o.retailer_id=r.retailer_id +and o.product_id=:product_id +order by o.last_modified desc + + + + + + + + +select o.offer_id, o.retailer_id, retailer_name, price, shipping, + stock_status, special_offer_p, special_offer_html, + shipping_unavailable_p, offer_begins, offer_ends, o.deleted_p, + case when sign(current_timestamp-offer_begins) = 1 then 1 else 0 end as offer_begun_p, + case when sign(current_timestamp-offer_ends) = 1 then 1 else 0 end as offer_expired_p +from ec_offers o, ec_retailers r +where o.retailer_id=r.retailer_id + and o.product_id=:product_id + and (o.deleted_p='t' or o.offer_begins - current_timestamp > 0 or o.offer_ends - current_timestamp < 0) +order by o.last_modified desc + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/offers.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/offers.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/offers.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,14 @@ + + + + + + +select retailer_name, retailer_id, case when reach = 'web' then url else city || ', ' || usps_abbrev end as location +from ec_retailers order by retailer_name + + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/one.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/one.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/one.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,102 @@ + + + + + + select * from ec_products where product_id=:product_id + + + + + + + select * from ec_custom_product_field_values where product_id=:product_id + + + + + + + select category_id from ec_category_product_map where product_id=:product_id + + + + + + + select subcategory_id from ec_subcategory_product_map where product_id=:product_id + + + + + + + select subsubcategory_id from ec_subsubcategory_product_map where product_id=:product_id + + + + + + + select count(*) from ec_product_reviews where product_id = :product_id + + + + + + + select count(*) from ec_product_comments where product_id = :product_id + + + + + + + select count(*) from ec_product_links where product_b = :product_id + + + + + + + select count(*) from ec_product_links where product_a = :product_id + + + + + + + select count(*) from ec_sale_prices_current where product_id=:product_id + + + + + + + select user_class_id, user_class_name from ec_user_classes order by user_class_name + + + + + + + select price from ec_product_user_class_prices where product_id=:product_id and user_class_id=:user_class_id + + + + + + + select field_identifier, field_name, column_type from ec_custom_product_fields where active_p = 't' + + + + + + + select template_name from ec_templates where template_id=:template_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/recommendation-add-3-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/Attic/recommendation-add-3-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/recommendation-add-3-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,13 @@ + + + + oracle8.1.6 + + + + select ec_recommendation_id_sequence.nextval from dual + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/recommendation-add-3-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/Attic/recommendation-add-3-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/recommendation-add-3-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,13 @@ + + + + postgresql7.1 + + + + select ec_recommendation_id_sequence.nextval + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/recommendation-add-3.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/recommendation-add-3.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/recommendation-add-3.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,11 @@ + + + + + + select user_class_name from ec_user_classes where user_class_id=:user_class_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/recommendation-add-4-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/recommendation-add-4-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/recommendation-add-4-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,19 @@ + + + + oracle8.1.6 + + + + insert into ec_product_recommendations +(recommendation_id, product_id, user_class_id, recommendation_text, active_p, category_id, subcategory_id, subsubcategory_id, +last_modified, last_modifying_user, modified_ip_address) +values +(:recommendation_id, :product_id, :user_class_id, :recommendation_text, 't', :category_id, :subcategory_id, :subsubcategory_id, +sysdate, :user_id, :peeraddr) + + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/recommendation-add-4-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/recommendation-add-4-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/recommendation-add-4-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,19 @@ + + + + postgresql7.1 + + + + insert into ec_product_recommendations +(recommendation_id, product_id, user_class_id, recommendation_text, active_p, category_id, subcategory_id, subsubcategory_id, +last_modified, last_modifying_user, modified_ip_address) +values +(:recommendation_id, :product_id, :user_class_id, :recommendation_text, 't', :category_id, :subcategory_id, :subsubcategory_id, +current_timestamp, :user_id, :peeraddr) + + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/recommendation-add-4.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/recommendation-add-4.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/recommendation-add-4.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,11 @@ + + + + + + select count(*) from ec_product_recommendations where recommendation_id=:recommendation_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/recommendation-add.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/recommendation-add.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/recommendation-add.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,15 @@ + + + + + + +select product_name, product_id +from ec_products +where upper(product_name) like '%' || upper(:product_name_query) || '%' + + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/recommendation-delete-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/recommendation-delete-2.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/recommendation-delete-2.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,11 @@ + + + + + + delete from ec_product_recommendations where recommendation_id=:recommendation_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/recommendation-delete.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/recommendation-delete.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/recommendation-delete.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,21 @@ + + + + + + select r.*, p.product_name +from ec_product_recommendations r, ec_products p +where recommendation_id=:recommendation_id +and r.product_id=p.product_id + + + + + + + select user_class_name from ec_user_classes where user_class_id=:user_class_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/recommendation-text-edit-2-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/recommendation-text-edit-2-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/recommendation-text-edit-2-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,20 @@ + + + + oracle8.1.6 + + + + +update ec_product_recommendations +set recommendation_text = :recommendation_text, + last_modified=sysdate, + last_modifying_user=:user_id, + modified_ip_address=:peeraddr +where recommendation_id=:recommendation_id + + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/recommendation-text-edit-2-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/recommendation-text-edit-2-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/recommendation-text-edit-2-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,20 @@ + + + + postgresql7.1 + + + + +update ec_product_recommendations +set recommendation_text = :recommendation_text, + last_modified=current_timestamp, + last_modifying_user=:user_id, + modified_ip_address=:peeraddr +where recommendation_id=:recommendation_id + + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/recommendation-text-edit.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/recommendation-text-edit.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/recommendation-text-edit.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,15 @@ + + + + + + select r.*, p.product_name +from ec_product_recommendations r, ec_products p +where recommendation_id=$recommendation_id + +and r.product_id=p.product_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/recommendation.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/recommendation.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/recommendation.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,21 @@ + + + + + + select r.*, p.product_name +from ec_recommendations_cats_view r, ec_products p +where recommendation_id=:recommendation_id +and r.product_id=p.product_id + + + + + + + select user_class_name from ec_user_classes where user_class_id=:user_class_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/recommendations-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/recommendations-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/recommendations-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,22 @@ + + + + oracle8.1.6 + + + + +select + r.recommendation_id, r.the_category_name, r.the_subcategory_name, r.the_subsubcategory_name, + p.product_name, + c.user_class_name +from ec_recommendations_cats_view r, ec_products p, ec_user_classes c +where r.active_p='t' +and r.user_class_id = c.user_class_id(+) +and r.product_id = p.product_id +order by case when the_category_name = NULL then 0 else 1 end, upper(the_category_name), upper(the_subcategory_name), upper(the_subsubcategory_name) + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/recommendations.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/Attic/recommendations.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/recommendations.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,21 @@ + + + + + + +select + r.recommendation_id, r.the_category_name, r.the_subcategory_name, r.the_subsubcategory_name, + p.product_name, + c.user_class_name +from ec_recommendations_cats_view r + JOIN ec_products p using (product_id) + LEFT JOIN ec_user_classes c on (r.user_class_id = c.user_class_id) +where r.active_p='t' +order by case when the_category_name = NULL then 0 else 1 end, upper(the_category_name), upper(the_subcategory_name), upper(the_subsubcategory_name) + + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/review-add-2-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/review-add-2-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/review-add-2-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,17 @@ + + + + oracle8.1.6 + + + + insert into ec_product_reviews +(review_id, product_id, publication, author_name, review, display_p, review_date, last_modified, last_modifying_user, modified_ip_address) +values +(:review_id, :product_id, :publication, :author_name, :review, :display_p, to_date(:review_date, 'YYYY-MM-DD HH24:MI:SS'), sysdate, :user_id, :peeraddr) + + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/review-add-2-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/review-add-2-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/review-add-2-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,17 @@ + + + + postgresql7.1 + + + + insert into ec_product_reviews +(review_id, product_id, publication, author_name, review, display_p, review_date, last_modified, last_modifying_user, modified_ip_address) +values +(:review_id, :product_id, :publication, :author_name, :review, :display_p, to_date(:review_date, 'YYYY-MM-DD HH24:MI:SS'), current_timestamp, :user_id, :peeraddr) + + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/review-add-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/review-add-2.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/review-add-2.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,11 @@ + + + + + + select count(*) from ec_product_reviews where review_id=:review_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/review-add-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/Attic/review-add-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/review-add-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,13 @@ + + + + oracle8.1.6 + + + + select ec_product_review_id_sequence.nextval from dual + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/review-add-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/Attic/review-add-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/review-add-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,13 @@ + + + + postgresql7.1 + + + + select ec_product_review_id_sequence.nextval + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/review-edit-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/review-edit-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/review-edit-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,25 @@ + + + + oracle8.1.6 + + + + +update ec_product_reviews +set product_id=:product_id, + publication=:publication, + author_name=:author_name, + review_date=[ec_datetime_sql review_date], + review=:review, + display_p=:display_p, + last_modified=sysdate, + last_modifying_user=:user_id, + modified_ip_address=:peeraddr +where review_id=:review_id + + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/review-edit-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/review-edit-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/review-edit-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,25 @@ + + + + postgresql7.1 + + + + +update ec_product_reviews +set product_id=:product_id, + publication=:publication, + author_name=:author_name, + review_date=[ec_datetime_sql review_date], + review=:review, + display_p=:display_p, + last_modified=current_timestamp, + last_modifying_user=:user_id, + modified_ip_address=:peeraddr +where review_id=:review_id + + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/review.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/review.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/review.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,11 @@ + + + + + + select * from ec_product_reviews where review_id=:review_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/reviews.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/reviews.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/reviews.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,15 @@ + + + + + + +select review_id, author_name, publication, review_date, display_p +from ec_product_reviews +where product_id=:product_id + + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/sale-price-add-2-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/sale-price-add-2-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/sale-price-add-2-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,16 @@ + + + + oracle8.1.6 + + + + insert into ec_sale_prices +(sale_price_id, product_id, sale_price, sale_begins, sale_ends, sale_name, offer_code, last_modified, last_modifying_user, modified_ip_address) +values +(:sale_price_id, :product_id, :sale_price, to_date(:sale_begins,'YYYY-MM-DD HH24:MI:SS'), to_date(:sale_ends,'YYYY-MM-DD HH24:MI:SS'), :sale_name, :offer_code, sysdate, :user_id, :peeraddr) + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/sale-price-add-2-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/sale-price-add-2-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/sale-price-add-2-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,16 @@ + + + + postgresql7.1 + + + + insert into ec_sale_prices +(sale_price_id, product_id, sale_price, sale_begins, sale_ends, sale_name, offer_code, last_modified, last_modifying_user, modified_ip_address) +values +(:sale_price_id, :product_id, :sale_price, to_date(:sale_begins,'YYYY-MM-DD HH24:MI:SS'), to_date(:sale_ends,'YYYY-MM-DD HH24:MI:SS'), :sale_name, :offer_code, current_timestamp, :user_id, :peeraddr) + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/sale-price-add-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/sale-price-add-2.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/sale-price-add-2.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,11 @@ + + + + + + select count(*) from ec_sale_prices where sale_price_id=:sale_price_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/sale-price-add-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/sale-price-add-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/sale-price-add-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,20 @@ + + + + oracle8.1.6 + + + + select 1 from dual where to_date('$sale_begins(date)','YYYY-MM-DD HH24:MI:SS') >to_date('$sale_ends(date)', 'YYYY-MM-DD HH24:MI:SS') + + + + + + + select ec_sale_price_id_sequence.nextval from dual + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/sale-price-add-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/sale-price-add-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/sale-price-add-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,20 @@ + + + + postgresql7.1 + + + + select 1 where to_date('$sale_begins(date)','YYYY-MM-DD HH24:MI:SS') >to_date('$sale_ends(date)', 'YYYY-MM-DD HH24:MI:SS') + + + + + + + select ec_sale_price_id_sequence.nextval + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/sale-price-edit-3-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/sale-price-edit-3-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/sale-price-edit-3-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,24 @@ + + + + oracle8.1.6 + + + + +update ec_sale_prices +set sale_price=:sale_price, + sale_begins=to_date(:sale_begins,'YYYY-MM-DD HH24:MI:SS'), + sale_ends=to_date(:sale_ends,'YYYY-MM-DD HH24:MI:SS'), + sale_name=:sale_name, + offer_code=:offer_code, + last_modified=sysdate, + last_modifying_user=:user_id, + modified_ip_address=:peeraddr +where sale_price_id=:sale_price_id + + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/sale-price-edit-3-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/sale-price-edit-3-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/sale-price-edit-3-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,24 @@ + + + + postgresql7.1 + + + + +update ec_sale_prices +set sale_price=:sale_price, + sale_begins=to_date(:sale_begins,'YYYY-MM-DD HH24:MI:SS'), + sale_ends=to_date(:sale_ends,'YYYY-MM-DD HH24:MI:SS'), + sale_name=:sale_name, + offer_code=:offer_code, + last_modified=current_timestamp, + last_modifying_user=:user_id, + modified_ip_address=:peeraddr +where sale_price_id=:sale_price_id + + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/sale-price-edit-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/sale-price-edit-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/sale-price-edit-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,13 @@ + + + + oracle8.1.6 + + + + select sale_price, to_char(sale_begins,'YYYY-MM-DD HH24:MI:SS') as sale_begins, to_char(sale_ends,'YYYY-MM-DD HH24:MI:SS') as sale_ends, sale_name, offer_code from ec_sale_prices where sale_price_id=:sale_price_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/sale-price-edit-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/sale-price-edit-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/sale-price-edit-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,13 @@ + + + + postgresql7.1 + + + + select sale_price, to_char(sale_begins,'YYYY-MM-DD HH24:MI:SS') as sale_begins, to_char(sale_ends,'YYYY-MM-DD HH24:MI:SS') as sale_ends, sale_name, offer_code from ec_sale_prices where sale_price_id=:sale_price_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/sale-price-expire-2-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/sale-price-expire-2-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/sale-price-expire-2-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,20 @@ + + + + oracle8.1.6 + + + + +update ec_sale_prices +set sale_ends=sysdate, + last_modified=sysdate, + last_modifying_user=:user_id, + modified_ip_address=:peeraddr +where sale_price_id=:sale_price_id + + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/sale-price-expire-2-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/sale-price-expire-2-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/sale-price-expire-2-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,20 @@ + + + + postgresql7.1 + + + + +update ec_sale_prices +set sale_ends=current_timestamp, + last_modified=current_timestamp, + last_modifying_user=:user_id, + modified_ip_address=:peeraddr +where sale_price_id=:sale_price_id + + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/sale-prices-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/sale-prices-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/sale-prices-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,27 @@ + + + + oracle8.1.6 + + + + select sale_price_id, sale_name, sale_price, offer_code, to_char(sale_begins,'YYYY-MM-DD HH24:MI:SS') as sale_begins, to_char(sale_ends,'YYYY-MM-DD HH24:MI:SS') as sale_ends, case when sign(sysdate-sale_begins) = 1 then 1 else 0 end as sale_begun_p, case when sign(sysdate-sale_ends) = 1 then 1 else 0 end as sale_expired_p +from ec_sale_prices_current +where product_id=:product_id +order by last_modified desc + + + + + + + select sale_price_id, sale_name, sale_price, offer_code, to_char(sale_begins,'YYYY-MM-DD HH24:MI:SS') as sale_begins, to_char(sale_ends,'YYYY-MM-DD HH24:MI:SS') as sale_ends, case when sign(sysdate-sale_begins) = 1 then 1 else 0 end as sale_begun_p, case when sign(sysdate-sale_ends) = 1 then 1 else 0 end as sale_expired_p +from ec_sale_prices +where product_id=:product_id +and (sale_begins - sysdate > 0 or sale_ends - sysdate < 0) +order by last_modified desc + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/sale-prices-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/sale-prices-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/sale-prices-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,27 @@ + + + + postgresql7.1 + + + + select sale_price_id, sale_name, sale_price, offer_code, to_char(sale_begins,'YYYY-MM-DD HH24:MI:SS') as sale_begins, to_char(sale_ends,'YYYY-MM-DD HH24:MI:SS') as sale_ends, case when sign(current_timestamp-sale_begins) = 1 then 1 else 0 end as sale_begun_p, case when sign(current_timestamp-sale_ends) = 1 then 1 else 0 end as sale_expired_p +from ec_sale_prices_current +where product_id=:product_id +order by last_modified desc + + + + + + + select sale_price_id, sale_name, sale_price, offer_code, to_char(sale_begins,'YYYY-MM-DD HH24:MI:SS') as sale_begins, to_char(sale_ends,'YYYY-MM-DD HH24:MI:SS') as sale_ends, case when sign(current_timestamp-sale_begins) = 1 then 1 else 0 end as sale_begun_p, case when sign(current_timestamp-sale_ends) = 1 then 1 else 0 end as sale_expired_p +from ec_sale_prices +where product_id=:product_id +and (sale_begins - current_timestamp > 0 or sale_ends - current_timestamp < 0) +order by last_modified desc + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/search.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/search.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/search.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,11 @@ + + + + + + select product_id, product_name from ec_products where $additional_query_part + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/subsubcategory-property-toggle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/subsubcategory-property-toggle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/subsubcategory-property-toggle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,13 @@ + + + + + + update ec_subsubcategory_product_map +set $thing_to_update +where product_id=:product_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/supporting-file-delete-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/supporting-file-delete-2.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/supporting-file-delete-2.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,11 @@ + + + + + + select dirname from ec_products where product_id=:product_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/supporting-files-upload-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/supporting-files-upload-2.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/supporting-files-upload-2.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,11 @@ + + + + + + select dirname from ec_products where product_id=:product_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/supporting-files-upload.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/supporting-files-upload.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/supporting-files-upload.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,11 @@ + + + + + + select dirname from ec_products where product_id=$product_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/toggle-active-p-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/toggle-active-p-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/toggle-active-p-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,20 @@ + + + + oracle8.1.6 + + + + +update ec_products +set active_p = logical_negation(active_p), + last_modified = sysdate, + last_modifying_user = :user_id, + modified_ip_address = :peeraddr +where product_id = :product_id + + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/toggle-active-p-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/toggle-active-p-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/toggle-active-p-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,20 @@ + + + + postgresql7.1 + + + + +update ec_products +set active_p = logical_negation(active_p), + last_modified = current_timestamp, + last_modifying_user = :user_id, + modified_ip_address = :peeraddr +where product_id = :product_id + + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/toggle-no-shipping-avail-p-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/toggle-no-shipping-avail-p-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/toggle-no-shipping-avail-p-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,20 @@ + + + + oracle8.1.6 + + + + +update ec_products +set no_shipping_avail_p = logical_negation(no_shipping_avail_p), + last_modified = sysdate, + last_modifying_user = :user_id, + modified_ip_address = :peeraddr +where product_id = :product_id + + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/toggle-no-shipping-avail-p-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/toggle-no-shipping-avail-p-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/toggle-no-shipping-avail-p-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,20 @@ + + + + postgresql7.1 + + + + +update ec_products +set no_shipping_avail_p = logical_negation(no_shipping_avail_p), + last_modified = current_timestamp, + last_modifying_user = :user_id, + modified_ip_address = :peeraddr +where product_id = :product_id + + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/upload-2-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/upload-2-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/upload-2-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,13 @@ + + + + oracle8.1.6 + + + + insert into ec_custom_product_field_values (product_id, last_modified, last_modifying_user, modified_ip_address) values (:val_$product_id_column, sysdate, :user_id, :peeraddr) + + + + + Index: openacs-4/packages/ecommerce/www/admin/products/upload-2-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/upload-2-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/upload-2-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,13 @@ + + + + postgresql7.1 + + + + insert into ec_custom_product_field_values (product_id, last_modified, last_modifying_user, modified_ip_address) values (:val_$product_id_column, current_timestamp, :user_id, :peeraddr) + + + + + Index: openacs-4/packages/ecommerce/www/admin/retailers/add-3.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/retailers/add-3.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/retailers/add-3.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,15 @@ + + + + + + insert into ec_retailers +(retailer_id, retailer_name, primary_contact_name, secondary_contact_name, primary_contact_info, secondary_contact_info, line1, line2, city, usps_abbrev, zip_code, phone, fax, url, country_code, reach, nexus_states, financing_policy, return_policy, price_guarantee_policy, delivery_policy, installation_policy, $audit_fields) +values +(:retailer_id, :retailer_name, :primary_contact_name, :secondary_contact_name, :primary_contact_info, :secondary_contact_info, :line1, :line2, :city, :usps_abbrev, :zip_code, :phone, :fax, :url, :country_code, :reach, :nexus_states, :financing_policy, :return_policy, :price_guarantee_policy, :delivery_policy, :installation_policy, $audit_info) + + + + + + Index: openacs-4/packages/ecommerce/www/admin/retailers/edit-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/retailers/edit-2.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/retailers/edit-2.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,36 @@ + + + + + + +update ec_retailers + set retailer_name=:retailer_name, + primary_contact_name=:primary_contact_name, + secondary_contact_name=:secondary_contact_name, + primary_contact_info=:primary_contact_info, + secondary_contact_info=:secondary_contact_info, + line1=:line1, + line2=:line2, + city=:city, + usps_abbrev=:usps_abbrev, + zip_code=:zip_code, + phone=:phone, + fax=:fax, + url=:url, + country_code=:country_code, + reach=:reach, + nexus_states=:nexus_states, + financing_policy=:financing_policy, + return_policy=:return_policy, + price_guarantee_policy=:price_guarantee_policy, + delivery_policy=:delivery_policy, + installation_policy=:installation_policy, + $audit_update +where retailer_id=:retailer_id + + + + + + Index: openacs-4/packages/ecommerce/www/admin/retailers/edit.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/retailers/edit.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/retailers/edit.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,36 @@ + + + + + + +select retailer_id , + retailer_name, + primary_contact_name, + secondary_contact_name, + primary_contact_info, + secondary_contact_info, + line1, + line2, + city , + usps_abbrev, + zip_code, + phone, + fax, + url, + country_code, + reach, + nexus_states, + financing_policy, + return_policy, + + price_guarantee_policy, + delivery_policy, + installation_policy +from ec_retailers +where retailer_id=:retailer_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/retailers/index.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/retailers/index.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/retailers/index.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,17 @@ + + + + + + + select retailer_id, + retailer_name, + case when reach = 'web' then url else city || ', ' || usps_abbrev end as location + from ec_retailers + order by retailer_name + + + + + + Index: openacs-4/packages/ecommerce/www/admin/retailers/one.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/retailers/one.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/retailers/one.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,35 @@ + + + + + + +select retailer_id , + retailer_name, + primary_contact_name, + secondary_contact_name, + primary_contact_info, + secondary_contact_info, + line1, + line2, + city , + usps_abbrev, + zip_code, + phone, + fax, + url, + country_code, + reach, + nexus_states, + financing_policy, + return_policy, + + price_guarantee_policy, + delivery_policy, + installation_policy + from ec_retailers where retailer_id=:retailer_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/sales-tax/clear-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/sales-tax/clear-2.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/sales-tax/clear-2.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,21 @@ + + + + + + + select usps_abbrev + from ec_sales_tax_by_state + + + + + + + + delete from ec_sales_tax_by_state + + + + + Index: openacs-4/packages/ecommerce/www/admin/sales-tax/edit-2-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/sales-tax/edit-2-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/sales-tax/edit-2-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,33 @@ + + + + oracle8.1.6 + + + + + update ec_sales_tax_by_state + set tax_rate=:state_tax_rate, + shipping_p=:ship_status, + last_modified=sysdate, + last_modifying_user=:user_id, + modified_ip_address=:ip_addr + where usps_abbrev=:usps_abbrev + + + + + + + + + insert into ec_sales_tax_by_state + (usps_abbrev, tax_rate, shipping_p, last_modified, last_modifying_user, modified_ip_address) + values + (:usps_abbrev, :state_tax_rate, :ship_status, sysdate, :user_id, :ip_addr) + + + + + + Index: openacs-4/packages/ecommerce/www/admin/sales-tax/edit-2-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/sales-tax/edit-2-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/sales-tax/edit-2-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,33 @@ + + + + postgresql7.1 + + + + + update ec_sales_tax_by_state + set tax_rate=:state_tax_rate, + shipping_p=:ship_status, + last_modified=current_timestamp, + last_modifying_user=:user_id, + modified_ip_address=:ip_addr + where usps_abbrev=:usps_abbrev + + + + + + + + + insert into ec_sales_tax_by_state + (usps_abbrev, tax_rate, shipping_p, last_modified, last_modifying_user, modified_ip_address) + values + (:usps_abbrev, :state_tax_rate, :ship_status, current_timestamp, :user_id, :ip_addr) + + + + + + Index: openacs-4/packages/ecommerce/www/admin/sales-tax/edit-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/sales-tax/edit-2.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/sales-tax/edit-2.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,32 @@ + + + + + + select usps_abbrev from ec_sales_tax_by_state + + + + + + + + select count(*) + from ec_sales_tax_by_state + where usps_abbrev=:usps_abbrev + + + + + + + + + delete from ec_sales_tax_by_state + where usps_abbrev=:old_state + + + + + + Index: openacs-4/packages/ecommerce/www/admin/sales-tax/edit.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/sales-tax/edit.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/sales-tax/edit.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,18 @@ + + + + + + select tax_rate*100 from ec_sales_tax_by_state where usps_abbrev=:usps_abbrev + + + + + + + select shipping_p from ec_sales_tax_by_state where usps_abbrev=:usps_abbrev + + + + + Index: openacs-4/packages/ecommerce/www/admin/sales-tax/index.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/sales-tax/index.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/sales-tax/index.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,20 @@ + + + + + + select state_name, tax_rate*100 as tax_rate_in_percent, case when shipping_p = 't' then 'Yes' else 'No' end as shipping_p +from ec_sales_tax_by_state, states +where ec_sales_tax_by_state.usps_abbrev = states.usps_abbrev + + + + + + + select usps_abbrev from ec_sales_tax_by_state + + + + + Index: openacs-4/packages/ecommerce/www/admin/shipping-costs/edit-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/shipping-costs/edit-2.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/shipping-costs/edit-2.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,17 @@ + + + + + + update ec_admin_settings +set base_shipping_cost = :base_shipping_cost, +default_shipping_per_item = :default_shipping_per_item, +weight_shipping_cost = :weight_shipping_cost, +add_exp_base_shipping_cost = :add_exp_base_shipping_cost, +add_exp_amount_per_item = :add_exp_amount_per_item, +add_exp_amount_by_weight = :add_exp_amount_by_weight + + + + + Index: openacs-4/packages/ecommerce/www/admin/shipping-costs/index.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/shipping-costs/index.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/shipping-costs/index.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,12 @@ + + + + + + select base_shipping_cost, default_shipping_per_item, weight_shipping_cost, add_exp_base_shipping_cost, add_exp_amount_per_item, add_exp_amount_by_weight +from ec_admin_settings + + + + + Index: openacs-4/packages/ecommerce/www/admin/templates/add-2-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/templates/Attic/add-2-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/templates/add-2-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,13 @@ + + + + oracle8.1.6 + + + + select ec_template_id_sequence.nextval from dual + + + + + Index: openacs-4/packages/ecommerce/www/admin/templates/add-2-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/templates/Attic/add-2-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/templates/add-2-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,13 @@ + + + + postgresql7.1 + + + + select ec_template_id_sequence.nextval + + + + + Index: openacs-4/packages/ecommerce/www/admin/templates/add-3-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/templates/add-3-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/templates/add-3-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,16 @@ + + + + oracle8.1.6 + + + + insert into ec_templates +(template_id, template_name, template, last_modified, last_modifying_user, modified_ip_address) +values +(:template_id, :template_name, :template, sysdate, :user_id, '[DoubleApos [ns_conn peeraddr]]') + + + + + Index: openacs-4/packages/ecommerce/www/admin/templates/add-3-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/templates/add-3-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/templates/add-3-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,16 @@ + + + + postgresql7.1 + + + + insert into ec_templates +(template_id, template_name, template, last_modified, last_modifying_user, modified_ip_address) +values +(:template_id, :template_name, :template, current_timestamp, :user_id, '[DoubleApos [ns_conn peeraddr]]') + + + + + Index: openacs-4/packages/ecommerce/www/admin/templates/add-3.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/templates/add-3.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/templates/add-3.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,11 @@ + + + + + + select count(*) from ec_templates where template_id=:template_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/templates/add.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/templates/add.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/templates/add.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,11 @@ + + + + + + select template from ec_templates where template_id=:based_on + + + + + Index: openacs-4/packages/ecommerce/www/admin/templates/category-associate-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/templates/category-associate-2.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/templates/category-associate-2.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,42 @@ + + + + + + select template_name as old_template from +ec_templates, ec_category_template_map m +where ec_templates.template_id = m.template_id +and m.category_id=:category_id + + + + + + + insert into ec_category_template_map (category_id, template_id) values (:category_id, :template_id) + + + + + + + update ec_category_template_map set template_id=:template_id where category_id=:category_id + + + + + + + select template_name from ec_templates where template_id=:template_id + + + + + + + select category_name from ec_categories where category_id=:category_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/templates/category-associate.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/templates/category-associate.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/templates/category-associate.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,64 @@ + + + + + + +select template_name, template + from ec_templates + where template_id=:template_id + + + + + + + + +select count(*) + from ec_category_template_map + where template_id=:template_id + + + + + + + + + select m.category_id, c.category_name + from ec_category_template_map m, ec_categories c + where m.category_id = c.category_id + and m.template_id = :template_id + + + + + + + + +select count(*) + from ec_categories + where category_id not in (select category_id + from ec_category_template_map + where template_id=:template_id) + + + + + + + + + select category_id, category_name + from ec_categories + where category_id not in (select category_id + from ec_category_template_map + where template_id=:template_id) + + + + + + Index: openacs-4/packages/ecommerce/www/admin/templates/delete-2-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/templates/delete-2-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/templates/delete-2-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,13 @@ + + + + oracle8.1.6 + + + + update ec_products set template_id=null, last_modified=sysdate, last_modifying_user=:user_id, modified_ip_address='[DoubleApos [ns_conn peeraddr]]' where template_id=:template_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/templates/delete-2-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/templates/delete-2-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/templates/delete-2-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,13 @@ + + + + postgresql7.1 + + + + update ec_products set template_id=null, last_modified=current_timestamp, last_modifying_user=:user_id, modified_ip_address='[DoubleApos [ns_conn peeraddr]]' where template_id=:template_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/templates/delete-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/templates/delete-2.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/templates/delete-2.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,26 @@ + + + + + + select default_template +from ec_admin_settings + + + + + + + delete from ec_category_template_map where template_id=:template_id + + + + + + + delete from ec_templates where template_id=:template_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/templates/delete.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/templates/delete.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/templates/delete.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,12 @@ + + + + + + select default_template +from ec_admin_settings + + + + + Index: openacs-4/packages/ecommerce/www/admin/templates/edit-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/templates/edit-2.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/templates/edit-2.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,13 @@ + + + + + + update ec_templates +set template_name=:template_name, template=:template +where template_id=:template_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/templates/edit.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/templates/edit.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/templates/edit.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,11 @@ + + + + + + select template_name, template from ec_templates where template_id=:template_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/templates/index-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/templates/index-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/templates/index-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,18 @@ + + + + oracle8.1.6 + + + + +SELECT t.template_id, t.template_name, c.category_id, c.category_name + FROM ec_templates t, ec_category_template_map m, ec_categories c + WHERE t.template_id = m.template_id (+) + and m.category_id = c.category_id (+) + ORDER BY template_name, category_name + + + + + Index: openacs-4/packages/ecommerce/www/admin/templates/index.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/templates/index.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/templates/index.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,17 @@ + + + + + + +SELECT t.template_id, t.template_name, c.category_id, c.category_name + FROM ec_templates t + LEFT JOIN ec_category_template_map m using (template_id) + LEFT JOIN ec_categories c on (m.category_id = c.category_id) + ORDER BY template_name, category_name + + + + + + Index: openacs-4/packages/ecommerce/www/admin/templates/make-default-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/templates/make-default-2.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/templates/make-default-2.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,11 @@ + + + + + + update ec_admin_settings set default_template = :template_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/templates/make-default.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/templates/make-default.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/templates/make-default.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,11 @@ + + + + + + select template_name, template from ec_templates where template_id=:template_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/templates/one.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/templates/one.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/templates/one.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,18 @@ + + + + + + select template_name, template from ec_templates where template_id=:template_id + + + + + + + select default_template from ec_admin_settings + + + + + Index: openacs-4/packages/ecommerce/www/admin/user-classes/add-2-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/user-classes/add-2-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/user-classes/add-2-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,17 @@ + + + + oracle8.1.6 + + + + insert into ec_user_classes +(user_class_id, user_class_name, last_modified, last_modifying_user, modified_ip_address) +values +(:user_class_id,:user_class_name, sysdate, :user_id, '[DoubleApos [ns_conn peeraddr]]') + + + + + + Index: openacs-4/packages/ecommerce/www/admin/user-classes/add-2-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/user-classes/add-2-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/user-classes/add-2-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,17 @@ + + + + postgresql7.1 + + + + insert into ec_user_classes +(user_class_id, user_class_name, last_modified, last_modifying_user, modified_ip_address) +values +(:user_class_id,:user_class_name, current_timestamp, :user_id, '[DoubleApos [ns_conn peeraddr]]') + + + + + + Index: openacs-4/packages/ecommerce/www/admin/user-classes/add-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/user-classes/add-2.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/user-classes/add-2.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,11 @@ + + + + + + select count(*) from ec_user_classes where user_class_id=:user_class_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/user-classes/add-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/user-classes/Attic/add-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/user-classes/add-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,13 @@ + + + + oracle8.1.6 + + + + select ec_user_class_id_sequence.nextval from dual + + + + + Index: openacs-4/packages/ecommerce/www/admin/user-classes/add-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/user-classes/Attic/add-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/user-classes/add-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,13 @@ + + + + postgresql7.1 + + + + select ec_user_class_id_sequence.nextval + + + + + Index: openacs-4/packages/ecommerce/www/admin/user-classes/approve-toggle-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/user-classes/approve-toggle-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/user-classes/approve-toggle-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,15 @@ + + + + oracle8.1.6 + + + + update ec_user_class_user_map +set user_class_approved_p=[ec_decode $user_class_approved_p "t" "'f'" "'t'"], last_modified=sysdate, last_modifying_user=:admin_user_id, modified_ip_address='[DoubleApos [ns_conn peeraddr]]' +where user_id=:user_id and user_class_id=:user_class_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/user-classes/approve-toggle-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/user-classes/approve-toggle-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/user-classes/approve-toggle-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,15 @@ + + + + postgresql7.1 + + + + update ec_user_class_user_map +set user_class_approved_p=[ec_decode $user_class_approved_p "t" "'f'" "'t'"], last_modified=current_timestamp, last_modifying_user=:admin_user_id, modified_ip_address='[DoubleApos [ns_conn peeraddr]]' +where user_id=:user_id and user_class_id=:user_class_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/user-classes/delete-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/user-classes/delete-2.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/user-classes/delete-2.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,28 @@ + + + + + + select user_id from ec_user_class_user_map where user_class_id = :user_class_id + + + + + + + delete from ec_user_class_user_map where user_class_id=:user_class_id + + + + + + + + delete from ec_user_classes +where user_class_id=:user_class_id + + + + + + Index: openacs-4/packages/ecommerce/www/admin/user-classes/delete.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/user-classes/delete.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/user-classes/delete.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,11 @@ + + + + + + select user_class_name from ec_user_classes where user_class_id=:user_class_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/user-classes/edit.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/user-classes/edit.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/user-classes/edit.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,13 @@ + + + + + + update ec_user_classes +set user_class_name=:user_class_name +where user_class_id=:user_class_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/user-classes/index-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/user-classes/index-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/user-classes/index-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,21 @@ + + + + oracle8.1.6 + + + + + select ec_user_classes.user_class_id, + ec_user_classes.user_class_name, + count(user_id) as n_users + from ec_user_classes, ec_user_class_user_map m + where ec_user_classes.user_class_id = m.user_class_id(+) + group by ec_user_classes.user_class_id, ec_user_classes.user_class_name + order by user_class_name + + + + + + Index: openacs-4/packages/ecommerce/www/admin/user-classes/index.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/user-classes/index.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/user-classes/index.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,31 @@ + + + + + + + select ec_user_classes.user_class_id, + ec_user_classes.user_class_name, + count(user_id) as n_users + from ec_user_classes + LEFT JOIN ec_user_class_user_map m using (user_class_id) + group by ec_user_classes.user_class_id, ec_user_classes.user_class_name + order by user_class_name + + + + + + + + + select count(*) as approved_n_users + from ec_user_class_user_map + where user_class_approved_p = 't' + and user_class_id=:user_class_id + + + + + + Index: openacs-4/packages/ecommerce/www/admin/user-classes/member-add-2-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/user-classes/member-add-2-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/user-classes/member-add-2-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,26 @@ + + + + oracle8.1.6 + + + + update ec_user_class_user_map +set user_class_approved_p='t', last_modified=sysdate, last_modifying_user=:admin_user_id, modified_ip_address='[DoubleApos [ns_conn peeraddr]]' +where user_id=:user_id and user_class_id=:user_class_id + + + + + + + insert into ec_user_class_user_map +(user_id, user_class_id, user_class_approved_p, last_modified, last_modifying_user, modified_ip_address) +values +(:user_id, :user_class_id, 't', sysdate, :user_id, '[DoubleApos [ns_conn peeraddr]]') + + + + + + Index: openacs-4/packages/ecommerce/www/admin/user-classes/member-add-2-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/user-classes/member-add-2-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/user-classes/member-add-2-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,26 @@ + + + + postgresql7.1 + + + + update ec_user_class_user_map +set user_class_approved_p='t', last_modified=current_timestamp, last_modifying_user=:admin_user_id, modified_ip_address='[DoubleApos [ns_conn peeraddr]]' +where user_id=:user_id and user_class_id=:user_class_id + + + + + + + insert into ec_user_class_user_map +(user_id, user_class_id, user_class_approved_p, last_modified, last_modifying_user, modified_ip_address) +values +(:user_id, :user_class_id, 't', current_timestamp, :user_id, '[DoubleApos [ns_conn peeraddr]]') + + + + + + Index: openacs-4/packages/ecommerce/www/admin/user-classes/member-add-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/user-classes/member-add-2.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/user-classes/member-add-2.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,11 @@ + + + + + + select count(*) from ec_user_class_user_map where user_id=:user_id and user_class_id=:user_class_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/user-classes/member-add.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/user-classes/member-add.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/user-classes/member-add.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,13 @@ + + + + + + select user_id, first_names, last_name, email +from cc_users +where $last_bit_of_query + + + + + Index: openacs-4/packages/ecommerce/www/admin/user-classes/member-delete-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/user-classes/member-delete-2.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/user-classes/member-delete-2.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,11 @@ + + + + + + delete from ec_user_class_user_map where user_id=:user_id and user_class_id=:user_class_id + + + + + Index: openacs-4/packages/ecommerce/www/admin/user-classes/members.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/user-classes/members.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/user-classes/members.xql 10 Jul 2001 20:33:53 -0000 1.1 @@ -0,0 +1,23 @@ + + + + + + select user_class_name from ec_user_classes where user_class_id = :user_class_id + + + + + + + select +cc.user_id, first_names, last_name, email, +m.user_class_approved_p +from cc_users cc, ec_user_class_user_map m +where cc.user_id = m.user_id +and m.user_class_id=:user_class_id + + + + +