Index: openacs-4/packages/ecommerce/www/admin/products/extras-upload-2.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/extras-upload-2.adp,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/extras-upload-2.adp 15 Aug 2008 09:50:01 -0000 1.1 @@ -0,0 +1,17 @@ + + @title;noquote@ + @context;noquote@ +

@title@

+ +@doc_body;noquote@ + + +

Done reading @count_html@ rows from @csv_file@, updated(.) @rows_updated@ and inserted(i) @rows_inserted@ product extras! +

+

+(Note: "success" doesn't actually mean that the information was uploaded; it +just means that the database did not choke on it (since updates to tables are considered +successes even if 0 rows are updated). If you need reassurance, spot check some of the individual products.) +

+ + Index: openacs-4/packages/ecommerce/www/admin/products/extras-upload-2.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/extras-upload-2.tcl,v diff -u -r1.6 -r1.7 --- openacs-4/packages/ecommerce/www/admin/products/extras-upload-2.tcl 24 Feb 2005 13:33:15 -0000 1.6 +++ openacs-4/packages/ecommerce/www/admin/products/extras-upload-2.tcl 15 Aug 2008 09:50:01 -0000 1.7 @@ -16,45 +16,37 @@ file_type delimiter } - +set serious_errors 0 ad_require_permission [ad_conn package_id] admin if { [empty_string_p $csv_file] } { ad_return_error "Missing Data File" "You must input the name of the file on your local hard drive." - return + set serious_errors 1 } set user_id [ad_get_user_id] set ip [ns_conn peeraddr] -doc_body_append "[ad_admin_header "Uploading Extras"] +set title "Uploading Extras" +set context [list [list index Products] $title] -

Uploading Extras

- -[ad_context_bar [list "../" "Ecommerce([ec_system_name])"] [list "index.tcl" "Products"] "Uploading Extras"] - -
- -" - set unix_file_name ${csv_file.tmpfile} - # Check that the file is readible if { ![file readable $unix_file_name] } { - doc_body_append "Cannot read file $unix_file_name" - return + append doc_body "Cannot read file $unix_file_name" + set serious_errors 1 } # Check that delimiter is one character, if used if { [string length $delimiter] != 1 && [string eq $file_type "delim"]} { - doc_body_append "Delimiter is not one character long." - return + append doc_body "Delimiter is not one character long." + set serious_errors 1 } -doc_body_append "
-"
-
+set doc_body ""
 set datafilefp [open $unix_file_name]
 set count 0
+set rows_updated 0
+set rows_inserted 0
 set errors 0
 # Continue reading the file till the end but stop when an error
 # occured.
@@ -74,56 +66,77 @@
 while { $line_status != -1 && !$errors} {
     incr count
     if { $count == 1 } {
-	# first time thru, we grab the number of columns and their names
-	set number_of_columns [llength $elements]
-	set columns $elements
-	set product_id_column [lsearch -exact $columns "product_id"]
+        # first time thru, we grab the number of columns and their names
+        set number_of_columns [llength $elements]
+        set columns $elements
+        set product_id_column [lsearch -exact $columns "product_id"]
         # Caroline@meekshome.com Allows you to specify either the product_id or the sku
         set sku_column [lsearch -exact $columns "sku"]
+        ns_log Notice "ecommerce extras-upload-2.tcl: beginning bulk uploading of custom product fields with file $csv_file"
     } else {
-	# this line is a product
-# (this file used to insert rows into ec_custom_product_field_values, but
-# now that is done in upload-2.tcl, so we need to update instead)
-# 	set columns_sql "insert into ec_custom_product_field_values (last_modified, last_modifying_user, modified_ip_address "
-# 	set values_sql " values (sysdate or now(), $user_id, '$ip' "
-# 	for { set i 0 } { $i < $number_of_columns } { incr i } {
-# 	    append columns_sql ", [lindex $columns $i]"
-# 	    append values_sql ", '[DoubleApos [lindex $elements $i]]'"
-# 	}
-# 	set sql "$columns_sql ) $values_sql )"
+        # this line is a product
+        # (this file used to insert rows into ec_custom_product_field_values, but
+        # now that is done in upload-2.tcl, so we need to update instead)
 
-	set sql "update ec_custom_product_field_values set last_modified=sysdate, last_modifying_user=:user_id, modified_ip_address=:ip"
-        set moresql ""
-
+        set sqlupdate "update ec_custom_product_field_values set last_modified=sysdate, last_modifying_user=:user_id, modified_ip_address=:ip"
+        set sqlinsert_columns "insert into ec_custom_product_field_values (last_modified, last_modifying_user, modified_ip_address, product_id"
+        set sqlinsert_values "values (sysdate, :user_id, :ip, :product_id"
+        set moresqlupdate ""
+        set moresqlinsert_columns ""
+        set moresqlinsert_values ""
+        # setup sql for custom field values
         for { set i 0 } { $i < $number_of_columns } { incr i } {
-	    set var_name "var_$i"
-	    set $var_name [lindex $elements $i]
-	    if { $i != $product_id_column && $i != $sku_column} {
-	        append moresql ", [lindex $columns $i]=:$var_name"
-	    }
-	}
+            set var_name "var_$i"
+            set $var_name [lindex $elements $i]
+            set field_name [lindex $columns $i]
+            if { $i != ${product_id_column} && $i != ${sku_column} } {
+                append moresqlupdate ", ${field_name}=:$var_name"
+                append moresqlinsert_columns ", $field_name"
+                append moresqlinsert_values ", :$var_name"
+            }
+        }
+        # find the product and update the custom fields
+        set length_sku 0
+        set product_id 0
         # Caroline@meekshome.com - see if we have a product_id or need to use the sku
         if { $product_id_column > -1 } {
-            # product_id supplied
-            append sql "${moresql} where product_id=:var_$product_id_column"
-            if { [catch {db_dml product_update_with_product_id $sql} errmsg] } {
-	        append bad_products_sql "$sql\n"
-	        doc_body_append "FAILURE! SQL: $sql
 $errmsg\n"
-	    } else {
-	        doc_body_append "Success!
\n"
-	    }
-        } elseif { $sku_column > -1 } {
-            append sql "${moresql} where product_id = (select product_id from ec_products where sku = :var_$sku_column)"
-	    if { [catch {db_dml product_update_with_sku $sql} errmsg] } {
-	        append bad_products_sql "$sql\n"
-	        doc_body_append "FAILURE! SQL: $sql
 $errmsg\n"
-	    } else {
-	        doc_body_append "Success!
\n"
-	    }
-        } else {
-            ad_return_complaint 1 "Each row must either supply the product_id ($product_id_column) or the sku ($sku_column); number_of_columns: $number_of_columns, columns: $columns"
+            set product_id var_${product_id_column}             
+        } elseif { ${sku_column} > -1 && ${product_id} == 0 } {
+            set sku_var var_${sku_column}
+            set sku [expr $$sku_var]
+            # sku supplied, product_id not supplied
+            # still need to test for product_id below because sku might not have a product_id
+            set product_id [db_string get_product_id_from_sku "select product_id from ec_products where sku = :sku" -default "0"]
+            set length_sku [string length $sku]
+        } 
+        if { $product_id > 0 } {
+            # check to see if data exists, or if this will be a new row
+            set custom_product_id [db_string get_custom_field_product_id "select product_id as custom_product_id from ec_custom_product_field_values where product_id = :product_id" -default "0"]            
+            if { $custom_product_id > 0} {
+                set sql "${sqlupdate} ${moresqlupdate} where product_id=:product_id"
+                if { [catch {db_dml product_update_with_product_id $sql} errmsg] } {
+                    append bad_products_sql "$sql\n<"
+                    append doc_body "
FAILURE! SQL: $sql
 $errmsg\n
"
+                } else {
+                    append doc_body ". "
+                    incr rows_updated
+                }
+            } else {
+                # no custom fields exist for this product_id, insert custom fields
+                set sql "${sqlinsert_columns} ${moresqlinsert_columns} ) ${sqlinsert_values} ${moresqlinsert_values} )"
+                if { [catch {db_dml insert_custom_fields_with_product_id $sql} errmsg] } {
+                    append bad_products_sql "$sql\n<"
+                    append doc_body "
FAILURE! SQL: $sql
 $errmsg\n
"
+                } else {
+                    append doc_body "i "
+                    incr rows_inserted
+                }
+            }
+        }  else {
+            # adding ns_log for cases where uploading extends past max input time (config.tcl:recwait)
+            ns_log Notice "While bulk uploading custom fields, cannot obtain an existing product_id for row $count in file $csv_file."
+            append doc_body "
Row $count must supply either product_id ($product_id_column) or the sku ($sku_column); sku length: $length_sku number_of_columns: $number_of_columns,
  row values: $elements
"
         }
-
     }
 
     # read next line of data file, depending on file type, or end read loop if error.
@@ -139,15 +152,8 @@
     }
 
 }
+# post upload report to log in case the connection timed out.
+ns_log Notice "Custom field bulk uploading has read $count lines from ${csv_file}. Updated $rows_updated rows. Inserted $rows_inserted rows."
+append doc_body "
"
+set count_html "[ec_decode $count "0" "0" [expr $count -1]]"
 
-doc_body_append "
-

Done loading [ec_decode $count "0" "0" [expr $count -1]] products extras! - -

- -(Note: \"success\" doesn't actually mean that the information was uploaded; it -just means that the database did not choke on it (since updates to tables are considered -successes even if 0 rows are updated). If you need reassurance, spot check some of the individual products.) -[ad_admin_footer] -" - Index: openacs-4/packages/ecommerce/www/admin/products/extras-upload.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/extras-upload.adp,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/www/admin/products/extras-upload.adp 15 Aug 2008 09:50:01 -0000 1.1 @@ -0,0 +1,48 @@ + + @title;noquote@ + @context;noquote@ +

@title@

+ +
+ +
+Data Filename +
+CSV format
+Tab Delimited format
+Delimited by: (single character).
+
+
+ +
+
+
+ +

Notes:

+ + +

+This page uploads a data file containing product information into the database. The file format should be: +

+
+field_name_1, field_name_2, ... field_name_n
+value_1, value_2, ... value_n +
+

+where the first line contains the actual names of the columns in ec_custom_product_field_values and the remaining lines contain +the values for the specified fields, one line per product. +

+Legal values for field names are the columns in ec_custom_product_field_values +(which are set by you when you add custom database fields): +

+
+
+@doc_body;noquote@
+
+
+

+Note: Some of these fields may be inactive, in which case there +might be no good reason for you to include them in the upload. +Additionally, @undesirable_cols_html;noquote@ are set +automatically and should not appear in the CSV file. +

Index: openacs-4/packages/ecommerce/www/admin/products/extras-upload.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/extras-upload.tcl,v diff -u -r1.4 -r1.5 --- openacs-4/packages/ecommerce/www/admin/products/extras-upload.tcl 24 Feb 2005 13:33:15 -0000 1.4 +++ openacs-4/packages/ecommerce/www/admin/products/extras-upload.tcl 15 Aug 2008 09:50:01 -0000 1.5 @@ -26,79 +26,22 @@ ad_require_permission [ad_conn package_id] admin -doc_body_append "[ad_admin_header "Upload Product Extras"] +set title "Upload Product Extras" +set context [list [list index Products] $title] -

Upload Product Extras

- -[ad_context_bar [list "../" "Ecommerce([ec_system_name])"] [list "index.tcl" "Products"] "Upload Product Extras"] - -
- -
- -
-Data Filename -
-CSV format
-Tab Delimited format
-Delimited by: (single character).
-
-
- -
-
- -

- -Notes: - -

-

- -This page uploads a data file containing product information into the database. The file format should be: -

-

-field_name_1, field_name_2, ... field_name_n
-value_1, value_2, ... value_n -
-

-where the first line contains the actual names of the columns in ec_custom_product_field_values and the remaining lines contain -the values for the specified fields, one line per product. -

-Legal values for field names are the columns in ec_custom_product_field_values -(which are set by you when you add custom database fields): -

-

-
-"
-
 db_with_handle db {
-  for {set i 0} {$i < [ns_column count $db ec_custom_product_field_values]} {incr i} {
-    set col_to_print [ns_column name $db ec_custom_product_field_values $i]
-    set undesirable_cols [list "available_date" "last_modified" "last_modifying_user" "modified_ip_address"]
-    set required_cols [list "product_id"]
-    if { [lsearch -exact $undesirable_cols $col_to_print] == -1 } {
-      doc_body_append "$col_to_print"
-      if { [lsearch -exact $required_cols $col_to_print] != -1 } {
-	doc_body_append " (required, can use sku instead)"
-      }
-      doc_body_append "\n"
+    for {set i 0} {$i < [ns_column count $db ec_custom_product_field_values]} {incr i} {
+        set col_to_print [ns_column name $db ec_custom_product_field_values $i]
+        set undesirable_cols [list "available_date" "last_modified" "last_modifying_user" "modified_ip_address"]
+        set required_cols [list "product_id"]
+        if { [lsearch -exact $undesirable_cols $col_to_print] == -1 } {
+            append doc_body "$col_to_print"
+            if { [lsearch -exact $required_cols $col_to_print] != -1 } {
+                append doc_body " (required, can use sku as key instead)"
+            }
+            append doc_body "\n"
+        }
     }
-  }
 }
 
-doc_body_append "
-
-

-Note: Some of these fields may be inactive, in which case there -might be no good reason for you to include them in the upload. -Additionally, [join $undesirable_cols ", "] are set -automatically and should not appear in the CSV file. - -

- -
- -[ad_admin_footer] - -" +set undesirable_cols_html [join $undesirable_cols ", "]