" +# Get the name of the transfered CSV file set unix_file_name ${csv_file.tmpfile} - +# Check that the file is readable. if { ![file readable $unix_file_name] } { doc_body_append "Cannot read file $unix_file_name" return } -set csvfp [open $unix_file_name] +# Accept only field names that exist in the ec_product table and +# are not set automatically like creation_date. +set legal_field_names {sku product_name one_line_description detailed_description search_keywords price no_shipping_avail_p shipping shipping_additional weight present_p active_p url template_id stock_status color_list size_list style_list email_on_purchase_list} +# Check each entry in the CSV for the following required fields. +# These fields are required so that we can check if a product already +# in the products table and should be update rather than created. +set required_field_names {sku product_name} + +# Initialize each legal field name as the CSV file might not mention +# each and every one of them. +foreach legal_field_name $legal_field_names { + set $legal_field_name "" +} + +# Start reading. +set csvfp [open $unix_file_name] set count 0 +set errors 0 set success_count 0 -while { [ns_getcsv $csvfp elements] != -1 } { +# Continue reading the file till the end but stop when an error occured. +while { [ns_getcsv $csvfp elements] != -1 && !$errors} { incr count if { $count == 1 } { - # first time through, we grab the number of columns and their names - set number_of_columns [llength $elements] - set columns $elements - # These 2 lines added 1999-08-08 - set product_id_column [lsearch -exact $columns "product_id"] - set product_name_column [lsearch -exact $columns "product_name"] + + # First row, grab the field names and their number + set field_names $elements + set number_of_fields [llength $elements] + + # Check the field names against the list of legal names + foreach field_name $field_names { + if {[lsearch -exact $legal_field_names $field_name] == -1} { + incr errors + doc_body_append "FAILURE! $field_name is not an allowed field name.
" + } + } } else { - # this line is a product - # All this directory stuff added 1999-08-08 - # To be consistent with directory-creation that occurs when a - # product is added, dirname will be the first four letters - # (lowercase) of the product_name followed by the product_id - # (for uniqueness) - regsub -all {[^a-zA-Z]} [lindex $elements $product_name_column] "" letters_in_product_name - set letters_in_product_name [string tolower $letters_in_product_name] - if [catch {set dirname "[string range $letters_in_product_name 0 3][lindex $elements $product_id_column]"}] { - #maybe there aren't 4 letters in the product name - set dirname "$letters_in_product_name[lindex $elements $product_id_column]" + # Subsequent rows, thus a product + + # Reset the required fields to NULL so that we can later check if + # the CSV file gave them a value. + foreach required_field_name $required_field_names { + set $required_field_name "" } - - set columns_sql "insert into ec_products (creation_date, available_date, dirname, last_modified, last_modifying_user, modified_ip_address " - set values_sql " values (sysdate, sysdate, :dirname, sysdate, :user_id, :ip " - for { set i 0 } { $i < $number_of_columns } { incr i } { - append columns_sql ", [lindex $columns $i]" - set var_name "val_$i" - set $var_name [lindex $elements $i] - append values_sql ", :$var_name" + + # Assign the values in the CSV to the field names. + for { set i 0 } { $i < $number_of_fields } { incr i } { + set [lindex $field_names $i] [lindex $elements $i] } - set sql "$columns_sql ) $values_sql )" - # we have to also write a row into ec_custom_product_field_values - # for consistency with add*.tcl (added 1999-08-08) - db_transaction { - - if { [catch {db_dml product_insert $sql} errmsg] } { - doc_body_append "FAILURE! SQL: $sql
$number_of_columns
$product_id_column
$product_name_column
\n" - } else { - incr success_count - if { [catch {db_dml custom_product_field_insert "insert into ec_custom_product_field_values (product_id, last_modified, last_modifying_user, modified_ip_address) values (:val_$product_id_column, sysdate, :user_id, :peeraddr)" } errmsg] } { - doc_body_append "FAILURE! Insert into ec_custom_product_field_values failed for product_id=[set val_$product_id_column]
\n" + # Check if all the required fields have been given a value + foreach required_field_name $required_field_names { + if {[set $required_field_name] == ""} { + incr errors } + } + + # Create or update the product if all the required fields + # were given values. + if {!$errors} { + + # Check if there is already product with the give sku. + # Set product_id to NULL so that ACS picks a unique id if there no + # product with the gicen sku. + set product_id [db_string product_check {select product_id from ec_products where sku = :sku;} -default ""] + if { $product_id != ""} { + + # We found a product_id for the given sku, let's update the product. + if { [catch {db_dml product_update " + update ec_products set + user_id = :user_id, + product_name = :product_name, + price = :price, + one_line_description = :one_line_description, + detailed_description = :detailed_description, + search_keywords = :search_keywords, + present_p = :present_p, + stock_status = :stock_status, + now(), + color_list = :color_list, + size_list = :size_list, + style_list = :style_list, + email_on_purchase_list = :email_on_purchase_list, + url = :url, + no_shipping_avail_p = :no_shipping_avail_p, + shipping = :shipping, + shipping_additional = :shipping_additional, + weight = :weight, + active_p = 't', + template_id = :template_id + where product_id = :product_id; + "} errmsg] } { + doc_body_append "FAILURE! Product update of $product_name failed with error:<\p>
$errmsg
" + } else { + doc_body_append "Updated $product_name
" + } + } else { + + # Generate a product_id + set product_id [db_nextval acs_object_id_seq] + + # Dirname will be the first four letters + # (lowercase) of the product_name followed by the product_id + # (for uniqueness) + + regsub -all {[^a-zA-Z]} $product_name "" letters_in_product_name + set letters_in_product_name [string tolower $letters_in_product_name] + if [catch {set dirname "[string range $letters_in_product_name 0 3]$product_id"}] { + #maybe there aren't 4 letters in the product name + set dirname "${letters_in_product_name}$product_id" + } + + # Get the directory where dirname is stored + set subdirectory "[ec_data_directory][ec_product_directory][ec_product_file_directory $product_id]" + ec_assert_directory $subdirectory + + set full_dirname "$subdirectory/$dirname" + ec_assert_directory $full_dirname + + # There is no product with sku :sku so create a new product. + if { [catch {db_exec_plsql product_insert " + select ec_product__new( + :product_id, + :user_id, + :context_id, + :product_name, + :price, + :sku, + :one_line_description, + :detailed_description, + :search_keywords, + :present_p, + :stock_status, + :dirname, + now(), + :color_list, + :size_list, + :peeraddr + )"} errmsg] } { + doc_body_append "FAILURE! Product creation of $product_name failed with error:<\p>$errmsg
" + } else { + doc_body_append "Created $product_name
" + } + + if { [catch {db_dml product_insert_2 " + update ec_products set + style_list = :style_list, + email_on_purchase_list = :email_on_purchase_list, + url = :url, + no_shipping_avail_p = :no_shipping_avail_p, + shipping = :shipping, + shipping_additional = :shipping_additional, + weight = :weight, + active_p = 't', + template_id = :template_id + where product_id = :product_id; + "} errmsg] } { + doc_body_append "FAILURE! Product update of new product $product_name failed with error:<\p>$errmsg
" + } } + # Product line is completed, increase counter + incr success_count + } + } +} +# while { [ns_getcsv $csvfp elements] != -1 } { +# incr count +# if { $count == 1 } { - # Get the directory where dirname is stored - set subdirectory "[ec_data_directory][ec_product_directory][ec_product_file_directory [lindex $elements $product_id_column]]" - ec_assert_directory $subdirectory +# # first time through, we grab the number of columns and their names +# set number_of_columns [llength $elements] +# set columns $elements - set full_dirname "$subdirectory/$dirname" - ec_assert_directory $full_dirname - } - } -} +# # These 2 lines added 1999-08-08 +# set product_id_column [lsearch -exact $columns "product_id"] +# set product_name_column [lsearch -exact $columns "product_name"] + +# # Grap all column names +# set column_names $elements +# } else { +# # this line is a product + +# # All this directory stuff added 1999-08-08 +# # To be consistent with directory-creation that occurs when a +# # product is added, dirname will be the first four letters +# # (lowercase) of the product_name followed by the product_id +# # (for uniqueness) +# regsub -all {[^a-zA-Z]} [lindex $elements $product_name_column] "" letters_in_product_name +# set letters_in_product_name [string tolower $letters_in_product_name] +# if [catch {set dirname "[string range $letters_in_product_name 0 3][lindex $elements $product_id_column]"}] { +# #maybe there aren't 4 letters in the product name +# set dirname "$letters_in_product_name[lindex $elements $product_id_column]" +# } + +# set columns_sql "insert into ec_products (creation_date, available_date, dirname, last_modified, last_modifying_user, modified_ip_address " +# set values_sql " values (now(), now(), :dirname, now(), :user_id, :ip " +# for { set i 0 } { $i < $number_of_columns } { incr i } { +# append columns_sql ", [lindex $columns $i]" +# set var_name [lindex $column_names $i] +# set $var_name [lindex $elements $i] +# append values_sql ", :$var_name" +# } +# set sql "$columns_sql ) $values_sql )" + +# # we have to also write a row into ec_custom_product_field_values +# # for consistency with add*.tcl (added 1999-08-08) +# db_transaction { + +# # Insert the first 16 fields. A limitation of Postgres prevends us from inserting +# # all fields in one swoop. +# if { [catch {db_dml product_insert $sql} errmsg] } { +# doc_body_append "FAILURE! SQL: $sql
$number_of_columns
$product_id_column
$product_name_column
\n" +# } else { + +# # Successfully created a new product, time to add the missing fields. +# if { [catch {db_dml product_update $sql} errmsg] } { +# doc_body_append "FAILURE! SQL: produt_update
\n" +# } else { + +# # New product is complete, increase counter and add customer product field. +# incr success_count +# if { [catch {db_dml custom_product_field_insert "insert into ec_custom_product_field_values (product_id, last_modified, last_modifying_user, modified_ip_address) values (:val_$product_id_column, now(), :user_id, :peeraddr)" } errmsg] } { +# doc_body_append "FAILURE! Insert into ec_custom_product_field_values failed for product_id=[set val_$product_id_column]
\n" +# } +# } +# } + +# # Get the directory where dirname is stored +# set subdirectory "[ec_data_directory][ec_product_directory][ec_product_file_directory [lindex $elements $product_id_column]]" +# ec_assert_directory $subdirectory + +# set full_dirname "$subdirectory/$dirname" +# ec_assert_directory $full_dirname +# } +# } +# } + if { $success_count == 1 } { set product_string "product" } else { Index: openacs-4/packages/ecommerce/www/admin/products/upload.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/upload.tcl,v diff -u -r1.2 -r1.3 --- openacs-4/packages/ecommerce/www/admin/products/upload.tcl 23 Oct 2001 05:58:58 -0000 1.2 +++ openacs-4/packages/ecommerce/www/admin/products/upload.tcl 24 Nov 2001 03:40:55 -0000 1.3 @@ -88,7 +88,7 @@ " set undesirable_cols [list "dirname" "creation_date" "available_date" "last_modified" "last_modifying_user" "modified_ip_address"] -set required_cols [list "product_id" "product_name"] +set required_cols [list "sku" "product_name"] db_with_handle db {