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