<?php

include_once("Zend/Feed/Rss.php");

class News {

    // when set to 1, script will echo out queries and other data
    var $debug             = 0;

    // array of feeds available - maps to `news_feeds` in database
    private $feeds        = array();

    // array of articles returned after NEWS::loadArticles() is called
    var $articles        = array();

    // used for paging - number of records returned and total records available
    // after a query is performed using NEWS::loadArticles()
    var $num_records    = 0;
    var $total_records    = 0;

    // used in front-end to determine the title of the category specified
    // i.e. $_REQUEST['category'] = 'css-and-xhtml'
    // -> after NEWS::loadArticles() is called, NEWS::category_title = 'CSS and XHTML'
    var $category_title    = '';

    function __construct () {

        $this->loadFeeds();

    }

    function loadFeeds () {

        $this->feeds = array();

        $sql = "SELECT * FROM `news_feeds` ORDER BY `title` ASC";
        $query = mysql_query ($sql) or die(mysql_error());

        while ($row = mysql_fetch_assoc($query)) {

            $this->feeds[] = $this->cleanRow($row);

        }

    }

    // populates news object with articles matching a query
    // $arr is a hash specifying the parameters to search
    function loadArticles ($arr, $limit = '', $offset = '') {

        // reset object
        $this->articles = array();
        $this->num_records = 0;
        $this->total_records = 0;
        $this->category_title = '';

        // sanitize array before building query
        if (is_array($arr)) {

            foreach ($arr as $key=>$value) {
                $arr[$key] = mysql_real_escape_string($value);
            }

        }

        if ($arr['category']) {

            // get all categories and attempt to match against one passed in $arr
            $sql = "SELECT * FROM `news_categories`";
            $query = mysql_query ($sql);

            while ($row = mysql_fetch_assoc($query)) {

                // format title, i.e. CSS and XHTML becomes css-and-xhtml
                $category = $this->rewriteFormat(stripslashes($row['title']));

                if ($arr['category'] == $category) {

                    // match found - use category_id in query and set the object's category_title
                    $where[] = "t2.`category_id`='" .$row['id']. "'";
                    $this->category_title = stripslashes($row['title']);

                }

            }

        }

        // article published on given year
        if ($arr['year']) {

            $where[] = "YEAR(t1.`date`)='" .$arr['year']. "'";

        }

        // use 3 letter month for clarity - find articles published on given month
        if ($arr['month']) {

            $months = array('jan'=>1, 'feb'=>2, 'mar'=>3, 'apr'=>4,
                            'may'=>5, 'jun'=>6, 'jul'=>7, 'aug'=>8,
                            'sep'=>9, 'oct'=>10, 'nov'=>11, 'dec'=>12);

            $where[] = "MONTH(t1.`date`)='" .$months[$arr['month']]. "'";

        }

        // find articles published on given day of month
        if ($arr['day']) {

            $where[] = "DAY(t1.`date`)='" .$arr['day']. "'";

        }

        // scan title of article - not in use currently
        if ($arr['title']) {

            $where[] = "t1.`rewrite`='" .$arr['title']. "'";

        }

        // match query against article title
        if ($arr['query']) {

            $where[] = "t1.`title` LIKE '%" .$arr['query']. "%'";

        }

        // build WHERE portion of query
        if ($where) {

            $where = " WHERE (" .implode(") AND (", $where). ")";

        } else {

            $where = "";

        }

        // the query
        $sql = "SELECT SQL_CALC_FOUND_ROWS t1.*, t3.title AS `feed`, t3.url AS `feed_url`
                    FROM `news_articles` AS t1

                INNER JOIN `news_articles_categories_rel` AS t2
                    ON t1.id = t2.article_id

                INNER JOIN `news_feeds` AS t3
                    ON t1.feed_id = t3.id

                " .$where. "

                GROUP BY t1.id

                ORDER BY t1.date DESC, t1.title ASC";

        // allow for paging
        if ($limit) {
            $sql .= "
                     LIMIT " .$limit;
        }

        if ($offset) {
            $sql .= "
                     OFFSET " .$offset;
        }

        $this->debug($sql);

        $query = mysql_query ($sql);

        // results found -> populate object
        if (mysql_num_rows($query)) {

            $this->num_records = mysql_num_rows($query);

            $this->total_records = mysql_result(mysql_query("SELECT FOUND_ROWS()"), 0);

            while ($row = mysql_fetch_assoc($query)) {

                $this->articles[] = $this->cleanRow($row);

            }

        }

    }

    function downloadArticles ($strip_html = true) {

        // cron function - this populates the database

        // loop through the feeds and figure out what categories they feed into
        // and what filter is applied to the articles before insertion into that
        // category
        foreach ($this->feeds as $feed) {

            // go ahead and download the feed now
            $channel = new Zend_Feed_Rss($feed['url']);

            $this->debug("Processing feed: " .$channel->title. " (id: " .$feed['id']. ")");

            // loop through each feed item
            foreach ($channel as $item) {

                // clean up the feed data for insertion into database
                // some titles have text in-between brackets -- get rid of it
                $title             = trim(mysql_real_escape_string(preg_replace("/\[.*\]/", "", $item->title)));
                $content        = $item->description;

                // remove img, embed, input, script, small tags
                if ($strip_html) {
                    $content = preg_replace('!(<img[^>]*>|<embed[^>]*>|<input[^>]*>|<script[^>]*>|<small[^>]*>|</img>|</embed>|</input>|</script>|</small>)?!', '', $content);
                }

                $content         = mysql_real_escape_string($content);
                $link             = mysql_real_escape_string($item->link);
                $date             = date("Y-m-d H:i:s", strtotime($item->pubDate));

                $this->debug("Processing article: " .$item->title);

                // does the article already exist in the database?
                $sql = "SELECT * FROM `news_articles` WHERE `title`='" .$title. "'";
                $query = mysql_query ($sql);

                if (mysql_num_rows($query) == 0) {

                    $this->debug('<span style="color: #080;">Article does not already exist in database!</span>');

                    // unset the categories array -> this array tells what categories to insert
                    // the article into
                    $categories = array();


                    $sql = "SELECT * FROM `news_feeds_categories_rel`
                            WHERE `feed_id`='" .$feed['id']. "'";

                    $query = mysql_query ($sql);

                    // figure out what categories to insert article into and check against filters if exist
                    while ($row = mysql_fetch_assoc($query)) {

                        // relevant data here is
                        // category_id and filter
                        $row = $this->cleanRow($row);

                        $pass = true;

                        // filter exists, check against
                        if ($row['filter_id'] > 0) {

                            $this->debug('<span style="color: #777;">Checking against filter: '.$row['filter_id']. '</span>');

                            // get list of keywords
                            $sql = "SELECT * FROM `news_filters_keywords` WHERE `filter_id`='" .$row['filter_id']. "'";
                            $keyword_query = mysql_query ($sql);

                            if (mysql_num_rows($keyword_query)) {

                                $pass = false;

                                while ($keyword_row = mysql_fetch_assoc($keyword_query)) {

                                    $keyword = stripslashes($keyword_row['keyword']);

                                    if (preg_match('/\b' .$keyword. '\b/i', $title) || preg_match('/\b' .$keyword. '\b/i', $content)) {

                                        $this->debug('<span style="color: #080;">Match found on keyword: '.$keyword. '</span>');
                                        $pass = true;

                                    }

                                }

                            }

                        }

                        if ($pass == true) {

                            // article will be inserted into the category
                            $categories[] = $row['category_id'];

                        }

                    }

                    // inserted into 1 or more categories, so add to database
                    if (count($categories)) {

                        $this->debug('<span style="color: #080;">Inserting article to database</span>');

                        $sql = "INSERT INTO `news_articles`
                                SET
                                    `feed_id`='" .$feed['id']. "',
                                    `title`='" .$title. "',
                                    `content`='" .$content. "',
                                    `link`='" .$link. "',
                                    `date`='" .$date. "',
                                    `rewrite`='" .$this->rewriteFormat(stripslashes($title)). "'";
                        $query = mysql_query ($sql) or die(mysql_error());

                        $article_id = mysql_insert_id();

                        foreach ($categories as $category_id) {

                            $this->debug('<span style="color: #080;">Inserting article to category: ' .$category_id. '</span>');

                            $sql = "INSERT INTO `news_articles_categories_rel`
                                    SET
                                        `article_id`='" .$article_id. "',
                                        `category_id`='" .$category_id. "'";
                            $query = mysql_query ($sql) or die(mysql_error());

                        }

                        $inserts++;

                    }

                }

            }

        }

        $this->debug('<strong>DONE</strong> Inserted: ' .$inserts. ' articles');

        return true;

    }

    function deleteOldArticles () {

        // delete articles a year old
        $cutoff = mktime(0, 0, 0, date("n"), date("j"), date("Y") - 1);

        $sql = "DELETE FROM `news_articles` WHERE UNIX_TIMESTAMP(`date`) < " .$cutoff;
        $query = mysql_query ($sql);

    }

    function getCategories () {

        $sql = "SELECT t1.*, COUNT(t2.`id`) AS `count`
                FROM `news_categories` AS t1
                LEFT JOIN `news_articles_categories_rel` AS t2
                ON t1.id = t2.category_id
                GROUP BY t1.id
                ORDER BY t1.`title` ASC
                ";
        $query = mysql_query ($sql);

        while ($row = mysql_fetch_assoc($query)) {

            $categories[] = $this->cleanRow($row);

        }

        return $categories;

    }

    function getFilters () {

        $sql = "SELECT t1.*, COUNT(t2.`filter_id`) AS `count`
                FROM `news_filters` AS t1
                INNER JOIN `news_filters_keywords` AS t2
                ON t1.id = t2.filter_id
                GROUP BY t1.id
                ORDER BY t1.`title` ASC";
        $query = mysql_query ($sql);

        while ($row = mysql_fetch_assoc($query)) {

            $filters[] = $this->cleanRow($row);

        }

        return $filters;

    }

    function getFeeds () {

        return $this->feeds;

    }

    function cleanRow ($row) {

        // remove slashes from array
        foreach ($row as $key=>$value) {
            $row[$key] = stripslashes($value);
        }
        return $row;
    }

    function rewriteFormat ($str) {

        // clean up string and make it URL-friendly
        $str = strtolower(str_replace(" ", "-", $str));
        $str = preg_replace('/[^A-Za-z0-9-]/i', '', $str);

        return $str;

    }

    function debug ($s) {

        if (!$this->debug) return;

        echo '<pre>';

        if (is_array($s)) {
            print_r($s);
        } else {
            echo $s;
        }

        echo '</pre>';

    }

}

?>