added steward field to tc_indiv so we can list prospective elders
[eq/.git] / 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','','1','','1','',$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','1','',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