- # Set all records to be invalid. Only mark them as valid if they appear on the new list.
- $sth = $dbh->prepare("update eq_family set valid=0");
- $sth->execute or die "-E- DB error: $DBI::errstr\n";
- $sth = $dbh->prepare("update eq_family set companionship=0");
- $sth->execute or die "-E- DB error: $DBI::errstr\n";
-
- foreach $index (keys %membership_data)
- {
- $hashref = $membership_data{$index};
- foreach $key (keys %$hashref) {
- if($key =~ /HH Position/i && $membership_data{$index}{$key} =~ /Head of Household/i) {
- $family_name = $membership_data{$index}{'Preferred Name'};
- $family_name =~ s/\'/\\'/g; #'
- $id = $membership_data{$index}{'HofH ID'};
- $name_id = uc($family_name);
-
- # Find out how many families match this family's name
- $sth = $dbh->prepare("select * from eq_family where name_id='$name_id'");
- $sth->execute or die "-E- DB error: $DBI::errstr\n";
- my @data = ();
- while($sqlhashref = $sth->fetchrow_hashref) { push(@data, $sqlhashref); }
- my $rows = scalar @data;
-
- if($rows == 0) {
- # No existing records found for this family, make a new entry
- print " Adding new Family: $family_name\n";
- $sth = $dbh->prepare("insert into eq_family values (NULL,$id,'$family_name','$name_id','0','0','1','',1)");
- $sth->execute or die "-E- DB error: $DBI::errstr\n";
- } elsif($rows == 1) {
- # An existing record was found for this family, update it
- print " Updating existing family: $family_name\n";
- $sth = $dbh->prepare("update eq_family set hofh_id=$id where name_id='$name_id'");
- $sth->execute or die "-E- DB error: $DBI::errstr\n";
- $sth = $dbh->prepare("update eq_family set valid=1 where name_id='$name_id'");
- $sth->execute or die "-E- DB error: $DBI::errstr\n";
- } else {
- # More than one record was found. Error! This shouldn't happen.
- print " -E- More than one record found ($rows) for family name: $family_name\n";
+ # Set all records to be invalid. Only mark them as valid if they appear on the new list.
+ $sth = $dbh->prepare("update tc_family set valid=0");
+ $sth->execute or die "-E- DB error: $DBI::errstr\n";
+ $sth = $dbh->prepare("update tc_family set companionship=0");
+ $sth->execute or die "-E- DB error: $DBI::errstr\n";
+
+ foreach $index (keys %membership_data)
+ {
+ $hashref = $membership_data{$index};
+ foreach $key (keys %$hashref) {
+ if($key =~ /HH Position/i && $membership_data{$index}{$key} =~ /Head of Household/i) {
+ $family_name = $membership_data{$index}{'Preferred Name'};
+ $family_name =~ s/\'/\\'/g; #'
+ $id = $membership_data{$index}{'HofH ID'};
+ $name_id = uc($family_name);
+
+ # Find out how many families match this family's name
+ $sth = $dbh->prepare("select * from tc_family where name_id='$name_id'");
+ $sth->execute or die "-E- DB error: $DBI::errstr\n";
+ my @data = ();
+ while($sqlhashref = $sth->fetchrow_hashref) { push(@data, $sqlhashref); }
+ my $rows = scalar @data;
+
+ if($rows == 0) {
+ # No existing records found for this family, make a new entry
+ print " Adding new Family: $family_name\n";
+ $sth = $dbh->prepare("insert into tc_family values (NULL,$id,'$family_name','$name_id','0','0',NULL,1)");
+ $sth->execute or die "-E- DB error: $DBI::errstr\n";
+ } elsif($rows == 1) {
+ # An existing record was found for this family, update it
+ print " Updating existing family: $family_name\n";
+ $sth = $dbh->prepare("update tc_family set hofh_id=$id where name_id='$name_id'");
+ $sth->execute or die "-E- DB error: $DBI::errstr\n";
+ $sth = $dbh->prepare("update tc_family set valid=1 where name_id='$name_id'");
+ $sth->execute or die "-E- DB error: $DBI::errstr\n";
+ } else {
+ # More than one record was found. Error! This shouldn't happen.
+ print " -E- More than one record found ($rows) for family name: $family_name\n";
+ }
+
+ # Now update the individual field for this family
+ $sth = $dbh->prepare("select * from tc_individual WHERE name='$family_name'");
+ $sth->execute or die "-E- DB error: $DBI::errstr\n";
+ while($sqlhashref = $sth->fetchrow_hashref) {
+ $individual = $sqlhashref->{individual};
+ print " Updating family individual: $family_name -> $individual\n";
+ $sth = $dbh->prepare("update tc_family set individual=$individual where name_id='$name_id'");
+ $sth->execute or die "-E- DB error: $DBI::errstr\n";
+ }
+
+ # Now update the hometeaching field for this family
+ foreach $index (keys %hometeaching_data)
+ {
+ $hashref = $hometeaching_data{$index};
+ foreach $key (keys %$hashref) {
+ if($hometeaching_data{$index}{'Household'} =~ /(\S+)\s+(\S+),\s+(\S+)\s+(.*)/) {
+ $a = $1; $b = $2; $c = $3; $d = $4;
+ if($family_name =~ /$a/ && $hometeaching_data{$index}{'Household'} !~ /$family_name/i) {
+ print "I: Adjusting hometeaching match from: $hometeaching_data{$index}{'Household'} to $a, $c $d\n";
+ $hometeaching_data{$index}{'Household'} = "$a, $c $d";
+ }
+ }
+ if($key =~ /Quorum/i &&
+ $hometeaching_data{$index}{$key} =~ /Elders/i &&
+ $hometeaching_data{$index}{'Household'} =~ /$family_name/i &&
+ $data[0]->{companionship} != $hometeaching_data{$index}{'Comp ID'}
+ )
+ {
+ print " Updating hometeaching assignment for $family_name family\n";
+ $companionship = $hometeaching_data{$index}{'Comp ID'};
+ $sth = $dbh->prepare("update tc_family set companionship='$companionship' where name_id='$name_id'");
+ $sth->execute or die "-E- DB error: $DBI::errstr\n";
+ }
+ }
+ }
+ $sth->finish();
+ }