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