X-Git-Url: http://git.pippins.net/embedvideo/.git/?a=blobdiff_plain;f=bin%2Fimport_ward_data;h=2234b8a2e63ae3901bda8c6421c51fd2fa53228b;hb=HEAD;hp=964d930303176997143ee8995a7ae03c083f3881;hpb=53b9dc59fa5aabe9c55b109d158c5db379955013;p=eq%2F.git

diff --git a/bin/import_ward_data b/bin/import_ward_data
index 964d930..2234b8a 100755
--- a/bin/import_ward_data
+++ b/bin/import_ward_data
@@ -310,7 +310,7 @@ sub update_tc_companionship_table
 		for my $companion (keys %{$companionships->{$companionship}->{'companions'}}) {
 			#print "companion = $companion\n";
 			# see if $companion is in tc_companion
-			$sth = $dbh->prepare("SELECT * FROM tc_companion AS tc JOIN (tc_individual AS ti, tc_companionship AS tp) WHERE tc.individual=ti.individual AND tc.companionship=tp.companionship AND tp.mls_id=$companionship AND ti.name='$companion' COLLATE latin1_general_cs AND tc.valid=1");
+			$sth = $dbh->prepare("SELECT * FROM tc_companion AS tc JOIN (tc_individual AS ti, tc_companionship AS tp) WHERE tc.individual=ti.individual AND tc.companionship=tp.companionship AND tp.mls_id=$companionship AND ti.name='$companion' COLLATE latin1_general_cs AND tp.type='H' AND tc.valid=1");
 			$sth->execute or die "-E- DB error: $DBI::errstr\n";
 			my @data = ();
 			while($sqlhashref = $sth->fetchrow_hashref) { push(@data, $sqlhashref); }
@@ -318,7 +318,7 @@ sub update_tc_companionship_table
 			if($rows == 0) {
 				print "   $companion is not in tc_companion\n";
 				$changed = 1;
-				last;
+				next;
 			}
 			$db_individual = $data[0]->{'individual'};
 			if ($tc_companionship_id == 0) {
@@ -357,7 +357,7 @@ sub update_tc_companionship_table
 			# loop through families from HomeTeaching.csv
 			for my $family (keys %{$companionships->{$companionship}->{'families'}}) {
 				#print "SELECT * FROM tc_family AS tf JOIN (tc_companionship AS tc, tc_individual AS ti) WHERE tf.companionship=tc.companionship AND tf.individual=ti.individual AND ti.name=\"$family\" COLLATE latin1_general_cs AND tf.valid=1\n";
-				$sth = $dbh->prepare("SELECT * FROM tc_family AS tf JOIN (tc_companionship AS tc, tc_individual AS ti) WHERE tf.companionship=tc.companionship AND tf.individual=ti.individual AND ti.name=\"$family\" COLLATE latin1_general_cs AND tf.valid=1");
+				$sth = $dbh->prepare("SELECT * FROM tc_family AS tf JOIN (tc_companionship AS tc, tc_individual AS ti) WHERE tf.companionship=tc.companionship AND tf.individual=ti.individual AND ti.name=\"$family\" COLLATE latin1_general_cs AND tc.type='H' AND tf.valid=1");
 				$sth->execute or die "-E- DB error: $DBI::errstr\n";
 				# if family isn't in tc_family table, we have a change
 				# if family in tc_family has a different companionship assigned to it, we have a change
@@ -379,12 +379,12 @@ sub update_tc_companionship_table
 		if ($changed == 1) {
 			# invalidate existing companionship in database
 			print "   invalidating companionship:$tc_companionship_id\n";
-			$sth = $dbh->prepare("UPDATE tc_companionship SET valid=0 WHERE companionship='$tc_companionship_id'");
+			$sth = $dbh->prepare("UPDATE tc_companionship SET valid=0 WHERE companionship='$tc_companionship_id' AND type='H'");
 			$sth->execute or die "-E- DB error: $DBI::errstr\n";
 			
 			# create new companionship in database
 			print "   creating a new companionship\n";
-			$sth = $dbh->prepare("INSERT INTO tc_companionship values (NULL,$companionship,$district,1)");
+			$sth = $dbh->prepare("INSERT INTO tc_companionship values (NULL,$companionship,$district,'H',1)");
 			$sth->execute or die "-E- DB error: $DBI::errstr\n";
 			$comp_id = $dbh->last_insert_id(NULL,NULL,'tc_companionship',NULL);
 			
@@ -392,12 +392,14 @@ sub update_tc_companionship_table
 			for my $companion (keys %{$companionships->{$companionship}->{'companions'}}) {
 				print "   companion=$companion\n";
 				# if companion exists, preserve scheduling_priority
-				$sth = $dbh->prepare("SELECT * FROM tc_companion AS tc JOIN (tc_individual AS ti, tc_companionship AS tp) WHERE tc.individual=ti.individual AND tc.companionship=tp.companionship AND tp.mls_id=$companionship AND ti.name=\"$companion\" COLLATE latin1_general_cs AND tc.valid=1");
+				#print "SELECT tc.* FROM tc_companion AS tc JOIN (tc_individual AS ti, tc_companionship AS tp) WHERE tc.individual=ti.individual AND tc.companionship=tp.companionship AND tp.mls_id=$companionship AND ti.name=\"$companion\" AND tc.valid=1\n";
+				$sth = $dbh->prepare("SELECT tc.* FROM tc_companion AS tc JOIN (tc_individual AS ti, tc_companionship AS tp) WHERE tc.individual=ti.individual AND tc.companionship=tp.companionship AND tp.mls_id=$companionship AND ti.name=\"$companion\" AND tp.type='H' AND tc.valid=1");
 				$sth->execute or die "-E- DB error: $DBI::errstr\n";
 				if ($sqlhashref = $sth->fetchrow_hashref) {
 					my $individual = $sqlhashref->{'individual'};
 					my $scheduling_priority = $sqlhashref->{'scheduling_priority'};
 					print "   adding new companion $individual\n";
+					#print "INSERT INTO tc_companion values (NULL,$individual,$comp_id,$scheduling_priority,1)\n";
 					$sth = $dbh->prepare("INSERT INTO tc_companion values (NULL,$individual,$comp_id,$scheduling_priority,1)");
 					$sth->execute or die "-E- DB error: $DBI::errstr\n";
 				} else {
@@ -422,6 +424,7 @@ sub update_tc_companionship_table
 			}
 			
 			# invalidate existing old companions in database
+			#print "UPDATE tc_companion SET valid=0 WHERE companionship=$tc_companionship_id AND valid=1\n";
 			$sth = $dbh->prepare("UPDATE tc_companion SET valid=0 WHERE companionship=$tc_companionship_id AND valid=1");
 			$sth->execute or die "-E- DB error: $DBI::errstr\n";
 			
@@ -489,13 +492,6 @@ sub update_tc_family_table
 		{
 			$hashref = $hometeaching_data{$index};
 			foreach $key (keys %$hashref) {
-				#if($hometeaching_data{$index}{'Household'} =~ /(\S+)\s+(\S+),\s+(\S+)\s+(.*)/) {
-					#$a = $1; $b = $2; $c = $3; $d = $4;
-					#if($name =~ /$a/ && $hometeaching_data{$index}{'Household'} !~ /$name/i) { 
-						#print "I: Adjusting hometeaching match from: $hometeaching_data{$index}{'Household'} to $a, $c $d\n";
-						#$hometeaching_data{$index}{'Household'} = "$a, $c $d";
-					#}
-				#}
 				if($key =~ /Quorum/i &&
 				   $hometeaching_data{$index}{$key} =~ /Elders/i &&
 				   $hometeaching_data{$index}{'Household'} =~ /$name/i &&
@@ -600,7 +596,7 @@ sub update_tc_visit_table
 			my $importing_status = $visit_status{$history[$i]};
 			#print "importing_status = $importing_status\n";
 			#print "select * from tc_visit where family=$family_id and companionship=$comp_id and date='$visit_date'\n";
-			$sth = $dbh->prepare("select * from tc_visit where family=$family_id and companionship=$comp_id and date='$visit_date'");
+			$sth = $dbh->prepare("select * from tc_visit where family=$family_id and date='$visit_date'");
 			$sth->execute or die "-E- DB error: $DBI::errstr\n";
 			my @visit_data = ();
 			while($sqlhashref = $sth->fetchrow_hashref) { push(@visit_data, $sqlhashref); }
@@ -619,7 +615,7 @@ sub update_tc_visit_table
 				if ($importing_status ne '')
 				{
 					# add visit if it doesn't exist in tc_visit
-					$sth = $dbh->prepare("insert into tc_visit values (NULL, '$family_id', '$comp_id', '', '', '$visit_date', '', '$importing_status', 'hometeaching')");
+					$sth = $dbh->prepare("insert into tc_visit values (NULL, '$family_id', '$comp_id', '$visit_date', '', '$importing_status', 'H')");
 					$sth->execute or die "-E- DB error: $DBI::errstr\n";
 				}
 			}
@@ -738,6 +734,7 @@ sub update_tc_scheduling_priority_table
 			$sth2 = $dbh->prepare("insert into tc_scheduling_priority values (NULL, 30, '')");
 			$sth2->execute or die "-E- DB error: $DBI::errstr\n";
 			$scheduling_priority = $dbh->last_insert_id(NULL,NULL,'tc_scheduling_priority',NULL);
+			#print "update tc_companion set scheduling_priority=$scheduling_priority where individual=$individual\n";
 			$sth2 = $dbh->prepare("update tc_companion set scheduling_priority=$scheduling_priority where individual=$individual");
 			$sth2->execute or die "-E- DB error: $DBI::errstr\n";
 		}
@@ -751,26 +748,32 @@ sub remove_obsolete_scheduling_priority
 
 	#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");
+			#print "SELECT * FROM $table_name WHERE scheduling_priority=$scheduling_priority AND valid=1\n";
+			$sth2 = $dbh->prepare("SELECT * FROM $table_name WHERE scheduling_priority=$scheduling_priority AND valid=1");
 			$sth2->execute or die "-E- DB error: $DBI::errstr\n";
+			if (!($sqlhashref2 = $sth2->fetchrow_hashref)) {
+				#print "$name\n";
+				# set scheduling_priority to NULL
+				#print "UPDATE $table_name SET scheduling_priority=NULL WHERE individual=$individual\n";
+				$sth3 = $dbh->prepare("UPDATE $table_name SET scheduling_priority=NULL WHERE individual=$individual");
+				$sth3->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";
+				$sth3 = $dbh->prepare("DELETE FROM tc_scheduling_priority WHERE scheduling_priority=$scheduling_priority");
+				$sth3->execute or die "-E- DB error: $DBI::errstr\n";
+				$sth3->finish();
+			}
+			$sth2->finish();
 		}
 	}
+	$sth->finish();
 }
 
 ######################################################################