- # Now update the elder_id field for this family
- $sth = $dbh->prepare("select * from eq_elder WHERE name='$family_name'");
- $sth->execute or die "-E- DB error: $DBI::errstr\n";
- while($sqlhashref = $sth->fetchrow_hashref) {
- $elder_id = $sqlhashref->{elder};
- print " Updating family elder_id: $family_name -> $elder_id\n";
- $sth = $dbh->prepare("update eq_family set elder_id=$elder_id 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 eq_family set companionship='$companionship' where name_id='$name_id'");
- $sth->execute or die "-E- DB error: $DBI::errstr\n";
- }
- }
- }
- $sth->finish();
- }
- }
- }
-}
-
-# EQ_PARENT
-#+----------+------------------+------+-----+---------+-------+
-#| Field | Type | Null | Key | Default | Extra |
-#+----------+------------------+------+-----+---------+-------+
-#| parent | int(16) unsigned | | PRI | 0 | A |
-#| family | int(16) unsigned | YES | | NULL | |
-#| name | varchar(30) | YES | | NULL | |
-#| birthday | date | YES | | NULL | |
-#| phone | varchar(12) | YES | | NULL | |
-#| address | varchar(255) | YES | | NULL | |
-#| indiv_id | int(16) unsigned | YES | UNI | NULL | |
-#| valid | tinyint(1) | YES | | NULL | |
-#+----------+------------------+------+-----+---------+-------+
-sub update_eq_parent_table
-{
- print "\n-> Updating eq_parent table\n";
-
- # Set all records to be invalid. Only mark them as valid if they appear on the new list.
- $sth = $dbh->prepare("update eq_parent set valid=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 ||
- $membership_data{$index}{$key} =~ /Spouse/i
- ) {
- # Get some information from the hash about this parent
- $parent_name = $membership_data{$index}{'Preferred Name'};
- $parent_name =~ s/\'/\\'/g; #'
- $birthday = $membership_data{$index}{'Birth'};
- $birthday =~ /(\d+) (\S+) (\d+)/; $day=$1; $month=$monthname2num{$2}; $year=$3;
- $hofh_id = $membership_data{$index}{'HofH ID'};
- $id = $membership_data{$index}{'Indiv ID'};
- $phone = $membership_data{$index}{'Household Phone'};
- if($phone =~ /(\d\d\d-\d\d\d\d)/) { $phone = "$areacode-$1"; }
- if($phone =~ /^\(\d\d\d\) (\d\d\d-\d\d\d\d)/) { $phone = "$1-$2"; }
- $address = $membership_data{$index}{'Street 1'};
- if($membership_data{$index}{'Street 2'} ne "") {
- $address .= " " . $membership_data{$index}{'Street 2'};
- }
-
- # Find the family id for this parent's HofH_ID.
- $sth = $dbh->prepare("select * from eq_family where hofh_id='$hofh_id' and valid=1");
- $sth->execute or die "-E- DB error: $DBI::errstr\n";
- my @family_data = ();
- while($sqlhashref = $sth->fetchrow_hashref) { push(@family_data, $sqlhashref); }
- my $family_rows = scalar @family_data;
- if($family_rows > 0) { $family_id = $family_data[0]->{'family'}; }
- else { $family_id = 0; }
-
- # Find out how many parents match this parent's name
- $sth = $dbh->prepare("select * from eq_parent where name='$parent_name'");
- $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 && $family_rows > 0) {
- # No existing records found for this parent, make a new entry
- print " Adding new Parent: $parent_name\n";
- $sth = $dbh->prepare("insert into eq_parent values (NULL,$family_id,'$parent_name','$year-$month-$day','$phone','$address','$id',1)");
- $sth->execute or die "-E- DB error: $DBI::errstr\n";
- } elsif($rows == 1 && $family_rows > 0) {
- # An existing record was found for this parent, update it
- print " Updating existing parent: $parent_name\n";
- $sth = $dbh->prepare("update eq_parent set family='$family_id' where name='$parent_name'");
- $sth->execute or die "-E- DB error: $DBI::errstr\n";
- $sth = $dbh->prepare("update eq_parent set birthday='$year-$month-$day' where name='$parent_name'");
- $sth->execute or die "-E- DB error: $DBI::errstr\n";
- $sth = $dbh->prepare("update eq_parent set phone='$phone' where name='$parent_name'");
- $sth->execute or die "-E- DB error: $DBI::errstr\n";
- $sth = $dbh->prepare("update eq_parent set address='$address' where name='$parent_name'");
- $sth->execute or die "-E- DB error: $DBI::errstr\n";
- $sth = $dbh->prepare("update eq_parent set valid=1 where name='$parent_name'");
- $sth->execute or die "-E- DB error: $DBI::errstr\n";
- $sth = $dbh->prepare("update eq_parent set indiv_id='$id' where name='$parent_name'");
- $sth->execute or die "-E- DB error: $DBI::errstr\n";
- } elsif($rows > 1) {
- # More than one record was found. Error! This shouldn't happen.
- print " -E- More than one record found with same parent name: $parent_name with hofh_id: $hofh_id\n";
- } else {
- print " -E- Unable to find a family to attach this parent to: $parent_name with hofh_id: $hofh_id\n";
- }
- $sth->finish();
- }
- }
- }
-}