From 4800cbc5c11e33d6f32ea72c80c07a71ab02c158 Mon Sep 17 00:00:00 2001
From: "Alan J. Pippin" <ajp@server.pippins.net>
Date: Wed, 23 Apr 2008 07:34:46 -0600
Subject: [PATCH] Added ability to pull address of district leaders for their
 interviews.

---
 bin/import_ward_data   |  5 +++-
 bin/upgrade_2_0_to_3_0 |  4 +++
 inc/class.eq.inc.php   | 64 ++++++++++++++++++++++++++++++++++++++----
 3 files changed, 66 insertions(+), 7 deletions(-)

diff --git a/bin/import_ward_data b/bin/import_ward_data
index 4f88fd9..bf970a2 100755
--- a/bin/import_ward_data
+++ b/bin/import_ward_data
@@ -131,6 +131,7 @@ sub update_eq_aaronic_table
 #| Field       | Type             | Null | Key | Default | Extra          |
 #+-------------+------------------+------+-----+---------+----------------+
 #| elder       | int(16) unsigned |      | PRI | NULL    | auto_increment |
+#| indiv_id    | int(16) unsigned |      |     | NULL    |                |
 #| name        | varchar(60)      | YES  |     | NULL    |                |
 #| phone       | varchar(12)      | YES  |     | NULL    |                |
 #| email       | varchar(120)     | YES  |     | NULL    |                |
@@ -174,7 +175,7 @@ sub update_eq_elder_table
 		if($rows == 0) {
 		    # No existing records found for this elder, make a new entry
 		    print "   Adding new Elder: $elder_name\n";
-		    $sth = $dbh->prepare("insert into eq_elder values (NULL,'$elder_name','$phone','','1','','1','',$attending,1)");
+		    $sth = $dbh->prepare("insert into eq_elder values (NULL,'$id','$elder_name','$phone','','1','','1','',$attending,1)");
 		    $sth->execute or die "-E- DB error: $DBI::errstr\n";
 		} elsif($rows == 1) {
 		    # An existing record was found for this elder, update it
@@ -189,6 +190,8 @@ sub update_eq_elder_table
 		    $sth->execute or die "-E- DB error: $DBI::errstr\n";
 		    $sth = $dbh->prepare("update eq_elder set attending='$attending' where name='$elder_name'");
 		    $sth->execute or die "-E- DB error: $DBI::errstr\n";
+		    $sth = $dbh->prepare("update eq_elder set indiv_id='$id' where name='$elder_name'");
+		    $sth->execute or die "-E- DB error: $DBI::errstr\n";
 		} else {
 		    # More than one record was found. Error! This shouldn't happen.
 		    print "   -E- More than one record found ($rows) for Elder: $elder_name\n";
diff --git a/bin/upgrade_2_0_to_3_0 b/bin/upgrade_2_0_to_3_0
index 265699f..c37ba9d 100755
--- a/bin/upgrade_2_0_to_3_0
+++ b/bin/upgrade_2_0_to_3_0
@@ -19,6 +19,10 @@ $dbh=DBI->connect("dbi:mysql:dbname=$dbname:host=$dbhost:port=$dbport",$dbuser,$
 $sth = $dbh->prepare("ALTER TABLE `eq_elder` ADD `email` VARCHAR( 120 ) NULL AFTER `phone`");
 $sth->execute or die "-E- DB error: $DBI::errstr\n";
 
+# Add a new 'indiv_id' field to the eq_elder table
+$sth = $dbh->prepare("ALTER TABLE `eq_elder` ADD `indiv_id` INT( 16 ) UNSIGNED NOT NULL AFTER `elder`");
+$sth->execute or die "-E- DB error: $DBI::errstr\n";
+
 # Add a new 'location' field to the eq_appointment table
 $sth = $dbh->prepare("ALTER TABLE `eq_appointment` ADD `location` VARCHAR( 120 ) NULL AFTER `time`");
 $sth->execute or die "-E- DB error: $DBI::errstr\n";
diff --git a/inc/class.eq.inc.php b/inc/class.eq.inc.php
index 2ffb985..32f8245 100644
--- a/inc/class.eq.inc.php
+++ b/inc/class.eq.inc.php
@@ -1423,6 +1423,16 @@ class eq
 	$interviewer = $this->db->f('elder');
 	$district_number = '*';
 	$district_name = $president_name;
+	$sql = "SELECT * FROM eq_elder where elder='$president_id'";
+	$this->db2->query($sql,__LINE__,__FILE__);
+	if($this->db2->next_record()) {
+	  $indiv_id = $this->db2->f('indiv_id');
+	}
+	$sql = "SELECT * FROM eq_parent where indiv_id='$indiv_id'";
+	$this->db2->query($sql,__LINE__,__FILE__);
+	if($this->db2->next_record()) {
+	  $president_address = $this->db2->f('address');
+	}
       } else {
 	print "<hr><font color=red><h3>-E- Unable to locate EQ President in eq_presidency table</h3></font></hr>";
 	return;
@@ -1438,7 +1448,7 @@ class eq
 		$elder = $entry['elder'];
 		$appointment = $entry['appointment'];
 		$location = $entry['location'];
-		if($location == "") { $location = "$president_last_name"." home"; }
+		if($location == "") { $location = "$president_last_name"." home ($president_address)"; }
 		if($elder == 0) { $location = ""; }
 		
 		//Only perform a database update if we have made a change to this appointment
@@ -1518,7 +1528,7 @@ class eq
 	  $appointment = $this->db->f('appointment');
 	  $elder = $this->db->f('elder');
 	  $location = $this->db->f('location');
-	  if(($location == "") && ($elder > 0)) { $location = "$president_last_name"." home"; }
+	  if(($location == "") && ($elder > 0)) { $location = "$president_last_name"." home ($president_address)"; }
 	  
 	  $date = $this->db->f('date');
 	  $date_array = explode("-",$date);
@@ -1747,7 +1757,17 @@ class eq
 		  $supervisor = $entry['supervisor'];
 		  $supervisor_array = explode(",", $elderid2name[$supervisor]);
 		  $supervisor_last_name = $supervisor_array[0];
-		  $location = "$supervisor_last_name"." home";
+		  $sql = "SELECT * FROM eq_elder where elder='$supervisor'";
+		  $this->db2->query($sql,__LINE__,__FILE__);
+		  if($this->db2->next_record()) {
+		    $indiv_id = $this->db2->f('indiv_id');
+		  }
+		  $sql = "SELECT * FROM eq_parent where indiv_id='$indiv_id'";
+		  $this->db2->query($sql,__LINE__,__FILE__);
+		  if($this->db2->next_record()) {
+		    $supervisor_address = $this->db2->f('address');
+		  }
+		  $location = "$supervisor_last_name"." home ($supervisor_address)";
 		}
 		if($elder == 0) { $location = ""; }
 		
@@ -1830,6 +1850,17 @@ class eq
       $supervisor = $districts[$d]['supervisor'];
       $supervisor_array = explode(",", $supervisor);
       $supervisor_last_name = $supervisor_array[0];
+      $sql = "SELECT * FROM eq_elder where elder='$supervisor'";
+      $this->db2->query($sql,__LINE__,__FILE__);
+      if($this->db2->next_record()) {
+	$indiv_id = $this->db2->f('indiv_id');
+      }
+      $sql = "SELECT * FROM eq_parent where indiv_id='$indiv_id'";
+      $this->db2->query($sql,__LINE__,__FILE__);
+      if($this->db2->next_record()) {
+	$supervisor_address = $this->db2->f('address');
+      }
+      $location = "$supervisor_last_name"." home ($supervisor_address)";
       $table_title = "District ".$districts[$d]['district'].": ".$districts[$d]['name'].": All Elders with Interviews Not Completed";
       $appt_table_title = "District ".$districts[$d]['district'].": ".$districts[$d]['name'].": Interview Appointment Slots";
       $this->t->set_var('table_title',$table_title);
@@ -1844,7 +1875,7 @@ class eq
 	  $appointment = $this->db->f('appointment');
 	  $elder = $this->db->f('elder');
 	  $location = $this->db->f('location');
-	  if(($location == "") && ($elder > 0)) { $location = "$supervisor_last_name"." home"; }
+	  if(($location == "") && ($elder > 0)) { $location = "$supervisor_last_name"." home ($supervisor_address)"; }
 	  
 	  $date = $this->db->f('date');
 	  $date_array = explode("-",$date);
@@ -3636,6 +3667,7 @@ class eq
 	  $presidency_data[$i]['name'] = $this->db->f('name');
 	  $presidency_data[$i]['elder'] = $this->db->f('elder');
 	  $presidency2name[$presidency_data[$i]['id']] = $presidency_data[$i]['name'];
+	  $presidency2elder[$presidency_data[$i]['id']] = $presidency_data[$i]['elder'];
 	  $i++;
 	}
       
@@ -3688,7 +3720,17 @@ class eq
 		   else if($elder > 0) {
 		     $supervisor_name_array = explode(",",$presidency2name[$presidency]);
 		     $supervisor_last_name = $supervisor_name_array[0];
-		     $location = "$supervisor_last_name"." home";
+		     $sql = "SELECT * FROM eq_elder where elder='$presidency2elder[$presidency]'";
+		     $this->db2->query($sql,__LINE__,__FILE__);
+		     if($this->db2->next_record()) {
+		       $indiv_id = $this->db2->f('indiv_id');
+		     }
+		     $sql = "SELECT * FROM eq_parent where indiv_id='$indiv_id'";
+		     $this->db2->query($sql,__LINE__,__FILE__);
+		     if($this->db2->next_record()) {
+		       $supervisor_address = $this->db2->f('address');
+		     }
+		     $location = "$supervisor_last_name"." home ($supervisor_address)";
 		   }
 		 }
 		 
@@ -3805,7 +3847,17 @@ class eq
 	      else if($elder > 0) {
 		$supervisor_name_array = explode(",",$presidency2name[$presidency]);
 		$supervisor_last_name = $supervisor_name_array[0];
-		$location = "$supervisor_last_name"." home";
+		$sql = "SELECT * FROM eq_elder where elder='$presidency2elder[$presidency]'";
+		$this->db2->query($sql,__LINE__,__FILE__);
+		if($this->db2->next_record()) {
+		  $indiv_id = $this->db2->f('indiv_id');
+		}
+		$sql = "SELECT * FROM eq_parent where indiv_id='$indiv_id'";
+		$this->db2->query($sql,__LINE__,__FILE__);
+		if($this->db2->next_record()) {
+		  $supervisor_address = $this->db2->f('address');
+		}
+		$location = "$supervisor_last_name"." home ($supervisor_address)";
 	      }
 	    }
 	    
-- 
2.34.1