From 9dc519979d37f94ab0bec341d3508819cd3b68d3 Mon Sep 17 00:00:00 2001 From: "Alan J. Pippin" Date: Mon, 11 Feb 2008 23:53:30 -0700 Subject: [PATCH] Merged the eq_interview table with the eq_ppi table. Eliminated all references to the eq_interview table. The newly created bin/merge_eq_ppi_eq_interview_tables script needs to be run after pulling this code base. After that script runs, you can safely drop the eq_interview table once you know your entries have been moved over to the eq_ppi table properly. --- bin/merge_eq_ppi_eq_interview_tables | 50 ++++++++++++++++++++++++++++ inc/class.eq.inc.php | 21 ++++++------ sql/eq.sql | 14 +------- 3 files changed, 62 insertions(+), 23 deletions(-) create mode 100755 bin/merge_eq_ppi_eq_interview_tables diff --git a/bin/merge_eq_ppi_eq_interview_tables b/bin/merge_eq_ppi_eq_interview_tables new file mode 100755 index 0000000..c53ed4f --- /dev/null +++ b/bin/merge_eq_ppi_eq_interview_tables @@ -0,0 +1,50 @@ +#!/usr/bin/perl + +use DBI; +use Getopt::Std; + +################################################### +# GLOBALS +$dbname = "phpgroupware"; +$dbhost = "192.168.0.2"; # This can be an IP address or name +$dbport = 3306; +$dbuser = "phpgroupware"; # This may require an additional '\@localhost' +$dbpass = "phpgroupware"; +################################################### + +################################################### +# Connect to the database +$dbh=DBI->connect("dbi:mysql:dbname=$dbname:host=$dbhost:port=$dbport",$dbuser,$dbpass,{ + AutoCommit=>0, + PrintError=>0}) or print "Connect Failure:".$DBI::errstr."\n" and exit 2; +################################################### + +# Add a new 'aaronic' column to the eq_ppi table +$sth = $dbh->prepare("ALTER TABLE `eq_ppi` ADD `aaronic` INT( 16 ) NOT NULL DEFAULT '0' AFTER `elder`"); +$sth->execute or die "-E- DB error: $DBI::errstr\n"; + +# Parse the data out of the eq_interview table and add them to the eq_ppi table +$sth = $dbh->prepare("select * from eq_interview"); +$sth->execute or die "-E- DB error: $DBI::errstr\n"; +while($row = $sth->fetchrow_hashref) { + $interviewer = $row->{interviewer}; + $elder = $row->{elder}; + $aaronic = $row->{aaronic}; + $date = $row->{date}; + $notes = $row->{notes}; + $notes =~ s/\'/\\\'/g; + $eqpresppi = 0; + #print "$interviewer $elder $aaronic $date $eqpresppi $notes\n"; + $sth2 = $dbh->prepare("insert into eq_ppi values (NULL,'$interviewer','$elder','$aaronic','$date','$notes','$eqpresppi')"); + $sth2->execute or die "-E- DB error: $DBI::errstr\n"; +} + +print "\n-> Succesfully imported all eq_interview table entries into eq_ppi table...\n"; +print "-> You may drop the eq_interview table once you know all your data was transferred to the eq_ppi table correctly..\n"; + +################################################### +# Disconnect from the database +$dbh->disconnect(); +################################################### + + diff --git a/inc/class.eq.inc.php b/inc/class.eq.inc.php index 3dac97e..5b1679d 100644 --- a/inc/class.eq.inc.php +++ b/inc/class.eq.inc.php @@ -1908,12 +1908,12 @@ class eq } // If this companionship has had a hometeaching interview this quarter, don't show them on the schedule list - $sql = "SELECT * FROM eq_interview WHERE date >= '$quarter_start' AND date < '$quarter_end' ". + $sql = "SELECT * FROM eq_ppi WHERE date >= '$quarter_start' AND date < '$quarter_end' ". "AND elder=" . $id; $this->db2->query($sql,__LINE__,__FILE__); if(!$this->db2->next_record()) { - $sql = "SELECT * FROM eq_interview WHERE elder=" . $id . " ORDER BY date DESC"; + $sql = "SELECT * FROM eq_ppi WHERE elder=" . $id . " ORDER BY date DESC"; $this->db3->query($sql,__LINE__,__FILE__); if($this->db3->next_record()) { $date = $this->db3->f('date'); } else { $date = ""; } $link_data['menuaction'] = 'eq.eq.int_update'; @@ -2464,6 +2464,7 @@ class eq " ppi='" . $ppi . "'" . ", interviewer='" . $interviewer . "'" . ", elder='" . $elder . "'" . + ", aaronic='" . $aaronic . "'" . ", date='" . $date . "'" . ", notes='" . $notes . "'" . ", eqpresppi='" . $eqpresppi . "'" . @@ -2475,8 +2476,8 @@ class eq if($action == 'insert') { $notes = get_var('notes',array('POST')); - $this->db->query("INSERT INTO eq_ppi (interviewer,elder,date,notes,eqpresppi) " - . "VALUES ('" . $interviewer . "','" . $elder . "','" + $this->db->query("INSERT INTO eq_ppi (interviewer,elder,aaronic,date,notes,eqpresppi) " + . "VALUES ('" . $interviewer . "','" . $elder . "','" . $aaronic . "','" . $date . "','" . $notes . "','" . $eqpresppi ."')",__LINE__,__FILE__); $this->ppi_view(); return false; @@ -2678,7 +2679,7 @@ class eq $month_start = "$year"."-"."$month"."-"."01"; $month_end = "$year"."-"."$month"."-"."31"; $month = "$month"."/"."$year"; - $sql = "SELECT * FROM eq_interview WHERE date >= '$month_start' AND date <= '$month_end' ". + $sql = "SELECT * FROM eq_ppi WHERE date >= '$month_start' AND date <= '$month_end' ". "AND elder=" . $elder_id . " AND aaronic=" . $aaronic_id; $this->db2->query($sql,__LINE__,__FILE__); $header_row .= "$month"; @@ -2789,14 +2790,14 @@ class eq if($action == 'save') { $notes = get_var('notes',array('POST')); - $this->db->query("UPDATE eq_interview set " . - " interview='" . $interview . "'" . + $this->db->query("UPDATE eq_ppi set " . + " ppi='" . $interview . "'" . ", interviewer='" . $interviewer . "'" . ", elder='" . $elder . "'" . ", aaronic='" . $aaronic . "'" . ", date='" . $date . "'" . ", notes='" . $notes . "'" . - " WHERE interview=" . $interview,__LINE__,__FILE__); + " WHERE ppi=" . $interview,__LINE__,__FILE__); $this->int_view(); return false; } @@ -2804,7 +2805,7 @@ class eq if($action == 'insert') { $notes = get_var('notes',array('POST')); - $this->db->query("INSERT INTO eq_interview (interviewer,elder,aaronic,date,notes) " + $this->db->query("INSERT INTO eq_ppi (interviewer,elder,aaronic,date,notes) " . "VALUES ('" . $interviewer . "','" . $elder . "','" . $aaronic . "','" . $date . "','" . $notes ."')",__LINE__,__FILE__); $this->int_view(); @@ -2829,7 +2830,7 @@ class eq if($action == 'edit' || $action == 'view') { - $sql = "SELECT * FROM eq_interview WHERE interview=".$interview; + $sql = "SELECT * FROM eq_ppi WHERE ppi=".$interview; $this->db->query($sql,__LINE__,__FILE__); $this->db->next_record(); $this->t->set_var('interview',$interview); diff --git a/sql/eq.sql b/sql/eq.sql index 277ab8b..7d0add8 100644 --- a/sql/eq.sql +++ b/sql/eq.sql @@ -159,19 +159,6 @@ CREATE TABLE `eq_family` ( PRIMARY KEY (`family`) ) ENGINE=MyISAM AUTO_INCREMENT=277 DEFAULT CHARSET=latin1; --- --- Table structure for table `eq_interview` --- -CREATE TABLE `eq_interview` ( - `interview` int(16) unsigned NOT NULL auto_increment, - `interviewer` int(16) unsigned default NULL, - `elder` int(16) unsigned default NULL, - `aaronic` int(16) unsigned NOT NULL default '0', - `date` date default NULL, - `notes` text, - PRIMARY KEY (`interview`) -) ENGINE=MyISAM AUTO_INCREMENT=65 DEFAULT CHARSET=latin1; - -- -- Table structure for table `eq_parent` -- @@ -203,6 +190,7 @@ CREATE TABLE `eq_ppi` ( `ppi` int(16) unsigned NOT NULL auto_increment, `interviewer` int(16) unsigned default NULL, `elder` int(16) unsigned default NULL, + `aaronic` int(16) unsigned NOT NULL default '0', `date` date default NULL, `notes` text, `eqpresppi` tinyint(1) default '0', -- 2.34.1