Index: openacs-4/packages/ecommerce/www/category-browse-subcategory.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/category-browse-subcategory.adp,v diff -u -r1.9 -r1.10 --- openacs-4/packages/ecommerce/www/category-browse-subcategory.adp 28 Apr 2008 12:28:04 -0000 1.9 +++ openacs-4/packages/ecommerce/www/category-browse-subcategory.adp 28 Apr 2008 14:46:26 -0000 1.10 @@ -6,28 +6,46 @@ t
- + - + - +

Browse:

We recommend:

- @recommendations@ + + + + + + + + +
+ + + + @recommendations.product_name@ +

@recommendations.recommendation_text;noquote@

+ 		@recommendations.price_line;noquote@
+
- +

@category_name@ > @subcategory_name@ products:

Index: openacs-4/packages/ecommerce/www/category-browse-subcategory.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/category-browse-subcategory.tcl,v diff -u -r1.10 -r1.11 --- openacs-4/packages/ecommerce/www/category-browse-subcategory.tcl 28 Apr 2008 12:28:04 -0000 1.10 +++ openacs-4/packages/ecommerce/www/category-browse-subcategory.tcl 28 Apr 2008 14:46:26 -0000 1.11 @@ -19,7 +19,7 @@ subcategory_id:optional,naturalnum subsubcategory_id:optional,naturalnum {how_many:naturalnum {[ad_parameter -package_id [ec_id] ProductsToDisplayPerPage ecommerce]}} - {start:naturalnum "0"} + {start:naturalnum "1"} usca_p:optional } @@ -42,7 +42,9 @@ # see if they're logged in set user_id [ad_conn user_id] if { $user_id != 0 } { - set user_name [db_string get_user_name "select first_names || ' ' || last_name from cc_users where user_id=:user_id"] + acs_user::get -user_id $user_id -array user_info + set user_name "$user_info(first_names) $user_info(last_name)" + } else { set user_name "" } @@ -60,65 +62,65 @@ ec_create_new_session_if_necessary [export_url_vars category_id subcategory_id subsubcategory_id how_many start] cookies_are_not_required # type4 +# this is expensive - do we really need to log this every time? can we write to a file for batched reading later? if { [string compare $user_session_id "0"] != 0 } { db_dml grab_new_session_id " insert into ec_user_session_info (user_session_id, category_id) values (:user_session_id, :category_id) " } -set category_name [db_string get_category_name "select category_name from ec_categories where category_id=:category_id"] +set category_name [db_string -cache_key "ec-category_name-${category_id}" get_category_name "select category_name from ec_categories where category_id=:category_id"] set subcategory_name "" if { [have subcategory_id] } { - set subcategory_name [db_string get_subcat_name "select subcategory_name from ec_subcategories where subcategory_id=:subcategory_id"] + set subcategory_name [db_string -cache_key "ec-subcategory_name-${subcategory_id}" get_subcat_name "select subcategory_name from ec_subcategories where subcategory_id=:subcategory_id"] } set subsubcategory_name "" if { [have subsubcategory_id] } { - set subsubcategory_name [db_string get_subsubcat_name "select subsubcategory_name from ec_subsubcategories where subsubcategory_id=:subsubcategory_id"] + set subsubcategory_name [db_string -cache_key "ec-subsubcategory_name-${subsubcategory_id}" get_subsubcat_name "select subsubcategory_name from ec_subsubcategories where subsubcategory_id=:subsubcategory_id"] } #============================== # recommendations # Recommended products in this category -set recommendations {
} - if { [ad_parameter -package_id [ec_id] UserClassApproveP ecommerce] } { set user_class_approved_p_clause "and user_class_approved_p = 't'" } else { set user_class_approved_p_clause "" } -db_foreach get_recommended_products " - select p.product_id, p.product_name, p.dirname, r.recommendation_text, o.offer_code - from ec_product_recommendations r, ec_products_displayable p left outer join ec_user_session_offer_codes o on (p.product_id = o.product_id and user_session_id = :user_session_id) - 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" { +# when caching the db results, it must be limited to the (sub)category and also the user +# since per-user prices are calculated +# TODO: actually, it could be by offer code, which would allow less caching +upvar 0 ${sub}category_id cat_id +set cache_key_prefix "ec-${sub}category-browse-products-${cat_id}-${user_id}" +ns_log Notice "using cache_key_prefix: $cache_key_prefix" - append recommendations " - - - - - " -} +db_multirow -extend { + product_url price_line thumbnail_url thumbnail_width thumbnail_height + } -cache_key ${cache_key_prefix}-recommendations recommendations get_recommended_products "see xql" { + set price_line [ec_price_line $product_id $user_id $offer_code] + set product_url [export_vars -base product -override { product_id $product_id }] -if {[string equal $recommendations {
[ec_linked_thumbnail_if_it_exists $dirname "f" "t"] $product_name -

$recommendation_text

- 		[ec_price_line $product_id $user_id $offer_code]
}]} { - set recommendations "" -} else { - append recommendations "
" -} + if {[array exists thumbnail_info]} { + unset thumbnail_info + } + array set thumbnail_info [ecommerce::resource::image_info -type Thumbnail -product_id $product_id -dirname $dirname] + if {[array size thumbnail_info]} { + set thumbnail_url $thumbnail_info(url) + set thumbnail_width $thumbnail_info(width) + set thumbnail_height $thumbnail_info(height) + } else { + # must blank them out, otherwise they would still be in scope + set thumbnail_url "" + set thumbnail_width "" + set thumbnail_height "" + } + } #============================== # products @@ -136,16 +138,13 @@ " } -set count 0 -# TODO: memoize -# NOTE: careful if you do cache this since the code block calculates per-user specials, and also change implementation of count db_multirow -extend { thumbnail_url thumbnail_height thumbnail_width price_line - } products get_regular_product_list "sql in db specific xql files" { + } -cache_key ${cache_key_prefix}-products products get_regular_product_list "sql in db specific xql files" { if {[array exists thumbnail_info]} { unset thumbnail_info @@ -164,15 +163,14 @@ set price_line [ec_price_line $product_id $user_id $offer_code] - incr count } # what if start is < how many? shouldn't happen I guess... if { $start >= $how_many } { set prev_url [export_vars -base [ad_conn url] -override {{start {[expr $start - $how_many]}}} {category_id subsubcategory_id subcategory_id how_many}] } -set how_many_more [expr $count - $start - $how_many + 1] +set how_many_more [expr ${products:rowcount} - $start - $how_many + 1] if { $how_many_more > 0 } { set next_url [export_vars -base [ad_conn url] -override {{start {[expr $start + $how_many]}}} {category_id subsubcategory_id subcategory_id how_many}] @@ -191,20 +189,12 @@ set subcategories "" if { ![at_bottom_level_p] } { - db_foreach get_subcategories " -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 -" { - ns_log notice "category-browse-subcategory.tcl: loop" - append subcategories "
  • [eval "ident \$sub${sub}category_name"]" - } + db_multirow -extend { url name } -cache_key "ec-${sub}subcategories-${cat_id}" subcategories get_subcategories "see xql" { + set url [export_vars -base "category-browse-sub${sub}category" {category_id subcategory_id subsubcategory_id}] + set name [eval "ident \$sub${sub}category_name"] ; # what is this ident? + } + } set the_category_id $category_id Index: openacs-4/packages/ecommerce/www/category-browse.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/category-browse.adp,v diff -u -r1.10 -r1.11 --- openacs-4/packages/ecommerce/www/category-browse.adp 28 Apr 2008 12:28:04 -0000 1.10 +++ openacs-4/packages/ecommerce/www/category-browse.adp 28 Apr 2008 14:46:26 -0000 1.11 @@ -5,33 +5,44 @@ t - + - + - +

    @category_name@ > subcategories

    - > @subcategories.name@
     -

    We recommend:

    - @recommendations;noquote@ - + + + + + + + + +
    + + + + @recommendations.product_name@ +

    @recommendations.recommendation_text;noquote@

    +     		@recommendations.price_line;noquote@
    - +

    @category_name@ items:

    @@ -64,7 +75,7 @@ - + There are currently no items listed in this category.  Please check back often for updates. Index: openacs-4/packages/ecommerce/www/category-browse.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/category-browse.tcl,v diff -u -r1.13 -r1.14 --- openacs-4/packages/ecommerce/www/category-browse.tcl 28 Apr 2008 12:28:04 -0000 1.13 +++ openacs-4/packages/ecommerce/www/category-browse.tcl 28 Apr 2008 14:46:26 -0000 1.14 @@ -73,7 +73,8 @@ # see if they're logged in set user_id [ad_conn user_id] if { $user_id != 0 } { - set user_name [db_string get_full_name "select first_names || ' ' || last_name from cc_users where user_id=:user_id"] + acs_user::get -user_id $user_id -array user_info + set user_name "$user_info(first_names) $user_info(last_name)" } else { set user_name "" } @@ -90,6 +91,7 @@ ec_create_new_session_if_necessary [export_url_vars category_id subcategory_id subsubcategory_id how_many start] cookies_are_not_required +# this is expensive - do we really need to log this every time? can we write to a file for batched reading later? if { [string compare $user_session_id "0"] != 0 } { db_dml grab_new_session_id " insert into ec_user_session_info @@ -98,22 +100,22 @@ (:user_session_id, :category_id)" } -set category_name [db_string get_category_name " +set category_name [db_string -cache_key "ec-category_name-${category_id}" get_category_name " select category_name from ec_categories where category_id=:category_id"] set subcategory_name "" if [have subcategory_id] { - set subcategory_name [db_string get_subcat_name " + set subcategory_name [db_string -cache_key "ec-subcategory_name-${subcategory_id}" get_subcat_name " select subcategory_name from ec_subcategories where subcategory_id = :subcategory_id"] } set subsubcategory_name "" if [have subsubcategory_id] { - set subsubcategory_name [db_string get_subsubcat_name " + set subsubcategory_name [db_string get_subsubcat_name -cache_key "ec-subsubcategory_name-${subsubcategory_id}" " select subsubcategory_name from ec_subsubcategories where subsubcategory_id = :subsubcategory_id"] @@ -124,41 +126,42 @@ # Recommended products in this category -set recommendations {
    } - if { [ad_parameter -package_id [ec_id] UserClassApproveP ecommerce] } { set user_class_approved_p_clause "and user_class_approved_p = 't'" } else { set user_class_approved_p_clause "" } -db_foreach get_recommended_products " - select p.product_id, p.product_name, p.dirname, r.recommendation_text, o.offer_code - from ec_product_recommendations r, ec_products_displayable p left outer join ec_user_session_offer_codes o on (p.product_id = o.product_id and user_session_id = :user_session_id) - 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" { +# when caching the db results, it must be limited to the (sub)category and also the user +# since per-user prices are calculated +# TODO: actually, it could be by offer code, which would allow less caching +upvar 0 ${sub}category_id cat_id +set cache_key_prefix "ec-${sub}category-browse-products-${cat_id}-${user_id}" +ns_log Notice "using cache_key_prefix: $cache_key_prefix" - append recommendations " - - - - - " -} -if {[string equal $recommendations {
    [ec_linked_thumbnail_if_it_exists $dirname "f" "t"]$product_name -

    $recommendation_text

    -     		[ec_price_line $product_id $user_id $offer_code]
    }]} { - set recommendations "" -} else { - append recommendations "
    " -} +db_multirow -extend { + product_url price_line thumbnail_url thumbnail_width thumbnail_height + } -cache_key ${cache_key_prefix}-recommendations recommendations get_recommended_products "see xql" { + set price_line [ec_price_line $product_id $user_id $offer_code] + set product_url [export_vars -base product -override { product_id $product_id }] + if {[array exists thumbnail_info]} { + unset thumbnail_info + } + array set thumbnail_info [ecommerce::resource::image_info -type Thumbnail -product_id $product_id -dirname $dirname] + if {[array size thumbnail_info]} { + set thumbnail_url $thumbnail_info(url) + set thumbnail_width $thumbnail_info(width) + set thumbnail_height $thumbnail_info(height) + } else { + # must blank them out, otherwise they would still be in scope + set thumbnail_url "" + set thumbnail_width "" + set thumbnail_height "" + } + } + + #============================== # products @@ -172,16 +175,9 @@ and c.${sub}category_id = :${sub}category_id)" } -set count 0 - -# TODO: memoize -# NOTE: careful if you do cache this since the code block calculates per-user specials, and also change implementation of count db_multirow -extend { - thumbnail_url - thumbnail_height - thumbnail_width - price_line - } products get_regular_product_list "sql in db specific xql files" { + thumbnail_url thumbnail_height thumbnail_width price_line + } -cache_key ${cache_key_prefix}-products products get_regular_product_list "sql in db specific xql files" { if {[array exists thumbnail_info]} { unset thumbnail_info @@ -200,15 +196,14 @@ set price_line [ec_price_line $product_id $user_id $offer_code] - incr count } # what if start is < how many? shouldn't happen I guess... if { $start >= $how_many } { set prev_url [export_vars -base [ad_conn url] -override {{start {[expr $start - $how_many]}}} {category_id subsubcategory_id how_many}] } -set how_many_more [expr $count - $start - $how_many + 1] +set how_many_more [expr ${products:rowcount} - $start - $how_many + 1] if { $how_many_more > 0 } { set next_url [export_vars -base [ad_conn url] -override {{start {[expr $start + $how_many]}}} {category_id subsubcategory_id how_many}] @@ -225,19 +220,12 @@ #============================== # subcategories -set subcategories_p 0 if ![at_bottom_level_p] { - db_multirow -extend { url name } subcategories get_subcategories "see xql" { + db_multirow -extend { url name } -cache_key "ec-${sub}subcategories-${cat_id}" subcategories get_subcategories "see xql" { set url [export_vars -base "category-browse-sub${sub}category" {category_id subcategory_id subsubcategory_id}] - set name [eval "ident \$sub${sub}category_name"] + set name [eval "ident \$sub${sub}category_name"] ; # what is this ident? } - - if { ${subcategories:rowcount} > 0 } { - set subcategories_p 1 - # ident? what is this?? - } - } set the_category_id [eval "ident \$${sub}category_id"] Index: openacs-4/packages/ecommerce/www/index.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/index.adp,v diff -u -r1.9 -r1.10 --- openacs-4/packages/ecommerce/www/index.adp 28 Apr 2008 08:16:31 -0000 1.9 +++ openacs-4/packages/ecommerce/www/index.adp 28 Apr 2008 14:46:26 -0000 1.10 @@ -14,9 +14,23 @@ - +

    We recommend:

    - @recommendations_if_there_are_any;noquote@ + + + + + + + + +
    + + + + @recommendations.product_name@ +

    @recommendations.recommendation_text;noquote@

    +     		@recommendations.price_line;noquote@
     Index: openacs-4/packages/ecommerce/www/index.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/index.tcl,v diff -u -r1.8 -r1.9 --- openacs-4/packages/ecommerce/www/index.tcl 1 Mar 2005 00:01:30 -0000 1.8 +++ openacs-4/packages/ecommerce/www/index.tcl 28 Apr 2008 14:46:26 -0000 1.9 @@ -22,7 +22,8 @@ set user_id [ad_conn user_id] if { $user_id != 0 } { - set user_name [db_string get_user_name "select first_names || ' ' || last_name from cc_users where user_id=:user_id"] + acs_user::get -user_id $user_id -array user_info + set user_name "$user_info(first_names) $user_info(last_name)" } else { set user_name "" } @@ -38,57 +39,59 @@ set user_session_id [ec_get_user_session_id] -ec_create_new_session_if_necessary "" cookies_are_not_required +# if for some reason the user id on the session is incorrect, make a new session +# perhaps the old update was an attempt to fix the lose cart on register? anyway +# that is solved correctly now -# Log the user as the user_id for this session -if { $user_is_logged_on && ![string equal $user_session_id "0"]} { - db_dml update_session_user_id "update ec_user_sessions set user_id=:user_id where user_session_id = :user_session_id" +# TODO: test if this can be removed entirely +if { $user_is_logged_on && $user_session_id ne "0"} { + if {[db_string check_session_user_id "select user_id from ec_user_sessions where user_session_id = :user_session_id"] != $user_session_id} { + set $user_session_id 0 ; # which will force creation of a new session below + } } +ec_create_new_session_if_necessary "" cookies_are_not_required + set ec_user_string "" set register_url "/register?return_url=[ns_urlencode [ec_url]]" # the base url allows us to switch connections to http from https if currently an https connection # for saving computing SSL resources only when necessary set base_url "[ec_insecurelink [ad_conn url]]" -set recommendations_if_there_are_any "" - if [ad_parameter -package_id [ec_id] UserClassApproveP ecommerce] { set user_class_approved_p_clause "and user_class_approved_p = 't'" } else { set user_class_approved_p_clause "" } -db_foreach get_produc_recs " - select p.product_id, p.product_name, p.dirname, r.recommendation_text, o.offer_code - from ec_product_recommendations r, ec_products_displayable p left outer join ec_user_session_offer_codes o on (p.product_id = o.product_id and user_session_id = :user_session_id) - 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'" { +# must restrict cache per user due to offer pricing +# TODO: actually, it could be by offer code, which would allow less caching +set recommendations_cache_key "ec-index-recommendations-${user_id}" - append recommendations_if_there_are_any " -
    - - - - - " -} -if {[string equal $recommendations_if_there_are_any "
    [ec_linked_thumbnail_if_it_exists $dirname "f" "t"]$product_name -

    $recommendation_text

    -     		[ec_price_line $product_id $user_id $offer_code]
    "]} { - set recommendations_if_there_are_any "" -} else { - append recommendations_if_there_are_any "
    " -} +db_multirow -extend { + product_url price_line thumbnail_url thumbnail_width thumbnail_height + } -cache_key $recommendations_cache_key recommendations get_produc_recs "see xql" { + set price_line [ec_price_line $product_id $user_id $offer_code] + set product_url [export_vars -base product -override { product_id $product_id }] + if {[array exists thumbnail_info]} { + unset thumbnail_info + } + array set thumbnail_info [ecommerce::resource::image_info -type Thumbnail -product_id $product_id -dirname $dirname] + if {[array size thumbnail_info]} { + set thumbnail_url $thumbnail_info(url) + set thumbnail_width $thumbnail_info(width) + set thumbnail_height $thumbnail_info(height) + } else { + # must blank them out, otherwise they would still be in scope + set thumbnail_url "" + set thumbnail_width "" + set thumbnail_height "" + } + } + +# TODO: templatize and cache product list set products "" set have_how_many_more_p f Index: openacs-4/packages/ecommerce/www/admin/products/index.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/index.adp,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/index.adp 28 Apr 2008 14:46:26 -0000 1.1 @@ -0,0 +1,40 @@ + +@page_title@ +@context_bar;noquote@ + +t + +

    @page_title@

    + + Index: openacs-4/packages/ecommerce/www/admin/products/upload-utilities.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/upload-utilities.adp,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/upload-utilities.adp 28 Apr 2008 14:46:26 -0000 1.1 @@ -0,0 +1,61 @@ + +@page_title@ +@context_bar;noquote@ + +t + +

    @page_title@

    + +These utilities help you load catalog data into the database: + +
      +

    • Product Loader + uploads a data file that contains one line per product + in your catalog. Each line has fields corresponding to a subset of the + columns in the ec_products table. The first line of the data file is a + header that defines which fields are being loaded and the order that + they appear in the data file. The remaining lines contain the product + data. +

      • +

    • Product Extras Loader + is similar to the product loader except it + loads data into ec_custom_product_field_values, the table which contains + the values for each product of the custom fields you have added. + The file format resembles the products import data file format. +

      • +

    • Product Category Map Loader By Name-Matching + creates the mappings between products in ec_products table + and categories in ec_categories, ec_subcategories, and ec_subsubcategories tables + by inserting rows into ec_category_product_map and ec_subcategory_product_map.) The + data file you create for uploading should consist of product sku and + category or subcategory names, one per row. This process attempts to be + smart by using the SQL "LIKE" function to resolve close matches between + categories listed in the data file and those known in the database. +

      • +

    • Product Category Map Loader By Index Matching + creates the product-categories mappings by finding exact matches to the + category tables' indexes category_id, subcategory_id, subsubcategory_id. +

      • + +

    • + Bulk Product Image Loader imports product images for existing + products, and creates thumbnails for them. + Place product images in a set of directories accessible by this server. Then use this utility + to upload a file that maps product sku to the location of the product images + full_imagepathname. +

      • + +
    + +

    + Note: Products need to be loaded and their extra fields need to be defined before + loading product extras. +

    +

    + Note: Categories and subcategories need to be created before using + a Product Category Map Loader. +

    +

    + Note: There may be a peak load condition on the server for an extended period + when bulk loading of product images create product thumbnails (optional feature). +