bin/import_ward_data

   1 #!/usr/bin/perl
   2
   3 use DBI;
   4 use Getopt::Std;
   5
   6 $mydir = `cd \$(dirname $0) 2>/dev/null; pwd`; chomp($mydir);
   7 unshift @INC,("$mydir/../setup");
   8 if( -f "$mydir/../setup/db_config.local") { require "db_config.local"; }
   9 else { require "db_config"; }
  10
  11 %hometeaching_data = ();
  12 %membership_data = ();
  13 getopts('vsn:o:');
  14
  15 $monthname2num{'Jan'} = '01';
  16 $monthname2num{'Feb'} = '02';
  17 $monthname2num{'Mar'} = '03';
  18 $monthname2num{'Apr'} = '04';
  19 $monthname2num{'May'} = '05';
  20 $monthname2num{'Jun'} = '06';
  21 $monthname2num{'Jul'} = '07';
  22 $monthname2num{'Aug'} = '08';
  23 $monthname2num{'Sep'} = '09';
  24 $monthname2num{'Oct'} = '10';
  25 $monthname2num{'Nov'} = '11';
  26 $monthname2num{'Dec'} = '12';
  27
  28 ######################################################################
  29 # SUBROUTINES
  30 ######################################################################
  31 sub csv_to_hash
  32 {
  33     my ($filename, $hashref) = @_;
  34
  35     open(FILE,$filename) || die "-E- Could not open $filename for reading\n";
  36
  37     my $found_header = 0; my $index = 0;
  38     while(<FILE>)
  39     {
  40         $line = $_;
  41         @data = split /\",/, $line;
  42         if(!$found_header) { @header = @data; $found_header = 1; }
  43         else {
  44             foreach $i (0..$#data-1) {
  45                 $data[$i] =~ s/\"//g;
  46                 $header[$i] =~ s/\"//g;
  47                 $hashref->{$index}{$header[$i]} = $data[$i];
  48                 #print "$index: $i: $header[$i]: $data[$i]\n";
  49             }
  50             $index++;
  51         }
  52     }
  53
  54     close(FILE);
  55 }
  56
  57 sub optional_csv_to_hash
  58 {
  59     my ($filename, $hashref) = @_;
  60
  61     my $opened = open(FILE,$filename);
  62
  63     if ($opened) {
  64         my $found_header = 0; my $index = 0;
  65         while(<FILE>)
  66         {
  67             $line = $_;
  68             @data = split /\",/, $line;
  69             if(!$found_header) { @header = @data; $found_header = 1; }
  70             else {
  71                 foreach $i (0..$#data-1) {
  72                     $data[$i] =~ s/\"//g;
  73                     $header[$i] =~ s/\"//g;
  74                     $hashref->{$index}{$header[$i]} = $data[$i];
  75                     #print "$index: $i: $header[$i]: $data[$i]\n";
  76                 }
  77                 $index++;
  78             }
  79         }
  80
  81     close(FILE);
  82     }
  83     else
  84     {
  85         print "-W- could not open optional csv file $filename\n";
  86     }
  87 }
  88
  89 ######################################################################
  90 sub print_hash
  91 {
  92     my ($hashref) = @_;
  93
  94     foreach $key (sort {$a <=> $b} keys %$hashref) {
  95         print "Index: $key\n";
  96         foreach $field (keys %{$hashref->{$key}}) {
  97             print "$field: $hashref->{$key}{$field}\n";
  98         }
  99         print "\n";
 100     }
 101 }
 102
 103 ######################################################################
 104
 105 # 3RD_INDIV
 106 #+-------------+------------------+------+-----+---------+----------------+
 107 #| Field       | Type             | Null | Key | Default | Extra          |
 108 #+-------------+------------------+------+-----+---------+----------------+
 109 #| indiv       | int(16) unsigned |      | PRI | NULL    | auto_increment |
 110 #| indiv_id    | int(16) unsigned |      |     | NULL    |                |
 111 #| name        | varchar(60)      | YES  |     | NULL    |                |
 112 #| phone       | varchar(12)      | YES  |     | NULL    |                |
 113 #| email       | varchar(120)     | YES  |     | NULL    |                |
 114 #| priesthood  | enum             | YES  |     | NULL    |                |
 115 #| ppi_pri     | int(10) unsigned | YES  |     | 1       |                |
 116 #| ppi_notes   | varchar(128)     | YES  |     | NULL    |                |
 117 #| int_pri     | int(10) unsigned | YES  |     | 1       |                |
 118 #| int_notes   | varchar(128)     | YES  |     | NULL    |                |
 119 #| attending   | tinyint(1)       | YES  |     | 0       |                |
 120 #| valid       | tinyint(1)       | YES  |     | NULL    |                |
 121 #+-------------+------------------+------+-----+---------+----------------+
 122 sub update_tc_indiv_table
 123 {
 124     print "\n-> Updating tc_indiv table\n";
 125
 126     # Set all records to be invalid. Only mark them as valid if they appear on the new list.
 127     $sth = $dbh->prepare("update tc_indiv set valid=0");
 128     $sth->execute or die "-E- DB error: $DBI::errstr\n";
 129
 130     foreach $index (keys %membership_data)
 131     {
 132         $hashref = $membership_data{$index};
 133         $id = $membership_data{$index}{'Indiv ID'};
 134         $indiv_name = $membership_data{$index}{'Preferred Name'};
 135         $birthday = $membership_data{$index}{'Birth'};
 136         $birthday =~ /(\d+) (\S+) (\d+)/; $day=$1; $month=$monthname2num{$2}; $year=$3;
 137         $address = $membership_data{$index}{'Street 1'};
 138         if($membership_data{$index}{'Street 2'} ne "") {
 139             $address .= " " . $membership_data{$index}{'Street 2'};
 140         }
 141         $phone = $membership_data{$index}{'Household Phone'};
 142         $priesthood = $membership_data{$index}{'Priesthood'};
 143         $hhposition = $membership_data{$index}{'HH Position'};
 144         $organization = $organization_by_id{$id};
 145         $attending = 0;
 146         if(($organization =~ /Elders/) ||
 147            ($organization =~ /Young Men/) ||
 148            ($organization =~ /Sunday School/) ||
 149            ($organization =~ /Primary/)
 150            ) { $attending = 1; }
 151         if($phone =~ /(\d\d\d-\d\d\d\d)/) { $phone = "$areacode-$1"; }
 152         if($phone =~ /^\(\d\d\d\) (\d\d\d-\d\d\d\d)/) { $phone = "$1-$2"; }
 153         $email = $membership_data{$index}{'indiv E-mail'};
 154         if ($email eq "") { $email = $membership_data{$index}{'Household E-mail'}; }
 155         $sth = $dbh->prepare("select * from tc_indiv where name=\"$indiv_name\"");
 156         $sth->execute or die "-E- DB error: $DBI::errstr\n";
 157         my @data = ();
 158         while($sqlhashref = $sth->fetchrow_hashref) { push(@data, $sqlhashref); }
 159         my $rows = scalar @data;
 160         if($rows == 0) {
 161             # No existing records found for this indiv, make a new entry
 162             print "   Adding new indiv: $indiv_name\n";
 163             $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)");
 164             $sth->execute or die "-E- DB error: $DBI::errstr\n";
 165         } elsif($rows == 1) {
 166             # An existing record was found for this indiv, update it
 167             print "   Updating existing indiv: $indiv_name\n";
 168             $sth = $dbh->prepare("update tc_indiv set valid=1 where name=\"$indiv_name\"");
 169             $sth->execute or die "-E- DB error: $DBI::errstr\n";
 170             if($phone ne "") {
 171                 $sth = $dbh->prepare("update tc_indiv set phone='$phone' where name=\"$indiv_name\"");
 172             } else {
 173                 $sth = $dbh->prepare("update tc_indiv set phone=NULL where name=\"$indiv_name\"");
 174             }
 175             $sth->execute or die "-E- DB error: $DBI::errstr\n";
 176             if($birthday ne "") {
 177                 $sth = $dbh->prepare("update tc_indiv set birthday='$year-$month-$day' where name=\"$indiv_name\"");
 178             } else {
 179                 $sth = $dbh->prepare("update tc_indiv set birthday=NULL where name=\"$indiv_name\"");
 180             }
 181             $sth->execute or die "-E- DB error: $DBI::errstr\n";
 182             if($address ne "") {
 183                 $sth = $dbh->prepare("update tc_indiv set address='$address' where name=\"$indiv_name\"");
 184             } else {
 185                 $sth = $dbh->prepare("update tc_indiv set address=NULL where name=\"$indiv_name\"");
 186             }
 187             $sth->execute or die "-E- DB error: $DBI::errstr\n";
 188             $sth = $dbh->prepare("update tc_indiv set attending='$attending' where name=\"$indiv_name\"");
 189             $sth->execute or die "-E- DB error: $DBI::errstr\n";
 190             $sth = $dbh->prepare("update tc_indiv set indiv_id='$id' where name=\"$indiv_name\"");
 191             $sth->execute or die "-E- DB error: $DBI::errstr\n";
 192             $sth = $dbh->prepare("update tc_indiv set priesthood='$priesthood' where name=\"$indiv_name\"");
 193             $sth->execute or die "-E- DB error: $DBI::errstr\n";
 194             $sth = $dbh->prepare("update tc_indiv set email='$email' where name=\"$indiv_name\"");
 195             $sth->execute or die "-E- DB error: $DBI::errstr\n";
 196             $sth = $dbh->prepare("update tc_indiv set hh_position='$hhposition' where name=\"$indiv_name\"");
 197             $sth->execute or die "-E- DB error: $DBI::errstr\n";
 198         } else {
 199             # More than one record was found. Error! This shouldn't happen.
 200             print "   -E- More than one record found ($rows) for indiv: $indiv_name\n";
 201         }
 202     }
 203     $sth->finish();
 204 }
 205
 206 # 3RD_CALLING
 207 #+--------------+------------------+------+-----+---------+-------+
 208 #| Field        | Type             | Null | Key | Default | Extra |
 209 #+--------------+------------------+------+-----+---------+-------+
 210 #| indiv_id     | int(16) unsigned | YES  |     | NULL    |       |
 211 #| name         | varchar(30)      | YES  |     | NULL    |       |
 212 #| organization | varchar(30)      | YES  |     | NULL    |       |
 213 #| position     | varchar(30)      | YES  |     | NULL    |       |
 214 #| sequence     | int(16) unsigned | YES  |     | NULL    |       |
 215 #| sustained    | date             | YES  |     | NULL    |       |
 216 #+--------------+------------------+------+-----+---------+-------+
 217 sub update_tc_calling_table()
 218 {
 219     print "\n-> Updating tc_calling table\n";
 220
 221     #print "-> Organization Data Dump\n\n";
 222     #&print_hash(\%organization_data);
 223
 224     # Delete all records from the calling table. We have no history to
 225     # save here. Just re-populate with the latest calling information.
 226     $sth = $dbh->prepare("delete from tc_calling ");
 227     $sth->execute or die "-E- DB error: $DBI::errstr\n";
 228
 229     foreach $index (keys %organization_data)
 230     {
 231         $indiv_id = $organization_data{$index}{'Indiv ID'};
 232         $name = $organization_data{$index}{'Indiv Name'};
 233         $name =~ s/\'/\\'/g; #'
 234         $organization = $organization_data{$index}{'Organization'};
 235         $organization_by_name{$name} = $organization;
 236         $organization_by_id{$indiv_id} = $organization;
 237         $position = $organization_data{$index}{'Position'};
 238         $sequence = $organization_data{$index}{'Org Seq'};
 239         $sustained = $organization_data{$index}{'Sustained'};
 240         $sustained =~ /(\S+) (\d+)/; $month=$1; $year=$2;
 241         if($name eq "") { next; }
 242         print "   Adding new Calling: $name -> $position\n";
 243         $sth = $dbh->prepare("insert into tc_calling values ('$indiv_id','$name','$organization','$position','$sequence','$month $year')");
 244         $sth->execute or die "-E- DB error: $DBI::errstr\n";
 245     }
 246 }
 247
 248 # 3RD_DISTRICT
 249 #+------------+------------------+------+-----+---------+-------+
 250 #| Field      | Type             | Null | Key | Default | Extra |
 251 #+------------+------------------+------+-----+---------+-------+
 252 #| district   | int(16) unsigned |      | PRI | 0       |       |
 253 #| name       | varchar(30)      | YES  |     | NULL    |       |
 254 #| supervisor | int(16) unsigned | YES  |     | NULL    |       |
 255 #| valid      | tinyint(1)       | YES  |     | NULL    |       |
 256 #+------------+------------------+------+-----+---------+-------+
 257 sub update_tc_district_table
 258 {
 259     # Districts should be created by hand. This subroutine only
 260     # updates the supervisor's ID in each district.
 261     print "\n-> Updating tc_district table\n";
 262     $sth = $dbh->prepare("select * from tc_district");
 263     $sth->execute or die "-E- DB error: $DBI::errstr\n";
 264     while($sqlhashref = $sth->fetchrow_hashref) {
 265         $supervisor_name = $sqlhashref->{name};
 266         $district = $sqlhashref->{district};
 267         $sth2 = $dbh->prepare("select * from tc_indiv where name='$supervisor_name'");
 268         $sth2->execute or die "-E- DB error: $DBI::errstr\n";
 269         $sqlhashref2 = $sth2->fetchrow_hashref;
 270         $supervisor_id = $sqlhashref2->{indiv};
 271         $sth2->finish();
 272         $sth2 = $dbh->prepare("update tc_district set supervisor='$supervisor_id' where district='$district'");
 273         $sth2->execute or die "-E- DB error: $DBI::errstr\n";
 274         $sth2->finish();
 275     }
 276     $sth->finish();
 277 }
 278
 279 # 3RD_COMPANIONSHIP
 280 #+---------------+------------------+------+-----+---------+-------+
 281 #| Field         | Type             | Null | Key | Default | Extra |
 282 #+---------------+------------------+------+-----+---------+-------+
 283 #| companionship | int(16) unsigned |      |     | 0       |       |
 284 #| indiv         | int(16) unsigned | YES  |     | NULL    |       |
 285 #| district      | int(16) unsigned | YES  |     | NULL    |       |
 286 #| valid         | tinyint(1)       | YES  |     | NULL    |       |
 287 #+---------------+------------------+------+-----+---------+-------+
 288 sub update_tc_companionship_table
 289 {
 290     print "\n-> Updating tc_companionship table\n";
 291
 292     # First, mark all existing companionships as invalid in case they have been dissolved
 293     $sth = $dbh->prepare("update tc_companionship set valid=0");
 294     $sth->execute or die "-E- DB error: $DBI::errstr\n";
 295
 296     foreach $index (keys %hometeaching_data)
 297     {
 298         $hashref = $hometeaching_data{$index};
 299         foreach $key (keys %$hashref) {
 300             if($key =~ /Quorum/i && $hometeaching_data{$index}{$key} =~ /Elders/i) {
 301                 foreach $field ("Home Teacher 1","Home Teacher 2") {
 302                     $indiv_name = $hometeaching_data{$index}{$field};
 303                     if($indiv_name eq "") { next; }
 304                     $sth2 = $dbh->prepare("select * from tc_indiv where name='$indiv_name'");
 305                     $sth2->execute or die "-E- DB error: $DBI::errstr\n";
 306                     $sqlhashref2 = $sth2->fetchrow_hashref;
 307                     $indiv = $sqlhashref2->{indiv};
 308                     $id = $hometeaching_data{$index}{'Comp ID'};
 309                     $district = $hometeaching_data{$index}{'HT District'};
 310                     $sth = $dbh->prepare("select * from tc_companionship where indiv='$indiv' and companionship='$id'");
 311                     $sth->execute or die "-E- DB error: $DBI::errstr\n";
 312                     my @data = ();
 313                     while($sqlhashref = $sth->fetchrow_hashref) { push(@data, $sqlhashref); }
 314                     my $rows = scalar @data;
 315                     if($rows == 0) {
 316                         # No existing records found for this companionship, make a new entry
 317                         print "   Adding Companion to companionship: $indiv_name -> $id\n";
 318                         $sth = $dbh->prepare("insert into tc_companionship values ($id,'$indiv','$district',1)");
 319                         $sth->execute or die "-E- DB error: $DBI::errstr\n";
 320                     } else {
 321                         # An existing companionship was found for this companionship, update it
 322                         $sth2 = $dbh->prepare("select * from tc_companionship where district='$district' and companionship='$id'");
 323                         $sth2->execute or die "-E- DB error: $DBI::errstr\n";
 324                         print "   Updating Companionship with indiv: $indiv_name ($indiv) -> $id\n";
 325                         $sth = $dbh->prepare("update tc_companionship set district='$district' where indiv='$indiv' and companionship='$id'");
 326                         $sth->execute or die "-E- DB error 'district': $DBI::errstr\n";
 327                         $sth = $dbh->prepare("update tc_companionship set indiv='$indiv' where indiv='$indiv' and companionship='$id'");
 328                         $sth->execute or die "-E- DB error 'indiv': $DBI::errstr\n";
 329                         $sth = $dbh->prepare("update tc_companionship set valid=1 where indiv='$indiv' and companionship='$id'");
 330                         $sth->execute or die "-E- DB error 'valid': $DBI::errstr\n";
 331                     }
 332                     $sth->finish();
 333                     $sth2->finish();
 334                 }
 335             }
 336         }
 337     }
 338 }
 339
 340 # 3RD_FAMILY
 341 #+---------------+------------------+------+-----+---------+-------+
 342 #| Field         | Type             | Null | Key | Default | Extra |
 343 #+---------------+------------------+------+-----+---------+-------+
 344 #| family        | int(16) unsigned |      | PRI | 0       |   A   |
 345 #| hofh_id       | int(16) unsigned | YES  |     | NULL    |       |
 346 #| name          | varchar(30)      | YES  |     | NULL    |       |
 347 #| name_id       | varchar(30)      | YES  |     | NULL    |       |
 348 #| indiv_id      | int(16) unsigned | YES  |     | NULL    |       |
 349 #| companionship | int(16) unsigned | YES  |     | NULL    |       |
 350 #| visit_pri     | int(10) unsigned | YES  |     | 1       |       |
 351 #| visit_notes   | varchar(128)     | YES  |     | NULL    |       |
 352 #| valid         | tinyint(1)       | YES  |     | NULL    |       |
 353 #+---------------+------------------+------+-----+---------+-------+
 354 sub update_tc_family_table
 355 {
 356     print "\n-> Updating tc_family table\n";
 357
 358     # Set all records to be invalid. Only mark them as valid if they appear on the new list.
 359     $sth = $dbh->prepare("update tc_family set valid=0");
 360     $sth->execute or die "-E- DB error: $DBI::errstr\n";
 361     $sth = $dbh->prepare("update tc_family set companionship=0");
 362     $sth->execute or die "-E- DB error: $DBI::errstr\n";
 363
 364     foreach $index (keys %membership_data)
 365     {
 366         $hashref = $membership_data{$index};
 367         foreach $key (keys %$hashref) {
 368             if($key =~ /HH Position/i && $membership_data{$index}{$key} =~ /Head of Household/i) {
 369                 $family_name = $membership_data{$index}{'Preferred Name'};
 370                 $family_name =~ s/\'/\\'/g; #'
 371                 $id = $membership_data{$index}{'HofH ID'};
 372                 $name_id = uc($family_name);
 373
 374                 # Find out how many families match this family's name
 375                 $sth = $dbh->prepare("select * from tc_family where name_id='$name_id'");
 376                 $sth->execute or die "-E- DB error: $DBI::errstr\n";
 377                 my @data = ();
 378                 while($sqlhashref = $sth->fetchrow_hashref) { push(@data, $sqlhashref); }
 379                 my $rows = scalar @data;
 380
 381                 if($rows == 0) {
 382                     # No existing records found for this family, make a new entry
 383                     print "   Adding new Family: $family_name\n";
 384                     $sth = $dbh->prepare("insert into tc_family values (NULL,$id,'$family_name','$name_id','0','0','$default_visit_pri','',1)");
 385                     $sth->execute or die "-E- DB error: $DBI::errstr\n";
 386                 } elsif($rows == 1) {
 387                     # An existing record was found for this family, update it
 388                     print "   Updating existing family: $family_name\n";
 389                     $sth = $dbh->prepare("update tc_family set hofh_id=$id where name_id='$name_id'");
 390                     $sth->execute or die "-E- DB error: $DBI::errstr\n";
 391                     $sth = $dbh->prepare("update tc_family set valid=1 where name_id='$name_id'");
 392                     $sth->execute or die "-E- DB error: $DBI::errstr\n";
 393                 } else {
 394                     # More than one record was found. Error! This shouldn't happen.
 395                     print "   -E- More than one record found ($rows) for family name: $family_name\n";
 396                 }
 397
 398                 # Now update the indiv_id field for this family
 399                 $sth = $dbh->prepare("select * from tc_indiv WHERE name='$family_name'");
 400                 $sth->execute or die "-E- DB error: $DBI::errstr\n";
 401                 while($sqlhashref = $sth->fetchrow_hashref) {
 402                     $indiv_id = $sqlhashref->{indiv};
 403                     print "   Updating family indiv_id: $family_name -> $indiv_id\n";
 404                     $sth = $dbh->prepare("update tc_family set indiv_id=$indiv_id where name_id='$name_id'");
 405                     $sth->execute or die "-E- DB error: $DBI::errstr\n";
 406                 }
 407
 408                 # Now update the hometeaching field for this family
 409                 foreach $index (keys %hometeaching_data)
 410                 {
 411                     $hashref = $hometeaching_data{$index};
 412                     foreach $key (keys %$hashref) {
 413                         if($hometeaching_data{$index}{'Household'} =~ /(\S+)\s+(\S+),\s+(\S+)\s+(.*)/) {
 414                             $a = $1; $b = $2; $c = $3; $d = $4;
 415                             if($family_name =~ /$a/ && $hometeaching_data{$index}{'Household'} !~ /$family_name/i) {
 416                                 print "I: Adjusting hometeaching match from: $hometeaching_data{$index}{'Household'} to $a, $c $d\n";
 417                                 $hometeaching_data{$index}{'Household'} = "$a, $c $d";
 418                             }
 419                         }
 420                         if($key =~ /Quorum/i &&
 421                            $hometeaching_data{$index}{$key} =~ /Elders/i &&
 422                            $hometeaching_data{$index}{'Household'} =~ /$family_name/i &&
 423                            $data[0]->{companionship} != $hometeaching_data{$index}{'Comp ID'}
 424                            )
 425                         {
 426                             print "   Updating hometeaching assignment for $family_name family\n";
 427                             $companionship = $hometeaching_data{$index}{'Comp ID'};
 428                             $sth = $dbh->prepare("update tc_family set companionship='$companionship' where name_id='$name_id'");
 429                             $sth->execute or die "-E- DB error: $DBI::errstr\n";
 430                         }
 431                     }
 432                 }
 433                 $sth->finish();
 434             }
 435         }
 436     }
 437 }
 438
 439 # 3RD_VISIT
 440 #+----------------+------------------+------+-----+---------+-------+
 441 #| Field          | Type             | Null | Key | Default | Extra |
 442 #+----------------+------------------+------+-----+---------+-------+
 443 #| visit          | int(16) unsigned |      | PRI | 0       |   A   |
 444 #| family         | int(16) unsigned | YES  | UNI | NULL    |       |
 445 #| companionship  | int(16) unsigned | YES  |     | NULL    |       |
 446 #| date           | date             | YES  |     | NULL    |       |
 447 #| notes          | varchar(128)     | YES  |     | NULL    |       |
 448 #| visited        | varchar(1)       | YES  |     | NULL    |       |
 449 #+----------------+------------------+------+-----+---------+-------+
 450 sub update_tc_visit_table
 451 {
 452         print "\n-> updating tc_visit table\n";
 453
 454         my $month_header_retrieved = 0;
 455         my $month_header;
 456         my @data_months;
 457         my %months = ('Jan', 1, 'Feb', 2, 'Mar', 3, 'Apr', 4, 'May', 5, 'Jun', 6, 'Jul', 7, 'Aug', 8, 'Sep', 9, 'Oct', 10, 'Nov', 11, 'Dec', 12);
 458         ($second, $minute, $hour, $dayOfMonth, $month, $yearOffset, $dayOfWeek, $dayOfYear, $daylightSavings) = localtime();
 459         my %visit_status = ('X', 'y', '-', 'n', '', '');
 460
 461         foreach $index (keys %hometeaching_stats_data)
 462         {
 463                 $hashref = $hometeaching_stats_data{$index};
 464                 #foreach $key (keys %$hashref) {print "$key\n";}
 465
 466                 $family_name = $hometeaching_stats_data{$index}{"Preferred Name"};
 467                 print "   Updating visit data: $family_name\n";
 468
 469                 # get family id from tc_family
 470                 $sth = $dbh->prepare("select * from tc_family where name=\"$family_name\" and valid=1");
 471                 $sth->execute or die "-E- DB error: $DBI::errstr\n";
 472                 my @family_data = ();
 473                 while($sqlhashref = $sth->fetchrow_hashref) { push(@family_data, $sqlhashref); }
 474                 my $family_rows = scalar @family_data;
 475                 if($family_rows > 0) {
 476                         $family_id = $family_data[0]->{'family'};
 477                         $comp_id = $family_data[0]->{'companionship'};
 478                 }
 479                 else { next; }
 480                 #print "family_id = $family_id\n";
 481                 #print "comp_id = $comp_id\n";
 482
 483                 # ignore visits that weren't done by the EQ
 484                 if ($comp_id == 0) { next; }
 485
 486                 # retrieve the month header if not already done
 487                 if ($month_header_retrieved == 0)
 488                 {
 489                         foreach $key (keys %$hashref)
 490                         {
 491                                 if (($key ne "Preferred Name") && ($key ne "Home Teachers"))
 492                                 {
 493                                         $month_header = $key;
 494                                         @data_months = split /\t/, $key;
 495                                 }
 496                         }
 497                         $month_header_retrieved = 1;
 498                 }
 499
 500                 # loop through history data
 501                 @history = split /\t/, $hometeaching_stats_data{$index}{$month_header};
 502                 my $data_year = 1900 + $yearOffset;
 503                 my $data_month = $months{$data_months[-1]};
 504                 #print "$month_header\n";
 505                 #print $hometeaching_stats_data{$index}{$month_header};
 506                 #print "\n";
 507                 foreach $i (reverse(0..$#history)) {
 508                         # went back a calendar year, decrement $data_year
 509                         if ($months{$data_months[$i]} > $data_month)
 510                         {
 511                                 $data_year -= 1;
 512                         }
 513                         $data_month = $months{$data_months[$i]};
 514                         my $visit_date = sprintf("%4d-%02d-01\n", $data_year, $data_month);
 515                         #print "$visit_date\n";
 516                         my $importing_status = $visit_status{$history[$i]};
 517                         #print "importing_status = $importing_status\n";
 518                         #print "select * from tc_visit where family=$family_id and companionship=$comp_id and date='$visit_date'\n";
 519                         $sth = $dbh->prepare("select * from tc_visit where family=$family_id and companionship=$comp_id and date='$visit_date'");
 520                         $sth->execute or die "-E- DB error: $DBI::errstr\n";
 521                         my @visit_data = ();
 522                         while($sqlhashref = $sth->fetchrow_hashref) { push(@visit_data, $sqlhashref); }
 523                         my $visit_rows = scalar @visit_data;
 524                         if($visit_rows > 0) {
 525                                 my $visited = $visit_data[0]->{'visited'};
 526                                 #print "visited = $visited\n";
 527                                 # update visit if data is different in tc_visit
 528                                 if ($visited ne $importing_status)
 529                                 {
 530                                         #print "importing_status = $importing_status\n";
 531                                         $sth = $dbh->prepare("update tc_visit set visited='$importing_status' where family='$family_id' and date='$visit_date' and companionship='$comp_id'");
 532                                         $sth->execute or die "-E- DB error: $DBI::errstr\n";
 533                                 }
 534                         } else {
 535                                 if ($importing_status ne '')
 536                                 {
 537                                         # add visit if it doesn't exist in tc_visit
 538                                         $sth = $dbh->prepare("insert into tc_visit values (NULL, '$family_id', '$comp_id', '', '', '$visit_date', '', '$importing_status', 'hometeaching')");
 539                                         $sth->execute or die "-E- DB error: $DBI::errstr\n";
 540                                 }
 541                         }
 542                 }
 543         }
 544 }
 545
 546 ######################################################################
 547 sub check_for_changed_ids
 548 {
 549     # If the Indiv ID & HofH ID has changed between data sets, we could have problems
 550     my ($oldhashref, $newhashref) = @_;
 551     my $found_problem = 0;
 552
 553     foreach $oldindex (keys %$oldhashref)
 554     {
 555         $indiv_id = $oldhashref->{$oldindex}{'Indiv ID'};
 556         $hofh_id  = $oldhashref->{$oldindex}{'HofH ID'};
 557         $full_name = $oldhashref->{$oldindex}{'Full Name'};
 558         $hh_position = $oldhashref->{$oldindex}{'HH Position'};
 559         if($hh_position =~ /Other/i) { next; }
 560
 561         foreach $newindex (keys %$newhashref)
 562         {
 563             if($newhashref->{$newindex}{'Full Name'} eq $full_name &&
 564                $indiv_id != $newhashref->{$newindex}{'Indiv ID'})
 565             {
 566                 print "-W- Indiv ID for $full_name changed from $indiv_id to $newhashref->{$newindex}{'Indiv ID'}\n";
 567                 $found_problem = 1;
 568             }
 569
 570             if($newhashref->{$newindex}{'Full Name'} eq $full_name &&
 571                $hofh_id != $newhashref->{$newindex}{'HofH ID'})
 572             {
 573                 print "-W- HofH ID for $full_name changed from $hofh_id to $newhashref->{$newindex}{'HofH ID'}\n";
 574                 $found_problem = 1;
 575             }
 576         }
 577     }
 578
 579     return $found_problem;
 580 }
 581
 582 sub update_family_in_tc_indiv_table
 583 {
 584     print "\n-> Updating family info in tc_indiv table\n";
 585
 586     foreach $index (keys %membership_data)
 587     {
 588         $hashref = $membership_data{$index};
 589
 590         # get some information from hash about this individual
 591         $name = $membership_data{$index}{'Preferred Name'};
 592         $hofh_id = $membership_data{$index}{'HofH ID'};
 593
 594         # Find the family id for this individual's HofH_id
 595         $sth = $dbh->prepare("select * from tc_family where hofh_id=$hofh_id and valid=1");
 596         $sth->execute or die "-E- DB error: $DBI::errstr\n";
 597         my @family_data = ();
 598         while($sqlhashref = $sth->fetchrow_hashref) { push(@family_data, $sqlhashref); }
 599         my $family_rows = scalar @family_data;
 600         if($family_rows > 0) {
 601                 $family_id = $family_data[0]->{'family'};
 602
 603                 print "   Updating family data for: $name\n";
 604
 605                 # write the family id to the individual's data in tc_indiv
 606                 $sth = $dbh->prepare("update tc_indiv set family='$family_id' where name=\"$name\"");
 607                 $sth->execute or die "-E- DB error: $DBI::errstr\n";
 608         } else { $family_id = 0; }
 609     }
 610 }
 611
 612 sub update_organization_class_data
 613 {
 614         print "\n-> Updating organization class info in tc_indiv table\n";
 615
 616         foreach $index (keys %organization_class_data)
 617         {
 618                 # get name and organization info for each individual
 619                 $name = $organization_class_data{$index}{'Preferred Name'};
 620                 $org_class = $organization_class_data{$index}{'Organization Class'};
 621
 622                 if ($org_class =~ m/Elder/i) {
 623                         #print "   $name:  Elder\n";
 624                         $sth = $dbh->prepare("update tc_indiv set steward='Elder' where name=\"$name\"");
 625                         $sth->execute or die "-E- DB error: $DBI::errstr\n";
 626                 }
 627                 if ($org_class =~ m/High Priest/i) {
 628                         #print "   $name:  High Priest\n";
 629                         $sth = $dbh->prepare("update tc_indiv set steward='High Priest' where name=\"$name\"");
 630                         $sth->execute or die "-E- DB error: $DBI::errstr\n";
 631                 }
 632         }
 633 }
 634
 635 ######################################################################
 636 # MAIN
 637 ######################################################################
 638
 639 ###################################################
 640 # Open a connection to the database
 641 $dbh=DBI->connect("dbi:mysql:dbname=$dbname:host=$dbhost:port=$dbport",$dbuser,$dbpass,{
 642     AutoCommit=>0,
 643     PrintError=>0}) or print "Connect Failure:".$DBI::errstr."\n" and exit 2;
 644
 645 ###################################################
 646 # Check old directory against new directory to ensure
 647 # that the Indiv ID & HofH ID have not changed between updates
 648 if(defined $opt_o) {
 649     print "-> Comparing old data files to new ones: $opt_o => $opt_n\n";
 650     my %old_membership_data = ();
 651     my %new_membership_data = ();
 652     &csv_to_hash("$opt_o/Membership.csv",\%old_membership_data);
 653     &csv_to_hash("$opt_n/Membership.csv",\%new_membership_data);
 654
 655     $changed_ids=&check_for_changed_ids(\%old_membership_data, \%new_membership_data);
 656
 657     if($changed_ids) {
 658         print "\n";
 659         print "-E- Some Indiv IDs and HofH IDs have changed for Head of Households between \n";
 660         print "    $opt_o and $opt_n data sets.\n";
 661         print "    This script is not currently setup to handle this properly.\n";
 662         print "\n";
 663         print "    Exiting without updating...\n\n";
 664         exit;
 665     }
 666 }
 667
 668 ###################################################
 669 # Process command line options
 670 if(defined $opt_n) { $datadir = $opt_n; }
 671 else { $datadir = shift(@ARGV); }
 672 print "\n-> Processing all ward data files in $datadir\n";
 673
 674 ###################################################
 675 # Parse Ward Data Files
 676 &csv_to_hash("$datadir/Organization\ class\ per\ member.csv", \%organization_class_data);
 677 &csv_to_hash("$datadir/Membership.csv",\%membership_data);
 678 &csv_to_hash("$datadir/HomeTeaching.csv",\%hometeaching_data);
 679 &csv_to_hash("$datadir/Organization.csv",\%organization_data);
 680 &optional_csv_to_hash("$datadir/Home\ Teacher\ per\ Companionship.csv", \%hometeaching_stats_data);
 681 %organization_by_name = ();
 682 %organization_by_id = ();
 683
 684 if($opt_v) {
 685     print "-> Membership Data Dump\n\n";
 686     &print_hash(\%membership_data);
 687     print "-> HomeTeaching Data Dump\n\n";
 688     &print_hash(\%hometeaching_data);
 689     print "-> Organization Data Dump\n\n";
 690     &print_hash(\%organization_data);
 691     print "-> HomeTeaching Stats Data Dump\n\n";
 692     &print_hash(\%hometeaching_stats_data);
 693 }
 694
 695 if($opt_s) { $dbh->disconnect(); exit; }
 696
 697 # Now update the various eq DB tables
 698 &update_tc_calling_table();
 699 &update_tc_indiv_table();
 700 &update_tc_district_table();
 701 &update_tc_companionship_table();
 702 &update_tc_family_table();
 703 &update_tc_visit_table();
 704 &update_family_in_tc_indiv_table();
 705 &update_organization_class_data();
 706
 707 print "\n-> Import Successful! DONE...\n";
 708
 709 ###################################################
 710 # Disconnect from the database
 711 $dbh->disconnect();
 712
 713 ######################################################################
 714
 715