%#============================================================================ %# ePortal - WEB Based daily organizer %# Author - S.Rusakov %# %# Copyright (c) 2000-2004 Sergey Rusakov. All rights reserved. %# This program is open source software %# %# %#---------------------------------------------------------------------------- <%perl> # Промежуточные данные хранятся в виде # Title | hits/clicks | date1 | date2 | mon1 | mon2 | # hits/clicks/visitors # Это массив хэшей # Поиск возможных вариантов для даты my @distinct_dates; my $st = new ePortal::ThePersistent::Support( SQL => "SELECT distinct date FROM Statistics", OrderBy => 'date DESC', Attributes => { date => { dtype => 'Date' } }, ); $st->restore_where(limit_rows => $ePortal::Catalog::MAX_DATES_SHOW); while($st->restore_next) { push @distinct_dates, $st->date; } # Поиск возможных вариантов для месяцев my @distinct_month; $st = new ePortal::ThePersistent::Support( SQL => "SELECT distinct date_format(date,'%m.%Y') as month FROM Statistics", OrderBy => 'date DESC', ); $st->restore_where(limit_rows => $ePortal::Catalog::MAX_MONTH_SHOW); while($st->restore_next) { push @distinct_month, $st->month; } # Сбор статистики по датам $st = new ePortal::ThePersistent::Support( SQL => "SELECT c.Title, c.Clicks, c.Hits, s.date, sum(s.hits) as daily_hits, count(distinct s.visitor) as daily_visitor from Statistics s left join Catalog c on s.catalog_id=c.id", OrderBy => 'c.Title, date', GroupBy => 'catalog_id, date', #Where => 's.date >= date_sub( Attributes => { date => { dtype => 'Date' } }, ); $st->restore_where(); my %h = (); my @data; while($st->restore_next) { if ( $h{title} ne $st->Title ) { push @data, { %h } if exists $h{title}; %h = (); $h{title} = $st->Title; $h{clicks} = $st->Hits . '/' . $st->Clicks; } $h{$st->date} = $st->daily_hits . '/' . $st->daily_visitor; } push @data, { %h } if exists $h{title}; # Сбор статистики по месяцам $st = new ePortal::ThePersistent::Support( SQL => q{SELECT c.Title, c.Clicks, c.Hits, date_format(s.date, '%m.%Y') as month, sum(s.hits) as monthly_hits, count(distinct s.visitor) as monthly_visitor from Statistics s left join Catalog c on s.catalog_id=c.id}, OrderBy => 'c.title, month', GroupBy => 'catalog_id, month', ); $st->restore_where(); while($st->restore_next) { foreach my $d (@data) { if ( $d->{title} eq $st->Title ) { $d->{$st->month} = $st->monthly_hits . '/' . $st->monthly_visitor; last; } } }

% foreach (@distinct_dates) { % } % foreach (@distinct_month) { % } % foreach my $d (@data) { % foreach (@distinct_dates) { % } % foreach (@distinct_month) { % } % }
<% pick_lang(rus => "Ресурс", eng => "Resource") %> <% pick_lang(rus => "Всего", eng => "Total") %>
Hits/Clicks
<% $_ %>
Hits/Visitors
<% $_ %>
Hits/Visitors
<% $d->{title} %> <% $d->{clicks} %><% $d->{$_} || '-' %><% $d->{$_} || '-' %>

<% pick_lang(rus => "Примечание:", eng => "Memo:") %>
Hits - <% pick_lang(rus => "Общее кол-во обращений к ресурсу", eng => "Hits to resource's page") %>
Clicks - <% pick_lang(rus => "Кол-во переходов на ресурс через Каталог", eng => "Clicks on resource in Catalogue") %>
Visitors - <% pick_lang(rus => "Кол-во посетителей", eng => "Number of visitors") %>

<% pick_lang(rus => "Показаны только те ресурсы, по которым накоплена статистика", eng => "Only resrouces with statistics gathered are shown") %>

%#=== @METAGS attr ========================================================= <%attr> Title => {rus => "Статистика каталога", eng => "Catalogue statistics"}