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

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

Add last 5 postcodes

File size: 2.0 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->fetchall_arrayref({});
48}
49
50sub _getLastPostcodes {
51    my $dbh = shift;
52    my $num = shift;
53    my $sql = <<"HERE";
54SELECT easting, northing, outward, inward, TO_CHAR(created_at, 'DDth Mon HH24:MI')AS date
55FROM postcodes
56WHERE NOT deleted
57ORDER BY created_at DESC LIMIT ?
58HERE
59
60    my $sth = $dbh->prepare($sql);
61    $sth->execute($num);
62    return $sth->fetchall_arrayref({});
63}
64
65=pod
66
67=head build_home_stats
68
69build_home_stats takes a database handle as a parameter and builds the
70HTML stats for inclusion into the home page
71
72=cut
73
74sub build_home_stats {
75    my $dbh = shift;
76    my $template = Template->new($tt_config);
77
78    my $allStats = _getAllStats($dbh);
79    my $dateStats = _getDateStats($dbh);
80    my $lastPostcodes = _getLastPostcodes($dbh, 5);
81
82    my $vars = {
83        allstats => $allStats,
84        datestats => $dateStats,
85        lastpostcodes => $lastPostcodes
86    };
87   
88    $template->process('home_stats.tt', $vars, $staticdir . '/stats.html')
89        or warn $template->error();
90}
91
921;
Note: See TracBrowser for help on using the repository browser.