news.php

<?php
class News
{
	private $cache_section = 'news';
	private $dbctrl;
	
	function __construct()
	{
			$this->dbctrl = Base::initDB();
	}
	

	public function getStarForecasts($tournament_id=0, $limit=1)
	{
		$cache = new mCache($this->cache_section);
		$cache->ttl = 3600;
		$ckey = 'starforecasts';
		$cflags = array('starforecasts'=>mCache::flag_all);
		if(false === ($data = $cache->get($ckey, $cflags)))
		{
			$data = array();
			$q = "SELECT `starforecasts`.`id` AS `f_id`, `actuality`, `annotation`, `forecast`,
							`surname`, `name`, `middlename`, `sinfo`, `photo_small`,
							`tournaments`.`id` AS `t_id`, `tournaments`.`title` AS `t_title`
					FROM `starforecasts`
						INNER JOIN `people` ON (`people`.`id` = `people_id`)
						INNER JOIN `tournaments` ON (`tournaments`.`id` = `tournament_id`)
					WHERE (`actuality` >= NOW())".(($tournament_id) ? " AND (`tournament_id`={$tournament_id})":"")."
					ORDER BY `tournaments`.`priority` ASC, `starforecasts`.`date` DESC
					LIMIT 10";	//пока просто сгружаем последние 10 и кэшируем
			$dbctrl = Base::initDB();
			$result = $dbctrl->query($q);
			$tourn = 0;
			while($r = $result->fetch_object())
			{
				if(!isset($data[$r->t_id]))
					$data[$r->t_id]	= array();
				$data[$r->t_id][$r->f_id] = array(
					't_title'=>$r->t_title,
					'name'=>$r->name,
					'surname'=>$r->surname,
					'photo'=>$r->photo_small,
					'sinfo'=>$r->sinfo,
					'annot'=>$r->annotation,
					'forecasts'=>$r->forecast
				);
			}
			$cache->write($ckey, $data, $cflags);
		}
		if($tournament_id)
		{
			$dbt = array();
			$i = 0;
			foreach($data as $tourn_id=>&$d)
			{
				if(++$i > $limit)
					break;
				$dbt[$tourn_id] = $d;
			}
			return $dbt;
		}
		else
			return array_slice($data, 0, $limit, true);
		return $data;
	}

	public function showAnnouncements()
	{
		$dbctrl = Base::initDB();
		$q = "SELECT `title`, `collage`, `article`, `href`
				FROM `announcements`
				WHERE `date_till` >= NOW()";
		$result = $dbctrl->query($q);
		while($r = $result->fetch_object())
		{
			echo '<div class="announcement">';
			echo '<h4>'.$r->title.'</h4>';
			echo '<img src="'.$r->collage.'" title="'.$r->title.'"/><br/>';
			echo $r->article;
			echo '<p align="right"><a href="'.$r->href.'">Перейти к прогнозам</a> &raquo;</p>';
			echo '</div>';
		}
	}

	public function getByTournament($tournament_id, $num=5, $skip=0)
	{
		$tournament_id = (int)$tournament_id;
		$skip = (int)$skip;
		$num = (int)$num;

		if($skip == 0)
		{
			$cache = new mCache($this->cache_section);
			$cache->ttl = 300;
			$ckey = 'tourn_'.$tournament_id.'_'.$num;
			$cflags = array('news_by_tourn'=>$tournament_id);
			if(false !== ($data = $cache->get($ckey, $cflags)))
				return $data;
		}

		$data = array();		
		$dbctrl = Base::initDB();
		$q = "SELECT `news`.`id`, `sport_id`, DATE_FORMAT(`date`, '%d.%m.%Y %H:%i') AS `d`, `news`.`title`, `annotation`,
					`news_sources`.`title` AS `source_title`, `news_sources`.`site` AS `source_site`, `news`.`url`
				FROM `news`
					LEFT JOIN `news_sources` ON (`news`.`source_id` = `news_sources`.`id`)
				WHERE `tournament_id`={$tournament_id}
				ORDER BY `date` DESC LIMIT {$skip}, {$num}";
		$result = $dbctrl->query($q);
		while($r = $result->fetch_object())
		{
			$data[$r->id] = array(
				'tourn_id'=>$tournament_id,
				'sport_id'=>$r->sport_id,
				'd'=>$r->d,
				't'=>$r->title,
				'ann'=>$r->annotation,
				'srct'=>$r->source_title,
				'srcsite'=>$r->source_site,
				'url'=>$r->url
			);
		}
		if($skip == 0)
			$cache->write($ckey, $data, $cflags);
		return $data;
	}

	public function showArchivePanel($urlbase)
	{
	}

	public function getSiteNews($num=15, $skip=0)
	{
		$cache = new mCache($this->cache_section);
		$cache->ttl = 43200;
		if(!$skip)
		{
			$ckey = 'site_'.$num;
			$cflags = array('site'=>mCache::flag_all);
			if(false !== ($data = $cache->get($ckey, $cflags)))
				return $data;
		}

		$skip = (int)$skip;
		$num = (int)$num;
		$data = array();		
		$dbctrl = Base::initDB();
		$q = "SELECT `id`, DATE_FORMAT(`date`, '%d.%m.%Y %H:%i') AS `d`, `title`, `info`, `article`
				FROM `news_site`
				ORDER BY `date` DESC LIMIT {$skip}, {$num}";
		$result = $dbctrl->query($q);
		while($r = $result->fetch_object())
		{
			$data[$r->id] = array(
				'd'=>$r->d,
				't'=>$r->title,
				'ann'=>$r->info
			);
		}
		if($skip == 0)
			$cache->write($ckey, $data, $cflags);
		return $data;
	}

	public function getSiteNewsById($id)
	{
		$data = array();		
		$dbctrl = Base::initDB();
		$id = (int)$id;
		$q = "SELECT `id`, DATE_FORMAT(`date`, '%d.%m.%Y %H:%i') AS `d`, `title`, `info`, `article`
				FROM `news_site`
				WHERE `id`={$id}";
		$result = $dbctrl->query($q);
		if($r = $result->fetch_object())
		{
			$data = array(
				'd'=>$r->d,
				't'=>$r->title,
				'ann'=>$r->info,
				'article'=>$r->article
			);
		}
		return $data;
	}
	
	public function getTeamBinds($news_id, $status=false){
		$data = array();
		$news_id = (int)$news_id;
		$dbctrl = $this->dbctrl ? $this->dbctrl : Base::initDB();
		$bind_status_clause = '';
		if($status !== false && $status !== 0 && $status !== 1) return false;
		if($status !== false) $bind_status_clause = "AND nt.bind_status='{$status}'";
		$query = "SELECT nt.team_id, nt.bind_status, t.name, c.name as city, t.emblem_ico
					FROM news_teams nt
					INNER JOIN teams t ON t.id=nt.team_id
					INNER JOIN cities c ON c.id=t.city_id
					WHERE nt.news_id='{$news_id}' {$bind_status_clause}";
		if($result = $dbctrl->query($query)){
			while($row = $result->fetch_assoc()) $data[] = $row; //fetch_all()??
			return $data;
		}else{
			return false;
		}
	}

	public function getTournamentBinds($news_id, $status=false){
		$data = array();
		$news_id = (int)$news_id;
		$dbctrl = $this->dbctrl ? $this->dbctrl : Base::initDB();
		$bind_status_clause = '';
		if($status !== false && $status !== 0 && $status !== 1) return false;
		if($status !== false) $bind_status_clause = "AND nt.bind_status='{$status}'";
		$query = "SELECT nt.tournament_id, nt.bind_status 
					FROM news_tournaments nt
					WHERE nt.news_id='{$news_id}' {$bind_status_clause}";
		if($result = $dbctrl->query($query)){
			while($row = $result->fetch_assoc()) $data[] = $row; //fetch_all()??
			return $data;
		}else{
			return false;
		}
	}		
	
	public function getGameBinds($news_id, $status=false){
		$data = array();
		$news_id = (int)$news_id;
		$dbctrl = $this->dbctrl ? $this->dbctrl : Base::initDB();
		$bind_status_clause = '';
		if($status !== false && $status !== 0 && $status !== 1) return false;
		if($status !== false) $bind_status_clause = "AND ng.bind_status='{$status}'";
		$query = "SELECT ng.game_id, ng.bind_status, g.when, g.tournament_id, s.id as sport_id, c.id as competition_id, g.score_home, g.score_away, th.emblem_ico as home_ico, th.name as home_name, ta.emblem_ico as away_ico, ta.name as away_name
					FROM news_games ng
					INNER JOIN games g ON g.id=ng.game_id
					INNER JOIN teams th ON g.team_id_home=th.id
					INNER JOIN teams ta ON g.team_id_away=ta.id
					INNER JOIN tournaments t ON t.id=g.tournament_id
					INNER JOIN competitions c ON c.id=t.competition_id
					INNER JOIN sports s ON s.id=c.sport_id
					WHERE ng.news_id='{$news_id}' {$bind_status_clause}";
		if($result = $dbctrl->query($query)){
			while($row = $result->fetch_assoc()) $data[] = $row; //fetch_all()??
			return $data;
		}else{
			return false;
		}
	}


    /**
     * Связывает конкретную новость с играми, чьи ID перечислены в списке $games
     *
     * При $mode равном 'replace' все существующие привязки игр к этой новости
     * будут уничтожены и будут заменены новыми.
     *
     * При $mode равном 'add' старые привязки сохранятся, а новые добавятся к ним
     * (обработка дублирующихся присутствует)
     *
     * @param integer $news_id
     * @param array $games
     * @param string $mode
     *
     * @return boolean
     */
	public function bindGames($news_id, Array $games, $mode='add'){
		if(empty($games)) return false;
		$news_id = (int)$news_id;

		if($mode == 'add'){
                    $already_binded_games = $this->getGameBinds($news_id);
                    foreach($already_binded_games as $game) if(FALSE !== ($key = array_search($game['game_id'], $games))) unset($games[$key]);
                }elseif($mode == 'replace'){
                    $this->unbindAllGames($news_id);
                }else{
                    return false;
                }

                foreach($games as $game_id){
			$query = "INSERT INTO news_games (news_id, game_id, bind_status) VALUES('{$news_id}', '{$game_id}', 1)";
			if(!$this->dbctrl->query($query)) return false;
		}
		return true;
	}

	public function bindTeams($news_id, Array $teams){
		if(empty($teams)) return false;
		$news_id = (int)$news_id;
		$already_binded = $this->getTeamBinds($news_id);
		foreach($already_binded as $binded) if(FALSE !== ($key = array_search($binded['team_id'], $teams))) unset($teams[$key]);
		foreach($teams as $team_id){
			$query = "INSERT INTO news_teams (news_id, team_id, bind_status) VALUES('{$news_id}', '{$team_id}', 1)";
			if(!$this->dbctrl->query($query)) return false;
		}
		return true;
	}

	public function bindTournaments($news_id, Array $tournaments){
		if(empty($tournaments)) return false;
		$news_id = (int)$news_id;
		$already_binded = $this->getTournamentBinds($news_id);
		foreach($already_binded as $binded) if(FALSE !== ($key = array_search($binded['tournament_id'], $tournaments))) unset($tournaments[$key]);
		foreach($tournaments as $tournament_id){
			$query = "INSERT INTO news_tournaments (news_id, tournament_id, bind_status) VALUES('{$news_id}', '{$tournament_id}', 1)";
			if(!$this->dbctrl->query($query)) return false;
		}
		return true;
	}

	public function unbindGames($news_id, Array $games){
		if(empty($games)) return false;
		$news_id = (int)$news_id;
		foreach($games as $game_id){
			$query = "DELETE FROM news_games WHERE `news_id`='{$news_id}' AND `game_id`='{$game_id}'";
			if(!$this->dbctrl->query($query)) return false;
		}
		return true;
	}

        public function unbindAllGames($news_id){
            $news_id = (int)$news_id;
            if(!$this->dbctrl->query("DELETE FROM news_games WHERE `news_id`='{$news_id}'")) return false;
            return true;
        }

	/**
	 * Возвращает массив новостей, связанных с игрой.
	 * $before_interval - нижний предел времени новости в сутках относительно начала матча,
	 * $after_interval - верхний предел в часах
	 *
	 * @param integer $game_id
	 * @param integer $before_interval
	 * @param integer $after_interval
	 *
	 * @return Array
	 */
	public function getGameNews($game_id, $before_interval=NULL, $after_interval=NULL){
		$game_id = (int)$game_id;
		$query = "
			SELECT
			  n.`id`,
			  DATE_FORMAT(n.`date`, '%d.%m.%Y %H:%i') AS `date`,
			  n.sport_id,
			  n.title,
			  n.annotation,
			  n.url,
			  ns.title AS source_title,
			  ns.site
			FROM news_games AS ng
			  INNER JOIN news AS n ON (n.`id` = ng.news_id)
			  INNER JOIN news_sources AS ns ON (ns.`id` = n.source_id)
			  INNER JOIN games AS g ON (g.`id`=ng.game_id)
			WHERE ng.game_id = {$game_id} AND ng.bind_status = 1 AND n.show = 1
					AND (n.`date` BETWEEN DATE_SUB(n.`date`, INTERVAL 21 DAY) AND n.`date`)
			ORDER BY n.`date` DESC";
		$dbctrl = Base::initDB();
		$result = $dbctrl->query($query);
		$data = array();
		while($r = $result->fetch_assoc()) $data[$r['id']] = $r;
		return $data;
	}

	public function getInfluencesOfNews(Array $news_ids)
	{
		$data = array();
		if(!$news_ids)
			return;
		$q = "SELECT `news_id`, `team_id`, `influence`
				FROM `news_teams`
				WHERE `news_id` IN (".implode(',', $news_ids).")";
		$dbctrl = Base::initDB();
		$result = $dbctrl->query($q);
		while($r = $result->fetch_object())
		{
			if(!isset($data[$r->news_id]))
				$data[$r->news_id] = array();
			$data[$r->news_id][$r->team_id] = $r->influence;
		}
		return $data;
	}
	
	static function generateSelectorWhereClause(){
		$where_pieces = array();
		if(!empty($_GET['sport']) && $_GET['sport']!='all') $where_pieces[] = " `news`.`sport_id`='{$_GET['sport']}' ";
		$full_where = (empty($where_pieces)) ? "WHERE " : " WHERE " . implode(" AND ", $where_pieces) . " AND ";
		return $full_where;
	}
	
	public function getNews($num=5, $skip=0, $where_clause='')
	{
		$skip = (int)$skip;
		$num = (int)$num;

		/*if($skip == 0)
		{
			$cache = new mCache($this->cache_section);
			$cache->ttl = 300;
			$ckey = 'tourn_'.$tournament_id.'_'.$num;
			$cflags = array('news_by_tourn'=>$tournament_id);
			if(false !== ($data = $cache->get($ckey, $cflags)))
				return $data;
		}*/

		$data = array();		
		$dbctrl = Base::initDB();
		$q = "SELECT `news`.`id`, `sport_id`, DATE_FORMAT(`date`, '%d.%m.%Y %H:%i') AS `d`, `news`.`title`, `annotation`,
					`news_sources`.`title` AS `source_title`, `news_sources`.`site` AS `source_site`, `news`.`url`
				FROM `news`
					LEFT JOIN `news_sources` ON (`news`.`source_id` = `news_sources`.`id`)
				$where_clause `news`.`show`=1
				ORDER BY `date` DESC LIMIT {$skip}, {$num}";
		$result = $dbctrl->query($q);
		while($r = $result->fetch_object())
		{
			$data[$r->id] = array(
				'sport_id'=>$r->sport_id,
				'd'=>$r->d,
				't'=>$r->title,
				'ann'=>$r->annotation,
				'srct'=>$r->source_title,
				'srcsite'=>$r->source_site,
				'url'=>$r->url
			);
		}
		//$ids = array_keys($data);
		//$ids_str = implode(',', $ids);
		//$query = "SELECT nt.news_id, nt.tournament_id FROM news_tournaments nt INNER JOIN tournaments t ON t.id=nt.tournament_id INNER JOIN competitions c ON c.id=t.competition_id"
		/*if($skip == 0)
			$cache->write($ckey, $data, $cflags); */
		return $data;
	}

    /**
    * Возвращает массив новостей, связанных с конкретной командой (или с любой из списка команл $teams)
    * 
    * @param integer $num
    * @param integer $skip
    * @param Array $teams
    * 
    * @return Array
    */
	public function getNewsByTeam($num=5, $skip=0, Array $teams)
	{
		$skip = (int)$skip;
		$num = (int)$num;
        if(!is_array($teams)) return array();
        
		$where_clause = (!empty($teams)) ? " WHERE `news_teams`.`team_id` IN (".implode(',', $teams).") " : "";

		$data = array();		
		$dbctrl = Base::initDB();
		$q = "SELECT `news`.`id`, `sport_id`, DATE_FORMAT(`date`, '%d.%m.%Y %H:%i') AS `d`, `news`.`title`, `annotation`,
					`news_sources`.`title` AS `source_title`, `news_sources`.`site` AS `source_site`, `news`.`url`
				FROM `news_teams`
					LEFT JOIN `news` ON (`news`.`id` = `news_teams`.`news_id`)
					LEFT JOIN `news_sources` ON (`news`.`source_id` = `news_sources`.`id`) 
				$where_clause 
				ORDER BY `news`.`date` DESC LIMIT {$skip}, {$num}";
		$result = $dbctrl->query($q);
		while($r = $result->fetch_object())
		{
			$data[$r->id] = array(
				'sport_id'=>$r->sport_id,
				'd'=>$r->d,
				't'=>$r->title,
				'ann'=>$r->annotation,
				'srct'=>$r->source_title,
				'srcsite'=>$r->source_site,
				'url'=>$r->url
			);
		}
		return $data;
	}
	
    /**
    * Возвращает массив новостей, относящихся к конкретному соревнованию
    * 
    * @param integer $competition_id
    * @param integer $num
    * @param integer $skip
    * 
    * @return Array
    */
	public function getNewsByOneCompetition($competition_id, $num=5, $skip=0){
		$competition_id = (int)$competition_id;
        $num = (int)$num;
        $skip = (int)$skip;
        
		$db = Base::initDB();
		$query = "SELECT `n`.`id`, `n`.`sport_id`, DATE_FORMAT(`n`.`date`, '%d.%m.%Y %H:%i') AS `d`, `n`.`title`, `n`.`annotation`,
					`ns`.`title` AS `source_title`, `ns`.`site` AS `source_site`, `n`.`url` 
					FROM news n 
					LEFT JOIN news_sources ns ON (`n`.`source_id` = `ns`.`id`)
					WHERE n.id IN(
						SELECT news_id FROM news_tournaments WHERE tournament_id IN(
							SELECT id FROM tournaments WHERE competition_id='{$competition_id}'
							)
						)
					ORDER BY `n`.`date` DESC
					LIMIT {$skip}, {$num}
					";
		$result = $db->query($query);
		$data = array();
		while($r = $result->fetch_object())
		{
			$data[$r->id] = array(
				'sport_id'=>$r->sport_id,
				'd'=>$r->d,
				't'=>$r->title,
				'ann'=>$r->annotation,
				'srct'=>$r->source_title,
				'srcsite'=>$r->source_site,
				'url'=>$r->url
			);
		}
		return $data;
	}
	
    /**
    * Отображает новости по конкретному виду спорта, сгруппированные по соревнованиям.
    * Опционально принимает агрументы $competitions_limit и $news_limit для ограничения размеров выборки
    * 
    * @param string $sport_id
    * @param integer $competitions_limit
    * @param integer $news_limit
    */
	public function showNewsByCompetitions($sport_id, $competitions_limit=5, $news_limit=5){
		$db = Base::initDB();
		$competitions_limit = (int)$competitions_limit;
        $news_limit = (int)$news_limit;
        if(!isset(Sport::$known_kinds[$sport_id])) return false;

        //получаем список соревнований
		$query = "SELECT `id`, `name`, `logo_sm`
					FROM competitions WHERE sport_id='{$sport_id}' ORDER BY priority LIMIT {$competitions_limit}";
		$res = $db->query($query);
		
		$q = array();
		while($r = $res->fetch_assoc()){
			$comp[$r['id']] = $r;
			$q[] = "(SELECT
					".$r['id']." as comp_id,
					  `n`.`id`,
					  `n`.`sport_id`,
					  DATE_FORMAT(`n`.`date`, '%d.%m.%Y %H:%i') AS `d`,
					  `n`.`title` as `t`,
					  `n`.`annotation` as `ann`,
					  `ns`.`title`     AS `srct`,
					  `ns`.`site`      AS `srcsite`,
					  `n`.`url`
					FROM news n
					  LEFT JOIN news_sources ns
						ON (`n`.`source_id` = `ns`.`id`)
					  INNER JOIN news_tournaments nt
						ON (n.id = nt.news_id
							AND nt.bind_status = 1)
					INNER JOIN tournaments t ON(nt.tournament_id=t.id)
					WHERE t.competition_id='".$r['id']."' AND n.show=1 
					ORDER BY n.date DESC LIMIT ".$news_limit."
					)";
		}

        // выбираем все новости по выбранным выше соревнованиям одним запросом через UNION ALL
        // (быстрее и экономнее, чем запросы в цикле)
		$query = implode(' UNION ALL ', $q);
		$res = $db->query($query);
		$n_ids = array();
		$news_data = array();
		$r = array();
		while($r_news = $res->fetch_assoc()) $r[] = $r_news;

		foreach($r as $row){
			$n_ids[] = $row['id'];
			$news_data[$row['comp_id']][$row['id']] = $row;
		}
		$n_ids_str = implode(',',$n_ids);

        // выбираем связанные команды для всех новостей одним запросом
        // и таким образом опять избавляемся от запросов в цикле
		$teams_query = "SELECT nt.news_id, nt.team_id, t.emblem_ico, t.name
					FROM news_teams nt
					INNER JOIN teams t ON t.id=nt.team_id
					WHERE nt.news_id IN({$n_ids_str}) AND nt.bind_status=1;";

        if(!empty($n_ids)){
            $res = $db->query($teams_query);
            $news = $teams = array();
            while($teams_r = $res->fetch_assoc()){
                if(!isset($news[$teams_r['news_id']])) $news[$teams_r['news_id']] = array();
                $news[$teams_r['news_id']][] = $teams_r['team_id'];
                if(!isset($teams[$teams_r['team_id']]))
                    $teams[$teams_r['team_id']] = array('logo'=>$teams_r['emblem_ico'], 'name'=>$teams_r['name']);
            }
        }

        // теперь осталось только вывести
		foreach($comp as $comp_id=>$comp_data){
			if(!isset($news_data[$comp_id])) continue;
			echo '<img src="'.$comp_data['logo_sm'].'" class="h4" alt=""/> <h4>'.$comp_data['name'].'</h4>';
			echo boxTop('white');
			$i = 0;
			foreach($news_data[$comp_id] as $news_id=>&$ndata)
			{
				echo '<div class="news'.(++$i%2 ? ' m':'').'">';
				echo '<h5>'.$ndata['t'].'</h5>';
				echo ' <span class="date">/'.str_replace(' ', '&nbsp;', $ndata['d']).'/</span>';
				if(isset($news[$news_id]))
				{
					echo '<div class="lteams">';
					foreach($news[$news_id] as $team_id)
						echo '<img title="'.$teams[$team_id]['name'].'" alt="'.$teams[$team_id]['name'].'" title="'.$teams[$team_id]['name'].'" src="'.$teams[$team_id]['logo'].'" /> ';
					echo '</div>';
				}
				if($ndata['ann'])
					echo '<p class="annot">'.$ndata['ann'];
				if($ndata['srct'])
					echo ' <span class="nsource">//&nbsp;<a href="'.($ndata['url'] ? $ndata['url'] : $ndata['srcsite']).'" target="_blank">'.$ndata['srct'].'</a></span>';
				if($ndata['ann'])
					echo '</p>';
				echo '</div>';
			}
			echo '<div class="lcompets"><img src="'.$comp_data['logo_sm'].'" alt=""/> <a href="/news/'.$sport_id.'/'.$comp_id.'/">Все новости для прогнозов на турнир <strong>&laquo;'.$comp_data['name'].'&raquo;</strong></a></div>';
			echo boxBottom('white');
		}	
	}
	
    /**
    * Возвращает новости за определённую дату $date (переданную в формате YYYY-MM-DD).
    * Опционально может принимать вид спорта, для соответствующей фильтрации.
    * 
    * @param integer $num
    * @param integer $skip
    * @param string $date
    * @param string $sport_id
    * 
    * @return Array
    */
	public function getNewsByDate($num=5, $skip=0, $date, $sport_id='')
	{
		$skip = (int)$skip;
		$num = (int)$num;
		if(!preg_match('/^\d{4}\-\d{1,2}\-\d{1,2}$/i', $date)) return array();
		
		$where_clause = " WHERE `news`.`date` >= '{$date} 00:00:00' AND `news`.`date` <= '{$date} 23:59:59' ";
		
        if($sport_id){
            if(isset(Sport::$known_kinds[$sport_id])){
	            $where_clause .= "AND `sport_id`='{$sport_id}' ";
            }else{
                return array();
            }
        }

		$data = array();		
		$dbctrl = Base::initDB();
		$q = "SELECT `news`.`id`, `sport_id`, DATE_FORMAT(`date`, '%d.%m.%Y %H:%i') AS `d`, `news`.`title`, `annotation`,
					`news_sources`.`title` AS `source_title`, `news_sources`.`site` AS `source_site`, `news`.`url`
				FROM `news`
					LEFT JOIN `news_sources` ON (`news`.`source_id` = `news_sources`.`id`) 
				$where_clause AND `news`.`show`=1
				ORDER BY `news`.`date` DESC LIMIT {$skip}, {$num}";
		$result = $dbctrl->query($q);
		while($r = $result->fetch_object())
		{
			$data[$r->id] = array(
				'sport_id'=>$r->sport_id,
				'd'=>$r->d,
				't'=>$r->title,
				'ann'=>$r->annotation,
				'srct'=>$r->source_title,
				'srcsite'=>$r->source_site,
				'url'=>$r->url
			);
		}
		return $data;
	}
	
	/**
	 * Возвращает список из ID турниров, предположительно относящихся к новости с ID=$news_id
	 *
	 * @param integer $news_id
	 *
	 * @return Array
	 */
	public function tryDetermineTournament($news_id){
		$news_id = (int)$news_id;
		$db = Base::initDB();
		
		$r = $db->query("SELECT UNIX_TIMESTAMP(`date`) as `date` FROM news WHERE id='{$news_id}' LIMIT 1")->fetch_assoc();
		
		// далее, пытаемся определить турнир по связанным матчам:
		$games = $this->getGameBinds($news_id);
		$by_game_tournaments = array(); 
		if(!empty($games)){
			foreach($games as $game) $by_game_tournaments[] = $game['tournament_id'];
			//return array_unique($tournaments);
		}
		
		// последний шанс (только если первые два способа ничего не дали): определяем турнир по связанным командам
		$teams = $this->getTeamBinds($news_id);
		$by_team_tournaments = array(); 
		if(!empty($teams)){
			$news_date = $r['date'];
			foreach($teams as $team){
				$tournaments = Teams::getTeamTournaments($team['team_id']);
				foreach($tournaments as $tournament){
					if(($news_date <= $tournament['date_end'] && $news_date >= $tournament['date_start']) || 
						($tournament['date_start']-$news_date >=0 && $tournament['date_start']-$news_date <= (21*24*3600)))
					{	
						$by_team_tournaments[] = $tournament['tournament_id'];		   
					}
				}	
			}
			//return array_unique($news_tournaments);
		}
		
		$result = array_unique(array_merge($by_game_tournaments, $by_team_tournaments));
		if(!empty($result)) return $result;
		
		return false;
	}
	
    /**
    * Пытается определить игры(матчи), относящиеся к конкретной новости,
    * исходя из связанных с ней команд и/или турниров ($mode).
    *
    * Если аргумент $future_month_only равен TRUE (по умолчанию), в выборку попадут только те матчи,
    * которые будут проходить в день новости и в периоде 30 дней после даты новости.
    * 
    * Возвращает список ID игр в случае успешного определения, либо пустой массив.
    * 
    * @param integer $news_id
    * @param string $mode
    * 
    * @return Array
    */
	public function tryDetermineGames($news_id, $mode='teams', $future_month_only=true){
		$news_id = (int) $news_id;
		$db = Base::initDB();
                $get_date_query = "SELECT `date` FROM news WHERE id='$news_id' LIMIT 1";
                $date_res = $db->query($get_date_query)->fetch_assoc();
                $date = $date_res['date'];

                switch($mode){
                    case 'teams':
                        $teams = $this->getTeamBinds($news_id, 1);
                        if(!empty($teams)){
                                foreach($teams as $team) $teams_ids[] = $team['team_id'];
                                $teams_string = implode(',',$teams_ids);
                                $main_query = "SELECT id FROM games WHERE
                                                (games.team_id_home IN ({$teams_string}) OR games.team_id_away IN ({$teams_string}))";
                                if($future_month_only) $main_query .= " AND games.when BETWEEN '{$date}' AND DATE_ADD('{$date}', INTERVAL 30 DAY)";
                        }else{
                                return array();
                        }
                        break;

                    case 'tournaments':
                        $tournaments = $this->getTournamentBinds($news_id, 1);
                        if(!empty($tournaments)){
                            foreach($tournaments as $tournament) $tournaments_ids[] = $tournament['tournament_id'];
                            $tournaments_string = implode(',',$tournaments_ids);
                            $main_query = "SELECT id FROM games WHERE
                                games.tournament_id IN ({$tournaments_string})";
                            if($future_month_only) $main_query .= " AND games.when BETWEEN '{$date}' AND DATE_ADD('{$date}', INTERVAL 30 DAY)";
                        }else{
                            return array();
                        }
                        break;

                    case 'both':
                        $teams = $this->getTeamBinds($news_id, 1);
                        $tournaments = $this->getTournamentBinds($news_id, 1);

                        if(empty($teams) && empty($tournaments)) return array();

                        $teams_clause = '';
                        if(!empty($teams)){
                            foreach($teams as $team) $teams_ids[] = $team['team_id'];
                            $teams_string = implode(',',$teams_ids);
                            $teams_clause = " (games.team_id_home IN ({$teams_string}) OR games.team_id_away IN ({$teams_string})) ";
                        }

                        $tournaments_clause = '';
                        if(!empty($tournaments)){
                            foreach($tournaments as $tournament) $tournaments_ids[] = $tournament['tournament_id'];
                            $tournaments_string = implode(',',$tournaments_ids);
                            $tournaments_clause = " games.tournament_id IN ({$tournaments_string}) ";
                        }

                        $main_query = "SELECT id FROM games WHERE ";
                        if($future_month_only) $main_query .= "games.when BETWEEN '{$date}' AND DATE_ADD('{$date}', INTERVAL 30 DAY) AND ";
                        $main_query .= ($teams_clause && $tournaments_clause) ? ($teams_clause . "AND" . $tournaments_clause) : ($teams_clause ? $teams_clause : $tournaments_clause);
                        break;
                }
                $res = $db->query($main_query);
                $games = array();
                while($r = $res->fetch_assoc()) $games[] = $r['id'];
                return $games;
	}
	
	/*
	public function setTournamentId($news_id, $tournament_id){
		$news_id = (int)$news_id;
		$tournament_id = (int)$tournament_id;
		$db = Base::initDB();
		return $db->query("UPDATE news SET tournament_id='{$tournament_id}' WHERE id='{$news_id}'");		
	}
	*/
	
	public function getNewsCountBySelector(){
		$db = Base::initDB();
		if(!empty($_GET['team']) && $_GET['team']!='all'){
			$team_id = (int)$_GET['team'];
			$query = "SELECT COUNT(*) AS `count` FROM `news_teams` WHERE `news_teams`.`team_id`='{$team_id}'";	
		}else{
			if(empty($_GET['competition'])){
				$clauses = array();
				if(!empty($_GET['date']))
					// $_GET['date'] проходит проверку регулярным выражением (см. в начале /news/index.php)
					$clauses[] = "`news`.`date` >= '{$_GET['date']} 00:00:00' AND `news`.`date` <= '{$_GET['date']} 23:59:59'";
				if(!empty($_GET['sport']))
					$clauses[] = "`news`.`sport_id` = '{$_GET['sport']}'";
				$query = (!empty($clauses)) ? "SELECT COUNT(*) AS `count` FROM `news` WHERE ".implode(" AND ", $clauses) : "SELECT COUNT(*) AS `count` FROM `news`";
			}else{
				$mcache = new mCache('selector');
				if(FALSE === ($competitions = $mcache->get('competitions'))){ 
					$lists = $this->reloadCacheLists();
					$competitions = $lists['competitions'];
				}
				if(isset($competitions[(int)$_GET['competition']])){
					$tids = array();
					foreach($competitions[$_GET['competition']]['tournaments'] as $tournament)
						$tids[] = $tournament['id'];
					$tids_str = implode(',',$tids);
					$query = "SELECT COUNT(*) AS `count` FROM news_tournaments WHERE tournament_id IN({$tids_str})";
				}else{
					return 0;
				}
			}
		}
		$r = $db->query($query)->fetch_assoc();
		return $r['count'];
	}
	
    
    /**
    * Перезаписывает в кэше списки видов спорта, соревнований, турниров, команд, заменяя на актуальные из БД.
    * Возвращает массив из этих списков.
    * 
    * @return Array
    * 
    */
	public function reloadCacheLists(){
		$cache = new mCache('selector');
		$db = Base::initDB();
		$query =
				"
				SELECT
				  `tournaments`.`id`	AS `tournament_id`,
				  `tournaments`.`title` AS `tournament_name`,
				  `competitions`.`id`   AS `competition_id`,
				  `competitions`.`name` AS `competition_name`,
				  `competitions`.`logo_sm` AS `competition_logo`,
				  `sports`.`id`		 AS `sport_id`,
				  `sports`.`name`	   AS `sport_name`
				FROM `tournaments`
				  INNER JOIN `competitions`
					ON (`competition_id` = `competitions`.`id`)
				  INNER JOIN `sports`
					ON (`sport_id` = `sports`.`id`)
				WHERE `competitions`.`id` NOT IN (8)
				";

		$result = $db->fetchObject_arr($query);
		foreach($result as $key=>$obj){
			if(!isset($sports[$obj->sport_id]['name']))
				$sports[$obj->sport_id]['name'] = $obj->sport_name;
			if(!isset($sports[$obj->sport_id]['competitions'][$obj->competition_id]))
			{
				$sports[$obj->sport_id]['competitions'][$obj->competition_id] = array(
					'name' => $obj->competition_name,
					'logo' => $obj->competition_logo
				);
				$competitions[$obj->competition_id] = array(
					'name' => $obj->competition_name,
					'sport_id' => $obj->sport_id,
					'tournaments' => array()
				);
			}
			$sports[$obj->sport_id]['tournaments'][] = array(
				'id'=>$obj->tournament_id,
				'name'=>$obj->tournament_name
			);
			$competitions[$obj->competition_id]['tournaments'][] = array(
				'id'=>$obj->tournament_id,
				'name'=>$obj->tournament_name
			);
			$tournaments[$obj->tournament_id] = array(
				'name' => $obj->tournament_name,
				'sport_id' => $obj->sport_id,
				'competition_id' => $obj->competition_id
			);
		}
		$cache->write('competitions', $competitions);
		$cache->write('tournaments', $tournaments);
		$cache->write('sports', $sports);
		
		$teams_query = "SELECT teams.id, teams.sport_id, teams.city_id, cities.name as city, teams.name
						FROM teams 
						LEFT JOIN cities ON cities.id=teams.city_id
						ORDER BY teams.name";
		$teams_result = $db->query($teams_query);
		while($r = $teams_result->fetch_assoc()) $teams[$r['sport_id']][$r['id']] = array('city'=>$r['city'], 'name'=>$r['name']);
		$cache->write('teams', $teams);
		return array('teams'=>$teams, 'tournaments'=>$tournaments, 'sports'=>$sports, 'competitions'=>$competitions);	
	}	
}
?>