<?php
/*
 * Created on 07.03.2012
 * Oliver Soop(osoop1@ut.ee)
 */
 include_once ("URLDatabase.php");
 
 class URLDatabaseService {

	/**
	 * Funktsioon kontrollimaks, kas etteantud url-ist on k�ivitatud RSS voogude roomamist
	 * Kontrollitakse tabelist ekktt_crawled_urls_for_feeds
	 * $url - väärtus, kus tahetakse RSS voogude roomamist teostada
	 */
	public static function checkIfUrlIsCrawled($url) {
		

		$db = URLDatabase :: 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()) {
				$mysqli->close();
				return;
			};
			$stmt->fetch();
			$stmt->bind_result($count);
			$stmt->close();
			$mysqli->close();
			if ($count > 0) {
				echo "tõene";
				return true;
			}
			return false;
		}
		$mysqli->close();
	}

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

		$db = URLDatabase :: 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();
		}
		echo $mysqli->error;
		$mysqli->close();

	}



	
	/**
	 * Funktsioon lisamaks andmebaasi tabelisse ekktt_urls_to_be_crawled uus URL, mida peaks roomama
	 * $url - väärtus, kus RSS-voogude roomamist tuleb teostada
	 */
	public static function addCrawlableUrl($url) {

		$db = URLDatabase :: 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)) {
			$stmt->bind_param("s", $url);
			if (!$stmt->execute()) {
				$mysqli->close();
				return;
			};
			$stmt->close();
		}
		$mysqli->close();
	}
	
	/**
	 * Funktsioon, mis tagastab etteantud vahemikust URL-id, mida pole veel roomatud
	 * $start - algus
	 * $end - URL-ide arv
	 */
	public static function getCrawlableUrls($start, $end) {

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

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

		if ($stmt = $mysqli->prepare($sql)) {
			$stmt->bind_param("ii", $start, $end);
			if (!$stmt->execute()) {
				$mysqli->close();
				return;
			};
			$stmt->bind_result($url);
			$urls = array ();
			while ($stmt->fetch()) {
				array_push($urls, $url);
			}
			$stmt->close();
			$mysqli->close();
			return $urls;
		}
		$mysqli->close();
	}
	
	/**
	 * Funktsioon seadmaks roomatavate URL-ide nimekirjas oleva URL-i lippu, mis määrab, et URL on roomatud,
	 * samuti salvestatakse sisendi korral ka leitud URL-ide arv.
	 */
	public static function setUrlCrawled($url, $countOfFeeds) {

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

		$sql = "UPDATE ekktt_urls_to_be_crawled SET crawled = 1, amount_of_urls = ?, crawled_date = current_date WHERE url = ?";
		echo "test1";
		if ($stmt = $mysqli->prepare($sql)) {
			$stmt->bind_param("is", $countOfFeeds, $url);
			if (!$stmt->execute()) {
				$mysqli->close();
				return;
			};
			echo "test2";
			$stmt->close();
		}
		$mysqli->close();
	}
	
	/**
	 * Funktsioon, mille antakse ette url, mis eemaldada roomamata või roomatud sisend URL-ide hulgast
	 */
	public static function removeCrawlableUrl($url) {

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

		$sql = "DELETE FROM ekktt_urls_to_be_crawled WHERE url = ?";

		if ($stmt = $mysqli->prepare($sql)) {
			$stmt->bind_param("s", $url);
			if (!$stmt->execute()) {
				$mysqli->close();
				return;
			};
			$stmt->close();
		}
		$mysqli->close();
	}
	
	/**
	 * Funktsioon, mis tagastab andmebaasis olevate roomamata URL-ide arvu
	 * (URL-id, mis on antud sisendiks ning, kus alustatakse RSS voogude otsimist)
	 */
	public static function getAmountOfCrawlableUrls() {

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

		$sql = "SELECT COUNT(*) FROM ekktt_urls_to_be_crawled WHERE crawled = false";

		if ($stmt = $mysqli->prepare($sql)) {
			if (!$stmt->execute()) {
				$mysqli->close();
				return;
			};
			$stmt->bind_result($count);
			if(!$stmt->fetch()){
				echo "Tekkis probleem päringu käivitamisel";
			};
			$stmt->close();
			$mysqli->close();
			return $count;
		}
		$mysqli->close();
	}	

}
?>
