X-Git-Url: http://git.pippins.net/embedvideo/.git/static/gitweb.js?a=blobdiff_plain;f=bin%2Fimport_ward_data;h=560650a517eb92d278497134d91b111962cd114d;hb=adeefb69d6de73bde041fd236d418757f2920f72;hp=909f3ba9490e7a47d63a8e714b773a05f216b46c;hpb=b569f0e85df791ecd13ed18a9378424bbd570558;p=eq%2F.git diff --git a/bin/import_ward_data b/bin/import_ward_data index 909f3ba..560650a 100755 --- a/bin/import_ward_data +++ b/bin/import_ward_data @@ -108,34 +108,32 @@ sub print_hash #+-------------+------------------+------+-----+---------+----------------+ #| Field | Type | Null | Key | Default | Extra | #+-------------+------------------+------+-----+---------+----------------+ -#| indiv | int(16) unsigned | | PRI | NULL | auto_increment | -#| indiv_id | int(16) unsigned | | | NULL | | +#| individual | int(16) unsigned | | PRI | NULL | auto_increment | +#| mls_id | int(16) unsigned | | | NULL | | #| name | varchar(60) | YES | | NULL | | #| phone | varchar(12) | YES | | NULL | | #| email | varchar(120) | YES | | NULL | | #| priesthood | enum | YES | | NULL | | #| ppi_pri | int(10) unsigned | YES | | 1 | | #| ppi_notes | varchar(128) | YES | | NULL | | -#| int_pri | int(10) unsigned | YES | | 1 | | -#| int_notes | varchar(128) | YES | | NULL | | +#| hti_pri | int(10) unsigned | YES | | 1 | | +#| hti_notes | varchar(128) | YES | | NULL | | #| attending | tinyint(1) | YES | | 0 | | #| valid | tinyint(1) | YES | | NULL | | #+-------------+------------------+------+-----+---------+----------------+ -sub update_tc_indiv_table +sub update_tc_individual_table { - print "\n-> Updating tc_indiv table\n"; + print "\n-> Updating tc_individual 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_indiv set valid=0"); + $sth = $dbh->prepare("update tc_individual set valid=0"); $sth->execute or die "-E- DB error: $DBI::errstr\n"; foreach $index (keys %membership_data) { $hashref = $membership_data{$index}; $id = $membership_data{$index}{'Indiv ID'}; - $indiv_name = $membership_data{$index}{'Preferred Name'}; - $birthday = $membership_data{$index}{'Birth'}; - $birthday =~ /(\d+) (\S+) (\d+)/; $day=$1; $month=$monthname2num{$2}; $year=$3; + $individual_name = $membership_data{$index}{'Preferred Name'}; $address = $membership_data{$index}{'Street 1'}; if($membership_data{$index}{'Street 2'} ne "") { $address .= " " . $membership_data{$index}{'Street 2'}; @@ -154,52 +152,46 @@ sub update_tc_indiv_table if($phone =~ /^\(\d\d\d\) (\d\d\d-\d\d\d\d)/) { $phone = "$1-$2"; } $email = $membership_data{$index}{'indiv E-mail'}; if ($email eq "") { $email = $membership_data{$index}{'Household E-mail'}; } - $sth = $dbh->prepare("select * from tc_indiv where name=\"$indiv_name\""); + $sth = $dbh->prepare("select * from tc_individual where name=\"$individual_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) { - # No existing records found for this indiv, make a new entry - print " Adding new indiv: $indiv_name\n"; - $sth = $dbh->prepare("insert into tc_indiv values (NULL,'$id',\"$indiv_name\",'$year-$month-$day','$address','$phone','$email','','$hhposition','$priesthood','','$default_interview_pri','','$default_interview_pri','',$attending,1)"); + # 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','','$default_interview_pri','','$default_interview_pri','',$attending,1)"); $sth->execute or die "-E- DB error: $DBI::errstr\n"; } elsif($rows == 1) { - # An existing record was found for this indiv, update it - print " Updating existing indiv: $indiv_name\n"; - $sth = $dbh->prepare("update tc_indiv set valid=1 where name=\"$indiv_name\""); + # An existing record was found for this individual, update it + print " Updating existing individual: $individual_name\n"; + $sth = $dbh->prepare("update tc_individual set valid=1 where name=\"$individual_name\""); $sth->execute or die "-E- DB error: $DBI::errstr\n"; if($phone ne "") { - $sth = $dbh->prepare("update tc_indiv set phone='$phone' where name=\"$indiv_name\""); + $sth = $dbh->prepare("update tc_individual set phone='$phone' where name=\"$individual_name\""); } else { - $sth = $dbh->prepare("update tc_indiv set phone=NULL where name=\"$indiv_name\""); - } - $sth->execute or die "-E- DB error: $DBI::errstr\n"; - if($birthday ne "") { - $sth = $dbh->prepare("update tc_indiv set birthday='$year-$month-$day' where name=\"$indiv_name\""); - } else { - $sth = $dbh->prepare("update tc_indiv set birthday=NULL where name=\"$indiv_name\""); + $sth = $dbh->prepare("update tc_individual set phone=NULL where name=\"$individual_name\""); } $sth->execute or die "-E- DB error: $DBI::errstr\n"; if($address ne "") { - $sth = $dbh->prepare("update tc_indiv set address='$address' where name=\"$indiv_name\""); + $sth = $dbh->prepare("update tc_individual set address='$address' where name=\"$individual_name\""); } else { - $sth = $dbh->prepare("update tc_indiv set address=NULL where name=\"$indiv_name\""); + $sth = $dbh->prepare("update tc_individual set address=NULL where name=\"$individual_name\""); } $sth->execute or die "-E- DB error: $DBI::errstr\n"; - $sth = $dbh->prepare("update tc_indiv set attending='$attending' where name=\"$indiv_name\""); + $sth = $dbh->prepare("update tc_individual set attending='$attending' where name=\"$individual_name\""); $sth->execute or die "-E- DB error: $DBI::errstr\n"; - $sth = $dbh->prepare("update tc_indiv set indiv_id='$id' where name=\"$indiv_name\""); + $sth = $dbh->prepare("update tc_individual set mls_id='$id' where name=\"$individual_name\""); $sth->execute or die "-E- DB error: $DBI::errstr\n"; - $sth = $dbh->prepare("update tc_indiv set priesthood='$priesthood' where name=\"$indiv_name\""); + $sth = $dbh->prepare("update tc_individual set priesthood='$priesthood' where name=\"$individual_name\""); $sth->execute or die "-E- DB error: $DBI::errstr\n"; - $sth = $dbh->prepare("update tc_indiv set email='$email' where name=\"$indiv_name\""); + $sth = $dbh->prepare("update tc_individual set email='$email' where name=\"$individual_name\""); $sth->execute or die "-E- DB error: $DBI::errstr\n"; - $sth = $dbh->prepare("update tc_indiv set hh_position='$hhposition' where name=\"$indiv_name\""); + $sth = $dbh->prepare("update tc_individual set hh_position='$hhposition' where name=\"$individual_name\""); $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 indiv: $indiv_name\n"; + print " -E- More than one record found ($rows) for individual: $individual_name\n"; } } $sth->finish(); @@ -266,10 +258,10 @@ sub update_tc_district_table while($sqlhashref = $sth->fetchrow_hashref) { $supervisor_name = $sqlhashref->{name}; $district = $sqlhashref->{district}; - $sth2 = $dbh->prepare("select * from tc_indiv where name='$supervisor_name'"); + $sth2 = $dbh->prepare("select * from tc_individual where name='$supervisor_name'"); $sth2->execute or die "-E- DB error: $DBI::errstr\n"; $sqlhashref2 = $sth2->fetchrow_hashref; - $supervisor_id = $sqlhashref2->{indiv}; + $supervisor_id = $sqlhashref2->{individual}; $sth2->finish(); $sth2 = $dbh->prepare("update tc_district set supervisor='$supervisor_id' where district='$district'"); $sth2->execute or die "-E- DB error: $DBI::errstr\n"; @@ -283,7 +275,7 @@ sub update_tc_district_table #| Field | Type | Null | Key | Default | Extra | #+---------------+------------------+------+-----+---------+-------+ #| companionship | int(16) unsigned | | | 0 | | -#| indiv | int(16) unsigned | YES | | NULL | | +#| individual | int(16) unsigned | YES | | NULL | | #| district | int(16) unsigned | YES | | NULL | | #| valid | tinyint(1) | YES | | NULL | | #+---------------+------------------+------+-----+---------+-------+ @@ -301,34 +293,34 @@ sub update_tc_companionship_table 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 tc_indiv where name='$indiv_name'"); + $individual_name = $hometeaching_data{$index}{$field}; + if($individual_name eq "") { next; } + $sth2 = $dbh->prepare("select * from tc_individual where name='$individual_name'"); $sth2->execute or die "-E- DB error: $DBI::errstr\n"; $sqlhashref2 = $sth2->fetchrow_hashref; - $indiv = $sqlhashref2->{indiv}; + $individual = $sqlhashref2->{individual}; $id = $hometeaching_data{$index}{'Comp ID'}; $district = $hometeaching_data{$index}{'HT District'}; - $sth = $dbh->prepare("select * from tc_companionship where indiv='$indiv' and companionship='$id'"); + $sth = $dbh->prepare("select * from tc_companionship where individual='$individual' 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 tc_companionship values ($id,'$indiv','$district',1)"); + print " Adding Companion to companionship: $individual_name -> $id\n"; + $sth = $dbh->prepare("insert into tc_companionship values ($id,'$individual','$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 tc_companionship where district='$district' and companionship='$id'"); $sth2->execute or die "-E- DB error: $DBI::errstr\n"; - print " Updating Companionship with indiv: $indiv_name ($indiv) -> $id\n"; - $sth = $dbh->prepare("update tc_companionship set district='$district' where indiv='$indiv' and companionship='$id'"); + print " Updating Companionship with individual: $individual_name ($individual) -> $id\n"; + $sth = $dbh->prepare("update tc_companionship set district='$district' where individual='$individual' and companionship='$id'"); $sth->execute or die "-E- DB error 'district': $DBI::errstr\n"; - $sth = $dbh->prepare("update tc_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 tc_companionship set valid=1 where indiv='$indiv' and companionship='$id'"); + $sth = $dbh->prepare("update tc_companionship set individual='$individual' where individual='$individual' and companionship='$id'"); + $sth->execute or die "-E- DB error 'individual': $DBI::errstr\n"; + $sth = $dbh->prepare("update tc_companionship set valid=1 where individual='$individual' and companionship='$id'"); $sth->execute or die "-E- DB error 'valid': $DBI::errstr\n"; } $sth->finish(); @@ -347,7 +339,7 @@ sub update_tc_companionship_table #| 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 | | +#| individual | int(16) unsigned | YES | | NULL | | #| companionship | int(16) unsigned | YES | | NULL | | #| visit_pri | int(10) unsigned | YES | | 1 | | #| visit_notes | varchar(128) | YES | | NULL | | @@ -397,13 +389,13 @@ sub update_tc_family_table print " -E- More than one record found ($rows) for family name: $family_name\n"; } - # Now update the indiv_id field for this family - $sth = $dbh->prepare("select * from tc_indiv WHERE name='$family_name'"); + # 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) { - $indiv_id = $sqlhashref->{indiv}; - print " Updating family indiv_id: $family_name -> $indiv_id\n"; - $sth = $dbh->prepare("update tc_family set indiv_id=$indiv_id where name_id='$name_id'"); + $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"; } @@ -482,7 +474,7 @@ sub update_tc_visit_table #print "family_id = $family_id\n"; #print "comp_id = $comp_id\n"; - # ignore visits that weren't done by the EQ + # ignore visits that weren't done by the quorum if ($comp_id == 0) { next; } # retrieve the month header if not already done @@ -554,7 +546,7 @@ sub check_for_changed_ids foreach $oldindex (keys %$oldhashref) { - $indiv_id = $oldhashref->{$oldindex}{'Indiv ID'}; + $mls_id = $oldhashref->{$oldindex}{'Indiv ID'}; $hofh_id = $oldhashref->{$oldindex}{'HofH ID'}; $full_name = $oldhashref->{$oldindex}{'Full Name'}; $hh_position = $oldhashref->{$oldindex}{'HH Position'}; @@ -563,9 +555,9 @@ sub check_for_changed_ids foreach $newindex (keys %$newhashref) { if($newhashref->{$newindex}{'Full Name'} eq $full_name && - $indiv_id != $newhashref->{$newindex}{'Indiv ID'}) + $mls_id != $newhashref->{$newindex}{'Indiv ID'}) { - print "-W- Indiv ID for $full_name changed from $indiv_id to $newhashref->{$newindex}{'Indiv ID'}\n"; + print "-W- Indiv ID for $full_name changed from $mls_id to $newhashref->{$newindex}{'Indiv ID'}\n"; $found_problem = 1; } @@ -581,9 +573,9 @@ sub check_for_changed_ids return $found_problem; } -sub update_family_in_tc_indiv_table +sub update_family_in_tc_individual_table { - print "\n-> Updating family info in tc_indiv table\n"; + print "\n-> Updating family info in tc_individual table\n"; foreach $index (keys %membership_data) { @@ -604,8 +596,8 @@ sub update_family_in_tc_indiv_table print " Updating family data for: $name\n"; - # write the family id to the individual's data in tc_indiv - $sth = $dbh->prepare("update tc_indiv set family='$family_id' where name=\"$name\""); + # 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; @@ -615,7 +607,7 @@ sub update_family_in_tc_indiv_table sub update_organization_class_data { - print "\n-> Updating organization class info in tc_indiv table\n"; + print "\n-> Updating organization class info in tc_individual table\n"; foreach $index (keys %organization_class_data) { @@ -625,12 +617,12 @@ sub update_organization_class_data if ($org_class =~ m/Elder/i) { #print " $name: Elder\n"; - $sth = $dbh->prepare("update tc_indiv set steward='Elder' where name=\"$name\""); + $sth = $dbh->prepare("update tc_individual set steward='Elder' where name=\"$name\""); $sth->execute or die "-E- DB error: $DBI::errstr\n"; } if ($org_class =~ m/High Priest/i) { #print " $name: High Priest\n"; - $sth = $dbh->prepare("update tc_indiv set steward='High Priest' where name=\"$name\""); + $sth = $dbh->prepare("update tc_individual set steward='High Priest' where name=\"$name\""); $sth->execute or die "-E- DB error: $DBI::errstr\n"; } } @@ -700,12 +692,12 @@ if($opt_s) { $dbh->disconnect(); exit; } # Now update the various eq DB tables &update_tc_calling_table(); -&update_tc_indiv_table(); +&update_tc_individual_table(); &update_tc_district_table(); &update_tc_companionship_table(); &update_tc_family_table(); &update_tc_visit_table(); -&update_family_in_tc_indiv_table(); +&update_family_in_tc_individual_table(); &update_organization_class_data(); print "\n-> Import Successful! DONE...\n";