-#+---------------+------------------+------+-----+---------+-------+
-#| Field | Type | Null | Key | Default | Extra |
-#+---------------+------------------+------+-----+---------+-------+
-#| companionship | int(16) unsigned | | | 0 | |
-#| indiv | int(16) unsigned | YES | | NULL | |
-#| aaronic | int(16) unsigned | YES | | NULL | |
-#| district | int(16) unsigned | YES | | NULL | |
-#| valid | tinyint(1) | YES | | NULL | |
-#+---------------+------------------+------+-----+---------+-------+
-sub update_3rd_companionship_table
-{
- print "\n-> Updating 3rd_companionship table\n";
-
- # First, mark all existing companionships as invalid in case they have been dissolved
- $sth = $dbh->prepare("update 3rd_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 3rd_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") {
- $indiv_name = $hometeaching_data{$index}{$field};
- if($indiv_name eq "") { next; }
- $sth2 = $dbh->prepare("select * from 3rd_indiv where name='$indiv_name'");
- $sth2->execute or die "-E- DB error: $DBI::errstr\n";
- $sqlhashref2 = $sth2->fetchrow_hashref;
- $indiv = $sqlhashref2->{indiv};
- $aaronic = "NULL";
- if($indiv eq "") {
- $sth2 = $dbh->prepare("select * from 3rd_aaronic where name='$indiv_name'");
- $sth2->execute or die "-E- DB error: $DBI::errstr\n";
- $sqlhashref2 = $sth2->fetchrow_hashref;
- $aaronic = $sqlhashref2->{aaronic};
- $indiv = "NULL";
- if($aaronic eq "") { print "-W- Unable to find $indiv_name in 3rd_indiv or 3rd_aaronic tables\n"; next; }
- }
- $id = $hometeaching_data{$index}{'Comp ID'};
- $district = $hometeaching_data{$index}{'HT District'};
- $sth = $dbh->prepare("select * from 3rd_companionship where indiv='$indiv' 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: $indiv_name -> $id\n";
- $sth = $dbh->prepare("insert into 3rd_companionship values ($id,'$indiv','$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 3rd_companionship where district='$district' and companionship='$id'");
- $sth2->execute or die "-E- DB error: $DBI::errstr\n";
- if($indiv ne "NULL") {
- print " Updating Companionship with indiv: $indiv_name ($indiv) -> $id\n";
- $sth = $dbh->prepare("update 3rd_companionship set district='$district' where indiv='$indiv' and companionship='$id'");
- $sth->execute or die "-E- DB error 'district': $DBI::errstr\n";
- $sth = $dbh->prepare("update 3rd_companionship set indiv='$indiv' where indiv='$indiv' and companionship='$id'");
- $sth->execute or die "-E- DB error 'indiv': $DBI::errstr\n";
- $sth = $dbh->prepare("update 3rd_companionship set valid=1 where indiv='$indiv' and companionship='$id'");
- $sth->execute or die "-E- DB error 'valid': $DBI::errstr\n";
- } else {
- print " Updating Companionship with Aaronic: $indiv_name ($aaronic) -> $id\n";
- $sth = $dbh->prepare("update 3rd_companionship set district='$district' where aaronic='$aaronic' and companionship='$id'");
- $sth->execute or die "-E- DB error: $DBI::errstr\n";
- $sth = $dbh->prepare("update 3rd_companionship set aaronic='$aaronic' where aaronic='$aaronic' and companionship='$id'");
- $sth->execute or die "-E- DB error: $DBI::errstr\n";
- $sth = $dbh->prepare("update 3rd_companionship set valid=1 where aaronic='$aaronic' and companionship='$id'");
- $sth->execute or die "-E- DB error: $DBI::errstr\n";
- $sth = $dbh->prepare("update 3rd_aaronic set valid=1 where aaronic='$aaronic'");
- $sth->execute or die "-E- DB error: $DBI::errstr\n";
- }
- }
- $sth->finish();
- $sth2->finish();
- }
- }
- }
- }
-}
-
-# 3RD_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 | |
-#| indiv_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_3rd_family_table