Front to the WordPress application

Файловый менеджер - Редактировать - /home/freeclou/app.optimyar.com/front-web/build/assets/resources/agGrid/Models.zip

Назад
PK��z[1C��4��4��AuthorsModel.phpnu�ȯ��<?php namespace WP_Statistics\Models; use WP_Statistics\Abstracts\BaseModel; use WP_STATISTICS\Helper; use WP_Statistics\Service\Admin\Posts\WordCountService; use WP_Statistics\Utils\Query; class AuthorsModel extends BaseModel { /** * Counts the authors based on the given arguments. * By default, it will return total number of authors. * * @param array $args An array of arguments to filter the count. * @return int The total number of distinct authors. Returns 0 if no authors are found. / public function countAuthors($args = []) { $args = $this->parseArgs($args, [ 'date' => '', 'post_type' => Helper::get_list_post_type() ]); return Query::select('COUNT(DISTINCT post_author)') ->from('posts') ->where('post_status', '=', 'publish') ->where('post_type', 'IN', $args['post_type']) ->whereDate('post_date', $args['date']) ->getVar(); } public function getTopViewingAuthors($args = []) { $args = $this->parseArgs($args, [ 'date' => '', 'post_type' => Helper::get_list_post_type(), 'order_by' => 'total_views', 'order' => 'DESC', 'page' => 1, 'per_page' => 5, 'taxonomy' => '', 'term' => '' ]); $query = Query::select([ 'DISTINCT posts.post_author AS id', 'display_name AS name', 'SUM(pages.count) AS total_views' ]) ->from('posts') ->join('users', ['posts.post_author', 'users.ID']) ->join('pages', ['posts.ID', 'pages.id'], [], 'LEFT') ->where('post_status', '=', 'publish') ->where('post_type', 'IN', $args['post_type']) ->whereDate('date', $args['date']) ->groupBy('post_author') ->orderBy($args['order_by'], $args['order']) ->perPage($args['page'], $args['per_page']); if (!empty($args['taxonomy']) \|\| !empty($args['term'])) { $taxQuery = Query::select(['DISTINCT object_id']) ->from('term_relationships') ->join('term_taxonomy', ['term_relationships.term_taxonomy_id', 'term_taxonomy.term_taxonomy_id']) ->join('terms', ['term_taxonomy.term_id', 'terms.term_id']) ->where('term_taxonomy.taxonomy', 'IN', $args['taxonomy']) ->where('terms.term_id', '=', $args['term']) ->getQuery(); $query ->joinQuery($taxQuery, ['posts.ID', 'tax.object_id'], 'tax'); } $result = $query->getAll(); return $result ? $result : []; } public function getAuthorsByPostPublishes($args = []) { $args = $this->parseArgs($args, [ 'date' => '', 'post_type' => Helper::get_list_post_type(), 'page' => 1, 'per_page' => 5, 'taxonomy' => '', 'term' => '' ]); $query = Query::select(['DISTINCT post_author as id', 'display_name as name', 'COUNT() as post_count']) ->from('posts') ->join('users', ['posts.post_author', 'users.ID']) ->where('post_status', '=', 'publish') ->where('post_type', 'IN', $args['post_type']) ->whereDate('post_date', $args['date']) ->groupBy('posts.post_author') ->orderBy('post_count') ->perPage($args['page'], $args['per_page']); if (!empty($args['taxonomy']) \|\| !empty($args['term'])) { $query ->join('term_relationships', ['posts.ID', 'term_relationships.object_id']) ->join('term_taxonomy', ['term_relationships.term_taxonomy_id', 'term_taxonomy.term_taxonomy_id']) ->where('term_taxonomy.taxonomy', 'IN', $args['taxonomy']); if (!empty($args['term'])) { $query ->join('terms', ['term_taxonomy.term_id', 'terms.term_id']) ->where('terms.term_id', '=', $args['term']); } } $result = $query->getAll(); return $result ? $result : []; } public function getAuthorsByCommentsPerPost($args = []) { $args = $this->parseArgs($args, [ 'date' => '', 'post_type' => Helper::get_list_post_type(), 'page' => 1, 'per_page' => 5 ]); $result = Query::select([ 'DISTINCT posts.post_author AS id', 'display_name AS name', 'COUNT(comments.comment_ID) / COUNT(DISTINCT posts.ID) AS average_comments' ]) ->from('posts') ->join('users', ['posts.post_author', 'users.ID']) ->join('comments', ['posts.ID', 'comments.comment_post_ID']) ->where('comments.comment_type', '=', 'comment') ->where('comments.comment_approved', '=', '1') ->where('post_status', '=', 'publish') ->where('post_type', 'IN', $args['post_type']) ->whereDate('post_date', $args['date']) ->groupBy('post_author') ->orderBy('average_comments') ->perPage($args['page'], $args['per_page']) ->getAll(); return $result ? $result : []; } public function getAuthorsByViewsPerPost($args = []) { $args = $this->parseArgs($args, [ 'date' => '', 'post_type' => Helper::get_list_post_type(), 'order_by' => 'average_views', 'order' => 'DESC', 'page' => 1, 'per_page' => 5 ]); $result = Query::select([ 'DISTINCT posts.post_author AS id', 'display_name AS name', 'SUM(pages.count) AS total_views', 'COUNT(DISTINCT posts.ID) AS total_posts', 'SUM(pages.count) / COUNT(DISTINCT posts.ID) AS average_views' ]) ->from('posts') ->join('users', ['posts.post_author', 'users.ID']) ->join('pages', ['posts.ID', 'pages.id']) ->where('post_status', '=', 'publish') ->where('post_type', 'IN', $args['post_type']) ->whereDate('post_date', $args['date']) ->groupBy('post_author') ->orderBy($args['order_by'], $args['order']) ->perPage($args['page'], $args['per_page']) ->getAll(); return $result ? $result : []; } public function getAuthorsByWordsPerPost($args = []) { $args = $this->parseArgs($args, [ 'date' => '', 'post_type' => Helper::get_list_post_type(), 'page' => 1, 'per_page' => 5 ]); $result = Query::select([ 'DISTINCT posts.post_author AS id', 'display_name AS name', 'SUM(postmeta.meta_value) / COUNT(DISTINCT posts.ID) AS average_words' ]) ->from('posts') ->join('users', ['posts.post_author', 'users.ID']) ->join('postmeta', ['posts.ID', 'postmeta.post_id']) ->where('meta_key', '=', WordCountService::WORDS_COUNT_META_KEY) ->where('post_status', '=', 'publish') ->where('post_type', 'IN', $args['post_type']) ->whereDate('post_date', $args['date']) ->groupBy('post_author') ->orderBy('average_words') ->perPage($args['page'], $args['per_page']) ->getAll(); return $result ? $result : []; } public function getAuthorsReportData($args = []) { $args = $this->parseArgs($args, [ 'date' => '', 'post_type' => Helper::get_list_post_type(), 'order_by' => 'total_views', 'order' => 'DESC', 'page' => 1, 'per_page' => 5 ]); $commentsQuery = Query::select(['DISTINCT post_author', 'COUNT(comment_ID) AS total_comments']) ->from('posts') ->join('comments', ['posts.ID', 'comments.comment_post_ID']) ->where('comments.comment_type', '=', 'comment') ->where('comments.comment_approved', '=', '1') ->where('post_status', '=', 'publish') ->where('post_type', 'IN', $args['post_type']) ->whereDate('post_date', $args['date']) ->groupBy('post_author') ->getQuery(); $viewsQuery = Query::select(['DISTINCT post_author', 'SUM(count) AS total_views']) ->from('posts') ->join('pages', ['posts.ID', 'pages.id']) ->where('post_status', '=', 'publish') ->where('post_type', 'IN', $args['post_type']) ->whereDate('pages.date', $args['date']) ->groupBy('post_author') ->getQuery(); $authorQuery = Query::select(['post_author']) ->from('posts') ->where('post_status', '=', 'publish') ->where('post_type', 'IN', $args['post_type']) ->groupBy('post_author') ->getQuery(); $fields = [ 'users.ID AS id', 'users.display_name AS name', 'COUNT(DISTINCT posts.ID) AS total_posts', 'comments.total_comments AS total_comments', 'views.total_views AS total_views', 'comments.total_comments / COUNT(DISTINCT posts.ID) AS average_comments', 'views.total_views / COUNT(DISTINCT posts.ID) AS average_views' ]; if (WordCountService::isActive()) { $fields[] = 'words.total_words AS total_words'; $fields[] = 'words.total_words / COUNT(DISTINCT posts.ID) AS average_words'; } $query = Query::select($fields) ->from('users') ->join( 'posts', ['users.ID', 'posts.post_author'], [ ['posts.post_status', '=', 'publish'], ['posts.post_type', 'IN', $args['post_type']], ['DATE(posts.post_date)', 'BETWEEN', [$args['date']['from'], $args['date']['to']]] ], 'LEFT' ) ->joinQuery($authorQuery, ['users.ID', 'authors.post_author'], 'authors') ->joinQuery($commentsQuery, ['users.ID', 'comments.post_author'], 'comments', 'LEFT') ->joinQuery($viewsQuery, ['users.ID', 'views.post_author'], 'views', 'LEFT') ->groupBy(['users.ID', 'users.display_name']) ->orderBy($args['order_by'], $args['order']) ->perPage($args['page'], $args['per_page']); if (WordCountService::isActive()) { $wordsQuery = Query::select(['DISTINCT post_author', 'SUM(meta_value) AS total_words']) ->from('posts') ->join('postmeta', ['posts.ID', 'postmeta.post_id']) ->where('postmeta.meta_key', '=', 'wp_statistics_words_count') ->where('post_status', '=', 'publish') ->where('post_type', 'IN', $args['post_type']) ->whereDate('post_date', $args['date']) ->groupBy('post_author') ->getQuery(); $query->joinQuery($wordsQuery, ['users.ID', 'words.post_author'], 'words', 'LEFT'); } $result = $query->getAll(); return $result ? $result : []; } public function getAuthorsPagesData($args = []) { $args = $this->parseArgs($args, [ 'date' => '', 'post_type' => Helper::get_list_post_type(), 'order_by' => 'page_views', 'order' => 'DESC', 'page' => 1, 'per_page' => 5 ]); $viewsQuery = Query::select(['id AS author_id', 'SUM(count) AS total_author_views']) ->from('pages') ->where('type', '=', 'author') ->whereDate('date', $args['date']) ->groupBy('id') ->getQuery(); $authorQuery = Query::select(['post_author']) ->from('posts') ->where('post_status', '=', 'publish') ->where('post_type', 'IN', $args['post_type']) ->groupBy('post_author') ->getQuery(); $result = Query::select([ 'users.ID AS id', 'users.display_name AS name', 'COALESCE(COUNT(DISTINCT posts.ID), 0) AS total_posts', 'COALESCE(views.total_author_views, 0) AS page_views' ]) ->from('users') ->join( 'posts', ['users.ID', 'posts.post_author'], [ ['posts.post_status', '=', 'publish'], ['posts.post_type', 'IN', $args['post_type']], ['DATE(posts.post_date)', 'BETWEEN', [$args['date']['from'], $args['date']['to']]] ], 'LEFT' ) ->joinQuery($authorQuery, ['users.ID', 'authors.post_author'], 'authors') ->joinQuery($viewsQuery, ['users.ID', 'views.author_id'], 'views', 'LEFT') ->groupBy(['users.ID', 'users.display_name']) ->orderBy($args['order_by'], $args['order']) ->perPage($args['page'], $args['per_page']) ->getAll(); return $result ? $result : []; } }PK��z[�� )�� )��EventsModel.phpnu�ȯ��<?php namespace WP_Statistics\Models; use WP_Statistics\Abstracts\BaseModel; use WP_STATISTICS\Admin_Template; use WP_Statistics\Components\DateTime; use WP_Statistics\Utils\Query; class EventsModel extends BaseModel { public function countEvents($args = []) { $args = $this->parseArgs($args, [ 'event_name' => '', 'event_target' => '', 'author_id' => '', 'post_type' => '', 'post_id' => '', 'date' => '', 'group_by' => '', 'field' => '', 'not_null' => '' ]); $field = !empty($args['field']) ? $args['field'] : ''; $query = Query::select("COUNT($field)") ->from('events') ->where('event_name', 'IN', $args['event_name']) ->where('events.page_id', '=', $args['post_id']) ->whereJson('event_data', 'target_url', '=', $args['event_target']) ->whereDate('events.date', $args['date']) ->groupBy($args['group_by']); if (!empty($args['author_id']) \|\| !empty($args['post_type'])) { $query ->join('posts', ['events.page_id', 'posts.ID']) ->where('posts.post_type', 'IN', $args['post_type']) ->where('posts.post_author', '=', $args['author_id']); } $result = $query->getVar(); return $result ?? 0; } public function getInitialEventDate($args = []) { $args = $this->parseArgs($args, [ 'event_name' => '' ]); $result = Query::select('MIN(events.date) as date') ->from('events') ->where('event_name', '=', $args['event_name']) ->allowCaching() ->getVar(); return $result; } public function getEvents($args = []) { $args = $this->parseArgs($args, [ 'fields' => '', 'page' => 1, 'per_page' => Admin_Template::$item_per_page, 'event_name' => '', 'event_target' => '', 'author_id' => '', 'post_type' => '', 'post_id' => '', 'date' => '', 'decorator' => '', 'order' => 'date', 'order_by' => 'DESC', ]); $query = Query::select($args['fields']) ->from('events') ->where('event_name', 'IN', $args['event_name']) ->where('events.page_id', '=', $args['post_id']) ->whereJson('event_data', 'target_url', '=', $args['event_target']) ->whereDate('events.date', $args['date']) ->orderBy($args['order'], $args['order_by']) ->perPage($args['page'], $args['per_page']) ->decorate($args['decorator']); if (!empty($args['author_id']) \|\| !empty($args['post_type']) \|\| !empty($args['post_id'])) { $query ->join('posts', ['events.page_id', 'posts.ID']) ->where('posts.post_type', '=', $args['post_type']) ->where('posts.post_author', '=', $args['author_id']); } return $query->getAll(); } public function countDailyEvents($args = []) { $args = $this->parseArgs($args, [ 'event_name' => '', 'event_target' => '', 'author_id' => '', 'post_type' => '', 'post_id' => '', 'date' => '', 'decorator' => '', 'order' => 'date', 'order_by' => 'DESC', ]); $query = Query::select('COUNT(events.ID) as count, DATE(events.date) as date') ->from('events') ->where('event_name', 'IN', $args['event_name']) ->where('events.page_id', '=', $args['post_id']) ->whereJson('event_data', 'target_url', '=', $args['event_target']) ->whereDate('events.date', $args['date']) ->orderBy($args['order'], $args['order_by']) ->groupBy('Date(events.date)') ->decorate($args['decorator']); if (!empty($args['author_id']) \|\| !empty($args['post_type'])) { $query ->join('posts', ['events.page_id', 'posts.ID']) ->where('posts.post_type', 'IN', $args['post_type']) ->where('posts.post_author', '=', $args['author_id']); } return $query->getAll(); } public function countEventsByPage($args = []) { $args = $this->parseArgs($args, [ 'event_name' => '', 'event_target' => '', 'author_id' => '', 'post_type' => '', 'post_id' => '', 'date' => '', 'decorator' => '', 'per_page' => '', 'page' => 1, 'order' => 'count', 'order_by' => 'DESC', ]); $query = Query::select('COUNT(events.ID) as count, events.page_id, events.event_data') ->from('events') ->where('event_name', 'IN', $args['event_name']) ->where('events.page_id', '=', $args['post_id']) ->whereJson('event_data', 'target_url', '=', $args['event_target']) ->whereDate('events.date', $args['date']) ->orderBy($args['order'], $args['order_by']) ->groupBy('events.page_id') ->decorate($args['decorator']) ->whereNotNull('events.page_id') ->perPage($args['page'], $args['per_page']); if (!empty($args['author_id']) \|\| !empty($args['post_type']) \|\| !empty($args['post_id'])) { $query ->join('posts', ['events.page_id', 'posts.ID']) ->where('posts.post_type', '=', $args['post_type']) ->where('posts.post_author', '=', $args['author_id']); } return $query->getAll(); } public function countEventVisitorsByPage($args = []) { $args = $this->parseArgs($args, [ 'event_name' => '', 'author_id' => '', 'post_type' => '', 'post_id' => '', 'date' => '', 'per_page' => 10, 'page' => 1, 'order' => 'visitors', 'order_by' => 'DESC', ]); $query = Query::select('COUNT(DISTINCT events.visitor_id) as visitors, events.page_id as post_id, posts.post_title as title') ->from('events') ->join('posts', ['events.page_id', 'posts.ID']) ->where('events.page_id', '=', $args['post_id']) ->where('posts.post_type', '=', $args['post_type']) ->where('posts.post_author', '=', $args['author_id']) ->where('event_name', 'IN', $args['event_name']) ->whereDate('events.date', $args['date']) ->orderBy($args['order'], $args['order_by']) ->groupBy('events.page_id') ->whereNotNull('events.page_id') ->perPage($args['page'], $args['per_page']); return $query->getAll(); } public function getTopEvents($args = []) { $args = $this->parseArgs($args, [ 'date' => '', 'post_id' => '', 'post_type' => '', 'author_id' => '', 'page' => 1, 'per_page' => Admin_Template::$item_per_page, 'event_name'=> '', 'decorator' => '' ]); $query = Query::select([ "JSON_UNQUOTE(JSON_EXTRACT(`event_data`, '$.target_url')) AS target_url", "event_data", "COUNT() AS count" ]) ->from('events') ->where('event_name', 'IN', $args['event_name']) ->where('events.page_id', '=', $args['post_id']) ->whereDate('events.date', $args['date']) ->orderBy('count', 'DESC') ->perPage($args['page'], $args['per_page']) ->groupBy('target_url') ->decorate($args['decorator']); if (!empty($args['author_id']) \|\| !empty($args['post_type']) \|\| !empty($args['post_id'])) { $query ->join('posts', ['events.page_id', 'posts.ID']) ->where('posts.post_type', '=', $args['post_type']) ->where('posts.post_author', '=', $args['author_id']); } return $query->getAll(); } public function countTopEvents($args = []) { $args = $this->parseArgs($args, [ 'event_name' => '', 'author_id' => '', 'post_type' => '', 'post_id' => '', 'date' => '', ]); $subQuery = Query::select("JSON_UNQUOTE(JSON_EXTRACT(`event_data`, '$.target_url')) AS url") ->from('events') ->where('event_name', 'IN', $args['event_name']) ->where('events.page_id', '=', $args['post_id']) ->whereDate('events.date', $args['date']) ->groupBy('url'); if (!empty($args['author_id']) \|\| !empty($args['post_type']) \|\| !empty($args['post_id'])) { $subQuery ->join('posts', ['events.page_id', 'posts.ID']) ->where('posts.post_type', '=', $args['post_type']) ->where('posts.post_author', '=', $args['author_id']); } $result = Query::select('COUNT(url)') ->fromQuery($subQuery->getQuery()) ->getVar(); return $result; } public function insertEvent($args) { $data = [ 'date' => DateTime::get('now', 'Y-m-d H:i:s'), 'page_id' => $args['page_id'], 'visitor_id' => $args['visitor_id'], 'user_id' => $args['user_id'] ?? null, 'event_name' => $args['event_name'], 'event_data' => json_encode($args['event_data']) ]; $result = Query::insert('events') ->set($data) ->execute(); return $result; } public function deleteEvents($args) { $args = $this->parseArgs($args, [ 'event_name' => '' ]); $result = Query::delete('events') ->where('event_name', '=', $args['event_name']) ->execute(); return $result; } }PK��z[J��ExclusionsModel.phpnu�ȯ��<?php namespace WP_Statistics\Models; use WP_Statistics\Abstracts\BaseModel; use WP_Statistics\Utils\Query; class ExclusionsModel extends BaseModel { public function countExclusions($args = []) { $args = $this->parseArgs($args, [ 'date' => '', 'reason' => '' ]); $result = Query::select(['SUM(count) as count']) ->from('exclusions') ->where('reason', '=', $args['reason']) ->whereDate('date', $args['date']) ->orderBy('date') ->getVar(); return $result ?? 0; } public function getExclusions($args = []) { $args = $this->parseArgs($args, [ 'date' => '', 'reason' => '', 'exclusion_id' => '', 'per_page' => '', 'page' => 1, 'order_by' => 'count', 'order' => 'DESC', 'group_by' => 'reason', ]); $result = Query::select([ 'reason', 'date', 'SUM(count) as count' ]) ->from('exclusions') ->where('reason', '=', $args['reason']) ->where('id', '=', $args['exclusion_id']) ->whereDate('date', $args['date']) ->perPage($args['page'], $args['per_page']) ->groupBy($args['group_by']) ->orderBy($args['order_by'], $args['order']) ->getAll(); return $result; } } PK��z[>�ρ��HistoricalModel.phpnu�ȯ��<?php namespace WP_Statistics\Models; use WP_Statistics\Utils\Query; use WP_STATISTICS\Helper; use WP_Statistics\Utils\Url; class HistoricalModel { /* * Store the current resource ID. * * @var int\|null / private $resourceId = null; /* * Store the current resource type. * * @var string\|null / private $type = null; /* * Parse and validate the arguments for processing visitors data. * * This method ensures that the arguments meet the required criteria * by checking for the presence of either 'historical' or 'ignore_date' * keys. If any additional keys are non-empty, the method returns null. * * @param array $args Associative array of arguments to parse. Must include * either 'historical' or 'ignore_date' as a key. * @param array $defaults Optional. Default values to merge with the provided arguments. * Defaults to an empty array. * * @return array\|null Parsed arguments if valid; null otherwise. * @todo We have to migrate to the baseModel. we have to add a list of the allowed arguments to prevent passing extra args. / private function parseVisitorsArgs($args, $defaults = []) { if (empty($args['historical']) && empty($args['ignore_date'])) { return null; } $args = wp_parse_args($args, $defaults); $allowedArgs = ['ignore_post_type', 'ignore_date', 'historical', 'include_total', 'exclude', 'date_field']; foreach ($args as $key => $value) { if (in_array($key, $allowedArgs, true)) { continue; } if (! empty($value)) { return null; } } return $args; } /* * Parse and cache the arguments for reuse. * * @param array $args Arguments to be parsed. Must contain either 'historical' or 'ignore_date'. * @param array $defaults Optional. Default values to merge with provided arguments. * * @return array\|null Parsed and enhanced arguments, or null if required arguments are missing. / private function parseViewsArgs($args, $defaults = []) { if (empty($args['historical']) && empty($args['ignore_date'])) { return null; } $args = wp_parse_args($args, $defaults); $args['resource_id'] = $this->getResourceId($args); $args['uri'] = $this->getResourceUri($args); if (! empty($args['uri']) && ! empty($args['resource_id'])) { $args['category'] = 'uri'; } return $args; } /* * Get the ID value from a resource array argument. * * @param array $args { * Arguments containing resource ID. * * @type int $post_id Optional. Post ID to retrieve. * @type int $term Optional. Term ID to retrieve. * @type int $author_id Optional. Author ID to retrieve. * } * * @return int\|null The ID of the first found resource, or null if no resource ID specified / public function getResourceId($args) { if (!empty($args['post_id'])) { $this->resourceId = $args['post_id']; $this->type = 'post'; return $this->resourceId; } if (!empty($args['term'])) { $this->resourceId = $args['term']; $this->type = 'taxonomy'; return $this->resourceId; } if (!empty($args['author_id'])) { $this->resourceId = $args['author_id']; $this->type = 'author'; return $this->resourceId; } $this->resourceId = null; $this->type = null; return null; } /* * Get relative URI for a WordPress resource (post, term, or author). * * @param array $args { * Arguments for determining the resource URI. * * @type int $post_id Optional. Post ID to get its URI. * @type int $term Optional. Term ID to get its URI. * @type string $taxonomy Required if $term is set. The taxonomy name. * @type int $author_id Optional. Author ID to get their archive URI. * } * @return string\|null Relative path to the resource, or null if no valid resource specified / public function getResourceUri($args) { if (! empty($args['uri'])) { return $args['uri']; } if ($this->type === 'taxonomy') { return Helper::getResourcePath($this->resourceId, $args['taxonomy']); } if ($this->type === 'author') { return Helper::getResourcePath($this->resourceId, 'author'); } if ($this->type === 'post') { $postType = strtolower(Helper::getPostTypeName(get_post_type($this->resourceId), true)); return Helper::getResourcePath($this->resourceId, $postType); } return null; } /* * Get the total number of historical visitors. * * @param array $args { * Optional. An array of arguments for filtering historical visitor data. * * @type bool $ignore_date Whether to ignore date constraints when counting visitors. * Default false. When false, returns 0. * } * @return int Total number of historical visitors. / public function getVisitors($args) { $args = $this->parseVisitorsArgs($args); if (is_null($args)) { return 0; } $result = Query::select('SUM(`value`) AS `historical_views`') ->from('historical') ->where('category', '=', 'visitors') ->getVar(); return $result ?? 0; } /* * Get the total number of historical views based on provided arguments. * * @param array $args { * Arguments for retrieving views. * * @type string $type Optional. Type of view count to retrieve ('uri' for specific URI views). * @type int $post_id Optional. Post ID to get views for. * @type string $uri Optional. URI to get views for. * @type bool $ignore_date Optional. Whether to ignore date in URI comparison. * } * @return int Total number of historical views. / public function getViews($args) { $args = $this->parseViewsArgs($args, [ 'resource_id' => '', 'uri' => '', 'category' => 'visits', ]); if (is_null($args)) { return 0; } $result = Query::select('SUM(`value`) AS `historical_views`') ->from('historical') ->where('page_id', '=', $args['resource_id']) ->where('uri', '=', $args['uri']) ->where('category', '=', $args['category']) ->getVar(); return $result ?? 0; } } PK��z[�p��p��OnlineModel.phpnu�ȯ��<?php namespace WP_Statistics\Models; use WP_Statistics\Abstracts\BaseModel; use WP_Statistics\Decorators\VisitorDecorator; use WP_Statistics\Utils\Query; class OnlineModel extends BaseModel { public function countOnlines($args = []) { $args = $this->parseArgs($args, []); $result = Query::select('COUNT()') ->from('useronline') ->getVar(); return $result ? $result : 0; } public function getOnlineVisitorsData($args = []) { $args = $this->parseArgs($args, [ 'page' => 1, 'per_page' => '', 'order_by' => '', 'order' => '', ]); $result = Query::select([ 'useronline.ID as online_id', 'visitor_id as ID', 'useronline.ip', 'useronline.created', 'useronline.timestamp', 'visitor.referred', 'visitor.agent', 'visitor.platform', 'visitor.version', 'visitor.location', 'visitor.region', 'visitor.city', 'visitor.hits', 'visitor.source_name', 'visitor.source_channel', 'visitor.model', 'visitor.device', 'visitor.user_id', 'visitor.last_counter', 'visitor.last_page as last_page', 'visitor.last_view as last_view', 'users.display_name', 'users.user_email' ]) ->from('useronline') ->join('visitor', ['useronline.visitor_id', 'visitor.ID']) ->join('users', ['visitor.user_id', 'users.ID'], [], 'LEFT') ->perPage($args['page'], $args['per_page']) ->orderBy($args['order_by'], $args['order']) ->decorate(VisitorDecorator::class) ->getAll(); return $result ? $result : []; } }PK��z[�!(F��(F��PostsModel.phpnu�ȯ��<?php namespace WP_Statistics\Models; use WP_Statistics\Abstracts\BaseModel; use WP_STATISTICS\Helper; use WP_Statistics\Service\Admin\Posts\WordCountService; use WP_Statistics\Utils\Query; class PostsModel extends BaseModel { public function countPosts($args = []) { $args = $this->parseArgs($args, [ 'date' => '', 'post_type' => Helper::getPostTypes(), 'author_id' => '', 'taxonomy' => '', 'term' => '', 'filter_by_view_date' => false, 'url' => '' ]); $query = Query::select('COUNT()') ->from('posts') ->where('post_status', '=', 'publish') ->where('post_type', 'IN', $args['post_type']) ->where('post_author', '=', $args['author_id']); // Count posts within view date if ($args['filter_by_view_date'] == true) { $viewsQuery = Query::select(['pages.id', 'SUM(pages.count) AS views']) ->from('pages') ->where('uri', 'LIKE', "%{$args['url']}%") ->whereDate('pages.date', $args['date']) ->groupBy('pages.id') ->getQuery(); $query->joinQuery($viewsQuery, ['posts.ID', 'views.id'], 'views'); } else { $query ->whereDate('post_date', $args['date']); } if (!empty($args['taxonomy']) \|\| !empty($args['term'])) { $taxQuery = Query::select(['DISTINCT object_id']) ->from('term_relationships') ->join('term_taxonomy', ['term_relationships.term_taxonomy_id', 'term_taxonomy.term_taxonomy_id']) ->join('terms', ['term_taxonomy.term_id', 'terms.term_id']) ->where('term_taxonomy.taxonomy', 'IN', $args['taxonomy']) ->where('terms.term_id', '=', $args['term']) ->getQuery(); $query ->joinQuery($taxQuery, ['posts.ID', 'tax.object_id'], 'tax'); } $result = $query->getVar(); return $result ? $result : 0; } public function countDailyPosts($args = []) { $args = $this->parseArgs($args, [ 'date' => '', 'post_type' => '', 'author_id' => '', 'taxonomy' => '', 'term' => '' ]); $query = Query::select('COUNT() as posts, Date(post_date) as date') ->from('posts') ->where('post_status', '=', 'publish') ->where('post_type', 'IN', $args['post_type']) ->where('post_author', '=', $args['author_id']) ->whereDate('post_date', $args['date']) ->groupBy('Date(post_date)'); if (!empty($args['taxonomy']) \|\| !empty($args['term'])) { $taxQuery = Query::select(['DISTINCT object_id']) ->from('term_relationships') ->join('term_taxonomy', ['term_relationships.term_taxonomy_id', 'term_taxonomy.term_taxonomy_id']) ->join('terms', ['term_taxonomy.term_id', 'terms.term_id']) ->where('term_taxonomy.taxonomy', 'IN', $args['taxonomy']) ->where('terms.term_id', '=', $args['term']) ->getQuery(); $query ->joinQuery($taxQuery, ['posts.ID', 'tax.object_id'], 'tax'); } $result = $query->getAll(); return $result; } public function countWords($args = []) { $args = $this->parseArgs($args, [ 'date' => '', 'post_type' => '', 'author_id' => '', 'post_id' => '', 'taxonomy' => '', 'term' => '' ]); $wordsCountMetaKey = WordCountService::WORDS_COUNT_META_KEY; $query = Query::select('SUM(meta_value)') ->from('posts') ->join('postmeta', ['posts.ID', 'postmeta.post_id']) ->where('post_status', '=', 'publish') ->where('post_type', 'IN', $args['post_type']) ->where('posts.ID', '=', $args['post_id']) ->where('post_author', '=', $args['author_id']) ->where('meta_key', '=', $wordsCountMetaKey); // Filter by date when no particular post ID has been set if (empty($args['post_id'])) { $query ->whereDate('post_date', $args['date']); } if (!empty($args['taxonomy']) \|\| !empty($args['term'])) { $taxQuery = Query::select(['DISTINCT object_id']) ->from('term_relationships') ->join('term_taxonomy', ['term_relationships.term_taxonomy_id', 'term_taxonomy.term_taxonomy_id']) ->join('terms', ['term_taxonomy.term_id', 'terms.term_id']) ->where('term_taxonomy.taxonomy', 'IN', $args['taxonomy']) ->where('terms.term_id', '=', $args['term']) ->getQuery(); $query ->joinQuery($taxQuery, ['posts.ID', 'tax.object_id'], 'tax'); } $result = $query->getVar(); return $result ? $result : 0; } public function countComments($args = []) { $args = $this->parseArgs($args, [ 'date' => '', 'post_type' => '', 'author_id' => '', 'post_id' => '', 'taxonomy' => '', 'term' => '' ]); $query = Query::select('COUNT(comment_ID)') ->from('posts') ->join('comments', ['posts.ID', 'comments.comment_post_ID']) ->where('post_status', '=', 'publish') ->where('post_type', 'IN', $args['post_type']) ->where('post_author', '=', $args['author_id']) ->where('comments.comment_type', '=', 'comment') ->where('comments.comment_approved', '=', '1') ->where('posts.ID', '=', $args['post_id']) ->whereDate('comment_date', $args['date']); if (!empty($args['taxonomy']) \|\| !empty($args['term'])) { $taxQuery = Query::select(['DISTINCT object_id']) ->from('term_relationships') ->join('term_taxonomy', ['term_relationships.term_taxonomy_id', 'term_taxonomy.term_taxonomy_id']) ->join('terms', ['term_taxonomy.term_id', 'terms.term_id']) ->where('term_taxonomy.taxonomy', 'IN', $args['taxonomy']) ->where('terms.term_id', '=', $args['term']) ->getQuery(); $query ->joinQuery($taxQuery, ['posts.ID', 'tax.object_id'], 'tax'); } $result = $query->getVar(); return $result ? $result : 0; } public function getPostsReportData($args = []) { $args = $this->parseArgs($args, [ 'date' => '', 'post_type' => Helper::get_list_post_type(), 'resource_type' => Helper::get_list_post_type(), 'order_by' => 'title', 'order' => 'DESC', 'page' => 1, 'per_page' => 5, 'author_id' => '', 'url' => '' ]); $visitorsQuery = Query::select(['pages.id as post_id', 'COUNT(DISTINCT visitor_relationships.visitor_id) AS visitors']) ->from('visitor_relationships') ->join('pages', ['pages.page_id', 'visitor_relationships.page_id']) ->where('type', 'IN', $args['resource_type']) ->whereDate('visitor_relationships.date', $args['date']) ->groupBy('pages.id') ->getQuery(); $viewsQuery = Query::select(['pages.id', 'SUM(pages.count) AS views']) ->from('pages') ->where('type', 'IN', $args['resource_type']) ->where('uri', 'LIKE', "%{$args['url']}%") ->whereDate('pages.date', $args['date']) ->groupBy('pages.id') ->getQuery(); $fields = [ 'posts.ID AS post_id', 'posts.post_author AS author_id', 'posts.post_title AS title', 'posts.post_date AS date', 'COALESCE(pages.views, 0) AS views', 'COALESCE(visitors.visitors, 0) AS visitors', ]; if (WordCountService::isActive()) { $fields[] = "CAST(MAX(CASE WHEN postmeta.meta_key = 'wp_statistics_words_count' THEN postmeta.meta_value ELSE 0 END) AS UNSIGNED) AS words"; } $query = Query::select($fields) ->from('posts') ->joinQuery($viewsQuery, ['posts.ID', 'pages.id'], 'pages') ->joinQuery($visitorsQuery, ['posts.ID', 'visitors.post_id'], 'visitors', 'LEFT') ->where('post_type', 'IN', $args['post_type']) ->where('post_status', '=', 'publish') ->where('posts.post_author', '=', $args['author_id']) ->groupBy('posts.ID') ->orderBy($args['order_by'], $args['order']) ->perPage($args['page'], $args['per_page']); if (WordCountService::isActive()) { $query->join('postmeta', ['posts.ID', 'postmeta.post_id'], [], 'LEFT'); } $result = $query->getAll(); return $result; } public function getPostsViewsData($args = []) { $args = $this->parseArgs($args, [ 'date' => '', 'post_type' => Helper::get_list_post_type(), 'order_by' => 'views', 'order' => 'DESC', 'page' => 1, 'per_page' => 5, 'author_id' => '', 'taxonomy' => '', 'term' => '', 'show_no_views' => false ]); // Get posts with zero views or not $joinType = $args['show_no_views'] ? 'LEFT' : 'INNER'; $viewsQuery = Query::select(['id', 'SUM(count) AS views']) ->from('pages') ->whereDate('date', $args['date']) ->groupBy('id') ->getQuery(); $query = Query::select([ 'posts.ID', 'posts.post_author', 'posts.post_title', 'posts.post_date', 'COALESCE(pages.views, 0) AS views', ]) ->from('posts') ->joinQuery($viewsQuery, ['posts.ID', 'pages.id'], 'pages', $joinType) ->where('post_type', 'IN', $args['post_type']) ->where('post_status', '=', 'publish') ->where('posts.post_author', '=', $args['author_id']) ->groupBy('posts.ID') ->orderBy($args['order_by'], $args['order']) ->perPage($args['page'], $args['per_page']); if (!empty($args['taxonomy']) \|\| !empty($args['term'])) { $taxQuery = Query::select(['DISTINCT object_id']) ->from('term_relationships') ->join('term_taxonomy', ['term_relationships.term_taxonomy_id', 'term_taxonomy.term_taxonomy_id']) ->join('terms', ['term_taxonomy.term_id', 'terms.term_id']) ->where('term_taxonomy.taxonomy', 'IN', $args['taxonomy']) ->where('terms.term_id', '=', $args['term']) ->getQuery(); $query ->joinQuery($taxQuery, ['posts.ID', 'tax.object_id'], 'tax'); } $result = $query->getAll(); return $result; } public function getPostsVisitorsData($args = []) { $args = $this->parseArgs($args, [ 'date' => '', 'resource_type' => Helper::get_list_post_type(), 'post_type' => Helper::get_list_post_type(), 'order_by' => 'visitors', 'order' => 'DESC', 'page' => 1, 'per_page' => 10, 'author_id' => '', 'event_name' => '', 'post_ids' => [] ]); $visitorsQuery = Query::select(['pages.id as post_id', 'COUNT(DISTINCT visitor_relationships.visitor_id) AS visitors']) ->from('visitor_relationships') ->join('pages', ['pages.page_id', 'visitor_relationships.page_id']) ->where('type', 'IN', $args['resource_type']) ->whereDate('visitor_relationships.date', $args['date']) ->groupBy('pages.id') ->getQuery(); $query = Query::select([ 'posts.ID AS post_id', 'posts.post_title AS title', 'COALESCE(visitors.visitors, 0) AS visitors', ]) ->from('posts') ->joinQuery($visitorsQuery, ['posts.ID', 'visitors.post_id'], 'visitors', 'LEFT') ->where('posts.ID', 'IN', $args['post_ids']) ->where('post_type', 'IN', $args['post_type']) ->where('post_status', '=', 'publish') ->where('posts.post_author', '=', $args['author_id']) ->groupBy('posts.ID') ->orderBy($args['order_by'], $args['order']) ->perPage($args['page'], $args['per_page']); if (!empty($args['event_name'])) { $query ->join('events', ['events.page_id', 'posts.ID']) ->where('event_name', 'IN', $args['event_name']); } return $query->getAll(); } public function getPostsCommentsData($args = []) { $args = $this->parseArgs($args, [ 'date' => '', 'post_type' => Helper::get_list_post_type(), 'order_by' => 'comments', 'order' => 'DESC', 'page' => 1, 'per_page' => 5, 'author_id' => '', 'taxonomy' => '', 'term' => '', ]); $query = Query::select([ 'posts.ID', 'posts.post_author', 'posts.post_title', 'COALESCE(COUNT(comment_ID), 0) AS comments', ]) ->from('posts') ->join('comments', ['posts.ID', 'comments.comment_post_ID']) ->where('post_type', 'IN', $args['post_type']) ->where('post_status', '=', 'publish') ->where('posts.post_author', '=', $args['author_id']) ->where('comments.comment_type', '=', 'comment') ->where('comments.comment_approved', '=', '1') ->whereDate('posts.post_date', $args['date']) ->groupBy('posts.ID') ->orderBy($args['order_by'], $args['order']) ->perPage($args['page'], $args['per_page']); if (!empty($args['taxonomy']) \|\| !empty($args['term'])) { $taxQuery = Query::select(['DISTINCT object_id']) ->from('term_relationships') ->join('term_taxonomy', ['term_relationships.term_taxonomy_id', 'term_taxonomy.term_taxonomy_id']) ->join('terms', ['term_taxonomy.term_id', 'terms.term_id']) ->where('term_taxonomy.taxonomy', 'IN', $args['taxonomy']) ->where('terms.term_id', '=', $args['term']) ->getQuery(); $query ->joinQuery($taxQuery, ['posts.ID', 'tax.object_id'], 'tax'); } $result = $query->getAll(); return $result; } public function getPostsWordsData($args = []) { $args = $this->parseArgs($args, [ 'date' => '', 'post_type' => Helper::get_list_post_type(), 'order_by' => 'words', 'order' => 'DESC', 'page' => 1, 'per_page' => 5, 'author_id' => '' ]); $result = Query::select([ 'posts.ID', 'posts.post_author', 'posts.post_title', "MAX(CASE WHEN postmeta.meta_key = 'wp_statistics_words_count' THEN postmeta.meta_value ELSE 0 END) AS words", ]) ->from('posts') ->join('postmeta', ['posts.ID', 'postmeta.post_id'], [], 'LEFT') ->where('post_type', 'IN', $args['post_type']) ->where('post_status', '=', 'publish') ->where('posts.post_author', '=', $args['author_id']) ->whereDate('posts.post_date', $args['date']) ->groupBy('posts.ID') ->orderBy($args['order_by'], $args['order']) ->perPage($args['page'], $args['per_page']) ->getAll(); return $result; } public function getInitialPostDate($args = []) { $args = [ 'post_type' => Helper::getPostTypes() ]; $result = Query::select(['MIN(post_date) AS date']) ->from('posts') ->where('post_type', 'IN', $args['post_type']) ->allowCaching() ->getVar(); return $result; } public function get404Data($args = []) { $args = $this->parseArgs($args, [ 'date' => '', 'order_by' => 'views', 'order' => 'DESC', 'page' => 1, 'per_page' => 10, ]); $result = Query::select(['uri', 'SUM(count) AS views']) ->from('pages') ->where('type', '=', '404') ->whereDate('pages.date', $args['date']) ->groupBy('uri') ->orderBy($args['order_by'], $args['order']) ->perPage($args['page'], $args['per_page']) ->getAll(); return $result; } public function count404Data($args = []) { $args = $this->parseArgs($args, [ 'date' => '', 'page' => 1, 'per_page' => 10, ]); $result = Query::select(['COUNT(DISTINCT uri)']) ->from('pages') ->where('type', '=', '404') ->whereDate('pages.date', $args['date']) ->getVar(); return $result; } } PK��z[��w�"��"��TaxonomyModel.phpnu�ȯ��<?php namespace WP_Statistics\Models; use WP_STATISTICS\Helper; use WP_Statistics\Utils\Query; use WP_Statistics\Abstracts\BaseModel; class TaxonomyModel extends BaseModel { public function countTerms($args = []) { $args = $this->parseArgs($args, [ 'date' => '', 'taxonomy' => '', ]); $result = Query::select([ 'COUNT()', ]) ->from('term_taxonomy') ->where('taxonomy', 'IN', $args['taxonomy']) ->getVar(); return $result; } public function getTaxonomiesData($args = []) { $args = $this->parseArgs($args, [ 'post_type' => Helper::get_list_post_type(), 'order_by' => ['term_taxonomy.taxonomy', 'post_count'], 'taxonomy' => array_keys(Helper::get_list_taxonomy(true)), 'resource_type' => array_keys(Helper::get_list_taxonomy(true)), 'page' => 1, 'per_page' => 5, 'date' => '', 'author_id' => '', 'order' => '', 'count_total_posts' => false ]); $categoryViewsQuery = Query::select(['id', 'date', 'SUM(count) AS views']) ->from('pages') ->where('pages.type', 'IN', $args['resource_type']) ->whereDate('date', $args['date']) ->groupBy('id') ->getQuery(); $query = Query::select([ 'taxonomy', 'terms.term_id', 'terms.name', 'COUNT(DISTINCT posts.ID) as post_count', 'COALESCE(category.views, 0) as term_views' ]) ->from('term_taxonomy') ->join('terms', ['term_taxonomy.term_id', 'terms.term_id']) ->join('term_relationships', ['term_relationships.term_taxonomy_id', 'term_taxonomy.term_taxonomy_id'], [], 'LEFT') ->join('posts', ['posts.ID', 'term_relationships.object_id'], [['posts.post_type', 'IN', $args['post_type']], ['posts.post_status', '=', 'publish']], 'LEFT') ->joinQuery($categoryViewsQuery, ['category.id', 'term_taxonomy.term_taxonomy_id'], 'category', 'LEFT') ->where('term_taxonomy.taxonomy', 'IN', $args['taxonomy']) ->where('posts.post_author', '=', $args['author_id']) ->groupBy(['taxonomy', 'terms.term_id', 'terms.name']) ->orderBy($args['order_by'], $args['order']) ->perPage($args['page'], $args['per_page']); // If total posts is not requested, filter by date if ($args['count_total_posts'] == false) { $query->whereDate('posts.post_date', $args['date']); } $result = $query->getAll(); if (!empty($result)) { $taxonomies = []; foreach ($result as $item) { $taxonomies[$item->taxonomy][] = [ 'term_id' => $item->term_id, 'term_name' => $item->name, 'posts_count' => $item->post_count, 'views' => $item->term_views ]; } $result = $taxonomies; } return $result ? $result : []; } public function getTermsData($args = []) { $args = $this->parseArgs($args, [ 'order_by' => 'views', 'order' => '', 'page' => 1, 'per_page' => 5, 'date' => '', 'taxonomy' => '', 'author_id' => '', 'post_type' => Helper::getPostTypes(), 'date_field' => 'pages.date' ]); $result = Query::select([ 'terms.term_id', 'terms.name as term_name', 'SUM(pages.count) AS views', 'COUNT(DISTINCT posts.ID) AS posts' ]) ->from('posts') ->join('term_relationships', ['posts.ID', 'term_relationships.object_id']) ->join('term_taxonomy', ['term_relationships.term_taxonomy_id', 'term_taxonomy.term_taxonomy_id']) ->join('terms', ['term_taxonomy.term_id', 'terms.term_id']) ->join('pages', ['pages.id', 'posts.ID']) ->where('term_taxonomy.taxonomy', 'IN', $args['taxonomy']) ->where('posts.post_type', 'IN', $args['post_type']) ->where('posts.post_author', '=', $args['author_id']) ->whereDate($args['date_field'], $args['date']) ->groupBy(['term_id']) ->orderBy($args['order_by'], $args['order']) ->perPage($args['page'], $args['per_page']) ->getAll(); return $result; } public function getTermsReportData($args = []) { $args = $this->parseArgs($args, [ 'order_by' => 'views', 'order' => '', 'page' => 1, 'per_page' => 5, 'date' => '', 'taxonomy' => '', 'author_id' => '', 'post_type' => Helper::getDefaultPostTypes(), 'date_field' => 'pages.date' ]); $wordsQuery = Query::select([ 'terms.term_id', 'terms.name as term_name', 'SUM(postmeta.meta_value) AS words', 'COUNT(DISTINCT posts.ID) AS posts' ]) ->from('postmeta') ->join('posts', ['postmeta.post_id', 'posts.ID']) ->join('term_relationships', ['posts.ID', 'term_relationships.object_id']) ->join('term_taxonomy', ['term_relationships.term_taxonomy_id', 'term_taxonomy.term_taxonomy_id']) ->join('terms', ['term_taxonomy.term_id', 'terms.term_id']) ->where('postmeta.meta_key', '=', 'wp_statistics_words_count') ->where('term_taxonomy.taxonomy', 'IN', $args['taxonomy']) ->where('posts.post_type', 'IN', $args['post_type']) ->where('posts.post_author', '=', $args['author_id']) ->whereDate('posts.post_date', $args['date']) ->groupBy(['terms.term_id']) ->getQuery(); $result = Query::select([ 'terms.term_id', 'terms.name as term_name', 'SUM(pages.count) AS views', 'COALESCE(postmeta.posts, 0) AS posts', 'COALESCE(postmeta.words, 0) AS words', 'COALESCE(SUM(pages.count) / postmeta.posts, 0) AS avg_views', 'COALESCE(postmeta.words / postmeta.posts, 0) AS avg_words' ]) ->from('posts') ->join('term_relationships', ['posts.ID', 'term_relationships.object_id']) ->join('term_taxonomy', ['term_relationships.term_taxonomy_id', 'term_taxonomy.term_taxonomy_id']) ->join('terms', ['term_taxonomy.term_id', 'terms.term_id']) ->join('pages', ['pages.id', 'posts.ID']) ->joinQuery($wordsQuery, ['postmeta.term_id', 'terms.term_id'], 'postmeta', 'LEFT') ->where('term_taxonomy.taxonomy', 'IN', $args['taxonomy']) ->where('posts.post_type', 'IN', $args['post_type']) ->where('posts.post_author', '=', $args['author_id']) ->whereDate($args['date_field'], $args['date']) ->groupBy(['term_id']) ->orderBy($args['order_by'], $args['order']) ->perPage($args['page'], $args['per_page']) ->getAll(); return $result; } }PK��z[6�ˇ"8��"8��ViewsModel.phpnu�ȯ��<?php namespace WP_Statistics\Models; use WP_STATISTICS\Helper; use WP_Statistics\Utils\Query; use WP_Statistics\Abstracts\BaseModel; use WP_Statistics\Components\DateRange; use WP_Statistics\Decorators\VisitorDecorator; class ViewsModel extends BaseModel { public function countViews($args = []) { $args = $this->parseArgs($args, [ 'post_type' => Helper::get_list_post_type(), 'resource_type' => '', 'date' => '', 'author_id' => '', 'post_id' => '', 'query_param' => '', 'taxonomy' => '', 'term' => '', 'ignore_post_type' => false ]); $viewsQuery = Query::select(['id', 'date', 'SUM(count) AS count']) ->from('pages') ->where('pages.type', 'IN', $args['resource_type']) ->whereDate('date', $args['date']) ->groupBy('id') ->where('pages.uri', '=', $args['query_param']) ->getQuery(); $query = Query::select('SUM(pages.count) as total_views') ->fromQuery($viewsQuery, 'pages'); if (!empty($args['author_id']) \|\| !empty($args['post_id']) \|\| !empty($args['taxonomy']) \|\| !empty($args['term']) \|\| (!empty($args['post_type']) && !$args['ignore_post_type'])) { $query ->join('posts', ['pages.id', 'posts.ID']) ->where('post_type', 'IN', $args['post_type']) ->where('post_author', '=', $args['author_id']) ->where('posts.ID', '=', $args['post_id']); if (!empty($args['taxonomy']) \|\| !empty($args['term'])) { $taxQuery = Query::select(['DISTINCT object_id']) ->from('term_relationships') ->join('term_taxonomy', ['term_relationships.term_taxonomy_id', 'term_taxonomy.term_taxonomy_id']) ->join('terms', ['term_taxonomy.term_id', 'terms.term_id']) ->where('term_taxonomy.taxonomy', 'IN', $args['taxonomy']) ->where('terms.term_id', '=', $args['term']) ->getQuery(); $query ->joinQuery($taxQuery, ['posts.ID', 'tax.object_id'], 'tax'); } } $total = $query->getVar(); $total = $total ? intval($total) : 0; $total += $this->historicalModel->getViews($args); return $total; } /* * Returns views from `pages` table without joining with other tables. * * Used for calculating taxonomies views (Unlike `countViews()` which is suited for calculating posts/pages/cpt views). * * @param array $args Arguments to include in query (e.g. `post_id`, `resource_type`, `query_param`, `date`, etc.). * * @return int / public function countViewsFromPagesOnly($args = []) { $args = $this->parseArgs($args, [ 'post_id' => '', 'resource_type' => '', 'query_param' => '', 'date' => '', ]); $query = Query::select(['SUM(`count`) AS `count`']) ->from('pages') ->where('pages.id', '=', $args['post_id']) ->where('pages.type', 'IN', $args['resource_type']) ->where('pages.uri', '=', $args['query_param']) ->whereDate('date', $args['date']); if (is_numeric($args['post_id'])) { $query->groupBy('id'); } $total = $query->getVar(); $total = $total ? intval($total) : 0; $total += $this->historicalModel->getViews($args); return $total; } public function countDailyViews($args = []) { $args = $this->parseArgs($args, [ 'post_type' => Helper::get_list_post_type(), 'ignore_post_type' => false, 'resource_type' => '', 'resource_id' => '', 'date' => '', 'author_id' => '', 'post_id' => '', 'query_param' => '', 'taxonomy' => '', 'term' => '', ]); $query = Query::select([ 'SUM(pages.count) as views', 'pages.date as date', ]) ->from('pages') ->where('pages.type', 'IN', $args['resource_type']) ->where('pages.id', '=', $args['resource_id']) ->where('pages.uri', '=', $args['query_param']) ->whereDate('pages.date', $args['date']) ->groupBy('pages.date'); if (empty($args['resource_id']) && (!empty($args['author_id']) \|\| !empty($args['post_id']) \|\| !empty($args['taxonomy']) \|\| !empty($args['term']) \|\| (!empty($args['post_type']) && !$args['ignore_post_type']))) { $query ->join('posts', ['pages.id', 'posts.ID']) ->where('post_author', '=', $args['author_id']) ->where('posts.ID', '=', $args['post_id']) ->where('post_type', 'IN', $args['post_type']); if (!empty($args['taxonomy']) \|\| !empty($args['term'])) { $taxQuery = Query::select(['DISTINCT object_id']) ->from('term_relationships') ->join('term_taxonomy', ['term_relationships.term_taxonomy_id', 'term_taxonomy.term_taxonomy_id']) ->join('terms', ['term_taxonomy.term_id', 'terms.term_id']) ->where('term_taxonomy.taxonomy', 'IN', $args['taxonomy']) ->where('terms.term_id', '=', $args['term']) ->getQuery(); $query ->joinQuery($taxQuery, ['posts.ID', 'tax.object_id'], 'tax'); } } $result = $query->getAll(); return $result ?? []; } public function getViewsData($args = []) { $args = $this->parseArgs($args, [ 'date' => '', 'page' => 1, 'per_page' => 20 ]); $result = Query::select([ 'visitor.ID', 'visitor.ip', 'visitor.platform', 'visitor.agent', 'version', 'visitor.model', 'visitor.device', 'visitor.region', 'visitor.city', 'visitor.location', 'visitor.hits', 'visitor.user_id', 'visitor.referred', 'visitor.source_channel', 'visitor.source_name', 'visitor_relationships.page_id as last_page', 'visitor_relationships.date as last_view', ]) ->from('visitor_relationships') ->join('visitor', ['visitor_relationships.visitor_id', 'visitor.ID']) ->whereDate('visitor_relationships.date', $args['date']) ->decorate(VisitorDecorator::class) ->orderBy('visitor_relationships.date') ->perPage($args['page'], $args['per_page']) ->getAll(); return $result; } public function countViewRecords($args = []) { $args = $this->parseArgs($args, [ 'date' => '', ]); $result = Query::select([ 'COUNT()', ]) ->from('visitor_relationships') ->whereDate('visitor_relationships.date', $args['date']) ->getVar(); return $result; } public function getHourlyViews($args = []) { $args = $this->parseArgs($args, [ 'date' => '' ]); $result = Query::select([ 'HOUR(date) as hour', 'COUNT(DISTINCT visitor_id) as visitors', 'COUNT() as views' ]) ->from('visitor_relationships') ->whereDate('visitor_relationships.date', $args['date']) ->groupBy('hour') ->getAll(); return $result; } public function getViewsSummary($args = []) { $summary = [ 'today' => [ 'label' => esc_html__('Today', 'wp-statistics'), 'views' => $this->countViews(array_merge($args, ['date' => DateRange::get('today')])) ], 'yesterday' => [ 'label' => esc_html__('Yesterday', 'wp-statistics'), 'views' => $this->countViews(array_merge($args, ['date' => DateRange::get('yesterday')])) ], 'this_week' => [ 'label' => esc_html__('This week', 'wp-statistics'), 'views' => $this->countViews(array_merge($args, ['date' => DateRange::get('this_week')])) ], 'last_week' => [ 'label' => esc_html__('Last week', 'wp-statistics'), 'views' => $this->countViews(array_merge($args, ['date' => DateRange::get('last_week')])) ], 'this_month' => [ 'label' => esc_html__('This month', 'wp-statistics'), 'views' => $this->countViews(array_merge($args, ['date' => DateRange::get('this_month')])) ], 'last_month' => [ 'label' => esc_html__('Last month', 'wp-statistics'), 'views' => $this->countViews(array_merge($args, ['date' => DateRange::get('last_month')])) ], '7days' => [ 'label' => esc_html__('Last 7 days', 'wp-statistics'), 'views' => $this->countViews(array_merge($args, ['date' => DateRange::get('7days')])) ], '30days' => [ 'label' => esc_html__('Last 30 days', 'wp-statistics'), 'views' => $this->countViews(array_merge($args, ['date' => DateRange::get('30days')])) ], '90days' => [ 'label' => esc_html__('Last 90 days', 'wp-statistics'), 'views' => $this->countViews(array_merge($args, ['date' => DateRange::get('90days')])) ], '6months' => [ 'label' => esc_html__('Last 6 months', 'wp-statistics'), 'views' => $this->countViews(array_merge($args, ['date' => DateRange::get('6months')])) ], 'this_year' => [ 'label' => esc_html__('This year (Jan-Today)', 'wp-statistics'), 'views' => $this->countViews(array_merge($args, ['date' => DateRange::get('this_year')])) ] ]; if (!empty($args['include_total'])) { $summary['total'] = [ 'label' => esc_html__('Total', 'wp-statistics'), 'views' => $this->countViews(array_merge($args, ['ignore_date' => true, 'historical' => true])) ]; } return $summary; } public function getViewedPageUri($args = []) { $args = $this->parseArgs($args, [ 'id' => '', ]); $results = Query::select([ 'uri', 'page_id', 'SUM(count) AS total', ]) ->from('pages') ->where('id', '=', $args['id']) ->groupBy('uri') ->orderBy('total') ->getAll(); return $results; } public function getResourcesViews($args = []) { $args = $this->parseArgs($args, [ 'fields' => ['id', 'uri', 'type', 'SUM(count) as views'], 'resource_id' => '', 'resource_type' => '', 'date' => '', 'group_by' => 'id', 'not_null' => '', 'order_by' => 'views', 'page' => 1, 'per_page' => 10 ]); // If resource_id and resource_type are empty, get all views including 404, categories, home, etc... if (empty($args['resource_id']) && empty($args['resource_type'])) { $queries = []; $queries[] = Query::select($args['fields']) ->from('pages') ->where('id', '!=', '0') ->whereDate('date', $args['date']) ->groupBy('id') ->getQuery(); $queries[] = Query::select($args['fields']) ->from('pages') ->where('id', '=', '0') ->whereDate('date', $args['date']) ->groupBy(['uri', 'type']) ->getQuery(); $results = Query::union($queries) ->perPage($args['page'], $args['per_page']) ->orderBy('views', 'DESC') ->getAll(); } else { $results = Query::select($args['fields']) ->from('pages') ->where('id', '=', $args['resource_id']) ->where('type', 'IN', $args['resource_type']) ->whereDate('date', $args['date']) ->whereNotNull($args['not_null']) ->orderBy($args['order_by']) ->perPage($args['page'], $args['per_page']) ->groupBy($args['group_by']) ->getAll(); } return $results; } public function getPageRecord($args = []) { $args = $this->parseArgs($args, [ 'fields' => '', 'page_id' => '', 'ignore_date' => true ]); $result = Query::select('') ->from('pages') ->where('page_id', '=', $args['page_id']) ->allowCaching() ->getRow(); return $result; } public function countPagesRecords($args = []) { $args = $this->parseArgs($args, [ 'resource_id' => '', 'resource_type' => '', 'date' => '', 'not_null' => '', ]); $result = Query::select('COUNT()') ->from('pages') ->where('id', '=', $args['resource_id']) ->where('type', 'IN', $args['resource_type']) ->whereDate('date', $args['date']) ->whereNotNull($args['not_null']) ->getVar(); return $result; } } PK��z[�>lH �H ��VisitorsModel.phpnu�ȯ��<?php namespace WP_Statistics\Models; use WP_Statistics\Abstracts\BaseModel; use WP_STATISTICS\Admin_Template; use WP_Statistics\Components\DateRange; use WP_Statistics\Decorators\ReferralDecorator; use WP_Statistics\Decorators\VisitorDecorator; use WP_STATISTICS\Helper; use WP_Statistics\Service\Geolocation\GeolocationFactory; use WP_Statistics\Utils\Query; class VisitorsModel extends BaseModel { public function countVisitors($args = []) { $args = $this->parseArgs($args, [ 'date' => '', 'post_type' => '', 'resource_type' => '', 'resource_id' => '', 'author_id' => '', 'post_id' => '', 'query_param' => '', 'taxonomy' => '', 'term' => '', 'agent' => '', 'platform' => '', 'country' => '', 'user_id' => '', 'ip' => '', 'source_name' => '', 'logged_in' => false, 'user_role' => '', 'referrer' => '', 'not_null' => '', 'date_field' => 'last_counter', ]); $filteredArgs = array_filter($args); $field = ''; if (array_intersect(['resource_type', 'resource_id', 'query_param', 'post_type', 'author_id', 'post_id', 'taxonomy', 'term'], array_keys($filteredArgs))) { $field = 'DISTINCT visitor.ID'; } $query = Query::select("COUNT($field) as total_visitors") ->from('visitor') ->where('agent', '=', $args['agent']) ->where('location', '=', $args['country']) ->where('platform', '=', $args['platform']) ->where('user_id', '=', $args['user_id']) ->where('referred', '=', $args['referrer']) ->where('ip', '=', $args['ip']) ->where('source_name', 'IN', $args['source_name']) ->whereNotNull($args['not_null']) ->whereDate($args['date_field'], $args['date']); if ($args['logged_in'] === true) { $query->where('visitor.user_id', '!=', 0); $query->whereNotNull('visitor.user_id'); if (!empty($args['user_role'])) { $query->join('usermeta', ['visitor.user_id', 'usermeta.user_id']); $query->where('usermeta.meta_key', '=', "wp_capabilities"); $query->where('usermeta.meta_value', 'LIKE', "%{$args['user_role']}%"); } } if (array_intersect(['resource_type', 'resource_id', 'query_param'], array_keys($filteredArgs))) { $query ->join('visitor_relationships', ['visitor_relationships.visitor_id', 'visitor.ID']) ->join('pages', ['visitor_relationships.page_id', 'pages.page_id'], [], 'LEFT') ->where('pages.type', 'IN', $args['resource_type']) ->where('pages.id', '=', $args['resource_id']) ->where('pages.uri', '=', $args['query_param']); } if (array_intersect(['post_type', 'author_id', 'post_id', 'taxonomy', 'term'], array_keys($filteredArgs))) { $query ->join('visitor_relationships', ['visitor_relationships.visitor_id', 'visitor.ID']) ->join('pages', ['visitor_relationships.page_id', 'pages.page_id'], [], 'LEFT') ->join('posts', ['posts.ID', 'pages.id'], [], 'LEFT') ->where('post_type', 'IN', $args['post_type']) ->where('post_author', '=', $args['author_id']) ->where('posts.ID', '=', $args['post_id']); if (!empty($args['taxonomy']) \|\| !empty($args['term'])) { $taxQuery = Query::select(['DISTINCT object_id']) ->from('term_relationships') ->join('term_taxonomy', ['term_relationships.term_taxonomy_id', 'term_taxonomy.term_taxonomy_id']) ->join('terms', ['term_taxonomy.term_id', 'terms.term_id']) ->where('term_taxonomy.taxonomy', 'IN', $args['taxonomy']) ->where('terms.term_id', '=', $args['term']) ->getQuery(); $query ->joinQuery($taxQuery, ['posts.ID', 'tax.object_id'], 'tax'); } } $result = $query->getVar(); $total = $result ? intval($result) : 0; $total += $this->historicalModel->getVisitors($args); return $total; } public function countHits($args = []) { $args = $this->parseArgs($args, [ 'date' => '', 'agent' => '', 'platform' => '', 'country' => '', 'user_id' => '', 'ip' => '', 'logged_in' => false, 'user_role' => '' ]); $query = Query::select('SUM(visitor.hits) as total_visitors') ->from('visitor') ->where('agent', '=', $args['agent']) ->where('location', '=', $args['country']) ->where('platform', '=', $args['platform']) ->where('user_id', '=', $args['user_id']) ->where('ip', '=', $args['ip']) ->whereDate('last_counter', $args['date']); if ($args['logged_in'] === true) { $query->where('visitor.user_id', '!=', 0); $query->whereNotNull('visitor.user_id'); if (!empty($args['user_role'])) { $query->join('usermeta', ['visitor.user_id', 'usermeta.user_id']); $query->where('usermeta.meta_key', '=', "wp_capabilities"); $query->where('usermeta.meta_value', 'LIKE', "%{$args['user_role']}%"); } } $result = $query->getVar(); $total = $result ? intval($result) : 0; $total += $this->historicalModel->getViews($args); return $total; } public function getVisitorsHits($args = []) { $args = $this->parseArgs($args, [ 'date' => '', 'agent' => '', 'platform' => '', 'country' => '', 'user_id' => '', 'ip' => '', 'source_name' => '', 'referrer' => '' ]); $query = Query::select(['COUNT(visitor.ID) as visitors', 'SUM(visitor.hits) as hits']) ->from('visitor') ->where('agent', '=', $args['agent']) ->where('location', '=', $args['country']) ->where('platform', '=', $args['platform']) ->where('user_id', '=', $args['user_id']) ->where('referred', '=', $args['referrer']) ->where('ip', '=', $args['ip']) ->where('source_name', 'IN', $args['source_name']) ->whereDate('last_counter', $args['date']); $result = $query->getRow(); return [ 'visitors' => $result->visitors + $this->historicalModel->getVisitors($args), 'hits' => $result->hits + $this->historicalModel->getViews($args) ]; } public function countDailyVisitors($args = []) { $args = $this->parseArgs($args, [ 'date' => '', 'post_type' => '', 'resource_id' => '', 'resource_type' => '', 'author_id' => '', 'post_id' => '', 'query_param' => '', 'taxonomy' => '', 'term' => '', 'country' => '', 'user_id' => '', 'logged_in' => false, 'include_hits' => false, 'user_role' => '', 'source_channel' => '', 'not_null' => '', 'referred_visitors' => false ]); $filteredArgs = array_filter($args); $fields = [ 'date' => 'visitor.last_counter as date', 'visitors' => 'COUNT(visitor.ID) as visitors' ]; if (!empty($args['include_hits'])) { $fields['hits'] = 'SUM(visitor.hits) as hits'; } // If joined to other tables, add DISTINCT to count unique visitors if (array_intersect(['resource_type', 'resource_id', 'query_param', 'post_type', 'author_id', 'post_id', 'taxonomy', 'term'], array_keys($filteredArgs))) { $fields['visitors'] = 'COUNT(DISTINCT visitor.ID) as visitors'; } $query = Query::select($fields) ->from('visitor') ->where('location', '=', $args['country']) ->where('user_id', '=', $args['user_id']) ->where('source_channel', 'IN', $args['source_channel']) ->whereNotNull($args['not_null']) ->whereDate('visitor.last_counter', $args['date']) ->groupBy('visitor.last_counter'); if (!empty($args['referred_visitors'])) { $query->whereRaw(" AND ( (visitor.referred != '') OR (visitor.source_channel IS NOT NULL AND visitor.source_channel != 'direct') ) "); } if ($args['logged_in'] === true) { $query->where('visitor.user_id', '!=', 0); $query->whereNotNull('visitor.user_id'); if (!empty($args['user_role'])) { $query->join('usermeta', ['visitor.user_id', 'usermeta.user_id']); $query->where('usermeta.meta_key', '=', "wp_capabilities"); $query->where('usermeta.meta_value', 'LIKE', "%{$args['user_role']}%"); } } if (array_intersect(['resource_type', 'resource_id', 'query_param'], array_keys($filteredArgs))) { $query ->join('visitor_relationships', ['visitor_relationships.visitor_id', 'visitor.ID']) ->join('pages', ['visitor_relationships.page_id', 'pages.page_id'], [], 'LEFT') ->where('pages.type', 'IN', $args['resource_type']) ->where('pages.id', '=', $args['resource_id']) ->where('pages.uri', '=', $args['query_param']); } if (array_intersect(['post_type', 'author_id', 'post_id', 'taxonomy', 'term'], array_keys($filteredArgs))) { $query ->join('visitor_relationships', ['visitor_relationships.visitor_id', 'visitor.ID']) ->join('pages', ['visitor_relationships.page_id', 'pages.page_id'], [], 'LEFT') ->join('posts', ['posts.ID', 'pages.id'], [], 'LEFT') ->where('post_type', 'IN', $args['post_type']) ->where('post_author', '=', $args['author_id']) ->where('posts.ID', '=', $args['post_id']); if (!empty($args['taxonomy']) \|\| !empty($args['term'])) { $taxQuery = Query::select(['DISTINCT object_id']) ->from('term_relationships') ->join('term_taxonomy', ['term_relationships.term_taxonomy_id', 'term_taxonomy.term_taxonomy_id']) ->join('terms', ['term_taxonomy.term_id', 'terms.term_id']) ->where('term_taxonomy.taxonomy', 'IN', $args['taxonomy']) ->where('terms.term_id', '=', $args['term']) ->getQuery(); $query ->joinQuery($taxQuery, ['posts.ID', 'tax.object_id'], 'tax'); } } $result = $query->getAll(); return $result; } public function countDailyReferrers($args = []) { $args = $this->parseArgs($args, [ 'date' => '', 'resource_id' => '', 'resource_type' => '', 'source_channel' => '', 'source_name' => '', 'referrer' => '', 'fields' => [], 'group_by' => [] ]); $result = Query::select(array_merge(['COUNT() as referrers, last_counter as date'], $args['fields'])) ->from('visitor') ->where('source_channel', '=', $args['source_channel']) ->where('source_name', '=', $args['source_name']) ->where('referred', '=', $args['referrer']) ->whereDate('visitor.last_counter', $args['date']) ->whereRaw(" AND ( (visitor.referred != '') OR (visitor.source_channel IS NOT NULL AND visitor.source_channel != 'direct') ) ") ->groupBy(array_merge(['last_counter'], $args['group_by'])); if (!empty($args['resource_id']) \|\| !empty($args['resource_type'])) { $result ->join('pages', ['visitor.first_page', 'pages.page_id']) ->where('pages.id', '=', $args['resource_id']) ->where('pages.type', 'IN', $args['resource_type']); } return $result->getAll() ?? []; } /** * Returns `COUNT DISTINCT` of a column from visitors table. * * @param array $args Arguments to include in query (e.g. `field`, `date`, `where_col`, `where_val`, etc.). * * @return int / public function countColumnDistinct($args = []) { $args = $this->parseArgs($args, [ 'field' => 'ID', 'date' => '', 'where_col' => 'ID', 'where_val' => '', 'where_not_null' => '', ]); $result = Query::select("COUNT(DISTINCT `{$args['field']}`) as `total`") ->from('visitor') ->where($args['where_col'], '=', $args['where_val']) ->whereNotNull($args['where_not_null']) ->whereDate('last_counter', $args['date']) ->perPage(1, 1) ->getVar(); return $result ? intval($result) : 0; } /* * Returns visitors' device information. * * @param array $args Arguments to include in query (e.g. `field`, `date`, `group_by`, etc.). * * @return array / public function getVisitorsDevices($args = []) { $args = $this->parseArgs($args, [ 'field' => 'agent', 'date' => '', 'where_not_null' => '', 'group_by' => [], 'order_by' => 'visitors', 'order' => 'DESC', 'per_page' => '', 'page' => 1, ]); $result = Query::select([ $args['field'], 'COUNT(visitor.ID) AS `visitors`', ]) ->from('visitor') ->whereDate('last_counter', $args['date']) ->whereNotNull($args['where_not_null']) ->groupBy($args['group_by']) ->orderBy($args['order_by'], $args['order']) ->perPage($args['page'], $args['per_page']) ->getAll(); return $result ? $result : []; } /* * Returns visitors' device versions for single view pages. * * @param array $args Arguments to include in query (e.g. `date`, etc.). * * @return array / public function getVisitorsDevicesVersions($args = []) { $args = $this->parseArgs($args, [ 'date' => '', 'where_col' => 'agent', 'where_val' => '', 'order_by' => 'visitors', 'order' => 'DESC', 'per_page' => '', 'page' => 1, ]); $result = Query::select([ 'CAST(`version` AS SIGNED) AS `casted_version`', 'COUNT(visitor.ID) AS `visitors`', ]) ->from('visitor') ->where($args['where_col'], '=', $args['where_val']) ->whereDate('last_counter', $args['date']) ->groupBy('casted_version') ->orderBy($args['order_by'], $args['order']) ->perPage($args['page'], $args['per_page']) ->getAll(); return $result ? $result : []; } public function getVisitorsSummary($args = []) { $periods = [ 'today' => ['label' => esc_html__('Today', 'wp-statistics'), 'date' => 'today'], 'yesterday' => ['label' => esc_html__('Yesterday', 'wp-statistics'), 'date' => 'yesterday'], 'this_week' => ['label' => esc_html__('This week', 'wp-statistics'), 'date' => 'this_week'], 'last_week' => ['label' => esc_html__('Last week', 'wp-statistics'), 'date' => 'last_week'], 'this_month' => ['label' => esc_html__('This month', 'wp-statistics'), 'date' => 'this_month'], 'last_month' => ['label' => esc_html__('Last month', 'wp-statistics'), 'date' => 'last_month'], '7days' => ['label' => esc_html__('Last 7 days', 'wp-statistics'), 'date' => '7days'], '30days' => ['label' => esc_html__('Last 30 days', 'wp-statistics'), 'date' => '30days'], '90days' => ['label' => esc_html__('Last 90 days', 'wp-statistics'), 'date' => '90days'], '6months' => ['label' => esc_html__('Last 6 months', 'wp-statistics'), 'date' => '6months'], 'this_year' => ['label' => esc_html__('This year (Jan-Today)', 'wp-statistics'), 'date' => 'this_year'], ]; $exclude = $args['exclude'] ?? []; $summary = []; foreach ($periods as $key => $period) { if (in_array($key, $exclude, true)) { continue; // Skip excluded periods } $summary[$key] = [ 'label' => $period['label'], 'visitors' => $this->countVisitors(array_merge($args, ['date' => DateRange::get($period['date'])])), ]; } // Conditionally add 'total' (if not excluded) if (!empty($args['include_total']) && !in_array('total', $exclude, true)) { $summary['total'] = [ 'label' => esc_html__('Total', 'wp-statistics'), 'visitors' => $this->countVisitors(array_merge($args, ['ignore_date' => true, 'historical' => true])), ]; } return $summary; } public function getHitsSummary($args = []) { $periods = [ 'today' => ['label' => esc_html__('Today', 'wp-statistics'), 'date' => 'today'], 'yesterday' => ['label' => esc_html__('Yesterday', 'wp-statistics'), 'date' => 'yesterday'], 'this_week' => ['label' => esc_html__('This week', 'wp-statistics'), 'date' => 'this_week'], 'last_week' => ['label' => esc_html__('Last week', 'wp-statistics'), 'date' => 'last_week'], 'this_month' => ['label' => esc_html__('This month', 'wp-statistics'), 'date' => 'this_month'], 'last_month' => ['label' => esc_html__('Last month', 'wp-statistics'), 'date' => 'last_month'], '7days' => ['label' => esc_html__('Last 7 days', 'wp-statistics'), 'date' => '7days'], '30days' => ['label' => esc_html__('Last 30 days', 'wp-statistics'), 'date' => '30days'], '90days' => ['label' => esc_html__('Last 90 days', 'wp-statistics'), 'date' => '90days'], '6months' => ['label' => esc_html__('Last 6 months', 'wp-statistics'), 'date' => '6months'], 'this_year' => ['label' => esc_html__('This year (Jan-Today)', 'wp-statistics'), 'date' => 'this_year'], ]; $exclude = $args['exclude'] ?? []; $summary = []; foreach ($periods as $key => $period) { if (in_array($key, $exclude, true)) { continue; // Skip excluded periods } $summary[$key] = [ 'label' => $period['label'], 'hits' => $this->countHits(array_merge($args, ['date' => DateRange::get($period['date'])])), ]; } // Conditionally add 'total' (if not excluded) if (!empty($args['include_total']) && !in_array('total', $exclude, true)) { $summary['total'] = [ 'label' => esc_html__('Total', 'wp-statistics'), 'hits' => $this->countHits(array_merge($args, ['ignore_date' => true, 'historical' => true])), ]; } return $summary; } public function getVisitorsHitsSummary($args = []) { $periods = [ 'today' => ['label' => esc_html__('Today', 'wp-statistics'), 'date' => 'today'], 'yesterday' => ['label' => esc_html__('Yesterday', 'wp-statistics'), 'date' => 'yesterday'], 'this_week' => ['label' => esc_html__('This week', 'wp-statistics'), 'date' => 'this_week'], 'last_week' => ['label' => esc_html__('Last week', 'wp-statistics'), 'date' => 'last_week'], 'this_month' => ['label' => esc_html__('This month', 'wp-statistics'), 'date' => 'this_month'], 'last_month' => ['label' => esc_html__('Last month', 'wp-statistics'), 'date' => 'last_month'], '7days' => ['label' => esc_html__('Last 7 days', 'wp-statistics'), 'date' => '7days'], '30days' => ['label' => esc_html__('Last 30 days', 'wp-statistics'), 'date' => '30days'], '90days' => ['label' => esc_html__('Last 90 days', 'wp-statistics'), 'date' => '90days'], '6months' => ['label' => esc_html__('Last 6 months', 'wp-statistics'), 'date' => '6months'], 'this_year' => ['label' => esc_html__('This year (Jan-Today)', 'wp-statistics'), 'date' => 'this_year'], ]; $exclude = $args['exclude'] ?? []; $summary = []; foreach ($periods as $key => $period) { if (in_array($key, $exclude)) { continue; // Skip excluded periods } $data = $this->getVisitorsHits(array_merge($args, ['date' => DateRange::get($period['date'])])); $summary[$key] = [ 'label' => $period['label'], 'visitors' => $data['visitors'], 'hits' => $data['hits'], ]; } // Conditionally add 'total' (if not excluded) if (!empty($args['include_total']) && !in_array('total', $exclude)) { $data = $this->getVisitorsHits(array_merge($args, ['ignore_date' => true, 'historical' => true])); $summary['total'] = [ 'label' => esc_html__('Total', 'wp-statistics'), 'visitors' => $data['visitors'], 'hits' => $data['hits'], ]; } return $summary; } /* * Get a summary of referred visitors from search engines. * * @param array $args * * @return array / public function getSearchEnginesSummary($args = []) { $periods = [ 'today' => ['label' => esc_html__('Today', 'wp-statistics'), 'date' => 'today'], 'yesterday' => ['label' => esc_html__('Yesterday', 'wp-statistics'), 'date' => 'yesterday'], 'this_week' => ['label' => esc_html__('This week', 'wp-statistics'), 'date' => 'this_week'], 'last_week' => ['label' => esc_html__('Last week', 'wp-statistics'), 'date' => 'last_week'], 'this_month' => ['label' => esc_html__('This month', 'wp-statistics'), 'date' => 'this_month'], 'last_month' => ['label' => esc_html__('Last month', 'wp-statistics'), 'date' => 'last_month'], '7days' => ['label' => esc_html__('Last 7 days', 'wp-statistics'), 'date' => '7days'], '30days' => ['label' => esc_html__('Last 30 days', 'wp-statistics'), 'date' => '30days'], '90days' => ['label' => esc_html__('Last 90 days', 'wp-statistics'), 'date' => '90days'], '6months' => ['label' => esc_html__('Last 6 months', 'wp-statistics'), 'date' => '6months'], 'this_year' => ['label' => esc_html__('This year (Jan-Today)', 'wp-statistics'), 'date' => 'this_year'], ]; $exclude = $args['exclude'] ?? []; $summary = []; foreach ($periods as $key => $period) { if (in_array($key, $exclude, true)) { continue; // Skip excluded periods } $summary[$key] = [ 'label' => $period['label'], 'search_engines' => $this->countReferredVisitors(array_merge($args, ['date' => DateRange::get($period['date']), 'source_channel' => ['search', 'paid_search']])), ]; } // Conditionally add 'total' (if not excluded) if (!empty($args['include_total']) && !in_array('total', $exclude, true)) { $summary['total'] = [ 'label' => esc_html__('Total', 'wp-statistics'), 'search_engines' => $this->countReferredVisitors(array_merge($args, ['ignore_date' => true, 'historical' => true, 'source_channel' => ['search', 'paid_search']])), ]; } return $summary; } public function getVisitorsData($args = []) { $args = $this->parseArgs($args, [ 'date' => '', 'resource_type' => '', 'resource_id' => '', 'post_type' => '', 'author_id' => '', 'post_id' => '', 'country' => '', 'agent' => '', 'platform' => '', 'user_id' => '', 'ip' => '', 'query_param' => '', 'taxonomy' => '', 'term' => '', 'order_by' => 'visitor.ID', 'order' => 'DESC', 'page' => '', 'per_page' => '', 'date_field' => 'visitor.last_counter', 'logged_in' => false, 'user_role' => '', 'event_target' => '', 'event_name' => '', 'fields' => [], 'referrer' => '', 'not_null' => '', 'source_channel' => '', 'referred_visitors' => '', 'utm_source' => '', 'utm_medium' => '', 'utm_campaign' => '', 'source_name' => '', 'group_by' => 'visitor.ID', 'decorate' => true, 'exclude_ids' => [] ]); // Set default fields if (empty($args['fields'])) { $args['fields'] = [ 'visitor.ID', 'visitor.ip', 'visitor.platform', 'visitor.agent', 'version', 'visitor.model', 'visitor.device', 'visitor.location', 'visitor.user_id', 'visitor.region', 'visitor.city', 'visitor.hits', 'visitor.referred', 'visitor.last_counter', 'visitor.source_channel', 'visitor.source_name', 'visitor.first_page', 'visitor.first_view', 'visitor.last_page', 'visitor.last_view', ]; // When retrieving data for a single resource, get the page view date if (!empty($args['resource_id']) && ($args['resource_type'])) { $args['fields'][] = 'visitor_relationships.date as page_view'; } } $query = Query::select($args['fields']) ->from('visitor') ->where('agent', '=', $args['agent']) ->where('platform', '=', $args['platform']) ->where('user_id', '=', $args['user_id']) ->where('ip', 'LIKE', "%{$args['ip']}%") ->where('referred', '=', $args['referrer']) ->where('visitor.location', '=', $args['country']) ->where('visitor.source_channel', 'IN', $args['source_channel']) ->where('visitor.source_name', 'IN', $args['source_name']) ->where('visitor.ID', 'NOT IN', $args['exclude_ids']) ->whereNotNull($args['not_null']) ->whereDate($args['date_field'], $args['date']) ->perPage($args['page'], $args['per_page']) ->orderBy($args['order_by'], $args['order']) ->groupBy($args['group_by']); if ($args['decorate'] == true) { $query->decorate(VisitorDecorator::class); } // When source_channel is `unassigned`, only get visitors without source_channel if ($args['source_channel'] === 'unassigned') { $query ->whereNull('visitor.source_channel'); } else { $query ->where('source_channel', '=', $args['source_channel']); } if (!empty($args['referred_visitors'])) { $query->whereRaw(" AND ( (visitor.referred != '') OR (visitor.source_channel IS NOT NULL AND visitor.source_channel != 'direct') ) "); } if ($args['logged_in'] === true) { $query->where('visitor.user_id', '!=', 0); $query->whereNotNull('visitor.user_id'); if (!empty($args['user_role'])) { $query->join('usermeta', ['visitor.user_id', 'usermeta.user_id']); $query->where('usermeta.meta_key', '=', "wp_capabilities"); $query->where('usermeta.meta_value', 'LIKE', "%{$args['user_role']}%"); } } $filteredArgs = array_filter($args); if (array_intersect(['resource_type', 'resource_id', 'query_param', 'utm_source', 'utm_medium', 'utm_campaign'], array_keys($filteredArgs))) { $query ->join('visitor_relationships', ['visitor_relationships.visitor_id', 'visitor.ID']) ->join('pages', ['visitor_relationships.page_id', 'pages.page_id'], [], 'LEFT') ->where('pages.type', 'IN', $args['resource_type']) ->where('pages.id', '=', $args['resource_id']) ->where('pages.uri', '=', $args['query_param']) ->where('pages.uri', 'LIKE', $args['utm_source'] ? "%utm_source={$args['utm_source']}%" : '') ->where('pages.uri', 'LIKE', $args['utm_medium'] ? "%utm_medium={$args['utm_medium']}%" : '') ->where('pages.uri', 'LIKE', $args['utm_campaign'] ? "%utm_campaign={$args['utm_campaign']}%" : ''); } if (array_intersect(['post_type', 'post_id', 'author_id', 'query_param', 'taxonomy', 'term'], array_keys($filteredArgs))) { $query ->join('visitor_relationships', ['visitor_relationships.visitor_id', 'visitor.ID']) ->join('pages', ['visitor_relationships.page_id', 'pages.page_id'], [], 'LEFT') ->join('posts', ['posts.ID', 'pages.id'], [], 'LEFT') ->where('post_type', 'IN', $args['post_type']) ->where('post_author', '=', $args['author_id']) ->where('posts.ID', '=', $args['post_id']); if (array_intersect(['taxonomy', 'term'], array_keys($filteredArgs))) { $taxQuery = Query::select(['DISTINCT object_id']) ->from('term_relationships') ->join('term_taxonomy', ['term_relationships.term_taxonomy_id', 'term_taxonomy.term_taxonomy_id']) ->join('terms', ['term_taxonomy.term_id', 'terms.term_id']) ->where('term_taxonomy.taxonomy', 'IN', $args['taxonomy']) ->where('terms.term_id', '=', $args['term']) ->getQuery(); $query ->joinQuery($taxQuery, ['posts.ID', 'tax.object_id'], 'tax'); } } if (!empty($args['event_target']) \|\| !empty($args['event_name'])) { $query ->join('events', ['events.visitor_id', 'visitor.ID']) ->where('event_name', 'IN', $args['event_name']) ->whereJson('event_data', 'target_url', '=', $args['event_target']); } $result = $query->getAll(); return $result ? $result : []; } public function getReferredVisitors($args = []) { $args = $this->parseArgs($args, [ 'fields' => ['visitor.ID', 'visitor.ip', 'visitor.platform', 'visitor.agent', 'version', 'visitor.model', 'visitor.device', 'visitor.location', 'visitor.user_id', 'visitor.region', 'visitor.city', 'visitor.hits', 'visitor.referred', 'visitor.last_counter', 'visitor.source_channel', 'visitor.source_name', 'users.display_name', 'users.user_email', 'visitor.first_page', 'visitor.first_view', 'visitor.last_page', 'visitor.last_view'], 'date' => '', 'source_channel' => '', 'source_name' => '', 'referrer' => '', 'order_by' => 'visitor.ID', 'order' => 'desc', 'page' => '', 'per_page' => '', 'utm_source' => '', 'utm_medium' => '', 'utm_campaign' => '', 'resource_id' => '', 'resource_type' => Helper::getPostTypes(), 'group_by' => '', 'decorate' => true ]); $query = Query::select($args['fields']) ->from('visitor') ->join('users', ['visitor.user_id', 'users.ID'], [], 'LEFT') ->where('source_name', '=', $args['source_name']) ->where('referred', '=', $args['referrer']) ->whereRaw(" AND ( (visitor.referred != '') OR (visitor.source_channel IS NOT NULL AND visitor.source_channel != 'direct') ) ") ->whereDate('visitor.last_counter', $args['date']) ->perPage($args['page'], $args['per_page']) ->orderBy($args['order_by'], $args['order']) ->groupBy($args['group_by']); if ($args['decorate'] == true) { $query->decorate(VisitorDecorator::class); } // When source_channel is `unassigned`, only get visitors without source_channel if ($args['source_channel'] === 'unassigned') { $query ->whereNull('visitor.source_channel'); } else { $query ->where('source_channel', '=', $args['source_channel']); } if (!empty($args['utm_source']) \|\| !empty($args['utm_medium']) \|\| !empty($args['utm_campaign']) \|\| !empty($args['resource_id'])) { $query ->join('pages', ['visitor.first_page', 'pages.page_id']) ->where('pages.id', '=', $args['resource_id']) ->where('pages.type', 'IN', $args['resource_type']) ->where('pages.uri', 'LIKE', $args['utm_source'] ? "%utm_source={$args['utm_source']}%" : '') ->where('pages.uri', 'LIKE', $args['utm_medium'] ? "%utm_medium={$args['utm_medium']}%" : '') ->where('pages.uri', 'LIKE', $args['utm_campaign'] ? "%utm_campaign={$args['utm_campaign']}%" : ''); } $result = $query->getAll(); return $result ?? []; } public function countReferredVisitors($args = []) { $args = $this->parseArgs($args, [ 'date' => '', 'source_channel' => '', 'source_name' => '', 'referrer' => '' ]); $query = Query::select('COUNT()') ->from('visitor') ->where('source_name', '=', $args['source_name']) ->where('referred', '=', $args['referrer']) ->whereDate('visitor.last_counter', $args['date']) ->whereRaw(" AND ( (visitor.referred != '') OR (visitor.source_channel IS NOT NULL AND visitor.source_channel != 'direct') ) "); // When source_channel is `unassigned`, only get visitors without source_channel if ($args['source_channel'] === 'unassigned') { $query ->whereNull('visitor.source_channel'); } else { $query ->where('source_channel', 'IN', $args['source_channel']); } return $query->getVar() ?? 0; } public function searchVisitors($args = []) { $args = $this->parseArgs($args, [ 'user_id' => '', 'ip' => '', 'username' => '', 'email' => '', ]); $result = Query::select([ 'visitor.ID', 'visitor.user_id', 'visitor.ip', 'users.display_name', 'users.user_email', 'users.user_login' ]) ->from('visitor') ->join('users', ['visitor.user_id', 'users.ID'], [], 'LEFT') ->where('user_id', '=', $args['user_id']) ->where('user_email', 'LIKE', "%{$args['email']}%") ->where('user_login', 'LIKE', "%{$args['username']}%") ->whereRaw( "OR (ip LIKE '#hash#%' AND ip LIKE %s)", ["#hash#{$args['ip']}%"] ) ->whereRaw( "OR (ip NOT LIKE '#hash#%' AND ip LIKE %s)", ["{$args['ip']}%"] ) ->whereRelation('OR') ->getAll(); return $result ? $result : []; } public function getVisitorData($args = []) { $args = $this->parseArgs($args, [ 'fields' => [], 'visitor_id' => '', 'ip' => '', // not recommended to get visitor data by ip, it's less efficient 'decorate' => true, 'page_info' => true, 'user_info' => true ]); $fields = !empty($args['fields']) && is_array($args['fields']) ? $args['fields'] : [ 'visitor.ID', 'visitor.platform', 'visitor.agent', 'version', 'visitor.model', 'visitor.device', 'visitor.location', 'visitor.user_id', 'visitor.region', 'visitor.city', 'visitor.hits', 'visitor.last_counter', 'visitor.referred', 'visitor.source_channel', 'visitor.source_name', 'visitor.ip', 'visitor.first_page', 'visitor.first_view', 'visitor.last_page', 'visitor.last_view' ]; // If visitor_id is empty, get visitor_id by IP if (empty($args['visitor_id']) \|\| !empty($args['ip'])) { $visitorId = Query::select(['ID']) ->from('visitor') ->where('ip', '=', $args['ip']) ->getVar(); $args['visitor_id'] = $visitorId ?? ''; } if ($args['page_info']) { $fields[] = 'pages.uri as first_uri'; } if ($args['user_info']) { $fields[] = 'users.display_name'; $fields[] = 'users.user_email'; $fields[] = 'users.user_login'; $fields[] = 'users.user_registered'; } $query = Query::select($fields) ->from('visitor') ->where('visitor.ID', '=', $args['visitor_id']); if ($args['page_info']) { $query ->join('pages', ['first_page', 'pages.page_id'], [], 'LEFT'); } if ($args['user_info']) { $query ->join('users', ['visitor.user_id', 'users.ID'], [], 'LEFT'); } if ($args['decorate']) { $query ->decorate(VisitorDecorator::class); } $result = $query ->getRow(); return $result; } public function getVisitorJourney($args) { $args = $this->parseArgs($args, [ 'visitor_id' => '', 'ignore_date' => true, ]); $result = Query::select([ 'date', 'page_id', ]) ->from('visitor_relationships') ->where('visitor_relationships.visitor_id', '=', $args['visitor_id']) ->orderBy('date') ->getAll(); return $result; } public function countGeoData($args = []) { $args = $this->parseArgs($args, [ 'date' => '', 'count_field' => 'location', 'continent' => '', 'country' => '', 'region' => '', 'city' => '', 'not_null' => '', ]); $result = Query::select([ "COUNT(DISTINCT {$args['count_field']}) as total" ]) ->from('visitor') ->whereDate('visitor.last_counter', $args['date']) ->where('visitor.continent', '=', $args['continent']) ->where('visitor.location', '=', $args['country']) ->where('visitor.region', '=', $args['region']) ->where('visitor.city', '=', $args['city']) ->whereNotNull("visitor.{$args['count_field']}") ->getVar(); return $result ? intval($result) : 0; } public function getVisitorsGeoData($args = []) { $args = $this->parseArgs($args, [ 'fields' => [ 'city' => 'visitor.city as city', 'country' => 'visitor.location as country', 'region' => 'visitor.region as region', 'continent' => 'visitor.continent as continent', 'visitors' => 'COUNT(visitor.ID) as visitors', 'views' => 'SUM(visitor.hits) as views', // All views are counted and results can't be filtered by author, post type, etc... ], 'date' => '', 'country' => '', 'city' => '', 'region' => '', 'continent' => '', 'not_null' => '', 'post_type' => '', 'author_id' => '', 'post_id' => '', 'per_page' => '', 'query_param' => '', 'taxonomy' => '', 'term' => '', 'page' => 1, 'source_channel' => '', 'group_by' => 'visitor.location', 'event_name' => '', 'event_target' => '', 'order_by' => ['visitors', 'views'], 'order' => 'DESC', 'utm_source' => '', 'utm_medium' => '', 'utm_campaign' => '', 'referrer' => '', 'resource_id' => '', 'resource_type' => '', 'referred_visitors' => false ]); $filteredArgs = array_filter($args); // If joined to other tables, add DISTINCT to count unique visitors if (isset($args['fields']['visitors']) && array_intersect(['resource_type', 'resource_id', 'query_param', 'post_type', 'author_id', 'post_id', 'taxonomy', 'term', 'event_name', 'event_target'], array_keys($filteredArgs))) { $args['fields']['visitors'] = 'COUNT(DISTINCT visitor.ID) as visitors'; } $query = Query::select($args['fields']) ->from('visitor') ->where('visitor.location', 'IN', $args['country']) ->where('visitor.city', 'IN', $args['city']) ->where('visitor.region', 'IN', $args['region']) ->where('visitor.continent', 'IN', $args['continent']) ->where('visitor.referred', '=', $args['referrer']) ->whereDate('visitor.last_counter', $args['date']) ->whereNotNull($args['not_null']) ->perPage($args['page'], $args['per_page']) ->groupBy($args['group_by']) ->orderBy($args['order_by'], $args['order']); if (!empty($args['referred_visitors'])) { $query->whereRaw(" AND ( (visitor.referred != '') OR (visitor.source_channel IS NOT NULL AND visitor.source_channel != 'direct') ) "); } if (array_intersect(['resource_id', 'resource_type'], array_keys($filteredArgs))) { $query ->join('visitor_relationships', ['visitor_relationships.visitor_id', 'visitor.ID']) ->join('pages', ['visitor_relationships.page_id', 'pages.page_id'], [], 'LEFT') ->where('pages.id', '=', $args['resource_id']) ->where('pages.type', 'IN', $args['resource_type']); } if (array_intersect(['utm_source', 'utm_medium', 'utm_campaign'], array_keys($filteredArgs))) { $query ->join('pages', ['visitor.first_page', 'pages.page_id'], []) ->where('pages.uri', 'LIKE', $args['utm_source'] ? "%utm_source={$args['utm_source']}%" : '') ->where('pages.uri', 'LIKE', $args['utm_medium'] ? "%utm_medium={$args['utm_medium']}%" : '') ->where('pages.uri', 'LIKE', $args['utm_campaign'] ? "%utm_campaign={$args['utm_campaign']}%" : ''); } if (array_intersect(['post_type', 'post_id', 'query_param', 'author_id', 'taxonomy', 'term'], array_keys($filteredArgs))) { $query ->join('visitor_relationships', ['visitor_relationships.visitor_id', 'visitor.ID']) ->join('pages', ['visitor_relationships.page_id', 'pages.page_id'], [], 'LEFT') ->join('posts', ['posts.ID', 'pages.id'], [], 'LEFT') ->where('post_type', 'IN', $args['post_type']) ->where('post_author', '=', $args['author_id']) ->where('posts.ID', '=', $args['post_id']) ->where('pages.uri', '=', $args['query_param']); if (array_intersect(['taxonomy', 'term'], array_keys($filteredArgs))) { $taxQuery = Query::select(['DISTINCT object_id']) ->from('term_relationships') ->join('term_taxonomy', ['term_relationships.term_taxonomy_id', 'term_taxonomy.term_taxonomy_id']) ->join('terms', ['term_taxonomy.term_id', 'terms.term_id']) ->where('term_taxonomy.taxonomy', 'IN', $args['taxonomy']) ->where('terms.term_id', '=', $args['term']) ->getQuery(); $query ->joinQuery($taxQuery, ['posts.ID', 'tax.object_id'], 'tax'); } } if (!empty($args['event_target']) \|\| !empty($args['event_name'])) { $query ->join('events', ['events.visitor_id', 'visitor.ID']) ->where('event_name', 'IN', $args['event_name']) ->whereJson('event_data', 'target_url', '=', $args['event_target']); } $result = $query->getAll(); return $result ? $result : []; } public function getVisitorsWithIncompleteLocation($returnCount = false) { $privateCountry = GeolocationFactory::getProviderInstance()->getPrivateCountryCode(); // Determine the select fields based on the returnCount parameter $selectFields = $returnCount ? 'COUNT()' : ['ID']; // Build the query $query = Query::select($selectFields) ->from('visitor') ->whereRaw( "(location = '' OR location = %s OR location IS NULL OR continent = '' OR continent IS NULL OR (continent = location)) AND ip NOT LIKE '#hash#%'", [$privateCountry] ); // Execute the query and return the result based on the returnCount parameter if ($returnCount) { return intval($query->getVar()); } else { return $query->getAll(); } } public function getVisitorsWithIncompleteSourceChannel($args = []) { $result = Query::select([ 'visitor.ID' ]) ->from('visitor') ->whereNotNull('referred') ->whereNull('source_channel') ->whereNull('source_name') ->getAll(); return $result ? $result : []; } public function updateVisitor($id, $data) { Query::update('visitor') ->set($data) ->where('ID', '=', $id) ->execute(); } public function getReferrers($args = []) { $args = $this->parseArgs($args, [ 'date' => '', 'post_type' => '', 'source_channel' => '', 'post_id' => '', 'country' => '', 'query_param' => '', 'taxonomy' => '', 'term' => '', 'referrer' => '', 'not_null' => 'visitor.referred', 'group_by' => 'visitor.referred', 'page' => 1, 'per_page' => 10, 'decorate' => false, 'utm_source' => '', 'utm_medium' => '', 'utm_campaign' => '', 'resource_id' => '', 'resource_type' => '' ]); $filteredArgs = array_filter($args); $fields = [ 'visitors' => 'COUNT(visitor.ID) AS visitors', 'referred' => 'visitor.referred', 'source_channel' => 'visitor.source_channel', 'source_name' => 'visitor.source_name', 'last_counter' => 'visitor.last_counter' ]; // If joined to other tables, add DISTINCT to count unique visitors if (array_intersect(['post_type', 'post_id', 'taxonomy', 'term'], array_keys($filteredArgs))) { $fields['visitors'] = 'COUNT(DISTINCT visitor.ID) AS visitors'; } $query = Query::select($fields) ->from('visitor') ->where('visitor.location', '=', $args['country']) ->whereDate('visitor.last_counter', $args['date']) ->whereNotNull($args['not_null']) ->groupBy($args['group_by']) ->orderBy('visitors') ->perPage($args['page'], $args['per_page']); // If not null is not set, get all referrers including those coming with just UTM without any source if (empty($args['not_null'])) { $query->whereRaw("AND ((visitor.referred != '') OR (visitor.source_channel IS NOT NULL))"); } // When source_channel is `unassigned`, only get visitors without source_channel if ($args['source_channel'] === 'unassigned') { $query->whereNull('visitor.source_channel'); } else { $query->where('source_channel', 'IN', $args['source_channel']); } if (!empty($args['referrer'])) { $query->where('visitor.referred', 'LIKE', "%{$args['referrer']}%"); } if (array_intersect(['resource_id', 'resource_type', 'query_param', 'utm_source', 'utm_medium', 'utm_campaign'], array_keys($filteredArgs))) { $query ->join('pages', ['visitor.first_page', 'pages.page_id'], [], 'LEFT') ->where('pages.id', '=', $args['resource_id']) ->where('pages.type', 'IN', $args['resource_type']) ->where('pages.uri', '=', $args['query_param']) ->where('pages.uri', 'LIKE', $args['utm_source'] ? "%utm_source={$args['utm_source']}%" : '') ->where('pages.uri', 'LIKE', $args['utm_medium'] ? "%utm_medium={$args['utm_medium']}%" : '') ->where('pages.uri', 'LIKE', $args['utm_campaign'] ? "%utm_campaign={$args['utm_campaign']}%" : ''); } if (array_intersect(['post_type', 'post_id', 'taxonomy', 'term'], array_keys($filteredArgs))) { $query ->join('pages', ['visitor.first_page', 'pages.page_id'], [], 'LEFT') ->join('posts', ['posts.ID', 'pages.id'], [], 'LEFT') ->where('post_type', 'IN', $args['post_type']) ->where('posts.ID', '=', $args['post_id']); if (array_intersect(['taxonomy', 'term'], array_keys($filteredArgs))) { $taxQuery = Query::select(['DISTINCT object_id']) ->from('term_relationships') ->join('term_taxonomy', ['term_relationships.term_taxonomy_id', 'term_taxonomy.term_taxonomy_id']) ->join('terms', ['term_taxonomy.term_id', 'terms.term_id']) ->where('term_taxonomy.taxonomy', 'IN', $args['taxonomy']) ->where('terms.term_id', '=', $args['term']) ->getQuery(); $query ->joinQuery($taxQuery, ['posts.ID', 'tax.object_id'], 'tax'); } } if ($args['decorate']) { $query->decorate(ReferralDecorator::class); } $result = $query->getAll(); return $result ? $result : []; } public function countReferrers($args = []) { $args = $this->parseArgs($args, [ 'date' => '', 'source_channel' => '', 'post_type' => '', 'post_id' => '', 'country' => '', 'query_param' => '', 'taxonomy' => '', 'term' => '', 'not_null' => 'visitor.referred' ]); $filteredArgs = array_filter($args); $query = Query::select([ 'COUNT(DISTINCT visitor.referred)' ]) ->from('visitor') ->where('source_channel', 'IN', $args['source_channel']) ->where('visitor.location', '=', $args['country']) ->whereDate('visitor.last_counter', $args['date']) ->whereNotNull($args['not_null']); // If not null is not set, get all referrers including those coming with just UTM without any source if (empty($args['not_null'])) { $query->whereRaw("AND ((visitor.referred != '') OR (visitor.source_channel IS NOT NULL))"); } if (array_intersect(['post_type', 'post_id', 'query_param', 'taxonomy', 'term'], array_keys($filteredArgs))) { $query ->join('pages', ['visitor.first_page', 'pages.page_id'], [], 'LEFT') ->join('posts', ['posts.ID', 'pages.id'], [], 'LEFT') ->where('post_type', 'IN', $args['post_type']) ->where('posts.ID', '=', $args['post_id']) ->where('pages.uri', '=', $args['query_param']); if (array_intersect(['taxonomy', 'term'], array_keys($filteredArgs))) { $taxQuery = Query::select(['DISTINCT object_id']) ->from('term_relationships') ->join('term_taxonomy', ['term_relationships.term_taxonomy_id', 'term_taxonomy.term_taxonomy_id']) ->join('terms', ['term_taxonomy.term_id', 'terms.term_id']) ->where('term_taxonomy.taxonomy', 'IN', $args['taxonomy']) ->where('terms.term_id', '=', $args['term']) ->getQuery(); $query ->joinQuery($taxQuery, ['posts.ID', 'tax.object_id'], 'tax'); } } $result = $query->getVar(); return $result ? $result : 0; } /* * Returns visitors, visits and referrers for the past given days, separated daily. * * @param array $args Arguments to include in query (e.g. `date`, `post_type`, `post_id`, etc.). * * @return array Format: `[{'date' => "STRING", 'visitors' => INT, 'visits' => INT, 'referrers' => INT}, ...]`. * * @deprecated Do NOT use this class anymore as it's been deprecated. Instead, use countDailyVisitors, countDailyViews, and countDailyReferrers / public function getDailyStats($args = []) { $args = $this->parseArgs($args, [ 'date' => DateRange::get('30days'), 'post_type' => '', 'post_id' => '', 'resource_type' => '', 'author_id' => '', 'taxonomy' => '', 'term_id' => '', ]); $range = DateRange::get('30days'); $startDate = $range['from'] . ' 00:00:00'; $endDate = date('Y-m-d', strtotime($range['to'] . ' +1 day')) . ' 00:00:00'; $fields = [ '`visitor`.`last_counter` AS `date`', 'COUNT(DISTINCT `visitor`.`ID`) AS `visitors`', 'SUM(`visitor`.`hits`) AS `visits`', 'COUNT(DISTINCT CASE WHEN(`visitor`.`referred` <> "") THEN `visitor`.`ID` END) AS `referrers`', ]; if (is_numeric($args['post_id']) \|\| !empty($args['author_id']) \|\| !empty($args['term_id'])) { // For single pages/posts/authors/terms $fields[2] = 'SUM(DISTINCT `pages`.`count`) AS `visits`'; } $query = Query::select($fields)->from('visitor'); $query->where('visitor.last_counter', '>=', $startDate) ->where('visitor.last_counter', '<', $endDate) ->groupBy('visitor.last_counter'); $filteredArgs = array_filter($args); if (array_intersect(['post_type', 'post_id', 'resource_type', 'author_id', 'taxonomy', 'term_id'], array_keys($filteredArgs))) { $query ->join('visitor_relationships', ['`visitor_relationships`.`visitor_id`', '`visitor`.`ID`']) ->join('pages', '`visitor_relationships`.`page_id` = `pages`.`page_id` AND `visitor`.`last_counter` = `pages`.`date`'); if (!empty($args['resource_type'])) { $query ->where('pages.type', 'IN', $args['resource_type']); if (is_numeric($args['post_id'])) { $query->where('pages.ID', '=', intval($args['post_id'])); } } else { $query->join('posts', ['`posts`.`ID`', '`pages`.`id`']); if (!empty($args['post_type'])) { $query->where('posts.post_type', 'IN', $args['post_type']); } if (is_numeric($args['post_id'])) { $query->where('posts.ID', '=', intval($args['post_id'])); } if (!empty($args['author_id'])) { $query->where('posts.post_author', '=', $args['author_id']); } } if (!empty($args['taxonomy']) && !empty($args['term_id']) && empty($args['resource_type'])) { $taxQuery = Query::select(['DISTINCT object_id']) ->from('term_relationships') ->join('term_taxonomy', ['term_relationships.term_taxonomy_id', 'term_taxonomy.term_taxonomy_id']) ->join('terms', ['term_taxonomy.term_id', 'terms.term_id']) ->where('term_taxonomy.taxonomy', 'IN', $args['taxonomy']) ->where('terms.term_id', '=', $args['term_id']) ->getQuery(); $query ->joinQuery($taxQuery, ['posts.ID', 'tax.object_id'], 'tax'); } } $result = $query->getAll(); return $result ? $result : []; } public function getVisitorHits($args = []) { $args = $this->parseArgs($args, [ 'date' => '', 'user_id' => '', ]); $query = Query::select('SUM(visitor.hits) as hits') ->from('visitor') ->where('user_id', '=', $args['user_id']) ->whereDate('last_counter', $args['date']); $result = $query->getVar(); return intval($result); } public function getBounceRate($args = []) { $args = $this->parseArgs($args, [ 'date' => '', 'resource_id' => '', 'resource_type' => '', 'query_param' => '' ]); $singlePageVisitors = Query::select('visitor_id') ->from('visitor_relationships') ->whereDate('date', $args['date']) ->groupBy('visitor_id') ->having('COUNT(page_id) = 1') ->getQuery(); $query = Query::select(['COUNT(visitor.ID) as visitors']) ->fromQuery($singlePageVisitors, 'single') ->join('visitor', ['visitor.ID', 'single.visitor_id']) ->join('pages', ['visitor.first_page', 'pages.page_id']) ->where('pages.id', '=', $args['resource_id']) ->where('pages.type', 'IN', $args['resource_type']) ->where('pages.uri', '=', $args['query_param']); $singlePageVisits = $query->getVar() ?? 0; $totalPageEntries = $this->countEntryPageVisitors($args); $result = Helper::calculatePercentage($singlePageVisits, $totalPageEntries); return $result; } public function countEntryPageVisitors($args = []) { $args = $this->parseArgs($args, [ 'date' => '', 'resource_id' => '', 'resource_type' => '', 'query_param' => '' ]); $query = Query::select(['COUNT(visitor.ID) as visitors']) ->from('visitor') ->join('pages', ['visitor.first_page', 'pages.page_id']) ->where('pages.id', '=', $args['resource_id']) ->where('pages.type', 'IN', $args['resource_type']) ->where('pages.uri', '=', $args['query_param']) ->whereDate('last_counter', $args['date']); $result = $query->getVar(); return $result ?? 0; } public function countExitPageVisitors($args = []) { $args = $this->parseArgs($args, [ 'date' => '', 'resource_id' => '', 'resource_type' => '', 'query_param' => '' ]); $query = Query::select(['COUNT(visitor.ID) as visitors']) ->from('visitor') ->join('pages', ['visitor.last_page', 'pages.page_id']) ->where('pages.id', '=', $args['resource_id']) ->where('pages.type', 'IN', $args['resource_type']) ->where('pages.uri', '=', $args['query_param']) ->whereDate('last_counter', $args['date']); $result = $query->getVar(); return $result ?? 0; } public function getEntryPages($args = []) { $args = $this->parseArgs($args, [ 'date' => '', 'resource_type' => Helper::getPostTypes(), 'page' => 1, 'per_page' => Admin_Template::$item_per_page, 'author_id' => '', 'uri' => '', 'order_by' => 'visitors', 'order' => 'DESC', 'source_channel' => '', 'not_null' => '', 'referred_visitors' => false ]); $query = Query::select([ 'COUNT(visitor.ID) as visitors', 'pages.id as post_id', 'pages.page_id', 'posts.post_title', 'posts.post_date' ]) ->from('visitor') ->join('pages', ['visitor.first_page', 'pages.page_id']) ->join('posts', ['posts.ID', 'pages.id'], [], 'LEFT') ->where('visitor.source_channel', 'IN', $args['source_channel']) ->where('pages.type', 'IN', $args['resource_type']) ->where('pages.uri', '=', $args['uri']) ->where('posts.post_author', '=', $args['author_id']) ->whereNotNull($args['not_null']) ->whereDate('last_counter', $args['date']) ->groupBy('pages.id') ->orderBy($args['order_by'], $args['order']) ->perPage($args['page'], $args['per_page']); if (!empty($args['referred_visitors'])) { $query->whereRaw(" AND ( (visitor.referred != '') OR (visitor.source_channel IS NOT NULL AND visitor.source_channel != 'direct') ) "); } return $query->getAll(); } public function countEntryPages($args = []) { $args = $this->parseArgs($args, [ 'date' => '', 'resource_type' => Helper::getPostTypes(), 'author_id' => '', 'uri' => '' ]); $query = Query::select('COUNT(DISTINCT pages.id)') ->from('visitor') ->join('pages', ['visitor.first_page', 'pages.page_id']) ->where('pages.type', 'IN', $args['resource_type']) ->where('pages.uri', '=', $args['uri']) ->whereDate('last_counter', $args['date']); if (!empty($args['author_id'])) { $query ->join('posts', ['posts.ID', 'pages.id']) ->where('posts.post_author', '=', $args['author_id']); } $result = $query->getVar(); return intval($result); } public function getExitPages($args = []) { $args = $this->parseArgs($args, [ 'date' => '', 'resource_type' => Helper::getPostTypes(), 'page' => 1, 'per_page' => Admin_Template::$item_per_page, 'author_id' => '', 'uri' => '', 'order_by' => 'exits', 'order' => 'DESC' ]); $subQuery = Query::select("pages.id, COUNT(DISTINCT visitor.ID) as visitors") ->from('visitor') ->join('visitor_relationships', ['visitor.ID', 'visitor_relationships.visitor_id']) ->join('pages', ['visitor_relationships.page_id', 'pages.page_id']) ->where('pages.type', 'IN', $args['resource_type']) ->whereDate('visitor.last_counter', $args['date']) ->groupBy('pages.id') ->getQuery(); $query = Query::select([ 'page_visitors.visitors as visitors', 'COUNT(visitor.ID) as exits', 'pages.id as post_id, pages.page_id', "COALESCE(COUNT(visitor.ID) / page_visitors.visitors, 0) 100 AS exit_rate" ]) ->from('visitor') ->join('pages', ['visitor.last_page', 'pages.page_id']) ->joinQuery($subQuery, ['pages.id', 'page_visitors.id'], 'page_visitors') ->where('pages.type', 'IN', $args['resource_type']) ->where('pages.uri', '=', $args['uri']) ->whereDate('last_counter', $args['date']) ->groupBy('pages.id') ->orderBy($args['order_by'], $args['order']) ->perPage($args['page'], $args['per_page']); if (!empty($args['author_id'])) { $query ->join('posts', ['posts.ID', 'pages.id'], [], 'LEFT') ->where('posts.post_author', '=', $args['author_id']); } $result = $query->getAll(); return $result; } public function countExitPages($args = []) { $args = $this->parseArgs($args, [ 'date' => '', 'resource_type' => Helper::getPostTypes(), 'author_id' => '', 'uri' => '' ]); $query = Query::select('COUNT(DISTINCT pages.id)') ->from('visitor') ->join('pages', ['visitor.last_page', 'pages.page_id']) ->where('pages.type', 'IN', $args['resource_type']) ->where('pages.uri', '=', $args['uri']) ->whereDate('last_counter', $args['date']); if (!empty($args['author_id'])) { $query ->join('posts', ['posts.ID', 'pages.id']) ->where('posts.post_author', '=', $args['author_id']); } $result = $query->getVar(); return intval($result); } } PK��z[1C��4��4��AuthorsModel.phpnu�ȯ��PK��z[�� )�� )��?5��EventsModel.phpnu�ȯ��PK��z[J��^��ExclusionsModel.phpnu�ȯ��PK��z[>�ρ��d��HistoricalModel.phpnu�ȯ��PK��z[�p��p��OnlineModel.phpnu�ȯ��PK��z[�!(F��(F��W��PostsModel.phpnu�ȯ��PK��z[��w�"��"��TaxonomyModel.phpnu�ȯ��PK��z[6�ˇ"8��"8�� ViewsModel.phpnu�ȯ��PK��z[�>lH �H ��$�VisitorsModel.phpnu�ȯ��PK�� 2��

| ver. 1.4 | Github | . | PHP 8.1.33 | Генерация страницы: 0.01 | proxy | phpinfo | Настройка