<?PHP

class MetricsRecorder extends Plugin {

    function __construct()
    {
        $this->DB = new Database();
    }

    function Register()
    {
        $this->Name = "Metrics Recorder";
        $this->Version = "1.1.4";
        $this->Description = "CWIS plugin for recording usage and web metrics data.";
        $this->Author = "Internet Scout";
        $this->Url = "http://scout.wisc.edu/cwis/";
        $this->Email = "scout@scout.wisc.edu";
        $this->Requires = array(
                "CWISCore" => "2.2.3",
                "BotDetector" => "1.0.0");
        $this->EnabledByDefault = FALSE;
    }

    function Install()
    {
        $Queries = array(
                "CREATE TABLE IF NOT EXISTS MetricsRecorder_SampleData (
                    SampleDate      TIMESTAMP,
                    SampleType      SMALLINT NOT NULL,
                    SampleValue     INT,
                    INDEX           (SampleDate,SampleType));",
                "CREATE TABLE IF NOT EXISTS MetricsRecorder_EventData (
                    EventId         INT NOT NULL AUTO_INCREMENT,
                    EventDate       TIMESTAMP,
                    EventType       SMALLINT NOT NULL,
                    UserId          INT,
                    IPAddress       INT UNSIGNED,
                    DataOne         TEXT,
                    DataTwo         TEXT,
                    INDEX           Index_I (EventId),
                    INDEX           Index_TD (EventType,EventDate),
                    INDEX           Index_TUD (EventType,UserId,EventDate),
                    INDEX           Index_T (EventType));",
                "CREATE TABLE IF NOT EXISTS MetricsRecorder_EventAnnotations (
                    EventId         INT NOT NULL,
                    AnnotatorId     SMALLINT,
                    Annotation      TEXT,
                    INDEX           (EventId));",
                "CREATE TABLE IF NOT EXISTS MetricsRecorder_Annotators (
                    AnnotatorId     SMALLINT NOT NULL AUTO_INCREMENT,
                    AnnotatorName   TEXT,
                    INDEX           (AnnotatorId));",
                );
        foreach ($Queries as $Query)
        {
            $Result = $this->DB->Query($Query);
            if ($Result === FALSE) {  break;  }
        }
        return ($Result === FALSE) ? "Database setup failed." : NULL;
    }

    function Upgrade($PreviousVersion)
    {
        # if previously-installed version is earlier than 1.1.4
        if (version_compare($PreviousVersion, "1.1.4", "<"))
        {
            # replace old Type/User index with Type/User/Date index for event data
            $DB = new Database();
            $DB->SetQueryErrorsToIgnore(array(
                    '/DROP\s+INDEX\s+[^\s]+\s+\([^)]+\)/i'
                            => '/Can\'t\s+DROP\s+[^\s]+\s+check\s+that/i'));
            $DB->Query("DROP INDEX EventType_2 ON MetricsRecorder_EventData");
            $DB->Query("CREATE INDEX Index_TUD ON MetricsRecorder_EventData"
                    ." (EventType,UserId,EventDate)");
        }
    }

    function DeclareEvents()
    {
        return array(
                "MetricsRecorder_EVENT_RECORD_EVENT"
                        => ApplicationFramework::EVENTTYPE_NAMED,
                );
    }

    function HookEvents()
    {
        return array(
                "EVENT_DAILY" => "RecordDailySampleData",
                "EVENT_USER_LOGIN" => "RecordUserLogin",
                "EVENT_USER_ADDED" => "RecordNewUserAdded",
                "EVENT_USER_VERIFIED" => "RecordNewUserVerified",
                "EVENT_SEARCH_COMPLETE" => "RecordSearch",
                "EVENT_OAIPMH_REQUEST" => "RecordOAIRequest",
                "EVENT_FULL_RECORD_VIEW" => "RecordFullRecordView",
                "EVENT_URL_FIELD_CLICK" => "RecordUrlFieldClick",
                );

    }

    /**
    * Record periodically-sampled data.  (HOOKED METHOD)
    * @param LastRunAt Timestamp when method was last called.
    */
    function RecordDailySampleData($LastRunAt)
    {
        # record total number of registered users
        $UserFactory = new UserFactory($this->DB);
        $this->RecordSample(self::ST_REGUSERCOUNT, $UserFactory->GetUserCount());

        # record number of privileged users
        $this->RecordSample(self::ST_PRIVUSERCOUNT,
                count($UserFactory->GetUsersWithPrivileges(
                        PRIV_SYSADMIN, PRIV_NEWSADMIN, PRIV_RESOURCEADMIN,
                        PRIV_FORUMADMIN, PRIV_CLASSADMIN, PRIV_NAMEADMIN,
                        PRIV_USERADMIN, PRIV_COLLECTIONADMIN)));

        # record total number of resources
        $ResourceFactory = new ResourceFactory();
        $this->RecordSample(self::ST_RESOURCECOUNT, $ResourceFactory->GetItemCount());

        # record number of resources that have been rated
        $this->RecordSample(self::ST_RATEDRESOURCECOUNT,
                $ResourceFactory->GetRatedResourceCount());

        # record number of users who have rated resources
        $this->RecordSample(self::ST_RRESOURCEUSERCOUNT,
                $ResourceFactory->GetRatedResourceUserCount());

        # record number of searches currently saved
        $SavedSearchFactory = new SavedSearchFactory();
        $this->RecordSample(self::ST_SAVEDSEARCHCOUNT,
                $SavedSearchFactory->GetSearchCount());

        # record number of users who currently have searches saved
        $this->RecordSample(self::ST_SSEARCHUSERCOUNT,
                $SavedSearchFactory->GetSearchUserCount());

        # record number of users that have logged in within the last day
        $this->RecordSample(self::ST_DAILYLOGINCOUNT,
                $UserFactory->GetUserCount(
                        "LastLoginDate > '".addslashes($LastRunAt)."'"));

        # record number of new accounts created and verified within the last day
        $this->RecordSample(self::ST_DAILYNEWACCTS,
                $UserFactory->GetUserCount(
                        "CreationDate > '".addslashes($LastRunAt)
                        ."' AND RegistrationConfirmed > 0"));
    }

    function RecordUserLogin($UserId, $Password)
    {
        $this->RecordEvent(self::ET_USERLOGIN, NULL, NULL, $UserId);
    }

    function RecordNewUserAdded($UserId, $Password)
    {
        $this->RecordEvent(self::ET_NEWUSERADDED, NULL, NULL, $UserId);
    }

    function RecordNewUserVerified($UserId)
    {
        $this->RecordEvent(self::ET_NEWUSERVERIFIED, NULL, NULL, $UserId);
    }

    function RecordSearch($AdvancedSearch, $SearchParameters, $SearchResults)
    {
        $this->RecordEvent(
                ($AdvancedSearch ? self::ET_ADVANCEDSEARCH : self::ET_SEARCH),
                $SearchParameters, count($SearchResults));
    }

    function RecordOAIRequest($RequesterIP, $QueryString)
    {
        $this->RecordEvent(
                self::ET_OAIREQUEST, $RequesterIP, $QueryString, NULL, 0);
    }

    function RecordFullRecordView($ResourceId)
    {
        # if referring URL is available
        $SearchGroups = NULL;
        if (isset($_SERVER["HTTP_REFERER"]))
        {
            # if GET parameters are part of referring URL
            $Pieces = parse_url($_SERVER["HTTP_REFERER"]);
            if (isset($Pieces["query"]))
            {
                # if search parameters look to be available from GET parameters
                parse_str($Pieces["query"], $Args);
                if (isset($Args["P"]) && ($Args["P"] == "AdvancedSearch"))
                {
                    # extract search parameters for recording with view
                    $SearchGroups =
                            SavedSearch::TranslateSearchGroupsToUrlParameters(
                            SavedSearch::TranslateUrlParametersToSearchGroups(
                            $Pieces["query"]));
                }
            }
        }

        # record full record view
        $this->RecordEvent(
                self::ET_FULLRECORDVIEW, $ResourceId, $SearchGroups);
    }

    /**
    * Retrieve list of full record view events.
    * @param User User (object) to retrieve full record views for.  Specify NULL for
    *       all users.  (OPTIONAL - defaults to NULL)
    * @param Count Number of views to return.  (OPTIONAL - defaults to 10)
    * @param Offset Offset into list of views to begin.  (OPTIONAL - defaults to 0)
    * @param NoDupes Whether to eliminate duplicate records with record IDs,
    *       returning only the newest.  (OPTIONAL - defaults to TRUE)
    * @return Array with event ID as index and associative array of view attributes
    *       as value ("Date", "ResourceId", "IPAddress", "QueryString", "UserId")
    */
    function GetFullRecordViews($User = NULL, $Count = 10, $Offset = 0, $NoDupes = TRUE)
    {
        # do while we need more views and there are records left in DB
        $Views = array();
        do
        {
            # retrieve block of view records from database
            $this->DB->Query("SELECT * FROM MetricsRecorder_EventData"
                    ." WHERE EventType = ".self::ET_FULLRECORDVIEW
                    .($User ? " AND UserId = ".intval($User->Id()) : " ")
                    ." ORDER BY EventDate DESC"
                    ." LIMIT ".intval($Offset).",".$Count);
            $Offset += $Count;

            # while we need more views and records are left in the current block
            while ((count($Views) < $Count) && ($Row = $this->DB->FetchRow()))
            {
                # if caller did not care about dupes or record is not a dupe
                if (!$NoDupes || (!isset($SeenIDs[$Row["DataOne"]])))
                {
                    # add record to views
                    if ($NoDupes) {  $SeenIDs[$Row["DataOne"]] = TRUE;  }
                    $Views[$Row["EventId"]] = array(
                            "Date" => $Row["EventDate"],
                            "ResourceId" => $Row["DataOne"],
                            "IPAddress" => $Row["IPAddress"],
                            "QueryString" => $Row["DataTwo"],
                            "UserId" => $Row["UserId"],
                            );
                }
            }
        }
        while ((count($Views) < $Count) && $this->DB->NumRowsSelected());

        # return views to caller
        return $Views;
    }

    /**
    * Get count of full record views for specified resource.
    * @param ResourceId ID of resource.
    * @param ExcludePrivs Array of privileges that will exclude users from count.
    *       (OPTIONAL - defaults to NULL)
    * @return Record view count.
    */
    function GetFullRecordViewCount($ResourceId, $ExcludePrivs = NULL)
    {
        $SubQuery = User::GetSqlQueryForUsersWithPriv($ExcludePrivs);
        $ViewCount = $this->DB->Query("SELECT COUNT(*) AS ViewCount"
                ." FROM MetricsRecorder_EventData"
                ." WHERE EventType = ".self::ET_FULLRECORDVIEW
                ." AND DataOne = '".addslashes($ResourceId)."'"
                ." AND UserId NOT IN (".$SubQuery.")",
                "ViewCount");
        return $ViewCount;
    }

    /**
    * Get count of URL clicks for specified resource and field.
    * @param ResourceId ID of resource.
    * @param FieldId ID of metadata field.
    * @param ExcludePrivs Array of privileges that will exclude users from count.
    *       (OPTIONAL - defaults to NULL)
    * @return Record view count.
    */
    function GetUrlFieldClickCount($ResourceId, $FieldId, $ExcludePrivs = NULL)
    {
        $SubQuery = User::GetSqlQueryForUsersWithPriv($ExcludePrivs);
        $ClickCount = $this->DB->Query("SELECT COUNT(*) AS ClickCount"
                ." FROM MetricsRecorder_EventData"
                ." WHERE EventType = ".self::ET_URLFIELDCLICK
                ." AND DataOne = '".addslashes($ResourceId)."'"
                ." AND DataTwo = '".addslashes($FieldId)."'"
                ." AND UserId NOT IN (".$SubQuery.")",
                "ClickCount");
        return $ClickCount;
    }

    function RecordUrlFieldClick($ResourceId, $FieldId)
    {
        $this->RecordEvent(self::ET_URLFIELDCLICK, $ResourceId, $FieldId);
    }

    private function RecordSample($SampleType, $SampleValue)
    {
        $this->DB->Query("INSERT INTO MetricsRecorder_SampleData SET"
                ." SampleType = ".intval($SampleType).", "
                ." SampleValue = ".intval($SampleValue));
    }

    private function RecordEvent($EventType, $DataOne = NULL, $DataTwo = NULL,
            $UserId = NULL, $DedupeTime = 10)
    {
        # exit if event appears to be triggered by a bot
        $SignalResult = $GLOBALS["AF"]->SignalEvent("BotDetector_EVENT_CHECK_FOR_BOT");
        if ($SignalResult === TRUE) {  return;  }

        # use ID of currently-logged-in user if none supplied
        if ($UserId === NULL)
        {
            if ($GLOBALS["G_User"]->Id()) {  $UserId = $GLOBALS["G_User"]->Id();  }
        }

        # if deduplication time specified
        if ($DedupeTime > 0)
        {
            $Query = "SELECT COUNT(*) AS RowCount FROM MetricsRecorder_EventData"
                    ." WHERE EventType = ".intval($EventType)
                    ." AND EventDate >=  FROM_UNIXTIME('".(time() - $DedupeTime)."')";
            if ($DataOne !== NULL)
            {
                $Query .= " AND DataOne = '".addslashes($DataOne)."'";
            }
            if ($DataTwo !== NULL)
            {
                $Query .= " AND DataTwo = '".addslashes($DataTwo)."'";
            }
            if ($UserId !== NULL)
            {
                $Query .= " AND UserId = ".intval($GLOBALS["G_User"]->Id());
            }
            if (isset($_SERVER["REMOTE_ADDR"]) && ($_SERVER["REMOTE_ADDR"] != "::1"))
            {
                $Query .= " AND IPAddress = INET_ATON('"
                        .addslashes($_SERVER["REMOTE_ADDR"])."')";
            }
            $RowCount = $this->DB->Query($Query, "RowCount");
            if ($RowCount > 0) {  return;  }
        }

        # build query and save event info to database
        $Query = "INSERT INTO MetricsRecorder_EventData SET"
                ." EventType = ".intval($EventType);
        if ($DataOne !== NULL)
        {
            $Query .= ", DataOne = '".addslashes($DataOne)."'";
        }
        if ($DataTwo !== NULL)
        {
            $Query .= ", DataTwo = '".addslashes($DataTwo)."'";
        }
        if ($UserId !== NULL)
        {
            $Query .= ", UserId = ".intval($GLOBALS["G_User"]->Id());
        }
        if (isset($_SERVER["REMOTE_ADDR"]) && ($_SERVER["REMOTE_ADDR"] != "::1"))
        {
            $Query .= ", IPAddress = INET_ATON('"
                    .addslashes($_SERVER["REMOTE_ADDR"])."')";
        }
        $this->DB->Query($Query);

        # retrieve ID of recorded event (for possible use in annotations)
        $EventId = $this->DB->LastInsertId("MetricsRecorder_EventData");

        # signal to give other code a chance add event annotations
        $SignalResults = $GLOBALS["AF"]->SignalEvent("MetricsRecorder_EVENT_RECORD_EVENT",
                array(
                        "EventType" => $EventType,
                        "DataOne" => $DataOne,
                        "DataTwo" => $DataTwo,
                        "UserId" => $UserId,
                ));

        # if annotation data was returned by signal
        if (count($SignalResults))
        {
            # for each annotation
            foreach ($SignalResults as $Annotator => $Annotation)
            {
                # if annotation supplied
                if ($Annotation !== NULL)
                {
                    # look up ID for annotator
                    $AnnotatorId = $this->DB->Query("SELECT AnnotatorId"
                            ." FROM MetricsRecorder_Annotators"
                            ." WHERE AnnotatorName = '".addslashes($Annotator)."'",
                            "AnnotatorId");

                    # if no annotator ID found
                    if (!$this->DB->NumRowsSelected())
                    {
                        # add ID for annotator
                        $this->DB->Query("INSERT INTO MetricsRecorder_Annotators"
                                ." SET AnnotatorName = '".addslashes($Annotator)."'");
                        $AnnotatorId = $this->DB->LastInsertId(
                                "MetricsRecorder_Annotators");
                    }

                    # save annotation to database
                    $this->DB->Query("INSERT INTO MetricsRecorder_EventAnnotations"
                            ." (EventId, AnnotatorId, Annotation) VALUES ("
                            .$EventId.", ".$AnnotatorId.", "
                            ."'".addslashes($Annotation)."')");
                }
            }
        }
    }

    /**
    * Retrieve count of clicks on specified URL field for each resource.
    * @param FieldId ID of URL metadata field.
    * @param StartDate Beginning date of range to search, in SQL date
    *       format.  (OPTIONAL, defaults to NULL)
    * @param EndDate Ending date of range to search, in SQL date
    *       format.  (OPTIONAL, defaults to NULL)
    * @param Offset Zero-based offset into results array.  (OPTIONAL, defaults to 0)
    * @param Count Number of results to return (0 to retrieve all).
    *       (OPTIONAL, defaults to 0)
    * @param ResourceSubQuery SQL subquery to limit set of resource IDs.  (OPTIONAL)
    * @return Associative array of count data, with "Counts" element containing
    *       array of URL click counts, indexed by resource IDs and sorted in
    *       descending order of click counts, "StartDate" and "EndDate" elements
    *       containing dates of first and last recorded clicks.
    */
    function GetUrlFieldClickCounts($FieldId, $StartDate = NULL, $EndDate = NULL,
            $Offset = 0, $Count = 0, $ResourceSubQuery = NULL, $UserExclSubQuery = NULL)
    {
        return $this->GetClickCounts(self::ET_URLFIELDCLICK, $StartDate, $EndDate,
                $Offset, $Count, $FieldId, $ResourceSubQuery, $UserExclSubQuery);
    }

    /**
    * Retrieve count of full record views for each resource.
    * @param StartDate Beginning date of range to search, in SQL date
    *       format.  (OPTIONAL, defaults to NULL)
    * @param EndDate Ending date of range to search, in SQL date
    *       format.  (OPTIONAL, defaults to NULL)
    * @param Offset Zero-based offset into results array.  (OPTIONAL, defaults to 0)
    * @param Count Number of results to return (0 to retrieve all).
    *       (OPTIONAL, defaults to 0)
    * @param ResourceSubQuery SQL subquery to limit set of resource IDs.
    *       (OPTIONAL, defaults to NULL))
    * @param UserExclSubQuery SQL subquery for user IDs to exclude from count. (OPTIONAL)
    * @return Associative array of count data, with "Counts" element containing
    *       array of full record view counts, indexed by resource IDs and sorted in
    *       descending order of view counts, "StartDate" and "EndDate" elements
    *       containing dates of first and last recorded views.
    */
    function GetFullRecordViewCounts($StartDate = NULL, $EndDate = NULL, $Offset = 0,
            $Count = 0, $ResourceSubQuery = NULL, $UserExclSubQuery = NULL)
    {
        return $this->GetClickCounts(self::ET_FULLRECORDVIEW, $StartDate, $EndDate,
                $Offset, $Count, NULL, $ResourceSubQuery, $UserExclSubQuery);
    }

    private function GetClickCounts($ClickType, $StartDate, $EndDate,
            $Offset, $Count, $FieldId, $ResourceSubQuery, $UserExclSubQuery)
    {
        # build query from supplied parameters
        $DBQuery = "SELECT DataOne, COUNT(DataOne) AS Clicks"
                ." FROM MetricsRecorder_EventData";
        $QueryConditional = " WHERE EventType = ".$ClickType;
        if ($StartDate)
        {
            $QueryConditional .= " AND EventDate >= '".addslashes($StartDate)."'";
        }
        if ($EndDate)
        {
            $QueryConditional .= " AND EventDate <= '".addslashes($EndDate)."'";
        }
        if ($FieldId)
        {
            $QueryConditional .= " AND DataTwo = '".addslashes($FieldId)."'";
        }
        if ($ResourceSubQuery)
        {
            $QueryConditional .= " AND DataOne IN (".$ResourceSubQuery.")";
        }
        if ($UserExclSubQuery)
        {
            $QueryConditional .= " AND UserId NOT IN (".$UserExclSubQuery.")";
        }
        $DBQuery .= $QueryConditional." GROUP BY DataOne ORDER BY Clicks DESC";
        if ($Offset || $Count)
        {
            $DBQuery .= " LIMIT ".intval($Offset);
            if ($Count) {  $DBQuery .= ", ".intval($Count);  }
        }

        # retrieve click counts from database
        $this->DB->Query($DBQuery);

        # for each count returned
        $Counts = array();
        while ($Row = $this->DB->FetchRow())
        {
            # add count to data to be returned
            $Counts[$Row["DataOne"]] = $Row["Clicks"];
        }
        $Data["Counts"] = $Counts;

        # retrieve earliest click date
        $DBQuery = "SELECT EventDate FROM MetricsRecorder_EventData"
                .$QueryConditional." ORDER BY EventDate ASC LIMIT 1";
        $Data["StartDate"] = $this->DB->Query($DBQuery, "EventDate");

        # retrieve latest click date
        $DBQuery = "SELECT EventDate FROM MetricsRecorder_EventData"
                .$QueryConditional." ORDER BY EventDate DESC LIMIT 1";
        $Data["EndDate"] = $this->DB->Query($DBQuery, "EventDate");

        # return click data to caller
        return $Data;
    }

    /** @name Sample data types */ /*@{*/
    const ST_REGUSERCOUNT = 1;       /**< Number of registered users */
    const ST_PRIVUSERCOUNT = 2;      /**< Number of privileged users */
    const ST_RESOURCECOUNT = 3;      /**< Number of resources  */
    const ST_RATEDRESOURCECOUNT = 4; /**< Number of rated resources  */
    const ST_RRESOURCEUSERCOUNT = 5; /**< Number of users who rated resources  */
    const ST_SAVEDSEARCHCOUNT = 6;   /**< Number of saved searches */
    const ST_SSEARCHUSERCOUNT = 7;   /**< Number of users with saved searches */
    const ST_DAILYLOGINCOUNT = 8;    /**< Number of logins in last day */
    const ST_DAILYNEWACCTS = 9;      /**< Number new accounts in last day */
    /*@}*/

    /** @name Event data types */ /*@{*/
    const ET_NONE = 0;                /**< no event (do not record) */
    const ET_USERLOGIN =  1;          /**< User logged in */
    const ET_NEWUSERADDED =  2;       /**< User signed up for new account */
    const ET_NEWUSERVERIFIED =  3;    /**< User verified new account */
    const ET_SEARCH =  4;             /**< Keyword search performed */
    const ET_ADVANCEDSEARCH =  5;     /**< Fielded search performed */
    const ET_OAIREQUEST =  6;         /**< OAI-PMH harvest request */
    const ET_FULLRECORDVIEW =  7;     /**< Full record page viewed */
    const ET_URLFIELDCLICK =  8;      /**< URL field clicked */
    /* (recording not yet implemented for following events) */
    const ET_RSSREQUEST = 9;          /**< RSS feed request */
    /*@}*/

    private $DB;
}
