websdev.gconex.com/wp-content/plugins/seo-by-rank-math-pro/includes/modules/analytics/class-posts.php

<?php
/**
 * The Analytics Module
 *
 * @since      2.0.0
 * @package    RankMath
 * @subpackage RankMath\modules
 * @author     Rank Math <support@rankmath.com>
 */

namespace RankMathPro\Analytics;

use stdClass;
use WP_Error;
use WP_REST_Request;
use RankMath\Traits\Cache;
use RankMath\Traits\Hooker;
use RankMath\Analytics\Stats;

defined( 'ABSPATH' ) || exit;

/**
 * Posts class.
 */
class Posts {

	use Hooker, Cache;

	/**
	 * Main instance
	 *
	 * Ensure only one instance is loaded or can be loaded.
	 *
	 * @return Posts
	 */
	public static function get() {
		static $instance;

		if ( is_null( $instance ) && ! ( $instance instanceof Posts ) ) {
			$instance = new Posts();
			$instance->setup();
		}

		return $instance;
	}

	/**
	 * Constructor.
	 */
	public function setup() {
		$this->filter( 'rank_math/analytics/single/report', 'add_badges', 10, 1 );
		$this->filter( 'rank_math/analytics/single/report', 'add_backlinks', 10, 1 );
		$this->filter( 'rank_math/analytics/single/report', 'add_ranking_keywords', 10, 1 );
		$this->filter( 'rank_math/analytics/single/report', 'get_graph_data_for_post', 10, 1 );
		$this->filter( 'rank_math/analytics/post_data', 'sort_new_data', 10, 2 );
		$this->filter( 'rank_math/analytics/get_objects_by_score_args', 'get_objects_by_score_args', 10, 2 );
		$this->filter( 'rank_math/analytics/get_posts_rows_by_objects', 'get_posts_rows_by_objects', 10, 2 );
	}
	/**
	 * Get posts by objects.
	 *
	 * @param  boolean         $result Check.
	 * @param  WP_REST_Request $request Full details about the request.
	 * @return $args for order and orderby.
	 */
	public function get_objects_by_score_args( $result, WP_REST_Request $request ) {

		$orderby              = $request->get_param( 'orderby' );
		$is_valid_order_param = in_array( $orderby, [ 'title', 'seo_score' ], true );
		$orderby              = $is_valid_order_param ? $orderby : 'created';
		$order                = strtoupper( $request->get_param( 'order' ) );

		$args['orderBy'] = $orderby;
		$args['order']   = $order;

		return $args;
	}
	/**
	 * Change user perference.
	 *
	 * @param  array           $data array.
	 * @param  WP_REST_Request $request post object.
	 * @return array $data sorted array.
	 */
	public function sort_new_data( $data, WP_REST_Request $request ) {
		$id      = $request->get_param( 'id' );
		$orderby = $request->get_param( 'orderby' );
		$order   = strtoupper( $request->get_param( 'order' ) );

		if ( 'query' !== $orderby ) {

			$data['rankingKeywords'] = $this->ranking_keyword_array_sort( $data['rankingKeywords'], $order, $orderby );

		}

		if ( 'query' === $orderby ) {

			if ( 'DESC' === $order ) {
				uasort(
					$data['rankingKeywords'],
					function( $a, $b ) use ( $orderby ) {
						return strtolower( $a[ $orderby ] ) < strtolower( $b[ $orderby ] );
					}
				);
			}

			if ( 'ASC' === $order ) {
				uasort(
					$data['rankingKeywords'],
					function( $a, $b ) use ( $orderby ) {
						return strtolower( $a[ $orderby ] ) > strtolower( $b[ $orderby ] );
					}
				);
			}
		}

		return $data;
	}

	/**
	 * Sort array for ranking keyword by order and orderby
	 *
	 * @param  array    $arr array.
	 *
	 * @param  Variable $arr_order is order direction.
	 *
	 * @param  Variable $arr_orderby is key for sort.
	 */
	public function ranking_keyword_array_sort( $arr, $arr_order, $arr_orderby ) {

		if ( 'DESC' === $arr_order ) {
			uasort(
				$arr,
				function( $a, $b ) use ( $arr_orderby ) {
					return $a[ $arr_orderby ]['total'] < $b[ $arr_orderby ]['total'];
				}
			);
		}

		if ( 'ASC' === $arr_order ) {
			uasort(
				$arr,
				function( $a, $b ) use ( $arr_orderby ) {
					return $a[ $arr_orderby ]['total'] > $b[ $arr_orderby ]['total'];
				}
			);
		}

		return $arr;
	}
	/**
	 * Get posts by objects.
	 *
	 * @param  boolean         $result Check.
	 * @param  WP_REST_Request $request Full details about the request.
	 * @return array Posts rows.
	 */
	public function get_posts_rows_by_objects( $result, WP_REST_Request $request ) {
		$orderby   = $request->get_param( 'orderby' );
		$order     = strtoupper( $request->get_param( 'order' ) );
		$objects   = Stats::get()->get_objects_by_score( $request );
		$objects   = Links::get_links_by_objects( $objects );
		$pages     = isset( $objects['rows'] ) ? \array_keys( $objects['rows'] ) : [];
		$pageviews = Pageviews::get_pageviews( [ 'pages' => $pages ] );
		$pageviews = Stats::get()->set_page_as_key( $pageviews['rows'] );
		$console   = Stats::get()->get_analytics_data(
			[
				'orderBy'   => 'diffImpressions',
				'pageview'  => true,
				'offset'    => 0, // Here offset should always zero.
				'perpage'   => ! empty( $objects['rowsFound'] ) ? $objects['rowsFound'] : 0,
				'sub_where' => " AND page IN ('" . join( "', '", $pages ) . "')",
			]
		);

		$new_rows = [];
		if ( ! empty( $objects['rows'] ) ) {
			foreach ( $objects['rows'] as $object ) {
				$page = $object['page'];

				if ( isset( $pageviews[ $page ] ) ) {
					$object['pageviews'] = [
						'total'      => (int) $pageviews[ $page ]['pageviews'],
						'difference' => (int) $pageviews[ $page ]['difference'],
					];
				}

				if ( isset( $console[ $page ] ) ) {
					$object = \array_merge( $console[ $page ], $object );
				}

				if ( ! isset( $object['links'] ) ) {
					$object['links'] = new stdClass();
				}

				$new_rows[ $page ] = $object;
			}
		}

		$history  = $this->get_graph_data_for_pages( $pages );
		$new_rows = Stats::get()->set_page_position_graph( $new_rows, $history );

		if ( in_array( $orderby, [ 'position', 'clicks', 'pageviews', 'impressions' ], true ) ) {
			$new_rows = $this->analytics_array_sort( $new_rows, $order, $orderby );
		}

		if ( empty( $new_rows ) ) {
			$new_rows['response'] = 'No Data';
		}
		return [
			'rows'      => $new_rows,
			'rowsFound' => ! empty( $objects['rowsFound'] ) ? $objects['rowsFound'] : 0,
		];
	}

	/**
	 * Sort array by order and orderby
	 *
	 * @param  array    $arr array.
	 *
	 * @param  Variable $arr_order is order direction.
	 *
	 * @param  Variable $arr_orderby is key for sort.
	 *
	 * @return $arr sorted array
	 */
	public function analytics_array_sort( $arr, $arr_order, $arr_orderby ) {

		if ( 'DESC' === $arr_order ) {
			uasort(
				$arr,
				function( $a, $b ) use ( $arr_orderby ) {

					if ( false === array_key_exists( $arr_orderby, $a ) ) {
						$a[ $arr_orderby ] = [ 'total' => '0' ];
					}
					if ( false === array_key_exists( $arr_orderby, $b ) ) {
						$b[ $arr_orderby ] = [ 'total' => '0' ];
					}

					return $a[ $arr_orderby ]['total'] < $b[ $arr_orderby ]['total'];
				}
			);
		}

		if ( 'ASC' === $arr_order ) {
			uasort(
				$arr,
				function( $a, $b ) use ( $arr_orderby ) {

					if ( false === array_key_exists( $arr_orderby, $a ) ) {
						$a[ $arr_orderby ] = [ 'total' => '0' ];
					}
					if ( false === array_key_exists( $arr_orderby, $b ) ) {
						$b[ $arr_orderby ] = [ 'total' => '0' ];
					}

					return $a[ $arr_orderby ]['total'] > $b[ $arr_orderby ]['total'];
				}
			);
		}

		return $arr;
	}
	/**
	 * Get ranking keywords data and append it to existing post data.
	 *
	 * @param  object $post Post object.
	 * @return object
	 */
	public function add_ranking_keywords( $post ) {
		$page    = $post->page;
		$data    = Stats::get()->get_analytics_data(
			[
				'dimension' => 'query',
				'offset'    => 0,
				'perpage'   => 20,
				'orderBy'   => 'impressions',
				'sub_where' => "AND page = '{$page}'",
			]
		);
		$history = Keywords::get()->get_graph_data_for_keywords( \array_keys( $data ) );

		$post->rankingKeywords = Stats::get()->set_query_position( $data, $history ); // phpcs:ignore

		return $post;
	}

	/**
	 * Append backlinks data into existing post data.
	 *
	 * @param  object $post  Post object.
	 * @return object
	 */
	public function add_backlinks( $post ) {
		$post->backlinks = [
			'total'      => 0,
			'previous'   => 0,
			'difference' => 0,
		];

		return $post;
	}

	/**
	 * Append badges data into existing post data.
	 *
	 * @param  object $post  Post object.
	 * @return object
	 */
	public function add_badges( $post ) {
		$post->badges = [
			'clicks'      => $this->get_position_for_badges( 'clicks', $post->page ),
			'traffic'     => $this->get_position_for_badges( 'traffic', $post->page ),
			'keywords'    => $this->get_position_for_badges( 'query', $post->page ),
			'impressions' => $this->get_position_for_badges( 'impressions', $post->page ),
		];

		return $post;
	}

	/**
	 * Get position for badges.
	 *
	 * @param  string $column Column name.
	 * @param  string $page   Page url.
	 * @return integer
	 */
	public function get_position_for_badges( $column, $page ) {
		$start = date( 'Y-m-d H:i:s', strtotime( '-30 days ', Stats::get()->end ) );
		if ( 'traffic' === $column ) {
			$rows = DB::traffic()
				->select( 'page' )
				->selectSum( 'pageviews', 'pageviews' )
				->whereBetween( 'created', [ $start, Stats::get()->end_date ] )
				->groupBy( 'page' )
				->orderBy( 'pageviews', 'DESC' )
				->limit( 5 );
		} else {
			$rows = DB::analytics()
				->select( 'page' )
				->whereBetween( 'created', [ $start, Stats::get()->end_date ] )
				->groupBy( 'page' )
				->orderBy( $column, 'DESC' )
				->limit( 5 );
		}

		if ( 'impressions' === $column || 'click' === $column ) {
			$rows->selectSum( $column, $column );
		}

		if ( 'query' === $column ) {
			$rows->selectCount( 'DISTINCT(query)', 'keywords' );
		}

		$rows = $rows->get( ARRAY_A );
		foreach ( $rows as $index => $row ) {
			if ( $page === $row['page'] ) {
				return $index + 1;
			}
		}

		return 99;
	}

	/**
	 * Append analytics graph data into existing post data.
	 *
	 * @param  object $post Post object.
	 * @return object
	 */
	public function get_graph_data_for_post( $post ) {
		global $wpdb;

		// Step1. Get splitted date intervals for graph within selected date range.
		$data          = new stdClass();
		$page          = $post->page;
		$intervals     = Stats::get()->get_intervals();
		$sql_daterange = Stats::get()->get_sql_date_intervals( $intervals );

		// Step2. Get analytics data summary for each splitted date intervals.
		$query   = $wpdb->prepare(
			"SELECT DATE_FORMAT( created, '%%Y-%%m-%%d') as date, SUM( clicks ) as clicks, SUM(impressions) as impressions, ROUND( AVG(ctr), 2 ) as ctr, {$sql_daterange}
			FROM {$wpdb->prefix}rank_math_analytics_gsc
			WHERE created BETWEEN %s AND %s AND page LIKE '%{$page}'
			GROUP BY range_group",
			Stats::get()->start_date,
			Stats::get()->end_date
		);
		$metrics = $wpdb->get_results( $query );

		// Step3. Get position data summary for each splitted date intervals.
		$query     = $wpdb->prepare(
			"SELECT page, MAX(CONCAT(t.uid, ':', t.range_group)) as range_group FROM
				(SELECT page, MAX(CONCAT(page, ':', DATE(created), ':', LPAD((100 - position), 3, '0'))) as uid, {$sql_daterange}
				FROM {$wpdb->prefix}rank_math_analytics_gsc
				WHERE created BETWEEN %s AND %s AND page LIKE '%{$page}'
				GROUP BY range_group, DATE(created)
				ORDER BY DATE(created) DESC) AS t
			GROUP BY t.range_group",
			Stats::get()->start_date,
			Stats::get()->end_date
		);
		$positions = $wpdb->get_results( $query );
		$positions = Stats::get()->extract_data_from_mixed( $positions, 'range_group', ':', [ 'range_group', 'position', 'date' ] );

		// Step4. Get keywords count for each splitted date intervals.
		$query    = $wpdb->prepare(
			"SELECT DATE_FORMAT( created, '%%Y-%%m-%%d') as date, COUNT(DISTINCT(query)) as keywords, {$sql_daterange}
			FROM {$wpdb->prefix}rank_math_analytics_gsc
			WHERE created BETWEEN %s AND %s AND page LIKE '%{$page}'
			GROUP BY range_group",
			Stats::get()->start_date,
			Stats::get()->end_date
		);
		$keywords = $wpdb->get_results( $query );
		// phpcs:enable

		// Step5. Filter graph data.
		$metrics   = Stats::get()->filter_graph_rows( $metrics );
		$positions = Stats::get()->filter_graph_rows( $positions );
		$keywords  = Stats::get()->filter_graph_rows( $keywords );

		// Step6. Convert types.
		$metrics   = array_map( [ Stats::get(), 'normalize_graph_rows' ], $metrics );
		$positions = array_map( [ Stats::get(), 'normalize_graph_rows' ], $positions );
		$keywords  = array_map( [ Stats::get(), 'normalize_graph_rows' ], $keywords );

		// Step7. Merge all analytics data.
		$data = Stats::get()->get_date_array(
			$intervals['dates'],
			[
				'clicks'      => [],
				'impressions' => [],
				'position'    => [],
				'ctr'         => [],
				'keywords'    => [],
				'pageviews'   => [],
			]
		);

		$data = Stats::get()->get_merge_data_graph( $metrics, $data, $intervals['map'] );
		$data = Stats::get()->get_merge_data_graph( $positions, $data, $intervals['map'] );
		$data = Stats::get()->get_merge_data_graph( $keywords, $data, $intervals['map'] );

		// Step8. Get traffic data in case analytics is connected for each splitted data intervals.
		if ( \RankMath\Google\Analytics::is_analytics_connected() ) {
			$query   = $wpdb->prepare(
				"SELECT DATE_FORMAT( created, '%%Y-%%m-%%d') as date, SUM( pageviews ) as pageviews, {$sql_daterange}
				FROM {$wpdb->prefix}rank_math_analytics_ga
				WHERE created BETWEEN %s AND %s AND page LIKE '%{$page}'
				GROUP BY range_group",
				Stats::get()->start_date,
				Stats::get()->end_date
			);
			$traffic = $wpdb->get_results( $query );

			// Filter graph data.
			$traffic = Stats::get()->filter_graph_rows( $traffic );

			// Convert types.
			$traffic = array_map( [ Stats::get(), 'normalize_graph_rows' ], $traffic );

			$data = Stats::get()->get_merge_data_graph( $traffic, $data, $intervals['map'] );
		}

		$data = Stats::get()->get_graph_data_flat( $data );

		// Step9. Append graph data into existing post data.
		$post->graph = array_values( $data );

		return $post;
	}

	/**
	 * Get posts rows.
	 *
	 * @param WP_REST_Request $request Full details about the request.
	 *
	 * @return array Posts rows.
	 */
	public function get_posts_rows( WP_REST_Request $request ) {
		$per_page = 25;

		$cache_args             = $request->get_params();
		$cache_args['per_page'] = $per_page;

		$cache_group = 'rank_math_rest_posts_rows';
		$cache_key   = $this->generate_hash( $cache_args );
		$data        = $this->get_cache( $cache_key, $cache_group );
		if ( ! empty( $data ) ) {
			return $data;
		}

		// Pagination.
		$offset    = ( $request->get_param( 'page' ) - 1 ) * $per_page;
		$orderby   = $request->get_param( 'orderby' );
		$post_type = sanitize_key( $request->get_param( 'postType' ) );
		$order     = $request->get_param( 'order' );
		$order     = in_array( $order, [ 'asc', 'desc' ], true ) ? $order : 'desc';
		$order     = strtoupper( $order );

		$post_type_clause = $post_type ? " AND o.object_subtype = '{$post_type}'" : '';
		if ( 'pageviews' === $orderby ) {
			// Get posts order by pageviews.
			$t_data    = Pageviews::get_pageviews_with_object(
				[
					'order'     => $order,
					'limit'     => "LIMIT {$offset}, {$per_page}",
					'sub_where' => $post_type_clause,
				]
			);
			$pageviews = Stats::get()->set_page_as_key( $t_data['rows'] );
			$pages     = \array_keys( $pageviews );
			$pages     = array_map( 'esc_sql', $pages );
			$console   = Stats::get()->get_analytics_data(
				[
					'offset'    => 0, // Should set as 0.
					'perpage'   => $per_page,
					'objects'   => false,
					'sub_where' => " AND page IN ('" . join( "', '", $pages ) . "')",
				]
			);

			$data['rowsFound'] = $this->rows_found();

			foreach ( $pageviews as $page => &$pageview ) {
				$pageview['pageviews'] = [
					'total'      => (int) $pageview['pageviews'],
					'difference' => (int) $pageview['difference'],
				];

				if ( isset( $console[ $page ] ) ) {
					unset( $console[ $page ]['pageviews'] );
					$pageview = \array_merge( $pageview, $console[ $page ] );
				}
			}

			$history   = $this->get_graph_data_for_pages( $pages );
			$pageviews = Stats::get()->set_page_position_graph( $pageviews, $history );

			$data['rows'] = $pageviews;

		} else {
			// Get posts order by impressions.
			$t_data = DB::objects()
				->select( [ 'page', 'title', 'object_id' ] )
				->where( 'is_indexable', 1 );
			if ( 'title' === $orderby ) {
				$t_data->orderBy( $orderby, $order )
					->limit( $per_page, $offset );
			}
			$t_data = $t_data->get( ARRAY_A );

			$pages  = Stats::get()->set_page_as_key( $t_data );
			$params = \array_keys( $pages );
			$params = array_map( 'esc_sql', $params );

			$args = [
				'dimension' => 'page',
				'offset'    => 0,
				'perpage'   => 20000,
				'sub_where' => " AND page IN ('" . join( "', '", $params ) . "')",
			];

			if ( 'title' !== $orderby ) {
				$args['orderBy'] = $orderby;
				$args['order']   = $order;
			}

			$rows = Stats::get()->get_analytics_data( $args );

			if ( 'title' !== $orderby ) {
				foreach ( $pages as $page => $row ) {
					if ( ! isset( $rows[ $page ] ) ) {
						$rows[ $page ] = $row;
					} else {
						$rows[ $page ] = \array_merge( $rows[ $page ], $row );
					}
				}

				$history           = $this->get_graph_data_for_pages( $params );
				$data['rows']      = Stats::get()->set_page_position_graph( $rows, $history );
				$data['rowsFound'] = count( $pages );

				// Filter array by $offset, $perpage value.
				$data['rows'] = array_slice( $data['rows'], $offset, $per_page, true );

			} else {
				foreach ( $pages as $page => &$row ) {
					if ( isset( $rows[ $page ] ) ) {
						$row = \array_merge( $row, $rows[ $page ] );
					}
				}

				$history           = $this->get_graph_data_for_pages( $params );
				$data['rows']      = Stats::get()->set_page_position_graph( $pages, $history );
				$data['rowsFound'] = $this->rows_found();
			}

			// Get fetched page info again.
			$pages  = Stats::get()->set_page_as_key( $data['rows'] );
			$params = \array_keys( $pages );
			$params = array_map( 'esc_sql', $params );

			// Get pageviews info.
			$pageviews = Pageviews::get_pageviews_with_object(
				[
					'limit'     => "LIMIT 0, {$per_page}",
					'sub_where' => " AND o.page IN ('" . join( "', '", $params ) . "')" . $post_type_clause,
				]
			);
			$pageviews = Stats::get()->set_page_as_key( $pageviews['rows'] );

			// Merge pageview info into main data.
			foreach ( $data['rows'] as $page => &$row ) {
				if ( isset( $pageviews[ $page ] ) ) {
					$pageview = [
						'pageviews' => [
							'total'      => (int) $pageviews[ $page ]['pageviews'],
							'difference' => (int) $pageviews[ $page ]['difference'],
						],
					];
					$row      = \array_merge( $row, $pageview );
				}
			}
		}
		if ( empty( $data ) ) {
			$data['response'] = 'No Data';
		} else {
			$this->set_cache( $cache_key, $data, $cache_group, DAY_IN_SECONDS );
		}
		return $data;
	}

	/**
	 * Get top 5 winning posts.
	 *
	 * @return array
	 */
	public function get_winning_posts() {
		global $wpdb;

		$cache_key = Stats::get()->get_cache_key( 'winning_posts', Stats::get()->days . 'days' );
		$cache     = get_transient( $cache_key );

		if ( false !== $cache ) {
			return $cache;
		}

		$rows = Stats::get()->get_analytics_data(
			[
				'order'    => 'ASC',
				'objects'  => true,
				'pageview' => true,
				'offset'   => 0,
				'perpage'  => 5,
				'type'     => 'win',
			]
		);

		$history = $this->get_graph_data_for_pages( \array_keys( $rows ) );
		$rows    = Stats::get()->set_page_position_graph( $rows, $history );

		if ( empty( $rows ) ) {
			$rows['response'] = 'No Data';
		}
		set_transient( $cache_key, $rows, DAY_IN_SECONDS );

		return $rows;
	}

	/**
	 * Get top 5 losing posts.
	 *
	 * @return object
	 */
	public function get_losing_posts() {
		global $wpdb;

		$cache_key = Stats::get()->get_cache_key( 'losing_posts', Stats::get()->days . 'days' );
		$cache     = get_transient( $cache_key );

		if ( false !== $cache ) {
			return $cache;
		}

		$rows = Stats::get()->get_analytics_data(
			[
				'objects'  => true,
				'pageview' => true,
				'offset'   => 0,
				'perpage'  => 5,
				'type'     => 'lose',
			]
		);

		$history = $this->get_graph_data_for_pages( \array_keys( $rows ) );
		$rows    = Stats::get()->set_page_position_graph( $rows, $history );
		if ( empty( $rows ) ) {
			$rows['response'] = 'No Data';
		}
		set_transient( $cache_key, $rows, DAY_IN_SECONDS );

		return $rows;
	}

	/**
	 * Get graph data for pages.
	 *
	 * @param array $pages Pages to get data for.
	 *
	 * @return array
	 */
	public function get_graph_data_for_pages( $pages ) {
		global $wpdb;

		$intervals     = Stats::get()->get_intervals();
		$sql_daterange = Stats::get()->get_sql_date_intervals( $intervals );
		$pages         = \array_map( 'esc_sql', $pages );
		$pages         = '(\'' . join( '\', \'', $pages ) . '\')';

		$query = $wpdb->prepare(
			"SELECT page, date, MAX(CONCAT(t.uid, ':', t.range_group)) as range_group FROM
				( SELECT page, DATE_FORMAT( created,'%%Y-%%m-%%d') as date, MAX( CONCAT( page, ':', DATE( created ), ':', LPAD( ( 100 - position ), 3, '0' ) ) ) as uid, {$sql_daterange}
				FROM {$wpdb->prefix}rank_math_analytics_gsc
				WHERE page IN {$pages} AND created BETWEEN %s AND %s
				GROUP BY page, range_group, DATE(created)
				ORDER BY page ASC, DATE(created) DESC) AS t
			GROUP BY t.page, t.range_group
			ORDER BY date ASC",
			Stats::get()->start_date,
			Stats::get()->end_date
		);
		$data  = $wpdb->get_results( $query );

		$data = Stats::get()->extract_data_from_mixed( $data, 'range_group', ':', [ 'range_group', 'position' ] );
		$data = Stats::get()->filter_graph_rows( $data );

		return array_map( [ Stats::get(), 'normalize_graph_rows' ], $data );
	}

	/**
	 * Count indexable pages.
	 *
	 * @return mixed
	 */
	private function rows_found() {
		return DB::objects()
			->selectCount( 'page' )
			->where( 'is_indexable', 1 )
			->getVar();
	}
}