Index: openacs-4/packages/ecommerce/tcl/ecommerce-procs.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/tcl/ecommerce-procs.xql,v diff -u -r1.5 -r1.6 --- openacs-4/packages/ecommerce/tcl/ecommerce-procs.xql 22 Oct 2001 08:02:10 -0000 1.5 +++ openacs-4/packages/ecommerce/tcl/ecommerce-procs.xql 26 May 2002 04:36:49 -0000 1.6 @@ -1,414 +1,357 @@ + - - - - select package_id from apm_packages - where package_key = 'ecommerce' - - - + + + select package_id from apm_packages + where package_key = 'ecommerce' + + + + + + select subcategory_id + from ec_subcategories + where category_id = :category_id + and subcategory_id in ([join $subcategory_list ", "]) + order by subcategory_name + + - - - - - select subcategory_id from ec_subcategories where category_id = :category_id and subcategory_id in ([join $subcategory_list ", "]) order by subcategory_name - - - + + + select category_name + from ec_categories + where category_id = :category_id + + - - - - - select category_name from ec_categories where category_id = :category_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 subcategory_name from ec_subcategories where subcategory_id = :subcategory_id - - - + + + select subsubcategory_name + from ec_subsubcategories + where subcategory_id = :subcategory_id + and subsubcategory_id in ([join $subsubcategory_list ","]) + order by subsubcategory_name + + - - - - - select subsubcategory_name from ec_subsubcategories where subcategory_id = :subcategory_id and subsubcategory_id in ([join $subsubcategory_list ","]) order by subsubcategory_name - - - + + + select product_name + from ec_products + where product_id = :product_id + + - - - - - select product_name from ec_products where product_id = :product_id - - - + + + select subcategory_id + from ec_subsubcategories + where subsubcategory_id = :subsubcategory_id + + - - - - - select subcategory_id from ec_subsubcategories where subsubcategory_id = :subsubcategory_id - - - + + + select category_id + from ec_subcategories + where subcategory_id = :subcategory_id + + - - - - - select category_id from ec_subcategories where subcategory_id = :subcategory_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 subcategory_name from ec_subcategories where subcategory_id = :subcategory_id - - + + + select subsubcategory_name + from ec_subsubcategories + where subsubcategory_id = :subsubcategory_id + + - - - - select subsubcategory_name from ec_subsubcategories where subsubcategory_id = :subsubcategory_id - - + + + select category_id + from ec_subcategories + where subcategory_id = :subcategory_id + + - - - - - select category_id from ec_subcategories where subcategory_id = :subcategory_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 subcategory_name from ec_subcategories where subcategory_id = :subcategory_id - - + + + select category_name + from ec_categories + where category_id = :category_id + + - - - - select category_name from ec_categories where category_id = :category_id - - + + + select category_id + from ec_category_product_map + where product_id = :product_id + + - - - - - select category_id from ec_category_product_map where product_id = :product_id - - - + + + select s.subcategory_id + from ec_subcategory_product_map m, ec_subcategories s + where m.subcategory_id = s.subcategory_id + and s.category_id = :category_id + and m.product_id = :product_id + + - - - - - select s.subcategory_id - from ec_subcategory_product_map m, - ec_subcategories s - where m.subcategory_id = s.subcategory_id - and s.category_id = :category_id - and m.product_id = :product_id - - - + + + select ss.subsubcategory_id + from ec_subsubcategory_product_map m, ec_subsubcategories ss + where m.subsubcategory_id = ss.subsubcategory_id + and ss.subcategory_id = :subcategory_id + and m.product_id = :product_id + + - - - - - select ss.subsubcategory_id - from ec_subsubcategory_product_map m, - ec_subsubcategories ss - where m.subsubcategory_id = ss.subsubcategory_id - and ss.subcategory_id = :subcategory_id - and m.product_id = :product_id - - - + + + select p.product_id, p.product_name + from ec_products_displayable p, ec_product_links l + where l.product_a = :product_id + and l.product_b = p.product_id + + - - - - - select p.product_id, p.product_name from ec_products_displayable p, ec_product_links l where l.product_a = :product_id and l.product_b = p.product_id - - - + + + select publication, author_name, review_date, review + from ec_product_reviews + where product_id = :product_id + and display_p = 't' + + - - - - - select publication, author_name, review_date, review from ec_product_reviews where product_id = :product_id and display_p = 't' - - - + + + select avg(rating) + from ec_product_comments + where product_id = :product_id + and approved_p = 't' + + - - - - - select avg(rating) from ec_product_comments where product_id = :product_id and approved_p = 't' - - - + + + select count(*) + from ec_product_comments + where product_id = :product_id + and (approved_p='t' [ec_decode [util_memoize {ad_parameter -package_id [ec_id] ProductCommentsNeedApprovalP ecommerce} [ec_cache_refresh]] "0" "or approved_p is null" ""]) + + - - - - - select count(*) from ec_product_comments where product_id = :product_id and (approved_p='t' [ec_decode [util_memoize {ad_parameter -package_id [ec_id] ProductCommentsNeedApprovalP ecommerce} [ec_cache_refresh]] "0" "or approved_p is null" ""]) - - - + + + select category_id, category_name + from ec_categories + order by sort_key + + - - - - - select category_id, category_name from ec_categories order by sort_key - - - + + + select user_id as correct_user_id + from ec_orders + where order_id = :order_id + + - - - - - select user_id as correct_user_id from ec_orders where order_id = :order_id - - - + + + select o.confirmed_date, o.creditcard_id, o.shipping_method, + u.email, o.shipping_address as shipping_address_id, c.billing_address as billing_address_id + from ec_orders o + left join cc_users u on (o.user_id = u.user_id) + left join ec_creditcards c on (o.creditcard_id = c.creditcard_id) + where o.order_id = :order_id + + - - - + + + select i.price_name, i.price_charged, i.color_choice, i.size_choice, i.style_choice, p.product_name, p.one_line_description, p.product_id, count(*) as quantity + from ec_items i, ec_products p + where i.order_id = :order_id + and i.product_id = p.product_id + group by p.product_name, p.one_line_description, p.product_id, i.price_name, i.price_charged, i.color_choice, i.size_choice, i.style_choice + + - select eco.confirmed_date, eco.creditcard_id, eco.shipping_method, - u.email, - eca.line1, eca.line2, eca.city, eca.usps_abbrev, eca.zip_code, eca.country_code, - eca.full_state_name, eca.attn, eca.phone, eca.phone_time - from ec_orders eco - LEFT JOIN cc_users u on (eco.user_id = u.user_id) - LEFT JOIN ec_addresses eca on (eco.shipping_address = eca.address_id) - where eco.order_id = :order_id - - - + + + select i.price_charged, i.price_name, i.color_choice, i.size_choice, i.style_choice, p.product_name, p.one_line_description, p.product_id, count(*) as quantity + from ec_items i, ec_products p + where i.order_id = :order_id + and i.product_id = p.product_id + group by p.product_name, p.one_line_description, p.product_id, i.price_charged, i.price_name, i.color_choice, i.size_choice, i.style_choice + + - - - - - select i.price_name, i.price_charged, i.color_choice, i.size_choice, i.style_choice, - p.product_name, p.one_line_description, p.product_id, - count(*) as quantity - from ec_items i, - ec_products p - where i.order_id = :order_id - and i.product_id = p.product_id - group by p.product_name, p.one_line_description, p.product_id, - i.price_name, i.price_charged, i.color_choice, i.size_choice, i.style_choice - - - + + + select i.price_charged, i.price_name, i.color_choice, i.size_choice, i.style_choice, p.product_name, p.one_line_description, p.product_id, count(*) as quantity + from ec_items i, ec_products p + where i.order_id = :order_id + and i.product_id = p.product_id + group by p.product_name, p.one_line_description, p.product_id, + i.price_charged, i.price_name, i.color_choice, i.size_choice, i.style_choice + + - - - - - select i.price_charged, i.price_name, i.color_choice, i.size_choice, i.style_choice, - p.product_name, p.one_line_description, p.product_id, - count(*) as quantity - from ec_items i, - ec_products p - where i.order_id = :order_id - and i.product_id = p.product_id - group by p.product_name, p.one_line_description, p.product_id, - i.price_charged, i.price_name, i.color_choice, i.size_choice, i.style_choice - - - + + + select count(*) + from $item_view + where order_id = :order_id + + - - - - - select i.price_charged, i.price_name, i.color_choice, i.size_choice, i.style_choice, - p.product_name, p.one_line_description, p.product_id, - count(*) as quantity - from ec_items i, - ec_products p - where i.order_id = :order_id - and i.product_id = p.product_id - group by p.product_name, p.one_line_description, p.product_id, - i.price_charged, i.price_name, i.color_choice, i.size_choice, i.style_choice - - - + + + select o.order_id, o.confirmed_date, o.order_state + from ec_orders o + where o.user_id = :user_id + order by o.order_id + + - - - - select count(*) from $item_view where order_id = :order_id - - + + + select * + from ec_product_purchase_comb + where product_id = :product_id + + - - - - - select o.order_id, o.confirmed_date, o.order_state - from ec_orders o - where o.user_id = :user_id - order by o.order_id - - - + + + select product_name + from ec_products + where product_id = :product_id + + - - - - - select * from ec_product_purchase_comb where product_id = :product_id - - - + + + select product_name + from ec_products + where product_id = :product_id + + - - - - - select product_name from ec_products where product_id = :product_id - - - + + + select s.shipment_date, s.carrier, s.tracking_number, s.shipment_id, s.shippable_p, count(*) as n_items + from ec_items i, ec_shipments s + where i.order_id = :order_id + and i.shipment_id = s.shipment_id + and i.product_id = :product_id + and i.color_choice [ec_decode $color_choice "" "is null" "= :color_choice"] + and i.size_choice [ec_decode $size_choice "" "is null" "= :size_choice"] + and i.style_choice [ec_decode $style_choice "" "is null" "= :style_choice"] + and i.price_charged [ec_decode $price_charged "" "is null" "= :price_charged"] + and i.price_name [ec_decode $price_name "" "is null" "= :price_name"] + group by s.shipment_date, s.carrier, s.tracking_number, s.shipment_id, s.shippable_p + + - - - - - select product_name from ec_products where product_id = :product_id - - - + + + select response_id, one_line, response_text + from ec_canned_responses + order by one_line + + - - - - - select s.shipment_date, s.carrier, s.tracking_number, s.shipment_id, s.shippable_p, count(*) as n_items - from ec_items i, - ec_shipments s - where i.order_id = :order_id - and i.shipment_id = s.shipment_id - and i.product_id = :product_id - and i.color_choice [ec_decode $color_choice "" "is null" "= :color_choice"] - and i.size_choice [ec_decode $size_choice "" "is null" "= :size_choice"] - and i.style_choice [ec_decode $style_choice "" "is null" "= :style_choice"] - and i.price_charged [ec_decode $price_charged "" "is null" "= :price_charged"] - and i.price_name [ec_decode $price_name "" "is null" "= :price_name"] - group by s.shipment_date, s.carrier, s.tracking_number, s.shipment_id, s.shippable_p - - - + + + select c.user_class_name, m.user_class_approved_p, c.user_class_id + from ec_user_classes c, ec_user_class_user_map m + where m.user_id = :user_id + and m.user_class_id = c.user_class_id + order by c.user_class_id + + - - - - - select response_id, one_line, response_text - from ec_canned_responses - order by one_line - - - + + + update ec_user_sessions + set user_id = :user_id + where user_session_id = :user_session_id + + - - - - - select c.user_class_name, m.user_class_approved_p, c.user_class_id - from ec_user_classes c, ec_user_class_user_map m - where m.user_id = :user_id - and m.user_class_id = c.user_class_id - order by c.user_class_id - - - + + + select state_name + from us_states + where abbrev = :usps_abbrev + + - - - - - update ec_user_sessions - set user_id = :user_id - where user_session_id = :user_session_id - - - - - - - - - select state_name from us_states where abbrev =:usps_abbrev - - - - - - - - - select default_name from countries where iso=:country_code - - - - - + + + select default_name + from countries + where iso = :country_code + + +