source: trunk/npemap.org.uk/perllib/NPEMap/Postcodes.pm @ 230

Last change on this file since 230 was 230, checked in by Dominic Hargreaves, 13 years ago

Better stats stuff

File size: 1.6 KB
Line 
1package NPEMap::Postcodes;
2
3use vars qw(@ISA @EXPORT);
4use warnings;
5use strict;
6
7use vars qw($tt_config);
8
9use Template;
10use NPEMap::Config;
11
12require Exporter;
13@ISA    = qw(Exporter);
14@EXPORT = qw(build_home_stats);
15
16sub _getAllStats {
17    my $dbh = shift;
18    my $sql = <<"HERE";
19SELECT COUNT(*) AS all,
20       SUM(CASE WHEN source = 0 THEN 1 ELSE 0 END) AS us,
21       COUNT(DISTINCT outward) AS all_out,
22       COUNT(DISTINCT CASE WHEN source = 0 THEN outward ELSE '' END) - 1 AS us_out
23FROM postcodes WHERE deleted = 'f';
24HERE
25
26    my $sth = $dbh->prepare($sql);
27
28    $sth->execute();
29
30    return $sth->fetchrow_hashref;
31}
32
33sub _getDateStats {
34    my $dbh = shift;
35    my $sql = <<"HERE";
36SELECT TO_CHAR(DATE_TRUNC('day', created_at), 'YYYY-MM-DD') AS day,
37       COUNT(*) AS all,
38       SUM(CASE WHEN source=0 THEN 1 ELSE 0 END) AS us
39FROM postcodes WHERE deleted ='f'
40GROUP BY  date_trunc('day', created_at)
41ORDER BY  date_trunc('day', created_at);
42
43HERE
44
45    my $sth = $dbh->prepare($sql);
46    $sth->execute();
47    return $sth->fetchrow_hashref;
48}
49
50=pod
51
52=head build_home_stats
53
54build_home_stats takes a database handle as a parameter and builds the
55HTML stats for inclusion into the home page
56
57=cut
58
59sub build_home_stats {
60    my $dbh = shift;
61    my $template = Template->new($tt_config);
62
63    my $allStats = _getAllStats($dbh);
64    my $dateStats = _getDateStats($dbh);
65
66    my $vars = {
67        allstats => $allStats,
68        datestats => $dateStats
69    };
70   
71    $template->process('home_stats.tt', $vars, $staticdir . '/stats.html')
72        or warn $template->error();
73}
74
751;
Note: See TracBrowser for help on using the repository browser.