Index: openacs-4/packages/ecommerce/www/account-oracle.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/account-oracle.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/account-oracle.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,41 @@
+
+
+
+ oracle8.1.6
+
+
+
+ select ec_gift_certificate_balance(:user_id) from dual
+
+
+
+
+
+
+ select 1 from dual where exists (select 1 from ec_user_classes)
+
+
+
+
+
+
+ select
+ ml.category_id,
+ c.category_name,
+ ml.subcategory_id,
+ s.subcategory_name,
+ ml.subsubcategory_id,
+ ss.subsubcategory_name
+from ec_cat_mailing_lists ml,
+ ec_categories c,
+ ec_subcategories s,
+ ec_subsubcategories ss
+where ml.user_id = :user_id
+and ml.category_id = c.category_id(+)
+and ml.subcategory_id = s.subcategory_id(+)
+and ml.subsubcategory_id = ss.subsubcategory_id(+)
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/account-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/account-postgresql.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/account-postgresql.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,20 @@
+
+
+
+ postgresql7.1
+
+
+
+ select ec_gift_certificate_balance(:user_id)
+
+
+
+
+
+
+ select 1 where exists (select 1 from ec_user_classes)
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/account.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/account.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/account.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,45 @@
+
+
+
+
+
+ select order_id, confirmed_date from ec_orders
+where user_id=:user_id
+and order_state not in ('in_basket','void','expired')
+order by order_id
+
+
+
+
+
+
+ select
+gift_certificate_id, issue_date, amount
+from ec_gift_certificates
+where purchased_by=:user_id
+and gift_certificate_state in ('authorized','authorized_plus_avs','authorized_minus_avs', 'confirmed')
+
+
+
+
+
+
+
+select
+ ml.category_id,
+ c.category_name,
+ ml.subcategory_id,
+ s.subcategory_name,
+ ml.subsubcategory_id,
+ ss.subsubcategory_name
+from ec_cat_mailing_lists ml
+ LEFT JOIN ec_categories c using(category_id)
+ LEFT JOIN ec_subcategories s using (subcategory_id)
+ LEFT JOIN ec_subsubcategories ss on (ml.subsubcategory_id = ss.subsubcategory_id)
+where ml.user_id = :user_id
+
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/category-browse-subcategory.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/category-browse-subcategory.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/category-browse-subcategory.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,89 @@
+
+
+
+
+
+ select first_names || ' ' || last_name from cc_users where user_id=:user_id
+
+
+
+
+
+
+
+ insert into ec_user_session_info (user_session_id, category_id)
+ values (:user_session_id, :category_id)
+
+
+
+
+
+
+
+ select category_name from ec_categories where category_id=:category_id
+
+
+
+
+
+
+ select subcategory_name from ec_subcategories where subcategory_id=:subcategory_id
+
+
+
+
+
+
+ select subsubcategory_name from ec_subsubcategories where subsubcategory_id=:subsubcategory_id
+
+
+
+
+
+
+ select
+ p.product_name, p.product_id, p.dirname, r.recommendation_text
+from ec_products_displayable p, ec_product_recommendations r
+where p.product_id = r.product_id
+and r.${sub}category_id=:${sub}category_id
+and r.active_p='t'
+and (r.user_class_id is null or r.user_class_id in
+ (select user_class_id
+ from ec_user_class_user_map m
+ where user_id=:user_id
+ $user_class_approved_p_clause))
+order by p.product_name
+
+
+
+
+
+
+ select p.product_id, p.product_name, p.one_line_description
+from ec_products_searchable p, $product_map($sub) m
+where p.product_id = m.product_id
+and m.${sub}category_id = :${sub}category_id
+$exclude_subproducts
+order by p.product_name
+
+
+
+
+
+
+
+
+SELECT * from ec_sub${sub}categories c
+ WHERE ${sub}category_id = :${sub}category_id
+ AND exists (
+ SELECT 'x' from ec_products_displayable p, $product_map(sub$sub) s
+ where p.product_id = s.product_id
+ and s.sub${sub}category_id = c.sub${sub}category_id
+ )
+ ORDER BY sort_key, sub${sub}category_name
+
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/category-browse-subsubcategory.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/category-browse-subsubcategory.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/category-browse-subsubcategory.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,86 @@
+
+
+
+
+
+ select first_names || ' ' || last_name from cc_users where user_id=:user_id
+
+
+
+
+
+
+ insert into ec_user_session_info (user_session_id, category_id) values (:user_session_id, :category_id)
+
+
+
+
+
+
+ select category_name from ec_categories where category_id=:category_id
+
+
+
+
+
+
+ select subcategory_name from ec_subcategories where subcategory_id=:subcategory_id
+
+
+
+
+
+
+ select subsubcategory_name from ec_subsubcategories where subsubcategory_id=:subsubcategory_id
+
+
+
+
+
+
+ select
+ p.product_name, p.product_id, p.dirname, r.recommendation_text
+from ec_products_displayable p, ec_product_recommendations r
+where p.product_id = r.product_id
+and r.${sub}category_id=:${sub}category_id
+and r.active_p='t'
+and (r.user_class_id is null or r.user_class_id in
+ (select user_class_id
+ from ec_user_class_user_map m
+ where user_id=:user_id
+ $user_class_approved_p_clause))
+order by p.product_name
+
+
+
+
+
+
+ select p.product_id, p.product_name, p.one_line_description
+from ec_products_searchable p, $product_map($sub) m
+where p.product_id = m.product_id
+and m.${sub}category_id = :${sub}category_id
+$exclude_subproducts
+order by p.product_name
+
+
+
+
+
+
+
+
+SELECT * from ec_sub${sub}categories c
+ WHERE ${sub}category_id = :${sub}category_id
+ AND exists (
+ SELECT 'x' from ec_products_displayable p, $product_map(sub$sub) s
+ where p.product_id = s.product_id
+ and s.sub${sub}category_id = c.sub${sub}category_id
+ )
+ ORDER BY sort_key, sub${sub}category_name
+
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/category-browse.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/category-browse.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/category-browse.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,86 @@
+
+
+
+
+
+ select first_names || ' ' || last_name from cc_users where user_id=:user_id
+
+
+
+
+
+
+ insert into ec_user_session_info (user_session_id, category_id) values (:user_session_id, :category_id)
+
+
+
+
+
+
+ select category_name from ec_categories where category_id=:category_id
+
+
+
+
+
+
+ select subcategory_name from ec_subcategories where subcategory_id=:subcategory_id
+
+
+
+
+
+
+ select subsubcategory_name from ec_subsubcategories where subsubcategory_id=:subsubcategory_id
+
+
+
+
+
+
+ select
+ p.product_name, p.product_id, p.dirname, r.recommendation_text
+from ec_products_displayable p, ec_product_recommendations r
+where p.product_id = r.product_id
+and r.${sub}category_id=:${sub}category_id
+and r.active_p='t'
+and (r.user_class_id is null or r.user_class_id in
+ (select user_class_id
+ from ec_user_class_user_map m
+ where user_id=:user_id
+ $user_class_approved_p_clause))
+order by p.product_name
+
+
+
+
+
+
+ select p.product_id, p.product_name, p.one_line_description
+from ec_products_searchable p, $product_map($sub) m
+where p.product_id = m.product_id
+and m.${sub}category_id = :${sub}category_id
+$exclude_subproducts
+order by p.product_name
+
+
+
+
+
+
+
+
+SELECT * from ec_sub${sub}categories c
+ WHERE ${sub}category_id = :${sub}category_id
+ AND exists (
+ SELECT 'x' from ec_products_displayable p, $product_map(sub$sub) s
+ where p.product_id = s.product_id
+ and s.sub${sub}category_id = c.sub${sub}category_id
+ )
+ ORDER BY sort_key, sub${sub}category_name
+
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/checkout-2-oracle.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/checkout-2-oracle.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/checkout-2-oracle.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,28 @@
+
+
+
+ oracle8.1.6
+
+
+
+
+ select p.no_shipping_avail_p, p.product_name, p.one_line_description, p.product_id,
+ count(*) as quantity,
+ u.offer_code,
+ i.color_choice, i.size_choice, i.style_choice
+ from ec_orders o, ec_items i, ec_products p,
+ (select offer_code, product_id
+ from ec_user_session_offer_codes usoc
+ where usoc.user_session_id=:user_session_id) u
+ where i.product_id=p.product_id
+ and o.order_id=i.order_id
+ and p.product_id= u.product_id(+)
+ and o.user_session_id=:user_session_id and o.order_state='in_basket'
+ group by p.no_shipping_avail_p, p.product_name, p.one_line_description, p.product_id,
+ u.offer_code,
+ i.color_choice, i.size_choice, i.style_choice
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/checkout-2.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/checkout-2.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/checkout-2.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,70 @@
+
+
+
+
+
+
+ select order_id,
+ user_id as order_owner
+ from ec_orders
+ where user_session_id=:user_session_id
+ and order_state='in_basket'
+
+
+
+
+
+
+
+ select count(*) from ec_items where order_id=:order_id
+
+
+
+
+
+
+ select count(*) from ec_addresses where address_id=:address_id and user_id=:user_id
+
+
+
+
+
+
+ update ec_orders set shipping_address=:address_id where order_id=:order_id
+
+
+
+
+
+
+ select shipping_address from ec_orders where order_id=:order_id
+
+
+
+
+
+
+ FIX ME OUTER JOIN
+
+ select p.no_shipping_avail_p, p.product_name, p.one_line_description, p.product_id,
+ count(*) as quantity,
+ u.offer_code,
+ i.color_choice, i.size_choice, i.style_choice
+ from ec_orders o
+ JOIN ec_items i on (o.order_id=i.order_id)
+ JOIN ec_products p on (i.product_id=p.product_id),
+ (select offer_code, product_id
+ from ec_user_session_offer_codes usoc
+ where usoc.user_session_id=:user_session_id) u
+ where i.product_id=p.product_id
+ and o.order_id=i.order_id
+ and p.product_id= u.product_id(+)
+ and o.user_session_id=:user_session_id and o.order_state='in_basket'
+ group by p.no_shipping_avail_p, p.product_name, p.one_line_description, p.product_id,
+ u.offer_code,
+ i.color_choice, i.size_choice, i.style_choice
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/checkout-3-oracle.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/checkout-3-oracle.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/checkout-3-oracle.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,20 @@
+
+
+
+ oracle8.1.6
+
+
+
+ select ec_order_cost(:order_id) from dual
+
+
+
+
+
+
+ select ec_gift_certificate_balance(:user_id) from dual
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/checkout-3-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/checkout-3-postgresql.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/checkout-3-postgresql.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,20 @@
+
+
+
+ postgresql7.1
+
+
+
+ select ec_order_cost(:order_id)
+
+
+
+
+
+
+ select ec_gift_certificate_balance(:user_id)
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/checkout-3.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/checkout-3.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/checkout-3.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,46 @@
+
+
+
+
+
+ select order_id from ec_orders where user_session_id=:user_session_id and order_state='in_basket'
+
+
+
+
+
+
+ select count(*) from ec_items where order_id=:order_id
+
+
+
+
+
+
+ select user_id from ec_orders where order_id=:order_id
+
+
+
+
+
+
+ select shipping_address from ec_orders where order_id=$order_id
+
+
+
+
+
+
+ select creditcard_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/checkout-oracle.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/checkout-oracle.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/checkout-oracle.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,20 @@
+
+
+
+ oracle8.1.6
+
+
+
+
+ select count(*)
+ from dual
+ where exists (select 1
+ from ec_products p, ec_items i
+ where i.product_id = p.product_id
+ and i.order_id = :order_id
+ and no_shipping_avail_p = 't')
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/checkout-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/checkout-postgresql.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/checkout-postgresql.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,20 @@
+
+
+
+ postgresql7.1
+
+
+
+
+ select count(*)
+
+ where exists (select 1
+ from ec_products p, ec_items i
+ where i.product_id = p.product_id
+ and i.order_id = :order_id
+ and no_shipping_avail_p = 't')
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/checkout.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/checkout.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/checkout.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,35 @@
+
+
+
+
+
+ select order_id from ec_orders where user_session_id=:user_session_id and order_state='in_basket'
+
+
+
+
+
+
+ update ec_orders set user_id=:user_id where order_id=:order_id
+
+
+
+
+
+
+
+select address_id, attn,
+ line1,
+ line2,
+ city, usps_abbrev, zip_code,
+ phone, country_code, full_state_name,
+ phone_time
+ from ec_addresses
+ where user_id=:user_id
+ and address_type='shipping'
+
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/credit-card-correction-2-oracle.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/Attic/credit-card-correction-2-oracle.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/credit-card-correction-2-oracle.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,13 @@
+
+
+
+ oracle8.1.6
+
+
+
+ select ec_creditcard_id_sequence.nextval from dual
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/credit-card-correction-2-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/Attic/credit-card-correction-2-postgresql.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/credit-card-correction-2-postgresql.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,13 @@
+
+
+
+ postgresql7.1
+
+
+
+ select ec_creditcard_id_sequence.nextval
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/credit-card-correction-2.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/credit-card-correction-2.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/credit-card-correction-2.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,50 @@
+
+
+
+
+
+ select order_id from ec_orders where user_session_id=:user_session_id and order_state='in_basket'
+
+
+
+
+
+
+ select order_id from ec_orders where user_id=:user_id and confirmed_date is not null and order_id=(select max(o2.order_id) from ec_orders o2 where o2.user_id=:user_id and o2.confirmed_date is not null)
+
+
+
+
+
+
+ select count(*) from ec_items where order_id=:order_id
+
+
+
+
+
+
+ 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,:cc_fmt , :creditcard_type,:expiry,:billing_zip_code)
+
+
+
+
+
+
+
+ update ec_orders set creditcard_id=:creditcard_id where order_id=:order_id and order_state='in_basket'
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/credit-card-correction.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/credit-card-correction.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/credit-card-correction.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,35 @@
+
+
+
+
+
+ select order_id from ec_orders where user_session_id=:user_session_id and order_state='in_basket'
+
+
+
+
+
+
+ select count(*) from ec_items where order_id=:order_id
+
+
+
+
+
+
+ select user_id from ec_orders where order_id=:order_id
+
+
+
+
+
+
+ select creditcard_type, creditcard_number, creditcard_expire, billing_zip_code from
+ec_creditcards, ec_orders
+where ec_creditcards.creditcard_id=ec_orders.creditcard_id
+and order_id=:order_id
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/finalize-order-oracle.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/finalize-order-oracle.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/finalize-order-oracle.xql 10 Jul 2001 20:42:02 -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/finalize-order-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/finalize-order-postgresql.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/finalize-order-postgresql.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,13 @@
+
+
+
+ postgresql7.1
+
+
+
+ select ec_gift_certificate_balance(:user_id)
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/finalize-order.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/finalize-order.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/finalize-order.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,64 @@
+
+
+
+
+
+ select order_id from ec_orders where user_session_id=:user_session_id and order_state='in_basket'
+
+
+
+
+
+
+
+ select order_id
+ from ec_orders
+ where user_id=:user_id
+ and confirmed_date is not null
+ and order_id=(
+ select max(o2.order_id)
+ from ec_orders o2
+ where o2.user_id=:user_id
+ and o2.confirmed_date is not null
+ )
+
+
+
+
+
+
+
+ select count(*) from ec_items where order_id=:order_id
+
+
+
+
+
+
+ select user_id from ec_orders where order_id=:order_id
+
+
+
+
+
+
+ select shipping_address from ec_orders where order_id=:order_id
+
+
+
+
+
+
+ select creditcard_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/gift-certificate-claim-2-oracle.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/gift-certificate-claim-2-oracle.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/gift-certificate-claim-2-oracle.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,35 @@
+
+
+
+ oracle8.1.6
+
+
+
+ insert into ec_problems_log
+ (problem_id, problem_date, problem_details)
+ values
+ (ec_problem_id_sequence.nextval, sysdate,:prob_details )
+
+
+
+
+
+
+
+ update ec_gift_certificates set user_id=:user_id, claimed_date=sysdate where gift_certificate_id=:gift_certificate_id
+
+
+
+
+
+
+ insert into ec_problems_log
+ (problem_id, problem_date, gift_certificate_id, problem_details)
+ values
+ (ec_problem_id_sequence.nextval, sysdate, :gift_certificate_id, :prob_details)
+
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/gift-certificate-claim-2-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/gift-certificate-claim-2-postgresql.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/gift-certificate-claim-2-postgresql.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,35 @@
+
+
+
+ postgresql7.1
+
+
+
+ insert into ec_problems_log
+ (problem_id, problem_date, problem_details)
+ values
+ (ec_problem_id_sequence.nextval, current_timestamp,:prob_details )
+
+
+
+
+
+
+
+ update ec_gift_certificates set user_id=:user_id, claimed_date=current_timestamp where gift_certificate_id=:gift_certificate_id
+
+
+
+
+
+
+ insert into ec_problems_log
+ (problem_id, problem_date, gift_certificate_id, problem_details)
+ values
+ (ec_problem_id_sequence.nextval, current_timestamp, :gift_certificate_id, :prob_details)
+
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/gift-certificate-claim-2.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/gift-certificate-claim-2.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/gift-certificate-claim-2.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,25 @@
+
+
+
+
+
+ select order_id from ec_orders where user_session_id=:user_session_id and order_state='in_basket'
+
+
+
+
+
+
+ select gift_certificate_id from ec_gift_certificates where claim_check=:claim_check
+
+
+
+
+
+
+ select user_id as gift_certificate_user_id, amount from ec_gift_certificates where gift_certificate_id=:gift_certificate_id
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/gift-certificate-claim.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/gift-certificate-claim.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/gift-certificate-claim.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,11 @@
+
+
+
+
+
+ select order_id from ec_orders where user_session_id=:user_session_id and order_state='in_basket'
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/gift-certificate-finalize-order-oracle.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/gift-certificate-finalize-order-oracle.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/gift-certificate-finalize-order-oracle.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,81 @@
+
+
+
+ oracle8.1.6
+
+
+
+ insert into ec_problems_log
+ (problem_id, problem_date, problem_details, gift_certificate_id)
+ values
+ (ec_problem_id_sequence.nextval, sysdate, 'Customer pushed reload on gift-certificate-finalize-order.tcl but gift_certificate_state wasn't authorized_plus_avs, authorized_minus_avs, failed_authorization, or confirmed',:gift_certificate_id)
+
+
+
+
+
+
+
+ select ec_creditcard_id_sequence.nextval from dual
+
+
+
+
+
+
+ insert into ec_gift_certificates
+ (gift_certificate_id, gift_certificate_state, amount, issue_date, purchased_by, expires, claim_check, certificate_message, certificate_to, certificate_from, recipient_email, last_modified, last_modifying_user, modified_ip_address)
+ values
+ (:gift_certificate_id, 'confirmed', :amount, sysdate, :user_id, add_months(sysdate,:gc_months),:claim_check, :certificate_message, :certificate_to, :certificate_from, :recipient_email, sysdate, :user_id, :peeraddr)
+
+
+
+
+
+
+
+ select ec_transaction_id_sequence.nextval from dual
+
+
+
+
+
+
+ insert into ec_financial_transactions
+ (transaction_id, gift_certificate_id, creditcard_id, transaction_amount, transaction_type, inserted_date)
+ values
+ (:transaction_id, :gift_certificate_id, :creditcard_id, :amount, 'charge', sysdate)
+
+
+
+
+
+
+
+ update ec_financial_transactions set authorized_date=sysdate, to_be_captured_p='t' where transaction_id=:transaction_id
+
+
+
+
+
+
+ update ec_gift_certificates set authorized_date=sysdate, gift_certificate_state=:cc_result where gift_certificate_id=:gift_certificate_id
+
+
+
+
+
+
+ select ec_gift_cert_id_sequence.nextval from dual
+
+
+
+
+
+
+ select round((sysdate-issue_date)*86400) as n_seconds from ec_gift_certificates where gift_certificate_id = :gift_certificate_id
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/gift-certificate-finalize-order-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/gift-certificate-finalize-order-postgresql.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/gift-certificate-finalize-order-postgresql.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,81 @@
+
+
+
+ postgresql7.1
+
+
+
+ insert into ec_problems_log
+ (problem_id, problem_date, problem_details, gift_certificate_id)
+ values
+ (ec_problem_id_sequence.nextval, current_timestamp, 'Customer pushed reload on gift-certificate-finalize-order.tcl but gift_certificate_state wasn't authorized_plus_avs, authorized_minus_avs, failed_authorization, or confirmed',:gift_certificate_id)
+
+
+
+
+
+
+
+ select ec_creditcard_id_sequence.nextval
+
+
+
+
+
+
+ insert into ec_gift_certificates
+ (gift_certificate_id, gift_certificate_state, amount, issue_date, purchased_by, expires, claim_check, certificate_message, certificate_to, certificate_from, recipient_email, last_modified, last_modifying_user, modified_ip_address)
+ values
+ (:gift_certificate_id, 'confirmed', :amount, current_timestamp, :user_id, add_months(current_timestamp,:gc_months),:claim_check, :certificate_message, :certificate_to, :certificate_from, :recipient_email, current_timestamp, :user_id, :peeraddr)
+
+
+
+
+
+
+
+ select ec_transaction_id_sequence.nextval
+
+
+
+
+
+
+ insert into ec_financial_transactions
+ (transaction_id, gift_certificate_id, creditcard_id, transaction_amount, transaction_type, inserted_date)
+ values
+ (:transaction_id, :gift_certificate_id, :creditcard_id, :amount, 'charge', current_timestamp)
+
+
+
+
+
+
+
+ update ec_financial_transactions set authorized_date=current_timestamp, to_be_captured_p='t' where transaction_id=:transaction_id
+
+
+
+
+
+
+ update ec_gift_certificates set authorized_date=current_timestamp, gift_certificate_state=:cc_result where gift_certificate_id=:gift_certificate_id
+
+
+
+
+
+
+ select ec_gift_cert_id_sequence.nextval
+
+
+
+
+
+
+ select round((current_timestamp-issue_date)*86400) as n_seconds from ec_gift_certificates where gift_certificate_id = :gift_certificate_id
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/gift-certificate-finalize-order.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/gift-certificate-finalize-order.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/gift-certificate-finalize-order.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,43 @@
+
+
+
+
+
+ select count(*) from ec_gift_certificates where gift_certificate_id=:gift_certificate_id
+
+
+
+
+
+
+ select gift_certificate_state from ec_gift_certificates where gift_certificate_id=:gift_certificate_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, :ccstuff_1, :creditcard_type,:expiry,:billing_zip_code)
+
+
+
+
+
+
+
+ update ec_financial_transactions set failed_p='t', to_be_captured_p='f' where transaction_id=:transaction_id
+
+
+
+
+
+
+ update ec_gift_certificates set gift_certificate_state='failed_authorization' where gift_certificate_id=:gift_certificate_id
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/gift-certificate-order-2.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/gift-certificate-order-2.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/gift-certificate-order-2.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,16 @@
+
+
+
+
+
+
+ select email
+ from persons, parties
+ where person_id = :mightbe
+ and person_id = party_id
+
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/gift-certificate-order-3.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/gift-certificate-order-3.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/gift-certificate-order-3.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,11 @@
+
+
+
+
+
+ select zip_code from ec_addresses where address_id=(select max(address_id) from ec_addresses where user_id=:user_id)
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/gift-certificate-order-4-oracle.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/Attic/gift-certificate-order-4-oracle.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/gift-certificate-order-4-oracle.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,13 @@
+
+
+
+ oracle8.1.6
+
+
+
+ select ec_gift_cert_id_sequence.nextval from dual
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/gift-certificate-order-4-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/Attic/gift-certificate-order-4-postgresql.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/gift-certificate-order-4-postgresql.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,13 @@
+
+
+
+ postgresql7.1
+
+
+
+ select ec_gift_cert_id_sequence.nextval
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/gift-certificate-order-4.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/gift-certificate-order-4.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/gift-certificate-order-4.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,11 @@
+
+
+
+
+
+ select email from cc_users where user_id=:user_id
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/gift-certificate.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/gift-certificate.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/gift-certificate.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,11 @@
+
+
+
+
+
+ select purchased_by, amount, recipient_email, certificate_to, certificate_from, certificate_message from ec_gift_certificates where gift_certificate_id=:gift_certificate_id
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/index-oracle.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/index-oracle.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/index-oracle.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,13 @@
+
+
+
+ oracle8.1.6
+
+
+
+ select 1 from dual where exists (select 1 from ec_categories)
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/index-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/index-postgresql.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/index-postgresql.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,13 @@
+
+
+
+ postgresql7.1
+
+
+
+ select 1 where exists (select 1 from ec_categories)
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/index.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/index.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/index.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,46 @@
+
+
+
+
+
+ select first_names || ' ' || last_name from cc_users where user_id=:user_id
+
+
+
+
+
+
+ update ec_user_sessions set user_id=:user_id where user_session_id = :user_session_id
+
+
+
+
+
+
+ select p.product_name, p.product_id, p.dirname, r.recommendation_text
+from ec_products_displayable p, ec_product_recommendations r
+where p.product_id=r.product_id
+and category_id is null
+and subcategory_id is null
+and subsubcategory_id is null
+and (r.user_class_id is null or r.user_class_id in (select user_class_id
+ from ec_user_class_user_map
+ where user_id = :user_id
+ $user_class_approved_p_clause))
+and r.active_p='t'
+
+
+
+
+
+
+ select
+p.product_name, p.product_id, p.one_line_description
+from ec_products_searchable p
+where not exists (select 1 from ec_category_product_map m where p.product_id = m.product_id)
+order by p.product_name
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/mailing-list-add-2.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/mailing-list-add-2.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/mailing-list-add-2.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,46 @@
+
+
+
+
+
+ select category_name from ec_categories where category_id=:category_id
+
+
+
+
+
+
+ select category_name from ec_categories where category_id=:category_id
+
+
+
+
+
+
+ select subcategory_name from ec_subcategories where subcategory_id=:subcategory_id
+
+
+
+
+
+
+ select category_name from ec_categories where category_id=:category_id
+
+
+
+
+
+
+ select subcategory_name from ec_subcategories where subcategory_id=:subcategory_id
+
+
+
+
+
+
+ select subsubcategory_name from ec_subsubcategories where subsubcategory_id=:subsubcategory_id
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/mailing-list-add.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/mailing-list-add.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/mailing-list-add.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,53 @@
+
+
+
+
+
+ select first_names || ' ' || last_name as user_name from cc_users where user_id=:user_id
+
+
+
+
+
+
+ select category_name from ec_categories where category_id=:category_id
+
+
+
+
+
+
+ select category_name from ec_categories where category_id=:category_id
+
+
+
+
+
+
+ select subcategory_name from ec_subcategories where subcategory_id=:subcategory_id
+
+
+
+
+
+
+ select category_name from ec_categories where category_id=:category_id
+
+
+
+
+
+
+ select subcategory_name from ec_subcategories where subcategory_id=:subcategory_id
+
+
+
+
+
+
+ select subsubcategory_name from ec_subsubcategories where subsubcategory_id=:subsubcategory_id
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/mailing-list-remove.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/mailing-list-remove.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/mailing-list-remove.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,46 @@
+
+
+
+
+
+ select category_name from ec_categories where category_id=:category_id
+
+
+
+
+
+
+ select category_name from ec_categories where category_id=:category_id
+
+
+
+
+
+
+ select subcategory_name from ec_subcategories where subcategory_id=:subcategory_id
+
+
+
+
+
+
+ select category_name from ec_categories where category_id=:category_id
+
+
+
+
+
+
+ select subcategory_name from ec_subcategories where subcategory_id=:subcategory_id
+
+
+
+
+
+
+ select subsubcategory_name from ec_subsubcategories where subsubcategory_id=:subsubcategory_id
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/payment-oracle.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/payment-oracle.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/payment-oracle.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,17 @@
+
+
+
+ oracle8.1.6
+
+
+
+
+ select ec_order_cost(:order_id) as otppgc,
+ ec_gift_certificate_balance(:user_id) as user_gift_certificate_balance
+ from dual
+
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/payment-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/payment-postgresql.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/payment-postgresql.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,17 @@
+
+
+
+ postgresql7.1
+
+
+
+
+ select ec_order_cost(:order_id) as otppgc,
+ ec_gift_certificate_balance(:user_id) as user_gift_certificate_balance
+
+
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/payment.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/payment.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/payment.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,45 @@
+
+
+
+
+
+
+ select order_id,
+ shipping_address as address_id,
+ user_id as order_owner
+ from ec_orders
+ where user_session_id=:user_session_id
+ and order_state='in_basket'
+
+
+
+
+
+
+
+ select count(*) from ec_items where order_id=:order_id
+
+
+
+
+
+
+ select c.creditcard_id, c.creditcard_type, c.creditcard_last_four, c.creditcard_expire
+ from ec_creditcards c
+ where c.user_id=:user_id
+ and c.creditcard_number is not null
+ and c.failed_p='f'
+ and 0 < (select count(*) from ec_orders o where o.creditcard_id=c.creditcard_id and o.shipping_address=$address_id)
+ order by c.creditcard_id desc
+
+
+
+
+
+
+ select zip_code from ec_addresses where address_id=:address_id
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/process-order-quantity-shipping-oracle.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/process-order-quantity-shipping-oracle.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/process-order-quantity-shipping-oracle.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,38 @@
+
+
+
+ oracle8.1.6
+
+
+
+ select i.item_id, i.product_id, u.offer_code
+from ec_items i,
+(select * from ec_user_session_offer_codes usoc where usoc.user_session_id=:user_session_id) u
+where i.product_id=u.product_id(+)
+and i.order_id=:order_id
+
+
+
+
+
+
+ select nvl(base_shipping_cost,0) from ec_admin_settings
+
+
+
+
+
+
+ select nvl(add_exp_base_shipping_cost,0) from ec_admin_settings
+
+
+
+
+
+
+ select ec_tax(0,:order_shipping_cost,:order_id) from dual
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/process-order-quantity-shipping-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/process-order-quantity-shipping-postgresql.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/process-order-quantity-shipping-postgresql.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,13 @@
+
+
+
+ postgresql7.1
+
+
+
+ select ec_tax(0,:order_shipping_cost,:order_id)
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/process-order-quantity-shipping.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/process-order-quantity-shipping.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/process-order-quantity-shipping.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,117 @@
+
+
+
+
+
+ select order_id from ec_orders where user_session_id=:user_session_id and order_state='in_basket'
+
+
+
+
+
+
+ select count(*) from ec_items where order_id=:order_id
+
+
+
+
+
+
+ select user_id from ec_orders where order_id=:order_id
+
+
+
+
+
+
+ select shipping_address from ec_orders where order_id=:order_id
+
+
+
+
+
+
+ update ec_orders
+set shipping_method=:shipping_method,
+ tax_exempt_p=:tax_exempt_p
+where order_id=:order_id
+
+
+
+
+
+
+ select user_class_id from ec_user_class_user_map where user_id=:user_id $additional_user_class_restriction
+
+
+
+
+
+
+ select default_shipping_per_item, weight_shipping_cost from ec_admin_settings
+
+
+
+
+
+
+ select add_exp_amount_per_item, add_exp_amount_by_weight from ec_admin_settings
+
+
+
+
+
+
+ select usps_abbrev from ec_addresses where address_id=:address_id
+
+
+
+
+
+
+ select tax_rate, shipping_p from ec_sales_tax_by_state where usps_abbrev=:usps_abbrev
+
+
+
+
+
+
+ FIX ME OUTER JOIN
+select i.item_id, i.product_id, u.offer_code
+from ec_items i,
+(select * from ec_user_session_offer_codes usoc where usoc.user_session_id=:user_session_id) u
+where i.product_id=u.product_id(+)
+and i.order_id=:order_id
+
+
+
+
+
+
+ update ec_items set price_charged=round(:price_charged,2), price_name=:price_name, shipping_charged=round(:shipping_charged,2), price_tax_charged=round(:tax_charged,2), shipping_tax_charged=round(:shipping_tax,2) where item_id=:item_id
+
+
+
+
+
+
+ select coalesce(base_shipping_cost,0) from ec_admin_settings
+
+
+
+
+
+
+ select coalesce(add_exp_base_shipping_cost,0) from ec_admin_settings
+
+
+
+
+
+
+ update ec_orders set shipping_charged=round(:order_shipping_cost,2), shipping_tax_charged=round(:tax_on_order_shipping_cost,2) where order_id=:order_id
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/process-payment-oracle.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/process-payment-oracle.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/process-payment-oracle.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,20 @@
+
+
+
+ oracle8.1.6
+
+
+
+ select ec_gift_certificate_balance(:user_id) from dual
+
+
+
+
+
+
+ select ec_creditcard_id_sequence.nextval from dual
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/process-payment-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/process-payment-postgresql.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/process-payment-postgresql.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,20 @@
+
+
+
+ postgresql7.1
+
+
+
+ select ec_gift_certificate_balance(:user_id)
+
+
+
+
+
+
+ select ec_creditcard_id_sequence.nextval
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/process-payment.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/process-payment.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/process-payment.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,78 @@
+
+
+
+
+
+ select order_id from ec_orders where user_session_id=:user_session_id and order_state='in_basket'
+
+
+
+
+
+
+ select count(*) from ec_items where order_id=:order_id
+
+
+
+
+
+
+ select user_id from ec_orders where order_id=:order_id
+
+
+
+
+
+
+ select shipping_address from ec_orders where order_id=:order_id
+
+
+
+
+
+
+ select shipping_method from ec_orders where order_id=:order_id
+
+
+
+
+
+
+ select user_id from ec_creditcards where creditcard_id=:creditcard_id
+
+
+
+
+
+
+ update ec_orders set creditcard_id=:creditcard_id 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,:cc_no , :creditcard_type,:expiry,:billing_zip_code)
+
+
+
+
+
+
+
+ update ec_orders set creditcard_id=:creditcard_id where order_id=:order_id
+
+
+
+
+
+
+ update ec_orders set creditcard_id=null where order_id=:order_id
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/product-oracle.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/product-oracle.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/product-oracle.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,38 @@
+
+
+
+ oracle8.1.6
+
+
+
+
+select *
+ from ec_products p, ec_custom_product_field_values v
+ where p.product_id = :product_id
+ and p.product_id = v.product_id(+)
+
+
+
+
+
+
+
+
+ select * from
+ (select category_id,
+ (select count(*)
+ from ec_subcategories s
+ where s.category_id = m.category_id) subcount,
+ (select count(*)
+ from ec_subsubcategories ss
+ where ss.subcategory_id = m.category_id) subsubcount
+ from ec_category_product_map m
+ where product_id = :product_id
+ order by subcount, subsubcount, category_id)
+ where rownum = 1
+
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/product-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/product-postgresql.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/product-postgresql.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,27 @@
+
+
+
+ postgresql7.1
+
+
+
+ FIX ME ROWNUM
+
+ select * from
+ (select category_id,
+ (select count(*)
+ from ec_subcategories s
+ where s.category_id = m.category_id) subcount,
+ (select count(*)
+ from ec_subsubcategories ss
+ where ss.subcategory_id = m.category_id) subsubcount
+ from ec_category_product_map m
+ where product_id = :product_id
+ order by subcount, subsubcount, category_id)
+ where rownum = 1
+
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/product-search.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/product-search.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/product-search.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,18 @@
+
+
+
+
+
+ insert into ec_user_session_info (user_session_id, category_id, search_text) values (:user_session_id, :category_id, :search_text)
+
+
+
+
+
+
+ select category_name from ec_categories where category_id=:category_id
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/product.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/product.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/product.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,86 @@
+
+
+
+
+
+ insert into ec_user_session_info (user_session_id, product_id) values (:user_session_id, :product_id)
+
+
+
+
+
+
+ select count(*) from ec_user_session_offer_codes where user_session_id=:user_session_id and product_id=:product_id
+
+
+
+
+
+
+ insert into ec_user_session_offer_codes (user_session_id, product_id, offer_code) values (:user_session_id, :product_id, :offer_code)
+
+
+
+
+
+
+ update ec_user_session_offer_codes set offer_code=:offer_code where user_session_id=:user_session_id and product_id=:product_id
+
+
+
+
+
+
+ select offer_code from ec_user_session_offer_codes where user_session_id=:user_session_id and product_id=:product_id
+
+
+
+
+
+
+ FIX ME OUTER JOIN
+
+select *
+ from ec_products p, ec_custom_product_field_values v
+ where p.product_id = :product_id
+ and p.product_id = v.product_id(+)
+
+
+
+
+
+
+
+
+ select template
+ from ec_templates
+ where template_id=:template_id
+
+
+
+
+
+
+
+
+SELECT template
+ FROM ec_templates t, ec_category_template_map ct, ec_category_product_map cp
+ WHERE t.template_id = ct.template_id
+ AND ct.category_id = cp.category_id
+ AND cp.product_id = :product_id
+
+
+
+
+
+
+
+ select template
+ from ec_templates
+ where template_id=(select default_template
+ from ec_admin_settings)
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/review-submit-2-oracle.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/review-submit-2-oracle.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/review-submit-2-oracle.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,23 @@
+
+
+
+ oracle8.1.6
+
+
+
+
+ select product_name,
+ ec_product_comment_id_sequence.nextval as comment_id,
+ user_email,
+ to_char(sysdate,'Day Month DD, YYYY') as current_date
+ from ec_products,
+ (select email as user_email
+ from cc_users
+ where user_id = :user_id)
+ where product_id=:product_id
+
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/review-submit-2-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/review-submit-2-postgresql.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/review-submit-2-postgresql.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,23 @@
+
+
+
+ postgresql7.1
+
+
+
+
+ select product_name,
+ ec_product_comment_id_sequence.nextval as comment_id,
+ user_email,
+ to_char(current_timestamp,'Day Month DD, YYYY') as current_date
+ from ec_products,
+ (select email as user_email
+ from cc_users
+ where user_id = :user_id)
+ where product_id=:product_id
+
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/review-submit-3-oracle.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/review-submit-3-oracle.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/review-submit-3-oracle.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,20 @@
+
+
+
+ oracle8.1.6
+
+
+
+
+ insert into ec_product_comments
+ (comment_id, product_id, user_id, user_comment, one_line_summary,
+ rating, comment_date, last_modified, last_modifying_user, modified_ip_address)
+ values
+ (:comment_id, :product_id, :user_id, :user_comment, :one_line_summary,
+ :rating, sysdate, sysdate, :user_id, :ns_conn_peeraddr)
+
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/review-submit-3-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/review-submit-3-postgresql.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/review-submit-3-postgresql.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,20 @@
+
+
+
+ postgresql7.1
+
+
+
+
+ insert into ec_product_comments
+ (comment_id, product_id, user_id, user_comment, one_line_summary,
+ rating, comment_date, last_modified, last_modifying_user, modified_ip_address)
+ values
+ (:comment_id, :product_id, :user_id, :user_comment, :one_line_summary,
+ :rating, current_timestamp, current_timestamp, :user_id, :ns_conn_peeraddr)
+
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/review-submit-3.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/review-submit-3.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/review-submit-3.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,19 @@
+
+
+
+
+
+
+select product_name,
+ comment_found_p
+from ec_products,
+ (select count(*) as comment_found_p
+ from ec_product_comments
+ where comment_id = :comment_id)
+where product_id = :product_id
+
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/review-submit.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/review-submit.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/review-submit.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,11 @@
+
+
+
+
+
+ select product_name from ec_products where product_id=:product_id
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/shipping-address-2-oracle.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/Attic/shipping-address-2-oracle.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/shipping-address-2-oracle.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,13 @@
+
+
+
+ oracle8.1.6
+
+
+
+ select ec_address_id_sequence.nextval from dual
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/shipping-address-2-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/Attic/shipping-address-2-postgresql.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/shipping-address-2-postgresql.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,13 @@
+
+
+
+ postgresql7.1
+
+
+
+ select ec_address_id_sequence.nextval
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/shipping-address-2.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/Attic/shipping-address-2.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/shipping-address-2.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,29 @@
+
+
+
+
+
+ select order_id from ec_orders where user_session_id=:user_session_id and order_state='in_basket'
+
+
+
+
+
+
+ insert into ec_addresses
+ (address_id, user_id, address_type, attn, line1, line2, city, usps_abbrev, zip_code, country_code, phone, phone_time)
+ values
+ (:address_id, :user_id, 'shipping', :attn, :line1,:line2,:city,:usps_abbrev,:zip_code,'us',:phone,:phone_time)
+
+
+
+
+
+
+
+ update ec_orders set shipping_address=:address_id where order_id=:order_id
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/shipping-address-international-2-oracle.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/Attic/shipping-address-international-2-oracle.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/shipping-address-international-2-oracle.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,13 @@
+
+
+
+ oracle8.1.6
+
+
+
+ select ec_address_id_sequence.nextval from dual
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/shipping-address-international-2-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/Attic/shipping-address-international-2-postgresql.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/shipping-address-international-2-postgresql.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,13 @@
+
+
+
+ postgresql7.1
+
+
+
+ select ec_address_id_sequence.nextval
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/shipping-address-international-2.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/Attic/shipping-address-international-2.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/shipping-address-international-2.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,29 @@
+
+
+
+
+
+ select order_id from ec_orders where user_session_id=:user_session_id and order_state='in_basket'
+
+
+
+
+
+
+ insert into ec_addresses
+ (address_id, user_id, address_type, attn, line1, line2, city, full_state_name, zip_code, country_code, phone, phone_time)
+ values
+ (:address_id, :user_id, 'shipping', :attn, :line1,:line2,:city,: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/shipping-address-international.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/Attic/shipping-address-international.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/shipping-address-international.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,11 @@
+
+
+
+
+
+ select first_names || ' ' || last_name as name from cc_users where user_id=:user_id
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/shipping-address.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/Attic/shipping-address.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/shipping-address.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,11 @@
+
+
+
+
+
+ select first_names || ' ' || last_name as name from cc_users where user_id=:user_id
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/shopping-cart-add-oracle.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/shopping-cart-add-oracle.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/shopping-cart-add-oracle.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,43 @@
+
+
+
+ oracle8.1.6
+
+
+
+ select ec_order_id_sequence.nextval from dual
+
+
+
+
+
+
+ insert into ec_orders
+ (order_id, user_session_id, order_state, in_basket_date)
+ select :order_id, :user_session_id, 'in_basket', sysdate from dual
+ where not exists (select 1 from ec_orders where user_session_id=:user_session_id and order_state='in_basket')
+
+
+
+
+
+
+ insert into ec_problems_log
+ (problem_id, problem_date, problem_details)
+ values
+ (ec_problem_id_sequence.nextval, sysdate,:errormsg)
+
+
+
+
+
+
+ insert into ec_items
+(item_id, product_id, color_choice, size_choice, style_choice, order_id, in_cart_date)
+(select ec_item_id_sequence.nextval, :product_id, :color_choice, :size_choice, :style_choice, :order_id, sysdate from dual
+ where not exists (select 1 from ec_items where order_id=:order_id and product_id=:product_id and color_choice [ec_decode $color_choice "" "is null" "= :color_choice"] and size_choice [ec_decode $size_choice "" "is null" "= :size_choice"] and style_choice [ec_decode $style_choice "" "is null" "= :style_choice"]))
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/shopping-cart-add-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/shopping-cart-add-postgresql.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/shopping-cart-add-postgresql.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,43 @@
+
+
+
+ postgresql7.1
+
+
+
+ select ec_order_id_sequence.nextval
+
+
+
+
+
+
+ insert into ec_orders
+ (order_id, user_session_id, order_state, in_basket_date)
+ select :order_id, :user_session_id, 'in_basket', current_timestamp
+ where not exists (select 1 from ec_orders where user_session_id=:user_session_id and order_state='in_basket')
+
+
+
+
+
+
+ insert into ec_problems_log
+ (problem_id, problem_date, problem_details)
+ values
+ (ec_problem_id_sequence.nextval, current_timestamp,:errormsg)
+
+
+
+
+
+
+ insert into ec_items
+(item_id, product_id, color_choice, size_choice, style_choice, order_id, in_cart_date)
+(select ec_item_id_sequence.nextval, :product_id, :color_choice, :size_choice, :style_choice, :order_id, current_timestamp
+ where not exists (select 1 from ec_items where order_id=:order_id and product_id=:product_id and color_choice [ec_decode $color_choice "" "is null" "= :color_choice"] and size_choice [ec_decode $size_choice "" "is null" "= :size_choice"] and style_choice [ec_decode $style_choice "" "is null" "= :style_choice"]))
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/shopping-cart-add.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/shopping-cart-add.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/shopping-cart-add.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,25 @@
+
+
+
+
+
+ select count(*) from ec_orders where user_session_id=:user_session_id and order_state='confirmed'
+
+
+
+
+
+
+ select order_id from ec_orders where user_session_id=:user_session_id and order_state='in_basket'
+
+
+
+
+
+
+ select order_id from ec_orders where user_session_id=:user_session_id and order_state='in_basket'
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/shopping-cart-delete-from.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/shopping-cart-delete-from.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/shopping-cart-delete-from.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,18 @@
+
+
+
+
+
+ select order_id from ec_orders where user_session_id=:user_session_id and order_state='in_basket'
+
+
+
+
+
+
+ delete from ec_items where order_id=:order_id and product_id=:product_id and color_choice [ec_decode $color_choice "" "is null" "= :color_choice"] and size_choice [ec_decode $size_choice "" "is null" "= :size_choice"] and style_choice [ec_decode $style_choice "" "is null" "= :style_choice"]
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/shopping-cart-oracle.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/shopping-cart-oracle.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/shopping-cart-oracle.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,27 @@
+
+
+
+ oracle8.1.6
+
+
+
+ select p.product_name, p.one_line_description, p.product_id, count(*) as quantity, u.offer_code, i.color_choice, i.size_choice, i.style_choice
+from ec_orders o, ec_items i, ec_products p,
+(select product_id, offer_code from ec_user_session_offer_codes usoc where usoc.user_session_id=:user_session_id) u
+where i.product_id=p.product_id
+and o.order_id=i.order_id
+and p.product_id=u.product_id(+)
+and o.user_session_id=:user_session_id and o.order_state='in_basket'
+group by p.product_name, p.one_line_description, p.product_id, u.offer_code, i.color_choice, i.size_choice, i.style_choice
+
+
+
+
+
+
+ select 1 from dual where exists (select 1 from ec_orders where user_id=:user_id and order_state='in_basket' and saved_p='t')
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/shopping-cart-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/shopping-cart-postgresql.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/shopping-cart-postgresql.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,13 @@
+
+
+
+ postgresql7.1
+
+
+
+ select 1 where exists (select 1 from ec_orders where user_id=:user_id and order_state='in_basket' and saved_p='t')
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/shopping-cart-quantities-change-oracle.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/shopping-cart-quantities-change-oracle.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/shopping-cart-quantities-change-oracle.xql 10 Jul 2001 20:42:02 -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)
+ values
+ (ec_item_id_sequence.nextval, :product_id, :color_choice, :size_choice, :style_choice, :order_id, sysdate)
+
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/shopping-cart-quantities-change-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/shopping-cart-quantities-change-postgresql.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/shopping-cart-quantities-change-postgresql.xql 10 Jul 2001 20:42:02 -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)
+ values
+ (ec_item_id_sequence.nextval, :product_id, :color_choice, :size_choice, :style_choice, :order_id, current_timestamp)
+
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/shopping-cart-quantities-change.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/shopping-cart-quantities-change.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/shopping-cart-quantities-change.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,55 @@
+
+
+
+
+
+ select order_id from ec_orders where order_state='in_basket' and user_session_id=:user_session_id
+
+
+
+
+
+
+
+ select i.product_id,
+ i.color_choice,
+ i.size_choice,
+ i.style_choice,
+ count(*) as r_quantity
+ from ec_orders o,
+ ec_items i
+ where o.order_id=i.order_id
+ and o.user_session_id=:user_session_id
+ and o.order_state='in_basket'
+ group by i.product_id,
+ i.color_choice,
+ i.size_choice,
+ i.style_choice
+
+
+
+
+
+
+
+
+ select max(item_id)
+ from ec_items
+ where product_id=:product_id
+ and color_choice [ec_decode $color_choice "" "is null" "= :color_choice"]
+ and size_choice [ec_decode $size_choice "" "is null" "= :size_choice"]
+ and style_choice [ec_decode $style_choice "" "is null" "= :style_choice"]
+ and order_id=:order_id $extra_condition
+
+
+
+
+
+
+
+ delete from ec_items where item_id in ([join $rows_to_delete ", "])
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/shopping-cart-retrieve-2-oracle.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/shopping-cart-retrieve-2-oracle.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/shopping-cart-retrieve-2-oracle.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,20 @@
+
+
+
+ oracle8.1.6
+
+
+
+ select to_char(o.in_basket_date,'Month DD, YYYY') as formatted_in_basket_date, o.in_basket_date, o.order_id, count(*) as n_products
+from ec_orders o, ec_items i
+where user_id=:user_id
+and order_state='in_basket'
+and saved_p='t'
+and i.order_id=o.order_id
+group by o.order_id, to_char(o.in_basket_date,'Month DD, YYYY'), o.in_basket_date
+order by o.in_basket_date
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/shopping-cart-retrieve-2-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/shopping-cart-retrieve-2-postgresql.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/shopping-cart-retrieve-2-postgresql.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,20 @@
+
+
+
+ postgresql7.1
+
+
+
+ select to_char(o.in_basket_date,'Month DD, YYYY') as formatted_in_basket_date, o.in_basket_date, o.order_id, count(*) as n_products
+from ec_orders o, ec_items i
+where user_id=:user_id
+and order_state='in_basket'
+and saved_p='t'
+and i.order_id=o.order_id
+group by o.order_id, to_char(o.in_basket_date,'Month DD, YYYY'), o.in_basket_date
+order by o.in_basket_date
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/shopping-cart-retrieve-3-oracle.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/shopping-cart-retrieve-3-oracle.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/shopping-cart-retrieve-3-oracle.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,13 @@
+
+
+
+ oracle8.1.6
+
+
+
+ select to_char(in_basket_date,'Month DD, YYYY') as formatted_in_basket_date from ec_orders where order_id=:order_id
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/shopping-cart-retrieve-3-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/shopping-cart-retrieve-3-postgresql.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/shopping-cart-retrieve-3-postgresql.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,13 @@
+
+
+
+ postgresql7.1
+
+
+
+ select to_char(in_basket_date,'Month DD, YYYY') as formatted_in_basket_date from ec_orders where order_id=:order_id
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/shopping-cart-retrieve-3.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/shopping-cart-retrieve-3.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/shopping-cart-retrieve-3.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,190 @@
+
+
+
+
+
+ select count(*) from ec_orders where order_id=:order_id
+
+
+
+
+
+
+ select count(*) from ec_orders where order_id=:order_id and user_id=:user_id
+
+
+
+
+
+
+ select count(*) from ec_orders where order_id=:order_id and order_state='in_basket' and saved_p='t'
+
+
+
+
+
+
+ select p.product_name, p.one_line_description, p.product_id, i.color_choice, i.size_choice, i.style_choice, count(*) as quantity
+ from ec_orders o, ec_items i, ec_products p
+ where i.product_id=p.product_id
+ and o.order_id=i.order_id
+ and o.order_id=:order_id
+ group by p.product_name, p.one_line_description, p.product_id, i.color_choice, i.size_choice, i.style_choice
+
+
+
+
+
+
+ select count(*) from ec_orders where order_state='in_basket' and user_session_id=:user_session_id
+
+
+
+
+
+
+ update ec_orders set user_session_id=:user_session_id, saved_p='f' where order_id=:order_id
+
+
+
+
+
+
+ select o.offer_code, o.product_id
+ from ec_user_sessions s, ec_user_session_offer_codes o, ec_sale_prices_current p
+ where p.offer_code=o.offer_code
+ and s.user_session_id=o.user_session_id
+ and s.user_id=:user_id
+ order by p.sale_price
+
+
+
+
+
+
+ delete from ec_user_session_offer_codes where user_session_id=:user_session_id
+
+
+
+
+
+
+ insert into ec_user_session_offer_codes
+ (user_session_id, product_id, offer_code)
+ values
+ (:user_session_id, :temp_pd, :offprod)
+
+
+
+
+
+
+
+ select order_id from ec_orders where user_session_id=:user_session_id and order_state='in_basket'
+
+
+
+
+
+
+ update ec_items set order_id=:current_basket where order_id=:order_id
+
+
+
+
+
+
+ select o.offer_code, o.product_id
+ from ec_user_sessions s, ec_user_session_offer_codes o, ec_sale_prices_current p
+ where p.offer_code=o.offer_code
+ and s.user_session_id=o.user_session_id
+ and s.user_id=:user_id
+ order by p.sale_price
+
+
+
+
+
+
+ delete from ec_user_session_offer_codes where user_session_id=:user_session_id
+
+
+
+
+
+
+ insert into ec_user_session_offer_codes
+ (user_session_id, product_id, offer_code)
+ values
+ (:user_session_id, :temp_pd, :offprod)
+
+
+
+
+
+
+
+ select order_id from ec_orders where user_session_id=:user_session_id and order_state='in_basket'
+
+
+
+
+
+
+ delete from ec_items where order_id=:current_basket
+
+
+
+
+
+
+ update ec_items set order_id=:current_basket where order_id=:order_id
+
+
+
+
+
+
+ select o.offer_code, o.product_id
+ from ec_user_sessions s, ec_user_session_offer_codes o, ec_sale_prices_current p
+ where p.offer_code=o.offer_code
+ and s.user_session_id=o.user_session_id
+ and s.user_id=:user_id
+ order by p.sale_price
+
+
+
+
+
+
+ delete from ec_user_session_offer_codes where user_session_id=:user_session_id
+
+
+
+
+
+
+ insert into ec_user_session_offer_codes
+ (user_session_id, product_id, offer_code)
+ values
+ (:user_session_id, :temp_pd, :offprod)
+
+
+
+
+
+
+
+ delete from ec_items where order_id=:order_id
+
+
+
+
+
+
+ delete from ec_items where order_id=:order_id
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/shopping-cart-retrieve.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/shopping-cart-retrieve.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/shopping-cart-retrieve.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,11 @@
+
+
+
+
+
+ select first_names || ' ' || last_name as user_name from cc_users where user_id=:user_id
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/shopping-cart-save-2.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/shopping-cart-save-2.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/shopping-cart-save-2.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,12 @@
+
+
+
+
+
+ update ec_orders set user_id=:user_id, user_session_id=null, saved_p='t'
+where user_session_id=:user_session_id and order_state='in_basket'
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/shopping-cart-save.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/shopping-cart-save.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/shopping-cart-save.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,11 @@
+
+
+
+
+
+ select first_names || ' ' || last_name as user_name from cc_users where user_id=:user_id
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/shopping-cart.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/shopping-cart.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/shopping-cart.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,29 @@
+
+
+
+
+
+ select count(*) from
+ec_orders o, ec_items i
+where o.order_id=i.order_id
+and o.user_session_id=:user_session_id and o.order_state='in_basket'
+
+
+
+
+
+
+ FIX ME OUTER JOIN
+select p.product_name, p.one_line_description, p.product_id, count(*) as quantity, u.offer_code, i.color_choice, i.size_choice, i.style_choice
+from ec_orders o, ec_items i, ec_products p,
+(select product_id, offer_code from ec_user_session_offer_codes usoc where usoc.user_session_id=:user_session_id) u
+where i.product_id=p.product_id
+and o.order_id=i.order_id
+and p.product_id=u.product_id(+)
+and o.user_session_id=:user_session_id and o.order_state='in_basket'
+group by p.product_name, p.one_line_description, p.product_id, u.offer_code, i.color_choice, i.size_choice, i.style_choice
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/thank-you.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/thank-you.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/thank-you.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,11 @@
+
+
+
+
+
+ select order_id from ec_orders where user_id=:user_id and confirmed_date is not null and order_id=(select max(o2.order_id) from ec_orders o2 where o2.user_id=$user_id and o2.confirmed_date is not null)
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/track-oracle.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/track-oracle.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/track-oracle.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,15 @@
+
+
+
+ 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/track-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/track-postgresql.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/track-postgresql.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,15 @@
+
+
+
+ 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/track.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/track.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/track.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,13 @@
+
+
+
+
+
+ select user_id from ec_orders o, ec_shipments s
+where o.order_id = s.order_id
+and s.shipment_id = :shipment_id
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/update-user-classes-2.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/update-user-classes-2.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/update-user-classes-2.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,13 @@
+
+
+
+
+
+ select user_class_id
+ from ec_user_class_user_map
+ where user_id = :user_id
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/update-user-classes.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/update-user-classes.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/update-user-classes.xql 10 Jul 2001 20:42:02 -0000 1.1
@@ -0,0 +1,13 @@
+
+
+
+
+
+ select user_class_id
+from ec_user_class_user_map
+where user_id = :user_id
+
+
+
+
+