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.8 -r1.9 --- openacs-4/packages/ecommerce/www/category-browse-subcategory.adp 28 Apr 2008 08:45:54 -0000 1.8 +++ openacs-4/packages/ecommerce/www/category-browse-subcategory.adp 28 Apr 2008 12:28:04 -0000 1.9 @@ -34,7 +34,11 @@ - + + + + + @products.product_name@ 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.9 -r1.10 --- openacs-4/packages/ecommerce/www/category-browse-subcategory.tcl 28 Apr 2008 08:16:31 -0000 1.9 +++ openacs-4/packages/ecommerce/www/category-browse-subcategory.tcl 28 Apr 2008 12:28:04 -0000 1.10 @@ -146,26 +146,36 @@ thumbnail_width price_line } products get_regular_product_list "sql in db specific xql files" { - - array set thumbnail_info [ecommerce::resource::image_info -type Thumbnail -product_id $product_id -dirname $dirname] - set thumbnail_url $thumbnail_info(url) - set thumbnail_width $thumbnail_info(width) - set thumbnail_height $thumbnail_info(height) - set price_line [ec_price_line $product_id $user_id $offer_code] + 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 "" + } - incr count -} + 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 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] 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}] + set next_url [export_vars -base [ad_conn url] -override {{start {[expr $start + $how_many]}}} {category_id subsubcategory_id subcategory_id how_many}] if { $how_many_more >= $how_many } { set how_many_next $how_many Index: openacs-4/packages/ecommerce/www/category-browse-subsubcategory.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/category-browse-subsubcategory.adp,v diff -u -r1.6 -r1.7 --- openacs-4/packages/ecommerce/www/category-browse-subsubcategory.adp 28 Apr 2008 08:45:54 -0000 1.6 +++ openacs-4/packages/ecommerce/www/category-browse-subsubcategory.adp 28 Apr 2008 12:28:04 -0000 1.7 @@ -24,7 +24,11 @@ - + + + + + @products.product_name@ Index: openacs-4/packages/ecommerce/www/category-browse-subsubcategory.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/category-browse-subsubcategory.tcl,v diff -u -r1.8 -r1.9 --- openacs-4/packages/ecommerce/www/category-browse-subsubcategory.tcl 28 Apr 2008 08:16:31 -0000 1.8 +++ openacs-4/packages/ecommerce/www/category-browse-subsubcategory.tcl 28 Apr 2008 12:28:04 -0000 1.9 @@ -144,25 +144,35 @@ price_line } products get_regular_product_list "sql in db specific xql files" { - array set thumbnail_info [ecommerce::resource::image_info -type Thumbnail -product_id $product_id -dirname $dirname] - set thumbnail_url $thumbnail_info(url) - set thumbnail_width $thumbnail_info(width) - set thumbnail_height $thumbnail_info(height) + 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 "" + } - set price_line [ec_price_line $product_id $user_id $offer_code] + set price_line [ec_price_line $product_id $user_id $offer_code] - incr count -} + 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 prev_url [export_vars -base [ad_conn url] -override {{start {[expr $start - $how_many]}}} {category_id subcategory_id subsubcategory_id how_many}] } set how_many_more [expr $count - $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}] + set next_url [export_vars -base [ad_conn url] -override {{start {[expr $start + $how_many]}}} {category_id subcategory_id subsubcategory_id how_many}] if { $how_many_more >= $how_many } { set how_many_next $how_many 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.9 -r1.10 --- openacs-4/packages/ecommerce/www/category-browse.adp 28 Apr 2008 08:45:54 -0000 1.9 +++ openacs-4/packages/ecommerce/www/category-browse.adp 28 Apr 2008 12:28:04 -0000 1.10 @@ -38,7 +38,11 @@ - + + + + + @products.product_name@ 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.12 -r1.13 --- openacs-4/packages/ecommerce/www/category-browse.tcl 28 Apr 2008 08:16:31 -0000 1.12 +++ openacs-4/packages/ecommerce/www/category-browse.tcl 28 Apr 2008 12:28:04 -0000 1.13 @@ -182,17 +182,27 @@ thumbnail_width price_line } products get_regular_product_list "sql in db specific xql files" { - - array set thumbnail_info [ecommerce::resource::image_info -type Thumbnail -product_id $product_id -dirname $dirname] - set thumbnail_url $thumbnail_info(url) - set thumbnail_width $thumbnail_info(width) - set thumbnail_height $thumbnail_info(height) - set price_line [ec_price_line $product_id $user_id $offer_code] + 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 "" + } - incr count -} + 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}] Index: openacs-4/packages/ecommerce/www/admin/products/categories-upload-2.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/categories-upload-2.adp,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/categories-upload-2.adp 28 Apr 2008 12:28:04 -0000 1.1 @@ -0,0 +1,14 @@ + +@page_title@ +@context_bar;noquote@ + +t + +

@page_title@

+ +
+@result_html;noquote@ + +

Done loading @success_count@ @category_string@ out of @count@.

+
+ Index: openacs-4/packages/ecommerce/www/admin/products/categories-upload-2.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/categories-upload-2.tcl,v diff -u -r1.6 -r1.7 --- openacs-4/packages/ecommerce/www/admin/products/categories-upload-2.tcl 9 Mar 2004 00:59:51 -0000 1.6 +++ openacs-4/packages/ecommerce/www/admin/products/categories-upload-2.tcl 28 Apr 2008 12:28:04 -0000 1.7 @@ -22,119 +22,102 @@ set user_id [ad_get_user_id] set ip [ns_conn peeraddr] -doc_body_append "[ad_admin_header "Uploading Category Mappings"] +set page_title "Uploading Category Mappings" +set context_bar [template::adp_parse [acs_root_dir]/packages/[ad_conn package_key]/www/contextbar "Uploading Category Mappings"] -[ad_context_bar [list "../" "Ecommerce([ec_system_name])"] [list "index.tcl" "Products"] "Uploading Categories"] -
+# Get the name of the transfered data file -

Uploading Category Mappings

+set unix_file_name ${csv_file.tmpfile} -
-" +# Check that the file is readable. -set csv_file_name ${csv_file.tmpfile} +if { ![file readable $unix_file_name] } { + doc_body_append "Cannot read file $unix_file_name" + return +} -set csvfp [open $csv_file_name] +set datafilefp [open $unix_file_name] set count 0 set success_count 0 -while { [ec_gets_char_delimited_line $csvfp elements] != -1 } { +set result_html "" + +# nb: no header line +while { [ns_getcsv $datafilefp elements] != -1 } { incr count - # this line is a product + set sku [lindex $elements 0] -# upload category id from file - set category [lindex $elements 1] -# upload subcategory id from file - set subcategory [lindex $elements 2] -# upload subsubcategory id from file - set subsubcategory [lindex $elements 3] + set category_matches [lrange $elements 1 end] - # Check if there is a product with the give sku. - # Set product_id to NULL if there is no - # product with the given sku. + # Set product_id to NULL if there is no product with the given sku. set product_id [db_string product_check {select product_id from ec_products where sku = :sku;} -default ""] - # Modified by RH on 05/05/03 - # Match the product with the given (sub)(sub)category. if { $product_id != "" } { + foreach category_match_uc $category_matches { + set category_match [string tolower $category_match_uc] - # see if this matches any subsubcategories - set subsubmatch_p 0 - db_foreach subsubcategories_select " - select c.category_id, c.category_name, s.subcategory_id, s.subcategory_name, ss.subsubcategory_id, ss.subsubcategory_name from ec_subsubcategories ss, ec_subcategories s, ec_categories c - where c.category_id = s.category_id - and s.subcategory_id = ss.subcategory_id - and :subsubcategory = ss.subsubcategory_id" { -# previous line changed to directly match category id -# so that there is no mistake in placement of product to categories - set subsubmatch_p 1 + set success 0 - # add this product to the matched subsubcategory - set sql "insert into ec_subsubcategory_product_map (product_id, subsubcategory_id, publisher_favorite_p, last_modified, last_modifying_user, modified_ip_address) values (:product_id, :subsubcategory_id, 'f', sysdate, :user_id, :ip)" - if { [catch {db_dml subsubcategory_insert $sql} errmsg] } { - #error, probably already loaded this one - } else { - doc_body_append "Matched $category to subsubcategory $subsubcategory_name in subcategory $subcategory_name, category $category_name.
\n" - } + set subsubcategory_id 0 + set subcategory_id 0 + set category_id 0 - # now add it to the subcategory that owns this subsubcategory - set sql "insert into ec_subcategory_product_map (product_id, subcategory_id, publisher_favorite_p, last_modified, last_modifying_user, modified_ip_address) values (:product_id, :subcategory_id, 'f', sysdate, :user_id, :ip)" - if { [catch {db_dml subcategory_insert $sql} errmsg] } { - #error, probably already loaded this one - } + # see if this matches any subsubcategories + if {[db_0or1row find_subsubcategory "see xql"]} { + # we will set all 3 + } elseif {[db_0or1row find_subcategory "see xql"]} { + # set only category and sub + } elseif {[db_0or1row find_category "see xql"]} { + # set only category + } - # now add it to the category that owns this subcategory - set sql "insert into ec_category_product_map (product_id, category_id, publisher_favorite_p, last_modified, last_modifying_user, modified_ip_address) values (:product_id, :category_id, 'f', sysdate, :user_id, :ip)" - if { [catch {db_dml unused_sub $sql} errmsg] } { - #error, probably already loaded this one - } - } + append result_html "sku: $sku, category match: \"$category_match\" :: " - # see if this matches any subcategories - set submatch_p 0 - db_foreach subcategories_select " - select c.category_id, c.category_name, s.subcategory_id, - s.subcategory_name from ec_subcategories s, ec_categories c - where c.category_id = s.category_id - and :subcategory = subcategory_id" { -# previous line changed to directly match category id -# so that there is no mistake in placement of product to categories - set submatch_p 1 - - # add this product to the matched subcategory - set sql "insert into ec_subcategory_product_map (product_id, subcategory_id, publisher_favorite_p, last_modified, last_modifying_user, modified_ip_address) values (:product_id, :subcategory_id, 'f', sysdate, :user_id, :ip)" - if { [catch {db_dml subcategory_insert $sql} errmsg] } { - #error, probably already loaded this one - } else { - doc_body_append "Matched $category to subcategory $subcategory_name in category $category_name
\n" - } - # now add it to the category that owns this subcategory - set sql "insert into ec_category_product_map (product_id, category_id, publisher_favorite_p, last_modified, last_modifying_user, modified_ip_address) values (:product_id, :category_id, 'f', sysdate, :user_id, :ip)" - if { [catch {db_dml unused_sub $sql} errmsg] } { - #error, probably already loaded this one - } - } + if {$subsubcategory_id} { + if { [catch {db_dml subsubcategory_insert ""} errmsg] } { + #error, probably already loaded this one + append result_html " already matched to subsubcategory $subsubcategory_name; " + } else { + append result_html " matched to subsubcategory $subsubcategory_name; " + set success 1 + } + } - # see if this matches any categories - set match_p 0 - db_foreach category_match_select "select category_id, category_name from ec_categories where :category = category_id" { -# previous line changed to directly match category id -# so that there is no mistake in placement of product to categories - set match_p 1 - set sql "insert into ec_category_product_map (product_id, category_id, publisher_favorite_p, last_modified, last_modifying_user, modified_ip_address) values (:product_id, :category_id, 'f', sysdate, :user_id, :ip)" - if { [catch {db_dml category_insert $sql} errmsg] } { - #error, probably already loaded this one - } else { - doc_body_append "Matched $category to category $category_name
\n" - } - } - if { ! ($match_p || $submatch_p || $subsubmatch_p) } { - doc_body_append "Datafile row $count : Could not find matching category reference for item (sku: $sku) with category ref: $category : $subcategory : $subsubcategory
\n" - } else { - incr success_count - } + if {$subcategory_id} { + if { [catch {db_dml subcategory_insert ""} errmsg] } { + #error, probably already loaded this one + append result_html " already matched to subcategory $subcategory_name; " + } else { + append result_html " matched to subcategory $subcategory_name; " + set success 1 + } + } + + if {$category_id} { + if { [catch {db_dml category_insert ""} errmsg] } { + #error, probably already loaded this one + append result_html " already matched to category $category_name." + } else { + append result_html " matched to category $category_name." + set success 1 + } + } + + if { ! $subsubcategory_id && ! $subcategory_id && ! $category_id } { + append result_html " could not find matching category" + } + + if { $success } { + incr success_count + } + + append result_html "
\n" + + } + } else { + append result_html "could not find matching sku for: $sku
\n" } } @@ -144,9 +127,3 @@ set category_string "category mappings" } -doc_body_append "

Done loading $success_count $category_string out of $count. - -

- -[ad_admin_footer] -" Index: openacs-4/packages/ecommerce/www/admin/products/categories-upload-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/categories-upload-2.xql,v diff -u -r1.4 -r1.5 --- openacs-4/packages/ecommerce/www/admin/products/categories-upload-2.xql 24 Feb 2005 13:33:15 -0000 1.4 +++ openacs-4/packages/ecommerce/www/admin/products/categories-upload-2.xql 28 Apr 2008 12:28:04 -0000 1.5 @@ -8,30 +8,30 @@ - + select c.category_id, c.category_name, s.subcategory_id, s.subcategory_name, ss.subsubcategory_id, ss.subsubcategory_name from ec_subsubcategories ss, ec_subcategories s, ec_categories c where c.category_id = s.category_id and s.subcategory_id = ss.subcategory_id - and :subsubcategory = subsubcategory_id + and :category_match = lower(subsubcategory_name) - + select c.category_id, c.category_name, s.subcategory_id, s.subcategory_name from ec_subcategories s, ec_categories c where c.category_id = s.category_id - and :subcategory = subcategory_id + and :category_match = lower(subcategory_name) - + select category_id, category_name from ec_categories - where :category = category_id + where :category_match = lower(category_name) Index: openacs-4/packages/ecommerce/www/admin/products/categories-upload.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/categories-upload.adp,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/categories-upload.adp 28 Apr 2008 12:28:04 -0000 1.1 @@ -0,0 +1,34 @@ + +@page_title@ +@context_bar;noquote@ + +t + +

@page_title@

+ +
+ +
+CSV Filename +

+

+ +
+
+ +

Notes:

+ +
+ + +

This page uploads a CSV file containing product category names and creates product category mappings.

+ +

The file format should made up of lines where the first column is a sku and all subsequent columns are names +which match one of a subsubcategory, a subcategory or a category (matched in that order). Category +matching is case insensitive.

+ +

If a subcategory match is found, the product is placed into the matching subcategory as well as the parent category +of the matching subcategory. If no match is found for a product, no mapping entry is made.

+ +
+
Index: openacs-4/packages/ecommerce/www/admin/products/categories-upload.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/categories-upload.tcl,v diff -u -r1.5 -r1.6 --- openacs-4/packages/ecommerce/www/admin/products/categories-upload.tcl 9 Mar 2004 00:59:51 -0000 1.5 +++ openacs-4/packages/ecommerce/www/admin/products/categories-upload.tcl 28 Apr 2008 12:28:04 -0000 1.6 @@ -35,54 +35,7 @@ ad_require_permission [ad_conn package_id] admin -doc_body_append "[ad_admin_header "Upload Category Mapping Data"] +set page_title "Upload Category Mapping Data" +set context_bar [template::adp_parse [acs_root_dir]/packages/[ad_conn package_key]/www/contextbar "Upload Category Mapping Data"] -

Upload Catalog Category Mapping Data

-[ad_context_bar [list "../" "Ecommerce([ec_system_name])"] [list "index.tcl" "Products"] "Upload Category Mapping Data"] - -
- -
- -
-CSV Filename -

-

- -
-
- -

- -Notes: - -

-

- -This page uploads a CSV file containing product category \"hints\" and creates product category mappings. -This is probably not generally useful for people who have clean data... -

-The file format should be: -

-

-sku_1, category_description_1
-sku_2, category_description_2
-...
-sku_n, category_description_n -
-

-Where each line contains a sku, category name pair. There may be multiple lines for a single sku -which will cause the product to get placed in multiple categories (or subcategories) -

-This program attempts to match the category name to an existing category using looses matching (SQL: like) -because some data is really nasty with lots of different formats for similar categories. -

-If a subcategory match is found, the product is placed into the matching subcategory as well as the parent category -of the matching subcategory. If no match is found for a product, no mapping entry is made. - -

-
- -[ad_admin_footer] -"