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
445 lines
| 1 | <?php |
| 2 | |
| 3 | namespace IAWP\Queries; |
| 4 | |
| 5 | use IAWP\Query; |
| 6 | use IAWP\Utils\Timezone; |
| 7 | |
| 8 | class Views extends Range_Query |
| 9 | { |
| 10 | const REFERRERS = 'REFERRERS'; |
| 11 | const RESOURCES = 'RESOURCES'; |
| 12 | const GEO = 'GEO'; |
| 13 | const CAMPAIGNS = 'CAMPAIGNS'; |
| 14 | private $views; |
| 15 | private $prev_period_views; |
| 16 | private $daily_views; |
| 17 | private $visitors; |
| 18 | private $prev_period_visitors; |
| 19 | private $daily_visitors; |
| 20 | private $sessions; |
| 21 | private $prev_period_sessions; |
| 22 | private $daily_sessions; |
| 23 | private $woocommerce_orders; |
| 24 | private $prev_woocommerce_orders; |
| 25 | private $woocommerce_net_sales; |
| 26 | private $prev_woocommerce_net_sales; |
| 27 | private $daily_woocommerce_orders; |
| 28 | private $daily_woocommerce_net_sales; |
| 29 | |
| 30 | public function __construct($type, $allowed_ids = null, $start = null, $end = null) |
| 31 | { |
| 32 | parent::__construct([ |
| 33 | 'start' => $start, |
| 34 | 'end' => $end, |
| 35 | ]); |
| 36 | if ($type === self::RESOURCES || $type === self::REFERRERS || $type === self::GEO || $type === self::CAMPAIGNS) { |
| 37 | $this->query($type, $allowed_ids); |
| 38 | } else { |
| 39 | throw new \Exception('IAWP_Views: Unsupported type'); |
| 40 | } |
| 41 | } |
| 42 | |
| 43 | public function views() |
| 44 | { |
| 45 | return $this->views; |
| 46 | } |
| 47 | |
| 48 | public function prev_period_views() |
| 49 | { |
| 50 | return $this->prev_period_views; |
| 51 | } |
| 52 | |
| 53 | public function daily_views() |
| 54 | { |
| 55 | return $this->daily_views; |
| 56 | } |
| 57 | |
| 58 | public function views_percentage_growth() |
| 59 | { |
| 60 | return $this->percentage_growth($this->views(), $this->prev_period_views()); |
| 61 | } |
| 62 | |
| 63 | public function visitors() |
| 64 | { |
| 65 | return $this->visitors; |
| 66 | } |
| 67 | |
| 68 | public function visitors_percentage_growth() |
| 69 | { |
| 70 | return $this->percentage_growth($this->visitors(), $this->prev_period_visitors()); |
| 71 | } |
| 72 | |
| 73 | public function prev_period_visitors() |
| 74 | { |
| 75 | return $this->prev_period_visitors; |
| 76 | } |
| 77 | |
| 78 | public function daily_visitors() |
| 79 | { |
| 80 | return $this->daily_visitors; |
| 81 | } |
| 82 | |
| 83 | public function sessions() |
| 84 | { |
| 85 | return $this->sessions; |
| 86 | } |
| 87 | |
| 88 | public function sessions_percentage_growth() |
| 89 | { |
| 90 | return $this->percentage_growth($this->sessions(), $this->prev_period_sessions()); |
| 91 | } |
| 92 | |
| 93 | public function prev_period_sessions() |
| 94 | { |
| 95 | return $this->prev_period_sessions; |
| 96 | } |
| 97 | |
| 98 | public function daily_sessions() |
| 99 | { |
| 100 | return $this->daily_sessions; |
| 101 | } |
| 102 | |
| 103 | public function woocommerce_orders(): int |
| 104 | { |
| 105 | return $this->woocommerce_orders; |
| 106 | } |
| 107 | |
| 108 | public function prev_woocommerce_orders(): int |
| 109 | { |
| 110 | return $this->prev_woocommerce_orders; |
| 111 | } |
| 112 | |
| 113 | public function woocommerce_orders_percentage_growth(): int |
| 114 | { |
| 115 | return $this->percentage_growth($this->woocommerce_orders(), $this->prev_woocommerce_orders()); |
| 116 | } |
| 117 | |
| 118 | public function woocommerce_net_sales(): float |
| 119 | { |
| 120 | return $this->woocommerce_net_sales; |
| 121 | } |
| 122 | |
| 123 | public function prev_woocommerce_net_sales(): float |
| 124 | { |
| 125 | return $this->prev_woocommerce_net_sales; |
| 126 | } |
| 127 | |
| 128 | public function woocommerce_net_sales_percentage_growth(): int |
| 129 | { |
| 130 | return $this->percentage_growth($this->woocommerce_net_sales(), $this->prev_woocommerce_net_sales()); |
| 131 | } |
| 132 | |
| 133 | public function daily_woocommerce_orders(): array |
| 134 | { |
| 135 | return $this->daily_woocommerce_orders; |
| 136 | } |
| 137 | |
| 138 | public function daily_woocommerce_net_sales(): array |
| 139 | { |
| 140 | return $this->daily_woocommerce_net_sales; |
| 141 | } |
| 142 | |
| 143 | private function percentage_growth(int $current_period, int $previous_period): int |
| 144 | { |
| 145 | if ($current_period === 0 && $previous_period !== 0) { |
| 146 | return -100; |
| 147 | } elseif ($current_period === 0 || $previous_period === 0) { |
| 148 | return 0; |
| 149 | } |
| 150 | |
| 151 | return round((($current_period / $previous_period) - 1) * 100); |
| 152 | } |
| 153 | |
| 154 | private function query($type, $allowed_ids) |
| 155 | { |
| 156 | global $wpdb; |
| 157 | $views_table = Query::get_table_name(Query::VIEWS); |
| 158 | $visitors_table = Query::get_table_name(Query::VISITORS); |
| 159 | $sessions_table = Query::get_table_name(Query::SESSIONS); |
| 160 | |
| 161 | if (is_null($allowed_ids)) { |
| 162 | $skip_in = 1; |
| 163 | $allowed_ids = [0]; |
| 164 | $in = '%s'; |
| 165 | } elseif (count($allowed_ids) === 0) { |
| 166 | $skip_in = 0; |
| 167 | $allowed_ids = [0]; |
| 168 | $in = '%s'; |
| 169 | } else { |
| 170 | $skip_in = 0; |
| 171 | $in = implode(',', array_fill(0, count($allowed_ids), '%s')); |
| 172 | } |
| 173 | |
| 174 | if ($type === self::RESOURCES) { |
| 175 | $column = 'views.resource_id'; |
| 176 | } elseif ($type === self::REFERRERS) { |
| 177 | $column = 'sessions.referrer_id'; |
| 178 | } elseif ($type === self::GEO) { |
| 179 | $column = 'sessions.visitor_id'; |
| 180 | } elseif ($type === self::CAMPAIGNS) { |
| 181 | $column = 'sessions.campaign_id'; |
| 182 | } |
| 183 | |
| 184 | $has_null_id = false; |
| 185 | foreach ($allowed_ids as $id) { |
| 186 | if (is_null($id)) { |
| 187 | $has_null_id = true; |
| 188 | break; |
| 189 | } |
| 190 | } |
| 191 | $null_check = ''; |
| 192 | |
| 193 | if ($type === self::REFERRERS && $has_null_id) { |
| 194 | $null_check = "OR $column IS NULL"; |
| 195 | } |
| 196 | |
| 197 | $wc_orders_table = Query::get_table_name(Query::WC_ORDERS); |
| 198 | $wc_order_stats_table = $wpdb->prefix . 'wc_order_stats'; |
| 199 | $utc = Timezone::utc_offset(); |
| 200 | $local_offset = Timezone::local_offset(); |
| 201 | |
| 202 | if (iawp_using_woocommerce()) { |
| 203 | $query = $wpdb->prepare( |
| 204 | " |
| 205 | SELECT |
| 206 | DATE(CONVERT_TZ(views.viewed_at, '$utc', '$local_offset')) as date, |
| 207 | COUNT(*) as views, |
| 208 | COUNT(DISTINCT sessions.visitor_id) as visitors, |
| 209 | COUNT(DISTINCT sessions.session_id) as sessions, |
| 210 | COUNT(DISTINCT IF(wc_order_stats.total_sales >= 0, wc_order_stats.order_id, NULL)) AS wc_orders, |
| 211 | SUM(IF(wc_order_stats.total_sales >= 0, wc_order_stats.total_sales, |
| 212 | 0)) AS wc_gross_sales, |
| 213 | COUNT(DISTINCT IF(wc_order_stats.total_sales < 0, wc_order_stats.order_id, |
| 214 | NULL)) AS wc_refunds, |
| 215 | ABS(SUM(IF(wc_order_stats.total_sales < 0, wc_order_stats.total_sales, |
| 216 | 0))) AS wc_refunded_amount |
| 217 | FROM $views_table AS views |
| 218 | LEFT JOIN $sessions_table AS sessions ON views.session_id = sessions.session_id |
| 219 | LEFT JOIN $visitors_table AS visitors ON sessions.visitor_id = visitors.visitor_id |
| 220 | LEFT JOIN $wc_orders_table AS wc_orders ON wc_orders.view_id = views.id |
| 221 | 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') |
| 222 | WHERE views.viewed_at BETWEEN %s AND %s |
| 223 | AND (%d = 1 OR $column IN ($in) $null_check) |
| 224 | AND ('$type' != 'GEO' OR visitors.country_code IS NOT NULL) |
| 225 | AND ('$type' != 'CAMPAIGNS' OR sessions.campaign_id IS NOT NULL) |
| 226 | GROUP BY DATE(CONVERT_TZ(views.viewed_at, '$utc', '$local_offset'))", |
| 227 | $this->formatted_start(), |
| 228 | $this->formatted_end(), |
| 229 | $skip_in, |
| 230 | ...$allowed_ids |
| 231 | ); |
| 232 | } else { |
| 233 | $query = $wpdb->prepare( |
| 234 | " |
| 235 | SELECT |
| 236 | DATE(CONVERT_TZ(views.viewed_at, '$utc', '$local_offset')) as date, |
| 237 | COUNT(*) as views, |
| 238 | COUNT(DISTINCT sessions.visitor_id) as visitors, |
| 239 | COUNT(DISTINCT sessions.session_id) as sessions, |
| 240 | 0 AS wc_orders, |
| 241 | 0 AS wc_gross_sales, |
| 242 | 0 AS wc_refunds, |
| 243 | 0 AS wc_refunded_amount |
| 244 | FROM $views_table AS views |
| 245 | LEFT JOIN $sessions_table AS sessions ON views.session_id = sessions.session_id |
| 246 | LEFT JOIN $visitors_table AS visitors ON sessions.visitor_id = visitors.visitor_id |
| 247 | WHERE views.viewed_at BETWEEN %s AND %s |
| 248 | AND (%d = 1 OR $column IN ($in) $null_check) |
| 249 | AND ('$type' != 'GEO' OR visitors.country_code IS NOT NULL) |
| 250 | AND ('$type' != 'CAMPAIGNS' OR sessions.campaign_id IS NOT NULL) |
| 251 | GROUP BY DATE(CONVERT_TZ(views.viewed_at, '$utc', '$local_offset'))", |
| 252 | $this->formatted_start(), |
| 253 | $this->formatted_end(), |
| 254 | $skip_in, |
| 255 | ...$allowed_ids |
| 256 | ); |
| 257 | } |
| 258 | $rows = $wpdb->get_results($query); |
| 259 | |
| 260 | // This separate query for visitor counts is required |
| 261 | // Without it, visitors visiting on separate days are counted twice in the quick stats |
| 262 | $visitors_query = $wpdb->prepare( |
| 263 | " |
| 264 | SELECT |
| 265 | COUNT(DISTINCT sessions.visitor_id) as visitors, |
| 266 | COUNT(DISTINCT sessions.session_id) as sessions |
| 267 | FROM $views_table AS views |
| 268 | LEFT JOIN $sessions_table AS sessions ON views.session_id = sessions.session_id |
| 269 | LEFT JOIN $visitors_table AS visitors ON sessions.visitor_id = visitors.visitor_id |
| 270 | WHERE views.viewed_at BETWEEN %s AND %s |
| 271 | AND (%d = 1 OR $column IN ($in) $null_check) |
| 272 | AND ('$type' != 'GEO' OR visitors.country_code IS NOT NULL) |
| 273 | AND ('$type' != 'CAMPAIGNS' OR sessions.campaign_id IS NOT NULL) |
| 274 | ", |
| 275 | $this->formatted_start(), |
| 276 | $this->formatted_end(), |
| 277 | $skip_in, |
| 278 | ...$allowed_ids |
| 279 | ); |
| 280 | $visitors_results = $wpdb->get_row($visitors_query); |
| 281 | $visitors = intval($visitors_results->visitors); |
| 282 | $sessions = intval($visitors_results->sessions); |
| 283 | |
| 284 | if (iawp_using_woocommerce()) { |
| 285 | $prev_period_query = $wpdb->prepare( |
| 286 | " |
| 287 | SELECT |
| 288 | COUNT(*) as views, |
| 289 | COUNT(DISTINCT sessions.visitor_id) as visitors, |
| 290 | COUNT(DISTINCT sessions.session_id) as sessions, |
| 291 | COUNT(DISTINCT IF(wc_order_stats.total_sales >= 0, wc_order_stats.order_id, NULL)) AS wc_orders, |
| 292 | SUM(IF(wc_order_stats.total_sales >= 0, wc_order_stats.total_sales, |
| 293 | 0)) AS wc_gross_sales, |
| 294 | COUNT(DISTINCT IF(wc_order_stats.total_sales < 0, wc_order_stats.order_id, |
| 295 | NULL)) AS wc_refunds, |
| 296 | ABS(SUM(IF(wc_order_stats.total_sales < 0, wc_order_stats.total_sales, |
| 297 | 0))) AS wc_refunded_amount |
| 298 | FROM $views_table AS views |
| 299 | LEFT JOIN $sessions_table AS sessions ON views.session_id = sessions.session_id |
| 300 | LEFT JOIN $visitors_table AS visitors ON sessions.visitor_id = visitors.visitor_id |
| 301 | LEFT JOIN $wc_orders_table AS wc_orders ON wc_orders.view_id = views.id |
| 302 | 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') |
| 303 | WHERE views.viewed_at BETWEEN %s AND %s |
| 304 | AND (%d = 1 OR $column IN ($in) $null_check) |
| 305 | AND ('$type' != 'GEO' OR visitors.country_code IS NOT NULL) |
| 306 | AND ('$type' != 'CAMPAIGNS' OR sessions.campaign_id IS NOT NULL); |
| 307 | ", |
| 308 | $this->prev_period_formatted_start(), |
| 309 | $this->prev_period_formatted_end(), |
| 310 | $skip_in, |
| 311 | ...$allowed_ids |
| 312 | ); |
| 313 | } else { |
| 314 | $prev_period_query = $wpdb->prepare( |
| 315 | " |
| 316 | SELECT |
| 317 | COUNT(*) as views, |
| 318 | COUNT(DISTINCT sessions.visitor_id) as visitors, |
| 319 | COUNT(DISTINCT sessions.session_id) as sessions, |
| 320 | 0 AS wc_orders, |
| 321 | 0 AS wc_gross_sales, |
| 322 | 0 AS wc_refunds, |
| 323 | 0 AS wc_refunded_amount |
| 324 | FROM $views_table AS views |
| 325 | LEFT JOIN $sessions_table AS sessions ON views.session_id = sessions.session_id |
| 326 | LEFT JOIN $visitors_table AS visitors ON sessions.visitor_id = visitors.visitor_id |
| 327 | WHERE views.viewed_at BETWEEN %s AND %s |
| 328 | AND (%d = 1 OR $column IN ($in) $null_check) |
| 329 | AND ('$type' != 'GEO' OR visitors.country_code IS NOT NULL) |
| 330 | AND ('$type' != 'CAMPAIGNS' OR sessions.campaign_id IS NOT NULL); |
| 331 | ", |
| 332 | $this->prev_period_formatted_start(), |
| 333 | $this->prev_period_formatted_end(), |
| 334 | $skip_in, |
| 335 | ...$allowed_ids |
| 336 | ); |
| 337 | } |
| 338 | |
| 339 | $prev_period_results = $wpdb->get_row($prev_period_query); |
| 340 | $prev_period_views = intval($prev_period_results->views); |
| 341 | $prev_period_visitors = intval($prev_period_results->visitors); |
| 342 | $prev_period_sessions = intval($prev_period_results->sessions); |
| 343 | $prev_woocommerce_orders = intval($prev_period_results->wc_orders); |
| 344 | $prev_woocommerce_net_sales = floatval($prev_period_results->wc_gross_sales) - floatval($prev_period_results->wc_refunded_amount); |
| 345 | |
| 346 | $views = 0; |
| 347 | $woocommerce_orders = 0; |
| 348 | $woocommerce_gross_sales = 0; |
| 349 | $woocommerce_refunded_amount = 0; |
| 350 | $daily_views = []; |
| 351 | $daily_visitors = []; |
| 352 | $daily_sessions = []; |
| 353 | $daily_woocommerce_orders = []; |
| 354 | $daily_woocommerce_net_sales = []; |
| 355 | |
| 356 | foreach ($rows as $row) { |
| 357 | $row_views = intval($row->views); |
| 358 | $row_visitors = intval($row->visitors); |
| 359 | $row_sessions = intval($row->sessions); |
| 360 | $row_woocommerce_orders = intval($row->wc_orders); |
| 361 | $row_woocommerce_gross_sales = floatval($row->wc_gross_sales); |
| 362 | $row_woocommerce_refunded_amount = floatval($row->wc_refunded_amount); |
| 363 | |
| 364 | $views += $row_views; |
| 365 | $woocommerce_orders += $row_woocommerce_orders; |
| 366 | $woocommerce_gross_sales += $row_woocommerce_gross_sales; |
| 367 | $woocommerce_refunded_amount += $row_woocommerce_refunded_amount; |
| 368 | $daily_views[] = [$row->date, $row_views]; |
| 369 | $daily_visitors[] = [$row->date, $row_visitors]; |
| 370 | $daily_sessions[] = [$row->date, $row_sessions]; |
| 371 | $daily_woocommerce_orders[] = [$row->date, $row_woocommerce_orders]; |
| 372 | $daily_woocommerce_net_sales[] = [$row->date, $row_woocommerce_gross_sales - $row_woocommerce_refunded_amount]; |
| 373 | } |
| 374 | |
| 375 | $this->views = $views; |
| 376 | $this->prev_period_views = $prev_period_views; |
| 377 | $this->visitors = $visitors; |
| 378 | $this->prev_period_visitors = $prev_period_visitors; |
| 379 | $this->sessions = $sessions; |
| 380 | $this->prev_period_sessions = $prev_period_sessions; |
| 381 | $this->woocommerce_orders = $woocommerce_orders; |
| 382 | $this->prev_woocommerce_orders = $prev_woocommerce_orders; |
| 383 | $this->woocommerce_net_sales = $woocommerce_gross_sales - $woocommerce_refunded_amount; |
| 384 | $this->prev_woocommerce_net_sales = $prev_woocommerce_net_sales; |
| 385 | $this->daily_views = $this->fill_in_partial_day_range($daily_views); |
| 386 | $this->daily_visitors = $this->fill_in_partial_day_range($daily_visitors); |
| 387 | $this->daily_sessions = $this->fill_in_partial_day_range($daily_sessions); |
| 388 | $this->daily_woocommerce_orders = $this->fill_in_partial_day_range($daily_woocommerce_orders); |
| 389 | $this->daily_woocommerce_net_sales = $this->fill_in_partial_day_range($daily_woocommerce_net_sales); |
| 390 | } |
| 391 | |
| 392 | /** |
| 393 | * @param array $partial_day_range |
| 394 | * @return array |
| 395 | */ |
| 396 | private function fill_in_partial_day_range(array $partial_day_range): array |
| 397 | { |
| 398 | $interval = new \DateInterval('P1D'); |
| 399 | $user_timezone = new \DateTimeZone(Timezone::local_offset()); |
| 400 | $start = clone $this->start(); |
| 401 | $end = clone $this->end(); |
| 402 | $start->setTimezone($user_timezone); |
| 403 | $end->setTimezone($user_timezone); |
| 404 | $date_range = new \DatePeriod($start, $interval, $end); |
| 405 | |
| 406 | $filled_in_data = []; |
| 407 | |
| 408 | foreach ($date_range as $date) { |
| 409 | $stat = $this->get_statistic_for_date($partial_day_range, $date); |
| 410 | $filled_in_data[] = [$date, $stat]; |
| 411 | } |
| 412 | |
| 413 | return $filled_in_data; |
| 414 | } |
| 415 | |
| 416 | /** |
| 417 | * @param array $partial_day_range |
| 418 | * @param \DateTime $datetime_to_match |
| 419 | * @return int Defaults to 0 |
| 420 | */ |
| 421 | private function get_statistic_for_date(array $partial_day_range, \DateTime $datetime_to_match): int |
| 422 | { |
| 423 | $user_timezone = new \DateTimeZone(Timezone::local_offset()); |
| 424 | $default_value = 0; |
| 425 | |
| 426 | foreach ($partial_day_range as $day) { |
| 427 | $date = $day[0]; |
| 428 | $stat = $day[1]; |
| 429 | |
| 430 | try { |
| 431 | $datetime = new \DateTime($date, $user_timezone); |
| 432 | } catch (\Exception $e) { |
| 433 | return $default_value; |
| 434 | } |
| 435 | |
| 436 | // Intentionally using non-strict equality to see if two distinct DateTime objects represent the same time |
| 437 | if ($datetime == $datetime_to_match) { |
| 438 | return $stat; |
| 439 | } |
| 440 | } |
| 441 | |
| 442 | return $default_value; |
| 443 | } |
| 444 | } |
| 445 |