+
+ # last check to break out if we discovered a change
+ if ($changed == 1) { last; }
+ }
+
+ # we found a change to the companionship
+ if ($changed == 1) {
+ # invalidate existing companionship in database
+ print " invalidating companionship:$tc_companionship_id\n";
+ $sth = $dbh->prepare("UPDATE tc_companionship SET valid=0 WHERE companionship='$tc_companionship_id'");
+ $sth->execute or die "-E- DB error: $DBI::errstr\n";
+
+ # create new companionship in database
+ print " creating a new companionship\n";
+ $sth = $dbh->prepare("INSERT INTO tc_companionship values (NULL,$companionship,$district,1)");
+ $sth->execute or die "-E- DB error: $DBI::errstr\n";
+ $comp_id = $dbh->last_insert_id(NULL,NULL,'tc_companionship',NULL);
+
+ # create new companions in database
+ for my $companion (keys %{$companionships->{$companionship}->{'companions'}}) {
+ print " companion=$companion\n";
+ # if companion exists, preserve scheduling_priority
+ #print "SELECT tc.* FROM tc_companion AS tc JOIN (tc_individual AS ti, tc_companionship AS tp) WHERE tc.individual=ti.individual AND tc.companionship=tp.companionship AND tp.mls_id=$companionship AND ti.name=\"$companion\" AND tc.valid=1\n";
+ $sth = $dbh->prepare("SELECT tc.* FROM tc_companion AS tc JOIN (tc_individual AS ti, tc_companionship AS tp) WHERE tc.individual=ti.individual AND tc.companionship=tp.companionship AND tp.mls_id=$companionship AND ti.name=\"$companion\" AND tc.valid=1");
+ $sth->execute or die "-E- DB error: $DBI::errstr\n";
+ if ($sqlhashref = $sth->fetchrow_hashref) {
+ my $individual = $sqlhashref->{'individual'};
+ my $scheduling_priority = $sqlhashref->{'scheduling_priority'};
+ print " adding new companion $individual\n";
+ #print "INSERT INTO tc_companion values (NULL,$individual,$comp_id,$scheduling_priority,1)\n";
+ $sth = $dbh->prepare("INSERT INTO tc_companion values (NULL,$individual,$comp_id,$scheduling_priority,1)");
+ $sth->execute or die "-E- DB error: $DBI::errstr\n";
+ } else {
+ # new companion, create scheduling_priority first
+ print " adding new companion without existing scheduling_priority\n";
+ $sth = $dbh->prepare("INSERT INTO tc_scheduling_priority values (NULL,30,'')");
+ $sth->execute or die "-E- DB error: $DBI::errstr\n";
+ my $scheduling_priority = $dbh->last_insert_id(NULL,NULL,'tc_scheduling_priority',NULL);
+
+ #print "SELECT * FROM tc_individual WHERE name=\"$companion\" AND valid='1'\n";
+ $sth = $dbh->prepare("SELECT * FROM tc_individual WHERE name=\"$companion\" COLLATE latin1_general_cs AND valid='1'");
+ $sth->execute or die "-E- DB error: $DBI::errstr\n";
+ if ($sqlhashref = $sth->fetchrow_hashref) {
+ my $individual = $sqlhashref->{'individual'};
+ #print "INSERT INTO tc_companion values (NULL,$individual,$comp_id,$scheduling_priority,1)\n";
+ $sth = $dbh->prepare("INSERT INTO tc_companion values (NULL,$individual,$comp_id,$scheduling_priority,1)");
+ $sth->execute or die "-E- DB error: $DBI::errstr\n";
+ } else {
+ die "trying to add $companion as a companion but doesn't exist in tc_individual - how is this possible!!!\n";
+ }
+ }
+ }
+
+ # invalidate existing old companions in database
+ #print "UPDATE tc_companion SET valid=0 WHERE companionship=$tc_companionship_id AND valid=1\n";
+ $sth = $dbh->prepare("UPDATE tc_companion SET valid=0 WHERE companionship=$tc_companionship_id AND valid=1");
+ $sth->execute or die "-E- DB error: $DBI::errstr\n";
+
+ # don't worry about linking families, that will be done in update_tc_family_table()
+ } else {
+ # companionship is the same, just update district in case it changed
+ $sth = $dbh->prepare("UPDATE tc_companionship SET district=$district WHERE companionship=$tc_companionship_id");
+ $sth->execute or die "-E- DB error: $DBI::errstr\n";