From 4800cbc5c11e33d6f32ea72c80c07a71ab02c158 Mon Sep 17 00:00:00 2001 From: "Alan J. Pippin" 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 "

-E- Unable to locate EQ President in eq_presidency table

"; 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