<?php /** * SeekQuarry/Yioop -- * Open Source Pure PHP Search Engine, Crawler, and Indexer * * Copyright (C) 2009 - 2024 Chris Pollett chris@pollett.org * * LICENSE: * * This program is free software: you can redistribute it and/or modify * it under the terms of the GNU General Public License as published by * the Free Software Foundation, either version 3 of the License, or * (at your option) any later version. * * This program is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU General Public License for more details. * * You should have received a copy of the GNU General Public License * along with this program. If not, see <https://www.gnu.org/licenses/>. * * END LICENSE * * @author Chris Pollett * @license https://www.gnu.org/licenses/ GPL3 * @link https://www.seekquarry.com/ * @copyright 2009 - 2024 * @filesource */ namespace seekquarry\yioop\models; use seekquarry\yioop\configs as C; use seekquarry\yioop\library as L; /** * Model used to keep track for analytic and user experience activities that * users carry out on a Yioop web site. For analytics things that might * tracked are wiki page views, queries, query outcomes. For UX things that * the impression model allows is to keep track of recent group a user has * visited to provide better bread crumb drop downs, make the manage account * landing page list more relevant groups, determine start of whether a * media item has been watched, completely watched, etc. * * In terms of how things are implemented in the database. The tables * ITEM_IMPRESSION and ITEM_IMPRESSION_SUMMARY contain the raw statistics * of activities. If differential privacy is in use then * ITEM_IMPRESSION_STAT keeps track of fuzzified statistics. * * @author Chris Pollett */ class ImpressionModel extends Model { /** * Used to create a new counter related to a particular user for a * particular activity in the impression analytics. This entails adding both * a log-like record of when the activity happened and creating a * new global count for this acitvity. * * @param int $user_id id of user we are adding analytic information for * @param int $item_id id of particular item we are adding analytic * information of * @param int $type_id type of particular item we are adding analytic * information of (group, wiki, thread, etc) */ public function init($user_id, $item_id, $type_id) { $this->initWithDb($user_id, $item_id, $type_id, $this->db); } /** * Used to add a count record related to a particular user for a particular * activity to the impression analytics. This entails adding both * a log-like record of when the activity happened and incrementing a * global count of this activity. * * @param int $user_id id of user we are adding analytic information for * @param int $item_id id of particular item we are adding analytic * information of * @param int $type_id type of particular item we are adding analytic * information of (group, wiki, thread, etc) */ public function add($user_id, $item_id, $type_id) { $this->addWithDb($user_id, $item_id, $type_id, $this->db); } /** * Used to add a count record related to a web search to the impression * analytics. This entails adding a record to QUERY_ITEM if it doesn't * exist together with an add() call. * * @param string $query search query we are adding an impression for */ public function addQueryImpression($query) { if (!C\SEARCH_ANALYTICS_MODE || C\SEARCH_ANALYTICS_MODE == "0") { return; } $db = $this->db; $dbinfo = ["DBMS" => C\DBMS, "DB_HOST" => C\DB_HOST, "DB_USER" => C\DB_USER, "DB_PASSWORD" => C\DB_PASSWORD, "DB_NAME" => C\DB_NAME]; $query_hash = L\crawlHash($query); $sql = "SELECT ID FROM QUERY_ITEM WHERE QUERY_HASH = ?"; $result = $db->execute($sql, [$query_hash]); $row = $db->fetchArray($result); $impression_type = (strncmp($query, "cache:", 6) === 0) ? C\CACHE_IMPRESSION : C\QUERY_IMPRESSION; if (empty($row['ID'])) { $sql = "INSERT INTO QUERY_ITEM (QUERY_HASH, QUERY, CREATION) VALUES (?, ?, ?)"; $sql = $db->insertIgnore($sql, $dbinfo); $result = $db->execute($sql, [$query_hash, $query, time()]); $this->initWithDb(C\PUBLIC_USER_ID, $db->insertID("QUERY_ITEM"), $impression_type, $db); } else { $this->addWithDb(C\PUBLIC_USER_ID, $row['ID'], $impression_type, $db); } } /** * Used to add an impression record related to a user clicking * a link on the query result page for web search. This entails adding a * record to QUERY_ITEM the particular link click from the query doesn't * exist together with an add() impression call call. * * @param string $query search query the user was clicking a link on * @param string $link url that the user clicked on from the results */ public function addQueryExitImpression($query, $link) { if (!C\SEARCH_ANALYTICS_MODE || C\SEARCH_ANALYTICS_MODE == "0") { return; } $db = $this->db; $dbinfo = ["DBMS" => C\DBMS, "DB_HOST" => C\DB_HOST, "DB_USER" => C\DB_USER, "DB_PASSWORD" => C\DB_PASSWORD, "DB_NAME" => C\DB_NAME]; $query_link = trim($query) . "=>" . trim($link); $query_hash = L\crawlHash($query); $ql_hash = L\crawlHash($query_link); $sql = "SELECT ID FROM QUERY_ITEM WHERE QUERY_HASH = ?"; $result = $db->execute($sql, [$ql_hash]); $row = $db->fetchArray($result); if (empty($row['ID'])) { $sql = "INSERT INTO QUERY_ITEM (QUERY_HASH, QUERY, CREATION, AUX_HASH) VALUES (?, ?, ?, ?)"; $sql = $db->insertIgnore($sql, $dbinfo); $result = $db->execute($sql, [$ql_hash, $query_link, time(), $query_hash]); $this->initWithDb(C\PUBLIC_USER_ID, $db->insertID("QUERY_ITEM"), C\QUERY_IMPRESSION_EXIT, $db); } else { $this->addWithDb(C\PUBLIC_USER_ID, $row['ID'], C\QUERY_IMPRESSION_EXIT, $db); } } /** * Used to delete information related to a particular user from the * impression analytics. * * @param int $user_id id of user we are deleting analytic information for * @param int $item_id id of particular item we are deleting analytic * information of * @param int $type_id type of particular item we are deleting analytic * information of (group, wiki, thread, etc) */ public function delete($user_id, $item_id, $type_id) { $this->deleteWithDb($user_id, $item_id, $type_id, $this->db); } /** * Returns num many most recent impression items of the given type for * a user * * @param int $user_id id of user we are looking for information about * @param int $type_id type of particular item we want information on * (group, wiki, thread, etc) * @param int $num how many most recent entries we want to get * @return array of $num many most recent item id's of $type_id of * for the given $user_id */ public function recent($user_id, $type_id, $num) { $db = $this->db; // mysql doesn't like LIMIT to be parameterized $num = intval($num); $sql = "SELECT ITEM_ID, MAX(VIEW_DATE) AS MOST_RECENT FROM ITEM_IMPRESSION WHERE USER_ID = ? AND ITEM_TYPE = ? GROUP BY ITEM_ID ORDER BY MOST_RECENT DESC LIMIT $num"; $result = $db->execute($sql, [$user_id, $type_id]); $rows = []; while ($row = $db->fetchArray($result)) { $rows[] = $row['ITEM_ID']; } $rows = array_values(array_filter($rows)); return $rows; } /** * Returns the most recent timestamp of any view impression of a group a * user has. * @param int $user_id want most recent impression for * @return int timstamp of most recent impression */ public function mostRecentThreadView($user_id, $thread_id) { $db = $this->db; $sql = "SELECT COALESCE(MAX(UPDATE_TIMESTAMP), 0) AS MOST_RECENT FROM ". "ITEM_IMPRESSION_SUMMARY WHERE USER_ID = ? AND ITEM_TYPE = " . C\THREAD_IMPRESSION . " AND ITEM_ID = ? " . "AND UPDATE_PERIOD = ". C\MOST_RECENT_VIEW; $result = $db->execute($sql, [$user_id, $thread_id]); if (!$result || !($row = $db->fetchArray($result))) { return 0; } return $row["MOST_RECENT"] ?? 0; } /** * Returns the most recent timestamp of any view impression of a group a * user has. * @param int $user_id want most recent impression for * @return int timstamp of most recent impression */ public function mostRecentGroupView($user_id) { $db = $this->db; $sql = "SELECT COALESCE(MAX(UPDATE_TIMESTAMP), 0) AS MOST_RECENT FROM ". "ITEM_IMPRESSION_SUMMARY WHERE USER_ID = $user_id AND ITEM_TYPE = " . C\GROUP_IMPRESSION . " AND ITEM_ID IN (SELECT GROUP_ID " . "FROM USER_GROUP WHERE USER_ID = $user_id AND STATUS = ". C\ACTIVE_STATUS . ") AND UPDATE_PERIOD = ".C\MOST_RECENT_VIEW; $result = $db->execute($sql); if (!$result || !($row = $db->fetchArray($result))) { return 0; } return $row["MOST_RECENT"]; } /** * For a list of group_ids that $user_id may belong to, returns an * array of pairs group_id => timestamp of most recent view * @param int $user_id user to look up most recent views for * @param array $group_ids groups to check in * @return array pairs group_id => timestamp */ public function mostRecentGroupViews($user_id, $group_ids) { $db = $this->db; $in_clause = " ("; $comma = ""; foreach ($group_ids as $group_id) { $in_clause .= $comma . intval($group_id); $comma = ","; } $in_clause .= ") "; $sql = "SELECT ITEM_ID AS GROUP_ID, UPDATE_TIMESTAMP AS MOST_RECENT " . "FROM ITEM_IMPRESSION_SUMMARY " . "WHERE USER_ID = ? AND ITEM_TYPE = " . C\GROUP_IMPRESSION . " AND ITEM_ID IN $in_clause AND UPDATE_PERIOD = ". C\MOST_RECENT_VIEW . " AND NUM_VIEWS = 0"; $result = $db->execute($sql, [$user_id]); if (!$result) { return []; } $group_views = []; while ($row = $db->fetchArray($result)) { if (!empty($row["GROUP_ID"]) && $row["MOST_RECENT"]) { $group_views[$row["GROUP_ID"]] = $row["MOST_RECENT"]; } } return $group_views; } /** * Used by Analytics job to aggregate impression raw data to make * hourly, daily, monthly, and yearly impression statistics. */ public function computeStatistics() { $db = $this->db; $dbinfo = ["DBMS" => C\DBMS, "DB_HOST" => C\DB_HOST, "DB_USER" => C\DB_USER, "DB_PASSWORD" => C\DB_PASSWORD, "DB_NAME" => C\DB_NAME]; $timestamps = [C\ONE_HOUR => floor(time()/C\ONE_HOUR) * C\ONE_HOUR, C\ONE_DAY => floor(time()/C\ONE_DAY) * C\ONE_DAY, C\ONE_MONTH => floor(time()/C\ONE_MONTH) * C\ONE_MONTH, C\ONE_YEAR => floor(time()/C\ONE_YEAR) * C\ONE_YEAR]; $one_week_stamp = C\ONE_WEEK * floor(time()/C\ONE_WEEK); $table = "ITEM_IMPRESSION"; $condition = " VIEW_DATE >= ? AND ITEM_ID IS NOT NULL "; $sum = " COUNT(*) "; foreach ($timestamps as $period => $timestamp) { $sql = "DELETE FROM ITEM_IMPRESSION_SUMMARY WHERE UPDATE_PERIOD = ? AND UPDATE_TIMESTAMP = ?"; $db->execute($sql, [$period, $timestamp]); $sql = "INSERT INTO ITEM_IMPRESSION_SUMMARY (USER_ID, ITEM_ID, ITEM_TYPE, UPDATE_PERIOD, UPDATE_TIMESTAMP, NUM_VIEWS) SELECT USER_ID, ITEM_ID, ITEM_TYPE, ? AS UPDATE_PERIOD, ? AS UPDATE_TIMESTAMP, $sum AS NUM_VIEWS FROM $table WHERE $condition GROUP BY USER_ID, ITEM_ID, ITEM_TYPE"; L\crawlLog( "Computing statistics for $period " . "second update period"); $sql = $db->insertIgnore($sql, $dbinfo); $db->execute($sql, [$period, $timestamp, $timestamp]); $table = "ITEM_IMPRESSION_SUMMARY"; $condition = "UPDATE_PERIOD = $period AND UPDATE_TIMESTAMP >= ?"; $sum = "SUM(NUM_VIEWS)"; } /* delete user summary data older than one year, delete impression info more than a month old */ $sql = "DELETE FROM ITEM_IMPRESSION WHERE VIEW_DATE < ?"; $db->execute($sql, [$one_week_stamp]); $sql = "DELETE FROM ITEM_IMPRESSION_SUMMARY WHERE UPDATE_TIMESTAMP < ?". " AND UPDATE_PERIOD > ". C\FOREVER; $db->execute($sql, [$timestamp]); } /** * Used to return an array of impression statistics for a particular update * period for a particular type of impression. * * @param int $type type of impression to return statistic * @param int $period an update period to get statistics for in second, for * example, 3600 would give statistics for an hour. Only * hour, day, month, and year second quantities supported * @param int $filter a string to filter the items names of the * statistics returns (for example, filter could be used to * filter statistics about popular thread names with respect to * the number of views statistics ) * @param int $group_id group identifier of group want stats for * @param int $user_id user identifier of user want stats for * @param int $limit first row we want from the result set * @param int $num number of rows we want starting from the first row * in the result set * @return array */ public function getStatistics($type, $period, $filter = "", $group_id = C\PUBLIC_GROUP_ID, $user_id = C\PUBLIC_USER_ID, $limit = 0, $num = 100) { $db = $this->db; $select = ""; $from = ""; $where = ""; list ($timestamp, $actual_period) = $this->getLastTimestamp($period); $parameters = [$type, $actual_period, $group_id, $user_id]; $filter_where = ""; switch ($type) { case C\GROUP_IMPRESSION: $select = ""; $from = ""; $where = " AND IIS.ITEM_ID = G.GROUP_ID"; break; case C\QUERY_IMPRESSION: $select = ", MIN(QI.QUERY) AS ITEM_NAME"; $from = ", QUERY_ITEM QI"; $where = " AND IIS.ITEM_ID = QI.ID "; if (!empty($filter)) { $filter_where = " AND LOWER(QI.QUERY) ". "LIKE LOWER('%$filter%') "; } break; case C\THREAD_IMPRESSION: $select = ", MIN(GI.TITLE) AS ITEM_NAME"; $from = ", GROUP_ITEM GI"; $where = " AND IIS.ITEM_ID = GI.ID ". "AND G.GROUP_ID = GI.GROUP_ID"; if (!empty($filter)) { $filter_where = " AND LOWER(GI.TITLE) ". "LIKE LOWER('%$filter%') "; } break; case C\WIKI_IMPRESSION: $select = ", MIN(GP.TITLE) AS ITEM_NAME"; $from = ", GROUP_PAGE GP"; $where = " AND IIS.ITEM_ID = GP.ID ". "AND G.GROUP_ID = GP.GROUP_ID"; if (!empty($filter)) { $filter_where = " AND LOWER(GP.TITLE) ". "LIKE LOWER('%$filter%') "; } break; } $timestamp_where = ""; if ($period != C\FOREVER) { $timestamp_where = " AND IIS.UPDATE_TIMESTAMP >= ? "; $parameters[] = $timestamp; } $sql = "SELECT MIN(G.GROUP_NAME) AS GROUP_NAME, IIS.ITEM_ID AS ID, MIN(IIS.UPDATE_PERIOD) AS PERIOD, MIN(IIS.UPDATE_TIMESTAMP) AS TIMESTAMP, SUM(IIS.NUM_VIEWS) AS NUM_VIEWS $select FROM ITEM_IMPRESSION_SUMMARY IIS, SOCIAL_GROUPS G $from WHERE IIS.ITEM_TYPE = ? AND IIS.UPDATE_PERIOD = ? AND G.GROUP_ID = ? AND IIS.USER_ID = ? $where $filter_where $timestamp_where GROUP BY IIS.ITEM_ID ORDER BY PERIOD ASC, NUM_VIEWS DESC " . $db->limitOffset($limit, $num); $result = $db->execute($sql, $parameters); $statistics = []; while ($row = $db->fetchArray($result)) { if ($type == C\GROUP_IMPRESSION) { $statistics[] = $row; } else { $item_name = $row['ITEM_NAME']; unset($row['ITEM_NAME']); $statistics[$item_name][] = $row; } } return $statistics; } /** * Calculates total number of views of given item id for * given time period * * @param int $type the impression type to get data for * @param int $period time period for which to show stats * @param int $item_id item identifier of item for which to show stats * @return array */ public function getPeriodHistogramData($type, $period, $item_id) { $db = $this->db; list ($timestamp, $actual_period) = $this->getLastTimestamp($period); $parameters = [$type, $actual_period, $item_id, $timestamp]; $sql = "SELECT IIS.ITEM_ID AS ID, IIS.NUM_VIEWS AS VIEWS, IIS.ITEM_TYPE AS TYPE, IIS.UPDATE_TIMESTAMP, IIS.UPDATE_PERIOD AS PERIOD FROM ITEM_IMPRESSION_SUMMARY IIS WHERE IIS.ITEM_TYPE = ? AND IIS.UPDATE_PERIOD = ? AND IIS.ITEM_ID = ? AND IIS.USER_ID = 2 AND IIS.UPDATE_TIMESTAMP >= ? ORDER BY UPDATE_TIMESTAMP ASC"; $result = $db->execute($sql, $parameters); $statistics = []; while ($row = $db->fetchArray($result)) { $statistics[] = $row; } return $statistics; } /** * Subtracts the timestamp to get actual time period window. * To get statistics for one time period, lower time period's data needs * to be extracted. For example, to get last day's stat, result in every * one hour needs to be extracted in the last 24 hours * * @param int $period time period * @return array */ public function getLastTimestamp($period) { $time_periods = [C\ONE_HOUR => 1, C\ONE_DAY => 24, C\ONE_MONTH => 30, C\ONE_YEAR => 12, C\FOREVER => 0]; $previous_period = [C\ONE_HOUR => C\ONE_HOUR, C\ONE_DAY => C\ONE_HOUR, C\ONE_MONTH => C\ONE_DAY, C\ONE_YEAR => C\ONE_MONTH, C\FOREVER => C\FOREVER]; $interval = (!empty($time_periods[$period])) ? $time_periods[$period] : C\FOREVER; $period = (!empty($previous_period[$period])) ? $previous_period[$period] : 0; $current_timestamp = floor(time()/$period) * $period; $last_timestamp = ($period == C\FOREVER) ? 0: ($current_timestamp - ($period * ($interval - 1))); return [$last_timestamp, $period]; } /** * Used to update the fuzzified view counts of a thread item. * * @param int $item_id id of the thread item to update fuzzified counts for * @param int $num_views current number of views for item. This value * is stored in the TMP_NUM_VIEWS column to remember the last time the * FUZZY_NUM_VIEWS column was updated. Only when the TMP_NUM_VIEWS column * differs from the NUM_VIEWS COLUMN will this method need to be called. * @param int $fuzzy_num_views number of views after epsilon privacy * fuzzification applied. */ public function updatePrivacyViews($item_id, $num_views, $fuzzy_num_views) { $sql = "UPDATE ITEM_IMPRESSION_SUMMARY SET FUZZY_NUM_VIEWS=?, TMP_NUM_VIEWS=? WHERE ITEM_ID=? AND ITEM_TYPE=" . C\THREAD_IMPRESSION; $this->db->execute($sql, [$fuzzy_num_views, $num_views, $item_id]); } /** * Used to update the fuzzy statistics of impression items. * * @param int $item_id id of the item to update the statistics * @param int $item_type type of the item * @param int $period time period of the item * @param int $num_views number of views of the item for * specified time period * @param int $fuzzy_num_views fuzzified views of the item * for specified time period */ public function updateImpressionStat($item_id, $item_type, $period, $num_views, $fuzzy_num_views) { $sql = "UPDATE ITEM_IMPRESSION_STAT SET FUZZY_NUM_VIEWS=?, NUM_VIEWS=? WHERE ITEM_ID=? AND ITEM_TYPE=? AND UPDATE_PERIOD=?"; $parameters = [$fuzzy_num_views, $num_views, $item_id, $item_type, $period]; $result = $this->db->execute($sql, $parameters); } /** * Returns the fuzzy statistics of the specified impression item. * If no statistics exists it creates default dummy statistics * It is assumed this function is always called at least once before * @see updateImpressionStat * * @param int $item_id id of the item to return the statistics * @param int $item_type type of the item * @param int $period time period of the item * @return array values of $sum and $fuzzy_num_views */ public function getImpressionStat($item_id, $item_type, $period) { $sql = "SELECT NUM_VIEWS, FUZZY_NUM_VIEWS FROM ITEM_IMPRESSION_STAT WHERE ITEM_ID=? AND ITEM_TYPE=? AND UPDATE_PERIOD=?"; $parameters = [$item_id, $item_type, $period]; $result = $this->db->execute($sql, $parameters); $row = $this->db->fetchArray($result); if (empty($row)) { $periods = [C\ONE_HOUR, C\ONE_DAY, C\ONE_MONTH, C\ONE_YEAR, C\FOREVER]; foreach ($periods as $period) { $sql = "INSERT INTO ITEM_IMPRESSION_STAT VALUES (?, ?, ?, -1, -1)"; $this->db->execute($sql, [$item_id, $type_id, $period]); } return [-1, -1]; } return [$row['NUM_VIEWS'], $row['FUZZY_NUM_VIEWS']]; } /** * Used to create a new counter related to a particular user for a * particular activity in the impression analytics. This entails adding both * a log-like record of when the activity happened and creating a * new global count for this activity. This static method version requires * having an initialized data source manager and may be appropriate to call * in the context of another model. * * @param int $user_id id of user we are adding analytic information for * @param int $item_id id of particular item we are adding analytic * information of * @param int $type_id type of particular item we are adding analytic * information of (group, wiki, thread, etc) * @param object $db a DatasourceManager used to query the Yioop * Yioop database */ public static function initWithDb($user_id, $item_id, $type_id, $db) { $dbinfo = ["DBMS" => C\DBMS, "DB_HOST" => C\DB_HOST, "DB_USER" => C\DB_USER, "DB_PASSWORD" => C\DB_PASSWORD, "DB_NAME" => C\DB_NAME]; $sql = "INSERT INTO ITEM_IMPRESSION_SUMMARY VALUES (?, ?, ?, " . C\FOREVER .", 0, 0, -1, -1)"; $sql = $db->insertIgnore($sql, $dbinfo); $db->execute($sql, [C\PUBLIC_USER_ID, $item_id, $type_id]); self::addWithDb($user_id, $item_id, $type_id, $db); } /** * Used to add a count record related to a particular user for a particular * activity to the impression analytics. This entails adding both * a log-like record of when the activity happened and incrementing a * global count of this activity. This static method version requires * having an initialized data source manager and may be appropriate to call * in the context of another model. * * @param int $user_id id of user we are adding analytic information for * @param int $item_id id of particular item we are adding analytic * information of * @param int $type_id type of particular item we are adding analytic * information of (group, wiki, thread, etc) * @param object $db a DatasourceManager used to query the Yioop * Yioop database */ public static function addWithDb($user_id, $item_id, $type_id, $db) { if (!C\GROUP_ANALYTICS_MODE || C\GROUP_ANALYTICS_MODE == "0" || !is_numeric($user_id) || !is_numeric($item_id)) { return; } $dbinfo = ["DBMS" => C\DBMS, "DB_HOST" => C\DB_HOST, "DB_USER" => C\DB_USER, "DB_PASSWORD" => C\DB_PASSWORD, "DB_NAME" => C\DB_NAME]; $sql = "INSERT INTO ITEM_IMPRESSION VALUES (?, ?, ?, ?)"; if ($user_id != C\PUBLIC_USER_ID) { $db->execute($sql, [$user_id, $item_id, $type_id, time()]); } $db->execute($sql, [C\PUBLIC_USER_ID, $item_id, $type_id, time()]); $sql = "DELETE FROM ITEM_IMPRESSION_SUMMARY WHERE USER_ID=? AND ITEM_ID=? AND ITEM_TYPE=? AND UPDATE_PERIOD = ". C\MOST_RECENT_VIEW; $db->execute($sql, [$user_id, $item_id, $type_id]); $sql = "INSERT INTO ITEM_IMPRESSION_SUMMARY VALUES (?, ?, ?, " . C\MOST_RECENT_VIEW .", ?, 0, -1, -1)"; $sql = $db->insertIgnore($sql, $dbinfo); $db->execute($sql, [$user_id, $item_id, $type_id, time()]); $sql = "UPDATE ITEM_IMPRESSION_SUMMARY SET NUM_VIEWS = NUM_VIEWS + 1 WHERE USER_ID=? AND ITEM_ID=? AND ITEM_TYPE=? AND UPDATE_PERIOD = ". C\FOREVER . " AND UPDATE_TIMESTAMP = 0"; $db->execute($sql, [C\PUBLIC_USER_ID, $item_id, $type_id]); } /** * Used to delete information related to a particular user from the * impression analytics. This static method version requires * having an initialized data source manager and may be appropriate to call * in the context of another model. * * @param int $user_id id of user we are deleting analytic information for * @param int $item_id id of particular item we are deleting analytic * information of * @param int $type_id type of particular item we are deleting analytic * information of (group, wiki, thread, etc) * @param object $db a DatasourceManager used to query the Yioop * Yioop database */ public static function deleteWithDb($user_id, $item_id, $type_id, $db) { $sql = "DELETE FROM ITEM_IMPRESSION WHERE USER_ID=? AND ITEM_ID=? AND ITEM_TYPE=?"; $db->execute($sql, [$user_id, $item_id, $type_id]); $db->execute($sql, [C\PUBLIC_USER_ID, $item_id, $type_id]); $sql = "DELETE FROM ITEM_IMPRESSION_SUMMARY WHERE USER_ID=? AND ITEM_ID=? AND ITEM_TYPE=?"; $db->execute($sql, [$user_id, $item_id, $type_id]); $db->execute($sql, [C\PUBLIC_USER_ID, $item_id, $type_id]); // Also delete records from ITEM_IMPRESSION_STAT $sql = "DELETE FROM ITEM_IMPRESSION_STAT WHERE ITEM_ID=? AND ITEM_TYPE=?"; $db->execute($sql, [$item_id, $type_id]); } /** * Deletes statistics related to web queries. */ public function deleteQueryStatistics() { $db = $this->db; $sql = "DELETE FROM ITEM_IMPRESSION WHERE ITEM_TYPE= " . C\QUERY_IMPRESSION . " OR ITEM_TYPE=" . C\CACHE_IMPRESSION; $db->execute($sql); $sql = "DELETE FROM ITEM_IMPRESSION_SUMMARY WHERE ITEM_TYPE= " . C\QUERY_IMPRESSION . " OR ITEM_TYPE=" . C\CACHE_IMPRESSION; $db->execute($sql); $sql = "DELETE FROM QUERY_ITEM"; $db->execute($sql); } }