PHP JoomleagueHelper::convertMatchDateToTimezone方法代码示例

 public function getData()
 {
     $data = parent::getData();
     if ($data) {
         foreach ($data as $match) {
             JoomleagueHelper::convertMatchDateToTimezone($match);
         }
     }
     return $data;
 }

    /**
     * Method to get the list
     *
     * @access public
     * @return array
     */
    function getData(&$params)
    {
        $usedp = $params->get('projects', '0');
        $usedpid = $params->get('playground', '0');
        $projectstring = is_array($usedp) ? implode(",", $usedp) : $usedp;
        $playgroundstring = is_array($usedpid) ? implode(",", $usedpid) : $usedpid;
        $numberofmatches = $params->get('maxmatches', '5');
        $db = JFactory::getDbo();
        $result = array();
        $query = 'SELECT  m.match_date, DATE_FORMAT(m.time_present, "%H:%i") time_present,
                          p.name AS project_name, p.id AS project_id, p.timezone, 
				 tj1.team_id team1, tj2.team_id team2, lg.name AS league_name,
			     plcd.id AS club_playground_id, 
			     plcd.name AS club_playground_name,
			     pltd.id AS team_playground_id, 
			     pltd.name AS team_playground_name, 
			     pl.id AS playground_id, 
			     pl.name AS playground_name,
			     t1.name AS team1_name,
			     t2.name AS team2_name
                      FROM #__joomleague_match AS m
                      INNER JOIN #__joomleague_project_team tj1 ON tj1.id = m.projectteam1_id 
                      INNER JOIN #__joomleague_project_team tj2 ON tj2.id = m.projectteam2_id 
                      INNER JOIN #__joomleague_project AS p ON p.id=tj1.project_id
                      INNER JOIN #__joomleague_team t1 ON t1.id = tj1.team_id
		      INNER JOIN #__joomleague_team t2 ON t2.id = tj2.team_id
                      INNER JOIN #__joomleague_club c ON c.id = t1.club_id
		      INNER JOIN #__joomleague_league lg ON lg.id = p.league_id
		      LEFT JOIN #__joomleague_playground AS plcd ON c.standard_playground = plcd.id
		      LEFT JOIN #__joomleague_playground AS pltd ON tj1.standard_playground = pltd.id 
		      LEFT JOIN #__joomleague_playground AS pl ON m.playground_id = pl.id

                      WHERE (m.playground_id IN (' . $playgroundstring . ')
                          OR (tj1.standard_playground IN (' . $playgroundstring . ') AND m.playground_id IS NULL)
                          OR (c.standard_playground IN (' . $playgroundstring . ') AND (m.playground_id IS NULL AND tj1.standard_playground IS NULL )))
                      AND m.match_date > NOW()
                      AND m.published = 1
                      AND p.published = 1';
        if ($projectstring != 0) {
            $query .= ' AND p.id IN (' . $projectstring . ')';
        }
        $query .= " ORDER BY m.match_date ASC LIMIT " . $numberofmatches;
        $db->setQuery($query);
        $info = $db->loadObjectList();
        if ($info) {
            foreach ($info as $match) {
                JoomleagueHelper::convertMatchDateToTimezone($match);
            }
        }
        return $info;
    }

    function &getMatch()
    {
        if (is_null($this->match)) {
            $query = 'SELECT m.*,DATE_FORMAT(m.time_present,"%H:%i") time_present, r.project_id, p.timezone 
					FROM #__joomleague_match AS m 
					INNER JOIN #__joomleague_round AS r on r.id=m.round_id 
					INNER JOIN #__joomleague_project AS p on r.project_id=p.id 
					WHERE m.id=' . $this->_db->Quote($this->matchid);
            $this->_db->setQuery($query, 0, 1);
            $this->match = $this->_db->loadObject();
            if ($this->match) {
                JoomleagueHelper::convertMatchDateToTimezone($this->match);
            }
        }
        return $this->match;
    }

 function &getMatch()
 {
     if (is_null($this->match)) {
         $db = JFactory::getDbo();
         $query = $db->getQuery(true);
         $query->select(array('m.*', 'DATE_FORMAT(m.time_present,"%H:%i") AS time_present', 'r.project_id', 'p.timezone'));
         $query->from('#__joomleague_match AS m');
         $query->join('INNER', '#__joomleague_round AS r on r.id = m.round_id');
         $query->join('INNER', '#__joomleague_project AS p on r.project_id=p.id');
         $query->where('m.id = ' . $db->Quote($this->matchid));
         $db->setQuery($query, 0, 1);
         $this->match = $db->loadObject();
         if ($this->match) {
             JoomleagueHelper::convertMatchDateToTimezone($this->match);
         }
     }
     return $this->match;
 }

 /**
  * get games
  * @return array
  */
 function getMatches()
 {
     if (is_null($this->matches)) {
         $this->matches = $this->getResultsRows($this->roundid, $this->divisionid, $this->config);
     }
     $allowed = $this->isAllowed();
     $user = JFactory::getUser();
     if (count($this->matches) > 0) {
         foreach ($this->matches as $k => $match) {
             JoomleagueHelper::convertMatchDateToTimezone($match);
             if ($match->checked_out == 0 || $match->checked_out == $user->id) {
                 if ($allowed || $this->isMatchAdmin($match->id)) {
                     $this->matches[$k]->allowed = true;
                 }
             }
         }
     }
     return $this->matches;
 }

 function _getResultsPlan($projectid = 0, $teamid = 0, $divisionid = 0, $playgroundid = 0, $ordering = 'ASC')
 {
     $matches = array();
     $query = ' SELECT m.projectteam1_id, m.projectteam2_id, m.match_date,' . ' 	p.name AS project_name, p.timezone,' . ' 	t1.id AS team1, t2.id AS team2,' . ' 	r.roundcode, r.name, r.project_id,' . ' 	plcd.id AS club_playground_id, plcd.name AS club_playground_name,' . ' pltd.id AS team_playground_id, pltd.name AS team_playground_name,' . ' pl.id AS playground_id, pl.name AS playground_name,' . ' plcd.address AS club_playground_address,' . ' plcd.zipcode AS club_playground_zipcode,' . ' plcd.city AS club_playground_city,' . ' pltd.address AS team_playground_address,' . ' pltd.zipcode AS team_playground_zipcode,' . ' pltd.city AS team_playground_city,' . ' pl.address AS playground_address,' . ' pl.zipcode AS playground_zipcode,' . ' pl.city AS playground_city' . ' FROM #__joomleague_match AS m' . ' INNER JOIN #__joomleague_round r ON m.round_id = r.id' . ' INNER JOIN #__joomleague_project_team AS pt1 ON m.projectteam1_id = pt1.id' . ' INNER JOIN #__joomleague_team AS t1 ON t1.id = pt1.team_id' . ' INNER JOIN #__joomleague_project_team AS pt2 ON m.projectteam2_id = pt2.id' . ' INNER JOIN #__joomleague_team AS t2 ON t2.id = pt2.team_id' . ' INNER JOIN #__joomleague_project AS p ON p.id = r.project_id' . ' INNER JOIN #__joomleague_club c ON c.id = t1.club_id' . ' LEFT JOIN #__joomleague_playground AS pl ON pl.id = m.playground_id' . ' LEFT JOIN #__joomleague_playground AS plcd ON c.standard_playground = plcd.id' . ' LEFT JOIN #__joomleague_playground AS pltd ON pt1.standard_playground = pltd.id' . ' WHERE m.published = 1 AND p.published = 1';
     if ($projectid != 0) {
         $query .= " AND r.project_id = '" . $projectid . "'";
     }
     if ($teamid != 0) {
         $query .= " AND (t1.id = " . $teamid . " OR t2.id = " . $teamid . ")";
     }
     if ($playgroundid != 0) {
         $query .= ' AND ( m.playground_id = "' . $playgroundid . '"' . ' OR (pt1.standard_playground = "' . $playgroundid . '" AND m.playground_id IS NULL)' . ' OR (c.standard_playground = "' . $playgroundid . '" AND (m.playground_id IS NULL AND pt1.standard_playground IS NULL  )))' . ' AND m.match_date > NOW()';
     }
     $query .= ' GROUP BY m.id' . " ORDER BY r.roundcode " . $ordering . ", m.match_date, m.match_number";
     $this->_db->setQuery($query);
     $matches = $this->_db->loadObjectList();
     if ($matches) {
         foreach ($matches as $match) {
             JoomleagueHelper::convertMatchDateToTimezone($match);
         }
     }
     return $matches;
 }

 function getNextGames($project_id = 0, $bShowReferees = 0)
 {
     $result = array();
     $playground = $this->getPlayground();
     if ($playground->id > 0) {
         $query = "SELECT m.*, DATE_FORMAT(m.time_present, '%H:%i') time_present,\n                             p.name AS project_name, p.timezone, tj.team_id team1, tj2.team_id team2\n                      FROM #__joomleague_match AS m\n                      INNER JOIN #__joomleague_project_team tj ON tj.id = m.projectteam1_id \n                      INNER JOIN #__joomleague_project_team tj2 ON tj2.id = m.projectteam2_id \n                      INNER JOIN #__joomleague_project AS p ON p.id=tj.project_id\n                      INNER JOIN #__joomleague_team t ON t.id = tj.team_id\n                      INNER JOIN #__joomleague_club c ON c.id = t.club_id\n                      WHERE (m.playground_id= " . (int) $playground->id . "\n                          OR (tj.standard_playground = " . (int) $playground->id . " AND m.playground_id IS NULL)\n                          OR (c.standard_playground = " . (int) $playground->id . " AND m.playground_id IS NULL))\n                      AND m.match_date > NOW()\n                      AND m.published = 1\n                      AND p.published = 1";
         if ($project_id > 0) {
             $query .= " AND project_id= " . (int) $project_id;
         }
         $query .= " GROUP BY m.id ORDER BY match_date ASC";
         $this->_db->setQuery($query);
         $matches = $this->_db->loadObjectList();
         if ($matches) {
             foreach ($matches as $match) {
                 JoomleagueHelper::convertMatchDateToTimezone($match);
             }
         }
         if ($bShowReferees > 0) {
             $project = $this->getProject();
             $this->_getRefereesByMatch($matches, $project);
         }
     }
     return $matches;
 }

 /**
  *
  * @param int $project_id
  * @param int $excludeMatchId
  */
 function getMatchRelationsOptions($project_id, $excludeMatchId = 0)
 {
     $query = ' SELECT	m.id AS value,' . ' m.match_date, p.timezone, t1.name AS t1_name, t2.name AS t2_name' . '	FROM #__joomleague_match AS m' . ' INNER JOIN #__joomleague_project_team AS pt1 ON m.projectteam1_id=pt1.id' . '	INNER JOIN #__joomleague_project_team AS pt2 ON m.projectteam2_id=pt2.id' . ' INNER JOIN #__joomleague_team AS t1 ON pt1.team_id=t1.id' . ' INNER JOIN #__joomleague_team AS t2 ON pt2.team_id=t2.id' . ' INNER JOIN #__joomleague_project AS p ON p.id=pt1.project_id' . ' WHERE pt1.project_id=' . $this->_db->Quote($project_id) . '   AND m.id NOT in (' . $excludeMatchId . ')' . '   AND m.published=1' . ' ORDER BY m.match_date DESC,t1.short_name';
     $this->_db->setQuery($query);
     $matches = $this->_db->loadObjectList();
     if ($matches) {
         foreach ($matches as $match) {
             JoomleagueHelper::convertMatchDateToTimezone($match);
         }
     }
     return $matches;
 }

 function getMatches($numberofmatches, $projectid, $teamid, $selectiondate, $ordering = 'DESC', $round = 0, $matchstatus, $bUseFavteams)
 {
     $result = array();
     $query_SELECT = ' SELECT matches.*, p.timezone, r.roundcode as roundcode, r.id as roundid, r.name as roundname, "dummy", ' . ' p.name AS project_name, ' . ' t1.name AS home_name, ' . ' t1.middle_name AS home_middlename, ' . ' t1.short_name AS home_shortname, ' . ' t2.name AS away_name, ' . ' t2.middle_name AS away_middlename, ' . ' t2.short_name AS away_shortname, ' . ' c1.logo_small AS home_icon, ' . ' c2.logo_small AS away_icon, ' . ' t1.id AS team1_id, ' . ' t2.id AS team2_id, ' . ' p.id AS project_id, ' . ' matches.id AS match_id, ' . ' pt1.division_id ';
     $query_FROM = ' FROM #__joomleague_match AS matches ' . ' INNER JOIN #__joomleague_project_team AS pt1 ON pt1.id = matches.projectteam1_id ' . ' INNER JOIN #__joomleague_project_team AS pt2 ON pt2.id = matches.projectteam2_id ' . ' INNER JOIN #__joomleague_team AS t1 ON pt1.team_id = t1.id ' . ' INNER JOIN #__joomleague_team AS t2 ON pt2.team_id = t2.id ' . ' INNER JOIN #__joomleague_round AS r ON matches.round_id = r.id ' . ' INNER JOIN #__joomleague_project AS p ON pt1.project_id = p.id ' . ' INNER JOIN #__joomleague_club AS c1 ON c1.id = t1.club_id ' . ' INNER JOIN #__joomleague_club AS c2 ON c2.id = t2.club_id ';
     $query_WHERE = " WHERE matches.published=1 ";
     switch ($matchstatus) {
         case 0:
             $query_WHERE .= " AND matches.match_date >=  STR_TO_DATE('" . $selectiondate . "', '%Y-%m-%d-%H:%i') ";
             $query_WHERE .= " AND ( matches.match_date + INTERVAL (p.game_regular_time+p.halftime*(p.game_parts-1)) MINUTE ) < UTC_TIMESTAMP() ";
             break;
         case 1:
             $query_WHERE .= " AND ( matches.match_date BETWEEN  STR_TO_DATE('" . $selectiondate . "', '%Y-%m-%d-%H:%i') AND UTC_TIMESTAMP() ) ";
             break;
         case 2:
             $query_WHERE .= " AND ( matches.match_date + INTERVAL (p.game_regular_time+p.halftime*(p.game_parts-1)) MINUTE ) >= UTC_TIMESTAMP() ";
             break;
         case 3:
             $query_WHERE .= " AND matches.match_date >= UTC_TIMESTAMP() ";
             break;
         case 4:
             $query_WHERE .= " AND matches.match_date >= STR_TO_DATE('" . $selectiondate . "', '%Y-%m-%d-%H:%i')";
             break;
     }
     if ($round != 0) {
         $query_WHERE .= " AND r.id = {$round}";
     }
     if ($projectid != -1 && $projectid != '') {
         if (count($projectid) > 1 || is_array($projectid)) {
             $projects = implode(",", $projectid);
         } else {
             $projects = $projectid;
         }
         $query_WHERE .= " AND pt1.project_id IN (" . $projects . ")";
     }
     if ($teamid != -1 && $teamid != '' && is_array($teamid)) {
         if ($teamid[0] != "") {
             if (count($teamid) > 1 || is_array($teamid)) {
                 $teams = implode(",", $teamid);
             } else {
                 $teams = $teamid;
             }
             $query_WHERE .= " AND (pt1.team_id IN (" . $teams . ")";
             $query_WHERE .= " OR pt2.team_id IN (" . $teams . "))";
         }
     }
     if ($bUseFavteams) {
         $teams = modJoomleagueTickerHelper::getFavs($projects);
         if (!empty($teams)) {
             $query_WHERE .= " AND (pt1.team_id IN (" . $teams . ")";
             $query_WHERE .= " OR pt2.team_id IN (" . $teams . "))";
         }
     }
     $query_END = " ORDER BY matches.match_date {$ordering}, matches.match_number DESC LIMIT " . $numberofmatches;
     $database = JFactory::getDbo();
     $database->setQuery($query_SELECT . $query_FROM . $query_WHERE . $query_END);
     $result = $database->loadObjectList();
     if ($result) {
         foreach ($result as $match) {
             JoomleagueHelper::convertMatchDateToTimezone($match);
         }
     }
     return $result;
 }

 function getMatches($caldates, $ordering = 'ASC')
 {
     $database = JFactory::getDbo();
     $teamCondition = '';
     $clubCondition = '';
     $favCondition = '';
     $limitingcondition = '';
     $limitingconditions = array();
     $customteam = JRequest::getVar('jlcteam', 0, 'default', 'POST');
     $teamid = $this->xparams->get('team_ids');
     $teamid = $customteam > 0 ? $customteam : $teamid;
     if ($teamid) {
         $teamids = explode(',', $teamid);
         JArrayHelper::toInteger($teamids);
         $teamCondition = '(matchpart1 IN (' . implode(', ', $teamids) . ') or matchpart2 IN (' . implode(', ', $teamids) . '))';
         $limitingconditions[] = $teamCondition;
     }
     $clubid = $this->xparams->get('club_ids');
     if ($clubid && $customteam == 0) {
         $clubids = explode(',', $clubid);
         JArrayHelper::toInteger($clubids);
         $clubCondition = '(teams.club_id IN (' . implode(', ', $clubids) . ') AND (matchpart1 = teams.id or matchpart2 = teams.id))';
         $limitingconditions[] = $clubCondition;
     }
     if ($this->xparams->get('joomleague_use_favteams', 0) == 1 && $customteam == 0) {
         $favCondition .= "(m.matchpart1 IN (" . $this->favteams . ") OR m.matchpart2 IN (" . $this->favteams . "))";
         $limitingconditions[] = $favCondition;
     }
     if (count($limitingconditions) > 0) {
         $limitingcondition .= ' AND (';
         $limitingcondition .= implode(' OR ', $limitingconditions);
         $limitingcondition .= ')';
     }
     $limit = isset($caldates['limitstart']) && isset($caldates['limitend']) ? ' LIMIT ' . $caldates['limitstart'] . ', ' . $caldates['limitend'] : '';
     $query = "SELECT  m.*,p.*,\n                      DATE_FORMAT(match_date, '%Y-%m-%d') AS caldate,\n                      r.matchcode, r.name AS roundname, r.round_date_first, r.round_date_last\n              FROM #__joomleague_matches m\n              LEFT JOIN #__joomleague_rounds r ON m.round_id = r.id\n              LEFT JOIN #__joomleague p ON p.id = m.project_id  \n              LEFT JOIN #__joomleague_teams teams ON (teams.id = m.matchpart1 OR teams.id = m.matchpart2) ";
     $where = " WHERE m.published = 1 \n               AND p.published = 1 ";
     if (isset($caldates['start'])) {
         $where .= " AND m.match_date >= '" . $caldates['start'] . "'";
     }
     if (isset($caldates['end'])) {
         $where .= " AND m.match_date <= '" . $caldates['end'] . "'";
     }
     if (isset($caldates['matchcode'])) {
         $where .= " AND r.matchcode = '" . $caldates['matchcode'] . "'";
     }
     $projectid = $this->xparams->get('project_ids');
     if ($projectid) {
         $projectids = explode(',', $projectid);
         JArrayHelper::toInteger($projectids);
         $where .= " AND m.project_id IN (" . implode(', ', $projectids) . ")";
     }
     if (isset($caldates['resultsonly']) && $caldates['resultsonly'] == 1) {
         $where .= " AND m.matchpart1_result IS NOT NULL";
     }
     $where .= $limitingcondition;
     $where .= " GROUP BY m.match_id";
     $where .= " ORDER BY m.match_date " . $ordering;
     $query = $this->prefix != '' ? str_replace('#__', $this->prefix, $query) : $query;
     $database->setQuery($query . $where . $limit);
     $result = $database->loadObjectList();
     if ($result) {
         foreach ($result as $match) {
             JoomleagueHelper::convertMatchDateToTimezone($match);
         }
     }
     return $result;
 }

 function getAwayMatches($orderBy = 'ASC')
 {
     $result = array();
     $teams = $this->getTeams();
     $startdate = $this->getStartDate();
     $enddate = $this->getEndDate();
     if (is_null($teams)) {
         return null;
     }
     $query = ' SELECT m.*, m.id AS match_id, DATE_FORMAT(m.time_present,"%H:%i") time_present,' . ' p.name        AS project_name, p.timezone,' . ' p.id          AS project_id,' . ' r.id          AS roundid,' . ' r.roundcode   AS roundcode,' . ' r.name		  AS roundname,' . ' t1.id         AS team1_id,' . ' t2.id         AS team2_id,' . ' t1.name       AS tname1,' . ' t2.name       AS tname2,' . ' t1.short_name AS tname1_short,' . ' t2.short_name AS tname2_short,' . ' t1.middle_name AS tname1_middle,' . ' t2.middle_name AS tname2_middle,' . ' t1.club_id    AS club1_id,' . ' t2.club_id    AS club2_id,' . ' p.id          AS prid,' . ' l.name        AS l_name,' . ' playground.name AS pl_name,' . ' c1.country AS home_country,' . ' c1.logo_small AS home_logo_small,' . ' c1.logo_middle AS home_logo_middle,' . ' c1.logo_big AS home_logo_big,' . ' c2.country AS away_country, ' . ' c2.logo_small AS away_logo_small, ' . ' c2.logo_middle AS away_logo_middle, ' . ' c2.logo_big AS away_logo_big, ' . ' tj1.division_id, t1.club_id as t1club_id, t2.club_id as t2club_id,' . ' d.name AS division_name, d.shortname AS division_shortname, d.parent_id AS parent_division_id,' . ' CASE WHEN CHAR_LENGTH(p.alias) THEN CONCAT_WS(\':\',p.id,p.alias) ELSE p.id END AS project_slug,' . '	CASE WHEN CHAR_LENGTH(d.alias) THEN CONCAT_WS(\':\',d.id,d.alias) ELSE d.id END AS division_slug,' . ' CASE WHEN CHAR_LENGTH(c1.alias) THEN CONCAT_WS(\':\',c1.id,c1.alias) ELSE c1.id END AS club1_slug,' . ' CASE WHEN CHAR_LENGTH(c2.alias) THEN CONCAT_WS(\':\',c2.id,c2.alias) ELSE c2.id END AS club2_slug,' . ' CASE WHEN CHAR_LENGTH(t1.alias) THEN CONCAT_WS(\':\',t1.id,t1.alias) ELSE t1.id END AS team1_slug,' . ' CASE WHEN CHAR_LENGTH(t2.alias) THEN CONCAT_WS(\':\',t2.id,t2.alias) ELSE t2.id END AS team2_slug' . ' FROM #__joomleague_match AS m ' . ' INNER JOIN #__joomleague_project_team tj1 ON tj1.id=m.projectteam1_id ' . ' INNER JOIN #__joomleague_project_team tj2 ON tj2.id=m.projectteam2_id ' . ' INNER JOIN #__joomleague_team t1 ON t1.id=tj1.team_id ' . ' INNER JOIN #__joomleague_team t2 ON t2.id=tj2.team_id ' . ' INNER JOIN #__joomleague_project AS p ON p.id=tj1.project_id ' . ' INNER JOIN #__joomleague_league l ON p.league_id=l.id ' . ' INNER JOIN #__joomleague_club c2 ON c2.id=t2.club_id ' . ' INNER JOIN #__joomleague_round r ON m.round_id=r.id ' . ' LEFT JOIN #__joomleague_club c1 ON c1.id=t1.club_id ' . ' LEFT JOIN #__joomleague_playground AS playground ON playground.id=m.playground_id ' . ' LEFT JOIN #__joomleague_division d ON d.id=tj1.division_id' . ' WHERE p.published=1 ' . ' AND (m.match_date BETWEEN ' . $this->_db->Quote($startdate) . ' AND ' . $this->_db->Quote($enddate) . ')';
     if ($this->project_id > 0) {
         $query .= ' AND p.id=' . $this->_db->Quote($this->project_id);
     }
     if ($this->clubid > 0) {
         $query .= ' AND t2.club_id=' . $this->_db->Quote($this->clubid);
     }
     $arrMatchIds = array();
     $arrMatchIds[] = 0;
     //no home matches
     foreach ($this->homematches as $game) {
         $arrMatchIds[] = $game->id;
     }
     $query .= ' AND NOT m.id in (' . implode(",", $arrMatchIds) . ')';
     $query .= ' AND m.published=1 ' . ' ORDER BY m.match_date ' . $orderBy;
     $this->_db->setQuery($query);
     $this->awaymatches = $this->_db->loadObjectList();
     if ($this->awaymatches) {
         foreach ($this->awaymatches as $match) {
             JoomleagueHelper::convertMatchDateToTimezone($match);
         }
     }
     return $this->awaymatches;
 }

 function getGames()
 {
     $teamplayers = $this->getTeamPlayers();
     $games = array();
     if (count($teamplayers)) {
         $quoted_tpids = array();
         foreach ($teamplayers as $teamplayer) {
             $quoted_tpids[] = $this->_db->Quote($teamplayer->id);
         }
         $tpid_list = '(' . implode(',', $quoted_tpids) . ')';
         $query = ' SELECT m.*, ios.*' . ' FROM #__joomleague_match AS m' . ' INNER JOIN' . ' (' . ' 		SELECT mp.match_id, t1.id AS team1, t2.id AS team2, r.roundcode, r.project_id, tp.projectteam_id, p.timezone, ' . ' 		COALESCE(sum(mp.came_in=0 AND mp.teamplayer_id IN (' . $tpid_list . ')),0) AS started,' . ' 		COALESCE(sum(mp.came_in=1 AND mp.teamplayer_id IN (' . $tpid_list . ')),0) AS sub_in,' . ' 		COALESCE(sum((mp.teamplayer_id IN (' . $tpid_list . ') AND mp.out = 1) OR mp.in_for IN (' . $tpid_list . ')),0) AS sub_out' . ' 		FROM #__joomleague_match_player AS mp' . ' 		INNER JOIN #__joomleague_team_player AS tp ON tp.id=mp.teamplayer_id' . ' 		INNER JOIN #__joomleague_match AS m ON m.id=mp.match_id' . ' 		INNER JOIN #__joomleague_round r ON r.id=m.round_id' . ' 		INNER JOIN #__joomleague_project AS p ON p.id=r.project_id' . ' 		INNER JOIN #__joomleague_project_team AS pt1 ON pt1.id=m.projectteam1_id' . ' 		INNER JOIN #__joomleague_team AS t1 ON t1.id=pt1.team_id' . ' 		INNER JOIN #__joomleague_project_team AS pt2 ON pt2.id=m.projectteam2_id' . ' 		INNER JOIN #__joomleague_team AS t2 ON t2.id=pt2.team_id' . ' 		WHERE ((mp.teamplayer_id IN (' . $tpid_list . ')) OR (mp.in_for IN (' . $tpid_list . ')))' . '           AND m.published = 1 AND p.published = 1' . ' 		GROUP BY mp.match_id, t1.id, t2.id, r.roundcode, r.project_id, tp.projectteam_id, p.timezone' . ' ) AS ios ON ios.match_id=m.id' . ' ORDER BY m.match_date';
         $this->_db->setQuery($query);
         $games = $this->_db->loadObjectList();
         if ($games) {
             foreach ($games as $game) {
                 JoomleagueHelper::convertMatchDateToTimezone($game);
             }
         }
     }
     return $games;
 }

 public function getMatches()
 {
     $db = JFactory::getDBO();
     $limit = $this->params->get('limit', 0) > 0 ? $this->params->get('limit', 0) : 1;
     $query = " SELECT m.*,m.id as match_id, t1.id team1_id, t2.id team2_id," . " " . $this->getDateStringNoTime() . " AS match_date_notime," . " " . $this->getDateString() . " AS match_date," . " NOW() AS currenttime," . " IF (" . "\t\tm.match_result_type > 0," . "     (p.game_regular_time+(p.game_parts * p.halftime)-p.halftime) + p.add_time," . "     p.game_regular_time+(p.game_parts * p.halftime)-p.halftime" . " ) AS totaltime," . " IF (" . "\t\t(match_date > UTC_TIMESTAMP() AND m.team1_result IS NULL) AND" . "\t\t((m.team1_result_split IS NULL) OR TRIM(REPLACE(m.team1_result_split, ';', '')) = '')," . $this->getDateString() . "," . "     'z'" . " ) AS upcoming," . " IF (" . "\t\t(m.team1_result IS NOT NULL OR " . $this->getDateString() . " < UTC_TIMESTAMP())," . $this->getDateString() . "," . "\t\t0" . " ) AS alreadyplayed," . " IF (" . "\t\t(" . "\t\t\t(" . "\t\t\t\tDATE_ADD(" . $this->getDateString() . "," . "\t\t\t\t\tINTERVAL IF (" . "\t\t\t\t\t\tm.match_result_type > 0," . "\t\t\t\t\t\t(p.game_regular_time+(p.game_parts * p.halftime)-p.halftime) + p.add_time," . "\t\t\t\t\t\tp.game_regular_time+(p.game_parts * p.halftime)-p.halftime" . "\t\t\t\t\t) MINUTE" . "\t\t\t\t) > UTC_TIMESTAMP()" . "\t\t\t) AND (" . $this->getDateString() . " < UTC_TIMESTAMP()" . "\t\t\t) AND (m.team1_result IS NULL)" . "\t\t)," . $this->getDateString() . "," . "\t\t'z'" . " ) AS actplaying," . " IF (" . "\t\t((m.team1_result IS NULL) AND (m.team1_result_split IS NOT NULL) AND" . "\t\t\tTRIM(REPLACE(m.team1_result_split, ';', '') != ''))," . $this->getDateString() . "," . "'z'" . " ) AS live," . " DATE_SUB(" . $this->getDateString() . ", INTERVAL '90' MINUTE) AS meetingtime," . " NOW() AS local_time," . " r.*," . " p.name AS pname," . " p.current_round," . " p.id AS project_id," . " p.timezone," . " p.game_parts," . " p.ordering," . " IF (mref.project_referee_id > 0, concat(person.lastname, ', ', person.firstname), '') AS refname," . " pg.name AS pg_name, pt1.division_id, " . " pg.short_name AS pg_shortname" . " FROM     #__joomleague_match m" . " INNER JOIN #__joomleague_round r ON m.round_id = r.id" . " INNER JOIN #__joomleague_project_team pt1 ON pt1.id = m.projectteam1_id" . " INNER JOIN #__joomleague_team t1 ON t1.id = pt1.team_id" . " INNER JOIN #__joomleague_project_team pt2 ON pt2.id = m.projectteam2_id" . " INNER JOIN #__joomleague_team t2 ON t2.id = pt2.team_id" . " INNER JOIN #__joomleague_project p ON pt1.project_id = p.id" . " LEFT JOIN #__joomleague_match_referee AS mref ON m.id = mref.match_id" . " LEFT JOIN #__joomleague_project_referee AS pref ON pref.id = mref.project_referee_id" . " LEFT JOIN #__joomleague_person AS person ON person.id = pref.person_id" . "       AND person.published = 1" . "       AND pref.published = 1" . " LEFT JOIN #__joomleague_playground AS pg ON pg.id = m.playground_id" . "  WHERE ( ";
     $this->buildWhere();
     $query .= implode(' AND ', $this->conditions);
     $query .= " )" . " GROUP BY m.id ";
     $query .= $this->buildOrder();
     $matches = $this->getFromDB($query);
     if ($matches) {
         foreach ($matches as $match) {
             JoomleagueHelper::convertMatchDateToTimezone($match);
         }
     }
     return $this->formatMatches($matches);
 }

    function getGames()
    {
        $query = '	SELECT	m.*,
							t1.id AS team1,
							t2.id AS team2,
							r.roundcode,
							r.project_id,
							p.timezone
					FROM #__joomleague_match AS m
					INNER JOIN #__joomleague_match_referee AS mr ON mr.match_id=m.id
					INNER JOIN #__joomleague_project_referee AS pr ON pr.id=mr.project_referee_id
					INNER JOIN #__joomleague_round r ON m.round_id=r.id
					INNER JOIN #__joomleague_project_team AS pt1 ON m.projectteam1_id=pt1.id
					INNER JOIN #__joomleague_team AS t1 ON t1.id=pt1.team_id
					INNER JOIN #__joomleague_project_team AS pt2 ON m.projectteam2_id=pt2.id
					INNER JOIN #__joomleague_team AS t2 ON t2.id=pt2.team_id
					INNER JOIN #__joomleague_project AS p ON p.id=r.project_id
					WHERE	pr.person_id=' . $this->_db->Quote($this->personid) . '
							AND r.project_id=' . $this->_db->Quote($this->projectid) . '
							AND m.published=1
					ORDER BY m.match_date ';
        $this->_db->setQuery($query);
        $games = $this->_db->loadObjectList();
        if ($games) {
            foreach ($games as $game) {
                JoomleagueHelper::convertMatchDateToTimezone($game);
            }
        }
        return $games;
    }

 function getMatchesOptions($project_id, $division = 0, $required = false)
 {
     $lang = JFactory::getLanguage();
     $extension = "com_joomleague";
     $source = JPath::clean(JPATH_SITE . '/components/' . $extension);
     $lang->load($extension, JPATH_SITE, null, false, false) || $lang->load($extension, $source, null, false, false) || $lang->load($extension, JPATH_SITE, $lang->getDefault(), false, false) || $lang->load($extension, $source, $lang->getDefault(), false, false);
     $query = "\tSELECT\tm.id AS value, m.match_date,\n\t\t\t\t\t\t\tCONCAT(t1.middle_name, ' - ', t2.middle_name) AS text,\n\t\t\t\t\t\t\tp.timezone\n\t\t\t\t\tFROM #__joomleague_match AS m\n\t\t\t\t\tINNER JOIN #__joomleague_project_team AS pt1 ON m.projectteam1_id = pt1.id\n\t\t\t\t\tINNER JOIN #__joomleague_project_team AS pt2 ON m.projectteam2_id = pt2.id\n\t\t\t\t\tINNER JOIN #__joomleague_team AS t1 ON pt1.team_id=t1.id\n\t\t\t\t\tINNER JOIN #__joomleague_team AS t2 ON pt2.team_id=t2.id\n\t\t\t\t\tINNER JOIN #__joomleague_project p ON p.id = pt1.project_id\n\t\t\t\t\tWHERE pt1.project_id = " . $this->_db->Quote($project_id);
     if ($division > 0) {
         $query .= " AND (pt1.division_id = " . $this->_db->Quote($division);
         $query .= " OR pt2.division_id = " . $this->_db->Quote($division) . ") ";
     }
     $query .= " ORDER BY m.match_date, t1.short_name";
     $this->_db->setQuery($query);
     $matches = $this->_db->loadObjectList();
     foreach ($matches as $match) {
         JoomleagueHelper::convertMatchDateToTimezone($match);
         $match->text = JoomleagueHelper::getMatchDate($match, JText::_('COM_JOOMLEAGUE_MATCHDAYDATE') . ' H:i') . ' - ' . $match->text;
     }
     return $this->addGlobalSelectElement($matches, $required);
 }