<?php

###########################################################################
# MySQL handling
#
# 2003-03-20 Stagnate
# * Moved user/pass/db/host into variables.inc
#
# 2003-03-16 Stagnate
# * Created file, MySQL variables and link, messageQuery function
#
# 2003-04-01 Stagnate
# * Added option to messageQuery function to handle multiple areas
#
# 2003-04-02 Stagnate
# * Added runQuery to consolidate query error handling
#
# 2003-04-07 Stagnate
# * Fixed messageQuery to sort by date _desc_
#
# 2003-04-12 Stagnate
# * Added forumQuery function for grabbing forum messages
#
# 2003-05-17 Stagnate
# * Added scheduleQuery function, removed forumQuery.
#
# 2003-05-22 Stagnate
# * Added rateUpdate function
###########################################################################

###########################################################################
# MySQL initialization
###########################################################################

$link = mysql_connect($dbhost, $dbuser, $dbpass)
        or die('Unable to connect to DB Server!');
mysql_select_db($dbname) or die('Unable to select DB!');

###########################################################################
# MySQL query functions
###########################################################################

function messageQuery ($type, $date_format, $expired, $offset, $count) {

###########################################################################
# $type is type string from message_type table, comma delimited
# $date_format is the MySQL date format string for the message create date
# $expired is boolean; set true to show expired messages
# $offset and $count are used for the LIMIT statement
#
# Returns a result handle with all message columns, poster's primary
# alias (C_PrimaryAlias) and I_UserID, and formatted date as D_Create_format
###########################################################################

  global $debug;

  # Surround each type with quotes for use with the 'IN' statement
  $mtype = "'" . preg_replace('/,/',"','",$type) . "'";
  $query = "SELECT message.*, C_PrimaryAlias,
                   DATE_FORMAT(message.D_Create, '$date_format')
                       as D_Create_format
              FROM message INNER JOIN message_type as mtype
                   ON message.I_MessageTypeID = mtype.I_MessageTypeID
                           INNER JOIN user
                   ON message.I_UserID = user.I_UserID
             WHERE C_MessageType IN ($mtype)";
  if (!$expired) { $query .= ' AND (D_Expire > NOW() OR D_Expire IS NULL)'; }
  $query .= " ORDER BY I_Priority desc, D_Create desc LIMIT $offset,$count";
  $result = runQuery($query,'mysql.inc - messageQuery');
  return $result;
}

function rateUpdate ($league, $rater, $user, $rating, $comment) {

###########################################################################
# $league is the league ID the rating applies to
# $rater is the user ID of the person rating
# $user is the user ID of the person being rated
# $rating is the rating value
# $comment is any additional info
#
# IMPORTANT: This function does not handle security!  Make sure you have 
#            validated access permission before calling this function.
# This function will add a rating entry, or update the entry if it already
# exists, but only if the rating has not yet been finalized (added to the
# user_league_link table).
###########################################################################

  $query = "SELECT * FROM rating
             WHERE I_RaterID = $rater
               AND I_UserID = $user
               AND I_LeagueID = $league";
  $exist_result = runQuery($query, 'mysql.inc - existing rate query');
  switch (mysql_num_rows($exist_result)) {
    case 0:
      $query = "INSERT INTO rating (I_RaterID, I_UserID, I_LeagueID, I_Rating,
                                    C_Comment)
                     VALUES ($rater, $user, $league, $rating, '$comment')";
      runQuery($query, 'mysql.inc - insert rating query');
      break;

    case 1:
      # Allow them to modify ratings.
      # First check if rating has already been set.
      #$query = "SELECT * FROM user_league_link
      #           WHERE I_UserID = $user AND I_LeagueID = $league
      #             AND I_Rating IS NOT NULL AND I_Rating > 0";
      #$set_result = runQuery($query, 'mysql.inc - rating set query');
      #if (mysql_num_rows($set_result) > 0) {
      #  die("User($user) rating has already been set!");
      #}
      
      $query = "UPDATE rating SET I_Rating = $rating
                 WHERE I_RaterID = $rater
                   AND I_LeagueID = $league
                   AND I_UserID = $user";
      runQuery($query, 'mysql.inc - update rating query');
      break;

    default:
      die("Error: user $user, rater $rater, league $league, rating $rating");
  }
}

function scheduleQuery ($I_SeasonID, $display, $interval, $date_format) {

###########################################################################
# $I_SeasonID is the league I_SeasonID to display, 0 for all active leagues
# $display sets the dates from which to query:
#    'all' - Query all games
#    'future' - Query unplayed games
#    'interval' - Query games over an interval from now until $interval
# $interval is a MySQL interval string (eg '7 DAY')
# $date_format is a MySQL date formating string used to display game date.
#
# Returns a result handle with home.*, visitor.*, I_Round, C_League,
# C_Segment, I_SeriesGames, game_date (formatted via $date_format)
###########################################################################

  $query = "SELECT home.I_TeamID as homeid, home.C_Team as hometeam,
                   visitor.I_TeamID as visitorid, visitor.C_Team as visitorteam,
                   C_Segment, D_Schedule, I_Round, C_League, I_SeriesGames,
                   Date_Format('$date_format', D_Schedule) as date_format
              FROM game INNER JOIN segment
                    ON game.I_SegmentID = segment.I_SegmentID
                        INNER JOIN season
                    ON segment.I_SeasonID = season.I_SeasonID
                        INNER JOIN league
                    ON season.I_LeagueID = league.I_LeagueID
                        INNER JOIN team_game_link homelink
                    ON game.I_GameID = homelink.I_GameID
                        INNER JOIN team_game_link visitorlink
                    ON game.I_GameID = visitorlink.I_GameID
                        INNER JOIN team home
                    ON homelink.I_TeamID = home.I_TeamID
                        INNER JOIN team visitor
                    ON visitorlink.I_TeamID = visitor.I_TeamID
             WHERE homelink.I_Home = 1 AND visitorlink.I_Home = 0";
  if ($I_SeasonID > 0) {
    $query .= " AND season.I_SeasonID = $I_SeasonID";
  }
  switch ($display) {
    case 'future':
            $query .= " AND D_Schedule >= CURRENT_DATE";
            break;

    case 'interval':
            $query .= " AND D_Schedule BETWEEN
                       (CURRENT_DATE) AND (CURRENT_DATE + INTERVAL $interval)";
            break;
  }
  $query .= " ORDER BY D_Schedule, season.I_SeasonID, home.I_TeamID, visitor.I_TeamID";

  $result = runQuery($query, 'mysql.inc - scheduleQuery');
  return($result);
}

function runQuery ($query, $code) {

###########################################################################
# Takes query string, runs query.  If debug mode is on prints out query
# and error string, otherwise dies with generic error.
#
# $query is the MySQL query string
# $code is the code reference that will be printed with error messages
###########################################################################

  global $debug;

  if (!($result = mysql_query($query))) {
    if($debug) {
      die("Location: $code<BR>Query: $query<BR>Error: " . mysql_error());
    } else {
      die("DB Query failed, please contact the system administrator.");
    }
  } else {
    return $result;
  }
}