import_ward_data

   1 #!/usr/bin/perl
   2
   3 use DBI;
   4 use Getopt::Std;
   5 ###################################################
   6 # GLOBALS
   7 $dbname = "phpgroupware";
   8 $dbhost = "192.168.0.2";
   9 $dbport = 3306;
  10 $dbuser = "phpgroupware";
  11 $dbpass = "phpgroupware";
  12 %hometeaching_data = ();
  13 %membership_data = ();
  14 getopts('vsn:o:');
  15
  16 $monthname2num{'Jan'} = '01';
  17 $monthname2num{'Feb'} = '02';
  18 $monthname2num{'Mar'} = '03';
  19 $monthname2num{'Apr'} = '04';
  20 $monthname2num{'May'} = '05';
  21 $monthname2num{'Jun'} = '06';
  22 $monthname2num{'Jul'} = '07';
  23 $monthname2num{'Aug'} = '08';
  24 $monthname2num{'Sep'} = '09';
  25 $monthname2num{'Oct'} = '10';
  26 $monthname2num{'Nov'} = '11';
  27 $monthname2num{'Dec'} = '12';
  28
  29 ######################################################################
  30 # SUBROUTINES
  31 ######################################################################
  32 sub csv_to_hash
  33 {
  34     my ($filename, $hashref) = @_;
  35
  36     open(FILE,$filename) || die "-E- Could not open $filename for reading\n";
  37
  38     my $found_header = 0; my $index = 0;
  39     while(<FILE>)
  40     {
  41         $line = $_;
  42         @data = split /\",/, $line;
  43         if(!$found_header) { @header = @data; $found_header = 1; }
  44         else {
  45             foreach $i (0..$#data-1) {
  46                 $data[$i] =~ s/\"//g;
  47                 $header[$i] =~ s/\"//g;
  48                 $hashref->{$index}{$header[$i]} = $data[$i];
  49                 #print "$index: $i: $header[$i]: $data[$i]\n";
  50             }
  51             $index++;
  52         }
  53     }
  54
  55     close(FILE);
  56 }
  57
  58 ######################################################################
  59 sub print_hash
  60 {
  61     my ($hashref) = @_;
  62
  63     foreach $key (sort {$a <=> $b} keys %$hashref) {
  64         print "Index: $key\n";
  65         foreach $field (keys %{$hashref->{$key}}) {
  66             print "$field: $hashref->{$key}{$field}\n";
  67         }
  68         print "\n";
  69     }
  70 }
  71
  72 ######################################################################
  73
  74 # EQ_AARONIC
  75 #+-------+--------------------+------+-----+---------+-------+
  76 #| Field | Type               | Null | Key | Default | Extra |
  77 #+-------+--------------------+------+-----+---------+-------+
  78 #| aaronic | int(16) unsigned |      | PRI | 0       |   A   |
  79 #| name    | varchar(60)      | YES  |     | NULL    |       |
  80 #| valid   | tinyint(1)       | YES  |     | NULL    |       |
  81 #+-------+--------------------+------+-----+---------+-------+
  82 sub update_eq_aaronic_table
  83 {
  84     print "-> Updating eq_aaronic table\n";
  85     foreach $index (keys %membership_data)
  86     {
  87         $hashref = $membership_data{$index};
  88         foreach $key (keys %$hashref) {
  89             if($key =~ /Priesthood/i &&
  90                ($membership_data{$index}{$key} =~ /^Teacher\s*$/i ||
  91                 $membership_data{$index}{$key} =~ /^Priest\s*$/i)) {
  92                 $aaronic_name = $membership_data{$index}{'Preferred Name'};
  93                 $sth = $dbh->prepare("select * from eq_aaronic where name='$aaronic_name'");
  94                 $sth->execute or die "-E- DB error: $DBI::errstr\n";
  95                 my @data = ();
  96                 while($sqlhashref = $sth->fetchrow_hashref) { push(@data, $sqlhashref); }
  97                 my $rows = scalar @data;
  98                 if($rows == 0) {
  99                     # No existing records found for this aaronic, make a new entry
 100                     print "   Adding new Aaronic: $aaronic_name\n";
 101                     $sth = $dbh->prepare("insert into eq_aaronic values (NULL,'$aaronic_name',1)");
 102                     $sth->execute or die "-E- DB error: $DBI::errstr\n";
 103                 } elsif($rows == 1) {
 104                     # An existing record was found for this aaronic, update it if it is valid
 105                     if($data[0]->{valid} == 1) {
 106                         print "   Updating existing aaronic: $aaronic_name\n";
 107                     }
 108                 } else {
 109                     # More than one record was found. Error! This shouldn't happen.
 110                     print "   -E- More than one record found ($rows) for aaronic name: $aaronic_name\n";
 111                 }
 112             }
 113         }
 114     }
 115     $sth->finish();
 116 }
 117
 118 # EQ_ELDER
 119 #+------------+------------------+------+-----+---------+-------+
 120 #| Field      | Type             | Null | Key | Default | Extra |
 121 #+------------+------------------+------+-----+---------+-------+
 122 #| elder      | int(16) unsigned |      | PRI | 0       |   A   |
 123 #| name       | varchar(60)      | YES  |     | NULL    |       |
 124 #| valid      | tinyint(1)       | YES  |     | NULL    |       |
 125 #+------------+------------------+------+-----+---------+-------+
 126 sub update_eq_elder_table
 127 {
 128     print "-> Updating eq_elder table\n";
 129
 130     # Set all records to be invalid. Only mark them as valid if they appear on the new list.
 131     $sth = $dbh->prepare("update eq_elder set valid=0");
 132     $sth->execute or die "-E- DB error: $DBI::errstr\n";
 133
 134     foreach $index (keys %membership_data)
 135     {
 136         $hashref = $membership_data{$index};
 137         foreach $key (keys %$hashref) {
 138             if($key =~ /Priesthood/i && $membership_data{$index}{$key} =~ /Elder/i) {
 139                 $elder_name = $membership_data{$index}{'Preferred Name'};
 140                 $sth = $dbh->prepare("select * from eq_elder where name='$elder_name'");
 141                 $sth->execute or die "-E- DB error: $DBI::errstr\n";
 142                 my @data = ();
 143                 while($sqlhashref = $sth->fetchrow_hashref) { push(@data, $sqlhashref); }
 144                 my $rows = scalar @data;
 145                 if($rows == 0) {
 146                     # No existing records found for this elder, make a new entry
 147                     print "   Adding new Elder: $elder_name\n";
 148                     $sth = $dbh->prepare("insert into eq_elder values (NULL,'$elder_name',1)");
 149                     $sth->execute or die "-E- DB error: $DBI::errstr\n";
 150                 } elsif($rows == 1) {
 151                     # An existing record was found for this elder, update it
 152                     print "   Updating existing Elder: $elder_name\n";
 153                     $sth = $dbh->prepare("update eq_elder set valid=1 where name='$elder_name'");
 154                     $sth->execute or die "-E- DB error: $DBI::errstr\n";
 155                 } else {
 156                     # More than one record was found. Error! This shouldn't happen.
 157                     print "   -E- More than one record found ($rows) for Elder: $elder_name\n";
 158                 }
 159             }
 160         }
 161     }
 162     $sth->finish();
 163 }
 164
 165 # EQ_DISTRICT
 166 #+------------+------------------+------+-----+---------+-------+
 167 #| Field      | Type             | Null | Key | Default | Extra |
 168 #+------------+------------------+------+-----+---------+-------+
 169 #| district   | int(16) unsigned |      | PRI | 0       |       |
 170 #| name       | varchar(30)      | YES  |     | NULL    |       |
 171 #| supervisor | int(16) unsigned | YES  |     | NULL    |       |
 172 #| valid      | tinyint(1)       | YES  |     | NULL    |       |
 173 #+------------+------------------+------+-----+---------+-------+
 174 sub update_eq_district_table
 175 {
 176     # Districts should be created by hand. This subroutine only
 177     # updates the supervisor's ID in each district.
 178     print "-> Updating eq_district table\n";
 179     $sth = $dbh->prepare("select * from eq_district");
 180     $sth->execute or die "-E- DB error: $DBI::errstr\n";
 181     while($sqlhashref = $sth->fetchrow_hashref) {
 182         $supervisor_name = $sqlhashref->{name};
 183         $district = $sqlhashref->{district};
 184         $sth2 = $dbh->prepare("select * from eq_elder where name='$supervisor_name'");
 185         $sth2->execute or die "-E- DB error: $DBI::errstr\n";
 186         $sqlhashref2 = $sth2->fetchrow_hashref;
 187         $supervisor_id = $sqlhashref2->{elder};
 188         $sth2->finish();
 189         $sth2 = $dbh->prepare("update eq_district set supervisor='$supervisor_id' where district='$district'");
 190         $sth2->execute or die "-E- DB error: $DBI::errstr\n";
 191         $sth2->finish();
 192     }
 193     $sth->finish();
 194 }
 195
 196 # EQ_COMPANIONSHIP
 197 #+---------------+------------------+------+-----+---------+-------+
 198 #| Field         | Type             | Null | Key | Default | Extra |
 199 #+---------------+------------------+------+-----+---------+-------+
 200 #| companionship | int(16) unsigned |      |     | 0       |       |
 201 #| elder         | int(16) unsigned | YES  |     | NULL    |       |
 202 #| aaronic       | int(16) unsigned | YES  |     | NULL    |       |
 203 #| district      | int(16) unsigned | YES  |     | NULL    |       |
 204 #| valid         | tinyint(1)       | YES  |     | NULL    |       |
 205 #+---------------+------------------+------+-----+---------+-------+
 206 sub update_eq_companionship_table
 207 {
 208     print "-> Updating eq_companionship table\n";
 209
 210     # First, mark all existing companionships as invalid in case they have been dissolved
 211     $sth = $dbh->prepare("update eq_companionship set valid=0");
 212     $sth->execute or die "-E- DB error: $DBI::errstr\n";
 213     # Second, mark all the aaronic invalid. We'll only mark the ones as valid that are assigned to hometeach
 214     $sth = $dbh->prepare("update eq_aaronic set valid=0");
 215     $sth->execute or die "-E- DB error: $DBI::errstr\n";
 216
 217     foreach $index (keys %hometeaching_data)
 218     {
 219         $hashref = $hometeaching_data{$index};
 220         foreach $key (keys %$hashref) {
 221             if($key =~ /Quorum/i && $hometeaching_data{$index}{$key} =~ /Elders/i) {
 222                 foreach $field ("Home Teacher 1","Home Teacher 2") {
 223                     $elder_name = $hometeaching_data{$index}{$field};
 224                     if($elder_name eq "") { next; }
 225                     $sth2 = $dbh->prepare("select * from eq_elder where name='$elder_name'");
 226                     $sth2->execute or die "-E- DB error: $DBI::errstr\n";
 227                     $sqlhashref2 = $sth2->fetchrow_hashref;
 228                     $elder = $sqlhashref2->{elder};
 229                     $aaronic = "NULL";
 230                     if($elder eq "") {
 231                         $sth2 = $dbh->prepare("select * from eq_aaronic where name='$elder_name'");
 232                         $sth2->execute or die "-E- DB error: $DBI::errstr\n";
 233                         $sqlhashref2 = $sth2->fetchrow_hashref;
 234                         $aaronic = $sqlhashref2->{aaronic};
 235                         $elder = "NULL";
 236                         if($aaronic eq "") { print "-W- Unable to find $elder_name in eq_elder or eq_aaronic tables\n"; }
 237                     }
 238                     $id = $hometeaching_data{$index}{'Comp ID'};
 239                     $district = $hometeaching_data{$index}{'HT District'};
 240                     $sth = $dbh->prepare("select * from eq_companionship where elder='$elder' and aaronic='$aaronic' and companionship='$id'");
 241                     $sth->execute or die "-E- DB error: $DBI::errstr\n";
 242                     my @data = ();
 243                     while($sqlhashref = $sth->fetchrow_hashref) { push(@data, $sqlhashref); }
 244                     my $rows = scalar @data;
 245                     if($rows == 0) {
 246                         # No existing records found for this companionship, make a new entry
 247                         print "   Adding Elder to companionship: $elder_name -> $id\n";
 248                         $sth = $dbh->prepare("insert into eq_companionship values ($id,'$elder','$aaronic','$district',1)");
 249                         $sth->execute or die "-E- DB error: $DBI::errstr\n";
 250                     } else {
 251                         # An existing companionship was found for this companionship, update it
 252                         $sth2 = $dbh->prepare("select * from eq_companionship where district='$district' and companionship='$id'");
 253                         $sth2->execute or die "-E- DB error: $DBI::errstr\n";
 254                         if($elder ne "NULL") {
 255                             print "   Updating Companionship with Elder: $elder_name ($elder) -> $id\n";
 256                             $sth = $dbh->prepare("update eq_companionship set district='$district' where elder='$elder' and companionship='$id'");
 257                             $sth->execute or die "-E- DB error 'district': $DBI::errstr\n";
 258                             $sth = $dbh->prepare("update eq_companionship set elder='$elder' where elder='$elder' and companionship='$id'");
 259                             $sth->execute or die "-E- DB error 'elder': $DBI::errstr\n";
 260                             $sth = $dbh->prepare("update eq_companionship set valid=1 where elder='$elder' and companionship='$id'");
 261                             $sth->execute or die "-E- DB error 'valid': $DBI::errstr\n";
 262                         } else {
 263                             print "   Updating Companionship with Aaronic: $elder_name ($aaronic) -> $id\n";
 264                             $sth = $dbh->prepare("update eq_companionship set district='$district' where aaronic='$aaronic' and companionship='$id'");
 265                             $sth->execute or die "-E- DB error: $DBI::errstr\n";
 266                             $sth = $dbh->prepare("update eq_companionship set aaronic='$aaronic' where aaronic='$aaronic' and companionship='$id'");
 267                             $sth->execute or die "-E- DB error: $DBI::errstr\n";
 268                             $sth = $dbh->prepare("update eq_companionship set valid=1 where aaronic='$aaronic' and companionship='$id'");
 269                             $sth->execute or die "-E- DB error: $DBI::errstr\n";
 270                             $sth = $dbh->prepare("update eq_aaronic set valid=1 where aaronic='$aaronic'");
 271                             $sth->execute or die "-E- DB error: $DBI::errstr\n";
 272                         }
 273                     }
 274                     $sth->finish();
 275                     $sth2->finish();
 276                 }
 277             }
 278         }
 279     }
 280 }
 281
 282 # EQ_FAMILY
 283 #+---------------+------------------+------+-----+---------+-------+
 284 #| Field         | Type             | Null | Key | Default | Extra |
 285 #+---------------+------------------+------+-----+---------+-------+
 286 #| family        | int(16) unsigned |      | PRI | 0       |   A   |
 287 #| hofh_id       | int(16) unsigned | YES  |     | NULL    |       |
 288 #| name          | varchar(30)      | YES  |     | NULL    |       |
 289 #| companionship | int(16) unsigned | YES  |     | NULL    |       |
 290 #| valid         | tinyint(1)       | YES  |     | NULL    |       |
 291 #+---------------+------------------+------+-----+---------+-------+
 292 sub update_eq_family_table
 293 {
 294     print "-> Updating eq_family table\n";
 295
 296     # Set all records to be invalid. Only mark them as valid if they appear on the new list.
 297     $sth = $dbh->prepare("update eq_family set valid=0");
 298     $sth->execute or die "-E- DB error: $DBI::errstr\n";
 299
 300     foreach $index (keys %membership_data)
 301     {
 302         $hashref = $membership_data{$index};
 303         foreach $key (keys %$hashref) {
 304             if($key =~ /HH Position/i && $membership_data{$index}{$key} =~ /Head of Household/i) {
 305                 $family_name = $membership_data{$index}{'Preferred Name'};
 306                 $family_name =~ s/\'/\\'/g; #'
 307                 $id = $membership_data{$index}{'HofH ID'};
 308
 309                 # Find out how many families match this family's name
 310                 $sth = $dbh->prepare("select * from eq_family where name='$family_name'");
 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
 316                 if($rows == 0) {
 317                     # No existing records found for this family, make a new entry
 318                     print "   Adding new Family: $family_name\n";
 319                     $sth = $dbh->prepare("insert into eq_family values (NULL,$id,'$family_name','0',1)");
 320                     $sth->execute or die "-E- DB error: $DBI::errstr\n";
 321                 } elsif($rows == 1) {
 322                     # An existing record was found for this family, update it
 323                     print "   Updating existing family: $family_name\n";
 324                     $sth = $dbh->prepare("update eq_family set hofh_id=$id where name='$family_name'");
 325                     $sth->execute or die "-E- DB error: $DBI::errstr\n";
 326                     $sth = $dbh->prepare("update eq_family set valid=1 where name='$family_name'");
 327                     $sth->execute or die "-E- DB error: $DBI::errstr\n";
 328                 } else {
 329                     # More than one record was found. Error! This shouldn't happen.
 330                     print "   -E- More than one record found ($rows) for family name: $family_name\n";
 331                 }
 332                 # Now update the hometeaching field for this family
 333                 foreach $index (keys %hometeaching_data)
 334                 {
 335                     $hashref = $hometeaching_data{$index};
 336                     foreach $key (keys %$hashref) {
 337                         if($hometeaching_data{$index}{'Household'} =~ /(\S+)\s+(\S+),\s+(\S+)\s+(.*)/) {
 338                             print "I: Adjusting hometeaching match from: $hometeaching_data{$index}{'Household'} to $1, $3 $4\n";
 339                             $hometeaching_data{$index}{'Household'} = "$1, $3 $4";
 340                         }
 341                         if($key =~ /Quorum/i &&
 342                            $hometeaching_data{$index}{$key} =~ /Elders/i &&
 343                            $hometeaching_data{$index}{'Household'} =~ /$family_name/ &&
 344                            $data[0]->{companionship} != $hometeaching_data{$index}{'Comp ID'}
 345                            )
 346                         {
 347                             print "   Updating hometeaching assignment for $family_name family\n";
 348                             $companionship = $hometeaching_data{$index}{'Comp ID'};
 349                             $sth = $dbh->prepare("update eq_family set companionship='$companionship' where name='$family_name'");
 350                             $sth->execute or die "-E- DB error: $DBI::errstr\n";
 351                         }
 352                     }
 353                 }
 354                 $sth->finish();
 355             }
 356         }
 357     }
 358 }
 359
 360 # EQ_PARENT
 361 #+----------+------------------+------+-----+---------+-------+
 362 #| Field    | Type             | Null | Key | Default | Extra |
 363 #+----------+------------------+------+-----+---------+-------+
 364 #| parent   | int(16) unsigned |      | PRI | 0       |   A   |
 365 #| family   | int(16) unsigned | YES  |     | NULL    |       |
 366 #| name     | varchar(30)      | YES  |     | NULL    |       |
 367 #| birthday | date             | YES  |     | NULL    |       |
 368 #| valid    | tinyint(1)       | YES  |     | NULL    |       |
 369 #+----------+------------------+------+-----+---------+-------+
 370 sub update_eq_parent_table
 371 {
 372     print "-> Updating eq_parent table\n";
 373
 374     # Set all records to be invalid. Only mark them as valid if they appear on the new list.
 375     $sth = $dbh->prepare("update eq_parent set valid=0");
 376     $sth->execute or die "-E- DB error: $DBI::errstr\n";
 377
 378     foreach $index (keys %membership_data)
 379     {
 380         $hashref = $membership_data{$index};
 381         foreach $key (keys %$hashref) {
 382             if($key =~ /HH Position/i &&
 383                $membership_data{$index}{$key} =~ /Head of Household/i ||
 384                $membership_data{$index}{$key} =~ /Spouse/i
 385                ) {
 386                 # Get some information from the hash about this parent
 387                 $parent_name = $membership_data{$index}{'Preferred Name'};
 388                 $parent_name =~ s/\'/\\'/g; #'
 389                 $birthday = $membership_data{$index}{'Birth'};
 390                 $birthday =~ /(\d+) (\S+) (\d+)/; $day=$1; $month=$monthname2num{$2}; $year=$3;
 391                 $hofh_id = $membership_data{$index}{'HofH ID'};
 392
 393                 # Find the family id for this parent's HofH_ID.
 394                 $sth = $dbh->prepare("select * from eq_family where hofh_id='$hofh_id'");
 395                 $sth->execute or die "-E- DB error: $DBI::errstr\n";
 396                 $sqlhashref = $sth->fetchrow_hashref();
 397                 $family_id = $sqlhashref->{'family'};
 398
 399                 # Find out how many parents match this parent's name
 400                 $sth = $dbh->prepare("select * from eq_parent where name='$parent_name'");
 401                 $sth->execute or die "-E- DB error: $DBI::errstr\n";
 402                 my @data = ();
 403                 while($sqlhashref = $sth->fetchrow_hashref) { push(@data, $sqlhashref); }
 404                 my $rows = scalar @data;
 405
 406                 if($rows == 0) {
 407                     # No existing records found for this parent, make a new entry
 408                     print "   Adding new Parent: $parent_name\n";
 409                     $sth = $dbh->prepare("insert into eq_parent values (NULL,$family_id,'$parent_name','$year-$month-$day',1)");
 410                     $sth->execute or die "-E- DB error: $DBI::errstr\n";
 411                 } elsif($rows == 1) {
 412                     # An existing record was found for this parent, update it
 413                     print "   Updating existing parent: $parent_name\n";
 414                     $sth = $dbh->prepare("update eq_parent set family='$family_id' where name='$parent_name'");
 415                     $sth->execute or die "-E- DB error: $DBI::errstr\n";
 416                     $sth = $dbh->prepare("update eq_parent set birthday='$year-$month-$day' where name='$parent_name'");
 417                     $sth->execute or die "-E- DB error: $DBI::errstr\n";
 418                     $sth = $dbh->prepare("update eq_parent set valid=1 where name='$parent_name'");
 419                     $sth->execute or die "-E- DB error: $DBI::errstr\n";
 420             } else {
 421                     # More than one record was found. Error! This shouldn't happen.
 422                     print "   -E- More than one record found with same parent name: $parent_name\n";
 423                 }
 424                 $sth->finish();
 425             }
 426         }
 427     }
 428 }
 429
 430 # EQ_CHILD
 431 #+----------+------------------+------+-----+---------+-------+
 432 #| Field    | Type             | Null | Key | Default | Extra |
 433 #+----------+------------------+------+-----+---------+-------+
 434 #| child    | int(16) unsigned |      | PRI | 0       |   A   |
 435 #| family   | int(16) unsigned | YES  |     | NULL    |       |
 436 #| name     | varchar(30)      | YES  |     | NULL    |       |
 437 #| birthday | date             | YES  |     | NULL    |       |
 438 #| valid    | tinyint(1)       | YES  |     | NULL    |       |
 439 #+----------+------------------+------+-----+---------+-------+
 440 sub update_eq_child_table
 441 {
 442     print "-> Updating eq_child table\n";
 443
 444     # Set all records to be invalid. Only mark them as valid if they appear on the new list.
 445     $sth = $dbh->prepare("update eq_child set valid=0");
 446     $sth->execute or die "-E- DB error: $DBI::errstr\n";
 447
 448     foreach $index (keys %membership_data)
 449     {
 450         $hashref = $membership_data{$index};
 451         foreach $key (keys %$hashref) {
 452             if($key =~ /HH Position/i && $membership_data{$index}{$key} =~ /Other/i ) {
 453                 $child_name = $membership_data{$index}{'Full Name'};
 454                 $child_name =~ s/\'/\\'/g; #'
 455                 $birthday = $membership_data{$index}{'Birth'};
 456                 $birthday =~ /(\d+) (\S+) (\d+)/; $day=$1; $month=$monthname2num{$2}; $year=$3;
 457                 $id = $membership_data{$index}{'Indiv ID'};
 458                 $hofh_id = $membership_data{$index}{'HofH ID'};
 459
 460                 # Find the family id for this child's HofH_ID.
 461                 $sth = $dbh->prepare("select * from eq_family where hofh_id='$hofh_id'");
 462                 $sth->execute or die "-E- DB error: $DBI::errstr\n";
 463                 $sqlhashref = $sth->fetchrow_hashref();
 464                 $family_id = $sqlhashref->{'family'};
 465
 466                 # Find out how many children have the same name for the same family
 467                 $sth = $dbh->prepare("select * from eq_child where name='$child_name'");
 468                 $sth->execute or die "-E- DB error: $DBI::errstr\n";
 469                 my @data = ();
 470                 while($sqlhashref = $sth->fetchrow_hashref) { push(@data, $sqlhashref); }
 471                 my $rows = scalar @data;
 472
 473                 if($rows == 0) {
 474                     # No existing records found for this child, make a new entry
 475                     print "   Adding new Child: $child_name\n";
 476                     $sth = $dbh->prepare("insert into eq_child values (NULL,$family_id,'$child_name','$year-$month-$day',1)");
 477                     $sth->execute or die "-E- DB error: $DBI::errstr\n";
 478                 } elsif($rows == 1) {
 479                     # An existing record was found for this child, update it
 480                     print "   Updating existing child: $child_name\n";
 481                     $sth = $dbh->prepare("update eq_child set family='$family_id' where name='$child_name'");
 482                     $sth->execute or die "-E- DB error: $DBI::errstr\n";
 483                     $sth = $dbh->prepare("update eq_child set birthday='$year-$month-$day' where name='$child_name'");
 484                     $sth->execute or die "-E- DB error: $DBI::errstr\n";
 485                     $sth = $dbh->prepare("update eq_child set valid=1 where name='$child_name'");
 486                     $sth->execute or die "-E- DB error: $DBI::errstr\n";
 487                 } else {
 488                     # More than one record was found. Error! This shouldn't happen.
 489                     print "   -E- More than one record found ($rows) with same child name: $child_name\n";
 490                 }
 491                 $sth->finish();
 492             }
 493         }
 494     }
 495 }
 496
 497 ######################################################################
 498 sub check_for_changed_ids
 499 {
 500     # If the Indiv ID & HofH ID has changed between data sets, we could have problems
 501     my ($oldhashref, $newhashref) = @_;
 502     my $found_problem = 0;
 503
 504     foreach $oldindex (keys %$oldhashref)
 505     {
 506         $indiv_id = $oldhashref->{$oldindex}{'Indiv ID'};
 507         $hofh_id  = $oldhashref->{$oldindex}{'HofH ID'};
 508         $full_name = $oldhashref->{$oldindex}{'Full Name'};
 509         $hh_position = $oldhashref->{$oldindex}{'HH Position'};
 510         if($hh_position =~ /Other/i) { next; }
 511
 512         foreach $newindex (keys %$newhashref)
 513         {
 514             if($newhashref->{$newindex}{'Full Name'} eq $full_name &&
 515                $indiv_id != $newhashref->{$newindex}{'Indiv ID'})
 516             {
 517                 print "-W- Indiv ID for $full_name changed from $indiv_id to $newhashref->{$newindex}{'Indiv ID'}\n";
 518                 $found_problem = 1;
 519             }
 520
 521             if($newhashref->{$newindex}{'Full Name'} eq $full_name &&
 522                $hofh_id != $newhashref->{$newindex}{'HofH ID'})
 523             {
 524                 print "-W- HofH ID for $full_name changed from $hofh_id to $newhashref->{$newindex}{'HofH ID'}\n";
 525                 $found_problem = 1;
 526             }
 527         }
 528     }
 529
 530     return $found_problem;
 531 }
 532
 533 ######################################################################
 534 # MAIN
 535 ######################################################################
 536
 537 ###################################################
 538 # Open a connection to the database
 539 $dbh=DBI->connect("dbi:mysql:dbname=$dbname;host=$dbhost port=$dbport",$dbuser,$dbpass,{
 540     AutoCommit=>0,
 541     PrintError=>0}) or print "Connect Failure:".$DBI::errstr."\n" and exit 2;
 542
 543 ###################################################
 544 # Check old directory against new directory to ensure
 545 # that the Indiv ID & HofH ID have not changed between updates
 546 if(defined $opt_o) {
 547     print "-> Comparing old data files to new ones: $opt_o => $opt_n\n";
 548     my %old_membership_data = ();
 549     my %new_membership_data = ();
 550     &csv_to_hash("$opt_o/Membership.csv",\%old_membership_data);
 551     &csv_to_hash("$opt_n/Membership.csv",\%new_membership_data);
 552
 553     $changed_ids=&check_for_changed_ids(\%old_membership_data, \%new_membership_data);
 554
 555     if($changed_ids) {
 556         print "\n";
 557         print "-E- Some Indiv IDs and HofH IDs have changed for Head of Households between \n";
 558         print "    $opt_o and $opt_n data sets.\n";
 559         print "    This script is not currently setup to handle this properly.\n";
 560         print "\n";
 561         print "    Exiting without updating...\n\n";
 562         exit;
 563     }
 564 }
 565
 566 ###################################################
 567 # Process command line options
 568 if(defined $opt_n) { $datadir = $opt_n; }
 569 else { $datadir = shift(@ARGV); }
 570 print "-> Processing all ward data files in $datadir\n";
 571
 572 ###################################################
 573 # Parse Ward Data Files
 574 &csv_to_hash("$datadir/Membership.csv",\%membership_data);
 575 &csv_to_hash("$datadir/HomeTeaching.csv",\%hometeaching_data);
 576
 577 if($opt_v) {
 578     print "-> Membership Data Dump\n\n";
 579     &print_hash(\%membership_data);
 580     print "-> HomeTeaching Data Dump\n\n";
 581     &print_hash(\%hometeaching_data);
 582 }
 583
 584 if($opt_s) { $dbh->disconnect(); exit; }
 585
 586 # Now update the various eq DB tables
 587 &update_eq_elder_table();
 588 &update_eq_aaronic_table();
 589 &update_eq_district_table();
 590 &update_eq_companionship_table();
 591 &update_eq_family_table();
 592 &update_eq_parent_table();
 593 &update_eq_child_table();
 594
 595 ###################################################
 596 # Disconnect from the database
 597 $dbh->disconnect();
 598
 599 ######################################################################
 600
 601
 602
 603
 604
 605
 606
 607
 608
 609
 610
 611