<?php

/**
 * JoomlaWatch - A real-time ajax joomla monitor and live stats
 * @version 1.2.0
 * @package JoomlaWatch
 * @license http://www.gnu.org/licenses/gpl-3.0.txt 	GNU General Public License v3
 * @copyright (C) 2007 by Matej Koval - All rights reserved!
 * @website http://www.codegravity.com
 **/

/** ensure this file is being included by a parent file */
if (!defined('_JEXEC') && !defined('_VALID_MOS'))
    die('Restricted access');

class JoomlaWatchVisit {

    var $database;
    var $config;
    var $helper;
    var $stat;
    var $block;
    var $goal;

    function JoomlaWatchVisit() {
        $this->database = new JoomlaWatchDB();
        $this->config = new JoomlaWatchConfig();
        $this->helper = new JoomlaWatchHelper();
        $this->stat = new JoomlaWatchStat();
        $this->block = new JoomlaWatchBlock();
        $this->goal = new JoomlaWatchGoal();

    }



    /**
     * visitor
     *
     * @return unknown
     */
    function getLastVisitId() {
        $query = sprintf("select #__joomlawatch_uri.id as last from #__joomlawatch left join #__joomlawatch_uri on #__joomlawatch.id = #__joomlawatch_uri.fk where #__joomlawatch.browser is not NULL order by #__joomlawatch_uri.id desc limit 1");
        $last = $this->database->resultQuery($query);
        return $last;
    }

    /**
     * visitor
     *
     * @return unknown
     */
    function deleteOldVisits() {

        $maxRows = $this->config->getConfigValue('JOOMLAWATCH_STATS_MAX_ROWS');
        $today = $this->helper->jwDateToday();

        /** get oldest visitor id in database */
        $query = sprintf("select (max(id)-min(id)) as difference from #__joomlawatch where browser is not null ");
        $difference = $this->database->resultQuery($query);

        /** if the difference between the oldest id-s is less than our max visitors, we do nothing */
        if ($difference < $this->config->getConfigValue('JOOMLAWATCH_LIMIT_VISITORS')) {
            return false;
        }

        for ($i = 0; $i<20; $i++) {
            /** delete records from previous day, which are not in top 20 (or value in maxRows */
            $query = sprintf("SELECT id FROM `#__joomlawatch_info` where `group` = '$i' and date = '%d' order by `value` desc limit %d,99999", (int) ($today -1), (int) $maxRows) ;
            $rows = @ $this->database->objectListQuery($query);
            foreach ($rows as $row) {
                $query = sprintf("delete from `#__joomlawatch_info` where id = '%d' ", (int) $row->id);
                $this->database->executeQuery($query);

                $query = sprintf("delete from `#__joomlawatch_uri` where fk = '%d' ", (int) $row->id);
                $this->database->executeQuery($query);
            }
        }

        $query = sprintf("select id as maxid from #__joomlawatch where browser is not NULL order by id desc limit 1");
        $rows = @ $this->database->objectListQuery($query);
        $row = @ $rows[0];
        $maxidvisitors = @ $row->maxid - $this->config->getConfigValue('JOOMLAWATCH_MAXID_VISITORS');


        /* move to history */
        $query = sprintf("INSERT INTO #__joomlawatch_history SELECT * from #__joomlawatch where id < '%d' and id > (select ifnull(max(id),0) and browser is not null from #__joomlawatch_history ) ", (int) $maxidvisitors);
        $this->database->executeQuery($query);


        /* then delete from original table */
        $query = sprintf("delete from #__joomlawatch where (browser is not NULL and id < '%d') ", (int) $maxidvisitors);
        $this->database->executeQuery($query);

        /* move to history */
        $query = sprintf("INSERT INTO #__joomlawatch_uri_history SELECT * from #__joomlawatch_uri where fk < '%d' and fk > (select ifnull(max(fk),0) from #__joomlawatch_uri_history ) ", (int) $maxidvisitors);
        $this->database->executeQuery($query);
        /* then delete from original table */
        $query = sprintf("delete from #__joomlawatch_uri where fk < '%d' ", (int) $maxidvisitors);
        $this->database->executeQuery($query);

        $maxidbots = @ $row->maxid - $this->config->getConfigValue('JOOMLAWATCH_MAXID_BOTS');

        $query = sprintf("select id from #__joomlawatch where (id < '%d' and browser is NULL) order by id desc", (int) $maxidbots);
        $rows = @ $this->database->objectListQuery($query);

        foreach ($rows as $row) {

            $query = sprintf("delete from #__joomlawatch where id = '%d' ", (int) $row->id);
            $this->database->executeQuery($query);

            $query = sprintf("delete from #__joomlawatch_uri where fk = '%d' ", (int) $row->id);
            $this->database->executeQuery($query);

        }


        if ($this->config->getConfigValue('JOOMLAWATCH_STATS_KEEP_DAYS') != 0) { // 0 = infinite
            $today = $this->helper->jwDateToday();
            $daysToKeep = $today -$this->config->getConfigValue('JOOMLAWATCH_STATS_KEEP_DAYS');

            $query = sprintf("delete from #__joomlawatch_info where date < '%d' ", (int) $daysToKeep);
            $this->database->executeQuery($query);
        }

        /*		//delete all IP records that are less than 1%
                $value = $this->config->getConfigValue('JOOMLAWATCH_STATS_IP_HITS');
                $query = sprintf("DELETE FROM `#__joomlawatch_info` where (`group` = '".DB_KEY_IP."' and date < '%d')", (int) ($today-1));
                $this->database->setQuery($query);
                $this->database->executeQuery($query);


                //delete all IP records that are less than 1%
                $value = $this->config->getConfigValue('JOOMLAWATCH_STATS_IP_HITS');
                $query = sprintf("DELETE FROM `#__joomlawatch_info` where (`group` = '".DB_KEY_IP."' and date < '%d' and value < '%s')", (int) $today, $this->database->getEscaped($value));
                $this->database->setQuery($query);
                $this->database->executeQuery($query);
        */
        //delete from uri2title record older than JOOMLAWATCH_STATS_KEEP_DAYS days
        $count = (JoomlaWatchHelper ::getServerTime() - ( 7 *3600*24)); // 7-day unaccessed records should be deleted
        $query = sprintf("DELETE FROM `#__joomlawatch_uri2title` where (timestamp < '%d') ", (int) $this->database->getEscaped($count));
        $this->database->executeQuery($query);

        //delete from uri2title record older than JOOMLAWATCH_STATS_KEEP_DAYS days
        $count = (JoomlaWatchHelper ::getServerTime() - ( 7 *3600*24));	// 7-day unaccessed records should be deleted
        $query = sprintf("DELETE FROM `#__joomlawatch_internal` where (timestamp < '%d') ", (int) $this->database->getEscaped($count));
        $this->database->executeQuery($query);


        /** delete old history */
        $maxHistorySetting = $this->config->getConfigValue('JOOMLAWATCH_HISTORY_MAX_DB_RECORDS');

        $query = sprintf("SELECT count(id) as count FROM `#__joomlawatch_history` where browser is not null");
        $count = $this->database->resultQuery($query);


        if ($count > $maxHistorySetting) {
            $query = sprintf("SELECT id FROM `#__joomlawatch_history` where browser is not null order by id desc limit %d,%s", $maxHistorySetting, 4294967295); // max unsigned int value

            $rows = @ $this->database->objectListQuery($query);
            foreach ($rows as $row) {

                $query = sprintf("DELETE FROM #__joomlawatch_history where id = '%d' ", (int) ($row->id));
                $this->database->setQuery($query);
                $this->database->query();

                $query = sprintf("DELETE FROM #__joomlawatch_uri_history WHERE fk = '%d' ", (int) ($row->id));
                $this->database->setQuery($query);
                $this->database->query();
            }
        }

        $query = sprintf("DELETE FROM #__joomlawatch_history WHERE browser is null ");
        $this->database->setQuery($query);
        $this->database->query();

        //delete obsolete uri_history entries
        $query = sprintf("DELETE FROM `#__joomlawatch_uri_history` WHERE fk < ( SELECT min( id ) FROM #__joomlawatch_history )");
        $this->database->setQuery($query);
        $this->database->query();

        //delete obsolete uri_history entries
        $query = sprintf("OPTIMIZE TABLE `#__joomlawatch_uri_history`");
        $this->database->setQuery($query);
        $this->database->query();

    }

    function sendNightlyEmails() {

        if ($this->config->getCheckboxValue("JOOMLAWATCH_EMAIL_REPORTS_ENABLED")) {

            $lastEmailDate = $this->config->getConfigValue("JOOMLAWATCH_LAST_EMAIL_DATE");
            $todayDate = $this->helper->jwDateToday();

            if ($lastEmailDate) {
                if ($lastEmailDate != $todayDate) {
                    $joomlaWatch = new JoomlaWatch();
                    $joomlaWatchStatHTML = new JoomlaWatchStatHTML($joomlaWatch);
                    $joomlaWatchStatHTML->sendNightlyEmail();
                    $this->config->saveConfigValue("JOOMLAWATCH_LAST_EMAIL_DATE", $todayDate);
                }
            } else {
                /** if we don't have any last email date, we'll insert it into database */
                $this->config->saveConfigValue("JOOMLAWATCH_LAST_EMAIL_DATE", $todayDate);
            }
        }
    }


    /**
     * extracts phrases by the referer, different search engines
     * @param  $referer
     * @return
     */
    function extractPhraseFromUrl($referer) {
        if (strstr($referer,"q=")) { // keywords from google
            preg_match('![&|?]q=([^&]*)!', urldecode($referer), $matches);
        }
        return trim(strtolower(@$matches[1]));
    }

    /**
     * visitor
     */
    function insertVisit($liveSite) {
        global $mainframe;

        $ip = addslashes(strip_tags(@ $_SERVER['REMOTE_ADDR']));
        $uri = $this->helper->getURI();
        $userObject = @ $mainframe->getUser();
        $newUsername = @ $userObject->username;


        if ($this->config->isIgnored('IP', $ip) || $this->config->isIgnored('URI', $uri) || $this->config->isIgnored('USER', $newUsername)) {
            return true;
        }

        $referer = addslashes(strip_tags(@ $_SERVER['HTTP_REFERER']));
        $referer = strip_tags($referer);
        $sameSite = strstr($referer, $liveSite);

        if (@ !$sameSite) {
            preg_match('@^(?:http://)?([^/]+)@i', $referer, $matches);
            $host = @ $matches[1];
            $this->stat->increaseKeyValueInGroup(DB_KEY_REFERERS, $host);
            $phrase = $this->extractPhraseFromUrl($referer);
            $phrase = str_replace("%2B", "+", $phrase);
            $this->stat->increaseKeyValueInGroup(DB_KEY_KEYPHRASE, $phrase);

            $keywords = explode(' ', $phrase);  //using space instead of + because google has changed it
            foreach ($keywords as $keyword) {

                $keyword = @ trim(strtolower($keyword));
                if ($keyword && strlen($keyword) >= 3) { //keyword leght must be >= as 3
                    $this->stat->increaseKeyValueInGroup(DB_KEY_KEYWORDS, $keyword);
                }
            }
        } else if (@strpos($referer, $liveSite) == 0) {	/* starts with the live site */
            $from = str_replace($liveSite, "", $referer);
            $from = "/".$from; // add / prefix, because live site contains already / and it's replaced

            $query = sprintf("select id from #__joomlawatch_internal where (`from` = '%s' and `to` = '%s') ", $this->database->getEscaped($from), $this->database->getEscaped($uri));
            $id = $this->database->resultQuery($query);

            if (! @$id) {
                $query = sprintf("insert into #__joomlawatch_internal (id, `from`,`to`,`timestamp`) values ('', '%s', '%s', '%d') ", $this->database->getEscaped($from), $this->database->getEscaped($uri), (int) JoomlaWatchHelper::getServerTime());
                $this->database->executeQuery($query);

                $query = sprintf("select id from #__joomlawatch_internal where (`from` = '%s' and `to` = '%s') ", $this->database->getEscaped($from), $this->database->getEscaped($uri));
                $id = $this->database->resultQuery($query);
            }

            $query = sprintf("update #__joomlawatch_internal set `timestamp` = '%d' where (id = '%d') ", $this->helper->getServerTime(), (int) $id);
            $this->database->executeQuery($query);

            $this->stat->increaseKeyValueInGroup(DB_KEY_INTERNAL, $id);

        }

        if ($this->helper->getServerTime() % 10 == 0)  {
            $this->sendNightlyEmails();
            $this->deleteOldVisits();
        }

        $time = $this->helper->getServerTime();

        $count = $this->block->getBlockedIp($ip);
        if (@ $count) {
            $this->block->dieWithBlockingMessage($ip);
        }

        $query = sprintf("select id, username from #__joomlawatch where ip = '%s' limit 1", $this->database->getEscaped($ip));
        $rows = @ $this->database->objectListQuery($query);
        $row = @ $rows[0];
        $id = @ $row->id;
        $username = @ $row->username;

        $title = @ $mainframe->getPageTitle();
        $this->addUri2Title($uri, $title);
        if (!@ $id) {

            $referer = strip_tags($referer);
            $ip = strip_tags($ip);
            $query = sprintf("insert into #__joomlawatch (id, ip, country, browser, referer) values ('', '%s',  NULL, NULL, '%s') ", $this->database->getEscaped($ip), $this->database->getEscaped($referer));
            $this->database->executeQuery($query);

            $query = sprintf("select id from #__joomlawatch where ip = '%s' limit 1", $this->database->getEscaped($ip));
            $rows = @ $this->database->objectListQuery($query);
            $row = @ $rows[0];
            $id = @ $row->id;

            $query = sprintf("insert into #__joomlawatch_uri (id, fk, timestamp, uri, title) values ('', '%d', '%d', '%s', '%s') ", (int) $id, (int) $time, $this->database->getEscaped($uri), $this->database->getEscaped($title));
            $this->database->executeQuery($query);
        } else {
            $query = sprintf("insert into #__joomlawatch_uri (id, fk, timestamp, uri, title) values ('', '%d', '%d', '%s', '%s') ", (int) $id, (int) $time, $this->database->getEscaped($uri), $this->database->getEscaped($title));
            $this->database->executeQuery($query);
        }

        if (($username != $newUsername) && ($newUsername)) {
            $query = sprintf("update #__joomlawatch set username = '%s' where ip = '%s'", $this->database->getEscaped($newUsername), $this->database->getEscaped($ip));
            $this->database->executeQuery($query);
        }

        if (@ $newUsername) {
            $this->stat->increaseKeyValueInGroup(DB_KEY_USERS, $newUsername);
        }

        if ($this->config->getConfigValue('JOOMLAWATCH_IP_STATS')) {
            $this->stat->increaseKeyValueInGroup(DB_KEY_IP, $ip); //add ip watching
        }

        $this->stat->increaseKeyValueInGroup(DB_KEY_HITS, DB_KEY_HITS);

        $this->goal->checkGoals($title, $newUsername, $ip, $referer, $liveSite);


    }

    /**
     * visitor
     */
    function updateVisitByBrowser($uri) {
        $ip = addslashes(strip_tags($_SERVER['REMOTE_ADDR']));
        $userAgent = addslashes(strip_tags(@ $_SERVER['HTTP_USER_AGENT']));

        if ($this->config->isIgnored('IP', $ip) || $this->config->isIgnored('URI', $uri)) {
            return true;
        }

        $this->updateBrowserStats($ip, $userAgent);

        $query = sprintf("select #__joomlawatch_uri.uri from #__joomlawatch left join #__joomlawatch_uri on #__joomlawatch.id = #__joomlawatch_uri.fk  where (#__joomlawatch.ip = '%s' and #__joomlawatch.browser is not null) order by #__joomlawatch_uri.timestamp desc limit 1", $this->database->getEscaped($ip));
        $rows = @ $this->database->objectListQuery($query);
        $row = @ $rows[0];
        $uri = @ $row->uri;

        $this->stat->increaseKeyValueInGroup(DB_KEY_URI, $uri);
        $this->stat->increaseKeyValueInGroup(DB_KEY_LOADS, DB_KEY_LOADS);

    }


    /**
     * Method called from the frontend module
     * visitor
     */
    function updateBrowserStats($ip, $userAgent) {
        $query = sprintf("select id,browser from #__joomlawatch where ip = '%s' order by id asc limit 1", $this->database->getEscaped($ip));
        $rows = @ $this->database->objectListQuery($query);
        $row = @ $rows[0];
        if (@ $row->browser == '')
            $firstTime = true;

        $country = $this->helper->countryByIp($ip);

        $query = sprintf("select browser,country from #__joomlawatch where ip = '%s' order by browser desc limit 1", $this->database->getEscaped($ip));
        $rows = @ $this->database->objectListQuery($query);
        $row = @ $rows[0];

        //check if first time visit
        if (@ !$row->browser) {

            $userAgent = strip_tags($userAgent);
            if (@ $userAgent) {
                // to make sure it's really unique for today
                $this->stat->increaseKeyValueInGroup(DB_KEY_UNIQUE, DB_KEY_UNIQUE);
            }

            /* Googlebot patch identifier: Googlebot-Image/1.0 */
            if (!strstr($userAgent, "Googlebot")) {
                $query = sprintf("update #__joomlawatch set browser = '%s' where ip = '%s'", $this->database->getEscaped($userAgent), $this->database->getEscaped($ip));
                $this->database->executeQuery($query);
            }

            $browser = $this->identifyBrowser(@ $userAgent);
            $this->stat->increaseKeyValueInGroup(DB_KEY_BROWSER, $browser);

            $os = $this->identifyOs(@ $userAgent);
            $this->stat->increaseKeyValueInGroup(DB_KEY_OS, $os);

            $this->stat->increaseKeyValueInGroup(DB_KEY_COUNTRY, $country);

        }

    }

    /**
     * visitor
     */
    function identifyOs($userAgent) {
        if (stristr($userAgent, "Mac"))
            $os = "Mac";
        else
            if (stristr($userAgent, "Linux"))
                $os = "Linux";
            else
                if (stristr($userAgent, "Windows 95"))
                    $os = "Windows98";
                else
                    if (stristr($userAgent, "Windows 98"))
                        $os = "Windows98";
                    else
                        if (stristr($userAgent, "Windows ME"))
                            $os = "Windows98";
                        else
                            if (stristr($userAgent, "Windows NT 4.0"))
                                $os = "WindowsNT";
                            else
                                if (stristr($userAgent, "Windows NT 5.1"))
                                    $os = "WindowsXP";
                                else
                                    if (stristr($userAgent, "Windows NT 6.0"))
                                        $os = "WindowsVista";
                                    else
                                        if (stristr($userAgent, "Windows NT 6.1"))
                                            $os = "Windows7";
                                        else
                                            if (stristr($userAgent, "Windows"))
                                                $os = "Windows";

        return @ $os;
    }

    /**
     * visitor
     */
    function identifyBrowser($userAgent) {
        if (stristr($userAgent, "Chrome"))
            $browser = "Chrome";
        else
            if (stristr($userAgent, "Safari"))
                $browser = "Safari";
            else
                if (stristr($userAgent, "MSIE"))
                    $browser = "Explorer";
                else
                    if (stristr($userAgent, "Firefox"))
                        $browser = "Firefox";
                    else
                        if (stristr($userAgent, "Opera"))
                            $browser = "Opera";
                        else
                            if (stristr($userAgent, "Mozilla"))
                                $browser = "Mozilla";

        return @ $browser;
    }

    /**
     * visitor
     */
    function getBrowserByIp($ip) {
        $query = sprintf("select browser from #__joomlawatch where (ip = '%s' and browser is not NULL) order by browser desc limit 1", $this->database->getEscaped($ip));
        $browser = $this->database->resultQuery($query);
        return $browser;
    }

    /**
     * visitor
     */
    function getBots() {
        $limit = 0;
        $limit = $this->config->getConfigValue('JOOMLAWATCH_LIMIT_BOTS');
        $query = sprintf("select ip, referer, username from #__joomlawatch where (browser is NULL) order by id desc limit %d", (int) $limit);
        $rows = $this->database->objectListQuery($query);
        return $rows;

    }

    /**
     * visitor
     */
    function getVisitors() {
        $limit = 0;
        $limit = $this->config->getConfigValue('JOOMLAWATCH_LIMIT_VISITORS');
        $query = sprintf("select ip, referer, username from #__joomlawatch where (browser is not NULL and browser != '') order by id desc limit %d", (int) $limit);
        $rows = $this->database->objectListQuery($query);
        return $rows;
    }

    /**
     * visitor
     */
    function getJoinedURIRows($ip) {
        $query = sprintf("SELECT * FROM #__joomlawatch LEFT JOIN #__joomlawatch_uri ON #__joomlawatch.id = #__joomlawatch_uri.fk where ip = '%s' ORDER BY #__joomlawatch_uri.timestamp desc", $this->database->getEscaped($ip));
        $rows = $this->database->objectListQuery($query);
        return $rows;
    }

    /**
     * visitor
     */
    function getJoinedURIRowById($id) {
        $query = sprintf("SELECT * FROM #__joomlawatch LEFT JOIN #__joomlawatch_uri ON #__joomlawatch.id = #__joomlawatch_uri.fk where #__joomlawatch_uri.id = '%d' ORDER BY #__joomlawatch_uri.timestamp desc", (int) $id);
        $rows = $this->database->objectListQuery($query);
        return $rows;
    }

    /**
     * visitor
     */
    function getInternalNameById($id) {
        $query = sprintf("select `from`,`to` from #__joomlawatch_internal where id = '%d'", (int) $id);
        $rows = @ $this->database->objectListQuery($query);
        $row = @$rows[0];
        return $row;
    }

    /**
     * visitor
     */
    function addUri2Title($uri, $title) {
        $title = htmlspecialchars($title);
        $uri = htmlspecialchars($uri);
        $this->increaseUri2TitleCount($uri);

        $query = sprintf("select id from #__joomlawatch_uri2title where (`uri` = '%s') limit 1 ", $this->database->getEscaped($uri), $this->database->getEscaped($title));
        $id = $this->database->resultQuery($query);

        if (!@$id) {
            $query = sprintf("insert into #__joomlawatch_uri2title (id, uri, title, count, timestamp) values ('','%s','%s',1,'%d') ", $this->database->getEscaped($uri), $this->database->getEscaped($title), (int) JoomlaWatchHelper::getServerTime());
            $this->database->executeQuery($query);
        }
    }

    /**
     * visitor
     */
    function getTitleByUri($uri) {
        $query = sprintf("select title from #__joomlawatch_uri2title where (`uri` = '%s') limit 1 ", $this->database->getEscaped($uri));
        $title = $this->database->resultQuery($query);
        return $title;
    }

    /**
     * visitor
     */
    function increaseUri2TitleCount($uri) {
        $query = sprintf("update #__joomlawatch_uri2title set count = count+1, timestamp = '%d' where (`uri` = '%s')", (int) JoomlaWatchHelper::getServerTime(), $this->database->getEscaped($uri));
        $this->database->executeQuery($query);
    }


}

?>