added on local at 2026-07-06 01:09:39
| 1 | #!/usr/bin/perl | |
| 2 | #====================================================================== | |
| 3 | # ContactForge -- Admin Traffic Reports (single-tenant). | |
| 4 | # | |
| 5 | # Tabs: overview | sources | devices | geo | pages | |
| 6 | # All queries cover the whole CRM marketing site -- no per-site filter. | |
| 7 | #====================================================================== | |
| 8 | use strict; | |
| 9 | use warnings; | |
| 10 | ||
| 11 | use lib '/var/www/vhosts/3dshawn.com/crm.3dshawn.com'; | |
| 12 | use CGI; | |
| 13 | use MODS::Template; | |
| 14 | use MODS::DBConnect; | |
| 15 | use MODS::Login; | |
| 16 | use MODS::ContactForge::Config; | |
| 17 | use MODS::ContactForge::Wrapper; | |
| 18 | use MODS::ContactForge::Stats; | |
| 19 | ||
| 20 | my $q = CGI->new; | |
| 21 | my $form = $q->Vars; | |
| 22 | my $auth = MODS::Login->new; | |
| 23 | my $wrap = MODS::ContactForge::Wrapper->new; | |
| 24 | my $tfile = MODS::Template->new; | |
| 25 | my $db = MODS::DBConnect->new; | |
| 26 | my $cfg = MODS::ContactForge::Config->new; | |
| 27 | my $stats = MODS::ContactForge::Stats->new; | |
| 28 | my $DB = $cfg->settings('database_name'); | |
| 29 | ||
| 30 | $|=1; | |
| 31 | my $userinfo = $auth->login_verify(); | |
| 32 | unless ($userinfo) { | |
| 33 | print "Status: 302 Found\nLocation: /login.cgi?return=/admin_traffic.cgi\n\n"; | |
| 34 | exit; | |
| 35 | } | |
| 36 | unless ($userinfo->{is_admin}) { | |
| 37 | print "Status: 403 Forbidden\nContent-Type: text/plain\n\nAdmin access required.\n"; | |
| 38 | exit; | |
| 39 | } | |
| 40 | ||
| 41 | sub _h { | |
| 42 | my $s = shift; $s = '' unless defined $s; | |
| 43 | $s =~ s/&/&/g; $s =~ s/</</g; $s =~ s/>/>/g; | |
| 44 | $s =~ s/"/"/g; $s =~ s/'/'/g; | |
| 45 | return $s; | |
| 46 | } | |
| 47 | # JSON-safe string escape (for the globe_json window.GLOBE_DATA payload). | |
| 48 | sub _jstr { | |
| 49 | my $s = shift; $s = '' unless defined $s; | |
| 50 | $s =~ s/\\/\\\\/g; | |
| 51 | $s =~ s/"/\\"/g; | |
| 52 | $s =~ s/[\x00-\x1f]//g; | |
| 53 | return $s; | |
| 54 | } | |
| 55 | ||
| 56 | my $tab = lc($form->{tab} || 'overview'); | |
| 57 | $tab =~ s/[^a-z]//g; | |
| 58 | $tab = 'overview' unless $tab =~ /^(overview|sources|devices|geo|pages)$/; | |
| 59 | ||
| 60 | my $days = $form->{days}; | |
| 61 | $days =~ s/[^0-9]//g if defined $days; | |
| 62 | $days = 30 unless $days && $days =~ /^\d+$/; | |
| 63 | $days = 730 if $days > 730; # 2-year cap (matches Stats::_range) | |
| 64 | $days = 1 if $days < 1; | |
| 65 | ||
| 66 | # Absolute range support: ?from=YYYY-MM-DDTHH:MM&to=YYYY-MM-DDTHH:MM | |
| 67 | # (HTML5 datetime-local format). Converted to MySQL DATETIME literal. | |
| 68 | sub _parse_dt { | |
| 69 | my $s = shift; | |
| 70 | return undef unless defined($s) && length($s); | |
| 71 | $s =~ s/[^0-9 :T\-]//g; | |
| 72 | # accept YYYY-MM-DDTHH:MM, YYYY-MM-DD HH:MM, YYYY-MM-DD (date only) | |
| 73 | if ($s =~ /^(\d{4}-\d{2}-\d{2})[T ](\d{2}:\d{2})(?::\d{2})?$/) { | |
| 74 | return "$1 $2:00"; | |
| 75 | } | |
| 76 | if ($s =~ /^(\d{4}-\d{2}-\d{2})$/) { | |
| 77 | return "$1 00:00:00"; | |
| 78 | } | |
| 79 | return undef; | |
| 80 | } | |
| 81 | my $abs_from = _parse_dt($form->{from}); | |
| 82 | my $abs_to = _parse_dt($form->{to}); | |
| 83 | my $range; | |
| 84 | my $is_absolute = 0; | |
| 85 | if ($abs_from && $abs_to) { | |
| 86 | $range = { from => $abs_from, to => $abs_to }; | |
| 87 | $is_absolute = 1; | |
| 88 | } else { | |
| 89 | $range = $days; | |
| 90 | } | |
| 91 | $stats->set_range($range); # all subsequent Stats calls use this window | |
| 92 | ||
| 93 | my $dbh = $db->db_connect(); | |
| 94 | ||
| 95 | my $schema_ready = 0; | |
| 96 | { | |
| 97 | my $r = $db->db_readwrite($dbh, qq~ | |
| 98 | SELECT COUNT(*) AS n FROM information_schema.tables | |
| 99 | WHERE table_schema='$DB' | |
| 100 | AND table_name IN ('visitors','tracking_sessions','pageviews') | |
| 101 | ~, $ENV{SCRIPT_NAME}, __LINE__); | |
| 102 | $schema_ready = ($r && $r->{n} && $r->{n} >= 3) ? 1 : 0; | |
| 103 | } | |
| 104 | ||
| 105 | # Always pull the KPI strip so every tab has the summary at the top. | |
| 106 | my $kpis = $schema_ready ? $stats->kpis($db, $dbh, $DB, $days) : {}; | |
| 107 | ||
| 108 | sub _rows_with_pct { | |
| 109 | my ($aref, $label_key, $count_key) = @_; | |
| 110 | my $total = 0; $total += ($_->{$count_key} || 0) for @$aref; | |
| 111 | my @out; | |
| 112 | foreach my $r (@$aref) { | |
| 113 | my $n = $r->{$count_key} || 0; | |
| 114 | my $pct = $total ? sprintf('%.1f', ($n / $total) * 100) : '0.0'; | |
| 115 | push @out, { | |
| 116 | label => _h($r->{$label_key} || 'Unknown'), | |
| 117 | count => $n, | |
| 118 | pct => $pct, | |
| 119 | }; | |
| 120 | } | |
| 121 | return \@out; | |
| 122 | } | |
| 123 | ||
| 124 | my $tvars = { | |
| 125 | schema_ready => $schema_ready ? 1 : 0, | |
| 126 | schema_missing => $schema_ready ? 0 : 1, | |
| 127 | ||
| 128 | tab_overview => ($tab eq 'overview') ? 1 : 0, | |
| 129 | tab_sources => ($tab eq 'sources') ? 1 : 0, | |
| 130 | tab_devices => ($tab eq 'devices') ? 1 : 0, | |
| 131 | tab_geo => ($tab eq 'geo') ? 1 : 0, | |
| 132 | tab_pages => ($tab eq 'pages') ? 1 : 0, | |
| 133 | ||
| 134 | days => $days, | |
| 135 | is_absolute => $is_absolute, | |
| 136 | is_rolling => $is_absolute ? 0 : 1, | |
| 137 | range_from => $is_absolute ? _h($abs_from) : '', | |
| 138 | range_to => $is_absolute ? _h($abs_to) : '', | |
| 139 | # datetime-local input value format (no seconds) | |
| 140 | range_from_input => $is_absolute ? do { (my $x = $abs_from) =~ s/ /T/; substr($x, 0, 16) } : '', | |
| 141 | range_to_input => $is_absolute ? do { (my $x = $abs_to) =~ s/ /T/; substr($x, 0, 16) } : '', | |
| 142 | range_label => $is_absolute | |
| 143 | ? "$abs_from -- $abs_to" | |
| 144 | : "Last $days days", | |
| 145 | preset_24h => (!$is_absolute && $days == 1) ? 1 : 0, | |
| 146 | preset_7d => (!$is_absolute && $days == 7) ? 1 : 0, | |
| 147 | preset_30d => (!$is_absolute && $days == 30) ? 1 : 0, | |
| 148 | preset_90d => (!$is_absolute && $days == 90) ? 1 : 0, | |
| 149 | preset_1y => (!$is_absolute && $days == 365) ? 1 : 0, | |
| 150 | preset_custom => $is_absolute ? 1 : 0, | |
| 151 | kpi_pageviews => $kpis->{pageviews} || 0, | |
| 152 | kpi_visitors => $kpis->{visitors} || 0, | |
| 153 | kpi_sessions => $kpis->{sessions} || 0, | |
| 154 | kpi_avg_seconds => $kpis->{avg_seconds} || 0, | |
| 155 | kpi_bounce_rate => $kpis->{bounce_rate} || 0, | |
| 156 | kpi_pv_per_session => $kpis->{pv_per_session} || 0, | |
| 157 | }; | |
| 158 | ||
| 159 | # Globe data is computed for any tab that may render the widget | |
| 160 | # (currently overview + geo). One call, reused. | |
| 161 | if ($schema_ready && ($tab eq 'overview' || $tab eq 'geo')) { | |
| 162 | my @globe = $stats->by_country_geo($db, $dbh, $DB, $days, 80); | |
| 163 | my @json_rows; | |
| 164 | foreach my $g (@globe) { | |
| 165 | push @json_rows, sprintf( | |
| 166 | '{"code":"%s","name":"%s","lat":%s,"lng":%s,"count":%d}', | |
| 167 | $g->{code}, _jstr($g->{name}), $g->{lat}+0, $g->{lng}+0, $g->{visitors}+0 | |
| 168 | ); | |
| 169 | } | |
| 170 | $tvars->{globe_json} = '[' . join(',', @json_rows) . ']'; | |
| 171 | # Always render the globe, even with zero countries. | |
| 172 | $tvars->{has_globe} = 1; | |
| 173 | ||
| 174 | # State-level rollup for globe.js zoom-in state dots. | |
| 175 | my $sr = $stats->state_rollup($db, $dbh, $DB); | |
| 176 | my @sparts; | |
| 177 | foreach my $code (sort keys %$sr) { | |
| 178 | my @rp; | |
| 179 | foreach my $region (sort keys %{ $sr->{$code} }) { | |
| 180 | my $rj = $region; $rj =~ s/\\/\\\\/g; $rj =~ s/"/\\"/g; $rj =~ s/[\x00-\x1f]//g; | |
| 181 | push @rp, sprintf('"%s":%d', $rj, $sr->{$code}{$region}+0); | |
| 182 | } | |
| 183 | push @sparts, sprintf('"%s":{%s}', $code, join(',', @rp)); | |
| 184 | } | |
| 185 | $tvars->{state_json} = '{' . join(',', @sparts) . '}'; | |
| 186 | ||
| 187 | # Full tree JSON for the state-level modal. | |
| 188 | my $tree2 = $stats->full_drill_tree($db, $dbh, $DB); | |
| 189 | my @tparts; | |
| 190 | foreach my $cc (@{ $tree2->{countries} }) { | |
| 191 | my @sparts2; | |
| 192 | foreach my $st (@{ $cc->{states} }) { | |
| 193 | my @cparts; | |
| 194 | foreach my $ct (@{ $st->{cities} }) { | |
| 195 | my $cj = $ct->{city}; $cj =~ s/\\/\\\\/g; $cj =~ s/"/\\"/g; $cj =~ s/[\x00-\x1f]//g; | |
| 196 | push @cparts, sprintf('{"city":"%s","visitors":%d,"sessions":%d}', $cj, $ct->{visitors}+0, $ct->{sessions}+0); | |
| 197 | } | |
| 198 | my $rj = $st->{region}; $rj =~ s/\\/\\\\/g; $rj =~ s/"/\\"/g; $rj =~ s/[\x00-\x1f]//g; | |
| 199 | push @sparts2, sprintf('"%s":{"visitors":%d,"sessions":%d,"cities":[%s]}', $rj, $st->{visitors}+0, $st->{sessions}+0, join(',', @cparts)); | |
| 200 | } | |
| 201 | push @tparts, sprintf('"%s":{"visitors":%d,"sessions":%d,"states":{%s}}', $cc->{code}, $cc->{visitors}+0, $cc->{sessions}+0, join(',', @sparts2)); | |
| 202 | } | |
| 203 | $tvars->{tree_json} = '{' . join(',', @tparts) . '}'; | |
| 204 | ||
| 205 | # Per-country dimension breakdowns JSON. | |
| 206 | my $dims = $stats->country_dimensions_rollup($db, $dbh, $DB); | |
| 207 | my @dparts; | |
| 208 | foreach my $code (sort keys %$dims) { | |
| 209 | my @dx; | |
| 210 | foreach my $dim (qw(browsers os devices resolutions viewports languages timezones)) { | |
| 211 | my $rh = $dims->{$code}{$dim} || {}; | |
| 212 | my @vp; | |
| 213 | foreach my $v (sort { $rh->{$b} <=> $rh->{$a} } keys %$rh) { | |
| 214 | my $vj = $v; $vj =~ s/\\/\\\\/g; $vj =~ s/"/\\"/g; $vj =~ s/[\x00-\x1f]//g; | |
| 215 | push @vp, sprintf('"%s":%d', $vj, $rh->{$v}+0); | |
| 216 | } | |
| 217 | push @dx, sprintf('"%s":{%s}', $dim, join(',', @vp)); | |
| 218 | } | |
| 219 | push @dparts, sprintf('"%s":{%s}', $code, join(',', @dx)); | |
| 220 | } | |
| 221 | $tvars->{dims_json} = '{' . join(',', @dparts) . '}'; | |
| 222 | ||
| 223 | # Site-wide overall audience breakdown (flatten dims across countries). | |
| 224 | { | |
| 225 | my %overall; | |
| 226 | foreach my $code (keys %$dims) { | |
| 227 | foreach my $dim (qw(browsers os devices resolutions viewports languages timezones)) { | |
| 228 | my $rh = $dims->{$code}{$dim} || {}; | |
| 229 | foreach my $v (keys %$rh) { $overall{$dim}{$v} += $rh->{$v}; } | |
| 230 | } | |
| 231 | } | |
| 232 | my @oparts; | |
| 233 | foreach my $dim (qw(browsers os devices resolutions viewports languages timezones)) { | |
| 234 | my $rh = $overall{$dim} || {}; | |
| 235 | my @vp; | |
| 236 | foreach my $v (sort { $rh->{$b} <=> $rh->{$a} } keys %$rh) { | |
| 237 | my $vj = $v; $vj =~ s/\\/\\\\/g; $vj =~ s/"/\\"/g; $vj =~ s/[\x00-\x1f]//g; | |
| 238 | push @vp, sprintf('"%s":%d', $vj, $rh->{$v}+0); | |
| 239 | } | |
| 240 | push @oparts, sprintf('"%s":{%s}', $dim, join(',', @vp)); | |
| 241 | } | |
| 242 | $tvars->{overall_dims_json} = '{' . join(',', @oparts) . '}'; | |
| 243 | } | |
| 244 | ||
| 245 | # Per-state dimension breakdowns JSON. | |
| 246 | my $sdims = $stats->state_dimensions_rollup($db, $dbh, $DB); | |
| 247 | my @sdparts; | |
| 248 | foreach my $code (sort keys %$sdims) { | |
| 249 | my @rparts; | |
| 250 | foreach my $region (sort keys %{ $sdims->{$code} }) { | |
| 251 | my @dx; | |
| 252 | foreach my $dim (qw(browsers os devices resolutions viewports languages timezones)) { | |
| 253 | my $rh = $sdims->{$code}{$region}{$dim} || {}; | |
| 254 | my @vp; | |
| 255 | foreach my $v (sort { $rh->{$b} <=> $rh->{$a} } keys %$rh) { | |
| 256 | my $vj = $v; $vj =~ s/\\/\\\\/g; $vj =~ s/"/\\"/g; $vj =~ s/[\x00-\x1f]//g; | |
| 257 | push @vp, sprintf('"%s":%d', $vj, $rh->{$v}+0); | |
| 258 | } | |
| 259 | push @dx, sprintf('"%s":{%s}', $dim, join(',', @vp)); | |
| 260 | } | |
| 261 | my $rj = $region; $rj =~ s/\\/\\\\/g; $rj =~ s/"/\\"/g; $rj =~ s/[\x00-\x1f]//g; | |
| 262 | push @rparts, sprintf('"%s":{%s}', $rj, join(',', @dx)); | |
| 263 | } | |
| 264 | push @sdparts, sprintf('"%s":{%s}', $code, join(',', @rparts)); | |
| 265 | } | |
| 266 | $tvars->{sdims_json} = '{' . join(',', @sdparts) . '}'; | |
| 267 | ||
| 268 | # Total country count for the new Meta-Admin-style "Countries" KPI tile. | |
| 269 | $tvars->{kpi_countries} = scalar(@globe); | |
| 270 | } | |
| 271 | ||
| 272 | if ($schema_ready && $tab eq 'overview') { | |
| 273 | # Daily series: only meaningful for rolling windows. In absolute | |
| 274 | # mode we skip it -- the picker label up top tells the user what | |
| 275 | # window the rest of the page reflects. | |
| 276 | if (!$is_absolute) { | |
| 277 | my $series = $stats->daily_series($db, $dbh, $DB, $days); | |
| 278 | $tvars->{series_labels_json} = '["' . join('","', @{$series->{labels}}) . '"]'; | |
| 279 | $tvars->{series_pv_json} = '[' . join(',', @{$series->{pageviews}}) . ']'; | |
| 280 | $tvars->{series_vs_json} = '[' . join(',', @{$series->{visitors}}) . ']'; | |
| 281 | $tvars->{has_series} = scalar(@{$series->{labels}}) ? 1 : 0; | |
| 282 | } else { | |
| 283 | $tvars->{has_series} = 0; | |
| 284 | } | |
| 285 | ||
| 286 | my @src = $stats->by_source($db, $dbh, $DB, $days, 8); | |
| 287 | my @br = $stats->by_browser($db, $dbh, $DB, $days, 6); | |
| 288 | my @top = $stats->by_page($db, $dbh, $DB, $days, 10); | |
| 289 | $tvars->{src_rows} = _rows_with_pct(\@src, 'source_kind', 'sessions'); | |
| 290 | $tvars->{has_src_rows} = scalar(@src) ? 1 : 0; | |
| 291 | $tvars->{br_rows} = _rows_with_pct(\@br, 'browser', 'sessions'); | |
| 292 | $tvars->{has_br_rows} = scalar(@br) ? 1 : 0; | |
| 293 | my @top_rows; | |
| 294 | foreach my $p (@top) { | |
| 295 | push @top_rows, { | |
| 296 | path => _h($p->{page_path} || '/'), | |
| 297 | pageviews => $p->{pageviews} || 0, | |
| 298 | sessions => $p->{sessions} || 0, | |
| 299 | }; | |
| 300 | } | |
| 301 | $tvars->{top_pages} = \@top_rows; | |
| 302 | $tvars->{has_top_pages} = scalar(@top_rows) ? 1 : 0; | |
| 303 | ||
| 304 | # ----- Accordion drill on Overview: full country -> state -> city tree. | |
| 305 | my $tree = $stats->full_drill_tree($db, $dbh, $DB); | |
| 306 | my $has_countries = ($tree && scalar @{ $tree->{countries} } > 0) ? 1 : 0; | |
| 307 | my $acc_html = q~ | |
| 308 | <style> | |
| 309 | .accord-scope { position: relative; --acc-brand: var(--col-brand, var(--col-brand-2, var(--col-accent, #5aa9ff))); } | |
| 310 | .accord-collapse-all { background: var(--col-bg-2, #0e1822); color: var(--col-text-muted); border: 1px solid var(--col-border); padding: 5px 11px; border-radius: 7px; font-size: 11px; font-weight: 600; cursor: pointer; transition: all .12s ease; font-family: inherit; margin-left: auto; } | |
| 311 | .accord-collapse-all:hover { color: var(--col-text); border-color: var(--acc-brand); } | |
| 312 | .accord-tbl { width:100%; border-collapse: collapse; margin-bottom: 8px; } | |
| 313 | .accord-tbl th { background: rgba(255,255,255,.03); padding: 10px 14px; text-align: left; font-size: 10.5px; text-transform: uppercase; letter-spacing: 1.4px; color: var(--col-text-muted); font-weight: 700; border-bottom: 1px solid var(--col-border); } | |
| 314 | .accord-tbl td { padding: 11px 14px; border-bottom: 1px solid var(--col-border); font-size: 13px; vertical-align: middle; } | |
| 315 | .accord-tbl tr.expandable { cursor: pointer; transition: background .12s ease; } | |
| 316 | .accord-tbl tr.expandable:hover { background: color-mix(in srgb, var(--acc-brand) 6%, rgba(255,255,255,.02)); } | |
| 317 | .accord-tbl tr.expandable.is-open { background: color-mix(in srgb, var(--acc-brand) 10%, transparent); box-shadow: inset 3px 0 0 0 var(--acc-brand); } | |
| 318 | .accord-tbl .chev { display:inline-block; width:14px; color: var(--col-text-muted); transition: transform .15s ease, color .15s ease; margin-right:6px; } | |
| 319 | .accord-tbl tr.expandable.is-open .chev { transform: rotate(90deg); color: var(--acc-brand); } | |
| 320 | .accord-tbl tr.kids > td { padding: 0 !important; background: rgba(0,0,0,.18); border-bottom: 1px solid var(--col-border); } | |
| 321 | .accord-tbl tr.kids[hidden] { display: none; } | |
| 322 | .accord-tbl .nested { width:100%; border-collapse: collapse; } | |
| 323 | .accord-tbl .nested td { padding: 9px 14px 9px 36px; font-size: 12.5px; border-bottom: 1px solid rgba(255,255,255,.05); } | |
| 324 | .accord-tbl .nested .nested td { padding-left: 58px; font-size: 12px; } | |
| 325 | .accord-tbl .num { text-align:right; font-variant-numeric: tabular-nums; } | |
| 326 | .accord-tbl .leaf td { padding-left: 80px; color: var(--col-text-2); } | |
| 327 | .accord-tbl tr.accord-empty td { text-align:center; color: var(--col-text-muted); padding: 26px 14px; font-size: 12.5px; font-style: italic; } | |
| 328 | </style> | |
| 329 | <div class="accord-scope"> | |
| 330 | <div style="display:flex;align-items:center;margin-bottom:8px;gap:8px"><span style="font-size:11px;color:var(--col-text-dim)">click any row with the chevron to expand</span><button type="button" class="accord-collapse-all">Collapse all</button></div> | |
| 331 | <table class="accord-tbl"> | |
| 332 | <thead><tr><th>Country / state / city</th><th class="num">Visitors</th><th class="num">Sessions</th></tr></thead> | |
| 333 | <tbody> | |
| 334 | ~; | |
| 335 | if ($has_countries) { | |
| 336 | my $ci = 0; | |
| 337 | foreach my $cc (@{ $tree->{countries} }) { | |
| 338 | $ci++; | |
| 339 | my $cid = "atcnt-$ci"; | |
| 340 | my $cname = _h($cc->{name}); | |
| 341 | my $has_states = scalar @{ $cc->{states} } > 0 ? 1 : 0; | |
| 342 | my $chev = $has_states ? '<span class="chev">›</span>' : '<span class="chev" style="opacity:0">›</span>'; | |
| 343 | $acc_html .= qq~<tr class="expandable" data-target="$cid"><td><strong>$chev$cname</strong></td><td class="num">$cc->{visitors}</td><td class="num">$cc->{sessions}</td></tr>~; | |
| 344 | if ($has_states) { | |
| 345 | $acc_html .= qq~<tr class="kids" id="$cid" hidden><td colspan="3"><table class="nested"><tbody>~; | |
| 346 | my $si = 0; | |
| 347 | foreach my $st (@{ $cc->{states} }) { | |
| 348 | $si++; | |
| 349 | my $sid = "${cid}-st-$si"; | |
| 350 | my $rname = _h($st->{region}); | |
| 351 | my $has_cities = scalar @{ $st->{cities} } > 0 ? 1 : 0; | |
| 352 | my $schev = $has_cities ? '<span class="chev">›</span>' : '<span class="chev" style="opacity:0">›</span>'; | |
| 353 | $acc_html .= qq~<tr class="expandable" data-target="$sid"><td>$schev$rname</td><td class="num">$st->{visitors}</td><td class="num">$st->{sessions}</td></tr>~; | |
| 354 | if ($has_cities) { | |
| 355 | $acc_html .= qq~<tr class="kids" id="$sid" hidden><td colspan="3"><table class="nested"><tbody>~; | |
| 356 | foreach my $ct (@{ $st->{cities} }) { | |
| 357 | my $cn = _h($ct->{city}); | |
| 358 | $acc_html .= qq~<tr class="leaf"><td>$cn</td><td class="num">$ct->{visitors}</td><td class="num">$ct->{sessions}</td></tr>~; | |
| 359 | } | |
| 360 | $acc_html .= '</tbody></table></td></tr>'; | |
| 361 | } | |
| 362 | } | |
| 363 | $acc_html .= '</tbody></table></td></tr>'; | |
| 364 | } | |
| 365 | } | |
| 366 | } else { | |
| 367 | $acc_html .= q~<tr class="accord-empty"><td colspan="3">No geo data yet — this table fills in as visitors are recorded.</td></tr>~; | |
| 368 | } | |
| 369 | $acc_html .= q~ </tbody> | |
| 370 | </table> | |
| 371 | </div> | |
| 372 | <script> | |
| 373 | (function(){ | |
| 374 | document.addEventListener('click', function(e){ | |
| 375 | var btn = e.target.closest('.accord-collapse-all'); | |
| 376 | if (btn) { | |
| 377 | var scope = btn.closest('.accord-scope') || document; | |
| 378 | scope.querySelectorAll('tr.kids').forEach(function(k){ k.hidden = true; }); | |
| 379 | scope.querySelectorAll('tr.expandable.is-open').forEach(function(t){ t.classList.remove('is-open'); }); | |
| 380 | return; | |
| 381 | } | |
| 382 | var tr = e.target.closest('tr.expandable'); | |
| 383 | if (!tr) return; | |
| 384 | var id = tr.dataset.target; | |
| 385 | if (!id) return; | |
| 386 | var kids = document.getElementById(id); | |
| 387 | if (!kids) return; | |
| 388 | var open = !kids.hidden; | |
| 389 | kids.hidden = open; | |
| 390 | tr.classList.toggle('is-open', !open); | |
| 391 | }); | |
| 392 | })(); | |
| 393 | </script> | |
| 394 | ~; | |
| 395 | $tvars->{accordion_html} = $acc_html; | |
| 396 | $tvars->{has_accordion} = 1; | |
| 397 | } | |
| 398 | elsif ($schema_ready && $tab eq 'sources') { | |
| 399 | my @src = $stats->by_source($db, $dbh, $DB, $days, 12); | |
| 400 | my @refs = $stats->by_referrer_host($db, $dbh, $DB, $days, 25); | |
| 401 | my @utm = $stats->by_utm_campaign($db, $dbh, $DB, $days, 25); | |
| 402 | $tvars->{src_rows} = _rows_with_pct(\@src, 'source_kind', 'sessions'); | |
| 403 | $tvars->{has_src_rows} = scalar(@src) ? 1 : 0; | |
| 404 | $tvars->{ref_rows} = _rows_with_pct(\@refs, 'host', 'sessions'); | |
| 405 | $tvars->{has_ref_rows} = scalar(@refs) ? 1 : 0; | |
| 406 | my @utm_rows; | |
| 407 | foreach my $u (@utm) { | |
| 408 | push @utm_rows, { | |
| 409 | campaign => _h($u->{campaign} || '(none)'), | |
| 410 | source => _h($u->{source} || '(none)'), | |
| 411 | medium => _h($u->{medium} || '(none)'), | |
| 412 | sessions => $u->{sessions} || 0, | |
| 413 | }; | |
| 414 | } | |
| 415 | $tvars->{utm_rows} = \@utm_rows; | |
| 416 | $tvars->{has_utm_rows} = scalar(@utm_rows) ? 1 : 0; | |
| 417 | } | |
| 418 | elsif ($schema_ready && $tab eq 'devices') { | |
| 419 | my @dev = $stats->by_device ($db, $dbh, $DB, $days); | |
| 420 | my @br = $stats->by_browser ($db, $dbh, $DB, $days, 12); | |
| 421 | my @os = $stats->by_os ($db, $dbh, $DB, $days, 12); | |
| 422 | my @res = $stats->by_resolution($db, $dbh, $DB, $days, 12); | |
| 423 | $tvars->{dev_rows} = _rows_with_pct(\@dev, 'device_type', 'sessions'); | |
| 424 | $tvars->{has_dev_rows} = scalar(@dev) ? 1 : 0; | |
| 425 | $tvars->{br_rows} = _rows_with_pct(\@br, 'browser', 'sessions'); | |
| 426 | $tvars->{has_br_rows} = scalar(@br) ? 1 : 0; | |
| 427 | $tvars->{os_rows} = _rows_with_pct(\@os, 'os', 'sessions'); | |
| 428 | $tvars->{has_os_rows} = scalar(@os) ? 1 : 0; | |
| 429 | $tvars->{res_rows} = _rows_with_pct(\@res, 'resolution', 'sessions'); | |
| 430 | $tvars->{has_res_rows} = scalar(@res) ? 1 : 0; | |
| 431 | } | |
| 432 | elsif ($schema_ready && $tab eq 'geo') { | |
| 433 | # Enriched country leaderboard: per-country engagement metrics | |
| 434 | # (avg duration, bounce rate, PVs/session) on top of visitor/session counts. | |
| 435 | my @c = $stats->by_country_enriched($db, $dbh, $DB, $days, 30); | |
| 436 | my @rows; | |
| 437 | my $total = 0; $total += ($_->{visitors} || 0) for @c; | |
| 438 | foreach my $r (@c) { | |
| 439 | my $n = $r->{visitors} || 0; | |
| 440 | my $sec = int($r->{avg_duration_seconds} || 0); | |
| 441 | my $dur = $sec >= 60 | |
| 442 | ? sprintf('%dm %02ds', int($sec/60), $sec % 60) | |
| 443 | : "${sec}s"; | |
| 444 | my $br = sprintf('%.1f%%', 100 * ($r->{bounce_rate} || 0)); | |
| 445 | my $pvs = sprintf('%.2f', ($r->{pv_per_session} || 0)); | |
| 446 | push @rows, { | |
| 447 | country => _h($r->{country} || '(unknown)'), | |
| 448 | visitors => $n, | |
| 449 | sessions => $r->{sessions} || 0, | |
| 450 | pct => $total ? sprintf('%.1f', ($n / $total) * 100) : '0.0', | |
| 451 | avg_duration => $dur, | |
| 452 | bounce_rate => $br, | |
| 453 | pv_per_session => $pvs, | |
| 454 | }; | |
| 455 | } | |
| 456 | $tvars->{country_rows} = \@rows; | |
| 457 | $tvars->{has_country_rows} = scalar(@rows) ? 1 : 0; | |
| 458 | ||
| 459 | # Timezone breakdown | |
| 460 | my @tz = $stats->by_timezone($db, $dbh, $DB, $days, 15); | |
| 461 | $tvars->{tz_rows} = _rows_with_pct(\@tz, 'timezone', 'sessions'); | |
| 462 | $tvars->{has_tz_rows} = scalar(@tz) ? 1 : 0; | |
| 463 | ||
| 464 | # Country drill (?country=US -> regions + cities for US) | |
| 465 | my $drill = $form->{country} || ''; | |
| 466 | $drill =~ s/[^A-Za-z]//g; | |
| 467 | $drill = uc(substr($drill, 0, 2)); | |
| 468 | $tvars->{has_country_drill} = 0; | |
| 469 | if (length($drill) == 2) { | |
| 470 | my $d = $stats->country_drill($db, $dbh, $DB, $drill, $days); | |
| 471 | if ($d && $d->{name}) { | |
| 472 | $tvars->{has_country_drill} = 1; | |
| 473 | $tvars->{drill_country_name} = _h($d->{name}); | |
| 474 | $tvars->{drill_total_visitors} = $d->{visitors}; | |
| 475 | $tvars->{drill_total_sessions} = $d->{sessions}; | |
| 476 | my @dr; | |
| 477 | foreach my $r (@{ $d->{regions} || [] }) { | |
| 478 | push @dr, { | |
| 479 | region => _h($r->{region} || '(unknown)'), | |
| 480 | visitors => $r->{visitors} || 0, | |
| 481 | sessions => $r->{sessions} || 0, | |
| 482 | }; | |
| 483 | } | |
| 484 | $tvars->{drill_regions} = \@dr; | |
| 485 | $tvars->{has_drill_regions} = scalar(@dr) ? 1 : 0; | |
| 486 | my @dc; | |
| 487 | foreach my $c2 (@{ $d->{cities} || [] }) { | |
| 488 | push @dc, { | |
| 489 | city => _h($c2->{city} || '(unknown)'), | |
| 490 | region => _h($c2->{region} || ''), | |
| 491 | visitors => $c2->{visitors} || 0, | |
| 492 | sessions => $c2->{sessions} || 0, | |
| 493 | }; | |
| 494 | } | |
| 495 | $tvars->{drill_cities} = \@dc; | |
| 496 | $tvars->{has_drill_cities} = scalar(@dc) ? 1 : 0; | |
| 497 | } | |
| 498 | } | |
| 499 | } | |
| 500 | elsif ($schema_ready && $tab eq 'pages') { | |
| 501 | my @pages = $stats->by_page($db, $dbh, $DB, $days, 50); | |
| 502 | my @landing = $stats->by_landing_page($db, $dbh, $DB, $days, 25); | |
| 503 | my @top_rows; | |
| 504 | foreach my $p (@pages) { | |
| 505 | push @top_rows, { | |
| 506 | path => _h($p->{page_path} || '/'), | |
| 507 | pageviews => $p->{pageviews} || 0, | |
| 508 | sessions => $p->{sessions} || 0, | |
| 509 | }; | |
| 510 | } | |
| 511 | $tvars->{top_pages} = \@top_rows; | |
| 512 | $tvars->{has_top_pages} = scalar(@top_rows) ? 1 : 0; | |
| 513 | my @land_rows; | |
| 514 | foreach my $l (@landing) { | |
| 515 | push @land_rows, { | |
| 516 | path => _h($l->{entry_page} || '/'), | |
| 517 | sessions => $l->{sessions} || 0, | |
| 518 | bounce => sprintf('%.1f', 100 * ($l->{bounce_rate} || 0)), | |
| 519 | }; | |
| 520 | } | |
| 521 | $tvars->{landing_rows} = \@land_rows; | |
| 522 | $tvars->{has_landing_rows} = scalar(@land_rows) ? 1 : 0; | |
| 523 | } | |
| 524 | ||
| 525 | $db->db_disconnect($dbh); | |
| 526 | ||
| 527 | print "Content-Type: text/html; charset=utf-8\nCache-Control: no-store, no-cache, must-revalidate, max-age=0\nPragma: no-cache\nExpires: 0\n\n"; | |
| 528 | ||
| 529 | my $body = join('', $tfile->template('cf_admin_traffic.html', $tvars, $userinfo)); | |
| 530 | ||
| 531 | $wrap->render({ | |
| 532 | userinfo => $userinfo, | |
| 533 | page_key => 'admin_traffic', | |
| 534 | title => 'Traffic Reports', | |
| 535 | body => $body, | |
| 536 | extra_js => ['/assets/javascript/globe.js?v=1783314562', | |
| 537 | '/assets/javascript/datepicker.js?v=3'], | |
| 538 | }); |