- # First, mark all existing companionships as invalid in case they have been dissolved
- $sth = $dbh->prepare("update eq_companionship set valid=0");
- $sth->execute or die "-E- DB error: $DBI::errstr\n";
- # Second, mark all the aaronic invalid. We'll only mark the ones as valid that are assigned to hometeach
- $sth = $dbh->prepare("update eq_aaronic set valid=0");
- $sth->execute or die "-E- DB error: $DBI::errstr\n";
-
- foreach $index (keys %hometeaching_data)
- {
- $hashref = $hometeaching_data{$index};
- foreach $key (keys %$hashref) {
- if($key =~ /Quorum/i && $hometeaching_data{$index}{$key} =~ /Elders/i) {
- foreach $field ("Home Teacher 1","Home Teacher 2") {
- $elder_name = $hometeaching_data{$index}{$field};
- if($elder_name eq "") { next; }
- $sth2 = $dbh->prepare("select * from eq_elder where name='$elder_name'");
- $sth2->execute or die "-E- DB error: $DBI::errstr\n";
- $sqlhashref2 = $sth2->fetchrow_hashref;
- $elder = $sqlhashref2->{elder};
- $aaronic = "NULL";
- if($elder eq "") {
- $sth2 = $dbh->prepare("select * from eq_aaronic where name='$elder_name'");
- $sth2->execute or die "-E- DB error: $DBI::errstr\n";
- $sqlhashref2 = $sth2->fetchrow_hashref;
- $aaronic = $sqlhashref2->{aaronic};
- $elder = "NULL";
- if($aaronic eq "") { print "-W- Unable to find $elder_name in eq_elder or eq_aaronic tables\n"; next; }
- }
- $id = $hometeaching_data{$index}{'Comp ID'};
- $district = $hometeaching_data{$index}{'HT District'};
- $sth = $dbh->prepare("select * from eq_companionship where elder='$elder' and aaronic='$aaronic' and companionship='$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 companionship, make a new entry
- print " Adding Companion to companionship: $elder_name -> $id\n";
- $sth = $dbh->prepare("insert into eq_companionship values ($id,'$elder','$aaronic','$district',1)");
- $sth->execute or die "-E- DB error: $DBI::errstr\n";
- } else {
- # An existing companionship was found for this companionship, update it
- $sth2 = $dbh->prepare("select * from eq_companionship where district='$district' and companionship='$id'");
- $sth2->execute or die "-E- DB error: $DBI::errstr\n";
- if($elder ne "NULL") {
- print " Updating Companionship with Elder: $elder_name ($elder) -> $id\n";
- $sth = $dbh->prepare("update eq_companionship set district='$district' where elder='$elder' and companionship='$id'");
- $sth->execute or die "-E- DB error 'district': $DBI::errstr\n";
- $sth = $dbh->prepare("update eq_companionship set elder='$elder' where elder='$elder' and companionship='$id'");
- $sth->execute or die "-E- DB error 'elder': $DBI::errstr\n";
- $sth = $dbh->prepare("update eq_companionship set valid=1 where elder='$elder' and companionship='$id'");
- $sth->execute or die "-E- DB error 'valid': $DBI::errstr\n";
- } else {
- print " Updating Companionship with Aaronic: $elder_name ($aaronic) -> $id\n";
- $sth = $dbh->prepare("update eq_companionship set district='$district' where aaronic='$aaronic' and companionship='$id'");
- $sth->execute or die "-E- DB error: $DBI::errstr\n";
- $sth = $dbh->prepare("update eq_companionship set aaronic='$aaronic' where aaronic='$aaronic' and companionship='$id'");
- $sth->execute or die "-E- DB error: $DBI::errstr\n";
- $sth = $dbh->prepare("update eq_companionship set valid=1 where aaronic='$aaronic' and companionship='$id'");
- $sth->execute or die "-E- DB error: $DBI::errstr\n";
- $sth = $dbh->prepare("update eq_aaronic set valid=1 where aaronic='$aaronic'");
- $sth->execute or die "-E- DB error: $DBI::errstr\n";
- }
- }
- $sth->finish();
- $sth2->finish();
- }
- }
- }
- }
-}
-
-# EQ_FAMILY
-#+---------------+------------------+------+-----+---------+-------+
-#| Field | Type | Null | Key | Default | Extra |
-#+---------------+------------------+------+-----+---------+-------+
-#| family | int(16) unsigned | | PRI | 0 | A |
-#| hofh_id | int(16) unsigned | YES | | NULL | |
-#| name | varchar(30) | YES | | NULL | |
-#| name_id | varchar(30) | YES | | NULL | |
-#| elder_id | int(16) unsigned | YES | | NULL | |
-#| companionship | int(16) unsigned | YES | | NULL | |
-#| visit_pri | int(10) unsigned | YES | | 1 | |
-#| visit_notes | varchar(128) | YES | | NULL | |
-#| valid | tinyint(1) | YES | | NULL | |
-#+---------------+------------------+------+-----+---------+-------+
-sub update_eq_family_table
-{
- print "\n-> Updating eq_family 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_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";
- }