- # 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";
+ # loop through each mls companionship and see if there are any changes to companions or familes from what we have in 3rd Counselor
+ for my $companionship (keys %$companionships) {
+ #print "companionship = $companionship\n";
+ my $changed = 0;
+ my $tc_companionship_id = 0;
+ my $district = $companionships->{$companionship}->{'district'};
+ #print "district = $district\n";
+ for my $companion (keys %{$companionships->{$companionship}->{'companions'}}) {
+ #print "companion = $companion\n";
+ # see if $companion is in tc_companion
+ $sth = $dbh->prepare("SELECT * FROM tc_companion AS tc JOIN (tc_individual AS ti, tc_companionship AS tp) WHERE tc.individual=ti.individual AND tc.companionship=tp.companionship AND tp.mls_id=$companionship AND ti.name='$companion' COLLATE latin1_general_cs AND tc.valid=1");
+ $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) {
+ print " $companion is not in tc_companion\n";
+ $changed = 1;
+ next;
+ }
+ $db_individual = $data[0]->{'individual'};
+ if ($tc_companionship_id == 0) {
+ # set tc_companionship to what was in database
+ $tc_companionship_id = $data[0]->{'companionship'};
+ } elsif ($tc_companionship_id != $data[0]->{'companionship'}) {
+ # companionship doesn't match what was in the database, we have a change
+ print " companionship doesn't match what is in database, $tc_companionship_id != $data[0]->{'companionship'}\n";
+ $changed = 1;
+ last;
+ }
+
+ # get list of families assigned to this companionship in database
+ #print "SELECT * FROM tc_family AS tf JOIN tc_individual AS ti WHERE tf.individual=ti.individual AND tf.companionship=$tc_companionship_id AND tf.valid=1\n";
+ $sth = $dbh->prepare("SELECT * FROM tc_family AS tf JOIN tc_individual AS ti WHERE tf.individual=ti.individual AND tf.companionship=$tc_companionship_id AND tf.valid=1");
+ $sth->execute or die "-E- DB error: $DBI::errstr\n";
+ @data = ();
+ while ($sqlhashref = $sth->fetchrow_hashref) { push(@data, $sqlhashref); }
+ for my $row (@data) {
+ #print "name: $row->{'name'}\n";
+ #print "companionship: $row->{'companionship'}\n";
+ # if family is not assigned, we have a change
+ if (! exists $companionships->{$companionship}->{'families'}->{$row->{'name'}}) {
+ print " family is not assigned, we have a change: $row->{'name'}\n";
+ $changed = 1;
+ last;
+ }
+ # if companionship assigned to family in database isn't this companionship, we have a change
+ if ($tc_companionship_id != $row->{'companionship'}) {
+ "companionship assigned to this family in the database isn't this companionship: $tc_companionship_id, $row->{'companionship'}\n";
+ $changed = 1;
+ last;
+ }
+ }
+
+ # loop through families from HomeTeaching.csv
+ for my $family (keys %{$companionships->{$companionship}->{'families'}}) {
+ #print "SELECT * FROM tc_family AS tf JOIN (tc_companionship AS tc, tc_individual AS ti) WHERE tf.companionship=tc.companionship AND tf.individual=ti.individual AND ti.name=\"$family\" COLLATE latin1_general_cs AND tf.valid=1\n";
+ $sth = $dbh->prepare("SELECT * FROM tc_family AS tf JOIN (tc_companionship AS tc, tc_individual AS ti) WHERE tf.companionship=tc.companionship AND tf.individual=ti.individual AND ti.name=\"$family\" COLLATE latin1_general_cs AND tf.valid=1");
+ $sth->execute or die "-E- DB error: $DBI::errstr\n";
+ # if family isn't in tc_family table, we have a change
+ # if family in tc_family has a different companionship assigned to it, we have a change
+ # no rows here means we have a change
+ @familydata = ();
+ while ($sqlhashref = $sth->fetchrow_hashref) { push(@familydata, $sqlhashref); }
+ if (scalar @familydata == 0) {
+ print " new family, not in db: $family\n";
+ $changed = 1;
+ last;
+ }
+ }
+
+ # last check to break out if we discovered a change
+ if ($changed == 1) { last; }
+ }
+
+ # we found a change to the companionship
+ if ($changed == 1) {
+ # invalidate existing companionship in database
+ print " invalidating companionship:$tc_companionship_id\n";
+ $sth = $dbh->prepare("UPDATE tc_companionship SET valid=0 WHERE companionship='$tc_companionship_id'");
+ $sth->execute or die "-E- DB error: $DBI::errstr\n";
+
+ # create new companionship in database
+ print " creating a new companionship\n";
+ $sth = $dbh->prepare("INSERT INTO tc_companionship values (NULL,$companionship,$district,1)");
+ $sth->execute or die "-E- DB error: $DBI::errstr\n";
+ $comp_id = $dbh->last_insert_id(NULL,NULL,'tc_companionship',NULL);
+
+ # create new companions in database
+ for my $companion (keys %{$companionships->{$companionship}->{'companions'}}) {
+ print " companion=$companion\n";
+ # if companion exists, preserve scheduling_priority
+ #print "SELECT tc.* FROM tc_companion AS tc JOIN (tc_individual AS ti, tc_companionship AS tp) WHERE tc.individual=ti.individual AND tc.companionship=tp.companionship AND tp.mls_id=$companionship AND ti.name=\"$companion\" AND tc.valid=1\n";
+ $sth = $dbh->prepare("SELECT tc.* FROM tc_companion AS tc JOIN (tc_individual AS ti, tc_companionship AS tp) WHERE tc.individual=ti.individual AND tc.companionship=tp.companionship AND tp.mls_id=$companionship AND ti.name=\"$companion\" AND tc.valid=1");
+ $sth->execute or die "-E- DB error: $DBI::errstr\n";
+ if ($sqlhashref = $sth->fetchrow_hashref) {
+ my $individual = $sqlhashref->{'individual'};
+ my $scheduling_priority = $sqlhashref->{'scheduling_priority'};
+ print " adding new companion $individual\n";
+ #print "INSERT INTO tc_companion values (NULL,$individual,$comp_id,$scheduling_priority,1)\n";
+ $sth = $dbh->prepare("INSERT INTO tc_companion values (NULL,$individual,$comp_id,$scheduling_priority,1)");
+ $sth->execute or die "-E- DB error: $DBI::errstr\n";
+ } else {
+ # new companion, create scheduling_priority first
+ print " adding new companion without existing scheduling_priority\n";
+ $sth = $dbh->prepare("INSERT INTO tc_scheduling_priority values (NULL,30,'')");
+ $sth->execute or die "-E- DB error: $DBI::errstr\n";
+ my $scheduling_priority = $dbh->last_insert_id(NULL,NULL,'tc_scheduling_priority',NULL);
+
+ #print "SELECT * FROM tc_individual WHERE name=\"$companion\" AND valid='1'\n";
+ $sth = $dbh->prepare("SELECT * FROM tc_individual WHERE name=\"$companion\" COLLATE latin1_general_cs AND valid='1'");
+ $sth->execute or die "-E- DB error: $DBI::errstr\n";
+ if ($sqlhashref = $sth->fetchrow_hashref) {
+ my $individual = $sqlhashref->{'individual'};
+ #print "INSERT INTO tc_companion values (NULL,$individual,$comp_id,$scheduling_priority,1)\n";
+ $sth = $dbh->prepare("INSERT INTO tc_companion values (NULL,$individual,$comp_id,$scheduling_priority,1)");
+ $sth->execute or die "-E- DB error: $DBI::errstr\n";
+ } else {
+ die "trying to add $companion as a companion but doesn't exist in tc_individual - how is this possible!!!\n";
+ }
+ }
+ }
+
+ # invalidate existing old companions in database
+ #print "UPDATE tc_companion SET valid=0 WHERE companionship=$tc_companionship_id AND valid=1\n";
+ $sth = $dbh->prepare("UPDATE tc_companion SET valid=0 WHERE companionship=$tc_companionship_id AND valid=1");
+ $sth->execute or die "-E- DB error: $DBI::errstr\n";
+
+ # don't worry about linking families, that will be done in update_tc_family_table()