From 17b6c5b5bedf4d243eb5aae967a14aece783f17b Mon Sep 17 00:00:00 2001 From: Owen Leonard Date: Fri, 17 Sep 2010 23:25:52 -0600 Subject: [PATCH] removed family field from tc_individual (double linkage), removed hoh_id/name/name_id from tc_family as they were unnecessary --- bin/import_ward_data | 157 ++++++++++++++----------------------------- inc/class.tc.inc.php | 78 +++++++-------------- sql/tc.sql | 1 - 3 files changed, 74 insertions(+), 162 deletions(-) diff --git a/bin/import_ward_data b/bin/import_ward_data index 52162dd..501f963 100755 --- a/bin/import_ward_data +++ b/bin/import_ward_data @@ -157,7 +157,7 @@ sub update_tc_individual_table if($rows == 0) { # No existing records found for this individual, make a new entry print " Adding new individual: $individual_name\n"; - $sth = $dbh->prepare("insert into tc_individual values (NULL,'$id',\"$individual_name\",'$address','$phone','$email','','$hhposition','$priesthood',NULL,$attending,1)"); + $sth = $dbh->prepare("insert into tc_individual values (NULL,'$id',\"$individual_name\",'$address','$phone','$email','$hhposition','$priesthood',NULL,$attending,1)"); $sth->execute or die "-E- DB error: $DBI::errstr\n"; } elsif($rows == 1) { # An existing record was found for this individual, update it @@ -330,9 +330,6 @@ sub update_tc_companionship_table #| 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 | | #| individual | int(16) unsigned | YES | | NULL | | #| companionship | int(16) unsigned | YES | | NULL | | #| scheduling_priority | int(10) unsigned | YES | | 30 | | @@ -343,83 +340,64 @@ sub update_tc_family_table print "\n-> Updating tc_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 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 = $dbh->prepare("update tc_family set valid=0 and 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"; - } + # find head of households in tc_individual + $sth = $dbh->prepare("SELECT * FROM tc_individual WHERE hh_position='Head of Household' and valid=1"); + $sth->execute or die "-E- DB error: $DBI::errstr\n"; + while ($sqlhashref = $sth->fetchrow_hashref) { + $individual = $sqlhashref{individual}; + $name = $sqlhashref{name}; + + $sth2 = $dbh->prepare("SELECT * FROM tc_family WHERE individual='$individual'"); + $sth2->execute or die "-E- DB error: $DBI::errstr\n"; + + my @data = (); + while($sqlhashref2 = $sth2->fetchrow_hashref) { push(@data, $sqlhashref2); } + my $rows = scalar @data; - # 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"; + if($rows == 0) { + # No existing records found for this family, make a new entry + print " Adding new Family: $name\n"; + $sth2 = $dbh->prepare("insert into tc_family values (NULL,'$individual','0',NULL,1)"); + $sth2->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: $name\n"; + $sth2 = $dbh->prepare("update tc_family set valid='1' where individual=$individual'"); + $sth2->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: $name\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($name =~ /$a/ && $hometeaching_data{$index}{'Household'} !~ /$name/i) { + print "I: Adjusting hometeaching match from: $hometeaching_data{$index}{'Household'} to $a, $c $d\n"; + $hometeaching_data{$index}{'Household'} = "$a, $c $d"; + } } - - # Now update the hometeaching field for this family - foreach $index (keys %hometeaching_data) + if($key =~ /Quorum/i && + $hometeaching_data{$index}{$key} =~ /Elders/i && + $hometeaching_data{$index}{'Household'} =~ /$name/i && + $data[0]->{companionship} != $hometeaching_data{$index}{'Comp ID'} + ) { - $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"; - } - } + print " Updating hometeaching assignment for $name family\n"; + $companionship = $hometeaching_data{$index}{'Comp ID'}; + $sth2 = $dbh->prepare("update tc_family set companionship='$companionship' where individual='$individual'"); + $sth2->execute or die "-E- DB error: $DBI::errstr\n"; } - $sth->finish(); } } + $sth->finish(); } } @@ -566,38 +544,6 @@ sub check_for_changed_ids return $found_problem; } -sub update_family_in_tc_individual_table -{ - print "\n-> Updating family info in tc_individual table\n"; - - foreach $index (keys %membership_data) - { - $hashref = $membership_data{$index}; - - # get some information from hash about this individual - $name = $membership_data{$index}{'Preferred Name'}; - $hofh_id = $membership_data{$index}{'HofH ID'}; - - # Find the family id for this individual's HofH_id - $sth = $dbh->prepare("select * from tc_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'}; - - print " Updating family data for: $name\n"; - - # write the family id to the individual's data in tc_individual - $sth = $dbh->prepare("update tc_individual set family='$family_id' where name=\"$name\""); - $sth->execute or die "-E- DB error: $DBI::errstr\n"; - } else { - $family_id = 0; - } - } -} - sub update_organization_class_data { print "\n-> Updating organization class info in tc_individual table\n"; @@ -749,7 +695,6 @@ if($opt_s) { $dbh->disconnect(); exit; } &update_tc_companionship_table(); &update_tc_family_table(); &update_tc_visit_table(); -&update_family_in_tc_individual_table(); &update_organization_class_data(); &update_tc_scheduling_priority_table(); diff --git a/inc/class.tc.inc.php b/inc/class.tc.inc.php index ee67be5..8de0e85 100644 --- a/inc/class.tc.inc.php +++ b/inc/class.tc.inc.php @@ -15,6 +15,7 @@ class tc { var $db; var $db2; + var $db3; var $t; var $nextmatchs; var $grants; @@ -258,7 +259,7 @@ class tc $table_data.= "$companion_table_entry
"; // Get the names of the families assigned this home teaching companionship - $sql = "SELECT * from tc_family where valid=1 AND companionship=".$unique_companionships[$j]['companionship']; + $sql = "SELECT * FROM tc_family AS tf JOIN tc_individual AS ti WHERE tf.individual=ti.individual AND tf.valid=1 AND tf.companionship=".$unique_companionships[$j]['companionship']; $sql = $sql . " ORDER BY name ASC"; $this->db->query($sql,__LINE__,__FILE__); $k=0; @@ -474,8 +475,8 @@ class tc $table_data.= "$companion_table_entry
"; // Get the names of the families assigned this home teaching companionship - $sql = "SELECT * from tc_family where valid=1 AND companionship=".$unique_companionships[$j]['companionship']; - $sql = $sql . " ORDER BY name ASC"; + $sql = "SELECT * FROM tc_family AS tf JOIN tc_individual AS ti WHERE tf.individual=ti.individual AND tf.valid=1 AND tf.companionship=".$unique_companionships[$j]['companionship']; + $sql = $sql . " ORDER BY ti.name ASC"; $this->db->query($sql,__LINE__,__FILE__); while ($this->db->next_record()) { $family_name = $this->db->f('name'); @@ -2069,7 +2070,7 @@ class tc $year = date('Y'); // create the family id -> family name mapping - $sql = "SELECT * FROM tc_family where valid=1 and individual != 0 and companionship != 0 ORDER BY name ASC"; + $sql = "SELECT * FROM tc_family AS tf JOIN tc_individual AS ti WHERE tf.individual=ti.individual AND tf.valid=1 AND tf.individual != 0 AND tf.companionship != 0 ORDER BY ti.name ASC"; $this->db->query($sql,__LINE__,__FILE__); $i=0; $family_id = NULL; @@ -2077,11 +2078,7 @@ class tc $family_id[$i] = $this->db->f('family'); $family_name[$i] = $this->db->f('name'); $familyid2name[$family_id[$i]] = $family_name[$i]; - $sql = "SELECT * FROM tc_individual where family='$family_id[$i]'"; - $this->db2->query($sql,__LINE__,__FILE__); - if($this->db2->next_record()) { - $familyid2address[$family_id[$i]] = $this->db2->f('address'); - } + $familyid2address[$family_id[$i]] = $this->db->f('address'); $i++; } array_multisort($family_name, $family_id); @@ -2218,43 +2215,18 @@ class tc // VISIT SCHEDULING TABLE - $sql = "SELECT * FROM tc_family AS tf JOIN tc_scheduling_priority AS tsp WHERE tf.scheduling_priority=tsp.scheduling_priority AND tf.valid=1 AND tf.individual != 0 AND tf.companionship != 0 ORDER BY tsp.priority ASC, tf.name ASC"; + $sql = "SELECT * FROM tc_family AS tf JOIN (tc_scheduling_priority AS tsp, tc_individual as ti) WHERE tf.scheduling_priority=tsp.scheduling_priority AND tf.individual=ti.individual AND tf.valid=1 AND tf.individual != 0 AND tf.companionship != 0 ORDER BY tsp.priority ASC, ti.name ASC"; $this->db->query($sql,__LINE__,__FILE__); $total_families=0; $families_with_yearly_visit=0; - $i=0; - $family_id = NULL; - $family_name = NULL; - $family_phone = NULL; - $family_visit_pri = NULL; - $family_visit_notes = NULL; - while ($this->db->next_record()) { - $family_id[$i] = $this->db->f('family'); - $family_name[$i] = $this->db->f('name'); - $family_phone[$family_id[$i]] = $family_id[$i] . " ERROR"; - $family_visit_pri[$family_id[$i]] = $this->db->f('visit_pri'); - $family_visit_notes[$family_id[$i]] = $this->db->f('visit_notes'); - $i++; + while ( $this->db->next_record()) { $total_families++; - } - - $sql = "SELECT * FROM tc_individual where valid=1"; - $this->db->query($sql,__LINE__,__FILE__); - while ($this->db->next_record()) { - $family = $this->db->f('family'); + $id = $this->db->f('family'); + $name = $this->db->f('name'); $phone = $this->db->f('phone'); - $family_phone[$family] = $phone; - } - - $max = count($family_id); - - for($i=0; $i < $max; $i++) { - $id = $family_id[$i]; - $name = $family_name[$i]; - $phone = $family_phone[$id]; - $vis_pri = $family_visit_pri[$id]; - $vis_notes = $family_visit_notes[$id]; + $vis_pri = $this->db->f('priority'); + $vis_notes = $this->db->f('notes'); // If this family has had a yearly visit this year, don't show them on the schedule list $year_start = $year - 1 . "-12-31"; $year_end = $year + 1 . "-01-01"; @@ -2264,9 +2236,9 @@ class tc if(!$this->db2->next_record()) { $sql = "SELECT * FROM tc_visit WHERE family=" . $id . " AND companionship=0 ORDER BY date DESC"; - $this->db->query($sql,__LINE__,__FILE__); - if($this->db->next_record()) { - $date = $this->db->f('date'); + $this->db3->query($sql,__LINE__,__FILE__); + if($this->db3->next_record()) { + $date = $this->db3->f('date'); } else { $date = ""; } @@ -2980,7 +2952,7 @@ class tc for ($i=0; $i < count($visit_list); $i++) { $this->nextmatchs->template_alternate_row_color(&$this->t); - $sql = "SELECT * FROM tc_family WHERE family=".$visit_list[$i]['family']; + $sql = "SELECT * FROM tc_family AS tf JOIN tc_individual AS ti WHERE tf.individual=ti.individual AND tf.family=".$visit_list[$i]['family']; $this->db->query($sql,__LINE__,__FILE__); $this->db->next_record(); @@ -3008,7 +2980,7 @@ class tc } // List the families that are available to record a visit against - $sql = "SELECT * FROM tc_family WHERE companionship != 0 and valid=1"; + $sql = "SELECT * FROM tc_family AS tf JOIN tc_individual AS ti WHERE tf.individual=ti.individual AND tf.companionship != 0 and tf.valid=1"; $this->db->query($sql,__LINE__,__FILE__); $total_records = $this->db->num_rows(); @@ -3595,18 +3567,14 @@ class tc $i++; } - $sql = "SELECT * FROM tc_family where valid=1 and individual != 0 ORDER BY name ASC"; + $sql = "SELECT * FROM tc_family AS tf JOIN tc_individual AS ti WHERE tf.individual=ti.individual AND tf.valid=1 AND tf.individual != 0 ORDER BY ti.name ASC"; $this->db->query($sql,__LINE__,__FILE__); $i=0; while ($this->db->next_record()) { $family_id[$i] = $this->db->f('family'); $family_name[$i] = $this->db->f('name'); $familyid2name[$family_id[$i]] = $family_name[$i]; - $sql = "SELECT * FROM tc_individual where family='$family_id[$i]' and hh_position='Head of Household'"; - $this->db2->query($sql,__LINE__,__FILE__); - if($this->db2->next_record()) { - $familyid2address[$family_id[$i]] = $this->db2->f('address'); - } + $familyid2address[$family_id[$i]] = $this->db->f('address'); $i++; } array_multisort($family_name, $family_id); @@ -4354,18 +4322,18 @@ class tc } if($family > 0) { - $sql = "SELECT * FROM tc_family where family='$family'"; + $sql = "SELECT * FROM tc_family WHERE family='$family'"; $this->db2->query($sql,__LINE__,__FILE__); if($this->db2->next_record()) { - $family_name = $this->db2->f('name'); - $phone = $this->db2->f('phone'); $individual = $this->db2->f('individual'); - $appt_name = $family_name . " Family Visit"; $sql = "SELECT * FROM tc_individual where individual='$individual'"; $this->db3->query($sql,__LINE__,__FILE__); if($this->db3->next_record()) { $phone = $this->db3->f('phone'); + $family_name = $this->db3->f('name'); + $phone = $this->db3->f('phone'); } + $appt_name = $family_name . " Family Visit"; $duration = $this->default_visit_appt_duration * 60; } } diff --git a/sql/tc.sql b/sql/tc.sql index 1a8cea3..c42555e 100644 --- a/sql/tc.sql +++ b/sql/tc.sql @@ -110,7 +110,6 @@ CREATE TABLE `tc_individual` ( `address` varchar(255) default NULL, `phone` varchar(12) default NULL, `email` varchar(120) default NULL, - `family` int(16) unsigned default NULL, `hh_position` enum('Head of Household','Spouse','Other') DEFAULT 'Other', `priesthood` enum('High Priest','Elder','Priest','Teacher','Deacon','Unordained') DEFAULT NULL, `steward` enum('High Priest','Elder') DEFAULT NULL, -- 2.34.1