<?php

/*
 * Created on 10.03.2012
 * Oliver Soop(osoop1@ut.ee)
 */
include_once ("Database.php");
class RSSDatabaseService {

	/**
	 * Funktsioon lisamaks RSS-voo URL-i andmebaasi, tabelisse ekktt_rss_feed_urls
	 */
	public static function addFeedUrl($url) {

		$db = Database :: getInstance();
		$db->connect();
		$mysqli = $db->getResource();

		$sql = "INSERT INTO ekktt_rss_feed_urls(url, insert_date) VALUES (?,current_date)";

		if ($stmt = $mysqli->prepare($sql)) {
			$stmt->bind_param("s", $url);
			if (!$stmt->execute()) {
				$mysqli->close();
				return;
			};
			$stmt->close();
		}
		$mysqli->close();

	}

	/**
	 * Meetod kontrollimaks, kas etteantud URL-ist on k�ivitatud RSS voogude roomamist
	 * Kontrollitakse tabelist ekktt_crawled_urls_for_feeds
	 */
	public static function checkIfUrlIsCrawled($url) {

		$db = Database :: getInstance();
		$db->connect();
		$mysqli = $db->getResource();

		$sql = "SELECT COUNT(*) FROM ekktt_crawled_urls_for_feeds WHERE url = ?";

		if ($stmt = $mysqli->prepare($sql)) {
			$stmt->bind_param("s", $url);
			if (!$stmt->execute()) {
				return;
			};
			$stmt->bind_result($count);
			$stmt->close();
			if ($count > 0) {
				$mysqli->close();
				return true;
			}
			$mysqli->close();
			return false;
		}
		$mysqli->close();
	}

	/**
	 * Funktsioon lisamaks andmebaasi tabelisse ekktt_crawled_urls_for_feeds uus URL, kus on
	 * k�ivitatud RSS-voogude roomamine
	 */
	public static function addCrawledUrl($url) {

		$db = Database :: getInstance();
		$db->connect();
		$mysqli = $db->getResource();

		$sql = "INSERT INTO ekktt_crawled_urls_for_feeds(url, crawled_date, insert_date) VALUES(?,	current_date, current_date)";

		if ($stmt = $mysqli->prepare($sql)) {
			$stmt->bind_param("s", $url);
			if (!$stmt->execute()) {
				$mysqli->close();
				return;
			};
			$stmt->close();
		}
		$mysqli->close();

	}

	/**
	 * Funktsioon pärimaks kõikide andmebaasis eksisteerivate RSS-voogude URL-id
	 */
	public static function getRSSFeedUrls() {
		$db = Database :: getInstance();
		$db->connect();
		$mysqli = $db->getResource();

		$sql = "SELECT url FROM ekktt_rss_feed_urls";

		if ($stmt = $mysqli->prepare($sql)) {
			if (!$stmt->execute()) {
				$stmt->close();
				$mysqli->close();
				return null;
			};
			$stmt->bind_result($url);

			$urls = array ();
			while ($stmt->fetch()) {
				array_push($urls, $url);
			}
			$stmt->close();
			$mysqli->close();
			return $urls;
		}
	}

	/**
	 * Funktsioon seadmaks andmebaasis RSS-voo roomamise kuupäeva hetke ajale
	 */
	public static function setCrawlDateToCurrent($url) {
		$db = Database :: getInstance();
		$db->connect();
		$mysqli = $db->getResource();

		$sql = "UPDATE ekktt_rss_feed_urls SET crawled_date = current_date() WHERE url = ?";

		if ($stmt = $mysqli->prepare($sql)) {
			$stmt->bind_param("s", $url);
			if (!$stmt->execute()) {
				$stmt->close();
				$mysqli->close();
				return false;
			}
			$stmt->close();
			$mysqli->close();
			return true;
		}
	}

	/**
	 * Funktsioon lisamaks uut uudisobjekti andmebaasi
	 */
	public static function insertNewArticle($story) {
		$db = Database :: getInstance();
		$db->connect();
		$mysqli = $db->getResource();
		
		$sql = "INSERT INTO ekktt_article(article_local_id, 
											url, 
											category_name, 
											channel_id, 
											lang_id, 
											title, 
											lead, 
											content, 
											author, 
											publish_date,
											state, 
											is_article_full, 
											html) VALUES(?,?,?,'RSS',?,?,?,?,?,?,0,?,?)";
		if ($stmt = $mysqli->prepare($sql)) {
			
			$nl = 0;
			
			if($story->content() == null) {
				$story->content($story->desc());
			}
			date_default_timezone_set("Europe/Helsinki"); 
			
			$stmt->bind_param("isssissssss", 
			$story->id(), $story->link(), $story->category(), $story->language(), $story->title(),
			$story->desc(), $story->content(), 
			$story->author(), date('Y-m-d H:i:s', strtotime($story->pubDate())), $story->fullArticle(), $story->html());
			//Siin kuupäevaga mingi kala
			if (!$stmt->execute()) {
				$stmt->close();
				$mysqli->close();
				return false;
			}
			$mysqli->close();
			$stmt->close();
			return true;
		}
		$mysqli->close();
		return false;
		
	}
	
	/**
	 * Funktsioon lisamaks andmebaasi tabelisse ekktt_urls_to_be_crawled uus URL, mida peaks roomama
	 */
	public static function addCrawlableUrl($url) {

		$db = Database :: getInstance();
		$db->connect();
		$mysqli = $db->getResource();

		$sql = "INSERT INTO ekktt_urls_to_be_crawled(url, insert_date) VALUES(?, current_date)";

		if ($stmt = $mysqli->prepare($sql)) {
			echo "lisame ühe juurde<br/><br/><br/>";
			$stmt->bind_param("s", $url);
			if (!$stmt->execute()) {
				$mysqli->close();
				return;
			};
			$stmt->close();
		}
		$mysqli->close();
	}
	
	/**
	 * Funktsioon, mis tagastab andmebaasist URL-id mida peaks roomama ning mis kuuluvad etteantud vahemiku
	 */
	public static function getCrawlableUrls($start, $end) {

		$db = Database :: getInstance();
		$db->connect();
		$mysqli = $db->getResource();
		$end = $end - $start;

		$sql = "SELECT url FROM ekktt_rss_feed_urls WHERE crawled = 0 LIMIT ?,?";

		if ($stmt = $mysqli->prepare($sql)) {
			$stmt->bind_param("ii", $start, $end);
			if (!$stmt->execute()) {
				$mysqli->close();
				return;
			};
			$urls = array();
			$stmt->bind_result($url);
			while($stmt->fetch()) {
				array_push($urls, $url);
			}
			$mysqli->close();
			$stmt->close();
			return $urls;
		}
	}
	
	/**
	 * Funktsioon, mis tagastab andmebaasis olevate RSS voogude arvu
	 */
	public static function getAmountOfRSSs() {
		$db = Database :: getInstance();
		$db->connect();
		$mysqli = $db->getResource();

		$sql = "SELECT count(*) FROM ekktt_rss_feed_urls";

		if ($stmt = $mysqli->prepare($sql)) {
			if (!$stmt->execute()) {
				return;
			};
			$stmt->bind_result($count);
			if(!$stmt->fetch()){
				echo "Tekkis probleem päringu käivitamisel";
			};
			$stmt->close();
			$mysqli->close();
			return $count;
		}
	}
	
	/**
	 * Funktsioon, mis tagastab roomamata sisend URL-ide arvu
	 */
	public static function getAmountOfCrawlableRSSs() {
		$db = Database :: getInstance();
		$db->connect();
		$mysqli = $db->getResource();

		$sql = "SELECT count(*) FROM ekktt_rss_feed_urls WHERE crawled = 0";

		if ($stmt = $mysqli->prepare($sql)) {
			if (!$stmt->execute()) {
				return;
			};
			$stmt->bind_result($count);
			if(!$stmt->fetch()){
				echo "Tekkis probleem päringu käivitamisel";
			};
			$stmt->close();
			$mysqli->close();
			return $count;
		}
	}
	
	/**
	 * Funktsioon, millega saab märkida RSS-voogude URL-id mitteroomatuks
	 */
	public static function setRSSFeedsUncrawled() {
		$db = Database :: getInstance();
		$db->connect();
		$mysqli = $db->getResource();

		$sql = "UPDATE ekktt_rss_feed_urls SET crawled = 0 WHERE crawled = 1;";

		if ($stmt = $mysqli->prepare($sql)) {
			if (!$stmt->execute()) {
				$mysqli->close();
				return;
			};
			$stmt->close();
			$mysqli->close();
		}
	}

	/**
	 * Funktsioon kontrollimaks, kas etteantud uudisobjekt eksisteerib juba andmebaasis
	 */
	public static function checkIfStoryExists($storyHeader) {

		$db = Database :: getInstance();
		$db->connect();
		$mysqli = $db->getResource();

		$sql = "SELECT count(*) FROM ekktt_article WHERE title = ?";

		if ($stmt = $mysqli->prepare($sql)) {
			$stmt->bind_param("s", $storyHeader);
			if (!$stmt->execute()) {
				$mysqli->close();
				return;
			};
			$stmt->bind_result($count);
			if(!$stmt->fetch()){
				echo "Tekkis probleem päringu käivitamisel";
			};
			$stmt->close();
			$mysqli->close();
			if($count > 0) {
				return true;
			}
			return false;
		}
	}	
	
	/**
	 * Funktsioon, millega saab märkida RSS-voo roomatuks
	 */
	public static function setFeedCrawled($url) {
		$db = Database :: getInstance();
		$db->connect();
		$mysqli = $db->getResource();

		$sql = "UPDATE ekktt_rss_feed_urls SET crawled = 1, crawled_date = current_date WHERE url = ?";

		if ($stmt = $mysqli->prepare($sql)) {
			$stmt->bind_param("s", $url);
			if (!$stmt->execute()) {
				$mysqli->close();
				return;
			};
			$stmt->close();
		}
		$mysqli->close();
	}	

}
?>
