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
#| 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 | |
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();
}
}
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";
&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();
{
var $db;
var $db2;
+ var $db3;
var $t;
var $nextmatchs;
var $grants;
$table_data.= "<tr bgcolor=#d3dce3><td colspan=20><table><tr>$companion_table_entry</tr></table><hr></td></tr>";
// 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;
$table_data.= "<tr bgcolor=#d3dce3><td colspan=20><table><tr>$companion_table_entry</tr></table><hr></td></tr>";
// 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');
$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;
$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);
// 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";
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 = "";
}
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();
}
// 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();
$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);
}
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;
}
}
projects / eq / .git / commitdiff