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);
}
function _getResultsRows($roundcode = 0, $teamId = 0, $ordering = 'ASC', $unpublished = 0, $getplayground = 0, $getreferee = 0)
{
$matches = array();
$joomleague = $this->getProject();
$query_SELECT = ' SELECT matches.*, p.timezone ';
$query_FROM = ' FROM #__joomleague_match AS matches ' . ' INNER JOIN #__joomleague_round AS r ON matches.round_id=r.id' . ' INNER JOIN #__joomleague_project AS p ON p.id=r.project_id' . ' INNER JOIN #__joomleague_project_team AS pt1 ON matches.projectteam1_id=pt1.id ' . ' INNER JOIN #__joomleague_team AS t1 ON t1.id=pt1.team_id ' . ' INNER JOIN #__joomleague_project_team AS pt2 ON matches.projectteam2_id=pt2.id ' . ' INNER JOIN #__joomleague_team AS t2 ON t2.id=pt2.team_id ';
$query_WHERE = ' WHERE r.project_id=' . (int) $this->projectid . ' AND r.roundcode=' . $roundcode;
if ($teamId) {
$query_WHERE .= " AND (t1.id=" . $teamId . " OR t2.id=" . $teamId . ")";
}
$query_END = " GROUP BY matches.id\n\t\t\t\t\t ORDER BY matches.match_date " . $ordering . ",matches.match_number";
if ($this->divisionid > 0) {
$query_FROM .= "\n\t\t\t\t\t\t\t LEFT JOIN #__joomleague_division AS d1 ON pt1.division_id=d1.id\n\t\t\t\t\t\t\t LEFT JOIN #__joomleague_division AS d2 ON pt2.division_id=d2.id";
$query_WHERE .= " AND (d1.id=" . $this->divisionid . " OR d1.parent_id=" . $this->divisionid . "\n\t\t\t\t\t\t\t OR d2.id=" . $this->divisionid . " OR d2.parent_id=" . $this->divisionid . ")";
}
if ($unpublished != 1) {
$query_WHERE .= " AND matches.published=1";
}
if ($getplayground) {
$query_SELECT .= ",playground.name AS playground_name,playground.short_name AS playground_short_name";
$query_FROM .= " LEFT JOIN #__joomleague_playground AS playground ON playground.id=matches.playground_id";
}
$this->_db->setQuery($query_SELECT . $query_FROM . $query_WHERE . $query_END);
$matches = $this->_db->loadObjectList();
if ($matches) {
foreach ($matches as $match) {
JoomleagueHelper::convertMatchDateToTimezone($match);
}
}
if ($getreferee) {
$this->_getRefereesByMatch($matches, $joomleague);
}
return $matches;
}
/**
* returns last X games
*
* @param int $current_roundcode
* @param int $ptid project team id
* @return array
*/
function _getTeamPreviousX($current_roundcode, $ptid)
{
$config = $this->getTemplateConfig('nextmatch');
$nblast = $config['nb_previous'];
$query = ' SELECT m.*, r.project_id, r.id AS roundid, r.roundcode, p.timezone, pt1.division_id ' . ' FROM #__joomleague_match AS m ' . ' INNER JOIN #__joomleague_round AS 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 p.id = m.projectteam1_id ' . ' WHERE (m.projectteam1_id = ' . $ptid . ' OR m.projectteam2_id = ' . $ptid . ')' . ' AND r.roundcode < ' . $current_roundcode . ' AND m.published = 1 ' . ' GROUP BY m.id ' . ' ORDER BY r.roundcode DESC ' . ' LIMIT 0, ' . $nblast;
$this->_db->setQuery($query);
$res = $this->_db->loadObjectList();
if ($res) {
$res = array_reverse($res);
foreach ($res as $game) {
JoomleagueHelper::convertMatchDateToTimezone($game);
}
}
return $res;
}
function getMatches($caldates, $ordering = 'ASC')
{
$database = JFactory::getDbo();
$teamCondition = '';
$clubCondition = '';
$favCondition = '';
$limitingcondition = '';
$limitingconditions = array();
$favConds = array();
$customteam = JRequest::getVar('jlcteam', 0, 'default', 'POST');
$teamid = $this->xparams->get('team_ids');
if ($customteam != 0) {
$limitingconditions[] = "( m.projectteam1_id = " . $customteam . " OR m.projectteam2_id = " . $customteam . ")";
}
if ($teamid && $customteam == 0) {
$teamids = is_array($teamid) ? implode(",", $teamid) : $teamid;
if ($teamids > 0) {
$limitingconditions[] = "pt.team_id IN (" . $teamids . ")";
}
}
$clubid = $this->xparams->get('club_ids');
if ($clubid && $customteam == 0) {
$clubids = is_array($clubid) ? implode(",", $clubid) : $clubid;
if ($clubids > 0) {
$limitingconditions[] = "team.club_id IN (" . $clubids . ")";
}
}
if ($this->xparams->get('joomleague_use_favteams', 0) == 1 && $customteam == 0) {
foreach ($this->favteams as $projectfavs) {
$favConds[] = "(pt.team_id IN (" . $projectfavs->fav_team . ") AND p.id =" . $projectfavs->id . ")";
}
if (!empty($favConds)) {
$limitingconditions[] = implode(' OR ', $favConds);
}
}
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 match_date AS caldate,\n r.roundcode, r.name AS roundname, r.round_date_first, r.round_date_last,\n m.id as matchcode, p.id as project_id\n FROM #__joomleague_match m\n inner JOIN #__joomleague_round r ON r.id = m.round_id\n inner JOIN #__joomleague_project p ON p.id = r.project_id\n inner JOIN #__joomleague_project_team pt ON (pt.id = m.projectteam1_id OR pt.id = m.projectteam2_id)\n inner JOIN #__joomleague_team team ON team.id = pt.team_id\n inner JOIN #__joomleague_club club ON club.id = team.club_id\n ";
$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 = is_array($projectid) ? implode(",", $projectid) : $projectid;
if ($projectids > 0) {
$where .= " AND p.id IN (" . $projectids . ")";
}
}
if (isset($caldates['resultsonly']) && $caldates['resultsonly'] == 1) {
$where .= " AND m.team1_result IS NOT NULL";
}
$where .= $limitingcondition;
$where .= " GROUP BY m.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;
}
