-" +# 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.- -[ad_admin_footer] -"
\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. - -