X-Git-Url: http://git.pippins.net/embedvideo/.git/?a=blobdiff_plain;f=bin%2Fimport_ward_data;h=6fbb96b3ba1e7400a251c92c2b6f546ca583a60d;hb=355decc5cec577acbcbafe58d335b2d1523c9e71;hp=501f96324cae8567bf0a01e8e4147862d1ef042f;hpb=17b6c5b5bedf4d243eb5aae967a14aece783f17b;p=eq%2F.git

diff --git a/bin/import_ward_data b/bin/import_ward_data
index 501f963..6fbb96b 100755
--- a/bin/import_ward_data
+++ b/bin/import_ward_data
@@ -138,13 +138,22 @@ sub update_tc_individual_table
 		$phone = $membership_data{$index}{'Household Phone'};
 		$priesthood = $membership_data{$index}{'Priesthood'};
 		$hhposition = $membership_data{$index}{'HH Position'};
-		$organization = $organization_by_id{$id};
+		$steward = ""; # This will be set later
+		# Default to the "Elders" quorum if the Organization data per member isn't available
+		# Only add "Elders" to the quorum since we don't have any other data availalbe to us to make a call
+	 	# TODO: make steward flexible with a setting in the config file	
+		if((! -e "$datadir/Organization\ class\ per\ member.csv") && ($priesthood =~ /Elder/i)) { 
+			$steward = "Elder";
+			$organization = "Elders"; 
+		}
+		# Preferred method is to pull organization data from the csv file
+		else { $organization = $organization_by_id{$id}; }
 		$attending = 0;
 		if(($organization =~ /Elders/) ||
 		   ($organization =~ /Young Men/) ||
 		   ($organization =~ /Sunday School/) ||
 		   ($organization =~ /Primary/)
-		   ) { $attending = 1; }
+		  ) { $attending = 1; }
 		if($phone =~ /(\d\d\d-\d\d\d\d)/) { $phone = "$areacode-$1"; }
 		if($phone =~ /^\(\d\d\d\) (\d\d\d-\d\d\d\d)/) { $phone = "$1-$2"; }
 		$email = $membership_data{$index}{'indiv E-mail'};
@@ -157,7 +166,7 @@ sub update_tc_individual_table
 		if($rows == 0) {
 			# No existing records found for this individual, make a new entry
 			print "   Adding new individual: $individual_name\n";
-			$sth = $dbh->prepare("insert into tc_individual values (NULL,'$id',\"$individual_name\",'$address','$phone','$email','$hhposition','$priesthood',NULL,$attending,1)");
+			$sth = $dbh->prepare("insert into tc_individual values (NULL,'$id',\"$individual_name\",'$address','$phone','$email','$hhposition','$priesthood','$steward',NULL,$attending,1)");
 			$sth->execute or die "-E- DB error: $DBI::errstr\n";
 		} elsif($rows == 1) {
 			# An existing record was found for this individual, update it
@@ -221,6 +230,7 @@ sub update_tc_calling_table()
 		$name =~ s/\'/\\'/g; #'
 		$organization = $organization_data{$index}{'Organization'};
 		$organization_by_name{$name} = $organization;
+		$indiv_id = $organization_data{$index}{'Indiv ID'};
 		$organization_by_id{$indiv_id} = $organization;
 		$position = $organization_data{$index}{'Position'};
 		$sustained = $organization_data{$index}{'Sustained'};
@@ -232,37 +242,6 @@ sub update_tc_calling_table()
 	}
 }
 
-# TC_DISTRICT
-#+------------+------------------+------+-----+---------+-------+
-#| Field      | Type             | Null | Key | Default | Extra |
-#+------------+------------------+------+-----+---------+-------+
-#| district   | int(16) unsigned |      | PRI | 0       |       |
-#| name       | varchar(30)      | YES  |     | NULL    |       |
-#| supervisor | int(16) unsigned | YES  |     | NULL    |       |
-#| valid      | tinyint(1)       | YES  |     | NULL    |       |
-#+------------+------------------+------+-----+---------+-------+
-sub update_tc_district_table
-{
-    # Districts should be created by hand. This subroutine only
-    # updates the supervisor's ID in each district.
-    print "\n-> Updating tc_district table\n";
-    $sth = $dbh->prepare("select * from tc_district");
-    $sth->execute or die "-E- DB error: $DBI::errstr\n";
-    while($sqlhashref = $sth->fetchrow_hashref) {
-	$supervisor_name = $sqlhashref->{name};
-	$district = $sqlhashref->{district};
-	$sth2 = $dbh->prepare("select * from tc_individual where name='$supervisor_name'");
-	$sth2->execute or die "-E- DB error: $DBI::errstr\n";
-	$sqlhashref2 = $sth2->fetchrow_hashref;
-	$supervisor_id = $sqlhashref2->{individual};
-	$sth2->finish();
-	$sth2 = $dbh->prepare("update tc_district set supervisor='$supervisor_id' where district='$district'");
-	$sth2->execute or die "-E- DB error: $DBI::errstr\n";
-	$sth2->finish();
-    }
-    $sth->finish();
-}
-
 # TC_COMPANIONSHIP
 #+----------------------+------------------+------+-----+---------+-------+
 #| Field                | Type             | Null | Key | Default | Extra |
@@ -346,9 +325,12 @@ sub update_tc_family_table
 	# find head of households in tc_individual
 	$sth = $dbh->prepare("SELECT * FROM tc_individual WHERE hh_position='Head of Household' and valid=1");
 	$sth->execute or die "-E- DB error: $DBI::errstr\n";
-	while ($sqlhashref = $sth->fetchrow_hashref) {
-		$individual = $sqlhashref{individual};
-		$name = $sqlhashref{name};
+	my @individual_data = ();
+	while ($sqlhashref = $sth->fetchrow_hashref) { push(@individual_data, $sqlhashref); }
+	my $individual_count = scalar @individual_data;
+	for($i=0;$i<$individual_count;$i++) {
+		$individual = $individual_data[$i]{'individual'};
+		$name = $individual_data[$i]{'name'};
 		
 		$sth2 = $dbh->prepare("SELECT * FROM tc_family WHERE individual='$individual'");
 		$sth2->execute or die "-E- DB error: $DBI::errstr\n";
@@ -360,12 +342,12 @@ sub update_tc_family_table
 		if($rows == 0) {
 			# No existing records found for this family, make a new entry
 			print "   Adding new Family: $name\n";
-			$sth2 = $dbh->prepare("insert into tc_family values (NULL,'$individual','0',NULL,1)");
+			$sth2 = $dbh->prepare("INSERT INTO tc_family VALUES (NULL,'$individual','0',NULL,1)");
 			$sth2->execute or die "-E- DB error: $DBI::errstr\n";
 		} elsif($rows == 1) {
 			# An existing record was found for this family, update it
 			print "   Updating existing family: $name\n";
-			$sth2 = $dbh->prepare("update tc_family set valid='1' where individual=$individual'");
+			$sth2 = $dbh->prepare("UPDATE tc_family SET valid='1' WHERE individual='$individual'");
 			$sth2->execute or die "-E- DB error: $DBI::errstr\n";
 		} else {
 			# More than one record was found. Error! This shouldn't happen.
@@ -432,7 +414,7 @@ sub update_tc_visit_table
 		print "   Updating visit data: $family_name\n";
 
 		# get family id from tc_family
-		$sth = $dbh->prepare("select * from tc_family where name=\"$family_name\" and valid=1");
+		$sth = $dbh->prepare("SELECT * FROM tc_family AS tf JOIN tc_individual AS ti WHERE tf.individual=ti.individual AND ti.name=\"$family_name\" AND tf.valid=1");
 		$sth->execute or die "-E- DB error: $DBI::errstr\n";
 		my @family_data = ();
 		while($sqlhashref = $sth->fetchrow_hashref) { push(@family_data, $sqlhashref); }
@@ -587,6 +569,7 @@ sub update_tc_scheduling_priority_table
 			$sth2->execute or die "-E- DB error: $DBI::errstr\n";
 		}
 	}
+	&remove_obsolete_scheduling_priority("tc_individual");
 	
 	# families
 	# TODO: make steward flexible with a setting in the config file
@@ -605,6 +588,7 @@ sub update_tc_scheduling_priority_table
 			$sth2->execute or die "-E- DB error: $DBI::errstr\n";
 		}
 	}
+	&remove_obsolete_scheduling_priority("tc_family");
 	
 	# companionships
 	# TODO: make steward flexible with a setting in the config file
@@ -623,7 +607,35 @@ sub update_tc_scheduling_priority_table
 			$sth2->execute or die "-E- DB error: $DBI::errstr\n";
 		}
 	}
+	&remove_obsolete_scheduling_priority("tc_companionship");
+}
+
+sub remove_obsolete_scheduling_priority
+{
+	my $table_name = $_[0];
+
+	#print "\n-> Cleaning $table_name\n";
 	
+	#$sth = $dbh->prepare("SELECT scheduling_priority FROM $table_name where scheduling_priority IS NOT NULL AND valid=0");
+	$sth = $dbh->prepare("SELECT * FROM $table_name WHERE valid=0");
+	$sth->execute or die "-E- DB error: $DBI::errstr\n";
+	while($sqlhashref = $sth->fetchrow_hashref) {
+		$scheduling_priority = $sqlhashref->{scheduling_priority};
+		$individual = $sqlhashref->{individual};
+		#$name = $sqlhashref->{name};
+		if ($scheduling_priority != "NULL") {
+			#print "$name\n";
+			# set scheduling_priority to NULL
+			#print "UPDATE $table_name SET scheduling_priority=NULL WHERE individual=$individual\n";
+			$sth2 = $dbh->prepare("UPDATE $table_name SET scheduling_priority=NULL WHERE individual=$individual");
+			$sth2->execute or die "-E- DB error: $DBI::errstr\n";
+			
+			# remove entry from tc_scheduling_priority
+			#print "DELETE FROM tc_scheduling_priority WHERE scheduling_priority=$scheduling_priority\n";
+			$sth2 = $dbh->prepare("DELETE FROM tc_scheduling_priority WHERE scheduling_priority=$scheduling_priority");
+			$sth2->execute or die "-E- DB error: $DBI::errstr\n";
+		}
+	}
 }
 
 ######################################################################
@@ -661,13 +673,14 @@ if(defined $opt_o) {
 
 ###################################################
 # Process command line options
+our $datadir;
 if(defined $opt_n) { $datadir = $opt_n; }
 else { $datadir = shift(@ARGV); }
 print "\n-> Processing all ward data files in $datadir\n";
 
 ###################################################
 # Parse Ward Data Files
-&csv_to_hash("$datadir/Organization\ class\ per\ member.csv", \%organization_class_data);
+&optional_csv_to_hash("$datadir/Organization\ class\ per\ member.csv", \%organization_class_data);
 &csv_to_hash("$datadir/Membership.csv",\%membership_data);
 &csv_to_hash("$datadir/HomeTeaching.csv",\%hometeaching_data);
 &csv_to_hash("$datadir/Organization.csv",\%organization_data);
@@ -688,10 +701,9 @@ if($opt_v) {
 
 if($opt_s) { $dbh->disconnect(); exit; }
 
-# Now update the various eq DB tables
+# Now update the various DB tables
 &update_tc_calling_table();
 &update_tc_individual_table();
-&update_tc_district_table();
 &update_tc_companionship_table();
 &update_tc_family_table();
 &update_tc_visit_table();