Campaigns.php
3 years ago
City_Statistics.php
3 years ago
Country_Statistics.php
3 years ago
Current_Traffic_Finder.php
3 years ago
Interval.php
3 years ago
Minute_Interval.php
3 years ago
Range_Query.php
3 years ago
Referrers.php
3 years ago
Reset_Database.php
3 years ago
Resources.php
3 years ago
Ten_Second_Interval.php
3 years ago
View.php
3 years ago
Views.php
3 years ago
Visitors_Over_Time_Finder.php
3 years ago
Views.php
279 lines
| 1 | <?php |
| 2 | |
| 3 | namespace IAWP_SCOPED\IAWP\Queries; |
| 4 | |
| 5 | use IAWP_SCOPED\IAWP\Query; |
| 6 | use IAWP_SCOPED\IAWP\Utils\Timezone; |
| 7 | class Views extends Range_Query |
| 8 | { |
| 9 | const REFERRERS = 'REFERRERS'; |
| 10 | const RESOURCES = 'RESOURCES'; |
| 11 | const GEO = 'GEO'; |
| 12 | const CAMPAIGNS = 'CAMPAIGNS'; |
| 13 | private $views; |
| 14 | private $prev_period_views; |
| 15 | private $daily_views; |
| 16 | private $visitors; |
| 17 | private $prev_period_visitors; |
| 18 | private $daily_visitors; |
| 19 | private $sessions; |
| 20 | private $prev_period_sessions; |
| 21 | private $daily_sessions; |
| 22 | private $woocommerce_orders; |
| 23 | private $prev_woocommerce_orders; |
| 24 | private $woocommerce_net_sales; |
| 25 | private $prev_woocommerce_net_sales; |
| 26 | private $daily_woocommerce_orders; |
| 27 | private $daily_woocommerce_net_sales; |
| 28 | public function __construct($type, $allowed_ids = null, $start = null, $end = null) |
| 29 | { |
| 30 | parent::__construct(['start' => $start, 'end' => $end]); |
| 31 | if ($type === self::RESOURCES || $type === self::REFERRERS || $type === self::GEO || $type === self::CAMPAIGNS) { |
| 32 | $this->query($type, $allowed_ids); |
| 33 | } else { |
| 34 | throw new \Exception('IAWP_Views: Unsupported type'); |
| 35 | } |
| 36 | } |
| 37 | public function views() |
| 38 | { |
| 39 | return $this->views; |
| 40 | } |
| 41 | public function prev_period_views() |
| 42 | { |
| 43 | return $this->prev_period_views; |
| 44 | } |
| 45 | public function daily_views() |
| 46 | { |
| 47 | return $this->daily_views; |
| 48 | } |
| 49 | public function views_percentage_growth() |
| 50 | { |
| 51 | return $this->percentage_growth($this->views(), $this->prev_period_views()); |
| 52 | } |
| 53 | public function visitors() |
| 54 | { |
| 55 | return $this->visitors; |
| 56 | } |
| 57 | public function visitors_percentage_growth() |
| 58 | { |
| 59 | return $this->percentage_growth($this->visitors(), $this->prev_period_visitors()); |
| 60 | } |
| 61 | public function prev_period_visitors() |
| 62 | { |
| 63 | return $this->prev_period_visitors; |
| 64 | } |
| 65 | public function daily_visitors() |
| 66 | { |
| 67 | return $this->daily_visitors; |
| 68 | } |
| 69 | public function sessions() |
| 70 | { |
| 71 | return $this->sessions; |
| 72 | } |
| 73 | public function sessions_percentage_growth() |
| 74 | { |
| 75 | return $this->percentage_growth($this->sessions(), $this->prev_period_sessions()); |
| 76 | } |
| 77 | public function prev_period_sessions() |
| 78 | { |
| 79 | return $this->prev_period_sessions; |
| 80 | } |
| 81 | public function daily_sessions() |
| 82 | { |
| 83 | return $this->daily_sessions; |
| 84 | } |
| 85 | public function woocommerce_orders() : int |
| 86 | { |
| 87 | return $this->woocommerce_orders; |
| 88 | } |
| 89 | public function prev_woocommerce_orders() : int |
| 90 | { |
| 91 | return $this->prev_woocommerce_orders; |
| 92 | } |
| 93 | public function woocommerce_orders_percentage_growth() : float |
| 94 | { |
| 95 | return $this->percentage_growth($this->woocommerce_orders(), $this->prev_woocommerce_orders()); |
| 96 | } |
| 97 | public function woocommerce_net_sales() : float |
| 98 | { |
| 99 | return $this->woocommerce_net_sales; |
| 100 | } |
| 101 | public function prev_woocommerce_net_sales() : float |
| 102 | { |
| 103 | return $this->prev_woocommerce_net_sales; |
| 104 | } |
| 105 | public function woocommerce_net_sales_percentage_growth() : float |
| 106 | { |
| 107 | return $this->percentage_growth((int) $this->woocommerce_net_sales(), (int) $this->prev_woocommerce_net_sales()); |
| 108 | } |
| 109 | public function daily_woocommerce_orders() : array |
| 110 | { |
| 111 | return $this->daily_woocommerce_orders; |
| 112 | } |
| 113 | public function daily_woocommerce_net_sales() : array |
| 114 | { |
| 115 | return $this->daily_woocommerce_net_sales; |
| 116 | } |
| 117 | private function percentage_growth(int $current_period, int $previous_period) : float |
| 118 | { |
| 119 | if ($current_period === 0 && $previous_period !== 0) { |
| 120 | return -100; |
| 121 | } elseif ($current_period === 0 || $previous_period === 0) { |
| 122 | return 0; |
| 123 | } |
| 124 | $growth = ($current_period / $previous_period - 1) * 100; |
| 125 | return \round($growth, 0); |
| 126 | } |
| 127 | private function query($type, $allowed_ids) |
| 128 | { |
| 129 | global $wpdb; |
| 130 | $views_table = Query::get_table_name(Query::VIEWS); |
| 131 | $visitors_table = Query::get_table_name(Query::VISITORS); |
| 132 | $sessions_table = Query::get_table_name(Query::SESSIONS); |
| 133 | if (\is_null($allowed_ids)) { |
| 134 | $skip_in = 1; |
| 135 | $allowed_ids = [0]; |
| 136 | $in = '%s'; |
| 137 | } elseif (\count($allowed_ids) === 0) { |
| 138 | $skip_in = 0; |
| 139 | $allowed_ids = [0]; |
| 140 | $in = '%s'; |
| 141 | } else { |
| 142 | $skip_in = 0; |
| 143 | $in = \implode(',', \array_fill(0, \count($allowed_ids), '%s')); |
| 144 | } |
| 145 | if ($type === self::RESOURCES) { |
| 146 | $column = 'views.resource_id'; |
| 147 | } elseif ($type === self::REFERRERS) { |
| 148 | $column = 'sessions.referrer_id'; |
| 149 | } elseif ($type === self::GEO) { |
| 150 | $column = 'sessions.visitor_id'; |
| 151 | } else { |
| 152 | $column = 'sessions.campaign_id'; |
| 153 | } |
| 154 | $has_null_id = \false; |
| 155 | foreach ($allowed_ids as $id) { |
| 156 | if (\is_null($id)) { |
| 157 | $has_null_id = \true; |
| 158 | break; |
| 159 | } |
| 160 | } |
| 161 | $null_check = ''; |
| 162 | if ($type === self::REFERRERS && $has_null_id) { |
| 163 | $null_check = "OR {$column} IS NULL"; |
| 164 | } |
| 165 | $wc_orders_table = Query::get_table_name(Query::WC_ORDERS); |
| 166 | $wc_order_stats_table = $wpdb->prefix . 'wc_order_stats'; |
| 167 | $utc = Timezone::utc_offset(); |
| 168 | $local_offset = Timezone::local_offset(); |
| 169 | if (\IAWP_SCOPED\iawp_using_woocommerce()) { |
| 170 | $query = $wpdb->prepare("\n SELECT\n DATE(CONVERT_TZ(views.viewed_at, '{$utc}', '{$local_offset}')) as date,\n COUNT(*) as views,\n COUNT(DISTINCT sessions.visitor_id) as visitors,\n COUNT(DISTINCT sessions.session_id) as sessions,\n COUNT(DISTINCT IF(wc_order_stats.total_sales >= 0, wc_order_stats.order_id, NULL)) AS wc_orders,\n SUM(IF(wc_order_stats.total_sales >= 0, wc_order_stats.total_sales,\n 0)) AS wc_gross_sales,\n COUNT(DISTINCT IF(wc_order_stats.total_sales < 0, wc_order_stats.order_id,\n NULL)) AS wc_refunds,\n ABS(SUM(IF(wc_order_stats.total_sales < 0, wc_order_stats.total_sales,\n 0))) AS wc_refunded_amount\n FROM {$views_table} AS views\n LEFT JOIN {$sessions_table} AS sessions ON views.session_id = sessions.session_id\n LEFT JOIN {$visitors_table} AS visitors ON sessions.visitor_id = visitors.visitor_id\n LEFT JOIN {$wc_orders_table} AS wc_orders ON wc_orders.view_id = views.id \n LEFT JOIN {$wc_order_stats_table} AS wc_order_stats ON (wc_orders.order_id = wc_order_stats.order_id OR wc_orders.order_id = wc_order_stats.parent_id) AND wc_order_stats.status IN ('wc-completed', 'wc-processing', 'wc-refunded')\n WHERE views.viewed_at BETWEEN %s AND %s\n AND (%d = 1 OR {$column} IN ({$in}) {$null_check})\n AND ('{$type}' != 'GEO' OR visitors.country_code IS NOT NULL)\n AND ('{$type}' != 'CAMPAIGNS' OR sessions.campaign_id IS NOT NULL)\n GROUP BY DATE(CONVERT_TZ(views.viewed_at, '{$utc}', '{$local_offset}'))", $this->formatted_start(), $this->formatted_end(), $skip_in, ...$allowed_ids); |
| 171 | } else { |
| 172 | $query = $wpdb->prepare("\n SELECT\n DATE(CONVERT_TZ(views.viewed_at, '{$utc}', '{$local_offset}')) as date,\n COUNT(*) as views,\n COUNT(DISTINCT sessions.visitor_id) as visitors,\n COUNT(DISTINCT sessions.session_id) as sessions,\n 0 AS wc_orders,\n 0 AS wc_gross_sales,\n 0 AS wc_refunds,\n 0 AS wc_refunded_amount\n FROM {$views_table} AS views\n LEFT JOIN {$sessions_table} AS sessions ON views.session_id = sessions.session_id\n LEFT JOIN {$visitors_table} AS visitors ON sessions.visitor_id = visitors.visitor_id\n WHERE views.viewed_at BETWEEN %s AND %s\n AND (%d = 1 OR {$column} IN ({$in}) {$null_check})\n AND ('{$type}' != 'GEO' OR visitors.country_code IS NOT NULL)\n AND ('{$type}' != 'CAMPAIGNS' OR sessions.campaign_id IS NOT NULL)\n GROUP BY DATE(CONVERT_TZ(views.viewed_at, '{$utc}', '{$local_offset}'))", $this->formatted_start(), $this->formatted_end(), $skip_in, ...$allowed_ids); |
| 173 | } |
| 174 | $rows = $wpdb->get_results($query); |
| 175 | // This separate query for visitor counts is required |
| 176 | // Without it, visitors visiting on separate days are counted twice in the quick stats |
| 177 | $visitors_query = $wpdb->prepare("\n SELECT\n COUNT(DISTINCT sessions.visitor_id) as visitors,\n COUNT(DISTINCT sessions.session_id) as sessions\n FROM {$views_table} AS views\n LEFT JOIN {$sessions_table} AS sessions ON views.session_id = sessions.session_id\n LEFT JOIN {$visitors_table} AS visitors ON sessions.visitor_id = visitors.visitor_id\n WHERE views.viewed_at BETWEEN %s AND %s\n AND (%d = 1 OR {$column} IN ({$in}) {$null_check})\n AND ('{$type}' != 'GEO' OR visitors.country_code IS NOT NULL)\n AND ('{$type}' != 'CAMPAIGNS' OR sessions.campaign_id IS NOT NULL)\n ", $this->formatted_start(), $this->formatted_end(), $skip_in, ...$allowed_ids); |
| 178 | $visitors_results = $wpdb->get_row($visitors_query); |
| 179 | $visitors = \intval($visitors_results->visitors); |
| 180 | $sessions = \intval($visitors_results->sessions); |
| 181 | if (\IAWP_SCOPED\iawp_using_woocommerce()) { |
| 182 | $prev_period_query = $wpdb->prepare("\n SELECT\n COUNT(*) as views,\n COUNT(DISTINCT sessions.visitor_id) as visitors,\n COUNT(DISTINCT sessions.session_id) as sessions,\n COUNT(DISTINCT IF(wc_order_stats.total_sales >= 0, wc_order_stats.order_id, NULL)) AS wc_orders,\n SUM(IF(wc_order_stats.total_sales >= 0, wc_order_stats.total_sales,\n 0)) AS wc_gross_sales,\n COUNT(DISTINCT IF(wc_order_stats.total_sales < 0, wc_order_stats.order_id,\n NULL)) AS wc_refunds,\n ABS(SUM(IF(wc_order_stats.total_sales < 0, wc_order_stats.total_sales,\n 0))) AS wc_refunded_amount\n FROM {$views_table} AS views\n LEFT JOIN {$sessions_table} AS sessions ON views.session_id = sessions.session_id\n LEFT JOIN {$visitors_table} AS visitors ON sessions.visitor_id = visitors.visitor_id\n LEFT JOIN {$wc_orders_table} AS wc_orders ON wc_orders.view_id = views.id \n LEFT JOIN {$wc_order_stats_table} AS wc_order_stats ON (wc_orders.order_id = wc_order_stats.order_id OR wc_orders.order_id = wc_order_stats.parent_id) AND wc_order_stats.status IN ('wc-completed', 'wc-processing', 'wc-refunded')\n WHERE views.viewed_at BETWEEN %s AND %s\n AND (%d = 1 OR {$column} IN ({$in}) {$null_check})\n AND ('{$type}' != 'GEO' OR visitors.country_code IS NOT NULL)\n AND ('{$type}' != 'CAMPAIGNS' OR sessions.campaign_id IS NOT NULL);\n ", $this->prev_period_formatted_start(), $this->prev_period_formatted_end(), $skip_in, ...$allowed_ids); |
| 183 | } else { |
| 184 | $prev_period_query = $wpdb->prepare("\n SELECT\n COUNT(*) as views,\n COUNT(DISTINCT sessions.visitor_id) as visitors,\n COUNT(DISTINCT sessions.session_id) as sessions,\n 0 AS wc_orders,\n 0 AS wc_gross_sales,\n 0 AS wc_refunds,\n 0 AS wc_refunded_amount\n FROM {$views_table} AS views\n LEFT JOIN {$sessions_table} AS sessions ON views.session_id = sessions.session_id\n LEFT JOIN {$visitors_table} AS visitors ON sessions.visitor_id = visitors.visitor_id\n WHERE views.viewed_at BETWEEN %s AND %s\n AND (%d = 1 OR {$column} IN ({$in}) {$null_check})\n AND ('{$type}' != 'GEO' OR visitors.country_code IS NOT NULL)\n AND ('{$type}' != 'CAMPAIGNS' OR sessions.campaign_id IS NOT NULL);\n ", $this->prev_period_formatted_start(), $this->prev_period_formatted_end(), $skip_in, ...$allowed_ids); |
| 185 | } |
| 186 | $prev_period_results = $wpdb->get_row($prev_period_query); |
| 187 | $prev_period_views = \intval($prev_period_results->views); |
| 188 | $prev_period_visitors = \intval($prev_period_results->visitors); |
| 189 | $prev_period_sessions = \intval($prev_period_results->sessions); |
| 190 | $prev_woocommerce_orders = \intval($prev_period_results->wc_orders); |
| 191 | $prev_woocommerce_net_sales = \floatval($prev_period_results->wc_gross_sales) - \floatval($prev_period_results->wc_refunded_amount); |
| 192 | $views = 0; |
| 193 | $woocommerce_orders = 0; |
| 194 | $woocommerce_gross_sales = 0; |
| 195 | $woocommerce_refunded_amount = 0; |
| 196 | $daily_views = []; |
| 197 | $daily_visitors = []; |
| 198 | $daily_sessions = []; |
| 199 | $daily_woocommerce_orders = []; |
| 200 | $daily_woocommerce_net_sales = []; |
| 201 | foreach ($rows as $row) { |
| 202 | $row_views = \intval($row->views); |
| 203 | $row_visitors = \intval($row->visitors); |
| 204 | $row_sessions = \intval($row->sessions); |
| 205 | $row_woocommerce_orders = \intval($row->wc_orders); |
| 206 | $row_woocommerce_gross_sales = \floatval($row->wc_gross_sales); |
| 207 | $row_woocommerce_refunded_amount = \floatval($row->wc_refunded_amount); |
| 208 | $views += $row_views; |
| 209 | $woocommerce_orders += $row_woocommerce_orders; |
| 210 | $woocommerce_gross_sales += $row_woocommerce_gross_sales; |
| 211 | $woocommerce_refunded_amount += $row_woocommerce_refunded_amount; |
| 212 | $daily_views[] = [$row->date, $row_views]; |
| 213 | $daily_visitors[] = [$row->date, $row_visitors]; |
| 214 | $daily_sessions[] = [$row->date, $row_sessions]; |
| 215 | $daily_woocommerce_orders[] = [$row->date, $row_woocommerce_orders]; |
| 216 | $daily_woocommerce_net_sales[] = [$row->date, $row_woocommerce_gross_sales - $row_woocommerce_refunded_amount]; |
| 217 | } |
| 218 | $this->views = $views; |
| 219 | $this->prev_period_views = $prev_period_views; |
| 220 | $this->visitors = $visitors; |
| 221 | $this->prev_period_visitors = $prev_period_visitors; |
| 222 | $this->sessions = $sessions; |
| 223 | $this->prev_period_sessions = $prev_period_sessions; |
| 224 | $this->woocommerce_orders = $woocommerce_orders; |
| 225 | $this->prev_woocommerce_orders = $prev_woocommerce_orders; |
| 226 | $this->woocommerce_net_sales = $woocommerce_gross_sales - $woocommerce_refunded_amount; |
| 227 | $this->prev_woocommerce_net_sales = $prev_woocommerce_net_sales; |
| 228 | $this->daily_views = $this->fill_in_partial_day_range($daily_views); |
| 229 | $this->daily_visitors = $this->fill_in_partial_day_range($daily_visitors); |
| 230 | $this->daily_sessions = $this->fill_in_partial_day_range($daily_sessions); |
| 231 | $this->daily_woocommerce_orders = $this->fill_in_partial_day_range($daily_woocommerce_orders); |
| 232 | $this->daily_woocommerce_net_sales = $this->fill_in_partial_day_range($daily_woocommerce_net_sales); |
| 233 | } |
| 234 | /** |
| 235 | * @param array $partial_day_range |
| 236 | * @return array |
| 237 | */ |
| 238 | private function fill_in_partial_day_range(array $partial_day_range) : array |
| 239 | { |
| 240 | $interval = new \DateInterval('P1D'); |
| 241 | $user_timezone = new \DateTimeZone(Timezone::local_offset()); |
| 242 | $start = clone $this->start(); |
| 243 | $end = clone $this->end(); |
| 244 | $start->setTimezone($user_timezone); |
| 245 | $end->setTimezone($user_timezone); |
| 246 | $date_range = new \DatePeriod($start, $interval, $end); |
| 247 | $filled_in_data = []; |
| 248 | foreach ($date_range as $date) { |
| 249 | $stat = $this->get_statistic_for_date($partial_day_range, $date); |
| 250 | $filled_in_data[] = [$date, $stat]; |
| 251 | } |
| 252 | return $filled_in_data; |
| 253 | } |
| 254 | /** |
| 255 | * @param array $partial_day_range |
| 256 | * @param \DateTime $datetime_to_match |
| 257 | * @return int Defaults to 0 |
| 258 | */ |
| 259 | private function get_statistic_for_date(array $partial_day_range, \DateTime $datetime_to_match) : int |
| 260 | { |
| 261 | $user_timezone = new \DateTimeZone(Timezone::local_offset()); |
| 262 | $default_value = 0; |
| 263 | foreach ($partial_day_range as $day) { |
| 264 | $date = $day[0]; |
| 265 | $stat = $day[1]; |
| 266 | try { |
| 267 | $datetime = new \DateTime($date, $user_timezone); |
| 268 | } catch (\Exception $e) { |
| 269 | return $default_value; |
| 270 | } |
| 271 | // Intentionally using non-strict equality to see if two distinct DateTime objects represent the same time |
| 272 | if ($datetime == $datetime_to_match) { |
| 273 | return $stat; |
| 274 | } |
| 275 | } |
| 276 | return $default_value; |
| 277 | } |
| 278 | } |
| 279 |