Source for file ResultsDB.inc

<?php
  /**
   * Result Tracker Module Database API.
   * This file is part of CompInaBox.
   * @copyright Copyright 2001-2005. Eric D. Nielsen, All rights reserverd.
   * @license http://opensource.org/licenses/gpl-license.php GNU Public License
   * @author Eric D. Nielsen <nielsene@alum.mit.edu>
   *
   * @package Database
   */
 
  /** 
   * Result Tracker Module Database API.
   * This class presents a OOP interface to the common
   * queries needed by Result Tracker Module functionality.
   * @todo Further tease out SQL to the {@link PhraseBook}.  The ResultsDBDB
   *  class should evolve into the functional API -- it needs more
   *  parameter checking and more consistency of return values to
   *  be useful.  The places where a SQL statement was wrapped up for use
   *  here, but is only used by a single location in the code, might get
   *  "demoted" to pure PhraseBook status, etc.
   * @package Database
   */
class ResultsDB extends CIB_DB
{
  
  /**
   * ResultsDB Constructor.
   * Simply call the parent constructor.
   * @todo Add a check after connecting to make sure its a central DB and
   *  not a per-comp DB
   * @access public
   *
   * @param string $dbname what database do you want to connect to
   * @param string $user as what user
   * @param string $pass which what password
   */
  function ResultsDB($dbname, $user, $pass)
    {
      CIB_DB::CIB_DB($dbname, $user, $pass);
    }
  
 
  /**
   * Return a list of possible id matches for a given name.
   * Given a name, attempts to find matching people.  Assumes "firstname
   * lastname" order.  If ony one components, tries both.  If multiple
   * components (3 or more) tries splitting into first and last sections
   * at each space.  Case insenitive.
   *
   * @access public
   * @param string $name the name to find IDs for
   * @return array a list of peopleIDs from the database
   * @todo Look at reconcilling with the current Prime/Partner matching code,
   *  but remember these touch different schema databases.
   */
  function getPeopleByName($name)
    {
      $ids = array();
      $nameComponents = explode(" ",trim($name));
      $numComponents = count($nameComponents);
      if ($numComponents==2)
    {
      $query = "SELECT peopleid FROM people WHERE lower(firstname)=lower('{$nameComponents[0]}') AND lower(lastname)=lower('{$nameComponents[1]}');";
      $result = $this->query($query);
      $numrows = $result->numrows();
      for ($i=0;$i<$numrows;$i++)
        {
          list($ids[])=$result->getRowAt($i);
        }
    }
      else if ($numComponents>2)
    {
      $seperator=1;
      while ($seperator < $numComponents)
        {
          $firstname=""; $lastname="";
          for ($i=0;$i<$numComponents;$i++)
        if ($i<$seperator) {
          if ($firstname!="") $firstname.= " ";
          $firstname.=$nameComponents[$i];
        } else {
          if ($lastname!="") $lastname.= " ";
          $lastname.=$nameComponents[$i];
        }
          $query = "SELECT peopleid FROM people WHERE
lower(firstname)=lower('$firstname') AND lower(lastname)=lower('$lastname');";
          $result = $this->query($query);
          $numrows = $result->numrows();
          for ($i=0;$i<$numrows;$i++)
        {
          list($tempID)=$result->getRowAt($i);
          $ids[]=$tempID;
        }
          $seperator++;
        }
    }
      else 
    {
      $query = "SELECT peopleid FROM people WHERE lower(firstname)=lower('{$nameComponents[0]}') OR lower(lastname)=lower('{$nameComponents[0]}');";
      $result = $this->query($query);
      $numrows = $result->numrows();
      for ($i=0;$i<$numrows;$i++)
        {
          list($ids[])=$result->getRowAt($i);
        }
    }
      return $ids;
    }
 
  /**
   * Returns a list of details about all requested IDs
   * Returns the name, hometown (if entered), current team memberships,
   * and any past team memberships known for each individuals in $personIDs
   * @access public
   * @param array $personIDs Array of peopleIDs from the DB
   * @return array an array of arrays, each item is formatted as:
   *  - "FirstName"=>firstname,
   *  - "LastName"=>lastname,
   *  - "City"=>hometwon, only if present in DB
   *  - "State"=>home state, only if present in DB
   *  - "Teams"=>array of team abbreviations (strings) only if any
   *     memberships found
   *  - "DancedAs"=>array of team abbreviations (string) from result list, only
   *    if any found
   *  Each subitem is keyed by the the associated personID
   * @todo remove duplicate entries from DanceAs that also appear in Teams, to
   *  show only historic values in DanceAs
   */
  function getIdentificationByID($personIDs)
    {
      if (!is_array($personIDs)) $personIDs = array($personIDs);
      $details = array();
      foreach ($personIDs as $pID)
    {
      $aDetail=array();
      $query = "SELECT firstname, lastname FROM people WHERE peopleid = $pID;";
      $result = $this->query($query);
      if ($result->numrows()>0) {
        list($aDetail["FirstName"],$aDetail["LastName"]) =
          $result->getRowAt(0);
      }
      $query = "SELECT city,state FROM people_addresses WHERE peopleid = $pID;";
      $result = $this->query($query);
      if ($result->numrows()>0) {
        list($aDetail["City"],$aDetail["State"]) =
          $result->getRowAt(0);
      }
      $query = "SELECT abbreviation FROM teams NATURAL JOIN team_membership WHERE peopleid = $pID;";
      $result = $this->query($query);
      $numteams = $result->numrows();
      $teams=array();
      for ($i=0;$i<$numteams;$i++)
        {
          list($teams[]) = $result->getRowAt($i);
        }
      $aDetail["Teams"]=$teams;
 
      $query =<<<END_QUERY
SELECT abbreviation FROM teams, result_couples
WHERE (leader=$pID AND leader_team=teamid) OR (follower=$pID AND follower_team=teamid);
END_QUERY;          $result = $this->query($query);
      $numteams = $result->numrows();
      $dancedAs=array();
      for ($i=0;$i<$numteams;$i++)
        {
          list($dancedAs[]) = $result->getRowAt($i);
        }
      $aDetail["DancedAs"]=$dancedAs;
      $details[$pID]=$aDetail;
    }
      return $details;
    }
 
  /**
   * Returns an exhaustive set of placement information.
   * Returns a complete (from the Result Tracker's knowledge base
   * perspective) set of results for the requested person.  The returned
   * strcuture is normally fed into the display rountines which
   * tabulate points.
   *
   * @access public
   * @param int $pID a peopleid to look up results for
   * @return array A deeply nested array whos details are highly likely
   *  to be refactored soon.
   * @todo Refactor results array into a new class
   */
  function getResultsFor($pID)
    {
      $placements=array(); 
      $query =<<<END_QUERY
SELECT eventid, compname, compunix, getformatteddate as date, 
       stylename, s.eventshortprefix, s.eventfullprefix,
       levelname, l.eventshortprefix, age_level, 
       placement, num_cuts, leader, follower,    
    size, rounds
  FROM comp_events_placements NATURAL JOIN result_couples NATURAL JOIN
       comp_events NATURAL JOIN 
       comp_events_size NATURAL JOIN
       all_comps_view NATURAL JOIN
       comp_events_cuts JOIN 
       comp_styles AS s USING (compunix, stylename) JOIN
       comp_levels AS l USING (compunix, levelname)
WHERE (leader=$pID or follower=$pID) and classification='Amateur/Amateur'
ORDER BY getfirstdate ASC;
END_QUERY;      $result = $this->query($query);
      $numPlacements = $result->numrows();
      for ($i=0;$i<$numPlacements;$i++)
    {
      list($eventID, $comp, $compunix, $compDate, $style, $sAbbrev, $sFull,
           $level, $lAbbrev,
           $age, $place, $cuts, 
           $leader, $follower,
           $numCouples,$numRounds) = $result->getRowAt($i);
      if ($place==0) $place="";
      if (!in_array($age,array_keys($placements))) 
            $placements[$age]=array();
      if (!in_array($level,array_keys($placements[$age])))
        $placements[$age][$level]=array();
      if (!in_array($style,array_keys($placements[$age][$level])))
        $placements[$age][$level][$style]=array();
      
      $partnerID = ($leader==$pID ? $follower : $leader);
      $query = "SELECT firstname, lastname FROM people WHERE peopleid=$partnerID;";
      $partnerResult = $this->query($query);
      list($pFirst, $pLast) = $partnerResult->getRowAt(0);
      $partnerName = "$pFirst $pLast";
      $query =<<< END_QUERY
SELECT DISTINCT dancename, danceabbrev, d.danceorder 
  FROM comp_events_dances AS d JOIN
       comp_style_dances AS s USING(dancename) 
 WHERE eventid=$eventID AND stylename='$style'
 ORDER BY d.danceorder;
END_QUERY;      $danceResult = $this->query($query);
      $numDances = $danceResult->numrows();
      $dances="";
      $danceAbbrev="";
      for ($j=0;$j<$numDances;$j++)
        {
          list($dance,$dAbbrev)=$danceResult->getRowAt($j);
          if ($j!=0)
        $dances .= "/ ";
          $dances.=$dance;
          $danceAbbrev.=$dAbbrev;
        }
      for ($j=0;$j<$numDances;$j++)
        {
          list($dance)=$danceResult->getRowAt($j);
          if (!in_array($dance,array_keys($placements[$age][$level][$style])))
        $placements[$age][$level][$style][$dance]=array();
          $entry = array("Comp"=>$comp, 
                 "CompUnix"=>$compunix,
                 "Date"=>$compDate, 
                 "Event"=>"$level $sFull $dances",
                 "EventShort"=>"$lAbbrev-$sAbbrev-$danceAbbrev",
                 "Partner"=>$partnerName,
                 "PartnerID"=>$partnerID,
                 "Place"=>$place,
                 "CoupleCuts"=>$cuts,
                 "EventSize"=>$numCouples,
                 "EventRounds"=>$numRounds);
          $placements[$age][$level][$style][$dance][]=$entry;
        }
    }          
      return $placements;
    }
 
  /**
   * Returns an exhaustive set of entry information.
   * Returns a complete (from the Result Tracker's knowledge base
   * perspective) set of entriess for the requested person.  The returned
   * strcuture is normally fed into the display rountines which
   * tabulate points.
   *
   * @access public
   * @param int $pID a peopleid to look up results for
   * @return array A deeply nested array whos details are highly likely
   *  to be refactored soon.
   * @todo Refactor results array into a new class
   */
  function getAllEntriesFor($pID)
    {
      $placements=array(); 
      $query =<<<END_QUERY
SELECT eventid, compname, compunix, getformatteddate as date, 
       stylename, s.eventshortprefix, s.eventfullprefix,
       levelname, l.eventshortprefix, age_level, 
       COALESCE(placement,0), num_cuts, leader, follower,    
    size, rounds
  FROM comp_events_cuts NATURAL JOIN result_couples NATURAL JOIN
       comp_events NATURAL JOIN 
       comp_events_size NATURAL JOIN
       all_comps_view LEFT JOIN
       comp_events_placements USING (eventid, coupleid) JOIN 
       comp_styles AS s USING (compunix, stylename) JOIN
       comp_levels AS l USING (compunix, levelname)
WHERE (leader=$pID or follower=$pID) and classification='Amateur/Amateur'
ORDER BY getfirstdate ASC;
END_QUERY;      $result = $this->query($query);
      $numPlacements = $result->numrows();
      for ($i=0;$i<$numPlacements;$i++)
    {
      list($eventID, $comp, $compunix, $compDate, $style, $sAbbrev, $sFull,
           $level, $lAbbrev,
           $age, $place, $cuts, 
           $leader, $follower,
           $numCouples,$numRounds) = $result->getRowAt($i);
      if ($place==0) $place="";
      if (!in_array($age,array_keys($placements))) 
            $placements[$age]=array();
      if (!in_array($level,array_keys($placements[$age])))
        $placements[$age][$level]=array();
      if (!in_array($style,array_keys($placements[$age][$level])))
        $placements[$age][$level][$style]=array();
      
      $partnerID = ($leader==$pID ? $follower : $leader);
      $query = "SELECT firstname, lastname FROM people WHERE peopleid=$partnerID;";
      $partnerResult = $this->query($query);
      list($pFirst, $pLast) = $partnerResult->getRowAt(0);
      $partnerName = "$pFirst $pLast";
      $query =<<< END_QUERY
SELECT DISTINCT dancename, danceabbrev, d.danceorder 
  FROM comp_events_dances AS d JOIN
       comp_style_dances AS s USING(dancename) 
 WHERE eventid=$eventID AND stylename='$style'
 ORDER BY d.danceorder;
END_QUERY;      $danceResult = $this->query($query);
      $numDances = $danceResult->numrows();
      $dances="";
      $danceAbbrev="";
      for ($j=0;$j<$numDances;$j++)
        {
          list($dance,$dAbbrev)=$danceResult->getRowAt($j);
          if ($j!=0)
        $dances .= "/ ";
          $dances.=$dance;
          $danceAbbrev.=$dAbbrev;
        }
      for ($j=0;$j<$numDances;$j++)
        {
          list($dance)=$danceResult->getRowAt($j);
          if (!in_array($dance,array_keys($placements[$age][$level][$style])))
        $placements[$age][$level][$style][$dance]=array();
          $entry = array("Comp"=>$comp, 
                 "CompUnix"=>$compunix,
                 "Date"=>$compDate, 
                 "Event"=>"$level $sFull $dances",
                 "EventShort"=>"$lAbbrev-$sAbbrev-$danceAbbrev",
                 "Partner"=>$partnerName,
                 "PartnerID"=>$partnerID,
                 "Place"=>$place,
                 "CoupleCuts"=>$cuts,
                 "EventSize"=>$numCouples,
                 "EventRounds"=>$numRounds);
          $placements[$age][$level][$style][$dance][]=$entry;
        }
    }          
      return $placements;
    }
 
  /**
   * Returns an person's name.
   * @access public
   * @param int $pid a peopleid to look up for
   * @return string "Firstname Lastname"
   * @todo Look into a CIB_Person Mapper (light weight compared to the
   *  Ragu_Person)
   */
  function getPersonName($pid)
    {
      $query = "SELECT firstname, lastname FROM people WHERE peopleid=$pid;";
      $result = $this->query($query);
      list($fname,$lname) = $result->getRowAt(0);
      $name = "$fname $lname";
      return $name;
    }
 
  /**
   * Returns an person's hometwon or "No adddress lists".
   * @access public
   * @param int $pid a peopleid to look up for
   * @return string "City, State"
   * @todo Look into a CIB_Person Mapper (light weight compared to the
   *  Ragu_Person)
   */
  function getHomeTown($pid)
  {
    $query = "SELECT city, state FROM people_addresses WHERE peopleid=$pid;";
    $result = $this->query($query);
    if ($result->numrows())
      {
    list($city,$state) = $result->getRowAt(0);
    $addr = "$city, $state";
      }
    else
      $addr = "No address listed";
    return $addr;
  }
  
  /**
   * Returns an person's team memberships
   * @access public
   * @param int $pid a peopleid to look up for
   * @return string "Team[, Team2[, Team3...]]"
   * @todo Look into a CIB_Person Mapper (light weight compared to the
   *  Ragu_Person)
   */
  function getAffiliation($pid)
  {
    $query = "SELECT name FROM team_membership NATURAL JOIN teams WHERE peopleid=$pid;";
    $result = $this->query($query);
    $numRows = $result->numrows();
    if ($numRows==0)
      $team = "No listed affiliation";
    else
      $team="";
    for ($i=0;$i<$numRows;$i++)
      {
    if ($i!=0)
      $team .= ", ";
    list($aTeam) = $result->getRowAt($i);
    $team .= $aTeam;
      }
    return $aTeam;
  }
  
  /**
   * Returns a list of all competitions a person has competed in.
   * @access public
   * @param int $personID a peopleid to look up for
   * @return array array of arrays, each numerically index subitem:
   *  - "Comp"=>Full Competition Name
   *  - "Unix"=>Unix Namee of Competition
   *  - "Date"=>Formatted date(s) of the competition
   *  The subitems are entered in oldest first order.
   */
  function getCompHistory($personID)
  {
    $query =<<<END_QUERY
SELECT DISTINCT compname, compunix,
       getformatteddate AS compDate, 
       getfirstdate AS sortDate
 FROM comp_events_cuts NATURAL JOIN result_couples NATURAL JOIN
      comp_events NATURAL JOIN all_comps_view
WHERE leader=$personID or follower=$personID
ORDER BY sortDate;
END_QUERY;    $result=$this->query($query);
    $numComps = $result->numrows();
    $comps=array();
    for ($i=0;$i<$numComps;$i++)
      {
    list($compname, $compunix, $compDate) = $result->getRowAt($i);
    $comps[]=array("Comp"=>$compname,
               "Unix"=>$compunix,
               "Date"=>$compDate);
      }
    return $comps;
  }
  
}
?>