<?php
/*
* Copyright (c) 2009, Francis Li and Simona Brusa Pasque'
* All rights reserved.
*
* Redistribution and use in source and binary forms, with or without
* modification, are permitted provided that the following conditions are met:
*     * Redistributions of source code must retain the above copyright
*       notice, this list of conditions and the following disclaimer.
*     * Redistributions in binary form must reproduce the above copyright
*       notice, this list of conditions and the following disclaimer in the
*       documentation and/or other materials provided with the distribution.
*     * Neither the name of the organization nor the
*       names of its contributors may be used to endorse or promote products
*       derived from this software without specific prior written permission.
*
* THIS SOFTWARE IS PROVIDED BY Francis Li and Simona Brusa Pasque' ''AS IS''
* AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, 
* THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR
* PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL Francis Li nor Simona Brusa Pasque'
* BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, 
* OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, 
* PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS;
* OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY,
* WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR
* OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF
* ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
*/

class Bill {
  //// id primary key
  public $id;
  //// congress
  public $congress;
  //// type of bill
  public $billtype;
  //// bill number
  public $number;
  //// datetime when introduced
  public $introduced;
  //// primary sponsor
  public $sponsor_rep_id;

  //// all titles
  public $titles;
  //// all sponsors, mapped as rep_id => date_joined
  public $sponsors;

  function get_billtype_full() {
    $types = array('h' => 'H.R.',
		  'hr' => 'H.Res.',
		  'hj' => 'H.J.Res.',
		  'hc' => 'H.Con.Res.',
		  's' => 'S.',
		  'sr' => 'S.Res.',
		  'sj' => 'S.J.Res.',
		  'sc' => 'S.Con.Res.');
    if (isset($types[$this->billtype])) {
      return $types[$this->billtype];
    }
    return $this->billtype;
  }

  static function restore($row) {
    $bill = new Bill();
    $bill->id = $row['id'];
    $bill->congress = $row['congress'];
    $bill->billtype = $row['billtype'];
    $bill->number = $row['number'];
    $bill->introduced = strtotime($row['introduced']);
    $bill->sponsor_rep_id = $row['sponsor_rep_id'];
    return $bill;
  }

  static function get_titles($id) {
    require_once "db.inc.php";
    $query =<<<EOQ
SELECT title FROM bill_title
WHERE bill_id={$id}
ORDER BY id ASC
EOQ;
    $result = mysql_query($query);
    if ($result) {
      $titles = array();
      while ($row = mysql_fetch_assoc($result)) {
	$titles[] = $row['title'];
      }
      return $titles;
    } else {
      error_log(mysql_error() .": ". $query);
    }
    return null;
  }

  static function get_sponsors($id) {
    require_once "db.inc.php";
    $query =<<<EOQ
SELECT rep_id, joined FROM bill_sponsor
WHERE bill_id={$id}
EOQ;
    $result = mysql_query($query);
    if ($result) {
      $sponsors = array();
      while ($row = mysql_fetch_assoc($result)) {
	$sponsors[$row['rep_id']] = strtotime($row['joined']);
      }
      return $sponsors;
    } else {
      error_log(mysql_error() .": ". $query);
    }
    return null;
  }

  static function find_cosponsored($rep1_id, $rep2_id, $index=0, $limit=PHP_INT_MAX) {
    require_once 'db.inc.php';
    $query =<<<EOQ
(SELECT bill.*, bill.introduced AS ui 
 FROM bill JOIN bill_sponsor 
 ON bill.id=bill_sponsor.bill_id 
 WHERE bill.sponsor_rep_id={$rep1_id} AND bill_sponsor.rep_id={$rep2_id})
UNION
(SELECT bill.*, bill.introduced AS ui 
 FROM bill JOIN bill_sponsor 
 ON bill.id=bill_sponsor.bill_id 
 WHERE bill.sponsor_rep_id={$rep2_id} AND bill_sponsor.rep_id={$rep1_id})
UNION
(SELECT bill.*, bill.introduced AS ui 
 FROM bill 
 JOIN bill_sponsor AS bs1 ON bill.id=bs1.bill_id 
 JOIN bill_sponsor AS bs2 ON bill.id=bs2.bill_id
 WHERE bs1.rep_id={$rep1_id} AND bs2.rep_id={$rep2_id})
ORDER BY ui DESC
LIMIT {$index}, {$limit}
EOQ;
    $result = mysql_query($query);
    $bills = array();
    if ($result) {
      while ($row = mysql_fetch_row($result)) {
	$bill = Bill::get($row[0]);
	if ($bill != null) {
	  $bills[] = $bill;
	}
      }
    } else {
      error_log(mysql_error() .': '. $query);
    }
    return $bills;
  }

  static function find_by_sponsor($rep_id, $index=0, $limit=PHP_INT_MAX) {
    require_once 'db.inc.php';
    $query =<<<EOQ
SELECT DISTINCT bill.id 
FROM bill 
LEFT JOIN bill_sponsor ON bill.id=bill_sponsor.bill_id 
WHERE bill.sponsor_rep_id={$rep_id} 
OR bill_sponsor.rep_id={$rep_id}
ORDER BY introduced DESC
LIMIT {$index}, {$limit}
EOQ;
    $result = mysql_query($query);
    $bills = array();
    if ($result) {
      while ($row = mysql_fetch_row($result)) {
	$bill = Bill::get($row[0]);
	if ($bill != null) {
	  $bills[] = $bill;
	}
      }
    }
    return $bills;
  }

  static function get($id) {
    require_once "db.inc.php";
    $query =<<<EOQ
SELECT *
FROM bill 
WHERE id={$id}
EOQ;
    $result = mysql_query($query);
    if ($result) {
      $row = mysql_fetch_assoc($result);
      if ($row) {
	$bill = Bill::restore($row);      
	$bill->titles = Bill::get_titles($bill->id);
	$bill->sponsors = Bill::get_sponsors($bill->id);
	return $bill;
      }
    } else {
      error_log(mysql_error() .": ". $query);
    }     
    return null;
  }

  static function get_by_congress_type_number($congress, $billtype, $number) {
    require_once "db.inc.php";
    $query =<<<EOQ
SELECT *
FROM bill 
WHERE congress={$congress} 
AND billtype='{$billtype}' 
AND number={$number}
EOQ;
    $result = mysql_query($query);
    if ($result) {
      $row = mysql_fetch_assoc($result);
      if ($row) {
	$bill = Bill::restore($row);      
	$bill->titles = Bill::get_titles($bill->id);
	$bill->sponsors = Bill::get_sponsors($bill->id);
	return $bill;
      }
    } else {
      error_log(mysql_error() .": ". $query);
    }     
    return null;
  }

  function save() {
    require_once "db.inc.php";
    //// update bill metadata itself
    $introduced = date("Y-m-d H:i:s", $this->introduced);
    $query =<<<EOQ
INSERT INTO bill
(congress, billtype, number, introduced, sponsor_rep_id)
VALUES
({$this->congress}, '{$this->billtype}', {$this->number}, '{$introduced}',
 {$this->sponsor_rep_id})
ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id),
congress=VALUES(congress), billtype=VALUES(billtype),
number=VALUES(number), introduced=VALUES(introduced), 
sponsor_rep_id=VALUES(sponsor_rep_id)
EOQ;
    $result = mysql_query($query);
    if ($result) {
      $lastId = mysql_insert_id();
      if ($lastId != 0) {
	$this->id = $lastId;
      }
    } else {
      error_log(mysql_error() .": ". $query);
    }

    //// handle updating all the titles
    foreach ($this->titles as $title_text) {
      $title_text = htmlentities($title_text, ENT_QUOTES);
      $query =<<<EOQ
INSERT INTO bill_title
(bill_id, title)
VALUES
({$this->id}, '{$title_text}')
ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), 
bill_id=VALUES(bill_id), title=VALUES(title)
EOQ;
      $result = mysql_query($query);
      if (!$result) {
	error_log(mysql_error() .": ". $query);
      }      
    }

    //// update co-sponsors list
    foreach ($this->sponsors as $rep_id => $joined) {
      $joined = date("Y-m-d H:i:s", $joined);
      $query =<<<EOQ
INSERT INTO bill_sponsor
(bill_id, rep_id, joined)
VALUES
({$this->id}, {$rep_id}, '{$joined}')
ON DUPLICATE KEY UPDATE
bill_id=VALUES(bill_id), rep_id=VALUES(rep_id), joined=VALUES(joined)
EOQ;
      $result = mysql_query($query);
      if (!$result) {
	error_log(mysql_error() .": ". $query);
      }      
    }
  }
}

class Amendment {
  //// id primary key
  public $id;
  //// congress
  public $congress;
  //// chamber
  public $chamber;
  //// number
  public $number;
  //// original bill
  public $bill_id;
  //// sequence number of amendment on bill
  public $sequence;
  //// datetime when amendment was offered
  public $offered;
  //// sponsor of amendmetn
  public $sponsor_rep_id;
  //// description
  public $description;

  static function restore($row) {
    $amdt = new Amendment();
    $amdt->id = $row['id'];
    $amdt->congress = $row['congress'];
    $amdt->chamber = $row['chamber'];
    $amdt->number = $row['number'];
    $amdt->bill_id = $row['bill_id'];
    $amdt->sequence = $row['sequence'];
    $amdt->offered = $row['offered'];
    $amdt->sponsor_rep_id = $row['sponsor_rep_id'];
    $amdt->description = $row['description'];
    return $amdt;
  }

  static function get($id) {
    require_once "db.inc.php";
    $query =<<<EOQ
SELECT *
FROM amendment
WHERE id={$id} 
EOQ;
    $result = mysql_query($query);
    if ($result) {
      $row = mysql_fetch_assoc($result);
      if ($row) {
	$amdt = Amendment::restore($row);      
	return $amdt;
      }
    } else {
      error_log(mysql_error() .": ". $query);
    }     
    return null;    
  }

  static function get_by_bill_sequence($bill_id, $sequence) {
    require_once "db.inc.php";
    $query =<<<EOQ
SELECT *
FROM amendment
WHERE bill_id={$bill_id} 
AND sequence={$sequence}
EOQ;
    $result = mysql_query($query);
    if ($result) {
      $row = mysql_fetch_assoc($result);
      if ($row) {
	$amdt = Amendment::restore($row);      
	return $amdt;
      }
    } else {
      error_log(mysql_error() .": ". $query);
    }     
    return null;    
  }

  static function get_by_congress_chamber_number($congress, $chamber, $number) {
    require_once "db.inc.php";
    $query =<<<EOQ
SELECT *
FROM amendment
WHERE congress={$congress}
AND chamber=UPPER('{$chamber}')
AND number={$number}
EOQ;
    $result = mysql_query($query);
    if ($result) {
      $row = mysql_fetch_assoc($result);
      if ($row) {
	$amdt = Amendment::restore($row);      
	return $amdt;
      }
    } else {
      error_log(mysql_error() .": ". $query);
    }     
    return null;    
  }

  function save() {
    require_once "db.inc.php";
    $offered = date("Y-m-d H:i:s", $this->offered);
    $description = htmlentities($this->description, ENT_QUOTES);
    $query =<<<EOQ
INSERT INTO amendment
(congress, chamber, number, bill_id, sequence, offered, sponsor_rep_id, 
 description)
VALUES
({$this->congress}, UPPER('{$this->chamber}'), {$this->number}, 
 {$this->bill_id}, {$this->sequence}, '{$offered}', {$this->sponsor_rep_id},
 '{$description}')
ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id),
congress=VALUES(congress), chamber=VALUES(chamber), number=VALUES(number), 
sequence=VALUES(sequence), offered=VALUES(offered), 
sponsor_rep_id=VALUES(sponsor_rep_id), description=VALUES(description)
EOQ;
    $result = mysql_query($query);
    if ($result) {
      $lastId = mysql_insert_id();
      if ($lastId != 0) {
	$this->id = $lastId;
      }
    } else {
      error_log(mysql_error() .": ". $query);
    }
  }
}

class Vote {
  public $id;
  public $congress;
  public $session;
  public $chamber;
  public $number;
  public $bill_id;
  public $amendment_id;
  public $question;
  public $type;
  public $required;
  public $result;
  public $votedate;

  public $votes;
  public $counts;

  function get_vote_full($rep_id) {
    if (isset($this->votes[$rep_id])) {
      $vote = $this->votes[$rep_id];
      if ($vote == '+') {
	return 'Yea';
      } else if ($vote == '-') {
	return 'Nay';
      } else if ($vote == 'P') {
	return 'Present';
      } else if ($vote == '0') {
	return 'Not voting';
      }
      return $vote;
    }
    return 'Not voting';
  }

  static function restore(&$row) {
    $vote = new Vote();
    $vote->id = $row['id'];
    $vote->congress = $row['congress'];
    $vote->session = $row['session'];
    $vote->chamber = $row['chamber'];
    $vote->number = $row['number'];
    $vote->bill_id = $row['bill_id'];
    $vote->amendment_id = $row['amendment_id'];
    $vote->question = $row['question'];
    $vote->type = $row['type'];
    $vote->required = $row['required'];
    $vote->result = $row['result'];
    $vote->votedate = strtotime($row['votedate']);    
    return $vote;
  }

  static function get_votes($vote_id) {
    require_once "db.inc.php";
    $query =<<<EOQ
SELECT * FROM vote_rep
WHERE vote_id={$vote_id}
EOQ;
    $result = mysql_query($query);
    if ($result) {
      $votes = array();
      while ($row = mysql_fetch_assoc($result)) {
	$votes[$row['rep_id']] = $row['vote'];
      }
      return $votes;
    } else {
      error_log(mysql_error() .": ". $query);
    }
    return null;
  }

  static function find_votes_agreed($rep1_id, $rep2_id, $index=0, $limit=PHP_INT_MAX) {
    $votes = array();
    $query =<<<EOQ
SELECT vote.* 
FROM vote 
JOIN vote_rep ON vote.id=vote_rep.vote_id 
JOIN vote_rep AS vote_rep2 ON vote.id=vote_rep2.vote_id 
WHERE vote_rep.rep_id={$rep1_id} 
AND vote_rep2.rep_id={$rep2_id} 
AND vote_rep.vote=vote_rep2.vote 
AND vote_rep.vote<>'0'
ORDER BY vote.votedate DESC 
LIMIT {$index}, {$limit}
EOQ;
    $result = mysql_query($query);
    if ($result) {
      while ($row = mysql_fetch_assoc($result)) {
	$vote = Vote::restore($row);
	$votes[] = $vote;
      }
    }
    return $votes;
  }

  static function find_votes_disagreed($rep1_id, $rep2_id, $index=0, $limit=PHP_INT_MAX) {
    $votes = array();
    $query =<<<EOQ
SELECT vote.* 
FROM vote 
JOIN vote_rep ON vote.id=vote_rep.vote_id 
JOIN vote_rep AS vote_rep2 ON vote.id=vote_rep2.vote_id 
WHERE vote_rep.rep_id={$rep1_id} 
AND vote_rep2.rep_id={$rep2_id} 
AND vote_rep.vote<>vote_rep2.vote 
AND vote_rep.vote<>'0'
AND vote_rep2.vote<>'0'
ORDER BY vote.votedate DESC 
LIMIT {$index}, {$limit}
EOQ;
    $result = mysql_query($query);
    if ($result) {
      while ($row = mysql_fetch_assoc($result)) {
	$vote = Vote::restore($row);
	$votes[] = $vote;
      }
    }
    return $votes;
  }

  static function find_by_rep_against_party($rep_id, $index=0, $limit=PHP_INT_MAX) {
    $votes = array();
    $rep = Rep::get($rep_id);
    $party = strtolower($rep->party);
    if (($party == 'd') || ($party == 'r')) {
      require_once "db.inc.php";
      $query =<<<EOQ
SELECT * FROM vote 
JOIN vote_rep ON vote.id=vote_rep.vote_id 
JOIN vote_stats ON vote.id=vote_stats.vote_id 
WHERE vote_rep.rep_id={$rep_id} 
AND vote_rep.vote<>'0' 
AND vote_rep.vote<>vote_stats.{$party}majority 
ORDER BY votedate DESC
LIMIT {$index}, {$limit}
EOQ;
      $result = mysql_query($query);
      if ($result) {
	$ids = array();
	while ($row = mysql_fetch_row($result)) {
	  $ids[] = $row[0];
	}
	foreach ($ids as $id) {
	  $votes[] = Vote::get_by_id($id);
	}
      } else {
	error_log(mysql_error() .": ". $query);
      }     
    }
    return $votes;    
  }

  static function find_latest_by_legislation($index = 0, $limit = PHP_INT_MAX) {
    require_once "db.inc.php";
    $query =<<<EOQ
SELECT MAX(id) AS mid FROM vote
GROUP BY bill_id
ORDER BY mid DESC
LIMIT {$index}, {$limit}
EOQ;
    $votes = array();
    $result = mysql_query($query);
    if ($result) {
      $ids = array();
      while ($row = mysql_fetch_row($result)) {
	$ids[] = $row[0];
      }
      foreach ($ids as $id) {
	$votes[] = Vote::get_by_id($id);
      }
    } else {
      error_log(mysql_error() .": ". $query);
    }     
    return $votes;    
  }

  static function find_by_legislation($bill_id) {
    require_once "db.inc.php";
    $query =<<<EOQ
SELECT * FROM vote
WHERE bill_id='{$bill_id}'
ORDER BY votedate DESC
EOQ;
    $votes = array();
    $result = mysql_query($query);
    if ($result) {
      while ($row = mysql_fetch_assoc($result)) {
	$vote = Vote::restore($row);
	$votes[] = $vote;
      }
      foreach ($votes as $vote) {
	$vote->votes = Vote::get_votes($vote->id);
	uksort($vote->votes, array($vote, "sort_votes"));
      }
    } else {
      error_log(mysql_error() .": ". $query);
    }     
    return $votes;
  }

  static function find_by_congress_session($congress, $session,
					   $index = 0, $limit = PHP_INT_MAX) {
    require_once "db.inc.php";
    $query =<<<EOQ
SELECT * FROM vote
WHERE congress={$congress}
AND session={$session}
ORDER BY votedate DESC
LIMIT {$index},{$limit}
EOQ;
    $votes = array();
    $result = mysql_query($query);
    if ($result) {
      while ($row = mysql_fetch_assoc($result)) {
	$vote = Vote::restore($row);
	$votes[] = $vote;
      }
      foreach ($votes as $vote) {
	$vote->votes = Vote::get_votes($vote->id);
	uksort($vote->votes, array($vote, "sort_votes"));
      }
    } else {
      error_log(mysql_error() .": ". $query);
    }     
    return $votes;    
  }

  static function find($congress, $session, $chamber, 
		       $index = 0, $limit = PHP_INT_MAX) {
    require_once "db.inc.php";
    $query =<<<EOQ
SELECT * FROM vote
WHERE congress={$congress}
AND session={$session}
AND chamber=UPPER('{$chamber}')
ORDER BY number DESC
LIMIT {$index},{$limit}
EOQ;
    $votes = array();
    $result = mysql_query($query);
    if ($result) {
      while ($row = mysql_fetch_assoc($result)) {
	$vote = Vote::restore($row);
	$votes[] = $vote;
      }
      foreach ($votes as $vote) {
	$vote->votes = Vote::get_votes($vote->id);
	uksort($vote->votes, array($vote, "sort_votes"));
      }
    } else {
      error_log(mysql_error() .": ". $query);
    }     
    return $votes;    
  }

  static function get_by_id($id) {
    require_once "db.inc.php";
    //// return the exact vote
    $query =<<<EOQ
SELECT * FROM vote
WHERE id={$id}
EOQ;
    $result = mysql_query($query);
    if ($result) {
      $row = mysql_fetch_assoc($result);
      $vote = Vote::restore($row);      
      $vote->votes = Vote::get_votes($vote->id);
      uksort($vote->votes, array($vote, "sort_votes"));
      return $vote;
    } else {
      error_log(mysql_error() .": ". $query);
    }     
    return null;
  }

  static function get_latest($congress, $session, $chamber) {
    require_once "db.inc.php";
    $query =<<<EOQ
SELECT * FROM vote
WHERE congress={$congress}
AND session={$session}
AND chamber=UPPER('{$chamber}')
ORDER BY number DESC
LIMIT 1
EOQ;
    $result = mysql_query($query);
    if ($result) {
      $row = mysql_fetch_assoc($result);
      $vote = Vote::restore($row);      
      $vote->votes = Vote::get_votes($vote->id);
      uksort($vote->votes, array($vote, "sort_votes"));
      return $vote;
    } else {
      error_log(mysql_error() .": ". $query);
    }     
    return null;
  }

  static function get($congress, $session, $chamber, $vote) {
    require_once "db.inc.php";
    //// return the exact vote
    $query =<<<EOQ
SELECT * FROM vote
WHERE congress={$congress}
AND session={$session}
AND chamber=UPPER('{$chamber}')
AND number={$vote}
EOQ;
    $result = mysql_query($query);
    if ($result) {
      $row = mysql_fetch_assoc($result);
      $vote = Vote::restore($row);      
      $vote->votes = Vote::get_votes($vote->id);
      uksort($vote->votes, array($vote, "sort_votes"));
      return $vote;
    } else {
      error_log(mysql_error() .": ". $query);
    }     
    return null;
  }

  function sort_votes($id1, $id2) {
    $vote1 = $this->votes[$id1];
    $vote2 = $this->votes[$id2];
    if ($vote1 == $vote2) {
      $rep1 = Rep::get($id1);
      $rep2 = Rep::get($id2);
      $party1 = $rep1->party;
      $party2 = $rep2->party;
      if ($party1 == $party2) {
	return strcmp($rep1->lastname, $rep2->lastname);
      }
      return strcmp($party1, $party2);
    }
    return strcmp($vote1, $vote2);
  }

  function save() {
    require_once "db.inc.php";
    $vd = date("Y-m-d H:i:s", $this->votedate);
    $question = htmlentities($this->question, ENT_QUOTES);
    $query =<<<EOQ
INSERT INTO vote
(congress, session, chamber, number, bill_id, amendment_id, question, 
 type, required, result, votedate)
VALUES
({$this->congress}, {$this->session}, UPPER('{$this->chamber}'), 
 {$this->number}, {$this->bill_id}, {$this->amendment_id},
 '{$question}', '{$this->type}', '{$this->required}', '{$this->result}', '{$vd}')
ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id),
bill_id=VALUES(bill_id), amendment_id=VALUES(amendment_id),
question=VALUES(question), type=VALUES(type), required=VALUES(required), 
result=VALUES(result), votedate=VALUES(votedate)
EOQ;
    $result = mysql_query($query);
    if ($result) {
      $lastId = mysql_insert_id();
      if ($lastId != 0) {
	$this->id = $lastId;
      }
    } else {
      error_log(mysql_error() .": ". $query);
      return;
    }

    foreach ($this->votes as $rep_id => $vote) {
      $query =<<<EOQ
INSERT INTO vote_rep
  (vote_id, rep_id, vote)
VALUES
  ({$this->id}, {$rep_id}, '{$vote}')
ON DUPLICATE KEY UPDATE
  vote_id=VALUES(vote_id), rep_id=VALUES(rep_id), vote=VALUES(vote)
EOQ;
      $result = mysql_query($query);
      if (!$result) {
	error_log(mysql_error() .": ". $query);
      }      
    }
  }

  function get_counts() {
    if (isset($this->counts)) {
      return $this->counts;
    }
    require_once "db.inc.php";
    $counts = array();
    $counts['totals'] = array();
    $query =<<<EOQ
SELECT rep.party, vote_rep.vote, count(*) 
FROM vote_rep 
JOIN rep ON vote_rep.rep_id=rep.id 
WHERE vote_rep.vote_id={$this->id} 
GROUP BY rep.party, vote_rep.vote
EOQ;
    $result = mysql_query($query);
    if ($result) {
      while ($row = mysql_fetch_row($result)) {
	if (!isset($counts[$row[0]])) {
	  $counts[$row[0]] = array();	    
	}
	$counts[$row[0]][$row[1]] = $row[2];
      }
    } else {
      error_log(mysql_error() .": ". $query);
    }    
    $query =<<<EOQ
SELECT vote_rep.vote, count(*) 
FROM vote_rep 
WHERE vote_rep.vote_id={$this->id} 
GROUP BY vote_rep.vote
WITH ROLLUP
EOQ;
    $result = mysql_query($query);
    if ($result) {
      while ($row = mysql_fetch_row($result)) {
	if (is_null($row[0])) {
	  $counts['totals']['total'] = $row[1];
	  continue;
	}
	if (!isset($counts['totals'][$row[0]])) {
	  $counts['totals'][$row[0]] = array();	    
	}
	$counts['totals'][$row[0]] = $row[1];
      }
    } else {
      error_log(mysql_error() .": ". $query);
    }    
    $this->counts = $counts;
    return $this->counts;
  }

  function get_chamber_name() {
    return ($this->chamber == 'S') ? "Senate" : "House";
  }

  function get_source_url() {
    if ($this->chamber == 'S') {
      $url = sprintf("http://senate.gov/legislative/LIS/roll_call_lists/roll_call_vote_cfm.cfm?congress=%d&session=%d&vote=%05d", 
		     $this->congress, $this->session, $this->number);
    }
    return $url;
  }

  function get_govtrack_url() {
    $url = sprintf("http://www.govtrack.us/congress/vote.xpd?vote=s%s-%d", 
		   date("Y", $this->votedate), $this->number);
    return $url;
  }

  function to_html() {
    $date = strftime("%B %d %Y %H:%M", $this->votedate);
    $result = <<<EOL
Id: {$this->id}<br />
Congress: {$this->congress}<br />
Session: {$this->session}<br />
Chamber: {$this->chamber}<br />
Number: {$this->number}<br />
Bill id: {$this->bill_id}<br />
Amendment id: {$this->amendment_id}<br />
Question: {$this->question}<br />
Type: {$this->type}<br />
Required: {$this->required}<br />
Result: {$this->result}<br />
Date: {$date} ($this->votedate)<br />
EOL;
    foreach ($this->votes as $rep => $vote) {
      $result = $result . $rep .": ". $vote ."<br />";
    }
    return $result;
  }
}

class Rep {
  public $id;
  public $bioguide_id;
  public $party;
  public $state;
  public $district;
  public $gender;
  public $title;
  public $firstname;
  public $middlename;
  public $lastname;
  public $name_suffix;
  public $nickname;
  public $phone;
  public $fax;
  public $website;
  public $webform;
  public $email;
  public $congress_office;
  public $twitter_id;
  public $youtube_url;

  static $cache;

  function get_party_full() {
    if ($this->party == 'D') {
      return 'Democrat';
    } else if ($this->party == 'R') {
      return 'Republican';
    } else if ($this->party == 'I') {
      return 'Independent';
    }
    return $this->party;
  }

  function get_title_full() {
    if ($this->title == 'Sen') {
      return 'Senator';
    } else if ($this->title == 'Rep') {
      return 'Representative';
    }
  }

  function get_state_full() {
    global $GEOIP_REGION_NAME;
    require 'geoip/geoipregionvars.php';
    if (isset($GEOIP_REGION_NAME['US'][$this->state])) {
      return $GEOIP_REGION_NAME['US'][$this->state];
    }
    return $this->state;
  }

  static function restore($row) {
    if (!isset(Rep::$cache)) {
      Rep::$cache = array();
    }
    $rep = new Rep();
    $rep->id = $row['id'];
    $rep->bioguide_id = $row['bioguide_id'];
    $rep->party = $row['party'];
    $rep->state = $row['state'];
    $rep->district = $row['district'];
    $rep->gender = $row['gender'];
    $rep->title = $row['title'];
    $rep->firstname = $row['firstname'];
    $rep->middlename = $row['middlename'];
    $rep->lastname = $row['lastname'];
    $rep->name_suffix = $row['name_suffix'];
    $rep->nickname = $row['nickname'];
    $rep->phone = $row['phone'];
    $rep->fax = $row['fax'];
    $rep->website = $row['website'];
    $rep->webform = $row['webform'];
    $rep->email = $row['email'];
    $rep->congress_office = $row['congress_office'];
    $rep->twitter_id = $row['twitter_id'];
    $rep->youtube_url = $row['youtube_url'];
    Rep::$cache[$rep->id] = $rep;
    return $rep;
  }

  static function get($id) {
    if (!isset(Rep::$cache)) {
      Rep::$cache = array();
    }
    if (isset(Rep::$cache[$id])) {
      return Rep::$cache[$id];
    }
    require_once "db.inc.php";
    $query = "SELECT * FROM rep WHERE id={$id}";
    $result = mysql_query($query);
    if ($result) {
      $row = mysql_fetch_assoc($result);
      $rep = Rep::restore($row);
      return $rep;
    }
    return null;
  }

  static function get_by_bioguide_id($id) {
    require_once "db.inc.php";
    $query = "SELECT * FROM rep WHERE bioguide_id='{$id}'";
    $result = mysql_query($query);
    if ($result) {
      $row = mysql_fetch_assoc($result);
      $rep = Rep::restore($row);
      return $rep;
    }
    return null;
  }

  function save() {
    require_once "db.inc.php";
    $query =<<<EOQ
INSERT INTO rep
(id, bioguide_id, party, state, district, gender, title, firstname, 
 middlename, lastname, name_suffix, nickname, phone, fax, website, webform, 
 email, congress_office, twitter_id, youtube_url)
VALUES
('{$this->id}', '{$this->bioguide_id}', UPPER('{$this->party}'), 
 UPPER('{$this->state}'), {$this->district}, UPPER('{$this->gender}'), 
 '{$this->title}', '{$this->firstname}', '{$this->middlename}', 
 '{$this->lastname}', '{$this->name_suffix}', '{$this->nickname}', 
 '{$this->phone}', '{$this->fax}', '{$this->website}', '{$this->webform}', 
 '{$this->email}', '{$this->congress_office}', '{$this->twitter_id}', 
 '{$this->youtube_url}')
ON DUPLICATE KEY UPDATE id=VALUES(id), bioguide_id=VALUES(bioguide_id), 
party=VALUES(party), state=VALUES(state), district=VALUES(district), 
gender=VALUES(gender), title=VALUES(title), firstname=VALUES(firstname), 
middlename=VALUES(middlename), lastname=VALUES(lastname), 
name_suffix=VALUES(name_suffix), nickname=VALUES(nickname), 
phone=VALUES(phone), fax=VALUES(fax), website=VALUES(website), 
webform=VALUES(webform), email=VALUES(email), 
congress_office=VALUES(congress_office), twitter_id=VALUES(twitter_id), 
youtube_url=VALUES(youtube_url)
EOQ;
    $result = mysql_query($query);
    if (!$result) {
      error_log(mysql_error() .": ". $query);
    }
  }

  function to_html() {
    return $this->lastname ." (". $this->party ."-". $this->state .")";
  }
}

class Record {
  public $id;
  public $congress;
  public $chamber;
  public $sequence;
  public $occurred;
  public $speaker_rep_id;
  public $bill_id;
  public $topic;
  public $speech;

  static function restore($row) {
    $record = new Record();
    $record->id = $row['id'];
    $record->congress = $row['congress'];
    $record->chamber = $row['chamber'];
    $record->sequence = $row['sequence'];
    $record->occurred = strtotime($row['occurred']);
    $record->speaker_rep_id = $row['speaker_rep_id'];
    $record->bill_id = $row['bill_id'];
    $record->topic = $row['topic'];
    $record->speech = $row['speech'];
    return $record;
  }

  function find_by_speaker($rep_id, $index=0, $limit=PHP_INT_MAX) {
    require_once 'db.inc.php';
    $query =<<<EOQ
SELECT * 
FROM record 
WHERE speaker_rep_id={$rep_id}
ORDER BY occurred DESC 
LIMIT {$index},{$limit}
EOQ;
    $result = mysql_query($query);
    $records = array();
    if ($result) {
      while ($row = mysql_fetch_assoc($result)) {
	$record = Record::restore($row);
	$records[] = $record;
      }
    } else {
      error_log(mysql_error() .": {$query}");
    }
    return $records;
  }

  function get($id) {
    require_once 'db.inc.php';
    $query =<<<EOQ
SELECT * 
FROM record 
WHERE id={$id}
EOQ;
    $result = mysql_query($query);
    if ($result) {
      if ($row = mysql_fetch_assoc($result)) {
	return Record::restore($row);
      }
    } else {
      error_log(mysql_error() .": {$query}");
    }
    return null;
  }

  function save() {
    require_once "db.inc.php";
    $occurred = date("Y-m-d H:i:s", $this->occurred);
    $topic = htmlentities($this->topic, ENT_QUOTES);
    $speech = htmlentities($this->speech, ENT_QUOTES);
    $query =<<<EOQ
INSERT INTO record
(congress, chamber, sequence, occurred, speaker_rep_id, bill_id, topic, speech)
VALUES
({$this->congress}, UPPER('{$this->chamber}'), {$this->sequence},
 '{$occurred}', {$this->speaker_rep_id}, {$this->bill_id},
 '{$topic}', '{$speech}')
ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), congress=VALUES(congress),
chamber=VALUES(chamber), sequence=VALUES(sequence), occurred=VALUES(occurred), 
speaker_rep_id=VALUES(speaker_rep_id), bill_id=VALUES(bill_id), 
topic=VALUES(topic), speech=VALUES(speech)
EOQ;
    $result = mysql_query($query);
    if (!$result) {
      error_log(mysql_error() .": ". $query);
    }
  }

  function get_speech_html() {
    $speech = html_entity_decode($this->speech, ENT_QUOTES);
    $speech = preg_replace(array('/\[Introducing [^\]]*\] /',
				 '/\<bill[^\>]*\>([^\<]*)\<\/bill\>/'),
			   array('', '$1'),
			   $speech);
    $speech = htmlentities($speech, ENT_QUOTES);
    $speech = str_replace(array('&lt;paragraph&gt;', '&lt;/paragraph&gt;'),
			  array('<p>', '</p>'),
			  $speech);
    return $speech;
  }

  function get_speech_excerpt_html() {
    $speech = $this->get_speech_html();
    if (strlen($speech) > 240) {
      $offset = min(240, strlen($speech));
      $speech = substr($speech, 0, $offset);
      $speech = $speech .'...</p>';
    }
    return $speech;
  }
}

class VoteStats {
  public $vote_id;
  public $dmajority;
  public $rmajority;

  function save() {
    require_once "db.inc.php";
    $query =<<<EOQ
INSERT INTO vote_stats
      (vote_id, dmajority, rmajority)
VALUES
      ({$this->vote_id}, '{$this->dmajority}', '{$this->rmajority}')
ON DUPLICATE KEY UPDATE 
      vote_id=VALUES(vote_id), dmajority=VALUES(dmajority), 
      rmajority=VALUES(rmajority)
EOQ;
    $result = mysql_query($query);
    if (!$result) {
      error_log(mysql_error() .": ". $query);
    }
  }
}