Index: openacs-4/packages/ecommerce/www/admin/products/extras-upload-2-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/extras-upload-2-oracle.xql,v diff -u -r1.1.2.2 -r1.1.2.3 --- openacs-4/packages/ecommerce/www/admin/products/extras-upload-2-oracle.xql 23 Jun 2005 19:28:15 -0000 1.1.2.2 +++ openacs-4/packages/ecommerce/www/admin/products/extras-upload-2-oracle.xql 27 Jun 2005 17:02:33 -0000 1.1.2.3 @@ -1,21 +1,21 @@ - postgresql7.1 + oracle8.1.6 update ec_custom_product_field_values set last_modified=sysdate, last_modifying_user=:user_id, modified_ip_address=:ip $moresql - where product_id = :var_$product_id_column + where product_id = :product_id - + - update ec_custom_product_field_values - set last_modified=sysdate, last_modifying_user=:user_id, modified_ip_address=:ip $moresql - where product_id = (select product_id from ec_products where sku = :var_$sku_column) + insert into ec_custom_product_field_values + ( last_modified, last_modifying_user, modified_ip_address, product_id $moresqlinsert_columns ) + values ( sysdate, :user_id, :ip, :product_id $moresqlinsert_values ) Index: openacs-4/packages/ecommerce/www/admin/products/extras-upload-2-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/products/extras-upload-2-postgresql.xql,v diff -u -r1.1.2.2 -r1.1.2.3 --- openacs-4/packages/ecommerce/www/admin/products/extras-upload-2-postgresql.xql 23 Jun 2005 19:28:15 -0000 1.1.2.2 +++ openacs-4/packages/ecommerce/www/admin/products/extras-upload-2-postgresql.xql 27 Jun 2005 17:02:33 -0000 1.1.2.3 @@ -6,16 +6,16 @@ update ec_custom_product_field_values - set last_modified=now(), last_modifying_user=:user_id, modified_ip_address=:ip $moresql - where product_id = :var_$product_id_column + set last_modified=now(), last_modifying_user=:user_id, modified_ip_address=:ip $moresqlupdate + where product_id = :product_id - + - update ec_custom_product_field_values - set last_modified=now(), last_modifying_user=:user_id, modified_ip_address=:ip $moresql - where product_id = (select product_id from ec_products where sku = :var_$sku_column) + insert into ec_custom_product_field_values + ( last_modified, last_modifying_user, modified_ip_address, product_id $moresqlinsert_columns ) + values ( now(), :user_id, :ip, :product_id $moresqlinsert_values ) 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.5.2.2 -r1.5.2.3 --- openacs-4/packages/ecommerce/www/admin/products/extras-upload-2.tcl 26 Jan 2005 21:25:55 -0000 1.5.2.2 +++ openacs-4/packages/ecommerce/www/admin/products/extras-upload-2.tcl 27 Jun 2005 17:02:33 -0000 1.5.2.3 @@ -34,7 +34,7 @@ [ad_context_bar [list "../" "Ecommerce([ec_system_name])"] [list "index.tcl" "Products"] "Uploading Extras"]
- +

" set unix_file_name ${csv_file.tmpfile} @@ -50,11 +50,10 @@ return } -doc_body_append "

-"
-
 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 +73,81 @@
 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: 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 default to update instead.
+        # For legacy systems where custom product fields do not yet exist, 
+        # we still need insert fields, so we test each imported row.
 
-	set sql "update ec_custom_product_field_values set last_modified=sysdate, last_modifying_user=:user_id, modified_ip_address=:ip"
-        set moresql ""
-
+        # build query for updating and another for inserting
+        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 (from legacy data)
+            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<"
+                    doc_body_append "
FAILURE! SQL: $sql
 $errmsg\n
"
+                } else {
+                    doc_body_append ". "
+                    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<"
+                    doc_body_append "
FAILURE! SQL: $sql
 $errmsg\n
"
+                } else {
+                    doc_body_append "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."
+            doc_body_append "
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 +163,12 @@
     }
 
 }
+# post upload report to log in case the connection timed out.
+ns_log Notice "ecommerce: Custom fields loader read $count lines from ${csv_file}. Updated $rows_updated rows. Inserted $rows_inserted rows."
+doc_body_append "

+
Done reading [ec_decode $count "0" "0" [expr $count -1]] rows from $csv_file, updated(.) $rows_updated and inserted(i) $rows_inserted product extras!
+

 
-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] " Fisheye: Tag 1.1 refers to a dead (removed) revision in file `openacs-4/packages/ecommerce/www/admin/products/extras-upload-2.xql'. Fisheye: No comparison available. Pass `N' to diff?