Index: openacs-4/packages/contacts/tcl/contacts-import-procs.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/contacts/tcl/contacts-import-procs.tcl,v
diff -u -N -r1.2 -r1.3
--- openacs-4/packages/contacts/tcl/contacts-import-procs.tcl	18 Dec 2006 13:03:19 -0000	1.2
+++ openacs-4/packages/contacts/tcl/contacts-import-procs.tcl	18 Dec 2006 19:15:05 -0000	1.3
@@ -27,10 +27,10 @@
 
 
     set organization_text_list [list \
-				    [list "E-Mail" "email"] \
 				    [list "Firma" "name"] \
 				    [list "Zusatz" "company_name_ext"] \
-				    [list "Internet" "company_url"]
+				    [list "Internet" "company_url"] \
+				    [list "E-Mail3" "organization_email"] \
 			       ]
 
 
@@ -111,10 +111,10 @@
 
 	# Make sure mandatory fields are set.
 	if {![exists_and_not_null values(E-Mail)]} {
-	    set person_email ""
+	    set email ""
 	    set values(E-Mail) ""
 	} else {
-	    set person_email $values(E-Mail)
+	    set email $values(E-Mail)
 	}
 	
 	if {![exists_and_not_null values(Vorname)]} {
@@ -124,11 +124,11 @@
 	    set first_names $values(Vorname)
 	}
 	
-	if {![exists_and_not_null values(Name)]} {
+	if {![exists_and_not_null values(Nachname)]} {
 	    set last_name " "
-	    set values(Name) ""
+	    set values(Nachname) ""
 	} else {
-	    set last_name $values(Name)
+	    set last_name $values(Nachname)
 	}
 
 	if {![exists_and_not_null values(Pstrasse)]} {
@@ -181,19 +181,25 @@
 	}
 	
 	if {![exists_and_not_null values(Firma)]} {
-	    set organization_name "$first_names $last_name"
+	    set organization_name ""
 	} else {
 	    set organization_name $values(Firma)
 	}
-	
+
+	if {![exists_and_not_null values(E-Mail3)]} {
+	    set organization_email ""
+	} else {
+	    set organization_email $values(E-Mail3)
+	}
+
 	# If there is no company phone, set to person phone
 	if {![exists_and_not_null values(Telefon2)]} {
 	    set values(Telefon2) $values(Telefon1)
 	}
 	
 	# Get Prof. and Dr. out of the way if they are in the name (title)
 	
-	foreach name {Vorname Name} {
+	foreach name {Vorname Nachname} {
 	    foreach prefix {Dr. Prof.} {
 		if {[string first $prefix $values($name)] > -1} {
 		    if {![exists_and_not_null values(Titel)]} {
@@ -227,85 +233,78 @@
 				      ]
 	set existing_organizations ""
 	set organization_revision_id ""
+	set organization_id ""
 	
-	foreach organization_id $existing_organization_ids {
-	    contacts::postal_address::get -attribute_name "company_address" -party_id $organization_id -array address_array
-	    if {[exists_and_not_null address_array(postal_code)] && $values(PLZ) == $address_array(postal_code)} {
-		lappend existing_organizations $organization_id
+	if {![string eq [string trim $organization_name] ""]} {
+	    foreach organization_id $existing_organization_ids {
+		contacts::postal_address::get -attribute_name "company_address" -party_id $organization_id -array address_array
+		if {[exists_and_not_null address_array(postal_code)] && $values(PLZ) == $address_array(postal_code)} {
+		    lappend existing_organizations $organization_id
+		}
 	    }
-	}
 
-	# set orga_p [organization::name_p -name $values(Firma)]
-	
-	if {[llength $existing_organizations] == 0} {
-	    #if organization does not exist, create it
-	    set organization_id [organization::new -name $organization_name -email $values(E-Mail) -url $values(Internet)]
-	    ns_log Notice "contacts::import::leads => Organization created $organization_name: $organization_id"
+	    # set orga_p [organization::name_p -name $values(Firma)]
 	    
-	    set organization_revision_id [contact::revision::new -party_id $organization_id]
-	    
-	    # Add to the default group
-	    set rel_id [db_string insert_rels { select acs_rel__new (NULL::integer,'organization_rel',:default_group_id,:organization_id,NULL,NULL,NULL) as org_rel_id }]
-	    db_dml insert_state { insert into membership_rels (rel_id,member_state) values (:rel_id,'approved') }
-	    
-	    # Add to the leads group
-	    set rel_id [db_string insert_rels2 { select acs_rel__new (NULL::integer,'organization_rel',:group_id,:organization_id,NULL,NULL,NULL) as org_rel_id }]
-	    db_dml insert_state2 { insert into membership_rels (rel_id,member_state) values (:rel_id,'approved') }
-	    
-	    # Add to the lexware group
-	    set rel_id [db_string insert_rels2 { select acs_rel__new (NULL::integer,'organization_rel',:lexware_group_id,:organization_id,NULL,NULL,NULL) as org_rel_id }]
-	    db_dml insert_state2 { insert into membership_rels (rel_id,member_state) values (:rel_id,'approved') }
-	    
-	    
-	    # set creation_date if "DatumErfassst" contains it
-	    # THis allows for import from other sources
-	    if {[exists_and_not_null values(DatumErfasst)]} {
-		set value $values(DatumErfasst)
-		ns_log notice $value
-		regexp {^([0-9]+)\.([0-9]+)\.([0-9]+)$} $value match day month year
-		if {[exists_and_not_null month]} {
-		    set date_string "$year-$month-$day 00:00"
-		    unset month
-		    # NOTE
-		    # here we update the creation_date of the ORGANIZATION_ID
-		    # with the value of "DatumErfasst", if set
-		    # /NOTE
-		    db_dml update_creation_date {
-			update cr_revisions
-			set publish_date = to_timestamp(:date_string, 'YYYY-MM-DD HH24:MI')
-			where revision_id = :organization_revision_id
+	    if {[llength $existing_organizations] == 0} {
+		#if organization does not exist, create it
+		set organization_id [organization::new -name $organization_name -email $organization_email -url $values(Internet)]
+		ns_log Notice "contacts::import::leads => Organization created $organization_name: $organization_id"
+		
+		set organization_revision_id [contact::revision::new -party_id $organization_id]
+		
+		# Add to the default group
+		set rel_id [db_string insert_rels { select acs_rel__new (NULL::integer,'organization_rel',:default_group_id,:organization_id,NULL,NULL,NULL) as org_rel_id }]
+		db_dml insert_state { insert into membership_rels (rel_id,member_state) values (:rel_id,'approved') }
+		
+		# Add to the other group
+		if {$group_id ne ""} {
+		    set rel_id [db_string insert_rels2 { select acs_rel__new (NULL::integer,'organization_rel',:group_id,:organization_id,NULL,NULL,NULL) as org_rel_id }]
+		    db_dml insert_state2 { insert into membership_rels (rel_id,member_state) values (:rel_id,'approved') }
+		}
+
+		# set creation_date if "DatumErfassst" contains it
+		# THis allows for import from other sources
+		if {[exists_and_not_null values(DatumErfasst)]} {
+		    set value $values(DatumErfasst)
+		    ns_log notice $value
+		    regexp {^([0-9]+)\.([0-9]+)\.([0-9]+)$} $value match day month year
+		    if {[exists_and_not_null month]} {
+			set date_string "$year-$month-$day 00:00"
+			unset month
+			# NOTE
+			# here we update the creation_date of the ORGANIZATION_ID
+			# with the value of "DatumErfasst", if set
+			# /NOTE
+			db_dml update_creation_date {
+			    update cr_revisions
+			    set publish_date = to_timestamp(:date_string, 'YYYY-MM-DD HH24:MI')
+			    where revision_id = :organization_revision_id
+			}
 		    }
 		}
-	    }
-	
-	} else {
-	    # If organization already exists, get first id
-	    set organization_id [lindex $existing_organizations 0]
-	    
-	    # check if organization is customer and do not import
-	    if {[db_0or1row check_if_customer {
-		select 1
-		from membership_rels m, acs_rels r
-		where m.rel_id = r.rel_id
-		and r.object_id_one = :customer_group_id
-		and r.object_id_two = :organization_id
-		and r.rel_type = 'organization_rel'
-	    }] && !$overwrite_customer_p} {
-		ns_log Notice "contacts::import::leads => Organization is customer $values(Firma): $organization_id"
-		continue
+		
 	    } else {
-		set organization_revision_id [content::item::get_best_revision -item_id $organization_id]
-		ns_log Notice "contacts::import::leads => Organization exists $values(Firma): $organization_id"
+		# If organization already exists, get first id
+		set organization_id [lindex $existing_organizations 0]
+		
+		# check if organization is customer and do not import
+		if {[db_0or1row check_if_customer {
+		    select 1
+		    from membership_rels m, acs_rels r
+		    where m.rel_id = r.rel_id
+		    and r.object_id_one = :customer_group_id
+		    and r.object_id_two = :organization_id
+		    and r.rel_type = 'organization_rel'
+		}] && !$overwrite_customer_p} {
+		    ns_log Notice "contacts::import::leads => Organization is customer $values(Firma): $organization_id"
+		    continue
+		} else {
+		    set organization_revision_id [content::item::get_best_revision -item_id $organization_id]
+		    ns_log Notice "contacts::import::leads => Organization exists $values(Firma): $organization_id"
+		}
 	    }
-	}
-
+	    
 	    if {![string eq "" $organization_revision_id]} {
-
-		if {![group::party_member_p -group_id $lexware_group_id -party_id $organization_id]} {
-		    # Add to the lexware group
-		    set rel_id [db_string insert_rels2 { select acs_rel__new (NULL::integer,'organization_rel',:lexware_group_id,:organization_id,NULL,NULL,NULL) as org_rel_id }]
-		    db_dml insert_state2 { insert into membership_rels (rel_id,member_state) values (:rel_id,'approved') }
-		}
 		
 		# Set organization text attributes
 		foreach pair $organization_text_list {
@@ -359,6 +358,11 @@
 		if {$Land == "D"} {
 		    set Land "DE"
 		}
+		
+		if {[string length $Land]>2} {
+		    set Land [ref_countries::get_country_code -country $Land]
+		}
+
 		if {$Land == "DE" || $Land == "AT"} {
 		    set locale "de_DE"
 		} elseif {$Land == "CH"} {
@@ -367,7 +371,7 @@
 		    set locale "en_US"
 		}
 
-		if {![string eq "" $Adresse]} {
+		if {![string eq "" $Adresse] && ![string eq "" $Land]} {
 		    set value_id [ams::util::postal_address_save \
 				      -delivery_address $Adresse \
 				      -municipality $Ort \
@@ -406,136 +410,183 @@
 		}
 
 		incr organization_count
-
+		
 	    }
+	}
+	    
 
-	    ############
-	    # if organization existed: check if person exists in any of these organizations
-	    ############
 
-	    set person_id [db_string get_person_by_names {
-		select min(person_id)
-		from persons, parties
-		where first_names = :first_names
-		and last_name = :last_name
-		and persons.person_id = parties.party_id
-		and email = :person_email
-	    } -default ""]
 
-	    set person_revision_id [content::item::get_best_revision -item_id $person_id]
 
-	    # Only create new persons. Do not update existing ones (too risky)
-	    if {$person_revision_id eq ""} {
-		#if there is no party_id
+	############
+	# if organization existed: check if person exists in any of these organizations
+	############
+	
+	set person_id [party::get_by_email -email $email]
+	
+	set person_revision_id [content::item::get_best_revision -item_id $person_id]
+	
+	# Only create new persons. Do not update existing ones (too risky)
+	if {$person_revision_id eq ""} {
+	    #if there is no party_id
+	    if {$person_id eq ""} {
 		set person_id [person::new \
 				   -first_names $first_names \
 				   -last_name $last_name \
-				   -email $person_email]
+				   -email $email]
+	    }
 
-		ns_log Notice "contacts::import::leads => Person created $first_names $last_name ($person_email): $person_id"
-
-		# todo: set correct locale
-		callback contact::person_add -package_id $contacts_package_id -person_id $person_id
-
+	    ns_log Notice "contacts::import::csv => Person created $first_names $last_name ($email): $person_id"
+	    
+	    # todo: set correct locale
+	    callback contact::person_add -package_id $contacts_package_id -person_id $person_id
+	    
+	    group::add_member \
+		-group_id $default_group_id \
+		-user_id $person_id \
+		-rel_type "membership_rel"
+	    
+	    if {$group_id ne ""} {
 		group::add_member \
-		    -group_id $default_group_id \
-		    -user_id $person_id \
-		    -rel_type "membership_rel"
-
-		group::add_member \
 		    -group_id $group_id \
 		    -user_id $person_id \
 		    -rel_type "membership_rel"
+	    }
 
-		#set revision_id to newly created revision for this party_id
-		set person_revision_id [contact::revision::new -party_id $person_id]
-		
-		relation_add "contact_rels_employment" $person_id $organization_id
-		
-		# set creation_date
-		if {[exists_and_not_null values(DatumErfasst)]} {
-		    set value $values(DatumErfasst)
-		    ns_log notice $value
-		    regexp {^([0-9]+)\.([0-9]+)\.([0-9]+)$} $value match day month year
-		    if {[exists_and_not_null month]} {
-			set date_string "$year-$month-$day 00:00"
-			unset month
-			# NOTE
-			# here we update the creation_date of the PERSON_ID
-			# with the value of "DatumErfasst", if set
-			# /NOTE
-			db_dml update_creation_date {
-			    update cr_revisions
-			    set publish_date = to_timestamp(:date_string, 'YYYY-MM-DD HH24:MI')
-			    where revision_id = :person_revision_id
-			}
-		    }
-		}
+	    #set revision_id to newly created revision for this party_id
+	    set person_revision_id [contact::revision::new -party_id $person_id]
 
-		# Set date attributes
-		foreach pair $person_date_list {
-		    set attribute [lindex $pair 0]
-		    set attribute_name [lindex $pair 1]
-		    if {[exists_and_not_null values($attribute)]} {
-			set value $values($attribute)
-			regexp {^([0-9]+)\.([0-9]+)\.([0-9]+)$} $value match day month year
-			set value_id [ams::util::time_save -time "$month-$day-$year 00:00"]
-			set attribute_id [attribute::id \
-					      -object_type "person" \
-					      -attribute_name $attribute_name
-					 ]
-			ams::attribute::value_save \
-			    -object_id $person_revision_id \
-			    -attribute_id $attribute_id \
-			    -value_id $value_id
+	    # set creation_date
+	    if {[exists_and_not_null values(DatumErfasst)]} {
+		set value $values(DatumErfasst)
+		ns_log notice $value
+		regexp {^([0-9]+)\.([0-9]+)\.([0-9]+)$} $value match day month year
+		if {[exists_and_not_null month]} {
+		    set date_string "$year-$month-$day 00:00"
+		    unset month
+		    # NOTE
+		    # here we update the creation_date of the PERSON_ID
+		    # with the value of "DatumErfasst", if set
+		    # /NOTE
+		    db_dml update_creation_date {
+			update cr_revisions
+			set publish_date = to_timestamp(:date_string, 'YYYY-MM-DD HH24:MI')
+			where revision_id = :person_revision_id
 		    }
 		}
-		
-		# Set the text attributes
-		foreach pair $person_text_list {
-		    set attribute [lindex $pair 0]
-		    set attribute_name [lindex $pair 1]
-		    if {[exists_and_not_null values($attribute)]} {
-			set value $values($attribute)
-			ams::attribute::save::text \
-			    -object_id $person_revision_id \
-			    -attribute_name $attribute_name \
-			    -object_type "person" \
-			    -value $value
-		    }
-		}
-		
-		# save salutation attribute
-		if {[exists_and_not_null values(Briefanrede)]} {
-		    set option_id $salutation($values(Briefanrede))
-		    set value_id [ams::util::options_save -options $option_id]
-		    
+	    }
+
+	    # Set date attributes
+	    foreach pair $person_date_list {
+		set attribute [lindex $pair 0]
+		set attribute_name [lindex $pair 1]
+		if {[exists_and_not_null values($attribute)]} {
+		    set value $values($attribute)
+		    regexp {^([0-9]+)\.([0-9]+)\.([0-9]+)$} $value match day month year
+		    set value_id [ams::util::time_save -time "$month-$day-$year 00:00"]
+		    set attribute_id [attribute::id \
+					  -object_type "person" \
+					  -attribute_name $attribute_name
+				     ]
 		    ams::attribute::value_save \
 			-object_id $person_revision_id \
-			-attribute_id $salutation_attribute_id \
+			-attribute_id $attribute_id \
 			-value_id $value_id
 		}
+	    }
+	    
+	    # Set the text attributes
+	    foreach pair $person_text_list {
+		set attribute [lindex $pair 0]
+		set attribute_name [lindex $pair 1]
+		if {[exists_and_not_null values($attribute)]} {
+		    set value $values($attribute)
+		    ams::attribute::save::text \
+			-object_id $person_revision_id \
+			-attribute_name $attribute_name \
+			-object_type "person" \
+			-value $value
+		}
+	    }
+	    
+	    # save salutation attribute
+	    if {[exists_and_not_null values(Briefanrede)]} {
+		set option_id $salutation($values(Briefanrede))
+		set value_id [ams::util::options_save -options $option_id]
 		
-		# Set phone attributes
-		foreach pair $person_phone_list {
-		    set attribute [lindex $pair 0]
-		    set attribute_name [lindex $pair 1]
-		    if {[exists_and_not_null values($attribute)] && $values($attribute) != "0"} {
-			set value $values($attribute)
-			set value_id [ams::util::telecom_number_save \
-					  -subscriber_number $value
+		ams::attribute::value_save \
+		    -object_id $person_revision_id \
+		    -attribute_id $salutation_attribute_id \
+		    -value_id $value_id
+	    }
+	    
+	    # Set phone attributes
+	    foreach pair $person_phone_list {
+		set attribute [lindex $pair 0]
+		set attribute_name [lindex $pair 1]
+		if {[exists_and_not_null values($attribute)] && $values($attribute) != "0"} {
+		    set value $values($attribute)
+		    set value_id [ams::util::telecom_number_save \
+				      -subscriber_number $value
+				 ]
+		    set attribute_id [attribute::id \
+					  -object_type "person" \
+					  -attribute_name $attribute_name
 				     ]
-			set attribute_id [attribute::id \
-					      -object_type "person" \
-					      -attribute_name $attribute_name
-					 ]
-			ams::attribute::value_save \
-			    -attribute_id $attribute_id \
-			    -value_id $value_id \
-			    -object_id $person_revision_id
-		    }
+		    ams::attribute::value_save \
+			-attribute_id $attribute_id \
+			-value_id $value_id \
+			-object_id $person_revision_id
 		}
 	    }
-	    array unset values
+	    
+	    # set person address attributes
+	    set Adresse $values(Pstrasse)
+	    set Ort $values(Port)
+	    set Postleitzahl $values(PPLZ)
+	    set Land $values(Pland)
+	    set bundesland $values(Pbundesland)
+	    if {$Land == "D"} {
+		set Land "DE"
+	    }
+	    
+	    if {[string length $Land]>2} {
+		set Land [ref_countries::get_country_code -country $Land]
+	    }
+	    
+	    if {$Land == "DE" || $Land == "AT"} {
+		set locale "de_DE"
+	    } elseif {$Land == "CH"} {
+		set locale "de_CH"
+	    } else {
+		set locale "en_US"
+	    }
+	    
+	    
+	    if {![string eq "" $Adresse] && ![string eq "" $Land]} {
+		set value_id [ams::util::postal_address_save \
+				  -delivery_address $Adresse \
+				  -municipality $Ort \
+				  -postal_code $Postleitzahl \
+				  -country_code $Land \
+				  -region $bundesland
+			     ]
+		set attribute_id [attribute::id \
+				      -object_type "person" \
+				      -attribute_name "visitaddress"
+				 ]
+		ams::attribute::value_save \
+		    -object_id $person_revision_id \
+		    -attribute_id $attribute_id \
+		    -value_id $value_id
+	    }
+	}
+
+		
+	if {$organization_id ne "" && $person_id ne ""} {
+	    relation_add "contact_rels_employment" $person_id $organization_id
+	}
+
+	array unset values
     }
 }